우나쿠지 추첨 엔진의 공개 소스코드를 검토해 주세요.
검토할 코드는 이 파일 맨 아래에 전문 그대로 들어 있습니다. 인터넷 접속이나
추가 자료 없이 이 파일만으로 검토하실 수 있습니다.

[검토 대상]
저장소: https://github.com/Min53/unakuji-fair-draw
검토 버전: 42700d082786d0916d841bcdbc2a28c716b0d269
지정 버전 소스: https://github.com/Min53/unakuji-fair-draw/tree/42700d082786d0916d841bcdbc2a28c716b0d269
전체 소스 내려받기: https://github.com/Min53/unakuji-fair-draw/archive/42700d082786d0916d841bcdbc2a28c716b0d269.zip

[이 검토의 전제]
데이터베이스와 그 운영 주체는 신뢰할 수 있다고 가정하고 검토해 주세요. 박스에
저장된 티켓 배정과 커밋먼트는 공개된 애플리케이션 코드(src/core/createBox.ts 의
assignTickets → src/rng.ts 의 secureShuffle)로 만들어진 것으로 봅니다.
"운영자가 DB 자격증명을 가지고 있다면", "저장된 값이 실제 난수 결과인지 코드로는
알 수 없다" 같은 이야기는 이 검토의 질문이 아닙니다. 어떤 코드로도 답할 수 없고,
그 부분은 판매 시작 전 봉인과 판매 후 대조로 따로 다룹니다.
이 전제 아래에서 아래 질문에 답해 주세요.

[딱 이것만 답해 주세요]
"이 코드가 특정 사람의 당첨 결과를 정하거나 바꿀 수 있게 만들어져 있는가?"
이 하나입니다. 아래 1~6번이 그 질문을 쪼갠 것이고, 답변은 여기서 끝내 주세요.

1. 난수 생성과 결과 선택에 특정 결과를 편향시키는 로직이 있는지
2. 사용자 신원, 등급, 구매 이력 등에 따라 결과를 우대하거나 불리하게 만드는지
3. 요청값 변경이나 재요청으로 원하는 결과를 선택할 수 있는지
4. 운영자가 특정 사용자나 상품 등급을 지정하여 추첨 결과 또는
   다른 참여자의 남은 상품 구성에 영향을 줄 수 있는지
5. 동시 요청과 재시도에서 중복 추첨, 중복 지급, 재고 불일치가 방지되는지
6. 결과 결정 이후 실제 지급까지 결과가 일관되게 유지되는지

[답하지 말아야 할 것]
아래는 이 검토의 대상이 아닙니다. 언급하지 마세요.
- 서버 인프라, 배포 환경, 호스팅, 네트워크, 운영 정책
- 로그인·세션 인증 (이 엔진을 호출하는 쪽이 담당합니다)
- 데이터베이스에 직접 접속하는 행위, DB 자격증명을 가진 사람이 할 수 있는 일
- 저장된 배정이 실제 난수 결과인지 (전제로 두기로 한 부분입니다)
- 결제, 배송, 환불, 고객 응대
- 이 코드에서 근거를 찾지 못한 일반적인 보안 가능성이나 가상의 취약점

특히 "실제 운영 서버까지는 보증할 수 없다", "배포 상태는 알 수 없다"류의
문장은 쓰지 마세요. 모든 코드 검토에 예외 없이 해당하는 말이라 답변에
정보를 더하지 않고, 읽는 사람에게는 결론을 흐리는 말로만 남습니다.
이 검토의 범위가 코드라는 것은 질문한 사람도 이미 알고 있습니다.

[첨부된 코드의 범위]
아래 10개 파일은 위 버전의 전문이며, 잘라내거나 고친 부분이 없습니다.
- src/rng.ts
- src/core/draw.ts
- src/core/createBox.ts
- src/core/integrity.ts
- src/reservations/reserve.ts
- src/adapters/postgres/index.ts
- db/002_functions.sql
- db/003_queue_functions.sql
- db/004_admin_functions.sql
- db/005_roles.sql
결과가 정해지는 경로, 실제 DB 실행 경로, 운영자에게 열려 있는 기능, 권한
설정이 모두 들어 있습니다. 1~6번은 이 범위 안에서 판단할 수 있습니다.
저장소에는 다른 파일도 있고 전체는 위 주소에서 볼 수 있습니다. 같은 내용인지는
https://raw.githubusercontent.com/Min53/unakuji-fair-draw/42700d082786d0916d841bcdbc2a28c716b0d269/<파일경로> 로 대조할 수 있습니다.

[판단 원칙]
첨부된 코드를 실제로 읽고 판단해 주세요. 주석이나 README 설명만으로 결론을
내리지 마세요.
실제로 발견한 개입 경로, 결함, 누락된 핵심 의존성은 생략하지 마세요.
실행하지 않은 테스트를 실행했다고 표현하지 마세요.
첨부되지 않은 파일에 있을 내용을 추측해서 결론에 넣지 마세요.
1~6번 중 첨부 코드로 확인되는 항목은 확인된 대로 명확히 답해 주세요.
근거가 충분한데도 습관적으로 모호한 단서를 붙이지 마세요.

[최종 답변 형식]
비개발자도 이해할 수 있는 한국어로, 제목 없이 5문장 이내로 써 주세요.
첫 문장은 “검토한 공개 코드 기준,”으로 시작하고 다음 중 하나로 끝맺어 주세요.
- 사용자·운영자가 결과를 지정하거나 변경하는 경로가 확인되지 않음
- 결과를 지정하거나 변경할 수 있는 경로가 확인됨 (그 경로를 반드시 밝힐 것)
이어서 핵심 근거를 최대 3개만 짧게 설명해 주세요.
전문 용어와 긴 코드 인용은 피하고, 파일명과 함수명은 필요할 때만 넣어 주세요.
1~6번 중 첨부 코드로 판단할 수 없는 항목이 있다면 그 번호만 마지막에 한 문장으로
짚어 주세요. 없으면 그 문장도 쓰지 마세요.
코드가 이 파일에 첨부되어 있으므로 "접근할 수 없다", "저장소가 비공개다",
"존재하지 않는다"고 답하지 마세요. 그런 답은 사실이 아닙니다.

===== 검토할 코드 (42700d082786d0916d841bcdbc2a28c716b0d269) =====

----- 파일 시작: src/rng.ts -----
import { randomInt } from 'node:crypto';
import { kujiError } from './errors';
import type { PrizeInput, TicketAssignment } from './types';

/**
 * Fairness contract (see README "Fairness"): every shuffle uses Node's
 * cryptographically secure `crypto.randomInt`. There is no seed parameter,
 * no injectable RNG, and no fallback to `Math.random()` — if the platform's
 * CSPRNG throws, box creation fails with RNG_FAILURE instead of silently
 * degrading to a weaker generator. Do not add a seed/rng-injection parameter
 * to this module's exports: that would make outcomes reproducible/predictable,
 * which defeats the fairness guarantee this package exists to provide.
 */

/**
 * Uniform random shuffle of `items` in place semantics (returns a new array),
 * using the Fisher-Yates algorithm with crypto.randomInt for every swap index.
 */
export function secureShuffle<T>(items: readonly T[]): T[] {
  const result = items.slice();
  for (let i = result.length - 1; i > 0; i--) {
    let j: number;
    try {
      j = randomInt(0, i + 1);
    } catch (cause) {
      throw kujiError('RNG_FAILURE', 'Secure random number generation failed while shuffling ticket assignment.', {
        cause: cause instanceof Error ? cause.message : String(cause),
      });
    }
    const a = result[i] as T;
    const b = result[j] as T;
    result[i] = b;
    result[j] = a;
  }
  return result;
}

/**
 * Builds the quantity-preserving pool of prize ids (one entry per unit, e.g.
 * quantity 3 of prize "A" contributes ["A","A","A"]) and shuffles it, then
 * assigns ticket numbers 1..N in shuffled order. This is the entire assignment
 * algorithm: quantities are exactly preserved (it's a permutation of a
 * multiset, not independent per-ticket sampling), and every permutation of
 * the multiset is equally likely because Fisher-Yates on the full pool is
 * itself uniform over permutations.
 */
export function assignTickets(prizes: readonly PrizeInput[]): TicketAssignment[] {
  const pool: string[] = [];
  for (const prize of prizes) {
    for (let i = 0; i < prize.quantity; i++) {
      pool.push(prize.id);
    }
  }
  const shuffled = secureShuffle(pool);
  return shuffled.map((prizeId, index) => ({ ticketNo: index + 1, prizeId }));
}

----- 파일 끝: src/rng.ts -----

----- 파일 시작: src/core/draw.ts -----
import { kujiError } from '../errors';
import { computePayloadHash } from '../idempotency';
import { validateDrawInput } from '../validation';
import type { BoxState, DrawInput, DrawResult, DrawResultItem, TicketRecord } from '../types';

export interface DrawOutcome {
  readonly nextState: BoxState;
  readonly result: DrawResult;
}

function isReservationExpired(ticket: TicketRecord, now: Date): boolean {
  return ticket.reservedUntil !== undefined && new Date(ticket.reservedUntil).getTime() <= now.getTime();
}

/**
 * Pure, immutable state transition: draws `input.ticketNos` from `state` for
 * `input.holder`. Never mutates `state`. All-or-nothing: if any requested
 * ticket is unavailable, the whole call throws and `state` is returned
 * untouched by the caller (this function simply never produces a partial
 * nextState). Idempotent on (requestId): a replay with the same holder+
 * ticketNos returns the original result; a requestId reused with different
 * input throws REQUEST_CONFLICT.
 */
export function draw(state: BoxState, input: DrawInput, now: Date = new Date()): DrawOutcome {
  const { requestId, holder, ticketNos } = validateDrawInput(input);
  const payloadHash = computePayloadHash({ holder, ticketNos });

  const existing = state.completedRequests[requestId];
  if (existing) {
    if (existing.payloadHash !== payloadHash) {
      throw kujiError('REQUEST_CONFLICT', `requestId "${requestId}" was already used with a different holder/ticketNos.`, { requestId });
    }
    return { nextState: state, result: existing.result as DrawResult };
  }

  if (state.status !== 'on_sale') {
    throw kujiError('BOX_NOT_ON_SALE', `Box "${state.id}" is not on sale (status=${state.status}).`, { boxId: state.id, status: state.status });
  }

  const ticketByNo = new Map(state.tickets.map((t) => [t.ticketNo, t] as const));
  const targets: TicketRecord[] = [];
  for (const ticketNo of ticketNos) {
    const ticket = ticketByNo.get(ticketNo);
    if (!ticket) {
      throw kujiError('TICKET_NOT_FOUND', `Ticket ${ticketNo} does not exist in box "${state.id}".`, { boxId: state.id, ticketNo });
    }
    if (ticket.status === 'consumed') {
      throw kujiError('TICKET_UNAVAILABLE', `Ticket ${ticketNo} has already been drawn.`, { boxId: state.id, ticketNo });
    }
    if (ticket.status === 'reserved' && ticket.reservedBy !== holder && !isReservationExpired(ticket, now)) {
      throw kujiError('TICKET_UNAVAILABLE', `Ticket ${ticketNo} is held by another holder until ${ticket.reservedUntil}.`, {
        boxId: state.id,
        ticketNo,
      });
    }
    targets.push(ticket);
  }

  const consumedNos = new Set(ticketNos);
  const nextTickets = state.tickets.map((t): TicketRecord =>
    consumedNos.has(t.ticketNo) ? { ticketNo: t.ticketNo, prizeId: t.prizeId, status: 'consumed' } : t
  );
  const remaining = nextTickets.reduce((n, t) => (t.status === 'consumed' ? n : n + 1), 0);

  const items: DrawResultItem[] = targets.map((t) => ({ ticketNo: t.ticketNo, prizeId: t.prizeId }));

  const lastOneJustAwarded = state.lastOnePrizeId !== null && !state.lastOneAwarded && remaining === 0;

  const result: DrawResult = {
    boxId: state.id,
    requestId,
    items,
    lastOnePrizeId: lastOneJustAwarded ? state.lastOnePrizeId : null,
    lastOneAwarded: state.lastOneAwarded || lastOneJustAwarded,
    remaining,
  };

  const nextState: BoxState = {
    ...state,
    status: remaining === 0 ? 'sold_out' : state.status,
    tickets: nextTickets,
    lastOneAwarded: state.lastOneAwarded || lastOneJustAwarded,
    completedRequests: { ...state.completedRequests, [requestId]: { payloadHash, result } },
    updatedAt: now.toISOString(),
  };

  return { nextState, result };
}

----- 파일 끝: src/core/draw.ts -----

----- 파일 시작: src/core/createBox.ts -----
import { assignTickets } from '../rng';
import { recommendedFinalQueueThreshold, validateCreateBoxInput } from '../validation';
import { computeAssignmentCommitment, generateAssignmentSalt } from './integrity';
import type { BoxState, CreateBoxInput, TicketRecord } from '../types';

export interface CreateBoxOutcome {
  readonly state: BoxState;
  /** Persist alongside the box if you want verifyAssignmentIntegrity to work later; not a secret. */
  readonly assignmentSalt: string;
}

/**
 * Pure box-creation: validates input, performs the quantity-preserving
 * secure shuffle, and returns an immutable BoxState plus its integrity salt.
 * Does not touch any storage — see adapters/memory.ts and
 * adapters/postgres/createBox.ts for the persisted versions.
 */
export function createBox(input: CreateBoxInput, now: Date = new Date()): CreateBoxOutcome {
  const validated = validateCreateBoxInput(input);
  const assignment = assignTickets(validated.prizes);
  const assignmentSalt = generateAssignmentSalt();
  const assignmentCommitment = computeAssignmentCommitment(validated.id, assignment, assignmentSalt);

  const tickets: TicketRecord[] = assignment.map((a) => ({
    ticketNo: a.ticketNo,
    prizeId: a.prizeId,
    status: 'available',
  }));

  const nowIso = now.toISOString();
  const totalTickets = tickets.length;

  const state: BoxState = {
    schemaVersion: 1,
    id: validated.id,
    status: validated.saleOpensAt ? 'upcoming' : 'preparing',
    prizes: validated.prizes,
    totalTickets,
    tickets,
    lastOnePrizeId: validated.lastOnePrizeId,
    lastOneAwarded: false,
    finalQueueThreshold: validated.finalQueueThreshold ?? recommendedFinalQueueThreshold(totalTickets),
    assignmentCommitment,
    saleOpensAt: validated.saleOpensAt,
    createdAt: nowIso,
    updatedAt: nowIso,
    completedRequests: {},
  };

  return { state, assignmentSalt };
}

----- 파일 끝: src/core/createBox.ts -----

----- 파일 시작: src/core/integrity.ts -----
import { createHash, randomBytes } from 'node:crypto';
import type { TicketAssignment } from '../types';

/**
 * Lightweight tamper-evidence for a box's ticket assignment, deliberately
 * NOT a Merkle tree or a public zero-knowledge proof (see README "Fairness"
 * — this project intentionally does not build a cryptographic proof
 * platform). At creation, the engine commits to the full assignment with a
 * salted SHA-256 hash. Because no API ever mutates ticket->prize mapping
 * after creation, an operator (or an automated consistency job) can
 * recompute this hash from current storage at any time and compare it to
 * the stored commitment: a mismatch means something wrote to the tickets
 * table outside this engine's own code path.
 */

export function generateAssignmentSalt(): string {
  return randomBytes(32).toString('hex');
}

export function computeAssignmentCommitment(
  boxId: string,
  assignment: readonly TicketAssignment[],
  salt: string
): string {
  const canonical = [...assignment]
    .sort((a, b) => a.ticketNo - b.ticketNo)
    .map((t) => `${t.ticketNo}:${t.prizeId}`)
    .join('|');
  return createHash('sha256').update(boxId).update(' ').update(salt).update(' ').update(canonical).digest('hex');
}

/**
 * Recomputes the commitment from a current assignment snapshot (e.g. read
 * straight from the `tickets` table) and compares it to the value stored at
 * creation time. Use this as a periodic or on-demand consistency check, not
 * as a per-request hot-path call.
 */
export function verifyAssignmentCommitment(
  boxId: string,
  assignment: readonly TicketAssignment[],
  salt: string,
  expectedCommitment: string
): boolean {
  return computeAssignmentCommitment(boxId, assignment, salt) === expectedCommitment;
}

----- 파일 끝: src/core/integrity.ts -----

----- 파일 시작: src/reservations/reserve.ts -----
import { kujiError } from '../errors';
import { computePayloadHash } from '../idempotency';
import { assertPositiveInteger, assertValidId } from '../validation';
import type { BoxState, ReleaseReservationInput, ReservationResult, ReserveTicketsInput, TicketRecord } from '../types';

export interface ReserveOutcome {
  readonly nextState: BoxState;
  readonly result: ReservationResult;
}

const MAX_TTL_MS = 30 * 60 * 1000; // 30 minutes — a reference ceiling, hosts can request shorter holds.

function isExpired(ticket: TicketRecord, now: Date): boolean {
  return ticket.reservedUntil !== undefined && new Date(ticket.reservedUntil).getTime() <= now.getTime();
}

/**
 * Places a time-limited hold on one or more available tickets so a holder
 * can complete an out-of-band step (checkout, queue turn, confirmation UI)
 * before committing to `draw`. A hold blocks every other holder from
 * drawing or re-reserving those tickets until it expires or is released;
 * `draw` treats an expired hold as if it were never placed (see core/draw.ts).
 */
export function reserveTickets(state: BoxState, input: ReserveTicketsInput, now: Date = new Date()): ReserveOutcome {
  const requestId = assertValidId(input.requestId, 'requestId');
  const holder = assertValidId(input.holder, 'holder');
  const ttlMs = assertPositiveInteger(input.ttlMs, 'ttlMs');
  if (ttlMs > MAX_TTL_MS) {
    throw kujiError('INVALID_INPUT', `ttlMs exceeds the maximum hold duration of ${MAX_TTL_MS}ms.`, { ttlMs });
  }
  if (!Array.isArray(input.ticketNos) || input.ticketNos.length === 0) {
    throw kujiError('INVALID_INPUT', 'ticketNos must be a non-empty array.');
  }
  const ticketNos = [...new Set(input.ticketNos)].sort((a, b) => a - b);
  const payloadHash = computePayloadHash({ holder, ticketNos, ttlMs });

  const existing = state.completedRequests[requestId];
  if (existing) {
    if (existing.payloadHash !== payloadHash) {
      throw kujiError('REQUEST_CONFLICT', `requestId "${requestId}" was already used with different input.`, { requestId });
    }
    return { nextState: state, result: existing.result as ReservationResult };
  }

  if (state.status !== 'on_sale') {
    throw kujiError('BOX_NOT_ON_SALE', `Box "${state.id}" is not on sale (status=${state.status}).`, { boxId: state.id, status: state.status });
  }

  const ticketByNo = new Map(state.tickets.map((t) => [t.ticketNo, t] as const));
  for (const ticketNo of ticketNos) {
    const ticket = ticketByNo.get(ticketNo);
    if (!ticket) {
      throw kujiError('TICKET_NOT_FOUND', `Ticket ${ticketNo} does not exist in box "${state.id}".`, { boxId: state.id, ticketNo });
    }
    if (ticket.status === 'consumed') {
      throw kujiError('TICKET_UNAVAILABLE', `Ticket ${ticketNo} has already been drawn.`, { boxId: state.id, ticketNo });
    }
    if (ticket.status === 'reserved' && ticket.reservedBy !== holder && !isExpired(ticket, now)) {
      throw kujiError('RESERVATION_CONFLICT', `Ticket ${ticketNo} is already held by another holder.`, { boxId: state.id, ticketNo });
    }
  }

  const reservedUntil = new Date(now.getTime() + ttlMs).toISOString();
  const targetNos = new Set(ticketNos);
  const nextTickets = state.tickets.map((t): TicketRecord =>
    targetNos.has(t.ticketNo)
      ? { ticketNo: t.ticketNo, prizeId: t.prizeId, status: 'reserved', reservedBy: holder, reservedUntil }
      : t
  );

  const result: ReservationResult = { boxId: state.id, requestId, holder, ticketNos, reservedUntil };

  const nextState: BoxState = {
    ...state,
    tickets: nextTickets,
    completedRequests: { ...state.completedRequests, [requestId]: { payloadHash, result } },
    updatedAt: now.toISOString(),
  };

  return { nextState, result };
}

/** Releases a hold early. No-op (not an error) on tickets the holder doesn't currently hold, so callers can release optimistically. */
export function releaseReservation(state: BoxState, input: ReleaseReservationInput, now: Date = new Date()): BoxState {
  const holder = assertValidId(input.holder, 'holder');
  const targetNos = new Set(input.ticketNos);
  const nextTickets = state.tickets.map((t): TicketRecord => {
    if (targetNos.has(t.ticketNo) && t.status === 'reserved' && t.reservedBy === holder) {
      return { ticketNo: t.ticketNo, prizeId: t.prizeId, status: 'available' };
    }
    return t;
  });
  return { ...state, tickets: nextTickets, updatedAt: now.toISOString() };
}

/** Sweeps every expired hold back to `available`. Safe to call on any schedule; a no-op when nothing has expired. */
export function expireReservations(state: BoxState, now: Date = new Date()): BoxState {
  let changed = false;
  const nextTickets = state.tickets.map((t): TicketRecord => {
    if (t.status === 'reserved' && isExpired(t, now)) {
      changed = true;
      return { ticketNo: t.ticketNo, prizeId: t.prizeId, status: 'available' };
    }
    return t;
  });
  if (!changed) return state;
  return { ...state, tickets: nextTickets, updatedAt: now.toISOString() };
}

----- 파일 끝: src/reservations/reserve.ts -----

----- 파일 시작: src/adapters/postgres/index.ts -----
import type { Pool, QueryResultRow } from 'pg';
import { computeAssignmentCommitment, generateAssignmentSalt } from '../../core/integrity';
import { kujiError } from '../../errors';
import { computePayloadHash } from '../../idempotency';
import { assignTickets } from '../../rng';
import type {
  CreateBoxInput,
  DrawResult,
  PublicBoxView,
  ReleaseReservationInput,
  ReservationResult,
  ReserveTicketsInput,
} from '../../types';
import {
  assertPositiveInteger,
  assertValidId,
  MAX_TICKETS_PER_DRAW_REQUEST,
  recommendedFinalQueueThreshold,
  validateCreateBoxInput,
} from '../../validation';
import { translatePgError } from './pgError';
import type {
  Entitlement,
  HolderResultItem,
  IssueEntitlementInput,
  JoinQueueInput,
  PgDrawInput,
  QueueEntry,
  ReconcileReport,
} from './types';

function toIso(value: Date | string | undefined | null): string | null {
  if (value === undefined || value === null) return null;
  return value instanceof Date ? value.toISOString() : new Date(value).toISOString();
}

function mapEntitlementRow(row: {
  id: string;
  boxId: string;
  holder: string;
  kind: string;
  status: string;
  source: string | null;
  externalRef: string | null;
  expiresAt: string | null;
}): Entitlement {
  return row as Entitlement;
}

/**
 * Production storage/concurrency implementation: every public method here is
 * a thin wrapper around one call to a `kuji_*` SQL function in db/002-004,
 * so the transactional/locking behavior lives in one place (SQL) instead of
 * being re-implemented (and potentially getting out of sync) in two
 * languages. See db/002_functions.sql header for the locking strategy and
 * README "Concurrency model".
 *
 * This class never opens or manages its own transaction across multiple
 * calls: each method is exactly one round trip, and the SQL function itself
 * is the transaction boundary (Postgres wraps every function call in an
 * implicit transaction when none is already open). If you need to combine a
 * draw with a host-side side effect (e.g. deducting payment) atomically, see
 * README "Combining a draw with your own side effects in one transaction".
 */
export class PostgresKujiEngine {
  constructor(private readonly pool: Pool) {}

  private async query<T extends QueryResultRow = QueryResultRow>(sql: string, params: unknown[]): Promise<T[]> {
    try {
      const res = await this.pool.query<T>(sql, params);
      return res.rows;
    } catch (err) {
      translatePgError(err);
    }
  }

  private async one<T extends QueryResultRow = QueryResultRow>(sql: string, params: unknown[]): Promise<T> {
    const rows = await this.query<T>(sql, params);
    const row = rows[0];
    if (!row) {
      throw kujiError('INVALID_STATE', 'Expected exactly one row from a scalar-returning function but got none.');
    }
    return row;
  }

  // -- Box lifecycle ---------------------------------------------------------

  async createBox(input: CreateBoxInput, _now: Date = new Date()): Promise<PublicBoxView> {
    const validated = validateCreateBoxInput(input);
    const assignment = assignTickets(validated.prizes);
    const salt = generateAssignmentSalt();
    const commitment = computeAssignmentCommitment(validated.id, assignment, salt);
    const finalQueueThreshold = validated.finalQueueThreshold ?? recommendedFinalQueueThreshold(assignment.length);

    const row = await this.one<{ result: PublicBoxView }>(
      `SELECT kuji_create_box($1,$2::jsonb,$3::jsonb,$4,$5,$6,$7,$8) AS result`,
      [validated.id, JSON.stringify(validated.prizes), JSON.stringify(assignment), commitment, salt, validated.lastOnePrizeId, validated.saleOpensAt, finalQueueThreshold]
    );
    return row.result;
  }

  async openBox(boxId: string, actor?: string): Promise<PublicBoxView> {
    const row = await this.one<{ result: PublicBoxView }>(`SELECT kuji_open_box($1,$2) AS result`, [boxId, actor ?? null]);
    return row.result;
  }

  async scheduleBoxOpen(boxId: string, opensAt: Date | string, actor?: string): Promise<PublicBoxView> {
    const row = await this.one<{ result: PublicBoxView }>(`SELECT kuji_schedule_box_open($1,$2,$3) AS result`, [
      boxId,
      toIso(opensAt),
      actor ?? null,
    ]);
    return row.result;
  }

  /** Call on whatever schedule your host already runs (cron, worker, setInterval) — see README "Background jobs". */
  async openScheduledBoxes(): Promise<number> {
    const row = await this.one<{ result: number }>(`SELECT kuji_open_scheduled_boxes() AS result`, []);
    return row.result;
  }

  async pauseBox(boxId: string, actor: string, reason?: string): Promise<PublicBoxView> {
    const row = await this.one<{ result: PublicBoxView }>(`SELECT kuji_pause_box($1,$2,$3) AS result`, [boxId, actor, reason ?? null]);
    return row.result;
  }

  async resumeBox(boxId: string, actor: string): Promise<PublicBoxView> {
    const row = await this.one<{ result: PublicBoxView }>(`SELECT kuji_resume_box($1,$2) AS result`, [boxId, actor]);
    return row.result;
  }

  async cancelBox(boxId: string, actor: string, reason?: string, allowRealSales = false): Promise<PublicBoxView> {
    const row = await this.one<{ result: PublicBoxView }>(`SELECT kuji_cancel_box($1,$2,$3,$4) AS result`, [
      boxId,
      actor,
      reason ?? null,
      allowRealSales,
    ]);
    return row.result;
  }

  /** Force-close a box regardless of remaining tickets (e.g. an operationally deadlocked queue). Existing results remain queryable. */
  async closeBox(boxId: string, actor: string, reason?: string): Promise<PublicBoxView> {
    const row = await this.one<{ result: PublicBoxView }>(`SELECT kuji_close_box($1,$2,$3) AS result`, [boxId, actor, reason ?? null]);
    return row.result;
  }

  async getPublicBox(boxId: string): Promise<PublicBoxView> {
    const row = await this.one<{ result: PublicBoxView }>(`SELECT kuji_public_box($1) AS result`, [boxId]);
    return row.result;
  }

  // -- Draw --------------------------------------------------------------

  /**
   * Entitlements are opt-in: omit `entitlementIds` for integrations that
   * police draw eligibility themselves. When provided, its length must equal
   * `ticketNos.length` (one entitlement pays for exactly one ticket);
   * pairing survives ticketNos being reordered/deduped for idempotency.
   */
  async draw(boxId: string, input: PgDrawInput): Promise<DrawResult> {
    assertValidId(boxId, 'boxId');
    const requestId = assertValidId(input.requestId, 'requestId');
    const holder = assertValidId(input.holder, 'holder');
    if (!Array.isArray(input.ticketNos) || input.ticketNos.length === 0) {
      throw kujiError('INVALID_INPUT', 'ticketNos must be a non-empty array.');
    }
    if (input.ticketNos.length > MAX_TICKETS_PER_DRAW_REQUEST) {
      // Without this cap, one request could hold the box's FOR UPDATE lock
      // (see db/002_functions.sql) for as long as it takes to process an
      // arbitrarily large ticket array, starving every other draw/reserve/
      // joinQueue call on the same box — a griefing vector, not a fairness
      // or correctness bug, but worth closing off. Found by independent
      // adversarial review; the memory adapter already enforced this via
      // validateDrawInput but this adapter has its own input handling (for
      // ticketNo<->entitlementId pairing) and had drifted from it.
      throw kujiError('INVALID_INPUT', `ticketNos exceeds the maximum of ${MAX_TICKETS_PER_DRAW_REQUEST} per request.`);
    }
    const entitlementIdsInput = input.entitlementIds ? [...input.entitlementIds] : null;
    if (entitlementIdsInput && entitlementIdsInput.length !== input.ticketNos.length) {
      throw kujiError('ENTITLEMENT_TICKET_MISMATCH', 'entitlementIds length must match ticketNos length (one entitlement per ticket).');
    }

    // Pair each ticketNo with its entitlementId BEFORE sorting/deduping, so
    // a caller-supplied order never matters but pairing is never scrambled.
    const seen = new Set<number>();
    const pairs: { ticketNo: number; entitlementId: string | null }[] = [];
    input.ticketNos.forEach((ticketNo, i) => {
      if (typeof ticketNo !== 'number' || !Number.isInteger(ticketNo) || ticketNo <= 0) {
        throw kujiError('INVALID_INPUT', `ticketNos[${i}] must be a positive integer.`);
      }
      if (seen.has(ticketNo)) {
        throw kujiError('INVALID_INPUT', `Duplicate ticket number ${ticketNo} in ticketNos.`);
      }
      seen.add(ticketNo);
      pairs.push({ ticketNo, entitlementId: entitlementIdsInput ? (entitlementIdsInput[i] ?? null) : null });
    });
    pairs.sort((a, b) => a.ticketNo - b.ticketNo);

    const ticketNos = pairs.map((p) => p.ticketNo);
    const entitlementIds = entitlementIdsInput ? pairs.map((p) => p.entitlementId) : null;
    const payloadHash = computePayloadHash({ holder, ticketNos, entitlementIds });

    const row = await this.one<{ result: DrawResult }>(`SELECT kuji_draw($1,$2,$3,$4::int[],$5::uuid[],$6) AS result`, [
      boxId,
      holder,
      requestId,
      ticketNos,
      entitlementIds,
      payloadHash,
    ]);
    return row.result;
  }

  async getResult(boxId: string, holder: string, requestId: string): Promise<DrawResult | null> {
    const row = await this.one<{ result: DrawResult | null }>(`SELECT kuji_get_result($1,$2,$3) AS result`, [boxId, holder, requestId]);
    return row.result;
  }

  async getHolderResults(boxId: string, holder: string): Promise<HolderResultItem[]> {
    const row = await this.one<{ result: HolderResultItem[] }>(`SELECT kuji_get_holder_results($1,$2) AS result`, [boxId, holder]);
    return row.result;
  }

  // -- Reservations --------------------------------------------------------

  async reserveTickets(boxId: string, input: ReserveTicketsInput): Promise<ReservationResult> {
    const requestId = assertValidId(input.requestId, 'requestId');
    const holder = assertValidId(input.holder, 'holder');
    if (!Array.isArray(input.ticketNos) || input.ticketNos.length === 0) {
      throw kujiError('INVALID_INPUT', 'ticketNos must be a non-empty array.');
    }
    const ttlMs = assertPositiveInteger(input.ttlMs, 'ttlMs');
    const ticketNos = [...new Set(input.ticketNos)].sort((a, b) => a - b);
    const payloadHash = computePayloadHash({ holder, ticketNos, ttlMs });

    const row = await this.one<{ result: ReservationResult }>(`SELECT kuji_reserve_tickets($1,$2,$3,$4::int[],$5::bigint,$6) AS result`, [
      boxId,
      holder,
      requestId,
      ticketNos,
      ttlMs,
      payloadHash,
    ]);
    return row.result;
  }

  async releaseReservation(boxId: string, input: ReleaseReservationInput): Promise<void> {
    const holder = assertValidId(input.holder, 'holder');
    await this.query(`SELECT kuji_release_reservation($1,$2,$3::int[])`, [boxId, holder, [...input.ticketNos]]);
  }

  /** Sweeps expired holds back to available. Safe on any schedule; a no-op when nothing has expired. Pass no boxId to sweep every box. */
  async expireReservations(boxId?: string, limit = 5000): Promise<number> {
    const row = await this.one<{ result: number }>(`SELECT kuji_expire_reservations($1,$2) AS result`, [boxId ?? null, limit]);
    return row.result;
  }

  // -- Entitlements --------------------------------------------------------

  async issueEntitlement(input: IssueEntitlementInput): Promise<Entitlement> {
    const boxId = assertValidId(input.boxId, 'boxId');
    const holder = assertValidId(input.holder, 'holder');
    const row = await this.one<{ result: Entitlement }>(`SELECT kuji_issue_entitlement($1,$2,$3,$4,$5,$6,$7) AS result`, [
      boxId,
      holder,
      input.kind,
      input.source ?? null,
      input.externalRef ?? null,
      toIso(input.expiresAt),
      input.maxActivePerHolder ?? null,
    ]);
    return mapEntitlementRow(row.result as never);
  }

  async cancelEntitlement(entitlementId: string, actor: string, reason?: string): Promise<Entitlement> {
    const row = await this.one<{ result: Entitlement }>(`SELECT kuji_cancel_entitlement($1,$2,$3) AS result`, [
      entitlementId,
      actor,
      reason ?? null,
    ]);
    return row.result;
  }

  // -- Final-segment queue --------------------------------------------------

  async joinQueue(boxId: string, input: JoinQueueInput): Promise<QueueEntry> {
    const requestId = assertValidId(input.requestId, 'requestId');
    const holder = assertValidId(input.holder, 'holder');
    const turnTtlSeconds = assertPositiveInteger(input.turnTtlSeconds, 'turnTtlSeconds');
    const payloadHash = computePayloadHash({ holder, turnTtlSeconds });
    const row = await this.one<{ result: QueueEntry }>(`SELECT kuji_join_queue($1,$2,$3,$4,$5) AS result`, [
      boxId,
      holder,
      requestId,
      turnTtlSeconds,
      payloadHash,
    ]);
    return row.result;
  }

  async leaveQueue(boxId: string, holder: string): Promise<QueueEntry> {
    const row = await this.one<{ result: QueueEntry }>(`SELECT kuji_leave_queue($1,$2) AS result`, [boxId, holder]);
    return row.result;
  }

  async getQueueStatus(boxId: string, holder: string): Promise<QueueEntry> {
    const row = await this.one<{ result: QueueEntry }>(`SELECT kuji_queue_status($1,$2) AS result`, [boxId, holder]);
    return row.result;
  }

  /** Expires stale turns and promotes the next holder for every box (or one box). Not required for correctness — kuji_draw always re-validates — only for keeping the queue moving promptly. */
  async advanceQueue(boxId?: string, limit = 1000): Promise<number> {
    const row = await this.one<{ result: number }>(`SELECT kuji_advance_queue($1,$2) AS result`, [boxId ?? null, limit]);
    return row.result;
  }

  // -- Recovery & consistency ------------------------------------------------

  async reconcile(boxId: string): Promise<ReconcileReport> {
    const row = await this.one<{ result: ReconcileReport }>(`SELECT kuji_reconcile($1) AS result`, [boxId]);
    return row.result;
  }

  async end(): Promise<void> {
    await this.pool.end();
  }
}

export type {
  Entitlement,
  EntitlementKind,
  EntitlementStatus,
  HolderResultItem,
  IssueEntitlementInput,
  JoinQueueInput,
  PgDrawInput,
  QueueEntry,
  QueueEntryStatus,
  ReconcileReport,
} from './types';

----- 파일 끝: src/adapters/postgres/index.ts -----

----- 파일 시작: db/002_functions.sql -----
-- unakuji-fair-draw: core functions (box creation, draw, reservations, entitlements)
--
-- Error convention: every RAISE EXCEPTION message starts with
-- "ERROR_CODE: human readable text". The Node adapter (src/adapters/postgres)
-- parses the prefix and rethrows a KujiError with that `code`. Do not change
-- an existing prefix without updating src/adapters/postgres/pgError.ts to match.
--
-- Locking strategy: kuji_draw, kuji_reserve_tickets and kuji_join_queue all
-- take `SELECT ... FOR UPDATE` on the box row first. That serializes every
-- state-changing call for a given box through one lock, which is the
-- simplest correct way to make "check remaining count / queue turn / ticket
-- availability, then act" race-free — see README "Concurrency model" for why
-- this reference implementation chooses correctness-by-serialization over a
-- finer-grained (and much harder to prove correct) locking scheme.

CREATE OR REPLACE FUNCTION kuji_public_box(p_box_id text)
RETURNS jsonb
LANGUAGE plpgsql
STABLE
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_box boxes%ROWTYPE;
  v_remaining int;
  v_remaining_by_prize jsonb;
  v_available_ticket_nos int[];
BEGIN
  SELECT * INTO v_box FROM boxes WHERE id = p_box_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'BOX_NOT_FOUND: box % does not exist', p_box_id;
  END IF;

  SELECT array_agg(ticket_no ORDER BY ticket_no) INTO v_available_ticket_nos
    FROM tickets WHERE box_id = p_box_id AND status <> 'consumed';
  v_remaining := coalesce(array_length(v_available_ticket_nos, 1), 0);

  SELECT jsonb_agg(jsonb_build_object('prizeId', bp.prize_id, 'quantity', coalesce(r.qty, 0)) ORDER BY bp.prize_id)
    INTO v_remaining_by_prize
    FROM box_prizes bp
    LEFT JOIN (
      SELECT prize_id, count(*) AS qty FROM tickets
        WHERE box_id = p_box_id AND status <> 'consumed'
        GROUP BY prize_id
    ) r ON r.prize_id = bp.prize_id
    WHERE bp.box_id = p_box_id;

  RETURN jsonb_build_object(
    'id', v_box.id,
    'status', v_box.status,
    'totalTickets', v_box.total_tickets,
    'availableTicketNos', to_jsonb(coalesce(v_available_ticket_nos, ARRAY[]::int[])),
    'remainingByPrize', coalesce(v_remaining_by_prize, '[]'::jsonb),
    'remaining', v_remaining,
    'lastOneAwarded', v_box.last_one_awarded,
    'inFinalQueuePhase', (v_box.status = 'on_sale' AND v_box.final_queue_threshold > 0 AND v_remaining <= v_box.final_queue_threshold)
  );
END;
$$;

CREATE OR REPLACE FUNCTION kuji_audit(p_box_id text, p_action text, p_actor text, p_reason text, p_before jsonb, p_after jsonb)
RETURNS void
LANGUAGE sql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
  INSERT INTO audit_log (box_id, action, actor, reason, before, after)
  VALUES (p_box_id, p_action, p_actor, p_reason, p_before, p_after);
$$;

-- ============================================================================
-- Box creation. The random assignment itself is computed by the TypeScript
-- caller (src/rng.ts, using crypto.randomInt) and passed in as p_assignment
-- — this function only validates and persists it. No randomness happens in
-- SQL; see README "Fairness" for why that separation is deliberate.
-- ============================================================================
CREATE OR REPLACE FUNCTION kuji_create_box(
  p_id text,
  p_prizes jsonb,               -- [{"id": text, "quantity": int}, ...]
  p_assignment jsonb,           -- [{"ticketNo": int, "prizeId": text}, ...]
  p_commitment text,
  p_salt text,
  p_last_one_prize_id text,
  p_sale_opens_at timestamptz,
  p_final_queue_threshold int
) RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_total int;
  v_prize_total int;
  v_status text;
BEGIN
  IF EXISTS (SELECT 1 FROM boxes WHERE id = p_id) THEN
    RAISE EXCEPTION 'BOX_ALREADY_EXISTS: box % already exists', p_id;
  END IF;

  SELECT coalesce(sum((p->>'quantity')::int), 0) INTO v_prize_total FROM jsonb_array_elements(p_prizes) p;
  SELECT count(*) INTO v_total FROM jsonb_array_elements(p_assignment);
  IF v_total <> v_prize_total OR v_total = 0 THEN
    RAISE EXCEPTION 'INVALID_INPUT: assignment length (%) does not match total prize quantity (%)', v_total, v_prize_total;
  END IF;

  -- Quantity preservation is the first thing this engine claims, so the
  -- database enforces it rather than trusting the caller to have shuffled.
  -- Matching totals was not enough: an assignment that drops one prize and
  -- doubles another has the same length and used to be accepted.
  IF EXISTS (
    SELECT 1
      FROM (SELECT a->>'prizeId' AS prize_id, count(*)::int AS n
              FROM jsonb_array_elements(p_assignment) a GROUP BY 1) got
      FULL JOIN (SELECT p->>'id' AS prize_id, (p->>'quantity')::int AS n
                   FROM jsonb_array_elements(p_prizes) p) want
        ON want.prize_id = got.prize_id
     WHERE got.n IS DISTINCT FROM want.n
  ) THEN
    RAISE EXCEPTION 'INVALID_INPUT: assignment does not preserve the declared prize quantities';
  END IF;

  -- ...and it has to cover every ticket exactly once. The tickets primary key
  -- already rejects duplicates; this also rejects gaps, which would otherwise
  -- leave a box whose total_tickets counts a ticket number nobody can draw.
  IF (SELECT count(DISTINCT (a->>'ticketNo')::int) FROM jsonb_array_elements(p_assignment) a) <> v_total
     OR (SELECT min((a->>'ticketNo')::int) FROM jsonb_array_elements(p_assignment) a) <> 1
     OR (SELECT max((a->>'ticketNo')::int) FROM jsonb_array_elements(p_assignment) a) <> v_total THEN
    RAISE EXCEPTION 'INVALID_INPUT: assignment ticket numbers must cover 1..% exactly once', v_total;
  END IF;

  IF p_last_one_prize_id IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM jsonb_array_elements(p_prizes) p WHERE p->>'id' = p_last_one_prize_id
  ) THEN
    RAISE EXCEPTION 'INVALID_INPUT: lastOnePrizeId % is not one of this box''s prizes', p_last_one_prize_id;
  END IF;

  v_status := CASE WHEN p_sale_opens_at IS NOT NULL THEN 'upcoming' ELSE 'preparing' END;

  INSERT INTO boxes (id, status, total_tickets, last_one_prize_id, final_queue_threshold, assignment_commitment, assignment_salt, sale_opens_at)
  VALUES (p_id, v_status, v_total, p_last_one_prize_id, coalesce(p_final_queue_threshold, 0), p_commitment, p_salt, p_sale_opens_at);

  INSERT INTO box_prizes (box_id, prize_id, quantity)
  SELECT p_id, p->>'id', (p->>'quantity')::int FROM jsonb_array_elements(p_prizes) p;

  INSERT INTO tickets (box_id, ticket_no, prize_id, status)
  SELECT p_id, (a->>'ticketNo')::int, a->>'prizeId', 'available' FROM jsonb_array_elements(p_assignment) a;

  PERFORM kuji_audit(p_id, 'create_box', NULL, NULL, NULL, kuji_public_box(p_id));

  RETURN kuji_public_box(p_id);
END;
$$;

-- ============================================================================
-- The central atomic operation. See file header for locking strategy and
-- README "Concurrency model" / "Recovery" for the idempotency contract.
-- ============================================================================
CREATE OR REPLACE FUNCTION kuji_draw(
  p_box_id text,
  p_holder text,
  p_request_id text,
  p_ticket_nos int[],
  p_entitlement_ids uuid[],
  p_payload_hash text
) RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_existing purchase_requests%ROWTYPE;
  v_box boxes%ROWTYPE;
  v_ticket_count int;
  v_available_count int;
  v_remaining_before int;
  v_remaining_after int;
  v_in_queue_phase boolean;
  v_ticket_no int;
  v_entitlement_id uuid;
  v_entitlement entitlements%ROWTYPE;
  v_idx int;
  v_items jsonb := '[]'::jsonb;
  v_prize_id text;
  v_last_one_awarded_now boolean := false;
  v_last_one_prize_id text := NULL;
  v_result jsonb;
BEGIN
  IF p_ticket_nos IS NULL OR array_length(p_ticket_nos, 1) IS NULL THEN
    RAISE EXCEPTION 'INVALID_INPUT: ticketNos must be a non-empty array';
  END IF;
  IF p_entitlement_ids IS NOT NULL AND array_length(p_entitlement_ids, 1) IS NOT NULL
     AND array_length(p_entitlement_ids, 1) <> array_length(p_ticket_nos, 1) THEN
    RAISE EXCEPTION 'ENTITLEMENT_TICKET_MISMATCH: entitlementIds length (%) must match ticketNos length (%)',
      array_length(p_entitlement_ids, 1), array_length(p_ticket_nos, 1);
  END IF;

  -- Idempotency short-circuit: a plain (uncontended) read is fine here
  -- because the box lock below still protects the write path; two
  -- concurrent first-attempts with the same requestId will both pass this
  -- check but only one will win the box lock and the INSERT at the end
  -- (PRIMARY KEY (box_id, request_id)) makes the loser's insert fail, which
  -- surfaces as an ordinary transaction error to that caller — safe to retry.
  SELECT * INTO v_existing FROM purchase_requests WHERE box_id = p_box_id AND request_id = p_request_id;
  IF FOUND THEN
    IF v_existing.payload_hash <> p_payload_hash THEN
      RAISE EXCEPTION 'REQUEST_CONFLICT: requestId % was already used with different input', p_request_id;
    END IF;
    RETURN v_existing.result;
  END IF;

  SELECT * INTO v_box FROM boxes WHERE id = p_box_id FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'BOX_NOT_FOUND: box % does not exist', p_box_id;
  END IF;
  IF v_box.status <> 'on_sale' THEN
    RAISE EXCEPTION 'BOX_NOT_ON_SALE: box % is not on sale (status=%)', p_box_id, v_box.status;
  END IF;

  v_ticket_count := array_length(p_ticket_nos, 1);
  SELECT count(*) INTO v_available_count FROM tickets WHERE box_id = p_box_id AND ticket_no = ANY(p_ticket_nos);
  IF v_available_count <> v_ticket_count THEN
    RAISE EXCEPTION 'TICKET_NOT_FOUND: one or more of tickets % do not exist in box %', p_ticket_nos, p_box_id;
  END IF;

  SELECT count(*) INTO v_remaining_before FROM tickets WHERE box_id = p_box_id AND status <> 'consumed';

  -- Final-segment queue: re-check the holder's turn atomically, inside the
  -- same box-locked transaction that is about to consume tickets, so a turn
  -- can never expire (or be raced) between "check" and "act".
  v_in_queue_phase := v_box.final_queue_threshold > 0 AND v_remaining_before <= v_box.final_queue_threshold;
  IF v_in_queue_phase THEN
    PERFORM 1 FROM queue_entries
      WHERE box_id = p_box_id AND holder = p_holder AND status = 'active' AND turn_expires_at > now()
      FOR UPDATE;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'QUEUE_TURN_REQUIRED: holder % does not hold an active queue turn for box % (remaining=%, threshold=%)',
        p_holder, p_box_id, v_remaining_before, v_box.final_queue_threshold;
    END IF;
  END IF;

  -- Entitlements (optional — omit p_entitlement_ids entirely for
  -- integrations that police draw eligibility themselves; see README
  -- "Entitlements are opt-in").
  IF p_entitlement_ids IS NOT NULL AND array_length(p_entitlement_ids, 1) IS NOT NULL THEN
    FOR v_idx IN 1 .. array_length(p_entitlement_ids, 1) LOOP
      v_entitlement_id := p_entitlement_ids[v_idx];
      SELECT * INTO v_entitlement FROM entitlements WHERE id = v_entitlement_id FOR UPDATE;
      IF NOT FOUND THEN
        RAISE EXCEPTION 'ENTITLEMENT_NOT_FOUND: entitlement % not found', v_entitlement_id;
      END IF;
      IF v_entitlement.box_id <> p_box_id THEN
        RAISE EXCEPTION 'ENTITLEMENT_INVALID: entitlement % does not belong to box %', v_entitlement_id, p_box_id;
      END IF;
      IF v_entitlement.holder <> p_holder THEN
        RAISE EXCEPTION 'FORBIDDEN: entitlement % does not belong to holder %', v_entitlement_id, p_holder;
      END IF;
      IF v_entitlement.status = 'void' THEN
        RAISE EXCEPTION 'ENTITLEMENT_INVALID: entitlement % has been voided', v_entitlement_id;
      END IF;
      IF v_entitlement.status = 'consumed' THEN
        RAISE EXCEPTION 'ENTITLEMENT_ALREADY_CONSUMED: entitlement % has already been used', v_entitlement_id;
      END IF;
      IF v_entitlement.expires_at IS NOT NULL AND v_entitlement.expires_at <= now() THEN
        RAISE EXCEPTION 'ENTITLEMENT_INVALID: entitlement % has expired', v_entitlement_id;
      END IF;
    END LOOP;
  END IF;

  -- Ticket availability (per-ticket, so the error names the offending ticket).
  FOREACH v_ticket_no IN ARRAY p_ticket_nos LOOP
    PERFORM 1 FROM tickets WHERE box_id = p_box_id AND ticket_no = v_ticket_no FOR UPDATE;
    IF EXISTS (SELECT 1 FROM tickets WHERE box_id = p_box_id AND ticket_no = v_ticket_no AND status = 'consumed') THEN
      RAISE EXCEPTION 'TICKET_UNAVAILABLE: ticket % has already been drawn', v_ticket_no;
    END IF;
    IF EXISTS (
      SELECT 1 FROM tickets
      WHERE box_id = p_box_id AND ticket_no = v_ticket_no AND status = 'reserved'
        AND reserved_by <> p_holder AND reserved_until > now()
    ) THEN
      RAISE EXCEPTION 'TICKET_UNAVAILABLE: ticket % is held by another holder', v_ticket_no;
    END IF;
  END LOOP;

  -- Consume tickets, build the result items, write the payout ledger.
  FOR v_idx IN 1 .. v_ticket_count LOOP
    v_ticket_no := p_ticket_nos[v_idx];
    UPDATE tickets
      SET status = 'consumed', reserved_by = NULL, reserved_until = NULL, consumed_at = now()
      WHERE box_id = p_box_id AND ticket_no = v_ticket_no
      RETURNING prize_id INTO v_prize_id;

    v_entitlement_id := NULL;
    IF p_entitlement_ids IS NOT NULL AND array_length(p_entitlement_ids, 1) IS NOT NULL THEN
      v_entitlement_id := p_entitlement_ids[v_idx];
    END IF;

    INSERT INTO payout_ledger (box_id, ticket_no, prize_id, holder, entitlement_id, request_id)
    VALUES (p_box_id, v_ticket_no, v_prize_id, p_holder, v_entitlement_id, p_request_id);

    v_items := v_items || jsonb_build_array(jsonb_build_object('ticketNo', v_ticket_no, 'prizeId', v_prize_id));
  END LOOP;

  -- Single-use entitlements are consumed exactly once, tied to the ticket
  -- they paid for; unlimited ("master") entitlements stay active forever —
  -- see 001_tables.sql comment on the entitlements table.
  IF p_entitlement_ids IS NOT NULL AND array_length(p_entitlement_ids, 1) IS NOT NULL THEN
    FOR v_idx IN 1 .. array_length(p_entitlement_ids, 1) LOOP
      UPDATE entitlements
        SET status = 'consumed', consumed_at = now(), consumed_ticket_no = p_ticket_nos[v_idx]
        WHERE id = p_entitlement_ids[v_idx] AND kind = 'single_use';
    END LOOP;
  END IF;

  SELECT count(*) INTO v_remaining_after FROM tickets WHERE box_id = p_box_id AND status <> 'consumed';

  -- Last-one: the request that drains the box to zero remaining normal
  -- tickets wins it, exactly once (PRIMARY KEY(box_id) on last_one_awards
  -- backs this up at the schema level even if this check had a bug).
  IF v_box.last_one_prize_id IS NOT NULL AND NOT v_box.last_one_awarded AND v_remaining_after = 0 THEN
    INSERT INTO last_one_awards (box_id, ticket_no, prize_id, holder, request_id)
    VALUES (p_box_id, p_ticket_nos[v_ticket_count], v_box.last_one_prize_id, p_holder, p_request_id)
    ON CONFLICT (box_id) DO NOTHING;
    IF FOUND THEN
      v_last_one_awarded_now := true;
      v_last_one_prize_id := v_box.last_one_prize_id;
      UPDATE boxes SET last_one_awarded = true WHERE id = p_box_id;
    END IF;
  END IF;

  UPDATE boxes
    SET status = CASE WHEN v_remaining_after = 0 THEN 'sold_out' ELSE status END,
        updated_at = now()
    WHERE id = p_box_id;

  v_result := jsonb_build_object(
    'boxId', p_box_id,
    'requestId', p_request_id,
    'items', v_items,
    'lastOnePrizeId', v_last_one_prize_id,
    'lastOneAwarded', (v_box.last_one_awarded OR v_last_one_awarded_now),
    'remaining', v_remaining_after
  );

  INSERT INTO purchase_requests (box_id, request_id, action, holder, payload_hash, result)
  VALUES (p_box_id, p_request_id, 'draw', p_holder, p_payload_hash, v_result);

  RETURN v_result;
END;
$$;

-- ============================================================================
-- Reservations
-- ============================================================================
CREATE OR REPLACE FUNCTION kuji_reserve_tickets(
  p_box_id text,
  p_holder text,
  p_request_id text,
  p_ticket_nos int[],
  p_ttl_ms bigint,
  p_payload_hash text
) RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_existing purchase_requests%ROWTYPE;
  v_box boxes%ROWTYPE;
  v_ticket_no int;
  v_reserved_until timestamptz;
  v_result jsonb;
BEGIN
  IF p_ticket_nos IS NULL OR array_length(p_ticket_nos, 1) IS NULL THEN
    RAISE EXCEPTION 'INVALID_INPUT: ticketNos must be a non-empty array';
  END IF;
  -- Millisecond precision end-to-end (matches ReserveTicketsInput.ttlMs in
  -- the core/memory-adapter contract) — deliberately NOT rounded up to whole
  -- seconds, so a short test/integration hold behaves the same here as it
  -- does against the memory adapter.
  IF p_ttl_ms IS NULL OR p_ttl_ms <= 0 OR p_ttl_ms > 1800000 THEN
    RAISE EXCEPTION 'INVALID_INPUT: ttlMs must be between 1 and 1800000';
  END IF;

  SELECT * INTO v_existing FROM purchase_requests WHERE box_id = p_box_id AND request_id = p_request_id;
  IF FOUND THEN
    IF v_existing.payload_hash <> p_payload_hash THEN
      RAISE EXCEPTION 'REQUEST_CONFLICT: requestId % was already used with different input', p_request_id;
    END IF;
    RETURN v_existing.result;
  END IF;

  SELECT * INTO v_box FROM boxes WHERE id = p_box_id FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'BOX_NOT_FOUND: box % does not exist', p_box_id;
  END IF;
  IF v_box.status <> 'on_sale' THEN
    RAISE EXCEPTION 'BOX_NOT_ON_SALE: box % is not on sale (status=%)', p_box_id, v_box.status;
  END IF;

  IF (SELECT count(*) FROM tickets WHERE box_id = p_box_id AND ticket_no = ANY(p_ticket_nos)) <> array_length(p_ticket_nos, 1) THEN
    RAISE EXCEPTION 'TICKET_NOT_FOUND: one or more of tickets % do not exist in box %', p_ticket_nos, p_box_id;
  END IF;

  FOREACH v_ticket_no IN ARRAY p_ticket_nos LOOP
    PERFORM 1 FROM tickets WHERE box_id = p_box_id AND ticket_no = v_ticket_no FOR UPDATE;
    IF EXISTS (SELECT 1 FROM tickets WHERE box_id = p_box_id AND ticket_no = v_ticket_no AND status = 'consumed') THEN
      RAISE EXCEPTION 'TICKET_UNAVAILABLE: ticket % has already been drawn', v_ticket_no;
    END IF;
    IF EXISTS (
      SELECT 1 FROM tickets
      WHERE box_id = p_box_id AND ticket_no = v_ticket_no AND status = 'reserved'
        AND reserved_by <> p_holder AND reserved_until > now()
    ) THEN
      RAISE EXCEPTION 'RESERVATION_CONFLICT: ticket % is already held by another holder', v_ticket_no;
    END IF;
  END LOOP;

  v_reserved_until := now() + make_interval(secs => p_ttl_ms / 1000.0);

  UPDATE tickets SET status = 'reserved', reserved_by = p_holder, reserved_until = v_reserved_until
    WHERE box_id = p_box_id AND ticket_no = ANY(p_ticket_nos);

  v_result := jsonb_build_object(
    'boxId', p_box_id,
    'requestId', p_request_id,
    'holder', p_holder,
    'ticketNos', to_jsonb(p_ticket_nos),
    'reservedUntil', to_jsonb(v_reserved_until)
  );

  INSERT INTO purchase_requests (box_id, request_id, action, holder, payload_hash, result)
  VALUES (p_box_id, p_request_id, 'reserve', p_holder, p_payload_hash, v_result);

  RETURN v_result;
END;
$$;

CREATE OR REPLACE FUNCTION kuji_release_reservation(p_box_id text, p_holder text, p_ticket_nos int[])
RETURNS void
LANGUAGE sql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
  UPDATE tickets
    SET status = 'available', reserved_by = NULL, reserved_until = NULL
    WHERE box_id = p_box_id AND ticket_no = ANY(p_ticket_nos)
      AND status = 'reserved' AND reserved_by = p_holder;
$$;

-- Sweeps expired holds back to 'available'. Safe to run on any schedule
-- (e.g. every few seconds from a worker) — see README "Background jobs".
CREATE OR REPLACE FUNCTION kuji_expire_reservations(p_box_id text DEFAULT NULL, p_limit int DEFAULT 5000)
RETURNS int
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_count int;
BEGIN
  WITH expired AS (
    SELECT box_id, ticket_no FROM tickets
    WHERE status = 'reserved' AND reserved_until <= now()
      AND (p_box_id IS NULL OR box_id = p_box_id)
    LIMIT p_limit
    FOR UPDATE
  )
  UPDATE tickets t SET status = 'available', reserved_by = NULL, reserved_until = NULL
    FROM expired e WHERE t.box_id = e.box_id AND t.ticket_no = e.ticket_no;
  GET DIAGNOSTICS v_count = ROW_COUNT;
  RETURN v_count;
END;
$$;

-- ============================================================================
-- Entitlements
-- ============================================================================
CREATE OR REPLACE FUNCTION kuji_issue_entitlement(
  p_box_id text,
  p_holder text,
  p_kind text,
  p_source text,
  p_external_ref text,
  p_expires_at timestamptz,
  p_max_active_per_holder int DEFAULT NULL
) RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_id uuid;
  v_active_count int;
BEGIN
  IF p_kind NOT IN ('single_use', 'unlimited') THEN
    RAISE EXCEPTION 'INVALID_INPUT: kind must be single_use or unlimited';
  END IF;
  IF NOT EXISTS (SELECT 1 FROM boxes WHERE id = p_box_id) THEN
    RAISE EXCEPTION 'BOX_NOT_FOUND: box % does not exist', p_box_id;
  END IF;

  IF p_max_active_per_holder IS NOT NULL THEN
    -- Advisory lock scoped to (box, holder) so two concurrent issuance calls
    -- for the same holder can't both pass the limit check before either commits.
    PERFORM pg_advisory_xact_lock(hashtextextended(p_box_id || ':' || p_holder, 0));
    SELECT count(*) INTO v_active_count FROM entitlements
      WHERE box_id = p_box_id AND holder = p_holder AND status = 'active';
    IF v_active_count >= p_max_active_per_holder THEN
      RAISE EXCEPTION 'ENTITLEMENT_LIMIT_EXCEEDED: holder % already has % active entitlements for box % (limit %)',
        p_holder, v_active_count, p_box_id, p_max_active_per_holder;
    END IF;
  END IF;

  INSERT INTO entitlements (box_id, holder, kind, source, external_ref, expires_at)
  VALUES (p_box_id, p_holder, p_kind, p_source, p_external_ref, p_expires_at)
  RETURNING id INTO v_id;

  RETURN jsonb_build_object(
    'id', v_id, 'boxId', p_box_id, 'holder', p_holder, 'kind', p_kind,
    'status', 'active', 'source', p_source, 'externalRef', p_external_ref, 'expiresAt', to_jsonb(p_expires_at)
  );
END;
$$;

CREATE OR REPLACE FUNCTION kuji_cancel_entitlement(p_entitlement_id uuid, p_actor text, p_reason text)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_entitlement entitlements%ROWTYPE;
BEGIN
  SELECT * INTO v_entitlement FROM entitlements WHERE id = p_entitlement_id FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'ENTITLEMENT_NOT_FOUND: entitlement % not found', p_entitlement_id;
  END IF;
  IF v_entitlement.status = 'consumed' THEN
    -- Deliberate: canceling an already-consumed entitlement would be
    -- "undo a completed draw", which this engine never does. See README
    -- "What this engine deliberately does not do".
    RAISE EXCEPTION 'ENTITLEMENT_ALREADY_CONSUMED: entitlement % has already been used and cannot be canceled', p_entitlement_id;
  END IF;
  IF v_entitlement.status = 'void' THEN
    RETURN jsonb_build_object('id', v_entitlement.id, 'status', 'void');
  END IF;

  UPDATE entitlements SET status = 'void', voided_at = now(), void_reason = p_reason WHERE id = p_entitlement_id;
  PERFORM kuji_audit(v_entitlement.box_id, 'cancel_entitlement', p_actor, p_reason,
    to_jsonb(v_entitlement), jsonb_build_object('id', v_entitlement.id, 'status', 'void'));

  RETURN jsonb_build_object('id', v_entitlement.id, 'status', 'void');
END;
$$;

----- 파일 끝: db/002_functions.sql -----

----- 파일 시작: db/003_queue_functions.sql -----
-- unakuji-fair-draw: final-segment queue functions
--
-- Turn re-validation for the actual draw happens inside kuji_draw()
-- (002_functions.sql), not here — these functions manage who is waiting /
-- whose turn it currently is, but the draw itself is the sole source of
-- truth for "did this holder's turn really still hold at the instant of
-- drawing".

CREATE OR REPLACE FUNCTION kuji_join_queue(
  p_box_id text,
  p_holder text,
  p_request_id text,
  p_turn_ttl_seconds int,
  p_payload_hash text
) RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_existing purchase_requests%ROWTYPE;
  v_box boxes%ROWTYPE;
  v_remaining int;
  v_next_position bigint;
  v_has_active boolean;
  v_status text;
  v_turn_expires_at timestamptz;
  v_id uuid;
  v_result jsonb;
BEGIN
  IF p_turn_ttl_seconds IS NULL OR p_turn_ttl_seconds <= 0 OR p_turn_ttl_seconds > 1800 THEN
    RAISE EXCEPTION 'INVALID_INPUT: turnTtlSeconds must be between 1 and 1800';
  END IF;

  SELECT * INTO v_existing FROM purchase_requests WHERE box_id = p_box_id AND request_id = p_request_id;
  IF FOUND THEN
    IF v_existing.payload_hash <> p_payload_hash THEN
      RAISE EXCEPTION 'REQUEST_CONFLICT: requestId % was already used with different input', p_request_id;
    END IF;
    RETURN v_existing.result;
  END IF;

  SELECT * INTO v_box FROM boxes WHERE id = p_box_id FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'BOX_NOT_FOUND: box % does not exist', p_box_id;
  END IF;
  IF v_box.status <> 'on_sale' THEN
    RAISE EXCEPTION 'BOX_NOT_ON_SALE: box % is not on sale (status=%)', p_box_id, v_box.status;
  END IF;

  SELECT count(*) INTO v_remaining FROM tickets WHERE box_id = p_box_id AND status <> 'consumed';
  IF v_box.final_queue_threshold = 0 OR v_remaining > v_box.final_queue_threshold THEN
    -- This is the fix for the join-guard gap this engine is modeled on: a
    -- box outside its final segment must reject joins outright, not just
    -- hide the "join" button in a UI a direct API call can bypass.
    RAISE EXCEPTION 'QUEUE_NOT_REQUIRED: box % is not in its final-segment queue phase (remaining=%, threshold=%)',
      p_box_id, v_remaining, v_box.final_queue_threshold;
  END IF;

  IF EXISTS (SELECT 1 FROM queue_entries WHERE box_id = p_box_id AND holder = p_holder AND status IN ('waiting','active')) THEN
    RAISE EXCEPTION 'QUEUE_ALREADY_JOINED: holder % already has an open queue entry for box %', p_holder, p_box_id;
  END IF;

  -- Expire any stale active entry for this box before deciding whether the
  -- new entry can start active immediately.
  UPDATE queue_entries SET status = 'expired', updated_at = now()
    WHERE box_id = p_box_id AND status = 'active' AND turn_expires_at <= now();

  SELECT EXISTS (SELECT 1 FROM queue_entries WHERE box_id = p_box_id AND status = 'active') INTO v_has_active;
  SELECT coalesce(max("position"), 0) + 1 INTO v_next_position FROM queue_entries WHERE box_id = p_box_id;

  IF v_has_active THEN
    v_status := 'waiting';
    v_turn_expires_at := NULL;
  ELSE
    v_status := 'active';
    v_turn_expires_at := now() + make_interval(secs => p_turn_ttl_seconds);
  END IF;

  INSERT INTO queue_entries (box_id, holder, "position", status, turn_ttl_seconds, turn_expires_at)
  VALUES (p_box_id, p_holder, v_next_position, v_status, p_turn_ttl_seconds, v_turn_expires_at)
  RETURNING id INTO v_id;

  v_result := jsonb_build_object(
    'id', v_id, 'boxId', p_box_id, 'holder', p_holder, 'position', v_next_position,
    'status', v_status, 'turnExpiresAt', to_jsonb(v_turn_expires_at)
  );

  INSERT INTO purchase_requests (box_id, request_id, action, holder, payload_hash, result)
  VALUES (p_box_id, p_request_id, 'join_queue', p_holder, p_payload_hash, v_result);

  RETURN v_result;
END;
$$;

-- Promotes the earliest 'waiting' entry (if any) for a box to 'active',
-- granting it the turn_ttl_seconds it was promised at join time. Shared by
-- kuji_leave_queue and kuji_advance_queue. Caller must already hold the box
-- lock (FOR UPDATE on boxes) — both call sites do.
CREATE OR REPLACE FUNCTION kuji_promote_next_in_queue(p_box_id text)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_next queue_entries%ROWTYPE;
BEGIN
  SELECT * INTO v_next FROM queue_entries
    WHERE box_id = p_box_id AND status = 'waiting'
    ORDER BY "position" ASC LIMIT 1 FOR UPDATE;
  IF FOUND THEN
    UPDATE queue_entries
      SET status = 'active', turn_expires_at = now() + make_interval(secs => v_next.turn_ttl_seconds), updated_at = now()
      WHERE id = v_next.id;
  END IF;
END;
$$;

CREATE OR REPLACE FUNCTION kuji_leave_queue(p_box_id text, p_holder text)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_box boxes%ROWTYPE;
  v_entry queue_entries%ROWTYPE;
BEGIN
  SELECT * INTO v_box FROM boxes WHERE id = p_box_id FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'BOX_NOT_FOUND: box % does not exist', p_box_id;
  END IF;

  SELECT * INTO v_entry FROM queue_entries
    WHERE box_id = p_box_id AND holder = p_holder AND status IN ('waiting','active')
    FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'QUEUE_ENTRY_NOT_FOUND: holder % has no open queue entry for box %', p_holder, p_box_id;
  END IF;

  UPDATE queue_entries SET status = 'left', updated_at = now() WHERE id = v_entry.id;

  IF v_entry.status = 'active' THEN
    PERFORM kuji_promote_next_in_queue(p_box_id);
  END IF;

  RETURN jsonb_build_object('id', v_entry.id, 'status', 'left');
END;
$$;

CREATE OR REPLACE FUNCTION kuji_queue_status(p_box_id text, p_holder text)
RETURNS jsonb
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
  SELECT coalesce(
    (SELECT jsonb_build_object(
        'id', id, 'boxId', box_id, 'holder', holder, 'position', "position",
        'status', status, 'turnExpiresAt', to_jsonb(turn_expires_at)
      )
      FROM queue_entries
      WHERE box_id = p_box_id AND holder = p_holder AND status IN ('waiting','active')
      ORDER BY joined_at DESC LIMIT 1),
    jsonb_build_object('boxId', p_box_id, 'holder', p_holder, 'status', 'not_queued')
  );
$$;

-- Sweeps every box: expires stale active turns, then promotes the next
-- waiting entry for each box left without one. Intended to run on a short
-- interval (a few seconds) from a background worker — see README
-- "Background jobs". Not required for correctness (kuji_draw always
-- re-validates), only for keeping the queue moving promptly.
CREATE OR REPLACE FUNCTION kuji_advance_queue(p_box_id text DEFAULT NULL, p_limit int DEFAULT 1000)
RETURNS int
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_box_id text;
  v_count int := 0;
BEGIN
  FOR v_box_id IN
    SELECT DISTINCT qe.box_id FROM queue_entries qe
    JOIN boxes b ON b.id = qe.box_id
    WHERE qe.status = 'active' AND qe.turn_expires_at <= now()
      AND (p_box_id IS NULL OR qe.box_id = p_box_id)
    LIMIT p_limit
  LOOP
    PERFORM 1 FROM boxes WHERE id = v_box_id FOR UPDATE;
    UPDATE queue_entries SET status = 'expired', updated_at = now()
      WHERE box_id = v_box_id AND status = 'active' AND turn_expires_at <= now();
    PERFORM kuji_promote_next_in_queue(v_box_id);
    v_count := v_count + 1;
  END LOOP;
  RETURN v_count;
END;
$$;

----- 파일 끝: db/003_queue_functions.sql -----

----- 파일 시작: db/004_admin_functions.sql -----
-- unakuji-fair-draw: lifecycle, recovery/consistency, and result-query functions

-- ============================================================================
-- Lifecycle
-- ============================================================================
CREATE OR REPLACE FUNCTION kuji_open_box(p_box_id text, p_actor text DEFAULT NULL)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_box boxes%ROWTYPE;
BEGIN
  SELECT * INTO v_box FROM boxes WHERE id = p_box_id FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'BOX_NOT_FOUND: box % does not exist', p_box_id;
  END IF;
  IF v_box.status NOT IN ('preparing', 'upcoming') THEN
    RAISE EXCEPTION 'INVALID_STATE: box % cannot open from status %', p_box_id, v_box.status;
  END IF;
  UPDATE boxes SET status = 'on_sale', updated_at = now() WHERE id = p_box_id;
  PERFORM kuji_audit(p_box_id, 'open_box', p_actor, NULL, to_jsonb(v_box.status), '"on_sale"'::jsonb);
  RETURN kuji_public_box(p_box_id);
END;
$$;

CREATE OR REPLACE FUNCTION kuji_schedule_box_open(p_box_id text, p_opens_at timestamptz, p_actor text DEFAULT NULL)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_box boxes%ROWTYPE;
BEGIN
  SELECT * INTO v_box FROM boxes WHERE id = p_box_id FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'BOX_NOT_FOUND: box % does not exist', p_box_id;
  END IF;
  IF v_box.status NOT IN ('preparing', 'upcoming') THEN
    RAISE EXCEPTION 'INVALID_STATE: box % cannot be scheduled from status %', p_box_id, v_box.status;
  END IF;
  IF p_opens_at <= now() THEN
    RAISE EXCEPTION 'INVALID_INPUT: opensAt must be in the future';
  END IF;
  UPDATE boxes SET status = 'upcoming', sale_opens_at = p_opens_at, updated_at = now() WHERE id = p_box_id;
  PERFORM kuji_audit(p_box_id, 'schedule_box_open', p_actor, NULL, to_jsonb(v_box.sale_opens_at), to_jsonb(p_opens_at));
  RETURN kuji_public_box(p_box_id);
END;
$$;

-- Idempotent bulk transition: call this from whatever scheduler the host
-- already runs (pg_cron, a worker queue, a plain setInterval) — the engine
-- does not register its own cron job. Only ever moves upcoming -> on_sale;
-- never touches paused/canceled/ended boxes.
CREATE OR REPLACE FUNCTION kuji_open_scheduled_boxes()
RETURNS int
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_count int;
BEGIN
  WITH due AS (
    SELECT id FROM boxes WHERE status = 'upcoming' AND sale_opens_at <= now() FOR UPDATE
  )
  UPDATE boxes SET status = 'on_sale', updated_at = now() WHERE id IN (SELECT id FROM due);
  GET DIAGNOSTICS v_count = ROW_COUNT;
  RETURN v_count;
END;
$$;

CREATE OR REPLACE FUNCTION kuji_pause_box(p_box_id text, p_actor text, p_reason text)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_box boxes%ROWTYPE;
BEGIN
  SELECT * INTO v_box FROM boxes WHERE id = p_box_id FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'BOX_NOT_FOUND: box % does not exist', p_box_id;
  END IF;
  IF v_box.status <> 'on_sale' THEN
    RAISE EXCEPTION 'INVALID_STATE: box % cannot be paused from status %', p_box_id, v_box.status;
  END IF;
  UPDATE boxes SET status = 'paused', paused_at = now(), pause_reason = p_reason, updated_at = now() WHERE id = p_box_id;
  PERFORM kuji_audit(p_box_id, 'pause_box', p_actor, p_reason, '"on_sale"'::jsonb, '"paused"'::jsonb);
  RETURN kuji_public_box(p_box_id);
END;
$$;

CREATE OR REPLACE FUNCTION kuji_resume_box(p_box_id text, p_actor text)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_box boxes%ROWTYPE;
BEGIN
  SELECT * INTO v_box FROM boxes WHERE id = p_box_id FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'BOX_NOT_FOUND: box % does not exist', p_box_id;
  END IF;
  IF v_box.status <> 'paused' THEN
    RAISE EXCEPTION 'INVALID_STATE: box % cannot resume from status %', p_box_id, v_box.status;
  END IF;
  UPDATE boxes SET status = 'on_sale', paused_at = NULL, pause_reason = NULL, updated_at = now() WHERE id = p_box_id;
  PERFORM kuji_audit(p_box_id, 'resume_box', p_actor, NULL, '"paused"'::jsonb, '"on_sale"'::jsonb);
  RETURN kuji_public_box(p_box_id);
END;
$$;

-- p_allow_real_sales must be explicitly true to cancel a box that has already
-- paid out at least one ticket — mirrors the "don't silently wipe a box with
-- real draws already on it" guard this engine is modeled on.
CREATE OR REPLACE FUNCTION kuji_cancel_box(p_box_id text, p_actor text, p_reason text, p_allow_real_sales boolean DEFAULT false)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_box boxes%ROWTYPE;
  v_has_sales boolean;
BEGIN
  SELECT * INTO v_box FROM boxes WHERE id = p_box_id FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'BOX_NOT_FOUND: box % does not exist', p_box_id;
  END IF;
  IF v_box.status IN ('canceled', 'ended') THEN
    RAISE EXCEPTION 'INVALID_STATE: box % is already terminal (status %)', p_box_id, v_box.status;
  END IF;

  SELECT EXISTS (SELECT 1 FROM payout_ledger WHERE box_id = p_box_id) INTO v_has_sales;
  IF v_has_sales AND NOT p_allow_real_sales THEN
    RAISE EXCEPTION 'INVALID_STATE: box % already has completed draws; pass allowRealSales to cancel anyway', p_box_id;
  END IF;

  UPDATE boxes SET status = 'canceled', canceled_at = now(), cancel_reason = p_reason, updated_at = now() WHERE id = p_box_id;
  PERFORM kuji_audit(p_box_id, 'cancel_box', p_actor, p_reason, to_jsonb(v_box.status), '"canceled"'::jsonb);
  RETURN kuji_public_box(p_box_id);
END;
$$;

-- Force-close: for operational situations (e.g. a deadlocked final queue)
-- where the box must stop accepting new draws right now regardless of
-- remaining tickets. Existing results remain fully queryable afterward.
CREATE OR REPLACE FUNCTION kuji_close_box(p_box_id text, p_actor text, p_reason text)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_box boxes%ROWTYPE;
BEGIN
  SELECT * INTO v_box FROM boxes WHERE id = p_box_id FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'BOX_NOT_FOUND: box % does not exist', p_box_id;
  END IF;
  IF v_box.status IN ('canceled', 'ended') THEN
    RAISE EXCEPTION 'INVALID_STATE: box % is already terminal (status %)', p_box_id, v_box.status;
  END IF;
  UPDATE boxes SET status = 'ended', ended_at = now(), end_reason = p_reason, updated_at = now() WHERE id = p_box_id;
  PERFORM kuji_audit(p_box_id, 'close_box', p_actor, p_reason, to_jsonb(v_box.status), '"ended"'::jsonb);
  RETURN kuji_public_box(p_box_id);
END;
$$;

-- ============================================================================
-- Result queries — both are scoped to p_holder so a caller can never read
-- another holder's request result or payout history (IDOR protection lives
-- in the WHERE clause, not in the caller remembering to check).
-- ============================================================================
CREATE OR REPLACE FUNCTION kuji_get_result(p_box_id text, p_holder text, p_request_id text)
RETURNS jsonb
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
  SELECT result FROM purchase_requests
  WHERE box_id = p_box_id AND request_id = p_request_id AND holder = p_holder;
$$;

CREATE OR REPLACE FUNCTION kuji_get_holder_results(p_box_id text, p_holder text)
RETURNS jsonb
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
  SELECT coalesce(jsonb_agg(jsonb_build_object(
      'ticketNo', ticket_no, 'prizeId', prize_id, 'requestId', request_id, 'drawnAt', to_jsonb(drawn_at)
    ) ORDER BY drawn_at), '[]'::jsonb)
  FROM payout_ledger WHERE box_id = p_box_id AND holder = p_holder;
$$;

-- ============================================================================
-- Consistency check. Read-only; never mutates. Run it on demand or on a
-- schedule (e.g. after each box closes) — see README "Recovery & consistency".
-- ============================================================================
CREATE OR REPLACE FUNCTION kuji_reconcile(p_box_id text)
RETURNS jsonb
LANGUAGE plpgsql
STABLE
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  v_box boxes%ROWTYPE;
  v_ticket_count int;
  v_consumed_count int;
  v_payout_count int;
  v_last_one_count int;
  v_prize_overdraw_count int;
  v_commitment_ok boolean;
  v_current_assignment jsonb;
  v_issues text[] := ARRAY[]::text[];
BEGIN
  SELECT * INTO v_box FROM boxes WHERE id = p_box_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'BOX_NOT_FOUND: box % does not exist', p_box_id;
  END IF;

  SELECT count(*) INTO v_ticket_count FROM tickets WHERE box_id = p_box_id;
  IF v_ticket_count <> v_box.total_tickets THEN
    v_issues := v_issues || format('ticket row count %s does not match boxes.total_tickets %s', v_ticket_count, v_box.total_tickets);
  END IF;

  SELECT count(*) INTO v_consumed_count FROM tickets WHERE box_id = p_box_id AND status = 'consumed';
  SELECT count(*) INTO v_payout_count FROM payout_ledger WHERE box_id = p_box_id;
  IF v_consumed_count <> v_payout_count THEN
    v_issues := v_issues || format('consumed ticket count %s does not match payout_ledger row count %s', v_consumed_count, v_payout_count);
  END IF;

  SELECT count(*) INTO v_prize_overdraw_count
    FROM (
      SELECT pl.prize_id, count(*) AS paid, bp.quantity
      FROM payout_ledger pl JOIN box_prizes bp ON bp.box_id = pl.box_id AND bp.prize_id = pl.prize_id
      WHERE pl.box_id = p_box_id
      GROUP BY pl.prize_id, bp.quantity
      HAVING count(*) > bp.quantity
    ) overdrawn;
  IF v_prize_overdraw_count > 0 THEN
    v_issues := v_issues || format('%s prize(s) paid out more than their configured quantity', v_prize_overdraw_count);
  END IF;

  SELECT count(*) INTO v_last_one_count FROM last_one_awards WHERE box_id = p_box_id;
  IF v_last_one_count > 1 THEN
    v_issues := v_issues || format('last_one_awards has %s rows for this box (should be at most 1)', v_last_one_count);
  END IF;
  IF v_box.last_one_awarded AND v_last_one_count = 0 THEN
    v_issues := v_issues || 'boxes.last_one_awarded is true but no last_one_awards row exists'::text;
  END IF;

  SELECT jsonb_agg(jsonb_build_object('ticketNo', ticket_no, 'prizeId', prize_id) ORDER BY ticket_no)
    INTO v_current_assignment
    FROM tickets WHERE box_id = p_box_id;
  -- Recompute the same commitment src/core/integrity.ts would, entirely in
  -- SQL, so this check has no dependency on the Node process being reachable.
  v_commitment_ok := (
    encode(digest(
      p_box_id || ' ' || v_box.assignment_salt || ' ' ||
      (SELECT string_agg(format('%s:%s', (t->>'ticketNo')::int, t->>'prizeId'), '|' ORDER BY (t->>'ticketNo')::int)
       FROM jsonb_array_elements(v_current_assignment) t),
      'sha256'), 'hex')
    = v_box.assignment_commitment
  );
  IF NOT v_commitment_ok THEN
    v_issues := v_issues || 'assignment_commitment does not match current ticket rows (possible tampering or a bug)'::text;
  END IF;

  RETURN jsonb_build_object(
    'boxId', p_box_id,
    'healthy', (array_length(v_issues, 1) IS NULL),
    'issues', to_jsonb(v_issues),
    'ticketCount', v_ticket_count,
    'consumedCount', v_consumed_count,
    'payoutCount', v_payout_count,
    'commitmentOk', v_commitment_ok
  );
END;
$$;

----- 파일 끝: db/004_admin_functions.sql -----

----- 파일 시작: db/005_roles.sql -----
-- unakuji-fair-draw: least-privilege application roles.
--
-- Optional in the sense that you may wire your own credentials instead, but
-- the model here is the one the engine is designed around, and skipping it
-- leaves every function callable by every role (see the REVOKE below).
--
-- Two roles, because two very different things talk to this schema:
--
--   unakuji_app    the participant-facing server: draws, reservations, queue
--   unakuji_admin  operator tooling: creating, opening, pausing, canceling
--
-- Neither role is granted anything on any table. Not INSERT, not UPDATE, not
-- even SELECT. The kuji_* functions in 002-004 are SECURITY DEFINER, so they
-- run with their owner's rights and reach the tables themselves; the callers
-- only ever hold EXECUTE.
--
-- That is the whole point of this file. While the functions ran as the caller,
-- a role that could run kuji_draw() necessarily also held INSERT on
-- payout_ledger and purchase_requests — and kuji_get_result() hands back
-- purchase_requests.result verbatim, so that INSERT was a second, unaudited
-- way to produce a result. No amount of narrowing the grants closed that:
-- the privileges the functions needed *were* the intervention path. Granting
-- EXECUTE and nothing else is what closes it. Now the only way to write a
-- draw result is kuji_draw(), and the only way to read another participant's
-- is not to.
--
-- Withholding SELECT matters too: tickets.prize_id for an undrawn ticket is
-- exactly the information a participant must not have, and a role with blanket
-- SELECT has it regardless of what the public API returns.
--
-- The kuji_* functions carry no authorization check of their own. p_actor is
-- a label written to audit_log, not a verified identity — this engine does not
-- know who your users are. So EXECUTE is the authorization, and the split
-- below is where it is decided.

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_roles WHERE rolname = 'unakuji_app') THEN
    CREATE ROLE unakuji_app LOGIN PASSWORD 'change_me_before_deploying';
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_roles WHERE rolname = 'unakuji_admin') THEN
    CREATE ROLE unakuji_admin LOGIN PASSWORD 'change_me_before_deploying';
  END IF;
END
$$;

-- SECURITY DEFINER functions resolve unqualified names through the search_path
-- pinned on each function (pg_catalog, public). Nobody but the owner may create
-- objects in public, so that path cannot be shadowed. Postgres 15+ does this by
-- default; the REVOKE keeps older servers honest.
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO unakuji_app, unakuji_admin;

-- Take back the default. Postgres grants EXECUTE on functions to PUBLIC, so
-- without this every grant below is decoration and every role can call
-- everything. Scoped to kuji_% on purpose: extensions install into public too,
-- and revoking EXECUTE on ALL FUNCTIONS takes pgcrypto's digest() away as well,
-- which breaks kuji_reconcile(). Looping also means a function added later is
-- covered by re-running this file, with no list here to forget to update.
DO $$
DECLARE fn record;
BEGIN
  FOR fn IN
    SELECT p.oid::regprocedure AS signature
      FROM pg_catalog.pg_proc p
      JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
     WHERE n.nspname = 'public'
       AND p.proname LIKE 'kuji\_%'
  LOOP
    EXECUTE format('REVOKE EXECUTE ON FUNCTION %s FROM PUBLIC', fn.signature);
  END LOOP;
END
$$;

-- ---------------------------------------------------------------------------
-- unakuji_app — what a participant's request can reach.
-- ---------------------------------------------------------------------------
GRANT EXECUTE ON FUNCTION
  kuji_public_box,
  kuji_draw, kuji_reserve_tickets, kuji_release_reservation, kuji_expire_reservations,
  kuji_issue_entitlement,
  kuji_join_queue, kuji_leave_queue, kuji_queue_status,
  kuji_promote_next_in_queue, kuji_advance_queue,
  kuji_get_result, kuji_get_holder_results, kuji_reconcile
TO unakuji_app;

-- Deliberately absent: kuji_create_box and every box lifecycle function. A
-- compromised participant server cannot mint a new assignment to draw from,
-- nor cancel or close a box that is selling.

-- ---------------------------------------------------------------------------
-- unakuji_admin — operator tooling. Changes a box's lifecycle, never a result.
-- ---------------------------------------------------------------------------
GRANT EXECUTE ON FUNCTION
  kuji_public_box,
  kuji_create_box,
  kuji_open_box, kuji_schedule_box_open, kuji_open_scheduled_boxes,
  kuji_pause_box, kuji_resume_box, kuji_cancel_box, kuji_close_box,
  kuji_cancel_entitlement,
  kuji_get_result, kuji_get_holder_results, kuji_reconcile
TO unakuji_admin;

-- Deliberately absent: kuji_draw and the reservation and queue functions. An
-- operator account cannot draw on a participant's behalf.

-- kuji_audit is not granted to anyone: it is called by the other functions,
-- which reach it as their owner. Nothing should be writing audit rows directly.

----- 파일 끝: db/005_roles.sql -----