f22d14e439
- TPlusSyncMixin 统一 T+ 同步预留四列（可空+普通索引，唯一键待 IT 定键后升级） - 留痕表实体 ID 无外键冗余固化，删除保护由服务层承担 - init-dev.sql 同步六表 DDL，alembic_version 标记更新 Co-Authored-By: Claude Code <noreply@anthropic.com>
134 lines
9.3 KiB
SQL
134 lines
9.3 KiB
SQL
-- Running upgrade bfbda9c3191d -> e1133a7c670a
|
||
|
||
CREATE TABLE products (
|
||
id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT,
|
||
product_name VARCHAR(128) NOT NULL COMMENT '产品名称',
|
||
spec_model VARCHAR(128) NOT NULL COMMENT '规格型号（空串而非 NULL：参与组合唯一键，MySQL 组合索引对 NULL 不去重）' DEFAULT '',
|
||
unit VARCHAR(16) COMMENT '计量单位',
|
||
stock_quantity NUMERIC(14, 2) COMMENT '数量（T+ 同步原样存储，语义待 IT 确认）',
|
||
sale_price NUMERIC(14, 4) COMMENT '销售单价（元，T+ 同步）',
|
||
product_category VARCHAR(32) NOT NULL COMMENT '产品类别：municipal_water市政水利 rail_transit轨道交通（决定折算阈值 30%/20%；T+同步不覆盖）',
|
||
status TINYINT NOT NULL COMMENT '状态：1启用 0停用' DEFAULT '1',
|
||
remark VARCHAR(255) COMMENT '备注',
|
||
create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP,
|
||
update_time DATETIME NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
source_no VARCHAR(64) COMMENT 'T+ 原始单据号（同步幂等去重锚点；唯一键待 IT 定键后升级）',
|
||
t_plus_id VARCHAR(64) COMMENT 'T+ 系统内部标识',
|
||
sync_batch_no VARCHAR(32) COMMENT '同步批次号（同步模块写入）',
|
||
synced_at DATETIME COMMENT '最近同步时间',
|
||
CONSTRAINT pk_products PRIMARY KEY (id),
|
||
CONSTRAINT uk_products_name_spec UNIQUE (product_name, spec_model)
|
||
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='产品表（类别锚定折算阈值参数；T+同步不覆盖产品类别）' COLLATE utf8mb4_general_ci;
|
||
|
||
CREATE INDEX idx_products_source_no ON products (source_no);
|
||
|
||
CREATE TABLE ownership_change_logs (
|
||
id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT,
|
||
owner_type TINYINT NOT NULL COMMENT '归属类型：1客户归属 2业务归属',
|
||
customer_id BIGINT UNSIGNED COMMENT '客户ID（固化，owner_type=1 时非空）',
|
||
business_id BIGINT UNSIGNED COMMENT '业务ID（固化，owner_type=2 时非空）',
|
||
old_owner_personnel_id BIGINT UNSIGNED COMMENT '变更前负责营销人员ID（固化）',
|
||
new_owner_personnel_id BIGINT UNSIGNED COMMENT '变更后负责营销人员ID（固化）',
|
||
old_assistant_personnel_id BIGINT UNSIGNED COMMENT '变更前协助营销人员ID（固化，仅业务归属使用）',
|
||
new_assistant_personnel_id BIGINT UNSIGNED COMMENT '变更后协助营销人员ID（固化，仅业务归属使用）',
|
||
change_reason VARCHAR(255) COMMENT '变更原因',
|
||
operator_id BIGINT UNSIGNED COMMENT '操作人ID（账号删除后置空）',
|
||
operator_name VARCHAR(64) NOT NULL COMMENT '操作人用户名（冗余固化）',
|
||
create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT pk_ownership_change_logs PRIMARY KEY (id),
|
||
CONSTRAINT fk_ownership_change_logs_operator_id FOREIGN KEY(operator_id) REFERENCES users (id) ON DELETE SET NULL
|
||
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='归属变更记录表（append-only；客户/业务归属变更留痕，不回溯历史）' COLLATE utf8mb4_general_ci;
|
||
|
||
CREATE INDEX idx_ownership_change_logs_customer_id ON ownership_change_logs (customer_id);
|
||
|
||
CREATE INDEX idx_ownership_change_logs_business_id ON ownership_change_logs (business_id);
|
||
|
||
CREATE INDEX idx_ownership_change_logs_create_time ON ownership_change_logs (create_time);
|
||
|
||
CREATE TABLE personnel (
|
||
id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT,
|
||
personnel_name VARCHAR(64) NOT NULL COMMENT '姓名',
|
||
business_role_id BIGINT UNSIGNED NOT NULL COMMENT '岗位角色ID（决定计提公式模板；T+同步不覆盖）',
|
||
manager_id BIGINT UNSIGNED COMMENT '直属营销经理ID（NULL=经理本人或未分配；团队成员关系锚点）',
|
||
phone VARCHAR(20) COMMENT '联系电话',
|
||
hire_date DATE COMMENT '入职日期',
|
||
is_probation TINYINT NOT NULL COMMENT '是否试用期：1是 0否（试用期绩效照算不发放）' DEFAULT '0',
|
||
salary_standard NUMERIC(12, 2) COMMENT '月工资标准（元；T+同步不覆盖）',
|
||
status TINYINT NOT NULL COMMENT '状态：1在职 0离职（离职人员不可再被业务/客户归属引用）' DEFAULT '1',
|
||
remark VARCHAR(255) COMMENT '备注',
|
||
create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP,
|
||
update_time DATETIME NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
source_no VARCHAR(64) COMMENT 'T+ 原始单据号（同步幂等去重锚点；唯一键待 IT 定键后升级）',
|
||
t_plus_id VARCHAR(64) COMMENT 'T+ 系统内部标识',
|
||
sync_batch_no VARCHAR(32) COMMENT '同步批次号（同步模块写入）',
|
||
synced_at DATETIME COMMENT '最近同步时间',
|
||
CONSTRAINT pk_personnel PRIMARY KEY (id),
|
||
CONSTRAINT fk_personnel_business_role_id FOREIGN KEY(business_role_id) REFERENCES business_roles (id) ON DELETE RESTRICT,
|
||
CONSTRAINT fk_personnel_manager_id FOREIGN KEY(manager_id) REFERENCES personnel (id) ON DELETE RESTRICT
|
||
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='营销人员表（T+同步不覆盖岗位与工资标准）' COLLATE utf8mb4_general_ci;
|
||
|
||
CREATE INDEX idx_personnel_business_role_id ON personnel (business_role_id);
|
||
|
||
CREATE INDEX idx_personnel_manager_id ON personnel (manager_id);
|
||
|
||
CREATE INDEX idx_personnel_source_no ON personnel (source_no);
|
||
|
||
CREATE TABLE customers (
|
||
id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT,
|
||
company_name VARCHAR(128) NOT NULL COMMENT '公司名称',
|
||
owner_name VARCHAR(64) NOT NULL COMMENT '客户负责人姓名（客户公司负责人的人名，非系统人员）',
|
||
contact_person VARCHAR(64) COMMENT '联系人',
|
||
contact_phone VARCHAR(20) COMMENT '联系电话',
|
||
address VARCHAR(255) COMMENT '地址',
|
||
industry VARCHAR(64) COMMENT '所属行业（自由文本）',
|
||
customer_type VARCHAR(32) NOT NULL COMMENT '客户分类：personal个人户 manager_personal经理个人户 enterprise企业户（计提规则匹配锚点；T+同步不覆盖）' DEFAULT 'personal',
|
||
owner_personnel_id BIGINT UNSIGNED COMMENT '归属负责营销人员ID（手动修改不回溯历史，变更留痕见 ownership_change_logs）',
|
||
remark VARCHAR(255) COMMENT '备注',
|
||
create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP,
|
||
update_time DATETIME NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
source_no VARCHAR(64) COMMENT 'T+ 原始单据号（同步幂等去重锚点；唯一键待 IT 定键后升级）',
|
||
t_plus_id VARCHAR(64) COMMENT 'T+ 系统内部标识',
|
||
sync_batch_no VARCHAR(32) COMMENT '同步批次号（同步模块写入）',
|
||
synced_at DATETIME COMMENT '最近同步时间',
|
||
CONSTRAINT pk_customers PRIMARY KEY (id),
|
||
CONSTRAINT fk_customers_owner_personnel_id FOREIGN KEY(owner_personnel_id) REFERENCES personnel (id) ON DELETE RESTRICT,
|
||
CONSTRAINT uk_customers_company_owner UNIQUE (company_name, owner_name)
|
||
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='客户表（公司名称+负责人为业务唯一标识；T+同步不覆盖客户分类）' COLLATE utf8mb4_general_ci;
|
||
|
||
CREATE INDEX idx_customers_customer_type ON customers (customer_type);
|
||
|
||
CREATE INDEX idx_customers_owner_personnel_id ON customers (owner_personnel_id);
|
||
|
||
CREATE INDEX idx_customers_source_no ON customers (source_no);
|
||
|
||
CREATE TABLE personnel_plans (
|
||
id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT,
|
||
personnel_id BIGINT UNSIGNED NOT NULL COMMENT '人员ID（personnel.id）',
|
||
plan_period VARCHAR(32) NOT NULL COMMENT '计划期间（如 2026Q1、2026-05）',
|
||
plan_content VARCHAR(500) NOT NULL COMMENT '计划内容',
|
||
target_desc VARCHAR(255) COMMENT '指标说明（文字描述）',
|
||
source_no VARCHAR(64) COMMENT 'T+ 原始条目号（预留）',
|
||
create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP,
|
||
update_time DATETIME NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
CONSTRAINT pk_personnel_plans PRIMARY KEY (id),
|
||
CONSTRAINT fk_personnel_plans_personnel_id FOREIGN KEY(personnel_id) REFERENCES personnel (id) ON DELETE RESTRICT
|
||
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='市场拓展计划表' COLLATE utf8mb4_general_ci;
|
||
|
||
CREATE INDEX idx_personnel_plans_personnel_id ON personnel_plans (personnel_id);
|
||
|
||
CREATE TABLE personnel_regions (
|
||
id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT,
|
||
personnel_id BIGINT UNSIGNED NOT NULL COMMENT '人员ID（personnel.id）',
|
||
region_name VARCHAR(128) NOT NULL COMMENT '区域名称（产品辐射地理范围）',
|
||
remark VARCHAR(255) COMMENT '备注',
|
||
create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP,
|
||
update_time DATETIME NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
CONSTRAINT pk_personnel_regions PRIMARY KEY (id),
|
||
CONSTRAINT fk_personnel_regions_personnel_id FOREIGN KEY(personnel_id) REFERENCES personnel (id) ON DELETE RESTRICT
|
||
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='产品辐射区域表' COLLATE utf8mb4_general_ci;
|
||
|
||
CREATE INDEX idx_personnel_regions_personnel_id ON personnel_regions (personnel_id);
|
||
|
||
UPDATE alembic_version SET version_num='e1133a7c670a' WHERE alembic_version.version_num = 'bfbda9c3191d';
|
||
|