package com.datacomsolusindo.migration

import com.datacomsolusindo.cpx_shared_code.entity.Account
import com.datacomsolusindo.cpx_shared_code.entity.Area
import com.datacomsolusindo.cpx_shared_code.entity.BaseEntity
import com.datacomsolusindo.cpx_shared_code.entity.Budget
import com.datacomsolusindo.cpx_shared_code.entity.CdrModifier
import com.datacomsolusindo.cpx_shared_code.entity.Corcos
import com.datacomsolusindo.cpx_shared_code.entity.CostCenter
import com.datacomsolusindo.cpx_shared_code.entity.Organization
import com.datacomsolusindo.cpx_shared_code.entity.Pbx
import com.datacomsolusindo.cpx_shared_code.entity.PhoneUser
import com.datacomsolusindo.cpx_shared_code.entity.PhoneUserPbx
import com.datacomsolusindo.cpx_shared_code.entity.Provider
import com.datacomsolusindo.cpx_shared_code.entity.Rate
import com.datacomsolusindo.cpx_shared_code.entity.Rights
import com.datacomsolusindo.cpx_shared_code.entity.Transaction
import com.datacomsolusindo.cpx_shared_code.entity.Trunk
import com.datacomsolusindo.cpx_shared_code.entity.WebUser
import com.datacomsolusindo.cpx_shared_code.service.ApiService
import com.datacomsolusindo.cpx_shared_code.service.CpDecrypt
import com.datacomsolusindo.cpx_shared_code.utility.*
import io.azam.ulidj.ULID
import io.github.semutkecil.simplecriteria.FilterData
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.context.annotation.Bean
import org.springframework.context.annotation.Configuration
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder
import org.springframework.security.crypto.factory.PasswordEncoderFactories
import org.springframework.security.crypto.password.PasswordEncoder
import org.springframework.stereotype.Service
import org.springframework.transaction.annotation.Transactional
import java.io.File
import java.nio.charset.StandardCharsets
import java.util.UUID
import kotlin.time.measureTime
import kotlin.time.measureTimedValue


@Service
class MigrationEntity(val passwordEncoder: PasswordEncoder, val queryNativeService: QueryNativeService) {

    @Autowired
    lateinit var apiService: ApiService

    @Autowired
    lateinit var cpDecrypt: CpDecrypt

    private val logger = SimpleLogger.getLogger(this::class.java)

    fun <T> dataToMap(
        clazz: Class<T>,
        fields: Map<String, String>,
        unique: String,
        rootFile: File,
        historyFile: File? = null,
        groupFile: File? = null
    ): List<MutableMap<String, Any?>> {
        logger.info("prepare data migration class ${clazz.simpleName}")
        val process = measureTimedValue {
            val historyData = historyFile?.let { readQueryDataToMap(it) }
            val groupData = groupFile?.let { readQueryDataToMap(it) }
            val rootData = readQueryDataToMap(rootFile)
            val mapRootData = rootData.map { map ->
                val data: MutableMap<String, Any?> = mutableMapOf()
                val fieldRoots = fields.toList().filterNot { f -> f.second.contains(".") }
                val joinRoots = fields.toList().filter { f -> f.second.contains(".") }

                fieldRoots.forEach { f ->
                    data[f.first] = map[f.second]
                }
                joinRoots.sortedByDescending { it.second }.forEach { f ->
                    data[f.first] = when {
                        f.second.startsWith("history.") ->
                            getValueAnotherFile(fields, historyData, unique, data, f.first, f.second, "history")

                        f.second.toString().startsWith("group.") ->
                            getValueAnotherFile(fields, groupData, unique, data, f.first, f.second, "group")

                        else -> map[f.second]
                    }
                }

//                fields.forEach { (t, u) ->
//                    data[t] = when {
//                        u.startsWith("history.") ->
//                            getValueAnotherFile(fields, historyData, unique, data, t, u, "history")
//
//                        u.startsWith("group.") -> getValueAnotherFile(fields, groupData, unique, data, t, u, "group")
//                        else -> map[u]
//                    }
//                }
                data
            }
            mapRootData
        }
        logger.info("finish prepare data [${process.value.size}] migration class ${clazz.simpleName} takes time ${process.duration.inWholeMilliseconds}ms")
        return process.value
    }

    private fun getValueAnotherFile(
        fields: Map<String, String>,
        dataFile: List<Map<String, Any?>>?,
        unique: String,
        rootData: Map<String, Any?>,
        keyRoot: String,
        valRoot: String,
        key: String
    ): Any? {
        val uniqueField = dataFile?.firstOrNull()?.let {
            if (it.any { a -> a.key == fields[unique] }) unique else "id"
        } ?: "id"
        val fieldUnique = fields[uniqueField]!!.split(".").last()

        val value = dataFile?.firstOrNull { f ->
            f[fieldUnique].toString() == rootData[uniqueField].toString()
        }?.get(valRoot.replace("${key}.", ""))

        return if (keyRoot.contains("_")) value?.let { id ->
            dataFile.firstOrNull { f ->
                f[fields["id"]!!.split(".").last()].toString() == id.toString()
            }?.get(fields["code"]) ?: value
        } else value
    }

    private fun readQueryDataToMap(file: File): List<Map<String, Any?>> {
        val map = measureTimedValue {
            file.bufferedReader(StandardCharsets.UTF_8)
                .readLines().filter {
                    it.startsWith("INSERT")
                }.mapNotNull {
                    try {
                        insertSqlToMap(it)
                    } catch (_: Exception) {
                        null
                    }
                }
        }
        logger.info("migration read query data ${map.value.size} takes time ${map.duration.inWholeMilliseconds}")
        return map.value
    }

    private fun insertSqlToMap(sql: String): Map<String, Any?> {
        val splitSql = sql.split(") VALUES (")
        val fieldColumn = splitSql[0].split("(").last().split(",")
            .map { it.trim().removeSurrounding("[", "]") }
        val valueColumn = splitValues(splitSql[1].dropLast(1))
            .map { parseValue(it.trim()) }
        val map: MutableMap<String, Any?> = mutableMapOf()
        fieldColumn.mapIndexed { index, key ->
            map[key] = valueColumn[index]
        }
        return map
    }

    private fun splitValues(input: String): List<String> {
        val result = mutableListOf<String>()
        var depth = 0
        var current = StringBuilder()
        for (c in input) {
            when (c) {
                '(' -> depth++
                ')' -> depth--
                ',' -> {
                    if (depth == 0) {
                        result.add(current.toString())
                        current = StringBuilder()
                        continue
                    }
                }
            }
            current.append(c)
        }
        result.add(current.toString())
        return result
    }

    private fun parseValue(raw: String): Any? {
        val value = raw.trim()
        if (value.equals("NULL", true)) return null
        // CAST(x AS ...)
        if (value.startsWith("CAST", true)) {
            val inner = Regex("CAST\\((.*)\\s+AS", RegexOption.IGNORE_CASE)
                .find(value)?.groupValues?.get(1)?.trim()
            return parseValue(inner ?: value)
        }
        // N'string'
        if (value.startsWith("N'") && value.endsWith("'")) {
            return value.substring(2, value.length - 1)
        }
        // 'string'
        if (value.startsWith("'") && value.endsWith("'")) {
            return value.substring(1, value.length - 1)
        }
        // number
        return value.toLongOrNull()
            ?: value.toDoubleOrNull()
            ?: value
    }

    private val failed: MutableList<Any> = mutableListOf()
    fun <T : BaseEntity> execute(
        clazz: Class<T>,
        fields: Map<String, String>,
        rootFile: File,
        historyFile: File?,
        groupFile: File?
    ): List<MutableMap<String, Any?>> {
        val data = dataToMap(clazz, fields, "code", rootFile, historyFile, groupFile)
        return data

//        val process = measureTime { insertData(clazz, data) }
//        logger.info(
//            "finished process migration ${clazz.simpleName} " +
//                    "data ${data.size} " +
//                    "success ${data.size - failed.size} " +
//                    "failed ${failed.size} " +
//                    "takes time ${process.inWholeMilliseconds}ms"
//        )
    }

    fun clazzEntity(migrationTarget: String): Class<out BaseEntity>? {
        return when (migrationTarget) {
            "organization" -> Organization::class.java
            "costCenter" -> CostCenter::class.java
            "pbx" -> Pbx::class.java
            "trunk" -> Trunk::class.java
            "callTransaction" -> Transaction::class.java
            "account" -> Account::class.java
            "area" -> Area::class.java
            "cdrModifier" -> CdrModifier::class.java
            "phoneUser" -> PhoneUser::class.java
            "provider" -> Provider::class.java
            "rate" -> Rate::class.java
            "webUser" -> WebUser::class.java
            "corcos" -> Corcos::class.java
            else -> null
        }
    }

    fun <T : BaseEntity> insertData(clazz: Class<T>, dataMap: List<MutableMap<String, Any?>>): MutableList<Any> {
        failed.clear()
        val sortingData = if (dataMap.any { it.any { a -> a.key == "structure" } }) {
            dataMap.sortedBy { f -> f["structure"].toString().length }
        } else dataMap
        sortingData.forEach { map ->
            try {
                val finalizer = finalizeMap(clazz.simpleName.camelCase(), map)
                val phoneUserPin = finalizer["pin"]
                val phoneUserExtension = finalizer["extension"]

                // budget
                val budgetAnnual = finalizer["budget.maxCost"]?.toString()?.toDoubleOrNull()
                val warningAnnual = finalizer["budget.warnCost"]?.toString()?.toDoubleOrNull()
                val budgetMaxCost = budgetAnnual?.let {
                    val monthBudget = it / 12.0
                    List(12) { monthBudget }.joinToString(";")
                }
                val budgetWarnCost = warningAnnual?.let {
                    val monthBudget = budgetAnnual?.let { b -> b / 12.0 } ?: 0.0
                    val warnPercent = ((it / 12.0) / monthBudget) * 100
                    List(12) { "%.2f".format(warnPercent) }.joinToString(";")
                }

                // phoneUserPbx
                val phoneUserPbxIds: MutableList<Any> = mutableListOf()
                val data = if (clazz.simpleName == "PhoneUser") {
                    finalizer["pbx.list"]?.toString()?.let {
                        it.split(";").forEach { fi ->
                            findId(Pbx::class.java, fi)?.let { id -> phoneUserPbxIds.add(id) }
                        }
                    }
                    finalizer["pbx_id"]?.toString()?.let {
                        if (it.isNotBlank()) {
                            phoneUserPbxIds.add(it)
                        }
                    }
                    val finalMap = finalizer.filterNot { fi ->
                        listOf(
                            "pbx.list",
                            "pbx_id",
                            "extension",
                            "pin",
                            "budget.maxCost",
                            "budget.warnCost",
                            "maxCost",
                            "warnCost"
                        ).any { a -> a == fi.key }
                    } as MutableMap<String, Any?>
                    queryNativeService.insertDataWithNativeQuery(clazz, finalMap)
//                    apiService.create(clazz, finalMap)
                } else {
//                    apiService.create(clazz, finalizer)
                    queryNativeService.insertDataWithNativeQuery(clazz, finalizer)
                }

                // create budget
                if (BudgetUserType.entries.any { a -> a.name.snakeToCamel() == clazz.simpleName.camelCase() }) {
//                    val id = clazz.collectAllField().findId().value(data)
//                    val res = apiService.findById(clazz, id!!, listOf("uid"))
//                    res?.get("uid")?.toString()?.let { uid ->
//                    apiService.create(
//                        Budget::class.java,
//                        mutableMapOf(
//                            "userType" to BudgetUserType.valueOf(
//                                clazz.simpleName.camelCase().camelToSnake().uppercase()
//                            ),
//                            "userUid" to data,
//                            "type" to "FLAT"
//                        ),
//                    )

                    queryNativeService.insertDataWithNativeQuery(
                        Budget::class.java, mutableMapOf(
                            "userType" to BudgetUserType.valueOf(
                                clazz.simpleName.camelCase().camelToSnake().uppercase()
                            ).ordinal,
                            "userUid" to data,
                            "type" to BudgetType.FLAT.ordinal,
                            "annualCost" to budgetAnnual,
                            "accumulate" to 0,
                            "maxCost" to (budgetMaxCost ?: "0;0;0;0;0;0;0;0;0;0;0;0"),
                            "warnCostPercentage" to (budgetWarnCost ?: "0;0;0;0;0;0;0;0;0;0;0;0"),
                            "tempCost" to "0;0;0;0;0;0;0;0;0;0;0;0"
                        )
                    )

                    // create phoneUserPbx
                    if (phoneUserPbxIds.isNotEmpty()) {
                        phoneUserPbxIds.forEach { pbxId ->
                            queryNativeService.insertDataWithNativeQuery(
                                PhoneUserPbx::class.java, mutableMapOf(
                                    "pin" to phoneUserPin,
                                    "extension" to phoneUserExtension,
                                    "pbx_id" to pbxId,
                                    "phoneUser_id" to data,
                                )
                            )
//                            apiService.create(
//                                PhoneUserPbx::class.java, mutableMapOf(
//                                    "pin" to phoneUserPin,
//                                    "extension" to phoneUserExtension,
//                                    "pbx_id" to pbxId,
//                                    "phoneUser_id" to id,
//                                )
//                            )
                        }
                    }
//                    }
                }
            } catch (e: Exception) {
                failed.add(map)
//                logger.error("failed insert data migration", e)
            }
        }
        return failed
    }

    private fun finalizeMap(className: String, map: MutableMap<String, Any?>): MutableMap<String, Any?> {
        val mapFinalize: MutableMap<String, Any?> = mutableMapOf()
        map["code"]?.toString()?.let {
            map["code"] = it.ifBlank { UUID.randomUUID().toString().take(6) }
        }
        map["name"]?.toString()?.let {
            map["name"] = it.ifBlank { "Auto ${(0..99999).toString().padStart(5, '0')}" }
        }

        map["emailOnOverBudget"]?.let {
            map["emailOnOverBudget"] = it.toString() == "1"
        }

        map["direction"]?.toString()?.let {
            map["direction"] = if (className == "transaction") {
                it.split("").mapNotNull { m ->
                    when (m.trim()) {
                        "C" -> Direction.INCOMING.ordinal //"C"
                        "G" -> Direction.OUTGOING.ordinal //"G"
                        "I" -> Direction.INTERNAL.ordinal //"I"
                        else -> null
                    }
                }.joinToString(";")
            } else {
                it.split("").mapNotNull { m ->
                    when (m.trim()) {
                        "C" -> "C"
                        "G" -> "G"
                        "I" -> "I"
                        else -> null
                    }
                }.joinToString(";")
            }
        }

        map.filterNot { it.key == "id" || it.key == "structure" }.forEach { (t, u) ->
            if (t.contains("_")) {
                val isParent = t.startsWith("parent")
                val value = if (t == "pbx_id") {
                    findId(Pbx::class.java, u ?: "PBX01")
                } else u?.toString()?.let { code ->
                    val clazzEntity = clazzEntity(if (isParent) className else t.split("_")[0])
                    clazzEntity?.let { findId(it, code) }
                }

                if (value != null) {
                    mapFinalize[t] = value
                }
            } else {
                mapFinalize[t] = u
            }
        }

        mapFinalize["pin"]?.toString()?.let {
            mapFinalize["pin"] = if (it.isBlank()) null
            else cpDecrypt.decrypt(it)?.let { p -> ToolAes.encrypt(p) }
        }

        mapFinalize["password"]?.toString()?.let {
            mapFinalize["password"] = if (it.isBlank()) ""
            else cpDecrypt.decrypt(it)?.let { p -> passwordEncoder.encode(p) } ?: ""
        }

        if (className == "transaction") {
            val to = mapFinalize["extTransferTo"]?.toString() ?: ""
            val from = mapFinalize["extTransferFrom"]?.toString() ?: ""
            mapFinalize["transferType"] = when {
                to.isBlank() && from.isBlank() -> TransferType.DIRECT.ordinal
                to.isNotBlank() && from.isBlank() -> TransferType.TRANSFER_TO.ordinal
                to.isBlank() && from.isNotBlank() -> TransferType.TRANSFER_FROM.ordinal
                else -> TransferType.TRANSFER_FROM_AND_TRANSFER_TO.ordinal
            }
        }

        if (className == "webUser") {
            defaultProfile?.let { mapFinalize["profile_id"] = it }
        }

        if (className == "corcos") {
            mapFinalize["name"] = "Corcos ${map["name"]}"
        }

        return mapFinalize.mapValues { v -> v.value?.toString() } as MutableMap<String, Any?>
    }

    private val defaultProfile: Any? by lazy {
        apiService.findListPage(
            Rights::class.java,
            listOf("uid"),
            FilterData.filter("name", FilterData.FILTEROP.EQ, "Default")
        ).firstOrNull()?.get("uid")
    }

    private fun toSnakeCase(input: String): String {
        return input
            .replace(Regex("([a-z0-9])([A-Z])"), "$1_$2")
            .lowercase()
    }

    private fun <T : BaseEntity> findId(clazz: Class<T>, value: Any): String? {
        return try {
            val query = "SELECT uid FROM ${toSnakeCase(clazz.simpleName)} WHERE code = :code "
            val id = apiService.em.createNativeQuery(query, String::class.java)
                .setParameter("code", value)
                .singleResult as String
            id
        } catch (_: Exception) {
            null
        }
    }

}

fun String.camelToSnake(): String =
    replace(Regex("([a-z0-9])([A-Z])"), "$1_$2")
        .lowercase()

fun String.camelCase(): String {
    return when {
        this.contains("_") || this.contains("-") || this.contains(" ") -> {
            this
                .lowercase()
                .split("_", "-", " ")
                .filter { it.isNotBlank() }
                .mapIndexed { index, word ->
                    if (index == 0) word
                    else word.replaceFirstChar { it.uppercase() }
                }
                .joinToString("")
        }

        else -> {
            this.replaceFirstChar { it.lowercase() }
        }
    }
}

fun String.snakeToCamel(): String {
    return this.lowercase()
        .split("_")
        .mapIndexed { index, s ->
            if (index == 0) s else s.replaceFirstChar { it.uppercase() }
        }
        .joinToString("")
}

@Configuration
class SecurityConfig {

    @Bean
    fun passwordEncoder(): PasswordEncoder = PasswordEncoderFactories.createDelegatingPasswordEncoder()

}

@Service
@Transactional
class QueryNativeService(val apiService: ApiService) {

    fun <T> insertDataWithNativeQuery(clazz: Class<T>, map: MutableMap<String, Any?>): String? {
        val uid = ULID.random()
        val fields = mutableListOf("uid")
        map.keys.forEach {
            val t = it.replace("_id", "_uid")
            fields.add(t)
        }
        val structure = map["parent_id"]?.toString()?.let {
            fields.add("structure")
            "${EntityUtility(apiService, Organization::class.java).parentStructure(it)}|$uid"
        }

        val tableName = when (clazz.simpleName.lowercase()) {
            "phoneuserpbx" -> "phoneuserpbx"
            "transaction" -> {
                fields.remove("uid")
                "calltransaction"
            }

            else -> clazz.simpleName.camelToSnake().lowercase()
        }

        val query = "INSERT INTO $tableName (${fields.joinToString() { it.camelToSnake() }}) " +
                "VALUES (${fields.joinToString() { ":$it" }})"

        val sqlNative = apiService.em.createNativeQuery(query)

        if (clazz.simpleName.lowercase() != "transaction") {
            sqlNative.setParameter("uid", uid)
        }

        structure?.let { sqlNative.setParameter("structure", structure) }
        map.forEach { (t, u) -> sqlNative.setParameter(t.replace("_id", "_uid"), u) }
        sqlNative.executeUpdate()
        return uid
    }

}