# Ensure Semantic Link Is Pip Installed First

# Import SemPy
import sempy.fabric as fabric
import datetime

# Set Variables
datasetname = "Fabric Capacity Metrics" # Default Microsoft Naming Convention
workspacename = "Microsoft Fabric Capacity Metrics Example" # Specify Your Workspace Name Here Where You Capacity App Resides
dmvquery = """SELECT [Name] as TableName FROM $SYSTEM.TMSCHEMA_TABLES WHERE [Name] <> 'All Measures' ORDER BY [Name]"""

# Get Table Names From Fabric Capacity App Semantic Model
df_table_names = fabric.evaluate_dax(
    datasetname,
    dmvquery,
    workspacename
)

# Loop through each TableName in the DataFrame
for index, row in df_table_names.iterrows():
    tablename = row['TableName']
    
    # Construct the DAX query dynamically to pass in
    daxevaluatequery = f"EVALUATE '{tablename}'"
    
    # Execute the DAX Query
    df_dax_query = fabric.evaluate_dax(
        datasetname,
        daxevaluatequery,
        workspacename
    )

    # Remove any whitespace from tablename in order to use it to create an object name in your lakehouse
    tablenamenospaces = tablename.replace(" ", "")

    # Now Append AuditTimestamp Column & Write Result To Lakehouse To Query Using SQL Endpoint
    now = datetime.datetime.now()
    df_dax_query["AuditTimestamp"] = now
    df_dax_query.to_lakehouse_table(f"{tablenamenospaces}", "overwrite")
    
    print(f"Successfully loaded {tablename} to Default Lakehouse")
    
print("Job Complete")