20260722090000_paid_community.sql

create table if not exists public.community_orders (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  status text not null default 'PENDING'
    check (status in ('PENDING', 'PAID', 'CLOSED', 'REFUNDED', 'REVOKED')),
  amount_cents integer not null default 990 check (amount_cents = 990),
  currency text not null default 'CNY' check (currency = 'CNY'),
  subject text not null default 'GPT-Image2 付费交流群长期资格',
  terms_version text not null,
  alipay_app_id text,
  alipay_seller_id text,
  alipay_trade_no text,
  refund_request_no text,
  refund_status text not null default 'NONE'
    check (refund_status in ('NONE', 'PROCESSING', 'SUCCEEDED', 'FAILED')),
  refund_failure_code text,
  paid_amount_cents integer check (paid_amount_cents is null or paid_amount_cents = 990),
  paid_currency text check (paid_currency is null or paid_currency = 'CNY'),
  metadata jsonb not null default '{}'::jsonb,
  expires_at timestamptz not null default (now() + interval '30 minutes'),
  paid_at timestamptz,
  refund_requested_at timestamptz,
  refund_checked_at timestamptz,
  refunded_at timestamptz,
  revoked_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index if not exists community_orders_one_active_per_user_idx
  on public.community_orders (user_id)
  where status in ('PENDING', 'PAID');

create unique index if not exists community_orders_alipay_trade_no_idx
  on public.community_orders (alipay_trade_no)
  where alipay_trade_no is not null;

create unique index if not exists community_orders_refund_request_no_idx
  on public.community_orders (refund_request_no)
  where refund_request_no is not null;

create index if not exists community_orders_created_at_idx
  on public.community_orders (created_at desc);

create index if not exists community_orders_status_idx
  on public.community_orders (status, created_at desc);

create table if not exists public.community_alipay_notify_events (
  id bigint generated by default as identity primary key,
  order_id uuid not null references public.community_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 community_alipay_notify_order_trade_event_idx
  on public.community_alipay_notify_events (order_id, trade_no, trade_status, notify_id);

create table if not exists public.community_group_qr_assets (
  id uuid primary key default gen_random_uuid(),
  media_type text not null check (media_type in ('image/png', 'image/jpeg', 'image/webp')),
  qr_bytes bytea not null,
  size_bytes integer not null check (size_bytes > 0 and size_bytes <= 2097152),
  is_current boolean not null default false,
  uploaded_by uuid not null references auth.users(id),
  created_at timestamptz not null default now(),
  retired_at timestamptz
);

create unique index if not exists community_group_qr_one_current_idx
  on public.community_group_qr_assets ((true))
  where is_current;

drop trigger if exists community_orders_set_updated_at on public.community_orders;
create trigger community_orders_set_updated_at
  before update on public.community_orders
  for each row
  execute function public.set_updated_at();

alter table public.community_orders enable row level security;
alter table public.community_alipay_notify_events enable row level security;
alter table public.community_group_qr_assets enable row level security;

revoke all on table public.community_orders from public, anon, authenticated;
revoke all on table public.community_alipay_notify_events from public, anon, authenticated;
revoke all on table public.community_group_qr_assets from public, anon, authenticated;

grant all on table public.community_orders to service_role;
grant all on table public.community_alipay_notify_events to service_role;
grant all on table public.community_group_qr_assets to service_role;
grant usage, select on sequence public.community_alipay_notify_events_id_seq to service_role;

create or replace function public.create_or_reuse_community_order(
  p_user_id uuid,
  p_terms_version text
)
returns public.community_orders
language plpgsql
security definer
set search_path = public
as $$
declare
  v_order public.community_orders%rowtype;
begin
  if p_user_id is null then
    raise exception 'COMMUNITY_USER_REQUIRED' using errcode = 'P0001';
  end if;
  if p_terms_version is null or btrim(p_terms_version) = '' then
    raise exception 'COMMUNITY_TERMS_REQUIRED' using errcode = 'P0001';
  end if;

  perform pg_advisory_xact_lock(hashtext(p_user_id::text));

  select co.*
    into v_order
    from public.community_orders as co
   where co.user_id = p_user_id
     and co.status in ('PENDING', 'PAID')
   order by co.created_at desc
   limit 1
   for update;

  if found then
    return v_order;
  end if;

  insert into public.community_orders (
    user_id,
    status,
    amount_cents,
    currency,
    subject,
    terms_version
  )
  values (
    p_user_id,
    'PENDING',
    990,
    'CNY',
    'GPT-Image2 付费交流群长期资格',
    p_terms_version
  )
  returning * into v_order;

  return v_order;
end;
$$;

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

  if not found then
    raise exception 'COMMUNITY_ORDER_NOT_FOUND' 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 or p_paid_amount_cents <> 990 then
    raise exception 'COMMUNITY_AMOUNT_MISMATCH' using errcode = 'P0001';
  end if;
  if p_paid_currency <> v_order.currency or p_paid_currency <> 'CNY' then
    raise exception 'COMMUNITY_CURRENCY_MISMATCH' using errcode = 'P0001';
  end if;

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

  if p_notify_id is not null and btrim(p_notify_id) <> '' then
    insert into public.community_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 in ('PAID', 'REFUNDED', 'REVOKED') then
    return query select v_order.status, false;
    return;
  end if;
  if v_order.status <> 'PENDING' then
    raise exception 'COMMUNITY_ORDER_NOT_PAYABLE' using errcode = 'P0001';
  end if;

  update public.community_orders as co
     set status = 'PAID',
         alipay_trade_no = p_trade_no,
         paid_amount_cents = p_paid_amount_cents,
         paid_currency = p_paid_currency,
         paid_at = coalesce(co.paid_at, now())
   where co.id = v_order.id;

  return query select 'PAID'::text, true;
end;
$$;

create or replace function public.close_community_order(p_order_id uuid)
returns table (
  current_status text,
  transitioned boolean
)
language plpgsql
security definer
set search_path = public
as $$
declare
  v_order public.community_orders%rowtype;
begin
  select co.*
    into v_order
    from public.community_orders as co
   where co.id = p_order_id
   for update;

  if not found then
    raise exception 'COMMUNITY_ORDER_NOT_FOUND' using errcode = 'P0001';
  end if;
  if v_order.status <> 'PENDING' then
    return query select v_order.status, false;
    return;
  end if;

  update public.community_orders as co
     set status = 'CLOSED'
   where co.id = v_order.id;
  return query select 'CLOSED'::text, true;
end;
$$;

create or replace function public.prepare_community_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.community_orders%rowtype;
begin
  select co.*
    into v_order
    from public.community_orders as co
   where co.id = p_order_id
   for update;

  if not found then
    raise exception 'COMMUNITY_ORDER_NOT_FOUND' using errcode = 'P0001';
  end if;
  if v_order.status = 'REFUNDED' then
    return query select v_order.refund_request_no, v_order.amount_cents, true;
    return;
  end if;
  if v_order.status <> 'PAID' then
    raise exception 'COMMUNITY_ORDER_NOT_REFUNDABLE' 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.refund_request_no is not null and v_order.refund_request_no <> p_refund_request_no then
    raise exception 'REFUND_REQUEST_NO_MISMATCH' using errcode = 'P0001';
  end if;

  update public.community_orders as co
     set refund_request_no = p_refund_request_no,
         refund_status = 'PROCESSING',
         refund_failure_code = null,
         refund_requested_at = coalesce(co.refund_requested_at, now())
   where co.id = v_order.id;

  return query select p_refund_request_no, v_order.amount_cents,
    v_order.refund_status = 'PROCESSING';
end;
$$;

create or replace function public.set_community_refund_state(
  p_order_id uuid,
  p_refund_status text,
  p_failure_code text default null
)
returns void
language plpgsql
security definer
set search_path = public
as $$
begin
  if p_refund_status not in ('PROCESSING', 'FAILED') then
    raise exception 'INVALID_REFUND_STATUS' using errcode = 'P0001';
  end if;
  update public.community_orders
     set refund_status = p_refund_status,
         refund_failure_code = nullif(left(coalesce(p_failure_code, ''), 120), ''),
         refund_checked_at = now()
   where id = p_order_id and status = 'PAID';
  if not found then
    raise exception 'COMMUNITY_ORDER_NOT_REFUNDABLE' using errcode = 'P0001';
  end if;
end;
$$;

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

  if not found then
    raise exception 'COMMUNITY_ORDER_NOT_FOUND' using errcode = 'P0001';
  end if;
  if v_order.status = 'REFUNDED' then
    return query select 'REFUNDED'::text, false;
    return;
  end if;
  if v_order.status <> 'PAID' or v_order.refund_status <> 'PROCESSING' then
    raise exception 'COMMUNITY_REFUND_NOT_PROCESSING' using errcode = 'P0001';
  end if;
  if v_order.alipay_trade_no <> p_trade_no
    or v_order.refund_request_no <> p_refund_request_no
    or p_refund_amount_cents <> v_order.amount_cents then
    raise exception 'COMMUNITY_REFUND_RESULT_MISMATCH' using errcode = 'P0001';
  end if;

  update public.community_orders as co
     set status = 'REFUNDED',
         refund_status = 'SUCCEEDED',
         refund_failure_code = null,
         refund_checked_at = now(),
         refunded_at = coalesce(co.refunded_at, now())
   where co.id = v_order.id;

  return query select 'REFUNDED'::text, true;
end;
$$;

create or replace function public.revoke_community_access(p_order_id uuid)
returns table (
  current_status text,
  transitioned boolean
)
language plpgsql
security definer
set search_path = public
as $$
declare
  v_order public.community_orders%rowtype;
begin
  select co.*
    into v_order
    from public.community_orders as co
   where co.id = p_order_id
   for update;

  if not found then
    raise exception 'COMMUNITY_ORDER_NOT_FOUND' using errcode = 'P0001';
  end if;
  if v_order.status = 'REVOKED' then
    return query select 'REVOKED'::text, false;
    return;
  end if;
  if v_order.status <> 'PAID' or v_order.refund_status = 'PROCESSING' then
    raise exception 'COMMUNITY_ORDER_NOT_REVOKABLE' using errcode = 'P0001';
  end if;

  update public.community_orders as co
     set status = 'REVOKED',
         revoked_at = coalesce(co.revoked_at, now())
   where co.id = v_order.id;
  return query select 'REVOKED'::text, true;
end;
$$;

create or replace function public.replace_community_group_qr_asset(
  p_media_type text,
  p_qr_bytes bytea,
  p_uploaded_by uuid
)
returns table (
  id uuid,
  media_type text,
  size_bytes integer,
  created_at timestamptz
)
language plpgsql
security definer
set search_path = public
as $$
declare
  v_asset public.community_group_qr_assets%rowtype;
  v_size integer;
begin
  v_size := octet_length(p_qr_bytes);
  if p_media_type not in ('image/png', 'image/jpeg', 'image/webp') then
    raise exception 'COMMUNITY_QR_INVALID_TYPE' using errcode = 'P0001';
  end if;
  if v_size is null or v_size < 1 or v_size > 2097152 then
    raise exception 'COMMUNITY_QR_INVALID_SIZE' using errcode = 'P0001';
  end if;
  if p_uploaded_by is null then
    raise exception 'COMMUNITY_QR_UPLOADER_REQUIRED' using errcode = 'P0001';
  end if;

  perform pg_advisory_xact_lock(hashtext('community_group_qr_current'));

  update public.community_group_qr_assets
     set is_current = false,
         retired_at = coalesce(retired_at, now())
   where is_current;

  insert into public.community_group_qr_assets (
    media_type,
    qr_bytes,
    size_bytes,
    is_current,
    uploaded_by
  )
  values (p_media_type, p_qr_bytes, v_size, true, p_uploaded_by)
  returning * into v_asset;

  return query select v_asset.id, v_asset.media_type, v_asset.size_bytes, v_asset.created_at;
end;
$$;

revoke execute on function public.create_or_reuse_community_order(uuid, text)
  from public, anon, authenticated;
revoke execute on function public.mark_community_order_paid(uuid, text, integer, text, text, jsonb)
  from public, anon, authenticated;
revoke execute on function public.close_community_order(uuid)
  from public, anon, authenticated;
revoke execute on function public.prepare_community_refund(uuid, text)
  from public, anon, authenticated;
revoke execute on function public.set_community_refund_state(uuid, text, text)
  from public, anon, authenticated;
revoke execute on function public.finalize_community_refund(uuid, text, text, integer)
  from public, anon, authenticated;
revoke execute on function public.revoke_community_access(uuid)
  from public, anon, authenticated;
revoke execute on function public.replace_community_group_qr_asset(text, bytea, uuid)
  from public, anon, authenticated;

grant execute on function public.create_or_reuse_community_order(uuid, text) to service_role;
grant execute on function public.mark_community_order_paid(uuid, text, integer, text, text, jsonb) to service_role;
grant execute on function public.close_community_order(uuid) to service_role;
grant execute on function public.prepare_community_refund(uuid, text) to service_role;
grant execute on function public.set_community_refund_state(uuid, text, text) to service_role;
grant execute on function public.finalize_community_refund(uuid, text, text, integer) to service_role;
grant execute on function public.revoke_community_access(uuid) to service_role;
grant execute on function public.replace_community_group_qr_asset(text, bytea, uuid) to service_role;