-- create temporary ingest table
drop table if exists ingest;
create temporary table ingest(
	-- term fields
	expression text not null, -- kanji of term (e.g. 読み込む)
	reading text not null, -- reading of term (e.g. よみこむ)
	term_tags text not null default '', -- space-separated *term* tags, merged if term already exists in DB

	-- definition fields
	glossary_sort int not null default 1, -- order of multiple meanings (glossaries)
	glossary text null default null, -- glossary content (support for basic HTML markup/styling)
	glossary_tags text null default null -- add tags to single glossary entry
);
-- TODO: ingest pitch-accent dictionaries

-- create temporary alternate readings table
drop table if exists alts;
create temporary table alts(
	expression text not null, -- kanji of alternate version
	reading text not null, -- reading of alternate version
	normal_expression text not null, -- kanji of parent (original)
	normal_reading text not null -- reading of parent (original)
);

-- #DICTIONARY_CONTENT_BEGIN
-- this template is 'rendered' by pasting a .dict.sql file in between these
-- DICTIONARY_CONTENT markers. the makefile can render these using the
-- following m4 code (called using m4 -P template.sql < any.dict.sql):
--
-- m4_undivert(`/dev/stdin')
--
-- this breaks when the first line of the input file is not a comment or empty
-- line, so the makefile accounts for this by concatenating an empty line with
-- the dict first. the runtime typescript dictionary importer handles this by
-- not calling m4 for this.
-- #DICTIONARY_CONTENT_END

-- create dict id
insert into dict (tag, language) values ('dict:' || :dict, :lang);

-- add terms
insert into term (expression, reading)
select expression, reading
from ingest;

-- add alternates
insert into term (expression, reading, alt)
select expression, reading, (select id from term where expression = normal_expression and reading = normal_reading)
from alts;

-- add definitions
insert into definition (term_id, sort, glossary, dict_id)
select
	term.id,
	ingest.glossary_sort,
	ingest.glossary,
	(select id from dict where tag = 'dict:' || :dict)
from ingest
join term on term.expression = ingest.expression and term.reading = ingest.reading;

-- create map of term_id and tag code
drop table if exists term_tag_map;
create temporary table term_tag_map (term_id, tag);
insert into term_tag_map
with tag_map(term_id, temp, tag) as (
	select
		(select id from term where expression is ingest.expression and reading is ingest.reading),
		term_tags || ' ',
		''
	from ingest
	union
	select
		term_id,
		substr(temp, instr(temp, ' ') + 1),
		substr(temp, 0, instr(temp, ' '))
	from tag_map
	where length(temp) > 1
)
select term_id, replace(tag, ' ', '')
from tag_map
where length(tag) > 0;

-- create map of definition_id and tag code
drop table if exists definition_tag_map;
create temporary table definition_tag_map (definition_id, tag);
insert into definition_tag_map
with tag_map(definition_id, temp, tag) as (
	select
		(select id from definition where glossary is ingest.glossary),
		glossary_tags || ' ',
		''
	from ingest
	union
	select
		definition_id,
		substr(temp, instr(temp, ' ') + 1),
		substr(temp, 0, instr(temp, ' '))
	from tag_map
	where length(temp) > 1
)
select definition_id, replace(tag, ' ', '')
from tag_map
where length(tag) > 0;

-- make sure tags exist
insert into tag (code)
select tag from term_tag_map
union
select tag from definition_tag_map;

-- add tags to terms
insert into term_tag (term_id, tag_id)
select
	term_id,
	tag.id
from term_tag_map
join tag on tag.code = term_tag_map.tag;

-- add tags to alternates
insert into term_tag (term_id, tag_id)
select term_alt.id, term_tag.tag_id
from alts
inner join term as term_normal on term_normal.expression = alts.normal_expression and term_normal.reading = alts.normal_reading
inner join term as term_alt on term_alt.expression = alts.expression and term_alt.reading = alts.reading
left join term_tag on term_tag.term_id = term_normal.id
where term_tag.tag_id is not null;

-- add tags to definitions
insert into definition_tag (definition_id, tag_id)
select
	definition_id,
	tag.id
from definition_tag_map
join tag on tag.code = definition_tag_map.tag;