DDL, податоци, погледи : dml.sql

File dml.sql, 42.5 KB (added by 231068, 2 days ago)
Line 
1CREATE EXTENSION IF NOT EXISTS pgcrypto;
2DROP EXTENSION pgcrypto CASCADE;
3
4--Bank_User
5INSERT INTO Bank_user (username, password_hash, role_id, employee_id)
6SELECT 'user' || e.employee_id,
7
8 ('$2a$10$' || encode(digest(gen_random_uuid()::text, 'sha256'), 'hex')) AS password_hash,
9
10 CASE e.position
11
12 -- ADMIN
13 WHEN 'Regional Manager' THEN 1
14 WHEN 'System Administrator' THEN 1
15 WHEN 'Database Administrator' THEN 1
16
17
18 -- MANAGER
19 WHEN 'Branch Manager' THEN 2
20 WHEN 'Operations Manager' THEN 2
21 WHEN 'HR Manager' THEN 2
22 WHEN 'HR Specialist' THEN 2
23 WHEN 'Financial Analyst' THEN 2
24 WHEN 'Accountant' THEN 2
25 WHEN 'Marketing Specialist' THEN 2
26 WHEN 'Sales Officer' THEN 2
27
28 -- TELLER
29 WHEN 'Teller' THEN 3
30 WHEN 'Cashier' THEN 3
31
32 -- CUSTOMER_SERVICE
33 WHEN 'Customer Service Representative' THEN 4
34 WHEN 'Personal Banker' THEN 4
35 WHEN 'Loan Officer' THEN 4
36 WHEN 'Credit Analyst' THEN 4
37 WHEN 'Mortgage Specialist' THEN 4
38
39 -- AUDITOR
40 WHEN 'Risk Analyst' THEN 5
41 WHEN 'Compliance Officer' THEN 5
42 WHEN 'AML Specialist' THEN 5
43 WHEN 'Internal Auditor' THEN 5
44
45 -- IT_SUPPORT
46 WHEN 'IT Support Specialist' THEN 6
47 END AS role_id,
48
49 e.employee_id
50
51FROM Employee e;
52SELECT r.role_name,
53 COUNT(bu.user_id) AS total_users,
54 ROUND(COUNT(bu.user_id) * 100.0 / SUM(COUNT(bu.user_id)) OVER (), 2) AS pct
55FROM Bank_user bu
56 JOIN Role r ON r.role_id = bu.role_id
57GROUP BY r.role_name;
58
59
60--Branch_employee
61WITH branch_enum AS (SELECT branch_id, row_number() OVER () AS rn
62 FROM Branch),
63 branch_cnt AS (SELECT COUNT(*) AS cnt
64 FROM branch_enum),
65 employee_enum AS (SELECT employee_id, row_number() OVER (ORDER BY random()) AS rn
66 FROM Employee),
67 employee_cnt AS (SELECT COUNT(*) AS cnt
68 FROM employee_enum),
69 t AS (SELECT i,
70 CURRENT_DATE - (floor(random() * 2000))::int AS start_date,
71 CASE
72 WHEN r < 0.65 THEN 'ACTIVE'
73 WHEN r < 0.85 THEN 'INACTIVE'
74 WHEN r < 0.95 THEN 'SUSPENDED'
75 ELSE 'TEMPORARY_ASSIGNMENT'
76 END AS status
77 FROM (SELECT generate_series(1, 100000) AS i, random() AS r) x)
78
79INSERT
80INTO Branch_employee (branch_id,
81 employee_id,
82 start_date,
83 end_date,
84 status)
85SELECT b.branch_id,
86 e.employee_id,
87 t.start_date,
88 CASE
89 WHEN t.status IN ('INACTIVE', 'SUSPENDED')
90 THEN t.start_date + (1 + floor(random() * 1000)) * INTERVAL '1 day'
91 END,
92 t.status
93FROM t
94 JOIN branch_cnt bc ON true
95 JOIN employee_cnt ec ON true
96 JOIN branch_enum b
97 ON b.rn = ((t.i - 1) % bc.cnt) + 1
98 JOIN employee_enum e
99 ON e.rn = ((t.i - 1) % ec.cnt) + 1;
100
101--Daily_report
102INSERT INTO Daily_report (report_date, total_transactions, total_amount, branch_id)
103SELECT CURRENT_DATE - (floor(random() * 365))::int,
104 (floor(random() * 1000))::int,
105 round((random() * 500000)::numeric, 2),
106 (1 + floor(random() * 101))::int
107FROM generate_series(1, 100000);
108
109--Counter
110INSERT INTO Counter (counter_number, status, branch_id)
111SELECT counter_number,
112 (ARRAY ['OPEN','CLOSED','MAINTENANCE'])[floor(random() * 3) + 1],
113 branch_id
114FROM (SELECT generate_series(1, 500) AS counter_number,
115 (1 + floor(random() * 101))::int AS branch_id) t
116ORDER BY random();
117
118--Employee_counter
119INSERT INTO Employee_counter (start_shift, end_shift, employee_id, counter_id)
120SELECT start_shift,
121 start_shift + INTERVAL '8 hours' + ((floor(random() * 45) - 15) || ' minutes')::interval,
122 (1 + floor(random() * 100000))::int,
123 (1 + floor(random() * 500))::int
124FROM (SELECT date_trunc('day', CURRENT_TIMESTAMP - ((floor(random() * 30))::int * INTERVAL '1 day'))
125 + INTERVAL '7 hours 45 minutes'
126 + ((floor(random() * 31)) || ' minutes')::interval AS start_shift
127 FROM generate_series(1, 100000)) t;
128
129--Client
130WITH ranked AS (SELECT c.client_id,
131 c.first_name,
132 c.last_name,
133 make_date(
134 (1950 + ((client_id::bigint * 7331) % 56))::int,
135 (1 + ((client_id::bigint * 2741) % 12))::int,
136 (1 + ((client_id::bigint * 1913) % 28))::int
137 ) AS birth_date,
138 CASE
139 WHEN c.first_name IN (
140 'Ana', 'Elena', 'Maja', 'Ivana', 'Katerina', 'Sonja', 'Vesna',
141 'Biljana',
142 'Daniela', 'Silvana', 'Irena', 'Suzana', 'Lidija', 'Violeta',
143 'Snezana',
144 'Gordana', 'Tatjana', 'Milica', 'Bojana', 'Natasa', 'Sara', 'Kristina',
145 'Marija', 'Sofija', 'Teodora', 'Dijana', 'Dragana', 'Angela',
146 'Liljana',
147 'Sandra', 'Jelena', 'Aleksandra', 'Jasmina', 'Marina', 'Jovana',
148 'Renata',
149 'Dusica', 'Zorica', 'Menka', 'Bisera', 'Smiljana', 'Cveta', 'Dobrila',
150 'Verica', 'Silvija', 'Rozalinda', 'Radmila', 'Neda', 'Monika',
151 'Milena',
152 'Dina', 'Rania', 'Samira', 'Fatima', 'Layla', 'Noura', 'Zainab',
153 'Hana',
154 'Hoda', 'Mariam', 'Rabia', 'Meryem', 'Hatice', 'Fatma', 'Emine',
155 'Havva',
156 'Elif', 'Ayse', 'Halime', 'Zeynep', 'Amira', 'Nadia', 'Salma',
157 'Yasmine',
158 'Samantha', 'Ashley', 'Kimberly', 'Rachel', 'Heather', 'Brittany',
159 'Emily',
160 'Lauren', 'Jessica', 'Melissa', 'Amanda', 'Tiffany', 'Crystal',
161 'Amber',
162 'Rebecca', 'Megan', 'Nicole', 'Christine', 'Katherine', 'Claudia',
163 'Sylvie',
164 'Monique', 'Sophie', 'Marie', 'Isabelle', 'Brigitte', 'Nathalie',
165 'Veronique',
166 'Danielle', 'Francesca', 'Federica', 'Alessia', 'Elisa', 'Chiara',
167 'Giulia',
168 'Paola', 'Martina', 'Sabine', 'Ursula', 'Helga', 'Ingrid', 'Hannah',
169 'Emma',
170 'Anna', 'Sarah', 'Laura', 'Olivia', 'Sophia', 'Elizabeth', 'Amy',
171 'Abigail',
172 'Stephanie', 'Katarina', 'Aneta', 'Emilija', 'Petra', 'Blagica',
173 'Valentina',
174 'Natasha', 'Lina', 'Andrea', 'Catherine'
175 ) THEN '455'
176 ELSE '450'
177 END AS gender_digits,
178 ROW_NUMBER() OVER (
179 PARTITION BY
180 1940 + (client_id % 60),
181 1 + (client_id * 13) % 12,
182 1 + (client_id * 19) % 28,
183 CASE
184 WHEN c.first_name IN (
185 'Ana', 'Elena', 'Maja', 'Ivana', 'Katerina', 'Sonja', 'Vesna',
186 'Biljana',
187 'Daniela', 'Silvana', 'Irena', 'Suzana', 'Lidija', 'Violeta',
188 'Snezana',
189 'Gordana', 'Tatjana', 'Milica', 'Bojana', 'Natasa', 'Sara',
190 'Kristina',
191 'Marija', 'Sofija', 'Teodora', 'Dijana', 'Dragana', 'Angela',
192 'Liljana',
193 'Sandra', 'Jelena', 'Aleksandra', 'Jasmina', 'Marina',
194 'Jovana', 'Renata',
195 'Dusica', 'Zorica', 'Menka', 'Bisera', 'Smiljana', 'Cveta',
196 'Dobrila',
197 'Verica', 'Silvija', 'Rozalinda', 'Radmila', 'Neda', 'Monika',
198 'Milena',
199 'Dina', 'Rania', 'Samira', 'Fatima', 'Layla', 'Noura',
200 'Zainab', 'Hana',
201 'Hoda', 'Mariam', 'Rabia', 'Meryem', 'Hatice', 'Fatma',
202 'Emine', 'Havva',
203 'Elif', 'Ayse', 'Halime', 'Zeynep', 'Amira', 'Nadia', 'Salma',
204 'Yasmine',
205 'Samantha', 'Ashley', 'Kimberly', 'Rachel', 'Heather',
206 'Brittany', 'Emily',
207 'Lauren', 'Jessica', 'Melissa', 'Amanda', 'Tiffany', 'Crystal',
208 'Amber',
209 'Rebecca', 'Megan', 'Nicole', 'Christine', 'Katherine',
210 'Claudia', 'Sylvie',
211 'Monique', 'Sophie', 'Marie', 'Isabelle', 'Brigitte',
212 'Nathalie', 'Veronique',
213 'Danielle', 'Francesca', 'Federica', 'Alessia', 'Elisa',
214 'Chiara', 'Giulia',
215 'Paola', 'Martina', 'Sabine', 'Ursula', 'Helga', 'Ingrid',
216 'Hannah', 'Emma',
217 'Anna', 'Sarah', 'Laura', 'Olivia', 'Sophia', 'Elizabeth',
218 'Amy', 'Abigail',
219 'Stephanie', 'Katarina', 'Aneta', 'Emilija', 'Petra',
220 'Blagica', 'Valentina',
221 'Natasha', 'Lina', 'Andrea', 'Catherine'
222 ) THEN '455'
223 ELSE '450'
224 END
225 ORDER BY c.client_id
226 ) AS rn
227 FROM Client c
228 WHERE c.embg IS NULL)
229UPDATE Client c
230SET embg = LPAD(EXTRACT(DAY FROM r.birth_date)::INT::TEXT, 2, '0') ||
231 LPAD(EXTRACT(MONTH FROM r.birth_date)::INT::TEXT, 2, '0') ||
232 LPAD((EXTRACT(YEAR FROM r.birth_date)::INT % 1000)::TEXT, 3, '0') ||
233 r.gender_digits || LPAD(r.rn::TEXT, 3, '0'),
234
235 phone = '+3897' || LPAD(((c.client_id * 7 + 1234567) % 9000000 + 1000000)::TEXT, 7, '0'),
236
237 email = LOWER(REPLACE(c.first_name, ' ', '')) || '.' ||
238 LOWER(REPLACE(REPLACE(c.last_name, ' ', ''), '-', '')) ||
239 c.client_id::TEXT || '@gmail.com',
240
241 address = (100 + (c.client_id * 17 + 3) % 900)::TEXT || ' - ' ||
242 (ARRAY [
243 'Partizanska','Makedonska','Ilindenska','Vasil Glavinov','Dame Gruev',
244 'Skupi','Kozle','Lerin','Karposh','Aerodrom','Butel','Gazi Baba',
245 'Jane Sandanski','Nikola Tesla','Sv Kliment Ohridski','Vardar',
246 'Treska','Bregalnica','Pcinja','Goce Delcev','Hristo Tatarcev',
247 'Boro Petrusevski','Naum Naumovski','Metodija Andonov','Ss Kiril i Metodij'
248 ])[1 + (c.client_id * 31) % 25] || ' - ' ||
249 (1 + (c.client_id * 11) % 200)::TEXT,
250
251 status = (ARRAY ['ACTIVE','ACTIVE','ACTIVE','INACTIVE','BLOCKED'])
252 [1 + (c.client_id * 7) % 5],
253
254 date_registered = r.birth_date +
255 ((c.client_id * 97 + 6574) %
256 (CURRENT_DATE - r.birth_date - 365 * 18))::INT
257 + 365 * 18,
258
259 branch_id = (1 + floor(random() * 101))::int
260
261FROM ranked r
262WHERE c.client_id = r.client_id;
263
264
265--Client_user
266INSERT INTO Client_user (username, password_hash, last_login, status, client_id)
267SELECT 'client' || client_id,
268 ('$2a$10$' || encode(digest(gen_random_uuid()::text, 'sha256'), 'hex')) AS password_hash,
269 CURRENT_TIMESTAMP - (floor(random() * 365) || ' days')::interval,
270 (ARRAY ['ACTIVE','LOCKED','DISABLED'])[floor(random() * 3) + 1],
271 client_id
272FROM Client
273WHERE random() < 0.7;
274
275
276--Loan
277WITH ordered_clients AS (SELECT client_id,
278 (ROW_NUMBER() OVER (ORDER BY client_id) - 1) AS rn
279 FROM Client)
280INSERT
281INTO Loan (loan_type,
282 amount,
283 interest_rate,
284 start_date,
285 end_date,
286 status,
287 approved_date,
288 client_id,
289 employee_id)
290SELECT (ARRAY ['HOME','CAR','PERSONAL','BUSINESS'])[floor(random() * 4) + 1],
291 round((1000 + random() * 90000)::numeric, 2),
292 round((1 + random() * 9)::numeric, 2),
293 base.start_date,
294 CASE
295 WHEN base.status = 'CLOSED'
296 THEN base.start_date + (floor(random() * 365) + 1)::int
297 ELSE NULL
298 END,
299 base.status,
300 CASE
301 WHEN base.status = 'APPROVED'
302 THEN base.start_date + (floor(random() * 100))::int
303 ELSE NULL
304 END,
305 c.client_id,
306 base.employee_id
307FROM (SELECT e.employee_id,
308 s.status,
309 CURRENT_DATE - (floor(random() * 1000))::int AS start_date,
310 (ROW_NUMBER() OVER () - 1) AS rn
311 FROM Employee e
312 JOIN (VALUES ('PENDING'),
313 ('APPROVED'),
314 ('REJECTED'),
315 ('CLOSED')) AS s(status) ON true
316 WHERE e.position IN (
317 'Customer Service Representative',
318 'Personal Banker',
319 'Loan Officer',
320 'Credit Analyst',
321 'Mortgage Specialist'
322 )
323 LIMIT 100000) base
324 JOIN ordered_clients c ON c.rn = base.rn % (SELECT COUNT(*) FROM ordered_clients);
325
326
327--Collateral
328WITH types AS (SELECT unnest(ARRAY [
329 'REAL_ESTATE',
330 'VEHICLE',
331 'GOLD',
332 'EQUIPMENT',
333 'SAVINGS_ACCOUNT'
334 ]) AS type),
335 shuffled_types AS (SELECT type,
336 ROW_NUMBER() OVER (ORDER BY random()) AS rn
337 FROM types),
338 ordered_loans AS (SELECT loan_id,
339 ROW_NUMBER() OVER (ORDER BY loan_id) AS rn
340 FROM Loan),
341 ordered_clients AS (SELECT client_id,
342 ROW_NUMBER() OVER (ORDER BY client_id) AS rn
343 FROM Client),
344 paired AS (SELECT l.loan_id, c.client_id, t.type
345 FROM ordered_loans l
346 JOIN ordered_clients c ON l.rn = c.rn
347 JOIN shuffled_types t ON t.rn = ((l.rn - 1) % 5) + 1)
348
349INSERT
350INTO Collateral (type,
351 description,
352 value,
353 status,
354 date_added,
355 loan_id,
356 client_id)
357SELECT p.type,
358
359 CASE p.type
360 WHEN 'REAL_ESTATE' THEN 'Apartment collateral'
361 WHEN 'VEHICLE' THEN 'Car collateral - sedan'
362 WHEN 'GOLD' THEN 'Gold jewelry set'
363 WHEN 'EQUIPMENT' THEN 'Office equipment'
364 WHEN 'SAVINGS_ACCOUNT' THEN 'Bank savings pledge'
365 END,
366
367 round((5000 + random() * 150000)::numeric, 2),
368
369 (ARRAY ['ACTIVE', 'RELEASED', 'PLEDGED', 'DEFAULTED'])[floor(random() * 4) + 1],
370
371 CURRENT_DATE - (floor(random() * 365))::int,
372
373 p.loan_id,
374 p.client_id
375FROM paired p;
376
377
378--Loan_installment
379WITH ordered_loans AS (SELECT loan_id,
380 start_date,
381 amount,
382 ROW_NUMBER() OVER (ORDER BY loan_id) AS rn
383 FROM Loan),
384 installments AS (SELECT ol.loan_id,
385
386 ROW_NUMBER() OVER (
387 PARTITION BY ol.loan_id
388 ORDER BY gs
389 ) AS installment_number,
390
391 (ol.start_date + (gs - 1) * INTERVAL '1 month')::date AS due_date,
392
393 ROUND((
394 ol.amount /
395 CASE
396 WHEN ol.amount <= 1000 THEN 3
397 WHEN ol.amount <= 3000 THEN 6
398 WHEN ol.amount <= 5000 THEN 12
399 WHEN ol.amount <= 10000 THEN 24
400 WHEN ol.amount <= 15000 THEN 36
401 WHEN ol.amount <= 20000 THEN 48
402 WHEN ol.amount <= 25000 THEN 60
403 WHEN ol.amount <= 30000 THEN 72
404 WHEN ol.amount <= 35000 THEN 84
405 WHEN ol.amount <= 40000 THEN 96
406 WHEN ol.amount <= 45000 THEN 108
407 WHEN ol.amount <= 50000 THEN 120
408 WHEN ol.amount <= 60000 THEN 132
409 WHEN ol.amount <= 70000 THEN 144
410 WHEN ol.amount <= 80000 THEN 156
411 WHEN ol.amount <= 90000 THEN 168
412 WHEN ol.amount <= 100000 THEN 180
413 WHEN ol.amount <= 120000 THEN 192
414 WHEN ol.amount <= 140000 THEN 204
415 WHEN ol.amount <= 160000 THEN 216
416 WHEN ol.amount <= 180000 THEN 228
417 WHEN ol.amount <= 200000 THEN 240
418 WHEN ol.amount <= 220000 THEN 252
419 WHEN ol.amount <= 240000 THEN 264
420 WHEN ol.amount <= 260000 THEN 276
421 WHEN ol.amount <= 280000 THEN 288
422 ELSE 300
423 END
424 )::numeric, 2) AS amount,
425
426 CASE
427 WHEN (ol.start_date + (gs - 1) * INTERVAL '1 month') < CURRENT_DATE
428 AND random() < 0.6 THEN 'PAID'
429 WHEN (ol.start_date + (gs - 1) * INTERVAL '1 month') < CURRENT_DATE
430 THEN 'LATE'
431 ELSE 'PENDING'
432 END AS status
433
434 FROM ordered_loans ol
435 CROSS JOIN generate_series(
436 1,
437 CASE
438 WHEN ol.amount <= 1000 THEN 3
439 WHEN ol.amount <= 3000 THEN 6
440 WHEN ol.amount <= 5000 THEN 12
441 WHEN ol.amount <= 10000 THEN 24
442 WHEN ol.amount <= 15000 THEN 36
443 WHEN ol.amount <= 20000 THEN 48
444 WHEN ol.amount <= 25000 THEN 60
445 WHEN ol.amount <= 30000 THEN 72
446 WHEN ol.amount <= 35000 THEN 84
447 WHEN ol.amount <= 40000 THEN 96
448 WHEN ol.amount <= 45000 THEN 108
449 WHEN ol.amount <= 50000 THEN 120
450 WHEN ol.amount <= 60000 THEN 132
451 WHEN ol.amount <= 70000 THEN 144
452 WHEN ol.amount <= 80000 THEN 156
453 WHEN ol.amount <= 90000 THEN 168
454 WHEN ol.amount <= 100000 THEN 180
455 WHEN ol.amount <= 120000 THEN 192
456 WHEN ol.amount <= 140000 THEN 204
457 WHEN ol.amount <= 160000 THEN 216
458 WHEN ol.amount <= 180000 THEN 228
459 WHEN ol.amount <= 200000 THEN 240
460 WHEN ol.amount <= 220000 THEN 252
461 WHEN ol.amount <= 240000 THEN 264
462 WHEN ol.amount <= 260000 THEN 276
463 WHEN ol.amount <= 280000 THEN 288
464 ELSE 300
465 END
466 ) gs)
467INSERT
468INTO Loan_installment (installment_number,
469 due_date,
470 amount,
471 status,
472 paid_date,
473 loan_id)
474SELECT installment_number,
475 due_date,
476 amount,
477 status,
478
479 CASE
480 WHEN status = 'PAID'
481 THEN due_date - (floor(random() * 5)::int) * INTERVAL '1 day'
482 ELSE NULL
483 END AS paid_date,
484
485 loan_id
486FROM installments;
487
488
489--Exchange_rate
490WITH ordered_currency AS (SELECT currency_id,
491 ROW_NUMBER() OVER (ORDER BY currency_id) AS rn
492 FROM Currency),
493 dates AS (SELECT generate_series(
494 CURRENT_DATE - INTERVAL '29 days',
495 CURRENT_DATE,
496 INTERVAL '1 day'
497 )::date AS rate_date),
498 currency_dates AS (SELECT oc.currency_id,
499 d.rate_date,
500 ROW_NUMBER() OVER (PARTITION BY oc.currency_id ORDER BY d.rate_date) AS seq
501 FROM ordered_currency oc
502 JOIN dates d ON TRUE)
503INSERT
504INTO Exchange_rate (rate, date_updated, currency_id)
505SELECT ROUND(
506 CAST(
507 CASE cd.currency_id
508
509
510 WHEN 2 THEN 1.000000 + (random() - 0.5) * 0.000
511
512
513 WHEN 1 THEN 1.08 + (random() - 0.5) * 0.01
514 WHEN 3 THEN 61.50 + (random() - 0.5) * 0.50
515 WHEN 4 THEN 0.86 + (random() - 0.5) * 0.01
516 WHEN 5 THEN 160.00 + (random() - 0.5) * 2.00
517 WHEN 6 THEN 0.95 + (random() - 0.5) * 0.01
518 WHEN 7 THEN 1.47 + (random() - 0.5) * 0.02
519 WHEN 8 THEN 1.62 + (random() - 0.5) * 0.02
520 WHEN 9 THEN 1.75 + (random() - 0.5) * 0.02
521
522
523 WHEN 10 THEN 11.00 + (random() - 0.5) * 0.10
524 WHEN 11 THEN 11.20 + (random() - 0.5) * 0.10
525 WHEN 12 THEN 7.45 + (random() - 0.5) * 0.08
526 WHEN 13 THEN 4.40 + (random() - 0.5) * 0.04
527 WHEN 14 THEN 24.50 + (random() - 0.5) * 0.20
528 WHEN 15 THEN 390.00 + (random() - 0.5) * 3.00
529 WHEN 16 THEN 118.00 + (random() - 0.5) * 1.00
530 WHEN 17 THEN 7.40 + (random() - 0.5) * 0.06
531 WHEN 18 THEN 1.96 + (random() - 0.5) * 0.02
532 WHEN 19 THEN 35.00 + (random() - 0.5) * 0.50
533 WHEN 20 THEN 95.00 + (random() - 0.5) * 1.00
534
535
536 WHEN 21 THEN 7.80 + (random() - 0.5) * 0.06
537 WHEN 22 THEN 90.00 + (random() - 0.5) * 0.50
538 WHEN 23 THEN 5.50 + (random() - 0.5) * 0.05
539 WHEN 28 THEN 1450.00 + (random() - 0.5) * 15.00
540
541
542 WHEN 24 THEN 20.00 + (random() - 0.5) * 0.20
543 WHEN 64 THEN 140.00 + (random() - 0.5) * 1.50
544 WHEN 78 THEN 1600.00 + (random() - 0.5) * 15.00
545
546
547 WHEN 25 THEN 18.00 + (random() - 0.5) * 0.20
548 WHEN 87 THEN 950.00 + (random() - 0.5) * 10.00
549 WHEN 88 THEN 4200.00 + (random() - 0.5) * 40.00
550 WHEN 90 THEN 41.00 + (random() - 0.5) * 0.40
551 WHEN 94 THEN 530.00 + (random() - 0.5) * 5.00
552
553
554 WHEN 26 THEN 1.35 + (random() - 0.5) * 0.01
555 WHEN 27 THEN 7.85 + (random() - 0.5) * 0.05
556 WHEN 29 THEN 3.67 + (random() - 0.5) * 0.005
557 WHEN 30 THEN 3.75 + (random() - 0.5) * 0.005
558 WHEN 31 THEN 3.64 + (random() - 0.5) * 0.005
559 WHEN 32 THEN 0.31 + (random() - 0.5) * 0.003
560 WHEN 33 THEN 0.38 + (random() - 0.5) * 0.003
561 WHEN 34 THEN 0.37 + (random() - 0.5) * 0.003
562 WHEN 35 THEN 49.00 + (random() - 0.5) * 0.80
563 WHEN 36 THEN 3.70 + (random() - 0.5) * 0.05
564 WHEN 37 THEN 35.50 + (random() - 0.5) * 0.40
565 WHEN 38 THEN 4.72 + (random() - 0.5) * 0.05
566 WHEN 39 THEN 16000.00 + (random() - 0.5) * 150.00
567 WHEN 40 THEN 56.50 + (random() - 0.5) * 0.60
568 WHEN 41 THEN 24500.00 + (random() - 0.5) * 200.00
569 WHEN 42 THEN 280.00 + (random() - 0.5) * 3.00
570 WHEN 43 THEN 110.00 + (random() - 0.5) * 1.00
571 WHEN 44 THEN 305.00 + (random() - 0.5) * 3.00
572 WHEN 45 THEN 133.50 + (random() - 0.5) * 1.50
573 WHEN 46 THEN 450.00 + (random() - 0.5) * 4.00
574 WHEN 47 THEN 39.50 + (random() - 0.5) * 0.60
575 WHEN 48 THEN 3.27 + (random() - 0.5) * 0.04
576 WHEN 49 THEN 2.68 + (random() - 0.5) * 0.03
577 WHEN 50 THEN 395.00 + (random() - 0.5) * 4.00
578 WHEN 51 THEN 1.70 + (random() - 0.5) * 0.015
579 WHEN 52 THEN 12800.00 + (random() - 0.5) * 100.00
580 WHEN 53 THEN 10.90 + (random() - 0.5) * 0.10
581 WHEN 54 THEN 89.00 + (random() - 0.5) * 0.80
582 WHEN 55 THEN 3450.00 + (random() - 0.5) * 30.00
583 WHEN 56 THEN 42000.00 + (random() - 0.5) * 400.00
584 WHEN 57 THEN 1310.00 + (random() - 0.5) * 12.00
585 WHEN 58 THEN 13000.00 + (random() - 0.5) * 100.00
586 WHEN 59 THEN 0.71 + (random() - 0.5) * 0.005
587 WHEN 60 THEN 89500.00 + (random() - 0.5) * 500.00
588
589
590 WHEN 61 THEN 250.00 + (random() - 0.5) * 2.50
591 WHEN 62 THEN 72.00 + (random() - 0.5) * 0.80
592 WHEN 63 THEN 57.00 + (random() - 0.5) * 0.60
593 WHEN 65 THEN 2550.00 + (random() - 0.5) * 25.00
594 WHEN 66 THEN 3780.00 + (random() - 0.5) * 35.00
595 WHEN 67 THEN 1280.00 + (random() - 0.5) * 12.00
596 WHEN 68 THEN 2870.00 + (random() - 0.5) * 28.00
597 WHEN 69 THEN 1730.00 + (random() - 0.5) * 18.00
598 WHEN 70 THEN 27.50 + (random() - 0.5) * 0.30
599 WHEN 71 THEN 13.60 + (random() - 0.5) * 0.15
600 WHEN 72 THEN 18.80 + (random() - 0.5) * 0.20
601 WHEN 73 THEN 855.00 + (random() - 0.5) * 8.00
602 WHEN 74 THEN 63.90 + (random() - 0.5) * 0.70
603 WHEN 75 THEN 8610.00 + (random() - 0.5) * 80.00
604 WHEN 76 THEN 22500.00 + (random() - 0.5) * 200.00
605 WHEN 77 THEN 13.50 + (random() - 0.5) * 0.15
606 WHEN 79 THEN 603.00 + (random() - 0.5) * 5.00
607 WHEN 80 THEN 603.00 + (random() - 0.5) * 5.00
608 WHEN 81 THEN 10.05 + (random() - 0.5) * 0.10
609 WHEN 82 THEN 3.12 + (random() - 0.5) * 0.03
610 WHEN 83 THEN 134.50 + (random() - 0.5) * 1.50
611 WHEN 84 THEN 4.85 + (random() - 0.5) * 0.05
612 WHEN 85 THEN 601.00 + (random() - 0.5) * 6.00
613 WHEN 86 THEN 972.00 + (random() - 0.5) * 10.00
614 WHEN 89 THEN 3.78 + (random() - 0.5) * 0.04
615 WHEN 91 THEN 7550.00 + (random() - 0.5) * 70.00
616 WHEN 92 THEN 6.91 + (random() - 0.5) * 0.07
617 WHEN 93 THEN 36.50 + (random() - 0.5) * 0.40
618 WHEN 95 THEN 7.78 + (random() - 0.5) * 0.08
619 WHEN 96 THEN 24.80 + (random() - 0.5) * 0.25
620 WHEN 97 THEN 36.60 + (random() - 0.5) * 0.40
621 WHEN 98 THEN 1.00
622 WHEN 99 THEN 58.50 + (random() - 0.5) * 0.60
623 WHEN 100 THEN 157.00 + (random() - 0.5) * 2.00
624 WHEN 101 THEN 6.78 + (random() - 0.5) * 0.07
625 WHEN 102 THEN 2.00
626 WHEN 103 THEN 2.70 + (random() - 0.5) * 0.005
627 WHEN 104 THEN 1.00
628 WHEN 105 THEN 2.27 + (random() - 0.5) * 0.025
629 WHEN 106 THEN 3.80 + (random() - 0.5) * 0.04
630 WHEN 107 THEN 8.45 + (random() - 0.5) * 0.09
631 WHEN 108 THEN 2.75 + (random() - 0.5) * 0.03
632 WHEN 109 THEN 2.38 + (random() - 0.5) * 0.025
633 WHEN 110 THEN 121.00 + (random() - 0.5) * 1.50
634 WHEN 111 THEN 0.83 + (random() - 0.5) * 0.005
635 WHEN 112 THEN 1.00
636 WHEN 113 THEN 138.00 + (random() - 0.5) * 2.00
637 WHEN 114 THEN 94.00 + (random() - 0.5) * 1.00
638 WHEN 115 THEN 17.80 + (random() - 0.5) * 0.20
639 WHEN 116 THEN 1.80 + (random() - 0.5) * 0.015
640 WHEN 117 THEN 45.50 + (random() - 0.5) * 0.50
641 WHEN 118 THEN 13.80 + (random() - 0.5) * 0.15
642 WHEN 119 THEN 15.40 + (random() - 0.5) * 0.15
643 WHEN 120 THEN 83.50 + (random() - 0.5) * 0.50
644 END
645 AS NUMERIC)
646 , 6)
647 AS rate,
648 cd.rate_date,
649 cd.currency_id
650FROM currency_dates cd
651ORDER BY cd.currency_id, cd.rate_date;
652
653
654--Account
655WITH gen AS (SELECT generate_series(1, 1000000) AS g),
656
657 clients AS (SELECT client_id, ROW_NUMBER() OVER (ORDER BY client_id) AS rn
658 FROM Client),
659
660 client_count AS (SELECT COUNT(*) AS cnt
661 FROM Client),
662
663 currencies AS (SELECT currency_id, ROW_NUMBER() OVER (ORDER BY currency_id) - 1 AS rn
664 FROM Currency),
665
666 currency_count AS (SELECT COUNT(*) AS cnt
667 FROM Currency),
668
669 branches AS (SELECT branch_id, ROW_NUMBER() OVER (ORDER BY branch_id) - 1 AS rn
670 FROM Branch),
671
672 branch_count AS (SELECT COUNT(*) AS cnt
673 FROM Branch),
674
675 client_loans AS (SELECT loan_id, client_id
676 FROM Loan),
677
678 final AS (SELECT g.g,
679 c.client_id,
680 'MK' || LPAD(g.g::text, 12, '0') AS account_number,
681
682 (ARRAY [
683 'SAVINGS','CHECKING','CREDIT','LOAN','INVESTMENT'
684 ])[1 + (floor(random() * 5))::int] AS account_type,
685
686 cur.currency_id,
687
688 CURRENT_DATE - (floor(random() * 3650))::int AS open_date,
689
690 (ARRAY [
691 'ACTIVE','ACTIVE','ACTIVE','ACTIVE',
692 'ACTIVE','CLOSED','BLOCKED'
693 ])[1 + (floor(random() * 7))::int] AS status,
694
695 cu.client_user_id,
696 cl.loan_id,
697 b.branch_id,
698 c.rn
699
700 FROM gen g
701 JOIN client_count cc ON true
702 JOIN currency_count curc ON true
703 JOIN branch_count bc ON true
704
705 JOIN clients c ON c.rn = ((g.g - 1) % cc.cnt) + 1
706
707 LEFT JOIN currencies cur ON cur.rn = (g.g - 1) % curc.cnt
708 LEFT JOIN branches b ON b.rn = (c.rn - 1) % bc.cnt
709
710 LEFT JOIN Client_User cu ON cu.client_id = c.client_id
711 LEFT JOIN client_loans cl ON cl.client_id = c.client_id)
712
713INSERT
714INTO Account (account_number,
715 account_type,
716 balance,
717 currency_id,
718 open_date,
719 status,
720 client_id,
721 client_user_id,
722 loan_id,
723 branch_id)
724SELECT account_number,
725 account_type,
726
727 CASE
728 WHEN account_type = 'CHECKING' THEN ROUND((random() * 5000)::numeric, 2)
729 WHEN account_type = 'SAVINGS' THEN ROUND((random() * 100000)::numeric, 2)
730 WHEN account_type = 'INVESTMENT' THEN ROUND((random() * 200000)::numeric, 2)
731 WHEN account_type = 'CREDIT' THEN ROUND((random() * 5000)::numeric, 2)
732 WHEN account_type = 'LOAN' THEN ROUND((random() * 300000)::numeric, 2)
733 END,
734
735 currency_id,
736 open_date,
737 status,
738 client_id,
739 client_user_id,
740
741 CASE
742 WHEN account_type = 'LOAN' THEN loan_id
743 ELSE NULL
744 END,
745
746 branch_id
747
748FROM final;
749
750
751--SavingsAccount
752INSERT INTO SavingsAccount (account_id,
753 interest_rate,
754 interest_period,
755 minimum_balance,
756 capitalization_type)
757SELECT a.account_id,
758
759 ROUND((1 + random() * 4.5)::numeric, 2) AS interest_rate,
760
761 (ARRAY ['DAILY','MONTHLY','MONTHLY','MONTHLY','YEARLY'])
762 [1 + floor(random() * 5)] AS interest_period,
763
764 ROUND((random() * 5000)::numeric, 2) AS minimum_balance,
765
766 (ARRAY ['SIMPLE','COMPOUND','COMPOUND'])
767 [1 + floor(random() * 3)] AS capitalization_type
768
769FROM Account a
770WHERE a.account_type = 'SAVINGS';
771
772
773
774--Transaction
775WITH
776account_cte AS (
777 SELECT account_id,
778 ROW_NUMBER() OVER (ORDER BY account_id) AS rn
779 FROM Account
780),
781currency_cte AS (
782 SELECT currency_id,
783 ROW_NUMBER() OVER (ORDER BY currency_id) AS rn
784 FROM Currency
785),
786loan_cte AS (
787 SELECT loan_id,
788 ROW_NUMBER() OVER (ORDER BY loan_id) AS rn
789 FROM Loan
790),
791installment_cte AS (
792 SELECT installment_id,
793 ROW_NUMBER() OVER (ORDER BY installment_id) AS rn
794 FROM Loan_installment
795),
796type_cte AS (
797 SELECT transaction_type_id,
798 ROW_NUMBER() OVER (ORDER BY transaction_type_id) AS rn
799 FROM Transaction_type
800),
801payment_cte AS (
802 SELECT method_id AS payment_method_id,
803 ROW_NUMBER() OVER (ORDER BY method_id) AS rn
804 FROM Payment_method
805),
806emp_cte AS (
807 SELECT emp_counter_id,
808 ROW_NUMBER() OVER (ORDER BY emp_counter_id) AS rn
809 FROM Employee_counter
810),
811
812counts AS (
813 SELECT
814 (SELECT COUNT(*) FROM account_cte) AS acc_cnt,
815 (SELECT COUNT(*) FROM currency_cte) AS cur_cnt,
816 (SELECT COUNT(*) FROM loan_cte) AS loan_cnt,
817 (SELECT COUNT(*) FROM installment_cte) AS inst_cnt,
818 (SELECT COUNT(*) FROM type_cte) AS type_cnt,
819 (SELECT COUNT(*) FROM payment_cte) AS pay_cnt,
820 (SELECT COUNT(*) FROM emp_cte) AS emp_cnt
821),
822
823gs_base AS (
824 SELECT
825 gs,
826 (gs % c.acc_cnt) + 1 AS acc_rn,
827 ((gs + 1) % c.acc_cnt) + 1 AS acc2_rn,
828 (gs % c.cur_cnt) + 1 AS cur_rn,
829 ((gs + 1) % c.cur_cnt) + 1 AS cur2_rn,
830 (gs % c.loan_cnt) + 1 AS loan_rn,
831 (gs % c.inst_cnt) + 1 AS inst_rn,
832 (gs % c.type_cnt) + 1 AS type_rn,
833 (gs % c.pay_cnt) + 1 AS pay_rn,
834 (gs % c.emp_cnt) + 1 AS emp_rn
835 FROM generate_series(1, 10000000) gs
836 CROSS JOIN counts c
837)
838
839INSERT INTO Transaction (
840 amount, transaction_date, status, description, reference_number,
841 account_id, account_id2, transaction_type_id, payment_method_id,
842 currency_id, currency_id2, installment_id, emp_counter_id, loan_id
843)
844SELECT
845 ROUND((RANDOM() * 9000 + 100)::numeric, 2),
846
847 (CURRENT_DATE - (gs.gs % 365) * INTERVAL '1 day')
848 + INTERVAL '8 hours'
849 + (RANDOM() * INTERVAL '8 hours'),
850
851 CASE (gs.gs % 4)
852 WHEN 0 THEN 'PENDING'
853 WHEN 1 THEN 'COMPLETED'
854 WHEN 2 THEN 'FAILED'
855 ELSE 'CANCELLED'
856 END,
857
858 CASE ((gs.gs % c.type_cnt) + 1)
859 WHEN 1 THEN 'Adding funds to an account'
860 WHEN 2 THEN 'Removing funds from an account'
861 WHEN 3 THEN 'Transferring funds between accounts'
862 WHEN 4 THEN 'Paying bills or services'
863 WHEN 5 THEN 'Bank service charge or fee'
864 WHEN 6 THEN 'Interest earned or charged on an account balance over time'
865 WHEN 7 THEN 'Refund of previously paid transaction'
866 WHEN 8 THEN 'Repayment of loan installment'
867 WHEN 9 THEN 'Conversion between different currencies'
868 END,
869
870 'TRX-' || LPAD(gs.gs::text, 10, '0'),
871
872 a.account_id,
873 NULLIF(a2.account_id, a.account_id),
874
875 t.transaction_type_id,
876 p.payment_method_id,
877 cu.currency_id,
878 NULLIF(cu2.currency_id, cu.currency_id),
879
880 i.installment_id,
881 e.emp_counter_id,
882
883 CASE
884 WHEN t.transaction_type_id = 8 THEN l.loan_id
885 ELSE NULL
886 END
887
888FROM gs_base gs
889CROSS JOIN counts c
890
891JOIN account_cte a ON a.rn = gs.acc_rn
892JOIN account_cte a2 ON a2.rn = gs.acc2_rn
893
894JOIN currency_cte cu ON cu.rn = gs.cur_rn
895JOIN currency_cte cu2 ON cu2.rn = gs.cur2_rn
896
897JOIN loan_cte l ON l.rn = gs.loan_rn
898JOIN installment_cte i ON i.rn = gs.inst_rn
899
900JOIN type_cte t ON t.rn = gs.type_rn
901JOIN payment_cte p ON p.rn = gs.pay_rn
902JOIN emp_cte e ON e.rn = gs.emp_rn;
903
904
905
906--Receipt
907INSERT INTO Receipt (print_time, receipt_number, transaction_id)
908SELECT
909 t.transaction_date + (random() * interval '2 hours') AS print_time,
910
911 'RCPT-' ||
912 to_char(t.transaction_date, 'YYYYMMDD') || '-' ||
913 LPAD(ROW_NUMBER() OVER (ORDER BY t.transaction_id)::text, 9, '0') AS receipt_number,
914
915 t.transaction_id
916FROM Transaction t
917LIMIT 10000000;
918
919
920--InterestPayment
921INSERT INTO InterestPayment (
922 amount,
923 payment_date,
924 period_start,
925 period_end,
926 account_id,
927 transaction_id
928)
929SELECT
930 ROUND((t.amount * (0.001 + random() * 0.009))::numeric, 2) AS amount,
931
932 (period_end + (1 + floor(random() * 5))::int) AS payment_date,
933
934 period_start,
935 period_end,
936
937 t.account_id,
938 t.transaction_id
939
940FROM (
941 SELECT
942 tr.transaction_id,
943 tr.account_id,
944 tr.amount,
945
946 (tr.transaction_date - INTERVAL '1 month')::date AS period_start,
947 tr.transaction_date::date AS period_end
948
949 FROM Transaction tr
950 INNER JOIN SavingsAccount sa
951 ON tr.account_id = sa.account_id
952 WHERE tr.amount > 0
953) t;