-- plugin plui versionning

-- module ETUDES

-- TODO : rapatrier ici les tables - vues - fontion du module études


-- ##################################################################################################################################################
-- pluiv_etudes ######################################################################################################################################
-- ##################################################################################################################################################

-- liste de valeur

-- DROP TABLE urba_plui_ecriture.pluiv_etude_libellestatut;

CREATE TABLE urba_plui_ecriture.pluiv_etude_libellestatut (
    code_statut bpchar(2) NOT NULL, -- Code de statut de l'étude
    libelle_statut text NOT NULL, -- Libellé su statut de l'étude
    CONSTRAINT pluiv_etude_libellestatut_pk PRIMARY KEY (code_statut)
);
COMMENT ON TABLE urba_plui_ecriture.pluiv_etude_libellestatut IS 'Table de référence des différents statuts des études';

-- Column comments

COMMENT ON COLUMN urba_plui_ecriture.pluiv_etude_libellestatut.code_statut IS 'Code de statut de l''étude';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_etude_libellestatut.libelle_statut IS 'Libellé su statut de l''étude';

-- les valeurs

INSERT INTO urba_plui_ecriture.pluiv_etude_libellestatut (code_statut,libelle_statut) VALUES
     ('01','A démarrer'),
     ('02','En cours'),
     ('03','Abandonnée'),
     ('04','Terminée'),
     ('05','Reportée');

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

-- DROP TABLE urba_plui_ecriture.pluiv_etudes;

CREATE TABLE urba_plui_ecriture.pluiv_etudes (
    id serial NOT NULL,
    code_etude text NOT NULL, -- Code de l'étude (identifiant métier pour faire le lien avec les urbanistes)
    nom_etude text NOT NULL, -- Libellé de l'étude
    description text NULL, -- Description de l'étude
    statut bpchar(2) NOT NULL, -- Statut de l'étude
    date_debut date NULL, -- Date de début de l'étude
    date_fin date NULL, -- Date de fin de l'étude
    commentaires text NULL, -- Informations complémentaires
    procedure_plui varchar(4) NULL, -- procedure de numérisation pour éventuel partitionnement
    geom geometry(MULTIPOLYGON, 3945) NULL, -- Emprise de l'étude
    CONSTRAINT pluiv_etudes_pk PRIMARY KEY (id),
    CONSTRAINT pluiv_etudes_unique UNIQUE (code_etude),
    CONSTRAINT pluiv_etudes_statut_fk FOREIGN KEY (statut) REFERENCES urba_plui_ecriture.pluiv_etude_libellestatut(code_statut)
);
COMMENT ON TABLE urba_plui_ecriture.pluiv_etudes IS 'Tableau de bord du suivi des études en lien avec les procédures';

-- Column comments

COMMENT ON COLUMN urba_plui_ecriture.pluiv_etudes.code_etude IS 'Code de l''étude (identifiant métier pour faire le lien avec les urbanistes)';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_etudes.nom_etude IS 'Libellé de l''étude';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_etudes.description IS 'Description de l''étude';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_etudes.statut IS 'Statut de l''étude';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_etudes.date_debut IS 'Date de début de l''étude';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_etudes.date_fin IS 'Date de fin de l''étude';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_etudes.commentaires IS 'Informations complémentaires';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_etudes.procedure_plui IS 'procedure de numérisation pour éventuel partitionnement';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_etudes.geom IS 'Emprise de l''étude';

-- table de correspondance projets <-> études #########################################################################

-- DROP TABLE urba_plui_ecriture.pluiv_corresp_projets_etudes;

CREATE TABLE urba_plui_ecriture.pluiv_corresp_projets_etudes (
    id serial NOT NULL, -- identifiant
    id_projet int4 NOT NULL, -- identifiant du projet
    id_etude int4 NOT NULL, -- identifiant de l'étude
    procedure_plui varchar(4) NULL, -- procedure de numérisation pour éventuel partitionnement
    CONSTRAINT pluiv_corresp_projets_etudes_pk PRIMARY KEY (id),
    CONSTRAINT pluiv_corresp_projets_etudes_projet_unique UNIQUE (id_projet),
    CONSTRAINT pluiv_corresp_projets_etudes_etude_unique UNIQUE (id_etude)
);

COMMENT ON TABLE urba_plui_ecriture.pluiv_corresp_projets_etudes IS 'Table de correspondance entre études et projets';

-- Column comments

COMMENT ON COLUMN urba_plui_ecriture.pluiv_corresp_projets_etudes.id IS 'identifiant';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_corresp_projets_etudes.id_projet IS 'identifiant du projet';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_corresp_projets_etudes.id_etude IS 'identifiant de l''étude';
COMMENT ON COLUMN urba_plui_ecriture.pluiv_corresp_projets_etudes.procedure_plui IS 'procedure de numérisation pour éventuel partitionnement';

-- FKEY sur table projet et étude
ALTER TABLE urba_plui_ecriture.pluiv_corresp_projets_etudes ADD CONSTRAINT pluiv_corresp_projets_etudes_fk FOREIGN KEY (id_etude) REFERENCES urba_plui_ecriture.pluiv_etudes(id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE urba_plui_ecriture.pluiv_corresp_projets_etudes ADD CONSTRAINT pluiv_corresp_projets_etudes_fk_1 FOREIGN KEY (id_projet) REFERENCES urba_plui_ecriture.pluiv_projets(id) ON DELETE CASCADE ON UPDATE CASCADE;

-- Table de suivi des modifications d'objets dans le périmètre d'une étude #########################################################################

-- Drop table

-- DROP TABLE urba_plui_ecriture.etude_corresp_etudes_projets_objets_embarques;

CREATE TABLE urba_plui_ecriture.etude_corresp_etudes_projets_objets_embarques (
    ogc_fid serial NOT NULL,
    id_obj text NOT NULL, -- Identifiant métier de l'objet embarqué dans le périmètre de l'étude
    id_projet int4 NOT NULL, -- Identifiant du projet d'appartenance
    id_etude int4 NOT NULL, -- Identifiant de l'étude
    modif bool NOT NULL DEFAULT false, -- Indicateur précisant si l'objet fait office d'une modif
    geom_ref geometry NULL, -- Géométrie de l'objet au moment de la création de l'étude
    geom_modif geometry NULL, -- Géométrie de l'objet à la validation de la VERSION
    procedure_plui varchar(4) NULL, -- procedure de numérisation pour éventuel partitionnement
    CONSTRAINT etude_corresp_etudes_projets_objets_embarques_ogc_fid_pk PRIMARY KEY (ogc_fid),
    CONSTRAINT etude_corresp_etudes_projets_objets_embarques_id_etude_fk FOREIGN KEY (id_etude) REFERENCES urba_plui_ecriture.pluiv_etudes(id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT etude_corresp_etudes_projets_objets_embarques_id_projet_fk FOREIGN KEY (id_projet) REFERENCES urba_plui_ecriture.pluiv_projets(id) ON UPDATE CASCADE ON DELETE CASCADE
);
COMMENT ON TABLE urba_plui_ecriture.etude_corresp_etudes_projets_objets_embarques IS 'Table listant les objets des projets embarqués dans l''emprise de l''étude associée';

-- Column comments

COMMENT ON COLUMN urba_plui_ecriture.etude_corresp_etudes_projets_objets_embarques.id_obj IS 'Identifiant métier de l''objet embarqué dans le périmètre de l''étude';
COMMENT ON COLUMN urba_plui_ecriture.etude_corresp_etudes_projets_objets_embarques.id_projet IS 'Identifiant du projet d''appartenance';
COMMENT ON COLUMN urba_plui_ecriture.etude_corresp_etudes_projets_objets_embarques.id_etude IS 'Identifiant de l''étude';
COMMENT ON COLUMN urba_plui_ecriture.etude_corresp_etudes_projets_objets_embarques.modif IS 'Indicateur précisant si l''objet fait office d''une modif';
COMMENT ON COLUMN urba_plui_ecriture.etude_corresp_etudes_projets_objets_embarques.geom_ref IS 'Géométrie de l''objet au moment de la création de l''étude';
COMMENT ON COLUMN urba_plui_ecriture.etude_corresp_etudes_projets_objets_embarques.geom_modif IS 'Géométrie de l''objet à la validation de la version';
COMMENT ON COLUMN urba_plui_ecriture.etude_corresp_etudes_projets_objets_embarques.procedure_plui IS 'procedure de numérisation pour éventuel partitionnement';


---------------------------------------------------------------------------
--                  Réajustement de la vue des études                    --
---------------------------------------------------------------------------
-- #123

DROP VIEW IF EXISTS urba_plui_ecriture.v__etude_suivi_etudes;
CREATE OR REPLACE VIEW urba_plui_ecriture.v__etude_suivi_etudes
AS WITH projets AS (
         SELECT _pluiv_projets.id_etude,
            array_agg(_pluiv_projets.id_projet) AS projets_rattaches
           FROM urba_plui_ecriture._pluiv_projets
          WHERE _pluiv_projets.id_etude IS NOT NULL
          GROUP BY _pluiv_projets.id_etude
        )
 SELECT e.ogc_fid,
    e.code_etude,
    e.nom_etude,
    e.num_cycle,
    e.description,
    e.statut,
    e.date_debut,
    e.date_fin,
    e.id_proc_cible,
    e.commentaires,
    projets.projets_rattaches,
    NULL::text[] AS projets_candidats,
    NULL::text[] AS projets_a_dupliquer,
    e.geom
   FROM urba_plui_ecriture._etude_suivi_etudes e
     LEFT JOIN urba_plui_ecriture._etude_suivi_procedures proc ON e.id_proc_cible = proc.ogc_fid
     LEFT JOIN projets ON e.ogc_fid = projets.id_etude;

COMMENT ON VIEW urba_plui_ecriture.v__etude_suivi_etudes IS 'Synthèse du tableau de bord du suivi des études';
COMMENT ON COLUMN urba_plui_ecriture.v__etude_suivi_etudes.nom_etude IS 'Libellé de l''étude';
COMMENT ON COLUMN urba_plui_ecriture.v__etude_suivi_etudes.num_cycle IS 'N° de cycle d''appartenance';
COMMENT ON COLUMN urba_plui_ecriture.v__etude_suivi_etudes.description IS 'Description de l''étude';
COMMENT ON COLUMN urba_plui_ecriture.v__etude_suivi_etudes.statut IS 'Statut de l''étude';
COMMENT ON COLUMN urba_plui_ecriture.v__etude_suivi_etudes.date_debut IS 'Date de début de l''étude';
COMMENT ON COLUMN urba_plui_ecriture.v__etude_suivi_etudes.date_fin IS 'Date de fin de l''étude';
COMMENT ON COLUMN urba_plui_ecriture.v__etude_suivi_etudes.id_proc_cible IS 'Identifiant de procédure cible';
COMMENT ON COLUMN urba_plui_ecriture.v__etude_suivi_etudes.commentaires IS 'Informations complémentaires';
COMMENT ON COLUMN urba_plui_ecriture.v__etude_suivi_etudes.projets_rattaches IS 'Liste des projets rattachés à l''étude';
COMMENT ON COLUMN urba_plui_ecriture.v__etude_suivi_etudes.projets_candidats IS 'Champ technique spécifiquement utilisé pour le formulaire QGIS';
COMMENT ON COLUMN urba_plui_ecriture.v__etude_suivi_etudes.projets_a_dupliquer IS 'Champ technique spécifiquement utilisé pour le formulaire QGIS';
COMMENT ON COLUMN urba_plui_ecriture.v__etude_suivi_etudes.geom IS 'Emprise de l''étude';

CREATE TRIGGER tv__etude_suivi_etudes INSTEAD OF INSERT OR DELETE OR UPDATE ON urba_plui_ecriture.v__etude_suivi_etudes FOR EACH ROW EXECUTE FUNCTION urba_plui_ecriture.tf_v__etude_suivi_etudes();

------------------------------------------------------------------------------------------------------
--                  Vue listant toutes les versions par études                                      --
------------------------------------------------------------------------------------------------------
CREATE OR REPLACE VIEW urba_plui_ecriture.v__etude_version AS
    SELECT v.ogc_fid, v.id_version, v.thematique, v.num_proj, v.num_version, v.statut, v.intitule, v.cycle_plui, v.id_projet , p.id_etude
    FROM urba_plui_ecriture."_etude_suivi_etudes" e
    JOIN urba_plui_ecriture."_pluiv_projets" p ON e.ogc_fid = p.id_etude
    JOIN urba_plui_ecriture."_pluiv_versionning_plui" v USING (id_projet)


-------------------------------------------------------------------------------------------------------
--                                  Fonctions sur les études                                         --
-------------------------------------------------------------------------------------------------------

-- ATTENTION MODIFICATION des fonctions créer et maj avec hstore
    -- pour un retour identifiant ajouter retour = 'oui' dans l'appel de la fonction
