BEGIN;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'interaction_role') THEN
    CREATE ROLE interaction_role LOGIN;
  END IF;
END $$;

CREATE SCHEMA IF NOT EXISTS interaction AUTHORIZATION interaction_role;
REVOKE ALL ON SCHEMA interaction FROM PUBLIC;

SET ROLE interaction_role;

CREATE TABLE IF NOT EXISTS interaction.conversations (
  conversation_id TEXT PRIMARY KEY,
  actor_id TEXT NOT NULL,
  principal_id TEXT NOT NULL,
  channel TEXT NOT NULL,
  state TEXT NOT NULL,
  created_at_unix_millis BIGINT NOT NULL,
  updated_at_unix_millis BIGINT NOT NULL,
  CHECK (channel = 'WEB_CHAT'),
  CHECK (state IN ('OPEN','ARCHIVED'))
);

CREATE TABLE IF NOT EXISTS interaction.utterances (
  utterance_id TEXT PRIMARY KEY,
  conversation_id TEXT NOT NULL,
  actor_id TEXT NOT NULL,
  principal_id TEXT NOT NULL,
  channel TEXT NOT NULL,
  received_at_unix_millis BIGINT NOT NULL,
  correlation_id TEXT NOT NULL,
  content TEXT NOT NULL,
  content_ref TEXT NOT NULL,
  speech_act TEXT NOT NULL,
  interpretation_state TEXT NOT NULL,
  corrects_utterance_id TEXT NOT NULL DEFAULT '',
  CHECK (channel = 'WEB_CHAT'),
  CHECK (content <> ''),
  CHECK (content_ref <> ''),
  CHECK (speech_act IN ('DIRECTIVE','ASSERTION','DECISION','PREFERENCE','QUESTION','HYPOTHESIS','CORRECTION','REQUEST_TO_INVESTIGATE','CONFIRMATION','REJECTION','CANCELLATION','CONVERSATIONAL','UNKNOWN')),
  CHECK (interpretation_state IN ('RECORDED','INTERPRETED'))
);

CREATE TABLE IF NOT EXISTS interaction.original_human_intents (
  intent_id TEXT PRIMARY KEY,
  utterance_id TEXT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  text TEXT NOT NULL,
  CHECK (text <> '')
);

CREATE TABLE IF NOT EXISTS interaction.relations (
  relation_id TEXT PRIMARY KEY,
  from_utterance_id TEXT NOT NULL,
  to_utterance_id TEXT NOT NULL,
  kind TEXT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (kind IN ('CORRECTS','RESPONDS_TO','REQUESTS_COGNITION','COGNITION_FALLBACK'))
);

RESET ROLE;
COMMIT;