using DMSDAL.DTO.ESSDocument;
using DMSDAL.Query.ESSDocument;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Validation;
using GB5Shared.Resource.Response;

namespace DMSDAL.CustomCode.ESSDocument
{
    public class ESSDocumentDAL : IESSDocumentDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public ESSDocumentDAL(IQueryExecutor queryExecutor, IValidation validation)
        {
            _QueryExecutor = queryExecutor;
            _Validation = validation;
        }

        // ─────────────────────────────────────────────────────────────────
        // GET EMPLOYEE ID BY USER
        // ─────────────────────────────────────────────────────────────────
        public async Task<int> GetEmployeeIdByUser(
    LoginDTO login, CancellationToken ct)
        {
            try
            {
                int employeeId = 0;

                // Step 1: MEMPLOYEE.EMPLOYEEID = login.UserId
                var resultA = await _QueryExecutor
                    .QueryAsync<EmployeeIdDTO>(
                        login,
                        ESSDocumentQB.GET_EMPLOYEE_ID_BY_USERID,
                        new { UserId = login.UserId },
                        cancellationToken: ct)
                    .ConfigureAwait(false);

                employeeId = resultA?.FirstOrDefault()?.EmployeeId ?? 0;

                // Step 2: Fallback — MEMPLOYEE.EMPLOYEECODE = login.UserCode
                if (employeeId == 0 || employeeId == -1)
                {
                    var resultB = await _QueryExecutor
                        .QueryAsync<EmployeeIdDTO>(
                            login,
                            ESSDocumentQB.GET_EMPLOYEE_ID_BY_USERCODE,
                            new { UserCode = login.UserCode },
                            cancellationToken: ct)
                        .ConfigureAwait(false);

                    employeeId = resultB?.FirstOrDefault()?.EmployeeId ?? 0;
                }

                if (employeeId == 0 || employeeId == -1)
                    throw new Exception(
                        $"[ESS] No employee record found for " +
                        $"UserId={login.UserId}, UserCode={login.UserCode}. " +
                        $"Verify MEMPLOYEE has matching EMPLOYEEID or EMPLOYEECODE.");

                return employeeId;
            }
            catch (Exception ex)
            {
                string error = await _Validation
                    .HandleException(ex, ErrorResponse.GetFetchingMessge);
                throw new Exception(error);
            }
        }
        // ─────────────────────────────────────────────────────────────────
        // LIST MY DOCUMENTS
        // ─────────────────────────────────────────────────────────────────
        public async Task<IEnumerable<ESSDocumentDTO>> ListMyDocuments(
            int objectTypeId, int employeeId,
            string? categoryCode, int? year, int? month,
            LoginDTO login, CancellationToken ct)
        {
            try
            {
                // Month only applies when Year is also provided
                int? resolvedMonth = (year.HasValue && month.HasValue)
                    ? month : null;

                var param = new
                {
                    EmployeeId = employeeId,    // 1st priority
                    ObjectTypeId = objectTypeId,  // 2nd priority
                    CategoryCode = categoryCode,  // 3rd (optional)
                    Year = year,          // 4th (optional)
                    Month = resolvedMonth  // 5th (optional, needs Year)
                };

                return await _QueryExecutor
                    .QueryAsync<ESSDocumentDTO>(
                        login,
                        ESSDocumentQB.LIST_MY_DOCUMENTS,
                        param,
                        cancellationToken: ct)
                    .ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _Validation
                    .HandleException(ex, ErrorResponse.GetFetchingMessge);
                throw new Exception(error);
            }
        }

        // ─────────────────────────────────────────────────────────────────
        // GET BY ID
        // ─────────────────────────────────────────────────────────────────
        public async Task<ESSDocumentDTO?> GetById(
            int attachmentId, LoginDTO login, CancellationToken ct)
        {
            try
            {
                var param = new { AttachmentId = attachmentId };

                return await _QueryExecutor
                    .QuerySingleAsync<ESSDocumentDTO>(
                        login,
                        ESSDocumentQB.GET_BY_ID,
                        param)
                    .ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _Validation
                    .HandleException(ex, ErrorResponse.GetFetchingMessge);
                throw new Exception(error);
            }
        }

        // ─────────────────────────────────────────────────────────────────
        // INSERT ATTACHMENT
        // ─────────────────────────────────────────────────────────────────
        public async Task<int> InsertAttachment(
            ESSDocumentInsertDTO dto, LoginDTO login, CancellationToken ct)
        {
            try
            {
                await _QueryExecutor
                    .ExecuteAsync(login, ESSDocumentQB.INSERT_ATTACHMENT, dto)
                    .ConfigureAwait(false);

                int attachmentId = await _QueryExecutor
                    .ExecuteScalarAsync<int>(
                        login,
                        ESSDocumentQB.GET_ATTACHMENTID_BY_STORAGEPATH,
                        new { StoragePath = dto.StoragePath },
                        cancellationToken: ct)
                    .ConfigureAwait(false);

                return attachmentId;
            }
            catch (Exception ex)
            {
                string error = await _Validation
                    .HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(error);
            }
        }

        // ─────────────────────────────────────────────────────────────────
        // SOFT DELETE
        // ─────────────────────────────────────────────────────────────────
        public async Task DeactivateAttachment(
            int attachmentId, LoginDTO login, CancellationToken ct)
        {
            try
            {
                var param = new
                {
                    AttachmentId = attachmentId,
                    ModifiedById = login.UserId,
                    ModifiedOn = DateTime.UtcNow
                };

                await _QueryExecutor
                    .ExecuteAsync(
                        login,
                        ESSDocumentQB.DEACTIVATE_ATTACHMENT,
                        param,
                        cancellationToken: ct)
                    .ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _Validation
                    .HandleException(ex, ErrorResponse.UpdateErrorMessage);
                throw new Exception(error);
            }
        }
    }
}