CREATE TABLE IF NOT EXISTS "im_private_message" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
    "client_message_id" varchar(64) DEFAULT NULL COMMENT '客户端消息编号',
    "sender_id" bigint NOT NULL COMMENT '发送人编号',
    "receiver_id" bigint NOT NULL COMMENT '接收人编号',
    "type" smallint NOT NULL COMMENT '消息类型',
    "content" varchar(8192) DEFAULT NULL COMMENT '消息内容',
    "status" tinyint NOT NULL COMMENT '消息状态',
    "receipt_status" tinyint NOT NULL DEFAULT 0 COMMENT '回执状态',
    "send_time" timestamp NOT NULL COMMENT '发送时间',
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id"),
    CONSTRAINT "uk_im_private_message_sender_client" UNIQUE ("sender_id", "client_message_id", "tenant_id")
) COMMENT 'IM 私聊消息表';

CREATE TABLE IF NOT EXISTS "im_group_message" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
    "client_message_id" varchar(64) DEFAULT NULL COMMENT '客户端消息编号',
    "sender_id" bigint NOT NULL COMMENT '发送人编号',
    "group_id" bigint NOT NULL COMMENT '群编号',
    "type" smallint NOT NULL COMMENT '消息类型',
    "content" varchar(8192) DEFAULT NULL COMMENT '消息内容',
    "status" tinyint NOT NULL COMMENT '消息状态',
    "send_time" timestamp NOT NULL COMMENT '发送时间',
    "receiver_user_ids" text DEFAULT NULL COMMENT '定向接收用户编号列表',
    "at_user_ids" varchar(1024) DEFAULT NULL COMMENT '@ 目标用户编号列表',
    "receipt_status" tinyint NOT NULL DEFAULT 0 COMMENT '回执状态',
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id"),
    CONSTRAINT "uk_im_group_message_sender_client" UNIQUE ("sender_id", "client_message_id", "tenant_id")
) COMMENT 'IM 群聊消息表';

CREATE TABLE IF NOT EXISTS "im_group" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
    "name" varchar(64) NOT NULL COMMENT '群名称',
    "owner_user_id" bigint NOT NULL COMMENT '群主用户编号',
    "avatar" varchar(512) DEFAULT NULL COMMENT '群头像',
    "notice" varchar(2048) DEFAULT NULL COMMENT '群公告',
    "banned" bit DEFAULT FALSE COMMENT '是否封禁',
    "banned_reason" varchar(512) DEFAULT NULL COMMENT '封禁原因',
    "banned_time" timestamp DEFAULT NULL COMMENT '封禁时间',
    "status" tinyint NOT NULL COMMENT '群状态',
    "dissolved_time" timestamp DEFAULT NULL COMMENT '解散时间',
    "muted_all" bit DEFAULT FALSE COMMENT '是否全群禁言',
    "join_approval" bit NOT NULL DEFAULT FALSE COMMENT '进群是否需群主 / 管理员审批；false 自由进群，true 需审批',
    "pinned_message_ids" varchar(128) DEFAULT NULL COMMENT '群置顶消息编号列表，逗号分隔',
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
) COMMENT 'IM 群信息表';

CREATE TABLE IF NOT EXISTS "im_group_member" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
    "group_id" bigint NOT NULL COMMENT '群编号',
    "user_id" bigint NOT NULL COMMENT '用户编号',
    "display_user_name" varchar(64) DEFAULT NULL COMMENT '组内显示名',
    "group_remark" varchar(64) DEFAULT NULL COMMENT '群备注',
    "silent" bit DEFAULT FALSE COMMENT '是否免打扰',
    "status" tinyint NOT NULL COMMENT '成员状态',
    "role" tinyint NOT NULL DEFAULT 3 COMMENT '成员角色：1=群主 2=管理员 3=普通成员',
    "join_time" timestamp DEFAULT NULL COMMENT '入群时间',
    "add_source" tinyint DEFAULT NULL COMMENT '加入来源',
    "inviter_user_id" bigint DEFAULT NULL COMMENT '邀请人用户编号；用户主动申请进群时为 NULL',
    "quit_time" timestamp DEFAULT NULL COMMENT '退群时间',
    "mute_end_time" timestamp DEFAULT NULL COMMENT '禁言到期时间',
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id"),
    CONSTRAINT "uk_im_group_member" UNIQUE ("group_id", "user_id", "tenant_id")
) COMMENT 'IM 群成员表';

CREATE TABLE IF NOT EXISTS "im_friend" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
    "user_id" bigint NOT NULL COMMENT '用户编号',
    "friend_user_id" bigint NOT NULL COMMENT '好友用户编号',
    "silent" bit DEFAULT FALSE COMMENT '是否免打扰',
    "display_name" varchar(64) NOT NULL DEFAULT '' COMMENT '好友展示备注（仅自己可见）',
    "add_source" tinyint DEFAULT NULL COMMENT '添加来源',
    "pinned" bit DEFAULT FALSE COMMENT '是否置顶联系人',
    "blocked" bit DEFAULT FALSE COMMENT '是否拉黑',
    "status" tinyint NOT NULL COMMENT '好友状态',
    "add_time" timestamp DEFAULT NULL COMMENT '添加好友时间',
    "delete_time" timestamp DEFAULT NULL COMMENT '删除好友时间',
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id"),
    CONSTRAINT "uk_user_friend" UNIQUE ("user_id", "friend_user_id", "tenant_id")
) COMMENT 'IM 好友关系表';

CREATE TABLE IF NOT EXISTS "im_friend_request" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
    "from_user_id" bigint NOT NULL COMMENT '发起方用户编号',
    "to_user_id" bigint NOT NULL COMMENT '接收方用户编号',
    "handle_result" tinyint NOT NULL DEFAULT 0 COMMENT '处理结果；0未处理；1同意；2拒绝',
    "apply_content" varchar(255) DEFAULT NULL COMMENT '申请理由',
    "handle_content" varchar(255) DEFAULT NULL COMMENT '处理理由',
    "display_name" varchar(64) DEFAULT NULL COMMENT '发起方对接收方的备注',
    "add_source" tinyint DEFAULT NULL COMMENT '添加来源',
    "handle_time" timestamp DEFAULT NULL COMMENT '处理时间',
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id"),
    CONSTRAINT "uk_im_friend_request" UNIQUE ("from_user_id", "to_user_id", "tenant_id")
) COMMENT 'IM 好友申请记录表';

CREATE TABLE IF NOT EXISTS "im_group_request" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
    "group_id" bigint NOT NULL COMMENT '群编号',
    "user_id" bigint NOT NULL COMMENT '申请人 / 被邀请人用户编号',
    "inviter_user_id" bigint DEFAULT NULL COMMENT '邀请人用户编号；NULL=主动申请；非NULL=被邀请待审批',
    "apply_content" varchar(255) DEFAULT NULL COMMENT '申请理由',
    "add_source" tinyint DEFAULT NULL COMMENT '加入来源',
    "handle_result" tinyint NOT NULL DEFAULT 0 COMMENT '处理结果；0未处理；1同意；2拒绝',
    "handle_user_id" bigint DEFAULT NULL COMMENT '处理人用户编号',
    "handle_content" varchar(255) DEFAULT NULL COMMENT '处理理由',
    "handle_time" timestamp DEFAULT NULL COMMENT '处理时间',
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id"),
    CONSTRAINT "uk_im_group_request" UNIQUE ("group_id", "user_id", "tenant_id")
) COMMENT 'IM 加群申请记录表';

CREATE TABLE IF NOT EXISTS "im_face_pack" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
    "name" varchar(64) NOT NULL COMMENT '表情包名称',
    "icon" varchar(512) DEFAULT NULL COMMENT '表情包图标（面板底部 tab 显示）',
    "sort" int NOT NULL DEFAULT 0 COMMENT '排序',
    "status" tinyint NOT NULL COMMENT '状态',
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
) COMMENT 'IM 表情包表';

CREATE TABLE IF NOT EXISTS "im_face_pack_item" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
    "pack_id" bigint NOT NULL COMMENT '所属表情包编号',
    "url" varchar(512) NOT NULL COMMENT '表情图 URL',
    "name" varchar(64) DEFAULT NULL COMMENT '表情名（可选；如「狗头」「捂脸」）',
    "width" int NOT NULL DEFAULT 0 COMMENT '渲染宽度（像素）',
    "height" int NOT NULL DEFAULT 0 COMMENT '渲染高度（像素）',
    "sort" int NOT NULL DEFAULT 0 COMMENT '排序',
    "status" tinyint NOT NULL COMMENT '状态',
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
) COMMENT 'IM 表情包项表';

CREATE TABLE IF NOT EXISTS "im_rtc_call" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
    "room" varchar(64) NOT NULL COMMENT '业务通话编号',
    "conversation_type" tinyint NOT NULL COMMENT '会话类型',
    "media_type" tinyint NOT NULL COMMENT '媒体类型',
    "inviter_user_id" bigint NOT NULL COMMENT '发起人用户编号',
    "group_id" bigint DEFAULT NULL COMMENT '群编号',
    "status" tinyint NOT NULL COMMENT '通话状态',
    "end_reason" tinyint DEFAULT NULL COMMENT '结束原因',
    "start_time" timestamp NOT NULL COMMENT '发起时间',
    "accept_time" timestamp DEFAULT NULL COMMENT '接通时间',
    "end_time" timestamp DEFAULT NULL COMMENT '结束时间',
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
) COMMENT 'IM 通话记录表';

CREATE TABLE IF NOT EXISTS "im_rtc_participant" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
    "call_id" bigint NOT NULL COMMENT '通话编号',
    "room" varchar(64) NOT NULL COMMENT '业务通话编号',
    "user_id" bigint NOT NULL COMMENT '参与者用户编号',
    "role" tinyint NOT NULL COMMENT '参与角色',
    "status" tinyint NOT NULL COMMENT '参与状态',
    "invite_time" timestamp NOT NULL COMMENT '被邀请时间',
    "accept_time" timestamp DEFAULT NULL COMMENT '接听时间',
    "leave_time" timestamp DEFAULT NULL COMMENT '离开时间',
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id"),
    CONSTRAINT "uk_im_rtc_participant_room_user" UNIQUE ("room", "user_id", "tenant_id")
) COMMENT 'IM 通话参与者表';

CREATE TABLE IF NOT EXISTS "im_face_user_item" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
    "user_id" bigint NOT NULL COMMENT '所属用户编号',
    "url" varchar(512) NOT NULL COMMENT '表情图 URL',
    "name" varchar(64) DEFAULT NULL COMMENT '表情名（可选）',
    "width" int NOT NULL DEFAULT 0 COMMENT '渲染宽度（像素）',
    "height" int NOT NULL DEFAULT 0 COMMENT '渲染高度（像素）',
    "sort" int NOT NULL DEFAULT 0 COMMENT '排序',
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id"),
    CONSTRAINT "uk_im_face_user_item_user_url_deleted" UNIQUE ("user_id", "url", "deleted")
) COMMENT 'IM 用户私有表情表';

CREATE TABLE IF NOT EXISTS "im_channel" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
    "code" varchar(64) NOT NULL COMMENT '频道业务码；唯一',
    "name" varchar(64) NOT NULL COMMENT '频道名称',
    "avatar" varchar(512) DEFAULT NULL COMMENT '频道头像',
    "sort" int NOT NULL DEFAULT 0 COMMENT '排序',
    "status" tinyint NOT NULL DEFAULT 0 COMMENT '状态；0 启用 1 停用',
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
) COMMENT 'IM 频道表';

CREATE TABLE IF NOT EXISTS "im_channel_material" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
    "channel_id" bigint NOT NULL COMMENT '频道编号',
    "type" tinyint NOT NULL COMMENT '内容类型；1 站内富文本 2 外链',
    "title" varchar(128) NOT NULL COMMENT '标题',
    "cover_url" varchar(512) DEFAULT NULL COMMENT '封面图',
    "summary" varchar(255) DEFAULT NULL COMMENT '摘要',
    "content" clob DEFAULT NULL COMMENT '正文；富文本 HTML',
    "url" varchar(512) DEFAULT NULL COMMENT '跳转链接；为空时点击在客户端内置详情页拉 content；非空则跳 url',
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
) COMMENT 'IM 频道素材表';

CREATE TABLE IF NOT EXISTS "im_channel_message" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
    "channel_id" bigint NOT NULL COMMENT '频道编号；冗余 im_channel_material.channel_id 便于检索',
    "material_id" bigint NOT NULL COMMENT '关联素材编号',
    "type" smallint NOT NULL COMMENT '消息类型',
    "content" varchar(8192) DEFAULT NULL COMMENT '消息内容；推送时 payload JSON 快照；不含富文本正文',
    "receiver_user_ids" text DEFAULT NULL COMMENT '接收人编号列表；逗号分隔；为空表示全员',
    "send_time" timestamp NOT NULL COMMENT '发送时间',
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
) COMMENT 'IM 频道消息表';

CREATE TABLE IF NOT EXISTS "im_conversation_read" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
    "user_id" bigint NOT NULL COMMENT '用户编号',
    "conversation_type" tinyint NOT NULL COMMENT '会话类型',
    "target_id" bigint NOT NULL COMMENT '目标编号',
    "message_id" bigint NOT NULL COMMENT '最大已读消息编号',
    "read_time" timestamp NOT NULL COMMENT '最近已读时间',
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id"),
    CONSTRAINT "uk_im_conversation_read_user_target" UNIQUE ("user_id", "conversation_type", "target_id", "tenant_id")
) COMMENT 'IM 会话读位置表';

CREATE TABLE IF NOT EXISTS "im_sensitive_word" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
    "word" varchar(128) NOT NULL COMMENT '敏感词',
    "status" tinyint NOT NULL DEFAULT 0 COMMENT '状态；0 启用 1 停用',
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id"),
    CONSTRAINT "uk_im_sensitive_word" UNIQUE ("word", "tenant_id")
) COMMENT 'IM 敏感词表';

CREATE TABLE IF NOT EXISTS "system_users" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "username" varchar(30) NOT NULL DEFAULT '',
    "password" varchar(100) NOT NULL DEFAULT '',
    "nickname" varchar(30) NOT NULL DEFAULT '',
    "remark" varchar(500) DEFAULT NULL,
    "dept_id" bigint DEFAULT NULL,
    "post_ids" varchar(255) DEFAULT NULL,
    "email" varchar(50) DEFAULT '',
    "mobile" varchar(11) DEFAULT '',
    "sex" tinyint DEFAULT 0,
    "avatar" varchar(100) DEFAULT '',
    "status" tinyint NOT NULL DEFAULT 0,
    "login_ip" varchar(50) DEFAULT '',
    "login_date" timestamp DEFAULT NULL,
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
) COMMENT '用户信息表';