-- this statement is prepared and run using :term and :user as inputs (see
-- db.ts or test/find)

-- this file is kind of messy because it needs to be one large query, instead
-- of separate phases creating temporary tables. queries with more than one
-- statement can't return results because of the way sqlite3 works.

-- TODO: add more comments in this file to explain what is going on

-- explain query plan -- testing only
with results(id, expression, reading, tags, depth, rules, original, deinflected) as (
	-- stripped deinflection table (remove some columns and duplicates)
	with deinflections(term, tags, depth, original, rules) as (
		-- recursively generated deinflection table
		with deinflect(length, term, tags, rules, rules_in, rules_out, depth) as (
			-- input term all substrings until length 1
			with inputs(length, term, tags, rules, rules_in, rules_out, depth) as (
				select length(:term), :term, '', -1, 0, 0, 0
				union
				select
					inputs.length - 1,
					substr(inputs.term, 1, inputs.length - 1),
					inputs.tags,
					inputs.rules,
					inputs.rules_in,
					inputs.rules_out,
					inputs.depth
				from inputs
				where inputs.length > 1
			)
			select * from inputs
			union -- join all recursive rows into one large table
			select
				deinflect.length,
				substr(deinflect.term, 1, length(deinflect.term)-length(deinflection.kana_in)) || deinflection.kana_out,
				deinflect.tags || ' ' || deinflection.tag, -- parsed to TokenTag[] on (sql) client-side
				deinflection.rules_out,
				deinflection.rules_in,
				deinflect.rules,
				deinflect.depth + 1
			from deinflect -- temp table
			inner join deinflection -- deinflection rules table
			on
				-- rules_in has to contain any of the current deconjugation rules
				(deinflect.rules & deinflection.rules_in != 0) and
				-- term.endsWith(kana_in)
				(substr(term, length(term) - length(kana_in) + 1) = kana_in) and
				-- can't deconjugate to length <1
				(length(term) > 0)
			limit 100 -- failsafe to catch any infinite loops
		)
		select term, tags, depth, substr(:term, 1, deinflect.length), rules
		from deinflect
	)
	select
		term.id,
		term.expression,
		term.reading,
		deinflections.tags || ' ' || group_concat(tag.code, ' ') as tags,
		deinflections.depth,
		rules,
		deinflections.original,
		deinflections.term
	from deinflections
	inner join term on (term.expression = deinflections.term) or (term.reading = deinflections.term)
	left join term_tag on term_tag.term_id = term.id
	left join tag on term_tag.tag_id = tag.id
	group by term.id, deinflections.original, deinflections.rules
	having term.id is not null
)
select
	results.id,
	results.expression,
	results.reading,
	results.tags,
	group_concat(deinflection_rules.tag, ' ') as rules,
	results.depth,
	results.original,
	results.deinflected,
	root_overlay.sort as root_overlay,
	user_overlay.sort as user_overlay
from results
left join deinflection_rules
	on results.rules & deinflection_rules.mask != 0
left join sort_overlay
	as root_overlay
	on (root_overlay.expression = results.expression) and
	(root_overlay.reading = results.reading) and
	(root_overlay.user_id = 0)
left join sort_overlay
	as user_overlay
	on (user_overlay.expression = results.expression) and
	(user_overlay.reading = results.reading) and
	(user_overlay.user_id = (select id from user where username = :user))
group by results.id, results.original, results.rules;