using System;
using System.Linq;
using System.ComponentModel.DataAnnotations;
using System.Collections.Generic;
using System.Data;

using NServiceKit.Common.Utils;
using NServiceKit.DataAnnotations;
using NServiceKit.Common;
using System.Reflection;

using NServiceKit.OrmLite;
using NServiceKit.OrmLite.Firebird;


namespace TestExpressions
{
    /// <summary>An author.</summary>
	public class Author{

        /// <summary>Initializes a new instance of the TestExpressions.Author class.</summary>
		public Author(){}

        /// <summary>Gets or sets the identifier.</summary>
        /// <value>The identifier.</value>
		[AutoIncrement]
		[Alias("AuthorID")]
		public Int32 Id { get; set;}

        /// <summary>Gets or sets the name.</summary>
        /// <value>The name.</value>
		[Index(Unique = true)]
		[StringLength(40)]
		public string Name { get; set;}

        /// <summary>Gets or sets the Date/Time of the birthday.</summary>
        /// <value>The birthday.</value>
		public DateTime Birthday { get; set;}

        /// <summary>Gets or sets the Date/Time of the last activity.</summary>
        /// <value>The last activity.</value>
		public DateTime ? LastActivity  { get; set;}

        /// <summary>Gets or sets the earnings.</summary>
        /// <value>The earnings.</value>
		public Decimal? Earnings { get; set;}  

        /// <summary>Gets or sets a value indicating whether the active.</summary>
        /// <value>true if active, false if not.</value>
		public bool Active { get; set; } 

        /// <summary>Gets or sets the city.</summary>
        /// <value>The city.</value>
		[StringLength(80)]
		[Alias("JobCity")]
		public string City { get; set;}

        /// <summary>Gets or sets the comments.</summary>
        /// <value>The comments.</value>
		[StringLength(80)]
		[Alias("Comment")]
		public string Comments { get; set;}

        /// <summary>Gets or sets the rate.</summary>
        /// <value>The rate.</value>
		public Int16 Rate{ get; set;}
	}

    /// <summary>A main class.</summary>
	class MainClass
	{
        /// <summary>Main entry-point for this application.</summary>
        /// <param name="args">Array of command-line argument strings.</param>
		public static void Main (string[] args)
		{
			Console.WriteLine ("Hello World!");
			
			OrmLiteConfig.DialectProvider = new FirebirdOrmLiteDialectProvider();
			SqlExpressionVisitor<Author> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor<Author>();
									
			using (IDbConnection db =
			       "User=SYSDBA;Password=masterkey;Database=employee.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;".OpenDbConnection())
			{
				db.DropTable<Author>();
				db.CreateTable<Author>();
				db.DeleteAll<Author>();
				
				List<Author> authors = new List<Author>();
				authors.Add(new Author(){Name="Demis Bellot",Birthday= DateTime.Today.AddYears(-20),Active=true,Earnings= 99.9m,Comments="CSharp books", Rate=10, City="London"});
				authors.Add(new Author(){Name="Angel Colmenares",Birthday= DateTime.Today.AddYears(-25),Active=true,Earnings= 50.0m,Comments="CSharp books", Rate=5, City="Bogota"});
				authors.Add(new Author(){Name="Adam Witco",Birthday= DateTime.Today.AddYears(-20),Active=true,Earnings= 80.0m,Comments="Math Books", Rate=9, City="London"});
				authors.Add(new Author(){Name="Claudia Espinel",Birthday= DateTime.Today.AddYears(-23),Active=true,Earnings= 60.0m,Comments="Cooking books", Rate=10, City="Bogota"});
				authors.Add(new Author(){Name="Libardo Pajaro",Birthday= DateTime.Today.AddYears(-25),Active=true,Earnings= 80.0m,Comments="CSharp books", Rate=9, City="Bogota"});
				authors.Add(new Author(){Name="Jorge Garzon",Birthday= DateTime.Today.AddYears(-28),Active=true,Earnings= 70.0m,Comments="CSharp books", Rate=9, City="Bogota"});
				authors.Add(new Author(){Name="Alejandro Isaza",Birthday= DateTime.Today.AddYears(-20),Active=true,Earnings= 70.0m,Comments="Java books", Rate=0, City="Bogota"});
				authors.Add(new Author(){Name="Wilmer Agamez",Birthday= DateTime.Today.AddYears(-20),Active=true,Earnings= 30.0m,Comments="Java books", Rate=0, City="Cartagena"});
				authors.Add(new Author(){Name="Rodger Contreras",Birthday= DateTime.Today.AddYears(-25),Active=true,Earnings= 90.0m,Comments="CSharp books", Rate=8, City="Cartagena"});
				authors.Add(new Author(){Name="Chuck Benedict",Birthday= DateTime.Today.AddYears(-22),Active=true,Earnings= 85.5m,Comments="CSharp books", Rate=8, City="London"});
				authors.Add(new Author(){Name="James Benedict II",Birthday= DateTime.Today.AddYears(-22),Active=true,Earnings= 85.5m,Comments="Java books", Rate=5, City="Berlin"});
				authors.Add(new Author(){Name="Ethan Brown",Birthday= DateTime.Today.AddYears(-20),Active=true,Earnings= 45.0m,Comments="CSharp books", Rate=5, City="Madrid"});
				authors.Add(new Author(){Name="Xavi Garzon",Birthday= DateTime.Today.AddYears(-22),Active=true,Earnings= 75.0m,Comments="CSharp books", Rate=9, City="Madrid"});
				authors.Add(new Author(){Name="Luis garzon",Birthday= DateTime.Today.AddYears(-22),Active=true,Earnings= 85.0m,Comments="CSharp books", Rate=10, City="Mexico"});
				
				db.InsertAll(authors);
				
				
				// lets start !
				
				// select authors born 20 year ago
				int year = DateTime.Today.AddYears(-20).Year;
				int expected=5;
				
				ev.Where(rn=> rn.Birthday>=new DateTime(year, 1,1) && rn.Birthday<=new DateTime(year, 12,31));
				List<Author> result=db.Select(ev);
				Console.WriteLine(ev.WhereExpression);
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
				result = db.Select<Author>(qry => qry.Where(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= new DateTime(year, 12, 31)));
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count);
				result = db.Select<Author>(rn => rn.Birthday >= new DateTime(year, 1, 1) && rn.Birthday <= new DateTime(year, 12, 31));
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected == result.Count);

				
				// select authors from London, Berlin and Madrid : 6
				expected=6;
				//Sql.In can take params object[]
				ev.Where(rn=> Sql.In( rn.City, new object[]{"London", "Madrid", "Berlin"}) );
				result=db.Select(ev);
				Console.WriteLine(ev.WhereExpression);
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
				
				// select authors from Bogota and Cartagena : 7
				expected=7;
				//... or Sql.In can  take IList<Object>
				List<Object> cities= new List<Object>();
				cities.Add("Bogota");
				cities.Add("Cartagena");
				ev.Where(rn => Sql.In(rn.City, cities ));
				result=db.Select(ev);
				Console.WriteLine(ev.WhereExpression);
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
				
				
				// select authors which name starts with A
				expected=3;
				ev.Where(rn=>  rn.Name.StartsWith("A") );
				result=db.Select(ev);
				Console.WriteLine(ev.WhereExpression);
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
				
				// select authors which name ends with Garzon o GARZON o garzon ( no case sensitive )
				expected=3;
				ev.Where(rn=>  rn.Name.ToUpper().EndsWith("GARZON") );
				result=db.Select(ev);
				Console.WriteLine(ev.WhereExpression);
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
				
				// select authors which name ends with garzon ( no case sensitive )
				expected=3;
				ev.Where(rn=>  rn.Name.EndsWith("garzon") );
				result=db.Select(ev);
				Console.WriteLine(ev.WhereExpression);
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
				
				
				// select authors which name contains  Benedict 
				expected=2;
				ev.Where(rn=>  rn.Name.Contains("Benedict") );
				result=db.Select(ev);
				Console.WriteLine(ev.WhereExpression);
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
				
				
				// select authors with Earnings <= 50 
				expected=3;
				ev.Where(rn=>  rn.Earnings<=50 );
				result=db.Select(ev);
				Console.WriteLine(ev.WhereExpression);
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
				
				// select authors with Rate = 10 and city=Mexio 
				expected=1;
				ev.Where(rn=>  rn.Rate==10 && rn.City=="Mexico");
				result=db.Select(ev);
				Console.WriteLine(ev.WhereExpression);
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
			
				//  enough selecting, lets update;
				// set Active=false where rate =0
				expected=2;
				ev.Where(rn=>  rn.Rate==0 ).Update(rn=> rn.Active);
				var rows = db.UpdateOnly( new Author(){ Active=false }, ev);
				Console.WriteLine(ev.WhereExpression);
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, rows, expected==rows);
			
				// insert values  only in Id, Name, Birthday, Rate and Active fields 
				expected=4;
				ev.Insert(rn =>new { rn.Id, rn.Name, rn.Birthday, rn.Active, rn.Rate} );
				db.InsertOnly( new Author(){Active=false, Rate=0, Name="Victor Grozny", Birthday=DateTime.Today.AddYears(-18)   }, ev);
				db.InsertOnly( new Author(){Active=false, Rate=0, Name="Ivan Chorny", Birthday=DateTime.Today.AddYears(-19)   }, ev);
				ev.Where(rn=> !rn.Active);
				result=db.Select(ev);
				Console.WriteLine(ev.WhereExpression);
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
				
				//update comment for City == null 
				expected=2;
				ev.Where( rn => rn.City==null ).Update(rn=> rn.Comments);
				rows=db.UpdateOnly(new Author(){Comments="No comments"}, ev);
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, rows, expected==rows);
				
				// delete where City is null 
				expected=2;
				rows = db.Delete( ev);
				Console.WriteLine(ev.WhereExpression);
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, rows, expected==rows);
			
				
				//   lets select  all records ordered by Rate Descending and Name Ascending
				expected=14;
				ev.Where().OrderBy(rn=> new{ at=Sql.Desc(rn.Rate), rn.Name }); // clear where condition
				result=db.Select(ev);
				Console.WriteLine(ev.WhereExpression);
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
				Console.WriteLine(ev.OrderByExpression);
				var author = result.FirstOrDefault();
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Claudia Espinel", author.Name, "Claudia Espinel"==author.Name);
				
				// select  only first 5 rows ....
				
				expected=5;
				ev.Limit(5); // note: order is the same as in the last sentence
				result=db.Select(ev);
				Console.WriteLine(ev.WhereExpression);
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
					
					
				// lets select only Name and City (name will be "UPPERCASED" )
			
				ev.Select(rn=> new { at= Sql.As( rn.Name.ToUpper(), "Name" ), rn.City} );
				Console.WriteLine(ev.SelectExpression);
				result=db.Select(ev);
				author = result.FirstOrDefault();
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper()==author.Name);
				
				//paging :
				ev.Limit(0,4);// first page, page size=4;
				result=db.Select(ev);
				author = result.FirstOrDefault();
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Claudia Espinel".ToUpper(), author.Name, "Claudia Espinel".ToUpper()==author.Name);
				
				ev.Limit(4,4);// second page
				result=db.Select(ev);
				author = result.FirstOrDefault();
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Jorge Garzon".ToUpper(), author.Name, "Jorge Garzon".ToUpper()==author.Name);
				
				ev.Limit(8,4);// third page
				result=db.Select(ev);
				author = result.FirstOrDefault();
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", "Rodger Contreras".ToUpper(), author.Name, "Rodger Contreras".ToUpper()==author.Name);
				
			
				// select distinct..
				ev.Limit(); // clear limit
				ev.SelectDistinct(r=>r.City);
				expected=6;
				result=db.Select(ev);	
				Console.WriteLine("Expected:{0} ; Selected:{1}, OK? {2}", expected, result.Count, expected==result.Count);
				
				
				Console.ReadLine();
				Console.WriteLine("Press Enter to continue");
				
			}
			
			Console.WriteLine ("This is The End my friend!");
		}
	}
}
