// IntelligentExtractionController.kt
@Tag(name = "IntelligentExtraction", description = "智能数据提取")
@RestController
@RequestMapping("/api/intelligent-extraction/pdfs")
class IntelligentExtractionController(
    private val pdfExtractionService: PdfExtractionService,
) {
    @Operation(summary = "上传 PDF 并解析")
    @PostMapping(consumes = [MediaType.MULTIPART_FORM_DATA_VALUE])
    @ResponseStatus(HttpStatus.CREATED)
    fun upload(
        @AuthenticationPrincipal principal: IdentityPrincipal,
        @RequestPart("file") file: MultipartFile,
    ): PdfUploadResponse =
        pdfExtractionService
            .upload(
                UploadPdfCommand(
                    fileName = file.originalFilename ?: "upload.pdf",
                    bytes = file.bytes,
                    creatorId = principal.id,
                ),
            ).toResponse()

    @Operation(summary = "已解析 PDF 列表")
    @GetMapping
    fun listDocuments(
        @Valid @ParameterObject request: PdfDocumentPageRequest,
    ): PageResult<PdfDocumentSummaryView> =
        pdfExtractionService
            .listDocuments(request.toQuery())
            .toSummaryResponsePage()

    @Operation(summary = "查询 PDF 全部分页")
    @GetMapping("/{id}/pages")
    fun listPages(@PathVariable id: Long): ListResult<PdfExtractionPageView> =
        ListResult(items = pdfExtractionService.listPages(id).map { it.toResponse() })

    @Operation(summary = "抽取 PDF 分页结构化数据")
    @PostMapping("/{id}/pages/{page_index}/extract-data")
    @ResponseStatus(HttpStatus.ACCEPTED)
    fun extractPageData(
        @AuthenticationPrincipal principal: IdentityPrincipal,
        @PathVariable id: Long,
        @PathVariable("page_index") pageIndex: Int,
    ): PdfPageDataExtractionStatusItemView =
        pdfExtractionService
            .submitPageDataExtraction(id, pageIndex, principal.id)
            .toResponse()

    @Operation(summary = "查询 PDF 分页结构化抽取暂存数据")
    @GetMapping("/{id}/pages/{page_index}/staging-sample-data")
    fun listStagingSampleData(
        @PathVariable id: Long,
        @PathVariable("page_index") pageIndex: Int,
    ): ListResult<PdfExtractionSampleDataStagingView> =
        ListResult(items = pdfExtractionService.listStagingSampleData(id, pageIndex).map { it.toResponse() })

    @Operation(summary = "将 PDF 暂存结构化数据写入正式样本数据")
    @PostMapping("/{id}/staging-sample-data/commit")
    @ResponseStatus(HttpStatus.NO_CONTENT)
    fun commitStagingSampleData(
        @AuthenticationPrincipal principal: IdentityPrincipal,
        @PathVariable id: Long,
    ) = pdfExtractionService.commitStagingSampleData(id, principal.id)

    @Operation(summary = "绑定 PDF 解析模板")
    @PutMapping("/{id}/template")
    fun bindTemplate(
        @PathVariable id: Long,
        @Valid @RequestBody request: PdfDocumentTemplateBindRequest,
    ): PdfDocumentSummaryView =
        pdfExtractionService.bindTemplate(id, request.toCommand()).toResponse()

    @Operation(summary = "审核 PDF 文档")
    @PutMapping("/{id}/review")
    fun reviewDocument(@PathVariable id: Long): PdfDocumentSummaryView =
        pdfExtractionService.reviewDocument(id).toResponse()
}

// PdfExtractionService.kt
fun upload(command: UploadPdfCommand): PdfUploadResult {
    val fileName = command.fileName
    validatePdfFileName(fileName)

    val pdfBytes = command.bytes
    val md5 = fileService.md5(pdfBytes)
    val existingDocument = documentService.findDocumentByMd5(md5)
    if (existingDocument != null) {
        val pages = documentService.findPagesByDocumentId(existingDocument.id)
        val localFilesComplete =
            isLocalRecognitionComplete(
                md5 = existingDocument.md5,
                status = existingDocument.status,
                pageIndexes = pages.map { it.pageIndex },
            )

        if (!localFilesComplete) {
            val pdfPath = fileService.saveUploadedPdf(md5, pdfBytes)
            stagingSampleDataService.deleteDocumentData(existingDocument.id)
            documentService.deletePagesByDocumentId(existingDocument.id)
            documentService.updateStatus(existingDocument.id, PdfExtractionStatus.UPLOADED)
            asyncService.submitOcrRecognition(existingDocument.id, existingDocument.fileName, pdfPath, pdfBytes)
        }

        return PdfUploadResult(
            id = existingDocument.id,
            fileName = existingDocument.fileName,
            pageCount = if (localFilesComplete) pages.size else 0,
        )
    }

    val pdfPath = fileService.saveUploadedPdf(md5, pdfBytes)
    val document = persistenceService.createUploadedDocument(fileName, md5, command.creatorId)
    asyncService.submitOcrRecognition(document.id, fileName, pdfPath, pdfBytes)

    return PdfUploadResult(
        id = document.id,
        fileName = document.fileName,
        pageCount = 0,
    )
}

private fun validatePdfFileName(fileName: String) {
    if (!fileName.lowercase().endsWith(".pdf")) {
        throw InvalidRequestException("error.pdf_extraction.pdf_only")
    }
    if (fileName.length > 255) {
        throw InvalidRequestException("error.pdf_extraction.file_name_too_long")
    }
}

// PdfExtractionService.kt
private fun isLocalRecognitionComplete(
    md5: String,
    status: PdfExtractionStatus,
    pageIndexes: List<Int>,
): Boolean {
    if (!fileService.uploadedPdfExists(md5)) {
        return false
    }
    if (status == PdfExtractionStatus.UPLOADED) {
        return true
    }
    if (status == PdfExtractionStatus.OCR_FAILED || pageIndexes.isEmpty()) {
        return false
    }
    return pageIndexes.all { pageIndex -> fileService.pageRecognitionFilesExist(md5, pageIndex) }
}

// PdfRecognitionFileService.kt
@Service
class PdfRecognitionFileService(
    private val store: LocalPdfRecognitionStore,
) {
    fun md5(bytes: ByteArray): String = store.md5(bytes)

    fun saveUploadedPdf(md5: String, bytes: ByteArray): Path =
        store.saveUploadedPdf(md5, bytes)

    fun pageRecognitionFilesExist(md5: String, pageIndex: Int): Boolean =
        store.pageRecognitionFilesExist(md5, pageIndex)

    fun readPageImageBytes(md5: String, pageIndex: Int): ByteArray =
        store.readPageImageBytes(md5, pageIndex)

    fun readPageMarkdownText(md5: String, pageIndex: Int): String =
        store.readPageMarkdownText(md5, pageIndex)

    fun writePageMarkdown(md5: String, pageIndex: Int, content: String) {
        store.writePageMarkdown(md5, pageIndex, content)
    }
}

// PdfExtractionAsyncService.kt
fun submitOcrRecognition(
    documentId: Long,
    fileName: String,
    pdfPath: Path,
    pdfBytes: ByteArray,
) {
    taskExecutor.execute {
        runOcrRecognition(documentId, fileName, pdfPath, pdfBytes)
    }
}

private fun runOcrRecognition(
    documentId: Long,
    fileName: String,
    pdfPath: Path,
    pdfBytes: ByteArray,
) {
    try {
        val parseResult = pdfOcrService.parse(fileName, pdfPath, pdfBytes)
        persistenceService.saveOcrSucceededPages(documentId, parseResult.pages)
    } catch (e: Exception) {
        log.error(e) { "PDF OCR task failed: documentId=$documentId, fileName=$fileName" }
        persistenceService.markOcrFailed(documentId)
    }
}

// PdfOcrService.kt
data class PdfOcrResult(
    val pages: List<PdfExtractionPageInput>,
)

@Service
class PdfOcrService(
    private val paddleOcrGateway: PaddleOcrGateway,
) {
    fun parse(
        fileName: String,
        pdfPath: Path,
        pdfBytes: ByteArray,
    ): PdfOcrResult =
        PdfOcrResult(
            pages = paddleOcrGateway.parse(fileName, pdfPath, pdfBytes).pages,
        )
}

// PdfDocumentService.kt
fun saveOcrSucceededPages(
    documentId: Long,
    pages: List<PdfExtractionPageInput>,
): PdfExtractionDocument {
    val document = documentRepository.updateStatus(documentId, PdfExtractionStatus.PENDING_REVIEW)
    pageRepository.replaceByDocumentId(document.id, pages)
    return document
}

// PdfExtractionService.kt
fun updatePage(
    documentId: Long,
    pageIndex: Int,
    command: UpdatePdfExtractionPageCommand,
): PdfExtractionPageResult {
    ensurePageIndexValid(pageIndex)
    validatePageUpdateCommand(command)

    val document = requireDocumentById(documentId)

    fileService.writePageMarkdown(document.md5, pageIndex, command.markdown)
    val existingPage =
        documentService.findPageByDocumentIdAndPageIndex(document.id, pageIndex)
            ?: throwPageNotFound(documentId, pageIndex)

    val mergedPageData =
        command.pageData
            .toDomain()
            .copy(ppOcrData = existingPage.pageData.ppOcrData)

    val page =
        documentService.updatePageData(document.id, pageIndex, mergedPageData)
            ?: throwPageNotFound(documentId, pageIndex)

    stagingSampleDataService.deletePageData(document.id, pageIndex)
    documentService.updatePageStatus(document.id, pageIndex, PdfPageDataExtractionStatus.NONE)
        ?: throwPageNotFound(documentId, pageIndex)
    documentService.touch(document.id)

    return toPageResult(documentService.findDocumentById(document.id) ?: document, page)
}

// PdfExtractionTemplateFactory.kt
@Component
class PdfExtractionTemplateFactory(
    private val templateService: IntelligentExtractionTemplateService,
    private val materialService: MaterialService,
    private val characteristicService: CharacteristicService,
    private val materialMetadataService: MaterialMetadataService,
) {
    fun create(templateId: Long): RuntimeExtractionTemplate {
        val template = templateService.requireById(templateId)
        val material = materialService.getById(template.materialId)
        val characteristic =
            characteristicService.getByIdAndMaterialId(template.characteristicId, template.materialId)

        if (characteristic.dataType != CharacteristicDataType.VARIABLE) {
            throw ApplicationException(
                "error.intelligent_extraction_template.characteristic_type_unsupported",
                httpStatus = HttpStatus.UNPROCESSABLE_ENTITY,
            )
        }

        return RuntimeExtractionTemplate(
            materialId = material.id,
            materialName = material.name,
            characteristicId = characteristic.id,
            characteristicName = characteristic.name,
            metadataMappings =
                template.metadataMappings.map { mapping ->
                    val metadata = materialMetadataService.requireById(mapping.metadataId)
                    RuntimeExtractionMetadataMapping(
                        metadataId = mapping.metadataId,
                        metadataName = metadata.name,
                        documentFieldName = mapping.matchValue.ifBlank { metadata.name },
                        remark = mapping.remark,
                    )
                },
            constraintText = template.constraintText,
        )
    }
}

// PdfExtractionService.kt
fun submitPageDataExtraction(
    documentId: Long,
    pageIndex: Int,
    creatorId: Long,
): PdfPageDataExtractionStatusItemResult {
    ensurePageIndexValid(pageIndex)

    val document = persistenceService.getDocumentForPageExtraction(documentId, pageIndex)
    val templateId =
        document.templateId
            ?: throw ApplicationException("error.pdf_extraction.template_required", httpStatus = HttpStatus.UNPROCESSABLE_ENTITY)

    val markdown = fileService.readPageMarkdownText(document.md5, pageIndex).trim()
    if (markdown.isBlank()) {
        throw ApplicationException(
            "error.pdf_extraction.page_markdown_empty",
            messageArgs = arrayOf<Any?>(documentId, pageIndex),
            httpStatus = HttpStatus.UNPROCESSABLE_ENTITY,
        )
    }

    val template = templateFactory.create(templateId)
    val page = asyncService.submitDataExtraction(documentId, pageIndex, markdown, template, creatorId)
    val stagingCount = stagingSampleDataService.countGroupedByPageIndex(documentId)[pageIndex] ?: 0

    return PdfPageDataExtractionStatusItemResult(
        pageIndex = page.pageIndex,
        status = page.status,
        stagingCount = stagingCount,
        updatedAt = page.updatedAt,
    )
}

// PdfExtractionPersistenceService.kt
fun getDocumentForPageExtraction(
    documentId: Long,
    pageIndex: Int,
): PdfExtractionDocument {
    val document = requireDocumentById(documentId)

    if (
        document.status != PdfExtractionStatus.PENDING_REVIEW &&
        document.status != PdfExtractionStatus.REVIEWED &&
        document.status != PdfExtractionStatus.COMMITTED
    ) {
        throw ConflictException("error.pdf_extraction.status_not_extractable")
    }
    if (document.templateId == null) {
        throw ApplicationException("error.pdf_extraction.template_required", httpStatus = HttpStatus.UNPROCESSABLE_ENTITY)
    }

    requirePage(document.id, pageIndex)
    return document
}

// PdfExtractionAsyncService.kt
fun submitDataExtraction(
    documentId: Long,
    pageIndex: Int,
    markdown: String,
    template: RuntimeExtractionTemplate,
    creatorId: Long,
): PdfExtractionPage {
    val page =
        persistenceService.markPageDataExtractionStatus(
            documentId,
            pageIndex,
            PdfPageDataExtractionStatus.RUNNING,
        )

    taskExecutor.execute {
        runDataExtraction(documentId, pageIndex, markdown, template, creatorId)
    }

    return page
}

private fun runDataExtraction(
    documentId: Long,
    pageIndex: Int,
    markdown: String,
    template: RuntimeExtractionTemplate,
    creatorId: Long,
) {
    try {
        val result =
            runBlocking {
                sampleExtractionService.extractByTemplate(markdown, template)
            }
        persistenceService.replaceStagingSampleData(
            documentId,
            pageIndex,
            result.sampleDataList,
            creatorId,
        )
    } catch (e: Exception) {
        log.error(e) { "PDF page data extraction task failed: documentId=$documentId, pageIndex=$pageIndex" }
        runCatching {
            persistenceService.markPageDataExtractionStatus(documentId, pageIndex, PdfPageDataExtractionStatus.FAILED)
        }
    }
}

// SampleExtractionService.kt
@Service
class SampleExtractionService(
    private val documentExtractionService: DocumentExtractionService,
) {
    suspend fun extractByTemplate(
        markdownContent: String,
        template: RuntimeExtractionTemplate,
    ): SampleExtractionResult {
        val normalizedContent = markdownContent.trim()
        if (normalizedContent.isBlank()) {
            throw ApplicationException(
                "error.pdf_extraction.page_markdown_empty",
                httpStatus = HttpStatus.UNPROCESSABLE_ENTITY,
            )
        }
        return documentExtractionService.extractByTemplate(normalizedContent, template)
    }

    fun buildPromptPreview(
        template: RuntimeExtractionTemplate,
        markdownPlaceholder: String,
    ): String = DocumentExtractionPrompts.buildIntelligentExtractionInput(template, markdownPlaceholder)
}

// DocumentExtractionPrompts.kt
val intelligentExtractionPrompt: String by lazy {
    """
    你是一个结构化数据抽取助手。

    任务目标：
    根据给定的 OCR 文本内容，结合预先定义的智能提取模板，抽取出用于系统入库的结构化数据。

    输出要求：
    1. 只输出 JSON 对象，不要输出解释说明，不要使用 Markdown 代码块。
    2. 顶层只包含 sampleDataList 字段，不要输出其他字段。
    3. sampleDataList 必须是数组，没有识别到样本数据时输出空数组 []。
    4. sampleDataList 中每一项都必须带上固定的 materialId，值直接使用模板传入的物料 ID。
    5. sampleDataList 中每一项都必须带上固定的 characteristicId，值直接使用模板传入的特性 ID。
    6. sampleDataList 中每一项都必须包含 sampleTime，表示采样时间，需要将抽取到的时间格式
       拼接时区偏移转换为 ISO 8601 的东八区时间字符串，如"2026-05-27T18:39:00+08:00"。
    7. metadata 中 name 必须使用 metadataName，value 来自 OCR 文本中 documentFieldName 对应的识别结果值。
    8. 如果某个样本信息没有识别到值，则在 metadata 中不要输出对应项。
    9. 可以参考 remark 和额外约束辅助判断字段含义，但不要凭空编造数据。
    10. 所有字段名必须严格保持约定，不要新增字段，不要修改字段名。
    11. 不要臆造数据，只提取 OCR 文本中能够明确识别的信息。
    12. 如果某个测点单元格为空、明显被删除、无法辨认，则跳过该测点，不要臆造数值。
    13. 抽取数据时要注意保持每一条数据的顺序不要变。
    14. sampleData 对应每一条记录，其中 variableValue 对应样本值、测点值、采样值等。
    """.trimIndent()
}

// DocumentExtractionPrompts.kt
fun buildIntelligentExtractionInput(
    template: RuntimeExtractionTemplate,
    markdown: String,
): String =
    buildString {
        appendLine("请根据如下提示从 OCR 识别的 markdown 中提取结构化数据：")
        appendLine()
        appendLine("### 已知模板信息：")
        appendLine(" - 物料 ID：${template.materialId}")
        appendLine(" - 物料名称：${template.materialName}")
        appendLine(" - 质量特性 ID：${template.characteristicId}")
        appendLine(" - 质量特性名称：${template.characteristicName}")
        appendLine(" - 样本信息映射列表：")
        template.metadataMappings.forEachIndexed { index, mapping ->
            appendLine(
                "   ${index + 1}. metadataName=${mapping.metadataName}; " +
                    "documentFieldName=${mapping.documentFieldName}; remark=${mapping.remark}",
            )
        }
        appendLine()
        appendLine("### 额外约束：")
        appendLine(template.constraintText.ifBlank { "无" })
        appendLine()
        appendLine("### OCR Markdown 文本如下：")
        appendLine(markdown)
    }

// LlmSampleExtractionResult.kt
@Serializable
@LLMDescription("智能数据提取统一输出结果。")
data class LlmSampleExtractionResult(
    @property:LLMDescription("样本数据列表。没有识别到样本数据时输出空数组。")
    val sampleDataList: List<LlmExtractionSampleDataItem> = emptyList(),
)

@Serializable
@LLMDescription("单条样本数据，用于承载后续入库所需的核心字段。")
data class LlmExtractionSampleDataItem(
    @property:LLMDescription("物料 ID，直接使用模板中的物料 ID。")
    val materialId: Long,
    @property:LLMDescription("质量特性 ID，直接使用模板中的质量特性 ID。")
    val characteristicId: Long,
    @property:LLMDescription("采样时间，必须为 ISO 8601 时间字符串。")
    val sampleTime: String,
    @property:LLMDescription("计量型采样值。")
    val variableValue: Double? = null,
    @property:LLMDescription("样本信息列表。")
    val metadata: List<LlmExtractionMetadataValueItem> = emptyList(),
)

// PdfExtractionPersistenceService.kt
fun replaceStagingSampleData(
    documentId: Long,
    pageIndex: Int,
    sampleDataList: List<IntelligentExtractionSampleDataItem>,
    creatorId: Long,
) {
    val page = requirePage(documentId, pageIndex)
    stagingSampleDataService.replacePageData(
        documentId = documentId,
        pageId = page.id,
        pageIndex = pageIndex,
        sampleDataList = sampleDataList,
        creatorId = creatorId,
    )
    sampleDataCommitService.commitExtractedSampleDataPage(documentId, pageIndex, creatorId)
    markPageDataExtractionStatus(documentId, pageIndex, PdfPageDataExtractionStatus.EXTRACTED)
}

// StagingSampleDataService.kt
@Service
class StagingSampleDataService(
    private val stagingSampleDataRepository: StagingSampleDataRepository,
) {
    fun replacePageData(
        documentId: Long,
        pageId: Long,
        pageIndex: Int,
        sampleDataList: List<IntelligentExtractionSampleDataItem>,
        creatorId: Long,
    ) {
        stagingSampleDataRepository.replacePageData(
            documentId = documentId,
            pageId = pageId,
            pageIndex = pageIndex,
            sampleDataList = sampleDataList,
            creatorId = creatorId,
        )
    }

    fun deletePageData(documentId: Long, pageIndex: Int) {
        stagingSampleDataRepository.deleteByDocumentIdAndPageIndex(documentId, pageIndex)
    }

    fun deleteDocumentData(documentId: Long) {
        stagingSampleDataRepository.deleteByDocumentId(documentId)
    }
}

// SampleDataCommitService.kt
@Transactional
fun commitStagingSampleData(
    documentId: Long,
    creatorId: Long,
) {
    val document =
        documentService.findDocumentById(documentId)
            ?: throw ResourceNotFoundException("error.pdf_extraction.document_not_found", arrayOf(documentId))

    if (!documentService.allPagesExtracted(documentId)) {
        throw InvalidRequestException("error.pdf_extraction.pages_not_all_extracted")
    }

    val stagingRows = stagingSampleDataRepository.listRecordsByDocumentId(documentId)
    if (stagingRows.isEmpty()) {
        throw ApplicationException("error.pdf_extraction.no_staging_sample_data", httpStatus = HttpStatus.UNPROCESSABLE_ENTITY)
    }

    stagingRows.forEach { validateStagingSampleData(it) }
    saveCommittedSampleData(document, stagingRows, creatorId)
    stagingSampleDataRepository.deleteByDocumentId(documentId)
    documentService.markCommitted(documentId)
}

// SampleDataCommitService.kt
@Transactional
fun commitExtractedSampleDataPage(
    documentId: Long,
    pageIndex: Int,
    creatorId: Long,
) {
    val document =
        documentService.findDocumentById(documentId)
            ?: throw ResourceNotFoundException("error.pdf_extraction.document_not_found", arrayOf(documentId))

    val committableRows =
        stagingSampleDataRepository
            .listRecordsByDocumentIdAndPageIndex(documentId, pageIndex)
            .filter { it.hasDetectionValue() }

    if (committableRows.isEmpty()) {
        return
    }

    committableRows.forEach { validateStagingSampleData(it) }
    saveCommittedSampleData(document, committableRows, creatorId)
    documentService.markCommitted(documentId)
}

// SampleDataCommitService.kt
private fun enrichCommittedMetadata(
    metadata: Map<String, String>,
    pdfFileName: String,
    pageIndex: Int,
): Map<String, String> =
    metadata.toMutableMap().apply {
        put(PDF_FILE_NAME_METADATA_KEY, pdfFileName)
        put(PDF_PAGE_INDEX_METADATA_KEY, pageIndex.toString())
    }

// CommittedSampleDataRepository.kt
@Repository
class CommittedSampleDataRepository {
    fun saveAll(
        rows: List<StagingSampleDataRecord>,
        creatorId: Long,
    ) {
        rows.forEach { staging ->
            SampleDataEntity.new {
                materialId = EntityID(staging.materialId, MaterialTable)
                characteristicId = EntityID(staging.characteristicId, CharacteristicTable)
                sampleTime = staging.sampleTime
                variableValue = staging.variableValue
                attributeValue = staging.attributeValue
                metadata = staging.metadata
                creator = UserEntity[creatorId]
            }
        }
    }
}

// PdfExtractionService.kt
fun getPageDataExtractionStatusSummary(documentId: Long): PdfPageDataExtractionStatusSummaryResult {
    val document = requireDocumentById(documentId)
    val pages = documentService.findPagesByDocumentId(documentId)
    val stagingCounts = stagingSampleDataService.countGroupedByPageIndex(documentId)
    val statusItems =
        pages.map { page ->
            PdfPageDataExtractionStatusItemResult(
                pageIndex = page.pageIndex,
                status = page.status,
                stagingCount = stagingCounts[page.pageIndex] ?: 0,
                updatedAt = page.updatedAt,
            )
        }

    return PdfPageDataExtractionStatusSummaryResult(
        documentId = documentId,
        documentStatus = document.status,
        pageCount = pages.size,
        extractedPageCount = statusItems.count { it.status == PdfPageDataExtractionStatus.EXTRACTED },
        runningPageCount = statusItems.count { it.status == PdfPageDataExtractionStatus.RUNNING },
        failedPageCount = statusItems.count { it.status == PdfPageDataExtractionStatus.FAILED },
        pages = statusItems,
    )
}

// PdfExtractionDocument.kt
enum class PdfExtractionStatus {
    UPLOADED,
    PENDING_REVIEW,
    REVIEWED,
    COMMITTED,
    OCR_FAILED,
}

enum class PdfPageDataExtractionStatus {
    NONE,
    RUNNING,
    EXTRACTED,
    FAILED,
    COMMITTED,
}

// PdfExtractionService.kt
@Transactional(readOnly = true)
fun findMatchedControlPlan(documentId: Long): ControlPlanListItem {
    val document = requireDocumentById(documentId)
    val templateId =
        document.templateId
            ?: throw ResourceNotFoundException("error.pdf_extraction.control_plan_template_unbound", arrayOf(documentId))
    val template = templateService.requireById(templateId)

    return controlPlanService
        .findFirstByCharacteristicIdAndPdfFileName(
            characteristicId = template.characteristicId,
            pdfFileName = document.fileName,
        )
        ?: throw ResourceNotFoundException(
            "error.pdf_extraction.control_plan_not_found",
            arrayOf<Any?>(documentId, document.fileName),
        )
}

// ControlPlanService.kt
fun findFirstByCharacteristicIdAndPdfFileName(
    characteristicId: Long,
    pdfFileName: String,
): ControlPlanListItem? {
    val normalizedPdfFileName = pdfFileName.trim()
    if (normalizedPdfFileName.isBlank()) {
        return null
    }

    return ControlPlanTable
        .join(MaterialTable, JoinType.INNER, onColumn = ControlPlanTable.materialId, otherColumn = MaterialTable.id)
        .join(CharacteristicTable, JoinType.INNER, onColumn = ControlPlanTable.characteristicId, otherColumn = CharacteristicTable.id)
        .join(UserTable, JoinType.INNER, onColumn = ControlPlanTable.creatorId, otherColumn = UserTable.id)
        .selectAll()
        .where { ControlPlanTable.characteristicId eq characteristicId }
        .orderBy(ControlPlanTable.updatedAt to SortOrder.DESC, ControlPlanTable.id to SortOrder.DESC)
        .firstOrNull { row ->
            row[ControlPlanTable.metadata][PDF_FILE_NAME_METADATA_KEY]?.trim() == normalizedPdfFileName
        }?.toControlPlanListItem()
}