# ---------------------------------------------------------------------------
# stock_analysis.py
#
# The chemist keeps a year of sales and a shelf full of stock in MySQL. This
# program reads both into pandas and answers what the shop actually loses
# money on:
#
#   1. Which medicines earn the shop its money?
#   2. When in the year is the shop busy?
#   3. Which kinds of medicine sell, and which only take up shelf space?
#   4. What is going to expire soon, and what is that worth?
#   5. Which supplier is the shop's money tied up with?
#
# The SQL here is deliberately plain: it pulls the rows out, and pandas does
# the arithmetic. That is easier to read, easier to change, and it is what the
# Informatics Practices course is about.
# ---------------------------------------------------------------------------

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

TODAY = pd.Timestamp("2026-06-30")      # the day the stock was counted

# --- 1. Connect ----------------------------------------------------------
# Change the user and password to the ones on your own computer.
con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password_here",
    database="lambdalab_chemist",
)

# --- 2. Read both tables into DataFrames ---------------------------------
# read_sql sends the query to MySQL and hands back a DataFrame, so from here
# on it is ordinary pandas — the same as if the data had come from a CSV.
meds = pd.read_sql("SELECT * FROM medicines", con)
sales = pd.read_sql(
    "SELECT s.sale_id, s.sale_date, s.qty, m.name, m.type, m.supplier, m.mrp "
    "FROM sales s JOIN medicines m ON s.med_id = m.med_id",
    con)
con.close()

print("Medicines on the shelf :", len(meds))
print("Sales in the year      :", len(sales))
print()

# MySQL gives dates back as date objects and SQLite as text. to_datetime()
# copes with either, so the program does not depend on which one is behind it.
sales["sale_date"] = pd.to_datetime(sales["sale_date"])
meds["expiry_date"] = pd.to_datetime(meds["expiry_date"])

# --- 3. Derive -----------------------------------------------------------
sales["value"] = sales["qty"] * sales["mrp"]       # what each sale was worth
meds["stock_value"] = meds["stock"] * meds["mrp"]  # money sitting on the shelf
# .dt.days turns the date difference into a plain number of days.
meds["days_to_expiry"] = (meds["expiry_date"] - TODAY).dt.days

print("Sales for the year   : Rs", round(sales["value"].sum(), 2))
print("Stock on the shelf   : Rs", round(meds["stock_value"].sum(), 2))
print("Units sold           :", int(sales["qty"].sum()))
print()

# --- 4. Question 1: which medicines earn the money? ----------------------
by_med = sales.groupby("name")["value"].sum().sort_values(ascending=False)

print("--- Ten highest-earning medicines ---")
print(by_med.head(10).round(2))
print()
print("The top ten are", round(by_med.head(10).sum() / by_med.sum() * 100, 1),
      "% of the year's takings.")
print()

top10 = by_med.head(10)[::-1]
plt.figure(figsize=(8, 5))
plt.barh(top10.index, top10.values, color="#3b7dd8")
plt.title("Ten highest-earning medicines")
plt.xlabel("Sales for the year (Rs)")
plt.tight_layout()
plt.savefig("chart1_top_medicines.png")
plt.close()

# --- 5. Question 2: when is the shop busy? -------------------------------
# to_period("M") drops the day part, so every date in a month becomes the
# same value and groupby can add the month up.
sales["month"] = sales["sale_date"].dt.to_period("M").astype(str)
monthly = sales.groupby("month")["value"].sum()

print("--- Sales, month by month ---")
print(monthly.round(2))
print()
print("Busiest month :", monthly.idxmax(), "at Rs", round(monthly.max(), 2))
print("Quietest month:", monthly.idxmin(), "at Rs", round(monthly.min(), 2))
print()

plt.figure(figsize=(9, 4.5))
plt.plot(monthly.index, monthly.values, marker="o", color="#e07b39")
plt.title("Sales of the chemist shop, month by month")
plt.xlabel("Month")
plt.ylabel("Sales (Rs)")
plt.xticks(rotation=45)
plt.grid(True, linestyle="--", alpha=0.5)
plt.tight_layout()
plt.savefig("chart2_monthly_sales.png")
plt.close()

# --- 6. Question 3: which kinds sell? ------------------------------------
by_type = sales.groupby("type")["value"].sum().sort_values(ascending=False)
stock_type = meds.groupby("type")["stock_value"].sum()

print("--- Sales by kind of medicine ---")
print(by_type.round(2))
print()
# Sales against stock held is the comparison that matters. A kind with a lot
# of stock and few sales is money the shop cannot use.
compare = pd.DataFrame({"sold": by_type, "on_shelf": stock_type}).fillna(0).round(2)
compare["turns"] = (compare["sold"] / compare["on_shelf"]).round(2)
print("--- Sold against stock held ---")
print(compare.sort_values("turns", ascending=False))
print()

plt.figure(figsize=(8.5, 4.5))
plt.bar(by_type.index, by_type.values, color="#4c9f70")
plt.title("Sales by kind of medicine")
plt.xlabel("Kind")
plt.ylabel("Sales for the year (Rs)")
plt.xticks(rotation=25)
plt.tight_layout()
plt.savefig("chart3_by_type.png")
plt.close()

# --- 7. Question 4: what is about to expire? -----------------------------
expired = meds[meds["days_to_expiry"] < 0]
soon = meds[(meds["days_to_expiry"] >= 0) & (meds["days_to_expiry"] <= 90)]

print("--- Already expired ---")
print(expired[["name", "expiry_date", "stock", "stock_value"]].to_string(index=False))
print("Value already lost: Rs", round(expired["stock_value"].sum(), 2))
print()
print("--- Expiring within 90 days ---")
print(soon[["name", "expiry_date", "days_to_expiry", "stock", "stock_value"]]
      .sort_values("days_to_expiry").to_string(index=False))
print("Value at risk: Rs", round(soon["stock_value"].sum(), 2))
print()

plt.figure(figsize=(8, 4.5))
plt.hist(meds["days_to_expiry"].values, bins=12, color="#c0653a", edgecolor="white")
plt.axvline(90, color="#c0392b", linestyle="--", label="90-day warning line")
plt.title("How long the stock has left before it expires")
plt.xlabel("Days to expiry (negative means already expired)")
plt.ylabel("Number of products")
plt.legend()
plt.tight_layout()
plt.savefig("chart4_expiry.png")
plt.close()

# --- 8. Question 5: where is the money tied up? --------------------------
by_supplier = meds.groupby("supplier")["stock_value"].sum().sort_values(ascending=False)
sold_supplier = sales.groupby("supplier")["value"].sum()

print("--- Stock value held, by supplier ---")
print(by_supplier.round(2))
print()
print("--- Sold in the year, by supplier ---")
print(sold_supplier.round(2))
print()

plt.figure(figsize=(8.5, 4.5))
plt.bar(by_supplier.index, by_supplier.values, color="#a05fc0")
plt.title("Money tied up in stock, by supplier")
plt.xlabel("Supplier")
plt.ylabel("Stock value (Rs)")
plt.xticks(rotation=12)
plt.tight_layout()
plt.savefig("chart5_suppliers.png")
plt.close()

# --- 9. The reorder and expiry list the shop prints ----------------------
alerts = meds[meds["days_to_expiry"] <= 90][
    ["name", "type", "expiry_date", "days_to_expiry", "stock", "stock_value"]]
alerts.sort_values("days_to_expiry").to_csv("expiry_alerts.csv", index=False)

print("Charts saved : chart1_top_medicines.png .. chart5_suppliers.png")
print("Alert list   : expiry_alerts.csv")