"""
Product Traceability System Migration

This migration adds:
1. Expiry date tracking for manufactured products
2. Manufacturing batch registry linking to serial numbers and sales
3. Complete audit trail for product lifecycle

Tables:
- product_batch_registry: Master batch tracking
- product_traceability_log: Full audit trail
- enhanced serial_numbers: Added expiry_date, manufactured_date, manufacturing_batch_id

Run with:
    python migrations/versions/add_product_traceability_system.py
"""
import sys
import os
sys.path.insert(0, os.path.dirname(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():
    """Run the product traceability system migration"""

    with engine.begin() as conn:
        try:
            # 1. Create product_batch_registry table
            logger.info("Creating product_batch_registry table...")
            conn.execute(text("""
                CREATE TABLE IF NOT EXISTS product_batch_registry (
                    id VARCHAR(36) PRIMARY KEY,
                    batch_number VARCHAR(50) NOT NULL UNIQUE,
                    product_id VARCHAR(36) NOT NULL,
                    manufacturing_date TIMESTAMP NOT NULL,
                    manufacture_site VARCHAR(255),
                    manufactured_quantity NUMERIC(15, 2) DEFAULT 0,
                    quality_status VARCHAR(50) DEFAULT 'pending',
                    expiry_date DATE,
                    storage_location VARCHAR(255),
                    storage_temperature VARCHAR(50),
                    storage_humidity_max NUMERIC(5, 2),
                    notes TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
                )
            """))

            # Create indexes for product_batch_registry
            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_batch_product
                ON product_batch_registry(batch_number, product_id)
            """))

            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_batch_date
                ON product_batch_registry(batch_number, manufacturing_date)
            """))

            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_batch_expiry
                ON product_batch_registry(expiry_date)
            """))

            # 2. Create product_traceability_log table (audit trail)
            logger.info("Creating product_traceability_log table...")
            conn.execute(text("""
                CREATE TABLE IF NOT EXISTS product_traceability_log (
                    id VARCHAR(36) PRIMARY KEY,
                    batch_number VARCHAR(50) NOT NULL,
                    serial_number VARCHAR(255),
                    event_type VARCHAR(50) NOT NULL,
                    event_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    product_id VARCHAR(36),
                    customer_id VARCHAR(36),
                    sale_id VARCHAR(36),
                    invoice_id VARCHAR(36),
                    location VARCHAR(255),
                    quantity NUMERIC(15, 2),
                    notes TEXT,
                    created_by VARCHAR(36),
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL,
                    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
                    FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE SET NULL,
                    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
                    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
                )
            """))

            # Create indexes for product_traceability_log
            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_trace_batch
                ON product_traceability_log(batch_number)
            """))

            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_trace_serial
                ON product_traceability_log(serial_number)
            """))

            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_trace_event_date
                ON product_traceability_log(event_date)
            """))

            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_trace_customer
                ON product_traceability_log(customer_id)
            """))
            # 3. Add fields to serial_numbers table
            logger.info("Enhancing serial_numbers table...")
            conn.execute(text("""
                ALTER TABLE serial_numbers
                ADD COLUMN IF NOT EXISTS expiry_date DATE,
                ADD COLUMN IF NOT EXISTS manufactured_date TIMESTAMP,
                ADD COLUMN IF NOT EXISTS manufacturing_batch_id VARCHAR(36),
                ADD COLUMN IF NOT EXISTS production_order_id VARCHAR(36),
                ADD COLUMN IF NOT EXISTS shelf_life_days INTEGER,
                ADD COLUMN IF NOT EXISTS storage_requirements TEXT,
                ADD COLUMN IF NOT EXISTS lot_code VARCHAR(100),
                ADD COLUMN IF NOT EXISTS quality_batch_number VARCHAR(100)
            """))

            # Create indexes for serial number enhancements
            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_serial_expiry
                ON serial_numbers(expiry_date)
            """))

            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_serial_manufacturing_batch
                ON serial_numbers(manufacturing_batch_id)
            """))

            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_serial_manufactured_date
                ON serial_numbers(manufactured_date)
            """))

            # 4. Create invoice_item_serials junction table (many-to-many)
            logger.info("Creating invoice_item_serials junction table...")
            conn.execute(text("""
                CREATE TABLE IF NOT EXISTS invoice_item_serials (
                    id VARCHAR(36) PRIMARY KEY,
                    invoice_item_id VARCHAR(36) NOT NULL,
                    serial_number_id VARCHAR(36) NOT NULL,
                    quantity NUMERIC(10, 2) DEFAULT 1,
                    batch_number VARCHAR(50),
                    expiry_date DATE,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (invoice_item_id) REFERENCES invoice_items(id) ON DELETE CASCADE,
                    FOREIGN KEY (serial_number_id) REFERENCES serial_numbers(id) ON DELETE CASCADE
                )
            """))

            # Create unique constraint for invoice_item_serials
            conn.execute(text("""
                CREATE UNIQUE INDEX IF NOT EXISTS unique_item_serial
                ON invoice_item_serials(invoice_item_id, serial_number_id)
            """))

            # Create indexes for invoice_item_serials
            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_iis_invoice_item
                ON invoice_item_serials(invoice_item_id)
            """))

            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_iis_serial_item
                ON invoice_item_serials(serial_number_id)
            """))

            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_iis_batch_item
                ON invoice_item_serials(batch_number)
            """))

            # 5. Add fields to invoice_items for batch/expiry tracking
            logger.info("Enhancing invoice_items table...")
            conn.execute(text("""
                ALTER TABLE invoice_items
                ADD COLUMN IF NOT EXISTS batch_number VARCHAR(50),
                ADD COLUMN IF NOT EXISTS expiry_date DATE,
                ADD COLUMN IF NOT EXISTS manufacturing_date TIMESTAMP
            """))

            # Create indexes for invoice item enhancements
            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_invoice_item_batch
                ON invoice_items(batch_number)
            """))

            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_invoice_item_expiry
                ON invoice_items(expiry_date)
            """))

            logger.info("✅ Product traceability system migration completed successfully!")

        except Exception as e:
            logger.error(f"❌ Migration failed: {e}")
            raise


def rollback_migration():
    """Rollback the product traceability system migration"""

    with engine.begin() as conn:
        try:
            logger.info("Rolling back product traceability system migration...")

            # Drop junction table
            conn.execute(text("DROP TABLE IF EXISTS invoice_item_serials"))

            # Drop main tables
            conn.execute(text("DROP TABLE IF EXISTS product_traceability_log"))
            conn.execute(text("DROP TABLE IF EXISTS product_batch_registry"))

            # Drop new columns from serial_numbers
            conn.execute(text("""
                ALTER TABLE serial_numbers
                DROP COLUMN IF EXISTS quality_batch_number,
                DROP COLUMN IF EXISTS lot_code,
                DROP COLUMN IF EXISTS storage_requirements,
                DROP COLUMN IF EXISTS shelf_life_days,
                DROP COLUMN IF EXISTS production_order_id,
                DROP COLUMN IF EXISTS manufacturing_batch_id,
                DROP COLUMN IF EXISTS manufactured_date,
                DROP COLUMN IF EXISTS expiry_date
            """))

            # Drop new columns from invoice_items
            conn.execute(text("""
                ALTER TABLE invoice_items
                DROP COLUMN IF EXISTS manufacturing_date,
                DROP COLUMN IF EXISTS expiry_date,
                DROP COLUMN IF EXISTS batch_number
            """))

            logger.info("✅ Rollback completed successfully!")

        except Exception as e:
            logger.error(f"❌ Rollback failed: {e}")
            raise


if __name__ == "__main__":
    if len(sys.argv) > 1 and sys.argv[1] == "rollback":
        rollback_migration()
    else:
        run_migration()
