C03 — Schema SQL

-- Item Master Schema v1
-- 核心流程：invoice_name_cluster -> sellable_item
-- 設計重點：
-- 1. barcode 以證據與屬性的角色建模，不當主鍵。
-- 2. `sellable_item` 是面向發票與審核的主要目標；`base_item` 負責往上彙總。
-- 3. `unit`、`bundle`、`carton` 都是第一級的可售層級。
-- 4. 對外代碼採用 BI-<家族碼> 與 SI-<家族碼>-<U/B/C><序號>。


BEGIN;

CREATE SCHEMA IF NOT EXISTS invoice_item_master_v1;
SET search_path TO invoice_item_master_v1, public;

CREATE TABLE base_items (
    base_item_id          BIGSERIAL PRIMARY KEY,
    base_item_code        TEXT NOT NULL UNIQUE
                           CHECK (base_item_code ~ '^BI-[0-9A-HJKMNPQRSTVWXYZ]{6}$'),
    canonical_name        TEXT NOT NULL,
    brand                 TEXT,
    category              TEXT,
    spec                  TEXT,
    item_tier             TEXT NOT NULL DEFAULT 'bronze'
                           CHECK (item_tier IN ('bronze', 'silver', 'gold')),
    status                TEXT NOT NULL DEFAULT 'draft'
                           CHECK (status IN ('draft', 'active', 'deprecated')),
    created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at            TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE sellable_items (
    sellable_item_id      BIGSERIAL PRIMARY KEY,
    base_item_id          BIGINT NOT NULL REFERENCES base_items(base_item_id),
    sellable_item_code    TEXT NOT NULL UNIQUE
                           CHECK (sellable_item_code ~ '^SI-[0-9A-HJKMNPQRSTVWXYZ]{6}-[UBC][0-9]{3}$'),
    sellable_name         TEXT NOT NULL,
    item_level            TEXT NOT NULL
                           CHECK (item_level IN ('unit', 'bundle', 'carton')),
    package_form          TEXT,
    pack_qty              NUMERIC(12, 3),
    pack_uom              TEXT,
    net_content_value     NUMERIC(12, 3),
    net_content_uom       TEXT,
    effective_from        DATE,
    effective_to          DATE,
    item_tier             TEXT NOT NULL DEFAULT 'bronze'
                           CHECK (item_tier IN ('bronze', 'silver', 'gold')),
    status                TEXT NOT NULL DEFAULT 'draft'
                           CHECK (status IN ('draft', 'active', 'deprecated')),
    created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at            TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE item_components (
    parent_sellable_item_id BIGINT NOT NULL REFERENCES sellable_items(sellable_item_id),
    child_sellable_item_id  BIGINT NOT NULL REFERENCES sellable_items(sellable_item_id),
    component_qty           NUMERIC(12, 3) NOT NULL,
    relation_type           TEXT NOT NULL
                             CHECK (relation_type IN ('contains_unit', 'contains_bundle', 'contains_carton')),
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (parent_sellable_item_id, child_sellable_item_id, relation_type)
);

CREATE TABLE item_aliases (
    alias_id               BIGSERIAL PRIMARY KEY,
    sellable_item_id       BIGINT NOT NULL REFERENCES sellable_items(sellable_item_id),
    alias_type             TEXT NOT NULL
                           CHECK (alias_type IN ('invoice_pn', 'channel_name', 'crowd_name', 'manual_alias')),
    alias_text             TEXT NOT NULL,
    merchant_code          TEXT,
    source_channel         TEXT,
    store_tax_id           TEXT,
    valid_from             DATE,
    valid_to               DATE,
    created_at             TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE item_barcodes (
    sellable_item_id       BIGINT NOT NULL REFERENCES sellable_items(sellable_item_id),
    barcode                TEXT NOT NULL,
    barcode_role           TEXT NOT NULL
                           CHECK (barcode_role IN ('canonical', 'supporting', 'historical', 'suspect')),
    barcode_class          TEXT,
    source_confidence      NUMERIC(6, 4),
    valid_from             DATE,
    valid_to               DATE,
    created_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (sellable_item_id, barcode, barcode_role)
);

CREATE TABLE sellable_item_images (
    sellable_item_id       BIGINT NOT NULL REFERENCES sellable_items(sellable_item_id),
    image_hash             TEXT NOT NULL,
    image_url              TEXT,
    image_role             TEXT NOT NULL
                           CHECK (image_role IN ('primary', 'supporting', 'historical', 'suspect')),
    created_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (sellable_item_id, image_hash)
);

CREATE TABLE invoice_lines (
    invoice_line_id        BIGSERIAL PRIMARY KEY,
    merchant_code          TEXT NOT NULL,
    channel_code           TEXT,
    store_name             TEXT,
    store_tax_id           TEXT,
    invoice_id             TEXT,
    raw_pn                 TEXT NOT NULL,
    invoice_date           DATE,
    quantity               NUMERIC(12, 3),
    amount                 NUMERIC(12, 2),
    created_at             TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE invoice_name_clusters (
    cluster_id             BIGSERIAL PRIMARY KEY,
    merchant_code          TEXT NOT NULL,
    channel_code           TEXT,
    normalized_pn          TEXT NOT NULL,
    representative_pn      TEXT NOT NULL,
    brand_hint             TEXT,
    category_hint          TEXT,
    cluster_status         TEXT NOT NULL DEFAULT 'open'
                           CHECK (cluster_status IN ('open', 'confirmed', 'merged', 'rejected')),
    line_count             BIGINT NOT NULL DEFAULT 0,
    created_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (merchant_code, normalized_pn)
);

CREATE TABLE cluster_members (
    cluster_id             BIGINT NOT NULL REFERENCES invoice_name_clusters(cluster_id),
    invoice_line_id        BIGINT NOT NULL UNIQUE REFERENCES invoice_lines(invoice_line_id),
    created_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (cluster_id, invoice_line_id)
);

CREATE TABLE evidence_observations (
    observation_id         BIGSERIAL PRIMARY KEY,
    source_type            TEXT NOT NULL
                           CHECK (source_type IN ('invoice', 'channel', 'crowd', 'barcode', 'image', 'history', 'manual')),
    source_channel         TEXT,
    source_record_id       TEXT,
    merchant_code          TEXT,
    store_name             TEXT,
    store_tax_id           TEXT,
    raw_name               TEXT,
    normalized_name        TEXT,
    brand_hint             TEXT,
    category_hint          TEXT,
    barcode                TEXT,
    barcode_class          TEXT,
    risk_flags             JSONB NOT NULL DEFAULT '[]'::jsonb,
    image_url              TEXT,
    image_hash             TEXT,
    package_form           TEXT,
    net_content_value      NUMERIC(12, 3),
    net_content_uom        TEXT,
    source_url             TEXT,
    payload                JSONB NOT NULL DEFAULT '{}'::jsonb,
    valid_from             DATE,
    valid_to               DATE,
    observed_at            TIMESTAMPTZ,
    created_at             TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE mapping_candidates (
    candidate_id           BIGSERIAL PRIMARY KEY,
    cluster_id             BIGINT NOT NULL REFERENCES invoice_name_clusters(cluster_id),
    sellable_item_id       BIGINT NOT NULL REFERENCES sellable_items(sellable_item_id),
    generated_by           TEXT NOT NULL
                           CHECK (generated_by IN ('rule', 'ml', 'history', 'crowd', 'manual')),
    score                  NUMERIC(6, 4) NOT NULL,
    proposed_mapping_tier  TEXT NOT NULL DEFAULT 'bronze'
                           CHECK (proposed_mapping_tier IN ('bronze', 'silver', 'gold')),
    candidate_status       TEXT NOT NULL DEFAULT 'proposed'
                           CHECK (candidate_status IN ('proposed', 'shortlisted', 'rejected', 'confirmed')),
    created_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (cluster_id, sellable_item_id)
);

CREATE TABLE mapping_candidate_evidence (
    candidate_id           BIGINT NOT NULL REFERENCES mapping_candidates(candidate_id),
    observation_id         BIGINT NOT NULL REFERENCES evidence_observations(observation_id),
    evidence_type          TEXT NOT NULL
                           CHECK (evidence_type IN ('name', 'barcode', 'image', 'channel', 'history', 'merchant', 'brand', 'category', 'store', 'time', 'manual')),
    evidence_score         NUMERIC(6, 4) NOT NULL,
    is_supporting          BOOLEAN NOT NULL DEFAULT TRUE,
    note                   TEXT,
    created_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (candidate_id, observation_id, evidence_type)
);

CREATE TABLE mapping_decisions (
    decision_id            BIGSERIAL PRIMARY KEY,
    cluster_id             BIGINT NOT NULL UNIQUE REFERENCES invoice_name_clusters(cluster_id),
    sellable_item_id       BIGINT REFERENCES sellable_items(sellable_item_id),
    decision_type          TEXT NOT NULL
                           CHECK (decision_type IN ('auto_confirmed', 'review_confirmed', 'rejected', 'needs_new_item', 'needs_more_evidence')),
    mapping_tier           TEXT
                           CHECK (mapping_tier IN ('bronze', 'silver', 'gold')),
    reviewer               TEXT,
    decision_note          TEXT,
    decided_at             TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE review_tasks (
    review_task_id         BIGSERIAL PRIMARY KEY,
    cluster_id             BIGINT REFERENCES invoice_name_clusters(cluster_id),
    candidate_id           BIGINT REFERENCES mapping_candidates(candidate_id),
    task_type              TEXT NOT NULL
                           CHECK (task_type IN ('candidate_confirm', 'new_item_triage', 'barcode_confirm', 'image_confirm')),
    task_status            TEXT NOT NULL DEFAULT 'open'
                           CHECK (task_status IN ('open', 'in_progress', 'completed', 'cancelled')),
    assigned_to            TEXT,
    created_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at           TIMESTAMPTZ
);

CREATE TABLE review_answers (
    review_answer_id       BIGSERIAL PRIMARY KEY,
    review_task_id         BIGINT NOT NULL REFERENCES review_tasks(review_task_id),
    answered_by            TEXT NOT NULL,
    answer_code            TEXT NOT NULL,
    answer_payload         JSONB NOT NULL DEFAULT '{}'::jsonb,
    answered_at            TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_sellable_items_base_item_id
    ON sellable_items (base_item_id);

CREATE INDEX idx_item_aliases_sellable_item_id
    ON item_aliases (sellable_item_id);

CREATE INDEX idx_item_aliases_invoice_lookup
    ON item_aliases (alias_type, merchant_code, alias_text);

CREATE INDEX idx_item_barcodes_barcode
    ON item_barcodes (barcode);

CREATE INDEX idx_sellable_item_images_hash
    ON sellable_item_images (image_hash);

CREATE INDEX idx_invoice_lines_merchant_raw_pn
    ON invoice_lines (merchant_code, raw_pn);

CREATE INDEX idx_invoice_lines_store_tax_id
    ON invoice_lines (store_tax_id, invoice_date);

CREATE INDEX idx_invoice_name_clusters_merchant_normalized_pn
    ON invoice_name_clusters (merchant_code, normalized_pn);

CREATE INDEX idx_evidence_observations_barcode
    ON evidence_observations (barcode);

CREATE INDEX idx_evidence_observations_normalized_name
    ON evidence_observations (normalized_name);

CREATE INDEX idx_evidence_observations_store_tax_id
    ON evidence_observations (store_tax_id, observed_at);

CREATE INDEX idx_mapping_candidates_cluster_score
    ON mapping_candidates (cluster_id, score DESC);

CREATE INDEX idx_review_tasks_status
    ON review_tasks (task_status, task_type);

COMMIT;