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

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_IDataRecord_GetStream()
        {
            using (var connection = TestHelpers.GetTestDBConnection())
            {
                using (TransactionScope scope = 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);
                                    Assert2.AreSequenceEqual(pic, pic2);
                                }
                            }
                        }
                    }
                }
            }
        }

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

            using (TransactionScope scope = 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));
        }
    }
}