'use strict';

const express = require('express');
const path = require('path');
const Database = require('better-sqlite3');
const { v4: uuidv4 } = require('uuid');
const AuditService = require('../services/AuditService');
const OffboardingService = require('../services/OffboardingService');

const dbPath = process.env.DB_PATH || path.join(__dirname, '..', '..', 'data', 'school.db');
let db;
const getDb = () => {
  if (!db) {
    db = new Database(dbPath);
    db.pragma('foreign_keys = ON');
  }
  return db;
};

const router = express.Router();

// Auth and Role Gating Middleware
const auth = (req, res, next) => {
  const token = req.headers.authorization?.split(' ')[1];
  if (!token) return res.status(401).json({ error: 'No token provided' });
  try {
    const jwt = require('jsonwebtoken');
    const { jwtSecret: JWT_SECRET } = require('../config');
    req.user = jwt.verify(token, JWT_SECRET);
    next();
  } catch (err) {
    res.status(401).json({ error: 'Invalid token' });
  }
};

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

const requireStrictAdmin = (req, res, next) => {
  const allowed = ['school_admin', 'systems_admin'];
  if (!allowed.includes(req.user.role)) {
    return res.status(403).json({ error: 'Forbidden: Administrator access required' });
  }
  next();
};

const requireViewAccess = (req, res, next) => {
  const allowed = ['school_admin', 'systems_admin', 'principal', 'hr'];
  if (!allowed.includes(req.user.role)) {
    return res.status(403).json({ error: 'Forbidden: Insufficient privileges to view records' });
  }
  next();
};

/**
 * POST /api/offboarding/students/:userId
 * Initiate student offboarding.
 */
router.post('/students/:userId', auth, requireAdminOrPrincipal, (req, res) => {
  const userId = parseInt(req.params.userId, 10);
  const initiatedBy = req.user.id;
  const payload = req.body; // reason, effectiveDate, destination, reasonNotes, override: { reason }

  try {
    const result = OffboardingService.offboardStudent(userId, payload, initiatedBy);
    res.status(201).json(result);
  } catch (error) {
    console.error('[offboarding] student failed:', error.message);
    res.status(400).json({ error: error.message });
  }
});

/**
 * POST /api/offboarding/staff/:userId
 * Initiate staff offboarding.
 */
router.post('/staff/:userId', auth, requireAdminOrPrincipal, (req, res) => {
  const userId = parseInt(req.params.userId, 10);
  const initiatedBy = req.user.id;
  const payload = req.body; // reason, effectiveDate, reasonNotes, override: { reason }

  try {
    const result = OffboardingService.offboardStaff(userId, payload, initiatedBy);
    res.status(201).json(result);
  } catch (error) {
    console.error('[offboarding] staff failed:', error.message);
    res.status(400).json({ error: error.message });
  }
});

/**
 * GET /api/offboarding/preflight/:userId
 * Fetch preflight checks for student or staff member.
 */
router.get('/preflight/:userId', auth, requireViewAccess, (req, res) => {
  const userId = parseInt(req.params.userId, 10);
  
  try {
    const database = getDb();
    const userRow = database.prepare('SELECT * FROM users WHERE id = ? AND is_deleted = 0').get(userId);
    if (!userRow) {
      return res.status(404).json({ error: 'User not found' });
    }
    
    if (userRow.role === 'student') {
      // 1. Enrollments
      const enrollments = database.prepare(`
        SELECT e.*, c.name as class_name 
        FROM enrollments e 
        JOIN classes c ON e.class_id = c.id 
        WHERE e.student_id = ? AND e.is_deleted = 0
      `).all(userId);
      
      // 2. Hostel
      const hostel = database.prepare(`
        SELECT ra.*, r.room_number, h.name as hostel_name 
        FROM room_assignments ra
        JOIN rooms r ON ra.room_id = r.id
        JOIN hostels h ON r.hostel_id = h.id
        WHERE ra.student_id = ? AND ra.status = 'active' AND ra.is_deleted = 0
      `).get(userId) || null;
      
      // 3. Transport
      const transport = database.prepare(`
        SELECT COUNT(*) as count 
        FROM transport_allocations 
        WHERE student_id = ? AND is_active = 1 AND is_deleted = 0
      `).get(userId)?.count || 0;
      
      // 4. Clubs
      const clubs = database.prepare(`
        SELECT COUNT(*) as count 
        FROM club_memberships 
        WHERE student_id = ? AND status = 'active' AND is_deleted = 0
      `).get(userId)?.count || 0;
      
      // 5. Fees
      const feeRow = database.prepare(`
        SELECT COALESCE(SUM(amount - paid_amount - discount_amount + fine_amount), 0) AS outstanding 
        FROM student_fees 
        WHERE student_id = ? AND is_deleted = 0
      `).get(userId);
      const outstandingFees = Math.round((feeRow?.outstanding || 0) * 100) / 100;
      
      res.json({
        role: 'student',
        user: {
          id: userRow.id,
          uid: userRow.uid,
          first_name: userRow.first_name,
          last_name: userRow.last_name,
          email: userRow.email,
          id_number: userRow.id_number,
        },
        checks: {
          enrollments,
          hostel,
          transport,
          clubs,
          fees: outstandingFees
        }
      });
    } else {
      // Staff member
      // 1. Leave requests
      const leave = database.prepare(`
        SELECT COUNT(*) as count 
        FROM leave_requests 
        WHERE staff_id = ? AND status = 'pending' AND is_deleted = 0
      `).get(userId)?.count || 0;
      
      // 2. Payroll
      const latestPayslip = database.prepare(`
        SELECT p.*, r.period_month, r.period_year
        FROM payslips p
        JOIN payroll_runs r ON p.payroll_run_id = r.id
        WHERE p.staff_id = ? AND p.is_deleted = 0 AND p.status != 'cancelled'
        ORDER BY r.period_year DESC, r.period_month DESC
        LIMIT 1
      `).get(userId) || null;
      
      // 3. Classes
      const classes = database.prepare(`
        SELECT id, name FROM classes WHERE class_teacher_id = ? AND is_deleted = 0
      `).all(userId);
      
      // 4. Subjects
      const subjects = database.prepare(`
        SELECT id, name FROM subjects WHERE teacher_id = ? AND is_deleted = 0
      `).all(userId);
      
      // 5. Auxiliary Roles
      let roles = [];
      try {
        roles = database.prepare(`
          SELECT * FROM user_roles WHERE user_id = ? AND revoked_at IS NULL AND is_deleted = 0
        `).all(userId);
      } catch (roleErr) {
        // user_roles table may not exist
      }
      
      res.json({
        role: 'staff',
        user: {
          id: userRow.id,
          uid: userRow.uid,
          first_name: userRow.first_name,
          last_name: userRow.last_name,
          email: userRow.email,
          role: userRow.role,
        },
        checks: {
          leave,
          payroll: latestPayslip ? {
            id: latestPayslip.id,
            status: latestPayslip.status,
            net_salary: latestPayslip.net_salary,
            period: `${latestPayslip.period_year}-${latestPayslip.period_month}`
          } : null,
          classes,
          subjects,
          roles
        }
      });
    }
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * GET /api/offboarding/records
 * List offboarding cases with filter.
 */
router.get('/records', auth, requireViewAccess, (req, res) => {
  let { audience, status, limit = 50, page = 1 } = req.query;
  const offset = (parseInt(page, 10) - 1) * parseInt(limit, 10);

  if (req.user.role === 'hr') {
    if (audience === 'student') {
      return res.status(403).json({ error: 'HR is restricted to staff offboarding records' });
    }
    audience = 'staff';
  }

  try {
    const database = getDb();
    let query = `
      SELECT r.*, 
             u.first_name || ' ' || u.last_name as user_name, u.role as user_role, u.id_number as user_id_number,
             u2.first_name || ' ' || u2.last_name as initiator_name
      FROM offboarding_records r
      JOIN users u ON r.user_id = u.id
      LEFT JOIN users u2 ON r.initiated_by = u2.id
      WHERE r.is_deleted = 0
    `;
    const params = [];

    if (audience) {
      query += ' AND r.audience = ?';
      params.push(audience);
    }
    if (status) {
      query += ' AND r.status = ?';
      params.push(status);
    }

    query += ' ORDER BY r.created_at DESC LIMIT ? OFFSET ?';
    params.push(parseInt(limit, 10), offset);

    const records = database.prepare(query).all(...params);

    // Get total count
    let countQuery = 'SELECT COUNT(*) as count FROM offboarding_records r WHERE r.is_deleted = 0';
    const countParams = [];
    if (audience) {
      countQuery += ' AND r.audience = ?';
      countParams.push(audience);
    }
    if (status) {
      countQuery += ' AND r.status = ?';
      countParams.push(status);
    }
    const countResult = database.prepare(countQuery).get(...countParams);
    const total = countResult ? countResult.count : 0;

    res.json({
      records,
      pagination: {
        page: parseInt(page, 10),
        limit: parseInt(limit, 10),
        total,
        pages: Math.ceil(total / parseInt(limit, 10))
      }
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * GET /api/offboarding/records/:id
 * Retrieve details and steps of offboarding record.
 */
router.get('/records/:id', auth, requireViewAccess, (req, res) => {
  const recordId = parseInt(req.params.id, 10);

  try {
    const database = getDb();
    const record = database.prepare(`
      SELECT r.*, 
             u.first_name || ' ' || u.last_name as user_name, u.role as user_role, u.id_number as user_id_number,
             u2.first_name || ' ' || u2.last_name as initiator_name
      FROM offboarding_records r
      JOIN users u ON r.user_id = u.id
      LEFT JOIN users u2 ON r.initiated_by = u2.id
      WHERE r.id = ? AND r.is_deleted = 0
    `).get(recordId);

    if (!record) {
      return res.status(404).json({ error: 'Offboarding record not found' });
    }

    if (req.user.role === 'hr' && record.audience === 'student') {
      return res.status(403).json({ error: 'HR is restricted to staff offboarding records' });
    }

    const actions = database.prepare(`
      SELECT a.*, u.first_name || ' ' || u.last_name as performed_by_name
      FROM offboarding_actions a
      LEFT JOIN users u ON a.performed_by = u.id
      WHERE a.record_id = ? AND a.is_deleted = 0
      ORDER BY a.id ASC
    `).all(recordId);

    res.json({ ...record, actions });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * POST /api/offboarding/records/:id/cancel
 * Re-open/Cancel offboarding (sets status = 'cancelled' and re-enables user)
 */
router.post('/records/:id/cancel', auth, requireAdminOrPrincipal, (req, res) => {
  const recordId = parseInt(req.params.id, 10);
  const database = getDb();

  const tx = database.transaction(() => {
    const record = database.prepare('SELECT * FROM offboarding_records WHERE id = ? AND is_deleted = 0').get(recordId);
    if (!record) {
      throw new Error('Record not found');
    }
    if (record.status === 'cancelled') {
      throw new Error('Record is already cancelled');
    }

    // Set record status to cancelled
    database.prepare(`
      UPDATE offboarding_records
      SET status = 'cancelled', updated_at = datetime('now', 'localtime'), sync_status = 'pending'
      WHERE id = ?
    `).run(recordId);

    // Re-activate user
    database.prepare(`
      UPDATE users
      SET is_active = 1, archive_status = NULL, updated_at = datetime('now', 'localtime'), sync_status = 'pending'
      WHERE id = ?
    `).run(record.user_id);

    AuditService.log(req, 'CANCEL_OFFBOARD', {
      entityType: 'offboarding_records',
      entityId: record.uid,
      newData: { status: 'cancelled', is_active: 1 },
      db: database
    });

    return record;
  });

  try {
    const record = tx();
    res.json({ success: true, message: 'Offboarding cancelled and user account re-activated', record_uid: record.uid });
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

/**
 * POST /api/offboarding/records/:id/override
 * Admin override logic.
 */
router.post('/records/:id/override', auth, requireStrictAdmin, (req, res) => {
  const recordId = parseInt(req.params.id, 10);
  const { reason } = req.body;

  if (!reason) {
    return res.status(400).json({ error: 'Override reason is required' });
  }

  const database = getDb();
  const tx = database.transaction(() => {
    const record = database.prepare('SELECT * FROM offboarding_records WHERE id = ? AND is_deleted = 0').get(recordId);
    if (!record) {
      throw new Error('Record not found');
    }

    const action = database.prepare(`
      SELECT * FROM offboarding_actions 
      WHERE record_id = ? AND step IN ('fee_settled', 'payroll_finalized')
    `).get(recordId);

    if (action) {
      database.prepare(`
        UPDATE offboarding_actions 
        SET status = 'completed', notes = ?, updated_at = datetime('now', 'localtime'), sync_status = 'pending'
        WHERE id = ?
      `).run(reason, action.id);
    }

    AuditService.log(req, 'RECORD_OVERRIDE', {
      entityType: 'offboarding_records',
      entityId: record.uid,
      newData: { reason },
      db: database
    });
  });

  try {
    tx();
    res.json({ success: true, message: 'Override applied successfully' });
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

/**
 * GET /api/offboarding/alumni
 * Searchable alumni directory (read-only).
 */
router.get('/alumni', auth, requireViewAccess, (req, res) => {
  const { cohort_id, year, search } = req.query;

  try {
    const database = getDb();
    let query = `
      SELECT u.id, u.uid, u.first_name, u.last_name, u.email, u.phone, u.archive_status,
             r.effective_date, r.reason, r.reason_notes, r.destination,
             c.name as cohort_name, c.id as cohort_id
      FROM users u
      LEFT JOIN offboarding_records r ON u.id = r.user_id AND r.audience = 'student' AND r.status = 'completed'
      LEFT JOIN cohort_students cs ON u.id = cs.student_id AND cs.is_deleted = 0
      LEFT JOIN student_cohorts c ON cs.cohort_id = c.id AND c.is_deleted = 0
      WHERE u.role = 'student' AND u.archive_status IN ('active_alumni', 'inactive_alumni') AND u.is_deleted = 0
    `;
    const params = [];

    if (cohort_id) {
      query += ' AND c.id = ?';
      params.push(parseInt(cohort_id, 10));
    }
    if (year) {
      query += " AND strftime('%Y', r.effective_date) = ?";
      params.push(year);
    }
    if (search) {
      query += ' AND (u.first_name LIKE ? OR u.last_name LIKE ? OR u.email LIKE ?)';
      const term = `%${search}%`;
      params.push(term, term, term);
    }

    query += ' ORDER BY r.effective_date DESC';
    const alumni = database.prepare(query).all(...params);
    res.json(alumni);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * GET /api/offboarding/former-staff
 * Former staff list (HR).
 */
router.get('/former-staff', auth, requireViewAccess, (req, res) => {
  const { department_id, search } = req.query;

  try {
    const database = getDb();
    let query = `
      SELECT u.id, u.uid, u.first_name, u.last_name, u.email, u.phone, u.role, u.archive_status,
             r.effective_date, r.reason, r.reason_notes,
             d.name as department_name, d.id as department_id
      FROM users u
      LEFT JOIN offboarding_records r ON u.id = r.user_id AND r.audience = 'staff' AND r.status = 'completed'
      LEFT JOIN departments d ON u.department_id = d.id AND d.is_deleted = 0
      WHERE u.archive_status = 'former_staff' AND u.is_deleted = 0
    `;
    const params = [];

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

    query += ' ORDER BY r.effective_date DESC';
    const staff = database.prepare(query).all(...params);
    res.json(staff);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

module.exports = router;
