/*
Breed @number_of_children from parents @id1 and @id2
*/
create procedure up_breed_children
   @number_of_children int,
   @id1 int, @id2 int,
/* 
chance a mutation will add a column.  If not a column
will be removed 
*/
   @mutation_add_column_probability float(8),
/* chance of a mutation */
   @mutation_probability float(8),
/* probability of the = operator */
   @equality_percent float(8)
as
declare
   @next_id int,
   @children_bred int,
   @crossover_threshold float(8),
   @new_mutation_column varchar(30)

begin

exec up_timing_start 'up_breed_children'


/* get an ordered list of each parent */
select id, identity(int,1,1) as col_order, c_name, operator, c_value
into #t1
from t_profile_column_values
where id = @id1
order by id,c_name

select id, identity(int,1,1) as col_order, c_name, operator, c_value
into #t2
from t_profile_column_values
where id = @id2
/* eliminate duplicates */
and c_name not in (select distinct c_name from #t1)
order by id,c_name

/*
Put them in a single table aligned by column order
*/
select t1.id, t2.id as t2_id, 
   coalesce(t1.col_order, t2.col_order) as col_order,
   t1.c_name as c1, t1.operator as op1, t1.c_value as val1,
   t2.c_name as c2, t2.operator as op2, t2.c_value as val2, 
   cast(-1.0 as float(8)) as random
into #aligned
from #t1 t1 full outer join #t2 t2
on
   t1.col_order = t2.col_order 

/* assign a random number to each pair */
while exists( select * from #aligned where random = -1)
begin
   set rowcount 1
   update #aligned set random = rand() where random = -1
   set rowcount 0
end


select @children_bred = 0
while @children_bred < @number_of_children
begin

	select @children_bred = @children_bred + 1
	
	select @next_id = max(id) + 1
	from t_profile_column_values
	
	
   select @crossover_threshold=rand()

   /*
   Take one parents value or the other depending on 
      @crossover_threshold vs. the row random numner
   */
	insert into t_profile_column_values
	select *
	from
	(
	select @next_id as id, null as test, 
      @id1 as parent1,@id2 as parent2,
	c_name = 
	   case when @crossover_threshold < random then coalesce(c1,c2)
	        else coalesce(c2,c1)
	   end,
	operator = 
	   case when @crossover_threshold < random then coalesce(op1,op2)
	        else coalesce(op2,op1)
	   end,
	c_value = 
	   case when @crossover_threshold < random then coalesce(val1,val2)
	        else coalesce(val2,val1)
	   end
	from #aligned
	) child
	where child.c_name is not null

   /*
   Get this set for the next iteration
   */
   update #aligned set random = -1.0
	while exists( select * from #aligned where random = -1.0)
	begin
	   set rowcount 1
	   update #aligned set random = rand() where random = -1.0
	   set rowcount 0
	end


   /*
   If there's a mutation, either add a new column
   or delete one.
   */
   if rand() < @mutation_probability 
   begin
      if rand() <@mutation_add_column_probability
      begin
         exec up_get_new_random_column
            @profile_id = @next_id,
            @new_column = @new_mutation_column  out
         exec  up_insert_profile_column_row
             @this_column = @new_mutation_column,
             @profile_id = @next_id,
             @parent1 = @id1,
             @parent2 = @id2,
             @equality_percent = @equality_percent
      end
      else
      begin
         set rowcount 1
         delete from t_profile_column_values 
         where id = @next_id
         set rowcount 0
      end
   end

end

exec up_timing_end 'up_breed_children'
end
go
