CREATE OR REPLACE FUNCTION get_max_orders_stats(
    date_from DATE,
    date_to   DATE
)
RETURNS TABLE (
    max_items_count    BIGINT,
    max_products_count BIGINT,
    max_total_amount   NUMERIC,
    customer_name      VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    WITH stats AS (
        SELECT
            o.order_id,
            o.customer_id,
            o.total_amount,
            COUNT(oi.item_id)             AS items_cnt,
            COALESCE(SUM(oi.quantity), 0) AS prod_cnt
        FROM Orders o
        LEFT JOIN OrderItems oi ON oi.order_id = o.order_id
        WHERE o.date BETWEEN date_from AND date_to
        GROUP BY o.order_id, o.customer_id, o.total_amount
    )
    SELECT
        MAX(s.items_cnt),
        MAX(s.prod_cnt),
        MAX(s.total_amount),
        (SELECT c.name
         FROM stats s2
         JOIN Customers c ON c.customer_id = s2.customer_id
         ORDER BY s2.total_amount DESC LIMIT 1)
    FROM stats s;
END;
$$;


SELECT * FROM get_max_orders_stats('2026-06-01', '2026-07-31');


CREATE OR REPLACE FUNCTION recalc_order_total()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    target_id INT;
BEGIN
    IF TG_OP = 'DELETE' THEN
        target_id := OLD.order_id;
    ELSE
        target_id := NEW.order_id;
    END IF;


    UPDATE Orders
    SET total_amount = COALESCE((
        SELECT SUM(
            oi.quantity * (

                SELECT SUM(s.quantity * m.price)
                FROM Specification s
                JOIN Materials m ON m.material_id = s.material_id
                WHERE s.product_id = oi.product_id
            )
        )
        FROM OrderItems oi
        WHERE oi.order_id = target_id
    ), 0)
    WHERE order_id = target_id;

    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_recalc_order_total
AFTER INSERT OR UPDATE OR DELETE ON OrderItems
FOR EACH ROW
EXECUTE FUNCTION recalc_order_total();


UPDATE OrderItems SET quantity = quantity;


SELECT * FROM Orders;


SELECT * FROM get_max_orders_stats('2026-06-01', '2026-07-31');