resource-platform/sql/schema.sql

-- ============================================
-- 软件项目资源登记平台 - 数据库设计
-- 数据库: MySQL 8.0+
-- 字符集: utf8mb4
-- ============================================

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

-- ============================================
-- 1. 用户表
-- ============================================
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username`    VARCHAR(50)     NOT NULL COMMENT '登录账号',
  `password`    VARCHAR(100)    NOT NULL COMMENT '密码(加密存储)',
  `real_name`   VARCHAR(50)     NOT NULL COMMENT '姓名',
  `email`       VARCHAR(100)    NOT NULL COMMENT '邮箱,用于到期提醒',
  `role`        TINYINT         NOT NULL DEFAULT 2 COMMENT '角色: 1-管理员 2-普通用户',
  `status`      TINYINT         NOT NULL DEFAULT 1 COMMENT '状态: 1-启用 0-禁用',
  `create_time` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB COMMENT='系统用户表';

-- ============================================
-- 2. 资源类型表(可扩展字典)
-- ============================================
DROP TABLE IF EXISTS `resource_type`;
CREATE TABLE `resource_type` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(50)     NOT NULL COMMENT '类型名称,如:云服务器、云数据库、对象存储、短信套餐、邮件套餐',
  `sort`        INT             NOT NULL DEFAULT 0 COMMENT '排序',
  `remark`      VARCHAR(255)             DEFAULT NULL,
  `create_time` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB COMMENT='资源类型字典表';

-- ============================================
-- 3. 服务商表(可选字典)
-- ============================================
DROP TABLE IF EXISTS `provider`;
CREATE TABLE `provider` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(50)     NOT NULL COMMENT '服务商名称,如:阿里云、腾讯云、AWS',
  `remark`      VARCHAR(255)             DEFAULT NULL,
  `create_time` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB COMMENT='服务商字典表';

-- ============================================
-- 4. 项目表
-- ============================================
DROP TABLE IF EXISTS `project`;
CREATE TABLE `project` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(100)    NOT NULL COMMENT '项目名称',
  `owner_id`    BIGINT UNSIGNED NOT NULL COMMENT '负责人,关联sys_user.id',
  `parent_id`   BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父项目ID: 0-顶层(无父),仅支持两层',
  `status`      TINYINT         NOT NULL DEFAULT 1 COMMENT '状态: 1-进行中 0-已下线',
  `remark`      VARCHAR(500)             DEFAULT NULL,
  `create_time` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted`     TINYINT         NOT NULL DEFAULT 0 COMMENT '逻辑删除: 0-未删除 1-已删除',
  PRIMARY KEY (`id`),
  KEY `idx_owner` (`owner_id`)
) ENGINE=InnoDB COMMENT='项目表';

-- ============================================
-- 5. 资源表(核心表)
-- ============================================
DROP TABLE IF EXISTS `resource`;
CREATE TABLE `resource` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`          VARCHAR(100)    NOT NULL COMMENT '资源名称',
  `type_id`       BIGINT UNSIGNED NOT NULL COMMENT '关联resource_type.id',
  `provider_id`   BIGINT UNSIGNED          DEFAULT NULL COMMENT '关联provider.id',
  `account_info`  VARCHAR(255)             DEFAULT NULL COMMENT '账号/控制台链接等信息(不存密码)',
  `start_date`    DATE                     DEFAULT NULL COMMENT '开通/购买日期',
  `expire_date`   DATE                     DEFAULT NULL COMMENT '到期日期',
  `cost`          DECIMAL(12,2)            DEFAULT 0.00 COMMENT '费用',
  `cost_cycle`    TINYINT                  DEFAULT 1 COMMENT '计费周期: 1-月付 2-年付 3-一次性',
  `owner_id`      BIGINT UNSIGNED NOT NULL COMMENT '负责人,关联sys_user.id',
  `status`        TINYINT         NOT NULL DEFAULT 1 COMMENT '状态: 1-使用中 2-即将到期 3-已过期 0-已停用',
  `remark`        VARCHAR(500)             DEFAULT NULL,
  `purchase_duration` TINYINT           DEFAULT NULL COMMENT '购买时长: 8-按量 1-1个月 2-2个月 3-3个月 4-6个月 5-1年 6-2年 7-3年',
  `create_time`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted`       TINYINT         NOT NULL DEFAULT 0 COMMENT '逻辑删除: 0-未删除 1-已删除',
  PRIMARY KEY (`id`),
  KEY `idx_type` (`type_id`),
  KEY `idx_owner` (`owner_id`),
  KEY `idx_expire` (`expire_date`)
) ENGINE=InnoDB COMMENT='资源表';

-- ============================================
-- 6. 项目-资源关联表(多对多)
-- ============================================
DROP TABLE IF EXISTS `project_resource`;
CREATE TABLE `project_resource` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_id`  BIGINT UNSIGNED NOT NULL,
  `resource_id` BIGINT UNSIGNED NOT NULL,
  `create_time` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_project_resource` (`project_id`, `resource_id`),
  KEY `idx_resource` (`resource_id`)
) ENGINE=InnoDB COMMENT='项目资源关联表';

-- ============================================
-- 6.1 资源续费记录表
-- ============================================
DROP TABLE IF EXISTS `resource_renewal`;
CREATE TABLE `resource_renewal` (
  `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `resource_id`      BIGINT UNSIGNED NOT NULL COMMENT '关联resource.id',
  `renew_date`       DATE            DEFAULT NULL COMMENT '续费操作/生效日期',
  `old_expire_date`  DATE            DEFAULT NULL COMMENT '续费前到期日期',
  `new_expire_date`  DATE            DEFAULT NULL COMMENT '续费后新到期日期',
  `duration_code`    TINYINT         DEFAULT NULL COMMENT '续费时长: 1-1个月 2-2个月 3-3个月 4-6个月 5-1年 6-2年 7-3年',
  `cost`             DECIMAL(12,2)   DEFAULT 0.00 COMMENT '本次续费金额(仅作历史记录, 不回写资源费用)',
  `remark`           VARCHAR(500)    DEFAULT NULL,
  `operator_id`      BIGINT UNSIGNED DEFAULT 0 COMMENT '操作人, 关联sys_user.id',
  `create_time`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_resource` (`resource_id`)
) ENGINE=InnoDB COMMENT='资源续费记录表';

-- ============================================
-- 7. 到期提醒记录表(避免重复发送邮件)
-- ============================================
DROP TABLE IF EXISTS `expire_notify_log`;
CREATE TABLE `expire_notify_log` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `resource_id`   BIGINT UNSIGNED NOT NULL,
  `notify_stage`  TINYINT         NOT NULL COMMENT '提醒阶段: 1-提前30天 2-提前7天 3-已过期',
  `notify_email`  VARCHAR(100)    NOT NULL,
  `notify_time`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_resource_stage` (`resource_id`, `notify_stage`)
) ENGINE=InnoDB COMMENT='到期提醒发送记录,同一资源同一阶段只发一次';

-- ============================================
-- 8. 操作日志表
-- ============================================
DROP TABLE IF EXISTS `operation_log`;
CREATE TABLE `operation_log` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`       BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '操作人(0=系统操作,定时任务等非 HTTP 场景)',
  `module`        VARCHAR(50)     NOT NULL COMMENT '模块: 资源/项目/用户/字典',
  `action`        VARCHAR(50)     NOT NULL COMMENT '操作: 新增/编辑/删除/关联变更',
  `target_id`     BIGINT UNSIGNED          DEFAULT NULL COMMENT '操作对象ID',
  `content`       VARCHAR(1000)            DEFAULT NULL COMMENT '操作详情',
  `create_time`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_target` (`target_id`)
) ENGINE=InnoDB COMMENT='操作日志表';

-- ============================================
-- 初始数据
-- ============================================
INSERT INTO `sys_user` (`username`, `password`, `real_name`, `email`, `role`) VALUES
('admin', '$2a$10$7JB720yubVSofvVWbazBLPRzKf5MhmwbAfNThBd3jzD09EQYDg.Ie', '系统管理员', 'admin@example.com', 1);
-- 默认密码: admin123 (BCrypt加密,首次登录后请修改)

INSERT INTO `resource_type` (`name`, `sort`) VALUES
('云服务器', 1), ('云数据库', 2), ('对象存储', 3), ('域名', 4),
('SSL证书', 5), ('CDN', 6), ('短信套餐', 7), ('邮件套餐', 8), ('其他', 99);

INSERT INTO `provider` (`name`) VALUES
('阿里云'), ('腾讯云'), ('华为云'), ('AWS'), ('其他');