"""
Database manager with SQLite support and automatic migrations
"""

import os
import json
import sqlite3
import logging
from pathlib import Path
from typing import Dict, Any, Optional, List
from datetime import datetime, timedelta
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.exc import SQLAlchemyError

from .models import (
    Base, DatabaseVersion, UserModel, ConfigurationModel, 
    ApiTokenModel, LogEntryModel, TemplateModel, SystemMetricModel, SessionModel
)
from .migrations import run_migrations

logger = logging.getLogger(__name__)


class DatabaseManager:
    """Manages SQLite database operations with versioning and migrations"""
    
    def __init__(self, db_path: str):
        self.db_path = Path(db_path)
        self.engine = None
        self.session_factory = None
        self.Session = None
        self._initialized = False
    
    def initialize(self) -> bool:
        """Initialize database connection and run migrations"""
        try:
            # Ensure database directory exists
            self.db_path.parent.mkdir(parents=True, exist_ok=True)

            # Create database URL
            db_url = f"sqlite:///{self.db_path}"

            # Create engine with proper SQLite configuration
            self.engine = create_engine(
                db_url,
                echo=False,
                pool_pre_ping=True,
                connect_args={
                    'check_same_thread': False,
                    'timeout': 30
                }
            )

            # Configure SQLite for better performance and reliability
            with self.engine.connect() as conn:
                conn.execute(text("PRAGMA journal_mode=WAL"))
                conn.execute(text("PRAGMA synchronous=NORMAL"))
                conn.execute(text("PRAGMA cache_size=10000"))
                conn.execute(text("PRAGMA temp_store=MEMORY"))
                conn.execute(text("PRAGMA mmap_size=268435456"))  # 256MB
                conn.commit()

            # Create session factory
            self.session_factory = sessionmaker(bind=self.engine)
            self.Session = scoped_session(self.session_factory)

            # Create all tables
            Base.metadata.create_all(self.engine)

            # Mark as initialized so migrations can use get_session()
            self._initialized = True

            # Run database migrations
            if not run_migrations(self):
                logger.error("Database migrations failed")
                self._initialized = False
                return False

            # Create default admin user if none exists
            self._create_default_admin()

            # Initialize default templates
            self._initialize_default_templates()

            logger.info("Database manager initialized successfully")
            return True

        except SQLAlchemyError as e:
            logger.warning(f"Failed to initialize database at {self.db_path}: {e}")
            # Try fallback locations if the primary location fails
            return self._initialize_with_fallback()
        except Exception as e:
            logger.error(f"Failed to initialize database manager: {e}")
            return False

    def _initialize_with_fallback(self) -> bool:
        """Try to initialize database in fallback locations"""
        fallback_paths = [
            # Try current working directory
            Path.cwd() / "mbetterclient.db",
            # Try user's home directory
            Path.home() / ".mbetterclient.db",
            # Try temp directory as last resort
            Path("/tmp") / "mbetterclient.db"
        ]

        for fallback_path in fallback_paths:
            try:
                logger.info(f"Trying fallback database location: {fallback_path}")

                # Ensure fallback directory exists
                fallback_path.parent.mkdir(parents=True, exist_ok=True)

                # Create database URL
                db_url = f"sqlite:///{fallback_path}"

                # Create engine with proper SQLite configuration
                self.engine = create_engine(
                    db_url,
                    echo=False,
                    pool_pre_ping=True,
                    connect_args={
                        'check_same_thread': False,
                        'timeout': 30
                    }
                )

                # Configure SQLite for better performance and reliability
                with self.engine.connect() as conn:
                    conn.execute(text("PRAGMA journal_mode=WAL"))
                    conn.execute(text("PRAGMA synchronous=NORMAL"))
                    conn.execute(text("PRAGMA cache_size=10000"))
                    conn.execute(text("PRAGMA temp_store=MEMORY"))
                    conn.execute(text("PRAGMA mmap_size=268435456"))  # 256MB
                    conn.commit()

                # Create session factory
                self.session_factory = sessionmaker(bind=self.engine)
                self.Session = scoped_session(self.session_factory)

                # Create all tables
                Base.metadata.create_all(self.engine)

                # Mark as initialized so migrations can use get_session()
                self._initialized = True

                # Update the db_path to the working fallback path
                self.db_path = fallback_path

                logger.warning(f"Using fallback database location: {fallback_path}")

                # Run database migrations
                if not run_migrations(self):
                    logger.error("Database migrations failed")
                    self._initialized = False
                    return False

                # Create default admin user if none exists
                self._create_default_admin()

                # Initialize default templates
                self._initialize_default_templates()

                logger.info("Database manager initialized successfully with fallback location")
                return True

            except Exception as e:
                logger.warning(f"Fallback location {fallback_path} also failed: {e}")
                continue

        logger.error("All database initialization attempts failed")
        return False
    
    def get_session(self):
        """Get database session"""
        if not self._initialized:
            raise RuntimeError("Database manager not initialized")
        return self.Session()
    
    def close(self):
        """Close database connections"""
        if self.Session:
            self.Session.remove()
        if self.engine:
            self.engine.dispose()
        logger.info("Database connections closed")
    
    def get_connection_status(self) -> Dict[str, Any]:
        """Get database connection status"""
        try:
            if not self._initialized or not self.engine:
                return {
                    "connected": False,
                    "error": "Database not initialized"
                }
            
            # Test connection
            with self.engine.connect() as conn:
                conn.execute(text("SELECT 1"))
            
            return {
                "connected": True,
                "database": str(self.db_path),
                "status": "healthy"
            }
            
        except Exception as e:
            logger.error(f"Database connection test failed: {e}")
            return {
                "connected": False,
                "error": str(e),
                "database": str(self.db_path) if self.db_path else "unknown"
            }
    
    def backup_database(self, backup_path: Optional[str] = None) -> bool:
        """Create database backup"""
        try:
            if backup_path is None:
                timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
                backup_path = f"{self.db_path}.backup_{timestamp}"
            
            # Use SQLite backup API for consistent backup
            with sqlite3.connect(str(self.db_path)) as source:
                with sqlite3.connect(backup_path) as backup:
                    source.backup(backup)
            
            logger.info(f"Database backed up to {backup_path}")
            return True
            
        except Exception as e:
            logger.error(f"Database backup failed: {e}")
            return False
    
    def restore_database(self, backup_path: str) -> bool:
        """Restore database from backup"""
        try:
            if not os.path.exists(backup_path):
                logger.error(f"Backup file not found: {backup_path}")
                return False
            
            # Close existing connections
            if self.Session:
                self.Session.remove()
            if self.engine:
                self.engine.dispose()
            
            # Replace current database with backup
            import shutil
            shutil.copy2(backup_path, self.db_path)
            
            # Reinitialize
            return self.initialize()
            
        except Exception as e:
            logger.error(f"Database restore failed: {e}")
            return False
    
    def vacuum_database(self) -> bool:
        """Vacuum database to optimize storage"""
        try:
            with self.engine.connect() as conn:
                conn.execute(text("VACUUM"))
                conn.commit()
            
            logger.info("Database vacuum completed")
            return True
            
        except Exception as e:
            logger.error(f"Database vacuum failed: {e}")
            return False
    
    # Configuration methods
    def get_configuration(self) -> Optional[Dict[str, Any]]:
        """Get application configuration from database"""
        try:
            session = self.get_session()
            
            config_entry = session.query(ConfigurationModel).filter_by(
                key='app_settings'
            ).first()
            
            if config_entry:
                return config_entry.get_typed_value()
            
            return None
            
        except Exception as e:
            logger.error(f"Failed to get configuration: {e}")
            return None
        finally:
            session.close()
    
    def save_configuration(self, config_data: Dict[str, Any]) -> bool:
        """Save application configuration to database"""
        try:
            session = self.get_session()
            
            config_entry = session.query(ConfigurationModel).filter_by(
                key='app_settings'
            ).first()
            
            if config_entry:
                config_entry.set_typed_value(config_data)
                config_entry.updated_at = datetime.utcnow()
            else:
                config_entry = ConfigurationModel(
                    key='app_settings',
                    description='Main application settings'
                )
                config_entry.set_typed_value(config_data)
                session.add(config_entry)
            
            session.commit()
            logger.debug("Configuration saved successfully")
            return True
            
        except Exception as e:
            logger.error(f"Failed to save configuration: {e}")
            session.rollback()
            return False
        finally:
            session.close()
    
    def get_config_value(self, key: str, default: Any = None) -> Any:
        """Get specific configuration value"""
        try:
            session = self.get_session()
            
            config_entry = session.query(ConfigurationModel).filter_by(key=key).first()
            
            if config_entry:
                return config_entry.get_typed_value()
            
            return default
            
        except Exception as e:
            logger.error(f"Failed to get config value '{key}': {e}")
            return default
        finally:
            session.close()
    
    def set_config_value(self, key: str, value: Any) -> bool:
        """Set specific configuration value"""
        try:
            session = self.get_session()
            
            config_entry = session.query(ConfigurationModel).filter_by(key=key).first()
            
            if config_entry:
                config_entry.set_typed_value(value)
                config_entry.updated_at = datetime.utcnow()
            else:
                config_entry = ConfigurationModel(key=key)
                config_entry.set_typed_value(value)
                session.add(config_entry)
            
            session.commit()
            return True
            
        except Exception as e:
            logger.error(f"Failed to set config value '{key}': {e}")
            session.rollback()
            return False
        finally:
            session.close()
    
    def delete_config_value(self, key: str) -> bool:
        """Delete configuration value"""
        try:
            session = self.get_session()
            
            config_entry = session.query(ConfigurationModel).filter_by(key=key).first()
            
            if config_entry:
                session.delete(config_entry)
                session.commit()
                return True
            
            return False
            
        except Exception as e:
            logger.error(f"Failed to delete config value '{key}': {e}")
            session.rollback()
            return False
        finally:
            session.close()
    
    def get_all_config_values(self) -> Dict[str, Any]:
        """Get all configuration values"""
        try:
            session = self.get_session()
            
            config_entries = session.query(ConfigurationModel).all()
            
            result = {}
            for entry in config_entries:
                if entry.key != 'app_settings':  # Skip main settings
                    result[entry.key] = entry.get_typed_value()
            
            return result
            
        except Exception as e:
            logger.error(f"Failed to get all config values: {e}")
            return {}
        finally:
            session.close()
    
    # User management methods
    def create_user(self, username: str, email: str, password: str, is_admin: bool = False, role: str = 'normal') -> Optional[Dict[str, Any]]:
        """Create new user"""
        try:
            session = self.get_session()
            
            # Check if user exists
            existing_user = session.query(UserModel).filter(
                (UserModel.username == username) | (UserModel.email == email)
            ).first()
            
            if existing_user:
                logger.warning(f"User already exists: {username} or {email}")
                return None
            
            # Create new user
            user = UserModel(
                username=username,
                email=email,
                is_admin=is_admin
            )
            user.set_password(password)
            
            # Set role (handle backward compatibility)
            if hasattr(user, 'set_role'):
                user.set_role(role)
            elif hasattr(user, 'role'):
                user.role = role
                user.is_admin = (role == 'admin')
            
            session.add(user)
            session.commit()
            
            # Extract data immediately before session closes
            user_data = {
                'id': user.id,
                'username': user.username,
                'email': user.email,
                'is_admin': user.is_admin,
                'role': getattr(user, 'role', 'normal'),
                'created_at': user.created_at,
                'updated_at': user.updated_at,
                'last_login': user.last_login
            }
            
            logger.info(f"User created: {username}")
            return user_data
            
        except Exception as e:
            logger.error(f"Failed to create user: {e}")
            session.rollback()
            return None
        finally:
            session.close()
    
    def get_user_by_username(self, username: str) -> Optional[UserModel]:
        """Get user by username"""
        try:
            session = self.get_session()
            return session.query(UserModel).filter_by(username=username).first()
        except Exception as e:
            logger.error(f"Failed to get user by username: {e}")
            return None
        finally:
            session.close()
    
    def get_user_by_id(self, user_id: int) -> Optional[UserModel]:
        """Get user by ID"""
        try:
            session = self.get_session()
            return session.query(UserModel).get(user_id)
        except Exception as e:
            logger.error(f"Failed to get user by ID: {e}")
            return None
        finally:
            session.close()
    
    def get_user_by_email(self, email: str) -> Optional[UserModel]:
        """Get user by email"""
        try:
            session = self.get_session()
            return session.query(UserModel).filter_by(email=email).first()
        except Exception as e:
            logger.error(f"Failed to get user by email: {e}")
            return None
        finally:
            session.close()
    
    def get_all_users(self) -> List[Dict[str, Any]]:
        """Get all users with extracted data"""
        try:
            session = self.get_session()
            users = session.query(UserModel).all()
            
            # Extract data immediately before session closes
            user_data = []
            for user in users:
                user_data.append({
                    'id': user.id,
                    'username': user.username,
                    'email': user.email,
                    'is_admin': user.is_admin,
                    'role': getattr(user, 'role', 'normal'),
                    'created_at': user.created_at,
                    'updated_at': user.updated_at,
                    'last_login': user.last_login
                })
            
            return user_data
            
        except Exception as e:
            logger.error(f"Failed to get all users: {e}")
            return []
        finally:
            session.close()
    
    def save_user(self, user: UserModel) -> Optional[Dict[str, Any]]:
        """Save user to database and return user data"""
        try:
            session = self.get_session()
            
            # Merge the user object to handle both new and existing users
            merged_user = session.merge(user)
            session.commit()
            
            # Extract data immediately before session closes
            user_data = {
                'id': merged_user.id,
                'username': merged_user.username,
                'email': merged_user.email,
                'is_admin': merged_user.is_admin,
                'role': getattr(merged_user, 'role', 'normal'),
                'created_at': merged_user.created_at,
                'updated_at': merged_user.updated_at,
                'last_login': merged_user.last_login
            }
            
            return user_data
            
        except Exception as e:
            logger.error(f"Failed to save user: {e}")
            session.rollback()
            return None
        finally:
            session.close()
    
    def save_api_token(self, token: ApiTokenModel) -> Optional[Dict[str, Any]]:
        """Save API token to database and return token data"""
        try:
            session = self.get_session()
            
            # Merge the token object to handle both new and existing tokens
            merged_token = session.merge(token)
            session.commit()
            
            # Extract data immediately before session closes
            token_data = {
                'id': merged_token.id,
                'name': merged_token.name,
                'user_id': merged_token.user_id,
                'expires_at': merged_token.expires_at,
                'created_at': merged_token.created_at,
                'is_active': merged_token.is_active
            }
            
            return token_data
            
        except Exception as e:
            logger.error(f"Failed to save API token: {e}")
            session.rollback()
            return None
        finally:
            session.close()
    
    def get_api_token_by_hash(self, token_hash: str) -> Optional[ApiTokenModel]:
        """Get API token by hash"""
        try:
            session = self.get_session()
            return session.query(ApiTokenModel).filter_by(token_hash=token_hash).first()
        except Exception as e:
            logger.error(f"Failed to get API token by hash: {e}")
            return None
        finally:
            session.close()
    
    def get_api_token_by_id(self, token_id: int) -> Optional[ApiTokenModel]:
        """Get API token by ID"""
        try:
            session = self.get_session()
            return session.query(ApiTokenModel).get(token_id)
        except Exception as e:
            logger.error(f"Failed to get API token by ID: {e}")
            return None
        finally:
            session.close()
    
    def get_user_api_tokens(self, user_id: int) -> List[ApiTokenModel]:
        """Get all API tokens for a user"""
        try:
            session = self.get_session()
            return session.query(ApiTokenModel).filter_by(user_id=user_id).all()
        except Exception as e:
            logger.error(f"Failed to get user API tokens: {e}")
            return []
        finally:
            session.close()
    
    def cleanup_expired_tokens(self) -> int:
        """Clean up expired API tokens"""
        try:
            session = self.get_session()
            
            cutoff_date = datetime.utcnow()
            
            deleted_count = session.query(ApiTokenModel).filter(
                ApiTokenModel.expires_at < cutoff_date
            ).delete()
            
            session.commit()
            
            if deleted_count > 0:
                logger.info(f"Cleaned up {deleted_count} expired tokens")
            
            return deleted_count
            
        except Exception as e:
            logger.error(f"Failed to cleanup expired tokens: {e}")
            session.rollback()
            return 0
        finally:
            session.close()
    
    def delete_api_token(self, token_id: int) -> bool:
        """Delete API token by ID"""
        try:
            session = self.get_session()
            
            token = session.query(ApiTokenModel).get(token_id)
            
            if token:
                session.delete(token)
                session.commit()
                logger.info(f"API token deleted: {token.name} (ID: {token_id})")
                return True
            else:
                logger.warning(f"API token not found for deletion: {token_id}")
                return False
            
        except Exception as e:
            logger.error(f"Failed to delete API token: {e}")
            session.rollback()
            return False
        finally:
            session.close()
    
    def delete_user(self, user_id: int) -> bool:
        """Delete user and all associated data"""
        try:
            session = self.get_session()
            
            user = session.query(UserModel).get(user_id)
            
            if user:
                # Prevent deletion of default admin user
                if user.username == 'admin':
                    logger.warning(f"Cannot delete default admin user: {user.username} (ID: {user_id})")
                    return False
                
                # Delete user's API tokens first
                session.query(ApiTokenModel).filter_by(user_id=user_id).delete()
                
                # Delete user
                session.delete(user)
                session.commit()
                logger.info(f"User deleted: {user.username} (ID: {user_id})")
                return True
            else:
                logger.warning(f"User not found for deletion: {user_id}")
                return False
            
        except Exception as e:
            logger.error(f"Failed to delete user: {e}")
            session.rollback()
            return False
        finally:
            session.close()
    
    # Logging methods
    def add_log_entry(self, level: str, component: str, message: str, 
                     details: Optional[Dict[str, Any]] = None, user_id: Optional[int] = None,
                     session_id: Optional[str] = None, ip_address: Optional[str] = None) -> bool:
        """Add log entry to database"""
        try:
            session = self.get_session()
            
            log_entry = LogEntryModel(
                level=level,
                component=component,
                message=message,
                details=details,
                user_id=user_id,
                session_id=session_id,
                ip_address=ip_address
            )
            
            session.add(log_entry)
            session.commit()
            
            return True
            
        except Exception as e:
            logger.error(f"Failed to add log entry: {e}")
            session.rollback()
            return False
        finally:
            session.close()
    
    def get_log_entries(self, level: Optional[str] = None, component: Optional[str] = None,
                       limit: int = 100, offset: int = 0) -> List[LogEntryModel]:
        """Get log entries with filtering"""
        try:
            session = self.get_session()
            
            query = session.query(LogEntryModel)
            
            if level:
                query = query.filter_by(level=level)
            
            if component:
                query = query.filter_by(component=component)
            
            query = query.order_by(LogEntryModel.created_at.desc())
            query = query.limit(limit).offset(offset)
            
            return query.all()
            
        except Exception as e:
            logger.error(f"Failed to get log entries: {e}")
            return []
        finally:
            session.close()
    
    def cleanup_old_logs(self, days: int = 30) -> int:
        """Clean up old log entries"""
        try:
            session = self.get_session()
            
            cutoff_date = datetime.utcnow() - timedelta(days=days)
            
            deleted_count = session.query(LogEntryModel).filter(
                LogEntryModel.created_at < cutoff_date
            ).delete()
            
            session.commit()
            
            if deleted_count > 0:
                logger.info(f"Cleaned up {deleted_count} old log entries")
            
            return deleted_count
            
        except Exception as e:
            logger.error(f"Failed to cleanup old logs: {e}")
            session.rollback()
            return 0
        finally:
            session.close()
    
    def _create_default_admin(self):
        """Create default admin and cashier users if they don't exist
        
        Note: This method primarily serves as a fallback. The preferred method
        for creating default users is through database migrations (Migration_005 and Migration_007).
        This method only creates users if migrations haven't already created them.
        """
        try:
            session = self.get_session()
            
            # Check if admin user already exists by username (more specific than is_admin check)
            admin_user = session.query(UserModel).filter_by(username='admin').first()
            
            if not admin_user:
                # Only create if no admin user exists at all
                any_admin = session.query(UserModel).filter_by(is_admin=True).first()
                
                if not any_admin:
                    # Create default admin - migrations should handle this, but fallback just in case
                    admin = UserModel(
                        username='admin',
                        email='admin@mbetterclient.local',
                        is_admin=True
                    )
                    admin.set_password('admin123')
                    
                    # Set admin role (handle backward compatibility)
                    if hasattr(admin, 'set_role'):
                        admin.set_role('admin')
                    elif hasattr(admin, 'role'):
                        admin.role = 'admin'
                    
                    session.add(admin)
                    logger.info("Default admin user created via fallback method (admin/admin123)")
                else:
                    logger.info("Admin users exist, skipping default admin creation")
            else:
                logger.info("Admin user 'admin' already exists, skipping creation")
            
            # Check if default cashier exists (this should be handled by Migration_007)
            cashier_user = session.query(UserModel).filter_by(username='cashier').first()
            
            if not cashier_user:
                # Create default cashier - migrations should handle this, but fallback just in case
                cashier = UserModel(
                    username='cashier',
                    email='cashier@mbetterclient.local',
                    is_admin=False
                )
                cashier.set_password('cashier123')
                
                # Set cashier role (handle backward compatibility)
                if hasattr(cashier, 'set_role'):
                    cashier.set_role('cashier')
                elif hasattr(cashier, 'role'):
                    cashier.role = 'cashier'
                
                session.add(cashier)
                logger.info("Default cashier user created via fallback method (cashier/cashier123)")
            else:
                logger.info("Cashier user 'cashier' already exists, skipping creation")
            
            session.commit()
            
        except Exception as e:
            logger.error(f"Failed to create default users: {e}")
            session.rollback()
        finally:
            session.close()
    
    def _initialize_default_templates(self):
        """Initialize default video overlay templates"""
        try:
            session = self.get_session()
            
            # Check if default template exists
            existing_template = session.query(TemplateModel).filter_by(
                name='news_template', is_system=True
            ).first()
            
            if not existing_template:
                # Create default news template
                news_template = TemplateModel(
                    name='news_template',
                    display_name='News Template',
                    description='Default news-style overlay template',
                    is_system=True,
                    category='news',
                    author='MbetterClient',
                    template_data={
                        'layout': {
                            'width': 1920,
                            'height': 1080
                        },
                        'elements': [
                            {
                                'id': 'news_bar',
                                'type': 'rectangle',
                                'x': 0,
                                'y': 950,
                                'width': 1920,
                                'height': 130,
                                'color': '#1a1a1a',
                                'opacity': 0.9
                            },
                            {
                                'id': 'athlete_image',
                                'type': 'image',
                                'x': 50,
                                'y': 960,
                                'width': 110,
                                'height': 110,
                                'source': 'assets/boxing_athletes.png',
                                'fit': 'cover'
                            },
                            {
                                'id': 'scrolling_text',
                                'type': 'text',
                                'x': 180,
                                'y': 990,
                                'width': 1500,
                                'height': 50,
                                'text': 'Breaking News: Boxing Match Updates',
                                'font_family': 'Arial',
                                'font_size': 32,
                                'font_weight': 'bold',
                                'color': '#ffffff',
                                'animation': {
                                    'type': 'scroll',
                                    'direction': 'left',
                                    'speed': 100
                                }
                            }
                        ]
                    }
                )
                
                session.add(news_template)
                session.commit()
                
                logger.info("Default news template created")
            
        except Exception as e:
            logger.error(f"Failed to initialize default templates: {e}")
            session.rollback()
        finally:
            session.close()