-- =====================================================================
-- MODÈLE DE DONNÉES — Système de gestion immobilière
-- Périmètre : FONDATION + MVP (Phase 1)
-- SGBD cible : MySQL / MariaDB (cPanel Namecheap)
-- Moteur : InnoDB — Jeu de caractères : utf8mb4
-- Devise : FCFA (DECIMAL sans subdivision réelle, stockée en (15,2))
-- =====================================================================
-- NOTE : ce schéma couvre le noyau vendable (Phase 1) + les entités
-- pivot dont le MVP dépend. Les Phases 2/3 viendront l'étendre.
-- Toutes les tables monétaires sont en FCFA. Coordonnées en DECIMAL(10,7).
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- DOMAINE SYSTÈME & CONFIGURATION
-- =====================================================================

-- Paramètres système (clé/valeur) — contrôlés par le super admin éditeur.
-- Ex : devise, fuseau, valeur_parcelle_m2, tarif_kwh_defaut, seuil_accord_proprietaire,
--      prefixe_quittance, prefixe_recu, format_numerotation...
CREATE TABLE parametres (
    cle             VARCHAR(80)   NOT NULL,
    valeur          TEXT          NULL,
    description     VARCHAR(255)  NULL,
    modifie_le      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (cle)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Identité visuelle de l'agence (une seule ligne) — contrôlée par le super admin éditeur.
CREATE TABLE branding (
    id                  TINYINT       NOT NULL DEFAULT 1,
    nom_agence          VARCHAR(150)  NOT NULL,
    slogan              VARCHAR(255)  NULL,
    logo_clair          VARCHAR(255)  NULL,
    logo_fonce          VARCHAR(255)  NULL,
    favicon             VARCHAR(255)  NULL,
    couleur_primaire    VARCHAR(7)    NULL,
    couleur_secondaire  VARCHAR(7)    NULL,
    adresse             VARCHAR(255)  NULL,
    telephone           VARCHAR(40)   NULL,
    whatsapp            VARCHAR(40)   NULL,
    email               VARCHAR(150)  NULL,
    ifu                 VARCHAR(40)   NULL,
    rccm                VARCHAR(60)   NULL,
    reseaux_sociaux     TEXT          NULL,
    modifie_le          DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT chk_branding_unique CHECK (id = 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Antennes / agences internes (multi-agence OPTIONNEL).
-- Une instance mono-bureau aura une seule ligne (est_principale = 1).
CREATE TABLE agences (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    nom             VARCHAR(150)  NOT NULL,
    adresse         VARCHAR(255)  NULL,
    telephone       VARCHAR(40)   NULL,
    est_principale  TINYINT(1)    NOT NULL DEFAULT 0,
    actif           TINYINT(1)    NOT NULL DEFAULT 1,
    cree_le         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Utilisateurs : comptes internes ET externes (portail). Rôles prédéfinis figés.
CREATE TABLE utilisateurs (
    id                  INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    agence_id           INT UNSIGNED  NULL,            -- antenne de rattachement (NULL = super admin)
    role                ENUM('super_admin','gerant','agent','comptable','gardien',
                             'locataire','proprietaire','acheteur') NOT NULL,
    nom_complet         VARCHAR(150)  NOT NULL,
    email               VARCHAR(150)  NULL,
    telephone           VARCHAR(40)   NULL,            -- identifiant central (tel/WhatsApp)
    mot_de_passe_hash   VARCHAR(255)  NOT NULL,
    actif               TINYINT(1)    NOT NULL DEFAULT 1,
    -- Validation des comptes externes (auto-inscription -> à valider par l'agence)
    statut_validation   ENUM('valide','en_attente','rejete') NOT NULL DEFAULT 'valide',
    -- 2FA optionnelle (TOTP ou email) — jamais SMS dans le socle
    tfa_active          TINYINT(1)    NOT NULL DEFAULT 0,
    tfa_secret          VARCHAR(120)  NULL,
    derniere_connexion  DATETIME      NULL,
    cree_le             DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_email (email),
    KEY idx_users_role (role),
    KEY idx_users_agence (agence_id),
    CONSTRAINT fk_users_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Journal d'audit — ACTIONS SENSIBLES uniquement + accès aux pièces sensibles.
-- Entrées INALTÉRABLES (aucun UPDATE/DELETE applicatif autorisé).
CREATE TABLE journal_audit (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    utilisateur_id  INT UNSIGNED    NULL,
    agence_id       INT UNSIGNED    NULL,
    action          VARCHAR(80)     NOT NULL,        -- ex : 'paiement.valide', 'caution.restitution'
    cible_type      VARCHAR(60)     NULL,            -- ex : 'bail', 'locataire', 'document'
    cible_id        BIGINT UNSIGNED NULL,
    valeur_avant    JSON            NULL,
    valeur_apres    JSON            NULL,
    horodatage      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- UTC+1
    PRIMARY KEY (id),
    KEY idx_audit_user (utilisateur_id),
    KEY idx_audit_cible (cible_type, cible_id),
    KEY idx_audit_date (horodatage)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- DOMAINE TIERS
-- =====================================================================

-- Propriétaires (mandants). Compte portail optionnel (utilisateur_id).
CREATE TABLE proprietaires (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    utilisateur_id  INT UNSIGNED  NULL,
    agence_id       INT UNSIGNED  NULL,
    nom_complet     VARCHAR(150)  NOT NULL,
    telephone       VARCHAR(40)   NULL,
    whatsapp        VARCHAR(40)   NULL,
    email           VARCHAR(150)  NULL,
    piece_type      VARCHAR(40)   NULL,
    piece_numero    VARCHAR(60)   NULL,
    mode_reversement_coord VARCHAR(120) NULL,         -- MoMo / banque / espèces
    cree_le         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_proprio_agence (agence_id),
    CONSTRAINT fk_proprio_user FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id) ON DELETE SET NULL,
    CONSTRAINT fk_proprio_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Locataires. Tel/WhatsApp = identifiant central (email facultatif).
-- Score de fiabilité conservé après départ (calcul auto en Phase 3).
CREATE TABLE locataires (
    id                  INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    utilisateur_id      INT UNSIGNED  NULL,
    agence_id           INT UNSIGNED  NULL,
    nom_complet         VARCHAR(150)  NOT NULL,
    date_naissance      DATE          NULL,
    profession          VARCHAR(120)  NULL,
    employeur           VARCHAR(150)  NULL,
    telephone           VARCHAR(40)   NOT NULL,
    telephone_secondaire VARCHAR(40)  NULL,
    whatsapp            VARCHAR(40)   NULL,
    email               VARCHAR(150)  NULL,
    piece_type          ENUM('cni','passeport','cip','autre') NULL,
    piece_numero        VARCHAR(60)   NULL,
    piece_scan          VARCHAR(255)  NULL,            -- pièce SENSIBLE (accès restreint)
    contact_urgence_nom VARCHAR(150)  NULL,
    contact_urgence_tel VARCHAR(40)   NULL,
    photo               VARCHAR(255)  NULL,
    score_fiabilite     TINYINT       NULL,            -- 0..100, alimenté en Phase 3
    sur_liste_noire     TINYINT(1)    NOT NULL DEFAULT 0, -- liste noire STRICTEMENT interne
    cree_le             DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_loc_agence (agence_id),
    KEY idx_loc_tel (telephone),
    CONSTRAINT fk_loc_user FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id) ON DELETE SET NULL,
    CONSTRAINT fk_loc_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Garants (caution personne) rattachés à un bail (cf. table baux).
CREATE TABLE garants (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    nom_complet     VARCHAR(150)  NOT NULL,
    telephone       VARCHAR(40)   NULL,
    piece_type      VARCHAR(40)   NULL,
    piece_numero    VARCHAR(60)   NULL,
    cree_le         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- DOMAINE BIENS (pivot central)
-- =====================================================================

-- Table de base des biens. Discriminateur type_bien.
-- Immeubles multi-lots : lot_parent_id auto-référence (un lot pointe son immeuble).
CREATE TABLE biens (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    reference       VARCHAR(30)   NOT NULL,            -- ex : BIEN-2026-00123
    agence_id       INT UNSIGNED  NULL,
    proprietaire_id INT UNSIGNED  NULL,                -- NULL = bien propre de l'agence
    lot_parent_id   INT UNSIGNED  NULL,                -- immeuble parent (multi-lots)
    type_bien       ENUM('location','meuble','vente','terrain') NOT NULL,
    -- Pour les locations : nature du local
    type_location   ENUM('habitation','commercial','bureau','boutique') NULL,
    designation     VARCHAR(180)  NOT NULL,
    descriptif      TEXT          NULL,
    -- Localisation : pin exact (interne) + quartier (public)
    adresse         VARCHAR(255)  NULL,
    quartier        VARCHAR(120)  NULL,
    latitude        DECIMAL(10,7) NULL,
    longitude       DECIMAL(10,7) NULL,
    nb_chambres     SMALLINT      NULL,
    nb_salles_eau   SMALLINT      NULL,
    superficie      DECIMAL(12,2) NULL,                -- m² (info libre)
    statut          ENUM('disponible','occupe','loue','sous_compromis','vendu','retire') NOT NULL DEFAULT 'disponible',
    publie_vitrine  TINYINT(1)    NOT NULL DEFAULT 0,  -- interrupteur publication vitrine
    cree_le         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_biens_reference (reference),
    KEY idx_biens_agence (agence_id),
    KEY idx_biens_type (type_bien),
    KEY idx_biens_statut (statut),
    KEY idx_biens_parent (lot_parent_id),
    CONSTRAINT fk_biens_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL,
    CONSTRAINT fk_biens_proprio FOREIGN KEY (proprietaire_id) REFERENCES proprietaires(id) ON DELETE SET NULL,
    CONSTRAINT fk_biens_parent FOREIGN KEY (lot_parent_id) REFERENCES biens(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Photos / galerie par bien.
CREATE TABLE biens_photos (
    id          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bien_id     INT UNSIGNED  NOT NULL,
    chemin      VARCHAR(255)  NOT NULL,
    ordre       SMALLINT      NOT NULL DEFAULT 0,
    est_couverture TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY idx_photos_bien (bien_id),
    CONSTRAINT fk_photos_bien FOREIGN KEY (bien_id) REFERENCES biens(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- DOMAINE BAUX & LOCATAIRES
-- =====================================================================

-- Bail (longue durée). Porte la config financière et la mécanique des poches.
CREATE TABLE baux (
    id                      INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bien_id                 INT UNSIGNED  NOT NULL,
    agence_id               INT UNSIGNED  NULL,
    type_location           ENUM('habitation','commercial','bureau','boutique') NOT NULL,
    date_entree             DATE          NOT NULL,
    duree_mois              SMALLINT      NULL,
    jour_paiement           TINYINT       NOT NULL DEFAULT 1,   -- jour du mois (1..28)
    loyer_mensuel           DECIMAL(15,2) NOT NULL,             -- loyer ACTUEL (historique : table dédiée)
    mode_paiement_habituel  ENUM('especes','momo','moov','virement') NULL,
    -- POCHE 1 — Avance de garantie (réserve) : config + solde courant
    garantie_mois_initiaux  SMALLINT      NOT NULL DEFAULT 0,   -- nb de mois d'avance à l'entrée
    garantie_montant_initial DECIMAL(15,2) NOT NULL DEFAULT 0,
    garantie_solde          DECIMAL(15,2) NOT NULL DEFAULT 0,   -- solde courant (alimenté par mouvements_garantie)
    -- POCHE 2 — Loyers prépayés : solde courant (en montant)
    prepaiement_solde       DECIMAL(15,2) NOT NULL DEFAULT 0,
    garant_id               INT UNSIGNED  NULL,
    statut                  ENUM('actif','echu','resilie','renouvele') NOT NULL DEFAULT 'actif',
    date_sortie             DATE          NULL,
    motif_sortie            VARCHAR(255)  NULL,
    cree_le                 DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_baux_bien (bien_id),
    KEY idx_baux_statut (statut),
    CONSTRAINT fk_baux_bien FOREIGN KEY (bien_id) REFERENCES biens(id),
    CONSTRAINT fk_baux_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL,
    CONSTRAINT fk_baux_garant FOREIGN KEY (garant_id) REFERENCES garants(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Lien bail <-> locataires (colocation : plusieurs locataires par bail).
CREATE TABLE bail_locataires (
    bail_id         INT UNSIGNED  NOT NULL,
    locataire_id    INT UNSIGNED  NOT NULL,
    est_principal   TINYINT(1)    NOT NULL DEFAULT 0,
    PRIMARY KEY (bail_id, locataire_id),
    CONSTRAINT fk_bl_bail FOREIGN KEY (bail_id) REFERENCES baux(id) ON DELETE CASCADE,
    CONSTRAINT fk_bl_loc FOREIGN KEY (locataire_id) REFERENCES locataires(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Historique du loyer dans le temps (révisions).
CREATE TABLE historique_loyer (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bail_id         INT UNSIGNED  NOT NULL,
    montant         DECIMAL(15,2) NOT NULL,
    date_effet      DATE          NOT NULL,
    motif           VARCHAR(255)  NULL,
    PRIMARY KEY (id),
    KEY idx_histloyer_bail (bail_id),
    CONSTRAINT fk_histloyer_bail FOREIGN KEY (bail_id) REFERENCES baux(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Clés / badges remis (vérifiés au décompte de sortie).
CREATE TABLE cles_badges (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bail_id         INT UNSIGNED  NOT NULL,
    libelle         VARCHAR(120)  NOT NULL,
    quantite        SMALLINT      NOT NULL DEFAULT 1,
    date_remise     DATE          NULL,
    date_restitution DATE         NULL,
    PRIMARY KEY (id),
    KEY idx_cles_bail (bail_id),
    CONSTRAINT fk_cles_bail FOREIGN KEY (bail_id) REFERENCES baux(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- DOMAINE CAUTIONS (multiples & configurables)
-- =====================================================================

-- Types de caution paramétrables (électricité, peinture, eau, ...).
CREATE TABLE types_caution (
    id          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    agence_id   INT UNSIGNED  NULL,
    libelle     VARCHAR(120)  NOT NULL,
    actif       TINYINT(1)    NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    CONSTRAINT fk_typecaution_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Cautions effectives d'un bail, avec suivi de restitution.
CREATE TABLE cautions_bail (
    id                  INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bail_id             INT UNSIGNED  NOT NULL,
    type_caution_id     INT UNSIGNED  NOT NULL,
    montant             DECIMAL(15,2) NOT NULL,
    montant_restitue    DECIMAL(15,2) NOT NULL DEFAULT 0,
    motif_retenue       VARCHAR(255)  NULL,
    statut              ENUM('versee','restituee_partielle','restituee_totale','retenue') NOT NULL DEFAULT 'versee',
    PRIMARY KEY (id),
    KEY idx_caution_bail (bail_id),
    CONSTRAINT fk_caution_bail FOREIGN KEY (bail_id) REFERENCES baux(id) ON DELETE CASCADE,
    CONSTRAINT fk_caution_type FOREIGN KEY (type_caution_id) REFERENCES types_caution(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- DOMAINE ENCAISSEMENT (échéances, paiements, poches)
-- =====================================================================

-- Échéancier mensuel des loyers (1 ligne par mois par bail).
CREATE TABLE echeances_loyer (
    id                  INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bail_id             INT UNSIGNED  NOT NULL,
    periode             CHAR(7)       NOT NULL,        -- 'AAAA-MM'
    montant_attendu     DECIMAL(15,2) NOT NULL,
    montant_paye        DECIMAL(15,2) NOT NULL DEFAULT 0,
    statut              ENUM('attendu','partiel','paye',
                             'couvert_prepaiement','ponctionne_garantie','impaye') NOT NULL DEFAULT 'attendu',
    date_echeance       DATE          NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_echeance (bail_id, periode),
    KEY idx_echeance_statut (statut),
    CONSTRAINT fk_echeance_bail FOREIGN KEY (bail_id) REFERENCES baux(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Paiements : double entrée (déclaration locataire à valider / saisie agent directe).
-- 4 natures figées. Référence MoMo OBLIGATOIRE si mode = momo/moov.
CREATE TABLE paiements (
    id                  INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bail_id             INT UNSIGNED  NOT NULL,
    echeance_id         INT UNSIGNED  NULL,            -- rattachement éventuel à un mois
    nature              ENUM('loyer_courant','prepaiement','renflouement_garantie','caution') NOT NULL,
    caution_bail_id     INT UNSIGNED  NULL,            -- si nature = caution
    montant             DECIMAL(15,2) NOT NULL,
    mode_paiement       ENUM('especes','momo','moov','virement') NOT NULL,
    reference_momo      VARCHAR(80)   NULL,            -- OBLIGATOIRE si momo/moov (contrôle applicatif + ci-dessous)
    preuve_fichier      VARCHAR(255)  NULL,
    date_paiement       DATE          NOT NULL,
    statut              ENUM('declare','valide','rejete') NOT NULL DEFAULT 'valide',
    declare_par_locataire TINYINT(1)  NOT NULL DEFAULT 0,
    valide_par          INT UNSIGNED  NULL,            -- agent qui a validé
    agent_id            INT UNSIGNED  NULL,            -- agent qui a saisi
    cree_le             DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_paiement_bail (bail_id),
    KEY idx_paiement_statut (statut),
    KEY idx_paiement_nature (nature),
    CONSTRAINT fk_paiement_bail FOREIGN KEY (bail_id) REFERENCES baux(id),
    CONSTRAINT fk_paiement_echeance FOREIGN KEY (echeance_id) REFERENCES echeances_loyer(id) ON DELETE SET NULL,
    CONSTRAINT fk_paiement_caution FOREIGN KEY (caution_bail_id) REFERENCES cautions_bail(id) ON DELETE SET NULL,
    CONSTRAINT fk_paiement_valideur FOREIGN KEY (valide_par) REFERENCES utilisateurs(id) ON DELETE SET NULL,
    CONSTRAINT fk_paiement_agent FOREIGN KEY (agent_id) REFERENCES utilisateurs(id) ON DELETE SET NULL,
    -- Référence MoMo obligatoire pour les paiements mobile money
    CONSTRAINT chk_reference_momo CHECK (
        (mode_paiement IN ('momo','moov') AND reference_momo IS NOT NULL AND reference_momo <> '')
        OR mode_paiement IN ('especes','virement')
    )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Grand livre de l'AVANCE DE GARANTIE (poche 1) — traçabilité + restitution au départ.
CREATE TABLE mouvements_garantie (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bail_id         INT UNSIGNED  NOT NULL,
    type            ENUM('versement_initial','ponction','renflouement','restitution') NOT NULL,
    montant         DECIMAL(15,2) NOT NULL,            -- signé selon le type (appli)
    solde_apres     DECIMAL(15,2) NOT NULL,
    paiement_id     INT UNSIGNED  NULL,
    echeance_id     INT UNSIGNED  NULL,                -- mois ponctionné le cas échéant
    date_mouvement  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    note            VARCHAR(255)  NULL,
    PRIMARY KEY (id),
    KEY idx_mvtgar_bail (bail_id),
    CONSTRAINT fk_mvtgar_bail FOREIGN KEY (bail_id) REFERENCES baux(id) ON DELETE CASCADE,
    CONSTRAINT fk_mvtgar_paiement FOREIGN KEY (paiement_id) REFERENCES paiements(id) ON DELETE SET NULL,
    CONSTRAINT fk_mvtgar_echeance FOREIGN KEY (echeance_id) REFERENCES echeances_loyer(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- DOMAINE DOCUMENTS
-- =====================================================================

-- Compteurs de numérotation séquentielle par type et par année.
CREATE TABLE sequences_documents (
    type_document   VARCHAR(40)   NOT NULL,            -- 'quittance','recu','bail',...
    annee           SMALLINT      NOT NULL,
    dernier_numero  INT UNSIGNED  NOT NULL DEFAULT 0,
    PRIMARY KEY (type_document, annee)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Documents générés / archivés. QR de vérification -> page publique (qr_token).
-- Pièces sensibles : flag sensible (accès restreint admin/gérant).
CREATE TABLE documents (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    numero          VARCHAR(40)   NOT NULL,            -- ex : QUIT-2026-00123
    type_document   ENUM('quittance','recu','bail','etat_lieux','decompte_sortie',
                         'attestation','recu_caution','avis_consommation','autre') NOT NULL,
    bien_id         INT UNSIGNED  NULL,
    bail_id         INT UNSIGNED  NULL,
    paiement_id     INT UNSIGNED  NULL,
    chemin_fichier  VARCHAR(255)  NULL,
    qr_token        CHAR(40)      NULL,                -- jeton de la page de vérification publique
    sensible        TINYINT(1)    NOT NULL DEFAULT 0,
    cree_le         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_doc_numero (numero),
    UNIQUE KEY uq_doc_qr (qr_token),
    KEY idx_doc_bien (bien_id),
    KEY idx_doc_bail (bail_id),
    CONSTRAINT fk_doc_bien FOREIGN KEY (bien_id) REFERENCES biens(id) ON DELETE SET NULL,
    CONSTRAINT fk_doc_bail FOREIGN KEY (bail_id) REFERENCES baux(id) ON DELETE SET NULL,
    CONSTRAINT fk_doc_paiement FOREIGN KEY (paiement_id) REFERENCES paiements(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- DOMAINE TRÉSORERIE (caisse — niveau MVP)
-- =====================================================================

CREATE TABLE caisses (
    id          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    agence_id   INT UNSIGNED  NULL,
    nom         VARCHAR(120)  NOT NULL,
    type        ENUM('especes','momo','moov','banque','autre') NOT NULL DEFAULT 'especes',
    solde       DECIMAL(15,2) NOT NULL DEFAULT 0,
    actif       TINYINT(1)    NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    CONSTRAINT fk_caisse_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE categories_flux (
    id          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    agence_id   INT UNSIGNED  NULL,
    sens        ENUM('recette','depense') NOT NULL,
    libelle     VARCHAR(120)  NOT NULL,
    actif       TINYINT(1)    NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    CONSTRAINT fk_categflux_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Mouvements de trésorerie. source_type/source_id = rattachement polymorphe
-- (ex : 'paiement'/123, 'reversement'/45) sans contrainte FK (souplesse).
CREATE TABLE mouvements_tresorerie (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    caisse_id       INT UNSIGNED  NOT NULL,
    categorie_id    INT UNSIGNED  NULL,
    sens            ENUM('entree','sortie') NOT NULL,
    montant         DECIMAL(15,2) NOT NULL,
    mode_paiement   ENUM('especes','momo','moov','virement') NULL,
    date_mouvement  DATE          NOT NULL,
    libelle         VARCHAR(255)  NULL,
    source_type     VARCHAR(40)   NULL,
    source_id       BIGINT UNSIGNED NULL,
    cree_par        INT UNSIGNED  NULL,
    cree_le         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_mvt_caisse (caisse_id),
    KEY idx_mvt_date (date_mouvement),
    KEY idx_mvt_source (source_type, source_id),
    CONSTRAINT fk_mvt_caisse FOREIGN KEY (caisse_id) REFERENCES caisses(id),
    CONSTRAINT fk_mvt_categ FOREIGN KEY (categorie_id) REFERENCES categories_flux(id) ON DELETE SET NULL,
    CONSTRAINT fk_mvt_user FOREIGN KEY (cree_par) REFERENCES utilisateurs(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- DOMAINE VITRINE & PORTAIL (niveau MVP)
-- =====================================================================

-- Demandes entrantes depuis le formulaire de la vitrine publique (alimente la pige en Phase 2).
CREATE TABLE demandes_contact (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bien_id         INT UNSIGNED  NULL,                -- bien concerné (si depuis une fiche)
    nom             VARCHAR(150)  NOT NULL,
    telephone       VARCHAR(40)   NOT NULL,
    message         TEXT          NULL,
    traite          TINYINT(1)    NOT NULL DEFAULT 0,
    cree_le         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_demande_bien (bien_id),
    CONSTRAINT fk_demande_bien FOREIGN KEY (bien_id) REFERENCES biens(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Messagerie bidirectionnelle (espace client <-> agence), rattachée à un dossier.
CREATE TABLE messages (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    expediteur_id   INT UNSIGNED  NOT NULL,
    destinataire_id INT UNSIGNED  NULL,                -- NULL = vers l'agence (boîte centralisée)
    contexte_type   VARCHAR(40)   NULL,                -- 'bail','mandat','dossier_vente'
    contexte_id     BIGINT UNSIGNED NULL,
    corps           TEXT          NOT NULL,
    lu              TINYINT(1)    NOT NULL DEFAULT 0,
    cree_le         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_msg_dest (destinataire_id),
    KEY idx_msg_contexte (contexte_type, contexte_id),
    CONSTRAINT fk_msg_exp FOREIGN KEY (expediteur_id) REFERENCES utilisateurs(id),
    CONSTRAINT fk_msg_dest FOREIGN KEY (destinataire_id) REFERENCES utilisateurs(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- FIN DU SCHÉMA FONDATION + MVP
-- ---------------------------------------------------------------------
-- COUVERT ICI : système/config, branding, antennes, utilisateurs, audit,
--   propriétaires (minimal), locataires, garants, biens (+ multi-lots,
--   photos), baux (+ colocation, historique loyer, clés), cautions
--   (types + suivi restitution), échéancier, paiements (4 natures,
--   référence MoMo obligatoire), grand livre garantie, documents
--   (numérotation + QR), trésorerie (caisses, catégories, mouvements),
--   vitrine (demandes de contact), messagerie portail.
--
-- NON COUVERT (Phases 2/3 — à étendre après cadrage champ par champ) :
--   meublés (calendrier, tarifs saisonniers, inventaire mobilier),
--   compteurs/décompteurs, vente (mandats, échéanciers, compromis),
--   terrains (détails juridiques, lotissement), démarcheurs,
--   reversements propriétaires (modes réel/garanti, créances agence),
--   maintenance, gardiens, visites, pige/matching, scoring/fidélité,
--   charges/TFU, BI, financier avancé, paie agence, juridique/litiges,
--   cycle de vie, transversaux (multilingue, import, corbeille, etc.).
-- =====================================================================