"""
Admin API SQLModel Database Models

Defines the database schema for admin document management, annotations, and training tasks.
Includes batch upload support, training document links, and annotation history.
"""

from datetime import datetime
from typing import Any
from uuid import UUID, uuid4

from sqlmodel import Field, SQLModel, Column, JSON

# Import field mappings from single source of truth
from shared.fields import CSV_TO_CLASS_MAPPING, FIELD_CLASSES, FIELD_CLASS_IDS


# =============================================================================
# Core Models
# =============================================================================


class AdminToken(SQLModel, table=True):
    """Admin authentication token."""

    __tablename__ = "admin_tokens"

    token: str = Field(primary_key=True, max_length=255)
    name: str = Field(max_length=255)
    is_active: bool = Field(default=True)
    created_at: datetime = Field(default_factory=datetime.utcnow)
    last_used_at: datetime | None = Field(default=None)
    expires_at: datetime | None = Field(default=None)


class AdminDocument(SQLModel, table=True):
    """Document uploaded for labeling/annotation."""

    __tablename__ = "admin_documents"

    document_id: UUID = Field(default_factory=uuid4, primary_key=True)
    admin_token: str | None = Field(default=None, foreign_key="admin_tokens.token", max_length=255, index=True)
    filename: str = Field(max_length=255)
    file_size: int
    content_type: str = Field(max_length=100)
    file_path: str = Field(max_length=512)  # Path to stored file
    page_count: int = Field(default=1)
    status: str = Field(default="pending", max_length=20, index=True)
    # Status: pending, auto_labeling, labeled, exported
    auto_label_status: str | None = Field(default=None, max_length=20)
    # Auto-label status: running, completed, failed
    auto_label_error: str | None = Field(default=None)
    # v2: Upload source tracking
    upload_source: str = Field(default="ui", max_length=20)
    # Upload source: ui, api
    batch_id: UUID | None = Field(default=None, index=True)
    # Link to batch upload (if uploaded via ZIP)
    group_key: str | None = Field(default=None, max_length=255, index=True)
    # User-defined grouping key for document organization
    category: str = Field(default="invoice", max_length=100, index=True)
    # Document category for training different models (e.g., invoice, letter, receipt)
    csv_field_values: dict[str, Any] | None = Field(default=None, sa_column=Column(JSON))
    # Original CSV values for reference
    auto_label_queued_at: datetime | None = Field(default=None)
    # When auto-label was queued
    annotation_lock_until: datetime | None = Field(default=None)
    # Lock for manual annotation while auto-label runs
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)


class AdminAnnotation(SQLModel, table=True):
    """Annotation for a document (bounding box + label)."""

    __tablename__ = "admin_annotations"

    annotation_id: UUID = Field(default_factory=uuid4, primary_key=True)
    document_id: UUID = Field(foreign_key="admin_documents.document_id", index=True)
    page_number: int = Field(default=1)  # 1-indexed
    class_id: int  # 0-9 for invoice fields
    class_name: str = Field(max_length=50)  # e.g., "invoice_number"
    # Bounding box (normalized 0-1 coordinates)
    x_center: float
    y_center: float
    width: float
    height: float
    # Original pixel coordinates (for display)
    bbox_x: int
    bbox_y: int
    bbox_width: int
    bbox_height: int
    # OCR extracted text (if available)
    text_value: str | None = Field(default=None)
    confidence: float | None = Field(default=None)
    # Source: manual, auto, imported
    source: str = Field(default="manual", max_length=20, index=True)
    # v2: Verification fields
    is_verified: bool = Field(default=False, index=True)
    verified_at: datetime | None = Field(default=None)
    verified_by: str | None = Field(default=None, max_length=255)
    # v2: Override tracking
    override_source: str | None = Field(default=None, max_length=20)
    # If this annotation overrides another: 'auto' or 'imported'
    original_annotation_id: UUID | None = Field(default=None)
    # Reference to the annotation this overrides
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)


class TrainingTask(SQLModel, table=True):
    """Training/fine-tuning task."""

    __tablename__ = "training_tasks"

    task_id: UUID = Field(default_factory=uuid4, primary_key=True)
    admin_token: str = Field(foreign_key="admin_tokens.token", max_length=255, index=True)
    name: str = Field(max_length=255)
    description: str | None = Field(default=None)
    status: str = Field(default="pending", max_length=20, index=True)
    # Status: pending, scheduled, running, completed, failed, cancelled
    task_type: str = Field(default="train", max_length=20)
    # Task type: train, finetune
    dataset_id: UUID | None = Field(default=None, foreign_key="training_datasets.dataset_id", index=True)
    # Training configuration
    config: dict[str, Any] | None = Field(default=None, sa_column=Column(JSON))
    # Schedule settings
    scheduled_at: datetime | None = Field(default=None)
    cron_expression: str | None = Field(default=None, max_length=50)
    is_recurring: bool = Field(default=False)
    # Execution details
    started_at: datetime | None = Field(default=None)
    completed_at: datetime | None = Field(default=None)
    error_message: str | None = Field(default=None)
    # Result metrics
    result_metrics: dict[str, Any] | None = Field(default=None, sa_column=Column(JSON))
    model_path: str | None = Field(default=None, max_length=512)
    # v2: Document count and extracted metrics
    document_count: int = Field(default=0)
    # Count of documents used in training
    metrics_mAP: float | None = Field(default=None, index=True)
    metrics_precision: float | None = Field(default=None)
    metrics_recall: float | None = Field(default=None)
    # Extracted metrics for easy querying
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)


class TrainingLog(SQLModel, table=True):
    """Training log entry."""

    __tablename__ = "training_logs"

    log_id: int | None = Field(default=None, primary_key=True)
    task_id: UUID = Field(foreign_key="training_tasks.task_id", index=True)
    level: str = Field(max_length=20)  # INFO, WARNING, ERROR
    message: str
    details: dict[str, Any] | None = Field(default=None, sa_column=Column(JSON))
    created_at: datetime = Field(default_factory=datetime.utcnow, index=True)


# =============================================================================
# Batch Upload Models (v2)
# =============================================================================


class BatchUpload(SQLModel, table=True):
    """Batch upload of multiple documents via ZIP file."""

    __tablename__ = "batch_uploads"

    batch_id: UUID = Field(default_factory=uuid4, primary_key=True)
    admin_token: str = Field(foreign_key="admin_tokens.token", max_length=255, index=True)
    filename: str = Field(max_length=255)  # ZIP filename
    file_size: int
    upload_source: str = Field(default="ui", max_length=20)
    # Upload source: ui, api
    status: str = Field(default="processing", max_length=20, index=True)
    # Status: processing, completed, partial, failed
    total_files: int = Field(default=0)
    processed_files: int = Field(default=0)
    # Number of files processed so far
    successful_files: int = Field(default=0)
    failed_files: int = Field(default=0)
    csv_filename: str | None = Field(default=None, max_length=255)
    # CSV file used for auto-labeling
    csv_row_count: int | None = Field(default=None)
    error_message: str | None = Field(default=None)
    created_at: datetime = Field(default_factory=datetime.utcnow)
    completed_at: datetime | None = Field(default=None)


class BatchUploadFile(SQLModel, table=True):
    """Individual file within a batch upload."""

    __tablename__ = "batch_upload_files"

    file_id: UUID = Field(default_factory=uuid4, primary_key=True)
    batch_id: UUID = Field(foreign_key="batch_uploads.batch_id", index=True)
    filename: str = Field(max_length=255)  # PDF filename within ZIP
    document_id: UUID | None = Field(default=None)
    # Link to created AdminDocument (if successful)
    status: str = Field(default="pending", max_length=20, index=True)
    # Status: pending, processing, completed, failed, skipped
    error_message: str | None = Field(default=None)
    annotation_count: int = Field(default=0)
    # Number of annotations created for this file
    csv_row_data: dict[str, Any] | None = Field(default=None, sa_column=Column(JSON))
    # CSV row data for this file (if available)
    created_at: datetime = Field(default_factory=datetime.utcnow)
    processed_at: datetime | None = Field(default=None)


# =============================================================================
# Training Document Link (v2)
# =============================================================================


class TrainingDataset(SQLModel, table=True):
    """Training dataset containing selected documents with train/val/test splits."""

    __tablename__ = "training_datasets"

    dataset_id: UUID = Field(default_factory=uuid4, primary_key=True)
    name: str = Field(max_length=255)
    description: str | None = Field(default=None)
    status: str = Field(default="building", max_length=20, index=True)
    # Status: building, ready, trained, archived, failed
    training_status: str | None = Field(default=None, max_length=20, index=True)
    # Training status: pending, scheduled, running, completed, failed, cancelled
    active_training_task_id: UUID | None = Field(default=None, index=True)
    train_ratio: float = Field(default=0.8)
    val_ratio: float = Field(default=0.1)
    seed: int = Field(default=42)
    total_documents: int = Field(default=0)
    total_images: int = Field(default=0)
    total_annotations: int = Field(default=0)
    dataset_path: str | None = Field(default=None, max_length=512)
    error_message: str | None = Field(default=None)
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)


class DatasetDocument(SQLModel, table=True):
    """Junction table linking datasets to documents with split assignment."""

    __tablename__ = "dataset_documents"

    id: UUID = Field(default_factory=uuid4, primary_key=True)
    dataset_id: UUID = Field(foreign_key="training_datasets.dataset_id", index=True)
    document_id: UUID = Field(foreign_key="admin_documents.document_id", index=True)
    split: str = Field(max_length=10)  # train, val, test
    page_count: int = Field(default=0)
    annotation_count: int = Field(default=0)
    created_at: datetime = Field(default_factory=datetime.utcnow)


class TrainingDocumentLink(SQLModel, table=True):
    """Junction table linking training tasks to documents."""

    __tablename__ = "training_document_links"

    link_id: UUID = Field(default_factory=uuid4, primary_key=True)
    task_id: UUID = Field(foreign_key="training_tasks.task_id", index=True)
    document_id: UUID = Field(foreign_key="admin_documents.document_id", index=True)
    annotation_snapshot: dict[str, Any] | None = Field(default=None, sa_column=Column(JSON))
    # Snapshot of annotations at training time (includes count, verified count, etc.)
    created_at: datetime = Field(default_factory=datetime.utcnow)


# =============================================================================
# Model Version Management
# =============================================================================


class ModelVersion(SQLModel, table=True):
    """Model version for inference deployment."""

    __tablename__ = "model_versions"

    version_id: UUID = Field(default_factory=uuid4, primary_key=True)
    version: str = Field(max_length=50, index=True)
    # Semantic version e.g., "1.0.0", "2.1.0"
    name: str = Field(max_length=255)
    description: str | None = Field(default=None)
    model_path: str = Field(max_length=512)
    # Path to the model weights file
    status: str = Field(default="inactive", max_length=20, index=True)
    # Status: active, inactive, archived
    is_active: bool = Field(default=False, index=True)
    # Only one version can be active at a time for inference

    # Model lineage
    model_type: str = Field(default="base", max_length=20, index=True)
    # "base" = trained from pretrained YOLO, "finetune" = fine-tuned from base model
    base_model_version_id: UUID | None = Field(default=None, index=True)
    # Points to the base model this was fine-tuned from (None for base models)
    base_training_dataset_id: UUID | None = Field(default=None, index=True)
    # The dataset used for original base training (for data mixing old samples)
    gating_status: str = Field(default="pending", max_length=20, index=True)
    # Deployment gating: pending, pass, review, reject, skipped

    # Training association
    task_id: UUID | None = Field(default=None, foreign_key="training_tasks.task_id", index=True)
    dataset_id: UUID | None = Field(default=None, foreign_key="training_datasets.dataset_id", index=True)

    # Training metrics
    metrics_mAP: float | None = Field(default=None)
    metrics_precision: float | None = Field(default=None)
    metrics_recall: float | None = Field(default=None)
    document_count: int = Field(default=0)
    # Number of documents used in training

    # Training configuration snapshot
    training_config: dict[str, Any] | None = Field(default=None, sa_column=Column(JSON))
    # Snapshot of epochs, batch_size, etc.

    # File info
    file_size: int | None = Field(default=None)
    # Model file size in bytes

    # Timestamps
    trained_at: datetime | None = Field(default=None)
    # When training completed
    activated_at: datetime | None = Field(default=None)
    # When this version was last activated
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)


# =============================================================================
# Fine-Tune Pool
# =============================================================================


class FineTunePoolEntry(SQLModel, table=True):
    """Document in the fine-tune pool for incremental model improvement."""

    __tablename__ = "finetune_pool_entries"

    entry_id: UUID = Field(default_factory=uuid4, primary_key=True)
    document_id: UUID = Field(foreign_key="admin_documents.document_id", index=True)
    added_by: str | None = Field(default=None, max_length=255)
    reason: str | None = Field(default=None, max_length=255)
    # Reason: user_reported_failure, manual_addition
    is_verified: bool = Field(default=False, index=True)
    verified_at: datetime | None = Field(default=None)
    verified_by: str | None = Field(default=None, max_length=255)
    created_at: datetime = Field(default_factory=datetime.utcnow)


# =============================================================================
# Deployment Gating
# =============================================================================


class GatingResult(SQLModel, table=True):
    """Model deployment gating validation result."""

    __tablename__ = "gating_results"

    result_id: UUID = Field(default_factory=uuid4, primary_key=True)
    model_version_id: UUID = Field(foreign_key="model_versions.version_id", index=True)
    task_id: UUID | None = Field(default=None, foreign_key="training_tasks.task_id")

    # Gate 1: Regression validation (original test set mAP)
    gate1_status: str = Field(default="pending", max_length=20)
    # pending, pass, review, reject
    gate1_original_mAP: float | None = Field(default=None)
    gate1_new_mAP: float | None = Field(default=None)
    gate1_mAP_drop: float | None = Field(default=None)

    # Gate 2: New sample validation (detection rate on pool docs)
    gate2_status: str = Field(default="pending", max_length=20)
    gate2_detection_rate: float | None = Field(default=None)
    gate2_total_samples: int | None = Field(default=None)
    gate2_detected_samples: int | None = Field(default=None)

    # Overall
    overall_status: str = Field(default="pending", max_length=20)
    # pending, pass, review, reject
    reviewer_notes: str | None = Field(default=None)
    reviewed_by: str | None = Field(default=None, max_length=255)
    reviewed_at: datetime | None = Field(default=None)

    created_at: datetime = Field(default_factory=datetime.utcnow)


# =============================================================================
# Annotation History (v2)
# =============================================================================


class AnnotationHistory(SQLModel, table=True):
    """History of annotation changes (for override tracking)."""

    __tablename__ = "annotation_history"

    history_id: UUID = Field(default_factory=uuid4, primary_key=True)
    annotation_id: UUID = Field(foreign_key="admin_annotations.annotation_id", index=True)
    document_id: UUID = Field(foreign_key="admin_documents.document_id", index=True)
    # Change action: created, updated, deleted, override
    action: str = Field(max_length=20, index=True)
    # Previous value (for updates/deletes)
    previous_value: dict[str, Any] | None = Field(default=None, sa_column=Column(JSON))
    # New value (for creates/updates)
    new_value: dict[str, Any] | None = Field(default=None, sa_column=Column(JSON))
    # Change metadata
    changed_by: str | None = Field(default=None, max_length=255)
    # User/token who made the change
    change_reason: str | None = Field(default=None)
    # Optional reason for change
    created_at: datetime = Field(default_factory=datetime.utcnow, index=True)


# FIELD_CLASSES and FIELD_CLASS_IDS are now imported from shared.fields
# This ensures consistency with the trained YOLO model


# Read-only models for API responses
class AdminDocumentRead(SQLModel):
    """Admin document response model."""

    document_id: UUID
    filename: str
    file_size: int
    content_type: str
    page_count: int
    status: str
    auto_label_status: str | None
    auto_label_error: str | None
    category: str = "invoice"
    created_at: datetime
    updated_at: datetime


class AdminAnnotationRead(SQLModel):
    """Admin annotation response model."""

    annotation_id: UUID
    document_id: UUID
    page_number: int
    class_id: int
    class_name: str
    x_center: float
    y_center: float
    width: float
    height: float
    bbox_x: int
    bbox_y: int
    bbox_width: int
    bbox_height: int
    text_value: str | None
    confidence: float | None
    source: str
    created_at: datetime


class TrainingTaskRead(SQLModel):
    """Training task response model."""

    task_id: UUID
    name: str
    description: str | None
    status: str
    task_type: str
    config: dict[str, Any] | None
    scheduled_at: datetime | None
    is_recurring: bool
    started_at: datetime | None
    completed_at: datetime | None
    error_message: str | None
    result_metrics: dict[str, Any] | None
    model_path: str | None
    dataset_id: UUID | None
    created_at: datetime


class TrainingDatasetRead(SQLModel):
    """Training dataset response model."""

    dataset_id: UUID
    name: str
    description: str | None
    status: str
    train_ratio: float
    val_ratio: float
    seed: int
    total_documents: int
    total_images: int
    total_annotations: int
    dataset_path: str | None
    error_message: str | None
    created_at: datetime
    updated_at: datetime


class DatasetDocumentRead(SQLModel):
    """Dataset document response model."""

    id: UUID
    dataset_id: UUID
    document_id: UUID
    split: str
    page_count: int
    annotation_count: int
