with epmTable as (select epm.member_id,
                         event_plan_id,
                         group_id,
                         data::jsonb ->> 'date'      as                                   date,
                         data::jsonb ->> 'isPresent' as                                   present,
                         case when data::jsonb ->> 'isPresent' = 'true' then 1 else 0 end present_absent

                  from (select epmember.attendance,
                               epmember.member_id,
                               epmember.group_id,
                               epmember.event_plan_id,
                               jsonb_array_elements_text(epmember.attendance::jsonb) as data
                        from event_plan_member epmember
                        where is_json(attendance) = true
                          and attendance <> '[]'
                       ) as epm),
     attTable as (
         select epmTable.event_plan_id,
                epmTable.group_id,
                epmTable.date::date,
                epmTable.member_id,
                sum(present_absent) as attendSum
         from epmTable
         group by epmTable.event_plan_id, epmTable.date, epmTable.member_id, epmTable.present, epmTable.group_id),
     eventTable1 as (
         select es.name                                     as session_name,
                epmAttend.member_id,
                epmAttend.group_id,
                epmAttend.date,
                epmAttend.attendSum,
                ep.id                                       as plan_id,
                ep.name                                     as plan_name,
                ep.from_date::date                          as plan_from_date,
                ep.to_date::date                            as plan_to_date,
                (ep.to_date::date - ep.from_date::date) + 1 as total_plan_days,
                e.name                                      as eventName,
                et.name                                     as eventType,
                a.name                                      as activity,
                at.name                                     as activityType


         from attTable epmAttend
                  join event_plan ep on ep.id = epmAttend.event_plan_id
                  join event_session_for_user esfu on esfu.id = ep.event_session_for_user_id
                  join event_session es on es.id = esfu.event_session_id
                  join event e on e.id = es.event_id
                  join event_type et on et.id = e.event_type_id
                  join activity a on a.id = e.activity_id
                  join activity_type at on at.id = a.activity_type_id

         where epmAttend.member_id like '%BD%'
           and at.name like '%19%'
           and ep.is_executed = true),
     groupTable as (select cp."Upazila"     as Group_upazila,
                           cp."Union"       as group_union,
                           cp."Para"        as group_para,
                           pp.purpose,
                           pp.componenet,
                           pp.participent_domain,
                           pgt.name         as Group_type,
                           pg.id            as group_id,
                           pg.name          as Group_name,
                           pg.group_formation_date::date,
                           count(member_id) as current_total_of_register_participants
                    from participant_group_member pgm
                             join participant_group pg on pgm.group_id = pg.id
                             join participant_group_type pgt on pgt.id = pg.participant_group_type_id
                             join program_pivoted pp on pp.program_id = pgt.component_id
                             join catchment_pivoted cp on cp.catchment_id = pg.catchment_id
                    where pgm.member_id like '%BD%'
                      and pg.status = 'Approved'
                    group by pg.id, pg.name, pp.purpose, pp.componenet, pp.participent_domain, pgt.name,
                             pg.group_formation_date, cp."Upazila", cp."Union", cp."Para")
select gt1.Group_upazila,
       gt1.group_union,
       gt1.group_para,
       gt1.purpose,
       gt1.componenet,
       gt1.participent_domain,
       gt1.Group_type,
       gt1.group_id,
       gt1.Group_name,
       gt1.current_total_of_register_participants,
       et1.eventType,
       et1.eventName,
       et1.session_name,
       et1.plan_id,
       et1.plan_to_date,
       et1.plan_from_date,
       et1.total_plan_days,
       min(et1.date) as execution_date,
       sum(et1.attendSum) as total_participant_attend
from eventTable1 et1
         join groupTable gt1 on gt1.group_id = et1.group_id
where gt1.group_id = 'b9719c51-e565-19da-8190-0ec55fc5f0c3'
group by et1.plan_id, et1.eventType, gt1.group_id, gt1.Group_name, gt1.current_total_of_register_participants,
         gt1.Group_type, et1.eventName, et1.session_name, et1.plan_to_date, et1.plan_from_date, et1.total_plan_days,
         gt1.Group_upazila, gt1.group_union, gt1.group_para, gt1.purpose,
         gt1.componenet,
         gt1.participent_domain