/**
 * HR Controller
 * Handles staff roles, salary grades, records, leave, and attendance
 */

const express = require('express');
const router = express.Router();
const path = require('path');
const Database = require('better-sqlite3');
const { v4: uuidv4 } = require('uuid');

const dbPath = process.env.DB_PATH || path.join(__dirname, '../../data/school.db');
const db = new Database(dbPath);
db.pragma('foreign_keys = ON');

// Auth middleware
const auth = (req, res, next) => {
  const token = req.headers.authorization?.split(' ')[1];
  if (!token) return res.status(401).json({ error: 'No token provided' });
  try {
    const jwt = require('jsonwebtoken');
    const { jwtSecret: JWT_SECRET } = require('../config');
    req.user = jwt.verify(token, JWT_SECRET);
    next();
  } catch (err) {
    res.status(401).json({ error: 'Invalid token' });
  }
};

// HR Access — `bursar` and `accountant` are included so that the Finance
// portal's `/api/finance/payroll/*` proxy (see finance.controller.js) can
// serve the same payroll data through HR's source-of-truth routes. Without
// these roles here, the Finance proxy would 403 every bursar request.
const hrAccess = (req, res, next) => {
  const allowedRoles = ['school_admin', 'systems_admin', 'principal', 'hr', 'bursar', 'accountant'];
  if (!allowedRoles.includes(req.user.role)) {
    return res.status(403).json({ error: 'HR, Finance, Principal or Admin access required' });
  }
  next();
};

const adminOnly = (req, res, next) => {
  if (!['school_admin', 'systems_admin'].includes(req.user.role) && req.user.role !== 'principal') {
    return res.status(403).json({ error: 'Admin or Principal access required' });
  }
  next();
};

// ============ STAFF ROLES ============

/**
 * @openapi
 * /api/hr/roles:
 *   get:
 *     summary: List staff roles
 *     description: |
 *       Returns all active (non-deleted) staff roles. Open to HR,
 *       finance, bursar, accountant, principal and admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Role list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 */
router.get('/roles', auth, hrAccess, (req, res) => {
  const roles = db.prepare('SELECT * FROM staff_roles WHERE is_deleted = 0').all();
  res.json(roles);
});

/**
 * @openapi
 * /api/hr/roles:
 *   post:
 *     summary: Create staff role
 *     description: |
 *       Inserts a new row into `staff_roles` and returns its
 *       numeric `id` and UUID `uid`. Admin or principal only.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               name: { type: string }
 *               description: { type: string }
 *               is_teaching_role: { type: boolean }
 *               permissions: { type: string }
 *     responses:
 *       200:
 *         description: Created role identifiers
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 id: { type: integer }
 *                 uid: { type: string }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Principal access required }
 *       500: { description: Server error }
 */
router.post('/roles', auth, adminOnly, (req, res) => {
  const { name, description, is_teaching_role, permissions } = req.body;
  const uid = uuidv4();
  const result = db.prepare(`
    INSERT INTO staff_roles (uid, name, description, is_teaching_role, permissions, sync_status)
    VALUES (?, ?, ?, ?, ?, 'pending')
  `).run(uid, name, description, is_teaching_role ? 1 : 0, permissions);
  res.json({ id: result.lastInsertRowid, uid });
});

// ============ SALARY GRADES ============

/**
 * @openapi
 * /api/hr/salary-grades:
 *   get:
 *     summary: List salary grades
 *     description: |
 *       Returns all active salary grades (pay scales with their
 *       allowances). Open to HR, finance, bursar, accountant,
 *       principal and admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Salary grade list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 */
router.get('/salary-grades', auth, hrAccess, (req, res) => {
  const grades = db.prepare('SELECT * FROM salary_grades WHERE is_deleted = 0').all();
  res.json(grades);
});

/**
 * @openapi
 * /api/hr/salary-grades:
 *   post:
 *     summary: Create salary grade
 *     description: |
 *       Inserts a new salary grade with all allowance components
 *       and an effective date. Admin or principal only.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               name: { type: string }
 *               grade_level: { type: integer }
 *               basic_salary: { type: number }
 *               housing_allowance: { type: number }
 *               transport_allowance: { type: number }
 *               medical_allowance: { type: number }
 *               other_allowances: { type: number }
 *               description: { type: string }
 *               effective_date: { type: string, format: date }
 *     responses:
 *       200:
 *         description: Created grade identifiers
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 id: { type: integer }
 *                 uid: { type: string }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Principal access required }
 *       500: { description: Server error }
 */
router.post('/salary-grades', auth, adminOnly, (req, res) => {
  const { name, grade_level, basic_salary, housing_allowance, transport_allowance, medical_allowance, other_allowances, description, effective_date } = req.body;
  const uid = uuidv4();
  const result = db.prepare(`
    INSERT INTO salary_grades (uid, name, grade_level, basic_salary, housing_allowance, transport_allowance, medical_allowance, other_allowances, description, effective_date, sync_status)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending')
  `).run(uid, name, grade_level, basic_salary, housing_allowance, transport_allowance, medical_allowance, other_allowances, description, effective_date);
  res.json({ id: result.lastInsertRowid, uid });
});

// ============ STAFF RECORDS ============

/**
 * @openapi
 * /api/hr/records:
 *   get:
 *     summary: List staff records
 *     description: |
 *       Returns all active staff records joined with their user,
 *       role, and salary grade. Open to HR, finance, bursar,
 *       accountant, principal and admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Staff record list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 */
router.get('/records', auth, hrAccess, (req, res) => {
  const records = db.prepare(`
    SELECT sr.*, u.first_name, u.last_name, u.email, r.name as role_name, sg.name as grade_name
    FROM staff_records sr
    JOIN users u ON sr.user_id = u.id
    LEFT JOIN staff_roles r ON sr.role_id = r.id
    LEFT JOIN salary_grades sg ON sr.salary_grade_id = sg.id
    WHERE sr.is_deleted = 0
  `).all();
  res.json(records);
});

/**
 * @openapi
 * /api/hr/records:
 *   post:
 *     summary: Create staff record
 *     description: |
 *       Creates a staff record linking a user to a role, salary
 *       grade, and employment details. `employment_type` defaults
 *       to `full-time`. Open to HR, finance, bursar, accountant,
 *       principal and admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - user_id
 *             properties:
 *               user_id: { type: integer }
 *               staff_number: { type: string }
 *               role_id: { type: integer }
 *               salary_grade_id: { type: integer }
 *               employment_type: { type: string }
 *               appointment_date: { type: string, format: date }
 *               bank_name: { type: string }
 *               account_number: { type: string }
 *               department: { type: string }
 *               designation: { type: string }
 *     responses:
 *       200:
 *         description: Created record identifiers
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 id: { type: integer }
 *                 uid: { type: string }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 *       500: { description: Server error }
 */
router.post('/records', auth, hrAccess, (req, res) => {
  const { user_id, staff_number, role_id, salary_grade_id, employment_type, appointment_date, bank_name, account_number, department, designation } = req.body;
  const uid = uuidv4();
  const normalizedType = (employment_type || 'full-time').toLowerCase();
  const result = db.prepare(`
    INSERT INTO staff_records (uid, user_id, staff_number, role_id, salary_grade_id, employment_type, appointment_date, bank_name, account_number, department, designation, sync_status)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending')
  `).run(uid, user_id, staff_number, role_id, salary_grade_id, normalizedType, appointment_date, bank_name, account_number, department, designation);
  res.json({ id: result.lastInsertRowid, uid });
});

/**
 * @openapi
 * /api/hr/records/{id}:
 *   put:
 *     summary: Update staff record
 *     description: |
 *       Updates mutable fields on a staff record by numeric `id`.
 *       Open to HR, finance, bursar, accountant, principal and
 *       admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *         description: Numeric staff record ID
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               role_id: { type: integer }
 *               salary_grade_id: { type: integer }
 *               employment_type: { type: string }
 *               bank_name: { type: string }
 *               account_number: { type: string }
 *               department: { type: string }
 *               designation: { type: string }
 *               is_active: { type: boolean }
 *     responses:
 *       200:
 *         description: Updated
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 *       500: { description: Server error }
 */
router.put('/records/:id', auth, hrAccess, (req, res) => {
  try {
    const { role_id, salary_grade_id, employment_type, bank_name, account_number, department, designation, is_active } = req.body;
    const normalizedType = (employment_type || 'full-time').toLowerCase();
    db.prepare(`
      UPDATE staff_records
      SET role_id = ?, salary_grade_id = ?, employment_type = ?, bank_name = ?, account_number = ?, department = ?, designation = ?, is_active = ?, updated_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
      WHERE id = ?
    `).run(role_id ? parseInt(role_id) : null, salary_grade_id ? parseInt(salary_grade_id) : null, normalizedType, bank_name, account_number, department, designation, is_active ? 1 : 0, req.params.id);
    res.json({ success: true });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

/**
 * @openapi
 * /api/hr/records/{id}:
 *   delete:
 *     summary: Soft-delete staff record
 *     description: |
 *       Sets `is_deleted = 1` on the staff record by numeric `id`.
 *       Open to HR, finance, bursar, accountant, principal and
 *       admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *         description: Numeric staff record ID
 *     responses:
 *       200:
 *         description: Deleted
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 *       500: { description: Server error }
 */
router.delete('/records/:id', auth, hrAccess, (req, res) => {
  try {
    db.prepare(`
      UPDATE staff_records
      SET is_deleted = 1, updated_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
      WHERE id = ?
    `).run(req.params.id);
    res.json({ success: true });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// ============ LEAVE ============

/**
 * @openapi
 * /api/hr/leave-types:
 *   get:
 *     summary: List leave types
 *     description: |
 *       Returns all active leave types (e.g. annual, sick, study).
 *       Open to HR, finance, bursar, accountant, principal and
 *       admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Leave type list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 */
router.get('/leave-types', auth, (req, res) => {
  const types = db.prepare('SELECT * FROM leave_types WHERE is_deleted = 0').all();
  res.json(types);
});

/**
 * @openapi
 * /api/hr/leave-requests:
 *   get:
 *     summary: List leave requests
 *     description: |
 *       Returns leave requests joined with the staff user and leave
 *       type. Privileged roles (`school_admin`, `systems_admin`,
 *       `principal`, `hr`) see every active request; other staff
 *       members see only their own.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Leave request list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/leave-requests', auth, (req, res) => {
  const isPrivileged = ['school_admin', 'systems_admin', 'principal', 'hr'].includes(req.user.role);

  // Sprint 1b — B11: support `?status=` filter (e.g. status=pending for the
  // leave-approval queue). Unprivileged callers still get scoped to their own
  // requests; the filter applies on top of that scope.
  const statusFilter = typeof req.query.status === 'string' && req.query.status.trim()
    ? req.query.status.trim()
    : null;

  const wheres = ['lr.is_deleted = 0'];
  const params = [];

  if (!isPrivileged) {
    wheres.push('lr.staff_id = ?');
    params.push(req.user.id);
  }
  if (statusFilter) {
    wheres.push('lr.status = ?');
    params.push(statusFilter);
  }
  const whereClause = wheres.length ? `WHERE ${wheres.join(' AND ')}` : '';

  const query = isPrivileged
    ? `SELECT lr.*, u.first_name, u.last_name, lt.name as leave_type
       FROM leave_requests lr
       JOIN users u ON lr.staff_id = u.id
       JOIN leave_types lt ON lr.leave_type_id = lt.id
       ${whereClause}
       ORDER BY lr.start_date DESC`
    : `SELECT lr.*, lt.name as leave_type
       FROM leave_requests lr
       JOIN leave_types lt ON lr.leave_type_id = lt.id
       ${whereClause}
       ORDER BY lr.start_date DESC`;

  const requests = db.prepare(query).all(...params);
  res.json(requests);
});

/**
 * @openapi
 * /api/hr/leave-requests:
 *   post:
 *     summary: Submit leave request
 *     description: |
 *       Inserts a new leave request on behalf of the calling user.
 *       The staff `id` is always taken from the JWT (`req.user.id`).
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - leave_type_id
 *               - start_date
 *               - end_date
 *             properties:
 *               leave_type_id: { type: integer }
 *               start_date: { type: string, format: date }
 *               end_date: { type: string, format: date }
 *               days_count: { type: number }
 *               reason: { type: string }
 *     responses:
 *       200:
 *         description: Created request identifiers
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 id: { type: integer }
 *                 uid: { type: string }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.post('/leave-requests', auth, (req, res) => {
  const { leave_type_id, start_date, end_date, days_count, reason } = req.body;
  const uid = uuidv4();
  const result = db.prepare(`
    INSERT INTO leave_requests (uid, staff_id, leave_type_id, start_date, end_date, days_count, reason, sync_status)
    VALUES (?, ?, ?, ?, ?, ?, ?, 'pending')
  `).run(uid, req.user.id, leave_type_id, start_date, end_date, days_count, reason);
  res.json({ id: result.lastInsertRowid, uid });
});

/**
 * @openapi
 * /api/hr/leave-requests/{uid}/status:
 *   put:
 *     summary: Approve or reject a leave request
 *     description: |
 *       Updates the `status` (and optionally `rejection_reason`)
 *       on a leave request identified by its UUID `uid` (NOT the
 *       numeric id). Records the approver from the JWT and stamps
 *       `approved_at`. Open to HR, finance, bursar, accountant,
 *       principal and admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: uid
 *         required: true
 *         schema: { type: string, format: uuid }
 *         description: Leave request UUID (not numeric id)
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - status
 *             properties:
 *               status:
 *                 type: string
 *                 description: New leave status (e.g. "approved", "rejected")
 *               rejection_reason: { type: string }
 *     responses:
 *       200:
 *         description: Updated
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 *       500: { description: Server error }
 */
router.put('/leave-requests/:uid/status', auth, hrAccess, (req, res) => {
  const { status, rejection_reason } = req.body;
  db.prepare(`
    UPDATE leave_requests 
    SET status = ?, rejection_reason = ?, approved_by = ?, approved_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
    WHERE uid = ?
  `).run(status, rejection_reason, req.user.id, req.params.uid);
  res.json({ success: true });
});

// ============ ATTENDANCE ============

/**
 * @openapi
 * /api/hr/attendance:
 *   get:
 *     summary: List staff attendance for a date
 *     description: |
 *       Returns attendance rows joined with staff names for the
 *       requested `date` (defaults to today, local time). Open to
 *       HR, finance, bursar, accountant, principal and admin
 *       roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: date
 *         schema: { type: string, format: date }
 *         description: ISO date (defaults to today)
 *     responses:
 *       200:
 *         description: Attendance list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 */
router.get('/attendance', auth, hrAccess, (req, res) => {
  const { date } = req.query;
  const records = db.prepare(`
    SELECT sa.*, u.first_name, u.last_name
    FROM staff_attendance sa
    JOIN users u ON sa.staff_id = u.id
    WHERE sa.date = ? AND sa.is_deleted = 0
  `).all(date || new Date().toISOString().split('T')[0]);
  res.json(records);
});

/**
 * @openapi
 * /api/hr/attendance:
 *   post:
 *     summary: Mark staff attendance
 *     description: |
 *       Inserts an attendance row for a staff member on a given
 *       date. `marked_by` is set from the JWT. Open to HR, finance,
 *       bursar, accountant, principal and admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - staff_id
 *               - status
 *             properties:
 *               staff_id: { type: integer }
 *               date: { type: string, format: date }
 *               status: { type: string }
 *               remarks: { type: string }
 *     responses:
 *       200:
 *         description: Created attendance identifiers
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 id: { type: integer }
 *                 uid: { type: string }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 *       500: { description: Server error }
 */
router.post('/attendance', auth, hrAccess, (req, res) => {
  const { staff_id, date, status, remarks } = req.body;
  const uid = uuidv4();
  const result = db.prepare(`
    INSERT INTO staff_attendance (uid, staff_id, date, status, remarks, marked_by, sync_status)
    VALUES (?, ?, ?, ?, ?, ?, 'pending')
  `).run(uid, staff_id, date, status, remarks, req.user.id);
  res.json({ id: result.lastInsertRowid, uid });
});

// ============ PAYROLL ============

/**
 * @openapi
 * /api/hr/payroll-runs:
 *   get:
 *     summary: List payroll runs
 *     description: |
 *       Returns all non-deleted payroll runs ordered by period
 *       descending. Open to HR, finance, bursar, accountant,
 *       principal and admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Payroll run list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 */
router.get('/payroll-runs', auth, hrAccess, (req, res) => {
  const runs = db.prepare('SELECT * FROM payroll_runs WHERE is_deleted = 0 ORDER BY period_year DESC, period_month DESC').all();
  res.json(runs);
});

/**
 * @openapi
 * /api/hr/payroll-runs:
 *   post:
 *     summary: Create payroll run
 *     description: |
 *       Inserts a draft payroll run for a given `period_month`
 *       and `period_year`. The creator is taken from the JWT.
 *       Open to HR, finance, bursar, accountant, principal and
 *       admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - period_month
 *               - period_year
 *             properties:
 *               period_month: { type: integer, minimum: 1, maximum: 12 }
 *               period_year: { type: integer }
 *               description: { type: string }
 *     responses:
 *       200:
 *         description: Created run identifiers
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 id: { type: integer }
 *                 uid: { type: string }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 *       500: { description: Server error }
 */
router.post('/payroll-runs', auth, hrAccess, (req, res) => {
  const { period_month, period_year, description } = req.body;
  const uid = uuidv4();
  
  const result = db.prepare(`
    INSERT INTO payroll_runs (uid, period_month, period_year, description, status, created_by, sync_status)
    VALUES (?, ?, ?, ?, 'draft', ?, 'pending')
  `).run(uid, period_month, period_year, description, req.user.id);
  
  res.json({ id: result.lastInsertRowid, uid });
});

/**
 * @openapi
 * /api/hr/payroll-runs/{id}/calculate:
 *   post:
 *     summary: Calculate payroll run
 *     description: |
 *       Deletes any existing payslips for the run, then generates
 *       one payslip per active staff record using their salary
 *       grade's allowances. Tax is applied at a flat 15% of gross
 *       and pension at 5% of basic salary (demo calculation).
 *       Updates the run status to `calculated`. Open to HR, finance,
 *       bursar, accountant, principal and admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *         description: Numeric payroll run ID
 *     responses:
 *       200:
 *         description: Number of payslips generated
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *                 count: { type: integer }
 *       404: { description: Payroll run not found }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 *       500: { description: Server error }
 */
router.post('/payroll-runs/:id/calculate', auth, hrAccess, (req, res) => {
  const runId = req.params.id;
  const run = db.prepare('SELECT * FROM payroll_runs WHERE id = ?').get(runId);
  
  if (!run) return res.status(404).json({ error: 'Payroll run not found' });
  
  // Clear existing payslips for this run
  db.prepare('DELETE FROM payslips WHERE payroll_run_id = ?').run(runId);
  
  // Get all active staff records with their salary grades
  const staff = db.prepare(`
    SELECT sr.*, sg.basic_salary, sg.housing_allowance, sg.transport_allowance, sg.medical_allowance, sg.other_allowances
    FROM staff_records sr
    JOIN salary_grades sg ON sr.salary_grade_id = sg.id
    WHERE sr.is_active = 1 AND sr.is_deleted = 0
  `).all();
  
  let totalGross = 0;
  let totalDeductions = 0;
  let totalNet = 0;
  
  const insertPayslip = db.prepare(`
    INSERT INTO payslips (
      uid, staff_id, payroll_run_id, basic_salary, housing_allowance, 
      transport_allowance, medical_allowance, other_allowances, gross_salary,
      tax_deduction, pension_deduction, other_deductions, total_deductions, net_salary, sync_status
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending')
  `);
  
  staff.forEach(s => {
    const gross = s.basic_salary + s.housing_allowance + s.transport_allowance + s.medical_allowance + s.other_allowances;
    // Simple tax calculation (e.g., 15% flat for demo)
    const tax = gross * 0.15;
    const pension = s.basic_salary * 0.05;
    const deductions = tax + pension;
    const net = gross - deductions;
    
    totalGross += gross;
    totalDeductions += deductions;
    totalNet += net;
    
    insertPayslip.run(uuidv4(), s.user_id, runId, s.basic_salary, s.housing_allowance, s.transport_allowance, s.medical_allowance, s.other_allowances, gross, tax, pension, 0, deductions, net);
  });
  
  db.prepare(`
    UPDATE payroll_runs 
    SET total_gross = ?, total_deductions = ?, total_net = ?, status = 'calculated', updated_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
    WHERE id = ?
  `).run(totalGross, totalDeductions, totalNet, runId);
  
  res.json({ success: true, count: staff.length });
});

/**
 * @openapi
 * /api/hr/payroll-runs/{id}/pay:
 *   post:
 *     summary: Mark payroll run as paid
 *     description: |
 *       Sets the run status to `paid` (with `paid_at`) and cascades
 *       `paid` to all of its payslips. Open to HR, finance, bursar,
 *       accountant, principal and admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *         description: Numeric payroll run ID
 *     responses:
 *       200:
 *         description: Marked paid
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *       404: { description: Payroll run not found }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 *       500: { description: Server error }
 */
router.post('/payroll-runs/:id/pay', auth, hrAccess, (req, res) => {
  try {
    const runId = req.params.id;
    const run = db.prepare('SELECT * FROM payroll_runs WHERE id = ?').get(runId);
    if (!run) return res.status(404).json({ error: 'Payroll run not found' });
    
    db.prepare(`
      UPDATE payroll_runs 
      SET status = 'paid', paid_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
      WHERE id = ?
    `).run(runId);

    db.prepare(`
      UPDATE payslips
      SET status = 'paid', paid_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
      WHERE payroll_run_id = ?
    `).run(runId);
    
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

/**
 * @openapi
 * /api/hr/payroll-runs/{id}/cancel:
 *   post:
 *     summary: Cancel payroll run
 *     description: |
 *       Refuses if the run is already `paid`. Otherwise deletes
 *       calculated payslips and resets the run to `draft` with
 *       zero totals. Open to HR, finance, bursar, accountant,
 *       principal and admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *         description: Numeric payroll run ID
 *     responses:
 *       200:
 *         description: Cancelled
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *       400: { description: Cannot cancel a paid payroll run }
 *       404: { description: Payroll run not found }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 *       500: { description: Server error }
 */
router.post('/payroll-runs/:id/cancel', auth, hrAccess, (req, res) => {
  try {
    const runId = req.params.id;
    const run = db.prepare('SELECT * FROM payroll_runs WHERE id = ?').get(runId);
    if (!run) return res.status(404).json({ error: 'Payroll run not found' });
    if (run.status === 'paid') {
      return res.status(400).json({ error: 'Cannot cancel a paid payroll run' });
    }
    
    // Clear calculated payslips
    db.prepare('DELETE FROM payslips WHERE payroll_run_id = ?').run(runId);
    
    // Reset run status to draft and totals to 0
    db.prepare(`
      UPDATE payroll_runs
      SET status = 'draft', total_gross = 0, total_deductions = 0, total_net = 0, updated_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
      WHERE id = ?
    `).run(runId);
    
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

/**
 * @openapi
 * /api/hr/payslips:
 *   get:
 *     summary: List payslips
 *     description: |
 *       Returns payslips joined with the staff user, their staff
 *       record, and the parent payroll run. Privileged roles
 *       (`school_admin`, `systems_admin`, `principal`, `hr`,
 *       `bursar`, `accountant`) see all payslips and may filter
 *       by `payroll_run_id`; other callers only see their own.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: payroll_run_id
 *         schema: { type: integer }
 *         description: Filter to a single payroll run (privileged callers only)
 *     responses:
 *       200:
 *         description: Payslip list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/payslips', auth, (req, res) => {
  const isPrivileged = ['school_admin', 'systems_admin', 'principal', 'hr', 'bursar', 'accountant'].includes(req.user.role);
  const { payroll_run_id } = req.query;

  let query = `
    SELECT p.*, u.first_name, u.last_name, sr.staff_number, pr.period_month, pr.period_year
    FROM payslips p
    JOIN users u ON p.staff_id = u.id
    JOIN staff_records sr ON u.id = sr.user_id
    JOIN payroll_runs pr ON p.payroll_run_id = pr.id
    WHERE p.is_deleted = 0
  `;
  const params = [];

  if (!isPrivileged) {
    query += ' AND p.staff_id = ?';
    params.push(req.user.id);
  } else if (payroll_run_id) {
    query += ' AND p.payroll_run_id = ?';
    params.push(payroll_run_id);
  }

  const payslips = db.prepare(query).all(...params);
  res.json(payslips);
});

// ============ PAYSLIP DETAIL (Phase 1 — B7) ============
// Single payslip with staff name + payroll_run.period joined.
// RBAC: hrAccess (Phase 0 widened list). Privacy: if the caller is the
// payslip's own staff member, allow self-view regardless of role; otherwise
// the caller must be in the privileged set or they get 403.

/**
 * @openapi
 * /api/hr/payslips/{id}:
 *   get:
 *     summary: Get a single payslip
 *     description: |
 *       Returns the payslip joined with staff name/email, staff
 *       number, department, designation, and the parent payroll
 *       run's period. A non-privileged caller may only view their
 *       own payslips (returns 403 otherwise). Open to HR, finance,
 *       bursar, accountant, principal and admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *         description: Numeric payslip ID
 *     responses:
 *       200:
 *         description: Payslip detail
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Invalid payslip id }
 *       401: { description: Unauthorized }
 *       403: { description: You may only view your own payslips }
 *       404: { description: Payslip not found }
 *       500: { description: Server error }
 */
router.get('/payslips/:id', auth, hrAccess, (req, res) => {
  try {
    const payslipId = parseInt(req.params.id, 10);
    if (Number.isNaN(payslipId)) {
      return res.status(400).json({ error: 'Invalid payslip id' });
    }

    const payslip = db.prepare(`
      SELECT
        p.*,
        u.first_name,
        u.last_name,
        u.email,
        sr.staff_number,
        sr.department,
        sr.designation,
        pr.period_month,
        pr.period_year,
        pr.id AS run_id,
        (pr.period_year || '-' || printf('%02d', pr.period_month)) AS period
      FROM payslips p
      JOIN users u ON p.staff_id = u.id
      LEFT JOIN staff_records sr ON u.id = sr.user_id
      JOIN payroll_runs pr ON p.payroll_run_id = pr.id
      WHERE p.id = ? AND p.is_deleted = 0
    `).get(payslipId);

    if (!payslip) {
      return res.status(404).json({ error: 'Payslip not found' });
    }

    // Privacy gate: non-privileged caller can only view their own payslip.
    const privileged = ['school_admin', 'systems_admin', 'principal', 'hr', 'bursar', 'accountant']
      .includes(req.user.role);
    if (!privileged && payslip.staff_id !== req.user.id) {
      return res.status(403).json({ error: 'You may only view your own payslips' });
    }

    res.json(payslip);
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// ============ HR DASHBOARD (Phase 1 — B1) ============
// Aggregates: headcount (active staff), open leave requests, latest payroll
// run, and today's attendance percent (staff_attendance). Returns 0 for
// attendance_today_pct if no rows exist for today.

/**
 * @openapi
 * /api/hr/dashboard/summary:
 *   get:
 *     summary: HR dashboard summary
 *     description: |
 *       Aggregates four tiles for the HR landing page: active staff
 *       headcount, number of open (pending) leave requests, the
 *       most recent payroll run (with its period and status), and
 *       today's attendance percentage (counts `present`, `late`
 *       and `half-day` as attended). Returns `0` for attendance
 *       when no rows are logged today. Open to HR, finance,
 *       bursar, accountant, principal and admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: HR dashboard tiles
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 headcount: { type: integer }
 *                 open_leave_requests: { type: integer }
 *                 last_payroll_run:
 *                   type: object
 *                   nullable: true
 *                   properties:
 *                     id: { type: integer }
 *                     period: { type: string }
 *                     status: { type: string }
 *                 attendance_today_pct: { type: number }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 *       500: { description: Server error }
 */
router.get('/dashboard/summary', auth, hrAccess, (_req, res) => {
  try {
    // headcount — active staff records
    const headcountRow = db.prepare(
      `SELECT COUNT(*) AS n FROM staff_records WHERE is_deleted = 0 AND is_active = 1`
    ).get();

    // open_leave_requests — status = 'pending'
    const openLeaveRow = db.prepare(
      `SELECT COUNT(*) AS n FROM leave_requests WHERE is_deleted = 0 AND status = 'pending'`
    ).get();

    // last_payroll_run — most recent non-deleted row
    const lastRunRow = db.prepare(
      `SELECT id, period_month, period_year, status
       FROM payroll_runs
       WHERE is_deleted = 0
       ORDER BY period_year DESC, period_month DESC, id DESC
       LIMIT 1`
    ).get();

    const last_payroll_run = lastRunRow
      ? {
          id: lastRunRow.id,
          period: `${lastRunRow.period_year}-${String(lastRunRow.period_month).padStart(2, '0')}`,
          status: lastRunRow.status,
        }
      : null;

    // attendance_today_pct — present + late + half-day counts as "present-ish";
    // returns 0 when no rows logged today (avoids NaN).
    const attRow = db.prepare(
      `SELECT
         COUNT(*) AS total,
         SUM(CASE WHEN status IN ('present','late','half-day') THEN 1 ELSE 0 END) AS attended
       FROM staff_attendance
       WHERE date = date('now', 'localtime') AND is_deleted = 0`
    ).get();
    const attendance_today_pct = attRow && attRow.total > 0
      ? Math.round((attRow.attended / attRow.total) * 1000) / 10
      : 0;

    res.json({
      headcount: headcountRow ? headcountRow.n : 0,
      open_leave_requests: openLeaveRow ? openLeaveRow.n : 0,
      last_payroll_run,
      attendance_today_pct,
    });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// ============ RECRUITMENT ============

/**
 * @openapi
 * /api/hr/vacancies:
 *   get:
 *     summary: List vacancies
 *     description: |
 *       Returns all non-deleted vacancies ordered by `created_at`
 *       descending. Returns `[]` on error so the UI can render an
 *       empty state. Open to HR, finance, bursar, accountant,
 *       principal and admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Vacancy list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 */
router.get('/vacancies', auth, hrAccess, (req, res) => {
  try {
    // Sprint 1b — B11: support `?status=` filter (open / closed / on_hold).
    const statusFilter = typeof req.query.status === 'string' && req.query.status.trim()
      ? req.query.status.trim()
      : null;

    let sql = 'SELECT * FROM vacancies WHERE is_deleted = 0';
    const params = [];
    if (statusFilter) {
      sql += ' AND status = ?';
      params.push(statusFilter);
    }
    sql += ' ORDER BY created_at DESC';

    const vacancies = db.prepare(sql).all(...params);
    res.json(vacancies);
  } catch (e) {
    res.json([]);
  }
});

/**
 * @openapi
 * /api/hr/vacancies:
 *   post:
 *     summary: Create vacancy
 *     description: |
 *       Inserts a new vacancy in `open` status. Open to HR,
 *       finance, bursar, accountant, principal and admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - title
 *             properties:
 *               title: { type: string }
 *               description: { type: string }
 *               requirements: { type: string }
 *               department: { type: string }
 *               closing_date: { type: string, format: date }
 *     responses:
 *       200:
 *         description: Created vacancy identifiers
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 id: { type: integer }
 *                 uid: { type: string }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 *       500: { description: Server error }
 */
router.post('/vacancies', auth, hrAccess, (req, res) => {
  try {
    const { title, description, requirements, department, closing_date } = req.body;
    const uid = uuidv4();
    const result = db.prepare(`
      INSERT INTO vacancies (uid, title, description, requirements, department, status, closing_date, sync_status)
      VALUES (?, ?, ?, ?, ?, 'open', ?, 'pending')
    `).run(uid, title, description, requirements, department, closing_date);
    res.json({ id: result.lastInsertRowid, uid });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

/**
 * @openapi
 * /api/hr/vacancies/{id}:
 *   put:
 *     summary: Update vacancy
 *     description: |
 *       Replaces editable fields on a vacancy by numeric `id`.
 *       Open to HR, finance, bursar, accountant, principal and
 *       admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *         description: Numeric vacancy ID
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               title: { type: string }
 *               description: { type: string }
 *               requirements: { type: string }
 *               department: { type: string }
 *               closing_date: { type: string, format: date }
 *     responses:
 *       200:
 *         description: Updated
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 *       500: { description: Server error }
 */
router.put('/vacancies/:id', auth, hrAccess, (req, res) => {
  try {
    const { title, description, requirements, department, closing_date } = req.body;
    db.prepare(`
      UPDATE vacancies
      SET title = ?, description = ?, requirements = ?, department = ?, closing_date = ?, updated_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
      WHERE id = ?
    `).run(title, description, requirements, department, closing_date, req.params.id);
    res.json({ success: true });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

/**
 * @openapi
 * /api/hr/vacancies/{id}/status:
 *   put:
 *     summary: Change vacancy status
 *     description: |
 *       Updates the `status` column on a vacancy by numeric `id`
 *       (e.g. `open`, `closed`, `on-hold`). Open to HR, finance,
 *       bursar, accountant, principal and admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *         description: Numeric vacancy ID
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - status
 *             properties:
 *               status: { type: string }
 *     responses:
 *       200:
 *         description: Updated
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 *       500: { description: Server error }
 */
router.put('/vacancies/:id/status', auth, hrAccess, (req, res) => {
  try {
    const { status } = req.body;
    db.prepare(`
      UPDATE vacancies
      SET status = ?, updated_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
      WHERE id = ?
    `).run(status, req.params.id);
    res.json({ success: true });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

/**
 * @openapi
 * /api/hr/vacancies/{id}:
 *   delete:
 *     summary: Soft-delete vacancy
 *     description: |
 *       Sets `is_deleted = 1` on a vacancy by numeric `id`. Open
 *       to HR, finance, bursar, accountant, principal and admin
 *       roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *         description: Numeric vacancy ID
 *     responses:
 *       200:
 *         description: Deleted
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 *       500: { description: Server error }
 */
router.delete('/vacancies/:id', auth, hrAccess, (req, res) => {
  try {
    db.prepare(`
      UPDATE vacancies
      SET is_deleted = 1, updated_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
      WHERE id = ?
    `).run(req.params.id);
    res.json({ success: true });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

/**
 * @openapi
 * /api/hr/applicants:
 *   post:
 *     summary: Create applicant
 *     description: |
 *       Inserts a new applicant (with optional `vacancy_id`) in
 *       `pending` status. Open to HR, finance, bursar, accountant,
 *       principal and admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - first_name
 *               - last_name
 *             properties:
 *               vacancy_id: { type: integer }
 *               first_name: { type: string }
 *               last_name: { type: string }
 *               email: { type: string }
 *               phone: { type: string }
 *               resume_url: { type: string }
 *               notes: { type: string }
 *     responses:
 *       200:
 *         description: Created applicant identifiers
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 id: { type: integer }
 *                 uid: { type: string }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 *       500: { description: Server error }
 */
router.post('/applicants', auth, hrAccess, (req, res) => {
  try {
    const { vacancy_id, first_name, last_name, email, phone, resume_url, notes } = req.body;
    const uid = uuidv4();
    const result = db.prepare(`
      INSERT INTO applicants (uid, vacancy_id, first_name, last_name, email, phone, resume_url, status, notes, sync_status)
      VALUES (?, ?, ?, ?, ?, ?, ?, 'pending', ?, 'pending')
    `).run(uid, vacancy_id ? parseInt(vacancy_id) : null, first_name, last_name, email, phone, resume_url || null, notes || null);
    res.json({ id: result.lastInsertRowid, uid });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

/**
 * @openapi
 * /api/hr/applicants:
 *   get:
 *     summary: List applicants
 *     description: |
 *       Returns all non-deleted applicants joined with the
 *       vacancy title, ordered by `created_at` descending.
 *       Returns `[]` on error so the UI can render an empty state.
 *       Open to HR, finance, bursar, accountant, principal and
 *       admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Applicant list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 */
router.get('/applicants', auth, hrAccess, (req, res) => {
  try {
    const applicants = db.prepare(`
      SELECT a.*, v.title as vacancy_title
      FROM applicants a
      LEFT JOIN vacancies v ON a.vacancy_id = v.id
      WHERE a.is_deleted = 0
      ORDER BY a.created_at DESC
    `).all();
    res.json(applicants);
  } catch (e) {
    res.json([]);
  }
});

/**
 * @openapi
 * /api/hr/applicants/{id}/status:
 *   put:
 *     summary: Update applicant status
 *     description: |
 *       Updates `status` (and optionally `interview_date` and
 *       `notes`) on an applicant by numeric `id`. Open to HR,
 *       finance, bursar, accountant, principal and admin roles.
 *     tags:
 *       - HR
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *         description: Numeric applicant ID
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - status
 *             properties:
 *               status: { type: string }
 *               interview_date: { type: string, format: date }
 *               notes: { type: string }
 *     responses:
 *       200:
 *         description: Updated
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *       401: { description: Unauthorized }
 *       403: { description: HR, Finance, Principal or Admin access required }
 *       500: { description: Server error }
 */
router.put('/applicants/:id/status', auth, hrAccess, (req, res) => {
  try {
    const { status, interview_date, notes } = req.body;
    db.prepare(`
      UPDATE applicants
      SET status = ?, interview_date = COALESCE(?, interview_date), notes = COALESCE(?, notes), updated_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
      WHERE id = ?
    `).run(status, interview_date, notes, req.params.id);
    res.json({ success: true });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

module.exports = router;
