using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
using Neo.Afx.ComponentModel;

namespace Neo.Afx.Crm
{
    /// <summary>
    /// Helper function to assist with sql dataset formatting based on a predefined message template
    /// </summary>
    public class MessageFormatter
    {
        #region MessageDetail Class

        private class MessageDetail
        {
            public SqlString Recipients;
            public SqlString Cc;
            public SqlString Bcc;
            public SqlString Subject;
            public SqlString Message;

            public MessageDetail(SqlString recipients, SqlString cc, SqlString bcc, SqlString subject, SqlString message)
            {
                Recipients = recipients;
                Cc = cc;
                Bcc = bcc;
                Subject = subject;
                Message = message;
            }
        }

        #endregion

        #region GetFormattedMessagesFillRow

        /// <summary>
        /// Method used by GetFormattedMessages to fill array
        /// </summary>
        /// <param name="messageDetailObj"></param>
        /// <param name="recipients"></param>
        /// <param name="cc"></param>
        /// <param name="bcc"></param>
        /// <param name="subject"></param>
        /// <param name="message"></param>
        public static void GetFormattedMessagesFillRow(object messageDetailObj, out SqlString recipients, out SqlString cc, out SqlString bcc, out SqlString subject, out SqlString message)
        {
            var obj = (MessageDetail)messageDetailObj;

            recipients = obj.Recipients;
            cc = obj.Cc;
            bcc = obj.Bcc;
            subject = obj.Subject;
            message = obj.Message;
        }

        #endregion

        /// <summary>
        /// Selects data from a provided SQL string and inserts it into the format string.
        /// recipientSql must contain the following fields: recipients, cc, bcc
        /// </summary>
        /// <returns>A table with popolated MessageDetail objects.</returns>
        [SqlFunction(
            IsDeterministic = true,
            IsPrecise = true,
            DataAccess = DataAccessKind.Read,
            SystemDataAccess = SystemDataAccessKind.Read,
            FillRowMethodName = "GetFormattedMessagesFillRow",
            TableDefinition = "Recipients nvarchar(max), Cc nvarchar(max), Bcc nvarchar(max), Subject nvarchar(1000), Message nvarchar(max)")]
        public static IEnumerable GetFormattedMessages(SqlString recipientSql, SqlString dataSql, SqlString subject, SqlString messageTemplateStart, SqlString messageTemplateData, SqlString messageTemplateEnd)
        {
            return GetFormattedMessagesImpl(SqlUtilities.ClrConnectionString,
                recipientSql.Value, dataSql.Value, subject.Value, messageTemplateStart.Value, messageTemplateData.Value, messageTemplateEnd.Value);
        }

        /// <summary>
        /// Gets the formatted messages.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="recipientSql">The recipient SQL.</param>
        /// <param name="dataSql">The data SQL.</param>
        /// <param name="subject">The subject.</param>
        /// <param name="messageTemplateStart">The message template start.</param>
        /// <param name="messageTemplateData">The message template data.</param>
        /// <param name="messageTemplateEnd">The message template end.</param>
        /// <returns></returns>
        public static IEnumerable GetFormattedMessagesImpl(string connectionString, string recipientSql, string dataSql, string subject, string messageTemplateStart, string messageTemplateData, string messageTemplateEnd)
        {
            var results = new ArrayList();
            using(var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                DataTable dt;
                using(var cmd = connection.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = recipientSql;
                    dt = cmd.FetchDataTable();
                }

                using(var cmd = connection.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    foreach(DataRow row in dt.Rows)
                    {
                        var message = new StringBuilder(messageTemplateStart);
                        cmd.CommandText = dataSql.Replace(EntityFormatType.Replacement, row);
                        using(var reader = cmd.ExecuteReader(CommandBehavior.Default))
                        {
                            while(reader.Read())
                            {
                                var s = messageTemplateData.Replace(EntityFormatType.Replacement, reader);
                                message.Append(s);
                            }
                        }
                        message.Append(messageTemplateEnd);

                        results.Add(new MessageDetail(
                            row["recipients"].ToString(),
                            row["cc"].ToString(),
                            row["bcc"].ToString(),
                            subject,
                            message.ToString()));
                    }
                }
            }

            return results;
        }

    }
}