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

-- 
-- BEGIN - [#10313] Permettre d’avoir des délais d’instruction en jours (instruction)
--
ALTER TABLE instruction
   ALTER COLUMN delai TYPE character varying(255),
   ALTER COLUMN delai SET DEFAULT '',
   ALTER COLUMN archive_delai TYPE character varying(255),
   ALTER COLUMN archive_delai SET DEFAULT '',
   ALTER COLUMN delai_notification TYPE character varying(255),
   ALTER COLUMN delai_notification SET DEFAULT '',
   ALTER COLUMN archive_delai_incompletude TYPE character varying(255),
   ALTER COLUMN archive_delai_incompletude SET DEFAULT '',
   ALTER COLUMN duree_validite TYPE character varying(255),
   ALTER COLUMN duree_validite SET DEFAULT '',
   ALTER COLUMN duree_validite_parametrage TYPE character varying(255),
   ALTER COLUMN duree_validite_parametrage SET DEFAULT '';

UPDATE instruction SET delai =
   CASE
      WHEN delai = '0'
      THEN ''
      ELSE CONCAT(delai, ' mois')
   END
   WHERE delai NOT LIKE '% mois';

UPDATE instruction SET archive_delai =
   CASE
      WHEN archive_delai = '0'
      THEN ''
      ELSE CONCAT(archive_delai, ' mois')
   END
   WHERE archive_delai NOT LIKE '% mois';

UPDATE instruction SET delai_notification =
   CASE
      WHEN delai_notification = '0'
      THEN ''
      ELSE CONCAT(delai_notification, ' mois')
   END
   WHERE delai_notification NOT LIKE '% mois';

UPDATE instruction SET archive_delai_incompletude =
   CASE
      WHEN archive_delai_incompletude = '0'
      THEN ''
      ELSE CONCAT(archive_delai_incompletude, ' mois')
   END
   WHERE archive_delai_incompletude NOT LIKE '% mois';

UPDATE instruction
    SET duree_validite = CASE
        WHEN duree_validite = '0' THEN '0 mois'
        ELSE CONCAT(duree_validite, ' mois')
    END
    WHERE duree_validite NOT LIKE '% mois';

UPDATE instruction
    SET duree_validite_parametrage = CASE
        WHEN duree_validite_parametrage = '0' THEN '0 mois'
        ELSE CONCAT(duree_validite_parametrage, ' mois')
    END
    WHERE duree_validite_parametrage NOT LIKE '% mois';
--
-- END - [#10313] Permettre d’avoir des délais d’instruction en jours (instruction)
--

--
-- BEGIN / [#10313] Permettre d’avoir des délais d’instruction en jours (evenement)
--
ALTER TABLE evenement 
    ALTER COLUMN delai TYPE character varying(255),
    ALTER COLUMN delai SET DEFAULT '',   
    ALTER COLUMN delai_notification TYPE character varying(255),
    ALTER COLUMN delai_notification SET DEFAULT '';


UPDATE evenement
SET delai = CASE
    WHEN delai = '0' THEN ''
    ELSE CONCAT(delai, ' mois')
END
WHERE delai NOT LIKE '% mois';

UPDATE evenement
SET delai_notification = CASE
    WHEN delai_notification = '0' THEN ''
    ELSE CONCAT(delai_notification, ' mois')
END
WHERE delai_notification NOT LIKE '% mois';

--
-- END - [#10313] Permettre d’avoir des délais d’instruction en jours (evenement)
--

--
-- BEGIN / [#10313] Permettre d’avoir des délais d’instruction en jours (dossier)
--
ALTER TABLE dossier
    ALTER COLUMN delai TYPE character varying(255),
    ALTER COLUMN delai SET DEFAULT '',
    ALTER COLUMN delai_incompletude TYPE character varying(255),
    ALTER COLUMN delai_incompletude SET DEFAULT '',
    ALTER COLUMN duree_validite TYPE character varying(255),
    ALTER COLUMN duree_validite SET DEFAULT '';

UPDATE dossier
SET duree_validite = CASE
    WHEN duree_validite = '0' THEN '0 mois'
    ELSE CONCAT(duree_validite, ' mois')
END
WHERE duree_validite NOT LIKE '% mois';

UPDATE dossier SET delai = 
CASE
    WHEN delai = '0'
    THEN ''
    ELSE CONCAT(delai, ' mois')
END
WHERE delai NOT LIKE '% mois';

UPDATE dossier SET delai_incompletude = 
CASE
    WHEN delai_incompletude = '0'
    THEN ''
    ELSE CONCAT(delai_incompletude, ' mois')
END
WHERE delai_incompletude NOT LIKE '% mois';

--
-- END - [#10313] Permettre d’avoir des délais d’instruction en jours (dossier)
--

--
-- BEGIN / [#10313] Permettre d’avoir des délais d’instruction en jours (action)
--
UPDATE action
SET
    regle_delai = REGEXP_REPLACE(regle_delai, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_limite = REGEXP_REPLACE(regle_date_limite, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_notification_delai = REGEXP_REPLACE(regle_date_notification_delai, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_complet = REGEXP_REPLACE(regle_date_complet, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_validite = REGEXP_REPLACE(regle_date_validite, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_decision = REGEXP_REPLACE(regle_date_decision, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_chantier = REGEXP_REPLACE(regle_date_chantier, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_achevement = REGEXP_REPLACE(regle_date_achevement, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_conformite = REGEXP_REPLACE(regle_date_conformite, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_rejet = REGEXP_REPLACE(regle_date_rejet, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_dernier_depot = REGEXP_REPLACE(regle_date_dernier_depot, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_limite_incompletude = REGEXP_REPLACE(regle_date_limite_incompletude, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_delai_incompletude = REGEXP_REPLACE(regle_delai_incompletude, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_autorite_competente = REGEXP_REPLACE(regle_autorite_competente, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_cloture_instruction = REGEXP_REPLACE(regle_date_cloture_instruction, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_premiere_visite = REGEXP_REPLACE(regle_date_premiere_visite, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_derniere_visite = REGEXP_REPLACE(regle_date_derniere_visite, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_contradictoire = REGEXP_REPLACE(regle_date_contradictoire, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_retour_contradictoire = REGEXP_REPLACE(regle_date_retour_contradictoire, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_ait = REGEXP_REPLACE(regle_date_ait, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_transmission_parquet = REGEXP_REPLACE(regle_date_transmission_parquet, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_date_affichage = REGEXP_REPLACE(regle_date_affichage, '(\d+)($| $|\+| \+)', '\1 mois'),
    regle_incompletude = REGEXP_REPLACE(regle_incompletude, '(\d+)($| $|\+| \+)', '\1 mois');

--
-- END - [#10313] Permettre d’avoir des délais d’instruction en jours (action)
--

--
-- BEGIN / [#10313] Permettre d’avoir des délais d’instruction en jours (dossier_autorisation_type_detaille)
--
ALTER TABLE dossier_autorisation_type_detaille 
    ALTER COLUMN duree_validite_parametrage TYPE character varying(255),
    ALTER COLUMN duree_validite_parametrage SET DEFAULT '';


UPDATE dossier_autorisation_type_detaille
SET duree_validite_parametrage = CASE
    WHEN duree_validite_parametrage = '0' THEN '0 mois'
    ELSE CONCAT(duree_validite_parametrage, ' mois')
END
WHERE duree_validite_parametrage NOT LIKE '% mois';

--
-- END - [#10313] Permettre d’avoir des délais d’instruction en jours (dossier_autorisation_type_detaille)
--

-- 
-- BEGIN - [#10313] Permettre d’avoir des délais d’instruction en jours (om_requete)
--
CREATE OR REPLACE FUNCTION calculate_date_with_duration(base_date DATE, duration_string VARCHAR, separator VARCHAR DEFAULT '+')
RETURNS DATE AS
$$
DECLARE
    duration_parts VARCHAR[];
    duration_part VARCHAR;
    calculated_date DATE;
BEGIN
    -- Populate calculated date with base date
    calculated_date := base_date;

    -- Convert input string into an array
    duration_parts := STRING_TO_ARRAY(duration_string, separator);

    -- Iterate over each part and accumulate the interval
    FOR i IN 1..ARRAY_LENGTH(duration_parts, 1) LOOP
        duration_part := duration_parts[i];

        -- Check if it contains 'months' or 'days'
        IF POSITION('months' IN duration_part) > 0 THEN
            calculated_date := calculated_date + MAKE_INTERVAL(MONTHS => SUBSTRING(duration_part, '\m[0-9]*\.?[0-9]+\M')::INTEGER);
        ELSIF POSITION('days' IN duration_part) > 0 THEN
            calculated_date := calculated_date + MAKE_INTERVAL(DAYS => SUBSTRING(duration_part, '\m[0-9]*\.?[0-9]+\M')::INTEGER);
        END IF;

    END LOOP;

    -- Return the final calculated date
    RETURN calculated_date;
END;
$$ LANGUAGE plpgsql;

UPDATE om_requete as omr SET
    requete = REPLACE(
            omr.requete,
            'to_char(dossier.date_limite_incompletude::date - make_interval(months => dossier.delai_incompletude), ''DD/MM/YYYY'') IS NOT NULL
        THEN to_char(dossier.date_limite_incompletude::date - make_interval(months => dossier.delai_incompletude), ''DD/MM/YYYY'')',
            '(dossier.date_limite_incompletude::text <> '''') IS TRUE AND (dossier.delai_incompletude::text <> '''') IS TRUE
            THEN to_char(&DB_PREFIXEcalculate_date_with_duration(dossier.date_limite_incompletude::date, REPLACE(REPLACE(dossier.delai_incompletude, ''jours'' , ''days''), ''mois'', ''months'')), ''DD/MM/YYYY'')'
        )
WHERE
    omr.code = 'instruction';

--
-- END - [#10313] Permettre d’avoir des délais d’instruction en jours (om_requete)
--


--
-- BEGIN / [#10346] Annexer un document de travail aux notifications
--

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'document_travail_notifier', (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR TECHNIQUE ET FONCTIONNEL')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'document_travail_notifier' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR TECHNIQUE ET FONCTIONNEL')
);

--
-- END / [#10346] Annexer un document de travail aux notifications
--


-- 
-- BEGIN - [#10345] - Remplissage de l'analyse reglémentaire d'un rapport d'instruction via un modèle 
--

CREATE TABLE IF NOT EXISTS modele_rapport_instruction (
    modele_rapport_instruction INTEGER,
    libelle CHARACTER VARYING(255) NOT NULL,
    code CHARACTER VARYING(50),
    contenu_om_html TEXT,
    om_validite_debut DATE,
    om_validite_fin DATE,
    CONSTRAINT modele_rapport_instruction_pkey PRIMARY KEY (modele_rapport_instruction)
);

COMMENT ON COLUMN modele_rapport_instruction.modele_rapport_instruction IS 'Identifiant unique numérique';
COMMENT ON COLUMN modele_rapport_instruction.libelle IS 'Nom du modèle';
COMMENT ON COLUMN modele_rapport_instruction.contenu_om_html IS 'Contenu du modèle';
COMMENT ON COLUMN modele_rapport_instruction.code IS 'Code du modèle';

CREATE SEQUENCE IF NOT EXISTS modele_rapport_instruction_seq
   START WITH 1
   INCREMENT BY 1
   NO MINVALUE
   NO MAXVALUE
   CACHE 1;

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'modele_rapport_instruction', (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR TECHNIQUE ET FONCTIONNEL')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'modele_rapport_instruction' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR TECHNIQUE ET FONCTIONNEL')
);

ALTER TABLE rapport_instruction
    ADD COLUMN IF NOT EXISTS modele INTEGER,
    ADD CONSTRAINT lien_rapport_instruction_modele_modele_rapport_instruction_fkey
        FOREIGN KEY (modele) REFERENCES modele_rapport_instruction(modele_rapport_instruction);

COMMENT ON COLUMN rapport_instruction.modele IS 'Modèle utilisé pour remplir le rapport d''instruction';

--
-- END   - [#10345] - Remplissage de l'analyse reglémentaire d'un rapport d'instruction via un modèle 
--
--


-- 
-- BEGIN - [#10339]  Lenteurs signalées par les utilisateurs : OrderBy date_limite_calcule
--

-- Ajout la date limit de dossier calculée en fonction de l'incomplétude ou non.
ALTER TABLE openads.dossier ADD COLUMN date_limite_calcule DATE DEFAULT NULL;

UPDATE openads.dossier SET date_limite_calcule = (
            CASE
                WHEN
                    dossier.incompletude is true 
                    AND dossier.incomplet_notifie is true
                THEN
                    dossier.date_limite_incompletude
                ELSE
                    dossier.date_limite
               END
    );

-- Ajoute la fonction de calcul de la date limite
\i stored_procedures/set_date_limite_calcule.sql

-- Ajoute un index sur la table openads.dossier sur la colone date_limite_calcule
-- Permet l'accélération des order by sur la date_limite_calcule
CREATE INDEX IF NOT EXISTS dossier_date_limite_calcule_idx on openads.dossier(date_limite_calcule);

-- Ajoute un index sur la table openads.instructeur sur la colone om_utilisateur
-- Permet l'accélération des joins de openads.instructeur sur la table openads.om_utilisateur
CREATE INDEX IF NOT EXISTS instructeur_om_utilisateur_idx on openads.instructeur(om_utilisateur);

-- 
-- END - [#10339]  Lenteurs signalées par les utilisateurs : OrderBy date_limite_calcule
--

--
-- BEGIN / [#10339] Lenteurs signalées par les utilisateurs - P3 "natures_travaux et donnees_techniques" 
--

-- Ajout la colonne de texte calculée de la description du projet selon les données techniques.
ALTER TABLE openads.dossier ADD COLUMN text_description_projet_calculee TEXT DEFAULT NULL;

-- Met à jour les dossiers existants avec la nouvelle valeur.
\i v6.5.0.dev0-set_text_description_projet_calculee.sql

-- Ajoute la fonction de calcul du texte calculée de la description du projet selon les données techniques.
\i stored_procedures/set_function_text_description_projet_calculee.sql

-- Ajout la colonne de texte calculée de la description du projet selon les données techniques.
ALTER TABLE openads.dossier ADD COLUMN text_nature_travaux_calculee TEXT DEFAULT NULL;

-- Met à jour les dossiers existants avec la nouvelle valeur.
\i v6.5.0.dev0-set_text_nature_travaux_calculee.sql

-- Ajoute la fonction de calcul du texte calculée de la description du projet selon les données techniques.
\i stored_procedures/set_function_text_nature_travaux_calculee.sql

--
-- END - [#10339] Lenteurs signalées par les utilisateurs - P3 "natures_travaux et donnees_techniques" 
--
