using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Collections.Specialized;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Tango.PPC.Shared.SQL
{
    /// <summary>
    /// Represents remote database query result composed of rows and columns.
    /// </summary>
    /// <example>
    /// <para>
    /// <i>
    /// The following example demonstrates how to set the connected machine's demo state and query for the connected machine's jobs.
    /// </i>
    /// </para>
    /// <code lang="C#" source="../Tango.FSE.Procedures/Examples/Sql/Program.cs" title="Remote SQL" region="Example" />
    /// </example>
    public class RemoteSqlDataSet
    {
        /// <summary>
        /// Gets or sets the dataset columns.
        /// </summary>
        public RemoteSqlColumnCollection Columns { get; set; }

        private ObservableCollection<RemoteSqlRow> _rows;
        /// <summary>
        /// Gets or sets the dataset rows.
        /// </summary>
        public ObservableCollection<RemoteSqlRow> Rows
        {
            get { return _rows; }
            set { _rows = value; OnRowsChanged(); }
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="RemoteSqlDataSet"/> class.
        /// </summary>
        public RemoteSqlDataSet()
        {
            Columns = new RemoteSqlColumnCollection();
            Rows = new ObservableCollection<RemoteSqlRow>();
        }

        private void OnRowsChanged()
        {
            if (Rows != null)
            {
                Rows.CollectionChanged -= Rows_CollectionChanged;
                Rows.CollectionChanged += Rows_CollectionChanged;

                InitRows();
            }
        }

        private void Rows_CollectionChanged(object sender, NotifyCollectionChangedEventArgs e)
        {
            InitRows();
        }

        private void InitRows()
        {
            if (Rows != null)
            {
                foreach (var row in Rows.ToList())
                {
                    row.Init(
                        (key) =>
                        {
                            return row.Values[Columns.GetIndexOf(key)];
                        },
                        (index) =>
                        {
                            return row.Values[index];
                        });
                }
            }
        }

        /// <summary>
        /// Creates a new <see cref="RemoteSqlDataSet"/> using the specified <see cref="SqlDataReader"/>.
        /// </summary>
        /// <param name="reader">The reader.</param>
        /// <returns></returns>
        public static Task<RemoteSqlDataSet> Load(SqlDataReader reader)
        {
            return Task.Factory.StartNew<RemoteSqlDataSet>(() =>
            {
                bool columnsRead = false;
                RemoteSqlDataSet dataSet = new RemoteSqlDataSet();

                try
                {
                    while (reader.Read())
                    {
                        RemoteSqlRow row = new RemoteSqlRow();

                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            if (!columnsRead)
                            {
                                dataSet.Columns.Add(new RemoteSqlColumn()
                                {
                                    Name = reader.GetName(i)
                                });
                            }

                            row.Values.Add(reader.GetValue(i));
                        }

                        columnsRead = true;
                        dataSet.Rows.Add(row);
                    }
                }
                finally
                {
                    reader.Close();
                }

                return dataSet;
            });
        }

        /// <summary>
        /// Returns a <see cref="System.String" /> that represents this instance.
        /// </summary>
        /// <returns>
        /// A <see cref="System.String" /> that represents this instance.
        /// </returns>
        public override string ToString()
        {
            return String.Join(", ", Columns.Select(x => x.Name)) + "\n" + String.Join(Environment.NewLine, Rows.Select(x => x.ToString()));
        }

        /// <summary>
        /// Formats this dataset as a string with columns and rows.
        /// </summary>
        /// <returns></returns>
        public String ToTableString()
        {
            Dictionary<int, int> columnsMaxLength = new Dictionary<int, int>();

            for (int i = 0; i < Columns.Count; i++)
            {
                columnsMaxLength.Add(i, Columns[i].Name.Length);
            }

            foreach (var row in Rows)
            {
                for (int i = 0; i < row.Values.Count; i++)
                {
                    int valueLength = row.Values[i].ToStringSafe().Length;

                    if (valueLength > columnsMaxLength[i])
                    {
                        columnsMaxLength[i] = valueLength;
                    }
                }
            }

            String str = String.Empty;

            for (int i = 0; i < Columns.Count; i++)
            {
                str += $"{Columns[i].Name.PadRight(columnsMaxLength[i])}{(i < Columns.Count - 1 ? " | " : "")}";
            }

            int width = str.Length;
            str += Environment.NewLine + String.Empty.PadRight(width, '-');

            foreach (var row in Rows)
            {
                str += Environment.NewLine;

                for (int i = 0; i < row.Values.Count; i++)
                {
                    str += $"{row.Values[i].ToStringSafe().PadRight(columnsMaxLength[i])}{(i < Columns.Count - 1 ? " | " : "")}";
                }
            }

            return str;
        }
    }
}