企业微信CRM模块功能设计（三）—— 升鲜宝生鲜配送供应链管理系统源代码

-- =========================================================
-- 文件名: 升鲜宝_CRM_整合版_MySQL_DDL.sql
-- 说明:
--   1. 本脚本为“升鲜宝 CRM 整合版”DDL，基于现有升鲜宝供应链库进行增强。
--   2. 原则：复用现有 mall_shop / cus_customer_contract / fin_collection_order 等主链，
--      改造现有 crm_* 表，并新增 CRM 缺口表。
--   3. 本脚本默认 MySQL 8.0.36+。
--   4. 新表均为幂等创建（DROP/CREATE 或 CREATE IF NOT EXISTS），
--      现有表增强尽量使用 ADD COLUMN IF NOT EXISTS。
-- =========================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =========================================================
-- A. 现有表增强
-- =========================================================

-- ---------------------------------------------------------
-- A1. 客户主表（店铺/客户表）增强
-- 依据：现有 mall_shop 已含 advance_payment / credit_amount / customer_balance /
-- debt_amount / customer_source_id / customer_star_id / period_id 等字段。
-- ---------------------------------------------------------
ALTER TABLE `mall_shop`
  ADD COLUMN IF NOT EXISTS `credit_status` int NOT NULL DEFAULT '1' COMMENT '信用状态 0禁用 1正常 2预警 3冻结' AFTER `debt_amount`,
  ADD COLUMN IF NOT EXISTS `allow_over_credit` int NOT NULL DEFAULT '0' COMMENT '是否允许超授信 0否1是' AFTER `credit_status`,
  ADD COLUMN IF NOT EXISTS `allow_overdue_order` int NOT NULL DEFAULT '0' COMMENT '是否允许逾期下单 0否1是' AFTER `allow_over_credit`,
  ADD COLUMN IF NOT EXISTS `warning_ratio` decimal(10,2) NOT NULL DEFAULT '80.00' COMMENT '授信预警比例' AFTER `allow_overdue_order`,
  ADD COLUMN IF NOT EXISTS `last_collection_date` bigint DEFAULT NULL COMMENT '最后收款日期' AFTER `warning_ratio`,
  ADD COLUMN IF NOT EXISTS `last_bill_date` bigint DEFAULT NULL COMMENT '最后出账日期' AFTER `last_collection_date`;

-- ---------------------------------------------------------
-- A2. 线索表增强
-- ---------------------------------------------------------
ALTER TABLE `crm_lead`
  ADD COLUMN IF NOT EXISTS `follow_up_status` int DEFAULT '0' COMMENT '跟进状态 0新客 1待再次沟通 2有意向 3已加微信 4转商机 5无效' AFTER `status`,
  ADD COLUMN IF NOT EXISTS `contact_last_time` bigint DEFAULT NULL COMMENT '最后跟进时间' AFTER `follow_up_status`,
  ADD COLUMN IF NOT EXISTS `contact_last_content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '最后跟进内容' AFTER `contact_last_time`,
  ADD COLUMN IF NOT EXISTS `contact_next_time` bigint DEFAULT NULL COMMENT '下次联系时间' AFTER `contact_last_content`,
  ADD COLUMN IF NOT EXISTS `transform_status` int DEFAULT '0' COMMENT '转化状态 0未转化 1已转化' AFTER `contact_next_time`,
  ADD COLUMN IF NOT EXISTS `customer_id` bigint DEFAULT NULL COMMENT '转化后的客户id=mall_shop.id' AFTER `transform_status`,
  ADD COLUMN IF NOT EXISTS `telephone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '电话号码' AFTER `contact_phone`,
  ADD COLUMN IF NOT EXISTS `qq` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'QQ' AFTER `telephone`,
  ADD COLUMN IF NOT EXISTS `wechat` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '微信' AFTER `qq`,
  ADD COLUMN IF NOT EXISTS `email` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '邮箱' AFTER `wechat`,
  ADD COLUMN IF NOT EXISTS `area_id` bigint DEFAULT NULL COMMENT '地区id' AFTER `email`,
  ADD COLUMN IF NOT EXISTS `detail_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '详细地址' AFTER `area_id`,
  ADD COLUMN IF NOT EXISTS `industry_id` bigint DEFAULT NULL COMMENT '行业id' AFTER `detail_address`,
  ADD COLUMN IF NOT EXISTS `level` int DEFAULT NULL COMMENT '客户等级' AFTER `industry_id`,
  ADD COLUMN IF NOT EXISTS `source_id` bigint DEFAULT NULL COMMENT '客户来源id=cus_customer_source.id' AFTER `level`,
  ADD COLUMN IF NOT EXISTS `invalid_reason` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '无效原因' AFTER `source_id`,
  ADD COLUMN IF NOT EXISTS `lead_pool_status` int DEFAULT '0' COMMENT '线索池状态 0未入池 1已入池 2已领取' AFTER `invalid_reason`;

-- ---------------------------------------------------------
-- A3. 联系人表增强
-- ---------------------------------------------------------
ALTER TABLE `crm_contact`
  ADD COLUMN IF NOT EXISTS `contact_last_time` bigint DEFAULT NULL COMMENT '最后跟进时间' AFTER `shop_id`,
  ADD COLUMN IF NOT EXISTS `contact_last_content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '最后跟进内容' AFTER `contact_last_time`,
  ADD COLUMN IF NOT EXISTS `contact_next_time` bigint DEFAULT NULL COMMENT '下次联系时间' AFTER `contact_last_content`,
  ADD COLUMN IF NOT EXISTS `wechat` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '微信' AFTER `email`,
  ADD COLUMN IF NOT EXISTS `qq` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'QQ' AFTER `wechat`,
  ADD COLUMN IF NOT EXISTS `area_id` bigint DEFAULT NULL COMMENT '地区id' AFTER `qq`,
  ADD COLUMN IF NOT EXISTS `detail_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '详细地址' AFTER `area_id`,
  ADD COLUMN IF NOT EXISTS `sex` int DEFAULT NULL COMMENT '性别 0未知 1男 2女' AFTER `detail_address`,
  ADD COLUMN IF NOT EXISTS `master_flag` int NOT NULL DEFAULT '0' COMMENT '是否关键决策人 0否1是' AFTER `sex`,
  ADD COLUMN IF NOT EXISTS `parent_contact_id` bigint DEFAULT NULL COMMENT '直系上属联系人id' AFTER `master_flag`;

-- ---------------------------------------------------------
-- A4. 商机表增强
-- ---------------------------------------------------------
ALTER TABLE `crm_opportunity`
  ADD COLUMN IF NOT EXISTS `follow_up_status` int DEFAULT '0' COMMENT '跟进状态' AFTER `stage`,
  ADD COLUMN IF NOT EXISTS `contact_last_time` bigint DEFAULT NULL COMMENT '最后跟进时间' AFTER `follow_up_status`,
  ADD COLUMN IF NOT EXISTS `contact_next_time` bigint DEFAULT NULL COMMENT '下次联系时间' AFTER `contact_last_time`,
  ADD COLUMN IF NOT EXISTS `status_type_id` bigint DEFAULT NULL COMMENT '商机阶段组ID' AFTER `contact_next_time`,
  ADD COLUMN IF NOT EXISTS `status_id` bigint DEFAULT NULL COMMENT '商机阶段ID' AFTER `status_type_id`,
  ADD COLUMN IF NOT EXISTS `end_status` int DEFAULT NULL COMMENT '结束状态 1赢单 2输单 3无效' AFTER `status_id`,
  ADD COLUMN IF NOT EXISTS `deal_time` bigint DEFAULT NULL COMMENT '实际/预计成交时间' AFTER `end_status`,
  ADD COLUMN IF NOT EXISTS `total_product_price` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '产品总金额' AFTER `deal_time`,
  ADD COLUMN IF NOT EXISTS `discount_percent` decimal(10,2) NOT NULL DEFAULT '100.00' COMMENT '整单折扣百分比' AFTER `total_product_price`,
  ADD COLUMN IF NOT EXISTS `total_price` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '商机总金额' AFTER `discount_percent`,
  ADD COLUMN IF NOT EXISTS `source_lead_id` bigint DEFAULT NULL COMMENT '来源线索ID=crm_lead.id' AFTER `total_price`,
  ADD COLUMN IF NOT EXISTS `end_remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '结束备注' AFTER `source_lead_id`;

-- ---------------------------------------------------------
-- A5. 客户公海表增强
-- ---------------------------------------------------------
ALTER TABLE `crm_customer_pool`
  ADD COLUMN IF NOT EXISTS `owner_id` bigint DEFAULT NULL COMMENT '原负责人id' AFTER `shop_id`,
  ADD COLUMN IF NOT EXISTS `put_in_reason` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '进入公海原因' AFTER `added_at`,
  ADD COLUMN IF NOT EXISTS `receive_id` bigint DEFAULT NULL COMMENT '领取人id' AFTER `put_in_reason`,
  ADD COLUMN IF NOT EXISTS `receive_date` bigint DEFAULT NULL COMMENT '领取时间' AFTER `receive_id`,
  ADD COLUMN IF NOT EXISTS `pool_state` int NOT NULL DEFAULT '1' COMMENT '公海状态 1在公海 2已领取 3已回收' AFTER `receive_date`;

-- ---------------------------------------------------------
-- A6. 回款计划表增强
-- ---------------------------------------------------------
ALTER TABLE `crm_payment_plan`
  ADD COLUMN IF NOT EXISTS `customer_id` bigint DEFAULT NULL COMMENT '客户id=mall_shop.id' AFTER `contract_id`,
  ADD COLUMN IF NOT EXISTS `owner_id` bigint DEFAULT NULL COMMENT '负责人id' AFTER `customer_id`,
  ADD COLUMN IF NOT EXISTS `remind_days` int DEFAULT NULL COMMENT '提前提醒天数' AFTER `due_date`,
  ADD COLUMN IF NOT EXISTS `remind_date` bigint DEFAULT NULL COMMENT '提醒日期' AFTER `remind_days`,
  ADD COLUMN IF NOT EXISTS `received_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '已回款金额' AFTER `amount`,
  ADD COLUMN IF NOT EXISTS `unreceived_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '未回款金额' AFTER `received_amount`,
  ADD COLUMN IF NOT EXISTS `finance_collection_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '财务收款单号' AFTER `unreceived_amount`;

-- ---------------------------------------------------------
-- A7. 回款记录表增强
-- ---------------------------------------------------------
ALTER TABLE `crm_payment`
  ADD COLUMN IF NOT EXISTS `customer_id` bigint DEFAULT NULL COMMENT '客户id=mall_shop.id' AFTER `plan_id`,
  ADD COLUMN IF NOT EXISTS `contract_id` bigint DEFAULT NULL COMMENT '合同id=cus_customer_contract.id' AFTER `customer_id`,
  ADD COLUMN IF NOT EXISTS `owner_id` bigint DEFAULT NULL COMMENT '负责人id' AFTER `contract_id`,
  ADD COLUMN IF NOT EXISTS `audit_status` int DEFAULT '0' COMMENT '审核状态 0待审核 1已审核 -1作废' AFTER `received_date`,
  ADD COLUMN IF NOT EXISTS `finance_collection_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '财务收款单号' AFTER `audit_status`,
  ADD COLUMN IF NOT EXISTS `collection_state` int DEFAULT '0' COMMENT '收款状态 0待确认 1已确认' AFTER `finance_collection_code`,
  ADD COLUMN IF NOT EXISTS `payment_voucher_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '回款凭证' AFTER `collection_state`;

-- ---------------------------------------------------------
-- A8. 销售团队表增强
-- ---------------------------------------------------------
ALTER TABLE `crm_sales_team`
  ADD COLUMN IF NOT EXISTS `vice_leader_id` bigint DEFAULT NULL COMMENT '副负责人ID' AFTER `leader_id`,
  ADD COLUMN IF NOT EXISTS `warning_enabled` int NOT NULL DEFAULT '1' COMMENT '是否开启团队提醒 0否1是' AFTER `description`,
  ADD COLUMN IF NOT EXISTS `escalation_enabled` int NOT NULL DEFAULT '1' COMMENT '是否开启升级提醒 0否1是' AFTER `warning_enabled`,
  ADD COLUMN IF NOT EXISTS `leader_notice_mode` int NOT NULL DEFAULT '1' COMMENT '负责人通知方式 1站内 2企微 3两者' AFTER `escalation_enabled`,
  ADD COLUMN IF NOT EXISTS `team_target_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '团队销售目标金额' AFTER `leader_notice_mode`,
  ADD COLUMN IF NOT EXISTS `team_target_customer_count` int NOT NULL DEFAULT '0' COMMENT '团队新增客户目标数' AFTER `team_target_amount`,
  ADD COLUMN IF NOT EXISTS `team_target_visit_count` int NOT NULL DEFAULT '0' COMMENT '团队拜访目标数' AFTER `team_target_customer_count`;

-- ---------------------------------------------------------
-- A9. 团队成员表增强
-- ---------------------------------------------------------
ALTER TABLE `crm_sales_team_member`
  ADD COLUMN IF NOT EXISTS `member_role` int NOT NULL DEFAULT '1' COMMENT '成员角色 1销售 2招商主管 3区域经理 4团队助理' AFTER `user_id`,
  ADD COLUMN IF NOT EXISTS `direct_leader_id` bigint DEFAULT NULL COMMENT '直属上级ID' AFTER `member_role`,
  ADD COLUMN IF NOT EXISTS `notice_enabled` int NOT NULL DEFAULT '1' COMMENT '是否接收提醒 0否1是' AFTER `direct_leader_id`,
  ADD COLUMN IF NOT EXISTS `notice_level` int NOT NULL DEFAULT '1' COMMENT '提醒级别 1本人 2主管 3团队负责人' AFTER `notice_enabled`,
  ADD COLUMN IF NOT EXISTS `wecom_user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '企业微信用户ID' AFTER `notice_level`,
  ADD COLUMN IF NOT EXISTS `kpi_target_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '个人销售目标' AFTER `wecom_user_id`,
  ADD COLUMN IF NOT EXISTS `kpi_target_visit_count` int NOT NULL DEFAULT '0' COMMENT '个人拜访目标数' AFTER `kpi_target_amount`,
  ADD COLUMN IF NOT EXISTS `kpi_target_new_customer_count` int NOT NULL DEFAULT '0' COMMENT '个人新增客户目标数' AFTER `kpi_target_visit_count`;

-- ---------------------------------------------------------
-- A10. 任务表增强
-- ---------------------------------------------------------
ALTER TABLE `crm_task`
  ADD COLUMN IF NOT EXISTS `task_type` int DEFAULT '0' COMMENT '任务类型 1跟进 2拜访 3回款 4合同 5其他' AFTER `description`,
  ADD COLUMN IF NOT EXISTS `team_id` bigint DEFAULT NULL COMMENT '团队ID' AFTER `owner_id`,
  ADD COLUMN IF NOT EXISTS `direct_leader_id` bigint DEFAULT NULL COMMENT '直属上级ID' AFTER `team_id`,
  ADD COLUMN IF NOT EXISTS `remind_time` bigint DEFAULT NULL COMMENT '提醒时间' AFTER `due_date`,
  ADD COLUMN IF NOT EXISTS `remind_status` int DEFAULT '0' COMMENT '提醒状态 0未提醒 1已提醒' AFTER `remind_time`,
  ADD COLUMN IF NOT EXISTS `overdue_status` int DEFAULT '0' COMMENT '逾期状态 0未逾期 1已逾期' AFTER `remind_status`,
  ADD COLUMN IF NOT EXISTS `escalation_status` int DEFAULT '0' COMMENT '升级状态 0未升级 1已升级' AFTER `overdue_status`,
  ADD COLUMN IF NOT EXISTS `source_biz_type` int DEFAULT NULL COMMENT '来源业务类型' AFTER `escalation_status`,
  ADD COLUMN IF NOT EXISTS `source_biz_id` bigint DEFAULT NULL COMMENT '来源业务ID' AFTER `source_biz_type`;

-- ---------------------------------------------------------
-- A11. 拜访计划表增强
-- ---------------------------------------------------------
ALTER TABLE `crm_plan_visit`
  ADD COLUMN IF NOT EXISTS `team_id` bigint DEFAULT NULL COMMENT '团队ID' AFTER `salesman_id`,
  ADD COLUMN IF NOT EXISTS `owner_id` bigint DEFAULT NULL COMMENT '负责人ID' AFTER `team_id`,
  ADD COLUMN IF NOT EXISTS `remind_before_minutes` int DEFAULT '120' COMMENT '提前提醒分钟数' AFTER `plan_end_visit_date`,
  ADD COLUMN IF NOT EXISTS `sign_required` int NOT NULL DEFAULT '1' COMMENT '是否必须签到 0否1是' AFTER `remind_before_minutes`,
  ADD COLUMN IF NOT EXISTS `sign_timeout_minutes` int DEFAULT '30' COMMENT '签到超时分钟数' AFTER `sign_required`,
  ADD COLUMN IF NOT EXISTS `visit_result_status` int DEFAULT '0' COMMENT '拜访结果状态 0未完成 1已完成 2异常' AFTER `sign_timeout_minutes`,
  ADD COLUMN IF NOT EXISTS `overdue_status` int DEFAULT '0' COMMENT '逾期状态 0正常 1逾期' AFTER `visit_result_status`,
  ADD COLUMN IF NOT EXISTS `escalation_status` int DEFAULT '0' COMMENT '升级状态 0未升级 1已升级' AFTER `overdue_status`;

-- ---------------------------------------------------------
-- A12. 拜访执行表增强
-- ---------------------------------------------------------
ALTER TABLE `crm_customer_visit`
  ADD COLUMN IF NOT EXISTS `team_id` bigint DEFAULT NULL COMMENT '团队ID' AFTER `salesman_id`,
  ADD COLUMN IF NOT EXISTS `plan_id` bigint DEFAULT NULL COMMENT '拜访计划ID=crm_plan_visit.id' AFTER `team_id`,
  ADD COLUMN IF NOT EXISTS `next_follow_time` bigint DEFAULT NULL COMMENT '下次跟进时间' AFTER `visit_status`,
  ADD COLUMN IF NOT EXISTS `abnormal_flag` int NOT NULL DEFAULT '0' COMMENT '是否异常 0否1是' AFTER `next_follow_time`,
  ADD COLUMN IF NOT EXISTS `abnormal_reason` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '异常原因' AFTER `abnormal_flag`,
  ADD COLUMN IF NOT EXISTS `remind_status` int DEFAULT '0' COMMENT '提醒状态 0未提醒 1已提醒' AFTER `abnormal_reason`;

-- =========================================================
-- B. 新增表
-- =========================================================

-- ---------------------------------------------------------
-- B1. 客户画像扩展表
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `crm_customer_profile`;
CREATE TABLE `crm_customer_profile` (
  `id` bigint NOT NULL COMMENT '主键id',
  `customer_id` bigint NOT NULL COMMENT '客户id=mall_shop.id',
  `owner_id` bigint DEFAULT NULL COMMENT '负责人id',
  `owner_time` bigint DEFAULT NULL COMMENT '成为负责人时间',
  `follow_up_status` int NOT NULL DEFAULT '0' COMMENT '跟进状态',
  `contact_last_time` bigint DEFAULT NULL COMMENT '最后跟进时间',
  `contact_last_content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '最后跟进内容',
  `contact_next_time` bigint DEFAULT NULL COMMENT '下次联系时间',
  `lock_status` int NOT NULL DEFAULT '0' COMMENT '锁定状态 0否1是',
  `deal_status` int NOT NULL DEFAULT '0' COMMENT '成交状态 0否1是',
  `health_score` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '客户健康度',
  `loss_risk_level` int NOT NULL DEFAULT '0' COMMENT '流失风险等级 0无 1低 2中 3高',
  `wecom_external_user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '企业微信外部联系人ID',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_customer_id` (`customer_id`) USING BTREE,
  KEY `idx_owner_id` (`owner_id`) USING BTREE,
  KEY `idx_next_follow` (`contact_next_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM客户画像扩展表';

-- ---------------------------------------------------------
-- B2. 客户公海配置表
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `crm_customer_pool_config`;
CREATE TABLE `crm_customer_pool_config` (
  `id` bigint NOT NULL COMMENT '主键id',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '是否启用客户公海 0否1是',
  `contact_expire_days` int DEFAULT NULL COMMENT '未跟进放入公海天数',
  `deal_expire_days` int DEFAULT NULL COMMENT '未成交放入公海天数',
  `notify_enabled` int DEFAULT '0' COMMENT '是否开启提前提醒 0否1是',
  `notify_days` int DEFAULT NULL COMMENT '提前提醒天数',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM客户公海配置表';

-- ---------------------------------------------------------
-- B3. 客户限制配置表
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `crm_customer_limit_config`;
CREATE TABLE `crm_customer_limit_config` (
  `id` bigint NOT NULL COMMENT '主键id',
  `type` int NOT NULL COMMENT '规则类型 1拥有客户数限制 2锁定客户数限制',
  `user_ids` varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '规则适用人群，逗号分隔',
  `dept_ids` varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '规则适用部门，逗号分隔',
  `max_count` int NOT NULL COMMENT '数量上限',
  `deal_count_enabled` int DEFAULT '0' COMMENT '成交客户是否占有拥有客户数 0否1是',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM客户限制配置表';

-- ---------------------------------------------------------
-- B4. 客户跟进配置表
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `crm_follow_up_config`;
CREATE TABLE `crm_follow_up_config` (
  `id` bigint NOT NULL COMMENT '主键id',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '是否启用 0否1是',
  `follow_expire_days` int DEFAULT NULL COMMENT '超过多少天未跟进判定为超时',
  `notify_enabled` int DEFAULT '0' COMMENT '是否开启提醒 0否1是',
  `notify_days` int DEFAULT NULL COMMENT '提前提醒天数',
  `leader_escalation_enabled` int DEFAULT '1' COMMENT '是否开启主管升级提醒 0否1是',
  `leader_escalation_minutes` int DEFAULT NULL COMMENT '逾期多少分钟升级给主管',
  `team_escalation_enabled` int DEFAULT '1' COMMENT '是否开启团队负责人升级提醒 0否1是',
  `team_escalation_minutes` int DEFAULT NULL COMMENT '逾期多少分钟升级给团队负责人',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM客户跟进配置表';

-- ---------------------------------------------------------
-- B5. 线索池表
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `crm_lead_pool`;
CREATE TABLE `crm_lead_pool` (
  `id` bigint NOT NULL COMMENT '主键id',
  `lead_id` bigint NOT NULL COMMENT '线索id=crm_lead.id',
  `owner_id` bigint DEFAULT NULL COMMENT '原负责人id',
  `put_in_date` bigint DEFAULT NULL COMMENT '放入时间',
  `put_in_reason` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '放入原因',
  `receive_id` bigint DEFAULT NULL COMMENT '领取人id',
  `receive_date` bigint DEFAULT NULL COMMENT '领取时间',
  `pool_state` int NOT NULL DEFAULT '1' COMMENT '线索池状态 1在池中 2已领取 3已关闭',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_lead_id` (`lead_id`) USING BTREE,
  KEY `idx_owner_id` (`owner_id`) USING BTREE,
  KEY `idx_receive_id` (`receive_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM线索池表';

-- ---------------------------------------------------------
-- B6. 线索池配置表
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `crm_lead_pool_config`;
CREATE TABLE `crm_lead_pool_config` (
  `id` bigint NOT NULL COMMENT '主键id',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '是否启用线索池 0否1是',
  `contact_expire_days` int DEFAULT NULL COMMENT '未跟进进入线索池天数',
  `notify_enabled` int DEFAULT '0' COMMENT '是否开启提前提醒 0否1是',
  `notify_days` int DEFAULT NULL COMMENT '提前提醒天数',
  `receive_limit_count` int DEFAULT NULL COMMENT '单人每日领取上限',
  `repeat_receive_enabled` int DEFAULT '0' COMMENT '是否允许重复领取 0否1是',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM线索池配置表';

-- ---------------------------------------------------------
-- B7. 跟进记录表
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `crm_follow_up_record`;
CREATE TABLE `crm_follow_up_record` (
  `id` bigint NOT NULL COMMENT '主键id',
  `biz_type` int DEFAULT NULL COMMENT '业务类型 1线索 2客户 3联系人 4商机 5合同',
  `biz_id` bigint DEFAULT NULL COMMENT '业务id',
  `follow_type` int DEFAULT NULL COMMENT '跟进类型 1电话 2拜访 3微信 4短信 5邮件 6其他',
  `content` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '跟进内容',
  `next_time` bigint DEFAULT NULL COMMENT '下次联系时间',
  `pic_urls` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '图片URL，多个逗号分隔',
  `file_urls` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '附件URL，多个逗号分隔',
  `team_id` bigint DEFAULT NULL COMMENT '团队ID',
  `owner_id` bigint DEFAULT NULL COMMENT '负责人ID',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_biz_type_biz_id` (`biz_type`,`biz_id`) USING BTREE,
  KEY `idx_owner_id` (`owner_id`) USING BTREE,
  KEY `idx_next_time` (`next_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM跟进记录表';

DROP TABLE IF EXISTS `crm_follow_up_record_contact_rel`;
CREATE TABLE `crm_follow_up_record_contact_rel` (
  `id` bigint NOT NULL COMMENT '主键id',
  `record_id` bigint NOT NULL COMMENT '跟进记录id=crm_follow_up_record.id',
  `contact_id` bigint NOT NULL COMMENT '联系人id=crm_contact.id',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_record_contact` (`record_id`,`contact_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM跟进记录-联系人关系表';

DROP TABLE IF EXISTS `crm_follow_up_record_opportunity_rel`;
CREATE TABLE `crm_follow_up_record_opportunity_rel` (
  `id` bigint NOT NULL COMMENT '主键id',
  `record_id` bigint NOT NULL COMMENT '跟进记录id=crm_follow_up_record.id',
  `opportunity_id` bigint NOT NULL COMMENT '商机id=crm_opportunity.id',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_record_opportunity` (`record_id`,`opportunity_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM跟进记录-商机关系表';

-- ---------------------------------------------------------
-- B8. 商机阶段配置
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `crm_opportunity_stage_group`;
CREATE TABLE `crm_opportunity_stage_group` (
  `id` bigint NOT NULL COMMENT '主键id',
  `group_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '阶段组名称',
  `dept_ids` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '适用部门ID，逗号分隔',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_group_name` (`group_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM商机阶段组表';

DROP TABLE IF EXISTS `crm_opportunity_stage`;
CREATE TABLE `crm_opportunity_stage` (
  `id` bigint NOT NULL COMMENT '主键id',
  `group_id` bigint NOT NULL COMMENT '阶段组ID=crm_opportunity_stage_group.id',
  `stage_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '阶段名称',
  `percent` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '赢单率',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_group_stage_name` (`group_id`,`stage_name`) USING BTREE,
  KEY `idx_group_id` (`group_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM商机阶段表';

-- ---------------------------------------------------------
-- B9. 商机产品明细表
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `crm_opportunity_product`;
CREATE TABLE `crm_opportunity_product` (
  `id` bigint NOT NULL COMMENT '主键id',
  `opportunity_id` bigint NOT NULL COMMENT '商机id=crm_opportunity.id',
  `product_sku_id` bigint DEFAULT NULL COMMENT '商品规格id',
  `sku_unit_id` bigint DEFAULT NULL COMMENT '规格单位id',
  `unit_id` bigint DEFAULT NULL COMMENT '单位id',
  `product_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称快照',
  `spec_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '规格名称快照',
  `qty` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '数量',
  `unit_price` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '单价',
  `discount_percent` decimal(10,2) NOT NULL DEFAULT '100.00' COMMENT '折扣百分比',
  `total_price` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '金额',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_opportunity_id` (`opportunity_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM商机产品明细表';

-- ---------------------------------------------------------
-- B10. 联系人与商机关联表
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `crm_contact_opportunity_rel`;
CREATE TABLE `crm_contact_opportunity_rel` (
  `id` bigint NOT NULL COMMENT '主键id',
  `contact_id` bigint NOT NULL COMMENT '联系人id=crm_contact.id',
  `opportunity_id` bigint NOT NULL COMMENT '商机id=crm_opportunity.id',
  `role_type` int DEFAULT NULL COMMENT '关系角色 1决策人 2影响者 3使用者 4其他',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_contact_opportunity` (`contact_id`,`opportunity_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM联系人与商机关联表';

-- ---------------------------------------------------------
-- B11. 客户标签
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `crm_customer_tag`;
CREATE TABLE `crm_customer_tag` (
  `id` bigint NOT NULL COMMENT '主键id',
  `tag_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '标签名称',
  `tag_color` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '标签颜色',
  `tag_type` int DEFAULT '1' COMMENT '标签类型 1系统 2自定义',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_tag_name` (`tag_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM客户标签表';

DROP TABLE IF EXISTS `crm_customer_tag_rel`;
CREATE TABLE `crm_customer_tag_rel` (
  `id` bigint NOT NULL COMMENT '主键id',
  `customer_id` bigint NOT NULL COMMENT '客户id=mall_shop.id',
  `tag_id` bigint NOT NULL COMMENT '标签id=crm_customer_tag.id',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_customer_tag` (`customer_id`,`tag_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM客户标签关系表';

-- ---------------------------------------------------------
-- B12. 发票
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `crm_invoice`;
CREATE TABLE `crm_invoice` (
  `id` bigint NOT NULL COMMENT '主键id',
  `invoice_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '发票单号',
  `customer_id` bigint NOT NULL COMMENT '客户id=mall_shop.id',
  `contract_id` bigint DEFAULT NULL COMMENT '合同id=cus_customer_contract.id',
  `opportunity_id` bigint DEFAULT NULL COMMENT '商机id=crm_opportunity.id',
  `invoice_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '发票抬头',
  `invoice_tax_no` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '税号',
  `invoice_type` int DEFAULT '1' COMMENT '发票类型 1普票 2专票',
  `invoice_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '开票金额',
  `invoice_date` bigint DEFAULT NULL COMMENT '开票日期',
  `owner_id` bigint DEFAULT NULL COMMENT '负责人id',
  `bill_state` int NOT NULL DEFAULT '0' COMMENT '状态 0待开票 1已开票 -1作废',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_invoice_code` (`invoice_code`) USING BTREE,
  KEY `idx_customer_id` (`customer_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM发票表';

DROP TABLE IF EXISTS `crm_invoice_log`;
CREATE TABLE `crm_invoice_log` (
  `id` bigint NOT NULL COMMENT '主键id',
  `invoice_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '发票单号',
  `operate_type` int DEFAULT NULL COMMENT '操作类型 1新增 2修改 3开票 4作废',
  `operate_content` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '操作内容',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_invoice_code` (`invoice_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM发票日志表';

-- ---------------------------------------------------------
-- B13. 团队业绩目标
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `crm_team_target`;
CREATE TABLE `crm_team_target` (
  `id` bigint NOT NULL COMMENT '主键id',
  `team_id` bigint NOT NULL COMMENT '团队id=crm_sales_team.id',
  `target_year` int NOT NULL COMMENT '目标年份',
  `target_month` int NOT NULL COMMENT '目标月份',
  `target_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '销售目标金额',
  `target_new_customer_count` int NOT NULL DEFAULT '0' COMMENT '新增客户目标数',
  `target_visit_count` int NOT NULL DEFAULT '0' COMMENT '拜访目标数',
  `target_collection_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '回款目标金额',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_team_year_month` (`team_id`,`target_year`,`target_month`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM团队业绩目标表';

DROP TABLE IF EXISTS `crm_member_target`;
CREATE TABLE `crm_member_target` (
  `id` bigint NOT NULL COMMENT '主键id',
  `team_member_id` bigint NOT NULL COMMENT '团队成员id=crm_sales_team_member.id',
  `target_year` int NOT NULL COMMENT '目标年份',
  `target_month` int NOT NULL COMMENT '目标月份',
  `target_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '销售目标金额',
  `target_new_customer_count` int NOT NULL DEFAULT '0' COMMENT '新增客户目标数',
  `target_visit_count` int NOT NULL DEFAULT '0' COMMENT '拜访目标数',
  `target_collection_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '回款目标金额',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_member_year_month` (`team_member_id`,`target_year`,`target_month`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM成员业绩目标表';

-- ---------------------------------------------------------
-- B14. 提醒规则与消息中心
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `crm_reminder_rule`;
CREATE TABLE `crm_reminder_rule` (
  `id` bigint NOT NULL COMMENT '主键id',
  `rule_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '规则编码',
  `rule_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '规则名称',
  `biz_type` int NOT NULL COMMENT '业务类型 1线索 2客户 3商机 4任务 5拜访 6合同 7回款',
  `trigger_type` int NOT NULL COMMENT '触发类型 1到期前 2到期时 3超时后',
  `trigger_offset_minutes` int DEFAULT '0' COMMENT '触发偏移分钟数',
  `receiver_scope` int NOT NULL COMMENT '接收范围 1本人 2直属上级 3团队负责人 4指定角色',
  `channel_type` int NOT NULL COMMENT '发送渠道 1站内 2企微 3短信 4邮件 5全部',
  `repeat_enabled` int NOT NULL DEFAULT '0' COMMENT '是否重复提醒 0否1是',
  `repeat_interval_minutes` int DEFAULT NULL COMMENT '重复提醒间隔分钟',
  `escalation_enabled` int NOT NULL DEFAULT '0' COMMENT '是否开启升级 0否1是',
  `escalation_level_1_minutes` int DEFAULT NULL COMMENT '一级升级分钟数',
  `escalation_level_2_minutes` int DEFAULT NULL COMMENT '二级升级分钟数',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_rule_code` (`rule_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM提醒规则表';

DROP TABLE IF EXISTS `crm_reminder_message`;
CREATE TABLE `crm_reminder_message` (
  `id` bigint NOT NULL COMMENT '主键id',
  `biz_type` int NOT NULL COMMENT '业务类型',
  `biz_id` bigint NOT NULL COMMENT '业务id',
  `rule_id` bigint DEFAULT NULL COMMENT '规则id=crm_reminder_rule.id',
  `title` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '标题',
  `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '内容',
  `receiver_id` bigint NOT NULL COMMENT '接收人id',
  `receiver_role_type` int DEFAULT NULL COMMENT '接收角色类型',
  `channel_type` int NOT NULL COMMENT '发送渠道 1站内 2企微 3短信 4邮件',
  `send_status` int NOT NULL DEFAULT '0' COMMENT '发送状态 0待发送 1已发送 2发送失败',
  `read_status` int NOT NULL DEFAULT '0' COMMENT '阅读状态 0未读 1已读',
  `handle_status` int NOT NULL DEFAULT '0' COMMENT '处理状态 0未处理 1已处理',
  `send_date` bigint DEFAULT NULL COMMENT '发送时间',
  `read_date` bigint DEFAULT NULL COMMENT '阅读时间',
  `handle_date` bigint DEFAULT NULL COMMENT '处理时间',
  `expire_date` bigint DEFAULT NULL COMMENT '过期时间',
  `notice_id` bigint DEFAULT NULL COMMENT '站内通知id=sys_notice.id',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_receiver_send_status` (`receiver_id`,`send_status`) USING BTREE,
  KEY `idx_biz_type_biz_id` (`biz_type`,`biz_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM提醒消息表';

DROP TABLE IF EXISTS `crm_reminder_escalation_log`;
CREATE TABLE `crm_reminder_escalation_log` (
  `id` bigint NOT NULL COMMENT '主键id',
  `message_id` bigint NOT NULL COMMENT '消息id=crm_reminder_message.id',
  `biz_type` int NOT NULL COMMENT '业务类型',
  `biz_id` bigint NOT NULL COMMENT '业务id',
  `from_receiver_id` bigint DEFAULT NULL COMMENT '原接收人id',
  `to_receiver_id` bigint DEFAULT NULL COMMENT '升级接收人id',
  `escalation_level` int NOT NULL COMMENT '升级级别',
  `escalation_reason` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '升级原因',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_message_id` (`message_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM提醒升级日志表';

-- ---------------------------------------------------------
-- B15. 企业微信集成
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `crm_wecom_config`;
CREATE TABLE `crm_wecom_config` (
  `id` bigint NOT NULL COMMENT '主键id',
  `corp_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '企业ID',
  `corp_secret` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '企业应用密钥',
  `agent_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '应用ID',
  `token` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '回调Token',
  `aes_key` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '回调AESKey',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM企业微信配置表';

DROP TABLE IF EXISTS `crm_wecom_user`;
CREATE TABLE `crm_wecom_user` (
  `id` bigint NOT NULL COMMENT '主键id',
  `user_id` bigint NOT NULL COMMENT '系统用户id',
  `wecom_user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '企业微信用户ID',
  `user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户名称',
  `mobile` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机号',
  `dept_ids` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '部门ID列表',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_wecom_user_id` (`wecom_user_id`) USING BTREE,
  UNIQUE KEY `uk_sys_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM企业微信员工映射表';

DROP TABLE IF EXISTS `crm_wecom_customer`;
CREATE TABLE `crm_wecom_customer` (
  `id` bigint NOT NULL COMMENT '主键id',
  `customer_id` bigint NOT NULL COMMENT '客户id=mall_shop.id',
  `wecom_external_user_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '企业微信外部联系人ID',
  `external_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '外部联系人名称',
  `mobile` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机号',
  `owner_user_id` bigint DEFAULT NULL COMMENT '归属系统用户ID',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_external_user_id` (`wecom_external_user_id`) USING BTREE,
  KEY `idx_customer_id` (`customer_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='CRM企业微信客户映射表';

-- ---------------------------------------------------------
-- B16. 客户信用结算中心
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `cus_customer_credit_profile`;
CREATE TABLE `cus_customer_credit_profile` (
  `id` bigint NOT NULL COMMENT '主键id',
  `customer_id` bigint NOT NULL COMMENT '客户id=mall_shop.id',
  `period_id` bigint DEFAULT NULL COMMENT '默认账期id=cus_customer_period.id',
  `credit_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '授信总额',
  `credit_used_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '已用授信',
  `credit_freeze_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '冻结授信',
  `credit_available_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '可用授信',
  `customer_balance` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '当前余额',
  `debt_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '当前欠款',
  `overdue_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '逾期金额',
  `pending_receivable_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '待收金额',
  `advance_payment_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '期初预收/预付款',
  `last_bill_date` bigint DEFAULT NULL COMMENT '最后出账日期',
  `last_collection_date` bigint DEFAULT NULL COMMENT '最后收款日期',
  `allow_over_credit` int NOT NULL DEFAULT '0' COMMENT '是否允许超授信 0否1是',
  `allow_overdue_order` int NOT NULL DEFAULT '0' COMMENT '是否允许逾期下单 0否1是',
  `warning_ratio` decimal(10,2) NOT NULL DEFAULT '80.00' COMMENT '授信预警比例',
  `settlement_priority` int NOT NULL DEFAULT '1' COMMENT '结算优先级 1先余额后授信 2只余额 3只授信',
  `credit_status` int NOT NULL DEFAULT '1' COMMENT '信用状态 0禁用 1正常 2预警 3冻结',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_customer_id` (`customer_id`) USING BTREE,
  KEY `idx_period_id` (`period_id`) USING BTREE,
  KEY `idx_credit_status` (`credit_status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='客户信用结算档案表';

DROP TABLE IF EXISTS `cus_customer_credit_change_log`;
CREATE TABLE `cus_customer_credit_change_log` (
  `id` bigint NOT NULL COMMENT '主键id',
  `customer_id` bigint NOT NULL COMMENT '客户id',
  `before_credit_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '变更前授信总额',
  `after_credit_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '变更后授信总额',
  `change_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '变更金额',
  `change_type` int NOT NULL DEFAULT '0' COMMENT '变更类型 1调增 2调减 3初始化 4冻结 5解冻',
  `reason` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '变更原因',
  `source_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '来源单号',
  `source_type` int DEFAULT NULL COMMENT '来源类型 1手工调整 2审批 3系统初始化 4其他',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_customer_id` (`customer_id`) USING BTREE,
  KEY `idx_source_code` (`source_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='客户授信变更流水表';

DROP TABLE IF EXISTS `cus_customer_credit_occupy_log`;
CREATE TABLE `cus_customer_credit_occupy_log` (
  `id` bigint NOT NULL COMMENT '主键id',
  `customer_id` bigint NOT NULL COMMENT '客户id',
  `order_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '业务单号',
  `order_type` int DEFAULT NULL COMMENT '业务类型 1销售单 2出库单 3收款单 4退货单 5调账单',
  `record_type` int NOT NULL DEFAULT '1' COMMENT '记录类型 1占用 2释放 3冻结 4解冻',
  `amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '金额',
  `before_used_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '操作前已用授信',
  `after_used_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '操作后已用授信',
  `before_freeze_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '操作前冻结授信',
  `after_freeze_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '操作后冻结授信',
  `reason` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '原因',
  `business_date` bigint DEFAULT NULL COMMENT '业务日期',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_customer_order` (`customer_id`,`order_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='客户授信占用释放流水表';

DROP TABLE IF EXISTS `cus_customer_credit_init`;
CREATE TABLE `cus_customer_credit_init` (
  `id` bigint NOT NULL COMMENT '主键id',
  `customer_id` bigint NOT NULL COMMENT '客户id',
  `init_date` bigint DEFAULT NULL COMMENT '初始化日期',
  `init_balance_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '期初余额',
  `init_debt_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '期初欠款',
  `init_credit_amount` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '期初授信',
  `init_advance_payment` decimal(20,3) NOT NULL DEFAULT '0.000' COMMENT '期初预收',
  `init_remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '初始化说明',
  `sort_code` int NOT NULL DEFAULT '1' COMMENT '排序码',
  `del_flag` int NOT NULL DEFAULT '0' COMMENT '删除标记 0正常 1删除',
  `enabled` int NOT NULL DEFAULT '1' COMMENT '状态 0禁用 1正常',
  `create_date` bigint DEFAULT NULL COMMENT '创建时间',
  `creator` bigint NOT NULL DEFAULT '0' COMMENT '创建人id',
  `update_date` bigint DEFAULT NULL COMMENT '修改时间',
  `updater` bigint NOT NULL DEFAULT '0' COMMENT '修改人id',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `org_id` bigint DEFAULT '0' COMMENT '机构id',
  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_customer_init` (`customer_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='客户信用期初初始化表';

-- =========================================================
-- C. 站内通知建议
-- 说明:
--   现有 sys_notice / sys_notice_user 已可复用，此处不重复建表。
--   CRM 提醒消息生成后，可同步写 sys_notice / sys_notice_user。
-- =========================================================

SET FOREIGN_KEY_CHECKS = 1;