

CREATE OR REPLACE FUNCTION get_invoice_hours(
    p_project_id INT,
    p_filter_conditions JSONB DEFAULT NULL
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $function$
DECLARE
    v_total_hours NUMERIC := 0;
    v_query TEXT;
    v_transformed_filter_conditions TEXT;
BEGIN
    v_query := 'SELECT COALESCE(SUM(invoice_hours), 0) 
                FROM project_invoice_details
                WHERE project_id = $1';

    IF p_filter_conditions IS NOT NULL AND p_filter_conditions <> '{}'::jsonb THEN
        v_transformed_filter_conditions := COALESCE(get_transformed_filter_conditions(p_filter_conditions), '');

        IF TRIM(v_transformed_filter_conditions) <> '' THEN
            v_query := v_query || ' AND (' || v_transformed_filter_conditions || ')';
        END IF;
    END IF;

    EXECUTE v_query INTO v_total_hours USING p_project_id;
    RETURN v_total_hours;
END;
$function$;