from __future__ import annotations import base64 import binascii from datetime import UTC, datetime from decimal import Decimal, InvalidOperation from hashlib import sha256 import json import os from pathlib import Path from sqlite3 import Connection, IntegrityError from typing import Any from uuid import uuid4 from jarvis_finance.config.paths import DEFAULT_RUNTIME_DIR from jarvis_finance.imports.truewealth_tax_statement import ( PARSER_ID, PARSER_VERSION, TrueWealthStatement, parse_truewealth_tax_statement, ) from jarvis_finance.services.performance_scope import set_performance_scope_classification TRUEWEALTH_PORTFOLIO_ID = "truewealth-free-assets" TRUEWEALTH_LABEL = "Freie Anlagen" def _now() -> str: return datetime.now(UTC).replace(microsecond=0).isoformat().replace("+00:00", "Z") def _id(prefix: str) -> str: return f"{prefix}_{uuid4().hex[:20]}" def _json_hash(value: Any) -> str: return sha256(json.dumps(value, sort_keys=True, separators=(",", ":"), default=str).encode()).hexdigest() def _decimal_text(value: Decimal, places: str = "0.01") -> str: return format(value.quantize(Decimal(places)), "f") def _decode_pdf(content_base64: str) -> bytes: try: return base64.b64decode(content_base64, validate=True) except (binascii.Error, ValueError) as exc: raise ValueError("TrueWealth PDF is not valid base64") from exc def _resolve_account_id(conn: Connection, requested: str | None = None) -> str: mapped = conn.execute( "SELECT account_id FROM truewealth_portfolios WHERE portfolio_id=? AND is_active=1", (TRUEWEALTH_PORTFOLIO_ID,), ).fetchone() if mapped: return str(mapped["account_id"]) configured = os.environ.get("JARVIS_TRUEWEALTH_ACCOUNT_ID", "").strip() if configured: return configured if requested: return requested candidates = conn.execute( """SELECT DISTINCT a.account_id FROM accounts a JOIN platforms p ON p.platform_id=a.platform_id WHERE a.is_active=1 AND replace(replace(lower(p.name),' ',''),'-','') LIKE '%truewealth%' AND EXISTS ( SELECT 1 FROM account_value_snapshots v WHERE v.account_id=a.account_id AND COALESCE(v.is_active,1)=1 ) ORDER BY a.account_id""" ).fetchall() if not candidates: candidates = conn.execute( """SELECT DISTINCT a.account_id FROM accounts a JOIN platforms p ON p.platform_id=a.platform_id WHERE a.is_active=1 AND replace(replace(lower(p.name),' ',''),'-','') LIKE '%truewealth%' ORDER BY a.account_id""" ).fetchall() if len(candidates) != 1: raise ValueError("Confirmed TrueWealth target account cannot be resolved unambiguously") return str(candidates[0]["account_id"]) def _validate_account(conn: Connection, account_id: str | None = None) -> str: resolved = _resolve_account_id(conn, account_id) if account_id and account_id != resolved: raise ValueError("TrueWealth portfolio account does not match the confirmed mapping") row = conn.execute( """SELECT a.account_id,a.is_active,p.name AS platform FROM accounts a JOIN platforms p ON p.platform_id=a.platform_id WHERE a.account_id=?""", (resolved,), ).fetchone() platform_key = str(row["platform"] if row else "").lower().replace(" ", "").replace("-", "") if not row or not row["is_active"] or "truewealth" not in platform_key: raise ValueError("Confirmed TrueWealth target account is unavailable") return resolved def _revision(conn: Connection, account_id: str) -> str: row = conn.execute( """SELECT COALESCE(MAX(created_at),'') AS latest,COUNT(*) AS count FROM account_value_snapshots WHERE account_id=?""", (account_id,), ).fetchone() imported = conn.execute("SELECT COUNT(*) FROM truewealth_import_batches").fetchone()[0] return _json_hash([row["latest"], row["count"], imported]) def _preview_identity(kind: str, payload: dict[str, Any], revision: str) -> tuple[str, str]: fingerprint = _json_hash([kind, payload, revision]) return f"twpreview_{fingerprint[:24]}", f"twconfirm_{fingerprint[24:48]}" def _statement_preview_payload(statement: TrueWealthStatement, filename: str) -> dict[str, Any]: activities = tuple(getattr(statement, "activities", ())) cash_basis = "confirmed_cash_component" cash_total = statement.cash_total_chf if not statement.cash: cash_total = statement.source_total_chf - statement.securities_total_chf cash_basis = "source_total_minus_confirmed_positions" components_total = statement.securities_total_chf + cash_total difference = statement.source_total_chf - components_total cash_derivation_status = "blocked" if cash_total < 0 else "confirmed_or_derived" return { "portfolio_label": TRUEWEALTH_LABEL, "snapshot_date": statement.statement_date, "period_from": statement.period_from, "period_to": statement.period_to, "file_sha256": statement.file_sha256, "filename_sha256": sha256(filename.encode()).hexdigest(), "page_count": statement.page_count, "source_total_chf": _decimal_text(statement.source_total_chf), "securities_total_chf": _decimal_text(statement.securities_total_chf), "cash_total_chf": _decimal_text(cash_total), "cash_basis": cash_basis, "cash_derivation_status": cash_derivation_status, "components_total_chf": _decimal_text(components_total), "reconciliation_difference_chf": _decimal_text(difference), "reconciliation_status": "matched" if difference == 0 else "within_tolerance", "positions": [ { "name": row.name, "isin": row.isin, "quantity": format(row.quantity, "f"), "currency": row.currency, "source_price": format(row.source_price, "f"), "source_value_chf": _decimal_text(row.source_value_chf), } for row in statement.positions ], "cash": [ { "currency": row.currency, "amount_original": format(row.amount_original, "f"), "fx_rate_to_chf": format(row.fx_rate_to_chf, "f") if row.fx_rate_to_chf is not None else None, "source_value_chf": _decimal_text(row.value_chf), } for row in statement.cash ], "activity_count": len(activities), "activity_counts": { event_type: sum(row.event_type == event_type for row in activities) for event_type in ("buy", "sell", "dividend", "split_out", "split_in") }, "activity_period_from": min((row.occurred_on for row in activities), default=None), "activity_period_to": max((row.occurred_on for row in activities), default=None), "external_cashflows_complete": False, "performance_blocker": "Externe Ein- und Auszahlungen sind im Steuerreport nicht vollständig ausgewiesen.", "parser_id": PARSER_ID, "parser_version": PARSER_VERSION, } def preview_truewealth_import(conn: Connection, request: dict[str, Any]) -> dict[str, Any]: requested = str(request.get("account_id") or "").strip() or None account_id = _validate_account(conn, requested) raw = _decode_pdf(str(request.get("content_base64") or "")) filename = str(request.get("file_name") or "source.pdf") statement = parse_truewealth_tax_statement(raw) payload = _statement_preview_payload(statement, filename) preview_id, confirmation_id = _preview_identity("official_import", payload, _revision(conn, account_id)) duplicate = conn.execute( "SELECT batch_id FROM truewealth_import_batches WHERE file_sha256=?", (statement.file_sha256,), ).fetchone() conflict = conn.execute( """SELECT batch_id FROM truewealth_import_batches WHERE account_id=? AND snapshot_date=? AND file_sha256<>? LIMIT 1""", (account_id, statement.statement_date, statement.file_sha256), ).fetchone() warnings = [] if payload["cash_derivation_status"] == "blocked": warnings.append("Derived cash residual is negative; confirmation is blocked") if Decimal(payload["reconciliation_difference_chf"]) != 0: warnings.append("Source total is rounded to whole CHF; components differ within CHF 1.00") if conflict: warnings.append("A different official source already exists for this snapshot date") return { **payload, "preview_id": preview_id, "confirmation_id": confirmation_id, "duplicate": bool(duplicate), "existing_batch_id": str(duplicate["batch_id"]) if duplicate else None, "conflict": bool(conflict), "conflicting_batch_id": str(conflict["batch_id"]) if conflict else None, "warnings": warnings, } def _archive_pdf(raw: bytes, digest: str) -> tuple[str, bool]: root = Path(os.environ.get("JARVIS_FINANCE_RUNTIME_DIR", str(DEFAULT_RUNTIME_DIR))).expanduser() folder = root / "imports" / "truewealth" / "archive" folder.mkdir(mode=0o700, parents=True, exist_ok=True) try: folder.chmod(0o700) except OSError: pass target = folder / f"{digest}.pdf" created = False if target.exists(): if sha256(target.read_bytes()).hexdigest() != digest: raise ValueError("Archived TrueWealth source hash mismatch") else: tmp = folder / f".{digest}.{uuid4().hex}.tmp" try: fd = os.open(tmp, os.O_WRONLY | os.O_CREAT | os.O_EXCL, 0o600) with os.fdopen(fd, "wb") as handle: handle.write(raw) handle.flush() os.fsync(handle.fileno()) os.replace(tmp, target) target.chmod(0o600) created = True finally: if tmp.exists(): tmp.unlink() return f"truewealth/archive/{digest}.pdf", created def _remove_new_archive(reference: str) -> None: root = Path(os.environ.get("JARVIS_FINANCE_RUNTIME_DIR", str(DEFAULT_RUNTIME_DIR))).expanduser() target = root / "imports" / reference try: target.unlink(missing_ok=True) except OSError: pass def _insert_audit( conn: Connection, *, action: str, entity_type: str, entity_id: str, details: dict[str, Any], note: str = "", quality: str = "ok", ) -> str: audit_id = _id("audit") now = _now() conn.execute( """INSERT INTO audit_log( audit_id,timestamp,source,action,entity_type,entity_id,old_values_json,new_values_json, user_text_note,confirmed,confirmation_timestamp,auto_parsed,parse_confidence,created_by, quality_status,created_at) VALUES(?,?,'truewealth_sprint12',?,?,?,NULL,?,?,1,?,0,NULL,'user',?,?)""", (audit_id, now, action, entity_type, entity_id, json.dumps(details, sort_keys=True), note, now, quality, now), ) return audit_id def confirm_truewealth_import(conn: Connection, request: dict[str, Any]) -> dict[str, Any]: if request.get("confirm") is not True: raise ValueError("Explicit TrueWealth import confirmation is required") preview = preview_truewealth_import(conn, request) if request.get("preview_id") != preview["preview_id"] or request.get("confirmation_id") != preview["confirmation_id"]: raise ValueError("TrueWealth import preview is stale or changed") if preview["duplicate"]: row = conn.execute( "SELECT batch_id,audit_id FROM truewealth_import_batches WHERE file_sha256=?", (preview["file_sha256"],), ).fetchone() return {"status": "confirmed", "batch_id": row["batch_id"], "audit_id": row["audit_id"], "idempotent": True} if preview["conflict"]: raise ValueError("A different official TrueWealth source already exists for this snapshot date") if preview["cash_derivation_status"] == "blocked": raise ValueError("TrueWealth cash residual is negative; official anchor confirmation is blocked") account_id = _validate_account(conn, str(request.get("account_id") or "").strip() or None) raw = _decode_pdf(str(request.get("content_base64") or "")) statement = parse_truewealth_tax_statement(raw) archive_reference, archive_created = _archive_pdf(raw, statement.file_sha256) now = _now() batch_id = _id("twbatch") snapshot_id = _id("twsnapshot") account_snapshot_id = _id("acctval") difference = Decimal(preview["reconciliation_difference_chf"]) try: conn.execute("BEGIN IMMEDIATE") _validate_account(conn, account_id) existing_portfolios = conn.execute( "SELECT portfolio_id,account_id FROM truewealth_portfolios WHERE is_active=1" ).fetchall() if existing_portfolios and any( row["portfolio_id"] != TRUEWEALTH_PORTFOLIO_ID or row["account_id"] != account_id for row in existing_portfolios ): raise ValueError("TrueWealth portfolio mapping is ambiguous") conn.execute( """INSERT OR IGNORE INTO truewealth_portfolios( portfolio_id,account_id,source_reference_hash,label,portfolio_kind,base_currency,is_active,created_at) VALUES(?,?,?,'Freie Anlagen','free_assets','CHF',1,?)""", (TRUEWEALTH_PORTFOLIO_ID, account_id, _json_hash(["TrueWealth", "Freie Anlagen"]), now), ) audit_id = _insert_audit( conn, action="truewealth_import_confirm", entity_type="truewealth_import_batch", entity_id=batch_id, details={ "snapshot_date": statement.statement_date, "file_sha256": statement.file_sha256, "position_count": len(statement.positions), "cash_count": len(statement.cash), "activity_count": len(statement.activities), "activity_counts": preview["activity_counts"], "external_cashflows_complete": False, "cash_basis": preview["cash_basis"], "cash_total_chf": preview["cash_total_chf"], "parser_id": PARSER_ID, "parser_version": PARSER_VERSION, }, ) conn.execute( """INSERT INTO truewealth_import_batches( batch_id,portfolio_id,account_id,file_sha256,filename_sha256,source_file_type,parser_id, parser_version,provenance,snapshot_date,page_count,archive_reference,status,audit_id,confirmed_at,confirmed_by, period_from,period_to,activity_from,activity_to,external_cashflows_complete) VALUES(?,?,?,?,?,'application/pdf',?,?,'truewealth_customer_export',?,?,?,'confirmed',?,?,'user',?,?,?,?,0)""", ( batch_id, TRUEWEALTH_PORTFOLIO_ID, account_id, statement.file_sha256, preview["filename_sha256"], PARSER_ID, PARSER_VERSION, statement.statement_date, statement.page_count, archive_reference, audit_id, now, statement.period_from, statement.period_to, preview["activity_period_from"], preview["activity_period_to"], ), ) conn.execute( """INSERT INTO truewealth_snapshots( snapshot_id,portfolio_id,account_id,batch_id,snapshot_date,source_total_chf,securities_total_chf, cash_total_chf,components_total_chf,reconciliation_difference_chf,reconciliation_tolerance_chf, reconciliation_status,position_count,cash_count,completeness_status,reason_codes_json,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,'1.00',?,?,?,'complete',?,?)""", ( snapshot_id, TRUEWEALTH_PORTFOLIO_ID, account_id, batch_id, statement.statement_date, _decimal_text(statement.source_total_chf), _decimal_text(statement.securities_total_chf), preview["cash_total_chf"], preview["components_total_chf"], _decimal_text(difference), "matched" if difference == 0 else "within_tolerance", len(statement.positions), len(statement.cash), json.dumps( sorted( (["source_total_rounded"] if difference else []) + (["cash_residual_derived_from_source_total"] if not statement.cash else []) ) ), now, ), ) for row in statement.positions: conn.execute( """INSERT INTO truewealth_snapshot_positions( snapshot_position_id,snapshot_id,source_row_reference,instrument_name,isin,asset_type,quantity, price_currency,source_price,source_value_chf,source_evidence_json,created_at) VALUES(?,?,?,?,?,NULL,?,?,?,?,?,?)""", ( _id("twposition"), snapshot_id, row.source_ref, row.name, row.isin, format(row.quantity, "f"), row.currency, format(row.source_price, "f"), _decimal_text(row.source_value_chf), json.dumps({"source": "official_tax_statement", "estimated": False}), now, ), ) for row in statement.cash: conn.execute( """INSERT INTO truewealth_snapshot_cash( snapshot_cash_id,snapshot_id,source_row_reference,currency,amount_original,fx_rate_to_chf, source_value_chf,source_evidence_json,created_at) VALUES(?,?,?,?,?,?,?,?,?)""", ( _id("twcash"), snapshot_id, row.source_ref, row.currency, format(row.amount_original, "f"), format(row.fx_rate_to_chf, "f") if row.fx_rate_to_chf is not None else None, _decimal_text(row.value_chf), json.dumps({"source": "official_tax_statement", "estimated": False}), now, ), ) for row in statement.activities: conn.execute( """INSERT OR IGNORE INTO truewealth_activities( activity_id,batch_id,portfolio_id,account_id,occurred_on,event_type, instrument_name,isin,quantity,gross_amount_chf,tax_amount_chf, external_cashflow,source_row_fingerprint,evidence_json,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,0,?,?,?)""", ( "twactivity_" + row.source_row_fingerprint[:20], batch_id, TRUEWEALTH_PORTFOLIO_ID, account_id, row.occurred_on, row.event_type, row.instrument_name, row.isin, format(row.quantity, "f"), _decimal_text(row.gross_amount_chf) if row.gross_amount_chf is not None else None, _decimal_text(row.tax_amount_chf) if row.tax_amount_chf is not None else None, row.source_row_fingerprint, json.dumps( { "source": "official_tax_statement", "external_cashflow": False, "amount_basis": "reported_chf_columns" if row.gross_amount_chf is not None else None, }, sort_keys=True, ), now, ), ) conn.execute( """INSERT INTO account_value_snapshots( snapshot_id,account_id,valuation_date,total_value_chf,currency,source_type,quality_status,notes, created_at,valuation_at,source_reference,is_active) VALUES(?,?,?,?,'CHF','truewealth_official_import','ok','Official TrueWealth source total',?,?,?,1)""", ( account_snapshot_id, account_id, statement.statement_date, _decimal_text(statement.source_total_chf), now, None, snapshot_id, ), ) set_performance_scope_classification( conn, account_id=account_id, included=True, classification_role="canonical_truewealth_total_value", source="truewealth_confirmed_official_import", note="Canonical TrueWealth official total-value account", classified_at=now, ) conn.commit() except IntegrityError as exc: conn.rollback() duplicate = conn.execute( "SELECT batch_id,audit_id FROM truewealth_import_batches WHERE file_sha256=?", (statement.file_sha256,), ).fetchone() if duplicate: return {"status": "confirmed", "batch_id": duplicate["batch_id"], "audit_id": duplicate["audit_id"], "idempotent": True} if archive_created: _remove_new_archive(archive_reference) raise ValueError("TrueWealth import conflicted with existing immutable data") from exc except Exception: conn.rollback() if archive_created: _remove_new_archive(archive_reference) raise return { "status": "confirmed", "batch_id": batch_id, "snapshot_id": snapshot_id, "audit_id": audit_id, "activity_count": len(statement.activities), "idempotent": False, } def _parse_valuation_at(value: str) -> datetime: try: parsed = datetime.fromisoformat(value.replace("Z", "+00:00")) except ValueError as exc: raise ValueError("Manual valuation timestamp is invalid") from exc if parsed.tzinfo is None: parsed = parsed.replace(tzinfo=UTC) return parsed.astimezone(UTC) def _manual_payload(request: dict[str, Any]) -> dict[str, Any]: try: value = Decimal(str(request.get("total_value_chf") or "")) except InvalidOperation as exc: raise ValueError("Manual TrueWealth total is invalid") from exc if not value.is_finite() or value <= 0 or value > Decimal("1000000000000"): raise ValueError("Manual TrueWealth total must be a positive finite CHF amount") valuation = _parse_valuation_at(str(request.get("valuation_at") or "")) note = str(request.get("note") or "").strip() if len(note) > 240: raise ValueError("Manual TrueWealth note is too long") return { "total_value_chf": _decimal_text(value), "valuation_at": valuation.isoformat().replace("+00:00", "Z"), "valuation_date": valuation.date().isoformat(), "note": note, "source_type": "truewealth_manual_provisional", } def preview_manual_truewealth_value(conn: Connection, request: dict[str, Any]) -> dict[str, Any]: account_id = _validate_account(conn) payload = _manual_payload(request) preview_id, confirmation_id = _preview_identity("manual_value", payload, _revision(conn, account_id)) return { **payload, "preview_id": preview_id, "confirmation_id": confirmation_id, "status": "manual_provisional", "warnings": [ "Position details remain at the latest official import date", "No difference is distributed to positions or invented as cash", ], } def confirm_manual_truewealth_value(conn: Connection, request: dict[str, Any]) -> dict[str, Any]: if request.get("confirm") is not True: raise ValueError("Explicit manual TrueWealth confirmation is required") preview = preview_manual_truewealth_value(conn, request) if request.get("preview_id") != preview["preview_id"] or request.get("confirmation_id") != preview["confirmation_id"]: raise ValueError("Manual TrueWealth preview is stale or changed") account_id = _validate_account(conn, str(request.get("account_id") or "").strip() or None) snapshot_id = _id("acctval") now = _now() try: conn.execute("BEGIN IMMEDIATE") conn.execute( """INSERT INTO account_value_snapshots( snapshot_id,account_id,valuation_date,total_value_chf,currency,source_type,quality_status,notes, created_at,valuation_at,source_reference,is_active) VALUES(?,?,?,?,'CHF','truewealth_manual_provisional','warning',?,?,?,?,1)""", ( snapshot_id, account_id, preview["valuation_date"], preview["total_value_chf"], preview["note"], now, preview["valuation_at"], snapshot_id, ), ) audit_id = _insert_audit( conn, action="truewealth_manual_value_confirm", entity_type="account_value_snapshot", entity_id=snapshot_id, details={"valuation_at": preview["valuation_at"], "source_type": "manual_provisional"}, note=preview["note"], quality="warning", ) conn.commit() except Exception: conn.rollback() raise return {"status": "confirmed", "snapshot_id": snapshot_id, "audit_id": audit_id} def set_manual_truewealth_value_active(conn: Connection, snapshot_id: str, request: dict[str, Any]) -> dict[str, Any]: if request.get("confirm") is not True: raise ValueError("Explicit manual valuation status confirmation is required") active = bool(request.get("active")) account_id = _validate_account(conn) row = conn.execute( """SELECT snapshot_id,is_active FROM account_value_snapshots WHERE snapshot_id=? AND account_id=? AND source_type IN ('truewealth_manual_provisional','manual_total_value')""", (snapshot_id, account_id), ).fetchone() if not row: raise ValueError("Manual TrueWealth valuation was not found") if bool(row["is_active"]) == active: audits = conn.execute( "SELECT audit_id FROM audit_log WHERE entity_type='account_value_snapshot' AND entity_id=? ORDER BY timestamp DESC LIMIT 1", (snapshot_id,), ).fetchone() return {"status": "confirmed", "snapshot_id": snapshot_id, "audit_id": audits["audit_id"] if audits else "", "idempotent": True} now = _now() note = str(request.get("note") or "").strip() try: conn.execute("BEGIN IMMEDIATE") conn.execute( """UPDATE account_value_snapshots SET is_active=?,deactivated_at=?,deactivation_reason=?,updated_at=? WHERE snapshot_id=?""", (1 if active else 0, None if active else now, None if active else note, now, snapshot_id), ) audit_id = _insert_audit( conn, action="truewealth_manual_value_reactivate" if active else "truewealth_manual_value_deactivate", entity_type="account_value_snapshot", entity_id=snapshot_id, details={"active": active}, note=note, quality="warning", ) conn.commit() except Exception: conn.rollback() raise return {"status": "confirmed", "snapshot_id": snapshot_id, "audit_id": audit_id, "idempotent": False} def _current_value_row(conn: Connection, account_id: str): return conn.execute( """SELECT snapshot_id,total_value_chf,valuation_date,valuation_at, source_type,quality_status,notes,is_active FROM account_value_snapshots WHERE account_id=? AND COALESCE(is_active,1)=1 ORDER BY valuation_date DESC, CASE source_type WHEN 'truewealth_official_import' THEN 3 WHEN 'truewealth_manual_provisional' THEN 2 ELSE 1 END DESC, COALESCE(valuation_at,created_at) DESC,created_at DESC LIMIT 1""", (account_id,), ).fetchone() def get_current_truewealth_value(conn: Connection) -> Decimal: account_id = _validate_account(conn) row = _current_value_row(conn, account_id) return Decimal(str(row["total_value_chf"])) if row else Decimal("0") def get_truewealth_summary(conn: Connection) -> dict[str, Any]: account_id = _validate_account(conn) current = _current_value_row(conn, account_id) official = conn.execute( """SELECT * FROM truewealth_snapshots WHERE account_id=? ORDER BY snapshot_date DESC,created_at DESC LIMIT 1""", (account_id,), ).fetchone() positions: list[dict[str, Any]] = [] cash: list[dict[str, Any]] = [] if official: positions = [dict(row) for row in conn.execute( """SELECT snapshot_position_id,instrument_name,isin,asset_type,quantity,price_currency, source_price,source_value_chf FROM truewealth_snapshot_positions WHERE snapshot_id=? ORDER BY instrument_name""", (official["snapshot_id"],), ).fetchall()] cash = [dict(row) for row in conn.execute( """SELECT snapshot_cash_id,currency,amount_original,fx_rate_to_chf,source_value_chf FROM truewealth_snapshot_cash WHERE snapshot_id=? ORDER BY currency""", (official["snapshot_id"],), ).fetchall()] history = [dict(row) for row in conn.execute( """SELECT snapshot_id,total_value_chf,valuation_date,valuation_at, source_type,quality_status,notes,COALESCE(is_active,1) AS is_active,deactivated_at,deactivation_reason FROM account_value_snapshots WHERE account_id=? ORDER BY valuation_date DESC,COALESCE(valuation_at,created_at) DESC,created_at DESC""", (account_id,), ).fetchall()] imports = [dict(row) for row in conn.execute( """SELECT b.batch_id,b.snapshot_date,b.parser_id,b.parser_version,b.confirmed_at,b.status, s.snapshot_id,s.position_count,s.cash_count,s.source_total_chf,s.securities_total_chf, s.cash_total_chf,s.components_total_chf,s.reconciliation_difference_chf,s.reconciliation_status, s.completeness_status FROM truewealth_import_batches b JOIN truewealth_snapshots s ON s.batch_id=b.batch_id WHERE b.account_id=? ORDER BY b.snapshot_date DESC,b.confirmed_at DESC""", (account_id,), ).fetchall()] current_type = str(current["source_type"]) if current else "unavailable" current_is_official = current_type == "truewealth_official_import" current_is_manual = current is not None and not current_is_official current_date = str(current["valuation_date"]) if current else None current_at = str(current["valuation_at"]) if current and current["valuation_at"] else current_date if current_is_manual: current_label = f"Gesamtwert manuell aktualisiert am {current_at}" elif current_is_official: current_label = "Gesamtwert gemäss offiziellem Import" else: current_label = "Noch keine Bewertung vorhanden" return { "portfolio_label": TRUEWEALTH_LABEL, "current_value_chf": str(current["total_value_chf"]) if current else None, "current_valuation_at": current_at, "current_source_type": current_type, "current_is_manual_provisional": current_is_manual, "current_label": current_label, "positions_snapshot_date": str(official["snapshot_date"]) if official else None, "positions_label": f"Positionsdetails gemäss letztem Import vom {official['snapshot_date']}" if official else "Noch keine offiziellen Positionsdetails importiert", "mixed_as_of": bool(current_is_manual and current_date and official and current_date > str(official["snapshot_date"])), "allocation_performance_usable": bool(current_is_official and current_date and official and current_date == str(official["snapshot_date"])), "positions": positions, "cash": cash, "history": history, "imports": imports, "latest_official": dict(official) if official else None, }