attendance overview

WITH
  date_series AS (
      SELECT generate_series(
          date_trunc('month', TO_DATE('2025-05', 'YYYY-MM'))::date,
          (date_trunc('month', TO_DATE('2025-05', 'YYYY-MM')) + interval '1 month - 1 day')::date,
          interval '1 day'
      )::date AS date
  )
,

  calendar_info AS (
      SELECT
          ds.date,
          date_trunc('week', ds.date)::date AS week_start,
          (date_trunc('week', ds.date) + interval '6 days')::date AS week_end,
          date_trunc('month', ds.date)::date AS month_start,
          (date_trunc('month', ds.date) + interval '1 month - 1 day')::date AS month_end,
          CASE
              WHEN ds.date < MIN(CASE WHEN EXTRACT(DOW FROM ds.date) = 1 THEN ds.date END) OVER () THEN 1
              ELSE FLOOR((ds.date - MIN(CASE WHEN EXTRACT(DOW FROM ds.date) = 1 THEN ds.date END) OVER ()) / 7) + 2
          END AS week_of_month
      FROM date_series ds
  )
,

  attendance_logs AS (
      SELECT
          eal.date,
          eal.employee_id,
          MIN(eal.in_time AT TIME ZONE 'UTC') AS min_in_time,
          MAX(eal.out_time AT TIME ZONE 'UTC') AS max_out_time,
          MAX(eal.in_time AT TIME ZONE 'UTC') AS max_in_time,
          BOOL_OR(eal.in_time IS NULL OR eal.out_time IS NULL) AS has_incomplete_logs,
          json_agg(
              json_build_object(
                  'in_time', eal.in_time,
                  'out_time', eal.out_time,
                  'status_type','present'
              ) ORDER BY eal.in_time ASC
          ) AS log_entries
      FROM employee_attendance_logs eal
      WHERE eal.employee_id = 'bf81eae2-8f92-4e51-86e0-90a0dff83964'
        AND eal.is_deleted = false
      GROUP BY eal.date, eal.employee_id
  )
,
      leave_info AS (
        SELECT
          lrt.transaction_date,
          json_agg(
            json_build_object(
              'leave_type', lt.leave_type,
              'change_duration', lrt.change_duration
            )
          )::json AS leave_details
        FROM "leave_requests" lr
        JOIN calendar_info ci ON ci.date = date_trunc('month', TO_DATE('2025-05-01', 'YYYY-MM'))::date
        INNER JOIN "leave_request_transaction" lrt
          ON lr.id = lrt.leave_request_id
          AND lrt.transaction_date BETWEEN ci.month_start AND ci.month_end
        LEFT JOIN "leave_plan_leave_types" lplt
          ON lr.leave_plan_leave_type_id = lplt.id
        LEFT JOIN "leave_types" lt
          ON lplt.leave_type_id = lt.id
        WHERE lr.employee_id = 'bf81eae2-8f92-4e51-86e0-90a0dff83964'
          AND ((lr.from_date BETWEEN ci.month_start AND ci.month_end)
          OR (lr.to_date BETWEEN ci.month_start AND ci.month_end))
          AND lr.status = 'approved'
        GROUP BY lrt.transaction_date
      ),
      attendance_details AS (
        SELECT
          ds.date,
          esh.shift_type_id,
          sta.min_half_day_hours,
          sta.min_full_day_hours,
          -- Work hours
          CASE
              WHEN aa.date IS NOT NULL 
                  AND aa.has_incomplete_logs = false
                  AND sta.hours_calculation_method = 'clock_in_clock_out' THEN
                  COALESCE(
                      (
                          SELECT TO_CHAR(
                              GREATEST(
                                  SUM(
                                      CASE
                                          WHEN eal.out_time < eal.in_time THEN
                                              (eal.out_time + INTERVAL '24 hours' - eal.in_time)::interval
                                          ELSE
                                              (eal.out_time - eal.in_time)::interval
                                      END
                                  ),
                                  INTERVAL '00:00:00'
                              ),
                              'HH24:MI:SS'
                          )
                          FROM "employee_attendance_logs" eal
                          WHERE eal.employee_id = e.id AND eal.is_deleted = false
                          AND eal.date = ds.date
                      ),
                      '00:00:00'
                  )
              WHEN aa.date IS NOT NULL 
                      AND sta.hours_calculation_method = 'first_in_last_out' THEN
                  COALESCE(
                      (
                          SELECT TO_CHAR(
                              GREATEST(
                                  SUM(
                                      CASE
                                          WHEN eal.out_time < eal.in_time THEN
                                              (eal.out_time + INTERVAL '24 hours' - eal.in_time)::interval
                                          ELSE
                                              (eal.out_time - eal.in_time)::interval
                                      END
                                  ),
                                  INTERVAL '00:00:00'
                              ),
                              'HH24:MI:SS'
                          )
                          FROM "employee_attendance_logs" eal
                          WHERE eal.employee_id = e.id AND eal.is_deleted = false
                          AND eal.date = ds.date
                      ),
                      '00:00:00'
                  )
              ELSE '00:00:00'
          END AS work_hours,
          CASE
            WHEN ds.date < esh.effective_from IS NULL THEN ARRAY[]::text[]
            WHEN ds.date >= '2025-05-01'::date
              AND hd.holiday_date IS NULL
              AND wo.day_of_week IS NULL
              AND aa.date IS NULL THEN ARRAY[]::text[]
            WHEN ds.date >= '2025-05-01'::date AND aa.date IS NULL THEN
              CASE
                WHEN wo.day_of_week = LOWER(TO_CHAR(ds.date, 'FMDay'))::day_of_week_enum OR hd.holiday_date IS NOT NULL THEN
                  CASE
                    WHEN CASE
                      WHEN ci.week_of_month = 1 THEN wo.first
                      WHEN ci.week_of_month = 2 THEN wo.second
                      WHEN ci.week_of_month = 3 THEN wo.third
                      WHEN ci.week_of_month = 4 THEN wo.fourth
                      WHEN ci.week_of_month = 5 THEN wo.fifth
                      ELSE wo.last
                    END = 'full_day' THEN ARRAY['full_day_weekly_off']
                  WHEN hd.holiday_date IS NOT NULL THEN ARRAY['holiday']
                    WHEN CASE
                      WHEN ci.week_of_month = 1 THEN wo.first
                      WHEN ci.week_of_month = 2 THEN wo.second
                      WHEN ci.week_of_month = 3 THEN wo.third
                      WHEN ci.week_of_month = 4 THEN wo.fourth
                      WHEN ci.week_of_month = 5 THEN wo.fifth
                      ELSE wo.last
                    END = 'first_half' THEN ARRAY['first_half_weekly_off']
                    WHEN CASE
                      WHEN ci.week_of_month = 1 THEN wo.first
                      WHEN ci.week_of_month = 2 THEN wo.second
                      WHEN ci.week_of_month = 3 THEN wo.third
                      WHEN ci.week_of_month = 4 THEN wo.fourth
                      WHEN ci.week_of_month = 5 THEN wo.fifth
                      ELSE wo.last
                    END = 'second_half' THEN ARRAY['second_half_weekly_off']
                  ELSE ARRAY[]::TEXT[]
                END
              ELSE ARRAY[]::TEXT[]
            END
            WHEN aa.date IS NOT NULL THEN
              CASE
                WHEN wo.day_of_week = LOWER(TO_CHAR(ds.date, 'FMDay'))::day_of_week_enum OR hd.holiday_date IS NOT NULL THEN
                  CASE
                    WHEN CASE
                      WHEN ci.week_of_month = 1 THEN wo.first
                      WHEN ci.week_of_month = 2 THEN wo.second
                      WHEN ci.week_of_month = 3 THEN wo.third
                      WHEN ci.week_of_month = 4 THEN wo.fourth
                      WHEN ci.week_of_month = 5 THEN wo.fifth
                      ELSE wo.last
                    END = 'full_day' THEN ARRAY['full_day_weekly_off', 'present']
                  WHEN hd.holiday_date IS NOT NULL THEN ARRAY['holiday', 'present']
                    WHEN CASE
                        WHEN ci.week_of_month = 1 THEN wo.first
                      WHEN ci.week_of_month = 2 THEN wo.second
                      WHEN ci.week_of_month = 3 THEN wo.third
                      WHEN ci.week_of_month = 4 THEN wo.fourth
                      WHEN ci.week_of_month = 5 THEN wo.fifth
                      ELSE wo.last
                    END = 'first_half' THEN ARRAY['first_half_weekly_off', 'present']
                    WHEN CASE
                      WHEN ci.week_of_month = 1 THEN wo.first
                      WHEN ci.week_of_month = 2 THEN wo.second
                      WHEN ci.week_of_month = 3 THEN wo.third
                      WHEN ci.week_of_month = 4 THEN wo.fourth
                      WHEN ci.week_of_month = 5 THEN wo.fifth
                      ELSE wo.last
                    END = 'second_half' THEN ARRAY['second_half_weekly_off', 'present']
                  ELSE ARRAY['present']
                END
              ELSE ARRAY['present']
            END
            ELSE
              CASE
                WHEN wo.day_of_week = LOWER(TO_CHAR(ds.date, 'FMDay'))::day_of_week_enum OR hd.holiday_date IS NOT NULL THEN
                  CASE
                    WHEN CASE
                      WHEN ci.week_of_month = 1 THEN wo.first
                      WHEN ci.week_of_month = 2 THEN wo.second
                      WHEN ci.week_of_month = 3 THEN wo.third
                      WHEN ci.week_of_month = 4 THEN wo.fourth
                      WHEN ci.week_of_month = 5 THEN wo.fifth
                      ELSE wo.last
                    END = 'full_day' THEN ARRAY['full_day_weekly_off']
                  WHEN hd.holiday_date IS NOT NULL THEN ARRAY['holiday']
                    WHEN CASE
                      WHEN ci.week_of_month = 1 THEN wo.first
                      WHEN ci.week_of_month = 2 THEN wo.second
                      WHEN ci.week_of_month = 3 THEN wo.third
                      WHEN ci.week_of_month = 4 THEN wo.fourth
                      WHEN ci.week_of_month = 5 THEN wo.fifth
                      ELSE wo.last
                    END = 'first_half' THEN ARRAY['first_half_weekly_off', 'absent']
                    WHEN CASE
                      WHEN ci.week_of_month = 1 THEN wo.first
                      WHEN ci.week_of_month = 2 THEN wo.second
                      WHEN ci.week_of_month = 3 THEN wo.third
                      WHEN ci.week_of_month = 4 THEN wo.fourth
                      WHEN ci.week_of_month = 5 THEN wo.fifth
                      ELSE wo.last
                    END = 'second_half' THEN ARRAY['second_half_weekly_off', 'absent']
                    ELSE ARRAY['absent']
                  END
                ELSE ARRAY['absent']
              END
          END AS attendance_status,
          li.leave_details
        FROM date_series ds
        LEFT JOIN "employees" e ON e.id = 'bf81eae2-8f92-4e51-86e0-90a0dff83964'
        LEFT JOIN "employee_shift_type_history" esh ON
          esh.employee_id = e.id AND
          esh.effective_from <= ds.date AND
          (esh.effective_to IS NULL OR esh.effective_to >= ds.date)
        LEFT JOIN "shift_type_applicable_criteria" sta ON 
            sta.shift_type_id = esh.shift_type_id AND 
            sta.is_deleted = false
        LEFT JOIN "employee_holiday_group_history" ehg ON
          ehg.employee_id = e.id AND
          ehg.effective_from <= ds.date AND
          (ehg.effective_to IS NULL OR ehg.effective_to >= ds.date)
        LEFT JOIN "holiday_groups" hg ON hg.id = ehg.holiday_group_id
        LEFT JOIN "holiday_group_holidays" hd ON
          hd.holiday_group_id = hg.id AND
          hd.holiday_date = ds.date AND
          hd.is_deleted = false
        LEFT JOIN "employee_weekly_off_type_history" ewo ON
          ewo.employee_id = e.id AND
          ewo.effective_from <= ds.date AND
          (ewo.effective_to IS NULL OR ewo.effective_to >= ds.date)
        LEFT JOIN "weekly_off_type_config" wo ON
          wo.weekly_off_type_id = ewo.weekly_off_type_id AND
          wo.day_of_week = LOWER(TO_CHAR(ds.date, 'FMDay'))::day_of_week_enum AND
          wo.is_deleted = false
        LEFT JOIN calendar_info ci ON ci.date = ds.date
        LEFT JOIN attendance_logs aa ON aa.date = ds.date
        LEFT JOIN leave_info li ON li.transaction_date = ds.date
      ),
			leave_calculations AS (
        SELECT
          ad.date,
          ad.attendance_status,
          COALESCE(
            (SELECT SUM(
            	CASE WHEN 
            		(
            			leave_detail->>'leave_type') != 'unpaid' 
            			AND NOT (ad.attendance_status @> ARRAY['present']) 
            				THEN (leave_detail->>'change_duration')::NUMERIC
            				ELSE 0
            			END
            		)
              FROM json_array_elements(COALESCE(ad.leave_details, '[]'::json)) AS leave_detail),
            0
          ) AS paid_leave_duration,
          COALESCE(
            (SELECT SUM(
            	CASE WHEN 
            		(
		          		leave_detail->>'leave_type') = 'unpaid' 
		          		AND NOT (ad.attendance_status @> ARRAY['present']) 
		          			THEN (leave_detail->>'change_duration')::numeric 
		          			ELSE 0
		          		END
            		)
              FROM json_array_elements(COALESCE(ad.leave_details, '[]'::json)) AS leave_detail),
            0
          ) AS unpaid_leave_duration
        FROM attendance_details ad
      ),
      day_status_on_logs AS (
      	SELECT
      		ad.date,
      		CASE 
      			WHEN ad.work_hours::interval >= ad.min_full_day_hours
      				THEN 'present'
      			WHEN ad.work_hours::interval >= ad.min_half_day_hours
      				THEN 'half_day'
      			ELSE 'absent'
      		END AS day_status
      	FROM date_series ds
      	LEFT JOIN attendance_details ad ON ds.date = ad.date
      )
--      final_attendance_statuses_calculation AS (
      	SELECT
      		ad.date,
      		CASE WHEN ad.attendance_status @> ARRAY['present'] THEN 
      			CASE 
	      			WHEN ad.attendance_status = ARRAY['present'] THEN
	      				CASE 
	      					WHEN dsl.day_status = 'half_day' THEN ARRAY['first_half_weekly_off', 'present']
	      					WHEN dsl.day_status = 'absent' THEN ARRAY['absent']
	      				ELSE ad.attendance_status
	      				END
      				WHEN ad.attendance_status = ARRAY['holiday', 'present'] THEN
      					CASE
      						WHEN dsl.day_status = 'half_day' THEN ARRAY['half_holiday_off', 'present']
      						WHEN dsl.day_status = 'absent' THEN ARRAY['holiday']
      					ELSE ad.attendance_status
      					END
      				WHEN ad.attendance_status = ARRAY['full_day_weekly_off', 'present'] THEN
      					CASE 
      						WHEN dsl.day_status = 'half_day' THEN ARRAY['first_half_weekly_off', 'present']
      						WHEN dsl.day_status = 'absent' THEN ARRAY['full_day_weekly_off']
      					ELSE ad.attendance_status
      					END
      				WHEN ad.attendance_status = ARRAY['first_half_weekly_off', 'present'] THEN
      					CASE
      						WHEN dsl.day_status = 'absent' THEN ARRAY['first_half_weekly_off', 'absent']
      						ELSE ad.attendance_status
      					END
      				WHEN ad.attendance_status = ARRAY['second_half_weekly_off', 'present'] THEN
      					CASE 
	      					WHEN dsl.day_status = 'absent' THEN ARRAY['second_half_weekly_off', 'absent']
      						ELSE ad.attendance_status
      					END
      			ELSE ad.attendance_status
      			END
      		ELSE ad.attendance_status
      		END AS final_attendance_status
      	FROM date_series ds
      	LEFT JOIN attendance_details ad ON ds.date = ad.date
      	LEFT JOIN day_status_on_logs dsl ON ds.date = dsl.date