-- LES OFFRES ET LES ABONNEMENTS.
--
-- DEUX TABLES PARCE QUE CE SONT DEUX CHOSES, et les confondre couterait cher :
--
--   une OFFRE est un CATALOGUE      « Essentiel : 1 concierge, 5 prestataires, 30 clients,
--                                   50 logements ». Elle se cree, se renomme, se retire du
--                                   catalogue. Elle appartient a l'editeur, pas au client ;
--
--   un ABONNEMENT est un ENGAGEMENT « la Conciergerie des Cimes, du 01/01/2026 au 31/12/2026,
--                                   sur les conditions que voici ». Il a des dates, et il en
--                                   succede d'autres.
--
-- ================================================================================
-- LES QUOTAS SONT COPIES SUR L'ABONNEMENT, ET C'EST LA DECISION CENTRALE
-- ================================================================================
--
-- L'abonnement porte offre_id ET ses quatre plafonds. Cette redondance est VOULUE.
--
-- Sans elle, changer « Essentiel » de 30 a 20 clients rabaisserait retroactivement tous les
-- abonnements en cours qui s'y referent — y compris ceux d'un client qui en a 25 et qui se
-- retrouverait bloque du jour au lendemain, sans que personne n'ait rien decide le concernant.
-- C'est le meme principe que le prix des prestations, copie sur la ligne d'intervention plutot
-- que lu dans le tarif : ce qui est ENGAGE ne doit pas bouger quand le catalogue bouge.
--
-- offre_id garde donc la trace de la formule CHOISIE — pour l'afficher, pour savoir combien de
-- clients sont sur « Essentiel » — mais ce sont les quatre colonnes de l'abonnement qui font
-- foi. Un abonnement sur mesure a simplement offre_id NULL, sans qu'aucune autre regle change.

CREATE TABLE offres (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    reference  CHAR(26)        NOT NULL,

    -- « Essentiel », « Confort », « Illimite ».
    nom        VARCHAR(80)     NOT NULL,

    -- Ce qui distingue deux formules quand leurs chiffres se ressemblent. Facultatif : une
    -- offre dont le nom suffit n'a pas a inventer une phrase.
    descriptif VARCHAR(255)    NULL,

    -- ============================================================================
    -- LES QUATRE PLAFONDS. NULL VEUT DIRE « SANS LIMITE », ET C'EST INDISPENSABLE.
    -- ============================================================================
    --
    -- Un zero voudrait dire « aucun », ce qui est une offre parfaitement concevable — une
    -- formule sans prestataire, par exemple. Il ne peut donc pas servir aussi a dire
    -- « illimite » : c'est exactement la confusion « absence lue comme zero » qui a coute
    -- plusieurs defauts a ce projet, et ici elle bloquerait toute creation au lieu de
    -- n'en bloquer aucune. Le pire sens possible.
    --
    -- La colonne est donc NULLABLE, et le domaine lit NULL comme l'infini.
    max_concierges   INT UNSIGNED NULL,
    max_prestataires INT UNSIGNED NULL,
    max_clients      INT UNSIGNED NULL,
    max_biens        INT UNSIGNED NULL,

    -- Une offre retiree du catalogue ne se supprime pas : des abonnements en cours s'y
    -- referent, et « sur quelle formule etait-il ? » est la question qu'on pose devant une
    -- facture. Elle cesse simplement d'etre proposee a la souscription.
    actif      TINYINT(1)      NOT NULL DEFAULT 1,

    -- L'ordre d'affichage du catalogue : Essentiel, Confort, Illimite. Un tri sur le nom
    -- les rendrait dans l'ordre alphabetique, qui n'est pas celui de la gamme.
    ordre      SMALLINT UNSIGNED NOT NULL DEFAULT 0,

    cree_le    DATETIME        NOT NULL,
    modifie_le DATETIME        NOT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uq_offres_reference (reference),

    -- Deux offres du meme nom rendraient le choix impossible dans une liste deroulante.
    UNIQUE KEY uq_offres_nom (nom),
    KEY idx_offres_catalogue (actif, ordre, nom)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE abonnements (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    reference       CHAR(26)        NOT NULL,

    conciergerie_id BIGINT UNSIGNED NOT NULL,

    -- La formule choisie, ou NULL pour du sur mesure. Ce n'est PAS elle qui fait foi : voir
    -- l'explication en tete de fichier. Elle sert a l'affichage et au denombrement.
    offre_id        BIGINT UNSIGNED NULL,

    -- ============================================================================
    -- LA PERIODE
    -- ============================================================================
    --
    -- debut NOT NULL : un engagement sans date de prise d'effet n'engage rien.
    --
    -- fin NULLABLE, et c'est « sans terme » — le meme choix que les contrats client, pour la
    -- meme raison : un abonnement reconduit sans echeance existe, et lui inventer une date
    -- lointaine ferait apparaitre une alerte d'expiration un jour, sans motif.
    debut           DATE            NOT NULL,
    fin             DATE            NULL,

    -- Les quatre plafonds ENGAGES. Copies de l'offre a la souscription, ou saisis directement
    -- pour du sur mesure. NULL = sans limite, comme sur l'offre.
    max_concierges   INT UNSIGNED NULL,
    max_prestataires INT UNSIGNED NULL,
    max_clients      INT UNSIGNED NULL,
    max_biens        INT UNSIGNED NULL,

    notes           TEXT            NULL,

    cree_le         DATETIME        NOT NULL,
    modifie_le      DATETIME        NOT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uq_abonnements_reference (reference),

    -- L'index qui sert la question posee a CHAQUE creation de client, de bien ou de compte :
    -- « quel abonnement couvre cette conciergerie aujourd'hui ? ». Trie par debut decroissant
    -- pour que le plus recent sorte le premier.
    KEY idx_abonnements_courant (conciergerie_id, debut DESC),

    CONSTRAINT fk_abonnements_conciergerie FOREIGN KEY (conciergerie_id)
        REFERENCES conciergeries (id) ON DELETE RESTRICT,

    -- RESTRICT : retirer une offre du catalogue ne doit pas emporter les abonnements qui s'y
    -- referent. On la desactive, on ne la supprime pas.
    CONSTRAINT fk_abonnements_offre FOREIGN KEY (offre_id)
        REFERENCES offres (id) ON DELETE RESTRICT,

    -- Une fin anterieure au debut est une faute de saisie qui produirait un abonnement expire
    -- avant d'avoir commence, donc une conciergerie gelee sans qu'on comprenne pourquoi.
    CONSTRAINT ck_abonnements_periode CHECK (fin IS NULL OR fin >= debut)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================================
-- LES TROIS FORMULES DE DEPART
-- ================================================================================
--
-- Trois, comme demande. Elles sont modifiables et supprimables depuis l'administration : ce
-- sont des donnees, pas une structure. On les pose ici pour que l'ecran ne s'ouvre pas sur un
-- catalogue vide — le seul etat ou l'on ne comprend pas a quoi il sert.
--
-- « ILLIMITE » PORTE DES NULL, PAS DE GRANDS NOMBRES. Un plafond a 9999 est un plafond : il
-- finit par etre atteint, et le message d'erreur parlerait alors d'une limite que personne
-- n'a voulue.
INSERT INTO offres (reference, nom, descriptif, max_concierges, max_prestataires,
                    max_clients, max_biens, actif, ordre, cree_le, modifie_le)
VALUES
    (CONCAT(REPEAT('0', 24), '01'), 'Essentiel',
     'Pour demarrer : un concierge, une poignee de logements.',
     1, 3, 20, 30, 1, 10, NOW(), NOW()),

    (CONCAT(REPEAT('0', 24), '02'), 'Confort',
     'La conciergerie etablie, avec une petite equipe.',
     3, 15, 100, 200, 1, 20, NOW(), NOW()),

    (CONCAT(REPEAT('0', 24), '03'), 'Illimite',
     'Aucun plafond, quelle que soit la taille de la structure.',
     NULL, NULL, NULL, NULL, 1, 30, NOW(), NOW());
