-- Live Chat System schema
-- Run once against your existing MySQL database (safe to add alongside existing tables).

CREATE TABLE IF NOT EXISTS chat_operators (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  display_name VARCHAR(128) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  status ENUM('online','away','offline') NOT NULL DEFAULT 'offline',
  last_active DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chat_sessions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  visitor_token VARCHAR(64) NOT NULL,             -- random id stored in visitor's browser (localStorage) — NOT unique: a returning visitor legitimately gets a new row with the same token once their prior session closes
  status ENUM('waiting','active','closed','no_agent') NOT NULL DEFAULT 'waiting',
  operator_id INT UNSIGNED NULL,
  visitor_name VARCHAR(128) NULL,
  visitor_email VARCHAR(191) NULL,
  visitor_reason VARCHAR(255) NULL,               -- pre-chat form "how can we help"
  zip_code VARCHAR(16) NULL,
  order_number VARCHAR(64) NULL,
  subject VARCHAR(255) NULL,
  page_url VARCHAR(500) NULL,
  referrer VARCHAR(500) NULL,
  ip_address VARCHAR(64) NULL,
  city VARCHAR(128) NULL,
  region VARCHAR(128) NULL,
  country VARCHAR(128) NULL,
  visitor_language VARCHAR(10) NULL,
  site VARCHAR(64) NULL,                          -- which of your sites (fleetautoglass.com, ptl.fleetautoglass.com, etc.)
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_activity DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ended_at DATETIME NULL,
  FOREIGN KEY (operator_id) REFERENCES chat_operators(id) ON DELETE SET NULL,
  INDEX (status),
  INDEX (last_activity),
  INDEX visitor_token_idx (visitor_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chat_messages (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_id INT UNSIGNED NOT NULL,
  sender_type ENUM('visitor','operator','system') NOT NULL,
  sender_name VARCHAR(128) NULL,
  message TEXT NULL,
  translated_message TEXT NULL,
  translated_lang VARCHAR(10) NULL,
  file_url VARCHAR(500) NULL,
  file_name VARCHAR(255) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  FOREIGN KEY (session_id) REFERENCES chat_sessions(id) ON DELETE CASCADE,
  INDEX (session_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chat_typing (
  session_id INT UNSIGNED NOT NULL,
  who ENUM('visitor','operator') NOT NULL,
  is_typing TINYINT(1) NOT NULL DEFAULT 0,
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (session_id, who),
  FOREIGN KEY (session_id) REFERENCES chat_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chat_settings (
  setting_key VARCHAR(64) NOT NULL PRIMARY KEY,
  setting_value TEXT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chat_session_refusals (
  session_id INT UNSIGNED NOT NULL,
  operator_id INT UNSIGNED NOT NULL,
  refused_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (session_id, operator_id),
  FOREIGN KEY (session_id) REFERENCES chat_sessions(id) ON DELETE CASCADE,
  FOREIGN KEY (operator_id) REFERENCES chat_operators(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS canned_responses (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  operator_id INT UNSIGNED NULL,   -- NULL = shared/global response visible to all operators
  category VARCHAR(64) NOT NULL DEFAULT 'General',
  shortcut VARCHAR(64) NOT NULL,   -- e.g. "/hi", "/quote"
  response_text TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (operator_id) REFERENCES chat_operators(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed one operator so you can log in (change password immediately after install).
-- password below is 'changeme' hashed with PHP password_hash() — replace via the console's
-- "add operator" step in setup.php instead of trusting this fixed hash.