学员课时包越用越乱？购课、课时消耗与剩余预警的避坑指南

CREATE TABLE class_package (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  student_id BIGINT NOT NULL,
  total_hours DECIMAL(5,1) NOT NULL,
  used_hours DECIMAL(5,1) NOT NULL DEFAULT 0,
  remain_hours DECIMAL(5,1) GENERATED ALWAYS AS (total_hours - used_hours) STORED,
  expire_at DATE NOT NULL,
  status TINYINT NOT NULL DEFAULT 0
);

CREATE TABLE class_consume (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  package_id BIGINT NOT NULL,
  lesson_id BIGINT NOT NULL,
  consume_hours DECIMAL(5,1) NOT NULL,
  consumed_at DATETIME NOT NULL,
  operator_id BIGINT NOT NULL
);

function pickPackage(studentId, type, hours) {
  return db.query(
    `SELECT id, remain_hours FROM class_package
     WHERE student_id = ? AND status = 0 AND package_type = ?
       AND remain_hours >= ? ORDER BY expire_at ASC LIMIT 1`,
    [studentId, type, hours]
  );
}

// 扣课事务：先扣余量再写流水
BEGIN;
UPDATE class_package SET used_hours = used_hours + 2
 WHERE id = ? AND remain_hours >= 2;
INSERT INTO class_consume (package_id, lesson_id, consume_hours) VALUES (?, ?, 2);
COMMIT;

SELECT s.name, p.remain_hours,
       DATEDIFF(p.expire_at, CURDATE()) AS days_left
FROM class_package p JOIN student s ON s.id = p.student_id
WHERE p.status = 0
  AND (p.remain_hours <= 3 OR DATEDIFF(p.expire_at, CURDATE()) <= 30);