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

namespace SwDAL.CustomCode.DbServer;

public class DbServerDAL : IDbServerDAL
{
    private readonly IQueryExecutor _QueryExecutor;

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

    // Credentials are excluded from the projection — see DbServerQB.GET_BY_ID
    public async Task<DbServerDTO?> GetById(int dbServerId, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.QuerySingleAsync<DbServerDTO>(
               loginDTO, DbServerQB.GET_BY_ID,
               new { DbServerId = dbServerId, TenantId = loginDTO.ClientId }, cancellationToken: ct)
               .ConfigureAwait(false);

    // Includes DBUSERNAME + DBPASSWORD — only for internal service-to-service use (provisioning, DML)
    public async Task<DbServerDTO?> GetByIdWithCredentials(int dbServerId, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.QuerySingleAsync<DbServerDTO>(
               loginDTO, DbServerQB.GET_BY_ID_WITH_CREDENTIALS,
               new { DbServerId = dbServerId, TenantId = loginDTO.ClientId })
               .ConfigureAwait(false);

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

    public async Task<Result<string>> SaveDbServer(
        DbServerDTO 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, DbServerQB.INSERT, dto, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("DB server saved.")
            : Result<string>.Failure("Failed to save DB server.");
    }

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

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

    public async Task<int> DeleteDbServer(int dbServerId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        int rows = await _QueryExecutor.ExecuteAsync(loginDTO, DbServerQB.DELETE,
            new
            {
                DbServerId = dbServerId,
                TenantId = loginDTO.ClientId
            },
            tx
        ).ConfigureAwait(false);

        return rows;
    }

    public async Task<string> GetListDBServer(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
    {
        var result = await _QueryExecutor.QueryAsync<dynamic>(
            login, DbServerQB.GET_LIST_DBSERVER);
        return JsonConvert.SerializeObject(result);
    }

    // GET UNIQUE SERVER HOSTNAMES
    public async Task<List<DbServerDTO>> GetServerHostNameList(LoginDTO loginDTO, CancellationToken ct)
    {
        var result = await _QueryExecutor.QueryAsync<DbServerDTO>(
            loginDTO,
            DbServerQB.GET_SERVER_HOSTNAME_LIST,
            new
            {
                TenantId = loginDTO.ClientId
            }
        ).ConfigureAwait(false);

        return result.ToList();
    }

    // GET DATABASES FOR SELECTED HOSTNAME

    public async Task<List<DbServerDTO>> GetDatabaseListByHostName(string hostName, LoginDTO loginDTO, CancellationToken ct)
    {
        var result = await _QueryExecutor.QueryAsync<DbServerDTO>(
            loginDTO,
            DbServerQB.GET_DATABASE_LIST_BY_HOSTNAME,
            new
            {
                HostName = hostName,
                TenantId = loginDTO.ClientId
            }
        ).ConfigureAwait(false);

        return result.ToList();
    }


}