-- =====================================================================
-- MODÈLE DE DONNÉES — EXTENSION PHASE 3 (opérationnel & support)
-- À appliquer APRÈS schema-mvp + schema-phase2.
-- Domaines : maintenance, personnel d'immeuble + RH, paie agence,
--   visites, pige/prospects/parrainage, notifications, charges & TFU,
--   scoring & liste noire, juridique (modèles, litiges), états des lieux,
--   cycle de vie, financier avancé (transferts, rapprochement), corbeille.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Extension de l'énumération des types de documents (juridique & transactions).
ALTER TABLE documents
    MODIFY type_document ENUM(
        'quittance','recu','bail','etat_lieux','decompte_sortie','attestation',
        'recu_caution','avis_consommation','mandat','releve_gerance',
        'recu_commission','recu_vente','mise_en_demeure','conge','fiche_bien','autre'
    ) NOT NULL;

-- =====================================================================
-- MAINTENANCE & RÉPARATIONS (Module 13)
-- Source locataire -> validation agence préalable. Payeur choisi manuellement.
-- =====================================================================

CREATE TABLE prestataires (
    id          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    agence_id   INT UNSIGNED  NULL,
    nom         VARCHAR(150)  NOT NULL,
    specialite  VARCHAR(120)  NULL,                    -- plombier, électricien, maçon...
    telephone   VARCHAR(40)   NULL,
    actif       TINYINT(1)    NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    CONSTRAINT fk_prestataire_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE tickets_maintenance (
    id                  INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bien_id             INT UNSIGNED  NOT NULL,
    bail_id             INT UNSIGNED  NULL,
    categorie           VARCHAR(80)   NULL,            -- plomberie, électricité, peinture...
    priorite            ENUM('normal','urgent') NOT NULL DEFAULT 'normal',
    description         TEXT          NOT NULL,
    source              ENUM('locataire','agent','gardien') NOT NULL,
    statut_validation   ENUM('valide','en_attente','rejete') NOT NULL DEFAULT 'valide', -- locataire -> en_attente
    statut              ENUM('ouvert','en_cours','resolu','cloture') NOT NULL DEFAULT 'ouvert',
    prestataire_id      INT UNSIGNED  NULL,
    payeur              ENUM('proprietaire','agence','locataire') NULL,  -- CHOISI MANUELLEMENT
    cout                DECIMAL(15,2) NULL,
    date_prevue         DATE          NULL,
    date_resolution     DATE          NULL,
    signale_par         INT UNSIGNED  NULL,
    cree_le             DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_ticket_bien (bien_id),
    KEY idx_ticket_statut (statut),
    CONSTRAINT fk_ticket_bien FOREIGN KEY (bien_id) REFERENCES biens(id),
    CONSTRAINT fk_ticket_bail FOREIGN KEY (bail_id) REFERENCES baux(id) ON DELETE SET NULL,
    CONSTRAINT fk_ticket_prestataire FOREIGN KEY (prestataire_id) REFERENCES prestataires(id) ON DELETE SET NULL,
    CONSTRAINT fk_ticket_signaleur FOREIGN KEY (signale_par) REFERENCES utilisateurs(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE ticket_suivi (
    id          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    ticket_id   INT UNSIGNED  NOT NULL,
    commentaire TEXT          NULL,
    statut      VARCHAR(40)   NULL,
    par_user    INT UNSIGNED  NULL,
    cree_le     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_ticketsuivi_ticket (ticket_id),
    CONSTRAINT fk_ticketsuivi_ticket FOREIGN KEY (ticket_id) REFERENCES tickets_maintenance(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- PERSONNEL D'IMMEUBLE + RH (Module 14) — distinct de la paie agence
-- =====================================================================

CREATE TABLE personnel_immeuble (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    agence_id       INT UNSIGNED  NULL,
    nom_complet     VARCHAR(150)  NOT NULL,
    fonction        VARCHAR(80)   NULL,                -- gardien, entretien, jardinier...
    telephone       VARCHAR(40)   NULL,
    piece_numero    VARCHAR(60)   NULL,
    date_embauche   DATE          NULL,
    photo           VARCHAR(255)  NULL,
    actif           TINYINT(1)    NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    CONSTRAINT fk_personnel_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE personnel_affectations (
    personnel_id    INT UNSIGNED  NOT NULL,
    bien_id         INT UNSIGNED  NOT NULL,
    prise_en_charge ENUM('proprietaire','agence') NOT NULL DEFAULT 'proprietaire',
    PRIMARY KEY (personnel_id, bien_id),
    CONSTRAINT fk_affect_personnel FOREIGN KEY (personnel_id) REFERENCES personnel_immeuble(id) ON DELETE CASCADE,
    CONSTRAINT fk_affect_bien FOREIGN KEY (bien_id) REFERENCES biens(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE personnel_remuneration (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    personnel_id    INT UNSIGNED  NOT NULL,
    periode         CHAR(7)       NOT NULL,
    montant         DECIMAL(15,2) NOT NULL,
    mode            ENUM('especes','momo','moov','virement') NULL,
    date_paiement   DATE          NULL,
    document_id     INT UNSIGNED  NULL,
    PRIMARY KEY (id),
    KEY idx_persrem_personnel (personnel_id),
    CONSTRAINT fk_persrem_personnel FOREIGN KEY (personnel_id) REFERENCES personnel_immeuble(id) ON DELETE CASCADE,
    CONSTRAINT fk_persrem_doc FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE personnel_presence (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    personnel_id    INT UNSIGNED  NOT NULL,
    jour            DATE          NOT NULL,
    statut          ENUM('present','absent','retard','conge') NOT NULL,
    note            VARCHAR(255)  NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_presence (personnel_id, jour),
    CONSTRAINT fk_presence_personnel FOREIGN KEY (personnel_id) REFERENCES personnel_immeuble(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE personnel_conges (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    personnel_id    INT UNSIGNED  NOT NULL,
    date_debut      DATE          NOT NULL,
    date_fin        DATE          NOT NULL,
    type            VARCHAR(80)   NULL,
    statut          ENUM('demande','accorde','refuse') NOT NULL DEFAULT 'demande',
    PRIMARY KEY (id),
    KEY idx_conges_personnel (personnel_id),
    CONSTRAINT fk_conges_personnel FOREIGN KEY (personnel_id) REFERENCES personnel_immeuble(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE personnel_incidents (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    personnel_id    INT UNSIGNED  NOT NULL,
    date_incident   DATE          NOT NULL,
    description     TEXT          NOT NULL,
    PRIMARY KEY (id),
    KEY idx_incident_personnel (personnel_id),
    CONSTRAINT fk_incident_personnel FOREIGN KEY (personnel_id) REFERENCES personnel_immeuble(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- PAIE DES EMPLOYÉS DE L'AGENCE (module promu) — distinct du personnel d'immeuble
-- =====================================================================

CREATE TABLE employes_agence (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    utilisateur_id  INT UNSIGNED  NULL,                -- lien compte interne
    agence_id       INT UNSIGNED  NULL,
    nom_complet     VARCHAR(150)  NOT NULL,
    fonction        VARCHAR(80)   NULL,                -- agent, comptable, gérant...
    date_embauche   DATE          NULL,
    salaire_base    DECIMAL(15,2) NULL,
    actif           TINYINT(1)    NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    CONSTRAINT fk_emp_user FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id) ON DELETE SET NULL,
    CONSTRAINT fk_emp_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE objectifs_agent (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    employe_id      INT UNSIGNED  NOT NULL,
    periode         CHAR(7)       NOT NULL,
    objectif_type   ENUM('locations','ventes','visites','ca') NOT NULL,
    cible           DECIMAL(15,2) NOT NULL,
    realise         DECIMAL(15,2) NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY idx_objectif_emp (employe_id),
    CONSTRAINT fk_objectif_emp FOREIGN KEY (employe_id) REFERENCES employes_agence(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Commissions internes de l'agent (COÛT pour l'agence) — distinctes des
-- commissions agence/démarcheur (table `commissions`, qui sont des revenus).
CREATE TABLE commissions_internes (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    employe_id      INT UNSIGNED  NOT NULL,
    transaction_type ENUM('location','vente') NOT NULL,
    bail_id         INT UNSIGNED  NULL,
    dossier_vente_id INT UNSIGNED NULL,
    montant         DECIMAL(15,2) NOT NULL,
    statut_reglement ENUM('du','paye') NOT NULL DEFAULT 'du',
    PRIMARY KEY (id),
    KEY idx_cominterne_emp (employe_id),
    CONSTRAINT fk_cominterne_emp FOREIGN KEY (employe_id) REFERENCES employes_agence(id) ON DELETE CASCADE,
    CONSTRAINT fk_cominterne_bail FOREIGN KEY (bail_id) REFERENCES baux(id) ON DELETE SET NULL,
    CONSTRAINT fk_cominterne_vente FOREIGN KEY (dossier_vente_id) REFERENCES dossiers_vente(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE bulletins_paie (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    employe_id      INT UNSIGNED  NOT NULL,
    periode         CHAR(7)       NOT NULL,
    salaire_base    DECIMAL(15,2) NOT NULL DEFAULT 0,
    primes          DECIMAL(15,2) NOT NULL DEFAULT 0,
    commissions     DECIMAL(15,2) NOT NULL DEFAULT 0,
    retenues        DECIMAL(15,2) NOT NULL DEFAULT 0,
    net_a_payer     DECIMAL(15,2) NOT NULL DEFAULT 0,
    document_id     INT UNSIGNED  NULL,
    PRIMARY KEY (id),
    KEY idx_paie_emp (employe_id),
    CONSTRAINT fk_paie_emp FOREIGN KEY (employe_id) REFERENCES employes_agence(id) ON DELETE CASCADE,
    CONSTRAINT fk_paie_doc FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- VISITES (Module 15) — agenda & conflits gérés applicativement
-- =====================================================================

CREATE TABLE visites (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bien_id         INT UNSIGNED  NOT NULL,
    prospect_id     INT UNSIGNED  NULL,
    agent_id        INT UNSIGNED  NULL,
    date_heure      DATETIME      NOT NULL,
    type            ENUM('location','achat') NOT NULL,
    statut          ENUM('planifiee','effectuee','annulee') NOT NULL DEFAULT 'planifiee',
    niveau_interet  ENUM('tres_interesse','a_relancer','pas_interesse') NULL,
    compte_rendu    TEXT          NULL,
    suite_a_donner  VARCHAR(255)  NULL,
    PRIMARY KEY (id),
    KEY idx_visite_bien (bien_id),
    KEY idx_visite_agent (agent_id, date_heure),
    CONSTRAINT fk_visite_bien FOREIGN KEY (bien_id) REFERENCES biens(id),
    CONSTRAINT fk_visite_agent FOREIGN KEY (agent_id) REFERENCES utilisateurs(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- PIGE & PROSPECTS (Module 16) + PARRAINAGE + LISTE D'ATTENTE
-- Matching = dérivé (croisement critères <-> biens), pas de table dédiée.
-- =====================================================================

CREATE TABLE prospects (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    agence_id       INT UNSIGNED  NULL,
    nom             VARCHAR(150)  NOT NULL,
    telephone       VARCHAR(40)   NOT NULL,
    source          ENUM('vitrine','telephone','whatsapp','bureau','recommandation') NULL,
    statut          ENUM('nouveau','en_cours','visite','converti','perdu') NOT NULL DEFAULT 'nouveau',
    agent_id        INT UNSIGNED  NULL,
    -- Critères de recherche
    type_recherche  ENUM('location','achat','terrain') NULL,
    budget_min      DECIMAL(15,2) NULL,
    budget_max      DECIMAL(15,2) NULL,
    zone            VARCHAR(120)  NULL,
    nb_chambres_min SMALLINT      NULL,
    type_local      ENUM('habitation','commercial','bureau','boutique') NULL,
    meuble          TINYINT(1)    NULL,
    cree_le         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_prospect_agence (agence_id),
    KEY idx_prospect_statut (statut),
    CONSTRAINT fk_prospect_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL,
    CONSTRAINT fk_prospect_agent FOREIGN KEY (agent_id) REFERENCES utilisateurs(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- FK différée : visites.prospect_id -> prospects (créée ici car prospects vient après).
ALTER TABLE visites
    ADD CONSTRAINT fk_visite_prospect FOREIGN KEY (prospect_id) REFERENCES prospects(id) ON DELETE SET NULL;

CREATE TABLE liste_attente (
    id          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bien_id     INT UNSIGNED  NOT NULL,
    prospect_id INT UNSIGNED  NOT NULL,
    cree_le     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_attente (bien_id, prospect_id),
    CONSTRAINT fk_attente_bien FOREIGN KEY (bien_id) REFERENCES biens(id) ON DELETE CASCADE,
    CONSTRAINT fk_attente_prospect FOREIGN KEY (prospect_id) REFERENCES prospects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE parrainages (
    id                  INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    parrain_locataire_id INT UNSIGNED NOT NULL,
    filleul_prospect_id INT UNSIGNED  NULL,
    statut              ENUM('en_cours','filleul_signe','recompense_versee') NOT NULL DEFAULT 'en_cours',
    recompense_montant  DECIMAL(15,2) NULL,            -- modalité à préciser en réalisation
    cree_le             DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_parrain_loc FOREIGN KEY (parrain_locataire_id) REFERENCES locataires(id) ON DELETE CASCADE,
    CONSTRAINT fk_parrain_prospect FOREIGN KEY (filleul_prospect_id) REFERENCES prospects(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- NOTIFICATIONS (Module 24) — socle natif + passerelles optionnelles
-- =====================================================================

CREATE TABLE modeles_messages (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    agence_id       INT UNSIGNED  NULL,
    type_evenement  VARCHAR(60)   NOT NULL,            -- loyer_echeance, retard, fin_bail...
    canal           ENUM('interne','email','whatsapp_semi','sms','whatsapp_api') NOT NULL,
    sujet           VARCHAR(180)  NULL,
    corps_template  TEXT          NOT NULL,            -- avec champs variables {nom}, {montant}...
    PRIMARY KEY (id),
    KEY idx_modele_event (type_evenement),
    CONSTRAINT fk_modele_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE notifications_config (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    agence_id       INT UNSIGNED  NULL,
    type_evenement  VARCHAR(60)   NOT NULL,
    canal           ENUM('interne','email','whatsapp_semi','sms','whatsapp_api') NOT NULL,
    actif           TINYINT(1)    NOT NULL DEFAULT 1,
    delai_valeur    INT           NULL,                -- ex : 3
    delai_unite     ENUM('minutes','heures','jours') NULL,  -- ex : jours avant échéance
    PRIMARY KEY (id),
    UNIQUE KEY uq_notifconfig (agence_id, type_evenement, canal),
    CONSTRAINT fk_notifconfig_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE notifications (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    destinataire_user_id INT UNSIGNED NULL,
    destinataire_contact VARCHAR(120) NULL,            -- tel/email si pas de compte
    type_evenement      VARCHAR(60)   NOT NULL,
    canal               ENUM('interne','email','whatsapp_semi','sms','whatsapp_api') NOT NULL,
    contenu             TEXT          NOT NULL,
    statut              ENUM('en_attente','envoye','lu','echec') NOT NULL DEFAULT 'en_attente',
    date_creation       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    date_envoi          DATETIME      NULL,
    PRIMARY KEY (id),
    KEY idx_notif_dest (destinataire_user_id),
    KEY idx_notif_statut (statut),
    CONSTRAINT fk_notif_dest FOREIGN KEY (destinataire_user_id) REFERENCES utilisateurs(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Passerelles externes optionnelles (SMS / WhatsApp API). Paramètres SENSIBLES.
CREATE TABLE passerelles_config (
    id          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    agence_id   INT UNSIGNED  NULL,
    type        ENUM('sms','whatsapp_api') NOT NULL,
    actif       TINYINT(1)    NOT NULL DEFAULT 0,       -- désactivé par défaut
    parametres  TEXT          NULL,                     -- credentials chiffrés (appli)
    PRIMARY KEY (id),
    CONSTRAINT fk_passerelle_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- CHARGES & TAXES (Module 20) — TFU en suivi simple
-- =====================================================================

CREATE TABLE taxes_foncieres (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bien_id         INT UNSIGNED  NOT NULL,
    annee           SMALLINT      NOT NULL,
    montant         DECIMAL(15,2) NOT NULL,            -- saisi, pas calculé
    date_echeance   DATE          NULL,
    statut          ENUM('a_payer','payee') NOT NULL DEFAULT 'a_payer',
    supporte_par    ENUM('proprietaire','agence','locataire') NOT NULL DEFAULT 'proprietaire',
    PRIMARY KEY (id),
    UNIQUE KEY uq_tfu (bien_id, annee),
    CONSTRAINT fk_tfu_bien FOREIGN KEY (bien_id) REFERENCES biens(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE charges_bien (
    id          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bien_id     INT UNSIGNED  NOT NULL,
    type_charge VARCHAR(120)  NOT NULL,                -- eau, ordures, gardiennage...
    qui_paie    ENUM('proprietaire','locataire') NOT NULL,
    recuperable TINYINT(1)    NOT NULL DEFAULT 0,       -- refacturable au locataire
    montant     DECIMAL(15,2) NULL,
    PRIMARY KEY (id),
    KEY idx_charge_bien (bien_id),
    CONSTRAINT fk_charge_bien FOREIGN KEY (bien_id) REFERENCES biens(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- SCORING & LISTE NOIRE (Module 18)
-- Le score lui-même est CALCULÉ (dérivé). On stocke les pondérations
-- configurables et le détail de la liste noire.
-- =====================================================================

CREATE TABLE scoring_ponderations (
    id          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    agence_id   INT UNSIGNED  NULL,
    critere     VARCHAR(80)   NOT NULL,                -- retards, recours_garantie, impayes...
    poids       SMALLINT      NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    CONSTRAINT fk_ponderation_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE liste_noire (
    id          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    locataire_id INT UNSIGNED NOT NULL,
    motif       VARCHAR(255)  NOT NULL,
    par_user    INT UNSIGNED  NULL,
    cree_le     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_listenoire_loc (locataire_id),
    CONSTRAINT fk_listenoire_loc FOREIGN KEY (locataire_id) REFERENCES locataires(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- NB : programme de fidélité = modalité de récompense laissée à préciser
--      au niveau champ ; non modélisé ici pour ne rien inventer.

-- =====================================================================
-- JURIDIQUE (Module 33) — modèles éditables, litiges
-- =====================================================================

-- Modèles de documents éditables à champs variables (baux, mandats,
-- mises en demeure, congés...). Sert aussi au Module 4.
CREATE TABLE modeles_documents (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    agence_id       INT UNSIGNED  NULL,
    type_document   VARCHAR(60)   NOT NULL,
    libelle         VARCHAR(150)  NOT NULL,
    contenu_template MEDIUMTEXT   NOT NULL,            -- avec {champs_variables}
    actif           TINYINT(1)    NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    KEY idx_modeledoc_type (type_document),
    CONSTRAINT fk_modeledoc_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE litiges (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    locataire_id    INT UNSIGNED  NULL,
    bail_id         INT UNSIGNED  NULL,
    objet           VARCHAR(180)  NOT NULL,
    statut          ENUM('ouvert','en_cours','clos') NOT NULL DEFAULT 'ouvert',
    notes           TEXT          NULL,
    date_ouverture  DATE          NOT NULL,
    PRIMARY KEY (id),
    KEY idx_litige_loc (locataire_id),
    CONSTRAINT fk_litige_loc FOREIGN KEY (locataire_id) REFERENCES locataires(id) ON DELETE SET NULL,
    CONSTRAINT fk_litige_bail FOREIGN KEY (bail_id) REFERENCES baux(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE litige_documents (
    litige_id   INT UNSIGNED  NOT NULL,
    document_id INT UNSIGNED  NOT NULL,
    PRIMARY KEY (litige_id, document_id),
    CONSTRAINT fk_litigedoc_litige FOREIGN KEY (litige_id) REFERENCES litiges(id) ON DELETE CASCADE,
    CONSTRAINT fk_litigedoc_doc FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- ÉTATS DES LIEUX (Module 4 — détail) & CYCLE DE VIE (Module 35)
-- =====================================================================

CREATE TABLE etats_lieux (
    id          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bail_id     INT UNSIGNED  NOT NULL,
    type        ENUM('entree','sortie') NOT NULL,
    date_etat   DATE          NOT NULL,
    document_id INT UNSIGNED  NULL,
    PRIMARY KEY (id),
    KEY idx_edl_bail (bail_id),
    CONSTRAINT fk_edl_bail FOREIGN KEY (bail_id) REFERENCES baux(id) ON DELETE CASCADE,
    CONSTRAINT fk_edl_doc FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE etat_lieux_lignes (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    etat_lieux_id   INT UNSIGNED  NOT NULL,
    piece           VARCHAR(120)  NOT NULL,
    description     TEXT          NULL,
    etat            VARCHAR(80)   NULL,
    photos          TEXT          NULL,                -- chemins (liste)
    PRIMARY KEY (id),
    KEY idx_edlligne_edl (etat_lieux_id),
    CONSTRAINT fk_edlligne_edl FOREIGN KEY (etat_lieux_id) REFERENCES etats_lieux(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- États successifs / photos avant-après travaux (cycle de vie du bien).
CREATE TABLE bien_etats (
    id          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bien_id     INT UNSIGNED  NOT NULL,
    date_etat   DATE          NOT NULL,
    type        ENUM('constat','avant_travaux','apres_travaux') NOT NULL,
    description TEXT          NULL,
    photos      TEXT          NULL,
    PRIMARY KEY (id),
    KEY idx_bienetat_bien (bien_id),
    CONSTRAINT fk_bienetat_bien FOREIGN KEY (bien_id) REFERENCES biens(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- FINANCIER AVANCÉ (Module 34) — transferts & rapprochement
-- (caisses multiples & petite caisse = lignes de `caisses` déjà existante ;
--  dettes/créances & tableau commissions = vues dérivées, pas de table)
-- =====================================================================

CREATE TABLE transferts_caisse (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    caisse_source_id INT UNSIGNED NOT NULL,
    caisse_dest_id  INT UNSIGNED  NOT NULL,
    montant         DECIMAL(15,2) NOT NULL,
    date_transfert  DATE          NOT NULL,
    note            VARCHAR(255)  NULL,
    cree_par        INT UNSIGNED  NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_transfert_src FOREIGN KEY (caisse_source_id) REFERENCES caisses(id),
    CONSTRAINT fk_transfert_dst FOREIGN KEY (caisse_dest_id) REFERENCES caisses(id),
    CONSTRAINT fk_transfert_user FOREIGN KEY (cree_par) REFERENCES utilisateurs(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Relevés importés (banque/MoMo) pour rapprochement assisté (pas d'API).
CREATE TABLE releves_importes (
    id          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    caisse_id   INT UNSIGNED  NULL,
    type        ENUM('banque','momo','moov') NOT NULL,
    periode     CHAR(7)       NULL,
    fichier     VARCHAR(255)  NULL,
    importe_le  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_releveimp_caisse FOREIGN KEY (caisse_id) REFERENCES caisses(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- TRANSVERSAUX
-- =====================================================================

-- Corbeille : suppression douce restaurable (entités non-financières).
-- Les documents financiers émis restent IMMUABLES (avoirs uniquement).
CREATE TABLE corbeille (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    entite_type     VARCHAR(60)   NOT NULL,            -- 'bien','locataire','prospect'...
    entite_id       BIGINT UNSIGNED NOT NULL,
    donnees         JSON          NULL,                -- snapshot pour restauration
    supprime_par    INT UNSIGNED  NULL,
    supprime_le     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    restaure        TINYINT(1)    NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY idx_corbeille_entite (entite_type, entite_id),
    CONSTRAINT fk_corbeille_user FOREIGN KEY (supprime_par) REFERENCES utilisateurs(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Journal des imports de données (onboarding Excel/CSV).
CREATE TABLE imports_log (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    type_import     VARCHAR(60)   NOT NULL,            -- biens, locataires, proprietaires
    fichier         VARCHAR(255)  NULL,
    lignes_importees INT          NOT NULL DEFAULT 0,
    lignes_erreur   INT           NOT NULL DEFAULT 0,
    par_user        INT UNSIGNED  NULL,
    cree_le         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_import_user FOREIGN KEY (par_user) REFERENCES utilisateurs(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- FIN EXTENSION PHASE 3 — LE MODÈLE DE DONNÉES EST COMPLET
-- ---------------------------------------------------------------------
-- SANS TABLE DÉDIÉE (dérivé / agrégé / client-side) :
--   * Statistiques & BI (Module 22) : agrégations des tables existantes.
--   * Recherche globale : index sur tables existantes.
--   * Calendrier global : vue agrégée des dates (échéances, visites,
--     fins de bail, réservations, TFU, maintenance).
--   * Mode hors-ligne (Module 31) : Service Worker + cache, côté client.
--   * Matching pige : croisement critères prospects <-> biens (requête).
--   * Multilingue : fichiers i18n côté interface (+ libellés déjà en base).
--   * Dettes/créances & tableau commissions : vues dérivées.
--
-- À PRÉCISER AU NIVEAU CHAMP AVANT RÉALISATION :
--   * Programme de fidélité : modalité de récompense non définie.
--   * Avis/notation des biens (Module 25) : table à ajouter quand le
--     format d'avis sera arrêté (note, commentaire, modération).
-- =====================================================================