"""SQLAlchemy models for Sheetly."""

from __future__ import annotations

from datetime import datetime
from typing import Any, Optional

from sqlalchemy import (
    JSON,
    Boolean,
    DateTime,
    Float,
    ForeignKey,
    Integer,
    String,
    Text,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

from app.timeutil import utc_now


class Base(DeclarativeBase):
    """Declarative base."""


class Batch(Base):
    """Upload batch job."""

    __tablename__ = "batches"

    id: Mapped[str] = mapped_column(String(36), primary_key=True)
    status: Mapped[str] = mapped_column(
        String(32), default="pending"
    )
    total: Mapped[int] = mapped_column(Integer, default=0)
    processed: Mapped[int] = mapped_column(Integer, default=0)
    failed: Mapped[int] = mapped_column(Integer, default=0)
    created_at: Mapped[datetime] = mapped_column(
        DateTime, default=utc_now
    )
    finished_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime, nullable=True
    )


class Document(Base):
    """Processed document record."""

    __tablename__ = "documents"

    id: Mapped[str] = mapped_column(String(36), primary_key=True)
    batch_id: Mapped[Optional[str]] = mapped_column(
        String(36), ForeignKey("batches.id"), nullable=True
    )
    filename: Mapped[str] = mapped_column(String(512))
    file_path: Mapped[str] = mapped_column(String(1024))
    mime_type: Mapped[str] = mapped_column(String(128), default="")
    doc_type: Mapped[str] = mapped_column(
        String(64), default="other"
    )
    status: Mapped[str] = mapped_column(
        String(32), default="queued"
    )
    language: Mapped[str] = mapped_column(String(8), default="")
    extracted: Mapped[Optional[dict[str, Any]]] = mapped_column(
        JSON, nullable=True
    )
    confidence: Mapped[Optional[dict[str, Any]]] = mapped_column(
        JSON, nullable=True
    )
    issues: Mapped[Optional[list[Any]]] = mapped_column(
        JSON, nullable=True
    )
    is_duplicate: Mapped[bool] = mapped_column(
        Boolean, default=False
    )
    duplicate_of: Mapped[Optional[str]] = mapped_column(
        String(36), nullable=True
    )
    error: Mapped[Optional[str]] = mapped_column(
        Text, nullable=True
    )
    avg_confidence: Mapped[Optional[float]] = mapped_column(
        Float, nullable=True
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime, default=utc_now
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime, default=utc_now, onupdate=utc_now
    )


class DuplicateIndex(Base):
    """Index for invoice duplicate detection."""

    __tablename__ = "duplicate_index"

    id: Mapped[int] = mapped_column(
        Integer, primary_key=True, autoincrement=True
    )
    supplier: Mapped[str] = mapped_column(String(256), index=True)
    invoice_number: Mapped[str] = mapped_column(
        String(128), index=True
    )
    document_id: Mapped[str] = mapped_column(String(36))
    created_at: Mapped[datetime] = mapped_column(
        DateTime, default=utc_now
    )