DROP TABLE IF EXISTS _stage_male_names   CASCADE;
DROP TABLE IF EXISTS _stage_female_names CASCADE;
DROP TABLE IF EXISTS _stage_surnames     CASCADE;

CREATE TABLE _stage_male_names (
    id   SERIAL      PRIMARY KEY,
    ime VARCHAR(50) NOT NULL
);

CREATE TABLE _stage_female_names (
    id   SERIAL      PRIMARY KEY,
    ime VARCHAR(50) NOT NULL
);

CREATE TABLE _stage_surnames (
    id      SERIAL      PRIMARY KEY,
    surname VARCHAR(50) NOT NULL
);


-- ─────────────────────────────────────────────────────
-- 2. LOAD YOUR CSV FILES
--
-- \copy uses the CLIENT machine's file path — works for
--   any user via the psql command-line client.
-- If you are a superuser connected locally you can also use:
--   COPY _stage_male_names (name) FROM '/absolute/path/male_names.csv' ...;
--
-- Adjust the file paths below to match where your CSVs live.
-- ─────────────────────────────────────────────────────

\copy _stage_male_names   (ime)    FROM 'C:\Users\acemi\OneDrive\Desktop\male_names.csv'   WITH (FORMAT csv, HEADER true);
\copy _stage_female_names (ime)    FROM 'female_names.csv' WITH (FORMAT csv, HEADER true);
\copy _stage_surnames     (surname) FROM 'surnames.csv'      WITH (FORMAT csv, HEADER true);

select * from surnames_final;

-- ─────────────────────────────────────────────────────
-- 3. VERIFY LOADS
-- ─────────────────────────────────────────────────────
INSERT INTO _stage_male_names(name) VALUES ('John'), ('Michael'), ('David');

DO $$
BEGIN
    RAISE NOTICE '──────────────────────────────────────';
    RAISE NOTICE 'Name pool sizes:';
    RAISE NOTICE '  Male names   : %', (SELECT COUNT(*) FROM male_names);
    RAISE NOTICE '  Female names : %', (SELECT COUNT(*) FROM female_names);
    RAISE NOTICE '  Surnames     : %', (SELECT COUNT(*) FROM surnames_final);
    RAISE NOTICE '──────────────────────────────────────';
END $$;


-- ─────────────────────────────────────────────────────
-- 4. PRE-COMPUTE POOL SIZES
--    Stored once in a temp table so the scalar subqueries
--    inside the main INSERT are evaluated exactly once,
--    not once per row.
-- ─────────────────────────────────────────────────────

CREATE TEMP TABLE _name_counts AS
SELECT
    (SELECT COUNT(*) FROM male_names)::INT   AS m_cnt,
    (SELECT COUNT(*) FROM female_names)::INT AS f_cnt,
    (SELECT COUNT(*) FROM surnames_final)::INT     AS s_cnt;


-- ─────────────────────────────────────────────────────
-- 5. MAIN INSERT
--
-- Architecture:
--   Inner subquery  — generate_series produces 10.1M base rows.
--                     Each row gets a random DOB, gender, and
--                     pre-drawn random indices into the name pools.
--                     The 1% overshoot (10.1M vs 10M) absorbs
--                     any rows lost to ON CONFLICT.
--
--   Outer SELECT    — joins the base rows to the staging name
--                     tables by the pre-drawn index, and computes
--                     the window-function dob_seq used to build
--                     the 13-digit person_id.
--
-- Random index formula: 1 + FLOOR(RANDOM() * pool_size)
--   Gives a uniform integer in [1, pool_size] matching the
--   SERIAL PKs of the staging tables.
--
-- ON CONFLICT DO NOTHING — silently skips the rare case where
--   two generated rows share (name, surname, date_of_birth).
--   With typical pools (≥ 1 000 names × ≥ 5 000 surnames ×
--   34 697 distinct DODs) the collision space exceeds 170 billion
--   combinations, so duplicates are effectively impossible.
--
-- Expected runtime: 10–25 minutes depending on hardware.
-- ─────────────────────────────────────────────────────

ALTER TABLE male_names DROP COLUMN id;

-- 2. Add it back as a SERIAL.
-- PostgreSQL will automatically populate it for all existing rows!
ALTER TABLE female_names ADD COLUMN id SERIAL PRIMARY KEY;
select * from surnames_final;

INSERT INTO person (person_id, name, surname, date_of_birth, gender)
SELECT
    -- ── person_id: YYYYMMDD + 5-digit birth-date sequence ──
    (
        TO_CHAR(b.dob, 'YYYYMMDD')
        ||
        LPAD(
            ROW_NUMBER() OVER (
                PARTITION BY b.dob       -- restart count per birth date
                ORDER BY     b.seq       -- stable order within each day
            )::TEXT,
            5, '0'
        )
    )::BIGINT                                                       AS person_id,

    -- ── name: chosen from pool matching gender ──────────────
    CASE b.gender
        WHEN 'M' THEN mn.ime
        WHEN 'F' THEN fn.ime
        ELSE          -- 'O': randomly pick from either pool
            CASE WHEN RANDOM() < 0.5 THEN mn.ime ELSE fn.ime END
    END                                                             AS name,

    sr.c2,
    b.dob                                                           AS date_of_birth,
    b.gender

FROM (
    -- ── Base row generation ─────────────────────────────────
    SELECT
        gs.seq,

        -- Random date of birth: full age range, including under-18
        DATE '1930-01-01'
            + (FLOOR(RANDOM() * (DATE '2024-12-31' - DATE '1930-01-01' + 1)))::INT
                                                                    AS dob,

        -- Gender: 49 % M / 49 % F / 2 % O
        CASE
            WHEN RANDOM() < 0.49 THEN 'M'::CHAR(1)
            WHEN RANDOM() < 0.98 THEN 'F'::CHAR(1)
            ELSE                       'O'::CHAR(1)
        END                                                         AS gender,

        -- Pre-draw name-pool indices (uniform in [1, pool_size])
        (1 + FLOOR(RANDOM() * (SELECT m_cnt FROM _name_counts)))::INT AS m_idx,
        (1 + FLOOR(RANDOM() * (SELECT f_cnt FROM _name_counts)))::INT AS f_idx,
        (1 + FLOOR(RANDOM() * (SELECT s_cnt FROM _name_counts)))::INT AS s_idx

    FROM generate_series(1, 1010000) AS gs(seq)   -- 1 % overshoot
) AS b

LEFT JOIN male_names   mn ON mn.id = b.m_idx
LEFT JOIN female_names fn ON fn.id = b.f_idx
LEFT JOIN surnames_final     sr ON sr.c1 = b.s_idx

ON CONFLICT (name, surname, date_of_birth) DO NOTHING;
select * from male_names;

INSERT INTO person (person_id, name, surname, date_of_birth, gender)
SELECT
    (
        TO_CHAR(b.dob, 'YYYYMMDD')
        ||
        LPAD(
            (ROW_NUMBER() OVER (
                PARTITION BY b.dob
                ORDER BY     b.seq
            ) + 10000)::TEXT,   -- offset avoids all existing IDs
            5, '0'
        )
    )::BIGINT                                           AS person_id,
    CASE b.gender
        WHEN 'M' THEN mn.c1
        WHEN 'F' THEN fn.c1
        ELSE CASE WHEN RANDOM() < 0.5 THEN mn.c1 ELSE fn.c1 END
    END                                                 AS name,
    sr.c2,
    b.dob                                               AS date_of_birth,
    b.gender
FROM (
    SELECT
        gs.seq,
        -- Adults only: DOB capped at 2006 so everyone is 18+ as of 2024
        DATE '1960-01-01'
            + (FLOOR(RANDOM() * (DATE '2006-12-31' - DATE '1960-01-01' + 1)))::INT AS dob,
        CASE
            WHEN RANDOM() < 0.49 THEN 'M'::CHAR(1)
            WHEN RANDOM() < 0.98 THEN 'F'::CHAR(1)
            ELSE                       'O'::CHAR(1)
        END                                             AS gender,
        (1 + FLOOR(RANDOM() * (SELECT m_cnt FROM _name_counts)))::INT AS m_idx,
        (1 + FLOOR(RANDOM() * (SELECT f_cnt FROM _name_counts)))::INT AS f_idx,
        (1 + FLOOR(RANDOM() * (SELECT s_cnt FROM _name_counts)))::INT AS s_idx
    FROM generate_series(1, 2000000) AS gs(seq)   -- 2M to be safe, targeting 1.5M net
) AS b
LEFT JOIN male_names   mn ON mn.id = b.m_idx
LEFT JOIN female_names fn ON fn.id = b.f_idx
LEFT JOIN surnames_final sr ON sr.id = b.s_idx
ON CONFLICT (name, surname, date_of_birth) DO NOTHING;

select * from surnames_final;


DROP TABLE IF EXISTS male_names;
DROP TABLE IF EXISTS female_names;
DROP TABLE IF EXISTS surnames_final;
DROP TABLE IF EXISTS _name_counts;



INSERT INTO party_leader (leader_id, person_id)
SELECT
    ROW_NUMBER() OVER (ORDER BY person_id) AS leader_id,
    person_id
FROM (
    SELECT person_id
    FROM   person
    WHERE  date_of_birth <= CURRENT_DATE - INTERVAL '30 years'
    ORDER BY RANDOM()
    LIMIT 500
) p;


DROP TABLE IF EXISTS _stage_prefixes;
DROP TABLE IF EXISTS _stage_suffixes;

CREATE TEMP TABLE _stage_prefixes (id SERIAL PRIMARY KEY, val VARCHAR(50));
CREATE TEMP TABLE _stage_suffixes (id SERIAL PRIMARY KEY, val VARCHAR(50));

INSERT INTO _stage_prefixes (val) VALUES
('National'),   ('Democratic'),  ('Social'),      ('Liberal'),     ('Conservative'),
('Green'),      ('People''s'),   ('United'),      ('Freedom'),     ('Progressive'),
('Republican'),  ('Labor'),       ('Workers'),     ('Christian'),   ('Independent'),
('New'),         ('Reform'),      ('Patriotic'),   ('Citizens'),    ('Popular'),
('Communist'),   ('Solidarity'),  ('Justice'),     ('Civic'),       ('Alliance');

INSERT INTO _stage_suffixes (val) VALUES
('Party'),       ('Alliance'),    ('Movement'),    ('Front'),       ('Union'),
('Force'),       ('Coalition'),   ('League'),      ('Congress'),    ('Federation'),
('Bloc'),        ('Initiative'),  ('Network'),     ('Platform'),    ('Assembly');

-- ── 2a. INSERT parties (no leader / parent yet) ──────
INSERT INTO political_party (party_id, name, abbreviation, founded_year)
SELECT
    ROW_NUMBER() OVER (ORDER BY p.val, s.val)       AS party_id,
    p.val || ' ' || s.val                           AS name,
    -- Abbreviation: first 2 letters of prefix + first 2 of suffix, uppercased
    UPPER(LEFT(p.val, 2) || LEFT(s.val, 2))         AS abbreviation,
    -- Founded anywhere from 1900 to 2020
    1900 + FLOOR(RANDOM() * 121)::INT               AS founded_year
FROM _stage_prefixes  p
CROSS JOIN _stage_suffixes s
where p.val <> s.val
ON CONFLICT (name) DO NOTHING;

select * from political_party;

DROP TABLE IF EXISTS _stage_prefixes;
DROP TABLE IF EXISTS _stage_suffixes;

WITH numbered_parties AS (
    SELECT party_id,
           ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn
    FROM   political_party
),
numbered_leaders AS (
    SELECT leader_id,
           ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn
    FROM   party_leader
),
leader_count AS (
    SELECT COUNT(*) AS cnt FROM party_leader
)
UPDATE political_party pp
SET    leader_id = nl.leader_id
FROM   numbered_parties np
JOIN   numbered_leaders nl
       ON nl.rn = ((np.rn - 1) % (SELECT cnt FROM leader_count) + 1)
WHERE  np.party_id = pp.party_id;

WITH candidates AS (
    SELECT party_id,
           ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn
    FROM   political_party
),
parents AS (
    SELECT party_id AS parent_id,
           ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn
    FROM   political_party
),
total AS (SELECT COUNT(*) AS cnt FROM political_party)
UPDATE political_party pp
SET    parent_party_id = pa.parent_id
FROM   candidates c
JOIN   parents    pa ON pa.rn = ((c.rn - 1) % (SELECT cnt FROM total) + 1)
WHERE  c.party_id        = pp.party_id
AND    pa.parent_id     <> pp.party_id          -- no self-reference
AND    c.rn % 10         < 3; 

INSERT INTO candidate (candidate_id, person_id)
SELECT
    ROW_NUMBER() OVER (ORDER BY person_id) AS candidate_id,
    person_id
FROM (
    SELECT person_id
    FROM   person
    WHERE  date_of_birth <= CURRENT_DATE - INTERVAL '18 years'
    ORDER BY RANDOM()
    LIMIT  50000
) p;


INSERT INTO election (
    election_id, name, election_type_id, region_id,
    election_date, description, status, winner_method_id, total_seats
)
VALUES

-- ── PARLIAMENTARY ────────────────────────────────────
(1,  'Parliamentary Election 2006',
     2, 141, '2006-07-05',
     'Early parliamentary elections held after the 2005 local elections.',
     0, 8, 120),

(2,  'Parliamentary Election 2008',
     2, 141, '2008-06-01',
     'Snap elections called amid political tensions.',
     0, 8, 120),

(3,  'Parliamentary Election 2011',
     2, 141, '2011-06-05',
     'Early elections following a budget dispute in parliament.',
     0, 8, 120),

(4,  'Parliamentary Election 2014',
     2, 141, '2014-04-27',
     'Parliamentary elections held concurrently with the presidential election.',
     0, 8, 120),

(5,  'Parliamentary Election 2016',
     2, 141, '2016-12-11',
     'Elections held amid the political crisis following wiretapping scandals.',
     0, 8, 120),

(6,  'Parliamentary Election 2019',
     2, 141, '2019-04-12',
     'Boycotted by opposition; results subsequently annulled.',
     0, 8, 120),

(7,  'Parliamentary Election 2020',
     2, 141, '2020-07-15',
     'First elections held under the constitutional name North Macedonia.',
     0, 8, 120),

(8,  'Parliamentary Election 2024',
     2, 141, '2024-05-08',
     'Parliamentary elections held concurrently with the presidential election.',
     0, 8, 120),

-- ── PRESIDENTIAL ─────────────────────────────────────
(9,  'Presidential Election 2009',
     1, 141, '2009-03-22',
     'Gjorge Ivanov elected president in the second round.',
     0, 3, NULL),

(10, 'Presidential Election 2014',
     1, 141, '2014-04-27',
     'Gjorge Ivanov re-elected for a second term.',
     0, 3, NULL),

(11, 'Presidential Election 2019',
     1, 141, '2019-04-21',
     'Stevo Pendarovski elected; turnout quorum not met in first round.',
     0, 3, NULL),

(12, 'Presidential Election 2024',
     1, 141, '2024-05-08',
     'Gordana Siljanovska-Davkova elected as first female president.',
     0, 3, NULL),

-- ── LOCAL / MUNICIPAL ────────────────────────────────
(13, 'Local Elections 2013',
     3, 141, '2013-03-24',
     'Municipal elections for mayors and council members across all municipalities.',
     0, 3, NULL),

(14, 'Local Elections 2017',
     3, 141, '2017-10-15',
     'Municipal elections following the resolution of the political crisis.',
     0, 3, NULL),

(15, 'Local Elections 2021',
     3, 141, '2021-10-17',
     'Municipal elections held under the SDSM-led government.',
     0, 3, NULL),

-- ── REFERENDUM ───────────────────────────────────────
(16, 'Independence Referendum 1991',
     6, 141, '1991-09-08',
     'Referendum on independence from Yugoslavia. 95% voted in favour.',
     0, 1, NULL),

(17, 'Prespa Agreement Referendum 2018',
     6, 141, '2018-09-30',
     'Referendum on the Prespa Agreement to rename the country. Quorum not met.',
     0, 1, NULL)

ON CONFLICT (name) DO NOTHING;


INSERT INTO person (person_id, name, surname, date_of_birth, gender)
VALUES

-- ── NORTH MACEDONIA ──────────────────────────────────
(1978090199001, 'Hristijan',  'Mickoski',      '1978-09-01', 'M'),  -- VMRO-DPMNE
(1974100199001, 'Zoran',      'Zaev',           '1974-10-01', 'M'),  -- SDSM (former)
(1974040199001, 'Oliver',     'Spasovski',      '1974-04-01', 'M'),  -- SDSM (current)
(1959020499001, 'Ali',        'Ahmeti',         '1959-02-04', 'M'),  -- DUI
(1960020199001, 'Menduh',     'Thaci',          '1960-02-01', 'M'),  -- DPA (former)
(1980010199001, 'Afrim',      'Gashi',          '1980-01-01', 'M'),  -- DPA (current)
(1984010199001, 'Bilal',      'Kasami',         '1984-01-01', 'M'),  -- BESA
(1977010199001, 'Arben',      'Taravari',       '1977-01-01', 'M'),  -- Alliance for Albanians
(1980060199001, 'Dimitar',    'Apasiev',        '1980-06-01', 'M'),  -- Levica
(1966010199001, 'Stevcho',    'Jakimovski',     '1966-01-01', 'M'),  -- GROM
(1952010199001, 'Petar',      'Gosev',          '1952-01-01', 'M'),  -- LDP (historical)
(1945010199001, 'Kiro',       'Gligorov',       '1945-01-01', 'M'),  -- SDSM founder
(1958010199001, 'Ljubco',     'Georgievski',    '1958-01-01', 'M'),  -- VMRO-DPMNE historical
(1960050199001, 'Branko',     'Crvenkovski',    '1960-05-01', 'M'),  -- SDSM historical
(1966030199001, 'Nikola',     'Gruevski',       '1970-08-31', 'M'),  -- VMRO-DPMNE historical
(1955010199001, 'Abdulakim',  'Ademi',          '1955-01-01', 'M'),  -- DUI historical
(1958030199001, 'Arben',      'Xhaferi',        '1958-03-01', 'M'),  -- DPA historical
(1963010199001, 'Imer',       'Selmani',        '1963-01-01', 'M'),  -- PDP
(1970010199001, 'Nevzat',     'Halili',         '1970-01-01', 'M'),  -- NDP
(1975010199001, 'Stevo',      'Pendarovski',    '1963-03-07', 'M'),  -- SDSM presidential
(1972010199001, 'Ljupcho',    'Zikov',          '1972-01-01', 'M'),  -- VMRO-NP
(1968010199001, 'Liljana',    'Popovska',       '1968-01-01', 'F'),  -- LDP historical
(1980010299001, 'Ziadin',     'Sela',           '1980-01-02', 'M'),  -- Alliance for Albanians co-founder
(1975020199001, 'Saso',       'Mijalkov',       '1975-02-01', 'M'),  -- VMRO-DPMNE historical
(1969010199001, 'Radmila',    'Sekerinska',     '1969-01-01', 'F'),  -- SDSM historical

-- ── SERBIA ───────────────────────────────────────────
(1970030599001, 'Aleksandar', 'Vucic',          '1970-03-05', 'M'),  -- SNS
(1966010199002, 'Ivica',      'Dacic',          '1966-01-01', 'M'),  -- SPS
(1966051399001, 'Zoran',      'Lutovac',        '1966-05-13', 'M'),  -- DS
(1944032499001, 'Vojislav',   'Kostunica',      '1944-03-24', 'M'),  -- DSS
(1954101199001, 'Vojislav',   'Seselj',         '1954-10-11', 'M'),  -- SRS
(1946112999001, 'Vuk',        'Draskovic',      '1946-11-29', 'M'),  -- SPO
(1967040399001, 'Dragan',     'Dilas',          '1967-04-03', 'M'),  -- SSP
(1970110899001, 'Bosko',      'Obradovic',      '1970-11-08', 'M'),  -- Dveri
(1963010199002, 'Zoran',      'Djindjic',       '1952-08-01', 'M'),  -- DS historical (assassinated)
(1952020199001, 'Slobodan',   'Milosevic',      '1941-08-20', 'M'),  -- SPS founder (historical)
(1958010199002, 'Velimir',    'Ilic',           '1949-09-09', 'M'),  -- Nova Srbija
(1965010199001, 'Cedomir',    'Jovanovic',      '1965-01-01', 'M'),  -- LDP Serbia
(1968010199002, 'Maja',       'Gojkovic',       '1968-01-01', 'F'),  -- SNS historical
(1971010199001, 'Ana',        'Brnabic',        '1975-09-18', 'F'),  -- SNS
(1960010199001, 'Tomislav',   'Nikolic',        '1952-02-15', 'M'),  -- SNS co-founder
(1967010199001, 'Nebojsa',    'Stefanovic',     '1967-01-01', 'M'),  -- SNS
(1975010199002, 'Milos',      'Jovanovic',      '1975-01-01', 'M'),  -- NADA/DSS current
(1968020199001, 'Branko',     'Ruzic',          '1968-02-01', 'M'),  -- SDPS
(1979010199001, 'Predrag',    'Milosavljevic',  '1979-01-01', 'M'),  -- PUPS
(1963030199001, 'Sulejman',   'Ugljanin',       '1950-06-22', 'M'),  -- SDA Sandzak
(1970050199001, 'Usame',      'Zukorlic',       '1967-11-20', 'M'),  -- BSZ
(1965060199001, 'Andras',     'Agostonc',       '1965-06-01', 'M'),  -- SVM
(1958020199001, 'Mladen',     'Obradovic',      '1958-02-01', 'M'),  -- Obraz historical
(1969030199001, 'Aleksandar', 'Sapic',          '1977-02-25', 'M'),  -- SNS Belgrade
(1974010199001, 'Boris',      'Tadic',          '1958-01-15', 'M')   -- DS historical president

ON CONFLICT (person_id) DO NOTHING;


INSERT INTO party_leader (leader_id, person_id)
VALUES
-- NMK leaders
(1001, 1978090199001),  -- Mickoski
(1002, 1974100199001),  -- Zaev
(1003, 1974040199001),  -- Spasovski
(1004, 1959020499001),  -- Ahmeti
(1005, 1960020199001),  -- Menduh Thaci
(1006, 1980010199001),  -- Afrim Gashi
(1007, 1984010199001),  -- Kasami
(1008, 1977010199001),  -- Taravari
(1009, 1980060199001),  -- Apasiev
(1010, 1966010199001),  -- Jakimovski
(1011, 1952010199001),  -- Gosev
(1012, 1945010199001),  -- Gligorov
(1013, 1958010199001),  -- Georgievski
(1014, 1960050199001),  -- Crvenkovski
(1015, 1966030199001),  -- Gruevski
(1016, 1955010199001),  -- Ademi
(1017, 1958030199001),  -- Xhaferi
(1018, 1963010199001),  -- Selmani
(1019, 1970010199001),  -- Halili
(1020, 1975010199001),  -- Pendarovski
(1021, 1972010199001),  -- Zikov
(1022, 1968010199001),  -- Popovska
(1023, 1980010299001),  -- Ziadin Sela
(1024, 1969010199001),  -- Sekerinska

-- Serbia leaders
(2001, 1970030599001),  -- Vucic
(2002, 1966010199002),  -- Dacic
(2003, 1966051399001),  -- Lutovac
(2004, 1944032499001),  -- Kostunica
(2005, 1954101199001),  -- Seselj
(2006, 1946112999001),  -- Draskovic
(2007, 1967040399001),  -- Dilas
(2008, 1970110899001),  -- Obradovic
(2009, 1963010199002),  -- Djindjic
(2010, 1952020199001),  -- Milosevic
(2011, 1958010199002),  -- Ilic
(2012, 1965010199001),  -- Cedomir Jovanovic
(2013, 1960010199001),  -- Nikolic
(2014, 1967010199001),  -- Nebojsa Stefanovic
(2015, 1975010199002),  -- Milos Jovanovic
(2016, 1968020199001),  -- Ruzic
(2017, 1963030199001),  -- Ugljanin
(2018, 1970050199001),  -- Zukorlic
(2019, 1965060199001),  -- Agostonc
(2020, 1974010199001)   -- Boris Tadic

ON CONFLICT (person_id) DO NOTHING;


INSERT INTO political_party (
    party_id, name, abbreviation, description,
    founded_year, ideology, leader_id, parent_party_id
)
VALUES

-- ── NORTH MACEDONIA ──────────────────────────────────
(1001, 'VMRO-DPMNE',
 'VMRO-DPMNE',
 'Внатрешна македонска револуционерна организација. Centre-right nationalist party, dominant force in Macedonian politics.',
 1990, 'Nationalism, Conservatism, Christian Democracy',
 1001, NULL),

(1002, 'Social Democratic Union of Macedonia',
 'SDSM',
 'Социјалдемократски сојуз на Македонија. Centre-left social democratic party.',
 1991, 'Social Democracy, Liberalism, Pro-Europeanism',
 1003, NULL),

(1003, 'Democratic Union for Integration',
 'DUI',
 'Bashkimi Demokratik për Integrim. Albanian minority party formed after the 2001 armed conflict.',
 2002, 'Albanian minority rights, Liberalism, Pro-EU',
 1004, NULL),

(1004, 'Democratic Party of Albanians',
 'DPA',
 'Partia Demokratike Shqiptare. Albanian minority conservative party.',
 1997, 'Albanian minority rights, Conservatism',
 1006, NULL),

(1005, 'BESA Movement',
 'BESA',
 'Lëvizja BESA. Albanian minority party emphasising civic values and anti-corruption.',
 2015, 'Albanian minority rights, Centrism, Civic nationalism',
 1007, NULL),

(1006, 'Alliance for Albanians',
 'AA',
 'Aleanca për Shqiptarët. Albanian minority party formed by merger of smaller parties.',
 2018, 'Albanian minority rights, Liberalism',
 1008, NULL),

(1007, 'Levica',
 'LEVICA',
 'The Left. Left-wing party focused on social justice and anti-corruption.',
 2015, 'Democratic Socialism, Anti-corruption, Euroscepticism',
 1009, NULL),

(1008, 'GROM',
 'GROM',
 'Граѓанска опција за Македонија. Centrist civic party.',
 2013, 'Centrism, Civic politics',
 1010, NULL),

(1009, 'VMRO - Peoples Party',
 'VMRO-NP',
 'VMRO - Народна партија. Split from VMRO-DPMNE, centre-right.',
 2004, 'Nationalism, Conservatism',
 1021, NULL),

(1010, 'Liberal Democratic Party of Macedonia',
 'LDP-MK',
 'Либерално-демократска партија. Centrist liberal party.',
 2004, 'Liberalism, Social Liberalism',
 1022, NULL),

(1011, 'Party for Democratic Prosperity',
 'PDP',
 'Partia për Prosperitet Demokratik. First major Albanian political party in Macedonia.',
 1990, 'Albanian minority rights, Social Democracy',
 1018, NULL),

(1012, 'Socialist Party of Macedonia',
 'SPM',
 'Социјалистичка партија на Македонија. Left-wing historical party.',
 1990, 'Socialism, Social Democracy',
 NULL, NULL),

(1013, 'Liberal Party of Macedonia',
 'LP-MK',
 'Либерална партија на Македонија. Historical liberal party.',
 1990, 'Liberalism',
 1011, NULL),

(1014, 'National Democratic Revival',
 'NDR',
 'Rilindja Demokratike Kombëtare. Albanian minority party.',
 2008, 'Albanian minority rights',
 1019, NULL),

(1015, 'Democratic Renewal of Macedonia',
 'DOM',
 'Демократска обнова на Македонија. Small centrist party.',
 2005, 'Centrism, Green politics',
 NULL, NULL),

-- ── SERBIA ───────────────────────────────────────────
(2001, 'Serbian Progressive Party',
 'SNS',
 'Srpska napredna stranka. Dominant centre-right populist party founded by Vucic and Nikolic.',
 2008, 'Conservatism, Populism, Pro-EU rhetoric',
 2001, NULL),

(2002, 'Socialist Party of Serbia',
 'SPS',
 'Socijalistička partija Srbije. Founded by Slobodan Milosevic.',
 1990, 'Socialism, Serbian nationalism',
 2002, NULL),

(2003, 'Democratic Party',
 'DS',
 'Demokratska stranka. Centre-left social democratic party.',
 1990, 'Social Democracy, Liberalism, Pro-EU',
 2003, NULL),

(2004, 'Democratic Party of Serbia',
 'DSS',
 'Demokratska stranka Srbije. Conservative nationalist party founded by Kostunica.',
 1992, 'Conservatism, Serbian nationalism, Euroscepticism',
 2004, NULL),

(2005, 'Serbian Radical Party',
 'SRS',
 'Srpska radikalna stranka. Far-right ultranationalist party led by Seselj.',
 1991, 'Ultra-nationalism, Greater Serbia, Social conservatism',
 2005, NULL),

(2006, 'Serbian Renewal Movement',
 'SPO',
 'Srpski pokret obnove. Centre-right monarchist party led by Vuk Draskovic.',
 1990, 'Monarchism, Conservatism, Serbian nationalism',
 2006, NULL),

(2007, 'Freedom and Justice Party',
 'SSP',
 'Stranka slobode i pravde. Centre-left pro-European party founded by Dragan Dilas.',
 2019, 'Social Democracy, Liberalism, Anti-corruption, Pro-EU',
 2007, NULL),

(2008, 'Dveri',
 'DVERI',
 'Srpski pokret Dveri. Conservative social movement turned political party.',
 2010, 'Social conservatism, Euroscepticism, Serbian nationalism',
 2008, NULL),

(2009, 'Nova Srbija',
 'NS',
 'Nova Srbija. Centre-right regional party founded by Velimir Ilic.',
 1998, 'Conservatism, Regionalism',
 2011, NULL),

(2010, 'Liberal Democratic Party of Serbia',
 'LDP-SRB',
 'Liberalno demokratska partija. Progressive liberal party.',
 2005, 'Liberalism, Pro-EU, Progressivism',
 2012, NULL),

(2011, 'Alliance of Vojvodina Hungarians',
 'SVM',
 'Savez vojvođanskih Mađara. Ethnic Hungarian minority party in Vojvodina.',
 1994, 'Hungarian minority rights, Regionalism, Liberalism',
 2019, NULL),

(2012, 'Social-Democratic Party of Serbia',
 'SDPS',
 'Socijaldemokratska partija Srbije. Centre-left coalition partner of SNS.',
 2009, 'Social Democracy, Centre-left',
 2016, NULL),

(2013, 'Bosniak Democratic Union of Sandzak',
 'BDZ',
 'Bošnjačka demokratska zajednica Sandžaka. Bosniak minority party.',
 2009, 'Bosniak minority rights, Regionalism',
 2018, NULL),

(2014, 'G17 Plus',
 'G17+',
 'Economic think-tank turned political party focused on economic reform.',
 2002, 'Liberalism, Economic reform',
 NULL, NULL),

(2015, 'Together — Civic Serbia',
 'ZAJEDNO',
 'Zajedno. Opposition coalition of the 1990s opposing Milosevic.',
 1996, 'Liberal democracy, Anti-authoritarianism',
 NULL, NULL),

(2016, 'Democratic Opposition of Serbia',
 'DOS',
 'Demokratska opozicija Srbije. Broad coalition that ousted Milosevic in 2000.',
 1999, 'Liberal democracy, Anti-authoritarianism',
 2009, NULL),

(2017, 'New Democratic Party Serbia',
 'NDP-SRB',
 'Split from the Democratic Party, merged back later.',
 2012, 'Social Democracy, Liberalism',
 NULL, NULL),

(2018, 'Party of United Pensioners of Serbia',
 'PUPS',
 'Partija ujedinjenih penzionera Srbije. Pensioners interest party.',
 2005, 'Pensioners interests, Centrism',
 NULL, NULL),

(2019, 'Movement of Socialists',
 'PS',
 'Pokret socijalista. Nationalist socialist party.',
 2008, 'Socialism, Serbian nationalism',
 NULL, NULL),

(2020, 'People''s Party Serbia',
 'NARODNA',
 'Narodna stranka. Centre-right party founded by former DS members.',
 2014, 'Conservatism, Pro-EU, Christian Democracy',
 2015, NULL)

ON CONFLICT (name) DO NOTHING;


INSERT INTO election_cycle (cycle_id, election_id, round_number)
VALUES

-- ── Parliamentary — single round each ────────────────
(101, 1,  1),   -- Parliamentary 2006
(102, 2,  1),   -- Parliamentary 2008
(103, 3,  1),   -- Parliamentary 2011
(104, 4,  1),   -- Parliamentary 2014
(105, 5,  1),   -- Parliamentary 2016
(106, 6,  1),   -- Parliamentary 2019
(107, 7,  1),   -- Parliamentary 2020
(108, 8,  1),   -- Parliamentary 2024

-- ── Presidential — two rounds each ───────────────────
(201, 9,  1),   -- Presidential 2009 Round 1 (22 Mar)
(202, 9,  2),   -- Presidential 2009 Round 2 (05 Apr)
(203, 10, 1),   -- Presidential 2014 Round 1 (27 Apr)
(204, 10, 2),   -- Presidential 2014 Round 2 (25 May) -- quorum not met first attempt
(205, 11, 1),   -- Presidential 2019 Round 1 (21 Apr)
(206, 11, 2),   -- Presidential 2019 Round 2 (05 May)
(207, 12, 1),   -- Presidential 2024 Round 1 (08 May)
(208, 12, 2),   -- Presidential 2024 Round 2 (22 May)

-- ── Local — two rounds each ───────────────────────────
(301, 13, 1),   -- Local 2013 Round 1 (24 Mar)
(302, 13, 2),   -- Local 2013 Round 2 (07 Apr)
(303, 14, 1),   -- Local 2017 Round 1 (15 Oct)
(304, 14, 2),   -- Local 2017 Round 2 (29 Oct)
(305, 15, 1),   -- Local 2021 Round 1 (17 Oct)
(306, 15, 2),   -- Local 2021 Round 2 (31 Oct)

-- ── Referendums — single round each ──────────────────
(401, 16, 1),   -- Independence Referendum 1991
(402, 17, 1)    -- Prespa Agreement Referendum 2018

ON CONFLICT (election_id, round_number) DO NOTHING;

INSERT INTO party_coalition (coalition_id, name, election_id)
VALUES

-- ── Parliamentary 2006 (election_id=1) ───────────────
(1001, 'For Macedonia',            1),   -- VMRO-DPMNE led
(1002, 'For European Future',      1),   -- SDSM + LDP
(1003, 'Democratic Renewal',       1),   -- DUI led

-- ── Parliamentary 2008 (election_id=2) ───────────────
(2001, 'For a Better Macedonia',   2),   -- VMRO-DPMNE led
(2002, 'Sun - Coalition for Europe', 2), -- SDSM led

-- ── Parliamentary 2011 (election_id=3) ───────────────
(3001, 'For a Better Macedonia',   3),   -- VMRO-DPMNE led
(3002, 'European Front',           3),   -- SDSM + BDI

-- ── Parliamentary 2014 (election_id=4) ───────────────
(4001, 'For a Better Macedonia',   4),   -- VMRO-DPMNE led
(4002, 'Coalition for a Better Macedonia', 4), -- SDSM led

-- ── Parliamentary 2016 (election_id=5) ───────────────
(5001, 'For Our Macedonia',        5),   -- VMRO-DPMNE led
(5002, 'For European Future',      5),   -- SDSM led

-- ── Parliamentary 2019 (election_id=6) ───────────────
(6001, 'Renewal of Macedonia',     6),   -- VMRO-DPMNE led
(6002, 'European Front',           6),   -- SDSM + DUI

-- ── Parliamentary 2020 (election_id=7) ───────────────
(7001, 'We Can',                   7),   -- SDSM + DUI
(7002, 'Renewal of Macedonia',     7),   -- VMRO-DPMNE led

-- ── Parliamentary 2024 (election_id=8) ───────────────
(8001, 'Your Macedonia',           8),   -- VMRO-DPMNE + DPA + others
(8002, 'Europe Now',               8),   -- SDSM + DUI

-- ── Local 2013 (election_id=13) ──────────────────────
(13001, 'Together for Macedonia',  13),  -- VMRO-DPMNE local coalition
(13002, 'For Our Cities',          13),  -- SDSM local coalition

-- ── Local 2017 (election_id=14) ──────────────────────
(14001, 'For Our Macedonia',       14),  -- VMRO-DPMNE local coalition
(14002, 'For European Macedonia',  14),  -- SDSM local coalition

-- ── Local 2021 (election_id=15) ──────────────────────
(15001, 'Together We Can',         15),  -- SDSM + DUI local coalition
(15002, 'For Macedonia',           15)   -- VMRO-DPMNE local coalition

ON CONFLICT (name) DO NOTHING;


INSERT INTO electoral_district (district_id, election_id, region_id, seats_available)
VALUES

-- ══════════════════════════════════════════════════════
-- PARLIAMENTARY 2006 (election_id=1)
-- ══════════════════════════════════════════════════════
(10001, 1, 1000, 35),  -- Skopje
(10002, 1, 1001, 10),  -- Eastern
(10003, 1, 1002, 12),  -- Northeastern
(10004, 1, 1003, 15),  -- Pelagonia
(10005, 1, 1004, 20),  -- Polog
(10006, 1, 1005, 10),  -- Southeastern
(10007, 1, 1006, 12),  -- Southwestern
(10008, 1, 1007,  6),  -- Vardar

-- ══════════════════════════════════════════════════════
-- PARLIAMENTARY 2008 (election_id=2)
-- ══════════════════════════════════════════════════════
(20001, 2, 1000, 35),
(20002, 2, 1001, 10),
(20003, 2, 1002, 12),
(20004, 2, 1003, 15),
(20005, 2, 1004, 20),
(20006, 2, 1005, 10),
(20007, 2, 1006, 12),
(20008, 2, 1007,  6),

-- ══════════════════════════════════════════════════════
-- PARLIAMENTARY 2011 (election_id=3)
-- ══════════════════════════════════════════════════════
(30001, 3, 1000, 35),
(30002, 3, 1001, 10),
(30003, 3, 1002, 12),
(30004, 3, 1003, 15),
(30005, 3, 1004, 20),
(30006, 3, 1005, 10),
(30007, 3, 1006, 12),
(30008, 3, 1007,  6),

-- ══════════════════════════════════════════════════════
-- PARLIAMENTARY 2014 (election_id=4)
-- ══════════════════════════════════════════════════════
(40001, 4, 1000, 35),
(40002, 4, 1001, 10),
(40003, 4, 1002, 12),
(40004, 4, 1003, 15),
(40005, 4, 1004, 20),
(40006, 4, 1005, 10),
(40007, 4, 1006, 12),
(40008, 4, 1007,  6),

-- ══════════════════════════════════════════════════════
-- PARLIAMENTARY 2016 (election_id=5)
-- ══════════════════════════════════════════════════════
(50001, 5, 1000, 35),
(50002, 5, 1001, 10),
(50003, 5, 1002, 12),
(50004, 5, 1003, 15),
(50005, 5, 1004, 20),
(50006, 5, 1005, 10),
(50007, 5, 1006, 12),
(50008, 5, 1007,  6),

-- ══════════════════════════════════════════════════════
-- PARLIAMENTARY 2019 (election_id=6)
-- ══════════════════════════════════════════════════════
(60001, 6, 1000, 35),
(60002, 6, 1001, 10),
(60003, 6, 1002, 12),
(60004, 6, 1003, 15),
(60005, 6, 1004, 20),
(60006, 6, 1005, 10),
(60007, 6, 1006, 12),
(60008, 6, 1007,  6),

-- ══════════════════════════════════════════════════════
-- PARLIAMENTARY 2020 (election_id=7)
-- ══════════════════════════════════════════════════════
(70001, 7, 1000, 35),
(70002, 7, 1001, 10),
(70003, 7, 1002, 12),
(70004, 7, 1003, 15),
(70005, 7, 1004, 20),
(70006, 7, 1005, 10),
(70007, 7, 1006, 12),
(70008, 7, 1007,  6),

-- ══════════════════════════════════════════════════════
-- PARLIAMENTARY 2024 (election_id=8)
-- ══════════════════════════════════════════════════════
(80001, 8, 1000, 35),
(80002, 8, 1001, 10),
(80003, 8, 1002, 12),
(80004, 8, 1003, 15),
(80005, 8, 1004, 20),
(80006, 8, 1005, 10),
(80007, 8, 1006, 12),
(80008, 8, 1007,  6),

-- ══════════════════════════════════════════════════════
-- PRESIDENTIAL — whole country, no seat count
-- ══════════════════════════════════════════════════════
(90001,  9,  141, NULL),  -- Presidential 2009
(90002, 10,  141, NULL),  -- Presidential 2014
(90003, 11,  141, NULL),  -- Presidential 2019
(90004, 12,  141, NULL),  -- Presidential 2024

-- ══════════════════════════════════════════════════════
-- LOCAL ELECTIONS — one district per planning region
-- ══════════════════════════════════════════════════════

-- Local 2013 (election_id=13)
(130001, 13, 1000, NULL),
(130002, 13, 1001, NULL),
(130003, 13, 1002, NULL),
(130004, 13, 1003, NULL),
(130005, 13, 1004, NULL),
(130006, 13, 1005, NULL),
(130007, 13, 1006, NULL),
(130008, 13, 1007, NULL),

-- Local 2017 (election_id=14)
(140001, 14, 1000, NULL),
(140002, 14, 1001, NULL),
(140003, 14, 1002, NULL),
(140004, 14, 1003, NULL),
(140005, 14, 1004, NULL),
(140006, 14, 1005, NULL),
(140007, 14, 1006, NULL),
(140008, 14, 1007, NULL),

-- Local 2021 (election_id=15)
(150001, 15, 1000, NULL),
(150002, 15, 1001, NULL),
(150003, 15, 1002, NULL),
(150004, 15, 1003, NULL),
(150005, 15, 1004, NULL),
(150006, 15, 1005, NULL),
(150007, 15, 1006, NULL),
(150008, 15, 1007, NULL),

-- ══════════════════════════════════════════════════════
-- REFERENDUMS — whole country, no seat count
-- ══════════════════════════════════════════════════════
(160001, 16, 141, NULL),  -- Independence Referendum 1991
(170001, 17, 141, NULL)   -- Prespa Agreement Referendum 2018

ON CONFLICT (election_id, region_id) DO NOTHING;


INSERT INTO coalition_member (coalition_member_id, coalition_id, party_id)
VALUES

-- ── 2006: "For Macedonia" — VMRO-DPMNE led ───────────
(100101, 1001, 1001),  -- VMRO-DPMNE
(100102, 1001, 1009),  -- VMRO-NP
(100103, 1001, 1012),  -- Socialist Party of Macedonia

-- ── 2006: "For European Future" — SDSM led ───────────
(100201, 1002, 1002),  -- SDSM
(100202, 1002, 1010),  -- LDP-MK

-- ── 2006: "Democratic Renewal" — DUI led ─────────────
(100301, 1003, 1003),  -- DUI
(100302, 1003, 1011),  -- PDP

-- ── 2008: "For a Better Macedonia" — VMRO-DPMNE ──────
(200101, 2001, 1001),  -- VMRO-DPMNE
(200102, 2001, 1009),  -- VMRO-NP
(200103, 2001, 1015),  -- DOM

-- ── 2008: "Sun - Coalition for Europe" — SDSM ─────────
(200201, 2002, 1002),  -- SDSM
(200202, 2002, 1010),  -- LDP-MK
(200203, 2002, 1013),  -- Liberal Party of Macedonia

-- ── 2011: "European Front" — SDSM + DUI ──────────────
(300201, 3002, 1002),  -- SDSM
(300202, 3002, 1003),  -- DUI

-- ── 2013 Local: "Together for Macedonia" ─────────────
(1300101, 13001, 1001), -- VMRO-DPMNE
(1300102, 13001, 1009), -- VMRO-NP

-- ── 2013 Local: "For Our Cities" ─────────────────────
(1300201, 13002, 1002), -- SDSM
(1300202, 13002, 1010), -- LDP-MK

-- ── 2014: "Coalition for Better Macedonia" — SDSM ────
(400201, 4002, 1002),  -- SDSM
(400202, 4002, 1005),  -- BESA
(400203, 4002, 1010),  -- LDP-MK

-- ── 2016: "For Our Macedonia" — VMRO-DPMNE ───────────
(500101, 5001, 1001),  -- VMRO-DPMNE
(500102, 5001, 1004),  -- DPA

-- ── 2017 Local: "For European Macedonia" — SDSM ──────
(1400201, 14002, 1002), -- SDSM
(1400202, 14002, 1003), -- DUI
(1400203, 14002, 1008), -- GROM

-- ── 2019: "Renewal of Macedonia" — VMRO-DPMNE ────────
(600101, 6001, 1001),  -- VMRO-DPMNE
(600102, 6001, 1004),  -- DPA
(600103, 6001, 1009),  -- VMRO-NP

-- ── 2020: "We Can" — SDSM + DUI ──────────────────────
(700101, 7001, 1002),  -- SDSM
(700102, 7001, 1003),  -- DUI
(700103, 7001, 1008),  -- GROM

-- ── 2021 Local: "Together We Can" ────────────────────
(1500101, 15001, 1002), -- SDSM
(1500102, 15001, 1003), -- DUI
(1500103, 15001, 1006), -- Alliance for Albanians

-- ── 2024: "Your Macedonia" — VMRO-DPMNE ──────────────
(800101, 8001, 1001),  -- VMRO-DPMNE
(800102, 8001, 1004),  -- DPA
(800103, 8001, 1005),  -- BESA
(800104, 8001, 1009),  -- VMRO-NP

-- ── 2024: "Europe Now" — SDSM + DUI ──────────────────
(800201, 8002, 1002),  -- SDSM
(800202, 8002, 1003)   -- DUI

ON CONFLICT (coalition_id, party_id) DO NOTHING;


INSERT INTO political_entity (
    entity_id, name, type,
    party_id, coalition_id, candidate_id,
    is_independent
)
VALUES

-- ══════════════════════════════════════════════════════
-- NMK PARTY ENTITIES
-- ══════════════════════════════════════════════════════
(1001, 'VMRO-DPMNE',                        'PARTY', 1001, NULL, NULL, FALSE),
(1002, 'SDSM',                              'PARTY', 1002, NULL, NULL, FALSE),
(1003, 'DUI',                               'PARTY', 1003, NULL, NULL, FALSE),
(1004, 'DPA',                               'PARTY', 1004, NULL, NULL, FALSE),
(1005, 'BESA Movement',                     'PARTY', 1005, NULL, NULL, FALSE),
(1006, 'Alliance for Albanians',            'PARTY', 1006, NULL, NULL, FALSE),
(1007, 'Levica',                            'PARTY', 1007, NULL, NULL, FALSE),
(1008, 'GROM',                              'PARTY', 1008, NULL, NULL, FALSE),
(1009, 'VMRO-NP',                           'PARTY', 1009, NULL, NULL, FALSE),
(1010, 'LDP Macedonia',                     'PARTY', 1010, NULL, NULL, FALSE),
(1011, 'PDP',                               'PARTY', 1011, NULL, NULL, FALSE),
(1012, 'Socialist Party of Macedonia',      'PARTY', 1012, NULL, NULL, FALSE),
(1013, 'Liberal Party of Macedonia',        'PARTY', 1013, NULL, NULL, FALSE),
(1014, 'NDR',                               'PARTY', 1014, NULL, NULL, FALSE),
(1015, 'DOM',                               'PARTY', 1015, NULL, NULL, FALSE),

-- ══════════════════════════════════════════════════════
-- SERBIA PARTY ENTITIES
-- ══════════════════════════════════════════════════════
(2001, 'SNS',                               'PARTY', 2001, NULL, NULL, FALSE),
(2002, 'SPS',                               'PARTY', 2002, NULL, NULL, FALSE),
(2003, 'DS Serbia',                         'PARTY', 2003, NULL, NULL, FALSE),
(2004, 'DSS',                               'PARTY', 2004, NULL, NULL, FALSE),
(2005, 'SRS',                               'PARTY', 2005, NULL, NULL, FALSE),
(2006, 'SPO',                               'PARTY', 2006, NULL, NULL, FALSE),
(2007, 'SSP',                               'PARTY', 2007, NULL, NULL, FALSE),
(2008, 'Dveri',                             'PARTY', 2008, NULL, NULL, FALSE),
(2009, 'Nova Srbija',                       'PARTY', 2009, NULL, NULL, FALSE),
(2010, 'LDP Serbia',                        'PARTY', 2010, NULL, NULL, FALSE),
(2011, 'SVM',                               'PARTY', 2011, NULL, NULL, FALSE),
(2012, 'SDPS',                              'PARTY', 2012, NULL, NULL, FALSE),
(2013, 'BDZ',                               'PARTY', 2013, NULL, NULL, FALSE),
(2014, 'G17 Plus',                          'PARTY', 2014, NULL, NULL, FALSE),
(2015, 'Zajedno',                           'PARTY', 2015, NULL, NULL, FALSE),
(2016, 'DOS',                               'PARTY', 2016, NULL, NULL, FALSE),
(2017, 'NDP Serbia',                        'PARTY', 2017, NULL, NULL, FALSE),
(2018, 'PUPS',                              'PARTY', 2018, NULL, NULL, FALSE),
(2019, 'Movement of Socialists',            'PARTY', 2019, NULL, NULL, FALSE),
(2020, 'Narodna stranka',                   'PARTY', 2020, NULL, NULL, FALSE),

-- ══════════════════════════════════════════════════════
-- COALITION ENTITIES
-- ══════════════════════════════════════════════════════
(3001, 'For Macedonia (2006)',               'COALITION', NULL, 1001, NULL, FALSE),
(3002, 'For European Future (2006)',         'COALITION', NULL, 1002, NULL, FALSE),
(3003, 'Democratic Renewal (2006)',          'COALITION', NULL, 1003, NULL, FALSE),
(3004, 'For a Better Macedonia (2008)',      'COALITION', NULL, 2001, NULL, FALSE),
(3005, 'Sun - Coalition for Europe (2008)', 'COALITION', NULL, 2002, NULL, FALSE),
(3006, 'European Front (2011)',              'COALITION', NULL, 3002, NULL, FALSE),
(3007, 'Together for Macedonia (2013)',      'COALITION', NULL, 13001, NULL, FALSE),
(3008, 'For Our Cities (2013)',              'COALITION', NULL, 13002, NULL, FALSE),
(3009, 'Coalition for Better Macedonia (2014)', 'COALITION', NULL, 4002, NULL, FALSE),
(3010, 'For Our Macedonia (2016)',           'COALITION', NULL, 5001, NULL, FALSE),
(3011, 'For European Macedonia (2017)',      'COALITION', NULL, 14002, NULL, FALSE),
(3012, 'Renewal of Macedonia (2019)',        'COALITION', NULL, 6001, NULL, FALSE),
(3013, 'We Can (2020)',                      'COALITION', NULL, 7001, NULL, FALSE),
(3014, 'Together We Can (2021)',             'COALITION', NULL, 15001, NULL, FALSE),
(3015, 'Your Macedonia (2024)',              'COALITION', NULL, 8001, NULL, FALSE),
(3016, 'Europe Now (2024)',                  'COALITION', NULL, 8002, NULL, FALSE),

-- ══════════════════════════════════════════════════════
-- INDEPENDENT CANDIDATE ENTITIES
-- A small set of independents to keep the data realistic.
-- Candidate IDs 1–10 exist in your candidate table
-- (generated from person table rows).
-- ══════════════════════════════════════════════════════
(9001, 'Independent Candidate 1',  'INDEPENDENT', NULL, NULL, 1,  TRUE),
(9002, 'Independent Candidate 2',  'INDEPENDENT', NULL, NULL, 2,  TRUE),
(9003, 'Independent Candidate 3',  'INDEPENDENT', NULL, NULL, 3,  TRUE),
(9004, 'Independent Candidate 4',  'INDEPENDENT', NULL, NULL, 4,  TRUE),
(9005, 'Independent Candidate 5',  'INDEPENDENT', NULL, NULL, 5,  TRUE),
(9006, 'Independent Candidate 6',  'INDEPENDENT', NULL, NULL, 6,  TRUE),
(9007, 'Independent Candidate 7',  'INDEPENDENT', NULL, NULL, 7,  TRUE),
(9008, 'Independent Candidate 8',  'INDEPENDENT', NULL, NULL, 8,  TRUE),
(9009, 'Independent Candidate 9',  'INDEPENDENT', NULL, NULL, 9,  TRUE),
(9010, 'Independent Candidate 10', 'INDEPENDENT', NULL, NULL, 10, TRUE)

ON CONFLICT (name) DO NOTHING;


INSERT INTO political_party (
    party_id, name, abbreviation, description,
    founded_year, ideology, leader_id, parent_party_id
)
VALUES (
    2003,
    'Democratic Party Serbia',
    'DS',
    'Demokratska stranka. Centre-left social democratic party.',
    1990,
    'Social Democracy, Liberalism, Pro-EU',
    2003,
    NULL
);
UPDATE political_entity
SET name = 'DS Serbia'
WHERE entity_id = 2003;

INSERT INTO election_participant (participant_id, district_id, entity_id)
VALUES

-- ══════════════════════════════════════════════════════
-- PARLIAMENTARY 2006 — coalitions + standalone parties
-- Competing: For Macedonia(3001), For European Future(3002),
--            Democratic Renewal(3003), DPA(1004), PDP(1011)
-- Same entities compete in all 8 districts
-- ══════════════════════════════════════════════════════
-- District Skopje (10001)
(1000101, 10001, 3001),(1000102, 10001, 3002),(1000103, 10001, 3003),
(1000104, 10001, 1004),(1000105, 10001, 1011),
-- District Eastern (10002)
(1000201, 10002, 3001),(1000202, 10002, 3002),(1000203, 10002, 3003),
(1000204, 10002, 1004),(1000205, 10002, 1011),
-- District Northeastern (10003)
(1000301, 10003, 3001),(1000302, 10003, 3002),(1000303, 10003, 3003),
(1000304, 10003, 1004),(1000305, 10003, 1011),
-- District Pelagonia (10004)
(1000401, 10004, 3001),(1000402, 10004, 3002),(1000403, 10004, 3003),
(1000404, 10004, 1004),(1000405, 10004, 1011),
-- District Polog (10005)
(1000501, 10005, 3001),(1000502, 10005, 3002),(1000503, 10005, 3003),
(1000504, 10005, 1004),(1000505, 10005, 1011),
-- District Southeastern (10006)
(1000601, 10006, 3001),(1000602, 10006, 3002),(1000603, 10006, 3003),
(1000604, 10006, 1004),(1000605, 10006, 1011),
-- District Southwestern (10007)
(1000701, 10007, 3001),(1000702, 10007, 3002),(1000703, 10007, 3003),
(1000704, 10007, 1004),(1000705, 10007, 1011),
-- District Vardar (10008)
(1000801, 10008, 3001),(1000802, 10008, 3002),(1000803, 10008, 3003),
(1000804, 10008, 1004),(1000805, 10008, 1011),

-- ══════════════════════════════════════════════════════
-- PARLIAMENTARY 2008
-- Competing: For a Better Macedonia(3004), Sun Coalition(3005),
--            DUI(1003), DPA(1004), BESA(1005)
-- ══════════════════════════════════════════════════════
(2000101, 20001, 3004),(2000102, 20001, 3005),(2000103, 20001, 1003),
(2000104, 20001, 1004),(2000105, 20001, 1005),
(2000201, 20002, 3004),(2000202, 20002, 3005),(2000203, 20002, 1003),
(2000204, 20002, 1004),(2000205, 20002, 1005),
(2000301, 20003, 3004),(2000302, 20003, 3005),(2000303, 20003, 1003),
(2000304, 20003, 1004),(2000305, 20003, 1005),
(2000401, 20004, 3004),(2000402, 20004, 3005),(2000403, 20004, 1003),
(2000404, 20004, 1004),(2000405, 20004, 1005),
(2000501, 20005, 3004),(2000502, 20005, 3005),(2000503, 20005, 1003),
(2000504, 20005, 1004),(2000505, 20005, 1005),
(2000601, 20006, 3004),(2000602, 20006, 3005),(2000603, 20006, 1003),
(2000604, 20006, 1004),(2000605, 20006, 1005),
(2000701, 20007, 3004),(2000702, 20007, 3005),(2000703, 20007, 1003),
(2000704, 20007, 1004),(2000705, 20007, 1005),
(2000801, 20008, 3004),(2000802, 20008, 3005),(2000803, 20008, 1003),
(2000804, 20008, 1004),(2000805, 20008, 1005),

-- ══════════════════════════════════════════════════════
-- PARLIAMENTARY 2011
-- Competing: VMRO-DPMNE(1001), European Front(3006),
--            DPA(1004), BESA(1005), Levica(1007)
-- ══════════════════════════════════════════════════════
(3000101, 30001, 1001),(3000102, 30001, 3006),(3000103, 30001, 1004),
(3000104, 30001, 1005),(3000105, 30001, 1007),
(3000201, 30002, 1001),(3000202, 30002, 3006),(3000203, 30002, 1004),
(3000204, 30002, 1005),(3000205, 30002, 1007),
(3000301, 30003, 1001),(3000302, 30003, 3006),(3000303, 30003, 1004),
(3000304, 30003, 1005),(3000305, 30003, 1007),
(3000401, 30004, 1001),(3000402, 30004, 3006),(3000403, 30004, 1004),
(3000404, 30004, 1005),(3000405, 30004, 1007),
(3000501, 30005, 1001),(3000502, 30005, 3006),(3000503, 30005, 1004),
(3000504, 30005, 1005),(3000505, 30005, 1007),
(3000601, 30006, 1001),(3000602, 30006, 3006),(3000603, 30006, 1004),
(3000604, 30006, 1005),(3000605, 30006, 1007),
(3000701, 30007, 1001),(3000702, 30007, 3006),(3000703, 30007, 1004),
(3000704, 30007, 1005),(3000705, 30007, 1007),
(3000801, 30008, 1001),(3000802, 30008, 3006),(3000803, 30008, 1004),
(3000804, 30008, 1005),(3000805, 30008, 1007),

-- ══════════════════════════════════════════════════════
-- PARLIAMENTARY 2014
-- Competing: VMRO-DPMNE(1001), Better Macedonia(3009),
--            DPA(1004), BESA(1005), Levica(1007), AA(1006)
-- ══════════════════════════════════════════════════════
(4000101, 40001, 1001),(4000102, 40001, 3009),(4000103, 40001, 1004),
(4000104, 40001, 1005),(4000105, 40001, 1007),(4000106, 40001, 1006),
(4000201, 40002, 1001),(4000202, 40002, 3009),(4000203, 40002, 1004),
(4000204, 40002, 1005),(4000205, 40002, 1007),(4000206, 40002, 1006),
(4000301, 40003, 1001),(4000302, 40003, 3009),(4000303, 40003, 1004),
(4000304, 40003, 1005),(4000305, 40003, 1007),(4000306, 40003, 1006),
(4000401, 40004, 1001),(4000402, 40004, 3009),(4000403, 40004, 1004),
(4000404, 40004, 1005),(4000405, 40004, 1007),(4000406, 40004, 1006),
(4000501, 40005, 1001),(4000502, 40005, 3009),(4000503, 40005, 1004),
(4000504, 40005, 1005),(4000505, 40005, 1007),(4000506, 40005, 1006),
(4000601, 40006, 1001),(4000602, 40006, 3009),(4000603, 40006, 1004),
(4000604, 40006, 1005),(4000605, 40006, 1007),(4000606, 40006, 1006),
(4000701, 40007, 1001),(4000702, 40007, 3009),(4000703, 40007, 1004),
(4000704, 40007, 1005),(4000705, 40007, 1007),(4000706, 40007, 1006),
(4000801, 40008, 1001),(4000802, 40008, 3009),(4000803, 40008, 1004),
(4000804, 40008, 1005),(4000805, 40008, 1007),(4000806, 40008, 1006),

-- ══════════════════════════════════════════════════════
-- PARLIAMENTARY 2016
-- Competing: For Our Macedonia(3010), SDSM(1002),
--            DUI(1003), BESA(1005), Levica(1007), AA(1006)
-- ══════════════════════════════════════════════════════
(5000101, 50001, 3010),(5000102, 50001, 1002),(5000103, 50001, 1003),
(5000104, 50001, 1005),(5000105, 50001, 1007),(5000106, 50001, 1006),
(5000201, 50002, 3010),(5000202, 50002, 1002),(5000203, 50002, 1003),
(5000204, 50002, 1005),(5000205, 50002, 1007),(5000206, 50002, 1006),
(5000301, 50003, 3010),(5000302, 50003, 1002),(5000303, 50003, 1003),
(5000304, 50003, 1005),(5000305, 50003, 1007),(5000306, 50003, 1006),
(5000401, 50004, 3010),(5000402, 50004, 1002),(5000403, 50004, 1003),
(5000404, 50004, 1005),(5000405, 50004, 1007),(5000406, 50004, 1006),
(5000501, 50005, 3010),(5000502, 50005, 1002),(5000503, 50005, 1003),
(5000504, 50005, 1005),(5000505, 50005, 1007),(5000506, 50005, 1006),
(5000601, 50006, 3010),(5000602, 50006, 1002),(5000603, 50006, 1003),
(5000604, 50006, 1005),(5000605, 50006, 1007),(5000606, 50006, 1006),
(5000701, 50007, 3010),(5000702, 50007, 1002),(5000703, 50007, 1003),
(5000704, 50007, 1005),(5000705, 50007, 1007),(5000706, 50007, 1006),
(5000801, 50008, 3010),(5000802, 50008, 1002),(5000803, 50008, 1003),
(5000804, 50008, 1005),(5000805, 50008, 1007),(5000806, 50008, 1006),

-- ══════════════════════════════════════════════════════
-- PARLIAMENTARY 2019
-- Competing: Renewal of Macedonia(3012), SDSM(1002),
--            DUI(1003), BESA(1005), Levica(1007), AA(1006)
-- ══════════════════════════════════════════════════════
(6000101, 60001, 3012),(6000102, 60001, 1002),(6000103, 60001, 1003),
(6000104, 60001, 1005),(6000105, 60001, 1007),(6000106, 60001, 1006),
(6000201, 60002, 3012),(6000202, 60002, 1002),(6000203, 60002, 1003),
(6000204, 60002, 1005),(6000205, 60002, 1007),(6000206, 60002, 1006),
(6000301, 60003, 3012),(6000302, 60003, 1002),(6000303, 60003, 1003),
(6000304, 60003, 1005),(6000305, 60003, 1007),(6000306, 60003, 1006),
(6000401, 60004, 3012),(6000402, 60004, 1002),(6000403, 60004, 1003),
(6000404, 60004, 1005),(6000405, 60004, 1007),(6000406, 60004, 1006),
(6000501, 60005, 3012),(6000502, 60005, 1002),(6000503, 60005, 1003),
(6000504, 60005, 1005),(6000505, 60005, 1007),(6000506, 60005, 1006),
(6000601, 60006, 3012),(6000602, 60006, 1002),(6000603, 60006, 1003),
(6000604, 60006, 1005),(6000605, 60006, 1007),(6000606, 60006, 1006),
(6000701, 60007, 3012),(6000702, 60007, 1002),(6000703, 60007, 1003),
(6000704, 60007, 1005),(6000705, 60007, 1007),(6000706, 60007, 1006),
(6000801, 60008, 3012),(6000802, 60008, 1002),(6000803, 60008, 1003),
(6000804, 60008, 1005),(6000805, 60008, 1007),(6000806, 60008, 1006),

-- ══════════════════════════════════════════════════════
-- PARLIAMENTARY 2020
-- Competing: We Can(3013), Renewal of Macedonia(3012 reused as VMRO standalone),
--            VMRO-DPMNE(1001), DPA(1004), BESA(1005), Levica(1007), AA(1006)
-- ══════════════════════════════════════════════════════
(7000101, 70001, 3013),(7000102, 70001, 1001),(7000103, 70001, 1004),
(7000104, 70001, 1005),(7000105, 70001, 1007),(7000106, 70001, 1006),
(7000201, 70002, 3013),(7000202, 70002, 1001),(7000203, 70002, 1004),
(7000204, 70002, 1005),(7000205, 70002, 1007),(7000206, 70002, 1006),
(7000301, 70003, 3013),(7000302, 70003, 1001),(7000303, 70003, 1004),
(7000304, 70003, 1005),(7000305, 70003, 1007),(7000306, 70003, 1006),
(7000401, 70004, 3013),(7000402, 70004, 1001),(7000403, 70004, 1004),
(7000404, 70004, 1005),(7000405, 70004, 1007),(7000406, 70004, 1006),
(7000501, 70005, 3013),(7000502, 70005, 1001),(7000503, 70005, 1004),
(7000504, 70005, 1005),(7000505, 70005, 1007),(7000506, 70005, 1006),
(7000601, 70006, 3013),(7000602, 70006, 1001),(7000603, 70006, 1004),
(7000604, 70006, 1005),(7000605, 70006, 1007),(7000606, 70006, 1006),
(7000701, 70007, 3013),(7000702, 70007, 1001),(7000703, 70007, 1004),
(7000704, 70007, 1005),(7000705, 70007, 1007),(7000706, 70007, 1006),
(7000801, 70008, 3013),(7000802, 70008, 1001),(7000803, 70008, 1004),
(7000804, 70008, 1005),(7000805, 70008, 1007),(7000806, 70008, 1006),

-- ══════════════════════════════════════════════════════
-- PARLIAMENTARY 2024
-- Competing: Your Macedonia(3015), Europe Now(3016),
--            BESA(1005), Levica(1007), AA(1006)
-- ══════════════════════════════════════════════════════
(8000101, 80001, 3015),(8000102, 80001, 3016),(8000103, 80001, 1005),
(8000104, 80001, 1007),(8000105, 80001, 1006),
(8000201, 80002, 3015),(8000202, 80002, 3016),(8000203, 80002, 1005),
(8000204, 80002, 1007),(8000205, 80002, 1006),
(8000301, 80003, 3015),(8000302, 80003, 3016),(8000303, 80003, 1005),
(8000304, 80003, 1007),(8000305, 80003, 1006),
(8000401, 80004, 3015),(8000402, 80004, 3016),(8000403, 80004, 1005),
(8000404, 80004, 1007),(8000405, 80004, 1006),
(8000501, 80005, 3015),(8000502, 80005, 3016),(8000503, 80005, 1005),
(8000504, 80005, 1007),(8000505, 80005, 1006),
(8000601, 80006, 3015),(8000602, 80006, 3016),(8000603, 80006, 1005),
(8000604, 80006, 1007),(8000605, 80006, 1006),
(8000701, 80007, 3015),(8000702, 80007, 3016),(8000703, 80007, 1005),
(8000704, 80007, 1007),(8000705, 80007, 1006),
(8000801, 80008, 3015),(8000802, 80008, 3016),(8000803, 80008, 1005),
(8000804, 80008, 1007),(8000805, 80008, 1006),

-- ══════════════════════════════════════════════════════
-- PRESIDENTIAL — whole country district, party entities
-- Each party nominates a presidential candidate
-- ══════════════════════════════════════════════════════
-- Presidential 2009 (90001)
(9000101, 90001, 1001),(9000102, 90001, 1002),(9000103, 90001, 1003),
-- Presidential 2014 (90002)
(9000201, 90002, 1001),(9000202, 90002, 1002),(9000203, 90002, 1003),
-- Presidential 2019 (90003)
(9000301, 90003, 1001),(9000302, 90003, 1002),(9000303, 90003, 1007),
-- Presidential 2024 (90004)
(9000401, 90004, 1001),(9000402, 90004, 1002),(9000403, 90004, 1007),

-- ══════════════════════════════════════════════════════
-- LOCAL 2013 — coalitions + standalone parties per region
-- ══════════════════════════════════════════════════════
(1300101,130001,3007),(1300102,130001,3008),(1300103,130001,1003),(1300104,130001,1004),
(1300201,130002,3007),(1300202,130002,3008),(1300203,130002,1003),(1300204,130002,1004),
(1300301,130003,3007),(1300302,130003,3008),(1300303,130003,1003),(1300304,130003,1004),
(1300401,130004,3007),(1300402,130004,3008),(1300403,130004,1003),(1300404,130004,1004),
(1300501,130005,3007),(1300502,130005,3008),(1300503,130005,1003),(1300504,130005,1004),
(1300601,130006,3007),(1300602,130006,3008),(1300603,130006,1003),(1300604,130006,1004),
(1300701,130007,3007),(1300702,130007,3008),(1300703,130007,1003),(1300704,130007,1004),
(1300801,130008,3007),(1300802,130008,3008),(1300803,130008,1003),(1300804,130008,1004),

-- ══════════════════════════════════════════════════════
-- LOCAL 2017
-- ══════════════════════════════════════════════════════
(1400101,140001,1001),(1400102,140001,3011),(1400103,140001,1003),(1400104,140001,1005),
(1400201,140002,1001),(1400202,140002,3011),(1400203,140002,1003),(1400204,140002,1005),
(1400301,140003,1001),(1400302,140003,3011),(1400303,140003,1003),(1400304,140003,1005),
(1400401,140004,1001),(1400402,140004,3011),(1400403,140004,1003),(1400404,140004,1005),
(1400501,140005,1001),(1400502,140005,3011),(1400503,140005,1003),(1400504,140005,1005),
(1400601,140006,1001),(1400602,140006,3011),(1400603,140006,1003),(1400604,140006,1005),
(1400701,140007,1001),(1400702,140007,3011),(1400703,140007,1003),(1400704,140007,1005),
(1400801,140008,1001),(1400802,140008,3011),(1400803,140008,1003),(1400804,140008,1005),

-- ══════════════════════════════════════════════════════
-- LOCAL 2021
-- ══════════════════════════════════════════════════════
(1500101,150001,3014),(1500102,150001,1001),(1500103,150001,1004),(1500104,150001,1007),
(1500201,150002,3014),(1500202,150002,1001),(1500203,150002,1004),(1500204,150002,1007),
(1500301,150003,3014),(1500302,150003,1001),(1500303,150003,1004),(1500304,150003,1007),
(1500401,150004,3014),(1500402,150004,1001),(1500403,150004,1004),(1500404,150004,1007),
(1500501,150005,3014),(1500502,150005,1001),(1500503,150005,1004),(1500504,150005,1007),
(1500601,150006,3014),(1500602,150006,1001),(1500603,150006,1004),(1500604,150006,1007),
(1500701,150007,3014),(1500702,150007,1001),(1500703,150007,1004),(1500704,150007,1007),
(1500801,150008,3014),(1500802,150008,1001),(1500803,150008,1004),(1500804,150008,1007)

ON CONFLICT (district_id, entity_id) DO NOTHING;


INSERT INTO candidate_list (list_id, election_id, entity_id)
VALUES

-- Parliamentary 2006 (election_id=1)
(10001,  1, 3001),(10002,  1, 3002),(10003,  1, 3003),
(10004,  1, 1004),(10005,  1, 1011),

-- Parliamentary 2008 (election_id=2)
(20001,  2, 3004),(20002,  2, 3005),(20003,  2, 1003),
(20004,  2, 1004),(20005,  2, 1005),

-- Parliamentary 2011 (election_id=3)
(30001,  3, 1001),(30002,  3, 3006),(30003,  3, 1004),
(30004,  3, 1005),(30005,  3, 1007),

-- Parliamentary 2014 (election_id=4)
(40001,  4, 1001),(40002,  4, 3009),(40003,  4, 1004),
(40004,  4, 1005),(40005,  4, 1007),(40006,  4, 1006),

-- Parliamentary 2016 (election_id=5)
(50001,  5, 3010),(50002,  5, 1002),(50003,  5, 1003),
(50004,  5, 1005),(50005,  5, 1007),(50006,  5, 1006),

-- Parliamentary 2019 (election_id=6)
(60001,  6, 3012),(60002,  6, 1002),(60003,  6, 1003),
(60004,  6, 1005),(60005,  6, 1007),(60006,  6, 1006),

-- Parliamentary 2020 (election_id=7)
(70001,  7, 3013),(70002,  7, 1001),(70003,  7, 1004),
(70004,  7, 1005),(70005,  7, 1007),(70006,  7, 1006),

-- Parliamentary 2024 (election_id=8)
(80001,  8, 3015),(80002,  8, 3016),(80003,  8, 1005),
(80004,  8, 1007),(80005,  8, 1006),

-- Presidential 2009 (election_id=9)
(90001,  9, 1001),(90002,  9, 1002),(90003,  9, 1003),

-- Presidential 2014 (election_id=10)
(100001, 10, 1001),(100002, 10, 1002),(100003, 10, 1003),

-- Presidential 2019 (election_id=11)
(110001, 11, 1001),(110002, 11, 1002),(110003, 11, 1007),

-- Presidential 2024 (election_id=12)
(120001, 12, 1001),(120002, 12, 1002),(120003, 12, 1007),

-- Local 2013 (election_id=13)
(130001, 13, 3007),(130002, 13, 3008),(130003, 13, 1003),(130004, 13, 1004),

-- Local 2017 (election_id=14)
(140001, 14, 1001),(140002, 14, 3011),(140003, 14, 1003),(140004, 14, 1005),

-- Local 2021 (election_id=15)
(150001, 15, 3014),(150002, 15, 1001),(150003, 15, 1004),(150004, 15, 1007)

ON CONFLICT (election_id, entity_id) DO NOTHING;


INSERT INTO candidate_party (candidate_party_id, candidate_id, party_id, election_id)

-- ── Parliamentary elections 1–8, parties 1001–1011 ───
SELECT
    e.election_id * 10000000
    + p.party_id  * 10000
    + ROW_NUMBER() OVER (PARTITION BY e.election_id, p.party_id ORDER BY c.candidate_id)
        AS candidate_party_id,
    c.candidate_id,
    p.party_id,
    e.election_id
FROM
    (SELECT election_id FROM election WHERE election_id BETWEEN 1 AND 8) e
CROSS JOIN
    (SELECT party_id FROM political_party
     WHERE  party_id IN (1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011)) p
JOIN
    candidate c ON c.candidate_id BETWEEN
        (p.party_id - 1001) * 1000 + 1
        AND
        (p.party_id - 1001) * 1000 + 120

UNION ALL

-- ── Presidential elections 9–12, 5 candidates per party
SELECT
    e.election_id * 10000000
    + p.party_id  * 10000
    + ROW_NUMBER() OVER (PARTITION BY e.election_id, p.party_id ORDER BY c.candidate_id)
        AS candidate_party_id,
    c.candidate_id,
    p.party_id,
    e.election_id
FROM
    (SELECT election_id FROM election WHERE election_id BETWEEN 9 AND 12) e
CROSS JOIN
    (SELECT party_id FROM political_party
     WHERE  party_id IN (1001,1002,1003,1007)) p
JOIN
    candidate c ON c.candidate_id BETWEEN
        (p.party_id - 1001) * 1000 + 121
        AND
        (p.party_id - 1001) * 1000 + 125

UNION ALL

-- ── Local elections 13–15, 80 candidates per party ───
SELECT
    e.election_id * 10000000
    + p.party_id  * 10000
    + ROW_NUMBER() OVER (PARTITION BY e.election_id, p.party_id ORDER BY c.candidate_id)
        AS candidate_party_id,
    c.candidate_id,
    p.party_id,
    e.election_id
FROM
    (SELECT election_id FROM election WHERE election_id BETWEEN 13 AND 15) e
CROSS JOIN
    (SELECT party_id FROM political_party
     WHERE  party_id IN (1001,1002,1003,1004,1005,1006,1007)) p
JOIN
    candidate c ON c.candidate_id BETWEEN
        (p.party_id - 1001) * 1000 + 126
        AND
        (p.party_id - 1001) * 1000 + 205

ON CONFLICT (candidate_id, party_id, election_id) DO NOTHING;

DO $$
DECLARE
    v_lists      INT;
    v_candidates INT;
BEGIN
    SELECT COUNT(*) INTO v_lists      FROM candidate_list;
    SELECT COUNT(*) INTO v_candidates FROM candidate;
    RAISE NOTICE 'Lists available    : %', v_lists;
    RAISE NOTICE 'Candidates available: %', v_candidates;
    RAISE NOTICE 'Expected output rows: %', v_candidates * 2;
    IF v_lists < 2 THEN
        RAISE EXCEPTION 'Need at least 2 lists — found %', v_lists;
    END IF;
    IF v_candidates < 1 THEN
        RAISE EXCEPTION 'No candidates found';
    END IF;
END $$;

CREATE TEMP TABLE _list_index AS
SELECT
    list_id,
    ROW_NUMBER() OVER (ORDER BY list_id) - 1 AS idx,
    COUNT(*)     OVER ()                      AS total_lists
FROM candidate_list;


INSERT INTO candidate_list_item (list_item_id, list_id, candidate_id, position)

WITH

-- ── All candidates with their two list assignments ────
assignments AS (

    -- Pass A — first list for each candidate
    SELECT
        c.candidate_id,
        li.list_id,
        'A' AS pass
    FROM      candidate   c
    JOIN      _list_index li
           ON li.idx = c.candidate_id % li.total_lists

    UNION ALL

    -- Pass B — second list for each candidate (offset by half)
    SELECT
        c.candidate_id,
        li.list_id,
        'B' AS pass
    FROM      candidate   c
    JOIN      _list_index li
           ON li.idx = (c.candidate_id + (SELECT MAX(total_lists)/2 FROM _list_index))
                       % li.total_lists
),

-- ── Remove any accidental same-list duplicates ────────
-- (can happen if total_lists is odd and offset == 0)
deduped AS (
    SELECT candidate_id, list_id
    FROM   assignments
    GROUP BY candidate_id, list_id   -- one row per (candidate, list) pair
),

-- ── Assign positions within each list ─────────────────
positioned AS (
    SELECT
        candidate_id,
        list_id,
        ROW_NUMBER() OVER (
            PARTITION BY list_id
            ORDER BY     candidate_id
        ) AS position
    FROM deduped
),

-- ── Generate list_item_id ─────────────────────────────
numbered AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY list_id, position) AS list_item_id,
        list_id,
        candidate_id,
        position
    FROM positioned
)

SELECT list_item_id, list_id, candidate_id, position
FROM   numbered

ON CONFLICT (list_id, candidate_id) DO NOTHING;


-- ── Cleanup ───────────────────────────────────────────
DROP TABLE IF EXISTS _list_index;


INSERT INTO candidate (candidate_id, person_id)
SELECT
    1500 + ROW_NUMBER() OVER (ORDER BY person_id) AS candidate_id,
    person_id
FROM (
    SELECT person_id
    FROM   person
    WHERE  date_of_birth <= CURRENT_DATE - INTERVAL '18 years'
    AND    person_id NOT IN (SELECT person_id FROM candidate)
    ORDER BY RANDOM()
    LIMIT  498500   -- 500000 - 1500 already in there
) p;



INSERT INTO voter_election (
    voter_election_id,
    voter_id,
    station_id,
    election_id,
    checkin_timestamp
)
WITH

-- Rank ballots within each station+election by timestamp
-- ballot #1 = first ballot cast at that station that day
ranked_ballots AS (
    SELECT
        station_id,
        election_id,
        ballot_timestamp,
        ROW_NUMBER() OVER (
            PARTITION BY station_id, election_id
            ORDER BY     ballot_timestamp
        )                           AS rn
    FROM ballot
),

-- Rank voters within each station by voter_id
-- voter #1 = lowest voter_id at that station
ranked_voters AS (
    SELECT
        voter_id,
        station_id,
        ROW_NUMBER() OVER (
            PARTITION BY station_id
            ORDER BY     voter_id
        )                           AS rn
    FROM voter
)

SELECT
    ROW_NUMBER() OVER (
        ORDER BY b.station_id, b.election_id, b.rn
    )                               AS voter_election_id,
    v.voter_id,
    b.station_id,
    b.election_id,
    b.ballot_timestamp              AS checkin_timestamp
FROM        ranked_ballots  b
JOIN        ranked_voters   v
        ON  v.station_id    = b.station_id
        AND v.rn            = b.rn
ON CONFLICT (voter_id, election_id) DO NOTHING;




INSERT INTO voter (voter_id, person_id, station_id, region_id)
WITH PosledenID AS (
    SELECT COALESCE(MAX(voter_id), 0) as max_id FROM voter
),
PreostanatiLugje AS (
    SELECT p.person_id, 
           row_number() OVER (ORDER BY md5(p.person_id::text)) as p_rn
    FROM person p
    LEFT JOIN voter v ON p.person_id = v.person_id
    WHERE v.person_id IS NULL 
    AND p.person_id <= 2008051999999
    LIMIT 752695 
),
SlobodniMesta AS (
   
    SELECT station_id, 
           row_number() OVER (ORDER BY station_id ASC) as s_rn,
           count(*) OVER () as total_free
    FROM polling_station 
    WHERE station_id BETWEEN 5001 AND 8743
    AND NOT EXISTS (SELECT 1 FROM voter v WHERE v.station_id = polling_station.station_id)
)
SELECT 
    pid.max_id + nl.p_rn,
    nl.person_id,
    sm.station_id,
    NULL
FROM PreostanatiLugje nl
CROSS JOIN PosledenID pid

JOIN SlobodniMesta sm ON sm.s_rn = (MOD(nl.p_rn - 1, (SELECT total_free FROM SlobodniMesta)) + 1)
WHERE sm.station_id IS NOT NULL;



INSERT INTO voter (voter_id, person_id, station_id, region_id)
WITH PosledenID AS (
    SELECT COALESCE(MAX(voter_id), 0) as max_id FROM voter
),
NoviLugjeMKD AS (
    SELECT p.person_id, 
           row_number() OVER (ORDER BY md5(p.person_id::text)) as p_rn
    FROM person p
    LEFT JOIN voter v ON p.person_id = v.person_id
    WHERE v.person_id IS NULL 
    AND p.person_id <= 2008051999999
    LIMIT 500000
),
MestaMKD AS (
    SELECT station_id, 
           row_number() OVER (ORDER BY station_id ASC) as s_rn
    FROM polling_station 
    WHERE station_id BETWEEN 5001 AND 8743
)
SELECT 
    pid.max_id + nl.p_rn,
    nl.person_id,
    ms.station_id,
    NULL
FROM NoviLugjeMKD nl
CROSS JOIN PosledenID pid

JOIN MestaMKD ms ON ms.s_rn = ((nl.p_rn - 1) / 500) + 1
WHERE ms.station_id IS NOT NULL;


INSERT INTO voter (voter_id, person_id, station_id, region_id)
WITH PosledenID AS (
    SELECT COALESCE(MAX(voter_id), 1300000) as max_id FROM voter
),
NoviLugjeSrbija AS (
    SELECT p.person_id, 
           row_number() OVER (ORDER BY md5(p.person_id::text)) as p_rn
    FROM person p
    LEFT JOIN voter v ON p.person_id = v.person_id
    WHERE v.person_id IS NULL 
    AND p.person_id <= 2008042999999 -- Проверка за полнолетство (родени пред 19.05.2008)
    LIMIT 3000000
),
MestaSrbija AS (
    SELECT station_id, 
           row_number() OVER (ORDER BY station_id ASC) as s_rn,
           count(*) OVER () as total_stations
    FROM polling_station 
    WHERE station_id BETWEEN 10000 AND 18000
)
SELECT 
    pid.max_id + nl.p_rn, 
    nl.person_id,
    ms.station_id,
    NULL
FROM NoviLugjeSrbija nl
CROSS JOIN PosledenID pid
JOIN MestaSrbija ms ON ms.s_rn = (MOD(nl.p_rn, (SELECT total_stations FROM MestaSrbija)) + 1)
WHERE ms.station_id IS NOT NULL;


INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (18, 'Parliamentary Election Serbia 1990', 2, 187, '1990-12-09', 'First multi-party parliamentary elections in Serbia.', 0, 8, 250);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (19, 'Parliamentary Election Serbia 1992', 2, 187, '1992-12-20', 'Early elections called amid Yugoslav dissolution.', 0, 8, 250);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (20, 'Parliamentary Election Serbia 1993', 2, 187, '1993-12-19', 'Snap elections following dissolution of parliament.', 0, 8, 250);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (21, 'Parliamentary Election Serbia 1997', 2, 187, '1997-09-21', 'Elections held under Milošević government.', 0, 8, 250);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (22, 'Parliamentary Election Serbia 2000', 2, 187, '2000-09-24', 'Elections following fall of Milošević regime.', 0, 8, 250);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (23, 'Parliamentary Election Serbia 2003', 2, 187, '2003-12-28', 'Early elections following assassination of PM Đinđić.', 0, 8, 250);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (24, 'Parliamentary Election Serbia 2007', 2, 187, '2007-01-21', 'Elections held amid Kosovo status negotiations.', 0, 8, 250);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (25, 'Parliamentary Election Serbia 2008', 2, 187, '2008-05-11', 'Snap elections following Kosovo independence declaration.', 0, 8, 250);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (26, 'Parliamentary Election Serbia 2012', 2, 187, '2012-05-06', 'SNS came to power for the first time.', 0, 8, 250);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (27, 'Parliamentary Election Serbia 2014', 2, 187, '2014-03-16', 'Early elections called by PM Vučić.', 0, 8, 250);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (28, 'Parliamentary Election Serbia 2016', 2, 187, '2016-04-24', 'Snap elections held concurrently with local elections.', 0, 8, 250);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (29, 'Parliamentary Election Serbia 2020', 2, 187, '2020-06-21', 'Elections boycotted by most opposition parties.', 0, 8, 250);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (30, 'Parliamentary Election Serbia 2022', 2, 187, '2022-04-03', 'Elections held concurrently with presidential election.', 0, 8, 250);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (31, 'Parliamentary Election Serbia 2023', 2, 187, '2023-12-17', 'Snap elections; SNS regained parliamentary majority.', 0, 8, 250);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (32, 'Presidential Election Serbia 1990', 1, 187, '1990-12-09', 'Slobodan Milosevic elected president.', 0, 3, NULL);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (33, 'Presidential Election Serbia 1992', 1, 187, '1992-12-20', 'Milosevic re-elected for second term.', 0, 3, NULL);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (34, 'Presidential Election Serbia 1997', 1, 187, '1997-09-21', 'Milan Milutinovic elected president.', 0, 3, NULL);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (35, 'Presidential Election Serbia 2002', 1, 187, '2002-09-29', 'Three failed attempts due to turnout quorum not met.', 0, 3, NULL);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (36, 'Presidential Election Serbia 2004', 1, 187, '2004-06-13', 'Boris Tadic elected president; quorum requirement abolished.', 0, 3, NULL);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (37, 'Presidential Election Serbia 2008', 1, 187, '2008-01-20', 'Boris Tadic re-elected in second round over Nikolic.', 0, 3, NULL);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (38, 'Presidential Election Serbia 2012', 1, 187, '2012-05-06', 'Tomislav Nikolic elected; SNS came to power.', 0, 3, NULL);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (39, 'Presidential Election Serbia 2017', 1, 187, '2017-04-02', 'Aleksandar Vucic elected president in first round.', 0, 3, NULL);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (40, 'Presidential Election Serbia 2022', 1, 187, '2022-04-03', 'Vucic re-elected with 60% in first round.', 0, 3, NULL);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (41, 'Local Elections Serbia 2016', 3, 187, '2016-04-24', 'Local elections held concurrently with parliamentary.', 0, 8, NULL);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (42, 'Local Elections Serbia 2020', 3, 187, '2020-06-21', 'Local elections held concurrently with parliamentary.', 0, 8, NULL);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (43, 'Local Elections Serbia 2024', 3, 187, '2024-06-02', 'Local elections held in majority of cities and municipalities.', 0, 8, NULL);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (44, 'Constitutional Referendum Serbia 2006', 6, 187, '2006-10-28', 'Referendum on the new constitution of Serbia.', 0, 1, NULL);
INSERT INTO election (election_id, name, election_type_id, region_id, election_date, description, status, winner_method_id, total_seats) VALUES (45, 'Referendum on Neutrality Serbia 2022', 6, 187, '2022-01-16', 'Referendum on constitutional amendments including judicial reform.', 0, 1, NULL);



INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180001, 18, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180002, 19, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180003, 20, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180004, 21, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180005, 22, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180006, 23, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180007, 24, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180008, 25, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180009, 26, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180010, 27, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180011, 28, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180012, 29, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180013, 30, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180014, 31, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180015, 32, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180016, 33, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180017, 34, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180018, 35, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180019, 36, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180020, 37, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180021, 38, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180022, 39, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180023, 40, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180024, 44, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180025, 45, 187, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180026, 41, 2001, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180027, 41, 2002, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180028, 41, 2003, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180029, 41, 2004, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180030, 41, 2005, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180031, 41, 2006, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180032, 41, 2007, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180033, 41, 2008, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180034, 41, 2010, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180035, 41, 2011, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180036, 41, 2012, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180037, 41, 2013, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180038, 41, 2014, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180039, 41, 2015, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180040, 41, 2016, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180041, 41, 2017, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180042, 41, 2018, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180043, 41, 2019, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180044, 41, 2020, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180045, 41, 2021, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180046, 41, 2022, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180047, 41, 2023, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180048, 41, 2024, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180049, 41, 2025, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180050, 41, 2026, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180051, 41, 2027, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180052, 41, 2028, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180053, 41, 2029, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180054, 41, 2030, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180055, 41, 2031, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180056, 42, 2001, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180057, 42, 2002, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180058, 42, 2003, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180059, 42, 2004, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180060, 42, 2005, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180061, 42, 2006, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180062, 42, 2007, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180063, 42, 2008, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180064, 42, 2010, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180065, 42, 2011, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180066, 42, 2012, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180067, 42, 2013, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180068, 42, 2014, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180069, 42, 2015, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180070, 42, 2016, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180071, 42, 2017, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180072, 42, 2018, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180073, 42, 2019, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180074, 42, 2020, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180075, 42, 2021, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180076, 42, 2022, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180077, 42, 2023, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180078, 42, 2024, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180079, 42, 2025, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180080, 42, 2026, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180081, 42, 2027, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180082, 42, 2028, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180083, 42, 2029, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180084, 42, 2030, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180085, 42, 2031, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180086, 43, 2001, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180087, 43, 2002, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180088, 43, 2003, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180089, 43, 2004, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180090, 43, 2005, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180091, 43, 2006, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180092, 43, 2007, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180093, 43, 2008, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180094, 43, 2010, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180095, 43, 2011, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180096, 43, 2012, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180097, 43, 2013, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180098, 43, 2014, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180099, 43, 2015, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180100, 43, 2016, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180101, 43, 2017, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180102, 43, 2018, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180103, 43, 2019, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180104, 43, 2020, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180105, 43, 2021, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180106, 43, 2022, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180107, 43, 2023, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180108, 43, 2024, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180109, 43, 2025, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180110, 43, 2026, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180111, 43, 2027, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180112, 43, 2028, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180113, 43, 2029, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180114, 43, 2030, NULL);
INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180115, 43, 2031, NULL);



INSERT INTO station_election (station_election_id, station_id, election_id)
SELECT
    ROW_NUMBER() OVER (ORDER BY ps.station_id, e.election_id),
    ps.station_id,
    e.election_id
FROM polling_station ps
JOIN region r1 ON r1.region_id = ps.municipality_id
JOIN region r2 ON r2.region_id = r1.parent_region_id
JOIN electoral_district ed ON (
    ed.region_id = r2.region_id          -- lokalni: match po okrug/region
    OR ed.region_id = r2.parent_region_id -- nacionalni: match po zemja
)
JOIN election e ON e.election_id = ed.election_id
WHERE NOT EXISTS (
    SELECT 1 FROM station_election se
    WHERE se.station_id = ps.station_id
    AND se.election_id = e.election_id
)
ORDER BY ps.station_id, e.election_id;



INSERT INTO vote_result (result_id, election_id, station_id, entity_id, candidate_id, votes)
WITH entity_weights AS (
    SELECT entity_id, weight_max, weight_min FROM (VALUES
        (1001, 400, 100), (1002, 350, 80),  (1003, 200, 50),
        (1004, 80,  5),   (1005, 80,  5),   (1006, 80,  5),
        (1007, 80,  5),   (1008, 50,  5),   (1009, 50,  5),
        (1010, 50,  5),   (1011, 80,  5),   (1012, 50,  5),
        (1013, 50,  5),   (1014, 50,  5),   (1015, 50,  5),
        (2001, 500, 200), (2002, 200, 50),  (2003, 150, 30),
        (2004, 100, 20),  (2005, 100, 20),  (2006, 80,  10),
        (2007, 80,  10),  (2008, 80,  10),  (2009, 80,  10),
        (2010, 60,  5),   (2011, 80,  10),  (2012, 60,  5),
        (2013, 60,  5),   (2014, 80,  10),  (2015, 80,  10),
        (2016, 100, 20),  (2017, 60,  5),   (2018, 80,  10),
        (2019, 100, 20),  (2020, 80,  10)
    ) AS w(entity_id, weight_max, weight_min)
)
SELECT
    ROW_NUMBER() OVER (ORDER BY ed.election_id, se.station_id, ep.entity_id),
    ed.election_id,
    se.station_id,
    ep.entity_id,
    pe.candidate_id,
    CAST(FLOOR(x.r * ew.weight_max + ew.weight_min) AS int)
FROM election_participant ep
JOIN political_entity pe ON pe.entity_id = ep.entity_id
JOIN electoral_district ed ON ed.district_id = ep.district_id
JOIN station_election se ON se.election_id = ed.election_id
JOIN polling_station ps ON ps.station_id = se.station_id
JOIN region r1 ON r1.region_id = ps.municipality_id
JOIN region r2 ON r2.region_id = r1.parent_region_id
JOIN entity_weights ew ON ew.entity_id = ep.entity_id
CROSS JOIN LATERAL (SELECT random() AS r) x
WHERE (ed.region_id = r2.region_id OR ed.region_id = r2.parent_region_id)
AND NOT EXISTS (
    SELECT 1 FROM vote_result vr
    WHERE vr.election_id = ed.election_id
    AND vr.station_id = se.station_id
    AND vr.entity_id = ep.entity_id
);



INSERT INTO candidate (candidate_id, person_id) VALUES
(900001, 1946112999001), -- Vuk Draskovic
(900002, 1952020199001), -- Slobodan Milosevic
(900003, 1944032499001), -- Vojislav Kostunica
(900004, 1960010199001), -- Tomislav Nikolic
(900005, 1974010199001), -- Boris Tadic
(900006, 1970030599001); -- Aleksandar Vucic



-- 1991 Independence Referendum: VMRO (1001), SDSM (1002) uchestvuvale
INSERT INTO election_participant (participant_id, district_id, entity_id)
VALUES 
    ((SELECT MAX(participant_id) FROM election_participant) + 1, 160001, 1001),
    ((SELECT MAX(participant_id) FROM election_participant) + 2, 160001, 1002);

-- 2018 Prespa Referendum: SDSM (1002), VMRO (1001), DUI (1003)
INSERT INTO election_participant (participant_id, district_id, entity_id)
VALUES 
    ((SELECT MAX(participant_id) FROM election_participant) + 3, 170001, 1002),
    ((SELECT MAX(participant_id) FROM election_participant) + 4, 170001, 1001),
    ((SELECT MAX(participant_id) FROM election_participant) + 5, 170001, 1003);
    


INSERT INTO vote_result (result_id, election_id, station_id, entity_id, candidate_id, votes)
WITH entity_weights AS (
    SELECT entity_id, w_min, w_max FROM (VALUES
        (1001,100,400),(1002,80,350),(1003,50,200),(1004,5,80),(1005,5,80),
        (1006,5,70),(1007,5,80),(1008,5,40),(1009,5,40),(1010,5,40),
        (1011,5,60),(1012,5,40),(1013,5,40),(1014,5,40),(1015,5,40),

        (3001,120,420),(3002,100,380),(3003,20,80),(3004,130,430),
        (3005,110,390),(3006,60,220),(3007,120,420),(3008,110,400),
        (3009,115,410),(3010,120,420),(3011,110,400),(3012,115,415),
        (3013,120,420),(3014,110,400),(3015,120,430),(3016,115,410),

        (9027,60,160),(9034,15,50),(9028,65,170),(9029,40,110),
        (9030,55,150),(9031,45,120),(9032,50,140),(9033,50,145),

        (9901,80,200),(9902,5,35),

        (2001,200,500),(2002,50,180),(2003,40,160),(2004,20,90),
        (2005,25,100),(2006,10,60),(2007,15,70),(2008,15,65),
        (2009,15,65),(2010,5,40),(2011,20,80),(2012,5,40),
        (2013,5,40),(2014,20,80),(2015,15,65),(2016,100,350),
        (2017,5,40),(2018,15,65),(2019,25,90),(2020,15,65),

        (9011,80,220),(9012,30,90),(9013,85,230),(9014,28,85),
        (9015,80,220),(9016,25,80),(9017,75,200),(9018,55,160),
        (9019,65,180),(9020,50,150),(9021,70,190),(9022,60,170),
        (9023,65,175),(9024,55,160),(9025,130,350),(9026,140,370)
    ) AS w(entity_id, w_min, w_max)
),

-- LOCAL MK
lok_mk AS (
    SELECT ep.entity_id, pe.candidate_id, ed.election_id, ps.station_id
    FROM election_participant ep
    JOIN political_entity pe   ON pe.entity_id = ep.entity_id
    JOIN electoral_district ed ON ed.district_id = ep.district_id
    JOIN election e            ON e.election_id = ed.election_id
    JOIN polling_station ps    ON ps.municipality_id = ed.region_id
    WHERE e.election_type_id = 3
      AND e.region_id = 141
),

-- LOCAL SRB
lok_srb AS (
    SELECT ep.entity_id, pe.candidate_id, ed.election_id, ps.station_id
    FROM election_participant ep
    JOIN political_entity pe   ON pe.entity_id = ep.entity_id
    JOIN electoral_district ed ON ed.district_id = ep.district_id
    JOIN election e            ON e.election_id = ed.election_id
    JOIN polling_station ps    ON ps.municipality_id = ed.region_id
    WHERE e.election_type_id = 3
      AND e.region_id = 187
),

-- PARLIAMENTARY MK
parl_mk AS (
    SELECT ep.entity_id, pe.candidate_id, ed.election_id, ps.station_id
    FROM election_participant ep
    JOIN political_entity pe   ON pe.entity_id = ep.entity_id
    JOIN electoral_district ed ON ed.district_id = ep.district_id
    JOIN election e            ON e.election_id = ed.election_id
    JOIN polling_station ps    ON TRUE
    JOIN region r1             ON r1.region_id = ps.municipality_id
    WHERE e.election_type_id = 2
      AND e.region_id = 141
      AND r1.parent_region_id = ed.region_id
),

-- PARLIAMENTARY SRB
parl_srb AS (
    SELECT ep.entity_id, pe.candidate_id, ed.election_id, ps.station_id
    FROM election_participant ep
    JOIN political_entity pe   ON pe.entity_id = ep.entity_id
    JOIN electoral_district ed ON ed.district_id = ep.district_id
    JOIN election e            ON e.election_id = ed.election_id
    JOIN polling_station ps    ON TRUE
    JOIN region r1             ON r1.region_id = ps.municipality_id
    WHERE e.election_type_id = 2
      AND e.region_id = 187
      AND r1.parent_region_id = ed.region_id
),

-- PRESIDENTIAL MK
pret_mk AS (
    SELECT ep.entity_id, pe.candidate_id, ed.election_id, ps.station_id
    FROM election_participant ep
    JOIN political_entity pe   ON pe.entity_id = ep.entity_id
    JOIN electoral_district ed ON ed.district_id = ep.district_id
    JOIN election e            ON e.election_id = ed.election_id
    JOIN polling_station ps    ON ps.municipality_id BETWEEN 1100 AND 1179
    WHERE e.election_type_id = 1
      AND e.region_id = 141
),

-- PRESIDENTIAL SRB
pret_srb AS (
    SELECT ep.entity_id, pe.candidate_id, ed.election_id, ps.station_id
    FROM election_participant ep
    JOIN political_entity pe   ON pe.entity_id = ep.entity_id
    JOIN electoral_district ed ON ed.district_id = ep.district_id
    JOIN election e            ON e.election_id = ed.election_id
    JOIN polling_station ps    ON ps.municipality_id BETWEEN 3001 AND 3202
    WHERE e.election_type_id = 1
      AND e.region_id = 187
),

-- REFERENDUM MK
ref_mk AS (
    SELECT ep.entity_id, pe.candidate_id, ed.election_id, ps.station_id
    FROM election_participant ep
    JOIN political_entity pe   ON pe.entity_id = ep.entity_id
    JOIN electoral_district ed ON ed.district_id = ep.district_id
    JOIN election e            ON e.election_id = ed.election_id
    JOIN polling_station ps    ON ps.municipality_id BETWEEN 1100 AND 1179
    WHERE e.election_type_id = 6
      AND e.region_id = 141
),

-- REFERENDUM SRB
ref_srb AS (
    SELECT ep.entity_id, pe.candidate_id, ed.election_id, ps.station_id
    FROM election_participant ep
    JOIN political_entity pe   ON pe.entity_id = ep.entity_id
    JOIN electoral_district ed ON ed.district_id = ep.district_id
    JOIN election e            ON e.election_id = ed.election_id
    JOIN polling_station ps    ON ps.municipality_id BETWEEN 3001 AND 3202
    WHERE e.election_type_id = 6
      AND e.region_id = 187
),

all_combinations AS (
    SELECT * FROM lok_mk
    UNION ALL SELECT * FROM lok_srb
    UNION ALL SELECT * FROM parl_mk
    UNION ALL SELECT * FROM parl_srb
    UNION ALL SELECT * FROM pret_mk
    UNION ALL SELECT * FROM pret_srb
    UNION ALL SELECT * FROM ref_mk
    UNION ALL SELECT * FROM ref_srb
)

SELECT
    ROW_NUMBER() OVER () AS result_id,
    c.election_id,
    c.station_id,
    c.entity_id,
    c.candidate_id,
    GREATEST(1, FLOOR(ew.w_min + random() * (ew.w_max - ew.w_min))::int) AS votes
FROM all_combinations c
JOIN entity_weights ew ON ew.entity_id = c.entity_id;



-- ── Step 1: find top 2 entities per presidential election
-- Used to determine which entities go to round 2
CREATE TEMP TABLE _top2_presidential AS
SELECT election_id, entity_id
FROM (
    SELECT
        vr.election_id,
        vr.entity_id,
        SUM(vr.votes)   AS total_votes,
        RANK() OVER (
            PARTITION BY vr.election_id
            ORDER BY SUM(vr.votes) DESC
        )               AS rnk
    FROM        vote_result      vr
    WHERE       vr.election_id IN (9,10,11,12,32,33,34,35,36,37,38,39,40)
    AND         vr.candidate_id IS NULL
    GROUP BY    vr.election_id, vr.entity_id
) ranked
WHERE rnk <= 2;

-- ── Step 2: find top 2 entities per local election per municipality
CREATE TEMP TABLE _top2_local AS
SELECT election_id, municipality_id, entity_id
FROM (
    SELECT
        vr.election_id,
        ps.municipality_id,
        vr.entity_id,
        SUM(vr.votes)   AS total_votes,
        RANK() OVER (
            PARTITION BY vr.election_id, ps.municipality_id
            ORDER BY SUM(vr.votes) DESC
        )               AS rnk
    FROM        vote_result      vr
    JOIN        polling_station  ps ON ps.station_id = vr.station_id
    WHERE       vr.election_id IN (13,14,15,41,42,43)
    AND         vr.candidate_id IS NULL
    GROUP BY    vr.election_id, ps.municipality_id, vr.entity_id
) ranked
WHERE rnk <= 2;

-- ── Verify temp tables ────────────────────────────────
DO $$
BEGIN
    RAISE NOTICE 'Top 2 presidential entries : %',
        (SELECT COUNT(*) FROM _top2_presidential);
    RAISE NOTICE 'Top 2 local entries        : %',
        (SELECT COUNT(*) FROM _top2_local);
END $$;


-- ── Main INSERT ───────────────────────────────────────
INSERT INTO ballot (
    election_id,
    station_id,
    entity_id,
    candidate_id,
    is_valid,
    ballot_timestamp
)

WITH

-- ── Round 1: ALL elections, ALL entities ──────────────
round1 AS (
    SELECT
        vr.election_id,
        vr.station_id,
        vr.entity_id,
        vr.candidate_id,
        e.election_date,
        0                                               AS round_offset,
        GREATEST(1, ROUND(vr.votes * 0.145)::INT)       AS ballot_count
    FROM        vote_result vr
    JOIN        election    e ON e.election_id = vr.election_id
    WHERE       vr.candidate_id IS NULL
),

-- ── Round 2: Presidential elections ──────────────────
-- Only top 2 entities, 14 days after round 1
round2_presidential AS (
    SELECT
        vr.election_id,
        vr.station_id,
        vr.entity_id,
        vr.candidate_id,
        e.election_date,
        14                                              AS round_offset,
        GREATEST(1, ROUND(vr.votes * 0.058)::INT)       AS ballot_count
    FROM        vote_result         vr
    JOIN        election            e   ON e.election_id  = vr.election_id
    JOIN        _top2_presidential  t2  ON t2.election_id = vr.election_id
                                       AND t2.entity_id   = vr.entity_id
    WHERE       vr.election_id IN (9,10,11,12,32,33,34,35,36,37,38,39,40)
    AND         vr.candidate_id IS NULL
),

-- ── Round 2: Local elections ──────────────────────────
-- Only top 2 entities per municipality, 14 days after round 1
round2_local AS (
    SELECT
        vr.election_id,
        vr.station_id,
        vr.entity_id,
        vr.candidate_id,
        e.election_date,
        14                                              AS round_offset,
        GREATEST(1, ROUND(vr.votes * 0.058)::INT)       AS ballot_count
    FROM        vote_result     vr
    JOIN        election        e   ON e.election_id    = vr.election_id
    JOIN        polling_station ps  ON ps.station_id    = vr.station_id
    JOIN        _top2_local     t2  ON t2.election_id   = vr.election_id
                                   AND t2.municipality_id = ps.municipality_id
                                   AND t2.entity_id     = vr.entity_id
    WHERE       vr.election_id IN (13,14,15,41,42,43)
    AND         vr.candidate_id IS NULL
),

-- ── Combine all rounds ────────────────────────────────
all_ballots AS (
    SELECT election_id, station_id, entity_id, candidate_id,
           election_date, round_offset, ballot_count
    FROM   round1
    UNION ALL
    SELECT election_id, station_id, entity_id, candidate_id,
           election_date, round_offset, ballot_count
    FROM   round2_presidential
    UNION ALL
    SELECT election_id, station_id, entity_id, candidate_id,
           election_date, round_offset, ballot_count
    FROM   round2_local
),

-- ── Expand each row into N individual ballot rows ─────
expanded AS (
    SELECT
        election_id,
        station_id,
        entity_id,
        candidate_id,
        election_date,
        round_offset
    FROM        all_ballots
    JOIN LATERAL generate_series(1, ballot_count) AS gs(n) ON TRUE
),

-- ── Add random values and validity ───────────────────
with_random AS (
    SELECT
        election_id,
        station_id,
        entity_id,
        candidate_id,
        election_date,
        round_offset,
        RANDOM()        AS r
    FROM expanded
)

SELECT
    
	election_id,
    station_id,

    -- 2.5% invalid ballots have NULL entity_id
    CASE WHEN r < 0.025 THEN NULL ELSE entity_id END    AS entity_id,
    CASE WHEN r < 0.025 THEN NULL ELSE candidate_id END AS candidate_id,

    -- is_valid flag
    CASE WHEN r < 0.025 THEN FALSE ELSE TRUE END        AS is_valid,

    -- Timestamp: election date + round offset + random hour 7:00-19:00
    (
        election_date::TIMESTAMP
        + (round_offset || ' days')::INTERVAL
        + INTERVAL '7 hours'
        + (RANDOM() * 43200 || ' seconds')::INTERVAL
    )                                                   AS ballot_timestamp

FROM with_random;


-- ── Cleanup temp tables ───────────────────────────────
DROP TABLE IF EXISTS _top2_presidential;
DROP TABLE IF EXISTS _top2_local;





INSERT INTO vote_result (result_id, election_id, station_id, entity_id, candidate_id, votes)
WITH
entity_weights(election_id, entity_id, weight) AS (VALUES
    -- МК ПАРЛАМЕНТАРНИ
    (1,3001,0.326),(1,3002,0.234),(1,3003,0.121),(1,1004,0.075),(1,1011,0.060),(1,1012,0.055),(1,1013,0.129),
    (2,3004,0.488),(2,3005,0.236),(2,1003,0.128),(2,1004,0.083),(2,1011,0.030),(2,1010,0.035),
    (3,3004,0.390),(3,3006,0.328),(3,1004,0.102),(3,1011,0.059),(3,1009,0.040),(3,1010,0.030),(3,1008,0.025),(3,1014,0.016),(3,1015,0.010),
    (4,3009,0.425),(4,1002,0.254),(4,1003,0.140),(4,1004,0.057),(4,1011,0.040),(4,1009,0.040),(4,1008,0.044),
    (5,3010,0.387),(5,1002,0.369),(5,1003,0.073),(5,1004,0.052),(5,1005,0.040),(5,1006,0.025),(5,1007,0.020),(5,1009,0.017),(5,1010,0.017),
    (6,3012,0.550),(6,1001,0.250),(6,1003,0.100),(6,1006,0.040),(6,1005,0.020),(6,1007,0.020),(6,1004,0.020),
    (7,3013,0.348),(7,1001,0.356),(7,1003,0.118),(7,1006,0.085),(7,1007,0.040),(7,1005,0.028),(7,1004,0.025),
    (8,3015,0.436),(8,3016,0.153),(8,1003,0.111),(8,1006,0.068),(8,1007,0.060),(8,1005,0.050),(8,1004,0.122),
    -- МК ПРЕТСЕДАТЕЛСКИ
    (9,9027,0.634),(9,9034,0.366),
    (10,9028,0.556),(10,9029,0.444),
    (11,9030,0.514),(11,9031,0.486),
    (12,9032,0.654),(12,9033,0.346),
    -- МК ЛОКАЛНИ
    (13,1001,0.420),(13,1002,0.300),(13,1003,0.100),(13,1004,0.080),(13,1011,0.050),(13,1010,0.050),
    (14,1001,0.400),(14,1002,0.350),(14,1003,0.080),(14,1004,0.060),(14,1005,0.040),(14,1006,0.035),(14,1007,0.035),
    (15,1001,0.340),(15,1002,0.400),(15,1003,0.090),(15,1006,0.060),(15,1005,0.050),(15,1007,0.060),
    -- МК РЕФЕРЕНДУМИ
    (16,9001,0.950),(16,9002,0.050),
    (17,9001,0.910),(17,9002,0.090),
    -- СРБ ПАРЛАМЕНТАРНИ
    (18,2002,0.460),(18,2006,0.200),(18,2015,0.180),(18,2005,0.080),(18,2009,0.080),
    (19,2002,0.400),(19,2005,0.220),(19,2006,0.170),(19,2015,0.120),(19,2009,0.090),
    (20,2002,0.370),(20,2005,0.240),(20,2006,0.160),(20,2015,0.140),(20,2009,0.090),
    (21,2002,0.350),(21,2005,0.280),(21,2006,0.190),(21,2009,0.090),(21,2015,0.090),
    (22,2016,0.520),(22,2002,0.280),(22,2005,0.140),(22,2009,0.060),
    (23,2005,0.280),(23,2004,0.180),(23,2003,0.130),(23,2002,0.080),(23,2009,0.080),(23,2014,0.120),(23,2010,0.070),(23,2006,0.060),
    (24,2005,0.290),(24,2004,0.170),(24,2003,0.230),(24,2002,0.060),(24,2014,0.070),(24,2009,0.070),(24,2010,0.050),(24,2006,0.060),
    (25,2003,0.380),(25,2005,0.290),(25,2004,0.110),(25,2002,0.070),(25,2010,0.050),(25,2006,0.050),(25,2009,0.050),
    (26,2001,0.240),(26,2003,0.220),(26,2002,0.140),(26,2004,0.090),(26,2005,0.050),(26,2010,0.060),(26,2012,0.060),(26,2008,0.050),(26,2014,0.050),(26,2019,0.040),
    (27,2001,0.480),(27,2003,0.060),(27,2002,0.110),(27,2004,0.050),(27,2012,0.060),(27,2008,0.060),(27,2019,0.050),(27,2010,0.050),(27,2020,0.050),(27,2018,0.030),
    (28,2001,0.480),(28,2002,0.110),(28,2003,0.060),(28,2008,0.050),(28,2020,0.050),(28,2012,0.050),(28,2019,0.040),(28,2011,0.040),(28,2018,0.040),(28,2010,0.040),(28,2004,0.040),
    (29,2001,0.620),(29,2002,0.110),(29,2012,0.050),(29,2019,0.040),(29,2018,0.040),(29,2011,0.040),(29,2008,0.050),(29,2009,0.050),
    (30,2001,0.440),(30,2002,0.110),(30,2007,0.130),(30,2008,0.060),(30,2020,0.050),(30,2012,0.050),(30,2019,0.040),(30,2011,0.040),(30,2018,0.040),(30,2013,0.040),
    (31,2001,0.460),(31,2002,0.070),(31,2007,0.120),(31,2008,0.050),(31,2020,0.060),(31,2012,0.050),(31,2019,0.050),(31,2011,0.040),(31,2018,0.040),(31,2013,0.030),(31,2004,0.030),
    -- СРБ ПРЕТСЕДАТЕЛСКИ
    (32,9011,0.650),(32,9012,0.350),
    (33,9013,0.600),(33,9014,0.400),
    (34,9015,0.550),(34,9016,0.450),
    (35,9017,0.500),(35,9018,0.500),
    (36,9019,0.530),(36,9020,0.470),
    (37,9021,0.520),(37,9022,0.480),
    (38,9023,0.510),(38,9024,0.490),
    (39,9025,1.000),
    (40,9026,1.000),
    -- СРБ ЛОКАЛИ
    (41,2001,0.470),(41,2002,0.100),(41,2003,0.060),(41,2008,0.060),(41,2020,0.050),(41,2012,0.050),(41,2019,0.050),(41,2011,0.050),(41,2018,0.050),(41,2004,0.060),
    (42,2001,0.580),(42,2002,0.100),(42,2012,0.050),(42,2019,0.050),(42,2018,0.050),(42,2011,0.050),(42,2008,0.060),(42,2009,0.060),
    (43,2001,0.460),(43,2002,0.070),(43,2007,0.120),(43,2008,0.060),(43,2020,0.060),(43,2012,0.050),(43,2019,0.050),(43,2011,0.040),(43,2018,0.040),(43,2013,0.050),
    -- СРБ РЕФЕРЕНДУМИ
    (44,9001,0.530),(44,9002,0.470),
    (45,9001,0.600),(45,9002,0.400)
),

turnout_factors(election_id, turnout) AS (VALUES
    (1,0.56),(2,0.57),(3,0.63),(4,0.61),(5,0.67),(6,0.42),(7,0.51),(8,0.55),
    (9,0.57),(10,0.55),(11,0.46),(12,0.55),
    (13,0.48),(14,0.60),(15,0.51),
    (16,0.75),(17,0.37),
    (18,0.72),(19,0.70),(20,0.68),(21,0.65),(22,0.73),(23,0.58),(24,0.60),(25,0.61),
    (26,0.57),(27,0.53),(28,0.56),(29,0.48),(30,0.55),(31,0.59),
    (32,0.72),(33,0.69),(34,0.57),(35,0.46),(36,0.48),(37,0.61),(38,0.57),(39,0.55),(40,0.59),
    (41,0.45),(42,0.47),(43,0.49),
    (44,0.55),(45,0.45)
),

regional_bias(election_id, entity_id, region_id, bias) AS (VALUES
    (1,3001,1000,1.12),(1,3002,1000,0.95),(1,1004,1000,0.80),
    (1,3001,1003,1.08),(1,3002,1003,1.05),
    (1,1004,1004,1.80),(1,1011,1004,1.60),(1,3003,1004,0.50),
    (1,1004,1006,1.70),(1,1011,1006,1.50),
    (1,1004,1002,1.40),(1,1011,1002,1.30),
    (2,1004,1004,1.85),(2,1003,1004,1.20),
    (2,1004,1006,1.75),(2,1003,1006,1.30),
    (2,1004,1002,1.50),(2,3004,1000,1.15),(2,3004,1003,1.10),
    (8,3015,1007,1.20),(8,3015,1003,1.15),(8,3016,1000,1.25),
    (8,1004,1004,1.90),(8,1004,1006,1.80)
),

station_district AS (
    -- МК Парламентарни (1-8)
    SELECT se.election_id, se.station_id, ed.district_id,
           ps.municipality_id AS municipality_region_id,
           r_mun.parent_region_id AS parent_region_id
    FROM station_election se
    JOIN polling_station ps ON ps.station_id = se.station_id
    JOIN region r_mun ON r_mun.region_id = ps.municipality_id
    JOIN electoral_district ed ON ed.election_id = se.election_id
        AND ed.region_id = r_mun.parent_region_id
    WHERE se.election_id BETWEEN 1 AND 8

    UNION ALL

    -- МК Претседателски (9-12)
    SELECT se.election_id, se.station_id, ed.district_id,
           ps.municipality_id,
           r_mun.parent_region_id
    FROM station_election se
    JOIN polling_station ps ON ps.station_id = se.station_id
    JOIN region r_mun ON r_mun.region_id = ps.municipality_id
    JOIN region r_reg ON r_reg.region_id = r_mun.parent_region_id
    JOIN electoral_district ed ON ed.election_id = se.election_id
        AND ed.region_id = r_reg.parent_region_id
    WHERE se.election_id BETWEEN 9 AND 12

    UNION ALL

    -- МК Локални (13-15)
    SELECT se.election_id, se.station_id, ed.district_id,
           ps.municipality_id,
           NULL::int8 AS parent_region_id
    FROM station_election se
    JOIN polling_station ps ON ps.station_id = se.station_id
    JOIN electoral_district ed ON ed.election_id = se.election_id
        AND ed.region_id = ps.municipality_id
    WHERE se.election_id BETWEEN 13 AND 15

    UNION ALL

    -- МК Референдуми (16-17)
    SELECT se.election_id, se.station_id, ed.district_id,
           ps.municipality_id,
           r_mun.parent_region_id
    FROM station_election se
    JOIN polling_station ps ON ps.station_id = se.station_id
    JOIN region r_mun ON r_mun.region_id = ps.municipality_id
    JOIN region r_reg ON r_reg.region_id = r_mun.parent_region_id
    JOIN electoral_district ed ON ed.election_id = se.election_id
        AND ed.region_id = r_reg.parent_region_id
    WHERE se.election_id IN (16, 17)

    UNION ALL

    -- СРБ Парламентарни + Претседателски + Референдуми (18-40, 44-45)
    SELECT se.election_id, se.station_id, ed.district_id,
           ps.municipality_id,
           r_mun.parent_region_id
    FROM station_election se
    JOIN polling_station ps ON ps.station_id = se.station_id
    JOIN region r_mun ON r_mun.region_id = ps.municipality_id
    JOIN region r_okrug ON r_okrug.region_id = r_mun.parent_region_id
    JOIN electoral_district ed ON ed.election_id = se.election_id
        AND ed.region_id = r_okrug.parent_region_id
    WHERE se.election_id IN (
        18,19,20,21,22,23,24,25,26,27,28,29,30,31,
        32,33,34,35,36,37,38,39,40,44,45
    )

    UNION ALL

    -- СРБ Локали (41, 42, 43)
    SELECT se.election_id, se.station_id, ed.district_id,
           ps.municipality_id,
           NULL::int8 AS parent_region_id
    FROM station_election se
    JOIN polling_station ps ON ps.station_id = se.station_id
    JOIN electoral_district ed ON ed.election_id = se.election_id
        AND ed.region_id = ps.municipality_id
    WHERE se.election_id IN (41, 42, 43)
),

station_totals AS (
    SELECT sd.election_id, sd.station_id, sd.district_id,
           sd.municipality_region_id, sd.parent_region_id,
           GREATEST(0, ROUND(
               ps.registered_voter * tf.turnout * (0.92 + random() * 0.16)
           )::int) AS total_votes
    FROM station_district sd
    JOIN polling_station ps ON ps.station_id = sd.station_id
    JOIN turnout_factors tf ON tf.election_id = sd.election_id
),

distributed AS (
    SELECT st.election_id, st.station_id, ep.entity_id,
           pe.candidate_id,
           GREATEST(0, ROUND(
               st.total_votes::numeric * ew.weight
               * COALESCE(rb.bias, 1.0)
               * (0.94 + random() * 0.12)
           )::int) AS votes
    FROM station_totals st
    JOIN election_participant ep ON ep.district_id = st.district_id
    JOIN entity_weights ew ON ew.election_id = st.election_id
        AND ew.entity_id = ep.entity_id
    JOIN political_entity pe ON pe.entity_id = ep.entity_id
    LEFT JOIN regional_bias rb ON rb.election_id = st.election_id
        AND rb.entity_id = ep.entity_id
        AND rb.region_id = st.parent_region_id
)

SELECT
    ROW_NUMBER() OVER (ORDER BY election_id, station_id, entity_id) AS result_id,
    election_id, station_id, entity_id, candidate_id, votes
FROM distributed;







UPDATE vote_result vr
SET votes = GREATEST(1, ROUND(
    vc.voter_count
    * tf.turnout
    * ew.weight
    * (0.90 + random() * 0.20)
)::int)
FROM
    (VALUES
        (1,3001,0.326),(1,3002,0.234),(1,3003,0.121),(1,1004,0.075),(1,1011,0.060),(1,1012,0.055),(1,1013,0.129),
        (2,3004,0.488),(2,3005,0.236),(2,1003,0.128),(2,1004,0.083),(2,1011,0.030),(2,1010,0.035),
        (3,3004,0.390),(3,3006,0.328),(3,1004,0.102),(3,1011,0.059),(3,1009,0.040),(3,1010,0.030),(3,1008,0.025),(3,1014,0.016),(3,1015,0.010),
        (4,3009,0.425),(4,1002,0.254),(4,1003,0.140),(4,1004,0.057),(4,1011,0.040),(4,1009,0.040),(4,1008,0.044),
        (5,3010,0.387),(5,1002,0.369),(5,1003,0.073),(5,1004,0.052),(5,1005,0.040),(5,1006,0.025),(5,1007,0.020),(5,1009,0.017),(5,1010,0.017),
        (6,3012,0.550),(6,1001,0.250),(6,1003,0.100),(6,1006,0.040),(6,1005,0.020),(6,1007,0.020),(6,1004,0.020),
        (7,3013,0.348),(7,1001,0.356),(7,1003,0.118),(7,1006,0.085),(7,1007,0.040),(7,1005,0.028),(7,1004,0.025),
        (8,3015,0.436),(8,3016,0.153),(8,1003,0.111),(8,1006,0.068),(8,1007,0.060),(8,1005,0.050),(8,1004,0.122),
        (9,9027,0.634),(9,9034,0.366),
        (10,9028,0.556),(10,9029,0.444),
        (11,9030,0.514),(11,9031,0.486),
        (12,9032,0.654),(12,9033,0.346),
        (13,1001,0.420),(13,1002,0.300),(13,1003,0.100),(13,1004,0.080),(13,1011,0.050),(13,1010,0.050),
        (14,1001,0.400),(14,1002,0.350),(14,1003,0.080),(14,1004,0.060),(14,1005,0.040),(14,1006,0.035),(14,1007,0.035),
        (15,1001,0.340),(15,1002,0.400),(15,1003,0.090),(15,1006,0.060),(15,1005,0.050),(15,1007,0.060),
        (16,9901,0.950),(16,9902,0.050),
        (17,9901,0.910),(17,9902,0.090)
    ) AS ew(election_id, entity_id, weight),
    (VALUES
        (1,0.56),(2,0.57),(3,0.63),(4,0.61),(5,0.67),(6,0.42),(7,0.51),(8,0.55),
        (9,0.57),(10,0.55),(11,0.46),(12,0.55),
        (13,0.48),(14,0.60),(15,0.51),
        (16,0.75),(17,0.37)
    ) AS tf(election_id, turnout),
    (
        SELECT station_id, COUNT(*) AS voter_count
        FROM voter
        GROUP BY station_id
    ) AS vc
WHERE vr.election_id = ew.election_id
  AND vr.entity_id   = ew.entity_id
  AND vr.election_id = tf.election_id
  AND vr.station_id  = vc.station_id
  AND vr.election_id IN (
      SELECT election_id FROM election WHERE region_id = 141
  );
  
 
 
 -- Прво создај temp табела со voter counts
CREATE TEMP TABLE tmp_voter_counts AS
SELECT station_id, COUNT(*) AS voter_count
FROM voter
GROUP BY station_id;

CREATE INDEX ON tmp_voter_counts(station_id);

-- Потоа UPDATE со JOIN на temp табела
UPDATE vote_result vr
SET votes = GREATEST(1, ROUND(
    tvc.voter_count
    * tf.turnout
    * ew.weight
    * (0.90 + random() * 0.20)
)::int)
FROM
    (VALUES
        (1,3001,0.326),(1,3002,0.234),(1,3003,0.121),(1,1004,0.075),(1,1011,0.060),(1,1012,0.055),(1,1013,0.129),
        (2,3004,0.488),(2,3005,0.236),(2,1003,0.128),(2,1004,0.083),(2,1011,0.030),(2,1010,0.035),
        (3,3004,0.390),(3,3006,0.328),(3,1004,0.102),(3,1011,0.059),(3,1009,0.040),(3,1010,0.030),(3,1008,0.025),(3,1014,0.016),(3,1015,0.010),
        (4,3009,0.425),(4,1002,0.254),(4,1003,0.140),(4,1004,0.057),(4,1011,0.040),(4,1009,0.040),(4,1008,0.044),
        (5,3010,0.387),(5,1002,0.369),(5,1003,0.073),(5,1004,0.052),(5,1005,0.040),(5,1006,0.025),(5,1007,0.020),(5,1009,0.017),(5,1010,0.017),
        (6,3012,0.550),(6,1001,0.250),(6,1003,0.100),(6,1006,0.040),(6,1005,0.020),(6,1007,0.020),(6,1004,0.020),
        (7,3013,0.348),(7,1001,0.356),(7,1003,0.118),(7,1006,0.085),(7,1007,0.040),(7,1005,0.028),(7,1004,0.025),
        (8,3015,0.436),(8,3016,0.153),(8,1003,0.111),(8,1006,0.068),(8,1007,0.060),(8,1005,0.050),(8,1004,0.122),
        (9,9027,0.634),(9,9034,0.366),
        (10,9028,0.556),(10,9029,0.444),
        (11,9030,0.514),(11,9031,0.486),
        (12,9032,0.654),(12,9033,0.346),
        (13,1001,0.420),(13,1002,0.300),(13,1003,0.100),(13,1004,0.080),(13,1011,0.050),(13,1010,0.050),
        (14,1001,0.400),(14,1002,0.350),(14,1003,0.080),(14,1004,0.060),(14,1005,0.040),(14,1006,0.035),(14,1007,0.035),
        (15,1001,0.340),(15,1002,0.400),(15,1003,0.090),(15,1006,0.060),(15,1005,0.050),(15,1007,0.060),
        (16,9901,0.950),(16,9902,0.050),
        (17,9901,0.910),(17,9902,0.090)
    ) AS ew(election_id, entity_id, weight),
    (VALUES
        (1,0.56),(2,0.57),(3,0.63),(4,0.61),(5,0.67),(6,0.42),(7,0.51),(8,0.55),
        (9,0.57),(10,0.55),(11,0.46),(12,0.55),
        (13,0.48),(14,0.60),(15,0.51),
        (16,0.75),(17,0.37)
    ) AS tf(election_id, turnout),
    tmp_voter_counts tvc
WHERE vr.election_id = ew.election_id
  AND vr.entity_id   = ew.entity_id
  AND vr.election_id = tf.election_id
  AND vr.station_id  = tvc.station_id
  AND vr.election_id IN (
      SELECT election_id FROM election WHERE region_id = 141
  );

-- Исчисти
DROP TABLE tmp_voter_counts;

SELECT SUM(votes) as sum_votes FROM vote_result;

SELECT 
    COUNT(*) AS vr_rows,
    SUM(votes) AS sum_votes,
    AVG(votes)::int AS avg_votes
FROM vote_result;





INSERT INTO public.election_type (election_type_id, type_name) VALUES(1, 'Presidential');
INSERT INTO public.election_type (election_type_id, type_name) VALUES(2, 'Parliamentary');
INSERT INTO public.election_type (election_type_id, type_name) VALUES(3, 'Local/Municipal');
INSERT INTO public.election_type (election_type_id, type_name) VALUES(4, 'Subnational');
INSERT INTO public.election_type (election_type_id, type_name) VALUES(5, 'Supranational');
INSERT INTO public.election_type (election_type_id, type_name) VALUES(6, 'Referendum');
INSERT INTO public.election_type (election_type_id, type_name) VALUES(7, 'By-Election');
INSERT INTO public.election_type (election_type_id, type_name) VALUES(8, 'Party Primary');
INSERT INTO public.election_type (election_type_id, type_name) VALUES(9, 'Internal Election');




INSERT INTO public.region_type (region_type_id, "name", description) VALUES(1, 'Country', 'Top-level sovereign state');
INSERT INTO public.region_type (region_type_id, "name", description) VALUES(2, 'First-level administrative division', 'State / Region / Province');
INSERT INTO public.region_type (region_type_id, "name", description) VALUES(3, 'Second-level administrative division', 'County / District / Municipality');
INSERT INTO public.region_type (region_type_id, "name", description) VALUES(4, 'Third-level administrative division', 'City / Town / Local unit');
INSERT INTO public.region_type (region_type_id, "name", description) VALUES(5, 'Fourth-level administrative division', 'Neighborhood / Ward / Precinct');





INSERT INTO public.winner_method (method_id, method_name) VALUES(1, 'Plurality (FPTP)');
INSERT INTO public.winner_method (method_id, method_name) VALUES(2, 'Absolute Majority');
INSERT INTO public.winner_method (method_id, method_name) VALUES(3, 'Two-Round Runoff');
INSERT INTO public.winner_method (method_id, method_name) VALUES(4, 'Instant Runoff (IRV)');
INSERT INTO public.winner_method (method_id, method_name) VALUES(5, 'Approval Voting');
INSERT INTO public.winner_method (method_id, method_name) VALUES(6, 'Borda Count');
INSERT INTO public.winner_method (method_id, method_name) VALUES(7, 'Condorcet Method');
INSERT INTO public.winner_method (method_id, method_name) VALUES(8, 'D’Hondt');
INSERT INTO public.winner_method (method_id, method_name) VALUES(9, 'Sainte-Laguë');
INSERT INTO public.winner_method (method_id, method_name) VALUES(10, 'Hare Quota');
INSERT INTO public.winner_method (method_id, method_name) VALUES(11, 'Droop Quota');
INSERT INTO public.winner_method (method_id, method_name) VALUES(12, 'Single Transferable Vote (STV)');
INSERT INTO public.winner_method (method_id, method_name) VALUES(13, 'Mixed-Member Proportional (MMP)');






CREATE TABLE _stage_regions (
    country_name TEXT,
    region_name TEXT,
    municipality_name TEXT
);


COPY _stage_region_import
FROM 'C:/Users/Luna/Downloads/regions.csv'
DELIMITER ','
CSV HEADER;


INSERT INTO region(region_id, name, region_type_id, parent_region_id)
SELECT
    ROW_NUMBER() OVER (),
    country_name,
    1,
    NULL
FROM _stage_regions
GROUP BY country_name;





INSERT INTO region(region_id, name, region_type_id, parent_region_id)
SELECT
    1000 + ROW_NUMBER() OVER (),
    s.region_name,
    2,
    r.region_id
FROM _stage_regions s
JOIN region r
    ON r.name = s.country_name
   AND r.region_type_id = 1
GROUP BY s.region_name, r.region_id;




INSERT INTO region(region_id, name, region_type_id, parent_region_id)
SELECT
    1100 + ROW_NUMBER() OVER (),
    s.municipality_name,
    3,
    r.region_id
FROM _stage_regions s
JOIN region r
    ON r.name = s.region_name
   AND r.region_type_id = 2;



DROP TABLE _state_regions;








CREATE TABLE _stage_polling_station (
    station_id       int8,
    "name"           varchar(255),
    municipality_id  int8,
    address          text,
    registered_voter int,
    voting_object    text
);




\copy _stage_polling_station FROM 'polling_stations.csv' WITH (FORMAT csv, HEADER true);



INSERT INTO public.polling_station (station_id, "name", municipality_id, address, registered_voter, voting_object)
SELECT station_id, "name", municipality_id, address, registered_voter, voting_object 
FROM _stage_polling_station;



DROP TABLE _stage_polling_station;





DO $$
DECLARE
    v_batch     INT := 500000;
    v_updated   INT;
    v_total     INT := 0;
BEGIN
    LOOP
        UPDATE person p
        SET    municipality_id = ps.municipality_id
        FROM   voter           v
        JOIN   polling_station ps ON ps.station_id = v.station_id
        WHERE  v.person_id = p.person_id
        AND    p.municipality_id IS NULL
        AND    p.ctid IN (
            SELECT ctid
            FROM   person
            WHERE  municipality_id IS NULL
            LIMIT  v_batch
        );

        GET DIAGNOSTICS v_updated = ROW_COUNT;
        v_total := v_total + v_updated;
        RAISE NOTICE 'Updated % rows so far...', v_total;

        EXIT WHEN v_updated = 0;
    END LOOP;

    RAISE NOTICE 'Done. Total updated: %', v_total;
END $$;

SELECT
    COUNT()                          AS total,
    COUNT(municipality_id)            AS with_municipality,
    COUNT() - COUNT(municipality_id) AS still_null
FROM person;

ANALYZE person;