using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GoodBooks.PAY.PAYDAL.DTO.Loyalty;
using GoodBooks.PAY.PAYDAL.Query.Loyalty;
using Newtonsoft.Json;

namespace GoodBooks.PAY.PAYDAL.CustomCode.Loyalty
{
    /// <summary>
    /// DAL implementation for MLOYALTYPROGRAM.
    /// GetLoyaltyProgram performs three separate queries (header, tiers, earn configs)
    /// and stitches them together before serialisation — avoids a complex multi-map JOIN
    /// on a one-to-many-to-many relationship.
    /// </summary>
    public class LoyaltyProgramDAL : ILoyaltyProgramDAL
    {
        private readonly IQueryExecutor _queryExecutor;

        public LoyaltyProgramDAL(IQueryExecutor queryExecutor)
        {
            _queryExecutor = queryExecutor;
        }

        /// <inheritdoc/>
        public async Task<string> GetLoyaltyProgram(int programId, LoginDTO login)
        {
            try
            {
                // 1. Fetch the header row
                LoyaltyProgramDTO dto = await _queryExecutor.QuerySingleAsync<LoyaltyProgramDTO>(
                    login,
                    LoyaltyProgramQB.GET_LOYALTY_PROGRAM,
                    new
                    {
                        LoyaltyProgramId = programId,
                        TenantId         = login.ClientId
                    }).ConfigureAwait(false);

                if (dto == null)
                {
                    return JsonConvert.SerializeObject(null);
                }

                // 2. Fetch child tiers
                IEnumerable<LoyaltyTierDTO> tiers = await _queryExecutor.QueryAsync<LoyaltyTierDTO>(
                    login,
                    LoyaltyTierQB.GET_TIERS_BY_PROGRAM,
                    new { LoyaltyProgramId = programId }).ConfigureAwait(false);

                dto.LoyaltyTierArray = tiers?.ToList() ?? new List<LoyaltyTierDTO>();

                // 3. Fetch child earn configs (active rules only — for display we show all statuses)
                IEnumerable<LoyaltyEarnCfgDTO> earnCfgs = await _queryExecutor.QueryAsync<LoyaltyEarnCfgDTO>(
                    login,
                    LoyaltyEarnCfgQB.GET_EARN_CFGS_BY_PROGRAM,
                    new { LoyaltyProgramId = programId }).ConfigureAwait(false);

                dto.LoyaltyEarnCfgArray = earnCfgs?.ToList() ?? new List<LoyaltyEarnCfgDTO>();

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

        /// <inheritdoc/>
        public async Task<string> GetLoyaltyProgramList(int pageOffset, int pageSize, LoginDTO login)
        {
            try
            {
                var result = await _queryExecutor.QueryPagedAsync<LoyaltyProgramDTO>(
                    login,
                    LoyaltyProgramQB.GET_LOYALTY_PROGRAM_LIST,
                    new
                    {
                        TenantId = login.ClientId,
                        Offset   = pageOffset,
                        PageSize = pageSize
                    }).ConfigureAwait(false);

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

        /// <inheritdoc/>
        public async Task<int> InsertLoyaltyProgram(LoyaltyProgramDTO dto, LoginDTO login, DbTransaction? tx)
        {
            try
            {
                int rows = await _queryExecutor.ExecuteAsync(
                    login,
                    LoyaltyProgramQB.INSERT_LOYALTY_PROGRAM,
                    new
                    {
                        dto.LoyaltyProgramId,
                        dto.ProgramCode,
                        dto.ProgramName,
                        dto.ProgramType,
                        dto.PointsName,
                        dto.PointsExpireDays,
                        dto.MinRedeemPoints,
                        dto.PointsToCurrency,
                        dto.CurrencyToPoints,
                        dto.AllowTiering,
                        dto.Remarks,
                        dto.Version,
                        dto.Status,
                        dto.SortOrder,
                        CreatedById  = login.UserId,
                        ModifiedById = login.UserId,
                        dto.SourceType,
                        TenantId     = login.ClientId
                    },
                    tx).ConfigureAwait(false);

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

        /// <inheritdoc/>
        public async Task<int> UpdateLoyaltyProgram(LoyaltyProgramDTO dto, LoginDTO login, DbTransaction? tx)
        {
            try
            {
                int rows = await _queryExecutor.ExecuteAsync(
                    login,
                    LoyaltyProgramQB.UPDATE_LOYALTY_PROGRAM,
                    new
                    {
                        dto.LoyaltyProgramId,
                        dto.ProgramCode,
                        dto.ProgramName,
                        dto.ProgramType,
                        dto.PointsName,
                        dto.PointsExpireDays,
                        dto.MinRedeemPoints,
                        dto.PointsToCurrency,
                        dto.CurrencyToPoints,
                        dto.AllowTiering,
                        dto.Remarks,
                        dto.Version,
                        dto.Status,
                        dto.SortOrder,
                        ModifiedById = login.UserId,
                        dto.SourceType,
                        TenantId     = login.ClientId
                    },
                    tx).ConfigureAwait(false);

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

        /// <inheritdoc/>
        public async Task<int> DeleteLoyaltyTiersByProgram(int programId, LoginDTO login, DbTransaction? tx)
        {
            try
            {
                int rows = await _queryExecutor.ExecuteAsync(
                    login,
                    LoyaltyProgramQB.DELETE_LOYALTY_TIER_BY_PROGRAM,
                    new { LoyaltyProgramId = programId },
                    tx).ConfigureAwait(false);

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