Application Development

Scope

This phase concerns itself with implementing the use cases defined in P3 of the project, found at: https://develop.finki.ukim.mk/projects/routemk/wiki/ApplicationDesign

Source

The source code for the application can be found at the following link ​https://github.com/KikoTheFinker/RouteMK, and includes setup through:

• Docker (recommended, especially for the DB)
• Native (no containers)

---

Transactions

Transactions are used from Spring Boot specifically in these files:

• ​CompanyTripServiceImpl.java
• ​PaymentServiceImpl.java

Indexes

To optimize query performance and reduce full table scans as data volume grows, the following indexes were added based on real usage patterns in analytical queries, views, and joins.

1. Most queries join trip and route using route_id, for example in company_performance_view, top_selling_routes_view, ticket statistics, and weighted usage reports. Without this index, every join would trigger a sequential scan of the trip table. With the index, the database can directly locate all trips for a given route.

CREATE INDEX idx_trip_route_id ON trip(route_id);

---

2. These two indexes accelerate lookups and joins involving route endpoints. They support queries that find trips between specific locations, which for our application is especially iseful to detect subroutes, one of the most important features. They're also useful in displaying routes with origin - destination metadata.

CREATE INDEX idx_route_from_location_id ON route(from_location_id);
CREATE INDEX idx_route_to_location_id ON route(to_location_id);

---

3. Filtering by trip status (e.g., 'COMPLETED', 'NOT_STARTED') is common in ticket statistics and reporting queries. This index ensures that the database can filter relevant trips quickly instead of scanning the full trip table. It's especially important in analytical use cases and dashboards.

CREATE INDEX idx_trip_status ON trip(status);

---

4. Views such as company_performance_view and top_selling_routes_view frequently join route and transport_organizer through transport_organizer_id. This index speeds up company-based route lookups, revenue calculations and per-organizer reporting. So, this index is useful for when we're calculating company metrics/revenue by organizer.

CREATE INDEX idx_route_transport_organizer_id ON route(transport_organizer_id);

Triggers

1. Trigger to update the number of free seats based on ticket insertion/deletion

This trigger automatically adjusts the number of available seats in a trip whenever a ticket is inserted or deleted. On insert, it decreases the free_seats count by one, and on delete, it increases it by one. This ensures that trip capacity stays accurate without requiring manual updates.

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();

2. Ticket discount and total price triggers

These two triggers work together in a way that the discount triggers first calculates and applies the reduced ticket price before the ticket is officially inserted into the database. Once the discounted ticket is saved, the payment triggers recalculate the total payment by summing all ticket prices linked to the same payment record, ensuring the final total reflects the applied discount.

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();

3. Trigger to automatically set trip status

This trigger assigns the default status 'NOT_STARTED' to any new trip that doesn't explicitly define a status. It guarantees consistent initialization of trip state at creation time. This prevents null or undefined statuses in downstream queries and logic.

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();

4. Trigger to update trip status

This function adjusts a trip's status based on available seats and trip date. It sets the trip to 'FULL' when no seats remain or resets it to 'NOT_STARTED' if seats reopen. If the trip date is in the past, it marks the trip as 'COMPLETED' to reflect its final state.

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;

CREATE TRIGGER trg_update_trip_status
    BEFORE UPDATE OF free_seats, date ON trip
    FOR EACH ROW
    EXECUTE FUNCTION update_trip_status();

5. Trigger to automatically set the ticket purchase time and date

This trigger ensures that every ticket has a purchase date and time set automatically. If either field is missing at insertion, it fills them with the current date and time. It prevents incomplete timestamps and simplifies data entry.

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();