package org.loculus.backend.service.seqsetcitations

import kotlinx.datetime.DateTimeUnit
import kotlinx.datetime.minus
import kotlinx.datetime.toJavaLocalDateTime
import kotlinx.datetime.toLocalDateTime
import mu.KotlinLogging
import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.LikePattern
import org.jetbrains.exposed.sql.Op
import org.jetbrains.exposed.sql.ResultRow
import org.jetbrains.exposed.sql.SortOrder
import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq
import org.jetbrains.exposed.sql.SqlExpressionBuilder.inList
import org.jetbrains.exposed.sql.SqlExpressionBuilder.like
import org.jetbrains.exposed.sql.and
import org.jetbrains.exposed.sql.andWhere
import org.jetbrains.exposed.sql.batchInsert
import org.jetbrains.exposed.sql.batchUpsert
import org.jetbrains.exposed.sql.deleteWhere
import org.jetbrains.exposed.sql.insert
import org.jetbrains.exposed.sql.max
import org.jetbrains.exposed.sql.or
import org.jetbrains.exposed.sql.selectAll
import org.jetbrains.exposed.sql.update
import org.keycloak.representations.idm.UserRepresentation
import org.loculus.backend.api.AccessionVersion
import org.loculus.backend.api.AddSeqSetCitationRequest
import org.loculus.backend.api.AdminSeqSetCitation
import org.loculus.backend.api.AuthorProfile
import org.loculus.backend.api.CitationOrigin
import org.loculus.backend.api.CitationSource
import org.loculus.backend.api.ResponseSeqSet
import org.loculus.backend.api.SeqSet
import org.loculus.backend.api.SeqSetCitation
import org.loculus.backend.api.SeqSetCitationSource
import org.loculus.backend.api.SeqSetCitationsConstants
import org.loculus.backend.api.SeqSetCitingSequence
import org.loculus.backend.api.SeqSetRecord
import org.loculus.backend.api.SequenceCitation
import org.loculus.backend.api.Status.APPROVED_FOR_RELEASE
import org.loculus.backend.api.SubmittedSeqSetRecord
import org.loculus.backend.auth.AuthenticatedUser
import org.loculus.backend.config.BackendConfig
import org.loculus.backend.controller.ForbiddenException
import org.loculus.backend.controller.NotFoundException
import org.loculus.backend.controller.UnprocessableEntityException
import org.loculus.backend.service.crossref.CrossRefService
import org.loculus.backend.service.crossref.DoiEntry
import org.loculus.backend.service.submission.AccessionPreconditionValidator
import org.loculus.backend.utils.DateProvider
import org.loculus.backend.utils.getNextSequenceNumber
import org.springframework.stereotype.Service
import org.springframework.transaction.annotation.Transactional
import java.sql.Timestamp
import java.time.LocalDate
import javax.sql.DataSource

private val log = KotlinLogging.logger { }

data class CitationSourcesUpdateResult(val updatedCitationSourceDOIs: Set<String>)

data class SeqSetToCitationSourceEntry(val citationSourceId: Long, val seqSetId: String, val seqSetVersion: Long)

private fun ResultRow.toSeqSet() = SeqSet(
    seqSetId = this[SeqSetsTable.seqSetId],
    seqSetVersion = this[SeqSetsTable.seqSetVersion],
    name = this[SeqSetsTable.name],
    createdAt = Timestamp.valueOf(this[SeqSetsTable.createdAt].toJavaLocalDateTime()),
    createdBy = this[SeqSetsTable.createdBy],
    description = this[SeqSetsTable.description],
    seqSetDOI = this[SeqSetsTable.seqSetDOI],
)

@Service
@Transactional
class SeqSetCitationsDatabaseService(
    private val accessionPreconditionValidator: AccessionPreconditionValidator,
    private val backendConfig: BackendConfig,
    private val crossRefService: CrossRefService,
    private val dateProvider: DateProvider,
    pool: DataSource,
) {
    init {
        Database.connect(pool)
    }

    fun constructSeqsetId(seqsetIdNumber: Long): String = "${backendConfig.accessionPrefix}SS_$seqsetIdNumber"

    fun createSeqSet(
        authenticatedUser: AuthenticatedUser,
        seqSetName: String,
        seqSetRecords: List<SubmittedSeqSetRecord>,
        seqSetDescription: String?,
    ): ResponseSeqSet {
        log.info { "Create seqSet $seqSetName, user ${authenticatedUser.username}" }

        validateSeqSetName(seqSetName)
        validateSeqSetRecords(seqSetRecords)

        val seqsetIdNumber = getNextSequenceNumber("seqset_id_sequence")
        val insertedSet = SeqSetsTable
            .insert {
                it[seqSetId] = constructSeqsetId(seqsetIdNumber)
                it[name] = seqSetName
                it[description] = seqSetDescription ?: ""
                it[seqSetVersion] = 1
                it[createdAt] = dateProvider.getCurrentDateTime()
                it[createdBy] = authenticatedUser.username
            }

        for (record in seqSetRecords) {
            val insertedRecord = SeqSetRecordsTable
                .insert {
                    it[SeqSetRecordsTable.accession] = record.accession
                    it[SeqSetRecordsTable.type] = record.type
                    it[SeqSetRecordsTable.isFocal] = record.isFocal
                }
            SeqSetToRecordsTable
                .insert {
                    it[SeqSetToRecordsTable.seqSetRecordId] = insertedRecord[SeqSetRecordsTable.seqSetRecordId]
                    it[SeqSetToRecordsTable.seqSetId] = insertedSet[SeqSetsTable.seqSetId]
                    it[SeqSetToRecordsTable.seqSetVersion] = 1
                }
        }

        return ResponseSeqSet(
            insertedSet[SeqSetsTable.seqSetId],
            insertedSet[SeqSetsTable.seqSetVersion],
        )
    }

    fun updateSeqSet(
        authenticatedUser: AuthenticatedUser,
        seqSetId: String,
        seqSetName: String,
        seqSetRecords: List<SubmittedSeqSetRecord>,
        seqSetDescription: String?,
    ): ResponseSeqSet {
        val username = authenticatedUser.username
        log.info { "Update seqSet $seqSetId, user $username" }

        validateSeqSetName(seqSetName)
        validateSeqSetRecords(seqSetRecords)

        val maxVersion = SeqSetsTable
            .select(SeqSetsTable.seqSetVersion.max())
            .where { SeqSetsTable.seqSetId eq seqSetId and (SeqSetsTable.createdBy eq username) }
            .firstOrNull()
            ?.get(SeqSetsTable.seqSetVersion.max())
            as Long?

        if (maxVersion == null) {
            throw NotFoundException("SeqSet $seqSetId does not exist")
        }

        validateUpdateSeqSetHasChanges(
            oldSeqSet = getSeqSet(seqSetId, maxVersion).first(),
            oldSeqSetRecords = getSeqSetRecords(seqSetId, maxVersion),
            newSeqSetName = seqSetName,
            newSeqSetRecords = seqSetRecords,
            newSeqSetDescription = seqSetDescription,
        )

        val newVersion = maxVersion + 1

        val insertedSet = SeqSetsTable
            .insert {
                it[SeqSetsTable.seqSetId] = seqSetId
                it[SeqSetsTable.name] = seqSetName
                it[SeqSetsTable.description] = seqSetDescription ?: ""
                it[SeqSetsTable.seqSetVersion] = newVersion
                it[SeqSetsTable.createdAt] = dateProvider.getCurrentDateTime()
                it[SeqSetsTable.createdBy] = username
            }

        for (record in seqSetRecords) {
            val existingRecord = SeqSetRecordsTable
                .selectAll()
                .where {
                    (SeqSetRecordsTable.accession eq record.accession) and
                        (SeqSetRecordsTable.isFocal eq record.isFocal)
                }
                .singleOrNull()

            val seqSetRecordId = if (existingRecord == null) {
                val insertedRecord = SeqSetRecordsTable
                    .insert {
                        it[SeqSetRecordsTable.accession] = record.accession
                        it[SeqSetRecordsTable.type] = record.type
                        it[SeqSetRecordsTable.isFocal] = record.isFocal
                    }
                insertedRecord[SeqSetRecordsTable.seqSetRecordId]
            } else {
                existingRecord[SeqSetRecordsTable.seqSetRecordId]
            }

            SeqSetToRecordsTable
                .insert {
                    it[SeqSetToRecordsTable.seqSetVersion] = newVersion
                    it[SeqSetToRecordsTable.seqSetId] = insertedSet[SeqSetsTable.seqSetId]
                    it[SeqSetToRecordsTable.seqSetRecordId] = seqSetRecordId
                }
        }

        return ResponseSeqSet(
            insertedSet[SeqSetsTable.seqSetId],
            insertedSet[SeqSetsTable.seqSetVersion],
        )
    }

    fun getSeqSet(seqSetId: String, version: Long?): List<SeqSet> {
        log.info { "Get seqSet $seqSetId, version $version" }

        val query = SeqSetsTable
            .selectAll()
            .where { SeqSetsTable.seqSetId eq seqSetId }

        if (version != null) {
            query.andWhere { SeqSetsTable.seqSetVersion eq version }
        }

        if (query.empty()) {
            throw NotFoundException("SeqSet $seqSetId, version $version does not exist")
        }

        return query.map { it.toSeqSet() }
    }

    fun getSeqSetRecords(seqSetId: String, version: Long?): List<SeqSetRecord> {
        log.info { "Get seqSet records for seqSet $seqSetId, version $version" }

        var selectedVersion = version

        if (selectedVersion == null) {
            selectedVersion = SeqSetsTable
                .select(SeqSetsTable.seqSetVersion.max())
                .where { SeqSetsTable.seqSetId eq seqSetId }
                .singleOrNull()?.get(SeqSetsTable.seqSetVersion)
        }
        if (selectedVersion == null) {
            throw NotFoundException("SeqSet $seqSetId does not exist")
        }

        if (SeqSetToRecordsTable
                .selectAll().where {
                    (SeqSetToRecordsTable.seqSetId eq seqSetId) and
                        (SeqSetToRecordsTable.seqSetVersion eq selectedVersion)
                }
                .empty()
        ) {
            throw NotFoundException("SeqSet $seqSetId, version $selectedVersion does not exist")
        }

        val selectedSeqSetRecords = SeqSetToRecordsTable
            .innerJoin(SeqSetRecordsTable)
            .selectAll()
            .where {
                (SeqSetToRecordsTable.seqSetId eq seqSetId) and
                    (SeqSetToRecordsTable.seqSetVersion eq selectedVersion)
            }
            .map {
                SeqSetRecord(
                    it[SeqSetRecordsTable.seqSetRecordId],
                    it[SeqSetRecordsTable.accession],
                    it[SeqSetRecordsTable.type],
                    it[SeqSetRecordsTable.isFocal],
                )
            }

        return selectedSeqSetRecords
    }

    fun getSeqSets(authenticatedUser: AuthenticatedUser): List<SeqSet> {
        val username = authenticatedUser.username
        log.info { "Get seqSets for user $username" }

        val selectedSeqSets = SeqSetsTable
            .selectAll()
            .where { SeqSetsTable.createdBy eq username }

        return selectedSeqSets.map { it.toSeqSet() }
    }

    fun deleteSeqSet(authenticatedUser: AuthenticatedUser, seqSetId: String, version: Long) {
        val username = authenticatedUser.username
        log.info { "Delete seqSet $seqSetId, version $version, user $username" }

        val seqSetDOI = SeqSetsTable
            .selectAll()
            .where {
                (SeqSetsTable.seqSetId eq seqSetId) and (SeqSetsTable.seqSetVersion eq version) and
                    (SeqSetsTable.createdBy eq username)
            }
            .singleOrNull()
            ?.get(SeqSetsTable.seqSetDOI)

        if (seqSetDOI != null) {
            throw UnprocessableEntityException("SeqSet $seqSetId, version $version has a DOI and cannot be deleted")
        }

        SeqSetsTable.deleteWhere {
            (SeqSetsTable.seqSetId eq seqSetId) and
                (SeqSetsTable.seqSetVersion eq version) and
                (SeqSetsTable.createdBy eq username)
        }
    }

    fun validateCreateSeqSetDOI(username: String, seqSetId: String, version: Long) {
        log.info { "Validate create DOI for seqSet $seqSetId, version $version, user $username" }

        if (SeqSetsTable
                .selectAll()
                .where {
                    (SeqSetsTable.seqSetId eq seqSetId) and (SeqSetsTable.seqSetVersion eq version) and
                        (SeqSetsTable.createdBy eq username)
                }
                .empty()
        ) {
            throw NotFoundException("SeqSet $seqSetId, version $version does not exist")
        }

        val now = dateProvider.getCurrentInstant()
        val sevenDaysAgo = now.minus(7, DateTimeUnit.DAY, DateProvider.timeZone).toLocalDateTime(DateProvider.timeZone)
        val count = SeqSetsTable
            .selectAll()
            .where {
                (SeqSetsTable.createdBy eq username) and (SeqSetsTable.createdAt greaterEq sevenDaysAgo) and
                    (SeqSetsTable.seqSetDOI neq "")
            }
            .count()
        if (count >= SeqSetCitationsConstants.DOI_WEEKLY_RATE_LIMIT) {
            throw UnprocessableEntityException(
                "User exceeded limit of ${SeqSetCitationsConstants.DOI_WEEKLY_RATE_LIMIT} DOIs created per week.",
            )
        }
    }

    fun createSeqSetDOI(authenticatedUser: AuthenticatedUser, seqSetId: String, version: Long): ResponseSeqSet {
        val username = authenticatedUser.username
        log.info { "Create DOI for seqSet $seqSetId, version $version, user $username" }

        if (!crossRefService.isActive) {
            throw ForbiddenException("The crossref service is not active, so creating DOIs is forbidden.")
        }

        if (!crossRefService.isWriteEnabled) {
            throw ForbiddenException("The crossref service is not write-enabled, so creating DOIs is forbidden.")
        }

        validateCreateSeqSetDOI(username, seqSetId, version)

        val doiPrefix = crossRefService.doiPrefix ?: "no-prefix-configured"
        val seqSetDOI = "$doiPrefix/$seqSetId.$version"

        val seqSet = getSeqSet(seqSetId, version)

        SeqSetsTable.update(
            {
                (SeqSetsTable.seqSetId eq seqSetId) and
                    (SeqSetsTable.seqSetVersion eq version) and
                    (SeqSetsTable.createdBy eq username)
            },
        ) {
            it[SeqSetsTable.seqSetDOI] = seqSetDOI
        }

        val crossRefXml = crossRefService.generateCrossRefXML(
            DoiEntry(
                LocalDate.now(),
                "SeqSet: ${seqSet[0].name}",
                seqSetDOI,
                "/seqsets/$seqSetId.$version",
                null,
            ),
        )
        crossRefService.postCrossRefXML(crossRefXml)
        log.info { "Successfully created DOI $seqSetDOI for seqSet $seqSetId, version $version, user $username" }

        return ResponseSeqSet(
            seqSetId,
            version,
        )
    }

    fun updateCitationSourcesFromCrossRef(citationSources: Set<SeqSetCitationSource>): CitationSourcesUpdateResult {
        // Map of database seqSet DOIs to their ID and version
        val doiToSeqSet = SeqSetsTable
            .select(SeqSetsTable.seqSetId, SeqSetsTable.seqSetVersion, SeqSetsTable.seqSetDOI)
            .where { SeqSetsTable.seqSetDOI.isNotNull() }
            .associate { it[SeqSetsTable.seqSetDOI]!! to (it[SeqSetsTable.seqSetId] to it[SeqSetsTable.seqSetVersion]) }

        // Citation sources matched with at least one seqSet present in the database
        val matchedSources = citationSources.filter { source ->
            source.seqSetDOIs.any { it in doiToSeqSet }
        }

        // Map of matched sources to their seqSet DOIs
        val matchedSourceToSeqSetDOIs = matchedSources.associate { it.source.sourceDOI to it.seqSetDOIs }

        // Upsert matched citation sources based on their DOI
        // Build entries for the join table from the returned source DOIs + primary keys
        val joinEntries = SeqSetCitationSourceTable
            .batchUpsert(matchedSources, SeqSetCitationSourceTable.sourceDOI) {
                this[SeqSetCitationSourceTable.sourceDOI] = it.source.sourceDOI
                this[SeqSetCitationSourceTable.origin] = CitationOrigin.CROSSREF
                this[SeqSetCitationSourceTable.title] = it.source.title
                this[SeqSetCitationSourceTable.year] = it.source.year
                this[SeqSetCitationSourceTable.contributors] = it.source.contributors
                this[SeqSetCitationSourceTable.journal] = it.source.journal
            }
            .flatMap { result ->
                val citationSourceId = result[SeqSetCitationSourceTable.citationSourceId]
                val seqSetDOIs = matchedSourceToSeqSetDOIs.getValue(result[SeqSetCitationSourceTable.sourceDOI])
                seqSetDOIs.mapNotNull { doi ->
                    doiToSeqSet[doi]?.let { (seqSetId, version) ->
                        SeqSetToCitationSourceEntry(citationSourceId, seqSetId, version)
                    }
                }
            }

        // Insert entries in the join table
        SeqSetToCitationSourceTable.batchInsert(
            joinEntries,
            ignore = true,
        ) { (citationSourceId, seqSetId, seqSetVersion) ->
            this[SeqSetToCitationSourceTable.citationSourceId] = citationSourceId
            this[SeqSetToCitationSourceTable.seqSetId] = seqSetId
            this[SeqSetToCitationSourceTable.seqSetVersion] = seqSetVersion
        }

        // Return upserted citation sources
        return CitationSourcesUpdateResult(matchedSources.mapTo(mutableSetOf()) { it.source.sourceDOI })
    }

    fun getSeqSetCitations(seqSetId: String, version: Long): List<SeqSetCitation> {
        log.info { "Get seqSet citations for seqSetId $seqSetId, version $version" }

        val seqSet = (
            SeqSetsTable
                .select(SeqSetsTable.seqSetId, SeqSetsTable.seqSetVersion)
                .where { (SeqSetsTable.seqSetId eq seqSetId) and (SeqSetsTable.seqSetVersion eq version) }
                .singleOrNull()
                ?: throw NotFoundException("SeqSet $seqSetId, version $version does not exist")
            )

        return SeqSetToCitationSourceTable.innerJoin(
            SeqSetCitationSourceTable,
        ).selectAll()
            .where {
                (SeqSetToCitationSourceTable.seqSetId eq seqSet[SeqSetsTable.seqSetId]) and
                    (SeqSetToCitationSourceTable.seqSetVersion eq seqSet[SeqSetsTable.seqSetVersion])
            }.orderBy(SeqSetCitationSourceTable.year to SortOrder.DESC).map {
                SeqSetCitation(
                    source = CitationSource(
                        it[SeqSetCitationSourceTable.sourceDOI],
                        it[SeqSetCitationSourceTable.title],
                        it[SeqSetCitationSourceTable.year],
                        it[SeqSetCitationSourceTable.contributors],
                        it[SeqSetCitationSourceTable.journal],
                    ),
                )
            }
    }

    fun getSequenceCitations(accession: String, version: Long?): List<SequenceCitation> {
        log.info { "Get sequence citations for accession ${accession}${version?.let { ", version $it" } ?: ""}" }

        // If a version is provided, return citations pinned to that exact accession and version
        // Otherwise, return all citations for the accession, regardless of version, including unversioned
        // NOTE: In this table, the 'accession' column can contain both accessions and accessionVersions
        val accessionQuery: Op<Boolean> = if (version != null) {
            val accessionVersion = AccessionVersion(accession, version)
            SeqSetRecordsTable.accession eq accessionVersion.displayAccessionVersion()
        } else {
            (SeqSetRecordsTable.accession eq accession) or
                (SeqSetRecordsTable.accession like (LikePattern.ofLiteral("$accession.") + "%"))
        }

        return SeqSetCitationSourceTable.innerJoin(
            SeqSetToCitationSourceTable,
        ).innerJoin(
            SeqSetsTable,
        ).innerJoin(SeqSetToRecordsTable).innerJoin(SeqSetRecordsTable).selectAll()
            .where { accessionQuery }
            .orderBy(
                SeqSetCitationSourceTable.year to SortOrder.DESC,
                SeqSetCitationSourceTable.citationSourceId to SortOrder.DESC,
            )
            .groupBy { it[SeqSetCitationSourceTable.citationSourceId] }
            .map { (_, rows) ->
                val first = rows.first()
                SequenceCitation(
                    source = CitationSource(
                        sourceDOI = first[SeqSetCitationSourceTable.sourceDOI],
                        title = first[SeqSetCitationSourceTable.title],
                        year = first[SeqSetCitationSourceTable.year],
                        contributors = first[SeqSetCitationSourceTable.contributors],
                        journal = first[SeqSetCitationSourceTable.journal],
                    ),
                    seqSets = rows.map {
                        SeqSetCitingSequence(
                            seqSetAccessionVersion = AccessionVersion(
                                it[SeqSetsTable.seqSetId],
                                it[SeqSetsTable.seqSetVersion],
                            ).displayAccessionVersion(),
                            sequenceAccession = it[SeqSetRecordsTable.accession],
                        )
                    },
                )
            }
    }

    fun getAllSeqSetCitations(): List<AdminSeqSetCitation> {
        log.info { "Get all seqSet citations" }

        return SeqSetCitationSourceTable.innerJoin(
            SeqSetToCitationSourceTable,
        ).innerJoin(
            SeqSetsTable,
        ).selectAll()
            .orderBy(
                SeqSetCitationSourceTable.year to SortOrder.DESC,
                SeqSetCitationSourceTable.citationSourceId to SortOrder.DESC,
            )
            .groupBy { it[SeqSetCitationSourceTable.citationSourceId] }
            .map { (_, rows) ->
                val first = rows.first()
                AdminSeqSetCitation(
                    source = CitationSource(
                        sourceDOI = first[SeqSetCitationSourceTable.sourceDOI],
                        title = first[SeqSetCitationSourceTable.title],
                        year = first[SeqSetCitationSourceTable.year],
                        contributors = first[SeqSetCitationSourceTable.contributors],
                        journal = first[SeqSetCitationSourceTable.journal],
                    ),
                    seqSets = rows.map { it.toSeqSet() },
                    origin = first[SeqSetCitationSourceTable.origin],
                )
            }
    }

    fun addCuratedCitation(request: AddSeqSetCitationRequest): AdminSeqSetCitation {
        log.info { "Add curated seqSet citation ${request.source.sourceDOI}" }

        if (request.source.sourceDOI.isBlank()) {
            throw UnprocessableEntityException("Citation source DOI must not be empty")
        }
        if (request.source.title.isBlank()) {
            throw UnprocessableEntityException("Citation source title must not be empty")
        }
        if (request.seqSetAccessionVersions.isEmpty()) {
            throw UnprocessableEntityException("At least one cited SeqSet must be provided")
        }

        val existingOrigin = SeqSetCitationSourceTable
            .select(SeqSetCitationSourceTable.origin)
            .where { SeqSetCitationSourceTable.sourceDOI eq request.source.sourceDOI }
            .singleOrNull()
            ?.get(SeqSetCitationSourceTable.origin)
        if (existingOrigin == CitationOrigin.CROSSREF) {
            throw UnprocessableEntityException(
                "Citation source ${request.source.sourceDOI} was discovered via CrossRef and cannot be modified manually",
            )
        }

        val accessionVersions = request.seqSetAccessionVersions.map { AccessionVersion.fromString(it) }

        val existingSeqSets = SeqSetsTable
            .select(SeqSetsTable.seqSetId, SeqSetsTable.seqSetVersion, SeqSetsTable.name, SeqSetsTable.seqSetDOI)
            .where {
                Pair(SeqSetsTable.seqSetId, SeqSetsTable.seqSetVersion) inList
                    accessionVersions.map { it.accession to it.version }
            }
            .associateBy { AccessionVersion(it[SeqSetsTable.seqSetId], it[SeqSetsTable.seqSetVersion]) }

        val missing = accessionVersions.filterNot { it in existingSeqSets }
        if (missing.isNotEmpty()) {
            throw NotFoundException(
                "The following SeqSets do not exist: ${missing.joinToString(", ") { it.displayAccessionVersion() }}",
            )
        }

        val citationSourceId = SeqSetCitationSourceTable
            .batchUpsert(listOf(request.source), SeqSetCitationSourceTable.sourceDOI) {
                this[SeqSetCitationSourceTable.sourceDOI] = it.sourceDOI
                this[SeqSetCitationSourceTable.origin] = CitationOrigin.CURATED
                this[SeqSetCitationSourceTable.title] = it.title
                this[SeqSetCitationSourceTable.year] = it.year
                this[SeqSetCitationSourceTable.contributors] = it.contributors
                this[SeqSetCitationSourceTable.journal] = it.journal
            }
            .single()[SeqSetCitationSourceTable.citationSourceId]

        SeqSetToCitationSourceTable.batchInsert(accessionVersions, ignore = true) { accessionVersion ->
            this[SeqSetToCitationSourceTable.citationSourceId] = citationSourceId
            this[SeqSetToCitationSourceTable.seqSetId] = accessionVersion.accession
            this[SeqSetToCitationSourceTable.seqSetVersion] = accessionVersion.version
        }

        val linkedSeqSets = SeqSetToCitationSourceTable
            .innerJoin(SeqSetsTable)
            .selectAll()
            .where { SeqSetToCitationSourceTable.citationSourceId eq citationSourceId }
            .map { it.toSeqSet() }

        return AdminSeqSetCitation(
            source = request.source,
            seqSets = linkedSeqSets,
            origin = CitationOrigin.CURATED,
        )
    }

    fun deleteCuratedCitation(sourceDOI: String) {
        log.info { "Delete curated seqSet citation $sourceDOI" }

        val citationSource = SeqSetCitationSourceTable
            .selectAll()
            .where { SeqSetCitationSourceTable.sourceDOI eq sourceDOI }
            .singleOrNull()
            ?: throw NotFoundException("Citation source $sourceDOI does not exist")

        if (citationSource[SeqSetCitationSourceTable.origin] != CitationOrigin.CURATED) {
            throw UnprocessableEntityException(
                "Citation source $sourceDOI was not manually curated and cannot be deleted",
            )
        }

        SeqSetCitationSourceTable.deleteWhere { SeqSetCitationSourceTable.sourceDOI eq sourceDOI }
    }

    fun validateSeqSetRecords(seqSetRecords: List<SubmittedSeqSetRecord>) {
        if (seqSetRecords.isEmpty()) {
            throw UnprocessableEntityException(
                "No accessions provided. In order to create a SeqSet you must provide at least one accession.",
            )
        }

        val uniqueAccessions = seqSetRecords.map { it.accession }.toSet()
        if (uniqueAccessions.size != seqSetRecords.size) {
            val duplicateAccessions = seqSetRecords
                .groupingBy { it.accession }
                .eachCount()
                .filter { it.value > 1 }
                .keys
            throw UnprocessableEntityException(
                "SeqSet must not contain duplicate accessions. " +
                    "The following accessions appear at least twice: ${duplicateAccessions.joinToString(", ")}",
            )
        }

        // If users don't provide version, we require that at least version 1 is released
        // If we check the latest version instead, that accessionVersion might exist (unknown to user) but not be released yet
        val accessionsWithoutVersionsWithV1 = seqSetRecords
            .filter { !it.accession.contains('.') }
            .map { AccessionVersion(it.accession, 1) }

        for (chunk in accessionsWithoutVersionsWithV1.chunked(1000)) {
            accessionPreconditionValidator.validate {
                thatAccessionVersionsExist(chunk)
                    .andThatSequenceEntriesAreInStates(listOf(APPROVED_FOR_RELEASE))
            }
        }
        val accessionsWithVersions = mutableListOf<AccessionVersion>()
        for (record in seqSetRecords.filter { it.accession.contains('.') }) {
            accessionsWithVersions.add(AccessionVersion.fromString(record.accession))
        }

        for (chunk in accessionsWithVersions.chunked(1000)) {
            accessionPreconditionValidator.validate {
                thatAccessionVersionsExist(chunk)
                    .andThatSequenceEntriesAreInStates(listOf(APPROVED_FOR_RELEASE))
            }
        }
    }

    fun validateSeqSetName(name: String) {
        if (name.isBlank()) {
            throw UnprocessableEntityException("SeqSet name must not be empty")
        }
    }

    fun validateUpdateSeqSetHasChanges(
        oldSeqSet: SeqSet,
        oldSeqSetRecords: List<SeqSetRecord>,
        newSeqSetName: String,
        newSeqSetRecords: List<SubmittedSeqSetRecord>,
        newSeqSetDescription: String?,
    ) {
        val oldSubmittedSeqSetRecords = oldSeqSetRecords.map {
            SubmittedSeqSetRecord(
                it.accession,
                it.type,
                it.isFocal,
            )
        }

        if (oldSeqSet.name == newSeqSetName &&
            oldSeqSet.description == newSeqSetDescription &&
            oldSubmittedSeqSetRecords == newSeqSetRecords
        ) {
            throw UnprocessableEntityException("SeqSet update must contain at least one change")
        }
    }

    fun transformKeycloakUserToAuthorProfile(keycloakUser: UserRepresentation): AuthorProfile {
        val emailDomain = keycloakUser.email?.substringAfterLast("@") ?: ""
        return AuthorProfile(
            keycloakUser.username,
            keycloakUser.firstName,
            keycloakUser.lastName,
            emailDomain,
            keycloakUser.attributes["university"]?.firstOrNull(),
        )
    }
}
