"""homework.py — a homework tracker that remembers.

Your other programs forget everything when you close them. This one does not.
It keeps its data in a file called homework.db, right next to this file.

Nothing to install. Both sqlite3 and argparse come with Python.

    python3 homework.py add "Ploy" "Unit 3 worksheet" --due 2026-09-01
    python3 homework.py list
    python3 homework.py list --student Ploy
    python3 homework.py done 1
    python3 homework.py report
"""
import argparse
import sqlite3
import sys
from pathlib import Path

# Keep the database next to this file, not wherever you happen to be standing.
DB_PATH = Path(__file__).with_name("homework.db")


def open_db():
    """Open the file, and make the table if this is the first ever run."""
    conn = sqlite3.connect(DB_PATH)
    conn.execute("""CREATE TABLE IF NOT EXISTS jobs (
        id      INTEGER PRIMARY KEY,
        student TEXT NOT NULL,
        title   TEXT NOT NULL,
        due     TEXT,
        done    INTEGER NOT NULL DEFAULT 0
    )""")
    conn.commit()
    return conn


def add(conn, args):
    conn.execute(
        # The ? marks are not decoration. Without them a student called
        # O'Brien would break this line.
        "INSERT INTO jobs (student, title, due) VALUES (?, ?, ?)",
        (args.student, args.title, args.due),
    )
    conn.commit()          # forget this line and nothing is saved
    print("Added: %s - %s" % (args.student, args.title))
    return 0


def show(conn, args):
    sql = "SELECT id, student, title, due, done FROM jobs"
    values = []
    if args.student:
        sql += " WHERE student = ?"
        values.append(args.student)
    sql += " ORDER BY done, id"

    rows = conn.execute(sql, values).fetchall()
    if not rows:
        print("Nothing here yet.")
        return 0

    print("%-4s %-12s %-28s %-12s %s" % ("ID", "STUDENT", "JOB", "DUE", "DONE"))
    for job_id, student, title, due, done in rows:
        print("%-4d %-12s %-28s %-12s %s"
              % (job_id, student, title[:28], due or "-", "yes" if done else "no"))
    return 0


def finish(conn, args):
    result = conn.execute("UPDATE jobs SET done = 1 WHERE id = ?", (args.job_id,))
    conn.commit()
    if result.rowcount == 0:
        print("There is no job with id %d. Try:  python3 homework.py list" % args.job_id)
        return 1
    print("Job %d is finished. Well done!" % args.job_id)
    return 0


def report(conn, args):
    rows = conn.execute("""SELECT student,
                                  COUNT(*)                   AS all_jobs,
                                  SUM(done)                  AS finished
                           FROM jobs
                           GROUP BY student
                           ORDER BY student""").fetchall()
    if not rows:
        print("Nothing here yet.")
        return 0

    print("%-12s %-8s %-10s %s" % ("STUDENT", "JOBS", "FINISHED", "LEFT"))
    for student, all_jobs, finished in rows:
        finished = finished or 0
        print("%-12s %-8d %-10d %d" % (student, all_jobs, finished, all_jobs - finished))
    return 0


def build_parser():
    parser = argparse.ArgumentParser(description="Keep track of homework.")
    jobs = parser.add_subparsers(dest="command", required=True)

    p_add = jobs.add_parser("add", help="add a new job")
    p_add.add_argument("student")
    p_add.add_argument("title")
    p_add.add_argument("--due", default=None, help="when it must be finished")
    p_add.set_defaults(run=add)

    p_list = jobs.add_parser("list", help="show the jobs")
    p_list.add_argument("--student", default=None, help="only this student")
    p_list.set_defaults(run=show)

    p_done = jobs.add_parser("done", help="mark a job finished")
    p_done.add_argument("job_id", type=int)
    p_done.set_defaults(run=finish)

    p_report = jobs.add_parser("report", help="count jobs for each student")
    p_report.set_defaults(run=report)

    return parser


def main():
    args = build_parser().parse_args()
    conn = open_db()
    try:
        return args.run(conn, args)
    finally:
        conn.close()


if __name__ == "__main__":
    sys.exit(main())