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

namespace SwDAL.CustomCode.SyncGroup;

public class SyncGroupDAL : ISyncGroupDAL
{
    private readonly IQueryExecutor _QueryExecutor;

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

    public async Task<SyncGroupDTO?> GetById(int syncGroupId, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.QuerySingleAsync<SyncGroupDTO>(
               loginDTO, SyncGroupQB.GET_BY_ID,
               new { SyncGroupId = syncGroupId, TenantId = loginDTO.ClientId })
               .ConfigureAwait(false);

    public async Task<IEnumerable<SyncTableDTO>> GetTables(int syncGroupId, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.QueryAsync<SyncTableDTO>(
               loginDTO, SyncGroupQB.GET_TABLES,
               new { SyncGroupId = syncGroupId, TenantId = loginDTO.ClientId })
               .ConfigureAwait(false);

    public async Task<bool> ExistsInClientDb(int syncGroupId, LoginDTO loginDTO)
    {
        int count = await _QueryExecutor.ExecuteScalarAsync<int>(
            loginDTO, SyncGroupQB.CHECK_IN_CLIENTDB,
            new { SyncGroupId = syncGroupId, TenantId = loginDTO.ClientId })
            .ConfigureAwait(false);
        return count > 0;
    }

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

    public async Task<Result<string>> SaveSyncGroup(
        SyncGroupDTO 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, SyncGroupQB.INSERT, dto, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Sync group saved.")
            : Result<string>.Failure("Failed to save sync group.");
    }

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

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

    public async Task<Result<string>> DeleteSyncGroup(
        int syncGroupId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        int rows = await _QueryExecutor.ExecuteAsync(
            loginDTO, SyncGroupQB.DELETE,
            new { SyncGroupId = syncGroupId, TenantId = loginDTO.ClientId }, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Sync group deleted.")
            : Result<string>.Failure("Sync group not found or already deleted.");
    }

    public async Task<Result<string>> InsertSyncTable(
        SyncTableDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        dto.TenantId = loginDTO.ClientId;

        int rows = await _QueryExecutor.ExecuteAsync(loginDTO, SyncGroupQB.INSERT_TABLE, dto, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Sync table added.")
            : Result<string>.Failure("Failed to add sync table.");
    }

    public async Task<Result<string>> DeleteSyncTable(
        int syncTableId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        int rows = await _QueryExecutor.ExecuteAsync(
            loginDTO, SyncGroupQB.DELETE_TABLE_BY_ID,
            new { SyncTableId = syncTableId, TenantId = loginDTO.ClientId }, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Sync table deleted.")
            : Result<string>.Failure("Sync table not found or already deleted.");
    }

    public async Task<Result<string>> DeleteAllSyncTables(
        int syncGroupId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        await _QueryExecutor.ExecuteAsync(
            loginDTO, SyncGroupQB.DELETE_TABLES_BY_GROUP,
            new { SyncGroupId = syncGroupId, TenantId = loginDTO.ClientId }, tx)
            .ConfigureAwait(false);
        return Result<string>.Success("Sync tables deleted.");
    }

    //-----------------------------GetSynGroup Header--------------------------------------
    public async Task<SyncGroupDTO?> GetSyncGroupHeader(int syncGroupId, LoginDTO login, CancellationToken ct)
    {
        return await _QueryExecutor
            .QuerySingleAsync<SyncGroupDTO>(
                login,
                SyncGroupQB.GET_BY_ID,
                new
                {
                    SyncGroupId = syncGroupId,
                    TenantId = login.ClientId
                },
                cancellationToken: ct)
            .ConfigureAwait(false);
    }

    //-------------------------------GetSyncTable (Child)--------------------------------------
    public async Task<IEnumerable<SyncTableDTO>> GetSyncTables(int syncGroupId, LoginDTO login, CancellationToken ct)
    {
        return await _QueryExecutor.QueryAsync<SyncTableDTO>(login,SyncGroupQB.GET_TABLES,
                new
                {
                    SyncGroupId = syncGroupId,
                    TenantId = login.ClientId
                },
                cancellationToken: ct).ConfigureAwait(false) ?? [];
    }
}