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,
    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;

-- 系统账号：文件传输助手。密码不可登录（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);