dodil/erp-brick-expenses

DODIL ERP brick: expenses

src/brick_expenses/migrations/00003_gates/up.sql

-- 00003_gates - slice B item 1: the remaining submit gates (EXP-08: cards, policy, stale, unconfirmed fields) and the manager's delegate (EXP-09). Additive only. ADD COLUMN is DISRUPTIVE on
-- DataK3: migrate the test copy first, the live one at a quiet hour.
-- exp_line: what a policy rule reads (city, nights, attendees), the employee's reason for a warned line, the rules' last answer, the fields a
-- reader suggested and the employee has not confirmed, and the stale mark (EXP-20).

ALTER TABLE exp_line ADD COLUMN city VARCHAR;

ALTER TABLE exp_line ADD COLUMN nights INTEGER;

ALTER TABLE exp_line ADD COLUMN attendees_json VARCHAR;

ALTER TABLE exp_line ADD COLUMN justification VARCHAR;

ALTER TABLE exp_line ADD COLUMN policy_result_json VARCHAR;

ALTER TABLE exp_line ADD COLUMN ai_fields_json VARCHAR;

ALTER TABLE exp_line ADD COLUMN stale BOOLEAN;

ALTER TABLE exp_line ADD COLUMN stale_reason VARCHAR;

ALTER TABLE exp_line ADD COLUMN card_txn_id VARCHAR;

ALTER TABLE exp_line ADD COLUMN recomputed_json VARCHAR;

ALTER TABLE exp_approval_step ADD COLUMN delegate_for VARCHAR;

CREATE TABLE IF NOT EXISTS exp_policy_rule (
    company_code     VARCHAR NOT NULL,
    rule_code        VARCHAR NOT NULL,
    valid_from       DATE NOT NULL,
    category_code    VARCHAR,
    scope            VARCHAR,
    city_or_country  VARCHAR,
    limit_amount     NUMERIC(18,2),
    currency         VARCHAR,
    mode             VARCHAR,
    requires         VARCHAR,
    message          VARCHAR,
    set_by           VARCHAR,
    created_at       TIMESTAMP,
    updated_at       TIMESTAMP,
    PRIMARY KEY (company_code, rule_code, valid_from)
);

CREATE TABLE IF NOT EXISTS exp_delegation (
    delegator        VARCHAR NOT NULL,
    delegate         VARCHAR NOT NULL,
    valid_from       DATE NOT NULL,
    delegator_email  VARCHAR,
    delegator_sub    VARCHAR,
    delegate_email   VARCHAR,
    delegate_sub     VARCHAR,
    valid_to         DATE,
    set_by           VARCHAR,
    created_at       TIMESTAMP,
    updated_at       TIMESTAMP,
    PRIMARY KEY (delegator, delegate, valid_from)
);

CREATE TABLE IF NOT EXISTS exp_card (
    card_id           VARCHAR NOT NULL,
    company_code      VARCHAR,
    holder_person_id  BIGINT,
    provider          VARCHAR,
    currency          VARCHAR,
    account_ref       VARCHAR,
    status            VARCHAR,
    stopped_from      DATE,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    PRIMARY KEY (card_id)
);

CREATE TABLE IF NOT EXISTS exp_card_txn (
    card_txn_id          VARCHAR NOT NULL,
    card_id              VARCHAR,
    company_code         VARCHAR,
    holder_person_id     BIGINT,
    posted_on            DATE,
    merchant             VARCHAR,
    billed_amount        NUMERIC(18,2),
    billed_currency      VARCHAR,
    original_amount      NUMERIC(18,2),
    original_currency    VARCHAR,
    status               VARCHAR,
    statement_ref        VARCHAR,
    matched_report_id    VARCHAR,
    matched_line_no      INTEGER,
    recovery_journal_id  BIGINT,
    created_at           TIMESTAMP,
    updated_at           TIMESTAMP,
    PRIMARY KEY (card_txn_id)
);

CREATE TABLE IF NOT EXISTS exp_card_claim (
    card_txn_id  VARCHAR NOT NULL,
    operation    VARCHAR NOT NULL,
    report_id    VARCHAR,
    payout_ref   VARCHAR,
    payment_id   VARCHAR,
    claimed_by   VARCHAR,
    claimed_at   TIMESTAMP,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (card_txn_id, operation)
);


INSERT INTO exp_category (company_code, category_code, valid_from, name, gl_purpose, tax_category, receipt_required, kind, created_at, updated_at) VALUES
    ('*', 'ALCOHOL', DATE '2000-01-01', 'Alcohol', 'entertainment', 'standard', TRUE, 'receipt', NOW(), NOW());