"""
Enhanced Template Variable Models with Advanced Features

This module provides sophisticated variable management for document templates including:
- Conditional logic and calculations
- Dynamic data source integration
- Variable dependencies and validation
- Type-safe variable definitions
"""
from sqlalchemy import Column, Integer, String, Text, ForeignKey, Boolean, JSON, Enum, Float, DateTime, func
from sqlalchemy.orm import relationship
from sqlalchemy.sql import expression
from enum import Enum as PyEnum
from typing import Dict, Any, List, Optional
import json

from app.models.base import BaseModel


class VariableType(PyEnum):
    """Variable types supported in templates"""
    STRING = "string"
    NUMBER = "number"
    DATE = "date"
    BOOLEAN = "boolean"
    CALCULATED = "calculated"
    CONDITIONAL = "conditional"
    QUERY = "query"
    LOOKUP = "lookup"


class TemplateVariable(BaseModel):
    """
    Enhanced template variables with support for complex logic, calculations, and data sources
    """
    __tablename__ = "template_variables"

    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    
    # Basic identification
    name = Column(String(100), nullable=False, index=True)
    display_name = Column(String(200), nullable=True)
    description = Column(Text, nullable=True)
    
    # Variable type and behavior
    variable_type = Column(Enum(VariableType), nullable=False, default=VariableType.STRING)
    required = Column(Boolean, default=False)
    active = Column(Boolean, default=True, nullable=False)
    
    # Default and static values
    default_value = Column(Text, nullable=True)
    static_value = Column(Text, nullable=True)  # When set, always returns this value
    
    # Advanced features
    formula = Column(Text, nullable=True)  # Mathematical or logical expressions
    conditional_logic = Column(JSON, nullable=True)  # If/then/else rules
    data_source_query = Column(Text, nullable=True)  # SQL query for dynamic data
    lookup_table = Column(String(100), nullable=True)  # Reference table name
    lookup_key_field = Column(String(100), nullable=True)  # Field to match on
    lookup_value_field = Column(String(100), nullable=True)  # Field to return
    
    # Validation rules
    validation_rules = Column(JSON, nullable=True)  # JSON schema or validation rules
    format_pattern = Column(String(200), nullable=True)  # Regex pattern for formatting
    
    # Dependencies and relationships
    depends_on = Column(JSON, nullable=True)  # List of variable names this depends on
    scope = Column(String(50), default="global")  # global, template, file, client
    
    # Metadata
    created_by = Column(String(150), ForeignKey("users.username"), nullable=True)
    category = Column(String(100), nullable=True, index=True)
    tags = Column(JSON, nullable=True)  # Array of tags for organization
    
    # Cache settings for performance
    cache_duration_minutes = Column(Integer, default=0)  # 0 = no cache
    last_cached_at = Column(DateTime, nullable=True)
    cached_value = Column(Text, nullable=True)

    def __repr__(self):
        return f"<TemplateVariable(name='{self.name}', type='{self.variable_type}')>"


class VariableTemplate(BaseModel):
    """
    Association between variables and document templates
    """
    __tablename__ = "variable_templates"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    template_id = Column(Integer, ForeignKey("document_templates.id"), nullable=False, index=True)
    variable_id = Column(Integer, ForeignKey("template_variables.id"), nullable=False, index=True)
    
    # Template-specific overrides
    override_default = Column(Text, nullable=True)
    override_required = Column(Boolean, nullable=True)
    display_order = Column(Integer, default=0)
    group_name = Column(String(100), nullable=True)  # For organizing variables in UI
    
    # Relationships
    template = relationship("DocumentTemplate")
    variable = relationship("TemplateVariable")


class VariableContext(BaseModel):
    """
    Context-specific variable values (per file, client, case, etc.)
    """
    __tablename__ = "variable_contexts"
    
    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    variable_id = Column(Integer, ForeignKey("template_variables.id"), nullable=False, index=True)
    
    # Context identification
    context_type = Column(String(50), nullable=False, index=True)  # file, client, global, session
    context_id = Column(String(100), nullable=False, index=True)  # The actual ID (file_no, client_id, etc.)
    
    # Value storage
    value = Column(Text, nullable=True)
    computed_value = Column(Text, nullable=True)  # Result after formula/logic processing
    last_computed_at = Column(DateTime, nullable=True)
    
    # Validation and metadata
    is_valid = Column(Boolean, default=True)
    validation_errors = Column(JSON, nullable=True)
    source = Column(String(100), nullable=True)  # manual, computed, imported, etc.
    
    # Relationships
    variable = relationship("TemplateVariable")

    def __repr__(self):
        return f"<VariableContext(variable_id={self.variable_id}, context='{self.context_type}:{self.context_id}')>"


class VariableAuditLog(BaseModel):
    """
    Audit trail for variable value changes
    """
    __tablename__ = "variable_audit_log"
    
    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    variable_id = Column(Integer, ForeignKey("template_variables.id"), nullable=False, index=True)
    context_type = Column(String(50), nullable=True, index=True)
    context_id = Column(String(100), nullable=True, index=True)
    
    # Change tracking
    old_value = Column(Text, nullable=True)
    new_value = Column(Text, nullable=True)
    change_type = Column(String(50), nullable=False)  # created, updated, deleted, computed
    change_reason = Column(String(200), nullable=True)
    
    # Metadata
    changed_by = Column(String(150), ForeignKey("users.username"), nullable=True)
    changed_at = Column(DateTime, default=func.now(), nullable=False)
    source_system = Column(String(100), nullable=True)  # web, api, import, etc.
    
    # Relationships
    variable = relationship("TemplateVariable")

    def __repr__(self):
        return f"<VariableAuditLog(variable_id={self.variable_id}, change='{self.change_type}')>"


class VariableGroup(BaseModel):
    """
    Logical groupings of variables for better organization
    """
    __tablename__ = "variable_groups"
    
    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    name = Column(String(100), nullable=False, unique=True, index=True)
    description = Column(Text, nullable=True)
    parent_group_id = Column(Integer, ForeignKey("variable_groups.id"), nullable=True)
    display_order = Column(Integer, default=0)
    
    # UI configuration
    icon = Column(String(50), nullable=True)
    color = Column(String(20), nullable=True)
    collapsible = Column(Boolean, default=True)
    
    # Relationships
    parent_group = relationship("VariableGroup", remote_side=[id])
    child_groups = relationship("VariableGroup", back_populates="parent_group")

    def __repr__(self):
        return f"<VariableGroup(name='{self.name}')>"