DatabaseCreation: views.sql

--  1. vw_election_results


CREATE OR REPLACE VIEW public.vw_election_results AS
WITH election_totals AS (
    SELECT vote_result.election_id,
           sum(vote_result.votes) AS total_valid_votes
    FROM vote_result
    GROUP BY vote_result.election_id
),
entity_votes AS (
    SELECT vote_result.election_id,
           vote_result.entity_id,
           sum(vote_result.votes) AS total_votes
    FROM vote_result
    WHERE vote_result.candidate_id IS NULL
    GROUP BY vote_result.election_id, vote_result.entity_id
),
calc AS (
    SELECT ev.election_id,
           ev.entity_id,
           ev.total_votes,
           el.total_valid_votes,
           ev.total_votes::numeric * 100.0 / NULLIF(el.total_valid_votes, 0)::numeric AS vote_pct
    FROM entity_votes ev
    JOIN election_totals el USING (election_id)
)
SELECT e.election_id,
       e.name                            AS election_name,
       e.election_date,
       et.type_name                      AS election_type,
       pe.entity_id,
       pe.name                           AS entity_name,
       pe.type                           AS entity_type,
       c.total_votes,
       c.total_valid_votes,
       round(c.vote_pct, 2)              AS vote_share_pct,
       c.vote_pct >= 5.0                 AS passed_threshold,
       wm.method_name                    AS winner_method
FROM calc c
JOIN election          e  USING (election_id)
JOIN election_type     et ON et.election_type_id = e.election_type_id
JOIN winner_method     wm ON wm.method_id        = e.winner_method_id
JOIN political_entity  pe USING (entity_id);


--  2. vw_dhondt_seat_allocation


CREATE OR REPLACE VIEW public.vw_dhondt_seat_allocation AS
WITH district_votes AS (
    SELECT ed.district_id, ed.election_id, ed.region_id, ed.seats_available,
           vr.entity_id, sum(vr.votes) AS district_votes
    FROM electoral_district ed
    JOIN election         e_1 ON e_1.election_id = ed.election_id
    JOIN vote_result       vr  ON vr.election_id  = ed.election_id
    JOIN polling_station   ps  ON ps.station_id   = vr.station_id
    JOIN region            r2  ON r2.region_id    = ps.municipality_id
    WHERE e_1.winner_method_id = 8
      AND vr.candidate_id IS NULL
      AND (r2.region_id = ed.region_id OR r2.parent_region_id = ed.region_id)
    GROUP BY ed.district_id, ed.election_id, ed.region_id, ed.seats_available, vr.entity_id
),
district_totals AS (
    SELECT district_id, sum(district_votes) AS total_district_votes FROM district_votes GROUP BY district_id
),
eligible AS (
    SELECT dv.*, dt.total_district_votes,
           dv.district_votes::numeric * 100.0 / NULLIF(dt.total_district_votes, 0::numeric) AS vote_pct
    FROM district_votes dv JOIN district_totals dt USING (district_id)
    WHERE (dv.district_votes::numeric * 100.0 / NULLIF(dt.total_district_votes, 0::numeric)) >= 5.0
),
dhondt AS (
    SELECT e_1.*, d.divisor,
           e_1.district_votes::numeric / d.divisor::numeric AS quotient
    FROM eligible e_1 CROSS JOIN LATERAL generate_series(1, e_1.seats_available) d(divisor)
),
ranked AS (
    SELECT *, rank() OVER (PARTITION BY district_id ORDER BY quotient DESC) AS rn FROM dhondt
),
seats AS (
    SELECT district_id, election_id, region_id, seats_available, entity_id,
           district_votes, total_district_votes, vote_pct,
           count(*) FILTER (WHERE rn <= seats_available) AS seats_won
    FROM ranked
    GROUP BY district_id, election_id, region_id, seats_available, entity_id,
             district_votes, total_district_votes, vote_pct
)
SELECT s.election_id, e.name AS election_name, e.election_date,
       s.district_id, r.name AS region_name, s.seats_available,
       s.entity_id, pe.name AS entity_name, pe.type AS entity_type,
       s.district_votes AS total_votes, s.total_district_votes,
       round(s.vote_pct, 2) AS vote_share_pct, s.seats_won
FROM seats s
JOIN election        e  USING (election_id)
JOIN region          r  ON r.region_id  = s.region_id
JOIN political_entity pe USING (entity_id)
ORDER BY s.election_id, s.district_id, s.seats_won DESC;


--  3. vw_invalid_ballot_analysis


CREATE OR REPLACE VIEW public.vw_invalid_ballot_analysis AS
SELECT e.election_id,
       e.name                                                           AS election_name,
       r.region_id,
       r.name                                                           AS region_name,
       count(*)                                                         AS total_ballots,
       count(*) FILTER (WHERE NOT b.is_valid)                          AS invalid_ballots,
       round(count(*) FILTER (WHERE NOT b.is_valid)::numeric * 100.0
             / NULLIF(count(*), 0)::numeric, 2)                        AS invalid_pct
FROM ballot           b
JOIN election         e  ON e.election_id    = b.election_id
JOIN polling_station  ps ON ps.station_id    = b.station_id
JOIN region           r  ON r.region_id      = ps.municipality_id
GROUP BY e.election_id, e.name, r.region_id, r.name;


--  4. vw_local_election_winners


CREATE OR REPLACE VIEW public.vw_local_election_winners AS
WITH totalvotesperparty AS (
    SELECT vr.election_id, e.name AS election_name, r.name AS municipality_name,
           pe.name AS party_name, sum(vr.votes) AS total_sum_votes
    FROM vote_result      vr
    JOIN election         e  ON vr.election_id = e.election_id
    JOIN political_entity pe ON vr.entity_id   = pe.entity_id
    JOIN polling_station  ps ON vr.station_id  = ps.station_id
    JOIN region           r  ON ps.municipality_id = r.region_id
    WHERE e.election_type_id = 3
    GROUP BY vr.election_id, e.name, r.name, pe.name
),
rankedwinners AS (
    SELECT *, rank() OVER (PARTITION BY election_id, municipality_name ORDER BY total_sum_votes DESC) AS rnk
    FROM totalvotesperparty
)
SELECT election_name, municipality_name,
       party_name           AS winner_name,
       total_sum_votes       AS votes_received,
       'Winner'::text        AS status
FROM rankedwinners
WHERE rnk = 1;


--  5. vw_parliamentary_municipality_winners


CREATE OR REPLACE VIEW public.vw_parliamentary_municipality_winners AS
WITH aggregatedvotes AS (
    SELECT e.election_id, e.name AS election_name, r.name AS municipality_name,
           pe.name AS party_name, sum(vr.votes) AS total_votes
    FROM vote_result      vr
    JOIN election         e  ON vr.election_id    = e.election_id
    JOIN political_entity pe ON vr.entity_id      = pe.entity_id
    JOIN polling_station  ps ON vr.station_id     = ps.station_id
    JOIN region           r  ON ps.municipality_id = r.region_id
    WHERE e.election_type_id = 2
    GROUP BY e.election_id, e.name, r.name, pe.name
),
rankedwinners AS (
    SELECT *, rank() OVER (PARTITION BY election_id, municipality_name ORDER BY total_votes DESC) AS pos
    FROM aggregatedvotes
)
SELECT election_name, municipality_name,
       party_name          AS dominant_party,
       total_votes         AS winning_votes,
       'Municipality Winner'::text AS status
FROM rankedwinners
WHERE pos = 1;


--  6. vw_party_demographic_performance


CREATE OR REPLACE VIEW public.vw_party_demographic_performance AS
WITH VoterDemographics AS (
    
    SELECT 
        ve.election_id,
        ve.station_id,
        p.date_of_birth,
        EXTRACT(YEAR FROM p.date_of_birth) as birth_year
    FROM public.voter_election ve
    JOIN public.voter v ON ve.voter_id = v.voter_id
    JOIN public.person p ON v.person_id = p.person_id
),
BallotStats AS (
   
    SELECT 
        b.election_id,
        b.station_id,
        b.entity_id,
        b.ballot_id
    FROM public.ballot b
    WHERE b.is_valid = true
)
SELECT 
    e.name AS election_name,
    r.name AS region_name,
    pe.name AS party_name,
    ps.name AS station_name,
   
    COALESCE(COUNT(CASE WHEN vd.birth_year >= 1996 THEN 1 END), 0) AS gen_z_votes,
    COALESCE(COUNT(CASE WHEN vd.birth_year >= 1981 AND vd.birth_year <= 1995 THEN 1 END), 0) AS millennial_votes,
    COALESCE(COUNT(CASE WHEN vd.birth_year < 1981 THEN 1 END), 0) AS senior_votes,
    COUNT(bs.ballot_id) AS total_valid_votes
FROM BallotStats bs
JOIN public.election e ON bs.election_id = e.election_id
JOIN public.polling_station ps ON bs.station_id = ps.station_id
JOIN public.region r ON ps.municipality_id = r.region_id
JOIN public.political_entity pe ON bs.entity_id = pe.entity_id

LEFT JOIN VoterDemographics vd ON bs.election_id = vd.election_id 
                             AND bs.station_id = vd.station_id
GROUP BY e.name, r.name, pe.name, ps.name;


--  7. vw_party_performance_over_time


CREATE OR REPLACE VIEW public.vw_party_performance_over_time AS
WITH party_votes AS (
    SELECT e.election_id, e.name AS election_name, e.election_date,
           pe.entity_id, pe.name AS entity_name, sum(vr.votes) AS total_votes
    FROM vote_result       vr
    JOIN election          e  ON e.election_id  = vr.election_id
    JOIN political_entity  pe ON pe.entity_id   = vr.entity_id
    WHERE vr.candidate_id IS NULL
    GROUP BY e.election_id, e.name, e.election_date, pe.entity_id, pe.name
),
totals AS (
    SELECT election_id, sum(total_votes) AS total_election_votes FROM party_votes GROUP BY election_id
)
SELECT pv.election_id, pv.election_name, pv.election_date,
       pv.entity_id, pv.entity_name, pv.total_votes, t.total_election_votes,
       round(pv.total_votes::numeric * 100.0 / NULLIF(t.total_election_votes, 0::numeric), 2) AS vote_share_pct
FROM party_votes pv JOIN totals t USING (election_id)
ORDER BY pv.entity_name, pv.election_date;


--  8. vw_polling_station_stats


CREATE OR REPLACE VIEW public.vw_polling_station_stats AS
SELECT e.election_id,
       e.name                                                      AS election_name,
       ps.station_id,
       ps.name                                                     AS station_name,
       count(DISTINCT v.voter_id)                                  AS registered_voters,
       count(b.ballot_id)                                          AS ballots_cast,
       count(b.ballot_id) FILTER (WHERE b.is_valid)               AS valid_ballots,
       count(b.ballot_id) FILTER (WHERE NOT b.is_valid)           AS invalid_ballots,
       round(count(b.ballot_id)::numeric * 100.0
             / NULLIF(count(DISTINCT v.voter_id), 0)::numeric, 2) AS turnout_pct
FROM polling_station  ps
JOIN station_election se ON se.station_id  = ps.station_id
JOIN election         e  ON e.election_id  = se.election_id
LEFT JOIN ballot      b  ON b.station_id   = ps.station_id AND b.election_id = e.election_id
LEFT JOIN voter       v  ON v.station_id   = ps.station_id
GROUP BY e.election_id, e.name, ps.station_id, ps.name;


--  9. vw_polling_station_voter_count


CREATE OR REPLACE VIEW public.vw_polling_station_voter_count AS
SELECT r.name  AS region_name,
       ps.name AS station_name,
       count(v.voter_id) AS actual_registered_voters
FROM polling_station  ps
JOIN region           r  ON ps.municipality_id = r.region_id
LEFT JOIN voter       v  ON ps.station_id      = v.station_id
GROUP BY r.name, ps.name;


--  10. vw_presidential_by_municipality


CREATE OR REPLACE VIEW public.vw_presidential_by_municipality AS
WITH cityvotes AS (
    SELECT e.name AS election_name, r.name AS municipality_name,
           pe.name AS candidate_name, sum(vr.votes) AS total_votes
    FROM vote_result      vr
    JOIN election         e  ON vr.election_id    = e.election_id
    JOIN political_entity pe ON vr.entity_id      = pe.entity_id
    JOIN polling_station  ps ON vr.station_id     = ps.station_id
    JOIN region           r  ON ps.municipality_id = r.region_id
    WHERE e.election_type_id = 1
    GROUP BY e.name, r.name, pe.name
),
rankedcities AS (
    SELECT *, rank() OVER (PARTITION BY election_name, municipality_name ORDER BY total_votes DESC) AS pos
    FROM cityvotes
)
SELECT election_name, municipality_name, candidate_name,
       total_votes, 'City Winner'::text AS level
FROM rankedcities
WHERE pos = 1;


--  11. vw_regional_voting_patterns


CREATE OR REPLACE VIEW public.vw_regional_voting_patterns AS
WITH regionhierarchy AS (
    SELECT ps.station_id,
           r.name        AS municipality_name,
           parent.name   AS sub_region_name,
           grandparent.name AS country_name
    FROM polling_station ps
    JOIN region      r           ON ps.municipality_id     = r.region_id
    LEFT JOIN region parent      ON r.parent_region_id     = parent.region_id
    LEFT JOIN region grandparent ON parent.parent_region_id = grandparent.region_id
),
candidatevotes AS (
    SELECT e.election_id, e.name AS election_name, e.election_type_id,
           pe.name AS candidate_name,
           CASE WHEN e.election_type_id = 1 THEN rh.country_name ELSE rh.municipality_name END AS final_region,
           sum(vr.votes) AS total_votes_sum
    FROM vote_result      vr
    JOIN election         e  ON vr.election_id = e.election_id
    JOIN political_entity pe ON vr.entity_id   = pe.entity_id
    JOIN regionhierarchy  rh ON vr.station_id  = rh.station_id
    GROUP BY e.election_id, e.name, e.election_type_id, pe.name,
             CASE WHEN e.election_type_id = 1 THEN rh.country_name ELSE rh.municipality_name END
),
rankedwinners AS (
    SELECT *, rank() OVER (PARTITION BY election_id, final_region ORDER BY total_votes_sum DESC) AS rnk
    FROM candidatevotes
)
SELECT election_name, final_region AS region_name,
       candidate_name AS dominant_entity,
       total_votes_sum AS winning_votes,
       'Winner'::text AS status
FROM rankedwinners
WHERE rnk = 1 AND final_region IS NOT NULL;


--  12. vw_voter_turnout


CREATE OR REPLACE VIEW public.vw_voter_turnout AS
WITH registered AS (
    SELECT se.election_id, ps.municipality_id AS region_id,
           sum(ps.registered_voter) AS registered_voters
    FROM station_election se JOIN polling_station ps ON ps.station_id = se.station_id
    GROUP BY se.election_id, ps.municipality_id
),
ballots AS (
    SELECT b.election_id, ps.municipality_id AS region_id,
           count(*) AS ballots_cast,
           count(*) FILTER (WHERE b.is_valid)      AS valid_ballots,
           count(*) FILTER (WHERE NOT b.is_valid)  AS invalid_ballots
    FROM ballot b JOIN polling_station ps ON ps.station_id = b.station_id
    GROUP BY b.election_id, ps.municipality_id
),
combined AS (
    SELECT COALESCE(r.election_id, b.election_id) AS election_id,
           COALESCE(r.region_id,   b.region_id)   AS region_id,
           r.registered_voters,
           b.ballots_cast, b.valid_ballots, b.invalid_ballots,
           b.ballots_cast::numeric * 100.0 / NULLIF(r.registered_voters, 0)::numeric AS turnout_pct_raw,
           b.invalid_ballots::numeric * 100.0 / NULLIF(b.ballots_cast, 0)::numeric   AS invalid_pct_raw
    FROM registered r FULL JOIN ballots b ON r.election_id = b.election_id AND r.region_id = b.region_id
)
SELECT e.election_id, e.name AS election_name, e.election_date,
       et.type_name AS election_type,
       c.region_id, rg.name AS region_name,
       COALESCE(c.registered_voters, 0::bigint) AS registered_voters,
       COALESCE(c.ballots_cast,      0::bigint) AS ballots_cast,
       COALESCE(c.valid_ballots,     0::bigint) AS valid_ballots,
       COALESCE(c.invalid_ballots,   0::bigint) AS invalid_ballots,
       round(c.turnout_pct_raw, 2) AS turnout_pct,
       round(c.invalid_pct_raw, 2) AS invalid_pct
FROM combined c
JOIN election      e  USING (election_id)
JOIN election_type et ON et.election_type_id = e.election_type_id
JOIN region        rg ON rg.region_id        = c.region_id
WHERE c.registered_voters IS NOT NULL OR c.ballots_cast IS NOT NULL
ORDER BY e.election_date, round(c.turnout_pct_raw, 2) DESC;