-- PensioenPlus.nl — Supabase tabellen
-- Plak dit in Supabase Dashboard → SQL Editor → Run

CREATE TABLE IF NOT EXISTS pp_users (
  id          BIGINT PRIMARY KEY,
  fn          TEXT NOT NULL,
  ln          TEXT,
  email       TEXT,
  stad        TEXT,
  ervaring    TEXT,
  datum       TEXT,
  badges      JSONB DEFAULT '[]',
  status      TEXT DEFAULT 'actief',
  quiz_profiel TEXT,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS pp_forum_threads (
  id        BIGINT PRIMARY KEY,
  titel     TEXT NOT NULL,
  auteur    TEXT,
  datum     TEXT,
  categorie TEXT,
  replies   INTEGER DEFAULT 0,
  gepind    BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS pp_quiz_results (
  id       BIGINT PRIMARY KEY,
  datum    TEXT,
  profiel  TEXT,
  score    INTEGER,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS pp_events (
  id             BIGINT PRIMARY KEY,
  dag            TEXT,
  mon            TEXT,
  datum          TEXT,
  tijd           TEXT,
  titel          TEXT NOT NULL,
  spreker        TEXT,
  spots          INTEGER DEFAULT 100,
  registrations  JSONB DEFAULT '[]',
  created_at     TIMESTAMPTZ DEFAULT NOW()
);

-- Row Level Security (iedereen mag lezen en anoniem invoegen)
ALTER TABLE pp_users         ENABLE ROW LEVEL SECURITY;
ALTER TABLE pp_forum_threads ENABLE ROW LEVEL SECURITY;
ALTER TABLE pp_quiz_results  ENABLE ROW LEVEL SECURITY;
ALTER TABLE pp_events        ENABLE ROW LEVEL SECURITY;

CREATE POLICY "public_read"   ON pp_users         FOR SELECT USING (true);
CREATE POLICY "anon_insert"   ON pp_users         FOR INSERT WITH CHECK (true);
CREATE POLICY "anon_update"   ON pp_users         FOR UPDATE USING (true);
CREATE POLICY "anon_delete"   ON pp_users         FOR DELETE USING (true);
CREATE POLICY "public_read"   ON pp_forum_threads FOR SELECT USING (true);
CREATE POLICY "anon_insert"   ON pp_forum_threads FOR INSERT WITH CHECK (true);
CREATE POLICY "anon_delete"   ON pp_forum_threads FOR DELETE USING (true);
CREATE POLICY "anon_insert_q" ON pp_quiz_results  FOR INSERT WITH CHECK (true);
CREATE POLICY "public_read_q" ON pp_quiz_results  FOR SELECT USING (true);
CREATE POLICY "public_read_e" ON pp_events        FOR SELECT USING (true);
CREATE POLICY "anon_insert_e" ON pp_events        FOR INSERT WITH CHECK (true);
CREATE POLICY "anon_delete_e" ON pp_events        FOR DELETE USING (true);
CREATE POLICY "anon_update_e" ON pp_events        FOR UPDATE USING (true);

-- NPS data (one row, upserted)
CREATE TABLE IF NOT EXISTS pp_nps_data (
  id         INTEGER DEFAULT 1 PRIMARY KEY,
  promoters  INTEGER DEFAULT 0,
  passives   INTEGER DEFAULT 0,
  detractors INTEGER DEFAULT 0,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
ALTER TABLE pp_nps_data ENABLE ROW LEVEL SECURITY;
CREATE POLICY "public_read_nps" ON pp_nps_data FOR SELECT USING (true);
CREATE POLICY "anon_upsert_nps" ON pp_nps_data FOR ALL USING (true);

-- Poll votes
CREATE TABLE IF NOT EXISTS pp_poll_votes (
  id        BIGINT PRIMARY KEY DEFAULT extract(epoch from now())::bigint,
  week_nr   INTEGER,
  year_nr   INTEGER,
  option_idx INTEGER,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
ALTER TABLE pp_poll_votes ENABLE ROW LEVEL SECURITY;
CREATE POLICY "anon_insert_pv" ON pp_poll_votes FOR INSERT WITH CHECK (true);
CREATE POLICY "public_read_pv" ON pp_poll_votes FOR SELECT USING (true);

-- Enable realtime for key tables
ALTER PUBLICATION supabase_realtime ADD TABLE pp_users;
ALTER PUBLICATION supabase_realtime ADD TABLE pp_forum_threads;