-- ============================================================
-- 27 — Gestion locative de l'appartement (complète)
-- ============================================================

-- 1) Le bien immobilier
CREATE TABLE IF NOT EXISTS appart_biens (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    nom           VARCHAR(120) NOT NULL DEFAULT 'Appartement',
    adresse       VARCHAR(200) NOT NULL DEFAULT '',
    surface_m2    DECIMAL(6,2) NULL,
    nb_chambres   TINYINT NULL,
    peb           VARCHAR(10) NOT NULL DEFAULT '',      -- certificat PEB (A-G)
    reference_cad VARCHAR(60) NOT NULL DEFAULT '',      -- référence cadastrale
    description   TEXT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2) Les baux (contrats de location)
CREATE TABLE IF NOT EXISTS appart_baux (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    bien_id         INT NOT NULL,
    client_id       INT NULL,                            -- locataire (dans la table clients)
    locataire_nom   VARCHAR(160) NOT NULL DEFAULT '',    -- si pas dans clients
    date_debut      DATE NOT NULL,
    date_fin        DATE NULL,
    duree_type      ENUM('9_ans','3_ans','1_an','courte','etudiant','autre') NOT NULL DEFAULT '9_ans',
    loyer_base      DECIMAL(9,2) NOT NULL DEFAULT 0,     -- loyer hors charges
    charges         DECIMAL(9,2) NOT NULL DEFAULT 0,     -- provision charges / frais de gestion
    garantie        DECIMAL(9,2) NOT NULL DEFAULT 0,     -- garantie locative
    jour_echeance   TINYINT NOT NULL DEFAULT 1,          -- jour du mois pour le loyer
    indexation      TINYINT NOT NULL DEFAULT 1,          -- indexation activée
    indice_base     DECIMAL(8,2) NULL,                   -- indice santé de départ
    mois_indice_base TINYINT NULL,                       -- mois de référence de l'indice
    annee_indice_base SMALLINT NULL,
    derniere_indexation SMALLINT NULL,                   -- année de la dernière indexation
    statut          ENUM('brouillon','actif','termine','resilie') NOT NULL DEFAULT 'actif',
    notes           TEXT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_bail_bien FOREIGN KEY (bien_id) REFERENCES appart_biens(id) ON DELETE CASCADE,
    KEY idx_bail_statut (statut)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3) Loyers mensuels (échéancier + suivi paiement + lien facture/CODA)
CREATE TABLE IF NOT EXISTS appart_loyers (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    bail_id       INT NOT NULL,
    periode       CHAR(7) NOT NULL,                      -- 'YYYY-MM'
    loyer         DECIMAL(9,2) NOT NULL DEFAULT 0,
    charges       DECIMAL(9,2) NOT NULL DEFAULT 0,
    total         DECIMAL(9,2) NOT NULL DEFAULT 0,
    date_echeance DATE NOT NULL,
    date_paiement DATE NULL,
    facture_id    INT NULL,                              -- facture générée (table factures)
    coda_mvt_id   INT NULL,                              -- mouvement CODA rapproché
    statut        ENUM('a_venir','du','paye','retard') NOT NULL DEFAULT 'du',
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_loyer (bail_id, periode),
    CONSTRAINT fk_loyer_bail FOREIGN KEY (bail_id) REFERENCES appart_baux(id) ON DELETE CASCADE,
    KEY idx_loyer_statut (statut)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4) États des lieux (entrée/sortie) avec photos
CREATE TABLE IF NOT EXISTS appart_etats_lieux (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    bail_id     INT NOT NULL,
    type        ENUM('entree','sortie') NOT NULL,
    date_edl    DATE NOT NULL,
    redige_par  VARCHAR(120) NOT NULL DEFAULT '',
    observations TEXT NULL,
    statut      ENUM('brouillon','signe') NOT NULL DEFAULT 'brouillon',
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_edl_bail FOREIGN KEY (bail_id) REFERENCES appart_baux(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS appart_photos (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    edl_id      INT NULL,                                -- rattaché à un état des lieux
    visite_id   INT NULL,                                -- ou à une visite
    bien_id     INT NULL,                                -- ou au bien (photos générales)
    fichier     VARCHAR(200) NOT NULL,                   -- nom de fichier stocké
    legende     VARCHAR(200) NOT NULL DEFAULT '',
    piece       VARCHAR(80) NOT NULL DEFAULT '',         -- pièce concernée
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5) Visites de contrôle
CREATE TABLE IF NOT EXISTS appart_visites (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    bail_id     INT NOT NULL,
    date_visite DATE NOT NULL,
    objet       VARCHAR(160) NOT NULL DEFAULT 'Visite de contrôle',
    constat     TEXT NULL,
    anomalie    TINYINT NOT NULL DEFAULT 0,              -- anomalie constatée
    suite       TEXT NULL,                               -- suite à donner
    statut      ENUM('planifiee','faite','annulee') NOT NULL DEFAULT 'planifiee',
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_visite_bail FOREIGN KEY (bail_id) REFERENCES appart_baux(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6) Relevés de compteurs
CREATE TABLE IF NOT EXISTS appart_compteurs (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    bail_id     INT NOT NULL,
    date_releve DATE NOT NULL,
    type        ENUM('eau','electricite','gaz','mazout') NOT NULL,
    valeur      DECIMAL(11,2) NOT NULL DEFAULT 0,
    unite       VARCHAR(10) NOT NULL DEFAULT '',         -- m3, kWh, L
    note        VARCHAR(200) NOT NULL DEFAULT '',
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_compteur_bail FOREIGN KEY (bail_id) REFERENCES appart_baux(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7) Interventions / maintenance
CREATE TABLE IF NOT EXISTS appart_interventions (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    bien_id     INT NOT NULL,
    bail_id     INT NULL,
    date_inter  DATE NOT NULL,
    objet       VARCHAR(160) NOT NULL,
    prestataire VARCHAR(120) NOT NULL DEFAULT '',
    cout        DECIMAL(9,2) NOT NULL DEFAULT 0,
    a_charge    ENUM('proprietaire','locataire') NOT NULL DEFAULT 'proprietaire',
    statut      ENUM('a_faire','en_cours','fait') NOT NULL DEFAULT 'a_faire',
    note        TEXT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_inter_bien FOREIGN KEY (bien_id) REFERENCES appart_biens(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Bien initial (appartement CNC1/CNC2)
INSERT INTO appart_biens (nom, adresse, description)
SELECT 'Appartement (CNC1/CNC2)', 'Rue de la Drève Boninas 2, 5021 Boninne', 'Appartement intégré au bâtiment des box — rez (CNC1) et étage (CNC2).'
WHERE NOT EXISTS (SELECT 1 FROM appart_biens);
