Context Navigation

Back to DatabaseCreation

DatabaseCreation: dml.sql

File dml.sql, 61.9 KB (added by 231151, 12 days ago)

Line
1	-- 1. AGENCY
2	INSERT INTO AGENCY (AGENCY_ID, AGENCY_NAME, AGENCY_URL, AGENCY_TIMEZONE, AGENCY_LANG, AGENCY_PHONE, AGENCY_EMAIL, AGENCY_FARE_URL) VALUES
3	(1, 'Public Transport Victoria', 'https://www.ptv.vic.gov.au', 'Australia/Melbourne', 'en', '075234354', 'info@ptv.vic.gov.au', 'https://www.ptv.vic.gov.au/tickets'),
4	(2, 'Metro Trains Melbourne', 'https://www.metrotrains.com.au', 'Australia/Melbourne', 'en', '077849265', 'info@metrotrains.com.au', 'https://www.metrotrains.com.au/fares'),
5	(3, 'Yarra Trams', 'https://www.yarratrams.com.au', 'Australia/Melbourne', 'en', '076785458', 'info@yarratrams.com.au', 'https://www.yarratrams.com.au/fares'),
6	(4, 'Melbourne Bus Link', 'https://www.melbournebuslink.com.au', 'Australia/Melbourne', 'en', '075159357', 'info@melbournebuslink.com.au', 'https://www.melbournebuslink.com.au/fares'),
7	(5, 'Transdev Melbourne', 'https://www.transdev.com.au', 'Australia/Melbourne', 'en', '077358851', 'info@transdev.com.au', 'https://www.transdev.com.au/fares');
8
9
10	-- 2. DISCOUNT
11	INSERT INTO DISCOUNT (DISCOUNT_ID, DISCOUNT_NAME, DISCOUNT_PERCENTAGE, DESCRIPTION, ELIGIBILITY_CRITERIA, VALID_FROM, VALID_TO) VALUES
12	(1, 'Student Discount', 30.0, 'Discount for students', 'Valid student ID required', '2020-01-01', '2030-12-31'),
13	(2, 'Senior Discount', 40.0, 'Discount for seniors 65+', 'Age 65 or above', '2020-01-01', '2030-12-31'),
14	(3, 'Child Discount', 50.0, 'Discount for children under 12', 'Age under 12', '2020-01-01', '2030-12-31'),
15	(4, 'Disabled Discount',60.0, 'Discount for disabled persons', 'Valid disability certificate', '2020-01-01', '2030-12-31'),
16	(5, 'Staff Discount', 100.0, 'Free travel for staff', 'Valid employee ID', '2020-01-01', '2030-12-31'),
17	(6, 'Weekend Promo', 20.0, 'Weekend promotional discount', 'Valid on weekends only', '2023-01-01', '2025-12-31'),
18	(7, 'Monthly Pass', 15.0, 'Discount for monthly pass holders', 'Active monthly pass required', '2021-01-01', '2030-12-31'),
19	(8, 'Early Bird', 10.0, 'Early morning travel discount', 'Trips before 7:00 AM', '2022-06-01', '2026-12-31'),
20	(9, 'Group Discount', 25.0, 'Discount for groups of 5+', 'Minimum 5 persons', '2021-01-01', '2030-12-31'),
21	(10, 'Annual Pass', 18.0, 'Discount for annual pass holders', 'Active annual pass required', '2020-01-01', '2030-12-31');
22
23
24	-- 3. ZONE
25	INSERT INTO ZONE (ZONE_ID, ZONE_NAME, ZONE_NUMBER, DESCRIPTION) VALUES
26	('Z01', 'City Centre', 1, 'Zone 1 coverage area'),
27	('Z02', 'Inner North', 2, 'Zone 2 coverage area'),
28	('Z03', 'Inner South', 3, 'Zone 3 coverage area'),
29	('Z04', 'Inner East', 4, 'Zone 4 coverage area'),
30	('Z05', 'Inner West', 5, 'Zone 5 coverage area'),
31	('Z06', 'Outer North', 6, 'Zone 6 coverage area'),
32	('Z07', 'Outer South', 7, 'Zone 7 coverage area'),
33	('Z08', 'Outer East', 8, 'Zone 8 coverage area'),
34	('Z09', 'Outer West', 9, 'Zone 9 coverage area'),
35	('Z10', 'CBD Core', 10, 'Zone 10 coverage area'),
36	('Z11', 'Airport Zone', 11, 'Zone 11 coverage area'),
37	('Z12', 'University Zone',12, 'Zone 12 coverage area'),
38	('Z13', 'Suburban North', 13, 'Zone 13 coverage area'),
39	('Z14', 'Suburban South', 14, 'Zone 14 coverage area'),
40	('Z15', 'Suburban East', 15, 'Zone 15 coverage area'),
41	('Z16', 'Suburban West', 16, 'Zone 16 coverage area'),
42	('Z17', 'Regional A', 17, 'Zone 17 coverage area'),
43	('Z18', 'Regional B', 18, 'Zone 18 coverage area'),
44	('Z19', 'Regional C', 19, 'Zone 19 coverage area'),
45	('Z20', 'Regional D', 20, 'Zone 20 coverage area');
46
47
48	-- 4. VEHICLE_TYPE
49	INSERT INTO VEHICLE_TYPE (VEHICLE_TYPE_ID, TYPE_NAME, DESCRIPTION) VALUES
50	(0, 'BUS', 'Road vehicle used for flexible urban and suburban passenger transport.'),
51	(1, 'TRAM', 'Electric rail vehicle operating on city streets with fixed stops and routes.'),
52	(2, 'TRAIN', 'Rail vehicle used for long-distance or regional passenger transport with high capacity.');
53
54
55	-- 5. STOPS
56	INSERT INTO STOPS (STOP_ID, STOP_NAME, LATITUDE, LONGITUDE, ZONE_ID, VEHICLE_TYPE_ID) VALUES
57	('1000', 'Dole Ave/Cheddar Rd', -37.70077481, 145.01895105, 'Z09', 0),
58	('10001', 'Rex St/Taylors Rd', -37.72697521, 144.77615243, 'Z06', 0),
59	('10002', 'Yuille St/Centenary Ave', -37.6761595, 144.59578941, 'Z13', 0),
60	('10009', 'Gum Rd/Main Rd West', -37.74149711, 144.77589939, 'Z06', 0),
61	('1001', 'Lloyd Ave/Cheddar Rd', -37.69918301, 145.01968529, 'Z09', 0),
62	('10010', 'Kings Rd/Main Rd West', -37.74194553, 144.78008474, 'Z06', 0),
63	('10011', 'Moffat St/Main Rd West', -37.7423246, 144.7834665, 'Z06', 0),
64	('10012', 'Washington St/Main Rd West', -37.74279566, 144.78791229, 'Z06', 0),
65	('10013', 'Kate St/Main Rd West', -37.74356938, 144.79457342, 'Z06', 0);
66
67	INSERT INTO STOPS (STOP_ID, STOP_NAME, LATITUDE, LONGITUDE, ZONE_ID, VEHICLE_TYPE_ID)
68	SELECT
69	gs::varchar,
70	'Stop ' \|\| gs,
71	(-37.5 - random() * 0.6)::decimal(10,7),
72	(144.5 + random() * 0.9)::decimal(10,7),
73	'Z' \|\| lpad((1 + (gs % 20))::text, 2, '0'),
74	(gs % 3)
75	FROM generate_series(1002, 27000) gs
76	WHERE gs NOT IN (1000,1001,10001,10002,10009,10010,10011,10012,10013)
77	ON CONFLICT (STOP_ID, VEHICLE_TYPE_ID) DO NOTHING;
78
79
80	-- 6. ROUTE
81	INSERT INTO ROUTE (ROUTE_ID, AGENCY_ID, START_STOP_ID, START_STOP_VT, END_STOP_ID, END_STOP_VT,
82	ROUTE_NAME, DISTANCE_KM, ESTIMATED_DURATION, VEHICLE_TYPE_ID) VALUES
83	('14-200-aus-1', 3, NULL, 0, NULL, 0, 'Bulleen - City (Queen St)', NULL, NULL, 0),
84	('14-207-aus-1', 5, NULL, 0, NULL, 0, 'Doncaster SC - City (Queen St)', NULL, NULL, 0),
85	('14-270-aus-1', 1, NULL, 0, NULL, 0, 'Mitcham - Box Hill', NULL, NULL, 0),
86	('14-271-aus-1', 1, NULL, 0, NULL, 0, 'Ringwood - Box Hill Station', NULL, NULL, 0),
87	('14-273-aus-1', 1, NULL, 0, NULL, 0, 'The Pines SC - Nunawading Station', NULL, NULL, 0),
88	('14-279-aus-1', 5, NULL, 0, NULL, 0, 'Doncaster SC/Templestowe - Box Hill Station', NULL, NULL, 0),
89	('14-280-aus-1', 5, NULL, 0, NULL, 0, 'The Pines SC', NULL, NULL, 0),
90	('14-281-aus-1', 5, NULL, 0, NULL, 0, 'Box Hill Station - Templestowe', NULL, NULL, 0),
91	('14-282-aus-1', 2, NULL, 0, NULL, 0, 'The Pines SC', NULL, NULL, 0);
92
93	INSERT INTO ROUTE (ROUTE_ID, AGENCY_ID, START_STOP_ID, START_STOP_VT, END_STOP_ID, END_STOP_VT,
94	ROUTE_NAME, DISTANCE_KM, ESTIMATED_DURATION, VEHICLE_TYPE_ID)
95	SELECT
96	(ARRAY['14','19','21','26','32','37','40','42','43','53','58','60','65'])[1 + (gs % 13)]
97	\|\| '-' \|\| (gs * 7 % 900 + 100)::text \|\| '-aus-1',
98	(1 + (gs % 5)),
99	NULL, 0, NULL, 0,
100	(ARRAY['City - Airport Express','CBD Loop Service','Suburban Connector',
101	'University Shuttle','Bayside Express','Northern Link',
102	'Eastern Corridor','Western Loop','Inner Circle',
103	'Peak Express','Night Rider','Cross-City Link','Orbital Route'])[1 + (gs % 13)],
104	NULL, NULL,
105	(gs % 3)
106	FROM generate_series(1, 700) gs
107	ON CONFLICT (ROUTE_ID) DO NOTHING;
108
109
110	-- 7. ROUTE_STOP
111	INSERT INTO ROUTE_STOP (ROUTE_STOP_ID, ROUTE_ID, STOP_ID, STOP_VT, STOP_SEQUENCE, DISTANCE_TRAVELED, TIMEPOINT) VALUES
112	(1, '14-200-aus-1', '5520', 0, 0, 0.54, 1),
113	(2, '14-200-aus-1', '787', 0, 1, 2.11, 1),
114	(3, '14-200-aus-1', '28597', 0, 2, 3.77, 1),
115	(4, '14-200-aus-1', '37721', 0, 3, 4.21, 1),
116	(5, '14-200-aus-1', '9963', 0, 4, 6.68, 1),
117	(6, '14-200-aus-1', '18764', 0, 5, 9.00, 1),
118	(7, '14-207-aus-1', '1628', 0, 0, 1.87, 1),
119	(8, '14-207-aus-1', '50851', 0, 1, 3.39, 1),
120	(9, '14-207-aus-1', '28723', 0, 2, 5.50, 1),
121	(10, '14-207-aus-1', '3545', 0, 3, 6.43, 1);
122
123	INSERT INTO ROUTE_STOP (ROUTE_ID, STOP_ID, STOP_VT, STOP_SEQUENCE, DISTANCE_TRAVELED, TIMEPOINT)
124	SELECT
125	r.route_id,
126	(1002 + ((r.rn * 15 + s.seq - 1) % 25999))::varchar,
127	0,
128	s.seq - 1,
129	((s.seq - 1) * (0.5 + random() * 2.5))::decimal(8,2),
130	1
131	FROM
132	(SELECT route_id, row_number() OVER () AS rn FROM ROUTE) r,
133	generate_series(1, 15) s(seq)
134	LIMIT 10990;
135
136
137	-- 8. SHAPE
138	INSERT INTO SHAPE (SHAPE_ID, DESCRIPTION, VEHICLE_TYPE_ID) VALUES
139	('14-200-aus-1.2.H', 'Southern branch line', 0),
140	('14-200-aus-1.3.R', 'Direct service path', 0),
141	('14-207-aus-1.2.R', 'Reverse direction path',0),
142	('14-207-aus-1.3.H', 'Southern branch line', 0),
143	('14-270-aus-1.2.R', 'Suburban connector', 0),
144	('14-270-aus-1.3.H', 'Main corridor route', 0),
145	('14-271-aus-1.2.R', 'Reverse direction path',0),
146	('14-271-aus-1.3.R', 'Peak hour variant', 0),
147	('14-271-aus-1.4.R', 'Main corridor route', 0);
148
149	INSERT INTO SHAPE (SHAPE_ID, DESCRIPTION, VEHICLE_TYPE_ID)
150	SELECT
151	route_id \|\| '.' \|\| variant \|\| '.' \|\| dir,
152	(ARRAY['Main corridor route','Reverse direction path','Southern branch line',
153	'Direct service path','Peak hour variant','Suburban connector'])[1 + (row_number() OVER () % 6)::int],
154	(row_number() OVER () % 3)::int
155	FROM
156	ROUTE
157	CROSS JOIN (VALUES (2),(3),(4)) v(variant)
158	CROSS JOIN (VALUES ('H'),('R')) d(dir)
159	ON CONFLICT (SHAPE_ID) DO NOTHING
160	LIMIT 5000;
161
162
163	-- 9. SHAPE_POINT
164	INSERT INTO SHAPE_POINT (SHAPE_ID, SHAPE_PT_SEQUENCE, SHAPE_PT_LAT, SHAPE_PT_LON, SHAPE_DIST_TRAVELED, VEHICLE_TYPE_ID) VALUES
165	('14-200-aus-1.2.H', 1, -37.81615682, 144.96115858, 0.0, 0),
166	('14-200-aus-1.2.H', 2, -37.81601034, 144.96109377, 17.25, 0),
167	('14-200-aus-1.2.H', 3, -37.81596499, 144.96107707, 22.51, 0),
168	('14-200-aus-1.2.H', 4, -37.81527434, 144.96074461, 104.67, 0),
169	('14-200-aus-1.2.H', 5, -37.81501394, 144.96063193, 135.27, 0),
170	('14-200-aus-1.2.H', 6, -37.81490367, 144.96058714, 148.15, 0),
171	('14-200-aus-1.2.H', 7, -37.81452051, 144.96041139, 193.46, 0),
172	('14-200-aus-1.2.H', 8, -37.81399553, 144.96016946, 255.59, 0);
173
174	INSERT INTO SHAPE_POINT (SHAPE_ID, SHAPE_PT_SEQUENCE, SHAPE_PT_LAT, SHAPE_PT_LON, SHAPE_DIST_TRAVELED, VEHICLE_TYPE_ID)
175	SELECT
176	s.shape_id,
177	pt.seq,
178	(-37.5 - random() * 0.6)::decimal(10,7),
179	(144.5 + random() * 0.9)::decimal(10,7),
180	((pt.seq - 1) * (15.0 + random() * 30.0))::decimal(8,2),
181	s.vehicle_type_id
182	FROM SHAPE s
183	CROSS JOIN generate_series(1, 500) pt(seq)
184	ON CONFLICT (SHAPE_ID, SHAPE_PT_SEQUENCE) DO NOTHING;
185
186
187	-- 10. VEHICLE
188	INSERT INTO VEHICLE (VEHICLE_ID, VEHICLE_TYPE_ID, REGISTRATION_NUMBER, MODEL, MANUFACTURER, CAPACITY, YEAR_OF_MANUFACTURE, STATUS) VALUES
189	('V0', 2, 'QN606RN', 'Fast-97', 'Hyundai', 111, 1997, 'MAINTENANCE'),
190	('V1', 0, 'CO767XD', 'Metro-57', 'MAN', 214, 2007, 'ACTIVE'),
191	('V2', 1, 'RV015SV', 'Metro-17', 'CAF', 281, 1997, 'MAINTENANCE'),
192	('V3', 2, 'UF352KU', 'City-60', 'CAF', 235, 2023, 'MAINTENANCE'),
193	('V4', 0, 'IX890WA', 'Urban-80', 'Scania', 122, 2012, 'INACTIVE'),
194	('V5', 2, 'XH211TJ', 'Urban-99', 'Alstom', 226, 2025, 'INACTIVE'),
195	('V6', 1, 'VY493NL', 'Fast-54', 'Scania', 189, 2018, 'INACTIVE'),
196	('V7', 2, 'CC480GL', 'Urban-57', 'Alstom', 276, 2022, 'ACTIVE'),
197	('V8', 2, 'HQ918DJ', 'Metro-63', 'Alstom', 163, 2000, 'MAINTENANCE'),
198	('V9', 0, 'JM685VA', 'Urban-11', 'Scania', 77, 2000, 'MAINTENANCE'),
199	('V10', 2, 'YT666UR', 'Metro-91', 'Skoda', 118, 2024, 'ACTIVE');
200
201	INSERT INTO VEHICLE (VEHICLE_ID, VEHICLE_TYPE_ID, REGISTRATION_NUMBER, MODEL, MANUFACTURER,
202	CAPACITY, YEAR_OF_MANUFACTURE, STATUS)
203	SELECT
204	'V' \|\| gs,
205	(gs % 3),
206	chr(65 + (gs * 7 % 26)) \|\| chr(65 + (gs * 13 % 26)) \|\|
207	lpad((gs % 1000)::text, 3, '0') \|\|
208	chr(65 + (gs * 3 % 26)) \|\| chr(65 + (gs * 17 % 26)),
209	(ARRAY['Metro','Urban','City','Fast','Express'])[1 + (gs % 5)] \|\| '-' \|\| (gs % 100),
210	(ARRAY['MAN','CAF','Scania','Alstom','Skoda','Hyundai','Siemens'])[1 + (gs % 7)],
211	(60 + (gs % 3) * 80 + (gs % 80)),
212	1997 + (gs % 29),
213	CASE
214	WHEN gs % 50 = 0 THEN 'MAINTENANCE'
215	WHEN gs % 100 = 0 THEN 'INACTIVE'
216	WHEN gs % 200 = 0 THEN 'DECOMMISSIONED'
217	ELSE 'ACTIVE'
218	END
219	FROM generate_series(11, 4999) gs
220	ON CONFLICT (VEHICLE_ID) DO NOTHING;
221
222
223	-- 11. EMPLOYEE
224	INSERT INTO EMPLOYEE (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, PHONE, EMAIL, HIRE_DATE, POSITION, STATUS, PASSWORD_HASH) VALUES
225	('E0', 'Paul', 'Martin', '075414458', 'paulmartin@outlook.com', '2023-09-06', 'Discount_officer', 'TERMINATED', '41a3f483bbdf22492001f893f987b3bc19fe8f2125231c99a734f7b29ef0319e'),
226	('E1', 'Emily', 'Snyder', '075196141', 'emilysnyder@yahoo.com', '2018-03-08', 'Conductor', 'ACTIVE', '4c03f9fac0559d8433430a7e4a2af55cc3bbb44b0a776bd6d38e23bc4ff38d62'),
227	('E2', 'Kim', 'Cabrera', '076005797', 'kimcabrera@yahoo.com', '2017-10-03', 'Conductor', 'ON_LEAVE', '5cab693cc34d346868d2602aeef0654fa0b0fe0c962fb1071c8da27e450f603f'),
228	('E3', 'Stephanie', 'Flynn', '075653688', 'stephanieflynn@gmail.com', '2021-08-22', 'Driver', 'ACTIVE', '8533c71de6850d87dfae7530c8955b19807e9851b24a72b973ad6f627bf10e12'),
229	('E4', 'Felicia', 'Monroe', '077681952', 'feliciamonroe@gmail.com', '2025-10-24', 'Conductor', 'TERMINATED', 'c43e6969a7e75780a42bade53e028d882d92ebfe5690d58d8ce8c847b174bd35'),
230	('E5', 'Jason', 'Silva', '077455841', 'jasonsilva@gmail.com', '2023-07-04', 'Conductor', 'ON_LEAVE', 'aaf62b95e13e4373aaef89e63eae1fc82bee8b5f051e026d34dcfc2c74d46626'),
231	('E6', 'William', 'Odonnell', '077831609', 'williamodonnell@yahoo.com', '2020-06-01', 'Conductor', 'ACTIVE', 'c7fb27d867de03cd011f86cf2724fe9ebb83a9d25c6367c33f58bc48bc041fc5'),
232	('E7', 'Jim', 'Cook', '075765534', 'jimcook@gmail.com', '2022-08-18', 'Driver', 'ACTIVE', 'd89b57880d9fa55631a4a04b797cc8ae6cd5f92f5c6d515345a8bb8da6dece29'),
233	('E8', 'Timothy', 'Fitzgerald', '077182043', 'timothyfitzgerald@yahoo.com', '2017-09-25', 'Conductor', 'ON_LEAVE', '14f93a567627d2405be356c9892aebafaf96b2f7e17948c7d6248e04c982fc19'),
234	('E9', 'Anthony', 'Jones', '076571402', 'anthonyjones@yahoo.com', '2018-03-30', 'Conductor', 'ON_LEAVE', 'e2b88297bf85c18e74479805304590040747018c03fb0572ce8e8e7d1afc35d1'),
235	('E10', 'Derek', 'Lynn', '075734317', 'dereklynn@outlook.com', '2021-01-31', 'Driver', 'INACTIVE', 'b77f5395f38a55f7bc1a7a9124bab208bac8f5fabd2dd47ae1c329eede4de554');
236
237	INSERT INTO EMPLOYEE (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, PHONE, EMAIL, HIRE_DATE, POSITION, STATUS, PASSWORD_HASH)
238	SELECT
239	'E' \|\| gs,
240	(ARRAY['Paul','Emily','Kim','Stephanie','Felicia','Jason','William','Jim','Timothy','Anthony',
241	'Derek','Sarah','Michael','Jessica','Robert','Jennifer','David','Lisa','James','Mary',
242	'John','Patricia','Charles','Linda','Daniel','Barbara','Matthew','Susan','Mark','Karen'])[1 + (gs % 30)],
243	(ARRAY['Martin','Snyder','Cabrera','Flynn','Monroe','Silva','Odonnell','Cook','Fitzgerald','Jones',
244	'Lynn','Smith','Johnson','Williams','Brown','Davis','Miller','Wilson','Moore','Taylor',
245	'Anderson','Thomas','Jackson','White','Harris','Garcia','Thompson','Young','Lee','Walker'])[1 + (gs % 30)],
246	'07' \|\| lpad((5000000 + gs * 7 % 5000000)::text, 7, '0'),
247	lower(
248	(ARRAY['paul','emily','kim','stephanie','felicia','jason','william','jim','timothy','anthony',
249	'derek','sarah','michael','jessica','robert','jennifer','david','lisa','james','mary',
250	'john','patricia','charles','linda','daniel','barbara','matthew','susan','mark','karen'])[1 + (gs % 30)]
251	\|\|
252	(ARRAY['martin','snyder','cabrera','flynn','monroe','silva','odonnell','cook','fitzgerald','jones',
253	'lynn','smith','johnson','williams','brown','davis','miller','wilson','moore','taylor',
254	'anderson','thomas','jackson','white','harris','garcia','thompson','young','lee','walker'])[1 + (gs % 30)]
255	) \|\| gs \|\| '@' \|\| (ARRAY['gmail.com','yahoo.com','outlook.com'])[1 + (gs % 3)],
256	CURRENT_DATE - (random() * 3650)::int,
257	CASE WHEN gs % 4 = 0 THEN 'Driver'
258	WHEN gs % 4 = 1 THEN 'Conductor'
259	WHEN gs % 4 = 2 THEN 'Discount_officer'
260	ELSE 'Manager' END,
261	CASE WHEN gs % 40 = 0 THEN 'TERMINATED'
262	WHEN gs % 20 = 0 THEN 'ON_LEAVE'
263	WHEN gs % 100 = 0 THEN 'INACTIVE'
264	ELSE 'ACTIVE' END,
265	md5('password' \|\| gs)
266	FROM generate_series(11, 24999) gs
267	ON CONFLICT (EMPLOYEE_ID) DO NOTHING;
268
269
270	-- 12. DISCOUNT_OFFICER
271	INSERT INTO DISCOUNT_OFFICER (OFFICER_ID, EMPLOYEE_ID, ASSIGNED_REGION, CERTIFICATION_CODE, AUTHORIZED_FROM, AUTHORIZED_TO) VALUES
272	('OFF0', 'E0', 'General', 'OFF-CERT-0000', '2023-09-06', '2029-05-18'),
273	('OFF1', 'E13', 'West', 'OFF-CERT-0001', '2022-07-08', '2023-12-25'),
274	('OFF2', 'E20', 'East', 'OFF-CERT-0002', '2020-05-15', '2029-11-02'),
275	('OFF3', 'E22', 'General', 'OFF-CERT-0003', '2016-10-17', '2018-04-25'),
276	('OFF4', 'E37', 'General', 'OFF-CERT-0004', '2025-05-17', '2028-02-23'),
277	('OFF5', 'E43', 'South', 'OFF-CERT-0005', '2016-05-30', '2025-06-27'),
278	('OFF6', 'E57', 'General', 'OFF-CERT-0006', '2019-05-17', '2020-12-13'),
279	('OFF7', 'E58', 'Central', 'OFF-CERT-0007', '2022-02-12', '2030-01-25'),
280	('OFF8', 'E63', 'South', 'OFF-CERT-0008', '2016-10-18', '2027-08-13'),
281	('OFF9', 'E71', 'General', 'OFF-CERT-0009', '2019-01-17', '2030-06-25'),
282	('OFF10', 'E73', 'West', 'OFF-CERT-0010', '2019-07-23', '2026-07-23');
283
284	INSERT INTO DISCOUNT_OFFICER (OFFICER_ID, EMPLOYEE_ID, ASSIGNED_REGION, CERTIFICATION_CODE, AUTHORIZED_FROM, AUTHORIZED_TO)
285	SELECT
286	'OFF' \|\| gs,
287	'E' \|\| (gs * 4 + 2),
288	(ARRAY['General','North','South','East','West','Central'])[1 + (gs % 6)],
289	'OFF-CERT-' \|\| lpad(gs::text, 4, '0'),
290	CURRENT_DATE - (random() * 3000)::int,
291	CURRENT_DATE + (random() * 1500)::int
292	FROM generate_series(11, 2499) gs
293	ON CONFLICT (OFFICER_ID) DO NOTHING;
294
295
296	-- 13. DISCOUNT_OFFICER_DISCOUNT
297	INSERT INTO DISCOUNT_OFFICER_DISCOUNT (ID, OFFICER_ID, DISCOUNT_ID, ASSIGNED_DATE, NOTES) VALUES
298	(1, 'OFF0', 9, '2024-02-05', 'Renewal of previous assignment'),
299	(2, 'OFF1', 10, '2025-06-25', 'Extended authorization period'),
300	(3, 'OFF2', 9, '2025-04-26', 'Authorized for student discounts'),
301	(4, 'OFF2', 4, '2024-06-05', 'Temporary authorization'),
302	(5, 'OFF3', 5, '2025-03-21', 'Granted for high-demand routes'),
303	(6, 'OFF3', 1, '2026-02-18', 'Authorized for student discounts'),
304	(7, 'OFF4', 9, '2026-01-06', 'Authorized for student discounts'),
305	(8, 'OFF4', 6, '2023-12-12', 'Renewal of previous assignment'),
306	(9, 'OFF5', 8, '2026-01-18', 'Authorized for senior discounts'),
307	(10, 'OFF5', 7, '2026-01-14', 'Extended authorization period');
308
309	INSERT INTO DISCOUNT_OFFICER_DISCOUNT (OFFICER_ID, DISCOUNT_ID, ASSIGNED_DATE, NOTES)
310	SELECT
311	'OFF' \|\| (gs % 2500),
312	(1 + (gs % 10)),
313	CURRENT_DATE - (random() * 800)::int,
314	(ARRAY['Renewal of previous assignment','Extended authorization period',
315	'Authorized for student discounts','Temporary authorization',
316	'Granted for high-demand routes','Authorized for senior discounts'])[1 + (gs % 6)]
317	FROM generate_series(11, 7500) gs;
318
319
320	-- 14. DRIVER
321	INSERT INTO DRIVER (DRIVER_ID, EMPLOYEE_ID, LICENSE_NUMBER, LICENSE_CATEGORY, LICENSE_EXPIRY_DATE, YEARS_EXPERIENCE) VALUES
322	('D0', 'E3', 'QP43551', 'D1', '2034-05-30', 22),
323	('D1', 'E7', 'ZA42594', 'D+E', '2029-10-11', 22),
324	('D2', 'E10', 'OV12030', 'D+E', '2028-12-23', 3),
325	('D3', 'E14', 'XV74573', 'D', '2032-12-01', 14),
326	('D4', 'E16', 'YT78687', 'D1', '2033-01-13', 15),
327	('D5', 'E21', 'QH30987', 'D+E', '2030-09-14', 5),
328	('D6', 'E24', 'KU95016', 'D+E', '2032-09-11', 13),
329	('D7', 'E28', 'UQ18105', 'D', '2029-12-10', 20),
330	('D8', 'E29', 'AZ58103', 'D+E', '2027-05-26', 4),
331	('D9', 'E33', 'PH24620', 'D', '2029-04-02', 21),
332	('D10', 'E34', 'NP46026', 'D1', '2034-04-30', 22);
333
334	INSERT INTO DRIVER (DRIVER_ID, EMPLOYEE_ID, LICENSE_NUMBER, LICENSE_CATEGORY, LICENSE_EXPIRY_DATE, YEARS_EXPERIENCE)
335	SELECT
336	'D' \|\| gs,
337	'E' \|\| (gs * 4),
338	chr(65 + (gs * 11 % 26)) \|\| chr(65 + (gs * 19 % 26)) \|\| lpad((gs % 100000)::text, 5, '0'),
339	(ARRAY['D','D1','D+E','DE'])[1 + (gs % 4)],
340	CURRENT_DATE + (random() * 3650)::int,
341	(random() * 25)::int
342	FROM generate_series(11, 12499) gs
343	ON CONFLICT (DRIVER_ID) DO NOTHING;
344
345
346	-- 15. CONDUCTOR
347	INSERT INTO CONDUCTOR (CONDUCTOR_ID, EMPLOYEE_ID, CERTIFICATION_NUMBER, HIRE_ROLE_DATE) VALUES
348	('CON0', 'E1', 'CERT00000', '2018-03-08'),
349	('CON1', 'E2', 'CERT00001', '2017-10-03'),
350	('CON2', 'E4', 'CERT00002', '2025-10-24'),
351	('CON3', 'E5', 'CERT00003', '2023-07-04'),
352	('CON4', 'E6', 'CERT00004', '2020-06-01'),
353	('CON5', 'E8', 'CERT00005', '2017-09-25'),
354	('CON6', 'E9', 'CERT00006', '2018-03-30'),
355	('CON7', 'E11', 'CERT00007', '2024-03-21'),
356	('CON8', 'E12', 'CERT00008', '2019-07-01'),
357	('CON9', 'E15', 'CERT00009', '2018-07-18'),
358	('CON10', 'E17', 'CERT00010', '2025-06-23');
359
360	INSERT INTO CONDUCTOR (CONDUCTOR_ID, EMPLOYEE_ID, CERTIFICATION_NUMBER, HIRE_ROLE_DATE)
361	SELECT
362	'CON' \|\| gs,
363	'E' \|\| (gs * 4 + 1),
364	'CERT' \|\| lpad(gs::text, 5, '0'),
365	CURRENT_DATE - (random() * 2500)::int
366	FROM generate_series(11, 9999) gs
367	ON CONFLICT (CONDUCTOR_ID) DO NOTHING;
368
369
370	-- 16. SHIFT
371	INSERT INTO SHIFT (SHIFT_ID, SHIFT_NAME, START_TIME, END_TIME, DESCRIPTION) VALUES
372	(1, 'First Shift', '06:00:00', '14:00:00', 'Morning shift'),
373	(2, 'Second Shift', '14:00:00', '22:00:00', 'Afternoon shift'),
374	(3, 'Third Shift', '22:00:00', '06:00:00', 'Night shift');
375
376
377	-- 17. TRIP
378	INSERT INTO TRIP (TRIP_ID, ROUTE_ID, VEHICLE_ID, DRIVER_ID, SHAPE_ID, TRIP_HEADSIGN,
379	START_TIME, END_TIME, DIRECTION_ID, WHEELCHAIR_ACCESSIBLE,
380	TRIP_DATE, STATUS, VEHICLE_TYPE_ID) VALUES
381	('14-200--1-MF1-200003', '14-200-aus-1', 'V1413', 'D8556', '14-200-aus-1.3.R', 'Little Collins St', '05:49:00', '06:32:00', 1, 1, '2026-05-12', 'SCHEDULED', 0),
382	('14-200--1-MF1-200005', '14-200-aus-1', 'V1984', 'D7108', '14-200-aus-1.3.R', 'Little Collins St', '06:09:00', '06:53:00', 1, 1, '2026-05-27', 'SCHEDULED', 0),
383	('14-200--1-MF1-200006', '14-200-aus-1', 'V1785', 'D9367', '14-200-aus-1.2.H', 'Bulleen Terminus', '06:40:00', '07:24:00', 0, 1, '2025-11-28', 'DELAYED', 0),
384	('14-200--1-MF1-200008', '14-200-aus-1', 'V4536', 'D4139', '14-200-aus-1.2.H', 'Bulleen Terminus', '07:00:00', '07:48:00', 0, 1, '2025-06-06', 'COMPLETED', 0),
385	('14-200--1-MF1-200010', '14-200-aus-1', 'V2055', 'D10469','14-200-aus-1.2.H', 'Bulleen Terminus', '07:15:00', '08:05:00', 0, 1, '2026-10-04', 'COMPLETED', 0),
386	('14-200--1-MF1-200012', '14-200-aus-1', 'V4594', 'D1584', '14-200-aus-1.2.H', 'Bulleen Terminus', '07:25:00', '08:17:00', 0, 1, '2025-08-31', 'IN_PROGRESS', 0),
387	('14-200--1-MF1-200013', '14-200-aus-1', 'V3551', 'D9934', '14-200-aus-1.3.R', 'Little Collins St', '07:09:00', '08:09:00', 1, 1, '2025-10-09', 'SCHEDULED', 0),
388	('14-200--1-MF1-200014', '14-200-aus-1', 'V2498', 'D3477', '14-200-aus-1.2.H', 'Bulleen Terminus', '07:40:00', '08:36:00', 0, 1, '2026-12-15', 'COMPLETED', 0),
389	('14-200--1-MF1-200016', '14-200-aus-1', 'V1071', 'D8719', '14-200-aus-1.2.H', 'Bulleen Terminus', '08:00:00', '08:57:00', 0, 1, '2025-02-04', 'SCHEDULED', 0),
390	('14-200--1-MF1-200017', '14-200-aus-1', 'V1695', 'D5282', '14-200-aus-1.3.R', 'Little Collins St', '07:30:00', '08:40:00', 1, 1, '2025-08-01', 'SCHEDULED', 0),
391	('14-200--1-MF1-200018', '14-200-aus-1', 'V235', 'D2267', '14-200-aus-1.2.H', 'Bulleen Terminus', '08:20:00', '09:14:00', 0, 1, '2025-04-15', 'DELAYED', 0),
392	('14-200--1-MF1-200020', '14-200-aus-1', 'V2364', 'D1572', '14-200-aus-1.2.H', 'Bulleen Terminus', '08:40:00', '09:33:00', 0, 1, '2026-04-17', 'COMPLETED', 0);
393
394	INSERT INTO VEHICLE (VEHICLE_ID, VEHICLE_TYPE_ID, REGISTRATION_NUMBER, MODEL, MANUFACTURER, CAPACITY, YEAR_OF_MANUFACTURE, STATUS)
395	SELECT 'V' \|\| gs, 0, 'AA' \|\| lpad(gs::text,3,'0') \|\| 'AA', 'Urban-' \|\| gs, 'MAN', 100, 2010, 'ACTIVE'
396	FROM generate_series(1000, 12500) gs
397	ON CONFLICT (VEHICLE_ID) DO NOTHING;
398
399	INSERT INTO DRIVER (DRIVER_ID, EMPLOYEE_ID, LICENSE_NUMBER, LICENSE_CATEGORY, LICENSE_EXPIRY_DATE, YEARS_EXPERIENCE)
400	SELECT 'D' \|\| gs, 'E' \|\| LEAST(gs * 2, 24999), 'XX' \|\| lpad(gs::text,5,'0'), 'D', '2030-01-01', 5
401	FROM generate_series(1000, 12499) gs
402	ON CONFLICT (DRIVER_ID) DO NOTHING;
403
404	INSERT INTO EMPLOYEE (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, PHONE, EMAIL, HIRE_DATE, POSITION, STATUS, PASSWORD_HASH)
405	SELECT 'E' \|\| gs, 'First'\|\|gs, 'Last'\|\|gs, '0700000000', 'emp'\|\|gs\|\|'@mail.com', '2015-01-01', 'Driver', 'ACTIVE', md5(gs::text)
406	FROM generate_series(25000, 30000) gs
407	ON CONFLICT (EMPLOYEE_ID) DO NOTHING;
408
409	INSERT INTO SHAPE (SHAPE_ID, DESCRIPTION, VEHICLE_TYPE_ID)
410	SELECT 'SHAPE-GEN-' \|\| gs, 'Generated shape', gs % 3
411	FROM generate_series(1, 500) gs
412	ON CONFLICT (SHAPE_ID) DO NOTHING;
413
414	INSERT INTO TRIP (TRIP_ID, ROUTE_ID, VEHICLE_ID, DRIVER_ID, SHAPE_ID,
415	TRIP_HEADSIGN, START_TIME, END_TIME, DIRECTION_ID,
416	WHEELCHAIR_ACCESSIBLE, TRIP_DATE, STATUS, VEHICLE_TYPE_ID)
417	SELECT
418	r.route_id \|\| '--' \|\| (gs % 2) \|\| '-MF' \|\| (1 + (gs % 14)) \|\| '-' \|\| (gs * 3 + 100),
419	r.route_id,
420	'V' \|\| (1000 + (gs % 3500)),
421	'D' \|\| (1000 + (gs % 3500)),
422	'SHAPE-GEN-' \|\| (1 + (gs % 500)),
423	(ARRAY['City CBD','Airport Terminal','Southern Cross Station','Flinders Street',
424	'Melbourne Central','Ringwood','Dandenong','Frankston',
425	'Box Hill','Sunshine','Broadmeadows','Werribee','Lilydale','Belgrave'])[1 + (gs % 14)],
426	(TIME '05:00' + ((gs % 540) * INTERVAL '2 minutes')),
427	(TIME '05:40' + ((gs % 540) * INTERVAL '2 minutes')),
428	gs % 2,
429	gs % 2,
430	CURRENT_DATE - (random() * 730)::int,
431	(ARRAY['SCHEDULED','SCHEDULED','SCHEDULED','IN_PROGRESS','COMPLETED','COMPLETED','COMPLETED','CANCELLED','DELAYED'])[1 + (gs % 9)],
432	r.vt
433	FROM
434	(SELECT route_id, (row_number() OVER ()) AS rn,
435	(CASE WHEN route_id LIKE '14-%' THEN 0
436	WHEN route_id LIKE '58-%' OR route_id LIKE '21-%' THEN 1
437	ELSE 2 END) AS vt
438	FROM ROUTE) r
439	CROSS JOIN generate_series(1, 544) gs
440	ON CONFLICT (TRIP_ID) DO NOTHING
441	LIMIT 379988;
442
443	-- 18. STOP_TIME
444	INSERT INTO STOP_TIME (STOP_TIME_ID, TRIP_ID, STOP_ID, STOP_VT, ROUTE_STOP_ID,
445	ARRIVAL_TIME, DEPARTURE_TIME, STOP_SEQUENCE, SHAPE_DIST_TRAVELED, VEHICLE_TYPE_ID) VALUES
446	('37-503--1-MF14-503180_1', '37-503--1-MF14-503180', '18771', 0, NULL, '07:20:00', '07:20:00', 1, 0.0, 0),
447	('37-503--1-MF14-503180_2', '37-503--1-MF14-503180', '6697', 0, NULL, '07:22:00', '07:22:00', 2, 354.81, 0),
448	('37-503--1-MF14-503180_3', '37-503--1-MF14-503180', '10549', 0, NULL, '07:23:00', '07:23:00', 3, 745.13, 0),
449	('37-503--1-MF14-503180_4', '37-503--1-MF14-503180', '10548', 0, NULL, '07:24:00', '07:24:00', 4, 924.8, 0),
450	('37-503--1-MF14-503180_5', '37-503--1-MF14-503180', '10547', 0, NULL, '07:25:00', '07:25:00', 5, 1092.79, 0),
451	('37-503--1-MF14-503180_6', '37-503--1-MF14-503180', '10546', 0, NULL, '07:25:00', '07:25:00', 6, 1256.34, 0),
452	('37-503--1-MF14-503180_7', '37-503--1-MF14-503180', '8854', 0, NULL, '07:27:00', '07:27:00', 7, 1591.89, 0),
453	('37-503--1-MF14-503180_8', '37-503--1-MF14-503180', '10544', 0, NULL, '07:28:00', '07:28:00', 8, 1814.19, 0),
454	('37-503--1-MF14-503180_9', '37-503--1-MF14-503180', '10543', 0, NULL, '07:29:00', '07:29:00', 9, 2010.66, 0),
455	('37-503--1-MF14-503180_10', '37-503--1-MF14-503180', '10542', 0, NULL, '07:30:00', '07:30:00', 10, 2191.9, 0);
456
457	DO $$
458	DECLARE
459	batch INT := 5000;
460	total INT := 380000;
461	stops_per INT := 37;
462	i INT := 0;
463	trip_ids TEXT[];
464	BEGIN
465	SELECT array_agg(trip_id ORDER BY trip_id)
466	INTO trip_ids
467	FROM TRIP;
468
469	WHILE i < total LOOP
470	INSERT INTO STOP_TIME (
471	STOP_TIME_ID, TRIP_ID, STOP_ID, STOP_VT,
472	ROUTE_STOP_ID, ARRIVAL_TIME, DEPARTURE_TIME,
473	STOP_SEQUENCE, SHAPE_DIST_TRAVELED, VEHICLE_TYPE_ID
474	)
475	SELECT
476	trip_ids[t_idx] \|\| '_' \|\| s_seq,
477	trip_ids[t_idx],
478	(1002 + ((t_idx * stops_per + s_seq) % 25999))::varchar,
479	0,
480	NULL,
481	(TIME '05:00' + ((t_idx % 540) * INTERVAL '2 minutes') + ((s_seq-1) * INTERVAL '90 seconds')),
482	(TIME '05:00' + ((t_idx % 540) * INTERVAL '2 minutes') + ((s_seq-1) * INTERVAL '90 seconds') + INTERVAL '20 seconds'),
483	s_seq,
484	((s_seq-1) * (200 + random() * 400))::decimal(8,2),
485	0
486	FROM generate_series(i+1, LEAST(i+batch, total)) t_idx,
487	generate_series(1, stops_per) s_seq
488	ON CONFLICT (STOP_TIME_ID) DO NOTHING;
489
490	i := i + batch;
491	RAISE NOTICE 'stop_time: % / % trips done', i, total;
492	END LOOP;
493	END;
494	$$;
495
496	-- 19. TRANSFER
497	INSERT INTO TRANSFER (TRANSFER_ID, FROM_STOP_ID, FROM_STOP_VT, TO_STOP_ID, TO_STOP_VT, FROM_ROUTE_ID, TO_ROUTE_ID, VEHICLE_TYPE_ID) VALUES
498	(1, '11212', 2, '11212', 2, 'aus:vic:vic-02-MDD:', 'aus:vic:vic-02-HBE:', 2),
499	(2, '11212', 2, '11212', 2, 'aus:vic:vic-02-HBE:', 'aus:vic:vic-02-MDD:', 2),
500	(3, '11212', 2, '11212', 2, 'aus:vic:vic-02-HBE:', 'aus:vic:vic-02-MDD:', 2),
501	(4, '11212', 2, '11212', 2, 'aus:vic:vic-02-HBE:', 'aus:vic:vic-02-MDD:', 2),
502	(5, '11212', 2, '11212', 2, 'aus:vic:vic-02-HBE:', 'aus:vic:vic-02-MDD:', 2),
503	(6, '11212', 2, '11212', 2, 'aus:vic:vic-02-HBE:', 'aus:vic:vic-02-HBE:', 2),
504	(7, '11212', 2, '11212', 2, 'aus:vic:vic-02-HBE:', 'aus:vic:vic-02-HBE:', 2),
505	(8, '11212', 2, '11212', 2, 'aus:vic:vic-02-HBE:', 'aus:vic:vic-02-HBE:', 2),
506	(9, '11212', 2, '11212', 2, 'aus:vic:vic-02-HBE:', 'aus:vic:vic-02-MDD:', 2),
507	(10, '11212', 2, '11212', 2, 'aus:vic:vic-02-HBE:', 'aus:vic:vic-02-MDD:', 2);
508
509	INSERT INTO TRANSFER (FROM_STOP_ID, FROM_STOP_VT, TO_STOP_ID, TO_STOP_VT, FROM_ROUTE_ID, TO_ROUTE_ID, VEHICLE_TYPE_ID)
510	SELECT
511	(1002 + (gs % 25999))::varchar,
512	(gs % 3),
513	(1002 + ((gs+1) % 25999))::varchar,
514	(gs % 3),
515	r1.route_id,
516	r2.route_id,
517	(gs % 3)
518	FROM generate_series(11, 15000) gs
519	JOIN LATERAL (SELECT route_id FROM ROUTE ORDER BY random() LIMIT 1) r1 ON TRUE
520	JOIN LATERAL (SELECT route_id FROM ROUTE ORDER BY random() LIMIT 1) r2 ON TRUE;
521
522
523	-- 20. DELAY_LOG
524	INSERT INTO DELAY_LOG (DELAY_ID, TRIP_ID, STOP_ID, STOP_VT, DELAY_MINUTES, DELAY_REASON, REPORTED_TIME) VALUES
525	(1, '14-200--1-MF1-200003', '14923', 0, 38, 'Vehicle breakdown', '2026-04-20 17:46:13'),
526	(2, '14-200--1-MF1-200003', '22664', 0, 23, 'Weather conditions', '2026-04-17 05:47:13'),
527	(3, '14-200--1-MF1-200003', '19599', 1, 33, 'Signal problem', '2026-04-19 16:01:13'),
528	(4, '14-200--1-MF1-200008', '20746', 0, 21, 'Signal problem', '2026-04-17 22:10:13'),
529	(5, '14-200--1-MF1-200008', '51019', 0, 43, 'Vehicle breakdown', '2026-04-18 13:54:13'),
530	(6, '14-200--1-MF1-200008', '3921', 0, 34, 'Vehicle breakdown', '2026-04-22 11:00:13'),
531	(7, '14-200--1-MF1-200013', 'vic:rail:MAC', 2, 33, 'Operational delay', '2026-04-21 11:50:13'),
532	(8, '14-200--1-MF1-200014', '50088', 0, 15, 'Traffic congestion', '2026-04-19 18:08:13'),
533	(9, '14-200--1-MF1-200014', '5163', 0, 24, 'Road construction', '2026-04-22 21:22:13'),
534	(10, '14-200--1-MF1-200016', 'vic:rail:ELT', 2, 24, 'Road construction', '2026-04-22 13:35:13');
535
536	INSERT INTO DELAY_LOG (TRIP_ID, STOP_ID, STOP_VT, DELAY_MINUTES, DELAY_REASON, REPORTED_TIME)
537	SELECT
538	t.trip_id,
539	(1002 + (gs % 25999))::varchar,
540	(gs % 3),
541	(1 + (random() * 44)::int),
542	(ARRAY['Vehicle breakdown','Weather conditions','Signal problem','Traffic congestion',
543	'Road construction','Operational delay','Passenger incident','Track obstruction'])[1 + (gs % 8)],
544	CURRENT_TIMESTAMP - (random() * 90 * INTERVAL '1 day')
545	FROM generate_series(11, 270000) gs
546	JOIN LATERAL (SELECT trip_id FROM TRIP OFFSET (gs % 380000) LIMIT 1) t ON TRUE;
547
548
549	-- 21. CAPACITY_LOG
550	INSERT INTO CAPACITY_LOG (CAPACITY_LOG_ID, TRIP_ID, VEHICLE_ID, STOP_ID, STOP_VT, PASSENGER_COUNT, RECORDED_TIME) VALUES
551	(1, '14-200--1-MF1-200003', 'V1413', '12137', 0, 53, '2026-04-21 20:40:29'),
552	(2, '14-200--1-MF1-200003', 'V1413', '3229', 0, 50, '2026-04-17 22:36:29'),
553	(3, '14-200--1-MF1-200003', 'V1413', 'vic:rail:TOT', 2, 36, '2026-04-21 12:21:29'),
554	(4, '14-200--1-MF1-200003', 'V1413', '9130', 0, 98, '2026-04-18 00:18:29'),
555	(5, '14-200--1-MF1-200003', 'V1413', '1291', 1, 6, '2026-04-18 23:39:29'),
556	(6, '14-200--1-MF1-200003', 'V1413', '673', 0, 106, '2026-04-17 08:53:29'),
557	(7, '14-200--1-MF1-200005', 'V1984', '21648', 0, 31, '2026-04-22 02:27:29'),
558	(8, '14-200--1-MF1-200005', 'V1984', '19059', 1, 40, '2026-04-21 22:15:29'),
559	(9, '14-200--1-MF1-200005', 'V1984', '51716', 0, 35, '2026-04-18 16:43:29'),
560	(10, '14-200--1-MF1-200006', 'V1785', '12489', 0, 22, '2026-04-19 12:44:29');
561
562	INSERT INTO CAPACITY_LOG (TRIP_ID, VEHICLE_ID, STOP_ID, STOP_VT, PASSENGER_COUNT, RECORDED_TIME)
563	SELECT
564	t.trip_id,
565	'V' \|\| (1000 + (gs % 3500)),
566	(1002 + (gs % 25999))::varchar,
567	(gs % 3),
568	(random() * 320)::int,
569	CURRENT_TIMESTAMP - (random() * 180 * INTERVAL '1 day')
570	FROM generate_series(11, 1500000) gs
571	JOIN LATERAL (SELECT trip_id FROM TRIP OFFSET (gs % 380000) LIMIT 1) t ON TRUE;
572
573
574
575	-- 22. VEHICLE_LOG
576	INSERT INTO VEHICLE_LOG (VEHICLE_LOG_ID, VEHICLE_ID, TRIP_ID, LATITUDE, LONGITUDE, SPEED_KMH, HEADING,
577	RECORDED_AT, OCCUPANCY_STATUS, CONGESTION_LEVEL,
578	CURRENT_STOP_ID, CURRENT_STOP_VT, NEXT_STOP_ID, NEXT_STOP_VT, DISTANCE_TO_NEXT_STOP_M) VALUES
579	(1, 'V1413', '14-200--1-MF1-200003', -37.849325, 145.3582946, 67.97, 237.88, '2026-04-20 06:01:56', 'CRUSHED_STANDING_ONLY', 'RUNNING_SMOOTHLY', '9803', 0, '18472', 0, 1098.46),
580	(2, 'V1413', '14-200--1-MF1-200003', -37.8497145, 145.3617465, 45.07, 269.83, '2026-04-20 06:02:09', 'NOT_ACCEPTING_PASSENGERS', 'RUNNING_SMOOTHLY', '9803', 0, '18472', 0, 1577.74),
581	(3, 'V1413', '14-200--1-MF1-200003', -37.8429521, 145.3598648, 27.3, 139.95, '2026-04-20 06:02:50', 'STANDING_ROOM_ONLY', 'STOP_AND_GO', '9803', 0, '18472', 0, 958.63),
582	(4, 'V1413', '14-200--1-MF1-200003', -37.8448664, 145.3563943, 33.11, 44.19, '2026-04-20 06:03:20', 'EMPTY', 'RUNNING_SMOOTHLY', '9803', 0, '18472', 0, 1794.2),
583	(5, 'V1413', '14-200--1-MF1-200003', -37.8490119, 145.3649641, 9.68, 6.87, '2026-04-20 06:03:28', 'NOT_ACCEPTING_PASSENGERS', 'STOP_AND_GO', '9803', 0, '18472', 0, 835.65),
584	(6, 'V1413', '14-200--1-MF1-200003', -37.8413512, 145.3645476, 21.92, 99.0, '2026-04-20 06:02:36', 'MANY_SEATS_AVAILABLE', 'CONGESTION', NULL, NULL, '18472', 0, 14.0),
585	(7, 'V1413', '14-200--1-MF1-200003', -37.8456942, 145.3559039, 53.77, 126.75, '2026-04-20 06:02:50', 'MANY_SEATS_AVAILABLE', 'RUNNING_SMOOTHLY', '9803', 0, NULL, NULL, NULL),
586	(8, 'V1413', '14-200--1-MF1-200003', -37.8487146, 145.3650877, 10.55, 308.23, '2026-04-20 06:03:27', 'FEW_SEATS_AVAILABLE', 'RUNNING_SMOOTHLY', '9803', 0, '18472', 0, 79.05),
587	(9, 'V1413', '14-200--1-MF1-200003', -37.8412829, 145.3626217, 55.17, 138.42, '2026-04-20 06:04:12', 'FULL', 'RUNNING_SMOOTHLY', '9803', 0, '18472', 0, 1693.73),
588	(10, 'V1413', '14-200--1-MF1-200003', -37.8482182, 145.3622887, 55.59, 45.04, '2026-04-20 06:02:50', 'MANY_SEATS_AVAILABLE', 'STOP_AND_GO', '9803', 0, '18472', 0, 218.97);
589
590	INSERT INTO VEHICLE_LOG (VEHICLE_ID, TRIP_ID, LATITUDE, LONGITUDE, SPEED_KMH, HEADING, RECORDED_AT,
591	OCCUPANCY_STATUS, CONGESTION_LEVEL,
592	CURRENT_STOP_ID, CURRENT_STOP_VT, NEXT_STOP_ID, NEXT_STOP_VT, DISTANCE_TO_NEXT_STOP_M)
593	SELECT
594	'V' \|\| (1000 + (gs % 3500)),
595	t.trip_id,
596	(-37.5 - random() * 0.6)::decimal(10,7),
597	(144.5 + random() * 0.9)::decimal(10,7),
598	(5 + random() * 95)::decimal(6,2),
599	(random() * 360)::decimal(5,2),
600	CURRENT_TIMESTAMP - (random() * 180 * INTERVAL '1 day'),
601	(ARRAY['EMPTY','MANY_SEATS_AVAILABLE','FEW_SEATS_AVAILABLE','STANDING_ROOM_ONLY',
602	'CRUSHED_STANDING_ONLY','FULL','NOT_ACCEPTING_PASSENGERS'])[1 + (gs % 7)],
603	(ARRAY['RUNNING_SMOOTHLY','RUNNING_SMOOTHLY','STOP_AND_GO','CONGESTION','SEVERE_CONGESTION'])[1 + (gs % 5)],
604	CASE WHEN gs % 8 = 0 THEN NULL ELSE (1002 + (gs % 25999))::varchar END,
605	CASE WHEN gs % 8 = 0 THEN NULL ELSE (gs % 3) END,
606	CASE WHEN gs % 6 = 0 THEN NULL ELSE (1002 + ((gs+1) % 25999))::varchar END,
607	CASE WHEN gs % 6 = 0 THEN NULL ELSE (gs % 3) END,
608	CASE WHEN gs % 6 = 0 THEN NULL ELSE (10 + random() * 1990)::decimal(8,2) END
609	FROM generate_series(11, 5400000) gs
610	JOIN LATERAL (SELECT trip_id FROM TRIP OFFSET (gs % 380000) LIMIT 1) t ON TRUE;
611
612
613
614	-- 23. FUEL_LOG
615	INSERT INTO FUEL_LOG (FUEL_LOG_ID, VEHICLE_ID, FUEL_AMOUNT, FUEL_COST, FUEL_DATE, MILEAGE) VALUES
616	(1, 'V0', 76.81, 168.11, '2015-06-02', 27985.45),
617	(2, 'V0', 160.65, 245.25, '2023-12-30', 30270.86),
618	(3, 'V0', 283.42, 582.85, '2018-04-17', 33125.57),
619	(4, 'V1', 148.32, 316.17, '2016-05-18', 4859.25),
620	(5, 'V1', 90.2, 147.27, '2017-03-12', 7524.74),
621	(6, 'V1', 118.51, 253.81, '2023-06-19', 8174.1),
622	(7, 'V1', 38.36, 58.89, '2017-05-11', 12533.86),
623	(8, 'V1', 202.57, 471.7, '2020-12-14', 14747.98),
624	(9, 'V1', 144.21, 265.17, '2017-01-19', 15145.34),
625	(10, 'V1', 75.56, 184.46, '2015-04-02', 18418.25);
626
627	INSERT INTO FUEL_LOG (VEHICLE_ID, FUEL_AMOUNT, FUEL_COST, FUEL_DATE, MILEAGE)
628	SELECT
629	'V' \|\| (gs % 5000),
630	(30 + random() * 300)::decimal(8,2),
631	(50 + random() * 600)::decimal(10,2),
632	CURRENT_DATE - (random() * 3650)::int,
633	(1000 + random() * 299000)::decimal(10,2)
634	FROM generate_series(11, 22000) gs;
635
636
637
638	-- 24. DRIVER_SCHEDULE
639	INSERT INTO DRIVER_SCHEDULE (DRIVER_SCHEDULE_ID, DRIVER_ID, TRIP_ID, SHIFT_ID, SHIFT_START, SHIFT_END, SCHEDULE_DATE) VALUES
640	(1, 'D8556', '14-200--1-MF1-200003', 3, '2025-11-03 22:00:00', '2025-11-04 03:00:00', '2025-11-03'),
641	(2, 'D7108', '14-200--1-MF1-200005', 1, '2025-11-15 06:00:00', '2025-11-15 14:00:00', '2025-11-15'),
642	(3, 'D9367', '14-200--1-MF1-200006', 1, '2025-12-10 06:00:00', NULL, '2025-12-10'),
643	(4, 'D4139', '14-200--1-MF1-200008', 3, '2025-12-14 22:00:00', '2025-12-15 00:00:00', '2025-12-14'),
644	(5, 'D10469', '14-200--1-MF1-200010', 3, '2026-02-19 22:00:00', '2026-02-19 23:00:00', '2026-02-19'),
645	(6, 'D1584', '14-200--1-MF1-200012', 3, '2026-01-20 22:00:00', '2026-01-21 05:00:00', '2026-01-20'),
646	(7, 'D9934', '14-200--1-MF1-200013', 2, '2025-12-26 14:00:00', '2025-12-26 22:00:00', '2025-12-26'),
647	(8, 'D3477', '14-200--1-MF1-200014', 2, '2025-12-02 14:00:00', NULL, '2025-12-02'),
648	(9, 'D8719', '14-200--1-MF1-200016', 1, '2026-04-22 06:00:00', '2026-04-22 14:00:00', '2026-04-22'),
649	(10, 'D5282', '14-200--1-MF1-200017', 1, '2025-12-27 06:00:00', '2025-12-27 14:00:00', '2025-12-27');
650
651	INSERT INTO DRIVER (DRIVER_ID, EMPLOYEE_ID, LICENSE_NUMBER, LICENSE_CATEGORY, LICENSE_EXPIRY_DATE, YEARS_EXPERIENCE)
652	SELECT 'D' \|\| gs, 'E' \|\| LEAST(gs*2, 24999), 'YY' \|\| lpad(gs::text,5,'0'), 'D', '2032-01-01', 10
653	FROM (VALUES (8556),(7108),(9367),(4139),(10469),(1584),(9934),(3477),(8719),(5282),(2267),(1572)) v(gs)
654	ON CONFLICT (DRIVER_ID) DO NOTHING;
655
656	INSERT INTO DRIVER_SCHEDULE (DRIVER_ID, TRIP_ID, SHIFT_ID, SHIFT_START, SHIFT_END, SCHEDULE_DATE)
657	SELECT
658	'D' \|\| (1000 + (gs % 3500)),
659	t.trip_id,
660	(1 + (gs % 3)),
661	ts,
662	CASE WHEN gs % 15 = 0 THEN NULL ELSE ts + INTERVAL '8 hours' END,
663	ts::date
664	FROM generate_series(11, 387000) gs
665	JOIN LATERAL (SELECT trip_id FROM TRIP OFFSET (gs % 380000) LIMIT 1) t ON TRUE
666	CROSS JOIN LATERAL (SELECT (CURRENT_TIMESTAMP - (random() * 365 * INTERVAL '1 day'))::timestamp AS ts) dt;
667
668
669
670	-- 25. CONDUCTOR_SCHEDULE
671	INSERT INTO CONDUCTOR_SCHEDULE (CONDUCTOR_SCHEDULE_ID, CONDUCTOR_ID, TRIP_ID, SHIFT_ID, SHIFT_START, SHIFT_END, SCHEDULE_DATE) VALUES
672	(1, 'CON2107', '14-200--1-MF1-200003', 3, '2026-04-19 22:00:00', '2026-04-20 03:00:00', '2026-04-19'),
673	(2, 'CON990', '14-200--1-MF1-200005', 2, '2026-03-31 14:00:00', '2026-03-31 22:00:00', '2026-03-31'),
674	(3, 'CON2298', '14-200--1-MF1-200006', 3, NULL, '2025-12-12 06:00:00', '2025-12-12'),
675	(4, 'CON7608', '14-200--1-MF1-200008', 2, '2025-12-31 14:00:00', '2025-12-31 22:00:00', '2025-12-31'),
676	(5, 'CON3851', '14-200--1-MF1-200012', 2, '2026-02-19 14:00:00', '2026-02-19 22:00:00', '2026-02-19'),
677	(6, 'CON7366', '14-200--1-MF1-200014', 3, '2026-01-01 22:00:00', '2026-01-01 23:00:00', '2026-01-01'),
678	(7, 'CON4353', '14-200--1-MF1-200016', 3, '2026-01-17 22:00:00', NULL, '2026-01-17'),
679	(8, 'CON1526', '14-200--1-MF1-200018', 1, '2026-01-31 06:00:00', '2026-01-31 14:00:00', '2026-01-31'),
680	(9, 'CON7894', '14-200--1-MF1-200021', 3, '2025-12-19 22:00:00', '2025-12-20 01:00:00', '2025-12-19'),
681	(10, 'CON7693', '14-200--1-MF1-200024', 1, '2025-12-21 06:00:00', '2025-12-21 14:00:00', '2025-12-21');
682
683	INSERT INTO CONDUCTOR (CONDUCTOR_ID, EMPLOYEE_ID, CERTIFICATION_NUMBER, HIRE_ROLE_DATE)
684	SELECT 'CON' \|\| gs, 'E' \|\| LEAST(gs, 24999), 'CERTX' \|\| gs, '2020-01-01'
685	FROM (VALUES (2107),(990),(2298),(7608),(3851),(7366),(4353),(1526),(7894),(7693)) v(gs)
686	ON CONFLICT (CONDUCTOR_ID) DO NOTHING;
687
688	INSERT INTO CONDUCTOR_SCHEDULE (CONDUCTOR_ID, TRIP_ID, SHIFT_ID, SHIFT_START, SHIFT_END, SCHEDULE_DATE)
689	SELECT
690	'CON' \|\| (gs % 10000),
691	t.trip_id,
692	(1 + (gs % 3)),
693	ts,
694	CASE WHEN gs % 15 = 0 THEN NULL ELSE ts + INTERVAL '8 hours' END,
695	ts::date
696	FROM generate_series(11, 270000) gs
697	JOIN LATERAL (SELECT trip_id FROM TRIP OFFSET (gs % 380000) LIMIT 1) t ON TRUE
698	CROSS JOIN LATERAL (SELECT (CURRENT_TIMESTAMP - (random() * 365 * INTERVAL '1 day'))::timestamp AS ts) dt;
699
700
701
702	-- 26. PASSENGER
703	INSERT INTO PASSENGER (PASSENGER_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE, DATE_OF_BIRTH, REGISTRATION_DATE, PASSENGER_TYPE, PASSWORD_HASH) VALUES
704	('PAS0', 'Scott', 'Chan', 'scottchan756@outlook.com', '076776183', '1978-10-13', '2018-03-22', 'REGULAR', '04fbe0e221f561746d7fc3990b172f7b385f544465cc69e9af7ed6bd698b5014'),
705	('PAS1', 'Jose', 'Gutierrez', 'josegutierrez697@gmail.com', '076658783', '1962-06-27', '2022-08-11', 'REGULAR', '43051ecceb7240657b6af82e6a0402edccccf043208e9a22a948255426fd8899'),
706	('PAS2', 'Alyssa', 'Salazar', 'alyssasalazar705@yahoo.com', '077003380', '1981-06-11', '2018-02-13', 'REGULAR', 'b6efc4ae3bb8f40466c0e548e9f2195b41aa101991c11b53f83579a39a7b8249'),
707	('PAS3', 'Zachary', 'Gonzales', 'zacharygonzales125@outlook.com','077534837', '2003-06-09', '2026-04-14', 'STUDENT', '7bea27fe14d87a029f16c74c1ef006de9ee68ed6c846acd2806ce67bf3bc00cf'),
708	('PAS4', 'Susan', 'Holder', 'susanholder332@gmail.com', '076698987', '1983-06-16', '2024-08-03', 'REGULAR', 'b21d7ef36c12d2f259551fe6c484838956e779c35746e4618d3f130e4ad0a4c4'),
709	('PAS5', 'Steven', 'James', 'stevenjames864@yahoo.com', '075943433', '2000-12-22', '2020-01-06', 'STUDENT', '9e887e2f938fdac728c88987b54ee0edb407ecf56b3808263680965560308ab3'),
710	('PAS6', 'Amy', 'Davis', 'amydavis256@gmail.com', '076953378', '1959-10-19', '2024-03-23', 'SENIOR', '58eeded7128960a16590fe03740b09b642cd982fd14318194985f8096ba7f368'),
711	('PAS7', 'Lisa', 'Johnson', 'lisajohnson585@gmail.com', '076417002', '1961-06-22', '2017-11-20', 'REGULAR', '403f0cdab3c18a724ebf0239f4ce81fbeffea98d149b80d88c30850feae8f48b'),
712	('PAS8', 'Jacqueline', 'Ortiz', 'jacquelineortiz447@outlook.com','077795132', '1949-08-26', '2023-08-16', 'SENIOR', 'ab6067a5343fb50fba77e15dadcfc712e00bc21580d8bf546cdf680a6d891a85'),
713	('PAS9', 'Susan', 'Lester', 'susanlester289@yahoo.com', '075386749', '2008-09-01', '2022-10-29', 'STUDENT', '00bb0f291b59571d79f089fbc5b7563a210ef8dae95b61cbecf97a239c8ad937'),
714	('PAS10', 'Nicholas', 'Griffin', 'nicholasgriffin671@outlook.com','076487055', '1983-06-18', '2021-07-04', 'REGULAR', '8e90157da0c64569239707dfb38e4e4e435c56897ccc18c313d96a37686f2984');
715
716	INSERT INTO PASSENGER (PASSENGER_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE, DATE_OF_BIRTH,
717	REGISTRATION_DATE, PASSENGER_TYPE, PASSWORD_HASH)
718	SELECT
719	'PAS' \|\| gs,
720	(ARRAY['Scott','Jose','Alyssa','Zachary','Susan','Steven','Amy','Lisa','Jacqueline','Nicholas',
721	'Paul','Emily','Kim','Stephanie','Felicia','Jason','William','Jim','Timothy','Anthony',
722	'Derek','Sarah','Michael','Jessica','Robert','Jennifer','David','Mary','James','Patricia'])[1 + (gs % 30)],
723	(ARRAY['Chan','Gutierrez','Salazar','Gonzales','Holder','James','Davis','Johnson','Ortiz','Griffin',
724	'Martin','Snyder','Cabrera','Flynn','Monroe','Silva','Odonnell','Cook','Fitzgerald','Jones',
725	'Lynn','Smith','Williams','Brown','Taylor','Anderson','Thomas','Wilson','Moore','Harris'])[1 + (gs % 30)],
726	lower(
727	(ARRAY['scott','jose','alyssa','zachary','susan','steven','amy','lisa','jacqueline','nicholas',
728	'paul','emily','kim','stephanie','felicia','jason','william','jim','timothy','anthony',
729	'derek','sarah','michael','jessica','robert','jennifer','david','mary','james','patricia'])[1 + (gs % 30)]
730	\|\|
731	(ARRAY['chan','gutierrez','salazar','gonzales','holder','james','davis','johnson','ortiz','griffin',
732	'martin','snyder','cabrera','flynn','monroe','silva','odonnell','cook','fitzgerald','jones',
733	'lynn','smith','williams','brown','taylor','anderson','thomas','wilson','moore','harris'])[1 + (gs % 30)]
734	) \|\| (gs % 999) \|\| '@' \|\| (ARRAY['gmail.com','yahoo.com','outlook.com'])[1 + (gs % 3)],
735	'07' \|\| lpad(((6000000 + gs * 11) % 4000000 + 6000000)::text, 7, '0'),
736	DATE '1944-01-01' + (random() * 29200)::int,
737	DATE '2015-01-01' + (random() * 4140)::int,
738	(ARRAY['REGULAR','REGULAR','REGULAR','STUDENT','SENIOR','DISABLED','CHILD'])[1 + (gs % 7)],
739	md5('pass' \|\| gs)
740	FROM generate_series(11, 4999999) gs
741	ON CONFLICT (PASSENGER_ID) DO NOTHING;
742
743
744
745	-- 27. TICKET_TYPE
746	INSERT INTO TICKET_TYPE (TICKET_TYPE_ID, NAME, DESCRIPTION, VALIDITY_DURATION, BASE_PRICE) VALUES
747	(1, 'Single Ticket', 'One-way single trip ticket', 60, 4.6),
748	(2, 'Daily Pass', 'Unlimited travel for one day', 1440, 10.6),
749	(3, 'Weekly Pass', 'Unlimited travel for one week', 10080, 53.0),
750	(4, 'Monthly Pass', 'Unlimited travel for one month', 43200, 206.0),
751	(5, 'Annual Pass', 'Unlimited travel for one year', 525600, 2116.0),
752	(6, 'Student Monthly', 'Monthly pass for students', 43200, 103.0);
753
754
755
756	-- 28. FARE_RULE
757	INSERT INTO FARE_RULE (FARE_RULE_ID, ZONE_ID, TICKET_TYPE_ID, AGENCY_ID, ROUTE_ID, PRICE, VALID_FROM, VALID_TO) VALUES
758	(1, 'Z01', 1, 1, NULL, 2.5, '2025-08-17', NULL),
759	(2, 'Z01', 1, 2, NULL, 0.5, '2026-01-13', '2026-04-21'),
760	(3, 'Z01', 1, 2, '65-L05-aus-1', 8.5, '2025-11-06', '2026-01-11'),
761	(4, 'Z01', 1, 2, '19-682-aus-1', 8.0, '2024-12-26', '2025-10-09'),
762	(5, 'Z01', 1, 3, '43-542-aus-1', 7.0, '2025-06-09', '2026-04-21'),
763	(6, 'Z01', 1, 3, NULL, 5.5, '2024-11-27', '2025-05-18'),
764	(7, 'Z01', 1, 3, NULL, 9.5, '2024-02-16', NULL),
765	(8, 'Z01', 1, 4, NULL, 0.0, '2025-12-15', '2026-04-21'),
766	(9, 'Z01', 1, 4, '26-850-aus-1', 13.0, '2025-05-04', '2025-12-09'),
767	(10, 'Z01', 1, 5, NULL, 8.5, '2026-02-20', '2026-04-21');
768
769	INSERT INTO FARE_RULE (ZONE_ID, TICKET_TYPE_ID, AGENCY_ID, ROUTE_ID, PRICE, VALID_FROM, VALID_TO)
770	SELECT
771	'Z' \|\| lpad((1 + (gs % 20))::text, 2, '0'),
772	(1 + (gs % 6)),
773	(1 + (gs % 5)),
774	CASE WHEN gs % 4 = 0 THEN NULL ELSE r.route_id END,
775	(0.5 + random() * 13.5)::decimal(8,2),
776	CURRENT_DATE - (random() * 730)::int,
777	CASE WHEN gs % 10 = 0 THEN NULL ELSE CURRENT_DATE + (random() * 730)::int END
778	FROM generate_series(11, 1200) gs
779	JOIN LATERAL (SELECT route_id FROM ROUTE ORDER BY random() LIMIT 1) r ON TRUE;
780
781
782
783	-- 29. TICKET
784	INSERT INTO TICKET (TICKET_ID, PASSENGER_ID, TRIP_ID, TICKET_TYPE_ID, FARE_RULE_ID, DISCOUNT_ID,
785	START_STOP_ID, START_STOP_VT, END_STOP_ID, END_STOP_VT,
786	PURCHASE_DATE, PRICE, STATUS) VALUES
787	('TKT0000001', 'PAS4698017', '20-767--1-MF1-87', 2, 1048, NULL, NULL, NULL, '51560', 0, '2025-10-09', 12.0, 'USED'),
788	('TKT0000002', 'PAS2500611', '03-5--5-T3-140606642', 1, 1091, NULL, NULL, NULL, '13645', 0, '2026-01-03', 8.5, 'EXPIRED'),
789	('TKT0000003', 'PAS3658516', '40-683--1-MF3-87894110', 4, 899, NULL, 'vic:rail:UNN_LI3', 2, '11296', 0, '2025-11-10', 0, 'REFUNDED'),
790	('TKT0000004', 'PAS3796241', '32-604--1-Sat6-604452', 2, 1107, NULL, '40231', 0, '22539', 0, '2026-04-15', 6.0, 'USED'),
791	('TKT0000005', 'PAS3553431', '53-420--1-Sun10-70503119', 3, 279, NULL, '13585', 0, NULL, NULL,'2026-02-19', 0, 'REFUNDED'),
792	('TKT0000006', 'PAS4024121', '60-194--1-MF10-4419432', 2, 517, NULL, '22920', 0, '8668', 0, '2026-03-06', 14.0, 'USED'),
793	('TKT0000007', 'PAS2986472', '02-BEG--37-T5-3120', 3, 771, NULL, '1151', 0, '43420', 0, '2025-11-21', 4.5, 'USED'),
794	('TKT0000008', 'PAS3194202', '58-G01--1-MF3-35049314', 2, 1099, 6, '13542', 0, '28481', 0, '2025-11-23', 10.8, 'VALID'),
795	('TKT0000009', 'PAS1589736', '60-494--1-Sat4-8070560', 1, 923, NULL, '7979', 0, '15430', 0, '2025-06-03', 4.5, 'USED'),
796	('TKT0000010', 'PAS4231578', '42-357--1-MF4-115', 2, 197, NULL, '3164', 0, '46339', 0, '2026-03-15', 10.5, 'VALID');
797
798	INSERT INTO TICKET (TICKET_ID, PASSENGER_ID, TRIP_ID, TICKET_TYPE_ID, FARE_RULE_ID, DISCOUNT_ID,
799	START_STOP_ID, START_STOP_VT, END_STOP_ID, END_STOP_VT,
800	PURCHASE_DATE, PRICE, STATUS)
801	SELECT
802	'TKT' \|\| lpad(gs::text, 7, '0'),
803	'PAS' \|\| (gs % 5000000),
804	t.trip_id,
805	(1 + (gs % 6)),
806	CASE WHEN gs % 12 = 0 THEN NULL ELSE (1 + (gs % 1200)) END,
807	CASE WHEN gs % 8 = 0 THEN NULL ELSE (1 + (gs % 10)) END,
808	CASE WHEN gs % 5 = 0 THEN NULL ELSE (1002 + (gs % 25999))::varchar END,
809	CASE WHEN gs % 5 = 0 THEN NULL ELSE (gs % 3) END,
810	CASE WHEN gs % 4 = 0 THEN NULL ELSE (1002 + ((gs+1) % 25999))::varchar END,
811	CASE WHEN gs % 4 = 0 THEN NULL ELSE (gs % 3) END,
812	DATE '2024-01-01' + (random() * 480)::int,
813	(0.0 + random() * 15.0)::decimal(8,2),
814	(ARRAY['USED','USED','USED','VALID','EXPIRED','CANCELLED','REFUNDED'])[1 + (gs % 7)]
815	FROM generate_series(11, 10000000) gs
816	JOIN LATERAL (SELECT trip_id FROM TRIP OFFSET (gs % 380000) LIMIT 1) t ON TRUE
817	ON CONFLICT (TICKET_ID) DO NOTHING;
818
819
820
821	-- 30. PAYMENT
822	INSERT INTO PAYMENT (PAYMENT_ID, TICKET_ID, PASSENGER_ID, AMOUNT, PAYMENT_METHOD, PAYMENT_DATE, PAYMENT_STATUS, TRANSACTION_REFERENCE) VALUES
823	('PAY0000001', 'TKT0000003', 'PAS803371', 3.5, 'CASH', '2025-08-31 12:21:05.445861', 'COMPLETED', NULL),
824	('PAY0000002', 'TKT0000004', 'PAS3506098', 7.0, 'ONLINE', '2026-03-05 04:00:05.446358', 'COMPLETED', 'TXN176855850'),
825	('PAY0000003', 'TKT0000005', 'PAS3672677', 5.47, 'MOBILE', '2026-02-10 20:20:05.446389', 'COMPLETED', 'TXN454882949'),
826	('PAY0000004', 'TKT0000007', 'PAS1170886', 3.08, 'CARD', '2026-03-22 08:28:05.446409', 'COMPLETED', 'TXN787784851'),
827	('PAY0000005', 'TKT0000008', 'PAS982494', 0.7, 'MOBILE', '2025-10-05 09:45:05.446423', 'COMPLETED', 'TXN127707823'),
828	('PAY0000006', 'TKT0000009', 'PAS3862170', 0.6, 'CASH', '2025-06-03 21:58:05.446436', 'REFUNDED', NULL),
829	('PAY0000007', 'TKT0000010', 'PAS2305744', 0.5, 'CONTACTLESS', '2025-06-02 07:28:05.446448', 'CANCELLED', 'TXN687895258'),
830	('PAY0000008', 'TKT0000011', 'PAS13699', 4.0, 'CASH', '2025-06-13 22:55:05.446458', 'COMPLETED', NULL),
831	('PAY0000009', 'TKT0000012', 'PAS1356000', 5.75, 'CONTACTLESS', '2025-06-02 12:32:05.446467', 'COMPLETED', 'TXN463888139'),
832	('PAY0000010', 'TKT0000013', 'PAS2908092', 4.55, 'CASH', '2026-01-20 10:26:05.446477', 'CANCELLED', NULL);
833
834	INSERT INTO PAYMENT (PAYMENT_ID, TICKET_ID, PASSENGER_ID, AMOUNT, PAYMENT_METHOD, PAYMENT_DATE, PAYMENT_STATUS, TRANSACTION_REFERENCE)
835	SELECT
836	'PAY' \|\| lpad(gs::text, 7, '0'),
837	'TKT' \|\| lpad(gs::text, 7, '0'),
838	'PAS' \|\| (gs % 5000000),
839	(0.5 + random() * 14.5)::decimal(10,2),
840	(ARRAY['CASH','CARD','CONTACTLESS','MOBILE','ONLINE','VOUCHER'])[1 + (gs % 6)],
841	CURRENT_TIMESTAMP - (random() * 480 * INTERVAL '1 day'),
842	(ARRAY['COMPLETED','COMPLETED','COMPLETED','COMPLETED','FAILED','REFUNDED','CANCELLED'])[1 + (gs % 7)],
843	CASE
844	WHEN (gs % 6) = 0 THEN NULL
845	WHEN (gs % 7) IN (5,6) THEN NULL
846	ELSE 'TXN' \|\| lpad((100000000 + gs * 97 % 900000000)::text, 9, '0')
847	END
848	FROM generate_series(11, 9000000) gs
849	ON CONFLICT (PAYMENT_ID) DO NOTHING;
850
851
852
853	-- 31. SUBSCRIPTION_PASS
854	INSERT INTO SUBSCRIPTION_PASS (PASS_ID, PASSENGER_ID, TICKET_TYPE_ID, ZONE_ID, PASS_TYPE, START_DATE, END_DATE, PRICE, STATUS) VALUES
855	('PASS1', 'PAS4789651', 1, 'Z01', 'STUDENT', '2023-12-26', '2024-01-25', 585.8, 'ACTIVE'),
856	('PASS2', 'PAS1860306', 2, NULL, 'DAILY', '2020-05-31', '2020-06-01', 795.24, 'EXPIRED'),
857	('PASS3', 'PAS727459', 2, 'Z07', 'WEEKLY', '2021-11-23', '2021-11-30', 1126.1, 'EXPIRED'),
858	('PASS4', 'PAS3632155', 2, 'Z15', 'WEEKLY', '2021-02-07', '2021-02-14', 229.39, 'ACTIVE'),
859	('PASS5', 'PAS4514184', 5, 'Z14', 'STUDENT', '2020-04-09', '2020-05-09', 1095.07, 'ACTIVE'),
860	('PASS6', 'PAS3317589', 5, NULL, 'DAILY', '2019-07-15', '2019-07-16', 867.41, 'ACTIVE'),
861	('PASS7', 'PAS4613663', 4, 'Z15', 'ANNUAL', '2020-12-21', '2021-12-21', 605.45, 'ACTIVE'),
862	('PASS8', 'PAS717158', 3, NULL, 'STUDENT', '2024-09-19', '2024-10-19', 868.83, 'EXPIRED'),
863	('PASS9', 'PAS3878203', 2, 'Z20', 'STUDENT', '2021-09-13', '2021-10-13', 457.93, 'ACTIVE'),
864	('PASS10', 'PAS1157157', 5, 'Z13', 'MONTHLY', '2019-02-08', '2019-03-10', 116.55, 'ACTIVE');
865
866	INSERT INTO SUBSCRIPTION_PASS (PASS_ID, PASSENGER_ID, TICKET_TYPE_ID, ZONE_ID, PASS_TYPE, START_DATE, END_DATE, PRICE, STATUS)
867	SELECT
868	'PASS' \|\| gs,
869	'PAS' \|\| (gs % 5000000),
870	(1 + (gs % 6)),
871	CASE WHEN gs % 5 = 0 THEN NULL ELSE 'Z' \|\| lpad((1 + (gs % 20))::text, 2, '0') END,
872	(ARRAY['DAILY','WEEKLY','MONTHLY','MONTHLY','MONTHLY','ANNUAL','STUDENT','SENIOR'])[1 + (gs % 8)],
873	DATE '2019-01-01' + (random() * 2000)::int,
874	DATE '2019-01-01' + (random() * 2000)::int + (random() * 365)::int,
875	(100 + random() * 2100)::decimal(8,2),
876	(ARRAY['ACTIVE','ACTIVE','ACTIVE','EXPIRED','SUSPENDED','CANCELLED'])[1 + (gs % 6)]
877	FROM generate_series(11, 300000) gs
878	ON CONFLICT (PASS_ID) DO NOTHING;
879
880
881
882	-- 32. NOTIFICATION
883	INSERT INTO NOTIFICATION (NOTIFICATION_ID, PASSENGER_ID, TRIP_ID, DELAY_LOG_ID, MESSAGE, NOTIFICATION_TYPE, SENT_TIME, STATUS) VALUES
884	(1, 'PAS4414736', '60-160--1-MF1-415210', NULL, 'Reminder: Your subscription pass expires soon.', 'REMINDER', '2025-09-04 13:01:32.639938', 'SENT'),
885	(2, 'PAS2065493', '58-G22--1-MF6-35067539', NULL, 'Special offer! Get 20% off your next monthly pass.', 'PROMO', '2026-02-16 20:07:32.640021', 'SENT'),
886	(3, 'PAS1073120', '21-688--1-MF9-55', NULL, 'Service alert: Route disruption reported on your usual line.', 'ALERT', '2025-09-11 17:03:32.640040', 'FAILED'),
887	(4, 'PAS1811411', '17-200--1-Sat11-200453', NULL, 'Special offer! Get 20% off your next monthly pass.', 'PROMO', '2025-12-16 15:47:32.640053', 'READ'),
888	(5, 'PAS3400523', NULL, NULL, 'Special offer! Get 20% off your next monthly pass.', 'PROMO', '2025-06-30 11:12:32.640064', 'PENDING'),
889	(6, 'PAS232767', '41-800--1-MF2-145', NULL, 'Important update regarding your transit service.', 'GENERAL', '2025-05-13 01:42:32.640078', 'READ'),
890	(7, 'PAS830139', NULL, NULL, 'Service alert: Route disruption reported on your usual line.', 'ALERT', '2025-10-30 23:37:32.640088', 'READ'),
891	(8, 'PAS2551180', NULL, NULL, 'Special offer! Get 20% off your next monthly pass.', 'PROMO', '2025-08-19 06:54:32.640099', 'READ'),
892	(9, 'PAS1988728', '53-241--1-MF10-10201816', 129779, 'Your trip is experiencing delays. We apologize for the inconvenience.', 'DELAY', '2026-01-04 23:27:32.640112', 'READ'),
893	(10, 'PAS700959', NULL, NULL, 'Important update regarding your transit service.', 'GENERAL', '2025-10-28 09:47:32.640123', 'SENT');
894
895	INSERT INTO NOTIFICATION (PASSENGER_ID, TRIP_ID, DELAY_LOG_ID, MESSAGE, NOTIFICATION_TYPE, SENT_TIME, STATUS)
896	SELECT
897	'PAS' \|\| (gs % 5000000),
898	CASE WHEN gs % 4 = 0 THEN NULL ELSE t.trip_id END,
899	CASE WHEN gs % 7 = 0 THEN (1 + (gs % 270000)) ELSE NULL END,
900	(ARRAY[
901	'Reminder: Your subscription pass expires soon.',
902	'Special offer! Get 20% off your next monthly pass.',
903	'Service alert: Route disruption reported on your usual line.',
904	'Your trip is experiencing delays. We apologize for the inconvenience.',
905	'Important update regarding your transit service.',
906	'Your stop is approaching. Please prepare to alight.'
907	])[1 + (gs % 6)],
908	(ARRAY['REMINDER','PROMO','ALERT','DELAY','GENERAL','GENERAL'])[1 + (gs % 6)],
909	CURRENT_TIMESTAMP - (random() * 365 * INTERVAL '1 day'),
910	(ARRAY['SENT','SENT','SENT','READ','READ','FAILED','PENDING'])[1 + (gs % 7)]
911	FROM generate_series(11, 10000000) gs
912	JOIN LATERAL (SELECT trip_id FROM TRIP OFFSET (gs % 380000) LIMIT 1) t ON TRUE;
913
914
915
916	-- 33. PASSENGER_DISCOUNT
917	INSERT INTO PASSENGER_DISCOUNT (PASSENGER_DISCOUNT_ID, PASSENGER_ID, DISCOUNT_ID, OFFICER_ID, ASSIGNED_DATE, EXPIRY_DATE, STATUS) VALUES
918	(1, 'PAS1', 9, 'OFF951', '2021-03-27', '2022-11-20', 'EXPIRED'),
919	(2, 'PAS4', 10, 'OFF39', '2019-02-04', '2020-08-31', 'ACTIVE'),
920	(3, 'PAS5', 6, 'OFF1084', '2022-01-16', '2024-07-27', 'ACTIVE'),
921	(4, 'PAS9', 6, 'OFF1025', '2021-04-02', '2024-03-27', 'EXPIRED'),
922	(5, 'PAS10', 7, 'OFF2295', '2016-11-19', '2018-10-01', 'ACTIVE'),
923	(6, 'PAS14', 7, 'OFF1519', '2022-07-23', '2024-05-29', 'ACTIVE'),
924	(7, 'PAS16', 4, 'OFF2476', '2015-01-23', '2015-11-18', 'REVOKED'),
925	(8, 'PAS19', 10, 'OFF1857', '2020-07-15', '2020-12-13', 'ACTIVE'),
926	(9, 'PAS20', 8, 'OFF2316', '2015-09-02', '2017-08-09', 'EXPIRED'),
927	(10, 'PAS22', 8, 'OFF2018', '2015-08-20', '2015-10-01', 'REVOKED');
928
929	INSERT INTO DISCOUNT_OFFICER (OFFICER_ID, EMPLOYEE_ID, ASSIGNED_REGION, CERTIFICATION_CODE, AUTHORIZED_FROM, AUTHORIZED_TO)
930	SELECT 'OFF' \|\| gs, 'E' \|\| LEAST(gs * 4 + 2, 24999), 'General', 'OFF-CERT-' \|\| gs, '2015-01-01', '2030-01-01'
931	FROM (VALUES (951),(39),(1084),(1025),(2295),(1519),(2476),(1857),(2316),(2018)) v(gs)
932	ON CONFLICT (OFFICER_ID) DO NOTHING;
933
934	INSERT INTO PASSENGER_DISCOUNT (PASSENGER_ID, DISCOUNT_ID, OFFICER_ID, ASSIGNED_DATE, EXPIRY_DATE, STATUS)
935	SELECT
936	'PAS' \|\| gs,
937	(1 + (gs % 10)),
938	CASE WHEN gs % 8 = 0 THEN NULL ELSE 'OFF' \|\| (gs % 2500) END,
939	DATE '2015-01-01' + (random() * 3000)::int,
940	DATE '2015-01-01' + (random() * 3000)::int + (random() * 730)::int,
941	(ARRAY['ACTIVE','ACTIVE','ACTIVE','EXPIRED','REVOKED','PENDING'])[1 + (gs % 6)]
942	FROM generate_series(11, 3499999) gs;
943
944
945
946	-- 34. COMPLAINT
947	INSERT INTO COMPLAINT (COMPLAINT_ID, PASSENGER_ID, TRIP_ID, EMPLOYEE_ID, COMPLAINT_TEXT, COMPLAINT_DATE, STATUS, RESOLUTION_NOTES) VALUES
948	(1, 'PAS12260', '14-281--1-MF4-281110', 'E10894', 'Driver was rude and unprofessional.', '2025-11-30', 'CLOSED', 'Passenger has been contacted and issue resolved.'),
949	(2, 'PAS2872226', '02-CGB--1-US-5898', 'E5609', 'Driver was using phone while driving.', '2026-01-21', 'RESOLVED', 'Compensation issued to the passenger.'),
950	(3, 'PAS2763066', '03-12--6-UT-140603056', 'E7557', 'Driver skipped my stop without stopping.', '2026-03-10', 'RESOLVED', 'No action required - complaint deemed invalid.'),
951	(4, 'PAS2200199', '02-CBE--1-UG-C519', 'E9260', 'Driver skipped my stop without stopping.', '2025-09-18', 'REJECTED', 'Driver has been notified and warned.'),
952	(5, 'PAS2200199', '02-WIL--36-T5-6373', 'E5077', 'Driver was rude and unprofessional.', '2025-12-24', 'CLOSED', 'Maintenance team has been informed.'),
953	(6, 'PAS4443919', '42-570--1-Sun1-17', 'E17557', 'Driver was using phone while driving.', '2025-06-24', 'RESOLVED', 'Passenger has been contacted and issue resolved.'),
954	(7, 'PAS4443919', '03-78--12-T3-136470740', 'E20499', 'Trip was delayed by more than 30 minutes without notice.', '2026-02-24', 'IN_PROGRESS', NULL),
955	(8, 'PAS3239988', '42-555--1-MF1-163', NULL, 'Driver was using phone while driving.', '2025-12-09', 'CLOSED', 'No action required - complaint deemed invalid.'),
956	(9, 'PAS1397715', '14-270--1-MF4-270880', NULL, 'Driver skipped my stop without stopping.', '2026-03-14', 'REJECTED', 'Driver has been notified and warned.'),
957	(10, 'PAS4981352', '03-75--8-T5-140634933', 'E22591', 'Air conditioning was not working during the trip.', '2026-04-07', 'REJECTED', 'Issue has been forwarded to the relevant department.');
958
959	INSERT INTO COMPLAINT (PASSENGER_ID, TRIP_ID, EMPLOYEE_ID, COMPLAINT_TEXT, COMPLAINT_DATE, STATUS, RESOLUTION_NOTES)
960	SELECT
961	'PAS' \|\| (gs % 5000000),
962	t.trip_id,
963	CASE WHEN gs % 5 = 0 THEN NULL ELSE 'E' \|\| (gs % 25000) END,
964	(ARRAY[
965	'Driver was rude and unprofessional.',
966	'Driver was using phone while driving.',
967	'Driver skipped my stop without stopping.',
968	'Trip was delayed by more than 30 minutes without notice.',
969	'Air conditioning was not working during the trip.',
970	'Vehicle was overcrowded and uncomfortable.',
971	'Ticket machine was out of service.',
972	'Stop announcement system was not functioning.'
973	])[1 + (gs % 8)],
974	CURRENT_DATE - (random() * 365)::int,
975	(ARRAY['OPEN','OPEN','IN_PROGRESS','RESOLVED','CLOSED','REJECTED'])[1 + (gs % 6)],
976	CASE
977	WHEN gs % 6 IN (0,1) THEN NULL
978	WHEN gs % 3 = 0 THEN 'Passenger has been contacted and issue resolved.'
979	WHEN gs % 3 = 1 THEN 'Compensation issued to the passenger.'
980	ELSE 'No action required - complaint deemed invalid.'
981	END
982	FROM generate_series(11, 300000) gs
983	JOIN LATERAL (SELECT trip_id FROM TRIP OFFSET (gs % 380000) LIMIT 1) t ON TRUE;

Download in other formats:

Original Format