CREATE TABLE IF NOT EXISTS customer_external_identities (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  identity_id TEXT NOT NULL UNIQUE,
  customer_id TEXT NOT NULL,
  channel TEXT NOT NULL,
  external_subject TEXT NOT NULL,
  display_name_snapshot TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS ix_customer_external_identities_channel_subject_unique
  ON customer_external_identities(channel, external_subject);
CREATE INDEX IF NOT EXISTS idx_customer_external_identities_identity_id ON customer_external_identities(identity_id);
CREATE INDEX IF NOT EXISTS idx_customer_external_identities_customer_id ON customer_external_identities(customer_id);
CREATE INDEX IF NOT EXISTS idx_customer_external_identities_channel ON customer_external_identities(channel);
CREATE INDEX IF NOT EXISTS idx_customer_external_identities_external_subject ON customer_external_identities(external_subject);
CREATE INDEX IF NOT EXISTS idx_customer_external_identities_created_at ON customer_external_identities(created_at);
CREATE INDEX IF NOT EXISTS idx_customer_external_identities_updated_at ON customer_external_identities(updated_at);

CREATE TABLE IF NOT EXISTS ai_sessions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  session_id TEXT NOT NULL UNIQUE,
  channel TEXT NOT NULL,
  thread_id TEXT,
  interaction_id TEXT,
  customer_id TEXT,
  agent_profile TEXT NOT NULL DEFAULT 'telegram_support',
  language TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  summary_text TEXT NOT NULL DEFAULT '',
  last_user_message_id TEXT,
  last_ai_message_id TEXT,
  handoff_reason TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  closed_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_ai_sessions_session_id ON ai_sessions(session_id);
CREATE INDEX IF NOT EXISTS idx_ai_sessions_channel ON ai_sessions(channel);
CREATE INDEX IF NOT EXISTS idx_ai_sessions_thread_id ON ai_sessions(thread_id);
CREATE INDEX IF NOT EXISTS idx_ai_sessions_interaction_id ON ai_sessions(interaction_id);
CREATE INDEX IF NOT EXISTS idx_ai_sessions_customer_id ON ai_sessions(customer_id);
CREATE INDEX IF NOT EXISTS idx_ai_sessions_agent_profile ON ai_sessions(agent_profile);
CREATE INDEX IF NOT EXISTS idx_ai_sessions_language ON ai_sessions(language);
CREATE INDEX IF NOT EXISTS idx_ai_sessions_status ON ai_sessions(status);
CREATE INDEX IF NOT EXISTS idx_ai_sessions_last_user_message_id ON ai_sessions(last_user_message_id);
CREATE INDEX IF NOT EXISTS idx_ai_sessions_last_ai_message_id ON ai_sessions(last_ai_message_id);
CREATE INDEX IF NOT EXISTS idx_ai_sessions_created_at ON ai_sessions(created_at);
CREATE INDEX IF NOT EXISTS idx_ai_sessions_updated_at ON ai_sessions(updated_at);
CREATE INDEX IF NOT EXISTS idx_ai_sessions_closed_at ON ai_sessions(closed_at);

CREATE TABLE IF NOT EXISTS ai_jobs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  job_id TEXT NOT NULL UNIQUE,
  session_id TEXT NOT NULL,
  thread_id TEXT NOT NULL,
  trigger_message_id TEXT,
  status TEXT NOT NULL DEFAULT 'pending',
  attempts INTEGER NOT NULL DEFAULT 0,
  next_attempt_at TEXT,
  locked_until TEXT,
  last_error TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_ai_jobs_job_id ON ai_jobs(job_id);
CREATE INDEX IF NOT EXISTS idx_ai_jobs_session_id ON ai_jobs(session_id);
CREATE INDEX IF NOT EXISTS idx_ai_jobs_thread_id ON ai_jobs(thread_id);
CREATE INDEX IF NOT EXISTS idx_ai_jobs_trigger_message_id ON ai_jobs(trigger_message_id);
CREATE INDEX IF NOT EXISTS idx_ai_jobs_status ON ai_jobs(status);
CREATE INDEX IF NOT EXISTS idx_ai_jobs_next_attempt_at ON ai_jobs(next_attempt_at);
CREATE INDEX IF NOT EXISTS idx_ai_jobs_locked_until ON ai_jobs(locked_until);
CREATE INDEX IF NOT EXISTS idx_ai_jobs_created_at ON ai_jobs(created_at);
CREATE INDEX IF NOT EXISTS idx_ai_jobs_updated_at ON ai_jobs(updated_at);

CREATE TABLE IF NOT EXISTS ai_turns (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  turn_id TEXT NOT NULL UNIQUE,
  session_id TEXT NOT NULL,
  thread_id TEXT,
  interaction_id TEXT,
  role TEXT NOT NULL,
  source_type TEXT NOT NULL,
  text TEXT NOT NULL,
  payload_json TEXT NOT NULL DEFAULT '{}',
  model TEXT,
  finish_reason TEXT,
  latency_ms INTEGER,
  created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_ai_turns_turn_id ON ai_turns(turn_id);
CREATE INDEX IF NOT EXISTS idx_ai_turns_session_id ON ai_turns(session_id);
CREATE INDEX IF NOT EXISTS idx_ai_turns_thread_id ON ai_turns(thread_id);
CREATE INDEX IF NOT EXISTS idx_ai_turns_interaction_id ON ai_turns(interaction_id);
CREATE INDEX IF NOT EXISTS idx_ai_turns_role ON ai_turns(role);
CREATE INDEX IF NOT EXISTS idx_ai_turns_source_type ON ai_turns(source_type);
CREATE INDEX IF NOT EXISTS idx_ai_turns_model ON ai_turns(model);
CREATE INDEX IF NOT EXISTS idx_ai_turns_created_at ON ai_turns(created_at);

ALTER TABLE telegram_threads ADD COLUMN ai_session_id TEXT;
ALTER TABLE telegram_threads ADD COLUMN ai_state TEXT;
ALTER TABLE telegram_threads ADD COLUMN ai_handoff_reason TEXT;
ALTER TABLE telegram_threads ADD COLUMN ai_last_model_at TEXT;
CREATE INDEX IF NOT EXISTS idx_telegram_threads_ai_session_id ON telegram_threads(ai_session_id);
CREATE INDEX IF NOT EXISTS idx_telegram_threads_ai_state ON telegram_threads(ai_state);
CREATE INDEX IF NOT EXISTS idx_telegram_threads_ai_last_model_at ON telegram_threads(ai_last_model_at);

ALTER TABLE telegram_messages ADD COLUMN author_type TEXT NOT NULL DEFAULT 'customer';
ALTER TABLE telegram_messages ADD COLUMN author_id TEXT;
UPDATE telegram_messages
SET author_type = CASE
  WHEN direction = 'outbound' THEN 'human'
  WHEN direction = 'system' THEN 'system'
  ELSE 'customer'
END
WHERE author_type IS NULL OR TRIM(author_type) = '';
UPDATE telegram_messages
SET author_id = operator_user
WHERE direction = 'outbound'
  AND operator_user IS NOT NULL
  AND (author_id IS NULL OR TRIM(author_id) = '');
CREATE INDEX IF NOT EXISTS idx_telegram_messages_author_type ON telegram_messages(author_type);
CREATE INDEX IF NOT EXISTS idx_telegram_messages_author_id ON telegram_messages(author_id);