# Vaccinations: checking congruence booking vs appointments vs orders

## Original question:

Can you please confirm what the relationship now and potential syncing is between:
    line_item.quantity
    booking.patients
    appointment_patients

## How the checking was done:

SELECT
	a.id,
	a.created_at,
	COUNT(b.id) AS count_bid,
	COUNT(ap.id) AS count_ap,
	SUM(li.quantity) AS sum_li_qty,
	SUM(bli.quantity) AS sum_bli_qty,
	COUNT(li.id) AS li_count,
	COUNT(b.id) AS boo_count,
	COUNT(pat.id) AS pat_count,
	array_agg(p.slug),
	MAX(b.v2_line_item_id) AS li_id,
	MAX(li.id) AS li2_id,
	MAX(b.type),
	MAX(bo.customer_type) AS order_from_booking_cust,
	MAX(o.customer_type) AS order_from_appointment_cust
FROM appointments a 
LEFT JOIN v2_orders o ON a.v2_order_id = o.id
LEFT JOIN v2_line_items li ON li.order_id = o.id
LEFT JOIN v2_products p ON li.product_id = p.id
LEFT JOIN appointments_patients ap  ON a.id = ap.appointment_id
LEFT JOIN bookings b ON b.id = a.booking_id
LEFT JOIN pat_links pli ON pli.pat_linkable_type = 'BookingBase' AND b.id = pli.pat_linkable_id
LEFT JOIN v2_line_items bli ON b.v2_line_item_id = bli.id
LEFT JOIN v2_orders bo ON bli.order_id = bo.id
LEFT JOIN persons pat ON pli.patient_id = pat.id AND pat.type = 'Patient'
WHERE p.id IN (16,7,48)
GROUP BY a.id

## line_item.qty <----> qty of appointment_patients -> quite bad

For 2703 investigated appointments 100% of them have these numbers in sync. So in that front I'd say it's pretty good

## line_item.qty <----> qty of booking.patients -> quite bad

There is no sync between these two. In majority of cases, Org bookings have no PatLinks with the Patients whatsoever.
In some cases, OrgBookings have 1 Patient.

Syncing pat_links with OrgBookings was not specified during planning of Vacc improvements '22.

That matter must be looked into in a greater detail.

## OrgBookings and their Appointments -> problem of doubled V2Orders

By far the most important issue I discovered is the problem of doubled orders.

#### Here's how it SHOULD work:

1. Organizational Booking for vaccinations is created
2. (V2::Order gets created right at this moment)
(... then, somewhat later)
3. folks create Appointments for this booking, one by one
4. ... and each Appointment SHOULD be assigned to the V2::Order created in #2

Well. **It turns out it's not the case and new Orders are being created instead**. What's also bad is, that these excessive orders are created not as organizational but as private.

2.7.4 :014 > a = Appointment.find(20118)
  Appointment Load (0.4ms)  SELECT "appointments".* FROM "appointments" WHERE "appointments"."deleted_at" IS NULL AND "appointments"."id" = $1 LIMIT $2  [["id", 20118], ["LIMIT", 1]]
 => #<Appointment id: 20118, service_center_id: nil, user_id: 50065, appointment_creator_type: "User", appointment_creator_id: 50065, date: "2022-09-29", timeslot: 690, duration: 0, deleted_at: nil, created_at: "2022-09-22 14:52:50.842367000 +0200",... 
2.7.4 :015 > a.v2_order.id
  V2::Order Load (0.4ms)  SELECT "v2_orders".* FROM "v2_orders" WHERE "v2_orders"."id" = $1 LIMIT $2  [["id", 78507], ["LIMIT", 1]]
 => 78507 
2.7.4 :016 > a.booking.v2_order.id
  BookingBase Load (0.4ms)  SELECT "bookings".* FROM "bookings" WHERE "bookings"."id" = $1 LIMIT $2  [["id", 129613], ["LIMIT", 1]]
  V2::LineItem Load (0.2ms)  SELECT "v2_line_items".* FROM "v2_line_items" WHERE "v2_line_items"."id" = $1 LIMIT $2  [["id", 78564], ["LIMIT", 1]]
  V2::Order Load (0.2ms)  SELECT "v2_orders".* FROM "v2_orders" WHERE "v2_orders"."id" = $1 LIMIT $2  [["id", 78442], ["LIMIT", 1]]
 => 78442 
2.7.4 :017 > a.booking.v2_order.customer
  Organization Load (0.2ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", 7], ["LIMIT", 1]]
 => #<Organization id: 7, name: "Stiftelsen Bærum Montessoriskole", org_nr: "976320409", created_at: "2020-11-30 13:53:49.598542000 +0100", updated_at: "2022-09-21 12:44:23.326708000 +0200", contact_person_id: 143797, billing_method: "email", billing_reference: "Vaksine", billing_email: "[e-mail]", billing_due_by: 30> 
2.7.4 :018 > a.v2_order.customer
  User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 50065], ["LIMIT", 1]]
 => #<User id: 50065, email: "[e-mail]", created_at: "2022-09-22 14:51:52.175954000 +0200", updated_at: "2022-09-22 14:52:50.693814000 +0200", phone_number: "[phone]", otp_secret: "TSMPSS2JPB3WTGAIIN5QFNXIPXMB3RLG", name: "Kathrine Krüger", address: nil, provider: nil, uid: nil, prescription_lookup_consent: nil, patient_id: 143843, deleted_at: nil, phone_number_old: nil, marketing_consent_at: nil, from_patient_at: nil, locale: "nb", national_id: [FILTERED]> 

#### Remedy:
- prevent vacc appoinments from creating orders again if they belong to booking having order (which should be the case for 100% of them)
- create backfill that heals existing data:
- deletes excessive orders
- makes sure compensations are counted properly
- re-associates vacc appointments to orders associated with their vacc bookings
- investigate possible other implications of such action