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

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
        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"
    }

    override fun onCreate(db: SQLiteDatabase) {
        val createTable = ("CREATE TABLE $TABLE_SHIMS (" +
                "$COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "$COLUMN_TEXT TEXT, " +
                "$COLUMN_HASH TEXT, " +
                "$COLUMN_TIME TEXT)")
        db.execSQL(createTable)
    }

    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 importDatabase(context: Context, uri: Uri) {
        val tempFile = File(context.cacheDir, "import_temp.db")
        context.contentResolver.openInputStream(uri)?.use { input ->
            tempFile.outputStream().use { output ->
                input.copyTo(output)
            }
        }

        val importDb = SQLiteDatabase.openDatabase(tempFile.absolutePath, null, SQLiteDatabase.OPEN_READONLY)
        copyRows(importDb, this.writableDatabase)
        importDb.close()
        tempFile.delete()
    }

    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 exportCsv(context: Context, uri: Uri) {
        val shims = 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")
                }
            }
        }
    }

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