-- Stage 3+ service schema (SQLite)

CREATE TABLE IF NOT EXISTS audit_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  event_id TEXT NOT NULL UNIQUE,
  actor TEXT NOT NULL,
  action TEXT NOT NULL,
  entity TEXT NOT NULL,
  metadata_json TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_audit_events_event_id ON audit_events (event_id);
CREATE INDEX IF NOT EXISTS ix_audit_events_actor ON audit_events (actor);
CREATE INDEX IF NOT EXISTS ix_audit_events_action ON audit_events (action);
CREATE INDEX IF NOT EXISTS ix_audit_events_entity ON audit_events (entity);
CREATE INDEX IF NOT EXISTS ix_audit_events_created_at ON audit_events (created_at);

CREATE TABLE IF NOT EXISTS voice_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  event_id TEXT NOT NULL UNIQUE,
  event_type TEXT NOT NULL,
  call_id TEXT NOT NULL,
  interaction_id TEXT,
  payload_json TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_voice_events_event_id ON voice_events (event_id);
CREATE INDEX IF NOT EXISTS ix_voice_events_event_type ON voice_events (event_type);
CREATE INDEX IF NOT EXISTS ix_voice_events_call_id ON voice_events (call_id);
CREATE INDEX IF NOT EXISTS ix_voice_events_interaction_id ON voice_events (interaction_id);
CREATE INDEX IF NOT EXISTS ix_voice_events_created_at ON voice_events (created_at);

CREATE TABLE IF NOT EXISTS telegram_messages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  message_id TEXT NOT NULL UNIQUE,
  chat_id TEXT NOT NULL,
  text TEXT NOT NULL,
  customer_external_id TEXT,
  payload_json TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_telegram_messages_message_id ON telegram_messages (message_id);
CREATE INDEX IF NOT EXISTS ix_telegram_messages_chat_id ON telegram_messages (chat_id);
CREATE INDEX IF NOT EXISTS ix_telegram_messages_customer_external_id ON telegram_messages (customer_external_id);
CREATE INDEX IF NOT EXISTS ix_telegram_messages_created_at ON telegram_messages (created_at);

CREATE TABLE IF NOT EXISTS kb_categories (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  category_id TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_kb_categories_category_id ON kb_categories (category_id);
CREATE INDEX IF NOT EXISTS ix_kb_categories_name ON kb_categories (name);
CREATE INDEX IF NOT EXISTS ix_kb_categories_created_at ON kb_categories (created_at);

CREATE TABLE IF NOT EXISTS kb_articles (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  article_id TEXT NOT NULL UNIQUE,
  category_id TEXT NOT NULL,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  tags_json TEXT NOT NULL DEFAULT '[]',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_kb_articles_article_id ON kb_articles (article_id);
CREATE INDEX IF NOT EXISTS ix_kb_articles_category_id ON kb_articles (category_id);
CREATE INDEX IF NOT EXISTS ix_kb_articles_title ON kb_articles (title);
CREATE INDEX IF NOT EXISTS ix_kb_articles_created_at ON kb_articles (created_at);
CREATE INDEX IF NOT EXISTS ix_kb_articles_updated_at ON kb_articles (updated_at);

CREATE TABLE IF NOT EXISTS reporting_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  queue_id TEXT NOT NULL,
  answered INTEGER NOT NULL DEFAULT 0,
  wait_seconds INTEGER NOT NULL DEFAULT 0,
  handle_seconds INTEGER NOT NULL DEFAULT 0,
  abandoned INTEGER NOT NULL DEFAULT 0,
  resolved_first_contact INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_reporting_events_queue_id ON reporting_events (queue_id);
CREATE INDEX IF NOT EXISTS ix_reporting_events_created_at ON reporting_events (created_at);

CREATE TABLE IF NOT EXISTS supervisor_agent_states (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  agent_id TEXT NOT NULL UNIQUE,
  state TEXT NOT NULL,
  queue_id TEXT,
  updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_supervisor_agent_states_agent_id ON supervisor_agent_states (agent_id);
CREATE INDEX IF NOT EXISTS ix_supervisor_agent_states_state ON supervisor_agent_states (state);
CREATE INDEX IF NOT EXISTS ix_supervisor_agent_states_queue_id ON supervisor_agent_states (queue_id);
CREATE INDEX IF NOT EXISTS ix_supervisor_agent_states_updated_at ON supervisor_agent_states (updated_at);

CREATE TABLE IF NOT EXISTS supervisor_queue_snapshots (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  queue_id TEXT NOT NULL UNIQUE,
  in_queue INTEGER NOT NULL DEFAULT 0,
  avg_wait_seconds INTEGER NOT NULL DEFAULT 0,
  updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_supervisor_queue_snapshots_queue_id ON supervisor_queue_snapshots (queue_id);
CREATE INDEX IF NOT EXISTS ix_supervisor_queue_snapshots_updated_at ON supervisor_queue_snapshots (updated_at);
