/**
 * Student Medical Records Module
 *
 * Backend API Controller
 *
 * Endpoints:
 *   GET    /api/medical/profiles/:student_uid  — full or role-filtered profile
 *   POST   /api/medical/profiles               — create (nurse/school_admin/systems_admin/principal)
 *   PUT    /api/medical/profiles/:uid          — update (nurse/school_admin/systems_admin/principal)
 *   GET    /api/medical/logs/:student_uid      — medication administrations
 *   POST   /api/medical/logs                   — log a dispensation (nurse/school_admin/systems_admin/principal)
 *   GET    /api/medical/history/:student_uid   — illness/injury/hospitalization history
 *   POST   /api/medical/history                — record an event (nurse/school_admin/systems_admin/principal)
 *   GET    /api/medical/dining/allergies       — kitchen-safe DTO
 *
 * RBAC matrix (see student_medical_info_plan.md):
 *   - nurse / school_admin / systems_admin / principal: full read+write
 *   - parent: read+write own children only (parent_students link)
 *   - student: read self only
 *   - teacher: read only is_visible_to_teachers rows from history, doctor_notes stripped
 *   - dining_staff: only the /api/medical/dining/allergies slim endpoint
 *
 * The endpoints accept `:student_uid` (cross-system uid). We resolve to the
 * numeric `users.id` once via resolveStudentId() at the top of each handler.
 */

const { v4: uuidv4 } = require('uuid');
const express = require('express');
const path = require('path');
const router = express.Router();

const dbPath = process.env.DB_PATH || path.join(__dirname, '../../data/school.db');
const Database = require('better-sqlite3');
const db = new Database(dbPath);
db.pragma('foreign_keys = ON');

// Inline auth middleware (mirrors exams.controller.js convention)
const auth = (req, res, next) => {
  const token = req.headers.authorization?.split(' ')[1];
  if (!token) return res.status(401).json({ error: 'No token provided' });
  try {
    req.user = require('jsonwebtoken').verify(token, require('../config').jwtSecret);
    next();
  } catch (err) {
    res.status(401).json({ error: 'Invalid token' });
  }
};

// Roles with full medical read/write access
const STAFF_ROLES = new Set(['nurse', 'school_admin', 'systems_admin', 'principal']);

function isStaff(role) {
  return STAFF_ROLES.has(role);
}

// Resolve a URL param that may be either the users.uid (cross-system id,
// a UUID) or the integer users.id. Returns { id, uid } or null.
function resolveStudentId(param) {
  if (param == null) return null;
  // Fast path: integer id
  if (/^\d+$/.test(String(param))) {
    const row = db.prepare('SELECT id, uid FROM users WHERE id = ? AND is_deleted = 0').get(Number(param));
    if (row) return row;
  }
  // Slow path: cross-system uid
  const row = db.prepare('SELECT id, uid FROM users WHERE uid = ? AND is_deleted = 0').get(String(param));
  return row || null;
}

// Verify that the requesting parent is linked to the student via parent_students.
function parentOwnsStudent(parentUserId, studentId) {
  const row = db.prepare(
    'SELECT 1 FROM parent_students WHERE parent_id = ? AND student_id = ? AND is_deleted = 0 LIMIT 1'
  ).get(parentUserId, studentId);
  return !!row;
}

// Strip the column set based on the requesting role.
// Returns a copy of `profile` with restricted fields removed for non-staff readers.
function applyProfileFieldFilter(profile, role) {
  if (!profile) return profile;
  if (isStaff(role)) {
    // Full record.
    return { ...profile };
  }
  if (role === 'student' || role === 'parent') {
    // Students and parents see everything on the profile EXCEPT private
    // nurse-only notes — those don't have a column on this table so the
    // base record is fine. No field stripping needed at this layer.
    return { ...profile };
  }
  // Default: hide clinical detail from non-staff callers.
  const { contraindications, chronic_conditions, doctor_notes, ...safe } = profile;
  return safe;
}

// Slim DTO row for dining hall (kitchen tablet). Strips everything except
// allergy + diet information and emergency contact basics.
function toDiningRow(profileRow) {
  if (!profileRow) return null;
  return {
    student_uid: profileRow.uid,
    student_id: profileRow.student_id,
    blood_group: profileRow.blood_group,
    allergies: profileRow.allergies,
    dietary_restrictions: profileRow.dietary_restrictions,
    emergency_instructions: profileRow.emergency_instructions,
  };
}

// ============ MEDICAL PROFILES ============

/**
 * @openapi
 * /api/medical/profiles/{student_uid}:
 *   get:
 *     summary: Get a student's medical profile
 *     description: |
 *       Returns the medical profile for a student, with role-based field
 *       redaction. Staff (nurse, school_admin, systems_admin, principal)
 *       receive the full record. Parents see their own child's record
 *       via parent_students. Students see their own record. Other roles
 *       are denied.
 *     tags:
 *       - Medical
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: student_uid
 *         required: true
 *         schema: { type: string }
 *     responses:
 *       200: { description: Profile (possibly redacted) }
 *       403: { description: Not allowed for this role }
 *       404: { description: Student not found }
 */
router.get('/profiles/:student_uid', auth, (req, res) => {
  const target = resolveStudentId(req.params.student_uid);
  if (!target) return res.status(404).json({ error: 'Student not found' });

  const role = req.user.role;
  const userId = req.user.id;

  if (role === 'student') {
    if (userId !== target.id) return res.status(403).json({ error: 'Students may only view their own profile' });
  } else if (role === 'parent') {
    if (!parentOwnsStudent(userId, target.id)) return res.status(403).json({ error: 'Not your child' });
  } else if (!isStaff(role)) {
    return res.status(403).json({ error: 'Role not permitted' });
  }

  const profile = db.prepare(
    'SELECT * FROM student_medical_profiles WHERE student_id = ? AND is_deleted = 0'
  ).get(target.id);
  if (!profile) return res.status(200).json(null);

  res.json(applyProfileFieldFilter(profile, role));
});

/**
 * @openapi
 * /api/medical/profiles:
 *   post:
 *     summary: Create a student's medical profile
 *     tags: [Medical]
 *     security: [{ bearerAuth: [] }]
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required: [student_uid]
 *     responses:
 *       201: { description: Created }
 *       400: { description: Validation error }
 *       403: { description: Forbidden }
 */
router.post('/profiles', auth, (req, res) => {
  if (!isStaff(req.user.role) && req.user.role !== 'parent') {
    return res.status(403).json({ error: 'Role not permitted' });
  }

  const {
    student_uid,
    student_id,
    blood_group, allergies, dietary_restrictions,
    contraindications, chronic_conditions, emergency_instructions,
  } = req.body;

  const resolved = resolveStudentId(student_uid ?? student_id);
  if (!resolved) return res.status(400).json({ error: 'student_uid required' });

  if (req.user.role === 'parent' && !parentOwnsStudent(req.user.id, resolved.id)) {
    return res.status(403).json({ error: 'Not your child' });
  }

  // Refuse to create a duplicate profile.
  const existing = db.prepare('SELECT id FROM student_medical_profiles WHERE student_id = ?').get(resolved.id);
  if (existing) return res.status(409).json({ error: 'Profile already exists for this student' });

  const uid = uuidv4();
  try {
    db.prepare(`
      INSERT INTO student_medical_profiles (
        uid, student_id,
        blood_group, allergies, dietary_restrictions,
        contraindications, chronic_conditions, emergency_instructions,
        updated_by, sync_status
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending')
    `).run(
      uid, resolved.id,
      blood_group ?? null,
      allergies ?? null,
      dietary_restrictions ?? null,
      contraindications ?? null,
      chronic_conditions ?? null,
      emergency_instructions ?? null,
      req.user.id,
    );
    const row = db.prepare('SELECT * FROM student_medical_profiles WHERE uid = ?').get(uid);
    res.status(201).json(row);
  } catch (e) {
    res.status(400).json({ error: e.message });
  }
});

/**
 * @openapi
 * /api/medical/profiles/{uid}:
 *   put:
 *     summary: Update a medical profile
 *     tags: [Medical]
 *     security: [{ bearerAuth: [] }]
 *     parameters:
 *       - in: path
 *         name: uid
 *         required: true
 *         schema: { type: string }
 *     responses:
 *       200: { description: Updated }
 *       403: { description: Forbidden }
 *       404: { description: Profile not found }
 */
router.put('/profiles/:uid', auth, (req, res) => {
  if (!isStaff(req.user.role) && req.user.role !== 'parent') {
    return res.status(403).json({ error: 'Role not permitted' });
  }
  const existing = db.prepare('SELECT * FROM student_medical_profiles WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
  if (!existing) return res.status(404).json({ error: 'Profile not found' });

  if (req.user.role === 'parent' && !parentOwnsStudent(req.user.id, existing.student_id)) {
    return res.status(403).json({ error: 'Not your child' });
  }

  const {
    blood_group, allergies, dietary_restrictions,
    contraindications, chronic_conditions, emergency_instructions,
  } = req.body;

  try {
    db.prepare(`
      UPDATE student_medical_profiles SET
        blood_group = COALESCE(?, blood_group),
        allergies = COALESCE(?, allergies),
        dietary_restrictions = COALESCE(?, dietary_restrictions),
        contraindications = COALESCE(?, contraindications),
        chronic_conditions = COALESCE(?, chronic_conditions),
        emergency_instructions = COALESCE(?, emergency_instructions),
        updated_by = ?,
        updated_at = datetime('now', 'localtime'),
        sync_status = 'pending'
      WHERE uid = ?
    `).run(
      blood_group, allergies, dietary_restrictions,
      contraindications, chronic_conditions, emergency_instructions,
      req.user.id,
      req.params.uid,
    );
    const row = db.prepare('SELECT * FROM student_medical_profiles WHERE uid = ?').get(req.params.uid);
    res.json(row);
  } catch (e) {
    res.status(400).json({ error: e.message });
  }
});

// ============ DASHBOARD HELPERS ============
// Lightweight counters used by the nurse dashboard. Declared BEFORE the
// `/logs/:student_uid` and `/history/:student_uid` routes so Express matches
// the literal paths first instead of treating "count-recent" as a uid.
/**
 * @openapi
 * /api/medical/logs/count-recent:
 *   get:
 *     summary: Count medication administrations in the last 30 days
 *     tags: [Medical]
 *     security: [{ bearerAuth: [] }]
 *     responses:
 *       200: { description: { count: number } }
 *       403: { description: Forbidden }
 */
router.get('/logs/count-recent', auth, (req, res) => {
  if (!isStaff(req.user.role) && req.user.role !== 'parent' && req.user.role !== 'student') {
    return res.status(403).json({ error: 'Role not permitted' });
  }
  const row = db.prepare(
    "SELECT COUNT(*) AS count FROM student_medication_logs WHERE is_deleted = 0 AND administered_at >= datetime('now', '-30 days')"
  ).get();
  res.json({ count: row?.count || 0 });
});

/**
 * @openapi
 * /api/medical/history/count-recent:
 *   get:
 *     summary: Count unresolved medical history events
 *     tags: [Medical]
 *     security: [{ bearerAuth: [] }]
 *     responses:
 *       200: { description: { count: number } }
 *       403: { description: Forbidden }
 */
router.get('/history/count-recent', auth, (req, res) => {
  if (!isStaff(req.user.role) && req.user.role !== 'parent' && req.user.role !== 'student') {
    return res.status(403).json({ error: 'Role not permitted' });
  }
  const row = db.prepare(
    'SELECT COUNT(*) AS count FROM student_medical_history WHERE is_deleted = 0 AND resolution_date IS NULL'
  ).get();
  res.json({ count: row?.count || 0 });
});

// ============ MEDICATION LOGS ============

/**
 * @openapi
 * /api/medical/logs/{student_uid}:
 *   get:
 *     summary: List medication administrations for a student
 *     tags: [Medical]
 *     security: [{ bearerAuth: [] }]
 *     parameters:
 *       - in: path
 *         name: student_uid
 *         required: true
 *         schema: { type: string }
 *     responses:
 *       200: { description: Array of medication log rows }
 *       403: { description: Forbidden }
 *       404: { description: Student not found }
 */
router.get('/logs/:student_uid', auth, (req, res) => {
  const target = resolveStudentId(req.params.student_uid);
  if (!target) return res.status(404).json({ error: 'Student not found' });

  const role = req.user.role;
  if (role === 'student') {
    if (req.user.id !== target.id) return res.status(403).json({ error: 'Students may only view their own logs' });
  } else if (role === 'parent') {
    if (!parentOwnsStudent(req.user.id, target.id)) return res.status(403).json({ error: 'Not your child' });
  } else if (!isStaff(role)) {
    return res.status(403).json({ error: 'Role not permitted' });
  }

  const rows = db.prepare(
    'SELECT * FROM student_medication_logs WHERE student_id = ? AND is_deleted = 0 ORDER BY administered_at DESC'
  ).all(target.id);
  res.json(rows);
});

/**
 * @openapi
 * /api/medical/logs:
 *   post:
 *     summary: Record a medication administration
 *     tags: [Medical]
 *     security: [{ bearerAuth: [] }]
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required: [student_uid, medication_name, dosage, administered_at]
 *     responses:
 *       201: { description: Created }
 *       400: { description: Validation error }
 */
router.post('/logs', auth, (req, res) => {
  if (!isStaff(req.user.role)) return res.status(403).json({ error: 'Role not permitted' });

  const {
    student_uid, student_id,
    medication_name, dosage, administered_at,
    reason, notes,
  } = req.body;
  if (!medication_name || !dosage || !administered_at) {
    return res.status(400).json({ error: 'medication_name, dosage and administered_at are required' });
  }
  const target = resolveStudentId(student_uid ?? student_id);
  if (!target) return res.status(400).json({ error: 'student_uid required' });

  // Pull the student's contraindications and warn if the medication name
  // appears in the list. This is a soft warning — the nurse still has
  // authority to administer (response includes `warning` field).
  const profile = db.prepare('SELECT contraindications FROM student_medical_profiles WHERE student_id = ?').get(target.id);
  let warning = null;
  if (profile?.contraindications && typeof profile.contraindications === 'string') {
    const meds = profile.contraindications.split(/[,;\n]+/).map(s => s.trim().toLowerCase()).filter(Boolean);
    if (meds.some(m => medication_name.toLowerCase().includes(m))) {
      warning = `WARNING: "${medication_name}" matches a documented contraindication. Verify before administrating.`;
    }
  }

  const uid = uuidv4();
  try {
    db.prepare(`
      INSERT INTO student_medication_logs (
        uid, student_id, medication_name, dosage, administered_at,
        administered_by, reason, notes, sync_status
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'pending')
    `).run(
      uid, target.id, medication_name, dosage, administered_at,
      req.user.id, reason ?? null, notes ?? null,
    );
    const row = db.prepare('SELECT * FROM student_medication_logs WHERE uid = ?').get(uid);
    res.status(201).json({ ...row, warning });
  } catch (e) {
    res.status(400).json({ error: e.message });
  }
});

// ============ MEDICAL HISTORY ============

/**
 * @openapi
 * /api/medical/history/{student_uid}:
 *   get:
 *     summary: List a student's medical history (illness/injury/hospitalization)
 *     description: |
 *       Staff see all rows. Parents see their own child's full history.
 *       Students see their own. Teachers see only rows where
 *       `is_visible_to_teachers = 1`, with `doctor_notes` stripped.
 *     tags: [Medical]
 *     security: [{ bearerAuth: [] }]
 *     parameters:
 *       - in: path
 *         name: student_uid
 *         required: true
 *         schema: { type: string }
 *     responses:
 *       200: { description: Array of history rows }
 *       403: { description: Forbidden }
 */
router.get('/history/:student_uid', auth, (req, res) => {
  const target = resolveStudentId(req.params.student_uid);
  if (!target) return res.status(404).json({ error: 'Student not found' });

  const role = req.user.role;
  if (role === 'student') {
    if (req.user.id !== target.id) return res.status(403).json({ error: 'Students may only view their own history' });
  } else if (role === 'parent') {
    if (!parentOwnsStudent(req.user.id, target.id)) return res.status(403).json({ error: 'Not your child' });
  } else if (!isStaff(role) && role !== 'teacher') {
    return res.status(403).json({ error: 'Role not permitted' });
  }

  let rows;
  if (role === 'teacher') {
    rows = db.prepare(
      "SELECT id, uid, student_id, event_type, description, severity, onset_date, resolution_date, treatment_details, is_visible_to_teachers, reported_by, created_at, updated_at FROM student_medical_history WHERE student_id = ? AND is_deleted = 0 AND is_visible_to_teachers = 1 ORDER BY onset_date DESC"
    ).all(target.id);
  } else {
    rows = db.prepare(
      'SELECT * FROM student_medical_history WHERE student_id = ? AND is_deleted = 0 ORDER BY onset_date DESC, created_at DESC'
    ).all(target.id);
  }
  res.json(rows);
});

/**
 * @openapi
 * /api/medical/history:
 *   post:
 *     summary: Record a new medical history event
 *     tags: [Medical]
 *     security: [{ bearerAuth: [] }]
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required: [student_uid, event_type, description]
 *     responses:
 *       201: { description: Created }
 *       400: { description: Validation error }
 */
router.post('/history', auth, (req, res) => {
  if (!isStaff(req.user.role)) return res.status(403).json({ error: 'Role not permitted' });

  const {
    student_uid, student_id,
    event_type, description, severity,
    onset_date, resolution_date,
    treatment_details, doctor_notes,
    is_visible_to_teachers,
  } = req.body;
  if (!event_type || !description) {
    return res.status(400).json({ error: 'event_type and description are required' });
  }
  const target = resolveStudentId(student_uid ?? student_id);
  if (!target) return res.status(400).json({ error: 'student_uid required' });

  const uid = uuidv4();
  try {
    db.prepare(`
      INSERT INTO student_medical_history (
        uid, student_id, event_type, description, severity,
        onset_date, resolution_date,
        treatment_details, doctor_notes,
        is_visible_to_teachers, reported_by, sync_status
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending')
    `).run(
      uid, target.id, event_type, description, severity ?? 'moderate',
      onset_date ?? null, resolution_date ?? null,
      treatment_details ?? null, doctor_notes ?? null,
      is_visible_to_teachers == null ? 1 : (is_visible_to_teachers ? 1 : 0),
      req.user.id,
    );
    const row = db.prepare('SELECT * FROM student_medical_history WHERE uid = ?').get(uid);
    res.status(201).json(row);
  } catch (e) {
    res.status(400).json({ error: e.message });
  }
});

// ============ DINING HALL ============

/**
 * @openapi
 * /api/medical/dining/allergies:
 *   get:
 *     summary: Kitchen-safe allergy + diet roster for the dining hall
 *     description: |
 *       Slim DTO listing each student's dietary restrictions and allergies
 *       suitable for a kitchen tablet. Strips contraindications, chronic
 *       conditions, and any doctor notes. Restricted to dining_staff and
 *       supervisors (nurse, school_admin, systems_admin, principal).
 *     tags: [Medical]
 *     security: [{ bearerAuth: [] }]
 *     responses:
 *       200: { description: Array of slim per-student rows }
 *       403: { description: Forbidden }
 */
router.get('/dining/allergies', auth, (req, res) => {
  const role = req.user.role;
  const allowed = new Set(['dining_staff', 'nurse', 'school_admin', 'systems_admin', 'principal']);
  if (!allowed.has(role)) {
    return res.status(403).json({ error: 'Role not permitted' });
  }

  const rows = db.prepare(`
    SELECT p.uid, p.student_id, p.blood_group, p.allergies,
           p.dietary_restrictions, p.emergency_instructions,
           u.first_name, u.last_name,
           e.class_id, c.name AS class_name,
           a.id AS alert_id, a.allergy_details, a.severity, a.special_instructions, a.alert_status, a.created_at AS alert_created_at
    FROM student_medical_profiles p
    JOIN users u ON u.id = p.student_id
    LEFT JOIN enrollments e ON e.student_id = u.id AND e.is_deleted = 0 AND e.status = 'active'
    LEFT JOIN classes c ON c.id = e.class_id
    LEFT JOIN dining_hall_alerts a ON a.student_id = u.id AND a.is_deleted = 0 AND a.alert_status IN ('active', 'acknowledged')
    WHERE p.is_deleted = 0 AND u.is_deleted = 0
    ORDER BY CASE WHEN a.alert_status = 'active' THEN 0 WHEN a.severity = 'life_threatening' THEN 1 ELSE 2 END, c.name, u.last_name
  `).all();

  const slimmed = rows.map((r) => ({
    student_uid: r.uid,
    student_id: r.student_id,
    name: `${r.first_name} ${r.last_name}`,
    class: r.class_name || 'Unassigned',
    blood_group: r.blood_group,
    allergies: r.allergies || r.allergy_details,
    dietary_restrictions: r.dietary_restrictions,
    emergency_instructions: r.emergency_instructions,
    alert_id: r.alert_id,
    severity: r.severity || (r.allergies ? 'severe' : 'mild'),
    special_instructions: r.special_instructions,
    alert_status: r.alert_status || 'normal',
    alert_created_at: r.alert_created_at,
  }));

  res.json(slimmed);
});

// ============ TEACHER CLASS FLAGS ============
// Read-only slim DTO used by /medical/class/:classId on the frontend.
// Returns one entry per student in the class with the *flags* a teacher
// needs (allergy + chronic-condition summary), with no clinical detail.

/**
 * @openapi
 * /api/medical/class/{classId}/flags:
 *   get:
 *     summary: Slim medical flags for every student in a class (teacher view)
 *     tags: [Medical]
 *     security: [{ bearerAuth: [] }]
 *     parameters:
 *       - in: path
 *         name: classId
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200: { description: Array of slim flag rows }
 *       403: { description: Forbidden }
 */
router.get('/class/:classId/flags', auth, (req, res) => {
  if (!isStaff(req.user.role) && req.user.role !== 'teacher') {
    return res.status(403).json({ error: 'Role not permitted' });
  }
  const classId = parseInt(req.params.classId);
  if (!classId) return res.status(400).json({ error: 'classId required' });

  const rows = db.prepare(`
    SELECT u.id AS student_id, u.uid AS student_uid, u.first_name, u.last_name,
           p.blood_group, p.allergies, p.chronic_conditions, p.emergency_instructions,
           (SELECT COUNT(*) FROM student_medication_logs m
              WHERE m.student_id = u.id AND m.is_deleted = 0
                AND date(m.administered_at) >= date('now', '-30 days')
           ) AS active_med_count,
           (SELECT COUNT(*) FROM student_medical_history h
              WHERE h.student_id = u.id AND h.is_deleted = 0
                AND h.is_visible_to_teachers = 1
                AND h.resolution_date IS NULL
           ) AS active_history_count
    FROM enrollments e
    JOIN users u ON u.id = e.student_id AND u.is_deleted = 0
    LEFT JOIN student_medical_profiles p ON p.student_id = u.id AND p.is_deleted = 0
    WHERE e.class_id = ? AND e.is_deleted = 0 AND e.status = 'active'
    ORDER BY u.last_name, u.first_name
  `).all(classId);

  // Strip student uids for safer logs (keep cross-system uid for sync)
  res.json(rows.map((r) => ({
    student_uid: r.student_uid,
    student_id: r.student_id,
    name: `${r.first_name} ${r.last_name}`,
    blood_group: r.blood_group,
    allergies: r.allergies,
    chronic_conditions: r.chronic_conditions,
    emergency_instructions: r.emergency_instructions,
    active_med_count: r.active_med_count,
    active_history_count: r.active_history_count,
  })));
});

// ============ SANATORIUM ENHANCEMENTS ============

// Student Search Autocomplete for Nurse
router.get('/students/search', auth, (req, res) => {
  if (!isStaff(req.user.role) && req.user.role !== 'teacher') {
    return res.status(403).json({ error: 'Role not permitted' });
  }
  const q = String(req.query.q || '').trim();
  if (!q) return res.json([]);
  
  const searchPattern = `%${q}%`;
  const students = db.prepare(`
    SELECT u.id, u.uid, u.id_number, u.first_name, u.last_name, u.email, u.phone, u.emergency_contact, u.emergency_phone,
           c.name as class_name
    FROM users u
    LEFT JOIN enrollments e ON e.student_id = u.id AND e.is_deleted = 0 AND e.status = 'active'
    LEFT JOIN classes c ON c.id = e.class_id
    WHERE u.role = 'student' AND u.is_deleted = 0 AND (
      u.first_name LIKE ? OR u.last_name LIKE ? OR (u.first_name || ' ' || u.last_name) LIKE ? OR u.id_number LIKE ? OR u.email LIKE ?
    )
    ORDER BY u.last_name, u.first_name LIMIT 20
  `).all(searchPattern, searchPattern, searchPattern, searchPattern, searchPattern);
  
  res.json(students);
});

// Clinic Visits (Sick Bay Encounters)
router.get('/visits', auth, (req, res) => {
  if (!isStaff(req.user.role) && req.user.role !== 'teacher') {
    return res.status(403).json({ error: 'Role not permitted' });
  }
  const { disposition, student_uid, limit = 50 } = req.query;
  
  let sql = `
    SELECT v.*, 
           u.first_name as student_first_name, u.last_name as student_last_name, u.id_number, u.uid as student_uid,
           n.first_name as nurse_first_name, n.last_name as nurse_last_name,
           c.name as class_name
    FROM sanatorium_visits v
    JOIN users u ON u.id = v.student_id
    LEFT JOIN users n ON n.id = v.nurse_id
    LEFT JOIN enrollments e ON e.student_id = u.id AND e.is_deleted = 0 AND e.status = 'active'
    LEFT JOIN classes c ON c.id = e.class_id
    WHERE v.is_deleted = 0
  `;
  const params = [];
  
  if (disposition && disposition !== 'all') {
    sql += ` AND v.disposition = ?`;
    params.push(disposition);
  }
  if (student_uid) {
    const student = resolveStudentId(student_uid);
    if (student) {
      sql += ` AND v.student_id = ?`;
      params.push(student.id);
    }
  }
  
  sql += ` ORDER BY v.visit_date DESC LIMIT ?`;
  params.push(parseInt(limit));
  
  res.json(db.prepare(sql).all(...params));
});

router.post('/visits', auth, (req, res) => {
  if (!isStaff(req.user.role)) return res.status(403).json({ error: 'Role not permitted' });
  
  const { student_uid, chief_complaint, vitals, treatment_administered, disposition, bed_number, parent_notified, notes } = req.body;
  const student = resolveStudentId(student_uid);
  if (!student) return res.status(400).json({ error: 'Invalid student identifier' });
  if (!chief_complaint) return res.status(400).json({ error: 'chief_complaint is required' });
  
  const uid = uuidv4();
  const vitalsJson = typeof vitals === 'object' ? JSON.stringify(vitals) : vitals || null;
  const nurseId = req.user.id;
  
  const stmt = db.prepare(`
    INSERT INTO sanatorium_visits (uid, student_id, nurse_id, chief_complaint, vitals_json, treatment_administered, disposition, bed_number, parent_notified, notes, sync_status)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending')
  `);
  const result = stmt.run(
    uid, student.id, nurseId, chief_complaint, vitalsJson,
    treatment_administered || null, disposition || 'discharged', bed_number || null,
    parent_notified ? 1 : 0, notes || null
  );
  
  res.status(201).json(db.prepare('SELECT * FROM sanatorium_visits WHERE id = ?').get(result.lastInsertRowid));
});

router.put('/visits/:id', auth, (req, res) => {
  if (!isStaff(req.user.role)) return res.status(403).json({ error: 'Role not permitted' });
  
  const { disposition, bed_number, parent_notified, treatment_administered, notes } = req.body;
  const visitId = parseInt(req.params.id);
  
  db.prepare(`
    UPDATE sanatorium_visits SET
      disposition = COALESCE(?, disposition),
      bed_number = ?,
      parent_notified = COALESCE(?, parent_notified),
      treatment_administered = COALESCE(?, treatment_administered),
      notes = COALESCE(?, notes),
      updated_at = datetime('now', 'localtime'),
      sync_status = 'pending'
    WHERE id = ? AND is_deleted = 0
  `).run(disposition, bed_number === null ? null : (bed_number || null), parent_notified !== undefined ? (parent_notified ? 1 : 0) : null, treatment_administered, notes, visitId);
  
  res.json(db.prepare('SELECT * FROM sanatorium_visits WHERE id = ?').get(visitId));
});

// Immunizations
router.get('/immunizations/:student_uid', auth, (req, res) => {
  const student = resolveStudentId(req.params.student_uid);
  if (!student) return res.status(404).json({ error: 'Student not found' });
  
  const rows = db.prepare(`
    SELECT * FROM student_immunizations
    WHERE student_id = ? AND is_deleted = 0
    ORDER BY administered_date DESC
  `).all(student.id);
  
  res.json(rows);
});

router.post('/immunizations', auth, (req, res) => {
  if (!isStaff(req.user.role)) return res.status(403).json({ error: 'Role not permitted' });
  
  const { student_uid, vaccine_name, dose_number, administered_date, administered_by, batch_number, notes } = req.body;
  const student = resolveStudentId(student_uid);
  if (!student) return res.status(400).json({ error: 'Invalid student identifier' });
  if (!vaccine_name) return res.status(400).json({ error: 'vaccine_name is required' });
  
  const uid = uuidv4();
  const stmt = db.prepare(`
    INSERT INTO student_immunizations (uid, student_id, vaccine_name, dose_number, administered_date, administered_by, batch_number, notes, sync_status)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'pending')
  `);
  const result = stmt.run(uid, student.id, vaccine_name, dose_number || 1, administered_date || new Date().toISOString().slice(0,10), administered_by || req.user.first_name, batch_number || null, notes || null);
  
  res.status(201).json(db.prepare('SELECT * FROM student_immunizations WHERE id = ?').get(result.lastInsertRowid));
});

// History Update & Delete
router.put('/history/:id', auth, (req, res) => {
  if (!isStaff(req.user.role)) return res.status(403).json({ error: 'Role not permitted' });
  const { title, description, event_type, severity, resolution_date, doctor_notes, is_visible_to_teachers } = req.body;
  
  db.prepare(`
    UPDATE student_medical_history SET
      title = COALESCE(?, title),
      description = COALESCE(?, description),
      event_type = COALESCE(?, event_type),
      severity = COALESCE(?, severity),
      resolution_date = ?,
      doctor_notes = COALESCE(?, doctor_notes),
      is_visible_to_teachers = COALESCE(?, is_visible_to_teachers),
      updated_at = datetime('now', 'localtime'),
      sync_status = 'pending'
    WHERE id = ? AND is_deleted = 0
  `).run(title, description, event_type, severity, resolution_date || null, doctor_notes, is_visible_to_teachers !== undefined ? (is_visible_to_teachers ? 1 : 0) : 1, req.params.id);
  
  res.json(db.prepare('SELECT * FROM student_medical_history WHERE id = ?').get(req.params.id));
});

router.delete('/history/:id', auth, (req, res) => {
  if (!isStaff(req.user.role)) return res.status(403).json({ error: 'Role not permitted' });
  db.prepare("UPDATE student_medical_history SET is_deleted = 1, sync_status = 'pending' WHERE id = ?").run(req.params.id);
  res.json({ message: 'History event deleted' });
});

router.delete('/logs/:id', auth, (req, res) => {
  if (!isStaff(req.user.role)) return res.status(403).json({ error: 'Role not permitted' });
  db.prepare("UPDATE student_medication_logs SET is_deleted = 1, sync_status = 'pending' WHERE id = ?").run(req.params.id);
  res.json({ message: 'Medication log deleted' });
});

// Notify Parent
router.post('/notify-parent', auth, (req, res) => {
  if (!isStaff(req.user.role)) return res.status(403).json({ error: 'Role not permitted' });
  const { student_uid, subject, body } = req.body;
  const student = resolveStudentId(student_uid);
  if (!student) return res.status(400).json({ error: 'Invalid student identifier' });

  const parents = db.prepare(`
    SELECT parent_id FROM parent_students WHERE student_id = ? AND is_deleted = 0
  `).all(student.id);

  if (parents.length === 0) {
    return res.status(404).json({ error: 'No parent account linked to this student' });
  }

  const stmt = db.prepare(`
    INSERT INTO messages (uid, sender_id, recipient_id, subject, body, priority, sync_status)
    VALUES (?, ?, ?, ?, ?, 'high', 'pending')
  `);

  for (const p of parents) {
    stmt.run(uuidv4(), req.user.id, p.parent_id, subject || 'Sanatorium Visit Alert', body);
  }

  res.json({ message: `Notification sent to ${parents.length} linked parent(s)` });
});

// Multer Configuration for Student Medical Document Uploads
const fs = require('fs');
const multer = require('multer');
const uploadDir = path.join(__dirname, '../../uploads/medical');
if (!fs.existsSync(uploadDir)) {
  fs.mkdirSync(uploadDir, { recursive: true });
}
const storage = multer.diskStorage({
  destination: (req, file, cb) => cb(null, uploadDir),
  filename: (req, file, cb) => {
    const ext = path.extname(file.originalname);
    cb(null, `med_${Date.now()}_${uuidv4().slice(0, 8)}${ext}`);
  }
});
const upload = multer({
  storage,
  limits: { fileSize: 10 * 1024 * 1024 } // 10MB limit
});

// Upload Medical Document / Attachment (PDF, Image, Lab Report, Doctor Note)
router.post('/documents/upload', auth, upload.single('file'), (req, res) => {
  if (!isStaff(req.user.role)) return res.status(403).json({ error: 'Role not permitted' });
  const { student_uid, document_type, notes } = req.body;
  const student = resolveStudentId(student_uid);
  if (!student) return res.status(400).json({ error: 'Invalid student identifier' });
  if (!req.file) return res.status(400).json({ error: 'No file uploaded' });

  const uid = uuidv4();
  const fileUrl = `/uploads/medical/${req.file.filename}`;
  const fileType = path.extname(req.file.originalname).replace('.', '').toLowerCase() || 'file';

  const stmt = db.prepare(`
    INSERT INTO student_medical_documents (
      uid, student_id, file_name, file_url, file_type, document_type, notes, uploaded_by, sync_status
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'pending')
  `);
  const result = stmt.run(
    uid,
    student.id,
    req.file.originalname,
    fileUrl,
    fileType,
    document_type || 'lab_report',
    notes || null,
    req.user.id
  );

  res.status(201).json(db.prepare('SELECT * FROM student_medical_documents WHERE id = ?').get(result.lastInsertRowid));
});

// Bulk Ingestion of Medical Profile & History Records (JSON/CSV)
router.post('/documents/ingest-json', auth, (req, res) => {
  if (!isStaff(req.user.role)) return res.status(403).json({ error: 'Role not permitted' });
  const { records } = req.body;
  if (!Array.isArray(records) || records.length === 0) {
    return res.status(400).json({ error: 'Array of records is required' });
  }

  let ingestedCount = 0;
  const ingestTransaction = db.transaction((rows) => {
    for (const row of rows) {
      const student = resolveStudentId(row.student_uid || row.student_id);
      if (!student) continue;

      // Upsert profile
      db.prepare(`
        INSERT INTO student_medical_profiles (
          uid, student_id, blood_group, allergies, dietary_restrictions, contraindications, chronic_conditions, emergency_instructions, updated_by, sync_status
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending')
        ON CONFLICT(student_id) DO UPDATE SET
          blood_group = COALESCE(EXCLUDED.blood_group, blood_group),
          allergies = COALESCE(EXCLUDED.allergies, allergies),
          dietary_restrictions = COALESCE(EXCLUDED.dietary_restrictions, dietary_restrictions),
          contraindications = COALESCE(EXCLUDED.contraindications, contraindications),
          chronic_conditions = COALESCE(EXCLUDED.chronic_conditions, chronic_conditions),
          emergency_instructions = COALESCE(EXCLUDED.emergency_instructions, emergency_instructions),
          updated_at = datetime('now', 'localtime'),
          sync_status = 'pending'
      `).run(
        uuidv4(), student.id, row.blood_group || null, row.allergies || null,
        row.dietary_restrictions || null, row.contraindications || null,
        row.chronic_conditions || null, row.emergency_instructions || null, req.user.id
      );

      // Record ingestion document entry
      db.prepare(`
        INSERT INTO student_medical_documents (
          uid, student_id, file_name, file_url, file_type, document_type, notes, uploaded_by, sync_status
        ) VALUES (?, ?, ?, ?, 'json', 'ingested_record', ?, ?, 'pending')
      `).run(uuidv4(), student.id, `ingested_${Date.now()}.json`, '#ingested', 'Bulk JSON Record Ingestion', req.user.id);

      ingestedCount++;
    }
  });

  try {
    ingestTransaction(records);
    res.json({ message: `Successfully ingested ${ingestedCount} medical records`, count: ingestedCount });
  } catch (err) {
    res.status(500).json({ error: 'Failed to ingest medical records', details: err.message });
  }
});

// Fetch Student Medical Documents
router.get('/documents/:student_uid', auth, (req, res) => {
  const student = resolveStudentId(req.params.student_uid);
  if (!student) return res.status(404).json({ error: 'Student not found' });

  const rows = db.prepare(`
    SELECT d.*, u.first_name AS uploader_first_name, u.last_name AS uploader_last_name
    FROM student_medical_documents d
    LEFT JOIN users u ON u.id = d.uploaded_by
    WHERE d.student_id = ? AND d.is_deleted = 0
    ORDER BY d.created_at DESC
  `).all(student.id);

  res.json(rows);
});

// Delete Medical Document
router.delete('/documents/:id', auth, (req, res) => {
  if (!isStaff(req.user.role)) return res.status(403).json({ error: 'Role not permitted' });
  db.prepare("UPDATE student_medical_documents SET is_deleted = 1, sync_status = 'pending' WHERE id = ?").run(req.params.id);
  res.json({ message: 'Document deleted' });
});

// Create Kitchen Dining Hall Allergy Alert
router.post('/dining-alerts', auth, (req, res) => {
  if (!isStaff(req.user.role)) return res.status(403).json({ error: 'Role not permitted' });
  const { student_uid, allergy_details, severity, special_instructions } = req.body;
  const student = resolveStudentId(student_uid);
  if (!student) return res.status(400).json({ error: 'Invalid student identifier' });
  if (!allergy_details) return res.status(400).json({ error: 'allergy_details is required' });

  const uid = uuidv4();
  const stmt = db.prepare(`
    INSERT INTO dining_hall_alerts (
      uid, student_id, allergy_details, severity, special_instructions, alert_status, flagged_by, sync_status
    ) VALUES (?, ?, ?, ?, ?, 'active', ?, 'pending')
  `);
  const result = stmt.run(uid, student.id, allergy_details, severity || 'severe', special_instructions || null, req.user.id);

  res.status(201).json(db.prepare('SELECT * FROM dining_hall_alerts WHERE id = ?').get(result.lastInsertRowid));
});

// Acknowledge Kitchen Dining Hall Allergy Alert (Kitchen Staff / Dining Staff)
router.put('/dining-alerts/:id/acknowledge', auth, (req, res) => {
  const role = req.user.role;
  const allowed = new Set(['dining_staff', 'nurse', 'school_admin', 'systems_admin', 'principal']);
  if (!allowed.has(role)) return res.status(403).json({ error: 'Role not permitted' });

  db.prepare(`
    UPDATE dining_hall_alerts SET
      alert_status = 'acknowledged',
      acknowledged_by = ?,
      acknowledged_at = datetime('now', 'localtime'),
      updated_at = datetime('now', 'localtime'),
      sync_status = 'pending'
    WHERE id = ? AND is_deleted = 0
  `).run(req.user.id, req.params.id);

  res.json(db.prepare('SELECT * FROM dining_hall_alerts WHERE id = ?').get(req.params.id));
});

module.exports = router;


