package com.vgmlr.stave

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.net.Uri
import androidx.core.content.edit
import androidx.core.database.sqlite.transaction

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

    companion object {
        private const val DATABASE_NAME = "stave.db"
        private const val DATABASE_VERSION = 3

        const val TABLE_TRIPS = "trips"
        const val TRIP_ID = "id"
        const val TRIP_NAME = "tripname"
        const val TRIP_TIME = "triptime"

        const val TABLE_ENTRIES = "entries"
        const val ENTRY_ID = "id"
        const val ENTRY_TRIP = "tripid"
        const val ENTRY_COST = "cost"
        const val ENTRY_CATEGORY = "category"
        const val ENTRY_LOCATION = "location"
        const val ENTRY_LAT = "lat"
        const val ENTRY_LNG = "lng"
        const val ENTRY_TIME = "entrytime"
        const val ENTRY_NOTE = "note"

        private const val CREATE_TRIPS =
            "CREATE TABLE $TABLE_TRIPS (" +
                    "$TRIP_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    "$TRIP_NAME TEXT, " +
                    "$TRIP_TIME TEXT)"

        private const val CREATE_ENTRIES =
            "CREATE TABLE $TABLE_ENTRIES (" +
                    "$ENTRY_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    "$ENTRY_TRIP INTEGER, " +
                    "$ENTRY_COST REAL, " +
                    "$ENTRY_CATEGORY TEXT, " +
                    "$ENTRY_LOCATION TEXT, " +
                    "$ENTRY_LAT REAL, " +
                    "$ENTRY_LNG REAL, " +
                    "$ENTRY_TIME TEXT, " +
                    "$ENTRY_NOTE TEXT)"
        
        private const val TRIP_SELECT =
            "SELECT t.$TRIP_ID, t.$TRIP_NAME, t.$TRIP_TIME, " +
                    "IFNULL((SELECT SUM(e.$ENTRY_COST) FROM $TABLE_ENTRIES e " +
                    "WHERE e.$ENTRY_TRIP = t.$TRIP_ID), 0) AS total, " +
                    "IFNULL((SELECT e.$ENTRY_LOCATION FROM $TABLE_ENTRIES e " +
                    "WHERE e.$ENTRY_TRIP = t.$TRIP_ID AND e.$ENTRY_LOCATION <> '' " +
                    "ORDER BY e.$ENTRY_ID ASC LIMIT 1), '') AS startloc, " +
                    "IFNULL((SELECT e.$ENTRY_LOCATION FROM $TABLE_ENTRIES e " +
                    "WHERE e.$ENTRY_TRIP = t.$TRIP_ID AND e.$ENTRY_LOCATION <> '' " +
                    "ORDER BY e.$ENTRY_ID DESC LIMIT 1), '') AS lastloc " +
                    "FROM $TABLE_TRIPS t"
    }

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

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

    fun addTrip(name: String, time: String): Long {
        val values = ContentValues().apply {
            put(TRIP_NAME, name)
            put(TRIP_TIME, time)
        }
        return writableDatabase.insert(TABLE_TRIPS, null, values)
    }

    fun getTrip(id: Int): StaveTrip? {
        if (id <= 0) return null
        readableDatabase.rawQuery("$TRIP_SELECT WHERE t.$TRIP_ID = ?", arrayOf(id.toString()))
            .use { c -> return if (c.moveToFirst()) readTrip(c) else null }
    }

    fun getAllTrips(): List<StaveTrip> {
        val list = mutableListOf<StaveTrip>()
        readableDatabase.rawQuery("$TRIP_SELECT ORDER BY t.$TRIP_ID DESC", null).use { c ->
            while (c.moveToNext()) list.add(readTrip(c))
        }
        return list
    }

    fun renameTrip(id: Int, name: String) {
        val values = ContentValues().apply { put(TRIP_NAME, name) }
        writableDatabase.update(TABLE_TRIPS, values, "$TRIP_ID = ?", arrayOf(id.toString()))
    }

    fun deleteTrip(id: Int) {
        writableDatabase.transaction {
            delete(TABLE_ENTRIES, "$ENTRY_TRIP = ?", arrayOf(id.toString()))
            delete(TABLE_TRIPS, "$TRIP_ID = ?", arrayOf(id.toString()))
        }
    }

    private fun readTrip(c: Cursor) = StaveTrip(
        id = c.getInt(0),
        name = c.getString(1) ?: "",
        stavetime = c.getString(2) ?: "",
        total = c.getDouble(3),
        startLocation = c.getString(4) ?: "",
        lastLocation = c.getString(5) ?: ""
    )

    // entries

    fun addEntry(
        tripId: Int,
        cost: Double,
        category: String,
        location: String,
        lat: Double?,
        lng: Double?,
        time: String,
        note: String
    ): Long {
        val values = ContentValues().apply {
            put(ENTRY_TRIP, tripId)
            put(ENTRY_COST, cost)
            put(ENTRY_CATEGORY, category)
            put(ENTRY_LOCATION, location)
            put(ENTRY_LAT, lat)
            put(ENTRY_LNG, lng)
            put(ENTRY_TIME, time)
            put(ENTRY_NOTE, note)
        }
        return writableDatabase.insert(TABLE_ENTRIES, null, values)
    }

    fun getEntries(tripId: Int): List<StaveEntry> {
        val list = mutableListOf<StaveEntry>()
        if (tripId <= 0) return list
        readableDatabase.rawQuery(
            "SELECT * FROM $TABLE_ENTRIES WHERE $ENTRY_TRIP = ? ORDER BY $ENTRY_ID DESC",
            arrayOf(tripId.toString())
        ).use { c ->
            while (c.moveToNext()) {
                list.add(
                    StaveEntry(
                        c.getInt(c.getColumnIndexOrThrow(ENTRY_ID)),
                        c.getInt(c.getColumnIndexOrThrow(ENTRY_TRIP)),
                        c.getDouble(c.getColumnIndexOrThrow(ENTRY_COST)),
                        c.getString(c.getColumnIndexOrThrow(ENTRY_CATEGORY)) ?: "",
                        c.getString(c.getColumnIndexOrThrow(ENTRY_LOCATION)) ?: "",
                        c.getString(c.getColumnIndexOrThrow(ENTRY_TIME)) ?: "",
                        c.optDouble(ENTRY_LAT),
                        c.optDouble(ENTRY_LNG),
                        c.getString(c.getColumnIndexOrThrow(ENTRY_NOTE)) ?: ""
                    )
                )
            }
        }
        return list
    }

    // nullable REAL
    private fun Cursor.optDouble(column: String): Double? {
        val i = getColumnIndex(column)
        return if (i < 0 || isNull(i)) null else getDouble(i)
    }

    fun deleteEntry(id: Int) {
        writableDatabase.delete(TABLE_ENTRIES, "$ENTRY_ID = ?", arrayOf(id.toString()))
    }

    fun exportTxt(context: Context, uri: Uri, trip: StaveTrip, entries: List<StaveEntry>) {
        context.contentResolver.openOutputStream(uri)?.use { output ->
            output.bufferedWriter().use { it.write(StaveText.format(trip, entries)) }
        }
    }
}

object StaveSession {
    private const val KEY_TRIP = "current_trip"

    private fun prefs(context: Context) =
        context.getSharedPreferences("stave_prefs", Context.MODE_PRIVATE)

    fun currentTripId(context: Context): Int = prefs(context).getInt(KEY_TRIP, -1)

    fun setCurrentTripId(context: Context, id: Int) = prefs(context).edit { putInt(KEY_TRIP, id) }

    fun clear(context: Context) = prefs(context).edit { putInt(KEY_TRIP, -1) }
}