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

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 = 5
        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 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)
        val cursor = importDb.rawQuery("SELECT * FROM $TABLE_SHIMS", null)
        if (cursor.moveToFirst()) {
            val mainDb = this.writableDatabase
            do {
                val values = ContentValues().apply {
                    put(COLUMN_TEXT, cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_TEXT)))
                    put(COLUMN_HASH, cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_HASH)))
                    put(COLUMN_TIME, cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_TIME)))
                }
                mainDb.insert(TABLE_SHIMS, null, values)
            } while (cursor.moveToNext())
        }
        cursor.close()
        importDb.close()
        tempFile.delete()
    }
}