"""
Apply Serial Tracker migration - adds sale/invoice/receipt links and lifecycle fields to serial_numbers
"""
from app.core.database import engine
from sqlalchemy import text

def apply_migration():
    with engine.connect() as conn:
        print("🔧 Applying Serial Tracker migration...")

        # 1. Make inventory_transaction_id nullable
        print("  ➜ Making inventory_transaction_id nullable...")
        conn.execute(text("""
            ALTER TABLE serial_numbers
            ALTER COLUMN inventory_transaction_id DROP NOT NULL
        """))

        # 2. Add new columns
        print("  ➜ Adding sale_id, invoice_id, receipt_id columns...")
        conn.execute(text("""
            ALTER TABLE serial_numbers
            ADD COLUMN IF NOT EXISTS sale_id VARCHAR(36) NULL,
            ADD COLUMN IF NOT EXISTS invoice_id VARCHAR(36) NULL,
            ADD COLUMN IF NOT EXISTS receipt_id VARCHAR(36) NULL
        """))

        print("  ➜ Adding lifecycle timestamp columns...")
        conn.execute(text("""
            ALTER TABLE serial_numbers
            ADD COLUMN IF NOT EXISTS warranty_started_at DATE NULL,
            ADD COLUMN IF NOT EXISTS sold_at DATE NULL,
            ADD COLUMN IF NOT EXISTS returned_at DATE NULL
        """))

        # 3. Create indexes
        print("  ➜ Creating indexes...")
        conn.execute(text("""
            CREATE INDEX IF NOT EXISTS ix_serial_numbers_sale_id
            ON serial_numbers(sale_id)
        """))

        conn.execute(text("""
            CREATE INDEX IF NOT EXISTS ix_serial_numbers_invoice_id
            ON serial_numbers(invoice_id)
        """))

        conn.execute(text("""
            CREATE INDEX IF NOT EXISTS ix_serial_numbers_receipt_id
            ON serial_numbers(receipt_id)
        """))

        # 4. Add foreign keys
        print("  ➜ Adding foreign key constraints...")
        conn.execute(text("""
            DO $$
            BEGIN
                IF NOT EXISTS (
                    SELECT 1 FROM information_schema.table_constraints
                    WHERE constraint_name = 'serial_numbers_sale_id_fkey'
                ) THEN
                    ALTER TABLE serial_numbers
                    ADD CONSTRAINT serial_numbers_sale_id_fkey
                    FOREIGN KEY (sale_id) REFERENCES sales(id);
                END IF;
            END $$;
        """))

        conn.execute(text("""
            DO $$
            BEGIN
                IF NOT EXISTS (
                    SELECT 1 FROM information_schema.table_constraints
                    WHERE constraint_name = 'serial_numbers_invoice_id_fkey'
                ) THEN
                    ALTER TABLE serial_numbers
                    ADD CONSTRAINT serial_numbers_invoice_id_fkey
                    FOREIGN KEY (invoice_id) REFERENCES invoices(id);
                END IF;
            END $$;
        """))

        conn.execute(text("""
            DO $$
            BEGIN
                IF NOT EXISTS (
                    SELECT 1 FROM information_schema.table_constraints
                    WHERE constraint_name = 'serial_numbers_receipt_id_fkey'
                ) THEN
                    ALTER TABLE serial_numbers
                    ADD CONSTRAINT serial_numbers_receipt_id_fkey
                    FOREIGN KEY (receipt_id) REFERENCES receipts(id);
                END IF;
            END $$;
        """))

        conn.commit()

        # 5. Verify columns
        print("  ➜ Verifying schema...")
        result = conn.execute(text("""
            SELECT column_name, data_type, is_nullable
            FROM information_schema.columns
            WHERE table_name='serial_numbers'
            AND column_name IN ('sale_id', 'invoice_id', 'receipt_id',
                               'warranty_started_at', 'sold_at', 'returned_at')
            ORDER BY column_name
        """))

        columns = [(r[0], r[1], r[2]) for r in result]
        print("\n✅ Migration complete! Added columns:")
        for col_name, col_type, nullable in columns:
            print(f"   • {col_name} ({col_type}, nullable={nullable})")

if __name__ == "__main__":
    try:
        apply_migration()
    except Exception as e:
        print(f"❌ Migration failed: {e}")
        import traceback
        traceback.print_exc()
