
-- ##########################################################################################################################################
-- Fonctions de gestion des thématiques
-- ##########################################################################################################################################

DROP FUNCTION IF EXISTS urba_plui_ecriture.qgis_verifier_projets_thematique(int);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.qgis_verifier_projets_thematique(id_them int)
 RETURNS TABLE(id_proj int, versions _text)
 LANGUAGE plpgsql
AS $function$

DECLARE
    projets_them record;
BEGIN

    -- >> Contrôles >>

    -- 1. Vérification de la présence d'une version validée
    IF (SELECT version_validee FROM urba_plui_ecriture.v_pluiv_thematiques WHERE id = id_them)
        THEN RAISE NOTICE 'La thématique contient une version validée. Elle ne peut pas être supprimée';
    END IF;

    -- >> OK. On continue le process >>

    -- 1. Récupération de tous les projets rattachés à la thématique
    RETURN QUERY SELECT
        proj.id AS id_proj,
        array_agg(v.id_version) AS versions
    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
    WHERE proc_them.id_thematique = id_them
    GROUP BY proj.id;

END

$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.qgis_verifier_projets_thematique(int) IS 'Vérifie les projets rattachés à la thématique en précisant la présence de versions validées';


DROP FUNCTION IF EXISTS urba_plui_ecriture.qgis_creer_thematique(id_proc int, new_nom_thematique text, new_description text, new_classe_entite text, new_code_cnig text, new_categories _text);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.qgis_creer_thematique(id_proc int, new_nom_thematique text, new_description text, new_classe_entite text, new_code_cnig text, new_categories _text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

DECLARE
    id_proc_them int;
    id_proj_dest int;
    id_version_dest int;
    id_them_dest int;

BEGIN

    -- Enregistrement de la thématique
    INSERT INTO urba_plui_ecriture.pluiv_thematiques (nom_thematique,description,classe_entite,code_cnig,categories)
    VALUES (
        new_nom_thematique,
        new_description,
        new_classe_entite,
        new_code_cnig,
        new_categories
    )
    RETURNING urba_plui_ecriture.pluiv_thematiques.id INTO id_them_dest;

    -- Association de la thématique à la procédure courante
    INSERT INTO urba_plui_ecriture.pluiv_corresp_procedures_thematiques (id_procedure,id_thematique) VALUES
    (id_proc,id_them_dest)
    RETURNING urba_plui_ecriture.pluiv_corresp_procedures_thematiques.id INTO id_proc_them;

    -- Création d'un premier projet
    INSERT INTO urba_plui_ecriture.pluiv_projets
    (id_proc_them,
    nom_projet,
    categorie,
    num_proj)
    VALUES (
            id_proc_them,
            concat('Projet vierge de la thématique "', new_nom_thematique, '"'),
            CASE
                WHEN new_classe_entite IN ('psc','inf') THEN concat(upper(LEFT(new_classe_entite,1)),new_code_cnig) -- categorie
                ELSE upper(new_classe_entite)
            END,
            1 -- 1er projet
        )
    RETURNING urba_plui_ecriture.pluiv_projets.id INTO id_proj_dest;
    RAISE NOTICE 'id_proj_dest : %', id_proj_dest;

    -- Création d'une version vierge en cours
    INSERT INTO urba_plui_ecriture.pluiv_versions
    (id_projet,
     num_version,
     intitule)
    VALUES (
            id_proj_dest,
            1, -- 1ère version
            'Version vierge en cours à renommer'
        )
    RETURNING urba_plui_ecriture.pluiv_versions.id INTO id_version_dest;
    RAISE NOTICE 'id_version_dest : %', id_version_dest;
END

$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.qgis_creer_thematique(id_proc int, new_nom_thematique text, new_description text, new_classe_entite text, new_code_cnig text, new_categories _text) IS 'Crée une nouvelle thématique avec un premier projet et une version vide prête à la numérisation';


----------------

DROP FUNCTION IF EXISTS urba_plui_ecriture.qgis_importer_thematique(id_them int, id_proc int);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.qgis_importer_thematique(id_them int, id_proc int)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

DECLARE
    id_proc_opp int;
    proj_them int;
    v int[] DEFAULT '{}';

BEGIN

    -- >> Contrôles >>
    -- Récupération de l'identifiant de la dernière procédure opposable
    SELECT id
    INTO id_proc_opp
    FROM urba_plui_ecriture.pluiv_procedures
    WHERE datefin_proc IS NOT NULL
    ORDER BY datefin_proc DESC
    LIMIT 1;

    IF id_proc_opp IS NULL
        THEN RAISE EXCEPTION 'Il n''existe pas de procédure opposable. cette action n''est pas permise';
    END IF;

    -- >> OK. On continue le process >>
    -- Pour chaque version validée des projets de la thématique de la dernière procédure opposable
    FOR proj_them IN SELECT proj.id AS id_proj, v.id AS v_validee
                    FROM urba_plui_ecriture.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
                    WHERE proc_them.id_thematique = id_them
                    AND proc_them.id_procedure = id_proc_opp
                    AND v.statut = '08'
    LOOP
        -- Import du projet avec la version validée seulement
        SELECT urba_plui_ecriture.qgis_importer_projet(proj_them, array_append('{}',proj_them.v_validee), id_proc);

    END LOOP;

END

$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.qgis_importer_thematique(id_them int, id_proc int) IS 'Récupère dans la procédure courante les données des versions validées des projets de la thématique issues de la dernière procédure opposable';

DROP FUNCTION IF EXISTS urba_plui_ecriture.qgis_supprimer_thematique(int);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.qgis_supprimer_thematique(id_them int)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

DECLARE

BEGIN

    -- >> Contrôles >>

    -- 1. Vérification de la présence d'une version validée
    IF (SELECT version_validee FROM urba_plui_ecriture.v_pluiv_thematiques WHERE id = id_them)
        THEN RAISE EXCEPTION 'La thématique contient une version validée. Elle ne peut pas être supprimée';
    END IF;

    -- >> OK. On continue le process >>

    -- Suppression de la thématique (pas besoin, géré par le trigger)
    DELETE FROM urba_plui_ecriture.pluiv_thematiques
    WHERE id = id_them;
END

$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.qgis_supprimer_thematique(int) IS 'Vérifie avant suppression de la thématique si aucune version validée n''y est rattachée';


DROP FUNCTION IF EXISTS urba_plui_ecriture.func_verifier_modification_thematique(IN classe_entite TEXT, IN old_categories _text, IN new_categories _text, OUT resultat TEXT, OUT used_categories _text);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.func_verifier_modification_thematique(IN classe_entite TEXT, IN old_categories _text, IN new_categories _text, OUT resultat TEXT, OUT used_categories _text)
-- RETURNS record
 LANGUAGE plpgsql
AS $function$

DECLARE
    removed_categories _text;
    categorie TEXT;
    query TEXT;
    query_result int;


BEGIN

    -- Résultat OK par défaut
    resultat = 'OK';

    -- >> Contrôles >>

    -- 1. Vérification si des sous-types ont été retirés de la liste
    SELECT ARRAY(SELECT UNNEST(old_categories) EXCEPT SELECT UNNEST(new_categories)) INTO removed_categories;

    -- Le cas échéant, on vérifie si des objets CNIG y sont rattachés
    IF array_length(removed_categories,1) > 0  THEN
        FOREACH categorie IN ARRAY removed_categories
        LOOP
            query = format('SELECT 1 FROM urba_plui_ecriture.%1$I WHERE type%2$s = %3$s AND stype%2$s = %4$s LIMIT 1'::TEXT,
                CASE WHEN classe_entite = 'psc' THEN 'prescription' ELSE 'information' END,
                classe_entite,
                quote_literal(split_part(categorie,'-',1)),
                quote_literal(split_part(categorie,'-',2))
            );
            RAISE NOTICE 'query : %', query;
            EXECUTE query INTO query_result;
            IF query_result = 1 THEN
                resultat = 'KO';
                SELECT array_append(used_categories,categorie) INTO used_categories;
                RAISE NOTICE 'used_categories : %', used_categories;
--              THEN RAISE EXCEPTION 'Le sous-type "%" a déjà été exploité dans une des vesions de la thématique. Impossible de le retirer', categorie;
            END IF;
        END LOOP;
    END IF;

    RAISE NOTICE 'Résultat : % (liste des catégories retirées déjà rattachées à des objets CNIG : %)', resultat, used_categories;

END

$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.func_verifier_modification_thematique(IN classe_entite TEXT, IN old_categories _text, IN new_categories _text, OUT resultat TEXT, OUT used_categories _text) IS 'Met à jour la thématique ou renvoie une erreur si des sous-types déjà exploités sont retirés';


DROP FUNCTION IF EXISTS urba_plui_ecriture.func_verifier_desactivation_thematique(id_them int);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.func_verifier_desactivation_thematique(id_them int)
 RETURNS boolean
 LANGUAGE sql
AS $function$
    -- >> Contrôles >>

    -- 1. Vérification s'il existe des versions validées de la thématique pour les procédures en cours
    select not EXISTS (
        SELECT 1
        FROM urba_plui_ecriture.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
        JOIN urba_plui_ecriture.pluiv_procedures proc on proc_them.id_procedure = proc.id
        WHERE proc_them.id_thematique = id_them
        AND proc.datefin_proc IS NULL
        AND v.statut = '08'
        )
$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.func_verifier_desactivation_thematique(id_them int) IS 'Vérifie la possibilité de désactiver la thématique';


-- ##########################################################################################################################################
-- Fonctions de duplication des projets / versions
-- ##########################################################################################################################################

DROP FUNCTION IF EXISTS urba_plui_ecriture.qgis_dupliquer_version(int);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.qgis_dupliquer_version(id_version_src int)
 RETURNS int
 LANGUAGE plpgsql
AS $function$

DECLARE
    version_src     record;
    id_version_validee TEXT;
    id_version_dest  int;
BEGIN

    -- Récupération des infos de la version
    SELECT * INTO version_src FROM urba_plui_ecriture.func_recuperer_infos_version(id_version_src);

    -- >> Contrôles >>

    -- 1. Vérification de l'état de la procédure
    IF (SELECT urba_plui_ecriture.func_verifier_statut_procedure(version_src.id_proc))
        THEN RAISE EXCEPTION 'La procédure cible est gelée. Cette action n''est pas permise';
    END IF;

    -- 2. Vérification du statut de la version à dupliquer
    IF (SELECT editable FROM urba_plui_ecriture.pluiv_versionlibellestatut WHERE valeur = version_src.statut)
        THEN RAISE EXCEPTION 'Le statut de la version "%" ne permet pas de la dupliquer', version_src.id_version;
    END IF;

    -- 3. Vérification de l'existence d'une version validée
    id_version_validee = (SELECT urba_plui_ecriture.func_proj_avec_version_validee(version_src.id_proj));
    IF id_version_validee IS NOT NULL
        THEN RAISE EXCEPTION 'Le projet contient déjà la version validée "%". Cette action n''est pas permise', id_version_validee;
    END IF;

    -- >> OK. On continue le process >>

    -- Duplication de la version
    SELECT urba_plui_ecriture.func_copier_donnees_version(version_src.id, version_src.id_proj) INTO id_version_dest;

    RETURN id_version_dest;
END

$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.qgis_dupliquer_version(int) IS 'Duplique une version au sein d''un même projet et renvoie l''identifiant de la nouvelle version';


DROP FUNCTION IF EXISTS urba_plui_ecriture.qgis_importer_version(int, int);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.qgis_importer_version(id_version_src int, id_proj_cible int)
 RETURNS int
 LANGUAGE plpgsql
AS $function$

DECLARE
    version_src     record;
    projet_dest     record;
    id_version_validee TEXT;
    id_version_dest  int;
BEGIN

    -- Récupération des infos de la version source
    SELECT * INTO version_src FROM urba_plui_ecriture.func_recuperer_infos_version(id_version_src);

    -- Récupération des infos du projet cible;
    SELECT * INTO projet_dest FROM urba_plui_ecriture.func_recuperer_infos_projet(id_proj_cible);

    -- >> Contrôles >>

    -- 1. Vérification de l'état de la procédure cible
    IF (SELECT urba_plui_ecriture.func_verifier_statut_procedure(projet_dest.id_proc))
        THEN RAISE EXCEPTION 'La procédure cible est gelée. Cette action n''est pas permise';
    END IF;

    -- 2. Vérification du statut de la version à dupliquer
    IF (SELECT editable FROM urba_plui_ecriture.pluiv_versionlibellestatut WHERE valeur = version_src.statut)
        THEN RAISE EXCEPTION 'Le statut de la version "%" ne permet pas de la dupliquer', version_src.id_version;
    END IF;

    -- 3. Vérification de l'existence d'une version validée
    id_version_validee = (SELECT urba_plui_ecriture.func_proj_avec_version_validee(projet_dest.id));
    IF id_version_validee IS NOT NULL
        THEN RAISE EXCEPTION 'Le projet contient déjà la version validée "%". Cette action n''est pas permise', id_version_validee;
    END IF;

    -- >> OK. On continue le process >>

    -- Duplication de la version
    SELECT urba_plui_ecriture.func_copier_donnees_version(version_src.id, projet_dest.id) INTO id_version_dest;

    RETURN id_version_dest;
END

$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.qgis_importer_version(int, int) IS 'Duplique une version d''un projet vers un autre';



DROP FUNCTION IF EXISTS urba_plui_ecriture.qgis_dupliquer_projet(id_proj_src int, ids_version int[]);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.qgis_dupliquer_projet(id_proj_src int, ids_version int[])
 RETURNS int
 LANGUAGE plpgsql
AS $function$

DECLARE
    version_src     record;
    id_version_src  int;
    id_version_validee  TEXT;
    projet_src      record;
    num_proj_dest   int;
    id_proj_dest int;
BEGIN

    -- Infos sur le projet et la procédure cible
    SELECT * INTO projet_src FROM urba_plui_ecriture.func_recuperer_infos_projet(id_proj_src);

    -- >> Contrôles >>

    -- 1. Vérification de l'état de la procédure
    IF (SELECT urba_plui_ecriture.func_verifier_statut_procedure(projet_src.id_proc))
        THEN RAISE EXCEPTION 'La procédure cible est gelée. Cette action n''est pas permise';
    END IF;

    -- 2. Vérification de l'existence d'une version validée dans le projet à dupliquer
    id_version_validee = (SELECT urba_plui_ecriture.func_proj_avec_version_validee(projet_src.id));
    IF id_version_validee IS NOT NULL
        THEN RAISE EXCEPTION 'Le projet à dupliquer contient déjà la version validée "%". Cette action n''est pas permise', id_version_validee;
    END IF;

    -- >> OK. On continue le process >>

    -- 1. Calcul du nouveau n° de projet
    num_proj_dest = (
        SELECT COALESCE(max(num_proj),0) + 1
        FROM urba_plui_ecriture.pluiv_projets proj
        JOIN urba_plui_ecriture.pluiv_corresp_procedures_thematiques proc_them on proj.id_proc_them = proc_them.id
        WHERE proj.categorie = projet_src.categorie
        AND proc_them.id_procedure = projet_src.id_proc
    );
    RAISE NOTICE 'Nouveau numéro de projet : %', num_proj_dest;

    -- 2. Enregistrement du projet
    INSERT INTO urba_plui_ecriture.pluiv_projets
    (id_proc_them,
    nom_projet,
    categorie,
    num_proj)
    VALUES (
            projet_src.id_proc_them,
            concat('Duplication du projet "', projet_src.nom_projet, '"'),
            projet_src.categorie,
            num_proj_dest
        )
    RETURNING urba_plui_ecriture.pluiv_projets.id INTO id_proj_dest;
    RAISE NOTICE 'id_proj_dest : %', id_proj_dest;

    -- 3. Duplication des versions
    FOREACH id_version_src IN ARRAY ids_version
        LOOP
            -- Récupération des infos de la version
            SELECT * INTO version_src FROM urba_plui_ecriture.func_recuperer_infos_version(id_version_src);

            -- Vérification du statut des versions à dupliquer
            IF (SELECT editable FROM urba_plui_ecriture.pluiv_versionlibellestatut WHERE valeur = version_src.statut)
                THEN RAISE EXCEPTION 'Le statut de la version "%" ne permet pas de la dupliquer', version_src.id_version;
                ELSE PERFORM urba_plui_ecriture.func_copier_donnees_version(version_src.id, id_proj_dest);
            END IF;

        END LOOP;

    RETURN id_proj_dest;
END

$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.qgis_dupliquer_projet(id_proj_src int, ids_version int[]) IS 'Duplique un projet au sein d''une même procédure et renvoie l''identifiant du nouveau projet';


DROP FUNCTION IF EXISTS urba_plui_ecriture.qgis_importer_projet(id_proj_src int, ids_version int[], id_proc_cible int);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.qgis_importer_projet(id_proj_src int, ids_version int[], id_proc_cible int)
 RETURNS int
 LANGUAGE plpgsql
AS $function$

DECLARE
    version_src     record;
    id_version_src  int;
    id_version_validee  TEXT;
    projet_src      record;
    id_proj_dest    int;
    num_proj_dest    int;
    id_proc_them_dest int;
BEGIN

    -- Infos sur le projet source
    SELECT * INTO projet_src FROM urba_plui_ecriture.func_recuperer_infos_projet(id_proj_src);

    -- >> Contrôles >>

    -- 1. Vérification de l'état de la procédure
    IF (SELECT urba_plui_ecriture.func_verifier_statut_procedure(id_proc_cible))
        THEN RAISE EXCEPTION 'La procédure cible est gelée. Cette action n''est pas permise';
    END IF;

    -- 2. Vérification de l'existence d'une version validée dans le projet à dupliquer
    -- FIXME : test nécessaire ?
--    id_version_validee = (SELECT urba_plui_ecriture.func_proj_avec_version_validee(projet_src.id));
--    IF id_version_validee IS NOT NULL
--        THEN RAISE EXCEPTION 'Le projet à dupliquer contient déjà la version validée "%". Cette action n''est pas permise', id_version_validee;
--    END IF;

    -- >> OK. On continue le process >>

    -- 1. Calcul du nouveau n° de projet

    -- Récupération du lien procédure/thématique
    RAISE NOTICE 'Id thématique : %; Id procédure cible : %', projet_src.id_them, id_proc_cible;
    SELECT proc_them.id INTO id_proc_them_dest
    FROM urba_plui_ecriture.pluiv_projets proj
    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
    JOIN urba_plui_ecriture.pluiv_thematiques t ON proc_them.id_thematique = t.id
    WHERE t.id = projet_src.id_them
    AND proc.id = id_proc_cible;
    RAISE NOTICE 'identifiant proc/them : %', id_proc_them_dest;

    -- Traitement difféent selon si la thématique du projet source existe dans la procédure cible
    IF id_proc_them_dest IS NULL -- La thématique n'est pas traitée dans la procédure
        THEN
            INSERT INTO urba_plui_ecriture.pluiv_corresp_procedures_thematiques
            (id_procedure,
            id_thematique)
            VALUES (
                    id_proc_cible,
                    projet_src.id_them
                )
            RETURNING urba_plui_ecriture.pluiv_corresp_procedures_thematiques.id INTO id_proc_them_dest;
            RAISE NOTICE 'id_proc_them_dest : %', id_proc_them_dest;

            -- initialisation du n° projet
            num_proj_dest = 1;

        ELSE -- Calcul du n° de projet
            num_proj_dest = (
            SELECT COALESCE(max(num_proj),0) + 1
            FROM urba_plui_ecriture.pluiv_projets
            WHERE id_proc_them = id_proc_them_dest
            );
            RAISE NOTICE 'Nouveau numéro de projet : %', num_proj_dest;
    END IF;

    -- 2. Enregistrement du projet
    INSERT INTO urba_plui_ecriture.pluiv_projets
    (id_proc_them,
    nom_projet,
    categorie,
    num_proj)
    VALUES (
            id_proc_them_dest,
            concat('Import du projet "', projet_src.nom_projet, '"'),
            projet_src.categorie,
            num_proj_dest
        )
    RETURNING urba_plui_ecriture.pluiv_projets.id INTO id_proj_dest;
    RAISE NOTICE 'id_proj_dest : %', id_proj_dest;

    -- 3. Duplication des versions
    FOREACH id_version_src IN ARRAY ids_version
        LOOP
            -- Récupération des infos de la version
            SELECT * INTO version_src FROM urba_plui_ecriture.func_recuperer_infos_version(id_version_src);

            -- Vérification du statut des versions à dupliquer
            IF (SELECT editable FROM urba_plui_ecriture.pluiv_versionlibellestatut WHERE valeur = version_src.statut)
                THEN RAISE EXCEPTION 'Le statut de la version "%" ne permet pas de la dupliquer', version_src.id_version;
                ELSE PERFORM urba_plui_ecriture.func_copier_donnees_version(version_src.id, id_proj_dest);
            END IF;

        END LOOP;

    RETURN id_proj_dest;
END

$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.qgis_importer_projet(id_proj_src int, ids_version int[], id_proc_cible int) IS 'Duplique un projet vers une autre procédure et renvoie l''identifiant du nouveau projet';


CREATE OR REPLACE FUNCTION urba_plui_ecriture.func_copier_donnees_version(id_version_src int, id_proj_dest int)
 RETURNS int
 LANGUAGE plpgsql
AS $function$

DECLARE
    tablename       TEXT;
    query           TEXT;
    version_src     record;
    projet_dest     record;
    num_version_dest int;
    id_version_dest int;
    columnnames     TEXT;
BEGIN

    -- Récupération des infos de la version
    SELECT * INTO version_src FROM urba_plui_ecriture.func_recuperer_infos_version(id_version_src);

    -- 1. Calcul du nouveau n° de version
    num_version_dest = (
--        SELECT lpad((COALESCE(max(num_version::int),0) + 1)::text, 2, '0') AS num_version -- Ajout de 0 à gauche du numéro de version pour avoir une chaîne de 2 caractères (0 avant le chiffre de 1 à 9)
        SELECT COALESCE(max(num_version),0) + 1 AS num_version
        FROM urba_plui_ecriture.pluiv_versions v
        WHERE v.id_projet = id_proj_dest
    );
    RAISE NOTICE 'Nouveau numéro de version : %', num_version_dest;


    -- 2. Récupération des infos nécessaires avant l'enregistrement en base
    -- 2.1. Infos sur le projet et la procédure cible
    SELECT * INTO projet_dest FROM urba_plui_ecriture.func_recuperer_infos_projet(id_proj_dest);
--    RAISE NOTICE 'projet_dest.categorie : %', projet_dest.categorie;
    -- 2.2. Nom de la table d'enregistrement
    SELECT urba_plui_ecriture.func_identifier_tablename(projet_dest.categorie) INTO tablename;

    -- 3. Enregistrement de la version
    INSERT INTO urba_plui_ecriture.pluiv_versions
    (id_projet,
     id_version_mere,
     num_version,
     intitule)
    VALUES (
            projet_dest.id,
            version_src.id, -- version de référence/mere (FK)
            num_version_dest,
            concat('Duplication de la version "', version_src.intitule, '"')
        )
    RETURNING urba_plui_ecriture.pluiv_versions.id INTO id_version_dest;
    RAISE NOTICE 'id_version_dest : %', id_version_dest;


    -- 4. Copie des entités
    -- 4.1. Récupération des champs de la table
    SELECT string_agg(column_name, ',')
    INTO columnnames
    FROM information_schema.columns
    WHERE table_schema = 'urba_plui_ecriture'
      AND table_name = tablename
      AND column_name NOT IN ('idurba', concat('id',tablename), 'lib_id_version'); -- Pas de copie du serial et de l'identifiant de version dans la 1ère étape d'import
    RAISE NOTICE 'Champs de la table % : %', tablename, columnnames;

    -- 4.2. Copie des objets
    query = format(
            'INSERT INTO urba_plui_ecriture.%1$I(%2$s,idurba,lib_id_version)
                SELECT %2$s, %3$L, %4$s
                FROM urba_plui_ecriture.%1$I
                WHERE lib_id_version = %5$s
                AND lib_hstore -> ''lib_modif''::text <> ''S''
            ',
            tablename,
            columnnames,
            concat(projet_dest.code_proc,projet_dest.num_proc,'_PH',projet_dest.num_phase,'_',projet_dest.categorie,'_',lpad(projet_dest.num_proj::TEXT,3,'0'),'_',lpad(num_version_dest::TEXT,2,'0')),
            id_version_dest, -- pour remplir lib_id_version
            version_src.id
        );
    RAISE NOTICE 'query : %', query;
    EXECUTE (query);

    -- 5. Réinitialisation de la valeur à 'N' de l'élément "lib_modif" de lib_hstore
    query = format(
            'UPDATE urba_plui_ecriture.%1$I
                SET lib_hstore = lib_hstore || (SELECT (''lib_modif=>'' || ''N''))::hstore
                WHERE lib_id_version = %2$s
            ',
            tablename,
            id_version_dest
        );

--    RAISE NOTICE 'query : %', query;
    EXECUTE (query);

    RETURN id_version_dest;
END

$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.func_copier_donnees_version(int, int) IS 'Procède à la copie de version et des objets qui y sont rattachés';

-- qgis_supprimer_version : Appelle la fonction de suppression si la version n'a pas de dépendance de version ou de réunion

CREATE OR REPLACE FUNCTION urba_plui_ecriture.qgis_supprimer_version(this_version int)
 RETURNS character
 LANGUAGE plpgsql
AS $function$
DECLARE
    module_planning pg_tables%rowtype;
    heritage urba_plui_ecriture.pluiv_versions%rowtype;
    planning_dependancy boolean = false;
    heritage_dependancy boolean = false;

BEGIN

-- si une version utilise cette version comme version mère impossible de la supprimer définitivement

    RAISE NOTICE 'planning_dependancy : % ', planning_dependancy;

    -- Vérification des dépendances d'héritage
    SELECT * INTO heritage
    FROM urba_plui_ecriture.pluiv_versions
    WHERE id_version_mere = (SELECT id FROM urba_plui_ecriture.pluiv_versions v_mere WHERE v_mere.id = this_version);

    IF FOUND THEN
        RAISE NOTICE 'Dépendance d''héritage';
        heritage_dependancy = TRUE;
    END IF;

    IF planning_dependancy OR heritage_dependancy
        THEN
            RAISE NOTICE 'Une dépendance trouvée';
            RETURN 'error_link';
        ELSE
            -- Suppression de la version
            RAISE NOTICE 'Suppression de la version %', this_version;
            DELETE
            FROM urba_plui_ecriture.pluiv_versions
            WHERE id = this_version;
    END IF;

    RETURN this_version;
END;
$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.qgis_supprimer_version(int) IS 'Appelle la fonction de suppression si la version n'' pas de dépendance de version ou de réunion';

-- ##########################################################################################################################################
-- Fonctions de fin de procédure
-- ##########################################################################################################################################

CREATE OR REPLACE FUNCTION urba_plui_ecriture.qgis_terminer_procedure(id_proc int, new_phase bool)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    versions_validees int[];
    thematiques_traitees int[];
    thematiques_non_traitees int[];
    proj_proc record;

    proc_opposable int;

    proc_gelee record;
    new_num_phase int;

    query TEXT;

BEGIN

    -- 0. Vérification si la procédure n'est pas déjà terminée
    IF EXISTS (
            SELECT 1
            FROM urba_plui_ecriture.pluiv_procedures
            WHERE id = id_proc
            AND datefin_proc IS NOT NULL
        ) THEN RAISE EXCEPTION 'Cette procédure est déjà terminée. Cette action n''est pas permise.';
    END IF;

    -- 1. Vérification de l'existence d'une version validée pour chaque projet traité dans cette procédure
    FOR proj_proc IN (
        SELECT proj.id,
            proj.nom_projet,
            v.id AS v_validee,
            proc.procedure_plui,
            proc_them.id_thematique AS id_them
        FROM urba_plui_ecriture.pluiv_projets proj
        JOIN urba_plui_ecriture.pluiv_corresp_procedures_thematiques proc_them on proj.id_proc_them = proc_them.id
        JOIN urba_plui_ecriture.v_pluiv_procedures proc on proc_them.id_procedure = proc.id
        LEFT JOIN urba_plui_ecriture.pluiv_versions v ON proj.id = v.id_projet AND v.statut = '08'
        WHERE proc_them.id_procedure = id_proc
        AND proj.embarque
    )
    LOOP
        IF proj_proc.v_validee IS NULL
            THEN -- Problème de version validée. Abandon de l'opération
                RAISE EXCEPTION 'Le projet % traité dans la procédure % n''a pas de version validée. Impossible de terminer cette procédure.', proj_proc.nom_projet, proj_proc.procedure_plui;
            ELSE -- Enregistrement de la thématique
                SELECT array_append(thematiques_traitees, proj_proc.id_them) INTO thematiques_traitees;
                SELECT array_append(versions_validees, proj_proc.v_validee) INTO versions_validees;
        END IF;
    END LOOP;

    RAISE NOTICE 'Liste des thématiques traitées : %', thematiques_traitees;
    RAISE NOTICE 'Liste des versions validées : %', versions_validees;

    -- Test sur l'existence d'au moins une version validée. Sinon rejet
--    RAISE NOTICE 'versions_validees : %', versions_validees;
    IF array_length(versions_validees,1) IS NULL
        THEN
            RAISE EXCEPTION 'Aucun projet n''est embarqué dans la fin de cette procédure. Opération annulée';
    END IF;

    -- 2. Récupération de l'id de la dernière procédure opposable
    SELECT id INTO proc_opposable
    FROM urba_plui_ecriture.pluiv_procedures
    WHERE datefin_proc IS NOT NULL
    ORDER BY datefin_proc DESC
    LIMIT 1;
    RAISE NOTICE 'Proc opposable : %', proc_opposable;

    -- 3. Enregistrement des versions de thématiques traitées
    query = format ('
            INSERT INTO urba_plui_ecriture.pluiv_versions_proc_terminees (id_proc, id_them, id_version)
            SELECT %1$s, *
            FROM unnest(%2$s::int[], %3$s::int[])', id_proc, quote_literal(thematiques_traitees), quote_literal(versions_validees));
    RAISE NOTICE 'Enregistrement des thématiques traitées: %', query;
    EXECUTE query;

    -- 4. Récupération de la liste des thématiques non traitées
    SELECT ARRAY(
            SELECT id FROM urba_plui_ecriture.pluiv_thematiques
          EXCEPT
            SELECT UNNEST(thematiques_traitees)
        ) INTO thematiques_non_traitees;
    RAISE NOTICE 'Liste des thématiques non traitées : %', thematiques_non_traitees;

    -- 5. Enregistrement des versions validées des thématiques non traitées depuis la procédure opposable
    query = format ('
            INSERT INTO urba_plui_ecriture.pluiv_versions_proc_terminees (id_proc, id_them, id_version)
            SELECT  %1$s, id_them, id_version
            FROM urba_plui_ecriture.pluiv_versions_proc_terminees
            WHERE id_proc = %2$s
            AND ARRAY[id_them] <@ %3$s::int[]', id_proc, proc_opposable, quote_literal(thematiques_non_traitees));
    RAISE NOTICE 'Enregistrement des thématiques non traitées: %', query;
    EXECUTE query;

    -- 6. Gel de la procédure
    -- 6.1 Suppression des versions abandonnées
    DELETE FROM urba_plui_ecriture.pluiv_versions
    WHERE id IN (
        SELECT v.id
        FROM urba_plui_ecriture.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
        WHERE proc_them.id_procedure = id_proc
        AND v.statut = '04' -- version abandonnée
    );
    -- 6.2 Gel des versions en cours
    UPDATE urba_plui_ecriture.pluiv_versions
    SET
        statut = '03',
        commentaires = concat('Version figée lors du gel de la procédure [anciens commentaires : "',commentaires,'"]')
    WHERE id IN (
        SELECT v.id
        FROM urba_plui_ecriture.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
        WHERE proc_them.id_procedure = id_proc
        AND v.statut = '02' -- version en cours
    );
    -- 6.3 Mise à jour de la date de fin de la procédure
    UPDATE urba_plui_ecriture.pluiv_procedures
    SET datefin_proc = now()
    WHERE id = id_proc;

    -- 7. Etape optionnnelle de création d'une nouvelle phase de procédure
    IF new_phase THEN

        -- 7.1. récupération des infos de la procédure
        SELECT code_proc, num_proc, num_phase, intitule
        INTO proc_gelee
        FROM urba_plui_ecriture.pluiv_procedures
        WHERE id = id_proc;

        -- 7.2. Calcul du n° de la nouvlle phase
        SELECT max(num_phase)+1 INTO new_num_phase
        FROM urba_plui_ecriture.pluiv_procedures
        WHERE code_proc = proc_gelee.code_proc
        AND num_proc = proc_gelee.num_proc;

        INSERT INTO urba_plui_ecriture.pluiv_procedures (code_proc,num_proc,num_phase,intitule,commentaires)
        VALUES (
            proc_gelee.code_proc,
            proc_gelee.num_proc,
            new_num_phase,
            concat('Nouvelle phase de la procécure "',proc_gelee.intitule,'"'),
            'Création de nouvelle phase après gel de la phase précédente'
        );

    END IF;

END;
$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.qgis_terminer_procedure(id_proc int, new_phase bool) IS 'Enregistre la liste des versions validées pour chaque thématique de la procédure avant de la geler. Crée en option une nouvelle phase de procédure';


-- ##########################################################################################################################################
-- Fonctions de vérification
-- ##########################################################################################################################################


DROP FUNCTION IF EXISTS urba_plui_ecriture.func_identifier_tablename(categorie char(3));
CREATE OR REPLACE FUNCTION urba_plui_ecriture.func_identifier_tablename(categorie char(3))
 RETURNS TEXT
 LANGUAGE plpgsql
AS $function$
DECLARE
    tablename TEXT;

BEGIN
    IF LEFT(categorie,1) = 'Z' THEN
        tablename = 'zoneurba';
    ELSIF LEFT(categorie,1) = 'P' THEN
        tablename = 'prescription';
    ELSIF LEFT(categorie,1) = 'I' THEN
        tablename = 'information';
    ELSIF LEFT(categorie,1) = 'H' THEN
        tablename = 'habillage';
    ELSE
        RAISE EXCEPTION 'Pas de table associée à cette catégorie !';
    END IF;
    RAISE NOTICE 'tablename : %', tablename;

    RETURN tablename;
END;
$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.func_identifier_tablename(categorie char(3)) IS 'Renvoie le nom de table de la classe d''entité en fonction de la catégorie renseignée';


----------------

CREATE OR REPLACE FUNCTION urba_plui_ecriture.func_verifier_statut_procedure(id_proc int)
 RETURNS boolean
 LANGUAGE sql
AS $function$
    SELECT EXISTS (
        SELECT 1
            FROM urba_plui_ecriture.pluiv_procedures
            WHERE id = id_proc
            AND datefin_proc IS NOT NULL
        )
$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.func_verifier_statut_procedure(id_proc int) IS 'Vérifie si la procédure est terminée';

----------------

CREATE OR REPLACE FUNCTION urba_plui_ecriture.func_id_version_depuis_idurba(p_idurba text)
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
    v_proc_token  text;   -- ex: MAJ8 ou M3
    v_code_proc   text;   -- ex: MAJ ou M
    v_num_proc    int;    -- ex: 8 ou 3 (NULL si pas de chiffre)
    v_phase_txt   text;   -- ex: PH1
    v_num_phase   int;    -- ex: 1
    v_cat         text;   -- ex: ZON / HAB / I02 / P07 ...
    v_num_projet  int;    -- ex: 1
    v_num_version int;    -- ex: 1
    v_id_version  int;
BEGIN
    -- Format attendu : PROC_PHASE_CAT_PROJET_VERSION  (ex: MAJ8_PH1_I02_001_01)
    v_proc_token  := split_part(p_idurba, '_', 1);
    v_phase_txt   := split_part(p_idurba, '_', 2);
    v_cat         := split_part(p_idurba, '_', 3);
    v_num_projet  := NULLIF(split_part(p_idurba, '_', 4), '')::int;
    v_num_version := NULLIF(split_part(p_idurba, '_', 5), '')::int;

    IF v_proc_token IS NULL OR v_phase_txt IS NULL OR v_cat IS NULL
       OR v_num_projet IS NULL OR v_num_version IS NULL THEN
        RAISE EXCEPTION
          'idurba invalide (%) : attendu PROC_PHASE_CAT_PROJET_VERSION (ex: MAJ8_PH1_I02_001_01)',
          p_idurba;
    END IF;

    -- PH1 -> 1
    v_num_phase := NULLIF(regexp_replace(v_phase_txt, '\D', '', 'g'), '')::int;
    IF v_num_phase IS NULL THEN
        RAISE EXCEPTION 'idurba (%) : phase % invalide (attendu PH<numero>)', p_idurba, v_phase_txt;
    END IF;

    -- MAJ8 -> code_proc='MAJ', num_proc=8   /  M3 -> code_proc='M', num_proc=3
    v_code_proc := NULLIF(regexp_replace(v_proc_token, '\d', '', 'g'), '');
    v_num_proc  := NULLIF(regexp_replace(v_proc_token, '\D', '', 'g'), '')::int;

    IF v_code_proc IS NULL THEN
        RAISE EXCEPTION 'idurba (%) : proc % invalide', p_idurba, v_proc_token;
    END IF;

    -- Normalisation catégorie (au cas où)
    v_cat := upper(v_cat);

    -- Recherche : on filtre bien sur la catégorie projet = token 3
    SELECT v.id
      INTO v_id_version
      FROM urba_plui_ecriture.pluiv_versions v
      JOIN urba_plui_ecriture.pluiv_projets p
        ON p.id = v.id_projet
      JOIN urba_plui_ecriture.pluiv_corresp_procedures_thematiques c
        ON c.id = p.id_proc_them
      JOIN urba_plui_ecriture.pluiv_procedures pr
        ON pr.id = c.id_procedure
     WHERE pr.code_proc   = v_code_proc
       AND pr.num_phase   = v_num_phase
       AND (v_num_proc IS NULL OR pr.num_proc = v_num_proc)
       AND p.num_proj     = v_num_projet
       AND v.num_version  = v_num_version
       AND p.categorie    = v_cat
     LIMIT 1;

    IF v_id_version IS NULL THEN
        RAISE EXCEPTION
          'Aucune version trouvée pour idurba=% (code_proc=% num_proc=% phase=% categorie=% projet=% version=%).',
          p_idurba, v_code_proc, v_num_proc, v_num_phase, v_cat, v_num_projet, v_num_version;
    END IF;

    RETURN v_id_version;
END;
$$;


----------------

DROP FUNCTION IF EXISTS urba_plui_ecriture.func_recuperer_infos_version(this_version int);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.func_recuperer_infos_version(this_version int)
 RETURNS table(id integer, id_version TEXT, intitule TEXT, categorie char(3), statut char(2), date_fin date, id_proj int, id_proc int, id_them int)
 LANGUAGE sql
AS $function$
    SELECT
        v.id,
        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,
        v.intitule,
        proj.categorie,
        v.statut,
        v.date_fin,
        proj.id AS id_proj,
        proc_them.id_procedure AS id_proc,
        proc_them.id_thematique AS id_them
    FROM urba_plui_ecriture.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
        JOIN urba_plui_ecriture.pluiv_procedures proc on proc_them.id_procedure = proc.id
    WHERE v.id = this_version;
$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.func_recuperer_infos_version(int4) IS 'Récupère les infos de la version renseignée';

----------------

DROP FUNCTION IF EXISTS urba_plui_ecriture.func_recuperer_infos_projet(this_project int);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.func_recuperer_infos_projet(this_project int)
 RETURNS table(id integer, id_proc int, id_them int, id_proc_them int, nom_projet TEXT, num_proj int, categorie char(3), code_proc TEXT, num_proc int, num_phase int)
 LANGUAGE sql
AS $function$
    SELECT
        proj.id,
        proc.id AS id_proc,
        them.id AS id_them,
        proj.id_proc_them,
        proj.nom_projet,
        proj.num_proj,
        proj.categorie,
        proc.code_proc,
        proc.num_proc,
        proc.num_phase
    FROM urba_plui_ecriture.pluiv_projets proj
        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
        JOIN urba_plui_ecriture.pluiv_thematiques them ON proc_them.id_thematique = them.id
    WHERE proj.id = this_project;
$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.func_recuperer_infos_projet(int4) IS 'Récupère les infos du projet renseigné';

----------------

DROP FUNCTION IF EXISTS urba_plui_ecriture.func_proj_avec_version_validee(this_project int);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.func_proj_avec_version_validee(this_project int)
 RETURNS TEXT
 LANGUAGE sql
AS $function$
        SELECT 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
        FROM urba_plui_ecriture.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
        JOIN urba_plui_ecriture.pluiv_procedures proc on proc_them.id_procedure = proc.id
        WHERE id_projet = this_project
        AND statut = '08'
$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.func_proj_avec_version_validee(int4) IS 'Renvoie le cas échéant l''identifiant de la version validée';



-- #################################################LES FONCTIONS qgis_[operation]_version #################################################################


CREATE OR REPLACE FUNCTION urba_plui_ecriture.qgis_modifier_statut_version(this_version int, new_status TEXT)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    version_ref             record;
    has_child               boolean;
    has_validated_version   boolean;
    end_date                date;
BEGIN
    -- Récupération des infos de la version
    SELECT * INTO version_ref FROM urba_plui_ecriture.func_recuperer_infos_version(this_version);

    -- Vérification si cette version a une version fille
    has_child = (SELECT EXISTS (SELECT 1 FROM urba_plui_ecriture.pluiv_versions WHERE id_version_mere = this_version));
--    RAISE NOTICE 'Version fille ? %', has_child;

    -- Vérification si le projet a une version déjà validée
    has_validated_version = (SELECT EXISTS (SELECT 1 FROM urba_plui_ecriture.pluiv_versions WHERE id_projet = version_ref.id_proj AND statut = '08'));
--    RAISE NOTICE e'Version validée ? % \n', has_validated_version;

    -- Vérification si la procédure est gelée
    IF EXISTS (
        SELECT 1
            FROM urba_plui_ecriture.pluiv_procedures
            WHERE id = version_ref.id_proc
            AND datefin_proc IS NOT NULL
        )
        THEN RAISE EXCEPTION 'La procédure cible est gelée. Cette action n''est pas permise';
    END IF;

    -- Vérification du statut cible au regard du statut courant
    IF
        (version_ref.statut = '01' AND new_status <> '01')
     OR
        (version_ref.statut = '02' AND new_status IN ('01','08'))
     OR
        (version_ref.statut = '03' AND new_status = '02' AND has_child)
     OR
        (version_ref.statut = '03' AND new_status = '01')
     OR
        (version_ref.statut = '03' AND new_status = '08' AND has_validated_version)
     OR
        (version_ref.statut = '04' AND new_status IN ('01','03','08'))
     OR
        (version_ref.statut = '04' AND new_status = '02' AND has_child)
     OR
        (version_ref.statut = '08' AND new_status <> '03')

        THEN RAISE EXCEPTION 'Le nouveau statut est incompatible avec le statut courant. Cette action n''est pas permise';

    END IF;

    -- Renseignement (ou non) de la date de fin
    IF new_status IN ('03','04','08')
        THEN end_date = now();
        ELSE end_date = NULL;
    END IF;

    -- Si pas d'erreur, mise à jour du statut et de la date de fin (le cas échéant)
    UPDATE urba_plui_ecriture.pluiv_versions
    SET
        statut = new_status,
        date_fin = end_date
    WHERE id = this_version;

END;
$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.qgis_modifier_statut_version(int, TEXT) IS 'Modifie le statut d''une version';

-- FONCTION : qgis_modifier_version : Permet de mettre à jour l''intitulé ou les commentaires de la version

CREATE OR REPLACE FUNCTION urba_plui_ecriture.qgis_modifier_version(new hstore)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
    -- Mise à jour de la table pluiv_versions
    PERFORM urba_plui_ecriture.func_maj_avec_hstore('urba_plui_ecriture', 'pluiv_versions', new, 'ogc_fid',
                                                new -> 'ogc_fid');

END;
$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.qgis_modifier_version(hstore) IS 'Permet de mettre à jour l''intitulé ou les commentaires de la version';



-- FONCTION : fin d'une procédure = le projet est intégrer dans les version de référence

-- TODO : dans projet datefin = date fin procédure sur les projets qui comportent une version validée et version status = 01


-- ##########################################################################################################################################
-- Fonction de récupération du code INSEE
-- ##########################################################################################################################################

CREATE OR REPLACE FUNCTION urba_plui_ecriture.func_recuperer_code_insee(objet geometry)
 RETURNS character
 LANGUAGE plpgsql
 STABLE
AS $function$
DECLARE
    cinsee text;
BEGIN

    -- TODO : problème avec zonage limitrophe de commune
    -- le ST_intersects remonte le code insee de la commune limitrophe
    -- dans ce cas utiliser le centroid de l'objet ?

    -- concatenation des code insee
    SELECT string_agg(code_insee, ' / ')
    INTO cinsee
    FROM urba_plui_ecriture.ref_limites_communales lc
    WHERE ST_Intersects(lc.geom, objet);

    RAISE NOTICE 'Code INSEE : %', cinsee;

    IF cinsee IS NULL THEN
        RAISE EXCEPTION 'Objet en dehors du périmètre territorial. Enregistrement non admis.';
    END IF;

    RETURN cinsee;
END

$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.func_recuperer_code_insee(geometry) IS 'récupère le ou les codes insee';

-- ##########################################################################################################################################
-- Fonction de vérification es champs CNIG
-- ##########################################################################################################################################

CREATE OR REPLACE FUNCTION urba_plui_ecriture.func_verifier_champs_entite()
 RETURNS character
 LANGUAGE plpgsql
 STABLE
AS $function$
DECLARE
    cinsee text;
BEGIN

    -- TODO : problème avec zonage limitrophe de commune
    -- le ST_intersects remonte le code insee de la commune limitrophe
    -- dans ce cas utiliser le centroid de l'objet ?

    -- concatenation des code insee
    SELECT string_agg(code_insee, ' / ')
    INTO cinsee
    FROM urba_plui_ecriture.ref_limites_communales lc
    WHERE ST_Intersects(lc.geom, objet);

    RAISE NOTICE 'Code INSEE : %', cinsee;

    IF cinsee IS NULL THEN
        RAISE EXCEPTION 'Objet en dehors du périmètre territorial. Enregistrement non admis.';
    END IF;

    RETURN cinsee;
END

$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.func_recuperer_code_insee(geometry) IS 'récupère le ou les codes insee';

-- ##########################################################################################################################################
-- Ces fonctions permettent de manipuler les HSTORE
-- ##########################################################################################################################################
--
--DROP FUNCTION urba_plui_ecriture.func_creer_avec_hstore(text,text,hstore);

-- CREATION

CREATE OR REPLACE FUNCTION urba_plui_ecriture.func_creer_avec_hstore(
    nschema text,
    ntable text,
    kv hstore,
    retour TEXT DEFAULT NULL)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
DECLARE
    identifiant text;
BEGIN

    -- Avec ou sans retour d'identifiant
    -- ATTENTION adhérence avec module étude sur table 'etude_suivi_etudes' ???

    IF (retour IS NULL) THEN

        EXECUTE (
            SELECT 'insert into ' || quote_ident(nschema) || '.' || quote_ident(ntable) || ' (' ||
                   string_agg(quote_ident(key), ',') || ') values (' ||
                   string_agg(quote_nullable(value), ',') || ')'
            FROM each(kv)
            WHERE value IS NOT NULL);
        identifiant = NULL;
    ELSE

        EXECUTE (
            SELECT 'insert into ' || quote_ident(nschema) || '.' || quote_ident(ntable) || ' (' ||
                   string_agg(quote_ident(key), ',') || ') values (' ||
                   string_agg(quote_nullable(value), ',') || ') returning ogc_fid'
            FROM each(kv)
            WHERE value IS NOT NULL) INTO identifiant;

    END IF;


    RETURN identifiant;
END;
$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.func_creer_avec_hstore(text,text,hstore,text) IS 'Permet de créer un enregistrement dans une table avec des attributs au format HSTORE';

----------------

--DROP FUNCTION urba_plui_ecriture.func_maj_avec_hstore(text,text,hstore,text,anyelement);

CREATE OR REPLACE FUNCTION urba_plui_ecriture.func_maj_avec_hstore(
    nschema text,
    ntable text,
    kv hstore,
    nid text,
    nid_value anyelement,
    retour TEXT DEFAULT NULL)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
DECLARE
    identifiant text;
BEGIN

    -- NOT IN pour les tables qui n'ont pas besoin d'un retour d'identifiant
    -- pour une fonction plus générique : ajouter une variable en entrée
    IF (retour IS NULL) THEN


        EXECUTE (
            SELECT 'update ' || quote_ident(nschema) || '.' || quote_ident(ntable) || ' set ' ||
                   string_agg(x, ',') || ' where ' || quote_ident(nid) || '=' || quote_literal(nid_value)
            FROM (
                SELECT quote_ident(key) || '=' || quote_nullable(value) AS x
                FROM each(kv)) t);

        identifiant = NULL;

    ELSE -- avec retour d'identifiant

        EXECUTE (
            SELECT 'update ' || quote_ident(nschema) || '.' || quote_ident(ntable) || ' set ' ||
                   string_agg(x, ',') || ' where ' || quote_ident(nid) || '=' || quote_literal(nid_value) ||
                   ' returning ogc_fid'
            FROM (
                SELECT quote_ident(key) || '=' || quote_nullable(value) AS x
                FROM each(kv)) t) INTO identifiant;

    END IF;

    RETURN identifiant;

END;
$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.func_maj_avec_hstore(text,text,hstore,text,anyelement,text) IS 'Permet de mettre à jour un enregistrement dans une table avec des attributs au format HSTORE';

-- ##########################################################################################################################################
-- Fonctions de gestion des styles
-- ##########################################################################################################################################


DROP FUNCTION IF EXISTS urba_plui_ecriture.func_verifier_existence_style(id_version int);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.func_verifier_existence_style(id_version int)
 RETURNS int
 LANGUAGE plpgsql
AS $function$

DECLARE
    version_src record;

BEGIN

    -- Récupération des infos de la version
    SELECT * INTO version_src FROM urba_plui_ecriture.func_recuperer_infos_version(id_version);

    -- Renvoie un éventuel identifiant de style non CNIG dans le contexte donné
    RETURN (SELECT id_style
    FROM urba_plui_ecriture.pluiv_styles
    WHERE id_them = version_src.id_them
    AND id_proc = version_src.id_proc
    AND NOT cnig);

END

$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.func_verifier_existence_style(id_version int) IS 'Renvoie un éventuel identifiant de style créé pour la thématique dans le contexte de cette phase de procédure';

----------------

DROP FUNCTION IF EXISTS urba_plui_ecriture.func_recuperer_details_style(IN geometry_column TEXT, OUT format TEXT, OUT type_geom TEXT);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.func_recuperer_details_style(IN geometry_column TEXT, OUT format TEXT, OUT type_geom TEXT)
 LANGUAGE plpgsql
AS $function$

BEGIN
        IF geometry_column IN ('geom','geom_surf')
            THEN
                format = 'surf';
                type_geom = 'Polygon';
        ELSIF geometry_column = 'geom_lin'
            THEN
                format = 'lin';
                type_geom = 'Linestring';
        ELSIF geometry_column IN ('geom_pct','geom_txt')
            THEN
                format = 'pct';
                type_geom = 'Point';
            ELSE
                RAISE EXCEPTION 'Nom de colonne de géométrie inconnu !';
        END IF;
END

$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.func_recuperer_details_style(IN geometry_column TEXT, OUT format TEXT, OUT type_geom TEXT) IS 'Renvoie le format et le type de géométrie en fonction du nom de colonne de géométrie du style';

----------------

DROP FUNCTION IF EXISTS urba_plui_ecriture.qgis_charger_style(id_version int, geometry_column TEXT);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.qgis_charger_style(id_version int, geometry_column TEXT)
 RETURNS int
 LANGUAGE plpgsql
AS $function$

DECLARE
    version_src record;
    details_style record;
    style_id int;

BEGIN

    -- Récupération des infos de la version
    SELECT * INTO version_src FROM urba_plui_ecriture.func_recuperer_infos_version(id_version);

    -- récupération du format et du type de géométrie à partir du nom de colonne de géométrie
    SELECT * INTO details_style FROM urba_plui_ecriture.func_recuperer_details_style(geometry_column);

    -- Cas A : un style a été créé au cours de la même procédure
    SELECT id_style INTO style_id
    FROM urba_plui_ecriture.pluiv_styles
    WHERE id_them = version_src.id_them
    AND id_proc = version_src.id_proc
    AND NOT cnig;

    -- On renvoie l'id s'il existe, sinon on continue
    IF style_id IS NOT NULL
        THEN RETURN style_id;
    END IF;

    -- Cas B : la version concerne une procédure terminée. On recherche le style associé à la procédure dont la date de fin est la plus proche de celle de la procédure de la version
    SELECT DISTINCT ON (s.id_them, proc.datefin_proc)
        id_style INTO style_id
    FROM urba_plui_ecriture.pluiv_styles s
    JOIN public.layer_styles ls ON s.id_style = ls.id
    JOIN urba_plui_ecriture.pluiv_procedures proc_version ON proc_version.id = version_src.id_proc
    CROSS JOIN urba_plui_ecriture.pluiv_procedures proc
    WHERE id_them = version_src.id_them
    AND proc.datefin_proc > proc_version.datefin_proc
    AND ls."type" = details_style.type_geom
    AND NOT cnig
    ORDER BY s.id_them, proc.datefin_proc DESC;

    -- On renvoie l'id s'il existe, sinon on continue
    IF style_id IS NOT NULL
        THEN RETURN style_id;
    END IF;

    -- Cas C : la version concerne une procédure en cours. On recherche le dernier style créé pour la thématique
    SELECT DISTINCT ON (s.id_them, ls.update_time)
        id_style INTO style_id
    FROM urba_plui_ecriture.pluiv_styles s
    JOIN public.layer_styles ls ON s.id_style = ls.id
    JOIN urba_plui_ecriture.pluiv_procedures proc ON proc.id = version_src.id_proc
    WHERE id_them = version_src.id_them
    AND proc.datefin_proc IS NULL
    AND ls."type" = details_style.type_geom
    AND NOT cnig
    ORDER BY s.id_them, ls.update_time DESC;

    -- On renvoie l'id s'il existe, sinon on continue
    IF style_id IS NOT NULL
        THEN RETURN style_id;
    END IF;

    -- Cas D : aucun style n'a été trouvé. On utilise le style CNIG par défaut (s'il existe)
    SELECT id_style INTO style_id
    FROM urba_plui_ecriture.pluiv_styles
    WHERE id_them = version_src.id_them
    AND cnig;

    IF style_id IS NOT NULL
        THEN
            RETURN style_id;
        ELSE
            RAISE NOTICE 'Aucun style trouvé pour cette thématique';
    END IF;

    RETURN style_id;
END

$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.qgis_charger_style(id_version int, type_geom TEXT) IS 'Renvoie l''identifiant de style par défaut à appliquer pour la thématique dans le contexte de la procédure de la version à charger';

----------------

DROP FUNCTION IF EXISTS urba_plui_ecriture.qgis_creer_style(id_version int, geometry_column TEXT, qml XML);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.qgis_creer_style(id_version int, geometry_column TEXT, qml XML)
 RETURNS int
 LANGUAGE plpgsql
AS $function$

DECLARE
    version_src record;
    details_style record;
    tablename TEXT;
    new_style_id int;

    symbologie TEXT;
    type_geom  TEXT;
BEGIN
        -- Récupération des infos de la version
        SELECT * INTO version_src FROM urba_plui_ecriture.func_recuperer_infos_version(id_version);

        -- récupération du format et du type de géométrie à partir du nom de colonne de géométrie
        SELECT * INTO details_style FROM urba_plui_ecriture.func_recuperer_details_style(geometry_column);

        -- 1. Vérification de l'état de la procédure
        IF (SELECT urba_plui_ecriture.func_verifier_statut_procedure(version_src.id_proc))
            THEN RAISE EXCEPTION 'La procédure cible est gelée. Cette action n''est pas permise';
        END IF;

    -- >> OK. On continue le process >>

        -- 1. Récupération des informations d'enregistrement
        -- 1.1 Table d'enregistrement
        SELECT urba_plui_ecriture.func_identifier_tablename(version_src.categorie) INTO tablename;

        -- 2. Enregistrement du style en base
        INSERT INTO public.layer_styles (
                            f_table_catalog,
                            f_table_schema,
                            f_table_name,
                            f_geometry_column,
                            stylename,
                            styleqml,
                            useasdefault,
                            type)
            VALUES (
                    'sig', -- FIXME : adhérence
                    'urba_plui_ecriture', -- FIXME : adhérence
                    tablename,
                    geometry_column,
                    concat('pluiv_ct_',tablename,'_',details_style.format),
                    qml,
                    FALSE,
                    type_geom)
        RETURNING public.layer_styles.id INTO new_style_id;

        -- 3. Enregistrement du style dans la table de gestion
        INSERT INTO urba_plui_ecriture.pluiv_styles (id_them,id_style,id_proc,format)
        VALUES (
            version_src.id_them,
            new_style_id,
            version_src.id_proc,
            details_style.format);

        RETURN new_style_id;

END

$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.qgis_creer_style(id_version int, geometry_column TEXT, qml XML) IS 'Enregistre un nouveau style associé à la thématique traitée par la version chargée';


----------------

DROP FUNCTION IF EXISTS urba_plui_ecriture.qgis_modifier_style(id_style int, qml XML);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.qgis_modifier_style(id_style int, qml XML)
 RETURNS void
 LANGUAGE sql
AS $function$

    -- Mise à jour du style en base
    UPDATE public.layer_styles
    SET styleqml = qml
    WHERE id = id_style;

$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.qgis_modifier_style(id_version int, qml XML) IS 'Met à jour le style associé à la thématique dans le contexte de la procédure de la version';



----------------

DROP FUNCTION IF EXISTS urba_plui_ecriture.qgis_supprimer_style(id_style int);
CREATE OR REPLACE FUNCTION urba_plui_ecriture.qgis_supprimer_style(id_style int)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

DECLARE
    style_details record;
BEGIN

    SELECT * INTO style_details
    FROM urba_plui_ecriture.pluiv_styles ps
    WHERE ps.id = $1;

    -- >> Contrôles >>

    -- 1. Vérification si c'est un style CNIG
    IF (style_details.cnig)
        THEN RAISE EXCEPTION 'Le style à supprimer est un style "CNIG" par défaut. Cette action n''est pas permise';
    END IF;

    -- 2. Vérification de l'état de la procédure
    IF (SELECT urba_plui_ecriture.func_verifier_statut_procedure(style_details.id_proc))
        THEN RAISE EXCEPTION 'La procédure cible est gelée. Cette action n''est pas permise';
    END IF;

    -- >> OK. On continue le process >>

    -- Suppression du style
    DELETE
    FROM urba_plui_ecriture.pluiv_styles ps
    WHERE ps.id_style = style_details.id_style;

END

$function$
;

COMMENT ON FUNCTION urba_plui_ecriture.qgis_supprimer_style(id_style int) IS 'Supprime le style associé à la thématique dans le contexte de la procédure de la version';
