/**
 * Front Office Controller
 * Handles Visitor Logs, Admission Enquiries, Complaints, Phone Call Logs,
 * and Postal Dispatch / Receipts.
 *
 * All writes set sync_status = 'pending'. Reads include WHERE is_deleted = 0.
 * Soft delete: UPDATE … SET is_deleted = 1, updated_at = datetime(\'now\', \'localtime\'),
 *   sync_status = 'pending' WHERE id = ? — never a real DELETE.
 *
 * Role check: 'school_admin' is the canonical admin role in this file
 * (matches existing visitor / enquiry / complaint handlers). 'principal' and
 * 'teacher' get write access to phone-calls (they often pick up the front
 * desk); only school_admin can write postal-dispatch (it's an audit trail).
 */

const express = require('express');
const router = express.Router();
const { v4: uuidv4 } = require('uuid');

// Database path
const dbPath = process.env.DB_PATH || require('path').join(__dirname, '../../data/school.db');
const Database = require('better-sqlite3');
let db;

const getDb = () => {
  if (!db) {
    db = new Database(dbPath);
    db.pragma('foreign_keys = ON');
  }
  return db;
};

// 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' });
  }
};

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

// Staff write access (front-desk can include admin / principal / teacher)
const staffWrite = (req, res, next) => {
  if (!['school_admin', 'systems_admin', 'principal', 'teacher'].includes(req.user.role)) {
    return res.status(403).json({ error: 'Staff access required' });
  }
  next();
};

// ============ VISITOR LOGS ============

// List all visitor logs
/**
 * @openapi
 * /api/front-office/visitors:
 *   get:
 *     summary: List visitor log entries
 *     description: |
 *       Returns every visitor log entry that is not soft-deleted,
 *       joined with the creating user's display name and ordered by
 *       most recent check-in first. Requires authentication.
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Visitor log list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Log a new visitor
 *     description: |
 *       Records a visitor check-in. `visitor_name` is required;
 *       all other fields are optional. `created_by` is set to the
 *       authenticated user's id. Requires authentication.
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - visitor_name
 *             properties:
 *               visitor_name: { type: string }
 *               visitor_type: { type: string }
 *               phone: { type: string }
 *               email: { type: string }
 *               id_number: { type: string }
 *               purpose: { type: string }
 *               person_to_visit: { type: string }
 *               badge_number: { type: string }
 *               remarks: { type: string }
 *     responses:
 *       201:
 *         description: Created visitor log
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: visitor_name is required }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.get('/visitors', auth, (req, res) => {
  try {
    const logs = getDb().prepare(`
      SELECT v.*, u.first_name || ' ' || u.last_name as creator_name
      FROM visitor_logs v
      LEFT JOIN users u ON v.created_by = u.id
      WHERE v.is_deleted = 0
      ORDER BY v.check_in DESC
    `).all();
    res.json(logs);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Create visitor log
router.post('/visitors', auth, (req, res) => {
  try {
    const { 
      visitor_name, visitor_type, phone, email, id_number, 
      purpose, person_to_visit, badge_number, remarks 
    } = req.body;
    
    if (!visitor_name) {
      return res.status(400).json({ error: 'Visitor name is required' });
    }
    
    const uid = uuidv4();
    const result = getDb().prepare(`
      INSERT INTO visitor_logs (
        uid, visitor_name, visitor_type, phone, email, id_number, 
        purpose, person_to_visit, badge_number, remarks, created_by
      )
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(uid, visitor_name, visitor_type, phone, email, id_number, purpose, person_to_visit, badge_number, remarks, req.user.id);
    
    const log = getDb().prepare('SELECT * FROM visitor_logs WHERE id = ?').get(result.lastInsertRowid);
    res.status(201).json(log);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Check out visitor
/**
 * @openapi
 * /api/front-office/visitors/{id}/checkout:
 *   put:
 *     summary: Check out a visitor
 *     description: |
 *       Sets `check_out` to the current local time on the visitor
 *       log entry, flags the row as `pending` for sync, and returns
 *       a success flag. Requires authentication.
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Checked out
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.put('/visitors/:id/checkout', auth, (req, res) => {
  try {
    getDb().prepare(`
      UPDATE visitor_logs 
      SET check_out = datetime(\'now\', \'localtime\'), updated_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(req.params.id);
    res.json({ success: true });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ ADMISSION ENQUIRIES ============

// List all admission enquiries
/**
 * @openapi
 * /api/front-office/enquiries:
 *   get:
 *     summary: List admission enquiries
 *     description: |
 *       Returns non-deleted admission enquiries joined with the
 *       assigned counselor's display name, ordered by most recent
 *       enquiry date first. Requires authentication.
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Enquiry list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Create an admission enquiry
 *     description: |
 *       Creates a new admission enquiry. `full_name` and `phone`
 *       are required; empty strings for optional fields are
 *       normalised to `null`. `assigned_to` is parsed as an
 *       integer user id when provided. Requires authentication.
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - full_name
 *               - phone
 *             properties:
 *               full_name: { type: string }
 *               email: { type: string }
 *               phone: { type: string }
 *               address: { type: string }
 *               class_interested: { type: string }
 *               source: { type: string }
 *               follow_up_date: { type: string, format: date }
 *               notes: { type: string }
 *               next_action: { type: string }
 *               assigned_to: { type: integer }
 *     responses:
 *       201:
 *         description: Created enquiry
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: full_name and phone are required }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.get('/enquiries', auth, (req, res) => {
  try {
    const enquiries = getDb().prepare(`
      SELECT e.*, u.first_name || ' ' || u.last_name as assigned_to_name
      FROM admission_enquiries e
      LEFT JOIN users u ON e.assigned_to = u.id
      WHERE e.is_deleted = 0
      ORDER BY e.enquiry_date DESC
    `).all();
    res.json(enquiries);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Create enquiry
router.post('/enquiries', auth, (req, res) => {
  try {
    const { 
      full_name, email, phone, address, class_interested, 
      source, follow_up_date, notes, next_action, assigned_to 
    } = req.body;
    
    if (!full_name || !phone) {
      return res.status(400).json({ error: 'Full name and phone are required' });
    }

    const counselorId = assigned_to && assigned_to !== '' ? parseInt(assigned_to) : null;
    const followUp = follow_up_date && follow_up_date !== '' ? follow_up_date : null;
    
    const uid = uuidv4();
    const result = getDb().prepare(`
      INSERT INTO admission_enquiries (
        uid, full_name, email, phone, address, class_interested, 
        source, follow_up_date, notes, next_action, assigned_to
      )
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      uid, 
      full_name, 
      email && email !== '' ? email : null, 
      phone, 
      address && address !== '' ? address : null, 
      class_interested && class_interested !== '' ? class_interested : null, 
      source && source !== '' ? source : null, 
      followUp, 
      notes && notes !== '' ? notes : null, 
      next_action && next_action !== '' ? next_action : null, 
      counselorId
    );
    
    const enquiry = getDb().prepare('SELECT * FROM admission_enquiries WHERE id = ?').get(result.lastInsertRowid);
    res.status(201).json(enquiry);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Update enquiry details
/**
 * @openapi
 * /api/front-office/enquiries/{id}:
 *   put:
 *     summary: Update an admission enquiry
 *     description: |
 *       Partially updates an existing enquiry. Fields omitted from
 *       the body keep their existing value; fields supplied as
 *       empty strings are normalised to `null`. Returns 404 if the
 *       enquiry does not exist or has been soft-deleted. Requires
 *       authentication.
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               full_name: { type: string }
 *               email: { type: string }
 *               phone: { type: string }
 *               address: { type: string }
 *               class_interested: { type: string }
 *               source: { type: string }
 *               status: { type: string }
 *               notes: { type: string }
 *               follow_up_date: { type: string, format: date }
 *               next_action: { type: string }
 *               assigned_to: { type: integer }
 *     responses:
 *       200:
 *         description: Updated
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       404: { description: Enquiry not found }
 *       500: { description: Server error }
 *   delete:
 *     summary: Soft-delete an admission enquiry
 *     description: |
 *       Marks the enquiry as deleted (`is_deleted = 1`) and flags
 *       it as `pending` for sync. The row is never removed. Requires
 *       authentication.
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Soft-deleted
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.put('/enquiries/:id', auth, (req, res) => {
  try {
    const { 
      full_name, email, phone, address, class_interested, 
      source, status, notes, follow_up_date, next_action, assigned_to 
    } = req.body;

    const existing = getDb().prepare('SELECT * FROM admission_enquiries WHERE id = ? AND is_deleted = 0').get(req.params.id);
    if (!existing) return res.status(404).json({ error: 'Enquiry not found' });

    const updated = {
      full_name: full_name !== undefined ? full_name : existing.full_name,
      email: email !== undefined ? (email === '' ? null : email) : existing.email,
      phone: phone !== undefined ? phone : existing.phone,
      address: address !== undefined ? (address === '' ? null : address) : existing.address,
      class_interested: class_interested !== undefined ? (class_interested === '' ? null : class_interested) : existing.class_interested,
      source: source !== undefined ? (source === '' ? null : source) : existing.source,
      status: status !== undefined ? status : existing.status,
      notes: notes !== undefined ? (notes === '' ? null : notes) : existing.notes,
      follow_up_date: follow_up_date !== undefined ? (follow_up_date === '' ? null : follow_up_date) : existing.follow_up_date,
      next_action: next_action !== undefined ? (next_action === '' ? null : next_action) : existing.next_action,
      assigned_to: assigned_to !== undefined ? (assigned_to === '' || assigned_to === null ? null : parseInt(assigned_to)) : existing.assigned_to
    };
    
    getDb().prepare(`
      UPDATE admission_enquiries SET
        full_name = ?,
        email = ?,
        phone = ?,
        address = ?,
        class_interested = ?,
        source = ?,
        status = ?,
        notes = ?,
        follow_up_date = ?,
        next_action = ?,
        assigned_to = ?,
        updated_at = datetime(\'now\', \'localtime\'),
        sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(
      updated.full_name,
      updated.email,
      updated.phone,
      updated.address,
      updated.class_interested,
      updated.source,
      updated.status,
      updated.notes,
      updated.follow_up_date,
      updated.next_action,
      updated.assigned_to,
      req.params.id
    );
    res.json({ success: true });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Soft delete admission enquiry
router.delete('/enquiries/:id', auth, (req, res) => {
  try {
    getDb().prepare(`
      UPDATE admission_enquiries
      SET is_deleted = 1, updated_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(req.params.id);
    res.json({ success: true });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ COMPLAINTS ============

// List all complaints
/**
 * @openapi
 * /api/front-office/complaints:
 *   get:
 *     summary: List complaints
 *     description: |
 *       Returns non-deleted complaints joined with the assigned
 *       owner's and resolver's display names, ordered by most recent
 *       receipt date first. Requires authentication.
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Complaint list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: File a new complaint
 *     description: |
 *       Creates a complaint in `status = open` and `sync_status =
 *       pending`. `complainant_name`, `category`, `subject`, and
 *       `description` are required; `complainant_type` defaults to
 *       `parent` and `priority` defaults to `medium` when omitted.
 *       Requires authentication.
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - complainant_name
 *               - category
 *               - subject
 *               - description
 *             properties:
 *               complainant_name: { type: string }
 *               complainant_type: { type: string }
 *               contact_phone: { type: string }
 *               contact_email: { type: string }
 *               category: { type: string }
 *               priority: { type: string }
 *               subject: { type: string }
 *               description: { type: string }
 *               assigned_to: { type: integer }
 *     responses:
 *       201:
 *         description: Created complaint
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Required fields missing }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.get('/complaints', auth, (req, res) => {
  try {
    const complaints = getDb().prepare(`
      SELECT c.*, 
        u1.first_name || ' ' || u1.last_name as assigned_to_name,
        u2.first_name || ' ' || u2.last_name as resolved_by_name
      FROM complaints c
      LEFT JOIN users u1 ON c.assigned_to = u1.id
      LEFT JOIN users u2 ON c.resolved_by = u2.id
      WHERE c.is_deleted = 0
      ORDER BY c.date_received DESC
    `).all();
    res.json(complaints);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Create complaint
router.post('/complaints', auth, (req, res) => {
  try {
    const { 
      complainant_name, complainant_type, contact_phone, contact_email, 
      category, priority, subject, description, assigned_to 
    } = req.body;
    
    if (!complainant_name || !category || !subject || !description) {
      return res.status(400).json({ error: 'Required fields missing' });
    }
    
    const assignedId = assigned_to && assigned_to !== '' ? parseInt(assigned_to) : null;
    const phoneVal = contact_phone && contact_phone !== '' ? contact_phone : null;
    const emailVal = contact_email && contact_email !== '' ? contact_email : null;
    
    const uid = uuidv4();
    const result = getDb().prepare(`
      INSERT INTO complaints (
        uid, complainant_name, complainant_type, contact_phone, contact_email, 
        category, priority, subject, description, assigned_to, status, sync_status
      )
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'open', 'pending')
    `).run(
      uid, 
      complainant_name, 
      complainant_type || 'parent', 
      phoneVal, 
      emailVal, 
      category, 
      priority || 'medium', 
      subject, 
      description, 
      assignedId
    );
    
    const complaint = getDb().prepare('SELECT * FROM complaints WHERE id = ?').get(result.lastInsertRowid);
    res.status(201).json(complaint);
  } catch (error) {
    console.error('CREATE COMPLAINT ERROR:', error);
    res.status(500).json({ error: error.message });
  }
});

// Resolve complaint
/**
 * @openapi
 * /api/front-office/complaints/{id}/resolve:
 *   put:
 *     summary: Resolve a complaint
 *     description: |
 *       Marks the complaint as `status = resolved`, stores the
 *       supplied `resolution` text, records the resolver as the
 *       authenticated user, and timestamps `resolved_at`. Requires
 *       authentication.
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               resolution: { type: string }
 *     responses:
 *       200:
 *         description: Resolved
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.put('/complaints/:id/resolve', auth, (req, res) => {
  try {
    const { resolution } = req.body;
    getDb().prepare(`
      UPDATE complaints SET
        status = 'resolved',
        resolution = ?,
        resolved_by = ?,
        resolved_at = datetime(\'now\', \'localtime\'),
        updated_at = datetime(\'now\', \'localtime\'),
        sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(resolution, req.user.id, req.params.id);
    res.json({ success: true });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ PHONE CALL LOGS ============

// List phone calls (filter by date range + handler)
/**
 * @openapi
 * /api/front-office/phone-calls:
 *   get:
 *     summary: List phone call logs
 *     description: |
 *       Returns non-deleted phone call logs joined with the
 *       handler's display name, ordered by most recent call time
 *       first. Optional `from` / `to` filter on `call_time` and
 *       `handled_by` narrows results. Requires authentication.
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: from
 *         schema: { type: string, format: date }
 *       - in: query
 *         name: to
 *         schema: { type: string, format: date }
 *       - in: query
 *         name: handled_by
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Phone call list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Log a phone call
 *     description: |
 *       Records a single phone call. `caller_name` is required;
 *       `handled_by` defaults to the authenticated user. Restricted
 *       to staff write roles (`school_admin`, `systems_admin`,
 *       `principal`, `teacher`).
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - caller_name
 *             properties:
 *               caller_name: { type: string }
 *               caller_phone: { type: string }
 *               caller_type: { type: string }
 *               direction: { type: string }
 *               call_time: { type: string, format: date-time }
 *               duration_seconds: { type: integer }
 *               purpose: { type: string }
 *               response: { type: string }
 *               follow_up_required: { type: boolean }
 *               follow_up_date: { type: string, format: date }
 *               handled_by: { type: integer }
 *               notes: { type: string }
 *     responses:
 *       201:
 *         description: Created call log
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: caller_name is required }
 *       401: { description: Unauthorized }
 *       403: { description: Staff only }
 *       500: { description: Server error }
 */
router.get('/phone-calls', auth, (req, res) => {
  try {
    const { from, to, handled_by } = req.query;
    let query = `
      SELECT p.*, u.first_name || ' ' || u.last_name as handler_name
      FROM phone_call_logs p
      LEFT JOIN users u ON p.handled_by = u.id
      WHERE p.is_deleted = 0
    `;
    const params = [];
    if (from) { query += ' AND p.call_time >= ?'; params.push(from); }
    if (to)   { query += ' AND p.call_time <= ?'; params.push(to); }
    if (handled_by) { query += ' AND p.handled_by = ?'; params.push(handled_by); }
    query += ' ORDER BY p.call_time DESC';
    const calls = getDb().prepare(query).all(...params);
    res.json(calls);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Create phone call log
router.post('/phone-calls', auth, staffWrite, (req, res) => {
  try {
    const {
      caller_name, caller_phone, caller_type, direction, call_time,
      duration_seconds, purpose, response, follow_up_required,
      follow_up_date, handled_by, notes,
    } = req.body;

    if (!caller_name) {
      return res.status(400).json({ error: 'caller_name is required' });
    }

    const uid = uuidv4();
    const result = getDb().prepare(`
      INSERT INTO phone_call_logs (
        uid, caller_name, caller_phone, caller_type, direction, call_time,
        duration_seconds, purpose, response, follow_up_required,
        follow_up_date, handled_by, notes
      )
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      uid, caller_name, caller_phone, caller_type, direction, call_time,
      duration_seconds, purpose, response, follow_up_required ? 1 : 0,
      follow_up_date, handled_by || req.user.id, notes
    );

    const row = getDb().prepare('SELECT * FROM phone_call_logs WHERE id = ?').get(result.lastInsertRowid);
    res.status(201).json(row);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Bulk import phone calls
/**
 * @openapi
 * /api/front-office/phone-calls/bulk:
 *   post:
 *     summary: Bulk import phone calls
 *     description: |
 *       Inserts each entry in the supplied `calls` array as a new
 *       phone call log row and returns a per-row success/failure
 *       summary along with aggregate `created` and `failed` counts.
 *       Defaults per record: `caller_type = other`, `direction =
 *       incoming`, `call_time = now`, `duration_seconds = 0`,
 *       `handled_by = caller`. Restricted to staff write roles
 *       (`school_admin`, `systems_admin`, `principal`, `teacher`).
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - calls
 *             properties:
 *               calls:
 *                 type: array
 *                 items:
 *                   type: object
 *                   required:
 *                     - caller_name
 *                   properties:
 *                     caller_name: { type: string }
 *                     caller_phone: { type: string }
 *                     caller_type: { type: string }
 *                     direction: { type: string }
 *                     call_time: { type: string, format: date-time }
 *                     duration_seconds: { type: integer }
 *                     purpose: { type: string }
 *                     response: { type: string }
 *                     follow_up_required: { type: boolean }
 *                     follow_up_date: { type: string, format: date }
 *                     handled_by: { type: integer }
 *                     notes: { type: string }
 *     responses:
 *       201:
 *         description: Per-row outcomes and counts
 *         content:
 *           application/json:
 *             schema: { type: object }
 *             properties:
 *               created: { type: integer }
 *               failed: { type: integer }
 *               results:
 *                 type: array
 *                 items: { type: object }
 *       400: { description: calls array required }
 *       401: { description: Unauthorized }
 *       403: { description: Staff only }
 *       500: { description: Server error }
 */
router.post('/phone-calls/bulk', auth, staffWrite, (req, res) => {
  try {
    const { calls } = req.body;
    
    if (!Array.isArray(calls) || calls.length === 0) {
      return res.status(400).json({ error: 'Calls array required' });
    }
    
    const insert = getDb().prepare(`
      INSERT INTO phone_call_logs (
        uid, caller_name, caller_phone, caller_type, direction, call_time,
        duration_seconds, purpose, response, follow_up_required,
        follow_up_date, handled_by, notes
      )
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);
    
    const results = [];
    for (const call of calls) {
      try {
        const uid = uuidv4();
        insert.run(
          uid, 
          call.caller_name, 
          call.caller_phone, 
          call.caller_type || 'other', 
          call.direction || 'incoming', 
          call.call_time || new Date().toISOString(),
          call.duration_seconds || 0, 
          call.purpose, 
          call.response, 
          call.follow_up_required ? 1 : 0,
          call.follow_up_date, 
          call.handled_by || req.user.id, 
          call.notes
        );
        results.push({ caller_name: call.caller_name, success: true });
      } catch (e) {
        results.push({ caller_name: call.caller_name, error: e.message, success: false });
      }
    }
    
    res.status(201).json({
      created: results.filter(r => r.success).length,
      failed: results.filter(r => !r.success).length,
      results
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Update phone call log
/**
 * @openapi
 * /api/front-office/phone-calls/{id}:
 *   put:
 *     summary: Update a phone call log
 *     description: |
 *       Updates fields on an existing call log via `COALESCE` so
 *       omitted fields retain their current values. `follow_up_required`
 *       is normalised to a 0/1 integer. Restricted to staff write
 *       roles (`school_admin`, `systems_admin`, `principal`,
 *       `teacher`).
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               caller_name: { type: string }
 *               caller_phone: { type: string }
 *               caller_type: { type: string }
 *               direction: { type: string }
 *               call_time: { type: string, format: date-time }
 *               duration_seconds: { type: integer }
 *               purpose: { type: string }
 *               response: { type: string }
 *               follow_up_required: { type: boolean }
 *               follow_up_date: { type: string, format: date }
 *               handled_by: { type: integer }
 *               notes: { type: string }
 *     responses:
 *       200:
 *         description: Updated
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Staff only }
 *       500: { description: Server error }
 *   delete:
 *     summary: Soft-delete a phone call log
 *     description: |
 *       Marks the call log as deleted (`is_deleted = 1`) and flags
 *       it as `pending` for sync. Restricted to staff write roles
 *       (`school_admin`, `systems_admin`, `principal`, `teacher`).
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Soft-deleted
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Staff only }
 *       500: { description: Server error }
 */
router.put('/phone-calls/:id', auth, staffWrite, (req, res) => {
  try {
    const {
      caller_name, caller_phone, caller_type, direction, call_time,
      duration_seconds, purpose, response, follow_up_required,
      follow_up_date, handled_by, notes,
    } = req.body;

    getDb().prepare(`
      UPDATE phone_call_logs SET
        caller_name       = COALESCE(?, caller_name),
        caller_phone      = COALESCE(?, caller_phone),
        caller_type       = COALESCE(?, caller_type),
        direction         = COALESCE(?, direction),
        call_time         = COALESCE(?, call_time),
        duration_seconds  = COALESCE(?, duration_seconds),
        purpose           = COALESCE(?, purpose),
        response          = COALESCE(?, response),
        follow_up_required = COALESCE(?, follow_up_required),
        follow_up_date    = COALESCE(?, follow_up_date),
        handled_by        = COALESCE(?, handled_by),
        notes             = COALESCE(?, notes),
        updated_at        = datetime(\'now\', \'localtime\'),
        sync_status       = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(
      caller_name, caller_phone, caller_type, direction, call_time,
      duration_seconds, purpose, response,
      follow_up_required === undefined ? null : (follow_up_required ? 1 : 0),
      follow_up_date, handled_by, notes, req.params.id
    );

    res.json({ success: true });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Soft delete phone call log
router.delete('/phone-calls/:id', auth, staffWrite, (req, res) => {
  try {
    getDb().prepare(`
      UPDATE phone_call_logs
      SET is_deleted = 1, updated_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(req.params.id);
    res.json({ success: true });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Mark follow-up done
/**
 * @openapi
 * /api/front-office/phone-calls/{id}/follow-up:
 *   put:
 *     summary: Mark follow-up complete
 *     description: |
 *       Clears `follow_up_required` on the call log and optionally
 *       appends or replaces `notes` (via `COALESCE`). Restricted to
 *       staff write roles (`school_admin`, `systems_admin`,
 *       `principal`, `teacher`).
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: false
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               notes: { type: string }
 *     responses:
 *       200:
 *         description: Follow-up cleared
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Staff only }
 *       500: { description: Server error }
 */
router.put('/phone-calls/:id/follow-up', auth, staffWrite, (req, res) => {
  try {
    const { notes } = req.body;
    getDb().prepare(`
      UPDATE phone_call_logs SET
        follow_up_required = 0,
        notes = COALESCE(?, notes),
        updated_at = datetime(\'now\', \'localtime\'),
        sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(notes, req.params.id);
    res.json({ success: true });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ POSTAL DISPATCH ============

// List postal items (filter by type + status)
/**
 * @openapi
 * /api/front-office/postal:
 *   get:
 *     summary: List postal items
 *     description: |
 *       Returns non-deleted postal dispatch rows joined with sender,
 *       receiver, and creator display names. Optional `type` and
 *       `status` filters and a `from` / `to` range on `dispatch_date`
 *       narrow the result. Requires authentication.
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: type
 *         schema: { type: string }
 *       - in: query
 *         name: status
 *         schema: { type: string }
 *       - in: query
 *         name: from
 *         schema: { type: string, format: date }
 *       - in: query
 *         name: to
 *         schema: { type: string, format: date }
 *     responses:
 *       200:
 *         description: Postal item list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Create a postal item
 *     description: |
 *       Records a single postal dispatch entry. `sender` and
 *       `receiver` are required; `type` defaults to `dispatch` when
 *       omitted. Restricted to admin roles (`school_admin`,
 *       `systems_admin`).
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - sender
 *               - receiver
 *             properties:
 *               type: { type: string }
 *               reference_number: { type: string }
 *               sender: { type: string }
 *               receiver: { type: string }
 *               address: { type: string }
 *               courier: { type: string }
 *               tracking_number: { type: string }
 *               dispatch_date: { type: string, format: date-time }
 *               description: { type: string }
 *     responses:
 *       201:
 *         description: Created postal item
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: sender and receiver are required }
 *       401: { description: Unauthorized }
 *       403: { description: Admin only }
 *       500: { description: Server error }
 */
router.get('/postal', auth, (req, res) => {
  try {
    const { type, status, from, to } = req.query;
    let query = `
      SELECT p.*,
        s.first_name || ' ' || s.last_name as sender_name,
        r.first_name || ' ' || r.last_name as receiver_name,
        u.first_name || ' ' || u.last_name as created_by_name
      FROM postal_dispatch p
      LEFT JOIN users s ON p.sender = CAST(s.id AS TEXT)
      LEFT JOIN users r ON p.receiver = CAST(r.id AS TEXT)
      LEFT JOIN users u ON p.created_by = u.id
      WHERE p.is_deleted = 0
    `;
    const params = [];
    if (type)   { query += ' AND p.type = ?';   params.push(type); }
    if (status) { query += ' AND p.status = ?'; params.push(status); }
    if (from)   { query += ' AND p.dispatch_date >= ?'; params.push(from); }
    if (to)     { query += ' AND p.dispatch_date <= ?'; params.push(to); }
    query += ' ORDER BY p.dispatch_date DESC';
    const items = getDb().prepare(query).all(...params);
    res.json(items);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Create postal item
router.post('/postal', auth, adminOnly, (req, res) => {
  try {
    const {
      type, reference_number, sender, receiver, address, courier,
      tracking_number, dispatch_date, description,
    } = req.body;

    if (!sender || !receiver) {
      return res.status(400).json({ error: 'sender and receiver are required' });
    }

    const uid = uuidv4();
    const result = getDb().prepare(`
      INSERT INTO postal_dispatch (
        uid, type, reference_number, sender, receiver, address, courier,
        tracking_number, dispatch_date, description, created_by
      )
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      uid, type || 'dispatch', reference_number, sender, receiver, address,
      courier, tracking_number, dispatch_date, description, req.user.id
    );

    const row = getDb().prepare('SELECT * FROM postal_dispatch WHERE id = ?').get(result.lastInsertRowid);
    res.status(201).json(row);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Bulk import postal dispatch
/**
 * @openapi
 * /api/front-office/postal/bulk:
 *   post:
 *     summary: Bulk import postal items
 *     description: |
 *       Inserts each entry in the supplied `items` array as a new
 *       postal dispatch row and returns a per-row success/failure
 *       summary along with aggregate `created` and `failed` counts.
 *       Defaults per record: `type = dispatch`, `dispatch_date =
 *       now`, `status = pending`. Restricted to staff write roles
 *       (`school_admin`, `systems_admin`, `principal`, `teacher`).
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - items
 *             properties:
 *               items:
 *                 type: array
 *                 items:
 *                   type: object
 *                   required:
 *                     - sender
 *                     - receiver
 *                   properties:
 *                     type: { type: string }
 *                     reference_number: { type: string }
 *                     sender: { type: string }
 *                     receiver: { type: string }
 *                     address: { type: string }
 *                     courier: { type: string }
 *                     tracking_number: { type: string }
 *                     dispatch_date: { type: string, format: date-time }
 *                     description: { type: string }
 *                     status: { type: string }
 *     responses:
 *       201:
 *         description: Per-row outcomes and counts
 *         content:
 *           application/json:
 *             schema: { type: object }
 *             properties:
 *               created: { type: integer }
 *               failed: { type: integer }
 *               results:
 *                 type: array
 *                 items: { type: object }
 *       400: { description: items array required }
 *       401: { description: Unauthorized }
 *       403: { description: Staff only }
 *       500: { description: Server error }
 */
router.post('/postal/bulk', auth, staffWrite, (req, res) => {
  try {
    const { items } = req.body;
    
    if (!Array.isArray(items) || items.length === 0) {
      return res.status(400).json({ error: 'Items array required' });
    }
    
    const insert = getDb().prepare(`
      INSERT INTO postal_dispatch (
        uid, type, reference_number, sender, receiver, address, courier,
        tracking_number, dispatch_date, description, status, created_by
      )
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);
    
    const results = [];
    for (const item of items) {
      try {
        const uid = uuidv4();
        insert.run(
          uid, 
          item.type || 'dispatch', 
          item.reference_number, 
          item.sender, 
          item.receiver, 
          item.address, 
          item.courier, 
          item.tracking_number, 
          item.dispatch_date || new Date().toISOString(), 
          item.description,
          item.status || 'pending',
          req.user.id
        );
        results.push({ sender: item.sender, receiver: item.receiver, success: true });
      } catch (e) {
        results.push({ sender: item.sender, receiver: item.receiver, error: e.message, success: false });
      }
    }
    
    res.status(201).json({
      created: results.filter(r => r.success).length,
      failed: results.filter(r => !r.success).length,
      results
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Update postal item
/**
 * @openapi
 * /api/front-office/postal/{id}:
 *   put:
 *     summary: Update a postal item
 *     description: |
 *       Updates fields on an existing postal dispatch row via
 *       `COALESCE` so omitted fields retain their current values.
 *       Restricted to admin roles (`school_admin`, `systems_admin`).
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               type: { type: string }
 *               reference_number: { type: string }
 *               sender: { type: string }
 *               receiver: { type: string }
 *               address: { type: string }
 *               courier: { type: string }
 *               tracking_number: { type: string }
 *               dispatch_date: { type: string, format: date-time }
 *               description: { type: string }
 *               status: { type: string }
 *     responses:
 *       200:
 *         description: Updated
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Admin only }
 *       500: { description: Server error }
 *   delete:
 *     summary: Soft-delete a postal item
 *     description: |
 *       Marks the postal row as deleted (`is_deleted = 1`) and
 *       flags it as `pending` for sync. Restricted to admin roles
 *       (`school_admin`, `systems_admin`).
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Soft-deleted
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Admin only }
 *       500: { description: Server error }
 */
router.put('/postal/:id', auth, adminOnly, (req, res) => {
  try {
    const {
      type, reference_number, sender, receiver, address, courier,
      tracking_number, dispatch_date, description, status,
    } = req.body;

    getDb().prepare(`
      UPDATE postal_dispatch SET
        type             = COALESCE(?, type),
        reference_number = COALESCE(?, reference_number),
        sender           = COALESCE(?, sender),
        receiver         = COALESCE(?, receiver),
        address          = COALESCE(?, address),
        courier          = COALESCE(?, courier),
        tracking_number  = COALESCE(?, tracking_number),
        dispatch_date    = COALESCE(?, dispatch_date),
        description      = COALESCE(?, description),
        status           = COALESCE(?, status),
        updated_at       = datetime(\'now\', \'localtime\'),
        sync_status      = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(
      type, reference_number, sender, receiver, address, courier,
      tracking_number, dispatch_date, description, status, req.params.id
    );

    res.json({ success: true });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Mark postal item as sent
/**
 * @openapi
 * /api/front-office/postal/{id}/mark-sent:
 *   put:
 *     summary: Mark postal item as sent
 *     description: |
 *       Sets `status = sent` on the postal dispatch row and flags it
 *       as `pending` for sync. Restricted to admin roles
 *       (`school_admin`, `systems_admin`).
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Marked sent
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Admin only }
 *       500: { description: Server error }
 */
router.put('/postal/:id/mark-sent', auth, adminOnly, (req, res) => {
  try {
    getDb().prepare(`
      UPDATE postal_dispatch SET
        status = 'sent',
        updated_at = datetime(\'now\', \'localtime\'),
        sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(req.params.id);
    res.json({ success: true });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Mark postal item as delivered
/**
 * @openapi
 * /api/front-office/postal/{id}/mark-delivered:
 *   put:
 *     summary: Mark postal item as delivered
 *     description: |
 *       Sets `status = delivered`, stamps `received_date` to the
 *       current local time, and flags the row as `pending` for
 *       sync. Restricted to admin roles (`school_admin`,
 *       `systems_admin`).
 *     tags:
 *       - Front Office
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Marked delivered
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Admin only }
 *       500: { description: Server error }
 */
router.put('/postal/:id/mark-delivered', auth, adminOnly, (req, res) => {
  try {
    getDb().prepare(`
      UPDATE postal_dispatch SET
        status = 'delivered',
        received_date = datetime(\'now\', \'localtime\'),
        updated_at = datetime(\'now\', \'localtime\'),
        sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(req.params.id);
    res.json({ success: true });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Soft delete postal item
router.delete('/postal/:id', auth, adminOnly, (req, res) => {
  try {
    getDb().prepare(`
      UPDATE postal_dispatch
      SET is_deleted = 1, updated_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(req.params.id);
    res.json({ success: true });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

module.exports = router;
