"""Votes: per person (individual roll calls + faction votes during mandates, with "say vs. vote"), lists, details.""" from __future__ import annotations from datetime import date from psycopg import Connection # Did the person speak on this item on the day of the vote? Speech titles are the item titles of the # parliament's speech list; vote_event titles come from the item page - similar, not identical. SPOKE = """ (SELECT jsonb_build_object('url', st.url, 'title', st.title) FROM statement st JOIN claim sc ON sc.id = st.claim_id AND sc.merged_into IS NULL WHERE st.person_id = me.id AND st.context = 'parliament_speech' AND st.said_at::date = ve.voted_on AND similarity(st.title, ve.item_title) > 0.35 ORDER BY similarity(st.title, ve.item_title) DESC LIMIT 1) """ def person_votes(conn: Connection, slug: str, *, topics: list[str] | None = None, include_parts: bool = False, limit: int = 100, offset: int = 0) -> list[dict]: """Votes concerning a person (include_parts: also votes on amendments and parts of a text). * individual=true: roll-call votes with the person's own vote; dissent=true when it differs from the majority of their faction (vote_position.party_id = their faction that day). * individual=false: faction votes cast while the person held a mandate for that faction. * spoke: the person's speech on that item on the same day ("say vs. vote"), if any. """ return conn.execute( f""" WITH me AS (SELECT id FROM person WHERE slug = %(slug)s) SELECT ve.id, ve.item_title, ve.item_ref, ve.stage, ve.voted_on, ve.result, ve.url, ve.protocol_url, ve.roll_call, vp.position, fo.short_name AS faction, ch.name AS chamber, true AS individual, (fp.position IS NOT NULL AND fp.position <> vp.position) AS dissent, {SPOKE} AS spoke, (SELECT jsonb_agg(DISTINCT t.slug) FROM topic_assignment ta JOIN topic t ON t.id = ta.topic_id WHERE ta.claim_id = vp.claim_id) AS topics FROM vote_position vp JOIN me ON me.id = vp.person_id JOIN claim c ON c.id = vp.claim_id AND c.merged_into IS NULL JOIN vote_event ve ON ve.id = vp.vote_event_id JOIN organization ch ON ch.id = ve.chamber_id LEFT JOIN organization fo ON fo.id = vp.party_id LEFT JOIN vote_position fp ON fp.vote_event_id = ve.id AND fp.organization_id = vp.party_id WHERE (%(include_parts)s OR NOT ve.part_vote) AND (%(topics)s::text[] IS NULL OR EXISTS ( SELECT 1 FROM topic_assignment ta JOIN topic t ON t.id = ta.topic_id WHERE ta.claim_id = vp.claim_id AND t.slug = ANY(%(topics)s))) UNION ALL SELECT ve.id, ve.item_title, ve.item_ref, ve.stage, ve.voted_on, ve.result, ve.url, ve.protocol_url, ve.roll_call, vp.position, o.short_name, ch.name, false, false, {SPOKE}, (SELECT jsonb_agg(DISTINCT t.slug) FROM topic_assignment ta JOIN topic t ON t.id = ta.topic_id WHERE ta.claim_id = vp.claim_id) FROM me JOIN role r ON r.person_id = me.id AND r.role = 'mp' JOIN claim rc ON rc.id = r.claim_id AND rc.merged_into IS NULL JOIN vote_event ve ON ve.chamber_id = r.organization_id AND NOT ve.roll_call AND ve.voted_on BETWEEN r.start_date AND COALESCE(r.end_date, 'infinity'::date) JOIN vote_position vp ON vp.vote_event_id = ve.id AND vp.organization_id = r.party_id JOIN organization o ON o.id = vp.organization_id JOIN organization ch ON ch.id = ve.chamber_id WHERE (%(topics)s::text[] IS NULL OR EXISTS ( SELECT 1 FROM topic_assignment ta JOIN topic t ON t.id = ta.topic_id WHERE ta.claim_id = vp.claim_id AND t.slug = ANY(%(topics)s))) ORDER BY voted_on DESC NULLS LAST LIMIT %(limit)s OFFSET %(offset)s """, {"slug": slug, "topics": topics, "include_parts": include_parts, "limit": limit, "offset": offset}, ).fetchall() def list_votes(conn: Connection, *, party: str | None = None, topics: list[str] | None = None, roll_call: bool | None = None, since: date | None = None, until: date | None = None, include_parts: bool = False, limit: int = 50, offset: int = 0) -> list[dict]: return conn.execute( """ SELECT ve.id, ve.item_title, ve.item_ref, ve.stage, ve.voted_on, ve.result, ve.url, ve.protocol_url, ve.raw_text, ve.roll_call, ch.name AS chamber, COALESCE(jsonb_agg(jsonb_build_object('party', o.short_name, 'slug', o.slug, 'color', o.color, 'position', vp.position) ORDER BY o.short_name) FILTER (WHERE o.id IS NOT NULL), '[]'::jsonb) AS positions, (SELECT count(*) FROM vote_position x WHERE x.vote_event_id = ve.id AND x.person_id IS NOT NULL) AS individual_votes FROM vote_event ve JOIN organization ch ON ch.id = ve.chamber_id LEFT JOIN vote_position vp ON vp.vote_event_id = ve.id AND vp.organization_id IS NOT NULL LEFT JOIN organization o ON o.id = vp.organization_id WHERE (%(include_parts)s OR NOT ve.part_vote) AND (%(party)s::text IS NULL OR EXISTS ( SELECT 1 FROM vote_position v2 JOIN organization o2 ON o2.id = v2.organization_id WHERE v2.vote_event_id = ve.id AND o2.slug = %(party)s)) AND (%(topics)s::text[] IS NULL OR EXISTS ( SELECT 1 FROM vote_position v3 JOIN topic_assignment ta ON ta.claim_id = v3.claim_id JOIN topic t ON t.id = ta.topic_id WHERE v3.vote_event_id = ve.id AND t.slug = ANY(%(topics)s))) AND (%(roll_call)s::boolean IS NULL OR ve.roll_call = %(roll_call)s) AND (%(since)s::date IS NULL OR ve.voted_on >= %(since)s) AND (%(until)s::date IS NULL OR ve.voted_on <= %(until)s) GROUP BY ve.id, ch.name ORDER BY ve.voted_on DESC NULLS LAST, ve.id DESC LIMIT %(limit)s OFFSET %(offset)s """, {"party": party, "topics": topics, "roll_call": roll_call, "since": since, "until": until, "include_parts": include_parts, "limit": limit, "offset": offset}, ).fetchall() def vote_detail(conn: Connection, vote_id: int) -> dict | None: vote = conn.execute( "SELECT ve.*, ch.name AS chamber FROM vote_event ve JOIN organization ch ON ch.id = ve.chamber_id WHERE ve.id = %s", (vote_id,), ).fetchone() if not vote: return None vote["positions"] = conn.execute( """SELECT o.short_name AS party, o.slug, o.color, vp.position FROM vote_position vp JOIN organization o ON o.id = vp.organization_id WHERE vp.vote_event_id = %s ORDER BY o.short_name""", (vote_id,), ).fetchall() vote["individual"] = conn.execute( """ SELECT p.slug, p.full_name, vp.position, o.short_name AS faction, (fp.position IS NOT NULL AND fp.position <> vp.position) AS dissent FROM vote_position vp JOIN person p ON p.id = vp.person_id LEFT JOIN organization o ON o.id = vp.party_id LEFT JOIN vote_position fp ON fp.vote_event_id = vp.vote_event_id AND fp.organization_id = vp.party_id WHERE vp.vote_event_id = %s ORDER BY o.short_name, p.family_name """, (vote_id,), ).fetchall() return vote