BEGIN;
SET ROLE work_role;
CREATE TABLE IF NOT EXISTS work.long_running_work (
  investigation_id TEXT PRIMARY KEY, objective TEXT NOT NULL,
  hypothesis_refs TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[], attempt_refs TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  finding_refs TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[], blockers TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  checkpoint_refs TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[], next_step TEXT NOT NULL, state TEXT NOT NULL,
  revision BIGINT NOT NULL, time_budget_millis BIGINT NOT NULL, cost_budget_micros BIGINT NOT NULL,
  max_attempts INTEGER NOT NULL, recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (investigation_id <> ''), CHECK (objective <> ''), CHECK (next_step <> ''), CHECK (revision > 0), CHECK (time_budget_millis >= 0), CHECK (cost_budget_micros >= 0), CHECK (max_attempts >= 0), CHECK (recorded_at_unix_millis > 0)
);
CREATE TABLE IF NOT EXISTS work.investigation_checkpoints (
  checkpoint_id TEXT PRIMARY KEY, investigation_id TEXT NOT NULL, completed_attempt_refs TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  completed_finding_refs TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[], next_step TEXT NOT NULL, recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (checkpoint_id <> ''), CHECK (investigation_id <> ''), CHECK (next_step <> ''), CHECK (recorded_at_unix_millis > 0)
);
CREATE TABLE IF NOT EXISTS work.investigation_attempts (
  investigation_id TEXT NOT NULL, attempt_ref TEXT NOT NULL, verified_at_unix_millis BIGINT NOT NULL,
  PRIMARY KEY (investigation_id, attempt_ref), CHECK (investigation_id <> ''), CHECK (attempt_ref <> ''), CHECK (verified_at_unix_millis > 0)
);
RESET ROLE;
COMMIT;