CREATE TABLE IF NOT EXISTS mci_person (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_code VARCHAR(64) NOT NULL COMMENT '所属租户',
    person_code VARCHAR(64) COMMENT '业务人员编码',
    person_name VARCHAR(128) NOT NULL COMMENT '姓名',
    phone VARCHAR(32) COMMENT '手机号',
    person_type TINYINT NOT NULL DEFAULT 1 COMMENT '类型：1内部人员 2供应商人员 3客户 4司机',
    institution_id BIGINT COMMENT '所属机构/公司',
    supplier_id BIGINT COMMENT '供应商 ID',
    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_person_code (tenant_code, person_code, deleted),
    KEY idx_person_name (person_name),
    KEY idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='联系人表';

CREATE TABLE IF NOT EXISTS mci_channel_identity (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_code VARCHAR(64) NOT NULL COMMENT '所属租户',
    person_id BIGINT NOT NULL COMMENT '联系人 ID',
    channel_type VARCHAR(32) NOT NULL COMMENT '渠道：wecom / wechat_personal / dingtalk / feishu',
    channel_user_id VARCHAR(128) NOT NULL COMMENT '渠道用户 ID',
    channel_user_name VARCHAR(128) COMMENT '渠道昵称',
    subject_id BIGINT COMMENT '所属渠道主体 mci_channel_subject',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0禁用 1启用',
    ext_info JSON 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_user (tenant_code, channel_type, channel_user_id, deleted),
    KEY idx_person_id (person_id),
    KEY idx_subject_id (subject_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='渠道身份表';

CREATE TABLE IF NOT EXISTS mci_conversation (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_code VARCHAR(64) NOT NULL COMMENT '所属租户',
    conversation_type TINYINT NOT NULL DEFAULT 1 COMMENT '1单聊 2群聊',
    channel_type VARCHAR(32) NOT NULL COMMENT 'wecom / wechat_personal / dingtalk / feishu',
    channel_conversation_id VARCHAR(128) NOT NULL COMMENT '渠道会话 ID',
    owner_account_id BIGINT COMMENT '群主/所有者账号',
    subject_id BIGINT COMMENT '所属渠道主体',
    conversation_name VARCHAR(256) COMMENT '会话名称',
    create_source TINYINT NOT NULL DEFAULT 1 COMMENT '来源：1渠道同步 2API 创建 3手动创建',
    create_task_id BIGINT COMMENT '建群任务 ID',
    institution_id BIGINT COMMENT '关联机构',
    supplier_id BIGINT COMMENT '关联供应商',
    contract_id BIGINT COMMENT '关联合同',
    channel_code VARCHAR(64) COMMENT '业务渠道编码',
    receive_config JSON COMMENT '收消息配置',
    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_conv (tenant_code, channel_type, channel_conversation_id, deleted),
    KEY idx_owner_account (owner_account_id),
    KEY idx_subject_id (subject_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会话表';

CREATE TABLE IF NOT EXISTS mci_conversation_participant (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    conversation_id BIGINT NOT NULL COMMENT '会话 ID',
    person_id BIGINT COMMENT '人员 ID',
    channel_identity_id BIGINT COMMENT '渠道身份 ID',
    participant_type TINYINT NOT NULL DEFAULT 1 COMMENT '1成员 2群主 3管理员',
    join_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_conv_participant (conversation_id, person_id, channel_identity_id),
    KEY idx_conversation_id (conversation_id),
    KEY idx_person_id (person_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会话参与者表';

CREATE TABLE IF NOT EXISTS mci_channel_account_conversation (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    account_id BIGINT NOT NULL COMMENT '渠道账号 ID',
    conversation_id BIGINT NOT NULL COMMENT '会话 ID',
    binding_type TINYINT NOT NULL COMMENT '1发送主号 2发送备用号 3接收主号 4接收备用号',
    sort_order INT NOT NULL DEFAULT 0 COMMENT '优先级',
    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,
    UNIQUE KEY uk_account_conv_binding (account_id, conversation_id, binding_type),
    KEY idx_conversation_id (conversation_id),
    KEY idx_account_id (account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='账号-会话绑定表';

CREATE TABLE IF NOT EXISTS mci_tag (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_code VARCHAR(64) NOT NULL COMMENT '所属租户',
    parent_id BIGINT NOT NULL DEFAULT 0 COMMENT '父标签 ID，0 表示根标签',
    tag_path VARCHAR(512) NOT NULL COMMENT '标签路径，如 销售/销售一部/华北组',
    level TINYINT NOT NULL DEFAULT 1 COMMENT '层级',
    tag_name VARCHAR(128) NOT NULL COMMENT '标签名',
    tag_source VARCHAR(32) NOT NULL DEFAULT 'manual' COMMENT '来源：system / business_sync / manual',
    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_tag_path (tenant_code, tag_path, deleted),
    KEY idx_parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='标签表';

CREATE TABLE IF NOT EXISTS mci_person_tag_binding (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_code VARCHAR(64) NOT NULL COMMENT '所属租户',
    tag_id BIGINT NOT NULL COMMENT '标签 ID',
    person_id BIGINT NOT NULL COMMENT '联系人 ID',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_person_tag (tag_id, person_id),
    KEY idx_person_id (person_id),
    KEY idx_tag_id (tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='人员标签绑定表';

CREATE TABLE IF NOT EXISTS mci_conversation_tag_binding (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_code VARCHAR(64) NOT NULL COMMENT '所属租户',
    tag_id BIGINT NOT NULL COMMENT '标签 ID',
    conversation_id BIGINT NOT NULL COMMENT '会话 ID',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_conversation_tag (tag_id, conversation_id),
    KEY idx_conversation_id (conversation_id),
    KEY idx_tag_id (tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会话标签绑定表';

ALTER TABLE mci_channel_identity
    ADD CONSTRAINT fk_identity_person_id FOREIGN KEY (person_id) REFERENCES mci_person (id),
    ADD CONSTRAINT fk_identity_subject_id FOREIGN KEY (subject_id) REFERENCES mci_channel_subject (id);

ALTER TABLE mci_conversation
    ADD CONSTRAINT fk_conversation_owner_account FOREIGN KEY (owner_account_id) REFERENCES mci_channel_account (id),
    ADD CONSTRAINT fk_conversation_subject_id FOREIGN KEY (subject_id) REFERENCES mci_channel_subject (id);

ALTER TABLE mci_conversation_participant
    ADD CONSTRAINT fk_participant_conversation_id FOREIGN KEY (conversation_id) REFERENCES mci_conversation (id),
    ADD CONSTRAINT fk_participant_person_id FOREIGN KEY (person_id) REFERENCES mci_person (id),
    ADD CONSTRAINT fk_participant_identity_id FOREIGN KEY (channel_identity_id) REFERENCES mci_channel_identity (id);

ALTER TABLE mci_channel_account_conversation
    ADD CONSTRAINT fk_binding_account_id FOREIGN KEY (account_id) REFERENCES mci_channel_account (id),
    ADD CONSTRAINT fk_binding_conversation_id FOREIGN KEY (conversation_id) REFERENCES mci_conversation (id);

ALTER TABLE mci_person_tag_binding
    ADD CONSTRAINT fk_person_tag_tag_id FOREIGN KEY (tag_id) REFERENCES mci_tag (id),
    ADD CONSTRAINT fk_person_tag_person_id FOREIGN KEY (person_id) REFERENCES mci_person (id);

ALTER TABLE mci_conversation_tag_binding
    ADD CONSTRAINT fk_conv_tag_tag_id FOREIGN KEY (tag_id) REFERENCES mci_tag (id),
    ADD CONSTRAINT fk_conv_tag_conversation_id FOREIGN KEY (conversation_id) REFERENCES mci_conversation (id);
