import enum
import uuid
from datetime import date, datetime
from decimal import Decimal

from geoalchemy2 import Geometry
from sqlalchemy import BigInteger, Boolean, Date, DateTime, Enum, Float, ForeignKey, Integer, Numeric, String, Text, func
from sqlalchemy.dialects.postgresql import JSONB, UUID
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class ObservationStatus(str, enum.Enum):
    a_qualifier = "a_qualifier"
    nid_suppose = "nid_suppose"
    nid_confirme = "nid_confirme"
    non_confirme = "non_confirme"
    transforme = "transforme"
    archive = "archive"


class RequestType(str, enum.Enum):
    public = "public"
    prive_accord = "prive_accord"
    prive_sans_accord = "prive_sans_accord"


class RequestStatus(str, enum.Enum):
    brouillon = "brouillon"
    transmise = "transmise"
    incomplete = "incomplete"
    a_qualifier = "a_qualifier"
    validee = "validee"
    programmee = "programmee"
    en_intervention = "en_intervention"
    terminee = "terminee"
    annulee = "annulee"
    refusee = "refusee"
    archive = "archive"


class UserRole(str, enum.Enum):
    elu = "elu"
    technicien = "technicien"
    admin = "admin"


class QuoteStatus(str, enum.Enum):
    brouillon = "brouillon"
    emis = "emis"
    accepte = "accepte"
    refuse = "refuse"
    annule = "annule"


class Commune(Base):
    __tablename__ = "communes"
    __table_args__ = {"schema": "frelons"}

    insee_com: Mapped[str] = mapped_column(String(5), primary_key=True)
    nom: Mapped[str] = mapped_column(String(150), index=True)
    nom_m: Mapped[str | None] = mapped_column(String(150))
    siren_epci: Mapped[str | None] = mapped_column(String(9), index=True)
    population: Mapped[int | None] = mapped_column(Integer)
    surf_km2: Mapped[Decimal | None] = mapped_column(Numeric(10, 2))
    actif: Mapped[bool] = mapped_column(Boolean, default=True, index=True)
    geom: Mapped[object | None] = mapped_column(Geometry("MULTIPOLYGON", srid=2154, spatial_index=True))
    synchronise_le: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class User(Base):
    __tablename__ = "utilisateurs"
    __table_args__ = {"schema": "frelons"}

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    email: Mapped[str] = mapped_column(String(254), unique=True, index=True)
    nom: Mapped[str] = mapped_column(String(150))
    prenom: Mapped[str | None] = mapped_column(String(150))
    password_hash: Mapped[str] = mapped_column(Text)
    role: Mapped[UserRole] = mapped_column(Enum(UserRole, name="user_role", schema="frelons"), index=True)
    commune_code: Mapped[str | None] = mapped_column(String(5), ForeignKey("frelons.communes.insee_com"))
    actif: Mapped[bool] = mapped_column(Boolean, default=True, index=True)
    doit_changer_mot_de_passe: Mapped[bool] = mapped_column(Boolean, default=False)
    tentatives_echouees: Mapped[int] = mapped_column(Integer, default=0)
    verrouille_jusqu_a: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    derniere_connexion: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    cree_le: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    modifie_le: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    commune: Mapped[Commune | None] = relationship()


class UserSession(Base):
    __tablename__ = "sessions_utilisateur"
    __table_args__ = {"schema": "frelons"}

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    utilisateur_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("frelons.utilisateurs.id", ondelete="CASCADE"), index=True)
    token_hash: Mapped[str] = mapped_column(Text, unique=True)
    adresse_ip: Mapped[str | None] = mapped_column(String(64))
    user_agent: Mapped[str | None] = mapped_column(Text)
    cree_le: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    expire_le: Mapped[datetime] = mapped_column(DateTime(timezone=True), index=True)
    derniere_activite: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    revoquee: Mapped[bool] = mapped_column(Boolean, default=False)


class AuditLog(Base):
    __tablename__ = "journal_audit"
    __table_args__ = {"schema": "frelons"}

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    utilisateur_id: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True), ForeignKey("frelons.utilisateurs.id", ondelete="SET NULL"), index=True)
    action: Mapped[str] = mapped_column(String(100), index=True)
    type_objet: Mapped[str | None] = mapped_column(String(50))
    objet_id: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True))
    details: Mapped[dict | None] = mapped_column(JSONB)
    adresse_ip: Mapped[str | None] = mapped_column(String(64))
    cree_le: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)


class Observation(Base):
    __tablename__ = "observations"
    __table_args__ = {"schema": "frelons"}

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    reference: Mapped[str] = mapped_column(String(32), unique=True, index=True)
    commune: Mapped[str] = mapped_column(String(160), index=True)
    commune_code: Mapped[str | None] = mapped_column(String(5), ForeignKey("frelons.communes.insee_com"), index=True)
    cree_par: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True), ForeignKey("frelons.utilisateurs.id", ondelete="SET NULL"), index=True)
    adresse: Mapped[str | None] = mapped_column(String(500))
    type_observation: Mapped[str] = mapped_column(String(40), default="nid_suppose")
    support: Mapped[str | None] = mapped_column(String(80))
    hauteur_estimee: Mapped[float | None] = mapped_column(Float)
    niveau_danger: Mapped[str] = mapped_column(String(20), default="modere")
    description: Mapped[str | None] = mapped_column(Text)
    contact_nom: Mapped[str | None] = mapped_column(String(160))
    contact_telephone: Mapped[str | None] = mapped_column(String(40))
    contact_email: Mapped[str | None] = mapped_column(String(254))
    status: Mapped[ObservationStatus] = mapped_column(
        Enum(ObservationStatus, name="observation_status", schema="frelons"),
        default=ObservationStatus.a_qualifier,
    )
    geom: Mapped[object] = mapped_column(Geometry("POINT", srid=4326, spatial_index=True))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    photos: Mapped[list["Photo"]] = relationship(back_populates="observation", cascade="all, delete-orphan")


class InterventionRequest(Base):
    __tablename__ = "demandes_intervention"
    __table_args__ = {"schema": "frelons"}

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    reference: Mapped[str] = mapped_column(String(32), unique=True, index=True)
    observation_id: Mapped[uuid.UUID | None] = mapped_column(
        UUID(as_uuid=True), ForeignKey("frelons.observations.id", ondelete="SET NULL")
    )
    type_demande: Mapped[RequestType] = mapped_column(Enum(RequestType, name="request_type", schema="frelons"))
    status: Mapped[RequestStatus] = mapped_column(
        Enum(RequestStatus, name="request_status", schema="frelons"),
        default=RequestStatus.transmise,
        index=True,
    )
    commune: Mapped[str] = mapped_column(String(160), index=True)
    commune_code: Mapped[str | None] = mapped_column(String(5), ForeignKey("frelons.communes.insee_com"), index=True)
    cree_par: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True), ForeignKey("frelons.utilisateurs.id", ondelete="SET NULL"), index=True)
    demandeur_nom: Mapped[str] = mapped_column(String(160))
    demandeur_fonction: Mapped[str | None] = mapped_column(String(160))
    demandeur_email: Mapped[str] = mapped_column(String(254))
    demandeur_telephone: Mapped[str | None] = mapped_column(String(40))
    adresse: Mapped[str] = mapped_column(String(500))
    niveau_urgence: Mapped[str] = mapped_column(String(20), default="normal", index=True)
    description_risque: Mapped[str | None] = mapped_column(Text)
    hauteur_estimee: Mapped[float | None] = mapped_column(Float)
    support: Mapped[str | None] = mapped_column(String(80))
    difficulte_acces: Mapped[str | None] = mapped_column(Text)
    proprietaire_nom: Mapped[str | None] = mapped_column(String(160))
    proprietaire_adresse: Mapped[str | None] = mapped_column(String(500))
    proprietaire_telephone: Mapped[str | None] = mapped_column(String(40))
    proprietaire_email: Mapped[str | None] = mapped_column(String(254))
    accord_proprietaire: Mapped[bool | None] = mapped_column(Boolean)
    justification_danger_imminent: Mapped[str | None] = mapped_column(Text)
    geom: Mapped[object] = mapped_column(Geometry("POINT", srid=4326, spatial_index=True))
    assigned_to: Mapped[str | None] = mapped_column(String(160))
    intervention_date: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    photos: Mapped[list["Photo"]] = relationship(back_populates="demande", cascade="all, delete-orphan")
    histories: Mapped[list["StatusHistory"]] = relationship(back_populates="demande", cascade="all, delete-orphan")
    intervention: Mapped["Intervention | None"] = relationship(back_populates="demande", cascade="all, delete-orphan", uselist=False)
    devis: Mapped["InterventionQuote | None"] = relationship(back_populates="demande", cascade="all, delete-orphan", uselist=False)


class InterventionTariff(Base):
    __tablename__ = "tarifs_intervention"
    __table_args__ = {"schema": "frelons"}

    code: Mapped[str] = mapped_column(String(40), primary_key=True)
    libelle: Mapped[str] = mapped_column(String(160))
    description: Mapped[str | None] = mapped_column(Text)
    prix: Mapped[Decimal] = mapped_column(Numeric(10, 2))
    actif: Mapped[bool] = mapped_column(Boolean, default=True, index=True)
    ordre: Mapped[int] = mapped_column(Integer, default=0)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())


class InterventionQuote(Base):
    __tablename__ = "devis_intervention"
    __table_args__ = {"schema": "frelons"}

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    demande_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("frelons.demandes_intervention.id", ondelete="CASCADE"), unique=True, index=True
    )
    reference: Mapped[str] = mapped_column(String(32), unique=True, index=True)
    statut: Mapped[QuoteStatus] = mapped_column(
        Enum(QuoteStatus, name="quote_status", schema="frelons"),
        default=QuoteStatus.brouillon,
        index=True,
    )
    type_intervention: Mapped[str] = mapped_column(String(40), ForeignKey("frelons.tarifs_intervention.code"))
    libelle_snapshot: Mapped[str] = mapped_column(String(160))
    prix_unitaire: Mapped[Decimal] = mapped_column(Numeric(10, 2))
    quantite: Mapped[int] = mapped_column(Integer, default=1)
    montant_total: Mapped[Decimal] = mapped_column(Numeric(10, 2))
    commune_facturee: Mapped[str] = mapped_column(String(160))
    date_devis: Mapped[date] = mapped_column(Date, server_default=func.current_date())
    validite_jusqu_au: Mapped[date | None] = mapped_column(Date)
    observations: Mapped[str | None] = mapped_column(Text)
    cree_par: Mapped[uuid.UUID | None] = mapped_column(
        UUID(as_uuid=True), ForeignKey("frelons.utilisateurs.id", ondelete="SET NULL"), index=True
    )
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    demande: Mapped[InterventionRequest] = relationship(back_populates="devis")
    tarif: Mapped[InterventionTariff] = relationship()


class Photo(Base):
    __tablename__ = "photos"
    __table_args__ = {"schema": "frelons"}

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    observation_id: Mapped[uuid.UUID | None] = mapped_column(
        UUID(as_uuid=True), ForeignKey("frelons.observations.id", ondelete="CASCADE")
    )
    demande_id: Mapped[uuid.UUID | None] = mapped_column(
        UUID(as_uuid=True), ForeignKey("frelons.demandes_intervention.id", ondelete="CASCADE")
    )
    filename: Mapped[str] = mapped_column(String(300))
    original_name: Mapped[str] = mapped_column(String(300))
    mime_type: Mapped[str] = mapped_column(String(120))
    size_bytes: Mapped[int] = mapped_column(Integer)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())

    observation: Mapped[Observation | None] = relationship(back_populates="photos")
    demande: Mapped[InterventionRequest | None] = relationship(back_populates="photos")


class StatusHistory(Base):
    __tablename__ = "historique_statuts"
    __table_args__ = {"schema": "frelons"}

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    demande_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("frelons.demandes_intervention.id", ondelete="CASCADE"), index=True
    )
    ancien_statut: Mapped[str | None] = mapped_column(String(40))
    nouveau_statut: Mapped[str] = mapped_column(String(40))
    commentaire: Mapped[str | None] = mapped_column(Text)
    auteur: Mapped[str | None] = mapped_column(String(160))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())

    demande: Mapped[InterventionRequest] = relationship(back_populates="histories")


class Intervention(Base):
    __tablename__ = "interventions"
    __table_args__ = {"schema": "frelons"}

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    demande_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("frelons.demandes_intervention.id", ondelete="CASCADE"), unique=True
    )
    agent_nom: Mapped[str | None] = mapped_column(String(160))
    agent_id: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True), ForeignKey("frelons.utilisateurs.id", ondelete="SET NULL"), index=True)
    debut_intervention: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    fin_intervention: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    methode: Mapped[str | None] = mapped_column(String(80))
    resultat: Mapped[str | None] = mapped_column(String(80))
    cout_ht: Mapped[Decimal | None] = mapped_column(Numeric(10, 2))
    compte_rendu: Mapped[str | None] = mapped_column(Text)
    signature_proprietaire_apres: Mapped[str | None] = mapped_column(String(500))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    demande: Mapped[InterventionRequest] = relationship(back_populates="intervention")


class NotificationLog(Base):
    __tablename__ = "notifications"
    __table_args__ = {"schema": "frelons"}

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    entity_type: Mapped[str] = mapped_column(String(40))
    entity_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), index=True)
    recipient: Mapped[str] = mapped_column(String(254))
    subject: Mapped[str] = mapped_column(String(300))
    status: Mapped[str] = mapped_column(String(30), default="pending")
    error: Mapped[str | None] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())