CREATE TABLE IF NOT EXISTS mci_channel_subject (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_code VARCHAR(64) COMMENT '所属租户，为空表示平台级共享主体',
    channel_type VARCHAR(32) NOT NULL COMMENT '渠道类型：wecom / dingtalk / feishu',
    subject_code VARCHAR(64) NOT NULL COMMENT '主体编码',
    subject_name VARCHAR(128) NOT NULL COMMENT '主体名称',
    corp_id VARCHAR(128) COMMENT '企微 corp_id（企微用）',
    corp_secret VARCHAR(512) COMMENT '通讯录 secret（企微用）',
    agent_id VARCHAR(64) COMMENT '应用 agent_id（企微用）',
    agent_secret VARCHAR(512) COMMENT '应用 secret（企微用）',
    session_archive_enabled TINYINT NOT NULL DEFAULT 0 COMMENT '是否开通会话存档能力：0否 1是',
    session_archive_private_key TEXT COMMENT '会话存档 RSA 私钥',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0禁用 1启用',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT NOT NULL DEFAULT 0,
    UNIQUE KEY uk_tenant_channel_subject (tenant_code, channel_type, subject_code, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='渠道主体表';

CREATE TABLE IF NOT EXISTS mci_channel_account (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_code VARCHAR(64) NOT NULL COMMENT '所属租户',
    account_type TINYINT NOT NULL COMMENT '1企微内部账号 2企微应用 3pywechat个人号 4钉钉机器人 5飞书机器人',
    channel_type VARCHAR(32) NOT NULL COMMENT 'wecom / wechat_personal / dingtalk / feishu',
    subject_id BIGINT COMMENT '所属渠道主体 mci_channel_subject',
    account_id VARCHAR(128) COMMENT '账号唯一标识：企微员工=userid，企微应用=agent_id，个微=wxid/bot_code，机器人=robot_id',
    account_name VARCHAR(128) COMMENT '账号名称',
    session_archive_enabled TINYINT NOT NULL DEFAULT 0 COMMENT '是否开启会话存档（企微内部账号用）',
    server_host VARCHAR(128) COMMENT '所在服务器（pywechat 用）',
    login_status TINYINT NOT NULL DEFAULT 0 COMMENT '登录状态：0未登录 1登录中 2已登录',
    online_status TINYINT NOT NULL DEFAULT 0 COMMENT '在线状态：0离线 1在线',
    risk_level TINYINT NOT NULL DEFAULT 0 COMMENT '风险等级',
    ext_info JSON COMMENT '渠道扩展字段',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0禁用 1启用',
    last_heartbeat DATETIME COMMENT '最后心跳',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT NOT NULL DEFAULT 0,
    UNIQUE KEY uk_tenant_channel_account (tenant_code, channel_type, account_id, deleted),
    KEY idx_subject_id (subject_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='渠道账号表';

ALTER TABLE mci_channel_account
    ADD CONSTRAINT fk_channel_account_subject_id FOREIGN KEY (subject_id) REFERENCES mci_channel_subject (id);
