CrossBorder_OS/backend/sql/init.sql

-- =============================================
-- OMS Database Initialization Script
-- =============================================
-- Create database
CREATE DATABASE IF NOT EXISTS oms DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE oms;

-- =============================================
-- 1. 组织架构 (sys_user, sys_department, sys_role, sys_api_key, sys_operation_log, sys_notification, sys_message_template, sys_approval_flow)
-- =============================================

CREATE TABLE sys_user (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    username VARCHAR(128) NOT NULL COMMENT '用户名',
    password_hash VARCHAR(256) NOT NULL COMMENT '密码哈希',
    email VARCHAR(256) COMMENT '邮箱',
    phone VARCHAR(64) COMMENT '手机号',
    avatar VARCHAR(512) COMMENT '头像URL',
    name VARCHAR(128) NOT NULL COMMENT '姓名',
    role VARCHAR(32) NOT NULL COMMENT '角色',
    role_label VARCHAR(64) COMMENT '角色标签',
    workspace VARCHAR(128) COMMENT '工作区',
    department_id BIGINT COMMENT '部门ID',
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
    last_login_at DATETIME COMMENT '最后登录时间',
    last_login_ip VARCHAR(64) COMMENT '最后登录IP',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_sys_user_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统用户表';

CREATE TABLE sys_department (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(128) NOT NULL COMMENT '部门名称',
    parent_id BIGINT COMMENT '父部门ID',
    leader_id BIGINT COMMENT '负责人ID',
    leader_name VARCHAR(128) COMMENT '负责人姓名',
    description VARCHAR(512) COMMENT '部门描述',
    sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='部门表';

CREATE TABLE sys_role (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(128) NOT NULL COMMENT '角色名称',
    description VARCHAR(512) COMMENT '角色描述',
    permissions JSON COMMENT '权限列表',
    bound_user_count INT NOT NULL DEFAULT 0 COMMENT '绑定用户数',
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统角色表';

CREATE TABLE sys_api_key (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(128) NOT NULL COMMENT 'Key名称',
    `system` VARCHAR(64) NOT NULL COMMENT '所属系统',
    scope VARCHAR(512) COMMENT '权限范围',
    key_value VARCHAR(256) NOT NULL COMMENT 'Key值',
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
    expire_at DATETIME COMMENT '过期时间',
    last_used_at DATETIME COMMENT '最后使用时间',
    ip_whitelist VARCHAR(512) COMMENT 'IP白名单',
    remark VARCHAR(1024) COMMENT '备注',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API密钥表';

CREATE TABLE sys_operation_log (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    module VARCHAR(64) NOT NULL COMMENT '模块',
    type VARCHAR(64) NOT NULL COMMENT '操作类型',
    object_id VARCHAR(64) COMMENT '对象ID',
    actor VARCHAR(128) NOT NULL COMMENT '操作人',
    actor_role VARCHAR(64) COMMENT '操作人角色',
    action VARCHAR(256) NOT NULL COMMENT '操作描述',
    result VARCHAR(64) COMMENT '操作结果',
    source_ip VARCHAR(64) COMMENT '来源IP',
    before_value TEXT COMMENT '变更前值',
    after_value TEXT COMMENT '变更后值',
    remark TEXT COMMENT '备注',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统操作日志表';

CREATE TABLE sys_notification (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    type VARCHAR(32) NOT NULL COMMENT '通知类型',
    title VARCHAR(256) NOT NULL COMMENT '通知标题',
    content TEXT COMMENT '通知内容',
    is_read TINYINT(1) NOT NULL DEFAULT 0 COMMENT '已读标记',
    read_at DATETIME COMMENT '阅读时间',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统通知表';

CREATE TABLE sys_message_template (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(128) NOT NULL COMMENT '模板名称',
    type VARCHAR(32) NOT NULL COMMENT '模板类型',
    channel VARCHAR(64) NOT NULL COMMENT '渠道',
    content TEXT NOT NULL COMMENT '模板内容',
    variables JSON COMMENT '变量列表',
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='消息模板表';

CREATE TABLE sys_approval_flow (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(128) NOT NULL COMMENT '流程名称',
    type VARCHAR(64) NOT NULL COMMENT '流程类型',
    nodes JSON NOT NULL COMMENT '流程节点',
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='审批流程表';

-- =============================================
-- 2. 商品中心
-- =============================================

CREATE TABLE product_category (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(128) NOT NULL COMMENT '类目名称',
    parent_id VARCHAR(64) COMMENT '父类目ID',
    level INT NOT NULL DEFAULT 0 COMMENT '层级',
    path VARCHAR(512) COMMENT '路径',
    sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品类目表';

CREATE TABLE product (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    spu VARCHAR(128) NOT NULL COMMENT 'SPU编码',
    title VARCHAR(256) NOT NULL COMMENT '商品标题',
    subtitle VARCHAR(512) COMMENT '副标题',
    category_id BIGINT NOT NULL COMMENT '类目ID',
    brand VARCHAR(128) COMMENT '品牌',
    tags JSON COMMENT '标签列表',
    description TEXT COMMENT '商品描述',
    specs JSON COMMENT '规格列表',
    translations JSON COMMENT '翻译列表',
    channel_status VARCHAR(32) NOT NULL DEFAULT 'INACTIVE' COMMENT '渠道状态',
    status VARCHAR(32) NOT NULL DEFAULT 'DRAFT' COMMENT '商品状态',
    owner VARCHAR(128) COMMENT '负责人',
    sku_count INT NOT NULL DEFAULT 0 COMMENT 'SKU数量',
    image VARCHAR(512) COMMENT '主图URL',
    images JSON COMMENT '图片列表',
    videos JSON COMMENT '视频列表',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_product_spu (spu)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品SPU表';

CREATE TABLE product_sku (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    product_id BIGINT NOT NULL COMMENT '商品ID',
    sku VARCHAR(128) NOT NULL COMMENT 'SKU编码',
    spec_combo VARCHAR(512) NOT NULL COMMENT '规格组合',
    barcode VARCHAR(128) COMMENT '条形码',
    cost_price DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '成本价',
    suggest_price DECIMAL(20,4) COMMENT '建议零售价',
    weight DECIMAL(10,3) COMMENT '重量(克)',
    length DECIMAL(10,2) COMMENT '长度(cm)',
    width DECIMAL(10,2) COMMENT '宽度(cm)',
    height DECIMAL(10,2) COMMENT '高度(cm)',
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT 'SKU状态',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_product_sku_combo (product_id, sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品SKU表';

CREATE TABLE channel_mapping (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    product_id BIGINT NOT NULL COMMENT '商品ID',
    sku_id BIGINT NOT NULL COMMENT 'SKU ID',
    channel_id BIGINT NOT NULL COMMENT '渠道ID',
    internal_sku VARCHAR(128) NOT NULL COMMENT '内部SKU',
    channel_sku VARCHAR(256) NOT NULL COMMENT '渠道SKU',
    shop_name VARCHAR(256) COMMENT '店铺名称',
    channel_category VARCHAR(256) COMMENT '渠道类目',
    mapping_status VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '映射状态',
    validate_status VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '校验状态',
    last_sync_at DATETIME COMMENT '最后同步时间',
    last_validate_result VARCHAR(512) COMMENT '最后校验结果',
    channel_title VARCHAR(512) COMMENT '渠道商品标题',
    channel_description TEXT COMMENT '渠道商品描述',
    channel_price DECIMAL(20,4) COMMENT '渠道价格',
    channel_image VARCHAR(512) COMMENT '渠道商品图片',
    attribute_mappings JSON COMMENT '属性映射',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_mapping_channel_sku (channel_id, internal_sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='渠道商品映射表';

CREATE TABLE pricing_rule (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    sku_id BIGINT NOT NULL COMMENT 'SKU ID',
    product_title VARCHAR(256) NOT NULL COMMENT '商品标题',
    currency VARCHAR(8) NOT NULL DEFAULT 'USD' COMMENT '币种',
    base_price DECIMAL(20,4) NOT NULL COMMENT '基础价格',
    channel_price DECIMAL(20,4) COMMENT '渠道价格',
    discount_price DECIMAL(20,4) COMMENT '折扣价格',
    channel_adjust_rule VARCHAR(128) COMMENT '渠道调价规则',
    exchange_source VARCHAR(64) COMMENT '汇率来源',
    warehouse_id BIGINT COMMENT '仓库ID',
    safe_stock_threshold INT COMMENT '安全库存阈值',
    warning_recipients VARCHAR(512) COMMENT '预警接收人',
    effective_time DATETIME NOT NULL COMMENT '生效时间',
    expire_time DATETIME COMMENT '失效时间',
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '规则状态',
    modified_by VARCHAR(64) COMMENT '修改人',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='定价规则表';

-- =============================================
-- 3. 渠道中心
-- =============================================

CREATE TABLE channel (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    channel_code VARCHAR(64) NOT NULL COMMENT '渠道编码',
    channel_name VARCHAR(128) NOT NULL COMMENT '渠道名称',
    shop_name VARCHAR(256) COMMENT '店铺名称',
    app_key VARCHAR(256) COMMENT '应用Key',
    app_secret VARCHAR(256) COMMENT '应用密钥',
    access_token VARCHAR(512) COMMENT '访问令牌',
    refresh_token VARCHAR(512) COMMENT '刷新令牌',
    token_expire_at DATETIME COMMENT '令牌过期时间',
    token_status VARCHAR(32) NOT NULL DEFAULT 'INVALID' COMMENT '令牌状态',
    webhook_url VARCHAR(512) COMMENT 'Webhook地址',
    webhook_secret VARCHAR(256) COMMENT 'Webhook密钥',
    sync_enabled TINYINT(1) NOT NULL DEFAULT 1 COMMENT '同步启用状态',
    sync_status VARCHAR(32) NOT NULL DEFAULT 'IDLE' COMMENT '同步状态',
    last_sync_at DATETIME COMMENT '最后同步时间',
    error_message VARCHAR(1024) COMMENT '错误信息',
    default_warehouse_id VARCHAR(64) COMMENT '默认仓库ID',
    remark VARCHAR(1024) COMMENT '备注',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_channel_code (channel_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='销售渠道表';

-- =============================================
-- 4. 仓库物流
-- =============================================

CREATE TABLE warehouse (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(128) NOT NULL COMMENT '仓库名称',
    type VARCHAR(32) NOT NULL COMMENT '仓库类型',
    address VARCHAR(512) NOT NULL COMMENT '仓库地址',
    contact VARCHAR(128) COMMENT '联系人',
    phone VARCHAR(64) COMMENT '联系电话',
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '仓库状态',
    manager VARCHAR(128) COMMENT '仓库管理员',
    remark VARCHAR(1024) COMMENT '备注',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='仓库表';

CREATE TABLE logistics_provider (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(128) NOT NULL COMMENT '物流商名称',
    channels JSON COMMENT '支持的渠道列表',
    billing_type VARCHAR(32) NOT NULL COMMENT '计费方式',
    tracking_url VARCHAR(512) COMMENT '追踪URL模板',
    contact VARCHAR(128) COMMENT '联系人',
    phone VARCHAR(64) COMMENT '联系电话',
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '物流商状态',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='物流商表';

CREATE TABLE shipping_template (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(128) NOT NULL COMMENT '模板名称',
    carrier_id BIGINT NOT NULL COMMENT '物流商ID',
    carrier_name VARCHAR(128) NOT NULL COMMENT '物流商名称',
    billing_type VARCHAR(32) NOT NULL COMMENT '计费方式',
    first_weight DECIMAL(10,3) NOT NULL DEFAULT 0 COMMENT '首重',
    first_cost DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '首费',
    continue_weight DECIMAL(10,3) NOT NULL DEFAULT 0 COMMENT '续重',
    continue_cost DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '续费',
    remote_surcharge DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '偏远附加费',
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '模板状态',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='运费模板表';

CREATE TABLE return_package (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    return_no VARCHAR(64) NOT NULL COMMENT '退件编号',
    original_tracking_no VARCHAR(256) COMMENT '原运单号',
    reason VARCHAR(1024) COMMENT '退件原因',
    status VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '处理状态',
    warehouse_id BIGINT NOT NULL COMMENT '仓库ID',
    handle_result VARCHAR(32) COMMENT '处理结果',
    images JSON COMMENT '图片列表',
    remark VARCHAR(1024) COMMENT '备注',
    handler_id BIGINT COMMENT '处理人ID',
    handler_name VARCHAR(128) COMMENT '处理人姓名',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_return_no (return_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='退件表';

-- =============================================
-- 5. 库存中心
-- =============================================

CREATE TABLE inventory (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    sku_id BIGINT NOT NULL COMMENT 'SKU ID',
    product_title VARCHAR(256) NOT NULL COMMENT '商品标题',
    warehouse_id BIGINT NOT NULL COMMENT '仓库ID',
    available INT NOT NULL DEFAULT 0 COMMENT '可用库存',
    locked INT NOT NULL DEFAULT 0 COMMENT '锁定库存',
    inbound INT NOT NULL DEFAULT 0 COMMENT '在途库存',
    safe_stock INT NOT NULL DEFAULT 0 COMMENT '安全库存',
    warning_status VARCHAR(32) NOT NULL DEFAULT 'NORMAL' COMMENT '预警状态',
    last_change_at DATETIME COMMENT '最后变动时间',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_inventory_sku_warehouse (sku_id, warehouse_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='库存表';

CREATE TABLE inventory_log (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    sku_id BIGINT NOT NULL COMMENT 'SKU ID',
    warehouse_id BIGINT NOT NULL COMMENT '仓库ID',
    source VARCHAR(64) NOT NULL COMMENT '变动来源',
    related_order_id BIGINT COMMENT '关联订单ID',
    related_order_no VARCHAR(64) COMMENT '关联订单号',
    operator VARCHAR(128) NOT NULL COMMENT '操作人',
    change_type VARCHAR(32) NOT NULL COMMENT '变动类型',
    quantity INT NOT NULL COMMENT '变动数量',
    before_qty INT NOT NULL COMMENT '变动前数量',
    after_qty INT NOT NULL COMMENT '变动后数量',
    reason VARCHAR(512) COMMENT '变动原因',
    remark VARCHAR(1024) COMMENT '备注',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='库存流水表';

-- =============================================
-- 6. 供应商采购
-- =============================================

CREATE TABLE supplier (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(128) NOT NULL COMMENT '供应商名称',
    company_name VARCHAR(256) COMMENT '公司名称',
    contact VARCHAR(128) COMMENT '联系人',
    phone VARCHAR(64) COMMENT '联系电话',
    email VARCHAR(256) COMMENT '邮箱',
    address VARCHAR(512) COMMENT '地址',
    bank_info VARCHAR(512) COMMENT '银行信息',
    tax_no VARCHAR(128) COMMENT '税号',
    contract_no VARCHAR(128) COMMENT '合同编号',
    settlement_type VARCHAR(32) NOT NULL DEFAULT 'CREDIT' COMMENT '结算方式',
    rating VARCHAR(16) COMMENT '评级',
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '供应商状态',
    related_sku_count INT NOT NULL DEFAULT 0 COMMENT '关联SKU数量',
    remark VARCHAR(1024) COMMENT '备注',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='供应商表';

CREATE TABLE supply_capability (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    supplier_id BIGINT NOT NULL COMMENT '供应商ID',
    sku_id BIGINT NOT NULL COMMENT 'SKU ID',
    product_title VARCHAR(256) NOT NULL COMMENT '商品标题',
    lead_time INT NOT NULL DEFAULT 0 COMMENT '交期(天)',
    moq INT NOT NULL DEFAULT 1 COMMENT '最小起订量',
    unit VARCHAR(32) NOT NULL DEFAULT 'PCS' COMMENT '单位',
    tier_prices JSON COMMENT '阶梯价格',
    is_default TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否默认',
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='供货能力表';

CREATE TABLE purchase_order (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    po_no VARCHAR(64) NOT NULL COMMENT '采购单号',
    supplier_id BIGINT NOT NULL COMMENT '供应商ID',
    warehouse_id BIGINT NOT NULL COMMENT '仓库ID',
    sku_count INT NOT NULL DEFAULT 0 COMMENT 'SKU数量',
    amount DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '采购金额',
    currency VARCHAR(8) NOT NULL DEFAULT 'CNY' COMMENT '币种',
    tax_amount DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '税额',
    freight DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '运费',
    expected_date DATE COMMENT '预计到货日期',
    arrival_progress VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '到货进度',
    status VARCHAR(32) NOT NULL DEFAULT 'DRAFT' COMMENT '采购单状态',
    remark VARCHAR(1024) COMMENT '备注',
    creator_id BIGINT NOT NULL COMMENT '创建人ID',
    creator_name VARCHAR(128) COMMENT '创建人姓名',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_po_no (po_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购单表';

CREATE TABLE purchase_order_item (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    purchase_order_id BIGINT NOT NULL COMMENT '采购单ID',
    sku_id BIGINT NOT NULL COMMENT 'SKU ID',
    sku VARCHAR(128) NOT NULL COMMENT 'SKU编码',
    product_title VARCHAR(256) NOT NULL COMMENT '商品标题',
    qty INT NOT NULL COMMENT '采购数量',
    price DECIMAL(20,4) NOT NULL COMMENT '采购单价',
    subtotal DECIMAL(20,4) NOT NULL COMMENT '小计金额',
    arrived_qty INT NOT NULL DEFAULT 0 COMMENT '已到货数量',
    qualified_qty INT NOT NULL DEFAULT 0 COMMENT '合格数量',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购单明细表';

CREATE TABLE purchase_arrival (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    purchase_order_id BIGINT NOT NULL COMMENT '采购单ID',
    arrival_no VARCHAR(64) NOT NULL COMMENT '到货单号',
    arrived_at DATETIME NOT NULL COMMENT '到货时间',
    warehouse_id BIGINT NOT NULL COMMENT '仓库ID',
    operator_id BIGINT NOT NULL COMMENT '操作人ID',
    operator_name VARCHAR(128) COMMENT '操作人姓名',
    remark VARCHAR(1024) COMMENT '备注',
    status VARCHAR(32) NOT NULL DEFAULT 'ARRIVED' COMMENT '状态',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_arrival_no (arrival_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='到货确认表';

CREATE TABLE purchase_arrival_item (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    arrival_id BIGINT NOT NULL COMMENT '到货单ID',
    purchase_order_item_id BIGINT NOT NULL COMMENT '采购单明细ID',
    sku_id BIGINT NOT NULL COMMENT 'SKU ID',
    expected_qty INT NOT NULL COMMENT '预期数量',
    actual_qty INT NOT NULL COMMENT '实际数量',
    qualified_qty INT NOT NULL DEFAULT 0 COMMENT '合格数量',
    unqualified_qty INT NOT NULL DEFAULT 0 COMMENT '不合格数量',
    iqc_status VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '质检状态',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='到货明细表';

CREATE TABLE iqc (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    iqc_no VARCHAR(64) NOT NULL COMMENT '质检单号',
    supplier_id BIGINT NOT NULL COMMENT '供应商ID',
    arrival_id BIGINT NOT NULL COMMENT '到货单ID',
    arrival_no VARCHAR(64) NOT NULL COMMENT '到货单号',
    standard VARCHAR(256) COMMENT '质检标准',
    qualified_qty INT NOT NULL DEFAULT 0 COMMENT '合格数量',
    unqualified_qty INT NOT NULL DEFAULT 0 COMMENT '不合格数量',
    result VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '质检结果',
    inspector_id BIGINT COMMENT '质检员ID',
    inspector_name VARCHAR(128) COMMENT '质检员姓名',
    inspect_time DATETIME COMMENT '质检时间',
    remark VARCHAR(1024) COMMENT '备注',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_iqc_no (iqc_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='来料质检表';

CREATE TABLE replenishment_plan (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    sku_id BIGINT NOT NULL COMMENT 'SKU ID',
    product_title VARCHAR(256) NOT NULL COMMENT '商品标题',
    warehouse_id BIGINT COMMENT '仓库ID',
    avg_daily_sales DECIMAL(12,4) NOT NULL DEFAULT 0 COMMENT '日均销量',
    safe_stock INT NOT NULL DEFAULT 0 COMMENT '安全库存',
    current_stock INT NOT NULL DEFAULT 0 COMMENT '当前库存',
    on_the_way INT NOT NULL DEFAULT 0 COMMENT '在途库存',
    suggested_qty INT NOT NULL DEFAULT 0 COMMENT '建议补货量',
    suggested_supplier_id VARCHAR(64) COMMENT '建议供应商ID',
    suggested_supplier_name VARCHAR(128) COMMENT '建议供应商名称',
    status VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '计划状态',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='备货计划表';

CREATE TABLE purchase_request (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    request_no VARCHAR(64) NOT NULL COMMENT '需求单号',
    applicant_id BIGINT NOT NULL COMMENT '申请人ID',
    applicant_name VARCHAR(128) NOT NULL COMMENT '申请人姓名',
    sku_id BIGINT NOT NULL COMMENT 'SKU ID',
    sku VARCHAR(128) NOT NULL COMMENT 'SKU编码',
    qty INT NOT NULL COMMENT '需求数量',
    reason VARCHAR(1024) COMMENT '需求原因',
    urgency VARCHAR(32) NOT NULL DEFAULT 'NORMAL' COMMENT '紧急程度',
    status VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '需求状态',
    related_order_id BIGINT COMMENT '关联订单ID',
    remark VARCHAR(1024) COMMENT '备注',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_request_no (request_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购需求表';

-- =============================================
-- 7. 订单中心
-- =============================================

CREATE TABLE orders (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    order_no VARCHAR(64) NOT NULL COMMENT '订单号',
    channel_order_no VARCHAR(128) COMMENT '渠道订单号',
    channel_id BIGINT NOT NULL COMMENT '渠道ID',
    order_status VARCHAR(32) NOT NULL DEFAULT 'CREATED' COMMENT '订单状态',
    shipping_status VARCHAR(32) NOT NULL DEFAULT 'UNSHIPPED' COMMENT '发货状态',
    payment_status VARCHAR(32) NOT NULL DEFAULT 'UNPAID' COMMENT '支付状态',
    refund_status VARCHAR(32) NOT NULL DEFAULT 'NONE' COMMENT '退款状态',
    exception_tag VARCHAR(64) COMMENT '异常标签',
    priority VARCHAR(32) NOT NULL DEFAULT 'NORMAL' COMMENT '优先级',
    paid_at DATETIME COMMENT '支付时间',
    shipped_at DATETIME COMMENT '发货时间',
    delivered_at DATETIME COMMENT '签收时间',
    buyer_id VARCHAR(64) COMMENT '买家ID',
    buyer VARCHAR(128) COMMENT '买家昵称',
    buyer_email VARCHAR(256) COMMENT '买家邮箱',
    buyer_phone VARCHAR(64) COMMENT '买家电话',
    buyer_country VARCHAR(64) COMMENT '买家国家',
    buyer_level VARCHAR(32) COMMENT '买家等级',
    buyer_tags JSON COMMENT '买家标签',
    buyer_register_time DATETIME COMMENT '注册时间',
    buyer_order_count INT NOT NULL DEFAULT 0 COMMENT '历史订单数',
    buyer_total_spent DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '历史消费金额',
    receiver_name VARCHAR(128) NOT NULL COMMENT '收货人姓名',
    receiver_phone VARCHAR(64) NOT NULL COMMENT '收货人电话',
    receiver_country VARCHAR(64) NOT NULL COMMENT '收货国家',
    receiver_state VARCHAR(128) COMMENT '收货省份',
    receiver_city VARCHAR(128) COMMENT '收货城市',
    receiver_district VARCHAR(128) COMMENT '收货区县',
    receiver_postal_code VARCHAR(32) COMMENT '收货邮编',
    receiver_address VARCHAR(512) NOT NULL COMMENT '详细地址',
    receiver_address_lang VARCHAR(64) COMMENT '地址语言',
    latitude DECIMAL(10,7) COMMENT '纬度',
    longitude DECIMAL(10,7) COMMENT '经度',
    transaction_id VARCHAR(128) COMMENT '交易流水号',
    payment_method VARCHAR(64) COMMENT '支付方式',
    payment_time DATETIME COMMENT '支付时间',
    currency VARCHAR(8) NOT NULL DEFAULT 'USD' COMMENT '订单币种',
    exchange_rate DECIMAL(20,6) NOT NULL DEFAULT 1 COMMENT '汇率',
    order_amount DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '商品金额',
    order_amount_cny DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '人民币金额',
    tax_amount DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '税额',
    shipping_fee DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '运费',
    discount_amount DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '折扣金额',
    coupon_code VARCHAR(128) COMMENT '优惠券编码',
    coupon_discount DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '优惠券折扣',
    actual_paid DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '实付金额',
    refund_amount DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '退款金额',
    shipping_method VARCHAR(64) COMMENT '配送方式',
    carrier_id BIGINT COMMENT '物流商ID',
    carrier_name VARCHAR(128) COMMENT '物流商名称',
    tracking_no VARCHAR(256) COMMENT '运单号',
    tracking_url VARCHAR(512) COMMENT '追踪URL',
    warehouse_id BIGINT COMMENT '仓库ID',
    warehouse_location VARCHAR(128) COMMENT '库位',
    estimated_delivery DATE COMMENT '预计送达日期',
    weight DECIMAL(10,3) COMMENT '重量(kg)',
    length DECIMAL(10,2) COMMENT '长度(cm)',
    width DECIMAL(10,2) COMMENT '宽度(cm)',
    height DECIMAL(10,2) COMMENT '高度(cm)',
    utm_source VARCHAR(128) COMMENT 'UTM来源',
    utm_medium VARCHAR(128) COMMENT 'UTM媒介',
    utm_campaign VARCHAR(128) COMMENT 'UTM广告系列',
    utm_content VARCHAR(256) COMMENT 'UTM内容',
    utm_term VARCHAR(256) COMMENT 'UTM关键词',
    referrer_url VARCHAR(1024) COMMENT 'referrer来源',
    landing_page VARCHAR(512) COMMENT '落地页',
    ip VARCHAR(64) COMMENT 'IP地址',
    ip_country VARCHAR(64) COMMENT 'IP归属国',
    device VARCHAR(64) COMMENT '设备类型',
    browser VARCHAR(64) COMMENT '浏览器',
    os VARCHAR(64) COMMENT '操作系统',
    parent_order_id BIGINT COMMENT '母订单ID',
    merge_order_id BIGINT COMMENT '合并订单ID',
    related_order_id BIGINT COMMENT '关联订单ID',
    original_order_id BIGINT COMMENT '原订单ID(补发用)',
    handler_id BIGINT COMMENT '处理人ID',
    handler_name VARCHAR(128) COMMENT '处理人姓名',
    handler_group VARCHAR(64) COMMENT '处理组',
    order_tags JSON COMMENT '订单标签',
    internal_remark TEXT COMMENT '内部备注',
    buyer_remark TEXT COMMENT '买家备注',
    item_count INT NOT NULL DEFAULT 0 COMMENT '商品数量',
    total_amount DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '订单总金额',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_order_no (order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单主表';

CREATE TABLE order_item (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    order_id BIGINT NOT NULL COMMENT '订单ID',
    product_id BIGINT NOT NULL COMMENT '商品ID',
    sku_id BIGINT NOT NULL COMMENT 'SKU ID',
    sku VARCHAR(128) NOT NULL COMMENT 'SKU编码',
    product_title VARCHAR(256) NOT NULL COMMENT '商品标题',
    product_image VARCHAR(512) COMMENT '商品图片',
    category_id VARCHAR(64) COMMENT '类目ID',
    category_name VARCHAR(128) COMMENT '类目名称',
    specs JSON COMMENT '规格信息',
    barcode VARCHAR(128) COMMENT '条形码',
    qty INT NOT NULL COMMENT '数量',
    price DECIMAL(20,4) NOT NULL COMMENT '单价',
    cost_price DECIMAL(20,4) NOT NULL COMMENT '成本价',
    profit DECIMAL(20,4) COMMENT '利润',
    profit_rate DECIMAL(8,4) COMMENT '利润率',
    subtotal DECIMAL(20,4) NOT NULL COMMENT '小计金额',
    weight DECIMAL(10,3) COMMENT '重量',
    available INT NOT NULL DEFAULT 0 COMMENT '可用库存',
    locked INT NOT NULL DEFAULT 0 COMMENT '锁定库存',
    in_transit INT NOT NULL DEFAULT 0 COMMENT '在途库存',
    reserved_qty INT NOT NULL DEFAULT 0 COMMENT '预留数量',
    shipped_qty INT NOT NULL DEFAULT 0 COMMENT '已发货数量',
    returned_qty INT NOT NULL DEFAULT 0 COMMENT '已退货数量',
    gift_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '赠品标记',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单明细表';

CREATE TABLE order_status_event (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    order_id BIGINT NOT NULL COMMENT '订单ID',
    status VARCHAR(64) NOT NULL COMMENT '状态',
    time DATETIME NOT NULL COMMENT '时间',
    title VARCHAR(256) NOT NULL COMMENT '标题',
    summary VARCHAR(1024) COMMENT '摘要',
    type VARCHAR(64) NOT NULL COMMENT '类型',
    operator VARCHAR(128) COMMENT '操作人',
    operator_role VARCHAR(64) COMMENT '操作人角色',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单状态事件表';

CREATE TABLE order_operation_log (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    order_id BIGINT COMMENT '订单ID',
    module VARCHAR(64) NOT NULL COMMENT '模块',
    type VARCHAR(64) NOT NULL COMMENT '操作类型',
    object_id VARCHAR(64) COMMENT '对象ID',
    time DATETIME NOT NULL COMMENT '操作时间',
    title VARCHAR(256) NOT NULL COMMENT '操作标题',
    content TEXT COMMENT '操作内容',
    operator VARCHAR(128) NOT NULL COMMENT '操作人',
    operator_role VARCHAR(64) COMMENT '操作人角色',
    result VARCHAR(64) COMMENT '操作结果',
    source_ip VARCHAR(64) COMMENT '来源IP',
    before_value TEXT COMMENT '变更前值',
    after_value TEXT COMMENT '变更后值',
    remark TEXT COMMENT '备注',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单操作日志表';

CREATE TABLE shipping_order (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    shipment_no VARCHAR(64) NOT NULL COMMENT '发货单号',
    order_id BIGINT NOT NULL COMMENT '订单ID',
    order_no VARCHAR(64) NOT NULL COMMENT '订单号',
    warehouse_id BIGINT NOT NULL COMMENT '仓库ID',
    items JSON NOT NULL COMMENT '发货商品明细',
    sku_count INT NOT NULL DEFAULT 0 COMMENT 'SKU数量',
    expected_qty INT NOT NULL DEFAULT 0 COMMENT '预期数量',
    actual_qty INT NOT NULL DEFAULT 0 COMMENT '实际数量',
    carrier_id BIGINT COMMENT '物流商ID',
    carrier_name VARCHAR(128) COMMENT '物流商名称',
    tracking_no VARCHAR(256) COMMENT '运单号',
    shipping_status VARCHAR(32) NOT NULL DEFAULT 'PENDING_PICK' COMMENT '发货状态',
    return_status VARCHAR(32) NOT NULL DEFAULT 'NOT_RETURNED' COMMENT '回传状态',
    return_tracking_no VARCHAR(256) COMMENT '退件运单号',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    shipped_at DATETIME COMMENT '发货时间',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_shipment_no (shipment_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='发货单表';

-- =============================================
-- 8. 售后中心
-- =============================================

CREATE TABLE after_sale (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    after_sale_no VARCHAR(64) NOT NULL COMMENT '售后单号',
    order_id BIGINT NOT NULL COMMENT '订单ID',
    order_no VARCHAR(64) NOT NULL COMMENT '订单号',
    buyer_id VARCHAR(64) COMMENT '买家ID',
    buyer VARCHAR(128) NOT NULL COMMENT '买家姓名',
    type VARCHAR(32) NOT NULL COMMENT '售后类型',
    amount DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '申请金额',
    audit_status VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '审核状态',
    refund_status VARCHAR(32) NOT NULL DEFAULT 'NONE' COMMENT '退款状态',
    reason VARCHAR(1024) NOT NULL COMMENT '申请原因',
    responsibility VARCHAR(64) COMMENT '责任方',
    refund_amount DECIMAL(20,4) COMMENT '实际退款金额',
    refund_method VARCHAR(64) COMMENT '退款方式',
    return_tracking_no VARCHAR(256) COMMENT '退货运单号',
    return_carrier VARCHAR(128) COMMENT '退货物流商',
    resend_warehouse_id BIGINT COMMENT '补发仓库ID',
    resend_sku_id BIGINT COMMENT '补发SKU ID',
    resend_order_id BIGINT COMMENT '补发订单ID',
    audit_remark TEXT COMMENT '审核备注',
    images JSON COMMENT '图片列表',
    handler_id BIGINT COMMENT '处理人ID',
    handler_name VARCHAR(128) COMMENT '处理人姓名',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_after_sale_no (after_sale_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后表';

-- =============================================
-- 9. 财务中心
-- =============================================

CREATE TABLE finance_payment (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    order_id BIGINT COMMENT '订单ID',
    payment_no VARCHAR(64) NOT NULL COMMENT '收款单号',
    channel_order_no VARCHAR(128) COMMENT '渠道订单号',
    channel_id BIGINT NOT NULL COMMENT '渠道ID',
    shop_name VARCHAR(256) COMMENT '店铺名称',
    currency VARCHAR(8) NOT NULL COMMENT '币种',
    amount DECIMAL(20,4) NOT NULL COMMENT '收款金额',
    fee DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '手续费',
    pay_method VARCHAR(64) COMMENT '支付方式',
    pay_time DATETIME COMMENT '支付时间',
    transaction_no VARCHAR(128) COMMENT '交易流水号',
    reconcile_status VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '对账状态',
    reconcile_time DATETIME COMMENT '对账时间',
    remark VARCHAR(1024) COMMENT '备注',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_payment_no (payment_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='收款表';

CREATE TABLE finance_refund (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    order_id BIGINT NOT NULL COMMENT '订单ID',
    refund_no VARCHAR(64) NOT NULL COMMENT '退款单号',
    channel_id BIGINT NOT NULL COMMENT '渠道ID',
    channel_order_no VARCHAR(128) COMMENT '渠道订单号',
    currency VARCHAR(8) NOT NULL COMMENT '币种',
    refund_amount DECIMAL(20,4) NOT NULL COMMENT '退款金额',
    refund_method VARCHAR(64) COMMENT '退款方式',
    refund_time DATETIME COMMENT '退款时间',
    refund_status VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '退款状态',
    channel_refund_no VARCHAR(128) COMMENT '渠道退款单号',
    reason VARCHAR(1024) COMMENT '退款原因',
    remark VARCHAR(1024) COMMENT '备注',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_refund_no (refund_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='退款表';

CREATE TABLE supplier_settlement (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    settlement_no VARCHAR(64) NOT NULL COMMENT '结算单号',
    supplier_id BIGINT NOT NULL COMMENT '供应商ID',
    period VARCHAR(32) NOT NULL COMMENT '结算周期',
    purchase_order_ids JSON COMMENT '关联采购单ID列表',
    payable_amount DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '应付金额',
    paid_amount DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '已付金额',
    status VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '结算状态',
    paid_at DATETIME COMMENT '支付时间',
    remark VARCHAR(1024) COMMENT '备注',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_settlement_no (settlement_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='供应商结算表';

CREATE TABLE invoice (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    invoice_no VARCHAR(64) NOT NULL COMMENT '发票号',
    invoice_type VARCHAR(32) NOT NULL COMMENT '发票类型',
    order_id BIGINT COMMENT '订单ID',
    buyer_name VARCHAR(256) NOT NULL COMMENT '购买方名称',
    buyer_tax_no VARCHAR(128) COMMENT '购买方税号',
    seller_name VARCHAR(256) NOT NULL COMMENT '销售方名称',
    amount DECIMAL(20,4) NOT NULL COMMENT '发票金额',
    tax_rate DECIMAL(8,4) NOT NULL COMMENT '税率',
    tax_amount DECIMAL(20,4) NOT NULL COMMENT '税额',
    invoice_date DATE NOT NULL COMMENT '开票日期',
    status VARCHAR(32) NOT NULL DEFAULT 'DRAFT' COMMENT '发票状态',
    remark VARCHAR(1024) COMMENT '备注',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_invoice_no (invoice_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='发票表';

-- =============================================
-- 10. 客服中心
-- =============================================

CREATE TABLE ticket (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    ticket_no VARCHAR(64) NOT NULL COMMENT '工单编号',
    title VARCHAR(256) NOT NULL COMMENT '工单标题',
    type VARCHAR(32) NOT NULL COMMENT '工单类型',
    priority VARCHAR(32) NOT NULL DEFAULT 'MEDIUM' COMMENT '优先级',
    status VARCHAR(32) NOT NULL DEFAULT 'OPEN' COMMENT '工单状态',
    creator VARCHAR(128) NOT NULL COMMENT '创建人',
    assignee_id BIGINT COMMENT '受理人ID',
    assignee_name VARCHAR(128) COMMENT '受理人姓名',
    related_order_id BIGINT COMMENT '关联订单ID',
    related_order_no VARCHAR(64) COMMENT '关联订单号',
    content TEXT NOT NULL COMMENT '工单内容',
    images JSON COMMENT '图片列表',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_ticket_no (ticket_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='工单表';

CREATE TABLE satisfaction (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    order_id BIGINT NOT NULL COMMENT '订单ID',
    order_no VARCHAR(64) NOT NULL COMMENT '订单号',
    channel_id BIGINT NOT NULL COMMENT '渠道ID',
    source VARCHAR(64) COMMENT '来源',
    buyer VARCHAR(128) COMMENT '买家',
    product_title VARCHAR(256) COMMENT '商品标题',
    rating INT NOT NULL COMMENT '评分',
    content TEXT COMMENT '评价内容',
    reply TEXT COMMENT '回复内容',
    handle_status VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '处理状态',
    cs_id BIGINT COMMENT '客服ID',
    cs_name VARCHAR(128) COMMENT '客服姓名',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='满意度评价表';

-- =============================================
-- 11. AI客服
-- =============================================

CREATE TABLE ai_channel (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(128) NOT NULL COMMENT '渠道名称',
    code VARCHAR(64) NOT NULL COMMENT '渠道编码',
    icon VARCHAR(64) COMMENT '图标',
    enabled TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用',
    robot_name VARCHAR(128) COMMENT '机器人名称',
    robot_enabled TINYINT(1) NOT NULL DEFAULT 1 COMMENT '机器人是否启用',
    auto_transfer_threshold INT NOT NULL DEFAULT 300 COMMENT '自动转人工阈值(秒)',
    priority INT NOT NULL DEFAULT 0 COMMENT '优先级',
    welcome_message TEXT COMMENT '欢迎语',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id),
    UNIQUE KEY uk_ai_channel_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='AI渠道配置表';

CREATE TABLE knowledge_category (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(128) NOT NULL COMMENT '分类名称',
    parent_id VARCHAR(64) COMMENT '父分类ID',
    sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
    count INT NOT NULL DEFAULT 0 COMMENT '条目数量',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='知识库分类表';

CREATE TABLE knowledge_base (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    category_id BIGINT NOT NULL COMMENT '分类ID',
    category_name VARCHAR(128) NOT NULL COMMENT '分类名称',
    keywords JSON COMMENT '关键词列表',
    question VARCHAR(512) NOT NULL COMMENT '问题',
    answer TEXT NOT NULL COMMENT '回答',
    clicks INT NOT NULL DEFAULT 0 COMMENT '点击次数',
    ai_score DECIMAL(5,2) NOT NULL DEFAULT 0 COMMENT 'AI评分',
    status VARCHAR(32) NOT NULL DEFAULT 'ENABLED' COMMENT '状态',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='知识库表';

CREATE TABLE auto_reply_rule (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(128) NOT NULL COMMENT '规则名称',
    priority INT NOT NULL DEFAULT 0 COMMENT '优先级',
    trigger_type VARCHAR(32) NOT NULL COMMENT '触发类型',
    keywords JSON COMMENT '关键词列表',
    match_mode VARCHAR(32) NOT NULL DEFAULT 'CONTAIN' COMMENT '匹配模式',
    responses JSON NOT NULL COMMENT '回复列表',
    status VARCHAR(32) NOT NULL DEFAULT 'ENABLED' COMMENT '规则状态',
    hit_count INT NOT NULL DEFAULT 0 COMMENT '命中次数',
    accuracy DECIMAL(5,2) NOT NULL DEFAULT 0 COMMENT '准确率',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='自动回复规则表';

CREATE TABLE chat_session (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    visitor_id BIGINT NOT NULL COMMENT '访客ID',
    visitor_name VARCHAR(128) COMMENT '访客名称',
    visitor_avatar VARCHAR(512) COMMENT '访客头像',
    channel_id BIGINT NOT NULL COMMENT '渠道ID',
    shop_name VARCHAR(256) COMMENT '店铺名称',
    ai_handled TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'AI处理标记',
    agent_id BIGINT COMMENT '客服ID',
    agent_name VARCHAR(128) COMMENT '客服名称',
    satisfaction INT COMMENT '满意度评分',
    status VARCHAR(32) NOT NULL DEFAULT 'WAITING' COMMENT '会话状态',
    source VARCHAR(64) COMMENT '来源',
    device VARCHAR(64) COMMENT '设备',
    location VARCHAR(128) COMMENT '位置',
    intent VARCHAR(128) COMMENT '意图',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    ended_at DATETIME COMMENT '结束时间',
    last_message_at DATETIME NOT NULL COMMENT '最后消息时间',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='AI会话表';

CREATE TABLE chat_message (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    session_id BIGINT NOT NULL COMMENT '会话ID',
    role VARCHAR(32) NOT NULL COMMENT '发送者角色',
    content TEXT NOT NULL COMMENT '消息内容',
    attachments JSON COMMENT '附件列表',
    timestamp DATETIME NOT NULL COMMENT '发送时间',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='AI消息表';

CREATE TABLE service_performance (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    agent_id BIGINT NOT NULL COMMENT '客服ID',
    agent_name VARCHAR(128) NOT NULL COMMENT '客服名称',
    agent_avatar VARCHAR(512) COMMENT '客服头像',
    department VARCHAR(128) COMMENT '部门',
    handle_count INT NOT NULL DEFAULT 0 COMMENT '处理数量',
    ai_assist_count INT NOT NULL DEFAULT 0 COMMENT 'AI辅助次数',
    avg_response_time DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '平均响应时间(秒)',
    avg_first_response_time DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '平均首次响应时间(秒)',
    satisfaction DECIMAL(5,2) NOT NULL DEFAULT 0 COMMENT '满意度',
    solve_rate DECIMAL(5,2) NOT NULL DEFAULT 0 COMMENT '解决率',
    date DATE NOT NULL COMMENT '统计日期',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='客服绩效表';

-- =============================================
-- 12. 营销中心
-- =============================================

CREATE TABLE promotion (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(128) NOT NULL COMMENT '活动名称',
    type VARCHAR(32) NOT NULL COMMENT '活动类型',
    channel_id BIGINT NOT NULL COMMENT '渠道ID',
    shop_name VARCHAR(256) COMMENT '店铺名称',
    start_time DATETIME NOT NULL COMMENT '开始时间',
    end_time DATETIME NOT NULL COMMENT '结束时间',
    status VARCHAR(32) NOT NULL DEFAULT 'DRAFT' COMMENT '活动状态',
    discount VARCHAR(64) COMMENT '折扣信息',
    min_amount DECIMAL(20,4) COMMENT '最低消费金额',
    products JSON COMMENT '参与商品列表',
    remark VARCHAR(1024) COMMENT '备注',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='促销活动表';

CREATE TABLE coupon (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(128) NOT NULL COMMENT '优惠券名称',
    coupon_type VARCHAR(32) NOT NULL COMMENT '优惠券类型',
    value DECIMAL(20,4) NOT NULL COMMENT '优惠值',
    min_amount DECIMAL(20,4) NOT NULL DEFAULT 0 COMMENT '最低消费金额',
    total_count INT NOT NULL DEFAULT 0 COMMENT '发放总量',
    used_count INT NOT NULL DEFAULT 0 COMMENT '已使用数量',
    used_rate DECIMAL(5,2) NOT NULL DEFAULT 0 COMMENT '使用率',
    valid_start DATETIME NOT NULL COMMENT '有效期开始',
    valid_end DATETIME NOT NULL COMMENT '有效期结束',
    status VARCHAR(32) NOT NULL DEFAULT 'DRAFT' COMMENT '优惠券状态',
    channels JSON COMMENT '可用渠道列表',
    remark VARCHAR(1024) COMMENT '备注',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by VARCHAR(64) COMMENT '创建人',
    updated_by VARCHAR(64) COMMENT '更新人',
    deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除标记',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='优惠券表';

CREATE TABLE price_watch (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    sku_id BIGINT NOT NULL COMMENT 'SKU ID',
    product_title VARCHAR(256) NOT NULL COMMENT '商品标题',
    channel_id BIGINT NOT NULL COMMENT '渠道ID',
    shop_name VARCHAR(256) COMMENT '店铺名称',
    local_price DECIMAL(20,4) NOT NULL COMMENT '本地价格',
    competitor_price DECIMAL(20,4) NOT NULL COMMENT '竞品价格',
    price_diff DECIMAL(20,4) NOT NULL COMMENT '价格差异',
    competitor_name VARCHAR(128) COMMENT '竞品名称',
    competitor_url VARCHAR(512) COMMENT '竞品链接',
    last_update DATETIME NOT NULL COMMENT '最后更新时间',
    alert_status VARCHAR(32) NOT NULL DEFAULT 'NORMAL' COMMENT '预警状态',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='价格监控表';

CREATE TABLE supplier_performance (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    supplier_id BIGINT NOT NULL COMMENT '供应商ID',
    supplier_name VARCHAR(128) NOT NULL COMMENT '供应商名称',
    contact VARCHAR(128) COMMENT '联系人',
    phone VARCHAR(64) COMMENT '联系电话',
    delivery_rate DECIMAL(5,2) NOT NULL DEFAULT 0 COMMENT '交货率',
    quality_rate DECIMAL(5,2) NOT NULL DEFAULT 0 COMMENT '质量合格率',
    response_time DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '响应时间(小时)',
    return_rate DECIMAL(5,2) NOT NULL DEFAULT 0 COMMENT '退货率',
    price_score DECIMAL(5,2) NOT NULL DEFAULT 0 COMMENT '价格评分',
    overall_score DECIMAL(5,2) NOT NULL DEFAULT 0 COMMENT '综合评分',
    rating_level VARCHAR(16) COMMENT '评级',
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='供应商绩效表';

-- =============================================
-- 13. 报表中心
-- =============================================

CREATE TABLE inventory_turnover (
    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    sku_id BIGINT NOT NULL COMMENT 'SKU ID',
    product_title VARCHAR(256) NOT NULL COMMENT '商品标题',
    warehouse_id BIGINT NOT NULL COMMENT '仓库ID',
    turnover_days INT NOT NULL DEFAULT 0 COMMENT '周转天数',
    sales_qty INT NOT NULL DEFAULT 0 COMMENT '销售数量',
    avg_stock DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT '平均库存',
    alert_status VARCHAR(32) NOT NULL DEFAULT 'NORMAL' COMMENT '预警状态',
    date DATE NOT NULL COMMENT '统计日期',
    tenant_id VARCHAR(64) NOT NULL DEFAULT 'DEFAULT' COMMENT '租户ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='库存周转表';

-- =============================================
-- 初始化数据
-- =============================================

-- 初始化超级管理员 (password: admin123)
INSERT INTO sys_user (username, password_hash, email, name, role, role_label, workspace, status, tenant_id) VALUES
('admin', '$2a$10$AKKZ1KuZW6lsiXk3ecbrn.Y5O8yucLF1/he37JoLsibfh3cVE4cSS', 'admin@oms.com', '系统管理员', 'ADMIN', '超级管理员', '全栈', 'ACTIVE', 'DEFAULT');

-- 初始化默认角色
INSERT INTO sys_role (name, description, permissions, bound_user_count, status, tenant_id) VALUES
('ADMIN', '超级管理员', '["*:*:*"]', 1, 'ACTIVE', 'DEFAULT'),
('MANAGER', '运营经理', '["product:*", "order:*", "inventory:*"]', 0, 'ACTIVE', 'DEFAULT'),
('OPERATOR', '运营专员', '["product:read", "order:*"]', 0, 'ACTIVE', 'DEFAULT'),
('PROCUREMENT', '采购员', '["purchase:*"]', 0, 'ACTIVE', 'DEFAULT'),
('WAREHOUSE', '仓库管理员', '["inventory:*", "warehouse:*"]', 0, 'ACTIVE', 'DEFAULT'),
('FINANCE', '财务', '["finance:*"]', 0, 'ACTIVE', 'DEFAULT'),
('CUSTOMER_SERVICE', '客服', '["after-sale:*", "ticket:*"]', 0, 'ACTIVE', 'DEFAULT');