-- PAYADJ1 - Payroll adjustment verification

-- 1) Latest adjustments
SELECT
    pa.id,
    pa.company_id,
    pa.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,
    pa.adjustment_date,
    pa.normal_minutes_adjustment,
    pa.ot_15_minutes_adjustment,
    pa.ot_20_minutes_adjustment,
    pa.reason,
    pa.status,
    pa.void_reason,
    pa.created_at
FROM payroll_adjustments pa
INNER JOIN users u ON u.id = pa.user_id
ORDER BY pa.id DESC
LIMIT 50;

-- 2) Active adjustment totals by staff and date range.
-- Change the dates as needed.
SELECT
    pa.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,
    SUM(pa.normal_minutes_adjustment) AS normal_adjustment_minutes,
    SUM(pa.ot_15_minutes_adjustment) AS ot_15_adjustment_minutes,
    SUM(pa.ot_20_minutes_adjustment) AS ot_20_adjustment_minutes
FROM payroll_adjustments pa
INNER JOIN users u ON u.id = pa.user_id
WHERE pa.status = 'ACTIVE'
  AND pa.adjustment_date BETWEEN '2026-05-04' AND CURDATE()
GROUP BY pa.user_id, staff_id, staff_name
ORDER BY staff_name;

-- 3) Voided adjustments audit
SELECT
    pa.id,
    pa.adjustment_date,
    COALESCE(u.staff_code, u.employee_code) AS staff_id,
    COALESCE(u.full_name, CONCAT(u.first_name, ' ', u.last_name)) AS staff_name,
    pa.reason,
    pa.void_reason,
    pa.voided_at
FROM payroll_adjustments pa
INNER JOIN users u ON u.id = pa.user_id
WHERE pa.status = 'VOID'
ORDER BY pa.voided_at DESC;