"""Modèle de données PostgreSQL (voir docs/MODELE_DONNEES.md)."""
from __future__ import annotations

import uuid
from datetime import date, datetime
from decimal import Decimal
from typing import Any

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

BILAN_STATUSES = ("brouillon", "a_verifier", "valide", "rouvert", "archive")
STATUS_LABELS = {
    "nouveau": "Non commencé",
    "brouillon": "Brouillon",
    "a_verifier": "À vérifier",
    "valide": "Validé",
    "rouvert": "Rouvert",
    "archive": "Archivé",
}
IMPORT_STATUSES = ("en_attente", "en_cours", "analyse", "echec", "applique", "annule", "rejete")
JOB_STATUSES = ("en_attente", "en_cours", "termine", "echec")


def _now() -> Any:
    return func.now()


class Base(DeclarativeBase):
    type_annotation_map = {dict[str, Any]: JSONB, list[Any]: JSONB}


# --- Comptes, rôles, permissions, sessions ---------------------------------------------------

class Role(Base):
    __tablename__ = "roles"
    code: Mapped[str] = mapped_column(String(32), primary_key=True)
    label: Mapped[str] = mapped_column(String(80))


class Permission(Base):
    __tablename__ = "permissions"
    code: Mapped[str] = mapped_column(String(48), primary_key=True)
    label: Mapped[str] = mapped_column(String(160))


class RolePermission(Base):
    __tablename__ = "role_permissions"
    role_code: Mapped[str] = mapped_column(ForeignKey("roles.code", ondelete="CASCADE"), primary_key=True)
    permission_code: Mapped[str] = mapped_column(
        ForeignKey("permissions.code", ondelete="CASCADE"), primary_key=True
    )


class User(Base):
    __tablename__ = "users"
    __table_args__ = (CheckConstraint("username = lower(username)", name="ck_users_username_lower"),)
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    username: Mapped[str] = mapped_column(String(64), unique=True)
    display_name: Mapped[str] = mapped_column(String(120))
    role_code: Mapped[str] = mapped_column(ForeignKey("roles.code"))
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    can_export: Mapped[bool] = mapped_column(Boolean, default=False)
    password_hash: Mapped[str] = mapped_column(String(255))
    must_change_password: Mapped[bool] = mapped_column(Boolean, default=True)
    password_changed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    mfa_enabled: Mapped[bool] = mapped_column(Boolean, default=False)
    mfa_secret_enc: Mapped[bytes | None] = mapped_column(LargeBinary)
    mfa_pending_secret_enc: Mapped[bytes | None] = mapped_column(LargeBinary)
    mfa_last_step: Mapped[int | None] = mapped_column(BigInteger)
    last_login_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())
    created_by: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
    deactivated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))


class RecoveryCode(Base):
    __tablename__ = "recovery_codes"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), index=True)
    code_hash: Mapped[str] = mapped_column(String(64))
    used_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())


class UserSession(Base):
    __tablename__ = "sessions"
    # Seule l'empreinte SHA-256 du jeton est stockée : une fuite de la base ne donne pas de session.
    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), index=True)
    csrf_token: Mapped[str] = mapped_column(String(64))
    mfa_verified: Mapped[bool] = mapped_column(Boolean, default=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())
    last_seen_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    revoked_reason: Mapped[str | None] = mapped_column(String(80))
    ip: Mapped[str | None] = mapped_column(String(64))
    user_agent: Mapped[str | None] = mapped_column(String(200))


class AuthThrottle(Base):
    __tablename__ = "auth_throttle"
    key: Mapped[str] = mapped_column(String(160), primary_key=True)
    failures: Mapped[int] = mapped_column(Integer, default=0)
    first_failure_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())
    locked_until: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    lock_count: Mapped[int] = mapped_column(Integer, default=0)


# --- Indicateurs -------------------------------------------------------------------------------

class IndicatorCategory(Base):
    __tablename__ = "indicator_categories"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    label: Mapped[str] = mapped_column(String(120))
    position: Mapped[int] = mapped_column(Integer, default=0)


class Indicator(Base):
    __tablename__ = "indicators"
    __table_args__ = (
        CheckConstraint("detail_kind IS NULL OR detail_kind IN ('taux','vitesse')", name="ck_ind_detail"),
        CheckConstraint(
            "direction IS NULL OR direction IN ('hausse_favorable','baisse_favorable')", name="ck_ind_direction"
        ),
    )
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    code: Mapped[str] = mapped_column(String(48), unique=True)
    label: Mapped[str] = mapped_column(String(160))
    position: Mapped[int] = mapped_column(Integer)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    category_id: Mapped[int | None] = mapped_column(ForeignKey("indicator_categories.id"))
    detail_kind: Mapped[str | None] = mapped_column(String(16))
    detail_unit: Mapped[str] = mapped_column(String(32), default="")
    direction: Mapped[str | None] = mapped_column(String(24))
    warn_max: Mapped[int | None] = mapped_column(Integer)
    include_in_total: Mapped[bool] = mapped_column(Boolean, default=True)
    aliases: Mapped[list[Any]] = mapped_column(JSONB, default=list)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())


# --- Bilans ------------------------------------------------------------------------------------

class Bilan(Base):
    __tablename__ = "bilans"
    __table_args__ = (
        UniqueConstraint("iso_year", "iso_week", name="uq_bilans_week"),
        CheckConstraint(f"status IN {BILAN_STATUSES}", name="ck_bilans_status"),
        CheckConstraint("iso_week BETWEEN 1 AND 53", name="ck_bilans_week"),
        CheckConstraint("period_end = period_start + 6", name="ck_bilans_period"),
    )
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    iso_year: Mapped[int] = mapped_column(Integer)
    iso_week: Mapped[int] = mapped_column(Integer)
    period_start: Mapped[date] = mapped_column(Date)
    period_end: Mapped[date] = mapped_column(Date)
    status: Mapped[str] = mapped_column(String(16), default="brouillon")
    revision: Mapped[int] = mapped_column(Integer, default=1)
    current_version: Mapped[int] = mapped_column(Integer, default=0)
    is_demo: Mapped[bool] = mapped_column(Boolean, default=False)
    created_by: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())
    updated_by: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())
    validated_by: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
    validated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    reopened_by: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
    reopened_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    reopen_reason: Mapped[str | None] = mapped_column(Text)
    archived_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    values: Mapped[list[BilanValue]] = relationship(back_populates="bilan", cascade="all, delete-orphan")


class BilanValue(Base):
    __tablename__ = "bilan_values"
    __table_args__ = (
        UniqueConstraint("bilan_id", "indicator_id", name="uq_values_bilan_indicator"),
        CheckConstraint("value IS NULL OR (value >= 0 AND value <= 100000)", name="ck_values_value"),
        CheckConstraint(
            "source_kind IN ('manuel','collage','import','reprise','restauration','demo')", name="ck_values_source"
        ),
    )
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    bilan_id: Mapped[int] = mapped_column(ForeignKey("bilans.id", ondelete="CASCADE"), index=True)
    indicator_id: Mapped[int] = mapped_column(ForeignKey("indicators.id"))
    # NULL = non renseigné ; 0 = aucune infraction relevée. Jamais de conversion implicite.
    value: Mapped[int | None] = mapped_column(Integer)
    observation: Mapped[str] = mapped_column(Text, default="")
    label_snapshot: Mapped[str] = mapped_column(String(160))
    source_kind: Mapped[str] = mapped_column(String(16), default="manuel")
    import_id: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True), ForeignKey("imports.id"))
    source_locator: Mapped[str | None] = mapped_column(String(160))
    source_ref: Mapped[str | None] = mapped_column(String(160))
    source_bbox: Mapped[dict[str, Any] | None] = mapped_column(JSONB)
    confidence: Mapped[float | None] = mapped_column(Float)
    warnings: Mapped[list[Any]] = mapped_column(JSONB, default=list)
    created_by: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())
    updated_by: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())

    bilan: Mapped[Bilan] = relationship(back_populates="values")
    details: Mapped[list[ValueDetail]] = relationship(
        cascade="all, delete-orphan", order_by="ValueDetail.position"
    )


class ValueDetail(Base):
    """Taux retenus (alcoolémie) ou vitesses retenues : informations structurées complémentaires."""
    __tablename__ = "value_details"
    __table_args__ = (
        CheckConstraint("kind IN ('taux','vitesse')", name="ck_details_kind"),
        CheckConstraint("amount >= 0", name="ck_details_amount"),
    )
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    value_id: Mapped[int] = mapped_column(ForeignKey("bilan_values.id", ondelete="CASCADE"), index=True)
    kind: Mapped[str] = mapped_column(String(16))
    amount: Mapped[Decimal] = mapped_column(Numeric(10, 3))
    unit: Mapped[str] = mapped_column(String(32), default="")
    position: Mapped[int] = mapped_column(Integer, default=0)


class BilanVersion(Base):
    """Version immuable (déclencheur PostgreSQL) créée à chaque validation."""
    __tablename__ = "bilan_versions"
    __table_args__ = (UniqueConstraint("bilan_id", "version_no", name="uq_versions_no"),)
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    bilan_id: Mapped[int] = mapped_column(ForeignKey("bilans.id"), index=True)
    version_no: Mapped[int] = mapped_column(Integer)
    snapshot: Mapped[dict[str, Any]] = mapped_column(JSONB)
    snapshot_sha256: Mapped[str] = mapped_column(String(64))
    comment: Mapped[str | None] = mapped_column(Text)
    is_demo: Mapped[bool] = mapped_column(Boolean, default=False)
    created_by: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())


class ValueHistory(Base):
    __tablename__ = "value_history"
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    bilan_id: Mapped[int] = mapped_column(ForeignKey("bilans.id", ondelete="CASCADE"), index=True)
    indicator_id: Mapped[int] = mapped_column(ForeignKey("indicators.id"))
    field: Mapped[str] = mapped_column(String(16))
    old_value: Mapped[str | None] = mapped_column(Text)
    new_value: Mapped[str | None] = mapped_column(Text)
    source_kind: Mapped[str] = mapped_column(String(16))
    import_id: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True))
    reason: Mapped[str | None] = mapped_column(Text)
    user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
    at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())
    last_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())


class Comment(Base):
    __tablename__ = "comments"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    bilan_id: Mapped[int] = mapped_column(ForeignKey("bilans.id", ondelete="CASCADE"), index=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    body: Mapped[str] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())


# --- Imports -----------------------------------------------------------------------------------

class ImportFile(Base):
    __tablename__ = "imports"
    __table_args__ = (CheckConstraint(f"status IN {IMPORT_STATUSES}", name="ck_imports_status"),)
    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    original_name: Mapped[str] = mapped_column(String(160))
    stored_name: Mapped[str] = mapped_column(String(80))
    size: Mapped[int] = mapped_column(Integer)
    sha256: Mapped[str] = mapped_column(String(64), index=True)
    detected_format: Mapped[str] = mapped_column(String(16))
    detected_mime: Mapped[str] = mapped_column(String(100))
    declared_mime: Mapped[str | None] = mapped_column(String(100))
    status: Mapped[str] = mapped_column(String(16), default="en_attente")
    av_status: Mapped[str] = mapped_column(String(16), default="non_execute")
    error_message: Mapped[str | None] = mapped_column(Text)
    analysis: Mapped[dict[str, Any]] = mapped_column(JSONB, default=dict)
    target_iso_year: Mapped[int | None] = mapped_column(Integer)
    target_iso_week: Mapped[int | None] = mapped_column(Integer)
    duplicate_of: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True))
    uploaded_by: Mapped[int] = mapped_column(ForeignKey("users.id"))
    uploaded_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())
    finished_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    purged_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))


class ImportSource(Base):
    """Feuille de classeur, page de PDF ou image d'un import."""
    __tablename__ = "import_sources"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    import_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("imports.id", ondelete="CASCADE"), index=True)
    kind: Mapped[str] = mapped_column(String(16))
    index: Mapped[int] = mapped_column(Integer)
    name: Mapped[str] = mapped_column(String(160))
    hidden: Mapped[bool] = mapped_column(Boolean, default=False)
    width: Mapped[int | None] = mapped_column(Integer)
    height: Mapped[int | None] = mapped_column(Integer)
    image_name: Mapped[str | None] = mapped_column(String(80))
    grid: Mapped[list[Any] | None] = mapped_column(JSONB)
    meta: Mapped[dict[str, Any]] = mapped_column(JSONB, default=dict)


class ImportProposal(Base):
    __tablename__ = "import_proposals"
    __table_args__ = (
        CheckConstraint("decision IN ('a_verifier','accepte','ignore')", name="ck_proposals_decision"),
    )
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    import_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("imports.id", ondelete="CASCADE"), index=True)
    source_id: Mapped[int | None] = mapped_column(ForeignKey("import_sources.id", ondelete="CASCADE"))
    row_index: Mapped[int] = mapped_column(Integer)
    raw_label: Mapped[str] = mapped_column(Text, default="")
    indicator_id: Mapped[int | None] = mapped_column(ForeignKey("indicators.id"))
    match_score: Mapped[float] = mapped_column(Float, default=0)
    raw_value: Mapped[str] = mapped_column(String(200), default="")
    value: Mapped[int | None] = mapped_column(Integer)
    value_error: Mapped[str | None] = mapped_column(String(200))
    observation: Mapped[str] = mapped_column(Text, default="")
    details: Mapped[list[Any]] = mapped_column(JSONB, default=list)
    label_ref: Mapped[str | None] = mapped_column(String(40))
    value_ref: Mapped[str | None] = mapped_column(String(40))
    observation_ref: Mapped[str | None] = mapped_column(String(40))
    bbox: Mapped[dict[str, Any] | None] = mapped_column(JSONB)
    value_bbox: Mapped[dict[str, Any] | None] = mapped_column(JSONB)
    confidence: Mapped[float] = mapped_column(Float, default=0)
    warnings: Mapped[list[Any]] = mapped_column(JSONB, default=list)
    decision: Mapped[str] = mapped_column(String(16), default="a_verifier")
    edited: Mapped[bool] = mapped_column(Boolean, default=False)
    updated_by: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
    updated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))


class ImportApplication(Base):
    """Validation humaine d'un import : instantané avant/après pour annulation complète."""
    __tablename__ = "import_applications"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    import_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("imports.id"), index=True)
    bilan_id: Mapped[int] = mapped_column(ForeignKey("bilans.id", ondelete="CASCADE"))
    before_snapshot: Mapped[dict[str, Any]] = mapped_column(JSONB)
    after_snapshot: Mapped[dict[str, Any]] = mapped_column(JSONB)
    report: Mapped[dict[str, Any]] = mapped_column(JSONB, default=dict)
    applied_by: Mapped[int] = mapped_column(ForeignKey("users.id"))
    applied_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())
    reverted_by: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
    reverted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))


class Job(Base):
    __tablename__ = "jobs"
    __table_args__ = (CheckConstraint(f"status IN {JOB_STATUSES}", name="ck_jobs_status"),)
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    kind: Mapped[str] = mapped_column(String(32))
    payload: Mapped[dict[str, Any]] = mapped_column(JSONB, default=dict)
    status: Mapped[str] = mapped_column(String(16), default="en_attente", index=True)
    progress: Mapped[int] = mapped_column(Integer, default=0)
    message: Mapped[str | None] = mapped_column(Text)
    attempts: Mapped[int] = mapped_column(Integer, default=0)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())
    started_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    finished_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))


# --- Exports, paramètres, audit -----------------------------------------------------------------

class ExportLog(Base):
    __tablename__ = "exports"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    bilan_id: Mapped[int | None] = mapped_column(ForeignKey("bilans.id", ondelete="SET NULL"))
    kind: Mapped[str] = mapped_column(String(8))
    options: Mapped[dict[str, Any]] = mapped_column(JSONB, default=dict)
    pages: Mapped[int | None] = mapped_column(Integer)
    size: Mapped[int] = mapped_column(Integer)
    sha256: Mapped[str] = mapped_column(String(64))
    created_by: Mapped[int] = mapped_column(ForeignKey("users.id"))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())


class Setting(Base):
    __tablename__ = "settings"
    key: Mapped[str] = mapped_column(String(64), primary_key=True)
    value: Mapped[Any] = mapped_column(JSONB)
    updated_by: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=_now())


class AuditLog(Base):
    """Journal en ajout seul et chaîné (hash de la ligne précédente)."""
    __tablename__ = "audit_log"
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    at: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
    username: Mapped[str | None] = mapped_column(String(64))
    action: Mapped[str] = mapped_column(String(64), index=True)
    target_type: Mapped[str | None] = mapped_column(String(32))
    target_id: Mapped[str | None] = mapped_column(String(64))
    ip: Mapped[str | None] = mapped_column(String(64))
    details: Mapped[dict[str, Any]] = mapped_column(JSONB, default=dict)
    prev_hash: Mapped[str] = mapped_column(String(64))
    hash: Mapped[str] = mapped_column(String(64))