package com.ruoyi.sales;

import com.ruoyi.RuoYiApplication;
import org.junit.jupiter.api.Assumptions;
import org.junit.jupiter.api.Disabled;
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 static org.junit.jupiter.api.Assertions.assertTrue;

/**
 * <b>业务口径</b>：历史里 {@code receipt_payment.invoice_ledger_id} 常被存成 <b>销售产品行 id</b>（{@code sales_ledger_product_id}），
 * 与真实 {@code invoice_ledger.id} 不一致。同步时应以 <b>开票台账</b> 为准：凡是「已经开票」的销售台账产品行，
 * 就必须有与之对应的回款流水与回款收入，且<strong>一行开票台账 → 一行回款 → 一行收入</strong>。
 * <p>
 * <b>推荐：{@link #syncReceiptByInvoicedSalesProducts_hbtmblcSchema()}</b>
 * <ol>
 *   <li>查出所有在开票台账里出现过的 {@code sales_ledger_product_id}（{@code invoice_ledger} → {@code invoice_registration_product}）。</li>
 *   <li>删除这些<strong>销售产品行</strong>上现有的 {@code receipt_payment}，并删除 {@code business_type=1} 且挂在这些回款上的 {@code account_income}。</li>
 *   <li>按 {@code invoice_ledger} 逐行插入新的 {@code receipt_payment}：{@code sales_ledger_id}、{@code sales_ledger_product_id} 来自登记产品行，
 *       {@code invoice_ledger_id = il.id}（真实台账主键），金额/日期/开票人与开票台账一致。</li>
 *   <li>按新回款主键插入 {@code account_income}（与 {@link com.ruoyi.sales.service.impl.ReceiptPaymentServiceImpl} 一致：{@code business_id=receipt_payment.id}）。</li>
 * </ol>
 * 未在开票里出现的销售产品，其回款记录<strong>不删不改</strong>。
 * <p>
 * <b>慎用：{@link #fullRebuildReceiptFromInvoiceLedger_hbtmblcSchema()}</b> — 清空<b>全部</b>回款与<b>全部</b>回款类收入后重建，
 * 仅用于整库以开票为唯一事实源的场景。
 * <p>
 * <b>写库开关</b>：{@code -Druoyi.invoiceReceiptRebuild.commit=true} 或 {@code RUOYI_INVOICE_RECEIPT_REBUILD_COMMIT=true}。
 */
@SpringBootTest(classes = RuoYiApplication.class)
class InvoiceLedgerReceiptIncomeRebuildBatchTest {

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

    private static final Long TENANT_ID = null;

    private static final String DEFAULT_REGISTRANT = "樊志英";

    private static final String RECEIPT_PAYMENT_TYPE = "0";

    static boolean commitSwitchEnabled() {
        String p = System.getProperty("ruoyi.invoiceReceiptRebuild.commit");
        if (p != null && "true".equalsIgnoreCase(p.trim())) {
            return true;
        }
        String e = System.getenv("RUOYI_INVOICE_RECEIPT_REBUILD_COMMIT");
        return e != null && "true".equalsIgnoreCase(e.trim());
    }

    static MapSqlParameterSource tenantParams() {
        return new MapSqlParameterSource("tenantId", TENANT_ID);
    }

    static String tenantCondIl() {
        return "(:tenantId IS NULL OR COALESCE(irp.tenant_id, il.tenant_id) = :tenantId)";
    }

    static String tenantRp(String alias) {
        return "(:tenantId IS NULL OR " + alias + ".tenant_id = :tenantId)";
    }

    /**
     * 出现在开票链路里的销售产品 id 集合（子查询片段，不含外层括号）。
     */
    static String invoicedProductIdSubquery() {
        return "SELECT DISTINCT irp.sales_ledger_product_id "
                + "FROM invoice_ledger il "
                + "INNER JOIN invoice_registration_product irp ON irp.id = il.invoice_registration_product_id "
                + "WHERE irp.sales_ledger_product_id IS NOT NULL "
                + "AND (" + tenantCondIl() + ")";
    }

    @Autowired
    private DataSource dataSource;

    /**
     * 按「已开票的销售台账产品行」重建回款与收入（推荐）。
     */
    @Test
    @Transactional
    void syncReceiptByInvoicedSalesProducts_hbtmblcSchema() {
        NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(dataSource);
        MapSqlParameterSource p = tenantParams();

        String deleteIncome =
                "DELETE ai FROM account_income ai "
                        + "INNER JOIN receipt_payment rp ON ai.business_id = rp.id AND ai.business_type = 1 "
                        + "WHERE rp.sales_ledger_product_id IN (" + invoicedProductIdSubquery() + ") "
                        + "AND (" + tenantRp("rp") + ")";
        int delInc = named.update(deleteIncome, p);
        log.warn("已删除「已开票产品」关联的回款收入 account_income 行数: {}", delInc);

        String deleteReceipt =
                "DELETE rp FROM receipt_payment rp "
                        + "WHERE rp.sales_ledger_product_id IN (" + invoicedProductIdSubquery() + ") "
                        + "AND (" + tenantRp("rp") + ")";
        int delRp = named.update(deleteReceipt, p);
        log.warn("已删除「已开票产品」上的旧回款 receipt_payment 行数: {}", delRp);

        MapSqlParameterSource insRp = tenantParams().addValue("rpType", RECEIPT_PAYMENT_TYPE);
        String insertReceipt =
                "INSERT INTO receipt_payment ("
                        + "sales_ledger_id, sales_ledger_product_id, invoice_ledger_id, "
                        + "receipt_payment_type, receipt_payment_amount, registrant, receipt_payment_date, "
                        + "create_time, create_user, update_time, update_user, tenant_id) "
                        + "SELECT "
                        + "  CAST(irp.sales_ledger_id AS SIGNED), "
                        + "  CAST(irp.sales_ledger_product_id AS SIGNED), "
                        + "  il.id, "
                        + "  :rpType, "
                        + "  il.invoice_total, "
                        + "  NULLIF(TRIM(il.invoice_person), ''), "
                        + "  DATE(il.invoice_date), "
                        + "  CAST(DATE(il.invoice_date) AS DATETIME), "
                        + "  il.create_user, "
                        + "  CAST(DATE(il.invoice_date) AS DATETIME), "
                        + "  il.update_user, "
                        + "  COALESCE(irp.tenant_id, il.tenant_id) "
                        + "FROM invoice_ledger il "
                        + "INNER JOIN invoice_registration_product irp ON irp.id = il.invoice_registration_product_id "
                        + "WHERE irp.sales_ledger_product_id IS NOT NULL "
                        + "AND (" + tenantCondIl() + ")";

        int insRpCnt = named.update(insertReceipt, insRp);
        log.warn("已按开票台账插入 receipt_payment 行数: {}", insRpCnt);

        MapSqlParameterSource regP = tenantParams().addValue("defReg", DEFAULT_REGISTRANT);
        named.update(
                "UPDATE receipt_payment rp SET rp.registrant = :defReg "
                        + "WHERE (rp.registrant IS NULL OR rp.registrant = '') AND (" + tenantRp("rp") + ")",
                regP);

        MapSqlParameterSource insAi = tenantParams()
                .addValue("rpType", RECEIPT_PAYMENT_TYPE)
                .addValue("defReg", DEFAULT_REGISTRANT);
        String insertIncome =
                "INSERT INTO account_income ("
                        + "income_date, income_type, customer_name, income_money, income_described, income_method, "
                        + "invoice_number, input_user, input_time, business_id, business_type, tenant_id, "
                        + "create_time, create_user, update_time, update_user) "
                        + "SELECT "
                        + "  DATE(il.invoice_date), "
                        + "  '3', "
                        + "  sl.customer_name, "
                        + "  rp.receipt_payment_amount, "
                        + "  '回款收入', "
                        + "  :rpType, "
                        + "  il.invoice_no, "
                        + "  COALESCE(NULLIF(TRIM(rp.registrant), ''), :defReg), "
                        + "  DATE(il.invoice_date), "
                        + "  rp.id, "
                        + "  1, "
                        + "  rp.tenant_id, "
                        + "  CAST(DATE(il.invoice_date) AS DATETIME), "
                        + "  rp.create_user, "
                        + "  CAST(DATE(il.invoice_date) AS DATETIME), "
                        + "  rp.update_user "
                        + "FROM receipt_payment rp "
                        + "INNER JOIN invoice_ledger il ON il.id = rp.invoice_ledger_id "
                        + "INNER JOIN invoice_registration_product irp ON irp.id = il.invoice_registration_product_id "
                        + "INNER JOIN sales_ledger sl ON sl.id = irp.sales_ledger_id "
                        + "WHERE (" + tenantCondIl() + ") "
                        + "AND (" + tenantRp("rp") + ") "
                        + "AND NOT EXISTS ("
                        + "  SELECT 1 FROM account_income x WHERE x.business_id = rp.id AND x.business_type = 1"
                        + ")";

        int insAiCnt = named.update(insertIncome, insAi);
        log.warn("已插入回款收入 account_income 行数: {}", insAiCnt);

        assertTrue(insRpCnt >= 0 && insAiCnt >= 0);
    }

    /**
     * 清空<b>全部</b>回款与回款类收入后，仅按开票台账重建（会影响未开票产品的回款，慎用）。
     */
    @Test
    @Transactional
    @Rollback(false)
    void fullRebuildReceiptFromInvoiceLedger_hbtmblcSchema() {

        NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(dataSource);
        MapSqlParameterSource insertParams = tenantParams().addValue("rpType", RECEIPT_PAYMENT_TYPE);

        int deletedIncome = named.update("DELETE FROM account_income WHERE business_type = 1", new MapSqlParameterSource());
        log.warn("fullRebuild: 已删除全部回款类收入行数: {}", deletedIncome);

        int deletedReceipt = named.update("DELETE FROM receipt_payment", new MapSqlParameterSource());
        log.warn("fullRebuild: 已清空 receipt_payment 行数: {}", deletedReceipt);

        String insertReceipt =
                "INSERT INTO receipt_payment ("
                        + "sales_ledger_id, sales_ledger_product_id, invoice_ledger_id, "
                        + "receipt_payment_type, receipt_payment_amount, registrant, receipt_payment_date, "
                        + "create_time, create_user, update_time, update_user, tenant_id) "
                        + "SELECT "
                        + "  CAST(irp.sales_ledger_id AS SIGNED), "
                        + "  CAST(irp.sales_ledger_product_id AS SIGNED), "
                        + "  il.id, "
                        + "  :rpType, "
                        + "  il.invoice_total, "
                        + "  NULLIF(TRIM(il.invoice_person), ''), "
                        + "  DATE(il.invoice_date), "
                        + "  CAST(DATE(il.invoice_date) AS DATETIME), "
                        + "  il.create_user, "
                        + "  CAST(DATE(il.invoice_date) AS DATETIME), "
                        + "  il.update_user, "
                        + "  COALESCE(irp.tenant_id, il.tenant_id) "
                        + "FROM invoice_ledger il "
                        + "INNER JOIN invoice_registration_product irp ON irp.id = il.invoice_registration_product_id "
                        + "WHERE " + tenantCondIl();

        int insertedReceipt = named.update(insertReceipt, insertParams);
        log.warn("fullRebuild: 已插入 receipt_payment 行数: {}", insertedReceipt);

        named.update(
                "UPDATE receipt_payment SET registrant = :defReg WHERE registrant IS NULL OR registrant = ''",
                new MapSqlParameterSource("defReg", DEFAULT_REGISTRANT));

        insertParams.addValue("defReg", DEFAULT_REGISTRANT);
        String insertIncome =
                "INSERT INTO account_income ("
                        + "income_date, income_type, customer_name, income_money, income_described, income_method, "
                        + "invoice_number, input_user, input_time, business_id, business_type, tenant_id, "
                        + "create_time, create_user, update_time, update_user) "
                        + "SELECT "
                        + "  DATE(il.invoice_date), "
                        + "  '3', "
                        + "  sl.customer_name, "
                        + "  rp.receipt_payment_amount, "
                        + "  '回款收入', "
                        + "  :rpType, "
                        + "  il.invoice_no, "
                        + "  COALESCE(NULLIF(TRIM(rp.registrant), ''), :defReg), "
                        + "  DATE(il.invoice_date), "
                        + "  rp.id, "
                        + "  1, "
                        + "  rp.tenant_id, "
                        + "  CAST(DATE(il.invoice_date) AS DATETIME), "
                        + "  rp.create_user, "
                        + "  CAST(DATE(il.invoice_date) AS DATETIME), "
                        + "  rp.update_user "
                        + "FROM receipt_payment rp "
                        + "INNER JOIN invoice_ledger il ON il.id = rp.invoice_ledger_id "
                        + "INNER JOIN invoice_registration_product irp ON irp.id = il.invoice_registration_product_id "
                        + "INNER JOIN sales_ledger sl ON sl.id = irp.sales_ledger_id "
                        + "WHERE " + tenantCondIl();

        int insertedIncome = named.update(insertIncome, insertParams);
        log.warn("fullRebuild: 已插入 account_income 行数: {}", insertedIncome);

        assertTrue(insertedReceipt >= 0 && insertedIncome >= 0);
    }
}