{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "0aecaaab",
   "metadata": {},
   "source": [
    "# 🎓 Student Attendance Management (Notebook)\n",
    "This notebook demonstrates how to use NumPy + Pandas + SQLite to manage and analyze attendance.\n",
    "You can run it locally or in any Jupyter environment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d4ccd27d",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "import os\n",
    "import sqlite3\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "from attendance_core import connect, init_db, load_csv_to_df, ingest_df, get_dataframe, compute_percentages_numpy, summary_per_subject, students_below_threshold\n",
    "\n",
    "DB_PATH = \"attendance.db\"\n",
    "conn = connect(DB_PATH)\n",
    "init_db(conn)\n",
    "print(\"Database ready at\", DB_PATH)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "762af545",
   "metadata": {},
   "source": [
    "## Load a CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22e83cbe",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "csv_path = r\"/mnt/data/attendance_tool/demo_attendance.csv\"\n",
    "df = load_csv_to_df(csv_path)\n",
    "df.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1538c039",
   "metadata": {},
   "source": [
    "## Ingest into SQLite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea5f0e88",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "ingest_df(conn, df)\n",
    "print(\"Ingested rows:\", len(df))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67dae717",
   "metadata": {},
   "source": [
    "## Filtered pull from DB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "484213bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "raw = get_dataframe(conn, start_date=None, end_date=None, subject=None, student_id=None)\n",
    "raw.head(10)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25b5c271",
   "metadata": {},
   "source": [
    "## Compute overall attendance percentages (NumPy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "102efa14",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "overall = compute_percentages_numpy(raw)\n",
    "overall.head(10)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df8a05d8",
   "metadata": {},
   "source": [
    "## Subject summary (Pandas)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ec0e9371",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "subj = summary_per_subject(raw)\n",
    "subj\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92ad847f",
   "metadata": {},
   "source": [
    "## Students below 75%"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a2b0e553",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "low = students_below_threshold(raw, 75.0)\n",
    "low.head(10)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6afcbac9",
   "metadata": {},
   "source": [
    "## Plot distributions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a99d0c24",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Histogram of attendance percentage\n",
    "plt.figure()\n",
    "overall[\"AttendancePercent\"].hist(bins=20)\n",
    "plt.title(\"Distribution of Overall Attendance (%)\")\n",
    "plt.xlabel(\"Attendance %\")\n",
    "plt.ylabel(\"Count\")\n",
    "plt.show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "08c6771f",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Average attendance per subject\n",
    "plt.figure()\n",
    "subj_sorted = subj.sort_values(\"AvgAttendancePercent\")\n",
    "plt.bar(subj_sorted[\"Subject\"], subj_sorted[\"AvgAttendancePercent\"])\n",
    "plt.title(\"Average Attendance by Subject (%)\")\n",
    "plt.xlabel(\"Subject\")\n",
    "plt.ylabel(\"Average %\")\n",
    "plt.xticks(rotation=45)\n",
    "plt.show()\n"
   ]
  }
 ],
 "metadata": {},
 "nbformat": 4,
 "nbformat_minor": 5
}