admission

select
        ms_student.student_id,
        tr_admission_batch.admission_batch_id,
        ms_school_location.school_short_address as school_location_name,
        ms_year_level.description as year_level_name,
        ms_admission_candidate.admission_form_id as form_number,
        ms_admission_candidate.candidate_id,
        ms_admission_candidate.email as candidate_email,
        ms_admission_candidate.student_name,
        ms_admission_candidate.gender,
        ms_admission_candidate.date_of_birth,
        ms_admission_candidate.origin_school,
        ms_admission_candidate.address as candidate_address,
        ms_admission_candidate.cellular_number as candidate_number,
        ms_admission_candidate.cellular_number,
        ms_admission_candidate.created_at as registration_date,
        ms_admission_candidate.nis_generated,
        ms_religion.agama,
        ms_school_level.school_level_id,
        ms_school_location.school_location_id,
        t_user.user_id,
        t_user.username,
        t_user.email,
        t_user.uuid,
        t_user.is_temporary,
        t_user.email_verified_at,
        tr_admission_candidate_status.unpaid,
        tr_admission_candidate_status.canceled,
        tr_admission_batch.batch_participant_category,
        tr_admission_batch.final_payment_amount,
        tr_admission_candidate_status.deleted_at,
        json_build_object(
            'payment', row_to_json(tr_admission_payment)
        ) as candidate,
        null as status_final_payment,
        null as insufficient_payment,
        null as current_status,
        null as next_status,
        MAX(tr_admission_batch_step_relation.admission_step_id) as admission_step_id,
        JSON_AGG(ms_admission_step.step_rule order by tr_admission_batch_step_relation.step_order) as step_orders,
        COUNT(distinct ms_admission_step.step_rule) as total_step_rules,
        JSON_AGG(distinct t_user.username) as usernames,
        JSON_AGG(distinct t_user.email) as emails,
        JSON_AGG(distinct ms_admission_parents.email) as parent_emails,
        JSON_AGG(distinct ms_admission_sibling.sibling_name) as sibling_names,
        JSON_AGG(ms_admission_parents.parent_name ORDER BY parent_type) as parent_names,
        JSON_AGG(ms_admission_parents.email ORDER BY parent_type) as parent_email,
        JSON_AGG(ms_admission_parents.cellular_number ORDER BY parent_type) as parent_number,
        JSON_AGG(ms_admission_parents.address ORDER BY parent_type) as parent_address,
        COUNT(distinct ms_admission_sibling.sibling_name) as sibling_count,
        MAX(case when ms_admission_step.step_rule = 'registration' THEN 1 ELSE 0 END) has_step_registration,
        MAX(case when ms_admission_step.step_rule = 'registration_payment' THEN 1 ELSE 0 END) has_step_registration_payment,
        MAX(case when ms_admission_step.step_rule = 'test_schedule' THEN 1 ELSE 0 END) has_step_test_schedule,
        MAX(case when ms_admission_step.step_rule = 'test_result' THEN 1 ELSE 0 END) has_step_test_result,
        MAX(case when ms_admission_step.step_rule = 'final_payment' THEN 1 ELSE 0 END) has_step_final_payment,
        MAX(case when tr_admission_payment.registration_payment_date is not null then 1 else 0 end) has_paid_registration_payment,
        CASE WHEN (
            MAX(case when ms_admission_step.step_rule = 'registration_payment' THEN 1 ELSE 0 END) = 0
        ) THEN true
        ELSE tr_admission_candidate_status.paid
        END,
        CASE WHEN (
            MAX(case when ms_admission_step.step_rule = 'registration_payment' THEN 1 ELSE 0 END) = 0
        ) THEN true
        ELSE tr_admission_candidate_status.verified
        END,
        CASE WHEN (
            MAX(case when ms_admission_step.step_rule = 'registration_payment' THEN 1 ELSE 0 END) = 0
        ) THEN true
        ELSE tr_admission_candidate_status.registration_payment_status
        END,
        MAX(case when tr_admission_test_schedule_participant.admission_test_schedule_participant_id is not null then 1 else 0 end) as has_test_schedule,
        MIN(tr_admission_test_schedule_criteria.test_date + tr_admission_test_schedule_criteria.start_time) <= '$now' as has_test_schedule_begin,
        MAX(case when tr_admission_test_result.test_result is not null then 1 else 0 end) as has_test_result,
        MAX(case when tr_admission_test_result.test_result is false then 1 else 0 end) as has_test_result_failed,
        MAX(case when tr_admission_payment.final_payment_date is not null then 1 else 0 end) has_paid_final_payment,
        CASE WHEN (
                         COUNT(distinct ms_admission_step.step_rule) = 1
                         AND
                         MAX(case when ms_admission_step.step_rule = 'registration' THEN 1 ELSE 0 END) = 1
                         AND
                         tr_admission_candidate_status.unpaid IS TRUE
        ) THEN TRUE
        WHEN (
            MAX(case when ms_admission_step.step_rule = 'test_schedule' THEN 1 ELSE 0 END) = 1
                AND
            MAX(case when ms_admission_step.step_rule = 'test_result' THEN 1 ELSE 0 END) = 0
                AND
            MAX(case when ms_admission_step.step_rule = 'final_payment' THEN 1 ELSE 0 END) = 0
                AND
            MIN(tr_admission_test_schedule_criteria.test_date + tr_admission_test_schedule_criteria.start_time) <= '$now'
        ) THEN TRUE
        WHEN (
            MAX(case when ms_admission_step.step_rule = 'test_result' THEN 1 ELSE 0 END) = 1
                AND
            MAX(case when ms_admission_step.step_rule = 'final_payment' THEN 1 ELSE 0 END) = 0
                AND
            MAX(case when tr_admission_test_result.test_result is true then 1 else 0 end) = 1
        ) THEN TRUE
        ELSE tr_admission_candidate_status.complete
        END AS complete,
        CASE WHEN (
            COUNT(distinct ms_admission_step.step_rule) = 1
                AND
            MAX(case when ms_admission_step.step_rule = 'registration' THEN 1 ELSE 0 END) = 1
                AND
            tr_admission_candidate_status.unpaid IS TRUE
        ) THEN TRUE
        ELSE tr_admission_candidate_status.final_payment_status
        END AS final_payment_status,
        case when (
            MAX(case when ms_admission_step.step_rule = 'test_result' THEN 1 ELSE 0 END) = 1
        ) then (
            case when (
                MAX(case when tr_admission_test_result.test_result is true then 1 else 0 end) = 1
                ) then true
            else false
            end
        )
        when (
            MAX(case when ms_admission_step.step_rule = 'test_schedule' THEN 1 ELSE 0 END) = 1
            ) then (
                MIN(tr_admission_test_schedule_criteria.test_date + tr_admission_test_schedule_criteria.start_time) <= '$now'
            )
        when (
            MAX(case when ms_admission_step.step_rule = 'registration_payment' THEN 1 ELSE 0 END) = 1
            ) then (
                    case when registration_payment_status is true
                        then true
                    else false
                    end
                )
        when tr_admission_candidate_status.unpaid IS FALSE
            THEN FALSE
        else true
        end as in_step_final_payment
from tr_admission_candidate_status
    join ms_admission_candidate
        on ms_admission_candidate.candidate_id = tr_admission_candidate_status.candidate_id
    join tr_admission_batch
        on tr_admission_batch.admission_batch_id = ms_admission_candidate.admission_batch_id
    join ms_school_level
        on ms_school_level.school_level_id = ms_admission_candidate.enroll_school_level_id
    join ms_school_location
        on ms_school_location.school_location_id = ms_admission_candidate.enroll_school_location_id
    join t_user
        on t_user.uuid = ms_admission_candidate.user_uuid
        and t_user.candidate_id = ms_admission_candidate.candidate_id
    join tr_admission_batch_step_relation
        on tr_admission_batch_step_relation.admission_batch_id = tr_admission_batch.admission_batch_id
    join ms_admission_step
        on ms_admission_step.admission_step_id = tr_admission_batch_step_relation.admission_step_id
    left join ms_year_level
        on ms_year_level.year_level_id = ms_admission_candidate.enroll_year_level_id
    left join ms_student
        on ms_student.user_id = t_user.user_id
    left join tr_admission_test_schedule_participant
        on tr_admission_test_schedule_participant.candidate_id = ms_admission_candidate.candidate_id
    left join tr_admission_test_schedule
        on (
            tr_admission_test_schedule.admission_batch_id = tr_admission_batch.admission_batch_id
                and
            tr_admission_test_schedule.admission_test_schedule_id = tr_admission_test_schedule_participant.admission_test_schedule_id
            )
    left join tr_admission_test_schedule_criteria
        on tr_admission_test_schedule_criteria.admission_test_schedule_id = tr_admission_test_schedule.admission_test_schedule_id
    left join tr_admission_test_result
        on tr_admission_test_result.admission_test_schedule_participant_id = tr_admission_test_schedule_participant.admission_test_schedule_participant_id
    left join tr_admission_payment
        on tr_admission_payment.candidate_id = ms_admission_candidate.candidate_id
    left join ms_admission_parents
        on ms_admission_parents.candidate_id = ms_admission_candidate.candidate_id
    left join ms_admission_sibling
        on ms_admission_sibling.candidate_id = ms_admission_candidate.candidate_id
    left join ms_religion
        on ms_religion.religion_id = ms_admission_candidate.religion_id
where tr_admission_batch.deleted_at is null
    and tr_admission_batch.academic_year = '2021-2022'
    and ms_admission_candidate.deleted_at is null
    and tr_admission_candidate_status.deleted_at is null
    and ms_school_level.deleted_at is null
    and ms_school_location.deleted_at is null
    and ms_year_level.deleted_at is null
    and t_user.deleted_at is null
    and ms_religion.deleted_at is null
    and tr_admission_batch_step_relation.deleted_at is null
    and ms_admission_step.deleted_at is null
    and ms_student.deleted_at is null
    and tr_admission_test_schedule_participant.deleted_at is null
    and tr_admission_test_schedule.deleted_at is null
    and tr_admission_test_schedule_criteria.deleted_at is null
    and tr_admission_test_result.deleted_at is null
    and tr_admission_payment.deleted_at is null
    and ms_admission_parents.deleted_at is null
    and ms_admission_sibling.deleted_at is null
group by
    tr_admission_batch.admission_batch_id,
    ms_admission_candidate.candidate_id,
    tr_admission_candidate_status.admission_candidate_status_id,
    tr_admission_payment.admission_payment_id,
    ms_student.student_id,
    t_user.user_id,
    ms_school_location.school_location_id,
    ms_school_level.school_level_id,
    ms_year_level.description,
    ms_religion.agama