from datetime import date, datetime

from sqlalchemy import Boolean, Date, DateTime, Float, ForeignKey, Integer, String, Text
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.database import Base


# ---------------------------------------------------------------------------
# Comptes utilisateurs
# ---------------------------------------------------------------------------
class Utilisateur(Base):
    __tablename__ = "utilisateurs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    identifiant: Mapped[str] = mapped_column(String(100), unique=True, index=True)
    nom_complet: Mapped[str] = mapped_column(String(200))
    mot_de_passe_hash: Mapped[str] = mapped_column(String(200))
    est_admin: Mapped[bool] = mapped_column(Boolean, default=False)
    actif: Mapped[bool] = mapped_column(Boolean, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)


class JournalConnexion(Base):
    __tablename__ = "journal_connexions"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    identifiant: Mapped[str] = mapped_column(String(100))
    reussie: Mapped[bool] = mapped_column(Boolean)
    adresse_ip: Mapped[str | None] = mapped_column(String(100), nullable=True)
    date_heure: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, index=True)


# ---------------------------------------------------------------------------
# Ferme Exploitation
# ---------------------------------------------------------------------------
class Ferme(Base):
    __tablename__ = "fermes"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    nom: Mapped[str] = mapped_column(String(200))
    localisation: Mapped[str | None] = mapped_column(String(300), nullable=True)
    photo_satellite_path: Mapped[str | None] = mapped_column(String(500), nullable=True)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    parcelles: Mapped[list["Parcelle"]] = relationship(back_populates="ferme", order_by="Parcelle.nom")


class Parcelle(Base):
    __tablename__ = "parcelles"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    ferme_id: Mapped[int | None] = mapped_column(ForeignKey("fermes.id"), nullable=True)
    nom: Mapped[str] = mapped_column(String(200))
    superficie_m2: Mapped[float | None] = mapped_column(Float, nullable=True)
    localisation: Mapped[str | None] = mapped_column(String(300), nullable=True)
    culture_actuelle: Mapped[str | None] = mapped_column(String(200), nullable=True)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    plan_path: Mapped[str | None] = mapped_column(String(500), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    ferme: Mapped["Ferme | None"] = relationship(back_populates="parcelles")
    photos: Mapped[list["ParcellePhoto"]] = relationship(
        back_populates="parcelle", cascade="all, delete-orphan"
    )
    plantations: Mapped[list["Plantation"]] = relationship(
        back_populates="parcelle", cascade="all, delete-orphan", order_by="desc(Plantation.date_plantation)"
    )
    entrees_station: Mapped[list["EntreeMarchandise"]] = relationship(
        back_populates="parcelle"
    )

    @property
    def derniere_plantation(self) -> "Plantation | None":
        return self.plantations[0] if self.plantations else None


class ParcellePhoto(Base):
    __tablename__ = "parcelle_photos"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    parcelle_id: Mapped[int] = mapped_column(ForeignKey("parcelles.id"))
    chemin: Mapped[str] = mapped_column(String(500))
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    parcelle: Mapped["Parcelle"] = relationship(back_populates="photos")


class Plantation(Base):
    __tablename__ = "plantations"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    parcelle_id: Mapped[int] = mapped_column(ForeignKey("parcelles.id"))
    date_plantation: Mapped[date] = mapped_column(Date, default=date.today)
    variete: Mapped[str] = mapped_column(String(200))
    nombre_plants: Mapped[int | None] = mapped_column(Integer, nullable=True)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    parcelle: Mapped["Parcelle"] = relationship(back_populates="plantations")


# ---------------------------------------------------------------------------
# Gestion Comptable
# ---------------------------------------------------------------------------
class Fournisseur(Base):
    __tablename__ = "fournisseurs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    nom: Mapped[str] = mapped_column(String(200))
    contact: Mapped[str | None] = mapped_column(String(200), nullable=True)
    telephone: Mapped[str | None] = mapped_column(String(50), nullable=True)
    adresse: Mapped[str | None] = mapped_column(String(300), nullable=True)
    numero_compte: Mapped[str | None] = mapped_column(String(100), nullable=True)
    solde_initial: Mapped[float] = mapped_column(Float, default=0)
    date_solde_initial: Mapped[date | None] = mapped_column(Date, nullable=True)

    achats: Mapped[list["Achat"]] = relationship(
        back_populates="fournisseur", cascade="all, delete-orphan"
    )
    reglements: Mapped[list["Reglement"]] = relationship(
        back_populates="fournisseur", cascade="all, delete-orphan", order_by="Reglement.date_reglement"
    )

    @property
    def solde(self) -> float:
        """Solde actuel du compte fournisseur (montant dû) : solde initial + achats - règlements."""
        total_achats = sum(a.montant_total for a in self.achats)
        total_reglements = sum(r.montant for r in self.reglements)
        return self.solde_initial + total_achats - total_reglements


class Achat(Base):
    __tablename__ = "achats"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    fournisseur_id: Mapped[int] = mapped_column(ForeignKey("fournisseurs.id"))
    date_achat: Mapped[date] = mapped_column(Date, default=date.today)
    numero_facture: Mapped[str | None] = mapped_column(String(100), nullable=True)
    designation_produit: Mapped[str] = mapped_column(String(300))
    quantite: Mapped[float | None] = mapped_column(Float, nullable=True)
    unite: Mapped[str | None] = mapped_column(String(20), nullable=True)
    prix_unitaire: Mapped[float | None] = mapped_column(Float, nullable=True)
    montant_total: Mapped[float] = mapped_column(Float, default=0)
    facture_path: Mapped[str | None] = mapped_column(String(500), nullable=True)

    fournisseur: Mapped["Fournisseur"] = relationship(back_populates="achats")
    reglements: Mapped[list["Reglement"]] = relationship(back_populates="achat")

    @property
    def montant_regle(self) -> float:
        return sum(r.montant for r in self.reglements)

    @property
    def solde_du(self) -> float:
        return round(self.montant_total - self.montant_regle, 2)


class Reglement(Base):
    __tablename__ = "reglements"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    fournisseur_id: Mapped[int] = mapped_column(ForeignKey("fournisseurs.id"))
    achat_id: Mapped[int | None] = mapped_column(ForeignKey("achats.id"), nullable=True)
    date_reglement: Mapped[date] = mapped_column(Date, default=date.today)
    mode_paiement: Mapped[str] = mapped_column(String(50), default="Virement")
    reference: Mapped[str | None] = mapped_column(String(100), nullable=True)
    montant: Mapped[float] = mapped_column(Float, default=0)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    fournisseur: Mapped["Fournisseur"] = relationship(back_populates="reglements")
    achat: Mapped["Achat | None"] = relationship(back_populates="reglements")


class FraisPersonnel(Base):
    __tablename__ = "frais_personnel"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    employe: Mapped[str] = mapped_column(String(200))
    periode: Mapped[str] = mapped_column(String(50))
    date_debut: Mapped[date | None] = mapped_column(Date, nullable=True)
    montant: Mapped[float] = mapped_column(Float, default=0)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)


class AutreFrais(Base):
    __tablename__ = "autres_frais"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    type_frais: Mapped[str] = mapped_column(String(200))
    date_frais: Mapped[date] = mapped_column(Date, default=date.today)
    montant: Mapped[float] = mapped_column(Float, default=0)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)


# ---------------------------------------------------------------------------
# Station de Conditionnement
# ---------------------------------------------------------------------------
class EntreeMarchandise(Base):
    __tablename__ = "entrees_marchandise"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    date_entree: Mapped[date] = mapped_column(Date, default=date.today)
    parcelle_id: Mapped[int | None] = mapped_column(
        ForeignKey("parcelles.id"), nullable=True
    )
    produit: Mapped[str] = mapped_column(String(200))
    poids_entree_kg: Mapped[float] = mapped_column(Float, default=0)
    poids_sortie_kg: Mapped[float | None] = mapped_column(Float, nullable=True)
    type_sortie: Mapped[str | None] = mapped_column(String(50), nullable=True)  # export / local
    destination: Mapped[str | None] = mapped_column(String(200), nullable=True)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    parcelle: Mapped["Parcelle | None"] = relationship(back_populates="entrees_station")

    @property
    def ecart_kg(self) -> float | None:
        if self.poids_sortie_kg is None:
            return None
        return round(self.poids_entree_kg - self.poids_sortie_kg, 2)

    @property
    def ecart_pourcent(self) -> float | None:
        if not self.poids_entree_kg or self.poids_sortie_kg is None:
            return None
        return round((self.ecart_kg / self.poids_entree_kg) * 100, 2)


# ---------------------------------------------------------------------------
# Gestion Commerciale
# ---------------------------------------------------------------------------
class Client(Base):
    __tablename__ = "clients"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    nom: Mapped[str] = mapped_column(String(200))
    contact: Mapped[str | None] = mapped_column(String(200), nullable=True)
    telephone: Mapped[str | None] = mapped_column(String(50), nullable=True)  # téléphone bureau
    telephone_portable: Mapped[str | None] = mapped_column(String(50), nullable=True)
    email: Mapped[str | None] = mapped_column(String(200), nullable=True)
    adresse: Mapped[str | None] = mapped_column(String(300), nullable=True)
    numero_rue: Mapped[str | None] = mapped_column(String(50), nullable=True)
    code_postal: Mapped[str | None] = mapped_column(String(20), nullable=True)
    pays: Mapped[str | None] = mapped_column(String(100), nullable=True)
    devise: Mapped[str] = mapped_column(String(10), default="MAD")
    registre_commerce: Mapped[str | None] = mapped_column(String(100), nullable=True)
    autres_infos_legales: Mapped[str | None] = mapped_column(Text, nullable=True)
    afficher_telephone_bureau: Mapped[bool] = mapped_column(Boolean, default=True)
    afficher_telephone_portable: Mapped[bool] = mapped_column(Boolean, default=True)
    afficher_email: Mapped[bool] = mapped_column(Boolean, default=True)
    afficher_registre_commerce: Mapped[bool] = mapped_column(Boolean, default=True)
    afficher_autres_infos: Mapped[bool] = mapped_column(Boolean, default=True)
    solde_initial: Mapped[float] = mapped_column(Float, default=0)
    date_solde_initial: Mapped[date | None] = mapped_column(Date, nullable=True)

    ventes: Mapped[list["VenteClient"]] = relationship(
        back_populates="client", cascade="all, delete-orphan"
    )
    encaissements: Mapped[list["Encaissement"]] = relationship(
        back_populates="client", cascade="all, delete-orphan", order_by="Encaissement.date_encaissement"
    )

    @property
    def solde(self) -> float:
        """Solde actuel du compte client (montant dû par le client) : solde initial
        + factures - avoirs - encaissements (les devis ne comptent pas, ce ne sont
        pas des engagements financiers)."""
        total_factures = sum(v.montant for v in self.ventes if v.type_document == "facture")
        total_avoirs = sum(v.montant for v in self.ventes if v.type_document == "avoir")
        total_encaissements = sum(e.montant for e in self.encaissements)
        return self.solde_initial + total_factures - total_avoirs - total_encaissements


class VenteClient(Base):
    __tablename__ = "ventes_client"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    client_id: Mapped[int] = mapped_column(ForeignKey("clients.id"))
    vendeur_id: Mapped[int | None] = mapped_column(ForeignKey("parametres_entreprise.id"), nullable=True)
    type_document: Mapped[str] = mapped_column(String(20), default="facture")  # devis | facture | avoir
    facture_origine_id: Mapped[int | None] = mapped_column(ForeignKey("ventes_client.id"), nullable=True)
    date_vente: Mapped[date] = mapped_column(Date, default=date.today)
    date_echeance: Mapped[date | None] = mapped_column(Date, nullable=True)
    numero_facture: Mapped[str | None] = mapped_column(String(100), nullable=True)
    mode_reglement: Mapped[str | None] = mapped_column(String(100), nullable=True)
    produit: Mapped[str] = mapped_column(String(200))
    quantite: Mapped[float | None] = mapped_column(Float, nullable=True)
    montant: Mapped[float] = mapped_column(Float, default=0)
    facture_vente_path: Mapped[str | None] = mapped_column(String(500), nullable=True)
    transporteur: Mapped[str | None] = mapped_column(String(200), nullable=True)
    matricule_camion: Mapped[str | None] = mapped_column(String(100), nullable=True)
    incoterm: Mapped[str | None] = mapped_column(String(200), nullable=True)

    client: Mapped["Client"] = relationship(back_populates="ventes")
    vendeur: Mapped["ParametresEntreprise | None"] = relationship()
    lignes: Mapped[list["LigneVente"]] = relationship(
        back_populates="vente", cascade="all, delete-orphan", order_by="LigneVente.id"
    )
    encaissements: Mapped[list["Encaissement"]] = relationship(back_populates="vente")
    facture_origine: Mapped["VenteClient | None"] = relationship(
        remote_side="VenteClient.id", foreign_keys=[facture_origine_id]
    )

    @property
    def montant_encaisse(self) -> float:
        return sum(e.montant for e in self.encaissements)

    @property
    def solde_du(self) -> float:
        return round(self.montant - self.montant_encaisse, 2)

    @property
    def statut_paiement(self) -> str:
        if self.type_document != "facture":
            return "—"
        if self.montant_encaisse <= 0:
            return "Impayée"
        if self.solde_du <= 0:
            return "Payée"
        return "Partielle"

    @property
    def en_retard(self) -> bool:
        return bool(
            self.type_document == "facture"
            and self.date_echeance
            and self.solde_du > 0
            and self.date_echeance < date.today()
        )


class Produit(Base):
    __tablename__ = "produits"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    nom: Mapped[str] = mapped_column(String(200))
    description: Mapped[str | None] = mapped_column(String(300), nullable=True)
    unite: Mapped[str | None] = mapped_column(String(50), nullable=True)
    prix_unitaire_defaut: Mapped[float | None] = mapped_column(Float, nullable=True)
    type_emballage_defaut: Mapped[str | None] = mapped_column(String(100), nullable=True)
    poids_colis_kg_defaut: Mapped[float | None] = mapped_column(Float, nullable=True)
    actif: Mapped[bool] = mapped_column(Boolean, default=True)


class LigneVente(Base):
    __tablename__ = "lignes_vente"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    vente_id: Mapped[int] = mapped_column(ForeignKey("ventes_client.id"))
    produit: Mapped[str] = mapped_column(String(200))
    nb_palettes: Mapped[int | None] = mapped_column(Integer, nullable=True)
    type_emballage: Mapped[str | None] = mapped_column(String(100), nullable=True)
    nombre_colis: Mapped[int | None] = mapped_column(Integer, nullable=True)
    poids_brut_kg: Mapped[float | None] = mapped_column(Float, nullable=True)
    poids_net_kg: Mapped[float | None] = mapped_column(Float, nullable=True)
    prix_unitaire: Mapped[float | None] = mapped_column(Float, nullable=True)
    montant: Mapped[float] = mapped_column(Float, default=0)

    vente: Mapped["VenteClient"] = relationship(back_populates="lignes")


class Encaissement(Base):
    __tablename__ = "encaissements"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    client_id: Mapped[int] = mapped_column(ForeignKey("clients.id"))
    vente_id: Mapped[int | None] = mapped_column(ForeignKey("ventes_client.id"), nullable=True)
    date_encaissement: Mapped[date] = mapped_column(Date, default=date.today)
    mode_paiement: Mapped[str] = mapped_column(String(50), default="Virement")
    reference: Mapped[str | None] = mapped_column(String(100), nullable=True)
    montant: Mapped[float] = mapped_column(Float, default=0)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    client: Mapped["Client"] = relationship(back_populates="encaissements")
    vente: Mapped["VenteClient | None"] = relationship(back_populates="encaissements")


# ---------------------------------------------------------------------------
# Paramètres entreprise (utilisés sur les factures : logo, coordonnées, banque)
# ---------------------------------------------------------------------------
class ParametresEntreprise(Base):
    __tablename__ = "parametres_entreprise"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    nom_societe: Mapped[str | None] = mapped_column(String(200), nullable=True)
    adresse_ligne1: Mapped[str | None] = mapped_column(String(300), nullable=True)
    adresse_ligne2: Mapped[str | None] = mapped_column(String(300), nullable=True)
    telephone: Mapped[str | None] = mapped_column(String(100), nullable=True)
    email: Mapped[str | None] = mapped_column(String(200), nullable=True)
    rc: Mapped[str | None] = mapped_column(String(100), nullable=True)
    ice: Mapped[str | None] = mapped_column(String(100), nullable=True)
    banque_nom: Mapped[str | None] = mapped_column(String(200), nullable=True)
    banque_iban: Mapped[str | None] = mapped_column(String(100), nullable=True)
    banque_swift: Mapped[str | None] = mapped_column(String(100), nullable=True)
    logo_path: Mapped[str | None] = mapped_column(String(500), nullable=True)