csdesign-smart-library/sql/user_query.sql

-- 假设当前操作的学生是 张三 (学号 S2023001)，我们先获取其 student_id
-- 在实际应用中，这个 ID 通常来自登录会话
DO $$
DECLARE
    v_student_id bigint;
BEGIN
    SELECT student_id INTO v_student_id FROM students WHERE stu_no = 'S2023001';
    -- 将 v_student_id 设为会话级变量，方便后续查询使用
    -- 注意: 这是一种在 psql 或特定SQL工具中设置变量的方式，
    -- 在应用代码中，你会直接使用从会话中获取的 student_id 变量。
    EXECUTE 'SET app.current_student_id = ' || v_student_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE NOTICE '学生 S2023001 未找到。请确保测试数据已插入。';
        -- 或者你可以在这里直接硬编码一个存在的 student_id 用于测试
        -- EXECUTE 'SET app.current_student_id = 1';
END $$;

-- 如果上面的 DO $$ 块由于环境限制无法执行 SET app.current_student_id，
-- 后续查询中请手动替换 current_setting('app.current_student_id')::bigint 为实际的 student_id (例如 1)

-- (1). 多条件组合查询图书
RAISE NOTICE '---------- (1) 多条件组合查询图书 ----------';

-- 按书名模糊查询 (使用 pg_trgm GIN 索引)
RAISE NOTICE '-- 按书名模糊查询 "计算机":';
SELECT book_id, isbn, title, authors, available_copies
FROM books
WHERE title LIKE '%计算机%' AND status = 'normal';

RAISE NOTICE '-- 按书名精确查询 (如果知道全名):';
SELECT book_id, isbn, title, authors, available_copies
FROM books
WHERE title = '数学之美' AND status = 'normal';

-- 按作者查询 (使用 GIN 索引)
RAISE NOTICE '-- 按作者查询包含 "吴军":';
SELECT book_id, isbn, title, authors, available_copies
FROM books
WHERE authors @> ARRAY['吴军'] AND status = 'normal'; -- '@>' 表示数组包含

-- 按分类号查询
RAISE NOTICE '-- 按分类号查询 "TP301":';
SELECT book_id, isbn, title, authors, classification_no, available_copies
FROM books
WHERE classification_no = 'TP301' AND status = 'normal';

-- 按ISBN精确查询
RAISE NOTICE '-- 按ISBN查询 "9787111624927":';
SELECT book_id, isbn, title, authors, available_copies
FROM books
WHERE isbn = '9787111624927' AND status = 'normal';

-- 组合查询：书名包含 "编程" 且 作者包含 "Eric Matthes"
RAISE NOTICE '-- 组合查询：书名包含 "编程" 且 作者包含 "Eric Matthes":';
SELECT book_id, isbn, title, authors, available_copies
FROM books
WHERE title LIKE '%编程%' AND authors @> ARRAY['Eric Matthes'] AND status = 'normal';

-- (2). 查看图书详情及当前可借状态
RAISE NOTICE '---------- (2) 查看图书详情及当前可借状态 ----------';
-- 假设查看 ISBN 为 '9787111624927' (深入理解计算机系统) 的图书详情
-- 首先获取 book_id
DO $$
DECLARE
    v_book_id bigint;
BEGIN
    SELECT book_id INTO v_book_id FROM books WHERE isbn = '9787111624927';
    EXECUTE 'SET app.current_book_id = ' || v_book_id;
END $$;

RAISE NOTICE '-- 查看图书 (ISBN: 9787111624927) 的详情:';
SELECT
    book_id,
    isbn,
    title,
    authors,
    publisher,
    publish_date,
    price,
    classification_no,
    location,
    total_copies,
    available_copies,
    status,
    description,
    cover_url,
    CASE
        WHEN status = 'normal' AND available_copies > 0 THEN '可借阅'
        WHEN status = 'normal' AND available_copies = 0 THEN '已全部借出'
        ELSE '不可借阅 (' || status::text || ')'
    END as borrow_availability
FROM books
WHERE book_id = current_setting('app.current_book_id')::bigint;

-- (3). 在线预约热门图书（当所有副本都被借出时）
RAISE NOTICE '---------- (3) 在线预约热门图书 ----------';
-- 假设要预约的书是 'Java核心技术 卷I' (book_id=7)，其 available_copies 在测试数据中为0
-- 首先确认该书确实没有可借副本
DO $$
DECLARE
    v_target_book_id bigint := 7; -- Java核心技术 卷I
    v_available int;
BEGIN
    SELECT available_copies INTO v_available FROM books WHERE book_id = v_target_book_id AND status = 'normal';
    IF v_available = 0 THEN
        RAISE NOTICE '-- 图书ID % (Java核心技术 卷I) 当前无可用副本，可以进行预约。';
        -- 学生张三 (student_id=1) 进行预约
        -- 检查是否已预约过
        IF NOT EXISTS (SELECT 1 FROM reservations
                       WHERE book_id = v_target_book_id
                         AND student_id = current_setting('app.current_student_id')::bigint
                         AND status IN ('waiting', 'available'))
        THEN
            INSERT INTO reservations (book_id, student_id, reserve_date, status)
            VALUES (v_target_book_id, current_setting('app.current_student_id')::bigint, current_date, 'waiting');
            RAISE NOTICE '-- 学生ID % 已成功预约图书ID %。', current_setting('app.current_student_id')::bigint, v_target_book_id;
        ELSE
            RAISE NOTICE '-- 学生ID % 已预约过图书ID % 或预约已可取。', current_setting('app.current_student_id')::bigint, v_target_book_id;
        END IF;
    ELSE
        RAISE NOTICE '-- 图书ID % 当前有 % 本可借，无需预约。', v_target_book_id, v_available;
    END IF;
END $$;

-- 查看张三的预约记录来确认
RAISE NOTICE '-- 查看学生张三的当前预约:';
SELECT r.reservation_id, b.title, r.reserve_date, r.status
FROM reservations r
JOIN books b ON r.book_id = b.book_id
WHERE r.student_id = current_setting('app.current_student_id')::bigint AND r.status IN ('waiting', 'available');


-- (4). 查看个人借阅历史、当前借阅情况和预约状态
RAISE NOTICE '---------- (4) 查看个人借阅历史、当前借阅情况和预约状态 ----------';

RAISE NOTICE '-- 学生张三 (ID: %) 的当前借阅情况:';
SELECT br.borrow_id, b.title, b.isbn, br.borrow_date, br.due_date, br.renew_times, br.status
FROM borrow_records br
JOIN books b ON br.book_id = b.book_id
WHERE br.student_id = current_setting('app.current_student_id')::bigint
  AND br.status IN ('borrowed', 'overdue');

RAISE NOTICE '-- 学生张三 (ID: %) 的借阅历史 (已还):';
SELECT br.borrow_id, b.title, b.isbn, br.borrow_date, br.return_date, br.status
FROM borrow_records br
JOIN books b ON br.book_id = b.book_id
WHERE br.student_id = current_setting('app.current_student_id')::bigint
  AND br.status = 'returned';

RAISE NOTICE '-- 学生张三 (ID: %) 的当前预约状态:'; -- 重复上面的预约查询，为了完整性
SELECT r.reservation_id, b.title, r.reserve_date, r.status
FROM reservations r
JOIN books b ON r.book_id = b.book_id
WHERE r.student_id = current_setting('app.current_student_id')::bigint AND r.status IN ('waiting', 'available');

-- (5). 在线续借图书（有限制条件）
RAISE NOTICE '---------- (5) 在线续借图书 ----------';
-- 假设张三要续借他借阅的《深入理解计算机系统》(book_id=1)
-- borrow_id 需要从他的当前借阅中找到，假设其 borrow_id 为 1 (根据你的测试数据)
DO $$
DECLARE
    v_borrow_id_to_renew bigint;
    v_student_id bigint := current_setting('app.current_student_id')::bigint;
    v_book_id_of_borrow bigint;
    v_max_renew_times int;
    v_borrow_duration_days int;
    v_current_renew_times int;
    v_current_due_date date;
    v_current_status borrow_status_enum;
    v_is_reserved boolean;
BEGIN
    -- 找到张三正在借阅的《深入理解计算机系统》的 borrow_id
    SELECT br.borrow_id, br.book_id, br.renew_times, br.due_date, br.status
    INTO v_borrow_id_to_renew, v_book_id_of_borrow, v_current_renew_times, v_current_due_date, v_current_status
    FROM borrow_records br
    JOIN books b ON br.book_id = b.book_id
    WHERE br.student_id = v_student_id
      AND b.isbn = '9787111624927' -- 《深入理解计算机系统》
      AND br.status = 'borrowed'
    LIMIT 1;

    IF NOT FOUND THEN
        RAISE NOTICE '-- 未找到学生ID % 对图书ISBN 9787111624927 的可续借记录。';
        RETURN;
    END IF;

    RAISE NOTICE '-- 尝试为学生ID % 续借 borrow_id % (Book ID: %)', v_student_id, v_borrow_id_to_renew, v_book_id_of_borrow;

    -- 获取系统参数
    SELECT setting_value::int INTO v_max_renew_times FROM system_settings WHERE setting_key = 'max_renew_times';
    SELECT setting_value::int INTO v_borrow_duration_days FROM system_settings WHERE setting_key = 'borrow_duration_days';

    -- 检查条件：
    -- 1. 状态为 'borrowed' (不能是 'overdue' 或 'lost')
    IF v_current_status != 'borrowed' THEN
        RAISE NOTICE '-- 续借失败: 图书状态为 %，不是 "borrowed"。', v_current_status;
        RETURN;
    END IF;

    -- 2. 续借次数未达上限
    IF v_current_renew_times >= v_max_renew_times THEN
        RAISE NOTICE '-- 续借失败: 已达到最大续借次数 (%)。', v_max_renew_times;
        RETURN;
    END IF;

    -- 3. 该书没有其他人预约
    SELECT EXISTS (SELECT 1 FROM reservations WHERE book_id = v_book_id_of_borrow AND status = 'waiting')
    INTO v_is_reserved;
    IF v_is_reserved THEN
        RAISE NOTICE '-- 续借失败: 该图书已被其他用户预约。';
        RETURN;
    END IF;

    -- 执行续借
    UPDATE borrow_records
    SET due_date = v_current_due_date + (v_borrow_duration_days || ' days')::interval, -- 从原应还日期开始计算
        renew_times = renew_times + 1
    WHERE borrow_id = v_borrow_id_to_renew;

    RAISE NOTICE '-- 续借成功! borrow_id: %, 新的应还日期: %, 当前续借次数: %',
                 v_borrow_id_to_renew,
                 (SELECT due_date FROM borrow_records WHERE borrow_id = v_borrow_id_to_renew),
                 (SELECT renew_times FROM borrow_records WHERE borrow_id = v_borrow_id_to_renew);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE NOTICE '-- 未找到相关借阅记录或系统参数。';
    WHEN OTHERS THEN
        RAISE NOTICE '续借操作发生错误: %', SQLERRM;
END $$;

-- (6). 查看和缴纳罚款
RAISE NOTICE '---------- (6) 查看和缴纳罚款 ----------';
-- 假设学生王五 (S2022003, student_id=3) 有未缴罚款
DO $$
DECLARE
    v_student_id_wangwu bigint;
    v_fine_id_to_pay bigint;
    v_amount_to_pay numeric;
BEGIN
    SELECT student_id INTO v_student_id_wangwu FROM students WHERE stu_no = 'S2022003';
    IF NOT FOUND THEN
        RAISE NOTICE '学生 S2022003 未找到。';
        RETURN;
    END IF;

    RAISE NOTICE '-- 查看学生王五 (ID: %) 的未缴罚款:';
    SELECT fine_id, amount, reason, status, issue_date
    FROM fines
    WHERE student_id = v_student_id_wangwu AND status = 'unpaid';

    -- 假设王五要缴纳 fine_id 为 (从上面查询结果中选取一个，例如第一个未缴罚款)
    SELECT fine_id, amount INTO v_fine_id_to_pay, v_amount_to_pay
    FROM fines
    WHERE student_id = v_student_id_wangwu AND status = 'unpaid'
    ORDER BY issue_date
    LIMIT 1;

    IF v_fine_id_to_pay IS NOT NULL THEN
        RAISE NOTICE '-- 模拟缴纳学生王五 (ID: %) 的罚款 fine_id: %, 金额: %', v_student_id_wangwu, v_fine_id_to_pay, v_amount_to_pay;
        UPDATE fines
        SET status = 'paid'
        WHERE fine_id = v_fine_id_to_pay;
        -- 触发器 trg_freeze_account 会在此 UPDATE 后执行，可能解冻账户

        RAISE NOTICE '-- 缴纳后，再次查看学生王五 (ID: %) 的未缴罚款:';
        SELECT fine_id, amount, reason, status, issue_date
        FROM fines
        WHERE student_id = v_student_id_wangwu AND status = 'unpaid';

        RAISE NOTICE '-- 查看学生王五 (ID: %) 的账户状态 (可能已因罚款缴纳而解冻):';
        SELECT stu_no, name, account_status FROM students WHERE student_id = v_student_id_wangwu;
    ELSE
        RAISE NOTICE '-- 学生王五 (ID: %) 没有未缴罚款可供缴纳。';
    END IF;
END $$;

-- (7). 对已借阅图书进行评分和评论
RAISE NOTICE '---------- (7) 对已借阅图书进行评分和评论 ----------';
-- 假设张三 (student_id=1) 要对他借阅过的《深入理解计算机系统》(book_id=1)进行评价
-- （假设他已经借过或者正在借阅）
DO $$
DECLARE
    v_student_id bigint := current_setting('app.current_student_id')::bigint;
    v_book_id_to_review bigint;
BEGIN
    SELECT book_id INTO v_book_id_to_review FROM books WHERE isbn = '9787111624927'; -- 深入理解计算机系统

    -- 检查是否已经评论过 (因为有 UNIQUE(book_id, student_id) 约束)
    IF EXISTS (SELECT 1 FROM reviews WHERE student_id = v_student_id AND book_id = v_book_id_to_review) THEN
        RAISE NOTICE '-- 学生ID % 已评价过图书ID %。';
        UPDATE reviews
        SET rating = 5, content = '更新评价：太经典了，常看常新！', review_time = now()
        WHERE student_id = v_student_id AND book_id = v_book_id_to_review;
        RAISE NOTICE '-- 已更新学生ID % 对图书ID % 的评价。';
    ELSE
        -- 应用层面通常会检查该学生是否实际借阅过这本书，这里我们直接插入
        INSERT INTO reviews (book_id, student_id, rating, content, review_time)
        VALUES (v_book_id_to_review, v_student_id, 5, '这本书写得太好了，深入浅出，对我帮助很大！', now());
        RAISE NOTICE '-- 学生ID % 已成功评价图书ID %。';
    END IF;

    RAISE NOTICE '-- 查看图书ID % 的所有评价:';
    SELECT s.name as student_name, r.rating, r.content, r.review_time
    FROM reviews r
    JOIN students s ON r.student_id = s.student_id
    WHERE r.book_id = v_book_id_to_review
    ORDER BY r.review_time DESC;
END $$;

-- (8). 查看图书推荐（如基于热门被借阅图书）
RAISE NOTICE '---------- (8) 查看图书推荐（热门图书） ----------';
RAISE NOTICE '-- 查看当前热门图书 (基于 v_hot_books 视图):';
SELECT b.isbn, b.title, b.authors, vhb.borrow_cnt
FROM v_hot_books vhb
JOIN books b ON vhb.book_id = b.book_id
ORDER BY vhb.borrow_cnt DESC;

-- 另一种推荐思路：基于用户所在院系的热门图书
RAISE NOTICE '-- 查看学生张三所在院系的热门图书 (示例):';
WITH student_department AS (
    SELECT department FROM students WHERE student_id = current_setting('app.current_student_id')::bigint
),
department_borrows AS (
    SELECT
        br.book_id,
        COUNT(*) as dept_borrow_count
    FROM borrow_records br
    JOIN students s ON br.student_id = s.student_id
    WHERE s.department = (SELECT department FROM student_department)
    GROUP BY br.book_id
)
SELECT b.title, b.authors, db.dept_borrow_count
FROM department_borrows db
JOIN books b ON db.book_id = b.book_id
ORDER BY db.dept_borrow_count DESC
LIMIT 10;

RAISE NOTICE '---------- 学生功能SQL示例演示完毕 ----------';