using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using PayRollDAL.DTO.EmployeeTransfer;
using PayRollDAL.Query.EmployeeTransfer;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;

namespace PayRollDAL.CustomeCode.EmployeeTransfer
{
    public class EmployeeTransferDAL : IEmployeeTransferDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public EmployeeTransferDAL(IQueryExecutor queryExecutor)
        {
            _QueryExecutor = queryExecutor;
        }

        // ── Get header + all detail rows ─────────────────────────────────

        public async Task<string> GetEmployeeTransfer(int employeeTransferId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { EmployeeTransferId = employeeTransferId };

                EmployeeTransferDTO header =
                    await _QueryExecutor.QuerySingleAsync<EmployeeTransferDTO>(loginDTO, EmployeeTransferQB.GET_EMPLOYEETRANSFER, parameters);

                if (header != null)
                {
                    List<EmployeeTransferDetailDTO> details =
                        (await _QueryExecutor.QueryAsync<EmployeeTransferDetailDTO>(
                            loginDTO, EmployeeTransferQB.GET_EMPLOYEETRANSFERDETAIL_BY_HEADER, parameters)).ToList();

                    header.EmployeeTransferDetailArray = details;

                    ApplyDateStringFormats(header);
                }

                return JsonConvert.SerializeObject(header);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Insert header + detail rows (own transaction) ────────────────

        public async Task<int> SaveEmployeeTransfer(EmployeeTransferDTO dto, LoginDTO loginDTO)
        {
            DbTransaction transaction = await _QueryExecutor.BeginTransactionAsync(loginDTO);

            try
            {
                int rows = await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeTransferQB.SAVE_EMPLOYEETRANSFER, dto, transaction);

                if (dto.EmployeeTransferDetailArray?.Count > 0)
                {
                    foreach (EmployeeTransferDetailDTO detail in dto.EmployeeTransferDetailArray)
                    {
                        detail.EmployeeTransferId = dto.EmployeeTransferId;
                        await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeTransferQB.SAVE_EMPLOYEETRANSFERDETAIL, detail, transaction);
                    }
                }

                await _QueryExecutor.CommitAsync(transaction);
                return rows;
            }
            catch (Exception ex)
            {
                await _QueryExecutor.RollbackAsync(transaction);
                throw new Exception("Error while saving EmployeeTransfer", ex);
            }
        }

        // ── Update header, replace detail rows (own transaction) ─────────

        public async Task<int> UpdateEmployeeTransfer(EmployeeTransferDTO dto, LoginDTO loginDTO)
        {
            DbTransaction transaction = await _QueryExecutor.BeginTransactionAsync(loginDTO);

            try
            {
                int rows = await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeTransferQB.UPDATE_EMPLOYEETRANSFER, dto, transaction);

                // GB4 parity: DELETE FROM TEMPLOYEETRANSFERDETAIL WHERE EMPLOYEETRANSFERID = ... then re-save
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, EmployeeTransferQB.DELETE_EMPLOYEETRANSFERDETAIL_BY_HEADER,
                    new { EmployeeTransferId = dto.EmployeeTransferId }, transaction);

                if (dto.EmployeeTransferDetailArray?.Count > 0)
                {
                    foreach (EmployeeTransferDetailDTO detail in dto.EmployeeTransferDetailArray)
                    {
                        detail.EmployeeTransferId = dto.EmployeeTransferId;
                        await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeTransferQB.SAVE_EMPLOYEETRANSFERDETAIL, detail, transaction);
                    }
                }

                await _QueryExecutor.CommitAsync(transaction);
                return rows;
            }
            catch (Exception ex)
            {
                await _QueryExecutor.RollbackAsync(transaction);
                throw new Exception("Error while updating EmployeeTransfer", ex);
            }
        }

        // ── Delete detail rows then header (own transaction) ─────────────

        public async Task<int> DeleteEmployeeTransfer(int employeeTransferId, LoginDTO loginDTO)
        {
            DbTransaction transaction = await _QueryExecutor.BeginTransactionAsync(loginDTO);

            try
            {
                var parameters = new { EmployeeTransferId = employeeTransferId };

                await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeTransferQB.DELETE_EMPLOYEETRANSFERDETAIL_BY_HEADER, parameters, transaction);

                int rows = await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeTransferQB.DELETE_EMPLOYEETRANSFER, parameters, transaction);

                await _QueryExecutor.CommitAsync(transaction);
                return rows;
            }
            catch (Exception ex)
            {
                await _QueryExecutor.RollbackAsync(transaction);
                throw new Exception("Error while deleting EmployeeTransfer", ex);
            }
        }

        // ── Picklist (Id + TransferNumber) ────────────────────────────────

        public async Task<string> GetSelectListEmployeeTransfer(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, bool isCount = false)
        {
            try
            {
                string json = "";

                if (!isCount)
                {
                    var parameters = new { FirstNumber = firstNumber, MaxResult = maxResult };

                    List<EmployeeTransferPicklistDTO> list =
                        (await _QueryExecutor.QueryAsync<EmployeeTransferPicklistDTO>(
                            loginDTO, EmployeeTransferQB.GET_SELECTLIST_EMPLOYEETRANSFER, parameters)).ToList();

                    json = JsonConvert.SerializeObject(list);
                }
                else
                {
                    int count = await _QueryExecutor.ExecuteScalarAsync<int>(
                        loginDTO, EmployeeTransferQB.GET_SELECTLIST_EMPLOYEETRANSFER_COUNT);
                    json = count.ToString();
                }

                return json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Paged header-only list ─────────────────────────────────────────

        public async Task<string> GetEmployeeTransferListAsync(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, bool isCount = false)
        {
            try
            {
                string json = "";

                if (!isCount)
                {
                    var parameters = new { FirstNumber = firstNumber, MaxResult = maxResult };

                    List<EmployeeTransferDTO> list =
                        (await _QueryExecutor.QueryAsync<EmployeeTransferDTO>(
                            loginDTO, EmployeeTransferQB.GET_EMPLOYEETRANSFER_LIST, parameters)).ToList();

                    foreach (EmployeeTransferDTO item in list)
                        ApplyDateStringFormats(item);

                    json = JsonConvert.SerializeObject(list);
                }
                else
                {
                    int count = await _QueryExecutor.ExecuteScalarAsync<int>(
                        loginDTO, EmployeeTransferQB.GET_EMPLOYEETRANSFER_LIST_COUNT);
                    json = count.ToString();
                }

                return json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Helpers ────────────────────────────────────────────────────────

        private static void ApplyDateStringFormats(EmployeeTransferDTO dto)
        {
            if (dto == null) return;

            if (dto.EmployeeTransferTransferDate != default)
                dto.EmployeeTransferTransferDateStringFormat = dto.EmployeeTransferTransferDate.ToString("dd-MMM-yyyy");

            if (dto.EmployeeTransferEffectiveFrom != default)
                dto.EmployeeTransferEffectiveFromStringFormat = dto.EmployeeTransferEffectiveFrom.ToString("dd-MMM-yyyy");
        }
    }
}