数据库大作业 — 数据库设计与实施

-- =============================================
-- 沉浸式剧本平台 — 核心表结构
-- 作者: Solivagant
-- =============================================

-- 1. 用户表
CREATE TABLE User (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(11) NOT NULL UNIQUE,
    nickname VARCHAR(50) NOT NULL,
    role_type TINYINT NOT NULL DEFAULT 0
        COMMENT '0-游客 1-运营 2-NPC 3-管理员',
    status TINYINT NOT NULL DEFAULT 0,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 剧本表
CREATE TABLE Script (
    script_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '剧本名称',
    description TEXT,
    difficulty TINYINT NOT NULL DEFAULT 1,
    duration INT NOT NULL COMMENT '时长（分）',
    min_players INT NOT NULL DEFAULT 1,
    max_players INT NOT NULL,
    cover_url VARCHAR(255),
    status TINYINT NOT NULL DEFAULT 0,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. 角色表
CREATE TABLE Role (
    role_id INT AUTO_INCREMENT PRIMARY KEY,
    script_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    description TEXT,
    is_key_role TINYINT NOT NULL DEFAULT 0,
    sort_order INT NOT NULL DEFAULT 0,
    FOREIGN KEY (script_id) REFERENCES Script(script_id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. 线索表
CREATE TABLE Clue (
    clue_id INT AUTO_INCREMENT PRIMARY KEY,
    script_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    content TEXT NOT NULL,
    clue_type TINYINT NOT NULL DEFAULT 0,
    trigger_type TINYINT NOT NULL DEFAULT 0,
    sequence_num INT NOT NULL,
    FOREIGN KEY (script_id) REFERENCES Script(script_id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. 打卡点表
CREATE TABLE CheckPoint (
    point_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    latitude DECIMAL(10,7) NOT NULL,
    longitude DECIMAL(10,7) NOT NULL,
    ar_content_url VARCHAR(255),
    binding_clue_id INT,
    is_required TINYINT NOT NULL DEFAULT 0,
    FOREIGN KEY (binding_clue_id) REFERENCES Clue(clue_id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 触发器1：订单支付 -> 场次人数自动更新
DELIMITER //
CREATE TRIGGER trg_order_payment_update_session
AFTER UPDATE ON `Order`
FOR EACH ROW
BEGIN
    IF OLD.payment_status = 0 AND NEW.payment_status = 1 THEN
        UPDATE `Session`
        SET current_players = current_players + 1
        WHERE session_id = NEW.session_id;
    END IF;
    IF OLD.payment_status = 1 AND NEW.payment_status = 2 THEN
        UPDATE `Session`
        SET current_players = current_players - 1
        WHERE session_id = NEW.session_id;
    END IF;
END//
DELIMITER ;

-- 触发器2：NPC排班时间冲突检测
DELIMITER //
CREATE TRIGGER trg_npcschedule_conflict_check
BEFORE INSERT ON NpcSchedule
FOR EACH ROW
BEGIN
    DECLARE conflict_count INT;
    SELECT COUNT(*) INTO conflict_count
    FROM NpcSchedule ns
    JOIN `Session` s ON ns.session_id = s.session_id
    JOIN `Session` s_new ON s_new.session_id = NEW.session_id
    WHERE ns.npc_id = NEW.npc_id
      AND ns.schedule_date = NEW.schedule_date
      AND (
          (s.start_time <= s_new.start_time AND s.end_time > s_new.start_time)
          OR
          (s_new.start_time <= s.start_time AND s_new.end_time > s.start_time)
      );
    IF conflict_count > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '该NPC在此时间段已有排班冲突';
    END IF;
END//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE proc_session_report(
    IN p_script_id INT,
    IN p_start_date DATE,
    IN p_end_date DATE
)
BEGIN
    DECLARE v_total_orders INT DEFAULT 0;
    DECLARE v_total_revenue DECIMAL(12,2) DEFAULT 0;

    SELECT COUNT(o.order_id), COALESCE(SUM(o.amount), 0)
    INTO v_total_orders, v_total_revenue
    FROM `Session` se
    LEFT JOIN `Order` o
        ON se.session_id = o.session_id AND o.payment_status = 1
    WHERE (p_script_id IS NULL OR se.script_id = p_script_id)
      AND DATE(se.start_time) BETWEEN p_start_date AND p_end_date;

    -- 汇总输出
    SELECT p_script_id AS script_id,
           p_start_date AS date_from, p_end_date AS date_to,
           v_total_orders AS paid_orders,
           v_total_revenue AS revenue;

    -- 逐场次明细
    SELECT s.name AS script_name, se.session_id,
           se.start_time,
           CONCAT(se.current_players, '/', se.max_players) AS occupancy,
           ROUND(se.current_players / se.max_players * 100, 1) AS rate,
           se.price,
           COUNT(o.order_id) AS order_count,
           COALESCE(SUM(o.amount), 0) AS session_revenue
    FROM `Session` se
    JOIN Script s ON se.script_id = s.script_id
    LEFT JOIN `Order` o
        ON se.session_id = o.session_id AND o.payment_status = 1
    WHERE (p_script_id IS NULL OR se.script_id = p_script_id)
      AND DATE(se.start_time) BETWEEN p_start_date AND p_end_date
    GROUP BY se.session_id
    ORDER BY se.start_time;
END//
DELIMITER ;

-- 调用：CALL proc_session_report(1, '2026-06-01', '2026-06-30');