-- MàJ de la version en BDD
UPDATE om_version SET om_version = '6.10.0' WHERE exists(SELECT 1 FROM om_version) = true;
INSERT INTO om_version
SELECT ('6.10.0') WHERE exists(SELECT 1 FROM om_version) = false;

--
-- BEGIN / [#10536] Mise à jour des données techniques et cerfas : CERFAs JAN 2025
--

-- Ajout de nouveaux champs

-- A_aménagement

-- a3x_nombre
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS a3x_nombre boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS a3x_nombre numeric;
COMMENT ON COLUMN cerfa.a3x_nombre IS 'Si les travaux sont réalisés par tranches : nombre';
COMMENT ON COLUMN donnees_techniques.a3x_nombre IS 'Si les travaux sont réalisés par tranches : nombre';


-- C_construction

-- c5ze6_piece
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS c5ze6_piece boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS c5ze6_piece boolean DEFAULT false;
COMMENT ON COLUMN cerfa.c5ze6_piece IS 'Type  de travaux : Transformation d’un garage en pièce de la construction';
COMMENT ON COLUMN donnees_techniques.c5ze6_piece IS 'Type  de travaux : Transformation d’un garage en pièce de la construction';

-- c2zi0_agrivoltaique
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS c2zi0_agrivoltaique boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS c2zi0_agrivoltaique boolean DEFAULT false;
COMMENT ON COLUMN cerfa.c2zi0_agrivoltaique IS 'Votre projet porte sur une installation agrivoltaïque : oui';
COMMENT ON COLUMN donnees_techniques.c2zi0_agrivoltaique IS 'Votre projet porte sur une installation agrivoltaïque : oui';

-- c2zi1_agrivoltaique
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS c2zi1_agrivoltaique boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS c2zi1_agrivoltaique boolean DEFAULT false;
COMMENT ON COLUMN cerfa.c2zi1_agrivoltaique IS 'Votre projet porte sur une installation agrivoltaïque : non';
COMMENT ON COLUMN donnees_techniques.c2zi1_agrivoltaique IS 'Votre projet porte sur une installation agrivoltaïque : non';

-- c2zm3_modification
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS c2zm3_modification boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS c2zm3_modification boolean DEFAULT false;
COMMENT ON COLUMN cerfa.c2zm3_modification IS 'Demande de modification d’une autorisation en cours de validité';
COMMENT ON COLUMN donnees_techniques.c2zm3_modification IS 'Demande de modification d’une autorisation en cours de validité';

-- c2zm4_regulvicepartiel
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS c2zm4_regulvicepartiel boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS c2zm4_regulvicepartiel boolean DEFAULT false;
COMMENT ON COLUMN cerfa.c2zm4_regulvicepartiel IS 'Demande de régularisation déposée en application de l’article L.600-5 du code de l’urbanisme';
COMMENT ON COLUMN donnees_techniques.c2zm4_regulvicepartiel IS 'Demande de régularisation déposée en application de l’article L.600-5 du code de l’urbanisme';

-- c2zm5_regulviceillegalite
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS c2zm5_regulviceillegalite boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS c2zm5_regulviceillegalite boolean DEFAULT false;
COMMENT ON COLUMN cerfa.c2zm5_regulviceillegalite IS 'Demande de régularisation déposée en application de l’article L.600-5-1 du code de l’urbanisme';
COMMENT ON COLUMN donnees_techniques.c2zm5_regulviceillegalite IS 'Demande de régularisation déposée en application de l’article L.600-5-1 du code de l’urbanisme';

-- c2zm6_regulnonconforme
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS c2zm6_regulnonconforme boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS c2zm6_regulnonconforme boolean DEFAULT false;
COMMENT ON COLUMN cerfa.c2zm6_regulnonconforme IS 'Demande de régularisation déposée en application de l’article R.462-9 du code de l’urbanisme';
COMMENT ON COLUMN donnees_techniques.c2zm6_regulnonconforme IS 'Demande de régularisation déposée en application de l’article R.462-9 du code de l’urbanisme';

-- c5zh1_agees
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS c5zh1_agees boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS c5zh1_agees numeric;
COMMENT ON COLUMN cerfa.c5zh1_agees IS 'Répartition du nombre de logements créés par type de résidence : Résidence pour personnes âgées';
COMMENT ON COLUMN donnees_techniques.c5zh1_agees IS 'Répartition du nombre de logements créés par type de résidence : Résidence pour personnes âgées';

-- c5zh2_etudiants
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS c5zh2_etudiants boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS c5zh2_etudiants numeric;
COMMENT ON COLUMN cerfa.c5zh2_etudiants IS 'Répartition du nombre de logements créés par type de résidence : Résidence pour étudiants';
COMMENT ON COLUMN donnees_techniques.c5zh2_etudiants IS 'Répartition du nombre de logements créés par type de résidence : Résidence pour étudiants';

-- c5zh3_tourisme
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS c5zh3_tourisme boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS c5zh3_tourisme numeric;
COMMENT ON COLUMN cerfa.c5zh3_tourisme IS 'Répartition du nombre de logements créés par type de résidence : Résidence de tourisme';
COMMENT ON COLUMN donnees_techniques.c5zh3_tourisme IS 'Répartition du nombre de logements créés par type de résidence : Résidence de tourisme';

-- c5zh4_residence
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS c5zh4_residence boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS c5zh4_residence numeric;
COMMENT ON COLUMN cerfa.c5zh4_residence IS 'Répartition du nombre de logements créés par type de résidence : Résidence hôtelière à vocation sociale';
COMMENT ON COLUMN donnees_techniques.c5zh4_residence IS 'Répartition du nombre de logements créés par type de résidence : Résidence hôtelière à vocation sociale';

-- c5zh5_sociale
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS c5zh5_sociale boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS c5zh5_sociale numeric;
COMMENT ON COLUMN cerfa.c5zh5_sociale IS 'Répartition du nombre de logements créés par type de résidence : Résidence sociale';
COMMENT ON COLUMN donnees_techniques.c5zh5_sociale IS 'Répartition du nombre de logements créés par type de résidence : Résidence sociale';

-- c5zh6_handicapes
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS c5zh6_handicapes boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS c5zh6_handicapes numeric;
COMMENT ON COLUMN cerfa.c5zh6_handicapes IS 'Répartition du nombre de logements créés par type de résidence : Résidence pour personnes handicapées';
COMMENT ON COLUMN donnees_techniques.c5zh6_handicapes IS 'Répartition du nombre de logements créés par type de résidence : Résidence pour personnes handicapées';

-- c5zh7_autres
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS c5zh7_autres boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS c5zh7_autres boolean DEFAULT false;
COMMENT ON COLUMN cerfa.c5zh7_autres IS 'Répartition du nombre de logements créés par type de résidence : Autres';
COMMENT ON COLUMN donnees_techniques.c5zh7_autres IS 'Répartition du nombre de logements créés par type de résidence : Autres';

-- c5zh8_autresprecision
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS c5zh8_autresprecision boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS c5zh8_autresprecision numeric;
COMMENT ON COLUMN cerfa.c5zh8_autresprecision IS 'Répartition du nombre de logements créés par type de résidence : Autres (nombre)';
COMMENT ON COLUMN donnees_techniques.c5zh8_autresprecision IS 'Répartition du nombre de logements créés par type de résidence : Autres (nombre)';

-- W_surfaces 2

-- w3es1_avanttravaux
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS w3es1_avanttravaux boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS w3es1_avanttravaux numeric;
COMMENT ON COLUMN cerfa.w3es1_avanttravaux IS 'Emprise au sol avant travaux (en m²)';
COMMENT ON COLUMN donnees_techniques.w3es1_avanttravaux IS 'Emprise au sol avant travaux (en m²)';

-- w3es2_creee
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS w3es2_creee boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS w3es2_creee numeric;
COMMENT ON COLUMN cerfa.w3es2_creee IS 'Emprise au sol créée (en m²)';
COMMENT ON COLUMN donnees_techniques.w3es2_creee IS 'Emprise au sol créée (en m²)';

-- w3es3_supprimee
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS w3es3_supprimee boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS w3es3_supprimee numeric;
COMMENT ON COLUMN cerfa.w3es3_supprimee IS 'Emprise au sol supprimée';
COMMENT ON COLUMN donnees_techniques.w3es3_supprimee IS 'Emprise au sol supprimée';


-- X_législation connexe

-- x1v_toiture
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS x1v_toiture boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS x1v_toiture boolean DEFAULT false;
COMMENT ON COLUMN cerfa.x1v_toiture IS 'Votre projet relève du II de l’article L. 171-4 du code de la construction et de l’habitation : Oui';
COMMENT ON COLUMN donnees_techniques.x1v_toiture IS 'Votre projet relève du II de l’article L. 171-4 du code de la construction et de l’habitation : Oui';

-- x1v0_toiture
ALTER TABLE ONLY cerfa ADD COLUMN IF NOT EXISTS x1v0_toiture boolean DEFAULT false;
ALTER TABLE ONLY donnees_techniques ADD COLUMN IF NOT EXISTS x1v0_toiture boolean DEFAULT false;
COMMENT ON COLUMN cerfa.x1v0_toiture IS 'Votre projet relève du II de l’article L. 171-4 du code de la construction et de l’habitation : Non';
COMMENT ON COLUMN donnees_techniques.x1v0_toiture IS 'Votre projet relève du II de l’article L. 171-4 du code de la construction et de l’habitation : Non';

--
-- END / [#10536] Mise à jour des données techniques et cerfas : CERFAs JAN 2025
--

--
-- BEGIN / [#10501] Ajouter le service consultant dans les demandeurs du dossier
--

COMMENT ON COLUMN lien_dossier_tiers.service_consultant IS 'Permet d''identifier un acteur du dossier comme étant le service consultant';

--
-- END / [#10501] Ajouter le service consultant dans les demandeurs du dossier
--

--
-- BEGIN / [#10545] Problème de notification lors de l'ajout d'une pièce
--

ALTER TABLE dossier_message ALTER COLUMN emetteur TYPE VARCHAR(150);

--
-- END / [#10545] Problème de notification lors de l'ajout d'une pièce
--

--
-- BEGIN / [#10551] Module d'ajout automatique du service consultant en tant que destinataire sur une instruction
--

ALTER TABLE ONLY evenement ADD COLUMN IF NOT EXISTS tiers_destinataire character varying(50);
COMMENT ON COLUMN evenement.tiers_destinataire IS 'Destinataire sur une instruction : aucun, acteur, acteur avec service consultant';

ALTER TABLE ONLY instruction ADD COLUMN IF NOT EXISTS tiers_destinataire integer DEFAULT NULL;
COMMENT ON COLUMN instruction.tiers_destinataire IS 'Destinataire sur une instruction : tiers identifié acteur';
ALTER TABLE ONLY instruction
    DROP CONSTRAINT IF EXISTS tiers_destinataire_lien_dossier_tiers_fkey;
ALTER TABLE ONLY instruction
    ADD CONSTRAINT tiers_destinataire_lien_dossier_tiers_fkey FOREIGN KEY (tiers_destinataire) REFERENCES tiers_consulte(tiers_consulte);

ALTER TABLE ONLY instruction ADD COLUMN IF NOT EXISTS tiers_destinataire_parametre character varying(50) DEFAULT NULL;
COMMENT ON COLUMN instruction.tiers_destinataire_parametre IS 'Destinataire sur une instruction : paramètre tiers_destinataire enregistré dans l''évènement';

-- Champs de fusion
UPDATE om_requete SET
    requete = REPLACE(requete, 'tiers_consulte.tiers_consulte = lien_dossier_tiers.tiers', 'tiers_consulte.tiers_consulte = lien_dossier_tiers.tiers
LEFT JOIN
        &DB_PREFIXEtiers_consulte AS tiers_consulte_destinataire
        ON
            tiers_consulte_destinataire.tiers_consulte = instruction.tiers_destinataire')
WHERE
    requete NOT LIKE '%tiers_consulte.tiers_consulte = lien_dossier_tiers.tiers
LEFT JOIN
        &DB_PREFIXEtiers_consulte AS tiers_consulte_destinataire
        ON
            tiers_consulte_destinataire.tiers_consulte = instruction.tiers_destinataire%'
    AND code = 'instruction';
--
UPDATE om_requete SET
    requete = REPLACE(requete, '&DB_PREFIXEadresse(''''::text, tiers_consulte.adresse::text, tiers_consulte.complement::text, ''''::text, ''''::text, tiers_consulte.cp::text, tiers_consulte.ville::text, ''''::text, ''''::text, '' ''::text) AS service_consultant_adresse', '&DB_PREFIXEadresse(''''::text, tiers_consulte.adresse::text, tiers_consulte.complement::text, ''''::text, ''''::text, tiers_consulte.cp::text, tiers_consulte.ville::text, ''''::text, ''''::text, '' ''::text) AS service_consultant_adresse,

        -- Tiers destinataire
        tiers_consulte_destinataire.libelle AS tiers_destinataire_libelle,
        &DB_PREFIXEadresse(''''::text, tiers_consulte_destinataire.adresse::text, tiers_consulte_destinataire.complement::text, ''''::text, ''''::text, tiers_consulte_destinataire.cp::text, tiers_consulte_destinataire.ville::text, ''''::text) AS tiers_destinataire_adresse_sdl,
        &DB_PREFIXEadresse(''''::text, tiers_consulte_destinataire.adresse::text, tiers_consulte_destinataire.complement::text, ''''::text, ''''::text, tiers_consulte_destinataire.cp::text, tiers_consulte_destinataire.ville::text, ''''::text, ''''::text, '' ''::text) AS tiers_destinataire_adresse')
WHERE
    requete NOT LIKE '%&DB_PREFIXEadresse(''''::text, tiers_consulte.adresse::text, tiers_consulte.complement::text, ''''::text, ''''::text, tiers_consulte.cp::text, tiers_consulte.ville::text, ''''::text, ''''::text, '' ''::text) AS service_consultant_adresse,

        -- Tiers destinataire
        tiers_consulte_destinataire.libelle AS tiers_destinataire_libelle,
        &DB_PREFIXEadresse(''''::text, tiers_consulte_destinataire.adresse::text, tiers_consulte_destinataire.complement::text, ''''::text, ''''::text, tiers_consulte_destinataire.cp::text, tiers_consulte_destinataire.ville::text, ''''::text) AS tiers_destinataire_adresse_sdl,
        &DB_PREFIXEadresse(''''::text, tiers_consulte_destinataire.adresse::text, tiers_consulte_destinataire.complement::text, ''''::text, ''''::text, tiers_consulte_destinataire.cp::text, tiers_consulte_destinataire.ville::text, ''''::text, ''''::text, '' ''::text) AS tiers_destinataire_adresse%'
    AND code = 'instruction';

UPDATE om_requete SET
    merge_fields = REPLACE(merge_fields, '[service_consultant_adresse] : adresse du service consultant sur une seule ligne', '[service_consultant_adresse] : adresse du service consultant sur une seule ligne
[tiers_destinataire_libelle] : nom du tiers destinataire
[tiers_destinataire_adresse_sdl] : adresse du tiers destinataire avec retours à la ligne sur les lignes de l’adresse
[tiers_destinataire_adresse] : adresse du tiers destinataire sur une seule ligne')
WHERE --Données générales de l'événement d'instruction
    merge_fields NOT LIKE '%[service_consultant_adresse] : adresse du service consultant sur une seule ligne
[tiers_destinataire_libelle] : nom du tiers destinataire
[tiers_destinataire_adresse_sdl] : adresse du tiers destinataire avec retours à la ligne sur les lignes de l’adresse
[tiers_destinataire_adresse] : adresse du tiers destinataire sur une seule ligne%'
    AND code = 'instruction';

--
-- END / [#10551] Module d'ajout automatique du service consultant en tant que destinataire sur une instruction
--

--
-- BEGIN / [#10547] Ajout du champ de fusion : département dépôt dossier
--

UPDATE om_requete SET
    requete = REPLACE(requete, 'dossier_instruction_type.libelle AS libelle_dit', 'dossier_instruction_type.libelle AS libelle_dit,
        (SELECT libelle FROM ledepartement) AS departement_dossier')
WHERE
    requete NOT LIKE '%dossier_instruction_type.libelle AS libelle_dit,
        (SELECT libelle FROM ledepartement) AS departement_dossier%'
    AND code = 'instruction';

UPDATE om_requete SET
    merge_fields = REPLACE(merge_fields, '[libelle_dit]', '[libelle_dit]
[departement_dossier] : libellé du département de dépôt du dossier')
WHERE
    merge_fields NOT LIKE '%[libelle_dit]
[departement_dossier] : libellé du département de dépôt du dossier%'
    AND code = 'instruction';

--
-- END / [#10547] Ajout du champ de fusion : département dépôt dossier
--

--
-- BEGIN / [#10556] Qualité Data demandeur.notification /Erreur sur la Copie des données lors d'un Modificatif sur dossier pré-demat 
--

UPDATE demandeur SET notification = FALSE WHERE notification IS NULL;
ALTER TABLE demandeur ALTER COLUMN notification SET DEFAULT FALSE;

--
-- END / [#10556] Qualité Data demandeur.notification /Erreur sur la Copie des données lors d'un Modificatif sur dossier pré-demat 
--
