sckw-service-platform/sql/2026/01/2026_01_07_chenxiaofei_create.sql

-- 1. 用户基础信息表
CREATE TABLE `users` (
                         `id` BIGINT UNSIGNED PRIMARY KEY COMMENT '雪花ID',
                         `username` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '登录账号',
                         `password_hash` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '加密存储的密码',
                         `full_name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用户姓名',
                         `avatar_url` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '头像URL地址',
                         `phone` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '联系电话',
                         `role_type` TINYINT NOT NULL DEFAULT 1 COMMENT '角色: 1-铲车司机(监管员), 2-门卫',
                         `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 0-禁用, 1-启用',
                         `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
                         `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                         UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';

-- 2. 登录日志表
CREATE TABLE `login_logs` (
                              `id` BIGINT UNSIGNED PRIMARY KEY COMMENT '雪花ID',
                              `user_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '关联用户ID',
                              `device_info` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '设备型号/标识',
                              `login_ip` VARCHAR(45) NOT NULL DEFAULT '' COMMENT '登录IP',
                              `login_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                              `login_type` TINYINT NOT NULL DEFAULT 1 COMMENT '1-首次登录, 2-切换账号登录',
                               INDEX `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='登录日志表';

-- 3. 物料信息字典表
CREATE TABLE `materials` (
                             `id` BIGINT UNSIGNED PRIMARY KEY COMMENT '雪花ID',
                             `material_name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '物料名称',
                             `category` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '物料分类',
                             `unit` VARCHAR(20) NOT NULL DEFAULT '吨' COMMENT '计量单位',
                             UNIQUE KEY `uk_material_name` (`material_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='物料信息表';

-- 4. 装载作业记录表 (核心业务表)
CREATE TABLE `loading_records` (
                                   `id` BIGINT UNSIGNED PRIMARY KEY COMMENT '雪花ID',
                                   `operator_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '操作员ID',
                                   `license_plate` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '车牌号',
                                   `customer_name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '客户名称',
                                   `load_type` TINYINT NOT NULL DEFAULT 1 COMMENT '装载类型: 1-装载, 2-补货',
                                   `material_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '物料ID',
                                   `quantity` DECIMAL(12, 2) NOT NULL DEFAULT 0.00 COMMENT '装载数量/重量',
                                   `work_date` DATE NOT NULL DEFAULT '1000-01-01' COMMENT '作业日期',
                                   `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
                                   INDEX `idx_query_composite` (`work_date`, `load_type`, `license_plate`),
                                   INDEX `idx_operator` (`operator_id`),
                                   INDEX `idx_customer` (`customer_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='铲车装载记录表';

-- 5. 意见反馈表
CREATE TABLE `feedbacks` (
                             `id` BIGINT UNSIGNED PRIMARY KEY COMMENT '雪花ID',
                             `user_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '反馈人ID',
                             `content` TEXT NOT NULL COMMENT '反馈正文',
                             `images` JSON NOT NULL COMMENT '图片附件地址',
                             `contact_info` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '联系方式',
                             `is_processed` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否已处理',
                             `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户反馈表';

-- 6. 系统版本管理表
CREATE TABLE `app_versions` (
                                `id` BIGINT UNSIGNED PRIMARY KEY COMMENT '雪花ID',
                                `version_code` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '版本号',
                                `platform` TINYINT NOT NULL DEFAULT 1 COMMENT '平台类型(1-Android,2-iOS)',
                                `update_log` TEXT NOT NULL COMMENT '更新日志',
                                `download_url` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '安装包下载地址',
                                `is_force_update` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否强制更新',
                                `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='版本控制表';

-- 系统内容配置表
CREATE TABLE `sys_articles` (
                                `id` BIGINT UNSIGNED PRIMARY KEY COMMENT '雪花ID',
                                `article_key` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '条款唯一标识 (如: REG_AGREEMENT, PRIVACY_POLICY)',
                                `title` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '条款标题',
                                `content` LONGTEXT NOT NULL COMMENT '条款详细内容 (富文本/HTML)',
                                `version` VARCHAR(20) NOT NULL DEFAULT '1.0.0' COMMENT '版本号',
                                `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                                UNIQUE KEY `uk_key` (`article_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='法律条款配置表';