"""
Add purchase_item_id to serial_numbers

Fixes the serial number fan-out bug where multiple line items for the
same product on a single purchase all received the full set of serials.
The new column links each SerialNumber directly to the specific
PurchaseItem it belongs to, so grouping in get_purchase_by_id is now
per-item rather than per-product.

Legacy rows (purchase_item_id IS NULL) continue to work via the
product_id fallback in get_purchase_by_id.

Run with:
    python migrations/add_purchase_item_id_to_serial_numbers.py
"""
import sys
import os
sys.path.insert(0, os.path.dirname(os.path.dirname(__file__)))

from sqlalchemy import text
from app.core.database import engine
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)


def run_migration():
    with engine.begin() as conn:
        # Add column (safe to run multiple times — IF NOT EXISTS guard)
        logger.info("Adding purchase_item_id column to serial_numbers...")
        conn.execute(text("""
            ALTER TABLE serial_numbers
            ADD COLUMN IF NOT EXISTS purchase_item_id VARCHAR(36)
            REFERENCES purchase_items(id) ON DELETE SET NULL
        """))

        logger.info("Creating index on serial_numbers.purchase_item_id...")
        conn.execute(text("""
            CREATE INDEX IF NOT EXISTS ix_serial_numbers_purchase_item_id
            ON serial_numbers (purchase_item_id)
        """))

        logger.info("Migration complete: purchase_item_id added to serial_numbers.")


if __name__ == "__main__":
    run_migration()
