/**
 * Users Controller
 * CRUD operations with role-based filtering
 */

const express = require('express');
const router = express.Router();
const bcrypt = require('bcryptjs');
const { v4: uuidv4 } = require('uuid');
const AuditService = require('../services/AuditService');
const IdGenerator = require('../services/IdGenerator');
const multer = require('multer');
const path = require('path');
const fs = require('fs');
const { VALID_GENDERS, normalizeGender } = require('../utils/gender');

const storage = multer.diskStorage({
  destination: (req, file, cb) => {
    const uploadDir = path.join(__dirname, '../../uploads');
    if (!fs.existsSync(uploadDir)) {
      fs.mkdirSync(uploadDir, { recursive: true });
    }
    cb(null, uploadDir);
  },
  filename: (req, file, cb) => {
    cb(null, `avatar-${req.params.id}-${Date.now()}${path.extname(file.originalname)}`);
  }
});
const upload = multer({ storage });


// Database path (shared with index.js)
const dbPath = process.env.DB_PATH || require('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');
  }
  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' });
  }
};

// Admin-or-principal middleware. `requireAdmin` from the shared helper
// matches the SCREENS.md matrix for routes that principal gets View access
// to (e.g. /users, /departments).
const { requireAdmin: adminOnly } = require('../middleware/requireRole');

// ============ USERS SPECIAL ENDPOINTS ============

/**
 * @openapi
 * /api/users/stats/overview:
 *   get:
 *     summary: User statistics by role
 *     description: Returns total user count and per-role active/inactive counts. Authenticated.
 *     tags:
 *       - Users
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Stats
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 total: { type: integer }
 *                 by_role:
 *                   type: object
 *                   additionalProperties:
 *                     type: object
 *                     properties:
 *                       total: { type: integer }
 *                       active: { type: integer }
 *                       inactive: { type: integer }
 */
// Get user statistics
router.get('/stats/overview', auth, (req, res) => {
  try {
    const stats = getDb().prepare(`
      SELECT 
        role,
        COUNT(*) as total,
        SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) as active,
        SUM(CASE WHEN is_active = 0 THEN 1 ELSE 0 END) as inactive
      FROM users
      WHERE is_deleted = 0
      GROUP BY role
    `).all();
    
    const total = getDb().prepare('SELECT COUNT(*) as count FROM users WHERE is_deleted = 0').get().count;
    
    res.json({
      total,
      by_role: stats.reduce((acc, s) => {
        acc[s.role] = { total: s.total, active: s.active, inactive: s.inactive };
        return acc;
      }, {})
    });
  } catch (error) {
    console.error('STATS OVERVIEW ERROR:', error);
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/users/teacher-students:
 *   get:
 *     summary: List students visible to the logged-in teacher
 *     description: |
 *       Returns the distinct students enrolled in classes the calling teacher
 *       is associated with, plus their class, score, progress, attendance and
 *       assignment counts. Caller must be `teacher`, `school_admin`,
 *       `systems_admin`, or `principal`.
 *     tags:
 *       - Users
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200: { description: List of students with class/score fields }
 *       403: { description: Teacher or admin role required }
 */
// Get students for logged-in teacher
router.get('/teacher-students', auth, (req, res) => {
  try {
    if (req.user.role !== 'teacher' && req.user.role !== 'school_admin' && req.user.role !== 'systems_admin') {
      return res.status(403).json({ error: 'Access denied' });
    }

    let query = `
      SELECT DISTINCT 
        u.id, u.uid, u.email, u.first_name, u.last_name, u.phone, u.gender, u.profile_image, u.is_active,
        c.name as class_name, c.section as class_section,
        COALESCE(ROUND((SELECT AVG(percentage) FROM exam_attempts WHERE student_id = u.id)), 75) as score,
        COALESCE(ROUND(
          (SELECT COUNT(*) FROM submissions sub WHERE sub.student_id = u.id AND sub.status = 'graded' AND sub.is_deleted = 0) * 100.0 / 
          NULLIF((SELECT COUNT(*) FROM assignments a JOIN subjects_new s2 ON a.course_id = s2.id JOIN enrollments e2 ON s2.class_id = e2.class_id WHERE e2.student_id = u.id AND a.is_deleted = 0 AND a.is_published = 1), 0)
        ), 0) as progress,
        COALESCE(ROUND(
          (SELECT SUM(CASE WHEN status IN ('present', 'late') THEN 1 ELSE 0 END) * 100.0 / COUNT(*) 
           FROM attendance 
           WHERE student_id = u.id)
        ), 100) as attendance,
        COALESCE(
          (SELECT COUNT(*) FROM submissions sub WHERE sub.student_id = u.id AND sub.status = 'graded' AND sub.is_deleted = 0),
          0
        ) as assignmentsCompleted,
        COALESCE(
          (SELECT COUNT(*) FROM assignments a JOIN subjects_new s2 ON a.course_id = s2.id JOIN enrollments e2 ON s2.class_id = e2.class_id WHERE e2.student_id = u.id AND a.is_deleted = 0 AND a.is_published = 1),
          0
        ) as totalAssignments
      FROM users u
      JOIN enrollments e ON u.id = e.student_id
      JOIN classes c ON e.class_id = c.id
      LEFT JOIN subjects_new s ON c.id = s.class_id
      WHERE u.role = 'student' AND u.is_deleted = 0 AND e.is_deleted = 0
    `;
    const params = [];

    if (req.user.role === 'teacher') {
      query += ' AND (c.class_teacher_id = ? OR s.teacher_id = ?)';
      params.push(req.user.id, req.user.id);
    }

    query += ' ORDER BY u.last_name, u.first_name';

    const students = getDb().prepare(query).all(...params);

    // Map to frontend format
    const formattedStudents = students.map(s => ({
      ...s,
      course: s.class_name + (s.class_section ? ` ${s.class_section}` : ''),
      score: Math.round(s.score || 75),
      status: s.score >= 85 ? 'excellent' : s.score >= 70 ? 'good' : 'attention'
    }));

    res.json(formattedStudents);
  } catch (error) {
    console.error('FETCH TEACHER STUDENTS ERROR:', error);
    res.status(500).json({ error: error.message });
  }
});

// Get children for logged-in parent user
/**
 * @openapi
 * /api/users/children:
 *   get:
 *     summary: List linked students for the logged-in parent
 *     description: Returns the children (students) linked to the calling parent user.
 *     tags:
 *       - Users
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200: { description: List of children }
 */
router.get('/children', auth, (req, res) => {
  try {
    console.log(`Fetching children for parent ID: ${req.user.id}`);
    if (req.user.role !== 'parent') {
      return res.status(403).json({ error: 'Only parent users can access this endpoint' });
    }

    const children = getDb().prepare(`
      SELECT u.id, u.uid, u.email, u.first_name, u.last_name, u.phone, u.gender, u.date_of_birth, u.profile_image, u.is_active, ps.relationship
      FROM users u
      JOIN parent_students ps ON u.id = ps.student_id
      WHERE ps.parent_id = ? AND u.is_deleted = 0 AND ps.is_deleted = 0
      ORDER BY u.last_name, u.first_name
    `).all(req.user.id);

    res.json(children);
  } catch (error) {
    console.error('FETCH CHILDREN ERROR:', error);
    res.status(500).json({ error: error.message });
  }
});

/**
 * GET /api/users/me/children
 *
 * Phase 1 PR 1: parent-as-staff. Returns the children linked to the
 * CALLING user, regardless of role. The existing /api/users/children
 * endpoint is role-restricted (parent only); this one is for the
 * "view as parent" toggle where the user might be a teacher whose
 * child is enrolled at the same school.
 */
router.get('/me/children', auth, (req, res) => {
  try {
    const children = getDb().prepare(`
      SELECT u.id, u.uid, u.email, u.first_name, u.last_name, u.phone, u.gender, u.date_of_birth, u.profile_image, u.is_active, ps.relationship
      FROM users u
      JOIN parent_students ps ON u.id = ps.student_id
      WHERE ps.parent_id = ? AND u.is_deleted = 0 AND ps.is_deleted = 0
      ORDER BY u.last_name, u.first_name
    `).all(req.user.id);
    res.json({ children });
  } catch (error) {
    console.error('FETCH ME/CHILDREN ERROR:', error);
    res.status(500).json({ error: error.message });
  }
});

/**
 * POST /api/users/link-child
 * Body: { parent_id, student_id, relationship?, is_primary? }
 * Creates a parent_students row. If one already exists (incl. soft-deleted)
 * it is restored with the new attributes. Refuses if either id does not
 * exist, is soft-deleted, or is the wrong role.
 */
/**
 * @openapi
 * /api/users/link-child:
 *   post:
 *     summary: Link a parent user to a student
 *     description: Creates a parent-child link. Admin/principal only.
 *     tags:
 *       - Users
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required: [parent_id, student_id]
 *             properties:
 *               parent_id: { type: integer }
 *               student_id: { type: integer }
 *     responses:
 *       200: { description: Link created }
 *       403: { description: Admin or Principal required }
 */
router.post('/link-child', auth, adminOnly, async (req, res) => {
  try {
    const { parent_id, student_id, relationship, is_primary } = req.body;
    if (!parent_id || !student_id) {
      return res.status(400).json({ error: 'parent_id and student_id are required' });
    }

    const database = getDb();
    const parent = database.prepare(
      "SELECT id, role, first_name, last_name, email FROM users WHERE id = ? AND is_deleted = 0"
    ).get(parent_id);
    if (!parent) return res.status(404).json({ error: 'Parent user not found' });
    if (parent.role !== 'parent') {
      return res.status(400).json({ error: `User ${parent.email} is not a parent (role: ${parent.role})` });
    }

    const student = database.prepare(
      "SELECT id, role, first_name, last_name, email FROM users WHERE id = ? AND is_deleted = 0"
    ).get(student_id);
    if (!student) return res.status(404).json({ error: 'Student user not found' });
    if (student.role !== 'student') {
      return res.status(400).json({ error: `User ${student.email} is not a student (role: ${student.role})` });
    }

    const validRelationships = ['father', 'mother', 'guardian', 'other'];
    const rel = validRelationships.includes(relationship) ? relationship : 'guardian';

    // If a row already exists (even soft-deleted) restore it; otherwise insert.
    const existing = database.prepare(
      'SELECT id FROM parent_students WHERE parent_id = ? AND student_id = ?'
    ).get(parent_id, student_id);

    if (existing) {
      database.prepare(`
        UPDATE parent_students SET
          relationship = ?,
          is_primary = ?,
          is_deleted = 0,
          updated_at = datetime(\'now\', \'localtime\'),
          sync_status = 'pending'
        WHERE id = ?
      `).run(rel, is_primary ? 1 : 0, existing.id);
    } else {
      database.prepare(`
        INSERT INTO parent_students (uid, parent_id, student_id, relationship, is_primary, sync_status)
        VALUES (?, ?, ?, ?, ?, 'pending')
      `).run(uuidv4(), parent_id, student_id, rel, is_primary ? 1 : 0);
    }

    await AuditService.log(req, 'LINK_PARENT_STUDENT', {
      entityType: 'parent_students',
      entityId: `${parent_id}:${student_id}`,
      newData: { parent_id, student_id, relationship: rel, is_primary: !!is_primary },
    });

    const link = database.prepare(`
      SELECT ps.*, u1.first_name || ' ' || u1.last_name as parent_name, u1.email as parent_email,
             u2.first_name || ' ' || u2.last_name as student_name, u2.email as student_email
      FROM parent_students ps
      JOIN users u1 ON ps.parent_id = u1.id
      JOIN users u2 ON ps.student_id = u2.id
      WHERE ps.parent_id = ? AND ps.student_id = ?
    `).get(parent_id, student_id);
    res.status(201).json(link);
  } catch (error) {
    console.error('LINK CHILD ERROR:', error);
    res.status(500).json({ error: error.message });
  }
});

/**
 * DELETE /api/users/link-child/:parent_id/:student_id
 * Soft delete an existing parent_students link.
 */
/**
 * @openapi
 * /api/users/link-child/{parent_id}/{student_id}:
 *   delete:
 *     summary: Unlink a parent from a student
 *     description: Removes a parent-child link. Admin/principal only.
 *     tags:
 *       - Users
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: parent_id
 *         required: true
 *         schema: { type: integer }
 *       - in: path
 *         name: student_id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200: { description: Link removed }
 *       403: { description: Admin or Principal required }
 */
router.delete('/link-child/:parent_id/:student_id', auth, adminOnly, async (req, res) => {
  try {
    const { parent_id, student_id } = req.params;
    const result = getDb().prepare(`
      UPDATE parent_students
      SET is_deleted = 1, updated_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
      WHERE parent_id = ? AND student_id = ? AND is_deleted = 0
    `).run(parent_id, student_id);
    if (result.changes === 0) return res.status(404).json({ error: 'Link not found' });

    await AuditService.log(req, 'UNLINK_PARENT_STUDENT', {
      entityType: 'parent_students',
      entityId: `${parent_id}:${student_id}`,
    });
    res.json({ success: true, message: 'Link removed' });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ USERS CRUD ============

// List users with optional role filtering
/**
 * @openapi
 * /api/users:
 *   get:
 *     summary: List users
 *     description: Paginated user list. Supports filtering by role, search, class.
 *     tags:
 *       - Users
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: role
 *         schema: { type: string }
 *       - in: query
 *         name: search
 *         schema: { type: string }
 *       - in: query
 *         name: class_id
 *         schema: { type: integer }
 *       - in: query
 *         name: limit
 *         schema: { type: integer, default: 100 }
 *       - in: query
 *         name: offset
 *         schema: { type: integer, default: 0 }
 *     responses:
 *       200: { description: Paginated user list }
 */
router.get('/', auth, adminOnly, (req, res) => {
  try {
    const { role, search, status, department_id, page = 1, limit = 50 } = req.query;
    const offset = (page - 1) * limit;
    
    let query = `
      SELECT id, uid, id_number, email, role, first_name, last_name, phone, address, 
             gender, date_of_birth, profile_image, emergency_contact, emergency_phone,
             department_id, is_active, created_at, last_login
      FROM users
      WHERE is_deleted = 0
    `;
    const params = [];
    
    if (role) {
      if (role === 'admin') {
        query += " AND role IN ('school_admin', 'systems_admin', 'principal')";
      } else {
        query += ' AND role = ?';
        params.push(role);
      }
    }
    
    if (department_id) {
      query += ' AND department_id = ?';
      params.push(department_id);
    }
    
    if (search) {
      query += ' AND (first_name LIKE ? OR last_name LIKE ? OR email LIKE ?)';
      const searchTerm = `%${search}%`;
      params.push(searchTerm, searchTerm, searchTerm);
    }
    
    if (status) {
      query += ' AND is_active = ?';
      params.push(status === 'active' ? 1 : 0);
    }
    
    query += ' ORDER BY last_name, first_name LIMIT ? OFFSET ?';
    params.push(parseInt(limit), parseInt(offset));
    
    const users = getDb().prepare(query).all(...params);
    
    // Get total count
    let countQuery = 'SELECT COUNT(*) as count FROM users WHERE is_deleted = 0';
    const countParams = [];
    if (role) {
      if (role === 'admin') {
        countQuery += " AND role IN ('school_admin', 'systems_admin', 'principal')";
      } else {
        countQuery += ' AND role = ?';
        countParams.push(role);
      }
    }
    if (department_id) {
      countQuery += ' AND department_id = ?';
      countParams.push(department_id);
    }
    if (search) {
      countQuery += ' AND (first_name LIKE ? OR last_name LIKE ? OR email LIKE ?)';
      const searchTerm = `%${search}%`;
      countParams.push(searchTerm, searchTerm, searchTerm);
    }
    if (status) {
      countQuery += ' AND is_active = ?';
      countParams.push(status === 'active' ? 1 : 0);
    }

    const countResult = getDb().prepare(countQuery).get(...countParams);
    const total = countResult ? countResult.count : 0;
    
    res.json({
      users,
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total,
        pages: Math.ceil(total / limit)
      }
    });
  } catch (error) {
    console.error('LIST USERS ERROR:', error);
    res.status(500).json({ error: error.message });
  }
});

// Get single user by ID
/**
 * @openapi
 * /api/users/{id}:
 *   get:
 *     summary: Get user by numeric id
 *     tags:
 *       - Users
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200: { description: User object }
 *       404: { description: Not found }
 */
router.get('/:id', auth, (req, res) => {
  try {
    const user = getDb().prepare(`
      SELECT id, uid, id_number, email, role, first_name, last_name, phone, address,
             gender, date_of_birth, profile_image, emergency_contact, emergency_phone,
             department_id, is_active, created_at, last_login
      FROM users
      WHERE id = ? AND is_deleted = 0
    `).get(req.params.id);
    
    if (!user) {
      return res.status(404).json({ error: 'User not found' });
    }
    
    res.json(user);
  } catch (error) {
    console.error('GET USER ERROR:', error);
    res.status(500).json({ error: error.message });
  }
});

// Get user by UID
/**
 * @openapi
 * /api/users/uid/{uid}:
 *   get:
 *     summary: Get user by uid (UUID)
 *     description: Alternative lookup by `users.uid`.
 *     tags:
 *       - Users
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: uid
 *         required: true
 *         schema: { type: string, format: uuid }
 *     responses:
 *       200: { description: User object }
 *       404: { description: Not found }
 */
router.get('/uid/:uid', auth, (req, res) => {
  try {
    const user = getDb().prepare(`
      SELECT id, uid, id_number, email, role, first_name, last_name, phone, address,
             gender, date_of_birth, profile_image, emergency_contact, emergency_phone,
             department_id, is_active, created_at, last_login
      FROM users
      WHERE uid = ? AND is_deleted = 0
    `).get(req.params.uid);
    
    if (!user) {
      return res.status(404).json({ error: 'User not found' });
    }
    
    res.json(user);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Create new user
/**
 * @openapi
 * /api/users:
 *   post:
 *     summary: Create a new user
 *     description: Admin/principal only. Hashes the password and assigns a UUID `uid`.
 *     tags:
 *       - Users
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required: [email, password, role]
 *             properties:
 *               email: { type: string, format: email }
 *               password: { type: string, format: password }
 *               role: { type: string }
 *               first_name: { type: string }
 *               last_name: { type: string }
 *               phone: { type: string }
 *     responses:
 *       200: { description: Created user (without password) }
 *       403: { description: Admin or Principal required }
 */
router.post('/', auth, adminOnly, async (req, res) => {
  try {
    const { email, password, role, first_name, last_name, phone, address, gender, date_of_birth, emergency_contact, emergency_phone, department_id } = req.body;
    const normalizedGender = normalizeGender(gender);
    
    // Validate required fields
    if (!email || !password || !role || !first_name || !last_name) {
      return res.status(400).json({ error: 'Missing required fields: email, password, role, first_name, last_name' });
    }

    if (normalizedGender && !VALID_GENDERS.includes(normalizedGender)) {
      return res.status(400).json({ error: `Invalid gender. Must be one of: ${VALID_GENDERS.join(', ')}` });
    }
    
    // Validate role
    const validRoles = ['systems_admin', 'school_admin', 'teacher', 'student', 'parent', 'accountant', 'librarian', 'nurse', 'principal', 'hr', 'bursar', 'clubs_head', 'dining_staff'];
    if (!validRoles.includes(role)) {
      return res.status(400).json({ error: `Invalid role. Must be one of: ${validRoles.join(', ')}` });
    }
    
    const hashedPassword = bcrypt.hashSync(password, 10);
    const uid = uuidv4();
    const id_number = await IdGenerator.generateId(role);
    
    const result = getDb().prepare(`
      INSERT INTO users (uid, id_number, email, password, role, first_name, last_name, phone, address, gender, date_of_birth, emergency_contact, emergency_phone, department_id)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(uid, id_number, email, hashedPassword, role, first_name, last_name, phone, address, normalizedGender, date_of_birth, emergency_contact, emergency_phone, department_id);
    
    await AuditService.log(req, 'CREATE_USER', {
      entityType: 'user',
      entityId: uid,
      newData: { email, role, first_name, last_name, id_number, department_id, gender: normalizedGender }
    });

    const newUser = getDb().prepare('SELECT id, uid, id_number, email, role, first_name, last_name, phone, address, gender, date_of_birth, emergency_contact, emergency_phone, department_id, is_active, created_at FROM users WHERE id = ?').get(result.lastInsertRowid);
    
    res.status(201).json(newUser);
  } catch (error) {
    if (error.message.includes('UNIQUE constraint failed')) {
      return res.status(400).json({ error: 'Email or ID Number already exists' });
    }
    res.status(500).json({ error: error.message });
  }
});

// Update user
/**
 * @openapi
 * /api/users/{id}:
 *   put:
 *     summary: Update user profile
 *     description: Self-update or admin update. Sensitive fields (role, password) are gated.
 *     tags:
 *       - Users
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               first_name: { type: string }
 *               last_name: { type: string }
 *               phone: { type: string }
 *               email: { type: string, format: email }
 *     responses:
 *       200: { description: Updated user }
 *       403: { description: Forbidden }
 */
router.put('/:id', auth, async (req, res) => {
  try {
    const { first_name, last_name, phone, address, gender, date_of_birth, emergency_contact, emergency_phone, profile_image, role, department_id } = req.body;
    const normalizedGender = normalizeGender(gender);
    
    const isPrivileged = ['systems_admin', 'school_admin', 'principal'].includes(req.user.role);

    // Only allow self or admin to update
    if (!isPrivileged && req.user.id !== parseInt(req.params.id)) {
      return res.status(403).json({ error: 'You can only update your own profile' });
    }

    if (normalizedGender && !VALID_GENDERS.includes(normalizedGender)) {
      return res.status(400).json({ error: `Invalid gender. Must be one of: ${VALID_GENDERS.join(', ')}` });
    }

    // Get old data for audit log
    const oldData = getDb().prepare('SELECT * FROM users WHERE id = ?').get(req.params.id);
    if (!oldData) return res.status(404).json({ error: 'User not found' });
    
    // Only privileged users can update role or department
    const finalRole = isPrivileged && role ? role : oldData.role;
    const finalDept = isPrivileged && department_id ? department_id : oldData.department_id;

    getDb().prepare(`
      UPDATE users SET
        first_name = COALESCE(?, first_name),
        last_name = COALESCE(?, last_name),
        phone = COALESCE(?, phone),
        address = COALESCE(?, address),
        gender = COALESCE(?, gender),
        date_of_birth = COALESCE(?, date_of_birth),
        emergency_contact = COALESCE(?, emergency_contact),
        emergency_phone = COALESCE(?, emergency_phone),
        profile_image = COALESCE(?, profile_image),
        role = ?,
        department_id = ?,
        updated_at = datetime(\'now\', \'localtime\'),
        sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(first_name, last_name, phone, address, normalizedGender, date_of_birth, emergency_contact, emergency_phone, profile_image, finalRole, finalDept, req.params.id);
    
    const updated = getDb().prepare('SELECT id, uid, email, role, first_name, last_name, phone, address, gender, date_of_birth, emergency_contact, emergency_phone, department_id, profile_image, is_active FROM users WHERE id = ?').get(req.params.id);
    
    await AuditService.log(req, 'UPDATE_USER', {
      entityType: 'user',
      entityId: oldData.uid,
      oldData: { email: oldData.email, role: oldData.role, first_name: oldData.first_name, last_name: oldData.last_name, gender: oldData.gender },
      newData: { email: updated.email, role: updated.role, first_name: updated.first_name, last_name: updated.last_name, gender: updated.gender }
    });

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

// Change password
/**
 * @openapi
 * /api/users/{id}/password:
 *   put:
 *     summary: Change user password
 *     description: Caller must supply the current password and a new password (min 8 chars).
 *     tags:
 *       - Users
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required: [current_password, new_password]
 *             properties:
 *               current_password: { type: string, format: password }
 *               new_password: { type: string, format: password }
 *     responses:
 *       200: { description: Password updated }
 *       400: { description: Missing or invalid input }
 *       401: { description: Current password mismatch }
 */
router.put('/:id/password', auth, async (req, res) => {
  try {
    const { current_password, new_password } = req.body;
    
    // Only self or admin can change password
    if (req.user.role !== 'systems_admin' && req.user.role !== 'school_admin' && req.user.id !== parseInt(req.params.id)) {
      return res.status(403).json({ error: 'You can only change your own password' });
    }
    
    const user = getDb().prepare('SELECT * FROM users WHERE id = ? AND is_deleted = 0').get(req.params.id);
    
    if (!user) {
      return res.status(404).json({ error: 'User not found' });
    }
    
    // Verify current password (admin bypasses this)
    if (req.user.role !== 'systems_admin' && req.user.role !== 'school_admin') {
      if (!current_password || !bcrypt.compareSync(current_password, user.password)) {
        return res.status(400).json({ error: 'Current password is incorrect' });
      }
    }
    
    if (!new_password || new_password.length < 6) {
      return res.status(400).json({ error: 'New password must be at least 6 characters' });
    }
    
    const hashedPassword = bcrypt.hashSync(new_password, 10);
    
    getDb().prepare(`
      UPDATE users SET password = ?, updated_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
      WHERE id = ?
    `).run(hashedPassword, req.params.id);
    
    await AuditService.log(req, 'CHANGE_PASSWORD', {
      entityType: 'user',
      entityId: user.uid,
      userEmail: user.email
    });

    res.json({ success: true, message: 'Password updated successfully' });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Deactivate/activate user
/**
 * @openapi
 * /api/users/{id}/status:
 *   patch:
 *     summary: Activate or deactivate a user
 *     description: Toggles `users.is_active`. Admin/principal only.
 *     tags:
 *       - Users
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required: [is_active]
 *             properties:
 *               is_active: { type: boolean }
 *     responses:
 *       200: { description: Updated user }
 *       403: { description: Admin or Principal required }
 */
router.patch('/:id/status', auth, adminOnly, async (req, res) => {
  try {
    const { is_active } = req.body;
    const user = getDb().prepare('SELECT * FROM users WHERE id = ?').get(req.params.id);

    getDb().prepare(`
      UPDATE users SET is_active = ?, updated_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(is_active ? 1 : 0, req.params.id);
    
    await AuditService.log(req, is_active ? 'ACTIVATE_USER' : 'DEACTIVATE_USER', {
      entityType: 'user',
      entityId: user.uid,
      userEmail: user.email
    });

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

// Delete user (soft delete)
/**
 * @openapi
 * /api/users/{id}:
 *   delete:
 *     summary: Soft-delete a user
 *     description: Sets `is_deleted = 1`. Admin/principal only.
 *     tags:
 *       - Users
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200: { description: User soft-deleted }
 *       403: { description: Admin or Principal required }
 */
router.delete('/:id', auth, adminOnly, async (req, res) => {
  try {
    const user = getDb().prepare('SELECT * FROM users WHERE id = ?').get(req.params.id);

    getDb().prepare(`
      UPDATE users SET is_deleted = 1, updated_at = datetime(\'now\', \'localtime\'), sync_status = 'pending'
      WHERE id = ?
    `).run(req.params.id);
    
    await AuditService.log(req, 'DELETE_USER', {
      entityType: 'user',
      entityId: user.uid,
      userEmail: user.email,
      oldData: { email: user.email, role: user.role, first_name: user.first_name, last_name: user.last_name }
    });

    res.json({ success: true, message: 'User deleted successfully' });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Reset user password
/**
 * @openapi
 * /api/users/{id}/reset-password:
 *   patch:
 *     summary: Reset user password (admin issues temp)
 *     description: |
 *       Generates a temporary password, persists the bcrypt hash, and returns
 *       the temp password in the response (the controller does not send email here).
 *       Admin/principal only.
 *     tags:
 *       - Users
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Temp password issued
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 temp_password: { type: string }
 *       403: { description: Admin or Principal required }
 */
router.patch('/:id/reset-password', auth, adminOnly, async (req, res) => {
  try {
    const { password } = req.body;
    if (!password || password.length < 6) {
      return res.status(400).json({ error: 'Password must be at least 6 characters long' });
    }

    const user = getDb().prepare('SELECT * FROM users WHERE id = ?').get(req.params.id);
    if (!user) return res.status(404).json({ error: 'User not found' });

    const hashedPassword = bcrypt.hashSync(password, 10);
    getDb().prepare('UPDATE users SET password = ?, updated_at = datetime(\'now\', \'localtime\') WHERE id = ?').run(hashedPassword, req.params.id);

    await AuditService.log(req, 'RESET_PASSWORD', {
      entityType: 'user',
      entityId: user.uid,
      userEmail: user.email
    });

    res.json({ success: true, message: 'Password reset successfully' });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ BULK OPERATIONS ============

// Bulk import users
/**
 * @openapi
 * /api/users/bulk:
 *   post:
 *     summary: Bulk import users from CSV
 *     description: |
 *       Accepts a CSV body (Content-Type: text/csv). The server parses rows,
 *       hashes passwords, and inserts users in a transaction. Up to 50MB
 *       (matches `express.json({ limit: '50mb' })`).
 *     tags:
 *       - Users
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         text/csv:
 *           schema:
 *             type: string
 *             description: CSV with header row matching the existing sample `sample_phone_calls.csv`-style layout
 *     responses:
 *       200:
 *         description: Import summary (per-row success/error)
 *       403: { description: Admin or Principal required }
 */
router.post('/bulk', auth, adminOnly, async (req, res) => {
  try {
    const { users } = req.body;
    
    if (!Array.isArray(users) || users.length === 0) {
      return res.status(400).json({ error: 'Users array required' });
    }
    
    const insert = getDb().prepare(`
      INSERT INTO users (uid, id_number, email, password, role, first_name, last_name, phone, address, gender, date_of_birth)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);
    
    const results = [];
    const db = getDb();
    
    // Process one by one to ensure unique sequential IDs are generated correctly
    for (const user of users) {
      try {
        const hashedPassword = bcrypt.hashSync(user.password || 'changeme123', 10);
        const uid = uuidv4();
        const id_number = await IdGenerator.generateId(user.role || 'student');
        const normalizedGender = normalizeGender(user.gender);

        if (normalizedGender && !VALID_GENDERS.includes(normalizedGender)) {
          throw new Error(`Invalid gender. Must be one of: ${VALID_GENDERS.join(', ')}`);
        }
        
        insert.run(
          uid, 
          id_number, 
          user.email, 
          hashedPassword, 
          user.role || 'student', 
          user.first_name, 
          user.last_name, 
          user.phone, 
          user.address, 
          normalizedGender,
          user.date_of_birth
        );
        
        results.push({ email: user.email, id_number, success: true });
      } catch (e) {
        results.push({ email: user.email, error: e.message, success: false });
      }
    }
    
    res.status(201).json({
      created: results.filter(r => r.success).length,
      failed: results.filter(r => !r.success).length,
      results
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Upload avatar endpoint
/**
 * @openapi
 * /api/users/{id}/avatar:
 *   post:
 *     summary: Upload user avatar image
 *     description: |
 *       Accepts a single image under field `avatar`. Stored under
 *       `uploads/avatar-{id}-{timestamp}.{ext}` and the public URL is persisted
 *       to `users.profile_image`.
 *     tags:
 *       - Users
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         multipart/form-data:
 *           schema:
 *             type: object
 *             properties:
 *               avatar:
 *                 type: string
 *                 format: binary
 *     responses:
 *       200:
 *         description: Avatar URL
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 url: { type: string }
 *       400: { description: No file uploaded }
 */
router.post('/:id/avatar', auth, upload.single('avatar'), async (req, res) => {
  try {
    if (!req.file) {
      return res.status(400).json({ error: 'No avatar file uploaded' });
    }

    const isPrivileged = ['systems_admin', 'school_admin', 'principal'].includes(req.user.role);
    if (!isPrivileged && req.user.id !== parseInt(req.params.id)) {
      return res.status(403).json({ error: 'You can only update your own avatar' });
    }

    const avatarUrl = `/uploads/${req.file.filename}`;
    
    getDb().prepare(`
      UPDATE users 
      SET profile_image = ?, updated_at = datetime('now', 'localtime'), sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(avatarUrl, req.params.id);

    const updated = getDb().prepare('SELECT id, uid, email, role, first_name, last_name, phone, address, profile_image FROM users WHERE id = ?').get(req.params.id);

    res.json({ success: true, avatarUrl, user: updated });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

module.exports = router;
