package com.marvinelsen.willow.domain

import com.github.houbb.segment.bs.SegmentBs
import com.github.houbb.segment.data.phrase.core.data.SegmentPhraseDatas
import com.github.houbb.segment.data.pos.core.data.SegmentPosDatas
import com.github.houbb.segment.support.format.impl.SegmentFormats
import com.github.houbb.segment.support.segment.impl.Segments
import com.github.houbb.segment.support.segment.mode.impl.SegmentModes
import com.github.houbb.segment.support.segment.result.impl.SegmentResultHandlers
import com.github.houbb.segment.support.tagging.pos.tag.impl.SegmentPosTaggings
import kotlinx.serialization.json.Json
import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.ResultSet

class SqliteDictionary(private val connection: Connection) : Dictionary {
    private val whitespaceRegex = """\s+""".toRegex()

    private val segmentBs = SegmentBs.newInstance()
        .segment(Segments.defaults())
        .segmentData(SegmentPhraseDatas.define())
        .segmentMode(SegmentModes.dict())
        .segmentFormat(SegmentFormats.chineseSimple())
        .posTagging(SegmentPosTaggings.simple())
        .posData(SegmentPosDatas.define())

    private val searchSimplifiedPreparedStatement: PreparedStatement by lazy {
        connection.prepareStatement(
            """
            SELECT traditional, simplified, pinyin_with_tone_marks, pinyin_with_tone_numbers, zhuyin, cedict_definitions, cross_straits_definitions, moe_definitions
            FROM entry 
            WHERE simplified GLOB ?
            ORDER BY character_count ASC
            """.trimIndent()
        )
    }

    private val searchTraditionalPreparedStatement: PreparedStatement by lazy {
        connection.prepareStatement(
            """
            SELECT traditional, simplified, pinyin_with_tone_marks, pinyin_with_tone_numbers, zhuyin, cedict_definitions, cross_straits_definitions, moe_definitions
            FROM entry 
            WHERE traditional GLOB ?
            ORDER BY character_count ASC
            """.trimIndent()
        )
    }

    private val searchPinyinPreparedStatement: PreparedStatement by lazy {
        connection.prepareStatement(
            """
            SELECT traditional, simplified, pinyin_with_tone_marks, pinyin_with_tone_numbers, zhuyin, cedict_definitions, cross_straits_definitions, moe_definitions
            FROM entry 
            WHERE searchable_pinyin GLOB ?
            OR searchable_pinyin_with_tone_numbers GLOB ?
            ORDER BY character_count ASC
            """.trimIndent()
        )
    }

    private val searchSegments = """
            WITH cte(id, segment) AS (VALUES ?)
            SELECT entry.traditional, simplified, pinyin_with_tone_marks, pinyin_with_tone_numbers, zhuyin, cedict_definitions, cross_straits_definitions, moe_definitions
            FROM entry INNER JOIN cte
            ON cte.segment = entry.traditional OR cte.segment = entry.simplified
            ORDER BY cte.id
    """.trimIndent()

    private val findWordsContaining: PreparedStatement by lazy {
        connection.prepareStatement(
            """
            SELECT traditional, simplified, pinyin_with_tone_marks, pinyin_with_tone_numbers, zhuyin, cedict_definitions, cross_straits_definitions, moe_definitions
            FROM entry
            WHERE traditional LIKE ?
            ORDER BY character_count ASC
            """.trimIndent()
        )
    }

    private val findCharacters = """
            WITH cte(id, character, syllable) AS (VALUES ?)
            SELECT traditional, simplified, pinyin_with_tone_marks, pinyin_with_tone_numbers, zhuyin, cedict_definitions, cross_straits_definitions, moe_definitions
            FROM entry INNER JOIN cte
            ON cte.character = entry.traditional OR cte.character = entry.simplified
            WHERE cte.syllable = entry.pinyin_with_tone_numbers
            ORDER BY cte.id
    """.trimIndent()

    override fun search(query: String, searchMode: SearchMode) = when (searchMode) {
        SearchMode.PINYIN -> searchPinyin(query)
        SearchMode.SIMPLIFIED -> searchSimplified(query)
        SearchMode.TRADITIONAL -> searchTraditional(query)
        SearchMode.SEGMENTS -> searchSegments(query)
    }

    override fun findWordsContaining(entry: DictionaryEntry): List<DictionaryEntry> {
        findWordsContaining.setString(1, "_%${entry.traditional}%")

        val resultSet: ResultSet = findWordsContaining.executeQuery()

        return resultSet.toListOfDictionaryEntries()
    }

    override fun findSentencesContaining(entry: DictionaryEntry): List<DictionaryEntry> {
        return emptyList()
    }

    override fun findCharacters(entry: DictionaryEntry): List<DictionaryEntry> {
        val pinyinSyllablesWithToneNumbers = entry.pinyinWithToneNumbers
            .lowercase()
            .split(" ")
            .filter { it.isNotBlank() }

        val characters = entry.traditional
            .split("")
            .filter { it.isNotBlank() }
            .filter { it != "，" }
            .filter { it != "·" }

        val charactersWithSyllables = characters.zip(pinyinSyllablesWithToneNumbers)

        val queryInput = charactersWithSyllables
            .mapIndexed { index, s -> "($index, '${s.first}', '${s.second}')" }
            .joinToString(",")

        val query = findCharacters.replace("?", queryInput)

        val resultSet: ResultSet = connection.createStatement().executeQuery(query)

        return resultSet.toListOfDictionaryEntries()
    }

    override fun searchSegments(phrase: String): List<DictionaryEntry> {
        val segments = segmentBs.segment(phrase, SegmentResultHandlers.word())

        val segmentsListString = segments
            .mapIndexed { index, s -> "($index, '$s')" }
            .joinToString(",")

        val query = searchSegments.replace("?", segmentsListString)

        val resultSet: ResultSet = connection.createStatement().executeQuery(query)

        return resultSet.toListOfDictionaryEntries()
    }

    private fun searchSimplified(query: String): List<DictionaryEntry> {
        searchSimplifiedPreparedStatement.setString(1, "$query*")

        val resultSet: ResultSet = searchSimplifiedPreparedStatement.executeQuery()

        return resultSet.toListOfDictionaryEntries()
    }

    private fun searchPinyin(query: String): List<DictionaryEntry> {
        val sanitizedQuery = query.lowercase().replace(whitespaceRegex, "")

        searchPinyinPreparedStatement.setString(1, "$sanitizedQuery*")
        searchPinyinPreparedStatement.setString(2, "$sanitizedQuery*")

        val resultSet: ResultSet = searchPinyinPreparedStatement.executeQuery()

        return resultSet.toListOfDictionaryEntries()
    }

    private fun searchTraditional(query: String): List<DictionaryEntry> {
        searchTraditionalPreparedStatement.setString(1, "$query*")

        val resultSet: ResultSet = searchTraditionalPreparedStatement.executeQuery()

        return resultSet.toListOfDictionaryEntries()
    }
}

@Suppress("MagicNumber")
private fun ResultSet.toDictionaryEntry() = DictionaryEntry(
    traditional = this.getString(1),
    simplified = this.getString(2),
    pinyinWithToneMarks = this.getString(3),
    pinyinWithToneNumbers = this.getString(4),
    zhuyin = this.getString(5),
    cedictDefinitions = Json.decodeFromString(this.getString(6)),
    crossStraitsDefinitions = Json.decodeFromString(this.getString(7)),
    moedictDefinitions = Json.decodeFromString(this.getString(8)),
)

private fun ResultSet.toListOfDictionaryEntries() = buildList {
    this@toListOfDictionaryEntries.use {
        while (it.next()) {
            add(it.toDictionaryEntry())
        }
    }
}
