delimiter $

create function not_enough(p_code int, p_amount int) /* product code, required amount */
returns int /* 0: enough, 1: not enough */
comment 'recursive'
begin
  declare v_no_more int default 0;
  declare v_result int default 0;
  declare v_code int;
  declare v_amount int;
  
  declare c_compound cursor for 
    select p.code_tool, p.size_tool from product p
    where p.code = p_code and p.type_tool = 1;
    
  declare continue handler for not found set v_no_more = 1;
  
  /* check simple tools */
  if exists (
    select 'x' from product p, tool p
    where p.code = p_code and
        p.type_tool = 0 and 
        t.id = p.code_tool and
        t.size < p.size_tool * p_amount
  ) then 
    return 1; 
  end if;

  /* check compound tools */
  open c_compound;
  loop
    fetch c_compound into v_code, v_amount;
    if v_no_more then leave; end if;
    
    set v_result = not_enough(v_code, v_amount);
    if v_result then leave; end if;
  end loop;
  close c_compound;
  
  return v_result;
  
end$

delimiter ;

create function has(item_id int)
returns int
begin
  declare v_result int default 1;
  declare v_no_more int default 0;
  declare v_code int;
  declare v_size int;  
  
  declare queue cursor for
    select item_id code_tool, 1 size_tool from dual
    union all
    select code_tool, size_tool from product
    where code = item_id and type_tool = 1;
    
  declare continue handler for not found set v_no_more = 1;

  
  open queue;
  loop
    fetch queue into v_code, v_size;
    if v_no_more then leave; end if;
    
    if exists (
      select 'x' from product p, tool t
      where p.code = v_code and p.type_tool = 0 and t.id = p.code_tool and 
        p.size_tool * v_size > t.size
    ) then 
      set v_result = 0;
      leave;
    end if;
  end loop;
  close queue;

  return v_result;
end

create function enough(p_code int, p_amount int, p_root int) /* product code, amount, 'root flag' must be 1 */
returns int /* 0: insufficient fonds, 1: enough fonds */
comment 'recursive, creates temporary table `needs`'
begin
  declare v_no_more int default 0;
  declare v_dummy int;
  declare v_code int;
  declare v_amount int;
  
  declare c_compound cursor for 
    select p.code_tool, p.size_tool from product p
    where p.code = p_code and p.type_tool = 1;
    
  declare continue handler for not found set v_no_more = 1;
  
  /* accumulator */
  if p_root then
    drop temporary table if exists needs;
    create temporary table needs(code int, size int) engine=MEMORY;
  end if;
  
  /* process simple tools */
  insert into needs(code, size)
  select p.code_tool, p.size_tool * p_amount
  from product p
  where p.code = p_code and p.type_tool = 0;
  
  /* process compound tools */
  open c_compound;
  loop
    fetch c_compound into v_code, v_amount;
    if v_no_more then leave; end if;
    
    set v_dummy = enough( v_code, v_amount, 0 );
  end loop;
  close c_compound;
  
  /* check tools amount */
  if p_root then
    alter table need add key IDX_CODE(code);
    
    if exists (
      select 'x' 
      from (select code, sum(size) need from needs group by 1) n, tools t
      where t.id = n.code and t.size < n.need
    ) then
      return 0;
    end if;
    
  end if;
   
  return 1;
end