package com.zy.third.erp.task;

import com.zy.asrs.entity.LocDetl;
import com.zy.asrs.entity.LocDetlChangelog;
import com.zy.asrs.mapper.LocDetlChangelogMapper;
import com.zy.common.service.erp.ErpSqlServer;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.HashMap;

@Slf4j
@Component
public class ERPLocDetlScheduler {

    @Autowired
    private ErpSqlServer erpSqlServer;

    @Value("${erp.enabled}")
    private Boolean erpEnabled;

    @Autowired
    private LocDetlChangelogMapper locDetlChangelogMapper;

    @Transactional(rollbackFor = Throwable.class)
    @Scheduled(cron = "${erp.refreshtime}")
    public void LocDetlScheduler() {
        if (!erpEnabled) return;

        List<LocDetlChangelog> logs = locDetlChangelogMapper.selectUnprocessedLogs();

        for (LocDetlChangelog changelog : logs) {
            try {
                String action = changelog.getActionType();
                LocDetl item = parseJsonToItem(changelog.getData());
                Map<String, Object> content = locDetlToMap(item);

                switch (action) {
                    case "INSERT":
                        String insertSql = buildInsertSql("asr_loc_detl", content);
                        erpSqlServer.update(insertSql);
                        log.info("同步ERP新增成功: locNo={}", item.getLocNo());
                        break;

                    case "UPDATE":
                        try {
                            // 解析 JSON
                            Map<String, Object> dataMap = JSON.parseObject(changelog.getData(), Map.class);
                            Map<String, Object> afterMap = (Map<String, Object>) dataMap.get("after");

                            // 拼接 UPDATE SQL
                            StringBuilder setSql = new StringBuilder();
                            afterMap.forEach((k, v) -> {
                                if (v == null) {
                                    setSql.append("[").append(k).append("]=NULL,");
                                } else if (v instanceof Number) {
                                    setSql.append("[").append(k).append("]=").append(v).append(",");
                                } else {
                                    setSql.append("[").append(k).append("]='").append(((String)v).replace("'", "''")).append("',");
                                }
                            });
                            setSql.deleteCharAt(setSql.length() - 1); // 去掉最后逗号

                            String updateSql = "UPDATE asr_loc_detl SET " + setSql + " WHERE loc_no='" + escapeSql((String)afterMap.get("loc_no")) + "' AND zpallet='" + escapeSql((String)afterMap.get("zpallet")) + "'";
                            erpSqlServer.update(updateSql);

                            log.info("同步ERP更新成功: locNo={}, zpallet={}", afterMap.get("loc_no"), afterMap.get("zpallet"));
                        } catch (Exception e) {
                            log.error("更新ERP失败: locNo=" + changelog.getLocNo(), e);
                        }

                    case "DELETE":
                        String deleteSql = "DELETE FROM asr_loc_detl WHERE loc_no='" + escapeSql(item.getLocNo()) + "'";
                        erpSqlServer.update(deleteSql);
                        log.info("同步ERP删除成功: locNo={}", item.getLocNo());
                        break;

                    default:
                        log.error("未知操作类型：" + action);
                }

                locDetlChangelogMapper.markProcessed(changelog.getId());

            } catch (Exception e) {
                log.error("同步ERP失败，日志ID：" + changelog.getId(), e);
            }
        }
    }

    /** JSON 转实体 */
    private LocDetl parseJsonToItem(String dataJson) {
        return JSON.parseObject(dataJson, LocDetl.class);
    }

    /** 实体转 Map */
    private Map<String, Object> locDetlToMap(LocDetl item) {
        Map<String, Object> map = new HashMap<>();
        map.put("loc_no", item.getLocNo());
        map.put("zpallet", item.getZpallet());
        map.put("anfme", item.getAnfme());
        map.put("matnr", item.getMatnr());
        map.put("maktx", item.getMaktx());
        map.put("batch", item.getBatch());
        map.put("order_no", item.getOrderNo());
        map.put("specs", item.getSpecs());
        map.put("model", item.getModel());
        map.put("color", item.getColor());
        map.put("brand", item.getBrand());
        map.put("unit", item.getUnit());
        map.put("price", item.getPrice());
        map.put("sku", item.getSku());
        map.put("units", item.getUnits());
        map.put("barcode", item.getBarcode());
        map.put("origin", item.getOrigin());
        map.put("manu", item.getManu());
        map.put("manu_date", item.getManuDate());
        map.put("item_num", item.getItemNum());
        map.put("safe_qty", item.getSafeQty());
        map.put("weight", item.getWeight());
        map.put("length", item.getLength());
        map.put("volume", item.getVolume());
        map.put("three_code", item.getThreeCode());
        map.put("supp", item.getSupp());
        map.put("supp_code", item.getSuppCode());
        map.put("be_batch", item.getBeBatch());
        map.put("dead_time", item.getDeadTime());
        map.put("dead_warn", item.getDeadWarn());
        map.put("source", item.getSource());
        map.put("inspect", item.getInspect());
        map.put("danger", item.getDanger());
        map.put("modi_user", item.getModiUser());
        map.put("modi_time", item.getModiTime());
        map.put("appe_user", item.getAppeUser());
        map.put("appe_time", item.getAppeTime());
        map.put("memo", item.getMemo());
        map.put("store_date", item.getStoreDate());
        map.put("out_order_no", item.getOutOrderNo());
        map.put("lu_hao", item.getLuHao());
        map.put("temp1", item.getTemp1());
        map.put("temp2", item.getTemp2());
        map.put("temp3", item.getTemp3());
        map.put("pro_type", item.getProType());
        map.put("packing", item.getPacking());
        map.put("ware_id", item.getWareId());
        map.put("ware_name", item.getWareName());
        map.put("i_no", item.getINo());
        return map;
    }

    /** 拼接 INSERT SQL */
    private String buildInsertSql(String tableName, Map<String, Object> content) {
        StringBuilder columns = new StringBuilder();
        StringBuilder values = new StringBuilder();
        content.forEach((k, v) -> {
            columns.append(k).append(",");
            values.append(toSqlValue(v)).append(",");
        });
        columns.deleteCharAt(columns.length() - 1);
        values.deleteCharAt(values.length() - 1);
        return "INSERT INTO " + tableName + " (" + columns + ") VALUES (" + values + ")";
    }

    /** 拼接 UPDATE SQL */
    private String buildUpdateSql(String tableName, Map<String, Object> content, String whereClause) {
        StringBuilder setSql = new StringBuilder();
        content.forEach((k, v) -> {
            setSql.append(k).append("=").append(toSqlValue(v)).append(",");
        });
        setSql.deleteCharAt(setSql.length() - 1);
        return "UPDATE " + tableName + " SET " + setSql + " WHERE " + whereClause;
    }

    /** 转换为 SQL 可用的值 */
    private String toSqlValue(Object value) {
        if (value == null) return "NULL";
        if (value instanceof String) return "'" + escapeSql((String) value) + "'";
        if (value instanceof Date) return "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) value) + "'";
        return value.toString();
    }

    /** SQL 注入简单转义 */
    private String escapeSql(String str) {
        if (str == null) return "";
        return str.replace("'", "''");
    }
}