"""
Manufacturing Variance Analysis & Batch Tracking Migration

This migration adds:
1. Variance tracking tables
2. Batch lot tracking fields
3. Quality management fields

Run with:
    python migrations/versions/add_manufacturing_variance_and_batch_tracking.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 manufacturing variance and batch tracking migration"""

    with engine.begin() as conn:
        try:
            # 1. Create manufacturing_cost_standards table
            logger.info("Creating manufacturing_cost_standards table...")
            conn.execute(text("""
                CREATE TABLE IF NOT EXISTS manufacturing_cost_standards (
                    id VARCHAR(36) PRIMARY KEY,
                    product_id VARCHAR(36) NOT NULL,
                    standard_material_cost FLOAT DEFAULT 0.0,
                    standard_labor_cost FLOAT DEFAULT 0.0,
                    standard_overhead_cost FLOAT DEFAULT 0.0,
                    effective_date DATE NOT NULL,
                    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 index separately for portability
            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_mfg_cost_std_product
                ON manufacturing_cost_standards(product_id)
            """))

            # 2. Create manufacturing_variance table
            logger.info("Creating manufacturing_variance table...")
            conn.execute(text("""
                CREATE TABLE IF NOT EXISTS manufacturing_variance (
                    id VARCHAR(36) PRIMARY KEY,
                    manufacturing_cost_id INTEGER NOT NULL,
                    product_id VARCHAR(36) NOT NULL,
                    batch_number VARCHAR(50) NOT NULL,
                    standard_cost FLOAT DEFAULT 0.0,
                    actual_cost FLOAT DEFAULT 0.0,
                    variance_amount FLOAT DEFAULT 0.0,
                    variance_percentage FLOAT DEFAULT 0.0,
                    material_variance FLOAT DEFAULT 0.0,
                    labor_variance FLOAT DEFAULT 0.0,
                    overhead_variance FLOAT DEFAULT 0.0,
                    variance_type VARCHAR(20),
                    notes TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (manufacturing_cost_id) REFERENCES manufacturing_costs(id) ON DELETE CASCADE,
                    FOREIGN KEY (product_id) REFERENCES products(id)
                )
            """))

            # Create indexes separately
            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_mfg_var_batch
                ON manufacturing_variance(batch_number)
            """))

            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_mfg_var_product
                ON manufacturing_variance(product_id)
            """))

            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_mfg_var_cost
                ON manufacturing_variance(manufacturing_cost_id)
            """))

            # 3. Add batch tracking fields to manufacturing_costs
            logger.info("Adding batch tracking fields to manufacturing_costs...")

            # Check if columns already exist and add them if not
            conn.execute(text("""
                ALTER TABLE manufacturing_costs
                ADD COLUMN IF NOT EXISTS batch_number VARCHAR(50),
                ADD COLUMN IF NOT EXISTS lot_start_date TIMESTAMP,
                ADD COLUMN IF NOT EXISTS lot_end_date TIMESTAMP,
                ADD COLUMN IF NOT EXISTS quality_check_status VARCHAR(50) DEFAULT 'pending',
                ADD COLUMN IF NOT EXISTS quality_notes TEXT,
                ADD COLUMN IF NOT EXISTS quality_checked_by VARCHAR(36),
                ADD COLUMN IF NOT EXISTS quality_check_date TIMESTAMP
            """))

            # Create index for batch_number
            conn.execute(text("""
                CREATE INDEX IF NOT EXISTS idx_mfg_cost_batch
                ON manufacturing_costs(batch_number)
            """))

            # 4. Add costing method config to app_settings
            logger.info("Adding manufacturing settings to app_settings...")
            conn.execute(text("""
                ALTER TABLE app_settings
                ADD COLUMN IF NOT EXISTS manufacturing_costing_method VARCHAR(50) DEFAULT 'actual',
                ADD COLUMN IF NOT EXISTS auto_sync_manufacturing_inventory BOOLEAN DEFAULT FALSE
            """))

            logger.info("✅ Manufacturing variance and batch tracking migration completed successfully!")

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


def rollback_migration():
    """Rollback the manufacturing variance and batch tracking migration"""

    with engine.begin() as conn:
        try:
            logger.info("Rolling back manufacturing variance and batch tracking migration...")

            # Drop indexes first
            conn.execute(text("DROP INDEX IF EXISTS idx_mfg_var_cost"))
            conn.execute(text("DROP INDEX IF EXISTS idx_mfg_var_product"))
            conn.execute(text("DROP INDEX IF EXISTS idx_mfg_var_batch"))
            conn.execute(text("DROP INDEX IF EXISTS idx_mfg_cost_std_product"))
            conn.execute(text("DROP INDEX IF EXISTS idx_mfg_cost_batch"))

            # Drop tables
            conn.execute(text("DROP TABLE IF EXISTS manufacturing_variance"))
            conn.execute(text("DROP TABLE IF EXISTS manufacturing_cost_standards"))

            # Drop columns from manufacturing_costs
            conn.execute(text("""
                ALTER TABLE manufacturing_costs
                DROP COLUMN IF EXISTS quality_check_date,
                DROP COLUMN IF EXISTS quality_checked_by,
                DROP COLUMN IF EXISTS quality_notes,
                DROP COLUMN IF EXISTS quality_check_status,
                DROP COLUMN IF EXISTS lot_end_date,
                DROP COLUMN IF EXISTS lot_start_date,
                DROP COLUMN IF EXISTS batch_number
            """))

            # Drop columns from app_settings
            conn.execute(text("""
                ALTER TABLE app_settings
                DROP COLUMN IF EXISTS auto_sync_manufacturing_inventory,
                DROP COLUMN IF EXISTS manufacturing_costing_method
            """))

            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()
