package com.vgmlr.shim

import android.content.ContentValues
import android.content.Context
import android.net.Uri
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import java.io.File
import androidx.core.database.sqlite.transaction
import org.json.JSONArray
import org.json.JSONObject

class ShimDatabase(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    companion object {
        private const val DATABASE_NAME = "shim.db"
        private const val DATABASE_VERSION = 6
        private val SQLITE_MAGIC = "SQLite format 3\u0000".toByteArray(Charsets.US_ASCII)
        private const val SNIFF_LEN = 512
        const val TABLE_SHIMS = "shims"
        const val COLUMN_ID = "id"
        const val COLUMN_TEXT = "shimtext"
        const val COLUMN_HASH = "shimhash"
        const val COLUMN_TIME = "shimtime"
        private const val CREATE_TABLE = "CREATE TABLE $TABLE_SHIMS (" +
                "$COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "$COLUMN_TEXT TEXT, " +
                "$COLUMN_HASH TEXT, " +
                "$COLUMN_TIME TEXT)"
    }

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(CREATE_TABLE)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL("DROP TABLE IF EXISTS $TABLE_SHIMS")
        onCreate(db)
    }

    fun addShim(text: String, hash: String, time: String): Long {
        val db = this.writableDatabase
        val values = ContentValues().apply {
            put(COLUMN_TEXT, text)
            put(COLUMN_HASH, hash)
            put(COLUMN_TIME, time)
        }
        return db.insert(TABLE_SHIMS, null, values)
    }

    fun getAllShims(): List<ShimClass> {
        val shimList = mutableListOf<ShimClass>()
        val db = this.readableDatabase
        val cursor = db.rawQuery("SELECT * FROM $TABLE_SHIMS ORDER BY $COLUMN_ID DESC", null)
        if (cursor.moveToFirst()) {
            do {
                shimList.add(ShimClass(
                    cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ID)),
                    cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_TEXT)),
                    cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_HASH)),
                    cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_TIME))
                ))
            } while (cursor.moveToNext())
        }
        cursor.close()
        return shimList
    }

    fun searchShims(query: String): List<ShimClass> {
        val shimList = mutableListOf<ShimClass>()
        val db = this.readableDatabase
        val (sql, selectionArgs) = when {
            query.startsWith("#") -> {
                "SELECT * FROM $TABLE_SHIMS WHERE $COLUMN_HASH LIKE ? ORDER BY $COLUMN_ID DESC" to arrayOf("%${query.substring(1)}%")
            }
            query.startsWith("\"") && query.endsWith("\"") && query.length >= 2 -> {
                "SELECT * FROM $TABLE_SHIMS WHERE $COLUMN_TEXT LIKE ? ORDER BY $COLUMN_ID DESC" to arrayOf("%${query.substring(1, query.length - 1)}%")
            }
            else -> {
                "SELECT * FROM $TABLE_SHIMS WHERE $COLUMN_TEXT LIKE ? OR $COLUMN_HASH LIKE ? ORDER BY $COLUMN_ID DESC" to arrayOf("%$query%", "%$query%")
            }
        }
        
        val cursor = db.rawQuery(sql, selectionArgs)
        if (cursor.moveToFirst()) {
            do {
                shimList.add(ShimClass(
                    cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ID)),
                    cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_TEXT)),
                    cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_HASH)),
                    cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_TIME))
                ))
            } while (cursor.moveToNext())
        }
        cursor.close()
        return shimList
    }

    fun deleteShim(id: Int) {
        val db = this.writableDatabase
        db.delete(TABLE_SHIMS, "$COLUMN_ID = ?", arrayOf(id.toString()))
        db.close()
    }

    fun deleteAllShims() {
        val db = this.writableDatabase
        db.delete(TABLE_SHIMS, null, null)
        db.execSQL("DELETE FROM sqlite_sequence WHERE name = '$TABLE_SHIMS'")
        db.close()
    }

    fun importFile(context: Context, uri: Uri): Boolean {
        val temp = File(context.cacheDir, "import_temp")
        context.contentResolver.openInputStream(uri)?.use { input ->
            temp.outputStream().use { output -> input.copyTo(output) }
        } ?: return false
        val ok = runCatching {
            when {
                isSqlite(temp) -> { importDatabase(temp); true }
                isCsv(temp) -> { importCsv(temp.readText()); true }
                else -> false
            }
        }.getOrDefault(false)
        temp.delete()
        return ok
    }

    private fun isSqlite(file: File): Boolean {
        val head = ByteArray(SQLITE_MAGIC.size)
        file.inputStream().use { if (it.read(head) != head.size) return false }
        return head.contentEquals(SQLITE_MAGIC)
    }

    private fun isCsv(file: File): Boolean {
        val head = ByteArray(SNIFF_LEN)
        val read = file.inputStream().use { it.read(head) }
        if (read <= 0) return false
        val prefix = String(head, 0, read, Charsets.UTF_8)
        if (prefix.contains('\u0000')) return false 
        val first = prefix.trimStart().firstOrNull() ?: return false
        if (first == '[' || first == '{' || first == '<') return false 

        val rows = parseCsv(file.readText()).filter { row -> row.any { it.isNotBlank() } }
        if (rows.isEmpty()) return false
        val width = rows.first().size
        return width in 3..4 && rows.all { it.size == width }
    }

    private fun importDatabase(file: File) {
        SQLiteDatabase.openDatabase(file.absolutePath, null, SQLiteDatabase.OPEN_READONLY)
            .use { copyRows(it, this.writableDatabase) }
    }

    private fun importCsv(text: String) {
        val rows = parseCsv(text).filter { row -> row.any { it.isNotBlank() } }
        if (rows.isEmpty()) return

        val header = rows.first().map { it.trim().lowercase() }
        val hasHeader = header.contains(COLUMN_TEXT)
        val offset = if (rows.first().size >= 4) 1 else 0
        val idxText = if (hasHeader) header.indexOf(COLUMN_TEXT) else offset
        val idxHash = if (hasHeader) header.indexOf(COLUMN_HASH) else offset + 1
        val idxTime = if (hasHeader) header.indexOf(COLUMN_TIME) else offset + 2
        val body = if (hasHeader) rows.drop(1) else rows

        this.writableDatabase.transaction {
            body.forEach { row ->
                val shimText = row.getOrNull(idxText).orEmpty()
                if (shimText.isBlank()) return@forEach
                val values = ContentValues().apply {
                    put(COLUMN_TEXT, shimText)
                    put(COLUMN_HASH, row.getOrNull(idxHash).orEmpty())
                    put(COLUMN_TIME, row.getOrNull(idxTime)?.takeIf { it.isNotBlank() }
                        ?: ShimOTCClock.getCurrentOTCTime())
                }
                insert(TABLE_SHIMS, null, values)
            }
        }
    }

    private fun parseCsv(text: String): List<List<String>> {
        val rows = mutableListOf<List<String>>()
        var row = mutableListOf<String>()
        val field = StringBuilder()
        var quoted = false
        var i = 0
        while (i < text.length) {
            val c = text[i]
            when {
                quoted && c == '"' && i + 1 < text.length && text[i + 1] == '"' -> {
                    field.append('"'); i++
                }
                c == '"' -> quoted = !quoted
                !quoted && c == ',' -> {
                    row.add(field.toString()); field.setLength(0)
                }
                !quoted && (c == '\n' || c == '\r') -> {
                    if (c == '\r' && i + 1 < text.length && text[i + 1] == '\n') i++
                    row.add(field.toString()); field.setLength(0)
                    rows.add(row); row = mutableListOf()
                }
                else -> field.append(c)
            }
            i++
        }
        if (field.isNotEmpty() || row.isNotEmpty()) {
            row.add(field.toString()); rows.add(row)
        }
        return rows
    }

    fun restoreFrom(file: File): Boolean {
        val src = SQLiteDatabase.openDatabase(file.absolutePath, null, SQLiteDatabase.OPEN_READONLY)
        return try {
            this.writableDatabase.transaction {
                delete(TABLE_SHIMS, null, null)
                execSQL("DELETE FROM sqlite_sequence WHERE name = '$TABLE_SHIMS'")
                copyRows(src, this)
            }
            true
        } catch (_: Exception) {
            false
        } finally {
            src.close()
        }
    }

    private fun copyRows(from: SQLiteDatabase, to: SQLiteDatabase) {
        from.rawQuery("SELECT * FROM $TABLE_SHIMS", null).use { c ->
            if (c.moveToFirst()) {
                do {
                    val values = ContentValues().apply {
                        put(COLUMN_TEXT, c.getString(c.getColumnIndexOrThrow(COLUMN_TEXT)))
                        put(COLUMN_HASH, c.getString(c.getColumnIndexOrThrow(COLUMN_HASH)))
                        put(COLUMN_TIME, c.getString(c.getColumnIndexOrThrow(COLUMN_TIME)))
                    }
                    to.insert(TABLE_SHIMS, null, values)
                } while (c.moveToNext())
            }
        }
    }

    fun exportDb(context: Context, uri: Uri, shims: List<ShimClass> = getAllShims()) {
        val temp = File(context.cacheDir, "export_temp.db")
        temp.delete()
        SQLiteDatabase.openOrCreateDatabase(temp, null).use { out ->
            out.execSQL(CREATE_TABLE)
            out.transaction {
                shims.forEach { shim ->
                    val values = ContentValues().apply {
                        put(COLUMN_ID, shim.id)
                        put(COLUMN_TEXT, shim.shimtext)
                        put(COLUMN_HASH, shim.shimhash)
                        put(COLUMN_TIME, shim.shimtime)
                    }
                    insert(TABLE_SHIMS, null, values)
                }
            }
        }
        context.contentResolver.openOutputStream(uri)?.use { output ->
            temp.inputStream().use { it.copyTo(output) }
        }
        temp.delete()
    }

    fun exportCsv(context: Context, uri: Uri, shims: List<ShimClass> = getAllShims()) {
        context.contentResolver.openOutputStream(uri)?.use { output ->
            output.bufferedWriter().use { writer ->
                writer.append("id,shimtext,shimhash,shimtime\n")
                shims.forEach { shim ->
                    writer.append(csvField(shim.id.toString()))
                    writer.append(",")
                    writer.append(csvField(shim.shimtext))
                    writer.append(",")
                    writer.append(csvField(shim.shimhash))
                    writer.append(",")
                    writer.append(csvField(shim.shimtime))
                    writer.append("\n")
                }
            }
        }
    }

    fun exportTxt(context: Context, uri: Uri, shims: List<ShimClass> = getAllShims()) {
        context.contentResolver.openOutputStream(uri)?.use { output ->
            output.bufferedWriter().use { writer ->
                shims.forEach { shim ->
                    val tags = shim.shimhash.split(" ").filter { it.isNotBlank() }
                    if (tags.isNotEmpty()) writer.append(tags.joinToString(" ") { "#$it" }).append("\n")
                    writer.append(shim.shimtext).append("\n")
                    writer.append(shim.shimtime).append("\n\n")
                }
            }
        }
    }

    fun exportJson(context: Context, uri: Uri, shims: List<ShimClass> = getAllShims()) {
        val array = JSONArray()
        shims.forEach { shim ->
            array.put(JSONObject().apply {
                put(COLUMN_ID, shim.id)
                put(COLUMN_TEXT, shim.shimtext)
                put(COLUMN_HASH, shim.shimhash)
                put(COLUMN_TIME, shim.shimtime)
            })
        }
        context.contentResolver.openOutputStream(uri)?.use { output ->
            output.bufferedWriter().use { it.write(array.toString(2)) }
        }
    }

    private fun csvField(value: String): String {
        val escaped = value.replace("\"", "\"\"")
        return "\"$escaped\""
    }
}