package com.yanzuoguang.excel;

import com.yanzuoguang.db.impl.DbRow;
import com.yanzuoguang.util.YzgError;
import com.yanzuoguang.util.base.ObjectHelper;
import com.yanzuoguang.util.exception.CodeException;
import com.yanzuoguang.util.exception.ExceptionHelper;
import com.yanzuoguang.util.helper.CheckerHelper;
import com.yanzuoguang.util.helper.FileHelper;
import com.yanzuoguang.util.helper.StringHelper;
import com.yanzuoguang.util.table.TableHead;
import com.yanzuoguang.util.table.TableHeadHelper;
import com.yanzuoguang.util.table.TableHeadItem;
import com.yanzuoguang.util.thread.ThreadHelper;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

/**
 * 控制台导出程序
 *
 * @param <T> 导出的数据
 * @author 颜佐光
 */
public class ExcelConsole<T> implements DbRow<T> {

    private final Pattern chinese = Pattern.compile("[^x00-xff]");
    /**
     * 配置信息
     */
    private final ExportData config;
    /**
     * 行数据处理
     */
    private final ExcelRow<T> rowHandle;

    /**
     * 行状态
     */
    private final ExcelStatus<T> excelStatus;

    /**
     * 工作薄
     */
    private SXSSFWorkbook workbook;

    /**
     * 工作Sheet
     */
    private Sheet sheet;

    /**
     * 单元格央视
     */
    private CellStyle defaultStyle;

    /**
     * 行号
     */
    private int rowIndex;
    /**
     * 行数据
     */
    private int rowData;
    /**
     * 开始数据行
     */
    private int startDataRowIndex = Integer.MAX_VALUE;
    /**
     * 结束数据行
     */
    private int endDataRowIndex = Integer.MIN_VALUE;
    /**
     * 缓存行数
     */
    private int cacheRow = 1000;

    /**
     * 控制台输出Excel
     *
     * @param exportData 导出数据
     */
    public ExcelConsole(ExportData exportData, ExcelStatus<T> excelStatus) {
        this(exportData, excelStatus, null);
    }

    /**
     * 导出成Excel
     *
     * @param config      导出信息
     * @param excelStatus 行状态处理
     * @param rowHandle   导出下载信息
     */
    public ExcelConsole(ExportData config, ExcelStatus<T> excelStatus, ExcelRow<T> rowHandle) {
        this.config = config;
        this.excelStatus = excelStatus;
        this.rowHandle = rowHandle;
    }

    /**
     * 缓存行
     *
     * @return 行号
     */
    public int getCacheRow() {
        return cacheRow;
    }

    /**
     * 缓存行
     *
     * @param cacheRow 行号
     */
    public void setCacheRow(int cacheRow) {
        this.cacheRow = cacheRow;
    }

    /**
     * 配置信息
     *
     * @return 配置数据
     */
    public ExportData getConfig() {
        return config;
    }

    /**
     * 行处理方式
     *
     * @return 当前处理行
     */
    public ExcelRow<T> getRowHandle() {
        ExcelRow<T> rowHandle;
        if (this.rowHandle == null) {
            rowHandle = new ExcelRowDefault<>();
        } else {
            rowHandle = this.rowHandle;
        }
        return rowHandle;
    }

    /**
     * 获取单位计算后的只
     *
     * @param from 来源值
     * @return 目标值
     */
    private short getUnit(int from) {
        return (short) (from * ExportData.ROW_HEIGHT_UNIT);
    }

    /**
     * 检测参数是否异常
     */
    public ExcelConsole<T> check() {
        CheckerHelper check = CheckerHelper.newInstance()
                .notBlankCheck("导出xls配置", this.config)
                .notBlankCheck("导出xls.标题", this.config.getTitle())
                .notBlankCheck("导出xls.子标题", this.config.getSubTitle())
                .notBlankCheck("导出xls.服务器路径", this.config.getServerPath())
                .notBlankCheck("导出xls.文件名", this.config.getFileName())
                .notBlankListCheck("导出xls.列", this.config.getColumns())
                .checkException();
        for (ExportColumn column : this.config.getColumns()) {
            check.notBlankCheck("导出xls.列名", column.getName())
                    .notBlankCheck("导出xls.标题", column.getTitle())
                    .checkException();
        }

        return this;
    }

    /**
     * 初始化Excel对象
     *
     * @return 需要初始化的Excel对象
     */
    protected TableHead initHead() {
        String[] columns = new String[this.config.getColumns().size()];
        int pos = 0;
        for (ExportColumn column : this.config.getColumns()) {
            column.columnIndex = pos;
            columns[pos++] = column.getTitle();
        }
        return TableHeadHelper.getTableHead(columns);
    }

    /**
     * 初始化Excel对象
     */
    protected void initExcel(TableHead head) {
        if (this.excelStatus != null) {
            excelStatus.excelInit(this);
        }
        if (this.workbook != null) {
            throw YzgError.getRuntimeException("034");
        }

        if (this.cacheRow < 1) {
            this.cacheRow = 1000;
        }

        // 创建工作簿对象
        workbook = new SXSSFWorkbook(this.cacheRow);
        workbook.setCompressTempFiles(true);
        sheet = workbook.createSheet();
        // 默认央视
        defaultStyle = initColumnCenterStyle(workbook);

        // 行和列都是从0开始计数，且起始结束都会合并

        // 写入标题、子标题
        rowIndex = 0;
        int columnLength = this.config.getColumns().size() - 1;
        writeTitle(rowIndex++, this.config.getTitle(), this.config.getTitleHeight(), columnLength);
        writeTitle(rowIndex++, this.config.getSubTitle(), this.config.getSubTitleHeight(), columnLength);
        // 写入自定义行
        writeDefineRow(this.config.getStartRows());
        // 写入头部信息
        writeHead(head);
        rowIndex += head.getTotalRow();
    }

    /**
     * 写入标题
     *
     * @param rowIndex     行号
     * @param content      内容
     * @param rowHeight    高度
     * @param columnLength 合并宽度
     */
    private void writeTitle(int rowIndex, String content, short rowHeight, int columnLength) {
        // 创建一行
        Row row = sheet.createRow(rowIndex);
        row.setHeight(getUnit(rowHeight));
        createCell(row, 0, content, defaultStyle);
        // 这里是合并excel中多列为1列
        CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 0, columnLength);
        addMergedRegion(region);
        setDefaultStyle(region, defaultStyle);
    }

    /**
     * 写入列头
     *
     * @param head 需要写入的列头
     */
    private void writeHead(TableHead head) {
        // 创建行
        Row[] rows = new Row[head.getTotalRow()];
        for (int i = 0; i < rows.length; i++) {
            rows[i] = sheet.createRow(i + rowIndex);
            rows[i].setHeight(getUnit(this.config.getHeadHeight()));
        }

        // 写入列头
        for (TableHeadItem headItem : head.getColumns()) {
            Row row = rows[headItem.getRow()];
            createCell(row, headItem.getColumn(), headItem.getName(), defaultStyle);

            // 判断是否需要合并列头
            if (headItem.getColumnCell() > 1 || headItem.getRowCell() > 1) {
                int rowStart = rowIndex + headItem.getRow();
                int rowEnd = rowStart + headItem.getRowCell() - 1;
                int columnStart = headItem.getColumn();
                int columnEnd = columnStart + headItem.getColumnCell() - 1;

                CellRangeAddress region = new CellRangeAddress(rowStart, rowEnd, columnStart, columnEnd);
                addMergedRegion(region);
                setDefaultStyle(region, defaultStyle);
            }
        }

        // 设置列宽度
        // 合并数据配置
        for (ExportColumn column : this.config.getColumns()) {
            sheet.setColumnWidth(column.columnIndex, getUnit(column.getWidth()));
            // 列样式
            column.cellStyle = createColumnStyle(this.workbook, column);
        }
    }

    private void writeDefineRow(List<ExcelDefineRow> rows) {
        // 创建行
        for (ExcelDefineRow row : rows) {
            row.rowIndex = rowIndex;
            Row sheetRow = sheet.createRow(rowIndex);
            sheetRow.setHeight(getUnit(row.getHeight()));
            for (ExcelDefineCell cell : row.getCells()) {
                cell.cellStyle = createCellStyle(cell);
                Object value = cell.getValue();
                cell.cell = createCell(sheetRow, cell.getColumn(), StringHelper.isNumber(value), StringHelper.toString(value), cell.cellStyle);
                // 合并单元格
                if (cell.getWidthSize() > 1 || cell.getHeightSize() > 1) {
                    int rowStart = rowIndex;
                    int rowEnd = rowStart + cell.getHeightSize() - 1;
                    int columnStart = cell.getColumn();
                    int columnEnd = columnStart + cell.getWidthSize() - 1;

                    CellRangeAddress region = new CellRangeAddress(rowStart, rowEnd, columnStart, columnEnd);
                    addMergedRegion(region);
                    setDefaultStyle(region, cell.cellStyle);
                }
            }
            rowIndex++;
        }
    }

    private void addMergedRegion(CellRangeAddress region) {
        for (int r = region.getFirstRow(); r <= region.getLastRow(); r++) {
            for (int c = region.getFirstColumn(); c <= region.getLastColumn(); c++) {
                if (r == region.getFirstRow() && c == region.getFirstColumn()) {
                    continue;
                }
                // 合并时清除非第一行第一列的值
                Row row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                Cell cell = row.getCell(c);
                if (cell == null) {
                    continue;
                }
                cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
            }
        }
        sheet.addMergedRegion(region);
    }

    private void writeDefineFormula(List<ExcelDefineRow> rows) {
        Map<String, Object> map = new HashMap<>(this.config.getColumns().size() * 2 + 3);
        // 设置列别名
        for (ExportColumn column : this.config.getColumns()) {
            map.put(column.getName(), String.format("%s:%s",
                            CellReferenceUtil.getCoordName(column.columnIndex, this.startDataRowIndex),
                            CellReferenceUtil.getCoordName(column.columnIndex, this.endDataRowIndex)
                    )
            );
            map.put(column.getName() + "_COL", CellReferenceUtil.getColName(column.columnIndex));
        }
        // 数据行开始行号
        map.put("startDataRowIndex", CellReferenceUtil.getRowName(this.startDataRowIndex));
        // 数据行结束行号
        map.put("endDataRowIndex", CellReferenceUtil.getRowName(this.endDataRowIndex));
        // 设置行别名
        for (ExcelDefineRow row : rows) {
            // 当前行号
            map.put("rowIndex", CellReferenceUtil.getRowName(row.rowIndex));

            for (ExcelDefineCell cell : row.getCells()) {
                String formula = cell.getFormula();
                if (StringHelper.isEmpty(formula)) {
                    continue;
                }
                formula = StringHelper.getFormat(formula, StringHelper.EMPTY, (sb, group, fieldFull, field, command) -> {
                    String value = StringHelper.getFirst(ObjectHelper.getString(map, field));
                    sb.append(value);
                });
                if (StringHelper.isDouble(formula)) {
                    return;
                }
                // 设置公式
                if (cell.cell == null) {
                    throw new CodeException("单元格没有找到，无法设置公式" + formula);
                }
                cell.cell.setCellFormula(formula);
            }
        }
    }

    /**
     * 开始生成Excel文件
     *
     * @return 当前对象
     */
    public ExcelConsole<T> open() {
        this.check();
        TableHead head = this.initHead();
        initExcel(head);

        return this;
    }

    /**
     * 循环处理单行数据
     *
     * @param t 需要处理的单行数据
     */
    @Override
    public void handle(T t) {
        // 数据开始行
        this.startDataRowIndex = Math.min(this.startDataRowIndex, this.rowIndex);
        // 数据结束行
        this.endDataRowIndex = Math.max(this.endDataRowIndex, this.rowIndex);

        ExcelRow<T> rowHandle = this.getRowHandle();
        // 创建一行
        Row row = sheet.createRow(rowIndex);
        // 高度
        int height = getUnit(this.config.getRowHeight());
        // 将当前組生成值密钥
        StringBuilder sb = new StringBuilder();
        // 写入本行内容
        for (ExportColumn column : this.config.getColumns()) {
            String columnName = column.getName();
            Object columnValue = rowHandle.get(t, columnName);
            String value = StringHelper.getFirst(StringHelper.toString(columnValue));
            // 合并組数据处理
            if (column.isMerger()) {
                sb.append(value.replace(":", "::"));
                sb.append(":");
                String groupValue = StringHelper.md5(sb.toString());
                // 更新合并内容
                column.groupData.updateMerger(rowIndex, groupValue);
            }
            // 单元格总宽度
            int cellWidth = getTextChineseLength(value) * 256;
            // 列宽度
            int columnWidth = getUnit(column.getWidth() - 10);
            // 当前单元格实际行高
            int nowCellHeight = StringHelper.getPage(cellWidth, columnWidth) * getUnit(this.config.getRowHeight());
            // 当前行行高
            height = Math.max(nowCellHeight, height);
            // 当不需要合并历史记录时，则创建新的内容
            createCell(row, column.columnIndex, StringHelper.isNumber(columnValue), value, column.cellStyle);
            // 合并列
            mergerColumn(column, column.columnIndex, false);
        }

        row.setHeight((short) Math.min(height, Short.MAX_VALUE));

        rowIndex++;
        rowData++;

        if (this.excelStatus != null) {
            excelStatus.excelRow(t, rowIndex);
        }
    }

    /**
     * 获取文本中文长度
     *
     * @param value 文本字符串
     * @return 内容
     */
    private int getTextChineseLength(String value) {
        // 中文长度
        int chinese = this.chinese.matcher(value).groupCount();
        // 实际内容宽度
        return value.length() + chinese;
    }

    /**
     * 合并列
     *
     * @param column    需要合并的列
     * @param columnPos 需要合并的列位置
     * @param last      是否最后一行，最后一行，则合并之前的数据
     */
    private void mergerColumn(ExportColumn column, int columnPos, boolean last) {
        // 判断列是否需要合并
        if (column.isMerger()) {
            ExcelMergerData mergerData = column.groupData;
            // 判断是否需要合并历史记录
            if (mergerData.isMergerFlag() || last) {
                // 合并历史记录单元格
                mergerData(column, mergerData, columnPos, last);
            }
        }
    }

    /**
     * 合并数据
     *
     * @param column       需要合并的列
     * @param mergerColumn 需要合并的列
     * @param columnPos    合并的列位置
     * @param last         是否最后一行，最后一行，则合并之前的数据
     */
    private void mergerData(ExportColumn column, ExcelMergerData mergerColumn, int columnPos, boolean last) {
        int rowStart = mergerColumn.getRowIndexHistory();
        int rowEnd = rowStart + mergerColumn.getRowCellHistory() - 1;
        if (last) {
            rowStart = mergerColumn.getRowIndex();
            rowEnd = rowStart + mergerColumn.getRowCell() - 1;
        }

        CellRangeAddress region = new CellRangeAddress(rowStart, rowEnd, columnPos, columnPos);
        addMergedRegion(region);
        setDefaultStyle(region, column.cellStyle);
    }

    /**
     * 会自动在生成完毕调用该函数
     *
     * @return 当前对象
     */
    public ExcelConsole<T> save() {
        if (this.excelStatus != null) {
            excelStatus.excelFinish(this);
        }
        if (workbook == null) {
            return this;
        }

        // 合并数据配置
        if (this.rowData > 0) {
            for (ExportColumn column : this.config.getColumns()) {
                mergerColumn(column, column.columnIndex, true);
            }
        }

        // 写入自定义行
        writeDefineRow(this.config.getEndRows());
        writeDefineFormula(this.config.getStartRows());
        writeDefineFormula(this.config.getEndRows());

        String fileNameTemp = this.getFileNameTemp();
        // 保存为临时文件
        try (OutputStream out = Files.newOutputStream(Paths.get(fileNameTemp))) {
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            ExceptionHelper.PrintError(ExcelConsole.class, e);
            throw YzgError.getRuntimeException("035");
        }
        // 判断临时文件是否存在
        File file = new File(fileNameTemp);
        if (!file.exists()) {
            throw YzgError.getRuntimeException("036", this.getFileName());
        }
        // 重命名成正式文件
        String fileName = this.getFileName();
        File toFile = new File(fileName);
        boolean b;
        do {
            b = file.renameTo(toFile);
            if (!b) {
                ThreadHelper.sleep(100);
            } else {
                break;
            }
        } while (file.exists());
        if (!b) {
            throw YzgError.getRuntimeException("036", fileName);
        }
        long maxTime = System.currentTimeMillis() + 30 * 1000;
        // 等待文件重命名成功
        while (!toFile.exists() && System.currentTimeMillis() <= maxTime) {
            ThreadHelper.sleep(100);
        }
        return this;
    }

    /**
     * 删除生成的Excel文件
     *
     * @return 当前文件
     */
    public ExcelConsole<T> remove() {
        if (this.workbook != null) {
            workbook = null;
        }
        File file = new File(this.getFileName());
        if (file.exists()) {
            if (!file.delete()) {
                throw YzgError.getRuntimeException("012", file.getName());
            }
        }
        return this;
    }

    /**
     * 获取保存文件名（全路径）
     *
     * @return 文件名
     */
    public String getFileName() {
        FileHelper.createDirectory(this.config.getServerPath());
        return String.format("%s/%s", this.config.getServerPath(), this.config.getFileName());
    }

    /**
     * 获取保存文件名（全路径）
     *
     * @return 文件名
     */
    public String getFileNameTemp() {
        return getFileName() + ".tmp";
    }

    /**
     * 创建单元格
     *
     * @param row       行
     * @param column    列
     * @param content   内容
     * @param cellStyle 设置默认样式
     * @return 单元格
     */
    private Cell createCell(Row row, int column, String content, CellStyle cellStyle) {
        return createCell(row, column, false, content, cellStyle);
    }

    /**
     * 创建单元格
     *
     * @param row       行
     * @param column    列
     * @param isNumber  是否是数字
     * @param content   内容
     * @param cellStyle 设置默认样式
     * @return 单元格
     */
    private Cell createCell(Row row, int column, boolean isNumber, String content, CellStyle cellStyle) {
        // 获取字节数、用于设置最大宽度
        Cell cell = row.createCell(column);
        if (isNumber) {
            cell.setCellValue(StringHelper.toDouble(content));
        } else {
            cell.setCellValue(StringHelper.getFirst(content));
        }
        cell.setCellStyle(cellStyle);
        return cell;
    }

    /**
     * <br>
     * <b>功能：</b>单元格的默认样式<br>
     * <b>作者：</b>yixq<br>
     * <b>@param wb
     * <b>@return</b>
     */
    public CellStyle initColumnCenterStyle(Workbook wb) {
        return createStyle(wb, true);
    }

    private CellStyle createStyle(Workbook wb, boolean isDefault) {
        CellStyle cellStyle = wb.createCellStyle();

        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        cellStyle.setFont(font);
        if (isDefault) {
            // 左右居中
            cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
            // 上下居中
            cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        }
        // 设置自动换行
        cellStyle.setWrapText(true);
        // 设置单元格的边框颜色．
        cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
        cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
        cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
        cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置单元格的边框为粗体
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        // 设置单元格的背景颜色．
        cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);

        return cellStyle;
    }

    private CellStyle createColumnStyle(SXSSFWorkbook wb, ExportColumn column) {
        CellStyle style = this.createStyle(wb, false);
        setAlignment(style, column.getAlignment());
        setVerticalAlignment(style, column.getVerticalAlignment());
        return style;
    }

    private CellStyle createCellStyle(ExcelDefineCell cell) {
        CellStyle style = this.createStyle(this.workbook, false);
        setAlignment(style, cell.getAlignment());
        setVerticalAlignment(style, cell.getVerticalAlignment());
        if (!cell.isTopBorder()) {
            style.setBorderTop(CellStyle.BORDER_NONE);
        }
        if (!cell.isBottomBorder()) {
            style.setBorderBottom(CellStyle.BORDER_NONE);
        }
        if (!cell.isLeftBorder()) {
            style.setBorderLeft(CellStyle.BORDER_NONE);
        }
        if (!cell.isRightBorder()) {
            style.setBorderRight(CellStyle.BORDER_NONE);
        }
        return style;
    }

    private void setAlignment(CellStyle style, int alignment) {
        // 左右居中
        switch (alignment) {
            case 1:
                style.setAlignment(CellStyle.ALIGN_LEFT);
                break;
            case 2:
                style.setAlignment(CellStyle.ALIGN_RIGHT);
                break;
            case 0:
            default:
                style.setAlignment(CellStyle.ALIGN_CENTER);
                break;
        }
    }

    private void setVerticalAlignment(CellStyle style, int verticalAlignment) {
        // 上下居中
        switch (verticalAlignment) {
            case 1:
                style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
                break;
            case 2:
                style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
                break;
            case 0:
            default:
                style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
                break;
        }
    }

    private void setDefaultStyle(CellRangeAddress region, CellStyle cellStyle) {
        // 使用RegionUtil类为合并后的单元格添加边框
        RegionUtil.setBorderTop(cellStyle.getBorderTop(), region, sheet, this.workbook);
        RegionUtil.setBorderLeft(cellStyle.getBorderLeft(), region, sheet, this.workbook);
        RegionUtil.setBorderRight(cellStyle.getBorderRight(), region, sheet, this.workbook);
        RegionUtil.setBorderBottom(cellStyle.getBorderBottom(), region, sheet, this.workbook);

        RegionUtil.setTopBorderColor(cellStyle.getTopBorderColor(), region, sheet, this.workbook);
        RegionUtil.setLeftBorderColor(cellStyle.getLeftBorderColor(), region, sheet, this.workbook);
        RegionUtil.setRightBorderColor(cellStyle.getRightBorderColor(), region, sheet, this.workbook);
        RegionUtil.setBottomBorderColor(cellStyle.getBottomBorderColor(), region, sheet, this.workbook);
    }
}