-- Run with psql -v ON_ERROR_STOP=1 so a failure stops the migration.
-- Do not pass --single-transaction: CREATE INDEX CONCURRENTLY cannot run inside a
-- transaction block. A failed concurrent build leaves an INVALID index to drop before
-- rerunning. Every statement is idempotent.
--
-- Apply before deploying the application; db.create_all() does not add a column to an
-- existing table. Do not drop anything here while an instance might still run the new code.
--
-- Shared-key activity summaries filter requests by their funding key. Build the lookup index
-- without blocking ordinary generation writes, before deploying the activity endpoint.
CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_waiting_prompts_sharedkey_id
  ON waiting_prompts (sharedkey_id)
  WHERE sharedkey_id IS NOT NULL;

-- No default, so the add is metadata-only and existing rows stay unknown. Provenance is
-- never backfilled from the effective prompt. The lock timeout fails rather than queueing
-- an ACCESS EXCLUSIVE lock behind a long transaction.
SET lock_timeout = '5s';
ALTER TABLE waiting_prompts ADD COLUMN IF NOT EXISTS submitted_prompt TEXT;
RESET lock_timeout;

-- No account or request foreign keys: a rejected submission has no waiting row, and
-- evidence must outlive request expiry and account deletion. user_id is nullable because
-- retention removes identity at the ceiling; the flags record which values retention removed.
CREATE TABLE IF NOT EXISTS prompt_moderation_events (
  id BIGSERIAL PRIMARY KEY,
  created TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT timezone('utc', now()),
  user_id INTEGER,
  request_id VARCHAR(36),
  job_id VARCHAR(36) UNIQUE,
  worker_id VARCHAR(36),
  proxied_account TEXT,
  ipaddr TEXT,
  ip_subject_key VARCHAR(64),
  origin_text VARCHAR(255),
  models JSONB,
  reason VARCHAR(32) NOT NULL,
  outcome VARCHAR(16) NOT NULL,
  submitted_prompt TEXT,
  moderation_prompt TEXT,
  effective_prompt TEXT,
  text_state VARCHAR(8) NOT NULL DEFAULT 'pending',
  text_sha256 VARCHAR(64),
  text_chars INTEGER,
  text_truncated BOOLEAN NOT NULL DEFAULT false,
  text_redacted BOOLEAN NOT NULL DEFAULT false,
  ipaddr_redacted BOOLEAN NOT NULL DEFAULT false,
  anonymized BOOLEAN NOT NULL DEFAULT false,
  CONSTRAINT ck_prompt_moderation_text_state CHECK (text_state IN ('pending', 'stored', 'none'))
);
-- Prompt text moves from the row to object storage. text_state records where it is held; an event created before
-- the column existed defaults to pending, so its text is uploaded. A constant default is metadata-only. The lock
-- timeout bounds the wait for the table lock; the check then validates every existing row while that lock is held,
-- which is quick only while the table is small.
SET lock_timeout = '5s';
ALTER TABLE prompt_moderation_events ADD COLUMN IF NOT EXISTS text_state VARCHAR(8) NOT NULL DEFAULT 'pending'
  CONSTRAINT ck_prompt_moderation_text_state CHECK (text_state IN ('pending', 'stored', 'none'));
ALTER TABLE prompt_moderation_events ADD COLUMN IF NOT EXISTS text_sha256 VARCHAR(64);
ALTER TABLE prompt_moderation_events ADD COLUMN IF NOT EXISTS text_chars INTEGER;
RESET lock_timeout;
-- The upload job reads only events whose text awaits upload.
CREATE INDEX IF NOT EXISTS ix_prompt_moderation_text_state_pending
  ON prompt_moderation_events (id) WHERE text_state = 'pending';
-- One partial index per retention step, so each pass reads only the events that step has
-- not finished rather than rescanning every event older than its window.
CREATE INDEX IF NOT EXISTS ix_prompt_moderation_text_pending
  ON prompt_moderation_events (created, id) WHERE NOT text_redacted;
CREATE INDEX IF NOT EXISTS ix_prompt_moderation_ipaddr_pending
  ON prompt_moderation_events (created, id) WHERE NOT ipaddr_redacted;
CREATE INDEX IF NOT EXISTS ix_prompt_moderation_anonymize_pending
  ON prompt_moderation_events (created, id) WHERE NOT anonymized;
-- Time-bounded browsing (since/until) reads every event, so it keeps a plain creation-time index.
CREATE INDEX IF NOT EXISTS ix_prompt_moderation_created ON prompt_moderation_events (created);
CREATE INDEX IF NOT EXISTS ix_prompt_moderation_user_id ON prompt_moderation_events (user_id, id);
-- Serves a proxied-account filter with or without an account; only service accounts supply one.
CREATE INDEX IF NOT EXISTS ix_prompt_moderation_proxied_account_id
  ON prompt_moderation_events (proxied_account, id) WHERE proxied_account IS NOT NULL;
-- The address filter matches the pseudonym, and the address only on events without one. Every
-- event captured under a usable secret has a pseudonym, so the address index holds the rest alone.
CREATE INDEX IF NOT EXISTS ix_prompt_moderation_ip_subject_key_id
  ON prompt_moderation_events (ip_subject_key, id) WHERE ip_subject_key IS NOT NULL;
CREATE INDEX IF NOT EXISTS ix_prompt_moderation_unkeyed_ipaddr_id
  ON prompt_moderation_events (ipaddr, id) WHERE ip_subject_key IS NULL;
CREATE INDEX IF NOT EXISTS ix_prompt_moderation_worker_id
  ON prompt_moderation_events (worker_id, id) WHERE worker_id IS NOT NULL;

-- Notes are separate so that writing one never edits captured evidence. Retention and account
-- wipes never delete a note.
CREATE TABLE IF NOT EXISTS prompt_moderation_notes (
  id BIGSERIAL PRIMARY KEY,
  event_id BIGINT NOT NULL REFERENCES prompt_moderation_events (id) ON DELETE CASCADE,
  author_id INTEGER NOT NULL,
  note VARCHAR(2000) NOT NULL,
  created TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT timezone('utc', now())
);
CREATE INDEX IF NOT EXISTS ix_prompt_moderation_notes_event_id ON prompt_moderation_notes (event_id, id);

-- Worker report records follow the evidence address window and ceiling: retention nulls the
-- address and origin text, then deletes the row, unless the account is under moderation action.
-- Dropping NOT NULL or a constraint and adding a nullable column without a default are
-- metadata-only; the lock timeout fails rather than queueing behind a long transaction. The
-- partial index lets the address step read only rows that still carry an address or origin
-- text; the ceiling step uses the existing created index.
SET lock_timeout = '5s';
ALTER TABLE user_problem_jobs ALTER COLUMN ipaddr DROP NOT NULL;
ALTER TABLE user_problem_jobs ADD COLUMN IF NOT EXISTS origin_text VARCHAR(255);
-- Worker report records are evidence about the reported account and must outlive the reporting worker.
ALTER TABLE user_problem_jobs DROP CONSTRAINT IF EXISTS user_problem_jobs_worker_id_fkey;
RESET lock_timeout;
CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_user_problem_jobs_ipaddr_pending
  ON user_problem_jobs (created, id)
  WHERE ipaddr IS NOT NULL OR origin_text IS NOT NULL;

-- The moderation promotion queues read users above the trust threshold, highest evaluating
-- kudos first, and stop at their limit. Build without blocking user writes, before deploying
-- the moderation overview.
CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_users_evaluating_kudos_rank
  ON users (evaluating_kudos DESC, id)
  WHERE evaluating_kudos > 0;

-- worker_suspicions remains the resettable current score. No foreign keys here: deleting a
-- worker or resetting its suspicion must not delete the history moderators review.
CREATE TABLE IF NOT EXISTS worker_suspicion_events (
  id BIGSERIAL PRIMARY KEY,
  created TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT timezone('utc', now()),
  worker_id UUID NOT NULL,
  worker_name VARCHAR(100) NOT NULL,
  user_id INTEGER NOT NULL,
  suspicion_id INTEGER NOT NULL,
  detail TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_worker_suspicion_events_created
  ON worker_suspicion_events (created);
CREATE INDEX IF NOT EXISTS ix_worker_suspicion_events_worker_id
  ON worker_suspicion_events (worker_id, id);
CREATE INDEX IF NOT EXISTS ix_worker_suspicion_events_user_id
  ON worker_suspicion_events (user_id, id);
