using Microsoft.Azure.CosmosDB.Table;
using Microsoft.Azure.Storage;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text.RegularExpressions;
using taloyhtio.idp.parser.common.model;
using tampuuriXls.model;

namespace tampuuriXls
{
    class Program
    {
        private static readonly Regex datePeriod = new Regex("^\\d{1,2}\\.\\d{1,2}\\.\\d{4}\\s-\\s\\d{1,2}\\.\\d{1,2}\\.\\d{4}\\s?$", RegexOptions.Compiled);

        private static CloudTableClient tableClient;
        private static List<CondoData> condos = new List<CondoData>();
        private static CondoData condo;
        private static FlatData flat;
        private static List<UserData> users;

        private static IWorkbook workbook;
        private static ISheet sheet;

        static void Main(string[] args)
        {
            var filename = args[0];
            workbook = WorkbookFactory.Create(filename);
            sheet = workbook.GetSheetAt(0);
            CollectData();
            InitClient();
            condos.ForEach(c =>
            {
                var condoMappings = GetCondo(c.Name);
                if (condoMappings.Count > 0)
                {
                    var flatsAzure = GetFlats(condoMappings[0].TaloyhtioPMCId, c.Name).ToDictionary(x => x.FlatTitle, x => x.Id);
                    SaveFees(condoMappings[0], c, flatsAzure);
                }
            });

        }

        private static void InitClient()
        {
            var storageAccount = CloudStorageAccount.Parse(ConfigurationManager.ConnectionStrings[Constants.KEY_AZURE_STORAGE].ConnectionString);
            tableClient = storageAccount.CreateCloudTableClient(
                new TableConnectionPolicy()
                {
                    UseDirectMode = false
                });
        }

        private static List<CondoMapping> GetCondo(string condoName)
        {
            var table = tableClient.GetTableReference("mdbCondoMappings");
            table.CreateIfNotExists();

            var allCondos = table.CreateQuery<CondoMapping>().ToList();

            if (condoName != null)
            {
                return allCondos.Where(x => condoName.Contains(x.PMSCondoName)).ToList();
            }
            return allCondos;
        }

        private static List<Flat> GetFlats(Guid taloyhtioPMCId, string pmsCondoName)
        {
            if (Guid.Empty.Equals(taloyhtioPMCId) || string.IsNullOrEmpty(pmsCondoName))
            {
                return null;
            }
            var table = tableClient.GetTableReference("mdbFlats");
            table.CreateIfNotExists();

            var query = table.CreateQuery<Flat>()
                .Where(d => d.PMCTaloyhtioId == taloyhtioPMCId && d.CondoPMS == pmsCondoName)
                .ToArray()
                .OrderBy(o => o.FlatTitle);

            return query.ToList();
        }

        private static void SaveFees(CondoMapping mapping, CondoData data, Dictionary<string, Guid> azureFlats)
        {
            var table = tableClient.GetTableReference("mdbMaintenanceFees");
            table.CreateIfNotExists();

            data.Flats.Where(flat => azureFlats.ContainsKey(flat.Title)).ToList().ForEach(flat =>
            {
                flat.Users.ForEach(user =>
                {
                    user.Fees.ForEach(feeRec =>
                    {
                        var fee = new MaintenanceFee(Guid.NewGuid())
                        {
                            Payer = user.Name,
                            FlatTitle = flat.Title,
                            FlatId = azureFlats[flat.Title],
                            PaymentType = feeRec.Name,
                            PMSCondoName = mapping.PMSCondoName,
                            PMCTaloyhtioId = mapping.TaloyhtioPMCId,
                            PeriodOfTime = "202010",
                            Fee = Convert.ToDouble(feeRec.Amount),

                            PartitionKey = user.Name,
                        };

                        var result = table.Execute(TableOperation.InsertOrReplace(fee));
                        Console.WriteLine(result.HttpStatusCode);
                    });
                });
            });
        }


        private static void CollectData()
        {
            int rowNum = 0;
            while (rowNum < sheet.LastRowNum)
            {
                var cell0 = sheet.GetRow(rowNum)?.GetCell(0)?.StringCellValue;
                if (datePeriod.IsMatch(cell0))
                {
                    condos.Add(condo = new CondoData { Name = sheet.GetRow(++rowNum).GetCell(0).StringCellValue });
                    rowNum++;
                    continue;
                }
                var cell1 = sheet.GetRow(rowNum++)?.GetCell(1)?.StringCellValue;
                if (!string.IsNullOrEmpty(cell1) && condo != null && cell1.StartsWith(condo.Name))
                {
                    var flatName = cell1.Replace(condo.Name, "").Trim();
                    Console.WriteLine($"flat: {flatName}");
                    condo.Flats.Add(flat = new FlatData { Title = flatName });

                    cell1 = sheet.GetRow(rowNum++)?.GetCell(1)?.StringCellValue;
                    if (string.IsNullOrEmpty(cell1))
                    {
                        break;
                    }
                    var people = cell1.Split(',');
                    users = new List<UserData>();
                    foreach (var item in people)
                    {
                        var user = new UserData { Name = item.Trim() };
                        users.Add(user);
                        flat.Users.Add(user);

                        //Console.WriteLine($"user: {item}");
                    }
                    //flat.Users.Add(user = new UserData { Name = cell1 });

                    while (true)
                    {
                        var cell2 = sheet.GetRow(rowNum)?.GetCell(2).StringCellValue;
                        if (cell2.Equals("Yhteensä:"))
                        {
                            users.ForEach(u =>
                            {
                                u.Fees.Add(new FeeData { Name = "Akkusaldo", Amount = sheet.GetRow(rowNum).GetCell(3).NumericCellValue.ToString() });
                                u.Fees.Add(new FeeData { Name = "Saldo", Amount = sheet.GetRow(rowNum).GetCell(8).NumericCellValue.ToString() });
                            });
                            rowNum++;
                            break;
                        }
                        users.ForEach(u =>
                        {
                            u.Fees.Add(new FeeData { Name = cell2, Amount = sheet.GetRow(rowNum).GetCell(4).NumericCellValue.ToString() });
                        });
                        rowNum++;
                    }
                }
            }
            //Console.ReadLine();
        }
    }
}