const { query, getClient } = require('../config/db');

// ═══════════════════════════════════════════════════════════════════
//  SHARED QUERY — re-used by list and detail endpoints
// ═══════════════════════════════════════════════════════════════════
const SELECT_LEAVE = `
  SELECT
    lr.id,
    lr.mode,
    lr.from_date,
    lr.to_date,
    lr.hours,
    lr.days,
    lr.reason,
    lr.status,
    lr.coverage_note,
    lr.forwarded_at,
    lr.approved_at,
    lr.rejected_reason,
    lr.created_at,
    lr.updated_at,

    -- Employee
    emp.id                              AS employee_id,
    emp.name                            AS employee_name,
    UPPER(LEFT(emp.name, 1)) ||
      UPPER(SPLIT_PART(emp.name,' ',2))  AS employee_initials,
    COALESCE(d.dept_name,'General')     AS employee_department,

    -- Leave type
    lt.id                               AS leave_type_id,
    lt.name                             AS leave_type_name,
    lt.color                            AS leave_type_color,

    -- Coverage
    cov.id                              AS coverage_user_id,
    cov.name                            AS coverage_name,
    UPPER(LEFT(cov.name,1)) ||
      UPPER(SPLIT_PART(cov.name,' ',2)) AS coverage_initials,

    -- HR
    hr.id                               AS approved_by_id,
    hr.name                             AS approved_by_name

  FROM leave_requests lr
  JOIN  users       emp ON emp.id = lr.employee_id
  JOIN  leave_types lt  ON lt.id  = lr.leave_type_id
  LEFT JOIN departments d   ON d.id   = emp.department_id
  LEFT JOIN users       cov ON cov.id = lr.coverage_user_id
  LEFT JOIN users       hr  ON hr.id  = lr.approved_by
`;

// ─────────────────────────────────────────────────────────────────
// STEP 1 — POST /api/leave/requests
// Employee submits a leave application.
// Body: { leave_type_id, mode, from_date, to_date?, hours?, days, reason }
// ─────────────────────────────────────────────────────────────────
async function applyLeave(req, res) {
  const { id: employee_id, org_id } = req.user;
  const {
    leave_type_id,
    mode = 'full',
    from_date,
    to_date,
    hours,
    days,
    reason,
  } = req.body;

  // ── Basic validation ──
  const errors = [];
  if (!leave_type_id)            errors.push('leave_type_id is required');
  if (!from_date)                errors.push('from_date is required');
  if (mode === 'full' && !to_date) errors.push('to_date is required for full-day leave');
  if (mode === 'hours' && !hours)  errors.push('hours is required for hourly leave');
  if (!days || isNaN(days))      errors.push('days (numeric) is required');
  if (!reason?.trim())           errors.push('reason is required');
  if (errors.length) return res.status(422).json({ success: false, errors });

  const client = await getClient();
  try {
    await client.query('BEGIN');

    // Verify the leave type belongs to this org
    const typeCheck = await client.query(
      `SELECT id, name FROM leave_types
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [leave_type_id, org_id]
    );
    if (!typeCheck.rows.length) {
      await client.query('ROLLBACK');
      return res.status(404).json({ success: false, message: 'Leave type not found' });
    }

    // Check sufficient balance
    const year = new Date(from_date).getFullYear();
    const balance = await client.query(
      `SELECT id, total_days, used_days
       FROM leave_balances
       WHERE user_id = $1 AND leave_type_id = $2 AND year = $3
         AND org_id = $4 AND is_deleted = false`,
      [employee_id, leave_type_id, year, org_id]
    );

    if (balance.rows.length) {
      const { total_days, used_days } = balance.rows[0];
      const remaining = parseFloat(total_days) - parseFloat(used_days);
      if (parseFloat(days) > remaining) {
        await client.query('ROLLBACK');
        return res.status(422).json({
          success: false,
          message: `Insufficient balance. Remaining: ${remaining} days`,
        });
      }
    }

    // Insert request
    const { rows } = await client.query(
      `INSERT INTO leave_requests
         (employee_id, leave_type_id, mode, from_date, to_date, hours,
          days, reason, status, org_id, created_by)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,'pending',$9,$10)
       RETURNING id`,
      [
        employee_id, leave_type_id, mode, from_date,
        to_date || null, hours || null, days, reason,
        org_id, employee_id,
      ]
    );

    await client.query('COMMIT');

    // Return full enriched record
    const full = await query(
      `${SELECT_LEAVE} WHERE lr.id = $1`, [rows[0].id]
    );

    res.status(201).json({ success: true, data: full.rows[0] });
  } catch (err) {
    await client.query('ROLLBACK');
    console.error('applyLeave:', err);
    res.status(500).json({ success: false, message: 'Internal server error' });
  } finally {
    client.release();
  }
}

// ─────────────────────────────────────────────────────────────────
// GET /api/leave/requests
// Returns leave requests.  Scoped by role:
//   employee  → own requests only
//   manager   → team's requests (pending + all)
//   hr/admin  → all org requests
//
// Query params (all optional):
//   status    = pending | approved | rejected
//   from      = YYYY-MM-DD
//   to        = YYYY-MM-DD
//   employee_id
//   page      = 1
//   limit     = 20
// ─────────────────────────────────────────────────────────────────
async function getLeaveRequests(req, res) {
  const { id: user_id, org_id, role } = req.user;
  const {
    status, from, to, employee_id,
    page = 1, limit = 20,
  } = req.query;

  const offset = (parseInt(page) - 1) * parseInt(limit);
  const params = [org_id];
  const conditions = ['lr.org_id = $1', 'lr.is_deleted = false'];

  // Role scoping
  const isHrOrAdmin = ['admin', 'hr', 'hr_admin'].includes(role);
  const isManager   = role === 'manager';

  if (!isHrOrAdmin) {
    if (isManager) {
      // Manager sees their team's requests
      params.push(user_id);
      conditions.push(`
        emp.id IN (
          SELECT employee_id FROM team_members
          WHERE manager_id = $${params.length} AND is_deleted = false
        )
      `);
    } else {
      // Employee sees only own
      params.push(user_id);
      conditions.push(`lr.employee_id = $${params.length}`);
    }
  }

  // Optional filters
  if (status) {
    params.push(status);
    conditions.push(`lr.status = $${params.length}`);
  }
  if (from) {
    params.push(from);
    conditions.push(`lr.from_date >= $${params.length}`);
  }
  if (to) {
    params.push(to);
    conditions.push(`lr.from_date <= $${params.length}`);
  }
  if (employee_id && isHrOrAdmin) {
    params.push(parseInt(employee_id));
    conditions.push(`lr.employee_id = $${params.length}`);
  }

  const WHERE = conditions.join(' AND ');

  try {
    const countResult = await query(
      `SELECT COUNT(*) FROM leave_requests lr
       JOIN users emp ON emp.id = lr.employee_id
       WHERE ${WHERE}`,
      params
    );
    const total = parseInt(countResult.rows[0].count);

    params.push(parseInt(limit));
    params.push(offset);
    const dataResult = await query(
      `${SELECT_LEAVE}
       WHERE ${WHERE}
       ORDER BY lr.created_at DESC
       LIMIT $${params.length - 1} OFFSET $${params.length}`,
      params
    );

    res.json({
      success: true,
      data: dataResult.rows,
      pagination: {
        total,
        page: parseInt(page),
        limit: parseInt(limit),
        pages: Math.ceil(total / parseInt(limit)),
      },
    });
  } catch (err) {
    console.error('getLeaveRequests:', err);
    res.status(500).json({ success: false, message: 'Internal server error' });
  }
}

// ─────────────────────────────────────────────────────────────────
// GET /api/leave/requests/:id
// ─────────────────────────────────────────────────────────────────
async function getLeaveRequestById(req, res) {
  const { id } = req.params;
  const { org_id } = req.user;

  try {
    const { rows } = await query(
      `${SELECT_LEAVE} WHERE lr.id = $1 AND lr.org_id = $2 AND lr.is_deleted = false`,
      [id, org_id]
    );
    if (!rows.length) return res.status(404).json({ success: false, message: 'Leave request not found' });
    res.json({ success: true, data: rows[0] });
  } catch (err) {
    console.error('getLeaveRequestById:', err);
    res.status(500).json({ success: false, message: 'Internal server error' });
  }
}

// ─────────────────────────────────────────────────────────────────
// STEP 2a — PATCH /api/leave/requests/:id/coverage
// Manager assigns a coverage person to a pending request.
// Body: { coverage_user_id, coverage_note? }
// ─────────────────────────────────────────────────────────────────
async function assignCoverage(req, res) {
  const { id } = req.params;
  const { coverage_user_id, coverage_note = '' } = req.body;
  const { org_id, id: updated_by } = req.user;

  if (!coverage_user_id) {
    return res.status(422).json({ success: false, message: 'coverage_user_id is required' });
  }

  try {
    // Verify the coverage person exists in this org
    const userCheck = await query(
      `SELECT id FROM users WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [coverage_user_id, org_id]
    );
    if (!userCheck.rows.length) {
      return res.status(404).json({ success: false, message: 'Coverage user not found' });
    }

    const { rows } = await query(
      `UPDATE leave_requests
       SET coverage_user_id = $1,
           coverage_note    = $2,
           updated_by       = $3,
           updated_at       = NOW()
       WHERE id = $4
         AND org_id = $5
         AND status = 'pending'
         AND is_deleted = false
       RETURNING id`,
      [coverage_user_id, coverage_note, updated_by, id, org_id]
    );

    if (!rows.length) {
      return res.status(404).json({
        success: false,
        message: 'Request not found or not in pending status',
      });
    }

    const full = await query(`${SELECT_LEAVE} WHERE lr.id = $1`, [rows[0].id]);
    res.json({ success: true, data: full.rows[0] });
  } catch (err) {
    console.error('assignCoverage:', err);
    res.status(500).json({ success: false, message: 'Internal server error' });
  }
}

// ─────────────────────────────────────────────────────────────────
// STEP 2b — PATCH /api/leave/requests/:id/forward
// Manager forwards a request (with coverage assigned) to HR.
// Sets forwarded_at timestamp.
// ─────────────────────────────────────────────────────────────────
async function forwardToHR(req, res) {
  const { id } = req.params;
  const { org_id, id: updated_by } = req.user;

  try {
    // Check all requests under this manager are covered before forwarding
    const check = await query(
      `SELECT id FROM leave_requests
       WHERE id = $1
         AND org_id = $2
         AND status = 'pending'
         AND is_deleted = false
         AND coverage_user_id IS NULL`,
      [id, org_id]
    );
    if (check.rows.length) {
      return res.status(422).json({
        success: false,
        message: 'Coverage must be assigned before forwarding to HR',
      });
    }

    const { rows } = await query(
      `UPDATE leave_requests
       SET forwarded_at = NOW(),
           updated_by   = $1,
           updated_at   = NOW()
       WHERE id = $2
         AND org_id = $3
         AND status = 'pending'
         AND is_deleted = false
       RETURNING id`,
      [updated_by, id, org_id]
    );

    if (!rows.length) {
      return res.status(404).json({ success: false, message: 'Request not found or already actioned' });
    }

    const full = await query(`${SELECT_LEAVE} WHERE lr.id = $1`, [rows[0].id]);
    res.json({ success: true, data: full.rows[0] });
  } catch (err) {
    console.error('forwardToHR:', err);
    res.status(500).json({ success: false, message: 'Internal server error' });
  }
}

// ─────────────────────────────────────────────────────────────────
// STEP 3a — PATCH /api/leave/requests/:id/approve
// HR approves a leave request.
// Deducts used_days from leave_balances atomically.
// ─────────────────────────────────────────────────────────────────
async function approveLeave(req, res) {
  const { id } = req.params;
  const { org_id, id: hr_id } = req.user;

  const client = await getClient();
  try {
    await client.query('BEGIN');

    // Fetch the request
    const reqResult = await client.query(
      `SELECT id, employee_id, leave_type_id, days, from_date, status
       FROM leave_requests
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, org_id]
    );
    if (!reqResult.rows.length) {
      await client.query('ROLLBACK');
      return res.status(404).json({ success: false, message: 'Leave request not found' });
    }

    const lr = reqResult.rows[0];
    if (lr.status !== 'pending') {
      await client.query('ROLLBACK');
      return res.status(422).json({
        success: false,
        message: `Cannot approve — request is already ${lr.status}`,
      });
    }

    // Approve
    await client.query(
      `UPDATE leave_requests
       SET status      = 'approved',
           approved_by = $1,
           approved_at = NOW(),
           updated_by  = $1,
           updated_at  = NOW()
       WHERE id = $2`,
      [hr_id, id]
    );

    // Deduct from leave_balances
    const year = new Date(lr.from_date).getFullYear();
    await client.query(
      `UPDATE leave_balances
       SET used_days  = used_days + $1,
           updated_by = $2,
           updated_at = NOW()
       WHERE user_id      = $3
         AND leave_type_id = $4
         AND year          = $5
         AND org_id        = $6`,
      [lr.days, hr_id, lr.employee_id, lr.leave_type_id, year, org_id]
    );

    await client.query('COMMIT');

    const full = await query(`${SELECT_LEAVE} WHERE lr.id = $1`, [id]);
    res.json({ success: true, data: full.rows[0] });
  } catch (err) {
    await client.query('ROLLBACK');
    console.error('approveLeave:', err);
    res.status(500).json({ success: false, message: 'Internal server error' });
  } finally {
    client.release();
  }
}

// ─────────────────────────────────────────────────────────────────
// STEP 3b — PATCH /api/leave/requests/:id/reject
// HR rejects a leave request.
// Body: { rejected_reason }
// ─────────────────────────────────────────────────────────────────
async function rejectLeave(req, res) {
  const { id } = req.params;
  const { rejected_reason } = req.body;
  const { org_id, id: hr_id } = req.user;

  if (!rejected_reason?.trim()) {
    return res.status(422).json({ success: false, message: 'rejected_reason is required' });
  }

  try {
    const { rows } = await query(
      `UPDATE leave_requests
       SET status          = 'rejected',
           rejected_reason = $1,
           approved_by     = $2,
           approved_at     = NOW(),
           updated_by      = $2,
           updated_at      = NOW()
       WHERE id = $3
         AND org_id = $4
         AND status = 'pending'
         AND is_deleted = false
       RETURNING id`,
      [rejected_reason, hr_id, id, org_id]
    );

    if (!rows.length) {
      return res.status(404).json({
        success: false,
        message: 'Request not found or already actioned',
      });
    }

    const full = await query(`${SELECT_LEAVE} WHERE lr.id = $1`, [rows[0].id]);
    res.json({ success: true, data: full.rows[0] });
  } catch (err) {
    console.error('rejectLeave:', err);
    res.status(500).json({ success: false, message: 'Internal server error' });
  }
}

// ─────────────────────────────────────────────────────────────────
// DELETE /api/leave/requests/:id
// Employee can withdraw own PENDING request (soft delete).
// ─────────────────────────────────────────────────────────────────
async function withdrawLeave(req, res) {
  const { id } = req.params;
  const { id: user_id, org_id } = req.user;

  try {
    const { rows } = await query(
      `UPDATE leave_requests
       SET is_deleted = true, updated_by = $1, updated_at = NOW()
       WHERE id = $2
         AND employee_id = $1
         AND org_id = $3
         AND status = 'pending'
         AND is_deleted = false
       RETURNING id`,
      [user_id, id, org_id]
    );

    if (!rows.length) {
      return res.status(404).json({
        success: false,
        message: 'Request not found, already actioned, or not owned by you',
      });
    }
    res.json({ success: true, message: 'Leave request withdrawn' });
  } catch (err) {
    console.error('withdrawLeave:', err);
    res.status(500).json({ success: false, message: 'Internal server error' });
  }
}

// ─────────────────────────────────────────────────────────────────
// GET /api/leave/requests/summary
// Step 4 — workflow summary counts for the org (HR/admin only)
// Query: ?from=YYYY-MM-DD&to=YYYY-MM-DD
// ─────────────────────────────────────────────────────────────────
async function getSummary(req, res) {
  const { org_id } = req.user;
  const { from, to } = req.query;

  const params = [org_id];
  let dateFilter = '';
  if (from) { params.push(from); dateFilter += ` AND lr.from_date >= $${params.length}`; }
  if (to)   { params.push(to);   dateFilter += ` AND lr.from_date <= $${params.length}`; }

  try {
    const { rows } = await query(
      `SELECT
         COUNT(*) FILTER (WHERE status = 'pending')  AS pending,
         COUNT(*) FILTER (WHERE status = 'approved') AS approved,
         COUNT(*) FILTER (WHERE status = 'rejected') AS rejected,
         COUNT(*)                                     AS total
       FROM leave_requests lr
       WHERE lr.org_id = $1 AND lr.is_deleted = false ${dateFilter}`,
      params
    );
    res.json({ success: true, data: rows[0] });
  } catch (err) {
    console.error('getSummary:', err);
    res.status(500).json({ success: false, message: 'Internal server error' });
  }
}

module.exports = {
  applyLeave,
  getLeaveRequests,
  getLeaveRequestById,
  assignCoverage,
  forwardToHR,
  approveLeave,
  rejectLeave,
  withdrawLeave,
  getSummary,
};