truncate etl_log;

CALL log('load_csv_annotations.sql', 'begin');

CALL log('Drop CSV tables', 'begin');

CALL log('Drop CSV tables', 'done');
CALL log('Annotation', 'begin');

create table Annotation
(
  annotation_type varchar(50),
  filename_short  varchar(20),
  start_idx       smallint,
  end_idx         smallint
) engine = myisam;

load data infile '/tmp/mysql/Annotation.csv' into table Annotation
fields terminated by ',' ignore 1 lines;

CALL log('Annotation', 'done');
CALL log('Sentence',   'begin');

create table Sentence like Annotation;
alter table Sentence add  column sentence_seq smallint  not null;

load data infile '/tmp/mysql/Sentence.csv' into table Sentence
fields terminated by ',' ignore 1 lines;

CALL log('Sentence',     'done');
CALL log('BaseToken',    'begin');

create table BaseToken like Annotation;

alter table BaseToken add column token_seq  smallint    not null;
alter table BaseToken add column pos        varchar(25) not null;
alter table BaseToken add column token      varchar(100) not null;

load data infile '/tmp/mysql/BaseToken.csv' into table BaseToken
fields terminated by ',' ignore 1 lines;

CALL log('BaseToken',  'done');
CALL log('NewLineToken',   'begin');

create table NewLineToken like BaseToken;

load data infile '/tmp/mysql/NewLineToken.csv' into table NewLineToken
fields terminated by ',' ignore 1 lines;

CALL log('NewLineToken', 'done');
CALL log('WordToken',  'begin');

create table WordToken like Annotation;

alter table WordToken add column token_seq  smallint     not null;
alter table WordToken add column pos        varchar(25)  not null;
alter table WordToken add column cap        tinyint      not null;
alter table WordToken add column token      varchar(100) not null;

load data infile '/tmp/mysql/WordToken.csv' into table WordToken
fields terminated by ',' ignore 1 lines;

alter table WordToken add index(pos, cap);

CALL log('WordToken',    'done');
CALL log('OntologyMatch','begin');

create table OntologyMatch like Annotation;

alter table OntologyMatch add column match_source  varchar(50)  not null;
alter table OntologyMatch add column match_value   varchar(50)  not null;
alter table OntologyMatch add column token         varchar(100) not null;

load data infile '/tmp/mysql/OntologyMatch.csv' into table OntologyMatch
fields terminated by ',' ignore 1 lines;

CALL log('OntologyMatch',  'done');
CALL log('Calculation',    'begin');

create table Calculation like OntologyMatch;

alter table Calculation drop column token;

load data infile '/tmp/mysql/Calculation.csv' into table Calculation
fields terminated by ',' ignore 1 lines;

CALL log('Calculation',    'done');
CALL log('file_idx',      'begin');

alter table Annotation     add column file_idx smallint not null;
alter table BaseToken      add column file_idx smallint not null;
alter table Sentence       add column file_idx smallint not null;
alter table WordToken      add column file_idx smallint not null;
alter table OntologyMatch  add column file_idx smallint not null;
alter table Calculation    add column file_idx smallint not null;

update Annotation     set file_idx = trim(replace(filename_short, '.txt', ''));
update BaseToken      set file_idx = trim(replace(filename_short, '.txt', ''));
update Sentence       set file_idx = trim(replace(filename_short, '.txt', ''));
update WordToken      set file_idx = trim(replace(filename_short, '.txt', ''));
update OntologyMatch  set file_idx = trim(replace(filename_short, '.txt', ''));
update Calculation    set file_idx = trim(replace(filename_short, '.txt', ''));

CALL log('file_idx',   'done');
CALL log('index',      'begin');

alter table WordToken add index (file_idx);
alter table WordToken add index (file_idx, token_seq);
alter table WordToken add index (file_idx, start_idx, end_idx);
alter table BaseToken add index (filename_short);
alter table BaseToken add index (file_idx, token_seq);
alter table BaseToken add index (file_idx, start_idx, end_idx);
alter table BaseToken add index (annotation_type, file_idx, token_seq);
alter table Annotation add index (annotation_type);
alter table Annotation add index (file_idx, start_idx, end_idx);

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

alter table OntologyMatch add index(match_source);
alter table OntologyMatch add index(match_source, match_value);
alter table OntologyMatch add index(file_idx, start_idx, end_idx);
alter table OntologyMatch add index(match_source, match_value, file_idx, start_idx, end_idx);

alter table Sentence add index(file_idx, start_idx, end_idx);
alter table Sentence add index(file_idx, sentence_seq);

CALL log('index',      'done');
call log('BaseToken:sentence_seq', 'begin.');

alter table BaseToken add sentence_seq integer not null;

update BaseToken b,    Sentence s
set    b.sentence_seq  = s.sentence_seq
where  b.file_idx      = s.file_idx      and
       b.start_idx    >= s.start_idx     and
       b.end_idx      <= s.end_idx         ;

call log('BaseToken:sentence_seq', 'done.');
call log('BaseToken:class',   'begin');

create table Class
(
  class_idx  smallint UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  class      varchar(20),
  cnt        integer
) engine = myisam;

insert into Class (class, cnt) values ('NA', 0);

insert into Class (class, cnt)
select tag_name as class, count(*) as cnt from human_annotations_train group by tag_name;
alter table  Class add index (class);

alter table BaseToken add class varchar(20) not null;

update BaseToken b,    human_annotations_train phi
set    b.class         = phi.tag_name
where  b.filename_short= phi.subject_id  and
       b.start_idx    >= phi.start_idx   and
       b.end_idx      <= phi.end_idx       ;

call log('BaseToken:class',    'done.');
CALL log('load_csv_annotations.sql', 'done');