CREATE DATABASE IF NOT EXISTS im DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE im;

CREATE TABLE IF NOT EXISTS im_user (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username    VARCHAR(64)     NOT NULL,
    password    VARCHAR(128)    NOT NULL,
    nickname    VARCHAR(64)     NOT NULL DEFAULT '',
    phone       VARCHAR(20)     NOT NULL DEFAULT '',
    signature   VARCHAR(128)    NOT NULL DEFAULT '',
    allow_search_account TINYINT NOT NULL DEFAULT 1 COMMENT '1允许通过账号搜索',
    allow_search_phone   TINYINT NOT NULL DEFAULT 1 COMMENT '1允许通过手机号搜索',
    allow_add_friend     TINYINT NOT NULL DEFAULT 1 COMMENT '1允许他人添加好友',
    avatar_ver  INT             NOT NULL DEFAULT 0,
    status      TINYINT         NOT NULL DEFAULT 1 COMMENT '1正常 0禁用 2系统账号',
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_username (username),
    KEY idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_sms_code (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    phone       VARCHAR(20)     NOT NULL,
    scene       VARCHAR(16)     NOT NULL,
    code        VARCHAR(8)      NOT NULL,
    expire_at   DATETIME        NOT NULL,
    used        TINYINT         NOT NULL DEFAULT 0,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_phone_scene (phone, scene, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_friend (
    uid         BIGINT UNSIGNED NOT NULL,
    friend_uid  BIGINT UNSIGNED NOT NULL,
    remark      VARCHAR(64)     NOT NULL DEFAULT '',
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (uid, friend_uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_message (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    from_uid    BIGINT UNSIGNED NOT NULL,
    to_uid      BIGINT UNSIGNED NOT NULL,
    msg_type    TINYINT         NOT NULL DEFAULT 1,
    content     TEXT            NOT NULL,
    sender_deleted   TINYINT      NOT NULL DEFAULT 0 COMMENT '发送方已删除',
    receiver_deleted TINYINT      NOT NULL DEFAULT 0 COMMENT '接收方已删除',
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_to_uid (to_uid, id),
    KEY idx_from_uid (from_uid, id),
    KEY idx_pair (from_uid, to_uid, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_conversation (
    uid           BIGINT UNSIGNED NOT NULL,
    peer_uid      BIGINT UNSIGNED NOT NULL,
    last_msg_id   BIGINT UNSIGNED NOT NULL DEFAULT 0,
    last_content  VARCHAR(256)    NOT NULL DEFAULT '',
    last_time     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    unread        INT             NOT NULL DEFAULT 0,
    PRIMARY KEY (uid, peer_uid),
    KEY idx_uid_time (uid, last_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 系统账号：文件传输助手。密码不可登录（status=2）
CREATE TABLE IF NOT EXISTS im_friend_request (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    from_uid    BIGINT UNSIGNED NOT NULL,
    to_uid      BIGINT UNSIGNED NOT NULL,
    status      TINYINT         NOT NULL DEFAULT 0 COMMENT '0待处理 1已同意 2已拒绝',
    message     VARCHAR(64)     NOT NULL DEFAULT '',
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_from_to (from_uid, to_uid),
    KEY idx_to_status (to_uid, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_sticker_pack (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name        VARCHAR(64)     NOT NULL,
    version     INT             NOT NULL DEFAULT 1,
    sort_order  INT             NOT NULL DEFAULT 0,
    status      TINYINT         NOT NULL DEFAULT 1,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_sticker (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    pack_id     BIGINT UNSIGNED NOT NULL,
    code        VARCHAR(64)     NOT NULL,
    name        VARCHAR(64)     NOT NULL DEFAULT '',
    file_path   VARCHAR(255)    NOT NULL,
    sha256      VARCHAR(64)     NOT NULL DEFAULT '',
    mime        VARCHAR(32)     NOT NULL DEFAULT 'image/png',
    sort_order  INT             NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY uk_pack_code (pack_id, code),
    KEY idx_pack (pack_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_file (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    from_uid    BIGINT UNSIGNED NOT NULL,
    name        VARCHAR(255)    NOT NULL,
    mime        VARCHAR(64)     NOT NULL DEFAULT 'application/octet-stream',
    size        INT             NOT NULL DEFAULT 0,
    sha256      VARCHAR(64)     NOT NULL DEFAULT '',
    file_path   VARCHAR(255)    NOT NULL DEFAULT '',
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_from (from_uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_favorite (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uid           BIGINT UNSIGNED NOT NULL,
    kind          TINYINT         NOT NULL DEFAULT 1 COMMENT '1文本 2图片 3文件 4名片 5笔记',
    title         VARCHAR(128)    NOT NULL DEFAULT '',
    content       MEDIUMTEXT      NOT NULL,
    src_msg_id    BIGINT UNSIGNED NOT NULL DEFAULT 0,
    src_peer_uid  BIGINT UNSIGNED NOT NULL DEFAULT 0,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_uid_time (uid, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_group (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name          VARCHAR(64)     NOT NULL DEFAULT '',
    owner_uid     BIGINT UNSIGNED NOT NULL,
    join_mode     TINYINT         NOT NULL DEFAULT 1 COMMENT '0开放 1审核 2禁止',
    member_count  INT             NOT NULL DEFAULT 0,
    avatar_ver    INT             NOT NULL DEFAULT 0,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_owner (owner_uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_group_member (
    group_id    BIGINT UNSIGNED NOT NULL,
    uid         BIGINT UNSIGNED NOT NULL,
    role        TINYINT         NOT NULL DEFAULT 0 COMMENT '0成员 1管理员 2群主',
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (group_id, uid),
    KEY idx_uid (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_group_request (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    group_id    BIGINT UNSIGNED NOT NULL,
    from_uid    BIGINT UNSIGNED NOT NULL,
    status      TINYINT         NOT NULL DEFAULT 0 COMMENT '0待处理 1已同意 2已拒绝',
    message     VARCHAR(64)     NOT NULL DEFAULT '',
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_group_from (group_id, from_uid),
    KEY idx_group_status (group_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_group_message (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    group_id    BIGINT UNSIGNED NOT NULL,
    from_uid    BIGINT UNSIGNED NOT NULL DEFAULT 0,
    msg_type    TINYINT         NOT NULL DEFAULT 1,
    content     TEXT            NOT NULL,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_group_id (group_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_group_msg_deleted (
    uid     BIGINT UNSIGNED NOT NULL,
    msg_id  BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (uid, msg_id),
    KEY idx_msg (msg_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_group_inbox (
    uid           BIGINT UNSIGNED NOT NULL,
    group_id      BIGINT UNSIGNED NOT NULL,
    last_msg_id   BIGINT UNSIGNED NOT NULL DEFAULT 0,
    last_content  VARCHAR(256)    NOT NULL DEFAULT '',
    last_time     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    unread        INT             NOT NULL DEFAULT 0,
    PRIMARY KEY (uid, group_id),
    KEY idx_uid_time (uid, last_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 系统账号：文件传输助手。密码不可登录（status=2）
INSERT INTO im_user (id, username, password, nickname, phone, signature, status)
VALUES (1, 'filehelper', '-', '文件传输助手', '', '文件助手', 2)
ON DUPLICATE KEY UPDATE nickname=VALUES(nickname), signature=VALUES(signature), status=VALUES(status);

INSERT INTO im_sticker_pack (id, name, version, sort_order, status)
VALUES (1, '默认表情', 1, 1, 1), (2, '手势', 1, 2, 1)
ON DUPLICATE KEY UPDATE name=VALUES(name), status=VALUES(status);