package net.taehui.twilight.system

import net.taehui.twilight.*
import net.taehui.twilight.BundleIO.BundleVariety
import net.taehui.twilight.Component
import net.taehui.twilight.qwilight.QwilightAvatar
import org.apache.commons.dbcp2.BasicDataSource
import org.apache.commons.io.FileUtils
import org.apache.commons.io.FilenameUtils
import java.io.IOException
import java.nio.file.Files
import java.nio.file.Path
import java.sql.*
import java.time.Instant
import java.time.LocalDate
import java.time.ZoneId
import java.time.format.DateTimeFormatter
import java.util.*
import java.util.concurrent.CompletableFuture
import java.util.stream.Stream
import kotlin.collections.ArrayList
import kotlin.math.pow

object DB : Logger {
    private val pool = BasicDataSource()
    private val yyyyMMFormat = DateTimeFormatter.ofPattern("yyyy-MM").withZone(ZoneId.systemDefault())
    private val yyyyMMDDFormat = DateTimeFormatter.ofPattern("yyyy-MM-dd").withZone(ZoneId.systemDefault())

    private fun setBaseDB() {
        pool.connection.use {
            it.createStatement().use { dbStatement ->
                dbStatement.executeUpdate(
                    """
                        CREATE TABLE IF NOT EXISTS tw_note (
                            Note_ID VARCHAR(139),
                            Note_ID_128 CHAR(32),
                            Note_ID_256 CHAR(64),
                            Note_Variety INTEGER,
                            Artist TEXT,
                            Title TEXT,
                            Genre TEXT,
                            Level_Text TEXT,
                            Level INTEGER,
                            Input_Mode INTEGER,
                            Total_Notes INTEGER,
                            Is_Salt BOOLEAN,
                            Ability_5K REAL,
                            Ability_7K REAL,
                            Ability_9K REAL,
                            Length REAL,
                            PRIMARY KEY (Note_ID),
                            KEY (Note_Variety),
                            FULLTEXT (Artist),
                            FULLTEXT (Title),
                            FULLTEXT (Genre),
                            KEY (Is_Salt),
                            KEY (Ability_5K),
                            KEY (Ability_7K),
                            KEY (Ability_9K)
                        ) ENGINE=InnoDB
                    """.trimIndent()
                )
                dbStatement.executeUpdate(
                    """
                        CREATE TABLE IF NOT EXISTS tw_comment (
                            Date DATETIME,
                            Note_ID VARCHAR(139),
                            Avatar VARCHAR(20),
                            Multiplier REAL,
                            Auto_Mode INTEGER,
                            Note_Salt_Mode INTEGER,
                            Audio_Multiplier REAL,
                            Faint_Note_Mode INTEGER,
                            Judgment_Mode INTEGER,
                            Hit_Points_Mode INTEGER,
                            Note_Mobility_Mode INTEGER,
                            Long_Note_Mode INTEGER,
                            Input_Favor_Mode INTEGER,
                            Note_Modify_Mode INTEGER,
                            Lowest_Judgment_Condition_Mode INTEGER,
                            Stand INTEGER,
                            Band INTEGER,
                            Is_P BOOLEAN,
                            Point REAL,
                            Salt INTEGER,
                            Comment_ID CHAR(128),
                            Is_Max BOOLEAN,
                            Is_Paused BOOLEAN,
                            Input_Flags INTEGER,
                            FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE,
                            KEY (Date),
                            KEY (Note_ID),
                            KEY (Avatar),
                            KEY (Judgment_Mode),
                            KEY (Hit_Points_Mode),
                            KEY (Input_Favor_Mode),
                            KEY (Stand),
                            KEY (Comment_ID),
                            KEY (Is_Max),
                            CHECK (Multiplier >= 0.0),
                            CHECK (Auto_Mode IN (0, 1)),
                            CHECK (Note_Salt_Mode IN (0, 1, 2, 4, 11, 13)),
                            CHECK (Audio_Multiplier >= 0.5 AND Audio_Multiplier <= 2.0),
                            CHECK (Faint_Note_Mode IN (0, 1, 2, 3)),
                            CHECK (Judgment_Mode IN (0, 1, 2, 3, 4)),
                            CHECK (Hit_Points_Mode IN (0, 1, 2, 3, 4, 5)),
                            CHECK (Note_Mobility_Mode IN (0, 1, 3, 4, 5)),
                            CHECK (Long_Note_Mode IN (0, 1)),
                            CHECK (Input_Favor_Mode IN (0, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16)),
                            CHECK (Note_Modify_Mode IN (0, 1)),
                            CHECK (Lowest_Judgment_Condition_Mode IN (0, 1)),
                            CHECK (Stand >= 0 AND Stand <= 1000000),
                            CHECK (Band >= 0),
                            CHECK (Point >= 0.0 AND Point <= 1.0),
                            CHECK (Input_Flags >= 0 AND Input_Flags <= 15)
                        ) ENGINE=InnoDB
                    """.trimIndent()
                )
                dbStatement.executeUpdate(
                    """
                        CREATE TABLE IF NOT EXISTS tw_bundle (
                            Avatar VARCHAR(20),
                            Date DATETIME,
                            Name VARCHAR(191),
                            Length LONG,
                            Competence INTEGER,
                            Variety INTEGER,
                            Etc TEXT,
                            PRIMARY KEY (Avatar, Name),
                            FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE,
                            CHECK (Competence IN (0, 1, 2)),
                            CHECK (Variety IN (0, 1, 2))
                        ) ENGINE=InnoDB
                    """.trimIndent()
                )
                dbStatement.executeUpdate(
                    """
                        CREATE TABLE IF NOT EXISTS tw_ubuntu (
                            Avatar VARCHAR(20),
                            Ubuntu VARCHAR(20),
                            PRIMARY KEY (Avatar, Ubuntu),
                            FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE,
                            FOREIGN KEY (Ubuntu) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE
                        ) ENGINE=InnoDB
                    """.trimIndent()
                )
                dbStatement.executeUpdate(
                    """
                        CREATE TABLE IF NOT EXISTS tw_avatar (
                            Avatar VARCHAR(20),
                            Silent_Site INTEGER,
                            Notify_Ubuntu INTEGER,
                            Default_Bundle_Competence INTEGER,
                            IO_Competence INTEGER,
                            Notify_Save_Bundle INTEGER,
                            PRIMARY KEY (Avatar),
                            FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE,
                            KEY (Notify_Ubuntu),
                            CHECK (Silent_Site IN (0, 1, 2, 3)),
                            CHECK (Notify_Ubuntu IN (0, 1)),
                            CHECK (Default_Bundle_Competence IN (0, 1, 2, 3)),
                            CHECK (IO_Competence IN (0, 1, 2, 3)),
                            CHECK (Notify_Save_Bundle IN (0, 1, 2, 3))
                        ) ENGINE=InnoDB
                    """.trimIndent()
                )
                dbStatement.executeUpdate(
                    """
                        CREATE TABLE IF NOT EXISTS tw_title (
                            Avatar VARCHAR(20),
                            Title_ID CHAR(36),
                            PRIMARY KEY (Avatar),
                            FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE
                        ) ENGINE=InnoDB
                    """.trimIndent()
                )
                dbStatement.executeUpdate(
                    """
                        CREATE TABLE IF NOT EXISTS tw_edge (
                            Avatar VARCHAR(20),
                            Edge_ID CHAR(36),
                            PRIMARY KEY (Avatar),
                            FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE,
                            KEY (Edge_ID)
                        ) ENGINE=InnoDB
                    """.trimIndent()
                )
                dbStatement.executeUpdate(
                    """
                        CREATE TABLE IF NOT EXISTS tw_site (
                            Site_ID CHAR(36),
                            Avatar_ID VARCHAR(20),
                            Avatar_Name VARCHAR(255),
                            Date LONG,
                            Site_Yell TEXT,
                            Platform_ID LONG,
                            KEY (Site_ID),
                            KEY (Platform_ID)
                        ) ENGINE=InnoDB
                    """.trimIndent()
                )
                dbStatement.executeUpdate(
                    """
                        CREATE TABLE IF NOT EXISTS tw_level (
                            Avatar VARCHAR(20),
                            Level_ID CHAR(36),
                            Date DATETIME,
                            PRIMARY KEY (Avatar, Level_ID),
                            FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE
                        ) ENGINE=InnoDB
                    """.trimIndent()
                )
                dbStatement.executeUpdate(
                    """
                        CREATE TABLE IF NOT EXISTS tw_commentary (
                            Note_ID VARCHAR(139),
                            Avatar VARCHAR(20),
                            Commentary TEXT,
                            PRIMARY KEY (Avatar, Note_ID),
                            FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE
                        ) ENGINE=InnoDB
                    """.trimIndent()
                )
                dbStatement.executeUpdate(
                    """
                        CREATE TABLE IF NOT EXISTS tw_translated (
                            Text VARCHAR(766),
                            Target_Language CHAR(2),
                            Translated_Text TEXT,
                            PRIMARY KEY (Text, Target_Language)
                        ) ENGINE=InnoDB
                    """.trimIndent()
                )
                dbStatement.executeUpdate(
                    """
                        CREATE TABLE IF NOT EXISTS tw_favor (
                            Note_ID VARCHAR(139),
                            Avatar VARCHAR(20),
                            Date DATETIME,
                            Favor BOOLEAN,
                            PRIMARY KEY (Avatar, Note_ID),
                            FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE
                        ) ENGINE=InnoDB
                    """.trimIndent()
                )
            }
        }
    }

    fun getSiteYells(siteID: String): Collection<SiteYell> {
        pool.connection.use {
            it.prepareStatement(
                """
                    SELECT Avatar_ID, Avatar_Name, Date, Site_Yell, Platform_ID
                    FROM tw_site
                    WHERE Site_ID = ?
                    ORDER BY Date
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setString(1, siteID)
                dbStatement.executeQuery().use { rows ->
                    val data = LinkedList<SiteYell>()
                    var siteYellID = -1
                    while (rows.next()) {
                        data.add(
                            SiteYell(
                                rows.getString("Avatar_ID"),
                                rows.getString("Avatar_Name"),
                                rows.getLong("Date"),
                                rows.getString("Site_Yell"),
                                ++siteYellID,
                                rows.getLong("Platform_ID")
                            )
                        )
                    }
                    return data
                }
            }
        }
    }

    fun loadDB() {
        val db = Configure.db
        pool.url = "jdbc:mariadb://" + db.remote + "/" + db.db
        pool.username = db.avatar
        pool.password = db.auth
        setBaseDB()
        logInfo("Loaded DB")
    }

    fun setFavor(qwilightSetFavor: JSON.QwilightSetFavor, avatarID: String): CompletableFuture<Void> {
        return logFuture {
            pool.connection.use {
                val noteID = qwilightSetFavor.noteID
                val favor = qwilightSetFavor.favor
                if (favor != null) {
                    it.prepareStatement(
                        """
                            REPLACE INTO tw_favor
                            VALUES(?, ?, ?, ?)
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.setString(1, noteID)
                        dbStatement.setString(2, avatarID)
                        dbStatement.setTimestamp(3, Timestamp(System.currentTimeMillis()))
                        dbStatement.setBoolean(4, favor)
                        dbStatement.execute()
                    }
                } else {
                    it.prepareStatement(
                        """
                            DELETE
                            FROM tw_favor
                            WHERE Note_ID = ? AND Avatar = ?
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.setString(1, noteID)
                        dbStatement.setString(2, avatarID)
                        dbStatement.execute()
                    }
                }
            }
        }
    }

    fun loadTranslatedText(text: String, targetLanguage: String): CompletableFuture<String> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Translated_Text
                        FROM tw_translated
                        WHERE Text = ? AND Target_Language = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, text)
                    dbStatement.setString(2, targetLanguage)
                    dbStatement.executeQuery().use { rows ->
                        if (rows.next()) rows.getString("Translated_Text") else ""
                    }
                }
            }
        }
    }

    fun saveTranslatedText(text: String, targetLanguage: String, translatedText: String): CompletableFuture<Void> {
        return logFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        REPLACE INTO tw_translated
                        VALUES(?, ?, ?)
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, text)
                    dbStatement.setString(2, targetLanguage)
                    dbStatement.setString(3, translatedText)
                    dbStatement.execute()
                }
            }
        }
    }

    fun getQwilightNames(): List<String> {
        pool.connection.use {
            it.prepareStatement(
                """
                    SELECT Avatar_Name
                    FROM tn_avatar
                    ORDER BY Last_Date
                    LIMIT 100
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.executeQuery().use { rows ->
                    val data = mutableListOf<String>()
                    while (rows.next()) {
                        data.add(rows.getString("Avatar_Name"))
                    }
                    return data
                }
            }
        }
    }

    fun wipeNote(noteID: String, logger: Logger): CompletableFuture<Void> {
        return logFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        DELETE
                        FROM tw_note
                        WHERE Note_ID = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, noteID)
                    logger.logInfo("Wiped ${dbStatement.executeUpdate()} Notes")
                }
                it.prepareStatement(
                    """
                        SELECT Note_ID
                        FROM tw_note
                        WHERE Note_ID LIKE ?
                    """.trimIndent()
                ).use { dbStatement ->
                    val noteID512 = Utility.getNoteID512(noteID)
                    dbStatement.setString(1, "$noteID512:%")
                    dbStatement.executeQuery().use { rows ->
                        if (!rows.next() && Files.deleteIfExists(TwilightComponent.NOTE_ENTRY_PATH.resolve(noteID512))) {
                            logger.logInfo("Wiped Note File")
                        } else {
                            logger.logInfo("Wiped 0 Note Files")
                        }
                    }
                }
                it.prepareStatement(
                    """
                        DELETE
                        FROM tw_comment
                        WHERE Note_ID = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, noteID)
                    logger.logInfo("Wiped ${dbStatement.executeUpdate()} Comments")
                }
                it.prepareStatement(
                    """
                        SELECT Comment_ID
                        FROM tw_comment
                        WHERE Note_ID = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, noteID)
                    dbStatement.executeQuery().use { rows ->
                        var wipedCount = 0
                        while (rows.next()) {
                            if (Files.deleteIfExists(
                                    TwilightComponent.COMMENT_ENTRY_PATH.resolve(Utility.getNoteID512(noteID))
                                        .resolve("${rows.getString("Comment_ID")}.xz")
                                )
                            ) {
                                ++wipedCount
                            }
                        }
                        logger.logInfo("Wiped $wipedCount Comment Files")
                    }
                }
            }
        }
    }

    fun wipeNotes() {
        pool.connection.use {
            it.prepareStatement(
                """
                    DELETE
                    FROM tw_note
                """.trimIndent()
            ).use { dbStatement -> dbStatement.execute() }
        }
    }

    fun getWipeNotes(): CompletableFuture<Collection<String>> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT tw_note.Note_ID
                        FROM tw_note LEFT OUTER JOIN tw_comment
                        ON tw_note.Note_ID = tw_comment.Note_ID
                        WHERE tw_comment.Note_ID IS NULL
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.executeQuery().use { rows ->
                        val data = mutableSetOf<String>()
                        while (rows.next()) {
                            val noteID = rows.getString("Note_ID")
                            if (!LevelSystem.levelNoteIDs.contains(noteID)) {
                                data.add(noteID)
                            }
                        }
                        data
                    }
                }
            }
        }
    }

    fun pmsNote(noteID: String, logger: Logger): CompletableFuture<Void> {
        return logFuture {
            pool.connection.use {
                it.autoCommit = false
                val pmsNoteID = "${Utility.getNoteID512(noteID)}:1"
                it.prepareStatement(
                    """
                        DELETE
                        FROM tw_note
                        WHERE Note_ID = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, noteID)
                    if (dbStatement.executeUpdate() > 0) {
                        logger.logInfo("Wiped Note")
                    }
                }
                it.prepareStatement(
                    """
                        UPDATE tw_comment
                        SET Note_ID = ?
                        WHERE Note_ID = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, pmsNoteID)
                    dbStatement.setString(2, noteID)
                    logger.logInfo("PMSed ${dbStatement.executeUpdate()} Comments")
                }
                it.commit()
            }
        }
    }

    fun getPMSNotes(): CompletableFuture<Collection<String>> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Note_ID, Title
                        FROM tw_note
                        WHERE Input_Mode = 12
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.executeQuery().use { rows ->
                        val data = mutableSetOf<String>()
                        while (rows.next()) {
                            val title = rows.getString("Title")
                            if (title.contains("9B", true) || title.contains("9K", true) || title.contains(
                                    "PMS",
                                    true
                                )
                            ) {
                                data.add(rows.getString("Note_ID"))
                            }
                        }
                        data
                    }
                }
            }
        }
    }

    fun getWipeComments(): CompletableFuture<Collection<Path>> {
        return logValueFuture {
            Files.newDirectoryStream(TwilightComponent.COMMENT_ENTRY_PATH).use { commentEntryPaths ->
                commentEntryPaths.map { commentEntryPath ->
                    Files.list(commentEntryPath).use { commentFilePaths ->
                        commentFilePaths.map { commentFilePath ->
                            pool.connection.use {
                                it.prepareStatement(
                                    """
                                        SELECT Comment_ID
                                        FROM tw_comment
                                        WHERE Comment_ID = ?
                                     """.trimIndent()
                                ).use { dbStatement ->
                                    dbStatement.setString(
                                        1, FilenameUtils.removeExtension(commentFilePath.fileName.toString())
                                    )
                                    dbStatement.executeQuery().use { rows ->
                                        if (rows.next()) {
                                            null
                                        } else {
                                            commentFilePath
                                        }
                                    }
                                }
                            }
                        }.toList()
                    }
                }.flatMap { it.toList() }.filterNotNull()
            }
        }
    }

    fun wipeComments(commentFilePaths: Collection<Path>): CompletableFuture<Int> {
        return logValueFuture {
            commentFilePaths.count {
                try {
                    Files.deleteIfExists(it)
                } catch (e: IOException) {
                    logFault(e)
                    false
                }
            }
        }
    }

    fun getWipeBundles(): CompletableFuture<Collection<Path>> {
        return logValueFuture {
            Files.newDirectoryStream(TwilightComponent.BUNDLE_ENTRY_PATH).use { bundleEntryPaths ->
                bundleEntryPaths.map { bundleEntryPath ->
                    Files.list(bundleEntryPath).use { bundleFilePaths ->
                        bundleFilePaths.map { bundleFilePath ->
                            pool.connection.use {
                                it.prepareStatement(
                                    """
                                        SELECT Avatar, Name
                                        FROM tw_bundle
                                        WHERE Name = ? AND Avatar = ?
                                     """.trimIndent()
                                ).use { dbStatement ->
                                    dbStatement.setString(
                                        1, FilenameUtils.removeExtension(bundleFilePath.fileName.toString())
                                    )
                                    dbStatement.setString(2, bundleEntryPath.fileName.toString())
                                    dbStatement.executeQuery().use { rows ->
                                        if (!rows.next()) {
                                            bundleFilePath
                                        } else {
                                            null
                                        }
                                    }
                                }
                            }
                        }.toList()
                    }
                }.flatMap { it.toList() }.filterNotNull()
            }
        }
    }

    fun wipeBundles(bundleFilePaths: Collection<Path>): CompletableFuture<Int> {
        return logValueFuture {
            bundleFilePaths.count {
                try {
                    Files.deleteIfExists(it)
                } catch (e: IOException) {
                    logFault(e)
                    false
                }
            }
        }
    }

    interface ICommentary {
        var commentary: String
    }

    fun getComment(
        noteID: String,
        avatarID: String,
        language: String,
        target: Boolean,
        logger: Logger
    ): CompletableFuture<Any> {
        val comments = mutableListOf<ICommentary>()
        return logValueFuture {
            val commentaryMap = mutableMapOf<String, String>()
            pool.connection.use { db ->
                db.prepareStatement(
                    """
                        SELECT Avatar, Commentary
                        FROM tw_commentary
                        WHERE Note_ID = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, noteID)
                    dbStatement.executeQuery().use { rows ->
                        while (rows.next()) {
                            commentaryMap[rows.getString("Avatar")] = rows.getString("Commentary")
                        }
                    }
                }
            }

            val favor = if (avatarID.isEmpty()) null else pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Favor
                        FROM tw_favor
                        WHERE Note_ID = ? AND Avatar = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, noteID)
                    dbStatement.setString(2, avatarID)
                    dbStatement.executeQuery().use { rows ->
                        if (rows.next()) rows.getBoolean("Favor") else null
                    }
                }
            }
            val ubuntu = if (avatarID.isEmpty() || !target) null else getUbuntu(avatarID).join().map { it[0] }
                .toMutableList().apply { add(avatarID) }

            var totalFavor = 0
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Favor
                        FROM tw_favor
                        WHERE Note_ID = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, noteID)
                    dbStatement.executeQuery().use { rows ->
                        while (rows.next()) {
                            totalFavor += if (rows.getBoolean("Favor")) 1 else -1
                        }
                    }
                }
            }
            totalFavor = 0.coerceAtLeast(totalFavor)

            pool.connection.use { db ->
                db.prepareStatement(
                    """
                        SELECT tw_comment.Date, Avatar_ID, Avatar_Name, Multiplier, Auto_Mode, Note_Salt_Mode, Audio_Multiplier, Faint_Note_Mode, Judgment_Mode, Hit_Points_Mode, Note_Mobility_Mode, Long_Note_Mode, Input_Favor_Mode, Note_Modify_Mode, Lowest_Judgment_Condition_Mode, Stand, Band, Is_P, Point, Salt, Comment_ID, Is_Paused, Input_Flags
                        FROM tn_avatar, tw_comment
                        WHERE tn_avatar.Avatar_ID = tw_comment.Avatar AND Note_ID = ? AND Is_Max = true
                        ORDER BY Stand DESC
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, noteID)
                    dbStatement.executeQuery().use { rows ->
                        while (rows.next()) {
                            if (ubuntu?.contains(rows.getString("Avatar_ID")) != false) {
                                comments.add(object : ICommentary {
                                    val avatarID = rows.getString("Avatar_ID")
                                    override var commentary = commentaryMap.getOrDefault(this.avatarID, "")
                                    val date = rows.getTimestamp("Date")
                                    val avatarName = rows.getString("Avatar_Name")
                                    val multiplier = rows.getDouble("Multiplier")
                                    val autoMode = rows.getInt("Auto_Mode")
                                    val noteSaltMode = rows.getInt("Note_Salt_Mode")
                                    val audioMultiplier = rows.getDouble("Audio_Multiplier")
                                    val faintNoteMode = rows.getInt("Faint_Note_Mode")
                                    val judgmentMode = rows.getInt("Judgment_Mode")
                                    val hitPointsMode = rows.getInt("Hit_Points_Mode")
                                    val noteMobilityMode = rows.getInt("Note_Mobility_Mode")
                                    val longNoteMode = rows.getInt("Long_Note_Mode")
                                    val inputFavorMode = rows.getInt("Input_Favor_Mode")
                                    val noteModifyMode = rows.getInt("Note_Modify_Mode")
                                    val lowestJudgmentConditionMode = rows.getInt("Lowest_Judgment_Condition_Mode")
                                    val stand = rows.getInt("Stand")
                                    val band = rows.getInt("Band")

                                    @JvmField
                                    val isP = rows.getBoolean("Is_P")
                                    val point = rows.getDouble("Point")
                                    val salt = rows.getInt("Salt")
                                    val commentID = rows.getString("Comment_ID") ?: ""

                                    @JvmField
                                    val isPaused = rows.getBoolean("Is_Paused")

                                    val inputFlags = rows.getInt("Input_Flags")
                                })
                            }
                        }

                        CompletableFuture.allOf(*comments.filter { it.commentary.isNotEmpty() }.map {
                            Translator.translate(
                                language,
                                it.commentary,
                                logger
                            ).thenApply { translatedText ->
                                it.commentary = translatedText
                            }
                        }.toTypedArray()).join()

                        object {
                            val favor = favor
                            val totalFavor = totalFavor
                            val comments = comments
                        }
                    }
                }
            }
        }
    }

    fun setAvatarTitle(avatarID: String, titleID: String): CompletableFuture<Void> {
        return logFuture {
            pool.connection.use { db ->
                if (titleID.isEmpty()) {
                    db.prepareStatement(
                        """
                            DELETE
                            FROM tw_title
                            WHERE Avatar = ?
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.setString(1, avatarID)
                        dbStatement.execute()
                    }
                } else {
                    if (TitleSystem.getTitleIDs(
                            getLevelIDs(avatarID), getNoteIDs(avatarID), getAvatarLevels(avatarID)[0]
                        ).stream().anyMatch { it == titleID }
                    ) {
                        db.prepareStatement(
                            """
                                REPLACE INTO tw_title
                                VALUES(?, ?)
                            """.trimIndent()
                        ).use { dbStatement ->
                            dbStatement.setString(1, avatarID)
                            dbStatement.setString(2, titleID)
                            dbStatement.execute()
                        }
                    }
                }
            }
        }
    }

    fun setCommentary(noteID: String, avatarID: String, commentary: String): CompletableFuture<Boolean> {
        return logValueFuture {
            pool.connection.use {
                val hasComment: Boolean
                it.prepareStatement(
                    """
                        SELECT Comment_ID
                        FROM tw_comment
                        WHERE Note_ID = ? AND Avatar = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, noteID)
                    dbStatement.setString(2, avatarID)
                    dbStatement.executeQuery().use { rows -> hasComment = rows.next() }
                }
                if (commentary.isBlank()) {
                    if (hasComment) {
                        it.prepareStatement(
                            """
                                DELETE
                                FROM tw_commentary
                                WHERE Note_ID = ? AND Avatar = ?
                            """.trimIndent()
                        ).use { dbStatement ->
                            dbStatement.setString(1, noteID)
                            dbStatement.setString(2, avatarID)
                            dbStatement.execute()
                        }
                    }
                    true
                } else {
                    if (hasComment) {
                        it.prepareStatement(
                            """
                                REPLACE INTO tw_commentary
                                VALUES(?, ?, ?)
                            """.trimIndent()
                        ).use { dbStatement ->
                            dbStatement.setString(1, noteID)
                            dbStatement.setString(2, avatarID)
                            dbStatement.setString(3, commentary)
                            dbStatement.execute()
                        }
                    }
                    hasComment
                }
            }
        }
    }

    fun getNotes(): CompletableFuture<Collection<String>> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Note_ID
                        FROM tw_note
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.executeQuery().use { rows ->
                        val data = mutableListOf<String>()
                        while (rows.next()) {
                            data.add(rows.getString("Note_ID"))
                        }
                        data
                    }
                }
            }
        }
    }

    fun setBundle(qwilightSetBundle: JSON.QwilightSetBundle, avatarID: String) {
        logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        UPDATE tw_bundle
                        SET Competence = ?
                        WHERE Name = ? AND Avatar = ? 
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setInt(1, qwilightSetBundle.bundleCompetence)
                    dbStatement.setString(2, qwilightSetBundle.bundleName)
                    dbStatement.setString(3, avatarID)
                    dbStatement.execute()
                }
            }
        }
    }

    fun getBundles(avatarID: String): CompletableFuture<Pair<Collection<Array<Any>>, Long>?> {
        return logValueFuture {
            pool.connection.use {
                val data = mutableListOf<Array<Any>>()
                it.prepareStatement(
                    """
                        SELECT *
                        FROM tw_bundle
                        WHERE Avatar = ?
                        ORDER BY Date DESC
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, avatarID)
                    dbStatement.executeQuery().use { rows ->
                        while (rows.next()) {
                            data.add(
                                arrayOf(
                                    rows.getTimestamp("Date").time,
                                    rows.getString("Name"),
                                    rows.getLong("Length"),
                                    rows.getInt("Competence"),
                                    rows.getInt("Variety")
                                )
                            )
                        }
                    }
                }
                it.prepareStatement(
                    """
                        SELECT SUM(Length) AS Length
                        FROM tw_bundle
                        WHERE Avatar = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, avatarID)
                    dbStatement.executeQuery().use { rows ->
                        if (rows.next()) {
                            Pair(data, rows.getLong("Length"))
                        } else {
                            null
                        }
                    }
                }
            }
        }
    }

    fun setConfigure(qwilightSetConfigure: JSON.QwilightSetConfigure, avatarID: String) {
        logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        REPLACE INTO tw_avatar
                        VALUES(?, ?, ?, ?, ?, ?)
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, avatarID)
                    dbStatement.setInt(2, qwilightSetConfigure.silentSiteCompetence)
                    dbStatement.setInt(3, qwilightSetConfigure.toNotifyUbuntuCompetence)
                    dbStatement.setInt(4, qwilightSetConfigure.defaultBundleCompetence)
                    dbStatement.setInt(5, qwilightSetConfigure.ioCompetence)
                    dbStatement.setInt(6, qwilightSetConfigure.toNotifySaveBundleCompetence)
                    dbStatement.execute()
                }
            }
        }
    }

    fun setLastDate(avatarID: String) {
        logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        UPDATE tn_avatar
                        SET Last_Date = ?
                        WHERE Avatar_ID = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setTimestamp(1, Timestamp(System.currentTimeMillis()))
                    dbStatement.setString(2, avatarID)
                    dbStatement.execute()
                }
            }
        }
    }

    fun wipeUbuntu(avatarID: String, ubuntuID: String): CompletableFuture<Void> {
        return logFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        DELETE
                        FROM tw_ubuntu
                        WHERE Avatar = ? AND Ubuntu = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, avatarID)
                    dbStatement.setString(2, ubuntuID)
                    dbStatement.execute()
                }
            }
        }
    }

    fun wipeBundle(avatarID: String, bundleName: String): CompletableFuture<Void> {
        return logFuture {
            var bundleVariety: BundleVariety? = null
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Variety
                        FROM tw_bundle
                        WHERE Name = ? AND Avatar = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, avatarID)
                    dbStatement.setString(2, bundleName)
                    dbStatement.executeQuery().use { rows ->
                        if (rows.next()) {
                            bundleVariety = BundleVariety.getBundleVariety(rows.getInt("Variety"))
                        }
                    }
                }
            }
            bundleVariety?.let {
                pool.connection.use { db ->
                    db.prepareStatement(
                        """
                            DELETE
                            FROM tw_bundle
                            WHERE Name = ? AND Avatar = ? 
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.setString(1, avatarID)
                        dbStatement.setString(2, bundleName)
                        dbStatement.execute()
                    }
                }
                FileUtils.deleteQuietly(
                    TwilightComponent.BUNDLE_ENTRY_PATH.resolve(avatarID).resolve(bundleName + it.fileVariety)
                        .toFile()
                )
            }
        }
    }

    fun silentSite(siteID: String): CompletableFuture<Boolean> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        DELETE
                        FROM tw_site
                        WHERE Site_ID = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, siteID)
                    dbStatement.execute()
                }
            }
        }
    }

    fun getUbuntu(avatarID: String): CompletableFuture<Collection<Array<String>>> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Avatar_ID, Avatar_Name
                        FROM tw_ubuntu, tn_avatar
                        WHERE Avatar = ? AND Ubuntu = Avatar_ID 
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, avatarID)
                    dbStatement.executeQuery().use { rows ->
                        val data = mutableListOf<Array<String>>()
                        while (rows.next()) {
                            data.add(arrayOf(rows.getString("Avatar_ID"), rows.getString("Avatar_Name")))
                        }
                        data
                    }
                }
            }
        }
    }

    fun getAvatarName(avatarID: String): String {
        pool.connection.use {
            it.prepareStatement(
                """
                    SELECT Avatar_Name
                    FROM tn_avatar
                    WHERE Avatar_ID = ?
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setString(1, avatarID)
                dbStatement.executeQuery().use { rows ->
                    return if (rows.next()) rows.getString("Avatar_Name") else ""
                }
            }
        }
    }

    fun getClearedLevelIDs(avatarID: String): CompletableFuture<Collection<String>> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Level_ID
                        FROM tw_level
                        WHERE Avatar = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, avatarID)
                    dbStatement.executeQuery().use { rows ->
                        val data = mutableListOf<String>()
                        while (rows.next()) {
                            data.add(rows.getString("Level_ID"))
                        }
                        data
                    }
                }
            }
        }
    }

    fun setWwwLevel(avatarID: String, levelID: String) {
        pool.connection.use {
            it.prepareStatement(
                """
                    REPLACE INTO tw_level
                    VALUES (?, ?, ?)
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setString(1, avatarID)
                dbStatement.setString(2, levelID)
                dbStatement.setTimestamp(3, Timestamp(System.currentTimeMillis()))
                dbStatement.execute()
            }
        }
    }

    fun getSilentSiteCompetence(avatarID: String): CompletableFuture<Int> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Silent_Site
                        FROM tw_avatar
                        WHERE Avatar = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, avatarID)
                    dbStatement.executeQuery()
                        .use { rows ->
                            if (rows.next()) {
                                rows.getInt("Silent_Site")
                            } else {
                                QwilightAvatar.SILENT_SITE_CALLABLE
                            }
                        }
                }
            }
        }
    }

    fun getIOCompetence(avatarID: String): CompletableFuture<Int> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT IO_Competence
                        FROM tw_avatar
                        WHERE Avatar = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, avatarID)
                    dbStatement.executeQuery()
                        .use { rows ->
                            if (rows.next()) {
                                rows.getInt("IO_Competence")
                            } else {
                                QwilightAvatar.IO_CALLABLE
                            }
                        }
                }
            }
        }
    }

    fun getNotifySaveBundle(avatarID: String): Int {
        pool.connection.use {
            it.prepareStatement(
                """
                    SELECT Notify_Save_Bundle
                    FROM tw_avatar
                    WHERE Avatar = ?
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setString(1, avatarID)
                dbStatement.executeQuery()
                    .use { rows -> return if (rows.next()) rows.getInt("Notify_Save_Bundle") else QwilightAvatar.NOTIFY_SAVE_BUNDLE_CALLABLE }
            }
        }
    }

    fun getConfigure(avatarID: String): CompletableFuture<IntArray> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Silent_Site, Notify_Ubuntu, Default_Bundle_Competence, IO_Competence, Notify_Save_Bundle
                        FROM tw_avatar
                        WHERE Avatar = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, avatarID)
                    dbStatement.executeQuery().use { rows ->
                        if (rows.next()) {
                            intArrayOf(
                                rows.getInt("Silent_Site"),
                                rows.getInt("Notify_Ubuntu"),
                                rows.getInt("Default_Bundle_Competence"),
                                rows.getInt("IO_Competence"),
                                rows.getInt("Notify_Save_Bundle")
                            )
                        } else {
                            intArrayOf(
                                QwilightAvatar.SILENT_SITE_CALLABLE,
                                QwilightAvatar.NOTIFY_UBUNTU,
                                QwilightAvatar.BUNDLE_VOID,
                                QwilightAvatar.IO_CALLABLE,
                                QwilightAvatar.NOTIFY_SAVE_BUNDLE_CALLABLE
                            )
                        }
                    }
                }
            }
        }
    }

    fun saveUbuntu(avatarID: String, ubuntuID: String): CompletableFuture<Boolean> {
        return logValueFuture {
            try {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            INSERT INTO tw_ubuntu
                            VALUES(?, ?)
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.setString(1, Utility.getDefaultAvatarID(avatarID))
                        dbStatement.setString(2, Utility.getDefaultAvatarID(ubuntuID))
                        dbStatement.execute()
                        true
                    }
                }
            } catch (e: SQLIntegrityConstraintViolationException) {
                false
            }
        }
    }

    fun saveBundle(
        avatarID: String, bundleName: String, bundleLength: Long, bundleVariety: BundleVariety, etc: String
    ) {
        pool.connection.use {
            it.prepareStatement(
                """
                    INSERT INTO tw_bundle
                    VALUES (?, ?, ?, ?, ?, ?, ?)
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setString(1, avatarID)
                dbStatement.setTimestamp(2, Timestamp(System.currentTimeMillis()))
                dbStatement.setString(3, bundleName)
                dbStatement.setLong(4, bundleLength)
                dbStatement.setInt(
                    5,
                    if (bundleVariety == BundleVariety.QWILIGHT) QwilightAvatar.BUNDLE_VOID else getDefaultBundleCompetence(
                        avatarID
                    )
                )
                dbStatement.setInt(6, bundleVariety.value)
                dbStatement.setString(7, etc)
                dbStatement.execute()
            }
        }
    }

    private fun getDefaultBundleCompetence(avatarID: String): Int {
        pool.connection.use {
            it.prepareStatement(
                """
                    SELECT Default_Bundle_Competence
                    FROM tw_avatar
                    WHERE Avatar = ?
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setString(1, avatarID)
                dbStatement.executeQuery()
                    .use { rows -> return if (rows.next()) rows.getInt("Default_Bundle_Competence") else QwilightAvatar.BUNDLE_VOID }
            }
        }
    }

    fun isBundleLengthAvailable(avatarID: String, bundleLength: Long): Boolean {
        return pool.connection.use {
            it.prepareStatement(
                """
                    SELECT SUM(Length) AS Length
                    FROM tw_bundle
                    WHERE Avatar = ?
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setString(1, avatarID)
                dbStatement.executeQuery()
                    .use { rows -> rows.next() && bundleLength + rows.getLong("Length") < TwilightComponent.BUNDLE_LENGTH }
            }
        }
    }

    fun hasBundleBefore(avatarID: String, bundleName: String): CompletableFuture<Boolean> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT *
                        FROM tw_bundle
                        WHERE Name = ? AND Avatar = ? 
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, avatarID)
                    dbStatement.setString(2, bundleName)
                    dbStatement.executeQuery().use { rows ->
                        rows.next()
                    }
                }
            }
        }
    }

    fun getLastDate(avatarID: String): Long {
        pool.connection.use { dbComponent ->
            dbComponent.prepareStatement(
                """
                    SELECT Last_Date
                    FROM tn_avatar
                    WHERE Avatar_ID = ?
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setString(1, avatarID)
                dbStatement.executeQuery().use {
                    return (if (it.next()) it.getTimestamp("Last_Date")
                        .toInstant() else Instant.now()).toEpochMilli()
                }
            }
        }
    }

    fun getBundle(avatarID: String, bundleName: String): CompletableFuture<Array<Any>?> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Competence, Variety, Etc, Name
                        FROM tw_bundle
                        WHERE Name = ? AND Avatar = ? 
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, bundleName)
                    dbStatement.setString(2, avatarID)
                    dbStatement.executeQuery().use { rows ->
                        if (rows.next()) {
                            arrayOf(
                                rows.getInt("Competence"),
                                rows.getInt("Variety"),
                                rows.getString("Etc"),
                                rows.getString("Name")
                            )
                        } else {
                            null
                        }
                    }
                }
            }
        }
    }

    fun setNote(noteID: String, noteID128: String, noteID256: String, targetComputing: Computing) {
        pool.connection.use {
            it.prepareStatement(
                """
                    REPLACE INTO tw_note
                    VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """.trimIndent()
            ).use { dbStatement ->
                val isSalt = targetComputing.isSalt
                dbStatement.setString(1, noteID)
                dbStatement.setString(2, noteID128)
                dbStatement.setString(3, noteID256)
                dbStatement.setInt(4, targetComputing.noteVariety.value)
                dbStatement.setString(5, targetComputing.artist)
                dbStatement.setString(6, targetComputing.title)
                dbStatement.setString(7, targetComputing.genre)
                dbStatement.setString(8, targetComputing.levelText)
                dbStatement.setInt(9, targetComputing.level)
                dbStatement.setInt(10, targetComputing.inputMode.value)
                dbStatement.setInt(11, targetComputing.totalNotes)
                dbStatement.setBoolean(12, isSalt)
                dbStatement.setDouble(
                    13,
                    if (isSalt) 0.0 else AbilitySystem.getAbility(
                        AbilityClassSystem.AbilityClassVariety.ABILITY_CLASS_5K,
                        noteID128,
                        noteID256
                    )
                )
                dbStatement.setDouble(
                    14,
                    if (isSalt) 0.0 else AbilitySystem.getAbility(
                        AbilityClassSystem.AbilityClassVariety.ABILITY_CLASS_7K,
                        noteID128,
                        noteID256
                    )
                )
                dbStatement.setDouble(
                    15,
                    if (isSalt) 0.0 else AbilitySystem.getAbility(
                        AbilityClassSystem.AbilityClassVariety.ABILITY_CLASS_9K,
                        noteID128,
                        noteID256
                    )
                )
                dbStatement.setDouble(16, targetComputing.length)
                dbStatement.execute()
            }
        }
    }

    fun learnAbility() {
        data class NoteID(val noteID: String, val noteID128: String, val noteID256: String, val isSalt: Boolean)

        val noteIDs = mutableListOf<NoteID>()
        pool.connection.use {
            it.prepareStatement(
                """
                    SELECT Note_ID, Note_ID_128, Note_ID_256, Is_Salt
                    FROM tw_note
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.executeQuery().use { rows ->
                    while (rows.next()) {
                        noteIDs.add(
                            NoteID(
                                rows.getString("Note_ID"),
                                rows.getString("Note_ID_128"),
                                rows.getString("Note_ID_256"),
                                rows.getBoolean("Is_Salt")
                            )
                        )
                    }
                }
            }

            noteIDs.parallelStream().use { parallel ->
                parallel.forEach { (noteID, noteID128, noteID256, isSalt) ->
                    it.prepareStatement(
                        """
                            UPDATE tw_note
                            SET Ability_5K = ?, Ability_7K = ?, Ability_9K = ?
                            WHERE Note_ID = ?
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.setDouble(
                            1,
                            if (isSalt) 0.0 else AbilitySystem.getAbility(
                                AbilityClassSystem.AbilityClassVariety.ABILITY_CLASS_5K,
                                noteID128,
                                noteID256
                            )
                        )
                        dbStatement.setDouble(
                            2,
                            if (isSalt) 0.0 else AbilitySystem.getAbility(
                                AbilityClassSystem.AbilityClassVariety.ABILITY_CLASS_7K,
                                noteID128,
                                noteID256
                            )
                        )
                        dbStatement.setDouble(
                            3,
                            if (isSalt) 0.0 else AbilitySystem.getAbility(
                                AbilityClassSystem.AbilityClassVariety.ABILITY_CLASS_9K,
                                noteID128,
                                noteID256
                            )
                        )
                        dbStatement.setString(4, noteID)
                        dbStatement.execute()
                    }
                }
            }
        }
    }

    fun isCrossUbuntu(avatarID: String, ubuntuID: String): Boolean {
        pool.connection.use {
            it.prepareStatement(
                """
                    SELECT *
                    FROM tw_ubuntu
                    WHERE Avatar = ? AND Ubuntu = ?
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setString(1, ubuntuID)
                dbStatement.setString(2, avatarID)
                dbStatement.executeQuery().use { rows -> return rows.next() }
            }
        }
    }

    fun getNotifyUbuntu(avatarID: String): Iterable<String> {
        pool.connection.use {
            it.prepareStatement(
                """
                    SELECT Ubuntu, Notify_Ubuntu
                    FROM tw_ubuntu LEFT OUTER JOIN tw_avatar
                    ON Ubuntu = tw_avatar.Avatar
                    WHERE tw_ubuntu.Avatar = ? AND Notify_Ubuntu = 0
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setString(1, avatarID)
                dbStatement.executeQuery().use { rows ->
                    val data = mutableListOf<String>()
                    while (rows.next()) {
                        data.add(rows.getString("Ubuntu"))
                    }
                    return data
                }
            }
        }
    }

    fun saveSiteYell(siteID: String, siteYell: SiteYell): CompletableFuture<Void> {
        return logFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        INSERT INTO
                        tw_site
                        VALUES(?, ?, ?, ?, ?, ?)
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, siteID)
                    dbStatement.setString(2, siteYell.avatarID)
                    dbStatement.setString(3, siteYell.avatarName)
                    dbStatement.setLong(4, siteYell.date)
                    dbStatement.setString(5, siteYell.siteYell)
                    dbStatement.setLong(6, siteYell.platformID)
                    dbStatement.execute()
                }
            }
        }
    }

    fun doModifySiteYell(siteYell: SiteYell): CompletableFuture<Void> {
        return logFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        UPDATE tw_site
                        SET Site_Yell = ?
                        WHERE Platform_ID = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, siteYell.siteYell)
                    dbStatement.setLong(2, siteYell.platformID)
                    dbStatement.execute()
                }
            }
        }
    }

    fun wipeSiteYell(siteYell: SiteYell): CompletableFuture<Void> {
        return logFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        DELETE
                        FROM tw_site
                        WHERE Platform_ID = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setLong(1, siteYell.platformID)
                    dbStatement.execute()
                }
            }
        }
    }

    fun saveComment(
        multiplier: Double,
        autoMode: Int,
        noteSaltMode: Int,
        audioMultiplier: Double,
        faintNoteMode: Int,
        judgmentMode: Int,
        hitPointsMode: Int,
        noteMobilityMode: Int,
        longNoteMode: Int,
        inputFavorMode: Int,
        noteModifyMode: Int,
        lowestJudgmentConditionMode: Int,
        stand: Int,
        band: Int,
        isP: Boolean,
        point: Double,
        salt: Int,
        commentID: String,
        avatarID: String,
        noteID: String,
        isPaused: Boolean,
        inputFlags: Int
    ): Boolean {
        var isNewStand = true
        pool.connection.use {
            it.autoCommit = false
            it.prepareStatement(
                """
                    SELECT Stand
                    FROM tw_comment
                    WHERE Note_ID = ? AND Avatar = ? AND Is_Max = true
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setString(1, noteID)
                dbStatement.setString(2, avatarID)
                dbStatement.executeQuery().use { rows ->
                    if (rows.next() && rows.getInt("Stand") > stand) {
                        isNewStand = false
                    }
                }
            }
            if (isNewStand) {
                it.prepareStatement(
                    """
                        UPDATE tw_comment
                        SET Is_Max = false
                        WHERE Note_ID = ? AND Avatar = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, noteID)
                    dbStatement.setString(2, avatarID)
                    dbStatement.execute()
                }
            }
            it.prepareStatement(
                """
                    INSERT INTO tw_comment
                    VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setTimestamp(1, Timestamp(System.currentTimeMillis()))
                dbStatement.setString(2, noteID)
                dbStatement.setString(3, avatarID)
                dbStatement.setDouble(4, multiplier)
                dbStatement.setInt(5, autoMode)
                dbStatement.setInt(6, noteSaltMode)
                dbStatement.setDouble(7, audioMultiplier)
                dbStatement.setInt(8, faintNoteMode)
                dbStatement.setInt(9, judgmentMode)
                dbStatement.setInt(10, hitPointsMode)
                dbStatement.setInt(11, noteMobilityMode)
                dbStatement.setInt(12, longNoteMode)
                dbStatement.setInt(13, inputFavorMode)
                dbStatement.setInt(14, noteModifyMode)
                dbStatement.setInt(15, lowestJudgmentConditionMode)
                dbStatement.setInt(16, stand)
                dbStatement.setInt(17, band)
                dbStatement.setBoolean(18, isP)
                dbStatement.setDouble(19, point)
                dbStatement.setInt(20, salt)
                dbStatement.setString(21, commentID)
                dbStatement.setBoolean(22, isNewStand)
                dbStatement.setBoolean(23, isPaused)
                dbStatement.setInt(24, inputFlags)
                dbStatement.execute()
            }
            it.commit()
        }
        return isNewStand
    }

    fun getTitle(qm: Map<String, String>): CompletableFuture<JSON.TwilightWwwTitle?> {
        return logValueFuture {
            val avatarID = Utility.getDefaultAvatarID(qm.getOrDefault("avatarID", ""))
            val language = qm.getOrDefault("language", "en-US")
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Title_ID
                        FROM tw_title
                        WHERE Avatar = ?
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, avatarID)
                    dbStatement.executeQuery().use { rows ->
                        if (rows.next()) {
                            val titleID = rows.getString("Title_ID")
                            if (titleID != null) {
                                val wwwTitle = TitleSystem.getTitle(language, titleID)
                                if (wwwTitle != null) {
                                    JSON.TwilightWwwTitle(wwwTitle.title, wwwTitle.titleColor)
                                } else {
                                    null
                                }
                            } else {
                                null
                            }
                        } else {
                            null
                        }
                    }
                }
            }
        }
    }

    private fun getLevelIDs(avatarID: String): Collection<String> {
        val levelIDs = mutableListOf<String>()
        pool.connection.use {
            it.prepareStatement(
                """
                    SELECT Level_ID
                    FROM tw_level
                    WHERE Avatar = ?
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setString(1, avatarID)
                dbStatement.executeQuery().use { rows ->
                    while (rows.next()) {
                        levelIDs.add(rows.getString("Level_ID"))
                    }
                }
            }
        }
        return levelIDs
    }

    private fun getNoteIDs(avatarID: String): Collection<String> {
        val noteIDs = mutableListOf<String>()
        pool.connection.use {
            it.prepareStatement(
                """
                    SELECT DISTINCT Note_ID
                    FROM tw_comment
                    WHERE Avatar = ?
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setString(1, avatarID)
                dbStatement.executeQuery().use { rows ->
                    while (rows.next()) {
                        noteIDs.add(rows.getString("Note_ID"))
                    }
                }
            }
        }
        return noteIDs
    }

    fun getTitleItems(avatarID: String): Collection<TitleSystem.TitleItem> {
        return TitleSystem.getTitleItems(
            getLevelIDs(avatarID), getNoteIDs(avatarID), getAvatarLevels(avatarID)[0]
        )
    }

    fun getTitles(qm: Map<String, String>): CompletableFuture<Collection<Any>> {
        return logValueFuture {
            val avatarID = Utility.getDefaultAvatarID(qm.getOrDefault("avatarID", ""))
            TitleSystem.getTitles(
                getLevelIDs(avatarID),
                getNoteIDs(avatarID),
                getAvatarLevels(avatarID)[0],
                qm.getOrDefault("language", "en-US")
            )
        }
    }

    fun getEdgeIDs(qm: Map<String, String>): CompletableFuture<Collection<String>> {
        return logValueFuture {
            val avatarID = Utility.getDefaultAvatarID(qm.getOrDefault("avatarID", ""))
            EdgeSystem.getEdgeIDs(
                getLevelIDs(avatarID), getNoteIDs(avatarID), getAvatarLevels(avatarID)[0]
            )
        }
    }

    fun setAvatarEdge(avatarID: String, edgeID: String): CompletableFuture<Void> {
        return logFuture {
            pool.connection.use {
                if (
                    EdgeSystem.getEdgeIDs(
                        getLevelIDs(avatarID), getNoteIDs(avatarID), getAvatarLevels(avatarID)[0]
                    ).contains(edgeID)
                ) {
                    it.prepareStatement(
                        """
                            REPLACE INTO tw_edge
                            VALUES(?, ?)
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.setString(1, avatarID)
                        dbStatement.setString(2, edgeID)
                        dbStatement.execute()
                    }
                }
            }
        }
    }

    fun getAvatarEdge(avatarID: String): String {
        pool.connection.use {
            it.prepareStatement(
                """
                    SELECT Edge_ID
                    FROM tw_edge
                    WHERE Avatar = ?
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setString(1, avatarID)
                dbStatement.executeQuery()
                    .use { rows -> return if (rows.next()) rows.getString("Edge_ID") else "Default" }
            }
        }
    }

    fun getNote(qm: Map<String, String>): CompletableFuture<Any> {
        return logValueFuture {
            val noteVariety = (qm["noteVariety"] ?: "0").toInt()
            val want = qm["want"] ?: ""
            val src = qm["src"] ?: "0"
            val fit = qm["fit"] ?: "0"
            val page = (qm["page"] ?: "1").toInt()
            val viewUnit = (qm["viewUnit"] ?: "15").toInt()

            var totalCount = 0
            var highestCount = 0
            var noteCount = 0
            val notes = mutableListOf<Any>()

            pool.connection.use { db ->
                val isNotWant = want.isEmpty()
                val noteVarietyStatement = if (noteVariety > 0) "Note_Variety = $noteVariety" else null
                val noteVarietyStatement0 = if (noteVarietyStatement != null) "WHERE $noteVarietyStatement" else " "
                val noteVarietyStatement1 = if (noteVarietyStatement != null) "AND $noteVarietyStatement" else " "
                if (isNotWant) {
                    db.prepareStatement(
                        """
                            SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Highest_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count
                            FROM tw_note, tw_comment
                            WHERE tw_note.Note_ID = tw_comment.Note_ID $noteVarietyStatement1
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            if (rows.next()) {
                                totalCount = rows.getInt("Total_Count")
                                highestCount = rows.getInt("Highest_Count")
                                noteCount = rows.getInt("Note_Count")
                            }
                        }
                    }
                } else {
                    db.prepareStatement(
                        when (src) {
                            "0" -> """
                                SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Highest_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count
                                FROM tw_note, tw_comment
                                WHERE tw_note.Note_ID = tw_comment.Note_ID AND MATCH(Title) AGAINST(? IN BOOLEAN MODE) $noteVarietyStatement1
                            """.trimIndent()

                            "1" -> """
                                SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Highest_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count
                                FROM tw_note, tw_comment, tn_avatar
                                WHERE tw_note.Note_ID = tw_comment.Note_ID AND Avatar = Avatar_ID AND MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE) $noteVarietyStatement1
                            """.trimIndent()

                            "2" -> """
                                SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Highest_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count
                                FROM tw_note, tw_comment
                                WHERE tw_note.Note_ID = tw_comment.Note_ID AND MATCH(Artist) AGAINST(? IN BOOLEAN MODE) $noteVarietyStatement1
                            """.trimIndent()

                            "3" -> """
                                SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Highest_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count
                                FROM tw_note, tw_comment
                                WHERE tw_note.Note_ID = tw_comment.Note_ID AND MATCH(Genre) AGAINST(? IN BOOLEAN MODE) $noteVarietyStatement1
                            """.trimIndent()

                            else -> null
                        }
                    ).use { dbStatement ->
                        if (dbStatement != null) {
                            dbStatement.setString(1, "*$want*")
                            dbStatement.executeQuery().use { rows ->
                                if (rows.next()) {
                                    totalCount = rows.getInt("Total_Count")
                                    highestCount = rows.getInt("Highest_Count")
                                    noteCount = rows.getInt("Note_Count")
                                }
                            }
                        }
                    }
                }
                val asStatement = """
                    DISTINCT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, Level, (
                        SELECT COUNT(DISTINCT Note_ID, Avatar)
                        FROM tw_comment
                        WHERE tw_note.Note_ID = tw_comment.Note_ID
                    ) AS Highest, (
                        SELECT COUNT(Comment_ID)
                        FROM tw_comment
                        WHERE tw_note.Note_ID = tw_comment.Note_ID
                    ) AS Total, (
                        SELECT MAX(Date)
                        FROM tw_comment
                        WHERE tw_note.Note_ID = tw_comment.Note_ID
                    ) AS Date
                """.trimIndent()
                val fitStatement = when (fit) {
                    "0" -> " ORDER BY Highest DESC "
                    "1" -> " ORDER BY Total DESC "
                    "2" -> " ORDER BY Date DESC "
                    "3" -> " ORDER BY Title "
                    "4" -> " ORDER BY Artist "
                    "5" -> " ORDER BY Genre "
                    "6" -> " ORDER BY Level_Text "
                    else -> " "
                }
                val dbStatement: PreparedStatement?
                if (isNotWant) {
                    dbStatement = db.prepareStatement(
                        "SELECT $asStatement FROM tw_note $noteVarietyStatement0 $fitStatement LIMIT ?, ?"
                    )
                    dbStatement.setInt(1, viewUnit * (page - 1))
                    dbStatement.setInt(2, viewUnit)
                } else {
                    dbStatement = when (src) {
                        "0" -> db.prepareStatement(
                            """
                                SELECT $asStatement
                                FROM tw_note
                                WHERE MATCH(Title) AGAINST(? IN BOOLEAN MODE) $noteVarietyStatement1 $fitStatement LIMIT ?, ?
                            """.trimIndent()
                        )

                        "1" -> db.prepareStatement(
                            """
                                SELECT $asStatement
                                FROM tw_note, tw_comment, tn_avatar
                                WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE) $noteVarietyStatement1 $fitStatement LIMIT ?, ?
                            """.trimIndent()
                        )

                        "2" -> db.prepareStatement(
                            """
                                SELECT $asStatement
                                FROM tw_note
                                WHERE MATCH(Artist) AGAINST(? IN BOOLEAN MODE) $noteVarietyStatement1 $fitStatement LIMIT ?, ?
                            """.trimIndent()
                        )

                        "3" -> db.prepareStatement(
                            """
                                SELECT $asStatement
                                FROM tw_note
                                WHERE MATCH(Genre) AGAINST(? IN BOOLEAN MODE) $noteVarietyStatement1 $fitStatement LIMIT ?, ?
                            """.trimIndent()
                        )

                        else -> null
                    }
                    dbStatement?.setString(1, "*$want*")
                    dbStatement?.setInt(2, viewUnit * (page - 1))
                    dbStatement?.setInt(3, viewUnit)
                }
                dbStatement?.use {
                    it.executeQuery().use { rows ->
                        while (rows.next()) {
                            notes.add(object {
                                val noteID = rows.getString("Note_ID")
                                val noteVariety = rows.getInt("Note_Variety")
                                val artist = rows.getString("Artist")
                                val title = rows.getString("Title")
                                val genre = rows.getString("Genre")
                                val levelText = rows.getString("Level_Text")
                                val level = rows.getInt("Level")
                                val highestCount = rows.getInt("Highest")
                                val totalCount = rows.getInt("Total")
                                val date = rows.getTimestamp("Date")
                            })
                        }
                    }
                }

                object {
                    val totalCount = totalCount
                    val highestCount = highestCount
                    val noteCount = noteCount
                    val notes = notes
                }
            }
        }
    }

    fun getLevels(levelGroup: LevelSystem.LevelGroup): CompletableFuture<Collection<Any>> {
        return logValueFuture {
            levelGroup.levelItems.mapNotNull { levelItem ->
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT COUNT(Avatar) AS Count
                            FROM tw_level
                            WHERE Level_ID = ?
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.setString(1, levelItem.levelID)
                        dbStatement.executeQuery().use { rows ->
                            if (rows.next()) {
                                object {
                                    val levelID = levelItem.levelID
                                    val title = levelItem.title
                                    val comment = levelItem.comment
                                    val levelText = levelItem.levelText
                                    val level = levelItem.level
                                    val avatars = rows.getInt("Count")
                                }
                            } else {
                                null
                            }
                        }
                    }
                }
            }
        }
    }

    fun getLevelNote(levelItem: LevelSystem.LevelGroup.LevelItem): CompletableFuture<Collection<Any>> {
        return logValueFuture {
            levelItem.noteID.split("/".toRegex()).map { noteID ->
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, Level
                            FROM tw_note
                            WHERE Note_ID = ?
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.setString(1, noteID)
                        dbStatement.executeQuery().use { rows ->
                            if (rows.next()) {
                                object {
                                    val noteID = rows.getString("Note_ID")
                                    val noteVariety = rows.getInt("Note_Variety")
                                    val artist = rows.getString("Artist")
                                    val title = rows.getString("Title")
                                    val genre = rows.getString("Genre")
                                    val levelText = rows.getString("Level_Text")
                                    val level = rows.getInt("Level")
                                }
                            } else {
                                object {
                                    val noteID = noteID
                                    val noteVariety = Component.NoteVariety.EVENT_NOTE
                                    val artist = null
                                    val title = null
                                    val genre = null
                                    val levelText = null
                                    val level = 0
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    fun getAvatarLevels(avatarID: String): IntArray {
        pool.connection.use {
            it.prepareStatement(
                """
                    SELECT SUM(Total_Notes * Stand / 1000000) AS Value
                    FROM tw_comment, tw_note
                    WHERE tw_comment.Note_ID = tw_note.Note_ID AND Avatar = ? AND Is_Salt = false
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setString(1, avatarID)
                dbStatement.executeQuery().use { rows ->
                    val value = if (rows.next()) rows.getDouble("Value") else 0.0
                    return intArrayOf(1 + (value / 10000).toInt(), (value % 10000).toInt(), 10000)
                }
            }
        }
    }

    fun getAvatar(qm: Map<String, String>): CompletableFuture<Any?> {
        return logValueFuture {
            val wantAvatarID = Utility.getDefaultAvatarID(qm.getOrDefault("avatarID", ""))
            val wantAvatarName = qm.getOrDefault("avatarName", "")

            var avatarID = ""
            var avatarName = ""
            var avatarIntro = ""
            var totalCount = 0
            var totalLength = 0.0
            var highestCount = 0
            var date = 0L
            var avatarLevels = intArrayOf()
            var avatarAbility5K = 0.0
            var avatarAbility5KClass = 0.0
            var avatarAbility5KPlace = 0
            var avatarAbility5KCount = 0
            var avatarAbility7K = 0.0
            var avatarAbility7KClass = 0.0
            var avatarAbility7KPlace = 0
            var avatarAbility7KCount = 0
            var avatarAbility9K = 0.0
            var avatarAbility9KClass = 0.0
            var avatarAbility9KPlace = 0
            var avatarAbility9KCount = 0
            val dateSet = mutableListOf<Long>()
            val dateValues = mutableListOf<Int>()
            val quitStatusValues = IntArray(7)

            val wantAvatars = mutableListOf<Any>()

            pool.connection.use { db ->
                if (wantAvatarID.isEmpty()) {
                    db.prepareStatement(
                        """
                            SELECT Avatar_ID, Avatar_Name, Avatar_Intro
                            FROM tn_avatar
                            WHERE MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE)
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.setString(1, "*$wantAvatarName*")
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                wantAvatars.add(object {
                                    val avatarID = rows.getString("Avatar_ID")
                                    val avatarName = rows.getString("Avatar_Name")
                                    val avatarIntro = rows.getString("Avatar_Intro")

                                    override fun toString(): String {
                                        return this.avatarID
                                    }
                                })
                            }
                            avatarID = wantAvatars.singleOrNull()?.toString() ?: ""
                        }
                    }
                } else {
                    db.prepareStatement(
                        """
                            SELECT Avatar_ID
                            FROM tn_avatar
                            WHERE Avatar_ID = ?
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.setString(1, wantAvatarID)
                        dbStatement.executeQuery().use { rows ->
                            if (rows.next()) {
                                avatarID = rows.getString("Avatar_ID")
                            }
                        }
                    }
                }
            }

            if (avatarID.isEmpty()) {
                if (wantAvatars.isEmpty()) {
                    null
                } else {
                    wantAvatars
                }
            } else {
                val futures = CompletableFuture.allOf(logValueFuture {
                    avatarLevels = getAvatarLevels(avatarID)
                }, logValueFuture {
                    pool.connection.use { db ->
                        db.prepareStatement(
                            """
                                SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT Note_ID) AS Highest_Count, Avatar_ID, Avatar_Name, Avatar_Intro, Last_Date
                                FROM tw_comment RIGHT OUTER JOIN tn_avatar
                                ON tw_comment.Avatar = tn_avatar.Avatar_ID
                                WHERE Avatar_ID = ?
                            """.trimIndent()
                        ).use { dbStatement ->
                            dbStatement.setString(1, avatarID)
                            dbStatement.executeQuery().use { rows ->
                                if (rows.next()) {
                                    totalCount = rows.getInt("Total_Count")
                                    highestCount = rows.getInt("Highest_Count")
                                    avatarName = rows.getString("Avatar_Name")
                                    avatarIntro = rows.getString("Avatar_Intro")
                                    date = rows.getTimestamp("Last_Date")?.toInstant()?.toEpochMilli() ?: 0
                                }
                            }
                        }
                    }
                }, logValueFuture {
                    pool.connection.use { db ->
                        db.prepareStatement(
                            """
                                SELECT SUM(Length) AS Total_Length
                                FROM tw_note, tw_comment, tn_avatar
                                WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Avatar_ID = ?
                            """.trimIndent()
                        ).use { dbStatement ->
                            dbStatement.setString(1, avatarID)
                            dbStatement.executeQuery().use { rows ->
                                if (rows.next()) {
                                    totalLength = rows.getDouble("Total_Length")
                                }
                            }
                        }
                    }
                }, logValueFuture {
                    pool.connection.use { db ->
                        val highestDateValue = System.currentTimeMillis()
                        for (i in 90 downTo 0) {
                            val yyyyMMDD = yyyyMMDDFormat.format(
                                Instant.ofEpochMilli(highestDateValue - 86400000L * i)
                                    .atZone(ZoneId.systemDefault())
                            )
                            dateSet.add(highestDateValue - 86400000L * i)
                            db.prepareStatement(
                                """
                                    SELECT COUNT(Comment_ID) AS Count
                                    FROM tw_comment
                                    WHERE Date BETWEEN "$yyyyMMDD 00:00:00" AND "$yyyyMMDD 23:59:59" AND Avatar = ?
                                """.trimIndent()
                            ).use { dbStatement ->
                                dbStatement.setString(1, avatarID)
                                dbStatement.executeQuery().use {
                                    if (it.next()) {
                                        dateValues.add(it.getInt("Count"))
                                    }
                                }
                            }
                        }
                    }
                }, logValueFuture {
                    pool.connection.use { db ->
                        db.prepareStatement(
                            """
                                SELECT Note_ID, Stand, Point
                                FROM tw_comment
                                WHERE Avatar = ? AND Is_Max = true
                            """.trimIndent()
                        ).use { dbStatement ->
                            dbStatement.setString(1, avatarID)
                            dbStatement.executeQuery().use {
                                while (it.next()) {
                                    ++quitStatusValues[Utility.getQuitStatusValue(
                                        it.getDouble("Point"), it.getInt("Stand")
                                    )]
                                }
                            }
                        }
                    }
                }, logValueFuture {
                    val rawAbilities5K = getAbilities5K()
                    val targetAbilities5K = getAbilities(rawAbilities5K).toList()
                    val validAbilities5K = targetAbilities5K.stream().filter { it.second.second }.toList()
                    val targetAbility5K =
                        targetAbilities5K.stream().filter { it.first.first == avatarID }.findAny().orElse(
                            Pair(Pair(avatarID, avatarName), Pair(0.0, false))
                        )
                    avatarAbility5K = targetAbility5K.second.first
                    avatarAbility5KPlace = validAbilities5K.indexOf(targetAbility5K)
                    avatarAbility5KCount = validAbilities5K.size
                    avatarAbility5KClass = if (avatarAbility5KPlace != -1) {
                        val absTargetAbilityPlace = 10.0
                        if (avatarAbility5KPlace < absTargetAbilityPlace) {
                            -(avatarAbility5KPlace + 1).toDouble()
                        } else {
                            (avatarAbility5KPlace - absTargetAbilityPlace) / (avatarAbility5KCount - absTargetAbilityPlace)
                        }
                    } else {
                        1.0
                    }
                }, logValueFuture {
                    val rawAbilities7K = getAbilities7K()
                    val targetAbilities7K = getAbilities(rawAbilities7K).toList()
                    val validAbilities7K = targetAbilities7K.stream().filter { it.second.second }.toList()
                    val targetAbility7K =
                        targetAbilities7K.stream().filter { it.first.first == avatarID }.findAny().orElse(
                            Pair(Pair(avatarID, avatarName), Pair(0.0, false))
                        )
                    avatarAbility7K = targetAbility7K.second.first
                    avatarAbility7KPlace = validAbilities7K.indexOf(targetAbility7K)
                    avatarAbility7KCount = validAbilities7K.size
                    avatarAbility7KClass = if (avatarAbility7KPlace != -1) {
                        val absTargetAbilityPlace = 10.0
                        if (avatarAbility7KPlace < absTargetAbilityPlace) {
                            -(avatarAbility7KPlace + 1).toDouble()
                        } else {
                            (avatarAbility7KPlace - absTargetAbilityPlace) / (avatarAbility7KCount - absTargetAbilityPlace)
                        }
                    } else {
                        1.0
                    }
                }, logValueFuture {
                    val rawAbilities9K = getAbilities9K()
                    val targetAbilities9K = getAbilities(rawAbilities9K).toList()
                    val validAbilities9K = targetAbilities9K.stream().filter { it.second.second }.toList()
                    val targetAbility9K =
                        targetAbilities9K.stream().filter { it.first.first == avatarID }.findAny().orElse(
                            Pair(Pair(avatarID, avatarName), Pair(0.0, false))
                        )
                    avatarAbility9K = targetAbility9K.second.first
                    avatarAbility9KPlace = validAbilities9K.indexOf(targetAbility9K)
                    avatarAbility9KCount = validAbilities9K.size
                    avatarAbility9KClass = if (avatarAbility9KPlace != -1) {
                        val absTargetAbilityPlace = 10.0
                        if (avatarAbility9KPlace < absTargetAbilityPlace) {
                            -(avatarAbility9KPlace + 1).toDouble()
                        } else {
                            (avatarAbility9KPlace - absTargetAbilityPlace) / (avatarAbility9KCount - absTargetAbilityPlace)
                        }
                    } else {
                        1.0
                    }
                })

                futures.join()

                object {
                    var avatarID = avatarID
                    val avatarName = avatarName
                    val avatarIntro = avatarIntro
                    val totalCount = totalCount
                    val totalLength = totalLength
                    val highestCount = highestCount
                    val date = date
                    val avatarLevels = avatarLevels
                    val avatarAbility5K = avatarAbility5K
                    val avatarAbility5KClass = avatarAbility5KClass
                    val avatarAbility5KPlace = avatarAbility5KPlace + 1
                    val avatarAbility5KCount = avatarAbility5KCount
                    val avatarAbility7K = avatarAbility7K
                    val avatarAbility7KClass = avatarAbility7KClass
                    val avatarAbility7KPlace = avatarAbility7KPlace + 1
                    val avatarAbility7KCount = avatarAbility7KCount
                    val avatarAbility9K = avatarAbility9K
                    val avatarAbility9KClass = avatarAbility9KClass
                    val avatarAbility9KPlace = avatarAbility9KPlace + 1
                    val avatarAbility9KCount = avatarAbility9KCount
                    val dateSet = dateSet
                    val dateValues = dateValues
                    val quitStatusValues = quitStatusValues
                }
            }
        }
    }

    fun getAvatarFavorites(inputMode: Component.InputMode, avatarID: String): CompletableFuture<Any> {
        return logValueFuture {
            pool.connection.use { db ->
                db.prepareStatement(
                    """
                        SELECT tw_note.Note_ID, Note_Variety, Title, Artist, Genre, Level_Text, Level, COUNT(Comment_ID) AS Count
                        FROM tw_note, tw_comment
                        WHERE tw_note.Note_ID = tw_comment.Note_ID AND Avatar = ? AND Input_Mode = ?
                        GROUP BY tw_note.Note_ID
                        ORDER BY COUNT DESC
                        LIMIT 10
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, avatarID)
                    dbStatement.setInt(2, inputMode.value)
                    dbStatement.executeQuery().use {
                        val data = mutableListOf<Any>()
                        while (it.next()) {
                            data.add(object {
                                val noteID = it.getString("Note_ID")
                                val noteVariety = it.getInt("Note_Variety")
                                val artist = it.getString("Artist")
                                val title = it.getString("Title")
                                val genre = it.getString("Genre")
                                val levelText = it.getString("Level_Text")
                                val level = it.getInt("Level")
                                val totalCount = it.getInt("Count")
                            })
                        }
                        data
                    }
                }
            }
        }
    }

    fun getAvatarLasts(inputMode: Component.InputMode, avatarID: String): CompletableFuture<Any> {
        return logValueFuture {
            pool.connection.use { db ->
                db.prepareStatement(
                    """
                        SELECT MAX(Date) AS Date, tw_note.Note_ID, Note_Variety, Title, Artist, Genre, Level_Text, Level
                        FROM tw_note, tw_comment
                        WHERE tw_note.Note_ID = tw_comment.Note_ID AND Avatar = ? AND Input_Mode = ?
                        GROUP BY tw_note.Note_ID
                        ORDER BY Date DESC
                        LIMIT 10
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, avatarID)
                    dbStatement.setInt(2, inputMode.value)
                    dbStatement.executeQuery().use {
                        val data = mutableListOf<Any>()
                        while (it.next()) {
                            data.add(object {
                                val noteID = it.getString("Note_ID")
                                val noteVariety = it.getInt("Note_Variety")
                                val artist = it.getString("Artist")
                                val title = it.getString("Title")
                                val genre = it.getString("Genre")
                                val levelText = it.getString("Level_Text")
                                val level = it.getInt("Level")
                                val date = it.getTimestamp("Date")
                            })
                        }
                        data
                    }
                }
            }
        }
    }

    fun getAvatarWwwLevels(avatarID: String): CompletableFuture<Any> {
        return logValueFuture {
            pool.connection.use { db ->
                db.prepareStatement(
                    """
                        SELECT Level_ID, Date
                        FROM tw_level
                        WHERE Avatar = ?
                        ORDER BY Date DESC
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.setString(1, avatarID)
                    dbStatement.executeQuery().use {
                        val data = mutableListOf<Any>()
                        while (it.next()) {
                            LevelSystem.getLevelItem(it.getString("Level_ID"))?.let { levelItem ->
                                data.add(object {
                                    val levelID = levelItem.levelID
                                    val title = levelItem.title
                                    val levelText = levelItem.levelText
                                    val level = levelItem.level
                                    val date = it.getTimestamp("Date")
                                })
                            }
                        }
                        data
                    }
                }
            }
        }
    }

    fun getAvatarAbilityNoteFiles(inputMode: Component.InputMode, avatarID: String): CompletableFuture<Any> {
        return logValueFuture {
            (when (inputMode) {
                Component.InputMode.INPUT_MODE_5_1 -> getAbilities5K()
                Component.InputMode.INPUT_MODE_7_1 -> getAbilities7K()
                Component.InputMode.INPUT_MODE_9 -> getAbilities9K()
                else -> emptyMap()
            }.entries.singleOrNull { it.key.first == avatarID } ?: AbstractMap.SimpleEntry(
                Pair(avatarID, ""),
                ArrayList()
            )).value.sortedByDescending { it.ability }
        }
    }

    interface IVSItem {
        val noteID: String
        val stand: Int
        var vsStand: Int
    }

    fun getAvatarVS(avatarID: String, targetID: String): CompletableFuture<Any> {
        fun getVSItems(avatarID: String, vsItems: MutableMap<String, IVSItem>): CompletableFuture<Void> {
            return logFuture {
                pool.connection.use { db ->
                    db.prepareStatement(
                        """
                        SELECT tw_note.Note_ID, Note_Variety, Title, Artist, Genre, Level_Text, Level, Stand
                        FROM tw_note, tw_comment
                        WHERE tw_note.Note_ID = tw_comment.Note_ID AND Avatar = ? AND Is_Max = true
                    """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.setString(1, avatarID)
                        dbStatement.executeQuery().use {
                            while (it.next()) {
                                val noteID = it.getString("Note_ID")
                                vsItems[noteID] = object : IVSItem {
                                    override val noteID = noteID
                                    val noteVariety = it.getInt("Note_Variety")
                                    val artist = it.getString("Artist")
                                    val title = it.getString("Title")
                                    val genre = it.getString("Genre")
                                    val levelText = it.getString("Level_Text")
                                    val level = it.getInt("Level")
                                    override val stand = it.getInt("Stand")
                                    override var vsStand = 0
                                }
                            }
                        }
                    }
                }
            }
        }

        val avatarVSMap = mutableMapOf<String, IVSItem>()
        val targetVSMap = mutableMapOf<String, IVSItem>()
        return CompletableFuture.allOf(getVSItems(avatarID, avatarVSMap), getVSItems(targetID, targetVSMap)).thenApply {
            val avatarVSItems = mutableSetOf<IVSItem>()
            val targetVSItems = mutableSetOf<IVSItem>()
            avatarVSMap.keys.intersect(targetVSMap.keys).forEach {
                val avatarVSItem = avatarVSMap[it]
                val targetVSItem = targetVSMap[it]
                if (avatarVSItem != null && targetVSItem != null) {
                    val vsStand = avatarVSItem.stand - targetVSItem.stand
                    if (vsStand > 0) {
                        avatarVSItem.vsStand = vsStand
                        avatarVSItems.add(avatarVSItem)
                    } else if (vsStand < 0) {
                        targetVSItem.vsStand = -vsStand
                        targetVSItems.add(targetVSItem)
                    }
                }
            }

            object {
                val avatarVSCount = avatarVSItems.size
                val targetVSCount = targetVSItems.size
                val avatarVSItems = avatarVSItems.sortedBy { it.vsStand }.take(10)
                val targetVSItems = targetVSItems.sortedBy { it.vsStand }.take(10)
            }
        }
    }

    private fun getAbilities5K(): Map<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>> {
        val abilities5K = mutableMapOf<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>>()
        pool.connection.use {
            it.prepareStatement(
                """
                    SELECT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, Level, Stand, Avatar_ID, Avatar_Name, Ability_5K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value
                    FROM tw_note, tw_comment, tn_avatar
                    WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Ability_5K > 0.0 AND (Input_Favor_Mode = 0 OR Input_Favor_Mode = 5 OR Input_Favor_Mode = 10) AND (Hit_Points_Mode = 1 OR Hit_Points_Mode = 2 OR Hit_Points_Mode = 5) AND (Judgment_Mode = 1 OR Judgment_Mode = 2 OR Judgment_Mode = 4) AND Is_Max = true
                    ORDER BY Value DESC
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.executeQuery().use { rows ->
                    while (rows.next()) {
                        val abilities = abilities5K.computeIfAbsent(
                            Pair(rows.getString("Avatar_ID"), rows.getString("Avatar_Name"))
                        ) { ArrayList() }
                        if (abilities.size < 50) {
                            abilities.add(JSON.TwilightWwwAvatarAbility(rows, 0.95.pow(abilities.size.toDouble())))
                        }
                    }
                }
            }
        }
        return abilities5K
    }

    fun getAvatarAbility5K(avatarID: String): Double {
        pool.connection.use { db ->
            db.prepareStatement(
                """
                    SELECT Ability_5K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value
                    FROM tw_note, tw_comment, tn_avatar
                    WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Avatar_ID = ? AND Ability_5K > 0.0 AND (Input_Favor_Mode = 0 OR Input_Favor_Mode = 5 OR Input_Favor_Mode = 10) AND (Hit_Points_Mode = 1 OR Hit_Points_Mode = 2 OR Hit_Points_Mode = 5) AND (Judgment_Mode = 1 OR Judgment_Mode = 2 OR Judgment_Mode = 4) AND Is_Max = true
                    ORDER BY Value DESC
                    LIMIT 50
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setString(1, avatarID)
                dbStatement.executeQuery().use { rows ->
                    val abilities = mutableListOf<Double>()
                    while (rows.next()) {
                        abilities.add(rows.getDouble("Value") * 0.95.pow(abilities.size.toDouble()))
                    }
                    return abilities.sumOf { it }
                }
            }
        }
    }

    private fun getAbilities7K(): Map<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>> {
        val abilities7K = mutableMapOf<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>>()
        pool.connection.use {
            it.prepareStatement(
                """
                    SELECT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, Level, Stand, Avatar_ID, Avatar_Name, Ability_7K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value
                    FROM tw_note, tw_comment, tn_avatar
                    WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Ability_7K > 0.0 AND (Input_Favor_Mode = 0 OR Input_Favor_Mode = 7 OR Input_Favor_Mode = 11) AND (Hit_Points_Mode = 1 OR Hit_Points_Mode = 2 OR Hit_Points_Mode = 5) AND (Judgment_Mode = 1 OR Judgment_Mode = 2 OR Judgment_Mode = 4) AND Is_Max = true
                    ORDER BY Value DESC
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.executeQuery().use { rows ->
                    while (rows.next()) {
                        val abilities = abilities7K.computeIfAbsent(
                            Pair(rows.getString("Avatar_ID"), rows.getString("Avatar_Name"))
                        ) { ArrayList() }
                        if (abilities.size < 50) {
                            abilities.add(JSON.TwilightWwwAvatarAbility(rows, 0.95.pow(abilities.size.toDouble())))
                        }
                    }
                }
            }
        }
        return abilities7K
    }

    fun getAvatarAbility7K(avatarID: String): Double {
        pool.connection.use { db ->
            db.prepareStatement(
                """
                    SELECT Ability_7K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value
                    FROM tw_note, tw_comment, tn_avatar
                    WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Avatar_ID = ? AND Ability_7K > 0.0 AND (Input_Favor_Mode = 0 OR Input_Favor_Mode = 7 OR Input_Favor_Mode = 11) AND (Hit_Points_Mode = 1 OR Hit_Points_Mode = 2 OR Hit_Points_Mode = 5) AND (Judgment_Mode = 1 OR Judgment_Mode = 2 OR Judgment_Mode = 4) AND Is_Max = true
                    ORDER BY Value DESC
                    LIMIT 50
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setString(1, avatarID)
                dbStatement.executeQuery().use { rows ->
                    val abilities = mutableListOf<Double>()
                    while (rows.next()) {
                        abilities.add(rows.getDouble("Value") * 0.95.pow(abilities.size.toDouble()))
                    }
                    return abilities.sumOf { it }
                }
            }
        }
    }

    private fun getAbilities9K(): Map<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>> {
        val abilities5K = mutableMapOf<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>>()
        pool.connection.use {
            it.prepareStatement(
                """
                    SELECT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, Level, Stand, Avatar_ID, Avatar_Name, Ability_9K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value
                    FROM tw_note, tw_comment, tn_avatar
                    WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Ability_9K > 0.0 AND (Input_Favor_Mode = 0 OR Input_Favor_Mode = 9) AND (Hit_Points_Mode = 1 OR Hit_Points_Mode = 2 OR Hit_Points_Mode = 5) AND (Judgment_Mode = 1 OR Judgment_Mode = 2 OR Judgment_Mode = 4) AND Is_Max = true
                    ORDER BY Value DESC
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.executeQuery().use { rows ->
                    while (rows.next()) {
                        val abilities = abilities5K.computeIfAbsent(
                            Pair(rows.getString("Avatar_ID"), rows.getString("Avatar_Name"))
                        ) { ArrayList() }
                        if (abilities.size < 50) {
                            abilities.add(JSON.TwilightWwwAvatarAbility(rows, 0.95.pow(abilities.size.toDouble())))
                        }
                    }
                }
            }
        }
        return abilities5K
    }

    fun getAvatarAbility9K(avatarID: String): Double {
        pool.connection.use { db ->
            db.prepareStatement(
                """
                    SELECT Ability_9K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value
                    FROM tw_note, tw_comment, tn_avatar
                    WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Avatar_ID = ? AND Ability_9K > 0.0 AND (Input_Favor_Mode = 0 OR Input_Favor_Mode = 9) AND (Hit_Points_Mode = 1 OR Hit_Points_Mode = 2 OR Hit_Points_Mode = 5) AND (Judgment_Mode = 1 OR Judgment_Mode = 2 OR Judgment_Mode = 4) AND Is_Max = true
                    ORDER BY Value DESC
                    LIMIT 50
                """.trimIndent()
            ).use { dbStatement ->
                dbStatement.setString(1, avatarID)
                dbStatement.executeQuery().use { rows ->
                    val abilities = mutableListOf<Double>()
                    while (rows.next()) {
                        abilities.add(rows.getDouble("Value") * 0.95.pow(abilities.size.toDouble()))
                    }
                    return abilities.sumOf { it }
                }
            }
        }
    }

    private fun getAbilities(abilities: Map<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>>): Stream<Pair<Pair<String, String>, Pair<Double, Boolean>>> {
        return abilities.entries.stream().map { (avatar, abilities) ->
            Pair(
                avatar, Pair(
                    abilities.sumOf { it.ability }, !abilities.isEmpty()
                )
            )
        }.sorted { o1, o2 ->
            val value = o2.second.first - o1.second.first
            if (value > 0) {
                1
            } else if (value < 0) {
                -1
            } else {
                0
            }
        }
    }

    fun getHOFTotalTotal(): CompletableFuture<Any> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Avatar_ID, Avatar_Name, COUNT(tw_comment.Note_ID) AS Value
                        FROM tw_comment, tn_avatar
                        WHERE tw_comment.Avatar = tn_avatar.Avatar_ID
                        GROUP BY Avatar_ID
                        ORDER BY Value DESC
                        LIMIT 10
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.executeQuery().use { rows ->
                        val data = mutableListOf<Any>()
                        while (rows.next()) {
                            data.add(object {
                                val avatarID = rows.getString("Avatar_ID")
                                val avatarName = rows.getString("Avatar_Name")
                                val value = rows.getLong("Value")
                            })
                        }
                        data
                    }
                }
            }
        }
    }

    fun getHOFAtTotal(): CompletableFuture<Any> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Avatar_ID, Avatar_Name, COUNT(tw_comment.Note_ID) AS Value
                        FROM tw_comment, tn_avatar
                        WHERE "${
                        yyyyMMDDFormat.format(
                            LocalDate.now().withDayOfMonth(1)
                        )
                    }" <= tw_comment.Date AND tw_comment.Avatar = tn_avatar.Avatar_ID
                        GROUP BY Avatar_ID
                        ORDER BY Value DESC
                        LIMIT 10
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.executeQuery().use { rows ->
                        val data = mutableListOf<Any>()
                        while (rows.next()) {
                            data.add(object {
                                val avatarID = rows.getString("Avatar_ID")
                                val avatarName = rows.getString("Avatar_Name")
                                val value = rows.getLong("Value")
                            })
                        }
                        data
                    }
                }
            }
        }
    }

    fun getHOFTotalHighest(): CompletableFuture<Any> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Avatar_ID, Avatar_Name, COUNT(DISTINCT tw_comment.Note_ID) AS Value
                        FROM tw_comment, tn_avatar
                        WHERE tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Max = true
                        GROUP BY Avatar_ID
                        ORDER BY Value DESC
                        LIMIT 10
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.executeQuery().use { rows ->
                        val data = mutableListOf<Any>()
                        while (rows.next()) {
                            data.add(object {
                                val avatarID = rows.getString("Avatar_ID")
                                val avatarName = rows.getString("Avatar_Name")
                                val value = rows.getLong("Value")
                            })
                        }
                        data
                    }
                }
            }
        }
    }

    fun getHOFAtHighest(): CompletableFuture<Any> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Avatar_ID, Avatar_Name, COUNT(DISTINCT tw_comment.Note_ID) AS Value
                        FROM tw_comment, tn_avatar
                        WHERE "${
                        yyyyMMDDFormat.format(
                            LocalDate.now().withDayOfMonth(1)
                        )
                    }" <= tw_comment.Date AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Max = true
                        GROUP BY Avatar_ID
                        ORDER BY Value DESC
                        LIMIT 10
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.executeQuery().use { rows ->
                        val data = mutableListOf<Any>()
                        while (rows.next()) {
                            data.add(object {
                                val avatarID = rows.getString("Avatar_ID")
                                val avatarName = rows.getString("Avatar_Name")
                                val value = rows.getLong("Value")
                            })
                        }
                        data
                    }
                }
            }
        }
    }

    fun getHOFTotalStand(): CompletableFuture<Any> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Avatar_ID, Avatar_Name, SUM(Stand) AS Value
                        FROM tw_comment, tn_avatar
                        WHERE tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Max = true
                        GROUP BY Avatar_ID
                        ORDER BY Value DESC
                        LIMIT 10
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.executeQuery().use { rows ->
                        val data = mutableListOf<Any>()
                        while (rows.next()) {
                            data.add(object {
                                val avatarID = rows.getString("Avatar_ID")
                                val avatarName = rows.getString("Avatar_Name")
                                val value = rows.getLong("Value")
                            })
                        }
                        data
                    }
                }
            }
        }
    }

    fun getHOFAtStand(): CompletableFuture<Any> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Avatar_ID, Avatar_Name, SUM(Stand) AS Value
                        FROM tw_comment, tn_avatar
                        WHERE "${
                        yyyyMMDDFormat.format(
                            LocalDate.now().withDayOfMonth(1)
                        )
                    }" <= tw_comment.Date AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Max = true
                        GROUP BY Avatar_ID
                        ORDER BY Value DESC
                        LIMIT 10
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.executeQuery().use { rows ->
                        val data = mutableListOf<Any>()
                        while (rows.next()) {
                            data.add(object {
                                val avatarID = rows.getString("Avatar_ID")
                                val avatarName = rows.getString("Avatar_Name")
                                val value = rows.getLong("Value")
                            })
                        }
                        data
                    }
                }
            }
        }
    }

    fun getHOFTotalBand(): CompletableFuture<Any> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Avatar_ID, Avatar_Name, MAX(Band) AS Value
                        FROM tw_comment, tn_avatar
                        WHERE tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Max = true
                        GROUP BY Avatar_ID
                        ORDER BY Value DESC
                        LIMIT 10
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.executeQuery().use { rows ->
                        val data = mutableListOf<Any>()
                        while (rows.next()) {
                            data.add(object {
                                val avatarID = rows.getString("Avatar_ID")
                                val avatarName = rows.getString("Avatar_Name")
                                val value = rows.getLong("Value")
                            })
                        }
                        data
                    }
                }
            }
        }
    }

    fun getHOFAtBand(): CompletableFuture<Any> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Avatar_ID, Avatar_Name, MAX(Band) AS Value
                        FROM tw_comment, tn_avatar
                        WHERE "${
                        yyyyMMDDFormat.format(
                            LocalDate.now().withDayOfMonth(1)
                        )
                    }" <= tw_comment.Date AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Max = true
                        GROUP BY Avatar_ID
                        ORDER BY Value DESC
                        LIMIT 10
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.executeQuery().use { rows ->
                        val data = mutableListOf<Any>()
                        while (rows.next()) {
                            data.add(object {
                                val avatarID = rows.getString("Avatar_ID")
                                val avatarName = rows.getString("Avatar_Name")
                                val value = rows.getLong("Value")
                            })
                        }
                        data
                    }
                }
            }
        }
    }

    fun getHOFAbility(inputMode: Component.InputMode): CompletableFuture<Any> {
        return logValueFuture {
            getAbilities(
                when (inputMode) {
                    Component.InputMode.INPUT_MODE_5_1 -> getAbilities5K()
                    Component.InputMode.INPUT_MODE_7_1 -> getAbilities7K()
                    Component.InputMode.INPUT_MODE_9 -> getAbilities9K()
                    else -> emptyMap()
                }
            ).limit(50).map { (avatar, abilities) ->
                object {
                    val avatarID = avatar.first
                    val avatarName = avatar.second
                    val value = abilities.first
                }
            }.toArray()
        }
    }

    fun getHOFLevel(): CompletableFuture<Any> {
        return logValueFuture {
            pool.connection.use {
                it.prepareStatement(
                    """
                        SELECT Avatar_ID, Avatar_Name, 1 + SUM(Total_Notes * Stand / 1000000 / 10000) AS Value
                        FROM tw_note, tw_comment, tn_avatar
                        WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Salt = false
                        GROUP BY Avatar_ID
                        ORDER BY Value DESC
                        LIMIT 50
                    """.trimIndent()
                ).use { dbStatement ->
                    dbStatement.executeQuery().use { rows ->
                        val data = mutableListOf<Any>()
                        while (rows.next()) {
                            data.add(object {
                                val avatarID = rows.getString("Avatar_ID")
                                val avatarName = rows.getString("Avatar_Name")
                                val value = rows.getLong("Value")
                            })
                        }
                        data
                    }
                }
            }
        }
    }

    fun getEtc(language: String): CompletableFuture<Any> {
        val yyyyMM = yyyyMMFormat.format(Instant.now())

        val totalDateSet = mutableListOf<String>()
        val totalDateValues = mutableListOf<Int>()
        val signUpDateSet = mutableListOf<String>()
        val signUpDateValues = mutableListOf<Int>()
        val avatarDateSet = mutableListOf<String>()
        val avatarDateValues = mutableListOf<Int>()
        val totalNoteFiles = mutableListOf<Any>()
        val totalTitles = mutableListOf<Any>()
        val totalEdges = mutableListOf<Any>()
        val favorites = mutableListOf<Any>()
        val favoritesAt = mutableListOf<Any>()
        val inputModes = mutableListOf<Any>()
        val autoModes = mutableListOf<Any>()
        val noteSaltModes = mutableListOf<Any>()
        val faintNoteModes = mutableListOf<Any>()
        val judgmentModes = mutableListOf<Any>()
        val hitPointsModes = mutableListOf<Any>()
        val noteMobilityModes = mutableListOf<Any>()
        val longNoteModes = mutableListOf<Any>()
        val inputFavorModes = mutableListOf<Any>()
        val noteModifyModes = mutableListOf<Any>()
        val lowestJudgmentConditionModes = mutableListOf<Any>()

        return CompletableFuture.allOf(
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT DATE_FORMAT(Date, "%Y-%m") AS Etc_Date, COUNT(*) AS Value
                            FROM tw_comment
                            GROUP BY Etc_Date
                            HAVING Etc_Date != "0000-00"
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                totalDateSet.add(rows.getString("Etc_Date"))
                                totalDateValues.add(rows.getInt("Value"))
                            }
                        }
                    }
                }
            },
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT DATE_FORMAT(Date, "%Y-%m") AS Etc_Date, COUNT(*) AS Value
                            FROM tn_avatar
                            GROUP BY Etc_Date
                            HAVING Etc_Date != "0000-00"
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                signUpDateSet.add(rows.getString("Etc_Date"))
                                signUpDateValues.add(rows.getInt("Value"))
                            }
                        }
                    }
                }
            },
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT DATE_FORMAT(Date, "%Y-%m") AS Etc_Date, COUNT(DISTINCT Avatar) AS Value
                            FROM tw_comment
                            GROUP BY Etc_Date
                            HAVING Etc_Date != "0000-00"
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                avatarDateSet.add(rows.getString("Etc_Date"))
                                avatarDateValues.add(rows.getInt("Value"))
                            }
                        }
                    }
                }
            },
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT tw_comment.Note_ID, Title, Artist, Genre, Level, Level_Text, COUNT(tw_comment.Note_ID) AS Value
                            FROM tw_note, tw_comment
                            WHERE tw_note.Note_ID = tw_comment.Note_ID AND Date BETWEEN "$yyyyMM-01" AND "$yyyyMM-31"
                            GROUP BY tw_comment.Note_ID
                            ORDER BY Value DESC
                            LIMIT 10;
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                totalNoteFiles.add(object {
                                    val noteID = rows.getString("Note_ID")
                                    val title = rows.getString("Title")
                                    val artist = rows.getString("Artist")
                                    val genre = rows.getString("Genre")
                                    val level = rows.getInt("Level")
                                    val levelText = rows.getString("Level_Text")
                                    val value = rows.getInt("Value")
                                })
                            }
                        }
                    }
                }
            },
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT Title_ID, COUNT(Title_ID) AS Value
                            FROM tw_title
                            GROUP BY Title_ID
                            ORDER BY Value DESC
                            LIMIT 10;
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                val wwwTitle = TitleSystem.getTitle(language, rows.getString("Title_ID"))
                                if (wwwTitle != null) {
                                    totalTitles.add(object {
                                        val title = wwwTitle.title
                                        val titleColor = wwwTitle.titleColor
                                        val value = rows.getInt("Value")
                                    })
                                }
                            }
                        }
                    }
                }
            },
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT Edge_ID, COUNT(Edge_ID) AS Value
                            FROM tw_edge
                            WHERE Edge_ID != "Default"
                            GROUP BY Edge_ID
                            ORDER BY Value DESC
                            LIMIT 10;
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                totalEdges.add(object {
                                    val edge =
                                        Base64.getEncoder()
                                            .encodeToString(EdgeSystem.getDrawing(rows.getString("Edge_ID")))
                                    val value = rows.getInt("Value")
                                })
                            }
                        }
                    }
                }
            },
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT tw_favor.Note_ID, Title, Artist, Genre, Level, Level_Text, SUM(2 * Favor - 1) AS Value
                            FROM tw_note, tw_favor
                            WHERE tw_note.Note_ID = tw_favor.Note_ID
                            GROUP BY tw_favor.Note_ID
                            ORDER BY Value DESC
                            LIMIT 10;
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                favorites.add(object {
                                    val noteID = rows.getString("Note_ID")
                                    val title = rows.getString("Title")
                                    val artist = rows.getString("Artist")
                                    val genre = rows.getString("Genre")
                                    val level = rows.getInt("Level")
                                    val levelText = rows.getString("Level_Text")
                                    val value = rows.getInt("Value")
                                })
                            }
                        }
                    }
                }
            },
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT tw_favor.Note_ID, Title, Artist, Genre, Level, Level_Text, SUM(2 * Favor - 1) AS Value
                            FROM tw_note, tw_favor
                            WHERE tw_note.Note_ID = tw_favor.Note_ID AND Date BETWEEN "$yyyyMM-01" AND "$yyyyMM-31"
                            GROUP BY tw_favor.Note_ID
                            ORDER BY Value DESC
                            LIMIT 10;
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                favoritesAt.add(object {
                                    val noteID = rows.getString("Note_ID")
                                    val title = rows.getString("Title")
                                    val artist = rows.getString("Artist")
                                    val genre = rows.getString("Genre")
                                    val level = rows.getInt("Level")
                                    val levelText = rows.getString("Level_Text")
                                    val value = rows.getInt("Value")
                                })
                            }
                        }
                    }
                }
            },
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT Input_Mode AS Mode, COUNT(Input_Mode) AS Value
                            FROM tw_note, tw_comment
                            WHERE tw_note.Note_ID = tw_comment.Note_ID
                            GROUP BY Mode
                            ORDER BY Value DESC
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                inputModes.add(object {
                                    val mode = rows.getInt("Mode")
                                    val value = rows.getInt("Value")
                                })
                            }
                        }
                    }
                }
            },
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT Auto_Mode AS Mode, COUNT(Auto_Mode) AS Value
                            FROM tw_comment
                            GROUP BY Mode
                            ORDER BY Value DESC
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                autoModes.add(object {
                                    val mode = rows.getInt("Mode")
                                    val value = rows.getInt("Value")
                                })
                            }
                        }
                    }
                }
            },
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT Note_Salt_Mode AS Mode, COUNT(Note_Salt_Mode) AS Value
                            FROM tw_comment
                            GROUP BY Mode
                            ORDER BY Value DESC
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                noteSaltModes.add(object {
                                    val mode = rows.getInt("Mode")
                                    val value = rows.getInt("Value")
                                })
                            }
                        }
                    }
                }
            },
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT Faint_Note_Mode AS Mode, COUNT(Faint_Note_Mode) AS Value
                            FROM tw_comment
                            GROUP BY Mode
                            ORDER BY Value DESC
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                faintNoteModes.add(object {
                                    val mode = rows.getInt("Mode")
                                    val value = rows.getInt("Value")
                                })
                            }
                        }
                    }
                }
            },
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT Judgment_Mode AS Mode, COUNT(Judgment_Mode) AS Value
                            FROM tw_comment
                            GROUP BY Mode
                            ORDER BY Value DESC
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                judgmentModes.add(object {
                                    val mode = rows.getInt("Mode")
                                    val value = rows.getInt("Value")
                                })
                            }
                        }
                    }
                }
            },
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT Hit_Points_Mode AS Mode, COUNT(Hit_Points_Mode) AS Value
                            FROM tw_comment
                            GROUP BY Mode
                            ORDER BY Value DESC
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                hitPointsModes.add(object {
                                    val mode = rows.getInt("Mode")
                                    val value = rows.getInt("Value")
                                })
                            }
                        }
                    }
                }
            },
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT Note_Mobility_Mode AS Mode, COUNT(Note_Mobility_Mode) AS Value
                            FROM tw_comment
                            GROUP BY Mode
                            ORDER BY Value DESC
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                noteMobilityModes.add(object {
                                    val mode = rows.getInt("Mode")
                                    val value = rows.getInt("Value")
                                })
                            }
                        }
                    }
                }
            },
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT Long_Note_Mode AS Mode, COUNT(Long_Note_Mode) AS Value
                            FROM tw_comment
                            GROUP BY Mode
                            ORDER BY Value DESC
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                longNoteModes.add(object {
                                    val mode = rows.getInt("Mode")
                                    val value = rows.getInt("Value")
                                })
                            }
                        }
                    }
                }
            },
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT Input_Favor_Mode AS Mode, COUNT(Input_Favor_Mode) AS Value
                            FROM tw_comment
                            GROUP BY Mode
                            ORDER BY Value DESC
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                inputFavorModes.add(object {
                                    val mode = rows.getInt("Mode")
                                    val value = rows.getInt("Value")
                                })
                            }
                        }
                    }
                }
            },
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT Note_Modify_Mode AS Mode, COUNT(Note_Modify_Mode) AS Value
                            FROM tw_comment
                            GROUP BY Mode
                            ORDER BY Value DESC
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                noteModifyModes.add(object {
                                    val mode = rows.getInt("Mode")
                                    val value = rows.getInt("Value")
                                })
                            }
                        }
                    }
                }
            },
            logValueFuture {
                pool.connection.use {
                    it.prepareStatement(
                        """
                            SELECT Lowest_Judgment_Condition_Mode AS Mode, COUNT(Lowest_Judgment_Condition_Mode) AS Value
                            FROM tw_comment
                            GROUP BY Mode
                            ORDER BY Value DESC
                        """.trimIndent()
                    ).use { dbStatement ->
                        dbStatement.executeQuery().use { rows ->
                            while (rows.next()) {
                                lowestJudgmentConditionModes.add(object {
                                    val mode = rows.getInt("Mode")
                                    val value = rows.getInt("Value")
                                })
                            }
                        }
                    }
                }
            }
        ).thenApply {
            object {
                val totalDateSet = totalDateSet
                val totalDateValues = totalDateValues
                val signUpDateSet = signUpDateSet
                val signUpDateValues = signUpDateValues
                val avatarDateSet = avatarDateSet
                val avatarDateValues = avatarDateValues
                val totalNoteFiles = totalNoteFiles
                val totalTitles = totalTitles
                val totalEdges = totalEdges
                val favorites = favorites
                val favoritesAt = favoritesAt
                val inputModes = inputModes
                val autoModes = autoModes
                val noteSaltModes = noteSaltModes
                val faintNoteModes = faintNoteModes
                val judgmentModes = judgmentModes
                val hitPointsModes = hitPointsModes
                val noteMobilityModes = noteMobilityModes
                val longNoteModes = longNoteModes
                val inputFavorModes = inputFavorModes
                val noteModifyModes = noteModifyModes
                val lowestJudgmentConditionModes = lowestJudgmentConditionModes
            }
        }
    }
}