using System.Data.Common;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GoodBooks.PAY.PAYDAL.DTO.Order;
using GoodBooks.PAY.PAYDAL.Query.Order;
using Newtonsoft.Json;

namespace GoodBooks.PAY.PAYDAL.CustomCode.Order
{
    public class PayOrderDAL : IPayOrderDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

        // ── Get single pay order by internal Id ──────────────────────────

        public async Task<string> GetPayOrder(int payOrderId, LoginDTO loginDTO)
        {
            try
            {
                var param = new
                {
                    PayOrderId = payOrderId,
                    TenantId   = loginDTO.ClientId
                };

                var result = await _QueryExecutor.QuerySingleAsync<PayOrderDTO>(
                    loginDTO, PayOrderQB.GET_PAY_ORDER, param).ConfigureAwait(false);

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

        // ── Get pay order by gateway-assigned order Id ────────────────────
        // Returns typed DTO (not JSON) — used by webhook BLL for internal processing.

        public async Task<PayOrderDTO?> GetPayOrderByGatewayOrderId(
            string gatewayOrderId, LoginDTO loginDTO)
        {
            try
            {
                var param = new
                {
                    GatewayPayOrderId = gatewayOrderId,
                    TenantId          = loginDTO.ClientId
                };

                var result = await _QueryExecutor.QuerySingleAsync<PayOrderDTO>(
                    loginDTO, PayOrderQB.GET_PAY_ORDER_BY_GATEWAY_ORDER_ID, param).ConfigureAwait(false);

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

        // ── Paged list of pay orders for the tenant ───────────────────────

        public async Task<string> GetPayOrderList(int pageOffset, int pageSize, LoginDTO loginDTO)
        {
            try
            {
                var param = new
                {
                    TenantId   = loginDTO.ClientId,
                    PageOffset = pageOffset,
                    PageSize   = pageSize
                };

                var result = await _QueryExecutor.QueryAsync<PayOrderListDTO>(
                    loginDTO, PayOrderQB.GET_PAY_ORDER_LIST, param).ConfigureAwait(false);

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

        // ── Payment history for one source document (Entitlement↔Payment, §9.1) ──

        public async Task<string> GetPayOrdersBySourceDoc(int sourceDocType, int sourceDocId, LoginDTO loginDTO)
        {
            try
            {
                var param = new
                {
                    SourceDocType = sourceDocType,
                    SourceDocId   = sourceDocId,
                    TenantId      = loginDTO.ClientId
                };

                var result = await _QueryExecutor.QueryAsync<PayOrderHistoryDTO>(
                    loginDTO, PayOrderQB.GET_PAY_ORDERS_BY_SOURCEDOC, param).ConfigureAwait(false);

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

        // ── Count active orders for source document ───────────────────────
        // Returns scalar COUNT — used as duplicate-order guard before InsertPayOrder.

        public async Task<int> CountActiveOrdersForSourceDoc(
            int sourceDocType, int sourceDocId, LoginDTO loginDTO)
        {
            try
            {
                var param = new
                {
                    TenantId    = loginDTO.ClientId,
                    SourceDocType = sourceDocType,
                    SourceDocId   = sourceDocId
                };

                int count = await _QueryExecutor.ExecuteScalarAsync<int>(
                    loginDTO, PayOrderQB.COUNT_ACTIVE_ORDERS_FOR_SOURCEDOC, param).ConfigureAwait(false);

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

        // ── Insert new pay order ──────────────────────────────────────────
        // Participates in caller-managed transaction when tx is provided.

        public async Task<int> InsertPayOrder(
            PayOrderDTO dto, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                int rows = await _QueryExecutor.ExecuteAsync(
                    loginDTO, PayOrderQB.INSERT_PAY_ORDER, dto, tx).ConfigureAwait(false);

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

        // ── Targeted status update ────────────────────────────────────────
        // Only updates ORDERSTATUS, GATEWAYPAYORDERID, PAIDAMT, and audit fields.
        // Financial amounts (OrderAmt, NetPayableAmt, etc.) are never touched here.

        public async Task<int> UpdatePayOrderStatus(
            int payOrderId,
            int orderStatus,
            string? gatewayPayOrderId,
            decimal paidAmt,
            LoginDTO loginDTO,
            DbTransaction? tx = null)
        {
            try
            {
                var param = new
                {
                    PayOrderId        = payOrderId,
                    TenantId          = loginDTO.ClientId,
                    OrderStatus       = orderStatus,
                    GatewayPayOrderId = gatewayPayOrderId,
                    PaidAmt           = paidAmt,
                    ModifiedById      = loginDTO.UserId,
                    ModifiedOn        = DateTime.UtcNow
                };

                int rows = await _QueryExecutor.ExecuteAsync(
                    loginDTO, PayOrderQB.UPDATE_PAY_ORDER_STATUS, param, tx).ConfigureAwait(false);

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

        // ── Update VoucherId after Finance receipt posting ─────────────────

        public async Task<int> UpdatePayOrderVoucherId(
            int payOrderId, int voucherId, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                var param = new
                {
                    PayOrderId   = payOrderId,
                    TenantId     = loginDTO.ClientId,
                    VoucherId    = voucherId,
                    ModifiedById = loginDTO.UserId,
                    ModifiedOn   = DateTime.UtcNow
                };

                int rows = await _QueryExecutor.ExecuteAsync(
                    loginDTO, PayOrderQB.UPDATE_PAY_ORDER_VOUCHERID, param, tx).ConfigureAwait(false);

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