main.py

"""
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)