-- 交通统计报表审核系统 - 数据库初始化脚本 v3
-- Demo范围：交企统H203-2表_道路货物运输月度生产情况

CREATE DATABASE IF NOT EXISTS traffic_audit DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE traffic_audit;

-- ============================================
-- 一、用户权限模块（含三员）
-- ============================================

-- 部门表
DROP TABLE IF EXISTS sys_dept;
CREATE TABLE sys_dept (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL COMMENT '部门名称',
    dept_code VARCHAR(20) NOT NULL COMMENT '行政区划代码',
    parent_id BIGINT DEFAULT 0 COMMENT '上级部门',
    dept_level TINYINT COMMENT '1省 2市 3县',
    sort_order INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 角色表
DROP TABLE IF EXISTS sys_role;
CREATE TABLE sys_role (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL COMMENT '角色名',
    role_code VARCHAR(50) NOT NULL COMMENT 'SYS_ADMIN/SEC_OFFICER/AUDITOR/PROVINCE/CITY/COUNTY',
    role_type VARCHAR(20) DEFAULT 'BUSINESS' COMMENT 'SYSTEM(三员)/BUSINESS(业务)',
    role_level TINYINT DEFAULT 1 COMMENT '0三员 1省厅 2市局 3县区',
    is_mutex TINYINT DEFAULT 0 COMMENT '1三员互斥 0普通'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 用户表
DROP TABLE IF EXISTS sys_user;
CREATE TABLE sys_user (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(200) NOT NULL COMMENT 'BCrypt',
    real_name VARCHAR(50),
    phone VARCHAR(20),
    dept_id BIGINT,
    status TINYINT DEFAULT 1 COMMENT '0禁用 1启用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 用户角色关联
DROP TABLE IF EXISTS sys_user_role;
CREATE TABLE sys_user_role (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 二、业务数据模块
-- ============================================

-- 清理旧版遗留表（v2 schema）
DROP TABLE IF EXISTS report_enterprise_monthly;

-- 交企统H203-2表：道路货物运输月度生产情况（Demo核心表）
DROP TABLE IF EXISTS h2032_enterprise_monthly;
CREATE TABLE h2032_enterprise_monthly (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    report_period VARCHAR(20) NOT NULL COMMENT '报表期 2026-06',
    region_code VARCHAR(20) COMMENT '所属地区',
    enterprise_code VARCHAR(50) COMMENT '企业代码',
    enterprise_name VARCHAR(200) COMMENT '企业名称',
    unified_credit_code VARCHAR(50) COMMENT '统一社会信用代码',
    report_unit VARCHAR(200) COMMENT '填报单位',

    -- 车辆数与吨位数
    vehicle_total INT COMMENT '车辆数_总计',
    tons_total DOUBLE COMMENT '标记吨位数_总计',
    vehicle_tractor INT COMMENT '车辆数_牵引车',
    vehicle_trailer INT COMMENT '车辆数_挂车',
    tons_trailer DOUBLE COMMENT '标记吨位数_挂车',
    vehicle_container INT COMMENT '车辆数_集装箱车',
    tons_container DOUBLE COMMENT '标记吨位数_集装箱车',
    vehicle_whole INT COMMENT '车辆数_整车',
    tons_whole DOUBLE COMMENT '标记吨位数_整车',

    -- 货运量与周转量
    freight_total DOUBLE COMMENT '货运量_总计（吨）',
    turnover_total DOUBLE COMMENT '货物周转量_总计（吨公里）',

    -- 分货类货运量
    freight_container DOUBLE COMMENT '货运量_集装箱（吨）',
    freight_coal DOUBLE COMMENT '货运量_煤炭及制品（吨）',
    freight_oil_gas DOUBLE COMMENT '货运量_石油天然气及制品（吨）',
    freight_crude_oil DOUBLE COMMENT '货运量_其中原油（吨）',
    freight_metal_ore DOUBLE COMMENT '货运量_金属矿石（吨）',
    freight_iron_ore DOUBLE COMMENT '货运量_其中铁矿石（吨）',
    freight_building DOUBLE COMMENT '货运量_矿物性建筑材料（吨）',
    freight_grain DOUBLE COMMENT '货运量_粮食（吨）',

    -- 运营指标
    avg_tonnage DOUBLE COMMENT '货车平均吨位',
    avg_tonnage_trailer DOUBLE COMMENT '平均吨位_挂车',
    avg_tonnage_whole DOUBLE COMMENT '平均吨位_整车',
    avg_distance DOUBLE COMMENT '货车平均运距（公里）',
    daily_trips_per_ton DOUBLE COMMENT '单吨日均载运次数',
    daily_mileage_per_vehicle DOUBLE COMMENT '单车单天行驶里程（公里）',
    daily_volume_per_vehicle DOUBLE COMMENT '单车日均运量（吨）',
    daily_trips_with_tractor DOUBLE COMMENT '单吨日均载运次数（结合牵引车）',

    -- 环比增速
    vehicle_mom_growth DOUBLE COMMENT '车辆数-总计环比增速',
    tons_mom_growth DOUBLE COMMENT '标记吨位-总计环比增速',
    freight_mom_growth DOUBLE COMMENT '货运量-总计环比增速',
    turnover_mom_growth DOUBLE COMMENT '货物周转量-总计环比增速',
    avg_distance_mom_growth DOUBLE COMMENT '货车平均运距-环比增速',

    -- 同比增速
    vehicle_yoy_growth DOUBLE COMMENT '车辆数-总计同比增速',
    tons_yoy_growth DOUBLE COMMENT '标记吨位-总计同比增速',
    tractor_trailer_ratio DOUBLE COMMENT '牵引车挂车比',
    freight_yoy_growth DOUBLE COMMENT '货运量-总计同比增速',
    turnover_yoy_growth DOUBLE COMMENT '货物周转量-总计同比增速',
    avg_distance_yoy_growth DOUBLE COMMENT '货车平均运距-同比增速',

    -- 填报信息
    unit_leader VARCHAR(50) COMMENT '单位负责人',
    stats_leader VARCHAR(50) COMMENT '统计负责人',
    contact_person VARCHAR(50) COMMENT '填表人',
    contact_phone VARCHAR(20) COMMENT '联系电话',
    report_date DATE COMMENT '报出日期',
    verify_explanation TEXT COMMENT '核实性原因解释',
    report_notes TEXT COMMENT '上报说明',
    modify_record TEXT COMMENT '修改记录',

    import_batch_id BIGINT COMMENT '导入批次',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='交企统H203-2表_道路货物运输月度生产情况';

-- 运政车辆数据
DROP TABLE IF EXISTS transport_auth_vehicle;
CREATE TABLE transport_auth_vehicle (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    report_period VARCHAR(20) COMMENT '报表期',
    enterprise_name VARCHAR(200) COMMENT '企业名称',
    tractor_count INT DEFAULT 0 COMMENT '总牵引车数',
    trailer_count INT DEFAULT 0 COMMENT '总挂车数',
    other_count INT DEFAULT 0 COMMENT '其它车辆数',
    trailer_tons DOUBLE COMMENT '挂车总吨位(吨)',
    other_tons DOUBLE COMMENT '其它车辆总吨位(吨)',
    import_batch_id BIGINT COMMENT '导入批次',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运政车辆数据';

-- 车辆轨迹里程数据
DROP TABLE IF EXISTS vehicle_track_mileage;
CREATE TABLE vehicle_track_mileage (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    enterprise_name VARCHAR(200) COMMENT '企业名称',
    monthly_mileage DOUBLE COMMENT '月度总里程(公里)',
    tracked_vehicles INT DEFAULT 0 COMMENT '有里程的车辆数',
    report_period VARCHAR(20) COMMENT '报表期',
    import_batch_id BIGINT COMMENT '导入批次',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='车辆轨迹里程数据';

-- 规上规下拆分运输量（用户手动上传）
DROP TABLE IF EXISTS scale_split_transport;
CREATE TABLE scale_split_transport (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    report_period VARCHAR(20) NOT NULL COMMENT '报表期 2026-06',
    period_type VARCHAR(10) NOT NULL COMMENT 'MONTH当月/CUMULATIVE累计',
    region_name VARCHAR(50) COMMENT '市州名称',

    -- 规上
    above_scale_freight DOUBLE COMMENT '规上货运量',
    above_scale_turnover DOUBLE COMMENT '规上周转量',
    above_scale_rank INT COMMENT '规上排名',
    above_scale_yoy DOUBLE COMMENT '规上同比',
    above_scale_yoy_rank INT COMMENT '规上增速排名',

    -- 规下
    below_scale_freight DOUBLE COMMENT '规下货运量',
    below_scale_turnover DOUBLE COMMENT '规下周转量',
    below_scale_rank INT COMMENT '规下排名',
    below_scale_yoy DOUBLE COMMENT '规下同比',
    below_scale_yoy_rank INT COMMENT '规下增速排名',

    -- 合计
    total_freight DOUBLE COMMENT '运输量合计-货运量',
    total_turnover DOUBLE COMMENT '运输量合计-周转量',
    total_rank INT COMMENT '合计排名',
    total_yoy DOUBLE COMMENT '合计同比',
    total_yoy_rank INT COMMENT '合计增速排名',

    import_batch_id BIGINT COMMENT '导入批次',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='规上规下拆分运输量';

-- 模板_货运量周转量（每月导入：左半今年1-N月，右半去年1-12月，N=报表期月份）
DROP TABLE IF EXISTS freight_turnover_import;
CREATE TABLE freight_turnover_import (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    report_period VARCHAR(20) NOT NULL COMMENT '报表期 2026-06',
    region_name VARCHAR(50) NOT NULL COMMENT '市州或湖北省',

    -- 今年1-12月货运量（左半，仅1-N月有值）
    freight_m01 DOUBLE COMMENT '1月货运量(万吨)',
    freight_m02 DOUBLE COMMENT '2月货运量(万吨)',
    freight_m03 DOUBLE COMMENT '3月货运量(万吨)',
    freight_m04 DOUBLE COMMENT '4月货运量(万吨)',
    freight_m05 DOUBLE COMMENT '5月货运量(万吨)',
    freight_m06 DOUBLE COMMENT '6月货运量(万吨)',
    freight_m07 DOUBLE COMMENT '7月货运量(万吨)',
    freight_m08 DOUBLE COMMENT '8月货运量(万吨)',
    freight_m09 DOUBLE COMMENT '9月货运量(万吨)',
    freight_m10 DOUBLE COMMENT '10月货运量(万吨)',
    freight_m11 DOUBLE COMMENT '11月货运量(万吨)',
    freight_m12 DOUBLE COMMENT '12月货运量(万吨)',

    -- 去年1-12月货运量（右半，固定12个月，算同比用）
    last_freight_m01 DOUBLE COMMENT '去年1月货运量(万吨)',
    last_freight_m02 DOUBLE COMMENT '去年2月货运量(万吨)',
    last_freight_m03 DOUBLE COMMENT '去年3月货运量(万吨)',
    last_freight_m04 DOUBLE COMMENT '去年4月货运量(万吨)',
    last_freight_m05 DOUBLE COMMENT '去年5月货运量(万吨)',
    last_freight_m06 DOUBLE COMMENT '去年6月货运量(万吨)',
    last_freight_m07 DOUBLE COMMENT '去年7月货运量(万吨)',
    last_freight_m08 DOUBLE COMMENT '去年8月货运量(万吨)',
    last_freight_m09 DOUBLE COMMENT '去年9月货运量(万吨)',
    last_freight_m10 DOUBLE COMMENT '去年10月货运量(万吨)',
    last_freight_m11 DOUBLE COMMENT '去年11月货运量(万吨)',
    last_freight_m12 DOUBLE COMMENT '去年12月货运量(万吨)',

    -- 今年1-12月周转量（左半，仅1-N月有值，导入存档，报表用拆分表）
    turnover_m01 DOUBLE COMMENT '1月周转量(万吨公里)',
    turnover_m02 DOUBLE COMMENT '2月周转量(万吨公里)',
    turnover_m03 DOUBLE COMMENT '3月周转量(万吨公里)',
    turnover_m04 DOUBLE COMMENT '4月周转量(万吨公里)',
    turnover_m05 DOUBLE COMMENT '5月周转量(万吨公里)',
    turnover_m06 DOUBLE COMMENT '6月周转量(万吨公里)',
    turnover_m07 DOUBLE COMMENT '7月周转量(万吨公里)',
    turnover_m08 DOUBLE COMMENT '8月周转量(万吨公里)',
    turnover_m09 DOUBLE COMMENT '9月周转量(万吨公里)',
    turnover_m10 DOUBLE COMMENT '10月周转量(万吨公里)',
    turnover_m11 DOUBLE COMMENT '11月周转量(万吨公里)',
    turnover_m12 DOUBLE COMMENT '12月周转量(万吨公里)',

    -- 去年1-12月周转量（右半，固定12个月）
    last_turnover_m01 DOUBLE COMMENT '去年1月周转量(万吨公里)',
    last_turnover_m02 DOUBLE COMMENT '去年2月周转量(万吨公里)',
    last_turnover_m03 DOUBLE COMMENT '去年3月周转量(万吨公里)',
    last_turnover_m04 DOUBLE COMMENT '去年4月周转量(万吨公里)',
    last_turnover_m05 DOUBLE COMMENT '去年5月周转量(万吨公里)',
    last_turnover_m06 DOUBLE COMMENT '去年6月周转量(万吨公里)',
    last_turnover_m07 DOUBLE COMMENT '去年7月周转量(万吨公里)',
    last_turnover_m08 DOUBLE COMMENT '去年8月周转量(万吨公里)',
    last_turnover_m09 DOUBLE COMMENT '去年9月周转量(万吨公里)',
    last_turnover_m10 DOUBLE COMMENT '去年10月周转量(万吨公里)',
    last_turnover_m11 DOUBLE COMMENT '去年11月周转量(万吨公里)',
    last_turnover_m12 DOUBLE COMMENT '去年12月周转量(万吨公里)',

    import_batch_id BIGINT COMMENT '导入批次',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='模板_货运量周转量导入';

-- ============================================
-- 三、审核规则模块
-- ============================================

DROP TABLE IF EXISTS audit_rule;
CREATE TABLE audit_rule (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    rule_name VARCHAR(100) NOT NULL COMMENT '规则名称',
    rule_code VARCHAR(50) NOT NULL COMMENT '规则编码',
    description TEXT COMMENT '规则描述',
    check_field VARCHAR(200) COMMENT '检查字段',
    compare_type VARCHAR(20) COMMENT 'DIFF/RATIO/MOM/YOY/NULL_CHECK/RANGE',
    threshold VARCHAR(50) COMMENT '阈值',
    alert_level VARCHAR(10) DEFAULT 'YELLOW' COMMENT 'RED/YELLOW',
    report_type VARCHAR(50) DEFAULT 'H2032' COMMENT '适用报表表号',
    is_enabled TINYINT DEFAULT 1,
    llm_raw_input TEXT COMMENT 'LLM翻译原始输入',
    sort_order INT DEFAULT 0,
    created_by BIGINT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 预置6条审核规则（Demo）
INSERT INTO audit_rule (rule_name, rule_code, description, check_field, compare_type, threshold, alert_level, report_type, is_enabled, sort_order) VALUES
('车辆数运政对比', 'RULE_001', '企业上报车辆数与运政数据对比，差距>5台需核实', 'vehicleTotal', 'DIFF', '5', 'RED', 'H2032', 1, 1),
('吨位运政对比', 'RULE_002', '企业上报吨位与运政数据对比，差距>50需核实', 'tonsTotal', 'DIFF', '50', 'RED', 'H2032', 1, 2),
('周转量轨迹对比', 'RULE_003', '上报周转量与轨迹测算周转量对比，高出30%需核实', 'turnoverTotal,trackMileage', 'RATIO', '1.3', 'RED', 'H2032', 1, 3),
('货运类型环比', 'RULE_004', '本月新增货运类型需核实', 'freightContainer,freightCoal', 'MOM', '0', 'YELLOW', 'H2032', 1, 4),
('集装箱异常', 'RULE_005', '无集装箱车但有集装箱货运量需核实', 'freightContainer,vehicleContainer', 'NULL_CHECK', '0', 'RED', 'H2032', 1, 5),
('整车吨位范围', 'RULE_006', '整车吨位不在4-40吨之间', 'tonsWhole', 'OUT_RANGE', '4,40', 'YELLOW', 'H2032', 1, 6);

-- ============================================
-- 四、审核结果模块
-- ============================================

DROP TABLE IF EXISTS audit_result;
CREATE TABLE audit_result (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    rule_id BIGINT NOT NULL COMMENT 'FK -> audit_rule',
    report_id BIGINT NOT NULL COMMENT 'FK -> h2032_enterprise_monthly',
    enterprise_code VARCHAR(50) COMMENT '企业代码',
    report_period VARCHAR(20) COMMENT '报表期',
    actual_value VARCHAR(200) COMMENT '实际值',
    threshold_value VARCHAR(200) COMMENT '阈值',
    deviation VARCHAR(200) COMMENT '偏差',
    status VARCHAR(20) DEFAULT 'PENDING' COMMENT 'PENDING/CONFIRMED/IGNORED',
    reviewer_id BIGINT COMMENT '审核人',
    review_comment TEXT COMMENT '审核意见',
    reviewed_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS audit_explanation;
CREATE TABLE audit_explanation (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    audit_result_id BIGINT NOT NULL COMMENT 'FK -> audit_result',
    explanation_text TEXT COMMENT '上报人解释',
    llm_suggestion TEXT COMMENT 'LLM建议',
    llm_confidence DECIMAL(5,2) COMMENT '置信度',
    llm_status VARCHAR(20) DEFAULT 'PENDING' COMMENT 'PENDING/PROCESSED/FAILED'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 五、系统日志与导入批次
-- ============================================

DROP TABLE IF EXISTS sys_operation_log;
CREATE TABLE sys_operation_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT COMMENT '操作人',
    module VARCHAR(50) COMMENT '模块',
    action VARCHAR(50) COMMENT '操作',
    detail TEXT COMMENT '详情JSON',
    ip_address VARCHAR(50) COMMENT 'IP地址',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS import_batch;
CREATE TABLE import_batch (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    file_name VARCHAR(200) COMMENT '文件名',
    import_type VARCHAR(50) COMMENT 'H2032/TRANSPORT_AUTH/TRACK_MILEAGE/SCALE_SPLIT',
    report_period VARCHAR(20) COMMENT '报表期',
    total_rows INT DEFAULT 0,
    success_rows INT DEFAULT 0,
    fail_rows INT DEFAULT 0,
    error_detail TEXT COMMENT '错误详情',
    created_by BIGINT COMMENT '导入人',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 六、LLM脱敏映射
-- ============================================

DROP TABLE IF EXISTS llm_desensitize_map;
CREATE TABLE llm_desensitize_map (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    original_value VARCHAR(200) COMMENT '原始值',
    masked_value VARCHAR(200) COMMENT '脱敏值',
    mask_type VARCHAR(20) COMMENT 'ENTERPRISE/REGION',
    call_id VARCHAR(50) COMMENT '调用ID'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 七、预置数据
-- ============================================

-- 预置部门（湖北省）
INSERT INTO sys_dept (dept_name, dept_code, dept_level) VALUES ('湖北省交通运输厅', '420000', 1);

-- 预置角色
INSERT INTO sys_role (role_name, role_code, role_type, role_level, is_mutex) VALUES
('系统管理员', 'SYS_ADMIN', 'SYSTEM', 0, 1),
('安全保密员', 'SEC_OFFICER', 'SYSTEM', 0, 1),
('安全审计员', 'AUDITOR', 'SYSTEM', 0, 1),
('省厅业务员', 'PROVINCE', 'BUSINESS', 1, 0);

-- 预置管理员账号 admin / admin123
INSERT INTO sys_user (username, password, real_name, dept_id) VALUES
('admin', '.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9lBOsl7iAt6Z5Eh', '系统管理员', 1);

INSERT INTO sys_user_role (user_id, role_id) VALUES (1, 1);