commands: split the command table by domain

1 files changed, 887 insertions, 0 deletions

diff --git a/src/cmd_bank.c b/src/cmd_bank.c
new file mode 100644
index 0000000..b1ab2e3
--- /dev/null
+++ b/src/cmd_bank.c
@@ -0,0 +1,887 @@
+#include "commands.h"
+#include "cmd_util.h"
+
+#include <stdio.h>
+#include <stdlib.h>
+#include <string.h>
+
+#include "audit.h"
+#include "db.h"
+#include "ledger.h"
+#include "util.h"
+
+/* ------------------------------------------------------------------ */
+/* bank reconciliation (phase 1: import and match, never book)         */
+/* ------------------------------------------------------------------ */
+
+struct seb_row {
+    const char *booked_at;
+    const char *value_date;
+    const char *text;
+    const char *type;
+    int64_t amount_ore;
+    int has_balance;
+    int64_t balance_ore;
+};
+
+static int csv_split(char *line, char **fields, int max)
+{
+    int nf = 0, in_quotes = 0;
+    char *dst = line;
+    fields[nf++] = dst;
+    for (char *p = line; *p; p++) {
+        if (*p == '"') {
+            if (in_quotes && p[1] == '"') {
+                *dst++ = '"';
+                p++;
+            } else {
+                in_quotes = !in_quotes;
+            }
+        } else if (*p == ';' && !in_quotes) {
+            *dst++ = '\0';
+            if (nf >= max)
+                return -1;
+            fields[nf++] = dst;
+        } else {
+            *dst++ = *p;
+        }
+    }
+    *dst = '\0';
+    return in_quotes ? -1 : nf;
+}
+
+static int seb_amount(const char *s, int64_t *out)
+{
+    while (*s == ' ')
+        s++;
+    int neg = 0;
+    if (*s == '-') {
+        neg = 1;
+        s++;
+    } else if (*s == '+') {
+        s++;
+    }
+    int64_t whole = 0, frac = 0;
+    int digits = 0, fdigits = 0, comma = 0;
+    for (; *s; s++) {
+        if (*s == ' ' || *s == '.')
+            continue;
+        if (*s == ',') {
+            if (comma)
+                return -1;
+            comma = 1;
+            continue;
+        }
+        if (*s < '0' || *s > '9')
+            return -1;
+        if (comma) {
+            if (fdigits >= 2)
+                return -1;
+            frac = frac * 10 + (*s - '0');
+            fdigits++;
+        } else {
+            if (whole > (INT64_MAX - 9) / 10)
+                return -1;
+            whole = whole * 10 + (*s - '0');
+            digits++;
+        }
+    }
+    if (digits == 0)
+        return -1;
+    while (fdigits < 2) {
+        frac *= 10;
+        fdigits++;
+    }
+    if (whole > (INT64_MAX - frac) / 100)
+        return -1;
+    int64_t v = whole * 100 + frac;
+    *out = neg ? -v : v;
+    return 0;
+}
+
+static int seb_parse(char *csv, struct seb_row **out, size_t *out_n,
+                     char *err, size_t errlen)
+{
+    static const char *header[7] = {
+        "Bokförd",      "Valutadatum", "Text", "Typ",
+        "Insättningar", "Uttag",       "Bokfört saldo",
+    };
+    struct seb_row *rows = NULL;
+    size_t n = 0, cap = 0;
+    char *cur = csv;
+    int lineno = 0;
+    while (*cur) {
+        lineno++;
+        char *nl = strchr(cur, '\n');
+        if (nl)
+            *nl = '\0';
+        size_t llen = strlen(cur);
+        if (llen && cur[llen - 1] == '\r')
+            cur[--llen] = '\0';
+        char *fields[8];
+        int nf = csv_split(cur, fields, 8);
+        if (nf < 0) {
+            snprintf(err, errlen, "malformed SEB CSV on line %d", lineno);
+            goto bad;
+        }
+        if (lineno == 1) {
+            int ok = nf == 7;
+            for (int i = 0; ok && i < 7; i++)
+                if (strcmp(fields[i], header[i]) != 0)
+                    ok = 0;
+            if (!ok) {
+                snprintf(err, errlen, "not a SEB CSV export: header mismatch");
+                goto bad;
+            }
+            if (!nl)
+                break;
+            cur = nl + 1;
+            continue;
+        }
+        if (llen == 0) {
+            if (!nl)
+                break;
+            cur = nl + 1;
+            continue;
+        }
+        if (nf != 7) {
+            snprintf(err, errlen, "malformed SEB CSV on line %d", lineno);
+            goto bad;
+        }
+        struct seb_row row;
+        row.booked_at = fields[0];
+        row.value_date = fields[1];
+        row.text = fields[2];
+        row.type = fields[3];
+        row.has_balance = fields[6][0] != '\0';
+        row.balance_ore = 0;
+        int has_dep = fields[4][0] != '\0';
+        int has_wd = fields[5][0] != '\0';
+        if (!util_date_valid(fields[0]) || !util_date_valid(fields[1]) ||
+            has_dep + has_wd != 1) {
+            snprintf(err, errlen, "malformed SEB CSV on line %d", lineno);
+            goto bad;
+        }
+        if (has_dep) {
+            if (seb_amount(fields[4], &row.amount_ore) != 0) {
+                snprintf(err, errlen, "malformed SEB CSV on line %d", lineno);
+                goto bad;
+            }
+        } else {
+            int64_t w = 0;
+            if (seb_amount(fields[5], &w) != 0) {
+                snprintf(err, errlen, "malformed SEB CSV on line %d", lineno);
+                goto bad;
+            }
+            row.amount_ore = w > 0 ? -w : w;
+        }
+        if (row.has_balance && seb_amount(fields[6], &row.balance_ore) != 0) {
+            snprintf(err, errlen, "malformed SEB CSV on line %d", lineno);
+            goto bad;
+        }
+        if (n == cap) {
+            cap = cap ? cap * 2 : 64;
+            rows = xrealloc(rows, cap * sizeof *rows);
+        }
+        rows[n++] = row;
+        if (!nl)
+            break;
+        cur = nl + 1;
+    }
+    *out = rows;
+    *out_n = n;
+    return 0;
+bad:
+    free(rows);
+    return -1;
+}
+
+static void bank_field(struct buf *b, const char *s, int wide)
+{
+    size_t n = strlen(s);
+    if (wide)
+        buf_append_u32be(b, (uint32_t)n);
+    else
+        buf_append_u16be(b, (uint16_t)n);
+    buf_append(b, s, n);
+}
+
+static void bank_row_hash(const char *account, const struct seb_row *row,
+                          unsigned char out[32])
+{
+    static const char tag[] = "bokf-v1-bank-tx";
+    struct buf b;
+    buf_init(&b);
+    buf_append(&b, tag, sizeof tag);
+    bank_field(&b, account, 0);
+    bank_field(&b, row->booked_at, 0);
+    bank_field(&b, row->value_date, 0);
+    bank_field(&b, row->text, 1);
+    bank_field(&b, row->type, 1);
+    buf_append_u64be(&b, (uint64_t)row->amount_ore);
+    unsigned char flag = row->has_balance ? 1 : 0;
+    buf_append(&b, &flag, 1);
+    if (row->has_balance)
+        buf_append_u64be(&b, (uint64_t)row->balance_ore);
+    util_sha256(b.p, b.len, out);
+    buf_free(&b);
+}
+
+static int bank_hash_cmp(const void *a, const void *b)
+{
+    return memcmp(a, b, 32);
+}
+
+static int bank_account_exists(struct req *r, const char *account)
+{
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            r->db, "SELECT 1 FROM accounts WHERE org_id=?1 AND number=?2", -1,
+            &st, NULL) != SQLITE_OK)
+        return -1;
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_text(st, 2, account, -1, SQLITE_TRANSIENT);
+    int found = sqlite3_step(st) == SQLITE_ROW;
+    sqlite3_finalize(st);
+    return found;
+}
+
+static yyjson_mut_val *h_bank_import(struct req *r)
+{
+    const char *format = arg_str(r->args, "format");
+    const char *b64 = arg_str(r->args, "content_base64");
+    const char *path = arg_str(r->args, "path");
+    const char *account = arg_str(r->args, "account");
+    if (!format || strcmp(format, "seb") != 0)
+        return fail(r, "INVALID_ARGS", "format must be \"seb\"");
+    unsigned char *content = NULL;
+    size_t len = 0;
+    if (b64) {
+        if (util_b64_decode(b64, strlen(b64), &content, &len) != 0)
+            return fail(r, "INVALID_ARGS",
+                        "content_base64 is not valid base64");
+    } else if (path) {
+        content = read_file(path, &len);
+        if (!content)
+            return fail(r, "NOT_FOUND", "could not read file");
+    } else {
+        return fail(r, "INVALID_ARGS", "content_base64 or path is required");
+    }
+    if (len > 64u * 1024u * 1024u) {
+        free(content);
+        return fail(r, "TOO_LARGE", "bank statement is too large");
+    }
+    if (memchr(content, '\0', len) != NULL) {
+        free(content);
+        return fail(r, "INVALID_ARGS", "not a SEB CSV export: header mismatch");
+    }
+    char *csv = xmalloc(len + 1);
+    memcpy(csv, content, len);
+    csv[len] = '\0';
+    free(content);
+    char *start = csv;
+    if (len >= 3 && memcmp(start, "\xEF\xBB\xBF", 3) == 0)
+        start += 3;
+    char perr[128];
+    struct seb_row *rows = NULL;
+    size_t nrows = 0;
+    if (seb_parse(start, &rows, &nrows, perr, sizeof perr) != 0) {
+        free(csv);
+        return fail(r, "INVALID_ARGS", perr);
+    }
+
+    char *setting = account && *account
+                        ? NULL
+                        : db_setting(r->db, r->org_id, "bank_account");
+    const char *acct = account && *account
+                           ? account
+                           : (setting ? setting : "1930");
+    int found = bank_account_exists(r, acct);
+    if (found < 0) {
+        free(setting);
+        free(rows);
+        free(csv);
+        return fail(r, "INTERNAL", "database error");
+    }
+    if (!found) {
+        yyjson_mut_val *res =
+            failf(r, "ACCOUNT_NOT_FOUND", "account %s not found", acct);
+        free(setting);
+        free(rows);
+        free(csv);
+        return res;
+    }
+
+    unsigned char(*hashes)[32] = xmalloc((nrows ? nrows : 1) * 32);
+    for (size_t i = 0; i < nrows; i++)
+        bank_row_hash(acct, &rows[i], hashes[i]);
+
+    int64_t imported = 0;
+    if (r->dry_run) {
+        unsigned char(*sorted)[32] = xmalloc((nrows ? nrows : 1) * 32);
+        memcpy(sorted, hashes, nrows * 32);
+        qsort(sorted, nrows, 32, bank_hash_cmp);
+        for (size_t i = 0; i < nrows;) {
+            size_t j = i + 1;
+            while (j < nrows && memcmp(sorted[i], sorted[j], 32) == 0)
+                j++;
+            sqlite3_stmt *q = NULL;
+            int exists = 0;
+            if (sqlite3_prepare_v2(
+                    r->db,
+                    "SELECT 1 FROM bank_transactions WHERE org_id=?1"
+                    " AND source_hash=?2",
+                    -1, &q, NULL) != SQLITE_OK) {
+                free(sorted);
+                free(hashes);
+                free(setting);
+                free(rows);
+                free(csv);
+                return fail(r, "INTERNAL", "database error");
+            }
+            sqlite3_bind_int64(q, 1, r->org_id);
+            sqlite3_bind_blob(q, 2, sorted[i], 32, SQLITE_TRANSIENT);
+            exists = sqlite3_step(q) == SQLITE_ROW;
+            sqlite3_finalize(q);
+            if (!exists)
+                imported++;
+            i = j;
+        }
+        free(sorted);
+    } else {
+        char ts[32];
+        util_iso8601(util_now(), ts, sizeof ts);
+        int failed = db_exec(r->db, "BEGIN IMMEDIATE", NULL) != 0;
+        for (size_t i = 0; i < nrows && !failed; i++) {
+            sqlite3_stmt *ins = NULL;
+            if (sqlite3_prepare_v2(
+                    r->db,
+                    "INSERT INTO bank_transactions(org_id,account,booked_at,"
+                    "value_date,text,type,amount_ore,balance_ore,source,"
+                    "source_hash,imported_at,imported_by)"
+                    " VALUES(?1,?2,?3,?4,?5,?6,?7,?8,'seb-csv',?9,?10,?11)"
+                    " ON CONFLICT(org_id,source_hash) DO NOTHING",
+                    -1, &ins, NULL) != SQLITE_OK) {
+                failed = 1;
+                break;
+            }
+            sqlite3_bind_int64(ins, 1, r->org_id);
+            sqlite3_bind_text(ins, 2, acct, -1, SQLITE_TRANSIENT);
+            sqlite3_bind_text(ins, 3, rows[i].booked_at, -1, SQLITE_TRANSIENT);
+            sqlite3_bind_text(ins, 4, rows[i].value_date, -1, SQLITE_TRANSIENT);
+            sqlite3_bind_text(ins, 5, rows[i].text, -1, SQLITE_TRANSIENT);
+            sqlite3_bind_text(ins, 6, rows[i].type, -1, SQLITE_TRANSIENT);
+            sqlite3_bind_int64(ins, 7, rows[i].amount_ore);
+            if (rows[i].has_balance)
+                sqlite3_bind_int64(ins, 8, rows[i].balance_ore);
+            else
+                sqlite3_bind_null(ins, 8);
+            sqlite3_bind_blob(ins, 9, hashes[i], 32, SQLITE_TRANSIENT);
+            sqlite3_bind_text(ins, 10, ts, -1, SQLITE_TRANSIENT);
+            sqlite3_bind_int64(ins, 11, r->sess->user_id);
+            int rc = sqlite3_step(ins);
+            sqlite3_finalize(ins);
+            if (rc != SQLITE_DONE) {
+                failed = 1;
+                break;
+            }
+            if (sqlite3_changes(r->db) > 0)
+                imported++;
+        }
+        if (failed) {
+            db_exec(r->db, "ROLLBACK", NULL);
+            free(hashes);
+            free(setting);
+            free(rows);
+            free(csv);
+            return fail(r, "INTERNAL", "database error");
+        }
+        if (db_exec(r->db, "COMMIT", NULL) != 0) {
+            db_exec(r->db, "ROLLBACK", NULL);
+            free(hashes);
+            free(setting);
+            free(rows);
+            free(csv);
+            return fail(r, "INTERNAL", "database error");
+        }
+    }
+    int64_t duplicates = (int64_t)nrows - imported;
+    const char *first = NULL, *last = NULL;
+    for (size_t i = 0; i < nrows; i++) {
+        if (!first || strcmp(rows[i].booked_at, first) < 0)
+            first = rows[i].booked_at;
+        if (!last || strcmp(rows[i].booked_at, last) > 0)
+            last = rows[i].booked_at;
+    }
+    if (!r->dry_run) {
+        char *reqjson = audit_args_json(r->args);
+        audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+                     "bank.import", reqjson, "OK", NULL);
+        free(reqjson);
+    }
+    yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "format", "seb");
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "account", acct);
+    yyjson_mut_obj_add_int(r->rdoc, o, "total", (int64_t)nrows);
+    yyjson_mut_obj_add_int(r->rdoc, o, "imported", imported);
+    yyjson_mut_obj_add_int(r->rdoc, o, "duplicates", duplicates);
+    if (first)
+        yyjson_mut_obj_add_strcpy(r->rdoc, o, "first_date", first);
+    else
+        yyjson_mut_obj_add_null(r->rdoc, o, "first_date");
+    if (last)
+        yyjson_mut_obj_add_strcpy(r->rdoc, o, "last_date", last);
+    else
+        yyjson_mut_obj_add_null(r->rdoc, o, "last_date");
+    if (r->dry_run)
+        yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true);
+    free(hashes);
+    free(setting);
+    free(rows);
+    free(csv);
+    return o;
+}
+
+static yyjson_mut_val *h_bank_list(struct req *r)
+{
+    const char *status = arg_str(r->args, "status");
+    const char *from = arg_str(r->args, "from");
+    const char *to = arg_str(r->args, "to");
+    const char *account = arg_str(r->args, "account");
+    int64_t limit = 200;
+    arg_int(r->args, "limit", &limit);
+    if (limit < 1)
+        limit = 200;
+    if (limit > 1000)
+        limit = 1000;
+    int status_code = 0;
+    if (status && strcmp(status, "unmatched") == 0)
+        status_code = 1;
+    else if (status && strcmp(status, "matched") == 0)
+        status_code = 2;
+
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT t.id,t.account,t.booked_at,t.value_date,t.text,t.type,"
+            "t.amount_ore,t.balance_ore FROM bank_transactions t"
+            " WHERE t.org_id=?1"
+            " AND (?2 IS NULL OR t.account=?2)"
+            " AND (?3 IS NULL OR t.booked_at>=?3)"
+            " AND (?4 IS NULL OR t.booked_at<=?4)"
+            " AND (?5=0"
+            "   OR (?5=1 AND NOT EXISTS (SELECT 1 FROM bank_matches m"
+            "        WHERE m.org_id=t.org_id AND m.transaction_id=t.id))"
+            "   OR (?5=2 AND EXISTS (SELECT 1 FROM bank_matches m"
+            "        WHERE m.org_id=t.org_id AND m.transaction_id=t.id)))"
+            " ORDER BY t.booked_at,t.id LIMIT ?6",
+            -1, &st, NULL) != SQLITE_OK)
+        return fail(r, "INTERNAL", "database error");
+    sqlite3_bind_int64(st, 1, r->org_id);
+    if (account)
+        sqlite3_bind_text(st, 2, account, -1, SQLITE_TRANSIENT);
+    else
+        sqlite3_bind_null(st, 2);
+    if (from)
+        sqlite3_bind_text(st, 3, from, -1, SQLITE_TRANSIENT);
+    else
+        sqlite3_bind_null(st, 3);
+    if (to)
+        sqlite3_bind_text(st, 4, to, -1, SQLITE_TRANSIENT);
+    else
+        sqlite3_bind_null(st, 4);
+    sqlite3_bind_int64(st, 5, status_code);
+    sqlite3_bind_int64(st, 6, limit);
+
+    yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
+    while (sqlite3_step(st) == SQLITE_ROW) {
+        int64_t tx_id = sqlite3_column_int64(st, 0);
+        char tx_account[16];
+        snprintf(tx_account, sizeof tx_account, "%s",
+                 sq(sqlite3_column_text(st, 1)));
+        const char *booked_at = sq(sqlite3_column_text(st, 2));
+        yyjson_mut_val *item = yyjson_mut_arr_add_obj(r->rdoc, items);
+        yyjson_mut_obj_add_int(r->rdoc, item, "id", tx_id);
+        yyjson_mut_obj_add_strcpy(r->rdoc, item, "account", tx_account);
+        yyjson_mut_obj_add_strcpy(r->rdoc, item, "booked_at", booked_at);
+        yyjson_mut_obj_add_strcpy(r->rdoc, item, "value_date",
+                                  sq(sqlite3_column_text(st, 3)));
+        yyjson_mut_obj_add_strcpy(r->rdoc, item, "text",
+                                  sq(sqlite3_column_text(st, 4)));
+        yyjson_mut_obj_add_strcpy(r->rdoc, item, "type",
+                                  sq(sqlite3_column_text(st, 5)));
+        yyjson_mut_obj_add_int(r->rdoc, item, "amount_ore",
+                               sqlite3_column_int64(st, 6));
+        if (sqlite3_column_type(st, 7) == SQLITE_NULL)
+            yyjson_mut_obj_add_null(r->rdoc, item, "balance_ore");
+        else
+            yyjson_mut_obj_add_int(r->rdoc, item, "balance_ore",
+                                   sqlite3_column_int64(st, 7));
+
+        yyjson_mut_val *matches = yyjson_mut_arr(r->rdoc);
+        sqlite3_stmt *ms = NULL;
+        if (sqlite3_prepare_v2(
+                r->db,
+                "SELECT v.id,v.series,v.number,v.date,"
+                "COALESCE(SUM(r.debit_ore-r.credit_ore),0)"
+                " FROM bank_matches m"
+                " JOIN vouchers v ON v.org_id=m.org_id AND v.id=m.voucher_id"
+                " LEFT JOIN accounts a ON a.org_id=v.org_id AND a.number=?2"
+                " LEFT JOIN voucher_rows r ON r.org_id=v.org_id"
+                "   AND r.voucher_id=v.id AND r.account_id=a.id"
+                " WHERE m.org_id=?1 AND m.transaction_id=?3"
+                " GROUP BY v.id,v.series,v.number,v.date ORDER BY v.id",
+                -1, &ms, NULL) != SQLITE_OK) {
+            sqlite3_finalize(st);
+            return fail(r, "INTERNAL", "database error");
+        }
+        sqlite3_bind_int64(ms, 1, r->org_id);
+        sqlite3_bind_text(ms, 2, tx_account, -1, SQLITE_TRANSIENT);
+        sqlite3_bind_int64(ms, 3, tx_id);
+        while (sqlite3_step(ms) == SQLITE_ROW) {
+            yyjson_mut_val *m = yyjson_mut_arr_add_obj(r->rdoc, matches);
+            yyjson_mut_obj_add_int(r->rdoc, m, "voucher_id",
+                                   sqlite3_column_int64(ms, 0));
+            yyjson_mut_obj_add_strcpy(r->rdoc, m, "series",
+                                      sq(sqlite3_column_text(ms, 1)));
+            yyjson_mut_obj_add_int(r->rdoc, m, "number",
+                                   sqlite3_column_int64(ms, 2));
+            yyjson_mut_obj_add_strcpy(r->rdoc, m, "date",
+                                      sq(sqlite3_column_text(ms, 3)));
+            yyjson_mut_obj_add_int(r->rdoc, m, "bank_amount_ore",
+                                   sqlite3_column_int64(ms, 4));
+        }
+        sqlite3_finalize(ms);
+        yyjson_mut_obj_add_val(r->rdoc, item, "matches", matches);
+
+        yyjson_mut_val *suggestions = yyjson_mut_arr(r->rdoc);
+        if (yyjson_mut_arr_size(matches) == 0) {
+            sqlite3_stmt *ss = NULL;
+            if (sqlite3_prepare_v2(
+                    r->db,
+                    "SELECT v.id,v.series,v.number,v.date,"
+                    "SUM(r.debit_ore-r.credit_ore)"
+                    " FROM vouchers v"
+                    " JOIN voucher_rows r ON r.org_id=v.org_id"
+                    "   AND r.voucher_id=v.id"
+                    " JOIN accounts a ON a.org_id=r.org_id"
+                    "   AND a.id=r.account_id"
+                    " WHERE v.org_id=?1 AND a.number=?2"
+                    " AND ABS(julianday(v.date)-julianday(?3))<=5"
+                    " AND NOT EXISTS (SELECT 1 FROM bank_matches m"
+                    "   WHERE m.org_id=v.org_id AND m.voucher_id=v.id)"
+                    " GROUP BY v.id,v.series,v.number,v.date"
+                    " HAVING SUM(r.debit_ore-r.credit_ore)=?4"
+                    " ORDER BY ABS(julianday(v.date)-julianday(?3)),v.date,"
+                    "v.id LIMIT 3",
+                    -1, &ss, NULL) != SQLITE_OK) {
+                sqlite3_finalize(st);
+                return fail(r, "INTERNAL", "database error");
+            }
+            sqlite3_bind_int64(ss, 1, r->org_id);
+            sqlite3_bind_text(ss, 2, tx_account, -1, SQLITE_TRANSIENT);
+            sqlite3_bind_text(ss, 3, booked_at, -1, SQLITE_TRANSIENT);
+            sqlite3_bind_int64(ss, 4, sqlite3_column_int64(st, 6));
+            while (sqlite3_step(ss) == SQLITE_ROW) {
+                yyjson_mut_val *s =
+                    yyjson_mut_arr_add_obj(r->rdoc, suggestions);
+                yyjson_mut_obj_add_int(r->rdoc, s, "voucher_id",
+                                       sqlite3_column_int64(ss, 0));
+                yyjson_mut_obj_add_strcpy(r->rdoc, s, "series",
+                                          sq(sqlite3_column_text(ss, 1)));
+                yyjson_mut_obj_add_int(r->rdoc, s, "number",
+                                       sqlite3_column_int64(ss, 2));
+                yyjson_mut_obj_add_strcpy(r->rdoc, s, "date",
+                                          sq(sqlite3_column_text(ss, 3)));
+                yyjson_mut_obj_add_int(r->rdoc, s, "amount_ore",
+                                       sqlite3_column_int64(ss, 4));
+            }
+            sqlite3_finalize(ss);
+        }
+        yyjson_mut_obj_add_val(r->rdoc, item, "suggestions", suggestions);
+    }
+    sqlite3_finalize(st);
+
+    sqlite3_stmt *sum = NULL;
+    int64_t unmatched = 0, matched = 0, unmatched_amount = 0;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT COUNT(*),"
+            "COALESCE(SUM(EXISTS (SELECT 1 FROM bank_matches m"
+            "  WHERE m.org_id=t.org_id AND m.transaction_id=t.id)),0),"
+            "COALESCE(SUM(CASE WHEN EXISTS (SELECT 1 FROM bank_matches m"
+            "  WHERE m.org_id=t.org_id AND m.transaction_id=t.id)"
+            "  THEN 0 ELSE t.amount_ore END),0)"
+            " FROM bank_transactions t"
+            " WHERE t.org_id=?1 AND (?2 IS NULL OR t.account=?2)",
+            -1, &sum, NULL) == SQLITE_OK) {
+        sqlite3_bind_int64(sum, 1, r->org_id);
+        if (account)
+            sqlite3_bind_text(sum, 2, account, -1, SQLITE_TRANSIENT);
+        else
+            sqlite3_bind_null(sum, 2);
+        if (sqlite3_step(sum) == SQLITE_ROW) {
+            int64_t total = sqlite3_column_int64(sum, 0);
+            matched = sqlite3_column_int64(sum, 1);
+            unmatched = total - matched;
+            unmatched_amount = sqlite3_column_int64(sum, 2);
+        }
+        sqlite3_finalize(sum);
+    }
+    yyjson_mut_val *res = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_val(r->rdoc, res, "items", items);
+    yyjson_mut_val *s = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_int(r->rdoc, s, "unmatched", unmatched);
+    yyjson_mut_obj_add_int(r->rdoc, s, "matched", matched);
+    yyjson_mut_obj_add_int(r->rdoc, s, "unmatched_amount_ore",
+                           unmatched_amount);
+    yyjson_mut_obj_add_val(r->rdoc, res, "summary", s);
+    return res;
+}
+
+static int bank_legs_sum(sqlite3 *db, int64_t org_id, int64_t tx_id,
+                         const char *account, int64_t *out)
+{
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            db,
+            "SELECT COALESCE(SUM(r.debit_ore-r.credit_ore),0)"
+            " FROM bank_matches m"
+            " JOIN voucher_rows r ON r.org_id=m.org_id"
+            "   AND r.voucher_id=m.voucher_id"
+            " JOIN accounts a ON a.org_id=r.org_id AND a.id=r.account_id"
+            " WHERE m.org_id=?1 AND m.transaction_id=?2 AND a.number=?3",
+            -1, &st, NULL) != SQLITE_OK)
+        return -1;
+    sqlite3_bind_int64(st, 1, org_id);
+    sqlite3_bind_int64(st, 2, tx_id);
+    sqlite3_bind_text(st, 3, account, -1, SQLITE_TRANSIENT);
+    int rc = sqlite3_step(st);
+    if (rc == SQLITE_ROW)
+        *out = sqlite3_column_int64(st, 0);
+    sqlite3_finalize(st);
+    return rc == SQLITE_ROW ? 0 : -1;
+}
+
+static yyjson_mut_val *h_bank_match(struct req *r)
+{
+    int64_t tx_id = 0, voucher_id = 0;
+    if (!arg_int(r->args, "transaction_id", &tx_id) || tx_id <= 0 ||
+        !arg_int(r->args, "voucher_id", &voucher_id) || voucher_id <= 0)
+        return fail(r, "INVALID_ARGS",
+                    "transaction_id and voucher_id are required");
+    sqlite3_stmt *st = NULL;
+    char account[16];
+    int64_t amount = 0;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT account,amount_ore FROM bank_transactions"
+            " WHERE org_id=?1 AND id=?2",
+            -1, &st, NULL) != SQLITE_OK)
+        return fail(r, "INTERNAL", "database error");
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_int64(st, 2, tx_id);
+    if (sqlite3_step(st) != SQLITE_ROW) {
+        sqlite3_finalize(st);
+        return fail(r, "NOT_FOUND", "bank transaction not found");
+    }
+    snprintf(account, sizeof account, "%s", sq(sqlite3_column_text(st, 0)));
+    amount = sqlite3_column_int64(st, 1);
+    sqlite3_finalize(st);
+
+    int found = 0;
+    if (sqlite3_prepare_v2(
+            r->db, "SELECT 1 FROM vouchers WHERE org_id=?1 AND id=?2", -1,
+            &st, NULL) != SQLITE_OK)
+        return fail(r, "INTERNAL", "database error");
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_int64(st, 2, voucher_id);
+    found = sqlite3_step(st) == SQLITE_ROW;
+    sqlite3_finalize(st);
+    if (!found)
+        return fail(r, "NOT_FOUND", "voucher not found");
+
+    int touches = 0;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT 1 FROM voucher_rows r"
+            " JOIN accounts a ON a.org_id=r.org_id AND a.id=r.account_id"
+            " WHERE r.org_id=?1 AND r.voucher_id=?2 AND a.number=?3",
+            -1, &st, NULL) != SQLITE_OK)
+        return fail(r, "INTERNAL", "database error");
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_int64(st, 2, voucher_id);
+    sqlite3_bind_text(st, 3, account, -1, SQLITE_TRANSIENT);
+    touches = sqlite3_step(st) == SQLITE_ROW;
+    sqlite3_finalize(st);
+    if (!touches)
+        return failf(r, "INVALID_ARGS",
+                     "voucher does not post to account %s", account);
+
+    int linked = 0;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT 1 FROM bank_matches WHERE org_id=?1 AND transaction_id=?2"
+            " AND voucher_id=?3",
+            -1, &st, NULL) != SQLITE_OK)
+        return fail(r, "INTERNAL", "database error");
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_int64(st, 2, tx_id);
+    sqlite3_bind_int64(st, 3, voucher_id);
+    linked = sqlite3_step(st) == SQLITE_ROW;
+    sqlite3_finalize(st);
+    if (linked)
+        return fail(r, "CONFLICT",
+                    "transaction is already matched to this voucher");
+
+    int64_t legs = 0;
+    if (bank_legs_sum(r->db, r->org_id, tx_id, account, &legs) != 0)
+        return fail(r, "INTERNAL", "database error");
+    if (r->dry_run) {
+        sqlite3_stmt *vs = NULL;
+        int64_t voucher_leg = 0;
+        if (sqlite3_prepare_v2(
+                r->db,
+                "SELECT COALESCE(SUM(r.debit_ore-r.credit_ore),0)"
+                " FROM voucher_rows r"
+                " JOIN accounts a ON a.org_id=r.org_id AND a.id=r.account_id"
+                " WHERE r.org_id=?1 AND r.voucher_id=?2 AND a.number=?3",
+                -1, &vs, NULL) != SQLITE_OK)
+            return fail(r, "INTERNAL", "database error");
+        sqlite3_bind_int64(vs, 1, r->org_id);
+        sqlite3_bind_int64(vs, 2, voucher_id);
+        sqlite3_bind_text(vs, 3, account, -1, SQLITE_TRANSIENT);
+        if (sqlite3_step(vs) == SQLITE_ROW)
+            voucher_leg = sqlite3_column_int64(vs, 0);
+        sqlite3_finalize(vs);
+        yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+        yyjson_mut_obj_add_int(r->rdoc, o, "transaction_id", tx_id);
+        yyjson_mut_obj_add_int(r->rdoc, o, "voucher_id", voucher_id);
+        yyjson_mut_obj_add_int(r->rdoc, o, "difference_ore",
+                               amount - (legs + voucher_leg));
+        yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true);
+        return o;
+    }
+
+    char ts[32];
+    util_iso8601(util_now(), ts, sizeof ts);
+    if (sqlite3_prepare_v2(
+            r->db,
+            "INSERT INTO bank_matches(org_id,transaction_id,voucher_id,"
+            "matched_at,matched_by,kind) VALUES(?1,?2,?3,?4,?5,'manual')",
+            -1, &st, NULL) != SQLITE_OK)
+        return fail(r, "INTERNAL", "database error");
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_int64(st, 2, tx_id);
+    sqlite3_bind_int64(st, 3, voucher_id);
+    sqlite3_bind_text(st, 4, ts, -1, SQLITE_TRANSIENT);
+    sqlite3_bind_int64(st, 5, r->sess->user_id);
+    int rc = sqlite3_step(st);
+    sqlite3_finalize(st);
+    if (rc != SQLITE_DONE)
+        return fail(r, "INTERNAL", "database error");
+
+    char *reqjson = audit_args_json(r->args);
+    audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+                 "bank.match", reqjson, "OK", NULL);
+    free(reqjson);
+
+    if (bank_legs_sum(r->db, r->org_id, tx_id, account, &legs) != 0)
+        return fail(r, "INTERNAL", "database error");
+    yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_int(r->rdoc, o, "transaction_id", tx_id);
+    yyjson_mut_obj_add_int(r->rdoc, o, "voucher_id", voucher_id);
+    yyjson_mut_obj_add_int(r->rdoc, o, "difference_ore", amount - legs);
+    return o;
+}
+
+static yyjson_mut_val *h_bank_unmatch(struct req *r)
+{
+    int64_t tx_id = 0, voucher_id = 0;
+    if (!arg_int(r->args, "transaction_id", &tx_id) || tx_id <= 0 ||
+        !arg_int(r->args, "voucher_id", &voucher_id) || voucher_id <= 0)
+        return fail(r, "INVALID_ARGS",
+                    "transaction_id and voucher_id are required");
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT 1 FROM bank_matches WHERE org_id=?1 AND transaction_id=?2"
+            " AND voucher_id=?3",
+            -1, &st, NULL) != SQLITE_OK)
+        return fail(r, "INTERNAL", "database error");
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_int64(st, 2, tx_id);
+    sqlite3_bind_int64(st, 3, voucher_id);
+    int found = sqlite3_step(st) == SQLITE_ROW;
+    sqlite3_finalize(st);
+    if (!found)
+        return fail(r, "NOT_FOUND", "match not found");
+    if (!r->dry_run) {
+        if (sqlite3_prepare_v2(
+                r->db,
+                "DELETE FROM bank_matches WHERE org_id=?1"
+                " AND transaction_id=?2 AND voucher_id=?3",
+                -1, &st, NULL) != SQLITE_OK)
+            return fail(r, "INTERNAL", "database error");
+        sqlite3_bind_int64(st, 1, r->org_id);
+        sqlite3_bind_int64(st, 2, tx_id);
+        sqlite3_bind_int64(st, 3, voucher_id);
+        int rc = sqlite3_step(st);
+        sqlite3_finalize(st);
+        if (rc != SQLITE_DONE)
+            return fail(r, "INTERNAL", "database error");
+        if (sqlite3_changes(r->db) == 0)
+            return fail(r, "NOT_FOUND", "match not found");
+        char *reqjson = audit_args_json(r->args);
+        audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+                     "bank.unmatch", reqjson, "OK", NULL);
+        free(reqjson);
+    }
+    yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_int(r->rdoc, o, "transaction_id", tx_id);
+    yyjson_mut_obj_add_int(r->rdoc, o, "voucher_id", voucher_id);
+    yyjson_mut_obj_add_bool(r->rdoc, o, "unmatched", true);
+    if (r->dry_run)
+        yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true);
+    return o;
+}
+
+
+static const struct cmd_arg args_bank_import[] = {
+    { "format", ARG_ENUM, 1, NULL, "seb", "Statement format" },
+    { "content_base64", ARG_STR, 0, NULL, NULL,
+      "Statement content, base64; alternative to path" },
+    { "path", ARG_STR, 0, NULL, NULL,
+      "Server-side path; alternative to content_base64" },
+    { "account", ARG_STR, 0, NULL, NULL,
+      "Bank account number; defaults to settings.bank_account" },
+};
+
+static const struct cmd_arg args_bank_list[] = {
+    { "status", ARG_ENUM, 0, "all", "all,unmatched,matched",
+      "Match status filter" },
+    { "from", ARG_DATE, 0, NULL, NULL, "Earliest booked date" },
+    { "to", ARG_DATE, 0, NULL, NULL, "Latest booked date" },
+    { "account", ARG_STR, 0, NULL, NULL, "Bank account number filter" },
+    { "limit", ARG_INT, 0, "200", NULL, "Page size, 1-1000" },
+};
+
+static const struct cmd_arg args_bank_match[] = {
+    { "transaction_id", ARG_INT, 1, NULL, NULL, "Bank transaction id" },
+    { "voucher_id", ARG_INT, 1, NULL, NULL, "Voucher id" },
+};
+
+const struct command g_cmd_bank[] = {
+    { "bank.import", "Import a bank statement (SEB CSV)", PERM_WRITE, 1, 1, 1,
+      h_bank_import, CMD_ARGS(args_bank_import) },
+    { "bank.list", "List bank transactions, matches and suggestions",
+      PERM_READ, 1, 0, 0, h_bank_list, CMD_ARGS(args_bank_list) },
+    { "bank.match", "Match a bank transaction to a voucher", PERM_WRITE, 1, 1,
+      1, h_bank_match, CMD_ARGS(args_bank_match) },
+    { "bank.unmatch", "Remove a transaction/voucher match", PERM_WRITE, 1, 1,
+      1, h_bank_unmatch, CMD_ARGS(args_bank_match) },
+};
+
+const struct cmd_table g_cmd_table_bank = {
+    g_cmd_bank, sizeof g_cmd_bank / sizeof g_cmd_bank[0]
+};