# ============================================
# Projet   : API-KA
# Fichier  : src/database/models.py
# Node     : m3u96b
# Author   : Simon-Pierre Boucher
# Contact  : contact@spboucher.ai
# Date     : 2026-08-16
# ============================================
"""Modèles SQLAlchemy : 9 tables de données (schéma identique) + collection_runs.

Règle append-only : aucune suppression destructive, l'historique complet est conservé.
Déduplication par contrainte unique (source, date_key, checksum).
"""

from __future__ import annotations

import datetime
from typing import Any

from sqlalchemy import (
    BigInteger,
    Date,
    DateTime,
    Float,
    Index,
    Integer,
    Text,
    UniqueConstraint,
    func,
)
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import DeclarativeBase, Mapped, declared_attr, mapped_column
from sqlalchemy.types import JSON

import socket

from src.config import REQUIRED_NODE

CURRENT_NODE = REQUIRED_NODE or socket.gethostname().split(".")[0].lower()

# BIGSERIAL sur PostgreSQL ; INTEGER auto-incrémenté en dev SQLite.
BigIntPK = BigInteger().with_variant(Integer(), "sqlite")
# JSONB sur PostgreSQL ; JSON générique en dev SQLite.
JSONPayload = JSONB().with_variant(JSON(), "sqlite")


class Base(DeclarativeBase):
    """Base déclarative commune à tous les modèles API-KA."""


class DataRecordMixin:
    """Colonnes communes aux 9 tables de données des services KA."""

    id: Mapped[int] = mapped_column(BigIntPK, primary_key=True, autoincrement=True)
    payload: Mapped[dict[str, Any]] = mapped_column(JSONPayload, nullable=False)
    source: Mapped[str] = mapped_column(Text, nullable=False)
    collected_at: Mapped[datetime.datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )
    date_key: Mapped[datetime.date] = mapped_column(Date, nullable=False)
    checksum: Mapped[str] = mapped_column(Text, nullable=False)

    @declared_attr.directive
    def __table_args__(cls) -> tuple:  # noqa: N805 — API declared_attr de SQLAlchemy
        table = cls.__tablename__
        return (
            Index(f"ix_{table}_date_key", "date_key"),
            Index(f"ix_{table}_source_date_key", "source", "date_key"),
            UniqueConstraint(
                "source",
                "date_key",
                "checksum",
                name=f"uq_{table}_source_date_checksum",
            ),
        )


class LoukaData(DataRecordMixin, Base):
    """Données quotidiennes du service lou-ka."""

    __tablename__ = "louka_data"


class ImmokaData(DataRecordMixin, Base):
    """Données quotidiennes du service immo-ka."""

    __tablename__ = "immoka_data"


class FoodkaData(DataRecordMixin, Base):
    """Données quotidiennes du service food-ka."""

    __tablename__ = "foodka_data"


class AutokaData(DataRecordMixin, Base):
    """Données quotidiennes du service auto-ka."""

    __tablename__ = "autoka_data"


class FabrikaData(DataRecordMixin, Base):
    """Données quotidiennes du service fabri-ka."""

    __tablename__ = "fabrika_data"


class RestokaData(DataRecordMixin, Base):
    """Données quotidiennes du service resto-ka."""

    __tablename__ = "restoka_data"


class SortikaData(DataRecordMixin, Base):
    """Données quotidiennes du service sorti-ka."""

    __tablename__ = "sortika_data"


class CreakaData(DataRecordMixin, Base):
    """Données quotidiennes du service crea-ka."""

    __tablename__ = "creaka_data"


class HousekaData(DataRecordMixin, Base):
    """Données quotidiennes du service house-ka."""

    __tablename__ = "houseka_data"


class RentkaData(DataRecordMixin, Base):
    """Données quotidiennes du service rent-ka."""

    __tablename__ = "rentka_data"


class JobkaData(DataRecordMixin, Base):
    """Données quotidiennes du service job-ka."""

    __tablename__ = "jobka_data"


DATA_MODELS: dict[str, type[Base]] = {
    "louka": LoukaData,
    "immoka": ImmokaData,
    "houseka": HousekaData,
    "foodka": FoodkaData,
    "autoka": AutokaData,
    "fabrika": FabrikaData,
    "restoka": RestokaData,
    "sortika": SortikaData,
    "creaka": CreakaData,
    "jobka": JobkaData,
    "rentka": RentkaData,
}


class CollectionRun(Base):
    """Journal de chaque exécution de collecte (succès, échec ou relance)."""

    __tablename__ = "collection_runs"
    __table_args__ = (
        Index("ix_collection_runs_service_date_key", "service", "date_key"),
        Index("ix_collection_runs_status", "status"),
    )

    id: Mapped[int] = mapped_column(BigIntPK, primary_key=True, autoincrement=True)
    service: Mapped[str] = mapped_column(Text, nullable=False)
    date_key: Mapped[datetime.date] = mapped_column(Date, nullable=False)
    status: Mapped[str] = mapped_column(
        Text, nullable=False
    )  # success / failed / retried
    records_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    duration_seconds: Mapped[float] = mapped_column(Float, nullable=False, default=0.0)
    error_message: Mapped[str | None] = mapped_column(Text, nullable=True)
    node: Mapped[str] = mapped_column(Text, nullable=False, default=CURRENT_NODE)
    started_at: Mapped[datetime.datetime] = mapped_column(
        DateTime(timezone=True), nullable=False
    )
    finished_at: Mapped[datetime.datetime] = mapped_column(
        DateTime(timezone=True), nullable=False
    )


class ApiRequest(Base):
    """Journal léger des requêtes HTTP servies par l'API (alimenté par le
    middleware de logging, purge automatique > 90 jours).

    Sert exclusivement la page /stats : appels par endpoint/jour, latences
    moyennes et p95, taux d'erreur. Le chemin est normalisé (paramètres de
    route repliés) pour garder une cardinalité bornée.
    """

    __tablename__ = "api_requests"
    __table_args__ = (
        Index("ix_api_requests_ts", "ts"),
        Index("ix_api_requests_endpoint_ts", "endpoint", "ts"),
    )

    id: Mapped[int] = mapped_column(BigIntPK, primary_key=True, autoincrement=True)
    ts: Mapped[datetime.datetime] = mapped_column(
        DateTime(timezone=True), nullable=False
    )
    method: Mapped[str] = mapped_column(Text, nullable=False, default="GET")
    endpoint: Mapped[str] = mapped_column(Text, nullable=False)
    status: Mapped[int] = mapped_column(Integer, nullable=False)
    duration_ms: Mapped[float] = mapped_column(Float, nullable=False, default=0.0)


class ConnectorHealth(Base):
    """État courant de chaque connecteur de l'écosystème KA (supervision).

    Une ligne par couple (service, source), upsertée toutes les 2 h par
    ``src.monitoring.connector_health``. La source virtuelle ``_app``
    représente l'état global de l'app. La ligne mémorise aussi l'état
    précédent (anti-spam des alertes : on n'alerte qu'à la transition).
    """

    __tablename__ = "connector_health"
    __table_args__ = (
        UniqueConstraint(
            "service", "source", name="uq_connector_health_service_source"
        ),
        Index("ix_connector_health_service", "service"),
        Index("ix_connector_health_status", "status"),
    )

    id: Mapped[int] = mapped_column(BigIntPK, primary_key=True, autoincrement=True)
    service: Mapped[str] = mapped_column(Text, nullable=False)
    source: Mapped[str] = mapped_column(Text, nullable=False)
    checked_at: Mapped[datetime.datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )
    status: Mapped[str] = mapped_column(
        Text, nullable=False
    )  # ok / degraded / broken / stale / retired
    last_success: Mapped[datetime.datetime | None] = mapped_column(
        DateTime(timezone=True), nullable=True
    )
    found_last: Mapped[int | None] = mapped_column(Integer, nullable=True)
    median_found: Mapped[float | None] = mapped_column(Float, nullable=True)
    consecutive_failures: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0
    )
    message: Mapped[str | None] = mapped_column(Text, nullable=True)
    # Dernière apparition de la source dans une fenêtre de sync (pilote le
    # retrait ``retired`` des sources disparues du journal).
    last_seen: Mapped[datetime.datetime | None] = mapped_column(
        DateTime(timezone=True), nullable=True
    )