ALTER TABLE br_case ENABLE ROW LEVEL SECURITY;
ALTER TABLE deadline ENABLE ROW LEVEL SECURITY;
ALTER TABLE task ENABLE ROW LEVEL SECURITY;
ALTER TABLE document_item ENABLE ROW LEVEL SECURITY;
ALTER TABLE comment_item ENABLE ROW LEVEL SECURITY;
ALTER TABLE notification_item ENABLE ROW LEVEL SECURITY;
ALTER TABLE app_user ENABLE ROW LEVEL SECURITY;
ALTER TABLE meeting ENABLE ROW LEVEL SECURITY;
ALTER TABLE decision ENABLE ROW LEVEL SECURITY;
ALTER TABLE template_item ENABLE ROW LEVEL SECURITY;
ALTER TABLE committee ENABLE ROW LEVEL SECURITY;

ALTER TABLE br_case FORCE ROW LEVEL SECURITY;
ALTER TABLE deadline FORCE ROW LEVEL SECURITY;
ALTER TABLE task FORCE ROW LEVEL SECURITY;
ALTER TABLE document_item FORCE ROW LEVEL SECURITY;
ALTER TABLE comment_item FORCE ROW LEVEL SECURITY;
ALTER TABLE notification_item FORCE ROW LEVEL SECURITY;
ALTER TABLE app_user FORCE ROW LEVEL SECURITY;
ALTER TABLE meeting FORCE ROW LEVEL SECURITY;
ALTER TABLE decision FORCE ROW LEVEL SECURITY;
ALTER TABLE template_item FORCE ROW LEVEL SECURITY;
ALTER TABLE committee FORCE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS committee_select ON committee;
DROP POLICY IF EXISTS committee_modify ON committee;
CREATE POLICY committee_select ON committee FOR SELECT USING (TRUE);
CREATE POLICY committee_modify ON committee FOR ALL USING (app_role() IN ('admin','chair')) WITH CHECK (app_role() IN ('admin','chair'));

DROP POLICY IF EXISTS user_select ON app_user;
DROP POLICY IF EXISTS user_modify ON app_user;
CREATE POLICY user_select ON app_user FOR SELECT USING (
  app_role() IN ('admin','chair') OR id = app_user_id()
);
CREATE POLICY user_modify ON app_user FOR ALL USING (
  app_role() = 'admin' OR id = app_user_id()
) WITH CHECK (
  app_role() = 'admin' OR id = app_user_id()
);

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 (
  deleted_at IS NULL AND can_read_conf_level(confidentiality_level, owner_user_id, committee_id)
);
CREATE POLICY br_case_modify_policy ON br_case FOR ALL USING (
  deleted_at IS NULL AND (
    app_role() = 'admin'
    OR owner_user_id = app_user_id()
    OR (committee_id = app_committee_id() AND app_role() IN ('chair', 'secretary'))
  )
) WITH CHECK (can_read_conf_level(confidentiality_level, owner_user_id, committee_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 can_read_conf_level(c.confidentiality_level, c.owner_user_id, c.committee_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_role() = 'admin'
      OR c.owner_user_id = app_user_id()
      OR (c.committee_id = app_committee_id() AND app_role() IN ('chair', 'secretary'))
    )
  )
) WITH CHECK (TRUE);

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 (
  committee_id IS NULL OR committee_id = app_committee_id() OR app_role() IN ('admin','chair','secretary')
);
CREATE POLICY meeting_modify_policy ON meeting FOR ALL USING (
  app_role() IN ('admin','chair','secretary')
) WITH CHECK (app_role() IN ('admin','chair','secretary'));

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 EXISTS (
    SELECT 1 FROM br_case c WHERE c.id = decision.case_id
    AND can_read_conf_level(c.confidentiality_level, c.owner_user_id, c.committee_id)
  ) OR app_role() IN ('admin','chair','secretary')
);
CREATE POLICY decision_modify_policy ON decision FOR ALL USING (
  app_role() IN ('admin','chair','secretary') OR responsible_user_id = app_user_id()
) WITH CHECK (TRUE);

DROP POLICY IF EXISTS task_select_policy ON task;
DROP POLICY IF EXISTS task_modify_policy ON task;
CREATE POLICY task_select_policy ON task FOR SELECT USING (
  deleted_at IS NULL AND (
    assigned_user_id = app_user_id()
    OR backup_user_id = app_user_id()
    OR app_role() IN ('admin','chair','secretary')
  )
);
CREATE POLICY task_modify_policy ON task FOR ALL USING (
  deleted_at IS NULL AND (
    app_role() IN ('admin','chair','secretary')
    OR assigned_user_id = app_user_id()
    OR created_by = app_user_id()
  )
) WITH CHECK (TRUE);

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 (
  deleted_at IS NULL
  AND scan_status = 'clean'
  AND (
    app_role() = 'admin'
    OR uploaded_by = app_user_id()
    OR EXISTS (
      SELECT 1 FROM br_case c
      WHERE c.id = document_item.case_id
      AND can_read_conf_level(document_item.confidentiality_level, c.owner_user_id, c.committee_id)
    )
    OR case_id IS NULL
  )
);
CREATE POLICY document_modify_policy ON document_item FOR ALL USING (
  deleted_at IS NULL AND (
    app_role() IN ('admin','chair','secretary')
    OR uploaded_by = app_user_id()
  )
) WITH CHECK (TRUE);

DROP POLICY IF EXISTS comment_select_policy ON comment_item;
DROP POLICY IF EXISTS comment_modify_policy ON comment_item;
CREATE POLICY comment_select_policy ON comment_item FOR SELECT USING (
  deleted_at IS NULL AND (
    visibility = 'intern'
    OR (visibility = 'vertraulich' AND app_role() IN ('admin','chair','secretary'))
  )
);
CREATE POLICY comment_modify_policy ON comment_item FOR ALL USING (
  deleted_at IS NULL AND (
    author_user_id = app_user_id()
    OR app_role() IN ('admin','chair','secretary')
  )
) WITH CHECK (TRUE);

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 (TRUE);
CREATE POLICY template_modify_policy ON template_item FOR ALL USING (app_role() IN ('admin','chair','secretary')) WITH CHECK (app_role() IN ('admin','chair','secretary'));

DROP POLICY IF EXISTS notification_select_policy ON notification_item;
DROP POLICY IF EXISTS notification_modify_policy ON notification_item;
CREATE POLICY notification_select_policy ON notification_item FOR SELECT USING (
  user_id = app_user_id() OR app_role() IN ('admin','chair','secretary')
);
CREATE POLICY notification_modify_policy ON notification_item FOR ALL USING (
  user_id = app_user_id() OR app_role() IN ('admin','chair','secretary')
) WITH CHECK (TRUE);