all-project/backend/sql/jcbt_scholarship_quota_management.sql

-- ========================================
-- 奖学金名额分配模块 - 数据库建表脚本
-- 创建时间: 2025-01-27
-- 模块: ruoyi-jcbt
-- 说明: 按批次进行名额分配管理
-- ========================================

USE hzdyjs_xg;

-- 删除已存在的表（开发环境）
DROP TABLE IF EXISTS `jcbt_scholarship_quota_adjustment`;
DROP TABLE IF EXISTS `jcbt_scholarship_quota`;

-- =====================================================
-- 1. 名额分配主表
-- =====================================================
CREATE TABLE `jcbt_scholarship_quota` (
    `id`                  BIGINT(20)      NOT NULL            COMMENT '主键ID',
    `batch_id`           BIGINT(20)      NOT NULL            COMMENT '批次ID',
    `scholarship_type_id` BIGINT(20)     NOT NULL            COMMENT '奖学金类型ID',
    `allocation_level`   VARCHAR(20)     NOT NULL            COMMENT '分配级别：UNIVERSITY-全校，COLLEGE-学院，MAJOR-专业，CLASS-班级',
    `target_dept_id`     BIGINT(20)      DEFAULT NULL        COMMENT '目标部门ID（学院/专业/班级）',
    `parent_quota_id`    BIGINT(20)      DEFAULT NULL        COMMENT '上级名额ID（层级分配）',
    `total_quota`        INT             NOT NULL DEFAULT 0  COMMENT '总名额',
    `used_quota`         INT             NOT NULL DEFAULT 0  COMMENT '已使用名额',
    `reserved_quota`     INT             NOT NULL DEFAULT 0  COMMENT '预留名额',
    `available_quota`    INT             NOT NULL DEFAULT 0  COMMENT '可用名额',
    `allocation_rule`    TEXT            DEFAULT NULL        COMMENT '分配规则（JSON格式）',
    `allocation_basis`   VARCHAR(500)    DEFAULT NULL        COMMENT '分配依据说明',
    `allocation_method`  VARCHAR(20)     DEFAULT 'MANUAL'    COMMENT '分配方式：MANUAL-手动分配，AUTO-自动分配，PROPORTIONAL-按比例分配',
    `is_adjustable`      TINYINT(1)      DEFAULT 1           COMMENT '是否可调整：0-否，1-是',
    `is_locked`          TINYINT(1)      DEFAULT 0           COMMENT '是否锁定：0-否，1-是',
    `status`             VARCHAR(20)     DEFAULT 'ACTIVE'    COMMENT '状态：ACTIVE-生效，INACTIVE-失效，PENDING-待审核',
    `remark`             VARCHAR(500)    DEFAULT NULL        COMMENT '备注',
    `tenant_id`          BIGINT(20)      NOT NULL            COMMENT '租户ID',
    `create_dept`        BIGINT(20)      DEFAULT NULL        COMMENT '创建部门',
    `create_by`          BIGINT(20)      DEFAULT NULL        COMMENT '创建者',
    `create_time`        DATETIME        DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by`          BIGINT(20)      DEFAULT NULL        COMMENT '更新者',
    `update_time`        DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted`            TINYINT(1)      DEFAULT 0           COMMENT '删除标记：0-正常，1-删除',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_batch_type_dept` (`batch_id`, `scholarship_type_id`, `target_dept_id`, `deleted`),
    INDEX `idx_batch_level` (`batch_id`, `allocation_level`),
    INDEX `idx_dept_quota` (`target_dept_id`, `total_quota`),
    INDEX `idx_parent_quota` (`parent_quota_id`),
    INDEX `idx_tenant_status` (`tenant_id`, `status`, `deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='奖学金名额分配表';

-- =====================================================
-- 2. 名额调整记录表
-- =====================================================
CREATE TABLE `jcbt_scholarship_quota_adjustment` (
    `id`                  BIGINT(20)      NOT NULL            COMMENT '主键ID',
    `quota_id`           BIGINT(20)      NOT NULL            COMMENT '名额分配ID',
    `adjustment_type`    VARCHAR(20)     NOT NULL            COMMENT '调整类型：INCREASE-增加，DECREASE-减少，TRANSFER-转移',
    `from_quota_id`      BIGINT(20)      DEFAULT NULL        COMMENT '源名额ID（转移时使用）',
    `to_quota_id`        BIGINT(20)      DEFAULT NULL        COMMENT '目标名额ID（转移时使用）',
    `adjustment_amount`  INT             NOT NULL            COMMENT '调整数量',
    `before_total`       INT             NOT NULL            COMMENT '调整前总名额',
    `after_total`        INT             NOT NULL            COMMENT '调整后总名额',
    `adjustment_reason`  VARCHAR(500)    NOT NULL            COMMENT '调整原因',
    `approval_status`    VARCHAR(20)     DEFAULT 'PENDING'   COMMENT '审批状态：PENDING-待审批，APPROVED-已通过，REJECTED-已拒绝',
    `approval_user_id`   BIGINT(20)      DEFAULT NULL        COMMENT '审批人ID',
    `approval_time`      DATETIME        DEFAULT NULL        COMMENT '审批时间',
    `approval_remark`    VARCHAR(500)    DEFAULT NULL        COMMENT '审批备注',
    `tenant_id`          BIGINT(20)      NOT NULL            COMMENT '租户ID',
    `create_dept`        BIGINT(20)      DEFAULT NULL        COMMENT '创建部门',
    `create_by`          BIGINT(20)      DEFAULT NULL        COMMENT '创建者',
    `create_time`        DATETIME        DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by`          BIGINT(20)      DEFAULT NULL        COMMENT '更新者',
    `update_time`        DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted`            TINYINT(1)      DEFAULT 0           COMMENT '删除标记：0-正常，1-删除',
    PRIMARY KEY (`id`),
    INDEX `idx_quota_id` (`quota_id`),
    INDEX `idx_adjustment_type` (`adjustment_type`, `approval_status`),
    INDEX `idx_from_to_quota` (`from_quota_id`, `to_quota_id`),
    INDEX `idx_tenant` (`tenant_id`, `deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='名额调整记录表';

-- =====================================================
-- 3. 添加外键约束（可选，根据实际情况）
-- =====================================================
-- ALTER TABLE `jcbt_scholarship_quota`
-- ADD CONSTRAINT `fk_quota_batch` FOREIGN KEY (`batch_id`) REFERENCES `jcbt_scholarship_batch` (`id`);

-- ALTER TABLE `jcbt_scholarship_quota`
-- ADD CONSTRAINT `fk_quota_type` FOREIGN KEY (`scholarship_type_id`) REFERENCES `jcbt_scholarship_type` (`id`);

-- ALTER TABLE `jcbt_scholarship_quota_adjustment`
-- ADD CONSTRAINT `fk_adjustment_quota` FOREIGN KEY (`quota_id`) REFERENCES `jcbt_scholarship_quota` (`id`);

-- =====================================================
-- 4. 验证表创建
-- =====================================================
SHOW TABLES LIKE 'jcbt_scholarship_quota%';
DESCRIBE jcbt_scholarship_quota;
DESCRIBE jcbt_scholarship_quota_adjustment;