using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Net;
using System.Xml;
using Microsoft.SharePoint.Client;
using OfficeOpenXml;
using Utilities;
using File = System.IO.File;
using Dapper;
namespace Unicer.ImportExcelRetail
{
    public class SharepointClient
    {
        private string host { get; set; }
        private string user { get; set; }
        private string pass { get; set; }
        private string ItemLimit { get; set; }
        private string listName { get; set; }

        private string importAlways { get; set; }
        private static bool SharepointOnline(string url, string username, string password)
        {
            try
            {


                using (ClientContext context = new ClientContext(url))
                {
                    var networkCred = new NetworkCredential(username, password);
                    context.Credentials = new SharePointOnlineCredentials(username, networkCred.SecurePassword);
                    Web web = context.Web;
                    List docs = web.Lists.GetByTitle("Fotos");
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public int GetLastFileModified(string url, string username, string password, string listName)
        {
            int itemCount = 0;
            using (var context = new ClientContext(url))
            {

                var networkCred = new NetworkCredential(username, password);
                context.Credentials = new SharePointOnlineCredentials(username, networkCred.SecurePassword);
                var site = context.Web;
                context.Load(site, s => s.ListTemplates);
                context.ExecutingWebRequest += (s, e) =>
                {
                    e.WebRequestExecutor.WebRequest.Proxy.Credentials =
                        new NetworkCredential("porto\\60348", "unicer.12345");

                };

                List oList = context.Web.Lists.GetByTitle(listName);
                context.ExecuteQuery();
                CamlQuery query = new CamlQuery();
                ListItemCollection collListItem = oList.GetItems(query);

                context.Load(collListItem);

                context.ExecuteQuery();
                itemCount = collListItem.Count;

            }
            return itemCount;
        }

        public Tuple<DateTime, byte[]> GetLastFile(string url, string username, string password, string listName)
        {
            int itemCount = 0;
            string titleName = string.Empty;
            using (var context = new ClientContext(url))
            {

                var networkCred = new NetworkCredential(username, password);
                context.Credentials = new SharePointOnlineCredentials(username, networkCred.SecurePassword);
                var site = context.Web;
                context.Load(site, s => s.ListTemplates);

                IEnumerable<List> allLists;

                var query = from list in context.Web.Lists
                            select list;

                allLists = context.LoadQuery(query);
                context.ExecuteQuery();
                List oList = allLists.Where(x => x.Title.Contains(listName)).OrderByDescending(x => x.Created)
                    .FirstOrDefault();

                if (oList != null)
                {
                    CamlQuery camlQuery = new CamlQuery();
                    var q = new CamlQuery() { ViewXml = "<View><Query><OrderBy><FieldRef Name='Modified' Ascending='False' /></OrderBy></Query></View>" };
                    ListItemCollection collListItem = oList.GetItems(camlQuery);
                    context.Load(collListItem, items => items.Include(
                        item => item["Modified"],
                        item => item["FileRef"]
                    ));
                    context.ExecuteQuery();

                    ListItem lastFile = collListItem.First();

                    ClientResult<Stream> streamResult = lastFile.File.OpenBinaryStream();
                    context.ExecuteQuery();
                    Tuple<DateTime, byte[]> tuple =
                        new Tuple<DateTime, byte[]>(DateTime.Parse(lastFile["Modified"].ToString()), ReadFully(streamResult.Value));

                    return tuple;
                }
            }
            return null;
        }

        public static byte[] ReadFully(Stream input)
        {
            byte[] buffer = new byte[16 * 1024];
            using (MemoryStream ms = new MemoryStream())
            {
                int read;
                while ((read = input.Read(buffer, 0, buffer.Length)) > 0)
                {
                    ms.Write(buffer, 0, read);
                }
                return ms.ToArray();
            }
        }


        public void Run()
        {
            // get FTPClient config values
            NameValueCollection config = ConfigurationManager.GetSection("ImportSettings") as NameValueCollection;
            host = config["SharepointURL"];
            user = config["SharepointUser"];
            pass = config["SharepointPassword"];
            listName = config["ListName"];
            //check connection to sharepoint online
            bool isSharepointOnline = SharepointOnline(host, user, pass);
            //bool isSqlServerOnline = CheckSqlServer();
            bool isSqlServerOnline = true;
            //Start read from sql
            Log(string.Format("Start importing file to:{0}", host));
            if (isSharepointOnline && isSqlServerOnline) // all good
            {
                Tuple<DateTime, byte[]> file = GetLastFile(host, user, pass, listName);

                if (file != null && file.Item2.Length > 0)
                {
                    Stream stream = new MemoryStream(file.Item2);
                    DataTable dt = new DataTable();
                    Log(string.Format("Found Excel File with size {0} and date  {1}", file.Item2.Length, file.Item1));
                    using (StreamReader sr = new StreamReader(stream))
                    {
                        // read the underlying stream and convert to a DataTable using ToDataTable extension method
                        dt = sr.BaseStream.ToDataTable(HasHeader: true);
                    }

                    Log(string.Format("Start importing {0} rows to sql", dt.Rows.Count));

                    bool error = InsertSqlData(dt, file.Item1);
                    if (!error) Log(string.Format("Sucess importing {0} rows to sql", dt.Rows.Count));

                }


            }
        }

        private bool InsertSqlData(DataTable dt, DateTime date)
        {
            bool error = false;
            try
            {


                using (IDbConnection connection = GetOpenConnection())
                {
                    string sqlQuery = @"INSERT INTO [UniwayRetail].[dbo].[RuturasOSA]
                         ([Cadeia]
                        ,[CodLoja]
                        ,[Loja]
                        ,[Vendedor]
                        ,[Segmento]
                        ,[Artigo]
                        ,[EAN]
                        ,[SKUS]
                        ,[Stock]
                        ,[DiasCobertura]
                        ,[RegData])
                    VALUES
                       (@cadeia,@CodLoja,@Loja,@Vendedor,@Segmento,@Artigo,@EAN,@SKUS,@Stock,@DiasCobertura,@RegData)
                    ";

                     string sqlQueryDelete = string.Format(@"DELETE [UniwayRetail].[dbo].[RuturasOSA]
                        where RegData >='{0}-{1}-{2}'",date.Year,date.Month,date.Day);
                     connection.Execute(sqlQueryDelete);


                    foreach (DataRow dataRow in dt.Rows)
                    {
                        if (dataRow["Cod. Loja"] != null && !string.IsNullOrEmpty(dataRow["Cod. Loja"].ToString()) && dataRow["EAN"] != null && !string.IsNullOrEmpty(dataRow["EAN"].ToString()))
                        connection.Execute(sqlQuery,
                            new
                            {
                                Cadeia = dataRow["Cadeia"],
                                CodLoja = dataRow["Cod. Loja"],
                                Loja = dataRow["Loja"],
                                Vendedor = dataRow["Vendedor"],
                                Segmento = dataRow["Segmento"],
                                Artigo = dataRow["Artigo"],
                                EAN = dataRow["EAN"],
                                SKUS = dataRow["SKUs"],
                                Stock = dataRow["Stock"],
                                DiasCobertura = dataRow["Dias Cobertura"],
                                RegData = date.Date,
                            });
                        else
                        {
                            Log(string.Format("Missing mandatory fields in Line:{0}", string.Join(",", dataRow.ItemArray)));
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                error = true;
                Log(string.Format("Error in DB:{0}", ex.Message));
            }
            return error;
        }


        public static IEnumerable<List<T>> SplitList<T>(List<T> locations, int nSize = 4900)
        {
            for (int i = 0; i < locations.Count; i += nSize)
            {
                yield return locations.GetRange(i, Math.Min(nSize, locations.Count - i));
            }
        }

        private dynamic ReadSQLData(bool onlyImported = true)
        {
            string queryAndImported = string.Empty;
            if (onlyImported)
            {
                queryAndImported = @"AND (f.Imported IS NULL OR f.Imported = 0)";
            }
            string date = DateTime.Now.AddYears(-1).ToString("MM/dd/yyyy");
            IEnumerable<dynamic> results = null;
            const string sql =
                @"SELECT vh.VisitaId,u.Nome,c.Name,vh.DataVisita,f.TipoFoto,f.FotoId from Visitas_Header vh
              inner JOIN Utilizadores u ON u.UserId=vh.UtilizadorId
              inner JOIN Clientes c  ON SUBSTRING(c.CustomerId, PATINDEX('%[^0 ]%', c.CustomerId + ' '), LEN(c.CustomerId)) = vh.ClienteId
              inner join Fotos f ON f.VisitaId = vh.VisitaId
              WHERE vh.DataVisita < '{0}' {1}
              ORDER BY vh.DataVisita asc
            ";

            try
            {
                using (IDbConnection connection = GetOpenConnection())
                {
                    results = connection.Query<dynamic>(string.Format(sql, date, queryAndImported));
                    Log(string.Format("Found {0} lines in DB:", results.Count()));
                }
            }
            catch (Exception ex)
            {
                Log(string.Format("Error in DB:{0}", ex.Message));
            }
            return results;
        }

        private byte[] ReadImageBytes(int idFoto)
        {

            byte[] results = null;
            const string sql = @"
                              SELECT f.Foto from Fotos f
                                WHERE f.FotoId = {0}
                                        ";

            try
            {
                using (IDbConnection connection = GetOpenConnection())
                {
                    results = connection.Query<byte[]>(string.Format(sql, idFoto)).FirstOrDefault();

                }
            }
            catch (Exception ex)
            {
                Log(string.Format("Error in DB:{0}", ex.Message));
            }
            return results;
        }

        public static IDbConnection GetOpenConnection()
        {
            // var connection = new SqlConnection(@"Data Source = UB-SQLDEV-01;Initial Catalog=UniwayRetail;User Id=UniwayRetail;Password=zxcvbnm123$%&/;");
            var connectionString = ConfigurationManager.ConnectionStrings["UniwayRetail"].ConnectionString;
            var connection = new SqlConnection(connectionString);
            //SetDialect(SimpleCRUD.Dialect.SQLServer);
            return connection;
        }


        public void Log(string logtext)
        {
            NameValueCollection config = ConfigurationManager.GetSection("ImportSettings") as NameValueCollection;
            string _logpath = config["Logs"];
            //string logfolder = Utility.RemoveFilenameFromFilepath(_logpath);
            string logfolder = _logpath;
            logfolder = AppDomain.CurrentDomain.BaseDirectory;
            string timeString = DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss");

            using (StreamWriter w = File.AppendText(logfolder + "Logs.txt"))
            {
                w.WriteLine(timeString + " " + logtext);
            }
            Console.WriteLine(logtext);
        }

        private bool CheckSqlServer()
        {
            try
            {
                using (IDbConnection connection = GetOpenConnection())
                {
                    ConnectionState conState = connection.State;

                    if (conState == ConnectionState.Closed || conState == ConnectionState.Broken)
                    {
                        Log(string.Format("Could not connect to DB:{0}", connection.ConnectionString));
                        return false;
                    }
                    else
                    {
                        return true;
                    }

                }
            }
            catch (Exception ex)
            {
                Log(string.Format("Could not connect to DB:{0}", ex.Message));
                return false;
            }
        }


    }

}