-- plugin plui versionning

-- uniquement las tables spécifiques au plugin
-- les vues pour modification des objets sont automatiquement déployées avec la fonction admin_deployer_vues_entite

-- ##########################################################################################################################################
-- vue des procédures
-- ##########################################################################################################################################

CREATE OR REPLACE VIEW urba_plui_ecriture.v_pluiv_procedures
AS SELECT
    id,
    code_proc,
    num_proc,
    num_phase,
    intitule,
    datefin_proc,
    commentaires,
    concat(code_proc,num_proc,'_PH',num_phase) AS procedure_plui
FROM
    urba_plui_ecriture.pluiv_procedures;

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

-- Column comments

COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_procedures.id IS 'identifiant unique';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_procedures.code_proc IS 'Code de procédure';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_procedures.num_proc IS 'N° de procédure';
COMMENT ON COLUMN urba_plui_ecriture.v_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.v_pluiv_procedures.intitule IS 'intitulé de procédure';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_procedures.datefin_proc IS 'Date de fin de procédure';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_procedures.commentaires IS 'Commentaires';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_procedures.procedure_plui IS 'Procédure/Phase de numérisation';

-- ##########################################################################################################################################
-- vues des versions
-- ##########################################################################################################################################

CREATE OR REPLACE VIEW urba_plui_ecriture.v_pluiv_versions AS
SELECT
    v.id,
    v.id_projet,
    concat(proc.code_proc,proc.num_proc,'_PH',proc.num_phase,'_',proj.categorie,'_',lpad(proj.num_proj::TEXT,3,'0'),'_',lpad(v.num_version::TEXT,2,'0')) AS id_version,
    proj.categorie,
    v.date_init,
    v.date_fin,
    CASE WHEN v_mere.id IS NOT NULL
        THEN concat(proc_mere.code_proc,proc_mere.num_proc,'_PH',proc_mere.num_phase,'_',proj_pere.categorie,'_',lpad(proj_pere.num_proj::TEXT,3,'0'),'_',lpad(v_mere.num_version::TEXT,2,'0'))
        ELSE NULL
    END AS version_mere,
    v.commentaires,
    v.num_version,
    v.statut,
    v.intitule,
    concat(proc.code_proc,proc.num_proc,'_PH',proc.num_phase) AS procedure_plui
FROM
    urba_plui_ecriture.pluiv_versions v
JOIN urba_plui_ecriture.pluiv_projets proj ON proj.id = v.id_projet
JOIN urba_plui_ecriture.pluiv_corresp_procedures_thematiques proc_them ON proj.id_proc_them = proc_them.id
JOIN urba_plui_ecriture.pluiv_procedures proc ON proc_them.id_procedure = proc.id
LEFT JOIN urba_plui_ecriture.pluiv_versions v_mere ON v_mere.id = v.id_version_mere
LEFT JOIN urba_plui_ecriture.pluiv_projets proj_pere ON proj_pere.id = v_mere.id_projet
LEFT JOIN urba_plui_ecriture.pluiv_corresp_procedures_thematiques proc_them_mere ON proj_pere.id_proc_them = proc_them_mere.id
LEFT JOIN urba_plui_ecriture.pluiv_procedures proc_mere ON proc_them_mere.id_procedure = proc_mere.id;

COMMENT ON VIEW urba_plui_ecriture.v_pluiv_versions IS 'Vue listant l''ensemble des versions de numérisation';

-- Column comments

COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_versions.id_projet IS 'identifiant du projet (Fkey)';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_versions.id_version IS 'Concaténation de la procédure, de la catégorie de thématique), du n° de projet et du n° de version';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_versions.categorie IS 'Catégorie du projet (classe d''entité + code CNIG pour PSC/INF)';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_versions.date_init IS 'Date de création de la version';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_versions.date_fin IS 'Date d''abandon du projet ou de la branche';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_versions.version_mere IS 'Version d''origine de la version';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_versions.commentaires IS 'sans commentaires !';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_versions.num_version IS 'N° de version';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_versions.statut IS 'Code de statut (cf liste dans la table ''versionlibellestatut'')';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_versions.intitule IS 'Titre du projet, adaptable en fonction de la version courante';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_versions.procedure_plui IS 'Procédure/Phase de numérisation';

-- ##########################################################################################################################################
-- vue des thématiques
-- ##########################################################################################################################################

CREATE OR REPLACE VIEW urba_plui_ecriture.v_pluiv_thematiques
AS WITH statut_thematique AS (
    SELECT
        proc_them.id_thematique,
        bool_or(CASE WHEN v.statut = '08' THEN TRUE ELSE FALSE END) AS version_validee -- vérification de la présence d'une version validée
    FROM urba_plui_ecriture.v_pluiv_versions v
    JOIN urba_plui_ecriture.pluiv_projets proj ON v.id_projet = proj.id
    JOIN urba_plui_ecriture.pluiv_corresp_procedures_thematiques proc_them on proj.id_proc_them = proc_them.id
    GROUP BY proc_them.id_thematique
    )
SELECT
    them.id,
    them.nom_thematique,
    them.description,
    them.classe_entite,
    them.code_cnig,
    them.categories,
    them.obsolete,
    statut_thematique.version_validee
FROM
    urba_plui_ecriture.pluiv_thematiques them
JOIN statut_thematique ON (them.id = statut_thematique.id_thematique);

COMMENT ON VIEW urba_plui_ecriture.v_pluiv_thematiques IS 'Liste des thématiques précisant pour chacune d''elles la présence d''une version validée';

-- Column comments

COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_thematiques.id IS 'identifiant unique';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_thematiques.nom_thematique IS 'Nom de la thématique';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_thematiques.description IS 'Description de la thématique';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_thematiques.classe_entite IS '"psc" (prescription) ou "inf" (information). Zonage et habillage ne sont pas concernés';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_thematiques.code_cnig IS 'Code CNIG embarqué dans la thématique';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_thematiques.categories IS 'Liste des sous-codes CNIG embarqués dans la thématique';
COMMENT ON COLUMN urba_plui_ecriture.v_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';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_thematiques.version_validee IS 'Indique si la thématique fait l''objet d''une version validée';

-- ##########################################################################################################################################
-- vue des catégories rattachables à une thématique
-- ##########################################################################################################################################

CREATE OR REPLACE VIEW urba_plui_ecriture.v_pluiv_categories_disponibles
AS SELECT
    classe_entite::TEXT,
    split_part(categorie,'-',1)::TEXT AS code_cnig,
    split_part(categorie,'-',2)::TEXT AS stype
FROM (
    SELECT
        'psc' AS classe_entite,
        concat(typepsc,'-',stypepsc) AS categorie
    FROM
        urba_plui_ecriture.prescriptionurbatype p
    WHERE
        NOT lib_supprime
        AND NOT lib_cacher
    UNION ALL
    SELECT
        'inf' AS classe_entite,
        concat(typeinf,'-',stypeinf) AS categorie
    FROM
        urba_plui_ecriture.informationurbatype i
    WHERE
        NOT lib_supprime
        AND NOT lib_cacher
) cat
WHERE NOT EXISTS (
    SELECT 1
    FROM urba_plui_ecriture.pluiv_thematiques them
    WHERE cat.classe_entite = them.classe_entite AND cat.categorie = ANY(them.categories)
    AND categories IS NOT NULL
)
ORDER BY classe_entite, code_cnig, stype
;

COMMENT ON VIEW urba_plui_ecriture.v_pluiv_categories_disponibles IS 'Liste des catégories disponibles pour la création ou la modification des thématiques';

COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_categories_disponibles.classe_entite IS '"psc" (prescription) ou "inf" (information). Zonage et habillage ne sont pas concernés';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_categories_disponibles.code_cnig IS 'Code CNIG de prescription ou d''information';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_categories_disponibles.stype IS 'Sous-code CNIG de prescritpion ou d''information';

-- ##########################################################################################################################################
-- vue des projets
-- ##########################################################################################################################################

CREATE OR REPLACE VIEW urba_plui_ecriture.v_pluiv_projets
AS SELECT
    p.id,
    p.nom_projet,
    p.description,
    p.date_init,
    p.date_fin,
    p.commentaires,
    p.categorie,
    lpad(p.num_proj::TEXT,3,'0') AS num_proj,
    t.nom_thematique,
    concat(proc.code_proc,proc.num_proc,'_PH',proc.num_phase) AS procedure_plui
FROM
    urba_plui_ecriture.pluiv_projets p
JOIN urba_plui_ecriture.pluiv_corresp_procedures_thematiques proc_them ON p.id_proc_them = proc_them.id
JOIN urba_plui_ecriture.pluiv_procedures proc ON proc_them.id_procedure = proc.id
JOIN urba_plui_ecriture.pluiv_thematiques t ON proc_them.id_thematique = t.id
  ORDER BY id_proc_them;

COMMENT ON VIEW urba_plui_ecriture.v_pluiv_projets IS 'Tableau de bord des projets faisant le lien entre les versions de numérisation et les études menées durant les procédures';

-- Column comments

COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_projets.id IS 'Identifiant unique';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_projets.nom_projet IS 'Intitulé du projet';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_projets.description IS 'Description du projet';
COMMENT ON COLUMN urba_plui_ecriture.v_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.v_pluiv_projets.date_fin IS 'Date de validation de version';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_projets.commentaires IS 'Informations complémentaires sur le suivi de la numérisation';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_projets.categorie IS 'Catégorie du projet (classe d''entité + code CNIG pour PSC/INF)';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_projets.num_proj IS 'N° du projet';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_projets.nom_thematique IS 'Intitulé de la thématique';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_projets.procedure_plui IS 'procédure de numérisation';

CREATE OR REPLACE VIEW urba_plui_ecriture.v_pluiv_projets_procedure
AS SELECT
    p.id AS id_projet,
    p.nom_projet,
    p.commentaires,
    p.categorie,
    t.nom_thematique,
    concat(proc.code_proc,proc.num_proc,'_PH',proc.num_phase) AS procedure_plui,
    p.embarque,
    v.id AS pk_version,
    v.id_version
FROM
    urba_plui_ecriture.pluiv_projets p
JOIN urba_plui_ecriture.pluiv_corresp_procedures_thematiques proc_them ON p.id_proc_them = proc_them.id
JOIN urba_plui_ecriture.pluiv_procedures proc ON proc_them.id_procedure = proc.id
JOIN urba_plui_ecriture.pluiv_thematiques t ON proc_them.id_thematique = t.id
LEFT JOIN urba_plui_ecriture.v_pluiv_versions v ON p.id = v.id_projet AND v.statut = '08'
  ORDER BY id_proc_them;

COMMENT ON VIEW urba_plui_ecriture.v_pluiv_projets_procedure IS 'Tableau de bord des projets pour préparer la fin de procédure';

-- Column comments

COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_projets_procedure.id_projet IS 'Identifiant de projet';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_projets_procedure.nom_projet IS 'Intitulé du projet';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_projets_procedure.categorie IS 'Catégorie du projet (classe d''entité + code CNIG pour PSC/INF)';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_projets_procedure.nom_thematique IS 'Intitulé de la thématique';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_projets_procedure.procedure_plui IS 'procédure de numérisation';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_projets_procedure.embarque IS 'Indique si le projet doit être pris en compte dans la fin de procédure';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_projets_procedure.pk_version IS 'Identifiant unique (PK) de la version validée du projet';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_projets_procedure.id_version IS 'Identifiant métier de la version validée du projet';

-- ##########################################################################################################################################
-- vues des styles
-- ##########################################################################################################################################

CREATE OR REPLACE VIEW urba_plui_ecriture.v_pluiv_styles AS
SELECT
    s.id,
    them.nom_thematique,
    concat(proc.code_proc, proc.num_proc, '_PH', proc.num_phase) AS procedure_plui,
    ls.stylename,
    upper(s.format) AS format,
    s.cnig
FROM
    urba_plui_ecriture.pluiv_styles s
JOIN urba_plui_ecriture.pluiv_procedures proc ON s.id_proc = proc.id
JOIN urba_plui_ecriture.pluiv_thematiques them ON s.id_them = them.id
JOIN public.layer_styles ls ON s.id_style = ls.id;

COMMENT ON VIEW urba_plui_ecriture.v_pluiv_styles IS 'Vue des styles associés à chaque thématique';

-- Column comments

COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_styles.id IS 'Identifiant unique (PK)';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_styles.nom_thematique IS 'Nom de la thématique associée au style';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_styles.procedure_plui IS 'Phase de procédure à partir de laquelle le style est appliqué à la thématique';
COMMENT ON COLUMN urba_plui_ecriture.v_pluiv_styles.stylename IS 'Nom du style dans la table public.layer_styles';
COMMENT ON COLUMN urba_plui_ecriture.v_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.v_pluiv_styles.cnig IS 'Indique si le style est celui fourni par le CNIG';
