Index: RouteMK-master/scripts/RouteMK.sql
===================================================================
--- RouteMK-master/scripts/RouteMK.sql (revision 39d0e9aa2e04189c99b926280b7c78d98d7d1c26)
+++ RouteMK-master/scripts/RouteMK.sql (revision 27919ca1487eb666c5b5a3e9c2a62e8cbee2d59f)
@@ -1,4 +1,3 @@
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;
@@ -24,4 +23,7 @@
DROP TABLE IF EXISTS vehicle CASCADE;
+
+-- TABLE CREATION
+
CREATE TABLE account
(
@@ -103,9 +105,11 @@
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 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
);
@@ -243,12 +247,142 @@
);
-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
-);
+
+
+-- TRIGGERS
+
+
+
+-- TRIGGER TO UPDATE FREE SEATS
+CREATE OR REPLACE FUNCTION update_free_seats()
+RETURNS TRIGGER AS $$
+BEGIN
+ IF TG_OP = 'INSERT' THEN
+UPDATE trip
+SET free_seats = free_seats - 1
+WHERE trip_id = NEW.trip_id;
+RETURN NEW;
+ELSIF TG_OP = 'DELETE' THEN
+UPDATE trip
+SET free_seats = free_seats + 1
+WHERE trip_id = OLD.trip_id;
+RETURN OLD;
+END IF;
+RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER ticket_insert_update_seats
+ AFTER INSERT ON ticket
+ FOR EACH ROW
+ EXECUTE FUNCTION update_free_seats();
+
+CREATE TRIGGER ticket_delete_update_seats
+ AFTER DELETE ON ticket
+ FOR EACH ROW
+ EXECUTE FUNCTION update_free_seats();
+
+
+-- 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
INSERT INTO account
values (100, 'duko@outlook.com', 'David', 'Davidov',
@@ -265,4 +399,7 @@
+INSERT INTO admin
+values (100, 300);
+
INSERT INTO transport_organizer
values (100, 100, 'Galeb', '1234512345123');
@@ -271,8 +408,4 @@
INSERT INTO transport_organizer
VALUES (300, 300, 'MakExpress', '1234512345125');
-
-INSERT INTO admin
-values (100, 300);
-
INSERT INTO location (location_id, latitude, longitude, name)
@@ -730,2 +863,41 @@
(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 39d0e9aa2e04189c99b926280b7c78d98d7d1c26)
+++ RouteMK-master/src/main/java/mk/route/routemk/models/enums/Status.java (revision 27919ca1487eb666c5b5a3e9c2a62e8cbee2d59f)
@@ -5,4 +5,5 @@
NOT_STARTED,
IN_PROGRESS,
- COMPLETED
+ COMPLETED,
+ FULL
}
Index: RouteMK-master/src/main/resources/templates/user/my-trips.html
===================================================================
--- RouteMK-master/src/main/resources/templates/user/my-trips.html (revision 39d0e9aa2e04189c99b926280b7c78d98d7d1c26)
+++ RouteMK-master/src/main/resources/templates/user/my-trips.html (revision 27919ca1487eb666c5b5a3e9c2a62e8cbee2d59f)
@@ -68,5 +68,6 @@
-
+
+