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

namespace excel_pajak_test;

[TestClass]
public sealed class DatabaseServiceTests
{
    private const string ValidConnectionString = "Server=localhost;Port=5432;Database=testdb;User Id=testuser;Password=testpass;";

    #region Connection String Validation Tests

    [TestMethod]
    public void ExecuteScalar_NullConnectionString_ThrowsArgumentException()
    {
        // Act & Assert
        Should.Throw<ArgumentException>(() => DatabaseService.ExecuteScalar("SELECT 1", null!));
    }

    [TestMethod]
    public void ExecuteScalar_EmptyConnectionString_ThrowsArgumentException()
    {
        // Act & Assert
        Should.Throw<ArgumentException>(() => DatabaseService.ExecuteScalar("SELECT 1", string.Empty));
    }

    #endregion

    #region Query Validation Tests

    [TestMethod]
    public void ExecuteScalar_NullQuery_ThrowsArgumentException()
    {
        // Act & Assert
        Should.Throw<ArgumentException>(() => DatabaseService.ExecuteScalar(null!, ValidConnectionString));
    }

    [TestMethod]
    public void ExecuteScalar_EmptyQuery_ThrowsArgumentException()
    {
        // Act & Assert
        Should.Throw<ArgumentException>(() => DatabaseService.ExecuteScalar(string.Empty, ValidConnectionString));
    }

    [TestMethod]
    public void ExecuteScalar_WhitespaceQuery_ThrowsArgumentException()
    {
        // Act & Assert
        Should.Throw<ArgumentException>(() => DatabaseService.ExecuteScalar("   ", ValidConnectionString));
    }

    #endregion

    #region Database Connection Tests

    [TestMethod]
    public void ExecuteScalar_InvalidConnection_ThrowsInvalidOperationException()
    {
        // Arrange
        var invalidConnection = "Server=invalidhost;Port=9999;Database=nonexistent;User Id=invalid;Password=invalid;";

        // Act & Assert
        var exception = Should.Throw<InvalidOperationException>(() => DatabaseService.ExecuteScalar("SELECT 1", invalidConnection));

        exception.Message.ShouldContain("Database error");
        exception.InnerException.ShouldNotBeNull();
    }

    #endregion

    #region Query Execution Tests (Integration - requires PostgreSQL)

    [TestMethod]
    [Ignore] // Requires database connection
    public void ExecuteScalar_ValidQuery_ReturnsResult()
    {
        // Act
        var result = DatabaseService.ExecuteScalar("SELECT 42", ValidConnectionString);

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

    [TestMethod]
    [Ignore] // Requires database connection
    public void ExecuteScalar_QueryReturnsNull_ReturnsNull()
    {
        // Act
        var result = DatabaseService.ExecuteScalar("SELECT NULL::text", ValidConnectionString);

        // Assert
        result.ShouldBeNull();
    }

    [TestMethod]
    [Ignore] // Requires database connection
    public void ExecuteScalar_EmptyResult_ReturnsNull()
    {
        // Act
        var result = DatabaseService.ExecuteScalar("SELECT 1 WHERE 1=0", ValidConnectionString);

        // Assert
        result.ShouldBeNull();
    }

    [TestMethod]
    [Ignore] // Requires database connection
    public void ExecuteScalar_QueryReturnsString_ReturnsString()
    {
        // Act
        var result = DatabaseService.ExecuteScalar("SELECT 'test value'", ValidConnectionString);

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

    [TestMethod]
    [Ignore] // Requires database connection
    public void ExecuteScalar_QueryReturnsInteger_ReturnsString()
    {
        // Act
        var result = DatabaseService.ExecuteScalar("SELECT 12345", ValidConnectionString);

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

    #endregion

    #region Error Handling Tests

    [TestMethod]
    [Ignore] // Requires database connection
    public void ExecuteScalar_InvalidSQL_ThrowsInvalidOperationException()
    {
        // Act & Assert
        var exception = Should.Throw<InvalidOperationException>(() => DatabaseService.ExecuteScalar("INVALID SQL SYNTAX", ValidConnectionString));

        exception.Message.ShouldMatch(".*(PostgreSQL error|Database error).*");
    }

    #endregion
}