-- Package 01B verification script

SELECT 'companies' AS check_name, COUNT(*) AS total_rows FROM companies;
SELECT 'company_settings' AS check_name, COUNT(*) AS total_rows FROM company_settings;
SELECT 'users_missing_company' AS check_name, COUNT(*) AS total_rows FROM users WHERE company_id IS NULL;
SELECT 'users_missing_staff_code' AS check_name, COUNT(*) AS total_rows FROM users WHERE staff_code IS NULL OR staff_code = '';
SELECT 'users_missing_full_name' AS check_name, COUNT(*) AS total_rows FROM users WHERE full_name IS NULL OR full_name = '';
SELECT 'worksites_missing_company' AS check_name, COUNT(*) AS total_rows FROM worksites WHERE company_id IS NULL;
SELECT 'work_sessions_missing_company' AS check_name, COUNT(*) AS total_rows FROM work_sessions WHERE company_id IS NULL;
SELECT 'work_session_events_missing_company' AS check_name, COUNT(*) AS total_rows FROM work_session_events WHERE company_id IS NULL;
SELECT 'open_sessions' AS check_name, COUNT(*) AS total_rows FROM work_sessions WHERE session_status = 'OPEN';
SELECT 'auto_closed_sessions' AS check_name, COUNT(*) AS total_rows FROM work_sessions WHERE auto_closed_flag = 1;
SELECT 'outside_radius_any' AS check_name, COUNT(*) AS total_rows
FROM work_sessions
WHERE clock_in_status = 'OUTSIDE_RADIUS' OR clock_out_status = 'OUTSIDE_RADIUS';

SELECT
    u.staff_code,
    u.full_name,
    COUNT(ws.id) AS session_count
FROM users u
LEFT JOIN work_sessions ws
    ON ws.user_id = u.id
   AND ws.company_id = u.company_id
GROUP BY u.id, u.staff_code, u.full_name
ORDER BY u.full_name;