package com.ffii.fpsms.modules.report.service

import org.springframework.stereotype.Service
import net.sf.jasperreports.engine.*
import net.sf.jasperreports.engine.data.JRMapCollectionDataSource
import java.io.InputStream
import com.ffii.core.support.JdbcDao

@Service
open class ReportService(
    private val jdbcDao: JdbcDao,
) {
    /**
     * Queries the database for inventory data based on dates and optional item type.
     */
    fun searchReport1(fromDate: String, toDate: String, itemType: String?): List<Map<String, Any>> {
        val args = mutableMapOf<String, Any>(
            "fromDate" to fromDate, 
            "toDate" to toDate
        )
        
        val itemTypeSql = if (!itemType.isNullOrBlank()) {
            args["itemType"] = "%$itemType%"
            "AND it.type LIKE :itemType"
        } else ""

        val sql = """
            SELECT 
                -- fake value for demo
                8.8 * iv.onHandQty as stockValue,
                iv.onHandQty, 
                it.code as itemCode, 
                it.name as itemName, 
                uc.code as uom, 
                it.type, iv.status
            FROM inventory iv
            LEFT JOIN items it ON iv.itemId = it.id
            LEFT JOIN uom_conversion uc ON iv.uomId = uc.id
            WHERE iv.created >= :fromDate AND iv.created < :toDate
            $itemTypeSql
        """.trimIndent()
        
        return jdbcDao.queryForList(sql, args)
    }


    fun searchReport3( itemType: String?): List<Map<String, Any>> {
        val args = mutableMapOf<String, Any>(

        )
        
        val itemTypeSql = if (!itemType.isNullOrBlank()) {
            args["itemType"] = "%$itemType%"
            "AND it.type LIKE :itemType"
        } else ""

        val sql = """
            SELECT 
                -- fake value for demo
                8.8 * iv.onHandQty as stockValue,
                iv.onHandQty, 
                it.code as itemCode, 
                it.name as itemName, 
                uc.code as uom, 
                it.type, iv.status
            FROM inventory iv
            LEFT JOIN items it ON iv.itemId = it.id
            LEFT JOIN uom_conversion uc ON iv.uomId = uc.id
            WHERE iv.created >= :fromDate AND iv.created < :toDate
            $itemTypeSql
        """.trimIndent()
        
        return jdbcDao.queryForList(sql, args)
    }

    /**
     * Helper function to build SQL clause for comma-separated values.
     * Supports multiple values like "val1, val2, val3" and generates OR conditions with LIKE.
     */
    private fun buildMultiValueLikeClause(
        paramValue: String?,
        columnName: String,
        paramPrefix: String,
        args: MutableMap<String, Any>
    ): String {
        if (paramValue.isNullOrBlank()) return ""
        
        val values = paramValue.split(",").map { it.trim() }.filter { it.isNotBlank() }
        if (values.isEmpty()) return ""
        
        val conditions = values.mapIndexed { index, value ->
            val paramName = "${paramPrefix}_$index"
            args[paramName] = "%$value%"
            "$columnName LIKE :$paramName"
        }
        
        return "AND (${conditions.joinToString(" OR ")})"
    }

    /**
     * Queries the database for Stock In Traceability Report data.
     * Joins stock_in_line, stock_in, items, item_category, qc_result, inventory_lot, inventory_lot_line, warehouse, and shop tables.
     * Supports comma-separated values for stockCategory, stockSubCategory, and itemCode.
     */
    fun searchStockInTraceabilityReport(
        stockCategory: String?,
        stockSubCategory: String?,
        itemCode: String?,
        lastInDateStart: String?,
        lastInDateEnd: String?
    ): List<Map<String, Any>> {
        val args = mutableMapOf<String, Any>()
        
        val stockCategorySql = buildMultiValueLikeClause(stockCategory, "ic.parent", "stockCategory", args)
        val stockSubCategorySql = buildMultiValueLikeClause(stockSubCategory, "ic.sub", "stockSubCategory", args)
        val itemCodeSql = buildMultiValueLikeClause(itemCode, "it.code", "itemCode", args)
        
        val lastInDateStartSql = if (!lastInDateStart.isNullOrBlank()) {
            args["lastInDateStart"] = lastInDateStart
            "AND sil.receiptDate >= :lastInDateStart"
        } else ""
        
        val lastInDateEndSql = if (!lastInDateEnd.isNullOrBlank()) {
            args["lastInDateEnd"] = lastInDateEnd
            "AND sil.receiptDate < :lastInDateEnd"
        } else ""

        val sql = """
            SELECT 
                COALESCE(ic.sub, '') as stockSubCategory,
                COALESCE(it.code, '') as itemNo,
                COALESCE(CONCAT(COALESCE(it.name, ''), ' ', COALESCE(it.description, '')), '') as itemName,
                COALESCE(uc.code, '') as unitOfMeasure,
                COALESCE(sil.dnNo, '') as dnNo,
                COALESCE(sil.lotNo, il.lotNo, '') as lotNo,
                COALESCE(DATE_FORMAT(COALESCE(sil.expiryDate, il.expiryDate), '%Y-%m-%d'), '') as expiryDate,
                CAST(COALESCE(sil.acceptedQty, 0) AS CHAR) as stockInQty,
                CAST(COALESCE(sil.acceptedQty, 0) AS CHAR) as iqcSampleQty,
                CAST(COALESCE(qr.failQty, 0) AS CHAR) as iqcDefectQty,
                CAST(CASE 
                    WHEN COALESCE(sil.acceptedQty, 0) > 0 
                    THEN ROUND((COALESCE(qr.failQty, 0) / sil.acceptedQty) * 100, 2)
                    ELSE 0 
                END AS CHAR) as iqcDefectPercentage,
                CASE 
                    WHEN qr.qcPassed = true OR qr.qcPassed IS NULL THEN 'Accept'
                    ELSE 'Reject'
                END as iqcResult,
                COALESCE(qr.remarks, '') as iqcRemarks,
                COALESCE(wh.code, '') as storeLocation,
                COALESCE(sp.code, '') as supplierID,
                COALESCE(sp.name, '') as supplierName,
                CAST(SUM(COALESCE(sil.acceptedQty, 0)) OVER (PARTITION BY it.id) AS CHAR) as totalStockInQty,
                CAST(SUM(COALESCE(sil.acceptedQty, 0)) OVER (PARTITION BY it.id) AS CHAR) as totalIqcSampleQty,
                CAST(SUM(COALESCE(qr.failQty, 0)) OVER (PARTITION BY it.id) AS CHAR) as totalIqcDefectQty
            FROM stock_in_line sil
            LEFT JOIN stock_in si ON sil.stockInId = si.id
            LEFT JOIN items it ON sil.itemId = it.id
            LEFT JOIN item_category ic ON it.categoryId = ic.id
            LEFT JOIN item_uom iu ON it.id = iu.itemId AND iu.stockUnit = true
            LEFT JOIN uom_conversion uc ON iu.uomId = uc.id
            LEFT JOIN qc_result qr ON sil.id = qr.stockInLineId
            LEFT JOIN inventory_lot il ON sil.inventoryLotId = il.id
            LEFT JOIN inventory_lot_line ill ON il.id = ill.inventoryLotId
            LEFT JOIN warehouse wh ON ill.warehouseId = wh.id
            LEFT JOIN shop sp ON si.supplierId = sp.id
            WHERE sil.deleted = false
            $stockCategorySql
            $stockSubCategorySql
            $itemCodeSql
            $lastInDateStartSql
            $lastInDateEndSql
            ORDER BY ic.sub, it.code, sil.lotNo
        """.trimIndent()
        
        return jdbcDao.queryForList(sql, args)
    }

    /**
     * Compiles and fills a Jasper Report, returning the PDF as a ByteArray.
     */
    fun createPdfResponse(templatePath: String, params: Map<String, Any>, dataList: List<Map<String, Any>>): ByteArray {
        val stream = this::class.java.getResourceAsStream(templatePath)
            ?: throw RuntimeException("Report template not found: $templatePath")
            
        val jasperReport = JasperCompileManager.compileReport(stream)
        
        val dataSource = JRMapCollectionDataSource(dataList)
        
        val jasperPrint = JasperFillManager.fillReport(jasperReport, params, dataSource)
        return JasperExportManager.exportReportToPdf(jasperPrint)
    }
}