using ExcelDataReader;
using System;
using System.Data;
using System.Globalization;
using System.IO;
using Volpe.Cafe.Utils;

namespace Volpe.Cafe.IO
{
    /// <summary>
    /// Provides methods for opening and parsing Excel files.
    /// </summary>
    [Serializable]
    public class Input
    {
        #region /*** Constructors ***/

        /// <summary>
        /// Initializes a new instance of the <see cref="Input"/> class, using the specified path for the location of the Excel
        /// workbook to open.
        /// </summary>
        /// <param name="path">The location of the Excel workbook to load.</param>
        public Input(string path)
        {
            const string XlOpenFileNotFound = "Error opening Excel workbook.  The specified file was not found.";

            if (!File.Exists(path))
            {
                throw new FileNotFoundException(XlOpenFileNotFound, path);
            }

            using (var stream = File.Open(path, FileMode.Open, FileAccess.Read))
            {
                using (var reader = ExcelReaderFactory.CreateReader(stream))
                {
                    this.SheetData = reader.AsDataSet().Tables;
                    this.SheetNames = this.GetWorksheetNames();
                }
            }
        }

        #endregion

        #region /*** Methods ***/

        /// <summary>
        /// Activates the worksheet specified by the given name, by obtaining the value of the worksheet into an
        /// internal buffer object.
        /// </summary>
        /// <param name="name">The name of the worksheet to activate within the active workbook.</param>
        public void ActivateWorksheet(string name)
        {
            for (int i = 0; i < this.SheetNames.Length; i++)
            {
                if (string.Equals(name, this.SheetNames[i], StringComparison.InvariantCultureIgnoreCase))
                {
                    this.ActivateWorksheet(i);
                    return;
                }
            }

            throw new ArgumentOutOfRangeException("name", "The specified worksheet name does not exist in this workbook.");
        }
        /// <summary>
        /// Activates the worksheet specified by the given index, by obtaining the value of the worksheet into an
        /// internal buffer object.
        /// </summary>
        /// <param name="index">The zero-based index of the worksheet to activate within the active workbook.</param>
        public void ActivateWorksheet(int index)
        {
            this.Buffer = this.SheetData[index];
        }

        #region /* Methods for parsing a cell into bool, int, double, string, etc. */

        /// <summary>
        /// Converts the value in the buffer at the specified row and column to its boolean equivalent.
        /// </summary>
        /// <param name="row">The row location of the value in the buffer to convert.</param>
        /// <param name="column">The column location of the value in the buffer to convert.</param>
        /// <returns>The boolean equivalent of the value in the buffer at the specified row and column.</returns>
        public bool GetBool(int row, int column)
        {
            string str = GetString(row, column);
            return str.Equals("TRUE");
        }
        /// <summary>
        /// Converts the value in the buffer at the specified row and column to its <see cref="Int32" /> equivalent.
        /// </summary>
        /// <param name="row">The row location of the value in the buffer to convert.</param>
        /// <param name="column">The column location of the value in the buffer to convert.</param>
        /// <param name="defaultValue">The default value to return if the value in the buffer at the specified row and
        ///   column could not be converted to an integer.</param>
        /// <returns>The Int32 equivalent of the value in the buffer at the specified row and column.</returns>
        public int GetInt32(int row, int column, int defaultValue = 0)
        {
            return this.Buffer.Rows[row][column].GetInt32(defaultValue);
        }
        /// <summary>
        /// Converts the value in the buffer at the specified row and column to its double equivalent.
        /// </summary>
        /// <param name="row">The row location of the value in the buffer to convert.</param>
        /// <param name="column">The column location of the value in the buffer to convert.</param>
        /// <param name="defaultValue">The default value to return if the value in the buffer at the specified row and
        ///   column could not be converted to a double.</param>
        /// <returns>The double equivalent of the value in the buffer at the specified row and column.</returns>
        public double GetDouble(int row, int column, double defaultValue = 0)
        {
            return this.Buffer.Rows[row][column].GetDouble(defaultValue);
        }
        /// <summary>
        /// Converts the value in the buffer at the specified row and column to its double or null equivalent.
        /// </summary>
        /// <param name="row">The row location of the value in the buffer to convert.</param>
        /// <param name="column">The column location of the value in the buffer to convert.</param>
        /// <returns>The double or null equivalent of the value in the buffer at the specified row and column.</returns>
        public double? GetDoubleNullable(int row, int column)
        {
            string str = GetString(row, column);
            return double.TryParse(str, NumberStyles.Any, NumberFormatInfo.InvariantInfo, out double result)
                ? result
                : (double?)null;
        }
        /// <summary>
        /// Converts the value in the buffer at the specified row and column to its char equivalent.
        /// </summary>
        /// <param name="row">The row location of the value in the buffer to convert.</param>
        /// <param name="column">The column location of the value in the buffer to convert.</param>
        /// <returns>The char equivalent of the value in the buffer at the specified row and column.</returns>
        public char GetChar(int row, int column)
        {
            string str = GetString(row, column);
            return str.Equals(string.Empty) ? '\0' : str[0];
        }
        /// <summary>
        /// Converts the value in the buffer at the specified row and column to its string equivalent.
        /// </summary>
        /// <param name="row">The row location of the value in the buffer to convert.</param>
        /// <param name="column">The column location of the value in the buffer to convert.</param>
        /// <returns>The string equivalent of the value in the buffer at the specified row and column.</returns>
        public string GetString(int row, int column)
        {
            object value = this.Buffer.Rows[row][column];
            return (value == null) ? string.Empty : value.ToString().Trim().ToUpper();
        }

        /// <summary>
        /// Obtains the value in the buffer at the specified row and column. If the value is null, an empty string will
        /// be returned instead.
        /// </summary>
        public object GetData(int row, int column)
        {
            object value = this.Buffer.Rows[row][column];
            return value ?? string.Empty;
        }
        /// <summary>
        /// Converts the value in the buffer at the specified row and column to its string equivalent trimmed to the 
        /// given length.
        /// </summary>
        /// <param name="row">The row location of the value in the buffer to convert.</param>
        /// <param name="column">The column location of the value in the buffer to convert.</param>
        /// <param name="trim">The length to trim the string to. Default value of 40.</param>
        /// <returns>The string equivalent of the value in the buffer at the specified row and column.
        /// If the string length is greater than the trim value all characters after the trim value index minus 3 will 
        /// be replaced with an elipse.
        /// </returns>
        public string GetTrimmedString(int row, int column, int trim = 40)
        {
            object value = this.Buffer.Rows[row][column];

            if (value == null) return string.Empty;

            string str = value.ToString().Trim().ToUpper();

            if (str.Length > trim) return str.Remove(trim - 3) + "...";

            return str;
        }
        #endregion

        private string[] GetWorksheetNames()
        {
            // get the collection and number of sheets
            int numSheets = this.SheetData.Count;

            // initialize the array of worksheet names
            string[] names = new string[numSheets];

            // scan each worksheet and save its name
            for (int i = 0; i < numSheets; i++)
            {
                names[i] = this.SheetData[i].TableName;
            }

            // return the array of worksheet names
            return names;
        }

        #endregion

        #region /*** Properties ***/

        /// <summary>Gets an array of worksheet names located within the current workbook.</summary>
        public string[] SheetNames { get; }

        /// <summary>Gets the number of rows in the buffer of the active worksheet within the current workbook.</summary>
        public int Rows => this.Buffer == null ? 0 : this.Buffer.Rows.Count;
        /// <summary>Gets the number of columns in the buffer of the active worksheet within the current workbook.</summary>
        public int Columns => this.Buffer == null ? 0 : this.Buffer.Columns.Count;

        /// <summary>Gets the data set obtained from an Excel input file.</summary>
        DataTableCollection SheetData { get; }
        /// <summary>Gets the data of the active worksheet located within the current workbook.</summary>
        DataTable Buffer { get; set; }

        #endregion
    }
}
