-- =====================================================================
-- MODÈLE DE DONNÉES — EXTENSION PHASE 2 (cœur métier)
-- À appliquer APRÈS schema-mvp-gestion-immobiliere.sql
-- Domaines : meublés, compteurs/décompteurs, mandats & reversements,
--            ventes, terrains, démarcheurs, commissions.
-- Toutes les FK pointent vers les tables du socle MVP.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- MEUBLÉS COURTE DURÉE (Module 2)
-- Un meublé est un `biens` avec type_bien = 'meuble'.
-- =====================================================================

-- Détails propres au meublé (extension 1:1 de biens).
CREATE TABLE meubles_details (
    bien_id         INT UNSIGNED  NOT NULL,
    capacite        SMALLINT      NULL,                -- nb de personnes
    equipements     TEXT          NULL,                -- liste libre (clim, wifi, TV...)
    caution_defaut  DECIMAL(15,2) NOT NULL DEFAULT 0,
    PRIMARY KEY (bien_id),
    CONSTRAINT fk_meubledet_bien FOREIGN KEY (bien_id) REFERENCES biens(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tarifs configurables : jour / semaine / mois, avec périodes saisonnières.
-- Un tarif "de base" a saison_libelle NULL et pas de dates ; un tarif
-- saisonnier porte un libellé et une plage de dates.
CREATE TABLE meubles_tarifs (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bien_id         INT UNSIGNED  NOT NULL,
    unite           ENUM('jour','semaine','mois') NOT NULL,
    montant         DECIMAL(15,2) NOT NULL,
    saison_libelle  VARCHAR(80)   NULL,                -- NULL = tarif de base
    date_debut      DATE          NULL,
    date_fin        DATE          NULL,
    PRIMARY KEY (id),
    KEY idx_meubletarif_bien (bien_id),
    CONSTRAINT fk_meubletarif_bien FOREIGN KEY (bien_id) REFERENCES biens(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Inventaire du mobilier (vérifié à l'entrée/sortie).
CREATE TABLE meubles_inventaire (
    id          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bien_id     INT UNSIGNED  NOT NULL,
    libelle     VARCHAR(150)  NOT NULL,
    quantite    SMALLINT      NOT NULL DEFAULT 1,
    etat        VARCHAR(80)   NULL,
    photo       VARCHAR(255)  NULL,
    PRIMARY KEY (id),
    KEY idx_meubleinv_bien (bien_id),
    CONSTRAINT fk_meubleinv_bien FOREIGN KEY (bien_id) REFERENCES biens(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Réservations / séjours. Anti-double-réservation contrôlé applicativement
-- (chevauchement de dates pour un même bien sur statut réservé/en_cours).
-- Client : locataire_id réutilisable OU client ponctuel (champs libres).
CREATE TABLE reservations_meuble (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bien_id         INT UNSIGNED  NOT NULL,
    locataire_id    INT UNSIGNED  NULL,
    client_nom      VARCHAR(150)  NULL,
    client_tel      VARCHAR(40)   NULL,
    client_piece    VARCHAR(60)   NULL,
    date_arrivee    DATE          NOT NULL,
    date_depart     DATE          NOT NULL,
    unite_tarif     ENUM('jour','semaine','mois') NOT NULL,
    tarif_applique  DECIMAL(15,2) NOT NULL,
    montant_total   DECIMAL(15,2) NOT NULL,
    acompte         DECIMAL(15,2) NOT NULL DEFAULT 0,
    caution         DECIMAL(15,2) NOT NULL DEFAULT 0,
    statut          ENUM('reserve','en_cours','termine','annule') NOT NULL DEFAULT 'reserve',
    cree_le         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_resa_bien (bien_id),
    KEY idx_resa_dates (date_arrivee, date_depart),
    KEY idx_resa_statut (statut),
    CONSTRAINT fk_resa_bien FOREIGN KEY (bien_id) REFERENCES biens(id),
    CONSTRAINT fk_resa_loc FOREIGN KEY (locataire_id) REFERENCES locataires(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Frais additionnels paramétrables (ménage, eau, électricité...) par séjour.
CREATE TABLE reservation_frais (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    reservation_id  INT UNSIGNED  NOT NULL,
    libelle         VARCHAR(120)  NOT NULL,
    montant         DECIMAL(15,2) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_resafrais_resa (reservation_id),
    CONSTRAINT fk_resafrais_resa FOREIGN KEY (reservation_id) REFERENCES reservations_meuble(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- COMPTEURS ÉLECTRICITÉ (Module 5)
-- 2 configs : compteur individuel officiel OU décompteur (compteur général).
-- L'eau est exclue. Tarif kWh libre paramétrable.
-- =====================================================================

CREATE TABLE compteurs (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bien_id         INT UNSIGNED  NOT NULL,            -- le lot rattaché
    bail_id         INT UNSIGNED  NULL,                -- locataire courant (décompteur)
    type_config     ENUM('individuel_officiel','decompteur') NOT NULL,
    numero          VARCHAR(60)   NULL,
    tarif_kwh       DECIMAL(10,2) NULL,                -- tarif libre (cas décompteur)
    actif           TINYINT(1)    NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    KEY idx_compteur_bien (bien_id),
    CONSTRAINT fk_compteur_bien FOREIGN KEY (bien_id) REFERENCES biens(id) ON DELETE CASCADE,
    CONSTRAINT fk_compteur_bail FOREIGN KEY (bail_id) REFERENCES baux(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE releves_compteur (
    id                  INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    compteur_id         INT UNSIGNED  NOT NULL,
    periode             CHAR(7)       NOT NULL,        -- 'AAAA-MM'
    index_precedent     DECIMAL(12,2) NOT NULL,
    index_actuel        DECIMAL(12,2) NOT NULL,
    consommation        DECIMAL(12,2) NOT NULL,        -- = actuel - precedent (kWh)
    montant_facture     DECIMAL(15,2) NOT NULL,        -- = consommation * tarif_kwh
    statut_facturation  ENUM('a_facturer','facture','paye') NOT NULL DEFAULT 'a_facturer',
    PRIMARY KEY (id),
    UNIQUE KEY uq_releve (compteur_id, periode),
    CONSTRAINT fk_releve_compteur FOREIGN KEY (compteur_id) REFERENCES compteurs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- TERRAINS (Module 7)
-- Un terrain est un `biens` avec type_bien = 'terrain'.
-- Lotissement parent / parcelles enfants via biens.lot_parent_id.
-- =====================================================================

-- Types de statut juridique : standard béninois + configurable.
CREATE TABLE types_statut_juridique (
    id          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    agence_id   INT UNSIGNED  NULL,
    libelle     VARCHAR(120)  NOT NULL,                -- TF, convention, attestation coutumière...
    actif       TINYINT(1)    NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    CONSTRAINT fk_statutjur_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Détails terrain (extension 1:1 de biens).
CREATE TABLE terrains_details (
    bien_id             INT UNSIGNED  NOT NULL,
    statut_juridique_id INT UNSIGNED  NULL,
    niveau_securisation ENUM('securise','en_cours','litigieux') NOT NULL DEFAULT 'en_cours',
    nb_parcelles        DECIMAL(8,2)  NULL,            -- superficie exprimée en parcelles
    valeur_parcelle_m2  DECIMAL(10,2) NULL,            -- paramétrable, variable selon région
    prix_parcelle       DECIMAL(15,2) NULL,            -- prix à la parcelle
    prix_global         DECIMAL(15,2) NULL,            -- prix d'ensemble
    demarches_en_cours  VARCHAR(255)  NULL,            -- bornage, immatriculation, conversion TF...
    PRIMARY KEY (bien_id),
    KEY idx_terrain_statut (statut_juridique_id),
    CONSTRAINT fk_terrain_bien FOREIGN KEY (bien_id) REFERENCES biens(id) ON DELETE CASCADE,
    CONSTRAINT fk_terrain_statutjur FOREIGN KEY (statut_juridique_id) REFERENCES types_statut_juridique(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- DÉMARCHEURS (Module 8)
-- Base réutilisable. Commission ONE-SHOT (fixe ou %).
-- =====================================================================

CREATE TABLE demarcheurs (
    id          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    agence_id   INT UNSIGNED  NULL,
    nom_complet VARCHAR(150)  NOT NULL,
    telephone   VARCHAR(40)   NULL,
    whatsapp    VARCHAR(40)   NULL,
    zone        VARCHAR(120)  NULL,
    piece_numero VARCHAR(60)  NULL,
    cree_le     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_demarcheur_agence (agence_id),
    CONSTRAINT fk_demarcheur_agence FOREIGN KEY (agence_id) REFERENCES agences(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- PROPRIÉTAIRES — MANDATS, REVERSEMENTS, CRÉANCES (Module 9)
-- =====================================================================

-- Mandat de gestion OU de vente. Porte la commission et le mode de reversement.
CREATE TABLE mandats (
    id                      INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    proprietaire_id         INT UNSIGNED  NOT NULL,
    bien_id                 INT UNSIGNED  NOT NULL,
    type                    ENUM('gestion','vente') NOT NULL,
    exclusif                TINYINT(1)    NOT NULL DEFAULT 0,
    date_debut              DATE          NOT NULL,
    date_fin                DATE          NULL,
    commission_type         ENUM('pourcentage','fixe') NOT NULL,
    commission_valeur       DECIMAL(15,2) NOT NULL,    -- % ou montant FCFA
    -- Spécifique gestion locative :
    mode_reversement        ENUM('reel','garanti') NULL,    -- réel = au perçu ; garanti = avance agence
    frequence_reversement   ENUM('mensuel','trimestriel','semestriel','annuel') NULL,
    seuil_accord_depense    DECIMAL(15,2) NULL,        -- au-delà : accord propriétaire requis
    statut                  ENUM('actif','expire','resilie') NOT NULL DEFAULT 'actif',
    PRIMARY KEY (id),
    KEY idx_mandat_proprio (proprietaire_id),
    KEY idx_mandat_bien (bien_id),
    CONSTRAINT fk_mandat_proprio FOREIGN KEY (proprietaire_id) REFERENCES proprietaires(id),
    CONSTRAINT fk_mandat_bien FOREIGN KEY (bien_id) REFERENCES biens(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Reversements périodiques au propriétaire (relevé de gérance).
CREATE TABLE reversements (
    id                  INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    mandat_id           INT UNSIGNED  NOT NULL,
    periode             CHAR(7)       NOT NULL,        -- 'AAAA-MM' ou début de période
    loyers_percus       DECIMAL(15,2) NOT NULL DEFAULT 0,
    commission_agence   DECIMAL(15,2) NOT NULL DEFAULT 0,
    depenses_deduites   DECIMAL(15,2) NOT NULL DEFAULT 0,
    net_reverse         DECIMAL(15,2) NOT NULL DEFAULT 0,
    statut              ENUM('a_reverser','reverse') NOT NULL DEFAULT 'a_reverser',
    date_reversement    DATE          NULL,
    PRIMARY KEY (id),
    KEY idx_reversement_mandat (mandat_id),
    CONSTRAINT fk_reversement_mandat FOREIGN KEY (mandat_id) REFERENCES mandats(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Avances de frais engagées par l'agence pour le compte du propriétaire
-- (réparations, charges). Au-delà du seuil : accord requis.
CREATE TABLE avances_frais (
    id                  INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    mandat_id           INT UNSIGNED  NOT NULL,
    bien_id             INT UNSIGNED  NULL,
    montant             DECIMAL(15,2) NOT NULL,
    motif               VARCHAR(255)  NULL,
    date_engagement     DATE          NOT NULL,
    statut_accord       ENUM('non_requis','en_attente','accorde','refuse') NOT NULL DEFAULT 'non_requis',
    reversement_id      INT UNSIGNED  NULL,            -- reversement sur lequel c'est déduit
    PRIMARY KEY (id),
    KEY idx_avance_mandat (mandat_id),
    CONSTRAINT fk_avance_mandat FOREIGN KEY (mandat_id) REFERENCES mandats(id) ON DELETE CASCADE,
    CONSTRAINT fk_avance_bien FOREIGN KEY (bien_id) REFERENCES biens(id) ON DELETE SET NULL,
    CONSTRAINT fk_avance_reversement FOREIGN KEY (reversement_id) REFERENCES reversements(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Mode GARANTI : créance de l'agence quand elle a reversé un loyer non encaissé.
-- 3 voies de dénouement figées : garantie locataire / propriétaire / perte agence.
CREATE TABLE creances_agence (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    mandat_id       INT UNSIGNED  NOT NULL,
    bail_id         INT UNSIGNED  NOT NULL,
    periode         CHAR(7)       NOT NULL,
    montant_avance  DECIMAL(15,2) NOT NULL,
    montant_recupere DECIMAL(15,2) NOT NULL DEFAULT 0,
    voie_denouement ENUM('en_cours','garantie_locataire','proprietaire','perte_agence') NOT NULL DEFAULT 'en_cours',
    cree_le         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_creance_mandat (mandat_id),
    KEY idx_creance_bail (bail_id),
    CONSTRAINT fk_creance_mandat FOREIGN KEY (mandat_id) REFERENCES mandats(id) ON DELETE CASCADE,
    CONSTRAINT fk_creance_bail FOREIGN KEY (bail_id) REFERENCES baux(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- VENTES (Module 6)
-- Le mandat de vente est porté par `mandats` (type = 'vente').
-- =====================================================================

CREATE TABLE dossiers_vente (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    bien_id         INT UNSIGNED  NOT NULL,
    acheteur_user_id INT UNSIGNED NULL,                -- compte portail acheteur (optionnel)
    acheteur_nom    VARCHAR(150)  NOT NULL,
    acheteur_tel    VARCHAR(40)   NULL,
    prix_vente      DECIMAL(15,2) NOT NULL,
    statut          ENUM('en_negociation','sous_compromis','solde','annule') NOT NULL DEFAULT 'en_negociation',
    date_compromis  DATE          NULL,
    date_acte       DATE          NULL,
    cree_le         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_dvente_bien (bien_id),
    CONSTRAINT fk_dvente_bien FOREIGN KEY (bien_id) REFERENCES biens(id),
    CONSTRAINT fk_dvente_acheteur FOREIGN KEY (acheteur_user_id) REFERENCES utilisateurs(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Échéancier de paiement échelonné (terrains et maisons).
CREATE TABLE echeances_vente (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    dossier_vente_id INT UNSIGNED NOT NULL,
    numero_tranche  SMALLINT      NOT NULL,
    montant         DECIMAL(15,2) NOT NULL,
    date_echeance   DATE          NOT NULL,
    montant_paye    DECIMAL(15,2) NOT NULL DEFAULT 0,
    statut          ENUM('a_payer','partiel','paye') NOT NULL DEFAULT 'a_payer',
    PRIMARY KEY (id),
    KEY idx_echvente_dossier (dossier_vente_id),
    CONSTRAINT fk_echvente_dossier FOREIGN KEY (dossier_vente_id) REFERENCES dossiers_vente(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Paiements de vente (reçu par tranche ; acte/quittance au solde).
CREATE TABLE paiements_vente (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    dossier_vente_id INT UNSIGNED NOT NULL,
    echeance_id     INT UNSIGNED  NULL,
    montant         DECIMAL(15,2) NOT NULL,
    mode_paiement   ENUM('especes','momo','moov','virement') NOT NULL,
    reference_momo  VARCHAR(80)   NULL,
    date_paiement   DATE          NOT NULL,
    document_id     INT UNSIGNED  NULL,                -- reçu généré (table documents)
    cree_le         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pvente_dossier (dossier_vente_id),
    CONSTRAINT fk_pvente_dossier FOREIGN KEY (dossier_vente_id) REFERENCES dossiers_vente(id) ON DELETE CASCADE,
    CONSTRAINT fk_pvente_echeance FOREIGN KEY (echeance_id) REFERENCES echeances_vente(id) ON DELETE SET NULL,
    CONSTRAINT fk_pvente_doc FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL,
    CONSTRAINT chk_pvente_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;

-- =====================================================================
-- COMMISSIONS (transversal location / vente — agence & démarcheur)
-- Démarcheur : ONE-SHOT (fixe ou %). Agence : selon mandat.
-- Rattachement polymorphe à la transaction (bail OU dossier_vente).
-- =====================================================================

CREATE TABLE commissions (
    id                  INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    beneficiaire        ENUM('agence','demarcheur') NOT NULL,
    demarcheur_id       INT UNSIGNED  NULL,            -- si bénéficiaire = démarcheur
    transaction_type    ENUM('location','vente') NOT NULL,
    bail_id             INT UNSIGNED  NULL,
    dossier_vente_id    INT UNSIGNED  NULL,
    calcul_type         ENUM('pourcentage','fixe') NOT NULL,
    calcul_valeur       DECIMAL(15,2) NOT NULL,
    montant             DECIMAL(15,2) NOT NULL,
    statut_reglement    ENUM('du','paye','echelonne') NOT NULL DEFAULT 'du',
    document_id         INT UNSIGNED  NULL,            -- reçu de commission
    cree_le             DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_commission_demarcheur (demarcheur_id),
    KEY idx_commission_bail (bail_id),
    KEY idx_commission_vente (dossier_vente_id),
    CONSTRAINT fk_commission_demarcheur FOREIGN KEY (demarcheur_id) REFERENCES demarcheurs(id) ON DELETE SET NULL,
    CONSTRAINT fk_commission_bail FOREIGN KEY (bail_id) REFERENCES baux(id) ON DELETE SET NULL,
    CONSTRAINT fk_commission_vente FOREIGN KEY (dossier_vente_id) REFERENCES dossiers_vente(id) ON DELETE SET NULL,
    CONSTRAINT fk_commission_doc FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- FIN EXTENSION PHASE 2
-- ---------------------------------------------------------------------
-- COUVERT : meublés (détails, tarifs saisonniers, inventaire, réservations,
--   frais), compteurs/décompteurs + relevés, terrains (statut juridique
--   configurable, parcelles, lotissement via lot_parent_id), démarcheurs,
--   mandats (gestion/vente, mode réel/garanti, seuil), reversements,
--   avances de frais, créances agence (3 voies de dénouement), ventes
--   (dossiers, échéancier, paiements par tranche), commissions
--   (agence + démarcheur, location + vente).
--
-- RESTE (lot suivant — domaines opérationnels & support) :
--   maintenance & prestataires, gardiens/personnel + RH, visites & agenda,
--   pige & matching & parrainage, notifications (file d'envoi, modèles),
--   charges & TFU, scoring & fidélité & liste noire (détail calcul),
--   BI, financier avancé (caisses multiples, petite caisse, dettes/créances),
--   paie agence, juridique (modèles, litiges, mises en demeure),
--   cycle de vie / états successifs, transversaux (corbeille, recherche,
--   calendrier global, multilingue, import).
-- =====================================================================