-- ===================== ZONA =====================
insert into Zona (ime)
select
    (array[
        'centralna zona',
        'rezidentska zona',
        'uchilishna zona',
        'bolnichka zona',
        'industriska zona',
        'komercijalna zona',
        'rekreativna zona',
        'avtopat zona',
        'peshachka zona',
        'turistichka zona'
    ])[floor(random()*10 + 1)]
from generate_series(1, 3000);

update Zona
set dozvolena_brzina =
        case
            when ime like 'uchilishna zona%' then 30
            when ime like 'bolnichka zona%' then 30
            when ime like 'peshachka zona%' then 20
            when ime like 'rezidentska zona%' then 40
            when ime like 'rekreativna zona%' then 30
            when ime like 'centralna zona%' then 50
            when ime like 'komercijalna zona%' then 50
            when ime like 'turistichka zona%' then 40
            when ime like 'industriska zona%' then 60
            when ime like 'avtopat zona%' then 80
            end
where 1 = 1;

update Zona
set opstina =
        case
            when ime like 'avtopat zona%' then
                (array [
                    'skopje','kumanovo','tetovo','veles','negotino',
                    'gevgelija','bitola','prilep','shtip','strumica',
                    'gostivar','ohrid','kochani','kavadarci'
                    ])[floor(random() * 13 + 1)]

            when ime like 'centralna zona%' then
                (array [
                    'centar','aerodrom','karpos','kisela voda','gjorce petrov',
                    'chair','butel','gazi baba','saraj','shuto orizari',
                    'ilinden','petrovec','sopishte','studenichani',
                    'skopje','kumanovo','tetovo','bitola','ohrid','prilep',
                    'strumica','veles','kavadarci','negotino','gostivar',
                    'struga','gevgelija','kocani','vinica','shtip'
                    ])[floor(random() * 30 + 1)]

            when ime like 'rezidentska zona%' then
                (array [
                    'aerodrom','karpos','kisela voda','gjorce petrov',
                    'butel','gazi baba','chair','shuto orizari',
                    'kumanovo','tetovo','bitola','prilep','veles',
                    'strumica','kochani','shtip','gostivar','kavadarci'
                    ])[floor(random() * 17 + 1)]

            when ime like 'uchilishna zona%' then
                (array [
                    'centar','aerodrom','karpos','kisela voda','gjorce petrov',
                    'chair','butel','gazi baba',
                    'kumanovo','tetovo','bitola','ohrid','prilep',
                    'strumica','veles','gostivar','struga','kochani','shtip','kavadarci'
                    ])[floor(random() * 19 + 1)]

            when ime like 'bolnichka zona%' then
                (array [
                    'centar','karpos','aerodrom',
                    'skopje','bitola','tetovo','kumanovo',
                    'ohrid','prilep','strumica','veles','shtip','kavadarci'
                    ])[floor(random() * 12 + 1)]

            when ime like 'industriska zona%' then
                (array [
                    'gazi baba','ilinden','petrovec',
                    'kumanovo','veles','kavadarci','negotino',
                    'prilep','bitola','tetovo','kochani','shtip','strumica'
                    ])[floor(random() * 13 + 1)]

            when ime like 'komercijalna zona%' then
                (array [
                    'centar','aerodrom','karpos','chair','gazi baba',
                    'skopje','kumanovo','tetovo','bitola','ohrid',
                    'prilep','strumica','veles','gostivar','struga',
                    'gevgelija','kochani','shtip','kavadarci'
                    ])[floor(random() * 18 + 1)]

            when ime like 'rekreativna zona%' then
                (array [
                    'karpos','gjorce petrov','saraj','sopishte',
                    'ohrid','struga','tetovo','gostivar',
                    'bitola','prilep','gevgelija','mavrovo','kavadarci'
                    ])[floor(random() * 12 + 1)]

            when ime like 'peshachka zona%' then
                (array [
                    'centar','aerodrom','karpos','chair',
                    'skopje','ohrid','bitola','tetovo',
                    'prilep','struga','kumanovo','shtip','kavadarci'
                    ])[floor(random() * 12 + 1)]

            when ime like 'turistichka zona%' then
                (array [
                    'ohrid','struga','gevgelija','bitola',
                    'krushevo','mavrovo','dojran','berovo',
                    'prilep','tetovo','skopje','kavadarci'
                    ])[floor(random() * 11 + 1)]
            end
where 1 = 1;

insert into Zona (ime)
select (array [
    'centralna zona',
    'rezidentska zona',
    'uchilishna zona',
    'bolnichka zona',
    'industriska zona',
    'komercijalna zona',
    'rekreativna zona',
    'avtopat zona',
    'peshachka zona',
    'turistichka zona'
    ])[floor(random() * 10 + 1)]
from generate_series(1, 3000);


-- ===================== LOKACIJA =====================
insert into Lokacija (zona_id, ulica, latitude, longitude)
select
    z.zona_id,
    case
        when z.opstina = 'centar' then
            (array[
                'dimitrie chupovski',
                'makedonija',
                'partizanski odredi',
                'mitropolit teodosij gologanov',
                'goce delchev'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'aerodrom' then
            (array[
                'jane sandanski',
                'srbija',
                'vasil karangeleski',
                'treta makedonska brigada',
                'video smilevski bato'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'karpos' then
            (array[
                'partizanski odredi',
                'ilindenska',
                'nikola tesla',
                'moskovska',
                'kozle'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'kisela voda' then
            (array[
                'boris trajkovski',
                'prvomajska',
                'naroden front',
                'hristo tatarchev',
                'anton popov'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'gjorce petrov' then
            (array[
                'gjorche petrov',
                'partizanski odredi',
                'makedonska vojska',
                'luka gerov',
                '4 juli'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'chair' then
            (array[
                'krste misirkov',
                'cvetan dimov',
                'braka cvetanovi',
                'kemal seyd',
                'dizhonska'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'butel' then
            (array[
                'butelska',
                'boca ivanova',
                'ferid murad',
                'kemal seyd',
                'radishanska'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'gazi baba' then
            (array[
                'aleksandar makedonski',
                'pero nakov',
                'finlandska',
                '16 makedonska brigada',
                'blagoja stefkovski'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'saraj' then
            (array[
                'saraj',
                'lokalna glavna',
                '1',
                '2',
                '3'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'shuto orizari' then
            (array[
                'indira gandhi',
                'nov zhivot',
                'shuto orizari',
                'bosna i hercegovina',
                'washingtonska'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'ilinden' then
            (array[
                'glavna',
                'industriska',
                '8',
                'marshal tito',
                '9'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'petrovec' then
            (array[
                'glavna',
                'marshal tito',
                'petrovec',
                '1',
                '2'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'sopishte' then
            (array[
                'sopishte',
                'glavna',
                'sonce',
                '1',
                '2'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'studenichani' then
            (array[
                'glavna',
                'studenichani',
                '1',
                '2',
                '3'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'skopje' then
            (array[
                'partizanski odredi',
                'jane sandanski',
                'goce delchev',
                'boris trajkovski',
                'krste misirkov'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'kumanovo' then
            (array[
                'oktomvriska revolucija',
                'treta makedonska udarna brigada',
                '11 oktomvri',
                'done bozhinov',
                'pero chakar'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'tetovo' then
            (array[
                'ilirija',
                'blagoja toska',
                'marshal tito',
                'vidoe smilevski bato',
                'goce delchev'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'bitola' then
            (array[
                'shirok sokak',
                'partizanska',
                'marsal tito',
                '4 noemvri',
                '8 septemvri'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'ohrid' then
            (array[
                'kej makedonija',
                'partizanska',
                'turistichka',
                'dejan vojvoda',
                'dimitar vlahov'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'prilep' then
            (array[
                'goce delchev',
                'partizanska',
                'aleksa shantij',
                'borsa',
                '11 oktomvri'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'strumica' then
            (array[
                'marshal tito',
                'goce delchev',
                'leninova',
                '24 oktomvri',
                'braka miladinovi'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'veles' then
            (array[
                'blagoj gjorev',
                '8 septemvri',
                'marshal tito',
                'dimitar vlahov',
                'kocho racin'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'kavadarci' then
            (array[
                '7 septemvri',
                'strasho pindzur',
                'marshal tito',
                'disanska',
                'edvard kardelj'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'negotino' then
            (array[
                'marshal tito',
                'partizanska',
                'industriska',
                'goce delchev',
                '11 oktomvri'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'gostivar' then
            (array[
                'braka gjinoski',
                'ilindenska',
                'marshal tito',
                'sedec',
                'goce delchev'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'struga' then
            (array[
                'marshal tito',
                'proleterski brigadi',
                'kej 8 noemvri',
                'vlado maleski',
                'partizanska'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'gevgelija' then
            (array[
                'marshal tito',
                '7 noemvri',
                'industriska',
                'goce delchev',
                'partizanska'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'kocani' then
            (array[
                'dimitar vlahov',
                'marshal tito',
                'strasho erbenov',
                'goce delchev',
                'kej na revolucijata'
            ])[floor(random()*5 + 1)]

        when z.opstina = 'vinica' then
            (array[
                'marshal tito',
                'partizanska',
                'ilindenska',
                'goce delchev',
                '8 septemvri'
            ])[floor(random()*5 + 1)]

        else 'glavna'
    end as ulica,

    round((41.2 + random() * 1.2)::numeric, 6) as latitude,
    round((20.4 + random() * 2.4)::numeric, 6) as longitude

from Zona z;

insert into Lokacija (zona_id, ulica, latitude, longitude)
select z.zona_id,
       case
           when z.opstina = 'centar' then
               (array [
                   'dimitrie chupovski',
                   'makedonija',
                   'partizanski odredi',
                   'mitropolit teodosij gologanov',
                   'goce delchev'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'aerodrom' then
               (array [
                   'jane sandanski',
                   'srbija',
                   'vasil karangeleski',
                   'treta makedonska brigada',
                   'video smilevski bato'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'karpos' then
               (array [
                   'partizanski odredi',
                   'ilindenska',
                   'nikola tesla',
                   'moskovska',
                   'kozle'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'kisela voda' then
               (array [
                   'boris trajkovski',
                   'prvomajska',
                   'naroden front',
                   'hristo tatarchev',
                   'anton popov'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'gjorce petrov' then
               (array [
                   'gjorche petrov',
                   'partizanski odredi',
                   'makedonska vojska',
                   'luka gerov',
                   '4 juli'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'chair' then
               (array [
                   'krste misirkov',
                   'cvetan dimov',
                   'braka cvetanovi',
                   'kemal seyd',
                   'dizhonska'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'butel' then
               (array [
                   'butelska',
                   'boca ivanova',
                   'ferid murad',
                   'kemal seyd',
                   'radishanska'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'gazi baba' then
               (array [
                   'aleksandar makedonski',
                   'pero nakov',
                   'finlandska',
                   '16 makedonska brigada',
                   'blagoja stefkovski'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'saraj' then
               (array [
                   'saraj',
                   'lokalna glavna',
                   '1',
                   '2',
                   '3'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'shuto orizari' then
               (array [
                   'indira gandhi',
                   'nov zhivot',
                   'shuto orizari',
                   'bosna i hercegovina',
                   'washingtonska'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'ilinden' then
               (array [
                   'glavna',
                   'industriska',
                   '8',
                   'marshal tito',
                   '9'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'petrovec' then
               (array [
                   'glavna',
                   'marshal tito',
                   'petrovec',
                   '1',
                   '2'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'sopishte' then
               (array [
                   'sopishte',
                   'glavna',
                   'sonce',
                   '1',
                   '2'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'studenichani' then
               (array [
                   'glavna',
                   'studenichani',
                   '1',
                   '2',
                   '3'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'skopje' then
               (array [
                   'partizanski odredi',
                   'jane sandanski',
                   'goce delchev',
                   'boris trajkovski',
                   'krste misirkov'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'kumanovo' then
               (array [
                   'oktomvriska revolucija',
                   'treta makedonska udarna brigada',
                   '11 oktomvri',
                   'done bozhinov',
                   'pero chakar'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'tetovo' then
               (array [
                   'ilirija',
                   'blagoja toska',
                   'marshal tito',
                   'vidoe smilevski bato',
                   'goce delchev'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'bitola' then
               (array [
                   'shirok sokak',
                   'partizanska',
                   'marsal tito',
                   '4 noemvri',
                   '8 septemvri'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'ohrid' then
               (array [
                   'kej makedonija',
                   'partizanska',
                   'turistichka',
                   'dejan vojvoda',
                   'dimitar vlahov'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'prilep' then
               (array [
                   'goce delchev',
                   'partizanska',
                   'aleksa shantij',
                   'borsa',
                   '11 oktomvri'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'strumica' then
               (array [
                   'marshal tito',
                   'goce delchev',
                   'leninova',
                   '24 oktomvri',
                   'braka miladinovi'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'veles' then
               (array [
                   'blagoj gjorev',
                   '8 septemvri',
                   'marshal tito',
                   'dimitar vlahov',
                   'kocho racin'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'kavadarci' then
               (array [
                   '7 septemvri',
                   'strasho pindzur',
                   'marshal tito',
                   'disanska',
                   'edvard kardelj'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'negotino' then
               (array [
                   'marshal tito',
                   'partizanska',
                   'industriska',
                   'goce delchev',
                   '11 oktomvri'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'gostivar' then
               (array [
                   'braka gjinoski',
                   'ilindenska',
                   'marshal tito',
                   'sedec',
                   'goce delchev'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'struga' then
               (array [
                   'marshal tito',
                   'proleterski brigadi',
                   'kej 8 noemvri',
                   'vlado maleski',
                   'partizanska'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'gevgelija' then
               (array [
                   'marshal tito',
                   '7 noemvri',
                   'industriska',
                   'goce delchev',
                   'partizanska'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'kocani' then
               (array [
                   'dimitar vlahov',
                   'marshal tito',
                   'strasho erbenov',
                   'goce delchev',
                   'kej na revolucijata'
                   ])[floor(random() * 5 + 1)]

           when z.opstina = 'vinica' then
               (array [
                   'marshal tito',
                   'partizanska',
                   'ilindenska',
                   'goce delchev',
                   '8 septemvri'
                   ])[floor(random() * 5 + 1)]

           else 'glavna'
           end                                    as ulica,

       round((41.2 + random() * 1.2)::numeric, 6) as latitude,
       round((20.4 + random() * 2.4)::numeric, 6) as longitude

from Zona z;


-- ===================== TIPKAMERA =====================
insert into TipKamera (ime, opis, max_opseg, multifunkcionalna)
values ('speed camera', 'kamera za detekcija na prekoracena brzina', 300, false),
       ('red light camera', 'kamera za detekcija na pominuvanje na crveno svetlo', 150, false),
       ('surveillance camera', 'kamera za opshto video nadgleduvanje na raskrsnici i javni povrshini', 200, false),
       ('anpr camera', 'kamera za avtomatsko prepoznavanje na registraciski tablichki', 100, true),
       ('multifunction traffic camera', 'kamera shto kombinira brzina, tablichki i opsto nadgleduvanje', 350, true);


-- ===================== KAMERA =====================
insert into Kamera (datum_instalacija, status, lokacija_id, tip_kamera_id)
select current_date - ((random() * 1500)::int) as datum_instalacija,

       case
           when random() < 0.90 then 'aktivna'
           else 'neaktivna'
           end                                 as status,

       l.lokacija_id,

       case
           when z.ime like 'avtopat zona%' then 1
           when z.ime like 'uchilishna zona%' then
               case
                   when random() < 0.7 then 1
                   else 3
                   end
           when z.ime like 'peshachka zona%' then 3
           when z.ime like 'centralna zona%' then
               case
                   when random() < 0.6 then 3
                   else 2
                   end
           when z.ime like 'komercijalna zona%' then 3
           when z.ime like 'industriska zona%' then 3
           when z.ime like 'bolnichka zona%' then 3
           when z.ime like 'rezidentska zona%' then 3
           when z.ime like 'rekreativna zona%' then 3
           when z.ime like 'turistichka zona%' then 3
           end                                 as tip_kamera_id

from Lokacija l
         join Zona z on l.zona_id = z.zona_id;


-- ===================== GRAGJANIN =====================
ALTER TABLE Gragjanin
ADD COLUMN pol CHAR(1);

ALTER TABLE Gragjanin
ADD CONSTRAINT CHK_Gragjanin_Pol
CHECK (pol IN ('M', 'F'));

WITH female_data AS (

    SELECT
        embg,

ARRAY[
'Ана','Марија','Јована','Тамара','Елена','Александра','Ивана','Сара','Мила','Теодора',
'Софија','Хана','Нина','Мартина','Ангела','Кристина','Тијана','Леона','Катерина','Симона',
'Бојана','Даниела','Моника','Лара','Илина','Ирена','Јасмина','Маја','Михаела','Анастасија',
'Емилија','Викторија','Лидија','Теа','Фатиме','Емина','Аделина','Анита','Анкица','Антонија',
'Билјана','Бисера','Благица','Борјана','Вера','Вероника','Весна','Виолета','Гордана','Дара',
'Даринка','Деница','Десислава','Димитра','Дина','Драгица','Есма','Жаклина','Жанета','Живка',
'Злата','Зорица','Зорка','Ивона','Искра','Јана','Јелена','Јорданка','Калина','Кети',
'Клара','Лила','Лилјана','Лина','Марина','Марта','Матеја','Меланија','Мирјана','Наде',
'Надежда','Оливера','Павлинка','Петра','Радмила','Радослава','Рената','Роза','Росица','Сашка',
'Светлана','Силвана','Славица','Стефанија','Сузана','Тања','Фани','Флора','Христина','Агнеса',
'Адела','Адријана','Алма','Амела','Аница','Анѓела','Арбена','Асја','Бадија','Бети',
'Бисерка','Бранка','Валентина','Васка','Василија','Ведрана','Вилма','Габи',
'Галина','Галена','Глорија','Деа','Дивна','Добрилка','Доротеа','Ева','Евгенија',
'Едита','Екатерина','Елисавета','Елма','Елмира','Емина','Ерика','Ермира',
'Жана','Здравка','Златка','Ива','Ина','Илина','Инес','Ирма','Јагода','Јулија',
'Камелија','Каролина','Касија','Клара','Кристина','Круна','Лана','Лејла','Ленче','Леонида',
'Ливија','Љубица','Магдалена','Мариана','Марика','Мелиса','Мерита','Милена','Милка','Мирела',
'Наташа','Невена','Нела','Нермина','Олга','Орхидеја','Паола','Пепа','Перса',
'Рајна','Рамона','Ребека','Рина','Сабина','Сања','Селма','Силвија','Снежана','Соња',
'Спомена','Стаменка','Тамина','Татјана','Тереза','Тина','Убавка','Фиданка','Хелена','Цвета',
'Цветанка','Џенита','Шпреса','Аурора','Блажа','Ведрана','Габијела',
'Елвира','Жанин','Инеса','Луна','Мирна','Нора','Офелија',
'Роксана','Сандра','Сузи','Фемија','Хатиџе','Цеца','Џулија','Шејла','Симона' , 'Миланка' ,'Оља' ,'Бојана' , 'Миа' ,'Делфина' ,'Гордана' ,'Христина' , 'Мариа' ,'Марија' ,'Нена' ,'Иларија' , 'Тамара' , 'Дона' , 'Мила' ,'Љубка' , 'Леона'
]
        AS female_names,

ARRAY[
'Иванова','Петрова','Стојанова','Николова','Јованова','Георгиева','Димитрова','Ангелова','Костова','Тодорова',
'Ристова','Спасова','Милева','Трајкова','Илиева','Попова','Стефанова','Павлова','Маркова','Кирева',
'Атанасова','Богданова','Величкова','Митрева','Цветкова','Христова','Лазарова','Георгиевска','Колева','Димкова',
'Младеновска','Наумова','Панова','Радева','Савева','Симонова','Тасевска','Узунова','Филиповска','Чолакова',
'Шопова','Арсова','Бајрамов','Блажевска','Василева','Гаврилова','Давидова','Ефтимова','Живкова','Златева','Јакимова','Караџова','Личеноска','Малиновска','Неделкова','Огненовска','Пандевска','Рангелова','Серафимова',
'Темелкова','Ќосевска','Апостолова','Бошковска','Велјаноска','Глигоровска','Деспотовска','Жупанова','Захариева',
'Иваноска','Јанкуловска','Караманова','Лозановска','Маневска','Наковска','Орданоска','Пешевска','Ристовска','Соколовска',
'Тренчевска','Угриновска','Фиданоска','Цековска','Чупеска','Шуманова','Абдулаи','Адеми','Алиу','Бајрами',
'Беќири','Бериша','Весели','Даути','Имери','Исмаили','Красниќи','Мустафа','Османи','Реџепи',
'Салиу','Сулејмани','Таири','Фејзула','Хасани','Шаќири','Адемовска','Бајрамовска','Бектеши','Билалова',
'Велиу','Гаши','Даути','Емини','Зеќири','Ибраими','Јусуфи','Кадриу','Лимани','Мемети',
'Нухиу','Османова','Пајазити','Рамадани','Селими','Тахири','Усеини','Фетаи','Халили','Џафери',
'Шабани','Андоновска','Бошњакова','Вељковска','Грујовска','Доневска','Евтимова','Жежовска','Зографска','Игњатова',
'Јосифовска','Китаноска','Луковска','Мицевска','Несторова','Петрушевска','Ризова','Славевска','Томовска','Урошевска',
'Фрчковска','Цветаноска','Чадиковска','Шутевска','Алексова','Бабунска','Вангеловска','Глигоровска','Дамјановска','Еленовска',
'Живановска','Здравковска','Илиевска','Јовановска','Караџовска','Лазаревска','Митановска','Николовска','Оровчанец','Павлеска',
'Ристеска','Стојческа','Тодоровска','Цветковска','Чуповска','Асанова','Јанчева', 'Пецуровски', 'Данчевска'
] AS female_surnames,

        abs(hashtext(embg)::bigint) AS h

    FROM gragjanin

    -- FEMALE
    WHERE substring(embg from 10 for 3)::int >= 500
)

UPDATE gragjanin g
SET
    ime =
        fd.female_names[
            (fd.h % array_length(fd.female_names, 1)) + 1
        ],

    prezime =
        fd.female_surnames[
            ((fd.h / 17) % array_length(fd.female_surnames, 1)) + 1
        ]

FROM female_data fd
WHERE g.embg = fd.embg;

WITH male_data AS (
    SELECT
        embg,
ARRAY[
'Александар','Андреј','Антонио','Бојан','Виктор','Владимир','Горан','Давид','Дејан','Димитар',
'Драган','Емил','Иван','Игор','Јован','Кристијан','Лазар','Леон','Марко','Мартин',
'Никола','Огнен','Павле','Петар','Стефан','Трајан','Филип','Христијан','Али','Самир',
'Аце','Благој','Васко','Глигор','Дане','Ернест','Жарко','Зоран','Иле','Јордан',
'Кирил','Љупчо','Миле','Наум','Орце','Пеце','Раде','Сашо','Тони','Урош',
'Филипче','Цане','Чеде','Џељал','Аднан','Арбен','Бесим','Влатко','Гоце',
'Горанче','Дамјан','Димче','Елвир','Живко','Здравко','Ибрахим','Јане','Кочо','Круме',
'Лазе','Марио','Ненад','Орхан','Перо','Ристо','Слободан','Томе','Ќамил','Фарук',
'Цветан','Џеват','Шенол','Андон','Бобан','Ване','Гаврил','Драги','Енес','Златко','Илија','Јовица','Костадин','Лукас','Мирко','Никче','Оливер','Панче',
'Роберт','Симеон','Тихомир','Ќире','Филипе','Цветко','Чедомир','Џанер','Шефкет','Алекс',
'Алек','Ангел','Бојче','Венко','Глигор','Доне','Елтон','Жељко','Зоки','Игорче',
'Јоце','Кире','Љубе','Митко','Никодин','Омер','Павел','Ранко','Славе','Трајче',
'Цветанче','Џевдет','Шакир','Агим','Бајрам','Веби','Гзим','Јусуф','Кадри','Љатиф','Мемет','Неџат','Осман',
'Рамадан','Сеад','Таир','Урим','Фадил','Хасан','Џевад','Шабан','Ацо','Бранко',
'Викентиј','Глигориј','Доне','Ефтим','Жарко','Зоки','Илчо','Јованче','Кирче',
'Милан','Огнен','Петар','Ратко','Станко','Трајко','Киро','Фоте','Цане',
'Чедомир','Џоле','Андреа','Бојанче','Велко','Горанчо','Димо','Ермал',
'Златан','Јусуф','Кире','Лазар','Мирослав','Николче','Ордан','Ристе', 'Ристо', 'Дарио', 'Саше','Томислав','Ќемал','Филипчо','Цанко',
'Валон','Гани','Дритон','Ервин','Зоранчо','Илми','Јахја','Кастриот',
'Борис','Видое','Гавро','Дончо','Евгениј','Жаре','Злате','Ице',
'Јаким','Калин','Лазо','Мартин','Петре','Раде','Сотир','Трајче',
'Урош','Цветко','Џино','Шенко','Ариф','Бакир','Вебијан','Гафур',
'Ване','Горан','Диме','Елдар','Желимир','Зоран','Јордан','Кирил','Лука',
'Милан','Огнен','Петко','Радован','Славко','Тихо','Кирил', 'Димитар', 'Ненад', 'Давид', 'Стефан', 'Саве', 'Влатко', 'Дарко', 'Иван', 'Игор', 'Јован', 'Спасе', 'Тони', 'Адам', 'Никола', 'Матеј', 'Марко', 'Ѓорѓи', 'Филип', 'Борјан', 'Максим', 'Милан', 'Симон', 'Давор', 'Леонид', 'Бошко', 'Костадин', 'Живко', 'Панче', 'Андреј', 'Бобан'
 ]

 AS male_names,

      ARRAY[
'Иванов','Петров','Стојанов','Николов','Јованов','Георгиев','Димитров','Ангелов','Костов','Тодоров',
'Ристов','Спасов','Милев','Трајков','Илиев','Попов','Стефанов','Павлов','Марков','Кирев',
'Атанасов','Богданов','Величков','Митрев','Цветков','Христов','Лазаров','Георгиевски','Колев','Димков',
'Младеновски','Наумов','Панов','Радев','Савев','Симонов','Тасевски','Узунов','Филиповски','Чолаков',
'Шопов','Арсов','Бајрамов','Блажевски','Василев','Гаврилов','Давидов','Ефтимов','Живков','Златев',
'Исаев','Јакимов','Караџов','Личеноски','Малиновски','Неделков','Огненовски','Пандевски','Рангелов','Серафимов',
'Темелков','Ќосевски','Апостолов','Бошковски','Велјановски','Глигоровски','Деспотовски','Елезовски','Жупанов','Захариев',
'Иваноски','Јанкуловски','Караманов','Лозановски','Маневски','Наковски','Орданоски','Пешевски','Ристовски','Соколовски',
'Тренчевски','Угриновски','Фиданоски','Цековски','Чупески','Шуманов','Абдулаи','Адеми','Алиу','Бајрами',
'Беќири','Бериша','Весели','Даути','Османи','Реџепи',
'Рамадани','Селими','Тахири','Усеини','Бошњаков',
'Вељковски','Грујовски','Доневски','Евтимов','Жежовски','Зографски','Игњатов','Јосифовски','Китаноски','Луковски',
'Мицевски','Несторов','Петрушевски','Ризов','Славевски','Томовски','Урошевски','Фрчковски','Цветаноски','Чадиковски',
'Шутевски','Алексов','Бабунски','Вангеловски','Дамјановски','Еленовски','Живановски','Здравковски','Илиевски','Јовановски',
'Караџовски','Лазаревски','Митановски','Николовски','Павлески','Ристески','Стојчески','Тодоровски','Фотевски','Цветковски',
'Чуповски','Шумански','Асанов','Бејтула','Велков','Георгиевски','Деспотски','Ефтимовски','Живковски','Златков',
'Илиоски','Јакимоски','Калинов','Лазароски','Милошевски','Никодинов','Орданов','Петков','Радески','Станков',
'Трајчески','Урошев','Филипов','Цанев','Јанчев',
'Реџепов',
'Сабри','Тефик','Улви','Фатон','Хамди','Шкелзен','Алексиев','Борисов','Видоев','Гавров',
'Дончев','Евгениев', 'Јорданов','Калиновски','Лазов','Мартинов','Неделчев','Петрески','Сотиров'
] AS male_surnames,

        abs(hashtext(embg)::bigint) AS h

    FROM gragjanin

    -- mashki EMBG
    WHERE substring(embg from 10 for 3)::int < 500
)

UPDATE gragjanin g
SET
    ime =
        md.male_names[
            (md.h % array_length(md.male_names, 1)) + 1
        ],

    prezime =
        md.male_surnames[
            ((md.h / 17) % array_length(md.male_surnames, 1)) + 1
        ]

FROM male_data md
WHERE g.embg = md.embg;


-- ===================== SOPSTVENIK =====================
INSERT INTO Sopstvenik (embg)
SELECT embg
FROM Gragjanin
ORDER BY random()
LIMIT 1200350;


-- ===================== VOZACKA =====================
INSERT INTO Vozacka
(vozacki_broj, datum_izdavanje, datum_istekuvanje, status, embg)
SELECT
    'MK-' || LPAD(row_number() OVER ()::TEXT, 7, '0') AS vozacki_broj,
    datum_izdavanje,
    datum_istekuvanje,

    CASE
        WHEN rn <= 0.93 THEN 'aktivna'
        WHEN rn <= 0.96 THEN 'istecena'
        ELSE 'suspendirana'
    END AS status,

    embg

FROM
(
    SELECT
        embg,
        datum_izdavanje,

        datum_izdavanje +
        CASE
            WHEN random() < 0.5 THEN INTERVAL '5 years'
            ELSE INTERVAL '10 years'
        END AS datum_istekuvanje,

        random() AS rn

    FROM
    (
        SELECT
            s.embg,

            (
                (g.datum_ragjanje + INTERVAL '18 years')::DATE
                +
                FLOOR(
                    random() *
                    (
                        CURRENT_DATE
                        - (g.datum_ragjanje + INTERVAL '18 years')::DATE
                    )
                )::INT
            ) AS datum_izdavanje

        FROM Sopstvenik s
        JOIN Gragjanin g
            ON s.embg = g.embg

        WHERE
            g.datum_ragjanje <= CURRENT_DATE - INTERVAL '18 years'

        ORDER BY random()
        LIMIT 900000
    ) x
) y;


-- ===================== KATEGORIJA =====================
INSERT INTO Kategorija (ime, opis)
VALUES
('AM', 'Kategorija za upravuvanje so mopedi, skuteri i lesni motorni vozila so pomala zafatnina i ogranicena maksimalna brzina. Ovaa kategorija najchesto se koristi za gradski prevoz i pochetno iskustvo vo soobrakjajot.'),

('A1', 'Kategorija za upravuvanje so lesni motocikli do 125 cm3 i odredena maksimalna mokjnost. Nameneta e za vozachi koi pocnuvaat so upravuvanje motocikli so pomala sila i polesna kontrola.'),

('A2', 'Kategorija za motocikli so sredna mokjnost i pogolema zafatnina, nameneta za vozachi so prethodno iskustvo i podgotvenost za upravuvanje posilni motorni vozila.'),

('A', 'Kategorija za upravuvanje so site tipovi motocikli bez ogranicuvanje na mokjnost ili zafatnina. Ovaa kategorija ovozmozhuva upravuvanje so sportski i profesionalni motocikli.'),

('B1', 'Kategorija za polesni chetiricikli i kompaktni motorni vozila nameneti za gradska upotreba i prevoz na mal broj patnici.'),

('B', 'Najchesto koristena kategorija koja ovozmozhuva upravuvanje so patnichki avtomobili do odredena maksimalna masa i vozila nameneti za sekojdneven privaten ili sluzhben prevoz.'),

('BE', 'Kategorija koja ovozmozhuva upravuvanje so vozila od kategorija B vo kombinacija so prikolka ili dopolnitelno prikacheno vozilo so pogolema masa.'),

('C1', 'Kategorija za upravuvanje so polesni tovarni vozila i kamioni so sredna nosivost, nameneti za lokalen i regionalen transport na roba.'),

('C1E', 'Kategorija za polesni tovarni vozila od C1 vo kombinacija so prikolka, najchesto koristena vo komercijalen i distributiven transport.'),

('C', 'Kategorija za upravuvanje so teski tovarni vozila i kamioni nameneti za profesionalen transport na roba na podolgi relacii.'),

('CE', 'Kategorija za teski tovarni vozila vo kombinacija so golemi prikolki i shleperi, nameneta za profesionalni vozachi vo megjunaroden i industriski transport.'),

('D1', 'Kategorija za upravuvanje so pomali avtobusi i kombinja nameneti za prevoz na pomal broj patnici vo lokalen ili privaten transport.'),

('D1E', 'Kategorija za pomali avtobusi vo kombinacija so prikolka ili dopolnitelna oprema za transport.'),

('D', 'Kategorija za upravuvanje so avtobusi i drugi vozila nameneti za javen i organiziran prevoz na pogolem broj patnici.'),

('DE', 'Kategorija za avtobusi so golema prikolka ili dopolnitelen transporten kapacitet, najchesto koristena vo profesionalen transport.'),

('F', 'Kategorija za upravuvanje so traktori, zemjodelski mashini i drugi rabotni vozila koi se koristat vo zemjodelstvo i industrija.'),

('G', 'Kategorija za specijalni rabotni mashini, gradezhna mehanizacija i terenski vozila nameneti za profesionalna upotreba vo posebni uslovi.');


-- ===================== VOZACKA_KATEGORIJA =====================
INSERT INTO Vozacka_Kategorija (vozacka_id, kategorija_id)
-- сите имаат B
SELECT v.vozacka_id, k.kategorija_id
FROM Vozacka v
         JOIN Kategorija k ON k.ime = 'B'

UNION

-- 25% имаат AM
SELECT v.vozacka_id, k.kategorija_id
FROM Vozacka v
         JOIN Kategorija k ON k.ime = 'AM'
WHERE random() < 0.25

UNION

-- 8% имаат A1
SELECT v.vozacka_id, k.kategorija_id
FROM Vozacka v
         JOIN Sopstvenik s ON v.embg = s.embg
         JOIN Gragjanin g ON s.embg = g.embg
         JOIN Kategorija k ON k.ime = 'A1'
WHERE date_part('year', age(current_date, g.datum_ragjanje)) >= 16
  AND random() < 0.08

UNION

-- 5% имаат A2
SELECT v.vozacka_id, k.kategorija_id
FROM Vozacka v
         JOIN Sopstvenik s ON v.embg = s.embg
         JOIN Gragjanin g ON s.embg = g.embg
         JOIN Kategorija k ON k.ime = 'A2'
WHERE date_part('year', age(current_date, g.datum_ragjanje)) >= 18
  AND random() < 0.05

UNION

-- 4% имаат A
SELECT v.vozacka_id, k.kategorija_id
FROM Vozacka v
         JOIN Sopstvenik s ON v.embg = s.embg
         JOIN Gragjanin g ON s.embg = g.embg
         JOIN Kategorija k ON k.ime = 'A'
WHERE date_part('year', age(current_date, g.datum_ragjanje)) >= 24
  AND random() < 0.04

UNION

-- 10% имаат BE
SELECT v.vozacka_id, k.kategorija_id
FROM Vozacka v
         JOIN Kategorija k ON k.ime = 'BE'
WHERE random() < 0.10

UNION

-- 6% имаат C
SELECT v.vozacka_id, k.kategorija_id
FROM Vozacka v
         JOIN Sopstvenik s ON v.embg = s.embg
         JOIN Gragjanin g ON s.embg = g.embg
         JOIN Kategorija k ON k.ime = 'C'
WHERE date_part('year', age(current_date, g.datum_ragjanje)) >= 21
  AND random() < 0.06

UNION

-- 2% имаат D
SELECT v.vozacka_id, k.kategorija_id
FROM Vozacka v
         JOIN Sopstvenik s ON v.embg = s.embg
         JOIN Gragjanin g ON s.embg = g.embg
         JOIN Kategorija k ON k.ime = 'D'
WHERE date_part('year', age(current_date, g.datum_ragjanje)) >= 24
  AND random() < 0.02

UNION

-- 4% имаат F
SELECT v.vozacka_id, k.kategorija_id
FROM Vozacka v
         JOIN Kategorija k ON k.ime = 'F'
WHERE random() < 0.04

UNION

-- 3% имаат G
SELECT v.vozacka_id, k.kategorija_id
FROM Vozacka v
         JOIN Kategorija k ON k.ime = 'G'
WHERE random() < 0.03;


-- ===================== TIP =====================
INSERT INTO Tip (ime)
VALUES
('Sedan'),
('Hecbek'),
('Karavan'),
('Kupe'),
('Kabriolet'),
('SUV'),
('Krosover'),
('Pikap'),
('Miniven'),
('Kombi'),
('Van'),
('Limuzina'),
('Sportski avtomobil'),
('Elektrichen avtomobil'),
('Hibriden avtomobil'),
('Gradski avtomobil'),
('Terensko vozilo'),
('Kamion'),
('Shleper'),
('Avtobus'),
('Minibus'),
('Motocikl'),
('Skuter'),
('Moped'),
('Traktor'),
('Rabotna mashina'),
('Policisko vozilo'),
('Ambulantno vozilo'),
('Protivpozharno vozilo');


-- ===================== VOZILO =====================
ALTER TABLE vozilo
ADD COLUMN registarska_oznaka VARCHAR(20);

ALTER TABLE vozilo
ADD CONSTRAINT UQ_Vozilo_Registarska
UNIQUE (registarska_oznaka);

ALTER TABLE vozilo
ADD CONSTRAINT CHK_Vozilo_Registarska
CHECK (
    registarska_oznaka IS NULL
    OR TRIM(registarska_oznaka) <> ''
);

ALTER TABLE vozilo
ADD COLUMN boja VARCHAR(50);

ALTER TABLE vozilo
ADD CONSTRAINT CHK_Vozilo_Boja
CHECK (
    boja IS NULL
    OR TRIM(boja) <> ''
);

INSERT INTO vozilo
(marka, broj_sasija, model, registarska_oznaka, boja, tip_id)

WITH params AS (
    SELECT 1400000 AS broj_vozila
),

generated AS (
    SELECT
        gs AS rn,
        random() AS r
    FROM params, generate_series(1, params.broj_vozila) gs
),

chosen_tip AS (
    SELECT
        rn,
        CASE
            WHEN r < 0.18 THEN 'Hecbek'
            WHEN r < 0.34 THEN 'Sedan'
            WHEN r < 0.47 THEN 'SUV'
            WHEN r < 0.57 THEN 'Krosover'
            WHEN r < 0.64 THEN 'Karavan'
            WHEN r < 0.70 THEN 'Gradski avtomobil'
            WHEN r < 0.76 THEN 'Kombi'
            WHEN r < 0.81 THEN 'Van'
            WHEN r < 0.86 THEN 'Dostavno vozilo'
            WHEN r < 0.90 THEN 'Kamion'
            WHEN r < 0.925 THEN 'Shleper'
            WHEN r < 0.945 THEN 'Motocikl'
            WHEN r < 0.960 THEN 'Skuter'
            WHEN r < 0.972 THEN 'Avtobus'
            WHEN r < 0.982 THEN 'Minibus'
            WHEN r < 0.988 THEN 'Policisko vozilo'
            WHEN r < 0.993 THEN 'Ambulantno vozilo'
            WHEN r < 0.996 THEN 'Protivpozharno vozilo'
            WHEN r < 0.998 THEN 'Diplomatsko vozilo'
            ELSE 'Rabotna mashina'
        END AS tip_ime
    FROM generated
),

with_tip AS (
    SELECT
        ct.rn,
        ct.tip_ime,
        t.tip_id
    FROM chosen_tip ct
    JOIN tip t ON t.ime = ct.tip_ime
),

vehicle_data AS (
    SELECT
        wt.rn,
        wt.tip_ime,
        wt.tip_id,
        ROW_NUMBER() OVER (PARTITION BY wt.tip_ime ORDER BY wt.rn) AS type_seq,

        CASE
            WHEN wt.tip_ime IN ('Hecbek','Sedan','SUV','Krosover','Karavan','Gradski avtomobil') THEN
                (ARRAY[
                    'Volkswagen|Golf',
                    'Volkswagen|Passat',
                    'Opel|Astra',
                    'Opel|Corsa',
                    'Toyota|Corolla',
                    'Toyota|Yaris',
                    'Ford|Focus',
                    'Renault|Clio',
                    'Peugeot|308',
                    'Skoda|Octavia',
                    'Hyundai|Tucson',
                    'Kia|Sportage',
                    'Dacia|Duster',
                    'Audi|A4',
                    'BMW|320'
                ])[(floor(random()*15)+1)::int]

            WHEN wt.tip_ime IN ('Kombi','Van','Dostavno vozilo') THEN
                (ARRAY[
                    'Volkswagen|Transporter',
                    'Ford|Transit',
                    'Mercedes-Benz|Sprinter',
                    'Fiat|Ducato',
                    'Renault|Master',
                    'Mercedes-Benz|Vito'
                ])[(floor(random()*6)+1)::int]

            WHEN wt.tip_ime IN ('Kamion','Shleper') THEN
                (ARRAY[
                    'MAN|TGX',
                    'Mercedes-Benz|Actros',
                    'Volvo|FH',
                    'Scania|R Series',
                    'DAF|XF',
                    'Iveco|Stralis'
                ])[(floor(random()*6)+1)::int]

            WHEN wt.tip_ime IN ('Avtobus','Minibus') THEN
                (ARRAY[
                    'Mercedes-Benz|Tourismo',
                    'Setra|S415',
                    'MAN|Lion City',
                    'Iveco|Daily Bus',
                    'Isuzu|Novo',
                    'Neoplan|Cityliner'
                ])[(floor(random()*6)+1)::int]

            WHEN wt.tip_ime IN ('Motocikl','Skuter') THEN
                (ARRAY[
                    'Yamaha|MT-07',
                    'Honda|CBR',
                    'Suzuki|Burgman',
                    'Kawasaki|Ninja',
                    'Piaggio|Liberty',
                    'Vespa|LX'
                ])[(floor(random()*6)+1)::int]

            WHEN wt.tip_ime = 'Policisko vozilo' THEN
                (ARRAY[
                    'Skoda|Octavia Police',
                    'Volkswagen|Passat Police',
                    'Ford|Transit Police',
                    'BMW|X5 Police'
                ])[(floor(random()*4)+1)::int]

            WHEN wt.tip_ime = 'Ambulantno vozilo' THEN
                (ARRAY[
                    'Mercedes-Benz|Sprinter Ambulance',
                    'Volkswagen|Crafter Ambulance',
                    'Fiat|Ducato Ambulance'
                ])[(floor(random()*3)+1)::int]

            WHEN wt.tip_ime = 'Protivpozharno vozilo' THEN
                (ARRAY[
                    'MAN|Fire Truck',
                    'Mercedes-Benz|Atego Fire',
                    'Iveco|Eurocargo Fire'
                ])[(floor(random()*3)+1)::int]

            WHEN wt.tip_ime = 'Diplomatsko vozilo' THEN
                (ARRAY[
                    'Mercedes-Benz|E-Class Diplomatic',
                    'BMW|5 Series Diplomatic',
                    'Audi|A6 Diplomatic'
                ])[(floor(random()*3)+1)::int]

            ELSE
                (ARRAY[
                    'Caterpillar|Excavator',
                    'JCB|Backhoe Loader',
                    'Komatsu|Bulldozer',
                    'Liebherr|Crane'
                ])[(floor(random()*4)+1)::int]
        END AS marka_model
    FROM with_tip wt
)

SELECT
    split_part(marka_model, '|', 1) AS marka,

    (10000000000000000 + rn)::BIGINT AS broj_sasija,

    split_part(marka_model, '|', 2) AS model,

    CASE
        WHEN tip_ime = 'Diplomatsko vozilo' THEN
            LPAD(((type_seq - 1) % 90 + 10)::TEXT, 2, '0')
            || '-CD-' ||
            LPAD(type_seq::TEXT, 5, '0')

        WHEN tip_ime = 'Policisko vozilo' THEN
            'POL-' || LPAD(type_seq::TEXT, 7, '0')

        WHEN tip_ime = 'Ambulantno vozilo' THEN
            'AMB-' || LPAD(type_seq::TEXT, 7, '0')

        WHEN tip_ime = 'Protivpozharno vozilo' THEN
            'FIR-' || LPAD(type_seq::TEXT, 7, '0')

        ELSE
            (ARRAY[
                'SK','BT','KU','TE','GV','OH','VE','ST','PR','PP','SR','KI','KO','KA',
                'BE','DE','NE','RE','SN','VI','VV','DB','DK','MB','KR','PS','VA','DH',
                'KS','PE','GE','KP','RA','SU'
            ])[((rn - 1) % 34 + 1)]
            ||
            LPAD((((rn - 1) / 34) % 10000)::TEXT, 4, '0')
            ||
            (ARRAY['A','B','C','E','H','J','K','M','N','P','R','S','T','V','Z'])
            [(((rn - 1) / (34 * 10000)) % 15 + 1)]
            ||
            (ARRAY['A','B','C','E','H','J','K','M','N','P','R','S','T','V','Z'])
            [(((rn - 1) / (34 * 10000 * 15)) % 15 + 1)]
    END AS registarska_oznaka,

    (ARRAY['Bela','Crna','Siva','Srebrna','Crvena','Sina','Zelena','Kafeava','Zolta'])
    [(floor(random()*9)+1)::int] AS boja,

    tip_id
FROM vehicle_data;

ALTER TABLE Vozilo
ADD CONSTRAINT UQ_Vozilo_BrojSasija UNIQUE (broj_sasija);


-- ===================== KORISNIK =====================
ALTER TABLE Korisnik
ALTER COLUMN embg TYPE VARCHAR(13);

INSERT INTO Korisnik (mail, hashed_password, embg)
SELECT
    LOWER(
        CASE
            WHEN rn = 1 THEN latin_ime || '.' || latin_prezime
            WHEN rn = 2 THEN latin_ime || '_' || latin_prezime
            WHEN rn = 3 THEN latin_prezime || '.' || latin_ime
            WHEN rn = 4 THEN latin_prezime || '_' || latin_ime
            ELSE latin_ime || '.' || latin_prezime || rn::TEXT
        END
        || '@' ||
        CASE (floor(random() * 8))::INT
            WHEN 0 THEN 'gmail.com'
            WHEN 1 THEN 'yahoo.com'
            WHEN 2 THEN 'hotmail.com'
            WHEN 3 THEN 'outlook.com'
            WHEN 4 THEN 'icloud.com'
            WHEN 5 THEN 'live.com'
            WHEN 6 THEN 'aol.com'
            ELSE 'mail.com'
        END
    ) AS mail,

    (
        CHR(65 + FLOOR(random() * 26)::INT)
        || SUBSTRING(md5(random()::TEXT) FROM 1 FOR 6)
        || FLOOR(random() * 90 + 10)::TEXT
        || CASE (floor(random() * 6))::INT
            WHEN 0 THEN '!'
            WHEN 1 THEN '@'
            WHEN 2 THEN '#'
            WHEN 3 THEN '$'
            WHEN 4 THEN '%'
            ELSE '&'
        END
    ) AS hashed_password,

    embg
FROM
(
    SELECT
        x.*,
        ROW_NUMBER() OVER (
            PARTITION BY latin_ime, latin_prezime
            ORDER BY embg
        ) AS rn
    FROM
    (
        SELECT
            g.embg,

            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            REPLACE(LOWER(g.ime),
            'а','a'),'б','b'),'в','v'),'г','g'),'д','d'),'ѓ','gj'),
            'е','e'),'ж','zh'),'з','z'),'ѕ','dz'),'и','i'),'ј','j'),
            'к','k'),'л','l'),'љ','lj'),'м','m'),'н','n'),'њ','nj'),
            'о','o'),'п','p'),'р','r'),'с','s'),'т','t'),'ќ','kj'),
            'у','u'),'ф','f'),'х','h'),'ц','c'),'ч','ch'),'џ','dj'),'ш','sh') AS latin_ime,

            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            REPLACE(LOWER(g.prezime),
            'а','a'),'б','b'),'в','v'),'г','g'),'д','d'),'ѓ','gj'),
            'е','e'),'ж','zh'),'з','z'),'ѕ','dz'),'и','i'),'ј','j'),
            'к','k'),'л','l'),'љ','lj'),'м','m'),'н','n'),'њ','nj'),
            'о','o'),'п','p'),'р','r'),'с','s'),'т','t'),'ќ','kj'),
            'у','u'),'ф','f'),'х','h'),'ц','c'),'ч','ch'),'џ','dj'),'ш','sh') AS latin_prezime

        FROM Gragjanin g
        WHERE AGE(CURRENT_DATE, g.datum_ragjanje) >= INTERVAL '18 years'
    ) x
) t

ON CONFLICT (mail) DO NOTHING;


-- ===================== ADMINISTRATOR =====================
ALTER TABLE Administrator
ADD CONSTRAINT UQ_Administrator_BrojNaLicenca UNIQUE (broj_na_licenca);

INSERT INTO Administrator
(
    datum_vrabotuvanje,
    broj_na_licenca,
    oddel,
    korisnik_id
)
SELECT
    CURRENT_DATE - ((random() * 3650)::int) AS datum_vrabotuvanje,

    100000 + ROW_NUMBER() OVER (ORDER BY k.korisnik_id) AS broj_na_licenca,

    CASE (random() * 4)::int
        WHEN 0 THEN 'Traffic Monitoring'
        WHEN 1 THEN 'Camera Control'
        WHEN 2 THEN 'Violation Review'
        WHEN 3 THEN 'Penalty Management'
        ELSE 'System Administration'
    END AS oddel,


    k.korisnik_id
FROM Korisnik k
ORDER BY random()
LIMIT (
    SELECT GREATEST(1, COUNT(*) * 2 / 100)
    FROM Korisnik
);

WITH numbered AS
(
    SELECT
        administrator_id,
        ROW_NUMBER() OVER (ORDER BY administrator_id) AS rn
    FROM Administrator
)

UPDATE Administrator a
SET oddel =
    CASE ((n.rn - 1) % 5)
        WHEN 0 THEN 'Traffic Monitoring'
        WHEN 1 THEN 'Camera Control'
        WHEN 2 THEN 'Violation Review'
        WHEN 3 THEN 'Penalty Management'
        ELSE 'System Administration'
    END
FROM numbered n
WHERE a.administrator_id = n.administrator_id;


-- ===================== ULOGA =====================
INSERT INTO Uloga (ime)
VALUES
    ('Administrator'),
    ('Korisnik');


-- ===================== KORISNIK_ULOGA =====================
INSERT INTO Korisnik_Uloga (korisnik_id, uloga_id)

SELECT
    k.korisnik_id,

    CASE
        WHEN random() < 0.20 THEN
            (SELECT uloga_id
             FROM Uloga
             WHERE lower(ime) = 'administrator')

        ELSE
            (SELECT uloga_id
             FROM Uloga
             WHERE lower(ime) = 'korisnik')
    END

FROM Korisnik k

ON CONFLICT DO NOTHING;


-- ===================== MREZHNAKONEKCIJA =====================
INSERT INTO MrezhnaKonekcija (ime, opis)
VALUES
    ('Ethernet', 'Zicna LAN konekcija za stabilen prenos na podatoci'),
    ('Fiber Optic', 'Opticka mreza so visok bandwidth za HD video streaming'),
    ('4G LTE', 'Mobilna mreza za kameri na oddalecheni lokacii'),
    ('5G', 'Brza mobilna mreza za real-time video nadzor'),
    ('WiFi', 'Bezicna lokalna mreza za komunikacija na uredite'),
    ('VPN', 'Bezbedna kriptirana mrezna konekcija'),
    ('Wireless Bridge', 'Bezicno povrzuvanje pomegju dve lokacii'),
    ('Satellite', 'Satelitska konekcija za ruralni i oddalecheni oblasti'),
    ('Municipal LAN', 'Opshtinska lokalna mreza za javni sistemi'),
    ('Police Secure Network', 'Interna policiska bezbedna mreza'),
    ('Traffic Control Network', 'Mreza za kontrola i monitoring na soobrakaj'),
    ('Camera Backbone Fiber', 'Centralna opticka infrastruktura za kameri'),
    ('Hybrid Network', 'Kombinacija od fiber i mobilna mreza'),
    ('Emergency Response VPN', 'VPN mreza za итни sluzhbi'),
    ('Smart City Grid', 'Integrirana mreza za smart city infrastruktura'),
    ('Public Transport Network', 'Mreza za monitoring na javniot prevoz'),
    ('Tunnel Monitoring Network', 'Specijalizirana mreza za tunelski nadzor'),
    ('Bridge Surveillance Link', 'Mrezna konekcija za monitoring na mostovi'),
    ('Parking Monitoring LAN', 'Lokalna mreza za parking sistemi'),
    ('AI Video Analytics Fiber', 'Opticka mreza za AI analiza na video'),
    ('Red Light Camera LTE', 'LTE mreza za kameri na semafori'),
    ('Highway Surveillance Wireless', 'Bezicna mreza za avtopatski kameri'),
    ('Border Security Network', 'Mreza za granichna bezbednost'),
    ('Drone Communication Link', 'Mreza za komunikacija so dronovi'),
    ('Environmental Sensor Network', 'Mreza za senzori i kameri'),
    ('Facial Recognition Network', 'Mreza za AI sistemi za prepoznavanje'),
    ('Real-Time Monitoring Fiber', 'Opticka mreza za monitoring vo realno vreme'),
    ('Backup LTE Connection', 'Rezervna mobilna konekcija pri prekin'),
    ('Secure Government LAN', 'Bezbedna mreza za drzhavni sistemi'),
    ('Integrated Surveillance Network', 'Centralizirana mreza za video nadzor');


-- ===================== MREZHNAKONEKCIJAKAMERA =====================
INSERT INTO MrezhnaKonekcijaKamera (mrezna_konekcija_id, kamera_id)
SELECT
    mk.mrezna_konekcija_id,
    k.kamera_id
FROM Kamera k
JOIN LATERAL (
    SELECT mrezna_konekcija_id
    FROM MrezhnaKonekcija
    WHERE ime =
        CASE
            WHEN k.kamera_id % 100 < 30 THEN 'Fiber Optic'
            WHEN k.kamera_id % 100 < 50 THEN '4G LTE'
            WHEN k.kamera_id % 100 < 65 THEN '5G'
            WHEN k.kamera_id % 100 < 78 THEN 'Ethernet'
            WHEN k.kamera_id % 100 < 88 THEN 'VPN'
            WHEN k.kamera_id % 100 < 94 THEN 'WiFi'
            WHEN k.kamera_id % 100 < 98 THEN 'Wireless Bridge'
            ELSE 'Satellite'
        END
    LIMIT 1
) mk ON true
ON CONFLICT DO NOTHING;

INSERT INTO MrezhnaKonekcijaKamera (mrezna_konekcija_id, kamera_id)
SELECT
    mk.mrezna_konekcija_id,
    k.kamera_id
FROM Kamera k
JOIN LATERAL (
    SELECT mrezna_konekcija_id
    FROM MrezhnaKonekcija
    WHERE ime =
        CASE
            WHEN k.kamera_id % 100 < 40 THEN 'Backup LTE Connection'
            WHEN k.kamera_id % 100 < 70 THEN 'VPN'
            WHEN k.kamera_id % 100 < 90 THEN 'Police Secure Network'
            ELSE 'Satellite'
        END
    LIMIT 1
) mk ON true
WHERE k.kamera_id % 10 IN (0, 3, 7)
ON CONFLICT DO NOTHING;


-- ===================== SNIMKA =====================
ALTER TABLE Snimka
ADD COLUMN arhivirana BOOLEAN DEFAULT FALSE;

ALTER TABLE Snimka
ADD COLUMN datum_arhiviranje DATE;

ALTER TABLE Snimka
ADD CONSTRAINT CHK_Snimka_Arhiviranje
CHECK (
    datum_arhiviranje IS NULL
    OR datum_arhiviranje >= datum
);

INSERT INTO Snimka
(
    datum,
    url_adresa,
    arhivirana,
    datum_arhiviranje,
    kamera_id
)
WITH kameri AS (
    SELECT
        kamera_id,
        datum_instalacija,
        ROW_NUMBER() OVER (ORDER BY kamera_id) AS rn,
        COUNT(*) OVER () AS total_kameri
    FROM Kamera
    WHERE datum_instalacija IS NOT NULL
),
generated AS (
    SELECT
        gs AS unique_id,
        k.kamera_id,
        k.datum_instalacija,

        (
            k.datum_instalacija
            + ((random() * GREATEST(1, CURRENT_DATE - k.datum_instalacija))::int)
        )::date AS datum,

        CASE
            WHEN random() < 0.35 THEN TRUE
            ELSE FALSE
        END AS arhivirana

    FROM generate_series(1, 5000000) gs
    JOIN kameri k
        ON k.rn = ((gs - 1) % k.total_kameri) + 1
)
SELECT
    datum,

    'https://safecity.mk/snimki/kamera_' || kamera_id ||
    '/datum_' || TO_CHAR(datum, 'YYYY_MM_DD') ||
    '/snimka_' || unique_id ||
    '_' || md5(kamera_id::text || '_' || unique_id::text || '_' || datum::text) ||
    '.mp4' AS url_adresa,

    arhivirana,

    CASE
        WHEN arhivirana = TRUE
        THEN LEAST(CURRENT_DATE, datum + ((random() * 60)::int))
        ELSE NULL
    END AS datum_arhiviranje,

    kamera_id
FROM generated;


-- ===================== SLIKA =====================
INSERT INTO Slika
(
    url,
    format,
    golemina,
    datum_kreiranje,
    snimka_id
)
SELECT
    'https://safecity.mk/sliki/snimka_' || x.snimka_id ||
    '/slika_' || x.broj_slika ||
    '_' || md5(x.snimka_id::text || '_' || x.broj_slika::text || '_' || x.datum::text) ||
    '.' || x.format AS url,

    x.format,
    x.golemina,
    x.datum AS datum_kreiranje,
    x.snimka_id
FROM
(
    SELECT
        s.snimka_id,
        s.datum,
        gs AS broj_slika,

        CASE ((s.snimka_id % 5))
            WHEN 0 THEN 'jpg'
            WHEN 1 THEN 'jpeg'
            WHEN 2 THEN 'png'
            WHEN 3 THEN 'bmp'
            ELSE 'gif'
        END AS format,

        100000 + (random() * 4900000)::int AS golemina

    FROM Snimka s
    JOIN Kamera k
        ON s.kamera_id = k.kamera_id
    CROSS JOIN generate_series(1, 2) gs
    WHERE s.datum >= k.datum_instalacija
) x;


-- ===================== PREGLEDSNIMKA =====================
INSERT INTO PregledSnimka
(
    administrator_id,
    snimka_id,
    status
)
SELECT
    a.administrator_id,
    s.snimka_id,
    CASE
        WHEN random() < 0.15 THEN 0
        WHEN random() < 0.75 THEN 1
        ELSE 2
    END AS status
FROM
(
    SELECT
        snimka_id,
        ROW_NUMBER() OVER (ORDER BY snimka_id) AS rn
    FROM Snimka
) s
JOIN
(
    SELECT
        administrator_id,
        ROW_NUMBER() OVER (ORDER BY administrator_id) AS rn,
        COUNT(*) OVER () AS total_admins
    FROM Administrator
) a
ON ((s.rn - 1) % a.total_admins) + 1 = a.rn
ON CONFLICT (administrator_id, snimka_id) DO NOTHING;


-- ===================== TIPPREKRSOK =====================
INSERT INTO TipPrekrsok (ime, iznos)
VALUES
('Prekoracena brzina do 10 km/h', 50),
('Prekoracena brzina od 10 do 30 km/h', 150),
('Prekoracena brzina nad 30 km/h', 300),
('Ekstremno prekoracena brzina nad 50 km/h', 600),
('Pominuvanje na crveno svetlo', 250),
('Pominuvanje na zholto svetlo pri zabrana', 120),
('Nepropisno parkiranje', 80),
('Parkiranje na invalidsko mesto', 250),
('Parkiranje na peshachki premin', 180),
('Parkiranje na avtobuska stanica', 200),
('Koristenje mobilen telefon pri vozenje', 120),
('Pisuvanje poraki pri vozenje', 180),
('Nekoristenje sigurnosen pojas', 100),
('Nekoristenje kaciga na motocikl', 150),
('Vozenje bez vozacka dozvola', 500),
('Vozenje so nesoodvetna kategorija', 400),
('Vozenje so istechena registracija', 200),
('Vozenje bez registracija', 500),
('Vozenje pod dejstvo na alkohol', 600),
('Vozenje pod dejstvo na narkotici', 800),
('Nepochtuvanje prvenstvo na minuvanje', 180),
('Nepropisno preticanje', 220),
('Vozenje vo zabraneta nasoka', 300),
('Vozenje vo zabraneta zona', 250),
('Nepochtuvanje peshachki premin', 200),
('Zagrozuvanje na peshaci', 350),
('Nepropisno svrtuvanje', 100),
('Nepropisno prestrojuvanje', 120),
('Prevoz na pogolem broj patnici od dozvolenoto', 140),
('Neispravni svetla', 70),
('Neispravni stop svetla', 80),
('Neispravni migavci', 60),
('Neispravni sopirachki', 350),
('Tehnichki neispravno vozilo', 400),
('Prekomerna buchava od vozilo', 90),
('Nelegalni temni stakla', 130),
('Vozenje bez osiguruvanje', 250),
('Koristenje falsifikuvani registraciski oznaki', 1000),
('Neprijavena soobrakjajna nesrekja', 500),
('Nepropisno dvizhenje vo zholta lenta', 150),
('Nepochtuvanje policiski naredbi', 700),
('Beganje od policija', 1200),
('Nepropisno dvizhenje na motocikl', 130),
('Nepropisno transportiranje tovar', 260),
('Nepropisno vlechenje prikolka', 170),
('Preoptovareno tovarno vozilo', 450),
('Vozenje bez tablichki', 500),
('Prekrshuvanje policiski chas', 300),
('Driftanje i opasno vozenje', 700),
('Nepropisno vrtenje polukruzhno', 120),
('Blokiranje raskrsnica', 100),
('Nepropisno koristenje sirena', 50),
('Nepropisno koristenje rotacioni svetla', 900),
('Koristenje vozilo bez tehnichki pregled', 250),
('Nepropisno dvizhenje vo kruzhen tek', 110);


-- ===================== SOPSTVENIK_VOZILO =====================
ALTER TABLE Sopstvenik_Vozilo
DROP CONSTRAINT FK_SV_Sopstvenik;

ALTER TABLE Sopstvenik_Vozilo
ALTER COLUMN embg TYPE CHAR(13)
USING embg::CHAR(13);

ALTER TABLE Sopstvenik_Vozilo
ADD CONSTRAINT FK_SV_Sopstvenik
FOREIGN KEY (embg)
REFERENCES Sopstvenik(embg)
ON DELETE CASCADE
ON UPDATE CASCADE;

INSERT INTO Sopstvenik_Vozilo (embg, vozilo_id)

SELECT
    s.embg,
    v.vozilo_id

FROM
(
    SELECT
        embg,
        row_number() OVER (ORDER BY random()) AS rn
    FROM Sopstvenik
) s

JOIN
(
    SELECT
        vozilo_id,
        row_number() OVER (ORDER BY random()) AS rn
    FROM Vozilo
) v

ON s.rn = v.rn

ON CONFLICT DO NOTHING;


-- ===================== REGISTRACIJA =====================
INSERT INTO registracija
(broj, mesto, datum, datum_istekuvanje, sopstvenikembg, vozilovozilo_id)
WITH valid_owners AS (
    SELECT s.embg, ROW_NUMBER() OVER (ORDER BY s.embg) AS owner_rn
    FROM sopstvenik s
    JOIN gragjanin g ON g.embg = s.embg
    WHERE AGE(CURRENT_DATE, g.datum_ragjanje) >= INTERVAL '18 years'
),
owner_count AS (
    SELECT COUNT(*) AS total_owners FROM valid_owners
),
valid_vehicles AS (
    SELECT v.vozilo_id, v.registarska_oznaka, t.ime AS tip_ime,
           ROW_NUMBER() OVER (ORDER BY v.vozilo_id) AS vehicle_rn,
           random() AS r
    FROM vozilo v
    JOIN tip t ON t.tip_id = v.tip_id
    WHERE t.ime NOT IN ('Policisko vozilo','Ambulantno vozilo','Protivpozharno vozilo','Diplomatsko vozilo')
),
vehicle_registration_count AS (
    SELECT vv.*,
           CASE
               WHEN r < 0.50 THEN (4 + floor(random() * 7))::int
               WHEN r < 0.75 THEN 2
               WHEN r < 0.85 THEN 3
               ELSE 1
           END AS registration_count
    FROM valid_vehicles vv
),
expanded AS (
    SELECT vrc.vozilo_id, vrc.registarska_oznaka, vrc.vehicle_rn, vrc.registration_count, gs.reg_num
    FROM vehicle_registration_count vrc
    CROSS JOIN LATERAL generate_series(1, vrc.registration_count) gs(reg_num)
),
registrations AS (
    SELECT e.*,
           (CURRENT_DATE - ((e.registration_count - e.reg_num) * 365) - ((random() * 120)::int))::date AS datum_registracija,
           CASE
               WHEN random() < 0.70 THEN ((e.vehicle_rn * 13) % oc.total_owners) + 1
               ELSE ((e.vehicle_rn * 13 + e.reg_num * 97) % oc.total_owners) + 1
           END AS owner_pick
    FROM expanded e
    CROSS JOIN owner_count oc
),
with_owner AS (
    SELECT r.*, vo.embg
    FROM registrations r
    JOIN valid_owners vo ON vo.owner_rn = r.owner_pick
)
SELECT
    ROW_NUMBER() OVER () AS broj,
    CASE LEFT(registarska_oznaka, 2)
        WHEN 'SK' THEN 1 WHEN 'BT' THEN 2 WHEN 'KU' THEN 3 WHEN 'TE' THEN 4
        WHEN 'GV' THEN 5 WHEN 'OH' THEN 6 WHEN 'VE' THEN 7 WHEN 'ST' THEN 8
        WHEN 'PR' THEN 9 WHEN 'PP' THEN 10 WHEN 'SR' THEN 11 WHEN 'KI' THEN 12
        WHEN 'KO' THEN 13 WHEN 'KA' THEN 14
        ELSE ((vehicle_rn - 1) % 34) + 1
    END AS mesto,
    datum_registracija AS datum,
    (datum_registracija + INTERVAL '1 year')::date AS datum_istekuvanje,
    embg AS sopstvenikembg,
    vozilo_id AS vozilovozilo_id
FROM with_owner;


-- ===================== KAZNA =====================
ALTER TABLE Kazna
ADD COLUMN IF NOT EXISTS iznos_za_plakanje INTEGER;

ALTER TABLE Kazna
ADD CONSTRAINT CHK_Kazna_Iznos
CHECK (iznos_za_plakanje IS NULL OR iznos_za_plakanje > 0);


-- ===================== NOTIFIKACIJA =====================
INSERT INTO Notifikacija
(
    sodrzina,
    slika_id,
    korisnik_id
)
SELECT
    CASE (random() * 7)::int
        WHEN 0 THEN
            'Ве известуваме дека е евидентирано надминување на дозволената брзина за 5 km/h.Ова известување претставува опомена и не е казна'
        WHEN 1 THEN
            'Ве известуваме дека е евидентирано надминување на дозволената брзина за 10 km/h.'
        WHEN 2 THEN
            'Ве известуваме дека е евидентирано надминување на дозволената брзина за 15 km/h.'
        WHEN 3 THEN
            'Ве известуваме дека е евидентирано користење мобилен телефон за време на управување со возило.'
        WHEN 4 THEN
            'Ве известуваме дека е евидентирано поминување на црвено светло.'
        WHEN 5 THEN
            'Ве известуваме дека е евидентирано непропуштање пешаци на пешачки премин.'
    END AS sodrzina,

    s.slika_id,

    k.korisnik_id

FROM
(
    SELECT
        slika_id,
        ROW_NUMBER() OVER (ORDER BY slika_id) AS rn
    FROM Slika
) s
JOIN
(
    SELECT
        korisnik_id,
        ROW_NUMBER() OVER (ORDER BY korisnik_id) AS rn,
        COUNT(*) OVER () AS total_korisnici
    FROM Korisnik
) k
ON ((s.rn - 1) % k.total_korisnici) + 1 = k.rn;


-- ===================== PREKRSOK =====================
INSERT INTO Prekrsok
(
    opis,
    vreme,
    status,
    datum,
    tip_prekrsok_id,
    kamera_id,
    kazna_id
)
WITH
target AS (
    SELECT 5000000::bigint AS total_needed
),
current_count AS (
    SELECT COUNT(*)::bigint AS current_total
    FROM Prekrsok
),
to_insert AS (
    SELECT GREATEST(0, t.total_needed - c.current_total) AS rows_to_insert
    FROM target t
    CROSS JOIN current_count c
),
kazni AS (
    SELECT
        kazna_id,
        datum,
        status,
        ROW_NUMBER() OVER (ORDER BY kazna_id) AS rn,
        COUNT(*) OVER () AS total
    FROM Kazna
),
tipovi AS (
    SELECT
        tip_prekrsok_id,
        ime,
        ROW_NUMBER() OVER (ORDER BY tip_prekrsok_id) AS rn,
        COUNT(*) OVER () AS total
    FROM TipPrekrsok
),
kameri AS (
    SELECT
        kamera_id,
        datum_instalacija,
        ROW_NUMBER() OVER (ORDER BY kamera_id) AS rn,
        COUNT(*) OVER () AS total
    FROM Kamera
),
broevi AS (
    SELECT g.gs
    FROM to_insert ti
    CROSS JOIN generate_series(1, ti.rows_to_insert) AS g(gs)
)
SELECT
    'Evidentiran prekrsok od tip: ' || t.ime AS opis,

    make_time(
        (6 + (b.gs % 17))::int,
        (b.gs % 60)::int,
        ((b.gs * 7) % 60)::int
    ) AS vreme,

    k.status,

    CASE
        WHEN k.datum < km.datum_instalacija THEN km.datum_instalacija
        ELSE k.datum
    END AS datum,

    t.tip_prekrsok_id,
    km.kamera_id,
    k.kazna_id
FROM broevi b
JOIN kazni k
    ON k.rn = ((b.gs - 1) % k.total) + 1
JOIN tipovi t
    ON t.rn = ((b.gs - 1) % t.total) + 1
JOIN kameri km
    ON km.rn = ((b.gs - 1) % km.total) + 1;

ALTER TABLE Prekrsok
ADD COLUMN detektirana_brzina INTEGER;

UPDATE Prekrsok
SET detektirana_brzina = 40 + floor(random() * 100)::int
WHERE detektirana_brzina IS NULL;

ALTER TABLE Prekrsok
ADD CONSTRAINT CHK_Prekrsok_Brzina
CHECK (detektirana_brzina IS NULL OR detektirana_brzina > 0);


-- ===================== PLAKANJE =====================
INSERT INTO Plakanje
(
    metod,
    datum,
    kazna_id
)
SELECT
    CASE (k.kazna_id % 3)
        WHEN 0 THEN 1
        WHEN 1 THEN 2
        ELSE 3
    END AS metod,

    LEAST(
        CURRENT_DATE,
        COALESCE(k.datum, CURRENT_DATE) + ((k.kazna_id % 365)::int)
    ) AS datum,

    k.kazna_id
FROM Kazna k
WHERE k.kazna_id <= 10000000
  AND NOT EXISTS (
      SELECT 1
      FROM Plakanje p
      WHERE p.kazna_id = k.kazna_id
  )
ORDER BY k.kazna_id
LIMIT 800000;


-- ===================== NOTIFIKACIJA_KAZNA =====================
INSERT INTO Notifikacija_Kazna
(
    notifikacija_id,
    kazna_id
)
SELECT
    n.notifikacija_id,
    k.kazna_id
FROM
(
    SELECT
        notifikacija_id,
        ROW_NUMBER() OVER (ORDER BY notifikacija_id) AS rn
    FROM Notifikacija
) n
JOIN
(
    SELECT
        kazna_id,
        ROW_NUMBER() OVER (ORDER BY kazna_id) AS rn
    FROM Kazna
) k
ON n.rn = k.rn
WHERE NOT EXISTS
(
    SELECT 1
    FROM Notifikacija_Kazna nk
    WHERE nk.notifikacija_id = n.notifikacija_id
      AND nk.kazna_id = k.kazna_id
);

WITH novi_notifikacii AS
(
    SELECT
        notifikacija_id,
        ROW_NUMBER() OVER (ORDER BY notifikacija_id) AS rn
    FROM Notifikacija
),
stari_redovi AS
(
    SELECT
        nk.notifikacija_id AS old_notifikacija_id,
        nk.kazna_id,
        ROW_NUMBER() OVER (ORDER BY nk.kazna_id) AS rn
    FROM Notifikacija_Kazna nk
)
UPDATE Notifikacija_Kazna nk
SET notifikacija_id = nn.notifikacija_id
FROM novi_notifikacii nn
JOIN stari_redovi sr
    ON nn.rn = sr.rn
WHERE nk.notifikacija_id = sr.old_notifikacija_id
  AND nk.kazna_id = sr.kazna_id;


-- ===================== ZALBA =====================
INSERT INTO Zalba
(
    sodrzina,
    datum_na_podnesuvanje,
    status,
    administrator_id,
    korisnik_id,
    prekrsok_id
)
SELECT
    CASE (p.prekrsok_id % 6)
        WHEN 0 THEN 'Казната е погрешно евидентирана од камерата.'
        WHEN 1 THEN 'Возилото не било управувано од мене во тој момент.'
        WHEN 2 THEN 'Семафорот не функционирал правилно.'
        WHEN 3 THEN 'Износот на казната не е точен.'
        WHEN 4 THEN 'Барам дополнителна проверка на доказите.'
        ELSE 'Во записот за прекршокот има неточни информации.'
    END AS sodrzina,

    LEAST(
        CURRENT_DATE,
        kz.datum + ((p.prekrsok_id % 15) + 1)::int
    ) AS datum_na_podnesuvanje,

    CASE (p.prekrsok_id % 10)
        WHEN 0 THEN 'Одобрена'
        WHEN 1 THEN 'Одобрена'
        WHEN 2 THEN 'Во постапка'
        WHEN 3 THEN 'Во постапка'
        ELSE 'Одбиена'
    END AS status,

    a.administrator_id,

    kz.korisnik_id,

    p.prekrsok_id

FROM Prekrsok p
JOIN Kazna kz
    ON kz.kazna_id = p.kazna_id

JOIN LATERAL (
    SELECT ad.administrator_id
    FROM Administrator ad
    ORDER BY ((ad.administrator_id + p.prekrsok_id) % 100000)
    LIMIT 1
) a ON true

WHERE kz.korisnik_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM Zalba z
      WHERE z.prekrsok_id = p.prekrsok_id
  )

ORDER BY p.prekrsok_id
LIMIT 200000;

WITH
admin_ids AS (
    SELECT array_agg(administrator_id) AS ids
    FROM administrator
),
korisnik_ids AS (
    SELECT array_agg(korisnik_id) AS ids
    FROM korisnik
),
prekrsok_ids AS (
    SELECT array_agg(prekrsok_id) AS ids
    FROM prekrsok
)

INSERT INTO zalba (
    sodrzina,
    datum_na_podnesuvanje,
    status,
    administrator_id,
    korisnik_id,
    prekrsok_id
)

SELECT
    CASE floor(random() * 6)::int
        WHEN 0 THEN 'Neosnovana zalba za prekrsok'
        WHEN 1 THEN 'Baranje za namaluvanje na kazna'
        WHEN 2 THEN 'Prigovor za pogresna identifikacija'
        WHEN 3 THEN 'Zalba za netocni podatoci'
        WHEN 4 THEN 'Baranje za povtorno razgleduvanje'
        ELSE 'Podnesena e oficijalna zalba'
    END,

    CURRENT_DATE - ((random() * 1000)::int),

    CASE floor(random() * 4)::int
        WHEN 0 THEN 'podnesena'
        WHEN 1 THEN 'vo_postapka'
        WHEN 2 THEN 'prifatena'
        ELSE 'odbijena'
    END,

    admin_ids.ids[
        floor(random() * array_length(admin_ids.ids, 1) + 1)::int
    ],

    korisnik_ids.ids[
        floor(random() * array_length(korisnik_ids.ids, 1) + 1)::int
    ],

    prekrsok_ids.ids[
        floor(random() * array_length(prekrsok_ids.ids, 1) + 1)::int
    ]

FROM generate_series(1, 7000000)

CROSS JOIN admin_ids
CROSS JOIN korisnik_ids
CROSS JOIN prekrsok_ids;


-- ===================== PREKRSOK_VOZILO =====================
INSERT INTO Prekrsok_Vozilo (prekrsok_id, vozilo_id)
SELECT p.prekrsok_id, v.vozilo_id
FROM Prekrsok p
CROSS JOIN LATERAL (
    SELECT vozilo_id
    FROM Vozilo
    ORDER BY RANDOM()
    LIMIT 1
) v
ON CONFLICT (prekrsok_id, vozilo_id) DO NOTHING;


-- ===================== PREKRSOK_STORITEL =====================
ALTER TABLE Prekrsok_Storitel
ALTER COLUMN storitel_embg TYPE VARCHAR(13);

INSERT INTO Prekrsok_Storitel (prekrsok_id, storitel_embg)
SELECT
    p.prekrsok_id,
    g.embg
FROM
(
    SELECT
        prekrsok_id,
        ROW_NUMBER() OVER (ORDER BY random()) AS rn
    FROM Prekrsok
) p
JOIN
(
    SELECT
        embg,
        ROW_NUMBER() OVER (ORDER BY random()) AS rn,
        COUNT(*) OVER () AS total
    FROM Gragjanin
) g
ON g.rn = ((p.rn - 1) % g.total) + 1
ON CONFLICT (prekrsok_id, storitel_embg) DO NOTHING;