using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using Microsoft.SqlServer.Server;
using Neo.Afx.ComponentModel;

namespace Neo.Afx.Sys
{
    /// <summary>
    /// The formatter.
    /// </summary>
    public class EntityFormatter
    {
        /// <summary>
        /// Selects data from the given entity and inserts it into the format string.
        /// </summary>
        /// <param name="entityID">The entity ID.</param>
        /// <param name="itemID">The item ID to be queried.</param>
        /// <param name="formatString">The format string.</param>
        /// <returns>The formatted string.</returns>
        [SqlFunction(
            IsDeterministic = true,
            IsPrecise = true,
            DataAccess = DataAccessKind.Read,
            SystemDataAccess = SystemDataAccessKind.Read
            )]
        public static SqlString GetFormattedEntity(SqlInt32 entityID, SqlInt32 itemID, SqlString formatString)
        {
            return new SqlString(GetFormattedEntityInternal(SqlUtilities.ClrConnectionString, EntityFormatType.Replacement, entityID.Value, itemID.Value, formatString.Value));
        }

        /// <summary>
        /// Selects data from the given entity and returns it as XML.
        /// </summary>
        /// <param name="entityID">The entity ID.</param>
        /// <param name="itemID">The item ID to be queried.</param>
        /// <returns>The formatted string.</returns>
        [SqlFunction(
            IsDeterministic = true,
            IsPrecise = true,
            DataAccess = DataAccessKind.Read,
            SystemDataAccess = SystemDataAccessKind.Read
            )]
        public static SqlXml GetEntityAsXml(SqlInt32 entityID, SqlInt32 itemID)
        {
            var root = XElement.Parse(string.Format("<e>{0}</e>",
                GetFormattedEntityInternal(SqlUtilities.ClrConnectionString, EntityFormatType.Xml, entityID.Value, itemID.Value, string.Empty)));
            return new SqlXml(root.CreateReader());
        }

        static string GetFormattedEntityInternal(String connectionString, EntityFormatType formatType, Int32 entityID, Int32 itemID, String formatString)
        {
            using(var connection = new SqlConnection(connectionString))
            {
                using(var cmd = connection.CreateCommand())
                {
                    cmd.Parameters.Add(new SqlParameter("entityID", entityID));

                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "SELECT EntityKey, EntityViewName FROM System.Entities WHERE EntityID = @EntityID";
                    cmd.Connection.Open();
                    using(var reader = cmd.ExecuteReader(CommandBehavior.Default))
                    {
                        if(reader.Read())
                        {
                            using(var readerCmd = connection.CreateCommand())
                            {
                                readerCmd.Parameters.Add(new SqlParameter("ItemID", itemID));

                                readerCmd.CommandType = CommandType.Text;
                                readerCmd.CommandText = "SELECT * FROM " + reader["EntityViewName"] + " WHERE " + reader["EntityKey"] + " = @ItemID";

                                reader.Close();

                                using(var entityReader = readerCmd.ExecuteReader(CommandBehavior.Default))
                                {
                                    if(entityReader.Read())
                                    {
                                        return formatString.Replace(formatType, entityReader);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            return formatString;
        }

        /// <summary>
        /// Gets the entity as CSV.
        /// </summary>
        /// <param name="entityID">The entity ID.</param>
        /// <param name="itemID">The item ID.</param>
        /// <returns></returns>
        [SqlFunction(
            IsDeterministic = true,
            IsPrecise = true,
            DataAccess = DataAccessKind.Read,
            SystemDataAccess = SystemDataAccessKind.Read
            )]
        public static SqlBytes GetEntityAsCsv(SqlInt32 entityID, SqlInt32 itemID)
        {
            byte[] fileResult;

            using(var dt = GetEntityTableInternal(SqlUtilities.ClrConnectionString, entityID.Value, itemID.Value))
            {
                using(var ms = new MemoryStream())
                using(var sw = new StreamWriter(ms))
                {
                    var iColCount = dt.Columns.Count;
                    for(var i = 0; i < iColCount; i++)
                    {
                        sw.Write(dt.Columns[i]);
                        if(i < iColCount - 1)
                        {
                            sw.Write(',');
                        }
                    }
                    sw.Write(sw.NewLine);

                    foreach(DataRow dr in dt.Rows)
                    {
                        for(var i = 0; i < iColCount; i++)
                        {
                            if(!Convert.IsDBNull(dr[i]))
                            {
                                sw.Write(dr[i].ToString());
                            }
                            if(i < iColCount - 1)
                            {
                                sw.Write(',');
                            }
                        }
                        sw.Write(sw.NewLine);
                    }

                    sw.Flush();
                    fileResult = ms.ToArray();
                }
            }

            return new SqlBytes(fileResult);
        }

        static DataTable GetEntityTableInternal(String connectionString, Int32 entityID, Int32 itemID)
        {
            var dt = new DataTable();
            using(var connection = new SqlConnection(connectionString))
            {
                using(var cmd = connection.CreateCommand())
                {
                    cmd.Parameters.Add(new SqlParameter("entityID", entityID));

                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "SELECT EntityKey, EntityViewName FROM System.Entities WHERE EntityID = @EntityID";
                    cmd.Connection.Open();
                    using(var reader = cmd.ExecuteReader(CommandBehavior.Default))
                    {
                        if(reader.Read())
                        {
                            using(var readerCmd = connection.CreateCommand())
                            {
                                readerCmd.Parameters.Add(new SqlParameter("ItemID", itemID));

                                readerCmd.CommandType = CommandType.Text;
                                readerCmd.CommandText = "SELECT * FROM " + reader["EntityViewName"] + " WHERE " + reader["EntityKey"] + " = @ItemID";

                                reader.Close();

                                using(var entityReader = readerCmd.ExecuteReader(CommandBehavior.Default))
                                {
                                    if(entityReader.Read())
                                    {
                                        dt.Load(entityReader);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// Gets the clean string ASCII.
        /// </summary>
        /// <param name="formatString">The format string.</param>
        /// <param name="cleanWith">The clean with.</param>
        /// <returns></returns>
        public static SqlString GetCleanStringASCII(SqlString formatString, SqlString cleanWith)
        {
            return CleanASCII(formatString, cleanWith);
        }

        /// <summary>
        /// Gets the clean XML ASCII.
        /// </summary>
        /// <param name="formatString">The format string.</param>
        /// <param name="cleanWith">The clean with.</param>
        /// <returns></returns>
        public static SqlXml GetCleanXmlASCII(SqlXml formatString, SqlString cleanWith)
        {
            try
            {
                SqlXml result = null;
                var cleanedResult = CleanASCII(formatString.Value, cleanWith).Value;
                if(!String.IsNullOrEmpty(cleanedResult))
                {
                    var xmldoc = XDocument.Parse(cleanedResult);
                    xmldoc.Declaration = new XDeclaration("1.0", "UTF-8", "");

                    using(var buffer = new MemoryStream())
                    {
                        using(var writer = XmlWriter.Create(buffer, new XmlWriterSettings
                        {
                            CheckCharacters = false
                        }))
                        {
                            xmldoc.WriteTo(writer);
                        }

                        buffer.Position = 0;

                        using(var reader = XmlReader.Create(buffer, new XmlReaderSettings
                        {
                            CheckCharacters = false
                        }))
                        {
                            result = new SqlXml(reader);
                        }
                    }
                }

                return result;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// Cleans the ASCII.
        /// </summary>
        /// <param name="formatString">The format string.</param>
        /// <param name="cleanWith">The clean with.</param>
        /// <returns></returns>
        [SqlFunction(
            IsDeterministic = true,
            IsPrecise = true,
            DataAccess = DataAccessKind.Read,
            SystemDataAccess = SystemDataAccessKind.Read
            )]
        static SqlString CleanASCII(SqlString formatString, SqlString cleanWith)
        {
            return Encoding.ASCII.GetString(
                Encoding.Convert(
                    Encoding.UTF8,
                    Encoding.GetEncoding(
                        Encoding.ASCII.EncodingName,
                        new EncoderReplacementFallback(cleanWith.ToString()),
                        new DecoderExceptionFallback()
                        ),
                    Encoding.UTF8.GetBytes(formatString.Value)
                    )
                );
        }
    }
}