CREATE TABLE IF NOT EXISTS `chat_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(1024) DEFAULT NULL,
  `user_phone` varchar(20) DEFAULT NULL,
  `user_email` varchar(100) DEFAULT NULL,
  `live_message_last` text DEFAULT NULL,
  `create_dttm` datetime NULL DEFAULT current_timestamp(),
  `user_live_active` tinyint(3) NULL DEFAULT 0,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `chat_answer` (
  `answer_id` int(11) NOT NULL AUTO_INCREMENT,
  `answer_description` text DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `question_tag_id` int(11) DEFAULT NULL,
  `create_dttm` datetime NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`answer_id`),
  KEY `question_tag_id` (`question_tag_id`) USING BTREE,
  KEY `user_id` (`user_id`),
  CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `chat_user` (`user_id`) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `chat_question_tag` (
  `question_tag_id` int(11) NOT NULL AUTO_INCREMENT,
  `question_tag_name` varchar(70) DEFAULT NULL,
  `question_tag_language` enum('MY','EN') NULL DEFAULT 'MY',
  `create_dttm` datetime NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`question_tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `chat_answer_bot` (
  `answer_bot_id` int(11) NOT NULL AUTO_INCREMENT,
  `answer_bot_description` mediumtext DEFAULT NULL,
  `answer_bot_language` enum('MY','EN') NULL DEFAULT 'MY',
  `answer_bot_suggestion_description` longtext DEFAULT NULL,
  `question_tag_id` int(11) DEFAULT NULL,
  `question_id` int(11) DEFAULT NULL,
  `create_dttm` datetime NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`answer_bot_id`),
  KEY `question_tag_id_answer_bot` (`question_tag_id`),
  KEY `question_id_answer_bot` (`question_id`),
  CONSTRAINT `question_tag_id_answer_bot` FOREIGN KEY (`question_tag_id`) REFERENCES `chat_question_tag` (`question_tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `chat_common_question` (
  `common_question_id` int(11) NOT NULL AUTO_INCREMENT,
  `common_question_description` varchar(256) DEFAULT NULL,
  `create_dttm` datetime NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`common_question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `chat_common_word` (
  `common_word_id` int(11) NOT NULL AUTO_INCREMENT,
  `common_word_description` varchar(50) DEFAULT NULL,
  `create_dttm` datetime NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`common_word_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `chat_config` (
  `config_id` int(11) NOT NULL,
  `config_status` tinyint(3) DEFAULT NULL,
  `config_start_time` time DEFAULT NULL,
  `config_end_time` time DEFAULT NULL,
  `config_message` varchar(1024) DEFAULT NULL,
  `create_dttm` datetime NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`config_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `chat_greeting` (
  `greeting_id` int(11) NOT NULL AUTO_INCREMENT,
  `greeting_description` varchar(200) DEFAULT NULL,
  `greeting_time_start` tinyint(3) NULL DEFAULT 0,
  `greeting_time_end` tinyint(3) NULL DEFAULT 0,
  `create_dttm` datetime NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`greeting_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `chat_live` (
  `live_id` int(11) NOT NULL AUTO_INCREMENT,
  `live_message` longtext DEFAULT NULL,
  `live_session` varchar(128) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `create_dttm` datetime NULL DEFAULT current_timestamp(),
  `live_read_status` tinyint(3) NULL DEFAULT 0,
  `admin_id` int(11) NULL DEFAULT 0,
  PRIMARY KEY (`live_id`),
  KEY `user_id` (`user_id`),
  KEY `live_session` (`live_session`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `chat_question` (
  `question_id` int(11) NOT NULL AUTO_INCREMENT,
  `question_description` mediumtext DEFAULT NULL,
  `question_review` tinyint(3) NULL DEFAULT 0,
  `question_language` enum('MY','EN') NULL DEFAULT 'MY',
  `user_id` int(11) NULL DEFAULT 0,
  `create_dttm` datetime NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`question_id`),
  KEY `user_id_question` (`user_id`),
  CONSTRAINT `user_id_question` FOREIGN KEY (`user_id`) REFERENCES `chat_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `chat_suggestion` (
  `suggestion_id` int(11) NOT NULL AUTO_INCREMENT,
  `suggestion_description` longtext DEFAULT NULL,
  `suggestion_status` char(3) DEFAULT 'A' COMMENT 'A=Active, X=Not Active',
  `create_dttm` datetime NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`suggestion_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert  into `chat_greeting`(`greeting_id`,`greeting_description`,`greeting_time_start`,`greeting_time_end`,`create_dttm`) values 
(1,'Selamat pagi',0,11,'2020-06-13 07:03:35'),
(2,'Selamat tengah hari',12,13,'2020-06-13 07:04:20'),
(3,'Selamat petang',14,18,'2020-06-13 07:05:23'),
(4,'Selamat malam',19,23,'2020-06-13 07:05:48');

insert  into `chat_config`(`config_id`,`config_status`,`config_start_time`,`config_end_time`,`config_message`,`create_dttm`) values 
(1,0,'09:00:00','17:00:00','Live chat dengan pentadbir beroperasi di antara jam 9.00 pagi hingga 5.00 petang.. harap maaf','2020-07-05 01:23:58');
