--
-- BEGIN / #1119 — Analyse PeC et Avis de consulation
--

-- Nouvelle table de référence *avis_decision_type*
CREATE TABLE IF NOT EXISTS avis_decision_type (
    avis_decision_type integer NOT NULL,
    code character varying(10) NULL,
    libelle character varying(255) NOT NULL,
    description text NULL,
    om_validite_debut date NULL,
    om_validite_fin date NULL,
    CONSTRAINT avis_decision_type_pkey PRIMARY KEY (avis_decision_type)
);
CREATE SEQUENCE IF NOT EXISTS avis_decision_type_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
COMMENT ON TABLE avis_decision_type IS 'Type d''avis décision';
COMMENT ON COLUMN avis_decision_type.avis_decision_type IS 'Identifiant technique du type d''avis décision';
COMMENT ON COLUMN avis_decision_type.code IS 'Code du type d''avis décision';
COMMENT ON COLUMN avis_decision_type.libelle IS 'Libellé du type d''avis décision';
COMMENT ON COLUMN avis_decision_type.description IS 'Description du type d''avis décision';
COMMENT ON COLUMN avis_decision_type.om_validite_debut IS 'Date de validité (début) du type d''avis décision';
COMMENT ON COLUMN avis_decision_type.om_validite_fin IS 'Date de validité (fin) du type d''avis décision';

--
ALTER TABLE avis_decision ADD IF NOT EXISTS avis_decision_type integer NULL;
ALTER TABLE ONLY avis_decision
    DROP CONSTRAINT IF EXISTS avis_decision_avis_decision_type;
ALTER TABLE ONLY avis_decision
    ADD CONSTRAINT avis_decision_avis_decision_type FOREIGN KEY (avis_decision_type) REFERENCES avis_decision_type(avis_decision_type);
COMMENT ON COLUMN avis_decision.avis_decision_type IS 'Type de l''avis de décision';

--
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'avis_decision_type', (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR TECHNIQUE ET FONCTIONNEL')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'avis_decision_type' 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'), 'avis_decision_type', (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR GENERAL')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'avis_decision_type' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR GENERAL')
);

-- Nouvelle table de référence *avis_decision_type*
CREATE TABLE IF NOT EXISTS avis_decision_nature (
    avis_decision_nature integer NOT NULL,
    code character varying(10) NULL,
    libelle character varying(255) NOT NULL,
    description text NULL,
    om_validite_debut date NULL,
    om_validite_fin date NULL,
    CONSTRAINT avis_decision_nature_pkey PRIMARY KEY (avis_decision_nature)
);
CREATE SEQUENCE IF NOT EXISTS avis_decision_nature_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
COMMENT ON TABLE avis_decision_nature IS 'Nature d''avis décision';
COMMENT ON COLUMN avis_decision_nature.avis_decision_nature IS 'Identifiant technique de la nature d''avis décision';
COMMENT ON COLUMN avis_decision_nature.code IS 'Code de la nature d''avis décision';
COMMENT ON COLUMN avis_decision_nature.libelle IS 'Libellé de la nature d''avis décision';
COMMENT ON COLUMN avis_decision_nature.description IS 'Description de la nature d''avis décision';
COMMENT ON COLUMN avis_decision_nature.om_validite_debut IS 'Date de validité (début) de la nature d''avis décision';
COMMENT ON COLUMN avis_decision_nature.om_validite_fin IS 'Date de validité (fin) de la nature d''avis décision';

--
ALTER TABLE avis_decision ADD IF NOT EXISTS avis_decision_nature integer NULL;
ALTER TABLE ONLY avis_decision
    DROP CONSTRAINT IF EXISTS avis_decision_avis_decision_nature;
ALTER TABLE ONLY avis_decision
    ADD CONSTRAINT avis_decision_avis_decision_nature FOREIGN KEY (avis_decision_nature) REFERENCES avis_decision_nature(avis_decision_nature);
COMMENT ON COLUMN avis_decision.avis_decision_nature IS 'Nature de l''avis de décision';

--
INSERT INTO om_droit (om_droit, libelle, om_profil)
SELECT nextval('om_droit_seq'), 'avis_decision_nature', (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR TECHNIQUE ET FONCTIONNEL')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'avis_decision_nature' 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'), 'avis_decision_nature', (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR GENERAL')
WHERE NOT EXISTS (
    SELECT om_droit FROM om_droit WHERE libelle = 'avis_decision_nature' AND om_profil = (SELECT om_profil FROM om_profil WHERE libelle = 'ADMINISTRATEUR GENERAL')
);

--
-- END / #1119 — Analyse PeC et Avis de consulation
--
