package com.hfg.xml.msofficexml.xlsx.spreadsheetml;

import java.time.LocalDate;
import java.time.ZoneId;
import java.time.temporal.ChronoUnit;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

import com.hfg.datetime.DateUtil;
import com.hfg.util.BooleanUtil;
import com.hfg.util.StringUtil;
import com.hfg.util.collection.CollectionUtil;
import com.hfg.xml.XMLAttribute;
import com.hfg.xml.XMLTag;
import com.hfg.xml.XMLUtil;
import com.hfg.xml.msofficexml.xlsx.CellRange;
import com.hfg.xml.msofficexml.xlsx.CellRef;
import com.hfg.xml.msofficexml.xlsx.spreadsheetml.style.SsmlCellFormat;


//------------------------------------------------------------------------------
/**
 Represents an Office Open XML worksheet data cell (<ssml:c>) tag.

 @author J. Alex Taylor, hairyfatguy.com
 */
//------------------------------------------------------------------------------
// com.hfg XML/HTML Coding Library
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public
// License along with this library; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
//
// J. Alex Taylor, President, Founder, CEO, COO, CFO, OOPS hairyfatguy.com
// jataylor@hairyfatguy.com
//------------------------------------------------------------------------------

public class SsmlCell extends SsmlXMLTag
{
   private SsmlWorksheet  mParentWorksheet;
   private SsmlCellFormat mCellFormat;

   private CellRange      mMergedCellRange;

   private Object  mValue;
   private String  mStringValue;
   private XMLTag  mValueTag;
   private XMLTag  mFormulaTag;



   private static final LocalDate REFERENCE_DATE;

   static
   {
      Calendar cal = new GregorianCalendar();
      cal.set(1900, Calendar.JANUARY, 1);
      REFERENCE_DATE = cal.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
   }

   //###########################################################################
   // CONSTRUCTORS
   //###########################################################################

   //---------------------------------------------------------------------------
   public SsmlCell(SsmlWorksheet inParentWorksheet)
   {
      super(SsmlXML.CELL, inParentWorksheet.getParentDoc());
      mParentWorksheet = inParentWorksheet;
   }

   //---------------------------------------------------------------------------
   public SsmlCell(SsmlWorksheet inParentWorksheet, XMLTag inXMLTag)
   {
      this(inParentWorksheet);
      inXMLTag.verifyTagName(SsmlXML.CELL);

      if (CollectionUtil.hasValues(inXMLTag.getAttributes()))
      {
         for (XMLAttribute attr : inXMLTag.getAttributes())
         {
            setAttribute(attr);
         }
      }

      // Copy subtags over to this tag
      if (CollectionUtil.hasValues(inXMLTag.getSubtags()))
      {
         for (XMLTag subtag : (List<XMLTag>) (Object) inXMLTag.getSubtags())
         {
            addSubtag(subtag);
         }
      }
   }

   //###########################################################################
   // PUBLIC METHODS
   //###########################################################################

   //---------------------------------------------------------------------------
   public String toString()
   {
      Object value = getValue();
      return (value != null ? value.toString() : null);
   }

   //---------------------------------------------------------------------------
   public SsmlCellFormat getFormat()
   {
      if (null == mCellFormat)
      {
         // Check if it has been added via setAttribute()...
         if (hasAttribute(SsmlXML.STYLE_IDX_ATT))
         {
            int cellFormatId = Integer.parseInt(getAttributeValue(SsmlXML.STYLE_IDX_ATT));
            mCellFormat = getParentDoc().getStylesPart().getCellFormats().get(cellFormatId);
         }
         else
         {
            setFormat(new SsmlCellFormat(getParentDoc()));
         }
      }

      return mCellFormat;
   }

   //---------------------------------------------------------------------------
   public SsmlCell setFormat(SsmlCellFormat inValue)
   {
      mCellFormat = inValue;

      if (inValue != null)
      {
         setAttribute(SsmlXML.STYLE_IDX_ATT, inValue.getIndex());
      }
      else
      {
         removeAttribute(SsmlXML.STYLE_IDX_ATT.getLocalName());
      }

      return this;
   }

   //---------------------------------------------------------------------------
   public SsmlCell setRef(CellRef inValue)
   {
      if (inValue != null)
      {
         setAttribute(SsmlXML.REF_ATT, inValue);
      }
      else
      {
         removeAttribute(SsmlXML.REF_ATT.getLocalName());
      }

      return this;
   }

   //---------------------------------------------------------------------------
   public CellRef getRef()
   {
      CellRef value = null;

      String stringValue = getAttributeValue(SsmlXML.REF_ATT);
      if (StringUtil.isSet(stringValue))
      {
         value = new CellRef(stringValue);
      }

      return value;
   }

   //---------------------------------------------------------------------------
   public SsmlCell mergeDown(int inValue)
   {
      CellRef cellRef = getRef();
      if (null == cellRef)
      {
         throw new RuntimeException("A CellRef must be specified for the cell before it can be merged!");
      }

      int endColIndex = cellRef.getColIndex();

      if (mMergedCellRange != null)
      {
         mParentWorksheet.removeMergeCell(mMergedCellRange);
         endColIndex = mMergedCellRange.getEndCell().getColIndex();
      }

      CellRef refForEndingCell = new CellRef().setColIndex(endColIndex).setRowIndex(cellRef.getRowIndex() + inValue);

      mMergedCellRange = new CellRange(cellRef, refForEndingCell);
      mParentWorksheet.mergeCells(mMergedCellRange);

      // Ensure that the rest of the cells being merge have been created
      ensureMergedCellsExist(mMergedCellRange);

      return this;
   }

   //---------------------------------------------------------------------------
   public SsmlCell mergeRight(int inValue)
   {
      CellRef cellRef = getRef();
      if (null == cellRef)
      {
         throw new RuntimeException("A CellRef must be specified for the cell before it can be merged!");
      }

      int endRowIndex = cellRef.getRowIndex();

      if (mMergedCellRange != null)
      {
         mParentWorksheet.removeMergeCell(mMergedCellRange);
         endRowIndex = mMergedCellRange.getEndCell().getRowIndex();
      }

      CellRef refForEndingCell = new CellRef().setColIndex(cellRef.getColIndex() + inValue).setRowIndex(endRowIndex);

      mMergedCellRange = new CellRange(cellRef, refForEndingCell);
      mParentWorksheet.mergeCells(mMergedCellRange);

      // Ensure that the rest of the cells being merge have been created
      ensureMergedCellsExist(mMergedCellRange);

      return this;
   }

   //---------------------------------------------------------------------------
   public void addComment(XlsxComment inComment)
   {
      XlsxComment comment = inComment.clone().setCellRef(getRef());
      mParentWorksheet.getCommentsPart().addComment(comment);
   }

   //---------------------------------------------------------------------------
   @Override
   public String getContent()
   {
      return mStringValue;
   }

   //---------------------------------------------------------------------------
   @Override
   public SsmlCell setContent(CharSequence inContent)
   {
      setValue(inContent);

      return this;
   }

   //---------------------------------------------------------------------------
   @Override
   public SsmlCell addContent(CharSequence inContent)
   {
      return setValue((mStringValue != null ? mStringValue : "") + inContent.toString());
   }

   //---------------------------------------------------------------------------
   public SsmlCellType getCellType()
   {
      String attrValueString = getAttributeValue(SsmlXML.CELL_DATA_TYPE_ATT);

      return StringUtil.isSet(attrValueString) ? SsmlCellType.valueOf(attrValueString) : null;
   }
/*
   //---------------------------------------------------------------------------
   public SsmlCell setValue(String inValue)
   {
      setAttribute(SsmlXML.CELL_DATA_TYPE_ATT, DataType.inlineStr);

      XMLTag inlineStrTag = new XMLTag(SsmlXML.INLINE_STRING);
      addSubtag(inlineStrTag);

      XMLTag textTag = new XMLTag(SsmlXML.TEXT);
      textTag.setContent(inValue);
      inlineStrTag.addSubtag(textTag);

      return this;
   }
*/

   //---------------------------------------------------------------------------
   public SsmlCell setValue(Object inValue)
   {
      if (inValue != null)
      {
         if (inValue instanceof Integer)
         {
            setValue(((Integer)inValue).intValue());
         }
         else if (inValue instanceof Long)
         {
            setValue(((Long)inValue).longValue());
         }
         else if (inValue instanceof Float)
         {
            setValue(((Float)inValue).floatValue());
         }
         else if (inValue instanceof Double)
         {
            setValue(((Double)inValue).doubleValue());
         }
         else if (inValue instanceof Date)
         {
            // Store dates w/o a type and with a number value that is the number of days after Jan 1, 1900
            LocalDate inputDate = ((Date)inValue).toInstant().atZone(ZoneId.systemDefault()).toLocalDate();

            long days = ChronoUnit.DAYS.between(REFERENCE_DATE, inputDate);

            // For some obscure reason, Excel files built on a mac use 1904 as the reference date...
            if (getParentDoc().getWorkbook().getProperties().get1904BasedDates())
            {
               days -= (365 * 4);
            }
            else
            {
               // Not exactly sure why this is necessary but it is
               days += 2;
            }
            
            getValueTag().setContent(days + "");

            // Remember the string value
            mStringValue = DateUtil.getISO_8601_Date((Date)inValue);

            // Make sure that the cell is formatted as a date
            if (null == mCellFormat)
            {
               //<xf numFmtId="14" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
               setFormat(getParentDoc().getStylesPart().getDefaultDateCellFormat());
            }
         }
         else
         {
            setValue(inValue.toString());
         }
      }

      mValue = inValue;

      return this;
   }

   //---------------------------------------------------------------------------
   public SsmlCell setValue(SsmlTextRun inValue)
   {
      setAttribute(SsmlXML.CELL_DATA_TYPE_ATT, SsmlCellType.s);

      int index = getParentDoc().getSharedStringsPart().defineString(inValue);

      getValueTag().setContent(index + "");

      // Remember the string value
      mStringValue = inValue.toXML();
      mValue = mStringValue;

      return this;
   }

   //---------------------------------------------------------------------------
   public SsmlCell setTextRuns(List<SsmlTextRun> inValue)
   {
      setAttribute(SsmlXML.CELL_DATA_TYPE_ATT, SsmlCellType.s);

      int index = getParentDoc().getSharedStringsPart().defineString(inValue);

      getValueTag().setContent(index + "");

      // Remember the string value
      StringBuilder xml = new StringBuilder();
      for (SsmlTextRun run : inValue)
      {
         xml.append(run.toXML());
      }

      mStringValue = xml.toString();

      return this;
   }

   //---------------------------------------------------------------------------
   public SsmlCell setValue(String inValue)
   {
      if (inValue != null)
      {
         setAttribute(SsmlXML.CELL_DATA_TYPE_ATT, SsmlCellType.s);

         int index = getParentDoc().getSharedStringsPart().defineString(inValue);

         getValueTag().setContent(index + "");

         // Remember the string value
         mStringValue = inValue;
         mValue = inValue;
      }
      else
      {
         removeAttribute(SsmlXML.CELL_DATA_TYPE_ATT);
         if (mValueTag != null)
         {
            removeSubtag(mValueTag);
            mValueTag = null;
            mValue = null;
            mStringValue = null;
         }
      }

      return this;
   }

   //---------------------------------------------------------------------------
   public SsmlCell setFormula(String inValue)
   {
      // Clear any value
      removeAttribute(SsmlXML.CELL_DATA_TYPE_ATT);
      if (mValueTag != null)
      {
         removeSubtag(mValueTag);
         mValueTag = null;
         mValue = null;
         mStringValue = null;
      }

      // Set the formula
      getFormulaTag().setContent(inValue);

      return this;
   }

   //---------------------------------------------------------------------------
   public SsmlCell setValue(int inValue)
   {
      return setPrimitiveNumberValue(inValue + "");
   }

   //---------------------------------------------------------------------------
   public SsmlCell setValue(long inValue)
   {
      return setPrimitiveNumberValue(inValue + "");
   }

   //---------------------------------------------------------------------------
   public SsmlCell setValue(float inValue)
   {
      return setPrimitiveNumberValue(inValue + "");
   }

   //---------------------------------------------------------------------------
   public SsmlCell setValue(double inValue)
   {
      return setPrimitiveNumberValue(inValue + "");
   }


   //---------------------------------------------------------------------------
   public Object getValue()
   {
      if (null == mValue)
      {
         XMLTag valueTag = getValueTag();

         Object value = null;

         if (StringUtil.isSet(valueTag.getContent()))
         {
            SsmlCellType cellType = getCellType();
            if (null == cellType)
            {
               cellType = SsmlCellType.n;
            }


            switch (cellType)
            {
               case s: // shared string
                  value = XMLUtil.unescapeContent(getParentDoc().getSharedStringsPart().getString(Integer.parseInt(valueTag.getContent())));
                  break;
               case str: // string stored in the value tag
                  value = valueTag.getUnescapedContent();
                  break;
               case n: // number
                  try
                  {
                     Double doubleValue = Double.parseDouble(valueTag.getContent());
                     if (doubleValue.intValue() == doubleValue)
                     {
                        value = doubleValue.intValue();
                     }
                     else
                     {
                        value = doubleValue;
                     }
                  }
                  catch (Exception e)
                  {
                     value = valueTag.getUnescapedContent();
                  }

                  // Is it a date in disguise?
                  if (getFormat() != null
                      && value instanceof Integer
                      && getFormat().getNumberFormat() != null
                      && StringUtil.isSet(getFormat().getNumberFormat().getFormatCode())
                      && getFormat().getNumberFormat().getFormatCode().toLowerCase().indexOf("y") >= 0)
                  {
                     // Dates are stored as a number value that is the number of days after Jan 1, 1900
                     value = getDateFromDays((Integer) value);
                  }

                  break;
               case b:
                  value = BooleanUtil.valueOf(valueTag.getUnescapedContent());
                  break;
               default:
                  System.err.println("Support for cell type " + cellType + " not yet added! Returning null.");
                  // TODO
            }

            mValue = value;
         }
      }

      return mValue;
   }

   //---------------------------------------------------------------------------
   private Date getDateFromDays(int inDays)
   {
      Date date = null;
      try
      {
         // For some obscure reason, Excel files built on a mac use 1904 as the reference date...
         if (getParentDoc().getWorkbook().getProperties().get1904BasedDates())
         {
            inDays += (365 * 4);
         }
         else
         {
            // Not exactly sure why this is necessary but it is
            inDays -= 2;
         }

         LocalDate localDate = REFERENCE_DATE.plus(inDays, ChronoUnit.DAYS);

         date = Date.from(localDate.atStartOfDay(ZoneId.systemDefault()).toInstant());
      }
      catch (Exception e)
      {
         // Igore
      }

      return date;
   }

   //---------------------------------------------------------------------------
   private XMLTag getValueTag()
   {
      if (null == mValueTag)
      {
         // Check if it has been added via addSubtag()...
         mValueTag = getOptionalSubtagByName(SsmlXML.VALUE);
         if (null == mValueTag)
         {
            mValueTag = new XMLTag(SsmlXML.VALUE);
            addSubtag(mValueTag);
         }
      }

      return mValueTag;
   }

   //---------------------------------------------------------------------------
   private XMLTag getFormulaTag()
   {
      if (null == mFormulaTag)
      {
         // Check if it has been added via addSubtag()...
         mFormulaTag = getOptionalSubtagByName(SsmlXML.CELL_FORMULA);
         if (null == mFormulaTag)
         {
            mFormulaTag = new XMLTag(SsmlXML.CELL_FORMULA);
            addSubtag(mFormulaTag);
         }
      }

      return mFormulaTag;
   }

   //---------------------------------------------------------------------------
   private SsmlCell setPrimitiveNumberValue(String inStringValue)
   {
      setAttribute(SsmlXML.CELL_DATA_TYPE_ATT, SsmlCellType.n);

      getValueTag().setContent(inStringValue);

      // Remember the string value
      mStringValue = inStringValue;

      return this;
   }

   //---------------------------------------------------------------------------
   private void ensureMergedCellsExist(CellRange inCellRange)
   {
      SsmlSheetData sheetData = (SsmlSheetData) getParentNode().getParentNode();

      int startingRowIndex = inCellRange.getBeginCell().getRowIndex();
      int endingRowIndex = inCellRange.getEndCell().getRowIndex();

      for (int i = startingRowIndex; i <= endingRowIndex; i++)
      {
         SsmlRow row = sheetData.getRowByRowIndex(i);
         if (null == row)
         {
            row = sheetData.addRow(i);
         }

         for (int colIndx = inCellRange.getBeginCell().getColIndex(); colIndx <= inCellRange.getEndCell().getColIndex(); colIndx++)
         {
            SsmlCell cell = row.getCellByColIndex(colIndx);
            if (null == cell)
            {
               row.addCellByColIndex(colIndx);
            }
         }
      }
   }

}