/* ====================================================================
   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.
==================================================================== */


namespace HH.WMS.Utils.NPOI.SS.Formula
{
    using System;
    using System.Text;
    using System.Text.RegularExpressions;
    using HH.WMS.Utils.NPOI.Util;
    using System.Globalization;

    /**
     * Formats sheet names for use in formula expressions.
     * 
     * @author Josh Micich
     */
    public class SheetNameFormatter
    {

        private static String BIFF8_LAST_COLUMN = "IV";
        private static int BIFF8_LAST_COLUMN_TEXT_LEN = BIFF8_LAST_COLUMN.Length;
        private static String BIFF8_LAST_ROW = (0x10000).ToString(CultureInfo.InvariantCulture);
        private static int BIFF8_LAST_ROW_TEXT_LEN = BIFF8_LAST_ROW.Length;

        private static char DELIMITER = '\'';

        private static string CELL_REF_PATTERN = "^([A-Za-z]+)([0-9]+)$";

        private SheetNameFormatter()
        {
            // no instances of this class
        }
        /**
         * Used to format sheet names as they would appear in cell formula expressions.
         * @return the sheet name UnChanged if there is no need for delimiting.  Otherwise the sheet
         * name is enclosed in single quotes (').  Any single quotes which were already present in the 
         * sheet name will be converted to double single quotes ('').  
         */
        public static String Format(String rawSheetName)
        {
            StringBuilder sb = new StringBuilder(rawSheetName.Length + 2);
            AppendFormat(sb, rawSheetName);
            return sb.ToString();
        }

        /**
         * Convenience method for when a StringBuilder is already available
         * 
         * @param out - sheet name will be Appended here possibly with delimiting quotes 
         */
        public static void AppendFormat(StringBuilder out1, String rawSheetName)
        {
            bool needsQuotes = NeedsDelimiting(rawSheetName);
            if (needsQuotes)
            {
                out1.Append(DELIMITER);
                AppendAndEscape(out1, rawSheetName);
                out1.Append(DELIMITER);
            }
            else
            {
                out1.Append(rawSheetName);
            }
        }

        public static void AppendFormat(StringBuilder out1, String workbookName, String rawSheetName)
        {
            bool needsQuotes = NeedsDelimiting(workbookName) || NeedsDelimiting(rawSheetName);
            if (needsQuotes)
            {
                out1.Append(DELIMITER);
                out1.Append('[');
                AppendAndEscape(out1, workbookName.Replace('[', '(').Replace(']', ')'));
                out1.Append(']');
                AppendAndEscape(out1, rawSheetName);
                out1.Append(DELIMITER);
            }
            else
            {
                out1.Append('[');
                out1.Append(workbookName);
                out1.Append(']');
                out1.Append(rawSheetName);
            }
        }

        private static void AppendAndEscape(StringBuilder sb, String rawSheetName)
        {
            int len = rawSheetName.Length;
            for (int i = 0; i < len; i++)
            {
                char ch = rawSheetName[i];
                if (ch == DELIMITER)
                {
                    // single quotes (') are encoded as ('')
                    sb.Append(DELIMITER);
                }
                sb.Append(ch);
            }
        }

        private static bool NeedsDelimiting(String rawSheetName)
        {
            int len = rawSheetName.Length;
            if (len < 1)
            {
                throw new Exception("Zero Length string is an invalid sheet name");
            }
            if (Char.IsDigit(rawSheetName[0]))
            {
                // sheet name with digit in the first position always requires delimiting
                return true;
            }
            for (int i = 0; i < len; i++)
            {
                char ch = rawSheetName[i];
                if (IsSpecialChar(ch))
                {
                    return true;
                }
            }
            if (Char.IsLetter(rawSheetName[0])
                    && Char.IsDigit(rawSheetName[len - 1]))
            {
                // note - values like "A$1:$C$20" don't Get this far 
                if (NameLooksLikePlainCellReference(rawSheetName))
                {
                    return true;
                }
            }
            if (NameLooksLikeBooleanLiteral(rawSheetName))
            {
                return true;
            }
            return false;
        }
        private static bool NameLooksLikeBooleanLiteral(String rawSheetName)
        {
            switch (rawSheetName[0])
            {
                case 'T':
                case 't':
                    return "TRUE".Equals(rawSheetName, StringComparison.OrdinalIgnoreCase);
                case 'F':
                case 'f':
                    return "FALSE".Equals(rawSheetName, StringComparison.OrdinalIgnoreCase);
            }
            return false;
        }
        /**
         * @return <c>true</c> if the presence of the specified Char in a sheet name would 
         * require the sheet name to be delimited in formulas.  This includes every non-alphanumeric 
         * Char besides Underscore '_'.
         */
        /* package */
        static bool IsSpecialChar(char ch)
        {
            // note - Char.IsJavaIdentifierPart() would allow dollars '$'
            if (Char.IsLetterOrDigit(ch))
            {
                return false;
            }
            switch (ch)
            {
                case '.': // dot is OK
                case '_': // Underscore is ok
                    return false;
                case '\n':
                case '\r':
                case '\t':
                    throw new Exception("Illegal Char (0x"
                            + StringUtil.ToHexString(ch) + ") found in sheet name");
            }
            return true;
        }


        /**
         * Used to decide whether sheet names like 'AB123' need delimiting due to the fact that they 
         * look like cell references.
         * <p/>
         * This code is currently being used for translating formulas represented with <code>Ptg</code>
         * tokens into human readable text form.  In formula expressions, a sheet name always has a 
         * trailing '!' so there is little chance for ambiguity.  It doesn't matter too much what this 
         * method returns but it is worth noting the likely consumers of these formula text strings:
         * <ol>
         * <li>POI's own formula parser</li>
         * <li>Visual reading by human</li>
         * <li>VBA automation entry into Excel cell contents e.g.  ActiveCell.Formula = "=c64!A1"</li>
         * <li>Manual entry into Excel cell contents</li>
         * <li>Some third party formula parser</li>
         * </ol>
         * 
         * At the time of writing, POI's formula parser tolerates cell-like sheet names in formulas
         * with or without delimiters.  The same goes for Excel(2007), both manual and automated entry.  
         * <p/>
         * For better or worse this implementation attempts to replicate Excel's formula renderer.
         * Excel uses range checking on the apparent 'row' and 'column' components.  Note however that
         * the maximum sheet size varies across versions.
         * @see org.apache.poi.hssf.util.CellReference
         */
        public static bool CellReferenceIsWithinRange(String lettersPrefix, String numbersSuffix)
        {
            return HH.WMS.Utils.NPOI.SS.Util.CellReference.CellReferenceIsWithinRange(lettersPrefix, numbersSuffix, HH.WMS.Utils.NPOI.SS.SpreadsheetVersion.EXCEL97);
        }

        /**
         * Note - this method assumes the specified rawSheetName has only letters and digits.  It 
         * cannot be used to match absolute or range references (using the dollar or colon char).
         * 
         * Some notable cases:
         *    <blockquote><table border="0" cellpAdding="1" cellspacing="0" 
         *                 summary="Notable cases.">
         *      <tr><th>Input </th><th>Result </th><th>Comments</th></tr>
         *      <tr><td>"A1" </td><td>true</td><td> </td></tr>
         *      <tr><td>"a111" </td><td>true</td><td> </td></tr>
         *      <tr><td>"AA" </td><td>false</td><td> </td></tr>
         *      <tr><td>"aa1" </td><td>true</td><td> </td></tr>
         *      <tr><td>"A1A" </td><td>false</td><td> </td></tr>
         *      <tr><td>"A1A1" </td><td>false</td><td> </td></tr>
         *      <tr><td>"A$1:$C$20" </td><td>false</td><td>Not a plain cell reference</td></tr>
         *      <tr><td>"SALES20080101" </td><td>true</td>
         *      		<td>Still needs delimiting even though well out of range</td></tr>
         *    </table></blockquote>
         *  
         * @return <c>true</c> if there is any possible ambiguity that the specified rawSheetName
         * could be interpreted as a valid cell name.
         */
        public static bool NameLooksLikePlainCellReference(String rawSheetName)
        {
            Regex matcher = new Regex(CELL_REF_PATTERN);
            if (!matcher.IsMatch(rawSheetName))
            {
                return false;
            }

            Match match = matcher.Matches(rawSheetName)[0];
            // rawSheetName == "Sheet1" Gets this far.
            String lettersPrefix = match.Groups[1].Value;
            String numbersSuffix = match.Groups[2].Value;
            return CellReferenceIsWithinRange(lettersPrefix, numbersSuffix);
        }

    }
}