"""
Migration 005: Add UserExportPresets table

Adds UserExportPresets table for storing user-specific Excel export filter presets.

Usage:
    python run_migration_005.py              # Usa environment corrente (DEV)
    python run_migration_005.py --env TEST   # Forza environment TEST
    python run_migration_005.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 005: Add UserExportPresets table"""
    try:
        print("=" * 70)
        print("MIGRATION 005: Add UserExportPresets Table")
        print("=" * 70)
        print()

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

        # Read migration SQL file
        migration_file = current_dir / "005_add_user_export_presets.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 "already exists" in str(e).lower():
                    print(f"       [WARN] Already exists (skipped)")
                else:
                    raise

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

        # Verify UserExportPresets table exists
        print("\nUserExportPresets table:")
        cur.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='UserExportPresets'")
        if cur.fetchone():
            print("  [OK] UserExportPresets table created")
        else:
            raise AssertionError("UserExportPresets table not found")

        # Verify table structure
        cur.execute("PRAGMA table_info(UserExportPresets)")
        cols = [row[1] for row in cur.fetchall()]
        required_cols = ['id_preset', 'user_id', 'nome_preset', 'filtri_json',
                        'includi_gemma', 'is_default', 'data_creazione', 'data_ultimo_uso']

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

        # Verify indexes
        print("\nIndices:")
        cur.execute("SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='UserExportPresets'")
        indexes = [row[0] for row in cur.fetchall()]

        expected_indexes = ['idx_user_export_presets_user_id', 'idx_user_export_presets_default']
        for idx in expected_indexes:
            if idx in indexes:
                print(f"  [OK] {idx} index found")
            else:
                print(f"  [WARN] {idx} index not found (may already exist)")

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

        con.close()
        return True

    except Exception as e:
        print()
        print("=" * 70)
        print(f"MIGRATION 005 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)