SELECT DATE_FORMAT(gb.created_at, '%Y-%m') AS ym, qo.text_en AS source, COUNT(DISTINCT gq.guest_book_id) AS total_prospects FROM shieldtech_admission.guest_book_questionnaires gq JOIN shieldtech_admission.questionnaire_options qo ON gq.questionnaire_option_id = qo.id JOIN shieldtech_admission.guest_books gb ON gq.guest_book_id = gb.id WHERE qo.question_id = 1 AND gq.answer = '1' AND gb.created_at IS NOT NULL GROUP BY ym, source ORDER BY ym ASC, source ASC;

SELECT '1. Leads Masuk (Guest Books)' as stage, COUNT(id) as total FROM shieldtech_admission.guest_books UNION ALL SELECT '2. Leads di Follow-Up' as stage, COUNT(DISTINCT guest_book_id) as total FROM shieldtech_admission.guest_book_follow_ups UNION ALL SELECT '3. Mengisi Aplikasi' as stage, COUNT(id) as total FROM shieldtech_admission.admission_student_profiles WHERE fill_by_parent = 1 UNION ALL SELECT '4. Diterima (Accepted)' as stage, COUNT(id) as total FROM shieldtech_admission.student_assessments WHERE acceptance_status_id = 1 UNION ALL SELECT '5. Menjadi Siswa Aktif (Bayar)' as stage, COUNT(DISTINCT pfc.prospect_profile_id) as total FROM shieldtech_admission.parent_financial_commitment_forms pfc WHERE pfc.status_id > 0;

SELECT r.name as regency_name, COUNT(gb.id) as total_prospects FROM shieldtech_admission.guest_books gb JOIN shieldtech_common.regencies r ON gb.regency_code = r.code GROUP BY r.name ORDER BY total_prospects DESC LIMIT 10;