-- 1. AGENCY  
INSERT INTO AGENCY (AGENCY_ID, AGENCY_NAME, AGENCY_URL, AGENCY_TIMEZONE, AGENCY_LANG, AGENCY_PHONE, AGENCY_EMAIL, AGENCY_FARE_URL) VALUES
(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'),
(2, 'Metro Trains Melbourne',    'https://www.metrotrains.com.au',       'Australia/Melbourne', 'en', '077849265', 'info@metrotrains.com.au',       'https://www.metrotrains.com.au/fares'),
(3, 'Yarra Trams',               'https://www.yarratrams.com.au',        'Australia/Melbourne', 'en', '076785458', 'info@yarratrams.com.au',        'https://www.yarratrams.com.au/fares'),
(4, 'Melbourne Bus Link',        'https://www.melbournebuslink.com.au',  'Australia/Melbourne', 'en', '075159357', 'info@melbournebuslink.com.au',  'https://www.melbournebuslink.com.au/fares'),
(5, 'Transdev Melbourne',        'https://www.transdev.com.au',          'Australia/Melbourne', 'en', '077358851', 'info@transdev.com.au',          'https://www.transdev.com.au/fares');


-- 2. DISCOUNT  
INSERT INTO DISCOUNT (DISCOUNT_ID, DISCOUNT_NAME, DISCOUNT_PERCENTAGE, DESCRIPTION, ELIGIBILITY_CRITERIA, VALID_FROM, VALID_TO) VALUES
(1,  'Student Discount', 30.0,  'Discount for students',              'Valid student ID required',    '2020-01-01', '2030-12-31'),
(2,  'Senior Discount',  40.0,  'Discount for seniors 65+',           'Age 65 or above',              '2020-01-01', '2030-12-31'),
(3,  'Child Discount',   50.0,  'Discount for children under 12',     'Age under 12',                 '2020-01-01', '2030-12-31'),
(4,  'Disabled Discount',60.0,  'Discount for disabled persons',      'Valid disability certificate', '2020-01-01', '2030-12-31'),
(5,  'Staff Discount',   100.0, 'Free travel for staff',              'Valid employee ID',            '2020-01-01', '2030-12-31'),
(6,  'Weekend Promo',    20.0,  'Weekend promotional discount',       'Valid on weekends only',       '2023-01-01', '2025-12-31'),
(7,  'Monthly Pass',     15.0,  'Discount for monthly pass holders',  'Active monthly pass required', '2021-01-01', '2030-12-31'),
(8,  'Early Bird',       10.0,  'Early morning travel discount',      'Trips before 7:00 AM',         '2022-06-01', '2026-12-31'),
(9,  'Group Discount',   25.0,  'Discount for groups of 5+',          'Minimum 5 persons',            '2021-01-01', '2030-12-31'),
(10, 'Annual Pass',      18.0,  'Discount for annual pass holders',   'Active annual pass required',  '2020-01-01', '2030-12-31');


-- 3. ZONE  
INSERT INTO ZONE (ZONE_ID, ZONE_NAME, ZONE_NUMBER, DESCRIPTION) VALUES
('Z01', 'City Centre',    1,  'Zone 1 coverage area'),
('Z02', 'Inner North',    2,  'Zone 2 coverage area'),
('Z03', 'Inner South',    3,  'Zone 3 coverage area'),
('Z04', 'Inner East',     4,  'Zone 4 coverage area'),
('Z05', 'Inner West',     5,  'Zone 5 coverage area'),
('Z06', 'Outer North',    6,  'Zone 6 coverage area'),
('Z07', 'Outer South',    7,  'Zone 7 coverage area'),
('Z08', 'Outer East',     8,  'Zone 8 coverage area'),
('Z09', 'Outer West',     9,  'Zone 9 coverage area'),
('Z10', 'CBD Core',       10, 'Zone 10 coverage area'),
('Z11', 'Airport Zone',   11, 'Zone 11 coverage area'),
('Z12', 'University Zone',12, 'Zone 12 coverage area'),
('Z13', 'Suburban North', 13, 'Zone 13 coverage area'),
('Z14', 'Suburban South', 14, 'Zone 14 coverage area'),
('Z15', 'Suburban East',  15, 'Zone 15 coverage area'),
('Z16', 'Suburban West',  16, 'Zone 16 coverage area'),
('Z17', 'Regional A',     17, 'Zone 17 coverage area'),
('Z18', 'Regional B',     18, 'Zone 18 coverage area'),
('Z19', 'Regional C',     19, 'Zone 19 coverage area'),
('Z20', 'Regional D',     20, 'Zone 20 coverage area');


-- 4. VEHICLE_TYPE 
INSERT INTO VEHICLE_TYPE (VEHICLE_TYPE_ID, TYPE_NAME, DESCRIPTION) VALUES
(0, 'BUS',   'Road vehicle used for flexible urban and suburban passenger transport.'),
(1, 'TRAM',  'Electric rail vehicle operating on city streets with fixed stops and routes.'),
(2, 'TRAIN', 'Rail vehicle used for long-distance or regional passenger transport with high capacity.');


-- 5. STOPS  
INSERT INTO STOPS (STOP_ID, STOP_NAME, LATITUDE, LONGITUDE, ZONE_ID, VEHICLE_TYPE_ID) VALUES
('1000',  'Dole Ave/Cheddar Rd',        -37.70077481, 145.01895105, 'Z09', 0),
('10001', 'Rex St/Taylors Rd',          -37.72697521, 144.77615243, 'Z06', 0),
('10002', 'Yuille St/Centenary Ave',    -37.6761595,  144.59578941, 'Z13', 0),
('10009', 'Gum Rd/Main Rd West',        -37.74149711, 144.77589939, 'Z06', 0),
('1001',  'Lloyd Ave/Cheddar Rd',       -37.69918301, 145.01968529, 'Z09', 0),
('10010', 'Kings Rd/Main Rd West',      -37.74194553, 144.78008474, 'Z06', 0),
('10011', 'Moffat St/Main Rd West',     -37.7423246,  144.7834665,  'Z06', 0),
('10012', 'Washington St/Main Rd West', -37.74279566, 144.78791229, 'Z06', 0),
('10013', 'Kate St/Main Rd West',       -37.74356938, 144.79457342, 'Z06', 0);

INSERT INTO STOPS (STOP_ID, STOP_NAME, LATITUDE, LONGITUDE, ZONE_ID, VEHICLE_TYPE_ID)
SELECT
    gs::varchar,
    'Stop ' || gs,
    (-37.5 - random() * 0.6)::decimal(10,7),
    (144.5 + random() * 0.9)::decimal(10,7),
    'Z' || lpad((1 + (gs % 20))::text, 2, '0'),
    (gs % 3)
FROM generate_series(1002, 27000) gs
WHERE gs NOT IN (1000,1001,10001,10002,10009,10010,10011,10012,10013)
ON CONFLICT (STOP_ID, VEHICLE_TYPE_ID) DO NOTHING;


-- 6. ROUTE 
INSERT INTO ROUTE (ROUTE_ID, AGENCY_ID, START_STOP_ID, START_STOP_VT, END_STOP_ID, END_STOP_VT,
                   ROUTE_NAME, DISTANCE_KM, ESTIMATED_DURATION, VEHICLE_TYPE_ID) VALUES
('14-200-aus-1', 3, NULL, 0, NULL, 0, 'Bulleen - City (Queen St)',                NULL, NULL, 0),
('14-207-aus-1', 5, NULL, 0, NULL, 0, 'Doncaster SC - City (Queen St)',           NULL, NULL, 0),
('14-270-aus-1', 1, NULL, 0, NULL, 0, 'Mitcham - Box Hill',                       NULL, NULL, 0),
('14-271-aus-1', 1, NULL, 0, NULL, 0, 'Ringwood - Box Hill Station',              NULL, NULL, 0),
('14-273-aus-1', 1, NULL, 0, NULL, 0, 'The Pines SC - Nunawading Station',        NULL, NULL, 0),
('14-279-aus-1', 5, NULL, 0, NULL, 0, 'Doncaster SC/Templestowe - Box Hill Station', NULL, NULL, 0),
('14-280-aus-1', 5, NULL, 0, NULL, 0, 'The Pines SC',                             NULL, NULL, 0),
('14-281-aus-1', 5, NULL, 0, NULL, 0, 'Box Hill Station - Templestowe',           NULL, NULL, 0),
('14-282-aus-1', 2, NULL, 0, NULL, 0, 'The Pines SC',                             NULL, NULL, 0);

INSERT INTO ROUTE (ROUTE_ID, AGENCY_ID, START_STOP_ID, START_STOP_VT, END_STOP_ID, END_STOP_VT,
                   ROUTE_NAME, DISTANCE_KM, ESTIMATED_DURATION, VEHICLE_TYPE_ID)
SELECT
    (ARRAY['14','19','21','26','32','37','40','42','43','53','58','60','65'])[1 + (gs % 13)]
        || '-' || (gs * 7 % 900 + 100)::text || '-aus-1',
    (1 + (gs % 5)),
    NULL, 0, NULL, 0,
    (ARRAY['City - Airport Express','CBD Loop Service','Suburban Connector',
           'University Shuttle','Bayside Express','Northern Link',
           'Eastern Corridor','Western Loop','Inner Circle',
           'Peak Express','Night Rider','Cross-City Link','Orbital Route'])[1 + (gs % 13)],
    NULL, NULL,
    (gs % 3)
FROM generate_series(1, 700) gs
ON CONFLICT (ROUTE_ID) DO NOTHING;


-- 7. ROUTE_STOP  
INSERT INTO ROUTE_STOP (ROUTE_STOP_ID, ROUTE_ID, STOP_ID, STOP_VT, STOP_SEQUENCE, DISTANCE_TRAVELED, TIMEPOINT) VALUES
(1,  '14-200-aus-1', '5520',  0, 0, 0.54,  1),
(2,  '14-200-aus-1', '787',   0, 1, 2.11,  1),
(3,  '14-200-aus-1', '28597', 0, 2, 3.77,  1),
(4,  '14-200-aus-1', '37721', 0, 3, 4.21,  1),
(5,  '14-200-aus-1', '9963',  0, 4, 6.68,  1),
(6,  '14-200-aus-1', '18764', 0, 5, 9.00,  1),
(7,  '14-207-aus-1', '1628',  0, 0, 1.87,  1),
(8,  '14-207-aus-1', '50851', 0, 1, 3.39,  1),
(9,  '14-207-aus-1', '28723', 0, 2, 5.50,  1),
(10, '14-207-aus-1', '3545',  0, 3, 6.43,  1);

INSERT INTO ROUTE_STOP (ROUTE_ID, STOP_ID, STOP_VT, STOP_SEQUENCE, DISTANCE_TRAVELED, TIMEPOINT)
SELECT
    r.route_id,
    (1002 + ((r.rn * 15 + s.seq - 1) % 25999))::varchar,
    0,
    s.seq - 1,
    ((s.seq - 1) * (0.5 + random() * 2.5))::decimal(8,2),
    1
FROM
    (SELECT route_id, row_number() OVER () AS rn FROM ROUTE) r,
    generate_series(1, 15) s(seq)
LIMIT 10990;   


-- 8. SHAPE
INSERT INTO SHAPE (SHAPE_ID, DESCRIPTION, VEHICLE_TYPE_ID) VALUES
('14-200-aus-1.2.H', 'Southern branch line',  0),
('14-200-aus-1.3.R', 'Direct service path',   0),
('14-207-aus-1.2.R', 'Reverse direction path',0),
('14-207-aus-1.3.H', 'Southern branch line',  0),
('14-270-aus-1.2.R', 'Suburban connector',    0),
('14-270-aus-1.3.H', 'Main corridor route',   0),
('14-271-aus-1.2.R', 'Reverse direction path',0),
('14-271-aus-1.3.R', 'Peak hour variant',     0),
('14-271-aus-1.4.R', 'Main corridor route',   0);

INSERT INTO SHAPE (SHAPE_ID, DESCRIPTION, VEHICLE_TYPE_ID)
SELECT
    route_id || '.' || variant || '.' || dir,
    (ARRAY['Main corridor route','Reverse direction path','Southern branch line',
           'Direct service path','Peak hour variant','Suburban connector'])[1 + (row_number() OVER () % 6)::int],
    (row_number() OVER () % 3)::int
FROM
    ROUTE
    CROSS JOIN (VALUES (2),(3),(4)) v(variant)
    CROSS JOIN (VALUES ('H'),('R')) d(dir)
ON CONFLICT (SHAPE_ID) DO NOTHING
LIMIT 5000;


-- 9. SHAPE_POINT
INSERT INTO SHAPE_POINT (SHAPE_ID, SHAPE_PT_SEQUENCE, SHAPE_PT_LAT, SHAPE_PT_LON, SHAPE_DIST_TRAVELED, VEHICLE_TYPE_ID) VALUES
('14-200-aus-1.2.H', 1, -37.81615682, 144.96115858,   0.0,   0),
('14-200-aus-1.2.H', 2, -37.81601034, 144.96109377,  17.25,  0),
('14-200-aus-1.2.H', 3, -37.81596499, 144.96107707,  22.51,  0),
('14-200-aus-1.2.H', 4, -37.81527434, 144.96074461, 104.67,  0),
('14-200-aus-1.2.H', 5, -37.81501394, 144.96063193, 135.27,  0),
('14-200-aus-1.2.H', 6, -37.81490367, 144.96058714, 148.15,  0),
('14-200-aus-1.2.H', 7, -37.81452051, 144.96041139, 193.46,  0),
('14-200-aus-1.2.H', 8, -37.81399553, 144.96016946, 255.59,  0);

INSERT INTO SHAPE_POINT (SHAPE_ID, SHAPE_PT_SEQUENCE, SHAPE_PT_LAT, SHAPE_PT_LON, SHAPE_DIST_TRAVELED, VEHICLE_TYPE_ID)
SELECT
    s.shape_id,
    pt.seq,
    (-37.5  - random() * 0.6)::decimal(10,7),
    (144.5  + random() * 0.9)::decimal(10,7),
    ((pt.seq - 1) * (15.0 + random() * 30.0))::decimal(8,2),
    s.vehicle_type_id
FROM SHAPE s
CROSS JOIN generate_series(1, 500) pt(seq)
ON CONFLICT (SHAPE_ID, SHAPE_PT_SEQUENCE) DO NOTHING;


-- 10. VEHICLE  
INSERT INTO VEHICLE (VEHICLE_ID, VEHICLE_TYPE_ID, REGISTRATION_NUMBER, MODEL, MANUFACTURER, CAPACITY, YEAR_OF_MANUFACTURE, STATUS) VALUES
('V0',  2, 'QN606RN', 'Fast-97',   'Hyundai', 111, 1997, 'MAINTENANCE'),
('V1',  0, 'CO767XD', 'Metro-57',  'MAN',     214, 2007, 'ACTIVE'),
('V2',  1, 'RV015SV', 'Metro-17',  'CAF',     281, 1997, 'MAINTENANCE'),
('V3',  2, 'UF352KU', 'City-60',   'CAF',     235, 2023, 'MAINTENANCE'),
('V4',  0, 'IX890WA', 'Urban-80',  'Scania',  122, 2012, 'INACTIVE'),
('V5',  2, 'XH211TJ', 'Urban-99',  'Alstom',  226, 2025, 'INACTIVE'),
('V6',  1, 'VY493NL', 'Fast-54',   'Scania',  189, 2018, 'INACTIVE'),
('V7',  2, 'CC480GL', 'Urban-57',  'Alstom',  276, 2022, 'ACTIVE'),
('V8',  2, 'HQ918DJ', 'Metro-63',  'Alstom',  163, 2000, 'MAINTENANCE'),
('V9',  0, 'JM685VA', 'Urban-11',  'Scania',   77, 2000, 'MAINTENANCE'),
('V10', 2, 'YT666UR', 'Metro-91',  'Skoda',   118, 2024, 'ACTIVE');

INSERT INTO VEHICLE (VEHICLE_ID, VEHICLE_TYPE_ID, REGISTRATION_NUMBER, MODEL, MANUFACTURER,
                     CAPACITY, YEAR_OF_MANUFACTURE, STATUS)
SELECT
    'V' || gs,
    (gs % 3),
    chr(65 + (gs * 7  % 26)) || chr(65 + (gs * 13 % 26)) ||
    lpad((gs % 1000)::text, 3, '0') ||
    chr(65 + (gs * 3  % 26)) || chr(65 + (gs * 17 % 26)),
    (ARRAY['Metro','Urban','City','Fast','Express'])[1 + (gs % 5)] || '-' || (gs % 100),
    (ARRAY['MAN','CAF','Scania','Alstom','Skoda','Hyundai','Siemens'])[1 + (gs % 7)],
    (60 + (gs % 3) * 80 + (gs % 80)),
    1997 + (gs % 29),
    CASE
        WHEN gs % 50  = 0 THEN 'MAINTENANCE'
        WHEN gs % 100 = 0 THEN 'INACTIVE'
        WHEN gs % 200 = 0 THEN 'DECOMMISSIONED'
        ELSE 'ACTIVE'
    END
FROM generate_series(11, 4999) gs
ON CONFLICT (VEHICLE_ID) DO NOTHING;


-- 11. EMPLOYEE
INSERT INTO EMPLOYEE (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, PHONE, EMAIL, HIRE_DATE, POSITION, STATUS, PASSWORD_HASH) VALUES
('E0',  'Paul',      'Martin',     '075414458', 'paulmartin@outlook.com',      '2023-09-06', 'Discount_officer', 'TERMINATED', '41a3f483bbdf22492001f893f987b3bc19fe8f2125231c99a734f7b29ef0319e'),
('E1',  'Emily',     'Snyder',     '075196141', 'emilysnyder@yahoo.com',       '2018-03-08', 'Conductor',        'ACTIVE',     '4c03f9fac0559d8433430a7e4a2af55cc3bbb44b0a776bd6d38e23bc4ff38d62'),
('E2',  'Kim',       'Cabrera',    '076005797', 'kimcabrera@yahoo.com',        '2017-10-03', 'Conductor',        'ON_LEAVE',   '5cab693cc34d346868d2602aeef0654fa0b0fe0c962fb1071c8da27e450f603f'),
('E3',  'Stephanie', 'Flynn',      '075653688', 'stephanieflynn@gmail.com',    '2021-08-22', 'Driver',           'ACTIVE',     '8533c71de6850d87dfae7530c8955b19807e9851b24a72b973ad6f627bf10e12'),
('E4',  'Felicia',   'Monroe',     '077681952', 'feliciamonroe@gmail.com',     '2025-10-24', 'Conductor',        'TERMINATED', 'c43e6969a7e75780a42bade53e028d882d92ebfe5690d58d8ce8c847b174bd35'),
('E5',  'Jason',     'Silva',      '077455841', 'jasonsilva@gmail.com',        '2023-07-04', 'Conductor',        'ON_LEAVE',   'aaf62b95e13e4373aaef89e63eae1fc82bee8b5f051e026d34dcfc2c74d46626'),
('E6',  'William',   'Odonnell',   '077831609', 'williamodonnell@yahoo.com',   '2020-06-01', 'Conductor',        'ACTIVE',     'c7fb27d867de03cd011f86cf2724fe9ebb83a9d25c6367c33f58bc48bc041fc5'),
('E7',  'Jim',       'Cook',       '075765534', 'jimcook@gmail.com',           '2022-08-18', 'Driver',           'ACTIVE',     'd89b57880d9fa55631a4a04b797cc8ae6cd5f92f5c6d515345a8bb8da6dece29'),
('E8',  'Timothy',   'Fitzgerald', '077182043', 'timothyfitzgerald@yahoo.com', '2017-09-25', 'Conductor',        'ON_LEAVE',   '14f93a567627d2405be356c9892aebafaf96b2f7e17948c7d6248e04c982fc19'),
('E9',  'Anthony',   'Jones',      '076571402', 'anthonyjones@yahoo.com',      '2018-03-30', 'Conductor',        'ON_LEAVE',   'e2b88297bf85c18e74479805304590040747018c03fb0572ce8e8e7d1afc35d1'),
('E10', 'Derek',     'Lynn',       '075734317', 'dereklynn@outlook.com',       '2021-01-31', 'Driver',           'INACTIVE',   'b77f5395f38a55f7bc1a7a9124bab208bac8f5fabd2dd47ae1c329eede4de554');

INSERT INTO EMPLOYEE (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, PHONE, EMAIL, HIRE_DATE, POSITION, STATUS, PASSWORD_HASH)
SELECT
    'E' || gs,
    (ARRAY['Paul','Emily','Kim','Stephanie','Felicia','Jason','William','Jim','Timothy','Anthony',
           'Derek','Sarah','Michael','Jessica','Robert','Jennifer','David','Lisa','James','Mary',
           'John','Patricia','Charles','Linda','Daniel','Barbara','Matthew','Susan','Mark','Karen'])[1 + (gs % 30)],
    (ARRAY['Martin','Snyder','Cabrera','Flynn','Monroe','Silva','Odonnell','Cook','Fitzgerald','Jones',
           'Lynn','Smith','Johnson','Williams','Brown','Davis','Miller','Wilson','Moore','Taylor',
           'Anderson','Thomas','Jackson','White','Harris','Garcia','Thompson','Young','Lee','Walker'])[1 + (gs % 30)],
    '07' || lpad((5000000 + gs * 7 % 5000000)::text, 7, '0'),
    lower(
        (ARRAY['paul','emily','kim','stephanie','felicia','jason','william','jim','timothy','anthony',
               'derek','sarah','michael','jessica','robert','jennifer','david','lisa','james','mary',
               'john','patricia','charles','linda','daniel','barbara','matthew','susan','mark','karen'])[1 + (gs % 30)]
        ||
        (ARRAY['martin','snyder','cabrera','flynn','monroe','silva','odonnell','cook','fitzgerald','jones',
               'lynn','smith','johnson','williams','brown','davis','miller','wilson','moore','taylor',
               'anderson','thomas','jackson','white','harris','garcia','thompson','young','lee','walker'])[1 + (gs % 30)]
    ) || gs || '@' || (ARRAY['gmail.com','yahoo.com','outlook.com'])[1 + (gs % 3)],
    CURRENT_DATE - (random() * 3650)::int,
    CASE WHEN gs % 4 = 0 THEN 'Driver'
         WHEN gs % 4 = 1 THEN 'Conductor'
         WHEN gs % 4 = 2 THEN 'Discount_officer'
         ELSE 'Manager' END,
    CASE WHEN gs % 40  = 0 THEN 'TERMINATED'
         WHEN gs % 20  = 0 THEN 'ON_LEAVE'
         WHEN gs % 100 = 0 THEN 'INACTIVE'
         ELSE 'ACTIVE' END,
    md5('password' || gs)
FROM generate_series(11, 24999) gs
ON CONFLICT (EMPLOYEE_ID) DO NOTHING;


-- 12. DISCOUNT_OFFICER  
INSERT INTO DISCOUNT_OFFICER (OFFICER_ID, EMPLOYEE_ID, ASSIGNED_REGION, CERTIFICATION_CODE, AUTHORIZED_FROM, AUTHORIZED_TO) VALUES
('OFF0',  'E0',  'General', 'OFF-CERT-0000', '2023-09-06', '2029-05-18'),
('OFF1',  'E13', 'West',    'OFF-CERT-0001', '2022-07-08', '2023-12-25'),
('OFF2',  'E20', 'East',    'OFF-CERT-0002', '2020-05-15', '2029-11-02'),
('OFF3',  'E22', 'General', 'OFF-CERT-0003', '2016-10-17', '2018-04-25'),
('OFF4',  'E37', 'General', 'OFF-CERT-0004', '2025-05-17', '2028-02-23'),
('OFF5',  'E43', 'South',   'OFF-CERT-0005', '2016-05-30', '2025-06-27'),
('OFF6',  'E57', 'General', 'OFF-CERT-0006', '2019-05-17', '2020-12-13'),
('OFF7',  'E58', 'Central', 'OFF-CERT-0007', '2022-02-12', '2030-01-25'),
('OFF8',  'E63', 'South',   'OFF-CERT-0008', '2016-10-18', '2027-08-13'),
('OFF9',  'E71', 'General', 'OFF-CERT-0009', '2019-01-17', '2030-06-25'),
('OFF10', 'E73', 'West',    'OFF-CERT-0010', '2019-07-23', '2026-07-23');

INSERT INTO DISCOUNT_OFFICER (OFFICER_ID, EMPLOYEE_ID, ASSIGNED_REGION, CERTIFICATION_CODE, AUTHORIZED_FROM, AUTHORIZED_TO)
SELECT
    'OFF' || gs,
    'E' || (gs * 4 + 2),
    (ARRAY['General','North','South','East','West','Central'])[1 + (gs % 6)],
    'OFF-CERT-' || lpad(gs::text, 4, '0'),
    CURRENT_DATE - (random() * 3000)::int,
    CURRENT_DATE + (random() * 1500)::int
FROM generate_series(11, 2499) gs
ON CONFLICT (OFFICER_ID) DO NOTHING;


-- 13. DISCOUNT_OFFICER_DISCOUNT 
INSERT INTO DISCOUNT_OFFICER_DISCOUNT (ID, OFFICER_ID, DISCOUNT_ID, ASSIGNED_DATE, NOTES) VALUES
(1,  'OFF0', 9,  '2024-02-05', 'Renewal of previous assignment'),
(2,  'OFF1', 10, '2025-06-25', 'Extended authorization period'),
(3,  'OFF2', 9,  '2025-04-26', 'Authorized for student discounts'),
(4,  'OFF2', 4,  '2024-06-05', 'Temporary authorization'),
(5,  'OFF3', 5,  '2025-03-21', 'Granted for high-demand routes'),
(6,  'OFF3', 1,  '2026-02-18', 'Authorized for student discounts'),
(7,  'OFF4', 9,  '2026-01-06', 'Authorized for student discounts'),
(8,  'OFF4', 6,  '2023-12-12', 'Renewal of previous assignment'),
(9,  'OFF5', 8,  '2026-01-18', 'Authorized for senior discounts'),
(10, 'OFF5', 7,  '2026-01-14', 'Extended authorization period');

INSERT INTO DISCOUNT_OFFICER_DISCOUNT (OFFICER_ID, DISCOUNT_ID, ASSIGNED_DATE, NOTES)
SELECT
    'OFF' || (gs % 2500),
    (1 + (gs % 10)),
    CURRENT_DATE - (random() * 800)::int,
    (ARRAY['Renewal of previous assignment','Extended authorization period',
           'Authorized for student discounts','Temporary authorization',
           'Granted for high-demand routes','Authorized for senior discounts'])[1 + (gs % 6)]
FROM generate_series(11, 7500) gs;


-- 14. DRIVER 
INSERT INTO DRIVER (DRIVER_ID, EMPLOYEE_ID, LICENSE_NUMBER, LICENSE_CATEGORY, LICENSE_EXPIRY_DATE, YEARS_EXPERIENCE) VALUES
('D0',  'E3',  'QP43551', 'D1',  '2034-05-30', 22),
('D1',  'E7',  'ZA42594', 'D+E', '2029-10-11', 22),
('D2',  'E10', 'OV12030', 'D+E', '2028-12-23',  3),
('D3',  'E14', 'XV74573', 'D',   '2032-12-01', 14),
('D4',  'E16', 'YT78687', 'D1',  '2033-01-13', 15),
('D5',  'E21', 'QH30987', 'D+E', '2030-09-14',  5),
('D6',  'E24', 'KU95016', 'D+E', '2032-09-11', 13),
('D7',  'E28', 'UQ18105', 'D',   '2029-12-10', 20),
('D8',  'E29', 'AZ58103', 'D+E', '2027-05-26',  4),
('D9',  'E33', 'PH24620', 'D',   '2029-04-02', 21),
('D10', 'E34', 'NP46026', 'D1',  '2034-04-30', 22);

INSERT INTO DRIVER (DRIVER_ID, EMPLOYEE_ID, LICENSE_NUMBER, LICENSE_CATEGORY, LICENSE_EXPIRY_DATE, YEARS_EXPERIENCE)
SELECT
    'D' || gs,
    'E' || (gs * 4),
    chr(65 + (gs * 11 % 26)) || chr(65 + (gs * 19 % 26)) || lpad((gs % 100000)::text, 5, '0'),
    (ARRAY['D','D1','D+E','DE'])[1 + (gs % 4)],
    CURRENT_DATE + (random() * 3650)::int,
    (random() * 25)::int
FROM generate_series(11, 12499) gs
ON CONFLICT (DRIVER_ID) DO NOTHING;


-- 15. CONDUCTOR
INSERT INTO CONDUCTOR (CONDUCTOR_ID, EMPLOYEE_ID, CERTIFICATION_NUMBER, HIRE_ROLE_DATE) VALUES
('CON0',  'E1',  'CERT00000', '2018-03-08'),
('CON1',  'E2',  'CERT00001', '2017-10-03'),
('CON2',  'E4',  'CERT00002', '2025-10-24'),
('CON3',  'E5',  'CERT00003', '2023-07-04'),
('CON4',  'E6',  'CERT00004', '2020-06-01'),
('CON5',  'E8',  'CERT00005', '2017-09-25'),
('CON6',  'E9',  'CERT00006', '2018-03-30'),
('CON7',  'E11', 'CERT00007', '2024-03-21'),
('CON8',  'E12', 'CERT00008', '2019-07-01'),
('CON9',  'E15', 'CERT00009', '2018-07-18'),
('CON10', 'E17', 'CERT00010', '2025-06-23');

INSERT INTO CONDUCTOR (CONDUCTOR_ID, EMPLOYEE_ID, CERTIFICATION_NUMBER, HIRE_ROLE_DATE)
SELECT
    'CON' || gs,
    'E' || (gs * 4 + 1),
    'CERT' || lpad(gs::text, 5, '0'),
    CURRENT_DATE - (random() * 2500)::int
FROM generate_series(11, 9999) gs
ON CONFLICT (CONDUCTOR_ID) DO NOTHING;


-- 16. SHIFT  
INSERT INTO SHIFT (SHIFT_ID, SHIFT_NAME, START_TIME, END_TIME, DESCRIPTION) VALUES
(1, 'First Shift',  '06:00:00', '14:00:00', 'Morning shift'),
(2, 'Second Shift', '14:00:00', '22:00:00', 'Afternoon shift'),
(3, 'Third Shift',  '22:00:00', '06:00:00', 'Night shift');


-- 17. TRIP  
INSERT INTO TRIP (TRIP_ID, ROUTE_ID, VEHICLE_ID, DRIVER_ID, SHAPE_ID, TRIP_HEADSIGN,
                  START_TIME, END_TIME, DIRECTION_ID, WHEELCHAIR_ACCESSIBLE,
                  TRIP_DATE, STATUS, VEHICLE_TYPE_ID) VALUES
('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),
('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),
('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),
('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),
('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),
('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),
('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),
('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),
('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),
('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),
('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),
('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);

INSERT INTO VEHICLE (VEHICLE_ID, VEHICLE_TYPE_ID, REGISTRATION_NUMBER, MODEL, MANUFACTURER, CAPACITY, YEAR_OF_MANUFACTURE, STATUS)
SELECT 'V' || gs, 0, 'AA' || lpad(gs::text,3,'0') || 'AA', 'Urban-' || gs, 'MAN', 100, 2010, 'ACTIVE'
FROM generate_series(1000, 12500) gs
ON CONFLICT (VEHICLE_ID) DO NOTHING;

INSERT INTO DRIVER (DRIVER_ID, EMPLOYEE_ID, LICENSE_NUMBER, LICENSE_CATEGORY, LICENSE_EXPIRY_DATE, YEARS_EXPERIENCE)
SELECT 'D' || gs, 'E' || LEAST(gs * 2, 24999), 'XX' || lpad(gs::text,5,'0'), 'D', '2030-01-01', 5
FROM generate_series(1000, 12499) gs
ON CONFLICT (DRIVER_ID) DO NOTHING;

INSERT INTO EMPLOYEE (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, PHONE, EMAIL, HIRE_DATE, POSITION, STATUS, PASSWORD_HASH)
SELECT 'E' || gs, 'First'||gs, 'Last'||gs, '0700000000', 'emp'||gs||'@mail.com', '2015-01-01', 'Driver', 'ACTIVE', md5(gs::text)
FROM generate_series(25000, 30000) gs
ON CONFLICT (EMPLOYEE_ID) DO NOTHING;

INSERT INTO SHAPE (SHAPE_ID, DESCRIPTION, VEHICLE_TYPE_ID)
SELECT 'SHAPE-GEN-' || gs, 'Generated shape', gs % 3
FROM generate_series(1, 500) gs
ON CONFLICT (SHAPE_ID) DO NOTHING;

INSERT INTO TRIP (TRIP_ID, ROUTE_ID, VEHICLE_ID, DRIVER_ID, SHAPE_ID,
                  TRIP_HEADSIGN, START_TIME, END_TIME, DIRECTION_ID,
                  WHEELCHAIR_ACCESSIBLE, TRIP_DATE, STATUS, VEHICLE_TYPE_ID)
SELECT
    r.route_id || '--' || (gs % 2) || '-MF' || (1 + (gs % 14)) || '-' || (gs * 3 + 100),
    r.route_id,
    'V' || (1000 + (gs % 3500)),
    'D' || (1000 + (gs % 3500)),
    'SHAPE-GEN-' || (1 + (gs % 500)),
    (ARRAY['City CBD','Airport Terminal','Southern Cross Station','Flinders Street',
           'Melbourne Central','Ringwood','Dandenong','Frankston',
           'Box Hill','Sunshine','Broadmeadows','Werribee','Lilydale','Belgrave'])[1 + (gs % 14)],
    (TIME '05:00' + ((gs % 540) * INTERVAL '2 minutes')),
    (TIME '05:40' + ((gs % 540) * INTERVAL '2 minutes')),
    gs % 2,
    gs % 2,
    CURRENT_DATE - (random() * 730)::int,
    (ARRAY['SCHEDULED','SCHEDULED','SCHEDULED','IN_PROGRESS','COMPLETED','COMPLETED','COMPLETED','CANCELLED','DELAYED'])[1 + (gs % 9)],
    r.vt
FROM
    (SELECT route_id, (row_number() OVER ()) AS rn,
            (CASE WHEN route_id LIKE '14-%' THEN 0
                  WHEN route_id LIKE '58-%' OR route_id LIKE '21-%' THEN 1
                  ELSE 2 END) AS vt
     FROM ROUTE) r
    CROSS JOIN generate_series(1, 544) gs
ON CONFLICT (TRIP_ID) DO NOTHING
LIMIT 379988;  

-- 18. STOP_TIME 
INSERT INTO STOP_TIME (STOP_TIME_ID, TRIP_ID, STOP_ID, STOP_VT, ROUTE_STOP_ID,
                       ARRIVAL_TIME, DEPARTURE_TIME, STOP_SEQUENCE, SHAPE_DIST_TRAVELED, VEHICLE_TYPE_ID) VALUES
('37-503--1-MF14-503180_1',  '37-503--1-MF14-503180', '18771', 0, NULL, '07:20:00', '07:20:00',  1,    0.0,    0),
('37-503--1-MF14-503180_2',  '37-503--1-MF14-503180', '6697',  0, NULL, '07:22:00', '07:22:00',  2,  354.81,  0),
('37-503--1-MF14-503180_3',  '37-503--1-MF14-503180', '10549', 0, NULL, '07:23:00', '07:23:00',  3,  745.13,  0),
('37-503--1-MF14-503180_4',  '37-503--1-MF14-503180', '10548', 0, NULL, '07:24:00', '07:24:00',  4,  924.8,   0),
('37-503--1-MF14-503180_5',  '37-503--1-MF14-503180', '10547', 0, NULL, '07:25:00', '07:25:00',  5, 1092.79,  0),
('37-503--1-MF14-503180_6',  '37-503--1-MF14-503180', '10546', 0, NULL, '07:25:00', '07:25:00',  6, 1256.34,  0),
('37-503--1-MF14-503180_7',  '37-503--1-MF14-503180', '8854',  0, NULL, '07:27:00', '07:27:00',  7, 1591.89,  0),
('37-503--1-MF14-503180_8',  '37-503--1-MF14-503180', '10544', 0, NULL, '07:28:00', '07:28:00',  8, 1814.19,  0),
('37-503--1-MF14-503180_9',  '37-503--1-MF14-503180', '10543', 0, NULL, '07:29:00', '07:29:00',  9, 2010.66,  0),
('37-503--1-MF14-503180_10', '37-503--1-MF14-503180', '10542', 0, NULL, '07:30:00', '07:30:00', 10, 2191.9,   0);

DO $$
DECLARE
    batch     INT := 5000;
    total     INT := 380000;
    stops_per INT := 37;
    i         INT := 0;
    trip_ids  TEXT[];
BEGIN
    SELECT array_agg(trip_id ORDER BY trip_id)
    INTO trip_ids
    FROM TRIP;

    WHILE i < total LOOP
        INSERT INTO STOP_TIME (
            STOP_TIME_ID, TRIP_ID, STOP_ID, STOP_VT,
            ROUTE_STOP_ID, ARRIVAL_TIME, DEPARTURE_TIME,
            STOP_SEQUENCE, SHAPE_DIST_TRAVELED, VEHICLE_TYPE_ID
        )
        SELECT
            trip_ids[t_idx] || '_' || s_seq,
            trip_ids[t_idx],
            (1002 + ((t_idx * stops_per + s_seq) % 25999))::varchar,
            0,
            NULL,
            (TIME '05:00' + ((t_idx % 540) * INTERVAL '2 minutes') + ((s_seq-1) * INTERVAL '90 seconds')),
            (TIME '05:00' + ((t_idx % 540) * INTERVAL '2 minutes') + ((s_seq-1) * INTERVAL '90 seconds') + INTERVAL '20 seconds'),
            s_seq,
            ((s_seq-1) * (200 + random() * 400))::decimal(8,2),
            0
        FROM generate_series(i+1, LEAST(i+batch, total)) t_idx,
             generate_series(1, stops_per) s_seq
        ON CONFLICT (STOP_TIME_ID) DO NOTHING;

        i := i + batch;
        RAISE NOTICE 'stop_time: % / % trips done', i, total;
    END LOOP;
END;
$$;

-- 19. TRANSFER 
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
(1,  '11212', 2, '11212', 2, 'aus:vic:vic-02-MDD:', 'aus:vic:vic-02-HBE:', 2),
(2,  '11212', 2, '11212', 2, 'aus:vic:vic-02-HBE:', 'aus:vic:vic-02-MDD:', 2),
(3,  '11212', 2, '11212', 2, 'aus:vic:vic-02-HBE:', 'aus:vic:vic-02-MDD:', 2),
(4,  '11212', 2, '11212', 2, 'aus:vic:vic-02-HBE:', 'aus:vic:vic-02-MDD:', 2),
(5,  '11212', 2, '11212', 2, 'aus:vic:vic-02-HBE:', 'aus:vic:vic-02-MDD:', 2),
(6,  '11212', 2, '11212', 2, 'aus:vic:vic-02-HBE:', 'aus:vic:vic-02-HBE:', 2),
(7,  '11212', 2, '11212', 2, 'aus:vic:vic-02-HBE:', 'aus:vic:vic-02-HBE:', 2),
(8,  '11212', 2, '11212', 2, 'aus:vic:vic-02-HBE:', 'aus:vic:vic-02-HBE:', 2),
(9,  '11212', 2, '11212', 2, 'aus:vic:vic-02-HBE:', 'aus:vic:vic-02-MDD:', 2),
(10, '11212', 2, '11212', 2, 'aus:vic:vic-02-HBE:', 'aus:vic:vic-02-MDD:', 2);

INSERT INTO TRANSFER (FROM_STOP_ID, FROM_STOP_VT, TO_STOP_ID, TO_STOP_VT, FROM_ROUTE_ID, TO_ROUTE_ID, VEHICLE_TYPE_ID)
SELECT
    (1002 + (gs % 25999))::varchar,
    (gs % 3),
    (1002 + ((gs+1) % 25999))::varchar,
    (gs % 3),
    r1.route_id,
    r2.route_id,
    (gs % 3)
FROM generate_series(11, 15000) gs
JOIN LATERAL (SELECT route_id FROM ROUTE ORDER BY random() LIMIT 1) r1 ON TRUE
JOIN LATERAL (SELECT route_id FROM ROUTE ORDER BY random() LIMIT 1) r2 ON TRUE;


-- 20. DELAY_LOG 
INSERT INTO DELAY_LOG (DELAY_ID, TRIP_ID, STOP_ID, STOP_VT, DELAY_MINUTES, DELAY_REASON, REPORTED_TIME) VALUES
(1,  '14-200--1-MF1-200003', '14923', 0, 38, 'Vehicle breakdown',  '2026-04-20 17:46:13'),
(2,  '14-200--1-MF1-200003', '22664', 0, 23, 'Weather conditions', '2026-04-17 05:47:13'),
(3,  '14-200--1-MF1-200003', '19599', 1, 33, 'Signal problem',     '2026-04-19 16:01:13'),
(4,  '14-200--1-MF1-200008', '20746', 0, 21, 'Signal problem',     '2026-04-17 22:10:13'),
(5,  '14-200--1-MF1-200008', '51019', 0, 43, 'Vehicle breakdown',  '2026-04-18 13:54:13'),
(6,  '14-200--1-MF1-200008', '3921',  0, 34, 'Vehicle breakdown',  '2026-04-22 11:00:13'),
(7,  '14-200--1-MF1-200013', 'vic:rail:MAC', 2, 33, 'Operational delay',  '2026-04-21 11:50:13'),
(8,  '14-200--1-MF1-200014', '50088', 0, 15, 'Traffic congestion', '2026-04-19 18:08:13'),
(9,  '14-200--1-MF1-200014', '5163',  0, 24, 'Road construction',  '2026-04-22 21:22:13'),
(10, '14-200--1-MF1-200016', 'vic:rail:ELT', 2, 24, 'Road construction',  '2026-04-22 13:35:13');

INSERT INTO DELAY_LOG (TRIP_ID, STOP_ID, STOP_VT, DELAY_MINUTES, DELAY_REASON, REPORTED_TIME)
SELECT
    t.trip_id,
    (1002 + (gs % 25999))::varchar,
    (gs % 3),
    (1 + (random() * 44)::int),
    (ARRAY['Vehicle breakdown','Weather conditions','Signal problem','Traffic congestion',
           'Road construction','Operational delay','Passenger incident','Track obstruction'])[1 + (gs % 8)],
    CURRENT_TIMESTAMP - (random() * 90 * INTERVAL '1 day')
FROM generate_series(11, 270000) gs
JOIN LATERAL (SELECT trip_id FROM TRIP OFFSET (gs % 380000) LIMIT 1) t ON TRUE;


-- 21. CAPACITY_LOG 
INSERT INTO CAPACITY_LOG (CAPACITY_LOG_ID, TRIP_ID, VEHICLE_ID, STOP_ID, STOP_VT, PASSENGER_COUNT, RECORDED_TIME) VALUES
(1,  '14-200--1-MF1-200003', 'V1413', '12137',         0, 53,  '2026-04-21 20:40:29'),
(2,  '14-200--1-MF1-200003', 'V1413', '3229',          0, 50,  '2026-04-17 22:36:29'),
(3,  '14-200--1-MF1-200003', 'V1413', 'vic:rail:TOT',  2, 36,  '2026-04-21 12:21:29'),
(4,  '14-200--1-MF1-200003', 'V1413', '9130',          0, 98,  '2026-04-18 00:18:29'),
(5,  '14-200--1-MF1-200003', 'V1413', '1291',          1,  6,  '2026-04-18 23:39:29'),
(6,  '14-200--1-MF1-200003', 'V1413', '673',           0, 106, '2026-04-17 08:53:29'),
(7,  '14-200--1-MF1-200005', 'V1984', '21648',         0, 31,  '2026-04-22 02:27:29'),
(8,  '14-200--1-MF1-200005', 'V1984', '19059',         1, 40,  '2026-04-21 22:15:29'),
(9,  '14-200--1-MF1-200005', 'V1984', '51716',         0, 35,  '2026-04-18 16:43:29'),
(10, '14-200--1-MF1-200006', 'V1785', '12489',         0, 22,  '2026-04-19 12:44:29');

INSERT INTO CAPACITY_LOG (TRIP_ID, VEHICLE_ID, STOP_ID, STOP_VT, PASSENGER_COUNT, RECORDED_TIME)
SELECT
    t.trip_id,
    'V' || (1000 + (gs % 3500)),
    (1002 + (gs % 25999))::varchar,
    (gs % 3),
    (random() * 320)::int,
    CURRENT_TIMESTAMP - (random() * 180 * INTERVAL '1 day')
FROM generate_series(11, 1500000) gs
JOIN LATERAL (SELECT trip_id FROM TRIP OFFSET (gs % 380000) LIMIT 1) t ON TRUE;



-- 22. VEHICLE_LOG 
INSERT INTO VEHICLE_LOG (VEHICLE_LOG_ID, VEHICLE_ID, TRIP_ID, LATITUDE, LONGITUDE, SPEED_KMH, HEADING,
                         RECORDED_AT, OCCUPANCY_STATUS, CONGESTION_LEVEL,
                         CURRENT_STOP_ID, CURRENT_STOP_VT, NEXT_STOP_ID, NEXT_STOP_VT, DISTANCE_TO_NEXT_STOP_M) VALUES
(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),
(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),
(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),
(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),
(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),
(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),
(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),
(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),
(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),
(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);

INSERT INTO VEHICLE_LOG (VEHICLE_ID, TRIP_ID, LATITUDE, LONGITUDE, SPEED_KMH, HEADING, RECORDED_AT,
                         OCCUPANCY_STATUS, CONGESTION_LEVEL,
                         CURRENT_STOP_ID, CURRENT_STOP_VT, NEXT_STOP_ID, NEXT_STOP_VT, DISTANCE_TO_NEXT_STOP_M)
SELECT
    'V' || (1000 + (gs % 3500)),
    t.trip_id,
    (-37.5  - random() * 0.6)::decimal(10,7),
    (144.5  + random() * 0.9)::decimal(10,7),
    (5  + random() * 95)::decimal(6,2),
    (random() * 360)::decimal(5,2),
    CURRENT_TIMESTAMP - (random() * 180 * INTERVAL '1 day'),
    (ARRAY['EMPTY','MANY_SEATS_AVAILABLE','FEW_SEATS_AVAILABLE','STANDING_ROOM_ONLY',
           'CRUSHED_STANDING_ONLY','FULL','NOT_ACCEPTING_PASSENGERS'])[1 + (gs % 7)],
    (ARRAY['RUNNING_SMOOTHLY','RUNNING_SMOOTHLY','STOP_AND_GO','CONGESTION','SEVERE_CONGESTION'])[1 + (gs % 5)],
    CASE WHEN gs % 8 = 0 THEN NULL ELSE (1002 + (gs % 25999))::varchar END,
    CASE WHEN gs % 8 = 0 THEN NULL ELSE (gs % 3) END,
    CASE WHEN gs % 6 = 0 THEN NULL ELSE (1002 + ((gs+1) % 25999))::varchar END,
    CASE WHEN gs % 6 = 0 THEN NULL ELSE (gs % 3) END,
    CASE WHEN gs % 6 = 0 THEN NULL ELSE (10 + random() * 1990)::decimal(8,2) END
FROM generate_series(11, 5400000) gs
JOIN LATERAL (SELECT trip_id FROM TRIP OFFSET (gs % 380000) LIMIT 1) t ON TRUE;



-- 23. FUEL_LOG 
INSERT INTO FUEL_LOG (FUEL_LOG_ID, VEHICLE_ID, FUEL_AMOUNT, FUEL_COST, FUEL_DATE, MILEAGE) VALUES
(1,  'V0', 76.81,  168.11, '2015-06-02', 27985.45),
(2,  'V0', 160.65, 245.25, '2023-12-30', 30270.86),
(3,  'V0', 283.42, 582.85, '2018-04-17', 33125.57),
(4,  'V1', 148.32, 316.17, '2016-05-18',  4859.25),
(5,  'V1',  90.2,  147.27, '2017-03-12',  7524.74),
(6,  'V1', 118.51, 253.81, '2023-06-19',  8174.1),
(7,  'V1',  38.36,  58.89, '2017-05-11', 12533.86),
(8,  'V1', 202.57, 471.7,  '2020-12-14', 14747.98),
(9,  'V1', 144.21, 265.17, '2017-01-19', 15145.34),
(10, 'V1',  75.56, 184.46, '2015-04-02', 18418.25);

INSERT INTO FUEL_LOG (VEHICLE_ID, FUEL_AMOUNT, FUEL_COST, FUEL_DATE, MILEAGE)
SELECT
    'V' || (gs % 5000),
    (30 + random() * 300)::decimal(8,2),
    (50  + random() * 600)::decimal(10,2),
    CURRENT_DATE - (random() * 3650)::int,
    (1000 + random() * 299000)::decimal(10,2)
FROM generate_series(11, 22000) gs;



-- 24. DRIVER_SCHEDULE  
INSERT INTO DRIVER_SCHEDULE (DRIVER_SCHEDULE_ID, DRIVER_ID, TRIP_ID, SHIFT_ID, SHIFT_START, SHIFT_END, SCHEDULE_DATE) VALUES
(1,  'D8556',  '14-200--1-MF1-200003', 3, '2025-11-03 22:00:00', '2025-11-04 03:00:00', '2025-11-03'),
(2,  'D7108',  '14-200--1-MF1-200005', 1, '2025-11-15 06:00:00', '2025-11-15 14:00:00', '2025-11-15'),
(3,  'D9367',  '14-200--1-MF1-200006', 1, '2025-12-10 06:00:00', NULL,                  '2025-12-10'),
(4,  'D4139',  '14-200--1-MF1-200008', 3, '2025-12-14 22:00:00', '2025-12-15 00:00:00', '2025-12-14'),
(5,  'D10469', '14-200--1-MF1-200010', 3, '2026-02-19 22:00:00', '2026-02-19 23:00:00', '2026-02-19'),
(6,  'D1584',  '14-200--1-MF1-200012', 3, '2026-01-20 22:00:00', '2026-01-21 05:00:00', '2026-01-20'),
(7,  'D9934',  '14-200--1-MF1-200013', 2, '2025-12-26 14:00:00', '2025-12-26 22:00:00', '2025-12-26'),
(8,  'D3477',  '14-200--1-MF1-200014', 2, '2025-12-02 14:00:00', NULL,                  '2025-12-02'),
(9,  'D8719',  '14-200--1-MF1-200016', 1, '2026-04-22 06:00:00', '2026-04-22 14:00:00', '2026-04-22'),
(10, 'D5282',  '14-200--1-MF1-200017', 1, '2025-12-27 06:00:00', '2025-12-27 14:00:00', '2025-12-27');

INSERT INTO DRIVER (DRIVER_ID, EMPLOYEE_ID, LICENSE_NUMBER, LICENSE_CATEGORY, LICENSE_EXPIRY_DATE, YEARS_EXPERIENCE)
SELECT 'D' || gs, 'E' || LEAST(gs*2, 24999), 'YY' || lpad(gs::text,5,'0'), 'D', '2032-01-01', 10
FROM (VALUES (8556),(7108),(9367),(4139),(10469),(1584),(9934),(3477),(8719),(5282),(2267),(1572)) v(gs)
ON CONFLICT (DRIVER_ID) DO NOTHING;

INSERT INTO DRIVER_SCHEDULE (DRIVER_ID, TRIP_ID, SHIFT_ID, SHIFT_START, SHIFT_END, SCHEDULE_DATE)
SELECT
    'D' || (1000 + (gs % 3500)),
    t.trip_id,
    (1 + (gs % 3)),
    ts,
    CASE WHEN gs % 15 = 0 THEN NULL ELSE ts + INTERVAL '8 hours' END,
    ts::date
FROM generate_series(11, 387000) gs
JOIN LATERAL (SELECT trip_id FROM TRIP OFFSET (gs % 380000) LIMIT 1) t ON TRUE
CROSS JOIN LATERAL (SELECT (CURRENT_TIMESTAMP - (random() * 365 * INTERVAL '1 day'))::timestamp AS ts) dt;



-- 25. CONDUCTOR_SCHEDULE  
INSERT INTO CONDUCTOR_SCHEDULE (CONDUCTOR_SCHEDULE_ID, CONDUCTOR_ID, TRIP_ID, SHIFT_ID, SHIFT_START, SHIFT_END, SCHEDULE_DATE) VALUES
(1,  'CON2107', '14-200--1-MF1-200003', 3, '2026-04-19 22:00:00', '2026-04-20 03:00:00', '2026-04-19'),
(2,  'CON990',  '14-200--1-MF1-200005', 2, '2026-03-31 14:00:00', '2026-03-31 22:00:00', '2026-03-31'),
(3,  'CON2298', '14-200--1-MF1-200006', 3, NULL,                  '2025-12-12 06:00:00', '2025-12-12'),
(4,  'CON7608', '14-200--1-MF1-200008', 2, '2025-12-31 14:00:00', '2025-12-31 22:00:00', '2025-12-31'),
(5,  'CON3851', '14-200--1-MF1-200012', 2, '2026-02-19 14:00:00', '2026-02-19 22:00:00', '2026-02-19'),
(6,  'CON7366', '14-200--1-MF1-200014', 3, '2026-01-01 22:00:00', '2026-01-01 23:00:00', '2026-01-01'),
(7,  'CON4353', '14-200--1-MF1-200016', 3, '2026-01-17 22:00:00', NULL,                  '2026-01-17'),
(8,  'CON1526', '14-200--1-MF1-200018', 1, '2026-01-31 06:00:00', '2026-01-31 14:00:00', '2026-01-31'),
(9,  'CON7894', '14-200--1-MF1-200021', 3, '2025-12-19 22:00:00', '2025-12-20 01:00:00', '2025-12-19'),
(10, 'CON7693', '14-200--1-MF1-200024', 1, '2025-12-21 06:00:00', '2025-12-21 14:00:00', '2025-12-21');

INSERT INTO CONDUCTOR (CONDUCTOR_ID, EMPLOYEE_ID, CERTIFICATION_NUMBER, HIRE_ROLE_DATE)
SELECT 'CON' || gs, 'E' || LEAST(gs, 24999), 'CERTX' || gs, '2020-01-01'
FROM (VALUES (2107),(990),(2298),(7608),(3851),(7366),(4353),(1526),(7894),(7693)) v(gs)
ON CONFLICT (CONDUCTOR_ID) DO NOTHING;

INSERT INTO CONDUCTOR_SCHEDULE (CONDUCTOR_ID, TRIP_ID, SHIFT_ID, SHIFT_START, SHIFT_END, SCHEDULE_DATE)
SELECT
    'CON' || (gs % 10000),
    t.trip_id,
    (1 + (gs % 3)),
    ts,
    CASE WHEN gs % 15 = 0 THEN NULL ELSE ts + INTERVAL '8 hours' END,
    ts::date
FROM generate_series(11, 270000) gs
JOIN LATERAL (SELECT trip_id FROM TRIP OFFSET (gs % 380000) LIMIT 1) t ON TRUE
CROSS JOIN LATERAL (SELECT (CURRENT_TIMESTAMP - (random() * 365 * INTERVAL '1 day'))::timestamp AS ts) dt;



-- 26. PASSENGER 
INSERT INTO PASSENGER (PASSENGER_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE, DATE_OF_BIRTH, REGISTRATION_DATE, PASSENGER_TYPE, PASSWORD_HASH) VALUES
('PAS0',  'Scott',      'Chan',      'scottchan756@outlook.com',      '076776183', '1978-10-13', '2018-03-22', 'REGULAR', '04fbe0e221f561746d7fc3990b172f7b385f544465cc69e9af7ed6bd698b5014'),
('PAS1',  'Jose',       'Gutierrez', 'josegutierrez697@gmail.com',    '076658783', '1962-06-27', '2022-08-11', 'REGULAR', '43051ecceb7240657b6af82e6a0402edccccf043208e9a22a948255426fd8899'),
('PAS2',  'Alyssa',     'Salazar',   'alyssasalazar705@yahoo.com',    '077003380', '1981-06-11', '2018-02-13', 'REGULAR', 'b6efc4ae3bb8f40466c0e548e9f2195b41aa101991c11b53f83579a39a7b8249'),
('PAS3',  'Zachary',    'Gonzales',  'zacharygonzales125@outlook.com','077534837', '2003-06-09', '2026-04-14', 'STUDENT', '7bea27fe14d87a029f16c74c1ef006de9ee68ed6c846acd2806ce67bf3bc00cf'),
('PAS4',  'Susan',      'Holder',    'susanholder332@gmail.com',      '076698987', '1983-06-16', '2024-08-03', 'REGULAR', 'b21d7ef36c12d2f259551fe6c484838956e779c35746e4618d3f130e4ad0a4c4'),
('PAS5',  'Steven',     'James',     'stevenjames864@yahoo.com',      '075943433', '2000-12-22', '2020-01-06', 'STUDENT', '9e887e2f938fdac728c88987b54ee0edb407ecf56b3808263680965560308ab3'),
('PAS6',  'Amy',        'Davis',     'amydavis256@gmail.com',         '076953378', '1959-10-19', '2024-03-23', 'SENIOR',  '58eeded7128960a16590fe03740b09b642cd982fd14318194985f8096ba7f368'),
('PAS7',  'Lisa',       'Johnson',   'lisajohnson585@gmail.com',      '076417002', '1961-06-22', '2017-11-20', 'REGULAR', '403f0cdab3c18a724ebf0239f4ce81fbeffea98d149b80d88c30850feae8f48b'),
('PAS8',  'Jacqueline', 'Ortiz',     'jacquelineortiz447@outlook.com','077795132', '1949-08-26', '2023-08-16', 'SENIOR',  'ab6067a5343fb50fba77e15dadcfc712e00bc21580d8bf546cdf680a6d891a85'),
('PAS9',  'Susan',      'Lester',    'susanlester289@yahoo.com',      '075386749', '2008-09-01', '2022-10-29', 'STUDENT', '00bb0f291b59571d79f089fbc5b7563a210ef8dae95b61cbecf97a239c8ad937'),
('PAS10', 'Nicholas',   'Griffin',   'nicholasgriffin671@outlook.com','076487055', '1983-06-18', '2021-07-04', 'REGULAR', '8e90157da0c64569239707dfb38e4e4e435c56897ccc18c313d96a37686f2984');

INSERT INTO PASSENGER (PASSENGER_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE, DATE_OF_BIRTH,
                       REGISTRATION_DATE, PASSENGER_TYPE, PASSWORD_HASH)
SELECT
    'PAS' || gs,
    (ARRAY['Scott','Jose','Alyssa','Zachary','Susan','Steven','Amy','Lisa','Jacqueline','Nicholas',
           'Paul','Emily','Kim','Stephanie','Felicia','Jason','William','Jim','Timothy','Anthony',
           'Derek','Sarah','Michael','Jessica','Robert','Jennifer','David','Mary','James','Patricia'])[1 + (gs % 30)],
    (ARRAY['Chan','Gutierrez','Salazar','Gonzales','Holder','James','Davis','Johnson','Ortiz','Griffin',
           'Martin','Snyder','Cabrera','Flynn','Monroe','Silva','Odonnell','Cook','Fitzgerald','Jones',
           'Lynn','Smith','Williams','Brown','Taylor','Anderson','Thomas','Wilson','Moore','Harris'])[1 + (gs % 30)],
    lower(
        (ARRAY['scott','jose','alyssa','zachary','susan','steven','amy','lisa','jacqueline','nicholas',
               'paul','emily','kim','stephanie','felicia','jason','william','jim','timothy','anthony',
               'derek','sarah','michael','jessica','robert','jennifer','david','mary','james','patricia'])[1 + (gs % 30)]
        ||
        (ARRAY['chan','gutierrez','salazar','gonzales','holder','james','davis','johnson','ortiz','griffin',
               'martin','snyder','cabrera','flynn','monroe','silva','odonnell','cook','fitzgerald','jones',
               'lynn','smith','williams','brown','taylor','anderson','thomas','wilson','moore','harris'])[1 + (gs % 30)]
    ) || (gs % 999) || '@' || (ARRAY['gmail.com','yahoo.com','outlook.com'])[1 + (gs % 3)],
    '07' || lpad(((6000000 + gs * 11) % 4000000 + 6000000)::text, 7, '0'),
    DATE '1944-01-01' + (random() * 29200)::int,
    DATE '2015-01-01' + (random() * 4140)::int,
    (ARRAY['REGULAR','REGULAR','REGULAR','STUDENT','SENIOR','DISABLED','CHILD'])[1 + (gs % 7)],
    md5('pass' || gs)
FROM generate_series(11, 4999999) gs
ON CONFLICT (PASSENGER_ID) DO NOTHING;



-- 27. TICKET_TYPE  
INSERT INTO TICKET_TYPE (TICKET_TYPE_ID, NAME, DESCRIPTION, VALIDITY_DURATION, BASE_PRICE) VALUES
(1, 'Single Ticket',   'One-way single trip ticket',       60,     4.6),
(2, 'Daily Pass',      'Unlimited travel for one day',     1440,   10.6),
(3, 'Weekly Pass',     'Unlimited travel for one week',    10080,  53.0),
(4, 'Monthly Pass',    'Unlimited travel for one month',   43200,  206.0),
(5, 'Annual Pass',     'Unlimited travel for one year',    525600, 2116.0),
(6, 'Student Monthly', 'Monthly pass for students',        43200,  103.0);



-- 28. FARE_RULE
INSERT INTO FARE_RULE (FARE_RULE_ID, ZONE_ID, TICKET_TYPE_ID, AGENCY_ID, ROUTE_ID, PRICE, VALID_FROM, VALID_TO) VALUES
(1,  'Z01', 1, 1, NULL,            2.5,  '2025-08-17', NULL),
(2,  'Z01', 1, 2, NULL,            0.5,  '2026-01-13', '2026-04-21'),
(3,  'Z01', 1, 2, '65-L05-aus-1',  8.5,  '2025-11-06', '2026-01-11'),
(4,  'Z01', 1, 2, '19-682-aus-1',  8.0,  '2024-12-26', '2025-10-09'),
(5,  'Z01', 1, 3, '43-542-aus-1',  7.0,  '2025-06-09', '2026-04-21'),
(6,  'Z01', 1, 3, NULL,            5.5,  '2024-11-27', '2025-05-18'),
(7,  'Z01', 1, 3, NULL,            9.5,  '2024-02-16', NULL),
(8,  'Z01', 1, 4, NULL,            0.0,  '2025-12-15', '2026-04-21'),
(9,  'Z01', 1, 4, '26-850-aus-1', 13.0,  '2025-05-04', '2025-12-09'),
(10, 'Z01', 1, 5, NULL,            8.5,  '2026-02-20', '2026-04-21');

INSERT INTO FARE_RULE (ZONE_ID, TICKET_TYPE_ID, AGENCY_ID, ROUTE_ID, PRICE, VALID_FROM, VALID_TO)
SELECT
    'Z' || lpad((1 + (gs % 20))::text, 2, '0'),
    (1 + (gs % 6)),
    (1 + (gs % 5)),
    CASE WHEN gs % 4 = 0 THEN NULL ELSE r.route_id END,
    (0.5 + random() * 13.5)::decimal(8,2),
    CURRENT_DATE - (random() * 730)::int,
    CASE WHEN gs % 10 = 0 THEN NULL ELSE CURRENT_DATE + (random() * 730)::int END
FROM generate_series(11, 1200) gs
JOIN LATERAL (SELECT route_id FROM ROUTE ORDER BY random() LIMIT 1) r ON TRUE;



-- 29. TICKET
INSERT INTO TICKET (TICKET_ID, PASSENGER_ID, TRIP_ID, TICKET_TYPE_ID, FARE_RULE_ID, DISCOUNT_ID,
                    START_STOP_ID, START_STOP_VT, END_STOP_ID, END_STOP_VT,
                    PURCHASE_DATE, PRICE, STATUS) VALUES
('TKT0000001', 'PAS4698017', '20-767--1-MF1-87',           2, 1048, NULL, NULL,           NULL, '51560', 0, '2025-10-09', 12.0,  'USED'),
('TKT0000002', 'PAS2500611', '03-5--5-T3-140606642',        1, 1091, NULL, NULL,           NULL, '13645', 0, '2026-01-03',  8.5,  'EXPIRED'),
('TKT0000003', 'PAS3658516', '40-683--1-MF3-87894110',      4,  899, NULL, 'vic:rail:UNN_LI3', 2, '11296', 0, '2025-11-10', 0,   'REFUNDED'),
('TKT0000004', 'PAS3796241', '32-604--1-Sat6-604452',       2, 1107, NULL, '40231',        0,   '22539', 0, '2026-04-15',  6.0,  'USED'),
('TKT0000005', 'PAS3553431', '53-420--1-Sun10-70503119',    3,  279, NULL, '13585',        0,   NULL,   NULL,'2026-02-19', 0,   'REFUNDED'),
('TKT0000006', 'PAS4024121', '60-194--1-MF10-4419432',      2,  517, NULL, '22920',        0,   '8668',  0, '2026-03-06', 14.0,  'USED'),
('TKT0000007', 'PAS2986472', '02-BEG--37-T5-3120',          3,  771, NULL, '1151',         0,   '43420', 0, '2025-11-21',  4.5,  'USED'),
('TKT0000008', 'PAS3194202', '58-G01--1-MF3-35049314',      2, 1099,   6,  '13542',        0,   '28481', 0, '2025-11-23', 10.8,  'VALID'),
('TKT0000009', 'PAS1589736', '60-494--1-Sat4-8070560',      1,  923, NULL, '7979',         0,   '15430', 0, '2025-06-03',  4.5,  'USED'),
('TKT0000010', 'PAS4231578', '42-357--1-MF4-115',           2,  197, NULL, '3164',         0,   '46339', 0, '2026-03-15', 10.5,  'VALID');

INSERT INTO TICKET (TICKET_ID, PASSENGER_ID, TRIP_ID, TICKET_TYPE_ID, FARE_RULE_ID, DISCOUNT_ID,
                    START_STOP_ID, START_STOP_VT, END_STOP_ID, END_STOP_VT,
                    PURCHASE_DATE, PRICE, STATUS)
SELECT
    'TKT' || lpad(gs::text, 7, '0'),
    'PAS' || (gs % 5000000),
    t.trip_id,
    (1 + (gs % 6)),
    CASE WHEN gs % 12 = 0 THEN NULL ELSE (1 + (gs % 1200)) END,
    CASE WHEN gs % 8  = 0 THEN NULL ELSE (1 + (gs % 10))  END,
    CASE WHEN gs % 5 = 0 THEN NULL ELSE (1002 + (gs % 25999))::varchar END,
    CASE WHEN gs % 5 = 0 THEN NULL ELSE (gs % 3) END,
    CASE WHEN gs % 4 = 0 THEN NULL ELSE (1002 + ((gs+1) % 25999))::varchar END,
    CASE WHEN gs % 4 = 0 THEN NULL ELSE (gs % 3) END,
    DATE '2024-01-01' + (random() * 480)::int,
    (0.0 + random() * 15.0)::decimal(8,2),
    (ARRAY['USED','USED','USED','VALID','EXPIRED','CANCELLED','REFUNDED'])[1 + (gs % 7)]
FROM generate_series(11, 10000000) gs
JOIN LATERAL (SELECT trip_id FROM TRIP OFFSET (gs % 380000) LIMIT 1) t ON TRUE
ON CONFLICT (TICKET_ID) DO NOTHING;



-- 30. PAYMENT 
INSERT INTO PAYMENT (PAYMENT_ID, TICKET_ID, PASSENGER_ID, AMOUNT, PAYMENT_METHOD, PAYMENT_DATE, PAYMENT_STATUS, TRANSACTION_REFERENCE) VALUES
('PAY0000001', 'TKT0000003', 'PAS803371',  3.5,  'CASH',        '2025-08-31 12:21:05.445861', 'COMPLETED', NULL),
('PAY0000002', 'TKT0000004', 'PAS3506098', 7.0,  'ONLINE',      '2026-03-05 04:00:05.446358', 'COMPLETED', 'TXN176855850'),
('PAY0000003', 'TKT0000005', 'PAS3672677', 5.47, 'MOBILE',      '2026-02-10 20:20:05.446389', 'COMPLETED', 'TXN454882949'),
('PAY0000004', 'TKT0000007', 'PAS1170886', 3.08, 'CARD',        '2026-03-22 08:28:05.446409', 'COMPLETED', 'TXN787784851'),
('PAY0000005', 'TKT0000008', 'PAS982494',  0.7,  'MOBILE',      '2025-10-05 09:45:05.446423', 'COMPLETED', 'TXN127707823'),
('PAY0000006', 'TKT0000009', 'PAS3862170', 0.6,  'CASH',        '2025-06-03 21:58:05.446436', 'REFUNDED',  NULL),
('PAY0000007', 'TKT0000010', 'PAS2305744', 0.5,  'CONTACTLESS', '2025-06-02 07:28:05.446448', 'CANCELLED', 'TXN687895258'),
('PAY0000008', 'TKT0000011', 'PAS13699',   4.0,  'CASH',        '2025-06-13 22:55:05.446458', 'COMPLETED', NULL),
('PAY0000009', 'TKT0000012', 'PAS1356000', 5.75, 'CONTACTLESS', '2025-06-02 12:32:05.446467', 'COMPLETED', 'TXN463888139'),
('PAY0000010', 'TKT0000013', 'PAS2908092', 4.55, 'CASH',        '2026-01-20 10:26:05.446477', 'CANCELLED', NULL);

INSERT INTO PAYMENT (PAYMENT_ID, TICKET_ID, PASSENGER_ID, AMOUNT, PAYMENT_METHOD, PAYMENT_DATE, PAYMENT_STATUS, TRANSACTION_REFERENCE)
SELECT
    'PAY' || lpad(gs::text, 7, '0'),
    'TKT' || lpad(gs::text, 7, '0'),
    'PAS' || (gs % 5000000),
    (0.5 + random() * 14.5)::decimal(10,2),
    (ARRAY['CASH','CARD','CONTACTLESS','MOBILE','ONLINE','VOUCHER'])[1 + (gs % 6)],
    CURRENT_TIMESTAMP - (random() * 480 * INTERVAL '1 day'),
    (ARRAY['COMPLETED','COMPLETED','COMPLETED','COMPLETED','FAILED','REFUNDED','CANCELLED'])[1 + (gs % 7)],
    CASE
        WHEN (gs % 6) = 0 THEN NULL
        WHEN (gs % 7) IN (5,6) THEN NULL
        ELSE 'TXN' || lpad((100000000 + gs * 97 % 900000000)::text, 9, '0')
    END
FROM generate_series(11, 9000000) gs
ON CONFLICT (PAYMENT_ID) DO NOTHING;



-- 31. SUBSCRIPTION_PASS 
INSERT INTO SUBSCRIPTION_PASS (PASS_ID, PASSENGER_ID, TICKET_TYPE_ID, ZONE_ID, PASS_TYPE, START_DATE, END_DATE, PRICE, STATUS) VALUES
('PASS1',  'PAS4789651', 1, 'Z01', 'STUDENT', '2023-12-26', '2024-01-25',  585.8,   'ACTIVE'),
('PASS2',  'PAS1860306', 2, NULL,  'DAILY',   '2020-05-31', '2020-06-01',  795.24,  'EXPIRED'),
('PASS3',  'PAS727459',  2, 'Z07', 'WEEKLY',  '2021-11-23', '2021-11-30', 1126.1,   'EXPIRED'),
('PASS4',  'PAS3632155', 2, 'Z15', 'WEEKLY',  '2021-02-07', '2021-02-14',  229.39,  'ACTIVE'),
('PASS5',  'PAS4514184', 5, 'Z14', 'STUDENT', '2020-04-09', '2020-05-09', 1095.07,  'ACTIVE'),
('PASS6',  'PAS3317589', 5, NULL,  'DAILY',   '2019-07-15', '2019-07-16',  867.41,  'ACTIVE'),
('PASS7',  'PAS4613663', 4, 'Z15', 'ANNUAL',  '2020-12-21', '2021-12-21',  605.45,  'ACTIVE'),
('PASS8',  'PAS717158',  3, NULL,  'STUDENT', '2024-09-19', '2024-10-19',  868.83,  'EXPIRED'),
('PASS9',  'PAS3878203', 2, 'Z20', 'STUDENT', '2021-09-13', '2021-10-13',  457.93,  'ACTIVE'),
('PASS10', 'PAS1157157', 5, 'Z13', 'MONTHLY', '2019-02-08', '2019-03-10',  116.55,  'ACTIVE');

INSERT INTO SUBSCRIPTION_PASS (PASS_ID, PASSENGER_ID, TICKET_TYPE_ID, ZONE_ID, PASS_TYPE, START_DATE, END_DATE, PRICE, STATUS)
SELECT
    'PASS' || gs,
    'PAS' || (gs % 5000000),
    (1 + (gs % 6)),
    CASE WHEN gs % 5 = 0 THEN NULL ELSE 'Z' || lpad((1 + (gs % 20))::text, 2, '0') END,
    (ARRAY['DAILY','WEEKLY','MONTHLY','MONTHLY','MONTHLY','ANNUAL','STUDENT','SENIOR'])[1 + (gs % 8)],
    DATE '2019-01-01' + (random() * 2000)::int,
    DATE '2019-01-01' + (random() * 2000)::int + (random() * 365)::int,
    (100 + random() * 2100)::decimal(8,2),
    (ARRAY['ACTIVE','ACTIVE','ACTIVE','EXPIRED','SUSPENDED','CANCELLED'])[1 + (gs % 6)]
FROM generate_series(11, 300000) gs
ON CONFLICT (PASS_ID) DO NOTHING;



-- 32. NOTIFICATION
INSERT INTO NOTIFICATION (NOTIFICATION_ID, PASSENGER_ID, TRIP_ID, DELAY_LOG_ID, MESSAGE, NOTIFICATION_TYPE, SENT_TIME, STATUS) VALUES
(1,  'PAS4414736', '60-160--1-MF1-415210',       NULL,   'Reminder: Your subscription pass expires soon.',               'REMINDER', '2025-09-04 13:01:32.639938', 'SENT'),
(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'),
(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'),
(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'),
(5,  'PAS3400523', NULL,                          NULL,   'Special offer! Get 20% off your next monthly pass.',           'PROMO',    '2025-06-30 11:12:32.640064', 'PENDING'),
(6,  'PAS232767',  '41-800--1-MF2-145',          NULL,   'Important update regarding your transit service.',             'GENERAL',  '2025-05-13 01:42:32.640078', 'READ'),
(7,  'PAS830139',  NULL,                          NULL,   'Service alert: Route disruption reported on your usual line.', 'ALERT',    '2025-10-30 23:37:32.640088', 'READ'),
(8,  'PAS2551180', NULL,                          NULL,   'Special offer! Get 20% off your next monthly pass.',           'PROMO',    '2025-08-19 06:54:32.640099', 'READ'),
(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'),
(10, 'PAS700959',  NULL,                          NULL,   'Important update regarding your transit service.',             'GENERAL',  '2025-10-28 09:47:32.640123', 'SENT');

INSERT INTO NOTIFICATION (PASSENGER_ID, TRIP_ID, DELAY_LOG_ID, MESSAGE, NOTIFICATION_TYPE, SENT_TIME, STATUS)
SELECT
    'PAS' || (gs % 5000000),
    CASE WHEN gs % 4 = 0 THEN NULL ELSE t.trip_id END,
    CASE WHEN gs % 7 = 0 THEN (1 + (gs % 270000)) ELSE NULL END,
    (ARRAY[
        'Reminder: Your subscription pass expires soon.',
        'Special offer! Get 20% off your next monthly pass.',
        'Service alert: Route disruption reported on your usual line.',
        'Your trip is experiencing delays. We apologize for the inconvenience.',
        'Important update regarding your transit service.',
        'Your stop is approaching. Please prepare to alight.'
    ])[1 + (gs % 6)],
    (ARRAY['REMINDER','PROMO','ALERT','DELAY','GENERAL','GENERAL'])[1 + (gs % 6)],
    CURRENT_TIMESTAMP - (random() * 365 * INTERVAL '1 day'),
    (ARRAY['SENT','SENT','SENT','READ','READ','FAILED','PENDING'])[1 + (gs % 7)]
FROM generate_series(11, 10000000) gs
JOIN LATERAL (SELECT trip_id FROM TRIP OFFSET (gs % 380000) LIMIT 1) t ON TRUE;



-- 33. PASSENGER_DISCOUNT 
INSERT INTO PASSENGER_DISCOUNT (PASSENGER_DISCOUNT_ID, PASSENGER_ID, DISCOUNT_ID, OFFICER_ID, ASSIGNED_DATE, EXPIRY_DATE, STATUS) VALUES
(1,  'PAS1',  9,  'OFF951',  '2021-03-27', '2022-11-20', 'EXPIRED'),
(2,  'PAS4',  10, 'OFF39',   '2019-02-04', '2020-08-31', 'ACTIVE'),
(3,  'PAS5',  6,  'OFF1084', '2022-01-16', '2024-07-27', 'ACTIVE'),
(4,  'PAS9',  6,  'OFF1025', '2021-04-02', '2024-03-27', 'EXPIRED'),
(5,  'PAS10', 7,  'OFF2295', '2016-11-19', '2018-10-01', 'ACTIVE'),
(6,  'PAS14', 7,  'OFF1519', '2022-07-23', '2024-05-29', 'ACTIVE'),
(7,  'PAS16', 4,  'OFF2476', '2015-01-23', '2015-11-18', 'REVOKED'),
(8,  'PAS19', 10, 'OFF1857', '2020-07-15', '2020-12-13', 'ACTIVE'),
(9,  'PAS20', 8,  'OFF2316', '2015-09-02', '2017-08-09', 'EXPIRED'),
(10, 'PAS22', 8,  'OFF2018', '2015-08-20', '2015-10-01', 'REVOKED');

INSERT INTO DISCOUNT_OFFICER (OFFICER_ID, EMPLOYEE_ID, ASSIGNED_REGION, CERTIFICATION_CODE, AUTHORIZED_FROM, AUTHORIZED_TO)
SELECT 'OFF' || gs, 'E' || LEAST(gs * 4 + 2, 24999), 'General', 'OFF-CERT-' || gs, '2015-01-01', '2030-01-01'
FROM (VALUES (951),(39),(1084),(1025),(2295),(1519),(2476),(1857),(2316),(2018)) v(gs)
ON CONFLICT (OFFICER_ID) DO NOTHING;

INSERT INTO PASSENGER_DISCOUNT (PASSENGER_ID, DISCOUNT_ID, OFFICER_ID, ASSIGNED_DATE, EXPIRY_DATE, STATUS)
SELECT
    'PAS' || gs,
    (1 + (gs % 10)),
    CASE WHEN gs % 8 = 0 THEN NULL ELSE 'OFF' || (gs % 2500) END,
    DATE '2015-01-01' + (random() * 3000)::int,
    DATE '2015-01-01' + (random() * 3000)::int + (random() * 730)::int,
    (ARRAY['ACTIVE','ACTIVE','ACTIVE','EXPIRED','REVOKED','PENDING'])[1 + (gs % 6)]
FROM generate_series(11, 3499999) gs;



-- 34. COMPLAINT 
INSERT INTO COMPLAINT (COMPLAINT_ID, PASSENGER_ID, TRIP_ID, EMPLOYEE_ID, COMPLAINT_TEXT, COMPLAINT_DATE, STATUS, RESOLUTION_NOTES) VALUES
(1,  'PAS12260',   '14-281--1-MF4-281110',      'E10894', 'Driver was rude and unprofessional.',                         '2025-11-30', 'CLOSED',      'Passenger has been contacted and issue resolved.'),
(2,  'PAS2872226', '02-CGB--1-US-5898',          'E5609',  'Driver was using phone while driving.',                      '2026-01-21', 'RESOLVED',    'Compensation issued to the passenger.'),
(3,  'PAS2763066', '03-12--6-UT-140603056',       'E7557',  'Driver skipped my stop without stopping.',                  '2026-03-10', 'RESOLVED',    'No action required - complaint deemed invalid.'),
(4,  'PAS2200199', '02-CBE--1-UG-C519',           'E9260',  'Driver skipped my stop without stopping.',                  '2025-09-18', 'REJECTED',    'Driver has been notified and warned.'),
(5,  'PAS2200199', '02-WIL--36-T5-6373',          'E5077',  'Driver was rude and unprofessional.',                       '2025-12-24', 'CLOSED',      'Maintenance team has been informed.'),
(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.'),
(7,  'PAS4443919', '03-78--12-T3-136470740',      'E20499', 'Trip was delayed by more than 30 minutes without notice.',  '2026-02-24', 'IN_PROGRESS', NULL),
(8,  'PAS3239988', '42-555--1-MF1-163',           NULL,     'Driver was using phone while driving.',                     '2025-12-09', 'CLOSED',      'No action required - complaint deemed invalid.'),
(9,  'PAS1397715', '14-270--1-MF4-270880',        NULL,     'Driver skipped my stop without stopping.',                  '2026-03-14', 'REJECTED',    'Driver has been notified and warned.'),
(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.');

INSERT INTO COMPLAINT (PASSENGER_ID, TRIP_ID, EMPLOYEE_ID, COMPLAINT_TEXT, COMPLAINT_DATE, STATUS, RESOLUTION_NOTES)
SELECT
    'PAS' || (gs % 5000000),
    t.trip_id,
    CASE WHEN gs % 5 = 0 THEN NULL ELSE 'E' || (gs % 25000) END,
    (ARRAY[
        'Driver was rude and unprofessional.',
        'Driver was using phone while driving.',
        'Driver skipped my stop without stopping.',
        'Trip was delayed by more than 30 minutes without notice.',
        'Air conditioning was not working during the trip.',
        'Vehicle was overcrowded and uncomfortable.',
        'Ticket machine was out of service.',
        'Stop announcement system was not functioning.'
    ])[1 + (gs % 8)],
    CURRENT_DATE - (random() * 365)::int,
    (ARRAY['OPEN','OPEN','IN_PROGRESS','RESOLVED','CLOSED','REJECTED'])[1 + (gs % 6)],
    CASE
        WHEN gs % 6 IN (0,1) THEN NULL
        WHEN gs % 3 = 0 THEN 'Passenger has been contacted and issue resolved.'
        WHEN gs % 3 = 1 THEN 'Compensation issued to the passenger.'
        ELSE 'No action required - complaint deemed invalid.'
    END
FROM generate_series(11, 300000) gs
JOIN LATERAL (SELECT trip_id FROM TRIP OFFSET (gs % 380000) LIMIT 1) t ON TRUE;