# Flow endpoint sync order affiliate member

## sync order

participant "cron" as cron
participant "everpro-logistic-client" as elc
database "popaket_logistic" as db0
database "everpro_data" as db1
participant "everpro-user" as eu

cron -> elc : hit endpoint 
elc -> db0 : get orders yesterday (join with shipment_orders_cashback)
note left
query :

select 
so.shipment_order_no, 
so.created_by,
lower(los.description) as status_name,
so.status,
l.logistic_name,
lr.rate_name,
lr.rate_code ,
so.shipment_price,
soc.total_cashback as member_total_cashback,
soc.affiliator_total_cashback,
case 
	when coalesce(so.dispatch_at, 0) <> 0 then  to_timestamp(so.dispatch_at)
	else NULL
end as dispatch_at, 
case 
	when coalesce(so.delivered_at , 0) <> 0 then  to_timestamp(so.delivered_at)
	else NULL
end as delivered_at ,
case 
	when coalesce(so.return_at  , 0) <> 0 then  to_timestamp(so.return_at)
	else NULL
end as return_at 
from shipment_orders so 
join shipment_orders_cashback soc on soc.shipment_order_no = so.shipment_order_no 
join logistic_order_status los on los.id = so.status 
join logistics l on l.id = so.logistic_id 
join logistic_rates lr on lr.id = so.rate_id 
where 
(so.dispatch_at  >= extract(epoch from date_trunc('day', current_timestamp) - interval '1' day) and so.dispatch_at  < extract(epoch FROM date_trunc('day', current_timestamp)) ) or 
(so.delivered_at >= extract(epoch from date_trunc('day', current_timestamp) - interval '1' day) and so.delivered_at < extract(epoch FROM date_trunc('day', current_timestamp)) ) or
(so.return_at  >= extract(epoch from date_trunc('day', current_timestamp) - interval '1' day) and so.return_at  < extract(epoch FROM date_trunc('day', current_timestamp)) ) 
end note
db0 --> elc
elc -> elc : collect created_by from order to array
elc -> eu : get affiliator_id via grpc everpro-user
note left
select r.user_id as affiliator_id, ur.user_id as member_id
from user_referral ur 
join referral r on r.entity_id = ur.referral_id 
where ur.user_id IN (userids)
end note
elc -> db1 : upsert to affiliate_orders
elc -> cron : success response

## backfill oktober

participant "cron" as cron
participant "everpro-logistic-client" as elc
database "popaket_logistic" as db0
database "everpro_data" as db1
participant "everpro-user" as eu

cron -> elc : hit endpoint 
elc -> db0 : select shipment_orders where return_at between 1 oktober - 31 oktober dan return_at nya is not null
note left 
query : 

select 
so.shipment_order_no, 
so.created_by,
lower(los.description) as status_name,
so.status,
l.logistic_name,
lr.rate_name,
lr.rate_code ,
so.shipment_price,
so.cashback,
case 
	when coalesce(so.dispatch_at, 0) <> 0 then  to_timestamp(so.dispatch_at)
	else NULL
end as dispatch_at, 
case 
	when coalesce(so.delivered_at , 0) <> 0 then  to_timestamp(so.delivered_at)
	else NULL
end as delivered_at ,
so.delivered_at,
case 
	when coalesce(so.return_at  , 0) <> 0 then  to_timestamp(so.return_at)
	else NULL
end as return_at 
from shipment_orders so 
--join shipment_orders_cashback soc on soc.shipment_order_no = so.shipment_order_no 
join logistic_order_status los on los.id = so.status 
join logistics l on l.id = so.logistic_id 
join logistic_rates lr on lr.id = so.rate_id 
where 
(so.return_at between extract(epoch from '2023-10-01T00:00:00Z'::timestamp) and extract(epoch from '2023-10-31T23:59:59Z'::timestamp) ) 

end note
db0 --> elc
elc -> elc : collect created_by from order as array
elc -> eu : get affiliator_id via grpc everpro-user
note left
select r.user_id as affiliator_id, ur.user_id as member_id
from user_referral ur 
join referral r on r.entity_id = ur.referral_id 
where ur.user_id IN (userids)
end note
elc -> elc : calculate cashback of affiliator 
elc -> elc : calculate commission from formula
note left
    est_commission = cashback affiliator - cashback member
end note
elc -> db1 : upsert to affiliate_orders
elc -> cron : success response

## Move data from fct_shipping_affiliate_commission_v2

participant "cron" as cron
participant "everpro-logistic-client" as elc
database "popaket_logistic" as db0
database "everpro_data" as db1
participant "everpro-user" as eu

cron -> elc : hit endpoint 
elc -> db0 : get data from fct_shipping_affiliate_commission_v2 
note left 
query : 

select 
member_user_id,
affiliator_user_id,
shipment_order_no,
dispatched_at,
delivered_at,
logistic_id,
service_name,
rate_code,
actual_courier_cost,
cashback,
est_commission 
from fct_shipping_affiliate_commission_v2 
end note
db0 --> elc
elc -> db0 : get status, status_id and return_at from shipment_orders 
db0 --> elc

elc -> db1 : upsert to affiliate_orders
elc -> cron : success response

## API Design

| data | value |
| -------- | -------- |
| url | http://everpro-logistic-client.dev.internal/logistic/private/v1/sync-affiliate-order
| query string | backfill_month_year, is_move_from_old_table |
| response | { "status": "Success", "status_code": 200, "message": "success sync order", "timestamp": "2023-10-25T03:10:15.82194619Z", "data": null }