-- Pacific Logistics · slice 28 · Bank Reconciliation (Iter 5 Turn 2)
--
-- Adds:
--   bank_statements       — one row per imported statement (CRDB / NMB / etc)
--   bank_statement_lines  — one row per transaction on the statement
--
-- Matching against the general ledger stays LOOSE: statement lines carry
-- an optional pointer to the journal_line they've been matched to. That
-- keeps the journal untouched (no schema changes on journal_lines) and
-- lets us report on unmatched lines from either side.

CREATE TABLE IF NOT EXISTS bank_statements (
    c0d              INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id        INT NOT NULL,
    bank_account_id  INT NOT NULL COMMENT 'FK to chart_of_accounts (the bank COA)',
    statement_from   DATE NOT NULL,
    statement_to     DATE NOT NULL,
    opening_balance  DECIMAL(20,2) NOT NULL DEFAULT 0,
    closing_balance  DECIMAL(20,2) NOT NULL DEFAULT 0,
    reference        VARCHAR(60)   NOT NULL DEFAULT '' COMMENT 'Statement no from bank',
    notes            TEXT,
    line_count       INT           NOT NULL DEFAULT 0,
    matched_count    INT           NOT NULL DEFAULT 0,
    status           VARCHAR(20)   NOT NULL DEFAULT 'OPEN' COMMENT 'OPEN | RECONCILED | VOID',
    reconciled_by    INT           NOT NULL DEFAULT 0,
    reconciled_on    DATETIME      NULL,
    created_by       INT           NOT NULL,
    created_on       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    edited_by        INT           NOT NULL DEFAULT 0,
    edited_on        TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted          TINYINT(1)    NOT NULL DEFAULT 0,
    KEY idx_bs_bank_period (bank_account_id, statement_from, statement_to),
    KEY idx_bs_status      (status, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bank_statement_lines (
    c0d                     INT AUTO_INCREMENT PRIMARY KEY,
    statement_id            INT NOT NULL,
    line_no                 INT NOT NULL,
    txn_date                DATE NOT NULL,
    description             VARCHAR(255) NOT NULL DEFAULT '',
    reference               VARCHAR(60)  NOT NULL DEFAULT '' COMMENT 'Bank txn ref',
    -- Signed amount: + = money IN to the bank account (deposit / receipt),
    -- - = money OUT (payment / charge). Simpler to reason about than two
    -- separate debit/credit columns.
    amount                  DECIMAL(20,2) NOT NULL,
    running_balance         DECIMAL(20,2) NULL,
    matched_journal_line_id INT NOT NULL DEFAULT 0 COMMENT 'FK to journal_lines',
    matched_by              INT NOT NULL DEFAULT 0,
    matched_on              DATETIME NULL,
    created_on              TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted                 TINYINT(1) NOT NULL DEFAULT 0,
    KEY idx_bsl_statement (statement_id, txn_date),
    KEY idx_bsl_matched   (matched_journal_line_id),
    KEY idx_bsl_amount    (statement_id, amount)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- Grants — Accountant (3), Finance Manager (2), Administrator (10),
-- Executive (1, read-only landing + view but not import/complete).
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(1,  'BankRecon', 'index',     1, NOW()),
(1,  'BankRecon', 'view',      1, NOW()),
(2,  'BankRecon', 'index',     1, NOW()),
(2,  'BankRecon', 'add',       1, NOW()),
(2,  'BankRecon', 'save',      1, NOW()),
(2,  'BankRecon', 'view',      1, NOW()),
(2,  'BankRecon', 'match',     1, NOW()),
(2,  'BankRecon', 'unmatch',   1, NOW()),
(2,  'BankRecon', 'autoMatch', 1, NOW()),
(2,  'BankRecon', 'adjust',    1, NOW()),
(2,  'BankRecon', 'complete',  1, NOW()),
(2,  'BankRecon', 'voidStmt',  1, NOW()),
(3,  'BankRecon', 'index',     1, NOW()),
(3,  'BankRecon', 'add',       1, NOW()),
(3,  'BankRecon', 'save',      1, NOW()),
(3,  'BankRecon', 'view',      1, NOW()),
(3,  'BankRecon', 'match',     1, NOW()),
(3,  'BankRecon', 'unmatch',   1, NOW()),
(3,  'BankRecon', 'autoMatch', 1, NOW()),
(3,  'BankRecon', 'adjust',    1, NOW()),
(3,  'BankRecon', 'complete',  1, NOW()),
(3,  'BankRecon', 'voidStmt',  1, NOW()),
(10, 'BankRecon', 'index',     1, NOW()),
(10, 'BankRecon', 'add',       1, NOW()),
(10, 'BankRecon', 'save',      1, NOW()),
(10, 'BankRecon', 'view',      1, NOW()),
(10, 'BankRecon', 'match',     1, NOW()),
(10, 'BankRecon', 'unmatch',   1, NOW()),
(10, 'BankRecon', 'autoMatch', 1, NOW()),
(10, 'BankRecon', 'adjust',    1, NOW()),
(10, 'BankRecon', 'complete',  1, NOW()),
(10, 'BankRecon', 'voidStmt',  1, NOW());


SELECT 'slice 28 imported' AS status,
       (SELECT COUNT(*) FROM bank_statements) AS statement_count,
       (SELECT COUNT(*) FROM bank_statement_lines) AS line_count;
