using Dapper;
using HashidsNet;
using Microsoft.AspNetCore.JsonPatch;
using MisIngredientesVue.Core.Exceptions;
using MisIngredientesVue.Core.Extensions;
using MisIngredientesVue.Core.Models;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Information;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace MisIngredientesVue.Core.Services
{
    public interface ISuppliersService
    {
        Task<FindResults<Supplier>> GetSuppliers(string tenantId, int skip, int take, string sortBy, string sortOrder, string q);
        Task<Supplier> InsertSupplier(string tenantId, int createdBy, Supplier supplier);
        Task<Supplier> GetSupplierById(string tenantId, int id);
        Task<IEnumerable<Supplier>> GetAllSuppliers(string tenantId);
        Task<IEnumerable<Supplier>> SearchVerifiedSuppliers(string tenantId, string q);
        Task<IEnumerable<Supplier>> SearchLocalSuppliers(string tenantId, string q);
        Task<IEnumerable<Supplier>> SearchAllSuppliers(string tenantId, string q);
        Task AddLinkToSupplier(string tenantId, int supplierId);
        Task UpdateSupplier(string tenantId, int modifiedBy, Supplier supplier);

    }

    public class SuppliersService : ISuppliersService
    {
        private readonly IRepository _repository;

        public SuppliersService(IRepository repository)
        {
            _repository = repository;
        }

        public async Task<FindResults<Supplier>> GetSuppliers(string tenantId, int skip, int take, string sortBy, string sortOrder, string q)
        {
            return await _repository.Query(async (db) =>
            {

                if (string.IsNullOrEmpty(sortBy) && string.IsNullOrEmpty(sortOrder))
                    sortOrder = "DESC";

                if (string.IsNullOrEmpty(sortBy))
                    sortBy = "Name";

                sortOrder = sortOrder.ToUpper();

                if (sortOrder != "ASC" && sortOrder != "DESC")
                    sortOrder = "DESC";

                if (!(new[] { "name", "legalname", "contact" }.Contains(sortBy)))
                    sortBy = "name";

                var whereQuery = "";
                var term = "";

                if (!string.IsNullOrEmpty(q))
                {
                    whereQuery = " AND suppliers.name ~* @term";
                    term = q.PrepareQuery();
                }

                var query = $@"SELECT * FROM suppliers
LEFT JOIN client_suppliers ON client_suppliers.supplier_tenantid = suppliers.tenantid
WHERE (tenantId = @tenantId OR client_tenantid = @tenantId) {whereQuery}
ORDER BY {sortBy} {sortOrder}
LIMIT {take}
OFFSET {skip}";

                var countQuery = $@"SELECT COUNT(*) FROM suppliers
LEFT JOIN client_suppliers ON client_suppliers.supplier_tenantid = suppliers.tenantid
WHERE (tenantId = @tenantId OR client_tenantid = @tenantId) {whereQuery}";

                var suppliers = await db.QueryAsync<Supplier>(query, new { tenantId, sortBy, term });
                var count = await db.QueryFirstAsync<int>(countQuery, new { tenantId, sortBy, term });

                return new FindResults<Supplier>()
                {
                    Results = suppliers.ToArray(),
                    Total = count
                };
            });

            //return await _repository.Find<Supplier, int>(tenantId, x => x.Name.Contains(q), skip, take, sortBy, sortOrder);
        }

        public async Task<IEnumerable<Supplier>> GetAllSuppliers(string tenantId)
        {
            return await _repository.Query(async (db) =>
            {
                return await db.QueryAsync<Supplier>($@"SELECT * FROM suppliers
LEFT JOIN client_suppliers ON client_suppliers.supplier_tenantId = suppliers.tenantid
WHERE (client_suppliers.client_tenantId = @tenantId OR tenantid = @tenantId) AND isactive = True", new { tenantId });
            });
        }

        public async Task<Supplier> GetSupplierById(string tenantId, int id)
        {
            return await _repository.Query<Supplier>(async (db) =>
            {
                var query = $@"SELECT public.suppliers.*, public.client_suppliers.comments as clientcomments, 
public.supplieraccounts.*, public.suppliercontacts.* FROM public.suppliers
LEFT JOIN public.client_suppliers ON client_suppliers.supplier_tenantid = suppliers.tenantid
LEFT JOIN public.supplieraccounts ON public.supplieraccounts.supplierid = public.suppliers.id
LEFT JOIN public.suppliercontacts ON public.suppliercontacts.supplierid = public.suppliers.id
WHERE public.suppliers.id = @id AND (public.suppliers.tenantid = @tenantId OR public.client_suppliers.client_tenantid = @tenantId)";

                Supplier supplier = null;

                await db.QueryAsync<Supplier, SupplierAccount, SupplierContact, Supplier>(query, (s, sa, sc) =>
                    {
                        if (supplier == null)
                        {
                            supplier = s;
                            supplier.Accounts = new List<SupplierAccount>();
                            supplier.Contacts = new List<SupplierContact>();
                        }

                        if (sa != null && !supplier.Accounts.Any(x => x.Id == sa.Id))
                            supplier.Accounts.Add(sa);

                        if (sc != null && !supplier.Contacts.Any(x => x.Id == sc.Id))
                            supplier.Contacts.Add(sc);

                        return supplier;
                    },

                    new { id, tenantId });

                supplier.PaymentDaysArray = !string.IsNullOrEmpty(supplier.PaymentDays) 
                    ? supplier.PaymentDays.Split(",").Select(x => ParseNumber(x)).Where(x => x != -1).ToArray()
                    : new int[] { };

                supplier.PaymentCutoffDaysArray = !string.IsNullOrEmpty(supplier.PaymentCutoffDays) 
                    ? supplier.PaymentCutoffDays.Split(",").Select(x => ParseNumber(x)).Where(x => x != -1).ToArray()
                    : new int[] { };


                if (!string.IsNullOrEmpty(supplier.ClientComments))
                    supplier.Comments = supplier.ClientComments;

                return supplier;

            });
        }

        private int ParseNumber(string numberAsString)
        {
            var number = 0;
            return Int32.TryParse(numberAsString, out number) ? number : -1;
        }

        public async Task<Supplier> InsertSupplier(string tenantId, int createdBy, Supplier supplier)
        {
            supplier.IsVerified = false;
            supplier.IsActive = true;

            if(!string.IsNullOrEmpty(supplier.PaymentCutoffDays))
                supplier.PaymentCutoffDays = string.Join(",", supplier.PaymentCutoffDaysArray);

            if (!string.IsNullOrEmpty(supplier.PaymentDays))
                supplier.PaymentDays = string.Join(",", supplier.PaymentDaysArray);

            return await _repository.Query(async (db) =>
            {
                var dynamicParameters = new DynamicParameters();

                var insertClientQuery = _repository.GetInsertQuery<Supplier, int>(tenantId, createdBy, supplier, ref dynamicParameters, "Id");

                var supplierId = db.QueryFirst<int>(insertClientQuery, dynamicParameters);

                var strBuilder = new StringBuilder();

                foreach (var account in supplier.Accounts)
                {
                    account.TenantId = tenantId;
                    account.SupplierId = supplierId;
                    strBuilder.Append(_repository.GetInsertQuery<SupplierAccount, int>(tenantId, createdBy, account, ref dynamicParameters, "id"));
                }

                foreach (var contact in supplier.Contacts)
                {
                    contact.TenantId = tenantId;
                    contact.SupplierId = supplierId;
                    strBuilder.Append(_repository.GetInsertQuery<SupplierContact, int>(tenantId, createdBy, contact, ref dynamicParameters, "id"));
                }

                _repository.PrintQuery(strBuilder.ToString(), dynamicParameters);

                await db.QueryAsync(strBuilder.ToString(), dynamicParameters);

                return supplier;
            });
        }

        public async Task<IEnumerable<Supplier>> SearchAllSuppliers(string tenantId, string q)
        {
            q = q?.PrepareQuery();

            var resultsQuery = $@"
SELECT * FROM suppliers
LEFT JOIN client_suppliers ON client_suppliers.supplier_tenantId = suppliers.tenantid
WHERE name ~* @q AND (client_suppliers.client_tenantId = @tenantId OR tenantid = @tenantId) AND isactive = True
LIMIT 20
";
            
            return (await _repository.Query(async (dbConnection) =>
            {
                return await dbConnection.QueryAsync<Supplier>(resultsQuery, new { q, tenantId });

            })).ToList();
        }

        public async Task<IEnumerable<Supplier>> SearchVerifiedSuppliers(string tenantId, string q)
        {
            q = q?.PrepareQuery();

            var resultsQuery = $@"
SELECT * FROM suppliers
LEFT JOIN client_suppliers ON client_suppliers.supplier_tenantId = suppliers.tenantid
WHERE name ~* @q AND isverified = True AND isactive = True AND supplier_tenantid is NULL
LIMIT 20
";

            return (await _repository.Query(async (dbConnection) =>
            {
                return await dbConnection.QueryAsync<Supplier>(resultsQuery, new { q, tenantId });

            })).ToList();
        }

        public async Task<IEnumerable<Supplier>> SearchLocalSuppliers(string tenantId, string q)
        {
            q = q?.PrepareQuery();

            var resultsQuery = $@"
SELECT * FROM suppliers
WHERE name ~* @q AND isverified = False AND isactive = True AND tenantid = @tenantId
LIMIT 20
";

            return (await _repository.Query(async (dbConnection) =>
            {
                return await dbConnection.QueryAsync<Supplier>(resultsQuery, new { q, tenantId });

            })).ToList();
        }

        public async Task AddLinkToSupplier(string tenantId, int supplierId)
        {
            await _repository.Query(async (dbConnection) =>
            {
                var existingQuery = "SELECT * FROM client_suppliers WHERE client_tenantid = @tenantId AND supplier_tenantid = (SELECT tenantId FROM suppliers WHERE id = @supplierId AND isverified = true)";
                var existingLink = await dbConnection.QueryFirstOrDefaultAsync<Client_Supplier>(existingQuery, new { tenantId, supplierId });

                if (existingLink != null) return null;

                var query = $@"INSERT INTO client_suppliers (client_tenantid, supplier_tenantid) VALUES (@tenantId, (SELECT tenantId FROM suppliers WHERE id = @supplierId AND isverified = true))";
                return await dbConnection.QueryAsync<Supplier>(query, new { tenantId, supplierId });
            });
        }

        public async Task UpdateSupplier(string tenantId, int modifiedBy, Supplier supplier)
        {
            await _repository.Query<string>(async (db) =>
            {
                var supplierId = supplier.Id;

                var isVerified = await db.QueryFirstAsync<bool>(@$"SELECT isverified FROM suppliers 
LEFT JOIN public.client_suppliers ON client_suppliers.supplier_tenantid = suppliers.tenantid
WHERE id = @supplierId AND (tenantid = @tenantId OR client_suppliers.client_tenantid = @tenantId)", new { supplierId, tenantId });

                if(isVerified) //Si está verificado sólo se pueden editar las observaciones (pero en la tabla de client_suppliers)
                {
                    await db.QueryAsync("UPDATE client_suppliers SET Comments = @comments WHERE Client_TenantId = @tenantId AND Supplier_TenantId = (SELECT tenantId FROM suppliers WHERE id = @id)", 
                        new { comments = supplier.Comments, tenantId = tenantId, id = supplier.Id });
                }
                else
                {
                    var dynamicParameters = new DynamicParameters();
                    var strBuilder = new StringBuilder();

                    //Accounts

                    var existingAccounts =
                        (await db.QueryAsync<SupplierAccount>(
                            $"SELECT * FROM {_repository.GetTableName<SupplierAccount>()} WHERE supplierId = @supplierId AND TenantId = @tenantId",
                            new { tenantId, supplier })).ToList();

                    var accountsDictionary = new Dictionary<int, int>();

                    foreach (var account in supplier.Accounts)
                    {
                        var existingAccount = existingAccounts.FirstOrDefault(x => x.Id == account.Id);
                        account.SupplierId = supplierId;
                        account.TenantId = tenantId;

                        if (existingAccount != null)
                        {
                            var existingAccountId = existingAccount.Id;
                            existingAccounts.Remove(existingAccount);
                            strBuilder.AppendLine(_repository.GetUpdateQuery<SupplierAccount, int>(tenantId, modifiedBy, x => x.Id == existingAccountId, account, ref dynamicParameters));
                        }
                        else
                        {
                            var accountDynamicParameters = new DynamicParameters();
                            var insertAccountQuery = _repository.GetInsertQuery<SupplierAccount, int>(tenantId, modifiedBy, account, ref accountDynamicParameters, "Id");
                            var requestAccountId = supplier.Id;
                            var accountId = db.QueryFirst<int>(insertAccountQuery, accountDynamicParameters);
                            account.Id = accountId;
                            accountsDictionary.Add(requestAccountId, accountId);
                        }
                    }

                    //Contactos

                    var contactsDictionary = new Dictionary<int, int>();

                    var existingContacts =
                        (await db.QueryAsync<SupplierContact>(
                            $"SELECT * FROM {_repository.GetTableName<SupplierContact>()} WHERE SupplierId = @supplierId AND TenantId = @tenantId",
                            new { tenantId, supplierId })).ToList();

                    foreach (var contact in supplier.Contacts)
                    {
                        var existingContact = existingContacts.FirstOrDefault(x => x.Id == contact.Id);
                        contact.SupplierId = supplierId;
                        contact.TenantId = tenantId;

                        if (existingContact != null)
                        {
                            var existingContactId = existingContact.Id;
                            existingContacts.Remove(existingContact);
                            strBuilder.AppendLine(_repository.GetUpdateQuery<SupplierContact, int>(tenantId, modifiedBy, x => x.Id == existingContactId, contact, ref dynamicParameters));
                        }
                        else
                        {
                            var contactDynamicParameters = new DynamicParameters();
                            var insertContactQuery = _repository.GetInsertQuery<SupplierContact, int>(tenantId, modifiedBy, contact, ref contactDynamicParameters, "Id");
                            var requestContactId = contact.Id;
                            var contactId = db.QueryFirst<int>(insertContactQuery, contactDynamicParameters);
                            contact.Id = contactId;
                            contactsDictionary.Add(requestContactId, contactId);
                        }
                    }

                    //----------

                    supplier.TenantId = tenantId;
                    strBuilder.AppendLine(_repository.GetUpdateQuery<Supplier, int>(tenantId, modifiedBy, x => x.Id == supplierId, supplier, ref dynamicParameters));

                    foreach (var account in existingAccounts)
                    {
                        strBuilder.AppendLine(_repository.GetDeleteQuery<SupplierAccount, int>(tenantId, account, ref dynamicParameters));
                    }

                    foreach (var contact in existingContacts)
                    {
                        strBuilder.AppendLine(_repository.GetDeleteQuery<SupplierContact, int>(tenantId, contact, ref dynamicParameters));
                    }

                    _repository.PrintQuery(strBuilder.ToString(), dynamicParameters);

                    await db.QueryMultipleAsync(strBuilder.ToString(), dynamicParameters);
                }

                return null;

            });
        }

        
    }
}