BEGIN;

SET ROLE situation_role;

CREATE TABLE IF NOT EXISTS situation.context_scopes (
  context_scope_id TEXT PRIMARY KEY,
  principal_id TEXT NOT NULL,
  revision BIGINT NOT NULL CHECK (revision > 0),
  bindings JSONB NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL CHECK (recorded_at_unix_millis > 0)
);

CREATE TABLE IF NOT EXISTS situation.focus_frames (
  focus_frame_id TEXT PRIMARY KEY,
  principal_id TEXT NOT NULL,
  actor_id TEXT NOT NULL,
  revision BIGINT NOT NULL CHECK (revision > 0),
  authority_scope_ref TEXT NOT NULL,
  context_scope_ref JSONB NOT NULL,
  goal_ref TEXT,
  task_ref TEXT,
  source_utterance_ref TEXT,
  provenance TEXT NOT NULL,
  confidence_millis BIGINT NOT NULL CHECK (confidence_millis >= 0),
  state TEXT NOT NULL,
  parent_frame_ref TEXT,
  activated_at_unix_millis BIGINT NOT NULL CHECK (activated_at_unix_millis > 0),
  suspended_at_unix_millis BIGINT,
  expired_at_unix_millis BIGINT,
  recorded_at_unix_millis BIGINT NOT NULL CHECK (recorded_at_unix_millis > 0)
);

CREATE TABLE IF NOT EXISTS situation.focus_transitions (
  transition_id TEXT PRIMARY KEY,
  focus_frame_id TEXT NOT NULL,
  from_revision BIGINT NOT NULL CHECK (from_revision > 0),
  to_revision BIGINT NOT NULL CHECK (to_revision > 0),
  operation TEXT NOT NULL,
  cause_ref TEXT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL CHECK (recorded_at_unix_millis > 0)
);

CREATE INDEX IF NOT EXISTS focus_transitions_frame_order ON situation.focus_transitions(focus_frame_id, recorded_at_unix_millis, transition_id);
GRANT SELECT, INSERT, UPDATE ON situation.context_scopes, situation.focus_frames, situation.focus_transitions TO situation_role;

RESET ROLE;
COMMIT;