package net.ranides.assira.xls;

import lombok.Data;
import lombok.RequiredArgsConstructor;
import lombok.experimental.UtilityClass;
import net.ranides.assira.generic.Ref;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;

import net.ranides.assira.collection.lists.VirtualList;
import net.ranides.assira.collection.query.CQuery;
import net.ranides.assira.text.StringTraits;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Optional;

@UtilityClass
public class XLSUtils {

    public static List<String> text(Ref<Row> row) {
        return text(row.orElse(null));
    }

    public static List<String> text(Row row) {
        if(row == null) {
            return Collections.emptyList();
        }
        return new RowAsList(row);
    }

    public static String text(Cell cell) {
        if(cell == null) {
            return "";
        }
        switch(cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    public static CellInfo info(Cell cell) {
        return new CellInfo(cell);
    }

    public static RowInfo info(Row row) {
        return new RowInfo(row);
    }

    public static String fore(Cell cell) {
        CellStyle style = cell.getCellStyle();
        if(style instanceof HSSFCellStyle) {
            HSSFCellStyle hs = (HSSFCellStyle) style;
            HSSFWorkbook wb = (HSSFWorkbook)cell.getSheet().getWorkbook();
            return color(hs.getFont(wb).getHSSFColor(wb)).orElse("#000000");
        }
        if(style instanceof XSSFCellStyle) {
            XSSFCellStyle xs = (XSSFCellStyle) style;
            XSSFFont font = xs.getFont();
            return color(font.getXSSFColor()).orElse("#000000");
        }
        return "#000000";
    }


    public static String back(Cell cell) {
        return color(cell.getCellStyle().getFillForegroundColorColor()).orElse("#ffffff");
    }

    public static Optional<String> color(Color color) {
        if(color instanceof HSSFColor) {
            return Optional.of(color0((HSSFColor) color));
        }
        if(color instanceof ExtendedColor) {
            return Optional.of(color0((ExtendedColor) color));
        }
        return Optional.empty();
    }

    private static String color0(HSSFColor color) {
        short[] rgb = color.getTriplet();
        return String.format("#%02x%02x%02x", rgb[0], rgb[1], rgb[2]);
    }

    private static String color0(ExtendedColor color) {
        byte[] rgb = color.getRGB();
        return String.format("#%02x%02x%02x", Byte.toUnsignedInt(rgb[0]), Byte.toUnsignedInt(rgb[1]), Byte.toUnsignedInt(rgb[2]));
    }

    public static int getColumnIndex(XLSReader reader, String columnName) {
        if(columnName == null) {
            return 0;
        }
        if (StringTraits.isNumber(columnName)) {
            return Integer.parseInt(columnName);
        }
        if (columnName.length() == 1) {
            char c = columnName.charAt(0);
            if(c>='A' && c<='Z') {
                return c-'A';
            }
        }

        List<String> head = XLSUtils.text(reader.header(true));
        for(int i=0, n=head.size(); i<n; i++) {
            if(columnName.equalsIgnoreCase(head.get(i).trim())) {
                return i;
            }
        }

        return 0;
    }

    public static boolean isHeaderIndex(String text) {
        if(StringTraits.isBlank(text)) {
            return false;
        }
        if(StringTraits.isNumber(text)) {
            return false;
        }
        if (text.length() == 1) {
            char c = text.charAt(0);
            return c < 'A' || c > 'Z';
        }
        return true;
    }

    public static class RowInfo extends ArrayList<CellInfo> {

        public RowInfo(Row row) {
            super(CQuery.from().iterable(row).map(CellInfo::new).list());
        }
    }

    @Data
    public static class CellInfo {

        private final String back;
        private final String fore;
        private final CellType type;
        private final int size;

        public CellInfo(Cell cell) {
            this.back = back(cell);
            this.fore = fore(cell);
            this.type = cell.getCellType();
            this.size = text(cell).length();
        }
    }

    @RequiredArgsConstructor
    private static final class RowAsList extends VirtualList<String> {

        private final Row row;

        @Override
        public int size() {
            return row.getLastCellNum() - row.getFirstCellNum();
        }

        @Override
        public String get(int index) {
            return XLSUtils.text(row.getCell(row.getFirstCellNum() + index));
        }
    }
}
