IntelligentRailwayCosting/SourceCode/IntelligentRailwayCosting/.script/init.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 TINYINT DEFAULT 0 COMMENT '任务排序',
    task_desc VARCHAR(1000) 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 '文件路径',
    collect_status TINYINT NOT NULL DEFAULT 0 COMMENT '采集状态(0:未开始, 1:进行中, 2:已完成, 3:采集失败)',
    collect_time DATETIME COMMENT '采集时间',
    collect_error VARCHAR(1000) COMMENT '采集错误信息',
    process_status TINYINT NOT NULL DEFAULT 0 COMMENT '处理状态(0:未处理,1:处理中, 2:已处理, 3:处理失败)',
    process_time DATETIME COMMENT '处理时间',
    process_error VARCHAR(1000) COMMENT '处理错误信息',
    send_status TINYINT NOT NULL DEFAULT 0 COMMENT '发送状态(0:未发送,1:发送中 ,2:已发送, 3:发送失败)',
    send_time DATETIME COMMENT '发送时间',
    send_error VARCHAR(1000) 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 '任务编号',
    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_code VARCHAR(50) COMMENT '定额编号',
    entry_name VARCHAR(255) COMMENT '工程或费用项目名称',
    units VARCHAR(20) COMMENT '单位',
    amount FLOAT COMMENT '数量',
    ex_file VARCHAR(50) COMMENT 'excel文件',
    ex_cell VARCHAR(50) COMMENT '数量单元格位置，例如"C17"',
    ex_row VARCHAR(1000) COMMENT '该行内容，由逗号连接多个单元格得到',
    ex_unit VARCHAR(50) COMMENT 'excel中给出的单位',
    ex_amount FLOAT COMMENT 'excel中给出的数量',
    process_status TINYINT NOT NULL DEFAULT 0 COMMENT '处理状态(0:未处理,1:处理中, 2:已处理, 3:处理失败)',
    process_time DATETIME COMMENT '处理时间',
    process_error VARCHAR(1000) COMMENT '处理错误信息',
    send_status TINYINT NOT NULL DEFAULT 0 COMMENT '发送状态(0:未发送,1:发送中 ,2:已发送, 3:发送失败)',
    send_time DATETIME COMMENT '发送时间',
    send_error VARCHAR(1000) 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 '操作结果(0:失败, 1:成功)',
    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='操作日志表';