import { Migration } from '@mikro-orm/migrations';

/**
 * Adds paid_amount, balance, refunded_amount on orders.
 * Values are maintained by PostgreSQL functions + triggers from payments / order.total.
 *
 * Definitions:
 * - paid_amount     = SUM(payments.amount) WHERE status = 'paid' AND deleted_at IS NULL
 * - refunded_amount = SUM(payments.amount) WHERE status = 'refunded' AND deleted_at IS NULL
 * - balance         = total - paid_amount  (remaining amount due)
 */
export class Migration20260718093000_addOrderPaymentAmounts extends Migration {
  override async up(): Promise<void> {
    this.addSql(`
      alter table "orders"
        add column "paid_amount" numeric(10,0) not null default 0,
        add column "balance" numeric(10,0) not null default 0,
        add column "refunded_amount" numeric(10,0) not null default 0;
    `);

    this.addSql(`
      create or replace function recalculate_order_payment_amounts(p_order_id char(26))
      returns void
      language plpgsql
      as $$
      declare
        v_paid numeric(10,0);
        v_refunded numeric(10,0);
      begin
        if p_order_id is null then
          return;
        end if;

        select
          coalesce(sum(p.amount) filter (where p.status = 'paid'), 0),
          coalesce(sum(p.amount) filter (where p.status = 'refunded'), 0)
        into v_paid, v_refunded
        from payments p
        where p.order_id = p_order_id
          and p.deleted_at is null;

        update orders o
        set
          paid_amount = v_paid,
          refunded_amount = v_refunded,
          balance = o.total - v_paid
        where o.id = p_order_id;
      end;
      $$;
    `);

    this.addSql(`
      create or replace function trg_payments_recalculate_order_payment_amounts()
      returns trigger
      language plpgsql
      as $$
      begin
        if tg_op = 'DELETE' then
          perform recalculate_order_payment_amounts(old.order_id);
          return old;
        end if;

        perform recalculate_order_payment_amounts(new.order_id);

        if tg_op = 'UPDATE' and old.order_id is distinct from new.order_id then
          perform recalculate_order_payment_amounts(old.order_id);
        end if;

        return new;
      end;
      $$;
    `);

    this.addSql(`
      create trigger payments_recalculate_order_payment_amounts
      after insert or update of amount, status, order_id, deleted_at or delete
      on payments
      for each row
      execute procedure trg_payments_recalculate_order_payment_amounts();
    `);

    this.addSql(`
      create or replace function trg_orders_sync_balance()
      returns trigger
      language plpgsql
      as $$
      begin
        new.balance := coalesce(new.total, 0) - coalesce(new.paid_amount, 0);
        return new;
      end;
      $$;
    `);

    this.addSql(`
      create trigger orders_sync_balance
      before insert or update of total, paid_amount
      on orders
      for each row
      execute procedure trg_orders_sync_balance();
    `);

    // Backfill existing rows: set balance from total, then recalc from payments
    this.addSql(`
      update orders
      set balance = total - paid_amount;
    `);

    this.addSql(`
      do $$
      declare
        r record;
      begin
        for r in
          select distinct order_id
          from payments
          where deleted_at is null
            and status in ('paid', 'refunded')
        loop
          perform recalculate_order_payment_amounts(r.order_id);
        end loop;
      end;
      $$;
    `);
  }

  override async down(): Promise<void> {
    this.addSql(`drop trigger if exists orders_sync_balance on orders;`);
    this.addSql(`drop trigger if exists payments_recalculate_order_payment_amounts on payments;`);
    this.addSql(`drop function if exists trg_orders_sync_balance();`);
    this.addSql(`drop function if exists trg_payments_recalculate_order_payment_amounts();`);
    this.addSql(`drop function if exists recalculate_order_payment_amounts(char(26));`);
    this.addSql(`
      alter table "orders"
        drop column "paid_amount",
        drop column "balance",
        drop column "refunded_amount";
    `);
  }
}