-- STEP 1: View all pay runs for payroll ID = 8 
-- This helps identify any duplicate or incorrect pay run entries
SELECT * 
FROM unza_payroll_08_08_2025.pam_pay_runs 
WHERE payroll_id = 8 
ORDER BY pay_run_period_id, pay_run_status;

-- STEP 2: Find and display duplicate pay runs based on same period ID
-- Join the grouped duplicates to get full details
SELECT pr.*
FROM unza_payroll_08_08_2025.pam_pay_runs pr
JOIN (
    SELECT payroll_id, pay_run_period_id
    FROM unza_payroll_08_08_2025.pam_pay_runs
    WHERE payroll_id = 8
    GROUP BY payroll_id, pay_run_period_id
    HAVING COUNT(*) > 1
) dup
ON pr.payroll_id = dup.payroll_id AND pr.pay_run_period_id = dup.pay_run_period_id
ORDER BY pr.pay_run_period_id, pr.id;

-- STEP 3: Close the duplicate pay run by setting status to 'closed' and active = 0
-- This ensures only one active pay run remains for the same period
UPDATE unza_payroll_08_08_2025.pam_pay_runs 
SET pay_run_status = 'closed', active = 0 
WHERE id = 70;

-- STEP 4: View the correct pay run that should remain active (ID = 69)
SELECT * 
FROM unza_payroll_08_08_2025.pam_pay_runs  
WHERE id = 69;

-- STEP 5: Update the tax table ID on the correct pay run (ID = 69)
-- Ensures proper tax rates are applied during calculations
UPDATE unza_payroll_08_08_2025.pam_pay_runs 
SET tax_table_id = 25 
WHERE id = 69;

-- STEP 6: View the payroll master record for payroll ID = 8
-- Needed before updating with the correct period and pay run ID
SELECT * 
FROM unza_payroll_08_08_2025.pam_payrolls 
WHERE id = 8;

-- STEP 7: Update the payroll to set:
-- - the current period month
-- - the correct current pay run ID
-- - the correct tax table ID
UPDATE unza_payroll_08_08_2025.pam_payrolls 
SET current_period_month = 7, 
    current_pay_run_id = 69, 
    current_tax_table_id = 25 
WHERE id = 8;


-- STEP 8 (optional check): Count records that are already assigned to the correct pay run (ID = 70)
-- Useful for validating if data has already been moved
SELECT COUNT(*) AS records
FROM unza_payroll_08_08_2025.pam_payslips 
WHERE pay_run_id =70 AND pay_run_period_id = 217;

-- STEP 9 (optional check): Count records that are already assigned to the correct pay run (ID = 69)
-- Useful for validating if data has already been moved
SELECT COUNT(*) AS records
FROM unza_payroll_08_08_2025.pam_payslips 
WHERE pay_run_id = 69 AND pay_run_period_id = 223;


-- STEP 10: Check if any payslips are still linked to the duplicate/incorrect pay run (ID = 70)
-- These may need to be moved to the correct pay run manually
SELECT * 
FROM unza_payroll_08_08_2025.pam_payslips 
WHERE pay_run_id = 70 AND pay_run_period_id = 217;

-- STEP 11: Check if any payslips are still linked to the duplicate/incorrect pay run (ID = 69)
-- These may need to be moved to the correct pay run manually
SELECT * 
FROM unza_payroll_08_08_2025.pam_payslips 
WHERE pay_run_id = 69 AND pay_run_period_id = 223;