package com.xly.tool;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.xly.service.AuthzService;
import com.xly.service.ErpClient;
import com.xly.service.OpService;
import dev.langchain4j.agent.tool.P;
import dev.langchain4j.agent.tool.Tool;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * ProposeWrite 工具（写操作，人在环）。
 *
 * <p>**只提议并暂存，绝不立即执行**：把「改某条记录的某字段」解析成具体的 表/记录id/列/新值，
 * 写一条 draft 到 ai_op_queue，返回一个提议。真正执行发生在用户点【确认】后的确定性端点里
 * （见 OpController），不经过 LLM。
 */
@Component
public class ProposeWriteTool {

    private final ErpClient erp;
    private final JdbcTemplate jdbc;
    private final OpService ops;
    private final ObjectMapper mapper;
    private final AuthzService authz;

    public ProposeWriteTool(ErpClient erp, JdbcTemplate jdbc, OpService ops, ObjectMapper mapper, AuthzService authz) {
        this.erp = erp;
        this.jdbc = jdbc;
        this.ops = ops;
        this.mapper = mapper;
        this.authz = authz;
    }

    @Tool("提议修改某条现有记录的某个字段（写操作）。本工具**只提议并暂存、绝不立即执行**——"
            + "必须等用户在对话内点【确认】后才真正修改。用于「把某个客户/物料的某字段改成X」这类需求。"
            + "直接给出实体类型（如 客户）即可，本工具会自行定位主表，无需先 findForms。")
    public String proposeUpdate(
            @P("实体/单据类型关键词，如 客户 / 物料 / 供应商") String entityKeyword,
            @P("要修改的那条记录的名称关键词（如某个客户名）") String recordKeyword,
            @P("要修改的字段中文名（如 简称 / 备注 / 联系电话）") String fieldChinese,
            @P("修改后的新值") String newValue) {

        if (isBlank(entityKeyword) || isBlank(recordKeyword) || isBlank(fieldChinese)) {
            return err("缺少信息：需要实体类型、记录名称关键词、字段中文名、新值。");
        }

        // 1) 定位可修改的主表（该实体名下、table 类型、最常用的一张）
        Map<String, Object> form = resolveForm(entityKeyword.trim());
        if (form == null) {
            return err("找不到「" + entityKeyword + "」对应的可修改主表。");
        }
        String formId = str(form.get("sFormId"));
        String moduleId = str(form.get("sModuleId"));
        String table = str(form.get("sDataSource"));
        if (!authz.canAccessModule(moduleId)) {
            return err("你没有修改「" + entityKeyword + "」的权限。");
        }

        // 2) 字段中文名 -> 技术列名
        String field = queryOne(
                "SELECT sField FROM viw_kg_field_dict WHERE sTable=? AND sChinese=? ORDER BY iFormUses DESC LIMIT 1",
                table, fieldChinese.trim());
        if (field == null) {
            field = queryOne(
                    "SELECT sField FROM viw_kg_field_dict WHERE sTable=? AND sChinese LIKE ? ORDER BY iFormUses DESC LIMIT 1",
                    table, "%" + fieldChinese.trim() + "%");
        }
        if (field == null) {
            return err("在该表单里找不到叫「" + fieldChinese + "」的字段，请换个字段名或先查看该表单有哪些字段。");
        }

        // 3) 定位唯一记录 + 旧值（用名称字段过滤）
        String nameField = queryOne(
                "SELECT sField FROM viw_kg_field_dict WHERE sTable=? AND sField LIKE '%Name' AND sField NOT LIKE '%NameId' " +
                        "ORDER BY iFormUses DESC LIMIT 1", table);
        JsonNode root;
        try {
            root = erp.readForm(formId.trim(), moduleId.trim(), 1, 5, nameField, recordKeyword.trim());
        } catch (Exception e) {
            return err("定位记录时读取失败：" + e.getMessage());
        }
        if (root.path("code").asInt(0) < 0) {
            return err("定位记录失败：" + root.path("msg").asText("未知错误"));
        }
        JsonNode rows = root.path("dataset").path("rows");
        JsonNode data = (rows.isArray() && rows.size() > 0) ? rows.get(0).path("dataSet") : null;
        int n = (data != null && data.isArray()) ? data.size() : 0;
        if (n == 0) {
            return err("没有找到名称含「" + recordKeyword + "」的记录，无法修改。");
        }
        if (n > 1) {
            StringBuilder names = new StringBuilder();
            for (int i = 0; i < data.size() && i < 5; i++) {
                if (i > 0) names.append("、");
                names.append(data.get(i).path(nameField == null ? "" : nameField).asText(""));
            }
            return err("匹配到多条记录（" + names + "），请提供更精确的名称，只改其中一条。");
        }

        JsonNode rec = data.get(0);
        String billId = rec.path("sId").asText(null);
        if (isBlank(billId)) {
            return err("定位到的记录缺少主键 sId，无法安全修改。");
        }
        String oldValue = rec.path(field).asText("");
        String recordName = nameField == null ? recordKeyword : rec.path(nameField).asText(recordKeyword);

        // 4) 暂存 draft（不执行）
        String description = "将【" + recordName + "】的【" + fieldChinese + "】"
                + (oldValue.isBlank() ? "" : ("由「" + oldValue + "」")) + "改为「" + newValue + "」";
        String opId = ops.createDraft("agent", "update", formId.trim(), moduleId.trim(), table, billId,
                field, fieldChinese, oldValue, newValue, description);

        Map<String, Object> out = new LinkedHashMap<>();
        out.put("opId", opId);
        out.put("summary", description);
        out.put("message", "已为你生成一条待确认的修改，请在下方点【确认】执行、或【取消】。");
        return toJson(out);
    }

    @Tool("提议**删除**某条现有记录（写操作）。本工具只提议并暂存、绝不立即执行——必须等用户点【确认】后才真正删除。"
            + "删除不可恢复，请慎用。直接给出实体类型与记录名即可。")
    public String proposeDelete(
            @P("实体类型，如 客户 / 物料 / 供应商") String entityKeyword,
            @P("要删除的记录的名称关键词") String recordKeyword) {

        if (isBlank(entityKeyword) || isBlank(recordKeyword)) {
            return err("缺少实体类型或记录名称。");
        }
        Map<String, Object> form = resolveForm(entityKeyword.trim());
        if (form == null) {
            return err("找不到「" + entityKeyword + "」对应的可操作主表。");
        }
        String formId = str(form.get("sFormId"));
        String moduleId = str(form.get("sModuleId"));
        String table = str(form.get("sDataSource"));
        if (!authz.canAccessModule(moduleId)) {
            return err("你没有操作「" + entityKeyword + "」的权限。");
        }
        String nameField = queryOne(
                "SELECT sField FROM viw_kg_field_dict WHERE sTable=? AND sField LIKE '%Name' AND sField NOT LIKE '%NameId' " +
                        "ORDER BY iFormUses DESC LIMIT 1", table);
        JsonNode root;
        try {
            root = erp.readForm(formId, moduleId, 1, 5, nameField, recordKeyword.trim());
        } catch (Exception e) {
            return err("定位记录失败：" + e.getMessage());
        }
        if (root.path("code").asInt(0) < 0) {
            return err("定位记录失败：" + root.path("msg").asText("未知错误"));
        }
        JsonNode rows = root.path("dataset").path("rows");
        JsonNode data = (rows.isArray() && rows.size() > 0) ? rows.get(0).path("dataSet") : null;
        int n = (data != null && data.isArray()) ? data.size() : 0;
        if (n == 0) {
            return err("没有找到名称含「" + recordKeyword + "」的记录。");
        }
        if (n > 1) {
            StringBuilder names = new StringBuilder();
            for (int i = 0; i < data.size() && i < 5; i++) {
                if (i > 0) names.append("、");
                names.append(nameField == null ? "" : data.get(i).path(nameField).asText(""));
            }
            return err("匹配到多条记录（" + names + "），请提供更精确的名称，只删其中一条。");
        }
        JsonNode rec = data.get(0);
        String billId = rec.path("sId").asText(null);
        if (isBlank(billId)) {
            return err("定位到的记录缺少主键 sId，无法安全删除。");
        }
        String recordName = nameField == null ? recordKeyword : rec.path(nameField).asText(recordKeyword);
        String description = "删除【" + recordName + "】（" + entityKeyword + "）";
        String opId = ops.createDraft("agent", "delete", formId, moduleId, table, billId,
                null, null, recordName, null, description);

        Map<String, Object> out = new LinkedHashMap<>();
        out.put("opId", opId);
        out.put("summary", description);
        out.put("message", "已为你生成一条待确认的删除，请在下方点【确认】执行、或【取消】。删除不可恢复，请谨慎。");
        return toJson(out);
    }

    @Tool("提议**新增**一条记录（写操作）。只提议并暂存、绝不立即执行——用户点确认后才真正新增。"
            + "给出实体类型 + 已知字段（JSON：字段中文名->值）；主键与必填字段会自动补齐。")
    public String proposeCreate(
            @P("实体类型，如 客户 / 物料") String entityKeyword,
            @P("已知字段的 JSON，键=字段中文名、值=字段值，例如 {\"客户名称\":\"常州测试公司\",\"客户简称\":\"常测\"}") String fieldsJson) {

        if (isBlank(entityKeyword)) {
            return err("缺少实体类型。");
        }
        Map<String, Object> form = resolveForm(entityKeyword.trim());
        if (form == null) {
            return err("找不到「" + entityKeyword + "」对应的可新增主表。");
        }
        String formId = str(form.get("sFormId"));
        String moduleId = str(form.get("sModuleId"));
        String table = str(form.get("sDataSource"));
        if (!authz.canAccessModule(moduleId)) {
            return err("你没有新增「" + entityKeyword + "」的权限。");
        }

        Map<String, Object> col = new LinkedHashMap<>();
        List<String> descParts = new ArrayList<>();
        try {
            if (!isBlank(fieldsJson)) {
                JsonNode fj = mapper.readTree(fieldsJson.trim());
                Iterator<Map.Entry<String, JsonNode>> it = fj.fields();
                while (it.hasNext()) {
                    Map.Entry<String, JsonNode> e = it.next();
                    String zh = e.getKey();
                    String v = e.getValue().asText("");
                    String colName = queryOne(
                            "SELECT sField FROM viw_kg_field_dict WHERE sTable=? AND (sChinese=? OR sChinese LIKE ?) " +
                                    "ORDER BY iFormUses DESC LIMIT 1", table, zh, "%" + zh + "%");
                    if (colName != null) {
                        col.put(colName, v);
                        descParts.add(zh + "=" + v);
                    }
                }
            }
        } catch (Exception ex) {
            return err("字段 JSON 解析失败：" + ex.getMessage());
        }
        if (descParts.isEmpty()) {
            return err("请至少提供一个有效字段（如客户名称）。");
        }

        // 自动补齐 NOT-NULL 无默认列（租户/制单人由 ERP 注入，跳过）
        for (String rc : requiredCols(table)) {
            if (col.containsKey(rc)) {
                continue;
            }
            if ("sId".equals(rc)) {
                col.put(rc, erp.newUuid());
            } else if (rc.endsWith("Id")) {
                String d = commonValue(table, rc);   // 外键：取现有最常见值兜底
                col.put(rc, d == null ? "" : d);
            } else if (rc.endsWith("No")) {
                col.put(rc, "AI" + (System.currentTimeMillis() % 1000000000L));
            } else {
                col.put(rc, "");
            }
        }
        col.putIfAbsent("sId", erp.newUuid());

        String payload;
        try {
            payload = mapper.writeValueAsString(col);
        } catch (Exception e) {
            return err("内部错误：" + e.getMessage());
        }
        String description = "新增【" + entityKeyword + "】：" + String.join("，", descParts);
        String opId = ops.createDraftPayload("agent", "create", formId, moduleId, table, payload, description);

        Map<String, Object> out = new LinkedHashMap<>();
        out.put("opId", opId);
        out.put("summary", description);
        out.put("message", "已为你生成一条待确认的新增，请在下方点【确认】执行、或【取消】。");
        return toJson(out);
    }

    /** 目标表的 NOT-NULL 无默认列（排除 ERP 会自动注入的租户/制单人）。 */
    private List<String> requiredCols(String table) {
        List<String> out = new ArrayList<>();
        try {
            List<Map<String, Object>> rows = jdbc.queryForList(
                    "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? " +
                            "AND IS_NULLABLE='NO' AND COLUMN_DEFAULT IS NULL AND EXTRA NOT LIKE '%auto_increment%'", table);
            Set<String> skip = Set.of("sBrandsId", "sSubsidiaryId", "sMakePerson");
            for (Map<String, Object> r : rows) {
                String c = str(r.get("COLUMN_NAME"));
                if (c != null && !skip.contains(c)) {
                    out.add(c);
                }
            }
        } catch (Exception ignore) {
        }
        return out;
    }

    /** 外键兜底：该列现有的最常见非空值。列名来自 KG/info_schema（可信）。 */
    private String commonValue(String table, String col) {
        try {
            return queryOne("SELECT `" + col + "` FROM `" + table + "` WHERE `" + col + "` IS NOT NULL AND `" + col +
                    "`<>'' GROUP BY `" + col + "` ORDER BY COUNT(*) DESC LIMIT 1");
        } catch (Exception e) {
            return null;
        }
    }

    /** 定位实体的可修改主表：该实体名下 table 类型、最常用(AI工具/连接度)的一张。 */
    private Map<String, Object> resolveForm(String entityKeyword) {
        try {
            List<Map<String, Object>> r = jdbc.queryForList(
                    "SELECT af.sFormId, af.sModuleId, af.sDataSource FROM viw_ai_useful_forms af " +
                            "LEFT JOIN viw_kg_form f ON f.sFormId = af.sFormId " +
                            "WHERE af.sFormTitle LIKE ? AND af.sExecType='table' " +
                            // 排除报表视图(viw_*)，只取可直接改的基础主表
                            "AND af.sDataSource NOT LIKE 'viw%' " +
                            "ORDER BY COALESCE(f.bAiTool,0) DESC, " +
                            "(COALESCE(f.iUpstream,0)+COALESCE(f.iDownstream,0)) DESC, CHAR_LENGTH(af.sFormTitle) ASC LIMIT 1",
                    "%" + entityKeyword + "%");
            return r.isEmpty() ? null : r.get(0);
        } catch (Exception e) {
            return null;
        }
    }

    private static String str(Object o) {
        return o == null ? null : o.toString();
    }

    private String queryOne(String sql, Object... args) {
        try {
            List<Map<String, Object>> r = jdbc.queryForList(sql, args);
            if (!r.isEmpty()) {
                Object v = r.get(0).values().iterator().next();
                return v == null ? null : v.toString();
            }
        } catch (Exception ignore) {
        }
        return null;
    }

    private static boolean isBlank(String s) {
        return s == null || s.isBlank();
    }

    private String err(String msg) {
        Map<String, Object> m = new LinkedHashMap<>();
        m.put("error", msg);
        return toJson(m);
    }

    private String toJson(Map<String, Object> m) {
        try {
            return mapper.writeValueAsString(m);
        } catch (Exception e) {
            return "{\"error\":\"内部错误\"}";
        }
    }
}