"""Hai yêu cầu cạnh tranh một chỗ học, sử dụng SQLite và hai kết nối."""
from concurrent.futures import ThreadPoolExecutor
from contextlib import closing
from pathlib import Path
import sqlite3
from tempfile import TemporaryDirectory


def dang_ky(path, lop, sinh_vien):
    with closing(sqlite3.connect(path, timeout=5, isolation_level=None)) as db:
        try:
            db.execute("BEGIN IMMEDIATE")
            if db.execute("SELECT 1 FROM dangky WHERE lop=? AND sinh_vien=?",
                          (lop, sinh_vien)).fetchone():
                db.rollback()
                return "đã đăng ký"
            changed = db.execute(
                "UPDATE lop SET con_lai=con_lai-1 WHERE ma=? AND con_lai>0",
                (lop,),
            ).rowcount
            if changed == 0:
                db.rollback()
                return "hết chỗ hoặc mã lớp không tồn tại"
            db.execute("INSERT INTO dangky VALUES(?,?)", (lop, sinh_vien))
            db.commit()
            return "đăng ký thành công"
        except sqlite3.Error:
            db.rollback()
            raise


if __name__ == "__main__":
    with TemporaryDirectory() as directory:
        path = Path(directory) / "sample.db"
        with closing(sqlite3.connect(path)) as db:
            db.executescript("""
                CREATE TABLE lop(ma TEXT PRIMARY KEY, con_lai INTEGER NOT NULL CHECK(con_lai>=0));
                CREATE TABLE dangky(lop TEXT NOT NULL, sinh_vien TEXT NOT NULL,
                                    PRIMARY KEY(lop,sinh_vien));
                INSERT INTO lop VALUES ('CTDL',1);
            """)

        with ThreadPoolExecutor(max_workers=2) as pool:
            pending = [pool.submit(dang_ky, path, "CTDL", sv) for sv in ("SV01", "SV02")]
            results = [job.result() for job in pending]

        with closing(sqlite3.connect(path)) as db:
            assert db.execute("SELECT con_lai FROM lop").fetchone() == (0,)
            [winner] = db.execute("SELECT sinh_vien FROM dangky").fetchone()
        assert results.count("đăng ký thành công") == 1
        assert dang_ky(path, "CTDL", winner) == "đã đăng ký"
        print(results, "| SV được giữ chỗ:", winner)