using FrameworkDAL.DTO.ReportHeader;
using FrameworkDAL.Query.ReportHeader;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using Newtonsoft.Json;

namespace FrameworkDAL.CustomCode.ReportHeader
{
    public class ReportHeaderDAL : IReportHeaderDAL
    {
        private readonly IQueryExecutor _qe;

        public ReportHeaderDAL(IQueryExecutor queryExecutor)
        {
            _qe = queryExecutor;
        }

        public async Task<string> GetReportHeader(int reportHeaderId, LoginDTO login, CancellationToken ct)
        {
            try
            {
                var header = await _qe.QuerySingleAsync<ReportHeaderDTO>(
                    login, ReportHeaderQB.GET_REPORTHEADER,
                    new { ReportHeaderId = reportHeaderId, ClientId = login.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);

                if (header != null)
                {
                    var details = await _qe.QueryAsync<ReportHeaderDetailDTO>(
                        login, ReportHeaderQB.GET_REPORTHEADER_DETAIL,
                        new { ReportHeaderId = reportHeaderId },
                        cancellationToken: ct).ConfigureAwait(false);

                    header.ReportHeaderDetailArray = details.ToList();
                }

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

        public async Task<string> GetReportHeaderList(LoginDTO login, CancellationToken ct)
        {
            try
            {
                var result = await _qe.QueryAsync<ReportHeaderDTO>(
                    login, ReportHeaderQB.GET_REPORTHEADER_LIST,
                    new { ClientId = login.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetReportHeaderPaged(int offset, int pageSize, LoginDTO login, CancellationToken ct)
        {
            try
            {
                var result = await _qe.QueryAsync<ReportHeaderDTO>(
                    login, ReportHeaderQB.GET_REPORTHEADER_PAGED,
                    new { ClientId = login.ClientId, Offset = offset, PageSize = pageSize },
                    cancellationToken: ct).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListReportHeader(LoginDTO login, CancellationToken ct)
        {
            try
            {
                var result = await _qe.QueryAsync<ReportHeaderPicklistDTO>(
                    login, ReportHeaderQB.GET_SELECTLIST,
                    new { ClientId = login.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveReportHeader(ReportHeaderDTO dto, LoginDTO login, CancellationToken ct, bool isNew)
        {
            var tx = await _qe.BeginTransactionAsync(login).ConfigureAwait(false);
            try
            {
                // 1. INSERT or UPDATE the header row
                string headerSql = isNew ? ReportHeaderQB.INSERT_REPORTHEADER : ReportHeaderQB.UPDATE_REPORTHEADER;
                await _qe.ExecuteAsync(login, headerSql, dto, tx).ConfigureAwait(false);

                // 2. Delete all existing detail rows, then re-insert
                await _qe.ExecuteAsync(login, ReportHeaderQB.DELETE_DETAIL,
                    new { ReportHeaderId = dto.ReportHeaderId }, tx).ConfigureAwait(false);

                foreach (var detail in dto.ReportHeaderDetailArray)
                {
                    detail.ReportHeaderId = dto.ReportHeaderId;
                    await _qe.ExecuteAsync(login, ReportHeaderQB.INSERT_DETAIL, detail, tx).ConfigureAwait(false);
                }

                await _qe.CommitAsync(tx).ConfigureAwait(false);
                return dto.ReportHeaderId;
            }
            catch (Exception)
            {
                await _qe.RollbackAsync(tx).ConfigureAwait(false);
                throw;
            }
        }

        public async Task DeleteReportHeader(int reportHeaderId, LoginDTO login, CancellationToken ct)
        {
            try
            {
                await _qe.ExecuteAsync(login, ReportHeaderQB.SOFT_DELETE_REPORTHEADER,
                    new
                    {
                        ReportHeaderId = reportHeaderId,
                        ClientId       = login.ClientId,
                        ModifiedById   = login.UserId,
                        ModifiedOn     = DateTime.UtcNow
                    },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}