package com.erp.reconciliation.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.erp.reconciliation.entity.ReconciliationDifference;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;

/**
 * 对账差异记录 Mapper
 * 
 * @author ERP Migration
 */
@Mapper
public interface ReconciliationDifferenceRepository extends BaseMapper<ReconciliationDifference> {

    /**
     * 根据对账单ID查询差异列表
     */
    @Select("SELECT * FROM reconciliation_difference WHERE bill_id = #{billId} AND deleted_at IS NULL ORDER BY created_at DESC")
    List<ReconciliationDifference> findByBillId(@Param("billId") Long billId);

    /**
     * 根据对账单号查询差异列表
     */
    @Select("SELECT * FROM reconciliation_difference WHERE bill_no = #{billNo} AND deleted_at IS NULL ORDER BY created_at DESC")
    List<ReconciliationDifference> findByBillNo(@Param("billNo") String billNo);

    /**
     * 分页查询差异记录
     */
    IPage<ReconciliationDifference> selectPageByConditions(
            Page<ReconciliationDifference> page,
            @Param("billId") Long billId,
            @Param("billNo") String billNo,
            @Param("differenceType") String differenceType,
            @Param("handleStatus") String handleStatus,
            @Param("platform") String platform,
            @Param("shopId") Long shopId,
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate
    );

    /**
     * 查询待处理的差异
     */
    @Select("SELECT * FROM reconciliation_difference WHERE handle_status = 'pending' AND deleted_at IS NULL ORDER BY created_at ASC LIMIT #{limit}")
    List<ReconciliationDifference> findPendingDifferences(@Param("limit") int limit);

    /**
     * 根据对账单ID删除差异记录
     */
    @Select("DELETE FROM reconciliation_difference WHERE bill_id = #{billId}")
    int deleteByBillId(@Param("billId") Long billId);

    /**
     * 根据内部订单ID查询差异
     */
    @Select("SELECT * FROM reconciliation_difference WHERE internal_order_id = #{orderId} AND deleted_at IS NULL ORDER BY created_at DESC")
    List<ReconciliationDifference> findByOrderId(@Param("orderId") Long orderId);

    /**
     * 根据平台订单号查询差异
     */
    @Select("SELECT * FROM reconciliation_difference WHERE platform_order_sn = #{platformOrderSn} AND deleted_at IS NULL ORDER BY created_at DESC")
    List<ReconciliationDifference> findByPlatformOrderSn(@Param("platformOrderSn") String platformOrderSn);

    /**
     * 统计各处理状态的数量
     */
    @Select("SELECT handle_status, COUNT(*) as count FROM reconciliation_difference WHERE deleted_at IS NULL GROUP BY handle_status")
    List<ReconciliationDifference> countByHandleStatus();

    /**
     * 统计差异总金额
     */
    @Select("SELECT SUM(ABS(difference_amount)) FROM reconciliation_difference WHERE bill_id = #{billId} AND deleted_at IS NULL")
    BigDecimal sumDifferenceAmountByBillId(@Param("billId") Long billId);

    /**
     * 根据处理状态更新
     */
    @Select("UPDATE reconciliation_difference SET handle_status = #{status}, handle_time = NOW(), updated_at = NOW() WHERE id = #{id}")
    int updateHandleStatus(@Param("id") Long id, @Param("status") String status);
}
