using System.Data.Common;
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>
    /// APPEND-ONLY DAL for TLOYALTYLEDGER.
    /// No UPDATE or DELETE methods. To reverse points, the BLL inserts a
    /// compensating negative-points row. This contract is enforced by the
    /// interface definition and the QB file (which contains no UPDATE constants).
    /// </summary>
    public class LoyaltyLedgerDAL : ILoyaltyLedgerDAL
    {
        private readonly IQueryExecutor _queryExecutor;

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

        // ── Insert ledger entry ───────────────────────────────────────────────

        public async Task<int> InsertLedgerEntry(LoyaltyLedgerDTO dto, LoginDTO login, DbTransaction? tx)
        {
            try
            {
                int rows = await _queryExecutor.ExecuteAsync(
                    login,
                    LoyaltyLedgerQB.INSERT_LEDGER_ENTRY,
                    dto,
                    tx).ConfigureAwait(false);

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

        // ── Live balance (never cached) ───────────────────────────────────────

        public async Task<decimal> GetAvailablePoints(int enrollmentId, LoginDTO login)
        {
            try
            {
                decimal points = await _queryExecutor.ExecuteScalarAsync<decimal>(
                    login,
                    LoyaltyLedgerQB.GET_AVAILABLE_POINTS,
                    new
                    {
                        EnrollmentId = enrollmentId,
                        TenantId     = login.ClientId
                    }).ConfigureAwait(false);

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

        // ── Paged ledger history ──────────────────────────────────────────────

        public async Task<string> GetLoyaltyLedger(int enrollmentId, int pageOffset, int pageSize, LoginDTO login)
        {
            try
            {
                var param = new
                {
                    EnrollmentId = enrollmentId,
                    TenantId     = login.ClientId,
                    Offset       = pageOffset,
                    PageSize     = pageSize
                };

                var result = await _queryExecutor.QueryPagedAsync<LoyaltyLedgerDTO>(
                    login,
                    LoyaltyLedgerQB.GET_LEDGER_PAGED,
                    param).ConfigureAwait(false);

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

        // ── Expiry job — points expiring today ───────────────────────────────

        public async Task<IEnumerable<(int EnrollmentId, decimal PointsExpiring)>> GetExpiringPoints(LoginDTO login)
        {
            try
            {
                var rows = await _queryExecutor.QueryAsync<ExpiringPointsRow>(
                    login,
                    LoyaltyLedgerQB.GET_EXPIRING_POINTS,
                    new { TenantId = login.ClientId }).ConfigureAwait(false);

                return rows.Select(r => (r.EnrollmentId, r.PointsExpiring));
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Internal projection type for the expiry query
        private sealed class ExpiringPointsRow
        {
            public int     EnrollmentId   { get; set; }
            public decimal PointsExpiring { get; set; }
        }
    }
}