package space.anity

import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.*
import java.sql.*
import java.util.logging.*

class DatabaseController(dbFileLocation: String = "main.db") {
    val db: Database
    val LOG = Logger.getLogger(this.javaClass.name)

    /**
     * Database table for the file location indexing
     */
    object FileLocation : Table() {
        val id = integer("id").autoIncrement().primaryKey()
        val location = text("location")
    }

    /**
     * Database table to index the users with their regarding passwords
     */
    object UserData : Table() {
        // only for multiple users:
        // val id = integer("id").autoIncrement().primaryKey()
        val username = varchar("username", 24).primaryKey()  // remove .primaryKey(), if id column is used
        val password = varchar("password", 64)
        val role = varchar("role", 64).default("USER")
    }

    /**
     * Database table storing general data/states
     */
    object General : Table() {
        val initialUse = integer("initialUse").default(1).primaryKey() // boolean -> 0:1
    }

    init {
        // create connection
        this.db = Database.connect("jdbc:sqlite:$dbFileLocation", "org.sqlite.JDBC")
        TransactionManager.manager.defaultIsolationLevel = Connection.TRANSACTION_SERIALIZABLE

        // add tables
        transaction {
            SchemaUtils.createMissingTablesAndColumns(FileLocation, UserData, General)
        }
    }

    fun createUser(usernameString: String, passwordHash: String, roleString: String) {
        transaction {
            try {
                UserData.insert {
                    it[username] = usernameString
                    it[password] = passwordHash
                    it[role] = roleString
                }
            } catch (_: org.jetbrains.exposed.exceptions.ExposedSQLException) {
                LOG.warning("User already exists!")
            }

        }
    }

    fun getUser(usernameString: String): List<Pair<String, Roles>> {
        return transaction {
            return@transaction UserData.select { UserData.username eq usernameString }.map {
                it[UserData.username] to (if (it[UserData.role] == "ADMIN") Roles.ADMIN else Roles.USER)
            }
        }
    }

    /*
    fun selectUser(uname: String) :String {
        var pwd :String

        transaction {
            UserData.select{UserData.username eq uname}.forEach {
                pwd = it[UserData.password]
            }
        }

        // return pwd
    }

    */

    // TODO add functions for database usage
}