from datetime import date, datetime

from flask_login import UserMixin
from flask_sqlalchemy import SQLAlchemy
from werkzeug.security import check_password_hash, generate_password_hash

from app.constants import ROLE_LABELS, ROLES_LOT
from app.database_profiles import ProfileRoutingSession
from app.phone import TEL_AFFICHAGE_INTERNATIONAL
from app.public_id import PublicIdMixin
from app.soft_delete import SoftDeleteMixin
from app.timestamp import TimestampMixin

db = SQLAlchemy(session_options={"class_": ProfileRoutingSession})


class User(PublicIdMixin, UserMixin, db.Model):
    __tablename__ = "users"

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False, index=True)
    password_hash = db.Column(db.String(256), nullable=False)
    is_active = db.Column(db.Boolean, default=True, nullable=False)
    is_admin = db.Column(db.Boolean, default=False, nullable=False)
    tel_affichage = db.Column(
        db.String(20), default=TEL_AFFICHAGE_INTERNATIONAL, nullable=False
    )

    coproprietes = db.relationship(
        "UserCopropriete", back_populates="user", cascade="all, delete-orphan"
    )

    def set_password(self, password: str) -> None:
        self.password_hash = generate_password_hash(password)

    def check_password(self, password: str) -> bool:
        return check_password_hash(self.password_hash, password)

    def accessible_copropriete_ids(self) -> list[int]:
        return [uc.copropriete_id for uc in self.coproprietes]


class Copropriete(PublicIdMixin, SoftDeleteMixin, db.Model):
    __tablename__ = "coproprietes"

    id = db.Column(db.Integer, primary_key=True)
    nom = db.Column(db.String(200), nullable=False)
    adresse = db.Column(db.Text, default="")
    notes = db.Column(db.Text, default="")
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    users = db.relationship(
        "UserCopropriete", back_populates="copropriete", cascade="all, delete-orphan"
    )
    personnes = db.relationship(
        "Personne", back_populates="copropriete", cascade="all, delete-orphan"
    )
    lots = db.relationship(
        "Lot", back_populates="copropriete", cascade="all, delete-orphan"
    )
    millieme_types = db.relationship(
        "MilliemeType", back_populates="copropriete", cascade="all, delete-orphan"
    )
    releve_types = db.relationship(
        "ReleveType", back_populates="copropriete", cascade="all, delete-orphan"
    )
    lot_types = db.relationship(
        "LotType", back_populates="copropriete", cascade="all, delete-orphan"
    )


class UserCopropriete(db.Model):
    __tablename__ = "user_copropriete"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    copropriete_id = db.Column(
        db.Integer, db.ForeignKey("coproprietes.id"), nullable=False
    )

    user = db.relationship("User", back_populates="coproprietes")
    copropriete = db.relationship("Copropriete", back_populates="users")

    __table_args__ = (
        db.UniqueConstraint("user_id", "copropriete_id", name="uq_user_copropriete"),
    )


class Personne(PublicIdMixin, SoftDeleteMixin, TimestampMixin, db.Model):
    __tablename__ = "personnes"

    id = db.Column(db.Integer, primary_key=True)
    copropriete_id = db.Column(
        db.Integer, db.ForeignKey("coproprietes.id"), nullable=False, index=True
    )
    civilite = db.Column(db.String(20), default="")
    nom = db.Column(db.String(120), nullable=False)
    prenom = db.Column(db.String(120), default="")
    raison_sociale = db.Column(db.String(200), default="")
    notes = db.Column(db.Text, default="")

    copropriete = db.relationship("Copropriete", back_populates="personnes")
    telephones = db.relationship(
        "Telephone", back_populates="personne", cascade="all, delete-orphan"
    )
    emails = db.relationship(
        "Email", back_populates="personne", cascade="all, delete-orphan"
    )
    adresses = db.relationship(
        "Adresse", back_populates="personne", cascade="all, delete-orphan"
    )
    roles_lot = db.relationship("LotPersonneRole", back_populates="personne")

    @property
    def nom_complet(self) -> str:
        if self.raison_sociale:
            return self.raison_sociale
        parts = [p for p in (self.prenom, self.nom) if p]
        return " ".join(parts) if parts else self.nom

    @property
    def libelle_affiche(self) -> str:
        if self.raison_sociale:
            contact = " ".join(p for p in (self.prenom, self.nom) if p)
            return f"{self.raison_sociale} ({contact})" if contact else self.raison_sociale
        return self.nom_complet

    @property
    def titre_affiche(self) -> str:
        """Libellé complet avec civilité devant le contact, pas la raison sociale."""
        if self.raison_sociale:
            contact_parts = []
            if self.civilite:
                contact_parts.append(self.civilite)
            contact_parts.extend(p for p in (self.prenom, self.nom) if p)
            contact = " ".join(contact_parts)
            if contact:
                return f"{self.raison_sociale} ({contact})"
            return f"{self.civilite} {self.raison_sociale}".strip()
        parts = [self.civilite] if self.civilite else []
        parts.extend(p for p in (self.prenom, self.nom) if p)
        return " ".join(parts) if parts else self.nom


    def roles_en_cours(self, copropriete_id: int) -> list["LotPersonneRole"]:
        return [
            r
            for r in self.roles_lot
            if r.est_actif and r.lot.copropriete_id == copropriete_id
        ]

    def roles_uniques_en_cours(self, copropriete_id: int) -> list[str]:
        """Libellés de rôles distincts en cours (ordre fixe : propriétaire, occupant…)."""
        actifs = {r.role for r in self.roles_en_cours(copropriete_id)}
        return [ROLE_LABELS[r] for r in ROLES_LOT if r in actifs]


class Telephone(db.Model):
    __tablename__ = "telephones"

    id = db.Column(db.Integer, primary_key=True)
    personne_id = db.Column(
        db.Integer, db.ForeignKey("personnes.id"), nullable=False, index=True
    )
    libelle = db.Column(db.String(50), default="")
    numero = db.Column(db.String(40), nullable=False)

    personne = db.relationship("Personne", back_populates="telephones")


class Email(db.Model):
    __tablename__ = "emails"

    id = db.Column(db.Integer, primary_key=True)
    personne_id = db.Column(
        db.Integer, db.ForeignKey("personnes.id"), nullable=False, index=True
    )
    libelle = db.Column(db.String(50), default="")
    adresse = db.Column(db.String(200), nullable=False)

    personne = db.relationship("Personne", back_populates="emails")


class Adresse(db.Model):
    __tablename__ = "adresses"

    id = db.Column(db.Integer, primary_key=True)
    personne_id = db.Column(
        db.Integer, db.ForeignKey("personnes.id"), nullable=False, index=True
    )
    libelle = db.Column(db.String(50), default="")
    ligne1 = db.Column(db.String(200), default="")
    ligne2 = db.Column(db.String(200), default="")
    code_postal = db.Column(db.String(12), default="")
    ville = db.Column(db.String(100), default="")

    personne = db.relationship("Personne", back_populates="adresses")

    @property
    def resume(self) -> str:
        parts = [self.ligne1, self.ligne2, f"{self.code_postal} {self.ville}".strip()]
        return ", ".join(p for p in parts if p)


class Lot(PublicIdMixin, SoftDeleteMixin, TimestampMixin, db.Model):
    __tablename__ = "lots"

    id = db.Column(db.Integer, primary_key=True)
    copropriete_id = db.Column(
        db.Integer, db.ForeignKey("coproprietes.id"), nullable=False, index=True
    )
    lot_syndic = db.Column(db.String(30), default="")
    lot_copro = db.Column(db.String(30), default="")
    numero_fiscal = db.Column(db.String(50), default="")
    designation = db.Column(db.String(200), default="")
    etage = db.Column(db.String(30), default="")
    porte = db.Column(db.String(30), default="")
    etage_porte = db.Column(db.String(30), default="")  # legacy, migré vers designation
    lot_type_id = db.Column(
        db.Integer, db.ForeignKey("lot_types.id", ondelete="SET NULL"), nullable=True, index=True
    )
    surface = db.Column(db.Float, nullable=True)
    cave = db.Column(db.String(30), default="")
    bal = db.Column(db.String(30), default="")
    milliemes = db.Column(db.String(30), default="")  # legacy, migration seulement
    notes = db.Column(db.Text, default="")

    copropriete = db.relationship("Copropriete", back_populates="lots")
    type_lot = db.relationship("LotType", back_populates="lots")
    roles = db.relationship(
        "LotPersonneRole", back_populates="lot", cascade="all, delete-orphan"
    )
    milliemes_valeurs = db.relationship(
        "LotMilliemes", back_populates="lot", cascade="all, delete-orphan"
    )
    compteurs = db.relationship(
        "Compteur", back_populates="lot", cascade="all, delete-orphan"
    )

    @property
    def libelle(self) -> str:
        parts = []
        if self.designation:
            parts.append(self.designation)
        elif self.etage or self.porte:
            ep = " ".join(p for p in (self.etage, self.porte) if p)
            if ep:
                parts.append(ep)
        if self.lot_copro:
            parts.append(f"lot {self.lot_copro}")
        return " — ".join(parts) if parts else "Lot sans libellé"

    def roles_actuels(self) -> list["LotPersonneRole"]:
        return [r for r in self.roles if r.est_actif]

    def roles_actifs(self, role: str | None = None) -> list["LotPersonneRole"]:
        actifs = [r for r in self.roles if r.est_actif]
        if role:
            actifs = [r for r in actifs if r.role == role]
        return actifs

    def roles_par_personne(self) -> dict[int, list["LotPersonneRole"]]:
        grouped: dict[int, list[LotPersonneRole]] = {}
        for role in sorted(self.roles, key=lambda r: (r.personne.nom, r.role)):
            if not role.est_actif_enregistrement:
                continue
            grouped.setdefault(role.personne_id, []).append(role)
        return grouped

    def compteurs_actifs(self) -> list["Compteur"]:
        return [c for c in self.compteurs if c.est_actif_enregistrement and c.actif]


class LotPersonneRole(PublicIdMixin, SoftDeleteMixin, TimestampMixin, db.Model):
    __tablename__ = "lot_personne_roles"

    id = db.Column(db.Integer, primary_key=True)
    lot_id = db.Column(db.Integer, db.ForeignKey("lots.id"), nullable=False, index=True)
    personne_id = db.Column(
        db.Integer, db.ForeignKey("personnes.id"), nullable=False, index=True
    )
    role = db.Column(db.String(20), nullable=False)
    date_debut = db.Column(db.Date, nullable=True)
    date_fin = db.Column(db.Date, nullable=True)
    notes = db.Column(db.Text, default="")

    lot = db.relationship("Lot", back_populates="roles")
    personne = db.relationship("Personne", back_populates="roles_lot")

    @property
    def est_actif(self) -> bool:
        return self.est_actif_enregistrement and self.date_fin is None

    @property
    def role_libelle(self) -> str:
        return ROLE_LABELS.get(self.role, self.role)


class LotType(PublicIdMixin, db.Model):
    __tablename__ = "lot_types"

    id = db.Column(db.Integer, primary_key=True)
    copropriete_id = db.Column(
        db.Integer, db.ForeignKey("coproprietes.id"), nullable=False, index=True
    )
    libelle = db.Column(db.String(100), nullable=False)
    ordre = db.Column(db.Integer, default=0)

    copropriete = db.relationship("Copropriete", back_populates="lot_types")
    lots = db.relationship("Lot", back_populates="type_lot")


class MilliemeType(PublicIdMixin, db.Model):
    __tablename__ = "millieme_types"

    id = db.Column(db.Integer, primary_key=True)
    copropriete_id = db.Column(
        db.Integer, db.ForeignKey("coproprietes.id"), nullable=False, index=True
    )
    libelle = db.Column(db.String(100), nullable=False)
    ordre = db.Column(db.Integer, default=0)

    copropriete = db.relationship("Copropriete", back_populates="millieme_types")
    valeurs = db.relationship("LotMilliemes", back_populates="type_millieme")


class LotMilliemes(db.Model):
    __tablename__ = "lot_milliemes"

    id = db.Column(db.Integer, primary_key=True)
    lot_id = db.Column(db.Integer, db.ForeignKey("lots.id"), nullable=False, index=True)
    type_id = db.Column(
        db.Integer, db.ForeignKey("millieme_types.id"), nullable=False, index=True
    )
    valeur = db.Column(db.String(50), default="")

    lot = db.relationship("Lot", back_populates="milliemes_valeurs")
    type_millieme = db.relationship("MilliemeType", back_populates="valeurs")

    __table_args__ = (
        db.UniqueConstraint("lot_id", "type_id", name="uq_lot_milliemes_type"),
    )


class ReleveType(PublicIdMixin, db.Model):
    __tablename__ = "releve_types"

    id = db.Column(db.Integer, primary_key=True)
    copropriete_id = db.Column(
        db.Integer, db.ForeignKey("coproprietes.id"), nullable=False, index=True
    )
    libelle = db.Column(db.String(100), nullable=False)
    unite = db.Column(db.String(20), default="")
    ordre = db.Column(db.Integer, default=0)

    copropriete = db.relationship("Copropriete", back_populates="releve_types")
    compteurs = db.relationship("Compteur", back_populates="type_releve")


class Compteur(PublicIdMixin, SoftDeleteMixin, db.Model):
    __tablename__ = "compteurs"

    id = db.Column(db.Integer, primary_key=True)
    lot_id = db.Column(db.Integer, db.ForeignKey("lots.id"), nullable=False, index=True)
    releve_type_id = db.Column(
        db.Integer, db.ForeignKey("releve_types.id"), nullable=True, index=True
    )
    type_compteur = db.Column(db.String(20), default="")  # legacy
    numero = db.Column(db.String(50), default="")
    emplacement = db.Column(db.String(100), default="")
    date_installation = db.Column(db.Date, nullable=True)
    date_fin = db.Column(db.Date, nullable=True)
    actif = db.Column(db.Boolean, default=True, nullable=False)

    lot = db.relationship("Lot", back_populates="compteurs")
    type_releve = db.relationship("ReleveType", back_populates="compteurs")
    releves = db.relationship(
        "Releve", back_populates="compteur", cascade="all, delete-orphan"
    )

    @property
    def type_libelle(self) -> str:
        if self.type_releve:
            return self.type_releve.libelle
        legacy = {"eau_froide": "Eau froide", "eau_chaude": "Eau chaude"}
        return legacy.get(self.type_compteur, self.type_compteur or "Relevé")


class Releve(PublicIdMixin, SoftDeleteMixin, db.Model):
    __tablename__ = "releves"

    id = db.Column(db.Integer, primary_key=True)
    compteur_id = db.Column(
        db.Integer, db.ForeignKey("compteurs.id"), nullable=False, index=True
    )
    date_releve = db.Column(db.Date, nullable=False, default=date.today)
    index_valeur = db.Column(db.Float, nullable=False)
    remarque = db.Column(db.Text, default="")
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    compteur = db.relationship("Compteur", back_populates="releves")

    def consommation_depuis_precedent(self) -> float | None:
        precedent = (
            Releve.query_actifs()
            .filter(
                Releve.compteur_id == self.compteur_id,
                Releve.date_releve < self.date_releve,
            )
            .order_by(Releve.date_releve.desc(), Releve.id.desc())
            .first()
        )
        if precedent:
            return self.index_valeur - precedent.index_valeur
        return None