server.js

/**
 * ============================================================
 *  Kawanda Secondary School — Backend Server
 *  Platform : Hugging Face Spaces (port 7860)
 *  Database : Supabase
 * ============================================================
 *
 *  REQUIRED ENVIRONMENT VARIABLES (set in HF Space Settings → Variables):
 *    SUPABASE_URL  — e.g. https://xxxx.supabase.co
 *    SUPABASE_KEY  — your Supabase service-role key (starts with eyJ...)
 *    JWT_SECRET    — any long random string, e.g. KawandaSS2026Secret
 *
 * ============================================================
 */

'use strict';

const express    = require('express');
const cors       = require('cors');
const bcrypt     = require('bcryptjs');
const jwt        = require('jsonwebtoken');
const { createClient } = require('@supabase/supabase-js');

/* ─────────────────────────────────────────────
   ENV — crash early with clear message if missing
───────────────────────────────────────────── */
const SUPABASE_URL = process.env.SUPABASE_URL;
const SUPABASE_KEY = process.env.SUPABASE_KEY;
const JWT_SECRET   = process.env.JWT_SECRET;
const PORT         = process.env.PORT || 7860;

/* Warn clearly if DB env vars are missing — but do NOT crash.
   The server must stay alive so HF Space doesn't show an HTML error page. */
if (!SUPABASE_URL || !SUPABASE_KEY) {
  console.error('\n⚠️  WARNING: SUPABASE_URL and SUPABASE_KEY are not set.');
  console.error('   Go to HF Space → Settings → Variables and secrets → add them there.');
  console.error('   API calls will fail until they are set.\n');
}

/* ─────────────────────────────────────────────
   SUPABASE CLIENT
───────────────────────────────────────────── */
const db = createClient(SUPABASE_URL, SUPABASE_KEY);

/* ─────────────────────────────────────────────
   EXPRESS SETUP
───────────────────────────────────────────── */
const app = express();

/* CORS — allow all origins (Netlify frontend can call this) */
app.use(cors({
  origin: '*',
  methods: ['GET', 'POST', 'PUT', 'PATCH', 'DELETE', 'OPTIONS'],
  allowedHeaders: ['Content-Type', 'Authorization'],
}));
app.options('*', cors()); /* handle preflight for all routes */

app.use(express.json({ limit: '1mb' }));

/* ─────────────────────────────────────────────
   HELPERS
───────────────────────────────────────────── */

/* Strip HTML tags to prevent XSS */
function sanitize(s) {
  if (typeof s !== 'string') return s;
  return s.replace(/<[^>]*>/g, '').trim().slice(0, 500);
}

/* Sign a JWT token */
function makeToken(payload) {
  return jwt.sign(payload, JWT_SECRET, { expiresIn: '7d' });
}

/* Middleware: require a valid JWT */
function requireAuth(req, res, next) {
  try {
    const header = req.headers.authorization || '';
    const token  = header.startsWith('Bearer ') ? header.slice(7) : header;
    req.user     = jwt.verify(token, JWT_SECRET);
    next();
  } catch {
    res.status(401).json({ error: 'Not logged in. Please sign in again.' });
  }
}

/* Middleware: require admin role */
function requireAdmin(req, res, next) {
  if (req.user && req.user.role === 'admin') return next();
  res.status(403).json({ error: 'Admin access required.' });
}

/* Middleware: require admin or teacher */
function requireStaff(req, res, next) {
  if (req.user && (req.user.role === 'admin' || req.user.role === 'teacher')) return next();
  res.status(403).json({ error: 'Staff access required.' });
}

/* Generate teacher login ID: KDSS{nnn}
   e.g. KDSS001, KDSS002, KDSS003 */
async function makeTeacherLoginId() {
  /* count existing teachers to get next number */
  const { count } = await db
    .from('users')
    .select('*', { count: 'exact', head: true })
    .eq('role', 'teacher');
  const next = (count || 0) + 1;
  /* try up to 20 slots to avoid collision */
  for (let i = next; i < next + 20; i++) {
    const id = 'KDSS' + String(i).padStart(3, '0');
    const { data } = await db.from('users').select('login_id').eq('login_id', id).single();
    if (!data) return id; /* slot is free */
  }
  /* fallback — extremely unlikely */
  return 'KDSS' + Date.now().toString().slice(-5);
}

/* Generate student login ID: KDSS{nn}{year}
   e.g. KDSS012026, KDSS022026, KDSS032026 */
async function makeStudentLoginId(cls) {
  const year = new Date().getFullYear();
  const prefix = 'KDSS';
  const suffix = String(year);
  /* Find highest existing student ID for this year */
  const { data } = await db
    .from('users')
    .select('login_id')
    .eq('role', 'student')
    .like('login_id', prefix + '%' + suffix)
    .order('login_id', { ascending: false })
    .limit(1);
  let next = 1;
  if (data && data.length > 0) {
    const mid = data[0].login_id.replace(prefix, '').replace(suffix, '');
    const n = parseInt(mid, 10);
    if (!isNaN(n)) next = n + 1;
  }
  /* Retry up to 20 slots to avoid collision */
  for (let attempt = 0; attempt < 20; attempt++) {
    const id = prefix + String(next + attempt).padStart(2, '0') + suffix;
    const { data: existing } = await db.from('users').select('id').eq('login_id', id).single();
    if (!existing) return id;
  }
  /* Fallback */
  return prefix + Date.now().toString().slice(-4) + suffix;
}

/* ─────────────────────────────────────────────
   SEED ADMIN ON STARTUP
   Creates admin account "Kawanda795 / Kawanda795#"
   in Supabase if it does not already exist.
───────────────────────────────────────────── */
async function seedAdmin() {
  console.log('🔍  Checking admin account...');
  try {
    const { data: existing } = await db
      .from('users')
      .select('id, login_id')
      .eq('login_id', 'Kawanda795')
      .single();

    if (existing) {
      console.log('✅  Admin account already exists: Kawanda795');
      return;
    }

    /* Admin not found — create it */
    const hash = await bcrypt.hash('Kawanda795#', 12);
    const { error } = await db.from('users').insert({
      login_id:      'Kawanda795',
      password_hash: hash,
      role:          'admin',
      full_name:     'Administrator',
      status:        'active',
    });

    if (error) {
      console.error('❌  Failed to create admin:', error.message);
      console.error('    Make sure your SUPABASE_KEY has INSERT permission on the users table.');
    } else {
      console.log('✅  Admin account created in Supabase: Kawanda795 / Kawanda795#');
    }
  } catch (err) {
    console.error('❌  seedAdmin error:', err.message);
    console.error('    This usually means SUPABASE_URL or SUPABASE_KEY is wrong.');
  }
}

/* ─────────────────────────────────────────────
   ROUTES
───────────────────────────────────────────── */

/* ── Health check (used by frontend to detect if server is awake) ── */
app.get('/api/health', (req, res) => {
  res.json({
    ok:      true,
    school:  'Kawanda Secondary School',
    version: '3.0',
    time:    new Date().toISOString(),
  });
});

/* ── AUTH: Login ── */
app.post('/api/auth/login', async (req, res) => {
  const loginId  = sanitize(req.body.loginId  || '');
  const password = String(req.body.password   || '');

  if (!loginId || !password) {
    return res.status(400).json({ error: 'Login ID and password are required.' });
  }
  if (password.length > 200) {
    return res.status(400).json({ error: 'Invalid input.' });
  }

  /* Fetch user from DB */
  const { data: user, error: dbErr } = await db
    .from('users')
    .select('*')
    .eq('login_id', loginId)
    .single();

  if (dbErr || !user) {
    /* Always hash to prevent timing attacks */
    await bcrypt.hash('timing_protection', 10);
    return res.status(401).json({ error: 'Incorrect login ID or password.' });
  }

  if (user.status === 'suspended') {
    return res.status(401).json({ error: 'Account suspended. Contact the school office.' });
  }

  const passwordMatch = await bcrypt.compare(password, user.password_hash);
  if (!passwordMatch) {
    return res.status(401).json({ error: 'Incorrect login ID or password.' });
  }

  /* Build token payload */
  const classes = user.classes || (user.class ? [user.class] : []);
  const payload = {
    id:         user.id,
    loginId:    user.login_id,
    role:       user.role,
    subRole:    user.sub_role   || null,
    permissions: user.permissions || null,
    classTchrOf: user.class_teacher_of || null,
    name:       user.full_name,
    subject:    user.subject    || null,
    cls:        user.class      || null,
    classes:    classes,
    childName:  user.child_name  || null,
    childCls:   user.child_class || null,
    childLogin: user.child_login || null,
  };

  res.json({
    token:     makeToken(payload),
    role:      user.role,
    subRole:   user.sub_role   || null,
    name:      user.full_name,
    subject:   user.subject    || null,
    cls:       user.class      || null,
    classes:   classes,
    childName: user.child_name  || null,
    childCls:  user.child_class || null,
  });
});

/* ── AUTH: Teacher self-registration (goes to pending approval) ── */
app.post('/api/auth/register/teacher', async (req, res) => {
  const name    = sanitize(req.body.name    || '');
  const subject = sanitize(req.body.subject || '');
  const stream  = sanitize(req.body.stream  || '');
  const classes = Array.isArray(req.body.classes) ? req.body.classes : (req.body.classes ? [req.body.classes] : []);
  const password = String(req.body.password || '');

  if (!name || !subject || !classes.length || !password) {
    return res.status(400).json({ error: 'Name, subject, at least one class, and password are required.' });
  }
  if (password.length < 6) {
    return res.status(400).json({ error: 'Password must be at least 6 characters.' });
  }

  const proposedLogin = await makeTeacherLoginId();
  const hash = await bcrypt.hash(password, 12);

  const { error } = await db.from('teacher_requests').insert({
    full_name:      name,
    subject:        subject,
    class:          classes[0],
    classes:        classes,
    stream:         stream || null,
    proposed_login: proposedLogin,
    password_hash:  hash,
    status:         'pending',
  });

  if (error) return res.status(500).json({ error: 'Could not submit request: ' + error.message });

  res.json({
    success:      true,
    proposedLogin: proposedLogin,
    message:      'Request submitted! Your login ID will be ' + proposedLogin + '. An admin will approve it shortly.',
  });
});

/* ── AUTH: Parent self-registration ── */
app.post('/api/auth/register/parent', async (req, res) => {
  const email    = sanitize(req.body.email    || '');
  const fullName = sanitize(req.body.fullName || '');
  const phone    = sanitize(req.body.phone    || '');
  const password = String(req.body.password   || '');

  if (!email || !fullName || !password) {
    return res.status(400).json({ error: 'Email, name and password are required.' });
  }
  if (password.length < 6) {
    return res.status(400).json({ error: 'Password must be at least 6 characters.' });
  }

  const { data: existing } = await db.from('users').select('login_id').eq('login_id', email).single();
  if (existing) {
    return res.status(409).json({ error: 'An account with this email already exists. Try signing in.' });
  }

  const hash = await bcrypt.hash(password, 12);
  const { error } = await db.from('users').insert({
    login_id:      email,
    password_hash: hash,
    role:          'parent',
    full_name:     fullName,
    parent_phone:  phone || null,
    status:        'active',
  });

  if (error) return res.status(500).json({ error: error.message });
  res.json({ success: true, message: 'Account created. You can now sign in with your email and password.' });
});

/* ── STUDENTS: list ── */
app.get('/api/students', requireAuth, requireAdmin, async (req, res) => {
  const { data, error } = await db
    .from('users')
    .select('id, login_id, full_name, class, parent_name, parent_phone, parent_email, boarding_type, status, created_at')
    .eq('role', 'student')
    .order('class').order('full_name');
  if (error) return res.status(500).json({ error: error.message });
  res.json(data || []);
});

/* ── STUDENTS: create ── */
app.post('/api/students', requireAuth, requireAdmin, async (req, res) => {
  const { fullName, cls, parentName, parentPhone, parentEmail, boardingType, password } = req.body;
  if (!fullName || !cls || !password) {
    return res.status(400).json({ error: 'Name, class and password are required.' });
  }
  const loginId = await makeStudentLoginId(cls);
  const hash    = await bcrypt.hash(password, 12);

  const { error } = await db.from('users').insert({
    login_id:      loginId,
    password_hash: hash,
    role:          'student',
    full_name:     sanitize(fullName),
    class:         cls,
    parent_name:   sanitize(parentName   || ''),
    parent_phone:  parentPhone  || null,
    parent_email:  parentEmail  || null,
    boarding_type: boardingType || null,
    status:        'active',
  });

  if (error) return res.status(500).json({ error: error.message });
  res.json({ success: true, loginId, message: 'Student created. Login ID: ' + loginId });
});

/* ── STUDENTS: update ── */
app.patch('/api/students/:loginId', requireAuth, requireAdmin, async (req, res) => {
  const updates = {};
  if (req.body.password) updates.password_hash = await bcrypt.hash(req.body.password, 12);
  if (req.body.status)   updates.status = req.body.status;
  if (req.body.cls)      updates.class  = req.body.cls;
  if (!Object.keys(updates).length) return res.status(400).json({ error: 'Nothing to update.' });
  await db.from('users').update(updates).eq('login_id', req.params.loginId).eq('role', 'student');
  res.json({ success: true });
});

/* ── STUDENTS: delete (suspend) ── */
app.delete('/api/students/:loginId', requireAuth, requireAdmin, async (req, res) => {
  await db.from('users').delete().eq('login_id', req.params.loginId).eq('role', 'student');
  res.json({ success: true });
});

/* ── TEACHERS: list ── */
app.get('/api/teachers', requireAuth, requireAdmin, async (req, res) => {
  const { data, error } = await db
    .from('users')
    .select('id, login_id, full_name, subject, class, classes, class_teacher_of, stream, status, created_at')
    .eq('role', 'teacher')
    .order('login_id');
  if (error) return res.status(500).json({ error: error.message });
  res.json((data || []).map(t => ({
    ...t,
    classes: t.classes || (t.class ? [t.class] : []),
  })));
});

/* ── TEACHERS: create (by admin) ── */
app.post('/api/teachers', requireAuth, requireAdmin, async (req, res) => {
  const { name, subject, cls, classes, password } = req.body;
  const classList = Array.isArray(classes) && classes.length ? classes : (cls ? [cls] : []);
  if (!name || !subject || !classList.length || !password) {
    return res.status(400).json({ error: 'Name, subject, at least one class, and password are required.' });
  }
  const loginId = await makeTeacherLoginId();
  const hash    = await bcrypt.hash(password, 12);

  const { error } = await db.from('users').insert({
    login_id:      loginId,
    password_hash: hash,
    role:          'teacher',
    full_name:     sanitize(name),
    subject:       subject,
    class:         classList[0],
    classes:       classList,
    status:        'active',
  });

  if (error) return res.status(500).json({ error: error.message });
  res.json({ success: true, loginId, classes: classList });
});

/* ── TEACHERS: update ── */
app.patch('/api/teachers/:loginId', requireAuth, requireAdmin, async (req, res) => {
  const updates = {};
  if (req.body.password) updates.password_hash = await bcrypt.hash(req.body.password, 12);
  if (req.body.status)   updates.status  = req.body.status;
  if (req.body.classes && req.body.classes.length) {
    updates.classes = req.body.classes;
    updates.class   = req.body.classes[0];
  }
  if (req.body.classTchrOf !== undefined) updates.class_teacher_of = req.body.classTchrOf || null;
  if (!Object.keys(updates).length) return res.status(400).json({ error: 'Nothing to update.' });
  await db.from('users').update(updates).eq('login_id', req.params.loginId).eq('role', 'teacher');
  res.json({ success: true });
});

/* ── TEACHERS: delete ── */
app.delete('/api/teachers/:loginId', requireAuth, requireAdmin, async (req, res) => {
  await db.from('users').delete().eq('login_id', req.params.loginId).eq('role', 'teacher');
  res.json({ success: true });
});

/* ── TEACHER REQUESTS: list pending ── */
app.get('/api/teacher-requests', requireAuth, requireAdmin, async (req, res) => {
  const { data } = await db
    .from('teacher_requests')
    .select('*')
    .eq('status', 'pending')
    .order('submitted_at', { ascending: false });
  res.json(data || []);
});

/* ── TEACHER REQUESTS: approve or reject ── */
app.patch('/api/teacher-requests/:id', requireAuth, requireAdmin, async (req, res) => {
  const { status } = req.body;
  const { data: reqRow } = await db.from('teacher_requests').select('*').eq('id', req.params.id).single();
  if (!reqRow) return res.status(404).json({ error: 'Request not found.' });

  if (status === 'approved') {
    const classList = reqRow.classes || [reqRow.class];
    const { data: existing } = await db.from('users').select('login_id').eq('login_id', reqRow.proposed_login).single();
    if (!existing) {
      await db.from('users').insert({
        login_id:      reqRow.proposed_login,
        password_hash: reqRow.password_hash,
        role:          'teacher',
        full_name:     reqRow.full_name,
        subject:       reqRow.subject,
        class:         classList[0],
        classes:       classList,
        stream:        reqRow.stream || null,
        status:        'active',
      });
    }
  }

  await db.from('teacher_requests').update({
    status,
    reviewed_at: new Date().toISOString(),
    reviewed_by: req.user.id,
  }).eq('id', req.params.id);

  res.json({ success: true, loginId: reqRow.proposed_login });
});

/* ── FEES: list ── */
app.get('/api/fees', requireAuth, async (req, res) => {
  let q = db.from('fee_payments').select('*').order('created_at', { ascending: false });
  if (req.query.student) q = q.ilike('student_name', '%' + req.query.student + '%');
  if (req.query.class)   q = q.eq('class',   req.query.class);
  if (req.query.term)    q = q.eq('term',    req.query.term);
  if (req.query.status)  q = q.eq('status',  req.query.status);
  if (req.query.student) q = q.ilike('student_name', '%' + req.query.student + '%');
  const { data, error } = await q;
  if (error) return res.status(500).json({ error: error.message });
  res.json(data || []);
});

/* ── FEES: record ── */
app.post('/api/fees', requireAuth, async (req, res) => {
  const { studentName, parentName, cls, term, year, amount, method, status, date } = req.body;
  if (!studentName || !cls || !amount) {
    return res.status(400).json({ error: 'Student name, class and amount are required.' });
  }
  const { data, error } = await db.from('fee_payments').insert({
    id:            'FEE-' + Date.now(),
    student_name:  sanitize(studentName),
    parent_name:   sanitize(parentName || ''),
    class:         cls,
    term:          term  || null,
    year:          year  || String(new Date().getFullYear()),
    amount:        parseInt(amount),
    method:        method || 'Cash',
    status:        status || 'paid',
    payment_date:  date   || new Date().toISOString().split('T')[0],
  }).select().single();
  if (error) return res.status(500).json({ error: error.message });
  res.json(data);
});

/* ── FEES: delete ── */
app.delete('/api/fees/:id', requireAuth, requireAdmin, async (req, res) => {
  await db.from('fee_payments').delete().eq('id', req.params.id);
  res.json({ success: true });
});

/* ── PROGRAMME: list ── */
app.get('/api/programme', async (req, res) => {
  const { data } = await db.from('programme').select('*').order('date');
  res.json(data || []);
});

/* ── PROGRAMME: create ── */
app.post('/api/programme', requireAuth, requireAdmin, async (req, res) => {
  const { data, error } = await db.from('programme').insert({
    date:        req.body.date,
    time:        req.body.time        || null,
    title:       sanitize(req.body.title || ''),
    type:        req.body.type        || 'academic',
    classes:     req.body.classes     || 'All',
    description: sanitize(req.body.description || ''),
  }).select().single();
  if (error) return res.status(500).json({ error: error.message });
  res.json(data);
});

/* ── PROGRAMME: update ── */
app.put('/api/programme/:id', requireAuth, requireAdmin, async (req, res) => {
  const { data, error } = await db.from('programme').update({
    date:        req.body.date,
    time:        req.body.time        || null,
    title:       sanitize(req.body.title || ''),
    type:        req.body.type        || 'academic',
    classes:     req.body.classes     || 'All',
    description: sanitize(req.body.description || ''),
  }).eq('id', req.params.id).select().single();
  if (error) return res.status(500).json({ error: error.message });
  res.json(data);
});

/* ── PROGRAMME: delete ── */
app.delete('/api/programme/:id', requireAuth, requireAdmin, async (req, res) => {
  await db.from('programme').delete().eq('id', req.params.id);
  res.json({ success: true });
});

/* ── ANNOUNCEMENTS: list ── */
app.get('/api/announcements', requireAuth, async (req, res) => {
  let q = db.from('announcements').select('*').order('created_at', { ascending: false });
  if (req.query.audience && req.query.audience !== 'All') {
    q = q.in('audience', ['All', req.query.audience]);
  }
  const { data } = await q;
  res.json(data || []);
});

/* ── ANNOUNCEMENTS: create ── */
app.post('/api/announcements', requireAuth, requireStaff, async (req, res) => {
  if (!req.body.title || !req.body.message) {
    return res.status(400).json({ error: 'Title and message are required.' });
  }
  const { data, error } = await db.from('announcements').insert({
    title:       sanitize(req.body.title),
    message:     sanitize(req.body.message),
    audience:    req.body.audience || 'All',
    urgent:      !!req.body.urgent,
    sender_name: req.user.name,
    sender_id:   req.user.id,
  }).select().single();
  if (error) return res.status(500).json({ error: error.message });
  res.json(data);
});

/* ── ANNOUNCEMENTS: delete ── */
app.delete('/api/announcements/:id', requireAuth, requireAdmin, async (req, res) => {
  await db.from('announcements').delete().eq('id', req.params.id);
  res.json({ success: true });
});

/* ── TIMETABLE: get by class ── */
app.get('/api/timetable/:cls', requireAuth, async (req, res) => {
  const { data } = await db.from('timetables').select('*').eq('class', req.params.cls);
  res.json(data || []);
});

/* ── TIMETABLE: set cell ── */
app.put('/api/timetable', requireAuth, requireStaff, async (req, res) => {
  const { cls, dayIndex, periodIndex, subject } = req.body;
  const { data, error } = await db.from('timetables').upsert({
    class:        cls,
    day_index:    dayIndex,
    period_index: periodIndex,
    subject:      sanitize(subject || ''),
  }, { onConflict: 'class,day_index,period_index' }).select().single();
  if (error) return res.status(500).json({ error: error.message });
  res.json(data);
});

/* ── RESOURCES: list ── */
app.get('/api/resources', requireAuth, async (req, res) => {
  let q = db.from('resources').select('*').order('created_at', { ascending: false });
  if (req.user.role === 'student') {
    q = q.eq('class', req.user.cls);
  } else {
    if (req.query.class)   q = q.eq('class',        req.query.class);
    if (req.query.subject) q = q.eq('subject',      req.query.subject);
    if (req.query.teacher) q = q.eq('teacher_name', req.query.teacher);
  }
  const { data, error } = await q;
  if (error) return res.status(500).json({ error: error.message });
  res.json(data || []);
});

/* ── RESOURCES: upload ── */
app.post('/api/resources', requireAuth, requireStaff, async (req, res) => {
  const { title, subject, cls, description, fileUrl, fileType } = req.body;
  if (!title || !subject || !cls || !fileUrl) {
    return res.status(400).json({ error: 'Title, subject, class and file link are required.' });
  }
  const { data, error } = await db.from('resources').insert({
    id:           'RES-' + Date.now(),
    title:        sanitize(title),
    subject:      subject,
    class:        cls,
    description:  sanitize(description || ''),
    file_url:     fileUrl,
    file_type:    fileType || 'other',
    teacher_name: req.user.name,
    teacher_id:   req.user.id,
  }).select().single();
  if (error) return res.status(500).json({ error: error.message });
  res.json(data);
});

/* ── RESOURCES: delete ── */
app.delete('/api/resources/:id', requireAuth, requireStaff, async (req, res) => {
  let q = db.from('resources').delete().eq('id', req.params.id);
  if (req.user.role === 'teacher') q = q.eq('teacher_id', req.user.id);
  await q;
  res.json({ success: true });
});

/* ── ADMISSIONS: submit application ── */
app.post('/api/admissions', async (req, res) => {
  const { firstName, lastName, programme } = req.body;
  if (!firstName || !lastName || !programme) {
    return res.status(400).json({ error: 'First name, last name and programme are required.' });
  }
  const reference = 'KSS-' + new Date().getFullYear() + '-' + Math.random().toString(36).slice(2, 8).toUpperCase();
  const { error } = await db.from('admission_applications').insert({
    id:              'APP-' + Date.now(),
    reference:       reference,
    first_name:      sanitize(firstName),
    last_name:       sanitize(lastName),
    date_of_birth:   req.body.dob          || null,
    gender:          req.body.gender        || null,
    programme:       programme,
    boarding_type:   req.body.boardingType  || null,
    guardian_name:   sanitize(req.body.guardianName  || ''),
    guardian_phone:  req.body.guardianPhone  || null,
    guardian_email:  req.body.guardianEmail  || null,
    previous_school: sanitize(req.body.prevSchool || ''),
    subjects_data:   req.body.subjects       || {},
  });
  if (error) return res.status(500).json({ error: error.message });
  res.json({ success: true, reference, message: 'Application submitted! Reference: ' + reference });
});

/* ── ADMISSIONS: list (admin only) ── */
app.get('/api/admissions', requireAuth, requireAdmin, async (req, res) => {
  let q = db.from('admission_applications').select('*').order('submitted_at', { ascending: false });
  if (req.query.status) q = q.eq('status', req.query.status);
  const { data } = await q;
  res.json(data || []);
});

/* ── ADMISSIONS: view one ── */
app.get('/api/admissions/:id', requireAuth, requireAdmin, async (req, res) => {
  const { data } = await db.from('admission_applications').select('*').eq('id', req.params.id).single();
  if (!data) return res.status(404).json({ error: 'Application not found.' });
  res.json(data);
});

/* ── ADMISSIONS: update status (accept/reject) ── */
app.patch('/api/admissions/:id', requireAuth, requireAdmin, async (req, res) => {
  const { status, adminNotes } = req.body;
  const { data: application } = await db.from('admission_applications').select('*').eq('id', req.params.id).single();
  if (!application) return res.status(404).json({ error: 'Application not found.' });

  let generatedLogin = null;

  if (status === 'accepted') {
    /* Determine class from programme */
    const classMap = { s1: 'S.1', s2: 'S.2', s3: 'S.3', s4: 'S.4', s5: 'S.5', s6: 'S.6', transfer: 'S.1' };
    const cls = classMap[application.programme] || 'S.1';
    generatedLogin = await makeStudentLoginId(cls);

    /* Generate a simple password */
    const pw   = 'Kss' + Math.floor(1000 + Math.random() * 9000);
    const hash = await bcrypt.hash(pw, 12);

    await db.from('users').insert({
      login_id:      generatedLogin,
      password_hash: hash,
      role:          'student',
      full_name:     application.first_name + ' ' + application.last_name,
      class:         cls,
      parent_name:   application.guardian_name  || null,
      parent_phone:  application.guardian_phone || null,
      parent_email:  application.guardian_email || null,
      status:        'active',
    });

    await db.from('admission_applications').update({
      status,
      admin_notes:     adminNotes || null,
      generated_login: generatedLogin,
      reviewed_at:     new Date().toISOString(),
    }).eq('id', req.params.id);
  } else {
    await db.from('admission_applications').update({
      status,
      admin_notes: adminNotes || null,
      reviewed_at: new Date().toISOString(),
    }).eq('id', req.params.id);
  }

  res.json({ success: true, generatedLogin });
});

app.delete('/api/admissions/:id', requireAuth, requireAdmin, async (req, res) => {
  const { error } = await db.from('admission_applications').delete().eq('id', req.params.id);
  if (error) return res.status(500).json({ error: error.message });
  res.json({ success: true });
});


/* ── ADMISSION CONFIG ── */
app.get('/api/admission-config', async (req, res) => {
  const { data } = await db.from('admission_config').select('*');
  const config = {};
  (data || []).forEach(row => { config[row.key] = row.value; });
  res.json(config);
});

app.put('/api/admission-config', requireAuth, requireAdmin, async (req, res) => {
  const rows = Object.entries(req.body).map(([key, value]) => ({ key, value: String(value) }));
  const { error } = await db.from('admission_config').upsert(rows, { onConflict: 'key' });
  if (error) return res.status(500).json({ error: error.message });
  res.json({ success: true });
});

/* ── HOMEPAGE IMAGES ── */
app.get('/api/homepage-images', async (req, res) => {
  const { data } = await db.from('homepage_images').select('*').eq('active', true).order('sort_order').order('created_at');
  res.json(data || []);
});

app.post('/api/homepage-images', requireAuth, requireAdmin, async (req, res) => {
  const { title, imageUrl, sortOrder } = req.body;
  if (!imageUrl) return res.status(400).json({ error: 'Image URL is required.' });
  const { data, error } = await db.from('homepage_images').insert({
    title:       sanitize(title || ''),
    image_url:   imageUrl,
    sort_order:  sortOrder || 0,
    active:      true,
    uploaded_by: req.user.id,
  }).select().single();
  if (error) return res.status(500).json({ error: error.message });
  res.json(data);
});

app.delete('/api/homepage-images/:id', requireAuth, requireAdmin, async (req, res) => {
  await db.from('homepage_images').delete().eq('id', req.params.id);
  res.json({ success: true });
});

/* ── NOTIFICATIONS: list ── */
app.get('/api/notifications', requireAuth, async (req, res) => {
  const { data } = await db
    .from('notifications')
    .select('*')
    .eq('recipient_id', req.user.id)
    .order('created_at', { ascending: false });
  res.json(data || []);
});

/* ── NOTIFICATIONS: mark read ── */
app.patch('/api/notifications/read-all', requireAuth, async (req, res) => {
  await db.from('notifications').update({ read: true }).eq('recipient_id', req.user.id);
  res.json({ success: true });
});

app.patch('/api/notifications/:id/read', requireAuth, async (req, res) => {
  await db.from('notifications').update({ read: true }).eq('id', req.params.id).eq('recipient_id', req.user.id);
  res.json({ success: true });
});

/* ── RC BATCHES: class teacher submits a batch of report cards to admin ── */
app.post('/api/rc-batches', requireAuth, async (req, res) => {
  const { cls, stream, term, year, note } = req.body;
  if (!cls || !term || !year) return res.status(400).json({ error: 'cls, term and year required.' });
  const { data, error } = await db.from('rc_batches').insert({
    teacher_login: req.user.loginId, teacher_name: req.user.name,
    class: cls, stream: stream || null, term, year, note: note || '', status: 'draft',
  }).select().single();
  if (error) return res.status(500).json({ error: error.message });
  res.json(data);
});

app.get('/api/rc-batches', requireAuth, async (req, res) => {
  let q = db.from('rc_batches').select('*').order('created_at', { ascending: false });
  if (req.user.role === 'teacher') q = q.eq('teacher_login', req.user.loginId);
  if (req.query.status) q = q.eq('status', req.query.status);
  const { data, error } = await q;
  if (error) return res.status(500).json({ error: error.message });
  res.json(data || []);
});

app.patch('/api/rc-batches/:id', requireAuth, async (req, res) => {
  const updates = {};
  if (req.body.status) updates.status = req.body.status;
  if (req.body.note !== undefined) updates.note = req.body.note;
  const { error } = await db.from('rc_batches').update(updates).eq('id', req.params.id);
  if (error) return res.status(500).json({ error: error.message });
  if (req.body.status === 'approved') {
    await db.from('report_cards').update({ status: 'published' }).eq('batch_id', req.params.id);
  }
  res.json({ success: true });
});

app.delete('/api/rc-batches/:id', requireAuth, requireAdmin, async (req, res) => {
  await db.from('rc_batches').delete().eq('id', req.params.id);
  res.json({ success: true });
});

/* ── RC ASSIGNMENTS: admin sends template request to class teacher ── */
app.post('/api/rc-assignments', requireAuth, requireAdmin, async (req, res) => {
  const { teacherLoginId, cls, stream, term, year, note } = req.body;
  if (!teacherLoginId || !cls || !term || !year)
    return res.status(400).json({ error: 'teacherLoginId, cls, term and year required.' });
  const { data, error } = await db.from('rc_assignments').insert({
    teacher_login: teacherLoginId, class: cls, stream: stream || null,
    term, year, note: note || '', status: 'pending', created_by: req.user.id,
  }).select().single();
  if (error) return res.status(500).json({ error: error.message });

  /* Send notification to the assigned teacher */
  try {
    const { data: teacher } = await db.from('users').select('id,full_name').eq('login_id', teacherLoginId).single();
    if (teacher) {
      const classLabel = cls + (stream ? ' ' + stream : '');
      await db.from('notifications').insert({
        recipient_id: teacher.id,
        title: 'Report Card Assignment',
        message: 'You have been assigned to fill report cards for ' + classLabel + ' — ' + term + ' ' + year + (note ? '.\nNote: ' + note : '.') + '\nGo to the Reports section to start filling.',
        type: 'rc_assignment',
        read: false,
      });
    }
  } catch(notifErr) { /* non-fatal */ }

  res.json(data);
});

app.get('/api/rc-assignments', requireAuth, async (req, res) => {
  let q = db.from('rc_assignments').select('*').order('created_at', { ascending: false });
  if (req.user.role === 'teacher') q = q.eq('teacher_login', req.user.loginId);
  const { data, error } = await q;
  if (error) return res.status(500).json({ error: error.message });
  res.json(data || []);
});

app.patch('/api/rc-assignments/:id', requireAuth, async (req, res) => {
  const { error } = await db.from('rc_assignments').update(req.body).eq('id', req.params.id);
  if (error) return res.status(500).json({ error: error.message });
  res.json({ success: true });
});

app.delete('/api/rc-assignments/:id', requireAuth, requireAdmin, async (req, res) => {
  await db.from('rc_assignments').delete().eq('id', req.params.id);
  res.json({ success: true });
});

/* ── RC BULK CSV ── */
app.post('/api/report-cards/bulk-csv', requireAuth, requireStaff, async (req, res) => {
  const { rows, batchId } = req.body;
  if (!rows || !rows.length) return res.status(400).json({ error: 'No rows provided.' });
  const results = [];
  for (const row of rows) {
    const { studentName, studentLogin, cls, stream, term, year, regNo, subjects,
            classTchrName, classTchrContact, principalComment } = row;
    if (!studentName || !cls) continue;
    let studentId = null;
    if (studentLogin) {
      const { data: su } = await db.from('users').select('id').eq('login_id', studentLogin).single();
      if (su) studentId = su.id;
    }
    const avg = subjects && subjects.length
      ? (subjects.reduce((s, r) => s + (r.total || 0), 0) / subjects.length).toFixed(2) : 0;
    const { data: rc, error } = await db.from('report_cards').insert({
      student_id: studentId, student_login: studentLogin || null,
      student_name: sanitize(studentName), class: cls, stream: stream || null,
      term: term || 'Term 1', year: year || '2026', reg_no: regNo || null,
      average: avg, batch_id: batchId || null,
      class_teacher_name: sanitize(classTchrName || ''),
      class_teacher_contact: classTchrContact || '',
      principal_comment: sanitize(principalComment || ''),
      status: 'draft', created_by: req.user.id,
    }).select().single();
    if (error || !rc) { results.push({ studentName, error: error?.message }); continue; }
    if (subjects && subjects.length) {
      await db.from('report_card_subjects').insert(subjects.map(s => ({
        report_card_id: rc.id, subject: s.subject, chapter: s.chapter || '',
        score_aoi: s.aoi != null ? s.aoi : null, score_eot: s.eot != null ? s.eot : null,
        marks: s.total != null ? s.total : (s.marks || 0), out_of: 100,
        grade: s.grade || (s.total >= 80 ? 'A' : s.total >= 70 ? 'B' : s.total >= 60 ? 'C' : s.total >= 50 ? 'D' : 'E'),
        teacher_name: req.user.name,
      })));
    }
    results.push({ studentName, id: rc.id, ok: true });
  }
  res.json({ created: results.filter(r => r.ok).length, results });
});


/* ── REPORT CARDS: list ── */
app.get('/api/report-cards', requireAuth, async (req, res) => {
  let q = db.from('report_cards').select('*, report_card_subjects(*)').order('created_at', { ascending: false });
  if (req.user.role === 'student') {
    q = q.or('student_login.eq.' + req.user.loginId + ',student_id.eq.' + req.user.id).eq('status', 'published');
  } else if (req.user.role === 'parent') {
    /* support both ?child_login= and ?studentLogin= */
    const cLogin = req.query.child_login || req.query.studentLogin || req.user.childLogin;
    if (cLogin) q = q.eq('student_login', cLogin).eq('status', 'published');
    else { res.json([]); return; }
  } else if (req.user.role === 'teacher') {
    if (req.query.batch_id) q = q.eq('batch_id', req.query.batch_id);
    else if (req.user.classTchrOf) q = q.eq('class', req.user.classTchrOf);
    else q = q.eq('class', req.user.cls);
  }
  if (req.query.class)      q = q.eq('class',    req.query.class);
  if (req.query.term)       q = q.eq('term',     req.query.term);
  if (req.query.year)       q = q.eq('year',     req.query.year);
  if (req.query.status)     q = q.eq('status',   req.query.status);
  if (req.query.batch_id)   q = q.eq('batch_id', req.query.batch_id);
  if (req.query.studentLogin && req.user.role !== 'parent') q = q.eq('student_login', req.query.studentLogin);
  const { data, error } = await q;
  if (error) return res.status(500).json({ error: error.message });
  res.json((data || []).map(rc => {
    const subs = rc.report_card_subjects || [];
    delete rc.report_card_subjects;
    rc.subjects = subs;
    return rc;
  }));
});

/* ── REPORT CARDS: get single by ID ── */
app.get('/api/report-cards/:id', requireAuth, async (req, res) => {
  const { data: rc, error } = await db.from('report_cards')
    .select('*, report_card_subjects(*)')
    .eq('id', req.params.id)
    .single();
  if (error || !rc) return res.status(404).json({ error: 'Report card not found.' });
  /* Role-based access check */
  if (req.user.role === 'student') {
    if (rc.student_login !== req.user.loginId && rc.student_id !== req.user.id)
      return res.status(403).json({ error: 'Access denied.' });
    if (rc.status !== 'published') return res.status(403).json({ error: 'Not yet published.' });
  } else if (req.user.role === 'parent') {
    /* parent must have linked this student */
    const { data: link } = await db.from('parent_children')
      .select('id').eq('parent_id', req.user.id).eq('student_login', rc.student_login).single();
    if (!link) return res.status(403).json({ error: 'Access denied.' });
    if (rc.status !== 'published') return res.status(403).json({ error: 'Not yet published.' });
  }
  const subs = rc.report_card_subjects || [];
  delete rc.report_card_subjects;
  rc.subjects = subs;
  res.json(rc);
});

/* ── REPORT CARDS: create ── */
app.post('/api/report-cards', requireAuth, requireStaff, async (req, res) => {
  const {
    studentId, studentLogin, studentName, cls, stream, term, year, regNo,
    subjects, conduct, classTchrComment, classTchrName, classTchrContact,
    principalComment, wardenComment, position, outOf, nextTermStart, nextTermBoard,
    batchId, status
  } = req.body;
  if (!studentName || !cls || !term || !year)
    return res.status(400).json({ error: 'Student name, class, term and year are required.' });

  // Resolve student_id from login if not provided
  let resolvedId = studentId || null;
  let resolvedLogin = studentLogin || null;
  if (!resolvedId && studentLogin) {
    const { data: su } = await db.from('users').select('id').eq('login_id', studentLogin).single();
    if (su) resolvedId = su.id;
  }

  const avg = subjects && subjects.length
    ? (subjects.reduce((s, r) => s + (r.total != null ? r.total : (r.marks || 0)), 0) / subjects.length).toFixed(2) : 0;

  const { data: rc, error } = await db.from('report_cards').insert({
    student_id: resolvedId, student_login: resolvedLogin,
    student_name: sanitize(studentName), class: cls, stream: stream || null,
    term, year, reg_no: regNo || null, average: avg,
    position: position || null, out_of: outOf || null, conduct: conduct || null,
    class_teacher_comment: sanitize(classTchrComment || ''),
    class_teacher_name:    sanitize(classTchrName    || ''),
    class_teacher_contact: classTchrContact || '',
    principal_comment:     sanitize(principalComment || ''),
    warden_comment:        sanitize(wardenComment    || ''),
    next_term_start: nextTermStart || null, next_term_board: nextTermBoard || null,
    batch_id: batchId || null,
    class_teacher_login: req.user.classTchrOf ? req.user.loginId : null,
    status: status || 'draft', created_by: req.user.id,
  }).select().single();

  if (error) return res.status(500).json({ error: error.message });

  if (subjects && subjects.length) {
    await db.from('report_card_subjects').insert(subjects.map(s => ({
      report_card_id: rc.id, subject: s.subject, chapter: s.chapter || '',
      score_aoi: s.aoi != null ? s.aoi : null, score_eot: s.eot != null ? s.eot : null,
      marks: s.total != null ? s.total : (s.marks || 0), out_of: s.outOf || 100,
      grade: s.grade || '', teacher_comment: sanitize(s.comment || ''),
      teacher_ref: s.teacherRef || '', teacher_name: s.teacherName || req.user.name,
    })));
  }
  res.json(rc);
});

/* ── REPORT CARDS: publish / update ── */
app.patch('/api/report-cards/:id', requireAuth, requireStaff, async (req, res) => {
  const updates = {};
  const b = req.body;
  if (b.status             !== undefined) updates.status                = b.status;
  if (b.principalComment   !== undefined) updates.principal_comment     = sanitize(b.principalComment   || '');
  if (b.conduct            !== undefined) updates.conduct               = b.conduct;
  if (b.classTchrComment   !== undefined) updates.class_teacher_comment = sanitize(b.classTchrComment   || '');
  if (b.classTchrName      !== undefined) updates.class_teacher_name    = sanitize(b.classTchrName      || '');
  if (b.classTchrContact   !== undefined) updates.class_teacher_contact = b.classTchrContact             || '';
  if (b.wardenComment      !== undefined) updates.warden_comment        = sanitize(b.wardenComment      || '');
  if (b.nextTermStart      !== undefined) updates.next_term_start       = b.nextTermStart || null;
  if (b.nextTermBoard      !== undefined) updates.next_term_board       = b.nextTermBoard || null;
  if (b.position           !== undefined) updates.position              = b.position || null;
  if (b.outOf              !== undefined) updates.out_of                = b.outOf    || null;
  if (b.regNo              !== undefined) updates.reg_no                = b.regNo    || null;
  if (b.studentName        !== undefined) updates.student_name          = sanitize(b.studentName || '');
  if (b.cls                !== undefined) updates.class                 = b.cls;
  if (b.stream             !== undefined) updates.stream                = b.stream || null;
  if (b.term               !== undefined) updates.term                  = b.term;
  if (b.year               !== undefined) updates.year                  = b.year;
  if (!Object.keys(updates).length) return res.status(400).json({ error: 'Nothing to update.' });
  const { error } = await db.from('report_cards').update(updates).eq('id', req.params.id);
  if (error) return res.status(500).json({ error: error.message });
  /* If subjects array is provided, replace them */
  if (b.subjects && Array.isArray(b.subjects)) {
    await db.from('report_card_subjects').delete().eq('report_card_id', req.params.id);
    if (b.subjects.length) {
      await db.from('report_card_subjects').insert(b.subjects.map(s => ({
        report_card_id: req.params.id,
        subject: s.subject, chapter: s.chapter || '',
        score_aoi: s.aoi != null ? s.aoi : null,
        score_eot: s.eot != null ? s.eot : null,
        marks: s.total != null ? s.total : (s.marks || 0), out_of: s.outOf || 100,
        grade: s.grade || '', teacher_comment: sanitize(s.comment || ''),
        teacher_ref: s.teacherRef || '', teacher_name: s.teacherName || req.user.name,
      })));
    }
    /* Recalculate average */
    const avg = b.subjects.length
      ? (b.subjects.reduce((a, s) => a + (s.total != null ? s.total : (s.marks || 0)), 0) / b.subjects.length).toFixed(2) : 0;
    await db.from('report_cards').update({ average: avg }).eq('id', req.params.id);
  }
  res.json({ success: true });
});

app.delete('/api/report-cards/:id', requireAuth, requireAdmin, async (req, res) => {
  const { error } = await db.from('report_cards').delete().eq('id', req.params.id);
  if (error) return res.status(500).json({ error: error.message });
  res.json({ success: true });
});


/* ── PARENT: link a child ── */
app.post('/api/parent/link-child', requireAuth, async (req, res) => {
  if (req.user.role !== 'parent') return res.status(403).json({ error: 'Parents only.' });
  const { childLoginId } = req.body;
  if (!childLoginId) return res.status(400).json({ error: 'Child login ID is required.' });

  const { data: student } = await db
    .from('users')
    .select('id, login_id, full_name, class')
    .eq('login_id', childLoginId.toUpperCase())
    .eq('role', 'student')
    .single();

  if (!student) return res.status(404).json({ error: 'Student not found. Please check the login ID.' });

  const { data: existing } = await db
    .from('parent_children')
    .select('id')
    .eq('parent_id', req.user.id)
    .eq('student_id', student.id)
    .single();

  if (existing) return res.status(409).json({ error: 'This child is already linked to your account.' });

  await db.from('parent_children').insert({
    parent_id:     req.user.id,
    student_id:    student.id,
    student_login: student.login_id,
    student_name:  student.full_name,
    student_class: student.class,
  });

  res.json({ success: true, message: student.full_name + ' (' + student.class + ') linked successfully.' });
});

/* ── PARENT: list children ── */
app.delete('/api/parent/link-child/:childLoginId', requireAuth, async (req, res) => {
  if (req.user.role !== 'parent') return res.status(403).json({ error: 'Parents only.' });
  await db.from('parent_children').delete()
    .eq('parent_id', req.user.id)
    .eq('student_login', req.params.childLoginId);
  res.json({ success: true });
});

app.get('/api/parent/children', requireAuth, async (req, res) => {
  if (req.user.role !== 'parent') return res.status(403).json({ error: 'Parents only.' });
  const { data } = await db.from('parent_children').select('*').eq('parent_id', req.user.id).order('created_at');
  res.json(data || []);
});

/* ── SUB-ADMINS: create ── */
app.post('/api/admin/sub-admins', requireAuth, requireAdmin, async (req, res) => {
  if (req.user.subRole) return res.status(403).json({ error: 'Only the super admin can create sub-admins.' });
  const { name, subRole, password, customLoginId } = req.body;
  const validRoles = ['headteacher', 'academic', 'nurse', 'bursar'];
  if (!name || !subRole || !password || !validRoles.includes(subRole)) {
    return res.status(400).json({ error: 'Name, valid role and password are required.' });
  }
  const loginId = customLoginId ? customLoginId.toUpperCase() : ('ADMIN' + subRole.toUpperCase().slice(0, 4) + Math.floor(100 + Math.random() * 900));
  const { data: existing } = await db.from('users').select('login_id').eq('login_id', loginId).single();
  if (existing) return res.status(409).json({ error: 'Login ID "' + loginId + '" is already taken.' });
  const hash = await bcrypt.hash(password, 12);
  const { error } = await db.from('users').insert({
    login_id:      loginId,
    password_hash: hash,
    role:          'admin',
    sub_role:      subRole,
    full_name:     sanitize(name),
    status:        'active',
  });
  if (error) return res.status(500).json({ error: error.message });
  res.json({ success: true, loginId });
});

/* ── SUB-ADMINS: list ── */
app.get('/api/admin/sub-admins', requireAuth, requireAdmin, async (req, res) => {
  const { data } = await db
    .from('users')
    .select('id, login_id, full_name, sub_role, status, created_at')
    .eq('role', 'admin')
    .not('sub_role', 'is', null)
    .order('sub_role');
  res.json(data || []);
});

/* ── SUB-ADMINS: delete ── */
app.delete('/api/admin/sub-admins/:loginId', requireAuth, requireAdmin, async (req, res) => {
  if (req.user.subRole) return res.status(403).json({ error: 'Only the super admin can remove sub-admins.' });
  await db.from('users').delete().eq('login_id', req.params.loginId).eq('role', 'admin').not('sub_role', 'is', null);
  res.json({ success: true });
});

/* ── REPORT TEMPLATES: get students for a class ── */
app.get('/api/report-template/students/:cls', requireAuth, requireAdmin, async (req, res) => {
  const { data, error } = await db.from('users').select('id, login_id, full_name, class, stream').eq('role','student').eq('class',req.params.cls).eq('status','active').order('full_name');
  if (error) return res.status(500).json({ error: error.message });
  res.json(data || []);
});

/* ── REPORT TEMPLATES: admin assigns template to a teacher ── */
app.post('/api/report-templates', requireAuth, requireAdmin, async (req, res) => {
  const { teacherLoginId, cls, term, year, subject } = req.body;
  if (!teacherLoginId || !cls || !term || !year) return res.status(400).json({ error: 'Teacher login, class, term and year required.' });
  const { data: teacher } = await db.from('users').select('id,full_name,subject').eq('login_id',teacherLoginId).eq('role','teacher').single();
  if (!teacher) return res.status(404).json({ error: 'Teacher not found: ' + teacherLoginId });
  const { data: students } = await db.from('users').select('id,login_id,full_name,stream').eq('role','student').eq('class',cls).eq('status','active').order('full_name');
  const subj = subject || teacher.subject || '';
  const { data: existing } = await db.from('report_templates').select('id').eq('teacher_login',teacherLoginId).eq('class',cls).eq('term',term).eq('year',year).eq('subject',subj).single();
  if (existing) {
    await db.from('report_templates').update({ student_count:(students||[]).length, updated_at:new Date().toISOString() }).eq('id',existing.id);
    return res.json({ success:true, id:existing.id, teacherName:teacher.full_name, studentCount:(students||[]).length, updated:true });
  }
  const { data: tmpl, error } = await db.from('report_templates').insert({ teacher_login:teacherLoginId, teacher_name:teacher.full_name, teacher_id:teacher.id, class:cls, term, year, subject:subj, student_count:(students||[]).length, status:'assigned', assigned_by:req.user.id, assigned_at:new Date().toISOString() }).select().single();
  if (error) return res.status(500).json({ error: error.message });
  res.json({ success:true, id:tmpl.id, teacherName:teacher.full_name, studentCount:(students||[]).length });
});

/* ── REPORT TEMPLATES: list ── */
app.get('/api/report-templates', requireAuth, async (req, res) => {
  let q = req.user.role === 'admin'
    ? db.from('report_templates').select('*').order('assigned_at',{ascending:false})
    : db.from('report_templates').select('*').eq('teacher_login',req.user.loginId).order('assigned_at',{ascending:false});
  const { data, error } = await q;
  if (error) return res.status(500).json({ error: error.message });
  res.json(data || []);
});

/* ── REPORT TEMPLATES: get students for a specific template (teacher downloads) ── */
app.get('/api/report-templates/:id/students', requireAuth, async (req, res) => {
  const { data: tmpl } = await db.from('report_templates').select('*').eq('id',req.params.id).single();
  if (!tmpl) return res.status(404).json({ error: 'Template not found.' });
  if (req.user.role === 'teacher' && tmpl.teacher_login !== req.user.loginId) return res.status(403).json({ error: 'Not assigned to you.' });
  const { data: students } = await db.from('users').select('id,login_id,full_name,stream').eq('role','student').eq('class',tmpl.class).eq('status','active').order('full_name');
  res.json({ template: tmpl, students: students || [] });
});

/* ── REPORT TEMPLATES: delete ── */
app.delete('/api/report-templates/:id', requireAuth, requireAdmin, async (req, res) => {
  await db.from('report_templates').delete().eq('id',req.params.id);
  res.json({ success:true });
});

/* ─────────────────────────────────────────────
   CATCH-ALL: unknown routes
───────────────────────────────────────────── */
app.use((req, res) => {
  res.status(404).json({ error: 'Not found: ' + req.method + ' ' + req.path });
});

/* ─────────────────────────────────────────────
   GLOBAL ERROR HANDLER
───────────────────────────────────────────── */
app.use((err, req, res, next) => {
  console.error('Unhandled error:', err);
  res.status(500).json({ error: 'Internal server error.' });
});

/* ─────────────────────────────────────────────
   START SERVER
───────────────────────────────────────────── */
app.listen(PORT, '0.0.0.0', () => {
  console.log('\n══════════════════════════════════════════');
  console.log(' Kawanda Secondary School — Backend v3.0');
  console.log('══════════════════════════════════════════');
  console.log(' Port       :', PORT);
  console.log(' Supabase   :', SUPABASE_URL);
  console.log(' JWT secret :', JWT_SECRET === 'KawandaSS_default_secret_change_me' ? '⚠️  using default — set JWT_SECRET env var' : '✅ custom');
  console.log('══════════════════════════════════════════\n');

  /* Seed admin 4 seconds after startup to let DB connection stabilise */
  setTimeout(seedAdmin, 4000);
});