/**
 * Club Attendance Controller
 *
 * Daily roll-call for students in a club/sport. Backed by the
 * `club_attendance` table. Expected roster is derived from active
 * `club_memberships`.
 *
 * NOTE: `server/src/controllers/clubs.controller.js` already exposes a
 * minimal attendance surface under `/api/clubs/attendance` and
 * `/api/clubs/attendance/bulk` (kept for backward compatibility — frontend
 * code and external integrations may call those URLs).
 *
 * This controller provides a more consistent module URL
 * (`/api/club-attendance/...`) with a fuller shape: list, roster, bulk,
 * history. Frontends should prefer this URL; do not delete the clubs
 * controller endpoints.
 *
 * Conventions (per .harness/docs/conventions.md):
 *   - All writes set sync_status = 'pending'.
 *   - Reads include WHERE is_deleted = 0.
 *   - All prepared statements use ? placeholders.
 *   - 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 / clubs_head)
const staffWrite = (req, res, next) => {
  if (!['admin', 'school_admin', 'systems_admin', 'principal', 'teacher', 'clubs_head'].includes(req.user.role)) {
    return res.status(403).json({ error: 'Staff access required' });
  }
  next();
};

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

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

/**
 * @openapi
 * /api/club-attendance:
 *   get:
 *     summary: List club attendance entries
 *     description: |
 *       Returns club attendance records, filterable by club and date
 *       range. Students see only their own record; parents see records
 *       for students linked via `parent_students` (optionally scoped to
 *       one child via `student_id`).
 *     tags:
 *       - Club Attendance
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: club_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 { club_id, date, from, to } = req.query;
    let query = `
      SELECT ca.*,
        c.name as club_name, c.type as club_type,
        u.first_name || ' ' || u.last_name as student_name,
        m.first_name || ' ' || m.last_name as marked_by_name
      FROM club_attendance ca
      LEFT JOIN clubs c ON ca.club_id = c.id
      JOIN users u ON ca.student_id = u.id
      LEFT JOIN users m ON ca.marked_by = m.id
      WHERE ca.is_deleted = 0
    `;
    const params = [];

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

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

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

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

    const rows = getDb().prepare(`
      SELECT
        cm.id as membership_id,
        cm.club_id,
        cm.student_id,
        cm.role as club_role,
        u.first_name || ' ' || u.last_name as student_name,
        c.name as club_name,
        ca.id as attendance_id,
        ca.status,
        ca.date
      FROM club_memberships cm
      JOIN users u ON cm.student_id = u.id
      JOIN clubs c ON cm.club_id = c.id
      LEFT JOIN club_attendance ca
        ON ca.student_id = cm.student_id
        AND ca.club_id = cm.club_id
        AND ca.date = ?
        AND ca.is_deleted = 0
      WHERE cm.club_id = ?
        AND cm.status = 'active'
        AND cm.is_deleted = 0
        AND u.is_deleted = 0
      ORDER BY u.last_name
    `).all(date, club_id);

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

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