using Microsoft.Extensions.Configuration;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using excel_pajak.Services;
using Shouldly;
using System;

namespace excel_pajak_test;

[TestClass]
public sealed class DatabaseServiceIntegrationTests
{
    private static string? _connectionString;

    [ClassInitialize]
    public static void ClassInitialize(TestContext context)
    {
        // Load configuration from appsettings.json
        var builder = new ConfigurationBuilder()
            .SetBasePath(AppContext.BaseDirectory)
            .AddJsonFile("appsettings.json", optional: false);

        var configuration = builder.Build();

        _connectionString = configuration["ConnectionStrings:DefaultConnection"];
        if (string.IsNullOrWhiteSpace(_connectionString))
        {
            throw new InvalidOperationException("Connection string is missing in test appsettings.json");
        }
    }

    [TestMethod]
    public void Integration_ValidQuery_ReturnsResult()
    {
        // Act
        var result = DatabaseService.ExecuteScalar("SELECT 42", _connectionString!);

        // Assert
        result.ShouldBe("42");
    }

    [TestMethod]
    public void Integration_QueryReturnsNull_ReturnsNull()
    {
        // Act
        var result = DatabaseService.ExecuteScalar("SELECT NULL::text", _connectionString!);

        // Assert
        result.ShouldBeNull();
    }

    [TestMethod]
    public void Integration_EmptyResult_ReturnsNull()
    {
        // Act
        var result = DatabaseService.ExecuteScalar("SELECT 1 WHERE 1=0", _connectionString!);

        // Assert
        result.ShouldBeNull();
    }

    [TestMethod]
    public void Integration_QueryReturnsString_ReturnsString()
    {
        // Act
        var result = DatabaseService.ExecuteScalar("SELECT 'test value'", _connectionString!);

        // Assert
        result.ShouldBe("test value");
    }

    [TestMethod]
    public void Integration_QueryReturnsInteger_ReturnsString()
    {
        // Act
        var result = DatabaseService.ExecuteScalar("SELECT 12345", _connectionString!);

        // Assert
        result.ShouldBe("12345");
    }

    [TestMethod]
    public void Integration_QueryReturnsTimestamp_ReturnsString()
    {
        // Act
        var result = DatabaseService.ExecuteScalar("SELECT NOW()", _connectionString!);

        // Assert
        result.ShouldNotBeNull();
        result.ShouldBeOfType<string>();
        Console.WriteLine($"Database timestamp: {result}");
    }

    [TestMethod]
    public void Integration_QueryVersion_ReturnsVersionString()
    {
        // Act
        var result = DatabaseService.ExecuteScalar("SELECT version()", _connectionString!);

        // Assert
        result.ShouldNotBeNull();
        result.ShouldStartWith("PostgreSQL");
        Console.WriteLine($"PostgreSQL version: {result}");
    }

    [TestMethod]
    public void Integration_InvalidSQL_ThrowsInvalidOperationException()
    {
        // Act & Assert
        var exception = Should.Throw<InvalidOperationException>(() => DatabaseService.ExecuteScalar("INVALID SQL SYNTAX", _connectionString!));

        exception.Message.ShouldMatch(".*(PostgreSQL error|Database error).*");
        Console.WriteLine($"Expected error caught: {exception.Message}");
    }

    [TestMethod]
    public void Integration_QueryToNonExistentTable_ThrowsInvalidOperationException()
    {
        // Act & Assert
        var exception = Should.Throw<InvalidOperationException>(() => DatabaseService.ExecuteScalar("SELECT * FROM nonexistent_table_xyz LIMIT 1", _connectionString!));

        exception.Message.ShouldMatch(".*(Database error|PostgreSQL error).*");
        exception.InnerException.ShouldNotBeNull();
        Console.WriteLine($"Expected error caught: {exception.Message}");
    }

    [TestMethod]
    public void Integration_QueryCurrentDatabase_ReturnsDatabaseName()
    {
        // Act
        var result = DatabaseService.ExecuteScalar("SELECT current_database()", _connectionString!);

        // Assert
        result.ShouldNotBeNull();
        Console.WriteLine($"Current database: {result}");
    }

    [TestMethod]
    public void Integration_QueryCurrentUser_ReturnsUserName()
    {
        // Act
        var result = DatabaseService.ExecuteScalar("SELECT current_user", _connectionString!);

        // Assert
        result.ShouldNotBeNull();
        Console.WriteLine($"Current user: {result}");
    }
}