Changes between Initial Version and Version 1 of DatabaseProgramming

Timestamp:

06/03/26 02:42:41 (3 weeks ago)

Author:

231071

Comment:

--

DatabaseProgramming

@@ +1 @@
+= Фаза 4 — Функции, Процедури, Тригери
+
+== Тригери
+
+=== 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();
+}}}
+
+----
+
+=== 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();
+}}}
+
+----
+
+=== 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();
+}}}
+
+----
+
+=== 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();
+}}}
+
+----
+
+=== 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();
+}}}
+
+----
+
+== Функции
+
+=== 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$;
+}}}
+
+----
+
+=== 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$;
+}}}
+
+----
+
+=== 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$;
+}}}
+
+----
+
+== Процедури
+
+=== 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$;
+}}}
+
+----
+
+=== 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$;
+}}}
+
+----
+
+=== 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$;
+}}}