dodil/erp-brick-gl

DODIL ERP brick: general ledger

src/brick_gl/migrations/00001_init/up.sql

-- 00001_init: the brick's first schema (the same tables as schema.sql).

CREATE TABLE IF NOT EXISTS gl_account (
    account_id            BIGINT NOT NULL,
    chart_id              VARCHAR,
    code                  VARCHAR,
    name                  VARCHAR,
    type                  VARCHAR,
    subtype               VARCHAR,
    normal_balance        VARCHAR,
    currency              VARCHAR,
    parent_account_id     BIGINT,
    monetary              BOOLEAN,
    source_only           BOOLEAN,
    fs_cost_centre        VARCHAR,
    fs_profit_centre      VARCHAR,
    fs_bp_id              VARCHAR,
    fs_tax_code           VARCHAR,
    default_profit_centre VARCHAR,
    movement_tracked      BOOLEAN,
    active_until          DATE,
    created_at            TIMESTAMP,
    updated_at            TIMESTAMP,
    PRIMARY KEY (account_id)
);

CREATE TABLE IF NOT EXISTS gl_account_edge (
    parent_id  BIGINT NOT NULL,
    child_id   BIGINT NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (parent_id, child_id)
);

CREATE TABLE IF NOT EXISTS gl_account_map (
    chart_id   VARCHAR NOT NULL,
    purpose    VARCHAR NOT NULL,
    qualifier  VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    account_id BIGINT,
    set_by     VARCHAR,
    set_at     TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (chart_id, purpose, qualifier, valid_from)
);

CREATE TABLE IF NOT EXISTS gl_accrual (
    company_code       VARCHAR NOT NULL,
    accrual_code       VARCHAR NOT NULL,
    kind               VARCHAR,
    ledger_id          VARCHAR,
    amount             NUMERIC(18,2),
    currency           VARCHAR,
    pl_account_id      BIGINT,
    balance_account_id BIGINT,
    cost_centre        VARCHAR,
    profit_centre      VARCHAR,
    start_period       VARCHAR,
    end_period         VARCHAR,
    method             VARCHAR,
    source_journal_id  BIGINT,
    status             VARCHAR,
    registered_by      VARCHAR,
    registered_sub     VARCHAR,
    approved_by        VARCHAR,
    approved_sub       VARCHAR,
    terminated_period  VARCHAR,
    reject_reason      VARCHAR,
    created_at         TIMESTAMP,
    updated_at         TIMESTAMP,
    PRIMARY KEY (company_code, accrual_code)
);

CREATE TABLE IF NOT EXISTS gl_accrual_release (
    company_code VARCHAR NOT NULL,
    accrual_code VARCHAR NOT NULL,
    period_id    VARCHAR NOT NULL,
    amount       NUMERIC(18,2),
    journal_id   BIGINT,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (company_code, accrual_code, period_id)
);

CREATE TABLE IF NOT EXISTS gl_ai_finding (
    finding_id    VARCHAR NOT NULL,
    journal_id    BIGINT,
    recon_id      VARCHAR,
    model         VARCHAR,
    inputs_digest VARCHAR,
    finding       VARCHAR,
    evidence      VARCHAR,
    decision      VARCHAR,
    decided_by    VARCHAR,
    decided_sub   VARCHAR,
    decided_at    TIMESTAMP,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (finding_id)
);

CREATE TABLE IF NOT EXISTS gl_allocation_cycle (
    cycle_id          VARCHAR NOT NULL,
    version           INTEGER NOT NULL,
    company_code      VARCHAR,
    ledger_id         VARCHAR,
    valid_from_period VARCHAR,
    valid_to_period   VARCHAR,
    segments_json     VARCHAR,
    status            VARCHAR,
    author            VARCHAR,
    author_sub        VARCHAR,
    approved_by       VARCHAR,
    approved_sub      VARCHAR,
    retired_reason    VARCHAR,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    PRIMARY KEY (cycle_id, version)
);

CREATE TABLE IF NOT EXISTS gl_allocation_run (
    run_id        VARCHAR NOT NULL,
    cycle_id      VARCHAR,
    cycle_version INTEGER,
    company_code  VARCHAR,
    ledger_id     VARCHAR,
    period_id     VARCHAR,
    run_no        INTEGER,
    status        VARCHAR,
    sender_digest VARCHAR,
    results_json  VARCHAR,
    journal_ids   VARCHAR,
    simulated_by  VARCHAR,
    posted_by     VARCHAR,
    reversed_by   VARCHAR,
    reason        VARCHAR,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (run_id)
);

CREATE TABLE IF NOT EXISTS gl_alt_account (
    company_code VARCHAR NOT NULL,
    account_id   BIGINT NOT NULL,
    valid_from   DATE NOT NULL,
    alt_code     VARCHAR,
    alt_name     VARCHAR,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (company_code, account_id, valid_from)
);

CREATE TABLE IF NOT EXISTS gl_audit_export (
    export_id    VARCHAR NOT NULL,
    kind         VARCHAR,
    company_code VARCHAR,
    fiscal_year  VARCHAR,
    object_key   VARCHAR,
    sha256       VARCHAR,
    row_count    INTEGER,
    produced_by  VARCHAR,
    produced_sub VARCHAR,
    produced_at  TIMESTAMP,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (export_id)
);

CREATE TABLE IF NOT EXISTS gl_balance (
    company_code      VARCHAR NOT NULL,
    ledger_id         VARCHAR NOT NULL,
    account_id        BIGINT NOT NULL,
    period_id         VARCHAR NOT NULL,
    profit_centre     VARCHAR NOT NULL,
    segment           VARCHAR NOT NULL,
    partner_company   VARCHAR NOT NULL,
    debit             NUMERIC(18,2),
    credit            NUMERIC(18,2),
    closing           NUMERIC(18,2),
    closing_currency  NUMERIC(18,2),
    adjustment_debit  NUMERIC(18,2),
    adjustment_credit NUMERIC(18,2),
    derived_at        TIMESTAMP,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    PRIMARY KEY (company_code, ledger_id, account_id, period_id, profit_centre, segment, partner_company)
);

CREATE TABLE IF NOT EXISTS gl_budget (
    budget_id     VARCHAR NOT NULL,
    version       INTEGER NOT NULL,
    company_code  VARCHAR,
    ledger_id     VARCHAR,
    fiscal_year   VARCHAR,
    status        VARCHAR,
    submitted_by  VARCHAR,
    submitted_sub VARCHAR,
    approved_by   VARCHAR,
    approved_sub  VARCHAR,
    return_reason VARCHAR,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (budget_id, version)
);

CREATE TABLE IF NOT EXISTS gl_budget_line (
    budget_id     VARCHAR NOT NULL,
    version       INTEGER NOT NULL,
    account_id    BIGINT NOT NULL,
    profit_centre VARCHAR NOT NULL,
    cost_centre   VARCHAR NOT NULL,
    period_id     VARCHAR NOT NULL,
    amount        NUMERIC(18,2),
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (budget_id, version, account_id, profit_centre, cost_centre, period_id)
);

CREATE TABLE IF NOT EXISTS gl_carry_forward (
    company_code     VARCHAR NOT NULL,
    ledger_id        VARCHAR NOT NULL,
    fiscal_year      VARCHAR NOT NULL,
    account_id       BIGINT NOT NULL,
    profit_centre    VARCHAR NOT NULL,
    segment          VARCHAR NOT NULL,
    partner_company  VARCHAR NOT NULL,
    opening          NUMERIC(18,2),
    opening_currency NUMERIC(18,2),
    derived_at       TIMESTAMP,
    source_digest    VARCHAR,
    created_at       TIMESTAMP,
    updated_at       TIMESTAMP,
    PRIMARY KEY (company_code, ledger_id, fiscal_year, account_id, profit_centre, segment, partner_company)
);

CREATE TABLE IF NOT EXISTS gl_chain_claim (
    company_code VARCHAR NOT NULL,
    chain_seq    INTEGER NOT NULL,
    journal_id   BIGINT,
    claimed_at   TIMESTAMP,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (company_code, chain_seq)
);

CREATE TABLE IF NOT EXISTS gl_chart (
    chart_id   VARCHAR NOT NULL,
    name       VARCHAR,
    kind       VARCHAR,
    country    VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (chart_id)
);

CREATE TABLE IF NOT EXISTS gl_close_run (
    run_id       VARCHAR NOT NULL,
    company_code VARCHAR,
    period_id    VARCHAR,
    kind         VARCHAR,
    status       VARCHAR,
    task_list_id VARCHAR,
    started_by   VARCHAR,
    started_sub  VARCHAR,
    tb_digest    VARCHAR,
    signed_by    VARCHAR,
    signed_sub   VARCHAR,
    signed_at    TIMESTAMP,
    stale_reason VARCHAR,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (run_id)
);

CREATE TABLE IF NOT EXISTS gl_close_task (
    run_id       VARCHAR NOT NULL,
    task_id      VARCHAR NOT NULL,
    title        VARCHAR,
    kind_of_task VARCHAR,
    gate         VARCHAR,
    owner_role   VARCHAR,
    due_on       DATE,
    depends_on   VARCHAR,
    status       VARCHAR,
    done_by      VARCHAR,
    done_sub     VARCHAR,
    done_at      TIMESTAMP,
    note         VARCHAR,
    evidence_key VARCHAR,
    reason       VARCHAR,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (run_id, task_id)
);

CREATE TABLE IF NOT EXISTS gl_close_task_list (
    task_list_id     VARCHAR NOT NULL,
    task_id          VARCHAR NOT NULL,
    company_code     VARCHAR,
    kind             VARCHAR,
    title            VARCHAR,
    kind_of_task     VARCHAR,
    gate             VARCHAR,
    owner_role       VARCHAR,
    due_business_day INTEGER,
    depends_on       VARCHAR,
    required         BOOLEAN,
    created_at       TIMESTAMP,
    updated_at       TIMESTAMP,
    PRIMARY KEY (task_list_id, task_id)
);

CREATE TABLE IF NOT EXISTS gl_company (
    company_code               VARCHAR NOT NULL,
    name                       VARCHAR,
    country                    VARCHAR,
    functional_currency        VARCHAR,
    chart_id                   VARCHAR,
    financial_year_end_month   INTEGER,
    bp_id                      BIGINT,
    default_profit_centre      VARCHAR,
    splitting_from_fiscal_year VARCHAR,
    is_default                 BOOLEAN,
    active_until               DATE,
    created_at                 TIMESTAMP,
    updated_at                 TIMESTAMP,
    PRIMARY KEY (company_code)
);

CREATE TABLE IF NOT EXISTS gl_company_access (
    principal_sub   VARCHAR NOT NULL,
    company_code    VARCHAR NOT NULL,
    valid_from      DATE NOT NULL,
    principal_email VARCHAR,
    valid_until     DATE,
    set_by          VARCHAR,
    set_at          TIMESTAMP,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (principal_sub, company_code, valid_from)
);

CREATE TABLE IF NOT EXISTS gl_company_ledger (
    company_code      VARCHAR NOT NULL,
    ledger_id         VARCHAR NOT NULL,
    from_fiscal_year  VARCHAR,
    until_fiscal_year VARCHAR,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    PRIMARY KEY (company_code, ledger_id)
);

CREATE TABLE IF NOT EXISTS gl_cost_centre (
    cost_centre  VARCHAR NOT NULL,
    name         VARCHAR,
    company_code VARCHAR,
    active_until DATE,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (cost_centre)
);

CREATE TABLE IF NOT EXISTS gl_cost_centre_assignment (
    cost_centre   VARCHAR NOT NULL,
    valid_from    DATE NOT NULL,
    profit_centre VARCHAR,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (cost_centre, valid_from)
);

CREATE TABLE IF NOT EXISTS gl_fiscal_year (
    company_code VARCHAR NOT NULL,
    fiscal_year  VARCHAR NOT NULL,
    starts_on    DATE,
    ends_on      DATE,
    status       VARCHAR,
    closed_by    VARCHAR,
    closed_at    TIMESTAMP,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (company_code, fiscal_year)
);

CREATE TABLE IF NOT EXISTS gl_fx_rate (
    from_ccy    VARCHAR NOT NULL,
    to_ccy      VARCHAR NOT NULL,
    rate_type   VARCHAR NOT NULL,
    rate_date   DATE NOT NULL,
    rate        NUMERIC(19,6),
    source      VARCHAR,
    entered_by  VARCHAR,
    entered_sub VARCHAR,
    entered_at  TIMESTAMP,
    created_at  TIMESTAMP,
    updated_at  TIMESTAMP,
    PRIMARY KEY (from_ccy, to_ccy, rate_type, rate_date)
);

CREATE TABLE IF NOT EXISTS gl_fx_reval_item (
    run_id              VARCHAR NOT NULL,
    account_id          BIGINT NOT NULL,
    currency            VARCHAR NOT NULL,
    document            VARCHAR NOT NULL,
    amount_currency     NUMERIC(18,2),
    booked_functional   NUMERIC(18,2),
    rate                NUMERIC(19,6),
    revalued_functional NUMERIC(18,2),
    gain_loss           NUMERIC(18,2),
    profit_centre       VARCHAR,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    PRIMARY KEY (run_id, account_id, currency, document)
);

CREATE TABLE IF NOT EXISTS gl_fx_reval_run (
    run_id              VARCHAR NOT NULL,
    company_code        VARCHAR,
    ledger_id           VARCHAR,
    key_date            DATE,
    kind                VARCHAR,
    journal_id          BIGINT,
    reversal_journal_id BIGINT,
    method              VARCHAR,
    run_by              VARCHAR,
    run_at              TIMESTAMP,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    PRIMARY KEY (run_id)
);

CREATE TABLE IF NOT EXISTS gl_group_account_map (
    account_id       BIGINT NOT NULL,
    valid_from       DATE NOT NULL,
    group_account_id BIGINT,
    set_by           VARCHAR,
    set_at           TIMESTAMP,
    created_at       TIMESTAMP,
    updated_at       TIMESTAMP,
    PRIMARY KEY (account_id, valid_from)
);

CREATE TABLE IF NOT EXISTS gl_ic_document (
    ic_document_id BIGINT NOT NULL,
    companies      VARCHAR,
    journal_ids    VARCHAR,
    proposed_by    VARCHAR,
    proposed_sub   VARCHAR,
    approved_by    VARCHAR,
    approved_sub   VARCHAR,
    status         VARCHAR,
    header_json    VARCHAR,
    lines_json     VARCHAR,
    reason         VARCHAR,
    created_at     TIMESTAMP,
    updated_at     TIMESTAMP,
    PRIMARY KEY (ic_document_id)
);

CREATE TABLE IF NOT EXISTS gl_ic_match (
    match_id         VARCHAR NOT NULL,
    company_code     VARCHAR,
    partner_company  VARCHAR,
    as_at            DATE,
    matched_json     VARCHAR,
    breaks_json      VARCHAR,
    within_tolerance BOOLEAN,
    accepted_by      VARCHAR,
    accepted_sub     VARCHAR,
    accepted_reason  VARCHAR,
    run_at           TIMESTAMP,
    created_at       TIMESTAMP,
    updated_at       TIMESTAMP,
    PRIMARY KEY (match_id)
);

CREATE TABLE IF NOT EXISTS gl_journal (
    journal_id          BIGINT NOT NULL,
    company_code        VARCHAR,
    ledger_id           VARCHAR,
    status              VARCHAR,
    journal_date        DATE,
    period_id           VARCHAR,
    fiscal_year         VARCHAR,
    book                VARCHAR,
    entry_no            INTEGER,
    source_module       VARCHAR,
    source_document     VARCHAR,
    event               VARCHAR,
    currency            VARCHAR,
    fx_rate             NUMERIC(19,6),
    memo                VARCHAR,
    total               NUMERIC(18,2),
    functional_total    NUMERIC(18,2),
    adjustment          BOOLEAN,
    reverse_next_period BOOLEAN,
    reversal_status     VARCHAR,
    reversal_reason     VARCHAR,
    ic_document_id      BIGINT,
    recurring_id        VARCHAR,
    accrual_id          VARCHAR,
    allocation_run_id   VARCHAR,
    reval_run_id        VARCHAR,
    proposed_by         VARCHAR,
    proposed_sub        VARCHAR,
    approved_by         VARCHAR,
    approved_sub        VARCHAR,
    rejected_by         VARCHAR,
    reject_reason       VARCHAR,
    posted_at           TIMESTAMP,
    reverses_journal_id BIGINT,
    body_digest         VARCHAR,
    chain_seq           INTEGER,
    prev_hash           VARCHAR,
    row_hash            VARCHAR,
    migration_run_id    VARCHAR,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    PRIMARY KEY (journal_id)
);

CREATE TABLE IF NOT EXISTS gl_journal_audit (
    audit_id   VARCHAR NOT NULL,
    journal_id BIGINT,
    event      VARCHAR,
    by_email   VARCHAR,
    by_sub     VARCHAR,
    event_at   TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (audit_id)
);

CREATE TABLE IF NOT EXISTS gl_journal_line (
    journal_id        BIGINT NOT NULL,
    line_no           INTEGER NOT NULL,
    account_id        BIGINT,
    purpose           VARCHAR,
    qualifier         VARCHAR,
    debit             NUMERIC(18,2),
    credit            NUMERIC(18,2),
    functional_debit  NUMERIC(18,2),
    functional_credit NUMERIC(18,2),
    currency          VARCHAR,
    amount_currency   NUMERIC(18,2),
    bp_id             BIGINT,
    tax_code          VARCHAR,
    cost_centre       VARCHAR,
    profit_centre     VARCHAR,
    partner_company   VARCHAR,
    movement_type     VARCHAR,
    against_document  VARCHAR,
    ic_reference      VARCHAR,
    asset_id          VARCHAR,
    line_memo         VARCHAR,
    odoo_line_id      VARCHAR,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    PRIMARY KEY (journal_id, line_no)
);

CREATE TABLE IF NOT EXISTS gl_key_figure_value (
    key_figure    VARCHAR NOT NULL,
    period_id     VARCHAR NOT NULL,
    receiver      VARCHAR NOT NULL,
    value         NUMERIC(19,4),
    entered_by    VARCHAR,
    entered_at    TIMESTAMP,
    frozen_by_run VARCHAR,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (key_figure, period_id, receiver)
);

CREATE TABLE IF NOT EXISTS gl_ledger (
    ledger_id            VARCHAR NOT NULL,
    name                 VARCHAR,
    kind                 VARCHAR,
    accounting_principle VARCHAR,
    reval_method         VARCHAR,
    created_at           TIMESTAMP,
    updated_at           TIMESTAMP,
    PRIMARY KEY (ledger_id)
);

CREATE TABLE IF NOT EXISTS gl_master_audit (
    audit_id    VARCHAR NOT NULL,
    entity      VARCHAR,
    natural_key VARCHAR,
    before_json VARCHAR,
    after_json  VARCHAR,
    by_email    VARCHAR,
    by_sub      VARCHAR,
    event_at    TIMESTAMP,
    created_at  TIMESTAMP,
    updated_at  TIMESTAMP,
    PRIMARY KEY (audit_id)
);

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

CREATE TABLE IF NOT EXISTS gl_migration_run (
    run_id       VARCHAR NOT NULL,
    odoo_db      VARCHAR,
    cutover_date DATE,
    status       VARCHAR,
    doors_json   VARCHAR,
    checks_json  VARCHAR,
    counts_json  VARCHAR,
    checksums    VARCHAR,
    loaded_at    TIMESTAMP,
    signed_by    VARCHAR,
    signed_at    TIMESTAMP,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (run_id)
);

CREATE TABLE IF NOT EXISTS gl_number_series (
    company_code VARCHAR NOT NULL,
    book         VARCHAR NOT NULL,
    fiscal_year  VARCHAR NOT NULL,
    entry_no     INTEGER NOT NULL,
    journal_id   BIGINT,
    claimed_at   TIMESTAMP,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (company_code, book, fiscal_year, entry_no)
);

CREATE TABLE IF NOT EXISTS gl_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 gl_period (
    company_code     VARCHAR NOT NULL,
    period_id        VARCHAR NOT NULL,
    fiscal_year      VARCHAR,
    starts_on        DATE,
    ends_on          DATE,
    status           VARCHAR,
    closed_by        VARCHAR,
    closed_sub       VARCHAR,
    closed_at        TIMESTAMP,
    last_posted_at   TIMESTAMP,
    statements_stale BOOLEAN,
    created_at       TIMESTAMP,
    updated_at       TIMESTAMP,
    PRIMARY KEY (company_code, period_id)
);

CREATE TABLE IF NOT EXISTS gl_period_event (
    event_id     VARCHAR NOT NULL,
    company_code VARCHAR,
    period_id    VARCHAR,
    event        VARCHAR,
    reason       VARCHAR,
    by_email     VARCHAR,
    by_sub       VARCHAR,
    event_at     TIMESTAMP,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (event_id)
);

CREATE TABLE IF NOT EXISTS gl_profit_centre (
    profit_centre        VARCHAR NOT NULL,
    name                 VARCHAR,
    parent_profit_centre VARCHAR,
    company_codes        VARCHAR,
    valid_from           DATE,
    valid_until          DATE,
    created_at           TIMESTAMP,
    updated_at           TIMESTAMP,
    PRIMARY KEY (profit_centre)
);

CREATE TABLE IF NOT EXISTS gl_profit_centre_segment (
    profit_centre VARCHAR NOT NULL,
    valid_from    DATE NOT NULL,
    segment       VARCHAR,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (profit_centre, valid_from)
);

CREATE TABLE IF NOT EXISTS gl_reconciliation (
    recon_id           VARCHAR NOT NULL,
    company_code       VARCHAR,
    control_account_id BIGINT,
    as_at              DATE,
    kind               VARCHAR,
    gl_balance         NUMERIC(18,2),
    outstanding_sum    NUMERIC(18,2),
    variance           NUMERIC(18,2),
    breaks_json        VARCHAR,
    accepted_by        VARCHAR,
    accepted_reason    VARCHAR,
    created_at         TIMESTAMP,
    updated_at         TIMESTAMP,
    PRIMARY KEY (recon_id)
);

CREATE TABLE IF NOT EXISTS gl_recurring_journal (
    recurring_id VARCHAR NOT NULL,
    version      INTEGER NOT NULL,
    company_code VARCHAR,
    ledger_id    VARCHAR,
    lines_json   VARCHAR,
    memo         VARCHAR,
    frequency    VARCHAR,
    first_period VARCHAR,
    last_period  VARCHAR,
    posting_day  VARCHAR,
    status       VARCHAR,
    proposed_by  VARCHAR,
    proposed_sub VARCHAR,
    approved_by  VARCHAR,
    approved_sub VARCHAR,
    ended_at     TIMESTAMP,
    end_reason   VARCHAR,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (recurring_id, version)
);

CREATE TABLE IF NOT EXISTS gl_recurring_posting (
    recurring_id   VARCHAR NOT NULL,
    version        INTEGER NOT NULL,
    period_id      VARCHAR NOT NULL,
    journal_id     BIGINT,
    skipped_reason VARCHAR,
    created_at     TIMESTAMP,
    updated_at     TIMESTAMP,
    PRIMARY KEY (recurring_id, version, period_id)
);

CREATE TABLE IF NOT EXISTS gl_segment (
    segment      VARCHAR NOT NULL,
    name         VARCHAR,
    active_until DATE,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (segment)
);

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

CREATE TABLE IF NOT EXISTS gl_split_line (
    journal_id        BIGINT NOT NULL,
    line_no           INTEGER NOT NULL,
    split_no          INTEGER NOT NULL,
    company_code      VARCHAR,
    ledger_id         VARCHAR,
    account_id        BIGINT,
    period_id         VARCHAR,
    journal_date      DATE,
    debit             NUMERIC(18,2),
    credit            NUMERIC(18,2),
    functional_debit  NUMERIC(18,2),
    functional_credit NUMERIC(18,2),
    currency          VARCHAR,
    profit_centre     VARCHAR,
    segment           VARCHAR,
    cost_centre       VARCHAR,
    partner_company   VARCHAR,
    movement_type     VARCHAR,
    bp_id             BIGINT,
    asset_id          VARCHAR,
    is_clearing       BOOLEAN,
    adjustment        BOOLEAN,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    PRIMARY KEY (journal_id, line_no, split_no)
);

CREATE TABLE IF NOT EXISTS gl_statement (
    report       VARCHAR NOT NULL,
    company_code VARCHAR NOT NULL,
    ledger_id    VARCHAR NOT NULL,
    period_id    VARCHAR NOT NULL,
    account_id   BIGINT NOT NULL,
    debit        NUMERIC(18,2),
    credit       NUMERIC(18,2),
    closing      NUMERIC(18,2),
    produced_at  TIMESTAMP,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (report, company_code, ledger_id, period_id, account_id)
);

CREATE TABLE IF NOT EXISTS gl_statement_assignment (
    fsv_id        VARCHAR NOT NULL,
    version_no    INTEGER NOT NULL,
    from_code     VARCHAR NOT NULL,
    to_code       VARCHAR NOT NULL,
    node_id       VARCHAR,
    dc_rule       VARCHAR,
    other_node_id VARCHAR,
    basis         VARCHAR,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (fsv_id, version_no, from_code, to_code)
);

CREATE TABLE IF NOT EXISTS gl_statement_node (
    fsv_id          VARCHAR NOT NULL,
    version_no      INTEGER NOT NULL,
    node_id         VARCHAR NOT NULL,
    parent_node_id  VARCHAR,
    seq             INTEGER,
    label           VARCHAR,
    sign            INTEGER,
    cash_flow_class VARCHAR,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (fsv_id, version_no, node_id)
);

CREATE TABLE IF NOT EXISTS gl_statement_version (
    fsv_id       VARCHAR NOT NULL,
    version_no   INTEGER NOT NULL,
    name         VARCHAR,
    kind         VARCHAR,
    chart_id     VARCHAR,
    status       VARCHAR,
    drafted_by   VARCHAR,
    published_by VARCHAR,
    published_at TIMESTAMP,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (fsv_id, version_no)
);