CREATE TABLE IF NOT EXISTS kummerkasten_setting (
  id SMALLINT PRIMARY KEY DEFAULT 1 CHECK (id = 1),
  enabled BOOLEAN NOT NULL DEFAULT FALSE,
  access_password_hash TEXT,
  intro_text TEXT NOT NULL DEFAULT 'Reichen Sie hier vertraulich einen Vorschlag oder ein Anliegen für die Tagesordnung ein.',
  target_committee_id BIGINT REFERENCES committee(id) ON DELETE SET NULL,
  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()
);

INSERT INTO kummerkasten_setting(id)
VALUES (1)
ON CONFLICT (id) DO NOTHING;

CREATE TABLE IF NOT EXISTS kummerkasten_submission (
  id BIGSERIAL PRIMARY KEY,
  agenda_item_id BIGINT REFERENCES meeting_agenda_item(id) ON DELETE SET NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  submitter_name VARCHAR(255),
  submitter_contact VARCHAR(255),
  target_committee_id BIGINT REFERENCES committee(id) ON DELETE SET NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'converted_to_agenda',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_kummerkasten_submission_created_at ON kummerkasten_submission(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_kummerkasten_submission_agenda_item ON kummerkasten_submission(agenda_item_id);

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

INSERT INTO app_page(page_key, path, label, nav_group, sort_order, description)
VALUES
  ('kummerkasten-admin', '/kummerkasten-admin', 'Kummerkasten', 'admin', 245, 'Öffentliche Kummerkasten-Seite konfigurieren')
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
  ('kummerkasten.configure', 'Kummerkasten konfigurieren', 'Kummerkasten', 'Öffentliche Kummerkasten-Seite einschalten und Kennwort verwalten')
ON CONFLICT (permission_key) DO UPDATE
  SET label = EXCLUDED.label,
      category = EXCLUDED.category,
      description = EXCLUDED.description;

INSERT INTO app_role_page(role_profile_id, page_key, can_view)
SELECT id, 'kummerkasten-admin', TRUE
  FROM app_role_profile
 WHERE code = 'admin'
ON CONFLICT (role_profile_id, page_key) DO UPDATE SET can_view = TRUE;

INSERT INTO app_role_permission(role_profile_id, permission_key, granted)
SELECT id, 'kummerkasten.configure', TRUE
  FROM app_role_profile
 WHERE code = 'admin'
ON CONFLICT (role_profile_id, permission_key) DO UPDATE SET granted = TRUE;

ALTER TABLE kummerkasten_setting ENABLE ROW LEVEL SECURITY;
ALTER TABLE kummerkasten_submission ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS kummerkasten_setting_select ON kummerkasten_setting;
DROP POLICY IF EXISTS kummerkasten_setting_modify ON kummerkasten_setting;
CREATE POLICY kummerkasten_setting_select ON kummerkasten_setting FOR SELECT USING (app_has_permission('kummerkasten.configure'));
CREATE POLICY kummerkasten_setting_modify ON kummerkasten_setting FOR ALL USING (app_has_permission('kummerkasten.configure')) WITH CHECK (app_has_permission('kummerkasten.configure'));

DROP POLICY IF EXISTS kummerkasten_submission_select ON kummerkasten_submission;
DROP POLICY IF EXISTS kummerkasten_submission_modify ON kummerkasten_submission;
CREATE POLICY kummerkasten_submission_select ON kummerkasten_submission FOR SELECT USING (app_has_permission('kummerkasten.configure'));
CREATE POLICY kummerkasten_submission_modify ON kummerkasten_submission FOR ALL USING (app_has_permission('kummerkasten.configure')) WITH CHECK (app_has_permission('kummerkasten.configure'));