-- PLUI versionning

-- script qui permet la création du schéma et des tables de la structure pour le plugin de versionning

-- Tables

-- ##################################################################################################################################################
-- pluiv_procedures ##################################################################################################################################
-- ##################################################################################################################################################

-- DROP TABLE urba_plui_ecriture.pluiv_procedures;
CREATE TABLE urba_plui_ecriture.pluiv_procedures (
    id serial NOT NULL, -- identifiant unique
    code_proc varchar(2) NOT NULL, -- Code de procédure
    num_proc int4 NULL, -- N° de procédure
    num_phase int4 NULL DEFAULT 1, -- N° de la phase de numérisation (en cas d'arrêt de la procédure, d'enquête publique, ...)
    intitule text NOT NULL, -- intitulé de procédure
    datefin_proc date NULL, -- Date de fin de procédure
    commentaires text NULL, -- Commentaires
    CONSTRAINT pluiv_procedures_pkey PRIMARY KEY (id),
    CONSTRAINT pluiv_procedures_unique UNIQUE (code_proc, num_proc, num_phase),
    CONSTRAINT pluiv_procedures_code_proc_fk FOREIGN KEY (code_proc) REFERENCES urba_plui_ecriture.procedureurbatype(nomproc)
);

COMMENT ON TABLE urba_plui_ecriture.pluiv_procedures IS 'Tableau de bord du suivi des procédures';

-- Column comments

COMMENT ON COLUMN urba_plui_ecriture.pluiv_procedures.id IS 'identifiant unique';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_procedures.code_proc IS 'Code de procédure';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_procedures.num_proc IS 'N° de procédure';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_procedures.num_phase IS 'N° de la phase de numérisation (en cas d''arrêt de la procédure, d''enquête publique, ...)';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_procedures.intitule IS 'intitulé de procédure';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_procedures.datefin_proc IS 'Date de fin de procédure';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_procedures.commentaires IS 'Commentaires';

-- ##################################################################################################################################################
-- pluiv_thematiques ##################################################################################################################################
-- ##################################################################################################################################################

--DROP TABLE urba_plui_ecriture.pluiv_thematiques;
CREATE TABLE urba_plui_ecriture.pluiv_thematiques (
    id serial NOT NULL, -- identifiant UNIQUE
    nom_thematique text NULL, -- Intitulé de la thématique
    description text NULL, -- Description de la thématique
    classe_entite TEXT NULL, -- "psc" (prescription) ou "inf" (information)
    code_cnig TEXT NULL,
    categories _text NULL, -- liste des sous-codes CNIG embarqués dans la thématique
    obsolete bool DEFAULT FALSE, -- Indique si la thématique est abandonnée
    CONSTRAINT pluiv_thematiques_pkey PRIMARY KEY (id),
    CONSTRAINT pluiv_thematiques_nom_thematique_unique UNIQUE (nom_thematique),
    CONSTRAINT pluiv_thematiques_classe_entite_code_cnig_categories_unique UNIQUE (classe_entite,code_cnig,categories),
    CONSTRAINT pluiv_thematiques_classe_entite_check CHECK (classe_entite IN ('zon','psc','inf','hab')),
    CONSTRAINT pluiv_thematiques_code_cnig_check CHECK (code_cnig ~ '\d{2}' AND code_cnig::int > 0 AND code_cnig::int <= 99)
);
COMMENT ON TABLE urba_plui_ecriture.pluiv_thematiques IS 'Liste des thématiques traitées dans le PLUi. Une thématique est une prescription ou une information de même code CNIG et portant sur tout ou partie des sous-codes rattachés. Elle est représentative d''une couche sur un plan graphique';

COMMENT ON COLUMN urba_plui_ecriture.pluiv_thematiques.id IS 'identifiant unique';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_thematiques.nom_thematique IS 'Nom de la thématique';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_thematiques.description IS 'Description de la thématique';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_thematiques.classe_entite IS '"psc" (prescription) ou "inf" (information). Zonage et habillage ne sont pas concernés';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_thematiques.code_cnig IS 'Code CNIG embarqué dans la thématique';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_thematiques.categories IS 'Liste des sous-codes CNIG embarqués dans la thématique';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_thematiques.obsolete IS 'Indique si la thématique est abandonnée. Le cas échéant, elle ne peut plus être sollicitée dans une procédure en cours';


--DROP TABLE urba_plui_ecriture.pluiv_corresp_procedures_thematiques;
CREATE TABLE urba_plui_ecriture.pluiv_corresp_procedures_thematiques (
    id serial NOT NULL, -- identifiant UNIQUE
    id_procedure int NULL, -- Identifiant de procédure
    id_thematique int NULL, -- Identifiant de thématique
    CONSTRAINT pluiv_corresp_procedures_thematiques_pkey PRIMARY KEY (id),
    CONSTRAINT pluiv_procedures_id_fk FOREIGN KEY (id_procedure) REFERENCES urba_plui_ecriture.pluiv_procedures(id),
    CONSTRAINT pluiv_thematiques_id_fk FOREIGN KEY (id_thematique) REFERENCES urba_plui_ecriture.pluiv_thematiques(id)
);
COMMENT ON TABLE urba_plui_ecriture.pluiv_corresp_procedures_thematiques IS 'Table de correspondance permettant d''associer les thématiques présentes dans une procédure';

COMMENT ON COLUMN urba_plui_ecriture.pluiv_corresp_procedures_thematiques.id IS 'identifiant unique';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_corresp_procedures_thematiques.id_procedure IS 'Identifiant de procédure';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_corresp_procedures_thematiques.id_thematique IS 'Identifiant de thématique';


-- ##################################################################################################################################################
-- pluiv_projets ##################################################################################################################################
-- ##################################################################################################################################################

--DROP TABLE urba_plui_ecriture.pluiv_projets;
CREATE TABLE urba_plui_ecriture.pluiv_projets (
    id serial NOT NULL, -- identifiant UNIQUE
    id_proc_them int4 NOT NULL, -- identifiant du couple thématique/procédure
    nom_projet text NULL, -- Intitulé du projet
    description text NULL, -- Description du projet
    date_init date NULL DEFAULT now(), -- Date de création du projet (date d'initialisation pour les projets de référence)
    date_fin date NULL, -- Date de validation de version
    commentaires text NULL, -- Informations complémentaires sur le suivi de la numérisation
    categorie bpchar(3) NULL, -- Catégorie portée par la version : "ZON" pour le zonage, "P" ou "I" + code CNIG pour prescriptions/informations, "HAB" pour habillage
    num_proj int NULL, -- N° du projet
    embarque boolean NULL DEFAULT FALSE,
    CONSTRAINT pluiv_projets_pkey PRIMARY KEY (id),
--    CONSTRAINT pluiv_projets_unique UNIQUE (id_projet),
    CONSTRAINT pluiv_projets_proc_them_fkey FOREIGN KEY (id_proc_them) REFERENCES urba_plui_ecriture.pluiv_corresp_procedures_thematiques(id) ON DELETE CASCADE
);

COMMENT ON TABLE urba_plui_ecriture.pluiv_projets IS 'Sous-découpage géographique de thématique pour segmenter le travail de numérisation. Une thématique peut ne comporter qu''un seul projet par thématique au sein d''une procédure';

-- Column comments

COMMENT ON COLUMN urba_plui_ecriture.pluiv_projets.id IS 'identifiant unique';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_projets.id_proc_them IS 'Identifiant du couple procédure/thématique (FK)';
--COMMENT ON COLUMN urba_plui_ecriture.pluiv_projets.id_projet IS 'Identifiant métier du projet (concaténation procédure + thématique + n° projet)';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_projets.nom_projet IS 'Intitulé du projet';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_projets.description IS 'Description du projet';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_projets.date_init IS 'Date de création du projet (date d''initialisation pour les projets de référence)';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_projets.date_fin IS 'Date de validation de version';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_projets.commentaires IS 'Informations complémentaires sur le suivi de la numérisation';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_projets.categorie IS 'Catégorie portée par le projet : "ZON" pour le zonage, "P" ou "I" + code CNIG pour prescriptions/informations, "HAB" pour habillage';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_projets.num_proj IS 'N° du projet';

-- ##################################################################################################################################################
-- pluiv_versions ######################################################################################################################################
-- ##################################################################################################################################################

-- // avec git = commit

-- DROP TABLE urba_plui_ecriture.pluiv_versionlibellestatut;
CREATE TABLE urba_plui_ecriture.pluiv_versionlibellestatut (
    id serial NOT NULL,
    valeur bpchar(2) NULL, -- Code de statut
    libelle varchar(50) NULL, -- Libellé de statut
    description text NULL, -- Descriptif du statut
    editable bool NULL, -- Indique si la version est éditable ou non
    CONSTRAINT versionlibellestatut_pkey PRIMARY KEY (id),
    CONSTRAINT pluiv_versionlibellestatut_unique UNIQUE (valeur)
);
COMMENT ON TABLE urba_plui_ecriture.pluiv_versionlibellestatut IS 'Table de référence des statuts possibles d''une version';

-- Column comments

COMMENT ON COLUMN urba_plui_ecriture.pluiv_versionlibellestatut.valeur IS 'Code de statut';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_versionlibellestatut.libelle IS 'Libellé de statut';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_versionlibellestatut.description IS 'Descriptif du statut';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_versionlibellestatut.editable IS 'Indique si la version est éditable ou non';

-- les valeurs

INSERT INTO urba_plui_ecriture.pluiv_versionlibellestatut (valeur,libelle,description,editable) VALUES
     ('01','Référence','Version d’origine d’un projet servant dont le numéro de projet est ''000'' ou le numéro de version est ‘00’. Cette version n’est pas modifiable pour des questions de traçabilité afin de pouvoir récupérer l’historique.
Les données qu’elle contient correspond à la version validée lors de la dernière approbation
',false),
     ('02','En cours','Version de travail modifiable',true),
     ('03','Figée','Version bloquée par l’utilisateur qui est donc non modifiable et potentiellement candidate pour une réunion',false),
     ('04','Abandonnée','Version de travail non modifiable potentiellement supprimable de la base de données',false),
     ('08','Validée','Version validée à l’issue de l’étape de validation des versions communales retenues ',false);

-- la table #########################################################################

-- DROP TABLE urba_plui_ecriture.pluiv_versions;

CREATE TABLE urba_plui_ecriture.pluiv_versions (
    id serial NOT NULL,
    id_projet int4 NOT NULL, -- identifiant du projet (Fkey)
    date_init timestamp NULL DEFAULT now(), -- Date de création de la version
    date_fin date NULL, -- Date d'abandon du projet ou de la branche
    id_version_mere int NULL, -- Id de version d'origine de la version (FK)
    commentaires text NULL, -- sans commentaires !
    num_version int NULL, -- N° de version
    statut char(2) NULL DEFAULT '02'::character(2), -- Code de statut (cf liste dans la table 'versionlibellestatut')
    intitule TEXT NULL, -- Titre du projet, adaptable en fonction de la version courante
--    bloque bool NULL DEFAULT false, -- permet de bloquer une version de référence qui présente des erreurs. Interdit la duplication.
--    id_dmodif varchar(20) NULL, -- identifiant du dernier modificateur
--    date_dmodif timestamp NULL, -- date_heure derniere modif
    CONSTRAINT pluiv_versions_pkey PRIMARY KEY (id),
    CONSTRAINT pluiv_versions_projets_fkey FOREIGN KEY (id_projet) REFERENCES urba_plui_ecriture.pluiv_projets(id) ON DELETE CASCADE,
    CONSTRAINT pluiv_versions_versions_fkey FOREIGN KEY (id_version_mere) REFERENCES urba_plui_ecriture.pluiv_versions(id),
    CONSTRAINT pluiv_versions_statut_fkey FOREIGN KEY (statut) REFERENCES urba_plui_ecriture.pluiv_versionlibellestatut(valeur)
);

CREATE INDEX pluiv_versions_num_version_btree ON urba_plui_ecriture.pluiv_versions USING btree (num_version);
--CREATE INDEX pluiv_versions_thematique_btree ON urba_plui_ecriture.pluiv_versions USING btree (thematique);

COMMENT ON TABLE urba_plui_ecriture.pluiv_versions IS 'Table listant l''ensemble des versions de numérisation en lien avec la table des projets';

-- Column comments
COMMENT ON COLUMN urba_plui_ecriture.pluiv_versions.id_projet IS 'identifiant du projet (FK)';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_versions.date_init IS 'Date de création de la version';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_versions.date_fin IS 'Date d''abandon du projet ou de la branche';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_versions.id_version_mere IS 'Id de version d''origine de la version';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_versions.commentaires IS 'sans commentaires !';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_versions.num_version IS 'N° de version';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_versions.statut IS 'Code de statut (cf liste dans la table ''versionlibellestatut'')';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_versions.intitule IS 'Titre du projet, adaptable en fonction de la version courante';
--COMMENT ON COLUMN urba_plui_ecriture.pluiv_versions.bloque IS 'permet de bloquer une version de référence qui présente des erreurs. Interdit la duplication.';
--COMMENT ON COLUMN urba_plui_ecriture.pluiv_versions.id_dmodif IS 'identifiant du dernier modificateur';
--COMMENT ON COLUMN urba_plui_ecriture.pluiv_versions.date_dmodif IS 'date_heure derniere modif';


-- ##################################################################################################################################################
-- Table de recensement des versions validées pour chaque procédure terminée
-- ##################################################################################################################################################

-- DROP TABLE urba_plui_ecriture.pluiv_versions_proc_terminees;

CREATE TABLE urba_plui_ecriture.pluiv_versions_proc_terminees (
    id serial NOT NULL,
    id_proc int NULL, -- Id de procedure terminée (FK)
    id_them int NULL, -- Id de thématique (FK)
    id_version int NULL, -- Id de version validée (FK)
    CONSTRAINT pluiv_versions_proc_terminees_pkey PRIMARY KEY (id),
    CONSTRAINT pluiv_versions_proc_terminees_proc_fkey FOREIGN KEY (id_proc) REFERENCES urba_plui_ecriture.pluiv_procedures(id),
    CONSTRAINT pluiv_versions_proc_terminees_them_fkey FOREIGN KEY (id_them) REFERENCES urba_plui_ecriture.pluiv_thematiques(id),
    CONSTRAINT pluiv_versions_proc_terminees_version_fkey FOREIGN KEY (id_version) REFERENCES urba_plui_ecriture.pluiv_versions(id),
    CONSTRAINT pluiv_versions_proc_terminees_unique UNIQUE (id_proc, id_version)
);


COMMENT ON TABLE urba_plui_ecriture.pluiv_versions_proc_terminees IS 'Table de recensement des versions validées pour chaque procédure terminée';

-- Column comments
COMMENT ON COLUMN urba_plui_ecriture.pluiv_versions_proc_terminees.id IS 'identifiant du projet (FK)';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_versions_proc_terminees.id_proc IS 'identifiant de procédure terminée(FK)';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_versions_proc_terminees.id_version IS 'identifiant de version validée (FK)';


-- ##################################################################################################################################################
-- Tables de gestion des libellés de zonage prescriptions/informations spécifiques à la collectivité
-- ##################################################################################################################################################

-- Zonage
CREATE TABLE urba_plui_ecriture.pluiv_zoneurbatype_ct (
    id serial NOT NULL,
    libelle varchar(12) NULL,
    libelong TEXT NULL,
    id_typezone int NULL,
    CONSTRAINT pluiv_zoneurbatype_ct_id_pkey PRIMARY KEY (id),
    CONSTRAINT pluiv_zoneurbatype_ct_libelle_unique UNIQUE (libelle)
);
COMMENT ON TABLE urba_plui_ecriture.pluiv_zoneurbatype_ct IS 'Table de correspondance avec la table zoneurba pour remplir le champ "libelong"';

COMMENT ON COLUMN urba_plui_ecriture.pluiv_zoneurbatype_ct.id IS 'identifiant unique';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_zoneurbatype_ct.libelle IS 'Nom court de la zone tel qu''il apparaît sur le plan de zonage';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_zoneurbatype_ct.libelong IS 'Nom complet littéral de la zone tel qu''il apparaît dans le chapitre du règlement écrit';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_zoneurbatype_ct.id_typezone IS 'Référence du type de la zone (FK)';

-- Prescription
CREATE TABLE urba_plui_ecriture.pluiv_prescriptionurbatype_ct (
    id serial NOT NULL, -- identifiant unique
    typepsc_ct TEXT NULL, -- Sous-sous type de la prescription
    libelle_typepsc_ct TEXT NULL, -- Libellé du sous-sous type de la prescription
    id_stypepsc int NULL, -- Sous type de la prescription (FK)
    urlfic TEXT NULL, -- Url vers le fichier de règlement
    proc_fin TEXT NULL, -- Dernière procédure dans laquelle la catégorie était encore utilisable (permet de filtrer la catégorie pour ne pas l'afficher dans le choix des codes de prescriptions CT
    CONSTRAINT pluiv_prescriptionurbatype_ct_pkey PRIMARY KEY (id),
    CONSTRAINT pluiv_prescriptionurbatype_ct_unique UNIQUE (typepsc_ct)
);
COMMENT ON TABLE urba_plui_ecriture.pluiv_prescriptionurbatype_ct IS 'Table de référence des différents sous-sous types de prescriptions propres à la collectivité territoriale';


COMMENT ON COLUMN urba_plui_ecriture.pluiv_prescriptionurbatype_ct.id IS 'identifiant unique';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_prescriptionurbatype_ct.typepsc_ct IS 'Type de niveau 2 (ou "sous-sous type") de prescription propre à la collectivité';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_prescriptionurbatype_ct.libelle_typepsc_ct IS 'Libellé de Type de niveau 2 (ou "sous-sous type") de prescription propre à la collectivité';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_prescriptionurbatype_ct.id_stypepsc IS 'Appartenance au sous type de prescription (FK)';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_prescriptionurbatype_ct.urlfic IS 'Url vers le fichier de règlement';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_prescriptionurbatype_ct.proc_fin IS 'Dernière procédure dans laquelle la catégorie était encore utilisable (permet de filtrer la catégorie pour ne pas l''afficher dans liste des choix de catégorie)';


-- Information
CREATE TABLE urba_plui_ecriture.pluiv_informationurbatype_ct (
    id serial NOT NULL, -- identifiant unique
    typeinf_ct TEXT NULL, -- Sous-sous type de la information
    libelle_typeinf_ct TEXT NULL, -- Libellé du sous-sous type de l'information
    id_stypeinf int NULL, -- Sous type de l'information (FK)
    urlfic TEXT NULL, -- Url vers le fichier de règlement
    proc_fin TEXT NULL, -- Dernière procédure dans laquelle la catégorie était encore utilisable (permet de filtrer la catégorie pour ne pas l'afficher dans le choix des codes d'informations CT
    CONSTRAINT pluiv_informationurbatype_ct_pkey PRIMARY KEY (id),
    CONSTRAINT pluiv_informationurbatype_ct_unique UNIQUE (typeinf_ct)
);
COMMENT ON TABLE urba_plui_ecriture.pluiv_informationurbatype_ct IS 'Table de référence des différents sous-sous types d''informations propres à la collectivité territoriale';


COMMENT ON COLUMN urba_plui_ecriture.pluiv_informationurbatype_ct.id IS 'identifiant unique';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_informationurbatype_ct.typeinf_ct IS 'Type de niveau 2 (ou "sous-sous type") d''information propre à la collectivité';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_informationurbatype_ct.libelle_typeinf_ct IS 'Libellé de Type de niveau 2 (ou "sous-sous type") d''information propre à la collectivité';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_informationurbatype_ct.id_stypeinf IS 'Appartenance au sous type d''information (FK)';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_informationurbatype_ct.urlfic IS 'Url vers le fichier de règlement';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_informationurbatype_ct.proc_fin IS 'Dernière procédure dans laquelle la catégorie était encore utilisable (permet de filtrer la catégorie pour ne pas l''afficher dans la liste des choix de catégorie)';


-- ##################################################################################################################################################
-- Table de gestion des champs supplémentaires qui sont géré au format HSTORE
-- ##################################################################################################################################################

-- dépendences
-- t_pluiv_champs_lib
-- tf_pluiv_champs_lib
-- admin_supprimer_vues_entite

-- Drop table

-- DROP TABLE urba_plui_ecriture.pluiv_champs_lib

CREATE TABLE urba_plui_ecriture.pluiv_champs_lib (
    id serial NOT NULL,
    classe_entite text NOT NULL, -- Nom de la classe d'entités ("zoneurba", "prescription", "information" ou habillage")
    nom_col text NOT NULL, -- Nom du champ (doit commencer par "lib_")
    publique bool NULL, -- Définit si ce champ est affiché dans les vues publiques
    valeur_defaut text NULL, -- Valeur par defaut du champ
    deployable bool NULL, -- Indique si le champ lib est à afficher dans les vues de l'outil
    CONSTRAINT pluiv_champs_lib_pkey PRIMARY KEY (id),
    CONSTRAINT pluiv_champs_lib_unique UNIQUE (classe_entite, nom_col)
);
COMMENT ON TABLE urba_plui_ecriture.pluiv_champs_lib IS 'Liste des champs supplémentaires au standard CNIG permettant de compléter l''information sur les différentes classes d''entités';

-- Column comments

COMMENT ON COLUMN urba_plui_ecriture.pluiv_champs_lib.classe_entite IS 'Nom de la classe d''entités ("zoneurba", "prescription", "information" ou habillage")';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_champs_lib.nom_col IS 'Nom du champ (doit commencer par "lib_")';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_champs_lib.publique IS 'Définit si ce champ est affiché dans les vues publiques';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_champs_lib.valeur_defaut IS 'Valeur par defaut du champ';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_champs_lib.deployable IS 'Indique si le champ lib est à afficher dans les vues de l''outil';

-- les données spécifique à la GAM se trouve dans le script 03b

-- ##################################################################################################################################################
-- Table de référence du nom des champs de géométrie pour les différentes classes d'entités
-- ##################################################################################################################################################

-- dépendences
-- table appellée par la fonction de déployement des vues de travail : admin_deployer_vues_entite

-- Drop table

-- DROP TABLE urba_plui_ecriture.pluiv_structure_entites;

CREATE TABLE urba_plui_ecriture.pluiv_structure_entites (
    id serial NOT NULL,
    classe_entite text NULL, -- Nom de la classe d'entités ("zoneurba", "prescription", "information" ou habillage")
    nom_colonne_geom text NULL, -- Intitulé de la colonne de géométrie pour la classe d'entité considérée
    prefixe text NULL, -- Préfixe de l'identifiant CNIG de la classe d'entité considérée
    cnig bpchar(4) NULL, -- Version du standard CNIG
    CONSTRAINT pluiv_structure_entites_pkey PRIMARY KEY (id)
);
COMMENT ON TABLE urba_plui_ecriture.pluiv_structure_entites IS 'Table de référence du nom des champs de géométrie pour les différentes classes d''entités (nécessaire pour redéployer les vues des différentes classes d''entités)';

-- Column comments

COMMENT ON COLUMN urba_plui_ecriture.pluiv_structure_entites.classe_entite IS 'Nom de la classe d''entités ("zoneurba", "prescription", "information" ou habillage")';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_structure_entites.nom_colonne_geom IS 'Intitulé de la colonne de géométrie pour la classe d''entité considérée';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_structure_entites.prefixe IS 'Préfixe de l''identifiant CNIG de la classe d''entité considérée';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_structure_entites.cnig IS 'Version du standard CNIG';

-- remplissage de la table pour les deux versions de CNIG (2014 et 2017)

INSERT INTO urba_plui_ecriture.pluiv_structure_entites (classe_entite,nom_colonne_geom,prefixe,cnig) VALUES
     ('zoneurba','geom','Z','2014'),
     ('prescription','geom_lin','L','2014'),
     ('prescription','geom_pct','P','2014'),
     ('prescription','geom_surf','S','2014'),
     ('information','geom_lin','LINE','2014'),
     ('information','geom_pct','POIN','2014'),
     ('habillage','geom_lin','HL','2014'),
     ('habillage','geom_pct','HP','2014'),
     ('habillage','geom_surf','HS','2014'),
     ('habillage','geom_txt','HT','2014');
INSERT INTO urba_plui_ecriture.pluiv_structure_entites (classe_entite,nom_colonne_geom,prefixe,cnig) VALUES
     ('zoneurba','geom','ZO','2017'),
     ('prescription','geom_lin','PL','2017'),
     ('prescription','geom_surf','PS','2017'),
     ('information','geom_lin','IL','2017'),
     ('information','geom_pct','IP','2017'),
     ('information','geom_surf','IS','2017'),
     ('habillage','geom_lin','HL','2017'),
     ('habillage','geom_pct','HP','2017'),
     ('habillage','geom_surf','HS','2017'),
     ('habillage','geom_txt','HT','2017');
INSERT INTO urba_plui_ecriture.pluiv_structure_entites (classe_entite,nom_colonne_geom,prefixe,cnig) VALUES
     ('information','geom_surf','PERI','2014'),
     ('prescription','geom_pct','PP','2017');


-- ##################################################################################################################################################
-- Table stockant de manière temporaire le contexte de saisie des données
-- à savoir dans quelle version l'utilisateur numérise ses données pour mettre à jour le n° de version des entités importées depuis d'autres versions
-- ##################################################################################################################################################

-- dépendences
-- table appelée par les fonctions de modification des entitées
-- tf_zoneurba, tf_prescription, tf_information et tf_habillage

-- DROP TABLE urba_plui_ecriture.pluiv_contexte_utilisateur;

CREATE TABLE urba_plui_ecriture.pluiv_contexte_utilisateur (
    ogc_fid serial NOT NULL,
    user_logged text NULL, -- Utilisateur ayant chargé la version
    current_version varchar(20) NULL, -- Identifiant de version courante
    classe_entite varchar NULL, -- Classe d'entité de la version chargée
    CONSTRAINT pluiv_contexte_utilisateur_ogc_fid_pkey PRIMARY KEY (ogc_fid)
);
COMMENT ON TABLE urba_plui_ecriture.pluiv_contexte_utilisateur IS 'Table stockant de manière temporaire le contexte de saisie des données, à savoir dans quelle version l''utilisateur numérise ses données pour mettre à jour le n° de version des entités importées depuis d''autres versions';

-- Column comments

COMMENT ON COLUMN urba_plui_ecriture.pluiv_contexte_utilisateur.user_logged IS 'Utilisateur ayant chargé la version';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_contexte_utilisateur.current_version IS 'Identifiant de version courante';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_contexte_utilisateur.classe_entite IS 'Classe d''entité de la version chargée';

-- la vue de travail

CREATE OR REPLACE VIEW urba_plui_ecriture.v_pluiv_contexte_utilisateur
AS SELECT pluiv_contexte_utilisateur.ogc_fid,
    pluiv_contexte_utilisateur.user_logged,
    pluiv_contexte_utilisateur.current_version,
    pluiv_contexte_utilisateur.classe_entite
   FROM urba_plui_ecriture.pluiv_contexte_utilisateur;

-- ##################################################################################################################################################
-- Table stockant les communes de l'EPCI
-- ##################################################################################################################################################

-- dépendences
-- fonction func_recuperer_code_insee

-- Drop table

-- DROP TABLE urba_plui_ecriture.ref_limites_communales;

CREATE TABLE urba_plui_ecriture.ref_limites_communales (
    ogc_fid int4 NOT NULL,
    geom geometry(POLYGON, 3945) NULL,
    code_insee varchar(5) NOT NULL,
    nom varchar(45) NULL,
    nom_etiquette varchar(50) NULL,
    nom_abv varchar(3) NULL,
    trigram bpchar(3) NULL,
    CONSTRAINT ref_limites_communales_pkey PRIMARY KEY (ogc_fid)
);
CREATE INDEX ref_limites_communales_geom_idx ON urba_plui_ecriture.ref_limites_communales USING gist (geom);

-- ##################################################################################################################################################
-- # Table de gestion des styles en lien avec la table layer_styles
-- ##################################################################################################################################################

-- table de gestion des styles QGIS

-- DROP TABLE public.layer_styles;

CREATE TABLE IF NOT EXISTS public.layer_styles (
    id serial NOT NULL,
    f_table_catalog varchar(256) NULL,
    f_table_schema varchar(256) NULL,
    f_table_name varchar(256) NULL,
    f_geometry_column varchar(256) NULL,
    stylename varchar(255) NULL,
    styleqml xml NULL,
    stylesld xml NULL,
    useasdefault bool NULL,
    description text NULL,
    "owner" varchar(30) NULL,
    ui xml NULL,
    update_time timestamp NULL DEFAULT now(),
    "type" varchar NULL,
    CONSTRAINT layer_styles_pkey PRIMARY KEY (id)
);

-- Table de gestion des styles

-- DROP TABLE urba_plui_ecriture.pluiv_styles;

CREATE TABLE urba_plui_ecriture.pluiv_styles (
    id serial NOT NULL, -- identifiant unique
    id_them int NULL, -- Identifiant de la thématique (FK)
    id_style int NULL, -- Identifiant du style stocké dans la table public.layer_styles (FK)
    id_proc int NULL, -- Identifiant de la phase de procédure à partir de laquelle le style est applicable (FK)
    format TEXT NULL, -- Précise s'il s'agit d'un style ponctuel (PCT), linéaire (LIN) ou surfacique (SURF)
    cnig bool NULL DEFAULT FALSE, -- Indique si le style est celui fourni par le CNIG
    CONSTRAINT pluiv_styles_pkey PRIMARY KEY (id),
    CONSTRAINT pluiv_styles_unique UNIQUE (id_them, id_proc, format, cnig),
    CONSTRAINT pluiv_styles_check_format CHECK (format IN ('pct','lin','surf','txt'))
);
COMMENT ON TABLE urba_plui_ecriture.pluiv_styles IS 'Table de gestion des styles permettant de faire le lien entre les thématiques et la table des styles';

-- Column comments

COMMENT ON COLUMN urba_plui_ecriture.pluiv_styles.id IS 'identifiant unique';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_styles.id_them IS 'Identifiant de la thématique (FK)';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_styles.id_style IS 'Identifiant du style stocké dans la table public.layer_styles (FK)';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_styles.id_proc IS 'Identifiant de la phase de procédure à partir de laquelle le style est applicable (FK)';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_styles.format IS 'Précise s''il s''agit d''un style ponctuel (PCT), linéaire (LIN) ou surfacique (SURF)';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_styles.cnig IS 'Indique si le style est celui fourni par le CNIG (pas le cas par défaut)';

-- FKEY
ALTER TABLE urba_plui_ecriture.pluiv_styles ADD CONSTRAINT layer_styles_fk FOREIGN KEY (id_style) REFERENCES public.layer_styles(id) ON DELETE CASCADE;
ALTER TABLE urba_plui_ecriture.pluiv_styles ADD CONSTRAINT pluiv_thematiques_fk FOREIGN KEY (id_them) REFERENCES urba_plui_ecriture.pluiv_thematiques(id) ON DELETE CASCADE ;
ALTER TABLE urba_plui_ecriture.pluiv_styles ADD CONSTRAINT pluiv_procedures_fk FOREIGN KEY (id_proc) REFERENCES urba_plui_ecriture.pluiv_procedures(id);


-- ##################################################################################################################################################
-- # Ajout des contraintes de dépendances entre le modèle CNIG et les tables du plugin
-- ##################################################################################################################################################

ALTER TABLE urba_plui_ecriture.zoneurba ADD CONSTRAINT pluiv_versions_fk FOREIGN KEY (lib_id_version) REFERENCES urba_plui_ecriture.pluiv_versions(id) ON DELETE CASCADE;
ALTER TABLE urba_plui_ecriture.prescription ADD CONSTRAINT pluiv_versions_fk FOREIGN KEY (lib_id_version) REFERENCES urba_plui_ecriture.pluiv_versions(id) ON DELETE CASCADE;
ALTER TABLE urba_plui_ecriture.information ADD CONSTRAINT pluiv_versions_fk FOREIGN KEY (lib_id_version) REFERENCES urba_plui_ecriture.pluiv_versions(id) ON DELETE CASCADE;
ALTER TABLE urba_plui_ecriture.habillage ADD CONSTRAINT pluiv_versions_fk FOREIGN KEY (lib_id_version) REFERENCES urba_plui_ecriture.pluiv_versions(id) ON DELETE CASCADE;

-- fkey sur zoneurbatype
ALTER TABLE urba_plui_ecriture.pluiv_zoneurbatype_ct ADD
    CONSTRAINT zoneurbatype_fk
    FOREIGN KEY (id_typezone)
    REFERENCES urba_plui_ecriture.zoneurbatype(id)
ON DELETE RESTRICT
ON UPDATE CASCADE;

-- fkey sur prescriptionurbatype
ALTER TABLE urba_plui_ecriture.pluiv_prescriptionurbatype_ct ADD
    CONSTRAINT prescriptionurbatype_fk
    FOREIGN KEY (id_stypepsc)
    REFERENCES urba_plui_ecriture.prescriptionurbatype(id)
ON DELETE RESTRICT
ON UPDATE CASCADE;

-- fkey sur informationurbatype
ALTER TABLE urba_plui_ecriture.pluiv_informationurbatype_ct ADD
    CONSTRAINT informationurbatype_fk
    FOREIGN KEY (id_stypeinf)
    REFERENCES urba_plui_ecriture.informationurbatype(id)
ON DELETE RESTRICT
ON UPDATE CASCADE;

-- fkey sur zoneurba
ALTER TABLE urba_plui_ecriture.zoneurba ADD
    CONSTRAINT zoneurba_typezone_ct_fk
    FOREIGN KEY (libelle)
    REFERENCES urba_plui_ecriture.pluiv_zoneurbatype_ct(libelle)
ON DELETE RESTRICT
ON UPDATE CASCADE;

-- fkey sur prescription
ALTER TABLE urba_plui_ecriture.prescription ADD
    CONSTRAINT prescription_typepsc_ct_fk
    FOREIGN KEY (lib_typepsc_ct)
    REFERENCES urba_plui_ecriture.pluiv_prescriptionurbatype_ct(typepsc_ct)
ON DELETE RESTRICT
ON UPDATE CASCADE;

-- fkey sur prescription
ALTER TABLE urba_plui_ecriture.information ADD
    CONSTRAINT information_typeinf_ct_fk
    FOREIGN KEY (lib_typeinf_ct)
    REFERENCES urba_plui_ecriture.pluiv_informationurbatype_ct(typeinf_ct)
ON DELETE RESTRICT
ON UPDATE CASCADE;

-- ##################################################################################################################################################
-- # Ajout des index
-- ##################################################################################################################################################

CREATE INDEX IF NOT EXISTS zoneurba_lib_id_version_idx
  ON urba_plui_ecriture.zoneurba (lib_id_version);

CREATE INDEX IF NOT EXISTS prescription_lib_id_version_idx
  ON urba_plui_ecriture.prescription (lib_id_version);

CREATE INDEX IF NOT EXISTS information_lib_id_version_idx
  ON urba_plui_ecriture.information (lib_id_version);

CREATE INDEX IF NOT EXISTS habillage_lib_id_version_idx
  ON urba_plui_ecriture.habillage (lib_id_version);

CREATE INDEX IF NOT EXISTS pluiv_projets_id_proc_them_idx
  ON urba_plui_ecriture.pluiv_projets (id_proc_them);

CREATE INDEX IF NOT EXISTS pluiv_versions_id_projet_idx
  ON urba_plui_ecriture.pluiv_versions (id_projet);
