import aiosqlite async def create_tables(db_path): async with aiosqlite.connect(db_path) as db: await db.execute('''CREATE TABLE IF NOT EXISTS participants (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE, tickets INTEGER DEFAULT 1, pass_point INTEGER DEFAULT 0, first_tea_after_pass INTEGER DEFAULT 0)''') await db.execute('''CREATE TABLE IF NOT EXISTS history (id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, present TEXT, winner TEXT, snapshot TEXT)''') await db.commit() async def get_participants(db_path): async with aiosqlite.connect(db_path) as db: cursor = await db.execute("SELECT * FROM participants") return await cursor.fetchall() async def add_participant(db_path, name): async with aiosqlite.connect(db_path) as db: try: await db.execute("INSERT INTO participants (name) VALUES (?)" , (name,)) await db.commit() except aiosqlite.IntegrityError: return False return True async def delete_participant(db_path, id): async with aiosqlite.connect(db_path) as db: await db.execute("DELETE FROM participants WHERE id = ?", (id,)) await db.commit() async def update_participants(db_path, participants): async with aiosqlite.connect(db_path) as db: await db.execute("UPDATE participants SET tickets = ?, pass_point = ?, first_tea_after_pass = ? WHERE name = ?", (participants['tickets'], int(participants['pass_point']), int(participants['first_tea_after_pass']), participants['name'])) await db.commit() async def get_history(db_path): async with aiosqlite.connect(db_path) as db: cursor = await db.execute("SELECT * FROM history ORDER BY id DESC") return await cursor.fetchall() async def add_history(db_path, date, present, winner, snapshot): async with aiosqlite.connect(db_path) as db: await db.execute("INSERT INTO history (date, present, winner, snapshot) VALUES (?, ?, ?, ?)" , (date, present, winner, snapshot)) await db.commit() async def update_participant_manually(db_path, id, tickets, pass_point): async with aiosqlite.connect(db_path) as db: await db.execute( "UPDATE participants SET tickets = ?, pass_point = ? WHERE id = ?", (tickets, int(pass_point), id) ) await db.commit()