校园外卖小程序：商家自助上架与接单的状态机实现

CREATE TABLE product (
  id uuid PRIMARY KEY,
  merchant_id uuid NOT NULL,
  campus_id uuid NOT NULL,
  name text NOT NULL,
  price_cents integer NOT NULL CHECK (price_cents >= 0),
  stock integer NOT NULL DEFAULT 0 CHECK (stock >= 0),
  status text NOT NULL CHECK (status IN ('DRAFT','PENDING_REVIEW','ON_SALE','OFF_SALE','REJECTED')),
  version integer NOT NULL DEFAULT 1,
  sale_start timestamptz,
  sale_end timestamptz
);

CREATE TABLE product_change (
  id uuid PRIMARY KEY,
  product_id uuid NOT NULL,
  before_json jsonb,
  after_json jsonb NOT NULL,
  status text NOT NULL CHECK (status IN ('PENDING','APPROVED','REJECTED')),
  reviewer_id uuid,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id uuid PRIMARY KEY,
  merchant_id uuid NOT NULL,
  campus_id uuid NOT NULL,
  building_id uuid NOT NULL,
  status text NOT NULL,
  accept_deadline timestamptz NOT NULL,
  accepted_by uuid,
  accepted_at timestamptz,
  version integer NOT NULL DEFAULT 1
);

CREATE TABLE order_item_snapshot (
  order_id uuid NOT NULL,
  product_id uuid NOT NULL,
  product_version integer NOT NULL,
  name_snapshot text NOT NULL,
  price_cents_snapshot integer NOT NULL,
  qty integer NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, product_id)
);

CREATE TABLE outbox (
  id bigserial PRIMARY KEY,
  dedupe_key text NOT NULL UNIQUE,
  event_type text NOT NULL,
  payload jsonb NOT NULL,
  status text NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','SENT','FAILED')),
  retry_count integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE ledger (
  id bigserial PRIMARY KEY,
  order_id uuid NOT NULL,
  party text NOT NULL CHECK (party IN ('PLATFORM','MERCHANT','RIDER','USER')),
  entry_type text NOT NULL,
  amount_cents integer NOT NULL,
  idempotency_key text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

import express from 'express';
import { Pool } from 'pg';
import { z } from 'zod';

const app = express();
app.use(express.json());
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const bodySchema = z.object({
  merchantId: z.string().uuid(),
  operatorId: z.string().uuid(),
  expectedVersion: z.number().int().positive()
});

app.post('/api/merchant/orders/:orderId/accept', async (req, res) => {
  const parsed = bodySchema.safeParse(req.body);
  if (!parsed.success) return res.status(400).json({ code: 'BAD_REQUEST' });
  const { merchantId, operatorId, expectedVersion } = parsed.data;
  const { orderId } = req.params;
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const updated = await client.query(
      `UPDATE orders
          SET status = 'ACCEPTED', accepted_at = now(), accepted_by = $3, version = version + 1
        WHERE id = $1 AND merchant_id = $2 AND status = 'PENDING_ACCEPT'
          AND version = $4 AND accept_deadline > now()
        RETURNING id, version`,
      [orderId, merchantId, operatorId, expectedVersion]
    );
    if (updated.rowCount !== 1) {
      await client.query('ROLLBACK');
      return res.status(409).json({ code: 'STATE_CONFLICT' });
    }
    await client.query(
      `INSERT INTO outbox (dedupe_key, event_type, payload)
       VALUES ($1, 'ORDER_ACCEPTED', jsonb_build_object('orderId', $2::text))
       ON CONFLICT (dedupe_key) DO NOTHING`,
      [`accept:${orderId}`, orderId]
    );
    await client.query('COMMIT');
    return res.json({ ok: true, version: updated.rows[0].version });
  } catch (error) {
    await client.query('ROLLBACK');
    return res.status(500).json({ code: 'INTERNAL' });
  } finally {
    client.release();
  }
});

app.listen(3000);

SELECT o.id,
       o.user_paid_cents - COALESCE(r.refund_cents, 0)
       - (o.merchant_payable_cents + o.platform_commission_cents + o.rider_income_cents) AS diff_cents
FROM orders o
LEFT JOIN (
  SELECT order_id, SUM(amount_cents) AS refund_cents
  FROM ledger
  WHERE entry_type = 'REFUND'
  GROUP BY order_id
) r ON r.order_id = o.id
WHERE ABS(o.user_paid_cents - COALESCE(r.refund_cents, 0)
  - (o.merchant_payable_cents + o.platform_commission_cents + o.rider_income_cents)) > 0;