-- create temporary ingest table
drop table if exists ingest;
-- TODO: ingest pitch-accent dictionaries
-- TODO: ingest alternate writings (space-separated)
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
);

-- #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 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 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;