BEGIN;
CREATE TABLE IF NOT EXISTS judge_ticket (
  id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "siteId" integer NOT NULL REFERENCES site(id) ON DELETE CASCADE,
  "judgeId" integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  subject varchar(200) NOT NULL,
  body text NOT NULL,
  status varchar(16) NOT NULL DEFAULT 'open',
  reply text NULL,
  "repliedById" integer NULL REFERENCES "user"(id) ON DELETE SET NULL,
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT chk_judge_ticket_status CHECK (status IN ('open','replied','closed'))
);
CREATE INDEX IF NOT EXISTS idx_judge_ticket_site_status ON judge_ticket("siteId", status, id DESC);
CREATE INDEX IF NOT EXISTS idx_judge_ticket_judge ON judge_ticket("judgeId", id DESC);
COMMIT;