WITH 
attendance_daily AS (
SELECT 
employee_id,
DATE(punch_time) as work_date,
MIN(punch_time) as first_clock,
MAX(punch_time) as last_clock
FROM attendance
WHERE YEAR(punch_time) = 2025
GROUP BY employee_id, DATE(punch_time)
),
attendance_total AS (
SELECT 
employee_id,
SUM(
TIMESTAMPDIFF(MINUTE, first_clock, last_clock) / 60.0 -
CASE 
WHEN HOUR(first_clock) < 12 AND HOUR(last_clock) >= 14 THEN 2
ELSE 0
END
) as total_hours
FROM attendance_daily
WHERE first_clock != last_clock
GROUP BY employee_id
),
attendance_score AS (
SELECT 
employee_id,
CASE 
WHEN total_hours < (SELECT AVG(total_hours) FROM attendance_total) 
THEN 1 
ELSE 0 
END as score
FROM attendance_total
),
compensation_score AS (
SELECT 
id as employee_id,
CASE 
WHEN (TIMESTAMPDIFF(YEAR, hire_date, '2026-03-06') + 1) * salary < 10 THEN 3
WHEN (TIMESTAMPDIFF(YEAR, hire_date, '2026-03-06') + 1) * salary < 20 THEN 2
WHEN (TIMESTAMPDIFF(YEAR, hire_date, '2026-03-06') + 1) * salary < 30 THEN 1
ELSE 0
END as score
FROM employees
),
walk_daily AS (
SELECT 
employee_id,
DATE(timestamp) as walk_date,
COUNT(*) as daily_count
FROM access_control
WHERE YEAR(timestamp) = 2025
AND (HOUR(timestamp) < 12 OR HOUR(timestamp) >= 14)
GROUP BY employee_id, DATE(timestamp)
),
walk_stats AS (
SELECT 
employee_id,
COUNT(*) as total_days,
SUM(CASE WHEN daily_count > 3 THEN 1 ELSE 0 END) as high_freq_days
FROM walk_daily
GROUP BY employee_id
),
walk_score AS (
SELECT 
employee_id,
CASE 
WHEN total_days > 70 AND high_freq_days > 25 THEN 3
WHEN total_days > 60 THEN 1
ELSE 0
END as score
FROM walk_stats
),
emp_flybook_avg AS (
SELECT 
employee_id,
AVG(daily_interaction) as avg_interaction,
AVG(daily_messages) as avg_message,
AVG(daily_documents) as avg_doc
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
GROUP BY employee_id
),
company_flybook_avg AS (
SELECT 
AVG(daily_interaction) as avg_interaction,
AVG(daily_messages) as avg_message,
AVG(daily_documents) as avg_doc
FROM flybook_stats
WHERE stat_date >= '2025-01-31'
),
flybook_score AS (
SELECT 
emp.employee_id,
CASE 
WHEN emp.avg_interaction < comp.avg_interaction 
 AND emp.avg_message < comp.avg_message 
 AND emp.avg_doc < comp.avg_doc THEN 3
WHEN (CASE WHEN emp.avg_interaction < comp.avg_interaction THEN 1 ELSE 0 END +
CASE WHEN emp.avg_message < comp.avg_message THEN 1 ELSE 0 END +
CASE WHEN emp.avg_doc < comp.avg_doc THEN 1 ELSE 0 END) = 2 THEN 1
ELSE 0
END as score
FROM emp_flybook_avg emp
CROSS JOIN company_flybook_avg comp
)
SELECT 
e.id as employee_id,
COALESCE(a.score, 0) + COALESCE(c.score, 0) + 
COALESCE(w.score, 0) + COALESCE(f.score, 0) as score
FROM employees e
LEFT JOIN attendance_score a ON e.id = a.employee_id
LEFT JOIN compensation_score c ON e.id = c.employee_id
LEFT JOIN walk_score w ON e.id = w.employee_id
LEFT JOIN flybook_score f ON e.id = f.employee_id
ORDER BY score DESC, e.id;