本地服务商同城调度系统怎么选：交易侧与履约侧验收

CREATE TABLE biz_order (
  order_no varchar(64) PRIMARY KEY,
  tenant_id varchar(32) NOT NULL,
  site_id varchar(32) NOT NULL,
  status varchar(16) NOT NULL CHECK (status IN ('wait_pay','wait_accept','delivering','done','refunded','settled')),
  pay_amount_cents bigint NOT NULL CHECK (pay_amount_cents >= 0)
);

CREATE TABLE settlement_ledger (
  id bigserial PRIMARY KEY,
  tenant_id varchar(32) NOT NULL,
  site_id varchar(32) NOT NULL,
  order_no varchar(64) NOT NULL,
  account_type varchar(16) NOT NULL CHECK (account_type IN ('merchant','rider','platform')),
  account_id varchar(64) NOT NULL,
  entry_type varchar(24) NOT NULL CHECK (entry_type IN ('sale','commission','delivery_fee','refund_reverse','withdraw')),
  amount_cents bigint NOT NULL CHECK (amount_cents <> 0),
  biz_key varchar(160) NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE account_balance (
  tenant_id varchar(32) NOT NULL,
  site_id varchar(32) NOT NULL,
  account_type varchar(16) NOT NULL,
  account_id varchar(64) NOT NULL,
  available_cents bigint NOT NULL DEFAULT 0 CHECK (available_cents >= 0),
  version integer NOT NULL DEFAULT 0,
  PRIMARY KEY (tenant_id, site_id, account_type, account_id)
);

CREATE TABLE refund_record (
  refund_no varchar(64) PRIMARY KEY,
  tenant_id varchar(32) NOT NULL,
  site_id varchar(32) NOT NULL,
  order_no varchar(64) NOT NULL,
  amount_cents bigint NOT NULL CHECK (amount_cents > 0),
  status varchar(16) NOT NULL DEFAULT 'received' CHECK (status IN ('received','posted','failed'))
);

CREATE OR REPLACE FUNCTION post_refund_reverse(
  p_tenant text, p_site text, p_refund_no text, p_order_no text,
  p_account_type text, p_account_id text, p_amount_cents bigint
) RETURNS TABLE(result text, balance_cents bigint) AS $$
DECLARE
  v_status text;
  v_inserted integer;
  v_balance bigint;
BEGIN
  IF p_amount_cents <= 0 THEN
    RAISE EXCEPTION 'amount_must_be_positive';
  END IF;

  SELECT status INTO v_status
    FROM refund_record
   WHERE refund_no = p_refund_no FOR UPDATE;

  IF NOT FOUND THEN
    INSERT INTO refund_record(refund_no, tenant_id, site_id, order_no, amount_cents, status)
    VALUES (p_refund_no, p_tenant, p_site, p_order_no, p_amount_cents, 'received');
  ELSIF v_status <> 'received' THEN
    RETURN QUERY SELECT 'duplicate_or_invalid_state', NULL::bigint;
    RETURN;
  END IF;

  INSERT INTO settlement_ledger(tenant_id, site_id, order_no, account_type, account_id, entry_type, amount_cents, biz_key)
  VALUES (p_tenant, p_site, p_order_no, p_account_type, p_account_id, 'refund_reverse', -p_amount_cents, p_refund_no || ':reverse')
  ON CONFLICT (biz_key) DO NOTHING;
  GET DIAGNOSTICS v_inserted = ROW_COUNT;

  IF v_inserted = 0 THEN
    RETURN QUERY SELECT 'duplicate_ledger', NULL::bigint;
    RETURN;
  END IF;

  UPDATE account_balance
     SET available_cents = available_cents - p_amount_cents,
         version = version + 1
   WHERE tenant_id = p_tenant AND site_id = p_site
     AND account_type = p_account_type AND account_id = p_account_id
     AND available_cents >= p_amount_cents
  RETURNING available_cents INTO v_balance;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'insufficient_balance';
  END IF;

  UPDATE refund_record SET status = 'posted' WHERE refund_no = p_refund_no;
  RETURN QUERY SELECT 'posted', v_balance;
END;
$$ LANGUAGE plpgsql;