/**
 * Sports & Clubs Controller
 * Handles clubs, sports teams, memberships, announcements, and club attendance
 */
const express = require('express');
const router = express.Router();
const path = require('path');
const Database = require('better-sqlite3');
const { v4: uuidv4 } = require('uuid');

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

// Auth middleware
const auth = (req, res, next) => {
  const token = req.headers.authorization?.split(' ')[1];
  if (!token) {
    console.error('Auth Error: No token provided for', req.originalUrl);
    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) {
    console.error('Auth Error: Invalid token for', req.originalUrl, err.message);
    res.status(401).json({ error: 'Invalid token' });
  }
};

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

// ============ CLUBS & SPORTS ============

/**
 * @openapi
 * /api/clubs:
 *   get:
 *     summary: List clubs
 *     description: |
 *       Returns the active clubs visible to the caller. Optional
 *       `type` filter narrows results to a category such as
 *       `sports` or `academic`.
 *     tags:
 *       - Clubs
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: type
 *         schema: { type: string }
 *     responses:
 *       200:
 *         description: Club list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Create club
 *     description: |
 *       Creates a new club. Restricted to staff roles
 *       (`school_admin`, `systems_admin`, `principal`, `teacher`,
 *       `clubs_head`).
 *     tags:
 *       - Clubs
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - name
 *               - type
 *             properties:
 *               name: { type: string }
 *               type: { type: string }
 *               description: { type: string }
 *               teacher_in_charge_id: { type: integer }
 *               schedule: { type: string }
 *               max_members: { type: integer }
 *     responses:
 *       200:
 *         description: Created club
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Staff only }
 */
router.get('/', auth, (req, res) => {
  const { type } = req.query;
  let query = `
    SELECT c.*, u.first_name || ' ' || u.last_name as teacher_name,
      (SELECT COUNT(*) FROM club_memberships WHERE club_id = c.id AND status = 'active' AND is_deleted = 0) as current_members
    FROM clubs c
    LEFT JOIN users u ON c.teacher_in_charge_id = u.id
    WHERE c.is_deleted = 0
  `;
  const params = [];
  
  if (type) {
    query += ' AND c.type = ?';
    params.push(type);
  }
  
  query += ' ORDER BY c.name';
  
  const clubs = db.prepare(query).all(...params);
  res.json(clubs);
});

router.post('/', auth, staffOnly, (req, res) => {
  const { name, type, description, teacher_in_charge_id, schedule, max_members } = req.body;
  const uid = uuidv4();
  try {
    const result = db.prepare(`
      INSERT INTO clubs (uid, name, type, description, teacher_in_charge_id, schedule, max_members)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `).run(uid, name, type, description, teacher_in_charge_id, schedule, max_members);
    res.json({ id: result.lastInsertRowid, uid });
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/clubs/{id}:
 *   put:
 *     summary: Update club
 *     description: |
 *       Updates an existing club. Restricted to staff roles.
 *     tags:
 *       - Clubs
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               name: { type: string }
 *               type: { type: string }
 *               description: { type: string }
 *               teacher_in_charge_id: { type: integer }
 *               schedule: { type: string }
 *               max_members: { type: integer }
 *               status: { type: string }
 *     responses:
 *       200:
 *         description: Updated
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Staff only }
 *       404: { description: Club not found }
 *   delete:
 *     summary: Soft-delete club
 *     description: |
 *       Marks the club as deleted (`is_deleted = 1`). Restricted to
 *       staff roles.
 *     tags:
 *       - Clubs
 *     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: Staff only }
 */
router.put('/:id', auth, staffOnly, (req, res) => {
  const { name, type, description, teacher_in_charge_id, schedule, max_members, status } = req.body;
  try {
    db.prepare(`
      UPDATE clubs
      SET name = ?, type = ?, description = ?, teacher_in_charge_id = ?, schedule = ?, max_members = ?, status = ?, updated_at = CURRENT_TIMESTAMP, sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(name, type, description, teacher_in_charge_id || null, schedule, max_members || null, status || 'active', req.params.id);
    res.json({ success: true });
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

router.delete('/:id', auth, staffOnly, (req, res) => {
  try {
    db.prepare(`
      UPDATE clubs
      SET is_deleted = 1, updated_at = CURRENT_TIMESTAMP, sync_status = 'pending'
      WHERE id = ?
    `).run(req.params.id);
    res.json({ success: true });
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

// ============ MEMBERSHIPS ============

/**
 * @openapi
 * /api/clubs/memberships:
 *   get:
 *     summary: List memberships
 *     description: |
 *       Returns club memberships. Students see only their own
 *       memberships; staff may filter by `club_id` or `student_id`.
 *     tags:
 *       - Clubs
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: club_id
 *         schema: { type: integer }
 *       - in: query
 *         name: student_id
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Membership list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/memberships', auth, (req, res) => {
  const { club_id, student_id } = req.query;
  let query = `
    SELECT cm.*, c.name as club_name, c.type as club_type, u.first_name, u.last_name, u.email
    FROM club_memberships cm
    JOIN clubs c ON cm.club_id = c.id
    JOIN users u ON cm.student_id = u.id
    WHERE cm.is_deleted = 0 AND c.is_deleted = 0
  `;
  const params = [];

  if (club_id) {
    query += ' AND cm.club_id = ?';
    params.push(club_id);
  }
  
  if (req.user.role === 'student') {
    query += ' AND cm.student_id = ?';
    params.push(req.user.id);
  } else if (student_id) {
    query += ' AND cm.student_id = ?';
    params.push(student_id);
  }

  const memberships = db.prepare(query).all(...params);
  res.json(memberships);
});

/**
 * @openapi
 * /api/clubs/memberships/join:
 *   post:
 *     summary: Join a club
 *     description: |
 *       Enrolls the caller (or the supplied `student_id` for staff)
 *       as an active member of the given club. Refuses if the club
 *       is full, the club does not exist, or the caller is already
 *       an active member.
 *     tags:
 *       - Clubs
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - club_id
 *             properties:
 *               club_id: { type: integer }
 *               student_id:
 *                 type: integer
 *                 description: Staff-only override; defaults to caller's id for students
 *     responses:
 *       200:
 *         description: Joined
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400:
 *         description: Club full, already a member, or club not found
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 */
router.post('/memberships/join', auth, (req, res) => {
  const { club_id } = req.body;
  const student_id = req.user.role === 'student' ? req.user.id : req.body.student_id;
  const uid = uuidv4();

  const performJoin = db.transaction(() => {
    const club = db.prepare('SELECT max_members FROM clubs WHERE id = ?').get(club_id);
    if (!club) throw new Error('Club not found');
    
    if (club.max_members) {
      const current = db.prepare("SELECT COUNT(*) as count FROM club_memberships WHERE club_id = ? AND status = 'active'").get(club_id).count;
      if (current >= club.max_members) throw new Error('Club is full');
    }

    const existing = db.prepare('SELECT id, status FROM club_memberships WHERE club_id = ? AND student_id = ?').get(club_id, student_id);
    if (existing) {
      if (existing.status === 'active') throw new Error('Already a member');
      db.prepare("UPDATE club_memberships SET status = 'active', join_date = CURRENT_DATE, is_deleted = 0 WHERE id = ?").run(existing.id);
      return existing.id;
    }

    const result = db.prepare(`
      INSERT INTO club_memberships (uid, club_id, student_id) VALUES (?, ?, ?)
    `).run(uid, club_id, student_id);
    return result.lastInsertRowid;
  });

  try {
    const id = performJoin();
    res.json({ success: true, id });
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/clubs/memberships/leave:
 *   post:
 *     summary: Leave a club
 *     description: |
 *       Marks the caller's membership (or `student_id` for staff) as
 *       `inactive` for the given club.
 *     tags:
 *       - Clubs
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - club_id
 *             properties:
 *               club_id: { type: integer }
 *               student_id:
 *                 type: integer
 *                 description: Staff-only override; defaults to caller's id for students
 *     responses:
 *       200:
 *         description: Left
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 */
router.post('/memberships/leave', auth, (req, res) => {
  const { club_id } = req.body;
  const student_id = req.user.role === 'student' ? req.user.id : req.body.student_id;
  
  db.prepare("UPDATE club_memberships SET status = 'inactive' WHERE club_id = ? AND student_id = ?").run(club_id, student_id);
  res.json({ success: true });
});

// ============ ANNOUNCEMENTS ============

/**
 * @openapi
 * /api/clubs/announcements:
 *   get:
 *     summary: List club announcements
 *     description: |
 *       Returns announcements (most recent first, capped at 50).
 *       When `club_id` is omitted, students see only announcements
 *       for clubs they actively belong to.
 *     tags:
 *       - Clubs
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: club_id
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Announcement list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Post club announcement
 *     description: |
 *       Creates a new announcement for a club. Restricted to staff
 *       roles.
 *     tags:
 *       - Clubs
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - club_id
 *               - title
 *               - content
 *             properties:
 *               club_id: { type: integer }
 *               title: { type: string }
 *               content: { type: string }
 *     responses:
 *       200:
 *         description: Created announcement
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Staff only }
 */
router.get('/announcements', auth, (req, res) => {
  const { club_id } = req.query;
  let query = `
    SELECT ca.*, c.name as club_name, u.first_name || ' ' || u.last_name as author_name
    FROM club_announcements ca
    JOIN clubs c ON ca.club_id = c.id
    JOIN users u ON ca.created_by = u.id
    WHERE ca.is_deleted = 0
  `;
  const params = [];
  
  if (club_id) {
    query += ' AND ca.club_id = ?';
    params.push(club_id);
  } else if (req.user.role === 'student') {
    query += ' AND ca.club_id IN (SELECT club_id FROM club_memberships WHERE student_id = ? AND status = \'active\')';
    params.push(req.user.id);
  }
  
  query += ' ORDER BY ca.created_at DESC LIMIT 50';
  
  const announcements = db.prepare(query).all(...params);
  res.json(announcements);
});

router.post('/announcements', auth, staffOnly, (req, res) => {
  const { club_id, title, content } = req.body;
  const uid = uuidv4();
  try {
    const result = db.prepare(`
      INSERT INTO club_announcements (uid, club_id, title, content, created_by)
      VALUES (?, ?, ?, ?, ?)
    `).run(uid, club_id, title, content, req.user.id);
    res.json({ id: result.lastInsertRowid, uid });
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

// ============ ROLL CALL (ATTENDANCE) ============

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

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

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

// ============ CHATS & FILE UPLOAD ============

const multer = require('multer');
const fs = require('fs');

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: 5 * 1024 * 1024 } // 5MB limit
});

// Upload endpoint
/**
 * @openapi
 * /api/clubs/chats/upload:
 *   post:
 *     summary: Upload chat image
 *     description: |
 *       Uploads an image (max 5 MB) for use in a club chat message.
 *       Returns the relative URL to pass as `image_url` in
 *       `POST /api/clubs/{id}/chats`.
 *     tags:
 *       - Clubs
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         multipart/form-data:
 *           schema:
 *             type: object
 *             required:
 *               - image
 *             properties:
 *               image:
 *                 type: string
 *                 format: binary
 *     responses:
 *       200:
 *         description: Image uploaded
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 imageUrl: { type: string }
 *       400:
 *         description: No image provided
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 */
router.post('/chats/upload', auth, upload.single('image'), (req, res) => {
  if (!req.file) {
    return res.status(400).json({ error: 'No image file uploaded' });
  }
  const imageUrl = `/uploads/${req.file.filename}`;
  res.json({ imageUrl });
});

// Get chats for a club
/**
 * @openapi
 * /api/clubs/{id}/chats:
 *   get:
 *     summary: List club chats
 *     description: |
 *       Returns the last 100 chat messages for the club, ordered
 *       oldest first. Staff may read any club; students must be
 *       active members.
 *     tags:
 *       - Clubs
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Chat messages
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       403:
 *         description: Not enrolled in this extracurricular activity
 *         content:
 *           application/json:
 *             schema: { type: object }
 *   post:
 *     summary: Post a chat message
 *     description: |
 *       Posts a new chat message in the club. Staff may post in any
 *       club; students must be active members. Returns `201`.
 *     tags:
 *       - Clubs
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               message: { type: string }
 *               image_url: { type: string }
 *     responses:
 *       201:
 *         description: Created message
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403:
 *         description: Not enrolled in this extracurricular activity
 *         content:
 *           application/json:
 *             schema: { type: object }
 */
router.get('/:id/chats', auth, (req, res) => {
  const clubId = parseInt(req.params.id);
  
  // 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 = db.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' });
    }
  }

  try {
    const chats = db.prepare(`
      SELECT cc.*, u.first_name || ' ' || u.last_name as sender_name, u.role as sender_role, u.profile_image as sender_image
      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 ASC
      LIMIT 100
    `).all(clubId);
    res.json(chats);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Post a chat message
router.post('/:id/chats', auth, (req, res) => {
  const clubId = parseInt(req.params.id);
  const { message, image_url } = req.body;
  const uid = uuidv4();

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

  try {
    const result = db.prepare(`
      INSERT INTO club_chats (uid, club_id, sender_id, message, image_url)
      VALUES (?, ?, ?, ?, ?)
    `).run(uid, clubId, req.user.id, message || null, image_url || null);
    
    const newChat = db.prepare(`
      SELECT cc.*, u.first_name || ' ' || u.last_name as sender_name, u.role as sender_role, u.profile_image as sender_image
      FROM club_chats cc
      JOIN users u ON cc.sender_id = u.id
      WHERE cc.id = ?
    `).get(result.lastInsertRowid);
    
    res.status(201).json(newChat);
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

module.exports = router;
