002_privileges.sql

-- MAIL

INSERT INTO UNICAEN_PRIVILEGE_CATEGORIE (
        CODE,
        LIBELLE,
        NAMESPACE,
        ORDRE)
    values
    ('mail', 'UnicaenMail - Gestion des mails', 'UnicaenMail\Provider\Privilege', 1051)
    ON CONFLICT (CODE) DO
    UPDATE SET
        LIBELLE=excluded.LIBELLE,
        NAMESPACE=excluded.NAMESPACE,
        ORDRE=excluded.ORDRE;

WITH d(code, lib, ordre) AS (
    SELECT 'mail_index', 'Affichage de l''index', 1 UNION
    SELECT 'mail_afficher', 'Afficher un mail', 2 UNION
    SELECT 'mail_reenvoi', 'Ré-envoi d''un mail', 30 UNION
    SELECT 'mail_supprimer', 'Suppression d''un mail', 4 UNION
    SELECT 'mail_test', 'Envoi d''un mail de test', 6 UNION
    SELECT 'mail_afficher_config', 'Afficher la configuration des mails', 5
)
INSERT INTO unicaen_privilege_privilege(CATEGORIE_ID, CODE, LIBELLE, ORDRE)
    SELECT cp.id, d.code, d.lib, d.ordre
    FROM d
    JOIN unicaen_privilege_categorie cp ON cp.CODE = 'mail'
    ON CONFLICT (CATEGORIE_ID, CODE) DO
    UPDATE SET
        LIBELLE=excluded.LIBELLE,
        ORDRE=excluded.ORDRE;

insert into unicaen_privilege_privilege_role_linker
    (role_id, privilege_id)
    (select role.id, privilege.id from unicaen_utilisateur_role role,
        unicaen_privilege_privilege privilege
    join unicaen_privilege_categorie cp on privilege.categorie_id = cp.id
    where role.role_id = 'Admin_tech'
    and cp.code in('mail'))
    on conflict do nothing;