= Фаза 4 — Функции, Процедури, Тригери

== Тригери

=== 1. trg_validate_ballot

**Тип:** `BEFORE INSERT` на табела `ballot`

Се активира пред секое внесување на гласачко ливче и ги проверува три услови: дали изборот е активен (`status=1`), дали гласачкото место е назначено на тој избор, и дали `entity_id` учествувал во изборот. Ја имплементира бизнис логиката за интегритет на гласање — спречува невалидни или фалсификувани ливчиња да влезат во системот.

{{{
CREATE OR REPLACE FUNCTION public.fn_validate_ballot()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_status        SMALLINT;
    v_station_ok    BOOLEAN;
    v_entity_ok     BOOLEAN;
BEGIN

    -- Check (a): election must be active
    SELECT status
    INTO   v_status
    FROM   election
    WHERE  election_id = NEW.election_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION
            'Election % does not exist', NEW.election_id;
    END IF;

    IF v_status <> 1 THEN
        RAISE EXCEPTION
            'Election % is not active (status=%). Ballots cannot be cast.',
            NEW.election_id, v_status;
    END IF;

    -- Check (b): station must be in this election
    SELECT EXISTS (
        SELECT 1
        FROM   station_election
        WHERE  station_id  = NEW.station_id
        AND    election_id = NEW.election_id
    ) INTO v_station_ok;

    IF NOT v_station_ok THEN
        RAISE EXCEPTION
            'Polling station % is not assigned to election %.',
            NEW.station_id, NEW.election_id;
    END IF;

    -- Check (c/d): entity validation for valid ballots
    IF NEW.is_valid = TRUE THEN

        IF NEW.entity_id IS NULL AND NEW.candidate_id IS NULL THEN
            RAISE EXCEPTION
                'A valid ballot must have either an entity_id or candidate_id.';
        END IF;

        IF NEW.entity_id IS NOT NULL THEN
            SELECT EXISTS (
                SELECT 1
                FROM   election_participant ep
                JOIN   electoral_district   ed
                       ON ed.district_id  = ep.district_id
                WHERE  ep.entity_id   = NEW.entity_id
                AND    ed.election_id  = NEW.election_id
            ) INTO v_entity_ok;

            IF NOT v_entity_ok THEN
                RAISE EXCEPTION
                    'Entity % did not participate in election %.',
                    NEW.entity_id, NEW.election_id;
            END IF;
        END IF;

    END IF;

    RETURN NEW;

END;
$function$;

-- Trigger definition
CREATE TRIGGER trg_validate_ballot
BEFORE INSERT ON public.ballot
FOR EACH ROW EXECUTE FUNCTION fn_validate_ballot();
}}}

----

=== 2. trg_after_insert_ballot_live_count

**Тип:** `AFTER INSERT` на табела `ballot`

Се активира по секое внесување на валидно ливче и автоматски го ажурира бројачот во `vote_result` преку UPSERT — ако записот постои го зголемува за 1, инаку создава нов. Ја имплементира логиката за live бројање на гласови во реално време, без потреба од рачна агрегација на резултати.

{{{
CREATE OR REPLACE FUNCTION public.fn_trigger_live_vote_counter()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_result_id_exists BIGINT;
    v_max_id           BIGINT;
BEGIN
    IF NEW.is_valid = TRUE THEN

        SELECT result_id INTO v_result_id_exists
        FROM public.vote_result
        WHERE election_id  = NEW.election_id
          AND station_id   = NEW.station_id
          AND entity_id    = NEW.entity_id
          AND (candidate_id = NEW.candidate_id
               OR (candidate_id IS NULL AND NEW.candidate_id IS NULL));

        IF v_result_id_exists IS NOT NULL THEN
            UPDATE public.vote_result
            SET votes = votes + 1
            WHERE result_id = v_result_id_exists;
        ELSE
            SELECT COALESCE(MAX(result_id), 0) + 1
            INTO   v_max_id
            FROM   public.vote_result;

            INSERT INTO public.vote_result
                (result_id, election_id, station_id, entity_id, candidate_id, votes)
            VALUES
                (v_max_id, NEW.election_id, NEW.station_id, NEW.entity_id, NEW.candidate_id, 1);
        END IF;

    END IF;

    RETURN NEW;
END;
$function$;

-- Trigger definition
CREATE TRIGGER trg_after_insert_ballot_live_count
AFTER INSERT ON public.ballot
FOR EACH ROW EXECUTE FUNCTION fn_trigger_live_vote_counter();
}}}

----

=== 3. trg_before_insert_voter_age

**Тип:** `BEFORE INSERT` на табела `voter`

Се активира пред регистрација на нов гласач и проверува дали лицето е полнолетно (≥18 години), фрлајќи детален исклучок со името на лицето ако не е. Директно ја имплементира законската забрана за гласање на малолетни лица на ниво на базата.

{{{
CREATE OR REPLACE FUNCTION public.fn_check_voter_age_limit()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_birth_date DATE;
    v_full_name  TEXT;
BEGIN
    SELECT date_of_birth, (name || ' ' || surname)
    INTO   v_birth_date, v_full_name
    FROM   public.person
    WHERE  person_id = NEW.person_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION
            'Грешка: Лицето со ID % не постои во матичната евиденција (табела person).',
            NEW.person_id;
    END IF;

    IF v_birth_date > CURRENT_DATE - INTERVAL '18 years' THEN
        RAISE EXCEPTION
            'Критична грешка: Лицето "%" (ID: %) има % години и е малолетно. '
            'Регистрацијата во базата на гласачи е ОДБИЕНА.',
            v_full_name,
            NEW.person_id,
            EXTRACT(YEAR FROM AGE(CURRENT_DATE, v_birth_date));
    END IF;

    RETURN NEW;
END;
$function$;

-- Trigger definition
CREATE TRIGGER trg_before_insert_voter_age
BEFORE INSERT ON public.voter
FOR EACH ROW EXECUTE FUNCTION fn_check_voter_age_limit();
}}}

----

=== 4. trg_after_voter_change_sync_stats

**Тип:** `AFTER INSERT OR DELETE` на табела `voter`

Автоматски го ажурира `registered_voter` бројачот во `polling_station` при секоја промена на гласачкиот список — +1 при регистрација, -1 при бришење. Ја одржува конзистентноста на статистиките за регистрирани гласачи без рачно одржување.

{{{
CREATE OR REPLACE FUNCTION public.fn_trigger_sync_registered_voters()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE public.polling_station
        SET    registered_voter = COALESCE(registered_voter, 0) + 1
        WHERE  station_id = NEW.station_id;

    ELSIF TG_OP = 'DELETE' THEN
        UPDATE public.polling_station
        SET    registered_voter = COALESCE(registered_voter, 0) - 1
        WHERE  station_id = OLD.station_id;
    END IF;

    RETURN NULL;
END;
$function$;

-- Trigger definition
CREATE TRIGGER trg_after_voter_change_sync_stats
AFTER INSERT OR DELETE ON public.voter
FOR EACH ROW EXECUTE FUNCTION fn_trigger_sync_registered_voters();
}}}

----

=== 5. trg_prevent_duplicate_voter_checkin

**Тип:** `BEFORE INSERT` на табела `voter_election`

Проверува три услови: изборот е активен, гласачот не се пријавил веќе на тој избор, и check-in е на неговата матична станица. Ја имплементира основната изборна контрола против двојно гласање и гласање надвор од матичното место.

{{{
CREATE OR REPLACE FUNCTION public.fn_prevent_duplicate_voter_checkin()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_registered_station BIGINT;
    v_election_status    SMALLINT;
    v_election_name      VARCHAR;
    v_voter_name         TEXT;
BEGIN
    -- (1) Изборот мора да биде активен
    SELECT e.status, e.name
    INTO   v_election_status, v_election_name
    FROM   election e
    WHERE  e.election_id = NEW.election_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Election % does not exist.', NEW.election_id;
    END IF;

    IF v_election_status <> 1 THEN
        RAISE EXCEPTION
            'Election "%" is not active. Voter check-in is not allowed.',
            v_election_name;
    END IF;

    -- (2) Гласачот не смее двапати да се пријави
    IF EXISTS (
        SELECT 1
        FROM   voter_election ve
        WHERE  ve.voter_id    = NEW.voter_id
        AND    ve.election_id = NEW.election_id
    ) THEN
        SELECT (p.name || ' ' || p.surname)
        INTO   v_voter_name
        FROM   voter  v
        JOIN   person p ON p.person_id = v.person_id
        WHERE  v.voter_id = NEW.voter_id;

        RAISE EXCEPTION
            'Voter "%" (id: %) has already checked in for election "%".',
            v_voter_name, NEW.voter_id, v_election_name;
    END IF;

    -- (3) Check-in мора да биде на матичната станица
    SELECT v.station_id
    INTO   v_registered_station
    FROM   voter v
    WHERE  v.voter_id = NEW.voter_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Voter % does not exist.', NEW.voter_id;
    END IF;

    IF v_registered_station <> NEW.station_id THEN
        RAISE EXCEPTION
            'Voter % must check in at their registered station (%). '
            'Attempted check-in at station %.',
            NEW.voter_id, v_registered_station, NEW.station_id;
    END IF;

    RETURN NEW;
END;
$function$;

-- Trigger definition
CREATE TRIGGER trg_prevent_duplicate_voter_checkin
BEFORE INSERT ON public.voter_election
FOR EACH ROW EXECUTE FUNCTION fn_prevent_duplicate_voter_checkin();
}}}

----

== Функции

=== 6. fn_calculate_dhondt

**Сигнатура:** `fn_calculate_dhondt(p_election_id bigint, p_district_id bigint)`

**Враќа:** `TABLE(round, entity_id, entity_name, votes, divisor, quotient, seat_awarded)`

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

{{{
CREATE OR REPLACE FUNCTION public.fn_calculate_dhondt(p_election_id bigint, p_district_id bigint)
 RETURNS TABLE(round bigint, entity_id bigint, entity_name character varying,
               votes bigint, divisor integer, quotient numeric, seat_awarded boolean)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_seats         INTEGER;
    v_method_id     BIGINT;
    v_election_name VARCHAR;
    v_region_name   VARCHAR;
BEGIN

    SELECT e.winner_method_id, e.name
    INTO   v_method_id, v_election_name
    FROM   election e
    WHERE  e.election_id = p_election_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Election % does not exist', p_election_id;
    END IF;

    IF v_method_id <> 8 THEN
        RAISE EXCEPTION
            'Election "%" does not use D''Hondt method. '
            'This function only works for proportional elections.',
            v_election_name;
    END IF;

    SELECT ed.seats_available, r.name
    INTO   v_seats, v_region_name
    FROM   electoral_district ed
    JOIN   region             r  ON r.region_id = ed.region_id
    WHERE  ed.district_id  = p_district_id
    AND    ed.election_id  = p_election_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION
            'District % does not exist for election %',
            p_district_id, p_election_id;
    END IF;

    IF v_seats IS NULL OR v_seats = 0 THEN
        RAISE EXCEPTION 'District % has no seats defined', p_district_id;
    END IF;

    RAISE NOTICE 'Calculating D''Hondt for: % — % (% seats)',
        v_election_name, v_region_name, v_seats;

    RETURN QUERY
    WITH
    -- Чекор 1: гласови по ентитет во дистриктот
    raw_votes AS (
        SELECT
            COALESCE(pe2.entity_id, vr.entity_id) AS entity_id,
            SUM(vr.votes)                          AS district_votes
        FROM        vote_result      vr
        JOIN        polling_station  ps  ON ps.station_id   = vr.station_id
        JOIN        region           r2  ON r2.region_id    = ps.municipality_id
        JOIN        political_entity pe  ON pe.entity_id    = vr.entity_id
        LEFT JOIN   coalition_member cm  ON cm.party_id     = pe.party_id
        LEFT JOIN   party_coalition  pc  ON pc.coalition_id = cm.coalition_id
                                        AND pc.election_id  = vr.election_id
        LEFT JOIN   political_entity pe2 ON pe2.coalition_id = pc.coalition_id
        WHERE       vr.election_id  = p_election_id
        AND         vr.candidate_id IS NULL
        AND (
            r2.region_id        = (SELECT region_id FROM electoral_district
                                   WHERE district_id = p_district_id)
            OR
            r2.parent_region_id = (SELECT region_id FROM electoral_district
                                   WHERE district_id = p_district_id)
        )
        GROUP BY COALESCE(pe2.entity_id, vr.entity_id)
    ),
    -- Чекор 2: филтрирање на ентитети над 5% праг
    district_total AS (
        SELECT SUM(district_votes) AS total FROM raw_votes
    ),
    eligible AS (
        SELECT rv.entity_id, rv.district_votes
        FROM   raw_votes rv
        CROSS JOIN district_total dt
        WHERE  rv.district_votes * 100.0 / NULLIF(dt.total, 0) >= 5.0
    ),
    -- Чекор 3: генерирање на сите квоциенти
    quotients AS (
        SELECT
            e.entity_id,
            e.district_votes,
            d.divisor,
            ROUND(e.district_votes::NUMERIC / d.divisor, 4) AS quotient
        FROM       eligible e
        CROSS JOIN generate_series(1, v_seats) AS d(divisor)
    ),
    -- Чекор 4: рангирање на сите квоциенти
    ranked AS (
        SELECT
            q.*,
            RANK() OVER (ORDER BY q.quotient DESC) AS global_rank
        FROM quotients q
    )
    -- Чекор 5: враќање со флаг за доделен мандат
    SELECT
        r.global_rank                  AS round,
        r.entity_id,
        pe.name                        AS entity_name,
        r.district_votes               AS votes,
        r.divisor,
        r.quotient,
        (r.global_rank <= v_seats)     AS seat_awarded
    FROM   ranked           r
    JOIN   political_entity pe ON pe.entity_id = r.entity_id
    ORDER BY r.global_rank, r.entity_id;

END;
$function$;
}}}

----

=== 7. fn_get_candidate_list_for_entity

**Сигнатура:** `fn_get_candidate_list_for_entity(p_entity_id bigint, p_election_id bigint)`

**Враќа:** `TABLE(list_position, candidate_id, full_name, date_of_birth, gender, municipality, party_name, entity_name, election_name)`

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

{{{
CREATE OR REPLACE FUNCTION public.fn_get_candidate_list_for_entity(p_entity_id bigint, p_election_id bigint)
 RETURNS TABLE(list_position integer, candidate_id bigint, full_name text,
               date_of_birth date, gender character, municipality character varying,
               party_name character varying, entity_name character varying,
               election_name character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_entity_name   VARCHAR;
    v_election_name VARCHAR;
    v_list_id       BIGINT;
BEGIN
    SELECT pe.name INTO v_entity_name
    FROM   political_entity pe
    WHERE  pe.entity_id = p_entity_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Political entity % does not exist.', p_entity_id;
    END IF;

    SELECT e.name INTO v_election_name
    FROM   election e
    WHERE  e.election_id = p_election_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Election % does not exist.', p_election_id;
    END IF;

    SELECT cl.list_id INTO v_list_id
    FROM   candidate_list cl
    WHERE  cl.entity_id   = p_entity_id
    AND    cl.election_id = p_election_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION
            'No candidate list found for entity "%" on election "%".',
            v_entity_name, v_election_name;
    END IF;

    RAISE NOTICE 'Candidate list for "%" — Election: "%" (list_id: %)',
        v_entity_name, v_election_name, v_list_id;

    RETURN QUERY
    SELECT
        cli.position                                AS list_position,
        c.candidate_id,
        (p.name || ' ' || p.surname)::TEXT          AS full_name,
        p.date_of_birth,
        p.gender,
        COALESCE(r.name, 'No municipality data')    AS municipality,
        COALESCE(
            (
                SELECT pp.name
                FROM   candidate_party cp
                JOIN   political_party  pp ON pp.party_id = cp.party_id
                WHERE  cp.candidate_id = c.candidate_id
                AND    cp.election_id  = p_election_id
                ORDER BY cp.candidate_party_id
                LIMIT  1
            ),
            v_entity_name
        )                                           AS party_name,
        v_entity_name                               AS entity_name,
        v_election_name                             AS election_name
    FROM        candidate_list_item cli
    JOIN        candidate           c   ON c.candidate_id = cli.candidate_id
    JOIN        person              p   ON p.person_id    = c.person_id
    LEFT JOIN   region              r   ON r.region_id    = p.municipality_id
    WHERE       cli.list_id = v_list_id
    ORDER BY    cli.position ASC;
END;
$function$;
}}}

----

=== 8. fn_station_anomaly_score

**Сигнатура:** `fn_station_anomaly_score(p_station_id bigint, p_election_id bigint)`

**Враќа:** `TABLE(station_id, station_name, election_name, registered_voters, ballots_cast, valid_ballots, invalid_ballots, turnout_pct, invalid_pct, total_votes_vr, anomaly_score, flag_high_turnout, flag_impossible_turnout, flag_high_invalid, flag_no_ballots, flag_impossible_votes, recommendation)`

Ја анализира статистиката на гласачко место и пресметува `anomaly_score` врз основа на 5 флагови: висока излезност (>95%), невозможна излезност (>100%), многу невалидни ливчиња (>10%), нула ливчиња, и гласови поголеми од регистрираните гласачи. Ја имплементира автоматската детекција на потенцијални изборни измами или грешки во внесот со конкретна препорака за акција.

||= Anomaly Score =||= Препорака =||
|| 0 || OK — No anomalies detected. ||
|| 1–2 || MINOR — Monitor this station. ||
|| 3–5 || WARNING — Manual review recommended. ||
|| >5 || CRITICAL — Immediate investigation required. ||

{{{
CREATE OR REPLACE FUNCTION public.fn_station_anomaly_score(p_station_id bigint, p_election_id bigint)
 RETURNS TABLE(station_id bigint, station_name character varying, election_name character varying,
               registered_voters integer, ballots_cast bigint, valid_ballots bigint,
               invalid_ballots bigint, turnout_pct numeric, invalid_pct numeric,
               total_votes_vr bigint, anomaly_score integer,
               flag_high_turnout boolean, flag_impossible_turnout boolean,
               flag_high_invalid boolean, flag_no_ballots boolean,
               flag_impossible_votes boolean, recommendation text)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_station_name    VARCHAR;
    v_election_name   VARCHAR;
    v_registered      INT;
    v_ballots         BIGINT;
    v_valid           BIGINT;
    v_invalid         BIGINT;
    v_votes_vr        BIGINT;
    v_turnout         NUMERIC;
    v_invalid_pct     NUMERIC;
    v_score           INT     := 0;
    v_flag_turnout    BOOLEAN := FALSE;
    v_flag_impossible BOOLEAN := FALSE;
    v_flag_invalid    BOOLEAN := FALSE;
    v_flag_no_ballot  BOOLEAN := FALSE;
    v_flag_imp_votes  BOOLEAN := FALSE;
    v_recommendation  TEXT;
BEGIN
    SELECT ps.name, ps.registered_voter
    INTO   v_station_name, v_registered
    FROM   polling_station ps
    WHERE  ps.station_id = p_station_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Polling station % does not exist.', p_station_id;
    END IF;

    SELECT e.name INTO v_election_name
    FROM   election e
    WHERE  e.election_id = p_election_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Election % does not exist.', p_election_id;
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM station_election se
        WHERE  se.station_id  = p_station_id
        AND    se.election_id = p_election_id
    ) THEN
        RAISE EXCEPTION 'Station % is not assigned to election %.',
            p_station_id, p_election_id;
    END IF;

    SELECT
        COUNT(*),
        COUNT(*) FILTER (WHERE b.is_valid = TRUE),
        COUNT(*) FILTER (WHERE b.is_valid = FALSE)
    INTO v_ballots, v_valid, v_invalid
    FROM ballot b
    WHERE b.station_id  = p_station_id
    AND   b.election_id = p_election_id;

    SELECT COALESCE(SUM(vr.votes), 0)
    INTO   v_votes_vr
    FROM   vote_result vr
    WHERE  vr.station_id  = p_station_id
    AND    vr.election_id = p_election_id;

    v_turnout := ROUND(
        v_ballots::NUMERIC * 100.0 / NULLIF(COALESCE(v_registered, 0), 0), 2);

    v_invalid_pct := ROUND(
        v_invalid::NUMERIC * 100.0 / NULLIF(v_ballots, 0), 2);

    -- Flag 1: Излезност > 95%
    IF v_turnout > 95.0 THEN
        v_flag_turnout := TRUE;
        v_score        := v_score + 3;
    END IF;

    -- Flag 2: Излезност > 100% (невозможно)
    IF v_turnout > 100.0 THEN
        v_flag_impossible := TRUE;
        v_score           := v_score + 10;
    END IF;

    -- Flag 3: Невалидни > 10%
    IF v_invalid_pct > 10.0 THEN
        v_flag_invalid := TRUE;
        v_score        := v_score + 2;
    END IF;

    -- Flag 4: Нула балоти
    IF v_ballots = 0 THEN
        v_flag_no_ballot := TRUE;
        v_score          := v_score + 2;
    END IF;

    -- Flag 5: Гласови во vote_result > регистрирани гласачи
    IF v_votes_vr > COALESCE(v_registered, 0) THEN
        v_flag_imp_votes := TRUE;
        v_score          := v_score + 5;
    END IF;

    v_recommendation := CASE
        WHEN v_score = 0             THEN 'OK — No anomalies detected.'
        WHEN v_score BETWEEN 1 AND 2 THEN 'MINOR — Monitor this station.'
        WHEN v_score BETWEEN 3 AND 5 THEN 'WARNING — Manual review recommended.'
        ELSE                              'CRITICAL — Immediate investigation required.'
    END;

    RETURN QUERY
    SELECT
        p_station_id, v_station_name, v_election_name,
        v_registered, v_ballots, v_valid, v_invalid,
        COALESCE(v_turnout, 0), COALESCE(v_invalid_pct, 0),
        v_votes_vr, v_score,
        v_flag_turnout, v_flag_impossible, v_flag_invalid,
        v_flag_no_ballot, v_flag_imp_votes, v_recommendation;
END;
$function$;
}}}

----

== Процедури

=== 9. sp_close_election

**Сигнатура:** `sp_close_election(IN p_election_id bigint)`

Ги верифицира резултатите, го наоѓа победникот со процент на гласови, го затвора изборот (`status=0`) и логира детален извештај. Ја имплементира официјалната процедура за завршување на изборен циклус — само избори со внесени резултати можат да се затворат.

{{{
CREATE OR REPLACE PROCEDURE public.sp_close_election(IN p_election_id bigint)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_election_name  VARCHAR;
    v_election_type  VARCHAR;
    v_status         SMALLINT;
    v_station_count  INT;
    v_total_votes    BIGINT;
    v_winner_name    VARCHAR;
    v_winner_votes   BIGINT;
    v_winner_pct     NUMERIC;
BEGIN
    -- (1) Провери дали изборот постои
    SELECT e.name, e.status, et.type_name
    INTO   v_election_name, v_status, v_election_type
    FROM   election e
    JOIN   election_type et ON et.election_type_id = e.election_type_id
    WHERE  e.election_id = p_election_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Election % does not exist.', p_election_id;
    END IF;

    -- (2) Провери дали е активен
    IF v_status = 0 THEN
        RAISE EXCEPTION 'Election "%" is already closed.', v_election_name;
    END IF;

    -- (3) Провери дали постојат vote_result записи
    SELECT COUNT(DISTINCT vr.station_id), SUM(vr.votes)
    INTO   v_station_count, v_total_votes
    FROM   vote_result vr
    WHERE  vr.election_id  = p_election_id
    AND    vr.candidate_id IS NULL;

    IF v_total_votes IS NULL OR v_total_votes = 0 THEN
        RAISE EXCEPTION
            'Election "%" has no vote results. Cannot close an election without results.',
            v_election_name;
    END IF;

    -- Најди победник
    SELECT pe.name, SUM(vr.votes),
           ROUND(SUM(vr.votes)::NUMERIC * 100.0 / NULLIF(v_total_votes, 0), 2)
    INTO   v_winner_name, v_winner_votes, v_winner_pct
    FROM   vote_result vr
    JOIN   political_entity pe ON pe.entity_id = vr.entity_id
    WHERE  vr.election_id  = p_election_id
    AND    vr.candidate_id IS NULL
    GROUP BY pe.name
    ORDER BY SUM(vr.votes) DESC
    LIMIT 1;

    -- Затвори го изборот
    UPDATE election SET status = 0 WHERE election_id = p_election_id;

    RAISE NOTICE '════════════════════════════════════════';
    RAISE NOTICE 'Election closed: % (%)', v_election_name, v_election_type;
    RAISE NOTICE 'Stations reported : %', v_station_count;
    RAISE NOTICE 'Total votes        : %', v_total_votes;
    RAISE NOTICE 'Winner             : %', v_winner_name;
    RAISE NOTICE 'Winner votes       : %', v_winner_votes;
    RAISE NOTICE 'Winner share       : % pct', v_winner_pct;
    RAISE NOTICE '════════════════════════════════════════';

END;
$procedure$;
}}}

----

=== 10. sp_open_election

**Сигнатура:** `sp_open_election(IN p_election_id bigint)`

Го активира изборот (`status=1`) по верификација дека не постои веќе активен избор од ист тип во системот. Ја имплементира бизнис правилото дека не можат да се одржуваат два истовидни избори истовремено.

{{{
CREATE OR REPLACE PROCEDURE public.sp_open_election(IN p_election_id bigint)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_election_name    VARCHAR;
    v_type_id          BIGINT;
    v_status           SMALLINT;
    v_same_type_active BOOLEAN;
BEGIN
    SELECT name, status, election_type_id
    INTO   v_election_name, v_status, v_type_id
    FROM   public.election
    WHERE  election_id = p_election_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Грешка: Изборот со ID % не постои.', p_election_id;
    END IF;

    IF v_status = 1 THEN
        RAISE EXCEPTION
            'Забелешка: Изборот "%" е веќе отворен и активен.', v_election_name;
    END IF;

    SELECT EXISTS (
        SELECT 1 FROM public.election
        WHERE  status = 1 AND election_type_id = v_type_id
    ) INTO v_same_type_active;

    IF v_same_type_active THEN
        RAISE EXCEPTION
            'Предупредување: Веќе постојат активни избори од овој тип во системот. '
            'Мора прво да ги затворите претходните од истата категорија.';
    END IF;

    UPDATE public.election SET status = 1 WHERE election_id = p_election_id;

    RAISE NOTICE '========================================';
    RAISE NOTICE 'Изборот "%" е УСПЕШНО ОТВОРЕН!', v_election_name;
    RAISE NOTICE 'Овозможено е истовремено гласање со други активни различни типови избори.';
    RAISE NOTICE '========================================';

END;
$procedure$;
}}}

----

=== 11. sp_register_newly_eligible_voters

**Сигнатура:** `sp_register_newly_eligible_voters()`

Ги регистрира сите лица кои наполниле 18 години а сè уште не се во гласачкиот список, и ги распоредува на најмалку оптоварената станица во batch-ови од 10,000. Ја автоматизира периодичната ажурирање на гласачкиот список која државните институции ја вршат пред секои избори.

{{{
CREATE OR REPLACE PROCEDURE public.sp_register_newly_eligible_voters()
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_count        INT := 0;
    v_max_voter_id BIGINT;
    v_station_id   BIGINT;
    v_region_id    BIGINT;
    rec            RECORD;
BEGIN
    SELECT COALESCE(MAX(voter_id), 0)
    INTO   v_max_voter_id
    FROM   voter;

    -- Најди најмалку оптоварена станица
    SELECT ps.station_id, ps.municipality_id
    INTO   v_station_id, v_region_id
    FROM   polling_station ps
    LEFT JOIN voter v ON v.station_id = ps.station_id
    GROUP BY ps.station_id, ps.municipality_id, ps.registered_voter
    ORDER BY COUNT(v.voter_id) * 1.0 / NULLIF(ps.registered_voter, 1) ASC
    LIMIT 1;

    IF v_station_id IS NULL THEN
        RAISE EXCEPTION 'No polling stations found in the system';
    END IF;

    FOR rec IN
        SELECT p.person_id
        FROM   person p
        WHERE  p.date_of_birth <= CURRENT_DATE - INTERVAL '18 years'
        AND    p.person_id NOT IN (SELECT person_id FROM voter)
        ORDER BY p.person_id
    LOOP
        v_max_voter_id := v_max_voter_id + 1;

        INSERT INTO voter (voter_id, person_id, region_id, station_id)
        VALUES (v_max_voter_id, rec.person_id, v_region_id, v_station_id)
        ON CONFLICT (person_id) DO NOTHING;

        v_count := v_count + 1;

        -- Commit во batch-ови од 10,000
        IF v_count % 10000 = 0 THEN
            RAISE NOTICE 'Registered % voters so far...', v_count;
        END IF;

    END LOOP;

    RAISE NOTICE 'Done. Total newly registered voters: %', v_count;

END;
$procedure$;
}}}