create table papelsynset(synset_id int(10), sense_id int(10), word_text varchar(80));
insert into papelsynset( sense_id, word_text, synset_id)
    select 
        sense_id_2 as sense_id,
        word_text_2 as word_text,
        sense_id_1 * 695 as synset_id
    from papel
    where relation = 'SYN'
  union
    select 
        sense_id_1 as sense_id, 
        word_text_1 as word_text, 
        sense_id_1 * 695 as synset_id
    from papel
    where relation = 'SYN'
    group by sense_id_1
;

insert ignore into papelsynset( sense_id, word_text, synset_id)
	select 
        sense_id_1 as sense_id, 
        word_text_1 as word_text, 
        sense_id_1 * 695 as synset_id
    from papel
    where relation = 'HYPER'
    group by sense_id_1
  union
	select 
        sense_id_2 as sense_id, 
        word_text_2 as word_text, 
        sense_id_2 * 695 as synset_id
    from papel
    where relation = 'HYPER'
    group by sense_id_2
;



create table dicsynset(synset_id int(10), sense_id int(10), word_text varchar(80));
insert into dicsynset( sense_id, word_text, synset_id)
    select 
        sense_id as sense_id,
        word_text as word_text,
        sense_id * 544 as synset_id
    from sense
    where source = 'Dicionario'
;



INSERT INTO IndexWord (index_word_id,lemma,pos)
	select sense_id as index_word_id, word_text as lemma, 
	case trim(word_pos)
    when 'ADV' then 'r'
    when 'CONJ' then 'r'
    when 'PREP' then 'r'
    when 'NP' then 'n'
    when '' then 'r'
    else lower(word_pos)
    end  as pos
	from sense
;



INSERT INTO Synset (synset_id,file_offset,pos,is_adj_cluster,gloss)
		select
			if(mwnsynset.synset_id REGEXP '.*[a-zA-Z]+.+' = 1, substring(mwnsynset.synset_id, 2), mwnsynset.synset_id) as synset_id,
			if(mwnsynset.synset_id REGEXP '.*[a-zA-Z]+.+' = 1, substring(mwnsynset.synset_id, 2), mwnsynset.synset_id) as file_offset,
			case trim(sense.word_pos)
				when 'ADV' then 'r'
				when 'CONJ' then 'r'
				when 'PREP' then 'r'
				when 'NP' then 'n'
				when '' then 'r'
				else lower(sense.word_pos)
			end as pos, 
			0 as is_adj_cluster,
			CONCAT(mwnsynset.domain, ': ', mwnsynset.sumo) as gloss
		from mwnsynset join sense on mwnsynset.sense_id = sense.sense_id
	union
		select 
			temanetsynset.synset_id, 
			temanetsynset.synset_id as file_offset, 
			case trim(sense.word_pos)
				when 'ADV' then 'r'
				when 'CONJ' then 'r'
				when 'PREP' then 'r'
				when 'NP' then 'n'
				when '' then 'r'
				else lower(sense.word_pos)
			end as pos, 
			0 as is_adj_cluster,
			CONCAT(temanetsynset.domain, ': ', temanetsynset.gloss,  '; "', temanetsynset.example, '"') as gloss
		from temanetsynsetwords join sense on temanetsynsetwords.sense_id = sense.sense_id 
			right join temanetsynset on temanetsynset.synset_id = temanetsynsetwords.synset_id
	union
		select
			papelsynset.synset_id,
			papelsynset.synset_id as file_offset,
			case trim(sense.word_pos)
				when 'ADV' then 'r'
				when 'CONJ' then 'r'
				when 'PREP' then 'r'
				when 'NP' then 'n'
				when '' then 'r'
				else lower(sense.word_pos)
			end as pos, 
			0 as is_adj_cluster,
			'' as gloss
		from papelsynset join sense on papelsynset.sense_id = sense.sense_id
	union
		select
			dicsynset.synset_id,
			dicsynset.synset_id as file_offset,
			case trim(sense.word_pos)
				when 'ADV' then 'r'
				when 'CONJ' then 'r'
				when 'PREP' then 'r'
				when 'NP' then 'n'
				when '' then 'r'
				else lower(sense.word_pos)
			end as pos, 
			0 as is_adj_cluster,
			concat(sense.word_pos_desc, ": ", definition.definition_text) as gloss
		from dicsynset join sense on dicsynset.sense_id = sense.sense_id
			join definition on definition.sense_id = dicsynset.sense_id
;



INSERT INTO IndexWordSynset (synset_id,index_word_id)
		select 
			if(synset_id REGEXP '.*[a-zA-Z]+.+' = 1, substring(synset_id, 2), synset_id) as synset_id, 
			sense_id as index_word_id
		from mwnsynset 
	union 
		select synset_id, sense_id as index_word_id
		from temanetsynsetwords
	union 
		select synset_id, sense_id as index_word_id
		from papelsynset
	union 
		select synset_id, sense_id as index_word_id
		from dicsynset
;



INSERT INTO SynsetWord (word, synset_id,sense_key,usage_cnt,word_index)
		select word_text as word, 
			if(synset_id REGEXP '.*[a-zA-Z]+.+' = 1, substring(synset_id, 2), synset_id) as synset_id, 
			CONCAT(word_text, RAND()) as sense_key, 0 as usage_cnt, 0 as word_index
		from mwnsynset 
	union 
		select word_text as word, synset_id, CONCAT(word_text, RAND()) as sense_key, 0 as usage_cnt, 0 as word_index
		from temanetsynsetwords
	union
		select word_text as word, synset_id, CONCAT(word_text, RAND()) as sense_key, 0 as usage_cnt, 0 as word_index
		from papelsynset 
	union
		select word_text as word, synset_id, CONCAT(word_text, RAND()) as sense_key, 0 as usage_cnt, 0 as word_index
		from dicsynset 
;



INSERT INTO SynsetPointer (synset_id,pointer_type,target_offset,target_pos,source_index,target_index)
    select 
		if(synset_id_1 REGEXP '.*[a-zA-Z]+.+' = 1, substring(synset_id_1, 2), synset_id_1) as synset_id,
		case relation
			when 'HYPO' then '~'
			when 'HYPER' then '@'
			else '^'
		end as pointer_type, 
		if(synset_id_2 REGEXP '.*[a-zA-Z]+.+' = 1, substring(synset_id_2, 2), synset_id_2) as target_offset,
		case trim(sense.word_pos)
			when 'ADV' then 'r'
			when 'CONJ' then 'r'
			when 'PREP' then 'r'
			when 'NP' then 'n'
			when '' then 'r'
			else lower(sense.word_pos)
		end as target_pos, 
		0 as source_index, 0 as target_index
    from mwnrel, mwnsynset, sense
    where mwnrel.synset_id_2 = mwnsynset.synset_id 
		and mwnsynset.sense_id = sense.sense_id
union
    select 
		synset_id_1 as synset_id, 
		case relation
			when 'HYPO' then '~'
			when 'HYPER' then '@'
			else '^'
		end as pointer_type, 
		synset_id_2 as target_offset, 
		case (sense.word_pos) 
			when 'ADV' then 'r'
			when 'CONJ' then 'r'
			when 'PREP' then 'r'
			when 'NP' then 'n'
			when '' then 'r'
			else lower(sense.word_pos)
		end as target_pos, 
		0 as source_index, 0 as target_index
    from temanetrel, temanetsynsetwords, sense
    where temanetrel.synset_id_2 = temanetsynsetwords.synset_id 
		and temanetsynsetwords.sense_id = sense.sense_id
union
	select 
		sense_id_1  * 695 as synset_id, 
		'@' as pointer_type, 
		sense_id_2  * 695 as target_offset, 
		case (sense.word_pos) 
			when 'ADV' then 'r'
			when 'CONJ' then 'r'
			when 'PREP' then 'r'
			when 'NP' then 'n'
			when '' then 'r'
			else lower(sense.word_pos)
		end as target_pos, 
		0 as source_index, 0 as target_index
    from papel, sense
    where papel.sense_id_2 = sense.sense_id
		and papel.relation = 'HYPER'
;



INSERT INTO SynsetVerbFrame (synset_id,frame_number,word_index)
	select 0 as synset_id, 0 as frame_number, 0 as word_index
;



INSERT INTO Exception (exception,lemma)
	select '' as exception, '' as lemma
;
