"""
SQLAlchemy database models for MbetterClient
"""

import json
import hashlib
from datetime import datetime, timedelta
from typing import Dict, Any, Optional, List
from sqlalchemy import (
    Column, Integer, String, Text, DateTime, Boolean, Float, 
    JSON, ForeignKey, UniqueConstraint, Index, create_engine
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
from werkzeug.security import generate_password_hash, check_password_hash

Base = declarative_base()


class BaseModel(Base):
    """Base model with common fields"""
    __abstract__ = True
    
    id = Column(Integer, primary_key=True)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)
    
    def to_dict(self, exclude_fields: Optional[List[str]] = None) -> Dict[str, Any]:
        """Convert model to dictionary"""
        exclude_fields = exclude_fields or []
        result = {}
        
        for column in self.__table__.columns:
            if column.name not in exclude_fields:
                value = getattr(self, column.name)
                if isinstance(value, datetime):
                    result[column.name] = value.isoformat()
                else:
                    result[column.name] = value
        
        return result
    
    def update_from_dict(self, data: Dict[str, Any], exclude_fields: Optional[List[str]] = None):
        """Update model from dictionary"""
        exclude_fields = exclude_fields or ['id', 'created_at']
        
        for key, value in data.items():
            if hasattr(self, key) and key not in exclude_fields:
                setattr(self, key, value)
        
        self.updated_at = datetime.utcnow()


class DatabaseVersion(BaseModel):
    """Database schema version tracking"""
    __tablename__ = 'database_versions'
    
    version = Column(String(50), nullable=False, unique=True)
    description = Column(String(255))
    applied_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    
    def __repr__(self):
        return f'<DatabaseVersion {self.version}>'


class UserModel(BaseModel):
    """User authentication and management"""
    __tablename__ = 'users'
    __table_args__ = (
        Index('ix_users_username', 'username'),
        Index('ix_users_email', 'email'),
    )
    
    username = Column(String(80), unique=True, nullable=False)
    email = Column(String(120), unique=True, nullable=False)
    password_hash = Column(String(255), nullable=False)
    is_active = Column(Boolean, default=True, nullable=False)
    is_admin = Column(Boolean, default=False, nullable=False)
    last_login = Column(DateTime)
    login_attempts = Column(Integer, default=0, nullable=False)
    locked_until = Column(DateTime)
    
    # Relationships
    api_tokens = relationship('ApiTokenModel', back_populates='user', cascade='all, delete-orphan')
    log_entries = relationship('LogEntryModel', back_populates='user')
    
    def set_password(self, password: str):
        """Set password hash"""
        self.password_hash = generate_password_hash(password)
    
    def check_password(self, password: str) -> bool:
        """Check password against hash"""
        return check_password_hash(self.password_hash, password)
    
    def is_locked(self) -> bool:
        """Check if account is locked"""
        if self.locked_until is None:
            return False
        return datetime.utcnow() < self.locked_until
    
    def lock_account(self, minutes: int = 15):
        """Lock account for specified minutes"""
        self.locked_until = datetime.utcnow() + timedelta(minutes=minutes)
        self.login_attempts = 0
    
    def unlock_account(self):
        """Unlock account"""
        self.locked_until = None
        self.login_attempts = 0
    
    def increment_login_attempts(self):
        """Increment failed login attempts"""
        self.login_attempts += 1
    
    def reset_login_attempts(self):
        """Reset login attempts on successful login"""
        self.login_attempts = 0
        self.last_login = datetime.utcnow()
    
    def to_dict(self, exclude_fields: Optional[List[str]] = None) -> Dict[str, Any]:
        """Convert to dictionary, excluding sensitive data"""
        if exclude_fields is None:
            exclude_fields = ['password_hash']
        else:
            exclude_fields.append('password_hash')
        
        return super().to_dict(exclude_fields)
    
    def __repr__(self):
        return f'<User {self.username}>'


class ConfigurationModel(BaseModel):
    """Application configuration storage"""
    __tablename__ = 'configuration'
    __table_args__ = (
        Index('ix_configuration_key', 'key'),
    )
    
    key = Column(String(255), unique=True, nullable=False)
    value = Column(Text, nullable=False)
    value_type = Column(String(50), default='string', nullable=False)  # string, json, int, float, bool
    description = Column(String(500))
    is_system = Column(Boolean, default=False, nullable=False)  # System vs user configurable
    
    def get_typed_value(self) -> Any:
        """Get value converted to proper type"""
        if self.value_type == 'json':
            try:
                return json.loads(self.value)
            except json.JSONDecodeError:
                return None
        elif self.value_type == 'int':
            try:
                return int(self.value)
            except (ValueError, TypeError):
                return 0
        elif self.value_type == 'float':
            try:
                return float(self.value)
            except (ValueError, TypeError):
                return 0.0
        elif self.value_type == 'bool':
            return self.value.lower() in ('true', '1', 'yes', 'on')
        else:
            return self.value
    
    def set_typed_value(self, value: Any):
        """Set value with automatic type detection"""
        if isinstance(value, dict) or isinstance(value, list):
            self.value = json.dumps(value)
            self.value_type = 'json'
        elif isinstance(value, bool):
            self.value = str(value).lower()
            self.value_type = 'bool'
        elif isinstance(value, int):
            self.value = str(value)
            self.value_type = 'int'
        elif isinstance(value, float):
            self.value = str(value)
            self.value_type = 'float'
        else:
            self.value = str(value)
            self.value_type = 'string'
    
    def __repr__(self):
        return f'<Configuration {self.key}={self.value}>'


class ApiTokenModel(BaseModel):
    """API tokens for dashboard authentication"""
    __tablename__ = 'api_tokens'
    __table_args__ = (
        Index('ix_api_tokens_token_hash', 'token_hash'),
        Index('ix_api_tokens_user_id', 'user_id'),
    )
    
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    name = Column(String(255), nullable=False)
    token_hash = Column(String(255), nullable=False, unique=True)
    is_active = Column(Boolean, default=True, nullable=False)
    expires_at = Column(DateTime, nullable=False)
    last_used_at = Column(DateTime)
    last_used_ip = Column(String(45))
    permissions = Column(JSON, default=list)  # List of permission strings
    
    # Relationships
    user = relationship('UserModel', back_populates='api_tokens')
    
    @staticmethod
    def hash_token(token: str) -> str:
        """Hash token for secure storage"""
        return hashlib.sha256(token.encode('utf-8')).hexdigest()
    
    @staticmethod
    def verify_token(token: str, token_hash: str) -> bool:
        """Verify token against hash"""
        return ApiTokenModel.hash_token(token) == token_hash
    
    def is_expired(self) -> bool:
        """Check if token is expired"""
        return datetime.utcnow() > self.expires_at
    
    def is_valid(self) -> bool:
        """Check if token is valid"""
        return self.is_active and not self.is_expired()
    
    def update_last_used(self, ip_address: Optional[str] = None):
        """Update last used timestamp and IP"""
        self.last_used_at = datetime.utcnow()
        if ip_address:
            self.last_used_ip = ip_address
    
    def revoke(self):
        """Revoke token"""
        self.is_active = False
    
    def extend_expiration(self, days: int = 365):
        """Extend token expiration"""
        self.expires_at = datetime.utcnow() + timedelta(days=days)
    
    def has_permission(self, permission: str) -> bool:
        """Check if token has specific permission"""
        if not self.permissions:
            return False
        return permission in self.permissions
    
    def to_dict(self, exclude_fields: Optional[List[str]] = None) -> Dict[str, Any]:
        """Convert to dictionary, excluding token hash"""
        if exclude_fields is None:
            exclude_fields = ['token_hash']
        else:
            exclude_fields.append('token_hash')
        
        result = super().to_dict(exclude_fields)
        result['is_expired'] = self.is_expired()
        result['is_valid'] = self.is_valid()
        return result
    
    def __repr__(self):
        return f'<ApiToken {self.name} for User {self.user_id}>'


class LogEntryModel(BaseModel):
    """System logging"""
    __tablename__ = 'log_entries'
    __table_args__ = (
        Index('ix_log_entries_level', 'level'),
        Index('ix_log_entries_component', 'component'),
        Index('ix_log_entries_created_at', 'created_at'),
    )
    
    level = Column(String(20), nullable=False)  # DEBUG, INFO, WARNING, ERROR, CRITICAL
    component = Column(String(100), nullable=False)  # qt_player, web_dashboard, api_client, core
    message = Column(Text, nullable=False)
    details = Column(JSON)  # Additional structured data
    
    # Optional associations
    user_id = Column(Integer, ForeignKey('users.id'))
    session_id = Column(String(255))
    ip_address = Column(String(45))
    
    # Relationships
    user = relationship('UserModel', back_populates='log_entries')
    
    def __repr__(self):
        return f'<LogEntry {self.level}: {self.message[:50]}...>'


class TemplateModel(BaseModel):
    """Video overlay templates"""
    __tablename__ = 'templates'
    __table_args__ = (
        Index('ix_templates_name', 'name'),
    )
    
    name = Column(String(100), unique=True, nullable=False)
    display_name = Column(String(200), nullable=False)
    description = Column(Text)
    template_data = Column(JSON, nullable=False)  # Template configuration and layout
    preview_image = Column(String(500))  # Path to preview image
    is_active = Column(Boolean, default=True, nullable=False)
    is_system = Column(Boolean, default=False, nullable=False)  # System vs user created
    
    # Template metadata
    author = Column(String(100))
    version = Column(String(20), default='1.0.0')
    category = Column(String(50), default='custom')  # news, sports, general, custom
    
    def get_template_config(self) -> Dict[str, Any]:
        """Get template configuration"""
        if isinstance(self.template_data, dict):
            return self.template_data
        elif isinstance(self.template_data, str):
            try:
                return json.loads(self.template_data)
            except json.JSONDecodeError:
                return {}
        else:
            return {}
    
    def set_template_config(self, config: Dict[str, Any]):
        """Set template configuration"""
        self.template_data = config
    
    def validate_template(self) -> bool:
        """Validate template data structure"""
        try:
            config = self.get_template_config()
            
            # Required fields
            required_fields = ['layout', 'elements']
            for field in required_fields:
                if field not in config:
                    return False
            
            # Validate elements
            if not isinstance(config['elements'], list):
                return False
            
            for element in config['elements']:
                if not isinstance(element, dict):
                    return False
                if 'type' not in element or 'id' not in element:
                    return False
            
            return True
            
        except Exception:
            return False
    
    def __repr__(self):
        return f'<Template {self.name}>'


class SystemMetricModel(BaseModel):
    """System performance and usage metrics"""
    __tablename__ = 'system_metrics'
    __table_args__ = (
        Index('ix_system_metrics_metric_name', 'metric_name'),
        Index('ix_system_metrics_created_at', 'created_at'),
    )
    
    metric_name = Column(String(100), nullable=False)
    metric_value = Column(Float, nullable=False)
    metric_unit = Column(String(20))  # bytes, percentage, count, seconds, etc.
    component = Column(String(50))  # qt_player, web_dashboard, api_client, system
    details = Column(JSON)  # Additional metric data
    
    def __repr__(self):
        return f'<SystemMetric {self.metric_name}: {self.metric_value} {self.metric_unit}>'


class SessionModel(BaseModel):
    """Web dashboard user sessions"""
    __tablename__ = 'sessions'
    __table_args__ = (
        Index('ix_sessions_session_id', 'session_id'),
        Index('ix_sessions_user_id', 'user_id'),
    )
    
    session_id = Column(String(255), unique=True, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    ip_address = Column(String(45), nullable=False)
    user_agent = Column(Text)
    is_active = Column(Boolean, default=True, nullable=False)
    expires_at = Column(DateTime, nullable=False)
    last_activity = Column(DateTime, default=datetime.utcnow, nullable=False)
    
    # Relationships
    user = relationship('UserModel')
    
    def is_expired(self) -> bool:
        """Check if session is expired"""
        return datetime.utcnow() > self.expires_at
    
    def is_valid(self) -> bool:
        """Check if session is valid"""
        return self.is_active and not self.is_expired()
    
    def extend_session(self, hours: int = 8):
        """Extend session expiration"""
        self.expires_at = datetime.utcnow() + timedelta(hours=hours)
        self.last_activity = datetime.utcnow()
    
    def deactivate(self):
        """Deactivate session"""
        self.is_active = False
    
    def __repr__(self):
        return f'<Session {self.session_id} for User {self.user_id}>'