from datetime import datetime
from sqlalchemy import (
    Column, String, Integer, DateTime, Boolean, JSON, Enum, ForeignKey, 
    Text, Index, UniqueConstraint, DECIMAL
)
from sqlalchemy.sql import func
from sqlalchemy.orm import relationship
from src.core.database import Base
from src.core.utils import generate_uuid
import enum


class SeverityEnum(str, enum.Enum):
    """Alert severity levels"""
    LOW = "low"
    MEDIUM = "medium"
    HIGH = "high"
    CRITICAL = "critical"


class AlertTypeEnum(str, enum.Enum):
    """Types of alerts supported"""
    BUDGET_VARIANCE = "budget_variance"
    CASH_FLOW = "cash_flow"
    REVENUE_DROP = "revenue_drop"
    EXPENSE_ANOMALY = "expense_anomaly"
    FORECAST_ACCURACY = "forecast_accuracy"
    DATA_INTEGRATION = "data_integration"
    HEADCOUNT_ANOMALY = "headcount_anomaly"
    CUSTOM = "custom"


class AlertRuleStatusEnum(str, enum.Enum):
    """Status of an alert rule"""
    ACTIVE = "active"
    INACTIVE = "inactive"
    PAUSED = "paused"
    ARCHIVED = "archived"


class AlertStatus(str, enum.Enum):
    """Status of a triggered alert"""
    UNRESOLVED = "unresolved"
    ACKNOWLEDGED = "acknowledged"
    RESOLVED = "resolved"
    DISMISSED = "dismissed"


class OperatorEnum(str, enum.Enum):
    """Comparison operators for rule conditions"""
    GREATER_THAN = ">"
    LESS_THAN = "<"
    EQUAL_TO = "="
    NOT_EQUAL = "!="
    GREATER_THAN_EQUAL = ">="
    LESS_THAN_EQUAL = "<="
    CONTAINS = "contains"
    NOT_CONTAINS = "not_contains"
    IN = "in"
    NOT_IN = "not_in"


class AlertRule(Base):
    """
    User-defined rules that trigger alerts when conditions are met.
    
    Stores the definition of when and what alerts should be created.
    """
    __tablename__ = "alert_rules"
    
    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    
    # Basic info
    user_id = Column(String, nullable=False, index=True)  # User who created the rule
    company_id = Column(String, nullable=False, index=True)  # Company owning the rule
    
    name = Column(String(255), nullable=False)
    description = Column(Text, nullable=True)
    
    # Rule configuration
    alert_type = Column(Enum(AlertTypeEnum), nullable=False, index=True)
    
    # Condition structure: {
    #   "type": "AND" | "OR",
    #   "conditions": [
    #       {
    #           "metric_id": "budget_variance",
    #           "operator": ">",
    #           "threshold": 0.15,
    #           "entity_type": "department",  # optional filter
    #           "entity_id": "sales"  # optional filter
    #       },
    #       ...
    #   ]
    # }
    conditions = Column(JSON, nullable=False)
    
    # Alert configuration
    severity = Column(Enum(SeverityEnum), default=SeverityEnum.MEDIUM, nullable=False)
    notification_channels = Column(JSON, default=lambda: ["in_app"])  # ["in_app", "email", "slack"]
    
    # Control
    enabled = Column(Boolean, default=True, nullable=False, index=True)
    status = Column(Enum(AlertRuleStatusEnum), default=AlertRuleStatusEnum.ACTIVE, nullable=False)
    
    # Metadata
    last_evaluated_at = Column(DateTime(timezone=True), nullable=True)
    last_triggered_at = Column(DateTime(timezone=True), nullable=True)
    times_triggered = Column(Integer, default=0, nullable=False)
    
    # Timestamps
    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(DateTime(timezone=True), onupdate=func.now(), nullable=True)
    deleted_at = Column(DateTime(timezone=True), nullable=True)
    
    # Relationships
    alerts = relationship("Alert", back_populates="rule")
    
    # Indexes
    __table_args__ = (
        Index("ix_alert_rules_user_company", "user_id", "company_id"),
        Index("ix_alert_rules_enabled_company", "enabled", "company_id"),
    )


class Alert(Base):
    """
    Instance of a triggered alert.
    
    Created when a rule conditions are met. Contains the evaluation result
    and current status of the alert.
    """
    __tablename__ = "alerts"
    
    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    
    # Reference to the rule that triggered this alert
    rule_id = Column(String, ForeignKey("alert_rules.id"), nullable=False, index=True)
    
    # Identification
    company_id = Column(String, nullable=False, index=True)
    alert_type = Column(Enum(AlertTypeEnum), nullable=False, index=True)
    
    # Alert content
    title = Column(String(255), nullable=False)
    message = Column(Text, nullable=False)
    severity = Column(Enum(SeverityEnum), nullable=False, index=True)
    
    # Metric data that triggered the alert
    metric_data = Column(JSON, nullable=True)  # {metric_value, threshold, variance, unit, ...}
    
    # Related entities for context
    entity_type = Column(String(50), nullable=True)  # "department", "product", "customer", etc.
    entity_id = Column(String, nullable=True)
    
    # GL entries related to this alert (for audit/drill-down)
    related_gl_ids = Column(JSON, default=lambda: [])  # List of GL entry IDs
    
    # AI generated insights & recommendations
    recommended_actions = Column(JSON, nullable=True)  # [{action, description, priority}, ...]
    ai_insights = Column(Text, nullable=True)  # Natural language explanation from AI
    
    # Status tracking
    status = Column(Enum(AlertStatus), default=AlertStatus.UNRESOLVED, nullable=False, index=True)
    is_read = Column(Boolean, default=False, nullable=False)
    is_acknowledged = Column(Boolean, default=False, nullable=False)
    
    # Resolution details
    resolved_by_user_id = Column(String, nullable=True)
    resolved_at = Column(DateTime(timezone=True), nullable=True)
    resolution_action = Column(Text, nullable=True)  # User's action taken to resolve
    
    # Timestamps
    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False, index=True)
    updated_at = Column(DateTime(timezone=True), onupdate=func.now(), nullable=True)
    
    # Relationships
    rule = relationship("AlertRule", back_populates="alerts")
    acknowledgements = relationship("AlertAcknowledgement", back_populates="alert")
    
    # Indexes
    __table_args__ = (
        Index("ix_alerts_company_status", "company_id", "status"),
        Index("ix_alerts_company_created", "company_id", "created_at"),
        Index("ix_alerts_rule_id_status", "rule_id", "status"),
    )


class AlertAcknowledgement(Base):
    """
    Tracks when and by whom an alert was acknowledged.
    
    Supports audit trail of alert handling.
    """
    __tablename__ = "alert_acknowledgements"
    
    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    
    # References
    alert_id = Column(String, ForeignKey("alerts.id"), nullable=False, index=True)
    user_id = Column(String, nullable=False, index=True)
    company_id = Column(String, nullable=False, index=True)
    
    # Action details
    action = Column(String(50), nullable=True)  # "acknowledged", "resolved", "dismissed"
    notes = Column(Text, nullable=True)  # User's notes
    
    # Timestamps
    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    
    # Relationship
    alert = relationship("Alert", back_populates="acknowledgements")
    
    # Indexes
    __table_args__ = (
        Index("ix_acknowledgements_alert_user", "alert_id", "user_id"),
        Index("ix_acknowledgements_company", "company_id"),
    )


class MetricSnapshot(Base):
    """
    Historical snapshots of calculated metrics.
    
    Used for trend analysis, anomaly detection (comparing current vs historical),
    and audit trail of metric values over time.
    """
    __tablename__ = "metric_snapshots"
    
    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    
    # Metric identification
    metric_key = Column(String(100), nullable=False, index=True)  
    # e.g., "budget_variance::department::sales"
    # format: {metric_type}::{entity_type}::{entity_id}
    
    company_id = Column(String, nullable=False, index=True)
    
    # Value and metadata
    metric_value = Column(DECIMAL(18, 4), nullable=False)
    unit = Column(String(50), nullable=True)  # "percentage", "currency", "count", etc.
    reference_value = Column(DECIMAL(18, 4), nullable=True)  # e.g., budget amount
    variance = Column(DECIMAL(18, 4), nullable=True)  # e.g., actual - budget
    variance_percentage = Column(DECIMAL(6, 2), nullable=True)  # e.g., 15.25 for 15.25%
    
    # Context
    entity_type = Column(String(50), nullable=True)  # "department", "product", "customer"
    entity_id = Column(String, nullable=True)
    period = Column(String(50), nullable=True)  # "2026-03", "Q1-2026", etc.
    
    # Data source references
    source_gl_ids = Column(JSON, default=lambda: [])  # GL entry IDs used for calculation
    
    # Timestamps (actual measurement time, not insert time)
    measured_at = Column(DateTime(timezone=True), nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    
    # Indexes for common queries
    __table_args__ = (
        Index("ix_metric_snapshots_key_company", "metric_key", "company_id"),
        Index("ix_metric_snapshots_company_measured", "company_id", "measured_at"),
        Index("ix_metric_snapshots_entity", "company_id", "entity_type", "entity_id"),
    )


class RuleEvaluationLog(Base):
    """
    Log of rule evaluations for debugging and audit.
    
    Records when a rule was evaluated and what happened (was alert created, was it deduped, etc.)
    """
    __tablename__ = "rule_evaluation_logs"
    
    id = Column(String, primary_key=True, index=True, default=generate_uuid)
    
    # References
    rule_id = Column(String, ForeignKey("alert_rules.id"), nullable=False, index=True)
    company_id = Column(String, nullable=False, index=True)
    
    # Evaluation details
    evaluation_result = Column(Boolean, nullable=False)  # True if rule conditions matched
    metric_values = Column(JSON, nullable=True)  # Actual values evaluated
    alert_id = Column(String, nullable=True)  # Alert created (if any)
    
    # Reason for no alert (if applicable)
    skip_reason = Column(String(100), nullable=True)  # "deduped", "below_threshold", etc.
    
    # Execution details
    evaluation_duration_ms = Column(Integer, nullable=True)  # How long the evaluation took
    error_message = Column(Text, nullable=True)  # If evaluation failed
    
    # Timestamps
    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False, index=True)
    
    # Indexes
    __table_args__ = (
        Index("ix_evaluation_logs_rule_company", "rule_id", "company_id"),
        Index("ix_evaluation_logs_company_created", "company_id", "created_at"),
    )
