Refund, reconciliation va moliyaviy hisob: ikki yoqlama ledger

CREATE FUNCTION operatsiya_muvozanati() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE farq bigint;
BEGIN
  SELECT sum(debet) - sum(kredit) INTO farq
    FROM hisob_yozuvlari WHERE operatsiya_id = NEW.operatsiya_id;
  IF farq <> 0 THEN
    RAISE EXCEPTION
      'operatsiya % muvozanatda emas: debet - kredit = %',
      NEW.operatsiya_id, farq USING ERRCODE = 'check_violation';
  END IF;
  RETURN NULL;
END $$;
CREATE CONSTRAINT TRIGGER hisob_yozuvlari_muvozanat
  AFTER INSERT ON hisob_yozuvlari
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION operatsiya_muvozanati();

\set VERBOSITY terse
BEGIN;
INSERT INTO hisob_yozuvlari (operatsiya_id, hisob, debet)
VALUES ('00000000-0000-0000-0000-000000000001',
        'provayder:payme', 1250000);
COMMIT;

BEGIN
INSERT 0 1
ERROR:  operatsiya 00000000-0000-0000-0000-000000000001 muvozanatda emas: debet - kredit = 1250000

BEGIN;
INSERT INTO hisob_yozuvlari (operatsiya_id, hisob, debet, kredit)
VALUES ('00000000-0000-0000-0000-000000000002',
        'provayder:payme', 1250000, 0),
       ('00000000-0000-0000-0000-000000000002',
        'daromad', 0, 1250000);
COMMIT;

BEGIN
INSERT 0 2
COMMIT

INSERT INTO hisob_yozuvlari (operatsiya_id, hisob, debet, kredit)
VALUES ('00000000-0000-0000-0000-000000000003',
        'qaytarish', 300000, 0),
       ('00000000-0000-0000-0000-000000000003',
        'provayder:payme', 0, 300000);
SELECT hisob, balans, yozuvlar
  FROM hisob_balanslari ORDER BY hisob;

INSERT 0 2
      hisob      |  balans  | yozuvlar 
-----------------+----------+----------
 daromad         | -1250000 |        1
 provayder:payme |   950000 |        2
 qaytarish       |   300000 |        1
(3 rows)

\set VERBOSITY terse
UPDATE hisob_yozuvlari SET debet = 1 WHERE hisob = 'qaytarish';
DELETE FROM hisob_yozuvlari;
INSERT INTO hisob_yozuvlari (operatsiya_id, hisob, debet, kredit)
VALUES ('00000000-0000-0000-0000-000000000004', 'x', 5, 5);
SELECT sum(debet) AS debet, sum(kredit) AS kredit
  FROM hisob_yozuvlari;

ERROR:  hisob_yozuvlari o'zgartirilmaydi (teskari yozuv qo'shing)
ERROR:  hisob_yozuvlari o'zgartirilmaydi (teskari yozuv qo'shing)
ERROR:  new row for relation "hisob_yozuvlari" violates check constraint "hisob_yozuvlari_check"
  debet  | kredit  
---------+---------
 1550000 | 1550000
(1 row)

function solishtir(biz, provayder) {
  const p = new Map();
  for (const q of provayder) {
    if (p.has(q.tashqiId)) {
      throw new Error(`takror id: ${q.tashqiId}`);
    }
    p.set(q.tashqiId, q);
  }
  const farqlar = [];
  let mos = 0;
  for (const b of biz) {
    const q = p.get(b.tashqiId);
    p.delete(b.tashqiId);
    if (!q) farqlar.push(`faqat-bizda:${b.tashqiId}`);
    else if (q.summa !== b.summa) farqlar.push(`summa:${b.tashqiId}`);
    else if (q.holat !== b.holat) farqlar.push(`holat:${b.tashqiId}`);
    else mos++;
  }
  for (const q of p.values()) {
    farqlar.push(`faqat-provayderda:${q.tashqiId}`);
  }
  return { mos, farqlar };
}

const q = (tashqiId, summa, holat = "bajarilgan") =>
  ({ tashqiId, summa, holat });
const biz = [q("a", 100), q("b", 200), q("c", 300),
  q("d", 1, "yaratilgan")];
const prov = [q("a", 100), q("b", 250), q("c", 300, "qaytarilgan"),
  q("e", 5)];
console.log(solishtir(biz, prov));
try {
  solishtir([], [q("x", 1), q("x", 1)]);
} catch (e) {
  console.log(e.message);
}

{
  mos: 1,
  farqlar: [ 'summa:b', 'holat:c', 'faqat-bizda:d', 'faqat-provayderda:e' ]
}
takror id: x

150 to'lov: 1032 ms, muvaffaqiyatli 150/150

daromad                -379516.00 so'm  (450 yozuv)
provayder:click         110000.00 so'm  (150 yozuv)
provayder:payme         159516.00 so'm  (150 yozuv)
provayder:uzum          110000.00 so'm  (150 yozuv)
jami debet 37951600 = kredit 37951600 tiyin; operatsiyalar 450
tranzaksiyalar: click/bajarilgan=150, click/yaratilgan=1, payme/bajarilgan=150, payme/yaratilgan=1, uzum/bajarilgan=150

export type Yozuv = {
  hisob: string;
  debet?: number;
  kredit?: number;
};

/**
 * Bitta operatsiya — muvozanatda bo'lishi shart (baza ham COMMIT'da
 * tekshiradi: deferred trigger).
 */
export async function ledgerYoz(
  c: PoolClient,
  yozuvlar: Yozuv[],
  o: { tranzaksiyaId?: number; izoh?: string } = {},
): Promise<string> {
  const debet = yozuvlar.reduce((a, y) => a + (y.debet ?? 0), 0);
  const kredit = yozuvlar.reduce((a, y) => a + (y.kredit ?? 0), 0);
  if (debet !== kredit)
    throw new Error(
      `ledger: debet ${String(debet)} ≠ kredit ${String(kredit)}`,
    );
  const op = randomUUID();
  for (const y of yozuvlar) {
    await c.query(
      "INSERT INTO hisob_yozuvlari (operatsiya_id, hisob, debet, " +
        "kredit, tranzaksiya_id, izoh) VALUES ($1, $2, $3, $4, $5, " +
        "$6)",
      [
        op,
        y.hisob,
        y.debet ?? 0,
        y.kredit ?? 0,
        o.tranzaksiyaId ?? null,
        o.izoh ?? "",
      ],
    );
  }
  return op;
}

/**
 * To'lov bajarildi: provayderdagi pulimiz (+), daromad (+); komissiya
 * berilsa — xarajat.
 */
export const tolovYozuvi = (
  c: PoolClient,
  provayder: string,
  summa: number,
  tranzaksiyaId: number,
  komissiya = 0,
) =>
  ledgerYoz(
    c,
    [
      { hisob: `provayder:${provayder}`, debet: summa - komissiya },
      ...(komissiya > 0
        ? [{ hisob: `komissiya:${provayder}`, debet: komissiya }]
        : []),
      { hisob: "daromad", kredit: summa },
    ],
    { tranzaksiyaId, izoh: "to'lov" },
  );

/**
 * Qaytarish (refund) — o'chirish emas, teskari yozuv. Komissiya
 * odatda qaytmaydi (provayder shartnomasiga bog'liq — hujjat
 * bo'yicha).
 */
export const qaytarishYozuvi = (
  c: PoolClient,
  provayder: string,
  summa: number,
  tranzaksiyaId: number,
) =>
  ledgerYoz(
    c,
    [
      { hisob: "qaytarish", debet: summa },
      { hisob: `provayder:${provayder}`, kredit: summa },
    ],
    { tranzaksiyaId, izoh: "qaytarish" },
  );

// hisob.ts — qaytarish (refund) va solishtirish (reconciliation).
// qismanQaytar — idempotent (kalit bilan), jami ≤ to'langan, ledger
// teskari yozuvi bilan.
// solishtir — provayder hisobotidagi (Payme GetStatement, Click/Uzum
// reyestr, Stripe balance transactions) tranzaksiyalar bilan bizning
// bazani
// kunlik solishtirish: faqat bizda / faqat provayderda / summa farqi
// / holat farqi. Fiskal chek (soliq.uz OFD) — hujjat bo'yicha,
// SINALMAGAN.
import type { Pool } from "pg";
import { tranzaksiya, qaytarishYozuvi } from "./db.ts";

export class QaytarishXato extends Error {
  readonly kod: "topilmadi" | "holat" | "ortiqcha";
  constructor(
    kod: "topilmadi" | "holat" | "ortiqcha",
    xabar: string,
  ) {
    super(xabar);
    this.kod = kod;
  }
}

export async function qismanQaytar(
  pool: Pool,
  tranzaksiyaId: number,
  summa: number,
  sabab: string,
  kalit: string,
): Promise<{ id: number; yangi: boolean; qoldi: number }> {
  return tranzaksiya(pool, async (c) => {
    const bor = (
      await c.query<{
        id: string;
        tranzaksiya_id: string;
        summa: string;
      }>(
        "SELECT id, tranzaksiya_id, summa FROM qaytarishlar WHERE " +
          "idempotentlik_kaliti = $1",
        [kalit],
      )
    ).rows[0];
    const t = (
      await c.query<{
        id: string;
        provayder: string;
        summa: string;
        holat: string;
        buyurtma_id: string;
      }>(
        "SELECT id, provayder, summa, holat, buyurtma_id FROM " +
          "tranzaksiyalar WHERE id = $1 FOR UPDATE",
        [tranzaksiyaId],
      )
    ).rows[0];
    if (!t)
      throw new QaytarishXato("topilmadi", "tranzaksiya yo'q");
    const jami = async () =>
      Number(
        (
          await c.query<{ s: string }>(
            "SELECT coalesce(sum(summa), 0) AS s FROM qaytarishlar " +
              "WHERE tranzaksiya_id = $1",
            [t.id],
          )
        ).rows[0]?.s ?? 0,
      );
    if (bor) {
      if (
        Number(bor.tranzaksiya_id) !== tranzaksiyaId ||
        Number(bor.summa) !== summa
      )
        throw new QaytarishXato(
          "holat",
          "idempotentlik kaliti boshqa so'rov bilan ishlatilgan",
        );
      return {
        id: Number(bor.id),
        yangi: false,
        qoldi: Number(t.summa) - (await jami()),
      };
    }
    if (t.holat !== "bajarilgan")
      throw new QaytarishXato(
        "holat",
        `tranzaksiya holati: ${t.holat}`,
      );
    const oldin = await jami();
    if (oldin + summa > Number(t.summa))
      throw new QaytarishXato(
        "ortiqcha",
        `qaytarish ${String(oldin + summa)} > to'langan ${t.summa}`,
      );
    const q = (
      await c.query<{ id: string }>(
        "INSERT INTO qaytarishlar (tranzaksiya_id, summa, sabab, " +
          "idempotentlik_kaliti) VALUES ($1, $2, $3, $4) RETURNING " +
          "id",
        [t.id, summa, sabab, kalit],
      )
    ).rows[0];
    await qaytarishYozuvi(c, t.provayder, summa, Number(t.id));
    if (oldin + summa === Number(t.summa)) {
      await c.query(
        "UPDATE tranzaksiyalar SET holat = 'qaytarilgan', " +
          "bekor_vaqt = now() WHERE id = $1",
        [t.id],
      );
      await c.query(
        "UPDATE buyurtmalar SET holat = 'qaytarilgan', " +
          "yangilangan_vaqt = now() WHERE id = $1",
        [t.buyurtma_id],
      );
    }
    return {
      id: Number(q?.id),
      yangi: true,
      qoldi: Number(t.summa) - oldin - summa,
    };
  });
}

export type Qator = {
  tashqiId: string;
  summa: number;
  holat: "yaratilgan" | "bajarilgan" | "bekor" | "qaytarilgan";
};
export type Farq =
  | { tur: "faqat-bizda"; tashqiId: string; biz: Qator }
  | { tur: "faqat-provayderda"; tashqiId: string; provayder: Qator }
  | {
      tur: "summa";
      tashqiId: string;
      biz: number;
      provayder: number;
    }
  | {
      tur: "holat";
      tashqiId: string;
      biz: string;
      provayder: string;
    };

/**
 * Ikki ro'yxatni tashqi id bo'yicha solishtiradi (O(n)). Bir tomonda
 * takror id — xato (hisobot buzilgan).
 */
export function solishtir(
  biz: Qator[],
  provayder: Qator[],
): { mos: number; farqlar: Farq[] } {
  const p = new Map<string, Qator>();
  for (const q of provayder) {
    if (p.has(q.tashqiId))
      throw new Error(
        `provayder hisobotida takror id: ${q.tashqiId}`,
      );
    p.set(q.tashqiId, q);
  }
  const farqlar: Farq[] = [];
  let mos = 0;
  for (const b of biz) {
    const q = p.get(b.tashqiId);
    p.delete(b.tashqiId);
    if (!q) {
      farqlar.push({
        tur: "faqat-bizda",
        tashqiId: b.tashqiId,
        biz: b,
      });
      continue;
    }
    if (q.summa !== b.summa)
      farqlar.push({
        tur: "summa",
        tashqiId: b.tashqiId,
        biz: b.summa,
        provayder: q.summa,
      });
    else if (q.holat !== b.holat)
      farqlar.push({
        tur: "holat",
        tashqiId: b.tashqiId,
        biz: b.holat,
        provayder: q.holat,
      });
    else mos++;
  }
  for (const q of p.values())
    farqlar.push({
      tur: "faqat-provayderda",
      tashqiId: q.tashqiId,
      provayder: q,
    });
  return { mos, farqlar };
}

/** Payme GetStatement natijasi → Qator (state: 1, 2, -1, -2). */
export const paymeQator = (t: {
  id: string;
  amount: number;
  state: number;
}): Qator => ({
  tashqiId: t.id,
  summa: t.amount,
  holat:
    t.state === 2
      ? "bajarilgan"
      : t.state === 1
        ? "yaratilgan"
        : t.state === -2
          ? "qaytarilgan"
          : "bekor",
});

export async function bizningQatorlar(
  pool: Pool,
  provayder: string,
  dan: Date,
  gacha: Date,
): Promise<Qator[]> {
  const r = await pool.query<{
    tashqi_id: string;
    summa: string;
    holat: Qator["holat"];
  }>(
    "SELECT tashqi_id, summa, holat FROM tranzaksiyalar WHERE " +
      "provayder = $1 AND yaratilgan_vaqt >= $2 AND " +
      "yaratilgan_vaqt < $3",
    [provayder, dan, gacha],
  );
  return r.rows.map((q) => ({
    tashqiId: q.tashqi_id,
    summa: Number(q.summa),
    holat: q.holat,
  }));
}

✔ solishtir: mos, faqat bizda, faqat provayderda, summa, holat; takror id — xato (3.3047ms)
▶ qaytarish va ledger (Postgres)
  ✔ qisman qaytarish: idempotent, ortiqcha — rad, to'liq qaytgach holat 'qaytarilgan'; ledger muvozanatda (145.5219ms)
  ✔ ledger himoyasi: muvozanatsiz operatsiya COMMIT'da rad; UPDATE/DELETE taqiq (65.1493ms)
  ✔ rekonsilyatsiya: Payme GetStatement va baza — bitta yo'qolgan Perform aniqlanadi (91.7323ms)
✔ qaytarish va ledger (Postgres) (1329.2742ms)
ℹ tests 4
ℹ suites 1
ℹ pass 4
ℹ fail 0
ℹ cancelled 0
ℹ skipped 0
ℹ todo 0
ℹ duration_ms 1907.0541

M='feat(tolov): refund, reconciliation va ikki yoqlama ledger'
M+=" — deferred trigger, o'zgarmas yozuvlar"
git commit -m "$M"