package com.ffii.fpsms.modules.master.service

import com.ffii.core.support.AbstractBaseEntityService
import com.ffii.core.support.JdbcDao
import com.ffii.fpsms.modules.master.entity.*
import com.ffii.fpsms.modules.master.web.models.ItemQc
import com.ffii.fpsms.modules.master.web.models.ItemWithQcResponse
import com.ffii.fpsms.modules.master.web.models.MessageResponse
import com.ffii.fpsms.modules.master.web.models.NewItemRequest
import org.springframework.stereotype.Service
import org.springframework.transaction.annotation.Transactional
import java.io.IOException
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter

@Service
open class ItemsService(
    private val jdbcDao: JdbcDao,
    private val itemsRepository: ItemsRepository,
    private val qcCheckRepository: QcCheckRepository,
    private val qcItemsRepository: QcItemRepository,
): AbstractBaseEntityService<Items, Long, ItemsRepository>(jdbcDao, itemsRepository)  {
    // do mapping with projection
    open fun allItems(): List<Items> {
        // TODO: Replace by actual logic
        val items = itemsRepository.findAll()
        return items
    }

    open fun getRoughScheduleList(): List<Map<String, Any>> {
        val now = LocalDateTime.now()
        val lastMonthStart = now.minusMonths(1).withDayOfMonth(1) // Start of last month
        val lastMonthEnd = now.minusDays(now.dayOfMonth.toLong()).withHour(23).withMinute(59).withSecond(59) // End of last month

        val curMonthStart = now.withDayOfMonth(1) // Start of last month
        val curMonthEnd = now.withDayOfMonth(31).withHour(23).withMinute(59).withSecond(59) // End of last month

        // Format dates if needed
        val formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")

        //TODO: change back to use last month for production
        val args = mapOf(
            "lastMonthStart" to curMonthStart.format(formatter),
            "lastMonthEnd" to curMonthEnd.format(formatter)
        )

        println("lastMonthStart:" + args.get("lastMonthStart"))
        println("lastMonthEnd:" + args.get("lastMonthEnd"))

        val sql = StringBuilder(
            " SELECT "
            + " pol.itemId, "
            + " i.name AS itemName, "
            + " pol.itemNo, "
            + " 0 AS openBalance, "
            + " COUNT(DISTINCT pol.purchaseOrderId) AS orderCount, "
            + " SUM(IFNULL(pol.qty, 0)) AS totalQty, "
            + " SUM(IFNULL(pol.qty, 0)) / COUNT(DISTINCT pol.purchaseOrderId) AS lastMonthAvgSalesCount, "
            + " 22000 - IFNULL(i.maxQty , 0) AS fgProductionLimit "
            + " FROM purchase_order po "
            + " LEFT JOIN purchase_order_line pol ON pol.purchaseOrderId = po.id "
            + " LEFT JOIN items i ON i.id = pol.itemId "
            + " WHERE po.deleted = false "
            + " AND po.estimatedArrivalDate >= :lastMonthStart "
            + " AND po.estimatedArrivalDate <= :lastMonthEnd "
            + " AND po.`type` LIKE \"shop\" "
            + " GROUP BY pol.itemId, pol.itemNo, i.name "
        );
        return jdbcDao.queryForList(sql.toString(), args);
    }

    open fun getItemsByPage(args: Map<String, Any>): List<Map<String, Any>>  {
        // Extract parameters from the DTO
        val sql = StringBuilder(
            "SELECT " +
            "i.id, " +
            "i.code, " +
            "i.name, " +
            "i.description " +
            "FROM items i " +
            "WHERE i.deleted = FALSE"
        );

        if (args.containsKey("name")){
            sql.append(" AND i.name like :name ");
        }

        if (args.containsKey("code")){
            sql.append(" AND i.code like :code ");
        }

        return jdbcDao.queryForList(sql.toString(), args);
    }

    open fun findById(id: Long): Items? {
        return itemsRepository.findByIdAndDeletedFalse(id);
    }

    open fun findByM18Id(m18Id: Long): Items? {
        return itemsRepository.findByM18IdAndDeletedIsFalse(m18Id)
    }

    open fun findByNameLikeAndM18UomId(name: String, m18UomId: Long): Items? {
        return itemsRepository.findByNameLikeAndUomM18Id(name, m18UomId);
    }

    open fun findByNameExactlyAndM18UomId(name: String, m18UomId: Long): Items? {
        return itemsRepository.findByNameExactlyAndUomM18Id(name, m18UomId);
    }

    open fun findByNameAndM18UomId(name: String, m18UomId: Long): Items? {
        return findByNameExactlyAndM18UomId(name, m18UomId) ?: findByNameLikeAndM18UomId(name, m18UomId);
    }

    open fun findByM18BomCode(m18BomCode: String): Items? {
        return itemsRepository.findByM18BomCode(m18BomCode);
    }

    // QcCheck included item
    open fun getItem(id: Long): ItemWithQcResponse {
        val list = listOf(1,2)
        val item = itemsRepository.findByIdAndDeletedFalse(id) ?: Items()
        val qcItems = qcItemsRepository.findAllByDeletedIsFalse()
        val qcCheckMap = qcCheckRepository.findAllByItemIdAndDeletedFalse(id).associateBy { it.id }
        val qc = qcItems.map {
            val check = qcCheckMap[it.id] // Find the corresponding qcCheck
            println(check?.description)
            println(check?.lowerLimit)
            ItemQc(
                id = it.id!!,
                name = it.name!!,
                code = it.code!!,
                description = it.description!!,
                instruction = check?.description,
                lowerLimit = check?.lowerLimit ,
                upperLimit = check?.upperLimit,
                isActive = check != null
                ) // Create combined item
        }
        val response = ItemWithQcResponse(
            item = item,
            qcChecks = qc
        )
        // TODO: Return with QC items
        return response
    }
//    open fun getItem(id: Long): Items? {
//        // TODO: Return with QC items
//        return itemsRepository.findById(id).get()
//    }
    @Throws(IOException::class)
    @Transactional
    open fun saveItem(request: NewItemRequest): MessageResponse {
        val duplicatedItem = itemsRepository.findByCodeAndTypeAndDeletedFalse(request.code, request.type.type)
        if (duplicatedItem != null && duplicatedItem.id != request.id) {
            return MessageResponse(
                id = request.id,
                code = request.code,
                name = request.name,
                type = request.type.toString(),
                message = "The item code has already existed",
                errorPosition = "code"
            )
        }
        val item = if (request.m18Id != null) findByM18Id(request.m18Id) ?: Items()
            else if (request.id != null && request.id > 0) itemsRepository.findByIdAndDeletedFalse(request.id) ?: Items()
            else Items()
        if (item.m18LastModifyDate == request.m18LastModifyDate) {
            return MessageResponse(
                id = item.id,
                code = item.code,
                name = item.name,
                type = item.type.toString(),
                message = "M18 Item does not have any updates",
                errorPosition = "m18LastModifyDate"
            )
        }

        item.apply {
            code = request.code
            name = request.name
            description = request.description
            remarks = request.remarks
            shelfLife = request.shelfLife
            countryOfOrigin = request.countryOfOrigin
            maxQty = request.maxQty
            this.type = request.type.type
            m18Id = request.m18Id ?: this.m18Id
            m18LastModifyDate = request.m18LastModifyDate ?: this.m18LastModifyDate
        }
        val savedItem = itemsRepository.saveAndFlush(item)
        return MessageResponse(
            id = savedItem.id,
            name = savedItem.name,
            code = savedItem.code,
            type = savedItem.type.toString(),
            message = "Item Save Success",
            errorPosition = null,
        )
    }
}