dodil/erp-brick-product

src/brick_product/migrations/00003_v4_slice_a/up.sql

-- 00003_v4_slice_a: additive only. Product spec v4, slice A (PRD-27..33): GTINs per unit, dated measures, dated unit roles
-- with rounding rules, a pricing quantity on prices, a unit's kind.

ALTER TABLE prod_unit ADD COLUMN IF NOT EXISTS kind VARCHAR;
UPDATE prod_unit SET kind = 'count'  WHERE kind IS NULL AND unit_code IN ('C62', 'XBX', 'SET');
UPDATE prod_unit SET kind = 'time'   WHERE kind IS NULL AND unit_code IN ('HUR', 'DAY', 'WEE', 'MON', 'ANN', 'MIN');
UPDATE prod_unit SET kind = 'mass'   WHERE kind IS NULL AND unit_code IN ('KGM', 'GRM', 'LBR');
UPDATE prod_unit SET kind = 'volume' WHERE kind IS NULL AND unit_code IN ('LTR', 'MLT', 'MTQ', 'FTQ');
UPDATE prod_unit SET kind = 'length' WHERE kind IS NULL AND unit_code IN ('MTR', 'CMT', 'MMT', 'INH');
UPDATE prod_unit SET kind = 'area'   WHERE kind IS NULL AND unit_code = 'MTK';
INSERT INTO prod_unit (unit_code, name, decimals, kind)
    SELECT v.unit_code, v.name, v.decimals, v.kind
    FROM (VALUES ('MTQ', 'cubic metre', 3, 'volume'), ('CMT', 'centimetre', 1, 'length'), ('MMT', 'millimetre', 0, 'length'),
                 ('LBR', 'pound', 3, 'mass'), ('FTQ', 'cubic foot', 3, 'volume'), ('INH', 'inch', 1, 'length'))
         AS v(unit_code, name, decimals, kind)
    WHERE v.unit_code NOT IN (SELECT unit_code FROM prod_unit);

ALTER TABLE prod_gtin ADD COLUMN IF NOT EXISTS kind VARCHAR;
ALTER TABLE prod_gtin ADD COLUMN IF NOT EXISTS status VARCHAR;
ALTER TABLE prod_gtin ADD COLUMN IF NOT EXISTS retired_on DATE;
UPDATE prod_gtin SET kind = 'main' WHERE kind IS NULL;
UPDATE prod_gtin SET status = 'active' WHERE status IS NULL;

CREATE TABLE IF NOT EXISTS prod_unit_measure (
    owner               VARCHAR NOT NULL,
    unit_code           VARCHAR NOT NULL,
    valid_from          DATE NOT NULL,
    gross_weight        NUMERIC(19,6),
    net_weight          NUMERIC(19,6),
    weight_unit_code    VARCHAR,
    volume              NUMERIC(19,6),
    volume_unit_code    VARCHAR,
    length              NUMERIC(19,6),
    width               NUMERIC(19,6),
    height              NUMERIC(19,6),
    dimension_unit_code VARCHAR,
    set_by              VARCHAR,
    set_at              TIMESTAMP,
    copied              BOOLEAN,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    PRIMARY KEY (owner, unit_code, valid_from)
);

CREATE TABLE IF NOT EXISTS prod_unit_role (
    owner             VARCHAR NOT NULL,
    role              VARCHAR NOT NULL,
    valid_from        DATE NOT NULL,
    unit_code         VARCHAR,
    min_quantity      NUMERIC(19,6),
    rounding_multiple NUMERIC(19,6),
    rule_unit_code    VARCHAR,
    set_by            VARCHAR,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    PRIMARY KEY (owner, role, valid_from)
);

ALTER TABLE prod_price ADD COLUMN IF NOT EXISTS pricing_quantity INTEGER;
ALTER TABLE prod_price_rule ADD COLUMN IF NOT EXISTS pricing_quantity INTEGER;