call Session;

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

call drop_table('Feature');

create table Feature
(
  annotation_type    varchar(25) not null,
  match_source       varchar(25) not null,
  match_value        varchar(25) null default '',
  cnt                int         null,
  datatype           varchar(25) null,
  description        text        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);

call log('Feature', 'TermFrequency');

insert into Feature
select 'TermFrequency', 'tf_ham_without_pos',null, count(*) as cnt, 'double', 'Publication Term Frequency'
from TermFrequency;

insert into Feature
select 'TermFrequency', 'tf_ham_with_pos',null, count(*) as cnt, 'double', 'Publication Term Frequency with Part of Speech'
from TermFrequency;

insert into Feature
select 'TermFrequency', 'token_len', null, count(*) as cnt, 'nominal(int)', 'String Length {WordToken or NumToken}'
from TermFrequency;

call log('Feature', 'bin');

insert into Feature
select 'bin', 'ontology', match_source, count(*) as cnt, 'nominal(int)', '#matches in Dictionary'
from OntologyMatch
group by match_source
order by cnt desc;

insert into Feature
select 'bin', match_source, match_bin, count(*) as cnt, 'nominal(int)', '@see FeatureBin'
from FeatureBin where match_source = 'regex'
group by match_source, match_bin;

insert into Feature
select 'bin', match_source, match_bin, count(*) as cnt, 'nominal(int)', '@see FeatureBin'
from FeatureBin where match_source != 'regex'
group by match_source, match_bin;

call log('Feature','WordToken:cap');

insert into Feature
select 'WordToken', 'cap', null, count(*) as cnt, 'nominal(4)', '(ctakes) Word Capitalization'
from WordToken;

call log('Feature','BaseToken...*');

insert into Feature
select annotation_type, 'BaseToken', null, count(*), 'binary', '(ctakes) token type'
from BaseToken group by annotation_type order by count(*) desc;

call log('Feature','Annotation...*');

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

call log('Feature','BaseToken:pos');

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

call log('Feature','regex');

insert into Feature
select annotation_type, match_source, match_value, count(*) as cnt, 'binary', '(Beckwith) regular expression'
from OntologyMatch
where match_source  = 'regex'
group by match_source, match_value
order by count(*) 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);

update Feature  set match_value = '' where match_value is null;

CALL log('Feature', 'FeatureBin:index');

alter table FeatureBin add index (match_source, match_value);
alter table FeatureBin add index (match_source, match_value, match_bin);

alter table FeatureBin add column match_value_feature_idx smallint unsigned;
alter table FeatureBin add column match_bin_feature_idx smallint unsigned;

alter table FeatureBin add index(match_value_feature_idx);

update FeatureBin b, Feature f
set b.match_bin_feature_idx = f.feature_idx
where f.annotation_type = 'bin' and f.match_value = b.match_bin;

update FeatureBin b, Feature f
set b.match_value_feature_idx = f.feature_idx
where f.match_source = f.match_source and f.match_value = b.match_value;

-- CALL log('Feature', 'removing sparse features');
--
-- select * from Feature where annotation_type != 'bin' and cnt < 10;
-- delete from Feature where annotation_type != 'bin' and cnt < 10;

CALL log('Feature', 'FeatureBin:done');
CALL log('Feature', 'POS:begin');

create table POS
(
  pos           varchar(25) null,
  pos_idx       smallint    unsigned,
  pos_bin_idx   smallint    unsigned
)
engine = myisam;

insert into POS (pos, pos_idx, pos_bin_idx)
select distinct match_value, match_value_feature_idx, match_bin_feature_idx from FeatureBin where match_source='pos';

alter table POS add index (pos);
CALL log('Feature', 'POS:done');
call log('Feature',     'done');


