Context Navigation

Back to DatabaseCreation

DatabaseCreation: dml.sql

File dml.sql, 135.3 KB (added by 231071, 7 days ago)

Line
1
2	DROP TABLE IF EXISTS _stage_male_names CASCADE;
3	DROP TABLE IF EXISTS _stage_female_names CASCADE;
4	DROP TABLE IF EXISTS _stage_surnames CASCADE;
5
6	CREATE TABLE _stage_male_names (
7	id SERIAL PRIMARY KEY,
8	ime VARCHAR(50) NOT NULL
9	);
10
11	CREATE TABLE _stage_female_names (
12	id SERIAL PRIMARY KEY,
13	ime VARCHAR(50) NOT NULL
14	);
15
16	CREATE TABLE _stage_surnames (
17	id SERIAL PRIMARY KEY,
18	surname VARCHAR(50) NOT NULL
19	);
20
21
22	-- ─────────────────────────────────────────────────────
23	-- 2. LOAD YOUR CSV FILES
24	--
25	-- \copy uses the CLIENT machine's file path — works for
26	-- any user via the psql command-line client.
27	-- If you are a superuser connected locally you can also use:
28	-- COPY _stage_male_names (name) FROM '/absolute/path/male_names.csv' ...;
29	--
30	-- Adjust the file paths below to match where your CSVs live.
31	-- ─────────────────────────────────────────────────────
32
33	\copy _stage_male_names (ime) FROM 'C:\Users\acemi\OneDrive\Desktop\male_names.csv' WITH (FORMAT csv, HEADER true);
34	\copy _stage_female_names (ime) FROM 'female_names.csv' WITH (FORMAT csv, HEADER true);
35	\copy _stage_surnames (surname) FROM 'surnames.csv' WITH (FORMAT csv, HEADER true);
36
37	select * from surnames_final;
38
39	-- ─────────────────────────────────────────────────────
40	-- 3. VERIFY LOADS
41	-- ─────────────────────────────────────────────────────
42	INSERT INTO _stage_male_names(name) VALUES ('John'), ('Michael'), ('David');
43
44	DO $$
45	BEGIN
46	RAISE NOTICE '──────────────────────────────────────';
47	RAISE NOTICE 'Name pool sizes:';
48	RAISE NOTICE ' Male names : %', (SELECT COUNT(*) FROM male_names);
49	RAISE NOTICE ' Female names : %', (SELECT COUNT(*) FROM female_names);
50	RAISE NOTICE ' Surnames : %', (SELECT COUNT(*) FROM surnames_final);
51	RAISE NOTICE '──────────────────────────────────────';
52	END $$;
53
54
55	-- ─────────────────────────────────────────────────────
56	-- 4. PRE-COMPUTE POOL SIZES
57	-- Stored once in a temp table so the scalar subqueries
58	-- inside the main INSERT are evaluated exactly once,
59	-- not once per row.
60	-- ─────────────────────────────────────────────────────
61
62	CREATE TEMP TABLE _name_counts AS
63	SELECT
64	(SELECT COUNT(*) FROM male_names)::INT AS m_cnt,
65	(SELECT COUNT(*) FROM female_names)::INT AS f_cnt,
66	(SELECT COUNT(*) FROM surnames_final)::INT AS s_cnt;
67
68
69	-- ─────────────────────────────────────────────────────
70	-- 5. MAIN INSERT
71	--
72	-- Architecture:
73	-- Inner subquery — generate_series produces 10.1M base rows.
74	-- Each row gets a random DOB, gender, and
75	-- pre-drawn random indices into the name pools.
76	-- The 1% overshoot (10.1M vs 10M) absorbs
77	-- any rows lost to ON CONFLICT.
78	--
79	-- Outer SELECT — joins the base rows to the staging name
80	-- tables by the pre-drawn index, and computes
81	-- the window-function dob_seq used to build
82	-- the 13-digit person_id.
83	--
84	-- Random index formula: 1 + FLOOR(RANDOM() * pool_size)
85	-- Gives a uniform integer in [1, pool_size] matching the
86	-- SERIAL PKs of the staging tables.
87	--
88	-- ON CONFLICT DO NOTHING — silently skips the rare case where
89	-- two generated rows share (name, surname, date_of_birth).
90	-- With typical pools (≥ 1 000 names × ≥ 5 000 surnames ×
91	-- 34 697 distinct DODs) the collision space exceeds 170 billion
92	-- combinations, so duplicates are effectively impossible.
93	--
94	-- Expected runtime: 10–25 minutes depending on hardware.
95	-- ─────────────────────────────────────────────────────
96
97	ALTER TABLE male_names DROP COLUMN id;
98
99	-- 2. Add it back as a SERIAL.
100	-- PostgreSQL will automatically populate it for all existing rows!
101	ALTER TABLE female_names ADD COLUMN id SERIAL PRIMARY KEY;
102	select * from surnames_final;
103
104	INSERT INTO person (person_id, name, surname, date_of_birth, gender)
105	SELECT
106	-- ── person_id: YYYYMMDD + 5-digit birth-date sequence ──
107	(
108	TO_CHAR(b.dob, 'YYYYMMDD')
109	\|\|
110	LPAD(
111	ROW_NUMBER() OVER (
112	PARTITION BY b.dob -- restart count per birth date
113	ORDER BY b.seq -- stable order within each day
114	)::TEXT,
115	5, '0'
116	)
117	)::BIGINT AS person_id,
118
119	-- ── name: chosen from pool matching gender ──────────────
120	CASE b.gender
121	WHEN 'M' THEN mn.ime
122	WHEN 'F' THEN fn.ime
123	ELSE -- 'O': randomly pick from either pool
124	CASE WHEN RANDOM() < 0.5 THEN mn.ime ELSE fn.ime END
125	END AS name,
126
127	sr.c2,
128	b.dob AS date_of_birth,
129	b.gender
130
131	FROM (
132	-- ── Base row generation ─────────────────────────────────
133	SELECT
134	gs.seq,
135
136	-- Random date of birth: full age range, including under-18
137	DATE '1930-01-01'
138	+ (FLOOR(RANDOM() * (DATE '2024-12-31' - DATE '1930-01-01' + 1)))::INT
139	AS dob,
140
141	-- Gender: 49 % M / 49 % F / 2 % O
142	CASE
143	WHEN RANDOM() < 0.49 THEN 'M'::CHAR(1)
144	WHEN RANDOM() < 0.98 THEN 'F'::CHAR(1)
145	ELSE 'O'::CHAR(1)
146	END AS gender,
147
148	-- Pre-draw name-pool indices (uniform in [1, pool_size])
149	(1 + FLOOR(RANDOM() * (SELECT m_cnt FROM _name_counts)))::INT AS m_idx,
150	(1 + FLOOR(RANDOM() * (SELECT f_cnt FROM _name_counts)))::INT AS f_idx,
151	(1 + FLOOR(RANDOM() * (SELECT s_cnt FROM _name_counts)))::INT AS s_idx
152
153	FROM generate_series(1, 1010000) AS gs(seq) -- 1 % overshoot
154	) AS b
155
156	LEFT JOIN male_names mn ON mn.id = b.m_idx
157	LEFT JOIN female_names fn ON fn.id = b.f_idx
158	LEFT JOIN surnames_final sr ON sr.c1 = b.s_idx
159
160	ON CONFLICT (name, surname, date_of_birth) DO NOTHING;
161	select * from male_names;
162
163	INSERT INTO person (person_id, name, surname, date_of_birth, gender)
164	SELECT
165	(
166	TO_CHAR(b.dob, 'YYYYMMDD')
167	\|\|
168	LPAD(
169	(ROW_NUMBER() OVER (
170	PARTITION BY b.dob
171	ORDER BY b.seq
172	) + 10000)::TEXT, -- offset avoids all existing IDs
173	5, '0'
174	)
175	)::BIGINT AS person_id,
176	CASE b.gender
177	WHEN 'M' THEN mn.c1
178	WHEN 'F' THEN fn.c1
179	ELSE CASE WHEN RANDOM() < 0.5 THEN mn.c1 ELSE fn.c1 END
180	END AS name,
181	sr.c2,
182	b.dob AS date_of_birth,
183	b.gender
184	FROM (
185	SELECT
186	gs.seq,
187	-- Adults only: DOB capped at 2006 so everyone is 18+ as of 2024
188	DATE '1960-01-01'
189	+ (FLOOR(RANDOM() * (DATE '2006-12-31' - DATE '1960-01-01' + 1)))::INT AS dob,
190	CASE
191	WHEN RANDOM() < 0.49 THEN 'M'::CHAR(1)
192	WHEN RANDOM() < 0.98 THEN 'F'::CHAR(1)
193	ELSE 'O'::CHAR(1)
194	END AS gender,
195	(1 + FLOOR(RANDOM() * (SELECT m_cnt FROM _name_counts)))::INT AS m_idx,
196	(1 + FLOOR(RANDOM() * (SELECT f_cnt FROM _name_counts)))::INT AS f_idx,
197	(1 + FLOOR(RANDOM() * (SELECT s_cnt FROM _name_counts)))::INT AS s_idx
198	FROM generate_series(1, 2000000) AS gs(seq) -- 2M to be safe, targeting 1.5M net
199	) AS b
200	LEFT JOIN male_names mn ON mn.id = b.m_idx
201	LEFT JOIN female_names fn ON fn.id = b.f_idx
202	LEFT JOIN surnames_final sr ON sr.id = b.s_idx
203	ON CONFLICT (name, surname, date_of_birth) DO NOTHING;
204
205	select * from surnames_final;
206
207
208	DROP TABLE IF EXISTS male_names;
209	DROP TABLE IF EXISTS female_names;
210	DROP TABLE IF EXISTS surnames_final;
211	DROP TABLE IF EXISTS _name_counts;
212
213
214
215	INSERT INTO party_leader (leader_id, person_id)
216	SELECT
217	ROW_NUMBER() OVER (ORDER BY person_id) AS leader_id,
218	person_id
219	FROM (
220	SELECT person_id
221	FROM person
222	WHERE date_of_birth <= CURRENT_DATE - INTERVAL '30 years'
223	ORDER BY RANDOM()
224	LIMIT 500
225	) p;
226
227
228	DROP TABLE IF EXISTS _stage_prefixes;
229	DROP TABLE IF EXISTS _stage_suffixes;
230
231	CREATE TEMP TABLE _stage_prefixes (id SERIAL PRIMARY KEY, val VARCHAR(50));
232	CREATE TEMP TABLE _stage_suffixes (id SERIAL PRIMARY KEY, val VARCHAR(50));
233
234	INSERT INTO _stage_prefixes (val) VALUES
235	('National'), ('Democratic'), ('Social'), ('Liberal'), ('Conservative'),
236	('Green'), ('People''s'), ('United'), ('Freedom'), ('Progressive'),
237	('Republican'), ('Labor'), ('Workers'), ('Christian'), ('Independent'),
238	('New'), ('Reform'), ('Patriotic'), ('Citizens'), ('Popular'),
239	('Communist'), ('Solidarity'), ('Justice'), ('Civic'), ('Alliance');
240
241	INSERT INTO _stage_suffixes (val) VALUES
242	('Party'), ('Alliance'), ('Movement'), ('Front'), ('Union'),
243	('Force'), ('Coalition'), ('League'), ('Congress'), ('Federation'),
244	('Bloc'), ('Initiative'), ('Network'), ('Platform'), ('Assembly');
245
246	-- ── 2a. INSERT parties (no leader / parent yet) ──────
247	INSERT INTO political_party (party_id, name, abbreviation, founded_year)
248	SELECT
249	ROW_NUMBER() OVER (ORDER BY p.val, s.val) AS party_id,
250	p.val \|\| ' ' \|\| s.val AS name,
251	-- Abbreviation: first 2 letters of prefix + first 2 of suffix, uppercased
252	UPPER(LEFT(p.val, 2) \|\| LEFT(s.val, 2)) AS abbreviation,
253	-- Founded anywhere from 1900 to 2020
254	1900 + FLOOR(RANDOM() * 121)::INT AS founded_year
255	FROM _stage_prefixes p
256	CROSS JOIN _stage_suffixes s
257	where p.val <> s.val
258	ON CONFLICT (name) DO NOTHING;
259
260	select * from political_party;
261
262	DROP TABLE IF EXISTS _stage_prefixes;
263	DROP TABLE IF EXISTS _stage_suffixes;
264
265	WITH numbered_parties AS (
266	SELECT party_id,
267	ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn
268	FROM political_party
269	),
270	numbered_leaders AS (
271	SELECT leader_id,
272	ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn
273	FROM party_leader
274	),
275	leader_count AS (
276	SELECT COUNT(*) AS cnt FROM party_leader
277	)
278	UPDATE political_party pp
279	SET leader_id = nl.leader_id
280	FROM numbered_parties np
281	JOIN numbered_leaders nl
282	ON nl.rn = ((np.rn - 1) % (SELECT cnt FROM leader_count) + 1)
283	WHERE np.party_id = pp.party_id;
284
285	WITH candidates AS (
286	SELECT party_id,
287	ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn
288	FROM political_party
289	),
290	parents AS (
291	SELECT party_id AS parent_id,
292	ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn
293	FROM political_party
294	),
295	total AS (SELECT COUNT(*) AS cnt FROM political_party)
296	UPDATE political_party pp
297	SET parent_party_id = pa.parent_id
298	FROM candidates c
299	JOIN parents pa ON pa.rn = ((c.rn - 1) % (SELECT cnt FROM total) + 1)
300	WHERE c.party_id = pp.party_id
301	AND pa.parent_id <> pp.party_id -- no self-reference
302	AND c.rn % 10 < 3;
303
304	INSERT INTO candidate (candidate_id, person_id)
305	SELECT
306	ROW_NUMBER() OVER (ORDER BY person_id) AS candidate_id,
307	person_id
308	FROM (
309	SELECT person_id
310	FROM person
311	WHERE date_of_birth <= CURRENT_DATE - INTERVAL '18 years'
312	ORDER BY RANDOM()
313	LIMIT 50000
314	) p;
315
316
317	INSERT INTO election (
318	election_id, name, election_type_id, region_id,
319	election_date, description, status, winner_method_id, total_seats
320	)
321	VALUES
322
323	-- ── PARLIAMENTARY ────────────────────────────────────
324	(1, 'Parliamentary Election 2006',
325	2, 141, '2006-07-05',
326	'Early parliamentary elections held after the 2005 local elections.',
327	0, 8, 120),
328
329	(2, 'Parliamentary Election 2008',
330	2, 141, '2008-06-01',
331	'Snap elections called amid political tensions.',
332	0, 8, 120),
333
334	(3, 'Parliamentary Election 2011',
335	2, 141, '2011-06-05',
336	'Early elections following a budget dispute in parliament.',
337	0, 8, 120),
338
339	(4, 'Parliamentary Election 2014',
340	2, 141, '2014-04-27',
341	'Parliamentary elections held concurrently with the presidential election.',
342	0, 8, 120),
343
344	(5, 'Parliamentary Election 2016',
345	2, 141, '2016-12-11',
346	'Elections held amid the political crisis following wiretapping scandals.',
347	0, 8, 120),
348
349	(6, 'Parliamentary Election 2019',
350	2, 141, '2019-04-12',
351	'Boycotted by opposition; results subsequently annulled.',
352	0, 8, 120),
353
354	(7, 'Parliamentary Election 2020',
355	2, 141, '2020-07-15',
356	'First elections held under the constitutional name North Macedonia.',
357	0, 8, 120),
358
359	(8, 'Parliamentary Election 2024',
360	2, 141, '2024-05-08',
361	'Parliamentary elections held concurrently with the presidential election.',
362	0, 8, 120),
363
364	-- ── PRESIDENTIAL ─────────────────────────────────────
365	(9, 'Presidential Election 2009',
366	1, 141, '2009-03-22',
367	'Gjorge Ivanov elected president in the second round.',
368	0, 3, NULL),
369
370	(10, 'Presidential Election 2014',
371	1, 141, '2014-04-27',
372	'Gjorge Ivanov re-elected for a second term.',
373	0, 3, NULL),
374
375	(11, 'Presidential Election 2019',
376	1, 141, '2019-04-21',
377	'Stevo Pendarovski elected; turnout quorum not met in first round.',
378	0, 3, NULL),
379
380	(12, 'Presidential Election 2024',
381	1, 141, '2024-05-08',
382	'Gordana Siljanovska-Davkova elected as first female president.',
383	0, 3, NULL),
384
385	-- ── LOCAL / MUNICIPAL ────────────────────────────────
386	(13, 'Local Elections 2013',
387	3, 141, '2013-03-24',
388	'Municipal elections for mayors and council members across all municipalities.',
389	0, 3, NULL),
390
391	(14, 'Local Elections 2017',
392	3, 141, '2017-10-15',
393	'Municipal elections following the resolution of the political crisis.',
394	0, 3, NULL),
395
396	(15, 'Local Elections 2021',
397	3, 141, '2021-10-17',
398	'Municipal elections held under the SDSM-led government.',
399	0, 3, NULL),
400
401	-- ── REFERENDUM ───────────────────────────────────────
402	(16, 'Independence Referendum 1991',
403	6, 141, '1991-09-08',
404	'Referendum on independence from Yugoslavia. 95% voted in favour.',
405	0, 1, NULL),
406
407	(17, 'Prespa Agreement Referendum 2018',
408	6, 141, '2018-09-30',
409	'Referendum on the Prespa Agreement to rename the country. Quorum not met.',
410	0, 1, NULL)
411
412	ON CONFLICT (name) DO NOTHING;
413
414
415	INSERT INTO person (person_id, name, surname, date_of_birth, gender)
416	VALUES
417
418	-- ── NORTH MACEDONIA ──────────────────────────────────
419	(1978090199001, 'Hristijan', 'Mickoski', '1978-09-01', 'M'), -- VMRO-DPMNE
420	(1974100199001, 'Zoran', 'Zaev', '1974-10-01', 'M'), -- SDSM (former)
421	(1974040199001, 'Oliver', 'Spasovski', '1974-04-01', 'M'), -- SDSM (current)
422	(1959020499001, 'Ali', 'Ahmeti', '1959-02-04', 'M'), -- DUI
423	(1960020199001, 'Menduh', 'Thaci', '1960-02-01', 'M'), -- DPA (former)
424	(1980010199001, 'Afrim', 'Gashi', '1980-01-01', 'M'), -- DPA (current)
425	(1984010199001, 'Bilal', 'Kasami', '1984-01-01', 'M'), -- BESA
426	(1977010199001, 'Arben', 'Taravari', '1977-01-01', 'M'), -- Alliance for Albanians
427	(1980060199001, 'Dimitar', 'Apasiev', '1980-06-01', 'M'), -- Levica
428	(1966010199001, 'Stevcho', 'Jakimovski', '1966-01-01', 'M'), -- GROM
429	(1952010199001, 'Petar', 'Gosev', '1952-01-01', 'M'), -- LDP (historical)
430	(1945010199001, 'Kiro', 'Gligorov', '1945-01-01', 'M'), -- SDSM founder
431	(1958010199001, 'Ljubco', 'Georgievski', '1958-01-01', 'M'), -- VMRO-DPMNE historical
432	(1960050199001, 'Branko', 'Crvenkovski', '1960-05-01', 'M'), -- SDSM historical
433	(1966030199001, 'Nikola', 'Gruevski', '1970-08-31', 'M'), -- VMRO-DPMNE historical
434	(1955010199001, 'Abdulakim', 'Ademi', '1955-01-01', 'M'), -- DUI historical
435	(1958030199001, 'Arben', 'Xhaferi', '1958-03-01', 'M'), -- DPA historical
436	(1963010199001, 'Imer', 'Selmani', '1963-01-01', 'M'), -- PDP
437	(1970010199001, 'Nevzat', 'Halili', '1970-01-01', 'M'), -- NDP
438	(1975010199001, 'Stevo', 'Pendarovski', '1963-03-07', 'M'), -- SDSM presidential
439	(1972010199001, 'Ljupcho', 'Zikov', '1972-01-01', 'M'), -- VMRO-NP
440	(1968010199001, 'Liljana', 'Popovska', '1968-01-01', 'F'), -- LDP historical
441	(1980010299001, 'Ziadin', 'Sela', '1980-01-02', 'M'), -- Alliance for Albanians co-founder
442	(1975020199001, 'Saso', 'Mijalkov', '1975-02-01', 'M'), -- VMRO-DPMNE historical
443	(1969010199001, 'Radmila', 'Sekerinska', '1969-01-01', 'F'), -- SDSM historical
444
445	-- ── SERBIA ───────────────────────────────────────────
446	(1970030599001, 'Aleksandar', 'Vucic', '1970-03-05', 'M'), -- SNS
447	(1966010199002, 'Ivica', 'Dacic', '1966-01-01', 'M'), -- SPS
448	(1966051399001, 'Zoran', 'Lutovac', '1966-05-13', 'M'), -- DS
449	(1944032499001, 'Vojislav', 'Kostunica', '1944-03-24', 'M'), -- DSS
450	(1954101199001, 'Vojislav', 'Seselj', '1954-10-11', 'M'), -- SRS
451	(1946112999001, 'Vuk', 'Draskovic', '1946-11-29', 'M'), -- SPO
452	(1967040399001, 'Dragan', 'Dilas', '1967-04-03', 'M'), -- SSP
453	(1970110899001, 'Bosko', 'Obradovic', '1970-11-08', 'M'), -- Dveri
454	(1963010199002, 'Zoran', 'Djindjic', '1952-08-01', 'M'), -- DS historical (assassinated)
455	(1952020199001, 'Slobodan', 'Milosevic', '1941-08-20', 'M'), -- SPS founder (historical)
456	(1958010199002, 'Velimir', 'Ilic', '1949-09-09', 'M'), -- Nova Srbija
457	(1965010199001, 'Cedomir', 'Jovanovic', '1965-01-01', 'M'), -- LDP Serbia
458	(1968010199002, 'Maja', 'Gojkovic', '1968-01-01', 'F'), -- SNS historical
459	(1971010199001, 'Ana', 'Brnabic', '1975-09-18', 'F'), -- SNS
460	(1960010199001, 'Tomislav', 'Nikolic', '1952-02-15', 'M'), -- SNS co-founder
461	(1967010199001, 'Nebojsa', 'Stefanovic', '1967-01-01', 'M'), -- SNS
462	(1975010199002, 'Milos', 'Jovanovic', '1975-01-01', 'M'), -- NADA/DSS current
463	(1968020199001, 'Branko', 'Ruzic', '1968-02-01', 'M'), -- SDPS
464	(1979010199001, 'Predrag', 'Milosavljevic', '1979-01-01', 'M'), -- PUPS
465	(1963030199001, 'Sulejman', 'Ugljanin', '1950-06-22', 'M'), -- SDA Sandzak
466	(1970050199001, 'Usame', 'Zukorlic', '1967-11-20', 'M'), -- BSZ
467	(1965060199001, 'Andras', 'Agostonc', '1965-06-01', 'M'), -- SVM
468	(1958020199001, 'Mladen', 'Obradovic', '1958-02-01', 'M'), -- Obraz historical
469	(1969030199001, 'Aleksandar', 'Sapic', '1977-02-25', 'M'), -- SNS Belgrade
470	(1974010199001, 'Boris', 'Tadic', '1958-01-15', 'M') -- DS historical president
471
472	ON CONFLICT (person_id) DO NOTHING;
473
474
475	INSERT INTO party_leader (leader_id, person_id)
476	VALUES
477	-- NMK leaders
478	(1001, 1978090199001), -- Mickoski
479	(1002, 1974100199001), -- Zaev
480	(1003, 1974040199001), -- Spasovski
481	(1004, 1959020499001), -- Ahmeti
482	(1005, 1960020199001), -- Menduh Thaci
483	(1006, 1980010199001), -- Afrim Gashi
484	(1007, 1984010199001), -- Kasami
485	(1008, 1977010199001), -- Taravari
486	(1009, 1980060199001), -- Apasiev
487	(1010, 1966010199001), -- Jakimovski
488	(1011, 1952010199001), -- Gosev
489	(1012, 1945010199001), -- Gligorov
490	(1013, 1958010199001), -- Georgievski
491	(1014, 1960050199001), -- Crvenkovski
492	(1015, 1966030199001), -- Gruevski
493	(1016, 1955010199001), -- Ademi
494	(1017, 1958030199001), -- Xhaferi
495	(1018, 1963010199001), -- Selmani
496	(1019, 1970010199001), -- Halili
497	(1020, 1975010199001), -- Pendarovski
498	(1021, 1972010199001), -- Zikov
499	(1022, 1968010199001), -- Popovska
500	(1023, 1980010299001), -- Ziadin Sela
501	(1024, 1969010199001), -- Sekerinska
502
503	-- Serbia leaders
504	(2001, 1970030599001), -- Vucic
505	(2002, 1966010199002), -- Dacic
506	(2003, 1966051399001), -- Lutovac
507	(2004, 1944032499001), -- Kostunica
508	(2005, 1954101199001), -- Seselj
509	(2006, 1946112999001), -- Draskovic
510	(2007, 1967040399001), -- Dilas
511	(2008, 1970110899001), -- Obradovic
512	(2009, 1963010199002), -- Djindjic
513	(2010, 1952020199001), -- Milosevic
514	(2011, 1958010199002), -- Ilic
515	(2012, 1965010199001), -- Cedomir Jovanovic
516	(2013, 1960010199001), -- Nikolic
517	(2014, 1967010199001), -- Nebojsa Stefanovic
518	(2015, 1975010199002), -- Milos Jovanovic
519	(2016, 1968020199001), -- Ruzic
520	(2017, 1963030199001), -- Ugljanin
521	(2018, 1970050199001), -- Zukorlic
522	(2019, 1965060199001), -- Agostonc
523	(2020, 1974010199001) -- Boris Tadic
524
525	ON CONFLICT (person_id) DO NOTHING;
526
527
528	INSERT INTO political_party (
529	party_id, name, abbreviation, description,
530	founded_year, ideology, leader_id, parent_party_id
531	)
532	VALUES
533
534	-- ── NORTH MACEDONIA ──────────────────────────────────
535	(1001, 'VMRO-DPMNE',
536	'VMRO-DPMNE',
537	'Внатрешна македонска револуционерна организација. Centre-right nationalist party, dominant force in Macedonian politics.',
538	1990, 'Nationalism, Conservatism, Christian Democracy',
539	1001, NULL),
540
541	(1002, 'Social Democratic Union of Macedonia',
542	'SDSM',
543	'Социјалдемократски сојуз на Македонија. Centre-left social democratic party.',
544	1991, 'Social Democracy, Liberalism, Pro-Europeanism',
545	1003, NULL),
546
547	(1003, 'Democratic Union for Integration',
548	'DUI',
549	'Bashkimi Demokratik për Integrim. Albanian minority party formed after the 2001 armed conflict.',
550	2002, 'Albanian minority rights, Liberalism, Pro-EU',
551	1004, NULL),
552
553	(1004, 'Democratic Party of Albanians',
554	'DPA',
555	'Partia Demokratike Shqiptare. Albanian minority conservative party.',
556	1997, 'Albanian minority rights, Conservatism',
557	1006, NULL),
558
559	(1005, 'BESA Movement',
560	'BESA',
561	'Lëvizja BESA. Albanian minority party emphasising civic values and anti-corruption.',
562	2015, 'Albanian minority rights, Centrism, Civic nationalism',
563	1007, NULL),
564
565	(1006, 'Alliance for Albanians',
566	'AA',
567	'Aleanca për Shqiptarët. Albanian minority party formed by merger of smaller parties.',
568	2018, 'Albanian minority rights, Liberalism',
569	1008, NULL),
570
571	(1007, 'Levica',
572	'LEVICA',
573	'The Left. Left-wing party focused on social justice and anti-corruption.',
574	2015, 'Democratic Socialism, Anti-corruption, Euroscepticism',
575	1009, NULL),
576
577	(1008, 'GROM',
578	'GROM',
579	'Граѓанска опција за Македонија. Centrist civic party.',
580	2013, 'Centrism, Civic politics',
581	1010, NULL),
582
583	(1009, 'VMRO - Peoples Party',
584	'VMRO-NP',
585	'VMRO - Народна партија. Split from VMRO-DPMNE, centre-right.',
586	2004, 'Nationalism, Conservatism',
587	1021, NULL),
588
589	(1010, 'Liberal Democratic Party of Macedonia',
590	'LDP-MK',
591	'Либерално-демократска партија. Centrist liberal party.',
592	2004, 'Liberalism, Social Liberalism',
593	1022, NULL),
594
595	(1011, 'Party for Democratic Prosperity',
596	'PDP',
597	'Partia për Prosperitet Demokratik. First major Albanian political party in Macedonia.',
598	1990, 'Albanian minority rights, Social Democracy',
599	1018, NULL),
600
601	(1012, 'Socialist Party of Macedonia',
602	'SPM',
603	'Социјалистичка партија на Македонија. Left-wing historical party.',
604	1990, 'Socialism, Social Democracy',
605	NULL, NULL),
606
607	(1013, 'Liberal Party of Macedonia',
608	'LP-MK',
609	'Либерална партија на Македонија. Historical liberal party.',
610	1990, 'Liberalism',
611	1011, NULL),
612
613	(1014, 'National Democratic Revival',
614	'NDR',
615	'Rilindja Demokratike Kombëtare. Albanian minority party.',
616	2008, 'Albanian minority rights',
617	1019, NULL),
618
619	(1015, 'Democratic Renewal of Macedonia',
620	'DOM',
621	'Демократска обнова на Македонија. Small centrist party.',
622	2005, 'Centrism, Green politics',
623	NULL, NULL),
624
625	-- ── SERBIA ───────────────────────────────────────────
626	(2001, 'Serbian Progressive Party',
627	'SNS',
628	'Srpska napredna stranka. Dominant centre-right populist party founded by Vucic and Nikolic.',
629	2008, 'Conservatism, Populism, Pro-EU rhetoric',
630	2001, NULL),
631
632	(2002, 'Socialist Party of Serbia',
633	'SPS',
634	'Socijalistička partija Srbije. Founded by Slobodan Milosevic.',
635	1990, 'Socialism, Serbian nationalism',
636	2002, NULL),
637
638	(2003, 'Democratic Party',
639	'DS',
640	'Demokratska stranka. Centre-left social democratic party.',
641	1990, 'Social Democracy, Liberalism, Pro-EU',
642	2003, NULL),
643
644	(2004, 'Democratic Party of Serbia',
645	'DSS',
646	'Demokratska stranka Srbije. Conservative nationalist party founded by Kostunica.',
647	1992, 'Conservatism, Serbian nationalism, Euroscepticism',
648	2004, NULL),
649
650	(2005, 'Serbian Radical Party',
651	'SRS',
652	'Srpska radikalna stranka. Far-right ultranationalist party led by Seselj.',
653	1991, 'Ultra-nationalism, Greater Serbia, Social conservatism',
654	2005, NULL),
655
656	(2006, 'Serbian Renewal Movement',
657	'SPO',
658	'Srpski pokret obnove. Centre-right monarchist party led by Vuk Draskovic.',
659	1990, 'Monarchism, Conservatism, Serbian nationalism',
660	2006, NULL),
661
662	(2007, 'Freedom and Justice Party',
663	'SSP',
664	'Stranka slobode i pravde. Centre-left pro-European party founded by Dragan Dilas.',
665	2019, 'Social Democracy, Liberalism, Anti-corruption, Pro-EU',
666	2007, NULL),
667
668	(2008, 'Dveri',
669	'DVERI',
670	'Srpski pokret Dveri. Conservative social movement turned political party.',
671	2010, 'Social conservatism, Euroscepticism, Serbian nationalism',
672	2008, NULL),
673
674	(2009, 'Nova Srbija',
675	'NS',
676	'Nova Srbija. Centre-right regional party founded by Velimir Ilic.',
677	1998, 'Conservatism, Regionalism',
678	2011, NULL),
679
680	(2010, 'Liberal Democratic Party of Serbia',
681	'LDP-SRB',
682	'Liberalno demokratska partija. Progressive liberal party.',
683	2005, 'Liberalism, Pro-EU, Progressivism',
684	2012, NULL),
685
686	(2011, 'Alliance of Vojvodina Hungarians',
687	'SVM',
688	'Savez vojvođanskih Mađara. Ethnic Hungarian minority party in Vojvodina.',
689	1994, 'Hungarian minority rights, Regionalism, Liberalism',
690	2019, NULL),
691
692	(2012, 'Social-Democratic Party of Serbia',
693	'SDPS',
694	'Socijaldemokratska partija Srbije. Centre-left coalition partner of SNS.',
695	2009, 'Social Democracy, Centre-left',
696	2016, NULL),
697
698	(2013, 'Bosniak Democratic Union of Sandzak',
699	'BDZ',
700	'Bošnjačka demokratska zajednica Sandžaka. Bosniak minority party.',
701	2009, 'Bosniak minority rights, Regionalism',
702	2018, NULL),
703
704	(2014, 'G17 Plus',
705	'G17+',
706	'Economic think-tank turned political party focused on economic reform.',
707	2002, 'Liberalism, Economic reform',
708	NULL, NULL),
709
710	(2015, 'Together — Civic Serbia',
711	'ZAJEDNO',
712	'Zajedno. Opposition coalition of the 1990s opposing Milosevic.',
713	1996, 'Liberal democracy, Anti-authoritarianism',
714	NULL, NULL),
715
716	(2016, 'Democratic Opposition of Serbia',
717	'DOS',
718	'Demokratska opozicija Srbije. Broad coalition that ousted Milosevic in 2000.',
719	1999, 'Liberal democracy, Anti-authoritarianism',
720	2009, NULL),
721
722	(2017, 'New Democratic Party Serbia',
723	'NDP-SRB',
724	'Split from the Democratic Party, merged back later.',
725	2012, 'Social Democracy, Liberalism',
726	NULL, NULL),
727
728	(2018, 'Party of United Pensioners of Serbia',
729	'PUPS',
730	'Partija ujedinjenih penzionera Srbije. Pensioners interest party.',
731	2005, 'Pensioners interests, Centrism',
732	NULL, NULL),
733
734	(2019, 'Movement of Socialists',
735	'PS',
736	'Pokret socijalista. Nationalist socialist party.',
737	2008, 'Socialism, Serbian nationalism',
738	NULL, NULL),
739
740	(2020, 'People''s Party Serbia',
741	'NARODNA',
742	'Narodna stranka. Centre-right party founded by former DS members.',
743	2014, 'Conservatism, Pro-EU, Christian Democracy',
744	2015, NULL)
745
746	ON CONFLICT (name) DO NOTHING;
747
748
749	INSERT INTO election_cycle (cycle_id, election_id, round_number)
750	VALUES
751
752	-- ── Parliamentary — single round each ────────────────
753	(101, 1, 1), -- Parliamentary 2006
754	(102, 2, 1), -- Parliamentary 2008
755	(103, 3, 1), -- Parliamentary 2011
756	(104, 4, 1), -- Parliamentary 2014
757	(105, 5, 1), -- Parliamentary 2016
758	(106, 6, 1), -- Parliamentary 2019
759	(107, 7, 1), -- Parliamentary 2020
760	(108, 8, 1), -- Parliamentary 2024
761
762	-- ── Presidential — two rounds each ───────────────────
763	(201, 9, 1), -- Presidential 2009 Round 1 (22 Mar)
764	(202, 9, 2), -- Presidential 2009 Round 2 (05 Apr)
765	(203, 10, 1), -- Presidential 2014 Round 1 (27 Apr)
766	(204, 10, 2), -- Presidential 2014 Round 2 (25 May) -- quorum not met first attempt
767	(205, 11, 1), -- Presidential 2019 Round 1 (21 Apr)
768	(206, 11, 2), -- Presidential 2019 Round 2 (05 May)
769	(207, 12, 1), -- Presidential 2024 Round 1 (08 May)
770	(208, 12, 2), -- Presidential 2024 Round 2 (22 May)
771
772	-- ── Local — two rounds each ───────────────────────────
773	(301, 13, 1), -- Local 2013 Round 1 (24 Mar)
774	(302, 13, 2), -- Local 2013 Round 2 (07 Apr)
775	(303, 14, 1), -- Local 2017 Round 1 (15 Oct)
776	(304, 14, 2), -- Local 2017 Round 2 (29 Oct)
777	(305, 15, 1), -- Local 2021 Round 1 (17 Oct)
778	(306, 15, 2), -- Local 2021 Round 2 (31 Oct)
779
780	-- ── Referendums — single round each ──────────────────
781	(401, 16, 1), -- Independence Referendum 1991
782	(402, 17, 1) -- Prespa Agreement Referendum 2018
783
784	ON CONFLICT (election_id, round_number) DO NOTHING;
785
786	INSERT INTO party_coalition (coalition_id, name, election_id)
787	VALUES
788
789	-- ── Parliamentary 2006 (election_id=1) ───────────────
790	(1001, 'For Macedonia', 1), -- VMRO-DPMNE led
791	(1002, 'For European Future', 1), -- SDSM + LDP
792	(1003, 'Democratic Renewal', 1), -- DUI led
793
794	-- ── Parliamentary 2008 (election_id=2) ───────────────
795	(2001, 'For a Better Macedonia', 2), -- VMRO-DPMNE led
796	(2002, 'Sun - Coalition for Europe', 2), -- SDSM led
797
798	-- ── Parliamentary 2011 (election_id=3) ───────────────
799	(3001, 'For a Better Macedonia', 3), -- VMRO-DPMNE led
800	(3002, 'European Front', 3), -- SDSM + BDI
801
802	-- ── Parliamentary 2014 (election_id=4) ───────────────
803	(4001, 'For a Better Macedonia', 4), -- VMRO-DPMNE led
804	(4002, 'Coalition for a Better Macedonia', 4), -- SDSM led
805
806	-- ── Parliamentary 2016 (election_id=5) ───────────────
807	(5001, 'For Our Macedonia', 5), -- VMRO-DPMNE led
808	(5002, 'For European Future', 5), -- SDSM led
809
810	-- ── Parliamentary 2019 (election_id=6) ───────────────
811	(6001, 'Renewal of Macedonia', 6), -- VMRO-DPMNE led
812	(6002, 'European Front', 6), -- SDSM + DUI
813
814	-- ── Parliamentary 2020 (election_id=7) ───────────────
815	(7001, 'We Can', 7), -- SDSM + DUI
816	(7002, 'Renewal of Macedonia', 7), -- VMRO-DPMNE led
817
818	-- ── Parliamentary 2024 (election_id=8) ───────────────
819	(8001, 'Your Macedonia', 8), -- VMRO-DPMNE + DPA + others
820	(8002, 'Europe Now', 8), -- SDSM + DUI
821
822	-- ── Local 2013 (election_id=13) ──────────────────────
823	(13001, 'Together for Macedonia', 13), -- VMRO-DPMNE local coalition
824	(13002, 'For Our Cities', 13), -- SDSM local coalition
825
826	-- ── Local 2017 (election_id=14) ──────────────────────
827	(14001, 'For Our Macedonia', 14), -- VMRO-DPMNE local coalition
828	(14002, 'For European Macedonia', 14), -- SDSM local coalition
829
830	-- ── Local 2021 (election_id=15) ──────────────────────
831	(15001, 'Together We Can', 15), -- SDSM + DUI local coalition
832	(15002, 'For Macedonia', 15) -- VMRO-DPMNE local coalition
833
834	ON CONFLICT (name) DO NOTHING;
835
836
837	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available)
838	VALUES
839
840	-- ══════════════════════════════════════════════════════
841	-- PARLIAMENTARY 2006 (election_id=1)
842	-- ══════════════════════════════════════════════════════
843	(10001, 1, 1000, 35), -- Skopje
844	(10002, 1, 1001, 10), -- Eastern
845	(10003, 1, 1002, 12), -- Northeastern
846	(10004, 1, 1003, 15), -- Pelagonia
847	(10005, 1, 1004, 20), -- Polog
848	(10006, 1, 1005, 10), -- Southeastern
849	(10007, 1, 1006, 12), -- Southwestern
850	(10008, 1, 1007, 6), -- Vardar
851
852	-- ══════════════════════════════════════════════════════
853	-- PARLIAMENTARY 2008 (election_id=2)
854	-- ══════════════════════════════════════════════════════
855	(20001, 2, 1000, 35),
856	(20002, 2, 1001, 10),
857	(20003, 2, 1002, 12),
858	(20004, 2, 1003, 15),
859	(20005, 2, 1004, 20),
860	(20006, 2, 1005, 10),
861	(20007, 2, 1006, 12),
862	(20008, 2, 1007, 6),
863
864	-- ══════════════════════════════════════════════════════
865	-- PARLIAMENTARY 2011 (election_id=3)
866	-- ══════════════════════════════════════════════════════
867	(30001, 3, 1000, 35),
868	(30002, 3, 1001, 10),
869	(30003, 3, 1002, 12),
870	(30004, 3, 1003, 15),
871	(30005, 3, 1004, 20),
872	(30006, 3, 1005, 10),
873	(30007, 3, 1006, 12),
874	(30008, 3, 1007, 6),
875
876	-- ══════════════════════════════════════════════════════
877	-- PARLIAMENTARY 2014 (election_id=4)
878	-- ══════════════════════════════════════════════════════
879	(40001, 4, 1000, 35),
880	(40002, 4, 1001, 10),
881	(40003, 4, 1002, 12),
882	(40004, 4, 1003, 15),
883	(40005, 4, 1004, 20),
884	(40006, 4, 1005, 10),
885	(40007, 4, 1006, 12),
886	(40008, 4, 1007, 6),
887
888	-- ══════════════════════════════════════════════════════
889	-- PARLIAMENTARY 2016 (election_id=5)
890	-- ══════════════════════════════════════════════════════
891	(50001, 5, 1000, 35),
892	(50002, 5, 1001, 10),
893	(50003, 5, 1002, 12),
894	(50004, 5, 1003, 15),
895	(50005, 5, 1004, 20),
896	(50006, 5, 1005, 10),
897	(50007, 5, 1006, 12),
898	(50008, 5, 1007, 6),
899
900	-- ══════════════════════════════════════════════════════
901	-- PARLIAMENTARY 2019 (election_id=6)
902	-- ══════════════════════════════════════════════════════
903	(60001, 6, 1000, 35),
904	(60002, 6, 1001, 10),
905	(60003, 6, 1002, 12),
906	(60004, 6, 1003, 15),
907	(60005, 6, 1004, 20),
908	(60006, 6, 1005, 10),
909	(60007, 6, 1006, 12),
910	(60008, 6, 1007, 6),
911
912	-- ══════════════════════════════════════════════════════
913	-- PARLIAMENTARY 2020 (election_id=7)
914	-- ══════════════════════════════════════════════════════
915	(70001, 7, 1000, 35),
916	(70002, 7, 1001, 10),
917	(70003, 7, 1002, 12),
918	(70004, 7, 1003, 15),
919	(70005, 7, 1004, 20),
920	(70006, 7, 1005, 10),
921	(70007, 7, 1006, 12),
922	(70008, 7, 1007, 6),
923
924	-- ══════════════════════════════════════════════════════
925	-- PARLIAMENTARY 2024 (election_id=8)
926	-- ══════════════════════════════════════════════════════
927	(80001, 8, 1000, 35),
928	(80002, 8, 1001, 10),
929	(80003, 8, 1002, 12),
930	(80004, 8, 1003, 15),
931	(80005, 8, 1004, 20),
932	(80006, 8, 1005, 10),
933	(80007, 8, 1006, 12),
934	(80008, 8, 1007, 6),
935
936	-- ══════════════════════════════════════════════════════
937	-- PRESIDENTIAL — whole country, no seat count
938	-- ══════════════════════════════════════════════════════
939	(90001, 9, 141, NULL), -- Presidential 2009
940	(90002, 10, 141, NULL), -- Presidential 2014
941	(90003, 11, 141, NULL), -- Presidential 2019
942	(90004, 12, 141, NULL), -- Presidential 2024
943
944	-- ══════════════════════════════════════════════════════
945	-- LOCAL ELECTIONS — one district per planning region
946	-- ══════════════════════════════════════════════════════
947
948	-- Local 2013 (election_id=13)
949	(130001, 13, 1000, NULL),
950	(130002, 13, 1001, NULL),
951	(130003, 13, 1002, NULL),
952	(130004, 13, 1003, NULL),
953	(130005, 13, 1004, NULL),
954	(130006, 13, 1005, NULL),
955	(130007, 13, 1006, NULL),
956	(130008, 13, 1007, NULL),
957
958	-- Local 2017 (election_id=14)
959	(140001, 14, 1000, NULL),
960	(140002, 14, 1001, NULL),
961	(140003, 14, 1002, NULL),
962	(140004, 14, 1003, NULL),
963	(140005, 14, 1004, NULL),
964	(140006, 14, 1005, NULL),
965	(140007, 14, 1006, NULL),
966	(140008, 14, 1007, NULL),
967
968	-- Local 2021 (election_id=15)
969	(150001, 15, 1000, NULL),
970	(150002, 15, 1001, NULL),
971	(150003, 15, 1002, NULL),
972	(150004, 15, 1003, NULL),
973	(150005, 15, 1004, NULL),
974	(150006, 15, 1005, NULL),
975	(150007, 15, 1006, NULL),
976	(150008, 15, 1007, NULL),
977
978	-- ══════════════════════════════════════════════════════
979	-- REFERENDUMS — whole country, no seat count
980	-- ══════════════════════════════════════════════════════
981	(160001, 16, 141, NULL), -- Independence Referendum 1991
982	(170001, 17, 141, NULL) -- Prespa Agreement Referendum 2018
983
984	ON CONFLICT (election_id, region_id) DO NOTHING;
985
986
987	INSERT INTO coalition_member (coalition_member_id, coalition_id, party_id)
988	VALUES
989
990	-- ── 2006: "For Macedonia" — VMRO-DPMNE led ───────────
991	(100101, 1001, 1001), -- VMRO-DPMNE
992	(100102, 1001, 1009), -- VMRO-NP
993	(100103, 1001, 1012), -- Socialist Party of Macedonia
994
995	-- ── 2006: "For European Future" — SDSM led ───────────
996	(100201, 1002, 1002), -- SDSM
997	(100202, 1002, 1010), -- LDP-MK
998
999	-- ── 2006: "Democratic Renewal" — DUI led ─────────────
1000	(100301, 1003, 1003), -- DUI
1001	(100302, 1003, 1011), -- PDP
1002
1003	-- ── 2008: "For a Better Macedonia" — VMRO-DPMNE ──────
1004	(200101, 2001, 1001), -- VMRO-DPMNE
1005	(200102, 2001, 1009), -- VMRO-NP
1006	(200103, 2001, 1015), -- DOM
1007
1008	-- ── 2008: "Sun - Coalition for Europe" — SDSM ─────────
1009	(200201, 2002, 1002), -- SDSM
1010	(200202, 2002, 1010), -- LDP-MK
1011	(200203, 2002, 1013), -- Liberal Party of Macedonia
1012
1013	-- ── 2011: "European Front" — SDSM + DUI ──────────────
1014	(300201, 3002, 1002), -- SDSM
1015	(300202, 3002, 1003), -- DUI
1016
1017	-- ── 2013 Local: "Together for Macedonia" ─────────────
1018	(1300101, 13001, 1001), -- VMRO-DPMNE
1019	(1300102, 13001, 1009), -- VMRO-NP
1020
1021	-- ── 2013 Local: "For Our Cities" ─────────────────────
1022	(1300201, 13002, 1002), -- SDSM
1023	(1300202, 13002, 1010), -- LDP-MK
1024
1025	-- ── 2014: "Coalition for Better Macedonia" — SDSM ────
1026	(400201, 4002, 1002), -- SDSM
1027	(400202, 4002, 1005), -- BESA
1028	(400203, 4002, 1010), -- LDP-MK
1029
1030	-- ── 2016: "For Our Macedonia" — VMRO-DPMNE ───────────
1031	(500101, 5001, 1001), -- VMRO-DPMNE
1032	(500102, 5001, 1004), -- DPA
1033
1034	-- ── 2017 Local: "For European Macedonia" — SDSM ──────
1035	(1400201, 14002, 1002), -- SDSM
1036	(1400202, 14002, 1003), -- DUI
1037	(1400203, 14002, 1008), -- GROM
1038
1039	-- ── 2019: "Renewal of Macedonia" — VMRO-DPMNE ────────
1040	(600101, 6001, 1001), -- VMRO-DPMNE
1041	(600102, 6001, 1004), -- DPA
1042	(600103, 6001, 1009), -- VMRO-NP
1043
1044	-- ── 2020: "We Can" — SDSM + DUI ──────────────────────
1045	(700101, 7001, 1002), -- SDSM
1046	(700102, 7001, 1003), -- DUI
1047	(700103, 7001, 1008), -- GROM
1048
1049	-- ── 2021 Local: "Together We Can" ────────────────────
1050	(1500101, 15001, 1002), -- SDSM
1051	(1500102, 15001, 1003), -- DUI
1052	(1500103, 15001, 1006), -- Alliance for Albanians
1053
1054	-- ── 2024: "Your Macedonia" — VMRO-DPMNE ──────────────
1055	(800101, 8001, 1001), -- VMRO-DPMNE
1056	(800102, 8001, 1004), -- DPA
1057	(800103, 8001, 1005), -- BESA
1058	(800104, 8001, 1009), -- VMRO-NP
1059
1060	-- ── 2024: "Europe Now" — SDSM + DUI ──────────────────
1061	(800201, 8002, 1002), -- SDSM
1062	(800202, 8002, 1003) -- DUI
1063
1064	ON CONFLICT (coalition_id, party_id) DO NOTHING;
1065
1066
1067	INSERT INTO political_entity (
1068	entity_id, name, type,
1069	party_id, coalition_id, candidate_id,
1070	is_independent
1071	)
1072	VALUES
1073
1074	-- ══════════════════════════════════════════════════════
1075	-- NMK PARTY ENTITIES
1076	-- ══════════════════════════════════════════════════════
1077	(1001, 'VMRO-DPMNE', 'PARTY', 1001, NULL, NULL, FALSE),
1078	(1002, 'SDSM', 'PARTY', 1002, NULL, NULL, FALSE),
1079	(1003, 'DUI', 'PARTY', 1003, NULL, NULL, FALSE),
1080	(1004, 'DPA', 'PARTY', 1004, NULL, NULL, FALSE),
1081	(1005, 'BESA Movement', 'PARTY', 1005, NULL, NULL, FALSE),
1082	(1006, 'Alliance for Albanians', 'PARTY', 1006, NULL, NULL, FALSE),
1083	(1007, 'Levica', 'PARTY', 1007, NULL, NULL, FALSE),
1084	(1008, 'GROM', 'PARTY', 1008, NULL, NULL, FALSE),
1085	(1009, 'VMRO-NP', 'PARTY', 1009, NULL, NULL, FALSE),
1086	(1010, 'LDP Macedonia', 'PARTY', 1010, NULL, NULL, FALSE),
1087	(1011, 'PDP', 'PARTY', 1011, NULL, NULL, FALSE),
1088	(1012, 'Socialist Party of Macedonia', 'PARTY', 1012, NULL, NULL, FALSE),
1089	(1013, 'Liberal Party of Macedonia', 'PARTY', 1013, NULL, NULL, FALSE),
1090	(1014, 'NDR', 'PARTY', 1014, NULL, NULL, FALSE),
1091	(1015, 'DOM', 'PARTY', 1015, NULL, NULL, FALSE),
1092
1093	-- ══════════════════════════════════════════════════════
1094	-- SERBIA PARTY ENTITIES
1095	-- ══════════════════════════════════════════════════════
1096	(2001, 'SNS', 'PARTY', 2001, NULL, NULL, FALSE),
1097	(2002, 'SPS', 'PARTY', 2002, NULL, NULL, FALSE),
1098	(2003, 'DS Serbia', 'PARTY', 2003, NULL, NULL, FALSE),
1099	(2004, 'DSS', 'PARTY', 2004, NULL, NULL, FALSE),
1100	(2005, 'SRS', 'PARTY', 2005, NULL, NULL, FALSE),
1101	(2006, 'SPO', 'PARTY', 2006, NULL, NULL, FALSE),
1102	(2007, 'SSP', 'PARTY', 2007, NULL, NULL, FALSE),
1103	(2008, 'Dveri', 'PARTY', 2008, NULL, NULL, FALSE),
1104	(2009, 'Nova Srbija', 'PARTY', 2009, NULL, NULL, FALSE),
1105	(2010, 'LDP Serbia', 'PARTY', 2010, NULL, NULL, FALSE),
1106	(2011, 'SVM', 'PARTY', 2011, NULL, NULL, FALSE),
1107	(2012, 'SDPS', 'PARTY', 2012, NULL, NULL, FALSE),
1108	(2013, 'BDZ', 'PARTY', 2013, NULL, NULL, FALSE),
1109	(2014, 'G17 Plus', 'PARTY', 2014, NULL, NULL, FALSE),
1110	(2015, 'Zajedno', 'PARTY', 2015, NULL, NULL, FALSE),
1111	(2016, 'DOS', 'PARTY', 2016, NULL, NULL, FALSE),
1112	(2017, 'NDP Serbia', 'PARTY', 2017, NULL, NULL, FALSE),
1113	(2018, 'PUPS', 'PARTY', 2018, NULL, NULL, FALSE),
1114	(2019, 'Movement of Socialists', 'PARTY', 2019, NULL, NULL, FALSE),
1115	(2020, 'Narodna stranka', 'PARTY', 2020, NULL, NULL, FALSE),
1116
1117	-- ══════════════════════════════════════════════════════
1118	-- COALITION ENTITIES
1119	-- ══════════════════════════════════════════════════════
1120	(3001, 'For Macedonia (2006)', 'COALITION', NULL, 1001, NULL, FALSE),
1121	(3002, 'For European Future (2006)', 'COALITION', NULL, 1002, NULL, FALSE),
1122	(3003, 'Democratic Renewal (2006)', 'COALITION', NULL, 1003, NULL, FALSE),
1123	(3004, 'For a Better Macedonia (2008)', 'COALITION', NULL, 2001, NULL, FALSE),
1124	(3005, 'Sun - Coalition for Europe (2008)', 'COALITION', NULL, 2002, NULL, FALSE),
1125	(3006, 'European Front (2011)', 'COALITION', NULL, 3002, NULL, FALSE),
1126	(3007, 'Together for Macedonia (2013)', 'COALITION', NULL, 13001, NULL, FALSE),
1127	(3008, 'For Our Cities (2013)', 'COALITION', NULL, 13002, NULL, FALSE),
1128	(3009, 'Coalition for Better Macedonia (2014)', 'COALITION', NULL, 4002, NULL, FALSE),
1129	(3010, 'For Our Macedonia (2016)', 'COALITION', NULL, 5001, NULL, FALSE),
1130	(3011, 'For European Macedonia (2017)', 'COALITION', NULL, 14002, NULL, FALSE),
1131	(3012, 'Renewal of Macedonia (2019)', 'COALITION', NULL, 6001, NULL, FALSE),
1132	(3013, 'We Can (2020)', 'COALITION', NULL, 7001, NULL, FALSE),
1133	(3014, 'Together We Can (2021)', 'COALITION', NULL, 15001, NULL, FALSE),
1134	(3015, 'Your Macedonia (2024)', 'COALITION', NULL, 8001, NULL, FALSE),
1135	(3016, 'Europe Now (2024)', 'COALITION', NULL, 8002, NULL, FALSE),
1136
1137	-- ══════════════════════════════════════════════════════
1138	-- INDEPENDENT CANDIDATE ENTITIES
1139	-- A small set of independents to keep the data realistic.
1140	-- Candidate IDs 1–10 exist in your candidate table
1141	-- (generated from person table rows).
1142	-- ══════════════════════════════════════════════════════
1143	(9001, 'Independent Candidate 1', 'INDEPENDENT', NULL, NULL, 1, TRUE),
1144	(9002, 'Independent Candidate 2', 'INDEPENDENT', NULL, NULL, 2, TRUE),
1145	(9003, 'Independent Candidate 3', 'INDEPENDENT', NULL, NULL, 3, TRUE),
1146	(9004, 'Independent Candidate 4', 'INDEPENDENT', NULL, NULL, 4, TRUE),
1147	(9005, 'Independent Candidate 5', 'INDEPENDENT', NULL, NULL, 5, TRUE),
1148	(9006, 'Independent Candidate 6', 'INDEPENDENT', NULL, NULL, 6, TRUE),
1149	(9007, 'Independent Candidate 7', 'INDEPENDENT', NULL, NULL, 7, TRUE),
1150	(9008, 'Independent Candidate 8', 'INDEPENDENT', NULL, NULL, 8, TRUE),
1151	(9009, 'Independent Candidate 9', 'INDEPENDENT', NULL, NULL, 9, TRUE),
1152	(9010, 'Independent Candidate 10', 'INDEPENDENT', NULL, NULL, 10, TRUE)
1153
1154	ON CONFLICT (name) DO NOTHING;
1155
1156
1157	INSERT INTO political_party (
1158	party_id, name, abbreviation, description,
1159	founded_year, ideology, leader_id, parent_party_id
1160	)
1161	VALUES (
1162	2003,
1163	'Democratic Party Serbia',
1164	'DS',
1165	'Demokratska stranka. Centre-left social democratic party.',
1166	1990,
1167	'Social Democracy, Liberalism, Pro-EU',
1168	2003,
1169	NULL
1170	);
1171	UPDATE political_entity
1172	SET name = 'DS Serbia'
1173	WHERE entity_id = 2003;
1174
1175	INSERT INTO election_participant (participant_id, district_id, entity_id)
1176	VALUES
1177
1178	-- ══════════════════════════════════════════════════════
1179	-- PARLIAMENTARY 2006 — coalitions + standalone parties
1180	-- Competing: For Macedonia(3001), For European Future(3002),
1181	-- Democratic Renewal(3003), DPA(1004), PDP(1011)
1182	-- Same entities compete in all 8 districts
1183	-- ══════════════════════════════════════════════════════
1184	-- District Skopje (10001)
1185	(1000101, 10001, 3001),(1000102, 10001, 3002),(1000103, 10001, 3003),
1186	(1000104, 10001, 1004),(1000105, 10001, 1011),
1187	-- District Eastern (10002)
1188	(1000201, 10002, 3001),(1000202, 10002, 3002),(1000203, 10002, 3003),
1189	(1000204, 10002, 1004),(1000205, 10002, 1011),
1190	-- District Northeastern (10003)
1191	(1000301, 10003, 3001),(1000302, 10003, 3002),(1000303, 10003, 3003),
1192	(1000304, 10003, 1004),(1000305, 10003, 1011),
1193	-- District Pelagonia (10004)
1194	(1000401, 10004, 3001),(1000402, 10004, 3002),(1000403, 10004, 3003),
1195	(1000404, 10004, 1004),(1000405, 10004, 1011),
1196	-- District Polog (10005)
1197	(1000501, 10005, 3001),(1000502, 10005, 3002),(1000503, 10005, 3003),
1198	(1000504, 10005, 1004),(1000505, 10005, 1011),
1199	-- District Southeastern (10006)
1200	(1000601, 10006, 3001),(1000602, 10006, 3002),(1000603, 10006, 3003),
1201	(1000604, 10006, 1004),(1000605, 10006, 1011),
1202	-- District Southwestern (10007)
1203	(1000701, 10007, 3001),(1000702, 10007, 3002),(1000703, 10007, 3003),
1204	(1000704, 10007, 1004),(1000705, 10007, 1011),
1205	-- District Vardar (10008)
1206	(1000801, 10008, 3001),(1000802, 10008, 3002),(1000803, 10008, 3003),
1207	(1000804, 10008, 1004),(1000805, 10008, 1011),
1208
1209	-- ══════════════════════════════════════════════════════
1210	-- PARLIAMENTARY 2008
1211	-- Competing: For a Better Macedonia(3004), Sun Coalition(3005),
1212	-- DUI(1003), DPA(1004), BESA(1005)
1213	-- ══════════════════════════════════════════════════════
1214	(2000101, 20001, 3004),(2000102, 20001, 3005),(2000103, 20001, 1003),
1215	(2000104, 20001, 1004),(2000105, 20001, 1005),
1216	(2000201, 20002, 3004),(2000202, 20002, 3005),(2000203, 20002, 1003),
1217	(2000204, 20002, 1004),(2000205, 20002, 1005),
1218	(2000301, 20003, 3004),(2000302, 20003, 3005),(2000303, 20003, 1003),
1219	(2000304, 20003, 1004),(2000305, 20003, 1005),
1220	(2000401, 20004, 3004),(2000402, 20004, 3005),(2000403, 20004, 1003),
1221	(2000404, 20004, 1004),(2000405, 20004, 1005),
1222	(2000501, 20005, 3004),(2000502, 20005, 3005),(2000503, 20005, 1003),
1223	(2000504, 20005, 1004),(2000505, 20005, 1005),
1224	(2000601, 20006, 3004),(2000602, 20006, 3005),(2000603, 20006, 1003),
1225	(2000604, 20006, 1004),(2000605, 20006, 1005),
1226	(2000701, 20007, 3004),(2000702, 20007, 3005),(2000703, 20007, 1003),
1227	(2000704, 20007, 1004),(2000705, 20007, 1005),
1228	(2000801, 20008, 3004),(2000802, 20008, 3005),(2000803, 20008, 1003),
1229	(2000804, 20008, 1004),(2000805, 20008, 1005),
1230
1231	-- ══════════════════════════════════════════════════════
1232	-- PARLIAMENTARY 2011
1233	-- Competing: VMRO-DPMNE(1001), European Front(3006),
1234	-- DPA(1004), BESA(1005), Levica(1007)
1235	-- ══════════════════════════════════════════════════════
1236	(3000101, 30001, 1001),(3000102, 30001, 3006),(3000103, 30001, 1004),
1237	(3000104, 30001, 1005),(3000105, 30001, 1007),
1238	(3000201, 30002, 1001),(3000202, 30002, 3006),(3000203, 30002, 1004),
1239	(3000204, 30002, 1005),(3000205, 30002, 1007),
1240	(3000301, 30003, 1001),(3000302, 30003, 3006),(3000303, 30003, 1004),
1241	(3000304, 30003, 1005),(3000305, 30003, 1007),
1242	(3000401, 30004, 1001),(3000402, 30004, 3006),(3000403, 30004, 1004),
1243	(3000404, 30004, 1005),(3000405, 30004, 1007),
1244	(3000501, 30005, 1001),(3000502, 30005, 3006),(3000503, 30005, 1004),
1245	(3000504, 30005, 1005),(3000505, 30005, 1007),
1246	(3000601, 30006, 1001),(3000602, 30006, 3006),(3000603, 30006, 1004),
1247	(3000604, 30006, 1005),(3000605, 30006, 1007),
1248	(3000701, 30007, 1001),(3000702, 30007, 3006),(3000703, 30007, 1004),
1249	(3000704, 30007, 1005),(3000705, 30007, 1007),
1250	(3000801, 30008, 1001),(3000802, 30008, 3006),(3000803, 30008, 1004),
1251	(3000804, 30008, 1005),(3000805, 30008, 1007),
1252
1253	-- ══════════════════════════════════════════════════════
1254	-- PARLIAMENTARY 2014
1255	-- Competing: VMRO-DPMNE(1001), Better Macedonia(3009),
1256	-- DPA(1004), BESA(1005), Levica(1007), AA(1006)
1257	-- ══════════════════════════════════════════════════════
1258	(4000101, 40001, 1001),(4000102, 40001, 3009),(4000103, 40001, 1004),
1259	(4000104, 40001, 1005),(4000105, 40001, 1007),(4000106, 40001, 1006),
1260	(4000201, 40002, 1001),(4000202, 40002, 3009),(4000203, 40002, 1004),
1261	(4000204, 40002, 1005),(4000205, 40002, 1007),(4000206, 40002, 1006),
1262	(4000301, 40003, 1001),(4000302, 40003, 3009),(4000303, 40003, 1004),
1263	(4000304, 40003, 1005),(4000305, 40003, 1007),(4000306, 40003, 1006),
1264	(4000401, 40004, 1001),(4000402, 40004, 3009),(4000403, 40004, 1004),
1265	(4000404, 40004, 1005),(4000405, 40004, 1007),(4000406, 40004, 1006),
1266	(4000501, 40005, 1001),(4000502, 40005, 3009),(4000503, 40005, 1004),
1267	(4000504, 40005, 1005),(4000505, 40005, 1007),(4000506, 40005, 1006),
1268	(4000601, 40006, 1001),(4000602, 40006, 3009),(4000603, 40006, 1004),
1269	(4000604, 40006, 1005),(4000605, 40006, 1007),(4000606, 40006, 1006),
1270	(4000701, 40007, 1001),(4000702, 40007, 3009),(4000703, 40007, 1004),
1271	(4000704, 40007, 1005),(4000705, 40007, 1007),(4000706, 40007, 1006),
1272	(4000801, 40008, 1001),(4000802, 40008, 3009),(4000803, 40008, 1004),
1273	(4000804, 40008, 1005),(4000805, 40008, 1007),(4000806, 40008, 1006),
1274
1275	-- ══════════════════════════════════════════════════════
1276	-- PARLIAMENTARY 2016
1277	-- Competing: For Our Macedonia(3010), SDSM(1002),
1278	-- DUI(1003), BESA(1005), Levica(1007), AA(1006)
1279	-- ══════════════════════════════════════════════════════
1280	(5000101, 50001, 3010),(5000102, 50001, 1002),(5000103, 50001, 1003),
1281	(5000104, 50001, 1005),(5000105, 50001, 1007),(5000106, 50001, 1006),
1282	(5000201, 50002, 3010),(5000202, 50002, 1002),(5000203, 50002, 1003),
1283	(5000204, 50002, 1005),(5000205, 50002, 1007),(5000206, 50002, 1006),
1284	(5000301, 50003, 3010),(5000302, 50003, 1002),(5000303, 50003, 1003),
1285	(5000304, 50003, 1005),(5000305, 50003, 1007),(5000306, 50003, 1006),
1286	(5000401, 50004, 3010),(5000402, 50004, 1002),(5000403, 50004, 1003),
1287	(5000404, 50004, 1005),(5000405, 50004, 1007),(5000406, 50004, 1006),
1288	(5000501, 50005, 3010),(5000502, 50005, 1002),(5000503, 50005, 1003),
1289	(5000504, 50005, 1005),(5000505, 50005, 1007),(5000506, 50005, 1006),
1290	(5000601, 50006, 3010),(5000602, 50006, 1002),(5000603, 50006, 1003),
1291	(5000604, 50006, 1005),(5000605, 50006, 1007),(5000606, 50006, 1006),
1292	(5000701, 50007, 3010),(5000702, 50007, 1002),(5000703, 50007, 1003),
1293	(5000704, 50007, 1005),(5000705, 50007, 1007),(5000706, 50007, 1006),
1294	(5000801, 50008, 3010),(5000802, 50008, 1002),(5000803, 50008, 1003),
1295	(5000804, 50008, 1005),(5000805, 50008, 1007),(5000806, 50008, 1006),
1296
1297	-- ══════════════════════════════════════════════════════
1298	-- PARLIAMENTARY 2019
1299	-- Competing: Renewal of Macedonia(3012), SDSM(1002),
1300	-- DUI(1003), BESA(1005), Levica(1007), AA(1006)
1301	-- ══════════════════════════════════════════════════════
1302	(6000101, 60001, 3012),(6000102, 60001, 1002),(6000103, 60001, 1003),
1303	(6000104, 60001, 1005),(6000105, 60001, 1007),(6000106, 60001, 1006),
1304	(6000201, 60002, 3012),(6000202, 60002, 1002),(6000203, 60002, 1003),
1305	(6000204, 60002, 1005),(6000205, 60002, 1007),(6000206, 60002, 1006),
1306	(6000301, 60003, 3012),(6000302, 60003, 1002),(6000303, 60003, 1003),
1307	(6000304, 60003, 1005),(6000305, 60003, 1007),(6000306, 60003, 1006),
1308	(6000401, 60004, 3012),(6000402, 60004, 1002),(6000403, 60004, 1003),
1309	(6000404, 60004, 1005),(6000405, 60004, 1007),(6000406, 60004, 1006),
1310	(6000501, 60005, 3012),(6000502, 60005, 1002),(6000503, 60005, 1003),
1311	(6000504, 60005, 1005),(6000505, 60005, 1007),(6000506, 60005, 1006),
1312	(6000601, 60006, 3012),(6000602, 60006, 1002),(6000603, 60006, 1003),
1313	(6000604, 60006, 1005),(6000605, 60006, 1007),(6000606, 60006, 1006),
1314	(6000701, 60007, 3012),(6000702, 60007, 1002),(6000703, 60007, 1003),
1315	(6000704, 60007, 1005),(6000705, 60007, 1007),(6000706, 60007, 1006),
1316	(6000801, 60008, 3012),(6000802, 60008, 1002),(6000803, 60008, 1003),
1317	(6000804, 60008, 1005),(6000805, 60008, 1007),(6000806, 60008, 1006),
1318
1319	-- ══════════════════════════════════════════════════════
1320	-- PARLIAMENTARY 2020
1321	-- Competing: We Can(3013), Renewal of Macedonia(3012 reused as VMRO standalone),
1322	-- VMRO-DPMNE(1001), DPA(1004), BESA(1005), Levica(1007), AA(1006)
1323	-- ══════════════════════════════════════════════════════
1324	(7000101, 70001, 3013),(7000102, 70001, 1001),(7000103, 70001, 1004),
1325	(7000104, 70001, 1005),(7000105, 70001, 1007),(7000106, 70001, 1006),
1326	(7000201, 70002, 3013),(7000202, 70002, 1001),(7000203, 70002, 1004),
1327	(7000204, 70002, 1005),(7000205, 70002, 1007),(7000206, 70002, 1006),
1328	(7000301, 70003, 3013),(7000302, 70003, 1001),(7000303, 70003, 1004),
1329	(7000304, 70003, 1005),(7000305, 70003, 1007),(7000306, 70003, 1006),
1330	(7000401, 70004, 3013),(7000402, 70004, 1001),(7000403, 70004, 1004),
1331	(7000404, 70004, 1005),(7000405, 70004, 1007),(7000406, 70004, 1006),
1332	(7000501, 70005, 3013),(7000502, 70005, 1001),(7000503, 70005, 1004),
1333	(7000504, 70005, 1005),(7000505, 70005, 1007),(7000506, 70005, 1006),
1334	(7000601, 70006, 3013),(7000602, 70006, 1001),(7000603, 70006, 1004),
1335	(7000604, 70006, 1005),(7000605, 70006, 1007),(7000606, 70006, 1006),
1336	(7000701, 70007, 3013),(7000702, 70007, 1001),(7000703, 70007, 1004),
1337	(7000704, 70007, 1005),(7000705, 70007, 1007),(7000706, 70007, 1006),
1338	(7000801, 70008, 3013),(7000802, 70008, 1001),(7000803, 70008, 1004),
1339	(7000804, 70008, 1005),(7000805, 70008, 1007),(7000806, 70008, 1006),
1340
1341	-- ══════════════════════════════════════════════════════
1342	-- PARLIAMENTARY 2024
1343	-- Competing: Your Macedonia(3015), Europe Now(3016),
1344	-- BESA(1005), Levica(1007), AA(1006)
1345	-- ══════════════════════════════════════════════════════
1346	(8000101, 80001, 3015),(8000102, 80001, 3016),(8000103, 80001, 1005),
1347	(8000104, 80001, 1007),(8000105, 80001, 1006),
1348	(8000201, 80002, 3015),(8000202, 80002, 3016),(8000203, 80002, 1005),
1349	(8000204, 80002, 1007),(8000205, 80002, 1006),
1350	(8000301, 80003, 3015),(8000302, 80003, 3016),(8000303, 80003, 1005),
1351	(8000304, 80003, 1007),(8000305, 80003, 1006),
1352	(8000401, 80004, 3015),(8000402, 80004, 3016),(8000403, 80004, 1005),
1353	(8000404, 80004, 1007),(8000405, 80004, 1006),
1354	(8000501, 80005, 3015),(8000502, 80005, 3016),(8000503, 80005, 1005),
1355	(8000504, 80005, 1007),(8000505, 80005, 1006),
1356	(8000601, 80006, 3015),(8000602, 80006, 3016),(8000603, 80006, 1005),
1357	(8000604, 80006, 1007),(8000605, 80006, 1006),
1358	(8000701, 80007, 3015),(8000702, 80007, 3016),(8000703, 80007, 1005),
1359	(8000704, 80007, 1007),(8000705, 80007, 1006),
1360	(8000801, 80008, 3015),(8000802, 80008, 3016),(8000803, 80008, 1005),
1361	(8000804, 80008, 1007),(8000805, 80008, 1006),
1362
1363	-- ══════════════════════════════════════════════════════
1364	-- PRESIDENTIAL — whole country district, party entities
1365	-- Each party nominates a presidential candidate
1366	-- ══════════════════════════════════════════════════════
1367	-- Presidential 2009 (90001)
1368	(9000101, 90001, 1001),(9000102, 90001, 1002),(9000103, 90001, 1003),
1369	-- Presidential 2014 (90002)
1370	(9000201, 90002, 1001),(9000202, 90002, 1002),(9000203, 90002, 1003),
1371	-- Presidential 2019 (90003)
1372	(9000301, 90003, 1001),(9000302, 90003, 1002),(9000303, 90003, 1007),
1373	-- Presidential 2024 (90004)
1374	(9000401, 90004, 1001),(9000402, 90004, 1002),(9000403, 90004, 1007),
1375
1376	-- ══════════════════════════════════════════════════════
1377	-- LOCAL 2013 — coalitions + standalone parties per region
1378	-- ══════════════════════════════════════════════════════
1379	(1300101,130001,3007),(1300102,130001,3008),(1300103,130001,1003),(1300104,130001,1004),
1380	(1300201,130002,3007),(1300202,130002,3008),(1300203,130002,1003),(1300204,130002,1004),
1381	(1300301,130003,3007),(1300302,130003,3008),(1300303,130003,1003),(1300304,130003,1004),
1382	(1300401,130004,3007),(1300402,130004,3008),(1300403,130004,1003),(1300404,130004,1004),
1383	(1300501,130005,3007),(1300502,130005,3008),(1300503,130005,1003),(1300504,130005,1004),
1384	(1300601,130006,3007),(1300602,130006,3008),(1300603,130006,1003),(1300604,130006,1004),
1385	(1300701,130007,3007),(1300702,130007,3008),(1300703,130007,1003),(1300704,130007,1004),
1386	(1300801,130008,3007),(1300802,130008,3008),(1300803,130008,1003),(1300804,130008,1004),
1387
1388	-- ══════════════════════════════════════════════════════
1389	-- LOCAL 2017
1390	-- ══════════════════════════════════════════════════════
1391	(1400101,140001,1001),(1400102,140001,3011),(1400103,140001,1003),(1400104,140001,1005),
1392	(1400201,140002,1001),(1400202,140002,3011),(1400203,140002,1003),(1400204,140002,1005),
1393	(1400301,140003,1001),(1400302,140003,3011),(1400303,140003,1003),(1400304,140003,1005),
1394	(1400401,140004,1001),(1400402,140004,3011),(1400403,140004,1003),(1400404,140004,1005),
1395	(1400501,140005,1001),(1400502,140005,3011),(1400503,140005,1003),(1400504,140005,1005),
1396	(1400601,140006,1001),(1400602,140006,3011),(1400603,140006,1003),(1400604,140006,1005),
1397	(1400701,140007,1001),(1400702,140007,3011),(1400703,140007,1003),(1400704,140007,1005),
1398	(1400801,140008,1001),(1400802,140008,3011),(1400803,140008,1003),(1400804,140008,1005),
1399
1400	-- ══════════════════════════════════════════════════════
1401	-- LOCAL 2021
1402	-- ══════════════════════════════════════════════════════
1403	(1500101,150001,3014),(1500102,150001,1001),(1500103,150001,1004),(1500104,150001,1007),
1404	(1500201,150002,3014),(1500202,150002,1001),(1500203,150002,1004),(1500204,150002,1007),
1405	(1500301,150003,3014),(1500302,150003,1001),(1500303,150003,1004),(1500304,150003,1007),
1406	(1500401,150004,3014),(1500402,150004,1001),(1500403,150004,1004),(1500404,150004,1007),
1407	(1500501,150005,3014),(1500502,150005,1001),(1500503,150005,1004),(1500504,150005,1007),
1408	(1500601,150006,3014),(1500602,150006,1001),(1500603,150006,1004),(1500604,150006,1007),
1409	(1500701,150007,3014),(1500702,150007,1001),(1500703,150007,1004),(1500704,150007,1007),
1410	(1500801,150008,3014),(1500802,150008,1001),(1500803,150008,1004),(1500804,150008,1007)
1411
1412	ON CONFLICT (district_id, entity_id) DO NOTHING;
1413
1414
1415	INSERT INTO candidate_list (list_id, election_id, entity_id)
1416	VALUES
1417
1418	-- Parliamentary 2006 (election_id=1)
1419	(10001, 1, 3001),(10002, 1, 3002),(10003, 1, 3003),
1420	(10004, 1, 1004),(10005, 1, 1011),
1421
1422	-- Parliamentary 2008 (election_id=2)
1423	(20001, 2, 3004),(20002, 2, 3005),(20003, 2, 1003),
1424	(20004, 2, 1004),(20005, 2, 1005),
1425
1426	-- Parliamentary 2011 (election_id=3)
1427	(30001, 3, 1001),(30002, 3, 3006),(30003, 3, 1004),
1428	(30004, 3, 1005),(30005, 3, 1007),
1429
1430	-- Parliamentary 2014 (election_id=4)
1431	(40001, 4, 1001),(40002, 4, 3009),(40003, 4, 1004),
1432	(40004, 4, 1005),(40005, 4, 1007),(40006, 4, 1006),
1433
1434	-- Parliamentary 2016 (election_id=5)
1435	(50001, 5, 3010),(50002, 5, 1002),(50003, 5, 1003),
1436	(50004, 5, 1005),(50005, 5, 1007),(50006, 5, 1006),
1437
1438	-- Parliamentary 2019 (election_id=6)
1439	(60001, 6, 3012),(60002, 6, 1002),(60003, 6, 1003),
1440	(60004, 6, 1005),(60005, 6, 1007),(60006, 6, 1006),
1441
1442	-- Parliamentary 2020 (election_id=7)
1443	(70001, 7, 3013),(70002, 7, 1001),(70003, 7, 1004),
1444	(70004, 7, 1005),(70005, 7, 1007),(70006, 7, 1006),
1445
1446	-- Parliamentary 2024 (election_id=8)
1447	(80001, 8, 3015),(80002, 8, 3016),(80003, 8, 1005),
1448	(80004, 8, 1007),(80005, 8, 1006),
1449
1450	-- Presidential 2009 (election_id=9)
1451	(90001, 9, 1001),(90002, 9, 1002),(90003, 9, 1003),
1452
1453	-- Presidential 2014 (election_id=10)
1454	(100001, 10, 1001),(100002, 10, 1002),(100003, 10, 1003),
1455
1456	-- Presidential 2019 (election_id=11)
1457	(110001, 11, 1001),(110002, 11, 1002),(110003, 11, 1007),
1458
1459	-- Presidential 2024 (election_id=12)
1460	(120001, 12, 1001),(120002, 12, 1002),(120003, 12, 1007),
1461
1462	-- Local 2013 (election_id=13)
1463	(130001, 13, 3007),(130002, 13, 3008),(130003, 13, 1003),(130004, 13, 1004),
1464
1465	-- Local 2017 (election_id=14)
1466	(140001, 14, 1001),(140002, 14, 3011),(140003, 14, 1003),(140004, 14, 1005),
1467
1468	-- Local 2021 (election_id=15)
1469	(150001, 15, 3014),(150002, 15, 1001),(150003, 15, 1004),(150004, 15, 1007)
1470
1471	ON CONFLICT (election_id, entity_id) DO NOTHING;
1472
1473
1474	INSERT INTO candidate_party (candidate_party_id, candidate_id, party_id, election_id)
1475
1476	-- ── Parliamentary elections 1–8, parties 1001–1011 ───
1477	SELECT
1478	e.election_id * 10000000
1479	+ p.party_id * 10000
1480	+ ROW_NUMBER() OVER (PARTITION BY e.election_id, p.party_id ORDER BY c.candidate_id)
1481	AS candidate_party_id,
1482	c.candidate_id,
1483	p.party_id,
1484	e.election_id
1485	FROM
1486	(SELECT election_id FROM election WHERE election_id BETWEEN 1 AND 8) e
1487	CROSS JOIN
1488	(SELECT party_id FROM political_party
1489	WHERE party_id IN (1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011)) p
1490	JOIN
1491	candidate c ON c.candidate_id BETWEEN
1492	(p.party_id - 1001) * 1000 + 1
1493	AND
1494	(p.party_id - 1001) * 1000 + 120
1495
1496	UNION ALL
1497
1498	-- ── Presidential elections 9–12, 5 candidates per party
1499	SELECT
1500	e.election_id * 10000000
1501	+ p.party_id * 10000
1502	+ ROW_NUMBER() OVER (PARTITION BY e.election_id, p.party_id ORDER BY c.candidate_id)
1503	AS candidate_party_id,
1504	c.candidate_id,
1505	p.party_id,
1506	e.election_id
1507	FROM
1508	(SELECT election_id FROM election WHERE election_id BETWEEN 9 AND 12) e
1509	CROSS JOIN
1510	(SELECT party_id FROM political_party
1511	WHERE party_id IN (1001,1002,1003,1007)) p
1512	JOIN
1513	candidate c ON c.candidate_id BETWEEN
1514	(p.party_id - 1001) * 1000 + 121
1515	AND
1516	(p.party_id - 1001) * 1000 + 125
1517
1518	UNION ALL
1519
1520	-- ── Local elections 13–15, 80 candidates per party ───
1521	SELECT
1522	e.election_id * 10000000
1523	+ p.party_id * 10000
1524	+ ROW_NUMBER() OVER (PARTITION BY e.election_id, p.party_id ORDER BY c.candidate_id)
1525	AS candidate_party_id,
1526	c.candidate_id,
1527	p.party_id,
1528	e.election_id
1529	FROM
1530	(SELECT election_id FROM election WHERE election_id BETWEEN 13 AND 15) e
1531	CROSS JOIN
1532	(SELECT party_id FROM political_party
1533	WHERE party_id IN (1001,1002,1003,1004,1005,1006,1007)) p
1534	JOIN
1535	candidate c ON c.candidate_id BETWEEN
1536	(p.party_id - 1001) * 1000 + 126
1537	AND
1538	(p.party_id - 1001) * 1000 + 205
1539
1540	ON CONFLICT (candidate_id, party_id, election_id) DO NOTHING;
1541
1542	DO $$
1543	DECLARE
1544	v_lists INT;
1545	v_candidates INT;
1546	BEGIN
1547	SELECT COUNT(*) INTO v_lists FROM candidate_list;
1548	SELECT COUNT(*) INTO v_candidates FROM candidate;
1549	RAISE NOTICE 'Lists available : %', v_lists;
1550	RAISE NOTICE 'Candidates available: %', v_candidates;
1551	RAISE NOTICE 'Expected output rows: %', v_candidates * 2;
1552	IF v_lists < 2 THEN
1553	RAISE EXCEPTION 'Need at least 2 lists — found %', v_lists;
1554	END IF;
1555	IF v_candidates < 1 THEN
1556	RAISE EXCEPTION 'No candidates found';
1557	END IF;
1558	END $$;
1559
1560	CREATE TEMP TABLE _list_index AS
1561	SELECT
1562	list_id,
1563	ROW_NUMBER() OVER (ORDER BY list_id) - 1 AS idx,
1564	COUNT(*) OVER () AS total_lists
1565	FROM candidate_list;
1566
1567
1568	INSERT INTO candidate_list_item (list_item_id, list_id, candidate_id, position)
1569
1570	WITH
1571
1572	-- ── All candidates with their two list assignments ────
1573	assignments AS (
1574
1575	-- Pass A — first list for each candidate
1576	SELECT
1577	c.candidate_id,
1578	li.list_id,
1579	'A' AS pass
1580	FROM candidate c
1581	JOIN _list_index li
1582	ON li.idx = c.candidate_id % li.total_lists
1583
1584	UNION ALL
1585
1586	-- Pass B — second list for each candidate (offset by half)
1587	SELECT
1588	c.candidate_id,
1589	li.list_id,
1590	'B' AS pass
1591	FROM candidate c
1592	JOIN _list_index li
1593	ON li.idx = (c.candidate_id + (SELECT MAX(total_lists)/2 FROM _list_index))
1594	% li.total_lists
1595	),
1596
1597	-- ── Remove any accidental same-list duplicates ────────
1598	-- (can happen if total_lists is odd and offset == 0)
1599	deduped AS (
1600	SELECT candidate_id, list_id
1601	FROM assignments
1602	GROUP BY candidate_id, list_id -- one row per (candidate, list) pair
1603	),
1604
1605	-- ── Assign positions within each list ─────────────────
1606	positioned AS (
1607	SELECT
1608	candidate_id,
1609	list_id,
1610	ROW_NUMBER() OVER (
1611	PARTITION BY list_id
1612	ORDER BY candidate_id
1613	) AS position
1614	FROM deduped
1615	),
1616
1617	-- ── Generate list_item_id ─────────────────────────────
1618	numbered AS (
1619	SELECT
1620	ROW_NUMBER() OVER (ORDER BY list_id, position) AS list_item_id,
1621	list_id,
1622	candidate_id,
1623	position
1624	FROM positioned
1625	)
1626
1627	SELECT list_item_id, list_id, candidate_id, position
1628	FROM numbered
1629
1630	ON CONFLICT (list_id, candidate_id) DO NOTHING;
1631
1632
1633	-- ── Cleanup ───────────────────────────────────────────
1634	DROP TABLE IF EXISTS _list_index;
1635
1636
1637	INSERT INTO candidate (candidate_id, person_id)
1638	SELECT
1639	1500 + ROW_NUMBER() OVER (ORDER BY person_id) AS candidate_id,
1640	person_id
1641	FROM (
1642	SELECT person_id
1643	FROM person
1644	WHERE date_of_birth <= CURRENT_DATE - INTERVAL '18 years'
1645	AND person_id NOT IN (SELECT person_id FROM candidate)
1646	ORDER BY RANDOM()
1647	LIMIT 498500 -- 500000 - 1500 already in there
1648	) p;
1649
1650
1651
1652	INSERT INTO voter_election (
1653	voter_election_id,
1654	voter_id,
1655	station_id,
1656	election_id,
1657	checkin_timestamp
1658	)
1659	WITH
1660
1661	-- Rank ballots within each station+election by timestamp
1662	-- ballot #1 = first ballot cast at that station that day
1663	ranked_ballots AS (
1664	SELECT
1665	station_id,
1666	election_id,
1667	ballot_timestamp,
1668	ROW_NUMBER() OVER (
1669	PARTITION BY station_id, election_id
1670	ORDER BY ballot_timestamp
1671	) AS rn
1672	FROM ballot
1673	),
1674
1675	-- Rank voters within each station by voter_id
1676	-- voter #1 = lowest voter_id at that station
1677	ranked_voters AS (
1678	SELECT
1679	voter_id,
1680	station_id,
1681	ROW_NUMBER() OVER (
1682	PARTITION BY station_id
1683	ORDER BY voter_id
1684	) AS rn
1685	FROM voter
1686	)
1687
1688	SELECT
1689	ROW_NUMBER() OVER (
1690	ORDER BY b.station_id, b.election_id, b.rn
1691	) AS voter_election_id,
1692	v.voter_id,
1693	b.station_id,
1694	b.election_id,
1695	b.ballot_timestamp AS checkin_timestamp
1696	FROM ranked_ballots b
1697	JOIN ranked_voters v
1698	ON v.station_id = b.station_id
1699	AND v.rn = b.rn
1700	ON CONFLICT (voter_id, election_id) DO NOTHING;
1701
1702
1703
1704
1705	INSERT INTO voter (voter_id, person_id, station_id, region_id)
1706	WITH PosledenID AS (
1707	SELECT COALESCE(MAX(voter_id), 0) as max_id FROM voter
1708	),
1709	PreostanatiLugje AS (
1710	SELECT p.person_id,
1711	row_number() OVER (ORDER BY md5(p.person_id::text)) as p_rn
1712	FROM person p
1713	LEFT JOIN voter v ON p.person_id = v.person_id
1714	WHERE v.person_id IS NULL
1715	AND p.person_id <= 2008051999999
1716	LIMIT 752695
1717	),
1718	SlobodniMesta AS (
1719
1720	SELECT station_id,
1721	row_number() OVER (ORDER BY station_id ASC) as s_rn,
1722	count(*) OVER () as total_free
1723	FROM polling_station
1724	WHERE station_id BETWEEN 5001 AND 8743
1725	AND NOT EXISTS (SELECT 1 FROM voter v WHERE v.station_id = polling_station.station_id)
1726	)
1727	SELECT
1728	pid.max_id + nl.p_rn,
1729	nl.person_id,
1730	sm.station_id,
1731	NULL
1732	FROM PreostanatiLugje nl
1733	CROSS JOIN PosledenID pid
1734
1735	JOIN SlobodniMesta sm ON sm.s_rn = (MOD(nl.p_rn - 1, (SELECT total_free FROM SlobodniMesta)) + 1)
1736	WHERE sm.station_id IS NOT NULL;
1737
1738
1739
1740	INSERT INTO voter (voter_id, person_id, station_id, region_id)
1741	WITH PosledenID AS (
1742	SELECT COALESCE(MAX(voter_id), 0) as max_id FROM voter
1743	),
1744	NoviLugjeMKD AS (
1745	SELECT p.person_id,
1746	row_number() OVER (ORDER BY md5(p.person_id::text)) as p_rn
1747	FROM person p
1748	LEFT JOIN voter v ON p.person_id = v.person_id
1749	WHERE v.person_id IS NULL
1750	AND p.person_id <= 2008051999999
1751	LIMIT 500000
1752	),
1753	MestaMKD AS (
1754	SELECT station_id,
1755	row_number() OVER (ORDER BY station_id ASC) as s_rn
1756	FROM polling_station
1757	WHERE station_id BETWEEN 5001 AND 8743
1758	)
1759	SELECT
1760	pid.max_id + nl.p_rn,
1761	nl.person_id,
1762	ms.station_id,
1763	NULL
1764	FROM NoviLugjeMKD nl
1765	CROSS JOIN PosledenID pid
1766
1767	JOIN MestaMKD ms ON ms.s_rn = ((nl.p_rn - 1) / 500) + 1
1768	WHERE ms.station_id IS NOT NULL;
1769
1770
1771	INSERT INTO voter (voter_id, person_id, station_id, region_id)
1772	WITH PosledenID AS (
1773	SELECT COALESCE(MAX(voter_id), 1300000) as max_id FROM voter
1774	),
1775	NoviLugjeSrbija AS (
1776	SELECT p.person_id,
1777	row_number() OVER (ORDER BY md5(p.person_id::text)) as p_rn
1778	FROM person p
1779	LEFT JOIN voter v ON p.person_id = v.person_id
1780	WHERE v.person_id IS NULL
1781	AND p.person_id <= 2008042999999 -- Проверка за полнолетство (родени пред 19.05.2008)
1782	LIMIT 3000000
1783	),
1784	MestaSrbija AS (
1785	SELECT station_id,
1786	row_number() OVER (ORDER BY station_id ASC) as s_rn,
1787	count(*) OVER () as total_stations
1788	FROM polling_station
1789	WHERE station_id BETWEEN 10000 AND 18000
1790	)
1791	SELECT
1792	pid.max_id + nl.p_rn,
1793	nl.person_id,
1794	ms.station_id,
1795	NULL
1796	FROM NoviLugjeSrbija nl
1797	CROSS JOIN PosledenID pid
1798	JOIN MestaSrbija ms ON ms.s_rn = (MOD(nl.p_rn, (SELECT total_stations FROM MestaSrbija)) + 1)
1799	WHERE ms.station_id IS NOT NULL;
1800
1801
1802	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);
1803	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);
1804	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);
1805	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);
1806	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);
1807	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);
1808	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);
1809	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);
1810	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);
1811	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);
1812	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);
1813	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);
1814	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);
1815	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);
1816	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);
1817	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);
1818	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);
1819	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);
1820	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);
1821	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);
1822	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);
1823	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);
1824	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);
1825	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);
1826	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);
1827	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);
1828	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);
1829	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);
1830
1831
1832
1833	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180001, 18, 187, NULL);
1834	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180002, 19, 187, NULL);
1835	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180003, 20, 187, NULL);
1836	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180004, 21, 187, NULL);
1837	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180005, 22, 187, NULL);
1838	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180006, 23, 187, NULL);
1839	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180007, 24, 187, NULL);
1840	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180008, 25, 187, NULL);
1841	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180009, 26, 187, NULL);
1842	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180010, 27, 187, NULL);
1843	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180011, 28, 187, NULL);
1844	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180012, 29, 187, NULL);
1845	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180013, 30, 187, NULL);
1846	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180014, 31, 187, NULL);
1847	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180015, 32, 187, NULL);
1848	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180016, 33, 187, NULL);
1849	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180017, 34, 187, NULL);
1850	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180018, 35, 187, NULL);
1851	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180019, 36, 187, NULL);
1852	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180020, 37, 187, NULL);
1853	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180021, 38, 187, NULL);
1854	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180022, 39, 187, NULL);
1855	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180023, 40, 187, NULL);
1856	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180024, 44, 187, NULL);
1857	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180025, 45, 187, NULL);
1858	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180026, 41, 2001, NULL);
1859	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180027, 41, 2002, NULL);
1860	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180028, 41, 2003, NULL);
1861	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180029, 41, 2004, NULL);
1862	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180030, 41, 2005, NULL);
1863	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180031, 41, 2006, NULL);
1864	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180032, 41, 2007, NULL);
1865	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180033, 41, 2008, NULL);
1866	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180034, 41, 2010, NULL);
1867	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180035, 41, 2011, NULL);
1868	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180036, 41, 2012, NULL);
1869	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180037, 41, 2013, NULL);
1870	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180038, 41, 2014, NULL);
1871	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180039, 41, 2015, NULL);
1872	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180040, 41, 2016, NULL);
1873	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180041, 41, 2017, NULL);
1874	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180042, 41, 2018, NULL);
1875	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180043, 41, 2019, NULL);
1876	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180044, 41, 2020, NULL);
1877	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180045, 41, 2021, NULL);
1878	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180046, 41, 2022, NULL);
1879	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180047, 41, 2023, NULL);
1880	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180048, 41, 2024, NULL);
1881	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180049, 41, 2025, NULL);
1882	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180050, 41, 2026, NULL);
1883	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180051, 41, 2027, NULL);
1884	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180052, 41, 2028, NULL);
1885	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180053, 41, 2029, NULL);
1886	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180054, 41, 2030, NULL);
1887	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180055, 41, 2031, NULL);
1888	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180056, 42, 2001, NULL);
1889	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180057, 42, 2002, NULL);
1890	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180058, 42, 2003, NULL);
1891	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180059, 42, 2004, NULL);
1892	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180060, 42, 2005, NULL);
1893	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180061, 42, 2006, NULL);
1894	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180062, 42, 2007, NULL);
1895	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180063, 42, 2008, NULL);
1896	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180064, 42, 2010, NULL);
1897	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180065, 42, 2011, NULL);
1898	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180066, 42, 2012, NULL);
1899	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180067, 42, 2013, NULL);
1900	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180068, 42, 2014, NULL);
1901	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180069, 42, 2015, NULL);
1902	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180070, 42, 2016, NULL);
1903	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180071, 42, 2017, NULL);
1904	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180072, 42, 2018, NULL);
1905	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180073, 42, 2019, NULL);
1906	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180074, 42, 2020, NULL);
1907	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180075, 42, 2021, NULL);
1908	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180076, 42, 2022, NULL);
1909	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180077, 42, 2023, NULL);
1910	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180078, 42, 2024, NULL);
1911	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180079, 42, 2025, NULL);
1912	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180080, 42, 2026, NULL);
1913	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180081, 42, 2027, NULL);
1914	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180082, 42, 2028, NULL);
1915	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180083, 42, 2029, NULL);
1916	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180084, 42, 2030, NULL);
1917	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180085, 42, 2031, NULL);
1918	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180086, 43, 2001, NULL);
1919	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180087, 43, 2002, NULL);
1920	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180088, 43, 2003, NULL);
1921	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180089, 43, 2004, NULL);
1922	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180090, 43, 2005, NULL);
1923	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180091, 43, 2006, NULL);
1924	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180092, 43, 2007, NULL);
1925	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180093, 43, 2008, NULL);
1926	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180094, 43, 2010, NULL);
1927	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180095, 43, 2011, NULL);
1928	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180096, 43, 2012, NULL);
1929	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180097, 43, 2013, NULL);
1930	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180098, 43, 2014, NULL);
1931	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180099, 43, 2015, NULL);
1932	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180100, 43, 2016, NULL);
1933	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180101, 43, 2017, NULL);
1934	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180102, 43, 2018, NULL);
1935	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180103, 43, 2019, NULL);
1936	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180104, 43, 2020, NULL);
1937	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180105, 43, 2021, NULL);
1938	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180106, 43, 2022, NULL);
1939	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180107, 43, 2023, NULL);
1940	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180108, 43, 2024, NULL);
1941	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180109, 43, 2025, NULL);
1942	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180110, 43, 2026, NULL);
1943	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180111, 43, 2027, NULL);
1944	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180112, 43, 2028, NULL);
1945	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180113, 43, 2029, NULL);
1946	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180114, 43, 2030, NULL);
1947	INSERT INTO electoral_district (district_id, election_id, region_id, seats_available) VALUES (180115, 43, 2031, NULL);
1948
1949
1950
1951	INSERT INTO station_election (station_election_id, station_id, election_id)
1952	SELECT
1953	ROW_NUMBER() OVER (ORDER BY ps.station_id, e.election_id),
1954	ps.station_id,
1955	e.election_id
1956	FROM polling_station ps
1957	JOIN region r1 ON r1.region_id = ps.municipality_id
1958	JOIN region r2 ON r2.region_id = r1.parent_region_id
1959	JOIN electoral_district ed ON (
1960	ed.region_id = r2.region_id -- lokalni: match po okrug/region
1961	OR ed.region_id = r2.parent_region_id -- nacionalni: match po zemja
1962	)
1963	JOIN election e ON e.election_id = ed.election_id
1964	WHERE NOT EXISTS (
1965	SELECT 1 FROM station_election se
1966	WHERE se.station_id = ps.station_id
1967	AND se.election_id = e.election_id
1968	)
1969	ORDER BY ps.station_id, e.election_id;
1970
1971
1972
1973	INSERT INTO vote_result (result_id, election_id, station_id, entity_id, candidate_id, votes)
1974	WITH entity_weights AS (
1975	SELECT entity_id, weight_max, weight_min FROM (VALUES
1976	(1001, 400, 100), (1002, 350, 80), (1003, 200, 50),
1977	(1004, 80, 5), (1005, 80, 5), (1006, 80, 5),
1978	(1007, 80, 5), (1008, 50, 5), (1009, 50, 5),
1979	(1010, 50, 5), (1011, 80, 5), (1012, 50, 5),
1980	(1013, 50, 5), (1014, 50, 5), (1015, 50, 5),
1981	(2001, 500, 200), (2002, 200, 50), (2003, 150, 30),
1982	(2004, 100, 20), (2005, 100, 20), (2006, 80, 10),
1983	(2007, 80, 10), (2008, 80, 10), (2009, 80, 10),
1984	(2010, 60, 5), (2011, 80, 10), (2012, 60, 5),
1985	(2013, 60, 5), (2014, 80, 10), (2015, 80, 10),
1986	(2016, 100, 20), (2017, 60, 5), (2018, 80, 10),
1987	(2019, 100, 20), (2020, 80, 10)
1988	) AS w(entity_id, weight_max, weight_min)
1989	)
1990	SELECT
1991	ROW_NUMBER() OVER (ORDER BY ed.election_id, se.station_id, ep.entity_id),
1992	ed.election_id,
1993	se.station_id,
1994	ep.entity_id,
1995	pe.candidate_id,
1996	CAST(FLOOR(x.r * ew.weight_max + ew.weight_min) AS int)
1997	FROM election_participant ep
1998	JOIN political_entity pe ON pe.entity_id = ep.entity_id
1999	JOIN electoral_district ed ON ed.district_id = ep.district_id
2000	JOIN station_election se ON se.election_id = ed.election_id
2001	JOIN polling_station ps ON ps.station_id = se.station_id
2002	JOIN region r1 ON r1.region_id = ps.municipality_id
2003	JOIN region r2 ON r2.region_id = r1.parent_region_id
2004	JOIN entity_weights ew ON ew.entity_id = ep.entity_id
2005	CROSS JOIN LATERAL (SELECT random() AS r) x
2006	WHERE (ed.region_id = r2.region_id OR ed.region_id = r2.parent_region_id)
2007	AND NOT EXISTS (
2008	SELECT 1 FROM vote_result vr
2009	WHERE vr.election_id = ed.election_id
2010	AND vr.station_id = se.station_id
2011	AND vr.entity_id = ep.entity_id
2012	);
2013
2014
2015
2016	INSERT INTO candidate (candidate_id, person_id) VALUES
2017	(900001, 1946112999001), -- Vuk Draskovic
2018	(900002, 1952020199001), -- Slobodan Milosevic
2019	(900003, 1944032499001), -- Vojislav Kostunica
2020	(900004, 1960010199001), -- Tomislav Nikolic
2021	(900005, 1974010199001), -- Boris Tadic
2022	(900006, 1970030599001); -- Aleksandar Vucic
2023
2024
2025
2026	-- 1991 Independence Referendum: VMRO (1001), SDSM (1002) uchestvuvale
2027	INSERT INTO election_participant (participant_id, district_id, entity_id)
2028	VALUES
2029	((SELECT MAX(participant_id) FROM election_participant) + 1, 160001, 1001),
2030	((SELECT MAX(participant_id) FROM election_participant) + 2, 160001, 1002);
2031
2032	-- 2018 Prespa Referendum: SDSM (1002), VMRO (1001), DUI (1003)
2033	INSERT INTO election_participant (participant_id, district_id, entity_id)
2034	VALUES
2035	((SELECT MAX(participant_id) FROM election_participant) + 3, 170001, 1002),
2036	((SELECT MAX(participant_id) FROM election_participant) + 4, 170001, 1001),
2037	((SELECT MAX(participant_id) FROM election_participant) + 5, 170001, 1003);
2038
2039
2040
2041	INSERT INTO vote_result (result_id, election_id, station_id, entity_id, candidate_id, votes)
2042	WITH entity_weights AS (
2043	SELECT entity_id, w_min, w_max FROM (VALUES
2044	(1001,100,400),(1002,80,350),(1003,50,200),(1004,5,80),(1005,5,80),
2045	(1006,5,70),(1007,5,80),(1008,5,40),(1009,5,40),(1010,5,40),
2046	(1011,5,60),(1012,5,40),(1013,5,40),(1014,5,40),(1015,5,40),
2047
2048	(3001,120,420),(3002,100,380),(3003,20,80),(3004,130,430),
2049	(3005,110,390),(3006,60,220),(3007,120,420),(3008,110,400),
2050	(3009,115,410),(3010,120,420),(3011,110,400),(3012,115,415),
2051	(3013,120,420),(3014,110,400),(3015,120,430),(3016,115,410),
2052
2053	(9027,60,160),(9034,15,50),(9028,65,170),(9029,40,110),
2054	(9030,55,150),(9031,45,120),(9032,50,140),(9033,50,145),
2055
2056	(9901,80,200),(9902,5,35),
2057
2058	(2001,200,500),(2002,50,180),(2003,40,160),(2004,20,90),
2059	(2005,25,100),(2006,10,60),(2007,15,70),(2008,15,65),
2060	(2009,15,65),(2010,5,40),(2011,20,80),(2012,5,40),
2061	(2013,5,40),(2014,20,80),(2015,15,65),(2016,100,350),
2062	(2017,5,40),(2018,15,65),(2019,25,90),(2020,15,65),
2063
2064	(9011,80,220),(9012,30,90),(9013,85,230),(9014,28,85),
2065	(9015,80,220),(9016,25,80),(9017,75,200),(9018,55,160),
2066	(9019,65,180),(9020,50,150),(9021,70,190),(9022,60,170),
2067	(9023,65,175),(9024,55,160),(9025,130,350),(9026,140,370)
2068	) AS w(entity_id, w_min, w_max)
2069	),
2070
2071	-- LOCAL MK
2072	lok_mk AS (
2073	SELECT ep.entity_id, pe.candidate_id, ed.election_id, ps.station_id
2074	FROM election_participant ep
2075	JOIN political_entity pe ON pe.entity_id = ep.entity_id
2076	JOIN electoral_district ed ON ed.district_id = ep.district_id
2077	JOIN election e ON e.election_id = ed.election_id
2078	JOIN polling_station ps ON ps.municipality_id = ed.region_id
2079	WHERE e.election_type_id = 3
2080	AND e.region_id = 141
2081	),
2082
2083	-- LOCAL SRB
2084	lok_srb AS (
2085	SELECT ep.entity_id, pe.candidate_id, ed.election_id, ps.station_id
2086	FROM election_participant ep
2087	JOIN political_entity pe ON pe.entity_id = ep.entity_id
2088	JOIN electoral_district ed ON ed.district_id = ep.district_id
2089	JOIN election e ON e.election_id = ed.election_id
2090	JOIN polling_station ps ON ps.municipality_id = ed.region_id
2091	WHERE e.election_type_id = 3
2092	AND e.region_id = 187
2093	),
2094
2095	-- PARLIAMENTARY MK
2096	parl_mk AS (
2097	SELECT ep.entity_id, pe.candidate_id, ed.election_id, ps.station_id
2098	FROM election_participant ep
2099	JOIN political_entity pe ON pe.entity_id = ep.entity_id
2100	JOIN electoral_district ed ON ed.district_id = ep.district_id
2101	JOIN election e ON e.election_id = ed.election_id
2102	JOIN polling_station ps ON TRUE
2103	JOIN region r1 ON r1.region_id = ps.municipality_id
2104	WHERE e.election_type_id = 2
2105	AND e.region_id = 141
2106	AND r1.parent_region_id = ed.region_id
2107	),
2108
2109	-- PARLIAMENTARY SRB
2110	parl_srb AS (
2111	SELECT ep.entity_id, pe.candidate_id, ed.election_id, ps.station_id
2112	FROM election_participant ep
2113	JOIN political_entity pe ON pe.entity_id = ep.entity_id
2114	JOIN electoral_district ed ON ed.district_id = ep.district_id
2115	JOIN election e ON e.election_id = ed.election_id
2116	JOIN polling_station ps ON TRUE
2117	JOIN region r1 ON r1.region_id = ps.municipality_id
2118	WHERE e.election_type_id = 2
2119	AND e.region_id = 187
2120	AND r1.parent_region_id = ed.region_id
2121	),
2122
2123	-- PRESIDENTIAL MK
2124	pret_mk AS (
2125	SELECT ep.entity_id, pe.candidate_id, ed.election_id, ps.station_id
2126	FROM election_participant ep
2127	JOIN political_entity pe ON pe.entity_id = ep.entity_id
2128	JOIN electoral_district ed ON ed.district_id = ep.district_id
2129	JOIN election e ON e.election_id = ed.election_id
2130	JOIN polling_station ps ON ps.municipality_id BETWEEN 1100 AND 1179
2131	WHERE e.election_type_id = 1
2132	AND e.region_id = 141
2133	),
2134
2135	-- PRESIDENTIAL SRB
2136	pret_srb AS (
2137	SELECT ep.entity_id, pe.candidate_id, ed.election_id, ps.station_id
2138	FROM election_participant ep
2139	JOIN political_entity pe ON pe.entity_id = ep.entity_id
2140	JOIN electoral_district ed ON ed.district_id = ep.district_id
2141	JOIN election e ON e.election_id = ed.election_id
2142	JOIN polling_station ps ON ps.municipality_id BETWEEN 3001 AND 3202
2143	WHERE e.election_type_id = 1
2144	AND e.region_id = 187
2145	),
2146
2147	-- REFERENDUM MK
2148	ref_mk AS (
2149	SELECT ep.entity_id, pe.candidate_id, ed.election_id, ps.station_id
2150	FROM election_participant ep
2151	JOIN political_entity pe ON pe.entity_id = ep.entity_id
2152	JOIN electoral_district ed ON ed.district_id = ep.district_id
2153	JOIN election e ON e.election_id = ed.election_id
2154	JOIN polling_station ps ON ps.municipality_id BETWEEN 1100 AND 1179
2155	WHERE e.election_type_id = 6
2156	AND e.region_id = 141
2157	),
2158
2159	-- REFERENDUM SRB
2160	ref_srb AS (
2161	SELECT ep.entity_id, pe.candidate_id, ed.election_id, ps.station_id
2162	FROM election_participant ep
2163	JOIN political_entity pe ON pe.entity_id = ep.entity_id
2164	JOIN electoral_district ed ON ed.district_id = ep.district_id
2165	JOIN election e ON e.election_id = ed.election_id
2166	JOIN polling_station ps ON ps.municipality_id BETWEEN 3001 AND 3202
2167	WHERE e.election_type_id = 6
2168	AND e.region_id = 187
2169	),
2170
2171	all_combinations AS (
2172	SELECT * FROM lok_mk
2173	UNION ALL SELECT * FROM lok_srb
2174	UNION ALL SELECT * FROM parl_mk
2175	UNION ALL SELECT * FROM parl_srb
2176	UNION ALL SELECT * FROM pret_mk
2177	UNION ALL SELECT * FROM pret_srb
2178	UNION ALL SELECT * FROM ref_mk
2179	UNION ALL SELECT * FROM ref_srb
2180	)
2181
2182	SELECT
2183	ROW_NUMBER() OVER () AS result_id,
2184	c.election_id,
2185	c.station_id,
2186	c.entity_id,
2187	c.candidate_id,
2188	GREATEST(1, FLOOR(ew.w_min + random() * (ew.w_max - ew.w_min))::int) AS votes
2189	FROM all_combinations c
2190	JOIN entity_weights ew ON ew.entity_id = c.entity_id;
2191
2192
2193
2194	-- ── Step 1: find top 2 entities per presidential election
2195	-- Used to determine which entities go to round 2
2196	CREATE TEMP TABLE _top2_presidential AS
2197	SELECT election_id, entity_id
2198	FROM (
2199	SELECT
2200	vr.election_id,
2201	vr.entity_id,
2202	SUM(vr.votes) AS total_votes,
2203	RANK() OVER (
2204	PARTITION BY vr.election_id
2205	ORDER BY SUM(vr.votes) DESC
2206	) AS rnk
2207	FROM vote_result vr
2208	WHERE vr.election_id IN (9,10,11,12,32,33,34,35,36,37,38,39,40)
2209	AND vr.candidate_id IS NULL
2210	GROUP BY vr.election_id, vr.entity_id
2211	) ranked
2212	WHERE rnk <= 2;
2213
2214	-- ── Step 2: find top 2 entities per local election per municipality
2215	CREATE TEMP TABLE _top2_local AS
2216	SELECT election_id, municipality_id, entity_id
2217	FROM (
2218	SELECT
2219	vr.election_id,
2220	ps.municipality_id,
2221	vr.entity_id,
2222	SUM(vr.votes) AS total_votes,
2223	RANK() OVER (
2224	PARTITION BY vr.election_id, ps.municipality_id
2225	ORDER BY SUM(vr.votes) DESC
2226	) AS rnk
2227	FROM vote_result vr
2228	JOIN polling_station ps ON ps.station_id = vr.station_id
2229	WHERE vr.election_id IN (13,14,15,41,42,43)
2230	AND vr.candidate_id IS NULL
2231	GROUP BY vr.election_id, ps.municipality_id, vr.entity_id
2232	) ranked
2233	WHERE rnk <= 2;
2234
2235	-- ── Verify temp tables ────────────────────────────────
2236	DO $$
2237	BEGIN
2238	RAISE NOTICE 'Top 2 presidential entries : %',
2239	(SELECT COUNT(*) FROM _top2_presidential);
2240	RAISE NOTICE 'Top 2 local entries : %',
2241	(SELECT COUNT(*) FROM _top2_local);
2242	END $$;
2243
2244
2245	-- ── Main INSERT ───────────────────────────────────────
2246	INSERT INTO ballot (
2247	election_id,
2248	station_id,
2249	entity_id,
2250	candidate_id,
2251	is_valid,
2252	ballot_timestamp
2253	)
2254
2255	WITH
2256
2257	-- ── Round 1: ALL elections, ALL entities ──────────────
2258	round1 AS (
2259	SELECT
2260	vr.election_id,
2261	vr.station_id,
2262	vr.entity_id,
2263	vr.candidate_id,
2264	e.election_date,
2265	0 AS round_offset,
2266	GREATEST(1, ROUND(vr.votes * 0.145)::INT) AS ballot_count
2267	FROM vote_result vr
2268	JOIN election e ON e.election_id = vr.election_id
2269	WHERE vr.candidate_id IS NULL
2270	),
2271
2272	-- ── Round 2: Presidential elections ──────────────────
2273	-- Only top 2 entities, 14 days after round 1
2274	round2_presidential AS (
2275	SELECT
2276	vr.election_id,
2277	vr.station_id,
2278	vr.entity_id,
2279	vr.candidate_id,
2280	e.election_date,
2281	14 AS round_offset,
2282	GREATEST(1, ROUND(vr.votes * 0.058)::INT) AS ballot_count
2283	FROM vote_result vr
2284	JOIN election e ON e.election_id = vr.election_id
2285	JOIN _top2_presidential t2 ON t2.election_id = vr.election_id
2286	AND t2.entity_id = vr.entity_id
2287	WHERE vr.election_id IN (9,10,11,12,32,33,34,35,36,37,38,39,40)
2288	AND vr.candidate_id IS NULL
2289	),
2290
2291	-- ── Round 2: Local elections ──────────────────────────
2292	-- Only top 2 entities per municipality, 14 days after round 1
2293	round2_local AS (
2294	SELECT
2295	vr.election_id,
2296	vr.station_id,
2297	vr.entity_id,
2298	vr.candidate_id,
2299	e.election_date,
2300	14 AS round_offset,
2301	GREATEST(1, ROUND(vr.votes * 0.058)::INT) AS ballot_count
2302	FROM vote_result vr
2303	JOIN election e ON e.election_id = vr.election_id
2304	JOIN polling_station ps ON ps.station_id = vr.station_id
2305	JOIN _top2_local t2 ON t2.election_id = vr.election_id
2306	AND t2.municipality_id = ps.municipality_id
2307	AND t2.entity_id = vr.entity_id
2308	WHERE vr.election_id IN (13,14,15,41,42,43)
2309	AND vr.candidate_id IS NULL
2310	),
2311
2312	-- ── Combine all rounds ────────────────────────────────
2313	all_ballots AS (
2314	SELECT election_id, station_id, entity_id, candidate_id,
2315	election_date, round_offset, ballot_count
2316	FROM round1
2317	UNION ALL
2318	SELECT election_id, station_id, entity_id, candidate_id,
2319	election_date, round_offset, ballot_count
2320	FROM round2_presidential
2321	UNION ALL
2322	SELECT election_id, station_id, entity_id, candidate_id,
2323	election_date, round_offset, ballot_count
2324	FROM round2_local
2325	),
2326
2327	-- ── Expand each row into N individual ballot rows ─────
2328	expanded AS (
2329	SELECT
2330	election_id,
2331	station_id,
2332	entity_id,
2333	candidate_id,
2334	election_date,
2335	round_offset
2336	FROM all_ballots
2337	JOIN LATERAL generate_series(1, ballot_count) AS gs(n) ON TRUE
2338	),
2339
2340	-- ── Add random values and validity ───────────────────
2341	with_random AS (
2342	SELECT
2343	election_id,
2344	station_id,
2345	entity_id,
2346	candidate_id,
2347	election_date,
2348	round_offset,
2349	RANDOM() AS r
2350	FROM expanded
2351	)
2352
2353	SELECT
2354
2355	election_id,
2356	station_id,
2357
2358	-- 2.5% invalid ballots have NULL entity_id
2359	CASE WHEN r < 0.025 THEN NULL ELSE entity_id END AS entity_id,
2360	CASE WHEN r < 0.025 THEN NULL ELSE candidate_id END AS candidate_id,
2361
2362	-- is_valid flag
2363	CASE WHEN r < 0.025 THEN FALSE ELSE TRUE END AS is_valid,
2364
2365	-- Timestamp: election date + round offset + random hour 7:00-19:00
2366	(
2367	election_date::TIMESTAMP
2368	+ (round_offset \|\| ' days')::INTERVAL
2369	+ INTERVAL '7 hours'
2370	+ (RANDOM() * 43200 \|\| ' seconds')::INTERVAL
2371	) AS ballot_timestamp
2372
2373	FROM with_random;
2374
2375
2376	-- ── Cleanup temp tables ───────────────────────────────
2377	DROP TABLE IF EXISTS _top2_presidential;
2378	DROP TABLE IF EXISTS _top2_local;
2379
2380
2381
2382
2383
2384	INSERT INTO vote_result (result_id, election_id, station_id, entity_id, candidate_id, votes)
2385	WITH
2386	entity_weights(election_id, entity_id, weight) AS (VALUES
2387	-- МК ПАРЛАМЕНТАРНИ
2388	(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),
2389	(2,3004,0.488),(2,3005,0.236),(2,1003,0.128),(2,1004,0.083),(2,1011,0.030),(2,1010,0.035),
2390	(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),
2391	(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),
2392	(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),
2393	(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),
2394	(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),
2395	(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),
2396	-- МК ПРЕТСЕДАТЕЛСКИ
2397	(9,9027,0.634),(9,9034,0.366),
2398	(10,9028,0.556),(10,9029,0.444),
2399	(11,9030,0.514),(11,9031,0.486),
2400	(12,9032,0.654),(12,9033,0.346),
2401	-- МК ЛОКАЛНИ
2402	(13,1001,0.420),(13,1002,0.300),(13,1003,0.100),(13,1004,0.080),(13,1011,0.050),(13,1010,0.050),
2403	(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),
2404	(15,1001,0.340),(15,1002,0.400),(15,1003,0.090),(15,1006,0.060),(15,1005,0.050),(15,1007,0.060),
2405	-- МК РЕФЕРЕНДУМИ
2406	(16,9001,0.950),(16,9002,0.050),
2407	(17,9001,0.910),(17,9002,0.090),
2408	-- СРБ ПАРЛАМЕНТАРНИ
2409	(18,2002,0.460),(18,2006,0.200),(18,2015,0.180),(18,2005,0.080),(18,2009,0.080),
2410	(19,2002,0.400),(19,2005,0.220),(19,2006,0.170),(19,2015,0.120),(19,2009,0.090),
2411	(20,2002,0.370),(20,2005,0.240),(20,2006,0.160),(20,2015,0.140),(20,2009,0.090),
2412	(21,2002,0.350),(21,2005,0.280),(21,2006,0.190),(21,2009,0.090),(21,2015,0.090),
2413	(22,2016,0.520),(22,2002,0.280),(22,2005,0.140),(22,2009,0.060),
2414	(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),
2415	(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),
2416	(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),
2417	(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),
2418	(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),
2419	(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),
2420	(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),
2421	(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),
2422	(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),
2423	-- СРБ ПРЕТСЕДАТЕЛСКИ
2424	(32,9011,0.650),(32,9012,0.350),
2425	(33,9013,0.600),(33,9014,0.400),
2426	(34,9015,0.550),(34,9016,0.450),
2427	(35,9017,0.500),(35,9018,0.500),
2428	(36,9019,0.530),(36,9020,0.470),
2429	(37,9021,0.520),(37,9022,0.480),
2430	(38,9023,0.510),(38,9024,0.490),
2431	(39,9025,1.000),
2432	(40,9026,1.000),
2433	-- СРБ ЛОКАЛИ
2434	(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),
2435	(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),
2436	(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),
2437	-- СРБ РЕФЕРЕНДУМИ
2438	(44,9001,0.530),(44,9002,0.470),
2439	(45,9001,0.600),(45,9002,0.400)
2440	),
2441
2442	turnout_factors(election_id, turnout) AS (VALUES
2443	(1,0.56),(2,0.57),(3,0.63),(4,0.61),(5,0.67),(6,0.42),(7,0.51),(8,0.55),
2444	(9,0.57),(10,0.55),(11,0.46),(12,0.55),
2445	(13,0.48),(14,0.60),(15,0.51),
2446	(16,0.75),(17,0.37),
2447	(18,0.72),(19,0.70),(20,0.68),(21,0.65),(22,0.73),(23,0.58),(24,0.60),(25,0.61),
2448	(26,0.57),(27,0.53),(28,0.56),(29,0.48),(30,0.55),(31,0.59),
2449	(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),
2450	(41,0.45),(42,0.47),(43,0.49),
2451	(44,0.55),(45,0.45)
2452	),
2453
2454	regional_bias(election_id, entity_id, region_id, bias) AS (VALUES
2455	(1,3001,1000,1.12),(1,3002,1000,0.95),(1,1004,1000,0.80),
2456	(1,3001,1003,1.08),(1,3002,1003,1.05),
2457	(1,1004,1004,1.80),(1,1011,1004,1.60),(1,3003,1004,0.50),
2458	(1,1004,1006,1.70),(1,1011,1006,1.50),
2459	(1,1004,1002,1.40),(1,1011,1002,1.30),
2460	(2,1004,1004,1.85),(2,1003,1004,1.20),
2461	(2,1004,1006,1.75),(2,1003,1006,1.30),
2462	(2,1004,1002,1.50),(2,3004,1000,1.15),(2,3004,1003,1.10),
2463	(8,3015,1007,1.20),(8,3015,1003,1.15),(8,3016,1000,1.25),
2464	(8,1004,1004,1.90),(8,1004,1006,1.80)
2465	),
2466
2467	station_district AS (
2468	-- МК Парламентарни (1-8)
2469	SELECT se.election_id, se.station_id, ed.district_id,
2470	ps.municipality_id AS municipality_region_id,
2471	r_mun.parent_region_id AS parent_region_id
2472	FROM station_election se
2473	JOIN polling_station ps ON ps.station_id = se.station_id
2474	JOIN region r_mun ON r_mun.region_id = ps.municipality_id
2475	JOIN electoral_district ed ON ed.election_id = se.election_id
2476	AND ed.region_id = r_mun.parent_region_id
2477	WHERE se.election_id BETWEEN 1 AND 8
2478
2479	UNION ALL
2480
2481	-- МК Претседателски (9-12)
2482	SELECT se.election_id, se.station_id, ed.district_id,
2483	ps.municipality_id,
2484	r_mun.parent_region_id
2485	FROM station_election se
2486	JOIN polling_station ps ON ps.station_id = se.station_id
2487	JOIN region r_mun ON r_mun.region_id = ps.municipality_id
2488	JOIN region r_reg ON r_reg.region_id = r_mun.parent_region_id
2489	JOIN electoral_district ed ON ed.election_id = se.election_id
2490	AND ed.region_id = r_reg.parent_region_id
2491	WHERE se.election_id BETWEEN 9 AND 12
2492
2493	UNION ALL
2494
2495	-- МК Локални (13-15)
2496	SELECT se.election_id, se.station_id, ed.district_id,
2497	ps.municipality_id,
2498	NULL::int8 AS parent_region_id
2499	FROM station_election se
2500	JOIN polling_station ps ON ps.station_id = se.station_id
2501	JOIN electoral_district ed ON ed.election_id = se.election_id
2502	AND ed.region_id = ps.municipality_id
2503	WHERE se.election_id BETWEEN 13 AND 15
2504
2505	UNION ALL
2506
2507	-- МК Референдуми (16-17)
2508	SELECT se.election_id, se.station_id, ed.district_id,
2509	ps.municipality_id,
2510	r_mun.parent_region_id
2511	FROM station_election se
2512	JOIN polling_station ps ON ps.station_id = se.station_id
2513	JOIN region r_mun ON r_mun.region_id = ps.municipality_id
2514	JOIN region r_reg ON r_reg.region_id = r_mun.parent_region_id
2515	JOIN electoral_district ed ON ed.election_id = se.election_id
2516	AND ed.region_id = r_reg.parent_region_id
2517	WHERE se.election_id IN (16, 17)
2518
2519	UNION ALL
2520
2521	-- СРБ Парламентарни + Претседателски + Референдуми (18-40, 44-45)
2522	SELECT se.election_id, se.station_id, ed.district_id,
2523	ps.municipality_id,
2524	r_mun.parent_region_id
2525	FROM station_election se
2526	JOIN polling_station ps ON ps.station_id = se.station_id
2527	JOIN region r_mun ON r_mun.region_id = ps.municipality_id
2528	JOIN region r_okrug ON r_okrug.region_id = r_mun.parent_region_id
2529	JOIN electoral_district ed ON ed.election_id = se.election_id
2530	AND ed.region_id = r_okrug.parent_region_id
2531	WHERE se.election_id IN (
2532	18,19,20,21,22,23,24,25,26,27,28,29,30,31,
2533	32,33,34,35,36,37,38,39,40,44,45
2534	)
2535
2536	UNION ALL
2537
2538	-- СРБ Локали (41, 42, 43)
2539	SELECT se.election_id, se.station_id, ed.district_id,
2540	ps.municipality_id,
2541	NULL::int8 AS parent_region_id
2542	FROM station_election se
2543	JOIN polling_station ps ON ps.station_id = se.station_id
2544	JOIN electoral_district ed ON ed.election_id = se.election_id
2545	AND ed.region_id = ps.municipality_id
2546	WHERE se.election_id IN (41, 42, 43)
2547	),
2548
2549	station_totals AS (
2550	SELECT sd.election_id, sd.station_id, sd.district_id,
2551	sd.municipality_region_id, sd.parent_region_id,
2552	GREATEST(0, ROUND(
2553	ps.registered_voter * tf.turnout * (0.92 + random() * 0.16)
2554	)::int) AS total_votes
2555	FROM station_district sd
2556	JOIN polling_station ps ON ps.station_id = sd.station_id
2557	JOIN turnout_factors tf ON tf.election_id = sd.election_id
2558	),
2559
2560	distributed AS (
2561	SELECT st.election_id, st.station_id, ep.entity_id,
2562	pe.candidate_id,
2563	GREATEST(0, ROUND(
2564	st.total_votes::numeric * ew.weight
2565	* COALESCE(rb.bias, 1.0)
2566	* (0.94 + random() * 0.12)
2567	)::int) AS votes
2568	FROM station_totals st
2569	JOIN election_participant ep ON ep.district_id = st.district_id
2570	JOIN entity_weights ew ON ew.election_id = st.election_id
2571	AND ew.entity_id = ep.entity_id
2572	JOIN political_entity pe ON pe.entity_id = ep.entity_id
2573	LEFT JOIN regional_bias rb ON rb.election_id = st.election_id
2574	AND rb.entity_id = ep.entity_id
2575	AND rb.region_id = st.parent_region_id
2576	)
2577
2578	SELECT
2579	ROW_NUMBER() OVER (ORDER BY election_id, station_id, entity_id) AS result_id,
2580	election_id, station_id, entity_id, candidate_id, votes
2581	FROM distributed;
2582
2583
2584
2585
2586
2587
2588
2589	UPDATE vote_result vr
2590	SET votes = GREATEST(1, ROUND(
2591	vc.voter_count
2592	* tf.turnout
2593	* ew.weight
2594	* (0.90 + random() * 0.20)
2595	)::int)
2596	FROM
2597	(VALUES
2598	(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),
2599	(2,3004,0.488),(2,3005,0.236),(2,1003,0.128),(2,1004,0.083),(2,1011,0.030),(2,1010,0.035),
2600	(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),
2601	(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),
2602	(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),
2603	(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),
2604	(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),
2605	(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),
2606	(9,9027,0.634),(9,9034,0.366),
2607	(10,9028,0.556),(10,9029,0.444),
2608	(11,9030,0.514),(11,9031,0.486),
2609	(12,9032,0.654),(12,9033,0.346),
2610	(13,1001,0.420),(13,1002,0.300),(13,1003,0.100),(13,1004,0.080),(13,1011,0.050),(13,1010,0.050),
2611	(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),
2612	(15,1001,0.340),(15,1002,0.400),(15,1003,0.090),(15,1006,0.060),(15,1005,0.050),(15,1007,0.060),
2613	(16,9901,0.950),(16,9902,0.050),
2614	(17,9901,0.910),(17,9902,0.090)
2615	) AS ew(election_id, entity_id, weight),
2616	(VALUES
2617	(1,0.56),(2,0.57),(3,0.63),(4,0.61),(5,0.67),(6,0.42),(7,0.51),(8,0.55),
2618	(9,0.57),(10,0.55),(11,0.46),(12,0.55),
2619	(13,0.48),(14,0.60),(15,0.51),
2620	(16,0.75),(17,0.37)
2621	) AS tf(election_id, turnout),
2622	(
2623	SELECT station_id, COUNT(*) AS voter_count
2624	FROM voter
2625	GROUP BY station_id
2626	) AS vc
2627	WHERE vr.election_id = ew.election_id
2628	AND vr.entity_id = ew.entity_id
2629	AND vr.election_id = tf.election_id
2630	AND vr.station_id = vc.station_id
2631	AND vr.election_id IN (
2632	SELECT election_id FROM election WHERE region_id = 141
2633	);
2634
2635
2636
2637	-- Прво создај temp табела со voter counts
2638	CREATE TEMP TABLE tmp_voter_counts AS
2639	SELECT station_id, COUNT(*) AS voter_count
2640	FROM voter
2641	GROUP BY station_id;
2642
2643	CREATE INDEX ON tmp_voter_counts(station_id);
2644
2645	-- Потоа UPDATE со JOIN на temp табела
2646	UPDATE vote_result vr
2647	SET votes = GREATEST(1, ROUND(
2648	tvc.voter_count
2649	* tf.turnout
2650	* ew.weight
2651	* (0.90 + random() * 0.20)
2652	)::int)
2653	FROM
2654	(VALUES
2655	(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),
2656	(2,3004,0.488),(2,3005,0.236),(2,1003,0.128),(2,1004,0.083),(2,1011,0.030),(2,1010,0.035),
2657	(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),
2658	(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),
2659	(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),
2660	(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),
2661	(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),
2662	(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),
2663	(9,9027,0.634),(9,9034,0.366),
2664	(10,9028,0.556),(10,9029,0.444),
2665	(11,9030,0.514),(11,9031,0.486),
2666	(12,9032,0.654),(12,9033,0.346),
2667	(13,1001,0.420),(13,1002,0.300),(13,1003,0.100),(13,1004,0.080),(13,1011,0.050),(13,1010,0.050),
2668	(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),
2669	(15,1001,0.340),(15,1002,0.400),(15,1003,0.090),(15,1006,0.060),(15,1005,0.050),(15,1007,0.060),
2670	(16,9901,0.950),(16,9902,0.050),
2671	(17,9901,0.910),(17,9902,0.090)
2672	) AS ew(election_id, entity_id, weight),
2673	(VALUES
2674	(1,0.56),(2,0.57),(3,0.63),(4,0.61),(5,0.67),(6,0.42),(7,0.51),(8,0.55),
2675	(9,0.57),(10,0.55),(11,0.46),(12,0.55),
2676	(13,0.48),(14,0.60),(15,0.51),
2677	(16,0.75),(17,0.37)
2678	) AS tf(election_id, turnout),
2679	tmp_voter_counts tvc
2680	WHERE vr.election_id = ew.election_id
2681	AND vr.entity_id = ew.entity_id
2682	AND vr.election_id = tf.election_id
2683	AND vr.station_id = tvc.station_id
2684	AND vr.election_id IN (
2685	SELECT election_id FROM election WHERE region_id = 141
2686	);
2687
2688	-- Исчисти
2689	DROP TABLE tmp_voter_counts;
2690
2691	SELECT SUM(votes) as sum_votes FROM vote_result;
2692
2693	SELECT
2694	COUNT(*) AS vr_rows,
2695	SUM(votes) AS sum_votes,
2696	AVG(votes)::int AS avg_votes
2697	FROM vote_result;
2698
2699
2700
2701
2702
2703	INSERT INTO public.election_type (election_type_id, type_name) VALUES(1, 'Presidential');
2704	INSERT INTO public.election_type (election_type_id, type_name) VALUES(2, 'Parliamentary');
2705	INSERT INTO public.election_type (election_type_id, type_name) VALUES(3, 'Local/Municipal');
2706	INSERT INTO public.election_type (election_type_id, type_name) VALUES(4, 'Subnational');
2707	INSERT INTO public.election_type (election_type_id, type_name) VALUES(5, 'Supranational');
2708	INSERT INTO public.election_type (election_type_id, type_name) VALUES(6, 'Referendum');
2709	INSERT INTO public.election_type (election_type_id, type_name) VALUES(7, 'By-Election');
2710	INSERT INTO public.election_type (election_type_id, type_name) VALUES(8, 'Party Primary');
2711	INSERT INTO public.election_type (election_type_id, type_name) VALUES(9, 'Internal Election');
2712
2713
2714
2715
2716	INSERT INTO public.region_type (region_type_id, "name", description) VALUES(1, 'Country', 'Top-level sovereign state');
2717	INSERT INTO public.region_type (region_type_id, "name", description) VALUES(2, 'First-level administrative division', 'State / Region / Province');
2718	INSERT INTO public.region_type (region_type_id, "name", description) VALUES(3, 'Second-level administrative division', 'County / District / Municipality');
2719	INSERT INTO public.region_type (region_type_id, "name", description) VALUES(4, 'Third-level administrative division', 'City / Town / Local unit');
2720	INSERT INTO public.region_type (region_type_id, "name", description) VALUES(5, 'Fourth-level administrative division', 'Neighborhood / Ward / Precinct');
2721
2722
2723
2724
2725
2726	INSERT INTO public.winner_method (method_id, method_name) VALUES(1, 'Plurality (FPTP)');
2727	INSERT INTO public.winner_method (method_id, method_name) VALUES(2, 'Absolute Majority');
2728	INSERT INTO public.winner_method (method_id, method_name) VALUES(3, 'Two-Round Runoff');
2729	INSERT INTO public.winner_method (method_id, method_name) VALUES(4, 'Instant Runoff (IRV)');
2730	INSERT INTO public.winner_method (method_id, method_name) VALUES(5, 'Approval Voting');
2731	INSERT INTO public.winner_method (method_id, method_name) VALUES(6, 'Borda Count');
2732	INSERT INTO public.winner_method (method_id, method_name) VALUES(7, 'Condorcet Method');
2733	INSERT INTO public.winner_method (method_id, method_name) VALUES(8, 'D’Hondt');
2734	INSERT INTO public.winner_method (method_id, method_name) VALUES(9, 'Sainte-Laguë');
2735	INSERT INTO public.winner_method (method_id, method_name) VALUES(10, 'Hare Quota');
2736	INSERT INTO public.winner_method (method_id, method_name) VALUES(11, 'Droop Quota');
2737	INSERT INTO public.winner_method (method_id, method_name) VALUES(12, 'Single Transferable Vote (STV)');
2738	INSERT INTO public.winner_method (method_id, method_name) VALUES(13, 'Mixed-Member Proportional (MMP)');
2739
2740
2741
2742
2743
2744
2745	CREATE TABLE _stage_regions (
2746	country_name TEXT,
2747	region_name TEXT,
2748	municipality_name TEXT
2749	);
2750
2751
2752	COPY _stage_region_import
2753	FROM 'C:/Users/Luna/Downloads/regions.csv'
2754	DELIMITER ','
2755	CSV HEADER;
2756
2757
2758	INSERT INTO region(region_id, name, region_type_id, parent_region_id)
2759	SELECT
2760	ROW_NUMBER() OVER (),
2761	country_name,
2762	1,
2763	NULL
2764	FROM _stage_regions
2765	GROUP BY country_name;
2766
2767
2768
2769
2770
2771	INSERT INTO region(region_id, name, region_type_id, parent_region_id)
2772	SELECT
2773	1000 + ROW_NUMBER() OVER (),
2774	s.region_name,
2775	2,
2776	r.region_id
2777	FROM _stage_regions s
2778	JOIN region r
2779	ON r.name = s.country_name
2780	AND r.region_type_id = 1
2781	GROUP BY s.region_name, r.region_id;
2782
2783
2784
2785
2786	INSERT INTO region(region_id, name, region_type_id, parent_region_id)
2787	SELECT
2788	1100 + ROW_NUMBER() OVER (),
2789	s.municipality_name,
2790	3,
2791	r.region_id
2792	FROM _stage_regions s
2793	JOIN region r
2794	ON r.name = s.region_name
2795	AND r.region_type_id = 2;
2796
2797
2798
2799	DROP TABLE _state_regions;
2800
2801
2802
2803
2804
2805
2806
2807
2808	CREATE TABLE _stage_polling_station (
2809	station_id int8,
2810	"name" varchar(255),
2811	municipality_id int8,
2812	address text,
2813	registered_voter int,
2814	voting_object text
2815	);
2816
2817
2818
2819
2820	\copy _stage_polling_station FROM 'polling_stations.csv' WITH (FORMAT csv, HEADER true);
2821
2822
2823
2824	INSERT INTO public.polling_station (station_id, "name", municipality_id, address, registered_voter, voting_object)
2825	SELECT station_id, "name", municipality_id, address, registered_voter, voting_object
2826	FROM _stage_polling_station;
2827
2828
2829
2830	DROP TABLE _stage_polling_station;
2831
2832
2833
2834
2835
2836	DO $$
2837	DECLARE
2838	v_batch INT := 500000;
2839	v_updated INT;
2840	v_total INT := 0;
2841	BEGIN
2842	LOOP
2843	UPDATE person p
2844	SET municipality_id = ps.municipality_id
2845	FROM voter v
2846	JOIN polling_station ps ON ps.station_id = v.station_id
2847	WHERE v.person_id = p.person_id
2848	AND p.municipality_id IS NULL
2849	AND p.ctid IN (
2850	SELECT ctid
2851	FROM person
2852	WHERE municipality_id IS NULL
2853	LIMIT v_batch
2854	);
2855
2856	GET DIAGNOSTICS v_updated = ROW_COUNT;
2857	v_total := v_total + v_updated;
2858	RAISE NOTICE 'Updated % rows so far...', v_total;
2859
2860	EXIT WHEN v_updated = 0;
2861	END LOOP;
2862
2863	RAISE NOTICE 'Done. Total updated: %', v_total;
2864	END $$;
2865
2866	SELECT
2867	COUNT() AS total,
2868	COUNT(municipality_id) AS with_municipality,
2869	COUNT() - COUNT(municipality_id) AS still_null
2870	FROM person;
2871
2872	ANALYZE person;
2873

Download in other formats:

Original Format