DatabaseCreation: dml.2.sql

-- ==========================================================
-- 1. STATIC BANK DATA
-- ==========================================================

INSERT INTO banka (banka_id, ime_na_banka, edb, datum_na_osnovanje)
SELECT gs,
       (ARRAY[
           'Komercijalna Banka', 'Stopanska Banka', 'NLB Banka', 'Halkbank',
           'Silk Road Bank', 'Sparkasse Bank', 'TTK Banka', 'Unibanka',
           'Centralna Kooperativna Banka', 'ProCredit Bank'
       ])[((gs - 1) % 10) + 1],
       RIGHT('5' || LPAD(gs::text, 12, '0'), 13),
       CURRENT_DATE - ((2000 + floor(random() * 13000))::int)
FROM generate_series(1, 10) gs;

INSERT INTO valuta (valuta_id, kod, ime, simbol)
VALUES
    (1, 'MKD', 'Makedonski denar', 'ден'),
    (2, 'EUR', 'Euro', '€'),
    (3, 'USD', 'US Dollar', '$'),
    (4, 'CHF', 'Swiss Franc', 'CHF'),
    (5, 'GBP', 'British Pound', '£');

INSERT INTO kursna_lista (kurs_id, datum, kupoven_kurs, sreden_kurs, prodazen_kurs, valuta_od_id, valuta_do_id)
WITH dates AS (
    SELECT generate_series(CURRENT_DATE - INTERVAL '29 days', CURRENT_DATE, INTERVAL '1 day')::date AS datum
), rates AS (
    SELECT * FROM (VALUES
        (2, 61.50::numeric),
        (3, 56.70::numeric),
        (4, 64.20::numeric),
        (5, 72.10::numeric)
    ) AS r(valuta_id, base_rate)
), generated AS (
    SELECT row_number() OVER (ORDER BY d.datum, r.valuta_id) AS kurs_id,
           d.datum,
           r.valuta_id,
           r.base_rate + ((random() - 0.5) * 2)::numeric AS mid
    FROM dates d
    CROSS JOIN rates r
)
SELECT kurs_id,
       datum,
       round(mid - 0.25, 4),
       round(mid, 4),
       round(mid + 0.25, 4),
       valuta_id,
       1
FROM generated;

-- ==========================================================
-- 2. USERS, ROLES AND PRIVILEGES
-- ==========================================================

INSERT INTO bank_user (user_id, username, password_hash, status)
SELECT gs,
       'user_' || gs,
       md5('password_' || gs),
       CASE WHEN gs % 20 = 0 THEN 'NEAKTIVEN' ELSE 'AKTIVEN' END
FROM generate_series(1, 110000) gs;

INSERT INTO role (role_id, ime)
VALUES
    (1, 'CLIENT'),
    (2, 'EMPLOYEE'),
    (3, 'ADMIN');

INSERT INTO privilegii (privilegija_id, privilegija)
VALUES
    (1, 'VIEW_ACCOUNT'),
    (2, 'CREATE_PAYMENT'),
    (3, 'VIEW_TRANSACTIONS'),
    (4, 'MANAGE_CLIENTS'),
    (5, 'APPROVE_LOAN'),
    (6, 'ADMIN_PANEL');

INSERT INTO role_privilegii (role_id, privilegija_id)
VALUES
    (1, 1), (1, 2), (1, 3),
    (2, 1), (2, 3), (2, 4), (2, 5),
    (3, 1), (3, 2), (3, 3), (3, 4), (3, 5), (3, 6);

INSERT INTO role_user (role_id, user_id)
SELECT CASE
           WHEN gs <= 100000 THEN 1
           WHEN gs <= 110000 THEN 2
           ELSE 3
       END AS role_id,
       gs AS user_id
FROM generate_series(1, 110000) gs;

-- ==========================================================
-- 3. BRANCHES
-- ==========================================================

INSERT INTO filijala (filijala_id, ime, banka_id)
SELECT gs,
       'Filijala_' || gs || '_' ||
       (ARRAY['Skopje','Bitola','Ohrid','Prilep','Tetovo','Kumanovo','Veles','Stip','Strumica','Gostivar'])[(gs % 10) + 1],
       ((gs - 1) % 10) + 1
FROM generate_series(1, 100) gs;

-- ==========================================================
-- 4. CLIENTS AND EMPLOYEES
-- ==========================================================

-- FIX: replace the old INSERT INTO klient block with this one.
-- Reason: embg is UNIQUE, so the 3-digit final index must be unique inside each
-- birth_date + city_code + gender group.

INSERT INTO klient (klient_id, user_id, ime, prezime, datum_ragjanje, tatkovo_ime, embg)
WITH person_raw AS (
    SELECT gs,
           CASE WHEN gs % 2 = 0 THEN 'M' ELSE 'F' END AS gender,
           CASE WHEN gs % 2 = 0 THEN
               (ARRAY['Aleksandar','Bojan','Stefan','Marko','Petar','Martin','David','Filip','Nikola','Krumislav','Vladimir','Andrej','Darko','Igor','Goran','Dejan','Mile','Tome','Antonio','Kristijan','Mihail','Damjan','Dimitar','Luka','Matej','Viktor','Daniel','Jovan'])[((gs * 7) % 28) + 1]
           ELSE
               (ARRAY['Sara','Ana','Marija','Elena','Ivana','Jovana','Kristina','Simona','Teodora','Angela','Mila','Tamara','Monika','Viktorija','Bojana','Katerina','Martina','Stefanija','Maja','Biljana','Aleksandra','Anastasija','Mia','Lara','Eva','Irena','Natalija'])[((gs * 7) % 27) + 1]
           END AS ime,
           CASE WHEN gs % 2 = 0 THEN
               (ARRAY['Petrovski','Trajkovski','Stojanovski','Ristovski','Jovanovski','Nikolovski','Tasevski','Bojinovski','Mitrevski','Georgievski','Atanasovski','Kostovski','Dimitrovski','Popovski','Ilievski','Mladenovski','Kolevski','Naumovski','Kuzmanovski','Krstevski'])[((gs * 11) % 20) + 1]
           ELSE
               (ARRAY['Petrovska','Trajkovska','Stojanovska','Ristovska','Jovanovska','Nikolovska','Tasevska','Bojinovska','Mitrevska','Georgievska','Atanasovska','Kostovska','Dimitrovska','Popovska','Ilievska','Mladenovska','Kolevska','Naumovska','Kuzmanovska','Krstevska'])[((gs * 11) % 20) + 1]
           END AS prezime,
           (ARRAY['Aleksandar','Bojan','Stefan','Marko','Petar','Martin','David','Filip','Nikola','Vladimir','Andrej','Darko','Igor','Goran','Dejan'])[((gs * 13) % 15) + 1] AS tatkovo_ime,

           -- deterministic birthdate instead of random, so distribution is stable
           make_date(
               (1946 + ((gs * 37) % 62))::int,
               (1 + ((gs * 17) % 12))::int,
               (1 + ((gs * 19) % 28))::int
           ) AS birth_date,

           (ARRAY['45','46','47','48','49','50','51','52','53','54'])[((gs * 23) % 10) + 1] AS city_code
    FROM generate_series(1, 100000) gs
), person AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY birth_date, city_code, gender
               ORDER BY gs
           ) AS embg_seq
    FROM person_raw
)
SELECT gs,
       gs,
       ime,
       prezime,
       birth_date,
       tatkovo_ime,
       LPAD(EXTRACT(DAY FROM birth_date)::int::text, 2, '0') ||
       LPAD(EXTRACT(MONTH FROM birth_date)::int::text, 2, '0') ||
       LPAD((EXTRACT(YEAR FROM birth_date)::int % 1000)::text, 3, '0') ||
       city_code ||
       CASE WHEN gender = 'M' THEN '0' ELSE '5' END ||
       LPAD(embg_seq::text, 3, '0') AS embg
FROM person;

-- Optional same fix for vraboten, because vraboten.embg is also UNIQUE.

INSERT INTO vraboten (vraboten_id, user_id, ime, prezime, tatkovo_ime, datum_ragjanje, embg)
WITH person_raw AS (
    SELECT gs,
           100000 + gs AS user_id,
           CASE WHEN gs % 2 = 0 THEN 'M' ELSE 'F' END AS gender,
           CASE WHEN gs % 2 = 0 THEN
               (ARRAY['Aleksandar','Bojan','Stefan','Marko','Petar','Martin','David','Filip','Nikola','Krumislav','Vladimir','Andrej','Darko','Igor','Goran','Dejan','Mile','Tome','Antonio','Kristijan','Mihail','Damjan','Dimitar','Luka','Matej','Viktor','Daniel','Jovan'])[((gs * 7) % 28) + 1]
           ELSE
               (ARRAY['Sara','Ana','Marija','Elena','Ivana','Jovana','Kristina','Simona','Teodora','Angela','Mila','Tamara','Monika','Viktorija','Bojana','Katerina','Martina','Stefanija','Maja','Biljana','Aleksandra','Anastasija','Mia','Lara','Eva','Irena','Natalija'])[((gs * 7) % 27) + 1]
           END AS ime,
           CASE WHEN gs % 2 = 0 THEN
               (ARRAY['Petrovski','Trajkovski','Stojanovski','Ristovski','Jovanovski','Nikolovski','Tasevski','Bojinovski','Mitrevski','Georgievski','Atanasovski','Kostovski','Dimitrovski','Popovski','Ilievski','Mladenovski','Kolevski','Naumovski','Kuzmanovski','Krstevski'])[((gs * 11) % 20) + 1]
           ELSE
               (ARRAY['Petrovska','Trajkovska','Stojanovska','Ristovska','Jovanovska','Nikolovska','Tasevska','Bojinovska','Mitrevska','Georgievska','Atanasovska','Kostovska','Dimitrovska','Popovska','Ilievska','Mladenovska','Kolevska','Naumovska','Kuzmanovska','Krstevska'])[((gs * 11) % 20) + 1]
           END AS prezime,
           (ARRAY['Aleksandar','Bojan','Stefan','Marko','Petar','Martin','David','Filip','Nikola','Vladimir','Andrej','Darko','Igor','Goran','Dejan'])[((gs * 13) % 15) + 1] AS tatkovo_ime,
           make_date(
               (1961 + ((gs * 37) % 44))::int,
               (1 + ((gs * 17) % 12))::int,
               (1 + ((gs * 19) % 28))::int
           ) AS birth_date,
           (ARRAY['45','46','47','48','49','50','51','52','53','54'])[((gs * 23) % 10) + 1] AS city_code
    FROM generate_series(1, 10000) gs
), person AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY birth_date, city_code, gender
               ORDER BY gs
           ) AS embg_seq
    FROM person_raw
)
SELECT gs,
       user_id,
       ime,
       prezime,
       tatkovo_ime,
       birth_date,
       LPAD(EXTRACT(DAY FROM birth_date)::int::text, 2, '0') ||
       LPAD(EXTRACT(MONTH FROM birth_date)::int::text, 2, '0') ||
       LPAD((EXTRACT(YEAR FROM birth_date)::int % 1000)::text, 3, '0') ||
       city_code ||
       CASE WHEN gender = 'M' THEN '0' ELSE '5' END ||
       LPAD(embg_seq::text, 3, '0') AS embg
FROM person;

INSERT INTO raboti_vo (vraboten_id, filijala_id, raboti_od, raboti_do)
SELECT gs,
       ((gs - 1) % 100) + 1,
       CURRENT_DATE - ((30 + floor(random() * 2970))::int),
       NULL
FROM generate_series(1, 10000) gs;

-- ==========================================================
-- 5. CONTACT DATA
-- FIXED V3: no UNION ALL is used in contact data.
-- This avoids PostgreSQL UNION type resolution errors completely.
-- ==========================================================

-- TELEFON: clients
INSERT INTO telefon (telefon_id, telefonski_broj, tip_telefon, klient_id, vraboten_id, banka_id, filijala_id)
SELECT gs::int,
       ('+3897' || ((gs % 8) + 1)::text || LPAD(gs::text, 6, '0'))::varchar(20),
       'MOBILEN'::varchar(50),
       gs::int,
       NULL::int,
       NULL::int,
       NULL::int
FROM generate_series(1, 100000) AS gs;

-- TELEFON: employees
INSERT INTO telefon (telefon_id, telefonski_broj, tip_telefon, klient_id, vraboten_id, banka_id, filijala_id)
SELECT (100000 + gs)::int,
       ('+3892' || (200000 + gs)::text)::varchar(20),
       'SLUZBEN'::varchar(50),
       NULL::int,
       gs::int,
       NULL::int,
       NULL::int
FROM generate_series(1, 10000) AS gs;

-- TELEFON: banks
INSERT INTO telefon (telefon_id, telefonski_broj, tip_telefon, klient_id, vraboten_id, banka_id, filijala_id)
SELECT (110000 + gs)::int,
       ('+3892' || (300000 + gs)::text)::varchar(20),
       'CENTRALA'::varchar(50),
       NULL::int,
       NULL::int,
       gs::int,
       NULL::int
FROM generate_series(1, 10) AS gs;

-- TELEFON: branches
INSERT INTO telefon (telefon_id, telefonski_broj, tip_telefon, klient_id, vraboten_id, banka_id, filijala_id)
SELECT (110010 + gs)::int,
       ('+3892' || (400000 + gs)::text)::varchar(20),
       'FILIJALA'::varchar(50),
       NULL::int,
       NULL::int,
       NULL::int,
       gs::int
FROM generate_series(1, 100) AS gs;

-- EMAIL: clients
INSERT INTO email (email_id, email, tip_email, klient_id, vraboten_id, banka_id, filijala_id)
SELECT k.klient_id::int,
       lower(k.ime || '.' || k.prezime || k.klient_id || CASE WHEN k.klient_id % 2 = 0 THEN '@gmail.com' ELSE '@yahoo.com' END)::varchar(100),
       'LICEN'::varchar(50),
       k.klient_id::int,
       NULL::int,
       NULL::int,
       NULL::int
FROM klient k;

-- EMAIL: employees
INSERT INTO email (email_id, email, tip_email, klient_id, vraboten_id, banka_id, filijala_id)
SELECT (100000 + v.vraboten_id)::int,
       lower(v.ime || '.' || v.prezime || v.vraboten_id || '@bank.mk')::varchar(100),
       'SLUZBEN'::varchar(50),
       NULL::int,
       v.vraboten_id::int,
       NULL::int,
       NULL::int
FROM vraboten v;

-- EMAIL: banks
INSERT INTO email (email_id, email, tip_email, klient_id, vraboten_id, banka_id, filijala_id)
SELECT (110000 + gs)::int,
       ('contact' || gs || '@bank.mk')::varchar(100),
       'KONTAKT'::varchar(50),
       NULL::int,
       NULL::int,
       gs::int,
       NULL::int
FROM generate_series(1, 10) AS gs;

-- EMAIL: branches
INSERT INTO email (email_id, email, tip_email, klient_id, vraboten_id, banka_id, filijala_id)
SELECT (110010 + gs)::int,
       ('branch' || gs || '@bank.mk')::varchar(100),
       'FILIJALA'::varchar(50),
       NULL::int,
       NULL::int,
       NULL::int,
       gs::int
FROM generate_series(1, 100) AS gs;

-- ADRESA: clients
INSERT INTO adresa (adresa_id, drzava, grad, opstina, naselba, ulica, broj, stanben_broj, tip_adresa, klient_id, vraboten_id, banka_id, filijala_id)
SELECT gs::int,
       'Makedonija'::varchar(100),
       (ARRAY['Skopje','Bitola','Ohrid','Prilep','Tetovo','Kumanovo','Veles','Stip','Strumica','Gostivar'])[(gs % 10) + 1]::varchar(100),
       (ARRAY['Centar','Karposh','Aerodrom','Gazi Baba','Kisela Voda','Chair','Bitola','Ohrid','Prilep','Tetovo'])[(gs % 10) + 1]::varchar(100),
       (ARRAY['Debar Maalo','Kapistec','Karposh 1','Novo Lisiche','Avtokomanda','Bair','Varosh','Dva Bresta','Senjak','Bansko'])[(gs % 10) + 1]::varchar(100),
       (ARRAY['Partizanska','Ilindenska','Makedonija','Vodnjanska','Dame Gruev','Jane Sandanski','ASNOM','Goce Delcev','11 Oktomvri','Orce Nikolov','Boris Trajkovski','Krste Misirkov','Kuzman Josifovski Pitu'])[(gs % 13) + 1]::varchar(150),
       ((gs % 200) + 1)::text::varchar(20),
       ((gs % 40) + 1)::text::varchar(20),
       'KLIENT'::varchar(50),
       gs::int,
       NULL::int,
       NULL::int,
       NULL::int
FROM generate_series(1, 100000) AS gs;

-- ADRESA: employees
INSERT INTO adresa (adresa_id, drzava, grad, opstina, naselba, ulica, broj, stanben_broj, tip_adresa, klient_id, vraboten_id, banka_id, filijala_id)
SELECT (100000 + gs)::int,
       'Makedonija'::varchar(100),
       (ARRAY['Skopje','Bitola','Ohrid','Prilep','Tetovo','Kumanovo','Veles','Stip','Strumica','Gostivar'])[(gs % 10) + 1]::varchar(100),
       (ARRAY['Centar','Karposh','Aerodrom','Gazi Baba','Kisela Voda','Chair','Bitola','Ohrid','Prilep','Tetovo'])[(gs % 10) + 1]::varchar(100),
       (ARRAY['Debar Maalo','Kapistec','Karposh 1','Novo Lisiche','Avtokomanda','Bair','Varosh','Dva Bresta','Senjak','Bansko'])[(gs % 10) + 1]::varchar(100),
       (ARRAY['Partizanska','Ilindenska','Makedonija','Vodnjanska','Dame Gruev','Jane Sandanski','ASNOM','Goce Delcev','11 Oktomvri','Orce Nikolov','Boris Trajkovski','Krste Misirkov','Kuzman Josifovski Pitu'])[(gs % 13) + 1]::varchar(150),
       ((gs % 200) + 1)::text::varchar(20),
       ((gs % 40) + 1)::text::varchar(20),
       'VRABOTEN'::varchar(50),
       NULL::int,
       gs::int,
       NULL::int,
       NULL::int
FROM generate_series(1, 10000) AS gs;

-- ADRESA: banks
INSERT INTO adresa (adresa_id, drzava, grad, opstina, naselba, ulica, broj, stanben_broj, tip_adresa, klient_id, vraboten_id, banka_id, filijala_id)
SELECT (110000 + gs)::int,
       'Makedonija'::varchar(100),
       (ARRAY['Skopje','Bitola','Ohrid','Prilep','Tetovo','Kumanovo','Veles','Stip','Strumica','Gostivar'])[(gs % 10) + 1]::varchar(100),
       (ARRAY['Centar','Karposh','Aerodrom','Gazi Baba','Kisela Voda','Chair','Bitola','Ohrid','Prilep','Tetovo'])[(gs % 10) + 1]::varchar(100),
       (ARRAY['Debar Maalo','Kapistec','Karposh 1','Novo Lisiche','Avtokomanda','Bair','Varosh','Dva Bresta','Senjak','Bansko'])[(gs % 10) + 1]::varchar(100),
       (ARRAY['Partizanska','Ilindenska','Makedonija','Vodnjanska','Dame Gruev','Jane Sandanski','ASNOM','Goce Delcev','11 Oktomvri','Orce Nikolov','Boris Trajkovski','Krste Misirkov','Kuzman Josifovski Pitu'])[(gs % 13) + 1]::varchar(150),
       ((gs % 200) + 1)::text::varchar(20),
       ((gs % 40) + 1)::text::varchar(20),
       'BANKA'::varchar(50),
       NULL::int,
       NULL::int,
       gs::int,
       NULL::int
FROM generate_series(1, 10) AS gs;

-- ADRESA: branches
INSERT INTO adresa (adresa_id, drzava, grad, opstina, naselba, ulica, broj, stanben_broj, tip_adresa, klient_id, vraboten_id, banka_id, filijala_id)
SELECT (110010 + gs)::int,
       'Makedonija'::varchar(100),
       (ARRAY['Skopje','Bitola','Ohrid','Prilep','Tetovo','Kumanovo','Veles','Stip','Strumica','Gostivar'])[(gs % 10) + 1]::varchar(100),
       (ARRAY['Centar','Karposh','Aerodrom','Gazi Baba','Kisela Voda','Chair','Bitola','Ohrid','Prilep','Tetovo'])[(gs % 10) + 1]::varchar(100),
       (ARRAY['Debar Maalo','Kapistec','Karposh 1','Novo Lisiche','Avtokomanda','Bair','Varosh','Dva Bresta','Senjak','Bansko'])[(gs % 10) + 1]::varchar(100),
       (ARRAY['Partizanska','Ilindenska','Makedonija','Vodnjanska','Dame Gruev','Jane Sandanski','ASNOM','Goce Delcev','11 Oktomvri','Orce Nikolov','Boris Trajkovski','Krste Misirkov','Kuzman Josifovski Pitu'])[(gs % 13) + 1]::varchar(150),
       ((gs % 200) + 1)::text::varchar(20),
       ((gs % 40) + 1)::text::varchar(20),
       'FILIJALA'::varchar(50),
       NULL::int,
       NULL::int,
       NULL::int,
       gs::int
FROM generate_series(1, 100) AS gs;

-- ==========================================================
-- 6. SERVICES, CREDITS, CONTRACTS
-- ==========================================================

INSERT INTO usluga (usluga_id, ime, opis, datum_od, datum_do, tip_usluga, status, banka_id, filijala_id)
SELECT gs,
       'Usluga_' || gs,
       'Opis za usluga ' || gs,
       CURRENT_DATE - ((100 + floor(random() * 1900))::int),
       NULL,
       (ARRAY['SMETKA','KREDIT','DEPOZIT','KARTICKA','ONLINE_BANKING'])[(gs % 5) + 1],
       'AKTIVNA',
       ((((gs - 1) % 100)) % 10) + 1,
       ((gs - 1) % 100) + 1
FROM generate_series(1, 500) gs;

INSERT INTO tip_kredit (tip_kredit_id, tip, opis)
VALUES
    (1, 'Stanben kredit', 'Kredit za stan'),
    (2, 'Potrosuvacki kredit', 'Gotovinski kredit'),
    (3, 'Avto kredit', 'Kredit za vozilo'),
    (4, 'Studentski kredit', 'Kredit za studenti');

INSERT INTO kredit (kredit_id, kamatna_stapka, rok_otplata, iznos_kredit, mesecna_rata, tip_kredit_id, usluga_id, valuta_id)
WITH base AS (
    SELECT gs,
           round((500 + random() * 99500)::numeric, 2) AS principal,
           (ARRAY[12,24,36,48,60,84,120,240,360])[(floor(random()*9)::int)+1] AS months,
           round((2.5 + random() * 7)::numeric, 2) AS rate
    FROM generate_series(1, 50000) gs
)
SELECT gs,
       rate,
       months,
       principal,
       round((principal / months) * (1 + rate / 100), 2),
       ((gs - 1) % 4) + 1,
       ((gs - 1) % 500) + 1,
       ((gs - 1) % 5) + 1
FROM base;

INSERT INTO dogovor (dogovor_id, naslov, datum_kreiranje, datum_posledna_promena, datum_potpisuvanje, status, klient_id, banka_id, usluga_id, filijala_id)
WITH base AS (
    SELECT gs,
           CURRENT_DATE - (floor(random() * 2500)::int) AS created,
           (ARRAY['KREIRAN','POTPISAN','POTPISAN','POTPISAN','OTKAZAN','ISTECEN'])[(floor(random()*6)::int)+1] AS status,
           ((gs - 1) % 500) + 1 AS usluga_id,
           ((gs - 1) % 100) + 1 AS filijala_id
    FROM generate_series(1, 120000) gs
)
SELECT gs,
       'Dogovor za bankarska usluga - ' || EXTRACT(YEAR FROM created)::int || '/' || LPAD(gs::text, 6, '0'),
       created,
       created + (floor(random() * 30)::int),
       CASE WHEN status = 'KREIRAN' THEN NULL ELSE created + (floor(random() * 20)::int) END,
       status,
       ((gs - 1) % 100000) + 1,
       ((filijala_id - 1) % 10) + 1,
       usluga_id,
       filijala_id
FROM base;

INSERT INTO potpisnik (potpisnik_id, datum_potpisuvanje, klient_id, dogovor_id)
SELECT gs,
       CURRENT_DATE - (floor(random() * 2000)::int),
       ((gs - 1) % 100000) + 1,
       gs
FROM generate_series(1, 120000) gs;

-- ==========================================================
-- 7. ACCOUNTS, DEPOSITS AND CARDS
-- ==========================================================

INSERT INTO smetka (smetka_id, broj_smetka, datum_otvaranje, status, tip_smetka, usluga_id, klient_id, kredit_id, banka_id, valuta_id, saldo)
WITH base AS (
    SELECT gs,
           CASE
               WHEN random() < 0.62 THEN 'TEKOVNA'
               WHEN random() < 0.85 THEN 'DEVIZNA'
               ELSE 'STEDNA'
           END AS tip_smetka,
           CASE
               WHEN random() < 0.88 THEN 'AKTIVNA'
               WHEN random() < 0.96 THEN 'BLOKIRANA'
               ELSE 'ZATVORENA'
           END AS status
    FROM generate_series(1, 250000) gs
), enriched AS (
    SELECT gs,
           tip_smetka,
           status,
           CASE
               WHEN tip_smetka = 'TEKOVNA' THEN CASE WHEN random() < 0.92 THEN 1 ELSE 2 + floor(random()*4)::int END
               WHEN tip_smetka = 'DEVIZNA' THEN 2 + floor(random()*4)::int
               ELSE CASE WHEN random() < 0.65 THEN 1 ELSE 2 + floor(random()*4)::int END
           END AS valuta_id
    FROM base
)
SELECT gs,
       RIGHT('300000000000' || LPAD(gs::text, 8, '0'), 20),
       CURRENT_DATE - (floor(random() * 3000)::int),
       status,
       tip_smetka,
       ((gs - 1) % 500) + 1,
       ((gs - 1) % 100000) + 1,
       CASE WHEN gs % 5 = 0 THEN ((gs - 1) % 50000) + 1 ELSE NULL END,
       ((gs - 1) % 10) + 1,
       valuta_id,
       CASE
           WHEN status = 'ZATVORENA' THEN round((random() * 100)::numeric, 2)
           WHEN status = 'BLOKIRANA' AND random() < 0.25 THEN round((-100 - random() * 24900)::numeric, 2)
           WHEN tip_smetka = 'TEKOVNA' AND valuta_id = 1 THEN round((500 + random() * 349500)::numeric, 2)
           WHEN tip_smetka = 'TEKOVNA' THEN round((20 + random() * 7980)::numeric, 2)
           WHEN tip_smetka = 'DEVIZNA' THEN round((50 + random() * 29950)::numeric, 2)
           WHEN tip_smetka = 'STEDNA' AND valuta_id = 1 THEN round((10000 + random() * 1490000)::numeric, 2)
           ELSE round((200 + random() * 79800)::numeric, 2)
       END AS saldo
FROM enriched;

INSERT INTO depozit (depozit_id, iznos_depozit, rok_depozit, kamatna_stapka, datum_odobruvanje, datum_aktiviranje, momentalna_sostojba, tip_depozit, usluga_id, smetka_id, valuta_id)
WITH base AS (
    SELECT gs,
           round((100 + random() * 99900)::numeric, 2) AS dep,
           CURRENT_DATE - ((1 + floor(random() * 2000))::int) AS approved
    FROM generate_series(1, 70000) gs
)
SELECT gs,
       dep,
       (ARRAY[3,6,12,24,36,60])[(floor(random()*6)::int)+1],
       round((0.5 + random() * 5)::numeric, 2),
       approved,
       approved + (floor(random() * 10)::int),
       round((dep * (1.0 + random() * 0.2))::numeric, 2),
       (ARRAY['OROCEN','VIDEN','STEDEN'])[(gs % 3) + 1],
       ((gs - 1) % 500) + 1,
       ((gs - 1) % 250000) + 1,
       ((gs - 1) % 5) + 1
FROM base;

INSERT INTO tip_karticka (tip_karticka_id, ime, opis)
VALUES
    (1, 'Debitna', 'Debitna karticka'),
    (2, 'Kreditna', 'Kreditna karticka'),
    (3, 'Prepaid', 'Prepaid karticka');

INSERT INTO karticka (karticka_id, broj_karticka, datum_izdavanje, datum_istekuvanje, cvc_kod, status, smetka_id, tip_karticka_id)
WITH base AS (
    SELECT gs,
           CURRENT_DATE - (floor(random() * 1500)::int) AS issued
    FROM generate_series(1, 180000) gs
)
SELECT gs,
       RIGHT('4' || LPAD(gs::text, 15, '0'), 16),
       issued,
       issued + (365 * (ARRAY[3,4,5])[(floor(random()*3)::int)+1]),
       LPAD((gs % 1000)::text, 3, '0'),
       CASE WHEN gs % 100 = 0 THEN 'BLOKIRANA' ELSE 'AKTIVNA' END,
       ((gs - 1) % 250000) + 1,
       ((gs - 1) % 3) + 1
FROM base;

-- ==========================================================
-- 8. NOTIFICATIONS
-- ==========================================================

INSERT INTO izvestuvanje (izvestuvanje_id, naslov, poraka, datum_isprakjanje, klient_id, banka_id)
SELECT gs,
       (ARRAY[
           'Uspesna transakcija', 'Priliv na smetka', 'Potsetnik za rata', 'Nisko saldo',
           'Promena na kursna lista', 'Nova bankarska ponuda', 'Bezbednosno izvestuvanje',
           'Karticka pred istek', 'Blokirana karticka', 'Potpisuvanje dogovor',
           'Promena na status', 'Izvod dostapen'
       ])[(floor(random()*12)::int)+1],
       'Avtomatsko bankarsko izvestuvanje za klientot. Iznos: ' || round((100 + random()*150000)::numeric, 2)::text || ' MKD.',
       CURRENT_DATE - (floor(random() * 1000)::int),
       ((gs - 1) % 100000) + 1,
       ((gs - 1) % 10) + 1
FROM generate_series(1, 500000) gs;

-- ==========================================================
-- 9. PAYMENT ORDERS AND TRANSACTIONS
-- ==========================================================

INSERT INTO nalog (
    nalog_id, datum_na_valuta, povikuvanje_na_broj_odobruvanje, iznos,
    danocen_broj_embg, svrha_na_plakjanje, smetka_primalac_id, cel_na_doznaka,
    hitno, uplateno_mesto, smetka_na_budetski_korisnik_edinka_korisnik,
    prihodna_sifra, programa, nacin_plakjanje, nalogodavac_id,
    danocen_broj_primalac, smetka_nalogodavac_id, smetka_nalogoprimac_id,
    smetka_nalogoprimac, valuta_id, potpisnik_id
)
WITH base AS (
    SELECT gs,
           ((gs - 1) % 250000) + 1 AS from_acc_id,
           -- Користиме модуло со поместување за да не биде истата сметка
           ((gs + 12345) % 250000) + 1 AS to_acc_id,
           (10 + (random() * 20000))::numeric AS amount,
           CURRENT_DATE - (floor(random() * 730))::int AS val_date
    FROM generate_series(1, 1000000) gs -- Оптимално 1 милион за тест во Фаза 4
)
SELECT gs, 
       val_date,
       'REF-' || gs || '-' || EXTRACT(YEAR FROM val_date),
       round(amount, 2),
       'EMBG' || LPAD(((gs % 100000) + 1)::text, 9, '0'),
       'Plakjanje na usluga/faktura br.' || gs,
       to_acc_id,
       'Transfer na sredstva',
       (gs % 10 = 0), -- на секој 10-ти налог е итен
       'Skopje, Filijala ' || ((gs % 100) + 1),
       NULL, NULL, NULL, -- Буџетски полиња
       'PP30',
       ((gs - 1) % 100000) + 1,
       'EDB' || LPAD(((gs % 50000) + 1)::text, 10, '0'),
       from_acc_id,
       to_acc_id,
       'MK30000' || LPAD(to_acc_id::text, 10, '0'),
       ((gs - 1) % 5) + 1,
       ((gs - 1) % 120000) + 1
FROM base;

-- Генерирање на трансакции директно од налозите
INSERT INTO transakcija (transakcija_id, datum_transakcija, vreme_izvrsena, iznos, status, smetka_isprakjac_id, smetka_primac_id, nalog_id, valuta_id)
SELECT nalog_id,
       datum_na_valuta,
       (CURRENT_TIME - (random() * interval '20 hours')),
       iznos,
       'ZAVRSENA',
       smetka_nalogodavac_id,
       smetka_nalogoprimac_id,
       nalog_id,
       valuta_id
FROM nalog;

-- ==========================================================
-- LOAN REPAYMENTS (RATA_KREDIT)
-- ==========================================================

INSERT INTO rata_kredit (rata_kredit_id, iznos_rata, datum_dospevanje, datum_plakjanje, status_rata, kredit_id, transakcija_id)
SELECT gs,
       k.mesecna_rata,
       k.created_at + (interval '1 month' * ((gs - 1) % 12 + 1)),
       CASE WHEN random() < 0.9 THEN k.created_at + (interval '1 month' * ((gs - 1) % 12 + 1)) ELSE NULL END,
       CASE WHEN random() < 0.9 THEN 'PLATENA' ELSE 'NEDOSPENA' END,
       k.kredit_id,
       CASE WHEN random() < 0.9 THEN ((gs - 1) % 1000000) + 1 ELSE NULL END
FROM (
    SELECT kr.kredit_id, kr.mesecna_rata, u.datum_od as created_at
    FROM kredit kr
    JOIN usluga u ON kr.usluga_id = u.usluga_id
    LIMIT 50000
) k
CROSS JOIN generate_series(1, 12) gs; -- Генерираме 12 рати за секој кредит