using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Xrm;
using System.Data;
using System.Data.OleDb;
using System.Collections.Specialized;
using System.Net;
using System.IO;

namespace DataExport
{
    public class GenerateMDB
    {
        private static Guid ESSENSCIA_GUID;
        private static string NEW_PASSWORD = "infochem";

        private static XrmDataContext XrmContext
        {
            get
            {
                return Program.XrmContext;
            }
        }

        public static void Execute(string outputPath)
        {
            Console.WriteLine("GenerateMDB: Starting");
            Console.WriteLine();

            // Get Essenscia account
            ESSENSCIA_GUID = (from a in XrmContext.accounts
                              where a.name == "Essenscia"
                              select a.accountid).Single();

            CreateUsersTable();
            Console.WriteLine();

            CreateCompaniesTable();
            Console.WriteLine();

            CreateGroupsTable();
            Console.WriteLine();

            CreateUsersGroupsTable();
            Console.WriteLine();

            CreateUserCompaniesTable();
            Console.WriteLine();

            UpdateDisappearedUsers();
            Console.WriteLine();

            SaveFile(outputPath);
            Console.WriteLine();

            Console.WriteLine("GenerateMDB: Complete");
        }

        private static void CreateUsersTable()
        {
            Console.WriteLine("GenerateMDB: Read [Users]");

            using (GenericAccessDataSetTableAdapters.UsersTableAdapter userAdapter = new GenericAccessDataSetTableAdapters.UsersTableAdapter())
            {
                Console.WriteLine("GenerateMDB: Removing records from [Users]");
                userAdapter.Clear();

                // Get the data from the DataTable
                using (GenericAccessDataSet.UsersDataTable users = userAdapter.GetData())
                {
                    // Get all contacts where (extranet = true or eNews = true) AND organization != essenscia
                    // We use "description" to store the "genericaccessmdb" parameter because we want to limit our select
                    Console.WriteLine("GenerateMDB: Retrieve contacts from CRM");
                    var contacts = (
                        from c in XrmContext.contacts
                        where
                            // Extranet / eNews
                            (c.qess_extranet == true || c.qess_enews == true)
                            // Cannot belong to Essenscia company
                            && c.parentcustomerid.Value != ESSENSCIA_GUID
                            // Cannot be inactive / deceased / whatever reason to be inactive
                            && c.statecode == "Active"
                            && c.qess_logonname != null
                        select new
                        {
                            ContactId = c.contactid,
                            FirstName = c.firstname,
                            LastName = c.lastname,
                            IDContact = c.qess_idcontact,
                            Title = c.qess_title_contact.qess_genericaccessmdb,
                            Language = c.qbe_language,
                            JobTitle = c.jobtitle,
                            Telephone = c.telephone1,
                            Fax = c.fax,
                            Email = c.emailaddress1,
                            LogonName = c.qess_logonname,
                            NotifyUser = c.qess_notifyuser,
                            NotificationFormat = c.qess_notificationformat
                        });

                    foreach (var contact in contacts)
                    {
                        // Construct user row
                        GenericAccessDataSet.UsersRow row = users.NewUsersRow();

                        row.UserID = contact.IDContact;
                        row.TitleID = contact.Title;
                        row.LastName = contact.LastName;
                        if (!string.IsNullOrEmpty(contact.FirstName))
                        {
                            row.FirstName = contact.FirstName;
                        }

                        switch (contact.Language.Value)
                        {
                            case 1:
                                row.LanguageID = "NL";
                                break;

                            case 2:
                                row.LanguageID = "FR";
                                break;

                            case 3:
                                row.LanguageID = "UK";
                                break;
                        }

                        row.UserFunction = contact.JobTitle;
                        row.IsManager = false;
                        if (!string.IsNullOrEmpty(contact.Telephone))
                        {
                            row.Tel1 = contact.Telephone;
                        }
                        row.Fax1 = contact.Fax;
                        row.Email = contact.Email;
                        row.IsFullTextSearchAllowed = true;
                        row.AcceptNotification = true;
                        row.UserName = contact.LogonName;

                        // If NotifyUser is not filled, it's a new user - set to true
                        row.NotifyUser = (contact.NotifyUser.HasValue ? contact.NotifyUser.Value : true);

                        // If NotifyUser is true, it's a new user ==> change to false to make sure next time correct update happens
                        // If user is new, then notificationFormat = 2, else null
                        if (row.NotifyUser)
                        {
                            // <-- Start ofXRM Update -->
                            XrmContext.UpdateObject(new contact()
                            {
                                contactid = contact.ContactId,
                                qess_notifyuser = false
                            });
                            XrmContext.SaveChanges();
                            // <-- End of XRM Update -->

                            if (contact.NotificationFormat == true)
                            {
                                row.NotificationFormat = "2";
                            }
                            else
                            {
                                row.NotificationFormat = "1";
                            }
                            row.Password = NEW_PASSWORD;
                        }
                        else
                        {
                            row.SetNotificationFormatNull();
                        }

                        row.AccountDisabled = false;
                        row.CreateLocalUser = false;
                        row.IsNTDomainUser = false;
                        row.IsChangePasswordNextLogon = false;
                        row.IsWhosWho = false;
                        row.IsWhosWhoAllowed = false;
                        row.IsMailUsUser = false;
                        row.IsChatUser = false;
                        row.IsOnlinePayment = false;
                        row.IsUserWallet = false;
                        row.IsUserCompanyWallet = false;
                        row.IsGuest = false;
                        row.IsENewsNotification = false;
                        row.IsENewsAllowed = false;
                        row.IsFileBasketConfirmationMessageHidden = false;
                        row.IsDelegateRestricted = false;
                        row.IsContactOnly = false;
                        // Add the row
                        try
                        {
                            users.Rows.Add(row);
                            Console.WriteLine("GenerateMDB: Added row for user " + row.UserID);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine("GenerateMDB: Error adding row: " + ex.Message);
                        }
                    }

                    // Reinject the data
                    Console.WriteLine("GenerateMDB: Update [Users]");

                    userAdapter.Update(users);
                    Console.WriteLine("GenerateMDB: " + users.Count + " records written to [Users]");
                }
            }
        }

        private static void CreateCompaniesTable()
        {
            Console.WriteLine("GenerateMDB: Read [Companies]");

            using (GenericAccessDataSetTableAdapters.CompaniesTableAdapter companyAdapter = new GenericAccessDataSetTableAdapters.CompaniesTableAdapter())
            {
                Console.WriteLine("GenerateMDB: Removing records from [Companies]");
                companyAdapter.Clear();

                // Get the data from the DataTable
                using (GenericAccessDataSet.CompaniesDataTable companies = companyAdapter.GetData())
                {
                    Console.WriteLine("GenerateMDB: Retrieve contacts from CRM");
                    var contacts = (
                        from c in XrmContext.contacts
                        where
                            // Extranet / eNews
                            (c.qess_extranet == true || c.qess_enews == true)
                            // Cannot belong to Essenscia company
                            && c.parentcustomerid.Value != ESSENSCIA_GUID && c.parentcustomerid != null
                            // Cannot be inactive / deceased / whatever reason to be inactive
                            && c.statecode == "Active"
                            && c.qess_logonname != null
                            // Must be a member
                            && c.qess_contacttype == 2
                        select new
                        {
                            AccountName = c.parentcustomerid.Name,
                            AccountIDContact = c.contact_customer_accounts.qess_idcontact,
                            AccountStatuscode = c.contact_customer_accounts.statuscode,
                            AccountID = c.parentcustomerid.Value,
                            ContactStatuscode = c.statuscode.Value
                        });

                    // Duplicate checking
                    List<Guid> addedCompanies = new List<Guid>();

                    foreach (var c in contacts)
                    {
                        // Must be a member - this cannot be checked in the query somehow
                        if (addedCompanies.Contains(c.AccountID)) continue;

                        GenericAccessDataSet.CompaniesRow row = companies.NewCompaniesRow();
                        row.UserCompanyID = c.AccountIDContact;
                        row.CompanyName = c.AccountName;
                        row.IsUserCompanyWallet = false;

                        // Add the row
                        try
                        {
                            companies.Rows.Add(row);
                            addedCompanies.Add(c.AccountID);
                            Console.WriteLine("GenerateMDB: Added row for company " + row.UserCompanyID);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine("GenerateMDB: Error adding row: " + ex.Message);
                        }
                    }

                    // Reinject the data
                    Console.WriteLine("GenerateMDB: Update [Companies]");

                    companyAdapter.Update(companies);
                    Console.WriteLine("GenerateMDB: " + companies.Count + " records written to [Companies]");
                }
            }
        }

        private static void CreateUsersGroupsTable()
        {
            Console.WriteLine("GenerateMDB: Read [UserGroups]");

            using (GenericAccessDataSetTableAdapters.UsersGroupsTableAdapter userGroupAdapter = new GenericAccessDataSetTableAdapters.UsersGroupsTableAdapter())
            {
                Console.WriteLine("GenerateMDB: Removing records from [UserGroups]");
                userGroupAdapter.Clear();

                // Get the data from the DataTable
                using (GenericAccessDataSet.UsersGroupsDataTable userGroups = userGroupAdapter.GetData())
                {
                    Console.WriteLine("GenerateMDB: Retrieve contacts from CRM (qess_extranet attribute)");

                    var contacts = (
                        from c in XrmContext.contacts
                        where
                            // Extranet / eNews
                            (c.qess_extranet == true || c.qess_enews == true)
                            // Cannot belong to Essenscia company
                            && c.parentcustomerid.Value != ESSENSCIA_GUID
                            // Cannot be inactive / deceased / whatever reason to be inactive
                            && c.statecode == "Active"
                            && c.qess_logonname != null
                        select new
                        {
                            ContactId = c.contactid,
                            IDContact = c.qess_idcontact,
                            Extranet = c.qess_extranet,
                            ENews = c.qess_enews,
                            ContactType = c.qess_contacttype
                        });

                    // Debug variables
                    int DB_contactsExtranet = 0;
                    int DB_contactsWithoutCommissions = 0;
                    int DB_contactsSingleSpecialCommission = 0;
                    int DB_contactsSingleNotSpecialCommission = 0;
                    int DB_contactsMultipleCommissionAtLeastOneSpecial = 0;
                    int DB_contactsMultipleCommissionAllSpecial = 0;

                    foreach (var c in contacts)
                    {
                        if (c.ENews == true)
                        {
                            // Add a record for E-news Reader
                            GenericAccessDataSet.UsersGroupsRow newsRow = userGroups.NewUsersGroupsRow();
                            newsRow.UserID = c.IDContact;
                            newsRow.GroupID = "E-news Read";

                            userGroups.Rows.Add(newsRow);
                        }

                        // Get all associated comissions for this contact
                        var boards = (from b in XrmContext.qbe_boards
                                      where b.qbe_contactid == c.ContactId && b.qbe_commissionid != null
                                      && b.statecode == "Active"
                                      select new
                                      {
                                          BoardID = b.qbe_boardid,
                                          CommissionID = b.qbe_commissionid,
                                          SpecialCommission = b.qbe_commission_qbe_board.qess_specialgroup,
                                          ExtranetCommission = b.qbe_commission_qbe_board.qess_extranet
                                      });

                        // If contact is a member (type 2), create a group ID 1 row
                        if (c.ContactType == 2)
                        {
                            GenericAccessDataSet.UsersGroupsRow oneRow = userGroups.NewUsersGroupsRow();
                            oneRow.UserID = c.IDContact;
                            oneRow.GroupID = "1";
                            try
                            {
                                userGroups.Rows.Add(oneRow);
                                Console.WriteLine("GenerateMDB: Added row for UserGroup " + oneRow.UserID + "/" + oneRow.GroupID);
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine("GenerateMDB: Failed to add row to [UserGroups]: " + ex.Message);
                            }
                        }

                        // Create a record for every commission
                        // If contact is NOT a member (type 2) and at least 1 commission is extranet = true
                        // create row with group ID 1
                        if (c.ContactType != 2 && boards.Count(b => b.ExtranetCommission == true) > 1)
                        {
                            GenericAccessDataSet.UsersGroupsRow oneRow = userGroups.NewUsersGroupsRow();
                            oneRow.UserID = c.IDContact;
                            oneRow.GroupID = "1";
                            try
                            {
                                userGroups.Rows.Add(oneRow);
                                Console.WriteLine("GenerateMDB: Added row for UserGroup " + oneRow.UserID + "/" + oneRow.GroupID);
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine("GenerateMDB: Failed to add row to [UserGroups]: " + ex.Message);
                            }
                        }

                        // No extranet, continue (skip contact)
                        if (c.Extranet != true) continue;

                        if (boards.Count() == 0)
                        {
                            DB_contactsWithoutCommissions = 0;
                        }

                        // If contact only part of ONE commission, check "special" parameter
                        // If it's true, continue (skip contact)
                        if (boards.Count() == 1 && boards.First().SpecialCommission == true)
                        {
                            DB_contactsSingleSpecialCommission++;
                            continue;
                        }

                        if (boards.Count() == 1 && boards.First().SpecialCommission == false)
                        {
                            DB_contactsSingleNotSpecialCommission++;
                        }

                        // There are more than 1 commissions, if these are ALL special, continue (skip contact)
                        if (boards.Count() > 1 && boards.Count() == boards.Count(b => b.SpecialCommission == true))
                        {
                            DB_contactsMultipleCommissionAllSpecial++;
                            continue;
                        }

                        if (boards.Count() > 1 && boards.Count(b => b.SpecialCommission == true) > 0)
                        {
                            DB_contactsMultipleCommissionAtLeastOneSpecial++;
                        }

                        // Create a record with Group ID 1
                        GenericAccessDataSet.UsersGroupsRow groupOneRow = userGroups.NewUsersGroupsRow();
                        groupOneRow.UserID = c.IDContact;
                        groupOneRow.GroupID = "1";
                        try
                        {
                            userGroups.Rows.Add(groupOneRow);
                            Console.WriteLine("GenerateMDB: Added row for UserGroup " + groupOneRow.UserID + "/" + groupOneRow.GroupID);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine("GenerateMDB: Failed to add row to [UserGroups]: " + ex.Message);
                        }

                        foreach (var b in boards)
                        {
                            var commission = (from comm in XrmContext.qbe_commissions
                                              where comm.qbe_commissionid == b.CommissionID.Value &&
                                              (comm.qess_specialgroup == true || comm.qess_extranet == true) &&
                                              comm.statecode == "Active"
                                              select new
                                              {
                                                  IDContact = comm.qess_idcontact,
                                                  Statecode = comm.statecode
                                              }).First();
                            if (commission == null || commission.Statecode == "Active") continue;

                            GenericAccessDataSet.UsersGroupsRow row = userGroups.NewUsersGroupsRow();
                            row.UserID = c.IDContact;
                            row.GroupID = commission.IDContact;

                            try
                            {
                                userGroups.Rows.Add(row);
                                Console.WriteLine("GenerateMDB: Added row for UserGroup " + row.UserID + "/" + row.GroupID);
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine("GenerateMDB: Failed to add row to [UserGroups]: " + ex.Message);
                            }
                        }

                        DB_contactsExtranet++;

                    }

                    // Reinject the data
                    Console.WriteLine("GenerateMDB: Update [UserGroups]");

                    userGroupAdapter.Update(userGroups);
                    Console.WriteLine("GenerateMDB: " + userGroups.Count + " records written to [UserGroups]");
                }
            }
        }

        private static void CreateUserCompaniesTable()
        {
            Console.WriteLine("GenerateMDB: Read [UserCompanies]");

            using (GenericAccessDataSetTableAdapters.UsersCompaniesTableAdapter userCompanyAdapter = new GenericAccessDataSetTableAdapters.UsersCompaniesTableAdapter())
            {
                Console.WriteLine("GenerateMDB: Removing records from [UserCompanies]");
                userCompanyAdapter.Clear();

                // Get the data from the DataTable
                using (GenericAccessDataSet.UsersCompaniesDataTable userCompanies = userCompanyAdapter.GetData())
                {
                    Console.WriteLine("GenerateMDB: Retrieve contacts from CRM");

                    var contacts = (
                        from c in XrmContext.contacts
                        where
                            // Extranet / eNews
                            (c.qess_extranet == true || c.qess_enews == true)
                            // Cannot belong to Essenscia company
                            && c.parentcustomerid.Value != ESSENSCIA_GUID
                            // Cannot be inactive / deceased / whatever reason to be inactive
                            && c.statecode == "Active"
                            && c.qess_logonname != null
                            && c.parentcustomerid != null
                            // Must be a member
                            && c.qess_contacttype == 2
                        select new
                        {
                            IDContact = c.qess_idcontact,
                            AccountIDContact = c.contact_customer_accounts.qess_idcontact
                        });

                    foreach (var c in contacts)
                    {
                        GenericAccessDataSet.UsersCompaniesRow row = userCompanies.NewUsersCompaniesRow();
                        row.UserID = c.IDContact;
                        row.UserCompanyID = c.AccountIDContact;
                        row.IsMainCompany = true;

                        try
                        {
                            userCompanies.Rows.Add(row);
                            Console.WriteLine("GenerateMDB: Added row for UserCompany " + row.UserID + "/" + row.UserCompanyID);
                        }
                        catch (Exception ex)
                        {
                            // Probably the same combination happened
                            Console.WriteLine("GenerateMDB: Failed to add row to [UserCompanies]: " + ex.Message);
                        }
                    }

                    // Reinject the data
                    Console.WriteLine("GenerateMDB: Update [UserCompanies]");

                    userCompanyAdapter.Update(userCompanies);
                    Console.WriteLine("GenerateMDB: " + userCompanies.Count + " records written to [UserCompanies]");
                }
            }
        }

        private static void CreateGroupsTable()
        {
            Console.WriteLine("GenerateMDB: Read [Groups]");

            using (GenericAccessDataSetTableAdapters.GroupsTableAdapter groupAdapter = new GenericAccessDataSetTableAdapters.GroupsTableAdapter())
            {
                Console.WriteLine("GenerateMDB: Removing records from [Groups]");
                groupAdapter.Clear();

                // Get the data from the DataTable
                using (GenericAccessDataSet.GroupsDataTable groups = groupAdapter.GetData())
                {
                    Console.WriteLine("GenerateMDB: Retrieve contacts from CRM");

                    var commissions = (from c in XrmContext.qbe_commissions
                                       where
                                           (c.qess_extranet == true || c.qess_specialgroup == true)
                                           && c.statecode == "Active"
                                       select new
                                       {
                                           IDContact = c.qess_idcontact,
                                           Name = c.qbe_name,
                                           Statecode = c.statecode
                                       });

                    foreach (var c in commissions)
                    {

                        GenericAccessDataSet.GroupsRow row = groups.NewGroupsRow();
                        row.GroupID = c.IDContact;
                        row.GroupCode = c.IDContact;
                        row.GroupName = c.Name;
                        row.SiteID = "1";
                        row.AuthorGroup = false;

                        try
                        {
                            groups.Rows.Add(row);
                            Console.WriteLine("GenerateMDB: Added row for Group " + row.GroupID);
                        }
                        catch (Exception ex)
                        {
                            // Probably the same combination happened
                            Console.WriteLine("GenerateMDB: Failed to add row to [Groups]: " + ex.Message);
                        }
                    }

                    // Reinject the data
                    Console.WriteLine("GenerateMDB: Update [Groups]");

                    groupAdapter.Update(groups);
                    Console.WriteLine("GenerateMDB: " + groups.Count + " records written to [Groups]");
                }
            }
        }

        private static void UpdateDisappearedUsers()
        {
            Console.WriteLine("GenerateMDB: Find disappeared contacts");

            var contacts = (from c in XrmContext.contacts
                            where c.qess_disappeareduser == true
                            select new contact()
                            {
                                contactid = c.contactid,
                                qess_disappeareduser = c.qess_disappeareduser
                            });

            int updates = 0;
            foreach (var c in contacts)
            {
                c.qess_disappeareduser = false;
                XrmContext.UpdateObject(c);
                XrmContext.SaveChanges();
                updates++;
            }

            Console.WriteLine("GenerateMDB: " + updates + " disappeared contacts updated");
        }

        private static void SaveFile(string outputPath)
        {
            File.Copy(Environment.CurrentDirectory + "\\GenericAccess.mdb", outputPath + "\\GenericAccess.mdb");
            Console.WriteLine("GenerateMDB: File saved");
        }

        // Equality comparer used to compare boards by the extranet parameter
        class BoardComparerByExtranet : IEqualityComparer<qbe_board>
        {
            #region IEqualityComparer<qbe_commission> Members

            public bool Equals(qbe_board x, qbe_board y)
            {
                return (x.qbe_commission_qbe_board.qess_extranet.HasValue && y.qbe_commission_qbe_board.qess_extranet.HasValue && x.qbe_commission_qbe_board.qess_extranet.Value == y.qbe_commission_qbe_board.qess_extranet.Value);
            }

            public int GetHashCode(qbe_board obj)
            {
                return obj.qbe_commission_qbe_board.qess_extranet.GetHashCode();
            }

            #endregion
        }
    }
}