#!/usr/bin/env python3
"""
Generate SQL to update users, groups, tas in the SQLite DB.
"""

import json
import sqlite3

with open("student_data.json", "rb") as f:
    student_data = json.load(f)

conn = sqlite3.connect("webtech-admin.sqlite")
conn.execute("PRAGMA foreign_keys = 1;")

for group_name, users in student_data.items():
    conn.execute(
        "INSERT INTO groups (name) VALUES (?) ON CONFLICT DO NOTHING", (group_name,)
    )
    for user in users.values():
        conn.execute(
            "INSERT INTO users (user_id, first_name, last_name, username) VALUES (?, ?, ?, ?) ON CONFLICT DO NOTHING",
            (user["uid"], user["first_name"], user["last_name"], user["name"]),
        )
        if user["is_ta"]:
            conn.execute(
                "INSERT INTO tas (ta, group_name) VALUES (?, ?) ON CONFLICT DO NOTHING",
                (user["uid"], group_name),
            )
        else:
            conn.execute(
                "INSERT INTO students (student_id, group_name) VALUES (?, ?) ON CONFLICT DO UPDATE SET group_name = excluded.group_name",
                (user["uid"], group_name),
            )
    conn.commit()