SET search_path TO library, public;

-- 清理可能存在的旧数据（可选，测试时方便重跑）
-- 请注意，CASCADE 会删除关联数据，请谨慎在生产环境使用
DELETE FROM fines;
DELETE FROM reviews;
DELETE FROM reservations;
DELETE FROM borrow_records;
DELETE FROM admins;
DELETE FROM students;
DELETE FROM books;
DELETE FROM system_settings WHERE setting_key NOT IN ('fine_per_day', 'freeze_threshold', 'max_borrow_default'); -- 保留核心设置

-- 0. 系统参数 (你已插入，这里可以补充更多，如果需要)
INSERT INTO system_settings(setting_key, setting_value) VALUES
('max_renew_times', '2'),            -- 最大续借次数
('borrow_duration_days', '30'),      -- 默认借阅时长（天）
('reservation_expiry_days', '3'),  -- 预约保留天数
('admin_default_password_hash', 'xxxx'); -- 示例：管理员默认密码哈希
-- ON CONFLICT (setting_key) DO UPDATE SET setting_value = EXCLUDED.setting_value; -- 如果要允许更新

-- 1. 管理员信息 (Admins)
INSERT INTO admins (emp_no, name, position, phone, privilege_lv) VALUES
('A001', '张管理', '馆长', '13800138000', 0),  -- 最高权限
('A002', '李协助', '图书管理员', '13900139000', 1),
('A003', '王登记', '流通部职员', '13700137000', 2);

-- 2. 图书信息 (Books)
-- 注意: available_copies 初始设为 total_copies，后续借阅会通过触发器减少
INSERT INTO books (isbn, title, authors, publisher, publish_date, price, classification_no, location, total_copies, available_copies, status, description, cover_url) VALUES
('9787111624927', '深入理解计算机系统', ARRAY['Randal E. Bryant', 'David R. O''Hallaron'], '机械工业出版社', '2019-05-01', 139.00, 'TP301', 'A区2架3层', 10, 10, 'normal', '计算机系统的经典之作。', 'http://example.com/cover1.jpg'),
('9787030598007', '数学之美', ARRAY['吴军'], '科学出版社', '2020-01-01', 68.00, 'O1', 'B区1架1层', 15, 15, 'normal', '通俗易懂的数学科普读物。', 'http://example.com/cover2.jpg'),
('9787544270878', '百年孤独', ARRAY['加西亚·马尔克斯'], '南海出版公司', '2011-06-01', 39.50, 'I775.45', 'C区5架2层', 8, 8, 'normal', '魔幻现实主义文学的代表作。', 'http://example.com/cover3.jpg'),
('9787115478850', 'Python编程：从入门到实践', ARRAY['Eric Matthes'], '人民邮电出版社', '2018-03-01', 89.00, 'TP312PY', 'A区3架1层', 12, 12, 'normal', 'Python入门畅销书。', 'http://example.com/cover4.jpg'),
('9787508647009', '人类简史：从动物到上帝', ARRAY['尤瓦尔·赫拉利'], '中信出版社', '2014-11-01', 68.00, 'K02', 'D区1架1层', 20, 20, 'normal', '一部宏大的人类发展史。', 'http://example.com/cover5.jpg'),
('9787532767406', '追风筝的人', ARRAY['卡勒德·胡赛尼'], '上海人民出版社', '2006-05-01', 29.00, 'I712.45', 'C区4架3层', 7, 7, 'normal', '关于爱、友谊、背叛和救赎的故事。', 'http://example.com/cover6.jpg'),
('9787121362308', 'Java核心技术 卷I', ARRAY['Cay S. Horstmann'], '电子工业出版社', '2019-06-01', 128.00, 'TP312JA', 'A区3架2层', 5, 0, 'normal', 'Java经典教材，目前全部被借出，用于测试预约。', 'http://example.com/cover7.jpg'), -- 特意设置 available_copies 为 0
('9787208159659', '三体全集', ARRAY['刘慈欣'], '重庆出版社', '2019-01-01', 168.00, 'I247.5', 'C区6架1层', 3, 3, 'damaged', '科幻巨作，其中一本有损坏。', 'http://example.com/cover8.jpg'),
('9787559620187', '明朝那些事儿 (套装全7册)', ARRAY['当年明月'], '北京联合出版公司', '2017-08-01', 258.00, 'K248', 'D区2架1层', 6, 6, 'normal', '通俗易懂的明史。', 'http://example.com/cover9.jpg'),
('9787111600815', '算法导论 (原书第3版)', ARRAY['Thomas H. Cormen'], '机械工业出版社', '2012-12-01', 128.00, 'TP301.6', 'A区2架4层', 9, 9, 'normal', '算法领域的圣经。', 'http://example.com/cover10.jpg'),
('9780132350884', 'Clean Code', ARRAY['Robert C. Martin'], 'Prentice Hall', '2008-08-01', 50.00, 'TP311.1', 'A区1架5层', 4, 4, 'removed', '一本已下架的书，测试状态。', 'http://example.com/cover11.jpg');


-- 3. 学生信息 (Students)
-- max_borrow 使用默认值（通过 get_setting_int('max_borrow_default') 获取，即10）
-- current_borrow 初始为0，会由触发器更新
INSERT INTO students (stu_no, name, gender, department, major, grade, class, phone, email, account_status) VALUES
('S2023001', '张三', 'M', '计算机学院', '软件工程', '2023', '01班', '13512345671', 'zhangsan@example.com', 'active'),
('S2023002', '李四', 'F', '计算机学院', '人工智能', '2023', '02班', '13512345672', 'lisi@example.com', 'active'),
('S2022003', '王五', 'M', '外国语学院', '英语', '2022', '01班', '13512345673', 'wangwu@example.com', 'active'),
('S2021004', '赵六', 'F', '经济管理学院', '工商管理', '2021', '03班', '13512345674', 'zhaoliu@example.com', 'active'),
('S2023005', '孙七', 'M', '计算机学院', '软件工程', '2023', '01班', '13512345675', 'sunqi@example.com', 'reported'), -- 测试挂失状态
('S2022006', '周八', 'F', '人文学院', '历史学', '2022', '02班', '13512345676', 'zhouba@example.com', 'active'),
('S2021007', '吴九', 'M', '理学院', '数学与应用数学', '2021', '01班', '13512345677', 'wujiu@example.com', 'active'),
('S2023008', '郑十', 'F', '计算机学院', '网络工程', '2023', '03班', '13512345678', 'zhengshi@example.com', 'active');

-- 修改一个学生的 max_borrow，测试非默认值
UPDATE students SET max_borrow = 15 WHERE stu_no = 'S2023001';
UPDATE students SET max_borrow = 5 WHERE stu_no = 'S2021004'; -- 用于测试借阅上限

-- 4. 借阅记录 (Borrow Records)
-- book_id 和 student_id 需要引用已存在的 ID
-- due_date = borrow_date + (SELECT setting_value::int FROM system_settings WHERE setting_key = 'borrow_duration_days')
-- fine_amount 初始为0, 逾期归还将由触发器在 fines 表中记录罚款
-- 触发器 trg_sync_book_student 会在每次插入/更新/删除后执行

-- 获取 book_id 和 student_id 的示例 (实际使用时，你可能需要根据书名/学号查询得到)
-- SELECT book_id FROM books WHERE isbn = '9787111624927'; -- 1
-- SELECT student_id FROM students WHERE stu_no = 'S2023001'; -- 1

-- 借阅场景 1: 正常借出，未到期
INSERT INTO borrow_records (book_id, student_id, borrow_date, due_date, status) VALUES
(1, 1, current_date - interval '10 days', current_date - interval '10 days' + interval '30 days', 'borrowed'), -- 张三借《深入理解计算机系统》
(2, 2, current_date - interval '5 days', current_date - interval '5 days' + interval '30 days', 'borrowed');  -- 李四借《数学之美》

-- 借阅场景 2: 正常借出，今天到期
INSERT INTO borrow_records (book_id, student_id, borrow_date, due_date, status) VALUES
(3, 1, current_date - interval '30 days', current_date, 'borrowed'); -- 张三借《百年孤独》，今天到期

-- 借阅场景 3: 已逾期，未归还
INSERT INTO borrow_records (book_id, student_id, borrow_date, due_date, status) VALUES
(4, 3, current_date - interval '40 days', current_date - interval '10 days', 'overdue'); -- 王五借《Python编程》，已逾期10天

-- 借阅场景 4: 正常归还 (触发器 trg_calc_fine 不会产生罚款)
INSERT INTO borrow_records (book_id, student_id, borrow_date, due_date, return_date, status) VALUES
(5, 4, current_date - interval '20 days', current_date + interval '10 days', current_date - interval '2 days', 'returned'); -- 赵六借《人类简史》，已按时归还

-- 借阅场景 5: 逾期归还 (为了测试 trg_calc_fine，先插入为逾期，再UPDATE为归还)
-- 步骤A: 先插入一条记录，状态为 'overdue' (或者 'borrowed' 但实际已过 due_date)
INSERT INTO borrow_records (book_id, student_id, borrow_date, due_date, status)
VALUES (6, 5, current_date - interval '35 days', current_date - interval '5 days', 'overdue'); -- 孙七借《追风筝的人》
-- 记录下这条 borrow_id (假设是 6，根据实际情况调整)
-- 步骤B: 更新这条记录为 'returned'，这将触发 trg_calc_fine
-- UPDATE borrow_records
-- SET status = 'returned', return_date = current_date
-- WHERE borrow_id = (SELECT borrow_id FROM borrow_records WHERE student_id=5 AND book_id=6 AND status='overdue');
-- (该update放到下面演示触发器部分)

-- 借阅场景 6: 书籍遗失
INSERT INTO borrow_records (book_id, student_id, borrow_date, due_date, status) VALUES
(9, 6, current_date - interval '15 days', current_date + interval '15 days', 'lost'); -- 周八借《明朝那些事儿》，遗失


-- 5. 图书预约 (Reservations)
-- 只能预约 available_copies = 0 的书
-- (book_id=7, 'Java核心技术 卷I', available_copies 初始为0)
INSERT INTO reservations (book_id, student_id, reserve_date, status) VALUES
(7, 1, current_date - interval '2 days', 'waiting'), -- 张三预约《Java核心技术》
(7, 2, current_date - interval '1 day', 'waiting');   -- 李四预约《Java核心技术》

-- 假设一本被预约的书有归还了，管理员将其状态改为可取
-- UPDATE reservations SET status = 'available' WHERE book_id = 7 AND student_id = 1;

-- 6. 图书评价 (Reviews)
-- 学生通常评价已借阅过的书
INSERT INTO reviews (book_id, student_id, rating, content, review_time) VALUES
(5, 4, 5, '《人类简史》这本书太棒了，视角独特，值得一读！', now() - interval '1 day'), -- 赵六评价《人类简史》
(1, 1, 4, '《深入理解计算机系统》有点难，但很有收获。', now()), -- 张三评价
(2, 2, 5, '《数学之美》让我对数学有了新的认识。', now()); -- 李四评价

-- 尝试插入重复评价（应失败，因为有UNIQUE约束）
-- INSERT INTO reviews (book_id, student_id, rating, content, review_time) VALUES
-- (5, 4, 3, '第二次评价，内容一般。', now());

-- 7. 罚款记录 (Fines)
-- 部分罚款会由 trg_calc_fine 自动生成 (当逾期借阅记录更新为 'returned' 时)
-- 手动添加一些罚款记录：

-- 罚款场景1: 图书损坏 (假设管理员A002处理)
INSERT INTO fines (student_id, amount, reason, status, issue_date, admin_id) VALUES
( (SELECT student_id FROM students WHERE stu_no = 'S2022003'), -- 王五
  20.00,
  '损坏图书《Python编程》(ISBN:9787115478850)',
  'unpaid',
  current_date - interval '1 day',
  (SELECT admin_id FROM admins WHERE emp_no = 'A002')
);

-- 罚款场景2: 图书遗失 (假设管理员A002处理)
-- 对应 borrow_records 中周八遗失的《明朝那些事儿》
INSERT INTO fines (student_id, amount, reason, status, issue_date, admin_id) VALUES
( (SELECT student_id FROM students WHERE stu_no = 'S2022006'), -- 周八
  (SELECT price FROM books WHERE isbn = '9787559620187'), -- 按书价赔偿
  '遗失图书《明朝那些事儿》(ISBN:9787559620187)',
  'unpaid',
  current_date,
  (SELECT admin_id FROM admins WHERE emp_no = 'A002')
);

-- 罚款场景3: 之前的逾期罚款，已缴纳
INSERT INTO fines (student_id, amount, reason, status, issue_date, admin_id) VALUES
( (SELECT student_id FROM students WHERE stu_no = 'S2021007'), -- 吴九
  5.50,
  '图书逾期11天 (旧记录)',
  'paid',
  current_date - interval '60 days',
  (SELECT admin_id FROM admins WHERE emp_no = 'A003')
);


----------------------------------------------------
-- 测试触发器 trg_calc_fine 和 trg_freeze_account
----------------------------------------------------
DO $$
DECLARE
    v_student_id_sunqi bigint;
    v_borrow_id_sunqi bigint;
    v_fine_per_day numeric;
    v_days_overdue int;
    v_expected_fine numeric;
BEGIN
    -- 获取孙七的 student_id
    SELECT student_id INTO v_student_id_sunqi FROM students WHERE stu_no = 'S2023005';
    -- 获取孙七之前插入的逾期借阅记录 (book_id=6, 追风筝的人)
    SELECT borrow_id INTO v_borrow_id_sunqi
    FROM borrow_records
    WHERE student_id = v_student_id_sunqi AND book_id = (SELECT book_id FROM books WHERE isbn='9787532767406') AND status = 'overdue';

    RAISE NOTICE '孙七(ID:%)的借阅记录ID: % 将被更新为已归还。', v_student_id_sunqi, v_borrow_id_sunqi;

    -- 模拟孙七归还之前逾期的书 (book_id=6, 《追风筝的人》)
    -- 这将触发 trg_calc_fine
    UPDATE borrow_records
    SET status = 'returned', return_date = current_date
    WHERE borrow_id = v_borrow_id_sunqi;

    RAISE NOTICE '已更新孙七的借阅记录。现在检查 fines 表是否自动产生罚款记录...';
    -- trg_calc_fine 会根据 borrow_records 中的 due_date 和 return_date 计算罚款并插入 fines 表
    -- 假设罚款汇率是 0.5/天，逾期5天，罚款应为 2.50
    -- (current_date - (current_date - interval '5 days')) * 0.50
    SELECT setting_value::numeric INTO v_fine_per_day FROM system_settings WHERE setting_key = 'fine_per_day';
    SELECT (current_date - (SELECT due_date FROM borrow_records WHERE borrow_id = v_borrow_id_sunqi)) INTO v_days_overdue;
    v_expected_fine := v_days_overdue * v_fine_per_day;
    RAISE NOTICE '预期罚款金额: % * % = %', v_days_overdue, v_fine_per_day, v_expected_fine;

    -- 检查孙七的账户状态是否因为罚款超过阈值 (默认20) 而被冻结
    -- trg_freeze_account 会在 fines 表 INSERT/UPDATE/DELETE 后触发
    RAISE NOTICE '检查孙七账户是否因罚款自动冻结...';
    -- 如果孙七的罚款 (例如 2.50) 没有超过 freeze_threshold (默认20.00)，账户不会冻结。
    -- 我们再为孙七手动添加一笔大额罚款，使其总欠款超过阈值
    IF (SELECT sum(amount) FROM fines WHERE student_id = v_student_id_sunqi AND status = 'unpaid') < (SELECT setting_value::numeric FROM system_settings WHERE setting_key = 'freeze_threshold') THEN
        RAISE NOTICE '孙七当前未付罚款未达冻结阈值，为其添加一笔大额罚款...';
        INSERT INTO fines (student_id, amount, reason, status, issue_date, admin_id) VALUES
        (v_student_id_sunqi, 25.00, '严重损坏图书赔偿', 'unpaid', current_date, (SELECT admin_id FROM admins WHERE emp_no='A001'));
        RAISE NOTICE '已为孙七添加大额罚款，再次检查账户状态。';
    END IF;

END $$;