423 lines
13 KiB
TypeScript
423 lines
13 KiB
TypeScript
import crypto from 'crypto'
|
||
import { Prisma } from '@prisma/client'
|
||
import { prisma } from '@/lib/prisma'
|
||
|
||
export const STANDARD_MODEL_WHITE_BACKGROUND_BARCODE_OPERATION = {
|
||
UPLOAD: 'standard-model-white-background.by-barcode.upload',
|
||
CLEANUP: 'standard-model-white-background.by-barcode.cleanup',
|
||
UPLOAD_BY_SPU: 'standard-model-white-background.by-spu.upload',
|
||
} as const
|
||
|
||
export type BarcodeOperationType =
|
||
typeof STANDARD_MODEL_WHITE_BACKGROUND_BARCODE_OPERATION[keyof typeof STANDARD_MODEL_WHITE_BACKGROUND_BARCODE_OPERATION]
|
||
|
||
export class ExternalWhiteBackgroundBarcodeError extends Error {
|
||
status: number
|
||
code: string
|
||
|
||
constructor(status: number, code: string, message: string) {
|
||
super(message)
|
||
this.status = status
|
||
this.code = code
|
||
}
|
||
}
|
||
|
||
export type ResolvedStandardModelSkuByBarcode = {
|
||
barcode: string
|
||
materialId: string
|
||
skuId: string
|
||
skuName: string
|
||
resolvedSpu: string
|
||
resolvedSkuName: string
|
||
}
|
||
|
||
let supportTablesInitialized = false
|
||
|
||
async function executeRaw(sql: string) {
|
||
try {
|
||
await prisma.$executeRawUnsafe(sql)
|
||
} catch (error) {
|
||
console.error('[external-white-background-by-barcode] 执行 SQL 失败:', error)
|
||
}
|
||
}
|
||
|
||
export async function ensureStandardModelWhiteBackgroundBarcodeSupportTables() {
|
||
if (supportTablesInitialized) {
|
||
return
|
||
}
|
||
|
||
await executeRaw(`
|
||
CREATE TABLE IF NOT EXISTS "material_sku_barcodes" (
|
||
"id" TEXT PRIMARY KEY,
|
||
"sku_id" TEXT NOT NULL,
|
||
"barcode" TEXT NOT NULL,
|
||
"deleted_at" TIMESTAMP(3),
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
)
|
||
`)
|
||
|
||
await executeRaw(`
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1
|
||
FROM pg_constraint
|
||
WHERE conname = 'material_sku_barcodes_sku_id_fkey'
|
||
) THEN
|
||
ALTER TABLE "material_sku_barcodes"
|
||
ADD CONSTRAINT "material_sku_barcodes_sku_id_fkey"
|
||
FOREIGN KEY ("sku_id") REFERENCES "material_skus"("id")
|
||
ON DELETE CASCADE ON UPDATE CASCADE;
|
||
END IF;
|
||
END
|
||
$$;
|
||
`)
|
||
|
||
await executeRaw(`
|
||
CREATE UNIQUE INDEX IF NOT EXISTS "material_sku_barcodes_barcode_active_key"
|
||
ON "material_sku_barcodes" ("barcode")
|
||
WHERE "deleted_at" IS NULL
|
||
`)
|
||
await executeRaw(`CREATE INDEX IF NOT EXISTS "material_sku_barcodes_barcode_idx" ON "material_sku_barcodes" ("barcode")`)
|
||
await executeRaw(`CREATE INDEX IF NOT EXISTS "material_sku_barcodes_sku_id_idx" ON "material_sku_barcodes" ("sku_id")`)
|
||
await executeRaw(`CREATE INDEX IF NOT EXISTS "material_sku_barcodes_deleted_at_idx" ON "material_sku_barcodes" ("deleted_at")`)
|
||
|
||
await executeRaw(`
|
||
CREATE TABLE IF NOT EXISTS "external_api_idempotency_records" (
|
||
"id" TEXT PRIMARY KEY,
|
||
"api_key_id" TEXT NOT NULL,
|
||
"operation_type" TEXT NOT NULL,
|
||
"client_request_id" TEXT NOT NULL,
|
||
"request_hash" TEXT NOT NULL,
|
||
"status" TEXT NOT NULL,
|
||
"response_status" INTEGER,
|
||
"response_body" JSONB,
|
||
"completed_at" TIMESTAMP(3),
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
)
|
||
`)
|
||
|
||
await executeRaw(`
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1
|
||
FROM pg_constraint
|
||
WHERE conname = 'external_api_idempotency_records_api_key_id_fkey'
|
||
) THEN
|
||
ALTER TABLE "external_api_idempotency_records"
|
||
ADD CONSTRAINT "external_api_idempotency_records_api_key_id_fkey"
|
||
FOREIGN KEY ("api_key_id") REFERENCES "api_keys"("id")
|
||
ON DELETE CASCADE ON UPDATE CASCADE;
|
||
END IF;
|
||
END
|
||
$$;
|
||
`)
|
||
|
||
await executeRaw(`
|
||
CREATE UNIQUE INDEX IF NOT EXISTS "external_api_idempotency_records_api_key_operation_client_key"
|
||
ON "external_api_idempotency_records" ("api_key_id", "operation_type", "client_request_id")
|
||
`)
|
||
await executeRaw(`CREATE INDEX IF NOT EXISTS "external_api_idempotency_records_api_key_id_idx" ON "external_api_idempotency_records" ("api_key_id")`)
|
||
await executeRaw(`CREATE INDEX IF NOT EXISTS "external_api_idempotency_records_operation_type_idx" ON "external_api_idempotency_records" ("operation_type")`)
|
||
await executeRaw(`CREATE INDEX IF NOT EXISTS "external_api_idempotency_records_client_request_id_idx" ON "external_api_idempotency_records" ("client_request_id")`)
|
||
await executeRaw(`CREATE INDEX IF NOT EXISTS "external_api_idempotency_records_status_idx" ON "external_api_idempotency_records" ("status")`)
|
||
await executeRaw(`CREATE INDEX IF NOT EXISTS "external_api_idempotency_records_created_at_idx" ON "external_api_idempotency_records" ("created_at")`)
|
||
|
||
supportTablesInitialized = true
|
||
}
|
||
|
||
export function normalizeExternalBarcode(value: unknown) {
|
||
return String(value || '').trim()
|
||
}
|
||
|
||
export function stableHashPayload(payload: unknown) {
|
||
return crypto
|
||
.createHash('sha256')
|
||
.update(JSON.stringify(sortPayload(payload)))
|
||
.digest('hex')
|
||
}
|
||
|
||
function sortPayload(payload: unknown): unknown {
|
||
if (Array.isArray(payload)) {
|
||
return payload.map(sortPayload)
|
||
}
|
||
|
||
if (payload && typeof payload === 'object') {
|
||
return Object.keys(payload as Record<string, unknown>)
|
||
.sort()
|
||
.reduce((acc, key) => {
|
||
acc[key] = sortPayload((payload as Record<string, unknown>)[key])
|
||
return acc
|
||
}, {} as Record<string, unknown>)
|
||
}
|
||
|
||
return payload
|
||
}
|
||
|
||
export async function startExternalApiIdempotencyRecord(input: {
|
||
apiKeyId: string
|
||
operationType: BarcodeOperationType
|
||
clientRequestId: string
|
||
requestHash: string
|
||
}) {
|
||
await ensureStandardModelWhiteBackgroundBarcodeSupportTables()
|
||
|
||
if (!input.clientRequestId.trim()) {
|
||
throw new ExternalWhiteBackgroundBarcodeError(400, 'INVALID_CLIENT_REQUEST_ID', 'clientRequestId 不能为空')
|
||
}
|
||
|
||
try {
|
||
const result = await prisma.$transaction(
|
||
async (tx: any) => {
|
||
const existing = await tx.externalApiIdempotencyRecord.findUnique({
|
||
where: {
|
||
apiKeyId_operationType_clientRequestId: {
|
||
apiKeyId: input.apiKeyId,
|
||
operationType: input.operationType,
|
||
clientRequestId: input.clientRequestId,
|
||
},
|
||
},
|
||
})
|
||
|
||
if (existing) {
|
||
if (existing.requestHash !== input.requestHash) {
|
||
throw new ExternalWhiteBackgroundBarcodeError(
|
||
409,
|
||
'IDEMPOTENCY_CONFLICT',
|
||
'同一个 clientRequestId 已用于不同请求，请更换 clientRequestId',
|
||
)
|
||
}
|
||
|
||
if (existing.status === 'succeeded' && existing.responseBody && existing.responseStatus) {
|
||
return {
|
||
mode: 'replay' as const,
|
||
record: existing,
|
||
}
|
||
}
|
||
|
||
if (existing.status === 'failed') {
|
||
const record = await tx.externalApiIdempotencyRecord.update({
|
||
where: { id: existing.id },
|
||
data: {
|
||
status: 'processing',
|
||
responseStatus: null,
|
||
responseBody: Prisma.JsonNull,
|
||
completedAt: null,
|
||
},
|
||
})
|
||
|
||
return {
|
||
mode: 'created' as const,
|
||
record,
|
||
}
|
||
}
|
||
|
||
throw new ExternalWhiteBackgroundBarcodeError(
|
||
409,
|
||
'IDEMPOTENCY_CONFLICT',
|
||
'同一个 clientRequestId 的请求仍在处理中，请稍后重试',
|
||
)
|
||
}
|
||
|
||
const record = await tx.externalApiIdempotencyRecord.create({
|
||
data: {
|
||
apiKeyId: input.apiKeyId,
|
||
operationType: input.operationType,
|
||
clientRequestId: input.clientRequestId,
|
||
requestHash: input.requestHash,
|
||
status: 'processing',
|
||
},
|
||
})
|
||
|
||
return {
|
||
mode: 'created' as const,
|
||
record,
|
||
}
|
||
},
|
||
{
|
||
isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
|
||
},
|
||
)
|
||
|
||
return result
|
||
} catch (error: any) {
|
||
if (error instanceof ExternalWhiteBackgroundBarcodeError) {
|
||
throw error
|
||
}
|
||
|
||
if (error?.code === 'P2002') {
|
||
const existing = await (prisma as any).externalApiIdempotencyRecord.findUnique({
|
||
where: {
|
||
apiKeyId_operationType_clientRequestId: {
|
||
apiKeyId: input.apiKeyId,
|
||
operationType: input.operationType,
|
||
clientRequestId: input.clientRequestId,
|
||
},
|
||
},
|
||
})
|
||
|
||
if (existing?.requestHash !== input.requestHash) {
|
||
throw new ExternalWhiteBackgroundBarcodeError(
|
||
409,
|
||
'IDEMPOTENCY_CONFLICT',
|
||
'同一个 clientRequestId 已用于不同请求，请更换 clientRequestId',
|
||
)
|
||
}
|
||
|
||
if (existing?.status === 'succeeded' && existing.responseBody && existing.responseStatus) {
|
||
return {
|
||
mode: 'replay' as const,
|
||
record: existing,
|
||
}
|
||
}
|
||
|
||
if (existing?.status === 'failed') {
|
||
const record = await (prisma as any).externalApiIdempotencyRecord.update({
|
||
where: { id: existing.id },
|
||
data: {
|
||
status: 'processing',
|
||
responseStatus: null,
|
||
responseBody: Prisma.JsonNull,
|
||
completedAt: null,
|
||
},
|
||
})
|
||
|
||
return {
|
||
mode: 'created' as const,
|
||
record,
|
||
}
|
||
}
|
||
|
||
throw new ExternalWhiteBackgroundBarcodeError(
|
||
409,
|
||
'IDEMPOTENCY_CONFLICT',
|
||
'同一个 clientRequestId 的请求仍在处理中，请稍后重试',
|
||
)
|
||
}
|
||
|
||
throw error
|
||
}
|
||
}
|
||
|
||
export async function completeExternalApiIdempotencyRecord(input: {
|
||
recordId: string
|
||
responseStatus: number
|
||
responseBody: unknown
|
||
}) {
|
||
await (prisma as any).externalApiIdempotencyRecord.update({
|
||
where: { id: input.recordId },
|
||
data: {
|
||
status: 'succeeded',
|
||
responseStatus: input.responseStatus,
|
||
responseBody: input.responseBody,
|
||
completedAt: new Date(),
|
||
},
|
||
})
|
||
}
|
||
|
||
export async function failExternalApiIdempotencyRecord(recordId: string) {
|
||
try {
|
||
await (prisma as any).externalApiIdempotencyRecord.update({
|
||
where: { id: recordId },
|
||
data: {
|
||
status: 'failed',
|
||
},
|
||
})
|
||
} catch (error) {
|
||
console.error('[external-white-background-by-barcode] 更新幂等失败状态失败:', error)
|
||
}
|
||
}
|
||
|
||
export async function resolveStandardModelSkuByBarcode(
|
||
barcode: string,
|
||
): Promise<ResolvedStandardModelSkuByBarcode> {
|
||
await ensureStandardModelWhiteBackgroundBarcodeSupportTables()
|
||
|
||
const normalizedBarcode = normalizeExternalBarcode(barcode)
|
||
if (!normalizedBarcode) {
|
||
throw new ExternalWhiteBackgroundBarcodeError(400, 'INVALID_BARCODE', 'barcode 不能为空')
|
||
}
|
||
|
||
const matches = await (prisma as any).materialSkuBarcode.findMany({
|
||
where: {
|
||
barcode: normalizedBarcode,
|
||
deletedAt: null,
|
||
},
|
||
select: {
|
||
id: true,
|
||
barcode: true,
|
||
sku: {
|
||
select: {
|
||
id: true,
|
||
skuName: true,
|
||
deletedAt: true,
|
||
materialId: true,
|
||
material: {
|
||
select: {
|
||
id: true,
|
||
productModel: true,
|
||
materialType: true,
|
||
deletedAt: true,
|
||
},
|
||
},
|
||
},
|
||
},
|
||
},
|
||
take: 3,
|
||
})
|
||
|
||
if (matches.length === 0) {
|
||
throw new ExternalWhiteBackgroundBarcodeError(404, 'BARCODE_NOT_FOUND', '未找到对应的标准模型 SKU 条码绑定')
|
||
}
|
||
|
||
const validTargets = matches.filter((match: any) => match.sku)
|
||
const activeSkuIds = Array.from(new Set(validTargets.map((match: any) => String(match.sku.id))))
|
||
if (activeSkuIds.length > 1 || matches.length > 1) {
|
||
throw new ExternalWhiteBackgroundBarcodeError(409, 'BARCODE_AMBIGUOUS', '该 barcode 绑定了多个 SKU，无法确定唯一标准模型 SKU')
|
||
}
|
||
|
||
const match = validTargets[0]
|
||
const sku = match?.sku
|
||
const material = sku?.material
|
||
|
||
if (!sku) {
|
||
throw new ExternalWhiteBackgroundBarcodeError(404, 'BARCODE_NOT_FOUND', '未找到对应的标准模型 SKU 条码绑定')
|
||
}
|
||
|
||
if (sku.deletedAt) {
|
||
throw new ExternalWhiteBackgroundBarcodeError(410, 'SKU_DELETED', '该 barcode 绑定的 SKU 已被删除')
|
||
}
|
||
|
||
if (!material) {
|
||
throw new ExternalWhiteBackgroundBarcodeError(
|
||
404,
|
||
'BARCODE_NOT_BOUND_TO_STANDARD_MODEL_SKU',
|
||
'该 barcode 未绑定到有效标准模型素材',
|
||
)
|
||
}
|
||
|
||
if (material.deletedAt) {
|
||
throw new ExternalWhiteBackgroundBarcodeError(410, 'MATERIAL_DELETED', '该 barcode 绑定的素材已被删除')
|
||
}
|
||
|
||
if (material.materialType !== '标准模型') {
|
||
throw new ExternalWhiteBackgroundBarcodeError(
|
||
400,
|
||
'BARCODE_NOT_BOUND_TO_STANDARD_MODEL_SKU',
|
||
'该 barcode 绑定的不是标准模型 SKU',
|
||
)
|
||
}
|
||
|
||
return {
|
||
barcode: normalizedBarcode,
|
||
materialId: sku.materialId,
|
||
skuId: sku.id,
|
||
skuName: sku.skuName,
|
||
resolvedSpu: material.productModel,
|
||
resolvedSkuName: sku.skuName,
|
||
}
|
||
}
|