using System.Data;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using System.Data.Common;
using Newtonsoft.Json;
using SwDAL.DTO.DdlScript;
using SwDAL.Query.DdlScript;

namespace SwDAL.CustomCode.DdlScript;

public class DdlScriptDAL : IDdlScriptDAL
{
    private readonly IQueryExecutor _QueryExecutor;

    public DdlScriptDAL(IQueryExecutor queryExecutor) => _QueryExecutor = queryExecutor;

    public async Task<DdlScriptDTO?> GetById(int ddlScriptId, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.QuerySingleAsync<DdlScriptDTO>(
               loginDTO, DdlScriptQB.GET_BY_ID,
               new { DdlScriptId = ddlScriptId, TenantId = loginDTO.ClientId })
               .ConfigureAwait(false);

    public async Task<IEnumerable<DdlScriptDTO>> GetByBranch(int branchId, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.QueryAsync<DdlScriptDTO>(
               loginDTO, DdlScriptQB.GET_BY_BRANCH,
               new { BranchId = branchId, TenantId = loginDTO.ClientId })
               .ConfigureAwait(false);

    public async Task<string> GetSelectListDdlScript(int first, int max, LoginDTO loginDTO)
    {
        var result = await _QueryExecutor.QueryAsync<DdlScriptPickListDTO>(
            loginDTO, DdlScriptQB.GET_SELECT_LIST,
            new { firstnumber = first, maxresult = max, TenantId = loginDTO.ClientId })
            .ConfigureAwait(false);
        return JsonConvert.SerializeObject(result);
    }

    public async Task<bool> ExistsInExecutions(int ddlScriptId, LoginDTO loginDTO)
    {
        int count = await _QueryExecutor.ExecuteScalarAsync<int>(
            loginDTO, DdlScriptQB.CHECK_IN_EXECUTIONS,
            new { DdlScriptId = ddlScriptId, TenantId = loginDTO.ClientId })
            .ConfigureAwait(false);
        return count > 0;
    }

    public async Task<Result<string>> SaveDdlScript(
        DdlScriptDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        dto.CreatedById  = loginDTO.UserId;
        dto.CreatedOn    = DateTime.UtcNow;
        dto.ModifiedById = loginDTO.UserId;
        dto.ModifiedOn   = DateTime.UtcNow;

        int rows = await _QueryExecutor.ExecuteAsync(loginDTO, DdlScriptQB.INSERT, dto, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("DDL script saved.")
            : Result<string>.Failure("Failed to save DDL script.");
    }

    public async Task<Result<string>> UpdateDdlScript(
        DdlScriptDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        dto.ModifiedById = loginDTO.UserId;
        dto.ModifiedOn   = DateTime.UtcNow;

        int rows = await _QueryExecutor.ExecuteAsync(loginDTO, DdlScriptQB.UPDATE, dto, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("DDL script updated.")
            : Result<string>.Failure("Failed to update DDL script.");
    }

    public async Task<Result<string>> DeleteDdlScript(
        int ddlScriptId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        int rows = await _QueryExecutor.ExecuteAsync(
            loginDTO, DdlScriptQB.DELETE,
            new { DdlScriptId = ddlScriptId, TenantId = loginDTO.ClientId }, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("DDL script deleted.")
            : Result<string>.Failure("DDL script not found, already deleted, or not in Draft status.");
    }

    public async Task<Result<string>> UpdateScriptStatus(
        int ddlScriptId, byte newStatus, byte requiredCurrentStatus,
        int approvedById, DateTime? approvedOn,
        LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        int rows = await _QueryExecutor.ExecuteAsync(
            loginDTO, DdlScriptQB.UPDATE_STATUS,
            new
            {
                DdlScriptId           = ddlScriptId,
                ScriptStatus          = newStatus,
                RequiredCurrentStatus = requiredCurrentStatus,
                ApprovedById          = approvedById,
                ApprovedOn            = approvedOn,
                ModifiedById          = loginDTO.UserId,
                ModifiedOn            = DateTime.UtcNow,
                TenantId              = loginDTO.ClientId
            }, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Script status updated.")
            : Result<string>.Failure("Script status transition failed — script not found or not in required status.");
    }
}