""" main.py — Gifted Hands Senior School Backend FastAPI server for Hugging Face Spaces. Environment variables (set as HF Secrets): SUPABASE_URL, SUPABASE_SERVICE_KEY GOOGLE_SERVICE_ACCOUNT_JSON SMS_API_KEY, SMS_SENDER_ID AIRTEL_CLIENT_ID, AIRTEL_CLIENT_SECRET, AIRTEL_ENV (sandbox/production) MTN_CLIENT_ID, MTN_CLIENT_SECRET, MTN_SUBSCRIPTION_KEY, MTN_ENV JWT_SECRET (any random string) """ import os, json, re, logging, secrets, httpx from datetime import datetime, date, timedelta from typing import Optional, Any from fastapi import FastAPI, HTTPException, Depends, UploadFile, File, Form, Request, Header from fastapi.middleware.cors import CORSMiddleware from fastapi.responses import JSONResponse, StreamingResponse from pydantic import BaseModel, Field import jwt as pyjwt from supabase import create_client, Client import io logging.basicConfig(level=logging.INFO) log = logging.getLogger("ghss") # ── ENV ───────────────────────────────────────────────────────────────────── SUPABASE_URL = os.getenv("SUPABASE_URL", "") SUPABASE_KEY = os.getenv("SUPABASE_SERVICE_KEY", "") GOOGLE_SA_JSON = os.getenv("GOOGLE_SERVICE_ACCOUNT_JSON", "") SMS_API_KEY = os.getenv("SMS_API_KEY", "") SMS_SENDER_ID = os.getenv("SMS_SENDER_ID", "") # Leave blank unless you have a registered AT sender ID AIRTEL_CLIENT_ID = os.getenv("AIRTEL_CLIENT_ID", "") AIRTEL_CLIENT_SECRET = os.getenv("AIRTEL_CLIENT_SECRET", "") AIRTEL_ENV = os.getenv("AIRTEL_ENV", "sandbox") MTN_CLIENT_ID = os.getenv("MTN_CLIENT_ID", "") MTN_CLIENT_SECRET = os.getenv("MTN_CLIENT_SECRET", "") MTN_SUB_KEY = os.getenv("MTN_SUBSCRIPTION_KEY", "") JWT_SECRET = os.getenv("JWT_SECRET", "ghss-default-secret-change-this") SMS_USERNAME = os.getenv("SMS_USERNAME", "giftedhandsss") # Africa's Talking account username # ── BACKBLAZE B2 ───────────────────────────────────────────────────────────── B2_KEY_ID = os.getenv("B2_KEY_ID", "") # Application Key ID B2_APP_KEY = os.getenv("B2_APPLICATION_KEY", "") # Application Key B2_BUCKET_NAME = os.getenv("B2_BUCKET_NAME", "ghss-files") B2_BUCKET_ID = os.getenv("B2_BUCKET_ID", "") # ── SUPABASE CLIENT ───────────────────────────────────────────────────────── sb: Client = None if SUPABASE_URL and SUPABASE_KEY: sb = create_client(SUPABASE_URL, SUPABASE_KEY) def db(): if not sb: raise HTTPException(500, "Database not configured. Set SUPABASE_URL and SUPABASE_SERVICE_KEY.") return sb # ── GOOGLE DRIVE ───────────────────────────────────────────────────────────── _drive_token_cache = {} def get_drive_service(): """Returns a valid Bearer token for Google Drive API v3, cached for 55 minutes.""" import time now = time.time() if _drive_token_cache.get("token") and _drive_token_cache.get("exp", 0) > now + 30: return _drive_token_cache["token"] if not GOOGLE_SA_JSON: return None try: import google.oauth2.service_account as sa import google.auth.transport.requests as ga_req creds_dict = json.loads(GOOGLE_SA_JSON) creds = sa.Credentials.from_service_account_info( creds_dict, scopes=["https://www.googleapis.com/auth/drive"] ) req = ga_req.Request() creds.refresh(req) _drive_token_cache["token"] = creds.token _drive_token_cache["exp"] = now + 3300 # 55 min cache return creds.token except Exception as e: log.warning(f"Drive auth failed: {e}") return None def get_drive_sa_email() -> str: """Return the service account email from the JSON secret.""" if not GOOGLE_SA_JSON: return "" try: return json.loads(GOOGLE_SA_JSON).get("client_email", "") except Exception: return "" async def drive_create_folder(name: str, parent_id: str) -> Optional[str]: token = get_drive_service() if not token: return None async with httpx.AsyncClient() as client: r = await client.post( "https://www.googleapis.com/drive/v3/files", headers={"Authorization": f"Bearer {token}"}, json={"name": name, "mimeType": "application/vnd.google-apps.folder", "parents": [parent_id]} ) if r.status_code in (200, 201): return r.json().get("id") log.warning(f"drive_create_folder '{name}' failed: {r.status_code} {r.text[:200]}") return None async def drive_find_or_create_folder(name: str, parent_id: str) -> Optional[str]: """Find an existing folder by name under parent, or create it.""" token = get_drive_service() if not token: return None async with httpx.AsyncClient() as client: q = f"name='{name}' and mimeType='application/vnd.google-apps.folder' and '{parent_id}' in parents and trashed=false" r = await client.get( "https://www.googleapis.com/drive/v3/files", headers={"Authorization": f"Bearer {token}"}, params={"q": q, "fields": "files(id,name)", "pageSize": "1"} ) if r.status_code == 200: files = r.json().get("files", []) if files: return files[0]["id"] return await drive_create_folder(name, parent_id) async def drive_make_root_folder(name: str) -> Optional[str]: """Create a root-level folder (no parent) owned by the service account.""" token = get_drive_service() if not token: return None async with httpx.AsyncClient() as client: # Check if it already exists at root q = f"name='{name}' and mimeType='application/vnd.google-apps.folder' and 'root' in parents and trashed=false" r = await client.get( "https://www.googleapis.com/drive/v3/files", headers={"Authorization": f"Bearer {token}"}, params={"q": q, "fields": "files(id,name)", "pageSize": "1"} ) if r.status_code == 200: files = r.json().get("files", []) if files: return files[0]["id"] # Create it r2 = await client.post( "https://www.googleapis.com/drive/v3/files", headers={"Authorization": f"Bearer {token}"}, json={"name": name, "mimeType": "application/vnd.google-apps.folder"} ) if r2.status_code in (200, 201): return r2.json().get("id") log.warning(f"drive_make_root_folder failed: {r2.status_code} {r2.text[:200]}") return None async def drive_share_folder(folder_id: str, email: str, role: str = "reader") -> bool: """Share a Drive folder with a user email. Returns True on success.""" token = get_drive_service() if not token or not folder_id: log.warning("drive_share_folder: no token or folder_id") return False async with httpx.AsyncClient() as client: r = await client.post( f"https://www.googleapis.com/drive/v3/files/{folder_id}/permissions", headers={"Authorization": f"Bearer {token}"}, json={"type": "user", "role": role, "emailAddress": email}, params={"sendNotificationEmail": "false"} ) if r.status_code in (200, 201): log.info(f"Drive: shared folder {folder_id} with {email} as {role}") return True else: log.error(f"Drive share FAILED: {r.status_code} — {r.text[:300]}") return False async def drive_upload_file(file_bytes: bytes, filename: str, mime_type: str, parent_id: str) -> Optional[str]: token = get_drive_service() if not token: return None try: boundary = "ghss_boundary_xA9" metadata = json.dumps({"name": filename, "parents": [parent_id]}) body = ( f"--{boundary}\r\nContent-Type: application/json; charset=UTF-8\r\n\r\n" f"{metadata}\r\n--{boundary}\r\nContent-Type: {mime_type}\r\n\r\n" ).encode() + file_bytes + f"\r\n--{boundary}--".encode() async with httpx.AsyncClient() as client: r = await client.post( "https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart", headers={ "Authorization": f"Bearer {token}", "Content-Type": f"multipart/related; boundary={boundary}" }, content=body, timeout=60.0 ) if r.status_code in (200, 201): return r.json().get("id") log.warning(f"drive_upload_file '{filename}' failed: {r.status_code}") except Exception as e: log.warning(f"Drive upload failed: {e}") return None async def drive_move_folder(file_id: str, new_parent_id: str, old_parent_id: str): token = get_drive_service() if not token: return async with httpx.AsyncClient() as client: await client.patch( f"https://www.googleapis.com/drive/v3/files/{file_id}", headers={"Authorization": f"Bearer {token}"}, params={"addParents": new_parent_id, "removeParents": old_parent_id}, json={} ) async def drive_signed_url(file_id: str) -> Optional[str]: """Returns a short-lived download URL for a Drive file.""" token = get_drive_service() if not token or not file_id: return None return f"https://www.googleapis.com/drive/v3/files/{file_id}?alt=media&access_token={token}" # ── BACKBLAZE B2 ───────────────────────────────────────────────────────────── _b2_auth_cache: dict = {} async def b2_authorize() -> Optional[dict]: """Return cached B2 credentials; re-authorizes automatically when expired.""" import time, base64 now = time.time() if _b2_auth_cache.get("exp", 0) > now + 120: return _b2_auth_cache.get("data") if not B2_KEY_ID or not B2_APP_KEY: return None cred_str = base64.b64encode(f"{B2_KEY_ID}:{B2_APP_KEY}".encode()).decode() try: async with httpx.AsyncClient(timeout=20.0) as client: r = await client.get( "https://api.backblazeb2.com/b2api/v2/b2_authorize_account", headers={"Authorization": f"Basic {cred_str}"} ) if r.status_code == 200: data = r.json() _b2_auth_cache["data"] = data _b2_auth_cache["exp"] = now + 86000 # ~24 h return data log.warning(f"B2 authorize failed: {r.status_code} {r.text[:200]}") except Exception as e: log.warning(f"B2 authorize error: {e}") return None async def b2_upload(file_bytes: bytes, b2_path: str, mime_type: str) -> Optional[str]: """Upload bytes to B2. Returns b2_path on success, None on failure.""" import hashlib from urllib.parse import quote creds = await b2_authorize() if not creds or not B2_BUCKET_ID: log.warning("B2 upload skipped: credentials or bucket ID not configured") return None sha1 = hashlib.sha1(file_bytes).hexdigest() try: async with httpx.AsyncClient(timeout=30.0) as client: r = await client.post( f"{creds['apiUrl']}/b2api/v2/b2_get_upload_url", headers={"Authorization": creds["authorizationToken"]}, json={"bucketId": B2_BUCKET_ID} ) if r.status_code != 200: log.warning(f"b2_get_upload_url failed: {r.text[:200]}") return None up = r.json() async with httpx.AsyncClient(timeout=120.0) as client: r2 = await client.post( up["uploadUrl"], headers={ "Authorization": up["authorizationToken"], "X-Bz-File-Name": quote(b2_path, safe="/"), "Content-Type": mime_type, "Content-Length": str(len(file_bytes)), "X-Bz-Content-Sha1": sha1, }, content=file_bytes, ) if r2.status_code == 200: return b2_path log.warning(f"B2 upload failed '{b2_path}': {r2.status_code} {r2.text[:200]}") except Exception as e: log.warning(f"B2 upload error '{b2_path}': {e}") return None async def b2_download_url(b2_path: str, valid_seconds: int = 3600) -> Optional[str]: """Return a signed download URL for a B2 file (forces browser download via attachment header).""" from urllib.parse import quote creds = await b2_authorize() if not creds or not B2_BUCKET_ID: return None try: async with httpx.AsyncClient(timeout=15.0) as client: r = await client.post( f"{creds['apiUrl']}/b2api/v2/b2_get_download_authorization", headers={"Authorization": creds["authorizationToken"]}, json={"bucketId": B2_BUCKET_ID, "fileNamePrefix": b2_path, "validDurationInSeconds": valid_seconds}, ) if r.status_code == 200: token = r.json().get("authorizationToken", "") encoded = quote(b2_path, safe="/") fname = b2_path.split("/")[-1] disposition = quote(f'attachment; filename="{fname}"') return (f"{creds['downloadUrl']}/file/{B2_BUCKET_NAME}/{encoded}" f"?Authorization={token}&b2ContentDisposition={disposition}") except Exception as e: log.warning(f"b2_download_url error: {e}") return None async def b2_delete_file(b2_path: str) -> bool: """Delete a file from B2. Tries delete_file_version first; falls back to hide_file if the key lacks deleteFiles permission (hide still removes it from listings).""" creds = await b2_authorize() if not creds or not B2_BUCKET_ID: return False try: async with httpx.AsyncClient(timeout=20.0) as client: # Step 1: look up fileId r = await client.post( f"{creds['apiUrl']}/b2api/v2/b2_list_file_names", headers={"Authorization": creds["authorizationToken"]}, json={"bucketId": B2_BUCKET_ID, "prefix": b2_path, "maxFileCount": 1}, ) if r.status_code != 200: log.warning(f"b2_delete_file list error: {r.status_code} {r.text[:100]}") return False files = r.json().get("files", []) if not files or files[0].get("fileName") != b2_path: log.info(f"b2_delete_file: '{b2_path}' not found — already deleted?") return True # treat as success if file doesn't exist file_id = files[0]["fileId"] # Step 2: hard-delete the version r2 = await client.post( f"{creds['apiUrl']}/b2api/v2/b2_delete_file_version", headers={"Authorization": creds["authorizationToken"]}, json={"fileId": file_id, "fileName": b2_path}, ) if r2.status_code == 200: log.info(f"b2_delete_file: deleted '{b2_path}'") return True # Step 3: fallback — hide file (works without deleteFiles permission) log.warning(f"b2_delete_file_version failed ({r2.status_code}), trying hide: {r2.text[:80]}") r3 = await client.post( f"{creds['apiUrl']}/b2api/v2/b2_hide_file", headers={"Authorization": creds["authorizationToken"]}, json={"bucketId": B2_BUCKET_ID, "fileName": b2_path}, ) if r3.status_code == 200: log.info(f"b2_delete_file: hid '{b2_path}' (hidden, not hard-deleted)") return True log.warning(f"b2_delete_file hide also failed: {r3.status_code} {r3.text[:80]}") except Exception as e: log.warning(f"b2_delete_file error '{b2_path}': {e}") return False # ── APP ─────────────────────────────────────────────────────────────────────── app = FastAPI(title="Gifted Hands SS API", version="2.0.0") app.add_middleware( CORSMiddleware, allow_origins=["*"], allow_credentials=True, allow_methods=["*"], allow_headers=["*"], ) # ══════════════════════════════════════════════════════════════ # GOOGLE DRIVE ENDPOINTS # ══════════════════════════════════════════════════════════════ @app.get("/api/health") def health(): return {"status": "ok", "version": "2.0.0", "time": datetime.utcnow().isoformat()} # ── SMS ────────────────────────────────────────────────────────────────────── def _normalize_phone(phone: str) -> str: """Convert a Uganda phone number to international format (+256...).""" digits = re.sub(r'\D', '', phone) if digits.startswith('256') and len(digits) >= 12: return '+' + digits if digits.startswith('0') and len(digits) == 10: return '+256' + digits[1:] if len(digits) == 9 and digits[0] in ('7', '4', '3'): return '+256' + digits # Already has country code without + if len(digits) >= 11: return '+' + digits return '+256' + digits.lstrip('0') async def send_sms(phone: str, message: str): """Send SMS via Africa's Talking.""" if not SMS_API_KEY or not phone: log.info(f"SMS (mock — no API key or phone): {phone} → {message}") return True phone_intl = _normalize_phone(phone) try: payload_data = { "username": SMS_USERNAME, "to": phone_intl, "message": message, } # Only include sender ID if one is configured (omit in sandbox) if SMS_SENDER_ID and SMS_USERNAME.lower() != "sandbox": payload_data["from"] = SMS_SENDER_ID async with httpx.AsyncClient(timeout=30.0) as client: r = await client.post( "https://api.africastalking.com/version1/messaging", headers={ "apiKey": SMS_API_KEY, "Accept": "application/json", "Content-Type": "application/x-www-form-urlencoded", }, data=payload_data, ) log.info(f"SMS to {phone_intl}: HTTP {r.status_code} | {r.text[:300]}") if r.status_code not in (200, 201): log.warning(f"SMS delivery issue to {phone_intl}: {r.status_code} {r.text[:200]}") return r.status_code in (200, 201) except Exception as e: log.warning(f"SMS failed to {phone_intl}: {e}") return False # ── JWT ────────────────────────────────────────────────────────────────────── def create_token(login_id: str, role: str, name: str, permissions=None) -> str: payload = { "loginId": login_id, "role": role, "name": name, "permissions": permissions or [], "exp": datetime.utcnow() + timedelta(days=7) } return pyjwt.encode(payload, JWT_SECRET, algorithm="HS256") def decode_token(token: str) -> dict: try: return pyjwt.decode(token, JWT_SECRET, algorithms=["HS256"]) except Exception: raise HTTPException(401, "Invalid or expired token") async def auth(authorization: str = Header(None), token: str = "") -> dict: """Accept Bearer token from Authorization header OR ?token= query param (needed for file downloads).""" raw = authorization or (f"Bearer {token}" if token else "") if not raw or not raw.startswith("Bearer "): raise HTTPException(401, "Missing token") return decode_token(raw.split(" ", 1)[1]) async def admin_auth(authorization: str = Header(None)) -> dict: payload = await auth(authorization) if payload.get("role") not in ("admin", "sub_admin"): raise HTTPException(403, "Admin access required") return payload def require_perm(perm: str): """Factory: returns a Depends that enforces the given permission for sub_admins.""" async def _check(authorization: str = Header(None)) -> dict: payload = await auth(authorization) role = payload.get("role") if role not in ("admin", "sub_admin"): raise HTTPException(403, "Admin access required") if role == "sub_admin": perms = payload.get("permissions") or [] if perm not in perms: raise HTTPException(403, f"No permission: {perm}") return payload return _check async def teacher_auth(authorization: str = Header(None)) -> dict: payload = await auth(authorization) if payload.get("role") != "teacher": raise HTTPException(403, "Teacher access required") return payload async def student_auth(authorization: str = Header(None)) -> dict: payload = await auth(authorization) if payload.get("role") != "student": raise HTTPException(403, "Student access required") return payload async def parent_auth(authorization: str = Header(None)) -> dict: payload = await auth(authorization) if payload.get("role") != "parent": raise HTTPException(403, "Parent access required") return payload # ── HELPERS ─────────────────────────────────────────────────────────────────── def get_setting(key: str, default=None): try: r = db().table("settings").select("value").eq("key", key).single().execute() return r.data.get("value", default) except Exception: return default def set_setting(key: str, value: str): existing = db().table("settings").select("id").eq("key", key).limit(1).execute() if existing and existing.data: db().table("settings").update({"value": value, "updated_at": datetime.utcnow().isoformat()}).eq("key", key).execute() else: db().table("settings").insert({"key": key, "value": value, "updated_at": datetime.utcnow().isoformat()}).execute() def get_root_folder_id(): return get_setting("drive_root_folder_id", "") def get_active_students_folder(): return get_setting("drive_active_students_folder_id", "") def get_archived_students_folder(): return get_setting("drive_archived_students_folder_id", "") def get_work_folder(): return get_setting("drive_work_folder_id", "") def get_backups_folder(): return get_setting("drive_backups_folder_id", "") def calc_average(subjects: list) -> Optional[str]: totals = [s.get("total") for s in subjects if s.get("total") is not None] if not totals: return None avg = sum(totals) / len(totals) perf = "Distinction" if avg >= 80 else "Merit" if avg >= 70 else "Credit" if avg >= 60 else "Pass" if avg >= 50 else "Elementary" return f"{avg:.1f} — {perf}" def safe_one(result) -> Optional[dict]: """Safely get the first row from a .limit(1).execute() result — never raises.""" try: data = result.data if result else None if not data: return None return data[0] if isinstance(data, list) else data except Exception: return None def sync_rc_visibility(login_id: str, term: str, year: int): """Recalculate is_visible for all published report cards for a student/term/year.""" try: fees = db().table("student_fees").select("amount_required,amount_paid") \ .eq("student_login_id", login_id).eq("term", term).eq("year", year) \ .limit(1).execute() fees_row = safe_one(fees) if fees_row: paid = fees_row.get("amount_paid") or 0 req = fees_row.get("amount_required") or 0 visible = (req == 0) or (paid >= req) else: visible = True # No fees record → free db().table("report_cards").update({"is_visible": visible}) \ .eq("student_login", login_id).eq("term", term).eq("year", year) \ .eq("status", "published").execute() except Exception: pass # Don't let visibility sync crash the parent request @app.get("/api/drive/status") async def drive_status(payload: dict = Depends(admin_auth)): """Check Drive connection and return folder IDs currently saved.""" sa_email = get_drive_sa_email() connected = bool(get_drive_service()) if GOOGLE_SA_JSON else False return { "connected": connected, "serviceAccountEmail": sa_email, "hasSecret": bool(GOOGLE_SA_JSON), "folders": { "root": get_setting("drive_root_folder_id", ""), "studentsActive": get_setting("drive_active_students_folder_id", ""), "studentsArchived": get_setting("drive_archived_students_folder_id", ""), "work": get_setting("drive_work_folder_id", ""), "backups": get_setting("drive_backups_folder_id", ""), "resources": get_setting("drive_resources_folder_id", ""), } } @app.get("/api/b2/status") async def b2_status(payload: dict = Depends(admin_auth)): """Check Backblaze B2 configuration and connectivity.""" if not B2_KEY_ID: return {"configured": False, "message": "B2_KEY_ID secret not set"} creds = await b2_authorize() return { "configured": True, "connected": bool(creds), "bucketName": B2_BUCKET_NAME, "hasBucketId": bool(B2_BUCKET_ID), "accountId": creds.get("accountId", "") if creds else "", "message": "Connected" if creds else "Auth failed — check B2_KEY_ID and B2_APPLICATION_KEY secrets", } @app.get("/api/storage/browse") async def storage_browse(prefix: str = "", payload: dict = Depends(admin_auth)): """ Browse B2 bucket by prefix (acts like a folder navigator). prefix='' → root (shows admissions/, resources/, backups/) prefix='admissions/' → shows year sub-folders prefix='admissions/2025/John_Doe/' → shows student.jpg etc. """ if not B2_KEY_ID: return {"configured": False, "prefix": prefix, "folders": [], "files": []} creds = await b2_authorize() if not creds: return {"configured": True, "connected": False, "prefix": prefix, "folders": [], "files": []} try: body: dict = {"bucketId": B2_BUCKET_ID, "maxFileCount": 1000, "delimiter": "/"} if prefix: body["prefix"] = prefix async with httpx.AsyncClient(timeout=20.0) as client: r = await client.post( f"{creds['apiUrl']}/b2api/v2/b2_list_file_names", headers={"Authorization": creds["authorizationToken"]}, json=body, ) if r.status_code != 200: log.warning(f"B2 list_file_names failed: {r.text[:200]}") return {"configured": True, "connected": True, "error": "Listing failed — check bucket ID", "prefix": prefix, "folders": [], "files": []} data = r.json() folders, files = [], [] for f in data.get("files", []): if f.get("action") == "folder": # Virtual folder — strip trailing slash for display name folder_prefix = f["fileName"] display = folder_prefix.rstrip("/").split("/")[-1] folders.append({"name": display, "prefix": folder_prefix}) else: ts = f.get("uploadTimestamp", 0) uploaded = datetime.utcfromtimestamp(ts / 1000).strftime("%Y-%m-%d %H:%M") if ts else "" files.append({ "name": f["fileName"].split("/")[-1], "path": f["fileName"], "size": f.get("contentLength", 0), "contentType": f.get("contentType", ""), "uploaded": uploaded, "fileId": f.get("fileId", ""), }) return {"configured": True, "connected": True, "prefix": prefix, "folders": folders, "files": files} except Exception as e: log.warning(f"storage_browse error: {e}") return {"configured": True, "connected": True, "error": str(e)[:120], "prefix": prefix, "folders": [], "files": []} @app.get("/api/storage/file-url") async def storage_file_url(path: str, payload: dict = Depends(admin_auth)): """Return a 1-hour signed download URL for any B2 file by its path.""" url = await b2_download_url(path) if not url: raise HTTPException(503, "Could not generate download URL. Check B2 configuration.") return {"url": url} @app.get("/api/drive/test-share") async def test_drive_share(payload: dict = Depends(admin_auth)): """Test Drive token, folder creation, and sharing step by step. Returns detailed status.""" result = {} # Step 1: token token = get_drive_service() result["step1_token"] = "OK" if token else "FAILED — check GOOGLE_SERVICE_ACCOUNT_JSON secret" if not token: return result # Step 2: find/create root folder root_id = await drive_make_root_folder("Gifted Hands Senior School") result["step2_root_folder_id"] = root_id or "FAILED — could not create folder" if not root_id: return result set_setting("drive_root_folder_id", root_id) # Step 3: share async with httpx.AsyncClient() as client: r = await client.post( f"https://www.googleapis.com/drive/v3/files/{root_id}/permissions", headers={"Authorization": f"Bearer {token}"}, json={"type": "user", "role": "writer", "emailAddress": ADMIN_EMAIL}, params={"sendNotificationEmail": "false"} ) result["step3_share_status"] = r.status_code result["step3_share_response"] = r.text[:400] result["step3_share_ok"] = r.status_code in (200, 201) result["serviceAccountEmail"] = get_drive_sa_email() return result @app.get("/api/drive/browse") async def drive_browse(folder_id: str = "", payload: dict = Depends(admin_auth)): """List files and folders inside a Drive folder.""" token = get_drive_service() if not token: raise HTTPException(503, "Google Drive is not configured.") # Default to root GHSS folder if not folder_id: folder_id = get_setting("drive_root_folder_id", "") if not folder_id: raise HTTPException(503, "Drive root folder not set up. Restart the server once after adding the Drive secret.") async with httpx.AsyncClient() as client: q = f"'{folder_id}' in parents and trashed=false" r = await client.get( "https://www.googleapis.com/drive/v3/files", headers={"Authorization": f"Bearer {token}"}, params={ "q": q, "fields": "files(id,name,mimeType,size,modifiedTime,parents)", "orderBy": "folder,name", "pageSize": "200" } ) if r.status_code != 200: raise HTTPException(502, f"Drive API error: {r.text[:200]}") files = r.json().get("files", []) # Get folder name fr = await client.get( f"https://www.googleapis.com/drive/v3/files/{folder_id}", headers={"Authorization": f"Bearer {token}"}, params={"fields": "id,name,parents"} ) folder_info = fr.json() if fr.status_code == 200 else {"name": "Drive"} return {"folder": folder_info, "files": files} @app.get("/api/drive/file/{file_id}/url") async def drive_file_url(file_id: str, payload: dict = Depends(admin_auth)): """Get a short-lived download URL for a Drive file.""" url = await drive_signed_url(file_id) if not url: raise HTTPException(503, "Could not generate download link.") return {"url": url} @app.delete("/api/drive/file/{file_id}") async def drive_delete_file(file_id: str, payload: dict = Depends(admin_auth)): """Permanently delete a file or folder from Drive.""" token = get_drive_service() if not token: raise HTTPException(503, "Google Drive is not configured.") async with httpx.AsyncClient() as client: r = await client.delete( f"https://www.googleapis.com/drive/v3/files/{file_id}", headers={"Authorization": f"Bearer {token}"} ) if r.status_code not in (200, 204): raise HTTPException(502, f"Drive delete failed: {r.text[:200]}") return {"message": "Deleted"} # ══════════════════════════════════════════════════════════════ # AUTH # ══════════════════════════════════════════════════════════════ class LoginReq(BaseModel): loginId: str password: str class SetPasswordReq(BaseModel): loginId: str password: str class ParentSignupReq(BaseModel): name: str email: str password: str studentLoginId: str studentPassword: str # ── PARENT SIGNUP PREFLIGHT (client calls this first to get clear error msgs) ── # ══ GOOGLE DRIVE ENDPOINTS ══ @app.post("/api/drive/setup") async def setup_drive_folders(body: dict, payload: dict = Depends(admin_auth)): """ One-click Google Drive setup. Creates the full folder tree for GiftedHands SS and saves all folder IDs to settings. Idempotent — safe to run multiple times (finds existing folders instead of creating duplicates). Optionally shares the root with an admin Google account via body.shareWithEmail. """ token = get_drive_service() if not token: raise HTTPException(400, "GOOGLE_SERVICE_ACCOUNT_JSON secret is not set or is invalid. " "Add it in Hugging Face → Settings → Repository secrets and restart the Space.") share_email = (body.get("shareWithEmail") or "").strip() results = {} try: # ── 1. Root: GiftedHands Secondary School ──────────────────────────────── root_id = await drive_make_root_folder("Gifted Hands Senior School") if not root_id: raise HTTPException(500, "Failed to create root folder. Check Drive API permissions.") set_setting("drive_root_folder_id", root_id) results["root"] = root_id # ── 2. Top-level sub-folders ────────────────────────────────────────── students_active = await drive_find_or_create_folder("Students — Active", root_id) students_archived = await drive_find_or_create_folder("Students — Archived", root_id) work_folder = await drive_find_or_create_folder("Staff Work", root_id) backups_folder = await drive_find_or_create_folder("Database Backups", root_id) resources_folder = await drive_find_or_create_folder("Learning Resources", root_id) for fid, key in [ (students_active, "drive_active_students_folder_id"), (students_archived, "drive_archived_students_folder_id"), (work_folder, "drive_work_folder_id"), (backups_folder, "drive_backups_folder_id"), (resources_folder, "drive_resources_folder_id"), ]: if fid: set_setting(key, fid) results["students_active"] = students_active results["students_archived"] = students_archived results["work"] = work_folder results["backups"] = backups_folder results["resources"] = resources_folder # ── 3. Class sub-folders inside Students — Active ───────────────────── if students_active: class_folders = {} for cls in ["S.1", "S.2", "S.3", "S.4", "S.5", "S.6"]: fid = await drive_find_or_create_folder(cls, students_active) class_folders[cls] = fid results["class_folders"] = class_folders # ── 4. Share root folder with admin email if provided ───────────────── if share_email and root_id: await drive_share_folder(root_id, share_email, role="writer") results["shared_with"] = share_email sa_email = get_drive_sa_email() return { "message": "Google Drive set up successfully! All folders created.", "serviceAccountEmail": sa_email, "folders": results } except HTTPException: raise except Exception as e: log.error(f"Drive setup error: {e}") raise HTTPException(500, f"Drive setup failed: {str(e)[:200]}") @app.post("/api/auth/check-student") async def check_student(body: dict): """ Pre-flight: verify a student login ID exists before parent commits to signup. Returns {ok, name, class, stream} or raises 400 with a clear message. """ sid = (body.get("studentLoginId") or "").strip().upper() if not sid: raise HTTPException(400, "Student Login ID is required.") r = db().table("users").select("login_id,role,password,name").eq("login_id", sid).limit(1).execute() rows = r.data or [] if not rows: raise HTTPException(400, f"No student found with ID '{sid}'. Please check and try again.") u = rows[0] if u.get("role") != "student": raise HTTPException(400, f"'{sid}' is not a student account.") # Also get class/stream from students table sr = db().table("students").select("full_name,class,stream").eq("login_id", sid).limit(1).execute() srows = sr.data or [] stu = srows[0] if srows else {} return { "ok": True, "name": stu.get("full_name") or u.get("name") or sid, "class": stu.get("class") or "", "stream": stu.get("stream") or "", "hasPassword": bool(u.get("password")) } # ── PARENT SIGNUP (completely rewritten, no Pydantic model, raw body) ── @app.post("/api/auth/parent-signup") async def parent_signup(request: Request): """Parent self-registration — rewritten with full error detail.""" try: body = await request.json() except Exception: raise HTTPException(400, "Invalid request body. Please try again.") name = (body.get("name") or "").strip() email_raw = (body.get("email") or "").strip() pw = (body.get("password") or "").strip() phone = (body.get("phone") or "").strip() student_id = (body.get("studentLoginId") or "").strip().upper() student_pw = (body.get("studentPassword") or "").strip() # ── Validate inputs ────────────────────────────────────────────────────── if not name: raise HTTPException(400, "Full name is required.") if not email_raw or "@" not in email_raw: raise HTTPException(400, "A valid email address is required.") if not pw or len(pw) < 6: raise HTTPException(400, "Password must be at least 6 characters.") if not student_id: raise HTTPException(400, "Child's Login ID is required.") email = email_raw.lower() # ── 1. Find student in users table ─────────────────────────────────────── try: sr = db().table("users").select("login_id,role,password").eq("login_id", student_id).limit(1).execute() student_rows = sr.data or [] except Exception as e: log.error(f"parent_signup DB error (student lookup): {e}") raise HTTPException(500, f"Database error looking up student: {str(e)[:120]}") if not student_rows: raise HTTPException(400, f"No student account found with ID '{student_id}'. Check the ID sent to you via SMS.") student = student_rows[0] if student.get("role") != "student": raise HTTPException(400, f"'{student_id}' is not a student account.") # ── 2. Verify student password ─────────────────────────────────────────── stored_pw = student.get("password") if stored_pw: # if student has set a password, it must match if student_pw != stored_pw: raise HTTPException(400, "Child's password is incorrect. Ask your child for their current portal password.") # if stored_pw is None/empty, the student hasn't logged in yet — skip check # ── 3. Check email not already taken ──────────────────────────────────── try: er = db().table("users").select("login_id").eq("login_id", email).limit(1).execute() email_rows = er.data or [] except Exception as e: log.error(f"parent_signup DB error (email check): {e}") raise HTTPException(500, f"Database error checking email: {str(e)[:120]}") if email_rows: raise HTTPException(400, "An account with this email already exists. Please Sign In instead.") # ── 4. Create the user account ─────────────────────────────────────────── try: db().table("users").insert({ "login_id": email, "password": pw, "role": "parent", "name": name, "first_login": False, "is_active": True }).execute() except Exception as e: log.error(f"parent_signup DB error (insert user): {e}") raise HTTPException(500, f"Failed to create account: {str(e)[:150]}") # ── 5. Create parent profile row (best-effort) ─────────────────────────── try: db().table("parents").insert({"name": name, "email": email, "phone": phone or None}).execute() except Exception: # Row may already exist — update phone if provided if phone: try: db().table("parents").update({"phone": phone}).eq("email", email).execute() except Exception: pass # ── 6. Auto-link the child they verified during signup ─────────────────── try: # Get or create parent record (just inserted or already existed) pr = db().table("parents").select("id").eq("email", email).limit(1).execute() parent_row = safe_one(pr) if parent_row: parent_id = parent_row["id"] # Find student record id sr2 = db().table("students").select("id,full_name").eq("login_id", student_id).limit(1).execute() stu_row = safe_one(sr2) if stu_row: # Link parent → student (ignore if already linked) existing_link = db().table("student_parent_links").select("id") \ .eq("parent_id", parent_id).eq("student_id", stu_row["id"]).limit(1).execute() if not safe_one(existing_link): db().table("student_parent_links").insert({ "student_id": stu_row["id"], "parent_id": parent_id }).execute() except Exception as e: log.warning(f"Auto-link child failed (non-fatal): {e}") return {"message": "Account created! You can now sign in with your email and password."} class TeacherSignupReq(BaseModel): name: str subject: str = "" subjects: list = [] assignments: list = [] # [{cls, stream, subjects:[]}] phone: str = "" classes: list = [] stream: str = "" password: str class ChangePasswordReq(BaseModel): currentPassword: str newPassword: str @app.post("/api/auth/login") async def login(req: LoginReq): lid = req.loginId.strip() if not lid or not req.password: raise HTTPException(400, "Login ID and password required") # Find user r = db().table("users").select("*").eq("login_id", lid).limit(1).execute() user = safe_one(r) if not user: raise HTTPException(401, "Login ID not found") if not user.get("is_active"): raise HTTPException(403, "Account is not active. Contact the school administration.") # First login — no password set yet if user.get("first_login") and not user.get("password"): return {"firstLogin": True} # Check password (plain text as requested) if user.get("password") != req.password: raise HTTPException(401, "Incorrect password") token = create_token(lid, user["role"], user.get("name") or lid, user.get("permissions")) return {"token": token, "role": user["role"], "name": user.get("name") or lid, "firstLogin": False, "permissions": user.get("permissions") or []} @app.post("/api/auth/set-password") async def set_password(req: SetPasswordReq): lid = req.loginId.strip() if not lid or not req.password: raise HTTPException(400, "Login ID and password are required") r = db().table("users").select("*").eq("login_id", lid).limit(1).execute() user = safe_one(r) if not user: raise HTTPException(404, "User not found") db().table("users").update({"password": req.password, "first_login": False}).eq("login_id", lid).execute() token = create_token(lid, user["role"], user.get("name") or lid, user.get("permissions")) return {"token": token, "role": user["role"], "name": user.get("name") or lid, "permissions": user.get("permissions") or []} @app.post("/api/auth/teacher-signup") async def teacher_signup(req: TeacherSignupReq): # Generate proposed login r = db().rpc("next_teacher_login", {}).execute() proposed = r.data if r.data else f"GHSS{secrets.token_hex(2).upper()}" asgn = req.assignments or [] flat_subjects, flat_classes = _flatten_assignments(asgn) subjects = flat_subjects or req.subjects or ([req.subject] if req.subject else []) db().table("teacher_requests").insert({ "full_name": req.name.strip(), "subject": subjects[0] if subjects else "", "subjects": subjects, "assignments": asgn, "phone": req.phone or "", "password": req.password, "proposed_login": proposed, "status": "pending" }).execute() return {"message": "Request submitted — await admin approval.", "proposedLogin": proposed} @app.get("/api/auth/verify") async def verify(payload: dict = Depends(auth)): return {"valid": True, "role": payload.get("role")} @app.get("/api/auth/me") async def get_me(payload: dict = Depends(auth)): """Return fresh user info from DB — used on portal load to refresh permissions.""" login_id = payload.get("loginId") r = db().table("users").select("login_id,role,name,sub_role,permissions,is_active") \ .eq("login_id", login_id).limit(1).execute() u = safe_one(r) if not u: raise HTTPException(404, "User not found") return { "loginId": u["login_id"], "role": u["role"], "name": u.get("name") or login_id, "subRole": u.get("sub_role"), "permissions": u.get("permissions") or [], "isActive": u.get("is_active", True) } @app.put("/api/auth/change-password") async def change_password(req: ChangePasswordReq, payload: dict = Depends(auth)): login_id = payload.get("loginId") r = db().table("users").select("password").eq("login_id", login_id).limit(1).execute() user = safe_one(r) if not user: raise HTTPException(404, "User account not found") if user.get("password") != req.currentPassword: raise HTTPException(400, "Current password is incorrect") db().table("users").update({"password": req.newPassword}).eq("login_id", login_id).execute() return {"message": "Password updated"} # ══════════════════════════════════════════════════════════════ # ADMISSIONS # ══════════════════════════════════════════════════════════════ @app.post("/api/admissions") async def create_admission( firstName: str = Form(...), lastName: str = Form(...), dob: str = Form(""), gender: str = Form(""), district: str = Form(""), previousSchool: str = Form(""), programme: str = Form(...), boarding: str = Form("Day Scholar"), subjects: str = Form("[]"), parentName: str = Form(...), parentRelationship: str = Form(""), parentPhone: str = Form(...), parentOccupation: str = Form(""), parent2Name: str = Form(""), parent2Relationship: str = Form(""), parent2Phone: str = Form(""), smsPhone: str = Form(""), studentPhoto: Optional[UploadFile] = File(None), parentPhoto: Optional[UploadFile] = File(None), signaturePhoto: Optional[UploadFile] = File(None) ): try: subj_list = json.loads(subjects) if subjects else [] except Exception: subj_list = [] year = datetime.now().year safe_name = f"{firstName}_{lastName}".replace(" ", "_") folder = f"admissions/{year}/{safe_name}" # Upload photos to B2 (falls back gracefully if B2 not configured) stu_b2_path = par_b2_path = sig_b2_path = None if B2_KEY_ID: if studentPhoto: data = await studentPhoto.read() ext = (studentPhoto.filename or "student.jpg").rsplit(".", 1)[-1].lower() stu_b2_path = await b2_upload(data, f"{folder}/student.{ext}", studentPhoto.content_type or "image/jpeg") if parentPhoto: data = await parentPhoto.read() ext = (parentPhoto.filename or "parent1.jpg").rsplit(".", 1)[-1].lower() par_b2_path = await b2_upload(data, f"{folder}/parent1.{ext}", parentPhoto.content_type or "image/jpeg") if signaturePhoto: data = await signaturePhoto.read() ext = (signaturePhoto.filename or "signature.png").rsplit(".", 1)[-1].lower() sig_b2_path = await b2_upload(data, f"{folder}/signature.{ext}", signaturePhoto.content_type or "image/png") # Save to DB — reuse the _drive_id columns for B2 paths (no migration needed) row = { "first_name": firstName, "last_name": lastName, "dob": dob or None, "gender": gender, "district": district, "previous_school": previousSchool, "programme": programme, "boarding": boarding, "subjects": subj_list, "parent_name": parentName, "parent_relationship": parentRelationship, "parent_phone": parentPhone, "parent_occupation": parentOccupation, "parent2_name": parent2Name or None, "parent2_relationship": parent2Relationship or None, "parent2_phone": parent2Phone or None, "sms_phone": smsPhone or parentPhone, "student_photo_drive_id": stu_b2_path, "parent_photo_drive_id": par_b2_path, "signature_drive_id": sig_b2_path, "status": "received" } try: r = db().table("admissions").insert(row).execute() except Exception as e: log.error(f"Admission insert failed: {e}") raise HTTPException(500, "Could not save application. Please try again.") if not r.data: raise HTTPException(500, "Application was not saved. Please try again.") adm_id = r.data[0]["id"] fee = get_setting("admission_fee", "50000") await send_sms(smsPhone or parentPhone, f"GHSS: Application for {firstName} {lastName} received. Ref: GHSS-{adm_id}. " f"Admission fee: UGX {fee}. You will be notified of the decision.") return {"id": adm_id, "reference": f"GHSS-{adm_id}"} @app.get("/api/admissions") async def list_admissions(status: str = "", payload: dict = Depends(admin_auth)): q = db().table("admissions").select("*").order("submitted_at", desc=True) if status: q = q.eq("status", status) rows = q.execute().data or [] # Inject photo URLs — get one B2 download auth token covering all admission photos if B2_KEY_ID and B2_BUCKET_ID: rows_with_photos = [r for r in rows if r.get("student_photo_drive_id")] if rows_with_photos: creds = await b2_authorize() if creds: from urllib.parse import quote try: async with httpx.AsyncClient(timeout=15.0) as client: ar = await client.post( f"{creds['apiUrl']}/b2api/v2/b2_get_download_authorization", headers={"Authorization": creds["authorizationToken"]}, json={"bucketId": B2_BUCKET_ID, "fileNamePrefix": "admissions/", "validDurationInSeconds": 3600}, ) if ar.status_code == 200: dl_token = ar.json().get("authorizationToken", "") dl_base = creds["downloadUrl"] for row in rows: path = row.get("student_photo_drive_id") if path and "admissions/" in path: row["student_photo_url"] = f"{dl_base}/file/{B2_BUCKET_NAME}/{quote(path, safe='/')}?Authorization={dl_token}" except Exception as e: log.warning(f"B2 list photo-URL injection failed (non-fatal): {e}") return rows @app.get("/api/admissions/{adm_id}") async def get_admission(adm_id: int, payload: dict = Depends(admin_auth)): r = db().table("admissions").select("*").eq("id", adm_id).limit(1).execute() row = safe_one(r) if not row: raise HTTPException(404, "Application not found") # Add signed photo URL — supports B2 paths (contain "/") and legacy Drive IDs photo_path = row.get("student_photo_drive_id") if photo_path: if "/" in photo_path and B2_KEY_ID: # B2 file path row["student_photo_url"] = await b2_download_url(photo_path) elif "/" not in photo_path: # Legacy Google Drive file ID row["student_photo_url"] = await drive_signed_url(photo_path) return row class AdmissionDecision(BaseModel): status: str adminNotes: str = "" smsMessage: str = "" # custom message override from admin UI def _fill_sms(template: str, adm: dict, login_id: str = "") -> str: """Replace [name], [class], [stream], [login_id], [notes] placeholders.""" name = f"{adm.get('first_name','')} {adm.get('last_name','')}".strip() cls = adm.get("programme") or "" stream = adm.get("stream") or "North" boarding = adm.get("boarding") or "" return (template .replace("[name]", name) .replace("[class]", cls) .replace("[stream]", stream) .replace("[boarding]", boarding) .replace("[login_id]", login_id) ) @app.patch("/api/admissions/{adm_id}") async def decide_admission(adm_id: int, req: AdmissionDecision, payload: dict = Depends(require_perm("admissions"))): r = db().table("admissions").select("*").eq("id", adm_id).limit(1).execute() adm = safe_one(r) if not adm: raise HTTPException(404, "Application not found") updates = {"status": req.status, "admin_notes": req.adminNotes, "decided_at": datetime.utcnow().isoformat()} new_login_id = None phone = adm.get("sms_phone") or adm.get("parent_phone") if req.status == "accepted" and not adm.get("student_id"): # Generate student login year = datetime.now().year r_login = db().rpc("next_student_login", {"p_year": year}).execute() new_login_id = r_login.data or f"GHSS001{year}" # Create student record stu = db().table("students").insert({ "login_id": new_login_id, "full_name": f"{adm['first_name']} {adm['last_name']}", "gender": adm.get("gender"), "dob": adm.get("dob"), "class": adm.get("programme", "S.1"), "stream": "North", "boarding": adm.get("boarding", "Day Scholar"), "subjects": adm.get("subjects", []), "admission_year": year, "status": "active" }).execute() stu_id = stu.data[0]["id"] if stu.data else None updates["student_id"] = stu_id db().table("users").insert({ "login_id": new_login_id, "password": None, "role": "student", "name": f"{adm['first_name']} {adm['last_name']}", "first_login": True, "is_active": True }).execute() # Persist parent phone so bulk SMS works before parent signs up if stu_id and (adm.get("sms_phone") or adm.get("parent_phone")): sms_ph = adm.get("sms_phone") or adm.get("parent_phone") try: par_r = db().table("parents").insert({ "name": adm.get("parent_name") or f"Parent of {adm['first_name']} {adm['last_name']}", "phone": sms_ph, "relationship": adm.get("parent_relationship") or None, }).execute() par_id = par_r.data[0]["id"] if par_r.data else None if par_id: db().table("student_parent_links").insert({ "student_id": stu_id, "parent_id": par_id }).execute() except Exception as e: log.warning(f"Could not save parent record at admission acceptance: {e}") term = get_setting("current_term", "Term 1") existing_sf = db().table("student_fees").select("id") .eq("student_login_id", new_login_id).eq("term", term).eq("year", year) .limit(1).execute() if not (existing_sf and existing_sf.data): db().table("student_fees").insert({ "student_login_id": new_login_id, "term": term, "year": year, "amount_required": 0, "amount_paid": 0 }).execute() # Build SMS — use custom message if admin typed one, else use template if req.smsMessage.strip(): sms_body = _fill_sms(req.smsMessage, adm, new_login_id) else: tpl = get_setting("sms_approval_template", "Dear parent/guardian, [name] has been accepted to Gifted Hands Senior School. " "Class: [class]. Login ID: [login_id]. " "They will set their password on first login at the school portal.") sms_body = _fill_sms(tpl, adm, new_login_id) await send_sms(phone, sms_body) elif req.status == "rejected": if req.smsMessage.strip(): sms_body = _fill_sms(req.smsMessage, adm) else: tpl = get_setting("sms_rejection_template", "Dear parent/guardian, we regret to inform you that the application for [name] " "to Gifted Hands Senior School was not successful at this time. " "You may reapply in the next intake. Thank you.") sms_body = _fill_sms(tpl, adm) await send_sms(phone, sms_body) db().table("admissions").update(updates).eq("id", adm_id).execute() # Auto-delete after a final decision so accepted/rejected don't clutter the list if req.status in ("accepted", "rejected"): db().table("admissions").delete().eq("id", adm_id).execute() return {"message": "Decision saved", "loginId": new_login_id} @app.get("/api/sms-templates") async def get_sms_templates(payload: dict = Depends(admin_auth)): return { "approval": get_setting("sms_approval_template", "Dear parent/guardian, [name] has been accepted to Gifted Hands Senior School. " "Class: [class]. Login ID: [login_id]. " "They will set their password on first login at the school portal."), "rejection": get_setting("sms_rejection_template", "Dear parent/guardian, we regret to inform you that the application for [name] " "to Gifted Hands Senior School was not successful at this time. " "You may reapply in the next intake. Thank you.") } @app.put("/api/sms-templates") async def save_sms_templates(body: dict, payload: dict = Depends(admin_auth)): if "approval" in body: db().table("settings").upsert({"key": "sms_approval_template", "value": body["approval"]}).execute() if "rejection" in body: db().table("settings").upsert({"key": "sms_rejection_template", "value": body["rejection"]}).execute() return {"message": "Templates saved"} @app.post("/api/sms/send-bulk") async def send_bulk_sms(body: dict, payload: dict = Depends(admin_auth)): """ Send an SMS to multiple students (via their linked parent phone numbers). body: { message, recipients: [{name, phone, loginId}], subject } """ message = (body.get("message") or "").strip() recipients = body.get("recipients") or [] if not message: raise HTTPException(400, "Message cannot be empty") if not recipients: raise HTTPException(400, "No recipients selected") sent, failed = [], [] for rec in recipients: phone = (rec.get("phone") or "").strip() name = rec.get("name") or "" lid = rec.get("loginId") or "" if not phone: failed.append({"name": name, "reason": "No phone number"}) continue # Fill personal placeholders personalised = (message .replace("[name]", name) .replace("[login_id]", lid) .replace("[class]", rec.get("class") or "") .replace("[stream]", rec.get("stream") or "") ) ok = await send_sms(phone, personalised) if ok: sent.append(name) else: failed.append({"name": name, "reason": "Delivery failed"}) return {"sent": len(sent), "failed": len(failed), "failedList": failed} @app.get("/api/sms/recipients") async def get_sms_recipients(cls: str = "", stream: str = "", boarding: str = "", payload: dict = Depends(admin_auth)): """Return students with their parent's phone number for bulk SMS.""" q = db().table("students").select("id,login_id,full_name,class,stream,boarding") \ .eq("status", "active") if cls: q = q.eq("class", cls) if stream: q = q.eq("stream", stream) if boarding: q = q.eq("boarding", boarding) students = q.order("full_name").execute().data or [] # Build student_id → phone from student_parent_links → parents links_r = db().table("student_parent_links").select("student_id,parent_id").execute() parents_r = db().table("parents").select("id,name,phone").execute() parents_map = {p["id"]: p for p in (parents_r.data or [])} stu_id_to_phone: dict = {} for lnk in (links_r.data or []): sid = lnk.get("student_id") pid = lnk.get("parent_id") if pid and pid in parents_map and parents_map[pid].get("phone"): stu_id_to_phone[sid] = parents_map[pid]["phone"] result = [] for s in students: sid = s.get("id") # Use phone from linked parent portal account (student_parent_links → parents) phone = stu_id_to_phone.get(sid) or "" result.append({ "loginId": s["login_id"], "name": s["full_name"], "class": s.get("class") or "", "stream": s.get("stream") or "", "boarding": s.get("boarding") or "", "phone": phone }) return result @app.put("/api/sms/update-phone") async def sms_update_phone(body: dict, payload: dict = Depends(admin_auth)): """Set or update the SMS phone for a student (via linked parent record).""" login_id = (body.get("loginId") or "").strip() new_phone = (body.get("phone") or "").strip() if not login_id: raise HTTPException(400, "loginId required") if not new_phone: raise HTTPException(400, "Phone number required") # Find the student record sr = db().table("students").select("id,full_name").eq("login_id", login_id).limit(1).execute() stu = safe_one(sr) if not stu: raise HTTPException(404, "Student not found") stu_id = stu["id"] # Check for existing linked parent link_r = db().table("student_parent_links").select("parent_id").eq("student_id", stu_id).limit(1).execute() link = safe_one(link_r) if link: db().table("parents").update({"phone": new_phone}).eq("id", link["parent_id"]).execute() else: # Create a stub parent and link par_r = db().table("parents").insert({"name": f"Parent of {stu['full_name']}", "phone": new_phone}).execute() par_id = par_r.data[0]["id"] if par_r.data else None if par_id: db().table("student_parent_links").insert({"student_id": stu_id, "parent_id": par_id}).execute() return {"message": "Phone updated"} @app.post("/api/admissions/manual") async def manual_register( fullName: str = Form(...), gender: str = Form(""), dob: str = Form(""), cls: str = Form(...), stream: str = Form(...), boarding: str = Form("Day Scholar"), parentName: str = Form(""), parentPhone: str = Form(...), payload: dict = Depends(require_perm("admissions")) ): year = datetime.now().year r_login = db().rpc("next_student_login", {"p_year": year}).execute() new_login_id = r_login.data or f"GHSS001{year}" stu_r = db().table("students").insert({ "login_id": new_login_id, "full_name": fullName, "gender": gender, "dob": dob or None, "class": cls, "stream": stream, "boarding": boarding, "subjects": [], "admission_year": year, "status": "active" }).execute() stu_id = stu_r.data[0]["id"] if stu_r.data else None db().table("users").insert({ "login_id": new_login_id, "password": None, "role": "student", "name": fullName, "first_login": True, "is_active": True }).execute() # Save parent phone so bulk SMS and parent portal can find this student if stu_id and parentPhone: try: par_r = db().table("parents").insert({ "name": parentName or f"Parent of {fullName}", "phone": parentPhone, }).execute() par_id = par_r.data[0]["id"] if par_r.data else None if par_id: db().table("student_parent_links").insert({ "student_id": stu_id, "parent_id": par_id }).execute() except Exception as e: log.warning(f"Could not save parent record for {new_login_id}: {e}") await send_sms(parentPhone, f"GHSS: {fullName} has been registered. Login ID: {new_login_id}. " f"They will set their password on first login at the GHSS portal.") return {"loginId": new_login_id} # ══════════════════════════════════════════════════════════════ # STUDENTS # ══════════════════════════════════════════════════════════════ @app.get("/api/students") async def list_students(cls: str = "", stream: str = "", search: str = "", payload: dict = Depends(auth)): q = db().table("students").select("*").eq("status", "active") if cls: q = q.eq("class", cls) if stream: q = q.eq("stream", stream) if search: q = q.or_(f"full_name.ilike.%{search}%,login_id.ilike.%{search}%") r = q.order("full_name").execute() return r.data or [] @app.get("/api/students/lookup") async def lookup_student(loginId: str, payload: dict = Depends(auth)): r = db().table("students").select("login_id,full_name,class,stream,boarding") \ .eq("login_id", loginId.upper()).eq("status","active").limit(1).execute() row = safe_one(r) if not row: raise HTTPException(404, f"No student found with login ID '{loginId.upper()}'. Please check the ID.") return row # return the dict, not the list @app.get("/api/parent/children") async def parent_children(payload: dict = Depends(parent_auth)): parent_email = payload.get("loginId") pr = db().table("parents").select("id").eq("email", parent_email).limit(1).execute() parent = safe_one(pr) if not parent: return [] parent_id = parent["id"] links = db().table("student_parent_links").select("student_id").eq("parent_id", parent_id).execute() student_ids = [l["student_id"] for l in (links.data or [])] if not student_ids: return [] r2 = db().table("students").select("*").in_("id", student_ids).eq("status", "active").execute() return r2.data or [] @app.post("/api/parent/link-child") async def link_child(body: dict, payload: dict = Depends(parent_auth)): parent_email = payload.get("loginId") student_login_id = (body.get("studentLoginId") or "").strip().upper() if not student_login_id: raise HTTPException(400, "Student Login ID is required.") # Find student sr = db().table("students").select("id,full_name,class,stream").eq("login_id", student_login_id).limit(1).execute() stu = safe_one(sr) if not stu: raise HTTPException(404, f"No student found with login ID '{student_login_id}'.") # Find or create parent record pr = db().table("parents").select("id").eq("email", parent_email).limit(1).execute() parent = safe_one(pr) if not parent: ins = db().table("parents").insert({ "name": payload.get("name", parent_email), "email": parent_email }).execute() parent = safe_one(ins) or {} parent_id = parent.get("id") else: parent_id = parent["id"] if not parent_id: raise HTTPException(500, "Could not create parent profile. Please try again.") # Check limit count_r = db().table("student_parent_links").select("id").eq("parent_id", parent_id).execute() if len(count_r.data or []) >= 10: raise HTTPException(400, "You can link a maximum of 10 children per account.") # Check not already linked existing_link = db().table("student_parent_links").select("id") \ .eq("parent_id", parent_id).eq("student_id", stu["id"]).limit(1).execute() if safe_one(existing_link): raise HTTPException(400, f"{stu['full_name']} is already linked to your account.") db().table("student_parent_links").insert({"student_id": stu["id"], "parent_id": parent_id}).execute() return {"message": "Child linked successfully!", "student": stu} @app.delete("/api/parent/link-child/{login_id}") async def unlink_child(login_id: str, payload: dict = Depends(parent_auth)): parent_email = payload.get("loginId") pr = db().table("parents").select("id").eq("email", parent_email).limit(1).execute() parent = safe_one(pr) if not parent: return {"message": "Done"} sr = db().table("students").select("id").eq("login_id", login_id.upper()).limit(1).execute() stu = safe_one(sr) if stu: db().table("student_parent_links").delete() \ .eq("parent_id", parent["id"]).eq("student_id", stu["id"]).execute() return {"message": "Child unlinked"} @app.get("/api/students/{login_id}") async def get_student(login_id: str, payload: dict = Depends(auth)): r = db().table("students").select("*").eq("login_id", login_id.upper()).limit(1).execute() row = safe_one(r) if not row: raise HTTPException(404, "Student not found") return row class CreateStudent(BaseModel): fullName: str gender: str = "" dob: str = "" cls: str stream: str boarding: str = "Day Scholar" subjects: list = [] parentName: str = "" parentPhone: str = "" @app.post("/api/students") async def create_student(req: CreateStudent, payload: dict = Depends(require_perm("students"))): year = datetime.now().year r_login = db().rpc("next_student_login", {"p_year": year}).execute() new_login_id = r_login.data or f"GHSS001{year}" stu_r = db().table("students").insert({ "login_id": new_login_id, "full_name": req.fullName.strip(), "gender": req.gender, "dob": req.dob or None, "class": req.cls, "stream": req.stream, "boarding": req.boarding, "subjects": req.subjects, "admission_year": year, "status": "active" }).execute() stu_id = stu_r.data[0]["id"] if stu_r.data else None db().table("users").insert({ "login_id": new_login_id, "password": None, "role": "student", "name": req.fullName.strip(), "first_login": True, "is_active": True }).execute() # Save parent phone so bulk SMS and parent portal can find this student if stu_id and req.parentPhone: try: par_r = db().table("parents").insert({ "name": req.parentName or f"Parent of {req.fullName.strip()}", "phone": req.parentPhone, }).execute() par_id = par_r.data[0]["id"] if par_r.data else None if par_id: db().table("student_parent_links").insert({ "student_id": stu_id, "parent_id": par_id }).execute() except Exception as e: log.warning(f"Could not save parent record for {new_login_id}: {e}") await send_sms(req.parentPhone, f"GHSS: {req.fullName.strip()} has been registered. Login ID: {new_login_id}. " f"They will set their password on first login at the GHSS portal.") return {"loginId": new_login_id} @app.put("/api/students/{login_id}/subjects") async def update_student_subjects(login_id: str, body: dict, payload: dict = Depends(require_perm("students"))): db().table("students").update({"subjects": body.get("subjects", [])}).eq("login_id", login_id).execute() return {"message": "Subjects updated"} @app.put("/api/students/{login_id}/stream") async def update_student_stream(login_id: str, body: dict, payload: dict = Depends(require_perm("students"))): db().table("students").update({"stream": body.get("stream", "")}).eq("login_id", login_id).execute() return {"message": "Stream updated"} @app.put("/api/students/{login_id}/boarding") async def update_student_boarding(login_id: str, body: dict, payload: dict = Depends(require_perm("students"))): db().table("students").update({"boarding": body.get("boarding", "Day Scholar")}).eq("login_id", login_id).execute() return {"message": "Boarding updated"} @app.patch("/api/students/{login_id}/fees") async def set_student_fees_required(login_id: str, body: dict, payload: dict = Depends(require_perm("fees"))): """Allow admin to set/override a student's fees required amount for a term/year.""" term = body.get("term", get_setting("current_term", "Term 1")) year = int(body.get("year", datetime.now().year)) amount_required = int(body.get("amountRequired", 0)) existing = db().table("student_fees").select("*") \ .eq("student_login_id", login_id).eq("term", term).eq("year", year) \ .limit(1).execute() if safe_one(existing): db().table("student_fees").update({ "amount_required": amount_required, "updated_at": datetime.utcnow().isoformat() }).eq("student_login_id", login_id).eq("term", term).eq("year", year).execute() else: db().table("student_fees").insert({ "student_login_id": login_id, "term": term, "year": year, "amount_required": amount_required, "amount_paid": 0 }).execute() sync_rc_visibility(login_id, term, year) return {"message": "Fees updated and report card visibility synced"} @app.delete("/api/students/{login_id}") async def archive_student(login_id: str, payload: dict = Depends(require_perm("students"))): r = db().table("students").select("*").eq("login_id", login_id).limit(1).execute() stu = safe_one(r) if not stu: raise HTTPException(404, "Student not found") db().table("archived_students").insert({ "original_id": stu["id"], "login_id": login_id, "full_name": stu["full_name"], "last_class": stu["class"], "stream": stu.get("stream"), "admission_year": stu.get("admission_year"), "full_data": stu, "drive_folder_id": stu.get("drive_folder_id"), "reason": "archived" }).execute() db().table("students").update({"status": "archived"}).eq("login_id", login_id).execute() db().table("users").update({"is_active": False}).eq("login_id", login_id).execute() # Move Drive folder if stu.get("drive_folder_id"): archived_folder = get_archived_students_folder() if archived_folder: await drive_move_folder(stu["drive_folder_id"], archived_folder, get_active_students_folder()) return {"message": "Student archived"} # ── STUDENT PROFILE ────────────────────────────────────────────────────────── @app.get("/api/student/profile") async def student_profile(payload: dict = Depends(student_auth)): login_id = payload.get("loginId") r = db().table("students").select("*").eq("login_id", login_id).limit(1).execute() if not r.data: raise HTTPException(404, "Profile not found") return r.data @app.get("/api/student/report-cards") async def student_rcs(term: str = "", year: str = "", limit: int = 50, payload: dict = Depends(student_auth)): login_id = payload.get("loginId") q = db().table("report_cards").select("*").eq("student_login", login_id).eq("status", "published") if term: q = q.eq("term", term) if year: q = q.eq("year", int(year)) r = q.order("year", desc=True).limit(limit).execute() return r.data or [] @app.get("/api/student/resources") async def student_resources(subject: str = "", q: str = "", payload: dict = Depends(student_auth)): login_id = payload.get("loginId") stu = safe_one(db().table("students").select("class,stream").eq("login_id", login_id).limit(1).execute()) if not stu: return [] student_stream = stu.get("stream", "") query = db().table("resources").select("*").eq("class", stu["class"]).eq("is_visible", True) if student_stream: # Show resources targeting this stream OR resources with no stream (all streams) query = query.or_(f"stream.eq.{student_stream},stream.is.null") if subject: query = query.eq("subject", subject) if q: query = query.ilike("title", f"%{q}%") r = query.order("created_at", desc=True).execute() return r.data or [] @app.get("/api/student/fees") async def student_fees(payload: dict = Depends(student_auth)): login_id = payload.get("loginId") term = get_setting("current_term", "Term 1") year = datetime.now().year r = db().table("student_fees").select("*").eq("student_login_id", login_id).eq("term", term).eq("year", year).limit(1).execute() row = r.data[0] if r.data else None balance = { "amountRequired": int(row.get("amount_required") or 0) if row else 0, "amountPaid": int(row.get("amount_paid") or 0) if row else 0, } txns = db().table("transactions").select("*").eq("student_login_id", login_id).order("created_at", desc=True).execute() balance["transactions"] = txns.data or [] return balance # ══════════════════════════════════════════════════════════════ # TEACHERS # ══════════════════════════════════════════════════════════════ @app.get("/api/class-teacher-slots") async def class_teacher_slots(payload: dict = Depends(admin_auth)): """Return which class/stream slots are taken and by whom.""" taken = db().table("teachers").select("login_id,full_name,class_teacher_of") \ .eq("status", "active").not_.is_("class_teacher_of", "null").execute() taken_map = {} for t in (taken.data or []): if t.get("class_teacher_of"): taken_map[t["class_teacher_of"]] = {"loginId": t["login_id"], "name": t["full_name"]} # O-Level (S.1–S.4): streams are North, East, West # A-Level (S.5–S.6): no streams — one class teacher per class slot_defs = [ ("S.1", ["North", "East", "West"]), ("S.2", ["North", "East", "West"]), ("S.3", ["North", "East", "West"]), ("S.4", ["North", "East", "West"]), ("S.5", [""]), # No stream — label is just "S.5" ("S.6", [""]), ] slots = [] for cls, streams in slot_defs: for s in streams: label = f"{cls} {s}".strip() taken_by = taken_map.get(label) slots.append({"label": label, "class": cls, "stream": s, "takenBy": taken_by}) return slots @app.get("/api/teachers") async def list_teachers(cls: str = "", stream: str = "", subject: str = "", search: str = "", payload: dict = Depends(auth)): q = db().table("teachers").select("*").eq("status", "active") if subject: q = q.or_(f"subject.ilike.%{subject}%") if search: q = q.or_(f"full_name.ilike.%{search}%,login_id.ilike.%{search}%") r = q.order("full_name").execute() data = r.data or [] # Filter by class: teacher's classes array (JSONB/text) must contain the selected class if cls: filtered = [] for t in data: tclasses = t.get("classes") or [] if isinstance(tclasses, str): tclasses_str = tclasses.strip() if tclasses_str.startswith('{') and tclasses_str.endswith('}'): # PostgreSQL array format: {S.1,S.2} tclasses = [x.strip().strip('"') for x in tclasses_str[1:-1].split(',') if x.strip()] else: try: tclasses = json.loads(tclasses_str) except Exception: tclasses = [tclasses_str] if tclasses_str else [] if cls in tclasses: filtered.append(t) data = filtered # Filter by stream: teacher is class teacher of a room in that stream if stream: data = [t for t in data if stream in (t.get("class_teacher_of") or "")] return data class CreateTeacher(BaseModel): name: str subject: str = "" subjects: list = [] assignments: list = [] # [{cls, stream, subjects:[]}] classes: list = [] phone: str = "" # ── SUBJECT & CLASS CONSTANTS ──────────────────────────────────────────────── _CLASS_MAP = { "senior one": "S.1", "s1": "S.1", "s.1": "S.1", "senior two": "S.2", "s2": "S.2", "s.2": "S.2", "senior three": "S.3", "s3": "S.3", "s.3": "S.3", "senior four": "S.4", "s4": "S.4", "s.4": "S.4", "senior five": "S.5", "s5": "S.5", "s.5": "S.5", "senior six": "S.6", "s6": "S.6", "s.6": "S.6", } _S12_COMPULSORY = ["English Language","Mathematics","Biology","Chemistry","Physics", "History","Geography","Kiswahili","Physical Education","Entrepreneurship"] _S34_COMPULSORY = ["English Language","Mathematics","Biology","Chemistry","Physics", "History","Geography"] def _normalise_class(raw: str) -> str: return _CLASS_MAP.get(raw.strip().lower(), raw.strip()) def _resolve_subjects(cls: str, language: str, practical: str, religion: str, elective: str) -> list: opts = [x.strip() for x in [language, practical, religion, elective] if x and x.strip()] if cls in ("S.1", "S.2"): return list(_S12_COMPULSORY) + opts if cls in ("S.3", "S.4"): return list(_S34_COMPULSORY) + opts return opts # S.5/S.6 — whatever the user listed def _auto_grade(score, scale: list) -> str: try: s = int(score) for g in scale: if s >= int(g.get("min", 0)) and s <= int(g.get("max", 100)): return g.get("grade", "") except Exception: pass return "" # ── BULK STUDENT REGISTER ───────────────────────────────────────────────────── @app.post("/api/students/bulk") async def bulk_register_students(body: dict, payload: dict = Depends(require_perm("students"))): rows = body.get("rows", []) year = datetime.now().year ok = 0; fail = 0; ids: list = []; errs: list = [] for row in rows: try: name = (row.get("Name") or row.get("name") or "").strip() gender = (row.get("Gender") or row.get("gender") or "M").strip() raw_cls = (row.get("Class") or row.get("class") or "").strip() cls = _normalise_class(raw_cls) stream = (row.get("Stream") or row.get("stream") or "").strip() language = (row.get("Language") or row.get("language") or "").strip() practical= (row.get("Practical") or row.get("practical") or "").strip() religion = (row.get("Religion") or row.get("religion") or "").strip() elective = (row.get("Elective") or row.get("elective") or "").strip() if not name or not cls: fail += 1; errs.append(f"Row missing Name/Class: {row}"); continue subjects = _resolve_subjects(cls, language, practical, religion, elective) r_login = db().rpc("next_student_login", {"p_year": year}).execute() login_id = r_login.data or f"GHSS{year}{'%03d'%ok}" db().table("students").insert({ "login_id": login_id, "full_name": name, "gender": gender, "class": cls, "stream": stream, "subjects": subjects, "admission_year": year, "status": "active" }).execute() db().table("users").insert({ "login_id": login_id, "password": None, "role": "student", "name": name, "first_login": True, "is_active": True }).execute() ok += 1; ids.append(login_id) except Exception as e: fail += 1; errs.append(f"{row.get('Name','?')}: {str(e)[:80]}") return {"ok": ok, "fail": fail, "loginIds": ids, "errors": errs} # ── BULK TEACHER REGISTER ───────────────────────────────────────────────────── @app.post("/api/teachers/bulk") async def bulk_register_teachers(body: dict, payload: dict = Depends(require_perm("teachers"))): rows = body.get("rows", []) ok = 0; fail = 0; results: list = []; errs: list = [] for row in rows: try: name = (row.get("Name") or row.get("name") or "").strip() phone = (row.get("Phone") or row.get("phone") or "").strip() subject = (row.get("Subject") or row.get("subject") or "").strip() raw_cls = (row.get("Class") or row.get("class") or "").strip() cls = _normalise_class(raw_cls) if raw_cls else "" if not name: fail += 1; errs.append("Row missing Name"); continue asgn = [{"cls": cls, "stream": "", "subjects": [subject]}] if subject and cls else [] r_login = db().rpc("next_teacher_login", {}).execute() login_id = r_login.data or f"TCH{secrets.randbelow(9999)+1000}" db().table("teachers").insert({ "login_id": login_id, "full_name": name, "phone": phone, "subject": subject, "subjects": [subject] if subject else [], "classes": [cls] if cls else [], "assignments": asgn, "status": "active" }).execute() db().table("users").insert({ "login_id": login_id, "password": None, "role": "teacher", "name": name, "first_login": True, "is_active": True }).execute() ok += 1; results.append({"name": name, "loginId": login_id, "phone": phone}) except Exception as e: fail += 1; errs.append(f"{row.get('Name','?')}: {str(e)[:60]}") return {"ok": ok, "fail": fail, "results": results, "errors": errs} # ── REPORT REQUESTS (teacher-driven flow) ───────────────────────────────────── @app.post("/api/report-requests") async def create_report_request(body: dict, payload: dict = Depends(admin_auth)): cls = body.get("class", "").strip() stream = (body.get("stream") or "").strip() term = body.get("term", "").strip() year = int(body.get("year", datetime.now().year)) if not cls or not term: raise HTTPException(400, "class and term are required") dup = db().table("report_requests").select("id") \ .eq("class", cls).eq("stream", stream).eq("term", term).eq("year", year).limit(1).execute() if safe_one(dup): raise HTTPException(409, f"A request for {cls} {stream} {term} {year} already exists.") r = db().table("report_requests").insert({ "class": cls, "stream": stream, "term": term, "year": year, "status": "collecting", "created_by": payload.get("loginId") }).execute() req_id = r.data[0]["id"] if r.data else None # Auto-create submission slots for every teacher who teaches this class teachers = db().table("teachers").select("login_id,assignments").eq("status","active").execute().data or [] slots: list = [] seen: set = set() for tch in teachers: for a in (tch.get("assignments") or []): if a.get("cls") != cls: continue if stream and a.get("stream") and a.get("stream") != stream: continue for subj in (a.get("subjects") or []): key = (tch["login_id"], subj) if key in seen: continue seen.add(key) slots.append({"request_id": req_id, "teacher_login_id": tch["login_id"], "subject": subj, "status": "pending", "marks": {}}) if slots: db().table("report_submissions").insert(slots).execute() return {"id": req_id, "submissionsCreated": len(slots)} @app.get("/api/report-requests") async def list_report_requests(payload: dict = Depends(admin_auth)): reqs = db().table("report_requests").select("*").order("created_at", desc=True).execute().data or [] for req in reqs: subs = db().table("report_submissions").select("id,status").eq("request_id", req["id"]).execute().data or [] req["total"] = len(subs) req["submitted"] = sum(1 for s in subs if s["status"] == "submitted") return reqs @app.get("/api/report-requests/{req_id}") async def get_report_request(req_id: int, payload: dict = Depends(teacher_auth)): req = safe_one(db().table("report_requests").select("*").eq("id", req_id).limit(1).execute()) if not req: raise HTTPException(404, "Request not found") subs = db().table("report_submissions").select("id,teacher_login_id,subject,status,submitted_at") \ .eq("request_id", req_id).execute().data or [] tch_rows = db().table("teachers").select("login_id,full_name") \ .in_("login_id", [s["teacher_login_id"] for s in subs] or [""]).execute().data or [] tch_map = {t["login_id"]: t["full_name"] for t in tch_rows} for s in subs: s["teacher_name"] = tch_map.get(s["teacher_login_id"], s["teacher_login_id"]) req["submissions"] = subs return req @app.delete("/api/report-requests/{req_id}") async def delete_report_request(req_id: int, payload: dict = Depends(admin_auth)): db().table("report_submissions").delete().eq("request_id", req_id).execute() db().table("report_requests").delete().eq("id", req_id).execute() return {"message": "Deleted"} @app.get("/api/report-submissions/mine") async def my_report_submissions(payload: dict = Depends(teacher_auth)): login_id = payload.get("loginId") subs = db().table("report_submissions").select("id,request_id,subject,status,submitted_at") \ .eq("teacher_login_id", login_id).order("created_at", desc=True).execute().data or [] req_ids = list({s["request_id"] for s in subs}) reqs = {} if req_ids: r_rows = db().table("report_requests").select("id,class,stream,term,year,status") \ .in_("id", req_ids).execute().data or [] reqs = {r["id"]: r for r in r_rows} for s in subs: rq = reqs.get(s["request_id"], {}) s["class"] = rq.get("class",""); s["stream"] = rq.get("stream","") s["term"] = rq.get("term",""); s["year"] = rq.get("year","") return subs @app.get("/api/report-submissions/{sub_id}") async def get_report_submission(sub_id: int, payload: dict = Depends(teacher_auth)): sub = safe_one(db().table("report_submissions").select("*").eq("id", sub_id).limit(1).execute()) if not sub: raise HTTPException(404, "Submission not found") req = safe_one(db().table("report_requests").select("*").eq("id", sub["request_id"]).limit(1).execute()) if not req: raise HTTPException(404, "Request not found") q = db().table("students").select("login_id,full_name").eq("class", req["class"]).eq("status","active") if req.get("stream"): q = q.eq("stream", req["stream"]) sub["students"] = q.order("full_name").execute().data or [] sub["class"] = req["class"]; sub["stream"] = req.get("stream","") sub["term"] = req["term"]; sub["year"] = req["year"] return sub @app.put("/api/report-submissions/{sub_id}") async def save_report_submission(sub_id: int, body: dict, payload: dict = Depends(teacher_auth)): login_id = payload.get("loginId") sub = safe_one(db().table("report_submissions").select("teacher_login_id,status").eq("id", sub_id).limit(1).execute()) if not sub: raise HTTPException(404, "Submission not found") if sub["teacher_login_id"] != login_id and payload.get("role") != "admin": raise HTTPException(403, "Not your submission") action = body.get("action", "save") updates: dict = {"marks": body.get("marks", {})} if action == "submit": updates["status"] = "submitted" updates["submitted_at"] = datetime.utcnow().isoformat() db().table("report_submissions").update(updates).eq("id", sub_id).execute() return {"message": "Saved" if action == "save" else "Submitted to class teacher"} @app.post("/api/report-requests/{req_id}/publish") async def publish_report_request(req_id: int, body: dict, payload: dict = Depends(teacher_auth)): req = safe_one(db().table("report_requests").select("*").eq("id", req_id).limit(1).execute()) if not req: raise HTTPException(404, "Request not found") subs = db().table("report_submissions").select("*").eq("request_id", req_id).execute().data or [] pending = [s for s in subs if s["status"] != "submitted"] if pending and payload.get("role") != "admin": raise HTTPException(409, f"{len(pending)} teacher(s) haven't submitted yet.") # Build unified {student_login: {subject: marks_dict}} unified: dict = {} for sub in subs: for stu_login, md in (sub.get("marks") or {}).items(): unified.setdefault(stu_login, {})[sub["subject"]] = md try: scale = json.loads(get_setting("grading_scale","[]")) except Exception: scale = [] created = 0 for stu_login, subj_map in unified.items(): subjects_arr = [ {"subject": subj, "eot": md.get("eot", md.get("score",0)), "aoi": md.get("aoi",0), "chapter": md.get("chapter",""), "grade": md.get("grade") or _auto_grade(md.get("eot", md.get("score",0)), scale), "comment": md.get("comment","")} for subj, md in subj_map.items() ] ex = safe_one(db().table("report_cards").select("id") .eq("student_login_id", stu_login).eq("term", req["term"]).eq("year", req["year"]).limit(1).execute()) if ex: db().table("report_cards").update({"subjects": subjects_arr, "status": "published"}) \ .eq("student_login_id", stu_login).eq("term", req["term"]).eq("year", req["year"]).execute() else: db().table("report_cards").insert({ "student_login_id": stu_login, "class": req["class"], "stream": req.get("stream",""), "term": req["term"], "year": req["year"], "subjects": subjects_arr, "status": "published" }).execute() created += 1 db().table("report_requests").update({"status": "published"}).eq("id", req_id).execute() return {"message": "Published", "count": created} @app.post("/api/teachers") async def create_teacher(req: CreateTeacher, payload: dict = Depends(require_perm("teachers"))): r_login = db().rpc("next_teacher_login", {}).execute() new_login_id = r_login.data or f"GHSS00{secrets.randbelow(999)+1}" asgn = req.assignments or [] flat_subjects, flat_classes = _flatten_assignments(asgn) if not flat_subjects and req.subjects: flat_subjects = req.subjects if not flat_classes and req.classes: flat_classes = req.classes db().table("teachers").insert({ "login_id": new_login_id, "full_name": req.name.strip(), "subject": flat_subjects[0] if flat_subjects else req.subject, "subjects": flat_subjects, "classes": flat_classes, "assignments": asgn, "phone": req.phone, "status": "active" }).execute() db().table("users").insert({ "login_id": new_login_id, "password": None, "role": "teacher", "name": req.name.strip(), "first_login": True, "is_active": True }).execute() return {"loginId": new_login_id} @app.put("/api/teachers/{login_id}") @app.patch("/api/teachers/{login_id}") async def update_teacher(login_id: str, body: dict, payload: dict = Depends(require_perm("teachers"))): updates = {} if "subject" in body: updates["subject"] = body["subject"] if "subjects" in body: updates["subjects"] = body["subjects"] updates["subject"] = body["subjects"][0] if body["subjects"] else "" if "assignments" in body: updates["assignments"] = body["assignments"] flat_s, flat_c = _flatten_assignments(body["assignments"]) updates["subjects"] = flat_s updates["classes"] = flat_c updates["subject"] = flat_s[0] if flat_s else "" if "classes" in body: updates["classes"] = body["classes"] # Accept both camelCase (old) and snake_case (new frontend) ct_key = "classTeacherOf" if "classTeacherOf" in body else ("class_teacher_of" if "class_teacher_of" in body else None) if ct_key is not None: new_ct = body[ct_key] or None if new_ct: existing = db().table("teachers").select("login_id,full_name") \ .eq("class_teacher_of", new_ct).eq("status", "active") \ .neq("login_id", login_id).limit(1).execute() ex_row = safe_one(existing) if ex_row: raise HTTPException(409, f"{new_ct} already has a class teacher: {ex_row['full_name']}. Remove them first.") updates["class_teacher_of"] = new_ct if not updates: return {"message": "Nothing to update"} db().table("teachers").update(updates).eq("login_id", login_id).execute() return {"message": "Teacher updated"} @app.delete("/api/teachers/{login_id}") async def archive_teacher(login_id: str, payload: dict = Depends(require_perm("teachers"))): r = db().table("teachers").select("*").eq("login_id", login_id).limit(1).execute() tch = safe_one(r) if not tch: raise HTTPException(404, "Teacher not found") db().table("archived_teachers").insert({ "original_id": tch["id"], "login_id": login_id, "full_name": tch["full_name"], "subject": tch.get("subject"), "full_data": tch, "drive_folder_id": tch.get("drive_folder_id"), "reason": "archived" }).execute() db().table("teachers").update({"status": "archived"}).eq("login_id", login_id).execute() db().table("users").update({"is_active": False}).eq("login_id", login_id).execute() return {"message": "Teacher archived"} @app.get("/api/teacher-requests") async def list_teacher_requests(payload: dict = Depends(admin_auth)): r = db().table("teacher_requests").select("*").eq("status", "pending").execute() return r.data or [] @app.patch("/api/teacher-requests/{req_id}") async def decide_teacher_request(req_id: int, body: dict, payload: dict = Depends(admin_auth)): r = db().table("teacher_requests").select("*").eq("id", req_id).limit(1).execute() req_row = safe_one(r) if not req_row: raise HTTPException(404, "Request not found") status = body.get("status", "rejected") new_login_id = None if status == "approved": new_login_id = req_row.get("proposed_login") req_subjects = req_row.get("subjects") or ([req_row["subject"]] if req_row.get("subject") else []) req_asgn = req_row.get("assignments") or [] flat_s, flat_c = _flatten_assignments(req_asgn) db().table("teachers").insert({ "login_id": new_login_id, "full_name": req_row["full_name"], "subject": req_row.get("subject"), "subjects": flat_s or req_subjects, "assignments": req_asgn, "classes": flat_c, "phone": req_row.get("phone"), "status": "active" }).execute() db().table("users").insert({ "login_id": new_login_id, "password": req_row.get("password"), "role": "teacher", "name": req_row["full_name"], "first_login": False, "is_active": True }).execute() db().table("teacher_requests").update({"status": status}).eq("id", req_id).execute() return {"message": "Decision saved", "loginId": new_login_id} @app.get("/api/teacher/profile") async def teacher_profile(payload: dict = Depends(teacher_auth)): login_id = payload.get("loginId") r = db().table("teachers").select("*").eq("login_id", login_id).limit(1).execute() row = safe_one(r) if not row: raise HTTPException(404, "Profile not found") # Normalise subjects and assignments if not row.get("subjects"): single = row.get("subject", "") row["subjects"] = [single] if single else [] if not row.get("assignments"): row["assignments"] = [] return row @app.get("/api/teacher/students") async def teacher_students(cls: str = "", stream: str = "", search: str = "", payload: dict = Depends(teacher_auth)): q = db().table("students").select("*").eq("status", "active") if cls: q = q.eq("class", cls) if stream: q = q.eq("stream", stream) if search: q = q.ilike("full_name", f"%{search}%") r = q.order("full_name").execute() return r.data or [] @app.get("/api/teacher/report-cards") async def teacher_rcs(payload: dict = Depends(teacher_auth)): login_id = payload.get("loginId") r = db().table("report_cards").select("*").eq("teacher_login", login_id).order("created_at", desc=True).execute() return r.data or [] # ══════════════════════════════════════════════════════════════ # PARENT # ══════════════════════════════════════════════════════════════ @app.get("/api/parent/fees") async def parent_fees(studentLoginId: str, payload: dict = Depends(parent_auth)): term = get_setting("current_term", "Term 1") year = datetime.now().year r = db().table("student_fees").select("*").eq("student_login_id", studentLoginId).eq("term", term).eq("year", year).limit(1).execute() row = r.data[0] if r.data else None balance = { "amountRequired": int(row.get("amount_required") or 0) if row else 0, "amountPaid": int(row.get("amount_paid") or 0) if row else 0, } txns = db().table("transactions").select("*").eq("student_login_id", studentLoginId).order("created_at", desc=True).execute() balance["transactions"] = txns.data or [] return balance @app.get("/api/parent/report-cards") async def parent_rcs(studentLoginId: str, term: str = "", year: str = "", payload: dict = Depends(parent_auth)): q = db().table("report_cards").select("*").eq("student_login", studentLoginId).eq("status", "published") if term: q = q.eq("term", term) if year: q = q.eq("year", int(year)) r = q.order("year", desc=True).execute() return r.data or [] @app.post("/api/parent/upload-photos") async def parent_upload_photos( parentPhoto: Optional[UploadFile] = File(None), childPhoto: Optional[UploadFile] = File(None), childLoginId: str = Form(""), payload: dict = Depends(parent_auth) ): active_folder = get_active_students_folder() if parentPhoto: data = await parentPhoto.read() await drive_upload_file(data, "parent.jpg", parentPhoto.content_type or "image/jpeg", active_folder or "root") if childPhoto and childLoginId: stu = db().table("students").select("drive_folder_id").eq("login_id", childLoginId).limit(1).execute() stu_row = safe_one(stu) if stu_row and stu_row.get("drive_folder_id"): photos_folder = await drive_create_folder("Photos", stu_row["drive_folder_id"]) data = await childPhoto.read() await drive_upload_file(data, "student.jpg", childPhoto.content_type or "image/jpeg", photos_folder) return {"message": "Photos uploaded"} # ══════════════════════════════════════════════════════════════ # FEES & PAYMENTS # ══════════════════════════════════════════════════════════════ @app.get("/api/fees") async def list_fees(cls: str = "", stream: str = "", term: str = "", status: str = "", student: str = "", year: str = "", payload: dict = Depends(auth)): q = db().table("transactions").select("*") if cls: q = q.eq("class", cls) if stream: q = q.eq("stream", stream) if term: q = q.eq("term", term) if status: q = q.eq("status", status) if student: q = q.ilike("student_name", f"%{student}%") if year: q = q.eq("year", int(year)) r = q.order("created_at", desc=True).execute() return r.data or [] @app.post("/api/fees") async def record_fee(body: dict, payload: dict = Depends(require_perm("fees"))): term = body.get("term", get_setting("current_term", "Term 1")) year = int(body.get("year", datetime.now().year)) amount = int(body.get("amount", 0)) r = db().table("transactions").insert({ "student_login_id": body.get("studentLoginId", ""), "student_name": body.get("studentName", ""), "class": body.get("cls", ""), "stream": body.get("stream", ""), "term": term, "year": year, "amount": amount, "method": body.get("method", "Cash"), "status": body.get("status", "paid"), "payment_date": body.get("date", str(date.today())), "recorded_by": payload.get("loginId") }).execute() # Update student fees balance login_id = body.get("studentLoginId", "") if login_id: existing = db().table("student_fees").select("*").eq("student_login_id", login_id).eq("term", term).eq("year", year).limit(1).execute() if safe_one(existing): new_paid = (safe_one(existing) or {}).get("amount_paid") or 0 new_paid += amount db().table("student_fees").update({"amount_paid": new_paid, "updated_at": datetime.utcnow().isoformat()}).eq("student_login_id", login_id).eq("term", term).eq("year", year).execute() else: db().table("student_fees").insert({"student_login_id": login_id, "term": term, "year": year, "amount_required": 0, "amount_paid": amount}).execute() # Sync report card visibility after fee update sync_rc_visibility(login_id, term, year) return {"id": r.data[0]["id"] if r.data else 0} @app.delete("/api/fees/{fee_id}") async def delete_fee(fee_id: int, payload: dict = Depends(require_perm("fees"))): db().table("transactions").delete().eq("id", fee_id).execute() return {"message": "Deleted"} @app.post("/api/fees/set-term") async def set_term_fees(body: dict, payload: dict = Depends(require_perm("fees"))): cls = body.get("cls") term = body.get("term") year = int(body.get("year", datetime.now().year)) day_amt = int(body.get("dayAmount", 0)) board_amt = int(body.get("boardAmount", 0)) # Avoid upsert on_conflict issues with composite key — use select → update/insert existing_cfg = db().table("term_fees_config").select("id") \ .eq("class", cls).eq("term", term).eq("year", year) \ .limit(1).execute() if existing_cfg and existing_cfg.data: db().table("term_fees_config").update({ "day_amount": day_amt, "board_amount": board_amt, "updated_at": datetime.utcnow().isoformat() }).eq("id", existing_cfg.data[0]["id"]).execute() else: db().table("term_fees_config").insert({ "class": cls, "term": term, "year": year, "day_amount": day_amt, "board_amount": board_amt, "updated_at": datetime.utcnow().isoformat() }).execute() # Propagate fee amounts to every active student in this class. # Portals read from student_fees — without this step they always show UGX 0. # Day scholars get day_amt; boarders get board_amt. students = db().table("students").select("login_id,boarding") \ .eq("class", cls).eq("status", "active").execute() for s in (students.data or []): login_id = s["login_id"] is_boarder = (s.get("boarding") or "").strip().lower() == "boarder" amount_required = board_amt if is_boarder else day_amt existing_sf = db().table("student_fees").select("id") \ .eq("student_login_id", login_id).eq("term", term).eq("year", year) \ .limit(1).execute() if existing_sf and existing_sf.data: db().table("student_fees").update({ "amount_required": amount_required, "updated_at": datetime.utcnow().isoformat() }).eq("id", existing_sf.data[0]["id"]).execute() else: db().table("student_fees").insert({ "student_login_id": login_id, "term": term, "year": year, "amount_required": amount_required, "amount_paid": 0 }).execute() sync_rc_visibility(login_id, term, year) return {"message": "Term fees saved"} @app.get("/api/fees/term-configs") async def list_term_fee_configs(cls: str = "", term: str = "", year: str = "", payload: dict = Depends(auth)): q = db().table("term_fees_config").select("*") if cls: q = q.eq("class", cls) if term: q = q.eq("term", term) if year: q = q.eq("year", int(year)) r = q.order("year", desc=True).execute() return r.data or [] @app.delete("/api/fees/term-config/{config_id}") async def delete_term_fee_config(config_id: str, payload: dict = Depends(require_perm("fees"))): db().table("term_fees_config").delete().eq("id", config_id).execute() return {"message": "Config deleted"} @app.post("/api/fees/pay") async def initiate_payment(body: dict, payload: dict = Depends(parent_auth)): """ Mobile money payment initiation. Falls back to manual pending if API credentials are not configured. """ student_login_id = body.get("studentLoginId", "") amount = int(body.get("amount", 0)) phone = body.get("phone", "").strip() method = body.get("method", "airtel").lower() term = get_setting("current_term", "Term 1") year = datetime.now().year transaction_ref = f"GHSS-{secrets.token_hex(6).upper()}" # Save pending transaction r = db().table("transactions").insert({ "student_login_id": student_login_id, "student_name": body.get("studentName", ""), "term": term, "year": year, "amount": amount, "method": "Airtel Money" if method == "airtel" else "MTN Money", "status": "pending", "airtel_ref": transaction_ref if method == "airtel" else None, "mtn_ref": transaction_ref if method == "mtn" else None, "payment_date": str(date.today()) }).execute() tx_id = r.data[0]["id"] if r.data else 0 # Try mobile money API if method == "airtel" and AIRTEL_CLIENT_ID: try: await _airtel_push(phone, amount, transaction_ref) except Exception as e: log.warning(f"Airtel API failed: {e}") elif method == "mtn" and MTN_CLIENT_ID: try: await _mtn_push(phone, amount, transaction_ref) except Exception as e: log.warning(f"MTN API failed: {e}") # If no API configured, transaction stays pending — admin can mark as paid manually return {"transactionRef": transaction_ref, "txId": tx_id, "status": "pending"} async def _airtel_push(phone: str, amount: int, ref: str): """Airtel Money Uganda USSD Push.""" base = "https://openapiuat.airtel.africa" if AIRTEL_ENV == "sandbox" else "https://openapi.airtel.africa" async with httpx.AsyncClient() as client: # Get token tok = await client.post(f"{base}/auth/oauth2/token", json={"client_id": AIRTEL_CLIENT_ID, "client_secret": AIRTEL_CLIENT_SECRET, "grant_type": "client_credentials"}) token = tok.json().get("access_token") # Initiate payment await client.post(f"{base}/merchant/v1/payments/", headers={"Authorization": f"Bearer {token}", "X-Country": "UG", "X-Currency": "UGX"}, json={"reference": ref, "subscriber": {"country": "UG", "currency": "UGX", "msisdn": phone}, "transaction": {"amount": amount, "country": "UG", "currency": "UGX", "id": ref}}) async def _mtn_push(phone: str, amount: int, ref: str): """MTN MoMo Uganda collection.""" base = "https://sandbox.momodeveloper.mtn.com" if AIRTEL_ENV == "sandbox" else "https://proxy.momoapi.mtn.com" async with httpx.AsyncClient() as client: tok = await client.post(f"{base}/collection/token/", headers={"Authorization": f"Basic {MTN_CLIENT_ID}:{MTN_CLIENT_SECRET}", "Ocp-Apim-Subscription-Key": MTN_SUB_KEY}) token = tok.json().get("access_token") await client.post(f"{base}/collection/v1_0/requesttopay", headers={"Authorization": f"Bearer {token}", "X-Reference-Id": ref, "X-Target-Environment": "sandbox" if AIRTEL_ENV == "sandbox" else "mtnuganda", "Ocp-Apim-Subscription-Key": MTN_SUB_KEY}, json={"amount": str(amount), "currency": "UGX", "externalId": ref, "payer": {"partyIdType": "MSISDN", "partyId": phone}, "payerMessage": "GHSS School Fees", "payeeNote": "Gifted Hands SS"}) @app.post("/api/fees/webhook") async def fees_webhook(request: Request): """Webhook called by Airtel/MTN when payment confirmed.""" body = await request.json() ref = body.get("reference") or body.get("transactionId") or body.get("id") if not ref: return {"status": "ignored"} tx = db().table("transactions").select("*").or_(f"airtel_ref.eq.{ref},mtn_ref.eq.{ref}").limit(1).execute() if not tx.data: return {"status": "not_found"} tx_row = tx.data[0] db().table("transactions").update({"status": "paid"}).eq("id", tx_row["id"]).execute() login_id = tx_row.get("student_login_id") if login_id: term = tx_row.get("term"); year = tx_row.get("year"); amount = tx_row.get("amount", 0) existing = db().table("student_fees").select("*").eq("student_login_id", login_id).eq("term", term).eq("year", year).limit(1).execute() if safe_one(existing): new_paid = (safe_one(existing) or {}).get("amount_paid") or 0 new_paid += amount db().table("student_fees").update({"amount_paid": new_paid}).eq("student_login_id", login_id).eq("term", term).eq("year", year).execute() return {"status": "ok"} @app.get("/api/fees/transaction-status") async def transaction_status(ref: str, payload: dict = Depends(auth)): tx = db().table("transactions").select("status").or_(f"airtel_ref.eq.{ref},mtn_ref.eq.{ref}").limit(1).execute() row = safe_one(tx) if not row: raise HTTPException(404, "Transaction not found") return {"status": row.get("status", "pending")} # ══════════════════════════════════════════════════════════════ # REPORT CARDS # ══════════════════════════════════════════════════════════════ @app.get("/api/report-cards") async def list_rcs(cls: str = "", stream: str = "", term: str = "", year: str = "", payload: dict = Depends(auth)): q = db().table("report_cards").select("id,student_name,student_login,class,stream,term,year,average,status,is_visible") if cls: q = q.eq("class", cls) if stream: q = q.eq("stream", stream) if term: q = q.eq("term", term) if year: q = q.eq("year", int(year)) r = q.order("student_name").execute() return r.data or [] @app.get("/api/report-cards/{rc_id}") async def get_rc(rc_id: int, payload: dict = Depends(auth)): r = db().table("report_cards").select("*").eq("id", rc_id).limit(1).execute() rc = safe_one(r) if not rc: raise HTTPException(404, "Report card not found") role = payload.get("role") if role == "student" and not rc.get("is_visible"): raise HTTPException(403, "Report card locked. Pay outstanding fees to unlock.") if role == "parent" and not rc.get("is_visible"): raise HTTPException(403, "Report card locked. Outstanding fees must be paid.") return rc @app.post("/api/report-cards") async def create_rc(body: dict, payload: dict = Depends(admin_auth)): body["average"] = calc_average(body.get("subjects", [])) body["status"] = body.get("status", "draft") body["teacher_login"] = payload.get("loginId") r = db().table("report_cards").insert(body).execute() return {"id": r.data[0]["id"]} @app.patch("/api/report-cards/{rc_id}") async def update_rc(rc_id: int, body: dict, payload: dict = Depends(auth)): if "subjects" in body: body["average"] = calc_average(body["subjects"]) # When published, check fees to set is_visible if body.get("status") == "published": rc = safe_one(db().table("report_cards").select("student_login,term,year").eq("id", rc_id).limit(1).execute()) if rc: fees = db().table("student_fees").select("amount_required,amount_paid").eq("student_login_id", rc["student_login"]).eq("term", rc["term"]).eq("year", rc["year"]).limit(1).execute() if fees.data: paid = fees.data[0].get("amount_paid", 0) req = fees.data[0].get("amount_required", 0) # req == 0 means no fees required → always visible; otherwise paid must cover req body["is_visible"] = (req == 0) or (paid >= req) db().table("report_cards").update(body).eq("id", rc_id).execute() return {"message": "Updated"} @app.delete("/api/report-cards/{rc_id}") async def delete_rc(rc_id: int, payload: dict = Depends(admin_auth)): db().table("report_cards").delete().eq("id", rc_id).execute() return {"message": "Deleted"} @app.post("/api/report-cards/bulk-csv") async def bulk_rc_csv(body: dict, payload: dict = Depends(auth)): rows = body.get("rows", []) status = body.get("status", "draft") created = 0 for row in rows: subjs = row.get("subjects", []) avg = calc_average(subjs) db().table("report_cards").insert({ "student_name": row.get("studentName", ""), "student_login": row.get("studentLogin", ""), "class": row.get("cls", "S.1"), "stream": row.get("stream", ""), "term": row.get("term", "Term 1"), "year": int(row.get("year", datetime.now().year)), "subjects": subjs, "average": avg, "status": status, "teacher_login": payload.get("loginId") }).execute() created += 1 return {"created": created} @app.get("/api/rc-template") async def rc_template(cls: str, stream: str = "", term: str = "", year: str = "", chapters: str = "", payload: dict = Depends(teacher_auth)): """Generate an Excel template with all students × all subjects × 4 mark columns.""" import json as _json from openpyxl import Workbook from openpyxl.styles import PatternFill, Font, Alignment, Border, Side from openpyxl.utils import get_column_letter try: q = db().table("students").select("login_id,full_name,subjects") \ .eq("class", cls).eq("status", "active") if stream: q = q.eq("stream", stream) students = q.order("full_name").execute().data or [] all_subjs: list = [] for s in students: for sub in (s.get("subjects") or []): if sub not in all_subjs: all_subjs.append(sub) if not all_subjs: all_subjs = ["English Language", "Mathematics"] chapter_map: dict = {} if chapters: try: chapter_map = _json.loads(chapters) except Exception: pass wb = Workbook() ws = wb.active ws.title = "Marks" gold_fill = PatternFill("solid", fgColor="B8860B") grey_fill = PatternFill("solid", fgColor="2A2A2A") dark_fill = PatternFill("solid", fgColor="1A1A1A") white_bold = Font(bold=True, color="FFFFFF", size=9) gold_bold = Font(bold=True, color="B8860B", size=9) thin = Side(style="thin", color="444444") border = Border(left=thin, right=thin, top=thin, bottom=thin) center = Alignment(horizontal="center", vertical="center", wrap_text=True) ws.append([f"CLASS: {cls}", f"STREAM: {stream}", f"TERM: {term}", f"YEAR: {year}", "Fill marks 0-3 per paper. Use - if student was absent. Leave blank if subject not taken."]) end_col = 5 + len(all_subjs) * 4 if end_col > 5: ws.merge_cells(start_row=1, start_column=5, end_row=1, end_column=end_col) for c in ws[1]: c.fill = dark_fill; c.font = gold_bold; c.alignment = center chap_row = ["CHAPTERS", "—", "—", "—", "—"] for sub in all_subjs: chap = chapter_map.get(sub, "") chap_row += [chap, chap, chap, chap] ws.append(chap_row) for c in ws[2]: c.fill = grey_fill; c.font = Font(size=8, color="AAAAAA", italic=True); c.alignment = center hdr = ["StudentLogin", "StudentName", "Class", "Stream", "Term", "Year"] for sub in all_subjs: hdr += [f"{sub}\nMid P1\n(0-3)", f"{sub}\nMid P2\n(0-3)", f"{sub}\nEnd P1\n(0-3)", f"{sub}\nEnd P2\n(0-3)"] ws.append(hdr) ws.row_dimensions[3].height = 48 for c in ws[3]: c.fill = gold_fill; c.font = white_bold; c.alignment = center; c.border = border ws.freeze_panes = "C4" stu_subjects_set = {s["login_id"]: set(s.get("subjects") or []) for s in students} for row_i, s in enumerate(students, start=4): row = [s["login_id"], s["full_name"], cls, stream, term, year] for sub in all_subjs: if sub in stu_subjects_set.get(s["login_id"], set()): row += ["", "", "", ""] else: row += ["N/A", "N/A", "N/A", "N/A"] ws.append(row) fill = PatternFill("solid", fgColor="1E1E1E" if row_i % 2 == 0 else "242424") for c in ws[row_i]: c.fill = fill; c.font = Font(size=8, color="EEEEEE"); c.alignment = center; c.border = border ws.column_dimensions["A"].width = 14 ws.column_dimensions["B"].width = 22 for col_i in range(3, 7): ws.column_dimensions[get_column_letter(col_i)].width = 8 for col_i in range(7, 7 + len(all_subjs) * 4): ws.column_dimensions[get_column_letter(col_i)].width = 10 buf = io.BytesIO() wb.save(buf) buf.seek(0) fname = f"RC_{cls.replace('.','')}{stream}_{term.replace(' ','')}_{year}.xlsx" return StreamingResponse(buf, media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", headers={"Content-Disposition": f"attachment; filename={fname}"}) except Exception as e: log.error(f"rc_template error: {e}") raise HTTPException(500, f"Template generation failed: {str(e)}") @app.post("/api/rc-assignments/upload") async def upload_rc_file( assignmentId: str = Form(...), file: UploadFile = File(...), payload: dict = Depends(teacher_auth)): """Parse a filled Excel template, compute scores, and save as a submitted batch.""" import json as _json from openpyxl import load_workbook login_id = payload.get("loginId") assign_id = int(assignmentId) ar = db().table("rc_assignments").select("*").eq("id", assign_id).limit(1).execute() if not ar.data: raise HTTPException(404, "Assignment not found") assign = ar.data[0] data_bytes = await file.read() try: wb = load_workbook(io.BytesIO(data_bytes), data_only=True) ws = wb.active except Exception as e: raise HTTPException(400, f"Could not read Excel file: {e}") rows = list(ws.iter_rows(values_only=True)) if len(rows) < 4: raise HTTPException(400, "Template appears empty or corrupted") # Row 2 (index 1) = CHAPTERS row chap_row = rows[1] # Row 3 (index 2) = column headers hdr_row = rows[2] # Row 4+ (index 3+) = student data # Build subject → column index mapping from header row # Headers: StudentLogin(0) StudentName(1) Class(2) Stream(3) Term(4) Year(5) # Then groups of 4 per subject: Mid P1, Mid P2, End P1, End P2 FIXED_COLS = 6 subjects_in_order: list = [] subj_col_start: dict = {} # subject → first column index (0-based) col = FIXED_COLS while col < len(hdr_row): cell_val = str(hdr_row[col] or "") subj_name = cell_val.split("\n")[0].strip() if subj_name and subj_name not in ("N/A", "—", ""): if subj_name not in subj_col_start: subjects_in_order.append(subj_name) subj_col_start[subj_name] = col col += 4 # Chapters: row 2, same column mapping chapter_map: dict = {} for sub in subjects_in_order: ci = subj_col_start[sub] chapter_map[sub] = str(chap_row[ci] or "") if ci < len(chap_row) else "" # Collect raw marks: {subject: {student_login: [p1_mid, p2_mid, p1_end, p2_end]}} raw: dict = {sub: {} for sub in subjects_in_order} student_meta: dict = {} # login_id → full_name for row in rows[3:]: if not row or not row[0]: continue login = str(row[0]).strip() name = str(row[1] or "").strip() if not login or login.upper() == "STUDENT LOGIN": continue student_meta[login] = name for sub in subjects_in_order: ci = subj_col_start[sub] marks = [] for offset in range(4): ci2 = ci + offset v = row[ci2] if ci2 < len(row) else None marks.append(v) raw[sub][login] = marks def _parse_mark(v): """Return float, '-' (dodged), or None (blank/N/A).""" if v is None: return None s = str(v).strip() if s in ("", "N/A"): return None if s == "-": return "-" try: return float(s) except Exception: return None def _col_held(marks_per_student: dict, col_offset: int) -> bool: """True if at least one student has a real number (not all dash/blank).""" for login, marks in marks_per_student.items(): v = _parse_mark(marks[col_offset]) if col_offset < len(marks) else None if isinstance(v, float): return True return False def _compute_pct(p1_raw, p2_raw, p1_held: bool, p2_held: bool, weight: float) -> float: """Compute percentage for mid or end component (weight = 20 or 80).""" p1 = _parse_mark(p1_raw) if p1_held else None p2 = _parse_mark(p2_raw) if p2_held else None # Dodge (dash) → 0 if paper was held for others if p1_held and p1 == "-": p1 = 0.0 if p2_held and p2 == "-": p2 = 0.0 # Average held papers only vals = [v for v in [p1, p2] if isinstance(v, float)] if not vals: return 0.0 avg = sum(vals) / len(vals) return round((avg / 3.0) * weight, 2) # Per-subject, determine which papers were held mid_p1_held: dict = {} mid_p2_held: dict = {} end_p1_held: dict = {} end_p2_held: dict = {} for sub in subjects_in_order: mid_p1_held[sub] = _col_held(raw[sub], 0) mid_p2_held[sub] = _col_held(raw[sub], 1) end_p1_held[sub] = _col_held(raw[sub], 2) end_p2_held[sub] = _col_held(raw[sub], 3) # Build per-student subject records student_subjects: dict = {login: [] for login in student_meta} for sub in subjects_in_order: for login, marks in raw[sub].items(): if login not in student_meta: continue # Check if student takes this subject (not all N/A) parsed = [_parse_mark(m) for m in marks] if all(v is None for v in parsed): continue # student doesn't take this subject mid_pct = _compute_pct(marks[0], marks[1], mid_p1_held[sub], mid_p2_held[sub], 20.0) end_pct = _compute_pct(marks[2], marks[3], end_p1_held[sub], end_p2_held[sub], 80.0) total = round(mid_pct + end_pct, 2) student_subjects[login].append({ "subject": sub, "chapter": chapter_map.get(sub, ""), "mid_pct": mid_pct, "end_pct": end_pct, "total": total, }) # Load grading scale from settings grading_raw = get_setting("grading_scale", "") grading_bands: list = [] if grading_raw: try: grading_bands = _json.loads(grading_raw) except Exception: pass if not grading_bands: grading_bands = [ {"min": 2.5, "grade": "Outstanding"}, {"min": 2.0, "grade": "Very Good"}, {"min": 1.5, "grade": "Good"}, {"min": 1.0, "grade": "Fair"}, {"min": 0.5, "grade": "Basic"}, {"min": 0.0, "grade": "Below Basic"}, ] def _get_grade(merit_3: float) -> str: for band in sorted(grading_bands, key=lambda b: b["min"], reverse=True): if merit_3 >= band["min"]: return band["grade"] return "Below Basic" # Load comment bands cb_raw = get_setting("comment_bands", "") comment_bands: list = [] if cb_raw: try: comment_bands = _json.loads(cb_raw) except Exception: pass def _get_comment(total_100: float) -> str: for band in sorted(comment_bands, key=lambda b: b.get("min", 0), reverse=True): if total_100 >= band.get("min", 0): return band.get("comment", "") return "" # Fetch class teacher info tch_r = db().table("teachers").select("full_name,phone").eq("login_id", login_id).limit(1).execute() tch_row = tch_r.data[0] if tch_r.data else {} tch_name = tch_row.get("full_name", login_id) tch_phone = tch_row.get("phone", "") # Fetch subject teachers for attributes (first letter of teacher name per subject) sub_tchs_r = db().table("teachers").select("full_name,subject").eq("status", "active").execute() sub_tch_map: dict = {} # subject → first letter of teacher name for t in (sub_tchs_r.data or []): sub = t.get("subject") or "" name = t.get("full_name") or "" if sub and name and sub not in sub_tch_map: sub_tch_map[sub] = name[0].upper() # Delete old submitted records for this assignment db().table("report_cards").delete().eq("batch_id", assign_id) \ .in_("status", ["draft", "submitted"]).execute() db().table("rc_batches").delete().eq("assignment_id", assign_id) \ .in_("status", ["submitted", "rejected"]).execute() # Create batch batch = db().table("rc_batches").insert({ "assignment_id": assign_id, "teacher_login": login_id, "teacher_name": tch_name, "class": assign["class"], "stream": assign.get("stream", ""), "term": assign["term"], "year": assign["year"], "status": "submitted" }).execute() batch_id = batch.data[0]["id"] if batch.data else None # Save report cards for login, subj_list in student_subjects.items(): if not subj_list: continue totals = [s["total"] for s in subj_list] avg_100 = round(sum(totals) / len(totals), 2) if totals else 0 merit_3 = round((avg_100 / 100.0) * 3.0, 2) grade = _get_grade(merit_3) ct_comment = _get_comment(avg_100) # Add attribute to each subject record for s in subj_list: s["attribute"] = sub_tch_map.get(s["subject"], "") db().table("report_cards").insert({ "student_name": student_meta[login], "student_login": login, "class": assign["class"], "stream": assign.get("stream", ""), "term": assign["term"], "year": assign["year"], "subjects": subj_list, "average": str(merit_3), "class_teacher_name": tch_name, "class_teacher_contact": tch_phone, "class_teacher_comment": ct_comment, "teacher_login": login_id, "status": "submitted", "batch_id": batch_id, "is_visible": False, }).execute() db().table("rc_assignments").update({ "status": "submitted", "submitted_at": datetime.utcnow().isoformat() }).eq("id", assign_id).execute() return {"message": "Submitted for admin review", "students": len(student_subjects), "batchId": batch_id} @app.post("/api/rc-assignments/assign-all") async def assign_all_class_teachers(body: dict, payload: dict = Depends(require_perm("report_cards"))): """Assign report card task to every class teacher in one click.""" term = body.get("term", get_setting("current_term", "Term 1")) year = int(body.get("year", datetime.now().year)) note = body.get("note", "") teachers = db().table("teachers").select("login_id,class_teacher_of") \ .eq("status", "active").not_.is_("class_teacher_of", "null").execute().data or [] count = 0 for t in teachers: ct_of = (t.get("class_teacher_of") or "").strip() if not ct_of: continue parts = ct_of.split(" ", 1) cls = parts[0] stream = parts[1] if len(parts) > 1 else "" db().table("rc_assignments").insert({ "teacher_login": t["login_id"], "class": cls, "stream": stream, "term": term, "year": year, "note": note, "status": "pending" }).execute() count += 1 return {"message": f"Assigned to {count} class teacher(s)", "count": count} @app.patch("/api/rc-batches/{batch_id}/comments") async def add_batch_comments(batch_id: int, body: dict, payload: dict = Depends(require_perm("report_cards"))): """Set warden and/or head teacher (principal) comment on every card in a batch.""" updates: dict = {} if "wardenComment" in body: updates["warden_comment"] = body["wardenComment"] if "principalComment" in body: updates["principal_comment"] = body["principalComment"] if not updates: raise HTTPException(400, "No comments provided") db().table("report_cards").update(updates).eq("batch_id", batch_id).execute() return {"message": "Comments saved"} @app.post("/api/rc-batches/publish-all") async def publish_all_batches(payload: dict = Depends(require_perm("report_cards"))): """Approve and publish every submitted batch at once.""" batches = db().table("rc_batches").select("id").eq("status", "submitted").execute().data or [] published = 0 for b in batches: bid = b["id"] db().table("rc_batches").update({"status": "approved"}).eq("id", bid).execute() db().table("report_cards").update({"status": "published", "is_visible": True}) \ .eq("batch_id", bid).execute() published += 1 return {"message": f"Published {published} batch(es)", "batches": published} @app.get("/api/settings/grading") async def get_grading(payload: dict = Depends(admin_auth)): import json as _json raw = get_setting("grading_scale", "") if raw: try: return {"bands": _json.loads(raw)} except Exception: pass return {"bands": [ {"min": 2.5, "grade": "Outstanding"}, {"min": 2.0, "grade": "Very Good"}, {"min": 1.5, "grade": "Good"}, {"min": 1.0, "grade": "Fair"}, {"min": 0.5, "grade": "Basic"}, {"min": 0.0, "grade": "Below Basic"}, ]} @app.put("/api/settings/grading") async def save_grading(body: dict, payload: dict = Depends(admin_auth)): import json as _json bands = body.get("bands", []) db().table("settings").upsert({"key": "grading_scale", "value": _json.dumps(bands)}).execute() return {"message": "Grading scale saved"} @app.get("/api/teacher/comment-bands") async def get_comment_bands(payload: dict = Depends(teacher_auth)): import json as _json raw = get_setting("comment_bands", "") if raw: try: return {"bands": _json.loads(raw)} except Exception: pass return {"bands": [ {"min": 80, "label": "A", "comment": "Excellent performance. Keep it up!"}, {"min": 65, "label": "B", "comment": "Good performance. Work harder to improve."}, {"min": 50, "label": "C", "comment": "Fair performance. More effort is needed."}, {"min": 35, "label": "D", "comment": "Below average. Seek extra help."}, {"min": 0, "label": "F", "comment": "Poor performance. Must work much harder."}, ]} @app.put("/api/teacher/comment-bands") async def save_comment_bands(body: dict, payload: dict = Depends(teacher_auth)): import json as _json bands = body.get("bands", []) db().table("settings").upsert({"key": "comment_bands", "value": _json.dumps(bands)}).execute() return {"message": "Comment bands saved"} # ── RC ASSIGNMENTS ───────────────────────────────────────────── @app.post("/api/rc-assignments") async def create_rc_assignment(body: dict, payload: dict = Depends(require_perm("report_cards"))): db().table("rc_assignments").insert({ "teacher_login": body.get("teacherLoginId"), "class": body.get("cls"), "stream": body.get("stream", ""), "term": body.get("term"), "year": int(body.get("year", datetime.now().year)), "note": body.get("note", ""), "status": "pending" }).execute() return {"message": "Assignment sent"} @app.get("/api/rc-assignments/all") async def all_rc_assignments(payload: dict = Depends(require_perm("report_cards"))): r = db().table("rc_assignments").select("*").order("assigned_at", desc=True).execute() return r.data or [] @app.get("/api/rc-assignments/mine") async def my_rc_assignments(status: str = "", payload: dict = Depends(teacher_auth)): login_id = payload.get("loginId") q = db().table("rc_assignments").select("*").eq("teacher_login", login_id) if status: q = q.eq("status", status) r = q.order("assigned_at", desc=True).execute() return r.data or [] @app.post("/api/rc-assignments/{assign_id}/submit") async def submit_rc_batch(assign_id: int, body: dict, payload: dict = Depends(teacher_auth)): login_id = payload.get("loginId") # Safe fetch — don't use .single() which raises if missing ar = db().table("rc_assignments").select("*").eq("id", assign_id).limit(1).execute() if not ar.data: raise HTTPException(404, "Assignment not found") assign = ar.data[0] records = body.get("records", []) comment = body.get("classTchrComment", "") # Delete any previously submitted (draft/submitted) cards for this assignment to prevent duplicates db().table("report_cards").delete() \ .eq("batch_id", assign.get("id")) \ .in_("status", ["draft", "submitted"]).execute() # Also delete old batches for this assignment that weren't approved db().table("rc_batches").delete() \ .eq("assignment_id", assign_id) \ .in_("status", ["submitted", "rejected"]).execute() # Resolve teacher name once tn_r = db().table("teachers").select("full_name").eq("login_id", login_id).limit(1).execute() teacher_name = tn_r.data[0].get("full_name", login_id) if tn_r.data else login_id # Create new batch batch = db().table("rc_batches").insert({ "assignment_id": assign_id, "teacher_login": login_id, "teacher_name": teacher_name, "class": assign.get("class"), "stream": assign.get("stream"), "term": assign.get("term"), "year": assign.get("year"), "status": "submitted" }).execute() batch_id = batch.data[0]["id"] if batch.data else None for rec in records: avg = calc_average(rec.get("subjects", [])) db().table("report_cards").insert({ "student_name": rec.get("studentName", ""), "student_login": rec.get("studentLoginId", ""), "class": assign.get("class"), "stream": assign.get("stream"), "term": assign.get("term"), "year": assign.get("year"), "subjects": rec.get("subjects", []), "average": avg, "class_teacher_comment": comment, "teacher_login": login_id, "status": "submitted", "batch_id": batch_id, "is_visible": False }).execute() db().table("rc_assignments").update({ "status": "submitted", "submitted_at": datetime.utcnow().isoformat() }).eq("id", assign_id).execute() return {"message": "Submitted for admin approval", "batchId": batch_id} @app.post("/api/rc-assignments/upload") async def upload_rc_file(assignmentId: str = Form(...), file: UploadFile = File(...), payload: dict = Depends(teacher_auth)): return {"message": "File received — will be processed", "assignmentId": assignmentId} @app.get("/api/rc-batches") async def list_rc_batches(status: str = "", payload: dict = Depends(require_perm("report_cards"))): q = db().table("rc_batches").select("*") if status: q = q.eq("status", status) r = q.order("submitted_at", desc=True).execute() return r.data or [] @app.patch("/api/rc-batches/{batch_id}") async def update_rc_batch(batch_id: int, body: dict, payload: dict = Depends(require_perm("report_cards"))): new_status = body.get("status") if new_status not in ("approved", "rejected"): raise HTTPException(400, "status must be 'approved' or 'rejected'") db().table("rc_batches").update({"status": new_status}).eq("id", batch_id).execute() if new_status == "approved": db().table("report_cards").update({"status": "published"}).eq("batch_id", batch_id).execute() # Set is_visible per student based on fees rcs = db().table("report_cards").select("id,student_login,term,year").eq("batch_id", batch_id).execute().data or [] for rc in rcs: sl = rc.get("student_login") or "" if not sl: # No student linked → visible by default db().table("report_cards").update({"is_visible": True}).eq("id", rc["id"]).execute() continue try: fees = db().table("student_fees").select("amount_required,amount_paid") \ .eq("student_login_id", sl).eq("term", rc["term"]).eq("year", rc["year"]) \ .limit(1).execute() except Exception: fees = None if fees and fees.data: fee_row = fees.data[0] paid = fee_row.get("amount_paid") or 0 req = fee_row.get("amount_required") or 0 # req == 0 → free, card visible; otherwise must be fully paid visible = (req == 0) or (paid >= req) else: # No fees record → treat as free, show the card visible = True db().table("report_cards").update({"is_visible": visible}).eq("id", rc["id"]).execute() elif new_status == "rejected": db().table("report_cards").update({"status": "draft"}).eq("batch_id", batch_id).execute() return {"message": "Batch updated"} # ══════════════════════════════════════════════════════════════ # RESOURCES # ══════════════════════════════════════════════════════════════ @app.get("/api/resources") async def list_resources(cls: str = "", subject: str = "", q: str = "", teacher: str = "", payload: dict = Depends(auth)): query = db().table("resources").select("*").eq("is_visible", True) if cls: query = query.eq("class", cls) if subject: query = query.eq("subject", subject) if q: query = query.ilike("title", f"%{q}%") if teacher == "me": query = query.eq("teacher_login_id", payload.get("loginId")) r = query.order("created_at", desc=True).execute() return r.data or [] @app.post("/api/resources") async def upload_resource( title: str = Form(...), cls: str = Form(...), subject: str = Form(...), stream: str = Form(""), file: UploadFile = File(...), payload: dict = Depends(teacher_auth) ): login_id = payload.get("loginId") tch = db().table("teachers").select("full_name").eq("login_id", login_id).limit(1).execute() teacher_name = tch.data[0].get("full_name", login_id) if tch.data else login_id file_bytes = await file.read() file_ext = file.filename.split(".")[-1].lower() if file.filename else "file" if not B2_KEY_ID: raise HTTPException(503, "File storage (Backblaze B2) is not configured. Ask the administrator to add the B2 secrets.") safe_cls = cls.replace(".", "").replace(" ", "_") safe_sub = subject.replace(" ", "_").replace("/", "_") b2_path = f"resources/{safe_cls}/{safe_sub}/{file.filename}" b2_key = await b2_upload(file_bytes, b2_path, file.content_type or "application/octet-stream") if not b2_key: raise HTTPException(503, "File upload to Backblaze B2 failed. Check the server logs or verify the B2 credentials.") r = db().table("resources").insert({ "teacher_login_id": login_id, "teacher_name": teacher_name, "title": title.strip(), "subject": subject, "class": cls, "file_name": file.filename, "file_type": file_ext, "drive_file_id": b2_key, # column reused — stores B2 path "stream": stream or None, "is_visible": True }).execute() return {"id": r.data[0]["id"] if r.data else 0} @app.get("/api/resources/{res_id}/download") async def download_resource(res_id: int, token: str = "", payload: dict = Depends(auth)): r = db().table("resources").select("drive_file_id,file_name").eq("id", res_id).limit(1).execute() res = safe_one(r) if not res: raise HTTPException(404, "Resource not found") fid = res.get("drive_file_id") if not fid: raise HTTPException(503, "File not available — it may not have been uploaded correctly.") # Support both B2 paths (contain "/") and legacy Google Drive file IDs if "/" in fid and B2_KEY_ID: url = await b2_download_url(fid, valid_seconds=3600) else: url = await drive_signed_url(fid) if not url: raise HTTPException(503, "Could not generate download link. Check storage configuration.") return {"url": url} # frontend opens this directly — avoids CORS issues on cross-origin redirect @app.delete("/api/resources/{res_id}") async def delete_resource(res_id: int, payload: dict = Depends(require_perm("resources"))): row = safe_one(db().table("resources").select("drive_file_id").eq("id", res_id).limit(1).execute()) if row: fid = row.get("drive_file_id") or "" if fid and "/" in fid and B2_KEY_ID: await b2_delete_file(fid) # delete from B2; non-fatal if it fails db().table("resources").update({"is_visible": False}).eq("id", res_id).execute() return {"message": "Removed"} # ══════════════════════════════════════════════════════════════ # PROGRAMME & ANNOUNCEMENTS # ══════════════════════════════════════════════════════════════ @app.get("/api/programme") async def list_programme(upcoming: bool = False, limit: int = 50): q = db().table("programme").select("*") if upcoming: q = q.gte("date", str(date.today())) r = q.order("date").limit(limit).execute() return r.data or [] @app.post("/api/programme") async def create_programme(body: dict, payload: dict = Depends(require_perm("programme"))): r = db().table("programme").insert(body).execute() return {"id": r.data[0]["id"]} @app.put("/api/programme/{prog_id}") async def update_programme(prog_id: int, body: dict, payload: dict = Depends(require_perm("programme"))): db().table("programme").update(body).eq("id", prog_id).execute() return {"message": "Updated"} @app.delete("/api/programme/{prog_id}") async def delete_programme(prog_id: int, payload: dict = Depends(require_perm("programme"))): db().table("programme").delete().eq("id", prog_id).execute() return {"message": "Deleted"} @app.get("/api/announcements") async def list_announcements(audience: str = "", limit: int = 50): q = db().table("announcements").select("*") if audience and audience not in ("all", "All"): q = q.or_(f"audience.eq.All,audience.eq.{audience}") r = q.order("created_at", desc=True).limit(limit).execute() return r.data or [] @app.post("/api/announcements") async def create_announcement(body: dict, payload: dict = Depends(require_perm("announcements"))): body["posted_by"] = payload.get("loginId") r = db().table("announcements").insert(body).execute() return {"id": r.data[0]["id"]} @app.delete("/api/announcements/{ann_id}") async def delete_announcement(ann_id: int, payload: dict = Depends(require_perm("announcements"))): db().table("announcements").delete().eq("id", ann_id).execute() return {"message": "Deleted"} # ══════════════════════════════════════════════════════════════ # FEEDBACK # ══════════════════════════════════════════════════════════════ @app.get("/api/feedback") async def list_feedback(payload: dict = Depends(admin_auth)): r = db().table("feedback").select("*").order("submitted_at", desc=True).execute() return r.data or [] @app.post("/api/feedback") async def create_feedback(body: dict): title = (body.get("title") or "").strip() desc = (body.get("description") or "").strip() if not title or not desc: raise HTTPException(400, "Title and description are required") db().table("feedback").insert({"title": title, "description": desc}).execute() return {"message": "Feedback received"} @app.patch("/api/feedback/{fb_id}") async def update_feedback(fb_id: int, body: dict, payload: dict = Depends(require_perm("feedback"))): db().table("feedback").update({"is_read": body.get("is_read", True)}).eq("id", fb_id).execute() return {"message": "Updated"} @app.delete("/api/feedback/{fb_id}") async def delete_feedback(fb_id: int, payload: dict = Depends(require_perm("feedback"))): db().table("feedback").delete().eq("id", fb_id).execute() return {"message": "Deleted"} # ══════════════════════════════════════════════════════════════ # TIMETABLE # ══════════════════════════════════════════════════════════════ @app.get("/api/timetable/{cls}/{stream}") async def get_timetable(cls: str, stream: str): r = db().table("timetable").select("*").eq("class", cls).eq("stream", stream).execute() return r.data or [] @app.put("/api/timetable") async def update_timetable(body: dict, payload: dict = Depends(require_perm("timetable"))): cls = body.get("cls") stream = body.get("stream") day_index = body.get("dayIndex") period_index = body.get("periodIndex") subject = body.get("subject") or None # Try UPDATE first; if no row exists, INSERT (avoids upsert on_conflict issues) existing = db().table("timetable").select("id") \ .eq("class", cls).eq("stream", stream) \ .eq("day_index", day_index).eq("period_index", period_index) \ .limit(1).execute() if existing and existing.data: db().table("timetable").update({ "subject": subject, "updated_at": datetime.utcnow().isoformat() }).eq("id", existing.data[0]["id"]).execute() else: db().table("timetable").insert({ "class": cls, "stream": stream, "day_index": day_index, "period_index": period_index, "subject": subject, "updated_at": datetime.utcnow().isoformat() }).execute() return {"message": "Updated"} # ══════════════════════════════════════════════════════════════ # PASSWORDS (admin view/change) # ══════════════════════════════════════════════════════════════ @app.get("/api/users/{login_id}/password") async def get_user_password(login_id: str, payload: dict = Depends(admin_auth)): r = db().table("users").select("password,first_login").eq("login_id", login_id).limit(1).execute() if not r.data: raise HTTPException(404, "User not found") return {"password": r.data[0].get("password") or "(not set yet)", "firstLogin": r.data[0].get("first_login")} @app.put("/api/users/{login_id}/password") async def set_user_password(login_id: str, body: dict, payload: dict = Depends(admin_auth)): db().table("users").update({"password": body.get("password"), "first_login": False}).eq("login_id", login_id).execute() return {"message": "Password updated"} # ══════════════════════════════════════════════════════════════ # ARCHIVED # ══════════════════════════════════════════════════════════════ @app.get("/api/archived/students") async def list_archived_students(cls: str = "", year: str = "", search: str = "", payload: dict = Depends(admin_auth)): q = db().table("archived_students").select("*") if cls: q = q.eq("last_class", cls) if year: q = q.eq("admission_year", int(year)) if search: q = q.ilike("full_name", f"%{search}%") r = q.order("archived_at", desc=True).execute() return r.data or [] @app.post("/api/archived/students/{login_id}/revive") async def revive_student(login_id: str, payload: dict = Depends(admin_auth)): arch = safe_one(db().table("archived_students").select("*").eq("login_id", login_id).limit(1).execute()) if not arch: raise HTTPException(404, "Archived student not found") full = arch.get("full_data", {}) full["status"] = "active" # Re-insert into students (upsert by login_id) db().table("students").upsert(full).execute() db().table("users").update({"is_active": True}).eq("login_id", login_id).execute() db().table("archived_students").delete().eq("login_id", login_id).execute() # Move Drive folder back if arch.get("drive_folder_id"): active_folder = get_active_students_folder() if active_folder: await drive_move_folder(arch["drive_folder_id"], active_folder, get_archived_students_folder()) return {"message": "Student revived"} @app.delete("/api/archived/students/{login_id}") async def permanently_delete_student(login_id: str, payload: dict = Depends(admin_auth)): """Permanently remove an archived student and all associated records.""" arch = safe_one(db().table("archived_students").select("id").eq("login_id", login_id).limit(1).execute()) if not arch: raise HTTPException(404, "Archived student not found") db().table("archived_students").delete().eq("login_id", login_id).execute() db().table("students").delete().eq("login_id", login_id).execute() db().table("users").delete().eq("login_id", login_id).execute() db().table("student_fees").delete().eq("student_login_id", login_id).execute() db().table("report_cards").delete().eq("student_login", login_id).execute() return {"message": "Student permanently deleted"} @app.get("/api/archived/teachers") async def list_archived_teachers(search: str = "", payload: dict = Depends(admin_auth)): q = db().table("archived_teachers").select("*") if search: q = q.ilike("full_name", f"%{search}%") r = q.order("archived_at", desc=True).execute() return r.data or [] @app.post("/api/archived/teachers/{login_id}/revive") async def revive_teacher(login_id: str, payload: dict = Depends(admin_auth)): arch = safe_one(db().table("archived_teachers").select("*").eq("login_id", login_id).limit(1).execute()) if not arch: raise HTTPException(404, "Archived teacher not found") full = arch.get("full_data", {}) full["status"] = "active" db().table("teachers").upsert(full).execute() db().table("users").update({"is_active": True}).eq("login_id", login_id).execute() db().table("archived_teachers").delete().eq("login_id", login_id).execute() return {"message": "Teacher revived"} # ══════════════════════════════════════════════════════════════ # SETTINGS & SYSTEM # ══════════════════════════════════════════════════════════════ @app.get("/api/admission-config") async def get_admission_config(): keys = ["admission_open","academic_year","admission_fee","admission_deadline","admission_report_date", "phone1","phone2","school_email","head_teacher","school_notice","last_backup"] r = db().table("settings").select("key,value").in_("key", keys).execute() out = {row["key"]: row["value"] for row in (r.data or [])} return { "open": out.get("admission_open") == "true", "year": out.get("academic_year"), "admissionFee": out.get("admission_fee"), "deadline": out.get("admission_deadline"), "reportDate": out.get("admission_report_date"), "phone1": out.get("phone1"), "phone2": out.get("phone2"), "email": out.get("school_email"), "headTeacher": out.get("head_teacher"), "notice": out.get("school_notice"), "lastBackup": out.get("last_backup") } @app.put("/api/admission-config") async def save_admission_config(body: dict, payload: dict = Depends(admin_auth)): mapping = { "open": ("admission_open", lambda v: "true" if v else "false"), "year": ("academic_year", str), "admissionFee": ("admission_fee", str), "deadline": ("admission_deadline", str), "reportDate": ("admission_report_date", str), "phone1": ("phone1", str), "phone2": ("phone2", str), "email": ("school_email", str), "headTeacher": ("head_teacher", str), "notice": ("school_notice", str) } for field, (key, conv) in mapping.items(): if field in body: set_setting(key, conv(body[field])) return {"message": "Settings saved"} @app.post("/api/settings/backup") async def backup_now(payload: dict = Depends(admin_auth)): tables = ["students","teachers","users","transactions","student_fees","report_cards", "announcements","programme","admissions","feedback","timetable","resources"] snapshot = {} for tbl in tables: try: r = db().table(tbl).select("*").execute() snapshot[tbl] = r.data or [] except Exception: snapshot[tbl] = [] backup_json = json.dumps(snapshot, default=str) backup_bytes = backup_json.encode() now = datetime.utcnow() filename = f"backup_{now.strftime('%Y-%m-%d_%H%M')}.json" stored_id = None if B2_KEY_ID: stored_id = await b2_upload(backup_bytes, f"backups/{filename}", "application/json") elif GOOGLE_SA_JSON: backups_folder = get_backups_folder() if backups_folder: stored_id = await drive_upload_file(backup_bytes, filename, "application/json", backups_folder) # Save record expires = now + timedelta(days=7) db().table("backups").insert({ "drive_file_id": stored_id, "drive_file_name": filename, "size_bytes": len(backup_bytes), "created_by": payload.get("loginId"), "expires_at": expires.isoformat() }).execute() set_setting("last_backup", now.isoformat()) # Delete old backups (older than 7 days) old_cutoff = (now - timedelta(days=7)).isoformat() db().table("backups").delete().lt("expires_at", old_cutoff).execute() return {"message": "Backup complete", "filename": filename} @app.post("/api/settings/promote") async def promote_students(payload: dict = Depends(admin_auth)): year = datetime.now().year if get_setting(f"promotion_done_{year}") == "true": raise HTTPException(400, f"Promotion for {year} already done") class_order = ["S.1","S.2","S.3","S.4","S.5","S.6"] promoted = 0; archived_count = 0 for cls in reversed(class_order): students = db().table("students").select("*").eq("class", cls).eq("status", "active").execute().data or [] if cls == "S.6": for stu in students: db().table("archived_students").insert({ "original_id": stu["id"], "login_id": stu["login_id"], "full_name": stu["full_name"], "last_class": stu["class"], "stream": stu.get("stream"), "admission_year": stu.get("admission_year"), "full_data": stu, "drive_folder_id": stu.get("drive_folder_id"), "reason": "graduated" }).execute() db().table("students").update({"status": "archived"}).eq("login_id", stu["login_id"]).execute() db().table("users").update({"is_active": False}).eq("login_id", stu["login_id"]).execute() archived_count += 1 else: next_cls = class_order[class_order.index(cls) + 1] for stu in students: db().table("students").update({"class": next_cls}).eq("login_id", stu["login_id"]).execute() promoted += 1 set_setting(f"promotion_done_{year}", "true") set_setting("academic_year", str(year + 1)) return {"promoted": promoted, "archived": archived_count} # ══════════════════════════════════════════════════════════════ # SUB-ADMINS # ══════════════════════════════════════════════════════════════ @app.get("/api/admin/sub-admins") async def list_sub_admins(payload: dict = Depends(admin_auth)): r = db().table("users").select("login_id,name,sub_role,permissions,is_active,created_at").eq("role", "sub_admin").execute() data = r.data or [] for row in data: row["full_name"] = row.get("name") return data @app.post("/api/admin/sub-admins") async def create_sub_admin(body: dict, payload: dict = Depends(admin_auth)): if payload.get("role") != "admin": raise HTTPException(403, "Only the main admin can create sub-admins") # Use teacher sequential login for sub-admins r_login = db().rpc("next_teacher_login", {}).execute() new_login_id = r_login.data or f"SADM{secrets.token_hex(3).upper()}" db().table("users").insert({ "login_id": new_login_id, "password": body.get("password"), "role": "sub_admin", "name": body.get("name"), "sub_role": body.get("subRole"), "permissions": body.get("permissions", []), "first_login": False, "is_active": True }).execute() return {"loginId": new_login_id} @app.delete("/api/admin/sub-admins/{login_id}") async def delete_sub_admin(login_id: str, payload: dict = Depends(admin_auth)): db().table("users").delete().eq("login_id", login_id).eq("role", "sub_admin").execute() return {"message": "Deleted"} # ══════════════════════════════════════════════════════════════ # STARTUP # ══════════════════════════════════════════════════════════════ @app.on_event("startup") async def startup_event(): log.info("GHSS Backend starting up…") if not SUPABASE_URL: log.warning("SUPABASE_URL not set — database calls will fail") if B2_KEY_ID: log.info("Backblaze B2 configured — file storage active (admissions, resources, backups)") elif not GOOGLE_SA_JSON: log.warning("Neither B2_KEY_ID nor GOOGLE_SERVICE_ACCOUNT_JSON set — file uploads will be skipped") if not SMS_API_KEY: log.info("SMS_API_KEY not set — SMS will be logged only (not sent)") if not AIRTEL_CLIENT_ID: log.info("AIRTEL_CLIENT_ID not set — payments fall back to manual cash mode") # Auto-setup Google Drive folders only when Drive is configured and B2 is NOT the primary store. # (Drive browser still works independently if both are configured.) if not B2_KEY_ID and GOOGLE_SA_JSON: try: await _auto_setup_drive() except Exception as e: log.warning(f"Drive auto-setup failed (non-fatal): {e}") log.info("Startup complete.") async def _auto_setup_drive(): """Ensure all Drive folders exist and are shared with the school admin.""" ADMIN_EMAIL = "mubarakkabuye@gmail.com" token = get_drive_service() if not token: log.warning("Drive auto-setup: could not get Drive token, skipping.") return root_id = await drive_make_root_folder("Gifted Hands Senior School") if not root_id: log.warning("Drive auto-setup: could not create/find root folder.") return set_setting("drive_root_folder_id", root_id) students_active = await drive_find_or_create_folder("Students — Active", root_id) students_archived = await drive_find_or_create_folder("Students — Archived", root_id) work_folder = await drive_find_or_create_folder("Staff Work", root_id) backups_folder = await drive_find_or_create_folder("Database Backups", root_id) resources_folder = await drive_find_or_create_folder("Learning Resources", root_id) for fid, key in [ (students_active, "drive_active_students_folder_id"), (students_archived, "drive_archived_students_folder_id"), (work_folder, "drive_work_folder_id"), (backups_folder, "drive_backups_folder_id"), (resources_folder, "drive_resources_folder_id"), ]: if fid: set_setting(key, fid) log.info(f"Drive auto-setup complete. Root folder: {root_id}.") def _flatten_assignments(assignments: list): """Derive flat subjects and classes lists from assignments array.""" subjects_set, classes_set = set(), set() for a in assignments: for s in (a.get("subjects") or []): subjects_set.add(s) if a.get("cls"): classes_set.add(a["cls"]) return sorted(subjects_set), sorted(classes_set)