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

--
-- BEGIN / [#10416] Modification des champs de fusion concernant les DIA
--

-- Modification du type des champs de integer vers numeric
ALTER TABLE donnees_techniques ALTER COLUMN ci6sl1 TYPE numeric;
ALTER TABLE donnees_techniques ALTER COLUMN ci6sl2 TYPE numeric;
ALTER TABLE donnees_techniques ALTER COLUMN ci6sl3 TYPE numeric;
ALTER TABLE donnees_techniques ALTER COLUMN ci6sl4 TYPE numeric;
ALTER TABLE donnees_techniques ALTER COLUMN ci6sl5 TYPE numeric;

UPDATE om_requete SET
    requete = REPLACE(requete, 'COALESCE (donnees_techniques.dia_mod_cess_prix_vente,'''') AS dia_mod_cess_prix_vente,', 'COALESCE (CASE WHEN donnees_techniques.dia_mod_cess_prix_vente IS NOT NULL THEN donnees_techniques.dia_mod_cess_prix_vente ELSE donnees_techniques.fi1c END,'''') AS dia_mod_cess_prix_vente,')
WHERE
    code = 'instruction';

-- Le champ de fusion dia_mod_cess_prix_vente doit afficher le prix de vente en lettre
-- et en chiffre ou seulement en lettre pour les DIA utilisant la dernière version du cerfa
UPDATE om_requete SET
    merge_fields = REPLACE(merge_fields, '[dia_mod_cess_prix_vente] (Prix de vente en lettres et chiffres)', '[dia_mod_cess_prix_vente] (Prix de vente en lettres et chiffres ou seulement en lettres)')
WHERE
    code = 'instruction';

--
-- END / [#10416] Modification des champs de fusion concernant les DIA
--

--
-- BEGIN / [#10459] Permettre la soustraction de délai d'une date dans la requête des champs de fusion
--

--
DROP FUNCTION IF EXISTS calculate_date_with_duration(date, varchar, varchar);
DROP FUNCTION IF EXISTS calculate_date_with_duration(date, varchar, varchar, varchar);
CREATE OR REPLACE FUNCTION calculate_date_with_duration(base_date DATE, duration_string VARCHAR, operator VARCHAR DEFAULT '+', 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
            IF operator = '+' THEN
                calculated_date := calculated_date + MAKE_INTERVAL(MONTHS => SUBSTRING(duration_part, '\m[0-9]*\.?[0-9]+\M')::INTEGER);
            ELSEIF operator = '-' THEN
                calculated_date := calculated_date - MAKE_INTERVAL(MONTHS => SUBSTRING(duration_part, '\m[0-9]*\.?[0-9]+\M')::INTEGER);
            END IF;
        ELSIF POSITION('days' IN duration_part) > 0 THEN
            IF operator = '+' THEN
                calculated_date := calculated_date + MAKE_INTERVAL(DAYS => SUBSTRING(duration_part, '\m[0-9]*\.?[0-9]+\M')::INTEGER);
            ELSEIF operator = '-' THEN
                calculated_date := calculated_date - MAKE_INTERVAL(DAYS => SUBSTRING(duration_part, '\m[0-9]*\.?[0-9]+\M')::INTEGER);
            END IF;
        END IF;

    END LOOP;

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

--
UPDATE om_requete SET
    requete = REPLACE(
        requete,
        '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'')',
        'THEN TO_CHAR(&DB_PREFIXEcalculate_date_with_duration(dossier.date_limite_incompletude::date, REPLACE(REPLACE(REPLACE(dossier.delai_incompletude, ''jours'', ''days''), ''mois'', ''months''), ''jour'', ''days''), ''-''), ''DD/MM/YYYY'')'
    )
WHERE code = 'instruction';

--
-- END / [#10459] Permettre la soustraction de délai d'une date dans la requête des champs de fusion
--
