#!/usr/bin/python -t
# vim: set ts=4 sw=4:
#
# Copyright (c) 2002-2010 Martin A. Godisch <martin@godisch.de>.
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.

import sys, codecs, locale

if sys.stdout.encoding is None:
	(lang, enc) = locale.getdefaultlocale()
	if enc is not None:
		(e, d, sr, sw) = codecs.lookup(enc)
		sys.stdout = sw(sys.stdout)

import sqlite3, sys

language = set()
chapter  = set()
section  = set()

dbh = sqlite3.connect(sys.argv[1])
sth = dbh.cursor()

# not necessary, since main select based on translation
#sth.execute("DELETE FROM wordlist WHERE id IN " \
#	"(SELECT id FROM wordlist EXCEPT SELECT word1 FROM translation EXCEPT SELECT word2 FROM translation)")

sth.execute("SELECT lang1.name, lang2.name, chapter.name, section.name, level, word1.word, word2.word " \
	"FROM translation " \
	"INNER JOIN wordlist AS word1 ON (translation.word1 = word1.id) " \
	"INNER JOIN wordlist AS word2 ON (translation.word2 = word2.id) " \
	"LEFT OUTER JOIN language AS lang1 ON (word1.language = lang1.id) " \
	"LEFT OUTER JOIN language AS lang2 ON (word2.language = lang2.id) " \
	"LEFT OUTER JOIN section ON (translation.section = section.id) " \
	"LEFT OUTER JOIN chapter ON (section.chapter = chapter.id)")

for row in sth:
	for i in 0, 1:
		if row[i] not in language:
			print u"INSERT INTO language (name) VALUES ('%s');".encode('utf-8') % row[i]
			language.add(row[i])
	if row[2] and row[2] not in chapter:
		print "INSERT INTO chapter (name) VALUES ('%s');" % row[2]
		chapter.add(row[2])
	if row[3] and not (row[2], row[3]) in section:
		print "INSERT INTO section (chapter, name) SELECT id, '%s' FROM chapter WHERE name = '%s';" % (row[3], row[2])
		section.add((row[2], row[3]))
	print "INSERT INTO wordlist (language, word) SELECT id, '%s' FROM language WHERE name = '%s';" % (row[5], row[0])
	print "INSERT INTO wordlist (language, word) SELECT id, '%s' FROM language WHERE name = '%s';" % (row[6], row[1])
	if row[3]:
		print "INSERT INTO translation (id, word1, word2, section) " \
			"SELECT IFNULL(MAX(translation.id) + 1, 1), word1.id, word2.id, section.id " \
			"FROM language AS lang1 INNER JOIN wordlist AS word1 ON (lang1.id = word1.language) " \
			"INNER JOIN language AS lang2 INNER JOIN wordlist AS word2 ON (lang2.id = word2.language) " \
			"LEFT OUTER JOIN section LEFT OUTER JOIN chapter ON (section.chapter = chapter.id) " \
			"LEFT OUTER JOIN translation " \
			"WHERE lang1.name = '%s' AND word1.word = '%s' AND lang2.name = '%s' AND word2.word = '%s' " \
			"AND chapter.name = '%s' AND section.name = '%s';" \
			% (row[0], row[5], row[1], row[6], row[2], row[3])
	else:
		print "INSERT INTO translation (id, word1, word2) " \
			"SELECT IFNULL(MAX(translation.id) + 1, 1), word1.id, word2.id " \
			"FROM language AS lang1 INNER JOIN wordlist AS word1 ON (lang1.id = word1.language) " \
			"INNER JOIN language AS lang2 INNER JOIN wordlist AS word2 ON (lang2.id = word2.language) " \
			"LEFT OUTER JOIN translation " \
			"WHERE lang1.name = '%s' AND word1.word = '%s' AND lang2.name = '%s' AND word2.word = '%s';" \
			% (row[0], row[5], row[1], row[6])

sth.close()
dbh.close()
