Patient Care Plan

/*
    This model provides a business-ready, denormalized view of patient care plan data
    for all enrollments (current and historical). Each row represents one patient care plan,
    enriched with clinic/client/patient identifiers from the associated patient care record
    and plan metadata from the associated care plan. Use the is_current flag to filter to
    active enrollments (matching /ext/v2/patient-care-plans/current/) or to compare all
    plans against the /patient-care-plans/ API endpoint.
*/

"
    )
}}

with patient_care_plans as (
    select * from {{ ref('care_plan_patient_care_plan') }}
)

, patient_cares as (
    select * from {{ ref('care_plan_patient_care') }}
)

, care_plans as (
    select * from {{ ref('care_plan') }}
)

, users as (
    select * from {{ ref('user') }}
)

, care_patient_invoice_items as (
    select * from {{ ref('care_plan_care_patient_invoice_item') }}
    where is_deleted = false
)

, invoices as (
    select * from {{ ref('invoice') }}
)

, invoice_items as (
    select * from {{ ref('invoice_item') }}
)

-- Sum all invoice item amounts per patient care plan to compute amount invoiced to date
, invoice_items_agg as (
    select
        patient_care_plan_id
        , max(last_updated) as invoice_items_last_updated
        , coalesce(sum(amount), 0) as total_invoiced
    from care_patient_invoice_items
    group by patient_care_plan_id
)

-- Care patient invoice items joined to their invoice item and invoice,
-- with billing_status computed following the API's priority logic
, care_invoice_items_with_invoice as (
    select
        cpii.patient_care_plan_id
        , cpii.invoice_item_id
        , cpii.kind
        , cpii.amount
        , invoices.invoice_id
        , case
            when invoices.marked_as_collections_at is not null then 'COLLECTIONS'
            when invoices.marked_not_rendered_at is not null then 'NOT_RENDERED'
            when invoices.status in (
                'IN_PROGRESS', 'READY_FOR_PAYMENT', 'IMPORTED'
            ) then invoices.status
            when invoices.amount_paid >= invoices.net_total then 'PAID'
            else 'PAYMENT_DUE'
        end as billing_status
        , greatest(invoices.last_updated, invoice_items.last_updated) as invoice_last_updated
    from care_patient_invoice_items as cpii
    left join invoice_items
        on cpii.invoice_item_id = invoice_items.id
    left join invoices
        on invoice_items.invoice_id = invoices.id
    where
        cpii.invoice_item_id is not null
)

-- Aggregate care plan invoice items per (patient_care_plan_id, invoice_id)
, invoice_items_by_invoice as (
    select
        patient_care_plan_id
        , invoice_id
        , billing_status
        , max(invoice_last_updated) as invoice_last_updated
        , array_agg(
            object_construct(
                'invoice_item_id', invoice_item_id
                , 'kind', kind
                , 'amount', amount
            )
        ) as invoice_items
    from care_invoice_items_with_invoice
    group by
        patient_care_plan_id
        , invoice_id
        , billing_status
)

-- Aggregate invoice history per patient_care_plan_id
, invoice_history_agg as (
    select
        patient_care_plan_id
        , array_agg(
            object_construct(
                'invoice_id', invoice_id
                , 'billing_status', billing_status
                , 'invoice_items', invoice_items
            )
        ) as invoice_history
        , max(invoice_last_updated) as invoice_history_last_updated
    from invoice_items_by_invoice
    group by patient_care_plan_id
)

select
    patient_care_plans.id as patient_care_plan_id
    , patient_care_plans.created
    , patient_care_plans.modified
    , patient_care_plans.is_deleted
    , patient_cares.patient_id as clinic_patient_id
    , patient_cares.billing_clinic_id as clinic_id
    , patient_cares.billing_client_id as clinic_client_id
    , patient_care_plans.care_plan_id
    , patient_care_plans.start_date
    , patient_care_plans.end_date
    , patient_care_plans.deactivated_at
    , patient_care_plans.status
    , patient_care_plans.core_plan_id
    , coalesce(patient_care_plans.next_plan_id, care_plans.next_plan_id) as next_plan_id
    , patient_care_plans.should_renew
    , patient_care_plans.plan_cost
    , patient_care_plans.enrollment_fee
    , care_plans.name as care_plan_name
    , care_plans.kind as care_plan_kind
    , patient_care_plans.deactivated_by_user_id
    , users.full_name as deactivated_by_user_name
    , patient_care_plans.next_payment_date
    , round(
        patient_care_plans.plan_cost
        + patient_care_plans.enrollment_fee
        - coalesce(invoice_items_agg.total_invoiced, 0)
        , 2
    )::float as remaining_balance
    , patient_care_plans.is_billed_monthly
    , patient_care_plans.monthly_fee as monthly_cost
    , coalesce(
        invoice_history_agg.invoice_history, parse_json('[]')
    ) as invoice_history
    , case
        when patient_care_plans.is_deleted = true then false
        else patient_care_plans.is_current
    end as is_current
    , greatest(
        coalesce(patient_care_plans.last_updated, '1900-01-01')
        , coalesce(patient_cares.last_updated, '1900-01-01')
        , coalesce(care_plans.last_updated, '1900-01-01')
        , coalesce(invoice_history_agg.invoice_history_last_updated, '1900-01-01')
        , coalesce(users.last_updated, '1900-01-01')
        , coalesce(invoice_items_agg.invoice_items_last_updated, '1900-01-01')
    ) as last_updated
    , patient_care_plans.vetcove_corporate_id
from patient_care_plans
left join patient_cares
    on patient_care_plans.patient_care_id = patient_cares.id
left join care_plans
    on patient_care_plans.care_plan_id = care_plans.id
left join invoice_items_agg
    on patient_care_plans.id = invoice_items_agg.patient_care_plan_id
left join invoice_history_agg
    on patient_care_plans.id = invoice_history_agg.patient_care_plan_id
left join users
    on patient_care_plans.deactivated_by_user_id = users.id