133 lines
6.0 KiB
Python
133 lines
6.0 KiB
Python
from sqlalchemy import text
|
|
from sqlalchemy.engine import Engine
|
|
|
|
# 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
|
|
"""
|
|
|
|
|
|
# Per-account earliest day the daily series needs to start at: the earlier of
|
|
# the account's first confirmed booking and its anchor_date (an anchor can
|
|
# predate the first booking, e.g. an anchor set before any import). Accounts
|
|
# with neither an anchor nor any confirmed booking have no meaningful start
|
|
# and are excluded (nothing to plot).
|
|
_ACCOUNT_START = """
|
|
SELECT a.id AS account_id, a.name AS account,
|
|
LEAST(
|
|
COALESCE(a.anchor_date, ft.first_date),
|
|
COALESCE(ft.first_date, a.anchor_date)
|
|
) AS start_date
|
|
FROM accounts a
|
|
LEFT JOIN (
|
|
SELECT account_id, MIN(booking_date) AS first_date
|
|
FROM transactions WHERE status = 'confirmed'
|
|
GROUP BY account_id
|
|
) ft ON ft.account_id = a.id
|
|
WHERE a.anchor_date IS NOT NULL OR ft.first_date IS NOT NULL
|
|
"""
|
|
|
|
VIEWS: dict[str, str] = {
|
|
# Daily (gap-free) balance per account: one row per calendar day from the
|
|
# account's start date (see _ACCOUNT_START) through CURRENT_DATE. The
|
|
# cumulative SUM ... OVER (... ROWS UNBOUNDED PRECEDING) carries the
|
|
# previous day's balance forward on days without any confirmed booking,
|
|
# matching services.balances.account_balance's per-Tagesende semantics
|
|
# (base + cumulated amounts up to and including that day).
|
|
"v_balance_history": f"""
|
|
WITH offsets AS ({_ACCOUNT_OFFSET}),
|
|
bounds AS ({_ACCOUNT_START}),
|
|
days AS (
|
|
SELECT b.account_id, b.account, gs.day::date AS day
|
|
FROM bounds b
|
|
CROSS JOIN LATERAL generate_series(
|
|
b.start_date, CURRENT_DATE, interval '1 day') AS gs(day)
|
|
),
|
|
daily_tx AS (
|
|
SELECT account_id, booking_date AS day, SUM(amount) AS day_amount
|
|
FROM transactions WHERE status = 'confirmed'
|
|
GROUP BY account_id, booking_date
|
|
)
|
|
SELECT d.day, d.account,
|
|
COALESCE(o.base, 0)
|
|
+ SUM(COALESCE(dt.day_amount, 0)) OVER (
|
|
PARTITION BY d.account_id ORDER BY d.day
|
|
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance
|
|
FROM days d
|
|
LEFT JOIN daily_tx dt ON dt.account_id = d.account_id AND dt.day = d.day
|
|
LEFT JOIN offsets o ON o.account_id = d.account_id""",
|
|
# Daily (gap-free) total balance across all accounts: same carry-forward
|
|
# logic as v_balance_history, but summed globally (equivalent to summing
|
|
# each account's own base + cumulated amounts, since a cumulative sum of
|
|
# a union of per-day amounts equals the sum of per-account cumulative
|
|
# sums). The day series spans from the earliest account start date
|
|
# through CURRENT_DATE.
|
|
"v_balance_total": f"""
|
|
WITH offsets AS ({_ACCOUNT_OFFSET}),
|
|
bounds AS ({_ACCOUNT_START}),
|
|
days AS (
|
|
SELECT gs.day::date AS day
|
|
FROM (SELECT MIN(start_date) AS start_date FROM bounds) b
|
|
CROSS JOIN LATERAL generate_series(
|
|
b.start_date, CURRENT_DATE, interval '1 day') AS gs(day)
|
|
),
|
|
daily_tx AS (
|
|
SELECT booking_date AS day, SUM(amount) AS day_amount
|
|
FROM transactions WHERE status = 'confirmed'
|
|
GROUP BY booking_date
|
|
)
|
|
SELECT d.day,
|
|
(SELECT COALESCE(SUM(base), 0) FROM offsets)
|
|
+ SUM(COALESCE(dt.day_amount, 0)) OVER (
|
|
ORDER BY d.day
|
|
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance
|
|
FROM days d
|
|
LEFT JOIN daily_tx dt ON dt.day = d.day""",
|
|
"v_monthly_by_category": """
|
|
SELECT date_trunc('month', t.booking_date) AS month,
|
|
COALESCE(c.name, 'unkategorisiert') AS category,
|
|
SUM(t.amount) AS total
|
|
FROM transactions t LEFT JOIN categories c ON c.id = t.category_id
|
|
WHERE t.status = 'confirmed' GROUP BY 1, 2""",
|
|
"v_projection": """
|
|
SELECT s.name AS scenario, p.day, p.balance
|
|
FROM projection_points p JOIN scenarios s ON s.id = p.scenario_id""",
|
|
}
|
|
|
|
|
|
def create_views(engine: Engine) -> None:
|
|
if engine.dialect.name != "postgresql":
|
|
return
|
|
with engine.begin() as conn:
|
|
for name, body in VIEWS.items():
|
|
# CREATE OR REPLACE VIEW fails if the output column set changed
|
|
# (name/type/order). Fall back to DROP + CREATE in that case so a
|
|
# redeploy always applies the current definition.
|
|
try:
|
|
with conn.begin_nested():
|
|
conn.execute(
|
|
text(f"CREATE OR REPLACE VIEW {name} AS {body}"))
|
|
except Exception:
|
|
conn.execute(text(f"DROP VIEW IF EXISTS {name} CASCADE"))
|
|
conn.execute(text(f"CREATE VIEW {name} AS {body}"))
|