package com.erp.service;

import com.erp.common.BaseService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;
import java.util.regex.Pattern;

@Service
@Slf4j
public class ShippingPlanService extends BaseService {

    @Autowired private CommonService commonService;
    @Autowired private JdbcTemplate jdbcTemplate;

    private static final Pattern UUID_PATTERN = Pattern.compile(
        "^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$",
        Pattern.CASE_INSENSITIVE
    );

    private boolean isUUID(String val) {
        return val != null && UUID_PATTERN.matcher(val).matches();
    }

    private String detectSource(List<String> ids) {
        if (ids.isEmpty()) return "detail";
        return ids.stream().allMatch(this::isUUID) ? "detail" : "master";
    }

    // ─── 기본 CRUD ───

    public Map<String, Object> getShippingPlanList(Map<String, Object> params) {
        commonService.applyCompanyCodeFilter(params);
        commonService.applyPagination(params);
        Number cntNum = sqlSession.selectOne("shippingPlan.getShippingPlanListCnt", params);
        int totalCount = cntNum != null ? cntNum.intValue() : 0;
        List<Map<String, Object>> list = sqlSession.selectList("shippingPlan.getShippingPlanList", params);
        return commonService.buildListResponse(list, totalCount, params);
    }

    public Map<String, Object> getShippingPlanInfo(Map<String, Object> params) {
        commonService.applyCompanyCodeFilter(params);
        return sqlSession.selectOne("shippingPlan.getShippingPlanInfo", params);
    }

    @Transactional
    public Map<String, Object> insertShippingPlan(Map<String, Object> params) {
        commonService.applyCompanyCodeFilter(params);
        sqlSession.insert("shippingPlan.insertShippingPlan", params);
        return params;
    }

    @Transactional
    public Map<String, Object> updateShippingPlan(Map<String, Object> params) {
        commonService.applyCompanyCodeFilter(params);
        sqlSession.update("shippingPlan.updateShippingPlan", params);
        return params;
    }

    @Transactional
    public Map<String, Object> deleteShippingPlan(Map<String, Object> params) {
        commonService.applyCompanyCodeFilter(params);
        sqlSession.delete("shippingPlan.deleteShippingPlan", params);
        return params;
    }

    // ─── 품목별 집계 + 기존 출하계획 조회 ───

    public Map<String, Object> getAggregate(Map<String, Object> params) {
        String companyCode = (String) params.get("company_code");
        String idsParam = (String) params.get("ids");

        if (idsParam == null || idsParam.isBlank()) {
            throw new IllegalArgumentException("ids 파라미터가 필요합니다");
        }

        List<String> idList = Arrays.stream(idsParam.split(","))
            .map(String::trim).filter(s -> !s.isEmpty()).toList();

        if (idList.isEmpty()) {
            throw new IllegalArgumentException("유효한 ID가 필요합니다");
        }

        String source = detectSource(idList);
        List<Map<String, Object>> orders = getNormalizedOrders(companyCode, idList, source);

        if (orders.isEmpty()) {
            throw new NoSuchElementException("해당 수주를 찾을 수 없습니다");
        }

        // 품목별 그룹핑
        Map<String, List<Map<String, Object>>> partCodeMap = new LinkedHashMap<>();
        for (Map<String, Object> order : orders) {
            String partCode = order.get("part_code") != null ? order.get("part_code").toString() : "UNKNOWN";
            partCodeMap.computeIfAbsent(partCode, k -> new ArrayList<>()).add(order);
        }

        Map<String, Object> resultData = new LinkedHashMap<>();
        for (Map.Entry<String, List<Map<String, Object>>> entry : partCodeMap.entrySet()) {
            String partCode = entry.getKey();
            List<Map<String, Object>> partOrders = entry.getValue();

            double totalBalance = partOrders.stream().mapToDouble(o -> {
                double balanceQty = toDouble(o.get("balance_qty"));
                double orderQty = toDouble(o.get("order_qty"));
                double shipQty = toDouble(o.get("ship_qty"));
                return balanceQty > 0 ? balanceQty : orderQty - shipQty;
            }).sum();

            // 기존 출하계획 조회
            List<Map<String, Object>> existingPlans = new ArrayList<>();
            if ("detail".equals(source)) {
                List<String> detailIds = partOrders.stream()
                    .map(o -> o.get("detail_id") != null ? o.get("detail_id").toString() : null)
                    .filter(Objects::nonNull).toList();
                if (!detailIds.isEmpty()) {
                    String placeholders = String.join(",", Collections.nCopies(detailIds.size(), "?"));
                    List<Object> qParams = new ArrayList<>();
                    qParams.add(companyCode);
                    qParams.addAll(detailIds);
                    existingPlans = jdbcTemplate.queryForList(
                        "SELECT id, detail_id, sales_order_id, plan_qty, plan_date, shipment_plan_no, status " +
                        "FROM shipment_plan WHERE company_code = ? AND detail_id IN (" + placeholders + ") " +
                        "ORDER BY created_date DESC",
                        qParams.toArray()
                    );
                }
            } else {
                List<Integer> masterIds = partOrders.stream()
                    .map(o -> o.get("master_id") != null ? ((Number) o.get("master_id")).intValue() : null)
                    .filter(Objects::nonNull).toList();
                if (!masterIds.isEmpty()) {
                    String placeholders = String.join(",", Collections.nCopies(masterIds.size(), "?"));
                    List<Object> qParams = new ArrayList<>();
                    qParams.add(companyCode);
                    qParams.addAll(masterIds);
                    existingPlans = jdbcTemplate.queryForList(
                        "SELECT id, sales_order_id, detail_id, plan_qty, plan_date, shipment_plan_no, status " +
                        "FROM shipment_plan WHERE company_code = ? AND sales_order_id IN (" + placeholders + ") " +
                        "ORDER BY created_date DESC",
                        qParams.toArray()
                    );
                }
            }

            double totalPlanQty = existingPlans.stream()
                .mapToDouble(p -> toDouble(p.get("plan_qty"))).sum();

            // 현재고
            List<Map<String, Object>> stockRows = jdbcTemplate.queryForList(
                "SELECT COALESCE(SUM(current_qty::numeric), 0) AS current_stock " +
                "FROM inventory_stock WHERE company_code = ? AND item_code = ?",
                companyCode, partCode
            );
            double currentStock = toDouble(stockRows.isEmpty() ? null : stockRows.get(0).get("current_stock"));

            // 생산중수량
            List<Map<String, Object>> prodRows = jdbcTemplate.queryForList(
                "SELECT COALESCE(SUM(plan_qty - COALESCE(completed_qty, 0)), 0) AS in_production " +
                "FROM production_plan_mng WHERE company_code = ? AND item_code = ? " +
                "AND status IN ('in_progress', 'planned')",
                companyCode, partCode
            );
            double inProductionQty = toDouble(prodRows.isEmpty() ? null : prodRows.get(0).get("in_production"));

            Map<String, Object> partResult = new LinkedHashMap<>();
            partResult.put("total_balance", totalBalance);
            partResult.put("total_plan_qty", totalPlanQty);
            partResult.put("current_stock", currentStock);
            partResult.put("available_stock", currentStock - totalPlanQty);
            partResult.put("in_production_qty", inProductionQty);
            partResult.put("existing_plans", existingPlans);
            partResult.put("orders", partOrders);
            resultData.put(partCode, partResult);
        }

        Map<String, Object> response = new HashMap<>();
        response.put("data", resultData);
        response.put("source", source);
        return response;
    }

    private List<Map<String, Object>> getNormalizedOrders(String companyCode, List<String> ids, String source) {
        if ("detail".equals(source)) {
            String placeholders = String.join(",", Collections.nCopies(ids.size(), "?"));
            List<Object> qParams = new ArrayList<>();
            qParams.add(companyCode);
            qParams.addAll(ids);
            return jdbcTemplate.queryForList(
                "SELECT d.id AS detail_id, m.id AS master_id, d.order_no, d.part_code, " +
                "COALESCE(d.part_name, i.item_name, d.part_code) AS part_name, " +
                "COALESCE(d.delivery_partner_code, m.partner_id, '') AS partner_code, " +
                "COALESCE(c.customer_name, d.delivery_partner_code, m.partner_id, '') AS partner_name, " +
                "COALESCE(d.due_date, m.due_date::text, '') AS due_date, " +
                "COALESCE(NULLIF(d.qty,'')::numeric, m.order_qty, 0) AS order_qty, " +
                "COALESCE(NULLIF(d.ship_qty,'')::numeric, m.ship_qty, 0) AS ship_qty, " +
                "COALESCE(NULLIF(d.balance_qty,'')::numeric, m.balance_qty, 0) AS balance_qty " +
                "FROM sales_order_detail d " +
                "LEFT JOIN sales_order_mng m ON d.order_no = m.order_no AND d.company_code = m.company_code " +
                "LEFT JOIN LATERAL (SELECT item_name FROM item_info " +
                "  WHERE item_number = d.part_code AND company_code = d.company_code LIMIT 1) i ON true " +
                "LEFT JOIN customer_mng c " +
                "  ON COALESCE(d.delivery_partner_code, m.partner_id) = c.customer_code " +
                "  AND d.company_code = c.company_code " +
                "WHERE d.company_code = ? AND d.id IN (" + placeholders + ")",
                qParams.toArray()
            );
        } else {
            List<Integer> numericIds = ids.stream()
                .map(id -> { try { return Integer.parseInt(id); } catch (NumberFormatException e) { return null; } })
                .filter(Objects::nonNull).toList();
            if (numericIds.isEmpty()) return Collections.emptyList();
            String placeholders = String.join(",", Collections.nCopies(numericIds.size(), "?"));
            List<Object> qParams = new ArrayList<>();
            qParams.add(companyCode);
            qParams.addAll(numericIds);
            return jdbcTemplate.queryForList(
                "SELECT m.id AS master_id, NULL AS detail_id, m.order_no, m.part_code, " +
                "COALESCE(m.part_name, i.item_name, m.part_code, '') AS part_name, " +
                "COALESCE(m.partner_id, '') AS partner_code, " +
                "COALESCE(c.customer_name, m.partner_id, '') AS partner_name, " +
                "COALESCE(m.due_date::text, '') AS due_date, " +
                "COALESCE(m.order_qty, 0) AS order_qty, " +
                "COALESCE(m.ship_qty, 0) AS ship_qty, " +
                "COALESCE(m.balance_qty, 0) AS balance_qty " +
                "FROM sales_order_mng m " +
                "LEFT JOIN LATERAL (SELECT item_name FROM item_info " +
                "  WHERE item_number = m.part_code AND company_code = m.company_code LIMIT 1) i ON true " +
                "LEFT JOIN customer_mng c " +
                "  ON m.partner_id = c.customer_code AND m.company_code = c.company_code " +
                "WHERE m.company_code = ? AND m.id IN (" + placeholders + ")",
                qParams.toArray()
            );
        }
    }

    // ─── 출하계획 일괄 저장 ───

    @Transactional
    public Map<String, Object> batchSave(Map<String, Object> params) {
        String companyCode = (String) params.get("company_code");
        String userId = (String) params.get("user_id");

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> plans = (List<Map<String, Object>>) params.get("plans");
        String source = (String) params.get("source");

        if (plans == null || plans.isEmpty()) {
            throw new IllegalArgumentException("저장할 출하계획 데이터가 필요합니다");
        }

        if (source == null) {
            List<String> sourceIds = plans.stream()
                .map(p -> p.get("source_id") != null ? p.get("source_id").toString() : "")
                .toList();
            source = detectSource(sourceIds);
        }

        List<Map<String, Object>> savedPlans = new ArrayList<>();
        final String detectedSource = source;

        for (Map<String, Object> plan : plans) {
            String sourceId = plan.get("source_id") != null ? plan.get("source_id").toString() : null;
            if (sourceId == null) continue;

            double planQty = toDouble(plan.get("plan_qty"));
            if (planQty <= 0) continue;

            String planDate = plan.get("plan_date") != null ? plan.get("plan_date").toString() : null;

            if ("detail".equals(detectedSource)) {
                List<Map<String, Object>> detailRows = jdbcTemplate.queryForList(
                    "SELECT d.id, d.order_no, d.qty, d.ship_qty, d.balance_qty, m.id AS master_id " +
                    "FROM sales_order_detail d " +
                    "LEFT JOIN sales_order_mng m ON d.order_no = m.order_no AND d.company_code = m.company_code " +
                    "WHERE d.id = ? AND d.company_code = ?",
                    sourceId, companyCode
                );
                if (detailRows.isEmpty()) {
                    throw new IllegalArgumentException("수주상세 " + sourceId + "을 찾을 수 없습니다");
                }

                Map<String, Object> detail = detailRows.get(0);
                double qty = toDouble(detail.get("qty"));
                double shipQty = toDouble(detail.get("ship_qty"));
                Object balanceRaw = detail.get("balance_qty");
                double balanceQty = (balanceRaw != null && !balanceRaw.toString().isBlank())
                    ? toDouble(balanceRaw) : qty - shipQty;

                if (balanceQty > 0 && planQty > balanceQty) {
                    throw new IllegalArgumentException("수주번호 " + detail.get("order_no") +
                        ": 출하계획량(" + planQty + ")이 미출하량(" + balanceQty + ")을 초과합니다");
                }

                Object masterId = detail.get("master_id");
                Map<String, Object> inserted = jdbcTemplate.queryForMap(
                    "INSERT INTO shipment_plan " +
                    "(company_code, detail_id, sales_order_id, plan_qty, plan_date, status, created_by, created_date) " +
                    "VALUES (?, ?, ?, ?, COALESCE(?::date, CURRENT_DATE), 'READY', ?, NOW()) RETURNING *",
                    companyCode, sourceId, masterId, planQty, planDate, userId
                );
                savedPlans.add(inserted);

                jdbcTemplate.update(
                    "UPDATE sales_order_detail " +
                    "SET ship_qty = (COALESCE(NULLIF(ship_qty,'')::numeric, 0) + ?)::text, " +
                    "    balance_qty = (COALESCE(NULLIF(qty,'')::numeric, 0) - COALESCE(NULLIF(ship_qty,'')::numeric, 0) - ?)::text, " +
                    "    updated_date = NOW() " +
                    "WHERE id = ? AND company_code = ?",
                    planQty, planQty, sourceId, companyCode
                );

            } else {
                int masterId;
                try {
                    masterId = Integer.parseInt(sourceId);
                } catch (NumberFormatException e) {
                    throw new IllegalArgumentException("유효하지 않은 수주 ID: " + sourceId);
                }

                List<Map<String, Object>> masterRows = jdbcTemplate.queryForList(
                    "SELECT id, order_no, order_qty, ship_qty, balance_qty FROM sales_order_mng " +
                    "WHERE id = ? AND company_code = ?",
                    masterId, companyCode
                );
                if (masterRows.isEmpty()) {
                    throw new IllegalArgumentException("수주 ID " + masterId + "을 찾을 수 없습니다");
                }

                Map<String, Object> master = masterRows.get(0);
                double balanceQty = toDouble(master.get("balance_qty"));

                if (balanceQty > 0 && planQty > balanceQty) {
                    throw new IllegalArgumentException("수주번호 " + master.get("order_no") +
                        ": 출하계획량(" + planQty + ")이 미출하량(" + balanceQty + ")을 초과합니다");
                }

                Map<String, Object> inserted = jdbcTemplate.queryForMap(
                    "INSERT INTO shipment_plan " +
                    "(company_code, sales_order_id, plan_qty, plan_date, status, created_by, created_date) " +
                    "VALUES (?, ?, ?, COALESCE(?::date, CURRENT_DATE), 'READY', ?, NOW()) RETURNING *",
                    companyCode, masterId, planQty, planDate, userId
                );
                savedPlans.add(inserted);

                jdbcTemplate.update(
                    "UPDATE sales_order_mng " +
                    "SET ship_qty = COALESCE(ship_qty, 0) + ?, " +
                    "    balance_qty = COALESCE(order_qty, 0) - COALESCE(ship_qty, 0) - ?, " +
                    "    updated_date = NOW() " +
                    "WHERE id = ? AND company_code = ?",
                    planQty, planQty, masterId, companyCode
                );
            }
        }

        Map<String, Object> result = new HashMap<>();
        result.put("saved_count", savedPlans.size());
        result.put("message", savedPlans.size() + "건 저장 완료");
        result.put("data", savedPlans);
        return result;
    }

    private double toDouble(Object val) {
        if (val == null) return 0.0;
        if (val instanceof Number) return ((Number) val).doubleValue();
        try { return Double.parseDouble(val.toString()); } catch (NumberFormatException e) { return 0.0; }
    }
}
