/**
 * Hostel Module Controller
 */
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' });
  }
};

const adminOnly = (req, res, next) => {
  if (!['school_admin', 'systems_admin', 'principal', 'warden'].includes(req.user.role)) {
    return res.status(403).json({ error: 'Restricted access' });
  }
  next();
};

// ============ HOSTELS ============

/**
 * @openapi
 * /api/hostels:
 *   get:
 *     summary: List hostels
 *     description: |
 *       Returns all non-deleted hostels with the warden's name,
 *       room count, total bed capacity, and currently occupied bed
 *       count.
 *     tags:
 *       - Hostels
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Hostel list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       500:
 *         description: Server error
 *         content:
 *           application/json:
 *             schema: { type: object }
 *   post:
 *     summary: Create hostel
 *     description: |
 *       Creates a new hostel record. Restricted to admin or
 *       warden roles (`school_admin`, `systems_admin`, `principal`,
 *       `warden`).
 *     tags:
 *       - Hostels
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - name
 *             properties:
 *               name: { type: string }
 *               code: { type: string }
 *               type: { type: string }
 *               address: { type: string }
 *               warden_id: { type: integer }
 *               phone: { type: string }
 *               description: { type: string }
 *     responses:
 *       200:
 *         description: Created hostel
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Admin only }
 */
router.get('/', auth, adminOnly, (req, res) => {
  try {
    const hostels = db.prepare(`
      SELECT h.*, u.first_name || ' ' || u.last_name as warden_name,
        (SELECT COUNT(*) FROM rooms WHERE hostel_id = h.id AND is_deleted = 0) as room_count,
        (SELECT SUM(bed_count) FROM rooms WHERE hostel_id = h.id AND is_deleted = 0) as total_capacity,
        (SELECT COUNT(*) FROM room_assignments WHERE room_id IN (SELECT id FROM rooms WHERE hostel_id = h.id) AND status = 'active' AND is_deleted = 0) as occupied_count
      FROM hostels h
      LEFT JOIN users u ON h.warden_id = u.id
      WHERE h.is_deleted = 0
    `).all();
    res.json(hostels);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.post('/', auth, adminOnly, (req, res) => {
  const { name, code, type, address, warden_id, phone, description } = req.body;
  const uid = uuidv4();
  try {
    const result = db.prepare(`
      INSERT INTO hostels (uid, name, code, type, address, warden_id, phone, description, sync_status)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'pending')
    `).run(uid, name, code, type, address, warden_id || null, phone || null, description || null);
    res.json({ id: result.lastInsertRowid, uid });
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

// ============ ROOMS ============

/**
 * @openapi
 * /api/hostels/{hostelId}/rooms:
 *   get:
 *     summary: List rooms in a hostel
 *     description: |
 *       Returns rooms for the given hostel, with the room-type
 *       name, max bed count, and currently occupied beds.
 *     tags:
 *       - Hostels
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: hostelId
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Room list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/:hostelId/rooms', auth, (req, res) => {
  const rooms = db.prepare(`
    SELECT r.*, rt.name as type_name, rt.bed_count as max_beds,
      (SELECT COUNT(*) FROM room_assignments WHERE room_id = r.id AND status = 'active' AND is_deleted = 0) as occupied_beds
    FROM rooms r
    LEFT JOIN room_types rt ON r.room_type_id = rt.id
    WHERE r.hostel_id = ? AND r.is_deleted = 0
  `).all(req.params.hostelId);
  res.json(rooms);
});

/**
 * @openapi
 * /api/hostels/rooms:
 *   post:
 *     summary: Create a room
 *     description: |
 *       Adds a new room under the given hostel. Restricted to
 *       admin or warden roles.
 *     tags:
 *       - Hostels
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - hostel_id
 *               - room_number
 *             properties:
 *               hostel_id: { type: integer }
 *               room_type_id: { type: integer }
 *               room_number: { type: string }
 *               floor: { type: string }
 *               bed_count: { type: integer }
 *               description: { type: string }
 *     responses:
 *       200:
 *         description: Created room
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Admin only }
 */
router.post('/rooms', auth, adminOnly, (req, res) => {
  const { hostel_id, room_type_id, room_number, floor, bed_count, description } = req.body;
  const uid = uuidv4();
  try {
    const result = db.prepare(`
      INSERT INTO rooms (uid, hostel_id, room_type_id, room_number, floor, bed_count, status, description, sync_status)
      VALUES (?, ?, ?, ?, ?, ?, 'available', ?, 'pending')
    `).run(uid, hostel_id, room_type_id, room_number, floor, bed_count, description || null);
    res.json({ id: result.lastInsertRowid, uid });
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

// ============ ASSIGNMENTS ============

/**
 * @openapi
 * /api/hostels/assignments:
 *   get:
 *     summary: List room assignments
 *     description: |
 *       Returns all room assignments with student name, hostel,
 *       and room number.
 *     tags:
 *       - Hostels
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Assignment list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Assign student to room
 *     description: |
 *       Assigns a student to a bed in the given room. Refuses when
 *       the room is full or the IDs are invalid. Updates the room
 *       status (`available` or `occupied`) accordingly. Restricted
 *       to admin or warden roles.
 *     tags:
 *       - Hostels
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - student_id
 *               - room_id
 *             properties:
 *               student_id: { type: integer }
 *               room_id: { type: integer }
 *               bed_number: { type: string }
 *               start_date: { type: string, format: date }
 *               end_date: { type: string, format: date }
 *               remarks: { type: string }
 *     responses:
 *       200:
 *         description: Assignment created
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400:
 *         description: Invalid IDs, room full, or room not found
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Admin only }
 */
router.get('/assignments', auth, (req, res) => {
  const assignments = db.prepare(`
    SELECT ra.*, u.first_name, u.last_name, h.name as hostel_name, r.room_number
    FROM room_assignments ra
    JOIN users u ON ra.student_id = u.id
    JOIN rooms r ON ra.room_id = r.id
    JOIN hostels h ON r.hostel_id = h.id
    WHERE ra.is_deleted = 0
  `).all();
  res.json(assignments);
});

router.post('/assignments', auth, adminOnly, (req, res) => {
  const { student_id, room_id, bed_number, start_date, end_date, remarks } = req.body;
  const uid = uuidv4();
  
  const studentId = parseInt(student_id);
  const roomId = parseInt(room_id);
  
  if (isNaN(studentId) || isNaN(roomId)) {
    return res.status(400).json({ error: 'Invalid Student ID or Room ID' });
  }
  
  const performAssignment = db.transaction(() => {
    // Check if room has space
    const room = db.prepare('SELECT bed_count FROM rooms WHERE id = ?').get(roomId);
    if (!room) {
      throw new Error('Room not found');
    }
    const occupied = db.prepare("SELECT COUNT(*) as count FROM room_assignments WHERE room_id = ? AND status = 'active'").get(roomId).count;
    
    if (occupied >= room.bed_count) {
      throw new Error('Room is fully occupied');
    }

    const assignedBy = db.prepare('SELECT id FROM users WHERE id = ?').get(req.user.id) ? req.user.id : null;
    const endDateVal = end_date && end_date !== '' ? end_date : null;

    const result = db.prepare(`
      INSERT INTO room_assignments (uid, student_id, room_id, bed_number, start_date, end_date, status, remarks, assigned_by, sync_status)
      VALUES (?, ?, ?, ?, ?, ?, 'active', ?, ?, 'pending')
    `).run(uid, studentId, roomId, bed_number, start_date, endDateVal, remarks || null, assignedBy);

    // Update room status if now full
    if (occupied + 1 >= room.bed_count) {
      db.prepare("UPDATE rooms SET status = 'occupied', updated_at = datetime('now', 'localtime'), sync_status = 'pending' WHERE id = ?").run(roomId);
    } else {
      db.prepare("UPDATE rooms SET status = 'available', updated_at = datetime('now', 'localtime'), sync_status = 'pending' WHERE id = ?").run(roomId);
    }

    return result.lastInsertRowid;
  });

  try {
    const id = performAssignment();
    res.json({ id, uid });
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

/**
 * @openapi
 * /api/hostels/assignments/{id}/checkout:
 *   post:
 *     summary: Check out a room assignment
 *     description: |
 *       Marks the assignment as `inactive`, sets `end_date` to
 *       today, and resets the room to `available`. Restricted to
 *       admin or warden roles.
 *     tags:
 *       - Hostels
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Checked out
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400:
 *         description: Assignment not found
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Admin only }
 */
router.post('/assignments/:id/checkout', auth, adminOnly, (req, res) => {
  const assignmentId = req.params.id;
  
  const performCheckout = db.transaction(() => {
    const assignment = db.prepare('SELECT room_id FROM room_assignments WHERE id = ?').get(assignmentId);
    if (!assignment) throw new Error('Assignment not found');

    db.prepare("UPDATE room_assignments SET status = 'inactive', end_date = date('now', 'localtime'), updated_at = datetime('now', 'localtime'), sync_status = 'pending' WHERE id = ?").run(assignmentId);
    db.prepare("UPDATE rooms SET status = 'available', updated_at = datetime('now', 'localtime'), sync_status = 'pending' WHERE id = ?").run(assignment.room_id);
  });

  try {
    performCheckout();
    res.json({ success: true });
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

// ============ METADATA ============

/**
 * @openapi
 * /api/hostels/room-types:
 *   get:
 *     summary: List room types
 *     description: |
 *       Returns the configured room types (capacity, description,
 *       etc.).
 *     tags:
 *       - Hostels
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Room-type list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/room-types', auth, (req, res) => {
  const types = db.prepare('SELECT * FROM room_types WHERE is_deleted = 0').all();
  res.json(types);
});

// ============ ATTENDANCE ============

/**
 * @openapi
 * /api/hostels/attendance:
 *   get:
 *     summary: List hostel attendance
 *     description: |
 *       Returns hostel roll-call records, filterable by `hostel_id`
 *       and `date`.
 *     tags:
 *       - Hostels
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: hostel_id
 *         schema: { type: integer }
 *       - in: query
 *         name: date
 *         schema: { type: string, format: date }
 *     responses:
 *       200:
 *         description: Attendance records
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/attendance', auth, (req, res) => {
  const { hostel_id, date } = req.query;
  let query = `
    SELECT ha.*, u.first_name, u.last_name, r.room_number
    FROM hostel_attendance ha
    JOIN users u ON ha.student_id = u.id
    LEFT JOIN rooms r ON ha.room_id = r.id
    WHERE ha.is_deleted = 0
  `;
  const params = [];
  
  if (hostel_id) { query += ' AND ha.hostel_id = ?'; params.push(hostel_id); }
  if (date) { query += ' AND ha.date = ?'; params.push(date); }
  
  const attendance = db.prepare(query).all(...params);
  res.json(attendance);
});

/**
 * @openapi
 * /api/hostels/attendance/bulk:
 *   post:
 *     summary: Bulk upsert hostel attendance
 *     description: |
 *       Upserts hostel roll-call records in a single transaction.
 *       Each record is matched on `(hostel_id, student_id, date)`
 *       and updated or inserted accordingly. Restricted to admin
 *       or warden roles.
 *     tags:
 *       - Hostels
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - records
 *             properties:
 *               records:
 *                 type: array
 *                 items:
 *                   type: object
 *                   required:
 *                     - hostel_id
 *                     - student_id
 *                     - date
 *                     - status
 *                   properties:
 *                     hostel_id: { type: integer }
 *                     room_id: { type: integer }
 *                     student_id: { type: integer }
 *                     date: { type: string, format: date }
 *                     status: { type: string }
 *     responses:
 *       200:
 *         description: Counts of processed records
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *                 count: { type: integer }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Admin only }
 */
router.post('/attendance/bulk', auth, adminOnly, (req, res) => {
  const { records } = req.body; // Array of { hostel_id, room_id, student_id, date, status }
  const upsert = db.prepare(`
    INSERT INTO hostel_attendance (uid, hostel_id, room_id, student_id, date, status, marked_by, sync_status)
    VALUES (?, ?, ?, ?, ?, ?, ?, 'pending')
  `);
  
  const processBulk = db.transaction((records) => {
    records.forEach(r => {
      const existing = db.prepare('SELECT id FROM hostel_attendance WHERE hostel_id = ? AND student_id = ? AND date = ?').get(r.hostel_id, r.student_id, r.date);
      if (existing) {
        db.prepare('UPDATE hostel_attendance SET status = ?, marked_by = ?, updated_at = datetime(\'now\', \'localtime\'), sync_status = "pending" WHERE id = ?').run(r.status, req.user.id, existing.id);
      } else {
        upsert.run(uuidv4(), r.hostel_id, r.room_id, r.student_id, r.date, r.status, req.user.id);
      }
    });
  });

  try {
    processBulk(records);
    res.json({ success: true, count: records.length });
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

module.exports = router;
