using IWS5.DatabaseAccess.Interfaces;
using IWS5.DatabaseAccess.IwsDataContext;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

namespace IWS5.DatabaseAccess.Repositories;

public class CustomQueryRepository : ICustomQueryRepository
{
    private readonly GreenfieldPd2015Context _context;

    public CustomQueryRepository(GreenfieldPd2015Context context)
    {
        _context = context;
    }

    public async Task<string> ExecuteScalarStringAsync(string sqlQuery, params SqlParameter[] parameters)
    {
        using (var command = _context.Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = sqlQuery;
            
            if (parameters.Length > 0)
            {
                command.Parameters.AddRange(parameters);
            }
            
            await _context.Database.OpenConnectionAsync();
            
            try
            {
                var result = await command.ExecuteScalarAsync();
                return result?.ToString() ?? string.Empty;
            }
            finally
            {
                await _context.Database.CloseConnectionAsync();
            }
        }
    }

    public async Task<string?> ExecuteScalarStringOrDefaultAsync(string sqlQuery, params SqlParameter[] parameters)
    {
        using (var command = _context.Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = sqlQuery;
            
            if (parameters.Length > 0)
            {
                command.Parameters.AddRange(parameters);
            }
            
            await _context.Database.OpenConnectionAsync();
            
            try
            {
                var result = await command.ExecuteScalarAsync();
                return result?.ToString();
            }
            finally
            {
                await _context.Database.CloseConnectionAsync();
            }
        }
    }

    public async Task<List<string>> ExecuteQueryStringsAsync(string sqlQuery, params SqlParameter[] parameters)
    {
        var results = new List<string>();
        
        using (var command = _context.Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = sqlQuery;
            
            if (parameters.Length > 0)
            {
                command.Parameters.AddRange(parameters);
            }
            
            await _context.Database.OpenConnectionAsync();
            
            try
            {
                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var value = reader.GetValue(0)?.ToString() ?? string.Empty;
                        results.Add(value);
                    }
                }
            }
            finally
            {
                await _context.Database.CloseConnectionAsync();
            }
        }
        
        return results;
    }
}