using SimpleStack.Orm;
using SimpleStack.Orm.SqlServer;

namespace Test{

   public class sample{

      [Alias("dogs")]
      public class Dog{
         [PrimaryKey()]
         [AutoIncrement()]
         public int Id{get; set;}
         public string Name{get; set;}
         [Alias("birth_date")]
         public DateTime? BirthDate{get; set;}
         public decimal Weight{get; set;}
         public string Breed{get; set;}
      }

      var factory = new OrmConnectionFactory(new SqlServerDialectProvider(), "server=...");
      using (var conn = factory.OpenConnection())
      {
         conn.CreateTable<Dog>();

         // INSERT INTO "dogs" ("Id", "Name", "birth_date", "Weight", "Breed" ) VALUES (@p_0, @p_1, @p_2, @p_3, @p_4)
         conn.Insert(new Dog{Name="Snoopy", BirthDate = new DateTime(1950,10,01), Weight=25.4});
         conn.Insert(new Dog{Name="Rex", Weight=45.6});
         conn.Insert(new Dog{Name="Popol", BirthDate = new DateTime(1918,09,13), Weight=2});

         // SELECT "Id", "Name", "birth_date" AS BirthDate, "Weight", "Breed"
         // FROM "dogs"
         // WHERE ("Id" = @p_0)
         // ORDER BY 1 -- ORDER BY is mandatory to use OFFSET and FETCH clause in SQLServer
         // OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
         var rex = conn.First<Dog>(x => Id == 2);

         rex.BirthDate = new DateTime(1994,11,10);

         // UPDATE "dogs" SET "Name"=@p_0, "birth_date"=@p_1, "Weight"=@p_2, "Breed"=@p_3 WHERE "Id"=@p_4
         conn.Update(rex);

         // DELETE FROM "dogs" WHERE ("Name" = @p_0)
         conn.DeleteAll<Dog>(x => x.Name == "Popol");

         // SELECT "Name", "Breed", "Weight"
         // FROM "dogsbackup"
         // WHERE (DATEPART(year,"birth_date") = @p_0) --will be specific depending on database
         // ORDER BY "Breed" ASC,"Weight" DESC
         conn.Select<Dog>(x => {
             x.From("dogsbackup");                         // Change From clause
             x.Select(y => new {y.Name,y.Breed,y.Weight}); // Only return some fields
             x.Where(y => y.BirthDate.Value.Year == 2019);
             x.OrderBy(y => y.Breed)
              .ThenByDescending(y => y.Weight);
         });

         // SELECT AVG(Weight) AS Weight
         // FROM "dogs"
         conn.GetScalar<Dog, decimal>(x => Sql.Avg(x.Weight))
      }
   }
}