alter table public.credit_packs
  add column if not exists alipay_amount_cents integer
  check (alipay_amount_cents is null or alipay_amount_cents > 0);

alter table public.payment_orders
  add column if not exists payment_provider text not null default 'stripe',
  add column if not exists provider_trade_no text,
  add column if not exists provider_refund_request_no text;

alter table public.payment_orders
  drop constraint if exists payment_orders_payment_provider_check;

alter table public.payment_orders
  add constraint payment_orders_payment_provider_check
  check (payment_provider in ('stripe', 'alipay'));

alter table public.payment_orders
  drop constraint if exists payment_orders_status_check;

alter table public.payment_orders
  add constraint payment_orders_status_check
  check (status in (
    'created',
    'checkout_created',
    'completed',
    'failed',
    'canceled',
    'refund_pending',
    'refunded'
  ));

create unique index if not exists payment_orders_provider_trade_no_idx
  on public.payment_orders (payment_provider, provider_trade_no)
  where provider_trade_no is not null;

create unique index if not exists payment_orders_provider_refund_request_no_idx
  on public.payment_orders (payment_provider, provider_refund_request_no)
  where provider_refund_request_no is not null;

create table if not exists public.alipay_notify_events (
  id bigint generated by default as identity primary key,
  order_id uuid not null references public.payment_orders(id) on delete cascade,
  notify_id text not null unique,
  trade_no text not null,
  trade_status text not null,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create unique index if not exists alipay_notify_order_trade_event_idx
  on public.alipay_notify_events (order_id, trade_no, trade_status, notify_id);

alter table public.alipay_notify_events enable row level security;

revoke all on table public.alipay_notify_events from public, anon, authenticated;
grant all on table public.alipay_notify_events to service_role;
grant usage, select on sequence public.alipay_notify_events_id_seq to service_role;

drop function if exists public.complete_alipay_credit_pack_order(uuid, text, integer);

create or replace function public.complete_alipay_credit_pack_order(
  p_order_id uuid,
  p_trade_no text,
  p_paid_amount_cents integer,
  p_notify_id text default null,
  p_notify_payload jsonb default '{}'::jsonb
)
returns table (
  completed boolean,
  credit_balance integer
)
language plpgsql
security definer
set search_path = public
as $$
declare
  v_order public.payment_orders%rowtype;
  v_balance integer;
begin
  select po.*
    into v_order
    from public.payment_orders as po
   where po.id = p_order_id
   for update;

  if not found then
    raise exception 'PAYMENT_ORDER_NOT_FOUND' using errcode = 'P0001';
  end if;

  if v_order.payment_provider <> 'alipay' or v_order.product_type <> 'credit_pack' then
    raise exception 'INVALID_ALIPAY_ORDER' using errcode = 'P0001';
  end if;

  if p_trade_no is null or btrim(p_trade_no) = '' then
    raise exception 'ALIPAY_TRADE_NO_REQUIRED' using errcode = 'P0001';
  end if;

  if p_paid_amount_cents <> v_order.amount_cents then
    raise exception 'ALIPAY_AMOUNT_MISMATCH' using errcode = 'P0001';
  end if;

  if p_notify_id is not null and btrim(p_notify_id) <> '' then
    insert into public.alipay_notify_events (
      order_id,
      notify_id,
      trade_no,
      trade_status,
      payload
    )
    values (
      v_order.id,
      p_notify_id,
      p_trade_no,
      coalesce(nullif(p_notify_payload ->> 'trade_status', ''), 'TRADE_SUCCESS'),
      coalesce(p_notify_payload, '{}'::jsonb)
    )
    on conflict (notify_id) do nothing;
  end if;

  if v_order.status = 'completed' then
    if v_order.provider_trade_no is not null and v_order.provider_trade_no <> p_trade_no then
      raise exception 'ALIPAY_TRADE_NO_MISMATCH' using errcode = 'P0001';
    end if;

    select p.credit_balance
      into v_balance
      from public.profiles as p
     where p.id = v_order.user_id;

    return query select false, v_balance;
    return;
  end if;

  if v_order.status not in ('created', 'checkout_created') then
    raise exception 'ALIPAY_ORDER_NOT_COMPLETABLE' using errcode = 'P0001';
  end if;

  update public.profiles as p
     set credit_balance = p.credit_balance + v_order.credits
   where p.id = v_order.user_id
   returning p.credit_balance into v_balance;

  if not found then
    raise exception 'PROFILE_NOT_FOUND' using errcode = 'P0001';
  end if;

  insert into public.credit_transactions (
    user_id,
    amount,
    type,
    source,
    reference_id,
    metadata
  )
  values (
    v_order.user_id,
    v_order.credits,
    'purchase',
    'alipay_webpay',
    v_order.id,
    jsonb_build_object(
      'alipayTradeNo', p_trade_no,
      'productId', v_order.product_id
    )
  );

  update public.payment_orders as po
     set status = 'completed',
         provider_trade_no = p_trade_no,
         completed_at = coalesce(po.completed_at, now()),
         metadata = po.metadata || jsonb_build_object('paidAmountCents', p_paid_amount_cents)
   where po.id = v_order.id;

  return query select true, v_balance;
end;
$$;

create or replace function public.prepare_alipay_credit_pack_refund(
  p_order_id uuid,
  p_refund_request_no text
)
returns table (
  refund_request_no text,
  refund_amount_cents integer,
  already_prepared boolean
)
language plpgsql
security definer
set search_path = public
as $$
declare
  v_order public.payment_orders%rowtype;
  v_balance integer;
begin
  select po.*
    into v_order
    from public.payment_orders as po
   where po.id = p_order_id
   for update;

  if not found then
    raise exception 'PAYMENT_ORDER_NOT_FOUND' using errcode = 'P0001';
  end if;

  if v_order.payment_provider <> 'alipay' or v_order.product_type <> 'credit_pack' then
    raise exception 'INVALID_ALIPAY_ORDER' using errcode = 'P0001';
  end if;

  if p_refund_request_no is null or btrim(p_refund_request_no) = '' then
    raise exception 'REFUND_REQUEST_NO_REQUIRED' using errcode = 'P0001';
  end if;

  if v_order.status in ('refund_pending', 'refunded') then
    if v_order.provider_refund_request_no <> p_refund_request_no then
      raise exception 'REFUND_REQUEST_NO_MISMATCH' using errcode = 'P0001';
    end if;
    return query select p_refund_request_no, v_order.amount_cents, true;
    return;
  end if;

  if v_order.status <> 'completed' then
    raise exception 'ALIPAY_ORDER_NOT_REFUNDABLE' using errcode = 'P0001';
  end if;

  select p.credit_balance
    into v_balance
    from public.profiles as p
   where p.id = v_order.user_id
   for update;

  if not found then
    raise exception 'PROFILE_NOT_FOUND' using errcode = 'P0001';
  end if;

  if v_balance < v_order.credits then
    raise exception 'PURCHASED_CREDITS_ALREADY_USED' using errcode = 'P0001';
  end if;

  update public.profiles as p
     set credit_balance = p.credit_balance - v_order.credits
   where p.id = v_order.user_id;

  insert into public.credit_transactions (
    user_id,
    amount,
    type,
    source,
    reference_id,
    metadata
  )
  values (
    v_order.user_id,
    -v_order.credits,
    'refund',
    'alipay_webpay_refund_reserve',
    v_order.id,
    jsonb_build_object(
      'refundRequestNo', p_refund_request_no,
      'productId', v_order.product_id
    )
  );

  update public.payment_orders as po
     set status = 'refund_pending',
         provider_refund_request_no = p_refund_request_no,
         metadata = po.metadata || jsonb_build_object('refundPreparedAt', now())
   where po.id = v_order.id;

  return query select p_refund_request_no, v_order.amount_cents, false;
end;
$$;

create or replace function public.finalize_alipay_credit_pack_refund(
  p_order_id uuid,
  p_trade_no text,
  p_refund_amount_cents integer
)
returns table (
  finalized boolean
)
language plpgsql
security definer
set search_path = public
as $$
declare
  v_order public.payment_orders%rowtype;
begin
  select po.*
    into v_order
    from public.payment_orders as po
   where po.id = p_order_id
   for update;

  if not found then
    raise exception 'PAYMENT_ORDER_NOT_FOUND' using errcode = 'P0001';
  end if;

  if v_order.payment_provider <> 'alipay' or v_order.product_type <> 'credit_pack' then
    raise exception 'INVALID_ALIPAY_ORDER' using errcode = 'P0001';
  end if;

  if v_order.provider_trade_no is not null and v_order.provider_trade_no <> p_trade_no then
    raise exception 'ALIPAY_TRADE_NO_MISMATCH' using errcode = 'P0001';
  end if;

  if p_refund_amount_cents is null or p_refund_amount_cents <> v_order.amount_cents then
    raise exception 'ALIPAY_REFUND_AMOUNT_MISMATCH' using errcode = 'P0001';
  end if;

  if v_order.status = 'refunded' then
    return query select false;
    return;
  end if;

  if v_order.status <> 'refund_pending' then
    raise exception 'ALIPAY_REFUND_NOT_PREPARED' using errcode = 'P0001';
  end if;

  update public.payment_orders as po
     set status = 'refunded',
         metadata = po.metadata || jsonb_build_object(
           'refundedAt', now(),
           'refundAmountCents', p_refund_amount_cents
         )
   where po.id = v_order.id;

  return query select true;
end;
$$;

revoke execute on function public.complete_alipay_credit_pack_order(uuid, text, integer, text, jsonb)
  from public, anon, authenticated;
revoke execute on function public.prepare_alipay_credit_pack_refund(uuid, text)
  from public, anon, authenticated;
revoke execute on function public.finalize_alipay_credit_pack_refund(uuid, text, integer)
  from public, anon, authenticated;

grant execute on function public.complete_alipay_credit_pack_order(uuid, text, integer, text, jsonb)
  to service_role;
grant execute on function public.prepare_alipay_credit_pack_refund(uuid, text)
  to service_role;
grant execute on function public.finalize_alipay_credit_pack_refund(uuid, text, integer)
  to service_role;