call Session;

call log('FeatureMatrix.sql', 'begin');

call log('FeatureMatrix', 'begin');
call log('FeatureMatrix', 'create');
create table FeatureMatrix
(
  file_idx       smallint not null,
  sample_idx     integer  not null,
  feature_idx    smallint not null,
  feature_value  float unsigned default 0
) engine=myisam;

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

select feature_idx into @tf_ham_without_pos from Feature where match_source = 'tf_ham_without_pos';
select feature_idx into @tf_ham_with_pos    from Feature where match_source = 'tf_ham_with_pos';
select feature_idx into @token_len          from Feature where match_source = 'token_len';

insert into FeatureMatrix
select file_idx, sample_idx, @tf_ham_without_pos, tf_ham_without_pos from TermFrequency;

insert into FeatureMatrix
select file_idx, sample_idx, @tf_ham_with_pos, tf_ham_with_pos from TermFrequency;

insert into FeatureMatrix
select file_idx, sample_idx, @token_len, token_len from TermFrequency;

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

select feature_idx into @cap from Feature where annotation_type = 'WordToken' and match_source = 'cap';

insert into FeatureMatrix
select  s.file_idx, s.sample_idx, @cap, w.cap
from    Sample s, WordToken w
where   w.file_idx     = s.file_idx  and
        w.token_seq    = s.token_seq   ;

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

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

-- NumToken and WordToken only
insert into FeatureMatrix
select  s.file_idx, s.sample_idx, f.feature_idx, 1
from    Feature f, Sample s
where   f.match_source     = 'BaseToken'       and
        f.annotation_type  = s.annotation_type;

insert into FeatureMatrix
select  s.file_idx, s.sample_idx, f.feature_idx, 1
from    Feature f, Sample s, BaseToken b
where   f.match_source     = 'BaseToken'       and
        f.annotation_type  = b.annotation_type and
        b.file_idx         = s.file_idx        and
        b.token_seq        = s.token_seq-1     and
        b.annotation_type in ('PunctuationToken', 'SymbolToken', 'ContractionToken');

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

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

-- Annotation is inside the WordNum
insert into FeatureMatrix
select  s.file_idx, s.sample_idx, f.feature_idx, 1
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           ;

-- WordNum is inside the Annotation
insert into FeatureMatrix
select  s.file_idx, s.sample_idx, f.feature_idx, 1
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:done');
call log('FeatureMatrix', 'BaseToken:pos');

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

insert into FeatureMatrix
select  s.file_idx, s.sample_idx, f.feature_idx, 1
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:done');
call log('FeatureMatrix', 'OntologyMatch:regex');

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

insert into FeatureMatrix
select  s.file_idx, s.sample_idx, f.feature_idx, 1
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:done');

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

insert into FeatureMatrix
select  fm.file_idx, fm.sample_idx, b.match_bin_feature_idx, count(*)
from    FeatureMatrix fm, FeatureBin b
where   fm.feature_idx = match_value_feature_idx
group by fm.file_idx, fm.sample_idx, b.match_bin_feature_idx;

call log('FeatureMatrix', 'bin:done');
call log('FeatureMatrix.sql', 'done');