# ---------------------------------------------------------------------------
# circulation_analysis.py
#
# A school library issues books all year and nobody ever looks at the register
# afterwards. This program reads it out of MySQL and answers the questions the
# librarian has to answer before the next purchase order goes in:
#
#   1. When is the library actually used, and when is it empty?
#   2. What do children read, and which shelf is dead?
#   3. Which classes use the library, and which have stopped?
#   4. How long is a book kept, and how many are overdue right now?
#   5. Which titles are worth buying more copies of?
#
# The library keeps admission numbers, not names. That is deliberate: a report
# that goes on a notice board should not carry a child's name against what
# they read.
# ---------------------------------------------------------------------------

import mysql.connector
import pandas as pd
import matplotlib.pyplot as plt

TODAY = pd.Timestamp("2026-03-31")      # the day the register was read
LOAN_DAYS = 14                          # how long a book may be kept

# --- 1. Connect and read -------------------------------------------------
con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password_here",
    database="lambdalab_library",
)

# One JOIN pulls the three tables into a single flat table, which is the shape
# pandas works best with. Everything after this is ordinary DataFrame work.
issues = pd.read_sql(
    "SELECT i.issue_id, i.issue_date, i.return_date, "
    "       b.title, b.genre, b.copies, m.class "
    "FROM issues i "
    "JOIN books b   ON i.book_id   = b.book_id "
    "JOIN members m ON i.member_id = m.member_id",
    con)
books = pd.read_sql("SELECT * FROM books", con)
con.close()

print("Issues in the register :", len(issues))
print("Titles in the library  :", len(books))
print()

issues["issue_date"] = pd.to_datetime(issues["issue_date"])
# errors="coerce" turns the NULLs of books still out into NaT rather than
# stopping with an error. Those rows have to survive: they are the overdue list.
issues["return_date"] = pd.to_datetime(issues["return_date"], errors="coerce")

still_out = issues["return_date"].isnull().sum()
print("Books still out today  :", still_out)
print()

# --- 2. Question 1: when is the library used? ----------------------------
issues["month"] = issues["issue_date"].dt.to_period("M").astype(str)
monthly = issues.groupby("month")["issue_id"].count()

print("--- Books issued, month by month ---")
print(monthly)
print()
print("Busiest month :", monthly.idxmax(), "with", monthly.max(), "issues")
print("Quietest month:", monthly.idxmin(), "with", monthly.min(), "issues")
print()

plt.figure(figsize=(9, 4.5))
plt.plot(monthly.index, monthly.values, marker="o", color="#e07b39")
plt.title("Books issued, month by month")
plt.xlabel("Month")
plt.ylabel("Books issued")
plt.xticks(rotation=45)
plt.grid(True, linestyle="--", alpha=0.5)
plt.tight_layout()
plt.savefig("chart1_monthly_issues.png")
plt.close()

# --- 3. Question 2: what gets read? --------------------------------------
by_genre = issues.groupby("genre")["issue_id"].count().sort_values(ascending=False)

print("--- Issues by genre ---")
print(by_genre)
print()
print("Share of all issues (%):")
print((by_genre / by_genre.sum() * 100).round(1))
print()

plt.figure(figsize=(8.5, 4.5))
plt.bar(by_genre.index, by_genre.values, color="#4c9f70")
plt.title("Books issued, by genre")
plt.xlabel("Genre")
plt.ylabel("Times issued in the year")
plt.xticks(rotation=25)
plt.tight_layout()
plt.savefig("chart2_genres.png")
plt.close()

# --- 4. Question 3: which classes use it? --------------------------------
order = ["VI", "VII", "VIII", "IX", "X", "XI", "XII"]
by_class = issues.groupby("class")["issue_id"].count().reindex(order)

print("--- Issues by class ---")
print(by_class)
print()

plt.figure(figsize=(8, 4.5))
plt.bar(by_class.index, by_class.values, color="#3b7dd8")
plt.title("Books issued, by class")
plt.xlabel("Class")
plt.ylabel("Books issued in the year")
plt.tight_layout()
plt.savefig("chart3_classes.png")
plt.close()

# --- 5. Question 4: how long is a book kept? -----------------------------
returned = issues.dropna(subset=["return_date"]).copy()
returned["days_kept"] = (returned["return_date"] - returned["issue_date"]).dt.days

print("--- Days a book is kept (returned books only) ---")
print(returned["days_kept"].describe().round(1))
print()
# The guard matters: a register in which nothing has been returned yet is
# perfectly possible at the start of a term, and without it the program stops
# with ZeroDivisionError on this line rather than saying so.
if len(returned) == 0:
    print("Returned late : nothing has been returned yet")
else:
    late = returned[returned["days_kept"] > LOAN_DAYS]
    print("Returned late :", len(late), "of", len(returned),
          "(", round(len(late) / len(returned) * 100, 1), "% )")

# A book still out is overdue if it went out more than LOAN_DAYS ago.
out = issues[issues["return_date"].isnull()].copy()
out["days_out"] = (TODAY - out["issue_date"]).dt.days
overdue = out[out["days_out"] > LOAN_DAYS]
print("Overdue right now:", len(overdue))
print()

if len(returned) > 0:
    plt.figure(figsize=(8, 4.5))
    plt.hist(returned["days_kept"].values, bins=14, color="#a05fc0", edgecolor="white")
    plt.axvline(LOAN_DAYS, color="#c0392b", linestyle="--", label="14-day limit")
    plt.title("How long a book is kept before it comes back")
    plt.xlabel("Days kept")
    plt.ylabel("Number of loans")
    plt.legend()
    plt.tight_layout()
    plt.savefig("chart4_days_kept.png")
    plt.close()

# --- 6. Question 5: what should the library buy? -------------------------
by_title = issues.groupby("title")["issue_id"].count().sort_values(ascending=False)

print("--- Ten most-borrowed titles ---")
print(by_title.head(10))
print()

# A title issued far more often than it has copies is one children wait for.
demand = books.set_index("title")[["copies"]].join(
    by_title.rename("times_issued")).fillna(0)
demand["times_issued"] = demand["times_issued"].astype(int)
demand["per_copy"] = (demand["times_issued"] / demand["copies"]).round(1)

print("--- Most in demand for the number of copies held ---")
print(demand.sort_values("per_copy", ascending=False).head(8))
print()
never = demand[demand["times_issued"] == 0]
print("Titles nobody borrowed all year:", len(never))
if len(never) > 0:
    print(list(never.index))
print()

top10 = by_title.head(10)[::-1]
plt.figure(figsize=(8.5, 5))
plt.barh(top10.index, top10.values, color="#c9772f")
plt.title("Ten most-borrowed titles")
plt.xlabel("Times issued in the year")
plt.tight_layout()
plt.savefig("chart5_top_titles.png")
plt.close()

# --- 7. The lists the librarian prints -----------------------------------
overdue[["title", "class", "issue_date", "days_out"]].sort_values(
    "days_out", ascending=False).to_csv("overdue.csv", index=False)
demand.sort_values("per_copy", ascending=False).to_csv("demand.csv")

print("Charts saved  : chart1_monthly_issues.png .. chart5_top_titles.png")
print("Lists saved   : overdue.csv, demand.csv")