-- WL6 - Verify staff lunch cap and auto-close working lunch

-- 1) Active staff with their current lunch setup
SELECT
    u.id AS user_id,
    COALESCE(u.staff_code, u.employee_code) AS staff_id,
    COALESCE(u.full_name, CONCAT(u.first_name, ' ', u.last_name)) AS staff_name,
    prs.name AS rule_name,
    prs.lunch_minutes,
    prs.working_lunch_paid,
    spr.effective_from,
    spr.effective_to
FROM users u
LEFT JOIN staff_payroll_rules spr
    ON spr.user_id = u.id
    AND spr.effective_from <= CURDATE()
    AND (spr.effective_to IS NULL OR spr.effective_to >= CURDATE())
LEFT JOIN payroll_rule_sets prs
    ON prs.id = spr.rule_set_id
WHERE u.is_active = 1
ORDER BY staff_name;

-- 2) Any working lunch record exceeding staff setup cap.
-- This should return zero rows after WL6.
SELECT
    b.id,
    b.work_session_id,
    b.user_id,
    COALESCE(u.staff_code, u.employee_code) AS staff_id,
    COALESCE(u.full_name, CONCAT(u.first_name, ' ', u.last_name)) AS staff_name,
    b.started_at,
    b.ended_at,
    b.rule_minutes_snapshot,
    b.actual_minutes AS stored_working_lunch_minutes,
    b.deducted_minutes,
    b.deduction_mode,
    b.status
FROM work_session_breaks b
INNER JOIN users u ON u.id = b.user_id
WHERE b.break_type = 'LUNCH'
  AND b.actual_minutes > b.rule_minutes_snapshot
ORDER BY b.id DESC;

-- 3) Open working lunches that are due for auto-close.
-- This should return zero rows after cron has run.
SELECT
    b.id,
    b.work_session_id,
    b.user_id,
    COALESCE(u.staff_code, u.employee_code) AS staff_id,
    COALESCE(u.full_name, CONCAT(u.first_name, ' ', u.last_name)) AS staff_name,
    b.started_at,
    b.rule_minutes_snapshot,
    TIMESTAMPDIFF(MINUTE, b.started_at, NOW()) AS elapsed_minutes,
    DATE_ADD(b.started_at, INTERVAL b.rule_minutes_snapshot MINUTE) AS should_auto_close_at,
    b.status
FROM work_session_breaks b
INNER JOIN users u ON u.id = b.user_id
WHERE b.break_type = 'LUNCH'
  AND b.status = 'OPEN'
  AND b.rule_minutes_snapshot > 0
  AND NOW() >= DATE_ADD(b.started_at, INTERVAL b.rule_minutes_snapshot MINUTE)
ORDER BY b.started_at ASC;

-- 4) Latest working lunch records
SELECT
    b.id,
    b.company_id,
    b.work_session_id,
    b.user_id,
    COALESCE(u.staff_code, u.employee_code) AS staff_id,
    COALESCE(u.full_name, CONCAT(u.first_name, ' ', u.last_name)) AS staff_name,
    b.started_at,
    b.ended_at,
    b.rule_minutes_snapshot,
    b.actual_minutes AS working_lunch_minutes,
    b.deducted_minutes AS lunch_deducted_minutes,
    b.deduction_mode,
    b.status
FROM work_session_breaks b
INNER JOIN users u ON u.id = b.user_id
WHERE b.break_type = 'LUNCH'
ORDER BY b.id DESC
LIMIT 50;