"""Money flows (donations, contracts, government ads, party funding, political ads), social posts, wiki edits.""" from __future__ import annotations from datetime import date from psycopg import Connection from politrack.queries.common import VISIBLE, SourceFilter, evidence_json def list_money(conn: Connection, *, sources: SourceFilter, kinds: list[str] | None = None, org: str | None = None, person: str | None = None, q: str | None = None, since: date | None = None, until: date | None = None, min_amount: float | None = None, limit: int = 50, offset: int = 0) -> list[dict]: """Money flows, filterable by kind, organisation (either side), connected person and period. `person` = flows of organisations connected to that person (side jobs, careers, party) - the "follow the money" view. """ source_sql, params = sources.sql("s") params.update({"kinds": kinds, "org": org, "person": person, "q": q, "since": since, "until": until, "min_amount": min_amount, "limit": limit, "offset": offset, "include_pending": False}) return conn.execute( f""" WITH person_orgs AS ( SELECT c.counterpart_org_id AS org_id FROM claim c JOIN person p ON p.id = c.person_id WHERE p.slug = %(person)s AND c.counterpart_org_id IS NOT NULL AND c.merged_into IS NULL UNION SELECT r.party_id FROM role r JOIN person p ON p.id = r.person_id WHERE p.slug = %(person)s ) SELECT c.id, c.summary_de, c.summary_en, c.occurred_at, c.status, c.data, mf.kind, mf.amount, mf.amount_category, mf.currency, mf.paid_on, mf.period, mf.reference, mf.category, mf.payer_name, mf.payee_name, pa.slug AS payer_slug, pa.kind AS payer_kind, pe.slug AS payee_slug, pe.kind AS payee_kind, ev.evidence, ev.source_count FROM money_flow mf JOIN claim c ON c.id = mf.claim_id LEFT JOIN organization pa ON pa.id = mf.payer_org_id LEFT JOIN organization pe ON pe.id = mf.payee_org_id {evidence_json(source_sql)} WHERE ev.evidence IS NOT NULL AND {VISIBLE} AND (%(kinds)s::text[] IS NULL OR mf.kind = ANY(%(kinds)s)) AND (%(org)s::text IS NULL OR pa.slug = %(org)s OR pe.slug = %(org)s) AND (%(person)s::text IS NULL OR mf.payer_org_id IN (SELECT org_id FROM person_orgs) OR mf.payee_org_id IN (SELECT org_id FROM person_orgs)) AND (%(q)s::text IS NULL OR mf.payer_name ILIKE '%%' || %(q)s || '%%' OR mf.payee_name ILIKE '%%' || %(q)s || '%%' OR c.summary_de ILIKE '%%' || %(q)s || '%%') AND (%(since)s::date IS NULL OR COALESCE(mf.paid_on, c.occurred_at::date, c.valid_from) >= %(since)s) AND (%(until)s::date IS NULL OR COALESCE(mf.paid_on, c.occurred_at::date, c.valid_from) <= %(until)s) AND (%(min_amount)s::numeric IS NULL OR mf.amount >= %(min_amount)s) ORDER BY COALESCE(mf.paid_on, c.occurred_at::date, c.valid_from) DESC NULLS LAST, mf.amount DESC NULLS LAST LIMIT %(limit)s OFFSET %(offset)s """, params, ).fetchall() def money_summary(conn: Connection, *, kinds: list[str] | None = None, since: date | None = None) -> list[dict]: """Top payers/payees per kind (for the money overview page).""" return conn.execute( """ SELECT mf.kind, o.slug, o.name, o.kind AS org_kind, CASE WHEN o.id = mf.payer_org_id THEN 'payer' ELSE 'payee' END AS side, sum(mf.amount) AS total, count(*) AS count FROM money_flow mf JOIN claim c ON c.id = mf.claim_id AND c.merged_into IS NULL JOIN organization o ON o.id IN (mf.payer_org_id, mf.payee_org_id) WHERE (%(kinds)s::text[] IS NULL OR mf.kind = ANY(%(kinds)s)) AND (%(since)s::date IS NULL OR COALESCE(mf.paid_on, c.valid_from) >= %(since)s) GROUP BY 1, 2, 3, 4, 5 ORDER BY total DESC NULLS LAST LIMIT 100 """, {"kinds": kinds, "since": since}, ).fetchall() def social_feed(conn: Connection, *, sources: SourceFilter, person: str | None = None, party: str | None = None, platforms: list[str] | None = None, limit: int = 50, offset: int = 0) -> list[dict]: """Public posts of politicians and of party/institution channels (`party` = the party's own channels plus posts of its current members).""" source_sql, params = sources.sql("s") params.update({"person": person, "party": party, "platforms": platforms, "limit": limit, "offset": offset, "include_pending": False}) return conn.execute( f""" SELECT c.id, st.platform, st.said_at, st.excerpt, st.url, st.title, c.data, p.slug AS person_slug, p.full_name AS person_name, o.slug AS org_slug, o.name AS org_name, ev.evidence FROM statement st JOIN claim c ON c.id = st.claim_id LEFT JOIN person p ON p.id = st.person_id LEFT JOIN organization o ON o.id = c.organization_id AND st.person_id IS NULL {evidence_json(source_sql)} WHERE st.context = 'social_post' AND ev.evidence IS NOT NULL AND {VISIBLE} AND (%(person)s::text IS NULL OR p.slug = %(person)s) AND (%(platforms)s::text[] IS NULL OR st.platform = ANY(%(platforms)s)) AND (%(party)s::text IS NULL OR o.slug = %(party)s OR EXISTS ( SELECT 1 FROM person_status ps JOIN organization po ON po.id = ANY(ps.current_party_ids) WHERE ps.person_id = p.id AND po.slug = %(party)s)) ORDER BY st.said_at DESC NULLS LAST LIMIT %(limit)s OFFSET %(offset)s """, params, ).fetchall() def wiki_edits(conn: Connection, *, limit: int = 100, offset: int = 0) -> list[dict]: return conn.execute( """ SELECT c.id, c.occurred_at, c.summary_de, c.summary_en, c.data, o.slug AS org_slug, o.name AS org_name, p.slug AS person_slug, p.full_name AS person_name, (SELECT d.url FROM claim_evidence e JOIN document d ON d.id = e.document_id WHERE e.claim_id = c.id LIMIT 1) AS url FROM claim c JOIN organization o ON o.id = c.organization_id LEFT JOIN person p ON p.id = c.counterpart_person_id WHERE c.claim_type = 'wiki_edit' AND c.merged_into IS NULL ORDER BY c.occurred_at DESC NULLS LAST LIMIT %s OFFSET %s """, (limit, offset), ).fetchall()