using System;
using System.IO;
using System.Linq;
using MileageTraker.Web.DAL;
using MileageTraker.Web.Models;

namespace MileageTraker.Web.Migrations
{
    using System.Data.Entity.Migrations;
    
    public partial class AddLogUser : DbMigration
    {
	    /// <summary>
	    /// 0 - UserId
	    /// 1 - Username
	    /// 2 - Email
	    /// 3 - FullName
	    /// 4 - IsApproved
	    /// </summary>
	    private const string InsertUserTemplate = @"INSERT INTO [User]
					([UserId]
					,[Username]
					,[Email]
					,[FullName]
					,[Password]
					,[Comment]
					,[IsApproved]
					,[PasswordFailuresSinceLastSuccess]
					,[LastPasswordFailureDate]
					,[LastActivityDate]
					,[LastLockoutDate]
					,[LastLoginDate]
					,[Created]
					,[IsLockedOut]
					,[LastPasswordChangedDate]
					,[PasswordResetToken])
				VALUES
					('{0}'
					,'{1}'
					,'{2}'
					,'{3}'
					,'uninitialized_state'
					,NULL
					,{4}
					,0
					,'1753-1-1'
					,'1753-1-1'
					,'1753-1-1'
					,'1753-1-1'
					,GETDATE()
					,0
					,'1753-1-1'
					,NULL)";

	    private const string InsertUserRoleDriverTemplate =
			@"INSERT INTO [RoleUser]
					   ([Role_RoleId]
					   ,[User_UserId])
					 VALUES
						   ('C070ADF5-CCA3-42B0-92E4-9E38472940DF'
						   ,'{0}')";

	    private static string GetUserInsertSql(User user)
	    {
		    return string.Format(InsertUserTemplate,
		                         user.UserId,
		                         user.Username,
		                         user.Email,
		                         user.FullName,
		                         user.IsApproved ? 1 : 0);
	    }

		private static string GetUserInsertRoleDriverSql(User user)
		{
			return string.Format(InsertUserRoleDriverTemplate,
								 user.UserId);
		}

	    public override void Up()
		{
			AddColumn("Log", "User_UserId", c => c.Guid());
			AddForeignKey("Log", "User_UserId", "User", "UserId");
			CreateIndex("Log", "User_UserId");

			Sql(@"INSERT INTO [Role]
				   ([RoleId]
				   ,[RoleName]
				   ,[Description])
				 VALUES
					   ('C070ADF5-CCA3-42B0-92E4-9E38472940DF'
					   ,'Driver'
					   ,'Driver')");

		    var existingUsers = new [] {"dsloan", "ccecil"};
		    var appDomain = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "App_Data");
			var users = UserImporter.Import(Path.Combine(appDomain, "UserEmails.xls"));
		    users = (from u in users
					 where !existingUsers.Contains(u.Username)
		            select u).ToList();
			foreach (var user in users)
			{
				user.UserId = Guid.NewGuid();
				Sql(GetUserInsertSql(user));
				Sql(GetUserInsertRoleDriverSql(user));
			}

			Sql(@"UPDATE [Log]
				SET [Log].User_UserId = [User].UserId
				FROM [Log]
					INNER JOIN [User] ON 
						[User].FullName = [Log].EmployeeName");
        }

	    public override void Down()
        {
            DropIndex("Log", new[] { "User_UserId" });
            DropForeignKey("Log", "User_UserId", "User");
            DropColumn("Log", "User_UserId");

			Sql(@"DELETE FROM [User]
				WHERE UserId IN (
					SELECT RoleUser.User_UserId
					FROM RoleUser
					INNER JOIN Role On RoleUser.Role_RoleId = Role.RoleId
					WHERE RoleName = 'Driver'
				)");

			Sql(@"DELETE FROM ROLE WHERE RoleName = 'Driver'");
        }
    }
}