using System.Data.Common;
using Dapper;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using QMSDAL.DTO;
using QMSDAL.Query.PcsCard;

namespace QMSDAL.CustomCode.PcsCard
{
    public class PcsCardDAL : IPcsCardDAL
    {
        private static readonly IReadOnlyDictionary<string, string> _sortWhitelist =
            new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
            {
                ["CARDNO"]          = "c.CARDNO",
                ["SERIALNO"]        = "c.SERIALNO",
                ["PLANNEDEND"]      = "c.PLANNEDEND",
                ["OVERALLSTATUS"]   = "c.OVERALLSTATUS",
                ["COMPLETEDSTAGES"] = "c.COMPLETEDSTAGES"
            };

        private readonly IQueryExecutor _QueryExecutor;

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

        public async Task<PcsCardDTO?> GetPcsCardAsync(
            int pcsCardId, LoginDTO login, CancellationToken ct = default)
        {
            try
            {
                var param = new { PcsCardId = pcsCardId, TenantId = login.ClientId };
                return await _QueryExecutor
                    .QuerySingleAsync<PcsCardDTO>(login, PcsCardQB.GET_PCSCARD, param, cancellationToken: ct)
                    .ConfigureAwait(false);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<PcsCardDTO?> GetPcsCardBySerialNoAsync(
            string serialNo, LoginDTO login, CancellationToken ct = default)
        {
            try
            {
                var param = new { SerialNo = serialNo, TenantId = login.ClientId };
                return await _QueryExecutor
                    .QuerySingleAsync<PcsCardDTO>(login, PcsCardQB.GET_PCSCARD_BY_SERIALNO, param, cancellationToken: ct)
                    .ConfigureAwait(false);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<(IEnumerable<PcsCardListDTO> Rows, int TotalCount)> GetPcsCardListAsync(
            int ouId, int periodId, byte? overallStatus, int? customerId, int? itemId,
            string? variantCode, string? search,
            DateTime? plannedEndFrom, DateTime? plannedEndTo,
            string sortBy, string sortDir, int pageOffset, int pageSize,
            LoginDTO login, CancellationToken ct = default)
        {
            try
            {
                var filters = new List<string>();
                var p = new DynamicParameters();
                p.Add("OuId",       ouId);
                p.Add("PeriodId",   periodId);
                p.Add("PageOffset", pageOffset);
                p.Add("PageSize",   pageSize);

                if (overallStatus.HasValue)
                {
                    filters.Add("AND c.OVERALLSTATUS = @OverallStatus");
                    p.Add("OverallStatus", overallStatus.Value);
                }
                if (customerId.HasValue)
                {
                    filters.Add("AND c.CUSTOMERID = @CustomerId");
                    p.Add("CustomerId", customerId.Value);
                }
                if (itemId.HasValue)
                {
                    filters.Add("AND c.ITEMID = @ItemId");
                    p.Add("ItemId", itemId.Value);
                }
                if (!string.IsNullOrWhiteSpace(variantCode))
                {
                    filters.Add("AND s.SKUCODE = @VariantCode");
                    p.Add("VariantCode", variantCode);
                }
                if (!string.IsNullOrWhiteSpace(search))
                {
                    filters.Add("AND (c.SERIALNO LIKE @Search OR c.CARDNO LIKE @Search OR c.CUSTOMERSERIAL LIKE @Search)");
                    p.Add("Search", $"%{search}%");
                }
                if (plannedEndFrom.HasValue)
                {
                    filters.Add("AND c.PLANNEDEND >= @PlannedEndFrom");
                    p.Add("PlannedEndFrom", plannedEndFrom.Value);
                }
                if (plannedEndTo.HasValue)
                {
                    filters.Add("AND c.PLANNEDEND <= @PlannedEndTo");
                    p.Add("PlannedEndTo", plannedEndTo.Value);
                }

                var sortCol = _sortWhitelist.TryGetValue(sortBy ?? "", out var col) ? col : "c.CARDNO";
                var sortDir2 = string.Equals(sortDir, "DESC", StringComparison.OrdinalIgnoreCase) ? "DESC" : "ASC";

                var sql = PcsCardQB.GET_PCSCARD_LIST
                    .Replace("{DYNAMIC_FILTERS}", string.Join(" ", filters))
                    .Replace("{SORT_COL}", sortCol)
                    .Replace("{SORT_DIR}", sortDir2);

                var rows = await _QueryExecutor
                    .QueryAsync<PcsCardListDTO>(login, sql, p, cancellationToken: ct)
                    .ConfigureAwait(false);

                var rowList   = rows?.ToList() ?? new List<PcsCardListDTO>();
                var totalCount = rowList.FirstOrDefault()?.TotalCount ?? 0;
                return (rowList, totalCount);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<Result<string>> SavePcsCardAsync(
            PcsCardDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                dto.TenantId     = login.ClientId;
                dto.CreatedById  = login.UserId;
                dto.CreatedOn    = DateTime.UtcNow;
                dto.ModifiedById = login.UserId;
                dto.ModifiedOn   = DateTime.UtcNow;

                int rows = await _QueryExecutor
                    .ExecuteAsync(login, PcsCardQB.SAVE_PCSCARD, dto, tx, ct)
                    .ConfigureAwait(false);

                return rows > 0
                    ? Result<string>.Success("PCS card saved successfully.")
                    : Result<string>.Failure("Failed to save PCS card.");
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<Result<string>> UpdatePcsCardAsync(
            PcsCardDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                dto.ModifiedById = login.UserId;
                dto.ModifiedOn   = DateTime.UtcNow;

                int rows = await _QueryExecutor
                    .ExecuteAsync(login, PcsCardQB.UPDATE_PCSCARD, dto, tx, ct)
                    .ConfigureAwait(false);

                return rows > 0
                    ? Result<string>.Success("PCS card updated successfully.")
                    : Result<string>.Failure("PCS card not found or version conflict.");
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<Result<string>> DeletePcsCardAsync(
            int pcsCardId, LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                var param = new { PcsCardId = pcsCardId, ModifiedById = login.UserId, TenantId = login.ClientId };
                int rows = await _QueryExecutor
                    .ExecuteAsync(login, PcsCardQB.DELETE_PCSCARD, param, tx, ct)
                    .ConfigureAwait(false);

                return rows > 0
                    ? Result<string>.Success("PCS card deleted successfully.")
                    : Result<string>.Failure("PCS card not found or already deleted.");
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task UpdatePcsCardProgressAsync(
            int pcsCardId, short completedStages, short currentStage,
            byte overallStatus, byte overallResult, DateTime? actualEnd,
            LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                var param = new
                {
                    PcsCardId       = pcsCardId,
                    CompletedStages = completedStages,
                    CurrentStage    = currentStage,
                    OverallStatus   = overallStatus,
                    OverallResult   = overallResult,
                    ActualEnd       = actualEnd,
                    ModifiedById    = login.UserId
                };
                await _QueryExecutor
                    .ExecuteAsync(login, PcsCardQB.UPDATE_PCSCARD_PROGRESS, param, tx, ct)
                    .ConfigureAwait(false);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task UpdatePcsCardActualStartAsync(
            int pcsCardId, LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                var param = new { PcsCardId = pcsCardId, ModifiedById = login.UserId };
                await _QueryExecutor
                    .ExecuteAsync(login, PcsCardQB.UPDATE_PCSCARD_ACTUALSTART, param, tx, ct)
                    .ConfigureAwait(false);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task SetCardStatusAsync(
            int pcsCardId, byte overallStatus, LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                var param = new { PcsCardId = pcsCardId, OverallStatus = overallStatus, ModifiedById = login.UserId };
                await _QueryExecutor
                    .ExecuteAsync(login, PcsCardQB.SET_CARD_STATUS, param, tx, ct)
                    .ConfigureAwait(false);
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}