package com.perkelle.dev.bot.wrappers

import com.perkelle.dev.bot.utils.onComplete
import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
import kotlinx.coroutines.experimental.async
import kotlinx.coroutines.experimental.launch
import java.sql.*
import java.sql.Date
import java.util.*

abstract class SQLWrapper(private val host: String, private val username: String, private val password: String, private val database: String, private val port: Int = 3306) {

    companion object {
        var threadPoolSize = 10

        var connected = false
        lateinit var ds: HikariDataSource
    }

    val ALL = "*"

    fun login() : Boolean {
        return try {
            if(connected) return true

            val timezone = TimeZone.getDefault().id

            val config = HikariConfig()
            config.jdbcUrl = "jdbc:mysql://$host:$port/$database"
            config.username = username
            config.password = password
            config.addDataSourceProperty("autoReconnect", true)
            config.addDataSourceProperty("useJDBCCompliantTimezoneShift", true)
            config.addDataSourceProperty("useLegacyDatetimeCode", false)
            config.addDataSourceProperty("serverTimezone", timezone)
            config.addDataSourceProperty("cachePrepStmts", true)
            config.addDataSourceProperty("prepStmtCacheSize", 250)
            config.addDataSourceProperty("prepStmtCacheSqlLimit", 2048)
            config.addDataSourceProperty("maxIdle", 1)
            config.maximumPoolSize = threadPoolSize

            ds = HikariDataSource(config)
            connected = true

            true
        } catch(ex: SQLException) {
            ex.printStackTrace()
            false
        }
    }

    abstract fun setup()

    fun closeConnection() = ds.close()

    /**
     * @param name The name of the table
     * @param keys A list of SQLKeys
     */
    fun createTable(name: String, keys: List<SQLKey>) {
        execute("CREATE TABLE IF NOT EXISTS $name (${keys.joinToString(", ", transform = {
                "${it.name} " +
                        it.dataType.name +
                "${if(it.length != null)  "(${it.length})" else ""} " +
                if(it.autoIncrement) " AUTO INCREMENT" else "" +
                if(it.primaryKey) " PRIMARY KEY" else "" +
                if(it.unique) " UNIQUE" else ""
        })});")
    }

    fun selectData(table: String, fields: MutableList<String>, condition: ConditionBuilder? = null, callback: (List<SQLRow>) -> Unit) {
        if(fields.contains("*")) throw UnsupportedOperationException("* is unsupported")

        val injects = mutableListOf<String>()
        condition?.inserts?.forEach { injects.add(it.toString()) }

        executeQuery("SELECT ${fields.joinToString(", ")} FROM $table ${condition?.let { "WHERE ${it.condition}" } ?: "" };", *injects.toTypedArray()) { rs ->
            val rows = mutableListOf<SQLRow>()

            while(rs.next()) {
                rows.add(fields.map { it to rs.getString(it) }.toMap())
            }

            callback(rows)
        }
    }

    fun count(table: String, condition: ConditionBuilder? = null, callback: (Int) -> Unit) {
        val injects = mutableListOf<String>()
        condition?.inserts?.forEach { injects.add(it.toString()) }

        executeQuery("SELECT COUNT(*) FROM $table ${condition?.let { "WHERE ${condition.condition}" } ?: "" };", *injects.toTypedArray()) {
            callback(
                    if(it.next()) it.getInt(1)
                    else 0
            )
        }
    }

    fun getColumns(table: String, callback: (List<String>) -> Unit) {
        executeQuery("SHOW COLUMNS FROM ?;", table) {
            val columns = mutableListOf<String>()
            while(it.next())
                columns.add(it.getString(1))
        }
    }

    /**
     * @param table The table name
     * @param fields Field name, value
     * @param upserts If a unique key is already present, should we update it? Null if you don't want this to happen or there are no unique keys. The list should contain the unique keys.
     */
    fun insert(table: String, fields: Map<String, Any>, upserts: List<String>? = null) {
        fun Any.enclose() =
                if(this is Int || this is Long || this is Double || this is Float) "$this"
                else "'$this'"

        val statement = "INSERT INTO $table (" +
                "${fields.keys.joinToString(",")}) VALUES(" +
                "${"?,".repeat(fields.values.size).substring(0, (fields.values.size * 2) - 1)}) " +
                "${upserts?.let { " ON DUPLICATE KEY UPDATE " +
                        "${upserts.joinToString(",") { "`$it` = ${fields[it]!!.enclose()} " }} " }};"

        execute(statement, *fields.values.toTypedArray())
    }

    fun remove(table: String, condition: ConditionBuilder? = null) {
        val injects = mutableListOf<String>()
        condition?.inserts?.forEach { injects.add(it.toString()) }

        execute("DELETE FROM $table ${condition?.let { " WHERE ${it.condition}" } ?: ""};", *injects.toTypedArray())
    }

    fun update(table: String, fieldToUpdate: String, newValue: Any, condition: ConditionBuilder? = null) {
        val injects = mutableListOf<String>()
        condition?.inserts?.forEach { injects.add(it.toString()) }

        execute( "UPDATE $table SET $fieldToUpdate=${newValue as? Number ?: "'$newValue'"} ${condition?.condition};", *injects.toTypedArray())
    }

    /**
     * @param name The name of the key
     * @param dataType @see SQLDataTypes
     * @param length The max length of the data, e.g. VARCHAR(36). Null for thing like TEXT where it doesn't exist
     * @param unique Is it a unique key
     * @param autoIncrement Is it an auto incremental key (ints only)
     */
    data class SQLKey(val name: String, val dataType: SQLDataTypes, val length: Int?, val unique: Boolean, val autoIncrement: Boolean = false, val primaryKey: Boolean = false)

    /**
     * @param The key. If you do SELECT * FROM myTable WHERE `NAME` = 'RYAN'; the key will be RYAN
     * @param values Column, Value
     */

    /**
     * All SQL datatypes
     */
    enum class SQLDataTypes {
        CHAR,
        VARCHAR,
        TINYTEXT,
        TEXT,
        MEDIUMTEXT,
        LONGTEXT,
        TINYINT,
        SMALLINT,
        MEDIUMINT,
        INT,
        BIGINT,
        FLOAT,
        //DOUBLE - Not Supported
        //DECIMAL - Not Supported
        DATE,
        DATETIME,
        TIMESTAMP,
        TIME,
        ENUM,
        SET,
        BINARY
    }

    class ConditionBuilder {

        var condition = ""
        val inserts = mutableListOf<Any>()

        fun or(): ConditionBuilder {
            condition+=" OR "
            return this
        }

        fun and(): ConditionBuilder {
            condition+= " AND "
            return this
        }

        fun equalTo(comparison: Pair<String, Any>): ConditionBuilder {
            condition += "${comparison.first} = ?"

            inserts.add(comparison.second)
            return this
        }

        fun greaterThan(comparison: Pair<String, Int>): ConditionBuilder {
            condition += "${comparison.first} > ?"

            inserts.add(comparison.second)
            return this
        }

        fun lessThan(comparison: Pair<String, Int>): ConditionBuilder {
            condition+= "${comparison.first} < ?"

            inserts.add(comparison.second)
            return this
        }
    }

    private fun execute(toExecute: String, vararg injectValues: Any) {
        launch {
            val conn = ds.connection
            val ps = conn.prepareStatement(toExecute)
            ps.injectVariables(injectValues)
            ps.execute()
            ps.close()
            conn.close()
        }
    }

    private fun executeQuery(query: String, vararg injectValues: Any, callback: (ResultSet) -> Unit) {
        async {
            val conn = ds.connection
            val ps = conn.prepareStatement(query)
            ps.injectVariables(injectValues)
            val rs = ps.executeQuery()
            return@async Triple(rs, ps, conn)
        }.onComplete {
            callback(it.first)
            it.first.close()
            it.second.close()
            it.third.close()
        }
    }

    private fun PreparedStatement.injectVariables(injectValues: Array<out Any>) {
        injectValues.withIndex().map { it.index + 1 to it.value }.forEach { (index, value) ->
            when (value) {
                is String -> setString(index, value)
                is Byte -> setByte(index, value)
                is Boolean -> setBoolean(index, value)
                is Float -> setFloat(index, value)
                is Double -> setDouble(index, value)
                is Int -> setInt(index, value)
                is Long -> setLong(index, value)
                is Short -> setShort(index, value)
                is Time -> setTime(index, value)
                is Timestamp -> setTimestamp(index, value)
                is Date -> setDate(index, value)
            }
        }
    }
}

typealias SQLRow = Map<String, String>