/**
 * Hostel Attendance Controller
 *
 * Daily roll-call for students housed in a hostel. Backed by the
 * `hostel_attendance` table. Expected roster is derived from
 * `room_assignments` (active assignments).
 *
 * Conventions (per .harness/docs/conventions.md):
 *   - All writes set sync_status = 'pending'.
 *   - Reads include WHERE is_deleted = 0.
 *   - All prepared statements use ? placeholders (no string concat).
 *   - Auth: every protected route goes through `auth`; admin / principal /
 *     teacher can write attendance. Students / parents are read-only and
 *     scoped to their own record.
 */

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

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

const VALID_STATUSES = ['present', 'absent', 'excused'];

// ============ LIST ============

/**
 * @openapi
 * /api/hostel-attendance:
 *   get:
 *     summary: List hostel attendance entries
 *     description: |
 *       Returns hostel attendance records, filterable by hostel, room,
 *       and date range. Students see only their own record; parents see
 *       records for students linked via `parent_students`.
 *     tags:
 *       - Hostel Attendance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: hostel_id
 *         schema: { type: integer }
 *       - in: query
 *         name: room_id
 *         schema: { type: integer }
 *       - in: query
 *         name: date
 *         schema: { type: string, format: date }
 *       - in: query
 *         name: from
 *         schema: { type: string, format: date }
 *       - in: query
 *         name: to
 *         schema: { type: string, format: date }
 *       - in: query
 *         name: student_id
 *         schema: { type: integer }
 *         description: Restrict results to a single student (parents only)
 *     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 }
 */
router.get('/', auth, (req, res) => {
  try {
    const { hostel_id, room_id, date, from, to } = req.query;
    let query = `
      SELECT ha.*,
        h.name as hostel_name, h.code as hostel_code,
        rm.room_number,
        u.first_name || ' ' || u.last_name as student_name,
        m.first_name || ' ' || m.last_name as marked_by_name
      FROM hostel_attendance ha
      LEFT JOIN hostels h ON ha.hostel_id = h.id
      LEFT JOIN rooms rm ON ha.room_id = rm.id
      JOIN users u ON ha.student_id = u.id
      LEFT JOIN users m ON ha.marked_by = m.id
      WHERE ha.is_deleted = 0
    `;
    const params = [];

    // Role-based scoping: students see only themselves; parents see linked students.
    if (req.user.role === 'student') {
      query += ' AND ha.student_id = ?';
      params.push(req.user.id);
    } else if (req.user.role === 'parent') {
      if (req.query.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, req.query.student_id);
        if (!isLinked) {
          return res.status(403).json({ error: 'Not linked to this student' });
        }
        query += ' AND ha.student_id = ?';
        params.push(req.query.student_id);
      } else {
        query += ' AND ha.student_id IN (SELECT student_id FROM parent_students WHERE parent_id = ? AND is_deleted = 0)';
        params.push(req.user.id);
      }
    }

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

    const rows = getDb().prepare(query).all(...params);
    res.json(rows);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/hostel-attendance/roster:
 *   get:
 *     summary: Get hostel roster for a date
 *     description: |
 *       Active room assignments joined with that day's attendance row.
 *       `date` defaults to today.
 *     tags:
 *       - Hostel Attendance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: hostel_id
 *         schema: { type: integer }
 *       - in: query
 *         name: room_id
 *         schema: { type: integer }
 *       - in: query
 *         name: date
 *         schema: { type: string, format: date }
 *     responses:
 *       200:
 *         description: 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 = req.query.date || new Date().toISOString().split('T')[0];
    const { hostel_id, room_id } = req.query;

    let query = `
      SELECT
        ra.id as assignment_id,
        ra.room_id,
        ra.bed_number,
        ra.student_id,
        u.first_name || ' ' || u.last_name as student_name,
        rm.room_number,
        rm.hostel_id,
        h.name as hostel_name,
        ha.id as attendance_id,
        ha.status,
        ha.date
      FROM room_assignments ra
      JOIN users u ON ra.student_id = u.id
      JOIN rooms rm ON ra.room_id = rm.id
      LEFT JOIN hostels h ON rm.hostel_id = h.id
      LEFT JOIN hostel_attendance ha
        ON ha.student_id = ra.student_id
        AND ha.date = ?
        AND ha.is_deleted = 0
      WHERE ra.status = 'active'
        AND ra.is_deleted = 0
        AND u.is_deleted = 0
    `;
    const params = [date];

    if (hostel_id) { query += ' AND rm.hostel_id = ?'; params.push(hostel_id); }
    if (room_id)   { query += ' AND ra.room_id = ?';   params.push(room_id); }
    query += ' ORDER BY h.name, rm.room_number, u.last_name';

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

/**
 * @openapi
 * /api/hostel-attendance/bulk:
 *   post:
 *     summary: Bulk upsert hostel attendance
 *     description: |
 *       Upserts attendance records in a single transaction. Each
 *       record is matched on `(hostel_id, student_id, date)` and
 *       updated or inserted. Restricted to staff roles
 *       (`school_admin`, `systems_admin`, `principal`, `teacher`).
 *     tags:
 *       - Hostel Attendance
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - hostel_id
 *               - date
 *               - records
 *             properties:
 *               hostel_id: { type: integer }
 *               room_id: { type: integer }
 *               date: { type: string, format: date }
 *               records:
 *                 type: array
 *                 items:
 *                   type: object
 *                   required:
 *                     - student_id
 *                     - status
 *                   properties:
 *                     student_id: { type: integer }
 *                     status:
 *                       type: string
 *                       enum: [present, absent, 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 }
 *                 total: { type: integer }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Staff only }
 *       500: { description: Server error }
 */
router.post('/bulk', auth, staffWrite, (req, res) => {
  try {
    const { hostel_id, room_id, date, records } = req.body;
    if (!hostel_id || !date || !Array.isArray(records) || records.length === 0) {
      return res.status(400).json({ error: 'hostel_id, date, and non-empty records array are required' });
    }
    for (const r of records) {
      if (!r.student_id || !r.status) {
        return res.status(400).json({ error: 'Each record must have student_id and status' });
      }
      if (!VALID_STATUSES.includes(r.status)) {
        return res.status(400).json({ error: `status must be one of: ${VALID_STATUSES.join(', ')}` });
      }
    }

    const findExisting = getDb().prepare(
      'SELECT id FROM hostel_attendance WHERE hostel_id = ? AND student_id = ? AND date = ? AND is_deleted = 0'
    );
    const insertStmt = getDb().prepare(`
      INSERT INTO hostel_attendance (uid, hostel_id, room_id, student_id, date, status, marked_by)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `);
    const updateStmt = getDb().prepare(`
      UPDATE hostel_attendance SET
        status = ?, marked_by = ?, updated_at = CURRENT_TIMESTAMP, sync_status = 'pending'
      WHERE id = ?
    `);

    let inserted = 0;
    let updated = 0;

    const performBulk = getDb().transaction(() => {
      for (const r of records) {
        const existing = findExisting.get(hostel_id, r.student_id, date);
        if (existing) {
          updateStmt.run(r.status, req.user.id, existing.id);
          updated++;
        } else {
          insertStmt.run(uuidv4(), hostel_id, room_id || null, r.student_id, date, r.status, req.user.id);
          inserted++;
        }
      }
    });

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

/**
 * @openapi
 * /api/hostel-attendance/history:
 *   get:
 *     summary: Per-student hostel attendance history
 *     description: |
 *       Returns hostel attendance history for one student. Students see
 *       only their own history; parents must supply `student_id` for
 *       a linked child.
 *     tags:
 *       - Hostel Attendance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: student_id
 *         schema: { type: integer }
 *       - 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: student_id required for parent/staff }
 *       401: { description: Unauthorized }
 *       403: { description: Parent not linked to student }
 *       500: { description: Server error }
 */
router.get('/history', auth, (req, res) => {
  try {
    let studentId = req.query.student_id ? parseInt(req.query.student_id, 10) : null;

    if (req.user.role === 'student') {
      studentId = req.user.id; // force own history
    } else if (req.user.role === 'parent') {
      if (!studentId) {
        return res.status(400).json({ error: 'student_id is required for parent' });
      }
      const isLinked = getDb().prepare(
        'SELECT id FROM parent_students WHERE parent_id = ? AND student_id = ? AND is_deleted = 0'
      ).get(req.user.id, studentId);
      if (!isLinked) {
        return res.status(403).json({ error: 'Not linked to this student' });
      }
    } else {
      if (!studentId) {
        return res.status(400).json({ error: 'student_id is required' });
      }
    }

    const { from, to } = req.query;
    let query = `
      SELECT ha.*, h.name as hostel_name, rm.room_number
      FROM hostel_attendance ha
      LEFT JOIN hostels h ON ha.hostel_id = h.id
      LEFT JOIN rooms rm ON ha.room_id = rm.id
      WHERE ha.student_id = ? AND ha.is_deleted = 0
    `;
    const params = [studentId];
    if (from) { query += ' AND ha.date >= ?'; params.push(from); }
    if (to)   { query += ' AND ha.date <= ?'; params.push(to); }
    query += ' ORDER BY ha.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,
      excused: records.filter(r => r.status === 'excused').length,
    };
    res.json({ student_id: studentId, summary, records });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

module.exports = router;
