using System;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Transactions;
using Developpez.Dotnet.Data;
using NUnit.Framework;

namespace Developpez.Dotnet.Tests.Data
{
    [TestFixture]
    public class DataExtensionsTests
    {
        #region Méthodes utilitaires

        private static DbCommand GetPersonsQuery(DbConnection connection)
        {
            DbCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM Persons";
            return command;
        }

        #endregion

        [Test]
        public void Test_AsEnumerable_And_Field()
        {
            using (var connection = TestHelpers.GetTestDBConnection())
            {
                connection.Open();
                using (var command = GetPersonsQuery(connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        foreach (var record in reader.AsEnumerable())
                        {
                            // Type valeur, colonne non nullable
                            Guid id = record.Field<Guid>("Id");

                            // Type référence, colonne non nullable
                            string lastName = record.Field<string>("LastName");

                            // Type référence, colonne nullable
                            string firstName = record.Field<string>("FirstName");

                            // Type valeur, colonne nullable
                            DateTime? dateOfBirth = record.Field<DateTime?>("DateOfBirth");

                            // Type valeur, colonne nullable
                            int? luckyNumber = record.Field<int?>("LuckyNumber");

                            // Type valeur, colonne nullable, avec conversion
                            long longLuckyNumber = record.Field<long?>("LuckyNumber", true) ?? 0;
                        }
                    }
                }
            }
        }

        [Test]
        public void Test_Select()
        {
            using (var connection = TestHelpers.GetTestDBConnection())
            {
                connection.Open();
                using (var command = GetPersonsQuery(connection))
                {
                    var results =
                        from record in command
                        select new
                            {
                                Id = record.Field<Guid>("Id"),
                                LastName = record.Field<string>("LastName"),
                                DateOfBirth = record.Field<DateTime?>("DateOfBirth"),
                                LuckyNumber = record.Field<int?>("LuckyNumber"),
                                LongLuckyNumber = record.Field<long?>("LuckyNumber", true) ?? 0
                            };

                    Assert.AreEqual(2, results.Count());
                }
            }
        }

        [Test]
        public void Test_Select_WithParams()
        {
            using (var connection = TestHelpers.GetTestDBConnection())
            {
                connection.Open();
                using (var command = connection.CreateCommand("SELECT * FROM Persons WHERE Id = @id"))
                {
                    var parameters = new { Id = new Guid("51c7985a-23fb-4a09-9189-baf197a68707") };
                    var results = command.SetParameters(parameters)
                                         .Select(r => new
                                         {
                                             Id = r.Field<Guid>("Id"),
                                             LastName = r.Field<string>("LastName"),
                                             FirstName = r.Field<string>("FirstName"),
                                             DateOfBirth = r.Field<DateTime?>("DateOfBirth"),
                                             LuckyNumber = r.Field<int?>("LuckyNumber"),
                                         }).ToArray();
                    Assert.AreEqual(1, results.Length);
                    var result = results[0];
                    Assert.AreEqual("Escort", result.LastName);
                    Assert.AreEqual("Ford", result.FirstName);
                    Assert.AreEqual(null, result.DateOfBirth);
                    Assert.AreEqual(42, result.LuckyNumber);
                }
            }
        }

        [Test]
        public void Test_IDbCommand_SetParameters()
        {
            var items = new[]
            {
                new
                {
                    Id = Guid.NewGuid(),
                    LastName = "Morrison",
                    FirstName = "Jim",
                    DateOfBirth = new DateTime(1943, 12, 8),
                    LuckyNumber = 7
                },
                new
                {
                    Id = Guid.NewGuid(),
                    LastName = "Marley",
                    FirstName = "Bob",
                    DateOfBirth = new DateTime(1945, 2, 6),
                    LuckyNumber = 13
                }
            };

            using (var connection = TestHelpers.GetTestDBConnection())
            {
                using (new TransactionScope())
                {
                    connection.Open();

                    // Clear table
                    using (var command = connection.CreateCommand("DELETE FROM Persons"))
                    {
                        command.ExecuteNonQuery();
                    }

                    // Insert data
                    using (var command = connection.CreateCommand())
                    {
                        command.CommandText =
                            "INSERT INTO Persons(Id, LastName, FirstName, DateOfBirth, LuckyNumber) VALUES (@id, @lastName, @firstName, @dateOfBirth, @luckyNumber)";

                        foreach (var item in items)
                        {
                            command.SetParameters(item).ExecuteNonQuery();
                        }
                    }

                    // Check results
                    using (var command = connection.CreateCommand("SELECT * FROM Persons"))
                    {
                        var results =
                            command.Select(r => new
                            {
                                Id = r.GetGuid("Id"),
                                LastName = r.GetString("LastName"),
                                FirstName = r.GetString("FirstName"),
                                DateOfBirth = r.GetDateTime("DateOfBirth"),
                                LuckyNumber = r.GetInt32("LuckyNumber")
                            })
                            .ToArray();

                        for (int i = 0; i < results.Length; i++)
                        {
                            Assert.AreEqual(items[i].Id, results[i].Id);
                            Assert.AreEqual(items[i].LastName, results[i].LastName);
                            Assert.AreEqual(items[i].FirstName, results[i].FirstName);
                            Assert.AreEqual(items[i].DateOfBirth, results[i].DateOfBirth);
                            Assert.AreEqual(items[i].LuckyNumber, results[i].LuckyNumber);
                        }
                    }
                }
            }
        }

        [Test]
        public void Test_IDbConnection_Execute()
        {
            var items = new[]
            {
                new
                {
                    Id = Guid.NewGuid(),
                    LastName = "Morrison",
                    FirstName = "Jim",
                    DateOfBirth = new DateTime(1943, 12, 8),
                    LuckyNumber = 7
                },
                new
                {
                    Id = Guid.NewGuid(),
                    LastName = "Marley",
                    FirstName = "Bob",
                    DateOfBirth = new DateTime(1945, 2, 6),
                    LuckyNumber = 13
                }
            };

            using (var connection = TestHelpers.GetTestDBConnection())
            {
                using (new TransactionScope())
                {
                    connection.Open();

                    // Clear table
                    using (var command = connection.CreateCommand("DELETE FROM Persons"))
                    {
                        command.ExecuteNonQuery();
                    }

                    // Insert data
                    const string sql = "INSERT INTO Persons(Id, LastName, FirstName, DateOfBirth, LuckyNumber) VALUES (@id, @lastName, @firstName, @dateOfBirth, @luckyNumber)";
                    foreach (var item in items)
                    {
                        int count = connection.Execute(sql, item);
                        Assert.AreEqual(1, count);
                    }

                    // Check results
                    using (var command = connection.CreateCommand("SELECT * FROM Persons"))
                    {
                        var results =
                            command.Select(r => new
                            {
                                Id = r.GetGuid("Id"),
                                LastName = r.GetString("LastName"),
                                FirstName = r.GetString("FirstName"),
                                DateOfBirth = r.GetDateTime("DateOfBirth"),
                                LuckyNumber = r.GetInt32("LuckyNumber")
                            })
                            .ToArray();

                        for (int i = 0; i < results.Length; i++)
                        {
                            Assert.AreEqual(items[i].Id, results[i].Id);
                            Assert.AreEqual(items[i].LastName, results[i].LastName);
                            Assert.AreEqual(items[i].FirstName, results[i].FirstName);
                            Assert.AreEqual(items[i].DateOfBirth, results[i].DateOfBirth);
                            Assert.AreEqual(items[i].LuckyNumber, results[i].LuckyNumber);
                        }
                    }
                }
            }
        }

        [Test]
        public void Test_IDbConnection_Query()
        {
            using (var connection = TestHelpers.GetTestDBConnection())
            {
                connection.Open();
                string sql = "SELECT * FROM Persons WHERE Id = @id";
                var parameters = new { Id = new Guid("51c7985a-23fb-4a09-9189-baf197a68707") };

                foreach (var result in connection.Query(sql, parameters))
                {
                    Assert.AreEqual("Escort", result.GetString("LastName"));
                    Assert.AreEqual("Ford", result.GetString("FirstName"));
                    Assert.AreEqual(null, result.Field<DateTime?>("DateOfBirth"));
                    Assert.AreEqual(42, result.GetInt32("LuckyNumber"));
                    return;
                }
                Assert.Fail("No results, expected at least one");
            }
        }

        [Test]
        public void Test_IDbConnection_QueryScalar()
        {
            using (var connection = TestHelpers.GetTestDBConnection())
            {
                connection.Open();
                string sql = "SELECT LuckyNumber FROM Persons WHERE Id = @id";
                var parameters = new { Id = new Guid("51c7985a-23fb-4a09-9189-baf197a68707") };
                int result = (int)connection.QueryScalar(sql, parameters);
                Assert.AreEqual(42, result);
            }
        }

        [Test]
        public void Test_IDataRecord_GetStream()
        {
            using (var connection = TestHelpers.GetTestDBConnection())
            {
                using (new TransactionScope())
                {
                    connection.Open();

                    byte[] pic = new byte[2000];
                    Random random = new Random();
                    random.NextBytes(pic);

                    using (var command = connection.CreateCommand())
                    {
                        command.CommandText = "INSERT INTO Pictures (Id, Name, Description, Picture) VALUES (@id, @name, @description, @picture)";
                        command.AddParameter("@id", DbType.Guid).Value = Guid.NewGuid();
                        command.AddParameter("@name", DbType.String).Value = "Test";
                        command.AddParameter("@description", DbType.String).Value = "Test";
                        command.AddParameter("@picture", DbType.Binary).Value = pic;
                        command.ExecuteNonQuery();
                    }

                    using (var command = connection.CreateCommand())
                    {
                        command.CommandText = "SELECT * FROM Pictures";
                        using (DbDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                using (Stream stream = reader.GetStream("Picture"))
                                {
                                    byte[] pic2 = new byte[stream.Length];
                                    stream.Read(pic2, 0, pic2.Length);
                                    CollectionAssert.AreEqual(pic, pic2);
                                }
                            }
                        }
                    }
                }
            }
        }

        [Test]
        public void Test_DbDataAdapter_AddRowUpdateEventHandler()
        {
            DataTable table = new DataTable();
            var factory = TestHelpers.GetTestDbFactory();

            using (new TransactionScope())
            {
                using (var connection = TestHelpers.GetTestDBConnection())
                using (var command = GetPersonsQuery(connection))
                using (var adapter = factory.CreateDataAdapter())
                {
                    adapter.SelectCommand = command;
                    var builder = factory.CreateCommandBuilder();
                    builder.DataAdapter = adapter;

                    bool updatingHandlerCalled = false;
                    bool updatedHandlerCalled = false;

                    adapter.AddRowUpdatingHandler((sender, e) => updatingHandlerCalled = true);
                    adapter.AddRowUpdatedHandler((sender, e) => updatedHandlerCalled = true);

                    connection.Open();
                    adapter.Fill(table);

                    table.Rows[0]["FirstName"] = "Joe";
                    table.Rows[1]["LuckyNumber"] = 7;
                    adapter.Update(table);
                    Assert.IsTrue(updatingHandlerCalled);
                    Assert.IsTrue(updatedHandlerCalled);

                    table.Rows.Clear();
                    adapter.Fill(table);
                }
            }
        }

        [Test]
        public void Test_ToDataTable()
        {
            var source = new[]
                             {
                                 new
                                     {
                                         Id = 1,
                                         Name = "Joe",
                                         Age = (int?) 42
                                     },
                                 new
                                     {
                                         Id = 2,
                                         Name = "Jack",
                                         Age = default(int?)
                                     },
                                 new
                                     {
                                         Id = 3,
                                         Name = default(string),
                                         Age = (int?) 35
                                     }
                             };

            var table = source.ToDataTable();
            Assert.AreEqual(table.Columns.Count, 3);
            Assert.AreEqual(table.Rows.Count, source.Length);
            
            Assert.AreEqual(table.Columns[0].ColumnName, "Id");
            Assert.AreEqual(table.Columns[0].DataType, typeof(int));
            Assert.IsFalse(table.Columns[0].AllowDBNull);
            
            Assert.AreEqual(table.Columns[1].ColumnName, "Name");
            Assert.AreEqual(table.Columns[1].DataType, typeof(string));
            Assert.IsTrue(table.Columns[1].AllowDBNull);

            Assert.AreEqual(table.Columns[2].ColumnName, "Age");
            Assert.AreEqual(table.Columns[2].DataType, typeof(int));
            Assert.IsTrue(table.Columns[2].AllowDBNull);

            for (int i = 0; i < source.Length; i++)
            {
                Assert.AreEqual(source[i].Id, table.Rows[i]["Id"]);

                if (source[i].Name != null)
                    Assert.AreEqual(source[i].Name, table.Rows[i]["Name"]);
                else
                    Assert.AreEqual(DBNull.Value, table.Rows[i]["Name"]);

                if (source[i].Age.HasValue)
                    Assert.AreEqual(source[i].Age.Value, table.Rows[i]["Age"]);
                else
                    Assert.AreEqual(DBNull.Value, table.Rows[i]["Age"]);
            }
        }

        [Test]
        public void Test_Sort()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("NAME", typeof(string));
            dt.Rows.Add(new object[] { 6, "VINCENT" });
            dt.Rows.Add(new object[] { 4, "VINC" });
            dt.Rows.Add(new object[] { 3, "VIN" });
            dt.Rows.Add(new object[] { 5, "VINCE" });
            dt.Rows.Add(new object[] { 1, "V" });
            dt.Rows.Add(new object[] { 2, "VI" });
            dt.Rows.Add(new object[] { 0, string.Empty });

            dt = dt.Sort("NAME");
            Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[0].ItemArray[1].ToString()));
            Assert.IsTrue(dt.Rows[6].ItemArray[1].ToString().Equals("VINCENT"));

            dt = dt.Sort(dt.Columns[0]);
            Assert.IsTrue(dt.Rows[0].ItemArray[0].Equals(0));
            Assert.IsTrue(dt.Rows[6].ItemArray[0].Equals(6));

            dt = dt.Sort("NAME", System.ComponentModel.ListSortDirection.Descending);
            Assert.IsTrue(dt.Rows[0].ItemArray[1].ToString().Equals("VINCENT"));
            Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[6].ItemArray[1].ToString()));

            dt = dt.Sort(dt.Columns[0], System.ComponentModel.ListSortDirection.Descending);
            Assert.IsTrue(dt.Rows[0].ItemArray[0].Equals(6));
            Assert.IsTrue(dt.Rows[6].ItemArray[0].Equals(0));
        }
    }
}