namespace MileageTraker.Web.Migrations
{
    using System.Data.Entity.Migrations;
    
    public partial class AddVehiclePreviousLog : DbMigration
    {
        public override void Up()
        {
            AddColumn("Log", "VehiclePreviousLogId", c => c.Int());
            AddForeignKey("Log", "VehiclePreviousLogId", "Log", "LogId");
            CreateIndex("Log", "VehiclePreviousLogId");
	        var populateSql =
				@"WITH summary AS (
					SELECT
						ROW_NUMBER() OVER (
							PARTITION BY l.LogId 
							ORDER BY pl.EndOdometer DESC, pl.Created DESC) AS rk,
						l.LogId, pl.LogId as PreviousLogId
					FROM
						[Log] as l,
						[Log] as pl
					WHERE
						pl.VehicleId = l.VehicleId
						AND ((pl.[Date] <= l.[Date]
								AND pl.EndOdometer < l.EndOdometer)
							OR (
								pl.EndOdometer = l.EndOdometer
								AND pl.Created < l.Created
							))
				)
				UPDATE [Log]
				SET [Log].VehiclePreviousLogId = summary.PreviousLogId
				FROM summary
				WHERE summary.rk = 1
				AND [Log].LogId = summary.LogId
				";
			Sql(populateSql);
        }
        
        public override void Down()
        {
            DropIndex("Log", new[] { "VehiclePreviousLogId" });
            DropForeignKey("Log", "VehiclePreviousLogId", "Log");
            DropColumn("Log", "VehiclePreviousLogId");
        }
    }
}