DatabaseCreation: dml.sql

-------------------------------------------------------------------------------------------------------
--DML ZA GENERIRANJE PODATOCI
INSERT INTO Kategorija (kod) VALUES
('AM'),
('A1'),
('A2'),
('A'),
('B'),
('C1'),
('C'),
('D1'),
('D'),
('BE'),
('C1E'),
('CE'),
('D1E'),
('DE'),
('F'),
('G');
---------------------------------------------------------------------------------

--Polnenje Gragjanin

CREATE TABLE IF NOT EXISTS temp_maski_iminja (id BIGSERIAL PRIMARY KEY);
CREATE TABLE IF NOT EXISTS temp_zenski_iminja (id BIGSERIAL PRIMARY KEY);
CREATE TABLE IF NOT EXISTS temp_maski_preziminja (id BIGSERIAL PRIMARY KEY);
CREATE TABLE IF NOT EXISTS temp_zenski_preziminja (id BIGSERIAL PRIMARY KEY);
CREATE TABLE IF NOT EXISTS temp_adresi (id BIGSERIAL PRIMARY KEY);


CREATE TEMP TABLE t_m_ime AS SELECT name as ime, row_number() over() as rn FROM temp_maski_iminja;
CREATE TEMP TABLE t_m_prez AS SELECT surname as prezime, row_number() over() as rn FROM temp_maski_preziminja;
CREATE TEMP TABLE t_z_ime AS SELECT name as ime, row_number() over() as rn FROM temp_zenski_iminja;
CREATE TEMP TABLE t_z_prez AS SELECT surname_female as prezime, row_number() over() as rn FROM temp_zenski_preziminja;
CREATE TEMP TABLE t_adr AS SELECT adresa, row_number() over() as rn FROM temp_adresi;


CREATE TEMP TABLE t_broj AS
SELECT 
    (SELECT count(*) FROM t_m_ime) as c_m_ime,
    (SELECT count(*) FROM t_m_prez) as c_m_prez,
    (SELECT count(*) FROM t_z_ime) as c_z_ime,
    (SELECT count(*) FROM t_z_prez) as c_z_prez,
    (SELECT count(*) FROM t_adr) as c_adr;


----za zenski

INSERT INTO Gragjanin (EMBG, ime, prezime, pol, adresa, datum_ragjanje)
SELECT 

    (
        to_char(g.d, 'DD') ||    -- DD
        to_char(g.d, 'MM') ||        -- MM
        right(to_char(g.d, 'YYYY'), 3) ||    -- YYY
        (floor(random() * 9) + 41)::TEXT ||      -- RR (41 до 49)
        '5' ||             -- G (5 za zenski)
        lpad(floor(random() * 100)::TEXT, 2, '0') ||    -- NN (00 до 99)
        floor(random() * 10)::TEXT           -- C (0 до 9)
    )::CHAR(13) as EMBG,
    i.ime, 
    p.prezime, 
    'F', 
    a.adresa, 
    g.d
FROM (
    SELECT 
        gs.broj, 
        (CURRENT_DATE - INTERVAL '18 years' - (random() * INTERVAL '52 years'))::DATE as d 
    FROM generate_series(1, 1000000) gs(broj)
) g
CROSS JOIN t_broj b
JOIN t_z_ime i ON i.rn = (g.broj % b.c_z_ime) + 1
JOIN t_z_prez p ON p.rn = ((g.broj * 3) % b.c_z_prez) + 1
JOIN t_adr a ON a.rn = ((g.broj * 7) % b.c_adr) + 1
ON CONFLICT (EMBG) DO NOTHING;
---za maski
INSERT INTO Gragjanin (EMBG, ime, prezime, pol, adresa, datum_ragjanje)
SELECT
    (
        to_char(g.d, 'DD') ||  -- DD
        to_char(g.d, 'MM') ||    -- MM
        right(to_char(g.d, 'YYYY'), 3) ||   -- YYY
        (floor(random() * 9) + 41)::TEXT ||      -- RR(41 do 49)
        '0' ||                            -- G (0 za maski)
        lpad(floor(random() * 100)::TEXT, 2, '0') ||    -- NN (00 do 99)
        floor(random() * 10)::TEXT   -- C (0 do 9)
    )::CHAR(13) as EMBG,
    i.ime,
    p.prezime,
    'M',
    a.adresa,
    g.d
FROM (
    SELECT
        gs.broj,
        (CURRENT_DATE - INTERVAL '18 years' - (random() * INTERVAL '52 years'))::DATE as d
    FROM generate_series(1, 1000000) gs(broj)
) g
CROSS JOIN t_broj b
JOIN t_m_ime  i ON i.rn = (g.broj % b.c_m_ime)        + 1
JOIN t_m_prez p ON p.rn = ((g.broj * 3) % b.c_m_prez) + 1
JOIN t_adr    a ON a.rn = ((g.broj * 7) % b.c_adr)    + 1
ON CONFLICT (EMBG) DO NOTHING;


DROP TABLE t_m_ime;
DROP TABLE t_m_prez;
DROP TABLE t_z_ime;
DROP TABLE t_z_prez;
DROP TABLE t_adr;
DROP TABLE t_broj;

DROP TABLE IF EXISTS temp_maski_iminja;
DROP TABLE IF EXISTS temp_zenski_iminja;
DROP TABLE IF EXISTS temp_maski_preziminja;
DROP TABLE IF EXISTS temp_zenski_preziminja;
DROP TABLE IF EXISTS temp_adresi;
select * from gragjanin;

------------------------------------------------------------------------------------------------
--Polnenje policaec

CREATE TEMP TABLE tmp_sefovi AS
SELECT EMBG, row_number() over() - 1 as rn
FROM Gragjanin
WHERE datum_ragjanje < (CURRENT_DATE - INTERVAL '30 years')
LIMIT 500;


CREATE TEMP TABLE tmp_obicni AS
SELECT EMBG, row_number() over() - 1 as rn
FROM Gragjanin
WHERE datum_ragjanje < (CURRENT_DATE - INTERVAL '21 years')
  AND EMBG NOT IN (SELECT EMBG FROM tmp_sefovi)
LIMIT 9500;


INSERT INTO Policaec (EMBG_P, broj_na_znacka, EMBG_nadreden)
SELECT 
    EMBG, 
    'ZN-S-' || LPAD((rn + 1)::text, 4, '0'),
    NULL
FROM tmp_sefovi;


INSERT INTO Policaec (EMBG_P, broj_na_znacka, EMBG_nadreden)
SELECT 
    o.EMBG, 
    'ZN-' || LPAD((o.rn + 1)::text, 6, '0'),
    s.EMBG
FROM tmp_obicni o
JOIN tmp_sefovi s ON s.rn = (o.rn % 500);


DROP TABLE tmp_sefovi;
DROP TABLE tmp_obicni;

-------------------------------------------------------------------------

-- Polnenje sektor
INSERT INTO Sektor (id_sektor, ime, region, telefon) VALUES 
(1, 'SVR Skopje', 'Skopski', '+38923111111'),
(2, 'SVR Bitola', 'Pelagoniski', '+38947222222'),
(3, 'SVR Kumanovo', 'Severoistocen', '+38931333333'),
(4, 'SVR Shtip', 'Istocen', '+38932444444'),
(5, 'SVR Tetovo', 'Pologski', '+38944555555'),
(6, 'SVR Ohrid', 'Jugozapaden', '+38946666666'),
(7, 'SVR Veles', 'Vardarski', '+38943777777'),
(8, 'SVR Strumica', 'Jugoistocen', '+38934888888');

-- Polnenje stanica
INSERT INTO Stanica (ime, adresa, id_sektor) VALUES 
('PS Centar', 'ul. Goce Delcev br. 2, Skopje', 1),
('PS Karposh', 'ul. Partizanski odredi br. 80, Skopje', 1),
('PS Bit Pazar', 'ul. Krste Misirkov br. 1, Skopje', 1),
('PS Bitola 1', 'ul. Shirok Sokak br. 100, Bitola', 2),
('PS Prilep', 'ul. Mirce Acev br. 45, Prilep', 2),
('PS Resen', 'ul. 29-ti Noemvri br. 5, Resen', 2),
('PS Kumanovo', 'ul. 11-ti Oktomvri br. 1, Kumanovo', 3),
('PS Kriva Palanka', 'ul. Marsal Tito br. 88, Kriva Palanka', 3),
('PS Shtip', 'ul. Vanco Prke br. 30, Shtip', 4),
('PS Kocani', 'ul. Marsal Tito br. 12, Kocani', 4),
('PS Tetovo', 'ul. Ilindenska br. 10, Tetovo', 5),
('PS Gostivar', 'ul. JNA br. 2, Gostivar', 5),
('PS Ohrid', 'ul. Partizanska br. 1, Ohrid', 6),
('PS Struga', 'ul. Marshal Tito br. 15, Struga', 6),
('PS Kicevo', 'ul. Boris Kidric br. 4, Kicevo', 6),
('PS Veles', 'ul. 8-mi Septemvri br. 22, Veles', 7),
('PS Kavadarci', 'ul. Ilindenska br. 55, Kavadarci', 7),
('PS Negotino', 'ul. Marsal Tito br. 10, Negotino', 7),
('PS Strumica', 'ul. Leninova br. 4, Strumica', 8),
('PS Gevgelija', 'ul. 7-mi Noemvri br. 2, Gevgelija', 8);

CREATE TEMP TABLE temp_lokacii AS
SELECT 
    g.EMBG, 
    split_part(g.adresa, ' ', array_length(string_to_array(g.adresa, ' '), 1)) as grad
FROM Gragjanin g
JOIN Policaec p ON g.EMBG = p.EMBG_P;


INSERT INTO Raboti_vo (EMBG_P, id_stanica, datum_od)
SELECT 
    tl.EMBG, 
    COALESCE(

        (SELECT s.id_stanica 
         FROM Stanica s 
         WHERE split_part(s.adresa, ' ', array_length(string_to_array(s.adresa, ' '), 1)) = tl.grad 
         LIMIT 1),
         
   
        (SELECT id_stanica 
         FROM Stanica 
         ORDER BY RANDOM() 
         LIMIT 1)
    ) as id_stanica,
    (CURRENT_DATE - INTERVAL '1 year' * floor(random() * 15 + 1))::DATE as d_od
FROM temp_lokacii tl
ON CONFLICT DO NOTHING;


UPDATE Raboti_vo
SET datum_do = datum_od + (random() * (CURRENT_DATE - datum_od))::int
WHERE EMBG_P IN (
    SELECT EMBG_P
    FROM Raboti_vo
    WHERE datum_do IS NULL
    ORDER BY RANDOM()
    LIMIT 1500
);


DROP TABLE temp_lokacii;
---- update za policajci da imaat soodvetni sefovi

WITH segashni_rabotni_mesta AS (
    SELECT EMBG_P, id_stanica 
    FROM Raboti_vo 
    WHERE datum_do IS NULL
),
sefovi_po_stanica AS (
    SELECT 
        rm.id_stanica, 
        array_agg(p.EMBG_P) as sefovi_niza,
        count(*) as vkupno_sefovi
    FROM Policaec p
    JOIN segashni_rabotni_mesta rm ON p.EMBG_P = rm.EMBG_P
    WHERE p.EMBG_nadreden IS NULL
    GROUP BY rm.id_stanica
)
UPDATE Policaec p
SET EMBG_nadreden = sps.sefovi_niza[floor(random() * sps.vkupno_sefovi) + 1]
FROM segashni_rabotni_mesta rm
JOIN sefovi_po_stanica sps ON rm.id_stanica = sps.id_stanica
WHERE p.EMBG_P = rm.EMBG_P 
  AND p.EMBG_nadreden IS NOT NULL;

-----------------------------------------------------------------------------
---polnenje na vozilo

INSERT INTO Vozilo (broj_na_sasija, model, id_kategorija)
SELECT
    upper(substring(md5(rand.i::text || rand.sasija_rand::text) from 1 for 17)),
    CASE k.kat_id
        WHEN 1 THEN (ARRAY['Tomos APN 6', 'Piaggio Zip 50', 'Peugeot Kisbee 50', 'Aprilia SR 50', 'Kymco Agility 50'])[floor(random()*5)+1]
        WHEN 2 THEN (ARRAY['Yamaha MT-125', 'Honda CB125R', 'KTM 125 Duke', 'Aprilia RS 125'])[floor(random()*4)+1]
        WHEN 3 THEN (ARRAY['Honda CB500F', 'Kawasaki Z400', 'Yamaha MT-03', 'KTM 390 Duke'])[floor(random()*4)+1]
        WHEN 4 THEN (ARRAY['Yamaha MT-09', 'Kawasaki Z900', 'Honda CBR1000RR', 'BMW R 1250 GS', 'Suzuki Hayabusa'])[floor(random()*5)+1]
        WHEN 5 THEN (ARRAY['VW Golf', 'Skoda Octavia', 'Opel Astra', 'Toyota Corolla', 'Renault Clio', 'Ford Focus', 'BMW 320d', 'Audi A4'])[floor(random()*8)+1]
        WHEN 6 THEN (ARRAY['Iveco Daily', 'Mercedes Sprinter', 'Ford Transit', 'Fiat Ducato Maxi'])[floor(random()*4)+1]
        WHEN 7 THEN (ARRAY['MAN TGS', 'Mercedes Actros', 'Volvo FH', 'Scania R-series', 'DAF XF'])[floor(random()*5)+1]
        WHEN 8 THEN (ARRAY['Mercedes Sprinter Minibus', 'Ford Transit Minibus', 'Iveco Daily Minibus'])[floor(random()*3)+1]
        WHEN 9 THEN (ARRAY['Mercedes Tourismo', 'Setra S 415', 'Neoplan Tourliner', 'MAN Lion''s Coach'])[floor(random()*4)+1]
        WHEN 10 THEN (ARRAY['VW Touareg', 'Jeep Grand Cherokee', 'Toyota Hilux'])[floor(random()*3)+1]
        WHEN 11 THEN (ARRAY['Iveco Daily Prikolica', 'Mercedes Sprinter Prikolica'])[floor(random()*2)+1]
        WHEN 12 THEN (ARRAY['MAN TGX Poluprikolka', 'Volvo FH16 Prikolka', 'Scania S500 Prikolka'])[floor(random()*3)+1]
        WHEN 13 THEN (ARRAY['Mercedes Minibus Prikolica', 'Ford Transit Prikolica'])[floor(random()*2)+1]
        WHEN 14 THEN (ARRAY['Setra S 531 DT', 'Mercedes Travego Prikolica'])[floor(random()*2)+1]
        WHEN 15 THEN (ARRAY['John Deere 5075', 'IMT 539', 'Massey Ferguson 5700', 'Zetor', 'New Holland T4'])[floor(random()*5)+1]
        WHEN 16 THEN (ARRAY['Caterpillar 428', 'JCB 3CX', 'Bobcat S130', 'Komatsu Bager', 'IMT 506'])[floor(random()*5)+1]
    END,
    k.kat_id
FROM (
    SELECT 
        gs.i,
        random() as r,
        random() as sasija_rand
    FROM generate_series(1, 1000000) gs(i)
) rand
CROSS JOIN LATERAL (
    SELECT CASE
        WHEN rand.r < 0.70 THEN 5 
        WHEN rand.r < 0.72 THEN 1 
        WHEN rand.r < 0.74 THEN 2 
        WHEN rand.r < 0.76 THEN 3 
        WHEN rand.r < 0.78 THEN 4 
        WHEN rand.r < 0.81 THEN 15 
        WHEN rand.r < 0.83 THEN 16 
        WHEN rand.r < 0.85 THEN 6 
        WHEN rand.r < 0.89 THEN 7 
        WHEN rand.r < 0.91 THEN 11 
        WHEN rand.r < 0.94 THEN 12 
        WHEN rand.r < 0.95 THEN 8 
        WHEN rand.r < 0.97 THEN 9 
        WHEN rand.r < 0.98 THEN 13 
        WHEN rand.r < 0.99 THEN 14 
        ELSE 10 
    END as kat_id
) k
ON CONFLICT (broj_na_sasija) DO NOTHING;
-----------------------------------------------------------------------------
--polnenje na boja_vozilo
CREATE TABLE Katalog_Boi (
    ime_boja varchar(30) PRIMARY KEY
);
WITH dostapni_boi AS (
    SELECT array_agg(ime_boja) as boi_niza, count(*) as vkupno 
    FROM Katalog_Boi
)
INSERT INTO Boja_vozilo (broj_na_sasija, boja)
SELECT 
    v.broj_na_sasija,
    db.boi_niza[floor(random() * db.vkupno) + 1]
FROM Vozilo v
CROSS JOIN dostapni_boi db
ON CONFLICT DO NOTHING;

WITH dostapni_boi AS (
    SELECT array_agg(ime_boja) as boi_niza, count(*) as vkupno 
    FROM Katalog_Boi
)
INSERT INTO Boja_vozilo (broj_na_sasija, boja)
SELECT 
    v.broj_na_sasija,
    db.boi_niza[floor(random() * db.vkupno) + 1]
FROM Vozilo v
CROSS JOIN dostapni_boi db
WHERE random() < 0.15
ON CONFLICT DO NOTHING;

DROP TABLE Katalog_Boi;
-------------------------------------------------------------------------
-- polnenje Vozacka_dozvola
INSERT INTO Vozacka_dozvola (broj_dozvola, datum_izdavanje, datum_vaznost_do, EMBG)
SELECT
    chr(floor(random() * 26 + 65)::int) || lpad(floor(random() * 9999999)::text, 7, '0') AS broj_dozvola,
    g.izdadena_na_datum AS datum_izdavanje,
    (g.izdadena_na_datum + INTERVAL '10 years')::date AS datum_vaznost_do,
    g.EMBG
FROM (
    SELECT 
        EMBG,
        CASE 
            WHEN datum_ragjanje > (CURRENT_DATE - INTERVAL '38 years')::DATE THEN
                (datum_ragjanje + INTERVAL '18 years')::DATE + (random() * (CURRENT_DATE - (datum_ragjanje + INTERVAL '18 years')::DATE))::INT
            ELSE
                CURRENT_DATE - (random() * 7300)::INT
        END AS izdadena_na_datum
    FROM Gragjanin 
    WHERE datum_ragjanje < (CURRENT_DATE - INTERVAL '18 years')
      AND random() < 0.65  
) g
ON CONFLICT (broj_dozvola) DO NOTHING;

select * from vozacka_dozvola vd 
------------------------------------------------------------------------------------------
--polnenje na kategorija vozacka dozvola
INSERT INTO Kategorija_Vozacka_dozvola (id_kategorija, broj_dozvola, datum_polaganje)
SELECT 
    CASE WHEN random() < 0.90 THEN 5 ELSE 4 END,
    broj_dozvola,
    datum_izdavanje - (random() * 30 + 1)::INT
FROM Vozacka_dozvola;

INSERT INTO Kategorija_Vozacka_dozvola (id_kategorija, broj_dozvola, datum_polaganje)
SELECT 
    4, broj_dozvola, datum_izdavanje - (random() * 60 + 1)::INT
FROM Vozacka_dozvola
WHERE random() < 0.15
ON CONFLICT DO NOTHING;

INSERT INTO Kategorija_Vozacka_dozvola (id_kategorija, broj_dozvola, datum_polaganje)
SELECT 
    7, broj_dozvola, datum_izdavanje - (random() * 100 + 1)::INT
FROM Vozacka_dozvola
WHERE random() < 0.10
ON CONFLICT DO NOTHING;

INSERT INTO Kategorija_Vozacka_dozvola (id_kategorija, broj_dozvola, datum_polaganje)
SELECT 
    12, broj_dozvola, datum_izdavanje - (random() * 10 + 1)::INT
FROM Vozacka_dozvola
WHERE random() < 0.05
ON CONFLICT DO NOTHING;

INSERT INTO Kategorija_Vozacka_dozvola (id_kategorija, broj_dozvola, datum_polaganje)
SELECT 
    9, broj_dozvola, datum_izdavanje - (random() * 100 + 1)::INT
FROM Vozacka_dozvola
WHERE random() < 0.03
ON CONFLICT DO NOTHING;

INSERT INTO Kategorija_Vozacka_dozvola (id_kategorija, broj_dozvola, datum_polaganje)
SELECT 
    15, broj_dozvola, datum_izdavanje - (random() * 300 + 1)::INT
FROM Vozacka_dozvola
WHERE random() < 0.08
ON CONFLICT DO NOTHING;

select * from kategorija_vozacka_dozvola kvd 

------------------------------------------------------------------------------
DROP TABLE IF EXISTS tmp_sopstvenost_stage;
CREATE TEMP TABLE tmp_sopstvenost_stage AS
WITH vozila AS (
    SELECT broj_na_sasija, row_number() OVER () - 1 AS rn
    FROM Vozilo
),
gragjani AS (
    SELECT EMBG, adresa, row_number() OVER () - 1 AS rn
    FROM Gragjanin
    WHERE datum_ragjanje < (CURRENT_DATE - INTERVAL '18 years')
),
cnt AS (SELECT count(*) AS cg FROM gragjani),
baza AS (
    -- tekoven sopstvenik
    SELECT
        v.broj_na_sasija,
        g.EMBG,
        g.adresa,
        'tekoven'::varchar AS uloga,
        (CURRENT_DATE - INTERVAL '4 years' - ((random()*120)::int) * INTERVAL '1 day')::DATE AS datum_steknuvanje,
        NULL::DATE AS datum_kraj
    FROM vozila v
    CROSS JOIN cnt
    JOIN gragjani g ON g.rn = (v.rn % cnt.cg)
    UNION ALL
    -- predhoden sopstvenik
    SELECT
        v.broj_na_sasija,
        g.EMBG,
        g.adresa,
        'predhoden'::varchar AS uloga,
        (CURRENT_DATE - INTERVAL '9 years' - ((random()*120)::int) * INTERVAL '1 day')::DATE AS datum_steknuvanje,
        (CURRENT_DATE - INTERVAL '4 years 6 months' - ((random()*120)::int) * INTERVAL '1 day')::DATE AS datum_kraj
    FROM vozila v
    CROSS JOIN cnt
    JOIN gragjani g ON g.rn = ((v.rn + 500000) % cnt.cg)
)
SELECT
    b.broj_na_sasija,
    b.EMBG,
    b.uloga,
    b.datum_steknuvanje,
    b.datum_kraj,
    COALESCE(
        CASE split_part(b.adresa, ' ', array_length(string_to_array(b.adresa, ' '), 1))
            WHEN 'Skopje'    THEN 'SK' WHEN 'Skopje,'    THEN 'SK'
            WHEN 'Bitola'    THEN 'BT' WHEN 'Bitola,'    THEN 'BT'
            WHEN 'Kumanovo'  THEN 'KU' WHEN 'Kumanovo,'  THEN 'KU'
            WHEN 'Tetovo'    THEN 'TE' WHEN 'Tetovo,'    THEN 'TE'
            WHEN 'Ohrid'     THEN 'OH' WHEN 'Ohrid,'     THEN 'OH'
            WHEN 'Shtip'     THEN 'ST' WHEN 'Shtip,'     THEN 'ST'
            WHEN 'Veles'     THEN 'VE' WHEN 'Veles,'     THEN 'VE'
            WHEN 'Prilep'    THEN 'PP' WHEN 'Prilep,'    THEN 'PP'
            WHEN 'Kocani'    THEN 'KO' WHEN 'Kocani,'    THEN 'KO'
            WHEN 'Gostivar'  THEN 'GV' WHEN 'Gostivar,'  THEN 'GV'
            WHEN 'Gevgelija' THEN 'GE' WHEN 'Gevgelija,' THEN 'GE'
            WHEN 'Strumica'  THEN 'SR' WHEN 'Strumica,'  THEN 'SR'
            ELSE (ARRAY['SK','BT','KU','TE','OH','ST','VE','PP','KO','GV','GE','SR'])[floor(random()*12)+1]
        END, 'SK') AS region
FROM baza b;


-- Sekoj zapis dobiva globalen oid i reden broj vo ramki na regionot (za unikatni tablici)
DROP TABLE IF EXISTS tmp_sopstvenost_plan;
CREATE TEMP TABLE tmp_sopstvenost_plan AS
SELECT
    row_number() OVER () AS oid,
    s.broj_na_sasija, s.EMBG, s.uloga, s.datum_steknuvanje, s.datum_kraj, s.region,
    lpad((s.rn_reg % 10000)::text, 4, '0') AS p_broj,
    chr((65 + (((s.rn_reg / 10000) / 26) % 26))::int) || chr((65 + ((s.rn_reg / 10000) % 26))::int) AS p_kod
FROM (
    SELECT *, (row_number() OVER (PARTITION BY region ORDER BY random()) - 1) AS rn_reg
    FROM tmp_sopstvenost_stage
) s;


--polnenje na sopstvenost
INSERT INTO Sopstvenost (EMBG, broj_na_sasija, datum_steknuvanje, datum_kraj)
SELECT EMBG, broj_na_sasija, datum_steknuvanje, datum_kraj
FROM tmp_sopstvenost_plan;

-- polnenje na registerska tablica
INSERT INTO Registerska_tablica (region, broj, kod)
SELECT region, p_broj, p_kod
FROM tmp_sopstvenost_plan;

DROP TABLE IF EXISTS tmp_plan_ids;
CREATE TEMP TABLE tmp_plan_ids AS
SELECT
    pl.oid, pl.uloga, pl.datum_steknuvanje, pl.datum_kraj,
    so.id_sopstvenost, rt.id_tablica
FROM tmp_sopstvenost_plan pl
JOIN Sopstvenost so
  ON so.EMBG = pl.EMBG
 AND so.broj_na_sasija = pl.broj_na_sasija
 AND so.datum_steknuvanje = pl.datum_steknuvanje
JOIN Registerska_tablica rt
  ON rt.region = pl.region
 AND rt.broj = pl.p_broj
 AND rt.kod = pl.p_kod;


-- polnenje na registracija
-- tekovni sopstvenosti 
INSERT INTO Registracija (id_sopstvenost, datum_na_izvadanje, datum_na_istekuvanje, id_tablica)
SELECT
    m.id_sopstvenost,
    (CURRENT_DATE - ((3 - g.g) || ' years')::INTERVAL)::DATE,
    (CURRENT_DATE - ((3 - g.g) || ' years')::INTERVAL + INTERVAL '1 year')::DATE,
    m.id_tablica
FROM tmp_plan_ids m
CROSS JOIN generate_series(0, 2) AS g(g)
WHERE m.uloga = 'tekoven';

-- predhodni sopstvenosti
INSERT INTO Registracija (id_sopstvenost, datum_na_izvadanje, datum_na_istekuvanje, id_tablica)
SELECT
    m.id_sopstvenost,
    (CURRENT_DATE - ((7 - g.g) || ' years')::INTERVAL)::DATE,
    (CURRENT_DATE - ((7 - g.g) || ' years')::INTERVAL + INTERVAL '1 year')::DATE,
    m.id_tablica
FROM tmp_plan_ids m
CROSS JOIN generate_series(0, 1) AS g(g)
WHERE m.uloga = 'predhoden';


DROP TABLE tmp_sopstvenost_stage;
DROP TABLE tmp_sopstvenost_plan;
DROP TABLE tmp_plan_ids;

select count(*) from Sopstvenost;
select count(*) from Registerska_tablica;
select count(*) from Registracija;


-----------------------------------------------------------------
--polnenje na Slucaj

CREATE TABLE Tip_Nastan (
    ime varchar(100) PRIMARY KEY
);

CREATE TABLE Tip_Zadaca (
    opis varchar(255) PRIMARY KEY
);




WITH dostapni_nastani AS (
    SELECT array_agg(ime) as n_arr, count(*) as n_cnt 
    FROM Tip_Nastan
)
INSERT INTO Slucaj (datum_otvaranje, status, tip_nastan)
SELECT 
    datum,
    CASE 
        WHEN datum < CURRENT_DATE - INTERVAL '6 months' THEN 
            CASE WHEN random() < 0.95 THEN 'Zatvoren' ELSE 'Vo tek' END
        ELSE 
            CASE 
                WHEN random() < 0.4 THEN 'Otvoren' 
                WHEN random() < 0.8 THEN 'Vo tek' 
                ELSE 'Zatvoren' 
            END
    END,
    dn.n_arr[floor(random() * dn.n_cnt) + 1]
FROM (
    SELECT (CURRENT_DATE - (random() * 1825)::INT) as datum
    FROM generate_series(1, 1000000)
) d
CROSS JOIN dostapni_nastani dn;

------------------------------------------------------------------
--polenje na Zadaca

WITH policaici AS (
    SELECT array_agg(EMBG_P) as p_arr, count(*) as p_cnt 
    FROM Policaec
),
dostapni_zadaci AS (
    SELECT array_agg(opis) as z_arr, count(*) as z_cnt 
    FROM Tip_Zadaca
)
INSERT INTO Zadaca (opis, status, EMBG_policaec, id_slucaj)
SELECT 
   
    dz.z_arr[(((s.id_slucaj + serija.broj) % dz.z_cnt) + 1)::INT],
    
    
    CASE 
        WHEN s.status = 'Zatvoren' THEN 
            CASE WHEN random() < 0.85 THEN 'Zavrsena' ELSE 'Otkazana' END
        WHEN s.status = 'Otvoren' THEN 
            CASE WHEN random() < 0.90 THEN 'Aktivna' ELSE 'Zavrsena' END
        WHEN s.status = 'Vo tek' THEN 
            CASE 
                WHEN random() < 0.40 THEN 'Zavrsena' 
                WHEN random() < 0.80 THEN 'Aktivna' 
                ELSE 'Otkazana' 
            END
    END,
    
    p.p_arr[floor(random() * p.p_cnt) + 1],
    s.id_slucaj
FROM Slucaj s
CROSS JOIN policaici p
CROSS JOIN dostapni_zadaci dz

CROSS JOIN LATERAL generate_series(1, (floor(random() * 3) + 3)::INT) AS serija(broj);


----------------------------------------------------------------------------------------



---------polnenje na zapisnik

CREATE TEMP TABLE tmp_v AS SELECT broj_na_sasija, row_number() over() - 1 as rn FROM Vozilo;
CREATE TEMP TABLE tmp_g AS SELECT EMBG, row_number() over() - 1 as rn FROM Gragjanin WHERE datum_ragjanje < (CURRENT_DATE - INTERVAL '18 years');


CREATE TEMP TABLE tmp_p AS 
SELECT 
    rv.EMBG_P, 
    row_number() over() - 1 as rn,
    CASE s.ime
        WHEN 'PS Centar' THEN ARRAY['Bulevar Partizanski Odredi', 'Ulica Makedonija', 'Dimitrije Cupovski', '11-ti Oktomvri']
        WHEN 'PS Karposh' THEN ARRAY['Bulevar Ilinden', 'Ulica Ruzveltova', 'Bulevar 8-mi Septemvri', 'Moskovska']
        WHEN 'PS Bit Pazar' THEN ARRAY['Bulevar Krste Misirkov', 'Ulica Cvetan Dimov', 'Plasticharska', 'Nikola Karev']
        WHEN 'PS Bitola 1' THEN ARRAY['Shirok Sokak', 'Bulevar 1-vi Maj', 'Ulica Prilepska', 'Partizanska']
        WHEN 'PS Prilep' THEN ARRAY['Bulevar Goce Delcev', 'Ulica Prilepski Braniteli', 'Aleksandar Makedonski']
        WHEN 'PS Kumanovo' THEN ARRAY['Treta Makedonska Udarna Brigada', '11-ti Oktomvri', 'Oktomvriska Revolucija']
        WHEN 'PS Tetovo' THEN ARRAY['Bulevar Ilindenska', 'Bulevar Vidoe Smilevski-Bato', 'Blagoja Toska']
        WHEN 'PS Ohrid' THEN ARRAY['Bulevar Turisticka', 'Ulica Partizanska', 'Kej Makedonija', 'Jane Sandanski']
        WHEN 'PS Shtip' THEN ARRAY['Ulica Vanco Prke', 'Bulevar Goce Delcev', 'Zeleznicka']
        WHEN 'PS Veles' THEN ARRAY['Ulica 8-mi Septemvri', 'Ulica Blagoj Gjorev', 'Alekso Demnievski']
        WHEN 'PS Strumica' THEN ARRAY['Bulevar Leninova', 'Blagoj Jankov Mucheto', 'Marshal Tito']
        WHEN 'PS Gostivar' THEN ARRAY['Bulevar Bratsva i Edinstva', 'Ulica JNA', 'Ilindenska']
        WHEN 'PS Kocani' THEN ARRAY['Marsal Tito', 'Teodosija Paunov', 'Dimitar Vlahov']
        WHEN 'PS Kavadarci' THEN ARRAY['Ilindenska', '7-mi Septemvri', 'Meto Dija']
        WHEN 'PS Gevgelija' THEN ARRAY['7-mi Noemvri', 'Sava Mihajlov', 'Zeleznicka']
        ELSE ARRAY['Glavna Ulica', 'Bulevar Osloboduvanje', 'Ulica Marsal Tito', 'Lokalni Patishta']
    END as dostapni_ulici
FROM Raboti_vo rv
JOIN Stanica s ON rv.id_stanica = s.id_stanica
WHERE rv.datum_do IS NULL;


CREATE TEMP TABLE tmp_c AS 
SELECT 
    (SELECT count(*) FROM tmp_v) as cv, 
    (SELECT count(*) FROM tmp_g) as cg, 
    (SELECT count(*) FROM tmp_p) as cp;


INSERT INTO Zapisnik (vreme, datum, lokacija, Potpis, status_zapisnik, id_slucaj, Vozilo_Broj_Sasija, EMBG_Prekrsuvach, EMBG_Policaec)
SELECT 
    (TIME '00:00:00' + random() * (TIME '23:59:59' - TIME '00:00:00'))::time as vreme,
    s.datum_otvaranje as datum,
    p.dostapni_ulici[floor(random() * array_length(p.dostapni_ulici, 1)) + 1] as lokacija,
    random() < 0.85 as Potpis,
    -- statusot na zapisnikot zavisi od statusot na slucajot kon koj pripaga:
    -- zatvoren slucaj => zapisnikot e skoro sekogas zatvoren; otvoren slucaj => zapisnikot e otvoren
    CASE 
        WHEN s.status = 'Zatvoren' THEN CASE WHEN random() < 0.95 THEN 'Zatvoren' ELSE 'Otvoren' END
        WHEN s.status = 'Vo tek'   THEN CASE WHEN random() < 0.50 THEN 'Zatvoren' ELSE 'Otvoren' END
        ELSE 'Otvoren'
    END as status_zapisnik,
    s.id_slucaj,
    v.broj_na_sasija,
    g.EMBG,
    p.EMBG_P
FROM (
    SELECT id_slucaj, datum_otvaranje, status, row_number() over() - 1 as rn 
    FROM Slucaj 
    CROSS JOIN generate_series(1, 15) 
    LIMIT 10000000
) s
CROSS JOIN tmp_c c
JOIN tmp_v v ON v.rn = (s.rn % c.cv)
JOIN tmp_g g ON g.rn = (s.rn % c.cg)
JOIN tmp_p p ON p.rn = (s.rn % c.cp);


DROP TABLE tmp_v;
DROP TABLE tmp_g;
DROP TABLE tmp_p;
DROP TABLE tmp_c;




---update na zapisnik za razlicen broj na zapisnici po policaec



DROP TABLE IF EXISTS tmp_pool_rabota;
CREATE TEMP TABLE tmp_pool_rabota AS
SELECT
    rv.EMBG_P,
    rv.datum_od,
    COALESCE(rv.datum_do, CURRENT_DATE) AS datum_do,
    trim(split_part(s.adresa, ',', 2)) AS grad,
    GREATEST(1, (power(random(), 4) * 30 + 1)::INT) AS tezina
FROM Raboti_vo rv
JOIN Stanica s ON rv.id_stanica = s.id_stanica;


DROP TABLE IF EXISTS tmp_zap_grad;
CREATE TEMP TABLE tmp_zap_grad AS
SELECT 
    z.id_na_zapisnik,
    trim(split_part(s.adresa, ',', 2)) AS grad
FROM Zapisnik z
JOIN Raboti_vo rv ON rv.EMBG_P = z.EMBG_Policaec
    AND z.datum BETWEEN rv.datum_od AND COALESCE(rv.datum_do, CURRENT_DATE)
JOIN Stanica s ON rv.id_stanica = s.id_stanica;


UPDATE Zapisnik z
SET EMBG_Policaec = (
    SELECT pr.EMBG_P
    FROM tmp_pool_rabota pr, tmp_zap_grad tz
    WHERE tz.id_na_zapisnik = z.id_na_zapisnik
      AND pr.grad = tz.grad
      AND z.datum BETWEEN pr.datum_od AND pr.datum_do
    ORDER BY -ln(1.0 - random()) / pr.tezina
    LIMIT 1
);

DROP TABLE tmp_pool_rabota;
DROP TABLE tmp_zap_grad;

------------------------------------------------------------------------------------
---polnenje stavka zapisnik



INSERT INTO Stavka_Zapisnik (reden_broj, id_na_zapisnik, id_na_prekrsok)
SELECT 
    row_number() OVER (PARTITION BY z.id_na_zapisnik) as reden_broj,
    z.id_na_zapisnik,
    p.id_prekrsok
FROM Zapisnik z
CROSS JOIN LATERAL (
   
    SELECT (floor(random() * 3 + (z.id_na_zapisnik * 0)) + 1)::INT as limit_val
) r
CROSS JOIN LATERAL (
  
    SELECT id_prekrsok
    FROM Prekrsok
    ORDER BY random() + (z.id_na_zapisnik * 0) 
    LIMIT r.limit_val
) p;


------------polnenje uplata

TRUNCATE TABLE Uplata RESTART IDENTITY;


INSERT INTO Uplata (iznos, status, datum_uplata, Uplatil_Gragjanin, id_zapisnik, nacin_plakanje)
SELECT 
    0, 
    
  
    CASE WHEN (id_na_zapisnik::bigint * 137) % 100 < 70 THEN 'Plateno' ELSE 'Neplateno' END,
    
    
    CASE WHEN (id_na_zapisnik::bigint * 137) % 100 < 70 
         THEN LEAST(datum + ((id_na_zapisnik::bigint * 113) % 30)::int, CURRENT_DATE) 
         ELSE NULL 
    END,
    
    EMBG_Prekrsuvach,
    id_na_zapisnik,
    
    CASE 
        WHEN (id_na_zapisnik::bigint * 137) % 100 < 45 THEN 'E-bankarstvo'
        WHEN (id_na_zapisnik::bigint * 137) % 100 < 65 THEN 'Platezna karticka'
        WHEN (id_na_zapisnik::bigint * 137) % 100 < 70 THEN 'Gotovo/Uplatnica'
        ELSE NULL 
    END
FROM Zapisnik;


DROP TABLE IF EXISTS tmp_suma_zapisnici;


CREATE TEMP TABLE tmp_suma_zapisnici AS
SELECT 
    sz.id_na_zapisnik,
    SUM(k.iznos_kazna) as vkupen_iznos
FROM Stavka_Zapisnik sz
JOIN Prekrsok p ON sz.id_na_prekrsok = p.id_prekrsok
JOIN Kazna k ON p.id_kazna = k.id_kazna
GROUP BY sz.id_na_zapisnik;

ALTER TABLE tmp_suma_zapisnici ADD PRIMARY KEY (id_na_zapisnik);

UPDATE Uplata u
SET iznos = ts.vkupen_iznos
FROM tmp_suma_zapisnici ts
WHERE u.id_zapisnik = ts.id_na_zapisnik;


DROP TABLE tmp_suma_zapisnici;