bank: import SEB CSV and match against vouchers (schema v8)

1 files changed, 81 insertions, 0 deletions

diff --git a/src/db.c b/src/db.c
index c809bea..7e23cb0 100644
--- a/src/db.c
+++ b/src/db.c
@@ -271,6 +271,39 @@ static const char SCHEMA_V1[] =
     "  key    TEXT NOT NULL,"
     "  value  TEXT NOT NULL,"
     "  PRIMARY KEY (org_id, key)"
+    ") STRICT;\n"
+
+    "CREATE TABLE bank_transactions ("
+    "  org_id      INTEGER NOT NULL REFERENCES orgs(id),"
+    "  id          INTEGER PRIMARY KEY,"
+    "  account     TEXT NOT NULL,"
+    "  booked_at   TEXT NOT NULL,"
+    "  value_date  TEXT NOT NULL,"
+    "  text        TEXT NOT NULL,"
+    "  type        TEXT NOT NULL,"
+    "  amount_ore  INTEGER NOT NULL,"
+    "  balance_ore INTEGER,"
+    "  source      TEXT NOT NULL,"
+    "  source_hash BLOB NOT NULL CHECK (length(source_hash) = 32),"
+    "  imported_at TEXT NOT NULL,"
+    "  imported_by INTEGER NOT NULL REFERENCES users(id),"
+    "  UNIQUE (org_id, id),"
+    "  UNIQUE (org_id, source_hash)"
+    ") STRICT;\n"
+    "CREATE INDEX idx_bank_tx_date ON bank_transactions(org_id, booked_at);\n"
+
+    "CREATE TABLE bank_matches ("
+    "  org_id         INTEGER NOT NULL,"
+    "  transaction_id INTEGER NOT NULL,"
+    "  voucher_id     INTEGER NOT NULL,"
+    "  matched_at     TEXT NOT NULL,"
+    "  matched_by     INTEGER NOT NULL REFERENCES users(id),"
+    "  kind           TEXT NOT NULL DEFAULT 'manual'"
+    "    CHECK (kind IN ('manual','auto')),"
+    "  PRIMARY KEY (org_id, transaction_id, voucher_id),"
+    "  FOREIGN KEY (org_id, transaction_id)"
+    "    REFERENCES bank_transactions(org_id, id),"
+    "  FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id)"
     ") STRICT;\n";
 
 static const char SCHEMA_V2[] =
@@ -458,6 +491,50 @@ static int db_upgrade_v7(sqlite3 *db, char **err)
                    err);
 }
 
+/* v8: bank reconciliation — imported statement evidence and the mutable
+   transaction/voucher links (phase 1 never books anything). */
+static int db_upgrade_v8(sqlite3 *db, char **err)
+{
+    if (db_exec(db,
+                "CREATE TABLE IF NOT EXISTS bank_transactions ("
+                " org_id INTEGER NOT NULL REFERENCES orgs(id),"
+                " id INTEGER PRIMARY KEY,"
+                " account TEXT NOT NULL,"
+                " booked_at TEXT NOT NULL,"
+                " value_date TEXT NOT NULL,"
+                " text TEXT NOT NULL,"
+                " type TEXT NOT NULL,"
+                " amount_ore INTEGER NOT NULL,"
+                " balance_ore INTEGER,"
+                " source TEXT NOT NULL,"
+                " source_hash BLOB NOT NULL CHECK (length(source_hash) = 32),"
+                " imported_at TEXT NOT NULL,"
+                " imported_by INTEGER NOT NULL REFERENCES users(id),"
+                " UNIQUE (org_id, id),"
+                " UNIQUE (org_id, source_hash)"
+                ") STRICT;"
+                "CREATE INDEX IF NOT EXISTS idx_bank_tx_date"
+                " ON bank_transactions(org_id, booked_at);",
+                err) != 0)
+        return -1;
+    return db_exec(db,
+                   "CREATE TABLE IF NOT EXISTS bank_matches ("
+                   " org_id INTEGER NOT NULL,"
+                   " transaction_id INTEGER NOT NULL,"
+                   " voucher_id INTEGER NOT NULL,"
+                   " matched_at TEXT NOT NULL,"
+                   " matched_by INTEGER NOT NULL REFERENCES users(id),"
+                   " kind TEXT NOT NULL DEFAULT 'manual'"
+                   "   CHECK (kind IN ('manual','auto')),"
+                   " PRIMARY KEY (org_id, transaction_id, voucher_id),"
+                   " FOREIGN KEY (org_id, transaction_id)"
+                   "   REFERENCES bank_transactions(org_id, id),"
+                   " FOREIGN KEY (org_id, voucher_id)"
+                   "   REFERENCES vouchers(org_id, id)"
+                   ") STRICT",
+                   err);
+}
+
 static int db_upgrade(sqlite3 *db, int from, char **err)
 {
     if (db_exec(db, "BEGIN IMMEDIATE", err) != 0)
@@ -486,6 +563,10 @@ static int db_upgrade(sqlite3 *db, int from, char **err)
         db_exec(db, "ROLLBACK", NULL);
         return -1;
     }
+    if (from < 8 && db_upgrade_v8(db, err) != 0) {
+        db_exec(db, "ROLLBACK", NULL);
+        return -1;
+    }
     char *sql = sqlite3_mprintf(
         "UPDATE meta SET value='%d' WHERE key='schema_version'",
         BOKF_SCHEMA_VERSION);