package org.apache.poi.ss.unfixedBugs;

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

import java.awt.Desktop;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Locale;

import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFName;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTable;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;

import junit.framework.Assert;

public class TestXSSFSheetShiftRowsAndColumns {
    final File resultDir=new File("build/custom-reports-test");
    XSSFWorkbook workbook=null;
    XSSFSheet sheet=null;
    String fileName=null;
    final int numRows=4;
    final int numCols=4;
    final int INSERT_ROW=1;
    final int INSERT_COLUMN=1;
    final int FIRST_MERGE_ROW=INSERT_ROW+1;
    final int LAST_MERGE_ROW=numRows-1;
    final int FIRST_MERGE_COL=INSERT_COLUMN+1;
    final int LAST_MERGE_COL=numCols-1;
    
    public TestXSSFSheetShiftRowsAndColumns() {
        resultDir.mkdirs();
    }
    
    /**
     * This creates a workbook with one worksheet.  It then puts data in rows 0 to numRows-1 and colulmns
     * 0 to numCols-1.
     */
    @Before
    public void setup() {
        final String procName="TestXSSFSheetShiftRowsAndColumns.setup";
        workbook = new XSSFWorkbook();
        sheet = workbook.createSheet();

        for (int nRow = 0; nRow < numRows; ++nRow) {
            final XSSFRow row = sheet.createRow(nRow);
            for (int nCol = 0; nCol < numCols; ++nCol) {
                final XSSFCell cell = row.createCell(nCol);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(String.format("Row %d col %d", nRow, nCol));
            }
        }
        /*
         * Add a merge area
         */
        final CellRangeAddress range=new CellRangeAddress(FIRST_MERGE_ROW,LAST_MERGE_ROW,FIRST_MERGE_COL,LAST_MERGE_COL);
        sheet.addMergedRegion(range);
        System.out.println(String.format("\n%s: mergeArea=%s", procName,range));

    }

    
    /**
     * This method writes the workbook to resultDir/fileName.
     */
    @After
    public void cleanup() {
        final String procName="TestXSSFSheetRemoveTable.cleanup";
        if (workbook == null) {
            System.out.println(String.format(Locale.ROOT,"%s: workbook==null",procName));
            return;
        }
        
        if(fileName==null) {
            System.out.println(String.format(Locale.ROOT, "%s: fileName==null",procName));
            return;
        }
        
        final File file=new File(resultDir,fileName);
        try (OutputStream fileOut = new FileOutputStream(file)) {
            workbook.write(fileOut);
            System.out.println(String.format(Locale.ROOT, "%s: test file written to %s",procName,file.getAbsolutePath()));
        } catch (Exception e) {
            System.err.println(e.getMessage());
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    /**
     * Apply no shift.  The purpose of this is to test {@code testCellAddresses} and {@code testMergeRegion}.
     */
    @Test
    public void testNoShift() {
        final String procName="testNoShift";
        fileName=procName+".xlsx";
        
        testCellAddresses(procName,0,0);
        testMergeRegion(procName,0,0);
        
        System.out.println(String.format("%s: finished without error", procName));
    }
    
    @Test
    public void testShiftOneRowAndTestAddresses() {
        final String procName="testShiftOneRowAndTestAddresses";
        fileName=procName+".xlsx";
        final int nRowsToShift=1;
        
        sheet.shiftRows(INSERT_ROW, numRows-1, nRowsToShift);
        testCellAddresses(procName,nRowsToShift,0);
        System.out.println(String.format("%s: finished without error", procName));
    }
    
    @Test
    public void testShiftOneRowAndTestMergeRegion() {
        final String procName="testShiftOneRowAndTestMergeRegion";
        fileName=procName+".xlsx";
        final int nRowsToShift=1;
        
        sheet.shiftRows(INSERT_ROW, numRows-1, nRowsToShift);
        testMergeRegion(procName,nRowsToShift,0);
        System.out.println(String.format("%s: finished without error", procName));
    }
    
    @Test
    public void testShiftOneColumnAndTestAddresses() {
        final String procName="testShiftOneColumnAndTestAddresses";
        fileName=procName+".xlsx";
        final int nShift=1;
        
        sheet.shiftColumns(INSERT_COLUMN, numCols-1, nShift);
        testCellAddresses(procName,0,nShift);
        System.out.println(String.format("%s: finished without error", procName));
    }
    
    @Test
    public void testShiftOneColumnAndTestMergeRegion() {
        final String procName="testShiftOneRowAndTestMergeRegion";
        fileName=procName+".xlsx";
        final int nShift=1;
        
        sheet.shiftColumns(INSERT_COLUMN, numCols-1, nShift);
        testMergeRegion(procName,0,nShift);
        System.out.println(String.format("%s: finished without error", procName));
    }
    
    /**
     * Verify that the cell addresses are consistent
     */
    private void testCellAddresses(String procName,int nRowsToShift,int nColsToShift) {
        final int nNumRows=nRowsToShift+this.numCols;
        final int nNumCols=nColsToShift+this.numCols;
        for(int nRow=0;nRow<nNumRows;++nRow) {
            final XSSFRow row=sheet.getRow(nRow);
            if(row==null) {
                System.out.println(String.format("%s: Row %d is empty", procName,nRow));
                continue;
            }
            for(int nCol=0;nCol<nNumCols;++nCol) {
                final String address=new CellAddress(nRow,nCol).formatAsString();
                final XSSFCell cell=row.getCell(nCol);
                if(cell==null) {
                    System.out.println(String.format("%s: Cell %s is empty", procName,address));
                    continue;
                }
                final CTCell ctCell=cell.getCTCell();
                final Object cellAddress=cell.getAddress().formatAsString();
                final Object r=ctCell.getR();
                
                if(nCol==0 || nRow==0)
                System.out.println(String.format("%s: Row %d col %d address=%s cell.address=%s cell.getR=%s",procName, nRow,nCol,address,cellAddress,ctCell.getR()));
                assertEquals(String.format("%s: Testing cell.getAddress",procName),address,cellAddress);
                assertEquals(String.format("%s: Testing ctCell.getR",procName),address,r);
            }
        }
       
    }
    
    /**
    * Verify that the merge area is consistent
    */
    private void testMergeRegion(String procName,int nRowsToShift,int nColsToShift) {
       final CellRangeAddress range=sheet.getMergedRegion(0);
       assertEquals(String.format("%s: Testing merge area %s",procName,range),range,new CellRangeAddress(FIRST_MERGE_ROW,LAST_MERGE_ROW+nRowsToShift,FIRST_MERGE_COL,LAST_MERGE_COL+nColsToShift));
    }

 }