import subprocess
import os

sql_instance = "."
sql_database = "master"
folder_to_check = "./sql_scripts/"

def run_ps_command(cmd):
    # Build the PowerShell invocation list
    command = [
        "PowerShell",
        "-ExecutionPolicy", "Unrestricted",
        "-Command", cmd
    ]

    result = subprocess.run(
        command,
        capture_output=True,
        text=True
    )
    return result

for file in os.listdir(folder_to_check):
    if file.endswith(".sql"):
        full_path = os.path.join(folder_to_check, file)
        print(f"executing .sql file: {full_path}")

        sql_command = (
            f"Invoke-Sqlcmd -ServerInstance {sql_instance} "
            f"-Database {sql_database} -InputFile \"{full_path}\""
        )

        result = run_ps_command(sql_command)

        if result.returncode != 0:
            print("Error executing script:")
            print(result.stderr.strip())
        else:
            if result.stdout:
                print("Output:")
                print(result.stdout.strip())