# MyICR_Suite/app/modules/mediatrack/database/migrations/run_migration_002.py

"""
Migration 002: Add Serie conditional fields

Adds:
- formato, piattaforma columns to AnagraficaMedia
- delivery column to LogAggiornamenti
- Index on tipo for performance

Usage:
    python run_migration_002.py              # Usa environment corrente (DEV)
    python run_migration_002.py --env TEST   # Forza environment TEST
    python run_migration_002.py --env PROD   # Forza environment PROD
"""

import sqlite3
import sys
import os
from pathlib import Path

# Parse command line arguments PRIMA di importare moduli
target_env = None
if len(sys.argv) > 1:
    if sys.argv[1] == '--env' and len(sys.argv) > 2:
        target_env = sys.argv[2].lower()
        print(f"[MIGRATION] Target environment: {target_env.upper()}")
        # Override environment PRIMA di importare app.context
        os.environ['MYICR_ENV'] = target_env

# Fix PYTHONPATH per import
current_dir = Path(__file__).resolve().parent
mediatrack_dir = current_dir.parent.parent
myicr_suite_dir = mediatrack_dir.parent.parent.parent
if str(myicr_suite_dir) not in sys.path:
    sys.path.insert(0, str(myicr_suite_dir))

from app.modules.mediatrack.backend.database_logic import _conn


def run_migration():
    """Run migration 002: Add Serie conditional fields"""
    try:
        print("=" * 70)
        print("MIGRATION 002: Add Serie Conditional Fields")
        print("=" * 70)
        print()

        con = _conn()
        cur = con.cursor()

        # Read migration SQL file
        migration_file = current_dir / "002_add_serie_fields.sql"
        print(f"Reading migration from: {migration_file}")

        with open(migration_file, 'r', encoding='utf-8') as f:
            migration_sql = f.read()

        print("Executing migration statements...")
        print()

        # Execute each statement separately
        # Remove comments and split by semicolon
        statements = []
        for s in migration_sql.split(';'):
            # Remove comment lines and empty lines
            lines = [line for line in s.split('\n') if line.strip() and not line.strip().startswith('--')]
            if lines:
                statements.append('\n'.join(lines))

        for i, statement in enumerate(statements, 1):
            # Extract first line for logging (command type)
            first_line = statement.strip().split('\n')[0]
            print(f"  [{i}/{len(statements)}] {first_line}...")

            try:
                cur.execute(statement)
                print(f"       [OK] Success")
            except sqlite3.OperationalError as e:
                if "duplicate column name" in str(e).lower():
                    print(f"       [WARN] Column already exists (skipped)")
                else:
                    raise

        con.commit()
        print()
        print("=" * 70)
        print("VERIFICATION")
        print("=" * 70)

        # Verify AnagraficaMedia changes
        print("\nAnagraficaMedia table columns:")
        cur.execute("PRAGMA table_info(AnagraficaMedia)")
        cols = [row[1] for row in cur.fetchall()]

        for col in ['formato', 'piattaforma']:
            if col in cols:
                print(f"  [OK] {col} column found")
            else:
                raise AssertionError(f"Column '{col}' not found in AnagraficaMedia")

        # Verify LogAggiornamenti changes
        print("\nLogAggiornamenti table columns:")
        cur.execute("PRAGMA table_info(LogAggiornamenti)")
        cols = [row[1] for row in cur.fetchall()]

        if 'delivery' in cols:
            print(f"  [OK] delivery column found")
        else:
            raise AssertionError(f"Column 'delivery' not found in LogAggiornamenti")

        # Verify index
        print("\nIndices:")
        cur.execute("SELECT name FROM sqlite_master WHERE type='index' AND name='idx_anagrafica_tipo'")
        idx = cur.fetchone()
        if idx:
            print(f"  [OK] idx_anagrafica_tipo index found")
        else:
            print(f"  [WARN] idx_anagrafica_tipo index not found (may already exist)")

        print()
        print("=" * 70)
        print("MIGRATION 002 COMPLETED SUCCESSFULLY")
        print("=" * 70)

        con.close()
        return True

    except Exception as e:
        print()
        print("=" * 70)
        print(f"MIGRATION 002 FAILED: {e}")
        print("=" * 70)

        if 'con' in locals():
            con.rollback()
            con.close()

        import traceback
        traceback.print_exc()
        return False


if __name__ == "__main__":
    success = run_migration()
    sys.exit(0 if success else 1)