using Microsoft.Azure.CosmosDB.Table;
using Microsoft.Azure.Storage;
using Newtonsoft.Json;
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 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)
                {
                    SaveFlats(condoMappings[0], c);
                }
            });
        }

        static void SaveFlats(CondoMapping mapping, CondoData data)
        {
            var table = tableClient.GetTableReference("mdbFlats");
            table.CreateIfNotExists();

            data.Flats.ForEach(f =>
            {
                var id = Guid.NewGuid();
                var flat = new Flat
                {
                    PartitionKey = mapping.TaloyhtioPMCId.ToString(),
                    Id = id,
                    RowKey = id.ToString(),
                    CondoPMS = mapping.PMSCondoName,
                    FlatTitle = f.Title,
                    PMCTaloyhtioId = mapping.TaloyhtioPMCId,
                    FlatUsers = JsonConvert.SerializeObject(
                        f.Users.ConvertAll(p =>
                        {
                            return new FlatUser
                            {
                                DisplayName = p.Name
                            };
                        })
                    )
                };

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

        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 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}");
                    var flat = condo.Flats.FirstOrDefault(f => f.Title.Equals(flatName));
                    if (flat == null)
                    {
                        condo.Flats.Add(flat = new FlatData { Title = flatName });
                    }

                    cell1 = sheet.GetRow(rowNum++)?.GetCell(1)?.StringCellValue;
                    if (string.IsNullOrEmpty(cell1))
                    {
                        break;
                    }
                    var people = cell1.Split(',');
                    foreach (var item in people)
                    {
                        flat.Users.Add(new UserData { Name = item.Trim() });
                        //Console.WriteLine($"user: {item}");
                    }
                }
            }
            //Console.ReadLine();
        }
    }
}