oyaegeli98668
1d5f775d33
- 迁移 0007: 新增 product_barcode 表（一品多码），回填旧 product.gtin 为主码， 全局唯一索引保证「一码一品」，每品至多一个主码 - internal/gtin: GS1 GTIN-8/12/13/14 校验（校验位 + 拒收店内码/变量重量码/优惠券码） - 公开只读 API: 任一条码命中商品、详情返回 barcodes、搜索匹配条码 - adminstore: 商品详情含 barcodes；新增 AddBarcode/DeleteBarcode/SetPrimaryBarcode， 一码命中其他商品返回 ConflictError 供后台去重 待办（按用户要求暂停）: 后台 handler 路由、投稿/审核多条码、前后端 UI Co-Authored-By: Devin AI <158243242+devin-ai-integration[bot]@users.noreply.github.com>
37 lines
1.7 KiB
SQL
37 lines
1.7 KiB
SQL
-- Multi-barcode support: one product can carry many GS1 barcodes
|
|
-- (consumer unit EAN-13/UPC, case ITF-14, regional re-labels, etc.).
|
|
-- product.gtin is kept as the denormalized "primary" barcode for
|
|
-- backward compatibility and is mirrored from the is_primary row here.
|
|
|
|
CREATE TABLE product_barcode (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
product_id UUID NOT NULL REFERENCES product(id) ON DELETE CASCADE,
|
|
gtin VARCHAR(14) NOT NULL,
|
|
gtin_type VARCHAR(8) NOT NULL DEFAULT 'EAN13',
|
|
pack_level VARCHAR(8) NOT NULL DEFAULT 'each',
|
|
region VARCHAR(8),
|
|
is_primary BOOLEAN NOT NULL DEFAULT false,
|
|
source_id UUID REFERENCES source(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT product_barcode_type_chk CHECK (gtin_type IN ('EAN8','UPC','EAN13','ITF14','GTIN14')),
|
|
CONSTRAINT product_barcode_pack_chk CHECK (pack_level IN ('each','case','pallet'))
|
|
);
|
|
|
|
-- A barcode is globally unique: one code maps to exactly one product.
|
|
CREATE UNIQUE INDEX idx_product_barcode_gtin ON product_barcode (gtin);
|
|
CREATE INDEX idx_product_barcode_product ON product_barcode (product_id);
|
|
-- At most one primary barcode per product.
|
|
CREATE UNIQUE INDEX idx_product_barcode_primary ON product_barcode (product_id) WHERE is_primary;
|
|
|
|
-- Backfill: lift each product's existing gtin into the new table as primary.
|
|
INSERT INTO product_barcode (product_id, gtin, gtin_type, pack_level, is_primary)
|
|
SELECT id, gtin,
|
|
CASE WHEN length(gtin) = 8 THEN 'EAN8'
|
|
WHEN length(gtin) = 12 THEN 'UPC'
|
|
WHEN length(gtin) = 14 THEN 'GTIN14'
|
|
ELSE 'EAN13' END,
|
|
'each', true
|
|
FROM product
|
|
WHERE gtin IS NOT NULL AND gtin <> ''
|
|
ON CONFLICT (gtin) DO NOTHING;
|