| 1 | set search_path = project;
|
|---|
| 2 |
|
|---|
| 3 | INSERT INTO Korisnik (is_admin, adresa, email, ime, lozinka, telefon) VALUES
|
|---|
| 4 | (true, 'Bulevar Partizanski Odredi 45, Skopje', 'admin@example.com', 'Nikola Stojanovski', 'admin_password', '070123456'),
|
|---|
| 5 | (false, 'Ulica Makedonija 12, Skopje', 'user1@example.com', 'Elena Petrovska', 'user1_password', '071234567'),
|
|---|
| 6 | (false, 'Bulevar Sveti Kliment Ohridski 23, Skopje', 'user2@example.com', 'Marija Trajkovska', 'user2_password', '072345678'),
|
|---|
| 7 | (false, 'Ulica Vasil Glavinov 10, Skopje', 'user3@example.com', 'Ivana Risteska', 'user3_password', '073456789'),
|
|---|
| 8 | (false, 'Bulevar Krste Misirkov 56, Skopje', 'user4@example.com', 'Stefan Dimitrovski', 'user4_password', '074567890');
|
|---|
| 9 | INSERT INTO Role (description, name) VALUES
|
|---|
| 10 | ('Administrator with full access rights', 'ROLE_ADMIN'),
|
|---|
| 11 | ('Driver responsible for operating the bus', 'ROLE_DRIVER'),
|
|---|
| 12 | ('Conductor responsible for checking tickets', 'ROLE_CONDUCTOR'),
|
|---|
| 13 | ('Commuter using the public transport services', 'ROLE_PASSENGER');
|
|---|
| 14 | -- Nikola Stojanovski (Admin)
|
|---|
| 15 | INSERT INTO Korisnik_Role (korisnik_id, role_id) VALUES
|
|---|
| 16 | (1, 2),
|
|---|
| 17 | (1, 3),
|
|---|
| 18 | (1, 4),
|
|---|
| 19 | (1, 1); -- Admin role
|
|---|
| 20 |
|
|---|
| 21 | -- Elena Petrovska (Commuter)
|
|---|
| 22 | INSERT INTO Korisnik_Role (korisnik_id, role_id) VALUES
|
|---|
| 23 | (2, 4); -- Commuter role
|
|---|
| 24 |
|
|---|
| 25 | -- Marija Trajkovska (Driver)
|
|---|
| 26 | INSERT INTO Korisnik_Role (korisnik_id, role_id) VALUES
|
|---|
| 27 | (3, 2); -- Driver role
|
|---|
| 28 |
|
|---|
| 29 | -- Ivana Risteska (Conductor)
|
|---|
| 30 | INSERT INTO Korisnik_Role (korisnik_id, role_id) VALUES
|
|---|
| 31 | (4, 3); -- Conductor role
|
|---|
| 32 |
|
|---|
| 33 | -- Stefan Dimitrovski (Commuter)
|
|---|
| 34 | INSERT INTO Korisnik_Role (korisnik_id, role_id) VALUES
|
|---|
| 35 | (5, 4); -- Commuter role
|
|---|
| 36 |
|
|---|
| 37 | -- Elena Petrovska (Commuter)
|
|---|
| 38 | INSERT INTO Patnik (korisnik_id) VALUES
|
|---|
| 39 | (2);
|
|---|
| 40 |
|
|---|
| 41 | -- Stefan Dimitrovski (Commuter)
|
|---|
| 42 | INSERT INTO Patnik (korisnik_id) VALUES
|
|---|
| 43 | (5);
|
|---|
| 44 |
|
|---|
| 45 | -- Nikola Stojanovski (Admin)
|
|---|
| 46 | INSERT INTO Patnik (korisnik_id) VALUES
|
|---|
| 47 | (1); -- Assuming Nikola Stojanovski has vraboten_id 1
|
|---|
| 48 |
|
|---|
| 49 | -- Nikola Stojanovski (Admin)
|
|---|
| 50 | INSERT INTO Vraboten (datum_na_vrabotuvanje, datum_prekin_vrabotuvanje, plata, korisnik_id) VALUES
|
|---|
| 51 | ('2020-01-01', NULL, 60000, 1);
|
|---|
| 52 |
|
|---|
| 53 | -- Marija Trajkovska (Driver)
|
|---|
| 54 | INSERT INTO Vraboten (datum_na_vrabotuvanje, datum_prekin_vrabotuvanje, plata, korisnik_id) VALUES
|
|---|
| 55 | ('2021-05-15', NULL, 35000, 3);
|
|---|
| 56 |
|
|---|
| 57 | -- Ivana Risteska (Conductor)
|
|---|
| 58 | INSERT INTO Vraboten (datum_na_vrabotuvanje, datum_prekin_vrabotuvanje, plata, korisnik_id) VALUES
|
|---|
| 59 | ('2022-03-20', NULL, 30000, 4);
|
|---|
| 60 |
|
|---|
| 61 | -- Marija Trajkovska (Driver)
|
|---|
| 62 | INSERT INTO Vozac (vraboten_id) VALUES
|
|---|
| 63 | (2); -- Assuming Marija Trajkovska has vraboten_id 2
|
|---|
| 64 |
|
|---|
| 65 | -- Nikola Stojanovski (Admin)
|
|---|
| 66 | INSERT INTO Vozac (vraboten_id) VALUES
|
|---|
| 67 | (1); -- Assuming Nikola Stojanovski has vraboten_id 1
|
|---|
| 68 |
|
|---|
| 69 | -- Insert employees into the Kondukter table
|
|---|
| 70 |
|
|---|
| 71 | -- Ivana Risteska (Conductor)
|
|---|
| 72 | INSERT INTO Kondukter (vraboten_id) VALUES
|
|---|
| 73 | (3); -- Assuming Ivana Risteska has vraboten_id 3
|
|---|
| 74 |
|
|---|
| 75 | -- Nikola Stojanovski (Admin)
|
|---|
| 76 | INSERT INTO Kondukter (vraboten_id) VALUES
|
|---|
| 77 | (1); -- Assuming Nikola Stojanovski has vraboten_id 1
|
|---|
| 78 |
|
|---|
| 79 | INSERT INTO Avtobus (broj_sedishta, registracija, seriski_broj) VALUES
|
|---|
| 80 | (50, 'SK-1234-AB', 'SN-000001'),
|
|---|
| 81 | (45, 'SK-2345-BC', 'SN-000002'),
|
|---|
| 82 | (50, 'SK-3456-CD', 'SN-000003'),
|
|---|
| 83 | (40, 'SK-4567-DE', 'SN-000004'),
|
|---|
| 84 | (50, 'SK-5678-EF', 'SN-000005'),
|
|---|
| 85 | (55, 'SK-6789-FG', 'SN-000006'),
|
|---|
| 86 | (50, 'SK-7890-GH', 'SN-000007'),
|
|---|
| 87 | (45, 'SK-8901-HI', 'SN-000008'),
|
|---|
| 88 | (50, 'SK-9012-IJ', 'SN-000009'),
|
|---|
| 89 | (40, 'SK-0123-JK', 'SN-000010'),
|
|---|
| 90 | (50, 'SK-1234-KL', 'SN-000011'),
|
|---|
| 91 | (55, 'SK-2345-LM', 'SN-000012'),
|
|---|
| 92 | (50, 'SK-3456-MN', 'SN-000013'),
|
|---|
| 93 | (45, 'SK-4567-NO', 'SN-000014'),
|
|---|
| 94 | (50, 'SK-5678-OP', 'SN-000015'),
|
|---|
| 95 | (40, 'SK-6789-PQ', 'SN-000016'),
|
|---|
| 96 | (50, 'SK-7890-QR', 'SN-000017'),
|
|---|
| 97 | (55, 'SK-8901-RS', 'SN-000018'),
|
|---|
| 98 | (50, 'SK-9012-ST', 'SN-000019'),
|
|---|
| 99 | (45, 'SK-0123-TU', 'SN-000020');
|
|---|
| 100 |
|
|---|
| 101 | INSERT INTO Linija (ime) VALUES
|
|---|
| 102 | ('Line 1'),
|
|---|
| 103 | ('Line 2'),
|
|---|
| 104 | ('Line 3'),
|
|---|
| 105 | ('Line 4'),
|
|---|
| 106 | ('Line 5'),
|
|---|
| 107 | ('Line 6'),
|
|---|
| 108 | ('Line 7'),
|
|---|
| 109 | ('Line 8'),
|
|---|
| 110 | ('Line 9'),
|
|---|
| 111 | ('Line 10');
|
|---|
| 112 |
|
|---|
| 113 | INSERT INTO Pravec (pravec, opis) VALUES
|
|---|
| 114 | ('Skopje - Tetovo', 'Skopje to Tetovo'),
|
|---|
| 115 | ('Tetovo - Skopje', 'Tetovo to Skopje'),
|
|---|
| 116 | ('Skopje - Ohrid', 'Skopje to Ohrid'),
|
|---|
| 117 | ('Ohrid - Skopje', 'Ohrid to Skopje'),
|
|---|
| 118 | ('Skopje - Bitola', 'Skopje to Bitola'),
|
|---|
| 119 | ('Bitola - Skopje', 'Bitola to Skopje'),
|
|---|
| 120 | ('Skopje - Kumanovo', 'Skopje to Kumanovo'),
|
|---|
| 121 | ('Kumanovo - Skopje', 'Kumanovo to Skopje'),
|
|---|
| 122 | ('Skopje - Strumica', 'Skopje to Strumica'),
|
|---|
| 123 | ('Strumica - Skopje', 'Strumica to Skopje'),
|
|---|
| 124 | ('Skopje - Veles', 'Skopje to Veles'),
|
|---|
| 125 | ('Veles - Skopje', 'Veles to Skopje'),
|
|---|
| 126 | ('Skopje - Prilep', 'Skopje to Prilep'),
|
|---|
| 127 | ('Prilep - Skopje', 'Prilep to Skopje'),
|
|---|
| 128 | ('Skopje - Gostivar', 'Skopje to Gostivar'),
|
|---|
| 129 | ('Gostivar - Skopje', 'Gostivar to Skopje'),
|
|---|
| 130 | ('Skopje - Stip', 'Skopje to Stip'),
|
|---|
| 131 | ('Stip - Skopje', 'Stip to Skopje'),
|
|---|
| 132 | ('Skopje - Kavadarci', 'Skopje to Kavadarci'),
|
|---|
| 133 | ('Kavadarci - Skopje', 'Kavadarci to Skopje');
|
|---|
| 134 |
|
|---|
| 135 | -- Line 1: Skopje - Tetovo
|
|---|
| 136 | INSERT INTO linija_pravec (pravec_id, linija_id) VALUES
|
|---|
| 137 | (1, 1), -- Skopje to Tetovo
|
|---|
| 138 | (2, 1); -- Tetovo to Skopje
|
|---|
| 139 |
|
|---|
| 140 | -- Line 2: Skopje - Ohrid
|
|---|
| 141 | INSERT INTO linija_pravec (pravec_id, linija_id) VALUES
|
|---|
| 142 | (3, 2), -- Skopje to Ohrid
|
|---|
| 143 | (4, 2); -- Ohrid to Skopje
|
|---|
| 144 |
|
|---|
| 145 | -- Line 3: Skopje - Bitola
|
|---|
| 146 | INSERT INTO linija_pravec (pravec_id, linija_id) VALUES
|
|---|
| 147 | (5, 3), -- Skopje to Bitola
|
|---|
| 148 | (6, 3); -- Bitola to Skopje
|
|---|
| 149 |
|
|---|
| 150 | -- Line 4: Skopje - Kumanovo
|
|---|
| 151 | INSERT INTO linija_pravec (pravec_id, linija_id) VALUES
|
|---|
| 152 | (7, 4), -- Skopje to Kumanovo
|
|---|
| 153 | (8, 4); -- Kumanovo to Skopje
|
|---|
| 154 |
|
|---|
| 155 | -- Line 5: Skopje - Strumica
|
|---|
| 156 | INSERT INTO linija_pravec (pravec_id, linija_id) VALUES
|
|---|
| 157 | (9, 5), -- Skopje to Strumica
|
|---|
| 158 | (10, 5); -- Strumica to Skopje
|
|---|
| 159 |
|
|---|
| 160 | -- Line 6: Skopje - Veles
|
|---|
| 161 | INSERT INTO linija_pravec (pravec_id, linija_id) VALUES
|
|---|
| 162 | (11, 6), -- Skopje to Veles
|
|---|
| 163 | (12, 6); -- Veles to Skopje
|
|---|
| 164 |
|
|---|
| 165 | -- Line 7: Skopje - Prilep
|
|---|
| 166 | INSERT INTO linija_pravec (pravec_id, linija_id) VALUES
|
|---|
| 167 | (13, 7), -- Skopje to Prilep
|
|---|
| 168 | (14, 7); -- Prilep to Skopje
|
|---|
| 169 |
|
|---|
| 170 | -- Line 8: Skopje - Gostivar
|
|---|
| 171 | INSERT INTO linija_pravec (pravec_id, linija_id) VALUES
|
|---|
| 172 | (15, 8), -- Skopje to Gostivar
|
|---|
| 173 | (16, 8); -- Gostivar to Skopje
|
|---|
| 174 |
|
|---|
| 175 | -- Line 9: Skopje - Stip
|
|---|
| 176 | INSERT INTO linija_pravec (pravec_id, linija_id) VALUES
|
|---|
| 177 | (17, 9), -- Skopje to Stip
|
|---|
| 178 | (18, 9); -- Stip to Skopje
|
|---|
| 179 |
|
|---|
| 180 | -- Line 10: Skopje - Kavadarci
|
|---|
| 181 | INSERT INTO linija_pravec (pravec_id, linija_id) VALUES
|
|---|
| 182 | (19, 10), -- Skopje to Kavadarci
|
|---|
| 183 | (20, 10); -- Kavadarci to Skopje
|
|---|
| 184 |
|
|---|
| 185 | INSERT INTO Instanca_Na_Linija (linija_id, avtobus_id, start_date, end_date, vozac_id)
|
|---|
| 186 | VALUES
|
|---|
| 187 | -- Line 1: Skopje - Tetovo
|
|---|
| 188 | (1, 1, '2024-06-24 08:00:00', '2024-06-24 09:00:00', 1),
|
|---|
| 189 | (1, 1, '2024-06-24 09:00:00', '2024-06-24 10:00:00', 1),
|
|---|
| 190 | (1, 1, '2024-06-24 10:00:00', '2024-06-24 11:00:00', 1),
|
|---|
| 191 | (1, 1, '2024-06-24 11:00:00', '2024-06-24 12:00:00', 1),
|
|---|
| 192 | (1, 1, '2024-06-24 12:00:00', '2024-06-24 13:00:00', 1);
|
|---|
| 193 |
|
|---|
| 194 |
|
|---|
| 195 | -- Insert stops from Skopje to Tetovo
|
|---|
| 196 | INSERT INTO Postojka (lat, lon, ime, opis) VALUES
|
|---|
| 197 | (41.9981, 21.4254, 'Skopje', 'Capital city of North Macedonia'),
|
|---|
| 198 | (41.9643, 21.6228, 'Gazi Baba', 'Municipality near Skopje'),
|
|---|
| 199 | (42.0264, 21.5957, 'Tetovo', 'City in western North Macedonia');
|
|---|
| 200 |
|
|---|
| 201 | -- Insert stops from Tetovo to Skopje
|
|---|
| 202 | INSERT INTO Postojka (lat, lon, ime, opis) VALUES
|
|---|
| 203 | (42.0264, 21.5957, 'Tetovo', 'City in western North Macedonia'),
|
|---|
| 204 | (41.9643, 21.6228, 'Gazi Baba', 'Municipality near Skopje'),
|
|---|
| 205 | (41.9981, 21.4254, 'Skopje', 'Capital city of North Macedonia');
|
|---|
| 206 |
|
|---|
| 207 | -- Additional stops for Skopje to Tetovo
|
|---|
| 208 | INSERT INTO Postojka (lat, lon, ime, opis) VALUES
|
|---|
| 209 | (41.9808, 21.4631, 'Butel', 'Municipality in Skopje'),
|
|---|
| 210 | (42.0035, 21.4701, 'Kisela Voda', 'Municipality in Skopje'),
|
|---|
| 211 | (41.9866, 21.5666, 'Gostivar', 'City in western North Macedonia'),
|
|---|
| 212 | (41.9764, 21.6060, 'Zelino', 'Village near Tetovo'),
|
|---|
| 213 | (41.9939, 21.6481, 'Tetovo Bus Station', 'Central bus station in Tetovo'),
|
|---|
| 214 | (41.9861, 21.5705, 'Gostivar Train Station', 'Train station in Gostivar'),
|
|---|
| 215 | (42.0081, 21.4500, 'Skopje University', 'University campus in Skopje'),
|
|---|
| 216 | (42.0070, 21.4421, 'Kapishtec', 'Neighborhood in Skopje'),
|
|---|
| 217 | (41.9794, 21.4604, 'Skopje City Mall', 'Shopping mall in Skopje');
|
|---|
| 218 |
|
|---|
| 219 | -- Additional stops for Tetovo to Skopje
|
|---|
| 220 | INSERT INTO Postojka (lat, lon, ime, opis) VALUES
|
|---|
| 221 | (41.9839, 21.4647, 'Butel', 'Municipality in Skopje'),
|
|---|
| 222 | (41.9934, 21.4752, 'Kisela Voda', 'Municipality in Skopje'),
|
|---|
| 223 | (41.9901, 21.5804, 'Gostivar', 'City in western North Macedonia'),
|
|---|
| 224 | (41.9798, 21.6093, 'Zelino', 'Village near Tetovo'),
|
|---|
| 225 | (41.9997, 21.6543, 'Tetovo Bus Station', 'Central bus station in Tetovo'),
|
|---|
| 226 | (41.9930, 21.5741, 'Gostivar Train Station', 'Train station in Gostivar'),
|
|---|
| 227 | (42.0132, 21.4578, 'Skopje University', 'University campus in Skopje'),
|
|---|
| 228 | (42.0111, 21.4491, 'Kapishtec', 'Neighborhood in Skopje'),
|
|---|
| 229 | (41.9822, 21.4679, 'Skopje City Mall', 'Shopping mall in Skopje');
|
|---|
| 230 |
|
|---|
| 231 | -- Insert stops for Line 1 (Skopje - Tetovo)
|
|---|
| 232 | INSERT INTO Postojka_Na_Linija (linija_id, postojka_id, reden_broj, pravec_id) VALUES
|
|---|
| 233 | (1, 1, 1, 1), -- Skopje
|
|---|
| 234 | (1, 2, 2, 1), -- Gazi Baba
|
|---|
| 235 | (1, 8, 3, 1), -- Skopje University
|
|---|
| 236 | (1, 7, 4, 1), -- Kapishtec
|
|---|
| 237 | (1, 3, 5, 1), -- Tetovo
|
|---|
| 238 | (1, 6, 6, 1), -- Gostivar Train Station
|
|---|
| 239 | (1, 5, 7, 1), -- Gostivar
|
|---|
| 240 | (1, 4, 8, 1), -- Zelino
|
|---|
| 241 | (1, 9, 9, 1), -- Tetovo Bus Station
|
|---|
| 242 | (1, 10, 10, 1); -- Butel
|
|---|
| 243 |
|
|---|
| 244 | -- Insert stops for Line 1 (Tetovo to Skopje)
|
|---|
| 245 | INSERT INTO Postojka_Na_Linija (linija_id, postojka_id, reden_broj, pravec_id) VALUES
|
|---|
| 246 | (1, 10, 1, 2), -- Butel
|
|---|
| 247 | (1, 9, 2, 2), -- Tetovo Bus Station
|
|---|
| 248 | (1, 4, 3, 2), -- Zelino
|
|---|
| 249 | (1, 5, 4, 2), -- Gostivar
|
|---|
| 250 | (1, 6, 5, 2), -- Gostivar Train Station
|
|---|
| 251 | (1, 3, 6, 2), -- Tetovo
|
|---|
| 252 | (1, 7, 7, 2), -- Kapishtec
|
|---|
| 253 | (1, 8, 8, 2), -- Skopje University
|
|---|
| 254 | (1, 2, 9, 2), -- Gazi Baba
|
|---|
| 255 | (1, 1, 10, 2); -- Skopje
|
|---|
| 256 |
|
|---|
| 257 |
|
|---|
| 258 | -- Insert ticket types
|
|---|
| 259 | INSERT INTO TipBilet (cena, trajnost, ime) VALUES
|
|---|
| 260 | (50.00, 3600000, 'Hourly'), -- Hourly ticket (1 hour = 3,600,000 milliseconds)
|
|---|
| 261 | (150.00, 86400000, 'Daily'), -- Daily ticket (1 day = 86,400,000 milliseconds)
|
|---|
| 262 | (1000.00, 2592000000, 'Monthly'), -- Monthly ticket (30 days = 2,592,000,000 milliseconds)
|
|---|
| 263 | (10000.00, 31536000000, 'Yearly'); -- Yearly ticket (365 days = 31,536,000,000 milliseconds)
|
|---|
| 264 |
|
|---|
| 265 | -- Add tickets for commuter with patnik_id 1
|
|---|
| 266 | INSERT INTO Bilet (datum_kupuvanje, patnik_id, tip_id, status) VALUES
|
|---|
| 267 | ('2024-06-24 10:00:00', 1, 1, 'INACTIVE'), -- Hourly ticket (ID 1)
|
|---|
| 268 | ('2024-06-23 15:00:00', 1, 2, 'ACTIVE'); -- Daily ticket (ID 2)
|
|---|
| 269 |
|
|---|
