package com.pharmacopoeia.service;

import com.pharmacopoeia.entity.Brand;
import com.pharmacopoeia.entity.BrandRecommendRule;
import com.pharmacopoeia.repository.BrandRecommendRuleRepository;
import com.pharmacopoeia.repository.BrandRepository;
import org.jetbrains.annotations.NotNull;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.stream.Collectors;

@Service
public class BrandRecommendService {

    private final BrandRecommendRuleRepository ruleRepository;
    private final BrandRepository brandRepository;

    public BrandRecommendService(BrandRecommendRuleRepository ruleRepository,
                                  BrandRepository brandRepository) {
        this.ruleRepository = ruleRepository;
        this.brandRepository = brandRepository;
    }

    // ==================== field constants ====================

    // Chinese CSV headers -> Java field name mapping
    private static final Map<String, String> CSV_HEADER_TO_FIELD = new LinkedHashMap<>() {{
        put("品牌名称", "name");
        put("功能主治", "function_indication");
        put("用法用量", "usage_dosage");
        put("禁忌", "contraindication");
        put("成分", "ingredients");
        put("性状", "properties");
        put("规格", "specification");
        put("不良反应", "adverse_reactions");
        put("注意事项", "precautions");
        put("执行标准", "execution_standard");
        put("贮藏", "storage");
        put("跳转链接", "jump_url");
        put("描述", "description");
        put("排序", "sort_order");
    }};

    private static final List<String> BRAND_CSV_COLS = new ArrayList<>(CSV_HEADER_TO_FIELD.keySet());
    
    private static final List<String> BRAND_FIELD_NAMES = new ArrayList<>(CSV_HEADER_TO_FIELD.values());
    private static final String BRANDS_CSV_HEADER = String.join(",", BRAND_CSV_COLS);

    // Rules CSV Chinese headers -> field name
    private static final Map<String, String> RULES_CSV_HEADER_TO_FIELD = new LinkedHashMap<>() {{
        put("关键词", "keyword");
        put("品牌ID", "brand_id");
        put("品牌名称", "brand_name");
        put("层级", "tier");
        put("是否启用", "is_active");
    }};
    private static final List<String> RULES_CSV_COLS = new ArrayList<>(RULES_CSV_HEADER_TO_FIELD.keySet());
    private static final String RULES_CSV_HEADER = String.join(",", RULES_CSV_COLS);

    // non-name, non-sort_order columns (field names for internal use)
    private static final List<String> BRAND_TEXT_COLS = BRAND_FIELD_NAMES.stream()
            .filter(c -> !"name".equals(c) && !"sort_order".equals(c))
            .toList();

    // ==================== tier matching ====================

    public List<Map<String, Object>> match(List<Map<String, Object>> sources, String fullAnswer) {
        List<BrandRecommendRule> rules = ruleRepository.findByIsActiveTrueOrderByTierAscKeywordAsc();
        return rules.isEmpty() ? List.of() : doMatch(rules, sources, fullAnswer);
    }

    private List<Map<String, Object>> doMatch(List<BrandRecommendRule> rules,
                                               List<Map<String, Object>> sources, String fullAnswer) {
        Map<Integer, List<BrandRecommendRule>> byTier = rules.stream()
                .collect(Collectors.groupingBy(BrandRecommendRule::getTier, LinkedHashMap::new, Collectors.toList()));

        Set<String> texts = sources.stream()
                .map(s -> (String) s.getOrDefault("name", ""))
                .filter(n -> !n.isEmpty())
                .collect(Collectors.toCollection(HashSet::new));
        Optional.ofNullable(fullAnswer).filter(a -> !a.isEmpty()).ifPresent(texts::add);

        return byTier.values().stream()
                .map(tierRules -> findMatches(tierRules, texts))
                .filter(m -> !m.isEmpty())
                .findFirst()
                .orElse(List.of());
    }

    private List<Map<String, Object>> findMatches(List<BrandRecommendRule> rules, Set<String> targets) {
        Set<Integer> brandIds = rules.stream().map(BrandRecommendRule::getBrandId).collect(Collectors.toSet());
        Map<Integer, Brand> brandMap = brandRepository.findAllById(brandIds).stream()
                .collect(Collectors.toMap(Brand::getId, b -> b));

        Set<Integer> seenRuleIds = new HashSet<>();
        Set<Integer> seenBrandIds = new HashSet<>();
        List<Map<String, Object>> result = new ArrayList<>();
        for (BrandRecommendRule rule : rules) {
            String kwLower = rule.getKeyword().toLowerCase();
            boolean hit = targets.stream().anyMatch(t -> t.toLowerCase().contains(kwLower));
            if (hit && seenRuleIds.add(rule.getId()) && seenBrandIds.add(rule.getBrandId())) {
                result.add(buildBrandResult(rule, brandMap.get(rule.getBrandId())));
            }
        }
        return result;
    }

    private Map<String, Object> buildBrandResult(BrandRecommendRule rule, Brand brand) {
        Map<String, Object> m = new LinkedHashMap<>();
        m.put("brand_id", rule.getBrandId());
        m.put("matched_keyword", rule.getKeyword());
        m.put("tier", rule.getTier());
        if (brand != null) {
            m.put("brand_name", brand.getName());
            m.put("description", nullToEmpty(brand.getDescription()));
            m.put("function", nullToEmpty(brand.getFunctionIndication()));
            m.put("usage_dosage", nullToEmpty(brand.getUsageDosage()));
            m.put("contraindication", nullToEmpty(brand.getContraindication()));
            m.put("ingredients", nullToEmpty(brand.getIngredients()));
            m.put("properties", nullToEmpty(brand.getProperties()));
            m.put("specification", nullToEmpty(brand.getSpecification()));
            m.put("adverse_reactions", nullToEmpty(brand.getAdverseReactions()));
            m.put("precautions", nullToEmpty(brand.getPrecautions()));
            m.put("execution_standard", nullToEmpty(brand.getExecutionStandard()));
            m.put("storage", nullToEmpty(brand.getStorage()));
            m.put("jump_url", nullToEmpty(brand.getJumpUrl()));
        }
        return m;
    }

    // ==================== brand CRUD ====================

    public Map<String, Object> listBrands(String keyword, int page, int pageSize) {
        List<Brand> all = brandRepository.findAllByOrderBySortOrderAsc();
        Optional.ofNullable(keyword)
                .filter(k -> !k.isBlank())
                .ifPresent(k -> {
                    String kw = k.toLowerCase();
                    all.removeIf(b -> !b.getName().toLowerCase().contains(kw));
                });
        return paginate(all, page, pageSize);
    }

    @Transactional
    public Map<String, Object> createBrand(Map<String, Object> body) {
        String name = require(body);
        if (brandRepository.existsByNameAndIdNot(name.trim(), null)) {
            throw new IllegalArgumentException("brand name already exists");
        }

        Brand brand = Brand.builder()
                .name(name.trim())
                .functionIndication(str(body, "function_indication"))
                .usageDosage(str(body, "usage_dosage"))
                .contraindication(str(body, "contraindication"))
                .ingredients(str(body, "ingredients"))
                .properties(str(body, "properties"))
                .specification(str(body, "specification"))
                .adverseReactions(str(body, "adverse_reactions"))
                .precautions(str(body, "precautions"))
                .executionStandard(str(body, "execution_standard"))
                .storage(str(body, "storage"))
                .jumpUrl(str(body, "jump_url"))
                .description(str(body, "description"))
                .sortOrder(intVal(body))
                .isActive(true)
                .build();
        brand = brandRepository.save(brand);
        return Map.of("ok", true, "id", brand.getId(), "message", "created");
    }

    @Transactional
    public Map<String, Object> updateBrand(int id, Map<String, Object> body) {
        Brand b = brandRepository.findById(id)
                .orElseThrow(() -> new IllegalArgumentException("brand not found"));

        Optional.ofNullable(body.get("name"))
                .map(Object::toString).map(String::trim).filter(n -> !n.isBlank())
                .ifPresent(n -> {
                    if (brandRepository.existsByNameAndIdNot(n, id)) {
                        throw new IllegalArgumentException("brand name already exists");
                    }
                    b.setName(n);
                });

        BRAND_TEXT_COLS.forEach(key -> applyStr(body, key, fieldSetter(b, key)));

        Optional.ofNullable(body.get("sort_order"))
                .filter(v -> v instanceof Number)
                .ifPresent(v -> b.setSortOrder(((Number) v).intValue()));
        Optional.ofNullable(body.get("is_active"))
                .filter(v -> v instanceof Boolean)
                .ifPresent(v -> b.setIsActive((Boolean) v));

        brandRepository.save(b);
        return Map.of("ok", true, "id", id, "message", "updated");
    }

    @Transactional
    public Map<String, Object> deleteBrand(int id) {
        brandRepository.findById(id)
                .orElseThrow(() -> new IllegalArgumentException("brand not found"));
        brandRepository.deleteById(id);
        return Map.of("ok", true, "message", "deleted");
    }

    public Map<String, Object> getBrand(int id) {
        Brand b = brandRepository.findById(id)
                .orElseThrow(() -> new IllegalArgumentException("brand not found"));

        Map<String, Object> m = new LinkedHashMap<>();
        m.put("id", b.getId());
        m.put("name", b.getName());
        BRAND_FIELD_NAMES.stream().filter(c -> !"name".equals(c))
                .forEach(c -> m.put(c, nullToEmpty(brandField(b, c))));
        m.put("is_active", b.getIsActive());
        m.put("created_at", b.getCreatedAt());
        m.put("updated_at", b.getUpdatedAt());
        return m;
    }

    // ==================== rule CRUD ====================

    public Map<String, Object> listRules(String keyword, int page, int pageSize) {
        List<BrandRecommendRule> all = ruleRepository.findAllByOrderByTierAscKeywordAsc();
        Optional.ofNullable(keyword)
                .filter(k -> !k.isBlank())
                .ifPresent(k -> all.removeIf(r -> !r.getKeyword().contains(k)));

        Set<Integer> brandIds = all.stream().map(BrandRecommendRule::getBrandId).collect(Collectors.toSet());
        Map<Integer, String> brandNames = brandRepository.findAllById(brandIds).stream()
                .collect(Collectors.toMap(Brand::getId, Brand::getName));

        Map<String, Object> result = new HashMap<>(paginate(all, page, pageSize));
        @SuppressWarnings("unchecked")
        List<BrandRecommendRule> items = (List<BrandRecommendRule>) result.get("items");
        List<Map<String, Object>> enriched = items.stream().map(r -> {
            Map<String, Object> m = new LinkedHashMap<>();
            m.put("id", r.getId());
            m.put("keyword", r.getKeyword());
            m.put("brand_id", r.getBrandId());
            m.put("brand_name", brandNames.getOrDefault(r.getBrandId(), ""));
            m.put("tier", r.getTier());
            m.put("is_active", r.getIsActive());
            m.put("created_at", r.getCreatedAt());
            m.put("updated_at", r.getUpdatedAt());
            return m;
        }).collect(Collectors.toList());
        result.put("items", enriched);
        return result;
    }

    @Transactional
    public Map<String, Object> createRule(String keyword, int brandId, int tier) {
        Optional.ofNullable(keyword).filter(k -> !k.isBlank())
                .orElseThrow(() -> new IllegalArgumentException("keyword required"));
        Optional.of(brandId).filter(brandRepository::existsById)
                .orElseThrow(() -> new IllegalArgumentException("brand not found"));
        if (ruleRepository.existsByKeywordAndIdNot(keyword.trim(), null)) {
            throw new IllegalArgumentException("keyword already exists");
        }

        BrandRecommendRule rule = BrandRecommendRule.builder()
                .keyword(keyword.trim()).brandId(brandId).tier(tier).isActive(true).build();
        rule = ruleRepository.save(rule);
        return Map.of("ok", true, "id", rule.getId(), "message", "created");
    }

    @Transactional
    public Map<String, Object> updateRule(int id, String keyword, Integer brandId, Integer tier, Boolean isActive) {
        BrandRecommendRule rule = ruleRepository.findById(id)
                .orElseThrow(() -> new IllegalArgumentException("rule not found"));

        Optional.ofNullable(keyword).filter(k -> !k.isBlank()).ifPresent(k -> {
            if (ruleRepository.existsByKeywordAndIdNot(k.trim(), id)) {
                throw new IllegalArgumentException("keyword already exists");
            }
            rule.setKeyword(k.trim());
        });
        Optional.ofNullable(brandId)
                .filter(brandRepository::existsById)
                .ifPresent(rule::setBrandId);
        Optional.ofNullable(tier).ifPresent(rule::setTier);
        Optional.ofNullable(isActive).ifPresent(rule::setIsActive);

        ruleRepository.save(rule);
        return Map.of("ok", true, "id", id, "message", "updated");
    }

    @Transactional
    public Map<String, Object> deleteRule(int id) {
        ruleRepository.findById(id)
                .orElseThrow(() -> new IllegalArgumentException("rule not found"));
        ruleRepository.deleteById(id);
        return Map.of("ok", true, "message", "deleted");
    }

    @Transactional
    public Map<String, Object> toggleRule(int id, Boolean isActive) {
        BrandRecommendRule rule = ruleRepository.findById(id)
                .orElseThrow(() -> new IllegalArgumentException("rule not found"));
        boolean newState = isActive != null ? isActive : !Boolean.TRUE.equals(rule.getIsActive());
        rule.setIsActive(newState);
        ruleRepository.save(rule);
        return Map.of("ok", true, "id", id, "is_active", newState,
                "message", newState ? "enabled" : "disabled");
    }

    // ==================== CSV import/export ====================

    public Map<String, Object> importBrandsCsv(String csv) {
        List<String[]> rows = parseCsv(csv);
        Map<String, Integer> colIdx = buildColIdx(rows, BRAND_CSV_COLS);

        int success = 0, skipped = 0, failed = 0;
        // Track names already in this batch to avoid duplicate-key violation within same import
        Set<String> batchNames = new HashSet<>();
        for (int i = 1; i < rows.size(); i++) {
            String[] cols = rows.get(i);
            try {
                String name = cell(cols, colIdx, "品牌名称");
                if (name == null || name.isEmpty()) { failed++; continue; }
                if (!batchNames.add(name) || brandRepository.existsByNameAndIdNot(name, null)) { skipped++; continue; }
                brandRepository.save(Brand.builder()
                        .name(name)
                        .functionIndication(cell(cols, colIdx, "功能主治"))
                        .usageDosage(cell(cols, colIdx, "用法用量"))
                        .contraindication(cell(cols, colIdx, "禁忌"))
                        .ingredients(cell(cols, colIdx, "成分"))
                        .properties(cell(cols, colIdx, "性状"))
                        .specification(cell(cols, colIdx, "规格"))
                        .adverseReactions(cell(cols, colIdx, "不良反应"))
                        .precautions(cell(cols, colIdx, "注意事项"))
                        .executionStandard(cell(cols, colIdx, "执行标准"))
                        .storage(cell(cols, colIdx, "贮藏"))
                        .jumpUrl(cell(cols, colIdx, "跳转链接"))
                        .description(cell(cols, colIdx, "描述"))
                        .sortOrder(cellInt(cols, colIdx, "排序"))
                        .isActive(true)
                        .build());
                success++;
            } catch (Exception e) { failed++; }
        }
        return Map.of("ok", true, "success", success, "skipped", skipped, "failed", failed);
    }

    @NotNull
    private static Map<String, Integer> buildColIdx(List<String[]> rows, List<String> expectedHeaders) {
        if (rows.isEmpty()) {
            throw new IllegalArgumentException("CSV格式不正确，期望表头: " + String.join(",", expectedHeaders));
        }

        String[] header = rows.getFirst();
        Map<String, Integer> colIdx = new LinkedHashMap<>();
        for (int i = 0; i < header.length; i++) {
            colIdx.put(normalizeHeader(header[i]), i);
        }

        for (String h : expectedHeaders) {
            if (!colIdx.containsKey(h)) {
                throw new IllegalArgumentException("CSV缺少列: " + h + "，实际表头: " + String.join(", ", header));
            }
        }
        return colIdx;
    }

    /** Normalize a CSV header cell: strip BOM, invisible chars, quotes, and trim. */
    private static String normalizeHeader(String raw) {
        if (raw == null) return "";
        String s = raw;
        // Strip BOM anywhere (not just at start of file — can appear on first cell)
        s = s.replace("﻿", "");
        // Strip zero-width characters that Excel/WPS may inject
        s = s.replace("​", ""); // zero-width space
        s = s.replace("‌", ""); // zero-width non-joiner
        s = s.replace("‍", ""); // zero-width joiner
        s = s.replace("﻿", ""); // BOM / zero-width no-break space
        s = s.replace(" ", " "); // non-breaking space → normal space
        // Strip other common invisible control chars except space
        s = s.replaceAll("[\\p{Cntrl}&&[^\t]]", "");
        // Strip surrounding quotes
        s = s.replace("\"", "").replace("'", "");
        return s.trim();
    }

    public String exportBrandsCsv(Set<Integer> ids) {
        List<Brand> brands = (ids != null && !ids.isEmpty()) ? brandRepository.findAllById(ids) : List.of();
        StringBuilder sb = new StringBuilder(BRANDS_CSV_HEADER).append("\n");
        List<Function<Brand, String>> extractors = List.of(
                b -> esc(b.getName()),
                b -> esc(b.getFunctionIndication()),
                b -> esc(b.getUsageDosage()),
                b -> esc(b.getContraindication()),
                b -> esc(b.getIngredients()),
                b -> esc(b.getProperties()),
                b -> esc(b.getSpecification()),
                b -> esc(b.getAdverseReactions()),
                b -> esc(b.getPrecautions()),
                b -> esc(b.getExecutionStandard()),
                b -> esc(b.getStorage()),
                b -> esc(b.getJumpUrl()),
                b -> esc(b.getDescription()),
                b -> String.valueOf(b.getSortOrder())
        );
        brands.forEach(b -> sb.append(extractors.stream()
                .map(f -> f.apply(b)).collect(Collectors.joining(","))).append("\n"));
        return sb.toString();
    }

    public Map<String, Object> importRulesCsv(String csv) {
        List<String[]> rows = parseCsv(csv);
        Map<String, Integer> colIdx = buildColIdx(rows, RULES_CSV_COLS);

        int success = 0, skipped = 0, failed = 0;
        Set<String> batchKw = new HashSet<>();
        for (int i = 1; i < rows.size(); i++) {
            String[] cols = rows.get(i);
            try {
                String kw = cell(cols, colIdx, "关键词");
                if (kw == null || kw.isEmpty()) { failed++; continue; }
                if (!batchKw.add(kw) || ruleRepository.existsByKeywordAndIdNot(kw, null)) { skipped++; continue; }

                int brandId = cellInt(cols, colIdx, "品牌ID");
                String brandName = cell(cols, colIdx, "品牌名称");
                Brand matched = brandId > 0 && brandRepository.existsById(brandId)
                        ? null
                        : brandRepository.findByName(brandName);
                int resolvedId = matched != null ? matched.getId() : brandId;
                if (resolvedId <= 0 || !brandRepository.existsById(resolvedId)) { failed++; continue; }

                int tier = cellInt(cols, colIdx, "层级");
                if (tier <= 0) {
                    tier = 1;
                }
                boolean isActive = !"false".equalsIgnoreCase(cell(cols, colIdx, "是否启用"));
                ruleRepository.save(BrandRecommendRule.builder()
                        .keyword(kw).brandId(resolvedId).tier(tier).isActive(isActive).build());
                success++;
            } catch (Exception e) { failed++; }
        }
        return Map.of("ok", true, "success", success, "skipped", skipped, "failed", failed);
    }

    public String exportRulesCsv(Set<Integer> ids) {
        List<BrandRecommendRule> rules = (ids != null && !ids.isEmpty()) ? ruleRepository.findAllById(ids) : List.of();
        Set<Integer> brandIds = rules.stream().map(BrandRecommendRule::getBrandId).collect(Collectors.toSet());
        Map<Integer, String> brandNames = brandRepository.findAllById(brandIds).stream()
                .collect(Collectors.toMap(Brand::getId, Brand::getName));
        StringBuilder sb = new StringBuilder(RULES_CSV_HEADER).append("\n");
        rules.forEach(r -> sb.append(esc(r.getKeyword())).append(",")
                .append(r.getBrandId()).append(",")
                .append(esc(brandNames.getOrDefault(r.getBrandId(), ""))).append(",")
                .append(r.getTier()).append(",")
                .append(r.getIsActive()).append("\n"));
        return sb.toString();
    }

    public byte[] exportBrandsCsvBytes(Set<Integer> ids) {
        return exportBrandsCsv(ids).getBytes(StandardCharsets.UTF_8);
    }

    public byte[] exportRulesCsvBytes(Set<Integer> ids) {
        return exportRulesCsv(ids).getBytes(StandardCharsets.UTF_8);
    }

    // ==================== helpers ====================

    private static <T> Map<String, Object> paginate(List<T> all, int page, int pageSize) {
        int total = all.size();
        int from = (page - 1) * pageSize;
        int to = Math.min(from + pageSize, total);
        List<T> items = from < total ? all.subList(from, to) : List.of();
        return Map.of(
            "items", items,
            "page", page,
            "page_size", pageSize,
            "total", total,
            "total_pages", (int) Math.ceil((double) total / pageSize)
        );
    }

    /** Parse CSV content into rows, respecting quoted fields.
     *  Newlines inside "..." quoted fields do NOT split rows.
     *  Auto-detects delimiter from header row (comma, tab, semicolon). */
    private static List<String[]> parseCsv(String csv) {
        List<String[]> rows = new ArrayList<>();
        // Strip UTF-8 BOM if present (Excel adds it when saving UTF-8 CSV)
        if (csv.startsWith("﻿")) {
            csv = csv.substring(1);
        }
        csv = csv.replace("\r\n", "\n").replace("\r", "\n");

        // Split into logical lines: \n inside a quoted field stays part of the cell value
        List<String> lines = new ArrayList<>();
        StringBuilder lineBuf = new StringBuilder();
        boolean inQuotes = false;
        for (int i = 0; i < csv.length(); i++) {
            char ch = csv.charAt(i);
            if (inQuotes) {
                lineBuf.append(ch);
                if (ch == '"' && i + 1 < csv.length() && csv.charAt(i + 1) == '"') {
                    lineBuf.append('"');
                    i++;
                } else if (ch == '"') {
                    inQuotes = false;
                }
            } else {
                if (ch == '"') {
                    inQuotes = true;
                    lineBuf.append(ch);
                } else if (ch == '\n') {
                    lines.add(lineBuf.toString());
                    lineBuf.setLength(0);
                } else {
                    lineBuf.append(ch);
                }
            }
        }
        if (!lineBuf.isEmpty()) {
            lines.add(lineBuf.toString());
        }

        // Filter empty/comment lines
        List<String> dataLines = new ArrayList<>();
        for (String line : lines) {
            String trimmed = line.trim();
            if (trimmed.isEmpty() || trimmed.startsWith("#")) {
                continue;
            }
            dataLines.add(trimmed);
        }
        if (dataLines.isEmpty()) {
            return rows;
        }

        // Auto-detect delimiter from header line
        char delimiter = detectDelimiter(dataLines.getFirst());

        for (String line : dataLines) {
            rows.add(parseCsvRow(line, delimiter));
        }
        return rows;
    }

    /** Detect the most likely delimiter from a header row. */
    private static char detectDelimiter(String headerLine) {
        int[] counts = new int[256];
        boolean inQ = false;
        for (int i = 0; i < headerLine.length(); i++) {
            char ch = headerLine.charAt(i);
            if (ch == '"') inQ = !inQ;
            if (!inQ && (ch == ',' || ch == '\t' || ch == ';' || ch == '|')) {
                counts[ch]++;
            }
        }
        char best = ',';
        int max = counts[','];
        if (counts['\t'] > max) { best = '\t'; max = counts['\t']; }
        if (counts[';'] > max) { best = ';'; max = counts[';']; }
        if (counts['|'] > max) { best = '|'; max = counts['|']; }
        return best;
    }

    /** Parse a single CSV row into cells, respecting "..." quoted fields */
    private static String[] parseCsvRow(String line, char delimiter) {
        List<String> cells = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        boolean inQuotes = false;
        for (int i = 0; i < line.length(); i++) {
            char ch = line.charAt(i);
            if (inQuotes) {
                if (ch == '"') {
                    if (i + 1 < line.length() && line.charAt(i + 1) == '"') {
                        sb.append('"');
                        i++;
                    } else {
                        inQuotes = false;
                    }
                } else {
                    sb.append(ch);
                }
            } else {
                if (ch == '"') {
                    inQuotes = true;
                } else if (ch == delimiter) {
                    cells.add(sb.toString().trim());
                    sb.setLength(0);
                } else {
                    sb.append(ch);
                }
            }
        }
        cells.add(sb.toString().trim());
        return cells.toArray(new String[0]);
    }

    private static String require(Map<String, Object> body) {
        String v = str(body, "name");
        if (v == null || v.isBlank()) {
            throw new IllegalArgumentException("brand name required");
        }
        return v;
    }

    private static Consumer<String> fieldSetter(Brand b, String col) {
        return switch (col) {
            case "function_indication" -> b::setFunctionIndication;
            case "usage_dosage" -> b::setUsageDosage;
            case "contraindication" -> b::setContraindication;
            case "ingredients" -> b::setIngredients;
            case "properties" -> b::setProperties;
            case "specification" -> b::setSpecification;
            case "adverse_reactions" -> b::setAdverseReactions;
            case "precautions" -> b::setPrecautions;
            case "execution_standard" -> b::setExecutionStandard;
            case "storage" -> b::setStorage;
            case "jump_url" -> b::setJumpUrl;
            case "description" -> b::setDescription;
            default -> v -> {};
        };
    }

    private static String brandField(Brand b, String col) {
        return switch (col) {
            case "function_indication" -> b.getFunctionIndication();
            case "usage_dosage" -> b.getUsageDosage();
            case "contraindication" -> b.getContraindication();
            case "ingredients" -> b.getIngredients();
            case "properties" -> b.getProperties();
            case "specification" -> b.getSpecification();
            case "adverse_reactions" -> b.getAdverseReactions();
            case "precautions" -> b.getPrecautions();
            case "execution_standard" -> b.getExecutionStandard();
            case "storage" -> b.getStorage();
            case "jump_url" -> b.getJumpUrl();
            case "description" -> b.getDescription();
            case "sort_order" -> String.valueOf(b.getSortOrder());
            default -> "";
        };
    }

    private static String str(Map<String, Object> body, String key) {
        Object value = body.get(key);
        if (value == null) {
            return null;
        }
        String s = value.toString().trim();
        return s.isEmpty() ? "" : s;
    }

    private static String nullToEmpty(String s) { return s != null ? s : ""; }

    private static void applyStr(Map<String, Object> body, String key, Consumer<String> setter) {
        if (body.containsKey(key)) {
            setter.accept(str(body, key));
        }
    }

    private static String cell(String[] cols, Map<String, Integer> colIdx, String header) {
        Integer idx = colIdx.get(header);
        if (idx == null || idx >= cols.length) {
            return null;
        }
        String v = cols[idx].trim();
        // Strip surrounding double quotes
        if (v.length() >= 2 && v.startsWith("\"") && v.endsWith("\"")) {
            v = v.substring(1, v.length() - 1).trim();
        }
        return v.isEmpty() ? null : v;
    }

    private static int cellInt(String[] cols, Map<String, Integer> colIdx, String header) {
        String v = cell(cols, colIdx, header);
        if (v == null || v.isEmpty()) {
            return 0;
        }
        try { return Integer.parseInt(v); } catch (NumberFormatException e) { return 0; }
    }

    private static String esc(String s) {
        return (s == null || s.isEmpty()) ? "" : "\"" + s.replace("\"", "\"\"") + "\"";
    }

    private static int intVal(Map<String, Object> body) {
        return Optional.ofNullable(body.get("sort_order"))
                .filter(v -> v instanceof Number)
                .map(v -> ((Number) v).intValue())
                .orElse(0);
    }
}
