"""SQLAlchemy ORM models for DSOL."""

from datetime import datetime
from typing import Optional
from uuid import uuid4

from sqlalchemy import (
    Boolean,
    CheckConstraint,
    DateTime,
    Float,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    UniqueConstraint,
    func,
)
from sqlalchemy.dialects.postgresql import ARRAY, JSONB, UUID
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


# Mixin for tenant + node isolation columns (data-isolation-spec Section 2.5)
class TenantIsolationMixin:
    """Adds tenant_id and node_id columns for multi-tenant data isolation."""

    tenant_id: Mapped[Optional[UUID]] = mapped_column(
        UUID(as_uuid=True), nullable=True, index=False
    )
    node_id: Mapped[Optional[UUID]] = mapped_column(
        UUID(as_uuid=True), nullable=True, index=False
    )


class Base(DeclarativeBase):
    """Base class for all ORM models."""

    pass


class ApiKey(Base):
    """API key model for authentication and rate limiting."""

    __tablename__ = "api_keys"

    id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True), primary_key=True, default=uuid4
    )
    key_hash: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    name: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    rate_limit_per_minute: Mapped[int] = mapped_column(Integer, default=60)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    last_used_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), nullable=True
    )

    # Relationships
    documents: Mapped[list["Document"]] = relationship(
        "Document", back_populates="api_key"
    )
    query_logs: Mapped[list["QueryLog"]] = relationship(
        "QueryLog", back_populates="api_key"
    )


class Document(TenantIsolationMixin, Base):
    """Document model for uploaded Excel files."""

    __tablename__ = "documents"

    id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True), primary_key=True, default=uuid4
    )
    filename: Mapped[str] = mapped_column(String(255), nullable=False)
    file_path: Mapped[str] = mapped_column(String(500), nullable=False)
    file_size_bytes: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    status: Mapped[str] = mapped_column(String(50), default="pending")
    error_message: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    sheet_count: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    record_count: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    processed_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), nullable=True
    )
    api_key_id: Mapped[Optional[UUID]] = mapped_column(
        UUID(as_uuid=True), ForeignKey("api_keys.id"), nullable=True
    )
    approval_status: Mapped[str] = mapped_column(
        String(20), nullable=False, default="pending", server_default="pending"
    )
    reviewed_by: Mapped[Optional[UUID]] = mapped_column(
        UUID(as_uuid=True), nullable=True
    )
    reviewed_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), nullable=True
    )
    rejection_reason: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    reviewed_note: Mapped[Optional[str]] = mapped_column(Text, nullable=True)

    # Relationships
    api_key: Mapped[Optional["ApiKey"]] = relationship(
        "ApiKey", back_populates="documents"
    )
    extracted_records: Mapped[list["ExtractedRecord"]] = relationship(
        "ExtractedRecord", back_populates="document", cascade="all, delete-orphan"
    )
    symbol_dictionaries: Mapped[list["SymbolDictionary"]] = relationship(
        "SymbolDictionary", back_populates="document", cascade="all, delete-orphan"
    )

    # Indexes
    __table_args__ = (
        Index("idx_documents_status", "status"),
        Index("idx_documents_api_key_id", "api_key_id"),
        Index("idx_documents_tenant_node", "tenant_id", "node_id"),
        Index("idx_documents_approval_status", "approval_status"),
    )


class ExtractedRecord(TenantIsolationMixin, Base):
    """Extracted record model for parsed Excel table rows."""

    __tablename__ = "extracted_records"

    id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True), primary_key=True, default=uuid4
    )
    document_id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("documents.id", ondelete="CASCADE"),
        nullable=False,
    )
    sheet_name: Mapped[str] = mapped_column(String(255), nullable=False)
    table_title: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    row_number: Mapped[int] = mapped_column(Integer, nullable=False)
    col_range: Mapped[Optional[str]] = mapped_column(String(50), nullable=True)
    content: Mapped[dict] = mapped_column(JSONB, nullable=False)
    resolved_content: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    headers: Mapped[Optional[list]] = mapped_column(JSONB, nullable=True)
    tags: Mapped[Optional[list]] = mapped_column(
        JSONB, nullable=True, server_default="[]"
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

    # Relationships
    document: Mapped["Document"] = relationship(
        "Document", back_populates="extracted_records"
    )
    source_cross_references: Mapped[list["CrossReference"]] = relationship(
        "CrossReference",
        foreign_keys="CrossReference.source_record_id",
        back_populates="source_record",
        cascade="all, delete-orphan",
    )
    target_cross_references: Mapped[list["CrossReference"]] = relationship(
        "CrossReference",
        foreign_keys="CrossReference.target_record_id",
        back_populates="target_record",
    )

    # Indexes - GIN for JSONB and ARRAY, B-tree for lookups
    # UniqueConstraint for ON CONFLICT upsert in StructuredStore
    __table_args__ = (
        Index("idx_extracted_records_document_id", "document_id"),
        Index("idx_extracted_records_sheet_name", "sheet_name"),
        Index(
            "idx_extracted_records_content",
            "content",
            postgresql_using="gin",
        ),
        Index(
            "idx_extracted_records_resolved_content",
            "resolved_content",
            postgresql_using="gin",
        ),
        Index(
            "idx_extracted_records_tags",
            "tags",
            postgresql_using="gin",
        ),
        Index("idx_extracted_records_tenant_node", "tenant_id", "node_id"),
    )


class SymbolDictionary(TenantIsolationMixin, Base):
    """Symbol dictionary model for symbol-to-meaning mappings."""

    __tablename__ = "symbol_dictionaries"

    id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True), primary_key=True, default=uuid4
    )
    document_id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("documents.id", ondelete="CASCADE"),
        nullable=False,
    )
    sheet_name: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    symbol: Mapped[str] = mapped_column(String(50), nullable=False)
    meaning: Mapped[str] = mapped_column(Text, nullable=False)
    context: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

    # Relationships
    document: Mapped["Document"] = relationship(
        "Document", back_populates="symbol_dictionaries"
    )

    # Indexes
    __table_args__ = (
        Index("idx_symbol_dictionaries_document_id", "document_id"),
        Index("idx_symbol_dictionaries_symbol", "symbol"),
        Index("idx_symbol_dict_tenant_node", "tenant_id", "node_id"),
    )


class CrossReference(Base):
    """Cross-reference model for links between extracted records."""

    __tablename__ = "cross_references"

    id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True), primary_key=True, default=uuid4
    )
    source_record_id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("extracted_records.id", ondelete="CASCADE"),
        nullable=False,
    )
    target_record_id: Mapped[Optional[UUID]] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("extracted_records.id", ondelete="SET NULL"),
        nullable=True,
    )
    reference_text: Mapped[str] = mapped_column(Text, nullable=False)
    reference_type: Mapped[Optional[str]] = mapped_column(String(50), nullable=True)
    resolved: Mapped[bool] = mapped_column(Boolean, default=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

    # Relationships
    source_record: Mapped["ExtractedRecord"] = relationship(
        "ExtractedRecord",
        foreign_keys=[source_record_id],
        back_populates="source_cross_references",
    )
    target_record: Mapped[Optional["ExtractedRecord"]] = relationship(
        "ExtractedRecord",
        foreign_keys=[target_record_id],
        back_populates="target_cross_references",
    )


class QueryLog(TenantIsolationMixin, Base):
    """Query log model for tracking API queries and responses."""

    __tablename__ = "query_logs"

    id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True), primary_key=True, default=uuid4
    )
    api_key_id: Mapped[Optional[UUID]] = mapped_column(
        UUID(as_uuid=True), ForeignKey("api_keys.id"), nullable=True
    )
    query_text: Mapped[str] = mapped_column(Text, nullable=False)
    answer_text: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    confidence: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
    sources: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    processing_time_ms: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

    # Relationships
    api_key: Mapped[Optional["ApiKey"]] = relationship(
        "ApiKey", back_populates="query_logs"
    )

    # Indexes - GIN for JSONB
    __table_args__ = (
        Index(
            "idx_query_logs_sources",
            "sources",
            postgresql_using="gin",
        ),
        Index("idx_query_logs_tenant_node", "tenant_id", "node_id"),
    )


class DomainTermDictionary(TenantIsolationMixin, Base):
    """Domain term dictionary with multilingual support."""

    __tablename__ = "domain_term_dictionary"

    id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True), primary_key=True, default=uuid4
    )
    term_original: Mapped[str] = mapped_column(String(255), nullable=False)
    term_ja: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    term_en: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    term_vi: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    source_language: Mapped[str] = mapped_column(String(10), nullable=False)
    domain_category: Mapped[str] = mapped_column(String(100), default="general")
    source_document_id: Mapped[Optional[UUID]] = mapped_column(
        UUID(as_uuid=True), ForeignKey("documents.id", ondelete="CASCADE"), nullable=True
    )
    translation_failed: Mapped[bool] = mapped_column(Boolean, default=False)
    confidence: Mapped[float] = mapped_column(Float, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

    # Per-node uniqueness: same term may exist across different nodes.
    __table_args__ = (
        UniqueConstraint("term_original", "node_id", name="uq_term_original_node"),
        CheckConstraint(
            "confidence >= 0 AND confidence <= 1",
            name="ck_domain_term_confidence_range",
        ),
        Index("idx_domain_dict_term_ja", "term_ja"),
        Index("idx_domain_dict_term_en", "term_en"),
        Index("idx_domain_dict_term_vi", "term_vi"),
        Index("idx_domain_terms_tenant_node", "tenant_id", "node_id"),
    )