"""
SQLAlchemy database models for MbetterClient
"""

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

# Enum for match status
class MatchStatus(str, Enum):
    PENDING = "pending"
    SCHEDULED = "scheduled"
    BET = "bet"
    INGAME = "ingame"
    DONE = "done"
    CANCELLED = "cancelled"
    FAILED = "failed"
    PAUSED = "paused"

# Enum for bet result status
class BetResult(str, Enum):
    WIN = "win"
    LOST = "lost"
    PENDING = "pending"
    CANCELLED = "cancelled"

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'),
        Index('ix_users_role', 'role'),
    )
    
    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)  # Keep for backward compatibility
    role = Column(String(20), default='normal', nullable=False)  # admin, normal, cashier
    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 using SHA-256 with salt (consistent with AuthManager)"""
        import hashlib
        import secrets
        salt = secrets.token_hex(16)
        password_hash = hashlib.sha256((password + salt).encode()).hexdigest()
        self.password_hash = f"{salt}:{password_hash}"
    
    def check_password(self, password: str) -> bool:
        """Check password against hash (consistent with AuthManager)"""
        try:
            salt, password_hash = self.password_hash.split(':', 1)
            expected_hash = hashlib.sha256((password + salt).encode()).hexdigest()
            return password_hash == expected_hash
        except (ValueError, AttributeError):
            return False
    
    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 is_admin_user(self) -> bool:
        """Check if user has admin role"""
        return self.role == 'admin' or self.is_admin
    
    def is_cashier_user(self) -> bool:
        """Check if user has cashier role"""
        return self.role == 'cashier'
    
    def is_normal_user(self) -> bool:
        """Check if user has normal role"""
        return self.role == 'normal'
    
    def set_role(self, role: str):
        """Set user role and update is_admin for backward compatibility"""
        valid_roles = ['admin', 'normal', 'cashier']
        if role not in valid_roles:
            raise ValueError(f"Invalid role: {role}. Must be one of {valid_roles}")
        
        self.role = role
        self.is_admin = (role == 'admin')
        self.updated_at = 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}>'


class MatchModel(BaseModel):
    """Boxing matches from fixture files"""
    __tablename__ = 'matches'
    __table_args__ = (
        Index('ix_matches_match_number', 'match_number'),
        Index('ix_matches_fixture_id', 'fixture_id'),
        Index('ix_matches_active_status', 'active_status'),
        Index('ix_matches_file_sha1sum', 'file_sha1sum'),
        Index('ix_matches_zip_sha1sum', 'zip_sha1sum'),
        Index('ix_matches_zip_upload_status', 'zip_upload_status'),
        Index('ix_matches_zip_validation_status', 'zip_validation_status'),
        Index('ix_matches_created_by', 'created_by'),
        Index('ix_matches_fixture_active_time', 'fixture_active_time'),
        Index('ix_matches_composite', 'active_status', 'zip_upload_status', 'created_at'),
        UniqueConstraint('fixture_id', 'match_number', name='uq_matches_fixture_match'),
    )
    
    # Core match data from fixture file
    match_number = Column(Integer, nullable=False, comment='Match # from fixture file')
    fighter1_township = Column(String(255), nullable=False, comment='Fighter1 (Township)')
    fighter2_township = Column(String(255), nullable=False, comment='Fighter2 (Township)')
    venue_kampala_township = Column(String(255), nullable=False, comment='Venue (Kampala Township)')
    
    # Match timing and results
    start_time = Column(DateTime, comment='Match start time')
    end_time = Column(DateTime, comment='Match end time')
    result = Column(String(255), comment='Match result/outcome (main result only, e.g. RET2)')
    winning_outcomes = Column(JSON, comment='Array of winning outcomes from extraction associations (e.g., ["WIN1", "X1", "12"])')
    under_over_result = Column(String(50), comment='UNDER/OVER result if applicable')
    done = Column(Boolean, default=False, nullable=False, comment='Match completion flag (0=pending, 1=done)')
    running = Column(Boolean, default=False, nullable=False, comment='Match running flag (0=not running, 1=running)')
    status = Column(Enum('pending', 'scheduled', 'bet', 'ingame', 'done', 'cancelled', 'failed', 'paused'), default='pending', nullable=False, comment='Match status enum')
    fixture_active_time = Column(Integer, nullable=True, comment='Unix timestamp when fixture became active on server')
    
    # File metadata
    filename = Column(String(1024), nullable=False, comment='Original fixture filename')
    file_sha1sum = Column(String(255), nullable=False, comment='SHA1 checksum of fixture file')
    fixture_id = Column(String(255), nullable=False, comment='Fixture identifier (multiple matches can share same fixture)')
    active_status = Column(Boolean, default=False, nullable=False, comment='Active status flag')
    
    # ZIP file related fields
    zip_filename = Column(String(1024), comment='Associated ZIP filename')
    zip_sha1sum = Column(String(255), comment='SHA1 checksum of ZIP file')
    zip_upload_status = Column(String(20), default='pending', comment='Upload status: pending, uploading, completed, failed')
    zip_upload_progress = Column(Float, default=0.0, comment='Upload progress percentage (0.0-100.0)')
    zip_validation_status = Column(String(20), default='pending', comment='Validation status: pending, validating, valid, invalid, failed')
    
    # User tracking
    created_by = Column(Integer, ForeignKey('users.id'), comment='User who created this record')
    
    # Relationships
    creator = relationship('UserModel', foreign_keys=[created_by])
    outcomes = relationship('MatchOutcomeModel', back_populates='match', cascade='all, delete-orphan')
    
    def is_upload_pending(self) -> bool:
        """Check if ZIP upload is pending"""
        return self.zip_upload_status == 'pending'
    
    def is_upload_in_progress(self) -> bool:
        """Check if ZIP upload is in progress"""
        return self.zip_upload_status == 'uploading'
    
    def is_upload_completed(self) -> bool:
        """Check if ZIP upload is completed"""
        return self.zip_upload_status == 'completed'
    
    def is_upload_failed(self) -> bool:
        """Check if ZIP upload failed"""
        return self.zip_upload_status == 'failed'
    
    def set_upload_status(self, status: str, progress: float = None):
        """Set upload status and optionally progress"""
        valid_statuses = ['pending', 'uploading', 'completed', 'failed']
        if status not in valid_statuses:
            raise ValueError(f"Invalid status: {status}. Must be one of {valid_statuses}")
        
        self.zip_upload_status = status
        if progress is not None:
            self.zip_upload_progress = min(100.0, max(0.0, progress))
        self.updated_at = datetime.utcnow()
    
    def activate(self):
        """Activate this match"""
        self.active_status = True
        self.updated_at = datetime.utcnow()
    
    def deactivate(self):
        """Deactivate this match"""
        self.active_status = False
        self.updated_at = datetime.utcnow()
    
    def get_outcomes_dict(self) -> Dict[str, float]:
        """Get match outcomes as a dictionary"""
        return {outcome.column_name: outcome.float_value for outcome in self.outcomes}
    
    def add_outcome(self, column_name: str, float_value: float):
        """Add or update match outcome"""
        # Check if outcome already exists
        existing = next((o for o in self.outcomes if o.column_name == column_name), None)
        if existing:
            existing.float_value = float_value
            existing.updated_at = datetime.utcnow()
        else:
            outcome = MatchOutcomeModel(
                column_name=column_name,
                float_value=float_value
            )
            self.outcomes.append(outcome)
    
    def to_dict(self, exclude_fields: Optional[List[str]] = None) -> Dict[str, Any]:
        """Convert to dictionary with outcomes"""
        result = super().to_dict(exclude_fields)
        result['outcomes'] = self.get_outcomes_dict()
        result['outcome_count'] = len(self.outcomes)
        return result
    
    def __repr__(self):
        return f'<Match #{self.match_number}: {self.fighter1_township} vs {self.fighter2_township}>'


class MatchOutcomeModel(BaseModel):
    """Match outcome values from fixture files"""
    __tablename__ = 'match_outcomes'
    __table_args__ = (
        Index('ix_match_outcomes_match_id', 'match_id'),
        Index('ix_match_outcomes_column_name', 'column_name'),
        Index('ix_match_outcomes_float_value', 'float_value'),
        Index('ix_match_outcomes_composite', 'match_id', 'column_name'),
        UniqueConstraint('match_id', 'column_name', name='uq_match_outcomes_match_column'),
    )

    match_id = Column(Integer, ForeignKey('matches.id', ondelete='CASCADE'), nullable=False, comment='Foreign key to matches table')
    column_name = Column(String(255), nullable=False, comment='Result column name from fixture file')
    float_value = Column(Float, nullable=False, comment='Float value with precision')

    # Relationships
    match = relationship('MatchModel', back_populates='outcomes')

    def __repr__(self):
        return f'<MatchOutcome {self.column_name}={self.float_value} for Match {self.match_id}>'


class ExtractionAssociationModel(BaseModel):
    """Associations between match outcomes and extraction results"""
    __tablename__ = 'extraction_associations'
    __table_args__ = (
        Index('ix_extraction_associations_outcome_name', 'outcome_name'),
        Index('ix_extraction_associations_extraction_result', 'extraction_result'),
        Index('ix_extraction_associations_composite', 'outcome_name', 'extraction_result'),
    )

    outcome_name = Column(String(255), nullable=False, comment='Match outcome name (e.g., WIN1, DRAW, X1, etc.)')
    extraction_result = Column(String(50), nullable=False, comment='Extraction result category (WIN1, X, WIN2)')
    is_default = Column(Boolean, default=False, nullable=False, comment='Whether this is a default association')

    def __repr__(self):
        return f'<ExtractionAssociation {self.outcome_name} -> {self.extraction_result}>'


class GameConfigModel(BaseModel):
    """Game configuration settings"""
    __tablename__ = 'game_config'
    __table_args__ = (
        Index('ix_game_config_key', 'config_key'),
        UniqueConstraint('config_key', name='uq_game_config_key'),
    )

    config_key = Column(String(100), nullable=False, unique=True, comment='Configuration key')
    config_value = Column(Text, nullable=False, comment='Configuration value as text')
    value_type = Column(String(20), default='string', nullable=False, comment='Type of value: string, int, float, bool')
    description = Column(String(500), comment='Description of the configuration setting')
    is_system = Column(Boolean, default=False, nullable=False, comment='Whether this is a system setting')

    def get_typed_value(self) -> Any:
        """Get value converted to proper type"""
        if self.value_type == 'int':
            try:
                return int(self.config_value)
            except (ValueError, TypeError):
                return 0
        elif self.value_type == 'float':
            try:
                return float(self.config_value)
            except (ValueError, TypeError):
                return 0.0
        elif self.value_type == 'bool':
            return self.config_value.lower() in ('true', '1', 'yes', 'on')
        else:
            return self.config_value

    def set_typed_value(self, value: Any):
        """Set value with automatic type detection"""
        if isinstance(value, bool):
            self.config_value = str(value).lower()
            self.value_type = 'bool'
        elif isinstance(value, int):
            self.config_value = str(value)
            self.value_type = 'int'
        elif isinstance(value, float):
            self.config_value = str(value)
            self.value_type = 'float'
        else:
            self.config_value = str(value)
            self.value_type = 'string'

    def __repr__(self):
        return f'<GameConfig {self.config_key}={self.config_value}>'


class BetModel(BaseModel):
    """Betting system main table"""
    __tablename__ = 'bets'
    __table_args__ = (
        Index('ix_bets_uuid', 'uuid'),
        Index('ix_bets_fixture_id', 'fixture_id'),
        Index('ix_bets_created_at', 'created_at'),
        Index('ix_bets_barcode_data', 'barcode_data'),
        Index('ix_bets_barcode_standard', 'barcode_standard'),
        UniqueConstraint('uuid', name='uq_bets_uuid'),
        UniqueConstraint('barcode_data', 'barcode_standard', name='uq_bets_barcode'),
    )

    uuid = Column(String(1024), nullable=False, unique=True, comment='Unique identifier for the bet')
    fixture_id = Column(String(255), nullable=False, comment='Reference to fixture_id from matches table')
    bet_datetime = Column(DateTime, default=datetime.utcnow, nullable=False, comment='Bet creation timestamp')
    paid = Column(Boolean, default=False, nullable=False, comment='Payment status (True if payment received)')
    paid_out = Column(Boolean, default=False, nullable=False, comment='Payout status (True if winnings paid out)')

    # Barcode fields for verification
    barcode_standard = Column(String(50), comment='Barcode standard used (ean13, code128, etc.)')
    barcode_data = Column(String(255), comment='Barcode data for verification')

    # Relationships
    bet_details = relationship('BetDetailModel', back_populates='bet', cascade='all, delete-orphan')
    
    def get_total_amount(self) -> float:
        """Get total amount of all bet details"""
        return sum(detail.amount for detail in self.bet_details)
    
    def get_bet_count(self) -> int:
        """Get number of bet details"""
        return len(self.bet_details)
    
    def has_pending_bets(self) -> bool:
        """Check if bet has any pending bet details"""
        return any(detail.result == 'pending' for detail in self.bet_details)
    
    def calculate_total_winnings(self) -> float:
        """Calculate total winnings from won bets"""
        return sum(detail.win_amount for detail in self.bet_details if detail.result == 'win')

    def get_overall_status(self) -> str:
        """Get overall bet status based on bet details"""
        if not self.bet_details:
            return 'pending'

        results = [detail.result for detail in self.bet_details]

        # If any detail is pending, bet is pending
        if 'pending' in results:
            return 'pending'

        # If all results are cancelled, bet is cancelled
        if all(result == 'cancelled' for result in results):
            return 'cancelled'

        # If any result is win, bet is win
        if 'win' in results:
            return 'win'

        # Otherwise, all results are lost
        return 'lost'

    def is_paid_out(self) -> bool:
        """Check if bet winnings have been paid out"""
        return self.paid_out

    def mark_paid_out(self):
        """Mark bet as paid out"""
        self.paid_out = True
        self.updated_at = datetime.utcnow()
    
    def to_dict(self, exclude_fields: Optional[List[str]] = None) -> Dict[str, Any]:
        """Convert to dictionary with bet details"""
        result = super().to_dict(exclude_fields)
        result['bet_details'] = [detail.to_dict() for detail in self.bet_details]
        result['total_amount'] = self.get_total_amount()
        result['bet_count'] = self.get_bet_count()
        result['has_pending'] = self.has_pending_bets()
        result['overall_status'] = self.get_overall_status()
        result['total_winnings'] = self.calculate_total_winnings()
        return result
    
    def __repr__(self):
        return f'<Bet {self.uuid} for Fixture {self.fixture_id}>'


class BetDetailModel(BaseModel):
    """Betting system details table"""
    __tablename__ = 'bets_details'
    __table_args__ = (
        Index('ix_bets_details_bet_id', 'bet_id'),
        Index('ix_bets_details_match_id', 'match_id'),
        Index('ix_bets_details_outcome', 'outcome'),
        Index('ix_bets_details_result', 'result'),
        Index('ix_bets_details_composite', 'bet_id', 'match_id'),
    )
    
    bet_id = Column(String(1024), ForeignKey('bets.uuid'), nullable=False, comment='Foreign key to bets table uuid field')
    match_id = Column(Integer, ForeignKey('matches.id'), nullable=False, comment='Foreign key to matches table')
    outcome = Column(String(255), nullable=False, comment='Bet outcome/prediction')
    amount = Column(Float(precision=2), nullable=False, comment='Bet amount with 2 decimal precision')
    win_amount = Column(Float(precision=2), default=0.0, nullable=False, comment='Winning amount (calculated when result is win)')
    result = Column(Enum('win', 'lost', 'pending', 'cancelled'), default='pending', nullable=False, comment='Bet result status')
    
    # Relationships
    bet = relationship('BetModel', back_populates='bet_details')
    match = relationship('MatchModel')
    
    def is_pending(self) -> bool:
        """Check if bet detail is pending"""
        return self.result == 'pending'
    
    def is_won(self) -> bool:
        """Check if bet detail was won"""
        return self.result == 'win'
    
    def is_lost(self) -> bool:
        """Check if bet detail was lost"""
        return self.result == 'lost'
    
    def is_cancelled(self) -> bool:
        """Check if bet detail was cancelled"""
        return self.result == 'cancelled'
    
    def set_result(self, result: str, win_amount: float = None):
        """Set bet result and optionally win amount"""
        valid_results = ['win', 'lost', 'pending', 'cancelled']
        if result not in valid_results:
            raise ValueError(f"Invalid result: {result}. Must be one of {valid_results}")
        self.result = result
        if win_amount is not None:
            self.win_amount = win_amount
        self.updated_at = datetime.utcnow()
    
    def __repr__(self):
        return f'<BetDetail {self.outcome}={self.amount} ({self.result}) for Match {self.match_id}>'


class AvailableBetModel(BaseModel):
    """Available betting options for extraction system"""
    __tablename__ = 'available_bets'
    __table_args__ = (
        Index('ix_available_bets_bet_name', 'bet_name'),
        Index('ix_available_bets_is_active', 'is_active'),
        Index('ix_available_bets_sort_order', 'sort_order'),
        UniqueConstraint('bet_name', name='uq_available_bets_bet_name'),
    )

    bet_name = Column(String(50), nullable=False, unique=True, comment='Bet option name (e.g., WIN1, DRAW, X)')
    description = Column(String(255), comment='Description of the bet option')
    is_active = Column(Boolean, default=True, nullable=False, comment='Whether this bet option is active')
    sort_order = Column(Integer, default=0, comment='Sort order for display')

    def __repr__(self):
        return f'<AvailableBet {self.bet_name}: {self.description}>'


class ResultOptionModel(BaseModel):
    """Result options for extraction system results area"""
    __tablename__ = 'result_options'
    __table_args__ = (
        Index('ix_result_options_result_name', 'result_name'),
        Index('ix_result_options_is_active', 'is_active'),
        Index('ix_result_options_sort_order', 'sort_order'),
        UniqueConstraint('result_name', name='uq_result_options_result_name'),
    )

    result_name = Column(String(50), nullable=False, unique=True, comment='Result option name (e.g., DRAW, WIN1, KO1)')
    description = Column(String(255), comment='Description of the result option')
    is_active = Column(Boolean, default=True, nullable=False, comment='Whether this result option is active')
    sort_order = Column(Integer, default=0, comment='Sort order for display')

    def __repr__(self):
        return f'<ResultOption {self.result_name}: {self.description}>'


class ExtractionStatsModel(BaseModel):
    """Statistics for extraction results and betting patterns"""
    __tablename__ = 'extraction_stats'
    __table_args__ = (
        Index('ix_extraction_stats_match_id', 'match_id'),
        Index('ix_extraction_stats_fixture_id', 'fixture_id'),
        Index('ix_extraction_stats_match_datetime', 'match_datetime'),
        Index('ix_extraction_stats_actual_result', 'actual_result'),
        Index('ix_extraction_stats_composite', 'fixture_id', 'match_id'),
    )

    # Match identification
    match_id = Column(Integer, ForeignKey('matches.id'), nullable=False, comment='Foreign key to matches table')
    fixture_id = Column(String(255), nullable=False, comment='Fixture identifier')
    match_datetime = Column(DateTime, nullable=False, comment='When the match was completed')

    # Overall betting statistics
    total_bets = Column(Integer, default=0, nullable=False, comment='Total number of bets placed on this match')
    total_amount_collected = Column(Float(precision=2), default=0.0, nullable=False, comment='Total amount collected from all bets')
    total_redistributed = Column(Float(precision=2), default=0.0, nullable=False, comment='Total amount redistributed to winners')

    # Result statistics (JSON structure for flexible result tracking)
    actual_result = Column(String(50), nullable=False, comment='The actual result of the match (WIN1, DRAW, WIN2, etc.)')
    result_breakdown = Column(JSON, nullable=False, comment='Detailed breakdown of bets and amounts by result option')

    # UNDER/OVER specific statistics
    under_bets = Column(Integer, default=0, nullable=False, comment='Number of UNDER bets')
    under_amount = Column(Float(precision=2), default=0.0, nullable=False, comment='Total amount bet on UNDER')
    over_bets = Column(Integer, default=0, nullable=False, comment='Number of OVER bets')
    over_amount = Column(Float(precision=2), default=0.0, nullable=False, comment='Total amount bet on OVER')

    # Extraction system statistics
    extraction_result = Column(String(50), comment='Result from extraction system (if different from actual)')
    cap_applied = Column(Boolean, default=False, nullable=False, comment='Whether redistribution CAP was applied')
    cap_percentage = Column(Float(precision=2), comment='CAP percentage used (if applied)')

    # Relationships
    match = relationship('MatchModel')

    def set_result_breakdown(self, breakdown: Dict[str, Dict[str, Any]]):
        """Set result breakdown as JSON

        Expected format:
        {
            "WIN1": {"bets": 10, "amount": 500.00, "coefficient": 2.0},
            "DRAW": {"bets": 5, "amount": 250.00, "coefficient": 3.0},
            ...
        }
        """
        self.result_breakdown = breakdown

    def get_result_breakdown(self) -> Dict[str, Dict[str, Any]]:
        """Get result breakdown from JSON"""
        if isinstance(self.result_breakdown, dict):
            return self.result_breakdown
        elif isinstance(self.result_breakdown, str):
            try:
                return json.loads(self.result_breakdown)
            except json.JSONDecodeError:
                return {}
        else:
            return {}

    def add_result_stat(self, result_name: str, bet_count: int, amount: float, coefficient: float = None):
        """Add or update statistics for a specific result"""
        breakdown = self.get_result_breakdown()
        if result_name not in breakdown:
            breakdown[result_name] = {"bets": 0, "amount": 0.0}

        breakdown[result_name]["bets"] += bet_count
        breakdown[result_name]["amount"] += amount
        if coefficient is not None:
            breakdown[result_name]["coefficient"] = coefficient

        self.set_result_breakdown(breakdown)

    def get_profit_loss(self) -> float:
        """Calculate profit/loss for this match (collected - redistributed)"""
        return self.total_amount_collected - self.total_redistributed

    def get_payout_ratio(self) -> float:
        """Calculate payout ratio (redistributed / collected)"""
        if self.total_amount_collected > 0:
            return self.total_redistributed / self.total_amount_collected
        return 0.0

    def to_dict(self, exclude_fields: Optional[List[str]] = None) -> Dict[str, Any]:
        """Convert to dictionary with calculated fields"""
        result = super().to_dict(exclude_fields)
        result['result_breakdown'] = self.get_result_breakdown()
        result['profit_loss'] = self.get_profit_loss()
        result['payout_ratio'] = self.get_payout_ratio()
        return result

    def __repr__(self):
        return f'<ExtractionStats Match {self.match_id}: {self.total_bets} bets, {self.total_amount_collected:.2f} collected, {self.actual_result}>'


class DailyRedistributionShortfallModel(BaseModel):
    """Daily redistribution shortfall tracking for CAP adjustments"""
    __tablename__ = 'daily_redistribution_shortfall'
    __table_args__ = (
        Index('ix_daily_redistribution_shortfall_date', 'date'),
        UniqueConstraint('date', name='uq_daily_redistribution_shortfall_date'),
    )

    date = Column(Date, nullable=False, unique=True, comment='Date for shortfall tracking')
    accumulated_shortfall = Column(Float(precision=2), default=0.0, nullable=False, comment='Accumulated shortfall from previous extractions')
    total_payin = Column(Float(precision=2), default=0.0, nullable=False, comment='Total payin for the day')
    total_redistributed = Column(Float(precision=2), default=0.0, nullable=False, comment='Total redistributed for the day')
    cap_percentage = Column(Float(precision=2), default=70.0, nullable=False, comment='CAP percentage used for calculations')

    def __repr__(self):
        return f'<DailyRedistributionShortfall {self.date}: shortfall={self.accumulated_shortfall:.2f}, payin={self.total_payin:.2f}, redistributed={self.total_redistributed:.2f}>'

