BEGIN;

SET ROLE situation_role;

CREATE TABLE IF NOT EXISTS situation.projects (
  project_id TEXT PRIMARY KEY,
  display_name TEXT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (project_id <> ''),
  CHECK (display_name <> '')
);

CREATE TABLE IF NOT EXISTS situation.project_aliases (
  project_id TEXT NOT NULL,
  alias TEXT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  PRIMARY KEY (project_id, alias),
  CHECK (project_id <> ''),
  CHECK (alias <> '')
);

CREATE TABLE IF NOT EXISTS situation.active_project_focus (
  actor_id TEXT NOT NULL,
  principal_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  project_display_name TEXT NOT NULL,
  provenance TEXT NOT NULL,
  confidence_millis BIGINT NOT NULL,
  source_utterance_id TEXT NOT NULL,
  updated_at_unix_millis BIGINT NOT NULL,
  PRIMARY KEY (actor_id, principal_id),
  CHECK (provenance IN ('EXPLICIT','INHERITED_FOCUS','ENTITY_REFERENCE','TASK_REFERENCE','REPOSITORY_REFERENCE','INFERRED','USER_CONFIRMED','AMBIGUOUS')),
  CHECK (confidence_millis BETWEEN 0 AND 1000)
);

INSERT INTO situation.projects(project_id, display_name, recorded_at_unix_millis)
VALUES ('assistant', 'Assistant', 1), ('upo', 'УПО', 1)
ON CONFLICT (project_id) DO NOTHING;

INSERT INTO situation.project_aliases(project_id, alias, recorded_at_unix_millis)
VALUES
  ('assistant', 'assistant', 1),
  ('assistant', 'домовой', 1),
  ('assistant', 'ассистент', 1),
  ('upo', 'упо', 1),
  ('upo', 'upo', 1)
ON CONFLICT (project_id, alias) DO NOTHING;

RESET ROLE;

SET ROLE interaction_role;

ALTER TABLE interaction.relations DROP CONSTRAINT IF EXISTS relations_kind_check;
ALTER TABLE interaction.relations
  ADD CONSTRAINT relations_kind_check
  CHECK (kind IN ('CORRECTS','RESPONDS_TO','REQUESTS_COGNITION','COGNITION_FALLBACK','SUPERSEDES','CLARIFIES'));

CREATE TABLE IF NOT EXISTS interaction.utterance_interpretations (
  interpretation_id TEXT PRIMARY KEY,
  utterance_id TEXT NOT NULL,
  speech_act TEXT NOT NULL,
  confidence_millis BIGINT NOT NULL,
  source TEXT NOT NULL,
  assertion_verification TEXT NOT NULL,
  requested_outcome TEXT NOT NULL DEFAULT '',
  clarification TEXT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (speech_act IN ('DIRECTIVE','ASSERTION','DECISION','PREFERENCE','QUESTION','HYPOTHESIS','CORRECTION','REQUEST_TO_INVESTIGATE','CONFIRMATION','REJECTION','CANCELLATION','CONVERSATIONAL','UNKNOWN')),
  CHECK (confidence_millis BETWEEN 0 AND 1000),
  CHECK (assertion_verification IN ('UNVERIFIED','VERIFIED','REJECTED','NOT_APPLICABLE')),
  CHECK (clarification IN ('NOT_REQUIRED','REQUIRED'))
);

CREATE TABLE IF NOT EXISTS interaction.project_references (
  project_reference_id TEXT PRIMARY KEY,
  utterance_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  project_display_name TEXT NOT NULL,
  role TEXT NOT NULL,
  provenance TEXT NOT NULL,
  confidence_millis BIGINT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (role IN ('PRIMARY','RELATED','REFERENCE_SOURCE')),
  CHECK (provenance IN ('EXPLICIT','INHERITED_FOCUS','ENTITY_REFERENCE','TASK_REFERENCE','REPOSITORY_REFERENCE','INFERRED','USER_CONFIRMED','AMBIGUOUS')),
  CHECK (confidence_millis BETWEEN 0 AND 1000)
);

RESET ROLE;
COMMIT;