-- =====================================================
-- 数据库初始化脚本
-- 数据库: shujuku2_system
-- =====================================================

SET NAMES utf8mb4;

-- 创建数据库
CREATE DATABASE IF NOT EXISTS shujuku2_system DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE shujuku2_system;

-- =====================================================
-- 1. 用户表
-- =====================================================
DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    email VARCHAR(100) NOT NULL COMMENT '邮箱',
    department_id BIGINT COMMENT '部门ID',
    role ENUM('admin', 'analyst', 'user', 'guest') DEFAULT 'user' COMMENT '角色',
    status ENUM('active', 'inactive') DEFAULT 'active' COMMENT '状态',
    auth_source VARCHAR(20) NOT NULL DEFAULT 'local' COMMENT '最近登录来源',
    avatar_url VARCHAR(500) COMMENT '头像地址',
    mobile VARCHAR(30) COMMENT '手机号',
    job_title VARCHAR(100) COMMENT '职位',
    dingtalk_user_id VARCHAR(128) COMMENT '钉钉用户ID',
    dingtalk_union_id VARCHAR(128) COMMENT '钉钉UnionID',
    google_sub VARCHAR(128) COMMENT 'Google账号唯一标识',
    department_names TEXT COMMENT '外部部门信息',
    dingtalk_department_ids TEXT COMMENT '钉钉部门ID列表(JSON)',
    last_login_at DATETIME COMMENT '最后登录时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email),
    INDEX idx_department (department_id),
    INDEX idx_role (role),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 插入默认管理员用户 (密码: admin123)
INSERT INTO users (username, password_hash, name, email, role, status) VALUES
('admin', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9lBOsl7iKTVKIUi', '系统管理员', 'admin@company.com', 'admin', 'active');

-- =====================================================
-- 2. 部门表
-- =====================================================
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',
    parent_id BIGINT DEFAULT 0 COMMENT '父部门ID',
    name VARCHAR(100) NOT NULL COMMENT '部门名称',
    code VARCHAR(50) NOT NULL COMMENT '部门编码',
    sort INT DEFAULT 0 COMMENT '排序',
    status ENUM('active', 'inactive') DEFAULT 'active' COMMENT '状态',
    source VARCHAR(20) NOT NULL DEFAULT 'local' COMMENT '来源',
    external_id VARCHAR(64) COMMENT '外部部门ID',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_parent (parent_id),
    INDEX idx_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';

-- 插入示例部门
INSERT INTO departments (name, code, sort) VALUES
('总公司', 'ROOT', 1),
('技术部', 'TECH', 2),
('市场部', 'SALES', 3),
('财务部', 'FINANCE', 4),
('运营部', 'OPS', 5);

-- =====================================================
-- 3. 权限表
-- =====================================================
DROP TABLE IF EXISTS permissions;
CREATE TABLE permissions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '权限ID',
    page_key VARCHAR(50) NOT NULL COMMENT '页面标识',
    page_name VARCHAR(100) NOT NULL COMMENT '页面名称',
    role VARCHAR(20) NOT NULL COMMENT '角色',
    allowed BOOLEAN DEFAULT TRUE COMMENT '是否允许',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    UNIQUE KEY uk_page_role (page_key, role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限表';

-- 插入默认权限配置
INSERT INTO permissions (page_key, page_name, role, allowed) VALUES
('dashboard', '仪表盘', 'admin', TRUE),
('dashboard', '仪表盘', 'analyst', TRUE),
('dashboard', '仪表盘', 'user', TRUE),
('dashboard', '仪表盘', 'guest', TRUE),
('analysis_summary', '监控', 'admin', TRUE),
('analysis_summary', '监控', 'analyst', TRUE),
('analysis_summary', '监控', 'user', TRUE),
('analysis_summary', '监控', 'guest', TRUE),
('analysis_customer_service', '客服', 'admin', TRUE),
('analysis_customer_service', '客服', 'analyst', TRUE),
('analysis_customer_service', '客服', 'user', TRUE),
('analysis_customer_service', '客服', 'guest', TRUE),
('analysis_product_design', '开发', 'admin', TRUE),
('analysis_product_design', '开发', 'analyst', TRUE),
('analysis_product_design', '开发', 'user', TRUE),
('analysis_product_design', '开发', 'guest', TRUE),
('analysis_logistics', '快递', 'admin', TRUE),
('analysis_logistics', '快递', 'analyst', TRUE),
('analysis_logistics', '快递', 'user', TRUE),
('analysis_logistics', '快递', 'guest', TRUE),
('analysis_warehouse', '仓库', 'admin', TRUE),
('analysis_warehouse', '仓库', 'analyst', TRUE),
('analysis_warehouse', '仓库', 'user', TRUE),
('analysis_warehouse', '仓库', 'guest', TRUE),
('analysis_supplier', '供应', 'admin', TRUE),
('analysis_supplier', '供应', 'analyst', TRUE),
('analysis_supplier', '供应', 'user', TRUE),
('analysis_supplier', '供应', 'guest', TRUE),
('analysis_procurement', '采购', 'admin', TRUE),
('analysis_procurement', '采购', 'analyst', TRUE),
('analysis_procurement', '采购', 'user', TRUE),
('analysis_procurement', '采购', 'guest', TRUE),
('analysis_region', '地区', 'admin', TRUE),
('analysis_region', '地区', 'analyst', TRUE),
('analysis_region', '地区', 'user', TRUE),
('analysis_region', '地区', 'guest', TRUE),
('reports', '报表管理', 'admin', TRUE),
('reports', '报表管理', 'analyst', TRUE),
('reports', '报表管理', 'user', FALSE),
('reports', '报表管理', 'guest', FALSE),
('export', '数据导出', 'admin', TRUE),
('export', '数据导出', 'analyst', TRUE),
('export', '数据导出', 'user', FALSE),
('export', '数据导出', 'guest', FALSE),
('alerts', '预警配置', 'admin', TRUE),
('alerts', '预警配置', 'analyst', FALSE),
('alerts', '预警配置', 'user', FALSE),
('alerts', '预警配置', 'guest', FALSE),
('users', '用户管理', 'admin', TRUE),
('users', '用户管理', 'analyst', FALSE),
('users', '用户管理', 'user', FALSE),
('users', '用户管理', 'guest', FALSE),
('settings', '系统设置', 'admin', TRUE),
('settings', '系统设置', 'analyst', FALSE),
('settings', '系统设置', 'user', FALSE),
('settings', '系统设置', 'guest', FALSE);

-- =====================================================
-- 4. 系统配置表
-- =====================================================
CREATE TABLE IF NOT EXISTS system_config (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '配置ID',
    config_key VARCHAR(100) NOT NULL COMMENT '配置键',
    config_value TEXT COMMENT '配置值',
    config_type VARCHAR(20) DEFAULT 'string' COMMENT '配置类型',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    UNIQUE KEY uk_config_key (config_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统配置表';

-- 插入默认配置
INSERT IGNORE INTO system_config (config_key, config_value, config_type) VALUES
('site_name', '拼多多自动化运营平台', 'string'),
('site_description', '数据分析平台', 'string'),
('logo', '', 'string'),
('favicon', '', 'string'),
('copyright', '© 2024 公司名称. All rights reserved.', 'string'),
('enable_alert', 'true', 'boolean'),
('enable_export', 'true', 'boolean'),
('enable_sso', 'true', 'boolean'),
('default_theme', 'light', 'string'),
('setup_completed', 'false', 'boolean'),
('setup_completed_at', '', 'string');

-- 专用服务密钥与普通配置隔离，升级时不得删除。
CREATE TABLE IF NOT EXISTS system_secrets (
    secret_key VARCHAR(120) PRIMARY KEY COMMENT '密钥用途',
    secret_value TEXT NOT NULL COMMENT '仅服务端读取的密钥值',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统持久密钥';

-- =====================================================
-- 5. 部门功能页面权限表
-- =====================================================
DROP TABLE IF EXISTS department_page_permissions;
CREATE TABLE department_page_permissions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '部门页面权限ID',
    department_id BIGINT NOT NULL COMMENT '部门ID',
    page_key VARCHAR(100) NOT NULL COMMENT '页面标识',
    page_name VARCHAR(100) NOT NULL COMMENT '页面名称',
    allowed BOOLEAN DEFAULT TRUE COMMENT '是否允许访问',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    UNIQUE KEY uk_department_page (department_id, page_key),
    INDEX idx_department_id (department_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门功能页面权限表';

-- =====================================================
-- 6. 认证配置表
-- =====================================================
DROP TABLE IF EXISTS auth_configs;
CREATE TABLE auth_configs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '配置ID',
    auth_base_url VARCHAR(255) COMMENT '认证回调基础地址',
    dingtalk_enabled BOOLEAN DEFAULT FALSE COMMENT '是否启用钉钉登录',
    dingtalk_app_key VARCHAR(255) COMMENT '钉钉AppKey',
    dingtalk_app_secret VARCHAR(255) COMMENT '钉钉AppSecret',
    dingtalk_sync_root_dept_id BIGINT DEFAULT 1 COMMENT '钉钉同步根部门ID',
    google_enabled BOOLEAN DEFAULT FALSE COMMENT '是否启用Google登录',
    google_client_id VARCHAR(255) COMMENT 'Google Client ID',
    google_client_secret VARCHAR(255) COMMENT 'Google Client Secret',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='认证配置表';

-- =====================================================
-- 6.1 Redis 配置表
-- =====================================================
DROP TABLE IF EXISTS redis_configs;
CREATE TABLE redis_configs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '配置ID',
    profile_key VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT 'Redis配置标识：default/changzhou',
    profile_name VARCHAR(100) COMMENT 'Redis配置名称',
    host VARCHAR(255) COMMENT 'Redis主机地址',
    port INT NOT NULL DEFAULT 6379 COMMENT 'Redis端口',
    password VARCHAR(255) COMMENT 'Redis密码',
    db INT NOT NULL DEFAULT 0 COMMENT 'Redis数据库编号',
    enabled TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用',
    is_default BOOLEAN DEFAULT TRUE COMMENT '是否默认',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_redis_configs_default (is_default),
    INDEX idx_redis_configs_profile_key (profile_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Redis配置表';

-- =====================================================
-- 7. 预警规则表
-- =====================================================
DROP TABLE IF EXISTS alert_rules;
CREATE TABLE alert_rules (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '规则ID',
    name VARCHAR(100) NOT NULL COMMENT '规则名称',
    cube_name VARCHAR(50) NOT NULL COMMENT 'Cube模型名',
    measure_name VARCHAR(100) NOT NULL COMMENT '监控指标',
    dimension_filter JSON COMMENT '维度过滤条件',
    `condition` VARCHAR(20) NOT NULL COMMENT '条件: gt/lt/eq/gte/lte',
    threshold DECIMAL(18,4) NOT NULL COMMENT '阈值',
    time_granularity VARCHAR(20) COMMENT '时间粒度',
    time_range VARCHAR(50) COMMENT '时间范围',
    notify_channels JSON COMMENT '通知渠道',
    severity ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium' COMMENT '严重程度',
    status ENUM('active', 'paused') DEFAULT 'active' COMMENT '状态',
    creator_id BIGINT COMMENT '创建者ID',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_status (status),
    INDEX idx_cube (cube_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='预警规则表';

-- =====================================================
-- 8. 预警记录表
-- =====================================================
DROP TABLE IF EXISTS alert_records;
CREATE TABLE alert_records (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID',
    rule_id BIGINT NOT NULL COMMENT '规则ID',
    triggered_at DATETIME NOT NULL COMMENT '触发时间',
    actual_value DECIMAL(18,4) NOT NULL COMMENT '实际值',
    threshold DECIMAL(18,4) NOT NULL COMMENT '阈值',
    status ENUM('triggered', 'acknowledged', 'resolved') DEFAULT 'triggered' COMMENT '状态',
    notified_channels JSON COMMENT '已通知渠道',
    notification_results JSON COMMENT '通知结果',
    acknowledged_by BIGINT COMMENT '确认人ID',
    acknowledged_at DATETIME COMMENT '确认时间',
    resolved_at DATETIME COMMENT '解决时间',
    INDEX idx_rule_triggered (rule_id, triggered_at),
    INDEX idx_status (status),
    INDEX idx_triggered (triggered_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='预警记录表';

-- =====================================================
-- 9. 通知渠道表
-- =====================================================
DROP TABLE IF EXISTS notification_channels;
CREATE TABLE notification_channels (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '渠道ID',
    user_id BIGINT NOT NULL COMMENT '用户ID',
    channel_type ENUM('wechat', 'email', 'sms', 'dingtalk') NOT NULL COMMENT '渠道类型',
    channel_value VARCHAR(255) NOT NULL COMMENT '渠道值(邮箱/手机/企业微信ID)',
    is_enabled BOOLEAN DEFAULT TRUE COMMENT '是否启用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_user (user_id),
    INDEX idx_type (channel_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知渠道表';

-- =====================================================
-- 10. S3 配置表
-- =====================================================
DROP TABLE IF EXISTS s3_configs;
CREATE TABLE s3_configs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '配置ID',
    provider VARCHAR(20) NOT NULL COMMENT '提供商: aws/aliyun/tencent/qiniu/minio/custom',
    endpoint VARCHAR(255) COMMENT 'S3 Endpoint',
    region VARCHAR(50) COMMENT 'S3区域',
    zone VARCHAR(32) COMMENT '厂商区域短码，例如 z0',
    protocol VARCHAR(10) NOT NULL DEFAULT 'https' COMMENT '访问协议',
    storage_class VARCHAR(32) NOT NULL DEFAULT 'STANDARD' COMMENT '默认存储类型',
    bucket VARCHAR(100) NOT NULL COMMENT '存储桶名称',
    access_key_id VARCHAR(255) NOT NULL COMMENT 'Access Key ID',
    secret_access_key VARCHAR(255) NOT NULL COMMENT 'Secret Access Key',
    custom_domain VARCHAR(255) COMMENT '自定义域名',
    cdn_url VARCHAR(255) COMMENT 'CDN地址',
    is_default BOOLEAN DEFAULT FALSE COMMENT '是否默认',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S3配置表';

-- =====================================================
-- 11. 数据库配置表
-- =====================================================
DROP TABLE IF EXISTS database_configs;
CREATE TABLE database_configs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '配置ID',
    name VARCHAR(100) NOT NULL COMMENT '配置名称',
    driver VARCHAR(20) NOT NULL COMMENT '驱动: mysql/postgresql',
    host VARCHAR(255) NOT NULL COMMENT '主机地址',
    port INT NOT NULL DEFAULT 3306 COMMENT '端口',
    database_name VARCHAR(100) NOT NULL COMMENT '数据库名',
    username VARCHAR(100) NOT NULL COMMENT '用户名',
    password VARCHAR(255) COMMENT '密码',
    `ssl` BOOLEAN DEFAULT TRUE COMMENT '是否SSL',
    ssl_ca TEXT COMMENT 'SSL CA证书',
    max_pool_size INT DEFAULT 10 COMMENT '最大连接池',
    query_timeout INT DEFAULT 300 COMMENT '查询超时(秒)',
    use_env_config BOOLEAN DEFAULT FALSE COMMENT '是否优先使用 .env 业务数据库配置',
    is_default BOOLEAN DEFAULT FALSE COMMENT '是否默认',
    status ENUM('active', 'inactive') DEFAULT 'active' COMMENT '状态',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据库配置表';

-- =====================================================
-- 12. 操作日志表
-- =====================================================
DROP TABLE IF EXISTS operation_logs;
CREATE TABLE operation_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '日志ID',
    user_id BIGINT COMMENT '用户ID',
    action VARCHAR(50) NOT NULL COMMENT '操作类型',
    module VARCHAR(50) NOT NULL COMMENT '模块',
    description TEXT COMMENT '操作描述',
    ip_address VARCHAR(45) COMMENT 'IP地址',
    user_agent TEXT COMMENT 'User Agent',
    request_params TEXT COMMENT '请求参数',
    response_status INT COMMENT '响应状态',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_module (module),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作日志表';

-- =====================================================
-- 11. KPI目标值配置表
-- =====================================================
DROP TABLE IF EXISTS sys_kpi_targets;
CREATE TABLE sys_kpi_targets (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '配置ID',
    page_code VARCHAR(50) NOT NULL COMMENT '页面编码',
    kpi_code VARCHAR(50) NOT NULL COMMENT '指标编码',
    kpi_name VARCHAR(100) NOT NULL COMMENT '指标名称',
    unit VARCHAR(20) DEFAULT '' COMMENT '单位',
    direction VARCHAR(10) DEFAULT 'lower' COMMENT '越低越好lower/越高越好higher',
    target_good DECIMAL(18, 4) COMMENT '优秀线',
    target_pass DECIMAL(18, 4) COMMENT '达标线',
    target_warn DECIMAL(18, 4) COMMENT '预警线',
    is_enabled TINYINT(1) DEFAULT 1 COMMENT '是否启用',
    sort_order INT DEFAULT 0 COMMENT '排序',
    remark VARCHAR(200) DEFAULT '' COMMENT '备注',
    created_by BIGINT COMMENT '创建人',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_by BIGINT COMMENT '更新人',
    updated_at DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    UNIQUE KEY uk_page_kpi (page_code, kpi_code),
    INDEX idx_page (page_code),
    INDEX idx_enabled (is_enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='KPI目标值配置表';

-- 插入默认KPI目标值配置
INSERT INTO sys_kpi_targets (page_code, kpi_code, kpi_name, unit, direction, target_good, target_pass, target_warn, sort_order) VALUES
-- 客服分析
('customerService', 'refundProcessTime', '退款处理时长', 'h', 'lower', 12, 24, 48, 1),
('customerService', 'disputeRate', '纠纷判责率', '%', 'lower', 10, 20, 30, 2),
('customerService', 'compensationRate', '赔付率', '%', 'lower', 0.3, 0.5, 1, 3),
('customerService', 'avgCompensation', '单笔平均赔付', '元', 'lower', 30, 50, 80, 4),
('customerService', 'labelRate', '打标完成率', '%', 'higher', 98, 90, 80, 5),
-- 仓库分析
('warehouse', 'sameDayShipRate', '当日发货率', '%', 'higher', 98, 95, 90, 1),
('warehouse', 'wrongShipRate', '错发漏发率', '%', 'lower', 0.2, 0.5, 1, 2),
('warehouse', 'nextDayRate', '次日达占比', '%', 'higher', 50, 30, 20, 3),
('warehouse', 'inventoryTurnover', '库存周转率', '次/月', 'higher', 10, 6, 4, 4),
-- 快递分析
('logistics', 'pickupRate24h', '24h揽收及时率', '%', 'higher', 98, 95, 90, 1),
('logistics', 'avgDeliveryTime', '平均物流时长', 'h', 'lower', 48, 72, 96, 2),
('logistics', 'abnormalRate', '物流异常率', '%', 'lower', 1, 2, 5, 3),
('logistics', 'lostDamageRate', '丢件破损率', '%', 'lower', 0.2, 0.5, 1, 4),
-- 供应商分析
('supplier', 'qualityRefundRate', '品质退款率', '%', 'lower', 1.5, 3, 5, 1),
('supplier', 'defectRate', '次品率', '%', 'lower', 1, 2, 3, 2),
('supplier', 'shipTimelyRate', '发货及时率', '%', 'higher', 98, 95, 90, 3),
-- 采购分析
('procurement', 'purchaseRefundRate', '采购SKU退款率', '%', 'lower', 1.5, 3, 5, 1),
('procurement', 'dropshipTimelyRate', '代发准时率', '%', 'higher', 98, 95, 90, 2),
('procurement', 'stockoutImpactRate', '缺货影响订单率', '%', 'lower', 0.5, 1, 2, 3),
-- 开发分析
('productDesign', 'designAttributionRate', '开发归因退款率', '%', 'lower', 5, 8, 12, 1),
('productDesign', 'firstQualityReturnRate', '首次品退率', '%', 'lower', 1, 2, 3, 2),
('productDesign', 'visualIssueRate', '视觉问题率', '%', 'lower', 2, 5, 8, 3);