/*
 * Copyright 2022 youngmonkeys.org
 *
 * Licensed under the ezyplatform, Version 1.0.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     https://youngmonkeys.org/licenses/ezyplatform-1.0.0.txt
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
*/

CREATE TABLE IF NOT EXISTS `ezychat_channels` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(256) COLLATE utf8mb4_unicode_520_ci NOT NULL,
    `parent_id` bigint unsigned NOT NULL DEFAULT 0,
    `avatar_image_id` bigint unsigned NOT NULL DEFAULT 0,
    `channel_type` varchar(25) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'NORMAL',
    `status` varchar(25) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'ACTIVATED',
    `created_at` datetime NOT NULL,
    `updated_at` datetime NOT NULL,
    `last_chat_time` datetime NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `index_name` (`name`),
    INDEX `index_parent_id` (`parent_id`),
    INDEX `index_channel_type` (`channel_type`),
    INDEX `index_status` (`status`),
    INDEX `index_created_at` (`created_at`),
    INDEX `index_updated_at` (`updated_at`),
    INDEX `index_last_chat_time` (`last_chat_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE IF NOT EXISTS `ezychat_channel_members` (
    `channel_id` bigint unsigned NOT NULL default 0,
    `member_id` bigint unsigned NOT NULL,
    `member_type` varchar(25) COLLATE utf8mb4_unicode_520_ci NOT NULL,
    `created_at` datetime NOT NULL,
    PRIMARY KEY (`channel_id`, `member_id`, `member_type`),
    INDEX `index_channel_id` (`channel_id`),
    INDEX `index_member_id_member_type` (`member_id`, `member_type`),
    INDEX `index_member_id` (`member_id`),
    INDEX `index_member_type` (`member_type`),
    INDEX `index_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE IF NOT EXISTS `ezychat_messages` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `sender_id` bigint unsigned NOT NULL DEFAULT 0,
    `sender_type` varchar(25) COLLATE utf8mb4_unicode_520_ci NOT NULL,
    `channel_id` bigint unsigned NOT NULL DEFAULT 0,
    `thread_id` bigint unsigned NOT NULL DEFAULT 0,
    `parent_id` bigint unsigned NOT NULL DEFAULT 0,
    `title` varchar(300) COLLATE utf8mb4_unicode_520_ci,
    `message` mediumtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
    `media_id` bigint unsigned NOT NULL DEFAULT 0,
    `message_type` varchar(25) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'NORMAL',
    `status` varchar(25) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'PUBLISHED',
    `created_at` datetime NOT NULL,
    `updated_at` datetime NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `index_sender_id_sender_type` (`sender_id`, `sender_type`),
    INDEX `index_channel_id` (`channel_id`),
    INDEX `index_thread_id` (`thread_id`),
    INDEX `index_parent_id` (`parent_id`),
    INDEX `index_media_id` (`media_id`),
    INDEX `index_message_type` (`message_type`),
    INDEX `index_status` (`status`),
    INDEX `index_created_at` (`created_at`),
    INDEX `index_updated_at` (`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE IF NOT EXISTS `ezychat_message_status` (
    `message_id` bigint unsigned NOT NULL,
    `receiver_id` bigint unsigned NOT NULL,
    `receiver_type` varchar(25) COLLATE utf8mb4_unicode_520_ci NOT NULL,
    `status` varchar(25) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'SENT',
    `created_at` datetime NOT NULL,
    `updated_at` datetime,
    PRIMARY KEY (`message_id`, `receiver_id`, `receiver_type`),
    INDEX `index_receiver_id_receiver_type` (`receiver_id`, `receiver_type`),
    INDEX `index_message_id` (`message_id`),
    INDEX `index_status` (`status`),
    INDEX `index_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE IF NOT EXISTS `ezychat_anonymous_users` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `uuid` varchar(128) COLLATE utf8mb4_unicode_520_ci,
    `user_id` bigint unsigned NOT NULL DEFAULT 0,
    `username` varchar(128) COLLATE utf8mb4_unicode_520_ci,
    `password` varchar(255) COLLATE utf8mb4_unicode_520_ci,
    `email` varchar(120) COLLATE utf8mb4_unicode_520_ci,
    `phone` varchar(20) COLLATE utf8mb4_unicode_520_ci,
    `display_name` varchar(250) COLLATE utf8mb4_unicode_520_ci DEFAULT '',
    `created_at` datetime NOT NULL,
    `updated_at` datetime NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `key_uuid` (`uuid`),
    INDEX `index_user_id` (`user_id`),
    INDEX `index_username` (`username`),
    INDEX `index_password` (`password`),
    INDEX `index_email` (`email`),
    INDEX `index_phone` (`phone`),
    INDEX `index_display_name` (`display_name`),
    INDEX `index_created_at` (`created_at`),
    INDEX `index_updated_at` (`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;