/* Création d'une table unique et partagée "public.layer_styles" */

CREATE TABLE public.layer_styles
(
  id                SERIAL PRIMARY KEY,
  f_table_catalog   VARCHAR,
  f_table_schema    VARCHAR,
  f_table_name      VARCHAR,
  f_geometry_column VARCHAR,
  stylename         VARCHAR(30),
  styleqml          XML,
  stylesld          XML,
  useasdefault      BOOLEAN,
  description       TEXT,
  owner             VARCHAR(30),
  ui                XML,
  update_time       TIMESTAMP DEFAULT now()
);

/* Création automatique des vues pour chacun des utilisateurs dans leurs dossiers respectifs */

CREATE OR REPLACE
FUNCTION public.refresh_layerstyles_user_view()
  RETURNS VOID LANGUAGE plpgsql AS
$func$
DECLARE
  username TEXT;
BEGIN
  RAISE NOTICE 'Création des vues de layer_styles';
  FOR username IN SELECT u.usename
                  FROM pg_catalog.pg_user u
                  WHERE u.usename LIKE 'lpo%' LOOP
    RAISE NOTICE 'Création de la vue %.layer_styles et des règles inhérentes', username;
    EXECUTE format('DROP VIEW IF EXISTS %I.layer_styles;', username);
    RAISE NOTICE 'Vue supprimée';
    EXECUTE format('CREATE VIEW %I.layer_styles AS SELECT * FROM public.layer_styles;'
    , username);
    RAISE NOTICE 'Vue créée';
    EXECUTE format(
        'DROP RULE IF EXISTS %s_layer_style_ins ON %I.layer_styles; ', username, username);
    RAISE NOTICE 'Règle insert supprimée';
    EXECUTE format(
        'CREATE RULE %s_layer_style_ins AS ON INSERT TO %I.layer_styles ' ||
        ' DO INSTEAD ' ||
        '   INSERT INTO public.layer_styles (' ||
        '       f_table_catalog, f_table_schema, f_table_name, f_geometry_column, stylename, styleqml, ' ||
        '       stylesld, useasdefault, description, owner, ui) ' ||
        '   VALUES (NEW.f_table_catalog,NEW.f_table_schema,NEW.f_table_name,NEW.f_geometry_column,NEW.stylename,' ||
        '       NEW.styleqml,NEW.stylesld,NEW.useasdefault,NEW.description,NEW.owner,NEW.ui);',
        username, username);
    RAISE NOTICE 'Règle insert créée';
    EXECUTE format('DROP RULE IF EXISTS %s_layer_style_upd ON %I.layer_styles;', username, username);
    RAISE NOTICE 'Règle update supprimée';
    EXECUTE format('CREATE RULE %s_layer_style_upd AS ON UPDATE TO %I.layer_styles ' ||
                   'DO INSTEAD ' ||
                   'UPDATE public.layer_styles ' ||
                   'SET ' ||
                   '  useasdefault = NEW.useasdefault,' ||
                   '  styleqml     = NEW.styleqml,' ||
                   '  stylesld     = NEW.stylesld,' ||
                   '  description  = NEW.description,' ||
                   '  owner        = NEW.owner ' ||
                   'WHERE f_table_catalog = OLD.f_table_catalog ' ||
                   '      AND f_table_schema = OLD.f_table_schema ' ||
                   '      AND f_table_name = OLD.f_table_name ' ||
                   '      AND f_geometry_column = OLD.f_geometry_column ' ||
                   '      AND stylename = OLD.stylename;', username, username);
    RAISE NOTICE 'Règle update créée';
    EXECUTE format(
        'DROP RULE IF EXISTS %s_layer_style_del ON %I.layer_styles;', username, username);
    RAISE NOTICE 'Règle delete créée';
    EXECUTE format(
        'CREATE RULE %s_layer_style_del AS ON DELETE TO %I.layer_styles DO INSTEAD DELETE FROM public.layer_styles WHERE id = OLD.id;',
        username, username);
    RAISE NOTICE 'Règle delete créée';
    EXECUTE format('GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE %I.layer_styles TO %s', username, username);
    RAISE NOTICE 'Grant mise à jour';
  END LOOP;

END
$func$;

SELECT refresh_layerstyles_user_view();

-- DROP RULE IF EXISTS lpo07fcl_layer_style_ins
-- ON lpo07_fcl.layer_styles;
-- CREATE RULE lpo07fcl_layer_style_ins AS ON INSERT TO lpo07_fcl.layer_styles
-- DO INSTEAD
--   INSERT INTO public.layer_styles (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, stylename, styleqml, stylesld, useasdefault, description, owner, ui)
--   VALUES (
--     NEW.f_table_catalog,
--     NEW.f_table_schema,
--     NEW.f_table_name,
--     NEW.f_geometry_column,
--     NEW.stylename,
--     NEW.styleqml,
--     NEW.stylesld,
--     NEW.useasdefault,
--     NEW.description,
--     NEW.owner,
--     NEW.ui
--   );
--
-- DROP RULE IF EXISTS lpo07fcl_layer_style_upd
-- ON lpo07_fcl.layer_styles;
-- CREATE RULE lpo07fcl_layer_style_upd AS ON UPDATE TO lpo07_fcl.layer_styles
-- DO INSTEAD
--   UPDATE public.layer_styles
--   SET
--     useasdefault = NEW.useasdefault,
--     styleqml     = NEW.styleqml,
--     stylesld     = NEW.stylesld,
--     description  = NEW.description,
--     owner        = NEW.owner
--   WHERE f_table_catalog = OLD.f_table_catalog
--         AND f_table_schema = OLD.f_table_schema
--         AND f_table_name = OLD.f_table_name
--         AND f_geometry_column = OLD.f_geometry_column
--         AND stylename = OLD.stylename;
--
-- DROP RULE IF EXISTS lpo07fcl_layer_style_del
-- ON lpo07_fcl.layer_styles;
-- CREATE RULE lpo07fcl_layer_style_del AS ON DELETE TO lpo07_fcl.layer_styles
-- DO INSTEAD
--   DELETE FROM public.layer_styles
--   WHERE id = OLD.id;


SELECT *
FROM lpo07_fcl.layer_styles;


/*
MESSAGE D'ERREUR DE QGIS:
Impossible d'enregistrer le style de la couche. Il n'est pas
possible de créer la table de destination dans la base.
C'est peut-être dû à un manque de permission de l'utilisateur ().
Veuillez contacter l'administrateur de la base de données.
*/