🏫 School AI Automation Bot

A robust, production-ready Python automation tool that checks the school portal daily, extracts and parses the latest daily updates PDF (with smart OCR fallback), analyzes the content using Gemini via OpenRouter, cross-references with a pre-configured calendar and lunch menu, and alerts parents via Telegram.

---

🏗️ System Architecture & Workflow

The diagram below outlines the end-to-end flow of the automated pipeline:

graph TD
    A[Start Automation: main.py] --> B[Initialize SQLite DB & Run 10-day retention cleanup]
    B --> C[OrionBrowser: Playwright login & fetch updates page]
    C --> D[Retrieve Google Docs Viewer redirect URL]
    D --> E[PDFDownloader: Download PDF & calculate SHA-256]
    E --> F{File Hash already processed?}
    
    F -- Yes --> G[Send 'No New Update' Telegram Alert]
    F -- No --> H[PDFReader: PyMuPDF text extraction]
    
    H --> I{Text length < 100 chars?}
    I -- Yes --> J[Fallback: PyTesseract OCR on page rasterizations]
    I -- No --> K[Combine complete document text]
    J --> K
    
    K --> L[SchoolSummarizer: OpenRouter Gemini 2.5 Flash Lite JSON prompt]
    L --> M[Fetch Tomorrow's Menu & Tomorrow's Calendar Events]
    M --> N[TelegramFormatter: Generate HTML formatted update]
    N --> O[TelegramBot: Send message notification to Chat ID]
    O --> P[Save file hash to database to prevent double processing]
    P --> Q[End Automation successfully]

Loading

---

✨ Key Features

• 🕵️ Playwright Browser Automation (browser.py)
  • Launches chromium (supports both headless and headed modes).
  • Authenticates to Hubble Orion using credentials with session serialization (browser_state/storage_state.json) to skip logins on subsequent runs.
  • Detects if the session has expired and automatically performs a fresh login.
  • Locates the latest "Jr.K.G Day" updates and extracts the redirecting URL to access the raw PDF binary.
• 📥 Resilient Downloader (downloader.py)
  • Downloads files with automatic retries and exponential backoff.
  • Computes the SHA-256 checksum of downloaded content to ensure absolute accuracy for duplicate detection.
• 💾 Database Management (database.py)
  • SQLite implementation tracking processed updates.
  • Functions to initialize, check, and store processed SHA-256 hashes.
• 📄 Hybrid PDF Extractor (pdf_reader.py)
  • Direct text extraction using PyMuPDF (fitz).
  • Smart fallback: If page text contains less than 100 characters, it automatically rasterizes the page at 300 DPI and performs Optical Character Recognition (OCR) via PyTesseract.
  • Saves a debug .txt file alongside the PDF in the downloads folder.
• 🤖 AI Summarizer (summarizer.py)
  • Uses the OpenRouter API with google/gemini-2.5-flash-lite to extract structured data in strict JSON schema format:
    • class, day, date, activities, homework, bring_tomorrow, important_announcements, and special_notes.
  • Resilient logic to handle network connection issues, API status codes, and JSON parsing issues.
• 🍱 Lunch Menu Checker (menu.py)
  • Integrates a weekly menu structure (weeks 1 to 5, Mon to Fri).
  • Features a built-in safety checker (e.g., verifying if the meal is Safe for Vihaan based on allergens like sprouts, peas, or dal).
• 📅 Event Reminders (events.py)
  • Maintains a database of PTMs, vacations, holidays, celebrations, color-themed days, and submission deadlines.
  • Retrieves any event scheduled for the upcoming day to include as reminders.
• 📨 Telegram Bot & Formatter (telegram_bot.py, formatter.py)
  • Sends beautifully formatted HTML updates directly to Telegram.
  • Includes safety tags (🟢 Safe / 🔴 Not Safe), alerts, and a direct link to the original Google Docs PDF Viewer page.
  • Sends descriptive failure notifications to the administrator if the script crashes.
• 🧹 Auto-Cleanup (cleanup.py)
  • Enforces a strict 10-day retention policy.
  • Purges database records and removes local .pdf/.txt files older than 10 days.

---

📁 File Inventory

File	Category	Description
main.py	Core	Coordinates the entire pipeline, database checks, downloading, parsing, summarizing, and alerts.
browser.py	Browser	Automates Orion Portal logins and fetches the latest PDF URLs using Playwright.
downloader.py	Network	Downloads the PDF files with a retry mechanism and calculates SHA-256 hashes.
pdf_reader.py	Parser	Extracts text from PDF files using PyMuPDF and falls back to PyTesseract OCR when necessary.
summarizer.py	AI	Requests and parses structured JSON summary data from OpenRouter's Gemini API.
prompts.py	AI	System prompts defining the structured JSON schemas and extraction rules.
menu.py	Helper	Matches tomorrow's date with the school lunch calendar and checks if the meal is safe.
events.py	Helper	Looks up school holidays, vacations, submission deadlines, and color days.
formatter.py	Telegram	Renders HTML-formatted messages containing activities, homework, menu status, and PDF links.
telegram_bot.py	Telegram	Handles communication with the Telegram API to send message notifications.
database.py	Database	Performs SQLite operations to log processed PDF hashes.
cleanup.py	Helper	Removes local downloads and database records older than 10 days.
utils.py	Helper	RegEx utilities for escaping Markdown variables.

---

⚙️ Configuration & Environment Setup

1. System Prerequisites

Ensure that Tesseract OCR is installed on your host system:

• macOS: brew install tesseract
• Debian/Ubuntu: sudo apt-get install tesseract-ocr

2. Python Environment Setup

Install dependencies into a virtual environment:

# Create and activate virtual environment
python3 -m venv venv
source venv/bin/activate

# Install requirements
pip install -r requirements.txt

# Install Playwright browser dependencies
playwright install chromium

3. Environment Variables (.env)

Create a .env file in the project root:

# Hubble Orion Portal Credentials
ORION_EMAIL=your-email@example.com
ORION_PASSWORD=your-portal-password

# Playwright Configuration
HEADLESS=true

# AI Service Settings
OPENROUTER_API_KEY=your-openrouter-api-key

# Telegram Bot Credentials
TELEGRAM_BOT_TOKEN=1234567890:ABCdefGhIJKlmNoPQRsTUVwxyZ
TELEGRAM_CHAT_ID=-100XXXXXXXXXX

---

🚀 Execution & Testing

Run Pipeline

Start the daily automation:

python main.py

Unit & Integration Scripts

The project contains several diagnostic scripts to verify each component individually:

• Database Integration: python test_database.py (Validates SQLite status tracking)
• Downloader Test: python test_download.py (Downloads a test document to ensure network parameters are correct)
• PDF OCR & Reading: python test_pdf.py (Extracts text/OCR from the latest downloaded file)
• Gemini Extraction: python test_summary.py (Verifies connection to OpenRouter and prompts the LLM for JSON formatting)
• Telegram Delivery: python test_telegram.py (Sends a placeholder HTML message to check bot delivery)