from datetime import datetime, timedelta
from flask_sqlalchemy import SQLAlchemy
from flask_login import UserMixin
from werkzeug.security import generate_password_hash, check_password_hash
from app import db
import uuid
import hashlib
import json
import jwt
import secrets

class User(UserMixin, db.Model):
    """User model for authentication"""
    __tablename__ = 'users'
    
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False, index=True)
    email = db.Column(db.String(120), unique=True, nullable=False, index=True)
    password_hash = db.Column(db.String(255), nullable=False)
    is_active = db.Column(db.Boolean, default=True, index=True)
    is_admin = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    last_login = db.Column(db.DateTime)
    
    # Relationships
    matches = db.relationship('Match', backref='creator', lazy='dynamic', foreign_keys='Match.created_by')
    uploads = db.relationship('FileUpload', backref='uploader', lazy='dynamic', foreign_keys='FileUpload.uploaded_by')
    sessions = db.relationship('UserSession', backref='user', lazy='dynamic', cascade='all, delete-orphan')
    logs = db.relationship('SystemLog', backref='user', lazy='dynamic', foreign_keys='SystemLog.user_id')
    api_tokens = db.relationship('APIToken', backref='user', lazy='dynamic', cascade='all, delete-orphan')
    
    def set_password(self, password):
        """Set password hash"""
        self.password_hash = generate_password_hash(password)
    
    def check_password(self, password):
        """Check password against hash"""
        return check_password_hash(self.password_hash, password)
    
    def update_last_login(self):
        """Update last login timestamp"""
        self.last_login = datetime.utcnow()
        db.session.commit()
    
    def generate_api_token(self, name, expires_in=None):
        """Generate a new API token for this user"""
        from flask import current_app
        
        # Generate a secure random token
        token_value = secrets.token_urlsafe(32)
        
        # Set expiration (default to 50 years if not specified)
        if expires_in is None:
            expires_at = datetime.utcnow() + timedelta(days=365*50)  # 50 years
        else:
            expires_at = datetime.utcnow() + expires_in
        
        # Create the token record
        api_token = APIToken(
            user_id=self.id,
            name=name,
            token_hash=APIToken.hash_token(token_value),
            expires_at=expires_at
        )
        
        db.session.add(api_token)
        db.session.commit()
        
        # Return the token record with the plain token value
        api_token.plain_token = token_value
        return api_token
    
    def revoke_api_token(self, token_id):
        """Revoke an API token"""
        token = self.api_tokens.filter_by(id=token_id).first()
        if token:
            token.is_active = False
            db.session.commit()
            return True
        return False
    
    def delete_api_token(self, token_id):
        """Delete an API token"""
        token = self.api_tokens.filter_by(id=token_id).first()
        if token:
            db.session.delete(token)
            db.session.commit()
            return True
        return False
    
    def to_dict(self):
        """Convert to dictionary for JSON serialization"""
        return {
            'id': self.id,
            'username': self.username,
            'email': self.email,
            'is_active': self.is_active,
            'is_admin': self.is_admin,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'last_login': self.last_login.isoformat() if self.last_login else None
        }
    
    def __repr__(self):
        return f'<User {self.username}>'

class Match(db.Model):
    """Primary matches table storing core fixture data"""
    __tablename__ = 'matches'
    
    id = db.Column(db.Integer, primary_key=True)
    match_number = db.Column(db.Integer, unique=True, nullable=False, index=True)
    fighter1_township = db.Column(db.String(255), nullable=False)
    fighter2_township = db.Column(db.String(255), nullable=False)
    venue_kampala_township = db.Column(db.String(255), nullable=False)
    
    # System fields
    start_time = db.Column(db.DateTime)
    end_time = db.Column(db.DateTime)
    result = db.Column(db.String(255))
    filename = db.Column(db.String(1024), nullable=False)
    file_sha1sum = db.Column(db.String(255), nullable=False, index=True)
    fixture_id = db.Column(db.String(255), nullable=False, index=True)
    active_status = db.Column(db.Boolean, default=False, index=True)
    
    # Fixture active time (unix timestamp when all matches in fixture become active)
    fixture_active_time = db.Column(db.BigInteger, nullable=True, index=True)
    
    # Status column
    status = db.Column(db.Enum('pending', 'scheduled', 'bet', 'ingame', 'cancelled', 'failed', 'paused', 'done', name='match_status'),
                       default='pending', index=True)
    
    # ZIP file related fields
    zip_filename = db.Column(db.String(1024))
    zip_sha1sum = db.Column(db.String(255), index=True)
    zip_upload_status = db.Column(db.Enum('pending', 'uploading', 'completed', 'failed', name='zip_upload_status'), 
                                 default='pending', index=True)
    zip_upload_progress = db.Column(db.Numeric(5, 2), default=0.00)
    
    # Metadata
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'), index=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # Relationships
    outcomes = db.relationship('MatchOutcome', backref='match', lazy='dynamic', cascade='all, delete-orphan')
    uploads = db.relationship('FileUpload', backref='match', lazy='dynamic', foreign_keys='FileUpload.match_id')
    logs = db.relationship('SystemLog', backref='match', lazy='dynamic', foreign_keys='SystemLog.match_id')
    
    def __init__(self, **kwargs):
        super(Match, self).__init__(**kwargs)
        # Only generate fixture_id if not provided
        if not self.fixture_id:
            self.fixture_id = str(uuid.uuid4())
    
    def calculate_file_sha1(self, file_path):
        """Calculate SHA1 checksum of a file"""
        sha1_hash = hashlib.sha1()
        with open(file_path, 'rb') as f:
            for chunk in iter(lambda: f.read(4096), b""):
                sha1_hash.update(chunk)
        return sha1_hash.hexdigest()
    
    def set_active(self):
        """Set match as active (both fixture and ZIP uploaded successfully)"""
        if self.zip_upload_status == 'completed' and self.zip_sha1sum:
            self.active_status = True
            db.session.commit()
            
            # Check if all matches in this fixture are now active
            self.update_fixture_active_time()
            return True
        return False
    
    def update_fixture_active_time(self):
        """Update fixture active time if all matches in fixture are now active"""
        try:
            import time
            # Get all matches in this fixture
            all_matches = Match.query.filter_by(fixture_id=self.fixture_id).all()
            
            # Check if all matches are active
            all_active = all([match.active_status for match in all_matches])
            
            if all_active:
                # Update fixture_active_time for all matches in this fixture if not already set
                current_time = int(time.time())
                
                # Only update if not already set (preserve the original activation time)
                matches_to_update = Match.query.filter_by(
                    fixture_id=self.fixture_id,
                    fixture_active_time=None
                ).all()
                
                for match in matches_to_update:
                    match.fixture_active_time = current_time
                
                if matches_to_update:
                    db.session.commit()
                    import logging
                    logger = logging.getLogger(__name__)
                    logger.info(f"Updated fixture {self.fixture_id} active time to {current_time}")
                    
        except Exception as e:
            import logging
            logger = logging.getLogger(__name__)
            logger.error(f"Failed to update fixture active time for {self.fixture_id}: {str(e)}")
    
    @classmethod
    def get_fixtures_with_active_time(cls, from_timestamp=None, limit=None):
        """Get fixtures ordered by active time, optionally from a specific timestamp with limit"""
        try:
            from sqlalchemy import func, and_
            
            # Base query to get fixture data with active time
            query = db.session.query(
                cls.fixture_id,
                cls.fixture_active_time,
                cls.filename,
                func.min(cls.created_at).label('created_at')
            ).filter(
                cls.fixture_active_time.isnot(None)
            ).group_by(
                cls.fixture_id,
                cls.fixture_active_time,
                cls.filename
            )
            
            # Apply timestamp filter if provided
            if from_timestamp is not None:
                query = query.filter(cls.fixture_active_time > from_timestamp)
            
            # Order by active time
            query = query.order_by(cls.fixture_active_time.asc())
            
            # Apply limit if provided
            if limit is not None:
                query = query.limit(limit)
            
            return query.all()
            
        except Exception as e:
            import logging
            logger = logging.getLogger(__name__)
            logger.error(f"Failed to get fixtures with active time: {str(e)}")
            return []
    
    @classmethod
    def get_last_activated_fixtures(cls, limit=10):
        """Get the last N activated fixtures ordered by active time (descending)"""
        try:
            from sqlalchemy import func, desc
            
            # Get fixtures ordered by active time descending (most recent first)
            query = db.session.query(
                cls.fixture_id,
                cls.fixture_active_time,
                cls.filename,
                func.min(cls.created_at).label('created_at')
            ).filter(
                cls.fixture_active_time.isnot(None)
            ).group_by(
                cls.fixture_id,
                cls.fixture_active_time,
                cls.filename
            ).order_by(desc(cls.fixture_active_time)).limit(limit)
            
            return query.all()
            
        except Exception as e:
            import logging
            logger = logging.getLogger(__name__)
            logger.error(f"Failed to get last activated fixtures: {str(e)}")
            return []
    
    @classmethod
    def backfill_fixture_active_times(cls):
        """Backfill fixture_active_time for existing active fixtures that don't have it set"""
        try:
            import time
            from sqlalchemy import func, and_
            import logging
            logger = logging.getLogger(__name__)
            
            # Find fixtures where all matches are active but fixture_active_time is NULL
            fixtures_needing_update = db.session.query(
                cls.fixture_id
            ).filter(
                and_(
                    cls.active_status == True,
                    cls.fixture_active_time.is_(None)
                )
            ).group_by(cls.fixture_id).all()
            
            updated_count = 0
            current_time = int(time.time())
            
            for fixture_row in fixtures_needing_update:
                fixture_id = fixture_row.fixture_id
                
                # Check if ALL matches in this fixture are active
                all_matches = cls.query.filter_by(fixture_id=fixture_id).all()
                all_active = all([match.active_status for match in all_matches])
                
                if all_active:
                    # Update all matches in this fixture to have the current timestamp
                    matches_to_update = cls.query.filter_by(
                        fixture_id=fixture_id,
                        fixture_active_time=None
                    ).all()
                    
                    for match in matches_to_update:
                        match.fixture_active_time = current_time
                    
                    updated_count += len(matches_to_update)
            
            if updated_count > 0:
                db.session.commit()
                logger.info(f"Backfilled fixture_active_time for {updated_count} matches")
            
            return updated_count
            
        except Exception as e:
            import logging
            logger = logging.getLogger(__name__)
            logger.error(f"Failed to backfill fixture active times: {str(e)}")
            return 0
    
    def add_outcome(self, column_name, float_value):
        """Add an outcome to this match"""
        outcome = MatchOutcome(
            match_id=self.id,
            column_name=column_name,
            float_value=float_value
        )
        db.session.add(outcome)
        return outcome
    
    def get_outcomes_dict(self):
        """Get outcomes as dictionary"""
        return {outcome.column_name: float(outcome.float_value) for outcome in self.outcomes}
    
    def to_dict(self, include_outcomes=True):
        """Convert to dictionary for JSON serialization"""
        data = {
            'id': self.id,
            'match_number': self.match_number,
            'fighter1_township': self.fighter1_township,
            'fighter2_township': self.fighter2_township,
            'venue_kampala_township': self.venue_kampala_township,
            'start_time': self.start_time.isoformat() if self.start_time else None,
            'end_time': self.end_time.isoformat() if self.end_time else None,
            'result': self.result,
            'filename': self.filename,
            'file_sha1sum': self.file_sha1sum,
            'fixture_id': self.fixture_id,
            'active_status': self.active_status,
            'status': self.status,
            'zip_filename': self.zip_filename,
            'zip_sha1sum': self.zip_sha1sum,
            'zip_upload_status': self.zip_upload_status,
            'zip_upload_progress': float(self.zip_upload_progress) if self.zip_upload_progress else 0.0,
            'fixture_active_time': self.fixture_active_time,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        }
        
        if include_outcomes:
            data['outcomes'] = self.get_outcomes_dict()
        
        return data
    
    @classmethod
    def get_matches_by_fixture(cls, fixture_id: str):
        """Get all matches for a specific fixture"""
        return cls.query.filter_by(fixture_id=fixture_id).all()
    
    @classmethod
    def get_fixtures_summary(cls):
        """Get summary of all fixtures with match counts"""
        from sqlalchemy import func
        return db.session.query(
            cls.fixture_id,
            cls.filename,
            func.count(cls.id).label('match_count'),
            func.min(cls.created_at).label('created_at')
        ).group_by(cls.fixture_id, cls.filename).all()
    
    def __repr__(self):
        return f'<Match {self.match_number}: {self.fighter1_township} vs {self.fighter2_township}>'

class MatchOutcome(db.Model):
    """Secondary outcomes table with foreign key relationships"""
    __tablename__ = 'match_outcomes'
    
    id = db.Column(db.Integer, primary_key=True)
    match_id = db.Column(db.Integer, db.ForeignKey('matches.id'), nullable=False, index=True)
    column_name = db.Column(db.String(255), nullable=False, index=True)
    float_value = db.Column(db.Numeric(10, 2), nullable=False, index=True)
    
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    __table_args__ = (
        db.UniqueConstraint('match_id', 'column_name', name='unique_match_column'),
    )
    
    def to_dict(self):
        """Convert to dictionary for JSON serialization"""
        return {
            'id': self.id,
            'match_id': self.match_id,
            'column_name': self.column_name,
            'float_value': float(self.float_value),
            'created_at': self.created_at.isoformat() if self.created_at else None
        }
    
    def __repr__(self):
        return f'<MatchOutcome {self.column_name}: {self.float_value}>'

class FileUpload(db.Model):
    """File uploads tracking table"""
    __tablename__ = 'file_uploads'
    
    id = db.Column(db.Integer, primary_key=True)
    filename = db.Column(db.String(1024), nullable=False, index=True)
    original_filename = db.Column(db.String(1024), nullable=False)
    file_path = db.Column(db.String(2048), nullable=False)
    file_size = db.Column(db.BigInteger, nullable=False)
    file_type = db.Column(db.Enum('fixture', 'zip', name='file_type'), nullable=False, index=True)
    mime_type = db.Column(db.String(255), nullable=False)
    sha1sum = db.Column(db.String(255), nullable=False, index=True)
    upload_status = db.Column(db.Enum('uploading', 'completed', 'failed', 'processing', name='upload_status'), 
                             default='uploading', index=True)
    upload_progress = db.Column(db.Numeric(5, 2), default=0.00)
    error_message = db.Column(db.Text)
    
    # Associated match (for ZIP files)
    match_id = db.Column(db.Integer, db.ForeignKey('matches.id'), index=True)
    
    # User tracking
    uploaded_by = db.Column(db.Integer, db.ForeignKey('users.id'), index=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # Relationships
    logs = db.relationship('SystemLog', backref='upload', lazy='dynamic', foreign_keys='SystemLog.upload_id')
    
    def update_progress(self, progress, status=None):
        """Update upload progress"""
        self.upload_progress = progress
        if status:
            self.upload_status = status
        self.updated_at = datetime.utcnow()
        db.session.commit()
    
    def mark_completed(self):
        """Mark upload as completed"""
        self.upload_status = 'completed'
        self.upload_progress = 100.00
        self.updated_at = datetime.utcnow()
        db.session.commit()
    
    def mark_failed(self, error_message):
        """Mark upload as failed"""
        self.upload_status = 'failed'
        self.error_message = error_message
        self.updated_at = datetime.utcnow()
        db.session.commit()
    
    def to_dict(self):
        """Convert to dictionary for JSON serialization"""
        return {
            'id': self.id,
            'filename': self.filename,
            'original_filename': self.original_filename,
            'file_size': self.file_size,
            'file_type': self.file_type,
            'mime_type': self.mime_type,
            'sha1sum': self.sha1sum,
            'upload_status': self.upload_status,
            'upload_progress': float(self.upload_progress) if self.upload_progress else 0.0,
            'error_message': self.error_message,
            'match_id': self.match_id,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        }
    
    def __repr__(self):
        return f'<FileUpload {self.filename} ({self.file_type})>'

class SystemLog(db.Model):
    """System logs table for comprehensive logging"""
    __tablename__ = 'system_logs'
    
    id = db.Column(db.Integer, primary_key=True)
    level = db.Column(db.Enum('DEBUG', 'INFO', 'WARNING', 'ERROR', 'CRITICAL', name='log_level'), 
                     nullable=False, index=True)
    message = db.Column(db.Text, nullable=False)
    module = db.Column(db.String(255), index=True)
    function_name = db.Column(db.String(255))
    line_number = db.Column(db.Integer)
    
    # Context information
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), index=True)
    match_id = db.Column(db.Integer, db.ForeignKey('matches.id'), index=True)
    upload_id = db.Column(db.Integer, db.ForeignKey('file_uploads.id'), index=True)
    session_id = db.Column(db.String(255))
    ip_address = db.Column(db.String(45))
    user_agent = db.Column(db.Text)
    
    # Additional metadata
    extra_data = db.Column(db.JSON)
    
    created_at = db.Column(db.DateTime, default=datetime.utcnow, index=True)
    
    @classmethod
    def log(cls, level, message, **kwargs):
        """Create a log entry"""
        log_entry = cls(
            level=level,
            message=message,
            **kwargs
        )
        db.session.add(log_entry)
        db.session.commit()
        return log_entry
    
    def to_dict(self):
        """Convert to dictionary for JSON serialization"""
        return {
            'id': self.id,
            'level': self.level,
            'message': self.message,
            'module': self.module,
            'function_name': self.function_name,
            'line_number': self.line_number,
            'user_id': self.user_id,
            'match_id': self.match_id,
            'upload_id': self.upload_id,
            'session_id': self.session_id,
            'ip_address': self.ip_address,
            'extra_data': self.extra_data,
            'created_at': self.created_at.isoformat() if self.created_at else None
        }
    
    def __repr__(self):
        return f'<SystemLog {self.level}: {self.message[:50]}...>'

class UserSession(db.Model):
    """Session management table"""
    __tablename__ = 'user_sessions'
    
    id = db.Column(db.Integer, primary_key=True)
    session_id = db.Column(db.String(255), unique=True, nullable=False, index=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False, index=True)
    ip_address = db.Column(db.String(45), nullable=False)
    user_agent = db.Column(db.Text)
    is_active = db.Column(db.Boolean, default=True, index=True)
    expires_at = db.Column(db.DateTime, nullable=False, index=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    last_activity = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    def __init__(self, **kwargs):
        super(UserSession, self).__init__(**kwargs)
        if not self.session_id:
            self.session_id = str(uuid.uuid4())
        if not self.expires_at:
            self.expires_at = datetime.utcnow() + timedelta(hours=24)
    
    def is_expired(self):
        """Check if session is expired"""
        return datetime.utcnow() > self.expires_at
    
    def extend_session(self, hours=24):
        """Extend session expiration"""
        self.expires_at = datetime.utcnow() + timedelta(hours=hours)
        self.last_activity = datetime.utcnow()
        db.session.commit()
    
    def deactivate(self):
        """Deactivate session"""
        self.is_active = False
        db.session.commit()
    
    def to_dict(self):
        """Convert to dictionary for JSON serialization"""
        return {
            'id': self.id,
            'session_id': self.session_id,
            'user_id': self.user_id,
            'ip_address': self.ip_address,
            'is_active': self.is_active,
            'expires_at': self.expires_at.isoformat() if self.expires_at else None,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'last_activity': self.last_activity.isoformat() if self.last_activity else None
        }
    
    def __repr__(self):
        return f'<UserSession {self.session_id} for User {self.user_id}>'

class APIToken(db.Model):
    """API tokens for external application authentication"""
    __tablename__ = 'api_tokens'
    
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False, index=True)
    name = db.Column(db.String(255), nullable=False)  # User-friendly name for the token
    token_hash = db.Column(db.String(255), nullable=False, unique=True, index=True)
    is_active = db.Column(db.Boolean, default=True, index=True)
    expires_at = db.Column(db.DateTime, nullable=False, index=True)
    last_used_at = db.Column(db.DateTime)
    last_used_ip = db.Column(db.String(45))
    
    # Metadata
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    def __init__(self, **kwargs):
        super(APIToken, self).__init__(**kwargs)
    
    @staticmethod
    def hash_token(token):
        """Hash a token for secure storage"""
        return hashlib.sha256(token.encode('utf-8')).hexdigest()
    
    @staticmethod
    def verify_token(token, token_hash):
        """Verify a token against its hash"""
        return APIToken.hash_token(token) == token_hash
    
    def is_expired(self):
        """Check if token is expired"""
        return datetime.utcnow() > self.expires_at
    
    def is_valid(self):
        """Check if token is valid (active and not expired)"""
        return self.is_active and not self.is_expired()
    
    def update_last_used(self, ip_address=None):
        """Update last used timestamp and IP"""
        self.last_used_at = datetime.utcnow()
        if ip_address:
            self.last_used_ip = ip_address
        db.session.commit()
    
    def revoke(self):
        """Revoke this token"""
        self.is_active = False
        db.session.commit()
    
    def extend_expiration(self, days=365):
        """Extend token expiration"""
        self.expires_at = datetime.utcnow() + timedelta(days=days)
        db.session.commit()
    
    @classmethod
    def find_by_token(cls, token):
        """Find a token by its value"""
        token_hash = cls.hash_token(token)
        return cls.query.filter_by(token_hash=token_hash, is_active=True).first()
    
    @classmethod
    def cleanup_expired(cls):
        """Remove expired tokens from database"""
        expired_tokens = cls.query.filter(cls.expires_at < datetime.utcnow()).all()
        for token in expired_tokens:
            db.session.delete(token)
        db.session.commit()
        return len(expired_tokens)
    
    def to_dict(self, include_token=False):
        """Convert to dictionary for JSON serialization"""
        data = {
            'id': self.id,
            'name': self.name,
            'is_active': self.is_active,
            'expires_at': self.expires_at.isoformat() if self.expires_at else None,
            'last_used_at': self.last_used_at.isoformat() if self.last_used_at else None,
            'last_used_ip': self.last_used_ip,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'is_expired': self.is_expired(),
            'is_valid': self.is_valid()
        }
        
        # Only include the plain token when explicitly requested (e.g., during creation)
        if include_token and hasattr(self, 'plain_token'):
            data['token'] = self.plain_token
        
        return data
    
    def __repr__(self):
        return f'<APIToken {self.name} for User {self.user_id}>'

class SystemSettings(db.Model):
    """System-wide configuration settings stored in database"""
    __tablename__ = 'system_settings'
    
    id = db.Column(db.Integer, primary_key=True)
    key = db.Column(db.String(255), unique=True, nullable=False, index=True)
    value = db.Column(db.Text, nullable=False)
    value_type = db.Column(db.Enum('string', 'boolean', 'integer', 'float', 'json', name='setting_value_type'),
                          default='string', nullable=False)
    description = db.Column(db.Text)
    
    # Metadata
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    @classmethod
    def get_setting(cls, key, default=None):
        """Get a setting value by key"""
        setting = cls.query.filter_by(key=key).first()
        if not setting:
            return default
        
        # Convert value based on type
        if setting.value_type == 'boolean':
            return setting.value.lower() in ('true', '1', 'yes', 'on')
        elif setting.value_type == 'integer':
            try:
                return int(setting.value)
            except ValueError:
                return default
        elif setting.value_type == 'float':
            try:
                return float(setting.value)
            except ValueError:
                return default
        elif setting.value_type == 'json':
            try:
                return json.loads(setting.value)
            except (json.JSONDecodeError, TypeError):
                return default
        else:  # string
            return setting.value
    
    @classmethod
    def set_setting(cls, key, value, value_type='string', description=None):
        """Set a setting value"""
        setting = cls.query.filter_by(key=key).first()
        
        # Convert value to string for storage
        if value_type == 'boolean':
            str_value = 'true' if value else 'false'
        elif value_type in ('integer', 'float'):
            str_value = str(value)
        elif value_type == 'json':
            str_value = json.dumps(value)
        else:  # string
            str_value = str(value)
        
        if setting:
            setting.value = str_value
            setting.value_type = value_type
            if description:
                setting.description = description
            setting.updated_at = datetime.utcnow()
        else:
            setting = cls(
                key=key,
                value=str_value,
                value_type=value_type,
                description=description
            )
            db.session.add(setting)
        
        db.session.commit()
        return setting
    
    @classmethod
    def is_registration_enabled(cls):
        """Check if user registration is enabled (defaults to False)"""
        return cls.get_setting('registration_enabled', False)
    
    @classmethod
    def set_registration_enabled(cls, enabled):
        """Enable or disable user registration"""
        return cls.set_setting(
            'registration_enabled',
            enabled,
            'boolean',
            'Enable or disable new user registration'
        )
    
    @classmethod
    def initialize_default_settings(cls):
        """Initialize default system settings"""
        defaults = [
            ('registration_enabled', False, 'boolean', 'Enable or disable new user registration'),
            ('max_upload_size_mb', 2048, 'integer', 'Maximum file upload size in MB'),
            ('session_timeout_hours', 24, 'integer', 'User session timeout in hours'),
            ('api_rate_limit_per_minute', 60, 'integer', 'API rate limit per minute per IP'),
            ('api_updates_default_count', 10, 'integer', 'Default number of fixtures returned by /api/updates when no from parameter is provided'),
        ]
        
        for key, default_value, value_type, description in defaults:
            existing = cls.query.filter_by(key=key).first()
            if not existing:
                cls.set_setting(key, default_value, value_type, description)
    
    def to_dict(self):
        """Convert to dictionary for JSON serialization"""
        return {
            'id': self.id,
            'key': self.key,
            'value': self.get_typed_value(),
            'value_type': self.value_type,
            'description': self.description,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        }
    
    def get_typed_value(self):
        """Get the value converted to its proper type"""
        return SystemSettings.get_setting(self.key)
    
    def __repr__(self):
        return f'<SystemSettings {self.key}: {self.value}>'