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

== Функции

=== `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;
$$;

}}}

=== `get_customer_monthly_spending`

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

{{{

CREATE OR REPLACE FUNCTION get_customer_monthly_spending(
    p_customer_id BIGINT,
    p_month DATE
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    v_month_start DATE;
    v_month_end DATE;
    v_total NUMERIC;
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;

    v_month_start := date_trunc('month', p_month)::date;
    v_month_end := (v_month_start + INTERVAL '1 month')::date;

    SELECT
        COALESCE(SUM(COALESCE(st.amount, pass.amount, 0)), 0)
    INTO v_total
    FROM Customer_Payment_Ticket cpt
    JOIN Payment p
        ON cpt.payment_id = p.payment_id
    JOIN Ticket t
        ON cpt.ticket_id = t.ticket_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
    WHERE cpt.user_id = p_customer_id
      AND p.status = 'Completed'::payment_status
      AND p.payment_date >= v_month_start
      AND p.payment_date < v_month_end;

    RETURN v_total;
END;
$$;

}}}

=== `get_driver_shift_count`

Оваа функција служи за да го пресмета бројот на смени (line assignments) што одреден возач ги има извршено во даден временски интервал.

{{{

CREATE OR REPLACE FUNCTION get_driver_shift_count(
    p_driver_id BIGINT,
    p_date_from TIMESTAMP,
    p_date_to TIMESTAMP
)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_count INTEGER;
BEGIN
    IF p_date_from >= p_date_to THEN
        RAISE EXCEPTION 'Start date must be before end date.';
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM Driver d
        WHERE d.user_id = p_driver_id
    ) THEN
        RAISE EXCEPTION 'Driver with id % does not exist.', p_driver_id;
    END IF;

    SELECT COUNT(*)
    INTO v_count
    FROM Line_assignment la
    WHERE la.driver_id = p_driver_id
      AND la.start_time >= p_date_from
      AND la.start_time < p_date_to;

    RETURN v_count;
END;
$$;

}}}

== Процедури

=== `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;
$$;

}}}

== Тригери

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

}}}