CREATE EXTENSION IF NOT EXISTS pgcrypto;
DROP EXTENSION pgcrypto CASCADE;

--Bank_User
INSERT INTO Bank_user (username, password_hash, role_id, employee_id)
SELECT 'user' || e.employee_id,

       ('$2a$10$' || encode(digest(gen_random_uuid()::text, 'sha256'), 'hex')) AS password_hash,

       CASE e.position

           -- ADMIN
           WHEN 'Regional Manager' THEN 1
           WHEN 'System Administrator' THEN 1
           WHEN 'Database Administrator' THEN 1


           -- MANAGER
           WHEN 'Branch Manager' THEN 2
           WHEN 'Operations Manager' THEN 2
           WHEN 'HR Manager' THEN 2
           WHEN 'HR Specialist' THEN 2
           WHEN 'Financial Analyst' THEN 2
           WHEN 'Accountant' THEN 2
           WHEN 'Marketing Specialist' THEN 2
           WHEN 'Sales Officer' THEN 2

           -- TELLER
           WHEN 'Teller' THEN 3
           WHEN 'Cashier' THEN 3

           -- CUSTOMER_SERVICE
           WHEN 'Customer Service Representative' THEN 4
           WHEN 'Personal Banker' THEN 4
           WHEN 'Loan Officer' THEN 4
           WHEN 'Credit Analyst' THEN 4
           WHEN 'Mortgage Specialist' THEN 4

           -- AUDITOR
           WHEN 'Risk Analyst' THEN 5
           WHEN 'Compliance Officer' THEN 5
           WHEN 'AML Specialist' THEN 5
           WHEN 'Internal Auditor' THEN 5

           -- IT_SUPPORT
           WHEN 'IT Support Specialist' THEN 6
           END                                                                 AS role_id,

       e.employee_id

FROM Employee e;
SELECT r.role_name,
       COUNT(bu.user_id)                                                    AS total_users,
       ROUND(COUNT(bu.user_id) * 100.0 / SUM(COUNT(bu.user_id)) OVER (), 2) AS pct
FROM Bank_user bu
         JOIN Role r ON r.role_id = bu.role_id
GROUP BY r.role_name;


--Branch_employee
WITH branch_enum AS (SELECT branch_id, row_number() OVER () AS rn
                     FROM Branch),
     branch_cnt AS (SELECT COUNT(*) AS cnt
                    FROM branch_enum),
     employee_enum AS (SELECT employee_id, row_number() OVER (ORDER BY random()) AS rn
                       FROM Employee),
     employee_cnt AS (SELECT COUNT(*) AS cnt
                      FROM employee_enum),
     t AS (SELECT i,
                  CURRENT_DATE - (floor(random() * 2000))::int AS start_date,
                  CASE
                      WHEN r < 0.65 THEN 'ACTIVE'
                      WHEN r < 0.85 THEN 'INACTIVE'
                      WHEN r < 0.95 THEN 'SUSPENDED'
                      ELSE 'TEMPORARY_ASSIGNMENT'
                      END                                      AS status
           FROM (SELECT generate_series(1, 100000) AS i, random() AS r) x)

INSERT
INTO Branch_employee (branch_id,
                      employee_id,
                      start_date,
                      end_date,
                      status)
SELECT b.branch_id,
       e.employee_id,
       t.start_date,
       CASE
           WHEN t.status IN ('INACTIVE', 'SUSPENDED')
               THEN t.start_date + (1 + floor(random() * 1000)) * INTERVAL '1 day'
           END,
       t.status
FROM t
         JOIN branch_cnt bc ON true
         JOIN employee_cnt ec ON true
         JOIN branch_enum b
              ON b.rn = ((t.i - 1) % bc.cnt) + 1
         JOIN employee_enum e
              ON e.rn = ((t.i - 1) % ec.cnt) + 1;

--Daily_report
INSERT INTO Daily_report (report_date, total_transactions, total_amount, branch_id)
SELECT CURRENT_DATE - (floor(random() * 365))::int,
       (floor(random() * 1000))::int,
       round((random() * 500000)::numeric, 2),
       (1 + floor(random() * 101))::int
FROM generate_series(1, 100000);

--Counter
INSERT INTO Counter (counter_number, status, branch_id)
SELECT counter_number,
       (ARRAY ['OPEN','CLOSED','MAINTENANCE'])[floor(random() * 3) + 1],
       branch_id
FROM (SELECT generate_series(1, 500)          AS counter_number,
             (1 + floor(random() * 101))::int AS branch_id) t
ORDER BY random();

--Employee_counter
INSERT INTO Employee_counter (start_shift, end_shift, employee_id, counter_id)
SELECT start_shift,
       start_shift + INTERVAL '8 hours' + ((floor(random() * 45) - 15) || ' minutes')::interval,
       (1 + floor(random() * 100000))::int,
       (1 + floor(random() * 500))::int
FROM (SELECT date_trunc('day', CURRENT_TIMESTAMP - ((floor(random() * 30))::int * INTERVAL '1 day'))
                 + INTERVAL '7 hours 45 minutes'
                 + ((floor(random() * 31)) || ' minutes')::interval AS start_shift
      FROM generate_series(1, 100000)) t;

--Client
WITH ranked AS (SELECT c.client_id,
                       c.first_name,
                       c.last_name,
                       make_date(
                               (1950 + ((client_id::bigint * 7331) % 56))::int,
                               (1 + ((client_id::bigint * 2741) % 12))::int,
                               (1 + ((client_id::bigint * 1913) % 28))::int
                       )       AS birth_date,
                       CASE
                           WHEN c.first_name IN (
                                                 'Ana', 'Elena', 'Maja', 'Ivana', 'Katerina', 'Sonja', 'Vesna',
                                                 'Biljana',
                                                 'Daniela', 'Silvana', 'Irena', 'Suzana', 'Lidija', 'Violeta',
                                                 'Snezana',
                                                 'Gordana', 'Tatjana', 'Milica', 'Bojana', 'Natasa', 'Sara', 'Kristina',
                                                 'Marija', 'Sofija', 'Teodora', 'Dijana', 'Dragana', 'Angela',
                                                 'Liljana',
                                                 'Sandra', 'Jelena', 'Aleksandra', 'Jasmina', 'Marina', 'Jovana',
                                                 'Renata',
                                                 'Dusica', 'Zorica', 'Menka', 'Bisera', 'Smiljana', 'Cveta', 'Dobrila',
                                                 'Verica', 'Silvija', 'Rozalinda', 'Radmila', 'Neda', 'Monika',
                                                 'Milena',
                                                 'Dina', 'Rania', 'Samira', 'Fatima', 'Layla', 'Noura', 'Zainab',
                                                 'Hana',
                                                 'Hoda', 'Mariam', 'Rabia', 'Meryem', 'Hatice', 'Fatma', 'Emine',
                                                 'Havva',
                                                 'Elif', 'Ayse', 'Halime', 'Zeynep', 'Amira', 'Nadia', 'Salma',
                                                 'Yasmine',
                                                 'Samantha', 'Ashley', 'Kimberly', 'Rachel', 'Heather', 'Brittany',
                                                 'Emily',
                                                 'Lauren', 'Jessica', 'Melissa', 'Amanda', 'Tiffany', 'Crystal',
                                                 'Amber',
                                                 'Rebecca', 'Megan', 'Nicole', 'Christine', 'Katherine', 'Claudia',
                                                 'Sylvie',
                                                 'Monique', 'Sophie', 'Marie', 'Isabelle', 'Brigitte', 'Nathalie',
                                                 'Veronique',
                                                 'Danielle', 'Francesca', 'Federica', 'Alessia', 'Elisa', 'Chiara',
                                                 'Giulia',
                                                 'Paola', 'Martina', 'Sabine', 'Ursula', 'Helga', 'Ingrid', 'Hannah',
                                                 'Emma',
                                                 'Anna', 'Sarah', 'Laura', 'Olivia', 'Sophia', 'Elizabeth', 'Amy',
                                                 'Abigail',
                                                 'Stephanie', 'Katarina', 'Aneta', 'Emilija', 'Petra', 'Blagica',
                                                 'Valentina',
                                                 'Natasha', 'Lina', 'Andrea', 'Catherine'
                               ) THEN '455'
                           ELSE '450'
                           END AS gender_digits,
                       ROW_NUMBER() OVER (
                           PARTITION BY
                               1940 + (client_id % 60),
                               1 + (client_id * 13) % 12,
                               1 + (client_id * 19) % 28,
                               CASE
                                   WHEN c.first_name IN (
                                                         'Ana', 'Elena', 'Maja', 'Ivana', 'Katerina', 'Sonja', 'Vesna',
                                                         'Biljana',
                                                         'Daniela', 'Silvana', 'Irena', 'Suzana', 'Lidija', 'Violeta',
                                                         'Snezana',
                                                         'Gordana', 'Tatjana', 'Milica', 'Bojana', 'Natasa', 'Sara',
                                                         'Kristina',
                                                         'Marija', 'Sofija', 'Teodora', 'Dijana', 'Dragana', 'Angela',
                                                         'Liljana',
                                                         'Sandra', 'Jelena', 'Aleksandra', 'Jasmina', 'Marina',
                                                         'Jovana', 'Renata',
                                                         'Dusica', 'Zorica', 'Menka', 'Bisera', 'Smiljana', 'Cveta',
                                                         'Dobrila',
                                                         'Verica', 'Silvija', 'Rozalinda', 'Radmila', 'Neda', 'Monika',
                                                         'Milena',
                                                         'Dina', 'Rania', 'Samira', 'Fatima', 'Layla', 'Noura',
                                                         'Zainab', 'Hana',
                                                         'Hoda', 'Mariam', 'Rabia', 'Meryem', 'Hatice', 'Fatma',
                                                         'Emine', 'Havva',
                                                         'Elif', 'Ayse', 'Halime', 'Zeynep', 'Amira', 'Nadia', 'Salma',
                                                         'Yasmine',
                                                         'Samantha', 'Ashley', 'Kimberly', 'Rachel', 'Heather',
                                                         'Brittany', 'Emily',
                                                         'Lauren', 'Jessica', 'Melissa', 'Amanda', 'Tiffany', 'Crystal',
                                                         'Amber',
                                                         'Rebecca', 'Megan', 'Nicole', 'Christine', 'Katherine',
                                                         'Claudia', 'Sylvie',
                                                         'Monique', 'Sophie', 'Marie', 'Isabelle', 'Brigitte',
                                                         'Nathalie', 'Veronique',
                                                         'Danielle', 'Francesca', 'Federica', 'Alessia', 'Elisa',
                                                         'Chiara', 'Giulia',
                                                         'Paola', 'Martina', 'Sabine', 'Ursula', 'Helga', 'Ingrid',
                                                         'Hannah', 'Emma',
                                                         'Anna', 'Sarah', 'Laura', 'Olivia', 'Sophia', 'Elizabeth',
                                                         'Amy', 'Abigail',
                                                         'Stephanie', 'Katarina', 'Aneta', 'Emilija', 'Petra',
                                                         'Blagica', 'Valentina',
                                                         'Natasha', 'Lina', 'Andrea', 'Catherine'
                                       ) THEN '455'
                                   ELSE '450'
                                   END
                           ORDER BY c.client_id
                           )   AS rn
                FROM Client c
                WHERE c.embg IS NULL)
UPDATE Client c
SET embg            = LPAD(EXTRACT(DAY FROM r.birth_date)::INT::TEXT, 2, '0') ||
                      LPAD(EXTRACT(MONTH FROM r.birth_date)::INT::TEXT, 2, '0') ||
                      LPAD((EXTRACT(YEAR FROM r.birth_date)::INT % 1000)::TEXT, 3, '0') ||
                      r.gender_digits || LPAD(r.rn::TEXT, 3, '0'),

    phone           = '+3897' || LPAD(((c.client_id * 7 + 1234567) % 9000000 + 1000000)::TEXT, 7, '0'),

    email           = LOWER(REPLACE(c.first_name, ' ', '')) || '.' ||
                      LOWER(REPLACE(REPLACE(c.last_name, ' ', ''), '-', '')) ||
                      c.client_id::TEXT || '@gmail.com',

    address         = (100 + (c.client_id * 17 + 3) % 900)::TEXT || ' - ' ||
                      (ARRAY [
                          'Partizanska','Makedonska','Ilindenska','Vasil Glavinov','Dame Gruev',
                          'Skupi','Kozle','Lerin','Karposh','Aerodrom','Butel','Gazi Baba',
                          'Jane Sandanski','Nikola Tesla','Sv Kliment Ohridski','Vardar',
                          'Treska','Bregalnica','Pcinja','Goce Delcev','Hristo Tatarcev',
                          'Boro Petrusevski','Naum Naumovski','Metodija Andonov','Ss Kiril i Metodij'
                          ])[1 + (c.client_id * 31) % 25] || ' - ' ||
                      (1 + (c.client_id * 11) % 200)::TEXT,

    status          = (ARRAY ['ACTIVE','ACTIVE','ACTIVE','INACTIVE','BLOCKED'])
        [1 + (c.client_id * 7) % 5],

    date_registered = r.birth_date +
                      ((c.client_id * 97 + 6574) %
                       (CURRENT_DATE - r.birth_date - 365 * 18))::INT
        + 365 * 18,

    branch_id       = (1 + floor(random() * 101))::int

FROM ranked r
WHERE c.client_id = r.client_id;


--Client_user
INSERT INTO Client_user (username, password_hash, last_login, status, client_id)
SELECT 'client' || client_id,
       ('$2a$10$' || encode(digest(gen_random_uuid()::text, 'sha256'), 'hex')) AS password_hash,
       CURRENT_TIMESTAMP - (floor(random() * 365) || ' days')::interval,
       (ARRAY ['ACTIVE','LOCKED','DISABLED'])[floor(random() * 3) + 1],
       client_id
FROM Client
WHERE random() < 0.7;


--Loan
WITH ordered_clients AS (SELECT client_id,
                                (ROW_NUMBER() OVER (ORDER BY client_id) - 1) AS rn
                         FROM Client)
INSERT
INTO Loan (loan_type,
           amount,
           interest_rate,
           start_date,
           end_date,
           status,
           approved_date,
           client_id,
           employee_id)
SELECT (ARRAY ['HOME','CAR','PERSONAL','BUSINESS'])[floor(random() * 4) + 1],
       round((1000 + random() * 90000)::numeric, 2),
       round((1 + random() * 9)::numeric, 2),
       base.start_date,
       CASE
           WHEN base.status = 'CLOSED'
               THEN base.start_date + (floor(random() * 365) + 1)::int
           ELSE NULL
           END,
       base.status,
       CASE
           WHEN base.status = 'APPROVED'
               THEN base.start_date + (floor(random() * 100))::int
           ELSE NULL
           END,
       c.client_id,
       base.employee_id
FROM (SELECT e.employee_id,
             s.status,
             CURRENT_DATE - (floor(random() * 1000))::int AS start_date,
             (ROW_NUMBER() OVER () - 1)                   AS rn
      FROM Employee e
               JOIN (VALUES ('PENDING'),
                            ('APPROVED'),
                            ('REJECTED'),
                            ('CLOSED')) AS s(status) ON true
      WHERE e.position IN (
                           'Customer Service Representative',
                           'Personal Banker',
                           'Loan Officer',
                           'Credit Analyst',
                           'Mortgage Specialist'
          )
      LIMIT 100000) base
         JOIN ordered_clients c ON c.rn = base.rn % (SELECT COUNT(*) FROM ordered_clients);


--Collateral
WITH types AS (SELECT unnest(ARRAY [
    'REAL_ESTATE',
    'VEHICLE',
    'GOLD',
    'EQUIPMENT',
    'SAVINGS_ACCOUNT'
    ]) AS type),
     shuffled_types AS (SELECT type,
                               ROW_NUMBER() OVER (ORDER BY random()) AS rn
                        FROM types),
     ordered_loans AS (SELECT loan_id,
                              ROW_NUMBER() OVER (ORDER BY loan_id) AS rn
                       FROM Loan),
     ordered_clients AS (SELECT client_id,
                                ROW_NUMBER() OVER (ORDER BY client_id) AS rn
                         FROM Client),
     paired AS (SELECT l.loan_id, c.client_id, t.type
                FROM ordered_loans l
                         JOIN ordered_clients c ON l.rn = c.rn
                         JOIN shuffled_types t ON t.rn = ((l.rn - 1) % 5) + 1)

INSERT
INTO Collateral (type,
                 description,
                 value,
                 status,
                 date_added,
                 loan_id,
                 client_id)
SELECT p.type,

       CASE p.type
           WHEN 'REAL_ESTATE' THEN 'Apartment collateral'
           WHEN 'VEHICLE' THEN 'Car collateral - sedan'
           WHEN 'GOLD' THEN 'Gold jewelry set'
           WHEN 'EQUIPMENT' THEN 'Office equipment'
           WHEN 'SAVINGS_ACCOUNT' THEN 'Bank savings pledge'
           END,

       round((5000 + random() * 150000)::numeric, 2),

       (ARRAY ['ACTIVE', 'RELEASED', 'PLEDGED', 'DEFAULTED'])[floor(random() * 4) + 1],

       CURRENT_DATE - (floor(random() * 365))::int,

       p.loan_id,
       p.client_id
FROM paired p;


--Loan_installment
WITH ordered_loans AS (SELECT loan_id,
                              start_date,
                              amount,
                              ROW_NUMBER() OVER (ORDER BY loan_id) AS rn
                       FROM Loan),
     installments AS (SELECT ol.loan_id,

                             ROW_NUMBER() OVER (
                                 PARTITION BY ol.loan_id
                                 ORDER BY gs
                                 )                                                 AS installment_number,

                             (ol.start_date + (gs - 1) * INTERVAL '1 month')::date AS due_date,

                             ROUND((
                                       ol.amount /
                                       CASE
                                           WHEN ol.amount <= 1000 THEN 3
                                           WHEN ol.amount <= 3000 THEN 6
                                           WHEN ol.amount <= 5000 THEN 12
                                           WHEN ol.amount <= 10000 THEN 24
                                           WHEN ol.amount <= 15000 THEN 36
                                           WHEN ol.amount <= 20000 THEN 48
                                           WHEN ol.amount <= 25000 THEN 60
                                           WHEN ol.amount <= 30000 THEN 72
                                           WHEN ol.amount <= 35000 THEN 84
                                           WHEN ol.amount <= 40000 THEN 96
                                           WHEN ol.amount <= 45000 THEN 108
                                           WHEN ol.amount <= 50000 THEN 120
                                           WHEN ol.amount <= 60000 THEN 132
                                           WHEN ol.amount <= 70000 THEN 144
                                           WHEN ol.amount <= 80000 THEN 156
                                           WHEN ol.amount <= 90000 THEN 168
                                           WHEN ol.amount <= 100000 THEN 180
                                           WHEN ol.amount <= 120000 THEN 192
                                           WHEN ol.amount <= 140000 THEN 204
                                           WHEN ol.amount <= 160000 THEN 216
                                           WHEN ol.amount <= 180000 THEN 228
                                           WHEN ol.amount <= 200000 THEN 240
                                           WHEN ol.amount <= 220000 THEN 252
                                           WHEN ol.amount <= 240000 THEN 264
                                           WHEN ol.amount <= 260000 THEN 276
                                           WHEN ol.amount <= 280000 THEN 288
                                           ELSE 300
                                           END
                                       )::numeric, 2)                              AS amount,

                             CASE
                                 WHEN (ol.start_date + (gs - 1) * INTERVAL '1 month') < CURRENT_DATE
                                     AND random() < 0.6 THEN 'PAID'
                                 WHEN (ol.start_date + (gs - 1) * INTERVAL '1 month') < CURRENT_DATE
                                     THEN 'LATE'
                                 ELSE 'PENDING'
                                 END                                               AS status

                      FROM ordered_loans ol
                               CROSS JOIN generate_series(
                              1,
                              CASE
                                  WHEN ol.amount <= 1000 THEN 3
                                  WHEN ol.amount <= 3000 THEN 6
                                  WHEN ol.amount <= 5000 THEN 12
                                  WHEN ol.amount <= 10000 THEN 24
                                  WHEN ol.amount <= 15000 THEN 36
                                  WHEN ol.amount <= 20000 THEN 48
                                  WHEN ol.amount <= 25000 THEN 60
                                  WHEN ol.amount <= 30000 THEN 72
                                  WHEN ol.amount <= 35000 THEN 84
                                  WHEN ol.amount <= 40000 THEN 96
                                  WHEN ol.amount <= 45000 THEN 108
                                  WHEN ol.amount <= 50000 THEN 120
                                  WHEN ol.amount <= 60000 THEN 132
                                  WHEN ol.amount <= 70000 THEN 144
                                  WHEN ol.amount <= 80000 THEN 156
                                  WHEN ol.amount <= 90000 THEN 168
                                  WHEN ol.amount <= 100000 THEN 180
                                  WHEN ol.amount <= 120000 THEN 192
                                  WHEN ol.amount <= 140000 THEN 204
                                  WHEN ol.amount <= 160000 THEN 216
                                  WHEN ol.amount <= 180000 THEN 228
                                  WHEN ol.amount <= 200000 THEN 240
                                  WHEN ol.amount <= 220000 THEN 252
                                  WHEN ol.amount <= 240000 THEN 264
                                  WHEN ol.amount <= 260000 THEN 276
                                  WHEN ol.amount <= 280000 THEN 288
                                  ELSE 300
                                  END
                                          ) gs)
INSERT
INTO Loan_installment (installment_number,
                       due_date,
                       amount,
                       status,
                       paid_date,
                       loan_id)
SELECT installment_number,
       due_date,
       amount,
       status,

       CASE
           WHEN status = 'PAID'
               THEN due_date - (floor(random() * 5)::int) * INTERVAL '1 day'
           ELSE NULL
           END AS paid_date,

       loan_id
FROM installments;


--Exchange_rate
WITH ordered_currency AS (SELECT currency_id,
                                 ROW_NUMBER() OVER (ORDER BY currency_id) AS rn
                          FROM Currency),
     dates AS (SELECT generate_series(
                                      CURRENT_DATE - INTERVAL '29 days',
                                      CURRENT_DATE,
                                      INTERVAL '1 day'
                      )::date AS rate_date),
     currency_dates AS (SELECT oc.currency_id,
                               d.rate_date,
                               ROW_NUMBER() OVER (PARTITION BY oc.currency_id ORDER BY d.rate_date) AS seq
                        FROM ordered_currency oc
                                 JOIN dates d ON TRUE)
INSERT
INTO Exchange_rate (rate, date_updated, currency_id)
SELECT ROUND(
               CAST(
                       CASE cd.currency_id

                           
                           WHEN 2 THEN 1.000000 + (random() - 0.5) * 0.000

                        
                           WHEN 1 THEN 1.08 + (random() - 0.5) * 0.01
                           WHEN 3 THEN 61.50 + (random() - 0.5) * 0.50
                           WHEN 4 THEN 0.86 + (random() - 0.5) * 0.01
                           WHEN 5 THEN 160.00 + (random() - 0.5) * 2.00
                           WHEN 6 THEN 0.95 + (random() - 0.5) * 0.01
                           WHEN 7 THEN 1.47 + (random() - 0.5) * 0.02
                           WHEN 8 THEN 1.62 + (random() - 0.5) * 0.02
                           WHEN 9 THEN 1.75 + (random() - 0.5) * 0.02

                           
                           WHEN 10 THEN 11.00 + (random() - 0.5) * 0.10
                           WHEN 11 THEN 11.20 + (random() - 0.5) * 0.10
                           WHEN 12 THEN 7.45 + (random() - 0.5) * 0.08
                           WHEN 13 THEN 4.40 + (random() - 0.5) * 0.04
                           WHEN 14 THEN 24.50 + (random() - 0.5) * 0.20
                           WHEN 15 THEN 390.00 + (random() - 0.5) * 3.00
                           WHEN 16 THEN 118.00 + (random() - 0.5) * 1.00
                           WHEN 17 THEN 7.40 + (random() - 0.5) * 0.06
                           WHEN 18 THEN 1.96 + (random() - 0.5) * 0.02
                           WHEN 19 THEN 35.00 + (random() - 0.5) * 0.50
                           WHEN 20 THEN 95.00 + (random() - 0.5) * 1.00

                           
                           WHEN 21 THEN 7.80 + (random() - 0.5) * 0.06
                           WHEN 22 THEN 90.00 + (random() - 0.5) * 0.50
                           WHEN 23 THEN 5.50 + (random() - 0.5) * 0.05
                           WHEN 28 THEN 1450.00 + (random() - 0.5) * 15.00

                           
                           WHEN 24 THEN 20.00 + (random() - 0.5) * 0.20
                           WHEN 64 THEN 140.00 + (random() - 0.5) * 1.50
                           WHEN 78 THEN 1600.00 + (random() - 0.5) * 15.00

                           
                           WHEN 25 THEN 18.00 + (random() - 0.5) * 0.20
                           WHEN 87 THEN 950.00 + (random() - 0.5) * 10.00
                           WHEN 88 THEN 4200.00 + (random() - 0.5) * 40.00
                           WHEN 90 THEN 41.00 + (random() - 0.5) * 0.40
                           WHEN 94 THEN 530.00 + (random() - 0.5) * 5.00

                           
                           WHEN 26 THEN 1.35 + (random() - 0.5) * 0.01
                           WHEN 27 THEN 7.85 + (random() - 0.5) * 0.05
                           WHEN 29 THEN 3.67 + (random() - 0.5) * 0.005
                           WHEN 30 THEN 3.75 + (random() - 0.5) * 0.005
                           WHEN 31 THEN 3.64 + (random() - 0.5) * 0.005
                           WHEN 32 THEN 0.31 + (random() - 0.5) * 0.003
                           WHEN 33 THEN 0.38 + (random() - 0.5) * 0.003
                           WHEN 34 THEN 0.37 + (random() - 0.5) * 0.003
                           WHEN 35 THEN 49.00 + (random() - 0.5) * 0.80
                           WHEN 36 THEN 3.70 + (random() - 0.5) * 0.05
                           WHEN 37 THEN 35.50 + (random() - 0.5) * 0.40
                           WHEN 38 THEN 4.72 + (random() - 0.5) * 0.05
                           WHEN 39 THEN 16000.00 + (random() - 0.5) * 150.00
                           WHEN 40 THEN 56.50 + (random() - 0.5) * 0.60
                           WHEN 41 THEN 24500.00 + (random() - 0.5) * 200.00
                           WHEN 42 THEN 280.00 + (random() - 0.5) * 3.00
                           WHEN 43 THEN 110.00 + (random() - 0.5) * 1.00
                           WHEN 44 THEN 305.00 + (random() - 0.5) * 3.00
                           WHEN 45 THEN 133.50 + (random() - 0.5) * 1.50
                           WHEN 46 THEN 450.00 + (random() - 0.5) * 4.00
                           WHEN 47 THEN 39.50 + (random() - 0.5) * 0.60
                           WHEN 48 THEN 3.27 + (random() - 0.5) * 0.04
                           WHEN 49 THEN 2.68 + (random() - 0.5) * 0.03
                           WHEN 50 THEN 395.00 + (random() - 0.5) * 4.00
                           WHEN 51 THEN 1.70 + (random() - 0.5) * 0.015
                           WHEN 52 THEN 12800.00 + (random() - 0.5) * 100.00
                           WHEN 53 THEN 10.90 + (random() - 0.5) * 0.10
                           WHEN 54 THEN 89.00 + (random() - 0.5) * 0.80
                           WHEN 55 THEN 3450.00 + (random() - 0.5) * 30.00
                           WHEN 56 THEN 42000.00 + (random() - 0.5) * 400.00
                           WHEN 57 THEN 1310.00 + (random() - 0.5) * 12.00
                           WHEN 58 THEN 13000.00 + (random() - 0.5) * 100.00
                           WHEN 59 THEN 0.71 + (random() - 0.5) * 0.005
                           WHEN 60 THEN 89500.00 + (random() - 0.5) * 500.00

                           
                           WHEN 61 THEN 250.00 + (random() - 0.5) * 2.50
                           WHEN 62 THEN 72.00 + (random() - 0.5) * 0.80
                           WHEN 63 THEN 57.00 + (random() - 0.5) * 0.60
                           WHEN 65 THEN 2550.00 + (random() - 0.5) * 25.00
                           WHEN 66 THEN 3780.00 + (random() - 0.5) * 35.00
                           WHEN 67 THEN 1280.00 + (random() - 0.5) * 12.00
                           WHEN 68 THEN 2870.00 + (random() - 0.5) * 28.00
                           WHEN 69 THEN 1730.00 + (random() - 0.5) * 18.00
                           WHEN 70 THEN 27.50 + (random() - 0.5) * 0.30
                           WHEN 71 THEN 13.60 + (random() - 0.5) * 0.15
                           WHEN 72 THEN 18.80 + (random() - 0.5) * 0.20
                           WHEN 73 THEN 855.00 + (random() - 0.5) * 8.00
                           WHEN 74 THEN 63.90 + (random() - 0.5) * 0.70
                           WHEN 75 THEN 8610.00 + (random() - 0.5) * 80.00
                           WHEN 76 THEN 22500.00 + (random() - 0.5) * 200.00
                           WHEN 77 THEN 13.50 + (random() - 0.5) * 0.15
                           WHEN 79 THEN 603.00 + (random() - 0.5) * 5.00
                           WHEN 80 THEN 603.00 + (random() - 0.5) * 5.00
                           WHEN 81 THEN 10.05 + (random() - 0.5) * 0.10
                           WHEN 82 THEN 3.12 + (random() - 0.5) * 0.03
                           WHEN 83 THEN 134.50 + (random() - 0.5) * 1.50
                           WHEN 84 THEN 4.85 + (random() - 0.5) * 0.05
                           WHEN 85 THEN 601.00 + (random() - 0.5) * 6.00
                           WHEN 86 THEN 972.00 + (random() - 0.5) * 10.00
                           WHEN 89 THEN 3.78 + (random() - 0.5) * 0.04
                           WHEN 91 THEN 7550.00 + (random() - 0.5) * 70.00
                           WHEN 92 THEN 6.91 + (random() - 0.5) * 0.07
                           WHEN 93 THEN 36.50 + (random() - 0.5) * 0.40
                           WHEN 95 THEN 7.78 + (random() - 0.5) * 0.08
                           WHEN 96 THEN 24.80 + (random() - 0.5) * 0.25
                           WHEN 97 THEN 36.60 + (random() - 0.5) * 0.40
                           WHEN 98 THEN 1.00
                           WHEN 99 THEN 58.50 + (random() - 0.5) * 0.60
                           WHEN 100 THEN 157.00 + (random() - 0.5) * 2.00
                           WHEN 101 THEN 6.78 + (random() - 0.5) * 0.07
                           WHEN 102 THEN 2.00
                           WHEN 103 THEN 2.70 + (random() - 0.5) * 0.005
                           WHEN 104 THEN 1.00
                           WHEN 105 THEN 2.27 + (random() - 0.5) * 0.025
                           WHEN 106 THEN 3.80 + (random() - 0.5) * 0.04
                           WHEN 107 THEN 8.45 + (random() - 0.5) * 0.09
                           WHEN 108 THEN 2.75 + (random() - 0.5) * 0.03
                           WHEN 109 THEN 2.38 + (random() - 0.5) * 0.025
                           WHEN 110 THEN 121.00 + (random() - 0.5) * 1.50
                           WHEN 111 THEN 0.83 + (random() - 0.5) * 0.005
                           WHEN 112 THEN 1.00
                           WHEN 113 THEN 138.00 + (random() - 0.5) * 2.00
                           WHEN 114 THEN 94.00 + (random() - 0.5) * 1.00
                           WHEN 115 THEN 17.80 + (random() - 0.5) * 0.20
                           WHEN 116 THEN 1.80 + (random() - 0.5) * 0.015
                           WHEN 117 THEN 45.50 + (random() - 0.5) * 0.50
                           WHEN 118 THEN 13.80 + (random() - 0.5) * 0.15
                           WHEN 119 THEN 15.40 + (random() - 0.5) * 0.15
                           WHEN 120 THEN 83.50 + (random() - 0.5) * 0.50
                           END
                   AS NUMERIC)
           , 6)
           AS rate,
       cd.rate_date,
       cd.currency_id
FROM currency_dates cd
ORDER BY cd.currency_id, cd.rate_date;


--Account
WITH gen AS (SELECT generate_series(1, 1000000) AS g),

     clients AS (SELECT client_id, ROW_NUMBER() OVER (ORDER BY client_id) AS rn
                 FROM Client),

     client_count AS (SELECT COUNT(*) AS cnt
                      FROM Client),

     currencies AS (SELECT currency_id, ROW_NUMBER() OVER (ORDER BY currency_id) - 1 AS rn
                    FROM Currency),

     currency_count AS (SELECT COUNT(*) AS cnt
                        FROM Currency),

     branches AS (SELECT branch_id, ROW_NUMBER() OVER (ORDER BY branch_id) - 1 AS rn
                  FROM Branch),

     branch_count AS (SELECT COUNT(*) AS cnt
                      FROM Branch),

     client_loans AS (SELECT loan_id, client_id
                      FROM Loan),

     final AS (SELECT g.g,
                      c.client_id,
                      'MK' || LPAD(g.g::text, 12, '0')             AS account_number,

                      (ARRAY [
                          'SAVINGS','CHECKING','CREDIT','LOAN','INVESTMENT'
                          ])[1 + (floor(random() * 5))::int]       AS account_type,

                      cur.currency_id,

                      CURRENT_DATE - (floor(random() * 3650))::int AS open_date,

                      (ARRAY [
                          'ACTIVE','ACTIVE','ACTIVE','ACTIVE',
                          'ACTIVE','CLOSED','BLOCKED'
                          ])[1 + (floor(random() * 7))::int]       AS status,

                      cu.client_user_id,
                      cl.loan_id,
                      b.branch_id,
                      c.rn

               FROM gen g
                        JOIN client_count cc ON true
                        JOIN currency_count curc ON true
                        JOIN branch_count bc ON true

                        JOIN clients c ON c.rn = ((g.g - 1) % cc.cnt) + 1

                        LEFT JOIN currencies cur ON cur.rn = (g.g - 1) % curc.cnt
                        LEFT JOIN branches b ON b.rn = (c.rn - 1) % bc.cnt

                        LEFT JOIN Client_User cu ON cu.client_id = c.client_id
                        LEFT JOIN client_loans cl ON cl.client_id = c.client_id)

INSERT
INTO Account (account_number,
              account_type,
              balance,
              currency_id,
              open_date,
              status,
              client_id,
              client_user_id,
              loan_id,
              branch_id)
SELECT account_number,
       account_type,

       CASE
           WHEN account_type = 'CHECKING' THEN ROUND((random() * 5000)::numeric, 2)
           WHEN account_type = 'SAVINGS' THEN ROUND((random() * 100000)::numeric, 2)
           WHEN account_type = 'INVESTMENT' THEN ROUND((random() * 200000)::numeric, 2)
           WHEN account_type = 'CREDIT' THEN ROUND((random() * 5000)::numeric, 2)
           WHEN account_type = 'LOAN' THEN ROUND((random() * 300000)::numeric, 2)
           END,

       currency_id,
       open_date,
       status,
       client_id,
       client_user_id,

       CASE
           WHEN account_type = 'LOAN' THEN loan_id
           ELSE NULL
           END,

       branch_id

FROM final;


--SavingsAccount
INSERT INTO SavingsAccount (account_id,
                            interest_rate,
                            interest_period,
                            minimum_balance,
                            capitalization_type)
SELECT a.account_id,

       ROUND((1 + random() * 4.5)::numeric, 2) AS interest_rate,

       (ARRAY ['DAILY','MONTHLY','MONTHLY','MONTHLY','YEARLY'])
           [1 + floor(random() * 5)]           AS interest_period,

       ROUND((random() * 5000)::numeric, 2)    AS minimum_balance,

       (ARRAY ['SIMPLE','COMPOUND','COMPOUND'])
           [1 + floor(random() * 3)]           AS capitalization_type

FROM Account a
WHERE a.account_type = 'SAVINGS';



--Transaction
WITH
account_cte AS (
    SELECT account_id,
           ROW_NUMBER() OVER (ORDER BY account_id) AS rn
    FROM Account
),
currency_cte AS (
    SELECT currency_id,
           ROW_NUMBER() OVER (ORDER BY currency_id) AS rn
    FROM Currency
),
loan_cte AS (
    SELECT loan_id,
           ROW_NUMBER() OVER (ORDER BY loan_id) AS rn
    FROM Loan
),
installment_cte AS (
    SELECT installment_id,
           ROW_NUMBER() OVER (ORDER BY installment_id) AS rn
    FROM Loan_installment
),
type_cte AS (
    SELECT transaction_type_id,
           ROW_NUMBER() OVER (ORDER BY transaction_type_id) AS rn
    FROM Transaction_type
),
payment_cte AS (
    SELECT method_id AS payment_method_id,
           ROW_NUMBER() OVER (ORDER BY method_id) AS rn
    FROM Payment_method
),
emp_cte AS (
    SELECT emp_counter_id,
           ROW_NUMBER() OVER (ORDER BY emp_counter_id) AS rn
    FROM Employee_counter
),

counts AS (
    SELECT
        (SELECT COUNT(*) FROM account_cte) AS acc_cnt,
        (SELECT COUNT(*) FROM currency_cte) AS cur_cnt,
        (SELECT COUNT(*) FROM loan_cte) AS loan_cnt,
        (SELECT COUNT(*) FROM installment_cte) AS inst_cnt,
        (SELECT COUNT(*) FROM type_cte) AS type_cnt,
        (SELECT COUNT(*) FROM payment_cte) AS pay_cnt,
        (SELECT COUNT(*) FROM emp_cte) AS emp_cnt
),

gs_base AS (
    SELECT
        gs,
        (gs % c.acc_cnt) + 1 AS acc_rn,
        ((gs + 1) % c.acc_cnt) + 1 AS acc2_rn,
        (gs % c.cur_cnt) + 1 AS cur_rn,
        ((gs + 1) % c.cur_cnt) + 1 AS cur2_rn,
        (gs % c.loan_cnt) + 1 AS loan_rn,
        (gs % c.inst_cnt) + 1 AS inst_rn,
        (gs % c.type_cnt) + 1 AS type_rn,
        (gs % c.pay_cnt) + 1 AS pay_rn,
        (gs % c.emp_cnt) + 1 AS emp_rn
    FROM generate_series(1, 10000000) gs
    CROSS JOIN counts c
)

INSERT INTO Transaction (
    amount, transaction_date, status, description, reference_number,
    account_id, account_id2, transaction_type_id, payment_method_id,
    currency_id, currency_id2, installment_id, emp_counter_id, loan_id
)
SELECT
    ROUND((RANDOM() * 9000 + 100)::numeric, 2),

    (CURRENT_DATE - (gs.gs % 365) * INTERVAL '1 day')
        + INTERVAL '8 hours'
        + (RANDOM() * INTERVAL '8 hours'),

    CASE (gs.gs % 4)
        WHEN 0 THEN 'PENDING'
        WHEN 1 THEN 'COMPLETED'
        WHEN 2 THEN 'FAILED'
        ELSE 'CANCELLED'
    END,

    CASE ((gs.gs % c.type_cnt) + 1)
        WHEN 1 THEN 'Adding funds to an account'
        WHEN 2 THEN 'Removing funds from an account'
        WHEN 3 THEN 'Transferring funds between accounts'
        WHEN 4 THEN 'Paying bills or services'
        WHEN 5 THEN 'Bank service charge or fee'
        WHEN 6 THEN 'Interest earned or charged on an account balance over time'
        WHEN 7 THEN 'Refund of previously paid transaction'
        WHEN 8 THEN 'Repayment of loan installment'
        WHEN 9 THEN 'Conversion between different currencies'
    END,

    'TRX-' || LPAD(gs.gs::text, 10, '0'),

    a.account_id,
    NULLIF(a2.account_id, a.account_id),

    t.transaction_type_id,
    p.payment_method_id,
    cu.currency_id,
    NULLIF(cu2.currency_id, cu.currency_id),

    i.installment_id,
    e.emp_counter_id,

    CASE
        WHEN t.transaction_type_id = 8 THEN l.loan_id
        ELSE NULL
    END

FROM gs_base gs
CROSS JOIN counts c

JOIN account_cte a   ON a.rn = gs.acc_rn
JOIN account_cte a2  ON a2.rn = gs.acc2_rn

JOIN currency_cte cu  ON cu.rn = gs.cur_rn
JOIN currency_cte cu2 ON cu2.rn = gs.cur2_rn

JOIN loan_cte l      ON l.rn = gs.loan_rn
JOIN installment_cte i ON i.rn = gs.inst_rn

JOIN type_cte t ON t.rn = gs.type_rn
JOIN payment_cte p ON p.rn = gs.pay_rn
JOIN emp_cte e ON e.rn = gs.emp_rn;



--Receipt
INSERT INTO Receipt (print_time, receipt_number, transaction_id)
SELECT
    t.transaction_date + (random() * interval '2 hours') AS print_time,

    'RCPT-' ||
    to_char(t.transaction_date, 'YYYYMMDD') || '-' ||
    LPAD(ROW_NUMBER() OVER (ORDER BY t.transaction_id)::text, 9, '0') AS receipt_number,

    t.transaction_id
FROM Transaction t
LIMIT 10000000;


--InterestPayment
INSERT INTO InterestPayment (
    amount,
    payment_date,
    period_start,
    period_end,
    account_id,
    transaction_id
)
SELECT
    ROUND((t.amount * (0.001 + random() * 0.009))::numeric, 2) AS amount,

    (period_end + (1 + floor(random() * 5))::int) AS payment_date,

    period_start,
    period_end,

    t.account_id,
    t.transaction_id

FROM (
    SELECT
        tr.transaction_id,
        tr.account_id,
        tr.amount,

        (tr.transaction_date - INTERVAL '1 month')::date AS period_start,
        tr.transaction_date::date AS period_end

    FROM Transaction tr
    INNER JOIN SavingsAccount sa
        ON tr.account_id = sa.account_id
    WHERE tr.amount > 0
) t;