using AdminDAL.DTO.FinanceBook;
using AdminDAL.Query.FinanceBook;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.GB5Solution;
using GB5Shared.ResponseStandard;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace AdminDAL.CustomCode.FinanceBook
{
    public class FinanceBookDAL : IFinanceBookDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public FinanceBookDAL(IQueryExecutor QueryExecutor)
        {
            _QueryExecutor = QueryExecutor;
        }
        // <summary>
        /// Retrieves a single FinanceBook record by its unique identifier.
        /// </summary>
        /// <param name="FinanceBookId">The ID of the FinanceBook to retrieve.</param>
        /// <param name="LoginDTO">The current user's login details.</param>
        /// <returns>A JSON string representing the FinanceBook details.</returns>
        public async Task<string> GetFinanceBook(int FinanceBookId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = FinanceBookQB.GET_FINANCEBOOK;
                var parameters = new { FinanceBookId = FinanceBookId };
                FinanceBookDTO financeBookDTO = await _QueryExecutor.QuerySingleAsync<FinanceBookDTO>(LoginDTO, Sql, parameters);
                return JsonConvert.SerializeObject(financeBookDTO);
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// Retrieves a list of FinanceBook records based on specified filter criteria.
        /// Typically used for populating dropdowns or selection lists.
        /// </summary>
        /// <param name="CriteriaDTO">Filter criteria for selecting FinanceBooks.</param>
        /// <param name="LoginDTO">The current user's login details.</param>
        /// <returns>A JSON string representing a list of FinanceBooks.</returns>
        public async Task<string> GetSelectListFinanceBook(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string sql = FinanceBookQB.GET_SELECTLIST_FINANCEBOOK;
                var parameters = new { };

                IEnumerable<FinanceBookPicklistDTO> result = await _QueryExecutor.QueryAsync<FinanceBookPicklistDTO>(LoginDTO, sql, parameters);
                string json = JsonConvert.SerializeObject(result);

                return json;
            }
            catch (Exception ex)
            {
                throw new Exception(AdminResource.SelectListFailed, ex);
            }
        }
        /// <summary>
        /// Inserts a new FinanceBook record into the database.
        /// Automatically sets created and modified metadata fields.
        /// </summary>
        /// <param name="FinanceBookDTO">The FinanceBook data to insert.</param>
        /// <param name="LoginDTO">The current user's login details.</param>
        /// <returns>The number of rows affected (should be 1 if successful).</returns>
        public async Task<int> SaveFinanceBook(FinanceBookDTO FinanceBookDTO, LoginDTO LoginDTO)
        {
            try
            {
                FinanceBookDTO.FinanceBookCreatedById = LoginDTO.UserId;
                FinanceBookDTO.FinanceBookCreatedOn = DateTime.UtcNow;
                FinanceBookDTO.FinanceBookModifiedById = LoginDTO.UserId;
                FinanceBookDTO.FinanceBookModifiedOn = DateTime.UtcNow;

                string Sql = FinanceBookQB.SAVE_FINANCEBOOK;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, FinanceBookDTO);
            }
            catch (Exception ex)
            {
                throw new Exception(AdminResource.SaveFailed, ex);
            }
        }
        /// <summary>
        /// Updates an existing FinanceBook record in the database.
        /// Automatically updates modified metadata fields.
        /// </summary>
        /// <param name="FinanceBookDTO">The FinanceBook data to update.</param>
        /// <param name="LoginDTO">The current user's login details.</param>
        /// <returns>The number of rows affected (should be 1 if successful).</returns>
        public async Task<int> UpdateFinanceBook(FinanceBookDTO FinanceBookDTO, LoginDTO LoginDTO)
        {
            try
            {
                FinanceBookDTO.FinanceBookModifiedById = LoginDTO.UserId;
                FinanceBookDTO.FinanceBookModifiedOn = DateTime.UtcNow;

                string Sql = FinanceBookQB.UPDATE_FINANCEBOOK;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, FinanceBookDTO);
            }
            catch (Exception ex)
            {
                throw new Exception(AdminResource.UpdateFailed, ex);
            }
        }
         /// <summary>
         /// Deletes a FinanceBook record by its unique identifier.
         /// </summary>
         /// <param name="FinanceBookId">The ID of the FinanceBook to delete.</param>
         /// <param name="LoginDTO">The current user's login details.</param>
         /// <returns>A message string indicating success or failure of the delete operation.</returns>
        public async Task<string> DeleteFinanceBook(int FinanceBookId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = FinanceBookQB.DELETE_FINANCEBOOK;
                var parameters = new { FinanceBookId = FinanceBookId };
                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, parameters);

                if (result > 0)
                {
                    return AdminResource.DeletedSuccessfully;
                }
                else
                {
                    return AdminResource.NotFoundToDelete;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(AdminResource.DeleteFailed, ex);
            }
        }
    }
}