CREATE DATABASE IF NOT EXISTS thermal_control_system
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE thermal_control_system;

CREATE TABLE IF NOT EXISTS categories (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL COMMENT '分类名称',
    type ENUM('data_file', 'data_package') NOT NULL COMMENT '分类类型',
    parent_id BIGINT DEFAULT NULL COMMENT '父分类ID',
    sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_type (type),
    INDEX idx_parent (parent_id)
) COMMENT='数据分类表';

CREATE TABLE IF NOT EXISTS data_files (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    filename VARCHAR(255) NOT NULL COMMENT '原始文件名',
    stored_name VARCHAR(255) NOT NULL UNIQUE COMMENT '存储文件名',
    file_path VARCHAR(500) NOT NULL COMMENT '文件路径',
    category_id BIGINT DEFAULT NULL COMMENT '分类ID',
    data_count INT NOT NULL DEFAULT 0 COMMENT '数据条数',
    uploaded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    remark TEXT NULL COMMENT '备注',
    INDEX idx_file_category (category_id)
) COMMENT='数据文件表';

ALTER TABLE data_files
    ADD COLUMN IF NOT EXISTS file_path VARCHAR(500) NOT NULL COMMENT '文件路径';

CREATE TABLE IF NOT EXISTS data_packages (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL COMMENT '数据包名称',
    category_id BIGINT DEFAULT NULL COMMENT '分类ID(data_package类型)',
    remark TEXT NULL COMMENT '备注',
    data_count INT NOT NULL DEFAULT 0 COMMENT '数据条数(所有关联文件合计)',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_pkg_category (category_id)
) COMMENT='数据包表';

CREATE TABLE IF NOT EXISTS data_package_files (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    package_id BIGINT NOT NULL COMMENT '数据包ID',
    file_id BIGINT NOT NULL COMMENT '数据文件ID',
    sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
    INDEX idx_dpf_package (package_id),
    INDEX idx_dpf_file (file_id)
) COMMENT='数据包文件关联表';
