using System.Data.Common;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using MMDAL.DTO.SupplyGroup;
using MMDAL.Query.SupplyGroup;
using Newtonsoft.Json;

namespace MMDAL.CustomCode.SupplyGroup
{
    public class SupplyGroupDAL : ISupplyGroupDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

        // ── Get single supply group — header + detail rows ────────────────

        public async Task<SupplyGroupDTO> GetSupplyGroup(int supplyGroupId, LoginDTO loginDTO)
        {
            try
            {
                var param = new { supplygroupid = supplyGroupId };

                var header = await _QueryExecutor.QuerySingleAsync<SupplyGroupDTO>(
                    loginDTO, SupplyGroupQB.GET_SUPPLYGROUP, param).ConfigureAwait(false);

                if (header != null)
                {
                    var details = await _QueryExecutor.QueryAsync<SupplyGroupDetailDTO>(
                        loginDTO, SupplyGroupQB.GET_SUPPLYGROUPDETAIL, param).ConfigureAwait(false);

                    header.SupplyGroupDetailArray = details?.ToList() ?? new List<SupplyGroupDetailDTO>();
                }

                return header!;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Insert header row ─────────────────────────────────────────────

        public async Task<Result<string>> SaveSupplyGroup(
            SupplyGroupDTO supplyGroupDTO, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                // Audit fields set in DAL (per Result<T> pattern — mirrors RoleDAL)
                supplyGroupDTO.SupplyGroupCreatedById  = loginDTO.UserId;
                supplyGroupDTO.SupplyGroupCreatedOn    = DateTime.UtcNow;
                supplyGroupDTO.SupplyGroupModifiedById = loginDTO.UserId;
                supplyGroupDTO.SupplyGroupModifiedOn   = DateTime.UtcNow;

                int rows = await _QueryExecutor.ExecuteAsync(
                    loginDTO, SupplyGroupQB.SAVE_SUPPLYGROUP, supplyGroupDTO, tx).ConfigureAwait(false);

                // Insert detail rows inside same transaction
                foreach (var detail in supplyGroupDTO.SupplyGroupDetailArray)
                {
                    await _QueryExecutor.ExecuteAsync(
                        loginDTO, SupplyGroupQB.SAVE_SUPPLYGROUPDETAIL, detail, tx).ConfigureAwait(false);
                }

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

        // ── Update header row + replace detail rows ───────────────────────

        public async Task<Result<string>> UpdateSupplyGroup(
            SupplyGroupDTO supplyGroupDTO, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                supplyGroupDTO.SupplyGroupModifiedById = loginDTO.UserId;
                supplyGroupDTO.SupplyGroupModifiedOn   = DateTime.UtcNow;

                int rows = await _QueryExecutor.ExecuteAsync(
                    loginDTO, SupplyGroupQB.UPDATE_SUPPLYGROUP, supplyGroupDTO, tx).ConfigureAwait(false);

                // Delete existing details then reinsert (legacy DELETE+reinsert pattern)
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, SupplyGroupQB.DELETE_SUPPLYGROUPDETAIL,
                    new { supplygroupid = supplyGroupDTO.SupplyGroupId }, tx).ConfigureAwait(false);

                foreach (var detail in supplyGroupDTO.SupplyGroupDetailArray)
                {
                    await _QueryExecutor.ExecuteAsync(
                        loginDTO, SupplyGroupQB.SAVE_SUPPLYGROUPDETAIL, detail, tx).ConfigureAwait(false);
                }

                return rows > 0
                    ? Result<string>.Success("Supply group updated successfully.")
                    : Result<string>.Failure("Failed to update supply group.");
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Soft delete header (STATUS = 2) ───────────────────────────────

        public async Task<Result<string>> DeleteSupplyGroup(
            int supplyGroupId, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                var param  = new { supplygroupid = supplyGroupId };
                int result = await _QueryExecutor.ExecuteAsync(
                    loginDTO, SupplyGroupQB.DELETE_SUPPLYGROUP, param, tx).ConfigureAwait(false);

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

        // ── Paged pick-list for dropdowns ─────────────────────────────────

        public async Task<string> GetSelectListSupplyGroup(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                var param  = new { firstnumber = firstNumber, maxresult = maxResult };
                var result = await _QueryExecutor.QueryAsync<SupplyGroupPickListDTO>(
                    loginDTO, SupplyGroupQB.GET_SELECTLIST_SUPPLYGROUP, param).ConfigureAwait(false);

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