-- LE TEMPS DE ROUTE ENTRE DEUX LOGEMENTS, pour reperer une journee impossible.
--
-- CE QUE CETTE MIGRATION REND POSSIBLE. Le planning d'equipe montre qui fait quoi et quand,
-- mais rien n'y dit qu'une suite d'interventions est irrealisable. Deux prestations espacees
-- de quarante minutes, separees de soixante-dix minutes de route, se lisent comme une
-- journee normale — et c'est le prestataire qui le decouvre sur place.
--
-- POURQUOI DES COORDONNEES, alors que le bien a une adresse. Parce qu'un service
-- d'itineraire ne prend pas du texte : il prend deux points. Le geocodage traduit l'un en
-- l'autre, et il n'a a etre fait QU'UNE FOIS par adresse — d'ou son resultat stocke ici
-- plutot que recalcule.
--
-- POURQUOI UN CACHE DE TRAJETS. C'est ce qui rend la fonction viable sur un hebergement
-- mutualise avec une cle d'API gratuite : le temps de route entre deux logements NE CHANGE
-- PAS. Vingt biens font cent quatre-vingt-dix paires, calculees une fois pour toutes. Sans
-- cache il faudrait interroger le service a chaque affichage du planning, soit des dizaines
-- d'appels par page — impossible dans les quotas, et lent.
--
-- CE QUE CE CACHE N'EST PAS. Il ne connait ni le trafic, ni la saison, ni les cols fermes.
-- En montagne, un itineraire calcule en aout peut passer par une route impraticable en
-- fevrier. C'est une ESTIMATION, et l'ecran doit le dire — voir Trajet::AVERTISSEMENT.

ALTER TABLE biens
    -- DECIMAL et non DOUBLE : une latitude a besoin de six decimales (environ dix
    -- centimetres), pas de dix-sept chiffres significatifs. Et DECIMAL se compare sans
    -- surprise, ce qui compte pour une cle de cache.
    ADD COLUMN latitude   DECIMAL(9, 6)  NULL AFTER ville,
    ADD COLUMN longitude  DECIMAL(9, 6)  NULL AFTER latitude,

    -- QUAND le geocodage a reussi. Sert a savoir si l'adresse a change depuis : on ne
    -- redemande pas un point qu'on a deja, mais on le redemande si l'adresse bouge.
    ADD COLUMN geocode_le DATETIME       NULL AFTER longitude,

    -- POURQUOI IL A ECHOUE, en clair. Une adresse mal orthographiee ou un lieu-dit absent
    -- des bases ne se geocode pas, et cela DOIT se voir : sans ce champ, le bien n'aurait
    -- simplement aucun trajet et personne ne saurait pourquoi. C'est ce que l'ecran
    -- d'administration affiche pour qu'on pose le point a la main.
    ADD COLUMN geocode_erreur VARCHAR(190) NULL AFTER geocode_le;

-- Les biens deja geocodes se trouvent vite : l'ecran d'administration cherche l'inverse,
-- ceux qui ne le sont pas.
CREATE INDEX idx_biens_geocode ON biens (geocode_le);

-- LE CACHE DES TRAJETS.
CREATE TABLE trajets (
    id       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

    /*
     | LA PAIRE EST ORDONNEE PAR IDENTIFIANT CROISSANT, toujours : depart_id < arrivee_id.
     |
     | Le trajet A vers B et le trajet B vers A ne sont pas rigoureusement identiques — sens
     | unique, denivele — mais l'ecart est negligeable devant l'usage qu'on en fait, et
     | stocker les deux sens doublerait le cache et les appels. On range donc une seule ligne
     | par paire, et l'appelant normalise avant de lire.
     |
     | La contrainte UNIQUE le garantit : sans elle, deux calculs concurrents inseriraient
     | deux lignes pour la meme paire et l'on ne saurait plus laquelle fait foi.
     */
    depart_id  BIGINT UNSIGNED NOT NULL,
    arrivee_id BIGINT UNSIGNED NOT NULL,

    minutes    SMALLINT UNSIGNED NOT NULL,
    metres     INT UNSIGNED      NOT NULL,

    -- Le service qui a repondu, et quand. Changer de fournisseur doit pouvoir invalider ce
    -- qui vient de l'ancien sans vider tout le cache.
    source     VARCHAR(40)  NOT NULL,
    calcule_le DATETIME     NOT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uq_trajets_paire (depart_id, arrivee_id),
    KEY idx_trajets_arrivee (arrivee_id),

    /*
     | ON DELETE CASCADE : un bien supprime emporte ses trajets. Ils ne veulent plus rien
     | dire, et les garder ferait grossir un cache dont les cles ne designent plus rien.
     */
    CONSTRAINT fk_trajets_depart
        FOREIGN KEY (depart_id) REFERENCES biens (id) ON DELETE CASCADE,
    CONSTRAINT fk_trajets_arrivee
        FOREIGN KEY (arrivee_id) REFERENCES biens (id) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
