using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using Newtonsoft.Json;
using QMSDAL.DTO;
using QMSDAL.Interfaces;  // ← Add this using
using QMSDAL.Query.ChecklistLog;
using System.Data.Common;

namespace QMSDAL.CustomCode.ChecklistLog
{
    public class ChecklistLogDAL : IChecklistLogDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

        // ── Get single log header ─────────────────────────────────────────────

        public async Task<ChecklistLogDTO?> GetChecklistLogAsync(
            int checklistLogId, LoginDTO login, CancellationToken ct = default)
        {
            try
            {
                var param = new { ChecklistLogId = checklistLogId };
                return await _QueryExecutor
                    .QuerySingleAsync<ChecklistLogDTO>(login, ChecklistLogQB.GET_CHECKLISTLOG, param)
                    .ConfigureAwait(false);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Get paged log list ────────────────────────────────────────────────

        public async Task<IEnumerable<ChecklistLogDTO>> GetChecklistLogListAsync(
            int checklistId, int overallResult, DateTime? fromDate, DateTime? toDate,
            int pageOffset, int pageSize, LoginDTO login, CancellationToken ct = default)
        {
            try
            {
                var param = new
                {
                    ChecklistId = checklistId,
                    OverallResult = overallResult,
                    FromDate = fromDate,
                    ToDate = toDate,
                    PageOffset = pageOffset,
                    PageSize = pageSize
                };

                var result = await _QueryExecutor
                    .QueryAsync<ChecklistLogDTO>(
                        login,
                        ChecklistLogQB.GET_CHECKLISTLOG_LIST,
                        param,
                        cancellationToken: ct)
                    .ConfigureAwait(false);

                return result ?? Enumerable.Empty<ChecklistLogDTO>();
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Get detail rows for a log ─────────────────────────────────────────

        public async Task<IEnumerable<ChecklistLogDetailDTO>> GetChecklistLogDetailAsync(
            int checklistLogId, LoginDTO login, CancellationToken ct = default)
        {
            try
            {
                var param = new { ChecklistLogId = checklistLogId };
                var result = await _QueryExecutor
                    .QueryAsync<ChecklistLogDetailDTO>(
                        login, ChecklistLogQB.GET_CHECKLISTLOGDETAIL, param, cancellationToken: ct)
                    .ConfigureAwait(false);

                return result ?? Enumerable.Empty<ChecklistLogDetailDTO>();
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Insert log header (returns Result<string>) ──────────────────────

        // ChecklistLogDAL.cs

        public async Task<int> SaveChecklistLogAndGetIdAsync(
            ChecklistLogDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                // Audit fields are already set in BLL
                // Just save and return the generated ID

                int generatedId = await _QueryExecutor
                    .ExecuteScalarAsync<int>(login, ChecklistLogQB.SAVE_CHECKLISTLOG, dto, tx)
                    .ConfigureAwait(false);

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

        public async Task<bool> UpdateChecklistLogAsync(
            ChecklistLogDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                // Audit fields are already set in BLL
                int rows = await _QueryExecutor
                    .ExecuteAsync(login, ChecklistLogQB.UPDATE_CHECKLISTLOG, dto, tx, ct)
                    .ConfigureAwait(false);

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

        public async Task<bool> SaveChecklistLogDetailsAsync(
            IEnumerable<ChecklistLogDetailDTO> details, LoginDTO login,
            DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                var detailList = details.ToList();
                foreach (var detail in detailList)
                {
                    // Audit fields are already set in BLL
                    await _QueryExecutor
                        .ExecuteAsync(login, ChecklistLogQB.SAVE_CHECKLISTLOGDETAIL, detail, tx, ct)
                        .ConfigureAwait(false);
                }

                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<string> GetSelectListChecklistLog(CriteriaDTO criteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string sql = ChecklistLogQB.GET_SELECTLIST_CHECKLISTLOG;
                var result = await _QueryExecutor.QueryAsync<ChecklistLogPicklistDTO>(LoginDTO, sql, null!);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception ex)
            {
                throw;
            }
        }
    }
}