-- ============================================================
-- Doli-RE — Real Estate Property
-- Table : llx_re_property
-- Table centrale d'inventaire des biens immobiliers
--
-- @package     DoliRE
-- @author      Devel-OC
-- @copyright   2025 Devel-OC
-- @license     GPL-3.0-or-later
-- @dolibarr    22 23
-- ============================================================

CREATE TABLE IF NOT EXISTS llx_re_property (
    rowid               INTEGER     NOT NULL AUTO_INCREMENT,

    -- Référence & type
    ref                 VARCHAR(30) NOT NULL,
    fk_property_type    INTEGER     NOT NULL,               -- FK → llx_re_property_type.rowid

    -- Hiérarchie parent/lot (max 2 niveaux)
    fk_parent_property  INTEGER     DEFAULT NULL,           -- NULL = bien racine (niveau 0)
    parent_lot_number   VARCHAR(30) DEFAULT NULL,           -- Numéro de lot dans le parent (ex: "Lot 12", "A-04")
    parent_level        TINYINT     NOT NULL DEFAULT 0,     -- 0 = racine, 1 = lot enfant

    -- Identification
    label               VARCHAR(255) NOT NULL,
    status              VARCHAR(30)  NOT NULL DEFAULT 'available',
    -- Statuts : available | rented | for_sale | sold | under_works | unavailable

    -- Localisation (toujours propre au bien, jamais héritée)
    address             VARCHAR(255) DEFAULT NULL,
    address2            VARCHAR(255) DEFAULT NULL,
    zip                 VARCHAR(10)  DEFAULT NULL,
    town                VARCHAR(100) DEFAULT NULL,
    fk_state            INTEGER      DEFAULT NULL,          -- FK → llx_c_departements.rowid
    fk_country          INTEGER      DEFAULT NULL,          -- FK → llx_c_country.rowid

    -- Géolocalisation
    latitude            DOUBLE(20,8) DEFAULT NULL,
    longitude           DOUBLE(20,8) DEFAULT NULL,
    gps_precision       TINYINT      DEFAULT NULL,          -- précision GPS en mètres (optionnel)
    geocoding_source    VARCHAR(20)  DEFAULT NULL,          -- Origine des coordonnées
    -- Valeurs : manual | nominatim | ign | google
    geocoding_date      DATETIME     DEFAULT NULL,          -- Date du dernier géocodage

    -- Référence cadastrale (commune à tous les types)
    ref_cadastrale      VARCHAR(50)  DEFAULT NULL,

    -- Notes
    note_public         TEXT         DEFAULT NULL,
    note_private        TEXT         DEFAULT NULL,

    -- Multi-entité Dolibarr
    entity              INTEGER      NOT NULL DEFAULT 1,

    -- Traçabilité
    fk_user_creat       INTEGER      NOT NULL,
    fk_user_modif       INTEGER      DEFAULT NULL,
    date_creation       DATETIME     NOT NULL,
    tms                 TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
                                     ON UPDATE CURRENT_TIMESTAMP,
    import_key          VARCHAR(14)  DEFAULT NULL,          -- clé d'import natif Dolibarr

    PRIMARY KEY (rowid)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

-- ----------------------------------------------------------------
-- Index
-- ----------------------------------------------------------------

-- Recherche par référence (unique par entité)
CREATE UNIQUE INDEX uk_re_property_ref
    ON llx_re_property (ref, entity);

-- Accès rapide par type
CREATE INDEX idx_re_property_type
    ON llx_re_property (fk_property_type);

-- Accès rapide aux lots d'un parent
CREATE INDEX idx_re_property_parent
    ON llx_re_property (fk_parent_property);

-- Filtrage par statut (très utilisé en liste)
CREATE INDEX idx_re_property_status
    ON llx_re_property (status);

-- Filtrage par ville
CREATE INDEX idx_re_property_town
    ON llx_re_property (town);

-- Filtrage multi-entité
CREATE INDEX idx_re_property_entity
    ON llx_re_property (entity);

-- ----------------------------------------------------------------
-- Contraintes de clé étrangère
-- ----------------------------------------------------------------

ALTER TABLE llx_re_property
    ADD CONSTRAINT fk_re_property_type
        FOREIGN KEY (fk_property_type)
        REFERENCES llx_re_property_type (rowid)
        ON UPDATE CASCADE
        ON DELETE RESTRICT;

ALTER TABLE llx_re_property
    ADD CONSTRAINT fk_re_property_parent
        FOREIGN KEY (fk_parent_property)
        REFERENCES llx_re_property (rowid)
        ON UPDATE CASCADE
        ON DELETE RESTRICT;          -- Interdit la suppression d'un parent ayant des lots

ALTER TABLE llx_re_property
    ADD CONSTRAINT fk_re_property_country
        FOREIGN KEY (fk_country)
        REFERENCES llx_c_country (rowid)
        ON UPDATE CASCADE
        ON DELETE SET NULL;

ALTER TABLE llx_re_property
    ADD CONSTRAINT fk_re_property_state
        FOREIGN KEY (fk_state)
        REFERENCES llx_c_departements (rowid)
        ON UPDATE CASCADE
        ON DELETE SET NULL;

ALTER TABLE llx_re_property
    ADD CONSTRAINT fk_re_property_user_creat
        FOREIGN KEY (fk_user_creat)
        REFERENCES llx_user (rowid)
        ON UPDATE CASCADE
        ON DELETE RESTRICT;

ALTER TABLE llx_re_property
    ADD CONSTRAINT fk_re_property_user_modif
        FOREIGN KEY (fk_user_modif)
        REFERENCES llx_user (rowid)
        ON UPDATE CASCADE
        ON DELETE SET NULL;

-- ----------------------------------------------------------------
-- Contrainte CHECK — cohérence hiérarchique
-- Un bien de niveau 1 doit avoir un parent, et réciproquement
-- ----------------------------------------------------------------
ALTER TABLE llx_re_property
    ADD CONSTRAINT chk_re_property_hierarchy
        CHECK (
            (parent_level = 0 AND fk_parent_property IS NULL)
            OR
            (parent_level = 1 AND fk_parent_property IS NOT NULL)
        );
