"""VetsLearn Intermediate 03 practice: starter.

This script runs, and two of its four figures are wrong on purpose.
Your job is to fix the queries in figures() and explain what was wrong.

Standard library only. Run it from this folder:

    python starter.py

It prints its four figures and says how many agree with expected.json.
It does not print the expected values. Work them out.
"""
import csv
import json
import sqlite3
from pathlib import Path

HERE = Path(__file__).resolve().parent


def load():
    """Load both files into an in-memory database. Every column is text."""
    con = sqlite3.connect(":memory:")
    con.execute("CREATE TABLE people (person_id TEXT, name TEXT, branch TEXT)")
    con.execute(
        "CREATE TABLE applications (app_id TEXT, person_id TEXT, role TEXT, submitted_on TEXT)"
    )
    for table, name in (("people", "people.csv"), ("applications", "applications.csv")):
        with open(HERE / name, newline="", encoding="utf-8") as handle:
            rows = [tuple(row) for row in csv.reader(handle)][1:]  # skip the header
        marks = ", ".join("?" for _ in rows[0])
        # The table name comes from the fixed list above. The values are bound.
        con.executemany(f"INSERT INTO {table} VALUES ({marks})", rows)
    return con


JOIN = "FROM people p LEFT JOIN applications a ON a.person_id = p.person_id"


def figures(con):
    def one(sql, params=()):
        return con.execute(sql, params).fetchone()[0]

    return {
        # WRONG ON PURPOSE: this counts joined rows and calls them people.
        "people": one(f"SELECT COUNT(*) {JOIN}"),
        "application_rows": one("SELECT COUNT(*) FROM applications"),
        "joined_rows": one(f"SELECT COUNT(*) {JOIN}"),
        # WRONG ON PURPOSE: this join starts from people, so it cannot see
        # an application whose owner is missing from people.csv.
        "applications_with_no_matching_person": one(
            f"SELECT COUNT(*) {JOIN} WHERE a.person_id IS NOT NULL AND p.person_id IS NULL"
        ),
    }


def main():
    got = figures(load())
    expected = json.loads((HERE / "expected.json").read_text(encoding="utf-8"))
    want = {
        "people": expected["people"]["distinctPeople"],
        "application_rows": expected["applications"]["rows"],
        "joined_rows": expected["join"]["leftJoinRowsAsGiven"],
        "applications_with_no_matching_person": len(expected["applications"]["noMatchingPerson"]),
    }
    for name, value in got.items():
        print(f"{name}: {value}")
    agree = sum(1 for name in got if got[name] == want[name])
    print(f"{agree} of {len(got)} figures agree with expected.json")
    return 0 if agree == len(got) else 1


if __name__ == "__main__":
    raise SystemExit(main())