@file:Suppress("MagicNumber", "LongMethod", "MaximumLineLength", "MaxLineLength", "NestedBlockDepth")

package com.marvinelsen.willow.database

import com.github.houbb.opencc4j.util.ZhConverterUtil
import com.marvinelsen.cedict.api.CedictParser
import com.marvinelsen.chinese.transliteration.api.PinyinSyllable
import com.marvinelsen.chinese.transliteration.api.TransliterationSystem
import com.marvinelsen.chinese.transliteration.api.Zhuyin
import com.marvinelsen.crossstraits.api.CrossStraitsDefinition
import com.marvinelsen.crossstraits.api.CrossStraitsParser
import com.marvinelsen.moedict.api.MoedictDefinition
import com.marvinelsen.moedict.api.MoedictParser
import kotlinx.serialization.builtins.ListSerializer
import kotlinx.serialization.builtins.serializer
import kotlinx.serialization.json.Json
import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet
import java.util.zip.GZIPInputStream

const val JDBC_CONNECTION_STRING = "jdbc:sqlite:dictionary.db"

fun main() {
    val connection = DriverManager.getConnection(JDBC_CONNECTION_STRING).apply {
        autoCommit = false
    }

    val statement = connection.createStatement()
    statement.executeUpdate(
        """
        CREATE TABLE IF NOT EXISTS entry(
            id INTEGER PRIMARY KEY,
            traditional TEXT NOT NULL,
            simplified TEXT NOT NULL,
            pinyin_with_tone_marks TEXT NOT NULL,
            pinyin_with_tone_numbers TEXT NOT NULL,
            zhuyin TEXT NOT NULL,
            searchable_pinyin TEXT NOT NULL,
            searchable_pinyin_with_tone_numbers TEXT NOT NULL,
            cedict_definitions JSON NOT NULL,
            cross_straits_definitions JSON NOT NULL,
            moe_definitions JSON NOT NULL,
            character_count INTEGER NOT NULL,
            CONSTRAINT character_count_gte CHECK(character_count > 0)
        );
        """.trimIndent()
    )
    statement.executeUpdate("CREATE INDEX IF NOT EXISTS idx_entry_traditional ON entry (traditional)")
    statement.executeUpdate("CREATE INDEX IF NOT EXISTS idx_entry_simplified ON entry (simplified)")
    statement.executeUpdate("CREATE INDEX IF NOT EXISTS idx_entry_character_count ON entry (character_count)")
    statement.executeUpdate("CREATE INDEX IF NOT EXISTS idx_entry_searchable_pinyin ON entry (searchable_pinyin)")
    statement.executeUpdate(
        "CREATE INDEX IF NOT EXISTS idx_entry_searchable_pinyin_with_tone_numbers ON entry (searchable_pinyin_with_tone_numbers)"
    )

    val cedictParser = CedictParser.instance
    val cedictEntries =
        cedictParser.parseCedict(
            GZIPInputStream(object {}.javaClass.getResourceAsStream("/cedict_1_0_ts_utf-8_mdbg.txt.gz")!!)
        )

    val insertStatement =
        connection.prepareStatement(
            "INSERT OR IGNORE INTO entry(traditional, simplified, pinyin_with_tone_marks, pinyin_with_tone_numbers, zhuyin, searchable_pinyin, searchable_pinyin_with_tone_numbers, cedict_definitions, cross_straits_definitions, moe_definitions, character_count) VALUES(?,?,?,?,?,?,?,?,?,?,?)"
        )
    for (entry in cedictEntries) {
        try {
            insertStatement.setString(1, entry.traditional)
            insertStatement.setString(2, entry.simplified)
            insertStatement.setString(
                3,
                entry.pinyinSyllables.joinToString(
                    separator = " "
                ) { it.format(TransliterationSystem.PINYIN_WITH_TONE_MARKS) }
            )
            insertStatement.setString(
                4,
                entry.pinyinSyllables.joinToString(
                    separator = " "
                ) { it.format(TransliterationSystem.PINYIN_WITH_TONE_NUMBERS) }
            )
            insertStatement.setString(
                5,
                entry.pinyinSyllables.joinToString(
                    separator = Zhuyin.SEPARATOR
                ) { it.format(TransliterationSystem.ZHUYIN) }
            )
            insertStatement.setString(
                6,
                entry.pinyinSyllables.joinToString(
                    separator = ""
                ) {
                    it.toSearchablePinyin()
                }
            )
            insertStatement.setString(
                7,
                entry.pinyinSyllables.joinToString(
                    separator = ""
                ) {
                    it.toSearchablePinyinWithToneNumbers()
                }
            )
            insertStatement.setString(
                8,
                Json.encodeToString(
                    ListSerializer(ListSerializer(String.serializer())),
                    entry.definitions.map { it.glosses }
                )
            )
            insertStatement.setString(
                9,
                Json.encodeToString(
                    ListSerializer(String.serializer()),
                    emptyList()
                )
            )
            insertStatement.setString(
                10,
                Json.encodeToString(
                    ListSerializer(String.serializer()),
                    emptyList()
                )
            )
            insertStatement.setInt(11, entry.traditional.length)
        } catch (_: Exception) {
            // no-op
        }

        insertStatement.addBatch()
    }

    insertStatement.executeBatch()
    connection.commit()
    insertStatement.close()
    statement.close()

    createCrossStraitsEntries(connection)
    createMoedictEntries(connection)

    connection.close()
}

private fun createCrossStraitsEntries(connection: Connection) {
    val insertStatement =
        connection.prepareStatement(
            "INSERT OR IGNORE INTO entry(traditional, simplified, pinyin_with_tone_marks, pinyin_with_tone_numbers, zhuyin, searchable_pinyin, searchable_pinyin_with_tone_numbers, cedict_definitions, cross_straits_definitions, moe_definitions, character_count) VALUES(?,?,?,?,?,?,?,?,?,?,?)"
        )

    val updateStatement = connection.prepareStatement(
        "UPDATE entry SET cross_straits_definitions = ? WHERE id = ?"
    )
    val statement = connection.createStatement()

    val crossStraitsEntries =
        CrossStraitsParser.instance.parse(
            GZIPInputStream(object {}.javaClass.getResourceAsStream("/cross-straits.csv.gz")!!)
        )
    for (entry in crossStraitsEntries) {
        val pronunciation = entry.pronunciationTaiwan.ifEmpty { entry.pronunciationMainland }
        val rs: ResultSet = statement.executeQuery(
            "SELECT id FROM entry WHERE traditional = '${entry.traditional}' AND pinyin_with_tone_numbers = '${
                pronunciation.joinToString(
                    separator = " "
                ) { it.format(TransliterationSystem.PINYIN_WITH_TONE_NUMBERS) }
            }'"
        )
        if (rs.next()) {
            updateStatement.setString(
                1,
                Json.encodeToString(ListSerializer(CrossStraitsDefinition.serializer()), entry.definitions)
            )
            updateStatement.setInt(2, rs.getInt(1))
            updateStatement.executeUpdate()
        } else {
            try {
                insertStatement.setString(1, entry.traditional)
                insertStatement.setString(2, entry.simplified)
                insertStatement.setString(
                    3,
                    pronunciation.joinToString(
                        separator = " "
                    ) { it.format(TransliterationSystem.PINYIN_WITH_TONE_MARKS) }
                )
                insertStatement.setString(
                    4,
                    pronunciation.joinToString(
                        separator = " "
                    ) { it.format(TransliterationSystem.PINYIN_WITH_TONE_NUMBERS) }
                )
                insertStatement.setString(
                    5,
                    pronunciation.joinToString(separator = Zhuyin.SEPARATOR) { it.format(TransliterationSystem.ZHUYIN) }
                )
                insertStatement.setString(
                    6,
                    pronunciation.joinToString(separator = "") {
                        it.toSearchablePinyin()
                    }
                )
                insertStatement.setString(
                    7,
                    pronunciation.joinToString(separator = "") {
                        it.toSearchablePinyinWithToneNumbers()
                    }
                )
                insertStatement.setString(
                    8,
                    Json.encodeToString(
                        ListSerializer(String.serializer()),
                        emptyList()
                    )
                )
                insertStatement.setString(
                    9,
                    Json.encodeToString(
                        ListSerializer(CrossStraitsDefinition.serializer()),
                        entry.definitions
                    )
                )
                insertStatement.setString(
                    10,
                    Json.encodeToString(
                        ListSerializer(String.serializer()),
                        emptyList()
                    )
                )
                insertStatement.setInt(11, entry.traditional.length)
            } catch (_: Exception) {
                // no-op
            }

            insertStatement.addBatch()
        }
        rs.close()
    }

    insertStatement.executeBatch()
    connection.commit()
    insertStatement.close()
    statement.close()
}

private fun createMoedictEntries(connection: Connection) {
    val insertStatement =
        connection.prepareStatement(
            "INSERT OR IGNORE INTO entry(traditional, simplified, pinyin_with_tone_marks, pinyin_with_tone_numbers, zhuyin, searchable_pinyin, searchable_pinyin_with_tone_numbers, cedict_definitions, cross_straits_definitions, moe_definitions, character_count) VALUES(?,?,?,?,?,?,?,?,?,?,?)"
        )

    val updateStatement = connection.prepareStatement(
        "UPDATE entry SET moe_definitions = ? WHERE id = ?"
    )
    val statement = connection.createStatement()

    val moedictEntries =
        MoedictParser.instance.parse(GZIPInputStream(object {}.javaClass.getResourceAsStream("/moedict.json.gz")!!))
    moedictEntries.filterNot { it.title.startsWith('{') }.forEach { entry ->
        val traditional = entry.title.sanitizeMoeTitle()

        entry.heteronyms.filterNot { it.zhuyin.isNullOrBlank() }
            .forEach { heteronym ->
                val pronunciation = heteronym.zhuyin!!
                    .sanitizeMoeZhuyin()
                    .split(Zhuyin.SEPARATOR)
                    .filterNot { it.isBlank() }
                    .map { PinyinSyllable.fromZhuyin(it) }

                val rs: ResultSet = statement.executeQuery(
                    "SELECT id FROM entry WHERE traditional = '$traditional' AND pinyin_with_tone_numbers = '${
                        pronunciation.joinToString(
                            separator = " "
                        ) { it.format(TransliterationSystem.PINYIN_WITH_TONE_NUMBERS) }
                    }'"
                )
                if (rs.next()) {
                    updateStatement.setString(
                        1,
                        Json.encodeToString(ListSerializer(MoedictDefinition.serializer()), heteronym.definitions)
                    )
                    updateStatement.setInt(2, rs.getInt(1))
                    updateStatement.executeUpdate()
                } else {
                    try {
                        insertStatement.setString(1, traditional)
                        insertStatement.setString(2, ZhConverterUtil.toSimple(traditional))
                        insertStatement.setString(
                            3,
                            pronunciation.joinToString(
                                separator = " "
                            ) { it.format(TransliterationSystem.PINYIN_WITH_TONE_MARKS) }
                        )
                        insertStatement.setString(
                            4,
                            pronunciation.joinToString(
                                separator = " "
                            ) { it.format(TransliterationSystem.PINYIN_WITH_TONE_NUMBERS) }
                        )
                        insertStatement.setString(
                            5,
                            pronunciation.joinToString(separator = Zhuyin.SEPARATOR) {
                                it.format(
                                    TransliterationSystem.ZHUYIN
                                )
                            }
                        )
                        insertStatement.setString(
                            6,
                            pronunciation.joinToString(separator = "") {
                                it.toSearchablePinyin()
                            }
                        )
                        insertStatement.setString(
                            7,
                            pronunciation.joinToString(separator = "") {
                                it.toSearchablePinyinWithToneNumbers()
                            }
                        )
                        insertStatement.setString(
                            8,
                            Json.encodeToString(
                                ListSerializer(String.serializer()),
                                emptyList()
                            )
                        )
                        insertStatement.setString(
                            9,
                            Json.encodeToString(
                                ListSerializer(String.serializer()),
                                emptyList()
                            )
                        )
                        insertStatement.setString(
                            10,
                            Json.encodeToString(
                                ListSerializer(MoedictDefinition.serializer()),
                                heteronym.definitions
                            )
                        )
                        insertStatement.setInt(11, entry.title.length)
                    } catch (_: Exception) {
                        // no-op
                    }

                    insertStatement.addBatch()
                }
                rs.close()
            }
    }

    insertStatement.executeBatch()
    connection.commit()
    insertStatement.close()
    statement.close()
}

private fun String.sanitizeMoeZhuyin() = this
    .substringBefore("(變)")
    .substringAfter("（又音）")
    .substringAfter("（語音）")
    .replace("""（.*）""".toRegex(), "")
    .replace("""\(.*\)""".toRegex(), "")
    .replace("ㄉㄨㄜˇ", "ㄉㄨㄣˇ")
    .replace("ㄦ", "${Zhuyin.SEPARATOR}ㄦ")
    .replace("""\s+""".toRegex(), Zhuyin.SEPARATOR)

private fun String.sanitizeMoeTitle() = this
    .replace("""（.*）""".toRegex(), "")
    .replace("""\(.*）""".toRegex(), "")

private fun PinyinSyllable.toSearchablePinyinWithToneNumbers() =
    this.format(TransliterationSystem.PINYIN_WITH_TONE_NUMBERS).lowercase()

private fun PinyinSyllable.toSearchablePinyin() =
    this.toSearchablePinyinWithToneNumbers().replace("""\d""".toRegex(), "")
