using IWS5.DatabaseAccess.Interfaces.IAdmin;
using IWS5.DatabaseAccess.IwsDataContext;
using IWS5.DatabaseAccess.IwsDataModels;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Data;

namespace IWS5.DatabaseAccess.Repositories.IAdmin;

public class ReportRepository(GreenfieldPd2015Context context)
    : IWSRepository<Atreport>(context), IReportRepository
{
    public async Task<List<Atreport>> ListAsync()
    {
        return await context.Atreports
            .OrderBy(r => r.Name)
            .ToListAsync();
    }

    public async Task<Atreport?> GetByIdAsync(int reportId)
    {
        return await context.Atreports
            .FirstOrDefaultAsync(r => r.ReportId == reportId);
    }

    public async Task<Atreport?> GetByNameAsync(string name)
    {
        return await context.Atreports
            .FirstOrDefaultAsync(r => r.Name == name);
    }

    public async Task AddAsync(Atreport entity)
    {
        if (entity.IsActive)
        {
            await context.Atreports
                .Where(r => r.IsActive)
                .ExecuteUpdateAsync(s => s.SetProperty(r => r.IsActive, false));
        }
        
        context.Atreports.Add(entity);
        await context.SaveChangesAsync();
    }

    public async Task UpdateAsync(Atreport entity)
    {
        if (entity.IsActive)
        {
            await context.Atreports
                .Where(r => r.ReportId != entity.ReportId && r.IsActive)
                .ExecuteUpdateAsync(s => s.SetProperty(r => r.IsActive, false));
        }

        context.Atreports.Update(entity);
        await context.SaveChangesAsync();
    }

    public async Task DeleteAsync(Atreport entity)
    {
        context.Atreports.Remove(entity);
        await context.SaveChangesAsync();
    }

    public async Task<(List<string> Columns, List<object[]> Rows)> ExecuteReportQueryAsync(string sql)
    {
        if (!sql.TrimStart().StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
            throw new InvalidOperationException("Only SELECT statements are allowed.");

        using var connection = (SqlConnection)context.Database.GetDbConnection();
        await connection.OpenAsync();

        using var command = new SqlCommand(sql, connection)
        {
            CommandType = CommandType.Text
        };

        using var reader = await command.ExecuteReaderAsync();

        var columns = Enumerable.Range(0, reader.FieldCount)
            .Select(reader.GetName)
            .ToList();

        var rows = new List<object[]>();
        while (await reader.ReadAsync())
        {
            var values = new object[reader.FieldCount];
            reader.GetValues(values);
            rows.Add(values);
        }

        return (columns, rows);
    }

    public async Task<bool> ValidateSqlAsync(string sql)
    {
        if (string.IsNullOrWhiteSpace(sql))
            return false;

        try
        {
            var connectionString = context.Database.GetConnectionString();
            if (string.IsNullOrEmpty(connectionString))
                throw new InvalidOperationException("Database connection string is not configured.");

            await using var connection = new SqlConnection(connectionString);
            await connection.OpenAsync();

            await using var command = connection.CreateCommand();
            command.CommandText = $"SET PARSEONLY ON; {sql}; SET PARSEONLY OFF;";
            await command.ExecuteNonQueryAsync();

            return true;
        }
        catch (Exception ex)
        {
            Console.WriteLine($"[ValidateSqlAsync] SQL validation failed: {ex.Message}");
            return false;
        }
    }

}
