/* ====================================================================
   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 cz.eurosoftworks.excelimporter;


import cz.eurosoftworks.excelimporter.model.CellTypes;
import cz.eurosoftworks.excelimporter.model.CellValue;
import cz.eurosoftworks.excelimporter.model.Record;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

import java.util.ArrayList;
import java.util.List;

/**
 * See org.xml.sax.helpers.DefaultHandler javadocs
 */
class SheetHandler extends DefaultHandler {
    enum xssfDataType {
        BOOL,
        ERROR,
        FORMULA,
        INLINESTR,
        SSTINDEX,
        NUMBER,
    }
    private ExcelReader excelReader;
    private List<String> headers = new ArrayList<>();
    private boolean recordRows = false;

    /**
     * Table with styles
     */
    private StylesTable stylesTable;

    /**
     * Table with unique strings
     */
    private ReadOnlySharedStringsTable sharedStringsTable;
    // Set when V start element is seen
    private boolean vIsOpen;
    // Set when cell start element is seen;
    // used when cell close element is seen.
    private xssfDataType nextDataType;
    // Used to format numeric cell values.
    private short formatIndex;
    private String formatString;
    private final DataFormatter formatter;
    private int thisColumn = -1;
    // The last column printed to the output stream
    private int lastColumnNumber = -1;
    // Gathers characters as they are seen.
    private StringBuffer value;

    private String lastColumn;
    private int columns;
    private Record lastRecord;
    private boolean firstColumn;
    private int lastColumnNum;
    private boolean maybeNull;


    public SheetHandler(ReadOnlySharedStringsTable sst, ExcelReader excelReader, StylesTable styles) {
        this.sharedStringsTable = sst;
        this.excelReader = excelReader;
        this.formatter = new DataFormatter();
        this.stylesTable = styles;
        this.value = new StringBuffer();
    }


    public void startElement(String uri, String localName, String name,
                             Attributes attributes) throws SAXException {

        if ("inlineStr".equals(name) || "v".equals(name)) {
            vIsOpen = true;
            // Clear contents cache
            value.setLength(0);
        }
        // c => cell
        else if ("c".equals(name)) {
            lastColumn = attributes.getValue("r");
            if ( lastColumn.equals("A2")) {
                recordRows = true;
                excelReader.setHeaders(headers);
                columns = headers.size();
                lastRecord = new Record();
            }
            if (lastColumn.replaceAll("[\\d]", "").equals("A") && !lastColumn.equals("A1")) {
                if (lastRecord.cellValues.size() > 0) {
                    excelReader.addRecord(lastRecord);
                }
                lastRecord = new Record();
                firstColumn = true;
            } else {
                firstColumn = false;
            }


            // Get the cell reference
            String r = attributes.getValue("r");
            int firstDigit = -1;
            for (int c = 0; c < r.length(); ++c) {
                if (Character.isDigit(r.charAt(c))) {
                    firstDigit = c;
                    break;
                }
            }

            // Set up defaults.
            maybeNull=false;
            this.nextDataType = xssfDataType.NUMBER;
            this.formatIndex = -1;
            this.formatString = null;
            String cellType = attributes.getValue("t");
            String cellStyleStr = attributes.getValue("s");
            if ("b".equals(cellType))
                nextDataType = xssfDataType.BOOL;
            else if ("e".equals(cellType))
                nextDataType = xssfDataType.ERROR;
            else if ("inlineStr".equals(cellType))
                nextDataType = xssfDataType.INLINESTR;
            else if ("s".equals(cellType))
                nextDataType = xssfDataType.SSTINDEX;
            else if ("str".equals(cellType))
                nextDataType = xssfDataType.FORMULA;
            else if (cellType == null) {
                maybeNull=true;
            } else if (cellStyleStr != null) {
                // It's a number, but almost certainly one
                //  with a special style or format
                int styleIndex = Integer.parseInt(cellStyleStr);
                XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
                this.formatIndex = style.getDataFormat();
                this.formatString = style.getDataFormatString();
                if (this.formatString == null)
                    this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
            }
        }
        XSSFSheetXMLHandler sd;
    }

    /*
       * (non-Javadoc)
       * @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String, java.lang.String, java.lang.String)
       */
    public void endElement(String uri, String localName, String name) throws SAXException {
        String thisStr;

        // v => contents of a cell
        if ("v".equals(name)) {
            maybeNull = false;
            if (recordRows) {
                if (lastColumn.equals("B2")) {
                    System.out.println("stuj");
                }
                // Process the value contents as required.
                // Do now, as characters() may be called more than once
                lastColumnNum = nameToColumn(lastColumn);
                CellValue<Object> cv = new CellValue<>();
                switch (nextDataType) {
                    case BOOL:
                        char first = value.charAt(0);
                        if (first == '0') {
                            cv.set(CellTypes.BOOLEAN, false);
                        } else {
                            cv.set(CellTypes.BOOLEAN, true);
                        }
                        break;

                    case ERROR:
                        cv.set(CellTypes.STRING, "ERROR: " + value.toString());
                        break;

                    case FORMULA:
                        // A formula could result in a string value,
                        // so always add double-quote characters.
                        cv.set(CellTypes.STRING, value.toString());
                        break;

                    case INLINESTR:
                        // TODO: have seen an example of this, so it's untested.
                        XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
                        cv.set(CellTypes.STRING,  rtsi.toString());
                        break;

                    case SSTINDEX:
                        String sstIndex = value.toString();
                        try {
                            int idx = Integer.parseInt(sstIndex);
                            XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
                            cv.set(CellTypes.STRING, rtss.toString());
                        }
                        catch (NumberFormatException ex) {
                            ex.printStackTrace();
                        }
                        break;

                    case NUMBER:
                        String n = value.toString();
                        if (this.formatString != null) {
                            thisStr = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex, this.formatString);
                            try {
                                int intV = Integer.valueOf(thisStr);
                                cv.set(CellTypes.INT, intV);
                            } catch (NumberFormatException ignored) {}
                            if (cv.getType() == null) {
                                try {
                                    Double doubleV = Double.valueOf(thisStr);
                                    cv.set(CellTypes.DOUBLE, doubleV);
                                } catch (NumberFormatException e) {
                                    cv.set(CellTypes.STRING, thisStr);
                                }
                            }
                        } else {
                            thisStr = n;
                            try {
                                int intV = Integer.valueOf(thisStr);
                                cv.set(CellTypes.INT, intV);
                            } catch (NumberFormatException ignored) {}
                            if (cv.getType() == null) {
                                try {
                                    Double doubleV = Double.valueOf(thisStr);
                                    cv.set(CellTypes.DOUBLE, doubleV);
                                } catch (NumberFormatException e) {
                                    cv.set(CellTypes.STRING, thisStr);
                                }
                            }
                        }
                        break;

                    default:
                        cv.set(CellTypes.STRING, "Unexpected type: " + nextDataType);
                        break;
                }

                lastRecord.cellValues.add(cv);
            } else {
                String sstIndex = value.toString();
                try {
                    int idx = Integer.parseInt(sstIndex);
                    XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
                    headers.add(rtss.toString());
                }
                catch (NumberFormatException ex) {
                    ex.printStackTrace();
                }
            }

            //System.out.println(thisStr);
            // Output after we've seen the string contents
            // Emit commas for any fields that were missing on this row
            if (lastColumnNumber == -1) {
                lastColumnNumber = 0;
            }

            // Update column
            if (thisColumn > -1)
                lastColumnNumber = thisColumn;

        } else if ("c".equals(name) && lastRecord != null && maybeNull) {
            CellValue<Object> cv = new CellValue();
            cv.set(CellTypes.NULL, null);
            lastRecord.cellValues.add(cv);
        }


    }

    /**
     * Captures characters only if a suitable element is open.
     * Originally was just "v"; extended for inlineStr also.
     */
    public void characters(char[] ch, int start, int length)
            throws SAXException {
        if (vIsOpen)
            value.append(ch, start, length);
    }

    /**
     *
     *
     * @param name
     * @return Index corresponding to the specified name
     */
    private int nameToColumn(String name) {
        int column = -1;
        for (int i = 0; i < name.length(); ++i) {
            int c = name.charAt(i);
            column = (column + 1) * 26 + c - 'A';
        }
        return column;
    }
}