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

namespace SwDAL.CustomCode.MetadataSync;

public class MetadataSyncDAL : IMetadataSyncDAL
{
    private readonly IQueryExecutor _QueryExecutor;

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

    public async Task<MetadataSyncDTO?> GetById(int metaVersionId, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.QuerySingleAsync<MetadataSyncDTO>(
               loginDTO, MetadataSyncQB.GET_BY_ID,
               new { MetaVersionId = metaVersionId, TenantId = loginDTO.ClientId })
               .ConfigureAwait(false);

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

    public async Task<Result<string>> SaveMetadataSync(
        MetadataSyncDTO 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, MetadataSyncQB.INSERT, dto, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Metadata version saved.")
            : Result<string>.Failure("Failed to save metadata version.");
    }

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

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

    public async Task<Result<string>> UpdateStatus(
        int metaVersionId, byte status, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        int rows = await _QueryExecutor.ExecuteAsync(
            loginDTO, MetadataSyncQB.UPDATE_STATUS,
            new
            {
                MetaVersionId = metaVersionId,
                MetaStatus    = status,
                ModifiedById  = loginDTO.UserId,
                ModifiedOn    = DateTime.UtcNow,
                TenantId      = loginDTO.ClientId
            }, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Metadata status updated.")
            : Result<string>.Failure("Failed to update metadata status.");
    }

    public async Task<Result<string>> DeleteMetadataSync(
        int metaVersionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        int rows = await _QueryExecutor.ExecuteAsync(
            loginDTO, MetadataSyncQB.DELETE,
            new { MetaVersionId = metaVersionId, TenantId = loginDTO.ClientId }, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Metadata version deleted.")
            : Result<string>.Failure("Metadata version not found or cannot be deleted.");
    }

    public async Task<IEnumerable<MetadataChangeDTO>> GetChanges(
        int metaVersionId, LoginDTO loginDTO, CancellationToken ct)
        => await _QueryExecutor.QueryAsync<MetadataChangeDTO>(
               loginDTO, MetadataSyncQB.GET_CHANGES,
               new { MetaVersionId = metaVersionId, TenantId = loginDTO.ClientId })
               .ConfigureAwait(false);

    public async Task<Result<string>> InsertChange(
        MetadataChangeDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        dto.TenantId = loginDTO.ClientId;
        int rows = await _QueryExecutor.ExecuteAsync(loginDTO, MetadataSyncQB.INSERT_CHANGE, dto, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Metadata change inserted.")
            : Result<string>.Failure("Failed to insert metadata change.");
    }

    public async Task<Result<string>> DeleteChange(
        int metaVersionId, string tableName, string columnName,
        LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        int rows = await _QueryExecutor.ExecuteAsync(
            loginDTO, MetadataSyncQB.DELETE_CHANGE,
            new { MetaVersionId = metaVersionId, TableName = tableName, ColumnName = columnName,
                  TenantId = loginDTO.ClientId }, tx)
            .ConfigureAwait(false);
        return rows > 0
            ? Result<string>.Success("Metadata change deleted.")
            : Result<string>.Failure("Metadata change not found or already deleted.");
    }

    public async Task<Result<string>> DeleteAllChanges(
        int metaVersionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        await _QueryExecutor.ExecuteAsync(
            loginDTO, MetadataSyncQB.DELETE_CHANGES_BY_VERSION,
            new { MetaVersionId = metaVersionId, TenantId = loginDTO.ClientId }, tx)
            .ConfigureAwait(false);
        return Result<string>.Success("All metadata changes deleted.");
    }
}