/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */

package org.apache.poi.ss.usermodel;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertTrue;

import java.util.Calendar;
import java.util.Locale;

import org.apache.poi.ss.ITestDataProvider;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.JvmBugs;
import org.apache.poi.util.LocaleUtil;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

/**
 * Common superclass for testing automatic sizing of sheet columns
 *
 * @author Yegor Kozlov
 */
public abstract class BaseTestSheetAutosizeColumn {

    private final ITestDataProvider _testDataProvider;

    private static Locale userLocale;
    
    @BeforeClass
    public static void initLocale() {
        userLocale = LocaleUtil.getUserLocale();
        LocaleUtil.setUserLocale(Locale.ROOT);
    }
    
    @AfterClass
    public static void resetLocale() {
        LocaleUtil.setUserLocale(userLocale);
    }
    
    protected BaseTestSheetAutosizeColumn(ITestDataProvider testDataProvider) {
        _testDataProvider = testDataProvider;
    }
    
    protected void trackColumnsForAutoSizingIfSXSSF(Sheet sheet) {
        // do nothing for Sheet base class. This will be overridden for SXSSFSheets.
    }

    @Test
    public void numericCells() throws Exception {
        Workbook workbook = _testDataProvider.createWorkbook();
        fixFonts(workbook);
        DataFormat df = workbook.getCreationHelper().createDataFormat();
        Sheet sheet = workbook.createSheet();
        trackColumnsForAutoSizingIfSXSSF(sheet);

        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue(0); // getCachedFormulaResult() returns 0 for not evaluated formula cells
        row.createCell(1).setCellValue(10);
        row.createCell(2).setCellValue("10");
        row.createCell(3).setCellFormula("(A1+B1)*1.0"); // a formula that returns '10'

        Cell cell4 = row.createCell(4);       // numeric cell with a custom style
        CellStyle style4 = workbook.createCellStyle();
        style4.setDataFormat(df.getFormat("0.0000"));
        cell4.setCellStyle(style4);
        cell4.setCellValue(10); // formatted as '10.0000'

        row.createCell(5).setCellValue("10.0000");

        // autosize not-evaluated cells, formula cells are sized as if the result is 0
        for (int i = 0; i < 6; i++) sheet.autoSizeColumn(i);

        assertTrue(sheet.getColumnWidth(0) < sheet.getColumnWidth(1));  // width of '0' is less then width of '10'
        assertEquals(sheet.getColumnWidth(1), sheet.getColumnWidth(2)); // 10 and '10' should be sized equally
        assertEquals(sheet.getColumnWidth(3), sheet.getColumnWidth(0)); // formula result is unknown, the width is calculated  for '0'
        assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(5)); // 10.0000 and '10.0000'

        // evaluate formulas and re-autosize
        evaluateWorkbook(workbook);

        for (int i = 0; i < 6; i++) sheet.autoSizeColumn(i);

        assertTrue(sheet.getColumnWidth(0) < sheet.getColumnWidth(1));  // width of '0' is less then width of '10'
        assertEquals(sheet.getColumnWidth(1), sheet.getColumnWidth(2)); // columns 1, 2 and 3 should have the same width
        assertEquals(sheet.getColumnWidth(2), sheet.getColumnWidth(3)); // columns 1, 2 and 3 should have the same width
        assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(5)); // 10.0000 and '10.0000'
        
        workbook.close();
    }

    @Test
    public void booleanCells() throws Exception {
        Workbook workbook = _testDataProvider.createWorkbook();
        fixFonts(workbook);
        Sheet sheet = workbook.createSheet();
        trackColumnsForAutoSizingIfSXSSF(sheet);

        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue(0); // getCachedFormulaResult() returns 0 for not evaluated formula cells
        row.createCell(1).setCellValue(true);
        row.createCell(2).setCellValue("TRUE");
        row.createCell(3).setCellFormula("1 > 0"); // a formula that returns true

        // autosize not-evaluated cells, formula cells are sized as if the result is 0
        for (int i = 0; i < 4; i++) sheet.autoSizeColumn(i);

        assertTrue(sheet.getColumnWidth(1) > sheet.getColumnWidth(0));  // 'true' is wider than '0'
        assertEquals(sheet.getColumnWidth(1), sheet.getColumnWidth(2));  // 10 and '10' should be sized equally
        assertEquals(sheet.getColumnWidth(3), sheet.getColumnWidth(0));  // formula result is unknown, the width is calculated  for '0'

        // evaluate formulas and re-autosize
        evaluateWorkbook(workbook);

        for (int i = 0; i < 4; i++) sheet.autoSizeColumn(i);

        assertTrue(sheet.getColumnWidth(1) > sheet.getColumnWidth(0));  // 'true' is wider than '0'
        assertEquals(sheet.getColumnWidth(1), sheet.getColumnWidth(2));  // columns 1, 2 and 3 should have the same width
        assertEquals(sheet.getColumnWidth(2), sheet.getColumnWidth(3));  // columns 1, 2 and 3 should have the same width

        workbook.close();
    }

    @Test
    public void dateCells() throws Exception {
        Workbook workbook = _testDataProvider.createWorkbook();
        fixFonts(workbook);
        Sheet sheet = workbook.createSheet();
        trackColumnsForAutoSizingIfSXSSF(sheet);
        DataFormat df = workbook.getCreationHelper().createDataFormat();

        CellStyle style1 = workbook.createCellStyle();
        style1.setDataFormat(df.getFormat("m"));

        CellStyle style3 = workbook.createCellStyle();
        style3.setDataFormat(df.getFormat("mmm"));

        CellStyle style5 = workbook.createCellStyle(); //rotated text
        style5.setDataFormat(df.getFormat("mmm/dd/yyyy"));

        Calendar calendar = LocaleUtil.getLocaleCalendar(2010, 0, 1); // Jan 1 2010

        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue(DateUtil.getJavaDate(0));   //default date

        Cell cell1 = row.createCell(1);
        cell1.setCellValue(calendar);
        cell1.setCellStyle(style1);
        row.createCell(2).setCellValue("1"); // column 1 should be sized as '1'

        Cell cell3 = row.createCell(3);
        cell3.setCellValue(calendar);
        cell3.setCellStyle(style3);
        row.createCell(4).setCellValue("Jan");

        Cell cell5 = row.createCell(5);
        cell5.setCellValue(calendar);
        cell5.setCellStyle(style5);
        row.createCell(6).setCellValue("Jan/01/2010");

        Cell cell7 = row.createCell(7);
        cell7.setCellFormula("DATE(2010,1,1)");
        cell7.setCellStyle(style3); // should be sized as 'Jan'

        // autosize not-evaluated cells, formula cells are sized as if the result is 0
        for (int i = 0; i < 8; i++) sheet.autoSizeColumn(i);

        assertEquals(sheet.getColumnWidth(2), sheet.getColumnWidth(1)); // date formatted as 'm'
        assertTrue(sheet.getColumnWidth(3) > sheet.getColumnWidth(1));  // 'mmm' is wider than 'm'
        assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(3)); // date formatted as 'mmm'
        assertTrue(sheet.getColumnWidth(5) > sheet.getColumnWidth(3));  // 'mmm/dd/yyyy' is wider than 'mmm'
        assertEquals(sheet.getColumnWidth(6), sheet.getColumnWidth(5)); // date formatted as 'mmm/dd/yyyy'

        // YK: width of not-evaluated formulas that return data is not determined
        // POI seems to conevert '0' to Excel date which is the beginng of the Excel's date system

        // evaluate formulas and re-autosize
        evaluateWorkbook(workbook);

        for (int i = 0; i < 8; i++) sheet.autoSizeColumn(i);

        assertEquals(sheet.getColumnWidth(2), sheet.getColumnWidth(1)); // date formatted as 'm'
        assertTrue(sheet.getColumnWidth(3) > sheet.getColumnWidth(1));  // 'mmm' is wider than 'm'
        assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(3)); // date formatted as 'mmm'
        assertTrue(sheet.getColumnWidth(5) > sheet.getColumnWidth(3));  // 'mmm/dd/yyyy' is wider than 'mmm'
        assertEquals(sheet.getColumnWidth(6), sheet.getColumnWidth(5)); // date formatted as 'mmm/dd/yyyy'
        assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(7)); // date formula formatted as 'mmm'
        
        workbook.close();
    }

    @Test
    public void stringCells() throws Exception {
        Workbook workbook = _testDataProvider.createWorkbook();
        fixFonts(workbook);
        Sheet sheet = workbook.createSheet();
        trackColumnsForAutoSizingIfSXSSF(sheet);
        Row row = sheet.createRow(0);
        
        Font defaultFont = workbook.getFontAt((short)0);

        CellStyle style1 = workbook.createCellStyle();
        Font font1 = workbook.createFont();
        font1.setFontHeight((short)(2*defaultFont.getFontHeight()));
        style1.setFont(font1);

        row.createCell(0).setCellValue("x");
        row.createCell(1).setCellValue("xxxx");
        row.createCell(2).setCellValue("xxxxxxxxxxxx");
        row.createCell(3).setCellValue("Apache\nSoftware Foundation"); // the text is splitted into two lines
        row.createCell(4).setCellValue("Software Foundation");

        Cell cell5 = row.createCell(5);
        cell5.setCellValue("Software Foundation");
        cell5.setCellStyle(style1); // same as in column 4 but the font is twice larger than the default font

        for (int i = 0; i < 10; i++) sheet.autoSizeColumn(i);

        assertTrue(2*sheet.getColumnWidth(0) < sheet.getColumnWidth(1)); // width is roughly proportional to the number of characters
        assertTrue(2*sheet.getColumnWidth(1) < sheet.getColumnWidth(2));
        assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(3));
        boolean ignoreFontSizeX2 = JvmBugs.hasLineBreakMeasurerBug();
        assertTrue(ignoreFontSizeX2 || sheet.getColumnWidth(5) > sheet.getColumnWidth(4)); //larger font results in a wider column width
        
        workbook.close();
    }

    @Test
    public void rotatedText() throws Exception {
        Workbook workbook = _testDataProvider.createWorkbook();
        fixFonts(workbook);
        Sheet sheet = workbook.createSheet();
        trackColumnsForAutoSizingIfSXSSF(sheet);
        Row row = sheet.createRow(0);

        CellStyle style1 = workbook.createCellStyle();
        style1.setRotation((short)90);

        Cell cell0 = row.createCell(0);
        cell0.setCellValue("Apache Software Foundation");
        cell0.setCellStyle(style1);

        Cell cell1 = row.createCell(1);
        cell1.setCellValue("Apache Software Foundation");

        for (int i = 0; i < 2; i++) sheet.autoSizeColumn(i);

        int w0 = sheet.getColumnWidth(0);
        int w1 = sheet.getColumnWidth(1);

        assertTrue(w0*5 < w1); // rotated text occupies at least five times less horizontal space than normal text
        
        workbook.close();
    }

    @Test
    public void mergedCells() throws Exception {
        Workbook workbook = _testDataProvider.createWorkbook();
        fixFonts(workbook);
        Sheet sheet = workbook.createSheet();
        trackColumnsForAutoSizingIfSXSSF(sheet);

        Row row = sheet.createRow(0);
        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:B1"));

        Cell cell0 = row.createCell(0);
        cell0.setCellValue("Apache Software Foundation");

        int defaulWidth = sheet.getColumnWidth(0);
        sheet.autoSizeColumn(0);
        // column is unchanged if merged regions are ignored (Excel like behavior)
        assertEquals(defaulWidth, sheet.getColumnWidth(0));

        sheet.autoSizeColumn(0, true);
        assertTrue(sheet.getColumnWidth(0) > defaulWidth);
        
        workbook.close();
    }

    
    /**
     * Auto-Sizing a column needs to work when we have rows
     *  passed the 32767 boundary. See bug #48079
     */
    @Test
    public void largeRowNumbers() throws Exception {
       Workbook workbook = _testDataProvider.createWorkbook();
       fixFonts(workbook);
       Sheet sheet = workbook.createSheet();
       trackColumnsForAutoSizingIfSXSSF(sheet);
       
       Row r0 = sheet.createRow(0);
       r0.createCell(0).setCellValue("I am ROW 0");
       Row r200 = sheet.createRow(200);
       r200.createCell(0).setCellValue("I am ROW 200");
       
       // This should work fine
       sheet.autoSizeColumn(0);
       
       // Get close to 32767
       Row r32765 = sheet.createRow(32765);
       r32765.createCell(0).setCellValue("Nearly there...");
       sheet.autoSizeColumn(0);
       
       // To it
       Row r32767 = sheet.createRow(32767);
       r32767.createCell(0).setCellValue("At the boundary");
       sheet.autoSizeColumn(0);
       
       // And passed it
       Row r32768 = sheet.createRow(32768);
       r32768.createCell(0).setCellValue("Passed");
       Row r32769 = sheet.createRow(32769);
       r32769.createCell(0).setCellValue("More Passed");
       sheet.autoSizeColumn(0);
       
       // Long way passed
       Row r60708 = sheet.createRow(60708);
       r60708.createCell(0).setCellValue("Near the end");
       sheet.autoSizeColumn(0);
       
       workbook.close();
    }
    
    // TODO should we have this stuff in the FormulaEvaluator?
    private void evaluateWorkbook(Workbook workbook){
        FormulaEvaluator eval = workbook.getCreationHelper().createFormulaEvaluator();
        for(int i=0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            for (Row r : sheet) {
                for (Cell c : r) {
                    if (c.getCellTypeEnum() == CellType.FORMULA){
                        eval.evaluateFormulaCellEnum(c);
                    }
                }
            }
        }
    }

    protected static void fixFonts(Workbook workbook) {
        if (!JvmBugs.hasLineBreakMeasurerBug()) return;
        for (int i=workbook.getNumberOfFonts()-1; i>=0; i--) {
            Font f = workbook.getFontAt((short)0);
            if ("Calibri".equals(f.getFontName())) {
                f.setFontName("Lucida Sans");
            } else if ("Cambria".equals(f.getFontName())) {
                f.setFontName("Lucida Bright");
            }
        }
    }
}