--
-- MokoSuite Support Tables
--

CREATE TABLE IF NOT EXISTS `#__mokosuitesupport_conversations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `channel` ENUM('website','facebook','whatsapp','instagram','email') NOT NULL DEFAULT 'website',
  `channel_id` VARCHAR(255) NOT NULL DEFAULT '',
  `contact_id` INT DEFAULT NULL,
  `visitor_name` VARCHAR(255) NOT NULL DEFAULT '',
  `visitor_email` VARCHAR(255) NOT NULL DEFAULT '',
  `agent_id` INT DEFAULT NULL,
  `status` ENUM('open','assigned','waiting','closed') NOT NULL DEFAULT 'open',
  `csat_rating` TINYINT UNSIGNED DEFAULT NULL,
  `started_at` DATETIME NOT NULL,
  `last_message_at` DATETIME DEFAULT NULL,
  `closed_at` DATETIME DEFAULT NULL,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_channel` (`channel`, `channel_id`),
  KEY `idx_agent` (`agent_id`),
  KEY `idx_status` (`status`),
  KEY `idx_contact` (`contact_id`),
  KEY `idx_last_msg` (`last_message_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitesupport_messages` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `conversation_id` INT UNSIGNED NOT NULL,
  `sender_type` ENUM('visitor','agent','system') NOT NULL,
  `sender_id` INT DEFAULT NULL,
  `body` TEXT NOT NULL,
  `attachments` JSON DEFAULT NULL,
  `read_at` DATETIME DEFAULT NULL,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_conversation` (`conversation_id`),
  KEY `idx_created` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitesupport_agents` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `display_name` VARCHAR(255) NOT NULL DEFAULT '',
  `avatar_path` VARCHAR(500) NOT NULL DEFAULT '',
  `status` ENUM('online','away','offline') NOT NULL DEFAULT 'offline',
  `max_conversations` INT UNSIGNED NOT NULL DEFAULT 5,
  `active_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user` (`user_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitesupport_canned_responses` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `body` TEXT NOT NULL,
  `category` VARCHAR(100) NOT NULL DEFAULT '',
  `shortcut` VARCHAR(50) NOT NULL DEFAULT '',
  `published` TINYINT NOT NULL DEFAULT 1,
  `ordering` INT NOT NULL DEFAULT 0,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_category` (`category`),
  KEY `idx_shortcut` (`shortcut`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;