package com.ffii.fpsms.modules.common.internalSetup

import com.ffii.core.utils.ExcelUtils
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.springframework.stereotype.Component
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.transaction.annotation.Transactional
import com.ffii.fpsms.modules.master.entity.ItemsRepository
import com.ffii.fpsms.modules.master.entity.WarehouseRepository
import com.ffii.fpsms.modules.stock.service.StockInLineService
import com.ffii.fpsms.modules.stock.web.model.StockInRequest
import java.io.File
import java.io.FileInputStream
import java.io.IOException
import java.math.BigDecimal
import java.time.LocalDate
import java.time.format.DateTimeFormatter
import com.ffii.fpsms.modules.stock.entity.InventoryLotLineRepository
import com.ffii.fpsms.modules.stock.web.model.PrintQrCodeForSilRequest

@Component
open class InventorySetup {

    @Autowired
    private lateinit var itemsRepository: ItemsRepository

    @Autowired
    private lateinit var warehouseRepository: WarehouseRepository

    @Autowired
    private lateinit var stockInLineService: StockInLineService

    private val DEFAULT_WAREHOUSE_CODE = "2F-W201-#A-01"
    private val DATE_FORMAT = DateTimeFormatter.ofPattern("yyyy/MM/dd")

    @Transactional(rollbackFor = [Exception::class])
    open fun importExcelFromLocal(filePath: String): Int {
        val file = File(filePath)
        if (!file.exists()) {
            throw IOException("File not found: $filePath")
        }

        FileInputStream(file).use { inputStream ->
            val workbook: Workbook = XSSFWorkbook(inputStream)

            try {
                val sheet: Sheet = workbook.getSheetAt(0)
                val inventoryDataList = mutableListOf<InventoryData>()

                // Column indices
                val COLUMN_ITEM_NO_INDEX = 6        // Column G
                val COLUMN_STORE_ID_INDEX = 14      // Column O
                val COLUMN_WAREHOUSE_INDEX = 16      // Column Q
                val COLUMN_AREA_INDEX = 17          // Column R
                val COLUMN_SLOT_INDEX = 18          // Column S
                val COLUMN_PRODUCT_LOT_NO_INDEX = 21 // Column V
                val COLUMN_QUANTITY_INDEX = 22       // Column W
                val COLUMN_EXPIRY_DATE_INDEX = 26    // Column AA

                val START_ROW_INDEX = 2  // Start from Excel row 3 (0-based: index 2)

                // ────────────────────────────────────────────────
                // Process data rows (starting from Excel row 3, index 2)
                // ────────────────────────────────────────────────
                for (rowIndex in START_ROW_INDEX..sheet.lastRowNum) {
                    val row = sheet.getRow(rowIndex) ?: continue

                    val itemNo = ExcelUtils.getStringValue(row.getCell(COLUMN_ITEM_NO_INDEX))?.trim() ?: ""

                    // Skip if itemNo is empty (required field)
                    if (itemNo.isEmpty()) {
                        println("SKIP row ${rowIndex + 1} - Missing itemNo")
                        continue
                    }

                    // Read warehouse location fields
                    val store_id = ExcelUtils.getStringValue(row.getCell(COLUMN_STORE_ID_INDEX))?.trim() ?: ""
                    val warehouse = ExcelUtils.getStringValue(row.getCell(COLUMN_WAREHOUSE_INDEX))?.trim() ?: ""
                    val area = ExcelUtils.getStringValue(row.getCell(COLUMN_AREA_INDEX))?.trim() ?: ""
                    val slot = ExcelUtils.getStringValue(row.getCell(COLUMN_SLOT_INDEX))?.trim() ?: ""

                    // Generate LocationCode: store_id-warehouse-area-slot
                    val locationCode = if (store_id.isNotEmpty() && warehouse.isNotEmpty() &&
                        area.isNotEmpty() && slot.isNotEmpty()) {
                        "$store_id-$warehouse-$area-$slot"
                    } else {
                        ""
                    }

                    val quantityStr = ExcelUtils.getStringValue(row.getCell(COLUMN_QUANTITY_INDEX))?.trim()
                    val quantity = quantityStr?.toBigDecimalOrNull()

                    if (quantity == null || quantity <= BigDecimal.ZERO) {
                        println("SKIP row ${rowIndex + 1} - Invalid quantity: $quantityStr")
                        continue
                    }

                    val productLotNo = ExcelUtils.getStringValue(row.getCell(COLUMN_PRODUCT_LOT_NO_INDEX))?.trim()

                    // Parse expiry date with fallback to default (1 month later)
                    val expiryDateStr = ExcelUtils.getStringValue(row.getCell(COLUMN_EXPIRY_DATE_INDEX))?.trim()
                    val expiryDate = expiryDateStr?.let {
                        try {
                            LocalDate.parse(it, DATE_FORMAT)
                        } catch (e: Exception) {
                            println("WARNING row ${rowIndex + 1} - Invalid expiry date format: $it (expected YYYY/MM/DD), using default: 1 month later")
                            null
                        }
                    } ?: run {
                        // If expiryDateStr is null or empty, or parsing failed, use default
                        LocalDate.now().plusMonths(1)
                    }

                    // If parsing failed, use default
                    val finalExpiryDate = expiryDate ?: LocalDate.now().plusMonths(1)

                    println("=== Processing row ${rowIndex + 1} (itemNo: $itemNo, qty: $quantity, expiryDate: $finalExpiryDate, locationCode: ${locationCode.ifEmpty { "DEFAULT" }}) ===")

                    inventoryDataList.add(
                        InventoryData(
                            itemNo = itemNo,
                            locationCode = locationCode,
                            quantity = quantity,
                            productLotNo = productLotNo,
                            expiryDate = finalExpiryDate
                        )
                    )
                }

                println("Total valid rows collected: ${inventoryDataList.size}")

                // ────────────────────────────────────────────────
                // Create stock in for each item
                // ────────────────────────────────────────────────
                var createdCount = 0
                var notFoundCount = 0
                var warehouseNotFoundCount = 0

                for (data in inventoryDataList) {
                    try {
                        // Find item by code (itemNo)
                        val item = itemsRepository.findFirstByCodeAndDeletedFalse(data.itemNo)

                        if (item == null) {
                            println("NOT FOUND - Item with code '${data.itemNo}' does not exist")
                            notFoundCount++
                            continue
                        }

                        if (item.id == null) {
                            println("ERROR - Item '${data.itemNo}' has no ID")
                            continue
                        }

                        // Find warehouse by LocationCode, fallback to default if not found
                        var warehouse = if (data.locationCode.isNotEmpty()) {
                            warehouseRepository.findByCodeAndDeletedIsFalse(data.locationCode)
                        } else {
                            null
                        }

                        // If warehouse not found, use default warehouse
                        if (warehouse == null) {
                            println("WAREHOUSE NOT FOUND - Warehouse with code '${data.locationCode}' does not exist, using default: $DEFAULT_WAREHOUSE_CODE")
                            warehouse = warehouseRepository.findByCodeAndDeletedIsFalse(DEFAULT_WAREHOUSE_CODE)
                            warehouseNotFoundCount++

                            if (warehouse == null) {
                                println("ERROR - Default warehouse '$DEFAULT_WAREHOUSE_CODE' also not found in database")
                                continue
                            }
                        }

                        if (warehouse.id == null) {
                            println("ERROR - Warehouse '${warehouse.code}' has no ID")
                            continue
                        }

                        // Generate dnNo: LT-YYYYMMDD-itemCode
                        val today = LocalDate.now()
                        val dnNo = "LT-${today.format(DateTimeFormatter.ofPattern("yyyyMMdd"))}-${data.itemNo}"

                        // Use generic stock in
                        val stockInRequest = StockInRequest(
                            itemId = item.id!!,
                            itemNo = data.itemNo,
                            demandQty = data.quantity,
                            acceptedQty = data.quantity,
                            expiryDate = data.expiryDate,
                            lotNo = null,  // System will auto-generate
                            productLotNo = data.productLotNo,
                            dnNo = dnNo,
                            type = "OPEN",
                            warehouseId = warehouse.id!!
                        )

                        stockInLineService.createStockIn(stockInRequest)
                        createdCount++
                        println("CREATED stock in for item: ${data.itemNo} (dnNo: $dnNo, warehouse: ${warehouse.code})")
                    } catch (e: Exception) {
                        println("Error processing inventory row (itemNo: ${data.itemNo}): ${e.message}")
                        e.printStackTrace()
                        continue
                    }
                }

                println("Import finished. Created $createdCount stock in records, Not found: $notFoundCount items, Warehouse not found (used default): $warehouseNotFoundCount.")
                return createdCount

            } finally {
                workbook.close()
            }
        }
    }

    data class InventoryData(
        val itemNo: String,
        val locationCode: String,
        val quantity: BigDecimal,
        val productLotNo: String?,
        val expiryDate: LocalDate
    )

    @Autowired
    private lateinit var inventoryLotLineRepository: InventoryLotLineRepository
    @Transactional(rollbackFor = [Exception::class])
    open fun printAllLotStockInLabels(
        printerId: Long,
        printQty: Int = 1,
        fromIndex: Int? = null,
        toIndex: Int? = null
    ): Int {
        println("=== Starting to print stock in labels for all lots ===")

        // Get all inventory lot lines that are not deleted and have stock in lines
        val allInventoryLotLines = try {
            inventoryLotLineRepository.findAllByDeletedIsFalseAndHasStockInLine()
        } catch (e: Exception) {
            println("Error fetching inventory lot lines: ${e.message}")
            e.printStackTrace()
            return 0
        }

        if (allInventoryLotLines.isEmpty()) {
            println("No inventory lot lines found to print")
            return 0
        }

        // Apply range filter if provided
        val inventoryLotLinesToPrint = if (fromIndex != null && toIndex != null) {
            val startIndex = fromIndex.coerceAtLeast(0)
            val endIndex = toIndex.coerceAtMost(allInventoryLotLines.size - 1)
            if (startIndex > endIndex || startIndex >= allInventoryLotLines.size) {
                println("Invalid range: fromIndex=$fromIndex, toIndex=$toIndex, total items=${allInventoryLotLines.size}")
                return 0
            }
            println("Printing range: index $startIndex to $endIndex (out of ${allInventoryLotLines.size} total items)")
            allInventoryLotLines.subList(startIndex, endIndex + 1)
        } else {
            println("Printing all ${allInventoryLotLines.size} inventory lot lines")
            allInventoryLotLines
        }

        println("Found ${inventoryLotLinesToPrint.size} inventory lot lines to print")

        var printedCount = 0
        var errorCount = 0

        // Loop through each inventory lot line
        for ((index, inventoryLotLine) in inventoryLotLinesToPrint.withIndex()) {
            try {
                // Get the associated stock in line
                val stockInLine = inventoryLotLine.inventoryLot?.stockInLine
                    ?: throw IllegalArgumentException("No stock in line associated with inventory lot line ID: ${inventoryLotLine.id}")

                val stockInLineId = stockInLine.id
                    ?: throw IllegalArgumentException("Stock in line has no ID")

                val actualIndex = if (fromIndex != null) fromIndex + index else index
                println("Processing lot ${actualIndex + 1}/${allInventoryLotLines.size}: Lot No: ${inventoryLotLine.inventoryLot?.lotNo}, StockInLineId: $stockInLineId")

                // Create print request
                val printRequest = PrintQrCodeForSilRequest(
                    stockInLineId = stockInLineId,
                    printerId = printerId,
                    printQty = printQty
                )

                // Print the label
                stockInLineService.printQrCode(printRequest)
                printedCount++

                println("✓ Successfully printed label for lot: ${inventoryLotLine.inventoryLot?.lotNo}")

            } catch (e: Exception) {
                errorCount++
                println("✗ Error printing label for inventory lot line ID ${inventoryLotLine.id}: ${e.message}")
                e.printStackTrace()
                // Continue with next lot instead of stopping
                continue
            }
        }

        println("=== Printing finished ===")
        println("Total processed: ${inventoryLotLinesToPrint.size}")
        println("Successfully printed: $printedCount")
        println("Errors: $errorCount")

        return printedCount
    }

    @Transactional(rollbackFor = [Exception::class])
    open fun printLotStockInLabelsByItemIds(
        printerId: Long,
        itemIds: List<Long>,
        printQty: Int = 1,
        fromIndex: Int? = null,
        toIndex: Int? = null
    ): Int {
        if (itemIds.isEmpty()) return 0

        val allInventoryLotLines = inventoryLotLineRepository
            .findAllByItemIdIn(itemIds)
            .filter { it.deleted == false && it.inventoryLot?.stockInLine != null }

        if (allInventoryLotLines.isEmpty()) return 0

        val inventoryLotLinesToPrint = if (fromIndex != null && toIndex != null) {
            val startIndex = fromIndex.coerceAtLeast(0)
            val endIndex = toIndex.coerceAtMost(allInventoryLotLines.size - 1)
            if (startIndex > endIndex || startIndex >= allInventoryLotLines.size) return 0
            allInventoryLotLines.subList(startIndex, endIndex + 1)
        } else {
            allInventoryLotLines
        }

        var printedCount = 0
        for ((index, inventoryLotLine) in inventoryLotLinesToPrint.withIndex()) {
            val stockInLineId = inventoryLotLine.inventoryLot?.stockInLine?.id
                ?: throw IllegalArgumentException("Stock in line missing")
            val actualIndex = if (fromIndex != null) fromIndex + index else index
            println("Processing lot ${actualIndex + 1}/${allInventoryLotLines.size}: Lot No: ${inventoryLotLine.inventoryLot?.lotNo}, StockInLineId: $stockInLineId")
            stockInLineService.printQrCode(
                PrintQrCodeForSilRequest(
                    stockInLineId = stockInLineId,
                    printerId = printerId,
                    printQty = printQty
                )
            )
            printedCount++
        }
        return printedCount
    }

}