using System.Data;
using NUnit.Framework;
using NSubstitute;

namespace YourNamespace.Tests
{
    [TestFixture]
    public class DatabaseShould
    {
        private IDbConnection _connection;
        private IDbTransaction _transaction;
        private IDbCommand _command;

        [SetUp]
        public void Setup()
        {
            _connection = Substitute.For<IDbConnection>();
            _transaction = Substitute.For<IDbTransaction>();
            _command = Substitute.For<IDbCommand>();
            
            _connection.BeginTransaction().Returns(_transaction);
            _connection.CreateCommand().Returns(_command);
        }

        [Test]
        public void ExecuteQuery_ReturnExpectedResults()
        {
            var reader = Substitute.For<IDataReader>();
            reader.Read().Returns(true, true, false);
            reader[0].Returns("Value1", "Value2");
            _command.ExecuteReader().Returns(reader);
            var sut = CreateSut();

            var results = sut.ExecuteQuery("SELECT * FROM Table");

            Assert.That(results.Count, Is.EqualTo(2));
            Assert.That(results[0], Is.EqualTo("Value1"));
            Assert.That(results[1], Is.EqualTo("Value2"));
        }

        [Test]
        public void CommitTransaction_WhenOperationSucceeds()
        {
            var sut = CreateSut();

            sut.ExecuteInTransaction(() => { _command.ExecuteNonQuery(); });

            _transaction.Received(1).Commit();
        }

        [Test]
        public void RollbackTransaction_WhenOperationFails()
        {
            _command.When(c => c.ExecuteNonQuery()).Do(_ => throw new System.Exception("DB Error"));
            var sut = CreateSut();

            Assert.Throws<System.Exception>(() => sut.ExecuteInTransaction(() => _command.ExecuteNonQuery()));

            _transaction.Received(1).Rollback();
        }

        [Test]
        public void UseCorrectParameters_WhenExecutingCommand()
        {
            var parameter = Substitute.For<IDbDataParameter>();
            _command.CreateParameter().Returns(parameter);
            var sut = CreateSut();

            sut.ExecuteWithParameter("INSERT INTO Table (Col) VALUES (@val)", "@val", "TestValue");

            _command.Received(1).Parameters.Add(Arg.Is<IDbDataParameter>(p => 
                p.ParameterName == "@val" && (string)p.Value == "TestValue"));
        }

        [Test]
        public void ReturnRowCount_WhenExecutingNonQuery()
        {
            _command.ExecuteNonQuery().Returns(5);
            var sut = CreateSut();

            var count = sut.ExecuteNonQuery("DELETE FROM Table");

            Assert.That(count, Is.EqualTo(5));
        }

        [Test]
        public void HandleNullValues_InQueryResults()
        {
            var reader = Substitute.For<IDataReader>();
            reader.Read().Returns(true, false);
            reader.IsDBNull(0).Returns(true);
            _command.ExecuteReader().Returns(reader);
            var sut = CreateSut();

            var results = sut.ExecuteQuery("SELECT Col FROM Table");

            Assert.That(results.Count, Is.EqualTo(1));
            Assert.That(results[0], Is.Null);
        }

        [Test]
        public void CloseConnection_AfterOperation()
        {
            _connection.State.Returns(ConnectionState.Open);
            var sut = CreateSut();

            sut.CloseConnection();

            _connection.Received(1).Close();
        }

        [Test]
        public void ThrowException_WhenConnectionFails()
        {
            _connection.When(c => c.Open()).Do(_ => throw new System.Exception("Connection failed"));
            var sut = CreateSut();

            Assert.Throws<System.Exception>(() => sut.OpenConnection());
        }

        [Test]
        public void ExecuteScalar_ReturnCorrectValue()
        {
            _command.ExecuteScalar().Returns(42);
            var sut = CreateSut();

            var result = sut.ExecuteScalar("SELECT COUNT(*) FROM Table");

            Assert.That(result, Is.EqualTo(42));
        }

        private DatabaseService CreateSut()
        {
            return new DatabaseService(_connection);
        }

        [TearDown]
        public void TearDown()
        {
            _connection?.Dispose();
            _command?.Dispose();
        }
    }

    public class DatabaseService
    {
        private readonly IDbConnection _connection;

        public DatabaseService(IDbConnection connection)
        {
            _connection = connection;
        }

        public void OpenConnection() => _connection.Open();

        public void CloseConnection()
        {
            if (_connection.State == ConnectionState.Open)
                _connection.Close();
        }

        public List<object> ExecuteQuery(string sql)
        {
            var results = new List<object>();
            using (var command = _connection.CreateCommand())
            {
                command.CommandText = sql;
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        results.Add(reader.IsDBNull(0) ? null : reader[0]);
                    }
                }
            }
            return results;
        }

        public int ExecuteNonQuery(string sql)
        {
            using (var command = _connection.CreateCommand())
            {
                command.CommandText = sql;
                return command.ExecuteNonQuery();
            }
        }

        public object ExecuteScalar(string sql)
        {
            using (var command = _connection.CreateCommand())
            {
                command.CommandText = sql;
                return command.ExecuteScalar();
            }
        }

        public void ExecuteInTransaction(Action operation)
        {
            using (var transaction = _connection.BeginTransaction())
            {
                try
                {
                    operation();
                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        public void ExecuteWithParameter(string sql, string paramName, object value)
        {
            using (var command = _connection.CreateCommand())
            {
                command.CommandText = sql;
                var parameter = command.CreateParameter();
                parameter.ParameterName = paramName;
                parameter.Value = value;
                command.Parameters.Add(parameter);
                command.ExecuteNonQuery();
            }
        }
    }
}