DROP MATERIALIZED VIEW IF EXISTS mv_election_results_cube CASCADE;

CREATE MATERIALIZED VIEW mv_election_results_cube AS

WITH base AS (
    SELECT
        e.election_id,
        e.name                                          AS election_name,
        e.election_date,
        EXTRACT(YEAR FROM e.election_date)::INT         AS election_year,
        et.type_name                                    AS election_type,
        wm.method_name                                  AS winner_method,
        cr.region_id                                    AS country_id,
        cr.name                                         AS country_name,
        pr.region_id                                    AS planning_region_id,
        pr.name                                         AS planning_region_name,
        r.region_id                                     AS municipality_id,
        r.name                                          AS municipality_name,
        pe.entity_id,
        pe.name                                         AS entity_name,
        pe.type                                         AS entity_type,
        vr.station_id,
        SUM(vr.votes)                                   AS votes
    FROM            vote_result      vr
    JOIN            election         e   ON e.election_id       = vr.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            polling_station  ps  ON ps.station_id       = vr.station_id
    JOIN            region           r   ON r.region_id         = ps.municipality_id
    LEFT JOIN       region           pr  ON pr.region_id        = r.parent_region_id
    LEFT JOIN       region           cr  ON cr.region_id        = pr.parent_region_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,
        EXTRACT(YEAR FROM e.election_date),
        et.type_name, wm.method_name,
        cr.region_id, cr.name,
        pr.region_id, pr.name,
        r.region_id, r.name,
        pe.entity_id, pe.name, pe.type,
        vr.station_id
)
SELECT
    election_id,
    election_name,
    election_date,
    election_year,
    election_type,
    winner_method,
    country_id,
    country_name,
    planning_region_id,
    planning_region_name,
    municipality_id,
    municipality_name,
    entity_id,
    entity_name,
    entity_type,
    SUM(votes)                                          AS total_votes,
    COUNT(DISTINCT station_id)                          AS stations_reporting,
    ROUND(
        SUM(votes) * 100.0 / NULLIF(
            SUM(SUM(votes)) OVER (
                PARTITION BY election_id, country_id,
                             planning_region_id, municipality_id
            ), 0
        ), 2
    )                                                   AS vote_share_pct,
    GROUPING(
        election_id, country_id,
        planning_region_id, municipality_id, entity_id
    )                                                   AS grouping_level
FROM base
GROUP BY ROLLUP (
    (election_id, election_name, election_date,
     election_year, election_type, winner_method),
    (country_id, country_name),
    (planning_region_id, planning_region_name),
    (municipality_id, municipality_name),
    (entity_id, entity_name, entity_type)
)
WITH DATA;

CREATE INDEX idx_cube1_election    ON mv_election_results_cube(election_id);
CREATE INDEX idx_cube1_entity      ON mv_election_results_cube(entity_id);
CREATE INDEX idx_cube1_country     ON mv_election_results_cube(country_id);
CREATE INDEX idx_cube1_region      ON mv_election_results_cube(planning_region_id);
CREATE INDEX idx_cube1_grouping    ON mv_election_results_cube(grouping_level);
CREATE INDEX idx_cube1_year        ON mv_election_results_cube(election_year);
CREATE INDEX idx_cube1_type        ON mv_election_results_cube(election_type);

CREATE UNIQUE INDEX idx_cube1_unique ON mv_election_results_cube(
    COALESCE(election_id, -1),
    COALESCE(country_id, -1),
    COALESCE(planning_region_id, -1),
    COALESCE(municipality_id, -1),
    COALESCE(entity_id, -1)
);



-- CUBE 2: mv_turnout_cube


DROP MATERIALIZED VIEW IF EXISTS mv_turnout_cube CASCADE;

CREATE MATERIALIZED VIEW mv_turnout_cube AS

WITH

registered AS (
    SELECT
        ps.municipality_id      AS region_id,
        COUNT(v.voter_id)       AS registered_voters
    FROM        voter           v
    JOIN        polling_station ps ON ps.station_id = v.station_id
    GROUP BY    ps.municipality_id
),

checkins AS (
    SELECT
        ps.municipality_id      AS region_id,
        ve.election_id,
        COUNT(ve.voter_election_id) AS voters_checked_in
    FROM        voter_election  ve
    JOIN        polling_station ps ON ps.station_id = ve.station_id
    GROUP BY    ps.municipality_id, ve.election_id
),

invalids AS (
    SELECT
        ps.municipality_id      AS region_id,
        b.election_id,
        COUNT(*)                AS invalid_ballots
    FROM        ballot          b
    JOIN        polling_station ps ON ps.station_id = b.station_id
    WHERE       b.is_valid = FALSE
    GROUP BY    ps.municipality_id, b.election_id
),

base AS (
    SELECT
        e.election_id,
        e.name                                          AS election_name,
        e.election_date,
        EXTRACT(YEAR FROM e.election_date)::INT         AS election_year,
        et.type_name                                    AS election_type,
        cr.region_id                                    AS country_id,
        cr.name                                         AS country_name,
        pr.region_id                                    AS planning_region_id,
        pr.name                                         AS planning_region_name,
        r.region_id                                     AS municipality_id,
        r.name                                          AS municipality_name,
        COALESCE(reg.registered_voters, 0)              AS registered_voters,
        COALESCE(c.voters_checked_in, 0)                AS voters_checked_in,
        COALESCE(inv.invalid_ballots, 0)                AS invalid_ballots
    FROM            election         e
    JOIN            election_type    et  ON et.election_type_id = e.election_type_id
    JOIN            region           r   ON r.region_id IN (
                                               SELECT DISTINCT municipality_id
                                               FROM polling_station
                                           )
    LEFT JOIN       region           pr  ON pr.region_id = r.parent_region_id
    LEFT JOIN       region           cr  ON cr.region_id = pr.parent_region_id
    LEFT JOIN       registered       reg ON reg.region_id = r.region_id
    LEFT JOIN       checkins         c   ON c.region_id   = r.region_id
                                        AND c.election_id = e.election_id
    LEFT JOIN       invalids         inv ON inv.region_id  = r.region_id
                                        AND inv.election_id = e.election_id
    WHERE           COALESCE(reg.registered_voters, 0) > 0
)

SELECT
    election_id,
    election_name,
    election_date,
    election_year,
    election_type,
    country_id,
    country_name,
    planning_region_id,
    planning_region_name,
    municipality_id,
    municipality_name,
    SUM(registered_voters)                              AS registered_voters,
    SUM(voters_checked_in)                              AS voters_checked_in,
    SUM(invalid_ballots)                                AS invalid_ballots,
    ROUND(
        SUM(voters_checked_in) * 100.0
        / NULLIF(SUM(registered_voters), 0), 2
    )                                                   AS turnout_pct,
    ROUND(
        SUM(invalid_ballots) * 100.0
        / NULLIF(SUM(voters_checked_in), 0), 2
    )                                                   AS invalid_pct,
    GROUPING(
        election_id, country_id,
        planning_region_id, municipality_id
    )                                                   AS grouping_level
FROM base
GROUP BY ROLLUP (
    (election_id, election_name, election_date,
     election_year, election_type),
    (country_id, country_name),
    (planning_region_id, planning_region_name),
    (municipality_id, municipality_name)
)
WITH DATA;

CREATE INDEX idx_cube2_election    ON mv_turnout_cube(election_id);
CREATE INDEX idx_cube2_country     ON mv_turnout_cube(country_id);
CREATE INDEX idx_cube2_region      ON mv_turnout_cube(planning_region_id);
CREATE INDEX idx_cube2_grouping    ON mv_turnout_cube(grouping_level);
CREATE INDEX idx_cube2_year        ON mv_turnout_cube(election_year);
CREATE INDEX idx_cube2_type        ON mv_turnout_cube(election_type);

CREATE UNIQUE INDEX idx_cube2_unique ON mv_turnout_cube(
    COALESCE(election_id, -1),
    COALESCE(country_id, -1),
    COALESCE(planning_region_id, -1),
    COALESCE(municipality_id, -1)
);


-- CUBE 3: mv_candidate_cube

DROP MATERIALIZED VIEW IF EXISTS mv_candidate_cube CASCADE;

CREATE MATERIALIZED VIEW mv_candidate_cube AS

WITH base AS (
    SELECT
        c.candidate_id,
        p.person_id,
        p.name                                          AS first_name,
        p.surname,
        p.gender,
        EXTRACT(YEAR FROM p.date_of_birth)::INT         AS birth_year,
        pp.party_id,
        pp.name                                         AS party_name,
        pp.abbreviation                                 AS party_abbreviation,
        e.election_id,
        e.name                                          AS election_name,
        e.election_date,
        EXTRACT(YEAR FROM e.election_date)::INT         AS election_year,
        et.type_name                                    AS election_type,
        cli.position                                    AS list_position
    FROM            candidate           c
    JOIN            person              p   ON p.person_id   = c.person_id
    JOIN            candidate_party     cp  ON cp.candidate_id = c.candidate_id
    JOIN            political_party     pp  ON pp.party_id   = cp.party_id
    JOIN            election            e   ON e.election_id  = cp.election_id
    JOIN            election_type       et  ON et.election_type_id = e.election_type_id
    LEFT JOIN       candidate_list_item cli ON cli.candidate_id = c.candidate_id
    LEFT JOIN       candidate_list      cl  ON cl.list_id     = cli.list_id
                                           AND cl.election_id = e.election_id
)

SELECT
    candidate_id,
    first_name,
    surname,
    gender,
    birth_year,
    party_id,
    party_name,
    party_abbreviation,
    election_id,
    election_name,
    election_date,
    election_year,
    election_type,
    COUNT(DISTINCT election_id)                         AS elections_contested,
    COUNT(DISTINCT party_id)                            AS parties_represented,
    MIN(list_position)                                  AS best_list_position,
    MAX(list_position)                                  AS worst_list_position,
    AVG(list_position)                                  AS avg_list_position,
    GROUPING(
        candidate_id, party_id, election_id
    )                                                   AS grouping_level
FROM base
GROUP BY ROLLUP (
    (candidate_id, first_name, surname, gender, birth_year),
    (party_id, party_name, party_abbreviation),
    (election_id, election_name, election_date,
     election_year, election_type)
)
WITH DATA;

CREATE INDEX idx_cube3_candidate   ON mv_candidate_cube(candidate_id);
CREATE INDEX idx_cube3_party       ON mv_candidate_cube(party_id);
CREATE INDEX idx_cube3_election    ON mv_candidate_cube(election_id);
CREATE INDEX idx_cube3_grouping    ON mv_candidate_cube(grouping_level);
CREATE INDEX idx_cube3_year        ON mv_candidate_cube(election_year);

CREATE UNIQUE INDEX idx_cube3_unique ON mv_candidate_cube(
    COALESCE(candidate_id, -1),
    COALESCE(party_id, -1),
    COALESCE(election_id, -1)
);


-- CUBE 4: mv_party_dominance_cube


DROP MATERIALIZED VIEW IF EXISTS mv_party_dominance_cube CASCADE;

CREATE MATERIALIZED VIEW mv_party_dominance_cube AS

WITH
party_votes AS (
    SELECT
        pp.party_id,
        pp.name                                         AS party_name,
        pp.abbreviation,
        pp.founded_year,
        pp.ideology,
        e.election_id,
        e.name                                          AS election_name,
        e.election_date,
        EXTRACT(YEAR FROM e.election_date)::INT         AS election_year,
        et.type_name                                    AS election_type,
        cr.region_id                                    AS country_id,
        cr.name                                         AS country_name,
        SUM(vr.votes)                                   AS total_votes
    FROM            vote_result      vr
    JOIN            political_entity pe  ON pe.entity_id    = vr.entity_id
    JOIN            political_party  pp  ON pp.party_id     = pe.party_id
    JOIN            election         e   ON e.election_id   = vr.election_id
    JOIN            election_type    et  ON et.election_type_id = e.election_type_id
    JOIN            polling_station  ps  ON ps.station_id   = vr.station_id
    JOIN            region           r   ON r.region_id     = ps.municipality_id
    LEFT JOIN       region           pr  ON pr.region_id    = r.parent_region_id
    LEFT JOIN       region           cr  ON cr.region_id    = pr.parent_region_id
    WHERE           vr.candidate_id IS NULL
    AND             pe.party_id IS NOT NULL
    GROUP BY
        pp.party_id, pp.name, pp.abbreviation,
        pp.founded_year, pp.ideology,
        e.election_id, e.name, e.election_date,
        EXTRACT(YEAR FROM e.election_date),
        et.type_name,
        cr.region_id, cr.name
),

-- Election totals for vote share calculation
election_totals AS (
    SELECT
        election_id,
        country_id,
        SUM(total_votes)                                AS election_total
    FROM party_votes
    GROUP BY election_id, country_id
)

SELECT
    pv.party_id,
    pv.party_name,
    pv.abbreviation,
    pv.founded_year,
    pv.ideology,
    pv.election_id,
    pv.election_name,
    pv.election_date,
    pv.election_year,
    pv.election_type,
    pv.country_id,
    pv.country_name,
    SUM(pv.total_votes)                                 AS total_votes,
    ROUND(
        SUM(pv.total_votes) * 100.0
        / NULLIF(MAX(et.election_total), 0), 2
    )                                                   AS vote_share_pct,
    RANK() OVER (
        PARTITION BY pv.election_id, pv.country_id
        ORDER BY SUM(pv.total_votes) DESC
    )                                                   AS rank_in_election,
    GROUPING(
        pv.party_id, pv.election_id, pv.country_id
    )                                                   AS grouping_level
FROM            party_votes     pv
JOIN            election_totals et  ON et.election_id = pv.election_id
                                   AND et.country_id  = pv.country_id
GROUP BY ROLLUP (
    (pv.party_id, pv.party_name, pv.abbreviation,
     pv.founded_year, pv.ideology),
    (pv.election_id, pv.election_name, pv.election_date,
     pv.election_year, pv.election_type),
    (pv.country_id, pv.country_name)
)
WITH DATA;

CREATE INDEX idx_cube4_party       ON mv_party_dominance_cube(party_id);
CREATE INDEX idx_cube4_election    ON mv_party_dominance_cube(election_id);
CREATE INDEX idx_cube4_country     ON mv_party_dominance_cube(country_id);
CREATE INDEX idx_cube4_grouping    ON mv_party_dominance_cube(grouping_level);
CREATE INDEX idx_cube4_year        ON mv_party_dominance_cube(election_year);
CREATE INDEX idx_cube4_rank        ON mv_party_dominance_cube(rank_in_election);

CREATE UNIQUE INDEX idx_cube4_unique ON mv_party_dominance_cube(
    COALESCE(party_id, -1),
    COALESCE(election_id, -1),
    COALESCE(country_id, -1)
);



-- CUBE 5: mv_electoral_district_cube

DROP MATERIALIZED VIEW IF EXISTS mv_electoral_district_cube CASCADE;

CREATE MATERIALIZED VIEW mv_electoral_district_cube AS

WITH

district_votes AS (
    SELECT
        ed.district_id,
        ed.election_id,
        ed.region_id,
        ed.seats_available,
        ep.entity_id,
        SUM(vr.votes)                                   AS entity_votes
    FROM            electoral_district  ed
    JOIN            election_participant ep  ON ep.district_id  = ed.district_id
    JOIN            vote_result         vr  ON vr.election_id   = ed.election_id
                                           AND vr.entity_id     = ep.entity_id
    JOIN            polling_station     ps  ON ps.station_id    = vr.station_id
    JOIN            region              r   ON r.region_id      = ps.municipality_id
    WHERE           vr.candidate_id IS NULL
    AND (
        r.region_id           = ed.region_id
        OR r.parent_region_id = ed.region_id
    )
    GROUP BY
        ed.district_id, ed.election_id,
        ed.region_id, ed.seats_available,
        ep.entity_id
),

district_totals AS (
    SELECT
        district_id,
        SUM(entity_votes)               AS district_total,
        COUNT(DISTINCT entity_id)       AS entities_competing
    FROM district_votes
    GROUP BY district_id
),

eligible AS (
    SELECT
        dv.*,
        dt.district_total,
        dt.entities_competing,
        ROUND(dv.entity_votes * 100.0
              / NULLIF(dt.district_total, 0), 2)        AS vote_share_pct
    FROM   district_votes  dv
    JOIN   district_totals dt ON dt.district_id = dv.district_id
    WHERE  dv.entity_votes * 100.0
           / NULLIF(dt.district_total, 0) >= 5.0
),

quotients AS (
    SELECT
        e.*,
        gs.divisor,
        e.entity_votes::NUMERIC / gs.divisor            AS quotient
    FROM       eligible e
    CROSS JOIN generate_series(1, COALESCE(e.seats_available, 1)) AS gs(divisor)
),

ranked AS (
    SELECT
        *,
        RANK() OVER (
            PARTITION BY district_id
            ORDER BY     quotient DESC
        )                                               AS quotient_rank
    FROM quotients
),

seats_calc AS (
    SELECT
        district_id,
        election_id,
        region_id,
        seats_available,
        entity_id,
        entity_votes,
        district_total,
        entities_competing,
        vote_share_pct,
        COUNT(*) FILTER (
            WHERE quotient_rank <= seats_available
        )                                               AS seats_won
    FROM   ranked
    GROUP BY
        district_id, election_id, region_id,
        seats_available, entity_id,
        entity_votes, district_total,
        entities_competing, vote_share_pct
)

SELECT
    s.election_id,
    e.name                                              AS election_name,
    e.election_date,
    EXTRACT(YEAR FROM e.election_date)::INT             AS election_year,
    et.type_name                                        AS election_type,
    s.district_id,
    r.name                                              AS district_name,
    pr.name                                             AS parent_region_name,
    MAX(s.seats_available)                              AS seats_available,
    s.entity_id,
    pe.name                                             AS entity_name,
    pe.type                                             AS entity_type,
    SUM(s.entity_votes)                                 AS total_votes,
    SUM(s.district_total)                               AS total_district_votes,
    ROUND(SUM(s.entity_votes) * 100.0
          / NULLIF(SUM(s.district_total), 0), 2)        AS vote_share_pct,
    SUM(s.seats_won)                                    AS seats_won,
    MAX(s.entities_competing)                           AS entities_competing,
    GROUPING(s.election_id, s.district_id, s.entity_id) AS grouping_level
FROM            seats_calc      s
JOIN            election        e   ON e.election_id       = s.election_id
JOIN            election_type   et  ON et.election_type_id = e.election_type_id
JOIN            region          r   ON r.region_id         = s.region_id
LEFT JOIN       region          pr  ON pr.region_id        = r.parent_region_id
JOIN            political_entity pe ON pe.entity_id        = s.entity_id
GROUP BY ROLLUP (
    (s.election_id, e.name, e.election_date,
     EXTRACT(YEAR FROM e.election_date), et.type_name),
    (s.district_id, r.name, pr.name),
    (s.entity_id, pe.name, pe.type)
)
WITH DATA;

CREATE INDEX idx_cube5_election ON mv_electoral_district_cube(election_id);
CREATE INDEX idx_cube5_district ON mv_electoral_district_cube(district_id);
CREATE INDEX idx_cube5_entity   ON mv_electoral_district_cube(entity_id);
CREATE INDEX idx_cube5_grouping ON mv_electoral_district_cube(grouping_level);
CREATE INDEX idx_cube5_seats    ON mv_electoral_district_cube(seats_won);

CREATE UNIQUE INDEX idx_cube5_unique ON mv_electoral_district_cube(
    COALESCE(election_id, -1),
    COALESCE(district_id, -1),
    COALESCE(entity_id,   -1)
);