= Functions, Procedures, Triggers =

Шемата `kbnteam` содржи три функции, тринаесет процедури и три тригери за договори, нарачки, достави, рецензии и лојалност. Функциите враќаат пресметки и проверки, процедурите се повикуваат со `CALL`, а тригерите автоматски се активираат при промени на податоците.

== Функција 1: fn_has_active_contract ==

Враќа TRUE ако постои договор за зададената компанија и ресторан со статус active, а зададениот датум е меѓу почетниот и крајниот датум, вклучувајќи ги границите. Инаку враќа FALSE. Кога датумот не е зададен, се користи тековниот датум. Називот на статусот се споредува без разлика на големината на буквите.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE FUNCTION kbnteam.fn_has_active_contract(
    p_company_id INTEGER,
    p_rest_id INTEGER,
    p_on_date DATE DEFAULT CURRENT_DATE
)
RETURNS BOOLEAN
LANGUAGE sql
STABLE
AS $$
    SELECT EXISTS (
        SELECT 1
        FROM kbnteam.contract ct
        JOIN kbnteam.contract_status cs
          ON cs.contract_status_id = ct.contract_status_id
        WHERE ct.company_id = p_company_id
          AND ct.rest_id = p_rest_id
          AND lower(cs.contract_status_name) = 'active'
          AND p_on_date BETWEEN ct.contract_start_date AND ct.contract_end_date
    );
$$;
}}}

== Функција 2: fn_calculate_order_total ==

Ги собира тековните цени на јадењата и пијалоците поврзани со нарачката. Ако нема ставки, враќа 0.00. Не применува попуст за лојалност или дополнителен надомест за достава и не ја менува нарачката; запишувањето на пресметаниот износ го вршат процедурите и тригерите.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE FUNCTION kbnteam.fn_calculate_order_total(
    p_order_id INTEGER
)
RETURNS NUMERIC(10,2)
LANGUAGE sql
STABLE
AS $$
    SELECT (
        COALESCE((
            SELECT SUM(m.meal_price)
            FROM kbnteam.order_meal om
            JOIN kbnteam.meal m
              ON m.meal_id = om.meal_id
            WHERE om.order_id = p_order_id
        ), 0)
        +
        COALESCE((
            SELECT SUM(d.drink_price)
            FROM kbnteam.order_drink od
            JOIN kbnteam.drink d
              ON d.drink_id = od.drink_id
            WHERE od.order_id = p_order_id
        ), 0)
    )::NUMERIC(10,2);
$$;
}}}

== Функција 3: fn_calculate_customer_loyalty_points ==

Ги собира износите на нарачките на клиентот, го заокружува збирот надолу и враќа цел број. Нарачките со статус cancelled придонесуваат со нула. Останатите статуси, вклучително pending и refunded, се вклучени. Ако нема нарачки, враќа нула. Функцијата ги пресметува поените, а нивното запишување го врши процедурата за освежување на лојалноста.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE FUNCTION kbnteam.fn_calculate_customer_loyalty_points(
    p_customer_user_id INTEGER
)
RETURNS INTEGER
LANGUAGE sql
STABLE
AS $$
    SELECT COALESCE(
        FLOOR(SUM(
            CASE
                WHEN lower(os.o_status_name) = 'cancelled' THEN 0
                ELSE o.order_total
            END
        )),
        0
    )::INTEGER
    FROM kbnteam.customer_order o
    JOIN kbnteam.order_status os
      ON os.o_status_id = o.o_status_id
    WHERE o.customer_user_id = p_customer_user_id;
$$;
}}}

== Процедура 1: pr_activate_or_renew_contract ==

Проверува дали компанијата и ресторанот постојат и дали периодот е валиден. Кога постои активен договор со период што се преклопува или е непосредно соседен, го проширува неговиот период. Ако нема таков договор, создава нов со статус active. Обновувањето се одбива ако проширениот период се преклопува со друг активен договор.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE PROCEDURE kbnteam.pr_activate_or_renew_contract(
    p_company_id INTEGER,
    p_rest_id INTEGER,
    p_start_date DATE,
    p_end_date DATE
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_active_status_id INTEGER;
    v_contract_id INTEGER;
    v_existing_start DATE;
    v_existing_end DATE;
    v_merged_start DATE;
    v_merged_end DATE;
BEGIN
    IF p_start_date IS NULL OR p_end_date IS NULL THEN
        RAISE EXCEPTION 'Contract start and end dates are required.';
    END IF;

    IF p_end_date < p_start_date THEN
        RAISE EXCEPTION
            'Contract end date % cannot be before start date %.',
            p_end_date,
            p_start_date;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM kbnteam.company c
        WHERE c.company_id = p_company_id
    ) THEN
        RAISE EXCEPTION 'Company % does not exist.', p_company_id;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM kbnteam.restaurant r
        WHERE r.rest_id = p_rest_id
    ) THEN
        RAISE EXCEPTION 'Restaurant % does not exist.', p_rest_id;
    END IF;

    SELECT cs.contract_status_id
    INTO v_active_status_id
    FROM kbnteam.contract_status cs
    WHERE lower(cs.contract_status_name) = 'active';

    IF v_active_status_id IS NULL THEN
        RAISE EXCEPTION 'The Active contract status does not exist.';
    END IF;

    SELECT
        ct.contract_id,
        ct.contract_start_date,
        ct.contract_end_date
    INTO
        v_contract_id,
        v_existing_start,
        v_existing_end
    FROM kbnteam.contract ct
    WHERE ct.company_id = p_company_id
      AND ct.rest_id = p_rest_id
      AND ct.contract_status_id = v_active_status_id
      AND p_start_date <= ct.contract_end_date + 1
      AND p_end_date >= ct.contract_start_date - 1
    ORDER BY ct.contract_end_date DESC
    LIMIT 1
    FOR UPDATE;

    IF FOUND THEN
        v_merged_start := LEAST(v_existing_start, p_start_date);
        v_merged_end := GREATEST(v_existing_end, p_end_date);

        IF EXISTS (
            SELECT 1
            FROM kbnteam.contract ct
            WHERE ct.company_id = p_company_id
              AND ct.rest_id = p_rest_id
              AND ct.contract_status_id = v_active_status_id
              AND ct.contract_id <> v_contract_id
              AND v_merged_start <= ct.contract_end_date
              AND v_merged_end >= ct.contract_start_date
        ) THEN
            RAISE EXCEPTION
                'Renewing contract % would overlap another active contract.',
                v_contract_id;
        END IF;

        UPDATE kbnteam.contract ct
        SET contract_start_date = v_merged_start,
            contract_end_date = v_merged_end,
            contract_status_id = v_active_status_id
        WHERE ct.contract_id = v_contract_id;
    ELSE
        IF EXISTS (
            SELECT 1
            FROM kbnteam.contract ct
            WHERE ct.company_id = p_company_id
              AND ct.rest_id = p_rest_id
              AND ct.contract_status_id = v_active_status_id
              AND p_start_date <= ct.contract_end_date
              AND p_end_date >= ct.contract_start_date
        ) THEN
            RAISE EXCEPTION
                'The requested dates overlap an existing active contract.';
        END IF;

        INSERT INTO kbnteam.contract (
            company_id,
            contract_end_date,
            contract_start_date,
            contract_status_id,
            rest_id
        )
        VALUES (
            p_company_id,
            p_end_date,
            p_start_date,
            v_active_status_id,
            p_rest_id
        );
    END IF;
END;
$$;
}}}

== Процедура 2: pr_create_company_order ==

Проверува дали компанијата постои и создава компаниска нарачка без доделена достава. Новиот идентификатор се враќа преку параметарот p_comp_order_id.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE PROCEDURE kbnteam.pr_create_company_order(
    p_company_id INTEGER,
    INOUT p_comp_order_id INTEGER DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM kbnteam.company c
        WHERE c.company_id = p_company_id
    ) THEN
        RAISE EXCEPTION 'Company % does not exist.', p_company_id;
    END IF;

    INSERT INTO kbnteam.company_order (
        company_id,
        delivery_id
    )
    VALUES (
        p_company_id,
        NULL
    )
    RETURNING comp_order_id INTO p_comp_order_id;
END;
$$;
}}}

== Процедура 3: pr_create_customer_order ==

Проверува дали клиентот и компаниската нарачка постојат и припаѓаат на иста компанија. Не дозволува додавање нарачка кон компаниска нарачка за која веќе има фактура. Создава клиентска нарачка со зададениот статус и почетен износ нула.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE PROCEDURE kbnteam.pr_create_customer_order(
    p_customer_user_id INTEGER,
    p_comp_order_id INTEGER,
    p_o_status_id INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_customer_company_id INTEGER;
    v_order_company_id INTEGER;
BEGIN
    SELECT c.company_id
    INTO v_customer_company_id
    FROM kbnteam.customer c
    WHERE c.user_id = p_customer_user_id;

    IF v_customer_company_id IS NULL THEN
        RAISE EXCEPTION 'Customer % does not exist.', p_customer_user_id;
    END IF;

    SELECT co.company_id
    INTO v_order_company_id
    FROM kbnteam.company_order co
    WHERE co.comp_order_id = p_comp_order_id
    FOR UPDATE;

    IF v_order_company_id IS NULL THEN
        RAISE EXCEPTION 'Company order % does not exist.', p_comp_order_id;
    END IF;

    IF v_customer_company_id <> v_order_company_id THEN
        RAISE EXCEPTION
            'Customer % belongs to company %, but company order % belongs to company %.',
            p_customer_user_id,
            v_customer_company_id,
            p_comp_order_id,
            v_order_company_id;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM kbnteam.invoice i
        WHERE i.comp_order_id = p_comp_order_id
    ) THEN
        RAISE EXCEPTION
            'Company order % is finalized and already has an invoice.',
            p_comp_order_id;
    END IF;

    INSERT INTO kbnteam.customer_order (
        comp_order_id,
        customer_user_id,
        o_status_id,
        order_total
    )
    VALUES (
        p_comp_order_id,
        p_customer_user_id,
        p_o_status_id,
        0
    );
END;
$$;
}}}

== Процедура 4: pr_add_order_item ==

Го прифаќа типот meal или drink и проверува дали производот постои. За ресторанот на производот е потребен активен договор со компанијата на клиентот на датумот на нарачката. Не дозволува повторно додавање на истиот производ, измена на нарачка со статус completed, cancelled или refunded, ниту измена по фактурирање. По додавањето повторно го пресметува вкупниот износ.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE PROCEDURE kbnteam.pr_add_order_item(
    p_order_id INTEGER,
    p_item_type VARCHAR(10),
    p_item_id INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_item_type TEXT;
    v_company_id INTEGER;
    v_rest_id INTEGER;
    v_order_date DATE;
    v_order_status TEXT;
BEGIN
    v_item_type := lower(trim(p_item_type));

    IF v_item_type NOT IN ('meal', 'drink') THEN
        RAISE EXCEPTION
            'Unsupported item type %. Expected meal or drink.',
            p_item_type;
    END IF;

    SELECT
        cu.company_id,
        o.order_datetime::date,
        lower(os.o_status_name)
    INTO
        v_company_id,
        v_order_date,
        v_order_status
    FROM kbnteam.customer_order o
    JOIN kbnteam.customer cu
      ON cu.user_id = o.customer_user_id
    JOIN kbnteam.order_status os
      ON os.o_status_id = o.o_status_id
    WHERE o.order_id = p_order_id
    FOR UPDATE OF o;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Customer order % does not exist.', p_order_id;
    END IF;

    IF v_order_status IN ('completed', 'cancelled', 'refunded') THEN
        RAISE EXCEPTION
            'Items cannot be added to order % while its status is %.',
            p_order_id,
            v_order_status;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM kbnteam.customer_order o
        JOIN kbnteam.invoice i
          ON i.comp_order_id = o.comp_order_id
        WHERE o.order_id = p_order_id
    ) THEN
        RAISE EXCEPTION
            'Items cannot be added to order % because its company order is finalized.',
            p_order_id;
    END IF;

    IF v_item_type = 'meal' THEN
        SELECT m.rest_id
        INTO v_rest_id
        FROM kbnteam.meal m
        WHERE m.meal_id = p_item_id;

        IF NOT FOUND THEN
            RAISE EXCEPTION 'Meal % does not exist.', p_item_id;
        END IF;

        IF NOT kbnteam.fn_has_active_contract(
            v_company_id,
            v_rest_id,
            v_order_date
        ) THEN
            RAISE EXCEPTION
                'Customer company % has no active contract with restaurant % for meal %.',
                v_company_id,
                v_rest_id,
                p_item_id;
        END IF;

        IF EXISTS (
            SELECT 1
            FROM kbnteam.order_meal om
            WHERE om.order_id = p_order_id
              AND om.meal_id = p_item_id
        ) THEN
            RAISE EXCEPTION
                'Meal % is already part of order %.',
                p_item_id,
                p_order_id;
        END IF;

        INSERT INTO kbnteam.order_meal (meal_id, order_id)
        VALUES (p_item_id, p_order_id);
    ELSE
        SELECT d.rest_id
        INTO v_rest_id
        FROM kbnteam.drink d
        WHERE d.drink_id = p_item_id;

        IF NOT FOUND THEN
            RAISE EXCEPTION 'Drink % does not exist.', p_item_id;
        END IF;

        IF NOT kbnteam.fn_has_active_contract(
            v_company_id,
            v_rest_id,
            v_order_date
        ) THEN
            RAISE EXCEPTION
                'Customer company % has no active contract with restaurant % for drink %.',
                v_company_id,
                v_rest_id,
                p_item_id;
        END IF;

        IF EXISTS (
            SELECT 1
            FROM kbnteam.order_drink od
            WHERE od.order_id = p_order_id
              AND od.drink_id = p_item_id
        ) THEN
            RAISE EXCEPTION
                'Drink % is already part of order %.',
                p_item_id,
                p_order_id;
        END IF;

        INSERT INTO kbnteam.order_drink (drink_id, order_id)
        VALUES (p_item_id, p_order_id);
    END IF;

    UPDATE kbnteam.customer_order o
    SET order_total = kbnteam.fn_calculate_order_total(p_order_id)
    WHERE o.order_id = p_order_id;
END;
$$;
}}}

== Процедура 5: pr_remove_order_item ==

Го отстранува зададениот производ од нарачката и повторно го пресметува износот. Одбива отстранување производ што не е дел од нарачката. Не дозволува измени при статус completed, cancelled или refunded, ниту по фактурирање.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE PROCEDURE kbnteam.pr_remove_order_item(
    p_order_id INTEGER,
    p_item_type VARCHAR(10),
    p_item_id INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_item_type TEXT;
    v_order_status TEXT;
BEGIN
    v_item_type := lower(trim(p_item_type));

    IF v_item_type NOT IN ('meal', 'drink') THEN
        RAISE EXCEPTION
            'Unsupported item type %. Expected meal or drink.',
            p_item_type;
    END IF;

    SELECT lower(os.o_status_name)
    INTO v_order_status
    FROM kbnteam.customer_order o
    JOIN kbnteam.order_status os
      ON os.o_status_id = o.o_status_id
    WHERE o.order_id = p_order_id
    FOR UPDATE OF o;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Customer order % does not exist.', p_order_id;
    END IF;

    IF v_order_status IN ('completed', 'cancelled', 'refunded') THEN
        RAISE EXCEPTION
            'Items cannot be removed from order % while its status is %.',
            p_order_id,
            v_order_status;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM kbnteam.customer_order o
        JOIN kbnteam.invoice i
          ON i.comp_order_id = o.comp_order_id
        WHERE o.order_id = p_order_id
    ) THEN
        RAISE EXCEPTION
            'Items cannot be removed from order % because its company order is finalized.',
            p_order_id;
    END IF;

    IF v_item_type = 'meal' THEN
        DELETE FROM kbnteam.order_meal om
        WHERE om.order_id = p_order_id
          AND om.meal_id = p_item_id;

        IF NOT FOUND THEN
            RAISE EXCEPTION
                'Meal % is not part of order %.',
                p_item_id,
                p_order_id;
        END IF;
    ELSE
        DELETE FROM kbnteam.order_drink od
        WHERE od.order_id = p_order_id
          AND od.drink_id = p_item_id;

        IF NOT FOUND THEN
            RAISE EXCEPTION
                'Drink % is not part of order %.',
                p_item_id,
                p_order_id;
        END IF;
    END IF;

    UPDATE kbnteam.customer_order o
    SET order_total = kbnteam.fn_calculate_order_total(p_order_id)
    WHERE o.order_id = p_order_id;
END;
$$;
}}}

== Процедура 6: pr_submit_customer_order ==

Проверува дали нарачката содржи барем едно јадење или пијалак и дали за секој вклучен ресторан постои активен договор на датумот на нарачката. Не дозволува поднесување при статус completed, cancelled или refunded, ниту по фактурирање. Го пресметува износот и го поставува статусот на pending.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE PROCEDURE kbnteam.pr_submit_customer_order(
    p_order_id INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_company_id INTEGER;
    v_order_date DATE;
    v_order_status TEXT;
    v_pending_status_id INTEGER;
BEGIN
    SELECT
        cu.company_id,
        o.order_datetime::date,
        lower(os.o_status_name)
    INTO
        v_company_id,
        v_order_date,
        v_order_status
    FROM kbnteam.customer_order o
    JOIN kbnteam.customer cu
      ON cu.user_id = o.customer_user_id
    JOIN kbnteam.order_status os
      ON os.o_status_id = o.o_status_id
    WHERE o.order_id = p_order_id
    FOR UPDATE OF o;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Customer order % does not exist.', p_order_id;
    END IF;

    IF v_order_status IN ('completed', 'cancelled', 'refunded') THEN
        RAISE EXCEPTION
            'Order % cannot be submitted while its status is %.',
            p_order_id,
            v_order_status;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM kbnteam.customer_order o
        JOIN kbnteam.invoice i
          ON i.comp_order_id = o.comp_order_id
        WHERE o.order_id = p_order_id
    ) THEN
        RAISE EXCEPTION
            'Order % belongs to a finalized company order.',
            p_order_id;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM kbnteam.order_meal om
        WHERE om.order_id = p_order_id

        UNION ALL

        SELECT 1
        FROM kbnteam.order_drink od
        WHERE od.order_id = p_order_id
    ) THEN
        RAISE EXCEPTION 'Order % cannot be submitted without items.', p_order_id;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM (
            SELECT m.rest_id
            FROM kbnteam.order_meal om
            JOIN kbnteam.meal m
              ON m.meal_id = om.meal_id
            WHERE om.order_id = p_order_id

            UNION

            SELECT d.rest_id
            FROM kbnteam.order_drink od
            JOIN kbnteam.drink d
              ON d.drink_id = od.drink_id
            WHERE od.order_id = p_order_id
        ) item_restaurant
        WHERE NOT kbnteam.fn_has_active_contract(
            v_company_id,
            item_restaurant.rest_id,
            v_order_date
        )
    ) THEN
        RAISE EXCEPTION
            'Order % contains an item from a restaurant without an active contract.',
            p_order_id;
    END IF;

    SELECT os.o_status_id
    INTO v_pending_status_id
    FROM kbnteam.order_status os
    WHERE lower(os.o_status_name) = 'pending';

    IF v_pending_status_id IS NULL THEN
        RAISE EXCEPTION 'The Pending order status does not exist.';
    END IF;

    UPDATE kbnteam.customer_order o
    SET order_total = kbnteam.fn_calculate_order_total(p_order_id),
        o_status_id = v_pending_status_id
    WHERE o.order_id = p_order_id;
END;
$$;
}}}

== Процедура 7: pr_change_order_status ==

Проверува дали нарачката и новиот статус постојат и дали преминот е дозволен. За completed бара барем еден производ и активни договори со вклучените ресторани на датумот на нарачката, а потоа го пресметува износот. По фактурирање дозволува само премин од completed во refunded. При премин во completed, cancelled или refunded ја повикува процедурата за освежување на лојалноста. Повторно задавање на тековниот статус не предизвикува промена.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE PROCEDURE kbnteam.pr_change_order_status(
    p_order_id INTEGER,
    p_new_status_id INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_current_status TEXT;
    v_new_status TEXT;
    v_customer_user_id INTEGER;
    v_company_id INTEGER;
    v_order_date DATE;
    v_transition_allowed BOOLEAN := FALSE;
BEGIN
    SELECT
        lower(os.o_status_name),
        o.customer_user_id,
        cu.company_id,
        o.order_datetime::date
    INTO
        v_current_status,
        v_customer_user_id,
        v_company_id,
        v_order_date
    FROM kbnteam.customer_order o
    JOIN kbnteam.order_status os
      ON os.o_status_id = o.o_status_id
    JOIN kbnteam.customer cu
      ON cu.user_id = o.customer_user_id
    WHERE o.order_id = p_order_id
    FOR UPDATE OF o;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Customer order % does not exist.', p_order_id;
    END IF;

    SELECT lower(os.o_status_name)
    INTO v_new_status
    FROM kbnteam.order_status os
    WHERE os.o_status_id = p_new_status_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order status % does not exist.', p_new_status_id;
    END IF;

    IF v_current_status = v_new_status THEN
        RETURN;
    END IF;

    v_transition_allowed := CASE v_current_status
        WHEN 'pending' THEN
            v_new_status IN ('on-hold', 'backorder', 'completed', 'cancelled')
        WHEN 'on-hold' THEN
            v_new_status IN ('pending', 'backorder', 'completed', 'cancelled')
        WHEN 'backorder' THEN
            v_new_status IN ('pending', 'on-hold', 'completed', 'cancelled')
        WHEN 'completed' THEN
            v_new_status = 'refunded'
        ELSE FALSE
    END;

    IF NOT v_transition_allowed THEN
        RAISE EXCEPTION
            'Order status cannot change from % to %.',
            v_current_status,
            v_new_status;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM kbnteam.customer_order o
        JOIN kbnteam.invoice i
          ON i.comp_order_id = o.comp_order_id
        WHERE o.order_id = p_order_id
    ) AND NOT (
        v_current_status = 'completed'
        AND v_new_status = 'refunded'
    ) THEN
        RAISE EXCEPTION
            'Order % belongs to a finalized company order.',
            p_order_id;
    END IF;

    IF v_new_status = 'completed' THEN
        IF NOT EXISTS (
            SELECT 1
            FROM kbnteam.order_meal om
            WHERE om.order_id = p_order_id

            UNION ALL

            SELECT 1
            FROM kbnteam.order_drink od
            WHERE od.order_id = p_order_id
        ) THEN
            RAISE EXCEPTION 'Order % cannot be completed without items.', p_order_id;
        END IF;

        IF EXISTS (
            SELECT 1
            FROM (
                SELECT m.rest_id
                FROM kbnteam.order_meal om
                JOIN kbnteam.meal m
                  ON m.meal_id = om.meal_id
                WHERE om.order_id = p_order_id

                UNION

                SELECT d.rest_id
                FROM kbnteam.order_drink od
                JOIN kbnteam.drink d
                  ON d.drink_id = od.drink_id
                WHERE od.order_id = p_order_id
            ) item_restaurant
            WHERE NOT kbnteam.fn_has_active_contract(
                v_company_id,
                item_restaurant.rest_id,
                v_order_date
            )
        ) THEN
            RAISE EXCEPTION
                'Order % contains an item from a restaurant without an active contract.',
                p_order_id;
        END IF;

        UPDATE kbnteam.customer_order o
        SET order_total = kbnteam.fn_calculate_order_total(p_order_id)
        WHERE o.order_id = p_order_id;
    END IF;

    UPDATE kbnteam.customer_order o
    SET o_status_id = p_new_status_id
    WHERE o.order_id = p_order_id;

    IF v_new_status IN ('completed', 'cancelled', 'refunded') THEN
        CALL kbnteam.pr_refresh_customer_loyalty(v_customer_user_id);
    END IF;
END;
$$;
}}}

== Процедура 8: pr_finalize_company_order ==

Бара компаниската нарачка да постои, да нема фактура и да содржи клиентски нарачки. Сите клиентски нарачки мора да бидат completed и да содржат барем еден производ. Повторно ги пресметува нивните износи и создава една фактура за компаниската нарачка. Не бара доставата да има статус delivered.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE PROCEDURE kbnteam.pr_finalize_company_order(
    p_comp_order_id INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_company_id INTEGER;
BEGIN
    SELECT co.company_id
    INTO v_company_id
    FROM kbnteam.company_order co
    WHERE co.comp_order_id = p_comp_order_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Company order % does not exist.', p_comp_order_id;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM kbnteam.invoice i
        WHERE i.comp_order_id = p_comp_order_id
    ) THEN
        RAISE EXCEPTION
            'Company order % is already finalized.',
            p_comp_order_id;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM kbnteam.customer_order o
        WHERE o.comp_order_id = p_comp_order_id
    ) THEN
        RAISE EXCEPTION
            'Company order % cannot be finalized without customer orders.',
            p_comp_order_id;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM kbnteam.customer_order o
        JOIN kbnteam.order_status os
          ON os.o_status_id = o.o_status_id
        WHERE o.comp_order_id = p_comp_order_id
          AND lower(os.o_status_name) <> 'completed'
    ) THEN
        RAISE EXCEPTION
            'Every customer order in company order % must be completed before finalization.',
            p_comp_order_id;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM kbnteam.customer_order o
        WHERE o.comp_order_id = p_comp_order_id
          AND NOT EXISTS (
              SELECT 1
              FROM kbnteam.order_meal om
              WHERE om.order_id = o.order_id
          )
          AND NOT EXISTS (
              SELECT 1
              FROM kbnteam.order_drink od
              WHERE od.order_id = o.order_id
          )
    ) THEN
        RAISE EXCEPTION
            'Company order % contains an empty customer order.',
            p_comp_order_id;
    END IF;

    UPDATE kbnteam.customer_order o
    SET order_total = kbnteam.fn_calculate_order_total(o.order_id)
    WHERE o.comp_order_id = p_comp_order_id;

    INSERT INTO kbnteam.invoice (comp_order_id)
    VALUES (p_comp_order_id);
END;
$$;
}}}

== Процедура 9: pr_assign_delivery_to_company_order ==

За компаниска нарачка без достава создава нова достава и ја поврзува со нарачката. Проверува дали доставувачот постои и дали неговиот ресторан има активен договор со компанијата на датумот на доставата. Датумот, статусот и забелешките се запишуваат во новата достава.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE PROCEDURE kbnteam.pr_assign_delivery_to_company_order(
    p_comp_order_id INTEGER,
    p_driver_user_id INTEGER,
    p_d_status_id INTEGER,
    p_delivery_date DATE DEFAULT CURRENT_DATE,
    p_delivery_notes VARCHAR(255) DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_company_id INTEGER;
    v_driver_rest_id INTEGER;
    v_delivery_id INTEGER;
BEGIN
    SELECT co.company_id
    INTO v_company_id
    FROM kbnteam.company_order co
    WHERE co.comp_order_id = p_comp_order_id
    FOR UPDATE;

    IF v_company_id IS NULL THEN
        RAISE EXCEPTION 'Company order % does not exist.', p_comp_order_id;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM kbnteam.company_order co
        WHERE co.comp_order_id = p_comp_order_id
          AND co.delivery_id IS NOT NULL
    ) THEN
        RAISE EXCEPTION 'Company order % already has a delivery.', p_comp_order_id;
    END IF;

    SELECT d.rest_id
    INTO v_driver_rest_id
    FROM kbnteam.driver d
    WHERE d.user_id = p_driver_user_id;

    IF v_driver_rest_id IS NULL THEN
        RAISE EXCEPTION 'Driver % does not exist.', p_driver_user_id;
    END IF;

    IF NOT kbnteam.fn_has_active_contract(v_company_id, v_driver_rest_id, p_delivery_date) THEN
        RAISE EXCEPTION
            'Company % has no active contract with restaurant % for delivery assignment.',
            v_company_id,
            v_driver_rest_id;
    END IF;

    INSERT INTO kbnteam.delivery (
        delivery_date,
        delivery_notes,
        d_status_id,
        driver_user_id
    )
    VALUES (
        p_delivery_date,
        p_delivery_notes,
        p_d_status_id,
        p_driver_user_id
    )
    RETURNING delivery_id INTO v_delivery_id;

    UPDATE kbnteam.company_order
    SET delivery_id = v_delivery_id
    WHERE comp_order_id = p_comp_order_id;
END;
$$;
}}}

== Процедура 10: pr_change_delivery_status ==

Проверува дали доставата и новиот статус постојат и дали преминот е дозволен. Статусите assigned, picked up, on the way, delivered и delayed бараат назначен доставувач. За delivered дополнително бара доставата да биде поврзана со компаниска нарачка. Повторно задавање на тековниот статус не предизвикува промена.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE PROCEDURE kbnteam.pr_change_delivery_status(
    p_delivery_id INTEGER,
    p_new_status_id INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_current_status TEXT;
    v_new_status TEXT;
    v_driver_user_id INTEGER;
    v_transition_allowed BOOLEAN := FALSE;
BEGIN
    SELECT
        lower(ds.d_status_name),
        d.driver_user_id
    INTO
        v_current_status,
        v_driver_user_id
    FROM kbnteam.delivery d
    JOIN kbnteam.delivery_status ds
      ON ds.d_status_id = d.d_status_id
    WHERE d.delivery_id = p_delivery_id
    FOR UPDATE OF d;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Delivery % does not exist.', p_delivery_id;
    END IF;

    SELECT lower(ds.d_status_name)
    INTO v_new_status
    FROM kbnteam.delivery_status ds
    WHERE ds.d_status_id = p_new_status_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Delivery status % does not exist.', p_new_status_id;
    END IF;

    IF v_current_status = v_new_status THEN
        RETURN;
    END IF;

    v_transition_allowed := CASE v_current_status
        WHEN 'pending' THEN
            v_new_status IN ('assigned', 'cancelled')
        WHEN 'assigned' THEN
            v_new_status IN ('picked up', 'delayed', 'failed', 'cancelled')
        WHEN 'picked up' THEN
            v_new_status IN ('on the way', 'delayed', 'failed', 'cancelled')
        WHEN 'on the way' THEN
            v_new_status IN ('delivered', 'delayed', 'failed')
        WHEN 'delayed' THEN
            v_new_status IN (
                'assigned',
                'picked up',
                'on the way',
                'delivered',
                'failed',
                'cancelled'
            )
        ELSE FALSE
    END;

    IF NOT v_transition_allowed THEN
        RAISE EXCEPTION
            'Delivery status cannot change from % to %.',
            v_current_status,
            v_new_status;
    END IF;

    IF v_new_status IN (
        'assigned',
        'picked up',
        'on the way',
        'delivered',
        'delayed'
    ) AND v_driver_user_id IS NULL THEN
        RAISE EXCEPTION
            'Delivery % requires an assigned driver before status can become %.',
            p_delivery_id,
            v_new_status;
    END IF;

    IF v_new_status = 'delivered' AND NOT EXISTS (
        SELECT 1
        FROM kbnteam.company_order co
        WHERE co.delivery_id = p_delivery_id
    ) THEN
        RAISE EXCEPTION
            'Delivery % is not attached to a company order.',
            p_delivery_id;
    END IF;

    UPDATE kbnteam.delivery d
    SET d_status_id = p_new_status_id
    WHERE d.delivery_id = p_delivery_id;
END;
$$;
}}}

== Процедура 11: pr_create_order_review ==

Бара завршена нарачка со статус completed, поврзана со зададениот клиент, и проверува дали веќе постои рецензија за неа. Запишува општа оценка и коментар, како и посебни оценки за храната и ресторанот. Оценките се во опсег од 1 до 5.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE PROCEDURE kbnteam.pr_create_order_review(
    p_order_id INTEGER,
    p_customer_user_id INTEGER,
    p_review_rating INTEGER,
    p_review_comment VARCHAR(255),
    p_food_rating INTEGER,
    p_restaurant_rating INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_customer_user_id INTEGER;
    v_order_status TEXT;
    v_review_id INTEGER;
BEGIN
    IF p_review_rating NOT BETWEEN 1 AND 5
       OR p_food_rating NOT BETWEEN 1 AND 5
       OR p_restaurant_rating NOT BETWEEN 1 AND 5 THEN
        RAISE EXCEPTION 'All review ratings must be between 1 and 5.';
    END IF;

    SELECT
        o.customer_user_id,
        lower(os.o_status_name)
    INTO
        v_order_customer_user_id,
        v_order_status
    FROM kbnteam.customer_order o
    JOIN kbnteam.order_status os
      ON os.o_status_id = o.o_status_id
    WHERE o.order_id = p_order_id
    FOR UPDATE OF o;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Customer order % does not exist.', p_order_id;
    END IF;

    IF v_order_customer_user_id <> p_customer_user_id THEN
        RAISE EXCEPTION
            'Customer % is not the owner of order %.',
            p_customer_user_id,
            p_order_id;
    END IF;

    IF v_order_status <> 'completed' THEN
        RAISE EXCEPTION
            'Order % must be completed before it can be reviewed.',
            p_order_id;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM kbnteam.order_review review_link
        WHERE review_link.order_id = p_order_id
    ) THEN
        RAISE EXCEPTION 'Order % has already been reviewed.', p_order_id;
    END IF;

    INSERT INTO kbnteam.review (
        review_comment,
        review_rating
    )
    VALUES (
        p_review_comment,
        p_review_rating
    )
    RETURNING review_id INTO v_review_id;

    INSERT INTO kbnteam.order_review (
        order_id,
        order_review_food_rating,
        order_review_res_rating,
        review_id
    )
    VALUES (
        p_order_id,
        p_food_rating,
        p_restaurant_rating,
        v_review_id
    );
END;
$$;
}}}

== Процедура 12: pr_create_delivery_review ==

Бара достава со статус delivered и клиент што има нарачка поврзана со таа достава. Не дозволува втора рецензија за истата достава, независно од клиентот. Запишува општа оценка и коментар, како и оценки за доставувачот и брзината, во опсег од 1 до 5.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE PROCEDURE kbnteam.pr_create_delivery_review(
    p_delivery_id INTEGER,
    p_customer_user_id INTEGER,
    p_review_rating INTEGER,
    p_review_comment VARCHAR(255),
    p_courier_rating INTEGER,
    p_speed_rating INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_delivery_status TEXT;
    v_review_id INTEGER;
BEGIN
    IF p_review_rating NOT BETWEEN 1 AND 5
       OR p_courier_rating NOT BETWEEN 1 AND 5
       OR p_speed_rating NOT BETWEEN 1 AND 5 THEN
        RAISE EXCEPTION 'All review ratings must be between 1 and 5.';
    END IF;

    SELECT lower(ds.d_status_name)
    INTO v_delivery_status
    FROM kbnteam.delivery d
    JOIN kbnteam.delivery_status ds
      ON ds.d_status_id = d.d_status_id
    WHERE d.delivery_id = p_delivery_id
    FOR UPDATE OF d;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Delivery % does not exist.', p_delivery_id;
    END IF;

    IF v_delivery_status <> 'delivered' THEN
        RAISE EXCEPTION
            'Delivery % must be delivered before it can be reviewed.',
            p_delivery_id;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM kbnteam.company_order co
        JOIN kbnteam.customer_order o
          ON o.comp_order_id = co.comp_order_id
        WHERE co.delivery_id = p_delivery_id
          AND o.customer_user_id = p_customer_user_id
    ) THEN
        RAISE EXCEPTION
            'Customer % is not associated with delivery %.',
            p_customer_user_id,
            p_delivery_id;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM kbnteam.delivery_review review_link
        WHERE review_link.delivery_id = p_delivery_id
    ) THEN
        RAISE EXCEPTION
            'Delivery % has already been reviewed.',
            p_delivery_id;
    END IF;

    INSERT INTO kbnteam.review (
        review_comment,
        review_rating
    )
    VALUES (
        p_review_comment,
        p_review_rating
    )
    RETURNING review_id INTO v_review_id;

    INSERT INTO kbnteam.delivery_review (
        del_review_courier_rating,
        del_review_speed_rating,
        delivery_id,
        review_id
    )
    VALUES (
        p_courier_rating,
        p_speed_rating,
        p_delivery_id,
        v_review_id
    );
END;
$$;
}}}

== Процедура 13: pr_refresh_customer_loyalty ==

Ги пресметува поените на клиентот и избира ниво чиј опсег ги содржи поените. При повеќе совпаѓања го избира нивото со најголем долен праг; ако нема совпаѓање, го избира нивото со најмал долен праг. Го користи статусот на лојалност со најмал идентификатор. Создава членство или ги ажурира поените, нивото и статусот на постојното членство.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE PROCEDURE kbnteam.pr_refresh_customer_loyalty(
    p_customer_user_id INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_points INTEGER;
    v_tier_id INTEGER;
    v_status_id INTEGER;
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM kbnteam.customer c
        WHERE c.user_id = p_customer_user_id
    ) THEN
        RAISE EXCEPTION 'Customer % does not exist.', p_customer_user_id;
    END IF;

    v_points := kbnteam.fn_calculate_customer_loyalty_points(p_customer_user_id);

    SELECT lt.tier_id
    INTO v_tier_id
    FROM kbnteam.loyalty_tier lt
    WHERE v_points BETWEEN lt.tier_minimum_points AND lt.tier_maximum_points
    ORDER BY lt.tier_minimum_points DESC
    LIMIT 1;

    IF v_tier_id IS NULL THEN
        SELECT lt.tier_id
        INTO v_tier_id
        FROM kbnteam.loyalty_tier lt
        ORDER BY lt.tier_minimum_points
        LIMIT 1;
    END IF;

    SELECT cls.cus_loyalty_status_id
    INTO v_status_id
    FROM kbnteam.customer_loyalty_status cls
    ORDER BY cls.cus_loyalty_status_id
    LIMIT 1;

    IF v_status_id IS NULL THEN
        RAISE EXCEPTION 'No customer loyalty status rows exist.';
    END IF;

    INSERT INTO kbnteam.customer_loyalty (
        cus_loyalty_curr_points,
        cus_loyalty_status_id,
        user_id,
        tier_id
    )
    VALUES (
        v_points,
        v_status_id,
        p_customer_user_id,
        v_tier_id
    )
    ON CONFLICT (user_id) DO UPDATE
    SET cus_loyalty_curr_points = EXCLUDED.cus_loyalty_curr_points,
        cus_loyalty_status_id = EXCLUDED.cus_loyalty_status_id,
        tier_id = EXCLUDED.tier_id;
END;
$$;
}}}

== Тригер 1: trg_validate_customer_order_company ==

Проверува дали клиентот и компаниската нарачка постојат и припаѓаат на иста компанија. При неуспешна проверка ја прекинува наредбата со исклучок. При успешна проверка го враќа новиот ред (NEW).

Проверката се активира при внесување нарачка и при ажурирање што ги наведува customer_user_id или comp_order_id. Ажурирање само на order_total или статусот не го активира овој тригер.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE FUNCTION kbnteam.tf_validate_customer_order_company()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_customer_company_id INTEGER;
    v_order_company_id INTEGER;
BEGIN
    SELECT c.company_id
    INTO v_customer_company_id
    FROM kbnteam.customer c
    WHERE c.user_id = NEW.customer_user_id;

    SELECT co.company_id
    INTO v_order_company_id
    FROM kbnteam.company_order co
    WHERE co.comp_order_id = NEW.comp_order_id;

    IF v_customer_company_id IS NULL THEN
        RAISE EXCEPTION 'Customer % does not exist.', NEW.customer_user_id;
    END IF;

    IF v_order_company_id IS NULL THEN
        RAISE EXCEPTION 'Company order % does not exist.', NEW.comp_order_id;
    END IF;

    IF v_customer_company_id <> v_order_company_id THEN
        RAISE EXCEPTION
            'Customer % belongs to company %, but company order % belongs to company %.',
            NEW.customer_user_id,
            v_customer_company_id,
            NEW.comp_order_id,
            v_order_company_id;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_validate_customer_order_company
ON kbnteam.customer_order;

CREATE TRIGGER trg_validate_customer_order_company
BEFORE INSERT OR UPDATE OF customer_user_id, comp_order_id
ON kbnteam.customer_order
FOR EACH ROW
EXECUTE FUNCTION kbnteam.tf_validate_customer_order_company();
}}}

== Тригер 2: trg_order_meal_guard_and_total ==

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

Ако со UPDATE ставката се префрли во друга нарачка, ги освежува износите и на новата и на старата нарачка.

Проверките за статус на нарачката и постоење фактура се дел од процедурите за додавање и отстранување производи; овој тригер ги проверува договорите и го освежува износот.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE FUNCTION kbnteam.tf_order_meal_guard_and_total()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_target_order_id INTEGER;
    v_old_order_id INTEGER;
    v_company_id INTEGER;
    v_rest_id INTEGER;
    v_order_date DATE;
BEGIN
    IF TG_OP <> 'DELETE' THEN
        SELECT
            cu.company_id,
            m.rest_id,
            o.order_datetime::date
        INTO
            v_company_id,
            v_rest_id,
            v_order_date
        FROM kbnteam.customer_order o
        JOIN kbnteam.customer cu
          ON cu.user_id = o.customer_user_id
        JOIN kbnteam.meal m
          ON m.meal_id = NEW.meal_id
        WHERE o.order_id = NEW.order_id;

        IF v_company_id IS NULL THEN
            RAISE EXCEPTION 'Order % is not linked to a valid customer/company.', NEW.order_id;
        END IF;

        IF NOT kbnteam.fn_has_active_contract(v_company_id, v_rest_id, v_order_date) THEN
            RAISE EXCEPTION
                'Customer company % has no active contract with restaurant % for meal %.',
                v_company_id,
                v_rest_id,
                NEW.meal_id;
        END IF;

        v_target_order_id := NEW.order_id;
    ELSE
        v_target_order_id := OLD.order_id;
    END IF;

    UPDATE kbnteam.customer_order o
    SET order_total = kbnteam.fn_calculate_order_total(v_target_order_id)
    WHERE o.order_id = v_target_order_id;

    IF TG_OP = 'UPDATE' AND OLD.order_id <> NEW.order_id THEN
        v_old_order_id := OLD.order_id;

        UPDATE kbnteam.customer_order o
        SET order_total = kbnteam.fn_calculate_order_total(v_old_order_id)
        WHERE o.order_id = v_old_order_id;
    END IF;

    RETURN COALESCE(NEW, OLD);
END;
$$;

DROP TRIGGER IF EXISTS trg_order_meal_guard_and_total
ON kbnteam.order_meal;

CREATE TRIGGER trg_order_meal_guard_and_total
AFTER INSERT OR UPDATE OR DELETE
ON kbnteam.order_meal
FOR EACH ROW
EXECUTE FUNCTION kbnteam.tf_order_meal_guard_and_total();
}}}

== Тригер 3: trg_order_drink_guard_and_total ==

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

Ако со UPDATE ставката се префрли во друга нарачка, ги освежува износите и на новата и на старата нарачка.

Проверките за статус на нарачката и постоење фактура се дел од процедурите за додавање и отстранување производи; овој тригер ги проверува договорите и го освежува износот.

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE FUNCTION kbnteam.tf_order_drink_guard_and_total()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_target_order_id INTEGER;
    v_old_order_id INTEGER;
    v_company_id INTEGER;
    v_rest_id INTEGER;
    v_order_date DATE;
BEGIN
    IF TG_OP <> 'DELETE' THEN
        SELECT
            cu.company_id,
            d.rest_id,
            o.order_datetime::date
        INTO
            v_company_id,
            v_rest_id,
            v_order_date
        FROM kbnteam.customer_order o
        JOIN kbnteam.customer cu
          ON cu.user_id = o.customer_user_id
        JOIN kbnteam.drink d
          ON d.drink_id = NEW.drink_id
        WHERE o.order_id = NEW.order_id;

        IF v_company_id IS NULL THEN
            RAISE EXCEPTION 'Order % is not linked to a valid customer/company.', NEW.order_id;
        END IF;

        IF NOT kbnteam.fn_has_active_contract(v_company_id, v_rest_id, v_order_date) THEN
            RAISE EXCEPTION
                'Customer company % has no active contract with restaurant % for drink %.',
                v_company_id,
                v_rest_id,
                NEW.drink_id;
        END IF;

        v_target_order_id := NEW.order_id;
    ELSE
        v_target_order_id := OLD.order_id;
    END IF;

    UPDATE kbnteam.customer_order o
    SET order_total = kbnteam.fn_calculate_order_total(v_target_order_id)
    WHERE o.order_id = v_target_order_id;

    IF TG_OP = 'UPDATE' AND OLD.order_id <> NEW.order_id THEN
        v_old_order_id := OLD.order_id;

        UPDATE kbnteam.customer_order o
        SET order_total = kbnteam.fn_calculate_order_total(v_old_order_id)
        WHERE o.order_id = v_old_order_id;
    END IF;

    RETURN COALESCE(NEW, OLD);
END;
$$;

DROP TRIGGER IF EXISTS trg_order_drink_guard_and_total
ON kbnteam.order_drink;

CREATE TRIGGER trg_order_drink_guard_and_total
AFTER INSERT OR UPDATE OR DELETE
ON kbnteam.order_drink
FOR EACH ROW
EXECUTE FUNCTION kbnteam.tf_order_drink_guard_and_total();
}}}