/**
 * Timetable Controller (FR-SMS1 timetable sub-feature, UAT TC-SMS-008/009/010)
 *
 *   GET    /api/timetable/periods                list period definitions
 *   POST   /api/timetable/periods                create period (admin/principal)
 *   PATCH  /api/timetable/periods/:id            update period
 *   DELETE /api/timetable/periods/:id            soft-delete period
 *
 *   GET    /api/timetable/entries                list (filters: class_id, teacher_id, day, period_id)
 *   POST   /api/timetable/entries                create (with conflict detection)
 *   PATCH  /api/timetable/entries/:id            update (with conflict detection)
 *   DELETE /api/timetable/entries/:id            soft-delete
 *
 *   GET    /api/timetable/conflicts              explicit conflict check (?class_id= or ?teacher_id= or ?room=)
 *   GET    /api/timetable/class/:classId         full week for a class
 *   GET    /api/timetable/teacher/:teacherId     full week for a teacher
 *
 * Conflict policy:
 *   - Class double-booked  (same class, day, period)
 *   - Teacher double-booked (same teacher, day, period)
 *   - Room double-booked    (same room, day, period)
 * Soft-deleted entries (is_deleted = 1) are excluded from conflict checks.
 *
 * RBAC:
 *   - admin / principal / systems_admin: full CRUD
 *   - teacher: read-all, but can only create entries where teacher_id = self
 *   - student / parent / others: read-self (own class timetable only)
 */
const express = require('express');
const router = express.Router();
const { v4: uuidv4 } = require('uuid');
const path = require('path');
const Database = require('better-sqlite3');

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

const adminOrPrincipal = (req, res, next) => {
  if (!['school_admin', 'systems_admin', 'principal'].includes(req.user.role)) {
    return res.status(403).json({ error: 'Admin or Principal access required' });
  }
  next();
};

const DAYS = ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'];
const dayName = (n) => DAYS[(Number(n) - 1) % 7] || `Day ${n}`;

/**
 * Returns the list of conflicts for a candidate entry.
 * `ignoreEntryId` lets update operations skip self-collision.
 */
function detectConflicts(candidate, ignoreEntryId = null) {
  const { class_id, teacher_id, day_of_week, period_id, room } = candidate;
  const conflicts = [];
  if (!class_id || !teacher_id || !day_of_week || !period_id) return conflicts;

  // 1. class double-booked
  const classRow = getDb().prepare(`
    SELECT e.id, e.teacher_id, e.subject_id, sub.name AS subject_name, u.first_name || ' ' || u.last_name AS teacher_name
    FROM timetable_entries e
    JOIN subjects sub ON e.subject_id = sub.id
    JOIN users u ON e.teacher_id = u.id
    WHERE e.is_deleted = 0
      AND e.class_id = ?
      AND e.day_of_week = ?
      AND e.period_id = ?
      AND (? IS NULL OR e.id != ?)
  `).get(class_id, day_of_week, period_id, ignoreEntryId, ignoreEntryId);
  if (classRow) {
    conflicts.push({
      kind: 'class_double_booked',
      message: `Class ${class_id} already has ${classRow.subject_name} (${classRow.teacher_name}) at ${dayName(day_of_week)} period ${period_id}`,
      existing_entry_id: classRow.id,
    });
  }

  // 2. teacher double-booked
  const teacherRow = getDb().prepare(`
    SELECT e.id, e.class_id, c.name AS class_name, sub.name AS subject_name
    FROM timetable_entries e
    JOIN classes c ON e.class_id = c.id
    JOIN subjects sub ON e.subject_id = sub.id
    WHERE e.is_deleted = 0
      AND e.teacher_id = ?
      AND e.day_of_week = ?
      AND e.period_id = ?
      AND (? IS NULL OR e.id != ?)
  `).get(teacher_id, day_of_week, period_id, ignoreEntryId, ignoreEntryId);
  if (teacherRow) {
    conflicts.push({
      kind: 'teacher_double_booked',
      message: `Teacher ${teacher_id} already teaches ${teacherRow.subject_name} for class ${teacherRow.class_name} at ${dayName(day_of_week)} period ${period_id}`,
      existing_entry_id: teacherRow.id,
    });
  }

  // 3. room double-booked (only if room is provided)
  if (room && String(room).trim() !== '') {
    const roomRow = getDb().prepare(`
      SELECT e.id, e.class_id, c.name AS class_name, sub.name AS subject_name
      FROM timetable_entries e
      JOIN classes c ON e.class_id = c.id
      JOIN subjects sub ON e.subject_id = sub.id
      WHERE e.is_deleted = 0
        AND LOWER(TRIM(e.room)) = LOWER(TRIM(?))
        AND e.day_of_week = ?
        AND e.period_id = ?
        AND (? IS NULL OR e.id != ?)
    `).get(room, day_of_week, period_id, ignoreEntryId, ignoreEntryId);
    if (roomRow) {
      conflicts.push({
        kind: 'room_double_booked',
        message: `Room "${room}" already booked for ${roomRow.class_name} (${roomRow.subject_name}) at ${dayName(day_of_week)} period ${period_id}`,
        existing_entry_id: roomRow.id,
      });
    }
  }

  return conflicts;
}

// ============ PERIODS ============

router.get('/periods', auth, (req, res) => {
  try {
    const rows = getDb().prepare(`
      SELECT * FROM timetable_periods WHERE is_deleted = 0 ORDER BY period_order ASC, id ASC
    `).all();
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.post('/periods', auth, adminOrPrincipal, (req, res) => {
  try {
    const { name, start_time, end_time, period_order, is_break } = req.body || {};
    if (!name || !start_time || !end_time || period_order === undefined) {
      return res.status(400).json({ error: 'name, start_time, end_time, period_order are required' });
    }
    const uid = uuidv4();
    const result = getDb().prepare(`
      INSERT INTO timetable_periods (uid, name, start_time, end_time, period_order, is_break)
      VALUES (?, ?, ?, ?, ?, ?)
    `).run(uid, name, start_time, end_time, Number(period_order), is_break ? 1 : 0);
    const row = getDb().prepare('SELECT * FROM timetable_periods WHERE id = ?').get(result.lastInsertRowid);
    res.status(201).json(row);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.patch('/periods/:id', auth, adminOrPrincipal, (req, res) => {
  try {
    const { name, start_time, end_time, period_order, is_break } = req.body || {};
    const existing = getDb().prepare('SELECT * FROM timetable_periods WHERE id = ? AND is_deleted = 0').get(req.params.id);
    if (!existing) return res.status(404).json({ error: 'Period not found' });
    getDb().prepare(`
      UPDATE timetable_periods
      SET name = COALESCE(?, name),
          start_time = COALESCE(?, start_time),
          end_time = COALESCE(?, end_time),
          period_order = COALESCE(?, period_order),
          is_break = COALESCE(?, is_break),
          updated_at = datetime('now','localtime'),
          sync_status = 'pending'
      WHERE id = ?
    `).run(name ?? null, start_time ?? null, end_time ?? null, period_order ?? null, is_break === undefined ? null : (is_break ? 1 : 0), req.params.id);
    const row = getDb().prepare('SELECT * FROM timetable_periods WHERE id = ?').get(req.params.id);
    res.json(row);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.delete('/periods/:id', auth, adminOrPrincipal, (req, res) => {
  try {
    const existing = getDb().prepare('SELECT id FROM timetable_periods WHERE id = ? AND is_deleted = 0').get(req.params.id);
    if (!existing) return res.status(404).json({ error: 'Period not found' });
    // Refuse if any active entry references this period
    const refCount = getDb().prepare('SELECT COUNT(*) AS c FROM timetable_entries WHERE period_id = ? AND is_deleted = 0').get(req.params.id).c;
    if (refCount > 0) {
      return res.status(409).json({ error: `Cannot delete period; ${refCount} active entries reference it`, code: 'PERIOD_IN_USE' });
    }
    getDb().prepare(`UPDATE timetable_periods SET is_deleted = 1, updated_at = datetime('now','localtime'), sync_status = 'pending' WHERE id = ?`).run(req.params.id);
    res.json({ success: true, id: Number(req.params.id) });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// ============ ENTRIES ============

const enrichEntry = (row) => {
  if (!row) return row;
  return {
    ...row,
    day_name: dayName(row.day_of_week),
  };
};

router.get('/entries', auth, (req, res) => {
  try {
    const { class_id, teacher_id, day, period_id, room } = req.query;
    const conds = ['e.is_deleted = 0'];
    const params = [];
    if (class_id)   { conds.push('e.class_id = ?');    params.push(Number(class_id)); }
    if (teacher_id) { conds.push('e.teacher_id = ?');  params.push(Number(teacher_id)); }
    if (day)        { conds.push('e.day_of_week = ?'); params.push(Number(day)); }
    if (period_id)  { conds.push('e.period_id = ?');   params.push(Number(period_id)); }
    if (room)       { conds.push('LOWER(TRIM(e.room)) = LOWER(TRIM(?))'); params.push(String(room)); }
    const sql = `
      SELECT e.*, c.name AS class_name, sub.name AS subject_name,
             u.first_name || ' ' || u.last_name AS teacher_name,
             p.name AS period_name, p.start_time, p.end_time
      FROM timetable_entries e
      JOIN classes c ON e.class_id = c.id
      JOIN subjects sub ON e.subject_id = sub.id
      JOIN users u ON e.teacher_id = u.id
      JOIN timetable_periods p ON e.period_id = p.id
      WHERE ${conds.join(' AND ')}
      ORDER BY e.day_of_week ASC, p.period_order ASC, c.name ASC
    `;
    const rows = getDb().prepare(sql).all(...params).map(enrichEntry);
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.post('/entries', auth, (req, res) => {
  try {
    const { class_id, subject_id, teacher_id, day_of_week, period_id, room, academic_year, term, notes } = req.body || {};
    if (!class_id || !subject_id || !teacher_id || !day_of_week || !period_id) {
      return res.status(400).json({ error: 'class_id, subject_id, teacher_id, day_of_week, period_id are required' });
    }
    if (Number(day_of_week) < 1 || Number(day_of_week) > 7) {
      return res.status(400).json({ error: 'day_of_week must be between 1 (Mon) and 7 (Sun)' });
    }
    // Teachers can only create entries where they are the teacher
    if (req.user.role === 'teacher' && Number(teacher_id) !== Number(req.user.id)) {
      return res.status(403).json({ error: 'Teachers can only create entries for themselves' });
    }
    // Validate FKs exist
    const cls = getDb().prepare('SELECT id FROM classes WHERE id = ? AND is_deleted = 0').get(class_id);
    if (!cls) return res.status(400).json({ error: `Class ${class_id} not found` });
    const sub = getDb().prepare('SELECT id FROM subjects WHERE id = ? AND is_deleted = 0').get(subject_id);
    if (!sub) return res.status(400).json({ error: `Subject ${subject_id} not found` });
    const tch = getDb().prepare("SELECT id FROM users WHERE id = ? AND role IN ('teacher','school_admin','principal','systems_admin') AND is_deleted = 0").get(teacher_id);
    if (!tch) return res.status(400).json({ error: `Teacher ${teacher_id} not found` });
    const per = getDb().prepare('SELECT id FROM timetable_periods WHERE id = ? AND is_deleted = 0').get(period_id);
    if (!per) return res.status(400).json({ error: `Period ${period_id} not found` });

    // Conflict detection
    const candidate = { class_id, teacher_id, day_of_week, period_id, room };
    const conflicts = detectConflicts(candidate);
    if (conflicts.length > 0) {
      return res.status(409).json({
        error: 'Timetable conflict(s) detected',
        code: 'TIMETABLE_CONFLICT',
        conflicts,
      });
    }

    const uid = uuidv4();
    const result = getDb().prepare(`
      INSERT INTO timetable_entries
        (uid, class_id, subject_id, teacher_id, day_of_week, period_id, room, academic_year, term, notes, created_by)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(uid, class_id, subject_id, teacher_id, day_of_week, period_id, room || null, academic_year || null, term || null, notes || null, req.user.id);
    const row = getDb().prepare(`
      SELECT e.*, c.name AS class_name, sub.name AS subject_name,
             u.first_name || ' ' || u.last_name AS teacher_name,
             p.name AS period_name, p.start_time, p.end_time
      FROM timetable_entries e
      JOIN classes c ON e.class_id = c.id
      JOIN subjects sub ON e.subject_id = sub.id
      JOIN users u ON e.teacher_id = u.id
      JOIN timetable_periods p ON e.period_id = p.id
      WHERE e.id = ?
    `).get(result.lastInsertRowid);
    res.status(201).json(enrichEntry(row));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.patch('/entries/:id', auth, (req, res) => {
  try {
    const existing = getDb().prepare('SELECT * FROM timetable_entries WHERE id = ? AND is_deleted = 0').get(req.params.id);
    if (!existing) return res.status(404).json({ error: 'Entry not found' });
    if (req.user.role === 'teacher' && Number(existing.teacher_id) !== Number(req.user.id)) {
      return res.status(403).json({ error: 'Teachers can only modify their own entries' });
    }
    const { class_id, subject_id, teacher_id, day_of_week, period_id, room, academic_year, term, notes } = req.body || {};
    const merged = {
      class_id: class_id ?? existing.class_id,
      subject_id: subject_id ?? existing.subject_id,
      teacher_id: teacher_id ?? existing.teacher_id,
      day_of_week: day_of_week ?? existing.day_of_week,
      period_id: period_id ?? existing.period_id,
      room: room === undefined ? existing.room : room,
    };
    // Re-detect conflicts against the merged candidate
    const conflicts = detectConflicts(merged, Number(req.params.id));
    if (conflicts.length > 0) {
      return res.status(409).json({
        error: 'Timetable conflict(s) detected',
        code: 'TIMETABLE_CONFLICT',
        conflicts,
      });
    }
    getDb().prepare(`
      UPDATE timetable_entries
      SET class_id = COALESCE(?, class_id),
          subject_id = COALESCE(?, subject_id),
          teacher_id = COALESCE(?, teacher_id),
          day_of_week = COALESCE(?, day_of_week),
          period_id = COALESCE(?, period_id),
          room = COALESCE(?, room),
          academic_year = COALESCE(?, academic_year),
          term = COALESCE(?, term),
          notes = COALESCE(?, notes),
          updated_at = datetime('now','localtime'),
          sync_status = 'pending'
      WHERE id = ?
    `).run(
      class_id ?? null,
      subject_id ?? null,
      teacher_id ?? null,
      day_of_week ?? null,
      period_id ?? null,
      room === undefined ? null : room,
      academic_year ?? null,
      term ?? null,
      notes ?? null,
      req.params.id
    );
    const row = getDb().prepare(`
      SELECT e.*, c.name AS class_name, sub.name AS subject_name,
             u.first_name || ' ' || u.last_name AS teacher_name,
             p.name AS period_name, p.start_time, p.end_time
      FROM timetable_entries e
      JOIN classes c ON e.class_id = c.id
      JOIN subjects sub ON e.subject_id = sub.id
      JOIN users u ON e.teacher_id = u.id
      JOIN timetable_periods p ON e.period_id = p.id
      WHERE e.id = ?
    `).get(req.params.id);
    res.json(enrichEntry(row));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.delete('/entries/:id', auth, adminOrPrincipal, (req, res) => {
  try {
    const existing = getDb().prepare('SELECT id FROM timetable_entries WHERE id = ? AND is_deleted = 0').get(req.params.id);
    if (!existing) return res.status(404).json({ error: 'Entry not found' });
    getDb().prepare(`UPDATE timetable_entries SET is_deleted = 1, updated_at = datetime('now','localtime'), sync_status = 'pending' WHERE id = ?`).run(req.params.id);
    res.json({ success: true, id: Number(req.params.id) });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// ============ CONFLICTS / VIEWS ============

router.get('/conflicts', auth, adminOrPrincipal, (req, res) => {
  try {
    const { class_id, teacher_id, room } = req.query;
    const conflicts = [];
    if (class_id) {
      const rows = getDb().prepare(`
        SELECT e.id, e.class_id, e.teacher_id, e.day_of_week, e.period_id, COUNT(*) AS dup_count
        FROM timetable_entries e
        WHERE e.is_deleted = 0 AND e.class_id = ?
        GROUP BY e.class_id, e.day_of_week, e.period_id
        HAVING dup_count > 1
      `).all(Number(class_id));
      for (const r of rows) conflicts.push({ kind: 'class_double_booked', ...r });
    }
    if (teacher_id) {
      const rows = getDb().prepare(`
        SELECT e.id, e.teacher_id, e.class_id, e.day_of_week, e.period_id, COUNT(*) AS dup_count
        FROM timetable_entries e
        WHERE e.is_deleted = 0 AND e.teacher_id = ?
        GROUP BY e.teacher_id, e.day_of_week, e.period_id
        HAVING dup_count > 1
      `).all(Number(teacher_id));
      for (const r of rows) conflicts.push({ kind: 'teacher_double_booked', ...r });
    }
    if (room) {
      const rows = getDb().prepare(`
        SELECT e.id, e.room, e.class_id, e.day_of_week, e.period_id, COUNT(*) AS dup_count
        FROM timetable_entries e
        WHERE e.is_deleted = 0 AND LOWER(TRIM(e.room)) = LOWER(TRIM(?))
        GROUP BY e.room, e.day_of_week, e.period_id
        HAVING dup_count > 1
      `).all(String(room));
      for (const r of rows) conflicts.push({ kind: 'room_double_booked', ...r });
    }
    res.json({ conflicts });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.get('/class/:classId', auth, (req, res) => {
  try {
    const rows = getDb().prepare(`
      SELECT e.*, sub.name AS subject_name,
             u.first_name || ' ' || u.last_name AS teacher_name,
             p.name AS period_name, p.start_time, p.end_time, p.period_order
      FROM timetable_entries e
      JOIN subjects sub ON e.subject_id = sub.id
      JOIN users u ON e.teacher_id = u.id
      JOIN timetable_periods p ON e.period_id = p.id
      WHERE e.is_deleted = 0 AND e.class_id = ?
      ORDER BY e.day_of_week ASC, p.period_order ASC
    `).all(req.params.classId).map(enrichEntry);
    res.json({ class_id: Number(req.params.classId), entries: rows });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

router.get('/teacher/:teacherId', auth, (req, res) => {
  try {
    const rows = getDb().prepare(`
      SELECT e.*, c.name AS class_name, sub.name AS subject_name,
             p.name AS period_name, p.start_time, p.end_time, p.period_order
      FROM timetable_entries e
      JOIN classes c ON e.class_id = c.id
      JOIN subjects sub ON e.subject_id = sub.id
      JOIN timetable_periods p ON e.period_id = p.id
      WHERE e.is_deleted = 0 AND e.teacher_id = ?
      ORDER BY e.day_of_week ASC, p.period_order ASC
    `).all(req.params.teacherId).map(enrichEntry);
    res.json({ teacher_id: Number(req.params.teacherId), entries: rows });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;
