/**
 * Attendance Controller
 * Student and staff attendance tracking
 */

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

// Phase 1 PR 1: cover-teacher check. Admins and the regular form tutor pass
// automatically; everyone else must have an active class-scoped user_roles
// grant for the class they're marking attendance in.
const { hasRoleForClass } = require('../utils/rbac');
const canMarkForClass = (req, classId) => {
  if (!req.user) return false;
  if (hasRoleForClass(req.user, classId, 'school_admin')) return true;
  if (hasRoleForClass(req.user, classId, 'systems_admin')) return true;
  if (hasRoleForClass(req.user, classId, 'teacher')) return true;
  return false;
};

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

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

// ============ ATTENDANCE CRUD ============

// List attendance records
/**
 * @openapi
 * /api/attendance:
 *   get:
 *     summary: List attendance records
 *     description: |
 *       Returns attendance records joined with student, class, and
 *       subject names. Students see only their own rows; parents
 *       see rows for students linked via `parent_students`
 *       (optionally scoped to one child via `student_id`); teachers
 *       see rows for the classes they own or the subjects they
 *       teach. Admins and principals see all rows that match the
 *       optional filters.
 *     tags:
 *       - Attendance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: class_id
 *         schema: { type: integer }
 *       - in: query
 *         name: student_id
 *         schema: { type: integer }
 *       - in: query
 *         name: date
 *         schema: { type: string, format: date }
 *       - in: query
 *         name: start_date
 *         schema: { type: string, format: date }
 *       - in: query
 *         name: end_date
 *         schema: { type: string, format: date }
 *       - in: query
 *         name: from
 *         schema: { type: string, format: date }
 *         description: Alias for start_date
 *       - in: query
 *         name: to
 *         schema: { type: string, format: date }
 *         description: Alias for end_date
 *       - in: query
 *         name: status
 *         schema:
 *           type: string
 *           enum: [present, absent, late, excused]
 *     responses:
 *       200:
 *         description: Attendance records
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Parent not linked to student }
 *       500: { description: Server error }
 *   post:
 *     summary: Mark attendance
 *     description: |
 *       Marks a single attendance row for a student on a date and
 *       subject. If a row already exists for the same
 *       `(student_id, date, subject_id)` triple it is updated
 *       (upsert). Restricted to admin or teacher roles.
 *     tags:
 *       - Attendance
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - student_id
 *               - date
 *               - status
 *             properties:
 *               student_id: { type: integer }
 *               class_id: { type: integer }
 *               subject_id: { type: integer }
 *               date: { type: string, format: date }
 *               status:
 *                 type: string
 *                 enum: [present, absent, late, excused]
 *               remarks: { type: string }
 *     responses:
 *       200:
 *         description: Updated existing record
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       201:
 *         description: Created record
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Teacher only }
 *       500: { description: Server error }
 */
router.get('/', auth, (req, res) => {
  try {
    const { date, class_id, student_id, status, start_date, end_date, from, to } = req.query;

    // Support both naming conventions
    const effectiveStartDate = start_date || from;
    const effectiveEndDate = end_date || to;

    let query = `
      SELECT a.*,
        u.first_name || ' ' || u.last_name as student_name,
        c.name as class_name,
        s.name as subject_name
      FROM attendance a
      JOIN users u ON a.student_id = u.id
      LEFT JOIN classes c ON a.class_id = c.id
      LEFT JOIN subjects s ON a.subject_id = s.id
      WHERE a.is_deleted = 0
    `;
    const params = [];

    // Filter by user role
    if (req.user.role === 'student') {
      query += ' AND a.student_id = ?';
      params.push(req.user.id);
    } else if (req.user.role === 'parent') {
      // Parents can only see attendance for their linked students
      if (student_id) {
        const isLinked = getDb().prepare('SELECT id FROM parent_students WHERE parent_id = ? AND student_id = ? AND is_deleted = 0').get(req.user.id, student_id);
        if (!isLinked) {
          return res.status(403).json({ error: 'You do not have permission to view attendance for this student' });       
        }
        query += ' AND a.student_id = ?';
        params.push(student_id);
      } else {
        query += ' AND a.student_id IN (SELECT student_id FROM parent_students WHERE parent_id = ? AND is_deleted = 0)';  
        params.push(req.user.id);
      }
    } else if (req.user.role === 'teacher') {
      // Teachers see attendance for their classes
      query += ' AND (a.class_id IN (SELECT id FROM classes WHERE class_teacher_id = ?) OR a.subject_id IN (SELECT id FROM subjects WHERE teacher_id = ?))';
      params.push(req.user.id, req.user.id);
    }

    if (date) {
      query += ' AND a.date = ?';
      params.push(date);
    }
    if (effectiveStartDate) {
      query += ' AND a.date >= ?';
      params.push(effectiveStartDate);
    }
    if (effectiveEndDate) {
      query += ' AND a.date <= ?';
      params.push(effectiveEndDate);
    }
    if (class_id) {
      query += ' AND a.class_id = ?';
      params.push(class_id);
    }
    if (student_id && req.user.role !== 'student' && req.user.role !== 'parent') {
      query += ' AND a.student_id = ?';
      params.push(student_id);
    }
    if (status) {
      query += ' AND a.status = ?';
      params.push(status);
    }
    
    query += ' ORDER BY a.date DESC, u.last_name';
    
    const records = getDb().prepare(query).all(...params);
    
    res.json(records);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Helper to inject mock attendance data
function injectMockData(studentId) {
  try {
    const db = getDb();
    // Get student's class
    const enrollment = db.prepare('SELECT class_id FROM enrollments WHERE student_id = ? AND is_deleted = 0').get(studentId);
    const classId = enrollment ? enrollment.class_id : null;
    
    // Get some subjects for this class
    const subjects = classId 
      ? db.prepare('SELECT id FROM subjects WHERE class_id = ? AND is_deleted = 0').all(classId)
      : db.prepare('SELECT id FROM subjects WHERE is_deleted = 0 LIMIT 5').all();
    
    const statuses = ['present', 'present', 'present', 'present', 'late', 'absent', 'excused'];
    const admin = db.prepare("SELECT id FROM users WHERE role IN ('school_admin', 'systems_admin') LIMIT 1").get();
    const markedBy = admin ? admin.id : 1;

    const insert = db.prepare(`
      INSERT INTO attendance (uid, student_id, class_id, subject_id, date, status, remarks, marked_by)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `);

    // Generate last 30 days of attendance
    const today = new Date();
    for (let i = 0; i < 30; i++) {
      const date = new Date(today);
      date.setDate(today.getDate() - i);
      
      // Skip weekends
      if (date.getDay() === 0 || date.getDay() === 6) continue;
      
      const dateStr = date.toISOString().split('T')[0];
      const status = statuses[Math.floor(Math.random() * statuses.length)];
      const subjectId = subjects.length > 0 ? subjects[i % subjects.length].id : null;
      
      // Check if already exists
      const existing = db.prepare('SELECT id FROM attendance WHERE student_id = ? AND date = ? AND subject_id IS ?').get(studentId, dateStr, subjectId);
      if (!existing) {
        insert.run(uuidv4(), studentId, classId, subjectId, dateStr, status, 'Automatically injected registry entry', markedBy);
      }
    }
    console.log(`Injected mock attendance for student ${studentId}`);
  } catch (err) {
    console.error('Error injecting mock data:', err);
  }
}

// Get today's attendance rows for a class (or all classes if class_id omitted)
// GET /api/attendance/today?class_id=
/**
 * @openapi
 * /api/attendance/today:
 *   get:
 *     summary: Get today's attendance
 *     description: |
 *       Returns today's attendance rows scoped by caller role.
 *       Students see only their own rows; parents see rows for
 *       students linked via `parent_students`; teachers see rows
 *       for the classes they own or the subjects they teach; admins
 *       and principals see all rows. The optional `class_id` query
 *       parameter further restricts the result to one class.
 *     tags:
 *       - Attendance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: class_id
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Today's attendance with date, total, and records
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 date: { type: string, format: date }
 *                 total: { type: integer }
 *                 records:
 *                   type: array
 *                   items: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Role not allowed }
 *       500: { description: Server error }
 */
router.get('/today', auth, (req, res) => {
  try {
    const today = new Date().toISOString().split('T')[0];
    let query = `
      SELECT a.*,
        u.first_name || ' ' || u.last_name as student_name,
        c.name as class_name,
        s.name as subject_name
      FROM attendance a
      JOIN users u ON a.student_id = u.id
      LEFT JOIN classes c ON a.class_id = c.id
      LEFT JOIN subjects s ON a.subject_id = s.id
      WHERE a.is_deleted = 0 AND a.date = ?
    `;
    const params = [today];

    if (req.user.role === 'student') {
      query += ' AND a.student_id = ?';
      params.push(req.user.id);
    } else if (req.user.role === 'parent') {
      query += ' AND a.student_id IN (SELECT student_id FROM parent_students WHERE parent_id = ? AND is_deleted = 0)';
      params.push(req.user.id);
    } else if (req.user.role === 'teacher') {
      query += ' AND (a.class_id IN (SELECT id FROM classes WHERE class_teacher_id = ?) OR a.subject_id IN (SELECT id FROM subjects WHERE teacher_id = ?))';
      params.push(req.user.id, req.user.id);
    } else if (req.user.role === 'school_admin' || req.user.role === 'systems_admin' || req.user.role === 'admin' || req.user.role === 'principal') {
      // admin / principal see all
    } else {
      return res.status(403).json({ error: 'Role not allowed' });
    }

    if (req.query.class_id) {
      query += ' AND a.class_id = ?';
      params.push(req.query.class_id);
    }
    query += ' ORDER BY u.last_name, s.name';

    const records = getDb().prepare(query).all(...params);
    res.json({ date: today, total: records.length, records });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Get single attendance record
/**
 * @openapi
 * /api/attendance/{id}:
 *   get:
 *     summary: Get attendance record by id
 *     description: |
 *       Returns a single attendance record joined with the student,
 *       class, and subject names.
 *     tags:
 *       - Attendance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Attendance record
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       404: { description: Attendance record not found }
 *       500: { description: Server error }
 *   put:
 *     summary: Update attendance record
 *     description: |
 *       Updates `status` (required) and optionally `remarks` on an
 *       existing attendance record. Restricted to admin or teacher
 *       roles.
 *     tags:
 *       - Attendance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - status
 *             properties:
 *               status:
 *                 type: string
 *                 enum: [present, absent, late, excused]
 *               remarks: { type: string }
 *     responses:
 *       200:
 *         description: Updated record
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Status is required }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Teacher only }
 *       404: { description: Attendance record not found }
 *       500: { description: Server error }
 */
router.get('/:id', auth, (req, res) => {
  try {
    const record = getDb().prepare(`
      SELECT a.*, 
        u.first_name || ' ' || u.last_name as student_name,
        c.name as class_name,
        s.name as subject_name
      FROM attendance a
      JOIN users u ON a.student_id = u.id
      LEFT JOIN classes c ON a.class_id = c.id
      LEFT JOIN subjects s ON a.subject_id = s.id
      WHERE a.id = ? AND a.is_deleted = 0
    `).get(req.params.id);
    
    if (!record) {
      return res.status(404).json({ error: 'Attendance record not found' });
    }
    
    res.json(record);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Mark attendance
router.post('/', auth, adminOrTeacher, (req, res) => {
  try {
    const { student_id, class_id, subject_id, date, status, remarks } = req.body;

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

    // Phase 1 PR 1: if a class_id is supplied, the user must be authorised
    // for that class (admin OR form tutor OR active cover grant). Teachers
    // without a class_id payload can still pass the adminOrTeacher check
    // and write an attendance row with class_id NULL — same as before.
    if (class_id && !canMarkForClass(req, Number(class_id))) {
      return res.status(403).json({
        error: 'You are not authorised to mark attendance for this class',
      });
    }

    // Validate status
    const validStatuses = ['present', 'absent', 'late', 'excused'];
    if (!validStatuses.includes(status)) {
      return res.status(400).json({ error: `Status must be one of: ${validStatuses.join(', ')}` });
    }
    
    // Check if already exists (upsert)
    const existing = getDb().prepare('SELECT id FROM attendance WHERE student_id = ? AND date = ? AND subject_id IS ?').get(student_id, date, subject_id || null);
    
    if (existing) {
      getDb().prepare(`
        UPDATE attendance SET
          status = ?,
          remarks = ?,
          marked_by = ?,
          class_id = COALESCE(?, class_id),
          updated_at = datetime(\'now\', \'localtime\'),
          sync_status = 'pending'
        WHERE id = ?
      `).run(status, remarks, req.user.id, class_id, existing.id);
      
      const updated = getDb().prepare('SELECT * FROM attendance WHERE id = ?').get(existing.id);
      return res.json(updated);
    }
    
    const uid = uuidv4();
    const result = getDb().prepare(`
      INSERT INTO attendance (uid, student_id, class_id, subject_id, date, status, remarks, marked_by)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `).run(uid, student_id, class_id, subject_id, date, status, remarks, req.user.id);
    
    const record = getDb().prepare(`
      SELECT a.*, u.first_name || ' ' || u.last_name as student_name, c.name as class_name
      FROM attendance a
      JOIN users u ON a.student_id = u.id
      LEFT JOIN classes c ON a.class_id = c.id
      WHERE a.id = ?
    `).get(result.lastInsertRowid);
    
    res.status(201).json(record);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Bulk mark attendance
/**
 * @openapi
 * /api/attendance/bulk:
 *   post:
 *     summary: Bulk upsert attendance
 *     description: |
 *       Upserts attendance records in a single transaction. Each
 *       record is matched on `(student_id, date)` and updated or
 *       inserted. For teacher callers, `class_id` is required and
 *       they may only submit attendance for a class they own or
 *       teach; admins and principals are unrestricted. Each record
 *       must include `student_id`, `date`, and `status`; a top-level
 *       `date` may be supplied and is used as the default.
 *     tags:
 *       - Attendance
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - records
 *             properties:
 *               date: { type: string, format: date }
 *               class_id: { type: integer }
 *               subject_id: { type: integer }
 *               records:
 *                 type: array
 *                 items:
 *                   type: object
 *                   required:
 *                     - student_id
 *                     - date
 *                     - status
 *                   properties:
 *                     student_id: { type: integer }
 *                     class_id: { type: integer }
 *                     subject_id: { type: integer }
 *                     date: { type: string, format: date }
 *                     status:
 *                       type: string
 *                       enum: [present, absent, late, excused]
 *                     remarks: { type: string }
 *     responses:
 *       201:
 *         description: Counts of inserted and updated records
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *                 inserted: { type: integer }
 *                 updated: { type: integer }
 *       400: { description: Validation error or class_id required for teacher }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Teacher only, or teacher does not own the class }
 *       404: { description: Class not found }
 *       500: { description: Server error }
 */
router.post('/bulk', auth, adminOrTeacher, (req, res) => {
  try {
    const { date, records, class_id, subject_id } = req.body;

    if (!Array.isArray(records) || records.length === 0) {
      return res.status(400).json({ error: 'Records array required' });
    }

    // Validate all records first
    for (const record of records) {
      const recordDate = record.date || date;
      if (!record.student_id || !recordDate || !record.status) {
        return res.status(400).json({ error: 'Each record must have student_id, date, and status' });
      }
    }

    // Teacher scope check: a teacher can only mark attendance for a class
    // they own (class_teacher_id) OR teach (subjects.teacher_id). Admins
    // and principals are unrestricted.
    if (req.user.role === 'teacher') {
      const targetClassId = parseInt(class_id, 10);
      if (!targetClassId || Number.isNaN(targetClassId)) {
        return res.status(400).json({ error: 'class_id is required for teacher bulk submit' });
      }
      const cls = getDb().prepare(
        'SELECT id, class_teacher_id FROM classes WHERE id = ? AND is_deleted = 0'
      ).get(targetClassId);
      if (!cls) {
        return res.status(404).json({ error: 'Class not found' });
      }
      const teaches = getDb().prepare(
        `SELECT 1 FROM subjects WHERE class_id = ? AND teacher_id = ? AND is_deleted = 0 LIMIT 1`
      ).get(targetClassId, req.user.id);
      if (cls.class_teacher_id !== req.user.id && !teaches) {
        return res.status(403).json({ error: 'You do not teach this class' });
      }
    }

    const upsert = getDb().prepare(`
      INSERT INTO attendance (uid, student_id, class_id, subject_id, date, status, remarks, marked_by)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `);

    const upsertMany = getDb().transaction((records) => {
      let inserted = 0;
      let updated = 0;

      for (const record of records) {
        const recordDate = record.date || date;
        const existing = getDb().prepare('SELECT id FROM attendance WHERE student_id = ? AND date = ?').get(record.student_id, recordDate);

        if (existing) {
          getDb().prepare(`
            UPDATE attendance SET status = ?, remarks = ?, marked_by = ?, updated_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
            WHERE id = ?
          `).run(record.status, record.remarks, req.user.id, existing.id);
          updated++;
        } else {
          // Prefer the explicit class_id/subject_id passed in the request body
          // (the page sends them so we can lock the row to a specific class).
          // Fall back to whatever the record carries, then to NULL.
          const uid = uuidv4();
          upsert.run(
            uid,
            record.student_id,
            class_id ?? record.class_id ?? null,
            subject_id ?? record.subject_id ?? null,
            recordDate,
            record.status,
            record.remarks,
            req.user.id
          );
          inserted++;
        }
      }

      return { inserted, updated };
    });
    
    const result = upsertMany(records);
    res.status(201).json({ success: true, ...result });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Update attendance
router.put('/:id', auth, adminOrTeacher, (req, res) => {
  try {
    const { status, remarks } = req.body;
    
    if (!status) {
      return res.status(400).json({ error: 'Status is required' });
    }
    
    getDb().prepare(`
      UPDATE attendance SET
        status = ?,
        remarks = COALESCE(?, remarks),
        marked_by = ?,
        updated_at = datetime(\'now\', \'localtime\'),
        sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(status, remarks, req.user.id, req.params.id);
    
    const updated = getDb().prepare(`
      SELECT a.*, u.first_name || ' ' || u.last_name as student_name
      FROM attendance a
      JOIN users u ON a.student_id = u.id
      WHERE a.id = ?
    `).get(req.params.id);
    
    if (!updated) {
      return res.status(404).json({ error: 'Attendance record not found' });
    }
    
    res.json(updated);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ DAILY ATTENDANCE ============

// Get class attendance for a date
/**
 * @openapi
 * /api/attendance/class/{classId}/date/{date}:
 *   get:
 *     summary: Get class roster with attendance for a date
 *     description: |
 *       Returns the active students enrolled in the class joined
 *       with that day's attendance row (if any). Useful for taking
 *       roll-call in a class.
 *     tags:
 *       - Attendance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: classId
 *         required: true
 *         schema: { type: integer }
 *       - in: path
 *         name: date
 *         required: true
 *         schema: { type: string, format: date }
 *     responses:
 *       200:
 *         description: Roster with attendance flags
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 date: { type: string, format: date }
 *                 class_id: { type: integer }
 *                 total_students: { type: integer }
 *                 marked: { type: integer }
 *                 records:
 *                   type: array
 *                   items: { type: object }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.get('/class/:classId/date/:date', auth, (req, res) => {
  try {
    const { classId: class_id, date } = req.params;
    
    // Get all enrolled students
    const students = getDb().prepare(`
      SELECT u.id, u.first_name, u.last_name, e.roll_number
      FROM enrollments e
      JOIN users u ON e.student_id = u.id
      WHERE e.class_id = ? AND e.status = 'active' AND e.is_deleted = 0 AND u.is_deleted = 0
      ORDER BY e.roll_number, u.last_name
    `).all(class_id);
    
    // Get attendance for that date
    const attendance = getDb().prepare(`
      SELECT * FROM attendance
      WHERE class_id = ? AND date = ? AND is_deleted = 0
    `).all(class_id, date);
    
    // Map attendance to students
    const attendanceMap = {};
    attendance.forEach(a => { attendanceMap[a.student_id] = a; });
    
    const result = students.map(s => ({
      ...s,
      attendance: attendanceMap[s.id] || null
    }));
    
    res.json({
      date,
      class_id: parseInt(class_id),
      total_students: students.length,
      marked: attendance.length,
      records: result
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ ATTENDANCE REPORTS ============

// Get attendance summary
/**
 * @openapi
 * /api/attendance/reports/summary:
 *   get:
 *     summary: Attendance summary report
 *     description: |
 *       Returns aggregate counts and percentages of attendance
 *       status (present, absent, late, excused) over an optional
 *       date range, optionally filtered to a single class.
 *       Restricted to admin or teacher roles.
 *     tags:
 *       - Attendance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: class_id
 *         schema: { type: integer }
 *       - in: query
 *         name: start_date
 *         schema: { type: string, format: date }
 *       - in: query
 *         name: end_date
 *         schema: { type: string, format: date }
 *     responses:
 *       200:
 *         description: Aggregate counts and percentages
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Teacher only }
 *       500: { description: Server error }
 */
router.get('/reports/summary', auth, adminOrTeacher, (req, res) => {
  try {
    const { class_id, start_date, end_date } = req.query;
    
    let whereClause = 'a.is_deleted = 0';
    const params = [];
    
    if (class_id) {
      whereClause += ' AND a.class_id = ?';
      params.push(class_id);
    }
    if (start_date) {
      whereClause += ' AND a.date >= ?';
      params.push(start_date);
    }
    if (end_date) {
      whereClause += ' AND a.date <= ?';
      params.push(end_date);
    }
    
    const summary = getDb().prepare(`
      SELECT 
        COUNT(*) as total_records,
        SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) as present,
        SUM(CASE WHEN a.status = 'absent' THEN 1 ELSE 0 END) as absent,
        SUM(CASE WHEN a.status = 'late' THEN 1 ELSE 0 END) as late,
        SUM(CASE WHEN a.status = 'excused' THEN 1 ELSE 0 END) as excused
      FROM attendance a
      WHERE ${whereClause}
    `).get(...params);
    
    res.json({
      ...summary,
      present_percentage: summary.total_records > 0 ? ((summary.present / summary.total_records) * 100).toFixed(1) : 0,
      absent_percentage: summary.total_records > 0 ? ((summary.absent / summary.total_records) * 100).toFixed(1) : 0
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Get student attendance report
/**
 * @openapi
 * /api/attendance/reports/student/{studentId}:
 *   get:
 *     summary: Per-student attendance history
 *     description: |
 *       Returns the attendance history for one student over an
 *       optional date range, plus a summary count by status.
 *       Students may only request their own history; other roles
 *       may request any student.
 *     tags:
 *       - Attendance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: studentId
 *         required: true
 *         schema: { type: integer }
 *       - in: query
 *         name: start_date
 *         schema: { type: string, format: date }
 *       - in: query
 *         name: end_date
 *         schema: { type: string, format: date }
 *     responses:
 *       200:
 *         description: History with status summary
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 records:
 *                   type: array
 *                   items: { type: object }
 *                 summary: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Access denied }
 *       500: { description: Server error }
 */
router.get('/reports/student/:studentId', auth, (req, res) => {
  try {
    const studentId = parseInt(req.params.studentId);
    
    // Check access
    if (req.user.role === 'student' && req.user.id !== studentId) {
      return res.status(403).json({ error: 'Access denied' });
    }
    
    const { start_date, end_date } = req.query;
    
    let query = `
      SELECT a.*, c.name as class_name, s.name as subject_name
      FROM attendance a
      LEFT JOIN classes c ON a.class_id = c.id
      LEFT JOIN subjects s ON a.subject_id = s.id
      WHERE a.student_id = ? AND a.is_deleted = 0
    `;
    const params = [studentId];
    
    if (start_date) {
      query += ' AND a.date >= ?';
      params.push(start_date);
    }
    if (end_date) {
      query += ' AND a.date <= ?';
      params.push(end_date);
    }
    
    query += ' ORDER BY a.date DESC';
    
    const records = getDb().prepare(query).all(...params);
    
    // Calculate summary
    const summary = {
      total: records.length,
      present: records.filter(r => r.status === 'present').length,
      absent: records.filter(r => r.status === 'absent').length,
      late: records.filter(r => r.status === 'late').length,
      excused: records.filter(r => r.status === 'excused').length
    };
    
    res.json({ records, summary });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ MY ATTENDANCE (Student) ============

/**
 * @openapi
 * /api/attendance/my:
 *   get:
 *     summary: My attendance (current user)
 *     description: |
 *       Returns the calling user's own attendance history over an
 *       optional date range, plus a summary count by status. The
 *       route is scoped to `req.user.id` (i.e. the JWT subject);
 *       the path is `/api/attendance/my` and applies to any role
 *       that has attendance rows attached to their user id, most
 *       commonly students.
 *     tags:
 *       - Attendance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: start_date
 *         schema: { type: string, format: date }
 *       - in: query
 *         name: end_date
 *         schema: { type: string, format: date }
 *     responses:
 *       200:
 *         description: Caller's attendance history with status summary
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 records:
 *                   type: array
 *                   items: { type: object }
 *                 summary: { type: object }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.get('/my', auth, (req, res) => {
  try {
    const { start_date, end_date } = req.query;
    
    let query = `
      SELECT a.*, c.name as class_name
      FROM attendance a
      LEFT JOIN classes c ON a.class_id = c.id
      WHERE a.student_id = ? AND a.is_deleted = 0
    `;
    const params = [req.user.id];
    
    if (start_date) {
      query += ' AND a.date >= ?';
      params.push(start_date);
    }
    if (end_date) {
      query += ' AND a.date <= ?';
      params.push(end_date);
    }
    
    query += ' ORDER BY a.date DESC';
    
    const records = getDb().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,
      late: records.filter(r => r.status === 'late').length,
      excused: records.filter(r => r.status === 'excused').length
    };
    
    res.json({ records, summary });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

module.exports = router;