<commit_before>-- Migrate Areas
PRINT 'Migrating areas...'
SET IDENTITY_INSERT NemesisEvents.dbo.Areas ON
INSERT
	INTO NemesisEvents.dbo.Areas (Id, Name)
	SELECT Id = AreaId, Name FROM Areas
SET IDENTITY_INSERT NemesisEvents.dbo.Areas OFF

-- Migrate Venues
PRINT 'Migrating venues...'
SET IDENTITY_INSERT NemesisEvents.dbo.Venues ON
INSERT
	INTO NemesisEvents.dbo.Venues (Id, AreaId, City, Description, Latitude, Longitude, Name, StreetAddress)
	SELECT Id = VenueId, AreaId, City = NULL, Description, Latitude, Longitude, Name, StreetAddress FROM Venues
SET IDENTITY_INSERT NemesisEvents.dbo.Venues OFF
UPDATE NemesisEvents.dbo.Venues SET City = LEFT(Name, CHARINDEX(' - ', Name) - 1) WHERE CHARINDEX(' - ', Name) > 0
UPDATE NemesisEvents.dbo.Venues SET City = LEFT(Name, CHARINDEX('  ', Name) - 1) WHERE CHARINDEX('  ', Name) > 0
UPDATE NemesisEvents.dbo.Venues SET Name = RIGHT(Name, LEN(Name) - LEN(City) - 3) WHERE City IS NOT NULL
UPDATE NemesisEvents.dbo.Venues SET City = Name WHERE City IS NULL AND AreaId > 1

-- Migrate Users
PRINT 'Migrating users...'
INSERT
	INTO NemesisEvents.dbo.AspNetUsers (
		AccessFailedCount,
		Email,
		EmailConfirmed,
		LockoutEnabled,
		NormalizedEmail,
		NormalizedUserName,
		PhoneNumberConfirmed,
		TwoFactorEnabled,
		UserName,
		FullName, 
		Enabled,
		DateCreated,
		DateLastLogin, 
		SecurityStamp)
	SELECT
		AccessFailedCount = 0,
		Email = U.Email,
		EmailConfirmed = 1,
		LockoutEnabled = 0,
		NormalizedEmail = U.Email,
		NormalizedUserName = U.UserName,
		PhoneNumberConfirmed = 0,
		TwoFactorEnabled = 0,
		UserName,
		FullName = ISNULL(U.DisplayName, U.UserName),
		Enabled = U.IsApproved,
		U.DateCreated,
		DateLastLogin = ISNULL(U.DateLastLogin, U.DateCreated),
		SecurityStamp = NEWID()
	FROM Users AS U

-- Migrate email frequency
PRINT 'Updating user email frequency...'
UPDATE NemesisEvents.dbo.AspNetUsers SET EmailFrequency = 3 WHERE UserName IN (SELECT UserName FROM Users WHERE SendWeeklyMessages = 1)
UPDATE NemesisEvents.dbo.AspNetUsers SET EmailFrequency = 2 WHERE UserName IN (SELECT UserName FROM Users WHERE SendDailyMessages = 1)
UPDATE NemesisEvents.dbo.AspNetUsers SET EmailFrequency = 1 WHERE UserName IN (SELECT UserName FROM Users WHERE SendSingleMessages = 1)

-- Migrate roles
PRINT 'Creating roles if needed...'
DECLARE @AdmRoleId int
SELECT @AdmRoleId = Id FROM NemesisEvents.dbo.AspNetRoles WHERE Name = 'Administrators'
IF (@AdmRoleId IS NULL) BEGIN
	INSERT INTO NemesisEvents.dbo.AspNetRoles VALUES (NEWID(), 'Administrators', 'administrators')
	SELECT @AdmRoleId = SCOPE_IDENTITY()
END
DECLARE @OrgRoleId int
SELECT @OrgRoleId = Id FROM NemesisEvents.dbo.AspNetRoles WHERE Name = 'Organizers'
IF (@OrgRoleId IS NULL) BEGIN
	INSERT INTO NemesisEvents.dbo.AspNetRoles VALUES (NEWID(), 'Organizers', 'organizers')
	SELECT @OrgRoleId = SCOPE_IDENTITY()
END
PRINT 'Updating user roles...'
INSERT INTO NemesisEvents.dbo.AspNetUserRoles SELECT UserId = Id, RoleId = @AdmRoleId FROM NemesisEvents.dbo.AspNetUsers WHERE UserName IN (SELECT UserName FROM Users WHERE IsAdministrator = 1)
INSERT INTO NemesisEvents.dbo.AspNetUserRoles SELECT UserId = Id, RoleId = @OrgRoleId FROM NemesisEvents.dbo.AspNetUsers WHERE UserName IN (SELECT UserName FROM Users WHERE IsOrganizer = 1)

-- Migrate Events
PRINT 'Migrating events...'
SET IDENTITY_INSERT NemesisEvents.dbo.Events ON
INSERT
	INTO NemesisEvents.dbo.Events (
		Id,
		AdmissionFee,
		AllowRegistration, DateBegin, DateEnd, DateCreated, Description, InvitationSent, Name,
		OrganizerName,
		OwnerId,
		UseRegistration, VenueId
	)
	SELECT
		Id = E.EventId,
		AdmissionFee = LTRIM(STR(AdmissionFee) + ' K'),
		AllowRegistration, DateBegin, DateEnd, E.DateCreated, Description, InvitationSent, Name,
		OrganizerName = Organizer,
		OwnerId = U.Id,
		UseRegistration, VenueId
	FROM Events AS E
	LEFT JOIN NemesisEvents.dbo.AspNetUsers AS U ON E.OwnerUserName = U.UserName
SET IDENTITY_INSERT NemesisEvents.dbo.Events OFF

-- Migrate Attendees
PRINT 'Migrating attendees...'
INSERT
	INTO NemesisEvents.dbo.Attendees (DateRegistered, EventId, UserId)
	SELECT
		DateRegistered = GETDATE(),
		EventId =  A.Event_EventId,
		UserId = U.Id
	FROM UserEvents AS A
	LEFT JOIN NemesisEvents.dbo.AspNetUsers AS U ON A.User_UserName = U.UserName

-- Migrate UserAreas
PRINT 'Migrating user watched areas...'
INSERT
	INTO NemesisEvents.dbo.UserAreas(AreaId, UserId)
	SELECT
		AreaId =  A.Area_AreaId,
		UserId = U.Id
	FROM UserAreas AS A
	LEFT JOIN NemesisEvents.dbo.AspNetUsers AS U ON A.User_UserName = U.UserName

-- Normalize users
PRINT 'Finding duplicate users...'
SELECT U.Id, U.UserName, U.Email, A.Count
FROM NemesisEvents.dbo.AspNetUsers AS U
LEFT JOIN (SELECT UserId, COUNT(*) AS Count FROM NemesisEvents.dbo.Attendees GROUP BY UserId) AS A ON U.Id = A.UserId
WHERE Email IN (SELECT Email FROM NemesisEvents.dbo.AspNetUsers GROUP BY Email HAVING COUNT(*) > 1)
ORDER BY U.Email, Id

PRINT 'Consolidating duplicate users...'
UPDATE NemesisEvents.dbo.Attendees SET UserId = 83   WHERE UserId = 120
UPDATE NemesisEvents.dbo.Attendees SET UserId = 2829 WHERE UserId = 3663
UPDATE NemesisEvents.dbo.Attendees SET UserId = 253  WHERE UserId = 3149
UPDATE NemesisEvents.dbo.Attendees SET UserId = 2825 WHERE UserId = 3604
UPDATE NemesisEvents.dbo.Attendees SET UserId = 3523 WHERE UserId = 3526
UPDATE NemesisEvents.dbo.Attendees SET UserId = 1458 WHERE UserId = 4065
UPDATE NemesisEvents.dbo.Attendees SET UserId = 4286 WHERE UserId = 4659
UPDATE NemesisEvents.dbo.Attendees SET UserId = 4477 WHERE UserId = 4408

UPDATE NemesisEvents.dbo.UserAreas SET UserId = 83   WHERE UserId = 120
UPDATE NemesisEvents.dbo.UserAreas SET UserId = 2829 WHERE UserId = 3663
UPDATE NemesisEvents.dbo.UserAreas SET UserId = 253  WHERE UserId = 3149
UPDATE NemesisEvents.dbo.UserAreas SET UserId = 2825 WHERE UserId = 3604
UPDATE NemesisEvents.dbo.UserAreas SET UserId = 3523 WHERE UserId = 3526
UPDATE NemesisEvents.dbo.UserAreas SET UserId = 1458 WHERE UserId = 4065
UPDATE NemesisEvents.dbo.UserAreas SET UserId = 4286 WHERE UserId = 4659
UPDATE NemesisEvents.dbo.UserAreas SET UserId = 4477 WHERE UserId = 4408

PRINT 'Deleting duplicate users...'
DELETE FROM NemesisEvents.dbo.AspNetUsers WHERE Id IN (120, 3663, 3149, 3604, 3526, 4065, 4659, 4408)

PRINT 'Changing user names to email addresses...'
UPDATE NemesisEvents.dbo.AspNetUsers SET UserName = Email, NormalizedUserName = Email

-- Add tags
PRINT 'Creating tags...'
IF NOT EXISTS (SELECT * FROM NemesisEvents.dbo.Tags WHERE Name = 'Vvoji')  INSERT INTO NemesisEvents.dbo.Tags VALUES ('Vvoji')
IF NOT EXISTS (SELECT * FROM NemesisEvents.dbo.Tags WHERE Name = 'IT profesionlov')  INSERT INTO NemesisEvents.dbo.Tags VALUES ('IT profesionlov')
IF NOT EXISTS (SELECT * FROM NemesisEvents.dbo.Tags WHERE Name = 'Uivatel')  INSERT INTO NemesisEvents.dbo.Tags VALUES ('Uivatel')
<commit_msg>Identity normalized values are uppercase<commit_after>-- Migrate Areas
PRINT 'Migrating areas...'
SET IDENTITY_INSERT NemesisEvents.dbo.Areas ON
INSERT
	INTO NemesisEvents.dbo.Areas (Id, Name)
	SELECT Id = AreaId, Name FROM Areas
SET IDENTITY_INSERT NemesisEvents.dbo.Areas OFF

-- Migrate Venues
PRINT 'Migrating venues...'
SET IDENTITY_INSERT NemesisEvents.dbo.Venues ON
INSERT
	INTO NemesisEvents.dbo.Venues (Id, AreaId, City, Description, Latitude, Longitude, Name, StreetAddress)
	SELECT Id = VenueId, AreaId, City = NULL, Description, Latitude, Longitude, Name, StreetAddress FROM Venues
SET IDENTITY_INSERT NemesisEvents.dbo.Venues OFF
UPDATE NemesisEvents.dbo.Venues SET City = LEFT(Name, CHARINDEX(' - ', Name) - 1) WHERE CHARINDEX(' - ', Name) > 0
UPDATE NemesisEvents.dbo.Venues SET City = LEFT(Name, CHARINDEX('  ', Name) - 1) WHERE CHARINDEX('  ', Name) > 0
UPDATE NemesisEvents.dbo.Venues SET Name = RIGHT(Name, LEN(Name) - LEN(City) - 3) WHERE City IS NOT NULL
UPDATE NemesisEvents.dbo.Venues SET City = Name WHERE City IS NULL AND AreaId > 1

-- Migrate Users
PRINT 'Migrating users...'
INSERT
	INTO NemesisEvents.dbo.AspNetUsers (
		AccessFailedCount,
		Email,
		EmailConfirmed,
		LockoutEnabled,
		NormalizedEmail,
		NormalizedUserName,
		PhoneNumberConfirmed,
		TwoFactorEnabled,
		UserName,
		FullName, 
		Enabled,
		DateCreated,
		DateLastLogin, 
		SecurityStamp)
	SELECT
		AccessFailedCount = 0,
		Email = U.Email,
		EmailConfirmed = 1,
		LockoutEnabled = 0,
		NormalizedEmail = UPPER(U.Email),
		NormalizedUserName = U.UserName,
		PhoneNumberConfirmed = 0,
		TwoFactorEnabled = 0,
		UserName,
		FullName = ISNULL(U.DisplayName, U.UserName),
		Enabled = U.IsApproved,
		U.DateCreated,
		DateLastLogin = ISNULL(U.DateLastLogin, U.DateCreated),
		SecurityStamp = NEWID()
	FROM Users AS U

-- Migrate email frequency
PRINT 'Updating user email frequency...'
UPDATE NemesisEvents.dbo.AspNetUsers SET EmailFrequency = 3 WHERE UserName IN (SELECT UserName FROM Users WHERE SendWeeklyMessages = 1)
UPDATE NemesisEvents.dbo.AspNetUsers SET EmailFrequency = 2 WHERE UserName IN (SELECT UserName FROM Users WHERE SendDailyMessages = 1)
UPDATE NemesisEvents.dbo.AspNetUsers SET EmailFrequency = 1 WHERE UserName IN (SELECT UserName FROM Users WHERE SendSingleMessages = 1)

-- Migrate roles
PRINT 'Creating roles if needed...'
DECLARE @AdmRoleId int
SELECT @AdmRoleId = Id FROM NemesisEvents.dbo.AspNetRoles WHERE Name = 'Administrators'
IF (@AdmRoleId IS NULL) BEGIN
	INSERT INTO NemesisEvents.dbo.AspNetRoles VALUES (NEWID(), 'Administrators', 'ADMINISTRATORS')
	SELECT @AdmRoleId = SCOPE_IDENTITY()
END
DECLARE @OrgRoleId int
SELECT @OrgRoleId = Id FROM NemesisEvents.dbo.AspNetRoles WHERE Name = 'Organizers'
IF (@OrgRoleId IS NULL) BEGIN
	INSERT INTO NemesisEvents.dbo.AspNetRoles VALUES (NEWID(), 'Organizers', 'ORGANIZERS')
	SELECT @OrgRoleId = SCOPE_IDENTITY()
END
PRINT 'Updating user roles...'
INSERT INTO NemesisEvents.dbo.AspNetUserRoles SELECT UserId = Id, RoleId = @AdmRoleId FROM NemesisEvents.dbo.AspNetUsers WHERE UserName IN (SELECT UserName FROM Users WHERE IsAdministrator = 1)
INSERT INTO NemesisEvents.dbo.AspNetUserRoles SELECT UserId = Id, RoleId = @OrgRoleId FROM NemesisEvents.dbo.AspNetUsers WHERE UserName IN (SELECT UserName FROM Users WHERE IsOrganizer = 1)

-- Migrate Events
PRINT 'Migrating events...'
SET IDENTITY_INSERT NemesisEvents.dbo.Events ON
INSERT
	INTO NemesisEvents.dbo.Events (
		Id,
		AdmissionFee,
		AllowRegistration, DateBegin, DateEnd, DateCreated, Description, InvitationSent, Name,
		OrganizerName,
		OwnerId,
		UseRegistration, VenueId
	)
	SELECT
		Id = E.EventId,
		AdmissionFee = LTRIM(STR(AdmissionFee) + ' K'),
		AllowRegistration, DateBegin, DateEnd, E.DateCreated, Description, InvitationSent, Name,
		OrganizerName = Organizer,
		OwnerId = U.Id,
		UseRegistration, VenueId
	FROM Events AS E
	LEFT JOIN NemesisEvents.dbo.AspNetUsers AS U ON E.OwnerUserName = U.UserName
SET IDENTITY_INSERT NemesisEvents.dbo.Events OFF

-- Migrate Attendees
PRINT 'Migrating attendees...'
INSERT
	INTO NemesisEvents.dbo.Attendees (DateRegistered, EventId, UserId)
	SELECT
		DateRegistered = GETDATE(),
		EventId =  A.Event_EventId,
		UserId = U.Id
	FROM UserEvents AS A
	LEFT JOIN NemesisEvents.dbo.AspNetUsers AS U ON A.User_UserName = U.UserName

-- Migrate UserAreas
PRINT 'Migrating user watched areas...'
INSERT
	INTO NemesisEvents.dbo.UserAreas(AreaId, UserId)
	SELECT
		AreaId =  A.Area_AreaId,
		UserId = U.Id
	FROM UserAreas AS A
	LEFT JOIN NemesisEvents.dbo.AspNetUsers AS U ON A.User_UserName = U.UserName

-- Normalize users
PRINT 'Finding duplicate users...'
SELECT U.Id, U.UserName, U.Email, A.Count
FROM NemesisEvents.dbo.AspNetUsers AS U
LEFT JOIN (SELECT UserId, COUNT(*) AS Count FROM NemesisEvents.dbo.Attendees GROUP BY UserId) AS A ON U.Id = A.UserId
WHERE Email IN (SELECT Email FROM NemesisEvents.dbo.AspNetUsers GROUP BY Email HAVING COUNT(*) > 1)
ORDER BY U.Email, Id

PRINT 'Consolidating duplicate users...'
UPDATE NemesisEvents.dbo.Attendees SET UserId = 83   WHERE UserId = 120
UPDATE NemesisEvents.dbo.Attendees SET UserId = 2829 WHERE UserId = 3663
UPDATE NemesisEvents.dbo.Attendees SET UserId = 253  WHERE UserId = 3149
UPDATE NemesisEvents.dbo.Attendees SET UserId = 2825 WHERE UserId = 3604
UPDATE NemesisEvents.dbo.Attendees SET UserId = 3523 WHERE UserId = 3526
UPDATE NemesisEvents.dbo.Attendees SET UserId = 1458 WHERE UserId = 4065
UPDATE NemesisEvents.dbo.Attendees SET UserId = 4286 WHERE UserId = 4659
UPDATE NemesisEvents.dbo.Attendees SET UserId = 4477 WHERE UserId = 4408

UPDATE NemesisEvents.dbo.UserAreas SET UserId = 83   WHERE UserId = 120
UPDATE NemesisEvents.dbo.UserAreas SET UserId = 2829 WHERE UserId = 3663
UPDATE NemesisEvents.dbo.UserAreas SET UserId = 253  WHERE UserId = 3149
UPDATE NemesisEvents.dbo.UserAreas SET UserId = 2825 WHERE UserId = 3604
UPDATE NemesisEvents.dbo.UserAreas SET UserId = 3523 WHERE UserId = 3526
UPDATE NemesisEvents.dbo.UserAreas SET UserId = 1458 WHERE UserId = 4065
UPDATE NemesisEvents.dbo.UserAreas SET UserId = 4286 WHERE UserId = 4659
UPDATE NemesisEvents.dbo.UserAreas SET UserId = 4477 WHERE UserId = 4408

PRINT 'Deleting duplicate users...'
DELETE FROM NemesisEvents.dbo.AspNetUsers WHERE Id IN (120, 3663, 3149, 3604, 3526, 4065, 4659, 4408)

PRINT 'Changing user names to email addresses...'
UPDATE NemesisEvents.dbo.AspNetUsers SET UserName = Email, NormalizedUserName = UPPER(Email)

-- Add tags
PRINT 'Creating tags...'
IF NOT EXISTS (SELECT * FROM NemesisEvents.dbo.Tags WHERE Name = 'Vvoji')  INSERT INTO NemesisEvents.dbo.Tags VALUES ('Vvoji')
IF NOT EXISTS (SELECT * FROM NemesisEvents.dbo.Tags WHERE Name = 'IT profesionlov')  INSERT INTO NemesisEvents.dbo.Tags VALUES ('IT profesionlov')
IF NOT EXISTS (SELECT * FROM NemesisEvents.dbo.Tags WHERE Name = 'Uivatel')  INSERT INTO NemesisEvents.dbo.Tags VALUES ('Uivatel')

{'fraction_non_alphanumeric': 0.08381313315356023, 'fraction_numerical': 0.024925296990015305, 'mean_word_length': 4.826751592356688, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 11, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '891129', 'n_tokens_mistral': 4944, 'n_tokens_neox': 4464, 'n_words': 1412}