package com.pixelized.biblib.repository.search

import androidx.sqlite.db.SimpleSQLiteQuery
import com.pixelized.biblib.database.BibLibDatabase
import com.pixelized.biblib.database.crossref.BookAuthorCrossRef
import com.pixelized.biblib.database.crossref.BookGenreCrossRef
import com.pixelized.biblib.database.data.*
import com.pixelized.biblib.database.factory.*
import com.pixelized.biblib.model.book.*
import com.pixelized.biblib.model.search.SortType
import com.pixelized.biblib.model.search.SortValue

class SearchRepository constructor(
    private val database: BibLibDatabase
) : ISearchRepository {

    override fun searchBooks(
        search: String?,
        isNew: Boolean?,
        authorId: Int?,
        seriesId: Int?,
        genreId: Int?,
        languageId: Int?,
        sortBy: Map<SortType, SortValue?>,
        limit: Int,
        offset: Int
    ): List<Book> {
        // build an argument list for the SimpleSQLQuery.
        val args = mutableListOf<Any>()
        val sort = mutableListOf<Any>()
        // build the core of the SQL query.
        var query = "SELECT ${BookDbo.TABLE}.* FROM ${BookDbo.TABLE}"
        // add where arguments.
        query += args.where(argument = search) {
            BookDbo.run { "$TITLE LIKE '%' || ? || '%'" }
        }
        query += args.where(argument = isNew) {
            BookDbo.run { "$IS_NEW LIKE ?" }
        }
        query += args.where(argument = authorId) {
            "${BookDbo.ID} IN (SELECT ${BookDbo.ID} FROM ${BookAuthorCrossRef.TABLE} WHERE ${AuthorDbo.ID} LIKE ?)"
        }
        query += args.where(argument = genreId) {
            "${BookDbo.ID} IN (SELECT ${BookDbo.ID} FROM ${BookGenreCrossRef.TABLE} WHERE ${GenreDbo.ID} LIKE ?)"
        }
        query += args.where(argument = seriesId) {
            BookDbo.run { "$TABLE.$SERIES_ID LIKE ?" }
        }
        query += args.where(argument = languageId) {
            BookDbo.run { "$TABLE.$LANGUAGE_ID LIKE ?" }
        }
        sortBy.entries.forEach { (type: SortType, value: SortValue?) ->
            query += sort.sort(argument = value) {
                when (type) {
                    SortType.New -> BookDbo.run { "$TABLE.$NEW_ORDER" }
                    SortType.Title -> BookDbo.run { "$TABLE.$SORT" }
                    SortType.Series -> BookDbo.run { "$TABLE.$SERIES_ID DESC, $TABLE.$SERIES_INDEX" }
                }
            }
        }
        // Limit and Offset the query.
        query += " LIMIT $limit OFFSET $offset;"
        // compute the query
        val liteQuery = SimpleSQLiteQuery(query, args.toTypedArray())
        val result = database.searchDao().getBooks(liteQuery)
        return result.map { it.toBook() }
    }

    override fun searchAuthor(search: String?, limit: Int, offset: Int): List<Author> {
        // build an argument list for the SimpleSQLQuery.
        val args = mutableListOf<Any>()
        // build the SQL query.
        val query = AuthorDbo.run {
            "SELECT $TABLE.* FROM $TABLE" +
                    args.where(argument = search) { "$TABLE.$NAME LIKE '%' || ? || '%'" } +
                    " ORDER BY $TABLE.$SORT" +
                    " LIMIT $limit OFFSET $offset;"
        }
        // compute the query
        val liteQuery = SimpleSQLiteQuery(query, args.toTypedArray())
        val result = database.searchDao().getAuthors(liteQuery)
        return result.map { it.toAuthor() }
    }

    override fun searchGenre(search: String?, limit: Int, offset: Int): List<Genre> {
        // build an argument list for the SimpleSQLQuery.
        val args = mutableListOf<Any>()
        // build the SQL query.
        val query = GenreDbo.run {
            "SELECT $TABLE.* FROM $TABLE" +
                    args.where(argument = search) { "$TABLE.$NAME LIKE '%' || ? || '%'" } +
                    " ORDER BY $TABLE.$NAME" +
                    " LIMIT $limit OFFSET $offset;"
        }
        // compute the query
        val liteQuery = SimpleSQLiteQuery(query, args.toTypedArray())
        val result = database.searchDao().getGenres(liteQuery)
        return result.map { it.toGenre() }
    }

    override fun searchSeries(search: String?, limit: Int, offset: Int): List<Series> {
        // build an argument list for the SimpleSQLQuery.
        val args = mutableListOf<Any>()
        // build the SQL query.
        val query = SeriesDbo.run {
            "SELECT $TABLE.* FROM $TABLE" +
                    args.where(argument = search) { "$TABLE.$NAME LIKE '%' || ? || '%'" } +
                    " ORDER BY $TABLE.$SORT" +
                    " LIMIT $limit OFFSET $offset;"
        }
        // compute the query
        val liteQuery = SimpleSQLiteQuery(query, args.toTypedArray())
        val result = database.searchDao().getSeries(liteQuery)
        return result.map { it.toSeries() }
    }

    override fun searchLanguage(search: String?, limit: Int, offset: Int): List<Language> {
        // build an argument list for the SimpleSQLQuery.
        val args = mutableListOf<Any>()
        // build the SQL query.
        val query = LanguageDbo.run {
            "SELECT $TABLE.* FROM $TABLE" +
                    args.where(argument = search) { "$TABLE.$NAME LIKE '%' || ? || '%'" } +
                    " ORDER BY $TABLE.$NAME" +
                    " LIMIT $limit OFFSET $offset;"
        }
        // compute the query
        val liteQuery = SimpleSQLiteQuery(query, args.toTypedArray())
        val result = database.searchDao().getLanguages(liteQuery)
        return result.map { it.toLanguage() }
    }
}

private fun MutableList<Any>.where(
    argument: Any?,
    block: () -> String,
): String {
    return if (argument != null) {
        val prefix = if (isEmpty()) "WHERE" else "AND"
        add(argument)
        " $prefix ${block()}"
    } else {
        ""
    }
}

private fun MutableList<Any>.sort(
    argument: SortValue?,
    block: () -> String,
): String {
    return if (argument != null) {
        val prefix = if (isEmpty()) "ORDER BY" else ","
        add(argument)
        " $prefix ${block()} ${argument.order()}"
    } else {
        ""
    }
}

private fun SortValue.order(): String = when (this) {
    SortValue.ASC -> "ASC"
    SortValue.DESC -> "DESC"
}