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

--
-- BEGIN / [#10672] Notifier par mail le service consultant
--

-- Ajout du champ notification_automatique_tiers_consultant
ALTER TABLE evenement
    ADD COLUMN IF NOT EXISTS notification_automatique_tiers_consultant boolean NOT NULL DEFAULT false;
COMMENT ON COLUMN evenement.notification_automatique_tiers_consultant IS 'Notifier automatiquement le tiers consultant du dossier si notification_tiers est à notification_automatique';

-- Ajout du droit pour l'administrateur technique et fonctionnel
-- permettant la consultation de la notification pour les tests
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'instruction_notification', (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR TECHNIQUE ET FONCTIONNEL')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'instruction_notification' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR TECHNIQUE ET FONCTIONNEL')
);

--
-- END / [#10672] Notifier par mail le service consultant

--
-- BEGIN / [#10673] Ajout de la permission d'export CSV sur le listing des dossiers d'instruction pour le profil de visualisation
--

-- VISUALISATION DA et DI
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT
    nextval('om_droit_seq'),
    'dossier_instruction_exporter',
    (SELECT om_profil FROM om_profil WHERE libelle = 'VISUALISATION DA et DI')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'dossier_instruction_exporter'
    AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'VISUALISATION DA et DI')
);

--
-- END / [#10673] Ajout de la permission d'export CSV sur le listing des dossiers d'instruction pour le profil de visualisation
--

--
-- BEGIN / [#10697] Gestion des opérateurs - Génération de l'arrêté de prescription
--

ALTER TABLE ONLY dossier_operateur
    ADD COLUMN IF NOT EXISTS arrete_prescription integer;
COMMENT ON COLUMN dossier_operateur.arrete_prescription IS 'Identifiant de l''instruction générée par l''action de génération de l''arrêté de prescription';

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

INSERT INTO om_droit (om_droit, libelle, om_profil)
    SELECT nextval('om_droit_seq'), 'dossier_operateur_arrete_prescription', (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR GENERAL')
    WHERE NOT EXISTS (
        SELECT om_droit FROM om_droit WHERE libelle = 'dossier_operateur_arrete_prescription' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR GENERAL')
    );
--
-- END / [#10697] Gestion des opérateurs - Génération de l'arrêté de prescription
--

--
-- BEGIN / [#10696] Augmente le nombre de caractère sur des références au numérotation des dossiers
--

ALTER TABLE ONLY task ALTER COLUMN dossier TYPE character varying(255);
ALTER TABLE ONLY task ALTER COLUMN object_id TYPE character varying(255);

ALTER TABLE ONLY lien_id_interne_uid_externe ALTER COLUMN object_id TYPE character varying(255);
ALTER TABLE ONLY num_dossier ALTER COLUMN ref TYPE character varying(255);

--
-- END / [#10696] Augmente le nombre de caractère sur des références au numérotation des dossiers
--

--
-- BEGIN / [#10701] Ajout du champ qualité pour le signataire
--

-- Ajout des commentaires pour les champs signataire_habilitation et agrement
COMMENT ON COLUMN signataire_arrete.signataire_habilitation IS 'Habilitation du signataire';
COMMENT ON COLUMN signataire_arrete.agrement IS 'Texte d''agrément du signataire';

--
-- END / [#10701] Ajout du champ qualité pour le signataire
--

--
-- BEGIN / [#10700]  Ajout de documents d'instructions importés à un dossier 
--

--
CREATE TABLE IF NOT EXISTS document_instruction_importe (
    document_instruction_importe integer NOT NULL,
    uid character varying(255) NOT NULL,
    dossier character varying(255) NOT NULL,
    nom_fichier character varying(255) NOT NULL,
    description text NULL,
    document_type integer NOT NULL,
    date_creation date NULL,
    date_modification date NULL,
    uid_dossier_final boolean NOT NULL DEFAULT FALSE,
    uid_thumbnail character varying(255) NULL,
    CONSTRAINT document_instruction_importe_pkey PRIMARY KEY (document_instruction_importe)
);
--
COMMENT ON TABLE document_instruction_importe IS 'Document importé rattaché à un dossier d''instruction';
COMMENT ON COLUMN document_instruction_importe.document_instruction_importe  IS 'Identifiant numérique unique';
COMMENT ON COLUMN document_instruction_importe.uid                   IS 'Identifiant unique du fichier, compose aussi son chemin';
COMMENT ON COLUMN document_instruction_importe.dossier               IS 'Dossier d''instruction auquel est rattaché le document';
COMMENT ON COLUMN document_instruction_importe.nom_fichier           IS 'Nom du document';
COMMENT ON COLUMN document_instruction_importe.description           IS 'Commentaire fourni à l''ajout';
COMMENT ON COLUMN document_instruction_importe.document_type         IS 'Identifiant du type du document';
COMMENT ON COLUMN document_instruction_importe.date_creation         IS 'Date de création';
COMMENT ON COLUMN document_instruction_importe.date_modification     IS 'Date de dernière modification';
COMMENT ON COLUMN document_instruction_importe.uid_dossier_final     IS 'Identifie que le document constitue dossier final';
COMMENT ON COLUMN document_instruction_importe.uid_thumbnail         IS 'Identifiant unique de la miniature du fichier';
--
CREATE SEQUENCE IF NOT EXISTS document_instruction_importe_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE document_instruction_importe_seq
  OWNED BY document_instruction_importe.document_instruction_importe;

--
ALTER TABLE ONLY document_instruction_importe
    DROP CONSTRAINT IF EXISTS document_instruction_importe_dossier_fkey;
ALTER TABLE ONLY document_instruction_importe
    ADD CONSTRAINT document_instruction_importe_dossier_fkey FOREIGN KEY (dossier) REFERENCES dossier(dossier);
--
ALTER TABLE ONLY document_instruction_importe
    DROP CONSTRAINT IF EXISTS document_instruction_importe_document_type_fkey;
ALTER TABLE ONLY document_instruction_importe
    ADD CONSTRAINT document_instruction_importe_document_type_fkey FOREIGN KEY (document_type) REFERENCES document_type(document_type);
--
ALTER TABLE ONLY document_instruction_importe
    DROP CONSTRAINT IF EXISTS document_instruction_importe_uid_uniq;
ALTER TABLE ONLY document_instruction_importe
    ADD CONSTRAINT document_instruction_importe_uid_uniq UNIQUE (uid);

--
CREATE INDEX IF NOT EXISTS document_instruction_importe_dossier ON document_instruction_importe USING btree (dossier);
CREATE INDEX IF NOT EXISTS document_instruction_importe_dossier_hash ON document_instruction_importe USING HASH (dossier);

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

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'document_instruction_importe', (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR GENERAL')
WHERE NOT EXISTS (
SELECT om_droit FROM om_droit WHERE libelle = 'document_instruction_importe' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR GENERAL')
);

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'document_instruction_importe',
(SELECT om_profil FROM om_profil WHERE libelle = 'ASSISTANTE')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'document_instruction_importe'
    AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'ASSISTANTE')
);

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'document_instruction_importe',
(SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'document_instruction_importe'
    AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR')
);

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'document_instruction_importe',
(SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR POLYVALENT COMMUNE')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'document_instruction_importe'
    AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR POLYVALENT COMMUNE')
);

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'document_instruction_importe',
(SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR POLYVALENT')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'document_instruction_importe'
    AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR POLYVALENT')
);

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'document_instruction_importe',
(SELECT om_profil FROM om_profil WHERE libelle = 'VISUALISATION DA et DI')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'document_instruction_importe'
    AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'VISUALISATION DA et DI')
);

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'document_instruction_importe',
(SELECT om_profil FROM om_profil WHERE libelle = 'GUICHET ET SUIVI')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'document_instruction_importe'
    AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'GUICHET ET SUIVI')
);

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'document_instruction_importe',
(SELECT om_profil FROM om_profil WHERE libelle = 'GUICHET UNIQUE')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'document_instruction_importe'
    AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'GUICHET UNIQUE')
);


INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'document_instruction_importe',
(SELECT om_profil FROM om_profil WHERE libelle = 'CHEF DE SERVICE CONTENTIEUX')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'document_instruction_importe'
    AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'CHEF DE SERVICE CONTENTIEUX')
);

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'document_instruction_importe',
(SELECT om_profil FROM om_profil WHERE libelle = 'TECHNICIEN')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'document_instruction_importe'
    AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'TECHNICIEN')
);


INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'document_instruction_importe',
(SELECT om_profil FROM om_profil WHERE libelle = 'JURISTE')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'document_instruction_importe'
    AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'JURISTE')
);


INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'document_instruction_importe',
(SELECT om_profil FROM om_profil WHERE libelle = 'DIRECTION INFRACTION')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'document_instruction_importe'
    AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'DIRECTION INFRACTION')
);

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'document_instruction_importe',
(SELECT om_profil FROM om_profil WHERE libelle = 'DIRECTION RECOURS')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'document_instruction_importe'
    AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'DIRECTION RECOURS')
);

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'document_instruction_importe',
(SELECT om_profil FROM om_profil WHERE libelle = 'RESPONSABLE DIVISION INFRACTION')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'document_instruction_importe'
    AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'RESPONSABLE DIVISION INFRACTION')
);

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'),'document_instruction_importe_modifier_bypass',(SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR GENERAL')
WHERE
NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'document_instruction_importe_modifier_bypass' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR GENERAL')
);

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'),'document_instruction_importe_modifier_bypass',(SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR POLYVALENT COMMUNE')
WHERE
NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'document_instruction_importe_modifier_bypass' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR POLYVALENT COMMUNE')
);

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

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'),'document_instruction_importe_supprimer_bypass',(SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR GENERAL')
WHERE
NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'document_instruction_importe_supprimer_bypass' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR GENERAL')
);

INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'),'document_instruction_importe_supprimer_bypass',(SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR POLYVALENT COMMUNE')
WHERE
NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'document_instruction_importe_supprimer_bypass' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'INSTRUCTEUR POLYVALENT COMMUNE')
);

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

--
-- END / [#10700]  Ajout de documents d'instructions importés à un dossier 
--
