package org.gotson.komga.infrastructure.jooq

import org.gotson.komga.domain.model.Book
import org.gotson.komga.domain.model.BookSearch
import org.gotson.komga.domain.persistence.BookRepository
import org.gotson.komga.jooq.Tables
import org.gotson.komga.jooq.tables.records.BookRecord
import org.jooq.Condition
import org.jooq.DSLContext
import org.jooq.impl.DSL
import org.springframework.beans.factory.annotation.Value
import org.springframework.data.domain.Page
import org.springframework.data.domain.PageImpl
import org.springframework.data.domain.PageRequest
import org.springframework.data.domain.Pageable
import org.springframework.data.domain.Sort
import org.springframework.stereotype.Component
import org.springframework.transaction.annotation.Transactional
import java.math.BigDecimal
import java.net.URL
import java.time.LocalDateTime
import java.time.ZoneId

@Component
class BookDao(
  private val dsl: DSLContext,
  @Value("#{@komgaProperties.database.batchChunkSize}") private val batchSize: Int,
) : BookRepository {
  private val b = Tables.BOOK
  private val m = Tables.MEDIA
  private val d = Tables.BOOK_METADATA
  private val r = Tables.READ_PROGRESS
  private val l = Tables.LIBRARY

  private val sorts = mapOf(
    "createdDate" to b.CREATED_DATE,
    "seriesId" to b.SERIES_ID,
    "number" to b.NUMBER,
  )

  override fun findByIdOrNull(bookId: String): Book? =
    findByIdOrNull(dsl, bookId)

  override fun findNotDeletedByLibraryIdAndUrlOrNull(libraryId: String, url: URL): Book? =
    dsl.selectFrom(b)
      .where(b.LIBRARY_ID.eq(libraryId).and(b.URL.eq(url.toString())))
      .and(b.DELETED_DATE.isNull)
      .orderBy(b.LAST_MODIFIED_DATE.desc())
      .fetchInto(b)
      .firstOrNull()
      ?.toDomain()

  private fun findByIdOrNull(dsl: DSLContext, bookId: String): Book? =
    dsl.selectFrom(b)
      .where(b.ID.eq(bookId))
      .fetchOneInto(b)
      ?.toDomain()

  override fun findAllBySeriesId(seriesId: String): Collection<Book> =
    dsl.selectFrom(b)
      .where(b.SERIES_ID.eq(seriesId))
      .fetchInto(b)
      .map { it.toDomain() }

  override fun findAllBySeriesIds(seriesIds: Collection<String>): Collection<Book> =
    dsl.selectFrom(b)
      .where(b.SERIES_ID.`in`(seriesIds))
      .fetchInto(b)
      .map { it.toDomain() }

  @Transactional
  override fun findAllNotDeletedByLibraryIdAndUrlNotIn(libraryId: String, urls: Collection<URL>): Collection<Book> {
    dsl.insertTempStrings(batchSize, urls.map { it.toString() })

    return dsl.selectFrom(b)
      .where(b.LIBRARY_ID.eq(libraryId))
      .and(b.DELETED_DATE.isNull)
      .and(b.URL.notIn(dsl.selectTempStrings()))
      .fetchInto(b)
      .map { it.toDomain() }
  }

  override fun findAllDeletedByFileSize(fileSize: Long): Collection<Book> =
    dsl.selectFrom(b)
      .where(b.DELETED_DATE.isNotNull.and(b.FILE_SIZE.eq(fileSize)))
      .fetchInto(b)
      .map { it.toDomain() }

  override fun findAll(): Collection<Book> =
    dsl.selectFrom(b)
      .fetchInto(b)
      .map { it.toDomain() }

  override fun findAll(bookSearch: BookSearch): Collection<Book> =
    dsl.select(*b.fields())
      .from(b)
      .leftJoin(d).on(b.ID.eq(d.BOOK_ID))
      .leftJoin(m).on(b.ID.eq(m.BOOK_ID))
      .where(bookSearch.toCondition())
      .fetchInto(b)
      .map { it.toDomain() }

  override fun findAll(bookSearch: BookSearch, pageable: Pageable): Page<Book> {
    val conditions = bookSearch.toCondition()

    val count = dsl.selectCount()
      .from(b)
      .leftJoin(d).on(b.ID.eq(d.BOOK_ID))
      .leftJoin(m).on(b.ID.eq(m.BOOK_ID))
      .where(conditions)
      .fetchOne(0, Long::class.java) ?: 0

    val orderBy = pageable.sort.toOrderBy(sorts)

    val items = dsl.select(*b.fields())
      .from(b)
      .leftJoin(d).on(b.ID.eq(d.BOOK_ID))
      .leftJoin(m).on(b.ID.eq(m.BOOK_ID))
      .where(conditions)
      .orderBy(orderBy)
      .apply { if (pageable.isPaged) limit(pageable.pageSize).offset(pageable.offset) }
      .fetchInto(b)
      .map { it.toDomain() }

    val pageSort = if (orderBy.isNotEmpty()) pageable.sort else Sort.unsorted()
    return PageImpl(
      items,
      if (pageable.isPaged) PageRequest.of(pageable.pageNumber, pageable.pageSize, pageSort)
      else PageRequest.of(0, maxOf(count.toInt(), 20), pageSort),
      count,
    )
  }

  override fun getLibraryIdOrNull(bookId: String): String? =
    dsl.select(b.LIBRARY_ID)
      .from(b)
      .where(b.ID.eq(bookId))
      .fetchOne(b.LIBRARY_ID)

  override fun getSeriesIdOrNull(bookId: String): String? =
    dsl.select(b.SERIES_ID)
      .from(b)
      .where(b.ID.eq(bookId))
      .fetchOne(b.SERIES_ID)

  override fun findFirstIdInSeriesOrNull(seriesId: String): String? =
    dsl.select(b.ID)
      .from(b)
      .leftJoin(d).on(b.ID.eq(d.BOOK_ID))
      .where(b.SERIES_ID.eq(seriesId))
      .orderBy(d.NUMBER_SORT)
      .limit(1)
      .fetchOne(b.ID)

  override fun findLastIdInSeriesOrNull(seriesId: String): String? =
    dsl.select(b.ID)
      .from(b)
      .leftJoin(d).on(b.ID.eq(d.BOOK_ID))
      .where(b.SERIES_ID.eq(seriesId))
      .orderBy(d.NUMBER_SORT.desc())
      .limit(1)
      .fetchOne(b.ID)

  override fun findFirstUnreadIdInSeriesOrNull(seriesId: String, userId: String): String? =
    dsl.select(b.ID)
      .from(b)
      .leftJoin(d).on(b.ID.eq(d.BOOK_ID))
      .leftJoin(r).on(b.ID.eq(r.BOOK_ID)).and(r.USER_ID.eq(userId).or(r.USER_ID.isNull))
      .where(b.SERIES_ID.eq(seriesId))
      .and(r.COMPLETED.isNull)
      .orderBy(d.NUMBER_SORT)
      .limit(1)
      .fetchOne(b.ID)

  override fun findAllIdsBySeriesId(seriesId: String): Collection<String> =
    dsl.select(b.ID)
      .from(b)
      .where(b.SERIES_ID.eq(seriesId))
      .fetch(b.ID)

  override fun findAllIdsBySeriesIds(seriesIds: Collection<String>): Collection<String> =
    dsl.select(b.ID)
      .from(b)
      .where(b.SERIES_ID.`in`(seriesIds))
      .fetch(0, String::class.java)

  override fun findAllIdsByLibraryId(libraryId: String): Collection<String> =
    dsl.select(b.ID)
      .from(b)
      .where(b.LIBRARY_ID.eq(libraryId))
      .fetch(b.ID)

  override fun findAllIds(bookSearch: BookSearch, sort: Sort): Collection<String> {
    val conditions = bookSearch.toCondition()

    val orderBy = sort.toOrderBy(sorts)

    return dsl.select(b.ID)
      .from(b)
      .leftJoin(m).on(b.ID.eq(m.BOOK_ID))
      .leftJoin(d).on(b.ID.eq(d.BOOK_ID))
      .where(conditions)
      .orderBy(orderBy)
      .fetch(b.ID)
  }

  override fun findAllByLibraryIdAndMediaTypes(libraryId: String, mediaTypes: Collection<String>): Collection<Book> =
    dsl.select(*b.fields())
      .from(b)
      .leftJoin(m).on(b.ID.eq(m.BOOK_ID))
      .where(b.LIBRARY_ID.eq(libraryId))
      .and(m.MEDIA_TYPE.`in`(mediaTypes))
      .fetchInto(b)
      .map { it.toDomain() }

  override fun findAllByLibraryIdAndMismatchedExtension(libraryId: String, mediaType: String, extension: String): Collection<Book> =
    dsl.select(*b.fields())
      .from(b)
      .leftJoin(m).on(b.ID.eq(m.BOOK_ID))
      .where(b.LIBRARY_ID.eq(libraryId))
      .and(m.MEDIA_TYPE.eq(mediaType))
      .and(b.URL.notLike("%.$extension"))
      .fetchInto(b)
      .map { it.toDomain() }

  override fun findAllByLibraryIdAndWithEmptyHash(libraryId: String): Collection<Book> =
    dsl.selectFrom(b)
      .where(b.LIBRARY_ID.eq(libraryId))
      .and(b.FILE_HASH.eq(""))
      .fetchInto(b)
      .map { it.toDomain() }

  @Transactional
  override fun insert(book: Book) {
    insert(listOf(book))
  }

  @Transactional
  override fun insert(books: Collection<Book>) {
    if (books.isNotEmpty()) {
      books.chunked(batchSize).forEach { chunk ->
        dsl.batch(
          dsl.insertInto(
            b,
            b.ID,
            b.NAME,
            b.URL,
            b.NUMBER,
            b.FILE_LAST_MODIFIED,
            b.FILE_SIZE,
            b.FILE_HASH,
            b.LIBRARY_ID,
            b.SERIES_ID,
            b.DELETED_DATE,
          ).values(null as String?, null, null, null, null, null, null, null, null, null),
        ).also { step ->
          chunk.forEach {
            step.bind(
              it.id,
              it.name,
              it.url,
              it.number,
              it.fileLastModified,
              it.fileSize,
              it.fileHash,
              it.libraryId,
              it.seriesId,
              it.deletedDate,
            )
          }
        }.execute()
      }
    }
  }

  @Transactional
  override fun update(book: Book) {
    updateBook(book)
  }

  @Transactional
  override fun update(books: Collection<Book>) {
    books.map { updateBook(it) }
  }

  private fun updateBook(book: Book) {
    dsl.update(b)
      .set(b.NAME, book.name)
      .set(b.URL, book.url.toString())
      .set(b.NUMBER, book.number)
      .set(b.FILE_LAST_MODIFIED, book.fileLastModified)
      .set(b.FILE_SIZE, book.fileSize)
      .set(b.FILE_HASH, book.fileHash)
      .set(b.LIBRARY_ID, book.libraryId)
      .set(b.SERIES_ID, book.seriesId)
      .set(b.DELETED_DATE, book.deletedDate)
      .set(b.LAST_MODIFIED_DATE, LocalDateTime.now(ZoneId.of("Z")))
      .where(b.ID.eq(book.id))
      .execute()
  }

  override fun delete(bookId: String) {
    dsl.deleteFrom(b).where(b.ID.eq(bookId)).execute()
  }

  @Transactional
  override fun delete(bookIds: Collection<String>) {
    dsl.insertTempStrings(batchSize, bookIds)

    dsl.deleteFrom(b).where(b.ID.`in`(dsl.selectTempStrings())).execute()
  }

  override fun deleteAll() {
    dsl.deleteFrom(b).execute()
  }

  override fun count(): Long = dsl.fetchCount(b).toLong()

  override fun countGroupedByLibraryName(): Map<String, Int> =
    dsl.select(l.NAME, DSL.count(b.ID))
      .from(l)
      .leftJoin(b).on(l.ID.eq(b.LIBRARY_ID))
      .groupBy(l.NAME)
      .fetchMap(l.NAME, DSL.count(b.ID))

  override fun getFilesizeGroupedByLibraryName(): Map<String, BigDecimal> =
    dsl.select(l.NAME, DSL.sum(b.FILE_SIZE))
      .from(l)
      .leftJoin(b).on(l.ID.eq(b.LIBRARY_ID))
      .groupBy(l.NAME)
      .fetchMap(l.NAME, DSL.sum(b.FILE_SIZE))

  private fun BookSearch.toCondition(): Condition {
    var c: Condition = DSL.trueCondition()

    if (!libraryIds.isNullOrEmpty()) c = c.and(b.LIBRARY_ID.`in`(libraryIds))
    if (!seriesIds.isNullOrEmpty()) c = c.and(b.SERIES_ID.`in`(seriesIds))
    searchTerm?.let { c = c.and(d.TITLE.containsIgnoreCase(it)) }
    if (!mediaStatus.isNullOrEmpty()) c = c.and(m.STATUS.`in`(mediaStatus))
    if (deleted == true) c = c.and(b.DELETED_DATE.isNotNull)
    if (deleted == false) c = c.and(b.DELETED_DATE.isNull)
    if (releasedAfter != null) c = c.and(d.RELEASE_DATE.gt(releasedAfter))

    return c
  }

  private fun BookRecord.toDomain() =
    Book(
      name = name,
      url = URL(url),
      fileLastModified = fileLastModified,
      fileSize = fileSize,
      fileHash = fileHash,
      id = id,
      libraryId = libraryId,
      seriesId = seriesId,
      deletedDate = deletedDate,
      createdDate = createdDate.toCurrentTimeZone(),
      lastModifiedDate = lastModifiedDate.toCurrentTimeZone(),
      number = number,
    )
}
