IntelligentRailwayCosting/SourceCode/IntelligentRailwayCosting/.script/init_mysql.sql

CREATE DATABASE IF NOT EXISTS iwb_railway_costing_v1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
USE iwb_railway_costing_v1;

-- 创建项目任务表
CREATE TABLE IF NOT EXISTS project_task (
    id INT AUTO_INCREMENT PRIMARY KEY,
    task_name VARCHAR(255) NOT NULL COMMENT '任务名称',
    task_sort INT DEFAULT 0 COMMENT '任务排序',
    task_desc VARCHAR(1000) COMMENT '任务描述',
    is_cover TINYINT DEFAULT 0 COMMENT '是否覆盖',
    project_id VARCHAR(50) NOT NULL COMMENT '项目编号',
    budget_id INT NOT NULL DEFAULT 0 COMMENT '概算序号',
    item_id INT NOT NULL COMMENT '条目序号',
    item_code VARCHAR(255) NOT NULL COMMENT '条目编号',
    file_path TEXT COMMENT '文件路径',
    process_status INT NOT NULL DEFAULT 0 COMMENT '处理状态(0:草稿, 1:待运行, 2:运行中, 200:运行成功, 4:运行失败 5:取消运行)',
    process_time DATETIME COMMENT '处理时间',
    process_error VARCHAR(4000) COMMENT '处理错误信息',
    send_status INT NOT NULL DEFAULT 0 COMMENT '推送状态(0:未推送, 1:推送中 ,2:推送成功, 3:推送失败, 4:已修改)',
    send_time DATETIME COMMENT '推送时间',
    send_error VARCHAR(4000) COMMENT '推送错误信息',
    is_del TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除(0:否, 1:是)',
    deleted_by VARCHAR(50) COMMENT '删除人',
    deleted_at DATETIME COMMENT '删除时间',
    created_by VARCHAR(50) COMMENT '创建人',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_by VARCHAR(50) COMMENT '更新人',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_project_id (project_id),
    INDEX idx_budget_id (budget_id),
    INDEX idx_item_id (item_id),
    INDEX idx_item_code (item_code),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='项目任务表';

-- 创建项目定额表
CREATE TABLE IF NOT EXISTS project_quota (
    id INT AUTO_INCREMENT PRIMARY KEY,
    task_id INT NOT NULL COMMENT '任务编号',
    parent_id INT NOT NULL COMMENT '父任务编号',
    project_id VARCHAR(50) NOT NULL COMMENT '项目编号',
    budget_id INT NOT NULL COMMENT '概算序号',
    budget_code VARCHAR(50) COMMENT '概算编号',
    item_id INT NOT NULL COMMENT '条目序号',
    item_code VARCHAR(255) NOT NULL COMMENT '条目编号',
    quota_id INT NULL DEFAULT 0 COMMENT '定额序号',
    quota_code VARCHAR(50) COMMENT '定额编号',
    quota_adjustment TEXT COMMENT '定额调整',
    entry_name VARCHAR(255) COMMENT '工程或费用项目名称',
    units VARCHAR(20) COMMENT '单位',
    amount FLOAT COMMENT '数量',
    ex_file VARCHAR(4000) COMMENT 'excel文件',
    ex_cell VARCHAR(50) COMMENT '数量单元格位置',
    ex_row VARCHAR(4000) COMMENT '该行内容',
    ex_unit VARCHAR(50) COMMENT 'excel中给出的单位',
    ex_amount FLOAT COMMENT 'excel中给出的数量',
    note VARCHAR(4000) COMMENT '说明',
    is_change INT NOT NULL DEFAULT 1 COMMENT '是否变更(0:未变更,1:已变更)',
    send_status INT NOT NULL DEFAULT 0 COMMENT '推送状态(0:未推送,1:推送中 ,2:已推送, 3:推送失败, 4:已修改)',
    send_time DATETIME COMMENT '推送时间',
    send_error VARCHAR(4000) COMMENT '推送错误信息',
    is_del TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除(0:否, 1:是)',
    deleted_by VARCHAR(50) COMMENT '删除人',
    deleted_at DATETIME COMMENT '删除时间',
    created_by VARCHAR(50) COMMENT '创建人',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_by VARCHAR(50) COMMENT '更新人',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_project_id (project_id),
    INDEX idx_budget_id (budget_id),
    INDEX idx_item_id (item_id),
    INDEX idx_item_code (item_code),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='项目定额表';

-- 创建日志表
CREATE TABLE IF NOT EXISTS sys_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL COMMENT '用户名',
    operation_type VARCHAR(50) NOT NULL COMMENT '操作类型',
    operation_desc VARCHAR(1000) COMMENT '操作描述',
    operation_result TINYINT COMMENT '操作结果',
    operation_module VARCHAR(100) COMMENT '操作模块',
    operation_data TEXT COMMENT '操作数据',
    data_changes TEXT COMMENT '数据变更记录',
    operation_ip VARCHAR(50) COMMENT '操作IP',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_username (username),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作日志表';