package jnpf.tmsUtil;

import jnpf.exception.DataException;
import jnpf.util.StringUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import javax.xml.parsers.DocumentBuilderFactory;
import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;

/**
 * TMS Excel 导入：支持 SpreadsheetML(.xls 本系统导出)、POI 二进制/xlsx、UTF-8 CSV。
 */
public final class TmsExcelImportHelper {

    private static final DataFormatter FORMATTER = new DataFormatter();

    private TmsExcelImportHelper() {
    }

    /**
     * 读取首个工作表全部行（含表头），单元格统一转字符串。
     */
    public static List<List<String>> readRows(byte[] bytes, String fileName) {
        if (bytes == null || bytes.length == 0) {
            throw new DataException("上传文件为空");
        }
        String name = fileName == null ? "" : fileName.toLowerCase();
        try {
            if (name.endsWith(".csv") || looksLikeCsv(bytes)) {
                return readCsv(bytes);
            }
            if (looksLikeSpreadsheetMl(bytes)) {
                return readSpreadsheetMl(bytes);
            }
            return readWithPoi(bytes);
        } catch (DataException e) {
            throw e;
        } catch (Exception e) {
            throw new DataException("无法解析导入文件，请使用模板填写后上传（xls/xlsx/csv）");
        }
    }

    private static boolean looksLikeSpreadsheetMl(byte[] bytes) {
        String head = new String(bytes, 0, Math.min(bytes.length, 200), StandardCharsets.UTF_8).trim();
        return head.startsWith("<?xml") || head.contains("urn:schemas-microsoft-com:office:spreadsheet");
    }

    private static boolean looksLikeCsv(byte[] bytes) {
        // 无扩展名时的兜底：首行含逗号且非 XML/ZIP
        if (bytes.length >= 2 && bytes[0] == 'P' && bytes[1] == 'K') {
            return false;
        }
        if (looksLikeSpreadsheetMl(bytes)) {
            return false;
        }
        String head = new String(bytes, 0, Math.min(bytes.length, 120), StandardCharsets.UTF_8);
        return head.contains(",") && !head.contains("<Workbook");
    }

    private static List<List<String>> readWithPoi(byte[] bytes) throws Exception {
        try (InputStream in = new ByteArrayInputStream(bytes);
             Workbook workbook = WorkbookFactory.create(in)) {
            Sheet sheet = workbook.getSheetAt(0);
            List<List<String>> rows = new ArrayList<>();
            int last = sheet.getLastRowNum();
            for (int i = 0; i <= last; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                int lastCell = Math.max(row.getLastCellNum(), 0);
                List<String> cells = new ArrayList<>(lastCell);
                boolean any = false;
                for (int c = 0; c < lastCell; c++) {
                    String v = cellText(row.getCell(c));
                    if (StringUtil.isNotEmpty(v)) {
                        any = true;
                    }
                    cells.add(v);
                }
                if (any) {
                    rows.add(cells);
                }
            }
            return rows;
        }
    }

    private static String cellText(Cell cell) {
        if (cell == null) {
            return "";
        }
        if (cell.getCellType() == CellType.FORMULA) {
            try {
                return FORMATTER.formatCellValue(cell).trim();
            } catch (Exception ignored) {
                return "";
            }
        }
        return FORMATTER.formatCellValue(cell).trim();
    }

    private static List<List<String>> readSpreadsheetMl(byte[] bytes) throws Exception {
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        factory.setNamespaceAware(true);
        factory.setFeature("http://apache.org/xml/features/disallow-doctype-decl", true);
        Document doc = factory.newDocumentBuilder().parse(new ByteArrayInputStream(bytes));
        NodeList rowNodes = doc.getElementsByTagNameNS("urn:schemas-microsoft-com:office:spreadsheet", "Row");
        if (rowNodes.getLength() == 0) {
            rowNodes = doc.getElementsByTagName("Row");
        }
        List<List<String>> rows = new ArrayList<>(rowNodes.getLength());
        for (int i = 0; i < rowNodes.getLength(); i++) {
            Element rowEl = (Element) rowNodes.item(i);
            NodeList children = rowEl.getChildNodes();
            List<String> cells = new ArrayList<>();
            boolean any = false;
            int col = 0;
            for (int j = 0; j < children.getLength(); j++) {
                Node n = children.item(j);
                if (!(n instanceof Element)) {
                    continue;
                }
                Element cell = (Element) n;
                if (!"Cell".equals(cell.getLocalName()) && !"Cell".equals(cell.getTagName())) {
                    continue;
                }
                String idx = cell.getAttributeNS("urn:schemas-microsoft-com:office:spreadsheet", "Index");
                if (StringUtil.isEmpty(idx)) {
                    idx = cell.getAttribute("ss:Index");
                }
                if (StringUtil.isNotEmpty(idx)) {
                    int target = Integer.parseInt(idx) - 1;
                    while (col < target) {
                        cells.add("");
                        col++;
                    }
                }
                String text = dataText(cell);
                if (StringUtil.isNotEmpty(text)) {
                    any = true;
                }
                cells.add(text);
                col++;
            }
            if (any) {
                rows.add(cells);
            }
        }
        return rows;
    }

    private static String dataText(Element cell) {
        NodeList dataNodes = cell.getElementsByTagNameNS("urn:schemas-microsoft-com:office:spreadsheet", "Data");
        if (dataNodes.getLength() == 0) {
            dataNodes = cell.getElementsByTagName("Data");
        }
        if (dataNodes.getLength() == 0) {
            return "";
        }
        return dataNodes.item(0).getTextContent() == null ? "" : dataNodes.item(0).getTextContent().trim();
    }

    private static List<List<String>> readCsv(byte[] bytes) throws Exception {
        Charset charset = detectCharset(bytes);
        try (BufferedReader reader = new BufferedReader(
                new InputStreamReader(new ByteArrayInputStream(bytes), charset))) {
            List<List<String>> rows = new ArrayList<>();
            String line;
            while ((line = reader.readLine()) != null) {
                if (StringUtil.isEmpty(line.trim())) {
                    continue;
                }
                if (rows.isEmpty() && line.startsWith("\uFEFF")) {
                    line = line.substring(1);
                }
                rows.add(parseCsvLine(line));
            }
            return rows;
        }
    }

    private static Charset detectCharset(byte[] bytes) {
        if (bytes.length >= 3
                && (bytes[0] & 0xFF) == 0xEF
                && (bytes[1] & 0xFF) == 0xBB
                && (bytes[2] & 0xFF) == 0xBF) {
            return StandardCharsets.UTF_8;
        }
        // Excel 中文环境常存 GBK
        String asUtf8 = new String(bytes, 0, Math.min(bytes.length, 200), StandardCharsets.UTF_8);
        if (asUtf8.contains("题库") || asUtf8.contains("题型") || asUtf8.contains("题干")) {
            return StandardCharsets.UTF_8;
        }
        return Charset.forName("GBK");
    }

    private static List<String> parseCsvLine(String line) {
        List<String> cells = new ArrayList<>();
        StringBuilder cur = new StringBuilder();
        boolean inQuote = false;
        for (int i = 0; i < line.length(); i++) {
            char ch = line.charAt(i);
            if (inQuote) {
                if (ch == '"') {
                    if (i + 1 < line.length() && line.charAt(i + 1) == '"') {
                        cur.append('"');
                        i++;
                    } else {
                        inQuote = false;
                    }
                } else {
                    cur.append(ch);
                }
            } else if (ch == '"') {
                inQuote = true;
            } else if (ch == ',') {
                cells.add(cur.toString().trim());
                cur.setLength(0);
            } else {
                cur.append(ch);
            }
        }
        cells.add(cur.toString().trim());
        return cells;
    }
}