SELECT
 p.email,
 MAX(p.name) AS name,
 '2026-02' AS periode,
 SUM(p.ada_masuk) AS hari_masuk,
 SUM(p.ada_keluar) AS hari_keluar,
 SUM(CASE WHEN p.ada_masuk=1 AND p.ada_keluar=1 THEN 1 ELSE 0 END) AS hari_lengkap,
 22 AS hari_kerja,
 (22 - SUM(CASE WHEN p.ada_masuk=1 AND p.ada_keluar=1 THEN 1 ELSE 0 END)) AS hari_kurang,
 COALESCE(k.gapok,0) AS gapok,
 COALESCE(k.tunjangan,0) AS tunjangan,
 (COALESCE(k.gapok,0) + COALESCE(k.tunjangan,0)) AS gaji_total,
 ROUND(
 (22 - SUM(CASE WHEN p.ada_masuk=1 AND p.ada_keluar=1 THEN 1 ELSE 0 END)) *
 ((COALESCE(k.gapok,0) + COALESCE(k.tunjangan,0)) / 22)
 ) AS potongan_absen
FROM (
 SELECT
 d.email,
 d.name,
 d.tgl,
 CASE WHEN SUM(d.jenis='masuk') > 0 THEN 1 ELSE 0 END AS ada_masuk,
 CASE WHEN SUM(d.jenis='keluar') > 0 THEN 1 ELSE 0 END AS ada_keluar
 FROM (
 SELECT
 DATE(e.date_created) AS tgl,
 MAX(CASE WHEN m.meta_key='hidden-2' THEN m.meta_value END) AS name,
 MAX(CASE WHEN m.meta_key='hidden-3' THEN m.meta_value END) AS email,
 LOWER(MAX(CASE WHEN m.meta_key='radio-1' THEN m.meta_value END)) AS jenis
 FROM wpbw_frmt_form_entry e
 JOIN wpbw_frmt_form_entry_meta m ON m.entry_id=e.entry_id
 WHERE m.meta_key IN ('hidden-2','hidden-3','radio-1')
 AND e.date_created >= '2026-02-01'
 AND e.date_created < '2026-03-01'
 GROUP BY e.entry_id, tgl
 ) d
 WHERE d.email IS NOT NULL AND d.email <> ''
 GROUP BY d.email, d.tgl
) p
LEFT JOIN wpbw_karyawan k ON k.email_id = p.email
GROUP BY p.email
ORDER BY name ASC