using Microsoft.Extensions.Configuration;
using Npgsql;

namespace excel_pajak.Services;

public static class DatabaseService
{


    public static string? ExecuteScalar(string query, string connectionString)
    {
        if (string.IsNullOrWhiteSpace(connectionString))
        {
            throw new ArgumentException("Connection string cannot be null or empty.", nameof(connectionString));
        }

        if (string.IsNullOrWhiteSpace(query))
        {
            throw new ArgumentException("Query cannot be null or empty.", nameof(query));
        }

        try
        {
            using var connection = new NpgsqlConnection(connectionString);
            connection.Open();

            using var command = new NpgsqlCommand(query, connection);
            var result = command.ExecuteScalar();
            connection.Close();
            // Handle empty result sets (no rows)
            if (result == null || result == DBNull.Value)
            {
                return null;
            }

            return result.ToString();
        }
        catch (Npgsql.PostgresException ex)
        {
            // Re-throw PostgreSQL exceptions with context
            throw new InvalidOperationException(
                $"PostgreSQL error executing query: {ex.MessageText ?? ex.Message}",
                ex);
        }
        catch (NpgsqlException ex)
        {
            // Re-throw Npgsql exceptions with context
            throw new InvalidOperationException(
                $"Database error executing query: {ex.Message}",
                ex);
        }
    }

}