generated from MokoConsulting/Template-Joomla
68 lines
2.5 KiB
SQL
68 lines
2.5 KiB
SQL
|
--
|
||
|
|
-- 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;
|