/**
 * Staff Attendance Controller
 *
 * Daily roll-call for all staff members. Backed by the
 * `staff_attendance` table.
 */

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

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

/**
 * @openapi
 * /api/staff-attendance/roster:
 *   get:
 *     summary: Get staff roster for a date
 *     description: |
 *       Returns all active staff members (roles other than `student`
 *       or `parent`) joined with that day's attendance row. `date`
 *       defaults to today.
 *     tags:
 *       - Staff Attendance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: date
 *         schema: { type: string, format: date }
 *       - in: query
 *         name: role
 *         schema: { type: string }
 *         description: Filter by staff role (use "all" to skip filter)
 *     responses:
 *       200:
 *         description: Staff roster with attendance flags
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 date: { type: string, format: date }
 *                 total: { type: integer }
 *                 roster:
 *                   type: array
 *                   items: { type: object }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.get('/roster', auth, (req, res) => {
  try {
    const { date, role } = req.query;
    const targetDate = date || new Date().toISOString().split('T')[0];

    let query = `
      SELECT 
        u.id as student_id,
        u.first_name || ' ' || u.last_name as student_name,
        u.role as staff_role,
        sa.status,
        sa.id as attendance_id,
        sa.remarks
      FROM users u
      LEFT JOIN staff_attendance sa ON u.id = sa.staff_id AND sa.date = ? AND sa.is_deleted = 0
      WHERE u.role NOT IN ('student', 'parent') AND u.is_active = 1 AND u.is_deleted = 0
    `;
    const params = [targetDate];

    if (role && role !== 'all') {
      query += ' AND u.role = ?';
      params.push(role);
    }

    query += ' ORDER BY u.last_name, u.first_name';

    const staff = db.prepare(query).all(...params);

    res.json({
      date: targetDate,
      total: staff.length,
      roster: staff
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/staff-attendance/bulk:
 *   post:
 *     summary: Bulk upsert staff attendance
 *     description: |
 *       Upserts attendance records in a single transaction. Each
 *       record is matched on `(staff_id, date)` and updated or
 *       inserted. Restricted to HR / admin roles
 *       (`school_admin`, `systems_admin`, `principal`, `hr`).
 *     tags:
 *       - Staff Attendance
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - date
 *               - records
 *             properties:
 *               date: { type: string, format: date }
 *               records:
 *                 type: array
 *                 items:
 *                   type: object
 *                   required:
 *                     - student_id
 *                     - status
 *                   properties:
 *                     student_id:
 *                       type: integer
 *                       description: Staff user ID (named student_id for legacy reasons)
 *                     status:
 *                       type: string
 *                       enum: [present, absent, excused]
 *                     remarks: { type: string }
 *     responses:
 *       200:
 *         description: Counts of inserted and updated records
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *                 total: { type: integer }
 *                 inserted: { type: integer }
 *                 updated: { type: integer }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: HR or Admin access required }
 *       500: { description: Server error }
 */
router.post('/bulk', auth, staffWrite, (req, res) => {
  try {
    const { date, records } = req.body; // records: [{ student_id, status, remarks }]
    if (!date || !records || !Array.isArray(records)) {
      return res.status(400).json({ error: 'Invalid payload' });
    }

    const upsert = db.prepare(`
      INSERT INTO staff_attendance (uid, staff_id, date, status, remarks, marked_by, sync_status)
      VALUES (?, ?, ?, ?, ?, ?, 'pending')
    `);

    const updateStmt = db.prepare(`
      UPDATE staff_attendance 
      SET status = ?, remarks = ?, marked_by = ?, updated_at = CURRENT_TIMESTAMP, sync_status = 'pending'
      WHERE id = ?
    `);

    const findExisting = db.prepare('SELECT id FROM staff_attendance WHERE staff_id = ? AND date = ? AND is_deleted = 0');

    let inserted = 0;
    let updated = 0;

    const process = db.transaction((recs) => {
      for (const r of recs) {
        const existing = findExisting.get(r.student_id, date);
        if (existing) {
          updateStmt.run(r.status, r.remarks || null, req.user.id, existing.id);
          updated++;
        } else {
          upsert.run(uuidv4(), r.student_id, date, r.status, r.remarks || null, req.user.id);
          inserted++;
        }
      }
    });

    process(records);

    res.json({
      success: true,
      total: records.length,
      inserted,
      updated
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/staff-attendance/history:
 *   get:
 *     summary: Per-staff attendance history
 *     description: |
 *       Detailed history for a single staff member. Staff can see
 *       their own history; admins / principals / HR can see anyone's.
 *     tags:
 *       - Staff Attendance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: student_id
 *         required: true
 *         schema: { type: integer }
 *         description: Staff user ID (named student_id for legacy reasons)
 *       - in: query
 *         name: from
 *         schema: { type: string, format: date }
 *       - in: query
 *         name: to
 *         schema: { type: string, format: date }
 *     responses:
 *       200:
 *         description: History with status summary
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 student_id: { type: integer }
 *                 summary: { type: object }
 *                 records:
 *                   type: array
 *                   items: { type: object }
 *       400: { description: staff_id required }
 *       401: { description: Unauthorized }
 *       403: { description: Access denied }
 *       500: { description: Server error }
 */
router.get('/history', auth, (req, res) => {
  try {
    const { student_id, from, to } = req.query; // 'student_id' is staff ID here
    if (!student_id) return res.status(400).json({ error: 'staff_id required' });

    // Permissions: staff can see their own, admins/hr can see all
    if (req.user.id != student_id && !['admin', 'school_admin', 'systems_admin', 'principal', 'hr'].includes(req.user.role)) {
      return res.status(403).json({ error: 'Access denied' });
    }

    let query = 'SELECT * FROM staff_attendance WHERE staff_id = ? AND is_deleted = 0';
    const params = [student_id];

    if (from) { query += ' AND date >= ?'; params.push(from); }
    if (to)   { query += ' AND date <= ?'; params.push(to);   }
    query += ' ORDER BY date DESC';

    const records = db.prepare(query).all(...params);

    const summary = {
      total: records.length,
      present: records.filter(r => r.status === 'present').length,
      absent: records.filter(r => r.status === 'absent').length,
      excused: records.filter(r => r.status === 'excused').length,
    };

    res.json({ student_id, summary, records });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

module.exports = router;
