using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Threading;
using System.Threading;

using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
using System.IO;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using System.Diagnostics;

namespace PSIP_Letters
{
    class Model
    {
        private static ViewModel _vm;
        public Model(ViewModel vm)
        {
            _vm = vm;
        }

        private string InputPath;
        private string TemplatePath;
        private string OutputPath;

        public void InputSelect()
        {
            string path = FileSelect("Excel Documents (*.xlsx)|*.xlsx|All files (*.*)|*.*");
            if (path != string.Empty) _vm.InputPath = path;
        }

        public void TemplateSelect()
        {
            string path = FileSelect("Word Documents (*.docx)|*.docx|All files (*.*)|*.*");
            if (path != string.Empty) _vm.TemplatePath = path;
        }

        public void OutputSelect()
        {
            string path = PathSelect();
            if (path != string.Empty) _vm.OutputPath = path;
        }

        private string FileSelect(string filter)
        {
            var dlg = new OpenFileDialog();
            dlg.Filter = filter;
            System.Windows.Forms.DialogResult result = dlg.ShowDialog();
            return (result == System.Windows.Forms.DialogResult.OK) ? dlg.FileName : string.Empty;
        }

        private string PathSelect()
        {
            var dlg = new FolderBrowserDialog();
            dlg.ShowNewFolderButton = true;
            System.Windows.Forms.DialogResult result = dlg.ShowDialog();
            return (result == System.Windows.Forms.DialogResult.OK) ? dlg.SelectedPath : string.Empty;
        }

        public void Go()
        {
            InputPath = _vm.InputPath;
            TemplatePath = _vm.TemplatePath;
            OutputPath = _vm.OutputPath;

            _vm.ErrorList = "Initializing...";
            _vm.Progress = 0;

            var t1 = new Task(() =>
                {
                    try
                    {
                        #if !DEBUG
                            Process[] processes = Process.GetProcessesByName("WINWORD");
                            if (processes.Length > 0)
                                throw new Exception("Error! All instances of Microsoft Word must be closed before running.");
                        #endif

                        System.Array values = ReadInput();
                        List<List<object>> valuesSorted = SortInput(values);
                        WordOutput(valuesSorted);

                        _vm.Dispatcher.Invoke(DispatcherPriority.Normal, (Action)(() =>
                        {
                            _vm.ErrorList = "Complete!";
                        }));
                    }
                    catch (Exception err)
                    {
                        _vm.Dispatcher.Invoke(DispatcherPriority.Normal, (Action)(() =>
                        {
                            _vm.ErrorList = "Error! " + err.Message;
                        }));
                    }
                });
            t1.Start();
        }

        private void WordReplace(string findText, string replaceText, Word.Application application)
        {
            Word.Find findObject = application.Selection.Find;
            findObject.ClearFormatting();

            findObject.Text = findText;
            findObject.Replacement.ClearFormatting();
            findObject.Replacement.Text = replaceText;

            object m = Type.Missing;
            object replaceAll = Word.WdReplace.wdReplaceAll;
            findObject.Execute(ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref replaceAll, ref m, ref m, ref m, ref m);
        }

        private void WordOutput(List<List<object>> values)
        {
            object m = Type.Missing;
            OutputPath = (OutputPath.LastIndexOf('\\') == OutputPath.Length - 1) ? OutputPath : OutputPath + "\\";
            OutputPath = OutputPath.Trim();
            if (!Directory.Exists(OutputPath)) Directory.CreateDirectory(OutputPath);

            string Date = DateTime.Now.ToString("MMM dd, yyyy");

            Word.Application application = new Word.Application();
            Word.Document document = application.Documents.Open(TemplatePath);

            object FileFormat = Word.WdSaveFormat.wdFormatXMLDocument;

            List<string> L = new List<string>();
            for (int j = 1; j <= 4; ++j)
            {
                L.Add(document.Tables[1].Cell(2, j).Range.Text.TrimEnd(new char[] { '\r', '\a' }));
            }

            if (document.Tables[1].Rows.Count < 2)
                throw new Exception("Error! Template file is missing a complete table");

            document.Tables[1].Rows.Last.Delete();

            double personTotal = 0;
            double doubleOut;
            for (int i = 0; i < values.Count; ++i)
            {
                string Employee_Name = (values[i][0] == null) ? string.Empty : values[i][0].ToString().Trim();
                string Employee_ID = (values[i][1] == null) ? string.Empty : values[i][1].ToString();
                string Employee_First_Name = (values[i][2] == null) ? string.Empty : values[i][2].ToString();
                string Employee_Last_Name = (values[i][3] == null) ? string.Empty : values[i][3].ToString();
                string Payroll_Location = (values[i][4] == null) ? string.Empty : values[i][4].ToString().Trim();
                string Department = (values[i][5] == null) ? string.Empty : values[i][5].ToString();
                string Sale_and_Lot = (values[i][6] == null) ? string.Empty : values[i][6].ToString();
                string Stock_RL_MSS = (values[i][7] == null) ? string.Empty : values[i][7].ToString();
                string Item_Description = (values[i][8] == null) ? string.Empty : values[i][8].ToString();
                string Sale_Date = (values[i][9] == null) ? string.Empty : DateTime.Parse(values[i][9].ToString()).ToString("MM/dd/yy");
                string Business_Unit = (values[i][10] == null) ? string.Empty : values[i][10].ToString();
                string Charge_Site = (values[i][11] == null) ? string.Empty : values[i][11].ToString();
                string Transaction_Currency_Code = (values[i][12] == null) ? string.Empty : values[i][12].ToString();
                string Transaction_Currency = (values[i][13] == null) ? string.Empty : values[i][13].ToString();
                string Purchase_Price = (values[i][14] == null) ? string.Empty : values[i][14].ToString();
                string Eligible_Percent = (values[i][15] == null) ? string.Empty : values[i][15].ToString();
                string Flat_Fee = (values[i][16] == null) ? string.Empty : values[i][16].ToString();
                string Transaction_Amount = (values[i][17] == null) ? string.Empty : values[i][17].ToString();
                string Payment_Currency_Code = (values[i][18] == null) ? string.Empty : values[i][18].ToString();
                string Payment_Currency = (values[i][19] == null) ? string.Empty : values[i][19].ToString();
                string Payment_Amount = (values[i][20] == null) ? string.Empty : values[i][20].ToString();

                if (!Directory.Exists(OutputPath + Payroll_Location)) Directory.CreateDirectory(OutputPath + Payroll_Location);
                if (File.Exists(OutputPath + Payroll_Location + "\\" + Employee_Name + @".docx"))
                {
                    try
                    {
                        File.Delete(OutputPath + Payroll_Location + "\\" + Employee_Name + @".docx");
                    }
                    catch (Exception err)
                    {
                        throw new Exception("Error! Could not overwrite employee " + Employee_Name);
                    }
                }

                List<string> LNew = new List<string>(L);
                bool flatFee = false;
                if (double.TryParse(Eligible_Percent, out doubleOut))
                {
                    Eligible_Percent = Math.Round(doubleOut * 100, 2).ToString("F2") + "%";
                    if (double.TryParse(Transaction_Amount, out doubleOut)) Transaction_Amount = Math.Round(doubleOut).ToString("#,##");
                    flatFee = false;
                }
                else
                {
                    if (double.TryParse(Flat_Fee, out doubleOut))
                        Transaction_Amount = Math.Round(doubleOut).ToString("#,##");
                    else
                        throw new Exception(Employee_Name + " - Flat fee is not a number");
                    flatFee = true;
                }

                if (double.TryParse(Payment_Amount, out doubleOut))
                    Payment_Amount = Math.Round(doubleOut).ToString("#,##");
                else
                    throw new Exception(Employee_Name + " - Payment amount is not a number");
                personTotal += doubleOut;

                document.Tables[1].Rows.Add();
                int count = document.Tables[1].Columns.Last.Cells.Count;

                for (int j = 1; j <= 4; ++j)
                {
                    if (j == 3)
                    {
                        Regex r = new Regex(@"\[IF_FLAT\]\r*(?<flatYes>.*?)\r*\[ELSE\]\r*(?<flatNo>.*?)\r*\[END_IF\]\r*", RegexOptions.Singleline);
                        string replace = (flatFee) ? "flatYes" : "flatNo";
                        LNew[j - 1] = r.Replace(LNew[j - 1], "${" + replace + "}");
                    }

                    document.Tables[1].Cell(count, j).Range.Text = LNew[j - 1]
                        .Replace("<SALE_DATE>", Sale_Date)
                        .Replace("<ITEM_DESCRIPTION>", Item_Description)
                        .Replace("<PERCENTAGE>", Eligible_Percent)
                        .Replace("<CURRENCY>", Transaction_Currency)
                        .Replace("<PAYMENT>", Transaction_Amount)
                        .Replace("<CONVERTED_CURRENCY>", Payment_Currency)
                        .Replace("<CONVERTED_PAYMENT>", Payment_Amount);

                    document.Tables[1].Cell(count, j).Range.Bold = 0;
                    document.Tables[1].Cell(count, j).Range.Italic = 0;
                }

                if (i == values.Count -1 || Employee_Name != values[i + 1][0].ToString())
                {
                    WordReplace("<DATE>", Date, application);
                    WordReplace("<FULL_NAME>", Employee_Name, application);
                    WordReplace("<LAST_NAME>", Employee_Last_Name, application);
                    WordReplace("<DEPARTMENT>", Department, application);
                    WordReplace("<PAYROLL_LOCATION>", Payroll_Location, application);
                    WordReplace("<FIRST_NAME>", Employee_First_Name, application);
                    WordReplace("<TOTAL_PAYMENT>", Math.Round(personTotal).ToString("#,##"), application);
                    WordReplace("<CONVERTED_CURRENCY>", Payment_Currency, application);

                    personTotal = 0;

                    Word.Range range1 = document.Content;
                    Word.Find find1 = range1.Find;
                    find1.Text = "<NEW_PAGE>";
                    find1.ClearFormatting();
                    find1.Replacement.ClearFormatting();
                    find1.Replacement.Text = string.Empty;

                    range1.Find.Execute(ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m);
                    var pStart = range1.get_Information(Word.WdInformation.wdActiveEndPageNumber);

                    Word.Range range2 = document.Content;
                    Word.Find find2 = range2.Find;
                    find2.Text = "</NEW_PAGE>";
                    find2.ClearFormatting();
                    find2.Replacement.ClearFormatting();
                    find2.Replacement.Text = string.Empty;

                    range2.Find.Execute(ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m);
                    var pEnd = range2.get_Information(Word.WdInformation.wdActiveEndPageNumber);

                    if (pStart != pEnd)
                    {
                        range1.Select();
                        application.Selection.InsertBreak(Word.WdBreakType.wdPageBreak);
                    }

                    object replaceAll = Word.WdReplace.wdReplaceAll;
                    range1.Find.Execute(ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref replaceAll, ref m, ref m, ref m, ref m);
                    range2.Find.Execute(ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref replaceAll, ref m, ref m, ref m, ref m);

                    string fullOutputPath = OutputPath + Payroll_Location + "\\" + Employee_Name + @".docx";
                    document.SaveAs2(fullOutputPath,
                        ref FileFormat,
                        ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m, ref m);

                    _vm.Dispatcher.Invoke(DispatcherPriority.Normal, (Action)(() =>
                        {
                            _vm.ErrorList = Payroll_Location + "-" + Employee_Name;
                            _vm.Progress = (double)Math.Round((double)((decimal)i / (decimal)values.Count) * 100);
                        }));

                    document.Close(false, ref m, ref m);
                    document = application.Documents.Open(TemplatePath);
                    document.Tables[1].Rows.Last.Delete();
                }
            }
            document.Close(false, ref m, ref m);
            application.Quit();
        }

        private System.Array ReadInput()
        {
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(InputPath, 0, false, 5, false, "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
            Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlApp.Worksheets["Report"];

            string name = string.Empty;
            int y = 4;
            do
            {
                name = xlWorkSheet.Cells[y++, 2].FormulaR1C1;
            }
            while (name != string.Empty);
            y -= 2;

            Excel.Range range = xlWorkSheet.get_Range("B4", "V" + y.ToString());
            System.Array values = (System.Array)range.Cells.Value;

            xlWorkbook.Close(false, false, false);
            xlApp.Quit();

            return values;
        }

        private List<List<object>> SortInput(System.Array values)
        {
            List<object> employeeIDs = new List<object>();

            for (int i = values.GetLowerBound(0); i <= values.GetUpperBound(0); ++i)
            {
                employeeIDs.Add(values.GetValue(i, 2));
            }
            employeeIDs = employeeIDs.Distinct().ToList();


            List<List<object>> L = new List<List<object>>();

            for (int i = 0; i < employeeIDs.Count; ++i)
            {
                for (int j = values.GetLowerBound(0); j <= values.GetUpperBound(0); ++j)
                {
                    if (object.Equals(values.GetValue(j, 2), employeeIDs[i]))
                    {
                        List<object> l = new List<object>();

                        for (int x = values.GetLowerBound(1); x <= values.GetUpperBound(1); ++x)
                        {
                            l.Add(values.GetValue(j, x));
                        }
                        L.Add(l);
                    }
                }
            }
            return L;
        }
    }
}