= Функции, процедури и тригери

== Функции

=== `get_customer_ticket_summary`

Оваа функција враќа детален преглед за билетите на одреден корисник, прикажувајќи колку вкупно билети има купено, колку од нив се single или pass билети, како и вкупната сума што ја има потрошено преку успешно реализирани плаќања.

{{{

CREATE OR REPLACE FUNCTION get_customer_ticket_summary(p_customer_id BIGINT)
RETURNS TABLE (
    customer_id BIGINT,
    total_tickets BIGINT,
    single_tickets BIGINT,
    pass_tickets BIGINT,
    total_spent NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM Customer c
        WHERE c.user_id = p_customer_id
    ) THEN
        RAISE EXCEPTION 'Customer with id % does not exist.', p_customer_id;
    END IF;

    RETURN QUERY
    SELECT
        c.user_id AS customer_id,
        COUNT(DISTINCT t.ticket_id) AS total_tickets,
        COUNT(DISTINCT st.ticket_id) AS single_tickets,
        COUNT(DISTINCT pt.ticket_id) AS pass_tickets,
        COALESCE(
            SUM(
                CASE
                    WHEN p.status = 'Completed'::payment_status
                    THEN COALESCE(st.amount, pass.amount, 0)
                    ELSE 0
                END
            ),
            0
        )::NUMERIC AS total_spent
    FROM Customer c
    LEFT JOIN Ticket t
        ON c.user_id = t.user_id
    LEFT JOIN Single_ticket st
        ON t.ticket_id = st.ticket_id
    LEFT JOIN Pass_ticket pt
        ON t.ticket_id = pt.ticket_id
    LEFT JOIN Pass_type pass
        ON pt.type_id = pass.id
    LEFT JOIN Customer_Payment_Ticket cpt
        ON t.ticket_id = cpt.ticket_id
       AND c.user_id = cpt.user_id
    LEFT JOIN Payment p
        ON cpt.payment_id = p.payment_id
    WHERE c.user_id = p_customer_id
    GROUP BY c.user_id;
END;
$$;

}}}

=== `is_ticket_valid`

Оваа функција служи за проверка дали даден билет е валиден за користење односно дали е платен и активен според неговиот тип (single или pass) и условите за важност.

{{{

CREATE OR REPLACE FUNCTION is_ticket_valid(
    p_ticket_id BIGINT,
    p_assignment_id BIGINT
)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
    v_is_valid BOOLEAN;
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM Ticket t
        WHERE t.ticket_id = p_ticket_id
    ) THEN
        RAISE EXCEPTION 'Ticket with id % does not exist.', p_ticket_id;
    END IF;

    SELECT
        EXISTS (
            SELECT 1
            FROM Single_ticket st
            JOIN Customer_Payment_Ticket cpt
                ON st.ticket_id = cpt.ticket_id
            JOIN Payment p
                ON cpt.payment_id = p.payment_id
            WHERE st.ticket_id = p_ticket_id
              AND st.assignment_id = p_assignment_id
              AND p.status = 'Completed'::payment_status
        )
        OR
        EXISTS (
            SELECT 1
            FROM Pass_ticket pt
            JOIN Pass_type pass
                ON pt.type_id = pass.id
            JOIN Customer_Payment_Ticket cpt
                ON pt.ticket_id = cpt.ticket_id
            JOIN Payment p
                ON cpt.payment_id = p.payment_id
            WHERE pt.ticket_id = p_ticket_id
              AND p.status = 'Completed'::payment_status
              AND CURRENT_DATE BETWEEN pass.valid_from AND pass.valid_until
        )
    INTO v_is_valid;

    RETURN v_is_valid;
END;
$$;

}}}

=== `count_active_lines`

Оваа функција е имплементирана со цел да обезбеди брз и едноставен начин за добивање на бројот на активни линии во системот. Нејзината намена е да поддржи анализа и следење на состојбата на транспортната мрежа преку централизирано пресметување на активните линии.

{{{

CREATE OR REPLACE FUNCTION count_active_lines()
RETURNS INTEGER AS $$
DECLARE
    v_active_count INTEGER;
BEGIN
    SELECT COUNT(*)
    INTO v_active_count
    FROM Line
    WHERE is_active = TRUE;
    
    RETURN v_active_count;
END;
$$ LANGUAGE plpgsql;

SELECT count_active_lines();
}}}

== Процедури

=== `buy_single_ticket`

Оваа процедура служи за креирање и купување на single билет за одреден клиент, при што автоматски се регистрира плаќање, се генерира билет поврзан со одредена линија и се поврзуваат сите потребни записи во системот за евиденција.

{{{

CREATE OR REPLACE PROCEDURE buy_single_ticket(
    p_customer_id BIGINT,
    p_assignment_id BIGINT,
    p_payment_type_id BIGINT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_payment_id BIGINT;
    v_ticket_id BIGINT;
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM Customer c
        WHERE c.user_id = p_customer_id
    ) THEN
        RAISE EXCEPTION 'Customer with id % does not exist.', p_customer_id;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM Line_assignment la
        WHERE la.assignment_id = p_assignment_id
    ) THEN
        RAISE EXCEPTION 'Line assignment with id % does not exist.', p_assignment_id;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM Payment_type pt
        WHERE pt.type_id = p_payment_type_id
    ) THEN
        RAISE EXCEPTION 'Payment type with id % does not exist.', p_payment_type_id;
    END IF;

    INSERT INTO Payment (type_id, status, payment_date, transaction_number)
    VALUES (
        p_payment_type_id,
        'Completed'::payment_status,
        CURRENT_DATE,
        'TRX-SINGLE-' || extract(epoch from clock_timestamp())::BIGINT || '-' || p_customer_id
    )
    RETURNING payment_id INTO v_payment_id;

    INSERT INTO Ticket (user_id)
    VALUES (p_customer_id)
    RETURNING ticket_id INTO v_ticket_id;

    INSERT INTO Single_ticket (ticket_id, amount, assignment_id)
    VALUES (v_ticket_id, 50, p_assignment_id);

    INSERT INTO Customer_Payment_Ticket (payment_id, ticket_id, user_id)
    VALUES (v_payment_id, v_ticket_id, p_customer_id);

    RAISE NOTICE 'Single ticket created. ticket_id=%, payment_id=%', v_ticket_id, v_payment_id;
END;
$$;

}}}

=== `buy_pass_ticket`

Оваа процедура служи за купување и креирање на pass билет за клиент, при што се регистрира плаќање, се создава билет од избран тип на pass и се поврзуваат сите релевантни записи во системот за евиденција.

{{{

CREATE OR REPLACE PROCEDURE buy_pass_ticket(
    p_customer_id BIGINT,
    p_pass_type_id INTEGER,
    p_payment_type_id BIGINT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_payment_id BIGINT;
    v_ticket_id BIGINT;
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM Customer c
        WHERE c.user_id = p_customer_id
    ) THEN
        RAISE EXCEPTION 'Customer with id % does not exist.', p_customer_id;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM Pass_type pt
        WHERE pt.id = p_pass_type_id
    ) THEN
        RAISE EXCEPTION 'Pass type with id % does not exist.', p_pass_type_id;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM Payment_type pt
        WHERE pt.type_id = p_payment_type_id
    ) THEN
        RAISE EXCEPTION 'Payment type with id % does not exist.', p_payment_type_id;
    END IF;

    INSERT INTO Payment (type_id, status, payment_date, transaction_number)
    VALUES (
        p_payment_type_id,
        'Completed'::payment_status,
        CURRENT_DATE,
        'TRX-PASS-' || extract(epoch from clock_timestamp())::BIGINT || '-' || p_customer_id
    )
    RETURNING payment_id INTO v_payment_id;

    INSERT INTO Ticket (user_id)
    VALUES (p_customer_id)
    RETURNING ticket_id INTO v_ticket_id;

    INSERT INTO Pass_ticket (ticket_id, type_id)
    VALUES (v_ticket_id, p_pass_type_id);

    INSERT INTO Customer_Payment_Ticket (payment_id, ticket_id, user_id)
    VALUES (v_payment_id, v_ticket_id, p_customer_id);

    RAISE NOTICE 'Pass ticket created. ticket_id=%, payment_id=%', v_ticket_id, v_payment_id;
END;
$$;

}}}

=== `assign_driver_to_line`

Оваа процедура служи за доделување на возач на конкретна линија и автобус во одреден временски период, при што прво проверува дали сите учесници и ресурси се активни и достапни и дали нема преклопување со други смени.

{{{

CREATE OR REPLACE PROCEDURE assign_driver_to_line(
    p_driver_id BIGINT,
    p_admin_id BIGINT,
    p_chassis_number VARCHAR,
    p_schedule_id INTEGER,
    p_start_time TIMESTAMP,
    p_end_time TIMESTAMP
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_assignment_id BIGINT;
BEGIN
    IF p_start_time >= p_end_time THEN
        RAISE EXCEPTION 'Start time must be before end time.';
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM Driver d
        WHERE d.user_id = p_driver_id
          AND d.status = 'Active'::employee_status
    ) THEN
        RAISE EXCEPTION 'Driver % does not exist or is not active.', p_driver_id;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM Admin a
        WHERE a.user_id = p_admin_id
          AND a.status = 'Active'::employee_status
    ) THEN
        RAISE EXCEPTION 'Admin % does not exist or is not active.', p_admin_id;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM Bus_instance bi
        WHERE bi.chassis_number = p_chassis_number
          AND bi.status = 'Active'::bus_status
    ) THEN
        RAISE EXCEPTION 'Bus % does not exist or is not active.', p_chassis_number;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM Schedule s
        WHERE s.schedule_id = p_schedule_id
    ) THEN
        RAISE EXCEPTION 'Schedule % does not exist.', p_schedule_id;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM Line_assignment la
        WHERE la.driver_id = p_driver_id
          AND p_start_time < la.end_time
          AND p_end_time > la.start_time
    ) THEN
        RAISE EXCEPTION 'Driver % already has an overlapping shift.', p_driver_id;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM Line_assignment la
        WHERE la.chassis_number = p_chassis_number
          AND p_start_time < la.end_time
          AND p_end_time > la.start_time
    ) THEN
        RAISE EXCEPTION 'Bus % is already assigned in this time interval.', p_chassis_number;
    END IF;

    INSERT INTO Line_assignment
        (driver_id, admin_id, chassis_number, schedule_id, start_time, end_time)
    VALUES
        (p_driver_id, p_admin_id, p_chassis_number, p_schedule_id, p_start_time, p_end_time)
    RETURNING assignment_id INTO v_assignment_id;

    RAISE NOTICE 'Line assignment created. assignment_id=%', v_assignment_id;
END;
$$;

}}}

=== `generate_line_assignment`

Оваа процедура служи за внесување на нова распределба на линија, при што се зачувуваат информациите за возачот, администраторот, автобусот и возниот ред што ќе се користи. Со нејзина помош процесот на креирање на распределби е поедноставен и се намалува можноста за грешки при внесувањето на податоците.

{{{

CREATE OR REPLACE PROCEDURE generate_line_assignment(
    assignment_driver_id BIGINT,
    assignment_admin_id BIGINT,
    assignment_chassis_number VARCHAR,
    assignment_schedule_id INT,
    assignment_start_time TIMESTAMP,
    assignment_end_time TIMESTAMP
)
LANGUAGE plpgsql AS $$

DECLARE
    new_assignment_id BIGINT;

BEGIN

    INSERT INTO Line_assignment(
        driver_id,
        admin_id,
        chassis_number,
        schedule_id,
        start_time,
        end_time
    )
    VALUES(
        assignment_driver_id,
        assignment_admin_id,
        assignment_chassis_number,
        assignment_schedule_id,
        assignment_start_time,
        assignment_end_time
    )
    RETURNING assignment_id INTO new_assignment_id;

    COMMIT;

END;
$$;

}}}

=== `register_customer`

Оваа процедура овозможува регистрација на нов корисник во системот преку внесување на неговите лични податоци и автоматско креирање на запис во табелата за патници. Со тоа се обезбедува едноставен и организиран процес на регистрација, при што податоците за корисникот и неговиот тип се зачувуваат на соодветните места во базата на податоци.

{{{

create or replace procedure register_customer(
    customer_name varchar,
    customer_surname varchar,
    customer_email varchar,
    customer_password varchar,
    customer_address varchar,
    customer_birth date,
    customer_phone varchar,
    customer_id_card varchar,
    customer_type_value customer_type
)
language plpgsql as $$

declare
    new_user_id bigint;

begin

    insert into ApplicationUser(
        name,
        surname,
        email,
        password,
        address,
        date_of_birth,
        phone_number,
        id_card
    )
    values(
        customer_name,
        customer_surname,
        customer_email,
        customer_password,
        customer_address,
        customer_birth,
        customer_phone,
        customer_id_card
    )
    returning user_id into new_user_id;

    insert into Customer(user_id, type)
    values(new_user_id, customer_type_value);

    commit;

end;
$$;

}}}

== Тригери

=== `prevent_driver_shift_overlap`

Оваj trigger служи за автоматска проверка и спречување на преклопување на работни смени за возач, односно не дозволува внес или измена на assignment ако временски се поклопува со друга веќе постоечка смена за истиот возач.

{{{

CREATE OR REPLACE FUNCTION prevent_driver_shift_overlap_fn()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.start_time >= NEW.end_time THEN
        RAISE EXCEPTION 'Start time must be before end time.';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM Line_assignment la
        WHERE la.driver_id = NEW.driver_id
          AND la.assignment_id <> COALESCE(NEW.assignment_id, -1)
          AND NEW.start_time < la.end_time
          AND NEW.end_time > la.start_time
    ) THEN
        RAISE EXCEPTION 'Driver % already has an overlapping shift.', NEW.driver_id;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_prevent_driver_shift_overlap ON Line_assignment;

CREATE TRIGGER trg_prevent_driver_shift_overlap
BEFORE INSERT OR UPDATE ON Line_assignment
FOR EACH ROW
EXECUTE FUNCTION prevent_driver_shift_overlap_fn();

}}}

=== `log_payment_status_change`

Оваj trigger служи за автоматско логирање на секоја промена на статусот на плаќање, така што секогаш кога ќе се смени статусот во табелата Payment, се зачувува запис со стар и нов статус во посебна лог табела.

{{{

CREATE TABLE IF NOT EXISTS Payment_status_log
(
    log_id BIGSERIAL PRIMARY KEY,
    payment_id BIGINT NOT NULL,
    old_status payment_status,
    new_status payment_status,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    description TEXT,
    CONSTRAINT payment_status_log_payment_fk
        FOREIGN KEY (payment_id) REFERENCES Payment(payment_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE OR REPLACE FUNCTION log_payment_status_change_fn()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.status IS DISTINCT FROM NEW.status THEN
        INSERT INTO Payment_status_log
            (payment_id, old_status, new_status, description)
        VALUES
            (
                NEW.payment_id,
                OLD.status,
                NEW.status,
                'Payment status changed from ' || OLD.status || ' to ' || NEW.status
            );
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_log_payment_status_change ON Payment;

CREATE TRIGGER trg_log_payment_status_change
AFTER UPDATE OF status ON Payment
FOR EACH ROW
EXECUTE FUNCTION log_payment_status_change_fn();

}}}

=== `payment_expired`

Овој тригер автоматски го проверува статусот на секоја уплата при нејзино внесување или ажурирање во системот. Доколку уплатата е сè уште во статус „Pending“, а датумот за плаќање е поминат, статусот автоматски се менува во „Failed“, со што се обезбедува точно евидентирање на неуспешните уплати.

{{{

create or replace function payment_expired()
returns trigger as $$

begin

    if NEW.status = 'Pending'
    and NEW.payment_date < current_date then

        NEW.status := 'Failed';

    end if;

    return NEW;

end;
$$ language plpgsql;

create trigger payment_status_trigger
before insert or update on Payment
for each row
execute function payment_expired();

}}}