package com.ruoyi.sales;

import com.ruoyi.RuoYiApplication;
import com.ruoyi.common.enums.ApproveTypeEnum;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.test.annotation.Rollback;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * 清理多出来的「发货审批」{@code approve_process}（{@code approve_type=7}，事由 {@code 发货审批:合同号}）。
 * <p>
 * 典型场景：销售订单已发货条数与有效发货审批条数应一致；若批量脚本重复插入，会出现同一事由多条
 * {@code approve_delete=0} 的记录，或存在合同号在「未发货」台账上仍有一条发货审批的孤儿数据。
 * <p>
 * 处理策略（与若依删除习惯一致，<b>软删除</b>）：
 * <ol>
 *   <li><b>重复事由</b>：同一 {@code approve_reason} 下保留 {@code id} 最小的一条，其余 {@code approve_delete=1}。</li>
 *   <li><b>孤儿事由</b>：事由解析出的合同号在 {@code sales_ledger} 中不存在 {@code delivery_status=5}（已发货）的订单时，
 *       将该发货审批软删除。（若你希望以其它规则定义「已发货」，需改 SQL。）</li>
 * </ol>
 * 同步将对应 {@code approve_node} 的 {@code delete_flag} 置为 1（删除标记）。
 * <p>
 * <b>执行前必须</b>：VM options {@code -Druoyi.deliveryApprove.cleanup.commit=true} 或环境变量
 * {@code RUOYI_DELIVERY_APPROVE_CLEANUP_COMMIT=true}，且连业务库。未开启时本用例跳过。
 */
@SpringBootTest(classes = RuoYiApplication.class)
class DeliveryApproveExtraCleanupTest {

    private static final Logger log = LoggerFactory.getLogger(DeliveryApproveExtraCleanupTest.class);

    private static final String REASON_PREFIX = "发货审批:";

    @Autowired
    private DataSource dataSource;

    @Test
    @Transactional
    @Rollback(false)
    void softRemoveExtraDeliveryApprovals() {
        NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(dataSource);
        int type7 = ApproveTypeEnum.DELIVERY.getCode();

        List<ApproveRowRef> duplicateExtras = selectDuplicateDeliveryExtras(named, type7);
        int n1 = applySoftRemove(named, duplicateExtras);
        log.warn("[发货审批清理] 重复事由软删除行数={}, id列表={}", n1, formatIds(duplicateExtras));

        List<ApproveRowRef> orphans = selectOrphanDeliveryApprovals(named, type7);
        int n2 = applySoftRemove(named, orphans);
        log.warn("[发货审批清理] 孤儿事由软删除行数={}, id列表={}", n2, formatIds(orphans));

        log.warn("[发货审批清理] 完成。重复={}, 孤儿={}, 合计软删除={}", n1, n2, n1 + n2);
    }

    private static String formatIds(List<ApproveRowRef> refs) {
        return refs.stream().map(r -> String.valueOf(r.id)).collect(Collectors.joining(","));
    }

    private List<ApproveRowRef> selectDuplicateDeliveryExtras(NamedParameterJdbcTemplate named, int approveType) {
        MapSqlParameterSource p = new MapSqlParameterSource();
        p.addValue("atype", approveType);
        p.addValue("pfx", REASON_PREFIX + "%");
        return named.query(
                "SELECT ap.id, ap.approve_id AS approveId FROM approve_process ap "
                        + "INNER JOIN ( "
                        + "  SELECT approve_reason, MIN(id) AS keep_id "
                        + "  FROM approve_process "
                        + "  WHERE approve_delete = 0 AND approve_type = :atype AND approve_reason LIKE :pfx "
                        + "  GROUP BY approve_reason "
                        + "  HAVING COUNT(*) > 1 "
                        + ") d ON ap.approve_reason = d.approve_reason AND ap.id <> d.keep_id "
                        + "WHERE ap.approve_delete = 0 AND ap.approve_type = :atype",
                p,
                (rs, i) -> new ApproveRowRef(rs.getLong("id"), rs.getString("approveId")));
    }

    /**
     * 事由中合同号在「已发货」销售台账中不存在则视为孤儿
     */
    private List<ApproveRowRef> selectOrphanDeliveryApprovals(NamedParameterJdbcTemplate named, int approveType) {
        MapSqlParameterSource p = new MapSqlParameterSource();
        p.addValue("atype", approveType);
        p.addValue("pfx", REASON_PREFIX + "%");
        return named.query(
                "SELECT ap.id, ap.approve_id AS approveId FROM approve_process ap "
                        + "WHERE ap.approve_delete = 0 AND ap.approve_type = :atype AND ap.approve_reason LIKE :pfx "
                        + "AND NOT EXISTS ( "
                        + "  SELECT 1 FROM sales_ledger sl "
                        + "  WHERE sl.sales_contract_no = TRIM(SUBSTRING(ap.approve_reason, CHAR_LENGTH('发货审批:') + 1)) "
                        + "    AND sl.delivery_status = 5 "
                        + ")",
                p,
                (rs, i) -> new ApproveRowRef(rs.getLong("id"), rs.getString("approveId")));
    }

    private int applySoftRemove(NamedParameterJdbcTemplate named, List<ApproveRowRef> refs) {
        if (refs.isEmpty()) {
            return 0;
        }
        Set<Long> idSet = new LinkedHashSet<>();
        Set<String> approveIdSet = new LinkedHashSet<>();
        for (ApproveRowRef r : refs) {
            idSet.add(r.id);
            if (r.approveId != null) {
                approveIdSet.add(r.approveId);
            }
        }
        List<Long> ids = new ArrayList<>(idSet);
        List<String> approveIds = new ArrayList<>(approveIdSet);

        MapSqlParameterSource p1 = new MapSqlParameterSource();
        p1.addValue("ids", ids);
        int u1 = named.update("UPDATE approve_process SET approve_delete = 1 WHERE id IN (:ids)", p1);

        MapSqlParameterSource p2 = new MapSqlParameterSource();
        p2.addValue("aids", approveIds);
        int u2 = named.update("UPDATE approve_node SET delete_flag = 1 WHERE delete_flag = 0 AND approve_process_id IN (:aids)", p2);

        log.debug("approve_process 更新行数={}, approve_node 更新行数={}", u1, u2);
        return u1;
    }

    private static final class ApproveRowRef {
        private final long id;
        private final String approveId;

        private ApproveRowRef(long id, String approveId) {
            this.id = id;
            this.approveId = approveId;
        }
    }
}