import pyodbc
import os
import re

# =====================================================
# CONFIGURATION (Your Docker setup)
# =====================================================
server = 'localhost,1470'                                      # DEV server local port 1470
database = 'XXXX'                                              # Database name
username = 'sa'                                                # User
password = 'Admin2022!'                                        # Password
schemas = ['SA', 'CA', 'DW']                                   # Schemas to export (SA,CA,DW)
output_file = r'C:\PROJECTS\liquibase_demo\All_Procedures_DEV.sql' # To adapt if needed

# =====================================================
# CONNEXION + EXPORT NETTOYÉ
# =====================================================
conn_str = (
    f"DRIVER={{ODBC Driver 17 for SQL Server}};"
    f"SERVER={server};"
    f"DATABASE={database};"
    f"UID={username};"
    f"PWD={password};"
    f"TrustServerCertificate=yes;"
    f"Encrypt=yes;"
)

try:
    conn = pyodbc.connect(conn_str)
    cursor = conn.cursor()

    schema_list = "','".join(schemas)
    query = f"""
    SELECT 
        ROUTINE_SCHEMA, 
        ROUTINE_NAME, 
        OBJECT_DEFINITION(OBJECT_ID(ROUTINE_SCHEMA + '.' + ROUTINE_NAME)) AS FullDefinition
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_TYPE = 'PROCEDURE' 
      AND ROUTINE_SCHEMA IN ('{schema_list}')
    ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME
    """
    
    cursor.execute(query)
    procedures = cursor.fetchall()
    
    with open(output_file, 'w', encoding='utf-8') as f:
        for proc in procedures:
            schema, name, definition = proc
            
            if definition: 
                clean_def = re.sub(r'^\s*--.*$\n?', '', definition, flags=re.MULTILINE)
                clean_def = re.sub(r'^\s*\n+', '', clean_def, flags=re.MULTILINE)
                
                f.write(f"-- {schema}.{name}\n")
                f.write(clean_def.rstrip())
                f.write("\nGO\n\n")
    
    print(f"✅ {len(procedures)} exported stored procedures → {output_file}")
    
except Exception as e:
    print(f"❌ Error : {e}")
    
finally:
    if 'conn' in locals():
        conn.close()