package com.erp.systemtool.service;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

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

/**
 * Data Consistency Service
 * 
 * Checks and fixes data consistency issues across the system.
 * Migrations from PHP DataConsistencyService.
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class DataConsistencyService {

    private final JdbcTemplate jdbcTemplate;

    /**
     * Scheduled full consistency check (runs daily at 1 AM)
     */
    @Scheduled(cron = "0 0 1 * * ?")
    public Map<String, Object> scheduledFullCheck() {
        return fullCheck();
    }

    /**
     * Execute full consistency check across all modules
     */
    public Map<String, Object> fullCheck() {
        Map<String, Object> results = new HashMap<>();
        results.put("stock", checkAndFixStockConsistency());
        results.put("order_stock", checkOrderStockConsistency());
        results.put("waybill", checkWaybillConsistency());
        results.put("finance", checkFinanceConsistency());
        results.put("checked_at", LocalDateTime.now().toString());
        return results;
    }

    /**
     * Check and fix stock consistency
     * Compares actual stock quantities with calculated from stock logs
     */
    public Map<String, Object> checkAndFixStockConsistency() {
        Map<String, Object> results = new LinkedHashMap<>();
        results.put("checked", 0);
        results.put("fixed", 0);
        results.put("errors", new ArrayList<>());

        try {
            // Get all stock records
            List<Map<String, Object>> stocks = jdbcTemplate.queryForList(
                "SELECT id, sku_code, warehouse_id, quantity FROM stocks"
            );

            for (Map<String, Object> stock : stocks) {
                Long stockId = ((Number) stock.get("id")).longValue();
                String skuCode = (String) stock.get("sku_code");
                Integer warehouseId = ((Number) stock.get("warehouse_id")).intValue();
                Integer currentQuantity = ((Number) stock.get("quantity")).intValue();

                results.put("checked", (int) results.get("checked") + 1);

                // Calculate actual quantity from stock logs
                Integer actualQuantity = calculateActualQuantity(skuCode, warehouseId);

                // Check if不一致
                if (!currentQuantity.equals(actualQuantity)) {
                    int diff = actualQuantity - currentQuantity;

                    log.warn("Stock inconsistency detected: sku={}, warehouse={}, current={}, actual={}, diff={}",
                        skuCode, warehouseId, currentQuantity, actualQuantity, diff);

                    // Fix the stock quantity
                    jdbcTemplate.update(
                        "UPDATE stocks SET quantity = ?, updated_at = NOW() WHERE id = ?",
                        actualQuantity, stockId
                    );

                    results.put("fixed", (int) results.get("fixed") + 1);

                    @SuppressWarnings("unchecked")
                    List<Map<String, Object>> errors = (List<Map<String, Object>>) results.get("errors");
                    errors.add(Map.of(
                        "sku_code", skuCode,
                        "warehouse_id", warehouseId,
                        "old_quantity", currentQuantity,
                        "actual_quantity", actualQuantity,
                        "diff", diff
                    ));
                }
            }
        } catch (Exception e) {
            log.error("Error checking stock consistency: {}", e.getMessage(), e);
            results.put("error", e.getMessage());
        }

        return results;
    }

    /**
     * Calculate actual stock quantity from stock logs
     */
    private Integer calculateActualQuantity(String skuCode, Integer warehouseId) {
        // Get initial stock
        try {
            List<Map<String, Object>> initial = jdbcTemplate.queryForList(
                "SELECT quantity FROM stock_initial WHERE sku_code = ? AND warehouse_id = ?",
                skuCode, warehouseId
            );
            int initialQuantity = initial.isEmpty() ? 0 : 
                ((Number) initial.get(0).get("quantity")).intValue();

            // Sum all changes from stock logs
            List<Map<String, Object>> changes = jdbcTemplate.queryForList(
                "SELECT SUM(change_quantity) as total FROM stock_logs " +
                "WHERE sku_code = ? AND warehouse_id = ?",
                skuCode, warehouseId
            );
            int totalChange = changes.isEmpty() || changes.get(0).get("total") == null ? 0 :
                ((Number) changes.get(0).get("total")).intValue();

            return initialQuantity + totalChange;
        } catch (Exception e) {
            log.warn("Error calculating actual quantity for sku={}, warehouse={}: {}",
                skuCode, warehouseId, e.getMessage());
            return 0;
        }
    }

    /**
     * Check order-stock consistency
     * Verifies that shipped orders have corresponding stock outbound records
     */
    public Map<String, Object> checkOrderStockConsistency() {
        Map<String, Object> results = new LinkedHashMap<>();
        results.put("checked", 0);
        results.put("issues", new ArrayList<>());

        try {
            // Get shipped orders without express number or with issues
            List<Map<String, Object>> orders = jdbcTemplate.queryForList(
                "SELECT id, short_id, erp_status, express_no FROM erp_orders " +
                "WHERE erp_status = 'shipped' AND express_no IS NOT NULL"
            );

            for (Map<String, Object> order : orders) {
                Long orderId = ((Number) order.get("id")).longValue();
                String orderSn = (String) order.get("short_id");

                results.put("checked", (int) results.get("checked") + 1);

                // Check for outbound log
                List<Map<String, Object>> outboundLogs = jdbcTemplate.queryForList(
                    "SELECT id FROM stock_logs WHERE order_id = ? AND change_type = 'outbound' LIMIT 1",
                    orderId
                );

                if (outboundLogs.isEmpty()) {
                    @SuppressWarnings("unchecked")
                    List<Map<String, Object>> issues = (List<Map<String, Object>>) results.get("issues");
                    issues.add(Map.of(
                        "order_id", orderId,
                        "order_sn", orderSn,
                        "issue", "Shipped order without outbound record",
                        "severity", "high"
                    ));
                }

                // Check for unconfirmed freeze
                List<Map<String, Object>> freezes = jdbcTemplate.queryForList(
                    "SELECT id FROM stock_freezes WHERE order_id = ? AND status = 'frozen' LIMIT 1",
                    orderId
                );

                if (!freezes.isEmpty()) {
                    @SuppressWarnings("unchecked")
                    List<Map<String, Object>> issues = (List<Map<String, Object>>) results.get("issues");
                    issues.add(Map.of(
                        "order_id", orderId,
                        "order_sn", orderSn,
                        "issue", "Unconfirmed freeze record exists",
                        "severity", "medium"
                    ));
                }
            }
        } catch (Exception e) {
            log.error("Error checking order-stock consistency: {}", e.getMessage(), e);
            results.put("error", e.getMessage());
        }

        return results;
    }

    /**
     * Check waybill-logistics consistency
     */
    public Map<String, Object> checkWaybillConsistency() {
        Map<String, Object> results = new LinkedHashMap<>();
        results.put("checked", 0);
        results.put("issues", new ArrayList<>());

        try {
            // Get used waybills
            List<Map<String, Object>> waybills = jdbcTemplate.queryForList(
                "SELECT id, waybill_no, status FROM waybills WHERE status = 'used' AND waybill_no IS NOT NULL"
            );

            for (Map<String, Object> waybill : waybills) {
                Long waybillId = ((Number) waybill.get("id")).longValue();
                String waybillNo = (String) waybill.get("waybill_no");

                results.put("checked", (int) results.get("checked") + 1);

                // Check for logistics traces
                List<Map<String, Object>> traces = jdbcTemplate.queryForList(
                    "SELECT id FROM logistics_traces WHERE waybill_no = ? LIMIT 1",
                    waybillNo
                );

                if (traces.isEmpty()) {
                    @SuppressWarnings("unchecked")
                    List<Map<String, Object>> issues = (List<Map<String, Object>>) results.get("issues");
                    issues.add(Map.of(
                        "waybill_id", waybillId,
                        "waybill_no", waybillNo,
                        "issue", "Used waybill without logistics traces",
                        "severity", "low"
                    ));
                }

                // Check for stale traces (> 24 hours without update)
                List<Map<String, Object>> lastStatus = jdbcTemplate.queryForList(
                    "SELECT status, updated_at FROM waybill_statuses WHERE waybill_no = ? ORDER BY updated_at DESC LIMIT 1",
                    waybillNo
                );

                if (!lastStatus.isEmpty()) {
                    String status = (String) lastStatus.get(0).get("status");
                    Object updatedAtObj = lastStatus.get(0).get("updated_at");
                    
                    if (!isFinalStatus(status) && isStale(updatedAtObj)) {
                        @SuppressWarnings("unchecked")
                        List<Map<String, Object>> issues = (List<Map<String, Object>>) results.get("issues");
                        issues.add(Map.of(
                            "waybill_id", waybillId,
                            "waybill_no", waybillNo,
                            "issue", "Logistics traces stale (>24h), current status: " + status,
                            "severity", "medium"
                        ));
                    }
                }
            }
        } catch (Exception e) {
            log.error("Error checking waybill consistency: {}", e.getMessage(), e);
            results.put("error", e.getMessage());
        }

        return results;
    }

    private boolean isFinalStatus(String status) {
        return "signed".equals(status) || "rejected".equals(status) || "returned".equals(status);
    }

    private boolean isStale(Object updatedAtObj) {
        if (updatedAtObj == null) return false;
        try {
            LocalDateTime updatedAt;
            if (updatedAtObj instanceof java.sql.Timestamp) {
                updatedAt = ((java.sql.Timestamp) updatedAtObj).toLocalDateTime();
            } else if (updatedAtObj instanceof LocalDateTime) {
                updatedAt = (LocalDateTime) updatedAtObj;
            } else {
                return false;
            }
            return updatedAt.isBefore(LocalDateTime.now().minusHours(24));
        } catch (Exception e) {
            return false;
        }
    }

    /**
     * Check finance consistency
     * Verifies order amounts match calculated amounts
     */
    public Map<String, Object> checkFinanceConsistency() {
        Map<String, Object> results = new LinkedHashMap<>();
        results.put("checked", 0);
        results.put("issues", new ArrayList<>());

        try {
            // Get orders from last month
            List<Map<String, Object>> orders = jdbcTemplate.queryForList(
                "SELECT id, short_id, total_amount, goods_amount, freight, discount_amount " +
                "FROM erp_orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)"
            );

            BigDecimal tolerance = new BigDecimal("0.01");

            for (Map<String, Object> order : orders) {
                Long orderId = ((Number) order.get("id")).longValue();
                String orderSn = (String) order.get("short_id");
                BigDecimal totalAmount = (BigDecimal) order.get("total_amount");
                BigDecimal goodsAmount = order.get("goods_amount") != null ? 
                    new BigDecimal(order.get("goods_amount").toString()) : BigDecimal.ZERO;
                BigDecimal freight = order.get("freight") != null ? 
                    new BigDecimal(order.get("freight").toString()) : BigDecimal.ZERO;
                BigDecimal discountAmount = order.get("discount_amount") != null ? 
                    new BigDecimal(order.get("discount_amount").toString()) : BigDecimal.ZERO;

                results.put("checked", (int) results.get("checked") + 1);

                BigDecimal calculated = goodsAmount.add(freight).subtract(discountAmount);
                BigDecimal diff = totalAmount.subtract(calculated).abs();

                if (diff.compareTo(tolerance) > 0) {
                    @SuppressWarnings("unchecked")
                    List<Map<String, Object>> issues = (List<Map<String, Object>>) results.get("issues");
                    issues.add(Map.of(
                        "order_id", orderId,
                        "order_sn", orderSn,
                        "issue", "Order amount inconsistency",
                        "details", Map.of(
                            "total_amount", totalAmount,
                            "calculated", calculated,
                            "diff", diff
                        ),
                        "severity", "high"
                    ));
                }
            }
        } catch (Exception e) {
            log.error("Error checking finance consistency: {}", e.getMessage(), e);
            results.put("error", e.getMessage());
        }

        return results;
    }
}
