BEGIN TRAN T1;

--delete from dbo.tContactsInGroupsDeleted
--delete from dbo.tContactsPlainDeleted

----delete from dbo.tContactsInGroups
----delete from dbo.tContactsPlain
----delete from dbo.tGroups
----delete contacts that are not in the groups
--DECLARE @deleteContacts AS TABLE(contactId int)
--INSERT INTO @deleteContacts(contactId) select tContactsPlain.id from tContactsPlain 

----left join 
----tContactsInGroups on tContactsPlain.id = tContactsInGroups.id left join 
----tGroups on tContactsInGroups.[groupId]= tGroups.id 
----where isnull(tGroups.[name],'') not in ('test2') 

--SET IDENTITY_INSERT [dbo].[tContactsPlainDeleted] ON 

--insert into tContactsPlainDeleted ([id]
--      ,[ssuID]
--      ,[lastRequestDate]
--      ,[lastUpdateDate]
--      ,[isEnabled]
--      ,[email1]
--      ,[requestNow]
--      ,[UserProfileDisplay]
--      ,[businessPhone]
--      ,[businessPhone2]
--      ,[mobilePhone]
--      ,[otherPhone]
--      ,[pager]
--      ,[radioPhone]
--      ,[homePhone]
--      ,[homePhone2]
--      ,[businessAddressCity]
--      ,[businessAddressCountry]
--      ,[businessAddressPostalCode]
--      ,[businessAddressState]
--      ,[businessAddressStreet]
--      ,[homeAddressCity]
--      ,[homeAddressCountry]
--      ,[homeAddressPostalCode]
--      ,[homeAddressState]
--      ,[homeAddressStreet]
--      ,[otherAddressCity]
--      ,[otherAddressCountry]
--      ,[otherAddressPostalCode]
--      ,[otherAddressState]
--      ,[otherAddressStreet]
--      ,[businessFax]
--      ,[homeFax]
--      ,[otherFax]
--      ,[company]
--      ,[department]
--      ,[email2]
--      ,[email3]
--      ,[firstName]
--      ,[lastName]
--      ,[middleName]
--      ,[jobTitle]
--      ,[managerName]
--      ,[office]
--      ,[suffix]
--      ,[spouse]
--      ,[anniversary]
--      ,[userField1]
--      ,[userField2]
--      ,[userField3]
--      ,[userField4]
--      ,[assistantName]
--      ,[notes]
--      ,[birthday]
--      ,[webPage]
--      ,[personalHomePage]
--      ,[businessHomePage]
--      ,[assistantPhone]
--      ,[callbackPhone]
--      ,[carPhone]
--      ,[companyMainPhone]
--      ,[children]
--      ,[hobbies]
--      ,[iMAddress]
--      ,[iSDN]
--      ,[language]
--      ,[nickname]
--      ,[primaryPhone]
--      ,[profession]
--      ,[telex]
--      ,[ttyTddPhone]
--      ,[displayName]
--      ,[fileAs]
--      ,[uniqueID]
--      ,[lastEditedDate]
--      ,[ssuLastRequest]
--      ,[ssuDisabled]
--      ,[ssuRequestNow]
--      ,[ssuExpireID]
--      ,[ssuLastUpdate]
--      ,[SSU_DepartmentCode]
--      ,[SSU_LocationCode]
--      ,[customICE]
--      ,[SSU_DepartmentID]
--      ,[SSU_LocationID]
--      ,[SSU_ManagerSsuId]
--      ,[hasPicture]
--      ,[customEscalation]
--      ,[displayNamePrefix]
--      ,[SSU_CompanyID]
--      ,[SSU_JobTitleID]
--      ,[SSU_TitleID]
--      ,[SSU_UserField2ID]
--      ,[SSU_UserField3ID]
--      ,[SSU_UserField4ID]
--      ,[userCertificate]
--      ,[Category]
--      ,[initials]
--      ,[postalAddressId]
--      ,[mailingAddressStreet]
--      ,[mailingAddressCity]
--      ,[mailingAddressState]
--      ,[mailingAddressPostalCode]
--      ,[mailingAddressCountry]
--      ,[mailingAddress]
--      ,[SSU_UserField1ID]
--      ,[SSU_UserField5ID]
--      ,[SSU_UserField6ID]
--      ,[SSU_UserField7ID]
--      ,[SSU_UserField8ID]
--      ,[SSU_ProfessionID]
--      ,[userField5]
--      ,[userField6]
--      ,[userField7]
--      ,[userField8]
--      ,[email1DisplayName]
--      ,[hasAttach]) select [id]
--      ,[ssuID]
--      ,[lastRequestDate]
--      ,[lastUpdateDate]
--      ,[isEnabled]
--      ,[email1]
--      ,[requestNow]
--      ,[UserProfileDisplay]
--      ,[businessPhone]
--      ,[businessPhone2]
--      ,[mobilePhone]
--      ,[otherPhone]
--      ,[pager]
--      ,[radioPhone]
--      ,[homePhone]
--      ,[homePhone2]
--      ,[businessAddressCity]
--      ,[businessAddressCountry]
--      ,[businessAddressPostalCode]
--      ,[businessAddressState]
--      ,[businessAddressStreet]
--      ,[homeAddressCity]
--      ,[homeAddressCountry]
--      ,[homeAddressPostalCode]
--      ,[homeAddressState]
--      ,[homeAddressStreet]
--      ,[otherAddressCity]
--      ,[otherAddressCountry]
--      ,[otherAddressPostalCode]
--      ,[otherAddressState]
--      ,[otherAddressStreet]
--      ,[businessFax]
--      ,[homeFax]
--      ,[otherFax]
--      ,[company]
--      ,[department]
--      ,[email2]
--      ,[email3]
--      ,[firstName]
--      ,[lastName]
--      ,[middleName]
--      ,[jobTitle]
--      ,[managerName]
--      ,[office]
--      ,[suffix]
--      ,[spouse]
--      ,[anniversary]
--      ,[userField1]
--      ,[userField2]
--      ,[userField3]
--      ,[userField4]
--      ,[assistantName]
--      ,[notes]
--      ,[birthday]
--      ,[webPage]
--      ,[personalHomePage]
--      ,[businessHomePage]
--      ,[assistantPhone]
--      ,[callbackPhone]
--      ,[carPhone]
--      ,[companyMainPhone]
--      ,[children]
--      ,[hobbies]
--      ,[iMAddress]
--      ,[iSDN]
--      ,[language]
--      ,[nickname]
--      ,[primaryPhone]
--      ,[profession]
--      ,[telex]
--      ,[ttyTddPhone]
--      ,[displayName]
--      ,[fileAs]
--      ,[uniqueID]
--      ,[lastEditedDate]
--      ,[ssuLastRequest]
--      ,[ssuDisabled]
--      ,[ssuRequestNow]
--      ,[ssuExpireID]
--      ,[ssuLastUpdate]
--      ,[SSU_DepartmentCode]
--      ,[SSU_LocationCode]
--      ,[customICE]
--      ,[SSU_DepartmentID]
--      ,[SSU_LocationID]
--      ,[SSU_ManagerSsuId]
--      ,[hasPicture]
--      ,[customEscalation]
--      ,[displayNamePrefix]
--      ,[SSU_CompanyID]
--      ,[SSU_JobTitleID]
--      ,[SSU_TitleID]
--      ,[SSU_UserField2ID]
--      ,[SSU_UserField3ID]
--      ,[SSU_UserField4ID]
--      ,[userCertificate]
--      ,[Category]
--      ,[initials]
--      ,[postalAddressId]
--      ,[mailingAddressStreet]
--      ,[mailingAddressCity]
--      ,[mailingAddressState]
--      ,[mailingAddressPostalCode]
--      ,[mailingAddressCountry]
--      ,[mailingAddress]
--      ,[SSU_UserField1ID]
--      ,[SSU_UserField5ID]
--      ,[SSU_UserField6ID]
--      ,[SSU_UserField7ID]
--      ,[SSU_UserField8ID]
--      ,[SSU_ProfessionID]
--      ,[userField5]
--      ,[userField6]
--      ,[userField7]
--      ,[userField8]
--      ,[email1DisplayName]
--      ,[hasAttach] from tContactsPlain where id in (select contactId from @deleteContacts)

--SET IDENTITY_INSERT [dbo].[tContactsPlainDeleted] OFF 


--SET IDENTITY_INSERT [dbo].[tContactsInGroupsDeleted] ON 

--insert into tContactsInGroupsDeleted ([id]
--      ,[groupId]
--      ,[contactId]) select [id]
--      ,[groupId]
--      ,[contactId] from tContactsInGroups where contactId in (select contactId from @deleteContacts)

--SET IDENTITY_INSERT [dbo].[tContactsInGroupsDeleted] OFF

--delete from tContactsInGroups where contactId in (select contactId from @deleteContacts)
--delete from tContactsPlain where id in (select contactId from @deleteContacts)

----export contacts

CREATE TABLE #contacts (
    -- Status and Identity Fields
    [Status] varchar(max),
    [First Name] varchar(max),
    [Last Name] varchar(max),
    [Email1] varchar(max),
    
    -- Timestamp Fields
    [Last Update] varchar(max),
    [Last Request] varchar(max),
    [Next Request] varchar(max),
    
    -- Grouping and Display Fields
    [Groups] varchar(max),
    [UserProfileDisplay] varchar(max),
    [ICE] varchar(max),
    [Category] varchar(max),
    
    -- Location and Identification
    [Location Code] varchar(max),
    [SSU Id] varchar(max),
    [uniqueID] varchar(max),
    
	--Duplicate Name Fields (for compatibility)
    [Last Name Dup] varchar(max),
    [First Name Dup] varchar(max),

    -- Professional Information
    [Title] varchar(max),
    [Company] varchar(max),
    [Department] varchar(max),
    [Email] varchar(max),
    
    -- Phone Numbers
    [Primary Direct Phone (no extensions)] varchar(max),
    [Mobile Phone] varchar(max),
    [Home Phone] varchar(max),
    [Alternate Phone Number] varchar(max),
    [Business Fax] varchar(max),
    
    -- Business Address
    [Business City] varchar(max),
    [Business Country] varchar(max),
    [Business Zip Code] varchar(max),
    [Business State] varchar(max),
    [Business Street] varchar(max),
    
    -- Escalation and Notes
    [Escalation Level (EOC Use)] varchar(max),
    [Notes (EOC Use)] varchar(max)
)

BULK INSERT #contacts
FROM 'C:\temp\Itrezzo0929.csv'
WITH
(
  FIRSTROW = 2,
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n',
  TABLOCK,
  KEEPNULLS ,-- Treat empty fields as NULLs.
  ERRORFILE = 'C:\temp\myRubbishData.log' 
)

DECLARE @Status VARCHAR(max), @FirstName VARCHAR(max), @LastName VARCHAR(max),
        @Email1 varchar(max), @LastUpdate varchar(max), @LastRequest varchar(max), @NextRequest varchar(max),
        @Groups varchar(max), @UserProfileDisplay varchar(max), @ICE varchar(max), @Category varchar(max),
        @LocationCode varchar(max), @SSUId varchar(max), @UniqueID varchar(max),
        @LastNameDup varchar(max), @FirstNameDup varchar(max), -- Duplicate name fields
        @Title varchar(max), @Company varchar(max), @Department varchar(max), @Email varchar(max),
        @PrimaryDirectPhone varchar(max), @MobilePhone varchar(max), @HomePhone varchar(max), 
        @AlternatePhoneNumber varchar(max), @BusinessFax varchar(max),
        @BusinessCity varchar(max), @BusinessCountry varchar(max), @BusinessZipCode varchar(max),
        @BusinessState varchar(max), @BusinessStreet varchar(max),
        @EscalationLevel varchar(max), @Notes varchar(max),
        @contactId int, @groupId int, @contactGroupId int

-- Cursor for processing all records from #contacts table
DECLARE contacts_cursor CURSOR
    FOR SELECT [Status], [First Name], [Last Name], [Email1], [Last Update], [Last Request], [Next Request],
               [Groups], [UserProfileDisplay], [ICE], [Category], [Location Code], [SSU Id], [uniqueID],
               [Last Name], [First Name], -- Duplicate fields
               [Title], [Company], [Department], [Email],
               [Primary Direct Phone (no extensions)], [Mobile Phone], [Home Phone], [Alternate Phone Number], [Business Fax],
               [Business City], [Business Country], [Business Zip Code], [Business State], [Business Street],
               [Escalation Level (EOC Use)], [Notes (EOC Use)]
        FROM #contacts

OPEN contacts_cursor
FETCH NEXT FROM contacts_cursor
INTO @Status, @FirstName, @LastName, @Email1, @LastUpdate, @LastRequest, @NextRequest,
     @Groups, @UserProfileDisplay, @ICE, @Category, @LocationCode, @SSUId, @UniqueID,
     @LastNameDup, @FirstNameDup, -- Duplicate fields
     @Title, @Company, @Department, @Email,
     @PrimaryDirectPhone, @MobilePhone, @HomePhone, @AlternatePhoneNumber, @BusinessFax,
     @BusinessCity, @BusinessCountry, @BusinessZipCode, @BusinessState, @BusinessStreet,
     @EscalationLevel, @Notes

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Use Email1 as primary email for lookup
    declare @emailMod VARCHAR(max)
    set @emailMod = @Email1

    -- Data cleaning and normalization - remove quotes
    set @LastName = REPLACE(@LastName, '"', '')
    set @FirstName = REPLACE(@FirstName, '"', '')
    set @Email1 = REPLACE(@Email1, '"', '')
    set @Email = REPLACE(@Email, '"', '')
    set @emailMod = REPLACE(@emailMod, '"', '')
    set @Company = REPLACE(@Company, '"', '')
    set @Department = REPLACE(@Department, '"', '')
    set @Title = REPLACE(@Title, '"', '')
    set @PrimaryDirectPhone = REPLACE(@PrimaryDirectPhone, '"', '')
    set @MobilePhone = REPLACE(@MobilePhone, '"', '')
    set @HomePhone = REPLACE(@HomePhone, '"', '')
    set @AlternatePhoneNumber = REPLACE(@AlternatePhoneNumber, '"', '')
    set @BusinessFax = REPLACE(@BusinessFax, '"', '')
    set @BusinessCity = REPLACE(@BusinessCity, '"', '')
    set @BusinessCountry = REPLACE(@BusinessCountry, '"', '')
    set @BusinessZipCode = REPLACE(@BusinessZipCode, '"', '')
    set @BusinessState = REPLACE(@BusinessState, '"', '')
    set @BusinessStreet = REPLACE(@BusinessStreet, '"', '')
    set @UserProfileDisplay = REPLACE(@UserProfileDisplay, '"', '')
    set @LocationCode = REPLACE(@LocationCode, '"', '')
    set @Category = REPLACE(@Category, '"', '')
    set @Status = REPLACE(@Status, '"', '')
    set @ICE = REPLACE(@ICE, '"', '')

    -- Notes processing and cleanup
    if(@Notes = ',')
    BEGIN
        set @Notes = null
    END

    declare @notesTrim varchar(max)
    set @notesTrim = RTRIM(LTRIM(@Notes))

    if(LEN(@notesTrim) > 0)
    BEGIN
        -- Remove trailing comma
        if(SUBSTRING(@notesTrim, len(@notesTrim), 1) = ',')
        BEGIN
            set @notesTrim = SUBSTRING(@notesTrim, 1, len(@notesTrim) - 1)
        END

        -- Remove leading comma
        if(SUBSTRING(@notesTrim, 1, 1) = ',')
        BEGIN
            set @notesTrim = SUBSTRING(@notesTrim, 2, len(@notesTrim))
        END
    END

    set @Notes = @notesTrim

    -- Find existing contact
    set @contactId = (select top 1 id from tContactsPlain where email1 = @emailMod)

    -- Create new contact if it doesn't exist
    IF @contactId is null and Len(@emailMod) > 0 and @emailMod <> 'N/A' and @emailMod <> 'n/a' and CHARINDEX('@', @emailMod) >= 0
    BEGIN
        insert into tContactsPlain (
            -- Core fields
            ssuID, isEnabled, requestNow, email1, firstName, lastName, jobTitle, company, department,
            -- Phone numbers
            businessPhone, businessPhone2, mobilePhone, homePhone,
            -- Fax numbers
            businessFax,
            -- Business address fields
            businessAddressCity, businessAddressCountry, businessAddressPostalCode,
            businessAddressState, businessAddressStreet,
            -- Additional fields
            notes, customEscalation, customICE,
            -- Extended fields
            Category,
            -- Additional contact data
            email2, -- use Email field as second email if different
            displayName, fileAs,
            -- Location
            SSU_LocationCode
       ) 
        values (
            -- Core fields
            COALESCE(@SSUId, 'external' + LOWER(REPLACE(newid(), '-', ''))), 1, 0, @emailMod, @FirstName, @LastName, @Title, @Company, @Department,
            -- Phone numbers
            @PrimaryDirectPhone, @AlternatePhoneNumber, @MobilePhone, @HomePhone,
            -- Fax numbers
            @BusinessFax,
            -- Business address
            @BusinessCity, @BusinessCountry, @BusinessZipCode, @BusinessState, @BusinessStreet,
            -- Additional fields
            @Notes, @EscalationLevel, @ICE,
            -- Extended fields
            @Category,
            -- Additional contact data
            CASE WHEN @Email <> @Email1 THEN @Email ELSE NULL END, -- email2 only if different
            COALESCE(@UserProfileDisplay, @FirstName + ' ' + @LastName), -- displayName
            @LastName + ', ' + @FirstName, -- fileAs
            -- Location
            @LocationCode
         )
        
        set @contactId = (SELECT SCOPE_IDENTITY())
    END
    -- Update existing contact
    ELSE IF @contactId is not null
    BEGIN
        update tContactsPlain set 
            -- Core fields
            firstName = isnull(@FirstName, firstName),
            lastName = isnull(@LastName, lastName),
            jobTitle = isnull(@Title, jobTitle),
            company = isnull(@Company, company),
            department = isnull(@Department, department),
            -- Phone numbers
            businessPhone = isnull(@PrimaryDirectPhone, businessPhone),
            mobilePhone = isnull(@MobilePhone, mobilePhone),
            homePhone = isnull(@HomePhone, homePhone),
            businessPhone2 = isnull(@AlternatePhoneNumber, businessPhone2),
            -- Fax numbers
            businessFax = isnull(@BusinessFax, businessFax),
            -- Address fields
            businessAddressCity = isnull(@BusinessCity, businessAddressCity),
            businessAddressCountry = isnull(@BusinessCountry, businessAddressCountry),
            businessAddressPostalCode = isnull(@BusinessZipCode, businessAddressPostalCode),
            businessAddressState = isnull(@BusinessState, businessAddressState),
            businessAddressStreet = isnull(@BusinessStreet, businessAddressStreet),
            -- Additional fields
            customEscalation = isnull(@EscalationLevel, customEscalation),
            notes = isnull(@Notes, notes),
            customICE = isnull(@ICE, customICE),
            -- Extended fields
            Category = isnull(@Category, Category),
            -- Additional contact data
            email2 = CASE 
                WHEN @Email IS NOT NULL AND @Email <> @Email1 AND @Email <> email2 
                THEN @Email 
                ELSE email2 
            END,
            displayName = isnull(COALESCE(@UserProfileDisplay, @FirstName + ' ' + @LastName), displayName),
            fileAs = isnull(@LastName + ', ' + @FirstName, fileAs),
            -- Location
            SSU_LocationCode = isnull(@LocationCode, SSU_LocationCode)
            -- EXCLUDED: lastEditedDate, lastUpdateDate, ssuDisabled, uniqueID
        where id = @contactId
    END

    -- Process groups if Groups field is populated
    IF @Groups IS NOT NULL AND LEN(@Groups) > 0
    BEGIN
        declare @group varchar(max)
        DECLARE groups_cursor CURSOR
            FOR SELECT [value]
            FROM STRING_SPLIT(@Groups, ';')
            WHERE [value] IS NOT NULL AND LEN(LTRIM(RTRIM([value]))) > 0
        
        OPEN groups_cursor
        FETCH NEXT FROM groups_cursor INTO @group 

        WHILE @@FETCH_STATUS = 0
        BEGIN
            set @group = RTRIM(LTRIM(@group))

            if @contactId is not null and @group is not null and LEN(@group) > 0
            BEGIN
                set @groupId = (select id from tGroups where [name] = @group)
                IF @groupId is null
                BEGIN
                    insert into tGroups ([name], entryId) values (@group, newid())
                    set @groupId = (SELECT SCOPE_IDENTITY())
                END

                -- Add contact to group
                if @groupId is not null
                BEGIN
                    set @contactGroupId = (select id from tContactsInGroups where contactId = @contactId and groupId = @groupId)
                    IF @contactGroupId is null
                    BEGIN
                        insert into tContactsInGroups (groupId, contactId) values (@groupId, @contactId)
                    END
                END
            END

            FETCH NEXT FROM groups_cursor INTO @group 
        END

        CLOSE groups_cursor;
        DEALLOCATE groups_cursor;
    END

    -- Move to next record
    FETCH NEXT FROM contacts_cursor
    INTO @Status, @FirstName, @LastName, @Email1, @LastUpdate, @LastRequest, @NextRequest,
         @Groups, @UserProfileDisplay, @ICE, @Category, @LocationCode, @SSUId, @UniqueID,
         @LastNameDup, @FirstNameDup, -- Duplicate fields
         @Title, @Company, @Department, @Email,
         @PrimaryDirectPhone, @MobilePhone, @HomePhone, @AlternatePhoneNumber, @BusinessFax,
         @BusinessCity, @BusinessCountry, @BusinessZipCode, @BusinessState, @BusinessStreet,
         @EscalationLevel, @Notes
END

CLOSE contacts_cursor;
DEALLOCATE contacts_cursor;
COMMIT TRAN T1;


IF OBJECT_ID('tempdb..#contacts') IS NOT NULL DROP TABLE #contacts
GO