CREATE OR REPLACE FUNCTION get_min_production_stats(
    date_from DATE,
    date_to   DATE
)
RETURNS TABLE (
    min_items_count    BIGINT,   
    min_products_count BIGINT, 
    min_total_amount   NUMERIC,   
    production_date    DATE
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    WITH stats AS (
        SELECT
            p.production_id,
            p.date,
            p.total_amount,
            COUNT(pi.item_id)       AS items_cnt,
            COALESCE(SUM(pi.quantity), 0) AS prod_cnt
        FROM Production p
        LEFT JOIN ProductionItems pi ON pi.production_id = p.production_id
        WHERE p.date BETWEEN date_from AND date_to
        GROUP BY p.production_id, p.date, p.total_amount
    )
    SELECT
        MIN(s.items_cnt),
        MIN(s.prod_cnt),
        MIN(s.total_amount),
        (SELECT s2.date FROM stats s2
         ORDER BY s2.total_amount ASC LIMIT 1)
    FROM stats s;
END;
$$;




CREATE OR REPLACE FUNCTION recalc_production_total()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    target_id INT;
BEGIN

    IF TG_OP = 'DELETE' THEN
        target_id := OLD.production_id;
    ELSE
        target_id := NEW.production_id;
    END IF;


    UPDATE Production
    SET total_amount = COALESCE((
        SELECT SUM(
            pi.quantity * (

                SELECT SUM(s.quantity * m.price)
                FROM Specification s
                JOIN Materials m ON m.material_id = s.material_id
                WHERE s.product_id = pi.product_id
            )
        )
        FROM ProductionItems pi
        WHERE pi.production_id = target_id
    ), 0)
    WHERE production_id = target_id;

    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_recalc_production_total
AFTER INSERT OR UPDATE OR DELETE ON ProductionItems
FOR EACH ROW
EXECUTE FUNCTION recalc_production_total();


UPDATE ProductionItems SET quantity = quantity;  -- триггер сработает

SELECT * FROM Production;

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