SET ROLE :adm_ka_owner;
BEGIN;

INSERT INTO db_history (beschreibung, zeitpunkt) 
	VALUES ('08_news.sql', current_timestamp);

CREATE TABLE ka_nachrichten_art_tbl (
    id         SERIAL NOT NULL,
    beschreibung VARCHAR(128),
    CONSTRAINT ka_nachrichten_art_tbl_pkey PRIMARY KEY (id)
);

INSERT INTO ka_nachrichten_art_tbl (id, beschreibung) VALUES ( -2, 'Sonstiges');
INSERT INTO ka_nachrichten_art_tbl (id, beschreibung) VALUES ( -1, 'Keine Angabe');
INSERT INTO ka_nachrichten_art_tbl (id, beschreibung) VALUES (  0, 'Systemnachricht');

CREATE TABLE ka_nachrichten_tbl (
    id         SERIAL NOT NULL,
    datum      TIMESTAMP,
    art        int4 REFERENCES ka_nachrichten_art_tbl(id) ON DELETE SET DEFAULT,
    titel      VARCHAR(128),
    nachricht  text,
    CONSTRAINT ka_nachrichten_tbl_pkey PRIMARY KEY (id)
);

CREATE TABLE nm_benutzer_nachrichten_tbl 
(
	id serial NOT NULL,
	benutzer_id int4 REFERENCES ka_benutzer_tbl(id) ON DELETE CASCADE,
	nachrichten_id int4 REFERENCES ka_nachrichten_tbl(id) ON DELETE CASCADE,
	gelesen timestamp DEFAULT now(),
	CONSTRAINT nm_benutzer_news_pkey PRIMARY KEY (id)
);

CREATE OR REPLACE FUNCTION markNewsAsRead( user_id integer, news_id integer) RETURNS VOID 
    LANGUAGE 'plpgsql' SECURITY DEFINER 
    AS $$
BEGIN
    INSERT INTO nm_benutzer_nachrichten_tbl (benutzer_id, nachrichten_id) VALUES (user_id, news_id);
END;
$$;

REVOKE ALL ON FUNCTION markNewsAsRead(user_id integer, news_id integer) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION markNewsAsRead(user_id integer, news_id integer) TO GROUP :cm_group;
GRANT EXECUTE ON FUNCTION markNewsAsRead(user_id integer, news_id integer) TO GROUP :admin_group;

--
-- Views auf die Nachrichten
--

CREATE OR REPLACE VIEW ka_nachrichten_art_tbl_view 
    AS SELECT
        a.id           AS id,
        a.beschreibung AS beschreibung
    FROM ka_nachrichten_art_tbl a;

CREATE OR REPLACE RULE ka_nachrichten_art_update_rule 
    AS ON UPDATE TO ka_nachrichten_art_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_nachrichten_art_delete_rule 
    AS ON DELETE TO ka_nachrichten_art_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_nachrichten_art_insert_rule
    AS ON INSERT TO ka_nachrichten_art_tbl_view DO INSTEAD NOTHING;

GRANT SELECT ON ka_nachrichten_art_tbl_view TO GROUP :cm_group;
GRANT SELECT ON ka_nachrichten_art_tbl_view TO GROUP :admin_group;

CREATE OR REPLACE VIEW ka_nachrichten_tbl_view 
    AS SELECT
        n.id           AS id,
        n.datum        AS datum,
        n.art          AS art,
        a.beschreibung AS art_beschreibung,
        n.titel        AS titel,
        n.nachricht    AS nachricht
    FROM ka_nachrichten_tbl n JOIN ka_nachrichten_art_tbl a
    ON n.art = a.id;

CREATE OR REPLACE RULE ka_nachrichten_update_rule
    AS ON UPDATE TO ka_nachrichten_tbl_view 
	DO INSTEAD
        UPDATE ka_nachrichten_tbl SET
            art        = new.art,
            titel      = new.titel,
            nachricht  = new.nachricht
	    WHERE ka_nachrichten_tbl.id = new.id;

CREATE OR REPLACE RULE ka_nachrichten_delete_rule 
    AS ON DELETE TO  ka_nachrichten_tbl_view DO INSTEAD NOTHING;

CREATE OR REPLACE RULE ka_nachrichten_insert_rule
    AS ON INSERT TO ka_nachrichten_tbl_view DO INSTEAD NOTHING;

GRANT SELECT ON ka_nachrichten_tbl_view TO GROUP :cm_group;
GRANT SELECT ON ka_nachrichten_tbl_view TO GROUP :admin_group;
GRANT UPDATE ON ka_nachrichten_tbl_view TO GROUP :admin_group;

CREATE OR REPLACE  VIEW nm_benutzer_nachrichten_tbl_view AS
    SELECT id, benutzer_id, nachrichten_id, gelesen FROM nm_benutzer_nachrichten_tbl; 

GRANT SELECT ON nm_benutzer_nachrichten_tbl_view TO :cm_group;
GRANT SELECT ON nm_benutzer_nachrichten_tbl_view TO :admin_group;

COMMIT;
