using System;
using System.Collections.Generic;
using System.Data;
using NUnit.Framework;
using ServiceStack.DataAnnotations;
using ServiceStack.Text;

namespace ServiceStack.OrmLite.Tests.Issues
{
    public class ParentTbl
    {
        [AutoIncrement]
        public long Id { get; set; }

        public DateTime? DateMarried { get; set; }

        [Reference]
        public List<ChildTbl> Childs { get; set; } = new List<ChildTbl>();

        public DateTime? DateOfBirth { get; set; }
    }

    public class ChildTbl
    {
        [AutoIncrement]
        public long Id { get; set; }

        [References(typeof(ParentTbl))]
        public long ParentId { get; set; }

        public DateTime? DateOfDeath { get; set; }
    }

    [TestFixtureOrmLite]
    public class DynamicDataIssue : OrmLiteProvidersTestBase
    {
        public DynamicDataIssue(DialectContext context) : base(context) {}

        private static void InitTables(IDbConnection db)
        {
            db.DropTable<ChildTbl>();
            db.DropTable<ParentTbl>();
            db.CreateTable<ParentTbl>();
            db.CreateTable<ChildTbl>();
        }

        [Test] 
        public void Can_select_null_DateTime_in_nullable_Tuple()
        {
            var date = new DateTime(2000,1,1);
            
            using (var db = OpenDbConnection())
            {
                InitTables(db);

                db.Insert(new ParentTbl { DateOfBirth = date });
                db.Insert(new ParentTbl { DateOfBirth = null });

                db.Select<ParentTbl>();
                db.Select<(int, DateTime?)>(db.From<ParentTbl>());
                db.Select<(int, DateTime?)>(db.From<ParentTbl>().Select(x => new { x.Id, x.DateOfBirth }));
                db.Select<(int, DateTime)>(db.From<ParentTbl>().Select(x => new { x.Id, x.DateOfBirth }));
            }
        }

        [Test]
        public void Complex_example()
        {
            using (var db = OpenDbConnection())
            {
                InitTables(db);

                var parentTbl = new ParentTbl { DateMarried = DateTime.Today };
                parentTbl.Id = db.Insert(parentTbl, selectIdentity:true);
                db.Insert(new ChildTbl { ParentId = parentTbl.Id, DateOfDeath = null });

                var q = db.From<ChildTbl>()
                    .RightJoin<ChildTbl, ParentTbl>((c, p) => c.ParentId == p.Id || c.Id == null)
                    .GroupBy<ParentTbl>((p) => new { p.Id })
                    .Select<ChildTbl, ParentTbl>((c, p) => new { p.Id, MaxKeyValuePeriodEnd = Sql.Max(c.DateOfDeath) });

                var theSqlStatement = q.ToSelectStatement();
                
                theSqlStatement.Print();
            }
        }

    }
}