package com.ffii.fpsms.modules.deliveryOrder.service

import com.ffii.core.support.JdbcDao
import com.ffii.fpsms.modules.deliveryOrder.entity.DoPickOrderRepository
import com.ffii.fpsms.modules.deliveryOrder.enums.DoPickOrderStatus
import com.ffii.fpsms.modules.deliveryOrder.web.models.LaneBtn
import com.ffii.fpsms.modules.deliveryOrder.web.models.LaneRow
import com.ffii.fpsms.modules.deliveryOrder.web.models.StoreLaneSummary
import com.ffii.fpsms.modules.deliveryOrder.web.models.DoPickOrderSummaryItem
import org.springframework.stereotype.Service
import java.time.LocalDate
import com.ffii.fpsms.modules.deliveryOrder.entity.DoPickOrderRecordRepository

@Service
class DoPickOrderQueryService(
    private val doPickOrderRepository: DoPickOrderRepository,
    private val jdbcDao: JdbcDao,
    private val doPickOrderRecordRepository: DoPickOrderRecordRepository
) {
    
    fun getSummaryByStore(storeId: String, requiredDate: LocalDate?, releaseType: String): StoreLaneSummary {
        val targetDate = requiredDate ?: LocalDate.now()
        println("    DEBUG: Getting summary for store=$storeId, date=$targetDate, releaseType=$releaseType")
        
        val actualStoreId = when (storeId) {
            "2/F" -> "2/F"
            "4/F" -> "4/F"
            else -> storeId
        }
        
        // Query active do_pick_order records (pending, released, completed that haven't been moved yet)
        val activeRecords = doPickOrderRepository.findByStoreIdAndRequiredDeliveryDateAndTicketStatusIn(
            actualStoreId,
            targetDate,
            listOf(DoPickOrderStatus.pending, DoPickOrderStatus.released, DoPickOrderStatus.completed)
        )
        
        // 根据 releaseType 过滤 activeRecords
        val filteredActiveRecordsByReleaseType = when (releaseType.lowercase()) {
            "batch" -> activeRecords.filter { it.releaseType == "batch" }
            "single" -> activeRecords.filter { it.releaseType == "single" }
            else -> activeRecords  // "all" 或其他值，不过滤
        }
        
        // Query completed records from do_pick_order_record table
        val completedRecords = doPickOrderRecordRepository.findByStoreIdAndRequiredDeliveryDateAndTicketStatusIn(
            actualStoreId,
            targetDate,
            listOf(DoPickOrderStatus.completed)
        )
        
        // 根据 releaseType 过滤 completedRecords
        val filteredCompletedRecordsByReleaseType = when (releaseType.lowercase()) {
            "batch" -> completedRecords.filter { it.releaseType == "batch" }
            "single" -> completedRecords.filter { it.releaseType == "single" }
            else -> completedRecords  // "all" 或其他值，不过滤
        }
        
        println("    DEBUG: Found ${activeRecords.size} active records for date $targetDate")
        println("    DEBUG: After releaseType filter: ${filteredActiveRecordsByReleaseType.size} active records")
        println("    DEBUG: Found ${completedRecords.size} completed records for date $targetDate")
        println("    DEBUG: After releaseType filter: ${filteredCompletedRecordsByReleaseType.size} completed records")
        
        // Filter active records (check for non-issue lines)
        val filteredActiveRecords = filteredActiveRecordsByReleaseType.filter { doPickOrder ->
            val hasNonIssueLines = checkDoPickOrderHasNonIssueLines(doPickOrder.id!!)
            if (!hasNonIssueLines) {
                println("    DEBUG: Filtering out DoPickOrder ${doPickOrder.id} - all lines are issues")
            }
            hasNonIssueLines
        }
        
        // For completed records, check if they have non-issue lines in the record table
        val filteredCompletedRecords = filteredCompletedRecordsByReleaseType.filter { record ->
            val hasNonIssueLines = checkDoPickOrderRecordHasNonIssueLines(record.id!!)
            if (!hasNonIssueLines) {
                println("    DEBUG: Filtering out DoPickOrderRecord ${record.id} - all lines are issues")
            }
            hasNonIssueLines
        }
        
        // Combine both lists - need to create a common interface or convert to a common type
        // Since both have the same fields we need, we can create a wrapper or use a data class
        val allRecords = filteredActiveRecords.map { 
            DoPickOrderSummaryItem(
                truckDepartureTime = it.truckDepartureTime,
                truckLanceCode = it.truckLanceCode,
                handledBy = it.handledBy
            )
        } + filteredCompletedRecords.map {
            DoPickOrderSummaryItem(
                truckDepartureTime = it.truckDepartureTime,
                truckLanceCode = it.truckLanceCode,
                handledBy = it.handledBy
            )
        }
        
        println("    DEBUG: After filtering, ${allRecords.size} records remain (${filteredActiveRecords.size} active + ${filteredCompletedRecords.size} completed)")
        
        val grouped = allRecords.groupBy { it.truckDepartureTime to it.truckLanceCode }
            .mapValues { (_, list) ->
                LaneBtn(
                    truckLanceCode = list.first().truckLanceCode ?: "",
                    unassigned = list.count { it.handledBy == null },
                    total = list.size
                )
            }
        
        val timeGroups = grouped.entries
            .groupBy { it.key.first }
            .mapValues { (_, entries) ->
                entries.map { it.value }
                .filter { it.unassigned > 0 } // filter out lanes with no unassigned orders 
                    .sortedByDescending { it.unassigned }
                    .take(3)
            }
            .filterValues { lanes -> lanes.isNotEmpty() }
            .toSortedMap(compareBy { it })
            .entries.take(4)
            .map { (time, lanes) ->
                LaneRow(
                    truckDepartureTime = time?.toString() ?: "",
                    lanes = lanes
                )
            }
        
        return StoreLaneSummary(storeId = storeId, rows = timeGroups)
    }
    
    
    private fun checkDoPickOrderHasNonIssueLines(doPickOrderId: Long): Boolean {
        return try {
            val totalLinesSql = """
                SELECT COUNT(*) as total_lines
                FROM fpsmsdb.do_pick_order_line dpol
                WHERE dpol.do_pick_order_id = :doPickOrderId 
                AND dpol.deleted = 0
            """.trimIndent()
            
            val totalLinesResult = jdbcDao.queryForList(totalLinesSql, mapOf("doPickOrderId" to doPickOrderId))
            val totalLines = (totalLinesResult.firstOrNull()?.get("total_lines") as? Number)?.toInt() ?: 0
            
            if (totalLines == 0) {
                return true
            }
            
            val nonIssueLinesSql = """
                SELECT COUNT(*) as non_issue_lines
                FROM fpsmsdb.do_pick_order_line dpol
                WHERE dpol.do_pick_order_id = :doPickOrderId 
                AND dpol.deleted = 0
                AND (dpol.status IS NULL OR dpol.status != 'issue')
            """.trimIndent()
            
            val nonIssueLinesResult = jdbcDao.queryForList(nonIssueLinesSql, mapOf("doPickOrderId" to doPickOrderId))
            val nonIssueLines = (nonIssueLinesResult.firstOrNull()?.get("non_issue_lines") as? Number)?.toInt() ?: 0
            
            nonIssueLines > 0
        } catch (e: Exception) {
            println("❌ Error checking non-issue lines: ${e.message}")
            true
        }
    }
    
    // Add new method to check non-issue lines for records
    private fun checkDoPickOrderRecordHasNonIssueLines(recordId: Long): Boolean {
        return try {
            // First get the record_id from do_pick_order_record
            val recordSql = """
                SELECT record_id 
                FROM fpsmsdb.do_pick_order_record dpor
                WHERE dpor.id = :recordId
            """.trimIndent()
            
            val recordResult = jdbcDao.queryForList(recordSql, mapOf("recordId" to recordId))
            val recordIdValue = (recordResult.firstOrNull()?.get("record_id") as? Number)?.toLong()
            
            if (recordIdValue == null) {
                return true  // If no record_id, assume it's valid
            }
            
            // Check do_pick_order_line_record table
            val totalLinesSql = """
                SELECT COUNT(*) as total_lines
                FROM fpsmsdb.do_pick_order_line_record dpolr
                WHERE dpolr.record_id = :recordId 
                AND dpolr.deleted = 0
            """.trimIndent()
            
            val totalLinesResult = jdbcDao.queryForList(totalLinesSql, mapOf("recordId" to recordIdValue))
            val totalLines = (totalLinesResult.firstOrNull()?.get("total_lines") as? Number)?.toInt() ?: 0
            
            if (totalLines == 0) {
                return true
            }
            
            val nonIssueLinesSql = """
                SELECT COUNT(*) as non_issue_lines
                FROM fpsmsdb.do_pick_order_line_record dpolr
                WHERE dpolr.record_id = :recordId 
                AND dpolr.deleted = 0
                AND (dpolr.status IS NULL OR dpolr.status != 'issue')
            """.trimIndent()
            
            val nonIssueLinesResult = jdbcDao.queryForList(nonIssueLinesSql, mapOf("recordId" to recordIdValue))
            val nonIssueLines = (nonIssueLinesResult.firstOrNull()?.get("non_issue_lines") as? Number)?.toInt() ?: 0
            
            nonIssueLines > 0
        } catch (e: Exception) {
            println("❌ Error checking non-issue lines for record: ${e.message}")
            true
        }
    }
}