"""
Database migration system for MbetterClient
"""

import logging
from typing import List, Dict, Any, Optional
from datetime import datetime
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError

from .models import DatabaseVersion

logger = logging.getLogger(__name__)


class DatabaseMigration:
    """Base class for database migrations"""
    
    def __init__(self, version: str, description: str):
        self.version = version
        self.description = description
    
    def up(self, db_manager) -> bool:
        """Apply migration"""
        raise NotImplementedError("Migration must implement up() method")
    
    def down(self, db_manager) -> bool:
        """Rollback migration"""
        raise NotImplementedError("Migration must implement down() method")
    
    def __str__(self):
        return f"Migration {self.version}: {self.description}"


class Migration_001_InitialSchema(DatabaseMigration):
    """Initial database schema"""
    
    def __init__(self):
        super().__init__("001", "Initial database schema")
    
    def up(self, db_manager) -> bool:
        """Create initial schema - handled by SQLAlchemy create_all"""
        try:
            # Schema is created by SQLAlchemy Base.metadata.create_all()
            logger.info("Initial schema created by SQLAlchemy")
            return True
        except Exception as e:
            logger.error(f"Failed to create initial schema: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Drop all tables"""
        try:
            with db_manager.engine.connect() as conn:
                # Get all table names
                result = conn.execute(text("""
                    SELECT name FROM sqlite_master 
                    WHERE type='table' AND name NOT LIKE 'sqlite_%'
                """))
                tables = [row[0] for row in result.fetchall()]
                
                # Drop all tables
                for table in tables:
                    conn.execute(text(f"DROP TABLE IF EXISTS {table}"))
                
                conn.commit()
                
            logger.info("All tables dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop tables: {e}")
            return False


class Migration_002_AddIndexes(DatabaseMigration):
    """Add performance indexes"""
    
    def __init__(self):
        super().__init__("002", "Add performance indexes")
    
    def up(self, db_manager) -> bool:
        """Add indexes for better query performance"""
        try:
            with db_manager.engine.connect() as conn:
                # Additional indexes for better performance
                indexes = [
                    "CREATE INDEX IF NOT EXISTS ix_log_entries_user_component ON log_entries(user_id, component)",
                    "CREATE INDEX IF NOT EXISTS ix_api_tokens_user_active ON api_tokens(user_id, is_active)",
                    "CREATE INDEX IF NOT EXISTS ix_sessions_user_active ON sessions(user_id, is_active)",
                    "CREATE INDEX IF NOT EXISTS ix_system_metrics_component_name ON system_metrics(component, metric_name)",
                ]
                
                for index_sql in indexes:
                    conn.execute(text(index_sql))
                
                conn.commit()
                
            logger.info("Performance indexes added")
            return True
        except Exception as e:
            logger.error(f"Failed to add indexes: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Remove added indexes"""
        try:
            with db_manager.engine.connect() as conn:
                indexes = [
                    "DROP INDEX IF EXISTS ix_log_entries_user_component",
                    "DROP INDEX IF EXISTS ix_api_tokens_user_active",
                    "DROP INDEX IF EXISTS ix_sessions_user_active", 
                    "DROP INDEX IF EXISTS ix_system_metrics_component_name",
                ]
                
                for index_sql in indexes:
                    conn.execute(text(index_sql))
                
                conn.commit()
                
            logger.info("Performance indexes removed")
            return True
        except Exception as e:
            logger.error(f"Failed to remove indexes: {e}")
            return False


class Migration_003_AddTemplateVersioning(DatabaseMigration):
    """Add template versioning support"""
    
    def __init__(self):
        super().__init__("003", "Add template versioning support")
    
    def up(self, db_manager) -> bool:
        """Add versioning columns to templates"""
        try:
            with db_manager.engine.connect() as conn:
                # Check if columns already exist
                result = conn.execute(text("PRAGMA table_info(templates)"))
                columns = [row[1] for row in result.fetchall()]
                
                if 'parent_template_id' not in columns:
                    conn.execute(text("""
                        ALTER TABLE templates 
                        ADD COLUMN parent_template_id INTEGER 
                        REFERENCES templates(id)
                    """))
                
                if 'version_major' not in columns:
                    conn.execute(text("ALTER TABLE templates ADD COLUMN version_major INTEGER DEFAULT 1"))
                
                if 'version_minor' not in columns:
                    conn.execute(text("ALTER TABLE templates ADD COLUMN version_minor INTEGER DEFAULT 0"))
                
                if 'version_patch' not in columns:
                    conn.execute(text("ALTER TABLE templates ADD COLUMN version_patch INTEGER DEFAULT 0"))
                
                conn.commit()
                
            logger.info("Template versioning columns added")
            return True
        except Exception as e:
            logger.error(f"Failed to add template versioning: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Remove versioning columns - SQLite doesn't support DROP COLUMN easily"""
        logger.warning("SQLite doesn't support DROP COLUMN - versioning columns will remain")
        return True


class Migration_004_AddUserPreferences(DatabaseMigration):
    """Add user preferences storage"""
    
    def __init__(self):
        super().__init__("004", "Add user preferences storage")
    
    def up(self, db_manager) -> bool:
        """Create user preferences table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("""
                    CREATE TABLE IF NOT EXISTS user_preferences (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                        preference_key VARCHAR(100) NOT NULL,
                        preference_value TEXT NOT NULL,
                        value_type VARCHAR(20) DEFAULT 'string',
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        UNIQUE(user_id, preference_key)
                    )
                """))
                
                # Add indexes
                conn.execute(text("""
                    CREATE INDEX IF NOT EXISTS ix_user_preferences_user_key 
                    ON user_preferences(user_id, preference_key)
                """))
                
                conn.commit()
                
            logger.info("User preferences table created")
            return True
        except Exception as e:
            logger.error(f"Failed to create user preferences table: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Drop user preferences table"""
        try:
            with db_manager.engine.connect() as conn:
                conn.execute(text("DROP TABLE IF EXISTS user_preferences"))
                conn.commit()
                
            logger.info("User preferences table dropped")
            return True
        except Exception as e:
            logger.error(f"Failed to drop user preferences table: {e}")
            return False


class Migration_005_CreateDefaultAdminUser(DatabaseMigration):
    """Create default admin user for initial login"""
    
    def __init__(self):
        super().__init__("005", "Create default admin user")
    
    def up(self, db_manager) -> bool:
        """Create default admin user"""
        try:
            # Check if any users exist and create admin if needed
            with db_manager.engine.connect() as conn:
                result = conn.execute(text("SELECT COUNT(*) FROM users"))
                user_count = result.scalar()
                
                if user_count == 0:
                    # No users exist, create default admin
                    import hashlib
                    import secrets
                    
                    username = "admin"
                    email = "admin@mbetterclient.local"
                    password = "admin123"  # User should change this immediately
                    
                    # Use AuthManager's password hashing method (SHA-256 + salt)
                    # This matches what authenticate_user expects
                    salt = secrets.token_hex(16)
                    password_hash = hashlib.sha256((password + salt).encode()).hexdigest()
                    stored_hash = f"{salt}:{password_hash}"
                    
                    # Insert admin user using raw SQL (consistent with other migrations)
                    # Include all NOT NULL columns with proper defaults
                    conn.execute(text("""
                        INSERT INTO users
                        (username, email, password_hash, is_active, is_admin, login_attempts, created_at, updated_at)
                        VALUES (:username, :email, :password_hash, 1, 1, 0, datetime('now'), datetime('now'))
                    """), {
                        'username': username,
                        'email': email,
                        'password_hash': stored_hash
                    })
                    
                    conn.commit()
                    
                    logger.info(f"Default admin user created - Username: {username}, Password: {password}")
                    logger.warning("SECURITY: Please change the default admin password immediately!")
                else:
                    logger.info(f"Users already exist ({user_count}), skipping default admin creation")
                
            return True
        except Exception as e:
            logger.error(f"Failed to create default admin user: {e}")
            return False
    
    def down(self, db_manager) -> bool:
        """Remove default admin user if it still exists with default credentials"""
        try:
            with db_manager.engine.connect() as conn:
                # Only remove if username is 'admin' and email is the default
                conn.execute(text("""
                    DELETE FROM users
                    WHERE username = 'admin'
                    AND email = 'admin@mbetterclient.local'
                """))
                
                conn.commit()
                
            logger.info("Default admin user removed")
            return True
        except Exception as e:
            logger.error(f"Failed to remove default admin user: {e}")
            return False


# Registry of all migrations in order
MIGRATIONS: List[DatabaseMigration] = [
    Migration_001_InitialSchema(),
    Migration_002_AddIndexes(),
    Migration_003_AddTemplateVersioning(),
    Migration_004_AddUserPreferences(),
    Migration_005_CreateDefaultAdminUser(),
]


def get_applied_migrations(db_manager) -> List[str]:
    """Get list of applied migration versions"""
    session = None
    try:
        session = db_manager.get_session()
        
        versions = session.query(DatabaseVersion.version).all()
        return [v[0] for v in versions]
        
    except Exception as e:
        logger.error(f"Failed to get applied migrations: {e}")
        return []
    finally:
        if session:
            session.close()


def mark_migration_applied(db_manager, migration: DatabaseMigration) -> bool:
    """Mark migration as applied"""
    session = None
    try:
        session = db_manager.get_session()
        
        # Check if already applied
        existing = session.query(DatabaseVersion).filter_by(version=migration.version).first()
        
        if not existing:
            db_version = DatabaseVersion(
                version=migration.version,
                description=migration.description,
                applied_at=datetime.utcnow()
            )
            
            session.add(db_version)
            session.commit()
            
            logger.info(f"Migration {migration.version} marked as applied")
        
        return True
        
    except Exception as e:
        logger.error(f"Failed to mark migration as applied: {e}")
        if session:
            session.rollback()
        return False
    finally:
        if session:
            session.close()


def unmark_migration_applied(db_manager, version: str) -> bool:
    """Remove migration from applied list"""
    session = None
    try:
        session = db_manager.get_session()
        
        migration = session.query(DatabaseVersion).filter_by(version=version).first()
        
        if migration:
            session.delete(migration)
            session.commit()
            
            logger.info(f"Migration {version} unmarked as applied")
        
        return True
        
    except Exception as e:
        logger.error(f"Failed to unmark migration: {e}")
        if session:
            session.rollback()
        return False
    finally:
        if session:
            session.close()


def run_migrations(db_manager) -> bool:
    """Run all pending migrations"""
    try:
        applied_versions = get_applied_migrations(db_manager)
        
        logger.info(f"Applied migrations: {applied_versions}")
        
        success = True
        applied_count = 0
        
        for migration in MIGRATIONS:
            if migration.version not in applied_versions:
                logger.info(f"Applying migration: {migration}")
                
                if migration.up(db_manager):
                    if mark_migration_applied(db_manager, migration):
                        applied_count += 1
                        logger.info(f"Migration {migration.version} applied successfully")
                    else:
                        logger.error(f"Failed to mark migration {migration.version} as applied")
                        success = False
                        break
                else:
                    logger.error(f"Migration {migration.version} failed")
                    success = False
                    break
            else:
                logger.debug(f"Migration {migration.version} already applied")
        
        if success:
            if applied_count > 0:
                logger.info(f"Successfully applied {applied_count} migrations")
            else:
                logger.info("All migrations up to date")
        else:
            logger.error("Migration process failed")
        
        return success
        
    except Exception as e:
        logger.error(f"Migration process failed: {e}")
        return False


def rollback_migration(db_manager, version: str) -> bool:
    """Rollback a specific migration"""
    try:
        # Find the migration
        migration = None
        for m in MIGRATIONS:
            if m.version == version:
                migration = m
                break
        
        if not migration:
            logger.error(f"Migration {version} not found")
            return False
        
        # Check if it's applied
        applied_versions = get_applied_migrations(db_manager)
        
        if version not in applied_versions:
            logger.error(f"Migration {version} is not applied")
            return False
        
        logger.info(f"Rolling back migration: {migration}")
        
        if migration.down(db_manager):
            if unmark_migration_applied(db_manager, version):
                logger.info(f"Migration {version} rolled back successfully")
                return True
            else:
                logger.error(f"Failed to unmark migration {version}")
                return False
        else:
            logger.error(f"Migration {version} rollback failed")
            return False
        
    except Exception as e:
        logger.error(f"Migration rollback failed: {e}")
        return False


def get_migration_status(db_manager) -> Dict[str, Any]:
    """Get current migration status"""
    try:
        applied_versions = get_applied_migrations(db_manager)
        
        status = {
            'total_migrations': len(MIGRATIONS),
            'applied_migrations': len(applied_versions),
            'pending_migrations': len(MIGRATIONS) - len(applied_versions),
            'applied_versions': applied_versions,
            'pending_versions': [],
            'migrations': []
        }
        
        for migration in MIGRATIONS:
            is_applied = migration.version in applied_versions
            
            migration_info = {
                'version': migration.version,
                'description': migration.description,
                'applied': is_applied,
                'applied_at': None
            }
            
            if is_applied:
                # Get applied date
                session = db_manager.get_session()
                try:
                    db_version = session.query(DatabaseVersion).filter_by(
                        version=migration.version
                    ).first()
                    if db_version:
                        migration_info['applied_at'] = db_version.applied_at.isoformat()
                finally:
                    session.close()
            else:
                status['pending_versions'].append(migration.version)
            
            status['migrations'].append(migration_info)
        
        return status
        
    except Exception as e:
        logger.error(f"Failed to get migration status: {e}")
        return {
            'error': str(e),
            'total_migrations': 0,
            'applied_migrations': 0,
            'pending_migrations': 0
        }