-- AI 补货建议系统表结构
-- 版本: 2.0
-- 更新: 2026-01-27

-- 1. AI预计划明细表
DROP TABLE IF EXISTS ai_pre_plan_detail;
CREATE TABLE ai_pre_plan_detail (
    id                   BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    group_id             BIGINT NOT NULL COMMENT '集团ID',
    brand_grouping_id    BIGINT COMMENT '品牌组合ID',
    dealer_grouping_id   BIGINT NOT NULL COMMENT '商家组合ID',
    dealer_grouping_name VARCHAR(128) COMMENT '商家组合名称',
    dealer_id            BIGINT COMMENT '商家ID',
    dealer_name          VARCHAR(128) COMMENT '商家名称',
    area_id              BIGINT COMMENT '区域ID',
    area_name            VARCHAR(128) COMMENT '区域名称',
    shop_id              BIGINT NOT NULL COMMENT '库房ID',
    shop_name            VARCHAR(128) COMMENT '库房名称',
    part_id              BIGINT COMMENT '配件ID',
    part_code            VARCHAR(64) NOT NULL COMMENT '配件编码',
    part_name            VARCHAR(256) COMMENT '配件名称',
    unit                 VARCHAR(32) COMMENT '单位',
    cost_price           DECIMAL(14,2) DEFAULT 0 COMMENT '成本价',
    base_ratio           DECIMAL(10,4) COMMENT '基准库销比',
    current_ratio        DECIMAL(10,4) COMMENT '当前库销比',
    valid_storage_cnt    DECIMAL(14,2) DEFAULT 0 COMMENT '有效库存数量(在库未锁+在途+计划数+调拨在途)',
    avg_sales_cnt        DECIMAL(14,2) DEFAULT 0 COMMENT '平均销量(月)',
    target_storage_cnt   DECIMAL(14,2) DEFAULT 0 COMMENT '目标库存数量',
    plan_cnt             INT DEFAULT 0 COMMENT '建议计划数量',
    plan_amount          DECIMAL(14,2) DEFAULT 0 COMMENT '建议计划金额',
    part_biz_type        TINYINT DEFAULT 1 COMMENT '配件业务类型: 1-配件 2-装饰',
    statistics_date      VARCHAR(16) NOT NULL COMMENT '统计日期(yyyy-MM-dd)',
    yn                   TINYINT DEFAULT 1 COMMENT '是否有效: 1-是 0-否',
    create_time          DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_group_date (group_id, statistics_date),
    INDEX idx_dealer_grouping (dealer_grouping_id),
    INDEX idx_shop (shop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI预计划明细表-存储每日计算的配件补货建议';

-- 2. AI补货任务表
DROP TABLE IF EXISTS ai_replenishment_task;
CREATE TABLE ai_replenishment_task (
    id                   BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    task_no              VARCHAR(32) NOT NULL UNIQUE COMMENT '任务编号(AI-开头)',
    group_id             BIGINT NOT NULL COMMENT '集团ID',
    dealer_grouping_id   BIGINT NOT NULL COMMENT '商家组合ID',
    dealer_grouping_name VARCHAR(128) COMMENT '商家组合名称',
    brand_grouping_id    BIGINT COMMENT '品牌组合ID',
    plan_amount          DECIMAL(14,2) DEFAULT 0 COMMENT '计划采购金额(预算)',
    actual_amount        DECIMAL(14,2) DEFAULT 0 COMMENT '实际分配金额',
    part_count           INT DEFAULT 0 COMMENT '分配配件数量',
    base_ratio           DECIMAL(10,4) COMMENT '基准库销比',
    status               TINYINT DEFAULT 0 COMMENT '状态: 0-运行中 1-成功 2-失败',
    error_message        TEXT COMMENT '错误信息',
    llm_provider         VARCHAR(32) COMMENT 'LLM提供商',
    llm_model            VARCHAR(64) COMMENT 'LLM模型名称',
    llm_total_tokens     INT DEFAULT 0 COMMENT 'LLM总Token数',
    statistics_date      VARCHAR(16) COMMENT '统计日期',
    start_time           DATETIME COMMENT '任务开始时间',
    end_time             DATETIME COMMENT '任务结束时间',
    create_time          DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_group_date (group_id, statistics_date),
    INDEX idx_dealer_grouping (dealer_grouping_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI补货任务表-记录每次补货建议任务执行';

-- 3. AI补货建议明细表
DROP TABLE IF EXISTS ai_replenishment_detail;
CREATE TABLE ai_replenishment_detail (
    id                   BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    task_no              VARCHAR(32) NOT NULL COMMENT '任务编号',
    group_id             BIGINT NOT NULL COMMENT '集团ID',
    dealer_grouping_id   BIGINT NOT NULL COMMENT '商家组合ID',
    brand_grouping_id    BIGINT COMMENT '品牌组合ID',
    shop_id              BIGINT NOT NULL COMMENT '库房ID',
    shop_name            VARCHAR(128) COMMENT '库房名称',
    part_code            VARCHAR(64) NOT NULL COMMENT '配件编码',
    part_name            VARCHAR(256) COMMENT '配件名称',
    unit                 VARCHAR(32) COMMENT '单位',
    cost_price           DECIMAL(14,2) DEFAULT 0 COMMENT '成本价',
    current_ratio        DECIMAL(10,4) COMMENT '当前库销比',
    base_ratio           DECIMAL(10,4) COMMENT '基准库销比',
    post_plan_ratio      DECIMAL(10,4) COMMENT '计划后预计库销比',
    valid_storage_cnt    DECIMAL(14,2) DEFAULT 0 COMMENT '有效库存数量',
    avg_sales_cnt        DECIMAL(14,2) DEFAULT 0 COMMENT '平均销量(月)',
    suggest_cnt          INT DEFAULT 0 COMMENT '建议采购数量',
    suggest_amount       DECIMAL(14,2) DEFAULT 0 COMMENT '建议采购金额',
    suggestion_reason    TEXT COMMENT '补货建议理由',
    priority             INT NOT NULL DEFAULT 2 COMMENT '优先级: 1-高, 2-中, 3-低',
    llm_confidence       FLOAT DEFAULT 0.8 COMMENT 'LLM置信度',
    statistics_date      VARCHAR(16) COMMENT '统计日期',
    create_time          DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_task_no (task_no),
    INDEX idx_shop_part (shop_id, part_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI补货建议明细表-存储每次任务的配件分配结果';

-- 4. AI补货报告表
DROP TABLE IF EXISTS ai_replenishment_report;
CREATE TABLE ai_replenishment_report (
    id                      BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    task_no                 VARCHAR(32) NOT NULL COMMENT '任务编号',
    group_id                BIGINT NOT NULL COMMENT '集团ID',
    dealer_grouping_id      BIGINT NOT NULL COMMENT '商家组合ID',
    dealer_grouping_name    VARCHAR(128) COMMENT '商家组合名称',
    total_storage_amount    DECIMAL(16,2) COMMENT '总库存金额',
    total_sales_amount      DECIMAL(16,2) COMMENT '总销售金额(90天)',
    overall_ratio           DECIMAL(10,4) COMMENT '整体库销比',
    target_ratio            DECIMAL(10,4) COMMENT '目标库销比',
    total_part_count        INT DEFAULT 0 COMMENT '配件总数',
    shortage_part_count     INT DEFAULT 0 COMMENT '缺货配件数',
    overstock_part_count    INT DEFAULT 0 COMMENT '超标配件数',
    normal_part_count       INT DEFAULT 0 COMMENT '正常配件数',
    stagnant_part_count     INT DEFAULT 0 COMMENT '呆滞配件数',
    suggest_total_amount    DECIMAL(14,2) COMMENT '建议采购总金额',
    suggest_part_count      INT DEFAULT 0 COMMENT '建议采购配件数',
    top_priority_parts      TEXT COMMENT '重点关注配件(JSON数组)',
    report_title            VARCHAR(256) COMMENT '报告标题',
    executive_summary       TEXT COMMENT '执行摘要',
    inventory_analysis      TEXT COMMENT '库存分析',
    risk_assessment         TEXT COMMENT '风险评估',
    purchase_recommendations TEXT COMMENT '采购建议',
    optimization_suggestions TEXT COMMENT '优化建议',
    full_report_markdown    LONGTEXT COMMENT '完整报告(Markdown)',
    llm_provider            VARCHAR(32) COMMENT 'LLM提供商',
    llm_model               VARCHAR(64) COMMENT 'LLM模型',
    generation_tokens       INT DEFAULT 0 COMMENT '生成Token数',
    statistics_date         VARCHAR(16) COMMENT '统计日期',
    create_time             DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_task_no (task_no),
    INDEX idx_dealer_grouping (dealer_grouping_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI补货分析报告表-存储LLM生成的分析报告';
