#!/usr/bin/env python3
"""
Migration script to make PO optional in GRN tables.
This allows creating GRNs without requiring a Purchase Order.
"""

import asyncio
import asyncpg
import os
from dotenv import load_dotenv

# Load environment variables
load_dotenv()

async def run_migration():
    """Run the database migration to make PO fields optional"""
    
    # Database connection parameters
    db_config = {
        'host': os.getenv('POSTGRES_HOST', 'ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech'),
        'port': int(os.getenv('POSTGRES_PORT', 5432)),
        'database': os.getenv('POSTGRES_DB', 'cuatrolabs'),
        'user': os.getenv('POSTGRES_USER', 'trans_owner'),
        'password': os.getenv('POSTGRES_PASSWORD'),
        'ssl': 'require'
    }
    
    print("🔄 Starting migration: Make PO optional in GRN tables")
    print(f"📡 Connecting to: {db_config['host']}:{db_config['port']}/{db_config['database']}")
    
    try:
        # Connect to database
        conn = await asyncpg.connect(**db_config)
        print("✅ Connected to database")
        
        # Start transaction
        async with conn.transaction():
            print("\n📝 Executing migration steps...")
            
            # Step 1: Make po_id nullable in scm_grn table
            print("1️⃣ Making po_id nullable in scm_grn table...")
            await conn.execute("""
                ALTER TABLE scm_grn 
                ALTER COLUMN po_id DROP NOT NULL;
            """)
            print("   ✅ scm_grn.po_id is now nullable")
            
            # Step 2: Make po_item_id nullable in scm_grn_item table
            print("2️⃣ Making po_item_id nullable in scm_grn_item table...")
            await conn.execute("""
                ALTER TABLE scm_grn_item 
                ALTER COLUMN po_item_id DROP NOT NULL;
            """)
            print("   ✅ scm_grn_item.po_item_id is now nullable")
            
            # Step 3: Make batch_no nullable in scm_grn_item table (if not already)
            print("3️⃣ Making batch_no nullable in scm_grn_item table...")
            await conn.execute("""
                ALTER TABLE scm_grn_item 
                ALTER COLUMN batch_no DROP NOT NULL;
            """)
            print("   ✅ scm_grn_item.batch_no is now nullable")
            
            print("\n🎉 Migration completed successfully!")
            print("📋 Summary of changes:")
            print("   • scm_grn.po_id: NOT NULL → NULL")
            print("   • scm_grn_item.po_item_id: NOT NULL → NULL") 
            print("   • scm_grn_item.batch_no: NOT NULL → NULL")
            print("\n💡 GRNs can now be created without Purchase Orders!")
        
        await conn.close()
        print("🔌 Database connection closed")
        
    except Exception as e:
        print(f"❌ Migration failed: {str(e)}")
        raise

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