# Financial Refactor
[TOC]

## Objectives

- remodelling financial internals to implement e-commerce approach with
- Orders
- Carts
- LineItems
- cleanup & simplify financial flow
- ease the monthly closing with
- introduction of price models
- introduction of compensation models
- enable manual and automatic corrections to compensations

## ToDo List
- [ ] plan financial refactor and new data model
- [ ] Implement interactors (service objects) for creating/updating Orders
- [ ] Implement interactors (service objects) for creating/updating Line Items
- [ ] Adjust/refactor booking interactors (service objects) to use order-based logic (booking creates order)

## Current DB implementation

CREATE TABLE public.line_items (
    id bigint NOT NULL,
    product_id bigint NOT NULL,
    cart_id bigint,
    discount integer DEFAULT 0 NOT NULL, //JK: How it is supposed to work?
    quantity integer DEFAULT 1 NOT NULL, //JK: mby witout default?
    created_at timestamp(6) without time zone NOT NULL,
    updated_at timestamp(6) without time zone NOT NULL,
    order_id bigint,
    product_extension_type character varying, //JK: not sure what that is
    product_extension_id bigint
);


CREATE TABLE public.orders (
    id bigint NOT NULL,
    user_id integer,
    name character varying NOT NULL,
    address character varying, //addr is for me a jsonb candidate
    zip character varying,
    city character varying,
    created_at timestamp(6) without time zone NOT NULL,
    updated_at timestamp(6) without time zone NOT NULL,
    stripe_charge_id character varying,
    marketing_consent_at timestamp without time zone, //JK: We prob. dont wanna keep it here
    save_user_consent_at timestamp without time zone, //JK: We prob. dont wanna keep it here
    email character varying NOT NULL,
    phone_number character varying NOT NULL,
    coupon_code character varying,
    shipping_state integer DEFAULT 0 NOT NULL,
    secret character varying,
    additional_info text,
    order_state integer DEFAULT 0 NOT NULL,
    product_terms_accepted_at timestamp without time zone,
    phone_number_old character varying
);

CREATE TABLE public.carts (
    id bigint NOT NULL,
    user_id bigint,
    coupon_code character varying,
    created_at timestamp(6) without time zone NOT NULL,
    updated_at timestamp(6) without time zone NOT NULL
);

## Relations between models

Schema of the relations [here](https://miro.com/app/board/o9J_kvTW3Ss=/?moveToWidget=3074457360661514887&cot=14)

## DB Modification

- ER diagram available [here](https://dbdiagram.io/d/60e6c8f77e498c3bb3ed7f99)

### Tables to be deleted
- `products`

### Tables to be renamed
- `services` renamed to `products`
- `sp_service_links` renamed to `sp_product_links`
- `extra_services`renamed to `manual_settlement_entries` ?
- `compensation_models` => `compensation_type`
- `sp_compensation_links` => `sp_compensation_type_links`

### Tables to be modified

Table "organizations" {
  "id" varchar
  "name" string
  "org_nr" string
  "visiting_address => replaced by address table when feature implemented" string 
  "visiting_zip" string 
  "mailing_address" string
  "mailing_zip" string
  "created_at" datetime
  "updated_at" datetime
  "contact_person_id (+)" bigint
  "contact_name (-)" string 
  "contact_email (-)" string
}

Table " services renamed  => products" {
  "id" varchar
  "name" string
  "slug" string
  "created_at" datetime
  "updated_at" datetime
  "public_name" string
  "type (+)" string 
  "image_url (+)"  string
  "description (+)"  string 
  "product_category_id(+)" bigint
  "vat (+)" float default 0.00
}

Table "sp_service_links renamed => sp_product_links" {
  "id" varchar
  "service_id => product_id" bigint
  "service_provider_id" bigint
  "created_at" datetime
  "updated_at" datetime
}

Table "orders" {
  "id" varchar
  "user_id" integer
  "created_at" datetime
  "updated_at" datetime
  "shipping_state" integer
  "additional_info" text
  "order_state" integer
  "product_terms_accepted_at (?)" datetime
  "coupon_code (-)" string
  "stripe_charge_id (-)" string
  "secret (-)" string
  "marketing_consent_at (-)" datetime
  "save_user_consent_at (-)" datetime
  "phone_number (-)" string 
  "name (-)" string
  "email (-)" string
}

Table "line_items" {
  "id" varchar
  "order_id" bigint
  "product_id (+)" bigint
  "cart_id" bigint
  "price_model_id (+)" bigint
  "booking_id (+)" bigint
  "coupon_id (+)" bigint
  "parent_id" bigint
  "discount (? private coupons)" integer
  "quantity" integer
  "unit_price (+)" integer
  "net_price (+)" integer
  "gross_price (+)" integer
  "taxes (+)" integer
  "created_at" datetime
  "updated_at" datetime
  "product_extension_type (-)" string
  "product_extension_id (-)" bigint
}

Table "coupons" {
 "id" varchar
  "active" boolean
  "code" string
  "used_count" integer
  "amount" integer
  "created_at" datetime
  "updated_at" datetime
  "zip_whitelist" text
  "two_patients_amount" integer
  "full_price_second_patient" boolean
  "type" string
  "user_id" integer
  "chat_amount" integer
  "description" text
  "receipt_text" string
  "customer_group" string
  "product_id (+)" bigint
  "organization_id (+)" bigint
}

Table "compensation_models => rename compensation_type" {
  "id" varchar
  "name" string
  "slug" string
  "require_paid_settlement (+)" boolean
}

Table "sp_compensation_links => renamed sp_compensation_type_links" {
  "id" bigint
  "compensation_type_id" bigint
  "service_provider_id" bigint
  "effective_from (+)" datetime
  "effective_to (+)" datetime
}

### tables to be created:

Table "prices" {
  "id" varchar
  "product_id" bigint
  "effective_from" datetime
  "effective_to" datetime
  "min_quantity" integer default 1
  "price" integer
  "organization_id" bigint
  "currency" enum default 'NOK'
  "type" pgenum ["unitary", "hourly"]
}

Table "compensations" {
  "id" varchar
  "product_id" bigint
  "service_provider_id" bigint
  "effective_from" datetime
  "effective_to" datetime
  "min_quantity" integer
  "compensation_amount" integer
  "currency" enum default 'NOK'
   "type" pgenum ["unitary", "hourly"]
}

Table "settlement_items" {
  "id" varchar
  "order_id" bigint
  "compensations_id" bigint
  "line_item_id"
  "min_quantity" integer
  "unit_compensation" integer
  "net_compensation" integer
  "applies_for_payout" boolean default true
}



Table "bookings" {
...
"number_of_patients (+)" integer
}

- links between table to be modified
- bookings : `service_id` -> `product_id`

## Price Model
[Database structure](https://docs.google.com/spreadsheets/d/1X6kFMoTkH-Wz9mG45logV6gV-prvcIJXEVBDW6KeVQw/edit#gid=308708792) put in two different stragies of storing tiers/prices. Both result in the same data stored in the database.

**Procedure for determining the product price:**
- find all the possible `prices` for a given product (filter by `effective_from` and `effective_to`)
- for organizations: prioritize organization-specific records over general ones
- split the number of patients you want into tiers (with a one-liner algorithm modeled in the [spreadsheet](https://docs.google.com/spreadsheets/d/1X6kFMoTkH-Wz9mG45logV6gV-prvcIJXEVBDW6KeVQw/edit#gid=688494282)
- for every tier: calculate quantity, fetch the price, multiply, sum up

**Price change procedure (for product/organization):**
- find all the `prices` records corresponding to the given product/organization
- "cancel" them (by setting the `effective_to` field to current time)
- create the new set of records based on data provided by the admin (set `effective_to` to *nil* unless explicitly chosen)
Preferrably, all of that should be done in a single transaction.

**Procedure for adding a tier to a product:**
Manually create a record in `prices` through Rails console/db console

**Validations:**
Every change to the `prices` table should be bounded by validations ensuring:
- there is always a valid price for every service (beware that some records may have the `effective_to` field set!)
- tiers do not overlap/contradict

**immutability**
`prices` records are immutable - you can only cancel it (by setting the `effective_to` to current date) and create a similar new one.
Possible future improvement: we could limit the growing number of records by removing unused ones (not linked to anything)

## Compensation Model
The compensation model is linked to several variables :
- the number of patients
- the type of service (product)
- potentially the type of service provider (employee / provisionnaly paid / independant business owner)
- potentially the number of hours on an order (hourly rate)

Adding the `service_provider_id` column to `compensations` provide the possibility to add general compensation model (fall back) when the `service_provider_id` is not specified and customized compensation model when the `service_provider_id` is specified.

The [Data structure](https://docs.google.com/spreadsheets/d/1X6kFMoTkH-Wz9mG45logV6gV-prvcIJXEVBDW6KeVQw/edit#gid=1514828152) is pretty similar to the price model structure.

**Procedure for determining the compensation amount :**
- find the possible tiers in the `compensations` table for a given product/service_provider/number of patients (filter by `effective_from` and `effective_to`)
- split the number of patients into tiers (as for the `prices`)
- read the compensation_amount for a given tier
- for every tier: calculate quantity, fetch the price, multiply, sum up

In order to maintain flexibility, the admin user will have the ability to create `compensations` for any type of service provider (even those we know are not being compensated through the platform - in this case we tag the records with a `applies_to_payout`: *false*, based on `sp_compensation_type_links`.

## Data model
ER diagram available [here](https://dbdiagram.io/d/60e6c8f77e498c3bb3ed7f99)

### Price

@startuml


class BookingBase {
}

class Order {
}

class LineItem {

}

class Cart {
  
}

class Price {

}

class Product {

}

LineItem "many" -- "1" BookingBase
LineItem "1" -- "1" Price
Order "1" -- "*many" LineItem
Cart "1" -- "*many" LineItem
Price "many" -- "1" Product
LineItem "1" -- "1" LineItem

note top of BookingBase #0F0: (Polymorphic: \n Booking, ChatBooking...)

@enduml

##### Design decisions made:
- `line_items` are *immutable*
- Every `Product` may have many `prices` because of quantity tiers + because of the (optional) binding to an `Organization`. There is always an existing `Price` for every `Product`
- `line_items` inside an order reflect that - a single `Product` results in as many `line_items` as quantity tiers required to cover the desired quantity (number of patients).
- `line_items` created based on quantity tiers are being grouped together using the `parent` field which points to the `line_item` on which the given `line_item` depends. "tier 1" `line_item` is a *parent* to "tier 2" `line_item` and so on.
- A single `line_item` may relate to many `bookings` which are set up to fullfill the service. The `line_item` changes its state to *fullfilled* only when the sum of patients number taken from fullfilled bookings reach the sumed up quantity of the *Line_item group* (the top *parent* `line_item` and all its descendands)
##### Possible future extenstions:
- handle hourly price by:
*- having a type attribute on the price_models table => [unitary/hourly]*
*- having a boolean attributes : `unitary default true`*

### Compensation

@startuml


class BookingBase {
}

class ServiceProvider {
}

class Order {
}

class SettlementItem {

}

class Settlement {

}

class Compensation {

}

class Product {

}

SettlementItem "many" -- "1" LineItem
LineItem "many" -- "1" BookingBase
SettlementItem "many" -- "1" Compensation
Compensation "many" -- "1" Product
Compensation "1" -- "1" ServiceProvider
Order "1" -- "*many" LineItem
LineItem "1" -- "1" LineItem


@enduml

##### Design decisions made:
- Every `Line_item` results in as many `settlement_items` as `service_providers` involved to fullfill it (found through the Line Item `bookings`).
- We link the `settlement_items` to the top parent Line Item only
- The relation between `settlement_items` and `line_items` is optional - there might exist `settlement_items` which we call *corrections* which do not have to relate to any Line Item. They can be edited manually through the *admin panel*.
- A special type of *correction* is the *automatic correction* which is a single record (per `Service Provider` per month) containing the result of the *Automatic Compensation Correction* algorithm.
- Having `settlement_items` could enable manual correction at a lower granularity level. Grouping `line_items` with `settlement_items` on the same table seems error prone, there is an asymetry between tiers in the `price_models` and tiers in the `compensation_models` wich result in the fact that there is not a one to one relation between a `line_item` and a `settlement_item`. Creating a separated `settlement_items` table appears to be the solution.
##### Automatic Compensation Correction
To be detailed based on [the compensation table](https://docs.google.com/presentation/d/1ythZmz6KTPqE86D8Ow-r6fWuO7vDfIfZ5tAabUZqD04/edit#slide=id.gde01c6c4c2_0_23). The main purpose of it is to create a lower- and upper-limit to the monthly settlement per Service Provider.
##### Possible future extensions:
- handle hourly compensation :
potential solutions =>
*- having a type attribute on the compensation_models table => [unitary/hourly]*
*- having a boolean attributes : `unitary default true`*
- is the service provider type really change the compensation that we should provide?
*If it’s a provisionaly paid service provider, there is a compensation model that will vary depending on the number of patient and we will emit a bank transfert. If it’s an employee, we will pay a salary, and if it’s an independent buisness owner (enkeltpersonforetak) we will pay an invoice. It’s seems that the difference between the 3 is :*
*- the payment method*
*- if there is a compensation to be calculated or not (for employee the salary does not depend on the number of patients visited)*

## User / Organization / ContactPerson
- An order should always have an authenticated user
- The coupon code can be used:
- to adjust the price of a service (fk product_id) => current implementation
- to link a user to an organization (fk organization_id).
- How to connect a user to an organization to identify a deal (specific price model) :
- using the coupon code. The coupon code will either have a `product_id`, meaning it is a classic coupon code. In this situation we will create a new line item for this order with a "correction / discount flag ?" on it (quantity: 1, unit_price: discount). On the other hand if the coupon code has an organization_id, we will connect the user to the organization (fk organization_id) and we will apply the prices negociated with that organization (specific `prices` records). Here we assume that the user belongs to one organization.

@startuml


class Product {

}

class CouponCode {
}
class Order {
}

class User {
}

class Organization {
}

class ContactPerson {
}


class User
note left: user is linked\nto an organization\nthanks to the coupon\ncode he enters 





Order "1" -- "1" User
Organization "1" -- "many" User
CouponCode "many" -- "1" Organization
CouponCode "1" -- "1" Product
Organization "1"--"1" ContactPerson
PriceModel "many" -- "1" Organization



@enduml

# Usage
The [sequence diagram](https://miro.com/app/board/o9J_kvTW3Ss=/?moveToWidget=3074457361323515500&cot=14) depicts the order of events required for a full flow from cart/order to settlement.

# Suggestion after discussion with Øyvind
- in order to maintain separation of concerns, line_items should have has little logic as possible into them.
- We had previously imagine a data structure where line_tems records (representing tiers of price model for a product) would be dependent of one another using a parent_id foreign key. This struture would enable modelizing the logic than certain tiers can not exist if the parent tiers does not exists (For instance, in a cart you could not have line_item representing tiers 2-6 patients if line_item representing tiers : 1 patient, doesn't exist)
- Another possibility is to have a line_groups table. The line_group will handle the interdependence logic between tiers. It will be in charge of reading the price model, and create the various line items depending on the quantity.Even if an order/cart has only one line_items, we will always create a line_group. This structure also enable to abstract complexity in layers : order deals only with line groups and line groups deal with line_items. We could also apply that same structure to settlement items.
- The relations is schematized [here](https://miro.com/app/board/o9J_kvTW3Ss=/?moveToWidget=3074457361415969095&cot=14)
- The relation between models is represented as follow :

@startuml


class BookingBase {
}

class Order {
}

class LineGroup {

}

class LineItem {

}

class Cart {
  
}

class Price {

}

class Product {

}

LineItem "many" -- "1" LineGroup
SettlementItem "many" -- "1" LineGroup
SettlementItem "many" -- "1" Compensation
LineGroup "1" -- "1" BookingBase
LineItem "1" -- "1" Price
Price "many" -- "1" Product
Compensation "many" -- "1" Product
Order "1" -- "*many" LineGroup
Cart "1" -- "*many" LineGroup




@enduml

##### Help with plantUML, should you need it

https://plantuml.com/sequence-diagram
https://plantuml.com/class-diagram