call log('Sentence', 'begin');

create table t_sort like Sentence;

insert into t_sort select * from Sentence order by file_idx, sentence_seq;
alter table t_sort add sentence_idx integer UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

drop table Sentence;
alter table t_sort rename to Sentence;

alter table BaseToken add sentence_idx integer not null;

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

call log('Sentence', 'done');
call log('Sample',   'begin');
call log('Sample',   'BaseToken');


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 sample_idx integer UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
alter table Sample add class_idx  tinyint UNSIGNED NULL;
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');
call log('Sample',   'class');
update Sample s, Class c
set   s.class_idx = c.class_idx
where s.class = c.class;
update Sample set class = 'NA', class_idx = 0 where class_idx is null;

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

-- alter table Sentence drop column sample_idx_start ;
-- alter table Sentence drop column sample_idx_end   ;

alter table Sentence add sample_idx_start int unsigned not null;
alter table Sentence add sample_idx_end   int unsigned not null;

create temporary table sentence_bounds select
sentence_idx, min(sample_idx) sample_idx_start, max(sample_idx) sample_idx_end
from Sample group by sentence_idx order by sentence_idx;

update Sentence s,  sentence_bounds bounds
set    s.sample_idx_start = bounds.sample_idx_start,
       s.sample_idx_end   = bounds.sample_idx_end
where  s.sentence_idx     = bounds.sentence_idx;

call log('Sample',  'sentence');
call log('Sample',  'done');
call log('Feature', 'begin');

create table Feature
(
  annotation_type    varchar(25) not null,
  match_source       varchar(25) not null,
  match_value        varchar(25) null,
  cnt                int         null,
  datatype           varchar(25) null
) engine=myisam;

alter table Feature add index (match_source);
alter table Feature add index (match_value);
alter table Feature add index (match_source, match_value);


insert into Feature
select 'WordToken', 'cap', cap, count(*) as cnt, 'nominal(4)'
from WordToken group by cap  order by cnt desc;

insert into Feature
select annotation_type, 'BaseToken', null, count(*), 'bool'
from BaseToken group by annotation_type order by count(*) desc;

insert into Feature
select annotation_type, 'Annotation', null, count(*), 'bool'
from Annotation
where annotation_type in
(
  'RomanNumeralAnnotation',
  'FractionAnnotation',
  'DateAnnotation',
  'MeasurementAnnotation',
  'RangeAnnotation',
  'PersonTitleAnnotation',
  'TimeAnnotation'
)
group by annotation_type
order by count(*) desc;

insert into Feature
select 'bin', match_source, match_bin, count(*) as cnt, 'bool'
from FeatureBin
group by match_source, match_bin;

insert into Feature
select 'BaseToken', 'pos', pos, count(*) as cnt, 'bool'
from BaseToken
where annotation_type = 'WordToken' or annotation_type = 'NumToken'
group by pos
order by cnt desc;

insert into Feature
select annotation_type, match_source, match_value, count(*) as cnt, 'bool'
from OntologyMatch
where match_source  = 'regex'
group by match_source, match_value
order by count(*) desc;

insert into Feature
select annotation_type, match_source, null, count(*) as cnt, 'INTEGER'
from OntologyMatch
group by match_source
order by cnt desc;

ALTER TABLE Feature add feature_idx smallint UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE Feature add index (annotation_type, match_source, match_value);

call log('Feature',  'done');
call log('FeatureMatrix', 'begin');

create table FeatureMatrix
(
  file_idx       smallint not null,
  sample_idx     integer  not null,
  feature_idx    smallint not null
) engine=myisam;

call log('FeatureMatrix', 'done');
call log('FeatureMatrix', 'WordToken:cap');

-- | annotation_type        | match_source | match_value              | datatype    | feature_idx |
-- +------------------------+--------------+--------------------------+-------------+-------------+
-- | WordToken              | cap          | 0                        |  nominal(4) |           1 |
-- | WordToken              | cap          | 3                        |  nominal(4) |           2 |
-- | WordToken              | cap          | 1                        |  nominal(4) |           3 |
-- | WordToken              | cap          | 2                        |  nominal(4) |           4 |

insert into FeatureMatrix
select  s.file_idx, s.sample_idx, f.feature_idx
from    Feature f, Sample s, WordToken w
where   f.match_source = 'cap'       and
        f.match_value  = w.cap       and
        w.file_idx     = s.file_idx  and
        w.token_seq    = s.token_seq   ;

call log('FeatureMatrix', 'WordToken:cap');
call log('FeatureMatrix', 'BaseToken');

-- | annotation_type        | match_source | match_value | datatype   | feature_idx |
-- +------------------------+--------------+--------------------------+-------------+
-- | WordToken              | BaseToken    | NULL        | bool       |           5 |
-- | PunctuationToken       | BaseToken    | NULL        | bool       |           6 |
-- | NumToken               | BaseToken    | NULL        | bool       |           7 |
-- | SymbolToken            | BaseToken    | NULL        | bool       |           8 |
-- | ContractionToken       | BaseToken    | NULL        | bool       |           9 |

insert into FeatureMatrix
select  distinct
        s.file_idx, s.sample_idx, f.feature_idx
from    Feature f, Sample s, BaseToken t
where   f.match_source     = 'BaseToken'       and
        f.annotation_type  = t.annotation_type and
        t.file_idx         = s.file_idx        and
        t.token_seq        = s.token_seq   ;

call log('FeatureMatrix', 'BaseToken');
call log('FeatureMatrix', 'Annotation');

-- +------------------------+--------------+-------------+------------+-------------+
-- | annotation_type        | match_source | match_value | datatype   | feature_idx |
-- +------------------------+--------------+-------------+------------+-------------+
-- | RomanNumeralAnnotation | Annotation   | NULL        | bool       |          10 |
-- | FractionAnnotation     | Annotation   | NULL        | bool       |          11 |
-- | DateAnnotation         | Annotation   | NULL        | bool       |          12 |
-- | MeasurementAnnotation  | Annotation   | NULL        | bool       |          13 |
-- | RangeAnnotation        | Annotation   | NULL        | bool       |          14 |
-- | PersonTitleAnnotation  | Annotation   | NULL        | bool       |          15 |
-- | TimeAnnotation         | Annotation   | NULL        | bool       |          16 |

insert into FeatureMatrix
select  distinct
        s.file_idx, s.sample_idx, f.feature_idx
from    Feature f, Sample s, Annotation a
where   f.match_source     = 'Annotation'      and
        f.annotation_type  = a.annotation_type and
        a.file_idx         = s.file_idx        and
        a.start_idx       >= s.start_idx       and
        a.end_idx         <= s.end_idx           ;

call log('FeatureMatrix', 'Annotation');
call log('FeatureMatrix', 'bin');

-- +------------------------+--------------+-------------+------------+-------------+
-- | annotation_type        | match_source | match_value | datatype   | feature_idx |
-- +------------------------+--------------+-------------+------------+-------------+
-- | bin                    | pos          | Adjectives  | bool       |          17 |
-- | bin                    | pos          | Adverbs     | bool       |          18 |
-- | bin                    | pos          | com-dep-wd  | bool       |          19 |
-- | bin                    | pos          | FW-Symb     | bool       |          20 |
-- | bin                    | pos          | Nouns       | bool       |          21 |
-- | bin                    | pos          | Numbers     | bool       |          22 |
-- | bin                    | pos          | Pronouns    | bool       |          23 |
-- | bin                    | pos          | Verbs       | bool       |          24 |
-- | bin                    | regex        | address     | bool       |          25 |
-- | bin                    | regex        | age         | bool       |          26 |
-- | bin                    | regex        | date        | bool       |          27 |
-- | bin                    | regex        | doctor      | bool       |          28 |
-- | bin                    | regex        | hospital    | bool       |          29 |
-- | bin                    | regex        | patient     | bool       |          30 |
-- | bin                    | regex        | phone       | bool       |          31 |

call log('FeatureMatrix', 'bin:pos');

insert into FeatureMatrix
select  distinct
        s.file_idx, s.sample_idx, f.feature_idx
from    Feature f, Sample s, FeatureBin b
where   f.match_source = 'pos'           and
        f.match_source = b.match_source  and
        f.match_value  = b.match_bin     and
        s.pos          = b.match_value     ;

call log('FeatureMatrix', 'bin:pos');
call log('FeatureMatrix', 'bin:regex');

insert into FeatureMatrix
select  distinct
        s.file_idx, s.sample_idx, f.feature_idx
from    Feature f, Sample s, FeatureBin b, OntologyMatch o
where   f.annotation_type = 'bin'           and
        f.match_source    = 'regex'         and
        f.match_source    = b.match_source  and
        f.match_source    = o.match_source  and
        f.match_value     = b.match_bin     and
        o.match_value     = b.match_value   and
        o.file_idx        = s.file_idx      and
        o.start_idx       >= s.start_idx    and
        o.end_idx         <= s.end_idx         ;

call log('FeatureMatrix', 'bin:regex');
call log('FeatureMatrix', 'BaseToken:pos');

-- +------------------------+--------------+-------------+------------+-------------+
-- | annotation_type        | match_source | match_value | datatype   | feature_idx |
-- +------------------------+--------------+-------------+------------+-------------+
-- | BaseToken              | pos          | NN          | bool       |          32 |
-- | BaseToken              | pos          | JJ          | bool       |          33 |
-- | BaseToken              | pos          | CD          | bool       |          34 |
-- | BaseToken              | pos          | IN          | bool       |          35 |
-- | BaseToken              | pos          | NNP         | bool       |          36 |
-- | BaseToken              | pos          | DT          | bool       |          37 |
-- | ...                    | ...          | ...         | ...        |          .. |
-- | BaseToken              | pos          | pos_paren   | bool       |          71 |
-- +------------------------+--------------+-------------+------------+-------------+


insert into FeatureMatrix
select  distinct
        s.file_idx, s.sample_idx, f.feature_idx
from    Feature f, Sample s
where
        f.annotation_type  = 'BaseToken'       and
        f.match_source     = 'pos'             and
        f.match_value      = s.pos               ;


call log('FeatureMatrix', 'BaseToken:pos');
call log('FeatureMatrix', 'OntologyMatch:regex');

-- +-----------------+--------------+-----------------+----------+-------------+
-- | annotation_type | match_source | match_value     | datatype | feature_idx |
-- +-----------------+--------------+-----------------+----------+-------------+
-- | OntologyMatch   | regex        | DATE22          | bool     |          72 |
-- | OntologyMatch   | regex        | DATE21          | bool     |          73 |
-- | OntologyMatch   | regex        | DATE17          | bool     |          74 |
-- | OntologyMatch   | regex        | DATE18          | bool     |          75 |
-- | OntologyMatch   | regex        | DATE_SEPARATORS | bool     |          76 |
-- | OntologyMatch   | regex        | DATE1           | bool     |          77 |
-- | OntologyMatch   | regex        | DATE4           | bool     |          78 |
-- | OntologyMatch   | regex        | YEAR_CENTURY    | bool     |          79 |
-- | OntologyMatch   | regex        | SUSPICIOUS_NUM  | bool     |          80 |
-- | OntologyMatch   | regex        | MONTH           | bool     |          81 |
-- | ...             | ...          | ...             | ...      |         ... |
-- | OntologyMatch   | regex        | ADDRESS         | bool     |         148 |
-- +-----------------+--------------+-----------------+----------+-------------+

insert into FeatureMatrix
select  distinct
        s.file_idx, s.sample_idx, f.feature_idx
from    Feature f, Sample s, OntologyMatch o
where   f.annotation_type = 'OntologyMatch' and
        f.match_source    = 'regex'         and
        f.match_source    = o.match_source  and
        f.match_value     = o.match_value   and
        o.file_idx        = s.file_idx      and
        o.start_idx       >= s.start_idx    and
        o.end_idx         <= s.end_idx         ;

call log('FeatureMatrix', 'OntologyMatch:regex');

-- TODO refactor

create table EmptySentence
select * from Sentence where sample_idx_start = 0 and sample_idx_end = 0;

delete from Sentence where sample_idx_start = 0 and sample_idx_end = 0;
alter table Sentence drop column sentence_idx ;
alter table Sentence add column sentence_idx integer UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

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

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('FeatureMatrix', 'csv');

select     s.file_idx, s.sample_idx, s.sentence_idx, m.feature_idx
from       FeatureMatrix m, Sample s
where      m.sample_idx = s.sample_idx
order by   m.sample_idx, m.feature_idx
into
  outfile '/tmp/mysql/FeatureMatrix.csv'
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n';

call log('FeatureMatrix', 'csv');
call log('Sample',        'csv');

select     file_idx, sample_idx, sentence_idx, start_idx, end_idx, token_seq, class_idx
from       Sample
order by   sample_idx
into
  outfile '/tmp/mysql/Sample.csv'
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n';

call log('Sample',   'csv');
call log('SentenceBoundary', 'csv');

select file_idx, 0 as sample_idx, sentence_idx, sample_idx_start, sample_idx_end, sentence_seq
from Sentence
-- where sample_idx_start >0 and sample_idx_end > 0
group by sentence_idx order by sentence_idx
into
  outfile '/tmp/mysql/SentenceBoundary.csv'
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n';

call log('SentenceBoundary', 'csv');






