call Session;

call log('export_csv.sql', 'begin');
-- ####

create table MyFeatureSelection like Feature;

insert into MyFeatureSelection
select * from feature where datatype in (
  'double',
  'nominal(int)',
  'nominal(4)',
  'binary'
);


call log('Sample.csv', 'begin');

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',       'done');
call log('SentenceIdx.csv', 'begin');


select     s.file_idx, s.sample_idx, s.sentence_idx, sample_idx_start, sample_idx_end, s.sentence_seq
from       Sentence sentence, Sample s
where      sentence.sentence_idx = s.sentence_idx
order by   s.sample_idx asc
into
  outfile '/tmp/mysql/SentenceIdx.csv'
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n';

call log('SentenceIdx.csv', 'done');
call log('TermFrequency.csv', 'begin');

-- TODO HACK ! SYM
update TermFrequency set pos = 'SYM' where pos like 'pos_%';

select     file_idx, sample_idx,
           TF.tf_ham_without_pos, TF.tf_ham_with_pos , TF.token_len,
           POS.pos_idx, POS.pos_bin_idx
from       TermFrequency TF  left join POS on TF.pos = POS.pos
order by   sample_idx
into
  outfile '/tmp/mysql/TermFrequency.csv'
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n';

call log('TermFrequency.csv', 'done');
call log('MyFeatureIdx.csv', 'begin');

select feature_idx, CASE datatype
when 'binary'  then 1
WHEN 'double'  THEN 3
ELSE 2 end
from MyFeatureSelection
order      by feature_idx
into
  outfile '/tmp/mysql/MyFeatureIdx.csv'
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n';

call log('MyFeatureIdx.csv', 'done');
-- call log('MyFeatureSelection.csv', 'csv');

-- select     *
-- from       MyFeatureSelection
-- order      by feature_idx
-- into
--   outfile '/tmp/mysql/MyFeatureSelection.csv'
--   FIELDS TERMINATED BY ','
--   LINES TERMINATED BY '\n';
--
-- call log('MyFeatureSelection.csv', 'csv');

-- call log('MyFeatureHeader.csv', 'begin');

set group_concat_max_len = 8096;

create temporary table my_FeatureHeader
select feature_idx, concat(feature_idx, '.',annotation_type, '.', match_source, '.', match_value, '.',datatype) as feature_label
from myFeatureSelection
order by feature_idx;

insert into my_FeatureHeader values (0,'Class_PHI_Label');

update my_FeatureHeader set feature_label=replace(feature_label, '..', '.');

select     feature_label
from       my_FeatureHeader
order      by feature_idx
into
  outfile '/tmp/mysql/MyFeatureHeader.csv'
  FIELDS TERMINATED BY ','
  LINES TERMINATED  BY ';';

call log('MyFeatureHeader.csv', 'done');

call log('export_csv.sql',      'done');
call log('FeatureMatrix.csv',   'begin');

select     s.file_idx, s.sample_idx, s.sentence_idx, m.feature_idx, m.feature_value
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',     'done');
call log('FeatureMatrixOnt.csv',  'begin');

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

-- ####
call log('FeatureMatrixOnt.csv', 'done');