select 'Creating procedures, see README for usage' as note;

drop procedure if exists README;
drop procedure if exists CSV_DIR;
drop procedure if exists LOG_LEVEL;
drop procedure if exists drop_table;
drop procedure if exists Session;
drop procedure if exists log;
drop procedure if exists tail;
drop procedure if exists head;
drop procedure if exists etime;
drop procedure if exists mem;
drop procedure if exists ps;
drop procedure if exists csvread;
drop procedure if exists csvwrite;
drop procedure if exists insert_FeatureMatrixOnt;

drop procedure if exists README;
delimiter //
create procedure README()
begin
  select * from README;
end//
delimiter ;

drop procedure if exists DATASET;
delimiter //
create procedure DATASET(user_dataset varchar(10))
begin
  if @DATASET is null then
    call SessionResume;
  end if;

  select user_dataset into @DATASET;

  call log('DATASET', @DATASET) ;
  call Session;

end//
delimiter ;

drop procedure if exists USE_TRAIN;
delimiter //
create procedure USE_TRAIN()
begin

  call log('DATASET', 'SWITCH') ;
  call DATASET('TRAIN');
end//
delimiter ;

drop procedure if exists USE_TEST;
delimiter //
create procedure USE_TEST()
begin

  call log('DATASET', 'SWITCH') ;
  call DATASET('TEST');
end//
delimiter ;


drop procedure if exists CSV_DIR;
delimiter //
create procedure CSV_DIR(csv_dir tinyint)
begin
  select csv_dir into @CSV_DIR;
  call log('CSV_DIR', log_level) ;
end//
delimiter ;

drop procedure if exists LOG_LEVEL;
delimiter //
create procedure LOG_LEVEL(log_level tinyint)
begin

  select log_level into @LOG_LEVEL;
  call log('LOG_LEVEL', log_level) ;
end//
delimiter ;


CREATE TABLE Session (
  DATASET varchar(5),
  CSV_DIR varchar(1000),
  LOG_LEVEL tinyint
);

alter  table Session add column session_id    int         UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
alter  table Session add column session_start timestamp   default now();

drop procedure if exists Session;
delimiter //
create procedure Session()
begin

   call SessionResume;

   if @DATASET is null then

     call README;
     select '@@@@' as warning;
     select '@DATASET is null? which one to use, train or test?' as warning;
     select 'Terminating now.' from _WARNING_TRAIN_OR_TEST;
   end if;

   insert into Session(LOG_LEVEL, DATASET, CSV_DIR) select @LOG_LEVEL, @DATASET, @CSV_DIR;

   select max(session_id) into @session_id from Session;
   select concat('@DATASET=', @DATASET,' @CSV_DIR=',@CSV_DIR, ' @session_id=', @session_id,' @LOG_LEVEL=', @LOG_LEVEL)
   as 'your current variable settings = ';

   call log('Session', 'started');

end//
delimiter ;

-- TODO: @@@NOTICE:
insert into Session (DATASET, CSV_DIR, LOG_LEVEL) select 'TEST', '/tmp/mysql/', 1;

drop procedure if exists SessionResume;
delimiter //
create procedure SessionResume()
begin
   if @CSV_DIR is null then
     select CSV_DIR into @CSV_DIR from Session order by session_id desc limit 1;
   end if;

   if @DATASET is null then
     select DATASET   into @DATASET   from Session order by session_id desc limit 1;
   end if;

   if @LOG_LEVEL is null then
     select LOG_LEVEL into @LOG_LEVEL from Session order by session_id desc limit 1;
   end if;
end//
delimiter ;


CREATE TABLE log
(
  event_time   timestamp    default now(),
  session_id   integer      NOT NULL,
  entity_name     varchar(100) NOT NULL,
  message      varchar(100) NULL,
  DATASET      varchar(5)   NOT null default '?????'
);
ALTER TABLE log add idx smallint UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

drop procedure if exists log;
delimiter //
create procedure log(log_entity_name varchar(100), log_message varchar(100))
begin

   insert into log
   (DATASET, session_id,  entity_name,     message) select
   @DATASET, @session_id, log_entity_name, log_message;

   if @LOG_LEVEL > 0 then
    select concat(DATASET, ',', entity_name, ',',message) as '' from log order by idx desc limit 1;
   end if;
end//
delimiter ;

call Session;
call log('LOG', 'create table');


drop procedure if exists tail;
delimiter //
create procedure tail()
begin
  select event_time, DATASET, entity_name, message from log order by idx DESC limit 50;
end//
delimiter ;

drop procedure if exists head;
delimiter //
create procedure head()
begin
  select event_time, DATASET, entity_name, message from log order by idx ASC limit 50;
end//
delimiter ;

drop procedure if exists etime;
delimiter //
create procedure etime()
begin
  select l1.idx, l1.event_time, l1.entity_name, l1.message, l1.event_time,
  UNIX_TIMESTAMP(l1.event_time)-UNIX_TIMESTAMP(l2.event_time) as etime
  from log l1, log l2
  where l1.idx = l2.idx+1
  order by l1.idx asc;
end//
delimiter ;

drop procedure if exists mem;
delimiter //
create procedure mem()
begin
  select
  table_schema,
  ENGINE,
  TABLE_NAME,
  TABLE_ROWS,
  DATA_LENGTH,
  concat( round( data_length / ( 1024 *1024 ) , 2 ) , 'M' )  data_length,
  concat( round( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length
  from
  information_schema.TABLES
  where
  TABLE_SCHEMA = 'scrubber'
  order by
  table_schema, engine, table_name;
end//
delimiter ;

drop procedure if exists ps;
delimiter //
create procedure ps()
begin
  select * from information_schema.processlist
  where information_schema.processlist.INFO not like '%information_schema.processlist%';
end//
delimiter ;

drop procedure if exists csvread;
delimiter //
create procedure csvread(mytable varchar(500))
begin

  select concat( ' load data '
                    ' local infile ', "'", concat(@CSV_DIR, mytable, '.csv'), "'",
                    ' into table ', mytable,
                    ' fields  terminated by ',"'", ",","'",' ignore 1 lines') into @csvout;

  select @csvout;

  prepare stmt from @csvout;
  execute stmt;
end//
delimiter ;

drop procedure if exists csvwrite;
delimiter //
create procedure csvwrite(mytable varchar(500))
begin

  select concat(mytable, '.csv') into @table_csv;

  select concat( ' SELECT * from ', mytable,
                    ' into outfile ', "'", concat(@CSV_DIR, @table_csv), "'",
                    ' fields terminated by ', "'", ',', "'"
                    ' lines  terminated by ', "'", '\\n', "'") into @csvout;

--   select @csvout;

  prepare stmt from @csvout;
  execute stmt;
end//
delimiter ;

drop procedure if exists drop_table;
delimiter //
create procedure drop_table(table_to_drop varchar(50))
begin

  set @s:=concat('drop table if exists ', table_to_drop);

  call log(table_to_drop, 'drop_table()');

  prepare stmt from @s;
  execute stmt;
end//
delimiter ;


-- TODO : refactor below
-- call log('insert_FeatureMatrixOnt()', '');

drop procedure if exists insert_FeatureMatrixOnt;
delimiter //
create procedure insert_FeatureMatrixOnt(ontology varchar(100))
begin

call log(ontology, 'temp');


create temporary table tmp_OM select file_idx, start_idx, end_idx, f.feature_idx, token from OntologyMatch om, Feature f
  where om.match_source like ontology and om.match_source = f.match_value;

alter table tmp_OM add index (file_idx, start_idx, end_idx);

select count(*) into @count_before from FeatureMatrixOnt;

call log('insert_FeatureMatrixOnt(before)', @count_before);

insert into FeatureMatrixOnt
select  s.file_idx, s.sample_idx,o.feature_idx, count(*) as count_matches
from    Sample s, tmp_OM o
where   o.file_idx        = s.file_idx      and
        o.start_idx       <= s.start_idx    and
        o.end_idx         >= s.end_idx
group by  s.file_idx, s.sample_idx,o.feature_idx;

select count(*) into @count_now from FeatureMatrixOnt;
select @count_now-@count_before into @count_diff;

call log(ontology, @count_diff);
call log('insert_FeatureMatrixOnt(now)', @count_now);

call log(ontology, 'done');
call log('insert_FeatureMatrixOnt()', ontology);

end//
delimiter ;

call log('procedures.sql', 'done');



