-- Adds SQLite-backed live chat support tables for MailMax.
-- Safe to run more than once.

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS live_chat_sessions (
  id TEXT PRIMARY KEY,
  status TEXT NOT NULL DEFAULT 'active',
  created_at INTEGER NOT NULL,
  last_message_at INTEGER DEFAULT NULL,
  visitor_ip TEXT DEFAULT NULL,
  visitor_city TEXT DEFAULT NULL,
  visitor_country TEXT DEFAULT NULL,
  visitor_country_code TEXT DEFAULT NULL
);
CREATE INDEX IF NOT EXISTS idx_live_chat_sessions_status ON live_chat_sessions(status);
CREATE INDEX IF NOT EXISTS idx_live_chat_sessions_created ON live_chat_sessions(created_at);

CREATE TABLE IF NOT EXISTS live_chat_messages (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  message TEXT NOT NULL,
  sender_type TEXT NOT NULL CHECK(sender_type IN ('user', 'agent')),
  sender_name TEXT DEFAULT NULL,
  file_id TEXT DEFAULT NULL,
  file_url TEXT DEFAULT NULL,
  file_name TEXT DEFAULT NULL,
  telegram_message_id TEXT DEFAULT NULL,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (session_id) REFERENCES live_chat_sessions(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_live_chat_messages_session ON live_chat_messages(session_id);
CREATE INDEX IF NOT EXISTS idx_live_chat_messages_created ON live_chat_messages(created_at);

CREATE TABLE IF NOT EXISTS live_chat_files (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  original_name TEXT NOT NULL,
  stored_name TEXT NOT NULL,
  mime_type TEXT DEFAULT 'application/octet-stream',
  size_bytes INTEGER NOT NULL DEFAULT 0,
  storage_path TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (session_id) REFERENCES live_chat_sessions(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_live_chat_files_session ON live_chat_files(session_id);

CREATE TABLE IF NOT EXISTS live_chat_settings (
  id TEXT PRIMARY KEY DEFAULT 'main',
  telegram_bot_token TEXT DEFAULT NULL,
  telegram_chat_id TEXT DEFAULT NULL,
  updated_at INTEGER DEFAULT NULL
);

-- The PHP livechat endpoint also adds file_id automatically for existing databases.


-- Existing installations are also upgraded automatically by api/livechat.php.
-- Run these manually only if you migrate the database outside the PHP endpoint:
-- ALTER TABLE live_chat_sessions ADD COLUMN visitor_ip TEXT DEFAULT NULL;
-- ALTER TABLE live_chat_sessions ADD COLUMN visitor_city TEXT DEFAULT NULL;
-- ALTER TABLE live_chat_sessions ADD COLUMN visitor_country TEXT DEFAULT NULL;
-- ALTER TABLE live_chat_sessions ADD COLUMN visitor_country_code TEXT DEFAULT NULL;