using System.Data;
using System.Data.Common;
using Dapper;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using Microsoft.Data.SqlClient;
using Newtonsoft.Json;
using SwDAL.DTO.ClientDatabase;
using SwDAL.DTO.DbServer;
using SwDAL.Query.ClientDatabase;

namespace SwDAL.CustomCode.ClientDatabase;

public class ClientDatabaseDAL : IClientDatabaseDAL
{
    private readonly IQueryExecutor _QueryExecutor;

    public ClientDatabaseDAL(
        IQueryExecutor queryExecutor)
    {
        _QueryExecutor = queryExecutor;
    }

    public async Task<ClientDatabaseDTO?> GetById(int clientDbId, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.QuerySingleAsync<ClientDatabaseDTO>(
               loginDTO, ClientDatabaseQB.GET_BY_ID,
               new { ClientDbId = clientDbId, TenantId = loginDTO.ClientId })
               .ConfigureAwait(false);

    public async Task<ClientDatabaseDTO?> GetByServerAndDatabaseName(int dbServerId, string databaseName, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.QuerySingleAsync<ClientDatabaseDTO>(
               loginDTO, ClientDatabaseQB.GET_BY_SERVER_AND_DATABASE_NAME,
               new { DbServerId = dbServerId, DatabaseName = databaseName, TenantId = loginDTO.ClientId },
               cancellationToken: ct)
               .ConfigureAwait(false);

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

    // ============================================================
    // INSERT
    // ============================================================

    public async Task<Result<string>> SaveClientDatabase(ClientDatabaseDTO 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,ClientDatabaseQB.INSERT,dto,tx).ConfigureAwait(false);

        return rows > 0
            ? Result<string>.Success("Client database saved.")
            : Result<string>.Failure("Failed to save client database.");
    }

    // ============================================================
    // UPDATE
    // ============================================================

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

        int rows = await _QueryExecutor.ExecuteAsync(loginDTO,ClientDatabaseQB.UPDATE,dto,tx).ConfigureAwait(false);

        return rows > 0? Result<string>.Success("Client database updated.")
            : Result<string>.Failure("Failed to update client database.");
    }


    public async Task<Result<string>> UpdateStatus(
        int clientDbId, byte clientDbStatus, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        int rows = await _QueryExecutor.ExecuteAsync(
            loginDTO, ClientDatabaseQB.UPDATE_STATUS,
            new
            {
                ClientDbId = clientDbId,
                ClientDbStatus = clientDbStatus,
                ModifiedById = loginDTO.UserId,
                ModifiedOn = DateTime.UtcNow,
                TenantId = loginDTO.ClientId
            }, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Client database status updated.")
            : Result<string>.Failure("Failed to update client database status.");
    }

    public async Task<int> UpdateDeliveryMode(int clientDbId, byte deliveryMode, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.ExecuteAsync(
               loginDTO, ClientDatabaseQB.UPDATE_DELIVERY_MODE,
               new
               {
                   ClientDbId = clientDbId,
                   DeliveryMode = deliveryMode,
                   ModifiedById = loginDTO.UserId,
                   ModifiedOn = DateTime.UtcNow,
                   TenantId = loginDTO.ClientId
               }, cancellationToken: ct)
               .ConfigureAwait(false);

    public async Task<Result<string>> DeleteClientDatabase(
        int clientDbId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        int rows = await _QueryExecutor.ExecuteAsync(
            loginDTO, ClientDatabaseQB.DELETE,
            new { ClientDbId = clientDbId, TenantId = loginDTO.ClientId }, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Client database deleted.")
            : Result<string>.Failure("Client database not found, not in Pending status, or already deleted.");
    }

    public async Task<Result<string>> UpdateCredentialVaultPaths(
        int clientDbId, string dbaVaultPath, string appVaultPath, string readOnlyVaultPath,
        LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        int rows = await _QueryExecutor.ExecuteAsync(
            loginDTO, ClientDatabaseQB.UPDATE_CREDENTIAL_VAULT_PATHS,
            new
            {
                ClientDbId = clientDbId,
                DbaVaultPath = dbaVaultPath,
                AppVaultPath = appVaultPath,
                ReadOnlyVaultPath = readOnlyVaultPath,
                ModifiedById = loginDTO.UserId,
                ModifiedOn = DateTime.UtcNow,
                TenantId = loginDTO.ClientId
            }, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Client database credential paths updated.")
            : Result<string>.Failure("Failed to update client database credential paths.");
    }

    public async Task<Result<string>> UpdateServerConfigId(
        int clientDbId, int serverConfigId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        int rows = await _QueryExecutor.ExecuteAsync(
            loginDTO, ClientDatabaseQB.UPDATE_SERVER_CONFIG_ID,
            new
            {
                ClientDbId = clientDbId,
                ServerConfigId = serverConfigId,
                ModifiedById = loginDTO.UserId,
                ModifiedOn = DateTime.UtcNow,
                TenantId = loginDTO.ClientId
            }, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Client database ServerConfigId updated.")
            : Result<string>.Failure("Failed to update client database ServerConfigId.");
    }

    public async Task<int?> GetMainServerConfigIdForModel(int dbModelId, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.QuerySingleAsync<int?>(
               loginDTO, ClientDatabaseQB.GET_MAIN_SERVER_CONFIG_ID_FOR_MODEL,
               new { DbModelId = dbModelId, TenantId = loginDTO.ClientId },
               cancellationToken: ct)
               .ConfigureAwait(false);
}