= Креирање и пополнување на базата

=== DDL скрипта за бришење и повторно креирање на табелите
[[html(<a href="https://develop.finki.ukim.mk/projects/EDB/attachment/wiki/DatabaseCreation/ddl.sql">ddl.sql</a>)]]

=== DML (seed) скрипта за пополнување на податоците
[[html(<a href="https://develop.finki.ukim.mk/projects/EDB/attachment/wiki/DatabaseCreation/dml.sql">dml.sql</a>)]]

== Погледи (Views)
[[html(<a href="https://develop.finki.ukim.mk/projects/EDB/attachment/wiki/DatabaseCreation/views.sql">views.sql</a>)]]

=== vw_election_results

Го прикажува збирниот изборен резултат по политички ентитет за секој избор — вклучувајќи вкупен број гласови, процент, и дали ентитетот го поминал 5% цензусот. Ја имплементира бизнис логиката за јавно објавување на официјални изборни резултати и овозможува брза споредба на перформансите на партиите и коалициите по избор.

{{{
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);
}}}

----

=== vw_dhondt_seat_allocation

Ја пресметува распределбата на парламентарни мандати по изборна единица користејќи D'Hondt метод, со вграден 5% праг и поддршка за коалиции. Ја имплементира законски задолжителната пропорционална распределба на мандати за парламентарни избори и овозможува транспарентен увид во тоа колку мандати освоил секој ентитет по регион.

{{{
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;
}}}

----

=== 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;
}}}

----

=== vw_local_election_winners

Го прикажува победникот на локалните избори (тип 3) по општина — партијата со најмногу гласови во секоја општина. Ја имплементира бизнис логиката за прогласување победник на локалните избори по принцип на релативно мнозинство, и овозможува брз преглед на политичката карта по општини.

{{{
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;
}}}

----

=== vw_parliamentary_municipality_winners

Го прикажува доминантниот политички ентитет по општина за парламентарни избори (тип 2), врз основа на агрегирани гласови по гласачко место. Ја имплементира логиката за визуелизација на изборната карта на парламентарни избори — корисна за анализа на регионалната доминација на партиите и медиумско известување.

{{{
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;
}}}

----

=== vw_party_demographic_performance

Ги прикажува гласовите по политички ентитет и гласачко место разбиени по генерациски кохорти: Gen Z (роден ≥1996), Милениалци (1981–1995) и Сениори (<1981). Ја имплементира логиката за демографска анализа на изборна поддршка, овозможувајќи партиите и истражувачите да ја разберат старосната структура на гласачкото тело по регион.

{{{
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;
}}}

----

=== 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;
}}}

----

=== 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;
}}}

----

=== vw_polling_station_voter_count

Прикажува точен број на регистрирани гласачи по гласачко место и регион, пресметан директно од табелата `voter`. Служи за верификација на конзистентноста на податоците — споредувајќи го овој поглед со полето `registered_voter` во `polling_station` може да се откријат евентуални несогласувања во бројачите.

{{{
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;
}}}

----

=== vw_presidential_by_municipality

Го прикажува водечкиот кандидат на претседателски избори (тип 1) по општина, врз основа на вкупни гласови. Ја имплементира логиката за регионална анализа на претседателски избори — овозможува визуелизација на изборната карта и увид во тоа кој кандидат доминира во кои општини.

{{{
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;
}}}

----

=== 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;
}}}

----

=== vw_voter_turnout

Дава сеопфатна статистика за излезност на гласачи по избор и општина — регистрирани гласачи, фрлени ливчиња, валидни, невалидни, процент на излезност и процент на невалидни. Ја имплементира логиката за официјално известување за излезност, задолжително за Државната изборна комисија, со поддршка за `FULL JOIN` за да се прикажат и општини без гласови.

{{{
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;
}}}