EXPLAIN ANALYZE
WITH events AS (
    SELECT
        calendar_events.id,
        calendar_events.period_start,
        calendar_events.legal_entity_id,
        encounter_reasons.title_translations -> 'uk' as reason_name,
        calendar_events.patient_id,
        calendar_events.comment,
        calendar_events.employee_id
    FROM
        calendar_events
    LEFT JOIN encounter_reasons ON calendar_events.encounter_reason_id IN (249, 250) AND calendar_events.encounter_reason_id = encounter_reasons.id
    WHERE calendar_events.legal_entity_id = 9045 AND calendar_events.period_start BETWEEN '2024-03-01 09:00:00.000000' AND '2024-03-30 09:00:00.000000'
), employees AS (
    SELECT
        dbo."Employees"."Id" as id,
        dbo."PositionRefs"."_Name" as position,
        dbo."Parties"."LegalEntityId" as legal_entity_id,
        dbo."Parties"."FirstName" as first_name,
        dbo."Parties"."LastName" as last_name,
        dbo."Parties"."SecondName" as second_name,
        CONCAT_WS(' ', dbo."Parties"."LastName", dbo."Parties"."FirstName", dbo."Parties"."SecondName") as full_name
    FROM dbo."Employees"
    JOIN dbo."Parties" ON dbo."Employees"."PartyId" = dbo."Parties"."Id"
    JOIN dbo."PositionRefs" ON dbo."Employees"."PositionRefId" = dbo."PositionRefs"."Id"
    WHERE dbo."Parties"."LegalEntityId" = 9045
), patients AS (
    SELECT
        user_patients.id,
        user_patients.legal_entity_id,
        personalities.first_name,
        personalities.last_name,
        personalities.second_name,
        CONCAT_WS(' ', personalities.last_name, personalities.first_name, personalities.second_name) as full_name
    FROM user_patients
    JOIN personalities ON user_patients.personality_id = personalities.id
    WHERE user_patients.legal_entity_id = 9045
), services AS (
    SELECT
        medical_service_orders.id,
        medical_service_orders.patient_id,
        medical_service_orders.employee_id,
        medical_service_orders.legal_entity_id,
        medical_service_orders.orderable_id,
        medical_service_orders.orderable_type,
        medical_service_services.title_translations -> 'uk' as service_title
    FROM medical_service_orders
    JOIN medical_service_service_orders ON medical_service_service_orders.medical_service_order_id = medical_service_orders.id
    JOIN medical_service_services ON medical_service_services.id = medical_service_service_orders.medical_service_service_id
    WHERE medical_service_orders.legal_entity_id = 9045
)
SELECT
    DISTINCT ON(events.id)
    patients.full_name as "ПІБ пацієнта",
    employees.full_name as "ПІБ лікаря",
    employees.position as "посада лікаря",
    events.period_start as "дата прийому",
    events.reason_name as "тип прийому",
    array_agg(services.service_title) as "послуги",
    events.comment as "коментар"
FROM events
    LEFT JOIN employees ON events.employee_id = employees.id
    LEFT JOIN patients ON events.patient_id = patients.id
    LEFT JOIN services ON services.orderable_id = events.id AND services.orderable_type = 'Ehr::Visit'
GROUP BY events.id, patients.full_name, employees.full_name, employees.position, events.period_start, events.reason_name, events.comment
LIMIT 1