using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Reflection;
using System.Xml;
using AddressValidation.DataLayer.DataModel;
using AddressValidation.DataLayer.Entities;

namespace AddressValidation.DataLayer
{
    public static class ContactDbManager
    {
        public static String ConnectionString { get; set; } = Properties.Settings.Default.ContactDataDbConnectionString;
        //public static String ConnectionString => Properties.Settings.Default.ContactDataDbConnectionString;

        public static void CreateDataBase()
        {

        }
        
        public static void InitMasterTable()
        {
            ClearTable(ConnectionString);
            InsertSampleData();
        }

        internal static void ExecuteCommand(String connectionString, String sql)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

        internal static void ExecuteCommand(String sql)
        {
            ExecuteCommand(ConnectionString, sql);
        }

        internal static void ClearTable(String connectionString)
        {
            String sql = @"TRUNCATE TABLE [dbo].[Contact]";

            ExecuteCommand(sql);
        }

        internal static void InsertSampleData()
        {
            Assembly executingAssembly = Assembly.GetExecutingAssembly();

            using (Stream stream = executingAssembly.GetManifestResourceStream("AddressValidation.DataLayer.DataSources.ContactList"))
            using (ZipArchive archive = new ZipArchive(stream, ZipArchiveMode.Read))
            {
                ZipArchiveEntry entry = archive.Entries.FirstOrDefault();

                if (entry == null)
                {
                    return;
                }

                Stream dataStream = entry.Open();
                XmlDocument xmlDoc = new XmlDocument();

                xmlDoc.Load(dataStream);

                String xPath = "NewDataSet/ContactList";

                var nodes = xmlDoc.SelectNodes(xPath);

                List<ContactInfo> contactInfos = new List<ContactInfo>(nodes.Count);

                foreach (XmlNode node in nodes)
                {
                    ContactInfo contact = new ContactInfo
                    {
                        FirstName = node["FirstName"]?.FirstChild?.Value,
                        LastName = node["LastName"]?.FirstChild?.Value,
                        Address1 = node["Address1"]?.FirstChild?.Value,
                        Address2 = node["Address2"]?.FirstChild?.Value,
                        City = node["City"]?.FirstChild?.Value,
                        State = node["State"]?.FirstChild?.Value,
                        Zip = node["Zip"]?.FirstChild?.Value,
                        Country = node["Country"]?.FirstChild?.Value,
                        Phone = node["Phone"]?.FirstChild?.Value,
                        Email = node["Email"]?.FirstChild?.Value,
                        Custom1 = String.Empty,
                        Custom2 = String.Empty,
                        IsDuplicate = false,
                        IsVerified = "V".Equals(node["Status"]?.FirstChild?.Value, StringComparison.OrdinalIgnoreCase)
                    };

                    contactInfos.Add(contact);
                }

                AddContacts(contactInfos);
            }
        }

        public static void AddContact(ContactInfo contactInfo)
        {
            using (AddressDbModelDataContext dataContext = new AddressDbModelDataContext(ConnectionString))
            {
                Contact contact = ContactInfo2Contact(contactInfo);
                dataContext.Contacts.InsertOnSubmit(contact);
                dataContext.SubmitChanges();
            }
        }

        public static void AddContacts(IEnumerable<ContactInfo> contactInfos)
        {
            using (AddressDbModelDataContext dataContext = new AddressDbModelDataContext(ConnectionString))
            {
                foreach (var contactInfo in contactInfos)
                {
                    Contact contact = ContactInfo2Contact(contactInfo);
                    dataContext.Contacts.InsertOnSubmit(contact);
                }          
            
                dataContext.SubmitChanges();            
            }
        }

        public static List<ContactInfo> GetContacts()
        {
            return GetContacts(null, null, null);
        }

        public static List<ContactInfo> GetContacts(Boolean isDuplicate)
        {
            return GetContacts(isDuplicate, null, null);
        }

        public static List<ContactInfo> GetContacts(Int32 startIndex, Int32 count)
        {
            return GetContacts(null, startIndex, count);
        }

        public static List<ContactInfo> GetContacts(Int32 startIndex, Int32 count, Boolean isDuplicate)
        {
            return GetContacts(isDuplicate, startIndex, count);
        }

        private static List<ContactInfo> GetContacts(Boolean? isDuplicate, Int32? startIndex = null, Int32? count = null)
        {
            List<ContactInfo> contactInfos = new List<ContactInfo>();

            using (AddressDbModelDataContext dataContext = new AddressDbModelDataContext(ConnectionString))
            {
                IQueryable<Contact> query;

                if (startIndex.HasValue && count.HasValue)
                {
                    query = isDuplicate.HasValue
                        ? dataContext.Contacts.Where(c => c.IsDuplicate == isDuplicate.Value).Skip(startIndex.Value).Take(count.Value)
                        : dataContext.Contacts.Skip(startIndex.Value).Take(count.Value);
                }
                else
                {
                    query = isDuplicate.HasValue
                        ? dataContext.Contacts.Where(c => c.IsDuplicate == isDuplicate.Value)
                        : dataContext.Contacts;
                }
                
                foreach (var contact in query)
                {
                    ContactInfo contactInfo = Contact2ContactInfo(contact);
                    contactInfos.Add(contactInfo);
                }
            }

            return contactInfos;
        }

        private static Contact ContactInfo2Contact(ContactInfo contactInfo)
        {
            Contact contact = new Contact
            {
                FirstName = contactInfo.FirstName,
                LastName = contactInfo.LastName,
                Address1 = contactInfo.Address1,
                Address2 = contactInfo.Address2,
                City = contactInfo.City,
                State = contactInfo.State,
                Zip = contactInfo.Zip,
                Country = contactInfo.Country,
                Phone = contactInfo.Phone,
                Email = contactInfo.Email,
                Custom1 = contactInfo.Custom1,
                Custom2 = contactInfo.Custom2,
                IsDuplicate = contactInfo.IsDuplicate,
                IsVerified = contactInfo.IsVerified
            };

            return contact;
        }

        private static ContactInfo Contact2ContactInfo(Contact contact)
        {
            ContactInfo contactInfo = new ContactInfo
            {
                FirstName = contact.FirstName,
                LastName = contact.LastName,
                Address1 = contact.Address1,
                Address2 = contact.Address2,
                City = contact.City,
                State = contact.State,
                Zip = contact.Zip,
                Country = contact.Country,
                Phone = contact.Phone,
                Email = contact.Email,
                Custom1 = contact.Custom1,
                Custom2 = contact.Custom2,
                IsDuplicate = contact.IsDuplicate,
                IsVerified = contact.IsVerified
            };

            return contactInfo;
        }
    }
}
