app.py

from flask import Flask, render_template, request, redirect, url_for, session, flash
from db import execute_query, call_procedure
from werkzeug.security import generate_password_hash, check_password_hash
import datetime
import os
from flask_wtf import CSRFProtect

app = Flask(__name__)
# prefer SECRET_KEY from environment for production; fallback to inline secret
app.secret_key = os.getenv('SECRET_KEY', "replace-with-a-secure-secret")
app.config['SECRET_KEY'] = app.secret_key
csrf = CSRFProtect(app)
from flask_wtf.csrf import CSRFError
from functools import wraps


# Gracefully handle CSRF errors and show a friendly message
@app.errorhandler(CSRFError)
def handle_csrf_error(e):
    flash('无效的或缺失的 CSRF 令牌，请重试或刷新页面。', 'error')
    return redirect(request.referrer or url_for('index'))

# 装饰器：要求登录
def login_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if not session.get('user_id'):
            flash('请先登录', 'info')
            return redirect(url_for('login'))
        return f(*args, **kwargs)
    return decorated_function

# 装饰器：要求角色
def role_required(role):
    def decorator(f):
        @wraps(f)
        def decorated_function(*args, **kwargs):
            if session.get('role') != role:
                flash('权限不足：需要{}角色'.format(role), 'error')
                return redirect(url_for('login'))
            return f(*args, **kwargs)
        return decorated_function
    return decorator

# 根路径重定向到登录页，避免 404
@app.route('/', methods=['GET'])
def index():
    return redirect(url_for('login'))

# 简易登录，不做加密校验，仅演示数据库角色驱动的调用
@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        user = execute_query(
            "SELECT user_id, username, role, password_hash FROM users WHERE username = %s",
            params=(username,),
            fetchone=True,
        )
        # validate password: only support hashed passwords
        if user:
            db_password = user.get('password_hash')
            valid = bool(db_password and check_password_hash(db_password, password))
        else:
            valid = False
        if valid:
            session['user_id'] = user['user_id']
            session['username'] = user['username']
            session['role'] = user['role']
            # 登录后按角色跳转
            if user['role'] == 'admin':
                return redirect(url_for('admin_home'))
            else:
                return redirect(url_for('reader_home'))
        flash('登录失败：用户名或密码不正确', 'error')
    return render_template('login.html', username=session.get('username'), role=session.get('role'))


# 读者首页
@app.route('/reader/home', methods=['GET'])
@login_required
@role_required('reader')
def reader_home():
    user_id = session.get('user_id')
    summary = execute_query(
        "SELECT current_borrow_count, current_overdue_count, reservations FROM view_user_home_stats WHERE user_id = %s",
        params=(user_id,),
        fetchone=True,
    ) or {'current_borrow_count': 0, 'current_overdue_count': 0, 'reservations': 0}
    data = summary
    return render_template('reader_home.html', username=session.get('username'), role=session.get('role'), stats=data)

# 注册
@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        full_name = request.form.get('full_name')
        email = request.form.get('email')
        phone = request.form.get('phone')
        gender = request.form.get('gender') or 'unspecified'
        if not username or not password:
            flash('用户名和密码不能为空', 'error')
            return redirect(url_for('register'))
        # 检查用户名是否存在
        existing = execute_query("SELECT user_id FROM users WHERE username = %s", params=(username,), fetchone=True)
        if existing:
            flash('用户名已存在', 'error')
            return redirect(url_for('register'))
        # Hash the password before storing
        pwd_hash = generate_password_hash(password)
        try:
            from db import execute_non_query
            execute_non_query(
                "INSERT INTO users(username, password_hash, role, full_name, email, phone, gender) VALUES (%s, %s, %s, %s, %s, %s, %s)",
                params=(username, pwd_hash, 'reader', full_name, email, phone, gender),
            )
            flash('注册成功，请登录', 'success')
            return redirect(url_for('login'))
        except Exception as e:
            flash(f'注册失败：{str(e)}', 'error')
            return redirect(url_for('register'))
    return render_template('register.html', username=session.get('username'), role=session.get('role'))


# 图书列表与搜索（读者）
@app.route('/books', methods=['GET', 'POST'])
@login_required
@role_required('reader')
def books():

    user_id = session.get('user_id')
    # 预约操作
    if request.method == 'POST':
        book_id = request.form.get('book_id')
        try:
            call_procedure('reserve_book', [user_id, book_id])
            flash('预约成功', 'success')
        except Exception as e:
            flash(f'预约失败：{str(e)}', 'error')
        return redirect(url_for('books'))

    # GET：读取搜索参数
    title = request.args.get('title', '').strip()
    author = request.args.get('author', '').strip()
    category = request.args.get('category', '').strip()
    isbn = request.args.get('isbn', '').strip()

    # 使用书目大宽表视图简化查询
    base_sql = (
        "SELECT book_id, title, authors, category, publisher, isbn, total_copies, available_copies "
        "FROM view_book_catalog "
        "WHERE 1=1 "
    )
    params = []
    if title:
        base_sql += " AND title ILIKE %s"
        params.append(f"%{title}%")
    if author:
        base_sql += " AND authors ILIKE %s"
        params.append(f"%{author}%")
    if category:
        base_sql += " AND category ILIKE %s"
        params.append(f"%{category}%")
    if isbn:
        base_sql += " AND isbn ILIKE %s"
        params.append(f"%{isbn}%")
    base_sql += " ORDER BY title"

    books = execute_query(base_sql, params=tuple(params) if params else None, fetchall=True)
    return render_template('books.html', username=session.get('username'), role=session.get('role'), books=books, title=title, author=author, category=category, isbn=isbn)

# 退出登录
@app.route('/logout', methods=['GET'])
@login_required
def logout():
    session.clear()
    flash('已登出', 'info')
    return redirect(url_for('login'))

# 借阅图书（读者）
@app.route('/borrow-by-book/<int:book_id>', methods=['POST'])
@login_required
@role_required('reader')
def borrow_by_book(book_id):
    user_id = session.get('user_id')
    # 查找任意可借副本（第一条）
    row = execute_query(
        "SELECT copy_id FROM book_copies WHERE book_id = %s AND status = 'available' ORDER BY copy_id LIMIT 1",
        params=(book_id,),
        fetchone=True,
    )
    if not row:
        flash('当前没有可借副本，无法借阅', 'error')
        return redirect(url_for('book_details', book_id=book_id))
    copy_id = row.get('copy_id')

    # 先检查是否有逾期未还
    overdue_row = execute_query(
        "SELECT COUNT(*) AS overdue FROM view_overdue_records WHERE user_id = %s",
        params=(user_id,),
        fetchone=True,
    ) or {'overdue': 0}
    if overdue_row.get('overdue', 0) > 0:
        flash('无法借书：您有超期未还的图书，请先归还超期书籍后再进行借阅。', 'error')
        return redirect(url_for('book_details', book_id=book_id))

    try:
        call_procedure('borrow_book', [user_id, copy_id])
        flash('借阅成功', 'success')
    except Exception as e:
        _msg = str(e)
        _idx = _msg.find('CONTEXT:')
        if _idx != -1:
            _msg = _msg[:_idx].strip()
        flash(f'借阅失败：{_msg}', 'error')
    return redirect(url_for('book_details', book_id=book_id))


# 归还图书（读者）
@app.route('/return/<int:borrow_id>', methods=['POST'])
@login_required
@role_required('reader')
def return_book_route(borrow_id):
    try:
        # 调用存储过程 return_book
        call_procedure('return_book', [borrow_id])
        flash('归还成功', 'success')
    except Exception as e:
        flash(f'归还失败：{str(e)}', 'error')
    return redirect(url_for('my_borrows'))


# 借阅记录页面（读者）
@app.route('/my-borrows', methods=['GET'])
@login_required
@role_required('reader')
def my_borrows():
    user_id = session.get('user_id')
    rows = execute_query(
        "SELECT borrow_id, copy_id, borrow_date, due_date, return_date, status, title, authors "
        "FROM view_borrow_details "
        "WHERE user_id = %s ORDER BY borrow_date DESC",
        params=(user_id,),
        fetchall=True,
    ) or []

    # 后端格式化日期为 YYYY-MM-DD（兼容 date/datetime 或字符串）
    def _fmt_date(val):
        if not val:
            return None
        if isinstance(val, (datetime.date, datetime.datetime)):
            return val.strftime('%Y-%m-%d')
        if isinstance(val, str):
            return val.split(' ')[0]
        return str(val)

    for r in rows:
        r['borrow_date'] = _fmt_date(r.get('borrow_date'))
        r['due_date'] = _fmt_date(r.get('due_date'))
        # 保持未归还记录的 return_date 为 None，以便模板区分是否归还
        r['return_date'] = _fmt_date(r.get('return_date'))
        # 方便模板判断是否逾期
        r['is_overdue'] = (r.get('status') == 'overdue')

    # 将记录拆分为“当前借阅”（未归还）和“借阅历史”（已归还）
    current = [r for r in rows if r.get('status') != 'returned']
    history = [r for r in rows if r.get('status') == 'returned']

    return render_template('my_borrows.html', username=session.get('username'), role=session.get('role'), current_borrows=current, history_borrows=history)

# 管理员首页
@app.route('/admin/home', methods=['GET'])
@login_required
@role_required('admin')
def admin_home():
    data = execute_query("SELECT * FROM view_admin_home_stats", fetchone=True) or {}
    return render_template('admin_home.html', username=session.get('username'), role=session.get('role'), stats=data)

# 用户个人信息页面
@app.route('/profile', methods=['GET', 'POST'])
@login_required
def profile():
    user_id = session.get('user_id')
    if request.method == 'POST':
        # 更新用户信息（当前用户）
        full_name = request.form.get('full_name')
        email = request.form.get('email')
        phone = request.form.get('phone')
        gender = request.form.get('gender')
        bio = request.form.get('bio')
        from db import execute_non_query
        try:
            execute_non_query(
                "UPDATE users SET full_name=%s, email=%s, phone=%s, gender=%s, bio=%s WHERE user_id=%s",
                params=(full_name, email, phone, gender, bio, user_id),
            )
            flash('个人信息已更新', 'success')
        except Exception as e:
            flash(f'更新失败：{str(e)}', 'error')
        return redirect(url_for('profile'))
    # GET：显示信息
    user = execute_query("SELECT user_id, username, role, full_name, email, phone, gender, bio FROM users WHERE user_id = %s", params=(user_id,), fetchone=True)
    return render_template('profile.html', username=session.get('username'), role=session.get('role'), user=user)

# 图书管理页面（管理员）
@app.route('/admin/books', methods=['GET'])
@login_required
@role_required('admin')
def admin_books():
    # 支持按 title/author/category/isbn 查询
    title = request.args.get('title', '').strip()
    author = request.args.get('author', '').strip()
    category = request.args.get('category', '').strip()
    isbn = request.args.get('isbn', '').strip()

    sql = "SELECT book_id, title, authors, category, publisher, isbn, total_copies, available_copies FROM view_book_catalog"
    conditions = []
    params = []
    if title:
        conditions.append("title ILIKE %s")
        params.append(f"%{title}%")
    if author:
        conditions.append("authors ILIKE %s")
        params.append(f"%{author}%")
    if category:
        conditions.append("category ILIKE %s")
        params.append(f"%{category}%")
    if isbn:
        conditions.append("isbn ILIKE %s")
        params.append(f"%{isbn}%")
    if conditions:
        sql += " WHERE " + " AND ".join(conditions)
    sql += " ORDER BY book_id DESC"

    rows = execute_query(sql, params=tuple(params), fetchall=True) or []
    categories = execute_query("SELECT category_id, name FROM categories ORDER BY name", fetchall=True) or []
    publishers = execute_query("SELECT publisher_id, name FROM publishers ORDER BY name", fetchall=True) or []
    return render_template('admin_books.html', username=session.get('username'), role=session.get('role'), books=rows, categories=categories, publishers=publishers)

# 新增图书（管理员）
@app.route('/admin/books', methods=['POST'])
@login_required
@role_required('admin')
def admin_books_create():
    title = request.form.get('title')
    authors_input = request.form.get('authors') or ''
    category_name = request.form.get('category_name')
    publisher_name = request.form.get('publisher_name')
    isbn = request.form.get('isbn')
    try:
        # 通过存储过程原子化执行全部创建逻辑
        call_procedure('create_book', [title, category_name, publisher_name, isbn, authors_input])
        flash('新增图书成功', 'success')
    except Exception as e:
        flash(f'新增图书失败：{str(e)}', 'error')
    return redirect(url_for('admin_books'))


# 新增图书页面（管理员）
@app.route('/admin/books/new', methods=['GET'])
@login_required
@role_required('admin')
def admin_books_new():
    return render_template('admin_books_new.html', username=session.get('username'), role=session.get('role'))

# 书籍详情页面（读者）
@app.route('/books/<int:book_id>', methods=['GET'])
@login_required
@role_required('reader')
def book_details(book_id):
    book = execute_query(
        "SELECT book_id, title, authors, category, publisher, isbn, total_copies, available_copies FROM view_book_catalog WHERE book_id = %s",
        params=(book_id,),
        fetchone=True,
    )
    # 使用 view_book_catalog 中已聚合的字段，避免额外的两次 COUNT 查询
    total_copies = book.get('total_copies', 0) if book else 0
    available_count = book.get('available_copies', 0) if book else 0

    # 检查用户是否有逾期未还（用于前端禁用借书按钮并给出友好提示）
    user_has_overdue = False
    user_has_reservation = False
    user_id = session.get('user_id')
    if user_id:
        overdue_row = execute_query(
            "SELECT COUNT(*) AS overdue FROM view_overdue_records WHERE user_id = %s",
            params=(user_id,),
            fetchone=True,
        ) or {'overdue': 0}
        user_has_overdue = (overdue_row.get('overdue', 0) > 0)
        # 检查是否已预约该书（waiting 或 notified）
        res = execute_query(
            "SELECT 1 FROM reservations WHERE user_id = %s AND book_id = %s AND status IN ('waiting','notified')",
            params=(user_id, book_id),
            fetchone=True,
        )
        user_has_reservation = bool(res)

    # 获取副本详情供管理员与读者查看
    copies = execute_query(
        "SELECT copy_id, status, location, reserved_for_user_id, reserved_for_username FROM view_copy_details WHERE book_id = %s ORDER BY copy_id",
        params=(book_id,),
        fetchall=True,
    ) or []

    # 计算该书当前预约排队人数（waiting 或 notified 状态）
    reservation_row = execute_query(
        "SELECT COUNT(*) AS cnt FROM reservations WHERE book_id = %s AND status IN ('waiting','notified')",
        params=(book_id,),
        fetchone=True,
    ) or {'cnt': 0}
    reservation_count = reservation_row.get('cnt', 0)

    # 单独查询该书的作者列表（包含ID），用于前端生成作者链接
    authors_data = execute_query(
        "SELECT a.author_id, a.name FROM authors a JOIN book_authors ba ON a.author_id = ba.author_id WHERE ba.book_id = %s ORDER BY ba.display_order",
        params=(book_id,),
        fetchall=True,
    ) or []

    return render_template('book_details.html', username=session.get('username'), role=session.get('role'), book=book, authors_list=authors_data, total_copies=total_copies, available_count=available_count, user_has_overdue=user_has_overdue, user_has_reservation=user_has_reservation, copies=copies, reservation_count=reservation_count)

# 书籍详情页面（管理员） - 用于副本管理等操作
@app.route('/admin/books/<int:book_id>', methods=['GET'])
@login_required
@role_required('admin')
def admin_book_details(book_id):
    book = execute_query(
        "SELECT book_id, title, authors, category, publisher, isbn, total_copies, available_copies FROM view_book_catalog WHERE book_id = %s",
        params=(book_id,),
        fetchone=True,
    )
    total_copies = book.get('total_copies', 0) if book else 0
    available_count = book.get('available_copies', 0) if book else 0

    # 获取副本详情供管理员与读者查看
    copies = execute_query(
        "SELECT copy_id, status, location, reserved_for_user_id, reserved_for_username FROM view_copy_details WHERE book_id = %s ORDER BY copy_id",
        params=(book_id,),
        fetchall=True,
    ) or []

    reservation_row = execute_query(
        "SELECT COUNT(*) AS cnt FROM reservations WHERE book_id = %s AND status IN ('waiting','notified')",
        params=(book_id,),
        fetchone=True,
    ) or {'cnt': 0}
    reservation_count = reservation_row.get('cnt', 0)

    authors_data = execute_query(
        "SELECT a.author_id, a.name FROM authors a JOIN book_authors ba ON a.author_id = ba.author_id WHERE ba.book_id = %s ORDER BY ba.display_order",
        params=(book_id,),
        fetchall=True,
    ) or []

    return render_template('book_details.html', username=session.get('username'), role=session.get('role'), book=book, authors_list=authors_data, total_copies=total_copies, available_count=available_count, copies=copies, reservation_count=reservation_count)


# 管理员：为指定书籍新增副本
@app.route('/admin/books/<int:book_id>/copies', methods=['POST'])
@login_required
@role_required('admin')
def admin_add_copy(book_id):
    location = request.form.get('location')
    try:
        from db import execute_non_query
        execute_non_query("INSERT INTO book_copies (book_id, location) VALUES (%s, %s)", params=(book_id, location))
        flash('副本新增成功', 'success')
    except Exception as e:
        flash(f'副本新增失败：{str(e)}', 'error')
    return redirect(url_for('admin_book_details', book_id=book_id))


# 管理员：删除指定副本
@app.route('/admin/copies/<int:copy_id>/delete', methods=['POST'])
@login_required
@role_required('admin')
def admin_delete_copy(copy_id):
    try:
        # 获取所属书籍以便重定向
        row = execute_query("SELECT book_id FROM book_copies WHERE copy_id = %s", params=(copy_id,), fetchone=True)
        if not row:
            flash('未找到指定副本', 'error')
            return redirect(url_for('admin_books'))
        book_id = row.get('book_id')
        from db import execute_non_query
        execute_non_query("DELETE FROM book_copies WHERE copy_id = %s", params=(copy_id,))
        flash('副本已删除', 'success')
    except Exception as e:
        flash(f'删除失败：{str(e)}', 'error')
        # 尝试重定向回图书详情页，如果无法确定 book_id 则回图书列表
        try:
            return redirect(url_for('admin_book_details', book_id=book_id))
        except Exception:
            return redirect(url_for('admin_books'))
    return redirect(url_for('admin_book_details', book_id=book_id))

# 作者详情页（读者）
@app.route('/author/<int:author_id>', methods=['GET'])
@login_required
@role_required('reader')
def author_details(author_id):
    # 现在 authors 表没有 bio 字段，先只查询基本信息并保证返回的对象包含 bio（可能为 None）
    author = execute_query(
        "SELECT author_id, name FROM authors WHERE author_id = %s",
        params=(author_id,),
        fetchone=True,
    )
    if not author:
        flash('作者不存在', 'error')
        return redirect(url_for('books'))

    books = execute_query(
        "SELECT v.book_id, v.title, v.category, v.publisher, v.available_copies "
        "FROM view_book_catalog v JOIN book_authors ba ON v.book_id = ba.book_id WHERE ba.author_id = %s ORDER BY v.title",
        params=(author_id,),
        fetchall=True,
    ) or []

    # 确保每本书都有 category 字段的可视化值（避免 NULL 显示为空）
    for b in books:
        if not b.get('category'):
            b['category'] = '-'

    return render_template('author_details.html', username=session.get('username'), role=session.get('role'), author=author, books=books)


# 作者详情页（管理员视图）
@app.route('/admin/author/<int:author_id>', methods=['GET'])
@login_required
@role_required('admin')
def admin_author_details(author_id):
    author = execute_query(
        "SELECT author_id, name FROM authors WHERE author_id = %s",
        params=(author_id,),
        fetchone=True,
    )
    if not author:
        flash('作者不存在', 'error')
        return redirect(url_for('admin_books'))

    books = execute_query(
        "SELECT v.book_id, v.title, v.category, v.publisher, v.available_copies "
        "FROM view_book_catalog v JOIN book_authors ba ON v.book_id = ba.book_id WHERE ba.author_id = %s ORDER BY v.title",
        params=(author_id,),
        fetchall=True,
    ) or []

    for b in books:
        if not b.get('category'):
            b['category'] = '-'

    return render_template('author_details.html', username=session.get('username'), role=session.get('role'), author=author, books=books)

# 添加 /reserve/<book_id> 路由，预约功能
@app.route('/reserve/<int:book_id>', methods=['POST'])
@login_required
@role_required('reader')
def reserve_book_route(book_id):
    user_id = session.get('user_id')
    try:
        # 调用存储过程 reserve_book
        call_procedure('reserve_book', [user_id, book_id])
        flash('预约成功', 'success')
    except Exception as e:
        flash(f'预约失败：{str(e)}', 'error')
    # 保持在当前书籍详情页，便于用户继续查看或重试
    return redirect(url_for('book_details', book_id=book_id))


# 预约列表页面（读者）
@app.route('/my-reservations', methods=['GET'])
@login_required
@role_required('reader')
def my_reservations():
    user_id = session.get('user_id')
    rows = execute_query(
        "SELECT reservation_id, user_id, username, full_name, book_id, title, isbn, authors, reservation_date, status, notified_at "
        "FROM view_reservation_details "
        "WHERE user_id = %s ORDER BY reservation_date DESC",
        params=(user_id,),
        fetchall=True,
    ) or []

    # 后端格式化日期为 YYYY-MM-DD（兼容 date/datetime 或字符串）
    def _fmt_date(val):
        if not val:
            return None
        if isinstance(val, (datetime.date, datetime.datetime)):
            return val.strftime('%Y-%m-%d')
        if isinstance(val, str):
            return val.split(' ')[0]
        return str(val)

    for r in rows:
        r['reservation_date'] = _fmt_date(r.get('reservation_date'))
        r['notified_at'] = _fmt_date(r.get('notified_at'))

    return render_template('my_reservations.html', username=session.get('username'), role=session.get('role'), reservations=rows)

# 取消预约（读者）
@app.route('/cancel-reservation/<int:reservation_id>', methods=['POST'])
@login_required
@role_required('reader')
def cancel_reservation(reservation_id):
    user_id = session.get('user_id')
    from db import execute_non_query
    try:
        affected = execute_non_query(
            "UPDATE reservations SET status='cancelled' WHERE reservation_id = %s AND user_id = %s AND status = 'waiting'",
            params=(reservation_id, user_id),
        )
        if affected:
            flash('已取消预约', 'success')
        else:
            flash('取消预约失败（可能不存在或不在waiting状态）', 'error')
    except Exception as e:
        flash(f'取消预约失败：{str(e)}', 'error')
    return redirect(url_for('my_reservations'))

# 领取预约的书籍（读者）
@app.route('/claim-reservation/<int:reservation_id>', methods=['POST'])
@login_required
@role_required('reader')
def claim_reservation(reservation_id):
    user_id = session.get('user_id')
    try:
        # 调用新的存储过程 borrow_with_reservation
        call_procedure('borrow_with_reservation', [reservation_id])
        flash('领取成功，借阅已完成', 'success')
    except Exception as e:
        flash(f'领取失败：{str(e)}', 'error')
    return redirect(url_for('my_reservations'))

# 借阅排行榜页面
@app.route('/book-popularity', methods=['GET'])
@login_required
def book_popularity():

    books = execute_query(
        "SELECT book_id, title, authors, borrow_times FROM view_book_popularity ORDER BY borrow_times DESC",
        fetchall=True,
    )
    return render_template('book_popularity.html', username=session.get('username'), role=session.get('role'), books=books or [])

# 副本管理接口已迁移到图书详情页（管理员）
# 已移除单独的 /admin/copies 页面；请使用图书详情页进行新增/删除副本操作。


# 借阅管理页面（管理员）
@app.route('/admin/borrows', methods=['GET'])
@login_required
@role_required('admin')
def admin_borrows():

    borrow_id = request.args.get('borrow_id', '').strip()
    if borrow_id:
        borrows = execute_query(
            "SELECT borrow_id, user_id, username, full_name, copy_id, borrow_date, due_date, return_date, status "
            "FROM view_borrow_details WHERE borrow_id = %s ORDER BY borrow_date DESC",
            params=(borrow_id,),
            fetchall=True,
        ) or []
    else:
        borrows = execute_query(
            "SELECT borrow_id, user_id, username, full_name, copy_id, borrow_date, due_date, return_date, status "
            "FROM view_borrow_details ORDER BY borrow_date DESC",
            fetchall=True,
        )
    return render_template('admin_borrows.html', username=session.get('username'), role=session.get('role'), borrows=borrows or [])

# 预约管理页面（管理员）
@app.route('/admin/reservations', methods=['GET'])
@login_required
@role_required('admin')
def admin_reservations():

    reservation_id = request.args.get('reservation_id', '').strip()
    if reservation_id:
        reservations = execute_query(
            "SELECT reservation_id, user_id, username, full_name, book_id, title, isbn, authors, reservation_date, status, notified_at "
            "FROM view_reservation_details WHERE reservation_id = %s ORDER BY reservation_date DESC",
            params=(reservation_id,),
            fetchall=True,
        ) or []
    else:
        reservations = execute_query(
            "SELECT reservation_id, user_id, username, full_name, book_id, title, isbn, authors, reservation_date, status, notified_at "
            "FROM view_reservation_details ORDER BY reservation_date DESC",
            fetchall=True,
        ) or []

    def _fmt_date(val):
        if not val:
            return None
        if isinstance(val, (datetime.date, datetime.datetime)):
            return val.strftime('%Y-%m-%d')
        if isinstance(val, str):
            return val.split(' ')[0]
        return str(val)

    for r in reservations:
        r['reservation_date'] = _fmt_date(r.get('reservation_date'))
        r['notified_at'] = _fmt_date(r.get('notified_at'))

    return render_template('admin_reservations.html', username=session.get('username'), role=session.get('role'), reservations=reservations)

@app.route('/admin/users', methods=['GET'])
@login_required
@role_required('admin')
def admin_users():
    q = request.args.get('q', '')
    q = q.strip() if q else ''
    if q:
        users = execute_query("SELECT user_id, username, role FROM users WHERE username ILIKE %s ORDER BY user_id", params=(f"%{q}%",), fetchall=True) or []
    else:
        users = execute_query("SELECT user_id, username, role FROM users ORDER BY user_id", fetchall=True) or []
    return render_template('admin_users.html', username=session.get('username'), role=session.get('role'), users=users)

# 编辑用户信息（管理员）
@app.route('/admin/users/<int:user_id>/edit', methods=['GET', 'POST'])
@login_required
@role_required('admin')
def admin_edit_user(user_id):
    if request.method == 'POST':
        username = request.form.get('username')
        role = request.form.get('role')
        full_name = request.form.get('full_name')
        email = request.form.get('email')
        phone = request.form.get('phone')
        gender = request.form.get('gender')
        bio = request.form.get('bio')
        password = request.form.get('password')
        from db import execute_non_query
        try:
            execute_non_query(
                "UPDATE users SET username=%s, role=%s, full_name=%s, email=%s, phone=%s, gender=%s, bio=%s WHERE user_id=%s",
                params=(username, role, full_name, email, phone, gender, bio, user_id),
            )
            if password:
                pwd_hash = generate_password_hash(password)
                execute_non_query(
                    "UPDATE users SET password_hash = %s WHERE user_id = %s",
                    params=(pwd_hash, user_id),
                )
            flash('用户信息已更新', 'success')
        except Exception as e:
            flash(f'更新失败：{str(e)}', 'error')
        return redirect(url_for('admin_users'))
    # GET
    user = execute_query("SELECT user_id, username, role, full_name, email, phone, gender, bio FROM users WHERE user_id = %s", params=(user_id,), fetchone=True)
    return render_template('admin_user_edit.html', username=session.get('username'), role=session.get('role'), user=user)


@app.route('/admin/users/<int:user_id>/delete', methods=['POST'])
@login_required
@role_required('admin')
def admin_delete_user(user_id):
    # Prevent admin from accidentally deleting themselves
    if session.get('user_id') == user_id:
        flash('不能删除当前登录用户', 'error')
        return redirect(url_for('admin_users'))
    try:
        from db import execute_non_query
        execute_non_query("DELETE FROM users WHERE user_id = %s", params=(user_id,))
        flash('用户已删除', 'success')
    except Exception as e:
        flash(f'删除失败：{str(e)}', 'error')
    return redirect(url_for('admin_users'))

# 标记预约过期（管理员） 
@app.route('/admin/expire-reservation/<int:reservation_id>', methods=['POST'])
@login_required
@role_required('admin')
def admin_expire_reservation(reservation_id):
    try:
        call_procedure('expire_reservation', [reservation_id])
        flash('预约已标记为过期，副本已释放给下一位', 'success')
    except Exception as e:
        flash(f'操作失败：{str(e)}', 'error')
    return redirect(url_for('admin_reservations'))

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=True)