-- 0025 — AFF rule exception requests
--
-- Supports APPROVAL_REQUIRED severity on training rules. Flow:
--   1. Slot-add attempt hits an AFF rule evaluated as APPROVAL_REQUIRED
--      and the caller has no active grant → BLOCK with a pointer to the
--      exception endpoint.
--   2. Staff submits POST /aff-exceptions { studentUserId, loadId,
--      ruleKey, reason }. Row written with status = PENDING.
--   3. Authorized reviewer (S&TA / DZ_MANAGER) calls
--      POST /aff-exceptions/:id/decide { approve: boolean, notes? }.
--      Row moves to APPROVED or DENIED; audit log captures decision.
--   4. Staff retries slot-add. When an APPROVED exception exists for
--      (studentUserId, loadId, ruleKey), the AFF gate treats it as
--      consumed and the block is cleared. Row moves to CONSUMED.
--
-- Additive only. Safe to re-run.

CREATE TABLE IF NOT EXISTS `aff_rule_exceptions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `dropzoneId` INT NOT NULL,
  `ruleKey` VARCHAR(100) NOT NULL,
  `studentUserId` INT NOT NULL,
  `loadId` INT NULL,
  `studentLevel` INT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'PENDING',
  `reason` TEXT NOT NULL,
  `reviewerUserId` INT NULL,
  `reviewerNotes` TEXT NULL,
  `decidedAt` DATETIME(3) NULL,
  `consumedAt` DATETIME(3) NULL,
  `consumedSlotId` INT NULL,
  `expiresAt` DATETIME(3) NULL,
  `requestedByUserId` INT NOT NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `aff_rule_exceptions_dz_status_idx` (`dropzoneId`, `status`),
  INDEX `aff_rule_exceptions_student_status_idx` (`studentUserId`, `status`),
  INDEX `aff_rule_exceptions_load_idx` (`loadId`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;