DELIMITER $$
use veev2 $$

-- Compute all values possible, given the current param_values
DROP PROCEDURE IF EXISTS `computeValues`$$
CREATE PROCEDURE `computeValues`()
READS SQL DATA
BEGIN

DECLARE rId, maxCnt, nbMax, isComputed, missingDeps, computedValId int;
DECLARE done BOOL DEFAULT FALSE;

DECLARE cur
	CURSOR FOR
	SELECT r.id
	from r_result r;

DECLARE
	CONTINUE HANDLER FOR
	SQLSTATE '02000'
	SET done = TRUE;

create temporary table tmp_possibles
(result_id bigint, resultval_id bigint, value varchar(255), cnt int);

OPEN cur;
	firstLoop: LOOP
		FETCH cur INTO rId;

		IF done THEN
		  CLOSE cur;
		  LEAVE firstLoop;
		END IF;
		
		-- Determining if we've already computed this result
		select case when cnt > 0 then 1 else 0 end into isComputed
		from
			(select count(*) as cnt
			from computed_val cv
			inner join r_result_val rv on cv.resultval_id=rv.id
			where rv.result_id = rId) a;
		
		-- Defining if we're missing a dependency or not
		select count(*) into missingDeps
		from dependency d
		where (d.result_id, d.input_id) not in
			(select r.id, i.id
			from param_val pv
			inner join r_value_assoc va on va.inputval_id=pv.inputval_id
			inner join r_result_val rv on rv.id=va.resultval_id
			inner join r_input_val iv on iv.id=pv.inputval_id
			inner join r_result r on r.id=rv.result_id
			inner join r_input i on i.id=iv.input_id
			where r.id=d.result_id)
		and d.result_id = rId;

		
		if ( isComputed + missingDeps = 0 ) 
		then
			
			delete from tmp_possibles;
			select null into maxCnt;
			select null into nbMax;
			select null into computedValId;
			
			-- we store the possible resultvals matching the current result, and their occurence in the association table
			insert into tmp_possibles(result_id, resultval_id, value, cnt)
			select rId, a.resultval_id, a.value, count(*) as cnt
			from 
			  (select rv.result_id, rv.id, va.resultval_id, rv.value
			  from r_value_assoc va
			  inner join param_val pv on va.inputval_id=pv.inputval_id
			  inner join r_result_val rv on rv.id=va.resultval_id) a
			inner join r_result r on r.id = a.result_id
			where r.id=rId
			group by r.id, a.resultval_id;

			-- we store the maximum occurance of a given result_val
			select max(cnt) into maxCnt
			from tmp_possibles;

			-- now we check if there's only one result_val that has the best matching with the param_values.
			-- If so, it's a computed value, and we must store it
			select count(*) into nbMax
			from tmp_possibles 
			where cnt = maxCnt;

			if(nbMax = 1)
			then
				select resultval_id into computedValId
				from tmp_possibles
				where cnt=maxCnt;
				
				insert into computed_val(resultval_id)
				values(computedValId);
				
				-- if this computed_val is also an input, store it in param_val
				-- TODO: replace this with a simple to the 'storeTableParam' procedure, which does exactly that
				insert into param_val(inputval_id)
				select iv.id
				from r_input_val iv
				inner join r_result_val rv on rv.value = iv.value
				inner join r_input i on i.id=iv.input_id
				inner join r_result r on r.id=rv.result_id
				where rv.id=computedValId
				and r.name = i.name;
			end if;
			
		end if;
	END LOOP;
	
END$$