package XLSX;

import Database.FisData;
import lombok.Cleanup;
import lombok.val;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;

public class ExcelFile {

    /**
     * Create Cell with specified value and style
     * @param row target row to add cell to
     * @param index cell index, start from 0
     * @param value cell value
     * @param style cell style
     * @return created cell
     */
    @SuppressWarnings("UnusedReturnValue")
    private static Cell CreateCell(Row row, int index, String value, CellStyle style){
        val cell = row.createCell(index);
        cell.setCellValue(value);
        cell.setCellStyle(style);
        return cell;
    }

    /**
     * Write FisData to Row
     * @param row target row to write data
     * @param data FisData to write
     */
    private static void WriteFisData(Row row, FisData data){
        row.createCell(0).setCellValue(data.id);
        row.createCell(1).setCellValue(data.AirlineCode);
        row.createCell(2).setCellValue(data.FlightNumber);
        row.createCell(3).setCellValue(data.DA);
        row.createCell(4).setCellValue(data.DI);
        row.createCell(5).setCellValue(data.Origin);
        row.createCell(6).setCellValue(data.Destination);
        row.createCell(7).setCellValue(data.Gate);
        row.createCell(8).setCellValue(data.Terminal);
        row.createCell(9).setCellValue(data.BaggageClaim);
        row.createCell(10).setCellValue(data.CheckinCounter);
        row.createCell(11).setCellValue(dtf.format(data.lastUpdated));
        row.createCell(12).setCellValue(dtf.format(data.scheduledTime));
        row.createCell(13).setCellValue(dtf.format(data.estimatedTime));
        row.createCell(14).setCellValue(dtf.format(data.actualTime));
        row.createCell(15).setCellValue(dtf.format(data.gateScheduledTime));
        row.createCell(16).setCellValue(dtf.format(data.gateEstimatedTime));
        row.createCell(17).setCellValue(dtf.format(data.gateActualTime));
        row.createCell(18).setCellValue(data.weather);
        row.createCell(19).setCellValue(String.format("%.1f",data.temperatureC));
        row.createCell(20).setCellValue(data.Remark);
        row.createCell(21).setCellValue(data.IsRead);
    }

    /**
     * Create Empty XLSX Workbook with specified filename and sheetname<br/>
     * The created workbook will have the following columns:<br/>
     * ID, AirlineCode, FlightNumber, DA, DI, Origin, Destination, Gate, Terminal, BaggageClaim, CheckinCounter, lastUpdated, scheduledTime, estimatedTime, actualTime, gateScheduledTime, gateEstimatedTime, gateActualTime, weather, temperatureC, Remark, IsRead
     * @param Filename target filename
     * @param Sheetname target sheetname
     * @return true if success, false if failed
     */
    public static boolean CreateEmptyWorkbook(String Filename, String Sheetname){
        try {
            @SuppressWarnings("unused")
            int maxrow = 100;
            // create workbook
            @Cleanup val workbook = new XSSFWorkbook();
            // create sheet
            val sheet = workbook.createSheet(Sheetname);

            // create font
            val headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerFont.setFontHeightInPoints((short) 16);
            headerFont.setFontName("Arial");

            // create cell style
            val headerCellStyle = workbook.createCellStyle();
            headerCellStyle.setFont(headerFont);

            // create cell headers
            val headerRow = sheet.createRow(0);
            CreateCell(headerRow, 0, "ID", headerCellStyle);
            //RestrictColumnToNumbersOnly(sheet, 0, 0, 1, maxrow, 1, 999999);
            CreateCell(headerRow, 1, "AirlineCode", headerCellStyle);
            //RestrictColumnToExactLength(sheet, 1, 1, 1, maxrow, 2);
            CreateCell(headerRow, 2, "FlightNumber", headerCellStyle);
            //RestrictColumnToNumbersOnly(sheet, 2, 2, 1, maxrow, 1, 9999);
            CreateCell(headerRow, 3, "DA", headerCellStyle);
            //RestrictCellToCharacter(sheet, 3, 3, 1, maxrow, "D", "A");
            CreateCell(headerRow, 4, "DI", headerCellStyle);
            //RestrictCellToCharacter(sheet, 4, 4, 1, maxrow, "D", "I");
            CreateCell(headerRow, 5, "Origin", headerCellStyle);
            //RestrictColumnToExactLength(sheet, 5, 5, 1, maxrow, 3);
            CreateCell(headerRow, 6, "Destination", headerCellStyle);

            // Berikut ini belum ada batasan
            CreateCell(headerRow, 7, "Gate", headerCellStyle);
            CreateCell(headerRow, 8, "Terminal", headerCellStyle);
            CreateCell(headerRow, 9, "BaggageClaim", headerCellStyle);
            CreateCell(headerRow, 10, "CheckinCounter", headerCellStyle);

            CreateCell(headerRow, 11, "lastUpdated", headerCellStyle);
            CreateCell(headerRow, 12, "scheduledTime", headerCellStyle);
            CreateCell(headerRow, 13, "estimatedTime", headerCellStyle);
            CreateCell(headerRow, 14, "actualTime", headerCellStyle);
            CreateCell(headerRow, 15, "gateScheduledTime", headerCellStyle);
            CreateCell(headerRow, 16, "gateEstimatedTime", headerCellStyle);
            CreateCell(headerRow, 17, "gateActualTime", headerCellStyle);
            //RestrictCellToDateTime(sheet, 11, 17, 1, maxrow);

            CreateCell(headerRow, 18, "weather", headerCellStyle);
            CreateCell(headerRow, 19, "temperatureC", headerCellStyle);
            CreateCell(headerRow, 20, "Remark", headerCellStyle);
            CreateCell(headerRow, 21, "IsRead", headerCellStyle);
            //RestrictCellToCharacter(sheet, 21, 21, 1, maxrow, "Y", "N", "0", "1");

            @Cleanup val out = new FileOutputStream(Filename);
            workbook.write(out);
            return true;
        } catch (Exception e){
            System.out.println("Error creating empty workbook "+Filename+ " with sheetname "+Sheetname+", Exception: "+e.getMessage());
        }
        return false;
    }

    /**
     * Save FisData array to XLSX file<br/>
     * If the file does not exist, it will create a new file with the specified filename and sheetname<br/>
     * If the file already exists, it will replace the sheet with the specified sheetname with the new data<br/>
     * Row 0 is header, Row 1 and so on is the data<br/>
     * @param filename target filename for XLSX file
     * @param sheetname target sheetname inside XLSX file
     * @param data FisData array to save
     * @return true if success, false if failed
     */
    @SuppressWarnings("unused")
    public static boolean SaveToXLSX(String filename, String sheetname, FisData[] data){
        try{
            File ff = new File(filename);
            if (!ff.exists()){
                if (!CreateEmptyWorkbook(filename, sheetname)){
                    return false;
                } else System.out.println("New workbook "+filename+" with sheetname "+sheetname+" created");
            }

            // sampai sini file ada
            if (data!=null){
                @Cleanup FileInputStream fis = new FileInputStream(filename);
                @Cleanup val workbook = new XSSFWorkbook(fis);
                var sheet = workbook.getSheet(sheetname);
                if (sheet==null) sheet = workbook.createSheet(sheetname);

                for(int i=0; i<data.length; i++){
                    val row = sheet.createRow(1+i);
                    WriteFisData(row, data[i]);
                }

                @Cleanup val out = new FileOutputStream(filename);
                workbook.write(out);

                return true;
            } else System.out.println("No data to save to "+filename);
        } catch (Exception e){
            System.out.println("Error saving to XLSX: "+e.getMessage());
        }
        return  false;
    }

    private final static DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    /**
     * Load FisData from XLSX file<br/>
     * @param filename target filename for XLSX file
     * @param sheetname target sheetname inside XLSX file, if null or empty, will load all sheet
     * @return FisData array, empty if failed
     */
    @SuppressWarnings("unused")
    public static FisData[] LoadFromXLSX(String filename, String sheetname){
        System.out.println("LoadFromXLSX: "+filename+", sheetname: "+sheetname);
        try{
            File ff = new File(filename);
            if (ff.isFile()){
                @Cleanup val fis = new FileInputStream(ff);
                @Cleanup val workbook = new XSSFWorkbook(fis);
                if (sheetname!=null && !sheetname.isEmpty()){
                    // spesifik sheet
                    val sheet = workbook.getSheet(sheetname);
                    if (sheet!=null){
                        val result = new ArrayList<FisData>();
                        val header = sheet.getRow(0);
                        if (HeaderRowComplete(header)){
                            System.out.println("LoadFromXLSX Header row complete");
                            // baca mulai row 1
                            for(int i=1; i<=sheet.getLastRowNum(); i++){
                                val rr = sheet.getRow(i);
                                if (rr!=null){
                                    try{
                                        val data = RowToFisData(result, rr);
                                        //System.out.println("Row read: "+data);
                                    } catch (Exception e){
                                        System.out.println("Error reading row: "+e.getMessage());
                                    }
                                }
                            }

                        }


                        return result.toArray(new FisData[0]);
                    } else System.out.println("Sheet "+sheetname+" not found in "+filename);
                }
            } else System.out.println("LoadFromXLSX failed, File "+filename+" not found");
        } catch (Exception e){
            System.out.println("Error loading from XLSX "+filename+", Exception: " +e.getMessage());
        }
        return new FisData[0];
    }

    private static double getValueasDouble(Cell cc){
        if (cc!=null){
            CellType ct = cc.getCellType();
            return switch (ct){
                case STRING -> {
                    try{
                        yield Double.parseDouble(cc.getStringCellValue());
                    } catch (Exception e){
                        yield 0;
                    }
                }
                case NUMERIC -> cc.getNumericCellValue();
                default -> 0;
            };
        }
        return 0;
    }

    private static String getValueastring(Cell cc){
        if (cc!=null){
            CellType ct = cc.getCellType();
            return switch (ct){
                case STRING -> cc.getStringCellValue();
                case NUMERIC -> String.valueOf((int)cc.getNumericCellValue());
                default -> "";
            };
        }
        return "";
    }

    private static int getValueasInt(Cell cc){
        if (cc!=null){
            CellType ct = cc.getCellType();
            return switch (ct){
                case STRING ->{
                    try{
                        yield Integer.parseInt(cc.getStringCellValue());
                    } catch (Exception e){
                        yield 0;
                    }
                }
                case NUMERIC -> (int) cc.getNumericCellValue();
                default -> 0;
            };
        }
        return 0;
    }

    private static char getValueasChar(Cell cc){
        if (cc!=null){
            CellType ct = cc.getCellType();
            return switch (ct){
                case STRING -> cc.getStringCellValue().charAt(0);
                case NUMERIC -> (char) cc.getNumericCellValue();
                default -> 0;
            };
        }
        return 0;
    }

    private static LocalDateTime getValueasDateTime(Cell cc){
        if (cc!=null){
            CellType ct = cc.getCellType();
            return switch (ct){
                case STRING -> {
                    String val = cc.getStringCellValue();
                    try{
                        yield LocalDateTime.parse(val, dtf);
                    } catch (Exception e){
                        yield null;
                    }
                }
                case NUMERIC -> {
                    try{
                        yield cc.getLocalDateTimeCellValue();
                    } catch (Exception e){
                        yield null;
                    }
                }
                default -> null;
            };
        }
        return null;
    }



    private static FisData RowToFisData(ArrayList<FisData> result, Row rr) {
        val data = new FisData();
        data.id = getValueasInt(rr.getCell(0));
        data.AirlineCode = getValueastring(rr.getCell(1));
        data.FlightNumber = getValueastring(rr.getCell(2));
        data.DA = getValueasChar(rr.getCell(3));
        data.DI = getValueasChar(rr.getCell(4));
        data.Origin = getValueastring(rr.getCell(5));
        data.Destination = getValueastring(rr.getCell(6));
        data.Gate = getValueastring(rr.getCell(7));
        data.Terminal = getValueastring(rr.getCell(8));
        data.BaggageClaim = getValueastring(rr.getCell(9));
        data.CheckinCounter = getValueastring(rr.getCell(10));
        data.lastUpdated = getValueasDateTime(rr.getCell(11));
        data.scheduledTime = getValueasDateTime(rr.getCell(12));
        data.estimatedTime = getValueasDateTime(rr.getCell(13));
        data.actualTime = getValueasDateTime(rr.getCell(14));
        data.gateScheduledTime = getValueasDateTime(rr.getCell(15));
        data.gateEstimatedTime = getValueasDateTime(rr.getCell(16));
        data.gateActualTime = getValueasDateTime(rr.getCell(17));
        data.weather = getValueastring(rr.getCell(18));
        data.temperatureC = getValueasDouble(rr.getCell(19));
        data.Remark = getValueastring(rr.getCell(20));
        data.IsRead = getValueastring(rr.getCell(21));
        result.add(data);
        return data;
    }

    /**
     * Save byte array to XLSX file<br/>
     * The file will be saved with the specified filename<br/>
     * The file will be checked if it is a valid XLSX file<br/>
     *
     * @param filename target filename for XLSX file
     * @param size size of filedata
     * @param filedata byte array to save
     * @return success if success, error: message if failed
     */
    public static String SaveToXLSX(String filename, int size, byte[] filedata) {
        if (filename!=null && !filename.isEmpty()){
            if (size>0){
                if (filedata!=null && filedata.length==size){
                    try{
                        Files.write(Path.of(filename), filedata);
                    } catch (Exception e){
                        return "error: "+e.getMessage();
                    }

                    // sampai sini file sudah tertulis
                    // cek apakah valid XLSX file
                    try{
                        @Cleanup val fis = new FileInputStream(filename);
                        @Cleanup val workbook = new XSSFWorkbook(fis);
                        val sheet = workbook.getSheetAt(0);
                        if (sheet!=null){
                            if (HeaderRowComplete(sheet.getRow(0))){

                                return "success";
                            } else return "error: header row not complete";
                        } else return "error: sheet is null, not valid XLSX file";
                    } catch (Exception e){
                        return "error: "+e.getMessage();
                    }
                } else return "error: filedata is null or size is not equal to filedata length";
            } else return "error: size is 0";
        } else return "error: filename is empty";

    }

    /**
     * Check if header row is complete<br/>
     * A complete header row contains ID, AirlineCode, FlightNumber, DA, DI, Origin, Destination, Gate, Terminal, BaggageClaim, CheckinCounter, lastUpdated, scheduledTime, estimatedTime, actualTime, gateScheduledTime, gateEstimatedTime, gateActualTime, weather, temperatureC, Remark, IsRead
     * @param header Row to check
     * @return true if complete, false if not complete
     */
    private static boolean HeaderRowComplete(Row header){
        if (header!=null){
            if (!header.getCell(0).getStringCellValue().equals("ID")) return false;
            if (!header.getCell(1).getStringCellValue().equals("AirlineCode")) return false;
            if (!header.getCell(2).getStringCellValue().equals("FlightNumber")) return false;
            if (!header.getCell(3).getStringCellValue().equals("DA")) return false;
            if (!header.getCell(4).getStringCellValue().equals("DI")) return false;
            if (!header.getCell(5).getStringCellValue().equals("Origin")) return false;
            if (!header.getCell(6).getStringCellValue().equals("Destination")) return false;
            if (!header.getCell(7).getStringCellValue().equals("Gate")) return false;
            if (!header.getCell(8).getStringCellValue().equals("Terminal")) return false;
            if (!header.getCell(9).getStringCellValue().equals("BaggageClaim")) return false;
            if (!header.getCell(10).getStringCellValue().equals("CheckinCounter")) return false;
            if (!header.getCell(11).getStringCellValue().equals("lastUpdated")) return false;
            if (!header.getCell(12).getStringCellValue().equals("scheduledTime")) return false;
            if (!header.getCell(13).getStringCellValue().equals("estimatedTime")) return false;
            if (!header.getCell(14).getStringCellValue().equals("actualTime")) return false;
            if (!header.getCell(15).getStringCellValue().equals("gateScheduledTime")) return false;
            if (!header.getCell(16).getStringCellValue().equals("gateEstimatedTime")) return false;
            if (!header.getCell(17).getStringCellValue().equals("gateActualTime")) return false;
            if (!header.getCell(18).getStringCellValue().equals("weather")) return false;
            if (!header.getCell(19).getStringCellValue().equals("temperatureC")) return false;
            if (!header.getCell(20).getStringCellValue().equals("Remark")) return false;
            if (!header.getCell(21).getStringCellValue().equals("IsRead")) return false;
            return true;
        }
        return false;
    }

    @SuppressWarnings("unused")
    private static void RestrictColumnToNumbersOnly(Sheet sheet, int columnstart, int columnend, int rowstart, int rowend, int minvalue, int maxvalue){
        if (sheet!=null){
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            DataValidationConstraint dvConstraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.INTEGER, DataValidationConstraint.OperatorType.BETWEEN, String.valueOf(minvalue), String.valueOf(maxvalue));
            CellRangeAddressList addressList = new CellRangeAddressList(rowstart, rowend, columnstart, columnend);
            DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
            sheet.addValidationData(validation);
        }

    }

    @SuppressWarnings("unused")
    private static void RestrictColumnToExactLength(Sheet sheet, int columnstart, int columnend, int rowstart, int rowend, int length){
        if (sheet!=null){
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();

            DataValidationConstraint dvConstraint = dvHelper.createTextLengthConstraint(DataValidationConstraint.OperatorType.EQUAL, String.valueOf(length), null);
            CellRangeAddressList addressList = new CellRangeAddressList(rowstart, rowend, columnstart, columnend);
            DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
            sheet.addValidationData(validation);
        }


    }

    @SuppressWarnings("unused")
    private static void RestrictCellToDateTime(Sheet sheet, int columnstart, int columnend, int rowstart, int rowend){
        if (sheet!=null){
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            DataValidationConstraint dvConstraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "1970-01-01", "2099-12-31", "yyyy-MM-dd HH:mm:ss");
            CellRangeAddressList addressList = new CellRangeAddressList(rowstart, rowend, columnstart, columnend);
            DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
            sheet.addValidationData(validation);

            CellStyle cs = sheet.getWorkbook().createCellStyle();
            CreationHelper createHelper = sheet.getWorkbook().getCreationHelper();
            cs.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
            for(int i=rowstart; i<=rowend; i++){
                Row row = sheet.getRow(i);
                if (row!=null){
                    for(int j=columnstart; j<=columnend; j++){
                        Cell cell = row.getCell(j);
                        if (cell!=null){
                            cell.setCellStyle(cs);
                        }
                    }
                }

            }

        }

    }

    @SuppressWarnings("unused")
    private static void RestrictCellToCharacter(Sheet sheet, int columnstart, int columend, int rowstart, int rowend,  String... allowedchar){
        if (sheet!=null){
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();

            DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(allowedchar);
            CellRangeAddressList addressList = new CellRangeAddressList(rowstart, rowend, columnstart, columend);
            DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
            validation.setShowErrorBox(true);
            sheet.addValidationData(validation);
        }
    }

}
