/**
 * Messages Controller
 * Internal messaging system
 */

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

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

const getDb = () => {
  if (!db) {
    db = new Database(dbPath);
    db.pragma('foreign_keys = ON');
    
    // Auto-migrate tables to support file/image attachments + delivery receipts
    try {
      db.exec(`ALTER TABLE messages ADD COLUMN attachment_url TEXT;`);
    } catch (e) {}
    try {
      db.exec(`ALTER TABLE messages ADD COLUMN attachment_name TEXT;`);
    } catch (e) {}
    try {
      db.exec(`ALTER TABLE messages ADD COLUMN attachment_type TEXT;`);
    } catch (e) {}
    try {
      db.exec(`ALTER TABLE messages ADD COLUMN is_delivered INTEGER DEFAULT 0;`);
    } catch (e) {}
    try {
      db.exec(`ALTER TABLE messages ADD COLUMN group_id INTEGER REFERENCES chat_groups(id);`);
    } catch (e) {}
  }
  return db;
};

// Configure Multer for attachments
const storage = multer.diskStorage({
  destination: function (req, file, cb) {
    const uploadDir = path.join(__dirname, '../../uploads');
    if (!fs.existsSync(uploadDir)){
        fs.mkdirSync(uploadDir, { recursive: true });
    }
    cb(null, uploadDir);
  },
  filename: function (req, file, cb) {
    const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
    cb(null, uniqueSuffix + path.extname(file.originalname));
  }
});

const upload = multer({ 
  storage: storage,
  limits: { fileSize: 10 * 1024 * 1024 } // 10MB limit
});

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

// ============ MESSAGES CRUD ============

// List messages (inbox)
/**
 * @openapi
 * /api/messages:
 *   get:
 *     summary: List messages
 *     description: |
 *       Returns the caller's inbox (default) or sent folder. The
 *       `folder` query param switches between `inbox` and `sent`.
 *       The WebSocket hub broadcasts `message:new` to recipients
 *       and `message:sent` to senders on a new message (see
 *       `services/wsHub.js`).
 *     tags:
 *       - Messages
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: folder
 *         schema: { type: string, enum: [inbox, sent, trash] }
 *         description: Mailbox folder; defaults to `inbox`.
 *       - in: query
 *         name: unread_only
 *         schema: { type: string, enum: ["true", "false"] }
 *         description: When `true`, restricts inbox to unread messages.
 *       - in: query
 *         name: sender_id
 *         schema: { type: integer }
 *         description: Filter inbox by the user id of the sender.
 *     responses:
 *       200:
 *         description: Message list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/', auth, (req, res) => {
  try {
    const { folder = 'inbox', unread_only, sender_id } = req.query;
    
    let query;
    const params = [req.user.id];
    
    if (folder === 'sent') {
      query = `
        SELECT m.*, u.first_name || ' ' || u.last_name as recipient_name, u.email as recipient_email
        FROM messages m
        LEFT JOIN users u ON m.recipient_id = u.id
        WHERE m.sender_id = ? AND m.is_deleted = 0
        ORDER BY m.created_at DESC
      `;
    } else {
      query = `
        SELECT m.*, u.first_name || ' ' || u.last_name as sender_name, u.email as sender_email
        FROM messages m
        LEFT JOIN users u ON m.sender_id = u.id
        WHERE m.recipient_id = ? AND m.is_deleted = 0
        ORDER BY m.created_at DESC
      `;
    }
    
    if (unread_only === 'true') {
      query = query.replace('is_deleted = 0', 'is_deleted = 0 AND m.is_read = 0');
    }
    
    if (sender_id && folder === 'inbox') {
      query = query.replace('is_deleted = 0', 'is_deleted = 0 AND m.sender_id = ?');
      params.unshift(sender_id);
    }
    
    const messages = getDb().prepare(query).all(...params);
    res.json(messages);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Get unread count
/**
 * @openapi
 * /api/messages/unread/count:
 *   get:
 *     summary: Get unread message count
 *     description: |
 *       Returns the number of unread, non-deleted messages in the
 *       caller's inbox.
 *     tags:
 *       - Messages
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Unread count
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 count: { type: integer }
 *       401: { description: Unauthorized }
 */
router.get('/unread/count', auth, (req, res) => {
  try {
    const result = getDb().prepare(`
      SELECT COUNT(*) as count FROM messages WHERE recipient_id = ? AND is_read = 0 AND is_deleted = 0
    `).get(req.user.id);
    
    res.json({ count: result.count });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Get announcements
/**
 * @openapi
 * /api/messages/announcements:
 *   get:
 *     summary: List broadcast announcements
 *     description: |
 *       Returns the most recent 50 broadcast announcements
 *       (`is_announcement = 1`). New announcements are pushed to all
 *       connected users via the WebSocket hub
 *       (`announcement:new` event).
 *     tags:
 *       - Messages
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Announcement list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Create a broadcast announcement
 *     description: |
 *       Creates a new broadcast announcement visible to all users.
 *       Restricted to admins and teachers. The WebSocket hub
 *       broadcasts an `announcement:new` event to every active
 *       user.
 *     tags:
 *       - Messages
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - title
 *               - body
 *             properties:
 *               title: { type: string }
 *               body: { type: string }
 *               audience:
 *                 type: string
 *                 description: |
 *                   Optional audience tag (e.g. `all`, `staff`,
 *                   `students`).
 *               subject: { type: string }
 *               priority: { type: string, enum: [low, normal, high] }
 *               attachment_url: { type: string }
 *               attachment_name: { type: string }
 *               attachment_type: { type: string }
 *     responses:
 *       201:
 *         description: Created announcement
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Teacher access required }
 */
router.get('/announcements', auth, (req, res) => {
  try {
    const announcements = getDb().prepare(`
      SELECT m.*, u.first_name || ' ' || u.last_name as sender_name
      FROM messages m
      LEFT JOIN users u ON m.sender_id = u.id
      WHERE m.is_announcement = 1 AND m.is_deleted = 0
      ORDER BY m.created_at DESC
      LIMIT 50
    `).all();

    try {
      getDb().prepare(`
        UPDATE notifications
        SET status = 'read', read_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
        WHERE recipient_id = ? AND status != 'read' AND (related_resource LIKE 'announcement:%' OR metadata LIKE '%"kind":"announcement"%')
      `).run(req.user.id);
    } catch {}
    
    res.json(announcements);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Get user contacts (for messaging)
/**
 * @openapi
 * /api/messages/contacts:
 *   get:
 *     summary: List messaging contacts
 *     description: |
 *       Returns the active users (and group/club addresses)
 *       available for the caller to message. Excludes the caller
 *       themselves. Capped at 100 results.
 *     tags:
 *       - Messages
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: search
 *         schema: { type: string }
 *         description: Free-text search across name and email.
 *       - in: query
 *         name: role
 *         schema: { type: string }
 *         description: Restrict to a single role (e.g. `student`).
 *     responses:
 *       200:
 *         description: Contact list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/contacts', auth, (req, res) => {
  try {
    const { search, role } = req.query;
    const callerRole = req.user.role;
    const callerId = req.user.id;

    // Role-based scoping: a contact list must respect the caller's role so a
    // parent cannot enumerate every student in the system, etc. The base
    // predicate is always "active, not deleted, not me". We append a role-
    // specific whitelist of allowed user IDs so contacts that don't belong
    // to the caller's ecosystem are filtered out.
    let allowedIdsSql = null;
    const allowedParams = [];

    if (callerRole === 'parent') {
      // Parents see only the teachers of their linked children + school
      // admins (for support). Never other parents' children.
      allowedIdsSql = `
        SELECT DISTINCT t.id FROM users t
        WHERE t.is_deleted = 0 AND t.is_active = 1 AND t.id != ?
          AND (
            t.role IN ('school_admin','systems_admin','principal','hr','bursar','accountant','librarian','clubs_head','nurse','dining_staff')
            OR t.id IN (
              SELECT s.teacher_id FROM subjects s
              JOIN enrollments e ON e.class_id = s.class_id AND e.status = 'active' AND e.is_deleted = 0
              JOIN parent_students ps ON ps.student_id = e.student_id AND ps.is_deleted = 0
              WHERE ps.parent_id = ? AND s.teacher_id IS NOT NULL
            )
            OR t.id IN (
              SELECT sub.teacher_id FROM subjects sub
              JOIN course_enrolments ce ON ce.course_id = (
                SELECT c.id FROM courses c WHERE c.subject_id = sub.id LIMIT 1
              ) AND ce.status = 'active' AND ce.is_deleted = 0
              JOIN parent_students ps ON ps.student_id = ce.student_id AND ps.is_deleted = 0
              WHERE ps.parent_id = ? AND sub.teacher_id IS NOT NULL
            )
          )
      `;
      allowedParams.push(callerId, callerId, callerId);
    } else if (callerRole === 'student') {
      // Students see their class teachers + all admins/staff for support.
      allowedIdsSql = `
        SELECT DISTINCT t.id FROM users t
        WHERE t.is_deleted = 0 AND t.is_active = 1 AND t.id != ?
          AND (
            t.role IN ('school_admin','systems_admin','principal','hr','bursar','accountant','librarian','clubs_head','nurse','dining_staff')
            OR t.id IN (
              SELECT s.teacher_id FROM subjects s
              JOIN enrollments e ON e.class_id = s.class_id AND e.status = 'active' AND e.is_deleted = 0
              WHERE e.student_id = ? AND s.teacher_id IS NOT NULL
            )
            OR t.id IN (
              SELECT ct.class_teacher_id FROM classes ct
              JOIN enrollments e ON e.class_id = ct.id AND e.status = 'active' AND e.is_deleted = 0
              WHERE e.student_id = ? AND ct.class_teacher_id IS NOT NULL
            )
          )
      `;
      allowedParams.push(callerId, callerId, callerId);
    } else if (callerRole === 'teacher') {
      // Teachers see students in their classes (active enrollments) +
      // their subject teachers + school staff.
      allowedIdsSql = `
        SELECT DISTINCT t.id FROM users t
        WHERE t.is_deleted = 0 AND t.is_active = 1 AND t.id != ?
          AND (
            t.role IN ('school_admin','systems_admin','principal','hr','bursar','accountant','librarian','clubs_head','nurse','dining_staff')
            OR t.role = 'teacher'
            OR t.id IN (
              SELECT e.student_id FROM enrollments e
              JOIN subjects s ON s.class_id = e.class_id AND s.teacher_id = ? AND s.is_deleted = 0
              WHERE e.status = 'active' AND e.is_deleted = 0
            )
            OR t.id IN (
              SELECT ct.id FROM users ct
              JOIN classes cl ON cl.class_teacher_id = ct.id AND cl.is_deleted = 0
              JOIN enrollments e ON e.class_id = cl.id AND e.status = 'active' AND e.is_deleted = 0
            )
          )
      `;
      allowedParams.push(callerId, callerId);
    } else {
      // Admin / principal / hr / bursar / librarian / nurse / accountant /
      // clubs_head / dining_staff / systems_admin: see all staff + teachers.
      // Excludes students and parents from the contact list to keep PII
      // scoped (use dedicated endpoints like /api/users?role=student when
      // an admin genuinely needs the full roster).
      allowedIdsSql = `
        SELECT id FROM users
        WHERE is_deleted = 0 AND is_active = 1 AND id != ?
          AND role NOT IN ('student','parent')
      `;
      allowedParams.push(callerId);
    }

    let query = `
      SELECT id, first_name, last_name, email, role, profile_image
      FROM users
      WHERE is_deleted = 0 AND is_active = 1 AND id != ?
        AND email NOT LIKE 'test-%' AND email NOT LIKE 'dup_%'
        AND id IN (${allowedIdsSql})
    `;
    const params = [callerId, ...allowedParams];

    if (search) {
      query += ' AND (first_name LIKE ? OR last_name LIKE ? OR email LIKE ?)';
      const searchTerm = `%${search}%`;
      params.push(searchTerm, searchTerm, searchTerm);
    }

    if (role) {
      query += ' AND role = ?';
      params.push(role);
    }

    query += ' ORDER BY last_name, first_name LIMIT 100';

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


// ============ CONVERSATIONS ============

// Get recent conversations (direct, groups, clubs)
/**
 * @openapi
 * /api/messages/conversations:
 *   get:
 *     summary: List conversations
 *     description: |
 *       Returns the conversation index for the caller, merging
 *       direct messages, chat groups, and club/sport chatrooms the
 *       caller has access to. Ordered by most recent activity.
 *     tags:
 *       - Messages
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Conversation index
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/conversations', auth, (req, res) => {
  try {
    // Orphaned DMs (recipient_id IS NULL) collapse the partition to a NULL
    // `other_user_id`. Those rows are unusable in the UI (no recipient to
    // click into, no name to render), so drop them here rather than
    // crashing the client's conversation filter on `null.toLowerCase()`.
    const conversations = getDb().prepare(`
      SELECT * FROM (
        -- Direct Messages
        SELECT
          CAST(other_user_id AS TEXT) as other_user_id,
          other_user_name,
          last_message,
          last_message_at,
          (SELECT COUNT(*) FROM messages WHERE sender_id = other_user_id AND recipient_id = ? AND is_read = 0 AND group_id IS NULL) as unread_count,
          last_sender_id,
          last_is_read,
          last_is_delivered,
          last_id
        FROM (
          SELECT
            CASE WHEN m.sender_id = ? THEN m.recipient_id ELSE m.sender_id END as other_user_id,
            u.first_name || ' ' || u.last_name as other_user_name,
            m.body as last_message,
            m.created_at as last_message_at,
            m.sender_id as last_sender_id,
            m.is_read as last_is_read,
            m.is_delivered as last_is_delivered,
            m.id as last_id,
            ROW_NUMBER() OVER (PARTITION BY CASE WHEN m.sender_id = ? THEN m.recipient_id ELSE m.sender_id END ORDER BY m.created_at DESC) as rn
          FROM messages m
          LEFT JOIN users u ON CASE WHEN m.sender_id = ? THEN m.recipient_id ELSE m.sender_id END = u.id
          WHERE (m.sender_id = ? OR m.recipient_id = ?)
            AND m.is_deleted = 0 AND m.group_id IS NULL
            AND m.recipient_id IS NOT NULL AND m.sender_id IS NOT NULL
        ) sub
        WHERE rn = 1 AND other_user_id IS NOT NULL

        UNION ALL

        -- Group Messages
        SELECT
          'g_' || g.id as other_user_id,
          g.name as other_user_name,
          COALESCE(m.body, 'Group created') as last_message,
          COALESCE(m.created_at, g.created_at) as last_message_at,
          0 as unread_count,
          COALESCE(m.last_sender_id, g.created_by) as last_sender_id,
          1 as last_is_read,
          1 as last_is_delivered,
          COALESCE(m.last_id, 0) as last_id
        FROM chat_groups g
        JOIN chat_group_members cgm ON g.id = cgm.group_id
        LEFT JOIN (
          SELECT group_id, body, sender_id as last_sender_id, id as last_id, created_at, ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY created_at DESC) as rn
          FROM messages WHERE group_id IS NOT NULL AND is_deleted = 0
        ) m ON m.group_id = g.id AND m.rn = 1
        WHERE cgm.user_id = ? AND g.is_deleted = 0

        UNION ALL

        -- Club/Sport Chatrooms
        SELECT
          'c_' || c.id as other_user_id,
          c.name || ' (' || CASE WHEN c.type = 'sport' THEN 'Sport' ELSE 'Club' END || ')' as other_user_name,
          COALESCE(cc.message, 'No messages yet') as last_message,
          COALESCE(cc.created_at, c.created_at) as last_message_at,
          0 as unread_count,
          COALESCE(cc.sender_id, c.teacher_in_charge_id) as last_sender_id,
          1 as last_is_read,
          1 as last_is_delivered,
          0 as last_id
        FROM clubs c
        LEFT JOIN (
          SELECT club_id, message, sender_id, created_at, ROW_NUMBER() OVER(PARTITION BY club_id ORDER BY created_at DESC) as rn
          FROM club_chats WHERE is_deleted = 0
        ) cc ON cc.club_id = c.id AND cc.rn = 1
        WHERE c.is_deleted = 0 AND (
          -- If user is student, check active membership
          (? = 'student' AND EXISTS (SELECT 1 FROM club_memberships WHERE club_id = c.id AND student_id = ? AND status = 'active' AND is_deleted = 0))
          OR
          -- If user is staff/teacher, check if they are the teacher in charge, or if they are admin/clubs_head (can access all)
          (? != 'student' AND (c.teacher_in_charge_id = ? OR ? IN ('school_admin', 'systems_admin', 'clubs_head')))
        )
      )
      ORDER BY last_message_at DESC
    `).all(
      req.user.id, req.user.id, req.user.id, req.user.id, req.user.id, req.user.id, // Direct messages
      req.user.id, // Group messages
      req.user.role, req.user.id, req.user.role, req.user.id, req.user.role // Club messages
    );

    res.json(conversations);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Mark conversation as read (existing) + emit delivered receipts to senders
/**
 * @openapi
 * /api/messages/conversation/{chatId}:
 *   get:
 *     summary: Get a conversation thread
 *     description: |
 *       Returns the last 100 messages for the resolved chat. The
 *       `chatId` may be a numeric user id (direct message), `g_<id>`
 *       for a chat group, or `c_<id>` for a club/sport chatroom.
 *       Marks the thread as read and emits `message:delivered`
 *       receipts to original senders via the WebSocket hub (see
 *       `services/wsHub.js`).
 *     tags:
 *       - Messages
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: chatId
 *         required: true
 *         schema: { type: string }
 *         description: |
 *           Conversation id: numeric user id, `g_<id>` for groups,
 *           or `c_<id>` for clubs.
 *     responses:
 *       200:
 *         description: Conversation messages
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Not enrolled in this conversation }
 */
router.get('/conversation/:chatId', auth, (req, res) => {
  try {
    const chatId = req.params.chatId;
    const wsHub = req.app.get('wsHub');

    if (chatId.startsWith('g_')) {
      const groupId = parseInt(chatId.replace('g_', ''));

      // Verify membership
      const member = getDb().prepare('SELECT 1 FROM chat_group_members WHERE group_id = ? AND user_id = ?').get(groupId, req.user.id);
      if (!member) return res.status(403).json({ error: 'Access denied to group' });

      const messages = getDb().prepare(`
        SELECT m.*,
          CASE WHEN m.sender_id = ? THEN 'sent' ELSE 'received' END as direction,
          u.first_name || ' ' || u.last_name as sender_name
        FROM messages m
        LEFT JOIN users u ON m.sender_id = u.id
        WHERE m.group_id = ? AND m.is_deleted = 0
        ORDER BY m.created_at DESC
        LIMIT 100
      `).all(req.user.id, groupId);

      // Emit delivered receipts to original senders
      if (wsHub) {
        try {
          const pending = messages.filter((m) => m.sender_id !== req.user.id && m.is_delivered !== 1);
          getDb().prepare('UPDATE messages SET is_delivered = 1, updated_at = CURRENT_TIMESTAMP WHERE group_id = ? AND recipient_id = ? AND is_delivered = 0').run(groupId, req.user.id);
          for (const m of pending) {
            wsHub.broadcastToUser(m.sender_id, {
              type: 'message:delivered',
              id: m.id,
              uid: m.uid,
              chat_id: `g_${groupId}`,
            });
          }
        } catch {}
      }

      try {
        getDb().prepare(`
          UPDATE notifications
          SET status = 'read', read_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
          WHERE recipient_id = ? AND status != 'read' AND metadata LIKE ?
        `).run(req.user.id, `%"groupId":${groupId}%`);
      } catch {}

      res.json(messages);
    } else if (chatId.startsWith('c_')) {
      const clubId = parseInt(chatId.replace('c_', ''));

      // Verify access: user must be admin, staff or enrolled in the club
      const isStaff = ['school_admin', 'systems_admin', 'principal', 'teacher', 'clubs_head'].includes(req.user.role);
      if (!isStaff) {
        const membership = getDb().prepare("SELECT id FROM club_memberships WHERE club_id = ? AND student_id = ? AND status = 'active' AND is_deleted = 0").get(clubId, req.user.id);
        if (!membership) {
          return res.status(403).json({ error: 'You are not enrolled in this extracurricular activity' });
        }
      }

      const messages = getDb().prepare(`
        SELECT cc.id, cc.uid, cc.sender_id, cc.message as body, cc.image_url as attachment_url, cc.created_at,
          CASE WHEN cc.sender_id = ? THEN 'sent' ELSE 'received' END as direction,
          u.first_name || ' ' || u.last_name as sender_name,
          u.role as sender_role
        FROM club_chats cc
        JOIN users u ON cc.sender_id = u.id
        WHERE cc.club_id = ? AND cc.is_deleted = 0
        ORDER BY cc.created_at DESC
        LIMIT 100
      `).all(req.user.id, clubId);

      // Map to ChatMessage format (e.g. attachment_type, attachment_name)
      const mapped = messages.map(m => ({
        ...m,
        attachment_name: m.attachment_url ? path.basename(m.attachment_url) : null,
        attachment_type: m.attachment_url ? 'image/jpeg' : null
      }));

      try {
        getDb().prepare(`
          UPDATE notifications
          SET status = 'read', read_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
          WHERE recipient_id = ? AND status != 'read' AND metadata LIKE ?
        `).run(req.user.id, `%"clubId":${clubId}%`);
      } catch {}

      res.json(mapped);
    } else {
      const otherUserId = parseInt(chatId);

      const messages = getDb().prepare(`
        SELECT m.*,
          CASE WHEN m.sender_id = ? THEN 'sent' ELSE 'received' END as direction,
          u.first_name || ' ' || u.last_name as other_user_name
        FROM messages m
        LEFT JOIN users u ON CASE WHEN m.sender_id = ? THEN m.recipient_id ELSE m.sender_id END = u.id
        WHERE ((m.sender_id = ? AND m.recipient_id = ?) OR (m.sender_id = ? AND m.recipient_id = ?))
          AND m.is_deleted = 0 AND m.group_id IS NULL
        ORDER BY m.created_at DESC
        LIMIT 100
      `).all(req.user.id, req.user.id, req.user.id, otherUserId, otherUserId, req.user.id);

      // Mark as read AND delivered (emit delivered receipt to sender)
      getDb().prepare(`
        UPDATE messages SET is_read = 1, is_delivered = 1, updated_at = CURRENT_TIMESTAMP
        WHERE recipient_id = ? AND sender_id = ? AND is_delivered = 0 AND group_id IS NULL
      `).run(req.user.id, otherUserId);

      try {
        getDb().prepare(`
          UPDATE notifications
          SET status = 'read', read_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
          WHERE recipient_id = ? AND status != 'read' AND (
            metadata LIKE ? OR related_resource LIKE ?
          )
        `).run(req.user.id, `%"sender_id":${otherUserId}%`, `message:%`);
      } catch {}

      if (wsHub) {
        try {
          const pending = messages.filter((m) => m.sender_id === otherUserId && m.is_delivered !== 1);
          for (const m of pending) {
            wsHub.broadcastToUser(m.sender_id, {
              type: 'message:delivered',
              id: m.id,
              uid: m.uid,
              chat_id: otherUserId,
            });
          }
        } catch {}
      }

      res.json(messages);
    }
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Get single message
/**
 * @openapi
 * /api/messages/{id}:
 *   get:
 *     summary: Get a single message
 *     description: |
 *       Returns a single message by id. Access is restricted to
 *       the sender, the recipient, admins, or to anyone when the
 *       message is a broadcast announcement. Marks the message as
 *       read when the caller is the recipient.
 *     tags:
 *       - Messages
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Message
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Access denied }
 *       404: { description: Message not found }
 *   put:
 *     summary: Update a message
 *     description: |
 *       Currently supports toggling the `is_read` flag for the
 *       caller's own received message. Only the recipient or an
 *       admin may update.
 *     tags:
 *       - Messages
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               is_read: { type: boolean }
 *     responses:
 *       200:
 *         description: Updated message
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Access denied }
 *       404: { description: Message not found }
 *   delete:
 *     summary: Soft-delete a message
 *     description: |
 *       Marks the message as deleted (`is_deleted = 1`). Allowed
 *       for the sender, recipient, or an admin.
 *     tags:
 *       - Messages
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Deleted
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Access denied }
 *       404: { description: Message not found }
 */
router.get('/:id', auth, (req, res) => {
  try {
    const message = getDb().prepare(`
      SELECT m.*, 
        s.first_name || ' ' || s.last_name as sender_name,
        r.first_name || ' ' || r.last_name as recipient_name
      FROM messages m
      LEFT JOIN users s ON m.sender_id = s.id
      LEFT JOIN users r ON m.recipient_id = r.id
      WHERE m.id = ? AND m.is_deleted = 0
    `).get(req.params.id);
    
    if (!message) {
      return res.status(404).json({ error: 'Message not found' });
    }
    
    // Check access
    if (message.is_announcement !== 1 && message.sender_id !== req.user.id && message.recipient_id !== req.user.id && !['school_admin', 'systems_admin'].includes(req.user.role)) {
      return res.status(403).json({ error: 'Access denied' });
    }
    
    // Mark as read
    if (message.recipient_id === req.user.id && message.is_read === 0) {
      getDb().prepare('UPDATE messages SET is_read = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?').run(req.params.id);
      message.is_read = 1;
    }
    
    res.json(message);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Upload attachment endpoint
/**
 * @openapi
 * /api/messages/upload:
 *   post:
 *     summary: Upload a message attachment
 *     description: |
 *       Uploads a single file (max 10 MB) and returns the relative
 *       URL to attach to a message via `attachment_url`.
 *     tags:
 *       - Messages
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         multipart/form-data:
 *           schema:
 *             type: object
 *             required:
 *               - file
 *             properties:
 *               file:
 *                 type: string
 *                 format: binary
 *                 description: File to attach (max 10 MB).
 *     responses:
 *       200:
 *         description: Uploaded file metadata
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 fileUrl: { type: string }
 *                 fileName: { type: string }
 *                 fileType: { type: string }
 *                 fileSize: { type: integer }
 *       400: { description: No file uploaded }
 *       401: { description: Unauthorized }
 */
router.post('/upload', auth, upload.single('file'), (req, res) => {
  if (!req.file) {
    return res.status(400).json({ error: 'No file uploaded' });
  }
  const fileUrl = `/uploads/${req.file.filename}`;
  res.json({ 
    fileUrl,
    fileName: req.file.originalname,
    fileType: req.file.mimetype,
    fileSize: req.file.size
  });
});

// Send message
const { notifyMessageRecipients } = require('./notifications.controller');
/**
 * @openapi
 * /api/messages:
 *   post:
 *     summary: Send a message
 *     description: |
 *       Sends a new direct message, group message, or club chat
 *       message. The `recipient_id` may be a numeric user id, a
 *       `g_<id>` group id, or a `c_<id>` club id. The WebSocket
 *       hub broadcasts `message:new` to recipients and
 *       `message:sent` back to the sender (see
 *       `services/wsHub.js`).
 *     tags:
 *       - Messages
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - recipient_id
 *               - subject
 *             properties:
 *               recipient_id:
 *                 type: string
 *                 description: |
 *                   Numeric user id, `g_<id>` for chat groups, or
 *                   `c_<id>` for club/sport chatrooms.
 *               subject: { type: string }
 *               body: { type: string }
 *               priority: { type: string, enum: [low, normal, high] }
 *               is_announcement: { type: boolean }
 *               attachment_url: { type: string }
 *               attachment_name: { type: string }
 *               attachment_type: { type: string }
 *     responses:
 *       201:
 *         description: Created message
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: recipient_id and subject are required }
 *       401: { description: Unauthorized }
 *       403: { description: Not enrolled in this club/chat }
 */
router.post('/', auth, (req, res) => {
  try {
    const { recipient_id, subject, body, priority, is_announcement, attachment_url, attachment_name, attachment_type } = req.body;
    
    if (!recipient_id || !subject) {
      return res.status(400).json({ error: 'recipient_id and subject are required' });
    }
    
    const uid = uuidv4();
    let result;
    let message;
    
if (String(recipient_id).startsWith('g_')) {
       const groupId = parseInt(String(recipient_id).replace('g_', ''));
       result = getDb().prepare(`
        INSERT INTO messages (uid, sender_id, group_id, subject, body, priority, is_announcement, attachment_url, attachment_name, attachment_type)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `).run(uid, req.user.id, groupId, subject, body || '', priority || 'normal', 0, attachment_url || null, attachment_name || null, attachment_type || null);

       message = getDb().prepare(`
        SELECT m.*, 'g_' || m.group_id as recipient_id
        FROM messages m
        WHERE m.id = ?
       `).get(result.lastInsertRowid);

       // Real-time push + in-app notifications to all group members
       const wsHub = req.app.get('wsHub');
       const memberRows = getDb().prepare('SELECT user_id FROM chat_group_members WHERE group_id = ?').all(groupId);
       const memberIds = memberRows.map((m) => m.user_id);
       notifyMessageRecipients({
         recipients: memberIds,
         sender_id: req.user.id,
         subject,
         body,
         kind: 'group_message',
         related_resource: `message:${message.id}`,
       });
       if (wsHub) {
         try {
           for (const m of memberRows) {
             wsHub.broadcastToUser(m.user_id, {
               type: 'message:new',
               message: { ...message, direction: m.user_id === req.user.id ? 'sent' : 'received' },
             });
           }
         } catch {}
       }
    } else if (String(recipient_id).startsWith('c_')) {
       const clubId = parseInt(String(recipient_id).replace('c_', ''));

       // Verify access: user must be admin, staff or enrolled in the club
       const isStaff = ['school_admin', 'systems_admin', 'principal', 'teacher', 'clubs_head'].includes(req.user.role);
       if (!isStaff) {
         const membership = getDb().prepare("SELECT id FROM club_memberships WHERE club_id = ? AND student_id = ? AND status = 'active' AND is_deleted = 0").get(clubId, req.user.id);
         if (!membership) {
           return res.status(403).json({ error: 'You are not enrolled in this extracurricular activity' });
         }
       }

       result = getDb().prepare(`
         INSERT INTO club_chats (uid, club_id, sender_id, message, image_url)
         VALUES (?, ?, ?, ?, ?)
       `).run(uid, clubId, req.user.id, body || null, attachment_url || null);

const ccMsg = getDb().prepare(`
          SELECT cc.id, cc.uid, cc.sender_id, cc.message as body, cc.image_url as attachment_url, cc.created_at,
            'c_' || cc.club_id as recipient_id,
            u.first_name || ' ' || u.last_name as sender_name
          FROM club_chats cc
          JOIN users u ON cc.sender_id = u.id
          WHERE cc.id = ?
        `).get(result.lastInsertRowid);

        // Real-time push + in-app notifications to all active club members
        const wsHub = req.app.get('wsHub');
        const memberRows = getDb().prepare("SELECT student_id FROM club_memberships WHERE club_id = ? AND status = 'active' AND is_deleted = 0").all(clubId);
        const memberIds = memberRows.map((m) => m.student_id);
        notifyMessageRecipients({
          recipients: memberIds,
          sender_id: req.user.id,
          subject,
          body: body || '',
          kind: 'club_message',
          related_resource: `club_message:${ccMsg.id}`,
        });
        if (wsHub) {
          try {
            for (const m of memberRows) {
              wsHub.broadcastToUser(m.student_id, {
                type: 'message:new',
                message: { ...ccMsg, direction: m.student_id === req.user.id ? 'sent' : 'received' },
              });
            }
          } catch {}
        }

       return res.status(201).json({
         ...ccMsg,
         attachment_name: ccMsg.attachment_url ? path.basename(ccMsg.attachment_url) : null,
         attachment_type: ccMsg.attachment_url ? 'image/jpeg' : null
       });
     } else {
       result = getDb().prepare(`
        INSERT INTO messages (uid, sender_id, recipient_id, subject, body, priority, is_announcement, attachment_url, attachment_name, attachment_type)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `).run(uid, req.user.id, recipient_id, subject, body || '', priority || 'normal', is_announcement ? 1 : 0, attachment_url || null, attachment_name || null, attachment_type || null);
      
message = getDb().prepare(`
         SELECT m.*, u.first_name || ' ' || u.last_name as recipient_name
         FROM messages m
         LEFT JOIN users u ON m.recipient_id = u.id
         WHERE m.id = ?
        `).get(result.lastInsertRowid);

        // In-app notification + real-time push to recipient.
        // The notification is persisted BEFORE the WebSocket push so the
        // bell-badge counter is consistent even when the recipient's tab
        // isn't currently open to receive the live event.
        notifyMessageRecipients({
          recipients: [recipient_id],
          sender_id: req.user.id,
          subject,
          body,
          kind: 'message',
          related_resource: `message:${message.id}`,
        });
        const wsHub = req.app.get('wsHub');
        if (wsHub && recipient_id) {
          try {
            wsHub.broadcastToUser(Number(recipient_id), {
              type: 'message:new',
              message: { ...message, direction: 'received' },
            });
            wsHub.broadcastToUser(req.user.id, {
              type: 'message:sent',
              message: { ...message, direction: 'sent' },
            });
          } catch {}
        }
    }
    
    res.status(201).json(message);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Reply to message
/**
 * @openapi
 * /api/messages/{id}/reply:
 *   post:
 *     summary: Reply to a message
 *     description: |
 *       Sends a reply to an existing message addressed to the
 *       caller. The reply is threaded by prepending `Re:` to the
 *       original subject.
 *     tags:
 *       - Messages
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *         description: Original message id.
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - body
 *             properties:
 *               body: { type: string }
 *     responses:
 *       201:
 *         description: Reply created
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Body is required }
 *       401: { description: Unauthorized }
 *       403: { description: Cannot reply to this message }
 *       404: { description: Original message not found }
 */
router.post('/:id/reply', auth, (req, res) => {
  try {
    const { body } = req.body;
    
    if (!body) {
      return res.status(400).json({ error: 'Body is required' });
    }
    
    const original = getDb().prepare(`
      SELECT * FROM messages WHERE id = ? AND is_deleted = 0
    `).get(req.params.id);
    
    if (!original) {
      return res.status(404).json({ error: 'Original message not found' });
    }
    
    // Can only reply to messages addressed to you
    if (original.recipient_id !== req.user.id) {
      return res.status(403).json({ error: 'Cannot reply to this message' });
    }
    
    const uid = uuidv4();
    
    const result = getDb().prepare(`
      INSERT INTO messages (uid, sender_id, recipient_id, subject, body, priority)
      VALUES (?, ?, ?, ?, ?, ?)
    `).run(uid, req.user.id, original.sender_id, `Re: ${original.subject}`, body, original.priority);
    
    const message = getDb().prepare('SELECT * FROM messages WHERE id = ?').get(result.lastInsertRowid);
    res.status(201).json(message);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Mark a single message as delivered (called by the recipient client)
/**
 * @openapi
 * /api/messages/{id}/delivered:
 *   put:
 *     summary: Mark a message delivered
 *     description: |
 *       Marks a single message as delivered and emits a
 *       `message:delivered` receipt to the original sender via the
 *       WebSocket hub (see `services/wsHub.js`). Driven by client
 *       delivery receipts; restricted to the recipient or admins.
 *     tags:
 *       - Messages
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *         description: Message id.
 *     responses:
 *       200:
 *         description: Marked delivered
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *                 id: { type: integer }
 *       401: { description: Unauthorized }
 *       403: { description: Access denied }
 *       404: { description: Message not found }
 */
router.put('/:id/delivered', auth, (req, res) => {
  try {
    const message = getDb().prepare('SELECT * FROM messages WHERE id = ?').get(req.params.id);
    if (!message) return res.status(404).json({ error: 'Message not found' });

    // Only the recipient (or staff/admin) can mark a message as delivered
    const canMark = message.recipient_id === req.user.id ||
      ['school_admin', 'systems_admin'].includes(req.user.role);
    if (!canMark) return res.status(403).json({ error: 'Access denied' });

    if (message.is_delivered !== 1) {
      getDb().prepare('UPDATE messages SET is_delivered = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?').run(req.params.id);
    }

    // Notify the sender via WebSocket
    const wsHub = req.app.get('wsHub');
    if (wsHub) {
      try {
        wsHub.broadcastToUser(message.sender_id, {
          type: 'message:delivered',
          id: message.id,
          uid: message.uid,
          chat_id: message.recipient_id,
        });
      } catch {}
    }

    res.json({ success: true, id: message.id });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Update message (mark read/unread)
router.put('/:id', auth, (req, res) => {
  try {
    const { is_read } = req.body;
    
    const message = getDb().prepare('SELECT * FROM messages WHERE id = ?').get(req.params.id);
    
    if (!message) {
      return res.status(404).json({ error: 'Message not found' });
    }
    
    // Only recipient can update
    if (message.recipient_id !== req.user.id && !['school_admin', 'systems_admin'].includes(req.user.role)) {
      return res.status(403).json({ error: 'Access denied' });
    }
    
    if (is_read !== undefined) {
      getDb().prepare('UPDATE messages SET is_read = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?').run(is_read ? 1 : 0, req.params.id);
    }
    
    const updated = getDb().prepare('SELECT * FROM messages WHERE id = ?').get(req.params.id);
    res.json(updated);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Delete message (soft delete)
router.delete('/:id', auth, (req, res) => {
  try {
    const message = getDb().prepare('SELECT * FROM messages WHERE id = ?').get(req.params.id);
    
    if (!message) {
      return res.status(404).json({ error: 'Message not found' });
    }
    
    // Can delete if you're sender or recipient
    if (message.sender_id !== req.user.id && message.recipient_id !== req.user.id && !['school_admin', 'systems_admin'].includes(req.user.role)) {
      return res.status(403).json({ error: 'Access denied' });
    }
    
    // Soft delete
    getDb().prepare('UPDATE messages SET is_deleted = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?').run(req.params.id);
    
    res.json({ success: true, message: 'Message deleted' });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Send announcement
router.post('/announcements', auth, (req, res) => {
  // Allowlist mirrors the client-side `canSendAnnouncements` in
  // client/src/pages/Messages.tsx so the UI composer and the API
  // accept the same roles. systems_admin and principal were added so
  // the school leadership and IT can broadcast without first having to
  // be promoted to school_admin.
  if (!['school_admin', 'systems_admin', 'principal', 'teacher'].includes(req.user.role)) {
    return res.status(403).json({ error: 'Admin or Teacher access required' });
  }
  
  try {
    const { recipient_id, subject, body, priority, attachment_url, attachment_name, attachment_type } = req.body;
    
    if (!subject) {
      return res.status(400).json({ error: 'Subject is required' });
    }
    
    const uid = uuidv4();
    
    const result = getDb().prepare(`
      INSERT INTO messages (uid, sender_id, recipient_id, subject, body, priority, is_announcement, attachment_url, attachment_name, attachment_type)
      VALUES (?, ?, ?, ?, ?, ?, 1, ?, ?, ?)
    `).run(uid, req.user.id, recipient_id || null, subject, body || '', priority || 'normal', attachment_url || null, attachment_name || null, attachment_type || null);
    
    const announcement = getDb().prepare('SELECT * FROM messages WHERE id = ?').get(result.lastInsertRowid);

    // In-app notification + real-time broadcast to every active user.
    const wsHub = req.app.get('wsHub');
    const allUsers = getDb().prepare('SELECT id FROM users WHERE is_active = 1 AND is_deleted = 0').all();
    notifyMessageRecipients({
      recipients: allUsers.map((u) => u.id),
      sender_id: req.user.id,
      subject,
      body,
      kind: 'announcement',
      related_resource: `announcement:${announcement.id}`,
    });
    if (wsHub) {
      try {
        for (const u of allUsers) {
          wsHub.broadcastToUser(u.id, {
            type: 'announcement:new',
            message: { ...announcement, direction: u.id === req.user.id ? 'sent' : 'received' },
          });
        }
      } catch {}
    }

    res.status(201).json(announcement);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ GROUPS ============

/**
 * @openapi
 * /api/messages/groups:
 *   post:
 *     summary: Create a chat group
 *     description: |
 *       Creates a new chat group with the caller as the creator.
 *       The creator and the supplied `member_ids` are added as
 *       members. The returned `id` is of the form `g_<id>` and
 *       can be used as a `recipient_id` in `POST /api/messages`.
 *     tags:
 *       - Messages
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - name
 *               - member_ids
 *             properties:
 *               name: { type: string }
 *               member_ids:
 *                 type: array
 *                 items: { type: integer }
 *                 description: User ids of additional group members.
 *     responses:
 *       201:
 *         description: Group created
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 id: { type: string, example: "g_42" }
 *                 name: { type: string }
 *       400: { description: Name and member_ids array are required }
 *       401: { description: Unauthorized }
 */
router.post('/groups', auth, (req, res) => {
  try {
    const { name, member_ids } = req.body;
    if (!name || !member_ids || !Array.isArray(member_ids)) {
      return res.status(400).json({ error: 'Name and member_ids array are required' });
    }

    const result = getDb().prepare(`
      INSERT INTO chat_groups (uid, name, created_by)
      VALUES (?, ?, ?)
    `).run(uuidv4(), name, req.user.id);

    const groupId = result.lastInsertRowid;

    // Add creator
    const members = [...new Set([...member_ids, req.user.id])];

    const insertMember = getDb().prepare(`
      INSERT INTO chat_group_members (group_id, user_id) VALUES (?, ?)
    `);

    const addMembers = getDb().transaction(() => {
      for (const id of members) {
        insertMember.run(groupId, id);
      }
    });
    addMembers();

    res.status(201).json({ id: `g_${groupId}`, name });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});



module.exports = router;
