--
-- BEGIN - [#10284] - Ajout du champ commentaire pour la généalogie 
--

ALTER TABLE ONLY genealogie 
    ADD COLUMN IF NOT EXISTS commentaire text;

--
-- END - [#10284] - Ajout du champ commentaire pour la généalogie 
--

--
-- BEGIN - [#10330] - Ajout du champ demandeur sur les opérations
--

ALTER TABLE ONLY operation
    ADD COLUMN IF NOT EXISTS demandeur integer,
    DROP CONSTRAINT IF EXISTS operation_demandeur_autorisation_fkey,
    ADD CONSTRAINT operation_demandeur_autorisation_fkey FOREIGN KEY (demandeur) REFERENCES autorisation(autorisation);

ALTER TABLE ONLY operation_archive
    ADD COLUMN IF NOT EXISTS demandeur integer;
--
-- END - [#10330] - Ajout du champ demandeur sur les opérations
--

--
-- BEGIN - [#10338] - Ajout du champ demandeur sur les travaux
--
ALTER TABLE ONLY travaux
    ADD COLUMN IF NOT EXISTS demandeur integer,
    DROP CONSTRAINT IF EXISTS travaux_demandeur_autorisation_fkey,
    ADD CONSTRAINT travaux_demandeur_autorisation_fkey FOREIGN KEY (demandeur) REFERENCES autorisation(autorisation);

ALTER TABLE ONLY travaux_archive
    ADD COLUMN IF NOT EXISTS demandeur integer;
--
-- END - [#10338] - Ajout du champ demandeur sur les travaux
--

--
-- BEGIN - [#10337] Restructuration des données du champ historique du défunt
--
ALTER TABLE ONLY operation_defunt
    DROP CONSTRAINT IF EXISTS operation_defunt_defunt_fkey;


UPDATE defunt
SET historique = (
    SELECT json_agg(
        json_build_object(
            'id', row_number,
            'categorie', substring(h, '^inhumation|transfert|reduction'),
            'type', 'inhumation',
            'dossier', substring(h, '\d*\-\d*'),
            'date', substring(h, '\d*\/\d*\/\d*'),
            'heure', (SELECT heure FROM operation WHERE numdossier = substring(h, '\d*\-\d*') LIMIT 1),
            'emplacement_source', COALESCE((SELECT emplacement FROM operation WHERE numdossier = substring(h, '\d*\-\d*') LIMIT 1), substring(h,'(?<=emplacement\s)\w*')::int),
            'emplacement_dest', (SELECT emplacement_transfert FROM operation WHERE numdossier = substring(h, '\d*\-\d*') AND categorie = 'transfert' LIMIT 1)
        )
    )
    FROM (  
        SELECT h, ROW_NUMBER() OVER () AS row_number
        FROM unnest(string_to_array(defunt.historique, E'\n')) h
        WHERE h ~ '^(inhumation|reduction|transfert)'
    ) subquery
)
WHERE historique IS NOT NULL AND historique != '' AND historique NOT LIKE '%[%{%}%]%';

-- gestion de l'historique des défunts archivés
UPDATE defunt_archive
SET historique = (
    SELECT json_agg(
        json_build_object(
            'id', row_number,
            'categorie', 'transfert',
            'type', 'exhumation',
            'dossier', substring(h, '\d*\-\d*'),
            'date', substring(h, '\d*\/\d*\/\d*'),
            'heure', (SELECT heure FROM operation_archive WHERE numdossier = substring(h, '\d*\-\d*') LIMIT 1),
            'emplacement_source', COALESCE((SELECT emplacement FROM operation_archive WHERE numdossier = substring(h, '\d*\-\d*') LIMIT 1), substring(h,'(?<=(concession|Concession)\s)\w*')::int),
            'emplacement_dest', (SELECT emplacement_transfert FROM operation_archive WHERE numdossier = substring(h, '\d*\-\d*') AND categorie = 'transfert' LIMIT 1)
        )
    )
    FROM (  
        SELECT h, ROW_NUMBER() OVER () AS row_number
        FROM unnest(string_to_array(defunt_archive.historique, E'\n')) h
        WHERE h ~ '^(inhumation|reduction|transfert|exhumé)'
    ) subquery
)
WHERE historique IS NOT NULL AND historique != '' AND historique NOT LIKE '%[%{%}%]%';

--
-- END - [#10337] Restructuration des données du champ historique du défunt
--

--
-- BEGIN - [#10356] - Conservation du pgeom lors de la création de l'emplacement libre suite à l'archivage
--
ALTER TABLE ONLY emplacement_archive
    ADD COLUMN IF NOT EXISTS geom public.geometry(Point,2154);
ALTER TABLE ONLY emplacement_archive
    ADD COLUMN IF NOT EXISTS pgeom public.geometry(MultiPolygon,2154);
--
-- END - [#10356] - Conservation du pgeom lors de la création de l'emplacement libre suite à l'archivage
--
