/**
 * Transport Attendance Controller
 *
 * Daily roll-call for students taking a transport route. Backed by the
 * `transport_attendance` table. Expected roster is derived from
 * `transport_allocations` (active allocations only).
 *
 * 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/transport-attendance:
 *   get:
 *     summary: List transport attendance entries
 *     description: |
 *       Returns transport attendance records, filterable by route and
 *       date range. Students see only their own record; parents see
 *       records for students linked via `parent_students`.
 *     tags:
 *       - Transport Attendance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: route_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 { route_id, date, from, to } = req.query;
    let query = `
      SELECT ta.*,
        r.name as route_name, r.code as route_code,
        u.first_name || ' ' || u.last_name as student_name,
        m.first_name || ' ' || m.last_name as marked_by_name
      FROM transport_attendance ta
      LEFT JOIN routes r ON ta.route_id = r.id
      JOIN users u ON ta.student_id = u.id
      LEFT JOIN users m ON ta.marked_by = m.id
      WHERE ta.is_deleted = 0
    `;
    const params = [];

    if (req.user.role === 'student') {
      query += ' AND ta.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 ta.student_id = ?';
        params.push(req.query.student_id);
      } else {
        query += ' AND ta.student_id IN (SELECT student_id FROM parent_students WHERE parent_id = ? AND is_deleted = 0)';
        params.push(req.user.id);
      }
    }

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

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

/**
 * @openapi
 * /api/transport-attendance/roster:
 *   get:
 *     summary: Get transport roster for a date
 *     description: |
 *       Active transport allocations for a route joined with that
 *       day's attendance row. `date` defaults to today. `route_id`
 *       is required.
 *     tags:
 *       - Transport Attendance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: route_id
 *         required: true
 *         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 }
 *                 route_id: { type: integer }
 *                 total: { type: integer }
 *                 roster:
 *                   type: array
 *                   items: { type: object }
 *       400: { description: route_id is required }
 *       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 { route_id } = req.query;
    if (!route_id) {
      return res.status(400).json({ error: 'route_id is required' });
    }

    const rows = getDb().prepare(`
      SELECT
        ta.id as allocation_id,
        ta.route_id,
        ta.student_id,
        u.first_name || ' ' || u.last_name as student_name,
        r.name as route_name,
        att.id as attendance_id,
        att.status,
        att.date
      FROM transport_allocations ta
      JOIN users u ON ta.student_id = u.id
      JOIN routes r ON ta.route_id = r.id
      LEFT JOIN transport_attendance att
        ON att.student_id = ta.student_id
        AND att.route_id = ta.route_id
        AND att.date = ?
        AND att.is_deleted = 0
      WHERE ta.route_id = ?
        AND ta.is_active = 1
        AND ta.is_deleted = 0
        AND u.is_deleted = 0
      ORDER BY u.last_name
    `).all(date, route_id);

    res.json({ date, route_id: parseInt(route_id, 10), total: rows.length, roster: rows });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/transport-attendance/bulk:
 *   post:
 *     summary: Bulk upsert transport attendance
 *     description: |
 *       Upserts attendance records in a single transaction. Each
 *       record is matched on `(route_id, student_id, date)` and
 *       updated or inserted. Restricted to staff roles
 *       (`school_admin`, `systems_admin`, `principal`, `teacher`).
 *     tags:
 *       - Transport Attendance
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - route_id
 *               - date
 *               - records
 *             properties:
 *               route_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 { route_id, date, records } = req.body;
    if (!route_id || !date || !Array.isArray(records) || records.length === 0) {
      return res.status(400).json({ error: 'route_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 transport_attendance WHERE route_id = ? AND student_id = ? AND date = ? AND is_deleted = 0'
    );
    const insertStmt = getDb().prepare(`
      INSERT INTO transport_attendance (uid, route_id, student_id, date, status, marked_by)
      VALUES (?, ?, ?, ?, ?, ?)
    `);
    const updateStmt = getDb().prepare(`
      UPDATE transport_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(route_id, r.student_id, date);
        if (existing) {
          updateStmt.run(r.status, req.user.id, existing.id);
          updated++;
        } else {
          insertStmt.run(uuidv4(), route_id, 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/transport-attendance/history:
 *   get:
 *     summary: Per-student transport attendance history
 *     description: |
 *       Returns transport attendance history for one student. Students
 *       see only their own history; parents must supply `student_id`
 *       for a linked child.
 *     tags:
 *       - Transport 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;
    } 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 ta.*, r.name as route_name
      FROM transport_attendance ta
      LEFT JOIN routes r ON ta.route_id = r.id
      WHERE ta.student_id = ? AND ta.is_deleted = 0
    `;
    const params = [studentId];
    if (from) { query += ' AND ta.date >= ?'; params.push(from); }
    if (to)   { query += ' AND ta.date <= ?'; params.push(to); }
    query += ' ORDER BY ta.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;
