dodil/erp-brick-pos

DODIL ERP brick: point of sale

src/brick_pos/migrations/00002_slice_b/up.sql

-- 00002_slice_b: slice B: closing the day, refunds, vouchers, recipes, the register snapshot. ADD COLUMN is DISRUPTIVE: migrate the test copy first, the live one at a quiet hour.

CREATE TABLE IF NOT EXISTS pos_cash_deposit (
    deposit_ref     VARCHAR NOT NULL,
    shop_id         VARCHAR,
    bag_no          VARCHAR,
    amount          NUMERIC(18,2),
    currency        VARCHAR,
    company_code    VARCHAR,
    bank_account_id VARCHAR,
    expected_on     DATE,
    denominations   VARCHAR,
    sealed_by       VARCHAR,
    sealed_sub      VARCHAR,
    sealed_at       TIMESTAMP,
    status          VARCHAR,
    handed_over_at  TIMESTAMP,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (deposit_ref)
);

CREATE TABLE IF NOT EXISTS pos_refund_claim (
    original_receipt_id VARCHAR NOT NULL,
    line_no             INTEGER NOT NULL,
    claim_no            INTEGER NOT NULL,
    refund_receipt_id   VARCHAR,
    quantity            NUMERIC(18,3),
    gross               NUMERIC(18,2),
    vat                 NUMERIC(18,2),
    refunded_after      NUMERIC(18,3),
    created_at          TIMESTAMP,
    PRIMARY KEY (original_receipt_id, line_no, claim_no)
);

CREATE TABLE IF NOT EXISTS pos_category_rule (
    category_code     VARCHAR NOT NULL,
    restock_on_refund BOOLEAN,
    set_by            VARCHAR,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    PRIMARY KEY (category_code)
);

CREATE TABLE IF NOT EXISTS pos_voucher (
    voucher_code      VARCHAR NOT NULL,
    company_code      VARCHAR,
    currency          VARCHAR,
    face_value        NUMERIC(18,2),
    profile           VARCHAR,
    issued_receipt_id VARCHAR,
    expires_on        DATE,
    status            VARCHAR,
    created_at        TIMESTAMP,
    PRIMARY KEY (voucher_code)
);

CREATE TABLE IF NOT EXISTS pos_voucher_claim (
    voucher_code  VARCHAR NOT NULL,
    claim_no      INTEGER NOT NULL,
    operation     VARCHAR,
    receipt_id    VARCHAR,
    pay_no        INTEGER,
    amount        NUMERIC(18,2),
    balance_after NUMERIC(18,2),
    created_at    TIMESTAMP,
    PRIMARY KEY (voucher_code, claim_no)
);

CREATE TABLE IF NOT EXISTS pos_line_component (
    receipt_id   VARCHAR NOT NULL,
    line_no      INTEGER NOT NULL,
    component_no INTEGER NOT NULL,
    product_id   VARCHAR,
    quantity     NUMERIC(19,6),
    unit_code    VARCHAR,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (receipt_id, line_no, component_no)
);

CREATE TABLE IF NOT EXISTS pos_recipe (
    product_id VARCHAR NOT NULL,
    version    INTEGER NOT NULL,
    valid_from DATE,
    created_by VARCHAR,
    created_at TIMESTAMP,
    PRIMARY KEY (product_id, version)
);

CREATE TABLE IF NOT EXISTS pos_recipe_line (
    product_id   VARCHAR NOT NULL,
    version      INTEGER NOT NULL,
    line_no      INTEGER NOT NULL,
    component_id VARCHAR,
    quantity     NUMERIC(18,3),
    unit_code    VARCHAR,
    modifier_id  VARCHAR,
    replaces     VARCHAR,
    PRIMARY KEY (product_id, version, line_no)
);

CREATE TABLE IF NOT EXISTS pos_register_snapshot (
    shop_id            VARCHAR NOT NULL,
    business_date      DATE NOT NULL,
    snapshot_no        INTEGER NOT NULL,
    price_list_id      VARCHAR,
    country            VARCHAR,
    currency           VARCHAR,
    prices_include_tax BOOLEAN,
    valid_until        DATE,
    digest             VARCHAR,
    products_json      VARCHAR,
    rates_json         VARCHAR,
    built_at           TIMESTAMP,
    PRIMARY KEY (shop_id, business_date, snapshot_no)
);

ALTER TABLE pos_session ADD COLUMN last_receipt_no INTEGER;

ALTER TABLE pos_session ADD COLUMN claimed_gap_from INTEGER;

ALTER TABLE pos_session ADD COLUMN claimed_gap_to INTEGER;

ALTER TABLE pos_session ADD COLUMN posted_at TIMESTAMP;

ALTER TABLE pos_session ADD COLUMN journal_id VARCHAR;

ALTER TABLE pos_session ADD COLUMN snapshot_no INTEGER;

ALTER TABLE pos_session ADD COLUMN snapshot_digest VARCHAR;

ALTER TABLE pos_receipt ADD COLUMN original_receipt_id VARCHAR;

ALTER TABLE pos_receipt ADD COLUMN void_reason VARCHAR;

ALTER TABLE pos_receipt ADD COLUMN voided_by VARCHAR;

ALTER TABLE pos_receipt ADD COLUMN voided_at TIMESTAMP;

ALTER TABLE pos_receipt_line ADD COLUMN original_line_no INTEGER;

ALTER TABLE pos_receipt_line ADD COLUMN restock BOOLEAN;

ALTER TABLE pos_receipt_line ADD COLUMN kind VARCHAR;

ALTER TABLE pos_receipt_line ADD COLUMN voucher_code VARCHAR;

ALTER TABLE pos_receipt_line ADD COLUMN recipe_source VARCHAR;

ALTER TABLE pos_receipt_line ADD COLUMN recipe_ref VARCHAR;

ALTER TABLE pos_receipt_line ADD COLUMN recipe_version INTEGER;

ALTER TABLE pos_receipt_line ADD COLUMN modifiers VARCHAR;

ALTER TABLE pos_payment_line ADD COLUMN voucher_code VARCHAR;