dodil/erp-brick-purchasing

DODIL ERP brick: purchasing

src/brick_purchasing/schema.sql

-- Tables of the purchasing brick (module `purchasing`). Every table prefixed `pur_`, a natural primary key each (never SERIAL), money NUMERIC(18,2),
-- a price or rate NUMERIC(19,4), a quantity NUMERIC(19,6), the stamps last so a later column is an append. Additive only.
-- Generated by scripts/gen_schema.py from infrastructure/tables.py.

CREATE TABLE IF NOT EXISTS pur_account_map (
    purpose VARCHAR NOT NULL,
    category VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    account_id VARCHAR,
    set_by_email VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (purpose, category, valid_from)
);

CREATE TABLE IF NOT EXISTS pur_agreement (
    agreement_id VARCHAR NOT NULL,
    bp_id BIGINT,
    kind VARCHAR,
    supplier_ref VARCHAR,
    agreement_no VARCHAR,
    revision INTEGER,
    valid_from DATE,
    valid_to DATE,
    target_value NUMERIC(18, 2),
    currency VARCHAR,
    status VARCHAR,
    firm_zone_days INTEGER,
    buyer_email VARCHAR,
    buyer_sub VARCHAR,
    approved_by_email VARCHAR,
    approved_by_sub VARCHAR,
    approved_on DATE,
    submitted_on DATE,
    release_levels INTEGER,
    release_thresholds VARCHAR,
    strategy_valid_from DATE,
    pending_json VARCHAR,
    close_reason VARCHAR,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (agreement_id)
);

CREATE TABLE IF NOT EXISTS pur_agreement_claim (
    agreement_id VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    purchase_order_id VARCHAR NOT NULL,
    order_line_no INTEGER NOT NULL,
    quantity_base NUMERIC(19, 6),
    value NUMERIC(18, 2),
    claimed_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (agreement_id, line_no, purchase_order_id, order_line_no)
);

CREATE TABLE IF NOT EXISTS pur_agreement_line (
    agreement_id VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    product_id VARCHAR,
    description VARCHAR,
    unit_code VARCHAR,
    unit_numerator INTEGER,
    unit_denominator INTEGER,
    target_quantity NUMERIC(19, 6),
    unit_price NUMERIC(19, 4),
    pricing_unit_code VARCHAR,
    pricing_numerator INTEGER,
    pricing_denominator INTEGER,
    pricing_quantity INTEGER,
    base_unit_code VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (agreement_id, line_no)
);

CREATE TABLE IF NOT EXISTS pur_agreement_versions (
    agreement_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP,
    PRIMARY KEY (agreement_id, version)
);

CREATE TABLE IF NOT EXISTS pur_ai_finding (
    subject_ref VARCHAR NOT NULL,
    step VARCHAR NOT NULL,
    run_no INTEGER NOT NULL,
    finding_no INTEGER NOT NULL,
    model VARCHAR,
    inputs_digest VARCHAR,
    finding VARCHAR,
    evidence VARCHAR,
    decision VARCHAR,
    decided_by_email VARCHAR,
    decided_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (subject_ref, step, run_no, finding_no)
);

CREATE TABLE IF NOT EXISTS pur_allocation (
    source_kind VARCHAR NOT NULL,
    source_id VARCHAR NOT NULL,
    bill_id VARCHAR NOT NULL,
    seq INTEGER NOT NULL,
    amount NUMERIC(18, 2),
    allocated_on DATE,
    allocated_by_email VARCHAR,
    reverses_seq INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (source_kind, source_id, bill_id, seq)
);

CREATE TABLE IF NOT EXISTS pur_approval_limit (
    approver_email VARCHAR NOT NULL,
    category VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    approver_sub VARCHAR,
    limit_amount NUMERIC(18, 2),
    currency VARCHAR,
    set_by_email VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (approver_email, category, valid_from)
);

CREATE TABLE IF NOT EXISTS pur_bill (
    bill_id VARCHAR NOT NULL,
    bp_id BIGINT,
    supplier_invoice_no VARCHAR,
    invoice_date DATE,
    kind VARCHAR,
    self_billed BOOLEAN,
    corrects_bill_id VARCHAR,
    purchase_order_id VARCHAR,
    company_code VARCHAR,
    currency VARCHAR,
    subtotal NUMERIC(18, 2),
    tax_total NUMERIC(18, 2),
    total NUMERIC(18, 2),
    supplier_tax_total NUMERIC(18, 2),
    due_date DATE,
    terms_id VARCHAR,
    status VARCHAR,
    bill_no VARCHAR,
    recorded_by_email VARCHAR,
    recorded_by_sub VARCHAR,
    recorded_on DATE,
    approved_by_email VARCHAR,
    approved_by_sub VARCHAR,
    approved_on DATE,
    source_kind VARCHAR,
    source_id VARCHAR,
    dispute_reason VARCHAR,
    dispute_by VARCHAR,
    resolve_outcome VARCHAR,
    cancel_reason VARCHAR,
    tax_ref VARCHAR,
    tax_frozen BOOLEAN,
    posted_journal_id VARCHAR,
    post_note VARCHAR,
    partner_company VARCHAR,
    ic_reference VARCHAR,
    down_payment_id VARCHAR,
    pre_dispute VARCHAR,
    supplier_vat_number VARCHAR,
    supplier_address VARCHAR,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (bill_id)
);

CREATE TABLE IF NOT EXISTS pur_bill_line (
    bill_id VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    order_line_no INTEGER,
    product_id VARCHAR,
    description VARCHAR,
    quantity NUMERIC(19, 6),
    unit_code VARCHAR,
    unit_numerator INTEGER,
    unit_denominator INTEGER,
    quantity_base NUMERIC(19, 6),
    pricing_unit_code VARCHAR,
    pricing_numerator INTEGER,
    pricing_denominator INTEGER,
    pricing_quantity INTEGER,
    unit_price NUMERIC(19, 4),
    net_amount NUMERIC(18, 2),
    tax_category VARCHAR,
    tax_code VARCHAR,
    tax_rate NUMERIC(19, 4),
    tax_amount NUMERIC(18, 2),
    vat_reclaimable NUMERIC(18, 2),
    expense_category VARCHAR,
    cis_part VARCHAR,
    withdrawal_id VARCHAR,
    delivery_cost_ref VARCHAR,
    fa_transaction_id VARCHAR,
    asset_id VARCHAR,
    cost_centre VARCHAR,
    project_id VARCHAR,
    account_assignment VARCHAR,
    item_category VARCHAR,
    ic_reference VARCHAR,
    posting_kind VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (bill_id, line_no)
);

CREATE TABLE IF NOT EXISTS pur_bill_versions (
    bill_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP,
    PRIMARY KEY (bill_id, version)
);

CREATE TABLE IF NOT EXISTS pur_billed_claim (
    purchase_order_id VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    bill_id VARCHAR NOT NULL,
    quantity_base NUMERIC(19, 6),
    claimed_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (purchase_order_id, line_no, bill_id)
);

CREATE TABLE IF NOT EXISTS pur_cis_deduction (
    payment_id VARCHAR NOT NULL,
    bill_id VARCHAR NOT NULL,
    labour_paid NUMERIC(18, 2),
    materials_paid NUMERIC(18, 2),
    rate_pct NUMERIC(19, 4),
    deduction NUMERIC(18, 2),
    tax_month VARCHAR,
    verification_number VARCHAR,
    status VARCHAR,
    deduction_ref VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (payment_id, bill_id)
);

CREATE TABLE IF NOT EXISTS pur_claim_versions (
    scope VARCHAR NOT NULL,
    seq INTEGER NOT NULL,
    claimed_by VARCHAR,
    claimed_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (scope, seq)
);

CREATE TABLE IF NOT EXISTS pur_consignment_withdrawal (
    withdrawal_id VARCHAR NOT NULL,
    bp_id BIGINT,
    product_id VARCHAR,
    quantity NUMERIC(19, 6),
    unit_code VARCHAR,
    withdrawn_on DATE,
    unit_price NUMERIC(19, 4),
    pricing_unit_code VARCHAR,
    pricing_quantity INTEGER,
    amount NUMERIC(18, 2),
    currency VARCHAR,
    status VARCHAR,
    settled_bill_id VARCHAR,
    reverses_withdrawal_id VARCHAR,
    move_ref VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (withdrawal_id)
);

CREATE TABLE IF NOT EXISTS pur_delivery_cost (
    purchase_order_id VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    kind VARCHAR NOT NULL,
    seq INTEGER NOT NULL,
    amount NUMERIC(18, 2),
    percent NUMERIC(19, 4),
    payee_bp_id BIGINT,
    currency VARCHAR,
    tax_code VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (purchase_order_id, line_no, kind, seq)
);

CREATE TABLE IF NOT EXISTS pur_down_payment (
    down_payment_id VARCHAR NOT NULL,
    dp_no VARCHAR,
    purchase_order_id VARCHAR,
    bp_id BIGINT,
    net_amount NUMERIC(18, 2),
    tax_code VARCHAR,
    tax_amount NUMERIC(18, 2),
    gross_amount NUMERIC(18, 2),
    currency VARCHAR,
    due_date DATE,
    status VARCHAR,
    requested_by_email VARCHAR,
    requested_by_sub VARCHAR,
    approved_by_email VARCHAR,
    approved_by_sub VARCHAR,
    approved_on DATE,
    payment_id VARCHAR,
    invoice_bill_id VARCHAR,
    paid_on DATE,
    refund_reference VARCHAR,
    cancel_reason VARCHAR,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (down_payment_id)
);

CREATE TABLE IF NOT EXISTS pur_down_payment_versions (
    down_payment_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP,
    PRIMARY KEY (down_payment_id, version)
);

CREATE TABLE IF NOT EXISTS pur_dp_clearing (
    down_payment_id VARCHAR NOT NULL,
    bill_id VARCHAR NOT NULL,
    seq INTEGER NOT NULL,
    amount NUMERIC(18, 2),
    tax_code VARCHAR,
    cleared_by_email VARCHAR,
    cleared_on DATE,
    reason VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (down_payment_id, bill_id, seq)
);

CREATE TABLE IF NOT EXISTS pur_evaluation (
    bp_id BIGINT NOT NULL,
    period_id VARCHAR NOT NULL,
    seq INTEGER NOT NULL,
    price_score INTEGER,
    delivery_score INTEGER,
    quantity_score INTEGER,
    quality_score INTEGER,
    service_score INTEGER,
    overall NUMERIC(19, 4),
    weights VARCHAR,
    frozen_by_email VARCHAR,
    frozen_on DATE,
    reason VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (bp_id, period_id, seq)
);

CREATE TABLE IF NOT EXISTS pur_evaluation_weight (
    criterion VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    weight INTEGER,
    set_by_email VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (criterion, valid_from)
);

CREATE TABLE IF NOT EXISTS pur_gate_override (
    purchase_order_id VARCHAR NOT NULL,
    revision INTEGER NOT NULL,
    line_no INTEGER NOT NULL,
    gate VARCHAR NOT NULL,
    failed_reason VARCHAR,
    accepted_by_email VARCHAR,
    accepted_by_sub VARCHAR,
    accepted_on DATE,
    reason VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (purchase_order_id, revision, line_no, gate)
);

CREATE TABLE IF NOT EXISTS pur_handover (
    handover_id VARCHAR NOT NULL,
    kind VARCHAR,
    subject_ref VARCHAR,
    status VARCHAR,
    reason VARCHAR,
    attempts INTEGER,
    payload VARCHAR,
    last_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (handover_id)
);

CREATE TABLE IF NOT EXISTS pur_migration_check (
    run_id VARCHAR NOT NULL,
    check_no INTEGER NOT NULL,
    name VARCHAR,
    odoo VARCHAR,
    ours VARCHAR,
    equal BOOLEAN,
    must VARCHAR,
    explanation VARCHAR,
    explained_by_email VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (run_id, check_no)
);

CREATE TABLE IF NOT EXISTS pur_migration_map (
    odoo_db VARCHAR NOT NULL,
    odoo_model VARCHAR NOT NULL,
    odoo_id BIGINT NOT NULL,
    natural_key VARCHAR,
    run_id VARCHAR,
    migrated_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (odoo_db, odoo_model, odoo_id)
);

CREATE TABLE IF NOT EXISTS pur_migration_run (
    run_id VARCHAR NOT NULL,
    odoo_db VARCHAR,
    cutover_date DATE,
    status VARCHAR,
    doors VARCHAR,
    checksums VARCHAR,
    loaded_at TIMESTAMP,
    reconciled_at TIMESTAMP,
    signed_by_email VARCHAR,
    signed_by_sub VARCHAR,
    signed_at TIMESTAMP,
    discard_reason VARCHAR,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (run_id)
);

CREATE TABLE IF NOT EXISTS pur_number_claim (
    number VARCHAR NOT NULL,
    series_id VARCHAR,
    document_id VARCHAR,
    claimed_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (number)
);

CREATE TABLE IF NOT EXISTS pur_number_series (
    series_id VARCHAR NOT NULL,
    year INTEGER NOT NULL,
    format VARCHAR,
    high_water_mark INTEGER,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (series_id, year)
);

CREATE TABLE IF NOT EXISTS pur_number_series_versions (
    series_id VARCHAR NOT NULL,
    year INTEGER NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP,
    PRIMARY KEY (series_id, year, version)
);

CREATE TABLE IF NOT EXISTS pur_object (
    object_key VARCHAR NOT NULL,
    content_type VARCHAR,
    body VARCHAR,
    sha256 VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (object_key)
);

CREATE TABLE IF NOT EXISTS pur_order (
    purchase_order_id VARCHAR NOT NULL,
    order_no VARCHAR,
    revision INTEGER,
    bp_id BIGINT,
    currency VARCHAR,
    status VARCHAR,
    buyer_email VARCHAR,
    buyer_sub VARCHAR,
    source_kind VARCHAR,
    source_id VARCHAR,
    request_id VARCHAR,
    rfq_id VARCHAR,
    agreement_id VARCHAR,
    release_no INTEGER,
    supplier_ref VARCHAR,
    terms_id VARCHAR,
    expected_on DATE,
    company_code VARCHAR,
    confirmed_on DATE,
    approved_total NUMERIC(18, 2),
    approved_by_email VARCHAR,
    approved_by_sub VARCHAR,
    approved_on DATE,
    submitted_on DATE,
    release_levels INTEGER,
    release_thresholds VARCHAR,
    strategy_valid_from DATE,
    net_total NUMERIC(18, 2),
    ship_to_bp_id BIGINT,
    ship_to_address VARCHAR,
    sales_order_id VARCHAR,
    so_line_no INTEGER,
    warehouse_id VARCHAR,
    location_id VARCHAR,
    cancel_reason VARCHAR,
    close_reason VARCHAR,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (purchase_order_id)
);

CREATE TABLE IF NOT EXISTS pur_order_component (
    purchase_order_id VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    component_no INTEGER NOT NULL,
    product_id VARCHAR,
    quantity_per NUMERIC(19, 6),
    unit_code VARCHAR,
    provided NUMERIC(19, 6),
    provision_move_refs VARCHAR,
    allowance_reason VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (purchase_order_id, line_no, component_no)
);

CREATE TABLE IF NOT EXISTS pur_order_line (
    purchase_order_id VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    product_id VARCHAR,
    description VARCHAR,
    quantity NUMERIC(19, 6),
    unit_code VARCHAR,
    unit_numerator INTEGER,
    unit_denominator INTEGER,
    quantity_base NUMERIC(19, 6),
    base_unit_code VARCHAR,
    pricing_unit_code VARCHAR,
    pricing_numerator INTEGER,
    pricing_denominator INTEGER,
    pricing_conversion_source VARCHAR,
    pricing_quantity INTEGER,
    unit_price NUMERIC(19, 4),
    quantity_priced NUMERIC(19, 6),
    net_amount NUMERIC(18, 2),
    price_source VARCHAR,
    tax_category VARCHAR,
    expense_category VARCHAR,
    kind VARCHAR,
    expected_on DATE,
    account_assignment VARCHAR,
    cost_centre VARCHAR,
    asset_id VARCHAR,
    asset_class_code VARCHAR,
    project_id VARCHAR,
    company_code VARCHAR,
    item_category VARCHAR,
    agreement_id VARCHAR,
    agreement_line_no INTEGER,
    ers BOOLEAN,
    order_multiple NUMERIC(19, 6),
    rounding_met BOOLEAN,
    rounding_reason VARCHAR,
    rounding_fix VARCHAR,
    quota_reason VARCHAR,
    supplier_product_code VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (purchase_order_id, line_no)
);

CREATE TABLE IF NOT EXISTS pur_order_revision (
    purchase_order_id VARCHAR NOT NULL,
    revision INTEGER NOT NULL,
    total NUMERIC(18, 2),
    reason VARCHAR,
    changed_by VARCHAR,
    changed_at TIMESTAMP,
    lines_json VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (purchase_order_id, revision)
);

CREATE TABLE IF NOT EXISTS pur_order_send (
    purchase_order_id VARCHAR NOT NULL,
    sent_at TIMESTAMP NOT NULL,
    sent_to VARCHAR,
    sent_by VARCHAR,
    revision INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (purchase_order_id, sent_at)
);

CREATE TABLE IF NOT EXISTS pur_order_versions (
    purchase_order_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP,
    PRIMARY KEY (purchase_order_id, version)
);

CREATE TABLE IF NOT EXISTS pur_payment (
    payment_id VARCHAR NOT NULL,
    run_id VARCHAR,
    bp_id BIGINT,
    currency VARCHAR,
    amount NUMERIC(18, 2),
    gross NUMERIC(18, 2),
    cis_deduction NUMERIC(18, 2),
    cis_rate_pct NUMERIC(19, 4),
    cis_status VARCHAR,
    cis_ref VARCHAR,
    cis_tax_month VARCHAR,
    bank_account_id VARCHAR,
    bank_reference VARCHAR,
    reference VARCHAR,
    paid_on DATE,
    status VARCHAR,
    kind VARCHAR,
    payee_bank_valid_from DATE,
    confirmed_by VARCHAR,
    reject_reason VARCHAR,
    posted_journal_id VARCHAR,
    post_note VARCHAR,
    source_kind VARCHAR,
    version_note VARCHAR,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (payment_id)
);

CREATE TABLE IF NOT EXISTS pur_payment_run (
    run_id VARCHAR NOT NULL,
    bank_account_id VARCHAR,
    gl_account_id VARCHAR,
    currency VARCHAR,
    pay_date DATE,
    file_format VARCHAR,
    status VARCHAR,
    proposed_by_email VARCHAR,
    proposed_by_sub VARCHAR,
    approved_by_email VARCHAR,
    approved_by_sub VARCHAR,
    approved_on DATE,
    file_key VARCHAR,
    file_sha256 VARCHAR,
    exported_on DATE,
    cancel_reason VARCHAR,
    left_out VARCHAR,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (run_id)
);

CREATE TABLE IF NOT EXISTS pur_payment_run_versions (
    run_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP,
    PRIMARY KEY (run_id, version)
);

CREATE TABLE IF NOT EXISTS pur_payment_versions (
    payment_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP,
    PRIMARY KEY (payment_id, version)
);

CREATE TABLE IF NOT EXISTS pur_quota (
    product_id VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    bp_id BIGINT NOT NULL,
    quota_pct NUMERIC(19, 4),
    base_quantity NUMERIC(19, 6),
    max_quantity NUMERIC(19, 6),
    set_by_email VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (product_id, valid_from, bp_id)
);

CREATE TABLE IF NOT EXISTS pur_quote (
    rfq_id VARCHAR NOT NULL,
    bp_id BIGINT NOT NULL,
    revision INTEGER NOT NULL,
    received_on DATE,
    late BOOLEAN,
    valid_to DATE,
    currency VARCHAR,
    lines_json VARCHAR,
    net_total NUMERIC(18, 2),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (rfq_id, bp_id, revision)
);

CREATE TABLE IF NOT EXISTS pur_receipt (
    receipt_id VARCHAR NOT NULL,
    kind VARCHAR,
    purchase_order_id VARCHAR,
    delivery_note VARCHAR,
    received_on DATE,
    received_by_email VARCHAR,
    received_by_sub VARCHAR,
    location_id VARCHAR,
    reason VARCHAR,
    corrects_receipt_id VARCHAR,
    status VARCHAR,
    bp_id BIGINT,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (receipt_id)
);

CREATE TABLE IF NOT EXISTS pur_receipt_line (
    receipt_id VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    order_line_no INTEGER,
    product_id VARCHAR,
    quantity NUMERIC(19, 6),
    unit_code VARCHAR,
    quantity_base NUMERIC(19, 6),
    location_id VARCHAR,
    move_ref VARCHAR,
    stock_move_id VARCHAR,
    handed_over_at TIMESTAMP,
    handover_note VARCHAR,
    reverses_line_no INTEGER,
    catch_quantity NUMERIC(19, 6),
    catch_unit_code VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (receipt_id, line_no)
);

CREATE TABLE IF NOT EXISTS pur_receipt_versions (
    receipt_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP,
    PRIMARY KEY (receipt_id, version)
);

CREATE TABLE IF NOT EXISTS pur_release (
    document_kind VARCHAR NOT NULL,
    document_id VARCHAR NOT NULL,
    revision INTEGER NOT NULL,
    level INTEGER NOT NULL,
    approved_by_email VARCHAR,
    approved_by_sub VARCHAR,
    approved_on DATE,
    strategy_valid_from DATE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (document_kind, document_id, revision, level)
);

CREATE TABLE IF NOT EXISTS pur_release_strategy (
    category VARCHAR NOT NULL,
    min_amount NUMERIC(18, 2) NOT NULL,
    valid_from DATE NOT NULL,
    levels INTEGER,
    level_thresholds VARCHAR,
    currency VARCHAR,
    set_by_email VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (category, min_amount, valid_from)
);

CREATE TABLE IF NOT EXISTS pur_request (
    purchase_request_id VARCHAR NOT NULL,
    requested_by_email VARCHAR,
    requested_by_sub VARCHAR,
    cost_centre VARCHAR,
    category VARCHAR,
    reason VARCHAR,
    amount NUMERIC(18, 2),
    currency VARCHAR,
    status VARCHAR,
    needed_by DATE,
    submitted_on DATE,
    approved_by_email VARCHAR,
    approved_by_sub VARCHAR,
    approved_on DATE,
    approval_note VARCHAR,
    refused_reason VARCHAR,
    closed_reason VARCHAR,
    release_levels INTEGER,
    release_thresholds VARCHAR,
    strategy_valid_from DATE,
    source_kind VARCHAR,
    request_ref VARCHAR,
    ship_to_bp_id BIGINT,
    ship_to_address VARCHAR,
    sales_order_id VARCHAR,
    so_line_no INTEGER,
    warehouse_id VARCHAR,
    location_id VARCHAR,
    order_id VARCHAR,
    ship_to_address_lines VARCHAR,
    ship_to_address_city VARCHAR,
    ship_to_address_postcode VARCHAR,
    ship_to_address_region VARCHAR,
    ship_to_address_country VARCHAR,
    cancelled_by_email VARCHAR,
    cancelled_by_sub VARCHAR,
    cancelled_on DATE,
    cancel_reason VARCHAR,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (purchase_request_id)
);

CREATE TABLE IF NOT EXISTS pur_request_line (
    purchase_request_id VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    product_id VARCHAR,
    description VARCHAR,
    quantity NUMERIC(19, 6),
    unit_code VARCHAR,
    unit_price NUMERIC(19, 4),
    net_amount NUMERIC(18, 2),
    bp_id BIGINT,
    needed_by DATE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (purchase_request_id, line_no)
);

CREATE TABLE IF NOT EXISTS pur_request_versions (
    purchase_request_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP,
    PRIMARY KEY (purchase_request_id, version)
);

CREATE TABLE IF NOT EXISTS pur_rfq (
    rfq_id VARCHAR NOT NULL,
    rfq_no VARCHAR,
    request_id VARCHAR,
    status VARCHAR,
    deadline DATE,
    currency VARCHAR,
    created_by_email VARCHAR,
    created_by_sub VARCHAR,
    sent_on DATE,
    awarded_bp_id BIGINT,
    award_reason VARCHAR,
    order_id VARCHAR,
    close_reason VARCHAR,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (rfq_id)
);

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

CREATE TABLE IF NOT EXISTS pur_rfq_supplier (
    rfq_id VARCHAR NOT NULL,
    bp_id BIGINT NOT NULL,
    first_sent_at TIMESTAMP,
    sends INTEGER,
    sent_to VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (rfq_id, bp_id)
);

CREATE TABLE IF NOT EXISTS pur_rfq_versions (
    rfq_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP,
    PRIMARY KEY (rfq_id, version)
);

CREATE TABLE IF NOT EXISTS pur_run_item (
    run_id VARCHAR NOT NULL,
    payment_id VARCHAR NOT NULL,
    bill_id VARCHAR NOT NULL,
    amount NUMERIC(18, 2),
    kind VARCHAR,
    labour_paid NUMERIC(18, 2),
    materials_paid NUMERIC(18, 2),
    recorded_by VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (run_id, payment_id, bill_id)
);

CREATE TABLE IF NOT EXISTS pur_schedule_line (
    agreement_id VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    revision INTEGER NOT NULL,
    delivery_date DATE NOT NULL,
    quantity NUMERIC(19, 6),
    unit_code VARCHAR,
    firm BOOLEAN,
    changed_reason VARCHAR,
    released_jit BOOLEAN,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (agreement_id, line_no, revision, delivery_date)
);

CREATE TABLE IF NOT EXISTS pur_schedule_release (
    agreement_id VARCHAR NOT NULL,
    release_no INTEGER NOT NULL,
    kind VARCHAR,
    sent_at TIMESTAMP,
    sent_to VARCHAR,
    lines_json VARCHAR,
    call_off_order_id VARCHAR,
    sent_by VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (agreement_id, release_no)
);

CREATE TABLE IF NOT EXISTS pur_self_billing (
    bp_id BIGINT NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE,
    document_key VARCHAR,
    supplier_vat_number VARCHAR,
    status VARCHAR,
    entered_by_email VARCHAR,
    entered_by_sub VARCHAR,
    approved_by_email VARCHAR,
    approved_by_sub VARCHAR,
    end_reason VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (bp_id, valid_from)
);

CREATE TABLE IF NOT EXISTS pur_service_score (
    bp_id BIGINT NOT NULL,
    period_id VARCHAR NOT NULL,
    score INTEGER,
    set_by_email VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (bp_id, period_id)
);

CREATE TABLE IF NOT EXISTS pur_settings (
    setting_key VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    value VARCHAR,
    set_by VARCHAR,
    set_at TIMESTAMP,
    changes VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (setting_key, valid_from)
);

CREATE TABLE IF NOT EXISTS pur_source (
    product_id VARCHAR NOT NULL,
    bp_id BIGINT NOT NULL,
    valid_from DATE NOT NULL,
    fixed BOOLEAN,
    blocked BOOLEAN,
    valid_to DATE,
    agreement_id VARCHAR,
    required BOOLEAN,
    set_by_email VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (product_id, bp_id, valid_from)
);

CREATE TABLE IF NOT EXISTS pur_supplier (
    bp_id BIGINT NOT NULL,
    valid_from DATE NOT NULL,
    terms_id VARCHAR,
    currency VARCHAR,
    payment_method VARCHAR,
    remittance_email VARCHAR,
    reclaims_vat BOOLEAN,
    tolerance_group VARCHAR,
    cis_subcontractor BOOLEAN,
    ers BOOLEAN,
    legal_name VARCHAR,
    country VARCHAR,
    set_by_email VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (bp_id, valid_from)
);

CREATE TABLE IF NOT EXISTS pur_supplier_bank (
    bp_id BIGINT NOT NULL,
    valid_from DATE NOT NULL,
    iban_or_account VARCHAR,
    bic_or_sort_code VARCHAR,
    account_name VARCHAR,
    status VARCHAR,
    entered_by_email VARCHAR,
    entered_by_sub VARCHAR,
    approved_by_email VARCHAR,
    approved_by_sub VARCHAR,
    call_back_note VARCHAR,
    reject_reason VARCHAR,
    country VARCHAR,
    requested_by VARCHAR,
    approved_on DATE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (bp_id, valid_from)
);

CREATE TABLE IF NOT EXISTS pur_supplier_price (
    bp_id BIGINT NOT NULL,
    product_id VARCHAR NOT NULL,
    unit_code VARCHAR NOT NULL,
    min_quantity NUMERIC(19, 6) NOT NULL,
    valid_from DATE NOT NULL,
    unit_price NUMERIC(19, 4),
    pricing_unit_code VARCHAR,
    pricing_quantity INTEGER,
    pricing_numerator INTEGER,
    pricing_denominator INTEGER,
    order_multiple NUMERIC(19, 6),
    currency VARCHAR,
    lead_days INTEGER,
    supplier_product_code VARCHAR,
    supplier_product_name VARCHAR,
    valid_to DATE,
    consignment BOOLEAN,
    set_by_email VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (bp_id, product_id, unit_code, min_quantity, valid_from)
);

CREATE TABLE IF NOT EXISTS pur_task (
    task_id VARCHAR NOT NULL,
    capability VARCHAR,
    subject_ref VARCHAR,
    plan VARCHAR,
    reason VARCHAR,
    status VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (task_id)
);

CREATE TABLE IF NOT EXISTS pur_terms (
    terms_id VARCHAR NOT NULL,
    name VARCHAR,
    days INTEGER,
    end_of_month BOOLEAN,
    discount_pct NUMERIC(19, 4),
    discount_days INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (terms_id)
);

CREATE TABLE IF NOT EXISTS pur_tolerance (
    tolerance_group VARCHAR NOT NULL,
    key VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    lower_amount NUMERIC(18, 2),
    upper_amount NUMERIC(18, 2),
    lower_pct NUMERIC(19, 4),
    upper_pct NUMERIC(19, 4),
    set_by_email VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (tolerance_group, key, valid_from)
);

CREATE TABLE IF NOT EXISTS pur_variance_acceptance (
    bill_id VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    kind VARCHAR NOT NULL,
    key VARCHAR,
    difference VARCHAR,
    tolerance_in_force VARCHAR,
    accepted_by_email VARCHAR,
    accepted_by_sub VARCHAR,
    accepted_on DATE,
    reason VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (bill_id, line_no, kind)
);