CREATE TABLE IF NOT EXISTS app_role_profile (
  id BIGSERIAL PRIMARY KEY,
  code VARCHAR(80) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  description TEXT,
  system_role role_type UNIQUE,
  is_system BOOLEAN NOT NULL DEFAULT FALSE,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_by BIGINT REFERENCES app_user(id),
  updated_by BIGINT REFERENCES app_user(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS app_permission (
  permission_key VARCHAR(120) PRIMARY KEY,
  label VARCHAR(160) NOT NULL,
  category VARCHAR(80) NOT NULL,
  description TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS app_page (
  page_key VARCHAR(80) PRIMARY KEY,
  path VARCHAR(120) NOT NULL UNIQUE,
  label VARCHAR(120) NOT NULL,
  nav_group VARCHAR(80) NOT NULL DEFAULT 'work',
  sort_order INTEGER NOT NULL DEFAULT 100,
  description TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS app_role_permission (
  role_profile_id BIGINT NOT NULL REFERENCES app_role_profile(id) ON DELETE CASCADE,
  permission_key VARCHAR(120) NOT NULL REFERENCES app_permission(permission_key) ON DELETE CASCADE,
  granted BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (role_profile_id, permission_key)
);

CREATE TABLE IF NOT EXISTS app_role_page (
  role_profile_id BIGINT NOT NULL REFERENCES app_role_profile(id) ON DELETE CASCADE,
  page_key VARCHAR(80) NOT NULL REFERENCES app_page(page_key) ON DELETE CASCADE,
  can_view BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (role_profile_id, page_key)
);

CREATE TABLE IF NOT EXISTS app_user_role_profile (
  user_id BIGINT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
  role_profile_id BIGINT NOT NULL REFERENCES app_role_profile(id) ON DELETE CASCADE,
  assigned_by BIGINT REFERENCES app_user(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (user_id, role_profile_id)
);

CREATE TABLE IF NOT EXISTS app_team_role (
  id BIGSERIAL PRIMARY KEY,
  code VARCHAR(80) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  description TEXT,
  can_view_cases BOOLEAN NOT NULL DEFAULT FALSE,
  can_write_cases BOOLEAN NOT NULL DEFAULT FALSE,
  can_view_documents BOOLEAN NOT NULL DEFAULT FALSE,
  can_write_documents BOOLEAN NOT NULL DEFAULT FALSE,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_by BIGINT REFERENCES app_user(id),
  updated_by BIGINT REFERENCES app_user(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS app_user_team_role (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
  team_role_id BIGINT NOT NULL REFERENCES app_team_role(id) ON DELETE CASCADE,
  committee_id BIGINT REFERENCES committee(id) ON DELETE CASCADE,
  assigned_by BIGINT REFERENCES app_user(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS ux_app_user_team_role_global
  ON app_user_team_role(user_id, team_role_id)
  WHERE committee_id IS NULL;

CREATE UNIQUE INDEX IF NOT EXISTS ux_app_user_team_role_committee
  ON app_user_team_role(user_id, team_role_id, committee_id)
  WHERE committee_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS br_case_team_access (
  case_id BIGINT NOT NULL REFERENCES br_case(id) ON DELETE CASCADE,
  team_role_id BIGINT NOT NULL REFERENCES app_team_role(id) ON DELETE CASCADE,
  can_read BOOLEAN NOT NULL DEFAULT TRUE,
  can_write BOOLEAN NOT NULL DEFAULT FALSE,
  created_by BIGINT REFERENCES app_user(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (case_id, team_role_id)
);

CREATE TABLE IF NOT EXISTS document_team_access (
  document_id BIGINT NOT NULL REFERENCES document_item(id) ON DELETE CASCADE,
  team_role_id BIGINT NOT NULL REFERENCES app_team_role(id) ON DELETE CASCADE,
  can_read BOOLEAN NOT NULL DEFAULT TRUE,
  can_write BOOLEAN NOT NULL DEFAULT FALSE,
  created_by BIGINT REFERENCES app_user(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (document_id, team_role_id)
);

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_app_role_profile_updated_at') THEN
    CREATE TRIGGER trg_app_role_profile_updated_at BEFORE UPDATE ON app_role_profile FOR EACH ROW EXECUTE FUNCTION set_updated_at();
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_app_team_role_updated_at') THEN
    CREATE TRIGGER trg_app_team_role_updated_at BEFORE UPDATE ON app_team_role FOR EACH ROW EXECUTE FUNCTION set_updated_at();
  END IF;
END $$;

INSERT INTO app_role_profile(code, name, description, system_role, is_system)
VALUES
  ('admin', 'admin', 'Systemverwaltung', 'admin', TRUE),
  ('chair', 'chair', 'Sitzungen, TOPs, Beschlüsse, Vorlagen, Ausschussverwaltung', 'chair', TRUE),
  ('secretary', 'secretary', 'Protokolle, Sitzungen, TOPs, Dokumente', 'secretary', TRUE),
  ('member', 'member', 'Eigene Vorgänge, Aufgaben, Kommentare, Kalender', 'member', TRUE),
  ('advisor', 'advisor', 'Lesender/beratender Zugriff auf freigegebene Inhalte', 'advisor', TRUE)
ON CONFLICT (code) DO UPDATE
  SET name = EXCLUDED.name,
      description = EXCLUDED.description,
      system_role = EXCLUDED.system_role,
      is_system = TRUE,
      is_active = TRUE,
      updated_at = NOW();

INSERT INTO app_page(page_key, path, label, nav_group, sort_order, description)
VALUES
  ('dashboard', '/', 'Dashboard', 'dashboard', 0, 'Startseite und Überblick'),
  ('cases', '/cases', 'Vorgänge', 'work', 10, 'Vorgangsverwaltung'),
  ('deadlines', '/deadlines', 'Fristen', 'work', 20, 'Fristenverwaltung'),
  ('meetings', '/meetings', 'Sitzungen', 'work', 30, 'Sitzungen planen, starten und protokollieren'),
  ('agenda-items', '/agenda-items', 'TOP-Bestand', 'work', 40, 'Tagesordnungspunkte'),
  ('decisions', '/decisions', 'Beschlüsse', 'work', 50, 'Beschlussverwaltung'),
  ('reports', '/reports', 'Reports', 'work', 60, 'Berichte und Auswertungen'),
  ('tasks', '/tasks', 'Aufgaben', 'work', 70, 'Aufgabenverwaltung'),
  ('calendar', '/calendar', 'Kalender', 'work', 80, 'Kalender'),
  ('documents', '/documents', 'Dokumente', 'work', 90, 'Dokumentenverwaltung'),
  ('comments', '/comments', 'Kommentare', 'work', 100, 'Kommentare'),
  ('notifications', '/notifications', 'Benachrichtigungen', 'work', 110, 'Hinweise'),
  ('users', '/users', 'Benutzer', 'admin', 200, 'Benutzerverwaltung'),
  ('committees', '/committees', 'Ausschüsse', 'admin', 210, 'Ausschussverwaltung'),
  ('templates', '/templates', 'Vorlagen / Formular-Editor', 'admin', 220, 'Vorlagenverwaltung und HTML-/Formular-Editor'),
  ('permissions', '/permissions', 'Rollen & Rechte', 'admin', 230, 'Rechtematrix und Teamrollen'),
  ('security', '/security', 'Sicherheit', 'admin', 240, 'MFA und Sicherheit'),
  ('audit', '/audit', 'Audit', 'admin', 250, 'Audit-Log')
ON CONFLICT (page_key) DO UPDATE
  SET path = EXCLUDED.path,
      label = EXCLUDED.label,
      nav_group = EXCLUDED.nav_group,
      sort_order = EXCLUDED.sort_order,
      description = EXCLUDED.description;

INSERT INTO app_permission(permission_key, label, category, description)
VALUES
  ('system.admin', 'Systemverwaltung', 'System', 'Voller Systemzugriff'),
  ('system.users', 'Benutzer verwalten', 'System', 'Benutzer anlegen, ändern und deaktivieren'),
  ('system.permissions', 'Rollen und Rechte verwalten', 'System', 'Rechtematrix, Custom-Rollen und Teamrollen verwalten'),
  ('system.security', 'Sicherheit verwalten', 'System', 'Sicherheits- und MFA-Funktionen verwalten'),
  ('system.audit', 'Audit lesen', 'System', 'Audit-Log anzeigen'),
  ('cases.read.own', 'Eigene Vorgänge lesen', 'Vorgänge', 'Eigene/erstellte Vorgänge lesen'),
  ('cases.read.all', 'Alle Vorgänge lesen', 'Vorgänge', 'Alle Vorgänge rollenbasiert lesen'),
  ('cases.write', 'Vorgänge bearbeiten', 'Vorgänge', 'Vorgänge anlegen und bearbeiten'),
  ('cases.write.all', 'Alle Vorgänge bearbeiten', 'Vorgänge', 'Nicht nur eigene Vorgänge bearbeiten'),
  ('cases.delete', 'Vorgänge löschen', 'Vorgänge', 'Vorgänge löschen/archivieren'),
  ('deadlines.write', 'Fristen bearbeiten', 'Fristen', 'Fristen anlegen und bearbeiten'),
  ('meetings.write', 'Sitzungen bearbeiten', 'Sitzungen', 'Sitzungen planen, starten, stoppen und löschen'),
  ('agenda.write', 'TOPs bearbeiten', 'Sitzungen', 'Tagesordnungspunkte bearbeiten'),
  ('decisions.write', 'Beschlüsse bearbeiten', 'Beschlüsse', 'Beschlüsse anlegen und ändern'),
  ('reports.read', 'Reports lesen', 'Reports', 'Berichte anzeigen'),
  ('tasks.write', 'Aufgaben bearbeiten', 'Aufgaben', 'Aufgaben anlegen und ändern'),
  ('calendar.write', 'Kalender bearbeiten', 'Kalender', 'Kalendereinträge anlegen und ändern'),
  ('documents.read.released', 'Freigegebene Dokumente lesen', 'Dokumente', 'Freigegebene/saubere interne Dokumente lesen'),
  ('documents.read.all', 'Alle Dokumente lesen', 'Dokumente', 'Alle Dokumente rollenbasiert lesen'),
  ('documents.write', 'Dokumente bearbeiten', 'Dokumente', 'Dokumente hochladen, versionieren und ändern'),
  ('documents.write.all', 'Alle Dokumente bearbeiten', 'Dokumente', 'Nicht nur eigene Dokumente bearbeiten'),
  ('comments.write', 'Kommentare schreiben', 'Kommentare', 'Kommentare anlegen und bearbeiten'),
  ('notifications.write', 'Benachrichtigungen bearbeiten', 'Benachrichtigungen', 'Benachrichtigungen anlegen/ändern'),
  ('templates.write', 'Vorlagen bearbeiten', 'Vorlagen', 'Vorlagen anlegen und ändern'),
  ('committees.write', 'Ausschüsse bearbeiten', 'Administration', 'Ausschüsse und Mitgliedschaften verwalten')
ON CONFLICT (permission_key) DO UPDATE
  SET label = EXCLUDED.label,
      category = EXCLUDED.category,
      description = EXCLUDED.description;

CREATE OR REPLACE FUNCTION grant_role_pages(role_code TEXT, page_keys TEXT[]) RETURNS VOID AS $$
DECLARE
  role_id BIGINT;
BEGIN
  SELECT id INTO role_id FROM app_role_profile WHERE code = role_code;
  IF role_id IS NULL THEN RETURN; END IF;

  DELETE FROM app_role_page WHERE role_profile_id = role_id;

  INSERT INTO app_role_page(role_profile_id, page_key, can_view)
  SELECT role_id, unnest(page_keys), TRUE
  ON CONFLICT (role_profile_id, page_key) DO UPDATE SET can_view = TRUE;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION grant_role_permissions(role_code TEXT, permission_keys TEXT[]) RETURNS VOID AS $$
DECLARE
  role_id BIGINT;
BEGIN
  SELECT id INTO role_id FROM app_role_profile WHERE code = role_code;
  IF role_id IS NULL THEN RETURN; END IF;

  DELETE FROM app_role_permission WHERE role_profile_id = role_id;

  INSERT INTO app_role_permission(role_profile_id, permission_key, granted)
  SELECT role_id, unnest(permission_keys), TRUE
  ON CONFLICT (role_profile_id, permission_key) DO UPDATE SET granted = TRUE;
END;
$$ LANGUAGE plpgsql;

SELECT grant_role_pages('admin', ARRAY[
  'dashboard','cases','deadlines','meetings','agenda-items','decisions','reports','tasks','calendar','documents','comments','notifications',
  'users','committees','templates','permissions','security','audit'
]);
SELECT grant_role_pages('chair', ARRAY[
  'dashboard','cases','deadlines','meetings','agenda-items','decisions','reports','tasks','calendar','documents','comments','notifications','committees','templates'
]);
SELECT grant_role_pages('secretary', ARRAY[
  'dashboard','meetings','agenda-items','decisions','reports','tasks','calendar','documents','comments','notifications'
]);
SELECT grant_role_pages('member', ARRAY[
  'dashboard','cases','deadlines','tasks','calendar','documents','comments','notifications'
]);
SELECT grant_role_pages('advisor', ARRAY[
  'dashboard','calendar','documents','comments','notifications'
]);

SELECT grant_role_permissions('admin', ARRAY(
  SELECT permission_key FROM app_permission
));
SELECT grant_role_permissions('chair', ARRAY[
  'cases.read.all','cases.write','cases.write.all','cases.delete','deadlines.write','meetings.write','agenda.write','decisions.write',
  'reports.read','tasks.write','calendar.write','documents.read.all','documents.write','documents.write.all','comments.write','notifications.write','templates.write','committees.write'
]);
SELECT grant_role_permissions('secretary', ARRAY[
  'meetings.write','agenda.write','decisions.write','reports.read','tasks.write','calendar.write','documents.write','comments.write','notifications.write'
]);
SELECT grant_role_permissions('member', ARRAY[
  'cases.read.own','cases.write','deadlines.write','tasks.write','calendar.write','documents.write','comments.write','notifications.write'
]);
SELECT grant_role_permissions('advisor', ARRAY[
  'documents.read.released'
]);

DROP FUNCTION IF EXISTS grant_role_pages(TEXT, TEXT[]);
DROP FUNCTION IF EXISTS grant_role_permissions(TEXT, TEXT[]);

CREATE OR REPLACE FUNCTION app_has_permission(permission TEXT, target_user_id BIGINT DEFAULT app_user_id(), target_role TEXT DEFAULT app_role()) RETURNS BOOLEAN AS $$
DECLARE
  allowed BOOLEAN;
BEGIN
  IF target_role = 'admin' THEN
    RETURN TRUE;
  END IF;

  SELECT EXISTS (
    SELECT 1
      FROM app_role_profile rp
      JOIN app_role_permission rperm ON rperm.role_profile_id = rp.id AND rperm.granted = TRUE
     WHERE rperm.permission_key = permission
       AND rp.is_active = TRUE
       AND (
         rp.system_role::TEXT = target_role
         OR EXISTS (
           SELECT 1
             FROM app_user_role_profile urp
            WHERE urp.role_profile_id = rp.id
              AND urp.user_id = target_user_id
         )
       )
  ) INTO allowed;

  RETURN COALESCE(allowed, FALSE);
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public;

CREATE OR REPLACE FUNCTION app_can_view_page(page_key TEXT, target_user_id BIGINT DEFAULT app_user_id(), target_role TEXT DEFAULT app_role()) RETURNS BOOLEAN AS $$
DECLARE
  allowed BOOLEAN;
BEGIN
  IF target_role = 'admin' THEN
    RETURN TRUE;
  END IF;

  SELECT EXISTS (
    SELECT 1
      FROM app_role_profile rp
      JOIN app_role_page rpage ON rpage.role_profile_id = rp.id AND rpage.can_view = TRUE
     WHERE rpage.page_key = app_can_view_page.page_key
       AND rp.is_active = TRUE
       AND (
         rp.system_role::TEXT = target_role
         OR EXISTS (
           SELECT 1
             FROM app_user_role_profile urp
            WHERE urp.role_profile_id = rp.id
              AND urp.user_id = target_user_id
         )
       )
  ) INTO allowed;

  RETURN COALESCE(allowed, FALSE);
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public;

CREATE OR REPLACE FUNCTION app_user_has_case_team_access(target_case_id BIGINT, write_access BOOLEAN DEFAULT FALSE, target_user_id BIGINT DEFAULT app_user_id()) RETURNS BOOLEAN AS $$
DECLARE
  allowed BOOLEAN;
BEGIN
  SELECT EXISTS (
    SELECT 1
      FROM br_case c
      JOIN app_user_team_role utr ON utr.user_id = target_user_id AND (utr.committee_id IS NULL OR utr.committee_id = c.committee_id)
      JOIN app_team_role tr ON tr.id = utr.team_role_id AND tr.is_active = TRUE
      LEFT JOIN br_case_team_access cta ON cta.case_id = c.id AND cta.team_role_id = tr.id
     WHERE c.id = target_case_id
       AND (
         (write_access = FALSE AND (tr.can_view_cases = TRUE OR cta.can_read = TRUE OR cta.can_write = TRUE))
         OR
         (write_access = TRUE AND (tr.can_write_cases = TRUE OR cta.can_write = TRUE))
       )
  ) INTO allowed;

  RETURN COALESCE(allowed, FALSE);
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public;

CREATE OR REPLACE FUNCTION app_user_has_document_team_access(target_document_id BIGINT, write_access BOOLEAN DEFAULT FALSE, target_user_id BIGINT DEFAULT app_user_id()) RETURNS BOOLEAN AS $$
DECLARE
  allowed BOOLEAN;
BEGIN
  SELECT EXISTS (
    SELECT 1
      FROM document_item d
      LEFT JOIN br_case c ON c.id = d.case_id
      JOIN app_user_team_role utr ON utr.user_id = target_user_id AND (utr.committee_id IS NULL OR utr.committee_id = COALESCE(c.committee_id, 0) OR c.id IS NULL)
      JOIN app_team_role tr ON tr.id = utr.team_role_id AND tr.is_active = TRUE
      LEFT JOIN document_team_access dta ON dta.document_id = d.id AND dta.team_role_id = tr.id
     WHERE d.id = target_document_id
       AND (
         (write_access = FALSE AND (tr.can_view_documents = TRUE OR dta.can_read = TRUE OR dta.can_write = TRUE))
         OR
         (write_access = TRUE AND (tr.can_write_documents = TRUE OR dta.can_write = TRUE))
       )
  ) INTO allowed;

  RETURN COALESCE(allowed, FALSE);
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public;

CREATE OR REPLACE FUNCTION app_can_read_case(target_case_id BIGINT) RETURNS BOOLEAN AS $$
DECLARE
  row br_case%ROWTYPE;
BEGIN
  SELECT * INTO row FROM br_case WHERE id = target_case_id AND deleted_at IS NULL;
  IF NOT FOUND THEN RETURN FALSE; END IF;

  IF app_role() IN ('admin', 'chair') OR app_has_permission('cases.read.all') THEN RETURN TRUE; END IF;
  IF row.owner_user_id = app_user_id() OR row.created_by = app_user_id() THEN RETURN TRUE; END IF;
  IF app_user_has_case_team_access(row.id, FALSE) THEN RETURN TRUE; END IF;

  RETURN FALSE;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public;

CREATE OR REPLACE FUNCTION app_can_write_case(target_case_id BIGINT) RETURNS BOOLEAN AS $$
DECLARE
  row br_case%ROWTYPE;
BEGIN
  SELECT * INTO row FROM br_case WHERE id = target_case_id AND deleted_at IS NULL;
  IF NOT FOUND THEN RETURN FALSE; END IF;

  IF app_role() IN ('admin', 'chair') OR app_has_permission('cases.write.all') THEN RETURN TRUE; END IF;
  IF (row.owner_user_id = app_user_id() OR row.created_by = app_user_id()) AND app_has_permission('cases.write') THEN RETURN TRUE; END IF;
  IF app_user_has_case_team_access(row.id, TRUE) AND app_has_permission('cases.write') THEN RETURN TRUE; END IF;

  RETURN FALSE;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public;

CREATE OR REPLACE FUNCTION app_can_read_document(target_document_id BIGINT) RETURNS BOOLEAN AS $$
DECLARE
  row document_item%ROWTYPE;
BEGIN
  SELECT * INTO row FROM document_item WHERE id = target_document_id AND deleted_at IS NULL;
  IF NOT FOUND THEN RETURN FALSE; END IF;
  IF row.scan_status <> 'clean' THEN RETURN FALSE; END IF;

  IF app_role() IN ('admin', 'chair') OR app_has_permission('documents.read.all') THEN RETURN TRUE; END IF;
  IF row.uploaded_by = app_user_id() THEN RETURN TRUE; END IF;
  IF row.case_id IS NOT NULL AND app_can_read_case(row.case_id) THEN RETURN TRUE; END IF;
  IF app_user_has_document_team_access(row.id, FALSE) THEN RETURN TRUE; END IF;
  IF row.case_id IS NULL AND row.decision_id IS NULL AND row.confidentiality_level = 'intern' AND app_has_permission('documents.read.released') THEN RETURN TRUE; END IF;

  RETURN FALSE;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public;

CREATE OR REPLACE FUNCTION app_can_write_document(target_document_id BIGINT) RETURNS BOOLEAN AS $$
DECLARE
  row document_item%ROWTYPE;
BEGIN
  SELECT * INTO row FROM document_item WHERE id = target_document_id AND deleted_at IS NULL;
  IF NOT FOUND THEN RETURN FALSE; END IF;

  IF app_role() IN ('admin', 'chair') OR app_has_permission('documents.write.all') THEN RETURN TRUE; END IF;
  IF row.uploaded_by = app_user_id() AND app_has_permission('documents.write') THEN RETURN TRUE; END IF;
  IF row.case_id IS NOT NULL AND app_can_write_case(row.case_id) AND app_has_permission('documents.write') THEN RETURN TRUE; END IF;
  IF app_user_has_document_team_access(row.id, TRUE) AND app_has_permission('documents.write') THEN RETURN TRUE; END IF;

  RETURN FALSE;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public;

CREATE OR REPLACE FUNCTION can_read_conf_level(level conf_level, owner BIGINT, committee BIGINT) RETURNS BOOLEAN AS $$
BEGIN
  IF app_role() IN ('admin', 'chair') OR app_has_permission('cases.read.all') THEN RETURN TRUE; END IF;
  IF owner = app_user_id() THEN RETURN TRUE; END IF;
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql STABLE;

DROP POLICY IF EXISTS br_case_select_policy ON br_case;
DROP POLICY IF EXISTS br_case_modify_policy ON br_case;
CREATE POLICY br_case_select_policy ON br_case FOR SELECT USING (app_can_read_case(id));
CREATE POLICY br_case_modify_policy ON br_case FOR ALL USING (app_can_write_case(id)) WITH CHECK (
  app_role() IN ('admin','chair')
  OR app_has_permission('cases.write.all')
  OR (app_has_permission('cases.write') AND (owner_user_id = app_user_id() OR created_by = app_user_id()))
);

DROP POLICY IF EXISTS deadline_select_policy ON deadline;
DROP POLICY IF EXISTS deadline_modify_policy ON deadline;
CREATE POLICY deadline_select_policy ON deadline FOR SELECT USING (
  deleted_at IS NULL AND EXISTS (SELECT 1 FROM br_case c WHERE c.id = deadline.case_id AND app_can_read_case(c.id))
);
CREATE POLICY deadline_modify_policy ON deadline FOR ALL USING (
  deleted_at IS NULL AND EXISTS (SELECT 1 FROM br_case c WHERE c.id = deadline.case_id AND app_can_write_case(c.id))
) WITH CHECK (app_has_permission('deadlines.write'));

DROP POLICY IF EXISTS decision_select_policy ON decision;
DROP POLICY IF EXISTS decision_modify_policy ON decision;
CREATE POLICY decision_select_policy ON decision FOR SELECT USING (
  case_id IS NULL OR app_can_read_case(case_id) OR app_has_permission('decisions.write')
);
CREATE POLICY decision_modify_policy ON decision FOR ALL USING (
  app_has_permission('decisions.write') OR responsible_user_id = app_user_id()
) WITH CHECK (app_has_permission('decisions.write') OR responsible_user_id = app_user_id());

DROP POLICY IF EXISTS document_select_policy ON document_item;
DROP POLICY IF EXISTS document_modify_policy ON document_item;
CREATE POLICY document_select_policy ON document_item FOR SELECT USING (app_can_read_document(id));
CREATE POLICY document_modify_policy ON document_item FOR ALL USING (app_can_write_document(id)) WITH CHECK (
  app_role() IN ('admin','chair')
  OR app_has_permission('documents.write.all')
  OR (app_has_permission('documents.write') AND uploaded_by = app_user_id())
  OR (case_id IS NOT NULL AND app_can_write_case(case_id) AND app_has_permission('documents.write'))
);

DROP POLICY IF EXISTS meeting_select_policy ON meeting;
DROP POLICY IF EXISTS meeting_modify_policy ON meeting;
CREATE POLICY meeting_select_policy ON meeting FOR SELECT USING (
  app_can_view_page('meetings') OR app_has_permission('meetings.write') OR committee_id IS NULL OR committee_id = app_committee_id()
);
CREATE POLICY meeting_modify_policy ON meeting FOR ALL USING (app_has_permission('meetings.write')) WITH CHECK (app_has_permission('meetings.write'));

DROP POLICY IF EXISTS template_select_policy ON template_item;
DROP POLICY IF EXISTS template_modify_policy ON template_item;
CREATE POLICY template_select_policy ON template_item FOR SELECT USING (app_can_view_page('templates'));
CREATE POLICY template_modify_policy ON template_item FOR ALL USING (app_has_permission('templates.write')) WITH CHECK (app_has_permission('templates.write'));

DROP POLICY IF EXISTS committee_modify ON committee;
CREATE POLICY committee_modify ON committee FOR ALL USING (app_has_permission('committees.write')) WITH CHECK (app_has_permission('committees.write'));

ALTER TABLE app_role_profile ENABLE ROW LEVEL SECURITY;
ALTER TABLE app_permission ENABLE ROW LEVEL SECURITY;
ALTER TABLE app_page ENABLE ROW LEVEL SECURITY;
ALTER TABLE app_role_permission ENABLE ROW LEVEL SECURITY;
ALTER TABLE app_role_page ENABLE ROW LEVEL SECURITY;
ALTER TABLE app_user_role_profile ENABLE ROW LEVEL SECURITY;
ALTER TABLE app_team_role ENABLE ROW LEVEL SECURITY;
ALTER TABLE app_user_team_role ENABLE ROW LEVEL SECURITY;
ALTER TABLE br_case_team_access ENABLE ROW LEVEL SECURITY;
ALTER TABLE document_team_access ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS app_role_profile_select ON app_role_profile;
DROP POLICY IF EXISTS app_role_profile_modify ON app_role_profile;
CREATE POLICY app_role_profile_select ON app_role_profile FOR SELECT USING (
  app_has_permission('system.permissions')
  OR system_role::TEXT = app_role()
  OR EXISTS (
    SELECT 1 FROM app_user_role_profile urp
     WHERE urp.role_profile_id = app_role_profile.id
       AND urp.user_id = app_user_id()
  )
);
CREATE POLICY app_role_profile_modify ON app_role_profile FOR ALL USING (app_has_permission('system.permissions')) WITH CHECK (app_has_permission('system.permissions'));

DROP POLICY IF EXISTS app_permission_select ON app_permission;
DROP POLICY IF EXISTS app_permission_modify ON app_permission;
CREATE POLICY app_permission_select ON app_permission FOR SELECT USING (TRUE);
CREATE POLICY app_permission_modify ON app_permission FOR ALL USING (app_has_permission('system.permissions')) WITH CHECK (app_has_permission('system.permissions'));

DROP POLICY IF EXISTS app_page_select ON app_page;
DROP POLICY IF EXISTS app_page_modify ON app_page;
CREATE POLICY app_page_select ON app_page FOR SELECT USING (TRUE);
CREATE POLICY app_page_modify ON app_page FOR ALL USING (app_has_permission('system.permissions')) WITH CHECK (app_has_permission('system.permissions'));

DROP POLICY IF EXISTS app_role_permission_select ON app_role_permission;
DROP POLICY IF EXISTS app_role_permission_modify ON app_role_permission;
CREATE POLICY app_role_permission_select ON app_role_permission FOR SELECT USING (
  app_has_permission('system.permissions')
  OR EXISTS (
    SELECT 1 FROM app_role_profile rp
     WHERE rp.id = app_role_permission.role_profile_id
       AND (
         rp.system_role::TEXT = app_role()
         OR EXISTS (
           SELECT 1 FROM app_user_role_profile urp
            WHERE urp.role_profile_id = rp.id
              AND urp.user_id = app_user_id()
         )
       )
  )
);
CREATE POLICY app_role_permission_modify ON app_role_permission FOR ALL USING (app_has_permission('system.permissions')) WITH CHECK (app_has_permission('system.permissions'));

DROP POLICY IF EXISTS app_role_page_select ON app_role_page;
DROP POLICY IF EXISTS app_role_page_modify ON app_role_page;
CREATE POLICY app_role_page_select ON app_role_page FOR SELECT USING (
  app_has_permission('system.permissions')
  OR EXISTS (
    SELECT 1 FROM app_role_profile rp
     WHERE rp.id = app_role_page.role_profile_id
       AND (
         rp.system_role::TEXT = app_role()
         OR EXISTS (
           SELECT 1 FROM app_user_role_profile urp
            WHERE urp.role_profile_id = rp.id
              AND urp.user_id = app_user_id()
         )
       )
  )
);
CREATE POLICY app_role_page_modify ON app_role_page FOR ALL USING (app_has_permission('system.permissions')) WITH CHECK (app_has_permission('system.permissions'));

DROP POLICY IF EXISTS app_user_role_profile_select ON app_user_role_profile;
DROP POLICY IF EXISTS app_user_role_profile_modify ON app_user_role_profile;
CREATE POLICY app_user_role_profile_select ON app_user_role_profile FOR SELECT USING (app_has_permission('system.permissions') OR user_id = app_user_id());
CREATE POLICY app_user_role_profile_modify ON app_user_role_profile FOR ALL USING (app_has_permission('system.permissions')) WITH CHECK (app_has_permission('system.permissions'));

DROP POLICY IF EXISTS app_team_role_select ON app_team_role;
DROP POLICY IF EXISTS app_team_role_modify ON app_team_role;
CREATE POLICY app_team_role_select ON app_team_role FOR SELECT USING (app_has_permission('system.permissions') OR TRUE);
CREATE POLICY app_team_role_modify ON app_team_role FOR ALL USING (app_has_permission('system.permissions')) WITH CHECK (app_has_permission('system.permissions'));

DROP POLICY IF EXISTS app_user_team_role_select ON app_user_team_role;
DROP POLICY IF EXISTS app_user_team_role_modify ON app_user_team_role;
CREATE POLICY app_user_team_role_select ON app_user_team_role FOR SELECT USING (app_has_permission('system.permissions') OR user_id = app_user_id());
CREATE POLICY app_user_team_role_modify ON app_user_team_role FOR ALL USING (app_has_permission('system.permissions')) WITH CHECK (app_has_permission('system.permissions'));

DROP POLICY IF EXISTS br_case_team_access_select ON br_case_team_access;
DROP POLICY IF EXISTS br_case_team_access_modify ON br_case_team_access;
CREATE POLICY br_case_team_access_select ON br_case_team_access FOR SELECT USING (app_has_permission('system.permissions') OR app_can_write_case(case_id));
CREATE POLICY br_case_team_access_modify ON br_case_team_access FOR ALL USING (app_can_write_case(case_id) OR app_has_permission('system.permissions')) WITH CHECK (app_can_write_case(case_id) OR app_has_permission('system.permissions'));

DROP POLICY IF EXISTS document_team_access_select ON document_team_access;
DROP POLICY IF EXISTS document_team_access_modify ON document_team_access;
CREATE POLICY document_team_access_select ON document_team_access FOR SELECT USING (app_has_permission('system.permissions') OR app_can_write_document(document_id));
CREATE POLICY document_team_access_modify ON document_team_access FOR ALL USING (app_can_write_document(document_id) OR app_has_permission('system.permissions')) WITH CHECK (app_can_write_document(document_id) OR app_has_permission('system.permissions'));

GRANT SELECT, INSERT, UPDATE, DELETE ON app_role_profile, app_permission, app_page, app_role_permission, app_role_page, app_user_role_profile, app_team_role, app_user_team_role, br_case_team_access, document_team_access TO br_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO br_app;
