scripts/apply_sql_fix.py

import asyncio
import os
import sys

# Add project root to python path
sys.path.append(os.getcwd())

from sqlalchemy import text
from app.sql import async_session

async def apply_sql_fix():
    print("Applying SQL fix for fn_get_po_items_for_purchase_return...")
    
    sql_file_path = "app/sql/fn_get_po_items_for_purchase_return.sql"
    
    with open(sql_file_path, "r") as f:
        sql_content = f.read()
        
    async with async_session() as session:
        try:
            # Split the SQL content into statements
            # The file contains a function definition ending with $BODY$; and an ALTER statement
            parts = sql_content.split("$BODY$;")
            
            if len(parts) >= 2:
                # Reconstruct the CREATE FUNCTION statement
                create_func_stmt = parts[0] + "$BODY$;"
                print("Executing CREATE FUNCTION statement...")
                await session.execute(text(create_func_stmt))
                
                # The rest might contain the ALTER statement
                remaining = parts[1].strip()
                if remaining:
                    print(f"Executing remaining statement: {remaining[:50]}...")
                    await session.execute(text(remaining))
            else:
                # Fallback if split fails (unexpected format), try executing as is (might fail)
                print("Could not split by $BODY$;, attempting to execute as single statement...")
                await session.execute(text(sql_content))
                
            await session.commit()
            print("✅ SQL fix applied successfully.")
        except Exception as e:
            print(f"❌ Error applying SQL fix: {e}")
            await session.rollback()

if __name__ == "__main__":
    asyncio.run(apply_sql_fix())