# 社团物资借还登记 · 课堂演示件
#
# 用法：先运行 第13章演示_建库.py 建好 items.db，再运行本文件。
#       数据全部存在 items.db 里，换台电脑要把这个文件一起拷过去。

import sqlite3
from pathlib import Path
from datetime import datetime

DB = Path(__file__).with_name('items.db')


def now():
    """当前时间，精确到分钟"""
    return datetime.now().strftime('%Y-%m-%d %H:%M')


def find_item_id(conn, name):
    """按名称查物资编号。查不到返回 None"""
    row = conn.execute('SELECT id FROM items WHERE name = ?', (name,)).fetchone()
    return row[0] if row else None


# ==================== 四种操作 ====================


def borrow(name, borrower):
    """增：登记借出，往 records 表插一行"""
    conn = sqlite3.connect(DB)
    item_id = find_item_id(conn, name)
    if item_id is None:
        print('台账里没有「' + name + '」这件物资。')
        conn.close()
        return
    # 已经借出且没还的，不能再借
    busy = conn.execute(
        'SELECT borrower FROM records WHERE item_id = ? AND return_at IS NULL',
        (item_id,)).fetchone()
    if busy:
        print('「' + name + '」还在', busy[0], '手上，不能重复借出。')
        conn.close()
        return
    conn.execute(
        'INSERT INTO records (item_id, borrower, borrow_at) VALUES (?, ?, ?)',
        (item_id, borrower, now()))
    conn.commit()
    print('已登记：', borrower, '借走「' + name + '」，时间', now())
    conn.close()


def give_back(name):
    """改：登记归还，把那一行的 return_at 填上"""
    conn = sqlite3.connect(DB)
    item_id = find_item_id(conn, name)
    if item_id is None:
        print('台账里没有「' + name + '」这件物资。')
        conn.close()
        return
    cur = conn.execute(
        'UPDATE records SET return_at = ? WHERE item_id = ? AND return_at IS NULL',
        (now(), item_id))
    conn.commit()
    # rowcount 是这句 SQL 实际改了几行
    if cur.rowcount == 0:
        print('「' + name + '」本来就没有借出记录，不用还。')
    else:
        print('已登记归还：「' + name + '」，时间', now())
    conn.close()


def who_has(name):
    """查：这件物资现在在谁手上"""
    conn = sqlite3.connect(DB)
    row = conn.execute('''
        SELECT records.borrower, records.borrow_at
        FROM records JOIN items ON items.id = records.item_id
        WHERE items.name = ? AND records.return_at IS NULL''', (name,)).fetchone()
    if row:
        print('「' + name + '」在', row[0], '手上，', row[1], '借走的。')
    else:
        print('「' + name + '」在库，没有借出。')
    conn.close()


def list_unreturned():
    """查：列出所有还没还回来的"""
    conn = sqlite3.connect(DB)
    rows = conn.execute('''
        SELECT items.name, records.borrower, records.borrow_at
        FROM records JOIN items ON items.id = records.item_id
        WHERE records.return_at IS NULL
        ORDER BY records.borrow_at''').fetchall()
    if not rows:
        print('没有未归还的物资。')
    else:
        print('未归还共', len(rows), '件：')
        for name, borrower, at in rows:
            print('  ', name, '—', borrower, '—', at)
    conn.close()


def remove_item(name):
    """删：把一件物资从台账里删掉"""
    conn = sqlite3.connect(DB)
    cur = conn.execute('DELETE FROM items WHERE name = ?', (name,))
    conn.commit()
    if cur.rowcount == 0:
        print('台账里没有「' + name + '」这件物资。')
    else:
        print('已从台账删除「' + name + '」。')
    conn.close()


# ==================== 命令行菜单 ====================


if __name__ == '__main__':
    while True:
        print()
        print('1 登记借出　2 登记归还　3 查在谁手上　4 列出未归还　5 删除物资　0 退出')
        choice = input('请选择：').strip()
        if choice == '1':
            borrow(input('物资名称：').strip(), input('借用人：').strip())
        elif choice == '2':
            give_back(input('物资名称：').strip())
        elif choice == '3':
            who_has(input('物资名称：').strip())
        elif choice == '4':
            list_unreturned()
        elif choice == '5':
            remove_item(input('物资名称：').strip())
        elif choice == '0':
            break
        else:
            print('请输入 0 到 5 之间的数字。')