using Microsoft.EntityFrameworkCore;

namespace CoreDatabase.Scripts
{
	/// <summary>
	/// Скрипты для миграции данных по кафедре
	/// </summary>
	public static class DepartmentMigrationScript
	{
		private static readonly string clearMigration =
@"DELETE FROM [DepartmentDatabasePortal].[dbo].[Classrooms];
GO
DELETE FROM [DepartmentDatabasePortal].[dbo].[DisciplineBlocks];
GO
DELETE FROM [DepartmentDatabasePortal].[dbo].[Disciplines];
GO
DELETE FROM [DepartmentDatabasePortal].[dbo].[Posts];
GO
DELETE FROM [DepartmentDatabasePortal].[dbo].[Lecturers];
GO
DELETE FROM [DepartmentDatabasePortal].[dbo].[TimeNorms];
GO
DELETE FROM [DepartmentDatabasePortal].[dbo].[AcademicPlans];
GO
DELETE FROM [DepartmentDatabasePortal].[dbo].[AcademicPlanRecords];
GO
DELETE FROM [DepartmentDatabasePortal].[dbo].[AcademicPlanRecordTimeNormHours];
GO";

		private static readonly string classroomsMigration =
@"DECLARE @employeeId uniqueidentifier;

SELECT @employeeId = emp.[Id] FROM [DepartmentDatabasePortal].[dbo].[Employees] emp

INSERT INTO [DepartmentDatabasePortal].[dbo].[Classrooms]([Id],[Number],[EmployeeId],[ClassroomType],[Square],[Capacity],[HaveProjector],[SecurityCode],
[DateCreate],[DateDelete],[IsDeleted])
SELECT [Id],[Number],@employeeId,[ClassroomType],1,[Capacity],0,'-',[DateCreate],[DateDelete],[IsDeleted] FROM [DepartmentDatabaseContext].[dbo].[Classrooms]";

		private static readonly string disciplineBlockMigration =
@"INSERT INTO [DepartmentDatabasePortal].[dbo].[DisciplineBlocks]([Id],[Title],[DisciplineBlockUseForGrouping],[DisciplineBlockOrder],
[DisciplineBlockBlueAsteriskName],[DateCreate],[DateDelete],[IsDeleted])
SELECT [Id],[Title],[DisciplineBlockUseForGrouping],[DisciplineBlockOrder],[DisciplineBlockBlueAsteriskName],[DateCreate],[DateDelete],[IsDeleted] FROM 
[DepartmentDatabaseContext].[dbo].[DisciplineBlocks]";

		private static readonly string disciplineMigration =
@"DELETE FROM [DepartmentDatabaseContext].[dbo].[Disciplines] WHERE [Id]='6CC684D5-900C-4278-AA43-0B83D0711C55' OR [Id]='A6598227-8449-4884-8290-13EABEC26924'
 OR [Id]='4581ADC8-5A15-4F95-A40D-1607856ACB5C' OR [Id]='9DA4D810-025A-4A03-81FD-293DEF52FA4B' -- дублт в дисципдинах

INSERT INTO [DepartmentDatabasePortal].[dbo].[Disciplines]([Id],[DisciplineBlockId],[DisciplineName],[DisciplineShortName],[Description],
[DisciplineBlueAsteriskName],[DateCreate],[DateDelete],[IsDeleted])
SELECT [Id],[DisciplineBlockId],[DisciplineName],[DisciplineShortName],[DisciplineDescription],[DisciplineBlueAsteriskName],[DateCreate],[DateDelete],[IsDeleted]
FROM [DepartmentDatabaseContext].[dbo].[Disciplines]";

		private static readonly string postMigration =
@"INSERT INTO [DepartmentDatabasePortal].[dbo].[Posts]([Id],[PostName],[Hours],[Order],[DateCreate],[DateDelete],[IsDeleted])
SELECT [Id],[StudyPostTitle],[Hours],1,[DateCreate],[DateDelete],[IsDeleted] FROM [DepartmentDatabaseContext].[dbo].[LecturerStudyPosts]";

		private static readonly string lecturerMigration =
@"DECLARE @userId uniqueidentifier;

SELECT @userId = u.[Id] FROM [DepartmentDatabasePortal].[dbo].[Users] u WHERE u.[UserName]='admin'

INSERT INTO [DepartmentDatabasePortal].[dbo].[Lecturers]([Id],[UserId],[LastName],[FirstName],[Patronymic],[Abbreviation],[DateBirth],[Address],[Email],
[MobileNumber],[HomeNumber],[Description],[Photo],[GroupElectricalSafety],[OnlyForPrivate],[DateCreate],[DateDelete],[IsDeleted])
SELECT [Id],@userId,[LastName],[FirstName],[Patronymic],[Abbreviation],[DateBirth],[Address],[Email],[MobileNumber],[HomeNumber],[Description],[Photo],'I',
[OnlyForPrivate],[DateCreate],[DateDelete],[IsDeleted] FROM [DepartmentDatabaseContext].[dbo].[Lecturers]";

		private static readonly string educationdirectionrMigration =
@"DECLARE @lecturerId uniqueidentifier;

SELECT @lecturerId = l.[Id] FROM [DepartmentDatabasePortal].[dbo].[Lecturers] l

INSERT INTO [DepartmentDatabasePortal].[dbo].[EducationDirections]([Id],[Cipher],[ShortName],[Title],[Profile],[Qualification],[LecturerId],[Description],
[DateCreate],[DateDelete],[IsDeleted])
SELECT [Id],[Cipher],[ShortName],[Title],[Profile],[Qualification],@lecturerId,[Description],[DateCreate],[DateDelete],[IsDeleted] FROM 
[DepartmentDatabaseContext].[dbo].[EducationDirections]";

		private static readonly string timeNormMigration =
@"INSERT INTO [DepartmentDatabasePortal].[dbo].[TimeNorms]([Id],[DisciplineBlockId],[TimeNormName],[TimeNormShortName]
      ,[TimeNormOrder],[TimeNormEducationDirectionQualification],[KindOfLoadName],[KindOfLoadAttributeName],[KindOfLoadBlueAsteriskName]
      ,[KindOfLoadBlueAsteriskAttributeName],[KindOfLoadBlueAsteriskPracticName],[UseInLearningProgress],[UseInSite],[DateCreate],[DateDelete],[IsDeleted])
SELECT [Id],[DisciplineBlockId],[TimeNormName],[TimeNormShortName],[TimeNormOrder],[TimeNormEducationDirectionQualification],[KindOfLoadName]
	  ,[KindOfLoadAttributeName],[KindOfLoadBlueAsteriskName],[KindOfLoadBlueAsteriskAttributeName],[KindOfLoadBlueAsteriskPracticName]
	  ,[UseInLearningProgress],[UseInSite],[DateCreate],[DateDelete],[IsDeleted] FROM [DepartmentDatabaseContext].[dbo].[TimeNorms] WHERE 
[AcademicYearId]='4FFC16B7-C1A5-47B1-B780-821ED70793DC'";

		private static readonly string academicplanMigration =
@"INSERT INTO [DepartmentDatabasePortal].[dbo].[AcademicPlans]([Id],[EducationDirectionId],[YearEntrance],[YearFinish],[DateCreate],[DateDelete],[IsDeleted])
SELECT [Id],[EducationDirectionId],2020 + ABS(CHECKSUM(NEWID()) % 100),2024,[DateCreate],[DateDelete],[IsDeleted] FROM [DepartmentDatabaseContext].[dbo].[AcademicPlans] WHERE 
[AcademicYearId]='4FFC16B7-C1A5-47B1-B780-821ED70793DC'";

		private static readonly string academicplanrecordsMigration =
@"INSERT INTO [DepartmentDatabasePortal].[dbo].[AcademicPlanRecords]([Id],[AcademicPlanId],[DisciplineId],[InDepartment],[Semester],[Zet]
      ,[AcademicPlanRecordParentId],[IsParent],[IsFacultative],[DateCreate],[DateDelete],[IsDeleted])
SELECT apr.[Id],apr.[AcademicPlanId],apr.[DisciplineId],apr.[InDepartment],apr.[Semester],apr.[Zet],apr.[AcademicPlanRecordParentId],apr.[IsParent],apr.[IsFacultative]
      ,apr.[DateCreate],apr.[DateDelete],apr.[IsDeleted] FROM [DepartmentDatabaseContext].[dbo].[AcademicPlanRecords] apr
JOIN [DepartmentDatabaseContext].[dbo].[AcademicPlans] ap on apr.[AcademicPlanId]=ap.[Id] WHERE 
ap.[AcademicYearId]='4FFC16B7-C1A5-47B1-B780-821ED70793DC'";

		private static readonly string academicplanrecordhoursMigration =
@"INSERT INTO [DepartmentDatabasePortal].[dbo].[AcademicPlanRecordTimeNormHours]([Id],[AcademicPlanRecordId],[TimeNormId],[PlanHours],[DateCreate],[DateDelete],
	[IsDeleted])
SELECT apre.[Id],apre.[AcademicPlanRecordId],apre.[TimeNormId],apre.[PlanHours],apre.[DateCreate],apre.[DateDelete],apre.[IsDeleted]
FROM [DepartmentDatabaseContext].[dbo].[AcademicPlanRecordElements] apre
JOIN [DepartmentDatabaseContext].[dbo].[AcademicPlanRecords] apr on apre.[AcademicPlanRecordId]=apr.[Id]
JOIN [DepartmentDatabaseContext].[dbo].[AcademicPlans] ap on apr.[AcademicPlanId]=ap.[Id] WHERE 
ap.[AcademicYearId]='4FFC16B7-C1A5-47B1-B780-821ED70793DC'";

		/// <summary>
		/// Перенос данных по безопасности
		/// </summary>
		/// <param name="context"></param>
		public static void RunScript(DbContext context)
		{
			context.Database.ExecuteSqlRaw(clearMigration, null);

			context.Database.ExecuteSqlRaw(classroomsMigration, null);
			context.Database.ExecuteSqlRaw(disciplineBlockMigration, null);
			context.Database.ExecuteSqlRaw(disciplineMigration, null);
			context.Database.ExecuteSqlRaw(postMigration, null);
			context.Database.ExecuteSqlRaw(lecturerMigration, null);
			context.Database.ExecuteSqlRaw(educationdirectionrMigration, null);
			context.Database.ExecuteSqlRaw(timeNormMigration, null);
			context.Database.ExecuteSqlRaw(academicplanMigration, null);
			context.Database.ExecuteSqlRaw(academicplanrecordsMigration, null);
			context.Database.ExecuteSqlRaw(academicplanrecordhoursMigration, null);
		}
	}
}