goods/migrations/0001_init.up.sql

-- OpenGoods (天工·商品标签) initial schema.
-- Public-good product information store: facts only, no commerce.

CREATE EXTENSION IF NOT EXISTS pgcrypto;   -- gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS pg_trgm;     -- fuzzy name search
CREATE EXTENSION IF NOT EXISTS ltree;       -- category subtree queries

-- Data sources (Open Food Facts / USDA / GS1 ...) with trust + license.
CREATE TABLE source (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name         TEXT NOT NULL,
    homepage     TEXT,
    license      TEXT,
    trust_weight NUMERIC(3,2) NOT NULL DEFAULT 0.5,
    notes        TEXT,
    UNIQUE (name)
);

CREATE TABLE brand (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            TEXT NOT NULL,
    normalized_name TEXT NOT NULL,
    aliases         TEXT[] NOT NULL DEFAULT '{}',
    UNIQUE (normalized_name)
);

CREATE TABLE manufacturer (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            TEXT NOT NULL,
    normalized_name TEXT NOT NULL,
    country         VARCHAR(64),
    UNIQUE (normalized_name)
);

-- Self-built category tree, each node optionally mapped to a GS1 GPC brick.
CREATE TABLE category (
    id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name_zh        TEXT NOT NULL,
    name_en        TEXT,
    parent_id      UUID REFERENCES category(id),
    path           LTREE NOT NULL,
    gpc_brick_code VARCHAR(10),
    level          INT NOT NULL DEFAULT 0,
    UNIQUE (path)
);

-- Parameter template / constraints per category.
CREATE TABLE category_schema (
    category_id           UUID PRIMARY KEY REFERENCES category(id) ON DELETE CASCADE,
    required_attributes   TEXT[] NOT NULL DEFAULT '{}',
    recommended_attributes TEXT[] NOT NULL DEFAULT '{}',
    nutriment_basis       VARCHAR(16)
);

-- Unit dictionary: each unit maps to a canonical unit within its dimension.
CREATE TABLE unit (
    code                VARCHAR(16) PRIMARY KEY,
    dimension           VARCHAR(16) NOT NULL,
    canonical           VARCHAR(16) NOT NULL,
    to_canonical_factor NUMERIC,
    aliases             TEXT[] NOT NULL DEFAULT '{}',
    display             TEXT
);

-- Parameter dictionary: standard attribute keys with default unit.
CREATE TABLE attribute_definition (
    key          VARCHAR(64) PRIMARY KEY,
    label_zh     TEXT,
    label_en     TEXT,
    dimension    VARCHAR(16),
    default_unit VARCHAR(16) REFERENCES unit(code),
    aliases      TEXT[] NOT NULL DEFAULT '{}'
);

CREATE TABLE product (
    id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    gtin                  VARCHAR(14),
    name                  TEXT NOT NULL,
    brand_id              UUID REFERENCES brand(id),
    manufacturer_id       UUID REFERENCES manufacturer(id),
    category_id           UUID REFERENCES category(id),
    gpc_brick_code        VARCHAR(10),
    net_content_value     NUMERIC,
    net_content_unit      VARCHAR(16),
    net_content_canonical NUMERIC,
    country_of_origin     VARCHAR(64),
    shelf_life_days       INT,
    storage               TEXT,
    attributes            JSONB NOT NULL DEFAULT '{}',
    quality_score         NUMERIC(4,3) NOT NULL DEFAULT 0,
    status                VARCHAR(16) NOT NULL DEFAULT 'active',
    canonical_id          UUID REFERENCES product(id),
    search_tsv            TSVECTOR,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT product_status_chk CHECK (status IN ('active','merged','deprecated')),
    CONSTRAINT product_quality_chk CHECK (quality_score >= 0 AND quality_score <= 1)
);

CREATE TABLE food_detail (
    product_id       UUID PRIMARY KEY REFERENCES product(id) ON DELETE CASCADE,
    ingredients_text TEXT,
    ingredients      JSONB,
    allergens        TEXT[] NOT NULL DEFAULT '{}',
    additives        TEXT[] NOT NULL DEFAULT '{}',
    nutriments       JSONB,
    nutrition_basis  VARCHAR(16),
    serving_size     VARCHAR(32),
    nutri_score      CHAR(1),
    labels           TEXT[] NOT NULL DEFAULT '{}',
    CONSTRAINT food_basis_chk CHECK (nutrition_basis IS NULL OR nutrition_basis IN ('per_100g','per_100ml','per_serving'))
);

-- Official manufacturer-suggested retail price snapshot (no purchase link).
CREATE TABLE product_msrp (
    id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id     UUID NOT NULL REFERENCES product(id) ON DELETE CASCADE,
    amount         NUMERIC(12,2) NOT NULL,
    currency       CHAR(3) NOT NULL,
    region         VARCHAR(8) NOT NULL DEFAULT 'CN',
    source_id      UUID REFERENCES source(id),
    source_url     TEXT,
    effective_date DATE,
    note           TEXT,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE product_image (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID NOT NULL REFERENCES product(id) ON DELETE CASCADE,
    url        TEXT NOT NULL,
    kind       VARCHAR(16) NOT NULL DEFAULT 'other',
    license    TEXT,
    source_id  UUID REFERENCES source(id),
    CONSTRAINT image_kind_chk CHECK (kind IN ('front','ingredients','nutrition','other'))
);

-- Field-level provenance: which source provided which fields.
CREATE TABLE product_source (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID NOT NULL REFERENCES product(id) ON DELETE CASCADE,
    source_id  UUID REFERENCES source(id),
    url        TEXT,
    fields     TEXT[] NOT NULL DEFAULT '{}',
    fetched_at TIMESTAMPTZ,
    raw        JSONB
);

CREATE TABLE merge_log (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    kept_id    UUID,
    merged_id  UUID,
    reason     TEXT,
    actor      TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Indexes
CREATE UNIQUE INDEX idx_product_gtin ON product (gtin) WHERE gtin IS NOT NULL;
CREATE INDEX idx_product_name_trgm ON product USING gin (name gin_trgm_ops);
CREATE INDEX idx_product_attrs ON product USING gin (attributes);
CREATE INDEX idx_product_tsv ON product USING gin (search_tsv);
CREATE INDEX idx_product_category ON product (category_id);
CREATE INDEX idx_product_brand ON product (brand_id);
CREATE INDEX idx_product_updated ON product (updated_at);
CREATE INDEX idx_food_nutriments ON food_detail USING gin (nutriments);
CREATE INDEX idx_category_path ON category USING gist (path);
CREATE INDEX idx_msrp_product ON product_msrp (product_id);
CREATE INDEX idx_psource_product ON product_source (product_id);

-- Keep search_tsv and updated_at in sync.
CREATE OR REPLACE FUNCTION product_sync_tsv() RETURNS trigger AS $$
BEGIN
    NEW.search_tsv := to_tsvector('simple', coalesce(NEW.name, ''));
    NEW.updated_at := now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_product_sync
    BEFORE INSERT OR UPDATE ON product
    FOR EACH ROW EXECUTE FUNCTION product_sync_tsv();