feat: Konto-Saldo-Anker mit ankerbasierter Saldenrechnung

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

finance/app/models/views.py

@@ -1,32 +1,45 @@
 from sqlalchemy import text
 from sqlalchemy.engine import Engine
 
-# Per-account starting balance: the opening_balance of each account's earliest
-# confirmed statement (if any). Used to seed the cumulative sums below so the
-# Grafana balance charts show the real account balance, not just the running
-# sum of imported transactions (which would start at zero and ignore the
-# balance that existed before the first imported statement).
-_ACCOUNT_BASE = """
-    SELECT DISTINCT ON (account_id) account_id, opening_balance
-    FROM statements
-    WHERE status = 'confirmed' AND opening_balance IS NOT NULL
-    ORDER BY account_id, period_start NULLS LAST, id
+# Per-account offset for the Konto-Saldo-Anker (Ausbaustufe 3 Task 1): the
+# same math as app.services.balances.account_balance, expressed as a single
+# constant "base" per account so it can be added to a plain (anchor-agnostic)
+# running sum of transaction amounts:
+#
+#   balance(day) = anchor_balance + Sum(tx: anchor_date < booking_date <= day)
+#                                  - Sum(tx: day < booking_date <= anchor_date)
+#                = anchor_balance - cumsum(<=anchor_date) + cumsum(<=day)
+#                = base + cumsum(<=day)
+#
+# ... which holds for `day` both before and after anchor_date, since the
+# subtracted/added transaction windows collapse into the same telescoping
+# sum. Accounts without an anchor get base=0 (via COALESCE below), matching
+# account_balance's "Ohne Anker: Summe ab 0" fallback.
+_ACCOUNT_OFFSET = """
+    SELECT a.id AS account_id,
+           a.anchor_balance - COALESCE((
+               SELECT SUM(t2.amount) FROM transactions t2
+               WHERE t2.account_id = a.id AND t2.status = 'confirmed'
+                 AND t2.booking_date <= a.anchor_date
+           ), 0) AS base
+    FROM accounts a
+    WHERE a.anchor_date IS NOT NULL AND a.anchor_balance IS NOT NULL
 """
 
 VIEWS: dict[str, str] = {
     "v_balance_history": f"""
         SELECT t.booking_date AS day, a.name AS account,
-               COALESCE(b.opening_balance, 0)
+               COALESCE(b.base, 0)
                  + SUM(SUM(t.amount)) OVER (PARTITION BY a.id
                                             ORDER BY t.booking_date) AS balance
         FROM transactions t JOIN accounts a ON a.id = t.account_id
-        LEFT JOIN ({_ACCOUNT_BASE}) b ON b.account_id = a.id
+        LEFT JOIN ({_ACCOUNT_OFFSET}) b ON b.account_id = a.id
         WHERE t.status = 'confirmed'
-        GROUP BY a.id, a.name, t.booking_date, b.opening_balance""",
+        GROUP BY a.id, a.name, t.booking_date, b.base""",
     "v_balance_total": f"""
         SELECT booking_date AS day,
-               (SELECT COALESCE(SUM(opening_balance), 0)
-                FROM ({_ACCOUNT_BASE}) s)
+               (SELECT COALESCE(SUM(base), 0)
+                FROM ({_ACCOUNT_OFFSET}) s)
                  + SUM(SUM(amount)) OVER (ORDER BY booking_date) AS balance
         FROM transactions WHERE status = 'confirmed' GROUP BY booking_date""",
     "v_monthly_by_category": """