using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using SwDAL.CustomCode.Tag;
using SwDAL.DTO.ChangeRequest;
using SwDAL.DTO.DdlScript;
using SwDAL.Enums;
using SwDAL.Query.ChangeRequest;

namespace SwDAL.CustomCode.ChangeRequest;

public class ChangeRequestDAL : IChangeRequestDAL
{
    private readonly IQueryExecutor _qe;
    private readonly ITagDAL        _tagDal;

    public ChangeRequestDAL(IQueryExecutor qe, ITagDAL tagDal)
    {
        _qe     = qe;
        _tagDal = tagDal;
    }

    public async Task<ChangeRequestDTO?> GetById(int changeRequestId, LoginDTO login, CancellationToken ct)
    {
        var dto = await _qe.QuerySingleAsync<ChangeRequestDTO>(login, ChangeRequestQB.GET_BY_ID,
            new { ChangeRequestId = changeRequestId, TenantId = login.ClientId }, cancellationToken: ct)
            .ConfigureAwait(false);

        if (dto is null) return null;

        var timeline = await _qe.QueryAsync<ChangeRequestTimelineDTO>(login, ChangeRequestQB.GET_TIMELINE,
            new { ChangeRequestId = changeRequestId, TenantId = login.ClientId }, cancellationToken: ct)
            .ConfigureAwait(false);

        var tags = await _tagDal.GetByCr(changeRequestId, login, ct).ConfigureAwait(false);

        dto.Timeline = timeline.ToList();
        dto.Tags     = tags.ToList();
        return dto;
    }

    public async Task<(IEnumerable<ChangeRequestListDTO> rows, int total)> GetList(
        ChangeRequestListCriteria criteria, int pageOffset, int pageSize, LoginDTO login, CancellationToken ct)
    {
        var (sql, countSql) = ChangeRequestQB.BuildCrList(criteria);
        var param = new
        {
            TenantId         = login.ClientId,
            CrStatus         = (byte?)criteria.CrStatus,
            QueryType        = (byte?)criteria.QueryType,
            Category         = (byte?)criteria.Category,
            criteria.ClientDatabaseId,
            SearchText       = string.IsNullOrWhiteSpace(criteria.SearchText) ? null : $"%{criteria.SearchText}%",
            PageOffset       = pageOffset,
            PageSize         = pageSize
        };
        var paged = await _qe.QueryPagedAsync<ChangeRequestListDTO>(login,sql,param);
        return (paged.Items ?? Enumerable.Empty<ChangeRequestListDTO>(),paged.TotalCount);
    }

    public async Task<IEnumerable<ChangeRequestListDTO>> GetSelectList(int firstNumber, int maxResult, LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<ChangeRequestListDTO>(login, ChangeRequestQB.GET_SELECT_LIST,
               new { TenantId = login.ClientId, FirstNumber = firstNumber, MaxResult = maxResult },
               cancellationToken: ct)
               .ConfigureAwait(false);

    public async Task<int> Insert(ChangeRequestDTO dto, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteIdentityAsync(login, ChangeRequestQB.INSERT, new
           {
               dto.Title,
               dto.Description,
               dto.Sql,
               dto.ClientDatabaseId,
               QueryType        = (byte)dto.QueryType,
               Category         = (byte)dto.Category,
               dto.TicketReference,
               dto.RollbackPlan,
               dto.ImpactAnalysis,
               ProvisioningMode  = (byte?)dto.ProvisioningMode,
               dto.TemplateBackupRef,
               dto.BaselineUpgradePackageId,
               TenantId         = login.ClientId,
               CreatedById      = login.UserId,
               ModifiedById     = login.UserId
           }).ConfigureAwait(false);

    public async Task Update(ChangeRequestDTO dto, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(login, ChangeRequestQB.UPDATE, new
           {
               dto.ChangeRequestId,
               dto.Title,
               dto.Description,
               dto.Sql,
               dto.ClientDatabaseId,
               QueryType        = (byte)dto.QueryType,
               Category         = (byte)dto.Category,
               dto.TicketReference,
               dto.RollbackPlan,
               dto.ImpactAnalysis,
               ProvisioningMode  = (byte?)dto.ProvisioningMode,
               dto.TemplateBackupRef,
               dto.BaselineUpgradePackageId,
               TenantId         = login.ClientId,
               ModifiedById     = login.UserId
           }, cancellationToken: ct).ConfigureAwait(false);

    public async Task UpdateStatus(int changeRequestId, byte newStatus, byte expectedStatus,
        int performedById, string? comment, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(login, ChangeRequestQB.UPDATE_STATUS, new
           {
               ChangeRequestId  = changeRequestId,
               NewStatus        = newStatus,
               ExpectedStatus   = expectedStatus,
               PerformedById    = performedById,
               Comment          = comment,
               TenantId         = login.ClientId
        }, cancellationToken: ct).ConfigureAwait(false);

    public async Task AppendTimeline(int changeRequestId, CrTimelineEventType eventType,
        string? comment, int performedById, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(login, ChangeRequestQB.INSERT_TIMELINE, new
           {
               ChangeRequestId = changeRequestId,
               EventType       = (byte)eventType,
               EventComment    = comment,
               PerformedById   = performedById,
               TenantId        = login.ClientId
        }, cancellationToken: ct).ConfigureAwait(false);

    public async Task Delete(int changeRequestId, LoginDTO login, CancellationToken ct)
        => await _qe.ExecuteAsync(login, ChangeRequestQB.DELETE, new
           {
               ChangeRequestId = changeRequestId,
               TenantId        = login.ClientId,
               ModifiedById    = login.UserId
           }, cancellationToken: ct).ConfigureAwait(false);

    public async Task<IEnumerable<DdlScriptDTO>> GetLinkedApprovedDdlScripts(
        int changeRequestId, LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<DdlScriptDTO>(login, ChangeRequestQB.GET_LINKED_APPROVED_DDL,
               new { ChangeRequestId = changeRequestId, TenantId = login.ClientId },
               cancellationToken: ct)
               .ConfigureAwait(false);

    public async Task UpdateExecutionStatusAsync(int changeRequestId, byte newExecutionStatus, LoginDTO login, CancellationToken ct, int? queuedById = null)
        => await _qe.ExecuteAsync(login, ChangeRequestQB.UPDATE_EXECUTION_STATUS, new
           {
               ChangeRequestId    = changeRequestId,
               NewExecutionStatus = newExecutionStatus,
               QueuedById         = queuedById
           }, cancellationToken: ct).ConfigureAwait(false);

    public async Task<IEnumerable<ChangeRequestDTO>> GetQueuedForExecutionAsync(LoginDTO login, CancellationToken ct)
        => await _qe.QueryAsync<ChangeRequestDTO>(login, ChangeRequestQB.GET_QUEUED_FOR_EXECUTION,
               new { TenantId = login.ClientId }, cancellationToken: ct)
               .ConfigureAwait(false);
}