call Session;

call log('Sample',   'begin');
call log('Sample',   'create');
call log('Sample',   'BaseToken');

call drop_table('Sample');

create table Sample
  select * from BaseToken where
  annotation_type = 'NumToken' or
  annotation_type = 'WordToken'
  order by file_idx, token_seq asc;

call log('Sample',   'BaseToken');
call log('Sample',   'index');

alter table Sample engine = myisam;
alter table Sample add class_idx  tinyint UNSIGNED NULL;
alter table Sample add sample_idx integer UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

alter table Sample add index (file_idx);
alter table Sample add index (file_idx, token_seq);
alter table Sample add index (file_idx, start_idx, end_idx);

call log('Sample',   'index:done');
call log('Sample',   'class:begin');

call log('Class', 'table');
create table Class
(
  class_idx   tinyint unsigned,
  class       varchar(10) not null
)
engine = myisam;

insert into Class values
 (1, 'NA'),
 (2, 'AGE'),
 (3, 'DATE'),
 (4, 'DOCTOR'),
 (5, 'HOSPITAL'),
 (6, 'ID'),
 (7, 'LOCATION'),
 (8, 'PATIENT'),
 (9, 'PHONE');

call log('Class',   'done');

update Sample s, Class c
set   s.class_idx = c.class_idx
where s.class = c.class;

update Sample set class_idx = 0 where class_idx is null;

call log('Sample',   'class:done');
call log('Sample',   'sentence');

call drop_table('tmp_sentence_bounds');

create temporary table tmp_sentence_bounds select
file_idx, sentence_seq, min(sample_idx) sample_idx_start, max(sample_idx) sample_idx_end
from Sample group by file_idx, sentence_seq order by file_idx, sentence_seq;

call drop_table('tmp_Sentence ');

create table tmp_Sentence select
 annotation_type   ,
 filename_short    ,
 start_idx         ,
 end_idx           ,
 sentence_seq      ,
 file_idx
from Sentence
order by file_idx, sentence_seq;

alter table tmp_Sentence add column sample_idx_start mediumint unsigned;
alter table tmp_Sentence add column sample_idx_end   mediumint unsigned;

alter table tmp_Sentence add index (file_idx, sentence_seq);
alter table tmp_Sentence add index (file_idx, sample_idx_start, sample_idx_end);

update tmp_Sentence s,  tmp_sentence_bounds bounds
set    s.sample_idx_start = bounds.sample_idx_start,
       s.sample_idx_end   = bounds.sample_idx_end
where  s.file_idx         = bounds.file_idx      and
       s.sentence_seq     = bounds.sentence_seq     ;

call drop_table('EmptySentence');

create table EmptySentence
select * from tmp_Sentence where sample_idx_end = 0;
delete from tmp_Sentence where sample_idx_end = 0;

alter table Sentence rename to dropme;
alter table tmp_Sentence   rename to Sentence;

call log('Sample', 'sentence:index');

alter table Sentence  add column sentence_idx integer UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
alter table Sample    add column sentence_idx smallint unsigned ;
drop table  dropme;

update Sample sample,    Sentence s
set    sample.sentence_idx  = s.sentence_idx
where  sample.file_idx      = s.file_idx      and
       sample.start_idx    >= s.start_idx     and
       sample.end_idx      <= s.end_idx         ;

call log('Sample',  'sentence:done');
call log('Sample',  'done');
call log('TermFrequency', 'begin');

call drop_table('TermFrequency');
call        log('TermFrequency', 'create');

create table TermFrequency
(
  file_idx      smallint unsigned,
  sample_idx    int(10)  unsigned,

  start_idx  smallint unsigned,
  end_idx    smallint unsigned,

  token varchar(100) not null null,
  token_len tinyint unsigned,

  pos  varchar(25) ,
  tf_ham_with_pos     double default -1,
  tf_ham_without_pos  double default -1

) engine = myisam;

insert into TermFrequency (file_idx, sample_idx, start_idx, end_idx, pos, token, token_len)
select file_idx, sample_idx, start_idx, end_idx, pos, token, length(token)
from Sample order by sample_idx;

alter table TermFrequency add index (file_idx, sample_idx, start_idx);
alter table TermFrequency add index (pos);

update  TermFrequency tf, Calculation c
set     tf_ham_without_pos = match_value
where   'tf_ham_without_pos' = c.match_source   and
        tf.file_idx  = c.file_idx               and
        tf.start_idx = c.start_idx              and
        tf.end_idx   = c.end_idx                   ;

update  TermFrequency tf, Calculation c
set     tf_ham_with_pos = match_value
where   'tf_ham_with_pos' = c.match_source   and
        tf.file_idx  = c.file_idx            and
        tf.start_idx = c.start_idx           and
        tf.end_idx   = c.end_idx               ;

call        log('Sample',   'done');

