package com.xly.service;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 表单 / 字段解析（KgSearch 内核）—— 把「实体类型 / 字段中文名」解析到具体的
 * 主表 / formId / moduleId / 技术列名，供 Read 与 Write 共用，集中一处避免各工具各猜一套。
 *
 * <p>数据全部来自已物化的知识图谱视图：{@code viw_ai_useful_forms} / {@code viw_kg_form}
 * / {@code viw_kg_field_dict}。
 */
@Service
public class FormResolverService {

    private final JdbcTemplate jdbc;

    public FormResolverService(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    /**
     * 定位某实体的**可改主表**：该实体名下、table 类型、排除报表视图(viw_*)、按 AI工具/连接度/名称长度
     * 取最常用的一张。返回含 sFormId / sModuleId / sDataSource 的行，找不到返回 null。
     */
    public Map<String, Object> resolveMasterForm(String entityKeyword) {
        if (isBlank(entityKeyword)) {
            return null;
        }
        try {
            List<Map<String, Object>> r = jdbc.queryForList(
                    "SELECT af.sFormId, af.sModuleId, af.sDataSource, af.sFormTitle 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' AND af.sDataSource NOT LIKE 'viw%' " +
                            // 排除明显的从属/参数表（颜色表/控制表/从表/多数量/配置），避免误定位到非主表
                            "AND af.sDataSource NOT LIKE '%param' AND af.sDataSource NOT LIKE '%slave' " +
                            "AND af.sDataSource NOT LIKE '%control' AND af.sDataSource NOT LIKE '%config' " +
                            "AND af.sDataSource NOT LIKE '%manyqtys' AND af.sDataSource NOT LIKE '%color%' " +
                            "ORDER BY COALESCE(f.bAiTool,0) DESC, " +
                            "(COALESCE(f.iUpstream,0)+COALESCE(f.iDownstream,0)) DESC, CHAR_LENGTH(af.sFormTitle) ASC LIMIT 1",
                    "%" + entityKeyword.trim() + "%");
            return r.isEmpty() ? null : r.get(0);
        } catch (Exception e) {
            return null;
        }
    }

    /** 表单数据源表 -> 字段中文名（字段字典），用于把技术列名渲染为中文表头。 */
    public Map<String, String> fieldLabels(String formId) {
        Map<String, String> m = new HashMap<>();
        try {
            List<Map<String, Object>> rows = jdbc.queryForList(
                    "SELECT fd.sField AS f, MIN(fd.sChinese) AS zh FROM viw_ai_useful_forms af " +
                            "JOIN viw_kg_field_dict fd ON fd.sTable = af.sDataSource " +
                            "WHERE af.sFormId = ? GROUP BY fd.sField", formId);
            for (Map<String, Object> r : rows) {
                Object f = r.get("f");
                Object zh = r.get("zh");
                if (f != null && zh != null) {
                    m.put(f.toString(), zh.toString());
                }
            }
        } catch (Exception ignore) {
        }
        return m;
    }

    /** 猜该表单/表的"名称"字段（以 Name 结尾、使用最广），用于按关键词过滤 / 展示记录名。 */
    public String resolveNameField(String table) {
        return 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);
    }

    /** 同 {@link #resolveNameField}，但从 formId 出发（经表单目录找到数据源表）。 */
    public String resolveNameFieldByFormId(String formId) {
        return queryOne(
                "SELECT fd.sField FROM viw_ai_useful_forms af " +
                        "JOIN viw_kg_field_dict fd ON fd.sTable = af.sDataSource " +
                        "WHERE af.sFormId = ? AND fd.sField LIKE '%Name' AND fd.sField NOT LIKE '%NameId' " +
                        "ORDER BY fd.iFormUses DESC LIMIT 1", formId);
    }

    // ERP 会自动注入 / 系统管理、不该让用户填的列（新增时排除）。
    private static final Set<String> SYS_EXACT = Set.of(
            "sId", "sBrandsId", "sSubsidiaryId", "sMakePerson", "sFormId", "sBillNo", "iIncrement", "iOrder",
            "tCreateDate", "tMakeDate", "tUpdateDate", "sModelsId", "sToken", "sParentId");
    private static final Pattern NUM = Pattern.compile("-?\\d+(\\.\\d+)?");

    /** 是否为系统/审计/计算列（新增表单不呈现、payload 不让 LLM 直填）。 */
    public boolean isSystemColumn(String col) {
        if (col == null || col.isBlank()) {
            return true;
        }
        if (SYS_EXACT.contains(col)) {
            return true;
        }
        // 布尔标志 bXxx / 日期 tXxx / 金额·折扣·比率计算列 / 各种经办人
        if (col.length() > 1 && (col.charAt(0) == 'b' || col.charAt(0) == 't') && Character.isUpperCase(col.charAt(1))) {
            return true;
        }
        if (col.contains("Money") || col.contains("Rate") || col.contains("Discount") || col.endsWith("Person")
                || col.contains("Reserve") || col.contains("Manual")) {
            return true;
        }
        return false;
    }

    /**
     * 复杂旗舰表单的**人工策展录入字段**（label→真实可写列 + 外键）。报价主表字段名与显示别名不一致
     * （表单里 sProductName/dProductQty 并非真实列），且核心列的 iFormUses 很低，纯靠字段字典排不出来——
     * 故对报价主表策展这组真实可写列（与实测能成功写入的一致）。其它表返回 null，走通用字段字典逻辑。
     */
    private List<Map<String, Object>> curatedFields(String table) {
        if (table == null) {
            return null;
        }
        if (table.equalsIgnoreCase("quoquotationmaster")) {
            // 报价是跨表主-从表单：主表(quoquotationmaster) + 印刷/部件明细(quoquotationslave) + 多数量(quoquotationmanyqtys)。
            // 每个字段标 target 表；印刷/颜色/单双面无独立列，作为备注写进从表 sMaterialsMemo（note）。价格由 ERP【核价】算。
            List<Map<String, Object>> l = new ArrayList<>();
            // fk → 下拉从对应表取选项；number → 数字；select → 固定选项；text/date 由控件决定
            l.add(bf("sCustomerId", "客户名称", "elecustomer", "master", "fkselect", null));
            l.add(bf("sProductId", "产品名称", "eleproduct", "master", "fkselect", null));
            l.add(bf("sMaterialsId", "物料名称", "elematerials", "master", "fkselect", null));
            l.add(bf("sProductUnit", "单位", null, "slave", "select", "只,个,套,张,本,件,PCS,KG,米,平方米"));
            l.add(bf("dLength", "长(L)", null, "master", "number", null));
            l.add(bf("dWidth", "宽(W)", null, "master", "number", null));
            l.add(bf("dHeight", "高(D)", null, "master", "number", null));
            l.add(bf("dQty", "数量", null, "master", "number", null));
            l.add(bf("sPrint", "印刷", null, "note", "select", "胶印,柔印,凹印,丝印,数码印刷,不印刷"));
            l.add(bf("sColor", "颜色", null, "note", "select", "彩色,黑白,专色,四色,无"));
            l.add(bf("sSingleDouble", "单双面", null, "note", "select", "单面,双面"));
            l.add(bf("dProductLength", "部件长", null, "slave", "number", null));
            l.add(bf("dProductWidth", "部件宽", null, "slave", "number", null));
            l.add(bf("dPrice", "单价", null, "master", "number", null));
            l.add(bf("dCoefficient", "系数", null, "slave", "number", null));
            l.add(bf("sMaterialsMemo", "材料备注", null, "slave", "text", null));
            Map<String, Object> many = bf("dManyQty", "多数量", null, "manyqtys", "text", null);
            many.put("hint", "多个报价数量用逗号分隔，如 1000,3000,5000");
            l.add(many);
            return l;
        }
        return null;
    }

    private Map<String, Object> bf(String col, String label, String fk, String targetTable, String type, String optionsCsv) {
        Map<String, Object> m = new LinkedHashMap<>();
        m.put("col", col);
        m.put("label", label);
        if (fk != null) {
            m.put("fk", fk);
        }
        if (targetTable != null) {
            m.put("table", targetTable);
        }
        if (type != null) {
            m.put("type", type);
        }
        if (optionsCsv != null && !optionsCsv.isBlank()) {
            List<String> opts = new ArrayList<>();
            for (String o : optionsCsv.split(",")) {
                if (!o.trim().isEmpty()) {
                    opts.add(o.trim());
                }
            }
            m.put("options", opts);
        }
        return m;
    }

    /**
     * FK 选项页（二级选择器数据源）：名称 + 若干展示列（字段字典按使用度选前 3 个非名称文本列），
     * 支持按名称模糊搜索 + 分页。仅允许「在字段字典里作为外键目标出现过」的表，避免任意读表。
     * 返回 {columns:[{col,label}…], rows:[{col:value…}], total, page, pageSize}。
     */
    public Map<String, Object> fkOptionPage(String fkTable, String brand, String q, int page, int pageSize) {
        Map<String, Object> out = new LinkedHashMap<>();
        out.put("columns", List.of());
        out.put("rows", List.of());
        out.put("total", 0);
        out.put("page", Math.max(1, page));
        out.put("pageSize", pageSize);
        try {
            Integer ok = jdbc.queryForObject(
                    "SELECT COUNT(*) FROM viw_kg_field_dict WHERE sFkTable=?", Integer.class, fkTable);
            if (ok == null || ok == 0) {
                return out;
            }
            String nameField = resolveNameField(fkTable);
            if (nameField == null) {
                return out;
            }
            // 展示列：名称列 + 使用度最高的最多 3 个其它文本列（排除系统/Id 列）
            List<Map<String, Object>> cols = new ArrayList<>();
            cols.add(Map.of("col", nameField, "label", labelOf(fkTable, nameField)));
            for (Map<String, Object> r : jdbc.queryForList(
                    "SELECT sField, SUBSTRING_INDEX(GROUP_CONCAT(sChinese ORDER BY CHAR_LENGTH(sChinese) ASC SEPARATOR 0x1f),0x1f,1) zh " +
                            "FROM viw_kg_field_dict WHERE sTable=? AND sField<>? AND sField NOT LIKE '%Id' " +
                            "AND CHAR_LENGTH(sChinese)>=2 GROUP BY sField ORDER BY SUM(iFormUses) DESC LIMIT 8", fkTable, nameField)) {
                String c = String.valueOf(r.get("sField"));
                if (isSystemColumn(c) || cols.size() >= 4) {
                    continue;
                }
                cols.add(Map.of("col", c, "label", String.valueOf(r.get("zh"))));
            }
            StringBuilder sel = new StringBuilder("sId");
            for (Map<String, Object> c : cols) {
                sel.append(", `").append(c.get("col")).append("`");
            }
            String where = "WHERE IFNULL(`" + nameField + "`,'')<>'' " +
                    (brand != null && !brand.isBlank() ? "AND sBrandsId=? " : "") +
                    (q != null && !q.isBlank() ? "AND `" + nameField + "` LIKE ? " : "");
            List<Object> args = new ArrayList<>();
            if (brand != null && !brand.isBlank()) {
                args.add(brand);
            }
            if (q != null && !q.isBlank()) {
                args.add("%" + q.trim() + "%");
            }
            Integer total = jdbc.queryForObject(
                    "SELECT COUNT(*) FROM `" + fkTable + "` " + where, Integer.class, args.toArray());
            int p = Math.max(1, page);
            int ps = Math.min(Math.max(1, pageSize), 100);
            List<Object> pageArgs = new ArrayList<>(args);
            pageArgs.add(ps);
            pageArgs.add((p - 1) * ps);
            List<Map<String, Object>> rows = jdbc.queryForList(
                    "SELECT " + sel + " FROM `" + fkTable + "` " + where +
                            "ORDER BY CHAR_LENGTH(`" + nameField + "`) ASC, `" + nameField + "` ASC LIMIT ? OFFSET ?",
                    pageArgs.toArray());
            out.put("columns", cols);
            out.put("rows", rows);
            out.put("total", total == null ? 0 : total);
            out.put("page", p);
            out.put("pageSize", ps);
        } catch (Exception ignore) {
        }
        return out;
    }

    private String labelOf(String table, String field) {
        String zh = queryOne(
                "SELECT sChinese FROM viw_kg_field_dict WHERE sTable=? AND sField=? ORDER BY CHAR_LENGTH(sChinese) ASC LIMIT 1",
                table, field);
        return zh == null ? field : zh;
    }

    /** 表列 -> MySQL 数据类型（information_schema）。 */
    public Map<String, String> columnTypes(String table) {
        Map<String, String> m = new HashMap<>();
        try {
            for (Map<String, Object> r : jdbc.queryForList(
                    "SELECT COLUMN_NAME c, DATA_TYPE d FROM information_schema.COLUMNS " +
                            "WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?", table)) {
                m.put(String.valueOf(r.get("c")), String.valueOf(r.get("d")).toLowerCase());
            }
        } catch (Exception ignore) {
        }
        return m;
    }

    /** 按列类型把用户给的字符串强转成合法值：数值取数字、日期非法则丢弃(交 ERP 默认)、bit→0/1、其余原样。 */
    public Object coerce(String dataType, String v) {
        if (v == null) {
            return null;
        }
        String t = dataType == null ? "varchar" : dataType.toLowerCase();
        if (t.contains("int") || t.equals("decimal") || t.contains("double") || t.contains("float") || t.contains("numeric")) {
            Matcher mm = NUM.matcher(v);
            return mm.find() ? mm.group() : "0";
        }
        if (t.contains("date") || t.contains("time")) {
            return v.matches("\\d{4}-\\d{2}-\\d{2}.*") ? v : null; // 非日期 → null，让 ERP 用默认值
        }
        if (t.equals("bit") || t.equals("tinyint")) {
            return (v.equals("1") || v.equalsIgnoreCase("true") || v.contains("是")) ? 1 : 0;
        }
        return v;
    }

    /** 类型默认值（自动补 NOT-NULL 无默认列用）。 */
    public Object typeDefault(String col, String dataType) {
        String t = dataType == null ? "varchar" : dataType.toLowerCase();
        if (t.contains("int") || t.equals("decimal") || t.contains("double") || t.contains("float") || t.contains("numeric")) {
            return 0;
        }
        return ""; // date/time 交 ERP 默认，不主动补
    }

    /**
     * 某实体主表的**业务录入字段**（字段字典里有中文名、且非系统/计算列），按使用度排序，最多 limit 个。
     * 这是 collectForm 呈现给用户填的字段来源——比 gdsconfigformslave 的界面布局字段更贴近"要填的业务参数"。
     * FK 列（有 sFkTable）标记 fk，前端让用户填名称、写入时解析成 id。
     */
    public List<Map<String, Object>> businessFields(String table, int limit) {
        List<Map<String, Object>> curated = curatedFields(table);
        if (curated != null) {
            return curated;
        }
        List<Map<String, Object>> out = new ArrayList<>();
        try {
            List<Map<String, Object>> rows = jdbc.queryForList(
                    "SELECT sField, " +
                            // 标签取非Id、更短的中文名（客户 优先于 客户Id）
                            "SUBSTRING_INDEX(GROUP_CONCAT(sChinese ORDER BY (sChinese LIKE '%Id') ASC, CHAR_LENGTH(sChinese) ASC SEPARATOR 0x1f),0x1f,1) zh, " +
                            "MAX(sFkTable) fk, SUM(iFormUses) u FROM viw_kg_field_dict " +
                            "WHERE sTable=? AND CHAR_LENGTH(sChinese)>=2 GROUP BY sField ORDER BY u DESC", table);
            for (Map<String, Object> r : rows) {
                String col = String.valueOf(r.get("sField"));
                if (isSystemColumn(col)) {
                    continue;
                }
                Map<String, Object> f = new LinkedHashMap<>();
                f.put("col", col);
                f.put("label", r.get("zh"));
                Object fk = r.get("fk");
                if (fk != null && !"null".equalsIgnoreCase(String.valueOf(fk)) && !String.valueOf(fk).isBlank()) {
                    f.put("fk", String.valueOf(fk));
                }
                out.add(f);
                if (out.size() >= limit) {
                    break;
                }
            }
        } catch (Exception ignore) {
        }
        return out;
    }

    /** 外键名称 -> id：在外键表按名称字段模糊匹配（元数据来源可信，非用户拼 SQL）。 */
    public String resolveFk(String fkTable, String name) {
        if (fkTable == null || fkTable.isBlank() || name == null || name.isBlank()) {
            return null;
        }
        String nameField = resolveNameField(fkTable);
        if (nameField == null) {
            return null;
        }
        return queryOne("SELECT sId FROM `" + fkTable + "` WHERE `" + nameField + "` LIKE ? " +
                "ORDER BY CHAR_LENGTH(`" + nameField + "`) ASC LIMIT 1", "%" + name.trim() + "%");
    }

    /**
     * 若该表有 NOT-NULL 的 sBillNo 列，按现有单号规律生成下一个单号；否则返回 null。
     * 规律 = 现有最新单号的前缀(字母)+ 年月(YYYYMM) + 递增序号(3位)，如 BJD202607082。
     */
    public String nextBillNo(String table, String brand) {
        try {
            Integer has = jdbc.queryForObject(
                    "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() " +
                            "AND TABLE_NAME=? AND COLUMN_NAME='sBillNo' AND IS_NULLABLE='NO'", Integer.class, table);
            if (has == null || has == 0) {
                return null;
            }
            String recent = queryOne("SELECT sBillNo FROM `" + table + "` WHERE sBrandsId=? AND IFNULL(sBillNo,'')<>'' " +
                    "ORDER BY tCreateDate DESC LIMIT 1", brand);
            String prefix = "AI";
            if (recent != null && !recent.isBlank()) {
                int i = 0;
                while (i < recent.length() && !Character.isDigit(recent.charAt(i))) {
                    i++;
                }
                if (i > 0) {
                    prefix = recent.substring(0, i);
                }
            }
            String ym = new SimpleDateFormat("yyyyMM").format(new Date());
            Integer seq;
            try {
                seq = jdbc.queryForObject(
                        "SELECT IFNULL(MAX(CAST(SUBSTRING(sBillNo, ?) AS UNSIGNED)),0)+1 FROM `" + table + "` " +
                                "WHERE sBrandsId=? AND sBillNo LIKE ?",
                        Integer.class, prefix.length() + ym.length() + 1, brand, prefix + ym + "%");
            } catch (Exception e) {
                seq = 1;
            }
            return prefix + ym + String.format("%03d", seq == null ? 1 : seq);
        } catch (Exception e) {
            return null;
        }
    }

    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();
    }
}