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

--
-- BEGIN / [#10265] - Gestion des enjeux
--
CREATE TABLE IF NOT EXISTS enjeux (
    enjeux INTEGER,
    libelle CHARACTER VARYING(255) NOT NULL,
    couleur CHARACTER VARYING(6),
    priorite INTEGER,
    CONSTRAINT enjeux_pkey PRIMARY KEY (enjeux)
);

COMMENT ON COLUMN enjeux.enjeux IS 'Identifiant unique';
COMMENT ON COLUMN enjeux.libelle IS 'Libellé de l''enjeu';
COMMENT ON COLUMN enjeux.couleur IS 'Couleur de l''enjeu';
COMMENT ON COLUMN enjeux.couleur IS 'Ordre de priorité où 1 = priorité max, 0 ou moins = pas de priorité. (Ex : p1 > p20 > p99 > p0)';

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

CREATE TABLE IF NOT EXISTS lien_enjeux_dossier_instruction_type(
    lien_enjeux_dossier_instruction_type INTEGER,
    enjeux INTEGER NOT NULL,
    dossier_instruction_type INTEGER NOT NULL,
    CONSTRAINT lien_enjeux_dossier_instruction_type_pkey PRIMARY KEY (lien_enjeux_dossier_instruction_type),
    CONSTRAINT lien_enjeux_dossier_instruction_type_enjeux_fk
        FOREIGN KEY (enjeux) REFERENCES enjeux(enjeux),
    CONSTRAINT lien_enjeux_dossier_instruction_type_dit_fk
        FOREIGN KEY (dossier_instruction_type) REFERENCES dossier_instruction_type(dossier_instruction_type)
);

COMMENT ON COLUMN lien_enjeux_dossier_instruction_type.lien_enjeux_dossier_instruction_type IS 'Identifiant unique';
COMMENT ON COLUMN lien_enjeux_dossier_instruction_type.enjeux IS 'Identifiant de l''enjeu';
COMMENT ON COLUMN lien_enjeux_dossier_instruction_type.dossier_instruction_type IS 'Identifiant du type de dossier d''instruction';

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

CREATE TABLE IF NOT EXISTS lien_enjeux_om_collectivite(
    lien_enjeux_om_collectivite INTEGER,
    enjeux INTEGER NOT NULL,
    om_collectivite INTEGER NOT NULL,
    CONSTRAINT lien_enjeux_om_collectivite_pkey PRIMARY KEY (lien_enjeux_om_collectivite),
    CONSTRAINT lien_enjeux_om_collectivite_enjeux_fk
        FOREIGN KEY (enjeux) REFERENCES enjeux(enjeux),
    CONSTRAINT lien_enjeux_om_collectivite_om_collectivite_fk
        FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite)
);

COMMENT ON COLUMN lien_enjeux_om_collectivite.lien_enjeux_om_collectivite IS 'Identifiant unique';
COMMENT ON COLUMN lien_enjeux_om_collectivite.enjeux IS 'Identifiant de l''enjeu ';
COMMENT ON COLUMN lien_enjeux_om_collectivite.om_collectivite IS 'Identifiant de la collectivité';

CREATE SEQUENCE IF NOT EXISTS lien_enjeux_om_collectivite_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'), 'enjeux', (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR TECHNIQUE ET FONCTIONNEL')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'enjeux' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR TECHNIQUE ET FONCTIONNEL')
);

-- Table de liaison entre les dossiers et les enjeux
CREATE TABLE IF NOT EXISTS lien_dossier_enjeux (
    lien_dossier_enjeux INTEGER,
    dossier CHARACTER VARYING(255) NOT NULL,
    enjeux INTEGER NOT NULL,
    CONSTRAINT lien_dossier_enjeux_pkey PRIMARY KEY (lien_dossier_enjeux),
    CONSTRAINT lien_dossier_enjeux_dossier_fk
        FOREIGN KEY(dossier) REFERENCES dossier(dossier),
    CONSTRAINT lien_dossier_enjeux_enjeux_fk
        FOREIGN KEY(enjeux) REFERENCES enjeux(enjeux)
);

COMMENT ON COLUMN lien_dossier_enjeux.lien_dossier_enjeux IS 'Identifiant de la table de liaison entre les dossiers et les enjeux.';
COMMENT ON COLUMN lien_dossier_enjeux.dossier IS 'Identifiant du dossier auquel est rattaché l''enjeu.';
COMMENT ON COLUMN lien_dossier_enjeux.enjeux IS 'Identifiant de l''enjeu associé au dossier.';

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

-- Copie des enjeux urba et erp du dossier sur le nouveau modèle des enjeux
INSERT INTO enjeux (enjeux, libelle, couleur, priorite)
SELECT 1, 'ERP', 'FF00FF', 1
WHERE NOT EXISTS (SELECT enjeux FROM enjeux WHERE enjeux = 1);
INSERT INTO enjeux (enjeux, libelle, couleur, priorite)
SELECT 2,'urbanisme', 'FFD700', 2
WHERE NOT EXISTS (SELECT enjeux FROM enjeux WHERE enjeux = 2);
DO $$
DECLARE
i TEXT;
n INTEGER;
BEGIN
 FOR i IN (SELECT tb.table_name FROM information_schema.tables AS tb WHERE tb.table_type='BASE TABLE' 
 AND tb.table_schema='openads' AND tb.table_name IN ('enjeux')) LOOP
 EXECUTE 'SELECT CASE WHEN (select max('||i||') from '||i||') IS NULL THEN 0 ELSE (select max('||i||') from '||i||') END' INTO n;
 EXECUTE 'ALTER SEQUENCE "' || i || '_seq" RESTART WITH '||n+1||';';
 END LOOP;
END $$;

INSERT INTO lien_enjeux_om_collectivite (lien_enjeux_om_collectivite, enjeux, om_collectivite)
SELECT
    nextval('lien_enjeux_om_collectivite_seq'),
    '1',
    (SELECT om_collectivite FROM om_collectivite WHERE niveau = '2' OR (niveau = '1' AND om_collectivite = 1))
WHERE NOT EXISTS (SELECT lien_enjeux_om_collectivite FROM lien_enjeux_om_collectivite WHERE enjeux = '1' AND om_collectivite = (SELECT om_collectivite FROM om_collectivite WHERE niveau = '2' OR (niveau = '1' AND om_collectivite = 1)));

INSERT INTO lien_enjeux_om_collectivite (lien_enjeux_om_collectivite, enjeux, om_collectivite)
SELECT
    nextval('lien_enjeux_om_collectivite_seq'),
    '2',
    (SELECT om_collectivite FROM om_collectivite WHERE niveau = '2' OR (niveau = '1' AND om_collectivite = 1))
WHERE NOT EXISTS (SELECT lien_enjeux_om_collectivite FROM lien_enjeux_om_collectivite WHERE enjeux = '2' AND om_collectivite = (SELECT om_collectivite FROM om_collectivite WHERE niveau = '2' OR (niveau = '1' AND om_collectivite = 1)));


INSERT INTO lien_enjeux_dossier_instruction_type (lien_enjeux_dossier_instruction_type, enjeux, dossier_instruction_type)
SELECT
    nextval('lien_enjeux_dossier_instruction_type_seq'),
    '1',
    dossier_instruction_type
FROM dossier_instruction_type
WHERE NOT EXISTS (
    SELECT lien_enjeux_dossier_instruction_type FROM lien_enjeux_dossier_instruction_type WHERE enjeux = '1'
    AND dossier_instruction_type = dossier_instruction_type.dossier_instruction_type
);

INSERT INTO lien_enjeux_dossier_instruction_type (lien_enjeux_dossier_instruction_type, enjeux, dossier_instruction_type)
SELECT
    nextval('lien_enjeux_dossier_instruction_type_seq'),
    '2',
    dossier_instruction_type
FROM dossier_instruction_type
WHERE NOT EXISTS (
    SELECT lien_enjeux_dossier_instruction_type FROM lien_enjeux_dossier_instruction_type WHERE enjeux = '2'
    AND dossier_instruction_type = dossier_instruction_type.dossier_instruction_type
);

INSERT INTO lien_dossier_enjeux (lien_dossier_enjeux, dossier, enjeux)
SELECT
    nextval('lien_dossier_enjeux_seq'),
    dossier.dossier,
    '1' AS enjeux
FROM
    dossier
WHERE
    dossier.enjeu_erp IS TRUE
    AND NOT EXISTS (
        SELECT lien_dossier_enjeux FROM lien_dossier_enjeux WHERE enjeux = '1'
        AND dossier = dossier.dossier
    );

INSERT INTO lien_dossier_enjeux (lien_dossier_enjeux, dossier, enjeux)
SELECT
    nextval('lien_dossier_enjeux_seq'),
    dossier.dossier,
    '2' AS enjeux
FROM
    dossier
WHERE
    dossier.enjeu_urba IS TRUE
    AND NOT EXISTS (
        SELECT lien_dossier_enjeux FROM lien_dossier_enjeux WHERE enjeux = '2'
        AND dossier = dossier.dossier
    );

-- Index
CREATE INDEX IF NOT EXISTS lien_enjeux_dit_enjeux_hash ON lien_enjeux_dossier_instruction_type USING HASH (enjeux);
CREATE INDEX IF NOT EXISTS lien_enjeux_dit_dossier_instruction_type_hash ON lien_enjeux_dossier_instruction_type USING HASH (dossier_instruction_type);
CREATE INDEX IF NOT EXISTS lien_enjeux_om_collectivite_enjeux_hash ON lien_enjeux_om_collectivite USING HASH (enjeux);
CREATE INDEX IF NOT EXISTS lien_enjeux_om_collectivite_om_collectivite_hash ON lien_enjeux_om_collectivite USING HASH (om_collectivite);
CREATE INDEX IF NOT EXISTS lien_enjeux_dossier_enjeux_hash ON lien_dossier_enjeux USING HASH (enjeux);
CREATE INDEX IF NOT EXISTS lien_enjeux_dossier_dossier_hash ON lien_dossier_enjeux USING HASH (dossier);
--
-- END / [#10265] - Gestion des enjeux
--

--
-- BEGIN / [#10283] La reprise de rédaction sur une instruction ne devrait plus être possible si le document est signé électroniquement
--

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

--
-- END / [#10283] La reprise de rédaction sur une instruction ne devrait plus être possible si le document est signé électroniquement
--
