CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_case_updated_at') THEN
    CREATE TRIGGER trg_case_updated_at BEFORE UPDATE ON br_case FOR EACH ROW EXECUTE FUNCTION set_updated_at();
    CREATE TRIGGER trg_deadline_updated_at BEFORE UPDATE ON deadline FOR EACH ROW EXECUTE FUNCTION set_updated_at();
    CREATE TRIGGER trg_meeting_updated_at BEFORE UPDATE ON meeting FOR EACH ROW EXECUTE FUNCTION set_updated_at();
    CREATE TRIGGER trg_decision_updated_at BEFORE UPDATE ON decision FOR EACH ROW EXECUTE FUNCTION set_updated_at();
    CREATE TRIGGER trg_task_updated_at BEFORE UPDATE ON task FOR EACH ROW EXECUTE FUNCTION set_updated_at();
    CREATE TRIGGER trg_document_updated_at BEFORE UPDATE ON document_item FOR EACH ROW EXECUTE FUNCTION set_updated_at();
    CREATE TRIGGER trg_template_updated_at BEFORE UPDATE ON template_item FOR EACH ROW EXECUTE FUNCTION set_updated_at();
  END IF;
END $$;

CREATE OR REPLACE FUNCTION audit_if_changed() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log(actor_user_id, action, entity_type, entity_id, metadata)
  VALUES (
    NULL,
    TG_OP,
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    jsonb_build_object('old', to_jsonb(OLD), 'new', to_jsonb(NEW))
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_audit_case') THEN
    CREATE TRIGGER trg_audit_case AFTER INSERT OR UPDATE OR DELETE ON br_case FOR EACH ROW EXECUTE FUNCTION audit_if_changed();
    CREATE TRIGGER trg_audit_deadline AFTER INSERT OR UPDATE OR DELETE ON deadline FOR EACH ROW EXECUTE FUNCTION audit_if_changed();
    CREATE TRIGGER trg_audit_meeting AFTER INSERT OR UPDATE OR DELETE ON meeting FOR EACH ROW EXECUTE FUNCTION audit_if_changed();
    CREATE TRIGGER trg_audit_decision AFTER INSERT OR UPDATE OR DELETE ON decision FOR EACH ROW EXECUTE FUNCTION audit_if_changed();
    CREATE TRIGGER trg_audit_task AFTER INSERT OR UPDATE OR DELETE ON task FOR EACH ROW EXECUTE FUNCTION audit_if_changed();
    CREATE TRIGGER trg_audit_document AFTER INSERT OR UPDATE OR DELETE ON document_item FOR EACH ROW EXECUTE FUNCTION audit_if_changed();
  END IF;
END $$;

CREATE OR REPLACE FUNCTION app_user_id() RETURNS BIGINT AS $$
  SELECT COALESCE(NULLIF(current_setting('app.user_id', true), ''), '0')::BIGINT;
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE FUNCTION app_role() RETURNS TEXT AS $$
  SELECT COALESCE(NULLIF(current_setting('app.user_role', true), ''), 'anonymous');
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE FUNCTION app_committee_id() RETURNS BIGINT AS $$
  SELECT COALESCE(NULLIF(current_setting('app.committee_id', true), ''), '0')::BIGINT;
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE FUNCTION can_read_conf_level(level conf_level, owner BIGINT, committee BIGINT) RETURNS BOOLEAN AS $$
BEGIN
  IF app_role() = 'admin' THEN RETURN TRUE; END IF;
  IF level = 'intern' THEN RETURN TRUE; END IF;
  IF level = 'vertraulich' THEN
    RETURN owner = app_user_id() OR committee = app_committee_id() OR app_role() IN ('chair','secretary');
  END IF;
  RETURN owner = app_user_id() OR app_role() IN ('chair');
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE VIEW v_case_masked AS
SELECT
  id,
  case_number,
  title,
  case_type,
  status,
  priority,
  confidentiality_level,
  CASE
    WHEN confidentiality_level = 'streng_vertraulich' AND app_role() NOT IN ('admin','chair') AND owner_user_id <> app_user_id()
      THEN '***'
    ELSE COALESCE(affected_employee_name, '')
  END AS affected_employee_name_masked,
  department,
  committee_id,
  owner_user_id,
  created_at
FROM br_case
WHERE deleted_at IS NULL;