CREATE TABLE IF NOT EXISTS telegram_threads (
  id SERIAL PRIMARY KEY,
  thread_id TEXT NOT NULL UNIQUE,
  chat_id TEXT NOT NULL UNIQUE,
  interaction_id TEXT NOT NULL,
  telegram_user_id TEXT,
  username TEXT,
  display_name TEXT,
  queue_id TEXT,
  status TEXT NOT NULL,
  claimed_by_user TEXT,
  claimed_at TEXT,
  last_message_at TEXT NOT NULL,
  last_message_preview TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_telegram_threads_thread_id ON telegram_threads(thread_id);
CREATE INDEX IF NOT EXISTS idx_telegram_threads_chat_id ON telegram_threads(chat_id);
CREATE INDEX IF NOT EXISTS idx_telegram_threads_interaction_id ON telegram_threads(interaction_id);
CREATE INDEX IF NOT EXISTS idx_telegram_threads_telegram_user_id ON telegram_threads(telegram_user_id);
CREATE INDEX IF NOT EXISTS idx_telegram_threads_username ON telegram_threads(username);
CREATE INDEX IF NOT EXISTS idx_telegram_threads_display_name ON telegram_threads(display_name);
CREATE INDEX IF NOT EXISTS idx_telegram_threads_queue_id ON telegram_threads(queue_id);
CREATE INDEX IF NOT EXISTS idx_telegram_threads_status ON telegram_threads(status);
CREATE INDEX IF NOT EXISTS idx_telegram_threads_claimed_by_user ON telegram_threads(claimed_by_user);
CREATE INDEX IF NOT EXISTS idx_telegram_threads_claimed_at ON telegram_threads(claimed_at);
CREATE INDEX IF NOT EXISTS idx_telegram_threads_last_message_at ON telegram_threads(last_message_at);
CREATE INDEX IF NOT EXISTS idx_telegram_threads_created_at ON telegram_threads(created_at);
CREATE INDEX IF NOT EXISTS idx_telegram_threads_updated_at ON telegram_threads(updated_at);

ALTER TABLE telegram_messages ADD COLUMN IF NOT EXISTS thread_id TEXT;
ALTER TABLE telegram_messages ADD COLUMN IF NOT EXISTS interaction_id TEXT;
ALTER TABLE telegram_messages ADD COLUMN IF NOT EXISTS direction TEXT NOT NULL DEFAULT 'inbound';
ALTER TABLE telegram_messages ADD COLUMN IF NOT EXISTS telegram_message_id_external TEXT;
ALTER TABLE telegram_messages ADD COLUMN IF NOT EXISTS operator_user TEXT;
ALTER TABLE telegram_messages ADD COLUMN IF NOT EXISTS delivery_status TEXT;

CREATE INDEX IF NOT EXISTS idx_telegram_messages_thread_id ON telegram_messages(thread_id);
CREATE INDEX IF NOT EXISTS idx_telegram_messages_interaction_id ON telegram_messages(interaction_id);
CREATE INDEX IF NOT EXISTS idx_telegram_messages_direction ON telegram_messages(direction);
CREATE INDEX IF NOT EXISTS idx_telegram_messages_external_id ON telegram_messages(telegram_message_id_external);
CREATE INDEX IF NOT EXISTS idx_telegram_messages_operator_user ON telegram_messages(operator_user);
CREATE INDEX IF NOT EXISTS idx_telegram_messages_delivery_status ON telegram_messages(delivery_status);