/*

BADASS : Base Archéologique de Données Attributaires et SpatialeS
Inrap, 2026

Version : 2.6
- Issue #363 : ajout des coordonnées d'emprise de chaque coupe dans la vue vue_recap_coupe_axe
- Issue #359 : modification de la vue vue_absents_dans_mere pour répertorier en plus des numéros d'entités absentes dans les tables mères pour les relations de n à n, les entités bsentes dans les tables mères pour les relations de 1 à n
- Issue #358 : reprise de la vue de détection des cycles stratigraphiques vue_cycles_stratigraphie pour l'alléger (elle provoquait un plantage de la bdd)
- Modification de la vue vue_liste_cycles_strati pour aider à corriger le cycle en affichant une liste orientée des US dans le cycle (limité à 500)
- Ajout du champ "read_only" INTEGER dans la table t_equipe pour gérer le mode lecture seule dans Bad'Mobil
- Sur le projet : le fond est désormais blanc (issue #354), il n'est plus possible de supprimer une US depuis la table des faits (issue #356), Il n'est plus possible de lier ou détacher des entités dans les sous-formulaires où on édite directement la table intermédiaire (issue #356), l'association avec les sujets est désormais possible depuis la table t_axe (issue #360), les champs calculés par triggers ne sont plus éditables dans la table j_us_log (issue #361)  
- Test dans l'interface du projet : contrainte de saisie pour que les associations avec des entités mères soient signalées comme manquantes

Version : 2.5
 - Les champs d'identificaiton dans les Six Couches sont désormais entiers
 - Ajout d'un champ "statut" dans la table t_us
 - Ajout d'une clé étrangère "id_prodphotogram" dans la table j_rel_minute pour pouvoir faire le lien entre les minutes de chantier et les produits des modèles 3D de photogrammétrie
 - Issue #194 : ajout de triggers dans la table t_log pour le calcul de zmin_log à la mise à jour du champ "alti"
 - Issue #266 : reprise de l'ensemble des triggers sur la table j_us_log
 - Issue #231 : ajout des vues vue_cycles_stratigraphie et vue_liste_cycles_strati qui liste les cycles stratigraphiques à partir de la table j_rel_us
 - Issue #243 : ajout d'un champ "mob_note" dans t_us pour ajouter les valeurs de matières de mobilier présent dans l'US sous forme de champ multivalué (thesaurus : "table_thes" like 't_mobilier' et "field_thes" like 'matiere')
 - Issue #289 : ajout des clés étrangères "numens", "numaxe", "numinute", "numlog" et "numphoto" dans la vue inventaire_us
 - Issue #294 : modification de la vue vue_verif_enregistrement pour éviter le conflit avec les paramètres de formulaire du projet sur les champs booléens qui fixaient des valeurs par défaut
 - Issue #295 : Remplacement du st_equals par WHEN OLD.geometry IS NOT NEW.geometry dans le trigger de mise à jour sur les axes (entre axe et t_axe)
 - Issue #304 : ajout de triggers de mise à jour de "numfait" entre la table t_us et les tables coupe_poly et coupe_line
 - Issue #309 : ajout d'une vue de récapitulation des altitudes par fait et par phase et global selon la table coupe_poly
 - Issue #310 : correction des vue vue_fait_periode et vue_fait_phase
 - Issue #311 : ajout d'une clé étrangère "numaxe" dans la table j_rel_photo
 - Issue #325 : ajout d'une vue pour localiser le long d'un axe en plan, l'emprise d'une US selon le dessin en coupe
 - Issue #335 : modification de la structure de la table pts_topo en concordance avec le modèle validé en réunion Référent SIG
 - Issue #337 : ajout de "id_seq" dans la table t_seq
 - Issue #342 : ajout d'un champ pour stocker le nom d'ensemble dans la table t_ens ("nomens")
 - Issue #343 : reprise de la vue vue_log_stratigraphique pour éviter les redondances
 - Issue #350 : mise en commentaire de la relation de clé étrangère sur la table t_prodphotogram
 - Ajout d'une table de configuration système saisie par le plugin pour récupérer des variables d'environnement
 - Ajout d'hyperliens dans la table a_propos pour accéder au MLD et au pense-bêtes de fonctionnement des triggers 

-- NB (architecture plugin) : dans le plugin QGIS, la table config et la vue a_propos
-- sont gérées par le fichier update_a_propos.sql, exécuté séparément à la création
-- et lors de la mise à jour des bases existantes. Ce fichier SQL constitue la référence
-- d'archivage complète incluant l'ensemble de la structure.

-- Table de configuration (remplie par Python au chargement du plugin)
CREATE TABLE IF NOT EXISTS config (
    cle   TEXT PRIMARY KEY,
    valeur TEXT
);
INSERT OR IGNORE INTO config (cle, valeur) VALUES ('plugin_uri', '');

--Vue a_propos, métadonnées de la base de données
DROP VIEW IF EXISTS a_propos;
CREATE VIEW a_propos AS
    SELECT 
        row_number() OVER() AS id_apropos,
        'version 2.5.2' AS version_badass, --la version utilisée de Badass
        'https://plugins.qgis.org/plugins/badass/version/2.5.2/' AS log_version, -- le lien vers le log de version sur le repository de plugin de QGIS
        'version 1.7.x' AS version_badmobil,  --la version compatible de Bad'Mobil
        'avril 2026' AS date_version, --le mois et l'année de la version
        'Collectif Ramen, Inrap' AS creator, --les auteurs
        'Licence Cecill-B' AS licence, -- la license associée
        'https://gitlab.com/projet-r-d-bddsarcheo/tutos/-/wikis/home' AS support,
        (SELECT valeur || 'file/mld_badass.html'        FROM config WHERE cle = 'plugin_uri') AS mld,
        (SELECT valeur || 'file/pense_bete_saisie.html' FROM config WHERE cle = 'plugin_uri') AS fonct_triggers
;

Version : 2.4.0
 - ajout du champ "profil" dans la table t_us ; issue #234
 - ajout du champ "nomfichier" dans la table t_minute ; issue #264
 - ajout de la table t_savecommand ; issue #185
 - reprise des triggers de calcul automatique de surface pour la table ouverture des 6 couches
 - correction issue 261 concernant la contrainte d'unicité de ordre sur t_phase
 - correction vue erreur_saisie_strati (issue 232)
 - correction des exports caviar (issue 255)
 - correction de la requête vue_recap_phae_per
 - ajout de champs dans la table t_photogram et j_rel_photogram (issue 226)
 - correction des vues vue_fait_phase et vue_fait_periode qui ne généraient pas de géométrie
 - les triggers répliquant les valeurs synchrones / egales / equivalentes entre les US sont désactivés pour éviter les redondances à l'export : trgai_j_rel_us, trgau_j_rel_us, trgad_j_rel_us 
 - modification de la vue d'inversion des relations stratigraphiques à partir de la table j_rel_us
 - ajout d'une vue a_propos, qui permet de renseigner la version de Badass, la version de Bad'Mobil à utiliser et la licence associée
 - ajout d'une condition supplémentaire dans les triggers de mise à jour de géométries entre les 6 couches et les table EAT (WHEN NOT ST_Equals(NEW.geometry, OLD.geometry))
 - corrections dans le projet : 
    - #215 (reprise du formulaire de t_photogram), 
    - #216 (reprise du formulaire de t_prodphotogram), 
    - #218 (correction du formulaire de la table t_ens), 
    - #219 (correction du formulaire de la table j_rel_fait), 
    - #237 (correction du formulaire de t_fait sur l'association avec les ensembles), 
    - #262 (suppression des otpions d'édition sur les tables mères en relation de n à n), 
    - #282 (réparation de la jointure entre t_axe et coupe_axe), 
    - #283 (mise en place du formulaire de la table traitement_photo)

Version : 2.3
 - ajout du champ "photok" dans la table t_log, pour le suivi de l'enregistrement de terrain
 - modification de la table photogrammetrie et ajout du champ "topok" pour le suivi de levé topographique et du champ "url_model" pour ajouter une URL pointant vers le modèle 3D
 - ajout dans la table j_rel_photogram des clés étrangères des table t_fait (numfait) et t_us (numus)
 - ajout dans la table t_equipe des champs "user" et "password" pour gérer l'autentification à Bad'mobil
 - ajout des triggers de calculs sur les logs stratigraphiques ; issue #194
 - ajout de la table traitement_photo et des triggers afférents : issue #207
 - ajout d'une vue vue_absents_dans_mere qui permet de lister les entités saisies dans les tables de relations de n à n, mais pas créée dans les tables mères


-------------------------------------------------------------------------------------------------------------------------
Auteur.e.s : Collectif Ramen (ordre alphabétique : Caroline Font, Thomas Guillemard, Florent Mercey, Christelle Seng) 
			Pour les tables liées à la photogrammétrie : Camille Mangier, Rachid El-Hajaoui
			Avec la collaboration de Mathias Cunault, Clément Féliu, Bertrand Houdusse
-------------------------------------------------------------------------------------------------------------------------            

*/

PRAGMA encoding='UTF-8';

-- LES 6 COUCHES

-- EMPRISE : unité technique ; emprise de l’opération
DROP TABLE IF EXISTS emprise; -- Supprime la table (et ses données) si elle existe déjà. 
CREATE TABLE emprise(
   "id_emprise" INTEGER PRIMARY KEY,
   "numope" TEXT,
   "nomope" TEXT,
   "typope" TEXT,
   "typemp" TEXT,
   "numoa" TEXT,
   "numprescr" TEXT,
   "ro" TEXT,
   "annee" INTEGER,
   "surface" FLOAT,
   "gid_caviar" INTEGER --clé primaire dans Caviar
);
SELECT 
AddGeometryColumn ('emprise','geometry',2154,'MULTIPOLYGON','XY',0); -- ajoute la colonne geométrie. Attention, fonction de Spatialite uniquement !!! 

-- OUVERTURE : unité technique : tout creusement réalisé à des fins d’observation (sondage, tranchée, décapage...)
DROP TABLE IF EXISTS ouverture;
CREATE TABLE ouverture(
   "id_ouverture" INTEGER PRIMARY KEY,
   "numouvert" INTEGER,
   "typouvert" TEXT,
   "surface" FLOAT,
   "date_leve" INTEGER
);
SELECT 
AddGeometryColumn ('ouverture','geometry',2154,'MULTIPOLYGON','XY',0);

-- POLY : unité d’observation archéologique représentée sous forme de polygone (us, fait, ens...)
DROP TABLE IF EXISTS poly;
CREATE TABLE poly(
   "id_poly" INTEGER PRIMARY KEY,
   "numpoly" INTEGER,
   "typoly" TEXT,
   "interpret" TEXT,
   "datedebut" INTEGER,
   "datefin" INTEGER,
   "date_leve" INTEGER
);
SELECT 
AddGeometryColumn ('poly','geometry',2154,'MULTIPOLYGON','XY',0);

-- POINT : unité d’observation archéologique représentée sous forme de point (isolat...)
DROP TABLE IF EXISTS point;
CREATE TABLE point(
   "id_point" INTEGER PRIMARY KEY,
   "numpoint" INTEGER UNIQUE,
   "typoint" TEXT,
   "interpret" TEXT,
   "datedebut" INTEGER,
   "datefin" INTEGER,
   "z_point" FLOAT,
   "date_leve" INTEGER
);
SELECT 
AddGeometryColumn ('point','geometry',2154,'POINT','XY',0);

-- AXE : unité technique matérialisant l’axe de coupe, sous forme de ligne
DROP TABLE IF EXISTS axe;
CREATE TABLE axe(
   "id_axe" INTEGER PRIMARY KEY,
   "numaxe" INTEGER,
   "typaxe" TEXT,
   "z_axe" FLOAT,
   "long_axe" FLOAT,
   "date_leve" INTEGER
);
SELECT 
AddGeometryColumn ('axe','geometry',2154,'LINESTRING','XY',0);

-- LOG : unité technique ponctuelle localisant l’emplacement des prélèvements, des logs géomorphologiques (lieux d’observation ponctuels)
DROP TABLE IF EXISTS log;
CREATE TABLE log(
   "id_log" INTEGER PRIMARY KEY,
   "numlog" INTEGER,
   "typlog" TEXT,
   "alti" FLOAT,
   "typalti" TEXT,
   "date_leve" INTEGER
);
SELECT 
AddGeometryColumn ('log','geometry',2154,'POINT','XY',0);

-- PTS_TOPO : unité technique ponctuelle localisant l’emplacement de l'ensemble des points levés par le topographe
DROP TABLE IF EXISTS pts_topo;
CREATE TABLE pts_topo(
   "id_ptopo" INTEGER PRIMARY KEY,
   "mat" TEXT, -- matricule
   "x" FLOAT,
   "y" FLOAT,
   "z" FLOAT,
   "type_pt" TEXT, -- fait/US, tranchée/sondage/décapage, isolat, log, coupe/plan, emprise topographiée/réelle, station/pointTopo, repère/alti
   "numero" INTEGER, -- numéro de l'entité à laquelle le point topo est associé (numfait, numus, numtr, numsd, numlog...)
   "appareil" TEXT, -- type d’appareil utilisé (tachéo, GPS)
   "precis_xy" FLOAT, -- précision du point topo en XY
   "precis_z" FLOAT, -- précision du point topo en Z (2 cm si tachéo)
   "scr" INTEGER DEFAULT 2154, -- système de référence (2154, 4326...)
   "geoide" TEXT DEFAULT 'IGN69', -- modèle de géoïde utilisé pour les altitudes (IGN69, IGN78, EGM96...)
   "date" INTEGER, -- à saisir sous la forme yymmdd (ex : 230212 pour 12 février 2023)
   "nom_topo" TEXT, -- nom du topographe ayant levé le point
   "note" TEXT
);
SELECT 
AddGeometryColumn ('pts_topo','geometry',2154,'POINT','XYZ',0);

-- LES COUPES DANS QGIS : tables nécessaires pour réaliser les coupes des faits dans QGIS (Caro POWAAAAA)

-- La table coupe_axe
DROP TABLE IF EXISTS coupe_axe;
CREATE TABLE coupe_axe(
   "id_coupe" INTEGER PRIMARY KEY,
   "numaxe" INTEGER DEFAULT NULL,
   "numinute" INTEGER DEFAULT NULL,
   "numsd" INTEGER DEFAULT NULL,
   "azimuth" FLOAT,
   "alti" FLOAT,
   "note" TEXT	
--   ,FOREIGN KEY ("numaxe") REFERENCES t_axe("numaxe")
--   ,FOREIGN KEY ("numinute") REFERENCES t_minute("numinute")
--   ,FOREIGN KEY ("numsd") REFERENCES t_sondage("numsd")
);
SELECT 
AddGeometryColumn ('coupe_axe','geometry',2154,'LINESTRING','XY',0);

-- La table coupe_line
DROP TABLE IF EXISTS coupe_line;
CREATE TABLE coupe_line(
   "id_cpline" INTEGER PRIMARY KEY,
   "numaxe" INTEGER DEFAULT NULL, 
   "numfait" INTEGER,
   "numus" INTEGER,
   "numsd" INTEGER DEFAULT NULL,
   "numtr" INTEGER,
   "typline" TEXT,
   "note" TEXT
--   ,FOREIGN KEY ("numaxe") REFERENCES t_axe("numaxe")
--   ,FOREIGN KEY ("numfait") REFERENCES t_fait("numfait")
--   ,FOREIGN KEY ("numus") REFERENCES t_us("numus")
--   ,FOREIGN KEY ("numsd") REFERENCES t_sondage("numsd")
);
SELECT 
AddGeometryColumn ('coupe_line','geometry',2154,'MULTILINESTRING','XY',0); -- mieux vaut prévoir une géométrie multiple dans le cas où une US négative serait interrompue (sondage, perturbation diverse)

-- La table coupe_poly
DROP TABLE IF EXISTS coupe_poly;
CREATE TABLE coupe_poly(
   "id_cpoly" INTEGER PRIMARY KEY,
   "numaxe" INTEGER  DEFAULT NULL,
   "numfait" INTEGER,
   "numus" INTEGER,
   "numsd" INTEGER DEFAULT NULL,
   "typoly" TEXT,
   "detail" TEXT,
   "note" TEXT
--   ,FOREIGN KEY ("numaxe") REFERENCES t_axe("numaxe")
--   ,FOREIGN KEY ("numfait") REFERENCES t_fait("numfait")
--   ,FOREIGN KEY ("numus") REFERENCES t_us("numus")
--   ,FOREIGN KEY ("numsd") REFERENCES t_sondage("numsd")
);
SELECT 
AddGeometryColumn ('coupe_poly','geometry',2154,'MULTIPOLYGON','XY',0);

-- Quelques tables de gestion des équipes
-- Table de liste des personnels de l'équipe
DROP TABLE IF EXISTS t_equipe;
CREATE TABLE t_equipe(
   "id_equipe" INTEGER PRIMARY KEY,
   "prenom" TEXT,
   "nom" TEXT,
   "fonction" TEXT, --cf thésaurus
   "user" TEXT,
   "password" TEXT,
   "read_only" INTEGER DEFAULT 0);
   
--Table de liste des tâches attribuées
DROP TABLE IF EXISTS t_tache;
CREATE TABLE t_tache(
   "id_tache" INTEGER PRIMARY KEY,
   "tache" TEXT);

--Les tâches possibles, ajouter les diverses études spécifiques à l'opération
INSERT INTO "t_tache" ("id_tache","tache") VALUES (1,'responsable scientifique');
INSERT INTO "t_tache" ("id_tache","tache") VALUES (2,'prescription et contrôle scientifique');
INSERT INTO "t_tache" ("id_tache","tache") VALUES (3,'montage du projet et suivi scientifique');
INSERT INTO "t_tache" ("id_tache","tache") VALUES (4,'mise en place et suivi de l''opération');
INSERT INTO "t_tache" ("id_tache","tache") VALUES (5,'montage du projet');
INSERT INTO "t_tache" ("id_tache","tache") VALUES (6,'archivage et mise à disposition des données numériques');
INSERT INTO "t_tache" ("id_tache","tache") VALUES (7,'fouille');
INSERT INTO "t_tache" ("id_tache","tache") VALUES (8,'photographie');
INSERT INTO "t_tache" ("id_tache","tache") VALUES (9,'enregistrement des données');
INSERT INTO "t_tache" ("id_tache","tache") VALUES (10,'relevé archéologique');
INSERT INTO "t_tache" ("id_tache","tache") VALUES (11,'levé topographique');
INSERT INTO "t_tache" ("id_tache","tache") VALUES (12,'coordination scientifique');
INSERT INTO "t_tache" ("id_tache","tache") VALUES (13,'rédaction');
INSERT INTO "t_tache" ("id_tache","tache") VALUES (14,'traitement primaire du mobilier');
INSERT INTO "t_tache" ("id_tache","tache") VALUES (15,'saisie des données d''enregistrement');
INSERT INTO "t_tache" ("id_tache","tache") VALUES (16,'dessin assisté par ordinateur');
INSERT INTO "t_tache" ("id_tache","tache") VALUES (17,'infographie');
INSERT INTO "t_tache" ("id_tache","tache") VALUES (18,'exploitation de système d''informations géographiques');
INSERT INTO "t_tache" ("id_tache","tache") VALUES (19,'mise en page du rapport');


--Tables de jonctions entre personnels et tâches
DROP TABLE IF EXISTS j_equipe_tache;
CREATE TABLE j_equipe_tache(
   "id_equipe_tache" INTEGER PRIMARY KEY,
   "id_equipe" INTEGER  DEFAULT NULL, 
   "id_tache" INTEGER  DEFAULT NULL
--   ,FOREIGN KEY ("id_equipe") REFERENCES t_equipe("id_equipe")
--   ,FOREIGN KEY ("id_tache") REFERENCES t_tache("id_tache")
   
   );

--Tables de jonctions entre personnels et autres tables référençant un ou plusieurs auteurs
DROP TABLE IF EXISTS j_equipe;
CREATE TABLE j_equipe(
   "id_jequipe" INTEGER PRIMARY KEY,
   "id_us" INTEGER  DEFAULT NULL, 
   "id_photo" INTEGER  DEFAULT NULL, 
   "id_minut" INTEGER  DEFAULT NULL, 
   "id_prel" INTEGER  DEFAULT NULL, 
   "id_photogram" INTEGER  DEFAULT NULL
--   ,FOREIGN KEY ("id_us") REFERENCES t_us("id_us")
--   ,FOREIGN KEY ("id_photo") REFERENCES t_photo("id_photo")
--   ,FOREIGN KEY ("id_minut") REFERENCES t_minute("id_minut")
--   ,FOREIGN KEY ("id_prel") REFERENCES t_prelevement("id_prel")
--   ,FOREIGN KEY ("id_photogram") REFERENCES t_photogram("id_photogram")
);


-- LA BASE ARCHEO : les tables des faits, sondage, us, etc. La géométrie peut être associée. 

-- FAIT : la table des faits archéologique qui récupère la géométrie par trigger de la table poly (6 couches) pour "typoly" LIKE 'fait'
DROP TABLE IF EXISTS t_fait;
CREATE TABLE t_fait(
   "id_fait" INTEGER PRIMARY KEY,
   "numfait" INTEGER UNIQUE,
   "interpret_alter" TEXT,
   "interpret" TEXT,
   "douteux" INTEGER,
   "equiv_diag" TEXT,
   "statut" TEXT,
   "rais_annule" TEXT,
   "fouille" TEXT,
   "enr_fini" INTEGER,
   "relev_fini" INTEGER,
   "photo_fini" INTEGER,
   "topo_fini" INTEGER,
   "profil" TEXT,
   "forme" TEXT,
   "orient" TEXT,
   "orient_calc" FLOAT,
   "descrip" TEXT,
   "prof_app" FLOAT,
   "diam" FLOAT,
   "dim_max" FLOAT,
   "dim_min" FLOAT,
   "epais" FLOAT,
   "prof_haut" FLOAT,
   "periode_hors_strati" TEXT, --correspond à une datation décorrélée de l'étude chrono-stratigraphique (si elle n'a pas eu lieu...), peut permettre de générer un plan phasé rapidement.
   "creator" TEXT,
   "note" TEXT
);
SELECT 
AddGeometryColumn ('t_fait','geometry',2154,'MULTIPOLYGON','XY',0);

-- US : unité stratitgraphique qui récupère la géométrie par trigger de la table poly (6 couches) pour "typoly" LIKE 'us'
DROP TABLE IF EXISTS t_us;
CREATE TABLE t_us(
   "id_us" INTEGER PRIMARY KEY,
   "numus" INTEGER UNIQUE,
   "numfait" INTEGER  DEFAULT NULL,
   "type_us" TEXT,
   "nature_us" TEXT,
   "interpret" TEXT, -- liste de valeurs : avec notamment les valeurs OTD 'dépôt inhumation primaire' et  'dépôt inhumation non primaire' et 'contenant inhumation'
   "description" TEXT,
   "statut" TEXT, -- liste de valeurs : non fouillée, en cours, fouillée, annulée
   "datsup_interpret" INTEGER,
   "datinf_interpret" INTEGER,
   "datsup_mobilier" INTEGER,
   "datinf_mobilier" INTEGER,
   "datsup_14c" INTEGER,
   "datinf_14c" INTEGER,
   "note_dat" TEXT,
   "forme" TEXT,
   "profil" TEXT, --ajout depuis le version 2.4.0
   "diam" FLOAT,
   "dim_max" FLOAT,
   "dim_min" FLOAT,
   "prof_app" FLOAT,
   "zmin" FLOAT,
   "zmax" FLOAT,
   "epais" FLOAT,
   "compo_sediment" TEXT,
   "texture" TEXT,
   "aspect" TEXT,
   "couleur" TEXT,
   "valeur_couleur" TEXT,
   "mob_note" TEXT, -- ajout depuis la version 2.5 pour renseigner les matières de mobilier présent dans l'US sous forme de champ multivalué (thesaurus : "table_thes" like 't_mobilier' et "field_thes" like 'matiere')
   "creator" TEXT,
   "datcreation" DATE,
   "note" TEXT,
   "num_seq" INTEGER  DEFAULT NULL, 
   "ordre_seq" INTEGER
--   ,FOREIGN KEY ("numfait") REFERENCES t_fait("numfait")
--   ,FOREIGN KEY ("num_seq") REFERENCES t_seq("numseq")
);
SELECT 
AddGeometryColumn ('t_us','geometry',2154,'MULTIPOLYGON','XY',0);

-- ENSEMBLE : les ensembles regroupant des faits et/ou des us (bâtiment...)
DROP TABLE IF EXISTS t_ens;
CREATE TABLE t_ens(
   "id_ens" INTEGER PRIMARY KEY,
   "numens" INTEGER UNIQUE,
   "nomens" TEXT, -- nom de l'ensemble
   "typens" TEXT, -- liste de valeurs Thesaurus (archéo, technique...)
   "interpret" TEXT, -- liste de valeurs
   "description" TEXT,
   "note" TEXT,
   "ens_englob" INTEGER -- renvoie vers le num d'un super ensemble qui englobe l'entité (et d'autres...)
   );
SELECT 
AddGeometryColumn ('t_ens','geometry',2154,'MULTIPOLYGON','XY',0);

-- SONDAGE : pour les ouvertures (unité technique) de type sondage qui récupère la géométrie par trigger de la table ouverture (6 couches) pour "typouvert" LIKE 'sondage'
DROP TABLE IF EXISTS t_sondage;
CREATE TABLE t_sondage(
   "id_sd" INTEGER PRIMARY KEY,
   "numsd" INTEGER UNIQUE,
   "numtr" INTEGER  DEFAULT NULL,
   "type" TEXT,
   "surface" TEXT,
   "prof" FLOAT,
   "topok" INTEGER DEFAULT 0, -- champ de suivi de topographie, 1 = topo faite, 0 = topo à faire
   "note" TEXT
--   ,FOREIGN KEY ("numtr") REFERENCES t_tranchee("numtr")
);
SELECT 
AddGeometryColumn ('t_sondage','geometry',2154,'MULTIPOLYGON','XY',0);

--TRANCHEE : pour les ouvertures (unité technique) de type tranchée qui récupère la géométrie par trigger de la table ouverture (6 couches) pour "typouvert" LIKE 'tranchée'
DROP TABLE IF EXISTS t_tranchee;
CREATE TABLE t_tranchee(
   "id_tr" INTEGER PRIMARY KEY,
   "numtr" INTEGER UNIQUE,
   "long" FLOAT,
   "larg" FLOAT,
   "surface" FLOAT,
   "prof_max" FLOAT,
   "prof_min" FLOAT,
   "topok" INTEGER DEFAULT 0, -- champ de suivi de topographie, 1 = topo faite, 0 = topo à faire
   "note" TEXT
   );
SELECT 
AddGeometryColumn('t_tranchee','geometry',2154,'MULTIPOLYGON','XY',0);

-- PHOTO : les photographies numériques (pas de géométrie)
DROP TABLE IF EXISTS t_photo;
CREATE TABLE t_photo(
   "id_photo" INTEGER PRIMARY KEY,
   "numphoto" INTEGER UNIQUE, -- numéro de 1 à n enregistré par l'archéo
   "nomfichier" TEXT, -- le nom du fichier photo avec son extension (attention à la casse)
   "chemin_url" TEXT, -- chemin d'accès du fichier
   "legend" TEXT, -- peut correspondre au titre de la figure dans le rapport
   "vue_vers" TEXT, -- liste de valeurs
   "creator" TEXT, -- liste de valeurs
   "datephoto" TEXT, -- date du cliché
   "type" TEXT, -- coupe, plan, mobilier, ambiance....
   "support" TEXT, -- numérique, argentique...
   "destination" TEXT, -- rapport, valorisation, publi...
   "note" TEXT
);

--table de saisie des relations entre la photo et les autres entités de la base de données. Les entités en relation sont saisies sous forme de liste d'entiers, séparés par une virgule (sans espace !)
DROP TABLE IF EXISTS traitement_photo;
CREATE TABLE traitement_photo(
	"id_traitement" INTEGER PRIMARY KEY,
	"numphoto" INTEGER UNIQUE, -- numéro de 1 à n enregistré par l'archéo
	"liste_tranchee" TEXT, --la liste des tranchées séparées par un slash '/' (SÉPARATEUR OBLIGATOIRE !)
	"liste_sd" TEXT, --la liste des sondages séparées par un slash '/' (SÉPARATEUR OBLIGATOIRE !)
	"liste_ens" TEXT, --la liste des ensembles séparées par un slash '/' (SÉPARATEUR OBLIGATOIRE !)
	"liste_fait" TEXT, --la liste des faits séparées par un slash '/' (SÉPARATEUR OBLIGATOIRE !)
	"liste_us" TEXT, --la liste des US séparées par un slash '/' (SÉPARATEUR OBLIGATOIRE !)
	"liste_log" TEXT, --la liste des logs séparées par un slash '/' (SÉPARATEUR OBLIGATOIRE !)
	"liste_mobilier" TEXT, --la liste des isos séparées par un slash '/' (SÉPARATEUR OBLIGATOIRE !)
	"liste_axe" TEXT, --la liste des axes séparés par un slash '/' (SÉPARATEUR OBLIGATOIRE !)
	"liste_photogram" TEXT, --la liste des modèles de photogrammétries séparées par un slash '/' (SÉPARATEUR OBLIGATOIRE !)
	"nomfichier" TEXT, -- le nom du fichier photo avec son extension (attention à la casse)
    "chemin_url" TEXT, -- chemin d'accès du fichier
    "legend" TEXT, -- peut correspondre au titre de la figure dans le rapport
    "vue_vers" TEXT, -- liste de valeurs
    "creator" TEXT, -- liste de valeurs
    "datephoto" TEXT, -- date du cliché
    "type" TEXT, -- coupe, plan, mobilier, ambiance....
    "support" TEXT, -- numérique, argentique...
    "destination" TEXT, -- rapport, valorisation, publi...
    "note" TEXT
	--, FOREIGN KEY ("numphoto") REFERENCES t_photo("numphoto")
);

-- MINUTE : les relevés de terrain sur minute de chantier (pas de géométrie)
DROP TABLE IF EXISTS t_minute;
CREATE TABLE t_minute(
   "id_minut" INTEGER PRIMARY KEY,
   "numinute" INTEGER UNIQUE,
   "descr" TEXT,
   "echelle" TEXT,
   "contenu" TEXT,
   "creator" TEXT,
   "format" TEXT,
   "support" TEXT,
   "nomfichier" TEXT, --ajout depuis la version 2.4.0
   "chemin_url" TEXT,
   "scan" INTEGER, -- booléen
   "min_dao" INTEGER, -- booléen
   "note" TEXT,
   "id_photogram" INTEGER  DEFAULT NULL
--   ,FOREIGN KEY ("id_photogram") REFERENCES t_photogram("id_photogram") --renvoie à l'id du modèle 3D 
);

-- MOBILIER : table revue le 09/04/2024
DROP TABLE IF EXISTS t_mobilier;
CREATE TABLE t_mobilier( 
   "id_mob" INTEGER PRIMARY KEY, -- clé primaire à la fois informatique et humaine (appelé en clé étrangère)
   "nomob" TEXT UNIQUE, -- ce n'est pas une clé primaire, puisque parfois la valeur est nulle
   "numpoint" INTEGER  DEFAULT NULL, -- ce champ doit être renseigné sur le terrain depuis Bad'Mobil comme numéro d'isolation
   "numus" INTEGER  DEFAULT NULL,
   "numfait" INTEGER  DEFAULT NULL,
   "numsd" INTEGER DEFAULT NULL,
   "iso_lot" TEXT, -- s'il s'agit d'un lot ou d'un isolat (liste fermée)
   "alti" FLOAT,
   "nr" INTEGER, -- nombre de restes
   "pr" FLOAT, -- poids des restes
   "matiere" TEXT, -- liste de valeurs (terre cuite, grès, calcaire, silex, verre, fer, argent, or, alliage cuivreux, bois, cuir, os, bois)
   "identification" TEXT, -- texte libre (ex : céramique, lithique, mortier, monnaie...)
   "description" TEXT, -- description du mobilier in extenso
   "datation" TEXT, -- datation du mobilier
   "etatcons" TEXT, -- information sur la taphonomie du mobilier
   "contenant_note" TEXT, -- un petit texte ou un numéro pour localiser le mobilier dans son contenant (en attendant d'avoir des directives du SRA sur ce point... et on n'a pas fini d'en chier...)
   "ref_cad" TEXT, -- numéro de section cadastrale et numéro de parcelle concaténés
   "note" TEXT
--   ,FOREIGN KEY ("numpoint") REFERENCES point("numpoint")
--   ,FOREIGN KEY ("numus") REFERENCES t_us("numus")
--   ,FOREIGN KEY ("numfait") REFERENCES t_fait("numfait")
--   ,FOREIGN KEY ("numsd") REFERENCES t_sondage("numsd")
 );
SELECT 
AddGeometryColumn ('t_mobilier','geometry',2154,'POINT','XY',0);

-- SEQUENCE : regroupement des US en séquences stratigraphiques (pas de géométrie)
DROP TABLE IF EXISTS t_seq;
CREATE TABLE t_seq(
   "id_seq" INTEGER PRIMARY KEY,
   "numseq" INTEGER UNIQUE,
   "titre" TEXT,
   "description" TEXT,
   "datation" TEXT,
   "crit_dat" TEXT, -- éléments sur lesquels se base la datation
   "note" TEXT
);

-- PHASE : regroupement des séquences en phases chrono-stratigraphiques (pas de géométrie)
DROP TABLE IF EXISTS t_phase;
CREATE TABLE t_phase(
   "id_phase" INTEGER PRIMARY KEY,
   "numphase" INTEGER UNIQUE,
   "ordre_stratifiant" INTEGER, -- numéro d'ordre en continu sur l'ensemble des phases pour l'utilisation du Stratifiant
   "titre" TEXT,
   "description" TEXT,
   "datation" TEXT,
   "tpq" INTEGER,
   "taq" INTEGER,
   "note" TEXT -- précision sur la nature de la phase, sur la datation...
);

-- PERIODE : regroupement des phases en périodes chronologiques (pas de géométrie)
DROP TABLE IF EXISTS t_periode;
CREATE TABLE t_periode(
   "id_period" INTEGER PRIMARY KEY,
   "numperiod" INTEGER UNIQUE,
   "titre" TEXT,
   "description" TEXT,
   "datation" TEXT,
   "date_inf" INTEGER,
   "date_sup" INTEGER,
   "note" TEXT
);

-- LOG : les logs enregistrés/décrits sur le terrain qui récupère la géométrie par trigger de la table plog (6 couches)
DROP TABLE IF EXISTS t_log; 
CREATE TABLE t_log(
   "id_log" INTEGER PRIMARY KEY,
   "numlog" INTEGER UNIQUE,
   "numtr" INTEGER  DEFAULT NULL, 
   "numsd" INTEGER  DEFAULT NULL, 
   "alti" FLOAT,
   "prof_log" FLOAT,
   "zmin_log" FLOAT,
   "objectif_log" TEXT,
   "note_log" TEXT,
   "topok" INTEGER DEFAULT 0, -- champ de suivi de topographie, 1 = topo faite, 0 = topo à faire
   "photok" INTEGER DEFAULT 0, -- champ de suivi de photographie, 1 = topo faite, 0 = topo à faire
   "numaxe" INTEGER  DEFAULT NULL -- REFERENCES t_axe("numaxe") dans le cas par exemple d'un transect
--   ,FOREIGN KEY ("numtr") REFERENCES t_tranchee("numtr")
--   ,FOREIGN KEY ("numsd") REFERENCES t_sondage("numsd")
--   ,FOREIGN KEY ("numaxe") REFERENCES t_axe("numaxe")
);
SELECT 
AddGeometryColumn ('t_log','geometry',2154,'POINT','XY',0);

-- PRELEVEMENTS : pour enregistrer les prélèvements
DROP TABLE IF EXISTS t_prelevement;
CREATE TABLE t_prelevement( 
   "id_prel" INTEGER PRIMARY KEY,
   "numprel" INTEGER UNIQUE,
   "numus" INTEGER  DEFAULT NULL, 
   "numfait" INTEGER  DEFAULT NULL,
   "type" TEXT,
   "alti" FLOAT,
   "objectif" TEXT,
   "volume" FLOAT,
   "contenant" TEXT,
   "creator" TEXT,
   "note" TEXT
--   ,FOREIGN KEY ("numus") REFERENCES t_us("numus")
--   ,FOREIGN KEY ("numfait") REFERENCES t_fait("numfait")
 );
SELECT 
AddGeometryColumn ('t_prelevement','geometry',2154,'POINT','XY',0);


-- AXE : les axes de relevé de plan et/ou coupe effectués sur le terrain qu'il peut être nécessaire d'enregistrer (en relation de n à n avec fait, us, sd, tr)
DROP TABLE IF EXISTS t_axe;
CREATE TABLE t_axe(
   "id_axe" INTEGER PRIMARY KEY,
   "numaxe" INTEGER UNIQUE NOT NULL,
   "nomaxe" TEXT,
   "note" TEXT,
   "long" FLOAT,
   "topok" INTEGER DEFAULT 0, -- champ de suivi de topographie, 1 = topo faite, 0 = topo à faire
   "alti" FLOAT
);
SELECT 
AddGeometryColumn ('t_axe','geometry',2154,'LINESTRING','XY',0);

-- TABLE DE RELATION / JOINCTION / JOINTURE (de 1 à n, permettant la relation de n à n entre deux tables utiles comme t_fait et t_photo)

-- gestion de la relation entre les axes et les sujets du relevé (faits, us, sd, tr)
DROP TABLE IF EXISTS j_rel_axe;
CREATE TABLE j_rel_axe(
   "id_rel_axe" INTEGER PRIMARY KEY,
   "numaxe" INTEGER  DEFAULT NULL, 
   "numfait" INTEGER  DEFAULT NULL, 
   "numus" INTEGER  DEFAULT NULL, 
   "numsd" INTEGER  DEFAULT NULL, 
   "numtr" INTEGER  DEFAULT NULL 
--   ,FOREIGN KEY ("numaxe") REFERENCES t_axe("numaxe")
--   ,FOREIGN KEY ("numfait") REFERENCES t_fait("numfait")
--   ,FOREIGN KEY ("numus") REFERENCES t_us("numus")
--   ,FOREIGN KEY ("numsd") REFERENCES t_sondage("numsd")
--   ,FOREIGN KEY ("numtr") REFERENCES t_tranchee("numtr")
);

-- gestion de la relation entre les US et les LOG, cette table permet de générer les box par US des logs automatiquement dans QGIS
DROP TABLE IF EXISTS j_us_log;
CREATE TABLE j_us_log(
   "id_us_log" INTEGER PRIMARY KEY,
   "numus" INTEGER  NOT NULL,
   "numlog" INTEGER  NOT NULL,
   "prof_toit" FLOAT,
   "prof_base" FLOAT,
   "epais_uslog" FLOAT,
   "zmax_uslog" FLOAT,
   "zmin_uslog" FLOAT,
   "commentaire" TEXT
--   ,FOREIGN KEY ("numus") REFERENCES t_us("numus")
--   ,FOREIGN KEY ("numlog") REFERENCES t_log("numlog")
);

-- gestion de la relation, les US (relation stratigraphique inter-US)
DROP TABLE IF EXISTS j_rel_us;
CREATE TABLE j_rel_us(
   "id_rel_us" INTEGER PRIMARY KEY,
   "numus1" INTEGER  DEFAULT NULL, 
   "numus2" INTEGER  DEFAULT NULL, 
   "typrel" TEXT,
   "incert" INTEGER, -- 0 = sûre ; 1 = incertain
   "naturelat" TEXT -- selon Desachy 2008 : rupture, continuité, indéfinie
--   ,FOREIGN KEY ("numus1") REFERENCES t_us("numus")
--   ,FOREIGN KEY ("numus2") REFERENCES t_us("numus")
);

-- gestion de la relation, les faits (relation stratigraphique inter-faits)
DROP TABLE IF EXISTS j_rel_fait;
CREATE TABLE j_rel_fait(
   "id_rel_fait" INTEGER PRIMARY KEY,
   "numfait1"  INTEGER DEFAULT NULL, 
   "numfait2" INTEGER  DEFAULT NULL, 
   "typrel" TEXT,
   "incert" INTEGER,
   "naturelat" TEXT -- selon Desachy 2008 : rupture, continuité, indéfinie
--   ,FOREIGN KEY ("numfait1") REFERENCES t_fait("numfait")
--   ,FOREIGN KEY ("numfait2") REFERENCES t_fait("numfait")
);

-- gestion de la relation entre les sondages et les US, faits et logs
DROP TABLE IF EXISTS j_rel_sondage;
CREATE TABLE j_rel_sondage(
   "id_rel_sd" INTEGER PRIMARY KEY,
   "numsd" INTEGER  DEFAULT NULL, 
   "numus" INTEGER  DEFAULT NULL, 
   "numlog" INTEGER  DEFAULT NULL, 
   "numfait" INTEGER  DEFAULT NULL 
--   ,FOREIGN KEY ("numsd") REFERENCES t_sondage("numsd")
--   ,FOREIGN KEY ("numus") REFERENCES t_us("numus")
--   ,FOREIGN KEY ("numlog") REFERENCES t_log("numlog")
--   ,FOREIGN KEY ("numfait") REFERENCES t_fait("numfait")
);

-- gestion de la relation entre les tranchées et les US, faits et les logs
DROP TABLE IF EXISTS j_rel_tranchee;
CREATE TABLE j_rel_tranchee(
   "id_rel_tr" INTEGER PRIMARY KEY,
   "numtr" INTEGER  DEFAULT NULL,
   "numus" INTEGER  DEFAULT NULL,
   "numfait" INTEGER  DEFAULT NULL,
   "numlog" INTEGER DEFAULT NULL
--   ,FOREIGN KEY ("numtr") REFERENCES t_tranchee("numtr")
--   ,FOREIGN KEY ("numus") REFERENCES t_us("numus")
--   ,FOREIGN KEY ("numfait") REFERENCES t_fait("numfait")
--   ,FOREIGN KEY ("numlog") REFERENCES t_log("numlog")
);

--gestion de la relation entre les ensembles et les US et faits
DROP TABLE IF EXISTS j_rel_ens;
CREATE TABLE j_rel_ens(
   "id_rel_ens" INTEGER PRIMARY KEY,
   "numens1" INTEGER  DEFAULT NULL,
   "numens2" INTEGER  DEFAULT NULL,
   "numus" INTEGER  DEFAULT NULL,
   "numfait" INTEGER  DEFAULT NULL,
   "typrel" TEXT,
   "incert" INTEGER
--   ,FOREIGN KEY ("numens1") REFERENCES t_ens("numens")
--   ,FOREIGN KEY ("numens2") REFERENCES t_ens("numens")
--   ,FOREIGN KEY ("numus") REFERENCES t_us("numus")
--   ,FOREIGN KEY ("numfait") REFERENCES t_fait("numfait")
);

-- gestion de la relation entre les séquences et les phases
DROP TABLE IF EXISTS j_seq_phase;
CREATE TABLE j_seq_phase(
   "id_rel_phase" INTEGER PRIMARY KEY,
   "numseq" INTEGER  DEFAULT NULL,
   "numphase" INTEGER  DEFAULT NULL,
   "ordre" INTEGER -- ordre de la séquence dans la phase
--   ,FOREIGN KEY ("numseq") REFERENCES t_seq("numseq")
--   ,FOREIGN KEY ("numphase") REFERENCES t_phase("numphase")
);

-- gestion de la relation les phases et les périodes
DROP TABLE IF EXISTS j_phase_per;
CREATE TABLE j_phase_per(
   "id_rel_periode" INTEGER PRIMARY KEY,
   "numphase" INTEGER  DEFAULT NULL,
   "numperiod" INTEGER  DEFAULT NULL,
   "ordre" INTEGER -- ordre éventuel de la phase dans la période
--   ,FOREIGN KEY ("numphase") REFERENCES t_phase("numphase")
--   ,FOREIGN KEY ("numperiod") REFERENCES t_periode("numperiod")
);

-- gestion de la relation entre les minutes de terrain et les US, faits, ensembles, les axes, isolats, les sondages et tranchées
DROP TABLE IF EXISTS j_rel_minute;
CREATE TABLE j_rel_minute(
   "id_rel_minute" INTEGER PRIMARY KEY,
   "numinute" INTEGER  DEFAULT NULL,
   "numsd" INTEGER  DEFAULT NULL,
   "numtr" INTEGER  DEFAULT NULL,
   "numens" INTEGER  DEFAULT NULL,
   "numfait" INTEGER  DEFAULT NULL,
   "numus" INTEGER  DEFAULT NULL,
   "id_mob" INTEGER  DEFAULT NULL,
   "numlog" INTEGER  DEFAULT NULL,
   "numaxe" INTEGER  DEFAULT NULL,
   "id_prodphotogram" INTEGER  DEFAULT NULL
--   ,FOREIGN KEY ("numinute") REFERENCES t_minute("numinute")
--   ,FOREIGN KEY ("numsd") REFERENCES t_sondage("numsd")
--   ,FOREIGN KEY ("numtr") REFERENCES t_tranchee("numtr")
--   ,FOREIGN KEY ("numens") REFERENCES t_ens("numens")
--   ,FOREIGN KEY ("numfait") REFERENCES t_fait("numfait")
--   ,FOREIGN KEY ("numus") REFERENCES t_us("numus")
--   ,FOREIGN KEY ("id_mob") REFERENCES t_mobilier("id_mob")
--   ,FOREIGN KEY ("numlog") REFERENCES t_log("numlog")
--   ,FOREIGN KEY ("numaxe") REFERENCES t_axe("numaxe")
--   ,FOREIGN KEY ("id_prodphotogram") REFERENCES t_prodphotogram("id_prodphotogram")
);

--gestion de la relation entre les photos et les US, faits, les ensembles et isolats et les sondages et tranchées
DROP TABLE IF EXISTS j_rel_photo;
CREATE TABLE j_rel_photo(
   "id_rel_photo" INTEGER PRIMARY KEY,
   "numphoto" INTEGER  DEFAULT NULL, 
   "numsd" INTEGER  DEFAULT NULL, 
   "numtr" INTEGER  DEFAULT NULL, 
   "numens" INTEGER  DEFAULT NULL, 
   "numfait" INTEGER  DEFAULT NULL, 
   "numus" INTEGER  DEFAULT NULL, 
   "id_mob" INTEGER  DEFAULT NULL, 
   "numlog" INTEGER  DEFAULT NULL, 
   "numaxe" INTEGER  DEFAULT NULL,
   "id_photogram" INTEGER  DEFAULT NULL 
--   ,FOREIGN KEY ("numphoto") REFERENCES t_photo("numphoto")
--   ,FOREIGN KEY ("numsd") REFERENCES t_sondage("numsd")
--   ,FOREIGN KEY ("numtr") REFERENCES t_tranchee("numtr")
--   ,FOREIGN KEY ("numens") REFERENCES t_ens("numens")
--   ,FOREIGN KEY ("numfait") REFERENCES t_fait("numfait")
--   ,FOREIGN KEY ("numus") REFERENCES t_us("numus")
--   ,FOREIGN KEY ("id_mob") REFERENCES t_mobilier("id_mob")
--   ,FOREIGN KEY ("numlog") REFERENCES t_log("numlog")
--   ,FOREIGN KEY ("id_photogram") REFERENCES t_photogram("id_photogram")
--   ,FOREIGN KEY ("numaxe") REFERENCES t_axe("numaxe")
);

-- PHOTOGRAMMETRIE : permet le suivi des prises de vues et des traitements photogrammétriques
DROP TABLE IF EXISTS t_photogram;
CREATE TABLE t_photogram(
-- prises de vues et traitement
	"id_photogram" INTEGER PRIMARY KEY, -- clé primaire
	"obj_model" TEXT, -- multivarié, objectif(s) du modèle (calibration, ortho zenithale, ortho coupe, mns, profil)
	"legend" TEXT, -- description du ou des sujet(s) de la scène aussi bref et limpide que possible
	"photog_notes" TEXT, -- champ libre permettant de noter détails lors de prise de vues
	"nomodel" TEXT, -- nom du modèle (concaténation d'un prefixe, id_photogram, legend)
	"url_loc_ptopo" TEXT, -- chemin vers fichier image qui représente la localisation des points topos
	"urljob" TEXT, -- url du dossier de travail
	"datemodel" DATE, -- date du début du traitement du modèle
	"creator" TEXT,-- auteur du traitement d'après liste de valeurs t_equipe
	"convers" INTEGER, -- étape de conversion raw réalisée = 1
	"calib" INTEGER, -- étape de calibration réalisée = 1
	"aerotr" INTEGER, -- étape d'aérotriangulation réalisée = 1
	"georef" INTEGER, -- étape géoréférencement réalisée = 1
	"nuagedense" INTEGER, -- nombre de nuage dense créés et exportés
	"mns" INTEGER, -- nombre de mns créés et exportés
	"orthoimage" INTEGER, -- nombre d'orthoimages créees et exportés
	"maillage" INTEGER, -- nombre de maillages crées et exportés
	"texture" INTEGER, -- nombre de textures créees et exportés
	"url_rapport" TEXT, -- chemin vers le fichier log ou rapport de calcul
	"model_comment" TEXT -- commentaires sur les étapes de calcul (erreur, réussites etc.)
	,"url_model" TEXT -- url vers le modèle 3D déposé en ligne
	,"topok" INTEGER
);
SELECT 
AddGeometryColumn('t_photogram','geometry',2154,'MULTIPOLYGON','XYZ',0); -- correspond à l'emprise du modèle

-- table de relation (n à n) entre la table de modèle 3d (t_photogram) et une table de points topo qui n'existe pas encore
DROP TABLE IF EXISTS j_rel_photogram;
CREATE TABLE j_rel_photogram(
	"id_rel_photogram" INTEGER PRIMARY KEY -- clé primaire
	,"id_photogram" INTEGER DEFAULT NULL -- id du modèle 
	,"id_ptopo" INTEGER DEFAULT NULL -- ce champ est une clé étrangère vers la table de points topo
	,"numfait" INTEGER DEFAULT NULL -- ce champ est une clé étrangère vers la table de faits
	,"numus" INTEGER DEFAULT NULL -- ce champ est une clé étrangère vers la table d'US
--  ,FOREIGN KEY ("id_photogram") REFERENCES t_photogram("id_photogram")
--  ,FOREIGN KEY ("id_ptopo") REFERENCES pts_topo("id_ptopo")  
--  ,FOREIGN KEY("numfait") REFERENCES t_fait("numfait")    
--  ,FOREIGN KEY("numus") REFERENCES t_us("numus")
);

-- table de gestion des différents produits issues des traitements photogrammétriques
DROP TABLE IF EXISTS t_prodphotogram;
CREATE TABLE t_prodphotogram(
	"id_prodphotogram" INTEGER PRIMARY KEY,
	"id_photogram" INTEGER DEFAULT NULL, -- id du modèle
	"url_prod" TEXT, -- chemin vers le produit
	"nomprod" TEXT, -- nom du produit
	"typprod" TEXT, -- multivarié (orthoimage, mns, nuage de points etc.)
	"note" TEXT  --commentaire général
    --, FOREIGN KEY ("id_photogram") REFERENCES t_photogram("id_photogram")
);	
SELECT 
AddGeometryColumn('t_prodphotogram','geometry',2154,'MULTIPOLYGON','XY',0); -- correspond à l'emprise du produit issu du traitement photogrammétrique


/*Les tables fantômes de géométrie pour les vues avec géométrie*/
--multipolygon
DROP TABLE IF EXISTS ghost_polygon_2154; -- Supprime la table (et ses données) si elle existe déjà. 
CREATE TABLE ghost_polygon_2154(
   "id" INTEGER PRIMARY KEY
);
SELECT 
AddGeometryColumn ('ghost_polygon_2154','geometry',2154,'MULTIPOLYGON','XY',0);

--multipolyline
DROP TABLE IF EXISTS ghost_polyline_2154; -- Supprime la table (et ses données) si elle existe déjà. 
CREATE TABLE ghost_polyline_2154(
   "id" INTEGER PRIMARY KEY
);
SELECT 
AddGeometryColumn ('ghost_polyline_2154','geometry',2154,'MULTILINESTRING','XY',0);

--point
DROP TABLE IF EXISTS ghost_point_2154; -- Supprime la table (et ses données) si elle existe déjà. 
CREATE TABLE ghost_point_2154(
   "id" INTEGER PRIMARY KEY
);
SELECT 
AddGeometryColumn ('ghost_point_2154','geometry',2154,'POINT','XY',0);

----------------------------------------------------------------------------------------------------------------------
/*table qui les toutes les requêtes envoyées à la base de données par le serveur*/
DROP TABLE IF EXISTS t_savecommand;
CREATE TABLE t_savecommand(
   "id" INTEGER PRIMARY KEY,
   "source" TEXT, -- badmobil, qgis
   "table" TEXT, -- précise la table sur laquelle a été exécutée la requête
   "type" TEXT, -- INSERT, UPDATE ou DELETE
   "command" TEXT, -- correspond à la requête sql envoyée et exécutée
   "timestamp" TEXT, -- heure et jour de l'envoi
   "creator" TEXT -- auteur de l'envoi de l'instruction sql 
   );



----------------------------------------------------------------------------------------------------------------------
-- THESAURUS : rassemble les termes des listes de valeurs
DROP TABLE IF EXISTS thesaurus_badass;
CREATE TABLE thesaurus_badass(
   "id_thes" INTEGER PRIMARY KEY,
   "tabl_thes" TEXT, -- table où on trouve le champs qui propose la valeurs
   "field_thes" TEXT, -- champ qui propose le terme en liste de valeurs
   "val_thes" TEXT, -- il s'agit de la valeur / terme / modalité
   "def_thes" TEXT, -- si possible, définition du terme 
   "cat_thes" TEXT, -- catégorie de regroupement des termes    
   "visible" INTEGER DEFAULT 1, -- permet de définir si l'enregistrement doit être affiché dans les listes de valeur
   "comment" TEXT -- observations diverses
);

-- les valeurs du thésaurus
INSERT INTO "thesaurus_badass" ("id_thes","tabl_thes","field_thes","val_thes","def_thes","cat_thes","visible","comment") VALUES 
 (1, NULL, 'couleur', 'blanc', NULL, NULL, 1, NULL),
 (2, NULL, 'couleur', 'noir', NULL, NULL, 1, NULL),
 (3, NULL, 'couleur', 'bleu', NULL, NULL, 1, NULL),
 (4, NULL, 'couleur', 'rouge', NULL, NULL, 1, NULL),
 (5, NULL, 'couleur', 'jaune', NULL, NULL, 1, NULL),
 (6, NULL, 'couleur', 'vert', NULL, NULL, 1, NULL),
 (7, NULL, 'couleur', 'beige', NULL, NULL, 1, NULL),
 (8, NULL, 'couleur', 'gris', NULL, NULL, 1, NULL),
 (9, NULL, 'couleur', 'brun', NULL, NULL, 1, NULL),
 (10, NULL, 'couleur', 'orange', NULL, NULL, 1, NULL),
 (11, NULL, 'forme', 'circulaire', NULL, NULL, 1, NULL),
 (12, NULL, 'forme', 'ovale', NULL, NULL, 1, NULL),
 (13, NULL, 'forme', 'quadrangulaire', NULL, NULL, 1, NULL),
 (14, NULL, 'forme', 'carré', NULL, NULL, 1, NULL),
 (15, NULL, 'forme', 'indéterminée', NULL, NULL, 1, NULL),
 (16, NULL, 'forme', 'irrégulière', NULL, NULL, 1, NULL),
 (17, NULL, 'forme', 'oblongue', NULL, NULL, 1, NULL),
 (18, NULL, 'forme', 'rectiligne', NULL, NULL, 1, NULL),
 (19, NULL, 'forme', 'incomplète', NULL, NULL, 1, NULL),
 (20, NULL, 'forme', 'trapézoïdale', NULL, NULL, 1, NULL),
 (21, NULL, 'forme', 'anthropomorphe', NULL, NULL, 1, NULL),
 (22, NULL, 'forme', 'banquette', NULL, NULL, 1, NULL),
 (23, NULL, 'forme', 'non visible', NULL, NULL, 1, NULL),
 (24, 't_fait', 'fouille', '50 %', NULL, NULL, 1, NULL),
 (25, 't_fait', 'fouille', '100 %', NULL, NULL, 1, NULL),
 (26, 't_fait', 'fouille', 'sondage', NULL, NULL, 1, NULL),
 (27, 't_us', 'interpret', 'terre arable', NULL, NULL, 1, NULL),
 (28, 't_us', 'interpret', 'dépôt primaire inhumation', NULL, NULL, 1, 'ne pas modifier, nécessaire au fonctionnement'),
 (29, 't_us', 'interpret', 'dépôt non primaire inhumation', NULL, NULL, 1, 'ne pas modifier, nécessaire au fonctionnement'),
 (31, 't_us', 'interpret', 'dépôt mobilier', NULL, NULL, 1, NULL),
 (32, 't_fait', 'interpret', 'silo', NULL, NULL, 1, NULL),
 (33, 't_fait', 'interpret', 'trou de poteau', NULL, NULL, 1, NULL),
 (34, 't_fait', 'interpret', 'fosse', NULL, NULL, 1, NULL),
 (35, 't_fait', 'interpret', 'fossé', NULL, NULL, 1, NULL),
 (36, 't_fait', 'interpret', 'four', NULL, NULL, 1, NULL),
 (37, 't_fait', 'interpret', 'maçonnerie', NULL, NULL, 1, NULL),
 (38, 't_fait', 'interpret', 'cave', NULL, NULL, 1, NULL),
 (39, 't_fait', 'interpret', 'réseau', NULL, NULL, 1, NULL),
 (40, 't_fait', 'interpret', 'puits', NULL, NULL, 1, NULL),
 (41, 't_fait', 'interpret', 'citerne', NULL, NULL, 1, NULL),
 (42, 't_fait', 'interpret', 'tranchée', NULL, NULL, 1, NULL),
 (43, 't_fait', 'interpret', 'sépulture', NULL, NULL, 1, NULL),
 (44, 't_fait', 'interpret', 'voie', NULL, NULL, 1, NULL),
 (45, 't_fait', 'interpret', 'fond de cabane', NULL, NULL, 1, NULL),
 (46, 't_fait', 'interpret', 'plate-forme', NULL, NULL, 1, NULL),
 (47, 't_fait', 'interpret', 'sablière', NULL, NULL, 1, NULL),
 (48, 't_fait', 'interpret', 'chablis', NULL, NULL, 1, NULL),
 (49, 't_fait', 'interpret', 'mare', NULL, NULL, 1, NULL),
 (50, 't_fait', 'interpret', 'anomalie géologique', NULL, NULL, 1, NULL),
 (51, 't_fait', 'interpret', 'incinération', NULL, NULL, 1, NULL),
 (52, 't_fait', 'interpret', 'cellier', NULL, NULL, 1, NULL),
 (53, 't_fait', 'interpret', 'radier', NULL, NULL, 1, NULL),
 (54, 't_fait', 'interpret', 'séchoir', NULL, NULL, 1, NULL),
 (55, 't_fait', 'interpret', 'trou de plantation', NULL, NULL, 1, NULL),
 (56, 't_fait', 'interpret', 'drain', NULL, NULL, 1, NULL),
 (57, 't_fait', 'interpret', 'indéterminée', NULL, NULL, 1, NULL),
 (58, 't_fait', 'interpret', 'sol', NULL, NULL, 1, NULL),
 (61, 't_mobilier', 'matiere', 'calcaire', NULL, NULL, 1, NULL),
 (64, 't_mobilier', 'matiere', 'grès', NULL, NULL, 1, NULL),
 (68, 't_mobilier', 'matiere', 'minéral indéterminé', NULL, NULL, 1, NULL),
 (73, 't_mobilier', 'matiere', 'silex', NULL, NULL, 1, NULL),
 (113, 't_us', 'nature_us', 'construction', NULL, NULL, 1, NULL),
 (114, 't_us', 'nature_us', 'occupation', NULL, NULL, 1, NULL),
 (115, 't_us', 'nature_us', 'destruction', NULL, NULL, 1, NULL),
 (116, 't_us', 'nature_us', 'abandon', NULL, NULL, 1, NULL),
 (117, 't_us', 'nature_us', 'remblai', NULL, NULL, 1, NULL),
 (118, 't_us', 'interpret', 'colluvions', NULL, NULL, 1, NULL),
 (119, 't_us', 'interpret', 'alluvions', NULL, NULL, 1, NULL),
 (120, 't_us', 'nature_us', 'naturel', NULL, NULL, 1, NULL),
 (122, 't_us', 'nature_us', 'autre', NULL, NULL, 1, NULL),
 (123, 't_fait', 'profil', 'en cuvette', NULL, NULL, 1, NULL),
 (124, 't_fait', 'profil', 'piriforme', NULL, NULL, 1, NULL),
 (125, 't_fait', 'profil', 'en "V"', NULL, NULL, 1, NULL),
 (126, 't_fait', 'profil', 'en "U"', NULL, NULL, 1, NULL),
 (127, 't_fait', 'profil', 'en auge', NULL, NULL, 1, NULL),
 (128, 't_fait', 'profil', 'irrégulier', NULL, NULL, 1, NULL),
 (129, 't_fait', 'profil', 'à fond plat', NULL, NULL, 1, NULL),
 (130, 't_fait', 'profil', 'indéterminé', NULL, NULL, 1, NULL),
 (131, 't_fait', 'profil', 'évasé', NULL, NULL, 1, NULL),
 (132, 't_fait', 'profil', 'concave', NULL, NULL, 1, NULL),
 (133, 't_fait', 'profil', 'cylindrique', NULL, NULL, 1, NULL),
 (134, 't_fait', 'profil', 'incomplet', NULL, NULL, 1, NULL),
 (135, 't_fait', 'statut', 'en cours', NULL, NULL, 1, NULL),
 (136, 't_fait', 'statut', 'fouillé', NULL, NULL, 1, NULL),
 (137, 't_fait', 'statut', 'non fouillé', NULL, NULL, 1, NULL),
 (139, 't_us', 'type_us', 'couche', NULL, NULL, 1, 'ne pas modifier'),
 (140, 't_us', 'type_us', 'négatif', NULL, NULL, 1, 'ne pas modifier'),
 (141, 't_us', 'type_us', 'altération', NULL, NULL, 1, 'ne pas modifier, nécessaire au fonctionnement'),
 (142, 't_us', 'valeur', 'clair', NULL, NULL, 1, NULL),
 (143, 't_us', 'valeur', 'moyen', NULL, NULL, 1, NULL),
 (144, 't_us', 'valeur', 'foncé', NULL, NULL, 1, NULL),
 (145, 'j_rel_us', 'typrel', 'sous', NULL, NULL, 1, NULL),
 (146, 'j_rel_us', 'typrel', 'sur', NULL, NULL, 1, NULL),
 (147, 'j_rel_us', 'typrel', 'égale', NULL, NULL, 1, NULL),
 (148, 'j_rel_us', 'typrel', 'équivalente', NULL, NULL, 1, NULL),
 (149, 'j_rel_us', 'typrel', 'synchrone', NULL, NULL, 1, NULL),
 (150, 't_fait', 'periode_hors_strati', 'Paléolithique', '-3300000 à -10000', NULL, 1, NULL),
 (151, 't_fait', 'periode_hors_strati', 'Paléolithique inférieur', '-3300000 à -300000', NULL, 1, NULL),
 (152, 't_fait', 'periode_hors_strati', 'Paléolithique moyen', '-300000 à -40000', NULL, 1, NULL),
 (153, 't_fait', 'periode_hors_strati', 'Paléolithique supérieur', '-40000 à -10000', NULL, 1, NULL),
 (154, 't_fait', 'periode_hors_strati', 'Mésolithique et Epipaléolithique', '-10000 à -5500', NULL, 1, NULL),
 (155, 't_fait', 'periode_hors_strati', 'Néolithique', '-5500 à -2200', NULL, 1, NULL),
 (156, 't_fait', 'periode_hors_strati', 'Néolithique ancien', '-5500 à -2200', NULL, 1, NULL),
 (157, 't_fait', 'periode_hors_strati', 'Néolithique moyen', '-5500 à -4600', NULL, 1, NULL),
 (158, 't_fait', 'periode_hors_strati', 'Néolithique récent', '-4600 à -3400', NULL, 1, NULL),
 (159, 't_fait', 'periode_hors_strati', 'Chalcolithique', '-3400 à -2900', NULL, 1, NULL),
 (160, 't_fait', 'periode_hors_strati', 'Protohistoire', '-2900 à -50', NULL, 1, NULL),
 (161, 't_fait', 'periode_hors_strati', 'Âge du Bronze', '-2200 à -800', NULL, 1, NULL),
 (162, 't_fait', 'periode_hors_strati', 'Âge du Bronze ancien', '-2200 à -1600', NULL, 1, NULL),
 (163, 't_fait', 'periode_hors_strati', 'Âge du Bronze moyen', '-1600 à -1400', NULL, 1, NULL),
 (164, 't_fait', 'periode_hors_strati', 'Âge du Bronze récent', '-1400 à -800', NULL, 1, NULL),
 (165, 't_fait', 'periode_hors_strati', 'Âge du Fer', '-800 à -50', NULL, 1, NULL),
 (166, 't_fait', 'periode_hors_strati', 'Hallstatt', '-800 à -450', NULL, 1, NULL),
 (167, 't_fait', 'periode_hors_strati', 'La Tène', '-450 à -50', NULL, 1, NULL),
 (168, 't_fait', 'periode_hors_strati', 'Antiquité romaine', '-50 à 476', NULL, 1, NULL),
 (169, 't_fait', 'periode_hors_strati', 'République romaine', '-50 à 10', NULL, 1, NULL),
 (170, 't_fait', 'periode_hors_strati', 'Empire romain', '-37 à 476', NULL, 1, NULL),
 (171, 't_fait', 'periode_hors_strati', 'Haut-Empire', '-37 à 235', NULL, 1, NULL),
 (172, 't_fait', 'periode_hors_strati', 'Bas-Empire', '235 à 476', NULL, 1, NULL),
 (173, 't_fait', 'periode_hors_strati', 'époque médiévale', '476 à 1492', NULL, 1, NULL),
 (174, 't_fait', 'periode_hors_strati', 'haut Moyen Âge', '476 à 999', NULL, 1, NULL),
 (175, 't_fait', 'periode_hors_strati', 'Moyen Âge', '1000 à 1299', NULL, 1, NULL),
 (176, 't_fait', 'periode_hors_strati', 'bas Moyen Âge', '1299 à 1492', NULL, 1, NULL),
 (177, 't_fait', 'periode_hors_strati', 'Temps modernes', '1492 à 1789', NULL, 1, NULL),
 (178, 't_fait', 'periode_hors_strati', 'époque contemporaine', '1789 à aujourd''hui', NULL, 1, NULL),
 (179, 't_fait', 'periode_hors_strati', 'ère industrielle', '1800 à 1914', NULL, 1, NULL),
 (180, 't_us', 'compo_sediment', 'limon', NULL, NULL, 1, NULL),
 (181, 't_us', 'compo_sediment', 'argile', NULL, NULL, 1, NULL),
 (182, 't_us', 'compo_sediment', 'sable', NULL, NULL, 1, NULL),
 (183, 't_us', 'texture', 'très compact', NULL, NULL, 1, NULL),
 (184, 't_us', 'texture', 'compact', NULL, NULL, 1, NULL),
 (185, 't_us', 'texture', 'meuble', NULL, NULL, 1, NULL),
 (186, 't_us', 'texture', 'très meuble', NULL, NULL, 1, NULL),
 (187, 't_us', 'interpret', 'aménagement inhumation', NULL, NULL, 1, NULL),
 (188, 't_us', 'interpret', 'contenant inhumation', NULL, NULL, 1, 'ne pas modifier, nécessaire au fonctionnement'),
 (189, 't_fait', 'orientation', 'nord/sud', NULL, NULL, 1, NULL),
 (190, 't_fait', 'orientation', 'sud/nord', NULL, NULL, 1, NULL),
 (191, 't_fait', 'orientation', 'est/ouest', NULL, NULL, 1, NULL),
 (192, 't_fait', 'orientation', 'ouest/est', NULL, NULL, 1, NULL),
 (193, 't_fait', 'orientation', 'nord-ouest/sud-est', NULL, NULL, 1, NULL),
 (194, 't_fait', 'orientation', 'sud-est/nord-ouest', NULL, NULL, 1, NULL),
 (195, 't_fait', 'orientation', 'nord-est/sud-ouest', NULL, NULL, 1, NULL),
 (196, 't_fait', 'orientation', 'sud-ouest/nord-est', NULL, NULL, 1, NULL),
 (198, 't_us', 'interpret', 'décapage', NULL, NULL, 1, NULL),
 (199, 't_us', 'interpret', 'nettoyage', NULL, NULL, 1, NULL),
 (200, 't_us', 'interpret', 'remblai', NULL, NULL, 1, NULL),
 (201, 't_us', 'interpret', 'creusement', NULL, NULL, 1, NULL),
 (202, 't_us', 'interpret', 'comblement', NULL, NULL, 1, NULL),
 (203, 't_us', 'interpret', 'radier', NULL, NULL, 1, NULL),
 (249, 't_mobilier', 'etatcons', 'très mauvais', NULL, NULL, 1, NULL),
 (250, 't_mobilier', 'etatcons', 'mauvais', NULL, NULL, 1, NULL),
 (251, 't_mobilier', 'etatcons', 'moyen', NULL, NULL, 1, NULL),
 (252, 't_mobilier', 'etatcons', 'bon', NULL, NULL, 1, NULL),
 (253, 't_mobilier', 'etatcons', 'très bon', NULL, NULL, 1, NULL),
 (254, 't_mobilier', 'etatcons', 'complet', NULL, NULL, 1, NULL),
 (283, 't_prelevement', 'objectif', 'prélèvement brut non trié', NULL, NULL, 1, NULL),
 (284, 't_prelevement', 'objectif', 'anthracologie', 'charbons de bois', NULL, 1, NULL),
 (285, 't_prelevement', 'objectif', 'carpologie', NULL, NULL, 1, NULL),
 (286, 't_prelevement', 'objectif', 'palynologie', NULL, NULL, 1, NULL),
 (287, 't_prelevement', 'objectif', 'granulométrie', NULL, NULL, 1, NULL),
 (288, 't_prelevement', 'objectif', 'micromorphologie', NULL, NULL, 1, NULL),
 (289, 't_prelevement', 'objectif', 'dendochronologie', NULL, NULL, 1, NULL),
 (290, 't_prelevement', 'objectif', 'thermoluminescence', NULL, NULL, 1, NULL),
 (291, 't_prelevement', 'objectif', 'datation carbone 14', NULL, NULL, 1, NULL),
 (292, 't_prelevement', 'objectif', 'phosphates', NULL, NULL, 1, NULL),
 (293, 't_prelevement', 'objectif', 'parasitologie', NULL, NULL, 1, NULL),
 (406, 't_minute', 'echelle', '1/10e', NULL, NULL, 1, NULL),
 (407, 't_minute', 'echelle', '1/20e', NULL, NULL, 1, NULL),
 (408, 't_minute', 'echelle', '1/40e', NULL, NULL, 1, NULL),
 (409, 't_minute', 'echelle', '1/50e', NULL, NULL, 1, NULL),
 (410, 't_minute', 'echelle', '1/100e', NULL, NULL, 1, NULL),
 (411, 't_minute', 'support', 'calque', NULL, NULL, 1, NULL),
 (412, 't_minute', 'support', 'numérique', NULL, NULL, 1, NULL),
 (413, 't_minute', 'format', 'A4', NULL, NULL, 1, NULL),
 (414, 't_minute', 'format', 'A3', NULL, NULL, 1, NULL),
 (415, NULL, 'auteur', 'M.X', NULL, NULL, 1, NULL),
 (416, NULL, 'auteur', 'Mme Y.', NULL, NULL, 1, NULL),
 (417, 't_minute', 'format', 'autre', NULL, NULL, 1, NULL),
 (418, 't_sondage', 'type', 'manuel', NULL, NULL, 1, NULL),
 (419, 't_sondage', 'type', 'mécanique', NULL, NULL, 1, NULL),
 (420, 't_sondage', 'type', 'profond', NULL, NULL, 1, NULL),
 (421, 't_ens', 'typens', 'archéologique', NULL, NULL, 1, NULL),
 (422, 't_ens', 'typens', 'technique', NULL, NULL, 1, NULL),
 (423, 't_ens', 'typens', 'entité architecturale', NULL, NULL, 1, NULL),
 (424, 't_ens', 'interpret', 'bâtiment', NULL, 'archéologique', 1, NULL),
 (425, 't_ens', 'interpret', 'enclos', NULL, 'archéologique', 1, NULL),
 (426, 't_ens', 'interpret', 'palissade', NULL, 'archéologique', 1, NULL),
 (427, 't_ens', 'interpret', 'cave', NULL, 'archéologique', 1, NULL),
 (428, 't_ens', 'interpret', 'nécropole', NULL, 'archéologique', 1, NULL),
 (429, 't_ens', 'interpret', 'zone', NULL, 'technique', 1, NULL),
 (430, 't_ens', 'interpret', 'secteur', NULL, 'technique', 1, NULL),
 (431, 't_ens', 'interpret', 'groupe', NULL, 'technique', 1, NULL),
 (432, 't_photo', 'support', 'numérique', NULL, NULL, 1, NULL),
 (433, 't_photo', 'support', 'argentique', NULL, NULL, 1, NULL),
 (434, 't_photo', 'vue_vers', 'nord', NULL, NULL, 1, NULL),
 (435, 't_photo', 'vue_vers', 'sud', NULL, NULL, 1, NULL),
 (436, 't_photo', 'vue_vers', 'est', NULL, NULL, 1, NULL),
 (437, 't_photo', 'vue_vers', 'ouest', NULL, NULL, 1, NULL),
 (438, 't_photo', 'vue_vers', 'nord-est', NULL, NULL, 1, NULL),
 (439, 't_photo', 'vue_vers', 'nord-ouest', NULL, NULL, 1, NULL),
 (440, 't_photo', 'vue_vers', 'sud-est', NULL, NULL, 1, NULL),
 (441, 't_photo', 'vue_vers', 'sud-ouest', NULL, NULL, 1, NULL),
 (442, 't_photogram', 'obj_model', 'orthoimage zenithale', NULL, NULL, 1, NULL),
 (443, 't_photogram', 'obj_model', 'orthoimage coupe', NULL, NULL, 1, NULL),
 (444, 't_photogram', 'obj_model', 'profil', NULL, NULL, 1, NULL),
 (445, 't_photogram', 'obj_model', 'modèle 3D', NULL, NULL, 1, NULL),
 (450, 't_equipe', 'fonction', 'conservateur·rice régional·e de l''archéologie', NULL, NULL, 1, NULL),
 (451, 't_equipe', 'fonction', 'conservateur·rice régional·e de l''archéologie adjoint', NULL, NULL, 1, NULL),
 (452, 't_equipe', 'fonction', 'conservateur·rice du patrimoine', NULL, NULL, 1, NULL),
 (453, 't_equipe', 'fonction', 'ingénieur·e d''études', NULL, NULL, 1, NULL),
 (454, 't_equipe', 'fonction', 'directeur·rice adjoint·e scientifiques et techniques', NULL, NULL, 1, NULL),
 (455, 't_equipe', 'fonction', 'délégué·e au directeur·trice adjoint scientifiques et techniques', NULL, NULL, 1, NULL),
 (456, 't_equipe', 'fonction', 'administrateur·rice', NULL, NULL, 1, NULL),
 (457, 't_equipe', 'fonction', 'gestionnaire de conventions, de contrats et marchés publics', NULL, NULL, 1, NULL),
 (458, 't_equipe', 'fonction', 'gestionnaire de collection', NULL, NULL, 1, NULL),
 (459, 't_equipe', 'fonction', 'documentaliste', NULL, NULL, 1, NULL),
 (460, 't_equipe', 'fonction', 'responsable de recherches archéologiques', NULL, NULL, 1, NULL),
 (461, 't_equipe', 'fonction', 'assistant·e de prévention', NULL, NULL, 1, NULL),
 (462, 't_equipe', 'fonction', 'topographe', NULL, NULL, 1, NULL),
 (463, 't_equipe', 'fonction', 'assistant·e de recherches archéologiques', NULL, NULL, 1, NULL),
 (464, 't_equipe', 'fonction', 'technicien·ne de recherches archéologiques', NULL, NULL, 1, NULL),
 (465, 't_equipe', 'fonction', 'spécialiste', NULL, NULL, 1, NULL),
 (467, 't_equipe', 'fonction', 'infographe', NULL, NULL, 1, NULL),
 (468, 'j_rel_us', 'naturelat', 'rupture', NULL, NULL, 1, NULL),
 (469, 'j_rel_us', 'naturelat', 'continuité', NULL, NULL, 1, NULL),
 (470, 'j_rel_us', 'naturelat', 'indéfinie', NULL, NULL, 1, NULL),
 (471, 'coupe_line', 'typline', 'TV', NULL, NULL, 1, NULL),
 (472, 'coupe_line', 'typline', 'US négative', NULL, NULL, 1, NULL),
 (473, 'coupe_line', 'typline', 'US négative restituée', NULL, NULL, 1, NULL),
 (474, 'coupe_line', 'typline', 'US', NULL, NULL, 1, NULL),
 (475, 'coupe_line', 'typline', 'restitution', NULL, NULL, 1, NULL),
 (476, 'coupe_line', 'typline', 'limite d''observation', NULL, NULL, 1, NULL),
 (477, 'coupe_line', 'typline', 'non visible', NULL, NULL, 1, NULL),
 (478, 'coupe_poly', 'typoly', 'US', NULL, NULL, 1, NULL),
 (479, 'coupe_poly', 'typoly', 'non visible', NULL, NULL, 1, NULL),
 (480, 'coupe_poly', 'detail', 'calcaire', NULL, NULL, 1, NULL),
 (481, 'coupe_poly', 'detail', 'torchis', NULL, NULL, 1, NULL),
 (482, 'coupe_poly', 'detail', 'calcaire brûlé', NULL, NULL, 1, NULL),
 (483, 'coupe_poly', 'detail', 'céramique', NULL, NULL, 1, NULL),
 (484, 'coupe_poly', 'detail', 'terre cuite', NULL, NULL, 1, NULL),
 (485, 't_us', 'aspect', 'homogène', NULL, NULL, 1, NULL),
 (486, 't_us', 'aspect', 'hétérogène', NULL, NULL, 1, NULL),
 (487, 't_photo', 'type', 'ambiance', NULL, NULL, 1, NULL),
 (488, 't_photo', 'type', 'général', NULL, NULL, 1, NULL),
 (489, 't_photo', 'type', 'coupe', NULL, NULL, 1, NULL),
 (490, 't_photo', 'type', 'plan', NULL, NULL, 1, NULL),
 (491, 't_photo', 'type', 'détail', NULL, NULL, 1, NULL),
 (492, 't_photo', 'type', 'mobilier', NULL, NULL, 1, NULL),
 (493, 't_photo', 'type', 'log', NULL, NULL, 1, NULL),
 (494, 't_photo', 'type', 'photogrammétrie', NULL, NULL, 1, NULL),
 (495, 't_photo', 'type', 'coupe et plan', NULL, NULL, 1, NULL),
 (496, 't_photo', 'type', 'zénithal', NULL, NULL, 1, NULL),
 (497, 't_us', 'type_us', 'annulée', NULL, NULL, 1, NULL),
 (498, 'coupe_poly', 'typoly', 'inclusion', NULL, NULL, 1, NULL),
 (499, 't_mobilier', 'matiere', 'terre cuite', NULL, '', 1, NULL),
 (500, 't_mobilier', 'matiere', 'verre', NULL, NULL, 1, NULL),
 (501, 't_mobilier', 'matiere', 'fer', NULL, NULL, 1, NULL),
 (502, 't_mobilier', 'matiere', 'alliage cuivreux', NULL, NULL, 1, NULL),
 (503, 't_mobilier', 'matiere', 'os', NULL, NULL, 1, NULL),
 (504, 't_mobilier', 'matiere', 'or', NULL, NULL, 1, NULL),
 (505, 't_mobilier', 'matiere', 'argent', NULL, NULL, 1, NULL),
 (506, 't_prelevement', 'type', 'vrac', NULL, NULL, 1, NULL),
 (507, 't_prelevement', 'type', 'bloc', NULL, NULL, 1, NULL),
 (508, 't_prelevement', 'type', 'colonne', NULL, NULL, 1, NULL),
 (509, 't_prelevement', 'type', 'fragment', NULL, NULL, 1, NULL),
 (510, 't_prelevement', 'objectif', 'phytolite', NULL, NULL, 1, NULL),
 (511, 't_prelevement', 'objectif', 'ADN', NULL, NULL, 1, NULL),
 (512, 't_prelevement', 'objectif', 'analyse d''isotope', NULL, NULL, 1, NULL),
 (513, 't_us', 'interpret', 'maçonnerie', NULL, NULL, 1, NULL),
 (514, 't_us', 'interpret', 'niveau de sol', NULL, NULL, 1, NULL),
 (515, 't_us', 'interpret', 'indéterminé', NULL, NULL, 1, NULL),
 (516, 't_fait', 'interpret', 'annulée', NULL, NULL, 1, NULL),
 (517, 't_prodphotogram', 'typprod', 'modèle numérique de surface (MNS)', NULL, NULL, 1, NULL),
 (518, 't_prodphotogram', 'typprod', 'orthoimage', NULL, NULL, 1, NULL),
 (519, 't_photogram', 'obj_model', 'MNS', NULL, NULL, 1, NULL),
 (520, 't_photogram', 'obj_model', 'calibration', NULL, NULL, 1, NULL),
 (522, 't_prodphotogram', 'typprod', 'nuage de points', NULL, NULL, 1, NULL),
 (523, 't_prodphotogram', 'typprod', 'modele 3D', NULL, NULL, 1, NULL);

----------------------------------------------------------------------------------------------------------------------
-- LES TRIGGERS --
 
-- pour les préfixes :
-- trg pour TRIGGER
-- a pour AFTER
-- b pour BEFORE
-- i pour INSERT
-- u pour UPDATE
-- d pour DELETE


-- les triggers à mettre en place pour la table poly (qui doivent mettre à jour les tables t_fait et t_us)

-- AFTER INSERT
-- qui met à jour la table t_fait après CREATION d'une entité dans poly
CREATE TRIGGER trgai_poly_maj_t_fait /*déclaration de création d'un nouveau trigger qui a pour nom...*/
    AFTER INSERT /*qui sera exécuté après l'ajout d'une nouvelle entité*/
    ON poly /*sur/dans la table*/
FOR EACH ROW /*commande obligatoire : pour tous les enregistrement*/
WHEN (NEW.typoly = 'fait') /*cette condition permet de restreindre les enregistrements concernés aux seuls 'fait' ; NEW correspond à une copie temporaire des nouveaux éléments de la table t_poly effectuée lors de l'exécution du trigger*/
BEGIN /* debut de l'action déclenchée*/
UPDATE t_fait /*avec une modification de la table t_fait*/
SET geometry = NEW.geometry /*qui redéfini la valeur du champ "geom" de la table t_fait par la valeur du champ "geom" de la copie temporaire NEW de la table t_poly*/
WHERE NEW.numpoly = numfait ; /*à chaque fois que la valeur du champ "num_fait" de la table t_fait est égale à la valeur du champ "numpoly" de la copie temporaire NEW de la table t_poly*/
END ; /*fin de l'action et fin du trigger*/

-- qui met à jour la table t_us après CREATION d'une entité dans poly
CREATE TRIGGER trgai_poly_maj_t_us 
    AFTER INSERT 
    ON poly 
FOR EACH ROW 
WHEN (NEW.typoly IN('US','us'))
   BEGIN
   UPDATE t_us
   SET geometry = NEW.geometry 
   WHERE NEW.numpoly = numus ;
END ; 

-- AFTER UPDATE
-- qui met à jour la table t_fait après MODIFICATION d'une entité dans poly
CREATE TRIGGER trgau_poly_maj_t_fait
   AFTER UPDATE /*qui sera exécuté après la modification de la géométrie d'une entité*/
   ON poly
FOR EACH ROW
WHEN (NEW.typoly = 'fait') AND NOT ST_Equals(NEW.geometry, OLD.geometry) -- ajout d'une condition supplémentaire
BEGIN
UPDATE t_fait
SET geometry = NEW.geometry
WHERE NEW.numpoly = numfait ;
END ;

-- qui met à jour la table t_us après MODIFICATION d'une entité dans poly
CREATE TRIGGER trgau_poly_maj_t_us
   AFTER UPDATE 
   ON poly
FOR EACH ROW
WHEN (NEW.typoly IN('US','us') ) AND NOT ST_Equals(NEW.geometry, OLD.geometry) -- ajout d'une condition supplémentaire
BEGIN
UPDATE t_us
SET geometry = NEW.geometry
WHERE NEW.numpoly = numus ;
END ;


-- AFTER DELETE
-- pas utile, parce que dans le cas d'une annulation, on préfère indiquer que l'objet est annulé plutôt que de supprimer l'enregitrement et dans le cas de mise à jour des entités dans la couche poly après un lever topo, il ne doit pas y avoir de conséquence sur l'enregistrement dans t_fait ou t_us


-- les triggers à mettre en place pour la table t_fait (qui doivent mettre à jour la table poly)

-- AFTER INSERT
-- on n'en fait pas de celui là car une création d'entité 
-- dans t_fait ne doit pas forcément induire une création équivalente dans t_poly

-- AFTER UPDATE
CREATE TRIGGER trgau_t_fait_maj_poly /*déclaration de création d'un nouveau trigger qui a pour nom...*/
   AFTER UPDATE /*qui sera exécuté après la modification de la géométrie d'une entité*/
   ON t_fait /*sur la table*/
FOR EACH ROW /*commande obligatoire : pour tous les enregistrement*/
WHEN NOT ST_Equals(NEW.geometry, OLD.geometry) -- ajout d'une condition supplémentaire
BEGIN /* debut de l'action déclenchée*/
UPDATE poly /*avec une modification de la table t_poly*/
SET geometry = NEW.geometry /*qui redéfini la valeur du champ "geom" de la table t_poly par la valeur du champ "geom" de la copie temporaire des nouveaux éléments NEW de la table t_fait*/
WHERE NEW.numfait = numpoly AND typoly = 'fait' ; /*pour les entités dont la valeur du champ "numpoly" de la table t_poly est égale à la valeur du champ "num_fait" de la copie temporaire NEW de la table t_fait et pour lesquels la valeur 'fait' est renseignée dans le champ "typoly"*/
END ; /*fin de l'action et fin du trigger*/

-- AFTER DELETE
-- inutile, voir supra

-- les triggers à mettre en place pour la table t_us (qui doivent mettre à jour la table poly)
-- AFTER UPDATE
CREATE TRIGGER trgau_t_us_maj_poly 
   AFTER UPDATE 
   ON t_us 
FOR EACH ROW 
WHEN NOT ST_Equals(NEW.geometry, OLD.geometry) -- ajout d'une condition supplémentaire
BEGIN 
UPDATE poly 
SET geometry = NEW.geometry 
WHERE NEW.numus = numpoly AND typoly IN('us', 'US') ; 
END ; 

-- AFTER DELETE
-- inutile, voir supra

-- trigger qui calcule la surface dans la table ouverture
-- AFTER INSERT
CREATE TRIGGER trgai_ouverture_surface
AFTER INSERT
ON ouverture
FOR EACH ROW
BEGIN
    UPDATE ouverture
    SET surface = ST_Area(geometry)
    WHERE rowid = NEW.rowid;
END;

-- BEFORE UPDATE
CREATE TRIGGER trgau_ouverture_surface 
BEFORE UPDATE 
ON ouverture 
FOR EACH ROW 
BEGIN 
	UPDATE ouverture 
	SET surface = ST_Area(NEW.geometry) 
	WHERE rowid = NEW.rowid;
END ;

-- les triggers à mettre en place pour la table ouverture (qui doivent mettre à jour les tables t_sondage et t_tranchee)

-- AFTER INSERT
-- qui met à jour la table t_sondage après CREATION d'une entité dans ouverture
CREATE TRIGGER trgai_ouverture_maj_t_sondage 
AFTER INSERT 
ON ouverture 
FOR EACH ROW 
WHEN (NEW.typouvert = 'sondage') 
BEGIN 
    UPDATE t_sondage
    SET surface = NEW.surface
    WHERE NEW.numouvert = numsd;

    UPDATE t_sondage 
    SET geometry = NEW.geometry 
    WHERE NEW.numouvert = numsd ; 
END ; 

-- qui met à jour la table t_tranchee après CREATION d'une entité dans ouverture
CREATE TRIGGER trgai_ouverture_maj_t_tranchee 
AFTER INSERT 
ON ouverture 
FOR EACH ROW 
WHEN (NEW.typouvert = 'tranchée')  
BEGIN
  UPDATE t_tranchee
  SET surface = NEW.surface
  WHERE numtr = NEW.numouvert;

  UPDATE t_tranchee
  SET geometry = NEW.geometry 
  WHERE numtr = NEW.numouvert;
END ; 

-- AFTER UPDATE
-- qui met à jour la table t_sondage après MODIFICATION d'une entité dans ouverture
CREATE TRIGGER trgau_ouverture_maj_t_sondage
AFTER UPDATE 
ON ouverture
FOR EACH ROW
WHEN (NEW.typouvert = 'sondage') AND NOT ST_Equals(NEW.geometry, OLD.geometry) -- ajout d'une condition supplémentaire
BEGIN
    UPDATE t_sondage
    SET surface = NEW.surface
    WHERE NEW.numouvert = numsd;

    UPDATE t_sondage
    SET geometry = NEW.geometry
    WHERE NEW.numouvert = numsd ;
END ;

-- qui met à jour la table t_tranchee après MODIFICATION d'une entité dans ouverture
CREATE TRIGGER trgau_ouverture_maj_t_tranchee
AFTER UPDATE 
ON ouverture
FOR EACH ROW
WHEN (NEW.typouvert = 'tranchée')  AND NOT ST_Equals(NEW.geometry, OLD.geometry) -- ajout d'une condition supplémentaire
BEGIN
    UPDATE t_tranchee
    SET surface = NEW.surface
    WHERE NEW.numouvert = numtr;
    
    UPDATE t_tranchee
    SET geometry = NEW.geometry
    WHERE NEW.numouvert = numtr ;
END ;

-- AFTER DELETE
-- inutile, voir supra 


-- les triggers à mettre en place pour la table t_sondage (qui doivent mettre à jour la table ouverture)
-- AFTER UPDATE
CREATE TRIGGER trgau_t_sondage_maj_ouverture 
   AFTER UPDATE 
   ON t_sondage 
FOR EACH ROW 
WHEN NOT ST_Equals(NEW.geometry, OLD.geometry) -- ajout d'une condition supplémentaire
BEGIN 
UPDATE ouverture 
SET geometry = NEW.geometry 
WHERE NEW.numsd = numouvert AND typouvert = 'sondage' ; 
UPDATE ouverture
SET surface = NEW.surface
WHERE NEW.numsd = numouvert AND typouvert = 'sondage' ;
END ; 

-- AFTER DELETE
-- inutile, voir supra


-- les triggers à mettre en place pour la table t_tranchee (qui doivent mettre à jour la table ouverture)
-- AFTER UPDATE
CREATE TRIGGER trgau_t_tranchee_maj_ouverture 
   AFTER UPDATE 
   ON t_tranchee 
FOR EACH ROW 
WHEN NOT ST_Equals(NEW.geometry, OLD.geometry) -- ajout d'une condition supplémentaire
BEGIN 
UPDATE ouverture 
SET geometry = NEW.geometry 
WHERE NEW.numtr = numouvert AND typouvert = 'tranchée' ; 
UPDATE ouverture
SET surface =  NEW.surface
WHERE NEW.numtr = numouvert AND typouvert = 'tranchée' ;
END ; 

-- AFTER DELETE
-- inutile, voir supra


-- les triggers à mettre en place pour la table axe (qui doivent mettre à jour la tables t_axe)

-- AFTER INSERT
-- qui met à jour la table t_axe après CREATION d'une entité dans axe
CREATE TRIGGER trgai_axe_maj_t_axe 
    AFTER INSERT 
    ON axe 
FOR EACH ROW 
BEGIN 
UPDATE t_axe 
SET geometry = NEW.geometry 
WHERE NEW.numaxe = numaxe ; 
UPDATE t_axe
SET alti = NEW.z_axe
WHERE NEW.numaxe = numaxe ;
END ; 

-- AFTER UPDATE
-- qui met à jour la table t_axe après MODIFICATION d'une entité dans axe
CREATE TRIGGER trgau_axe_maj_t_axe
   AFTER UPDATE 
   ON axe
FOR EACH ROW
WHEN OLD.geometry IS NOT NEW.geometry -- ajout d'une condition supplémentaire
BEGIN
UPDATE t_axe
SET geometry = NEW.geometry
WHERE NEW.numaxe = numaxe ;
UPDATE t_axe
SET alti = NEW.z_axe
WHERE NEW.numaxe = numaxe ;
END ;

-- AFTER DELETE
-- inutile, voir supra

-- les triggers à mettre en place pour la table t_axe (qui doivent mettre à jour la table axe)
-- AFTER UPDATE
CREATE TRIGGER trgau_t_axe_maj_axe 
   AFTER UPDATE 
   ON t_axe 
FOR EACH ROW 
WHEN OLD.geometry IS NOT NEW.geometry -- ajout d'une condition supplémentaire
BEGIN 
UPDATE axe 
SET geometry = NEW.geometry 
WHERE NEW.numaxe = numaxe ; 
UPDATE axe 
SET z_axe = NEW.alti 
WHERE NEW.numaxe = numaxe ;
END ; 

-- AFTER DELETE
-- inutile, voir supra 


-- les triggers à mettre en place pour la table point (qui doivent mettre à jour la tables t_mobilier)

-- AFTER INSERT
-- qui met à jour la table t_mobilier après CREATION d'une entité dans point
CREATE TRIGGER trgai_point_maj_t_mobilier 
    AFTER INSERT 
    ON point
FOR EACH ROW 
BEGIN 
UPDATE t_mobilier 
SET geometry = NEW.geometry, "alti" = NEW."z_point" 
WHERE NEW.numpoint = numpoint ; 
END ; 

-- AFTER UPDATE
-- qui met à jour la table t_mobilier après MODIFICATION d'une entité dans point
CREATE TRIGGER trgau_point_maj_t_mobilier
   AFTER UPDATE 
   ON point
FOR EACH ROW
WHEN NOT ST_Equals(NEW.geometry, OLD.geometry) -- ajout d'une condition supplémentaire
BEGIN
UPDATE t_mobilier
SET geometry = NEW.geometry, "alti" = NEW."z_point" 
WHERE NEW.numpoint = numpoint ;
END ;

-- AFTER DELETE
-- inutile, voir supra

-- les triggers à mettre en place pour la table t_point (qui doivent mettre à jour la table point)
-- AFTER UPDATE
CREATE TRIGGER trgau_t_mobilier_maj_point 
   AFTER UPDATE 
   ON t_mobilier 
FOR EACH ROW 
WHEN NOT ST_Equals(NEW.geometry, OLD.geometry) -- ajout d'une condition supplémentaire
BEGIN 
UPDATE point 
SET geometry = NEW.geometry 
WHERE NEW.numpoint = numpoint ; 
END ; 

-- AFTER DELETE
-- inutile, voir supra

-- les triggers à mettre en place pour la table log (qui doivent mettre à jour la tables t_log)

-- AFTER INSERT
-- qui met à jour la table t_log après CREATION d'une entité dans log
CREATE TRIGGER trgai_log_maj_t_log 
    AFTER INSERT 
    ON log
FOR EACH ROW 
BEGIN 
UPDATE t_log 
SET geometry = NEW.geometry 
WHERE NEW.numlog = numlog ;
UPDATE t_log 
SET alti = NEW.alti 
WHERE NEW.numlog = numlog ; 
END ; 

-- AFTER UPDATE
-- qui met à jour la table t_log après MODIFICATION d'une entité dans log
CREATE TRIGGER trgau_log_maj_t_log
   AFTER UPDATE 
   ON log
FOR EACH ROW
WHEN NOT ST_Equals(NEW.geometry, OLD.geometry) -- ajout d'une condition supplémentaire
BEGIN
UPDATE t_log
SET geometry = NEW.geometry
WHERE NEW.numlog = numlog ;
END ;

-- AFTER DELETE
-- inutile, voir supra

-- les triggers à mettre en place pour la table t_log (qui doivent mettre à jour la table plog)
-- AFTER UPDATE
CREATE TRIGGER trgau_t_log_maj_log 
   AFTER UPDATE 
   ON t_log 
FOR EACH ROW 
WHEN NOT ST_Equals(NEW.geometry, OLD.geometry) -- ajout d'une condition supplémentaire
BEGIN 
UPDATE log 
SET geometry = NEW.geometry 
WHERE NEW.numlog = numlog ;
UPDATE log 
SET alti = NEW.alti 
WHERE NEW.numlog = numlog ; 
END ; 

-- AFTER DELETE
-- inutile, voir supra

 /*les triggers qui vont doubler les relations horizontales entre les US
-- après la création d'une relation entre US
CREATE TRIGGER trgai_j_rel_us
   AFTER INSERT
   ON j_rel_us
FOR EACH ROW
WHEN (NEW."typrel" like 'égale' OR NEW."typrel" like 'équivalente' OR NEW."typrel" like 'synchrone')
BEGIN
   INSERT INTO j_rel_us ("numus1","numus2","typrel","incert") VALUES (NEW."numus2", NEW."numus1", NEW."typrel", NEW."incert");
END;
*/

-- après la mise à jour d'une relation entre US. ATTENTION : ce trigger est passé en commentaire car au final il semble redondant avec le INSERT (à voir...)
/*CREATE TRIGGER trgau_j_rel_us
   AFTER UPDATE
   ON j_rel_us
FOR EACH ROW
WHEN (NEW."jru_typrel" like 'égale' OR NEW."jru_typrel" like 'équivalente' OR NEW."jru_typrel" like 'synchrone')
BEGIN
   INSERT INTO j_rel_us ("jru_us1","jru_us2","jru_typrel","jru_incert") VALUES (NEW."jru_us2", NEW."jru_us1", NEW."jru_typrel", NEW."jru_incert");
end;*/

/*
-- après la suppression d'une relation entre US
CREATE TRIGGER trgad_j_rel_us 
   AFTER DELETE 
   ON j_rel_us 
FOR EACH ROW 
WHEN (OLD."typrel" like 'égale' OR OLD."typrel" like 'équivalente' OR OLD."typrel" like 'synchrone') 
BEGIN 
DELETE FROM j_rel_us 
WHERE ("numus1" = OLD."numus2" and "numus2" = OLD."numus1" and "typrel" = OLD."typrel" and "incert" = OLD."incert"); 
END ; 
*/

--------
--Ensemble de triggers qui calculent les valeurs des profondeurs et épaisseurs pour le dessin automatique des logs stratigraphiques
--------

-- Trigger pour calculer l'épaisseur après l'insertion d'une nouvelle entrée
CREATE TRIGGER trai_calcul_epaisseur_us_log AFTER INSERT ON j_us_log
BEGIN
    UPDATE j_us_log
    SET epais_uslog = NEW.prof_base - NEW.prof_toit
    WHERE id_us_log = NEW.id_us_log;
END;


-- Trigger pour recalculer l'épaisseur après une mise à jour de prof_toit ou prof_base
CREATE TRIGGER trau_calcul_epaisseur_us_log AFTER UPDATE ON j_us_log
BEGIN
    UPDATE j_us_log
    SET epais_uslog = NEW.prof_base - NEW.prof_toit
    WHERE id_us_log = NEW.id_us_log;
END;

-- Trigger pour calculer zmax_uslog après l'insertion d'une nouvelle entrée
CREATE TRIGGER trai_calcul_zmax_us_log AFTER INSERT ON j_us_log
WHEN NEW.prof_toit IS NOT NULL
BEGIN
    UPDATE j_us_log
    SET zmax_uslog = (SELECT alti FROM t_log WHERE t_log.numlog = NEW.numlog) - NEW.prof_toit
    WHERE id_us_log = NEW.id_us_log;
END;

-- Trigger pour recalculer zmax_uslog après une mise à jour de prof_toit
CREATE TRIGGER trau_calcul_zmax_us_log AFTER UPDATE OF prof_toit ON j_us_log
WHEN NEW.prof_toit IS NOT NULL
BEGIN
    UPDATE j_us_log
    SET zmax_uslog = (SELECT alti FROM t_log WHERE t_log.numlog = NEW.numlog) - NEW.prof_toit
    WHERE id_us_log = NEW.id_us_log;
END;

-- Trigger pour recalculer zmax_uslog après une mise à jour de alti dans t_log
CREATE TRIGGER trau_calcul_zmax_us_log_alti
AFTER UPDATE OF alti ON t_log
BEGIN
    UPDATE j_us_log
    SET zmax_uslog = (SELECT t_log.alti FROM t_log WHERE t_log.numlog = j_us_log.numlog) - j_us_log.prof_toit
    WHERE j_us_log.numlog = NEW.numlog;
END;


-- Trigger pour calculer zmin_uslog après l'insertion d'une nouvelle entrée
CREATE TRIGGER trai_calcul_zmin_us_log AFTER INSERT ON j_us_log
WHEN NEW.prof_base IS NOT NULL
BEGIN
    UPDATE j_us_log
    SET zmin_uslog = (SELECT alti FROM t_log WHERE t_log.numlog = NEW.numlog) - NEW.prof_base
    WHERE id_us_log = NEW.id_us_log;
END;

-- Trigger pour recalculer zmin_uslog après une mise à jour de prof_base
CREATE TRIGGER trau_calcul_zmin_us_log AFTER UPDATE OF prof_base ON j_us_log
WHEN NEW.prof_base IS NOT NULL
BEGIN
    UPDATE j_us_log
    SET zmin_uslog = (SELECT alti FROM t_log WHERE t_log.numlog = NEW.numlog) - NEW.prof_base
    WHERE id_us_log = NEW.id_us_log;
END;

-- Trigger pour recalculer zmin_uslog après une mise à jour de alti dans t_log
CREATE TRIGGER trau_calcul_zmin_us_log_alti
AFTER UPDATE OF alti ON t_log
BEGIN
    UPDATE j_us_log
    SET zmin_uslog = (SELECT t_log.alti FROM t_log WHERE t_log.numlog = j_us_log.numlog) - j_us_log.prof_base
    WHERE j_us_log.numlog = NEW.numlog;
END;

-- Trigger pour automatiquement remplir prof_toit pour les nouvelles entrées
CREATE TRIGGER trai_auto_prof_toit AFTER INSERT ON j_us_log
BEGIN
    UPDATE j_us_log
    SET prof_toit = (
        SELECT COALESCE(MAX(prof_base), 0) 
        FROM j_us_log AS prev_us_log 
        WHERE prev_us_log.numlog = NEW.numlog AND prev_us_log.id_us_log < NEW.id_us_log
    )
    WHERE id_us_log = NEW.id_us_log;
END;

-- Trigger pour automatiquement mettre à jour prof_toit après la mise à jour de prof_base
CREATE TRIGGER trau_auto_prof_toit
AFTER UPDATE OF prof_base ON j_us_log
BEGIN
    UPDATE j_us_log
    SET prof_toit = NEW.prof_base
    WHERE numlog = NEW.numlog 
    AND id_us_log = (
        SELECT MIN(id_us_log)
        FROM j_us_log
        WHERE numlog = NEW.numlog 
        AND id_us_log > NEW.id_us_log
    );
END;

-- Trigger pour automatiquement mettre à jour prof_base de l'US précédente après la mise à jour de prof_toit de l'US suivante
CREATE TRIGGER trau_update_prev_prof_base
AFTER UPDATE OF prof_toit ON j_us_log
BEGIN
    UPDATE j_us_log
    SET prof_base = NEW.prof_toit
    WHERE numlog = NEW.numlog
    AND id_us_log = (
        SELECT MAX(id_us_log) 
        FROM j_us_log 
        WHERE numlog = NEW.numlog 
        AND id_us_log < NEW.id_us_log
    );
END;


-- Trigger pour calculer l'altitude minimale du log après l'insertion d'une nouvelle entrée
CREATE TRIGGER trai_calcul_zmin_log AFTER INSERT ON t_log
BEGIN
    UPDATE t_log
    SET zmin_log = NEW.alti - NEW.prof_log
    WHERE id_log = NEW.id_log;
END;


-- Trigger pour recalculer l'altitude minimale du log après une mise à jour de prof_log
CREATE TRIGGER trau_calcul_zmin_log AFTER UPDATE ON t_log
BEGIN
    UPDATE t_log
    SET zmin_log = NEW.alti - NEW.prof_log
    WHERE id_log = NEW.id_log;
END;

-----TRIGGERS DE REMPLISSAGE DE T_PHOTO ET J_REL_PHOTO-----

/*Les triggers d'insertion des entités mères dans la table j_rel_photo*/

--AFTER INSERT
--Pour les tranchées
CREATE TRIGGER tgai_remplir_j_rel_photo_tr
AFTER INSERT ON traitement_photo
BEGIN
    -- Utiliser la requête récursive pour extraire les valeurs et insérer dans j_rel_photo
    INSERT INTO j_rel_photo (numphoto,numtr)
    WITH RECURSIVE Integers AS (
        SELECT
            NEW."numphoto",
            CAST(NULL AS INTEGER) AS int_value,
            NEW."liste_tranchee" || '/' AS remaining_string
        UNION ALL
        SELECT
            NEW."numphoto",
            CAST(substr(remaining_string, 1, instr(remaining_string, '/') - 1) AS INTEGER),
            substr(remaining_string, instr(remaining_string, '/') + 1)
        FROM
            Integers
        WHERE
            remaining_string <> ''
    )
    SELECT
        "numphoto",
        "int_value" AS numtr
    FROM
        Integers
    WHERE
        int_value IS NOT NULL;
END;

--Pour les sondages
CREATE TRIGGER tgai_remplir_j_rel_photo_sd
AFTER INSERT ON traitement_photo
BEGIN
    -- Utiliser la requête récursive pour extraire les valeurs et insérer dans j_rel_photo
    INSERT INTO j_rel_photo (numphoto,numsd)
    WITH RECURSIVE Integers AS (
        SELECT
            NEW."numphoto",
            CAST(NULL AS INTEGER) AS int_value,
            NEW."liste_sd" || '/' AS remaining_string
        UNION ALL
        SELECT
            NEW."numphoto",
            CAST(substr(remaining_string, 1, instr(remaining_string, '/') - 1) AS INTEGER),
            substr(remaining_string, instr(remaining_string, '/') + 1)
        FROM
            Integers
        WHERE
            remaining_string <> ''
    )
    SELECT
        "numphoto",
        "int_value" AS numsd
    FROM
        Integers
    WHERE
        int_value IS NOT NULL;
END;

--Pour les ensembles
CREATE TRIGGER tgai_remplir_j_rel_photo_ens
AFTER INSERT ON traitement_photo
BEGIN
    -- Utiliser la requête récursive pour extraire les valeurs et insérer dans j_rel_photo
    INSERT INTO j_rel_photo (numphoto,numens)
    WITH RECURSIVE Integers AS (
        SELECT
            NEW."numphoto",
            CAST(NULL AS INTEGER) AS int_value,
            NEW."liste_ens" || '/' AS remaining_string
        UNION ALL
        SELECT
            NEW."numphoto",
            CAST(substr(remaining_string, 1, instr(remaining_string, '/') - 1) AS INTEGER),
            substr(remaining_string, instr(remaining_string, '/') + 1)
        FROM
            Integers
        WHERE
            remaining_string <> ''
    )
    SELECT
        "numphoto",
        "int_value" AS numens
    FROM
        Integers
    WHERE
        int_value IS NOT NULL;
END;

--Pour les faits
CREATE TRIGGER tgai_remplir_j_rel_photo_fait
AFTER INSERT ON traitement_photo
BEGIN
    -- Utiliser la requête récursive pour extraire les valeurs et insérer dans j_rel_photo
    INSERT INTO j_rel_photo (numphoto,numfait)
    WITH RECURSIVE Integers AS (
        SELECT
            NEW."numphoto",
            CAST(NULL AS INTEGER) AS int_value,
            NEW."liste_fait" || '/' AS remaining_string
        UNION ALL
        SELECT
            NEW."numphoto",
            CAST(substr(remaining_string, 1, instr(remaining_string, '/') - 1) AS INTEGER),
            substr(remaining_string, instr(remaining_string, '/') + 1)
        FROM
            Integers
        WHERE
            remaining_string <> ''
    )
    SELECT
        "numphoto",
        "int_value" AS numfait
    FROM
        Integers
    WHERE
        int_value IS NOT NULL;
END;

--Pour les US
CREATE TRIGGER tgai_remplir_j_rel_photo_us
AFTER INSERT ON traitement_photo
BEGIN
    -- Utiliser la requête récursive pour extraire les valeurs et insérer dans j_rel_photo
    INSERT INTO j_rel_photo (numphoto,numus)
    WITH RECURSIVE Integers AS (
        SELECT
            NEW."numphoto",
            CAST(NULL AS INTEGER) AS int_value,
            NEW."liste_us" || '/' AS remaining_string
        UNION ALL
        SELECT
            NEW."numphoto",
            CAST(substr(remaining_string, 1, instr(remaining_string, '/') - 1) AS INTEGER),
            substr(remaining_string, instr(remaining_string, '/') + 1)
        FROM
            Integers
        WHERE
            remaining_string <> ''
    )
    SELECT
        "numphoto",
        "int_value" AS numus
    FROM
        Integers
    WHERE
        int_value IS NOT NULL;
END;

--Pour les logs
CREATE TRIGGER tgai_remplir_j_rel_photo_log
AFTER INSERT ON traitement_photo
BEGIN
    -- Utiliser la requête récursive pour extraire les valeurs et insérer dans j_rel_photo
    INSERT INTO j_rel_photo (numphoto,numlog)
    WITH RECURSIVE Integers AS (
        SELECT
            NEW."numphoto",
            CAST(NULL AS INTEGER) AS int_value,
            NEW."liste_log" || '/' AS remaining_string
        UNION ALL
        SELECT
            NEW."numphoto",
            CAST(substr(remaining_string, 1, instr(remaining_string, '/') - 1) AS INTEGER),
            substr(remaining_string, instr(remaining_string, '/') + 1)
        FROM
            Integers
        WHERE
            remaining_string <> ''
    )
    SELECT
        "numphoto",
        "int_value" AS numlog
    FROM
        Integers
    WHERE
        int_value IS NOT NULL;
END;

--Pour le mobilier
CREATE TRIGGER tgai_remplir_j_rel_photo_mobilier
AFTER INSERT ON traitement_photo
BEGIN
    -- Utiliser la requête récursive pour extraire les valeurs et insérer dans j_rel_photo
    INSERT INTO j_rel_photo (numphoto,id_mob)
    WITH RECURSIVE Integers AS (
        SELECT
            NEW."numphoto",
            CAST(NULL AS INTEGER) AS int_value,
            NEW."liste_mobilier" || '/' AS remaining_string
        UNION ALL
        SELECT
            NEW."numphoto",
            CAST(substr(remaining_string, 1, instr(remaining_string, '/') - 1) AS INTEGER),
            substr(remaining_string, instr(remaining_string, '/') + 1)
        FROM
            Integers
        WHERE
            remaining_string <> ''
    )
    SELECT
        "numphoto",
        "int_value" AS id_mob
    FROM
        Integers
    WHERE
        int_value IS NOT NULL;
END;

--Pour les axes
CREATE TRIGGER tgai_remplir_j_rel_photo_axe
AFTER INSERT ON traitement_photo
BEGIN
    -- Utiliser la requête récursive pour extraire les valeurs et insérer dans j_rel_photo
    INSERT INTO j_rel_photo (numphoto,numaxe)
    WITH RECURSIVE Integers AS (
        SELECT
            NEW."numphoto",
            CAST(NULL AS INTEGER) AS int_value,
            NEW."liste_axe" || '/' AS remaining_string
        UNION ALL
        SELECT
            NEW."numphoto",
            CAST(substr(remaining_string, 1, instr(remaining_string, '/') - 1) AS INTEGER),
            substr(remaining_string, instr(remaining_string, '/') + 1)
        FROM
            Integers
        WHERE
            remaining_string <> ''
    )
    SELECT
        "numphoto",
        "int_value" AS numaxe
    FROM
        Integers
    WHERE
        int_value IS NOT NULL;
END;

--Pour les modèles de photogrammétrie
CREATE TRIGGER tgai_remplir_j_rel_photo_photogram
AFTER INSERT ON traitement_photo
BEGIN
    -- Utiliser la requête récursive pour extraire les valeurs et insérer dans j_rel_photo
    INSERT INTO j_rel_photo (numphoto,id_photogram)
    WITH RECURSIVE Integers AS (
        SELECT
            NEW."numphoto",
            CAST(NULL AS INTEGER) AS int_value,
            NEW."liste_photogram" || '/' AS remaining_string
        UNION ALL
        SELECT
            NEW."numphoto",
            CAST(substr(remaining_string, 1, instr(remaining_string, '/') - 1) AS INTEGER),
            substr(remaining_string, instr(remaining_string, '/') + 1)
        FROM
            Integers
        WHERE
            remaining_string <> ''
    )
    SELECT
        "numphoto",
        "int_value" AS id_photogram
    FROM
        Integers
    WHERE
        int_value IS NOT NULL;
END;

--AFTER UPDATE
--pour les tranchées
CREATE TRIGGER tgau_maj_j_rel_photo_tr
AFTER UPDATE OF liste_tranchee ON traitement_photo
FOR EACH ROW
BEGIN
    -- Supprimer les enregistrements correspondants dans j_rel_photo
    DELETE FROM j_rel_photo WHERE numphoto = NEW.numphoto AND "numtr" IS NOT NULL;

    -- Utiliser la requête récursive pour extraire les nouvelles valeurs et insérer dans j_rel_photo
    INSERT INTO j_rel_photo (numphoto,numtr)
    WITH RECURSIVE Integers AS (
        SELECT
            NEW."numphoto",
            CAST(NULL AS INTEGER) AS int_value,
            NEW."liste_tranchee" || '/' AS remaining_string
        UNION ALL
        SELECT
            NEW."numphoto",
            CAST(substr(remaining_string, 1, instr(remaining_string, '/') - 1) AS INTEGER),
            substr(remaining_string, instr(remaining_string, '/') + 1)
        FROM
            Integers
        WHERE
            remaining_string <> ''
    )
    SELECT
        "numphoto",
        "int_value" AS numtr
    FROM
        Integers
    WHERE
        int_value IS NOT NULL;
END;

--pour les sondages
CREATE TRIGGER tgau_maj_j_rel_photo_sd
AFTER UPDATE OF liste_sd ON traitement_photo
FOR EACH ROW
BEGIN
    -- Supprimer les enregistrements correspondants dans j_rel_photo
    DELETE FROM j_rel_photo WHERE numphoto = NEW.numphoto AND "numsd" IS NOT NULL;

    -- Utiliser la requête récursive pour extraire les nouvelles valeurs et insérer dans j_rel_photo
    INSERT INTO j_rel_photo (numphoto,numsd)
    WITH RECURSIVE Integers AS (
        SELECT
            NEW."numphoto",
            CAST(NULL AS INTEGER) AS int_value,
            NEW."liste_sd" || '/' AS remaining_string
        UNION ALL
        SELECT
            NEW."numphoto",
            CAST(substr(remaining_string, 1, instr(remaining_string, '/') - 1) AS INTEGER),
            substr(remaining_string, instr(remaining_string, '/') + 1)
        FROM
            Integers
        WHERE
            remaining_string <> ''
    )
    SELECT
        "numphoto",
        "int_value" AS numsd
    FROM
        Integers
    WHERE
        int_value IS NOT NULL;
END;

--pour les ensembles
CREATE TRIGGER tgau_maj_j_rel_photo_ens
AFTER UPDATE OF liste_ens ON traitement_photo
FOR EACH ROW
BEGIN
    -- Supprimer les enregistrements correspondants dans j_rel_photo
    DELETE FROM j_rel_photo WHERE numphoto = NEW.numphoto AND "numens" IS NOT NULL;

    -- Utiliser la requête récursive pour extraire les nouvelles valeurs et insérer dans j_rel_photo
    INSERT INTO j_rel_photo (numphoto,numens)
    WITH RECURSIVE Integers AS (
        SELECT
            NEW."numphoto",
            CAST(NULL AS INTEGER) AS int_value,
            NEW."liste_ens" || '/' AS remaining_string
        UNION ALL
        SELECT
            NEW."numphoto",
            CAST(substr(remaining_string, 1, instr(remaining_string, '/') - 1) AS INTEGER),
            substr(remaining_string, instr(remaining_string, '/') + 1)
        FROM
            Integers
        WHERE
            remaining_string <> ''
    )
    SELECT
        "numphoto",
        "int_value" AS numens
    FROM
        Integers
    WHERE
        int_value IS NOT NULL;
END;

--pour les faits
CREATE TRIGGER tgau_maj_j_rel_photo_fait
AFTER UPDATE OF liste_fait ON traitement_photo
FOR EACH ROW
BEGIN
    -- Supprimer les enregistrements correspondants dans j_rel_photo
    DELETE FROM j_rel_photo WHERE numphoto = NEW.numphoto AND "numfait" IS NOT NULL;

    -- Utiliser la requête récursive pour extraire les nouvelles valeurs et insérer dans j_rel_photo
    INSERT INTO j_rel_photo (numphoto,numfait)
    WITH RECURSIVE Integers AS (
        SELECT
            NEW."numphoto",
            CAST(NULL AS INTEGER) AS int_value,
            NEW."liste_fait" || '/' AS remaining_string
        UNION ALL
        SELECT
            NEW."numphoto",
            CAST(substr(remaining_string, 1, instr(remaining_string, '/') - 1) AS INTEGER),
            substr(remaining_string, instr(remaining_string, '/') + 1)
        FROM
            Integers
        WHERE
            remaining_string <> ''
    )
    SELECT
        "numphoto",
        "int_value" AS numfait
    FROM
        Integers
    WHERE
        int_value IS NOT NULL;
END;

--pour les US
CREATE TRIGGER tgau_maj_j_rel_photo_us
AFTER UPDATE OF liste_us ON traitement_photo
FOR EACH ROW
BEGIN
    -- Supprimer les enregistrements correspondants dans j_rel_photo
    DELETE FROM j_rel_photo WHERE numphoto = NEW.numphoto AND "numus" IS NOT NULL;

    -- Utiliser la requête récursive pour extraire les nouvelles valeurs et insérer dans j_rel_photo
    INSERT INTO j_rel_photo (numphoto,numus)
    WITH RECURSIVE Integers AS (
        SELECT
            NEW."numphoto",
            CAST(NULL AS INTEGER) AS int_value,
            NEW."liste_us" || '/' AS remaining_string
        UNION ALL
        SELECT
            NEW."numphoto",
            CAST(substr(remaining_string, 1, instr(remaining_string, '/') - 1) AS INTEGER),
            substr(remaining_string, instr(remaining_string, '/') + 1)
        FROM
            Integers
        WHERE
            remaining_string <> ''
    )
    SELECT
        "numphoto",
        "int_value" AS numus
    FROM
        Integers
    WHERE
        int_value IS NOT NULL;
END;

--pour les logs
CREATE TRIGGER tgau_maj_j_rel_photo_log
AFTER UPDATE OF liste_log ON traitement_photo
FOR EACH ROW
BEGIN
    -- Supprimer les enregistrements correspondants dans j_rel_photo
    DELETE FROM j_rel_photo WHERE numphoto = NEW.numphoto AND "numlog" IS NOT NULL;

    -- Utiliser la requête récursive pour extraire les nouvelles valeurs et insérer dans j_rel_photo
    INSERT INTO j_rel_photo (numphoto,numlog)
    WITH RECURSIVE Integers AS (
        SELECT
            NEW."numphoto",
            CAST(NULL AS INTEGER) AS int_value,
            NEW."liste_log" || '/' AS remaining_string
        UNION ALL
        SELECT
            NEW."numphoto",
            CAST(substr(remaining_string, 1, instr(remaining_string, '/') - 1) AS INTEGER),
            substr(remaining_string, instr(remaining_string, '/') + 1)
        FROM
            Integers
        WHERE
            remaining_string <> ''
    )
    SELECT
        "numphoto",
        "int_value" AS numlog
    FROM
        Integers
    WHERE
        int_value IS NOT NULL;
END;

--pour le mobilier
CREATE TRIGGER tgau_maj_j_rel_photo_mobilier
AFTER UPDATE OF liste_mobilier ON traitement_photo
FOR EACH ROW
BEGIN
    -- Supprimer les enregistrements correspondants dans j_rel_photo
    DELETE FROM j_rel_photo WHERE numphoto = NEW.numphoto AND "id_mob" IS NOT NULL;

    -- Utiliser la requête récursive pour extraire les nouvelles valeurs et insérer dans j_rel_photo
    INSERT INTO j_rel_photo (numphoto,id_mob)
    WITH RECURSIVE Integers AS (
        SELECT
            NEW."numphoto",
            CAST(NULL AS INTEGER) AS int_value,
            NEW."liste_mobilier" || '/' AS remaining_string
        UNION ALL
        SELECT
            NEW."numphoto",
            CAST(substr(remaining_string, 1, instr(remaining_string, '/') - 1) AS INTEGER),
            substr(remaining_string, instr(remaining_string, '/') + 1)
        FROM
            Integers
        WHERE
            remaining_string <> ''
    )
    SELECT
        "numphoto",
        "int_value" AS id_mob
    FROM
        Integers
    WHERE
        int_value IS NOT NULL;
END;

--pour les axes
CREATE TRIGGER tgau_maj_j_rel_photo_axe
AFTER UPDATE OF liste_axe ON traitement_photo
FOR EACH ROW
BEGIN
    -- Supprimer les enregistrements correspondants dans j_rel_photo
    DELETE FROM j_rel_photo WHERE numphoto = NEW.numphoto AND "numaxe" IS NOT NULL;

    -- Utiliser la requête récursive pour extraire les nouvelles valeurs et insérer dans j_rel_photo
    INSERT INTO j_rel_photo (numphoto,numaxe)
    WITH RECURSIVE Integers AS (
        SELECT
            NEW."numphoto",
            CAST(NULL AS INTEGER) AS int_value,
            NEW."liste_axe" || '/' AS remaining_string
        UNION ALL
        SELECT
            NEW."numphoto",
            CAST(substr(remaining_string, 1, instr(remaining_string, '/') - 1) AS INTEGER),
            substr(remaining_string, instr(remaining_string, '/') + 1)
        FROM
            Integers
        WHERE
            remaining_string <> ''
    )
    SELECT
        "numphoto",
        "int_value" AS numaxe
    FROM
        Integers
    WHERE
        int_value IS NOT NULL;
END;

--pour les modèles de photogrammétrie
CREATE TRIGGER tgau_maj_j_rel_photo_photogram
AFTER UPDATE OF liste_photogram ON traitement_photo
FOR EACH ROW
BEGIN
    -- Supprimer les enregistrements correspondants dans j_rel_photo
    DELETE FROM j_rel_photo WHERE numphoto = NEW.numphoto AND "id_photogram" IS NOT NULL;

    -- Utiliser la requête récursive pour extraire les nouvelles valeurs et insérer dans j_rel_photo
    INSERT INTO j_rel_photo (numphoto,id_photogram)
    WITH RECURSIVE Integers AS (
        SELECT
            NEW."numphoto",
            CAST(NULL AS INTEGER) AS int_value,
            NEW."liste_photogram" || '/' AS remaining_string
        UNION ALL
        SELECT
            NEW."numphoto",
            CAST(substr(remaining_string, 1, instr(remaining_string, '/') - 1) AS INTEGER),
            substr(remaining_string, instr(remaining_string, '/') + 1)
        FROM
            Integers
        WHERE
            remaining_string <> ''
    )
    SELECT
        "numphoto",
        "int_value" AS id_photogram
    FROM
        Integers
    WHERE
        int_value IS NOT NULL;
END;

--Trigger de mise à jour de la table t_photo d'après les descriptions saisies dans traitement photo (AFTER INSERT et AFTER UPDATE)
--AFTER INSERT
DROP TRIGGER IF EXISTS tgai_maj_t_photo_f_traitphoto;
CREATE TRIGGER tgai_maj_t_photo_f_traitphoto
AFTER INSERT ON traitement_photo
FOR EACH ROW
BEGIN
    INSERT INTO t_photo (numphoto, nomfichier, legend, chemin_url, vue_vers, creator, datephoto, type, support, destination, note)
    VALUES (NEW.numphoto, NEW.nomfichier, NEW.legend, NEW.chemin_url, NEW.vue_vers, NEW.creator, NEW.datephoto, NEW.type, NEW.support, NEW.destination, NEW.note);
END;

--AFTER UPDATE	
DROP TRIGGER IF EXISTS tgau_maj_t_photo_f_traitphoto;
CREATE TRIGGER tgau_maj_t_photo_f_traitphoto
AFTER UPDATE ON traitement_photo
FOR EACH ROW
BEGIN 
    UPDATE t_photo 
    SET "nomfichier" = NEW."nomfichier",
        "legend" = NEW."legend",
		"chemin_url" = NEW."chemin_url",
		"vue_vers" = NEW."vue_vers",
		"creator" = NEW."creator",
		"datephoto" = NEW."datephoto",
		"type" = NEW."type",
		"support" = NEW."support",
		"destination" = NEW."destination",
		"note" = NEW."note"
    WHERE numphoto = NEW.numphoto;
END;

------------------------------------------------------------------------------
-- TRIGGERS DE GESTION DES NUMEROS DE FAITS ENTRE T_US ET LES DONNEES EN COUPE
------------------------------------------------------------------------------

-- Trigger pour mettre à jour le numéro de fait dans les tables coupe_line et coupe_poly après l'insertion d'une nouvelle US
CREATE TRIGGER insert_numfait_coupe
AFTER INSERT ON t_us
FOR EACH ROW
BEGIN
    UPDATE coupe_line 
    SET numfait = NEW.numfait 
    WHERE numus = NEW.numus;

    UPDATE coupe_poly 
    SET numfait = NEW.numfait 
    WHERE numus = NEW.numus;
END;

-- Trigger pour mettre à jour le numéro de fait dans les tables coupe_line et coupe_poly après la mise à jour d'une US
CREATE TRIGGER update_numfait_coupe
AFTER UPDATE ON t_us
FOR EACH ROW
BEGIN
    UPDATE coupe_line 
    SET numfait = NEW.numfait 
    WHERE numus = NEW.numus;

    UPDATE coupe_poly 
    SET numfait = NEW.numfait 
    WHERE numus = NEW.numus;
END;

-- Trigger pour mettre à jour le numéro de fait dans les tables coupe_line et coupe_poly après l'insertion d'une nouvelle ligne ou polygone de coupe (au cas où le numéro de fait n'aurait pas été renseigné dans t_us au moment de la création de la ligne ou du polygone)
CREATE TRIGGER insert_numfait_coupe_line
AFTER INSERT ON coupe_line
FOR EACH ROW
BEGIN
    UPDATE coupe_line 
    SET numfait = (SELECT numfait FROM t_us WHERE numus = NEW.numus)
    WHERE numus = NEW.numus;
END;

CREATE TRIGGER insert_numfait_coupe_poly
AFTER INSERT ON coupe_poly
FOR EACH ROW
BEGIN
    UPDATE coupe_poly 
    SET numfait = (SELECT numfait FROM t_us WHERE numus = NEW.numus)
    WHERE numus = NEW.numus;
END;

-- Trigger pour mettre à jour le numéro de fait dans les tables coupe_line et coupe_poly après la mise à jour du numéro d'US dans ces tables (au cas où le numus aurait été modifié après la création de la ligne ou du polygone)
-- Note : le trigger se déclenche uniquement sur UPDATE OF numus pour éviter toute récursion (si on écoutait UPDATE complet, le trigger se redéclencherait lui-même en mettant à jour numfait)
CREATE TRIGGER update_numfait_coupe_line
AFTER UPDATE OF numus ON coupe_line
FOR EACH ROW
BEGIN
    UPDATE coupe_line
    SET numfait = (SELECT numfait FROM t_us WHERE numus = NEW.numus)
    WHERE rowid = NEW.rowid;
END;

CREATE TRIGGER update_numfait_coupe_poly
AFTER UPDATE OF numus ON coupe_poly
FOR EACH ROW
BEGIN
    UPDATE coupe_poly
    SET numfait = (SELECT numfait FROM t_us WHERE numus = NEW.numus)
    WHERE rowid = NEW.rowid;
END;

/*
--------
--Triggers de création automatique des entités mères depuis la table fille, tgbi signifie trigger before insert, m_ signifie mère et signale la table mère, f_ signifie fille et signale la table fille
--tous les triggers n'ont pas été créés, seuls ceux qui peuvent poser un problème de saisie ont été ajouté
--------

--Entre coupe_axe et t_minute
CREATE TRIGGER tgbi_m_t_minute_f_coupe_axe
BEFORE INSERT ON coupe_axe
FOR EACH ROW
BEGIN
    -- Vérifie si le "numinute" existe dans t_minute
    -- Si ce n'est pas le cas, ajoute une nouvelle entité dans t_minute
    INSERT OR IGNORE INTO t_minute (numinute) SELECT NEW.numinute WHERE NEW.numinute IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_minute WHERE numinute = NEW.numinute);
END;

--Entre coupe_axe et t_sondage
CREATE TRIGGER tgbi_m_t_sondage_f_coupe_axe
BEFORE INSERT ON coupe_axe
FOR EACH ROW
BEGIN
    -- Vérifie si le "numsd" existe dans t_sondage
    -- Si ce n'est pas le cas, ajoute une nouvelle entité dans t_sondage
    INSERT OR IGNORE INTO t_sondage (numsd) SELECT NEW.numsd WHERE NEW.numsd IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_sondage WHERE numsd = NEW.numsd);
END;

--Entre coupe_line et t_fait
CREATE TRIGGER tgbi_m_t_fait_f_coupe_line
BEFORE INSERT ON coupe_line
FOR EACH ROW
BEGIN
    -- Vérifie si le "numfait" existe dans t_fait
    -- Si ce n'est pas le cas, ajoute une nouvelle entité dans t_fait
    INSERT OR IGNORE INTO t_fait (numfait) SELECT NEW.numfait WHERE NEW.numfait IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_fait WHERE numfait = NEW.numfait);
END;

--Entre coupe_line et t_us
CREATE TRIGGER tgbi_m_t_us_f_coupe_line
BEFORE INSERT ON coupe_line
FOR EACH ROW
BEGIN
    -- Vérifie si le "numus" existe dans t_us
    -- Si ce n'est pas le cas, ajoute une nouvelle entité dans t_us
    INSERT OR IGNORE INTO t_us (numus) SELECT NEW.numus WHERE NEW.numus IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_us WHERE numus = NEW.numus);
END;

--Entre coupe_poly et t_fait
CREATE TRIGGER tgbi_m_t_fait_f_coupe_poly
BEFORE INSERT ON coupe_poly
FOR EACH ROW
BEGIN
    -- Vérifie si le "numfait" existe dans t_fait
    -- Si ce n'est pas le cas, ajoute une nouvelle entité dans t_fait
    INSERT OR IGNORE INTO t_fait (numfait) SELECT NEW.numfait WHERE NEW.numfait IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_fait WHERE numfait = NEW.numfait);
END;

--Entre coupe_poly et t_us
CREATE TRIGGER tgbi_m_t_us_f_coupe_poly
BEFORE INSERT ON coupe_poly
FOR EACH ROW
BEGIN
    -- Vérifie si le "numus" existe dans t_us
    -- Si ce n'est pas le cas, ajoute une nouvelle entité dans t_us
    INSERT OR IGNORE INTO t_us (numus) SELECT NEW.numus WHERE NEW.numus IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_us WHERE numus = NEW.numus);
END;

--Entre t_us et t_fait
CREATE TRIGGER tgbi_m_t_fait_f_t_us
BEFORE INSERT ON t_us
FOR EACH ROW
BEGIN
    -- Vérifie si le "numfait" existe dans t_fait
    -- Si ce n'est pas le cas, ajoute une nouvelle entité dans t_fait
    INSERT OR IGNORE INTO t_fait (numfait) SELECT NEW.numfait WHERE NEW.numfait IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_fait WHERE numfait = NEW.numfait);
END;

--Entre t_us et t_seq
CREATE TRIGGER tgbi_m_t_seq_f_t_us
BEFORE INSERT ON t_us
FOR EACH ROW
BEGIN
    -- Vérifie si le "numseq" existe dans t_seq
    -- Si ce n'est pas le cas, ajoute une nouvelle entité dans t_seq
    INSERT OR IGNORE INTO t_seq (numseq) SELECT NEW.num_seq WHERE NEW.num_seq IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_seq WHERE numseq = NEW.num_seq);
END;

--Entre t_sondage et t_tranchee
CREATE TRIGGER tgbi_m_t_tranchee_f_t_sondage
BEFORE INSERT ON t_sondage
FOR EACH ROW
BEGIN
    -- Vérifie si le "numtr" existe dans t_tranchee
    -- Si ce n'est pas le cas, ajoute une nouvelle entité dans t_tranchee
    INSERT OR IGNORE INTO t_tranchee (numtr) SELECT NEW.numtr WHERE NEW.numtr IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_tranchee WHERE numtr = NEW.numtr);
END;

--Entre t_minute et t_photogram
CREATE TRIGGER tgbi_m_t_photogram_f_t_minute
BEFORE INSERT ON t_minute
FOR EACH ROW
BEGIN
    -- Vérifie si le "numinute" existe dans t_minute
    -- Si ce n'est pas le cas, ajoute une nouvelle entité dans t_minute
    INSERT OR IGNORE INTO t_minute (numinute) SELECT NEW.numinute WHERE NEW.numinute IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_minute WHERE numinute = NEW.numinute);
END;

--Entre t_mobilier et t_us
CREATE TRIGGER tgbi_m_t_us_f_t_mobilier
BEFORE INSERT ON t_mobilier
FOR EACH ROW
BEGIN
    -- Vérifie si le "numus" existe dans t_us
    -- Si ce n'est pas le cas, ajoute une nouvelle entité dans t_us
    INSERT OR IGNORE INTO t_us (numus) SELECT NEW.numus WHERE NEW.numus IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_us WHERE numus = NEW.numus);
END;

--Entre t_mobilier et t_fait
CREATE TRIGGER tgbi_m_t_fait_f_t_mobilier
BEFORE INSERT ON t_mobilier
FOR EACH ROW
BEGIN
    -- Vérifie si le "numfait" existe dans t_fait
    -- Si ce n'est pas le cas, ajoute une nouvelle entité dans t_fait
    INSERT OR IGNORE INTO t_fait (numfait) SELECT NEW.numfait WHERE NEW.numfait IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_fait WHERE numfait = NEW.numfait);
END;

--Entre t_log et t_tranchee
CREATE TRIGGER tgbi_m_t_tranchee_f_t_log
BEFORE INSERT ON t_log
FOR EACH ROW
BEGIN
    -- Vérifie si le "numtr" existe dans t_tranchee
    -- Si ce n'est pas le cas, ajoute une nouvelle entité dans t_tranchee
    INSERT OR IGNORE INTO t_tranchee (numtr) SELECT NEW.numtr WHERE NEW.numtr IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_tranchee WHERE numtr = NEW.numtr);
END;

--Entre t_log et t_sondage
CREATE TRIGGER tgbi_m_t_sondage_f_t_log
BEFORE INSERT ON t_log
FOR EACH ROW
BEGIN
    -- Vérifie si le "numsd" existe dans t_sondage
    -- Si ce n'est pas le cas, ajoute une nouvelle entité dans t_sondage
    INSERT OR IGNORE INTO t_sondage (numsd) SELECT NEW.numsd WHERE NEW.numsd IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_sondage WHERE numsd = NEW.numsd);
END;

--Entre t_prelevement et t_us
CREATE TRIGGER tgbi_m_t_us_f_t_prelevement
BEFORE INSERT ON t_prelevement
FOR EACH ROW
BEGIN
    -- Vérifie si le "numus" existe dans t_us
    -- Si ce n'est pas le cas, ajoute une nouvelle entité dans t_us
    INSERT OR IGNORE INTO t_us (numus) SELECT NEW.numus WHERE NEW.numus IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_us WHERE numus = NEW.numus);
END;

--Entre t_prelevement et t_fait
CREATE TRIGGER tgbi_m_t_fait_f_t_prelevement
BEFORE INSERT ON t_prelevement
FOR EACH ROW
BEGIN
    -- Vérifie si le "numfait" existe dans t_fait
    -- Si ce n'est pas le cas, ajoute une nouvelle entité dans t_fait
    INSERT OR IGNORE INTO t_fait (numfait) SELECT NEW.numfait WHERE NEW.numfait IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_fait WHERE numfait = NEW.numfait);
END;

--Entre j_rel_axe et t_fait
CREATE TRIGGER tgbi_m_t_fait_f_j_rel_axe
BEFORE INSERT ON j_rel_axe
FOR EACH ROW
BEGIN
    -- Vérifie si le "numfait" existe dans t_fait
    -- Si ce n'est pas le cas, ajoute une nouvelle entité dans t_fait
    INSERT OR IGNORE INTO t_fait (numfait) SELECT NEW.numfait WHERE NEW.numfait IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_fait WHERE numfait = NEW.numfait);
END;

--Entre j_rel_axe et t_us
CREATE TRIGGER tgbi_m_t_us_f_j_rel_axe
BEFORE INSERT ON j_rel_axe
FOR EACH ROW
BEGIN
    -- Vérifie si le "numus" existe dans t_us
    -- Si ce n'est pas le cas, ajoute une nouvelle entité dans t_us
    INSERT OR IGNORE INTO t_us (numus) SELECT NEW.numus WHERE NEW.numus IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_us WHERE numus = NEW.numus);
END;

--Entre j_rel_axe et t_tranchee
CREATE TRIGGER tgbi_m_t_tranchee_f_j_rel_axe
BEFORE INSERT ON j_rel_axe
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_tranchee (numtr) SELECT NEW.numtr WHERE NEW.numtr IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_tranchee WHERE numtr = NEW.numtr);
END;

--Entre j_rel_axe et t_sondage
CREATE TRIGGER tgbi_m_t_sondage_f_j_rel_axe
BEFORE INSERT ON j_rel_axe
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_sondage (numsd) SELECT NEW.numsd WHERE NEW.numsd IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_sondage WHERE numsd = NEW.numsd);
END;

--Entre j_rel_axe et t_axe
CREATE TRIGGER tgbi_m_t_axe_f_j_rel_axe
BEFORE INSERT ON j_rel_axe
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_axe (numaxe) SELECT NEW.numaxe WHERE NEW.numaxe IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_axe WHERE numaxe = NEW.numaxe);
END;

--Entre j_us_log et t_us
CREATE TRIGGER tgbi_m_t_us_f_j_us_log
BEFORE INSERT ON j_us_log
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_us (numus) SELECT NEW.numus WHERE NEW.numus IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_us WHERE numus = NEW.numus);
END;

--Entre j_us_log et t_log
CREATE TRIGGER tgbi_m_t_log_f_j_us_log
BEFORE INSERT ON j_us_log
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_log (numlog) SELECT NEW.numlog WHERE NEW.numlog IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_log WHERE numlog = NEW.numlog);
END;

--Entre j_rel_us et t_us 1
CREATE TRIGGER tgbi_m_t_us_f_j_rel_us1
BEFORE INSERT ON j_rel_us
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_us (numus) SELECT NEW.numus1 WHERE NEW.numus1 IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_us WHERE numus = NEW.numus1);
END;

--Entre j_rel_us et t_us 2
CREATE TRIGGER tgbi_m_t_us_f_j_rel_us2
BEFORE INSERT ON j_rel_us
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_us (numus) SELECT NEW.numus2 WHERE NEW.numus2 IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_us WHERE numus = NEW.numus2);
END;

--Entre j_rel_fait et t_fait 1
CREATE TRIGGER tgbi_m_t_fait_f_j_rel_fait1
BEFORE INSERT ON j_rel_fait
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_fait (numfait) SELECT NEW.numfait1 WHERE NEW.numfait1 IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_fait WHERE numfait = NEW.numfait1);
END;

--Entre j_rel_fait et t_fait 2
CREATE TRIGGER tgbi_m_t_fait_f_j_rel_fait2
BEFORE INSERT ON j_rel_fait
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_fait (numfait) SELECT NEW.numfait2 WHERE NEW.numfait2 IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_fait WHERE numfait = NEW.numfait2);
END;

--Entre j_rel_sondage et t_sondage
CREATE TRIGGER tgbi_m_t_sondage_f_j_rel_sondage
BEFORE INSERT ON j_rel_sondage
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_sondage (numsd) SELECT NEW.numsd WHERE NEW.numsd IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_sondage WHERE numsd = NEW.numsd);
END;

--Entre j_rel_sondage et t_us
CREATE TRIGGER tgbi_m_t_us_f_j_rel_sondage
BEFORE INSERT ON j_rel_sondage
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_us (numus) SELECT NEW.numus WHERE NEW.numus IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_us WHERE numus = NEW.numus);
END;

--Entre j_rel_sondage et t_log
CREATE TRIGGER tgbi_m_t_log_f_j_rel_sondage
BEFORE INSERT ON j_rel_sondage
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_log (numlog) SELECT NEW.numlog WHERE NEW.numlog IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_log WHERE numlog = NEW.numlog);
END;

--Entre j_rel_sondage et t_fait
CREATE TRIGGER tgbi_m_t_fait_f_j_rel_sondage
BEFORE INSERT ON j_rel_sondage
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_fait (numfait) SELECT NEW.numfait WHERE NEW.numfait IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_fait WHERE numfait = NEW.numfait);
END;

--Entre j_rel_tranchee et t_tranchee
CREATE TRIGGER tgbi_m_t_tranchee_f_j_rel_tranchee
BEFORE INSERT ON j_rel_tranchee
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_tranchee (numtr) SELECT NEW.numtr WHERE NEW.numtr IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_tranchee WHERE numtr = NEW.numtr);
END;

--Entre j_rel_tranchee et t_us
CREATE TRIGGER tgbi_m_t_us_f_j_rel_tranchee
BEFORE INSERT ON j_rel_tranchee
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_us (numus) SELECT NEW.numus WHERE NEW.numus IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_us WHERE numus = NEW.numus);
END;

--Entre j_rel_tranchee et t_fait
CREATE TRIGGER tgbi_m_t_fait_f_j_rel_tranchee
BEFORE INSERT ON j_rel_tranchee
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_fait (numfait) SELECT NEW.numfait WHERE NEW.numfait IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_fait WHERE numfait = NEW.numfait);
END;

--Entre j_rel_tranchee et t_log
CREATE TRIGGER tgbi_m_t_log_f_j_rel_tranchee
BEFORE INSERT ON j_rel_tranchee
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_log (numlog) SELECT NEW.numlog WHERE NEW.numlog IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_log WHERE numlog = NEW.numlog);
END;

--Entre j_rel_ens et t_ens 1
CREATE TRIGGER tgbi_m_t_ens_f_j_rel_ens1
BEFORE INSERT ON j_rel_ens
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_ens (numens) SELECT NEW.numens1 WHERE NEW.numens1 IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_ens WHERE numens = NEW.numens1);
END;

--Entre j_rel_ens et t_ens 2
CREATE TRIGGER tgbi_m_t_ens_f_j_rel_ens2
BEFORE INSERT ON j_rel_ens
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_ens (numens) SELECT NEW.numens2 WHERE NEW.numens2 IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_ens WHERE numens = NEW.numens2);
END;

--Entre j_rel_ens et t_us
CREATE TRIGGER tgbi_m_t_us_f_j_rel_ens
BEFORE INSERT ON j_rel_ens
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_us (numus) SELECT NEW.numus WHERE NEW.numus IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_us WHERE numus = NEW.numus);
END;

--Entre j_rel_ens et t_fait
CREATE TRIGGER tgbi_m_t_fait_f_j_rel_ens
BEFORE INSERT ON j_rel_ens
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_fait (numfait) SELECT NEW.numfait WHERE NEW.numfait IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_fait WHERE numfait = NEW.numfait);
END;

--Entre j_seq_phase et t_seq
CREATE TRIGGER tgbi_m_t_seq_f_j_seq_phase
BEFORE INSERT ON j_seq_phase
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_seq (numseq) SELECT NEW.numseq WHERE NEW.numseq IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_seq WHERE numseq = NEW.numseq);
END;

--Entre j_seq_phase et t_phase
CREATE TRIGGER tgbi_m_t_phase_f_j_seq_phase
BEFORE INSERT ON j_seq_phase
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_phase (numphase) SELECT NEW.numphase WHERE NEW.numphase IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_phase WHERE numphase = NEW.numphase);
END;

--Entre j_phase_per et t_phase
CREATE TRIGGER tgbi_m_t_phase_f_j_phase_per
BEFORE INSERT ON j_phase_per
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_phase (numphase) SELECT NEW.numphase WHERE NEW.numphase IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_phase WHERE numphase = NEW.numphase);
END;

--Entre j_phase_per et t_periode
CREATE TRIGGER tgbi_m_t_periode_f_j_phase_per
BEFORE INSERT ON j_phase_per
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_periode (numperiod) SELECT NEW.numperiod WHERE NEW.numperiod IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_periode WHERE numperiod = NEW.numperiod);
END;

--Entre j_rel_minute et t_minute
CREATE TRIGGER tgbi_m_t_minute_f_j_rel_minute
BEFORE INSERT ON j_rel_minute
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_minute (numinute) SELECT NEW.numinute WHERE NEW.numinute IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_minute WHERE numinute = NEW.numinute);
END;

--Entre j_rel_minute et t_sondage
CREATE TRIGGER tgbi_m_t_sondage_f_j_rel_minute
BEFORE INSERT ON j_rel_minute
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_sondage (numsd) SELECT NEW.numsd WHERE NEW.numsd IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_sondage WHERE numsd = NEW.numsd);
END;

--Entre j_rel_minute et t_tranchee
CREATE TRIGGER tgbi_m_t_tranchee_f_j_rel_minute
BEFORE INSERT ON j_rel_minute
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_tranchee (numtr) SELECT NEW.numtr WHERE NEW.numtr IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_tranchee WHERE numtr = NEW.numtr);
END;

--Entre j_rel_minute et t_ens
CREATE TRIGGER tgbi_m_t_ens_f_j_rel_minute
BEFORE INSERT ON j_rel_minute
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_ens (numens) SELECT NEW.numens WHERE NEW.numens IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_ens WHERE numens = NEW.numens);
END;

--Entre j_rel_minute et t_fait
CREATE TRIGGER tgbi_m_t_fait_f_j_rel_minute
BEFORE INSERT ON j_rel_minute
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_fait (numfait) SELECT NEW.numfait WHERE NEW.numfait IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_fait WHERE numfait = NEW.numfait);
END;

--Entre j_rel_minute et t_us
CREATE TRIGGER tgbi_m_t_us_f_j_rel_minute
BEFORE INSERT ON j_rel_minute
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_us (numus) SELECT NEW.numus WHERE NEW.numus IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_us WHERE numus = NEW.numus);
END;

--Entre j_rel_minute et t_mobilier
CREATE TRIGGER tgbi_m_t_mobilier_f_j_rel_minute
BEFORE INSERT ON j_rel_minute
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_mobilier (id_mob) SELECT NEW.id_mob WHERE NEW.id_mob IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_mobilier WHERE id_mob = NEW.id_mob);
END;

--Entre j_rel_minute et t_log
CREATE TRIGGER tgbi_m_t_log_f_j_rel_minute
BEFORE INSERT ON j_rel_minute
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_log (numlog) SELECT NEW.numlog WHERE NEW.numlog IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_log WHERE numlog = NEW.numlog);
END;

--Entre j_rel_minute et t_axe
CREATE TRIGGER tgbi_m_t_axe_f_j_rel_minute
BEFORE INSERT ON j_rel_minute
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_axe (numaxe) SELECT NEW.numaxe WHERE NEW.numaxe IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_axe WHERE numaxe = NEW.numaxe);
END;

--Entre j_rel_photo et t_photo
CREATE TRIGGER tgbi_m_t_photo_f_j_rel_photo
BEFORE INSERT ON j_rel_photo
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_photo (numphoto) SELECT NEW.numphoto WHERE NEW.numphoto IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_photo WHERE numphoto = NEW.numphoto);
END;

--Entre j_rel_photo et t_sondage
CREATE TRIGGER tgbi_m_t_sondage_f_j_rel_photo
BEFORE INSERT ON j_rel_photo
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_sondage (numsd) SELECT NEW.numsd WHERE NEW.numsd IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_sondage WHERE numsd = NEW.numsd);
END;

--Entre j_rel_photo et t_tranchee
CREATE TRIGGER tgbi_m_t_tranchee_f_j_rel_photo
BEFORE INSERT ON j_rel_photo
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_tranchee (numtr) SELECT NEW.numtr WHERE NEW.numtr IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_tranchee WHERE numtr = NEW.numtr);
END;

--Entre j_rel_photo et t_ens
CREATE TRIGGER tgbi_m_t_ens_f_j_rel_photo
BEFORE INSERT ON j_rel_photo
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_ens (numens) SELECT NEW.numens WHERE NEW.numens IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_ens WHERE numens = NEW.numens);
END;

--Entre j_rel_photo et t_fait
CREATE TRIGGER tgbi_m_t_fait_f_j_rel_photo
BEFORE INSERT ON j_rel_photo
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_fait (numfait) SELECT NEW.numfait WHERE NEW.numfait IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_fait WHERE numfait = NEW.numfait);
END;

--Entre j_rel_photo et t_us
CREATE TRIGGER tgbi_m_t_us_f_j_rel_photo
BEFORE INSERT ON j_rel_photo
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_us (numus) SELECT NEW.numus WHERE NEW.numus IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_us WHERE numus = NEW.numus);
END;

--Entre j_rel_photo et t_mobilier
CREATE TRIGGER tgbi_m_t_mobilier_f_j_rel_photo
BEFORE INSERT ON j_rel_photo
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_mobilier (id_mob) SELECT NEW.id_mob WHERE NEW.id_mob IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_mobilier WHERE id_mob = NEW.id_mob);
END;

--Entre j_rel_photo et t_log
CREATE TRIGGER tgbi_m_t_log_f_j_rel_photo
BEFORE INSERT ON j_rel_photo
FOR EACH ROW
BEGIN
    INSERT OR IGNORE INTO t_log (numlog) SELECT NEW.numlog WHERE NEW.numlog IS NOT NULL AND NOT EXISTS (SELECT 1 FROM t_log WHERE numlog = NEW.numlog);
END;

*/
------------------------------------------------------------------------
--Les vues
------------------------------------------------------------------------

--Vue de récapitulation des logs, faits et US enregistrés par tranchée
DROP VIEW IF EXISTS vue_recap_tranchee;
CREATE VIEW vue_recap_tranchee AS
SELECT j."numtr",
REPLACE(GROUP_CONCAT(DISTINCT j."numlog"),',',', ') AS recap_log, -- l'argument DISTINCT dans le GROUP_CONCAT permet de générer une liste de valeurs uniques, le REPLACE permet de modifier le symbole par défaut de la fonction GROUP_CONCAT (merci Bertrand d'avoir trouver l'astuce !)
REPLACE(GROUP_CONCAT(DISTINCT j."numfait"),',',', ') AS recap_fait,
REPLACE(GROUP_CONCAT(DISTINCT j."numus"),',',', ') AS recap_us
FROM (SELECT * FROM j_rel_tranchee ORDER BY "numlog","numfait","numus") AS j
GROUP BY j."numtr"
ORDER BY j."numtr";

--Vue de récapitulation des US des sequences, des phases et des périodes par fait
DROP VIEW IF EXISTS vue_recap_us_fait;
CREATE VIEW vue_recap_us_fait AS
SELECT "numfait" as numfait,
REPLACE(GROUP_CONCAT(DISTINCT u."numus"),',',', ') as recap_us,
REPLACE(GROUP_CONCAT(DISTINCT case when u."num_seq" IS NOT NULL then u."num_seq" ELSE null END),',',', ') as recap_seq,
REPLACE(GROUP_CONCAT(DISTINCT case when jsp."numphase" IS NOT NULL then jsp."numphase" ELSE null END),',',', ') as recap_phase,
REPLACE(GROUP_CONCAT(DISTINCT case when jpp."numperiod" IS NOT NULL then jpp."numperiod" ELSE null END),',',', ') as recap_periode
FROM (SELECT * FROM t_us ORDER BY "numus") as u
LEFT JOIN j_seq_phase as jsp ON u.num_seq = jsp.numseq
LEFT JOIN j_phase_per as jpp ON jsp.numphase = jpp.numphase
GROUP BY "numfait"
ORDER BY "numus";

--Vue de récapitulation des relations stratigraphiques par US
DROP VIEW IF EXISTS vue_recap_relationus;
CREATE VIEW vue_recap_relationus AS
SELECT "numus1" as numus,
REPLACE(GROUP_CONCAT(DISTINCT CASE WHEN "typrel" LIKE 'sous' THEN "numus2" ELSE null END),',', ', ') AS us_posterieur,
REPLACE(GROUP_CONCAT(DISTINCT CASE WHEN "typrel" LIKE 'sur' THEN "numus2" ELSE null END),',', ', ') AS us_anterieur,
REPLACE(GROUP_CONCAT(DISTINCT CASE WHEN "typrel" IN('égale','équivalente','synchrone') THEN "numus2" ELSE null END),',', ', ') AS rel_horizontal
FROM (SELECT * FROM j_rel_us ORDER BY "numus2")
GROUP BY numus1
ORDER BY numus1;

--Vue d'inversion des relations stratigraphiques à partir de la table j_rel_us
DROP VIEW IF EXISTS vue_j_rel_us_inverse;
CREATE VIEW vue_j_rel_us_inverse AS
SELECT "id_rel_us", "numus2" as numus1,
CASE WHEN "typrel" LIKE 'sur' THEN 'sous'
WHEN "typrel" LIKE 'sous' THEN 'sur' ELSE "typrel" end as typrel,
"numus1" as numus2, incert
FROM j_rel_us;

--Vue de récapitulation des relations stratigraphiques inversées par US
DROP VIEW IF EXISTS vue_recap_relationus_deduite;
CREATE VIEW vue_recap_relationus_deduite AS
SELECT "numus1" as numus,
REPLACE(GROUP_CONCAT(DISTINCT CASE WHEN "typrel" LIKE 'sous' THEN "numus2" ELSE null END),',', ', ') AS us_posterieur,
REPLACE(GROUP_CONCAT(DISTINCT CASE WHEN "typrel" LIKE 'sur' THEN "numus2" ELSE null END),',', ', ') AS us_anterieur,
REPLACE(GROUP_CONCAT(CASE WHEN "typrel" IN('égale','équivalente','synchrone') THEN "numus2" ELSE null END),',', ', ') AS rel_horizontal
FROM (SELECT * FROM vue_j_rel_us_inverse ORDER BY "numus2")
GROUP BY numus1
ORDER BY numus1;

--Vue de récapitulation des US par log stratigraphique
DROP VIEW IF EXISTS vue_recap_us_log;
CREATE VIEW vue_recap_us_log AS 
SELECT "numlog", 
REPLACE(GROUP_CONCAT(DISTINCT "numus"),',',', ') as recap_us,
MAX("prof_base") AS prof_max,
MIN("zmin_uslog") AS z_min, 
MAX("zmax_uslog") AS z_max 
FROM (SELECT * FROM j_us_log ORDER BY "numus") 
GROUP BY "numlog" 
ORDER BY "numlog";

--Vue de récapitulation des tranchées, sondages, faits, us, log et iso par minute
DROP VIEW IF EXISTS vue_recap_minute;
CREATE VIEW vue_recap_minute AS
SELECT 
    m.numinute, m.echelle, m.contenu, m.creator, m.format, m.support,
    REPLACE(GROUP_CONCAT(s.sujet),',', ', ') AS sujet
FROM t_minute AS m
LEFT JOIN (
    SELECT 
        s1.numinute,
        s1.sujet,
        s1.ordre
    FROM (
        SELECT numinute, 1 as ordre, 'Tr.'||numtr as sujet from j_rel_minute where numtr is not null
        UNION 
        SELECT numinute, 2 as ordre, 'Sd.'||numsd as sujet from j_rel_minute where numsd is not null
        UNION 
        SELECT numinute, 3 as ordre, 'Ens.'||numens as sujet from j_rel_minute where numens is not null
        UNION 
        SELECT numinute, 4 as ordre, 'F.'||numfait as sujet from j_rel_minute where numfait is not null
        UNION 
        SELECT numinute, 5 as ordre, 'US '||numus as sujet from j_rel_minute where numus is not null
        UNION 
        SELECT numinute, 6 as ordre, 'Iso.'||id_mob as sujet from j_rel_minute where id_mob is not null
        UNION 
        SELECT numinute, 7 as ordre, 'Log '||numlog as sujet from j_rel_minute where numlog is not null
        UNION 
        SELECT numinute, 8 as ordre, 'Axe '||numaxe as sujet from j_rel_minute where numaxe is not null
    ) AS s1
) AS s 
ON m.numinute = s.numinute
GROUP BY m.numinute 
ORDER BY m.numinute DESC;

--Vue de récapitulation des tranchées, sondages, faits, us et iso par photo 
DROP VIEW IF EXISTS vue_recap_photo;
CREATE VIEW vue_recap_photo AS
SELECT 
    p.numphoto, p.nomfichier, p.legend, p.vue_vers, p.creator, p.datephoto,p.type,
    REPLACE(GROUP_CONCAT(s.sujet),',', ', ') AS sujet
FROM t_photo AS p
LEFT JOIN (
    SELECT 
        s1.numphoto,
        s1.sujet,
        s1.ordre
    FROM (
        SELECT numphoto, 1 as ordre, 'Tr.'||numtr as sujet from j_rel_photo where numtr is not null
        UNION 
        SELECT numphoto, 2 as ordre, 'Sd.'||numsd as sujet from j_rel_photo where numsd is not null
        UNION 
        SELECT numphoto, 3 as ordre, 'Ens.'||numens as sujet from j_rel_photo where numens is not null
        UNION 
        SELECT numphoto, 4 as ordre, 'F.'||numfait as sujet from j_rel_photo where numfait is not null
        UNION 
        SELECT numphoto, 5 as ordre, 'US '||numus as sujet from j_rel_photo where numus is not null
        UNION 
        SELECT numphoto, 6 as ordre, 'Iso.'||id_mob as sujet from j_rel_photo where id_mob is not null
        UNION 
        SELECT numphoto, 7 as ordre, 'Log '||numlog as sujet from j_rel_photo where numlog is not null
    ) AS s1
) AS s 
ON p.numphoto = s.numphoto
GROUP BY p.numphoto 
ORDER BY p.numphoto DESC;


--Vue de récapitulation des tranchées, sondages, faits, us et iso par photogrammétrie 
DROP VIEW IF EXISTS vue_recap_photogram;
CREATE VIEW vue_recap_photogram AS
SELECT r."id_photogram",
	REPLACE(GROUP_CONCAT(s.sujet),',', ', ') AS sujet
FROM j_rel_photo AS r
JOIN vue_recap_photo AS s ON s.numphoto = r.numphoto
GROUP BY r."id_photogram"
ORDER BY r."id_photogram";

-- Vue d'exportation de la table t_us pour le stratifiant. Manque quelques champs : FPAestime (date estimée au plus ancien de fin de formation de l'US) ; FPRestime (date estimée au plus récent de fin de formation de l'US) ; REF_PhaseDebut (n° d'ordre de la phase au plus ancien attribué à l'US) ; REF_PhaseFin (n° d'ordre de la phase au plus récent attribué à l'US). Pour ces requêtes, j'ai besoin d'aide...
DROP VIEW IF EXISTS ExportUS;
CREATE VIEW ExportUS AS
SELECT u."numus" AS ID_US, REPLACE(REPLACE( u."type_us",'couche physique','couche'),'négative','négatif') AS Type_US, u."datinf_interpret" AS FPA, u."datsup_interpret" AS FPR,
p."ordre_stratifiant" AS REF_PhaseDebut, p."ordre_stratifiant" AS REF_PhaseFin
FROM t_us AS u
LEFT JOIN j_seq_phase AS ph ON ph."numseq" = u."num_seq" 
LEFT JOIN j_phase_per AS pe ON ph."numphase" = pe."numphase"
LEFT JOIN t_phase AS p ON p."numphase" = ph."numphase";


-- Vue d'exportation de la table Phase pour le stratifiant
DROP VIEW IF EXISTS ExportPhases;
CREATE VIEW ExportPhases AS
SELECT "ordre_stratifiant" AS ID_Phase, "numphase" AS NomPhase, "tpq" AS TPQ, "taq" AS TAQ
FROM t_phase;


-- Vue d'exportation de la table de relations stratigraphiques pour les relations d'antériorité/postériorié. Manque le champ RelationIncertaine rempli par NULL ou '?'
DROP VIEW IF EXISTS ExportRelations;
CREATE VIEW ExportRelations AS
SELECT DISTINCT CASE WHEN "typrel" LIKE 'sur' THEN "numus2" WHEN "typrel" LIKE 'sous' THEN "numus1" END AS REF_USanterieure, CASE WHEN "typrel" LIKE 'sur' THEN "numus1" WHEN "typrel" LIKE 'sous' THEN "numus2" END AS REF_USposterieure
FROM j_rel_us
WHERE "typrel" IN ('sur', 'sous');

-- Vue d'exportation de la table de relations stratigraphiques pour les relations horizontales. Manque le champ RelationIncertaine rempli par NULL ou '?'
DROP VIEW IF EXISTS ExportSynchros;
CREATE VIEW ExportSynchros AS
SELECT DISTINCT CASE WHEN "typrel" IN ('égal','équivalent','égale','équivalente','synchrone') THEN "numus1" END AS REF_USsynchro1, CASE WHEN "typrel" IN ('égal','équivalent','égale','équivalente','synchrone') THEN "numus2" END AS REF_USsynchro2
FROM j_rel_us
WHERE "typrel" IN ('égal','équivalent','égale','équivalente','synchrone');

-- Vue de détection des erreurs d'écritures de relations stratigraphiques : sur une même US ou relations contradictoires 
DROP VIEW IF EXISTS erreur_saisie_strati;
CREATE VIEW erreur_saisie_strati AS
SELECT 
    numus1,
    numus2,
    typrel,
    CASE 
        WHEN numus1 = numus2 THEN 'auto-relation d''US'
        WHEN typrel LIKE 'sous' AND EXISTS (
            SELECT 1 FROM j_rel_us j2 
            WHERE j2.numus1 = j_rel_us.numus2 
            AND j2.numus2 = j_rel_us.numus1 
            AND j2.typrel LIKE 'sous'
        ) THEN 'relation contradictoire'
        WHEN typrel LIKE 'sur' AND EXISTS (
            SELECT 1 FROM j_rel_us j2 
            WHERE j2.numus1 = j_rel_us.numus2 
            AND j2.numus2 = j_rel_us.numus1 
            AND j2.typrel LIKE 'sur'
        ) THEN 'relation contradictoire'
        ELSE NULL
    END AS erreur
FROM j_rel_us
WHERE (numus1 = numus2) 
    OR (typrel LIKE 'sous' AND EXISTS (
            SELECT 1 FROM j_rel_us j2 
            WHERE j2.numus1 = j_rel_us.numus2 
            AND j2.numus2 = j_rel_us.numus1 
            AND j2.typrel LIKE 'sous'
        ))
    OR (typrel LIKE 'sur' AND EXISTS (
            SELECT 1 FROM j_rel_us j2 
            WHERE j2.numus1 = j_rel_us.numus2 
            AND j2.numus2 = j_rel_us.numus1 
            AND j2.typrel LIKE 'sur'
        ));


-- Vue de détection des cycles de relations stratigraphiques (ex : US1 sur US2, US2 sur US3, US3 sur US1) et liste des US impliquées dans ces cycles
DROP VIEW IF EXISTS vue_cycles_stratigraphie;
CREATE VIEW vue_cycles_stratigraphie AS
WITH RECURSIVE
-- ------------------------------------------------------------------
-- 0) Normalisation : 'sous' saisi converti en 'sur' inversé
--    Supprime le besoin de vue_j_rel_us_inverse
-- ------------------------------------------------------------------
rel_sur AS (
    SELECT DISTINCT numus1, numus2
    FROM j_rel_us WHERE typrel = 'sur'
    UNION
    SELECT DISTINCT numus2 AS numus1, numus1 AS numus2
    FROM j_rel_us WHERE typrel = 'sous'
),

rel_horiz AS (
    SELECT DISTINCT numus1, numus2, typrel
    FROM j_rel_us
    WHERE typrel IN ('égale','équivalente','synchrone')
),

-- ------------------------------------------------------------------
-- 1) Verticales orientées (direction unique après normalisation)
-- ------------------------------------------------------------------
v(source, target) AS (
    SELECT numus1, numus2 FROM rel_sur
),

-- ------------------------------------------------------------------
-- 2) Horizontales bidirectionnelles
-- ------------------------------------------------------------------
h(a, b) AS (
    SELECT numus1, numus2 FROM rel_horiz
    UNION ALL
    SELECT numus2, numus1 FROM rel_horiz
),

-- ------------------------------------------------------------------
-- 3) US impliquées dans les relations
-- ------------------------------------------------------------------
nodes(u) AS (
    SELECT numus1 AS u FROM rel_sur
    UNION
    SELECT numus2 AS u FROM rel_sur
    UNION
    SELECT numus1 AS u FROM rel_horiz
    UNION
    SELECT numus2 AS u FROM rel_horiz
),

-- ------------------------------------------------------------------
-- 4) Arêtes combinées (vertical + horizontal)
-- ------------------------------------------------------------------
edges(x, y) AS (
    SELECT source, target FROM v
    UNION
    SELECT a, b FROM h
),

-- ------------------------------------------------------------------
-- 5) Fermeture de chemin (transitivité)
-- ------------------------------------------------------------------
reach(src, u) AS (
    SELECT u AS src, u
    FROM nodes
    UNION
    SELECT r.src, e.y
    FROM edges e
    JOIN reach r ON e.x = r.u
),

-- ------------------------------------------------------------------
-- 6) Paires mutuellement atteignables (forte connexité)
-- ------------------------------------------------------------------
mutual(a, b) AS (
    SELECT r1.src, r1.u
    FROM reach r1
    JOIN reach r2
      ON r2.src = r1.u
     AND r2.u   = r1.src
    WHERE r1.src <> r1.u
),

-- ------------------------------------------------------------------
-- 7) Arêtes non orientées pour regrouper les composantes
-- ------------------------------------------------------------------
e(a, b) AS (
    SELECT a, b FROM mutual
    UNION
    SELECT b, a FROM mutual
),

-- ------------------------------------------------------------------
-- 8) Graines : tous les sommets présents dans mutual
-- ------------------------------------------------------------------
seeds(u) AS (
    SELECT a FROM mutual
    UNION
    SELECT b FROM mutual
),

-- ------------------------------------------------------------------
-- 9) Propagation d'une étiquette de composante
-- ------------------------------------------------------------------
prop(u, root) AS (
    SELECT u, u
    FROM seeds
    UNION
    SELECT e.b,
           CASE WHEN p.root < e.b THEN p.root ELSE e.b END
    FROM e
    JOIN prop p ON e.a = p.u
),

-- ------------------------------------------------------------------
-- 10) US + identifiant de composante
-- ------------------------------------------------------------------
final_nodes AS (
    SELECT
        u         AS numus,
        MIN(root) AS cycle_id
    FROM prop
    GROUP BY u
),

-- ------------------------------------------------------------------
-- 11) Relations saisies internes aux composantes
-- ------------------------------------------------------------------
edges_in_cycles AS (
    SELECT fn1.cycle_id, r.numus1, r.numus2, r.typrel
    FROM j_rel_us r
    JOIN final_nodes fn1 ON fn1.numus = r.numus1
    JOIN final_nodes fn2 ON fn2.numus = r.numus2
    WHERE fn1.cycle_id = fn2.cycle_id
),

-- ------------------------------------------------------------------
-- 12) On garde uniquement les composantes contenant au moins
--     une relation verticale (sur/sous)
-- ------------------------------------------------------------------
valid_cycles AS (
    SELECT DISTINCT cycle_id
    FROM edges_in_cycles
    WHERE typrel IN ('sur','sous')
),

-- ------------------------------------------------------------------
-- 13) US appartenant aux cycles "valides"
-- ------------------------------------------------------------------
cycle_nodes AS (
    SELECT fn.cycle_id, fn.numus
    FROM final_nodes fn
    JOIN valid_cycles vc ON vc.cycle_id = fn.cycle_id
)

-- ------------------------------------------------------------------
-- Résultat final
-- ------------------------------------------------------------------
SELECT DISTINCT
    CAST(ec.cycle_id AS INTEGER) AS cycle_id,
    ec.numus1,
    CAST(ec.typrel AS TEXT) AS typrel,
    ec.numus2,
    'saisie' AS source
FROM edges_in_cycles ec
JOIN valid_cycles vc ON vc.cycle_id = ec.cycle_id

UNION

-- ------------------------------------------------------------------
-- Auto-relations (numus1 = numus2), non détectées par la récursion
-- ------------------------------------------------------------------
SELECT DISTINCT
    numus1 AS cycle_id,
    numus1,
    typrel,
    numus2,
    'saisie' AS source
FROM j_rel_us
WHERE numus1 = numus2
  AND typrel IN ('sur', 'sous')
ORDER BY cycle_id, numus1, numus2, source;

------------------------------------------------------------------
---------- la précédente vue vue_cycles_stratigraphie s'arrête ici
------------------------------------------------------------------

-- Vue qui liste les cycles stratigraphiques avec chemin reconstruit (une ligne par cycle)
DROP VIEW IF EXISTS vue_liste_cycles_strati;
CREATE VIEW vue_liste_cycles_strati AS
WITH RECURSIVE

-- Lecture unique de vue_cycles_stratigraphie
base AS (
    SELECT cycle_id, numus1, numus2, typrel
    FROM vue_cycles_stratigraphie
),

-- Arêtes disponibles dans les cycles, 'sous' normalisé en 'sur' inversé pour la traversée
aretes AS (
    SELECT DISTINCT cycle_id,
        CASE WHEN typrel = 'sous' THEN numus2 ELSE numus1 END AS numus1,
        CASE WHEN typrel = 'sous' THEN numus1 ELSE numus2 END AS numus2,
        CASE WHEN typrel = 'sous' THEN 'sur'  ELSE typrel END AS typrel
    FROM base
    WHERE numus1 <> numus2
),

-- Nœuds par cycle
noeuds AS (
    SELECT cycle_id, numus1 AS numus FROM aretes
    UNION
    SELECT cycle_id, numus2 AS numus FROM aretes
),

-- Nombre d'US par cycle
nb_us AS (
    SELECT cycle_id, COUNT(DISTINCT numus) AS nb_us
    FROM noeuds
    GROUP BY cycle_id
),

-- Point de départ : MIN(numus) par cycle
depart AS (
    SELECT cycle_id, MIN(numus) AS depart
    FROM noeuds
    GROUP BY cycle_id
),

-- Reconstruction récursive du chemin
chemin(cycle_id, depart, courant, chemin_display, visites, longueur, complet) AS (
    -- Base : partir du nœud de départ
    SELECT d.cycle_id, d.depart, d.depart,
           CAST(d.depart AS TEXT),
           ',' || CAST(d.depart AS TEXT) || ',',
           0, 0
    FROM depart d

    UNION ALL

    -- Extension du chemin arête par arête
    SELECT sp.cycle_id, sp.depart, a.numus2,
           sp.chemin_display
               || CASE WHEN a.typrel IN ('égale','équivalente','synchrone') THEN ' = ' ELSE ' → ' END
               || CAST(a.numus2 AS TEXT),
           sp.visites || CAST(a.numus2 AS TEXT) || ',',
           sp.longueur + 1,
           CASE WHEN a.numus2 = sp.depart THEN 1 ELSE 0 END
    FROM chemin sp
    JOIN aretes a ON a.numus1 = sp.courant AND a.cycle_id = sp.cycle_id
    WHERE sp.complet = 0
      AND sp.longueur < 500
      AND (
            -- Retour au départ : cycle complet
            (a.numus2 = sp.depart AND sp.longueur >= 1)
            OR
            -- Nœud non encore visité
            (a.numus2 <> sp.depart
             AND sp.visites NOT LIKE '%,' || CAST(a.numus2 AS TEXT) || ',%')
          )
),

-- Chemin le plus court par cycle (un seul résultat par cycle_id)
meilleur_chemin AS (
    SELECT cc.cycle_id, MIN(cc.chemin_display) AS chemin_cycle
    FROM chemin cc
    WHERE cc.complet = 1
      AND cc.longueur = (
          SELECT MIN(longueur) FROM chemin cc2
          WHERE cc2.cycle_id = cc.cycle_id AND cc2.complet = 1
      )
    GROUP BY cc.cycle_id
)

-- Résultat final : une ligne par cycle
SELECT
    nb.cycle_id,
    nb.nb_us,
    COALESCE(mc.chemin_cycle, '(chemin non trouvé)') AS chemin_cycle
FROM nb_us nb
LEFT JOIN meilleur_chemin mc ON mc.cycle_id = nb.cycle_id

UNION ALL

-- Auto-relations (numus1 = numus2)
SELECT DISTINCT
    numus1 AS cycle_id,
    1      AS nb_us,
    CAST(numus1 AS TEXT) || ' → ' || CAST(numus2 AS TEXT) AS chemin_cycle
FROM base
WHERE numus1 = numus2

ORDER BY cycle_id;

-- Vue recapitulative des us par sequence
DROP VIEW IF EXISTS vue_recap_us_seq;
CREATE VIEW vue_recap_us_seq AS
SELECT "num_seq", REPLACE(GROUP_CONCAT(DISTINCT "numus"),',',', ') AS recap_us 
FROM (SELECT * FROM t_us ORDER BY "numus") 
GROUP BY "num_seq" 
ORDER BY "numus";

-- Vue recapitulative des séquences par phase
DROP VIEW IF EXISTS vue_recap_pha_seq;
CREATE VIEW vue_recap_pha_seq AS
SELECT "numphase" as numphase, REPLACE(GROUP_CONCAT(DISTINCT "numseq"),',',', ') AS recap_seq
FROM (SELECT * FROM j_seq_phase ORDER BY "numseq")
GROUP BY "numphase" 
ORDER BY "numseq";

-- Vue recapitulative des phases par période
DROP VIEW IF EXISTS vue_recap_per_pha;
CREATE VIEW vue_recap_per_pha AS
SELECT "numperiod" as periode, REPLACE(GROUP_CONCAT(DISTINCT "numphase"),',',', ') as recap_phases
FROM (SELECT * FROM j_phase_per ORDER BY "numphase") as p
GROUP BY "numperiod" 
ORDER BY "numperiod";

-- Vue de récapitulation des phases et périodes à l'échelle de l'ensemble
DROP VIEW IF EXISTS vue_recap_periode_ea;
CREATE VIEW vue_recap_periode_ea AS
SELECT
    e."numens",
    REPLACE(GROUP_CONCAT(DISTINCT j2."numphase"), ',', ', ') AS phase,
    REPLACE(GROUP_CONCAT(DISTINCT j3."numperiod"), ',', ', ') AS periode,
    e.geometry
FROM
    t_ens AS e
JOIN
    j_rel_ens AS j1 ON e."numens" = j1."numens1"
LEFT JOIN
    t_us AS u ON j1."numus" = u."numus"
LEFT JOIN
    t_fait AS f ON j1."numfait" = f."numfait"
LEFT JOIN
    t_us AS u2 ON f."numfait" = u2."numfait"
LEFT JOIN
    j_seq_phase AS j2 ON j2."numseq" = COALESCE(u."num_seq", u2."num_seq")
LEFT JOIN
    j_phase_per AS j3 ON j3."numphase" = j2."numphase"
GROUP BY
    e."numens";

-- Vue de récapitulation des US par phase
DROP VIEW IF EXISTS vue_recap_us_phase;
CREATE VIEW vue_recap_us_phase AS
SELECT "numphase", GROUP_CONCAT(u."numus",', ') as recap_us 
FROM j_seq_phase
LEFT JOIN (SELECT * FROM t_us ORDER BY "numus") AS u ON u."num_seq" = "numseq"
GROUP BY "numphase" 
ORDER BY "numphase";

--Vue d'inventaire d'US
DROP VIEW IF EXISTS inventaire_us;
CREATE VIEW inventaire_us AS
WITH
cte_tr AS (
    SELECT numus, REPLACE(GROUP_CONCAT(DISTINCT numtr),',',', ') AS tranchees
    FROM j_rel_tranchee WHERE numus IS NOT NULL GROUP BY numus
),
cte_sd AS (
    SELECT numus, REPLACE(GROUP_CONCAT(DISTINCT numsd),',',', ') AS sondages
    FROM j_rel_sondage WHERE numus IS NOT NULL GROUP BY numus
),
cte_axe AS (
    SELECT numus, REPLACE(GROUP_CONCAT(DISTINCT numaxe),',',', ') AS axes
    FROM (
        SELECT numus, numaxe FROM coupe_poly WHERE numus IS NOT NULL
        UNION
        SELECT numus, numaxe FROM coupe_line WHERE numus IS NOT NULL
    ) GROUP BY numus
),
cte_log AS (
    SELECT numus, REPLACE(GROUP_CONCAT(DISTINCT numlog),',',', ') AS logs
    FROM j_us_log WHERE numus IS NOT NULL GROUP BY numus
),
cte_min AS (
    SELECT numus, REPLACE(GROUP_CONCAT(DISTINCT numinute),',',', ') AS minutes
    FROM j_rel_minute WHERE numus IS NOT NULL GROUP BY numus
),
cte_photo AS (
    SELECT numus, REPLACE(GROUP_CONCAT(DISTINCT numphoto),',',', ') AS photos
    FROM j_rel_photo WHERE numus IS NOT NULL GROUP BY numus
),
cte_ens AS (
    SELECT numus, numens1 AS numens
    FROM j_rel_ens WHERE numus IS NOT NULL GROUP BY numus
)
SELECT
     u."numus"
    ,u."numfait"
    ,ens.numens
    ,tr.tranchees
    ,sd.sondages
    ,axe.axes
    ,log.logs
    ,min.minutes
    ,photo.photos
    ,u."type_us", u."nature_us", u."interpret", u."description"
    ,u."forme", u."diam", u."dim_max", u."dim_min", u."epais", u."prof_app"
    ,u."datinf_mobilier", u."datsup_mobilier"
    ,CASE WHEN v."us_anterieur"  IS NULL AND d."us_anterieur"  IS NULL THEN ''
          WHEN v."us_anterieur"  IS NULL THEN d."us_anterieur"
          WHEN d."us_anterieur"  IS NULL THEN v."us_anterieur"
          WHEN d."us_anterieur" = v."us_anterieur" THEN d."us_anterieur"
          ELSE v."us_anterieur" ||', '|| d."us_anterieur" END AS sur,
    CASE WHEN v."us_posterieur" IS NULL AND d."us_posterieur" IS NULL THEN ''
          WHEN v."us_posterieur" IS NULL THEN d."us_posterieur"
          WHEN d."us_posterieur" IS NULL THEN v."us_posterieur"
          WHEN d."us_posterieur" = v."us_posterieur" THEN d."us_posterieur"
          ELSE v."us_posterieur"||', '||d."us_posterieur" END AS sous,
    CASE WHEN v."rel_horizontal" IS NULL AND d."rel_horizontal" IS NULL THEN ''
          WHEN v."rel_horizontal" IS NULL THEN d."rel_horizontal"
          WHEN d."rel_horizontal" IS NULL THEN v."rel_horizontal"
          WHEN d."rel_horizontal" = v."rel_horizontal" THEN d."rel_horizontal"
          ELSE v."rel_horizontal"||', '||d."rel_horizontal" END AS synch,
     u."note"
    ,u."num_seq" AS numseq
    ,ph."numphase"
    ,pe."numperiod"
FROM t_us AS u
LEFT JOIN cte_tr    AS tr    ON tr.numus    = u."numus"
LEFT JOIN cte_sd    AS sd    ON sd.numus    = u."numus"
LEFT JOIN cte_axe   AS axe   ON axe.numus   = u."numus"
LEFT JOIN cte_log   AS log   ON log.numus   = u."numus"
LEFT JOIN cte_min   AS min   ON min.numus   = u."numus"
LEFT JOIN cte_photo AS photo ON photo.numus = u."numus"
LEFT JOIN cte_ens   AS ens   ON ens.numus   = u."numus"
LEFT JOIN vue_recap_relationus         AS v ON v."numus"  = u."numus"
LEFT JOIN vue_recap_relationus_deduite AS d ON d."numus"  = u."numus"
LEFT JOIN j_seq_phase AS ph ON ph."numseq"   = u."num_seq"
LEFT JOIN j_phase_per AS pe ON pe."numphase" = ph."numphase"
ORDER BY u."numus";

/*Vue de relations spatiales entre la table des faits et les tables t_tranchee, t_sondage et t_axe*/
DROP VIEW IF EXISTS vue_intersects_fait;
CREATE VIEW vue_intersects_fait AS
SELECT e."numens", f."numfait", t."numtr", s."numsd", a."id_axe", a."numaxe", 
       x(startpoint(ca.geometry)) AS x_min_coupe, 
       y(startpoint(ca.geometry)) AS y_min_coupe, 
       x(endpoint(ca.geometry)) AS x_max_coupe, 
       y(endpoint(ca.geometry)) AS y_max_coupe
FROM t_fait AS f
LEFT JOIN t_tranchee AS t ON st_intersects(f.geometry, t.geometry)
LEFT JOIN t_sondage AS s ON st_intersects(f.geometry, s.geometry)
LEFT JOIN t_ens AS e ON st_intersects(f.geometry, e.geometry)
LEFT JOIN t_axe AS a ON st_intersects(f.geometry, a.geometry)
JOIN coupe_axe AS ca ON a."numaxe" = ca."numaxe";

/*Vue de relations spatiales entre la table des US et les tranchées, sondages et axe*/
DROP VIEW IF EXISTS vue_intersects_us;
CREATE VIEW vue_intersects_us AS
SELECT u."numus", u."numfait", t."numtr", s."numsd", a."id_axe", a."numaxe"
FROM t_us AS u
LEFT JOIN t_fait AS f ON f."numfait" = u."numfait"
LEFT JOIN t_tranchee AS t ON (u.geometry IS NULL AND f.geometry IS NOT NULL AND st_intersects(f.geometry, t.geometry))
                           OR (u.geometry IS NOT NULL AND st_intersects(u.geometry, t.geometry))
LEFT JOIN t_sondage AS s ON (u.geometry IS NULL AND f.geometry IS NOT NULL AND st_intersects(f.geometry, s.geometry))
                           OR (u.geometry IS NOT NULL AND st_intersects(u.geometry, s.geometry))
LEFT JOIN t_axe AS a ON (u.geometry IS NULL AND f.geometry IS NOT NULL AND st_intersects(f.geometry, a.geometry))
                      OR (u.geometry IS NOT NULL AND st_intersects(u.geometry, a.geometry));

/*Vue de génération de la table j_rel_axe d'association entre les axes, les US, les faits et les sondages. Le contenu de cette vue peut-être copier/coller dans la table j_rel_axe*/
DROP VIEW IF EXISTS vue_matrix_rel_axe;
CREATE VIEW vue_matrix_rel_axe AS
SELECT p."numaxe" AS numaxe, p."numus" AS numus, p."numfait" AS numfait, NULL AS numsd
FROM coupe_poly AS p
WHERE numus IS NOT NULL OR numfait IS NOT NULL OR numsd IS NOT NULL
UNION
SELECT l."numaxe" AS numaxe, l."numus" AS numus, l."numfait" AS numfait, l."numsd" AS numsd
FROM coupe_line AS l
WHERE numus IS NOT NULL OR numfait IS NOT NULL OR numsd IS NOT NULL
ORDER BY numaxe
;

/*Vue de récapitulation des faits, US et sondage par axe dessiné en coupe et calcul des coordonnées d'emprise de chaque coupe d'après la coupe coupe_line*/
DROP VIEW IF EXISTS vue_recap_coupe_axe;
CREATE VIEW vue_recap_coupe_axe AS
SELECT t."numaxe",
       (SELECT GROUP_CONCAT("numus", ', ')
        FROM (SELECT DISTINCT "numus" FROM vue_matrix_rel_axe WHERE numaxe = t.numaxe ORDER BY "numus")
       ) AS us,
       (SELECT GROUP_CONCAT("numfait", ', ')
        FROM (SELECT DISTINCT "numfait" FROM vue_matrix_rel_axe WHERE numaxe = t.numaxe ORDER BY "numfait")
       ) AS fait,
       (SELECT GROUP_CONCAT("numsd", ', ')
        FROM (SELECT DISTINCT "numsd" FROM vue_matrix_rel_axe WHERE numaxe = t.numaxe ORDER BY "numsd")
       ) AS sd,
		MIN(MbrMinX(l."geometry")) AS x_min,
		MAX(MbrMaxX(l."geometry")) AS x_max,
		MIN(MbrMinY(l."geometry")) AS y_min,
		MAX(MbrMaxY(l."geometry")) AS y_max
FROM (SELECT DISTINCT numaxe FROM vue_matrix_rel_axe) AS t
	JOIN coupe_line AS l ON l."numaxe" = t."numaxe"
GROUP BY t."numaxe"	;


--Vue de vérification des faits et US créés par le BEFORE INSERT et non décrits
DROP VIEW IF EXISTS vue_verif_enregistrement;
CREATE VIEW vue_verif_enregistrement AS
--pour t_fait :
SELECT 't_fait' AS "table", "numfait" AS "numero"
FROM t_fait
WHERE "interpret" IS NULL AND "interpret_alter" IS NULL AND "equiv_diag" IS NULL AND "statut" IS NULL AND "rais_annule" IS NULL AND "fouille" IS NULL AND "profil" IS NULL AND "forme" IS NULL AND "orient" IS NULL AND "orient_calc" IS NULL AND "descrip" IS NULL AND "prof_app" IS NULL AND "diam" IS NULL AND "dim_max" IS NULL AND "dim_min" IS NULL AND "epais" IS NULL AND "prof_haut" IS NULL AND "periode_hors_strati" IS NULL AND "creator" IS NULL AND "note" IS NULL

UNION

--pour t_us
SELECT 't_us' AS "table", "numus" AS "numero"
FROM t_us
WHERE "numfait" IS NULL AND "type_us" IS NULL AND "nature_us" IS NULL AND "interpret" IS NULL AND "description" IS NULL AND "datsup_interpret" IS NULL AND "datinf_interpret" IS NULL AND "datsup_mobilier" IS NULL AND "datinf_mobilier" IS NULL AND "datsup_14c" IS NULL AND "datinf_14c" IS NULL AND "note_dat" IS NULL AND "forme" IS NULL AND "diam" IS NULL AND "dim_max" IS NULL AND "dim_min" IS NULL AND "prof_app" IS NULL AND "zmin" IS NULL AND "zmax" IS NULL AND "epais" IS NULL AND "compo_sediment" IS NULL AND "texture" IS NULL AND "aspect" IS NULL AND "couleur" IS NULL AND "valeur_couleur" IS NULL AND "creator" IS NULL AND "datcreation" IS NULL AND "note" IS NULL AND "num_seq" IS NULL AND "ordre_seq" IS NULL
;

--Vue de vérification des entités saisies dans les tables de relations de n à n et entre les tables en relation de 1 à n. Si elles sont absentes des tables mères elles seront listées ici
DROP VIEW IF EXISTS vue_absents_dans_mere;
CREATE VIEW vue_absents_dans_mere AS
SELECT numentite, table_mere, table_fille
FROM (
    SELECT cast(j.id_us as integer) as numentite, 't_us' as table_mere, 'j_equipe' as table_fille
    FROM j_equipe j
    LEFT JOIN t_us t ON j.id_us = t.id_us
    WHERE t.id_us IS NULL UNION 
    SELECT cast(j.id_photo as integer) as numentite, 't_photo' as table_mere, 'j_equipe' as table_fille
    FROM j_equipe j
    LEFT JOIN t_photo t ON j.id_photo = t.id_photo
    WHERE t.id_photo IS NULL UNION 
    SELECT cast(j.id_minut as integer) as numentite, 't_minute' as table_mere, 'j_equipe' as table_fille
    FROM j_equipe j
    LEFT JOIN t_minute t ON j.id_minut = t.id_minut
    WHERE t.id_minut IS NULL UNION 
    SELECT cast(j.id_prel as integer) as numentite, 't_prelevement' as table_mere, 'j_equipe' as table_fille
    FROM j_equipe j
    LEFT JOIN t_prelevement t ON j.id_prel = t.id_prel
    WHERE t.id_prel IS NULL UNION 
    SELECT cast(j.id_photogram as integer) as numentite, 't_photogram' as table_mere, 'j_equipe' as table_fille
    FROM j_equipe j
    LEFT JOIN t_photogram t ON j.id_photogram = t.id_photogram
    WHERE t.id_photogram IS NULL UNION 
    SELECT cast(j.id_equipe as integer) as numentite, 't_equipe' as table_mere, 'j_equipe_tache' as table_fille
    FROM j_equipe_tache j
    LEFT JOIN t_equipe t ON j.id_equipe = t.id_equipe
    WHERE t.id_equipe IS NULL UNION 
    SELECT cast(j.id_tache as integer) as numentite, 't_tache' as table_mere, 'j_equipe_tache' as table_fille
    FROM j_equipe_tache j
    LEFT JOIN t_tache t ON j.id_tache = t.id_tache
    WHERE t.id_tache IS NULL UNION 
    SELECT cast(j.numphase as integer) as numentite, 't_phase' as table_mere, 'j_phase_per' as table_fille
    FROM j_phase_per j
    LEFT JOIN t_phase t ON j.numphase = t.numphase
    WHERE t.numphase IS NULL UNION 
    SELECT cast(j.numperiod as integer) as numentite, 't_periode' as table_mere, 'j_phase_per' as table_fille
    FROM j_phase_per j
    LEFT JOIN t_periode t ON j.numperiod = t.numperiod
    WHERE t.numperiod IS NULL UNION 
    SELECT cast(j.numaxe as integer) as numentite, 't_axe' as table_mere, 'j_rel_axe' as table_fille
    FROM j_rel_axe j
    LEFT JOIN t_axe t ON j.numaxe = t.numaxe
    WHERE t.numaxe IS NULL UNION 
    SELECT cast(j.numfait as integer) as numentite, 't_fait' as table_mere, 'j_rel_axe' as table_fille
    FROM j_rel_axe j
    LEFT JOIN t_fait t ON j.numfait = t.numfait
    WHERE t.numfait IS NULL UNION 
    SELECT cast(j.numus as integer) as numentite, 't_us' as table_mere, 'j_rel_axe' as table_fille
    FROM j_rel_axe j
    LEFT JOIN t_us t ON j.numus = t.numus
    WHERE t.numus IS NULL UNION 
    SELECT cast(j.numsd as integer) as numentite, 't_sondage' as table_mere, 'j_rel_axe' as table_fille
    FROM j_rel_axe j
    LEFT JOIN t_sondage t ON j.numsd = t.numsd
    WHERE t.numsd IS NULL UNION 
    SELECT cast(j.numtr as integer) as numentite, 't_tranchee' as table_mere, 'j_rel_axe' as table_fille
    FROM j_rel_axe j
    LEFT JOIN t_tranchee t ON j.numtr = t.numtr
    WHERE t.numtr IS NULL UNION 
    SELECT cast(j.numus as integer) as numentite, 't_us' as table_mere, 'j_rel_ens' as table_fille
    FROM j_rel_ens j
    LEFT JOIN t_us t ON j.numus = t.numus
    WHERE t.numus IS NULL UNION 
    SELECT cast(j.numfait as integer) as numentite, 't_fait' as table_mere, 'j_rel_ens' as table_fille
    FROM j_rel_ens j
    LEFT JOIN t_fait t ON j.numfait = t.numfait
    WHERE t.numfait IS NULL UNION 
    SELECT cast(j.numinute as integer) as numentite, 't_minute' as table_mere, 'j_rel_minute' as table_fille
    FROM j_rel_minute j
    LEFT JOIN t_minute t ON j.numinute = t.numinute
    WHERE t.numinute IS NULL UNION 
    SELECT cast(j.numsd as integer) as numentite, 't_sondage' as table_mere, 'j_rel_minute' as table_fille
    FROM j_rel_minute j
    LEFT JOIN t_sondage t ON j.numsd = t.numsd
    WHERE t.numsd IS NULL UNION 
    SELECT cast(j.numtr as integer) as numentite, 't_tranchee' as table_mere, 'j_rel_minute' as table_fille
    FROM j_rel_minute j
    LEFT JOIN t_tranchee t ON j.numtr = t.numtr
    WHERE t.numtr IS NULL UNION 
    SELECT cast(j.numens as integer) as numentite, 't_ens' as table_mere, 'j_rel_minute' as table_fille
    FROM j_rel_minute j
    LEFT JOIN t_ens t ON j.numens = t.numens
    WHERE t.numens IS NULL UNION 
    SELECT cast(j.numfait as integer) as numentite, 't_fait' as table_mere, 'j_rel_minute' as table_fille
    FROM j_rel_minute j
    LEFT JOIN t_fait t ON j.numfait = t.numfait
    WHERE t.numfait IS NULL UNION 
    SELECT cast(j.numus as integer) as numentite, 't_us' as table_mere, 'j_rel_minute' as table_fille
    FROM j_rel_minute j
    LEFT JOIN t_us t ON j.numus = t.numus
    WHERE t.numus IS NULL UNION 
    SELECT cast(j.id_mob as integer) as numentite, 't_mobilier' as table_mere, 'j_rel_minute' as table_fille
    FROM j_rel_minute j
    LEFT JOIN t_mobilier t ON j.id_mob = t.id_mob
    WHERE t.id_mob IS NULL UNION
    SELECT cast(j.numlog as integer) as numentite, 't_log' as table_mere, 'j_rel_minute' as table_fille
    FROM j_rel_minute j
    LEFT JOIN t_log t ON j.numlog = t.numlog
    WHERE t.numlog IS NULL UNION 
    SELECT cast(j.numaxe as integer) as numentite, 't_axe' as table_mere, 'j_rel_minute' as table_fille
    FROM j_rel_minute j
    LEFT JOIN t_axe t ON j.numaxe = t.numaxe
    WHERE t.numaxe IS NULL UNION 
    SELECT cast(j.numphoto as integer) as numentite, 't_photo' as table_mere, 'j_rel_photo' as table_fille
    FROM j_rel_photo j
    LEFT JOIN t_photo t ON j.numphoto = t.numphoto
    WHERE t.numphoto IS NULL UNION 
    SELECT cast(j.numsd as integer) as numentite, 't_sondage' as table_mere, 'j_rel_photo' as table_fille
    FROM j_rel_photo j
    LEFT JOIN t_sondage t ON j.numsd = t.numsd
    WHERE t.numsd IS NULL UNION 
    SELECT cast(j.numtr as integer) as numentite, 't_tranchee' as table_mere, 'j_rel_photo' as table_fille
    FROM j_rel_photo j
    LEFT JOIN t_tranchee t ON j.numtr = t.numtr
    WHERE t.numtr IS NULL UNION 
    SELECT cast(j.numens as integer) as numentite, 't_ens' as table_mere, 'j_rel_photo' as table_fille
    FROM j_rel_photo j
    LEFT JOIN t_ens t ON j.numens = t.numens
    WHERE t.numens IS NULL UNION 
    SELECT cast(j.numfait as integer) as numentite, 't_fait' as table_mere, 'j_rel_photo' as table_fille
    FROM j_rel_photo j
    LEFT JOIN t_fait t ON j.numfait = t.numfait
    WHERE t.numfait IS NULL UNION 
    SELECT cast(j.numus as integer) as numentite, 't_us' as table_mere, 'j_rel_photo' as table_fille
    FROM j_rel_photo j
    LEFT JOIN t_us t ON j.numus = t.numus
    WHERE t.numus IS NULL UNION 
    SELECT cast(j.id_mob as integer) as numentite, 't_mobilier' as table_mere, 'j_rel_photo' as table_fille
    FROM j_rel_photo j
    LEFT JOIN t_mobilier t ON j.id_mob = t.id_mob
    WHERE t.id_mob IS NULL UNION 
    SELECT cast(j.numlog as integer) as numentite, 't_log' as table_mere, 'j_rel_photo' as table_fille
    FROM j_rel_photo j
    LEFT JOIN t_log t ON j.numlog = t.numlog
    WHERE t.numlog IS NULL UNION 
    SELECT cast(j.id_photogram as integer) as numentite, 't_photogram' as table_mere, 'j_rel_photo' as table_fille
    FROM j_rel_photo j
    LEFT JOIN t_photogram t ON j.id_photogram = t.id_photogram
    WHERE t.id_photogram IS NULL UNION 
    SELECT cast(j.id_photogram as integer) as numentite, 't_photogram' as table_mere, 'j_rel_photogram' as table_fille
    FROM j_rel_photogram j
    LEFT JOIN t_photogram t ON j.id_photogram = t.id_photogram
    WHERE t.id_photogram IS NULL UNION 
    SELECT cast(j.id_ptopo as integer) as numentite, 'pts_topo' as table_mere, 'j_rel_photogram' as table_fille
    FROM j_rel_photogram j
    LEFT JOIN pts_topo t ON j.id_ptopo = t.id_ptopo
    WHERE t.id_ptopo IS NULL UNION 
    SELECT cast(j.numfait as integer) as numentite, 't_fait' as table_mere, 'j_rel_photogram' as table_fille
    FROM j_rel_photogram j
    LEFT JOIN t_fait t ON j.numfait = t.numfait
    WHERE t.numfait IS NULL UNION 
    SELECT cast(j.numus as integer) as numentite, 't_us' as table_mere, 'j_rel_photogram' as table_fille
    FROM j_rel_photogram j
    LEFT JOIN t_us t ON j.numus = t.numus
    WHERE t.numus IS NULL UNION 
    SELECT cast(j.numsd as integer) as numentite, 't_sondage' as table_mere, 'j_rel_sondage' as table_fille
    FROM j_rel_sondage j
    LEFT JOIN t_sondage t ON j.numsd = t.numsd
    WHERE t.numsd IS NULL UNION 
    SELECT cast(j.numus as integer) as numentite, 't_us' as table_mere, 'j_rel_sondage' as table_fille
    FROM j_rel_sondage j
    LEFT JOIN t_us t ON j.numus = t.numus
    WHERE t.numus IS NULL UNION 
    SELECT cast(j.numlog as integer) as numentite, 't_log' as table_mere, 'j_rel_sondage' as table_fille
    FROM j_rel_sondage j
    LEFT JOIN t_log t ON j.numlog = t.numlog
    WHERE t.numlog IS NULL UNION 
    SELECT cast(j.numfait as integer) as numentite, 't_fait' as table_mere, 'j_rel_sondage' as table_fille
    FROM j_rel_sondage j
    LEFT JOIN t_fait t ON j.numfait = t.numfait
    WHERE t.numfait IS NULL UNION 
    SELECT cast(j.numtr as integer) as numentite, 't_tranchee' as table_mere, 'j_rel_tranchee' as table_fille
    FROM j_rel_tranchee j
    LEFT JOIN t_tranchee t ON j.numtr = t.numtr
    WHERE t.numtr IS NULL UNION 
    SELECT cast(j.numus as integer) as numentite, 't_us' as table_mere, 'j_rel_tranchee' as table_fille
    FROM j_rel_tranchee j
    LEFT JOIN t_us t ON j.numus = t.numus
    WHERE t.numus IS NULL UNION 
    SELECT cast(j.numfait as integer) as numentite, 't_fait' as table_mere, 'j_rel_tranchee' as table_fille
    FROM j_rel_tranchee j
    LEFT JOIN t_fait t ON j.numfait = t.numfait
    WHERE t.numfait IS NULL UNION 
    SELECT cast(j.numlog as integer) as numentite, 't_log' as table_mere, 'j_rel_tranchee' as table_fille
    FROM j_rel_tranchee j
    LEFT JOIN t_log t ON j.numlog = t.numlog
    WHERE t.numlog IS NULL UNION 
    SELECT cast(j.numseq as integer) as numentite, 't_seq' as table_mere, 'j_seq_phase' as table_fille
    FROM j_seq_phase j
    LEFT JOIN t_seq t ON j.numseq = t.numseq
    WHERE t.numseq IS NULL UNION 
    SELECT cast(j.numphase as integer) as numentite, 't_phase' as table_mere, 'j_seq_phase' as table_fille
    FROM j_seq_phase j
    LEFT JOIN t_phase t ON j.numphase = t.numphase
    WHERE t.numphase IS NULL UNION 
    SELECT cast(j.numus as integer) as numentite, 't_us' as table_mere, 'j_us_log' as table_fille
    FROM j_us_log j
    LEFT JOIN t_us t ON j.numus = t.numus
    WHERE t.numus IS NULL UNION 
    SELECT cast(j.numlog as integer) as numentite, 't_log' as table_mere, 'j_us_log' as table_fille
    FROM j_us_log j
    LEFT JOIN t_log t ON j.numlog = t.numlog
    WHERE t.numlog IS NULL UNION 
    SELECT cast(j.numens1 as integer) as numentite, 't_ens' as table_mere, 'j_rel_ens' as table_fille
    FROM j_rel_ens j
    LEFT JOIN t_ens t ON j.numens1 = t.numens
    WHERE t.numens IS NULL UNION 
    SELECT cast(j.numens2 as integer) as numentite, 't_ens' as table_mere, 'j_rel_ens' as table_fille
    FROM j_rel_ens j
    LEFT JOIN t_ens t ON j.numens2 = t.numens
    WHERE t.numens IS NULL UNION 
    SELECT cast(j.numfait1 as integer) as numentite, 't_fait' as table_mere, 'j_rel_fait' as table_fille
    FROM j_rel_fait j
    LEFT JOIN t_fait t ON j.numfait1 = t.numfait
    WHERE t.numfait IS NULL UNION 
    SELECT cast(j.numfait2 as integer) as numentite, 't_fait' as table_mere, 'j_rel_fait' as table_fille
    FROM j_rel_fait j
    LEFT JOIN t_fait t ON j.numfait2 = t.numfait
    WHERE t.numfait IS NULL UNION 
    SELECT cast(j.numus1 as integer) as numentite, 't_us' as table_mere, 'j_rel_us' as table_fille
    FROM j_rel_us j
    LEFT JOIN t_us t ON j.numus1 = t.numus
    WHERE t.numus IS NULL UNION 
    SELECT cast(j.numus2 as integer) as numentite, 't_us' as table_mere, 'j_rel_us' as table_fille
    FROM j_rel_us j
    LEFT JOIN t_us t ON j.numus2 = t.numus
    WHERE t.numus IS NULL UNION
    -- Relations directes 1-à-n : tables filles vers tables mères
    -- t_us → t_fait
    SELECT cast(u.numfait as integer) as numentite, 't_fait' as table_mere, 't_us' as table_fille
    FROM t_us u
    LEFT JOIN t_fait t ON u.numfait = t.numfait
    WHERE t.numfait IS NULL AND u.numfait IS NOT NULL UNION
    -- t_us → t_seq
    SELECT cast(u.num_seq as integer) as numentite, 't_seq' as table_mere, 't_us' as table_fille
    FROM t_us u
    LEFT JOIN t_seq t ON u.num_seq = t.numseq
    WHERE t.numseq IS NULL AND u.num_seq IS NOT NULL UNION
    -- t_sondage → t_tranchee
    SELECT cast(s.numtr as integer) as numentite, 't_tranchee' as table_mere, 't_sondage' as table_fille
    FROM t_sondage s
    LEFT JOIN t_tranchee t ON s.numtr = t.numtr
    WHERE t.numtr IS NULL AND s.numtr IS NOT NULL UNION
    -- t_mobilier → point
    SELECT cast(m.numpoint as integer) as numentite, 'point' as table_mere, 't_mobilier' as table_fille
    FROM t_mobilier m
    LEFT JOIN point t ON m.numpoint = t.numpoint
    WHERE t.numpoint IS NULL AND m.numpoint IS NOT NULL UNION
    -- t_mobilier → t_us
    SELECT cast(m.numus as integer) as numentite, 't_us' as table_mere, 't_mobilier' as table_fille
    FROM t_mobilier m
    LEFT JOIN t_us t ON m.numus = t.numus
    WHERE t.numus IS NULL AND m.numus IS NOT NULL UNION
    -- t_mobilier → t_fait
    SELECT cast(m.numfait as integer) as numentite, 't_fait' as table_mere, 't_mobilier' as table_fille
    FROM t_mobilier m
    LEFT JOIN t_fait t ON m.numfait = t.numfait
    WHERE t.numfait IS NULL AND m.numfait IS NOT NULL UNION
    -- t_mobilier → t_sondage
    SELECT cast(m.numsd as integer) as numentite, 't_sondage' as table_mere, 't_mobilier' as table_fille
    FROM t_mobilier m
    LEFT JOIN t_sondage t ON m.numsd = t.numsd
    WHERE t.numsd IS NULL AND m.numsd IS NOT NULL UNION
    -- t_log → t_tranchee
    SELECT cast(l.numtr as integer) as numentite, 't_tranchee' as table_mere, 't_log' as table_fille
    FROM t_log l
    LEFT JOIN t_tranchee t ON l.numtr = t.numtr
    WHERE t.numtr IS NULL AND l.numtr IS NOT NULL UNION
    -- t_log → t_sondage
    SELECT cast(l.numsd as integer) as numentite, 't_sondage' as table_mere, 't_log' as table_fille
    FROM t_log l
    LEFT JOIN t_sondage t ON l.numsd = t.numsd
    WHERE t.numsd IS NULL AND l.numsd IS NOT NULL UNION
    -- t_log → t_axe
    SELECT cast(l.numaxe as integer) as numentite, 't_axe' as table_mere, 't_log' as table_fille
    FROM t_log l
    LEFT JOIN t_axe t ON l.numaxe = t.numaxe
    WHERE t.numaxe IS NULL AND l.numaxe IS NOT NULL UNION
    -- t_prelevement → t_us
    SELECT cast(p.numus as integer) as numentite, 't_us' as table_mere, 't_prelevement' as table_fille
    FROM t_prelevement p
    LEFT JOIN t_us t ON p.numus = t.numus
    WHERE t.numus IS NULL AND p.numus IS NOT NULL UNION
    -- t_prelevement → t_fait
    SELECT cast(p.numfait as integer) as numentite, 't_fait' as table_mere, 't_prelevement' as table_fille
    FROM t_prelevement p
    LEFT JOIN t_fait t ON p.numfait = t.numfait
    WHERE t.numfait IS NULL AND p.numfait IS NOT NULL UNION
    -- t_minute → t_photogram
    SELECT cast(m.id_photogram as integer) as numentite, 't_photogram' as table_mere, 't_minute' as table_fille
    FROM t_minute m
    LEFT JOIN t_photogram t ON m.id_photogram = t.id_photogram
    WHERE t.id_photogram IS NULL AND m.id_photogram IS NOT NULL UNION
    -- t_ens → t_ens (ensemble englobant, auto-référence)
    SELECT cast(e.ens_englob as integer) as numentite, 't_ens' as table_mere, 't_ens' as table_fille
    FROM t_ens e
    LEFT JOIN t_ens t ON e.ens_englob = t.numens
    WHERE t.numens IS NULL AND e.ens_englob IS NOT NULL UNION
    -- t_prodphotogram → t_photogram
    SELECT cast(pp.id_photogram as integer) as numentite, 't_photogram' as table_mere, 't_prodphotogram' as table_fille
    FROM t_prodphotogram pp
    LEFT JOIN t_photogram t ON pp.id_photogram = t.id_photogram
    WHERE t.id_photogram IS NULL AND pp.id_photogram IS NOT NULL UNION
    -- coupe_line → t_axe
    SELECT cast(cl.numaxe as integer) as numentite, 't_axe' as table_mere, 'coupe_line' as table_fille
    FROM coupe_line cl
    LEFT JOIN t_axe t ON cl.numaxe = t.numaxe
    WHERE t.numaxe IS NULL AND cl.numaxe IS NOT NULL UNION
    -- coupe_line → t_fait
    SELECT cast(cl.numfait as integer) as numentite, 't_fait' as table_mere, 'coupe_line' as table_fille
    FROM coupe_line cl
    LEFT JOIN t_fait t ON cl.numfait = t.numfait
    WHERE t.numfait IS NULL AND cl.numfait IS NOT NULL UNION
    -- coupe_line → t_us
    SELECT cast(cl.numus as integer) as numentite, 't_us' as table_mere, 'coupe_line' as table_fille
    FROM coupe_line cl
    LEFT JOIN t_us t ON cl.numus = t.numus
    WHERE t.numus IS NULL AND cl.numus IS NOT NULL UNION
    -- coupe_line → t_sondage
    SELECT cast(cl.numsd as integer) as numentite, 't_sondage' as table_mere, 'coupe_line' as table_fille
    FROM coupe_line cl
    LEFT JOIN t_sondage t ON cl.numsd = t.numsd
    WHERE t.numsd IS NULL AND cl.numsd IS NOT NULL UNION
    -- coupe_poly → t_axe
    SELECT cast(cp.numaxe as integer) as numentite, 't_axe' as table_mere, 'coupe_poly' as table_fille
    FROM coupe_poly cp
    LEFT JOIN t_axe t ON cp.numaxe = t.numaxe
    WHERE t.numaxe IS NULL AND cp.numaxe IS NOT NULL UNION
    -- coupe_poly → t_fait
    SELECT cast(cp.numfait as integer) as numentite, 't_fait' as table_mere, 'coupe_poly' as table_fille
    FROM coupe_poly cp
    LEFT JOIN t_fait t ON cp.numfait = t.numfait
    WHERE t.numfait IS NULL AND cp.numfait IS NOT NULL UNION
    -- coupe_poly → t_us
    SELECT cast(cp.numus as integer) as numentite, 't_us' as table_mere, 'coupe_poly' as table_fille
    FROM coupe_poly cp
    LEFT JOIN t_us t ON cp.numus = t.numus
    WHERE t.numus IS NULL AND cp.numus IS NOT NULL UNION
    -- coupe_poly → t_sondage
    SELECT cast(cp.numsd as integer) as numentite, 't_sondage' as table_mere, 'coupe_poly' as table_fille
    FROM coupe_poly cp
    LEFT JOIN t_sondage t ON cp.numsd = t.numsd
    WHERE t.numsd IS NULL AND cp.numsd IS NOT NULL UNION
    -- coupe_axe → t_axe
    SELECT cast(ca.numaxe as integer) as numentite, 't_axe' as table_mere, 'coupe_axe' as table_fille
    FROM coupe_axe ca
    LEFT JOIN t_axe t ON ca.numaxe = t.numaxe
    WHERE t.numaxe IS NULL AND ca.numaxe IS NOT NULL UNION
    -- coupe_axe → t_minute
    SELECT cast(ca.numinute as integer) as numentite, 't_minute' as table_mere, 'coupe_axe' as table_fille
    FROM coupe_axe ca
    LEFT JOIN t_minute t ON ca.numinute = t.numinute
    WHERE t.numinute IS NULL AND ca.numinute IS NOT NULL UNION
    -- coupe_axe → t_sondage
    SELECT cast(ca.numsd as integer) as numentite, 't_sondage' as table_mere, 'coupe_axe' as table_fille
    FROM coupe_axe ca
    LEFT JOIN t_sondage t ON ca.numsd = t.numsd
    WHERE t.numsd IS NULL AND ca.numsd IS NOT NULL)
	WHERE numentite IS NOT NULL;


/*Les vues géométriques*/

-- Vue de jointure des périodes et des faits
DROP VIEW IF EXISTS vue_fait_periode;
CREATE VIEW vue_fait_periode AS
WITH distinct_data AS (
    SELECT DISTINCT
        numfait,
        geometry,
        numperiod
    FROM (
        SELECT
            f.numfait,
            f.geometry,
            jpp.numperiod
        FROM
            t_fait as f
        LEFT JOIN
            t_us as us ON f.numfait = us.numfait
        LEFT JOIN
            t_seq as seq ON us.num_seq = seq.numseq
        LEFT JOIN
            j_seq_phase as jsp ON jsp.numseq = seq.numseq
        LEFT JOIN
            j_phase_per as jpp ON jpp.numphase = jsp.numphase
    ) AS subquery
    WHERE numperiod IS NOT NULL
)
SELECT
    ROW_NUMBER() OVER() AS id_unique,
    numfait,
    geometry,
    numperiod
FROM
    distinct_data;
    DELETE FROM views_geometry_columns WHERE view_name = 'vue_fait_periode';
    INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
  VALUES ('vue_fait_periode', 'geometry', 'vid', 'ghost_polygon_2154', 'geometry','1');

-- Vue de jointure des phases et des faits
DROP VIEW IF EXISTS vue_fait_phase;
CREATE VIEW vue_fait_phase AS
WITH distinct_data AS (
    SELECT DISTINCT
        numfait,
        geometry,
        numphase
    FROM (
        SELECT
            f.numfait,
            f.geometry,
            jsp.numphase
        FROM
            t_fait AS f
        LEFT JOIN
            t_us AS us ON f.numfait = us.numfait
        LEFT JOIN
            t_seq AS seq ON us.num_seq = seq.numseq
        LEFT JOIN
            j_seq_phase AS jsp ON jsp.numseq = seq.numseq
    ) AS subquery
    WHERE numphase IS NOT NULL
)
SELECT
    ROW_NUMBER() OVER() AS vid,
    numfait,
    geometry,
    numphase
FROM
    distinct_data;
--interprétation de la géométrie de la vue
DELETE FROM views_geometry_columns WHERE view_name = 'vue_fait_phase';
INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
  VALUES ('vue_fait_phase', 'geometry', 'vid', 'ghost_polygon_2154', 'geometry','1');

-- Vue de visualisation des logs stratigraphiques
DROP VIEW IF EXISTS vue_log_stratigraphique;
CREATE VIEW vue_log_stratigraphique AS
SELECT 
    t.*,
    l.numtr,
    l.zmin_log, 
        BuildMbr(
            t."numlog" - 1,
            CASE
                WHEN "zmin_uslog" IS NOT NULL         THEN "zmin_uslog"
                WHEN t."zmax_uslog" = l."zmin_log"    THEN t."zmax_uslog" - 0.10
                ELSE                                       l."zmin_log"
            END,
            t."numlog" - 0.75,
            t."zmax_uslog",
            2154
        ) AS geometry
FROM j_us_log AS t 
JOIN t_log AS l 
ON t."numlog" = l."numlog";
--Interprétation de la géométrie de la vue, voir ici : http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/sp-view.html et référencement de la géométrie dans la table views_geometry_columns d'après la couche fantôme ghost_polygon_2154
DELETE FROM views_geometry_columns WHERE view_name = 'vue_log_stratigraphique';
INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
  VALUES ('vue_log_stratigraphique', 'geometry', 'id_us_log', 'ghost_polygon_2154', 'geometry','1');


--Pour Caviar table prescription
DROP VIEW IF EXISTS caviar_prescription;
CREATE VIEW caviar_prescription AS
   SELECT 
   "gid_caviar" as gid,
   "typemp",
   "numoa",
   "tranche",
   "geometry"
   FROM emprise;
 -- interprétation de la géométrie de la vue
DELETE FROM views_geometry_columns WHERE view_name = 'caviar_prescription';
INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
  VALUES ('caviar_prescription', 'geometry', 'numope', 'ghost_polygon_2154', 'geometry','1');

--Pour Caviar table ouverture
DROP VIEW IF EXISTS caviar_ouverture;
CREATE VIEW caviar_ouverture AS
 SELECT 
   e."gid_caviar" as gidoperef,
   o."numouvert",
   o."typouvert",
   o."geometry",
   o."id_ouverture"
   FROM ouverture AS o
   JOIN emprise as e
   ON st_intersects(st_buffer(e.geometry,1000),o.geometry)
   WHERE e."gid_caviar" IS NOT NULL;
 -- interprétation de la géométrie de la vue
DELETE FROM views_geometry_columns WHERE view_name = 'caviar_ouverture';
INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
  VALUES ('caviar_ouverture', 'geometry', 'id_ouverture', 'ghost_polygon_2154', 'geometry','1');
  
--Pour Caviar table uniteobservation
DROP VIEW IF EXISTS caviar_uniteobservation;
CREATE VIEW caviar_uniteobservation AS
   SELECT 
   e."gid_caviar" as gidoperef,
   p."numpoly" AS "numunobs",
   p."typoly" AS "typunobs",
   p."interpret",
   p."datedebut",
   p."datefin",
   p."geometry",
   p."id_poly"
   FROM poly as p
   JOIN emprise as e
   ON st_intersects(st_buffer(e.geometry,1000),p.geometry)
   WHERE e."gid_caviar" IS NOT NULL;
 -- interprétation de la géométrie de la vue
DELETE FROM views_geometry_columns WHERE view_name = 'caviar_uniteobservation';
INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
  VALUES ('caviar_uniteobservation', 'geometry', 'id_poly', 'ghost_polygon_2154', 'geometry','1');
  
--Pour Caviar table uniteobservation point
DROP VIEW IF EXISTS caviar_uniteobservation_point;
CREATE VIEW caviar_uniteobservation_point AS
   SELECT 
   e."gid_caviar" as gidoperef,
   p."numpoint" AS "numunobs",
   p."typoint" AS "typunobs",
   p."interpret",
   p."datedebut",
   p."datefin",
   p."geometry",
   p."id_point"
   FROM point AS p
   JOIN emprise as e 
   ON st_intersects(st_buffer(e.geometry,1000),p.geometry)
   WHERE e."gid_caviar" IS NOT NULL;
 -- interprétation de la géométrie de la vue
DELETE FROM views_geometry_columns WHERE view_name = 'caviar_uniteobservation_point';
INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
  VALUES ('caviar_uniteobservation_point', 'geometry', 'id_point', 'ghost_point_2154', 'geometry','1');
  
--Vue de translation des coupes sur le plan
--pour les axes
DROP VIEW IF EXISTS vue_axe_coupe_translate;
CREATE VIEW vue_axe_coupe_translate AS 
SELECT a."id_axe",a."numaxe",c."numinute",c."azimuth",c."alti", 
st_translate(c."geometry", x(startpoint(a."geometry"))-x(startpoint(c."geometry")),y(startpoint(a."geometry"))-y(startpoint(c."geometry")),0) AS geometry --requête de translation des coordonnées en coupe vers les coordonnées en plan
FROM coupe_axe AS c 
JOIN t_axe AS a ON a."numaxe" = c."numaxe";
--interprétation de la géométrie de la vue
DELETE FROM views_geometry_columns WHERE view_name = 'vue_axe_coupe_translate';
INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
  VALUES ('vue_axe_coupe_translate', 'geometry', 'id_axe', 'ghost_polyline_2154', 'geometry','1');    
  
--pour les lignes
DROP VIEW IF EXISTS vue_line_coupe_translate;
CREATE VIEW vue_line_coupe_translate AS 
SELECT l."id_cpline",a."id_axe",a."numaxe","numfait","numus","typline", 
st_translate(l."geometry", x(startpoint(a."geometry"))-x(startpoint(c."geometry")),y(startpoint(a."geometry"))-y(startpoint(c."geometry")),0) AS geometry --requête de translation des coordonnées en coupe vers les coordonnées en plan
FROM coupe_line AS l 
JOIN coupe_axe AS c ON c."numaxe" = l."numaxe" 
JOIN t_axe AS a ON a."numaxe" = l."numaxe" ;
--interprétation de la géométrie de la vue
DELETE FROM views_geometry_columns WHERE view_name = 'vue_line_coupe_translate';
INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
  VALUES ('vue_line_coupe_translate', 'geometry', 'id_cpline', 'ghost_polyline_2154', 'geometry','1');    

--pour les polygones
DROP VIEW IF EXISTS vue_poly_coupe_translate;
CREATE VIEW vue_poly_coupe_translate AS 
SELECT p."id_cpoly",a."id_axe",a."numaxe","numfait","numus","typoly","detail", 
st_translate(p."geometry", x(startpoint(a."geometry"))-x(startpoint(c."geometry")),y(startpoint(a."geometry"))-y(startpoint(c."geometry")),0) as geometry --requête de translation des coordonnées en coupe vers les coordonnées en plan
FROM coupe_poly AS p 
JOIN coupe_axe AS c ON c."numaxe" = p."numaxe" 
JOIN t_axe AS a ON a."numaxe" = c."numaxe";
--interprétation de la géométrie de la vue
DELETE FROM views_geometry_columns WHERE view_name = 'vue_poly_coupe_translate';
INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
  VALUES ('vue_poly_coupe_translate', 'geometry', 'id_cpoly', 'ghost_polygon_2154', 'geometry','1');   

--Vue de translation et rotation des coupes sur le plan
--pour les lignes
DROP VIEW IF EXISTS vue_line_coupe_translate_rotate;
CREATE VIEW vue_line_coupe_translate_rotate AS 
SELECT l."id_cpline",a."id_axe",a."numaxe","numfait","numus","typline","numsd", 
shiftcoords( rotatecoords( shiftcoords(l.geometry,-x(startpoint(a.geometry)),-y(startpoint(a.geometry))) ,c.azimuth-90) ,x(startpoint(a.geometry)),y(startpoint(a.geometry))) AS geometry --requête de translation des coordonnées en coupe vers les coordonnées en plan
FROM vue_line_coupe_translate AS l 
JOIN coupe_axe AS c ON c."numaxe" = l."numaxe" 
JOIN t_axe AS a ON a."numaxe" = l."numaxe" ;
--interprétation de la géométrie de la vue
DELETE FROM views_geometry_columns WHERE view_name = 'vue_line_coupe_translate_rotate';
INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
  VALUES ('vue_line_coupe_translate_rotate', 'geometry', 'id_cpline', 'ghost_polyline_2154', 'geometry','1');    

--pour les polygones
DROP VIEW IF EXISTS vue_poly_coupe_translate_rotate;
CREATE VIEW vue_poly_coupe_translate_rotate AS 
SELECT p."id_cpoly",a."id_axe",a."numaxe","numfait","numus","typoly","detail","numsd", 
shiftcoords(rotatecoords( shiftcoords(p.geometry,-x(startpoint(a.geometry)),-y(startpoint(a.geometry))) ,c.azimuth-90) ,x(startpoint(a.geometry)),y(startpoint(a.geometry))) as geometry --requête de translation des coordonnées en coupe vers les coordonnées en plan
FROM vue_poly_coupe_translate AS p 
JOIN coupe_axe AS c ON c."numaxe" = p."numaxe" 
JOIN t_axe AS a ON a."numaxe" = c."numaxe";
--interprétation de la géométrie de la vue
DELETE FROM views_geometry_columns WHERE view_name = 'vue_poly_coupe_translate_rotate';
INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
  VALUES ('vue_poly_coupe_translate_rotate', 'geometry', 'id_cpoly', 'ghost_polygon_2154', 'geometry','1');  
  
--Vue de translation des US négatives en coupe sur un axe médian  
DROP VIEW IF EXISTS vue_cumulcoupe_us_negative;
CREATE VIEW vue_cumulcoupe_us_negative AS 
SELECT l."id_cpline",t."id_axe", a."numaxe", l."numus", l."numfait", l."numsd", 
shiftcoords(l."geometry", -x(st_centroid(l."geometry"))-10 ,0) AS geometry --requête de translation vers un axe médian à -10 m en X et en y = z
FROM coupe_line AS l 
JOIN coupe_axe AS a ON a."numaxe" = l."numaxe"
JOIN t_axe AS t ON t."numaxe" = l."numaxe" 
WHERE "typline" LIKE 'US négative';
--interprétation de la géométrie de la vue
DELETE FROM views_geometry_columns WHERE view_name = 'vue_cumulcoupe_us_negative';
INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
  VALUES ('vue_cumulcoupe_us_negative', 'geometry', 'id_cpline', 'ghost_polyline_2154', 'geometry','1'); 

-- Vue de localisation de l'emprise d'une US le long de l'axe en plan selon le dessin en coupe
DROP VIEW IF EXISTS v_coupe_emprises_frac_numaxe;

CREATE VIEW v_coupe_emprises_frac_numaxe AS
WITH
axes AS (
  SELECT a.numaxe, a.geometry AS ageom, Length(a.geometry) AS alen
  FROM t_axe a
),
caxes_longest AS (
  SELECT numaxe, MAX(Length(geometry)) AS maxlen
  FROM coupe_axe
  WHERE numaxe IS NOT NULL
  GROUP BY numaxe
),
caxes AS (
  SELECT c.numaxe, c.geometry AS cgeom, Length(c.geometry) AS clen
  FROM coupe_axe c
  JOIN caxes_longest m
    ON m.numaxe = c.numaxe AND Length(c.geometry) = m.maxlen
),
ax AS (
  SELECT a.numaxe, a.ageom, a.alen, cx.cgeom, cx.clen
  FROM axes a
  JOIN caxes cx USING (numaxe)
),
poly_bbox AS (
  SELECT 'poly' AS src, p.id_cpoly AS src_id, p.numaxe, p.numus,
         MbrMinX(p.geometry) AS xmin, MbrMaxX(p.geometry) AS xmax,
         MbrMinY(p.geometry) AS ymin, MbrMaxY(p.geometry) AS ymax
  FROM coupe_poly p
  WHERE p.typoly LIKE 'US%' AND p.numus IS NOT NULL AND p.numaxe IS NOT NULL
),
line_bbox AS (
  SELECT 'line' AS src, l.id_cpline AS src_id, l.numaxe, l.numus,
         MbrMinX(l.geometry) AS xmin, MbrMaxX(l.geometry) AS xmax,
         MbrMinY(l.geometry) AS ymin, MbrMaxY(l.geometry) AS ymax
  FROM coupe_line l
  WHERE l.numus IS NOT NULL AND l.numaxe IS NOT NULL
),
items AS (
  SELECT * FROM poly_bbox
  UNION ALL
  SELECT * FROM line_bbox
),
corners AS (
  SELECT src, src_id, numaxe, numus, xmin AS cx, ymin AS cy, ymin AS y0, ymax AS y1 FROM items
  UNION ALL SELECT src, src_id, numaxe, numus, xmin, ymax, ymin, ymax FROM items
  UNION ALL SELECT src, src_id, numaxe, numus, xmax, ymin, ymin, ymax FROM items
  UNION ALL SELECT src, src_id, numaxe, numus, xmax, ymax, ymin, ymax FROM items
),
proj AS (
  SELECT
    c.src, c.src_id, c.numaxe, c.numus,
    Line_Locate_Point(ax.cgeom, MakePoint(c.cx, c.cy, 2154)) AS frac,
    c.y0 AS ymin_keep, c.y1 AS ymax_keep
  FROM corners c
  JOIN ax USING (numaxe)
),
agg AS (
  SELECT
    src, src_id, numaxe, numus,
    MAX(0.0, MIN(1.0, MIN(frac))) AS f0,
    MAX(0.0, MIN(1.0, MAX(frac))) AS f1,
    MIN(ymin_keep) AS y_min_m,
    MAX(ymax_keep) AS y_max_m
  FROM proj
  GROUP BY src, src_id, numaxe, numus
),
safe AS (
  SELECT
    src, src_id, numaxe, numus, y_min_m, y_max_m,
    CASE WHEN ABS(f1 - f0) < 1e-9 THEN MAX(f0 - 1e-6, 0.0) ELSE f0 END AS f_start,
    CASE WHEN ABS(f1 - f0) < 1e-9 THEN MIN(f1 + 1e-6, 1.0) ELSE f1 END AS f_end
  FROM agg
)
SELECT
  (s.numaxe * 2000000000) + (CASE WHEN s.src='poly' THEN 0 ELSE 1000000000 END) + s.src_id AS ROWID,
  s.src,
  s.src_id,                 -- id_cpoly / id_cpline
  s.numaxe,
  s.numus,
  ROUND(s.f_start * ax.alen, 3) AS x_min_m,               -- sur t_axe (m)
  ROUND(s.f_end   * ax.alen, 3) AS x_max_m,
  ROUND((s.f_end - s.f_start) * ax.alen, 3) AS width_m,
  ROUND(s.y_min_m, 3) AS y_min_m,                         -- altitudes (depuis coupe_*)
  ROUND(s.y_max_m, 3) AS y_max_m,
  ROUND(s.y_max_m - s.y_min_m, 3) AS height_m,
  Line_Substring(ax.ageom, s.f_start, s.f_end) AS geometry
FROM safe s
JOIN ax USING (numaxe)
ORDER BY s.numaxe, s.numus, s.src, s.src_id;

-- Enregistrer la vue spatiale pour QGIS
DELETE FROM views_geometry_columns WHERE view_name = 'v_coupe_emprises_frac_numaxe';
INSERT INTO views_geometry_columns
(view_name,                        view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
VALUES
('v_coupe_emprises_frac_numaxe',   'geometry',    'rowid',    't_axe',      'geometry',        1);


--Vue de pointage des erreurs de géométrie
DROP VIEW IF EXISTS erreur_geometry;
CREATE VIEW erreur_geometry AS
SELECT rowid, 'coupe_line' as "table", ST_ISVALIDREASON(geometry) as erreur_geometrie, ST_ISVALIDDETAIL(geometry) as geometry
FROM coupe_line
WHERE ST_ISVALID(geometry) = 0 
UNION
SELECT rowid, 'coupe_poly' as "table", ST_ISVALIDREASON(geometry) as erreur_geometrie, ST_ISVALIDDETAIL(geometry) as geometry
FROM coupe_poly
WHERE ST_ISVALID(geometry) = 0
UNION
SELECT rowid, 'emprise' as "table", ST_ISVALIDREASON(geometry) as erreur_geometrie, ST_ISVALIDDETAIL(geometry) as geometry
FROM emprise
WHERE ST_ISVALID(geometry) = 0
UNION
SELECT rowid, 'ouverture' as "table", ST_ISVALIDREASON(geometry) as erreur_geometrie, ST_ISVALIDDETAIL(geometry) as geometry
FROM ouverture
WHERE ST_ISVALID(geometry) = 0
UNION
SELECT rowid, 'poly' as "table", ST_ISVALIDREASON(geometry) as erreur_geometrie, ST_ISVALIDDETAIL(geometry) as geometry
FROM poly
WHERE ST_ISVALID(geometry) = 0
UNION
SELECT rowid, 't_ens' as "table", ST_ISVALIDREASON(geometry) as erreur_geometrie, ST_ISVALIDDETAIL(geometry) as geometry
FROM t_ens
WHERE ST_ISVALID(geometry) = 0
UNION
SELECT rowid, 't_fait' as "table", ST_ISVALIDREASON(geometry) as erreur_geometrie, ST_ISVALIDDETAIL(geometry) as geometry
FROM t_fait
WHERE ST_ISVALID(geometry) = 0
UNION
SELECT rowid, 't_prodphotogram' as "table", ST_ISVALIDREASON(geometry) as erreur_geometrie, ST_ISVALIDDETAIL(geometry) as geometry
FROM t_prodphotogram
WHERE ST_ISVALID(geometry) = 0
UNION
SELECT rowid, 't_photogram' as "table", ST_ISVALIDREASON(geometry) as erreur_geometrie, ST_ISVALIDDETAIL(geometry) as geometry
FROM t_photogram
WHERE ST_ISVALID(geometry) = 0
UNION
SELECT rowid, 't_sondage' as "table", ST_ISVALIDREASON(geometry) as erreur_geometrie, ST_ISVALIDDETAIL(geometry) as geometry
FROM t_sondage
WHERE ST_ISVALID(geometry) = 0
UNION
SELECT rowid, 't_tranchee' as "table", ST_ISVALIDREASON(geometry) as erreur_geometrie, ST_ISVALIDDETAIL(geometry) as geometry
FROM t_tranchee
WHERE ST_ISVALID(geometry) = 0
UNION
SELECT rowid, 't_us' as "table", ST_ISVALIDREASON(geometry) as erreur_geometrie, ST_ISVALIDDETAIL(geometry) as geometry
FROM t_us
WHERE ST_ISVALID(geometry) = 0;
--interprétation de la géométrie de la vue
DELETE FROM views_geometry_columns WHERE view_name = 'erreur_geometry';
INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
  VALUES ('erreur_geometry', 'geometry', 'rowid', 'ghost_point_2154', 'geometry','1');  
  
/*Vue de projection des axes en plan en coupe. Attention ! Cette vue ne doit pas servir de support pour le dessin des coupes, son contenu, une fois consolider, doit intégrer la couche coupe_axe*/
DROP VIEW IF EXISTS vue_coupe_axe;
CREATE VIEW vue_coupe_axe AS 
SELECT "id_axe", "numaxe", "nomaxe",
degrees(st_azimuth(st_startpoint(geometry),st_endpoint(geometry))) as azimuth,
st_length(geometry) as long_axe,
"alti",
"note",
makeline(makepoint((SUM(st_length(geometry)) OVER(ORDER BY "id_axe" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))-st_length(geometry)+"id_axe"* 5 - 5 , "alti"),
makepoint((SUM(st_length(geometry)) OVER(ORDER BY "id_axe" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))+"id_axe"* 5 - 5 ,"alti")) as geometry
FROM t_axe;
--interprétation de la géométrie de la vue
DELETE FROM views_geometry_columns WHERE view_name = 'vue_coupe_axe';
INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
  VALUES ('vue_coupe_axe', 'geometry', 'id_axe', 'ghost_polyline_2154', 'geometry','1'); 

-- Vue de récapitulation des altitudes, largeurs et profondeurs des faits par phase selon les données dans coupe_poly
-- Métriques globales (à l'échelle du fait) : toit_fait, fond_fait, profondeur_conservee_globale, largeur_max_fait, numaxe_max
-- Métriques par phase : toit_phase, fond_phase, profondeur_phase
-- numaxe_max indique l'axe sur lequel la largeur maximale a été mesurée (option B : max global + axe de référence)
DROP VIEW IF EXISTS vue_coupe_poly_recap_metriq;
CREATE VIEW vue_coupe_poly_recap_metriq AS
WITH max_altitude AS (
    -- Toit global du fait : altitude maximale toutes US confondues
    SELECT
        numfait,
        MAX(MbrMaxY(geometry)) AS max_altitude
    FROM coupe_poly
    WHERE numfait IS NOT NULL
    GROUP BY numfait
),
min_altitude_globale AS (
    -- Fond global du fait : altitude minimale toutes US et phases confondues
    SELECT
        numfait,
        MIN(MbrMinY(geometry)) AS min_altitude
    FROM coupe_poly
    WHERE numfait IS NOT NULL
    GROUP BY numfait
),
altitude_par_phase AS (
    -- Toit et fond par phase, en joignant directement t_us et j_seq_phase (plus léger qu'inventaire_us)
    SELECT
        cp.numfait,
        ph.numphase,
        MAX(MbrMaxY(cp.geometry)) AS max_altitude_phase,
        MIN(MbrMinY(cp.geometry)) AS min_altitude_phase
    FROM coupe_poly AS cp
    JOIN t_us AS u ON u.numus = cp.numus
    LEFT JOIN j_seq_phase AS ph ON ph.numseq = u.num_seq
    WHERE cp.numfait IS NOT NULL
    GROUP BY cp.numfait, ph.numphase
),
width_per_axis AS (
    -- Largeur max par fait et par axe (bounding box X)
    SELECT
        numfait,
        numaxe,
        MAX(MbrMaxX(geometry) - MbrMinX(geometry)) AS max_width
    FROM coupe_poly
    WHERE numfait IS NOT NULL
    GROUP BY numfait, numaxe
),
max_width AS (
    -- Sélection de l'axe présentant la plus grande largeur par fait (ROW_NUMBER pour dédoublonnage en cas d'égalité)
    SELECT
        numfait,
        numaxe AS numaxe_max,
        max_width,
        ROW_NUMBER() OVER (PARTITION BY numfait ORDER BY max_width DESC) AS rn
    FROM width_per_axis
)

SELECT
    ma.numfait,
    -- métriques globales
    ma.max_altitude                                   AS toit_fait,
    mig.min_altitude                                  AS fond_fait,
    (ma.max_altitude - mig.min_altitude)              AS profondeur_conservee_globale,
    mw.max_width                                      AS largeur_max_fait,
    mw.numaxe_max,
    -- métriques par phase
    app.numphase,
    app.max_altitude_phase                            AS toit_phase,
    app.min_altitude_phase                            AS fond_phase,
    (app.max_altitude_phase - app.min_altitude_phase) AS profondeur_phase
FROM max_altitude AS ma
JOIN min_altitude_globale AS mig ON ma.numfait = mig.numfait
JOIN altitude_par_phase AS app ON ma.numfait = app.numfait
JOIN max_width AS mw ON ma.numfait = mw.numfait
WHERE mw.rn = 1;
  
----------------------------------------------------------------------------------------------------------------------
--LES TABLES, VUES ET TRIGGERS CI-APRÈS PERMETTENT LA CRÉATION D'ENTITÉ EN SÉRIES

/*Tables de création d'entité rapide pour le terrain*/
--Table de création
DROP TABLE IF EXISTS entite_rapide;
CREATE TABLE entite_rapide(
	"id_entite" INTEGER PRIMARY KEY,
	"id_serie" INTEGER REFERENCES "serie"("id_serie") DEFAULT NULL,
	"numentite" INTEGER, -- l'incrément créé à partir de la série sélectionnée dans "id_serie"
	"interpret" TEXT, -- permet d'ajouter une interprétation rapide
	"note" TEXT
	);
	
--Table de création de séries d'incrément par spécification du nombre de départ et du pas
DROP TABLE IF EXISTS serie;
CREATE TABLE serie(
	"id_serie" INTEGER PRIMARY KEY,
	"nombre_depart" INTEGER,
	"nombre_arrivee" INTEGER,
	"pas" INTEGER,
	"tablentite" TEXT,
	"note" TEXT
	); 

--Vue de récapitulation des numéros déjà saisi et à saisir sur les tables par série
DROP VIEW IF EXISTS vue_numaximum_serie;
CREATE VIEW vue_numaximum_serie AS
SELECT s."id_serie", 't_us' AS "tablentite", max(t."numus") AS "numaximum",
				CASE WHEN max(t."numus") IS NULL THEN s."nombre_depart" ELSE max(t."numus")+s."pas" END AS "new_numentite"
				FROM serie AS s 
				LEFT JOIN t_us AS t ON t."numus" >= s."nombre_depart" AND t."numus" <= s."nombre_arrivee"
				WHERE s."tablentite" like 't_us'
				GROUP BY s."id_serie"
				UNION
				SELECT s."id_serie", 't_fait' AS "tablentite", max(t."numfait") AS "numaximum",
				CASE WHEN max(t."numfait") IS NULL THEN s."nombre_depart" ELSE max(t."numfait")+s."pas" END AS "new_numentite"
				FROM serie AS s 
				LEFT JOIN t_fait AS t ON t."numfait" >= s."nombre_depart" AND t."numfait" <= s."nombre_arrivee"
				WHERE s."tablentite" like 't_fait'
				GROUP BY s."id_serie"
				UNION
				SELECT s."id_serie", 't_axe' AS "tablentite", max(t."numaxe") AS "numaximum",
				CASE WHEN max(t."numaxe") IS NULL THEN s."nombre_depart" ELSE max(t."numaxe")+s."pas" END AS "new_numentite"
				FROM serie AS s
				LEFT JOIN t_axe AS t ON t."numaxe" >= s."nombre_depart" AND t."numaxe" <= s."nombre_arrivee"
				WHERE s."tablentite" like 't_axe'
				GROUP BY s."id_serie"
				UNION
				SELECT s."id_serie", 't_ens' AS "tablentite", max(t."numens") AS "numaximum",
				CASE WHEN max(t."numens") IS NULL THEN s."nombre_depart" ELSE max(t."numens")+s."pas" END AS "new_numentite"
				FROM serie AS s
				LEFT JOIN t_ens AS t ON t."numens" >= s."nombre_depart" AND t."numens" <= s."nombre_arrivee"
				WHERE s."tablentite" like 't_ens'
				GROUP BY s."id_serie"
				UNION
				SELECT s."id_serie", 't_log' AS "tablentite", max(t."numlog") AS "numaximum",
				CASE WHEN max(t."numlog") IS NULL THEN s."nombre_depart" ELSE max(t."numlog")+s."pas" END AS "new_numentite"
				FROM serie AS s
				LEFT JOIN t_log AS t ON t."numlog" >= s."nombre_depart" AND t."numlog" <= s."nombre_arrivee"
				WHERE s."tablentite" like 't_log'
				GROUP BY s."id_serie"
				UNION
				SELECT s."id_serie", 't_mobilier' AS "tablentite", max(t."id_mob") AS "numaximum",
				CASE WHEN max(t."id_mob") IS NULL THEN s."nombre_depart" ELSE max(t."id_mob")+s."pas" END AS "new_numentite"
				FROM serie AS s
				LEFT JOIN t_mobilier AS t ON t."id_mob" >= s."nombre_depart" AND t."id_mob" <= s."nombre_arrivee"
				WHERE s."tablentite" like 't_mobilier'
				GROUP BY s."id_serie"
				UNION
				SELECT s."id_serie", 't_prelevement' AS "tablentite", max(t."numprel") AS "numaximum",
				CASE WHEN max(t."numprel") IS NULL THEN s."nombre_depart" ELSE max(t."numprel")+s."pas" END AS "new_numentite"
				FROM serie AS s
				LEFT JOIN t_prelevement AS t ON t."numprel" >= s."nombre_depart" AND t."numprel" <= s."nombre_arrivee"
				WHERE s."tablentite" like 't_prelevement'
				GROUP BY s."id_serie"
				UNION
				SELECT s."id_serie", 't_sondage' AS "tablentite", max(t."numsd") AS "numaximum",
				CASE WHEN max(t."numsd") IS NULL THEN s."nombre_depart" ELSE max(t."numsd")+s."pas" END AS "new_numentite"
				FROM serie AS s
				LEFT JOIN t_sondage AS t ON t."numsd" >= s."nombre_depart" AND t."numsd" <= s."nombre_arrivee"
				WHERE s."tablentite" like 't_sondage'
				GROUP BY s."id_serie"
				UNION
				SELECT s."id_serie", 't_tranchee' AS "tablentite", max(t."numtr") AS "numaximum",
				CASE WHEN max(t."numtr") IS NULL THEN s."nombre_depart" ELSE max(t."numtr")+s."pas" END AS "new_numentite"
				FROM serie AS s
				LEFT JOIN t_tranchee AS t ON t."numtr" >= s."nombre_depart" AND t."numtr" <= s."nombre_arrivee"
				WHERE s."tablentite" like 't_tranchee'
				GROUP BY s."id_serie"
;

--------
--Trigger associé aux tables de création rapide 
--------

--pour l'ajout du nouveau numéro d'entité
DROP TRIGGER IF EXISTS trgai_entite_rapide_newnumentite;
CREATE TRIGGER trgai_entite_rapide_newnumentite
	AFTER INSERT
	ON entite_rapide
	FOR EACH ROW
	WHEN NEW."id_serie" IS NOT NULL
		BEGIN
		UPDATE entite_rapide SET "numentite" = 
			(SELECT "new_numentite" 
			FROM (
				SELECT e."id_entite", 't_us' AS "tablentite", max(t."numus") AS "numaximum",
				CASE WHEN max(t."numus") IS NULL THEN s."nombre_depart" ELSE max(t."numus")+s."pas" END AS "new_numentite"
				FROM entite_rapide AS e 
				LEFT JOIN serie AS s ON e."id_serie" = s."id_serie"
				LEFT JOIN t_us AS t ON t."numus" >= s."nombre_depart" AND t."numus" <= s."nombre_arrivee"
				WHERE s."tablentite" like 't_us'
				GROUP BY s."id_serie"
				UNION
				SELECT e."id_entite", 't_fait' AS "tablentite", max(t."numfait") AS "numaximum",
				CASE WHEN max(t."numfait") IS NULL THEN s."nombre_depart" ELSE max(t."numfait")+s."pas" END AS "new_numentite"
				FROM entite_rapide AS e
				LEFT JOIN serie AS s ON e."id_serie" = s."id_serie"
				LEFT JOIN t_fait AS t ON t."numfait" >= s."nombre_depart" AND t."numfait" <= s."nombre_arrivee"
				WHERE s."tablentite" like 't_fait'
				GROUP BY s."id_serie"
				UNION
				SELECT e."id_entite", 't_axe' AS "tablentite", max(t."numaxe") AS "numaximum",
				CASE WHEN max(t."numaxe") IS NULL THEN s."nombre_depart" ELSE max(t."numaxe")+s."pas" END AS "new_numentite"
				FROM entite_rapide AS e 
				LEFT JOIN serie AS s ON e."id_serie" = s."id_serie"
				LEFT JOIN t_axe AS t ON t."numaxe" >= s."nombre_depart" AND t."numaxe" <= s."nombre_arrivee"
				WHERE s."tablentite" like 't_axe'
				GROUP BY s."id_serie"
				UNION
				SELECT e."id_entite", 't_ens' AS "tablentite", max(t."numens") AS "numaximum",
				CASE WHEN max(t."numens") IS NULL THEN s."nombre_depart" ELSE max(t."numens")+s."pas" END AS "new_numentite"
				FROM entite_rapide AS e
				LEFT JOIN serie AS s ON e."id_serie" = s."id_serie"
				LEFT JOIN t_ens AS t ON t."numens" >= s."nombre_depart" AND t."numens" <= s."nombre_arrivee"
				WHERE s."tablentite" like 't_ens'
				GROUP BY s."id_serie"
				UNION
				SELECT e."id_entite", 't_log' AS "tablentite", max(t."numlog") AS "numaximum",
				CASE WHEN max(t."numlog") IS NULL THEN s."nombre_depart" ELSE max(t."numlog")+s."pas" END AS "new_numentite"
				FROM entite_rapide AS e
				LEFT JOIN serie AS s ON e."id_serie" = s."id_serie"
				LEFT JOIN t_log AS t ON t."numlog" >= s."nombre_depart" AND t."numlog" <= s."nombre_arrivee"
				WHERE s."tablentite" like 't_log'
				GROUP BY s."id_serie"
				UNION
				SELECT e."id_entite", 't_mobilier' AS "tablentite", max(t."id_mob") AS "numaximum",
				CASE WHEN max(t."id_mob") IS NULL THEN s."nombre_depart" ELSE max(t."id_mob")+s."pas" END AS "new_numentite"
				FROM entite_rapide AS e
				LEFT JOIN serie AS s ON e."id_serie" = s."id_serie"
				LEFT JOIN t_mobilier AS t ON t."id_mob" >= s."nombre_depart" AND t."id_mob" <= s."nombre_arrivee"
				WHERE s."tablentite" like 't_mobilier'
				GROUP BY s."id_serie"
				UNION
				SELECT e."id_entite", 't_prelevement' AS "tablentite", max(t."numprel") AS "numaximum",
				CASE WHEN max(t."numprel") IS NULL THEN s."nombre_depart" ELSE max(t."numprel")+s."pas" END AS "new_numentite"
				FROM entite_rapide AS e
				LEFT JOIN serie AS s ON e."id_serie" = s."id_serie"
				LEFT JOIN t_prelevement AS t ON t."numprel" >= s."nombre_depart" AND t."numprel" <= s."nombre_arrivee"
				WHERE s."tablentite" like 't_prelevement'
				GROUP BY s."id_serie"
				UNION
				SELECT e."id_entite", 't_sondage' AS "tablentite", max(t."numsd") AS "numaximum",
				CASE WHEN max(t."numsd") IS NULL THEN s."nombre_depart" ELSE max(t."numsd")+s."pas" END AS "new_numentite"
				FROM entite_rapide AS e
				LEFT JOIN serie AS s ON e."id_serie" = s."id_serie"
				LEFT JOIN t_sondage AS t ON t."numsd" >= s."nombre_depart" AND t."numsd" <= s."nombre_arrivee"
				WHERE s."tablentite" like 't_sondage'
				GROUP BY s."id_serie"
				UNION
				SELECT e."id_entite", 't_tranchee' AS "tablentite", max(t."numtr") AS "numaximum",
				CASE WHEN max(t."numtr") IS NULL THEN s."nombre_depart" ELSE max(t."numtr")+s."pas" END AS "new_numentite"
				FROM entite_rapide AS e
				LEFT JOIN serie AS s ON e."id_serie" = s."id_serie"
				LEFT JOIN t_tranchee AS t ON t."numtr" >= s."nombre_depart" AND t."numtr" <= s."nombre_arrivee"
				WHERE s."tablentite" like 't_tranchee'
				GROUP BY s."id_serie")
			WHERE NEW."id_entite" = "id_entite");
			DELETE FROM entite_rapide;
END;

/*Trigger associé à la table de création d'entité rapide qui permet d'ajouter les enregistrements dans les tables ciblées*/

--pour t_axe
CREATE TRIGGER trgau_t_axe_maj_entite_rapide
   AFTER UPDATE OF "numentite"
   ON entite_rapide
FOR EACH ROW
	WHEN NEW."numentite" IS NOT NULL AND (SELECT s."tablentite" FROM entite_rapide AS e JOIN serie AS s ON OLD."id_serie" = s."id_serie") like 't_axe'
	BEGIN
	INSERT INTO t_axe("numaxe","geometry","note") VALUES (NEW."numentite", NULL,NEW."note");
END;

--pour t_ens
CREATE TRIGGER trgau_t_ens_maj_entite_rapide
   AFTER UPDATE OF "numentite"
   ON entite_rapide
FOR EACH ROW
	WHEN NEW."numentite" IS NOT NULL AND (SELECT s."tablentite" FROM entite_rapide AS e JOIN serie AS s ON OLD."id_serie" = s."id_serie") like 't_ens'
	BEGIN
	INSERT INTO t_ens("numens","typens","geometry","note") VALUES (NEW."numentite", NEW."interpret",NULL,NEW."note");
END;

--pour t_fait
CREATE TRIGGER trgau_t_fait_maj_entite_rapide
   AFTER UPDATE OF "numentite"
   ON entite_rapide
FOR EACH ROW
	WHEN NEW."numentite" IS NOT NULL AND (SELECT s."tablentite" FROM entite_rapide AS e JOIN serie AS s ON OLD."id_serie" = s."id_serie") like 't_fait'
	BEGIN
	INSERT INTO t_fait("numfait","interpret","geometry","note") VALUES (NEW."numentite",NEW."interpret",NULL,NEW."note");
END;

--pour t_log
CREATE TRIGGER trgau_t_log_maj_entite_rapide
   AFTER UPDATE OF "numentite"
   ON entite_rapide
FOR EACH ROW
	WHEN NEW."numentite" IS NOT NULL AND (SELECT s."tablentite" FROM entite_rapide AS e JOIN serie AS s ON OLD."id_serie" = s."id_serie") like 't_log'
	BEGIN
	INSERT INTO t_log("numlog","geometry","note_log") VALUES (NEW."numentite", NULL,NEW."note");
END;

--pour t_mobilier
CREATE TRIGGER trgau_t_mobilier_maj_entite_rapide
   AFTER UPDATE OF "numentite"
   ON entite_rapide
FOR EACH ROW
	WHEN NEW."numentite" IS NOT NULL AND (SELECT s."tablentite" FROM entite_rapide AS e JOIN serie AS s ON OLD."id_serie" = s."id_serie") like 't_mobilier'
	BEGIN
	INSERT INTO t_mobilier("id_mob","matiere","geometry","note") VALUES (NEW."numentite", NEW."interpret",NULL,NEW."note");
END;

--pour t_prelevement
CREATE TRIGGER trgau_t_prelevement_maj_entite_rapide
   AFTER UPDATE OF "numentite"
   ON entite_rapide
FOR EACH ROW
	WHEN NEW."numentite" IS NOT NULL AND (SELECT s."tablentite" FROM entite_rapide AS e JOIN serie AS s ON OLD."id_serie" = s."id_serie") like 't_prelevement'
	BEGIN
	INSERT INTO t_prelevement("numprel","geometry","note") VALUES (NEW."numentite", NULL,NEW."note");
END;

--pour t_sondage
CREATE TRIGGER trgau_t_sondage_maj_entite_rapide
   AFTER UPDATE OF "numentite"
   ON entite_rapide
FOR EACH ROW
	WHEN NEW."numentite" IS NOT NULL AND (SELECT s."tablentite" FROM entite_rapide AS e JOIN serie AS s ON OLD."id_serie" = s."id_serie") like 't_sondage'
	BEGIN
	INSERT INTO t_sondage("numsd","geometry","note") VALUES (NEW."numentite", NULL,NEW."note");
END;

--pour t_tranchee
CREATE TRIGGER trgau_t_tranchee_maj_entite_rapide
   AFTER UPDATE OF "numentite"
   ON entite_rapide
FOR EACH ROW
	WHEN NEW."numentite" IS NOT NULL AND (SELECT s."tablentite" FROM entite_rapide AS e JOIN serie AS s ON OLD."id_serie" = s."id_serie") like 't_tranchee'
	BEGIN
	INSERT INTO t_tranchee("numtr","geometry","note") VALUES (NEW."numentite", NULL,NEW."note");
END;

--pour t_us
CREATE TRIGGER trgau_t_us_maj_entite_rapide
   AFTER UPDATE OF "numentite"
   ON entite_rapide
FOR EACH ROW
	WHEN NEW."numentite" IS NOT NULL AND (SELECT s."tablentite" FROM entite_rapide AS e JOIN serie AS s ON OLD."id_serie" = s."id_serie") like 't_us'
	BEGIN
	INSERT INTO t_us("numus","nature_us","geometry","note") VALUES (NEW."numentite", NEW."interpret",NULL,NEW."note");
END;