using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml.Linq;
using Neo.Afx.ComponentModel;
using SystemData = System.Data;
using System.IO;
using Neo.Afx.Common;

namespace Neo.Afx.Services.Integration
{
    // ReSharper disable LoopCanBeConvertedToQuery
    //
    // DO NOT use LINQ statements as the compiler generated code for these may contain 
    // static fields which are not allowed by SQL 2005 (unless they are read-only)
    //

    /// <summary>
    /// A data store for <see cref="DataSource"/>'s.
    /// </summary>
    public abstract partial class IntegrationStore
    {
        readonly static IEnumerable<XElement> EmptyResult = new XElement[0];
        const char ParameterPrefix = '@';
        const string ParameterMatchPattern = @"@{1,}\w*";  // matches @ or @anyword or @@anyword or @@@anyword, etc.
        const string EntityTag = "e";
        const string EntityCollectionTag = "d";
        const string CachePrefix = "IntegrationStore_";
        const string CacheKeyIsCacheEnabled = CachePrefix + "IsCacheEnabled";
        const string CacheKeyDataSources = CachePrefix + "DataSources";

        const int CacheTimeout = 1440;

        #region Initialization

        /// <summary>
        /// Initializes a new instance of the <see cref="IntegrationStore"/> class.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        protected IntegrationStore(string connectionString)
        {
            ConnectionString = connectionString;
        }

        #endregion

        #region ConnectionString

        /// <summary>
        /// Gets the connection string.
        /// </summary>
        public string ConnectionString
        {
            get;
            private set;
        }

        #endregion

        #region DataSources

        /// <summary>
        /// Gets the dictionary of <see cref="DataSource"/>.
        /// </summary>
        Dictionary<string, DataSource> DataSources
        {
            get;
            set;
        }

        #endregion

        #region IsCacheEnabled

        /// <summary>
        /// Gets or sets a value indicating whether caching is enabled.
        /// </summary>
        public bool IsCacheEnabled
        {
            get;
            private set;
        }

        #endregion

        #region CacheContext

        /// <summary>
        /// Gets or sets the <see cref="ICacheContext"/>.
        /// </summary>
        public ICacheContext CacheContext
        {
            get;
            set;
        }

        #endregion

        #region GetData

        /// <summary>
        /// Gets the data from the named data source.
        /// <remarks>
        /// The <see cref="IntegrationStore.Open"/> must be invoked before calling this method.
        /// </remarks>
        /// </summary>
        /// <param name="name">The name of the data source to be used.</param>
        /// <param name="queryString">The query string to be used.</param>
        /// <param name="requestQuery">The request query - overrides the database value if specified.</param>
        /// <returns>The data from the named data source.</returns>
        public XElement GetData(string name, string queryString, string requestQuery = null)
        {
            EnsureCache();

            var ds = GetDataSource(name);
            if(ds == null)
            {
                return ToEntitiesXml(EmptyResult);
            }

            if(!string.IsNullOrEmpty(requestQuery))
            {
                ds.RequestQuery = requestQuery;
            }

            var cacheKey = CachePrefix + name;
            var isCacheEnabled = CacheContext != null && IsCacheEnabled && ds.IsCacheEnabled;
            if(isCacheEnabled)
            {
                var cachedEntities = CacheContext.Get<XElement>(cacheKey);
                if(cachedEntities != null)
                {
                    return cachedEntities;
                }
            }

            var resultList = EmptyResult;
            var queryStringArgs = queryString.ToNameValueCollection();
            switch(ds.ConnectionTypeEnum)
            {
                case ConnectionTypeEnum.SqlServer:
                case ConnectionTypeEnum.OleDb:
                case ConnectionTypeEnum.Odbc:
                case ConnectionTypeEnum.Oracle:
                    resultList = ExecuteDbRequest(ds, queryStringArgs);
                    break;

                case ConnectionTypeEnum.Web:
                    resultList = ExecuteWebRequest(ds, queryStringArgs);                    
                    break;
            }

            XElement result;
            if(ds.EntitySchemaDoc == null)
            {
                result = resultList.FirstOrDefault();
            }
            else
            {
                var top = queryStringArgs["top"];
                int count;
                Int32.TryParse(top, out count);
                if(count > 0)
                {
                    resultList = resultList.Take(count);
                }
                result = ToEntitiesXml(resultList);
            }

            if(isCacheEnabled)
            {
                CacheContext.Set(cacheKey, result, ds.CacheMinutes);
            }
            return result;
        }

        #endregion

        /////////////////////////////////////////////////////////////////////////////
        // Abstract methods
        /////////////////////////////////////////////////////////////////////////////

        #region Open

        /// <summary>
        /// Establishes a connection to the data store.
        /// <remarks>
        /// Must be invoked before the first call to any read method.
        /// </remarks>
        /// </summary>
        public abstract void Open();

        #endregion

        #region Read

        /// <summary>
        /// Gets a dictionary of all <see cref="DataSource"/> from the data store.
        /// <remarks>
        /// <see cref="Open"/> must be invoked before the first call to this.
        /// </remarks>
        /// </summary>
        /// <returns>A dictionary of all <see cref="DataSource"/> keyed by name.</returns>
        protected abstract Dictionary<string, DataSource> Read();

        /// <summary>
        /// Reads the <see cref="DataSource"/> from the data store.
        /// <remarks>
        /// <see cref="Open"/> must be invoked before the first call to this.
        /// </remarks>
        /// </summary>
        /// <param name="dataSourceName">The name of the data source to be read.</param>
        /// <returns>A <see cref="DataSource"/> if the read was successful; <code>null</code> otherwise.</returns>
        protected abstract DataSource Read(string dataSourceName);

        #endregion

        #region ReadCacheEnabled

        /// <summary>
        /// Reads from the data store whether caching is enabled globally.
        /// <remarks>
        /// <see cref="Open"/> must be invoked before the first call to this.
        /// </remarks>
        /// </summary>
        /// <returns><code>true</code> if caching is enabled; <code>false</code> otherwise.</returns>
        protected abstract bool ReadCacheEnabled();

        #endregion

        #region Close

        /// <summary>
        /// Closes connection to the data store
        /// <remarks>
        /// This should dispose of managed resources e.g. connections that were created when <see cref="Open"/> was invoked.
        /// This method is automatically called when this object is disposed.
        /// </remarks>
        /// </summary>
        public abstract void Close();

        #endregion

        /////////////////////////////////////////////////////////////////////////////
        // Helper methods for caching
        /////////////////////////////////////////////////////////////////////////////

        #region ClearCache

        /// <summary>
        /// Clears the cache.
        /// </summary>
        public void ClearCache()
        {
            if(CacheContext != null)
            {
                CacheContext.Clear();
            }
        }

        /// <summary>
        /// Removes the given data source from the cache.
        /// </summary>
        /// <param name="dataSourceName">The name of the data source to be removed.</param>
        public void ClearCache(string dataSourceName)
        {
            if(CacheContext != null)
            {
                CacheContext.Remove(CachePrefix + dataSourceName);
            }
        }

        #endregion

        #region EnsureCache

        void EnsureCache()
        {
            IsCacheEnabled = false;
            DataSources = null;

            //////if(CacheContext != null)
            //////{
            //////    var result = CacheContext.Get<bool>(CacheKeyIsCacheEnabled);
            //////    if(result == null)
            //////    {
            //////        IsCacheEnabled = ReadCacheEnabled();
            //////        CacheContext.Set(CacheKeyIsCacheEnabled, IsCacheEnabled, CacheTimeout);
            //////    }
            //////    else
            //////    {
            //////        IsCacheEnabled = (bool)result;
            //////    }

            //////    if(IsCacheEnabled)
            //////    {
            //////        result = CacheContext.Get(CacheKeyDataSources);
            //////        if(result == null)
            //////        {
            //////            DataSources = Read();
            //////            CacheContext.Set(CacheKeyDataSources, DataSources, CacheTimeout);
            //////        }
            //////        else
            //////        {
            //////            DataSources = (Dictionary<string, DataSource>)result;
            //////        }
            //////    }
            //////}
        }

        #endregion

        /////////////////////////////////////////////////////////////////////////////
        // Helper methods for creating a data source
        /////////////////////////////////////////////////////////////////////////////

        #region GetDataSource

        DataSource GetDataSource(string dataSourceName)
        {
            return IsCacheEnabled ? DataSources[dataSourceName] : Read(dataSourceName);
        }

        #endregion

        #region CreateDataSource

        /// <summary>
        /// Creates the <see cref="DataSource"/> using the given function to retrieve field values.
        /// </summary>
        /// <param name="getValue">The function used to retrieve field values.</param>
        /// <returns>A <see cref="DataSource"/> or <code>null</code> if <paramref name="getValue"/> is <code>null</code>.</returns>
        protected static DataSource CreateDataSource(Func<DataSourceField, string> getValue)
        {
            if(getValue != null)
            {
                var dataSource = new DataSource
                {
                    DataSourceID = Int32.Parse(getValue(DataSourceField.DataSourceID)),
                    DataSourceName = getValue(DataSourceField.DataSourceName),
                    ConnectionTypeEnum = (ConnectionTypeEnum)Enum.Parse(typeof(ConnectionTypeEnum), getValue(DataSourceField.ConnectionType), true),
                    CommandTypeEnum = (CommandTypeEnum)Enum.Parse(typeof(CommandTypeEnum), getValue(DataSourceField.CommandType), true),
                    ConnectionString = getValue(DataSourceField.ConnectionString),
                    RequestQuery = getValue(DataSourceField.RequestQuery),
                    ResponseXsl = getValue(DataSourceField.ResponseXsl),
                    EntitySchemaDoc = getValue(DataSourceField.EntitySchema).ToXElement(),
                    IsCacheEnabled = getValue(DataSourceField.IsCacheEnabled).ToBoolean(),
                    CacheMinutes = Int32.Parse(getValue(DataSourceField.CacheMinutes)),
                    AuthTypeEnum = (AuthTypeEnum)Enum.Parse(typeof(AuthTypeEnum), getValue(DataSourceField.AuthType), true),
                    AuthDomain = getValue(DataSourceField.AuthDomain),
                    AuthUser = getValue(DataSourceField.AuthUser),
                    AuthPassword = getValue(DataSourceField.AuthPassword)
                };

                AppendRequestHeaders(dataSource, getValue(DataSourceField.RequestHeaders));
                SetProperties(dataSource);
                return dataSource;
            }
            return null;
        }

        #endregion

        #region AppendRequestHeaders

        static void AppendRequestHeaders(DataSource dataSource, string requestHeaders)
        {
            if(!string.IsNullOrEmpty(requestHeaders))
            {
                var headers = XElement.Parse(requestHeaders);
                foreach(var header in headers.Elements())
                {
                    // ReSharper disable PossibleNullReferenceException
                    dataSource.RequestHeadersCollection.Add(header.Attribute("name").Value, header.Attribute("value").Value);
                    // ReSharper restore PossibleNullReferenceException
                }
            }
        }

        #endregion

        #region SetProperties

        static void SetProperties(DataSource dataSource)
        {
            if(dataSource.EntitySchemaDoc == null)
            {
                return;
            }

            dataSource.EntityName = dataSource.EntitySchemaDoc.ToString("name", string.Empty);
            dataSource.KeySeparator = dataSource.EntitySchemaDoc.ToString("keySeparator", ",");
            dataSource.TitleSeparator = dataSource.EntitySchemaDoc.ToString("titleSeparator", " ");

            foreach(var element in dataSource.EntitySchemaDoc.Elements())
            {
                var name = element.ToString("name", string.Empty);
                if(string.IsNullOrEmpty(name))
                {
                    continue;
                }
                var isKey = element.ToBoolean("isKey", false);
                var isTitle = element.ToBoolean("isTitle", false);
                var isAttribute = element.ToBoolean("isAttribute", false);
                var property = new DataSourceProperty
                {
                    Name = name,
                    IsAttribute = isAttribute
                };
                if(isKey)
                {
                    dataSource.KeyProperties.Add(property);
                }
                if(isTitle)
                {
                    dataSource.TitleProperties.Add(property);
                }
                if(!(isKey || isTitle))
                {
                    dataSource.Properties.Add(property);
                }
            }
        }

        #endregion

        /////////////////////////////////////////////////////////////////////////////
        // Helper methods for fetching data from a data source
        /////////////////////////////////////////////////////////////////////////////

        #region ExecuteWebRequest

        static IEnumerable<XElement> ExecuteWebRequest(DataSource ds, NameValueCollection queryString)
        {
            var responseText = string.Empty;
            var webClient = new IntegrationWebClient();
            webClient.Headers.Add(ds.RequestHeadersCollection);

            if(ds.AuthTypeEnum == AuthTypeEnum.NetworkCredential)
            {
                webClient.Credentials = new NetworkCredential(ds.AuthUser, ds.AuthPassword, ds.AuthDomain);
            }

            var request = GetWebParameters(ds, queryString);

            switch(ds.CommandTypeEnum)
            {
                case CommandTypeEnum.Get:

                    var requestUrl = string.Format("{0}?{1}", ds.ConnectionString, request);
                    responseText = webClient.DownloadString(requestUrl);
                    break;
                case CommandTypeEnum.Post:
                    responseText = webClient.UploadString(ds.ConnectionString, request);
                    break;
            }
            return ParseResponse(ds, responseText);
        }

        #endregion

        #region ExecuteDbRequest

        static IEnumerable<XElement> ExecuteDbRequest(DataSource ds, NameValueCollection queryString)
        {
            using(var conn = CreateDbConnection(ds.ConnectionTypeEnum, ds.ConnectionString))
            {
                if(conn == null)
                {
                    return null;
                }

                using(var cmd = conn.CreateCommand())
                {
                    AddDbParameters(ds, queryString, cmd.Parameters);

                    cmd.CommandType = (ds.CommandTypeEnum == CommandTypeEnum.StoredProcedure
                        || ds.CommandTypeEnum == CommandTypeEnum.StoredProcedureExec)
                        ? SystemData.CommandType.StoredProcedure
                        : SystemData.CommandType.Text;

                    cmd.CommandText = (ds.CommandTypeEnum == CommandTypeEnum.StoredProcedure
                        || ds.CommandTypeEnum == CommandTypeEnum.StoredProcedureExec)
                        ? ds.RequestQuery.Substring(0, ds.RequestQuery.IndexOf("@")).Replace("@", "")
                        : ds.RequestQuery;

                    cmd.Connection.Open();

                    var table = new DataTable();
                    if(ds.CommandTypeEnum != CommandTypeEnum.StoredProcedureExec)
                    {
                        using(var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly & CommandBehavior.KeyInfo))
                        {
                            table.Load(reader, LoadOption.OverwriteChanges);
                        }
                    }
                    else
                    {
                        cmd.ExecuteNonQuery();
                    }

                    return ParseResponse(ds, table);
                }
            }
        }

        #endregion

        #region CreateDbConnection

        static IDbConnection CreateDbConnection(ConnectionTypeEnum connectionType, string connectionString)
        {
            switch(connectionType)
            {
                case ConnectionTypeEnum.SqlServer:
                    return new SqlConnection(connectionString);

                case ConnectionTypeEnum.OleDb:
                    return new OleDbConnection(connectionString);

                case ConnectionTypeEnum.Odbc:
                    return new OdbcConnection(connectionString);

                case ConnectionTypeEnum.Oracle:
                    break;
            }
            return null;
        }

        #endregion

        #region AddDbParameters

        static void AddDbParameters(DataSource ds, NameValueCollection queryString, IList parameters)
        {
            var paramMatches = new List<string>();
            var matches = Regex.Matches(ds.RequestQuery, ParameterMatchPattern);

            foreach(Match match in matches)
            {
                var paramName = match.Value;
                if(!paramMatches.Contains(paramName))
                {
                    paramMatches.Add(paramName);
                    var value = GetDbParameterValue(queryString, paramName);
                    switch(ds.ConnectionTypeEnum)
                    {
                        case ConnectionTypeEnum.SqlServer:
                            parameters.Add(new SqlParameter(paramName, value));
                            break;

                        case ConnectionTypeEnum.OleDb:
                            parameters.Add(new OleDbParameter(paramName, value));
                            break;

                        case ConnectionTypeEnum.Odbc:
                            parameters.Add(new OdbcParameter(paramName, value));
                            break;

                        case ConnectionTypeEnum.Oracle:
                            break;
                    }
                }
            }
        }

        #endregion

        #region GetDbParameterValue

        static string GetDbParameterValue(NameValueCollection queryString, string paramName)
        {
            var key = paramName.Remove(0, 1);
            if(key.Length > 0 && key[0] == ParameterPrefix)
            {
                // Do not replace parameters with a @@ prefix just remove the extra @ 
                return key;
            }
            return queryString[key] ?? string.Empty;
        }

        #endregion

        #region GetWebParameters

        static string GetWebParameters(DataSource ds, NameValueCollection queryString)
        {
            return Regex.Replace(ds.RequestQuery, ParameterMatchPattern, m => GetDbParameterValue(queryString, m.Value));
        }

        #endregion

        #region ParseResponse

        static IEnumerable<XElement> ParseResponse(DataSource ds, string responseText)
        {
            if(string.IsNullOrEmpty(responseText))
            {
                return null;
            }

            var response = string.IsNullOrEmpty(ds.ResponseXsl)
                               ? XDocument.Parse(responseText)
                               : responseText.Transform(ds.ResponseXsl);

            var result = new List<XElement>();

            if(ds.EntitySchemaDoc == null)
            {
                // Don't convert, just return the XML
                result.Add(response.Root);
            }
            else if(response.Root != null && !response.Root.IsEmpty)
            {
                var entities = response.Root.HasElements
                                   ? response.Children(ds.EntityName)
                                   : XDocument.Parse(response.Root.Value).Children(ds.EntityName);

                foreach(var entity in entities)
                {
                    var e = entity;
                    var xml = ToEntityXml(ds, p => p.IsAttribute
                                                       ? e.ToString(p.Name, string.Empty)
                                                       : e.Child(p.Name).ToString(string.Empty));
                    result.Add(xml);
                }
            }
            return result;
        }

        static IEnumerable<XElement> ParseResponse(DataSource ds, DataTable dt)
        {
            var result = new List<XElement>();
            foreach(DataRow dr in dt.Rows)
            {
                var row = dr;
                result.Add(ToEntityXml(ds, p => row[p.Name].ToStringSafe()));
            }
            return result;
        }

        #endregion

        #region ToEntitiesXml

        static XElement ToEntitiesXml(IEnumerable<XElement> entities)
        {
            var result = new XElement(EntityCollectionTag);
            if(entities != null)
            {
                foreach(var entity in entities)
                {
                    result.Add(entity);
                }
            }
            return result;
        }

        #endregion

        #region ToEntityXml

        static XElement ToEntityXml(DataSource dataSource, Func<DataSourceProperty, string> getValue)
        {
            var result = new XElement(EntityTag,
                new XElement("key", JoinNames(dataSource.KeyProperties, dataSource.KeySeparator, getValue)),
                new XElement("title", JoinNames(dataSource.TitleProperties, dataSource.TitleSeparator, getValue))
                );

            if(dataSource.Properties.Count > 0)
            {
                var properties = new XElement("properties");
                foreach(var property in dataSource.Properties)
                {
                    properties.Add(new XElement(property.Name.ToLower(), getValue(property)));
                }
                result.Add(properties);
            }
            return result;
        }

        #endregion

        #region JoinNames

        static string JoinNames(IEnumerable<DataSourceProperty> properties, string separator, Func<DataSourceProperty, string> getValue)
        {
            var result = new StringBuilder();
            foreach(var property in properties)
            {
                var value = getValue(property);
                if(!string.IsNullOrEmpty(value))
                {
                    if(result.Length > 0)
                    {
                        result.Append(separator);
                    }
                    result.Append(value);
                }
            }
            return result.ToString();
        }

        #endregion
    }

    // ReSharper restore LoopCanBeConvertedToQuery
}