Index: RouteMK-master/scripts/RouteMK.sql
===================================================================
--- RouteMK-master/scripts/RouteMK.sql (revision f0c022eae17a381271f4df1eb56da24336df5418)
+++ RouteMK-master/scripts/RouteMK.sql (revision 39d0e9aa2e04189c99b926280b7c78d98d7d1c26)
@@ -1,3 +1,4 @@
DROP TABLE IF EXISTS child_ticket CASCADE;
+DROP TABLE IF EXISTS ticket_relations CASCADE;
DROP TABLE IF EXISTS ticket CASCADE;
DROP TABLE IF EXISTS payment CASCADE;
@@ -23,7 +24,4 @@
DROP TABLE IF EXISTS vehicle CASCADE;
-
--- TABLE CREATION
-
CREATE TABLE account
(
@@ -105,11 +103,9 @@
seat VARCHAR(10),
payment_id INT NOT NULL,
- ticket_related_with_id INT,
CONSTRAINT gets_on_location_fkey FOREIGN KEY (gets_on_location_id) REFERENCES location (location_id) ON DELETE CASCADE,
CONSTRAINT gets_off_location_fkey FOREIGN KEY (gets_off_location_id) REFERENCES location (location_id) ON DELETE CASCADE,
CONSTRAINT ticket_account_id_fkey FOREIGN KEY (account_id) REFERENCES account (account_id) ON DELETE CASCADE,
CONSTRAINT ticket_payment_id_fkey FOREIGN KEY (payment_id) REFERENCES payment (payment_id) ON DELETE CASCADE,
- CONSTRAINT trip_id_fkey FOREIGN KEY (trip_id) REFERENCES trip (trip_id) ON DELETE CASCADE,
- CONSTRAINT ticket_related_with_id_fk FOREIGN KEY (ticket_related_with_id) REFERENCES ticket(ticket_id) ON DELETE CASCADE
+ CONSTRAINT trip_id_fkey FOREIGN KEY (trip_id) REFERENCES trip (trip_id) ON DELETE CASCADE
);
@@ -247,110 +243,12 @@
);
-
-
--- TRIGGERS
-
--- TRIGGER TO CALCULATE TICKET PRICE WITH DISCOUNTS
-CREATE OR REPLACE FUNCTION apply_ticket_discount()
-RETURNS TRIGGER AS $$
-BEGIN
-UPDATE ticket
-SET price = tr.base_price - (tr.base_price * NEW.discount / 100)
- FROM trip tr
-WHERE ticket.ticket_id = NEW.ticket_id AND ticket.trip_id = tr.trip_id;
-RETURN NEW;
-END;
-$$ LANGUAGE plpgsql;
-CREATE TRIGGER apply_student_ticket_discount
- BEFORE INSERT ON student_ticket
- FOR EACH ROW EXECUTE FUNCTION apply_ticket_discount();
-
-CREATE TRIGGER apply_child_ticket_discount
- BEFORE INSERT ON child_ticket
- FOR EACH ROW EXECUTE FUNCTION apply_ticket_discount();
-
-
--- UPDATE TOTAL PAYMENT TOTAL PRICE (TRIGGER AFTER CHILD/STUDENT DISCOUNT IS APPLIED)
-CREATE OR REPLACE FUNCTION update_payment_total()
-RETURNS TRIGGER AS $$
-BEGIN UPDATE payment
- SET total_price = ( SELECT COALESCE(SUM(price), 0)
- FROM ticket
- WHERE payment_id = NEW.payment_id)
- WHERE payment_id = NEW.payment_id;
-
-RETURN NEW; END; $$
-LANGUAGE plpgsql;
-
-CREATE TRIGGER trg_update_payment_total_insert
- AFTER INSERT ON ticket
- FOR EACH ROW EXECUTE FUNCTION update_payment_total();
-
-CREATE TRIGGER trg_update_payment_total_update
- AFTER UPDATE OF price, payment_id ON ticket
- FOR EACH ROW EXECUTE FUNCTION update_payment_total();
-
-CREATE TRIGGER trg_update_payment_total_delete
- AFTER DELETE ON ticket
- FOR EACH ROW EXECUTE FUNCTION update_payment_total();
-
-
--- TRIGGER TO AUTOMATICALLY SET NOT_STARTED STATUS TO NEWLY CREATED TRIPS
-CREATE OR REPLACE FUNCTION set_default_trip_status()
-RETURNS TRIGGER AS $$
-BEGIN
- IF NEW.status IS NULL THEN
- NEW.status = 'NOT_STARTED';
-END IF;
-RETURN NEW;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER trg_set_default_trip_status
- BEFORE INSERT ON trip
- FOR EACH ROW EXECUTE FUNCTION set_default_trip_status();
-
-
-
--- TRIGGER TO UPDATE TRIP STATUS BASED ON TIME AND NUMBER OF SEATS
-CREATE OR REPLACE FUNCTION update_trip_status()
-RETURNS TRIGGER AS $$
-BEGIN
- IF NEW.free_seats = 0 THEN
- NEW.status = 'FULL';
- ELSIF NEW.free_seats > 0 AND OLD.status = 'FULL' THEN
- NEW.status = 'NOT_STARTED';
-END IF;
-
- IF NEW.date < CURRENT_DATE THEN
- NEW.status = 'COMPLETED';
-END IF;
-
-RETURN NEW;
-END;
-$$ LANGUAGE plpgsql;
-
-
--- TRIGGER TO AUTOMATICALLY SET TIME AND DATE OF TICKET PURCHASE
-CREATE OR REPLACE FUNCTION set_purchase_timestamp()
-RETURNS TRIGGER AS $$
-BEGIN
- IF NEW.date_purchased IS NULL THEN
- NEW.date_purchased = CURRENT_DATE;
-END IF;
- IF NEW.time_purchased IS NULL THEN
- NEW.time_purchased = CURRENT_TIME;
-END IF;
-RETURN NEW;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER trg_set_purchase_timestamp
- BEFORE INSERT ON ticket
- FOR EACH ROW EXECUTE FUNCTION set_purchase_timestamp();
-
-
-
--- INSERTION STATEMENTS
+CREATE TABLE ticket_relations
+(
+ ticket_relation_id SERIAL PRIMARY KEY,
+ parent_ticket_id INT NOT NULL,
+ child_ticket_id INT NOT NULL,
+ CONSTRAINT ticket_relations_parent_ticket_id_fkey FOREIGN KEY (parent_ticket_id) REFERENCES ticket (ticket_id) ON DELETE CASCADE,
+ CONSTRAINT ticket_relations_child_ticket_id_fkey FOREIGN KEY (child_ticket_id) REFERENCES ticket (ticket_id) ON DELETE CASCADE
+);
INSERT INTO account
values (100, 'duko@outlook.com', 'David', 'Davidov',
@@ -367,7 +265,4 @@
-INSERT INTO admin
-values (100, 300);
-
INSERT INTO transport_organizer
values (100, 100, 'Galeb', '1234512345123');
@@ -376,4 +271,8 @@
INSERT INTO transport_organizer
VALUES (300, 300, 'MakExpress', '1234512345125');
+
+INSERT INTO admin
+values (100, 300);
+
INSERT INTO location (location_id, latitude, longitude, name)
@@ -831,41 +730,2 @@
(12, 2003, 600),
(13, 2004, 700);
-
-
--- INSERT INTO child_ticket (child_ticket_id, ticket_id, discount, embg, parent_embg)
--- VALUES
--- (1, 302, 50, '1234567890123', '9876543210987'),
--- (2, 306, 40, '1112223334445', '5554443332221'),
--- (3, 311, 30, '2223334445556', '6665554443332'),
--- (4, 1003, 35, '3334445556667', '7776665554443'),
--- (5, 1022, 25, '4445556667778', '8887776665554');
-
-
--- INDEXES
-
--- Index on trip.route_id improves performance for joins and lookups between trips and routes.
--- In a large database, this is critical because many queries filter or aggregate trips by route,
--- such as ticket sales per route, route performance metrics, or finding trips for a specific route.
-CREATE INDEX idx_trip_route_id ON trip(route_id);
-
-
--- These indexes speed up frequent lookups for routes by start or end location,
--- which are heavily used in searches, subroute queries, and joins with trips and tickets.
--- The current table is not filled with too much information and if we use EXPLAIN ANALYZE on a sample query,
--- it will search the table sequentially still. Though in a realistic scenario where there are many routes to search,
--- these indexes will be quite useful.
-CREATE INDEX idx_route_to_location_id ON route(to_location_id);
-CREATE INDEX idx_route_from_location_id ON route(from_location_id);
-
-
--- Index on trip status speeds up queries filtering by trip state (e.g., 'COMPLETED', 'NOT_STARTED'),
--- which is especially useful in large databases when aggregating ticket sales or checking active trips.
--- The latter is a much more important scenario since it should be often that users are more interesed in upcoming
--- ('NOT_STARTED') trips.
-CREATE INDEX idx_trip_status ON trip(status);
-
-
--- Index on route.transport_organizer_id speeds up queries that filter or join routes by transport organizer.
--- In a large database, this is useful for performance reporting, calculating company metrics,
--- or retrieving all routes operated by a specific organizer without scanning the entire table.
-CREATE INDEX idx_route_transport_organizer_id ON route(transport_organizer_id);
Index: RouteMK-master/src/main/java/mk/route/routemk/models/enums/Status.java
===================================================================
--- RouteMK-master/src/main/java/mk/route/routemk/models/enums/Status.java (revision f0c022eae17a381271f4df1eb56da24336df5418)
+++ RouteMK-master/src/main/java/mk/route/routemk/models/enums/Status.java (revision 39d0e9aa2e04189c99b926280b7c78d98d7d1c26)
@@ -5,5 +5,4 @@
NOT_STARTED,
IN_PROGRESS,
- COMPLETED,
- FULL
+ COMPLETED
}
Index: RouteMK-master/src/main/resources/templates/user/my-trips.html
===================================================================
--- RouteMK-master/src/main/resources/templates/user/my-trips.html (revision f0c022eae17a381271f4df1eb56da24336df5418)
+++ RouteMK-master/src/main/resources/templates/user/my-trips.html (revision 39d0e9aa2e04189c99b926280b7c78d98d7d1c26)
@@ -68,6 +68,5 @@
-
-
+