using PRSA.DAL.DB;
using PRSA.Services.Models;
using Microsoft.Office.Interop.Excel;
using System;

namespace PRSA.Services.FileUploads
{
    public class FileUploadService : IFileUploadService
    {
        #region [ Variables ]

        private PRSAContext _context;

        #endregion [ Variables ]

        #region [ Constructor ]

        public FileUploadService(PRSAContext context)
        {
            _context = context;
        }

        #endregion [ Constructor ]

        #region [ Get ]
        /// <summary>
        /// Get all the tracker data to be displayed
        /// </summary>
        /// <returns></returns>
        public List<TrackerModel> GetTrackerData()
        {
            return _context.tracker.Select(x => new TrackerModel()
            {
                id = x.id,
                item_number = x.item_number,
                item_description = x.item_description,
                pack_size = x.pack_size,
                stocking_type = x.stocking_type,
                stock_cover = x.stock_cover,
                stock_balance = x.stock_balance,
                sales_forecast = x.sales_forecast,
                mtd_sales = x.mtd_sales,
                open_orders = x.open_orders,
                perc_sold_incl = x.perc_sold_incl,
                perc_sold_excl = x.perc_sold_excl
            }).ToList();
        }

        #endregion [ Get ]

        #region [ Store ]

        /// <summary>
        /// This method will read each sheet and upload the data to the database
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public string UploadTrackerSheet(string filePath)
        {
            Application excel = new Application();
            var modelList = new List<tracker>();

            Workbook wb = excel.Workbooks.Open(filePath);
            Worksheet ws = wb.Worksheets[1];

            Microsoft.Office.Interop.Excel.Range range = ws.UsedRange;

            try
            {
                for (int i = 9; i <= range.Rows.Count - 8; i++)
                {
                    //Data type validation
                    //Stock Cover
                    decimal stockCover = 0;
                    if (range.Cells[i, 6].Text.Contains("-") || string.IsNullOrWhiteSpace(range.Cells[i, 6].Text))
                    {
                        stockCover = 0;
                    }
                    else if (range.Cells[i, 6].Text.Contains("(") || range.Cells[i, 6].Text.Contains(")"))
                    {
                        stockCover = Convert.ToDecimal(range.Cells[i, 6].Text.Replace("(", string.Empty).Replace(")", string.Empty)) * -1;
                    }
                    else
                    {
                        stockCover = Convert.ToDecimal(range.Cells[i, 6].Text);
                    }

                    //Stock Balance
                    decimal stockBalance = 0;
                    if (range.Cells[i, 7].Text.Contains("-"))
                    {
                        stockBalance = 0;
                    }
                    else if (range.Cells[i, 7].Text.Contains("(") || range.Cells[i, 7].Text.Contains(")"))
                    {
                        stockBalance = Convert.ToDecimal(range.Cells[i, 7].Text.Replace("(", string.Empty).Replace(")", string.Empty)) * -1;
                    }
                    else
                    {
                        stockBalance = Convert.ToDecimal(range.Cells[i, 7].Text);
                    }

                    //FY-1
                    decimal fy1 = 0;
                    if (range.Cells[i, 8].Text.Contains("-"))
                    {
                        fy1 = 0;
                    }
                    else if (range.Cells[i, 8].Text.Contains("(") || range.Cells[i, 8].Text.Contains(")"))
                    {
                        fy1 = Convert.ToDecimal(range.Cells[i, 8].Text.Replace("(", string.Empty).Replace(")", string.Empty)) * -1;
                    }
                    else
                    {
                        fy1 = Convert.ToDecimal(range.Cells[i, 8].Text);
                    }

                    //RF 1
                    decimal rf1 = 0;
                    if (range.Cells[i, 9].Text.Contains("-"))
                    {
                        rf1 = 0;
                    }
                    else if (range.Cells[i, 9].Text.Contains("(") || range.Cells[i, 9].Text.Contains(")"))
                    {
                        rf1 = Convert.ToDecimal(range.Cells[i, 9].Text.Replace("(", string.Empty).Replace(")", string.Empty)) * -1;
                    }
                    else
                    {
                        rf1 = Convert.ToDecimal(range.Cells[i, 9].Text);
                    }

                    //Sales Forecast
                    decimal salesForecast = 0;
                    if (range.Cells[i, 10].Text.Contains("-"))
                    {
                        salesForecast = 0;
                    }
                    else if (range.Cells[i, 10].Text.Contains("(") || range.Cells[i, 10].Text.Contains(")"))
                    {
                        salesForecast = Convert.ToDecimal(range.Cells[i, 10].Text.Replace("(", string.Empty).Replace(")", string.Empty)) * -1;
                    }
                    else
                    {
                        salesForecast = Convert.ToDecimal(range.Cells[i, 10].Text);
                    }

                    //MTD Sales
                    decimal mtdSales = 0;
                    if (range.Cells[i, 11].Text.Contains("-"))
                    {
                        mtdSales = 0;
                    }
                    else if (range.Cells[i, 11].Text.Contains("(") || range.Cells[i, 11].Text.Contains(")"))
                    {
                        mtdSales = Convert.ToDecimal(range.Cells[i, 11].Text.Replace("(", string.Empty).Replace(")", string.Empty)) * -1;
                    }
                    else
                    {
                        mtdSales = Convert.ToDecimal(range.Cells[i, 11].Text);
                    }

                    //Open Orders
                    decimal openOrders = 0;
                    if (range.Cells[i, 12].Text.Contains("-"))
                    {
                        openOrders = 0;
                    }
                    else if (range.Cells[i, 12].Text.Contains("(") || range.Cells[i, 12].Text.Contains(")"))
                    {
                        openOrders = Convert.ToDecimal(range.Cells[i, 12].Text.Replace("(", string.Empty).Replace(")", string.Empty)) * -1;
                    }
                    else
                    {
                        openOrders = Convert.ToDecimal(range.Cells[i, 12].Text);
                    }

                    //Perc Sold Incl
                    decimal perc_sold_incl = 0;
                    if (range.Cells[i, 13].Text.Contains("-"))
                    {
                        perc_sold_incl = 0;
                    }
                    else if (range.Cells[i, 13].Text.Contains("(") || range.Cells[i, 13].Text.Contains(")"))
                    {
                        perc_sold_incl = Convert.ToDecimal(range.Cells[i, 13].Text.Replace("(", string.Empty).Replace(")", string.Empty)) * -1;
                    }
                    else if (range.Cells[i, 13].Text.Contains("%"))
                    {
                        perc_sold_incl = Convert.ToDecimal(range.Cells[i, 13].Text.Replace("%", string.Empty));
                    }
                    else
                    {
                        perc_sold_incl = Convert.ToDecimal(range.Cells[i, 13].Text);
                    }

                    //Perc Sold Excl
                    decimal perc_sold_excl = 0;
                    if (range.Cells[i, 14].Text.Contains("-"))
                    {
                        perc_sold_excl = 0;
                    }
                    else if (range.Cells[i, 14].Text.Contains("(") || range.Cells[i, 14].Text.Contains(")"))
                    {
                        perc_sold_excl = Convert.ToDecimal(range.Cells[i, 14].Text.Replace("(", string.Empty).Replace(")", string.Empty)) * -1;
                    }
                    else if (range.Cells[i, 14].Text.Contains("%"))
                    {
                        perc_sold_excl = Convert.ToDecimal(range.Cells[i, 14].Text.Replace("%", string.Empty));
                    }
                    else
                    {
                        perc_sold_excl = Convert.ToDecimal(range.Cells[i, 14].Text);
                    }

                    modelList.Add(new tracker
                    {
                        brand_group = range.Cells[i, 1].Text,
                        item_number = Convert.ToInt32(range.Cells[i, 2].Text),
                        item_description = range.Cells[i, 3].Text,
                        pack_size = range.Cells[i, 4].Text,
                        stocking_type = range.Cells[i, 5].Text,
                        stock_cover = stockCover,
                        stock_balance = stockBalance,
                        fy_1 = fy1,
                        rf1 = rf1,
                        sales_forecast = salesForecast,
                        mtd_sales = mtdSales,
                        open_orders = openOrders,
                        perc_sold_incl = perc_sold_incl,
                        perc_sold_excl = perc_sold_excl,
                        created_by = 1,
                        created_at = DateTime.Now,
                        updated_by = 1,
                        updated_at = DateTime.Now
                    });
                }

                _context.tracker.AddRange(modelList);
                _context.SaveChanges();

                wb.Close();
            }
            catch (Exception ex)
            {
                wb.Close();
                return "An error has ocurred while trying to upload your file. Please contact your System Administrator. System Exception: " + ex.Message;
            }

            return "Your file has been uploaded successfully.";
        }

        #endregion [ Store ]
    }
}