path: root/src/cmd_bank.c

#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 db_error(r);
    }
    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 db_error(r);
            }
            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 db_error(r);
        }
        if (db_exec(r->db, "COMMIT", NULL) != 0) {
            db_exec(r->db, "ROLLBACK", NULL);
            free(hashes);
            free(setting);
            free(rows);
            free(csv);
            return db_error(r);
        }
    }
    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 db_error(r);
    sqlite3_bind_int64(st, 1, r->org_id);
    bind_text_or_null(st, 2, account);
    bind_text_or_null(st, 3, from);
    bind_text_or_null(st, 4, to);
    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 db_error(r);
        }
        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 db_error(r);
            }
            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);
        bind_text_or_null(sum, 2, account);
        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 db_error(r);
    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 db_error(r);
    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 db_error(r);
    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 db_error(r);
    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 db_error(r);
    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 db_error(r);
        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 db_error(r);
    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 db_error(r);

    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 db_error(r);
    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 db_error(r);
    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 db_error(r);
        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 db_error(r);
        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]
};