/**
 * ============================================================================
 * LamaStudio School Suite — Multi-Tenant, Multi-Role Backend (Google Apps Script)
 * ============================================================================
 * ONE deployment of this script serves EVERY school on lamastudio.pk, across
 * FOUR roles:
 *   - Super Admin (you)   — sees every school, blocks/unblocks accounts,
 *                           resets a school's password.
 *   - School Admin        — the school's own login (unchanged from before).
 *   - Teacher             — created by the School Admin, scoped to one class.
 *   - Student             — logs in with enrollment no. + password, read-only,
 *                           sees only their own record and their own class.
 *
 * Each school still owns one private Google Sheet (auto-created at
 * registration) holding Students / Teachers / Attendance / Homework / Fees /
 * DateSheet / Tests / Notifications. A single "Master Registry" spreadsheet
 * maps schoolId -> that school's Sheet + login credentials + active/blocked
 * status. No school can read or write another school's data.
 *
 * ----------------------------------------------------------------------------
 * ONE-TIME SETUP (do this once, in order):
 * ----------------------------------------------------------------------------
 * 1. Go to https://script.google.com -> New project.
 * 2. Delete the placeholder code, paste this ENTIRE file in.
 * 3. In the toolbar function dropdown, select "setup", then click Run.
 *    Approve the permissions Google asks for. This creates your
 *    "LamaStudio Master Registry" spreadsheet.
 * 4. Open this file, find SUPER_ADMIN_PASSWORD_PLAINTEXT near the top,
 *    change 'change-this-password' to whatever you want your own super
 *    admin password to be. Select "setupSuperAdmin" in the function
 *    dropdown, click Run. This hashes and stores it — the plaintext in
 *    the code is never used again after this, so you can leave it (or
 *    blank it out) once it's run.
 * 5. Click Deploy -> New deployment.
 *      Select type: Web app
 *      Execute as: Me
 *      Who has access: Anyone
 *    Click Deploy, authorize again if asked.
 * 6. Copy the "Web app URL" (it ends in /exec). Paste it into api-client.js
 *    as the API_URL constant — that's the only thing that file needs from you.
 *
 * ----------------------------------------------------------------------------
 * WHEN YOU EDIT THIS CODE LATER:
 * ----------------------------------------------------------------------------
 * Deploy -> Manage deployments -> click the pencil (edit) icon on your
 * existing deployment -> Version: "New version" -> Deploy.
 * This keeps the SAME URL working (don't create a brand-new deployment,
 * or every page's api-client.js would need updating with a new URL).
 * ============================================================================
 */

const REGISTRY_SHEET_NAME = 'Schools';
const REGISTRY_HEADERS = [
    'schoolId', 'name', 'address', 'phone', 'principal', 'type', 'level', 'adminEmail',
    'passwordHash', 'apiKey', 'sheetId', 'logo', 'coverPhoto', 'shortCode', 'plan', 'status', 'createdAt'
];
const SUPER_ADMIN_PASSWORD_PLAINTEXT = 'change-this-password'; // edit, then run setupSuperAdmin() once

// Every collection listed here automatically gets full CRUD via the generic
// list/get/add/update/remove functions further down. Field names ending in
// "Json" are stored as JSON text in one cell and parsed back transparently.
const COLLECTIONS = {
    students: {
        tab: 'Students',
        headers: ['id', 'enrlNo', 'name', 'gender', 'dob', 'fatherGuardian', 'contact', 'whatsapp', 'class', 'section',
                  'subjectsJson', 'position', 'photo', 'behavioralJson', 'attendance',
                  'teacherRemarks', 'principalRemarks', 'remarks', 'password', 'apiKey', 'createdAt',
                  'cnic', 'religion', 'category', 'fatherCnic', 'caste', 'profession', 'monthlyIncome',
                  'admissionNo', 'academicSession', 'admissionTestMarks', 'interviewMarks', 'admissionRemarks', 'address',
                  'admissionDate', 'schoolAdmissionNo', 'emergencyCampaign',
                  'guardianName', 'guardianCnic', 'guardianCell', 'guardianRelation', 'guardianProfession',
                  'guardianAddress', 'distanceFromSchool', 'canWrite', 'canRead', 'canCount', 'serialNo', 'studentStatus', 'updatedAt']
    },
    teachers: {
        tab: 'Teachers',
        headers: ['id', 'name', 'email', 'loginId', 'passwordHash', 'apiKey', 'subject', 'assignedClass', 'assignedSection',
                  'contact', 'status', 'createdAt',
                  'cnic', 'dob', 'gender', 'qualification', 'joiningDate', 'address', 'emergencyContact']
    },
    activities: {
        tab: 'Activities',
        headers: ['id', 'type', 'title', 'description', 'date', 'photo', 'createdAt']
    },
    teacherAttendance: {
        tab: 'TeacherAttendance',
        headers: ['id', 'teacherId', 'teacherName', 'date', 'status', 'markedBy', 'createdAt']
    },
    studentRequests: {
        tab: 'StudentRequests',
        headers: ['id','studentId','studentName','enrlNo','class','section','requestType','subject','message','leaveFrom','leaveTo','status','teacherReply','adminReply','assignedTeacherId','createdAt','updatedAt','reviewedBy']
    },
    teacherLeaves: {tab:'TeacherLeaves',headers:['id','teacherId','teacherName','leaveFrom','leaveTo','reason','status','adminReply','reviewedBy','createdAt','updatedAt']},
    expenses: {tab:'Expenses',headers:['id','date','category','description','amount','paymentMethod','reference','vendor','createdAt','updatedAt']},
    income: {tab:'OtherIncome',headers:['id','date','category','description','amount','paymentMethod','reference','createdAt','updatedAt']},
    teacherPings: {
        tab: 'TeacherPings',
        headers: ['id', 'teacherId', 'message', 'acknowledged', 'createdAt']
    },
    attendance: {
        tab: 'Attendance',
        headers: ['id', 'studentId', 'studentName', 'class', 'date', 'status', 'markedBy', 'createdAt']
    },
    homework: {
        tab: 'Homework',
        headers: ['id', 'class', 'subject', 'title', 'description', 'dueDate', 'assignedBy', 'createdAt']
    },
    fees: {
        tab: 'Fees',
        headers: ['id', 'studentId', 'studentName', 'class', 'amount', 'date', 'status', 'method', 'createdAt']
    },
    datesheet: {
        tab: 'DateSheet',
        headers: ['id', 'examTitle', 'class', 'subject', 'date', 'time', 'invigilator', 'createdAt', 'day', 'subjectsJson', 'isPublished', 'published', 'status', 'schoolId']
    },
    tests: {
        tab: 'Tests',
        headers: ['id', 'class', 'subject', 'type', 'date', 'totalMarks', 'topics', 'createdAt', 'isPublished', 'published', 'status', 'updatedAt', 'link', 'title', 'schoolId']
    },
    notifications: {
        tab: 'Notifications',
        headers: ['id', 'title', 'audience', 'channel', 'message', 'status', 'createdAt']
    },
    resources: {
        tab: 'Resources',
        headers: ['id', 'category', 'title', 'description', 'link', 'createdAt']
    }
};

// ============================================================================
// ONE-TIME SETUP
// ============================================================================

function setup() {
    const props = PropertiesService.getScriptProperties();
    if (props.getProperty('MASTER_SHEET_ID')) {
        Logger.log('Already set up. Master Registry ID: ' + props.getProperty('MASTER_SHEET_ID'));
        return;
    }
    const ss = SpreadsheetApp.create('LamaStudio Master Registry');
    const sheet = ss.getActiveSheet();
    sheet.setName(REGISTRY_SHEET_NAME);
    sheet.appendRow(REGISTRY_HEADERS);
    props.setProperty('MASTER_SHEET_ID', ss.getId());
    Logger.log('Setup complete. Master Registry: ' + ss.getUrl());
}

/** Run once (after editing SUPER_ADMIN_PASSWORD_PLAINTEXT above) to set your super admin password. */
function setupSuperAdmin() {
    const props = PropertiesService.getScriptProperties();
    const hash = hashPassword(SUPER_ADMIN_PASSWORD_PLAINTEXT, 'SUPERADMIN');
    props.setProperty('SUPERADMIN_PASSWORD_HASH', hash);
    Logger.log('Super admin password set. You can now log in from super-admin-login.html.');
}

function getRegistrySheet() {
    const props = PropertiesService.getScriptProperties();
    let id = props.getProperty('MASTER_SHEET_ID');
    if (!id) {
        setup();
        id = props.getProperty('MASTER_SHEET_ID');
    }
    const sheet = SpreadsheetApp.openById(id).getSheetByName(REGISTRY_SHEET_NAME);
    ensureSheetHeaders(sheet, REGISTRY_HEADERS);
    return sheet;
}

/** Self-heal: adds any header a sheet is missing, appended at the end. Never reorders
 *  or removes existing columns, so already-registered schools never lose data —
 *  their old rows just show blank cells under the newly added header names. */
function ensureSheetHeaders(sheet, requiredHeaders) {
    const lastCol = sheet.getLastColumn();
    const existing = lastCol > 0 ? sheet.getRange(1, 1, 1, lastCol).getValues()[0] : [];
    const missing = requiredHeaders.filter(h => existing.indexOf(h) === -1);
    if (missing.length > 0) {
        sheet.getRange(1, existing.length + 1, 1, missing.length).setValues([missing]);
    }
}

// ============================================================================
// SMALL HELPERS
// ============================================================================

function genId(prefix) {
    return prefix + '_' + Utilities.getUuid().split('-')[0] + Date.now().toString(36);
}

function slugify(str) {
    return String(str || '').trim().toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/(^-|-$)/g, '') || 'school';
}

function deriveShortCode(name) {
    return String(name || 'SCH').split(/\s+/).map(w => w[0]).join('').toUpperCase().slice(0, 3) || 'SCH';
}

/**
 * Passwords are never stored in plain text for school admins or teachers.
 * This is a lightweight hash (SHA-256, salted) suitable for a small free
 * tool — it is NOT the same bar as a bank or hospital system. Student
 * portal passwords are stored as plain text by design (low-sensitivity
 * data — homework/attendance, not financial or health records — and it
 * keeps the "add a student" flow simple for school staff). If this suite
 * ever handles more sensitive data, swap in a real auth provider.
 */
function hashPassword(password, salt) {
    const raw = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, password + '::' + salt);
    return raw.map(b => (b < 0 ? b + 256 : b).toString(16).padStart(2, '0')).join('');
}

function randomPassword() {
    const chars = 'ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnpqrstuvwxyz23456789';
    let out = '';
    for (let i = 0; i < 10; i++) out += chars.charAt(Math.floor(Math.random() * chars.length));
    return out;
}

/** Scans the registry sheet and returns the first row matching `matcherFn`. */
function findSchoolRow(sheet, matcherFn) {
    const data = sheet.getDataRange().getValues();
    const headers = data[0];
    for (let i = 1; i < data.length; i++) {
        const rowObj = {};
        headers.forEach((h, idx) => { rowObj[h] = data[i][idx]; });
        if (matcherFn(rowObj)) {
            return { rowIndex: i + 1, headers, obj: rowObj };
        }
    }
    return null;
}

// ============================================================================
// SCHOOL REGISTRATION, LOGIN & PROFILE  (role: 'school' — unchanged behavior)
// ============================================================================

/** Creates a brand-new private Google Sheet for a school, with all tabs pre-built. */
function createSchoolSheet(schoolName) {
    const ss = SpreadsheetApp.create(schoolName + ' - LamaStudio Data');
    const defaultSheet = ss.getSheets()[0];
    Object.keys(COLLECTIONS).forEach(key => {
        const cfg = COLLECTIONS[key];
        const sh = ss.insertSheet(cfg.tab);
        sh.appendRow(cfg.headers);
        sh.setFrozenRows(1);
    });
    ss.deleteSheet(defaultSheet);
    return ss.getId();
}

function registerSchool(p) {
    if (!p.name || !p.name.trim()) throw new Error('School name is required.');
    if (!p.adminEmail || !p.adminEmail.trim()) throw new Error('An admin email is required.');
    if (!p.password || p.password.length < 6) throw new Error('Password must be at least 6 characters.');

    const registry = getRegistrySheet();
    const existing = findSchoolRow(registry, r => String(r.adminEmail).toLowerCase() === String(p.adminEmail).toLowerCase());
    if (existing) throw new Error('An account with this email already exists. Please log in instead.');

    const schoolId = genId('sch');
    const sheetId = createSchoolSheet(p.name);
    const apiKey = Utilities.getUuid();
    const shortCode = deriveShortCode(p.name);
    const passwordHash = hashPassword(p.password, schoolId);

    registry.appendRow([
        schoolId, p.name.trim(), p.address || '', p.phone || '', p.principal || '', p.type || 'Private',
        p.level || 'High', p.adminEmail.trim(), passwordHash, apiKey, sheetId,
        p.logo || '', p.coverPhoto || '', shortCode, 'Free', 'Active', new Date().toISOString()
    ]);

    return {
        schoolId, apiKey, name: p.name.trim(), address: p.address || '', phone: p.phone || '',
        principal: p.principal || '', type: p.type || 'Private', level: p.level || 'High',
        logo: p.logo || '', coverPhoto: '', shortCode
    };
}

function loginSchool(email, password) {
    if (!email || !password) throw new Error('Email and password are required.');
    const registry = getRegistrySheet();
    const match = findSchoolRow(registry, r => String(r.adminEmail).toLowerCase() === String(email).toLowerCase());
    if (!match) throw new Error('No account found with this email.');
    if (hashPassword(password, match.obj.schoolId) !== match.obj.passwordHash) throw new Error('Incorrect password.');
    if (match.obj.status !== 'Active') throw new Error('This account is blocked. Please contact LamaStudio support.');

    return {
        schoolId: match.obj.schoolId, apiKey: match.obj.apiKey, name: match.obj.name,
        address: match.obj.address, phone: match.obj.phone, principal: match.obj.principal,
        type: match.obj.type, level: match.obj.level, logo: match.obj.logo,
        coverPhoto: match.obj.coverPhoto || '', shortCode: match.obj.shortCode
    };
}

/** Every school-admin-authenticated call goes through here. */
function authorizeSchool(schoolId, apiKey) {
    if (!schoolId || !apiKey) throw new Error('Missing session — please log in again.');
    const registry = getRegistrySheet();
    const match = findSchoolRow(registry, r => r.schoolId === schoolId);
    if (!match || match.obj.apiKey !== apiKey) throw new Error('Invalid session. Please log in again.');
    if (match.obj.status !== 'Active') throw new Error('This account is blocked.');
    return match;
}

function getSchoolConfig(schoolId, apiKey) {
    const o = authorizeSchool(schoolId, apiKey).obj;
    return {
        schoolId: o.schoolId, name: o.name, address: o.address, phone: o.phone, principal: o.principal,
        type: o.type, level: o.level, logo: o.logo, coverPhoto: o.coverPhoto || '', shortCode: o.shortCode
    };
}

function updateSchoolConfig(schoolId, apiKey, patch) {
    const match = authorizeSchool(schoolId, apiKey);
    const registry = getRegistrySheet();
    const updated = Object.assign({}, match.obj, patch, { schoolId: match.obj.schoolId, apiKey: match.obj.apiKey });
    const newRow = match.headers.map(h => (updated[h] !== undefined ? updated[h] : ''));
    registry.getRange(match.rowIndex, 1, 1, match.headers.length).setValues([newRow]);
    return getSchoolConfig(schoolId, apiKey);
}

// ============================================================================
// SUPER ADMIN  (role: 'admin')
// ============================================================================

function adminLogin(password) {
    const props = PropertiesService.getScriptProperties();
    const storedHash = props.getProperty('SUPERADMIN_PASSWORD_HASH');
    if (!storedHash) throw new Error('Super admin password not set up yet. Run setupSuperAdmin() in the Apps Script editor first.');
    if (hashPassword(password, 'SUPERADMIN') !== storedHash) throw new Error('Incorrect password.');
    return { adminApiKey: storedHash };
}

function authorizeAdmin(adminApiKey) {
    const props = PropertiesService.getScriptProperties();
    const storedHash = props.getProperty('SUPERADMIN_PASSWORD_HASH');
    if (!storedHash || !adminApiKey || adminApiKey !== storedHash) throw new Error('Invalid admin session. Please log in again.');
}

/** Counts non-empty data rows in a tab, given an already-open Spreadsheet. */
function countRows(ss, tabName) {
    try {
        const sh = ss.getSheetByName(tabName);
        if (!sh) return 0;
        const data = sh.getDataRange().getValues();
        let count = 0;
        for (let i = 1; i < data.length; i++) { if (data[i][0]) count++; }
        return count;
    } catch (e) {
        return 0;
    }
}

/** Aggregate view across every registered school. Slower on many schools — fine at small/medium scale. */
function adminListSchools(adminApiKey) {
    authorizeAdmin(adminApiKey);
    const registry = getRegistrySheet();
    const data = registry.getDataRange().getValues();
    const headers = data[0];
    const out = [];
    for (let i = 1; i < data.length; i++) {
        if (!data[i][0]) continue;
        const row = {};
        headers.forEach((h, idx) => { row[h] = data[i][idx]; });
        let studentCount = 0, teacherCount = 0;
        try {
            const ss = SpreadsheetApp.openById(row.sheetId);
            studentCount = countRows(ss, COLLECTIONS.students.tab);
            teacherCount = countRows(ss, COLLECTIONS.teachers.tab);
        } catch (e) { /* sheet may have been deleted manually — leave counts at 0 */ }
        out.push({
            schoolId: row.schoolId, name: row.name, adminEmail: row.adminEmail, phone: row.phone,
            type: row.type, level: row.level, status: row.status, plan: row.plan,
            createdAt: row.createdAt, studentCount, teacherCount
        });
    }
    return out;
}

function adminSetStatus(adminApiKey, schoolId, status) {
    authorizeAdmin(adminApiKey);
    if (status !== 'Active' && status !== 'Blocked') throw new Error('Status must be Active or Blocked.');
    const registry = getRegistrySheet();
    const match = findSchoolRow(registry, r => r.schoolId === schoolId);
    if (!match) throw new Error('School not found.');
    const statusCol = match.headers.indexOf('status') + 1;
    registry.getRange(match.rowIndex, statusCol).setValue(status);
    return { schoolId, status };
}

/** Generates a brand-new password for a school's login and emails it to their registered admin email. */
function adminResetPassword(adminApiKey, schoolId) {
    authorizeAdmin(adminApiKey);
    const registry = getRegistrySheet();
    const match = findSchoolRow(registry, r => r.schoolId === schoolId);
    if (!match) throw new Error('School not found.');
    const newPassword = randomPassword();
    const newHash = hashPassword(newPassword, schoolId);
    const hashCol = match.headers.indexOf('passwordHash') + 1;
    registry.getRange(match.rowIndex, hashCol).setValue(newHash);
    try {
        MailApp.sendEmail(match.obj.adminEmail, 'Your LamaStudio password has been reset',
            'Hello ' + match.obj.name + ',\n\nYour login password has been reset by LamaStudio support.\n\n' +
            'New password: ' + newPassword + '\n\nPlease log in and keep this safe.');
    } catch (e) { /* email sending can fail silently — the password is still returned below */ }
    return { schoolId, newPassword, emailedTo: match.obj.adminEmail };
}

// ============================================================================
// TEACHERS  (role: 'teacher') — created by School Admin, not self-registered
// ============================================================================

/** School Admin action: create a teacher account inside their own school's sheet. */
function teacherLoginIdentifier(v) { return String(v || '').trim().toLowerCase(); }
function validateTeacherLoginId(v) {
    const id = String(v || '').trim();
    if (!/^[a-zA-Z0-9][a-zA-Z0-9._@-]{1,79}$/.test(id)) throw new Error('Teacher ID must be 2-80 characters: letters, numbers, dots, @, _ or hyphens.');
    return id;
}
function teacherLoginIdInUse(sheet, headers, loginId, excludeId) {
    const rows = sheet.getDataRange().getValues();
    return rows.slice(1).some(values => {
        const r = rowToRecord(headers, values);
        return r.id !== excludeId && [r.loginId, r.email].some(v => v && teacherLoginIdentifier(v) === teacherLoginIdentifier(loginId));
    });
}
function addTeacher(schoolId, apiKey, p) {
    if (!p.name || !p.password || String(p.password).length < 8) throw new Error('Teacher name and password (at least 8 characters) are required.');
    const loginId = validateTeacherLoginId(p.loginId || p.email);
    const email = String(p.email || '').trim().toLowerCase();
    if (email && !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email)) throw new Error('Enter a valid email or leave it empty.');
    const match = authorizeSchool(schoolId, apiKey);
    const ss = SpreadsheetApp.openById(match.obj.sheetId);
    const { sheet, headers } = getCollectionSheet({role:'school',ss}, 'teachers');
    if (teacherLoginIdInUse(sheet, headers, loginId) || (email && teacherLoginIdInUse(sheet, headers, email))) throw new Error('Teacher ID or email is already in use at this school.');
    const id = genId('tch');
    const record = { id, name:String(p.name).trim(), email, loginId,
        passwordHash:hashPassword(String(p.password),id), apiKey:Utilities.getUuid(),
        subject:p.subject||'', assignedClass:p.assignedClass||'', assignedSection:p.assignedSection||'',
        contact:p.contact||'', status:'Active', createdAt:new Date().toISOString(),
        cnic:p.cnic||'', dob:p.dob||'', gender:p.gender||'', qualification:p.qualification||'',
        joiningDate:p.joiningDate||'', address:p.address||'', emergencyContact:p.emergencyContact||'' };
    sheet.appendRow(recordToRow(headers,record));
    const safe=Object.assign({},record); delete safe.passwordHash; delete safe.apiKey; return safe;
}
function teacherLogin(schoolId, identifier, password) {
    if (!schoolId || !identifier || !password) throw new Error('School ID, Teacher ID/email and password are required.');
    const registry=getRegistrySheet();
    const schoolMatch=findSchoolRow(registry,r=>r.schoolId===schoolId);
    if(!schoolMatch || schoolMatch.obj.status!=='Active') throw new Error('School not found or inactive.');
    const ss=SpreadsheetApp.openById(schoolMatch.obj.sheetId);
    const {sheet,headers}=getCollectionSheet({role:'school',ss},'teachers');
    const data=sheet.getDataRange().getValues();
    for(let i=1;i<data.length;i++) {
        const row=rowToRecord(headers,data[i]);
        if([row.loginId,row.email].some(v=>v && teacherLoginIdentifier(v)===teacherLoginIdentifier(identifier))) {
            if(row.status!=='Active') throw new Error('Teacher account disabled.');
            if(hashPassword(String(password),row.id)!==row.passwordHash) throw new Error('Incorrect password.');
            return {schoolId,teacherId:row.id,apiKey:row.apiKey,name:row.name,loginId:row.loginId||row.email,
                subject:row.subject,assignedClass:row.assignedClass,assignedSection:row.assignedSection||'',
                schoolName:schoolMatch.obj.name,schoolLogo:schoolMatch.obj.logo};
        }
    }
    throw new Error('Teacher ID/email not found at this school.');
}
/** Every teacher-authenticated call goes through here. Returns the school context + the teacher's own row. */
function authorizeTeacher(schoolId, teacherId, apiKey) {
    if (!schoolId || !teacherId || !apiKey) throw new Error('Missing session — please log in again.');
    const registry = getRegistrySheet();
    const schoolMatch = findSchoolRow(registry, r => r.schoolId === schoolId);
    if (!schoolMatch || schoolMatch.obj.status !== 'Active') throw new Error('Invalid session or blocked account.');
    const ss = SpreadsheetApp.openById(schoolMatch.obj.sheetId);
    const sheet = ss.getSheetByName(COLLECTIONS.teachers.tab);
    const data = sheet.getDataRange().getValues();
    const headers = data[0];
    for (let i = 1; i < data.length; i++) {
        const row = rowToRecord(headers, data[i]);
        if (row.id === teacherId && row.apiKey === apiKey) {
            if (row.status !== 'Active') throw new Error('This teacher account has been disabled.');
            return { schoolMatch, teacher: row, ss };
        }
    }
    throw new Error('Invalid session. Please log in again.');
}

// ============================================================================
// STUDENTS  (role: 'student') — logs in with enrollment number + password
// ============================================================================

function studentLogin(schoolId, enrlNo, password) {
    if (!schoolId || !enrlNo || !password) throw new Error('School ID, enrollment number, and password are required.');
    const registry = getRegistrySheet();
    const schoolMatch = findSchoolRow(registry, r => r.schoolId === schoolId);
    if (!schoolMatch) throw new Error('School not found. Check your School ID.');
    if (schoolMatch.obj.status !== 'Active') throw new Error('This school\'s account is currently blocked.');

    const ss = SpreadsheetApp.openById(schoolMatch.obj.sheetId);
    const sheet = ss.getSheetByName(COLLECTIONS.students.tab);
    const data = sheet.getDataRange().getValues();
    const headers = data[0];
    for (let i = 1; i < data.length; i++) {
        const row = rowToRecord(headers, data[i]);
        if (String(row.enrlNo) === String(enrlNo)) {
            const suppliedHash = hashPassword(String(password), 'STUDENT:' + row.id);
            const storedPassword = String(row.password || '');
            if (storedPassword !== suppliedHash && storedPassword !== String(password)) throw new Error('Incorrect password.');
            if (storedPassword === String(password)) {
                const passCol = headers.indexOf('password');
                if (passCol >= 0) sheet.getRange(i + 1, passCol + 1).setValue(suppliedHash);
            }
            return {
                schoolId, studentId: row.id, apiKey: row.apiKey, name: row.name, class: row.class, section: row.section || '',
                schoolName: schoolMatch.obj.name, schoolLogo: schoolMatch.obj.logo
            };
        }
    }
    throw new Error('No student found with this enrollment number at this school.');
}

/** Every student-authenticated call goes through here. */
function authorizeStudent(schoolId, studentId, apiKey) {
    if (!schoolId || !studentId || !apiKey) throw new Error('Missing session — please log in again.');
    const registry = getRegistrySheet();
    const schoolMatch = findSchoolRow(registry, r => r.schoolId === schoolId);
    if (!schoolMatch || schoolMatch.obj.status !== 'Active') throw new Error('Invalid session or blocked account.');
    const ss = SpreadsheetApp.openById(schoolMatch.obj.sheetId);
    const sheet = ss.getSheetByName(COLLECTIONS.students.tab);
    const data = sheet.getDataRange().getValues();
    const headers = data[0];
    for (let i = 1; i < data.length; i++) {
        const row = rowToRecord(headers, data[i]);
        if (row.id === studentId && row.apiKey === apiKey) {
            return { schoolMatch, student: row, ss };
        }
    }
    throw new Error('Invalid session. Please log in again.');
}

// ============================================================================
// GENERIC COLLECTION CRUD — now role-aware
// ============================================================================
// role 'school'  -> full access to every collection in their own sheet (unchanged).
// role 'teacher' -> attendance/homework: add+update scoped to their own assignedClass;
//                   students: list/get scoped to their class, update (marks) only;
//                   datesheet/tests/notifications: list/get only (read-only).
// role 'student' -> get on their own student record only; list on
//                   homework/datesheet/tests/notifications scoped to their own class.
// role 'admin'   -> no collection access (registry-level actions only, above).

function rowToRecord(headers, rowValues) {
    const obj = {};
    headers.forEach((h, i) => {
        const val = rowValues[i];
        if (h.endsWith('Json')) {
            const key = h.slice(0, -4);
            try { obj[key] = val ? JSON.parse(val) : null; } catch (e) { obj[key] = null; }
        } else {
            obj[h] = val;
        }
    });
    return obj;
}

function recordToRow(headers, record) {
    return headers.map(h => {
        if (h.endsWith('Json')) {
            const key = h.slice(0, -4);
            return record[key] !== undefined ? JSON.stringify(record[key]) : '';
        }
        return record[h] !== undefined ? record[h] : '';
    });
}

/** Resolves { role, schoolId, apiKey, teacherId?, studentId? } into a working Spreadsheet + role context. */
function resolveContext(p) {
    const role = p.role || 'school';
    if (role === 'school') {
        const match = authorizeSchool(p.schoolId, p.apiKey);
        return { role, ss: SpreadsheetApp.openById(match.obj.sheetId) };
    }
    if (role === 'teacher') {
        const ctx = authorizeTeacher(p.schoolId, p.teacherId, p.apiKey);
        return { role, ss: ctx.ss, teacher: ctx.teacher };
    }
    if (role === 'student') {
        const ctx = authorizeStudent(p.schoolId, p.studentId, p.apiKey);
        return { role, ss: ctx.ss, student: ctx.student };
    }
    throw new Error('Unknown role: ' + role);
}

const TEACHER_WRITE_COLLECTIONS = ['attendance', 'homework'];
const TEACHER_READ_COLLECTIONS = ['students', 'datesheet', 'tests', 'activities', 'teacherPings', 'resources'];
const STUDENT_READ_COLLECTIONS = ['homework', 'datesheet', 'tests', 'notifications', 'activities', 'resources', 'timetable'];
function normalizeSchoolClass(value) {
    return String(value == null ? '' : value).trim().toLowerCase()
      .replace(/^(grade|class|standard|std)[\s._-]*/i, '')
      .replace(/^0+(?=\d)/, '').replace(/\s+/g, '');
}
function sameClass(a,b){ return normalizeSchoolClass(a) === normalizeSchoolClass(b); }
function recordAppliesToClass(record, studentClass) {
    var classes = String(record.class || '').split(/[,;|]/).map(function(v){return v.trim();}).filter(Boolean);
    var map = record.subjects && typeof record.subjects === 'object' ? record.subjects : {};
    if (!Object.keys(map).length && record.subjectsJson) {
        try { map = JSON.parse(record.subjectsJson); } catch (e) { map = {}; }
    }
    var keys = Object.keys(map);
    if (keys.length) return keys.some(function(k){return sameClass(k,studentClass) && !!map[k];});
    return classes.length ? classes.some(function(k){return sameClass(k,studentClass);}) : true;
}
function sameSection(record,section){ return !section || !record.section || sameClass(record.section,section); }
function teacherOwns(ctx,r){ return sameClass(r.class,ctx.teacher.assignedClass) && sameSection(r,ctx.teacher.assignedSection); }

function checkPermission(ctx, action, collection) {
    if (ctx.role === 'school') return; // full access, unchanged
    if (ctx.role === 'teacher' && collection === 'teacherLeaves' && ['list','get','add'].indexOf(action) !== -1) return;
    if (ctx.role === 'teacher') {
        if (collection === 'studentRequests' && ['list','get','update'].indexOf(action) !== -1) return;
        if (TEACHER_WRITE_COLLECTIONS.indexOf(collection) !== -1) return; // class-scoped writes
        if (collection === 'students' && (action === 'list' || action === 'get' || action === 'update')) return;
        if (collection === 'notifications' && (action === 'list' || action === 'get' || action === 'add')) return;
        if (TEACHER_READ_COLLECTIONS.indexOf(collection) !== -1 && (action === 'list' || action === 'get')) return;
        throw new Error('Teachers do not have permission for this action.');
    }
    if (ctx.role === 'student') {
        if (collection === 'studentRequests' && ['list','get','add'].indexOf(action) !== -1) return;
        if (collection === 'students' && action === 'get') return;
        if (STUDENT_READ_COLLECTIONS.indexOf(collection) !== -1 && action === 'list') return;
        throw new Error('Students only have read access to their own record and class information.');
    }
    throw new Error('This role cannot access collections directly.');
}

function getCollectionSheet(ctx, collection) {
    const cfg = COLLECTIONS[collection];
    if (!cfg) throw new Error('Unknown collection: ' + collection);
    let sheet = ctx.ss.getSheetByName(cfg.tab);
    if (!sheet) {
        // Self-heal: schools registered before this collection existed won't have this
        // tab yet. Create it on first access instead of throwing.
        sheet = ctx.ss.insertSheet(cfg.tab);
        sheet.appendRow(cfg.headers);
        sheet.setFrozenRows(1);
    } else {
        // Self-heal: schools registered before newer fields existed get those
        // columns added automatically, so nothing new is ever silently dropped.
        ensureSheetHeaders(sheet, cfg.headers);
    }
    // Existing sheets may have gained new columns over time; read their actual order.
    const actualHeaders = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    return { sheet, headers: actualHeaders };
}

function safeRecord(collection, record) {
    const copy = Object.assign({}, record);
    if (collection === 'students') { delete copy.password; delete copy.apiKey; }
    if (collection === 'teachers') { delete copy.passwordHash; delete copy.apiKey; }
    return copy;
}
function publishedRecord(record) {
    return String(record.isPublished).toLowerCase() === 'true' ||
           String(record.published).toLowerCase() === 'true' ||
           String(record.status).toLowerCase() === 'published';
}
function listRecords(ctx, collection) {
    checkPermission(ctx, 'list', collection);
    const { sheet, headers } = getCollectionSheet(ctx, collection);
    const data = sheet.getDataRange().getValues();
    let records = [];
    for (let i = 1; i < data.length; i++) {
        if (!data[i][headers.indexOf('id')]) continue;
        records.push(rowToRecord(headers, data[i]));
    }
    if (collection === 'teacherLeaves' && ctx.role === 'teacher') records = records.filter(r => String(r.teacherId) === String(ctx.teacher.id));
    if (collection === 'studentRequests') {
        if (ctx.role === 'student') records = records.filter(r => String(r.studentId) === String(ctx.student.id));
        if (ctx.role === 'teacher') records = records.filter(r => teacherOwns(ctx,r));
    }
    if (ctx.role === 'teacher' && (collection === 'attendance' || collection === 'homework' || collection === 'students')) {
        records = records.filter(r => teacherOwns(ctx,r));
    }
    if (ctx.role === 'teacher' && collection === 'teacherPings') {
        records = records.filter(r => r.teacherId === ctx.teacher.id);
    }
    if (ctx.role === 'student' && (collection === 'homework' || collection === 'datesheet' || collection === 'tests' || collection === 'timetable')) {
        records = records.filter(r => collection === 'datesheet' ? recordAppliesToClass(r, ctx.student.class) : (recordAppliesToClass(r, ctx.student.class) && sameSection(r,ctx.student.section)));
    }
    if (ctx.role === 'student' && ['datesheet','tests','notifications','activities','resources'].indexOf(collection) !== -1) {
        records = records.filter(publishedRecord);
    }
    return records.map(r => safeRecord(collection, r));
}

function getRecord(ctx, collection, id) {
    checkPermission(ctx, 'get', collection);
    if (ctx.role === 'student' && collection === 'students' && id !== ctx.student.id) {
        throw new Error('Students can only view their own record.');
    }
    const { sheet, headers } = getCollectionSheet(ctx, collection);
    const data = sheet.getDataRange().getValues();
    for (let i = 1; i < data.length; i++) {
        if (data[i][0] === id) {
            const r=rowToRecord(headers,data[i]);
            if (collection === 'teacherLeaves' && ctx.role === 'teacher' && String(r.teacherId) !== String(ctx.teacher.id)) throw new Error('Leave request not found.');
            if (collection === 'studentRequests') {
                if (ctx.role === 'student' && String(r.studentId) !== String(ctx.student.id)) throw new Error('Request not found.');
                if (ctx.role === 'teacher' && !teacherOwns(ctx,r)) throw new Error('Request outside assigned class.');
            }
            if(ctx.role==='teacher' && collection==='students' && !teacherOwns(ctx,r)) throw new Error('Record outside assigned class.');
            return safeRecord(collection, r);
        }
    }
    return null;
}

function addRecord(ctx, collection, record) {
    checkPermission(ctx, 'add', collection);
    if (collection === 'teacherLeaves' && ctx.role === 'teacher') {
        const from=String(record.leaveFrom||''), to=String(record.leaveTo||'');
        const reason=String(record.reason||'').trim();
        if(!/^\d{4}-\d{2}-\d{2}$/.test(from)||!/^\d{4}-\d{2}-\d{2}$/.test(to)||from>to) throw new Error('Invalid leave dates.');
        if(!reason||reason.length>2000) throw new Error('Reason required (max 2000 characters).');
        record={teacherId:ctx.teacher.id,teacherName:ctx.teacher.name||'',leaveFrom:from,leaveTo:to,reason:reason,status:'Pending',adminReply:'',reviewedBy:'',updatedAt:new Date().toISOString()};
    }
    if (['expenses','income'].indexOf(collection)!==-1 && ctx.role==='school') {
        const amount=Number(record.amount);
        if(!Number.isFinite(amount)||amount<=0||amount>1000000000) throw new Error('Enter a valid positive amount.');
        if(!/^\d{4}-\d{2}-\d{2}$/.test(String(record.date||''))) throw new Error('Invalid transaction date.');
        record={date:String(record.date),category:String(record.category||'Other').slice(0,80),description:String(record.description||'').slice(0,500),amount:amount,paymentMethod:String(record.paymentMethod||'Cash').slice(0,50),reference:String(record.reference||'').slice(0,100),vendor:String(record.vendor||'').slice(0,100)};
    }
    if (collection === 'studentRequests' && ctx.role === 'student') {
        const type = String(record.requestType || 'help');
        if (['help','sick_leave'].indexOf(type) === -1) throw new Error('Invalid request type.');
        const message = String(record.message || '').trim();
        if (!message || message.length > 3000) throw new Error('Describe your problem (maximum 3000 characters).');
        const from = String(record.leaveFrom || ''), to = String(record.leaveTo || '');
        if (type === 'sick_leave' && (!/^\d{4}-\d{2}-\d{2}$/.test(from) || !/^\d{4}-\d{2}-\d{2}$/.test(to) || from > to)) throw new Error('Enter a valid sick leave date range.');
        record = {studentId:ctx.student.id,studentName:ctx.student.name || '',enrlNo:ctx.student.enrlNo || '',class:ctx.student.class || '',section:ctx.student.section || '',requestType:type,subject:String(record.subject || '').slice(0,120),message:message,leaveFrom:type==='sick_leave'?from:'',leaveTo:type==='sick_leave'?to:'',status:'Pending',teacherReply:'',adminReply:'',assignedTeacherId:'',updatedAt:new Date().toISOString(),reviewedBy:''};
    }
    if (ctx.role === 'teacher' && TEACHER_WRITE_COLLECTIONS.indexOf(collection) !== -1) {
        record = Object.assign({}, record, { class: ctx.teacher.assignedClass, section: ctx.teacher.assignedSection || '', markedBy: ctx.teacher.name, assignedBy: ctx.teacher.name });
    }
    const { sheet, headers } = getCollectionSheet(ctx, collection);
    if (collection === 'students' && String(record.enrlNo || '').trim()) {
        const rows = sheet.getDataRange().getValues();
        const idx = headers.indexOf('enrlNo');
        if (idx >= 0 && rows.slice(1).some(r => String(r[idx] || '').trim() === String(record.enrlNo).trim()))
            throw new Error('Enrollment number already exists in this school.');
    }
    const id = genId(collection.slice(0, 3));
    const full = Object.assign({}, record, { id, createdAt: new Date().toISOString() });
    if (collection === 'students' && !full.apiKey) full.apiKey = Utilities.getUuid();
    if (collection === 'students') full.password = hashPassword(String(full.password || full.enrlNo || id), 'STUDENT:' + id);
    sheet.appendRow(recordToRow(headers, full));
    return safeRecord(collection, full);
}

function updateRecord(ctx, collection, id, patch) {
    checkPermission(ctx, 'update', collection);
    if (collection === 'teacherLeaves') {
        if(ctx.role!=='school') throw new Error('Only school admin can decide teacher leave.');
        if(!['Approved','Rejected','Pending'].includes(String(patch.status))) throw new Error('Invalid leave status.');
        patch={status:String(patch.status),adminReply:String(patch.adminReply||'').slice(0,1000),reviewedBy:'School Admin',updatedAt:new Date().toISOString()};
    }
    if (['expenses','income'].includes(collection)) {
        if(ctx.role!=='school') throw new Error('School admin only.');
        const allowed=['date','category','description','amount','paymentMethod','reference','vendor'];
        patch=Object.fromEntries(Object.entries(patch).filter(([k])=>allowed.includes(k)));
        if(patch.amount!==undefined && (!Number.isFinite(Number(patch.amount))||Number(patch.amount)<=0)) throw new Error('Invalid amount.');
        if(patch.date!==undefined && !/^\d{4}-\d{2}-\d{2}$/.test(String(patch.date))) throw new Error('Invalid date.');
        patch.updatedAt=new Date().toISOString();
    }
    const { sheet, headers } = getCollectionSheet(ctx, collection);
    const data = sheet.getDataRange().getValues();
    const idCol = headers.indexOf('id');
    for (let i = 1; i < data.length; i++) {
        if (data[i][idCol] === id) {
            const existing = rowToRecord(headers, data[i]);
            if (ctx.role === 'school' && collection === 'teachers') {
                patch = Object.assign({}, patch);
                delete patch.apiKey; delete patch.passwordHash;
                if (Object.prototype.hasOwnProperty.call(patch,'password')) {
                    if (patch.password) {
                        if (String(patch.password).length < 8) throw new Error('New password must have at least 8 characters.');
                        patch._passwordHash = hashPassword(String(patch.password), existing.id);
                        patch.apiKey = Utilities.getUuid(); // revoke existing teacher sessions
                    }
                    delete patch.password;
                }
                if (Object.prototype.hasOwnProperty.call(patch,'loginId')) {
                    patch.loginId = validateTeacherLoginId(patch.loginId);
                    if (teacherLoginIdInUse(sheet,headers,patch.loginId,id)) throw new Error('Teacher ID already in use.');
                }
                if (Object.prototype.hasOwnProperty.call(patch,'email')) {
                    patch.email = String(patch.email||'').trim().toLowerCase();
                    if (patch.email && (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(patch.email) || teacherLoginIdInUse(sheet,headers,patch.email,id))) throw new Error('Invalid or duplicate email.');
                }
                delete patch.id; delete patch.passwordHash;
                if (patch._passwordHash) { patch.passwordHash=patch._passwordHash; delete patch._passwordHash; }

            }
            if (ctx.role === 'teacher' && collection === 'students' && !sameClass(existing.class, ctx.teacher.assignedClass)) {
                throw new Error('Teachers can only update students in their own assigned class.');
            }
            if(ctx.role==='teacher') {
                if(collection==='students') {
                    if(!teacherOwns(ctx,existing)) throw new Error('Student outside assigned class/section.');
                    const allowed=['subjects','teacherRemarks'];
                    if(Object.keys(patch).some(k=>allowed.indexOf(k)===-1)) throw new Error('Teachers may only update student marks and teacher remarks.');
                } else if(TEACHER_WRITE_COLLECTIONS.indexOf(collection)!==-1) {
                    if(!teacherOwns(ctx,existing)) throw new Error('Record outside assigned class/section.');
                    patch=Object.assign({},patch,{class:ctx.teacher.assignedClass,section:ctx.teacher.assignedSection||''});
                }
            }
            if (collection === 'studentRequests') {
                if (ctx.role === 'student') throw new Error('Students cannot change submitted requests.');
                if (ctx.role === 'teacher') {
                    if (!teacherOwns(ctx,existing)) throw new Error('Request outside assigned class.');
                    if (Object.keys(patch).some(k => ['teacherReply','status'].indexOf(k) === -1)) throw new Error('Teacher can only reply and update status.');
                    if (patch.status && ['Pending','In Review','Resolved','Approved','Rejected'].indexOf(String(patch.status)) === -1) throw new Error('Invalid status.');
                    patch = Object.assign({},patch,{reviewedBy:ctx.teacher.name || 'Class Teacher',updatedAt:new Date().toISOString()});
                }
                if (ctx.role === 'school') {
                    if (Object.keys(patch).some(k => ['adminReply','status'].indexOf(k) === -1)) throw new Error('School admin can only reply and update status.');
                    if (patch.status && ['Pending','In Review','Resolved','Approved','Rejected'].indexOf(String(patch.status)) === -1) throw new Error('Invalid status.');
                    patch = Object.assign({},patch,{reviewedBy:'School Admin',updatedAt:new Date().toISOString()});
                }
            }
            if (collection === 'students' && ctx.role === 'school' && Object.prototype.hasOwnProperty.call(patch,'password')) {
                if (!patch.password) delete patch.password;
                else if (String(patch.password) !== String(existing.password)) patch.password = hashPassword(String(patch.password), 'STUDENT:' + id);
            }
            const updated = Object.assign({}, existing, patch, { id });
            sheet.getRange(i + 1, 1, 1, headers.length).setValues([recordToRow(headers, updated)]);
            return safeRecord(collection, updated);
        }
    }
    throw new Error('Record not found: ' + id);
}

function removeRecord(ctx, collection, id) {
    checkPermission(ctx, 'remove', collection);
    const { sheet, headers } = getCollectionSheet(ctx, collection);
    const data = sheet.getDataRange().getValues();
    const idCol = headers.indexOf('id');
    for (let i = 1; i < data.length; i++) {
        if (data[i][idCol] === id) {
            if(ctx.role==='teacher' && !teacherOwns(ctx,rowToRecord(headers,data[i]))) throw new Error('Record outside assigned class/section.');
            sheet.deleteRow(i + 1);
            return true;
        }
    }
    return false;
}

/** Admin sends a one-tap reminder to a teacher who hasn't marked attendance yet. */
function pingTeacher(schoolId, apiKey, teacherId, message) {
    const match = authorizeSchool(schoolId, apiKey);
    const ss = SpreadsheetApp.openById(match.obj.sheetId);
    const ctx = { role: 'school', ss };
    return addRecord(ctx, 'teacherPings', {
        teacherId,
        message: message || 'Please mark today\'s student attendance.',
        acknowledged: false
    });
}

/** Teacher dismisses their own ping after seeing it. */
function acknowledgePing(schoolId, teacherId, apiKey, pingId) {
    const ctx = authorizeTeacher(schoolId, teacherId, apiKey);
    const teacherCtx = { role: 'teacher', ss: ctx.ss, teacher: ctx.teacher };
    const { sheet, headers } = getCollectionSheet(teacherCtx, 'teacherPings');
    const data = sheet.getDataRange().getValues();
    const idCol = headers.indexOf('id');
    const teacherIdCol = headers.indexOf('teacherId');
    for (let i = 1; i < data.length; i++) {
        if (data[i][idCol] === pingId) {
            if (data[i][teacherIdCol] !== ctx.teacher.id) throw new Error('This ping does not belong to you.');
            const updated = Object.assign({}, rowToRecord(headers, data[i]), { acknowledged: true });
            sheet.getRange(i + 1, 1, 1, headers.length).setValues([recordToRow(headers, updated)]);
            return updated;
        }
    }
    throw new Error('Ping not found.');
}

// ============================================================================
// SHARED MAIN DASHBOARD  (school / teacher / student — never fees or HR data)
// ============================================================================

/** The same safe, non-financial overview shown to School Admin, Teacher, and Student/Parent alike. */
function getMainDashboardData(params) {
    const ctx = resolveContext(params);
    const ss = ctx.ss;

    let schoolName = '', logo = '', coverPhoto = '', address = '', level = '', type = '';
    if (ctx.role === 'school') {
        const match = authorizeSchool(params.schoolId, params.apiKey);
        schoolName = match.obj.name; logo = match.obj.logo; coverPhoto = match.obj.coverPhoto || '';
        address = match.obj.address; level = match.obj.level; type = match.obj.type;
    } else {
        const registry = getRegistrySheet();
        const schoolMatch = findSchoolRow(registry, r => r.schoolId === params.schoolId);
        if (schoolMatch) {
            schoolName = schoolMatch.obj.name; logo = schoolMatch.obj.logo; coverPhoto = schoolMatch.obj.coverPhoto || '';
            address = schoolMatch.obj.address; level = schoolMatch.obj.level; type = schoolMatch.obj.type;
        }
    }

    const notifRows = getCollectionSheet(ctx, 'notifications').sheet.getDataRange().getValues();
    const notifHeaders = getCollectionSheet(ctx, 'notifications').headers;
    const notifications = [];
    for (let i = 1; i < notifRows.length; i++) {
        const r = rowToRecord(notifHeaders, notifRows[i]);
        if (r.id) notifications.push(r);
    }
    notifications.sort((a, b) => String(b.createdAt).localeCompare(String(a.createdAt)));

    const actRows = getCollectionSheet(ctx, 'activities').sheet.getDataRange().getValues();
    const actHeaders = getCollectionSheet(ctx, 'activities').headers;
    const activities = [];
    for (let i = 1; i < actRows.length; i++) {
        const r = rowToRecord(actHeaders, actRows[i]);
        if (r.id) activities.push(r);
    }
    activities.sort((a, b) => String(b.date || b.createdAt).localeCompare(String(a.date || a.createdAt)));

    const dsRows = getCollectionSheet(ctx, 'datesheet').sheet.getDataRange().getValues();
    const dsHeaders = getCollectionSheet(ctx, 'datesheet').headers;
    const todayStr = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd');
    const upcomingExams = [];
    for (let i = 1; i < dsRows.length; i++) {
        const r = rowToRecord(dsHeaders, dsRows[i]);
        if (r.id && (String(r.isPublished).toLowerCase() === 'true' || String(r.published).toLowerCase() === 'true' || String(r.status).toLowerCase() === 'published')) upcomingExams.push(r);
    }
    upcomingExams.sort((a, b) => {
        function key(v) { const s=String(v||''); const m=s.match(/^(\d{2})\/(\d{2})\/(\d{4})$/); return m ? m[3]+'-'+m[2]+'-'+m[1] : s; }
        return key(a.date).localeCompare(key(b.date));
    });

    const testRows = getCollectionSheet(ctx, 'tests').sheet.getDataRange().getValues();
    const testHeaders = getCollectionSheet(ctx, 'tests').headers;
    const upcomingTests = [];
    for (let i = 1; i < testRows.length; i++) {
        const r = rowToRecord(testHeaders, testRows[i]);
        if (r.id && (String(r.isPublished).toLowerCase() === 'true' || String(r.published).toLowerCase() === 'true' || String(r.status).toLowerCase() === 'published')) upcomingTests.push(r);
    }
    upcomingTests.sort((a, b) => String(a.date).localeCompare(String(b.date)));

    const resRows = getCollectionSheet(ctx, 'resources').sheet.getDataRange().getValues();
    const resHeaders = getCollectionSheet(ctx, 'resources').headers;
    const resources = [];
    for (let i = 1; i < resRows.length; i++) {
        const r = rowToRecord(resHeaders, resRows[i]);
        if (r.id) resources.push(r);
    }
    resources.sort((a, b) => String(a.category).localeCompare(String(b.category)));

    return {
        schoolId: params.schoolId, schoolName, logo, coverPhoto, address, level, type,
        recentNotifications: (ctx.role === 'student' ? notifications.filter(publishedRecord) : notifications).slice(0, 5),
        recentActivities: (ctx.role === 'student' ? activities.filter(publishedRecord) : activities).slice(0, 5),
        upcomingExams: ctx.role === 'student' ? upcomingExams.filter(r => recordAppliesToClass(r, ctx.student.class)) : upcomingExams,
        upcomingTests: ctx.role === 'student' ? upcomingTests.filter(r => recordAppliesToClass(r, ctx.student.class)) : upcomingTests,
        resources: ctx.role === 'student' ? resources.filter(publishedRecord) : resources
    };
}

// ============================================================================
// SCHOOL ADMIN DASHBOARD STATS
// ============================================================================

function getDashboardStats(schoolId, apiKey) {
    const match = authorizeSchool(schoolId, apiKey);
    const ss = SpreadsheetApp.openById(match.obj.sheetId);
    const today = new Date();
    const todayStr = Utilities.formatDate(today, Session.getScriptTimeZone(), 'yyyy-MM-dd');

    const studentsSheet = ss.getSheetByName(COLLECTIONS.students.tab);
    const studentRows = studentsSheet.getDataRange().getValues();
    const studentHeaders = COLLECTIONS.students.headers;
    let totalStudents = 0;
    let maleCount = 0, femaleCount = 0;
    for (let i = 1; i < studentRows.length; i++) {
        if (!studentRows[i][0]) continue;
        totalStudents++;
        const r = rowToRecord(studentHeaders, studentRows[i]);
        if (String(r.gender).toUpperCase() === 'M') maleCount++;
        else if (String(r.gender).toUpperCase() === 'F') femaleCount++;
    }
    const genderBreakdown = { male: maleCount, female: femaleCount, other: totalStudents - maleCount - femaleCount };

    const attSheet = ss.getSheetByName(COLLECTIONS.attendance.tab);
    const attRows = attSheet.getDataRange().getValues();
    const attHeaders = attRows[0];
    let presentToday = 0, absentToday = 0;
    for (let i = 1; i < attRows.length; i++) {
        const r = rowToRecord(attHeaders, attRows[i]);
        if (!r.id) continue;
        if (String(r.date) === todayStr) {
            if (r.status === 'Present') presentToday++;
            else if (r.status === 'Absent') absentToday++;
        }
    }
    const markedToday = presentToday + absentToday;
    const attendanceRatio = markedToday > 0 ? Math.round((presentToday / markedToday) * 100) : null;

    // Which classes have not had their attendance marked today, and by whom.
    const classesMarkedToday = {};
    for (let i = 1; i < attRows.length; i++) {
        const r = rowToRecord(attHeaders, attRows[i]);
        if (r.id && String(r.date) === todayStr) classesMarkedToday[String(r.class)] = true;
    }

    const teachersSheet = ss.getSheetByName(COLLECTIONS.teachers.tab);
    const teacherRows = teachersSheet.getDataRange().getValues();
    const teacherHeaders = COLLECTIONS.teachers.headers;
    const allTeachers = [];
    for (let i = 1; i < teacherRows.length; i++) {
        const r = rowToRecord(teacherHeaders, teacherRows[i]);
        if (r.id) allTeachers.push(r);
    }
    const totalTeachers = allTeachers.length;
    const teachersNotMarked = allTeachers
        .filter(t => t.assignedClass && !classesMarkedToday[String(t.assignedClass)])
        .map(t => ({ id: t.id, name: t.name, assignedClass: t.assignedClass }));

    const tAttSheet = ss.getSheetByName(COLLECTIONS.teacherAttendance.tab) ||
        ss.insertSheet(COLLECTIONS.teacherAttendance.tab);
    if (tAttSheet.getLastRow() === 0) { tAttSheet.appendRow(COLLECTIONS.teacherAttendance.headers); tAttSheet.setFrozenRows(1); }
    const tAttRows = tAttSheet.getDataRange().getValues();
    const tAttHeaders = COLLECTIONS.teacherAttendance.headers;
    let teachersPresentToday = 0, teachersAbsentToday = 0;
    for (let i = 1; i < tAttRows.length; i++) {
        const r = rowToRecord(tAttHeaders, tAttRows[i]);
        if (!r.id || String(r.date) !== todayStr) continue;
        if (r.status === 'Present') teachersPresentToday++;
        else if (r.status === 'Absent') teachersAbsentToday++;
    }

    const feesSheet = ss.getSheetByName(COLLECTIONS.fees.tab);
    const feeRows = feesSheet.getDataRange().getValues();
    const feeHeaders = feeRows[0];
    let todayCollection = 0, overallTotal = 0, monthTotal = 0, yearTotal = 0;
    const monthTotals = {};
    const currentMonthKey = Utilities.formatDate(today, Session.getScriptTimeZone(), 'yyyy-MM');
    const currentYearKey = Utilities.formatDate(today, Session.getScriptTimeZone(), 'yyyy');
    for (let i = 1; i < feeRows.length; i++) {
        const r = rowToRecord(feeHeaders, feeRows[i]);
        if (!r.id || r.status !== 'Paid') continue;
        const amt = Number(r.amount) || 0;
        overallTotal += amt;
        if (String(r.date) === todayStr) todayCollection += amt;
        const month = String(r.date || '').slice(0, 7);
        if (month) monthTotals[month] = (monthTotals[month] || 0) + amt;
        if (month === currentMonthKey) monthTotal += amt;
        if (String(r.date || '').slice(0, 4) === currentYearKey) yearTotal += amt;
    }
    const incomeSeries = [];
    for (let i = 11; i >= 0; i--) {
        const d = new Date(today.getFullYear(), today.getMonth() - i, 1);
        const key = Utilities.formatDate(d, Session.getScriptTimeZone(), 'yyyy-MM');
        incomeSeries.push({ month: key, total: monthTotals[key] || 0 });
    }

    const notifSheet = ss.getSheetByName(COLLECTIONS.notifications.tab);
    const notifRows = notifSheet.getDataRange().getValues();
    const notifHeaders = notifRows[0];
    const notifications = [];
    for (let i = 1; i < notifRows.length; i++) {
        const r = rowToRecord(notifHeaders, notifRows[i]);
        if (r.id) notifications.push(r);
    }
    notifications.sort((a, b) => String(b.createdAt).localeCompare(String(a.createdAt)));

    const actSheet = ss.getSheetByName(COLLECTIONS.activities.tab) || ss.insertSheet(COLLECTIONS.activities.tab);
    if (actSheet.getLastRow() === 0) { actSheet.appendRow(COLLECTIONS.activities.headers); actSheet.setFrozenRows(1); }
    const actRows = actSheet.getDataRange().getValues();
    const actHeaders = actRows[0];
    const activities = [];
    for (let i = 1; i < actRows.length; i++) {
        const r = rowToRecord(actHeaders, actRows[i]);
        if (r.id) activities.push(r);
    }
    activities.sort((a, b) => String(b.date || b.createdAt).localeCompare(String(a.date || a.createdAt)));

    // Class + section breakdown, each with its own student count.
    const sectionMap = {};
    for (let i = 1; i < studentRows.length; i++) {
        if (!studentRows[i][0]) continue;
        const r = rowToRecord(studentHeaders, studentRows[i]);
        const key = String(r.class) + '|' + String(r.section || '');
        if (!sectionMap[key]) sectionMap[key] = { class: r.class, section: r.section || '', studentCount: 0 };
        sectionMap[key].studentCount++;
    }

    return {
        totalStudents, presentToday, absentToday, attendanceRatio, genderBreakdown,
        totalTeachers, teachersPresentToday, teachersAbsentToday, teachersNotMarked,
        todayCollection, monthTotal, yearTotal, overallTotal, incomeSeries,
        recentNotifications: notifications.slice(0, 5),
        recentActivities: activities.slice(0, 5),
        classSections: Object.keys(sectionMap).map(k => sectionMap[k])
    };
}

/** Per-student fee history and running total — used by the admin fee drill-down view. */
function getStudentFeeSummary(schoolId, apiKey, studentId) {
    const match = authorizeSchool(schoolId, apiKey);
    const ss = SpreadsheetApp.openById(match.obj.sheetId);
    const feesSheet = ss.getSheetByName(COLLECTIONS.fees.tab) || ss.insertSheet(COLLECTIONS.fees.tab);
    if (feesSheet.getLastRow() === 0) { feesSheet.appendRow(COLLECTIONS.fees.headers); feesSheet.setFrozenRows(1); }
    const rows = feesSheet.getDataRange().getValues();
    const headers = COLLECTIONS.fees.headers;
    const records = [];
    let totalPaid = 0, totalPending = 0, totalOverdue = 0;
    for (let i = 1; i < rows.length; i++) {
        const r = rowToRecord(headers, rows[i]);
        if (!r.id || r.studentId !== studentId) continue;
        records.push(r);
        const amt = Number(r.amount) || 0;
        if (r.status === 'Paid') totalPaid += amt;
        else if (r.status === 'Overdue') totalOverdue += amt;
        else totalPending += amt;
    }
    records.sort((a, b) => String(b.date).localeCompare(String(a.date)));
    return { records, totalPaid, totalPending, totalOverdue };
}

// ============================================================================
// HTTP ENTRY POINTS
// ============================================================================

function jsonOut(obj) {
    return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON);
}

function handleRequest(params) {
    const action = params.action;
    try {
        switch (action) {
            case 'register':
                return { status: 'success', school: registerSchool(params) };
            case 'login':
                return { status: 'success', school: loginSchool(params.email, params.password) };
            case 'getSchoolConfig':
                return { status: 'success', school: getSchoolConfig(params.schoolId, params.apiKey) };
            case 'updateSchoolConfig':
                return { status: 'success', school: updateSchoolConfig(params.schoolId, params.apiKey, params.patch || {}) };
            case 'getDashboardStats':
                return { status: 'success', stats: getDashboardStats(params.schoolId, params.apiKey) };
            case 'getMainDashboardData':
                return { status: 'success', data: getMainDashboardData(params) };
            case 'getStudentFeeSummary':
                return { status: 'success', summary: getStudentFeeSummary(params.schoolId, params.apiKey, params.studentId) };
            case 'pingTeacher':
                return { status: 'success', ping: pingTeacher(params.schoolId, params.apiKey, params.teacherId, params.message) };
            case 'acknowledgePing':
                return { status: 'success', ping: acknowledgePing(params.schoolId, params.teacherId, params.apiKey, params.pingId) };

            case 'addTeacher':
                return { status: 'success', teacher: addTeacher(params.schoolId, params.apiKey, params.teacher || {}) };
            case 'teacherLogin':
                return { status: 'success', teacher: teacherLogin(params.schoolId, params.email, params.password) };

            case 'studentLogin':
                return { status: 'success', student: studentLogin(params.schoolId, params.enrlNo, params.password) };

            case 'adminLogin':
                return { status: 'success', admin: adminLogin(params.password) };
            case 'adminListSchools':
                return { status: 'success', schools: adminListSchools(params.adminApiKey) };
            case 'adminSetStatus':
                return { status: 'success', result: adminSetStatus(params.adminApiKey, params.schoolId, params.newStatus) };
            case 'adminResetPassword':
                return { status: 'success', result: adminResetPassword(params.adminApiKey, params.schoolId) };

            case 'list':
                return { status: 'success', records: listRecords(resolveContext(params), params.collection) };
            case 'get':
                return { status: 'success', record: getRecord(resolveContext(params), params.collection, params.id) };
            case 'add':
                return { status: 'success', record: addRecord(resolveContext(params), params.collection, params.record || {}) };
            case 'update':
                return { status: 'success', record: updateRecord(resolveContext(params), params.collection, params.id, params.patch || {}) };
            case 'remove':
                removeRecord(resolveContext(params), params.collection, params.id);
                return { status: 'success' };

            default:
                return { status: 'error', message: 'Unknown action: ' + action };
        }
    } catch (err) {
        return { status: 'error', message: err.message };
    }
}

function doPost(e) {
    let params;
    try {
        params = JSON.parse(e.postData.contents);
    } catch (err) {
        return jsonOut({ status: 'error', message: 'Invalid request body.' });
    }
    return jsonOut(handleRequest(params));
}

function doGet(e) {
    return jsonOut(handleRequest(e.parameter));
}
