230 lines
7.8 KiB
TypeScript
230 lines
7.8 KiB
TypeScript
import { Migration } from '@mikro-orm/migrations';
|
|
|
|
/**
|
|
* DB-maintained order money fields.
|
|
*
|
|
* Inputs (set by app): coupon_discount, tax, delivery_fee, packing_fee
|
|
*
|
|
* Derived:
|
|
* - order_items.total_price = (unit_price - discount) * quantity
|
|
* - sub_total = SUM(unit_price * quantity) from non-deleted items
|
|
* - items_discount = SUM(discount * quantity) from non-deleted items
|
|
* - total_items = SUM(quantity) from non-deleted items
|
|
* - total_discount = coupon_discount + items_discount
|
|
* - total = GREATEST(0, sub_total - total_discount) + tax + delivery_fee + packing_fee
|
|
* - balance = total - paid_amount (also synced by payment-amount triggers)
|
|
*
|
|
* Also syncs pending payment.amount when order.total changes.
|
|
*/
|
|
export class Migration20260718110000_addOrderTotalsCalculation extends Migration {
|
|
override async up(): Promise<void> {
|
|
// --- order_items.total_price ---
|
|
this.addSql(`
|
|
create or replace function trg_order_items_sync_total_price()
|
|
returns trigger
|
|
language plpgsql
|
|
as $$
|
|
begin
|
|
new.total_price := (coalesce(new.unit_price, 0) - coalesce(new.discount, 0)) * coalesce(new.quantity, 0);
|
|
return new;
|
|
end;
|
|
$$;
|
|
`);
|
|
|
|
this.addSql(`
|
|
create trigger order_items_sync_total_price
|
|
before insert or update of unit_price, discount, quantity
|
|
on order_items
|
|
for each row
|
|
execute procedure trg_order_items_sync_total_price();
|
|
`);
|
|
|
|
// --- recalculate order aggregates from items + fee inputs ---
|
|
this.addSql(`
|
|
create or replace function recalculate_order_totals(p_order_id char(26))
|
|
returns void
|
|
language plpgsql
|
|
as $$
|
|
declare
|
|
v_sub_total numeric(10,0);
|
|
v_items_discount numeric(10,0);
|
|
v_total_items int;
|
|
v_total numeric(10,0);
|
|
begin
|
|
if p_order_id is null then
|
|
return;
|
|
end if;
|
|
|
|
select
|
|
coalesce(sum(oi.unit_price * oi.quantity), 0),
|
|
coalesce(sum(oi.discount * oi.quantity), 0),
|
|
coalesce(sum(oi.quantity), 0)::int
|
|
into v_sub_total, v_items_discount, v_total_items
|
|
from order_items oi
|
|
where oi.order_id = p_order_id
|
|
and oi.deleted_at is null;
|
|
|
|
update orders o
|
|
set
|
|
sub_total = v_sub_total,
|
|
items_discount = v_items_discount,
|
|
total_items = v_total_items,
|
|
total_discount = coalesce(o.coupon_discount, 0) + v_items_discount,
|
|
total = greatest(
|
|
0,
|
|
v_sub_total - (coalesce(o.coupon_discount, 0) + v_items_discount)
|
|
) + coalesce(o.tax, 0) + coalesce(o.delivery_fee, 0) + coalesce(o.packing_fee, 0),
|
|
balance = (
|
|
greatest(
|
|
0,
|
|
v_sub_total - (coalesce(o.coupon_discount, 0) + v_items_discount)
|
|
) + coalesce(o.tax, 0) + coalesce(o.delivery_fee, 0) + coalesce(o.packing_fee, 0)
|
|
) - coalesce(o.paid_amount, 0)
|
|
where o.id = p_order_id
|
|
returning o.total into v_total;
|
|
|
|
-- keep pending payments in sync with the new order total
|
|
update payments p
|
|
set amount = v_total
|
|
where p.order_id = p_order_id
|
|
and p.status = 'pending'
|
|
and p.deleted_at is null
|
|
and p.amount is distinct from v_total;
|
|
end;
|
|
$$;
|
|
`);
|
|
|
|
this.addSql(`
|
|
create or replace function trg_order_items_recalculate_order_totals()
|
|
returns trigger
|
|
language plpgsql
|
|
as $$
|
|
begin
|
|
if tg_op = 'DELETE' then
|
|
perform recalculate_order_totals(old.order_id);
|
|
return old;
|
|
end if;
|
|
|
|
perform recalculate_order_totals(new.order_id);
|
|
|
|
if tg_op = 'UPDATE' and old.order_id is distinct from new.order_id then
|
|
perform recalculate_order_totals(old.order_id);
|
|
end if;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
`);
|
|
|
|
this.addSql(`
|
|
create trigger order_items_recalculate_order_totals
|
|
after insert or update of unit_price, discount, quantity, order_id, deleted_at or delete
|
|
on order_items
|
|
for each row
|
|
execute procedure trg_order_items_recalculate_order_totals();
|
|
`);
|
|
|
|
// When fee / coupon / tax inputs change, recompute total (items unchanged)
|
|
this.addSql(`
|
|
create or replace function trg_orders_recalculate_totals_from_inputs()
|
|
returns trigger
|
|
language plpgsql
|
|
as $$
|
|
begin
|
|
if tg_op = 'UPDATE' and (
|
|
old.coupon_discount is not distinct from new.coupon_discount
|
|
and old.tax is not distinct from new.tax
|
|
and old.delivery_fee is not distinct from new.delivery_fee
|
|
and old.packing_fee is not distinct from new.packing_fee
|
|
) then
|
|
return new;
|
|
end if;
|
|
|
|
new.total_discount := coalesce(new.coupon_discount, 0) + coalesce(new.items_discount, 0);
|
|
new.total := greatest(
|
|
0,
|
|
coalesce(new.sub_total, 0) - new.total_discount
|
|
) + coalesce(new.tax, 0) + coalesce(new.delivery_fee, 0) + coalesce(new.packing_fee, 0);
|
|
new.balance := new.total - coalesce(new.paid_amount, 0);
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
`);
|
|
|
|
this.addSql(`
|
|
create trigger orders_recalculate_totals_from_inputs
|
|
before insert or update of coupon_discount, tax, delivery_fee, packing_fee
|
|
on orders
|
|
for each row
|
|
execute procedure trg_orders_recalculate_totals_from_inputs();
|
|
`);
|
|
|
|
// Sync pending payment amount when total changes (e.g. fee update)
|
|
this.addSql(`
|
|
create or replace function trg_orders_sync_pending_payment_amount()
|
|
returns trigger
|
|
language plpgsql
|
|
as $$
|
|
begin
|
|
if tg_op = 'UPDATE' and old.total is not distinct from new.total then
|
|
return new;
|
|
end if;
|
|
|
|
update payments p
|
|
set amount = new.total
|
|
where p.order_id = new.id
|
|
and p.status = 'pending'
|
|
and p.deleted_at is null
|
|
and p.amount is distinct from new.total;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
`);
|
|
|
|
// Must be AFTER UPDATE (not UPDATE OF total): BEFORE triggers may change NEW.total
|
|
// when only fee columns are in the client UPDATE, and UPDATE OF would not fire then.
|
|
this.addSql(`
|
|
create trigger orders_sync_pending_payment_amount
|
|
after update
|
|
on orders
|
|
for each row
|
|
when (old.total is distinct from new.total)
|
|
execute procedure trg_orders_sync_pending_payment_amount();
|
|
`);
|
|
|
|
// Backfill item total_price and order aggregates
|
|
this.addSql(`
|
|
update order_items
|
|
set total_price = (coalesce(unit_price, 0) - coalesce(discount, 0)) * coalesce(quantity, 0)
|
|
where total_price is distinct from (coalesce(unit_price, 0) - coalesce(discount, 0)) * coalesce(quantity, 0);
|
|
`);
|
|
|
|
this.addSql(`
|
|
do $$
|
|
declare
|
|
r record;
|
|
begin
|
|
for r in select id from orders where deleted_at is null
|
|
loop
|
|
perform recalculate_order_totals(r.id);
|
|
end loop;
|
|
end;
|
|
$$;
|
|
`);
|
|
}
|
|
|
|
override async down(): Promise<void> {
|
|
this.addSql(`drop trigger if exists orders_sync_pending_payment_amount on orders;`);
|
|
this.addSql(`drop trigger if exists orders_recalculate_totals_from_inputs on orders;`);
|
|
this.addSql(`drop trigger if exists order_items_recalculate_order_totals on order_items;`);
|
|
this.addSql(`drop trigger if exists order_items_sync_total_price on order_items;`);
|
|
this.addSql(`drop function if exists trg_orders_sync_pending_payment_amount();`);
|
|
this.addSql(`drop function if exists trg_orders_recalculate_totals_from_inputs();`);
|
|
this.addSql(`drop function if exists trg_order_items_recalculate_order_totals();`);
|
|
this.addSql(`drop function if exists trg_order_items_sync_total_price();`);
|
|
this.addSql(`drop function if exists recalculate_order_totals(char(26));`);
|
|
}
|
|
}
|