/****** Object:  Table [dbo].[CustomerWord]    Script Date: 27-09-2022 17:03:53 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustomerWord]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CustomerWord](
	[CustomerNumber] [int] NOT NULL,
	[SearchWord] [varchar](200) NOT NULL,
	[ColumnID] [int] NOT NULL
 CONSTRAINT [PK_CustomerWord] PRIMARY KEY CLUSTERED 
 (
	[CustomerNumber] ASC,
	[SearchWord] ASC,
	[ColumnID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Index [IX_CustomerWord]    Script Date: 27-09-2022 17:03:53 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomerWord]') AND name = N'IX_CustomerWord')
CREATE NONCLUSTERED INDEX [IX_CustomerWord] ON [dbo].[CustomerWord]
(
	[SearchWord] ASC,
	[ColumnID] ASC,
	[CustomerNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CustomerWord_Customer]') AND parent_object_id = OBJECT_ID(N'[dbo].[CustomerWord]'))
ALTER TABLE dbo.CustomerWord
DROP CONSTRAINT FK_CustomerWord_Customer
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CustomerWord_Customer1]') AND parent_object_id = OBJECT_ID(N'[dbo].[CustomerWord]'))
ALTER TABLE [dbo].[CustomerWord]  WITH CHECK ADD  CONSTRAINT [FK_CustomerWord_Customer1] FOREIGN KEY([CustomerNumber])
REFERENCES [dbo].[Customer] ([CustomerNumber]) 
ON UPDATE  CASCADE 
ON DELETE  CASCADE 
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CustomerWord_Customer1]') AND parent_object_id = OBJECT_ID(N'[dbo].[CustomerWord]'))
ALTER TABLE [dbo].[CustomerWord] CHECK CONSTRAINT [FK_CustomerWord_Customer1]
GO
IF EXISTS(
  SELECT * FROM Customer C 
  LEFT OUTER JOIN CustomerWord W ON W.CustomerNumber=C.CustomerNumber 
  WHERE W.CustomerNumber IS NULL
  AND ISNULL(C.CustomerAnonymized,0)=0
  AND ((FirstName IS NOT NULL AND FirstName<>'' ) OR
       (LastName IS NOT NULL AND LastName<>'') OR      
       (PhoneNumber IS NOT NULL AND REPLACE(PhoneNumber,' ','')<>'') OR      
       (CellPhoneNumber IS NOT NULL AND REPLACE(CellPhoneNumber,' ','')<>''))
)
BEGIN
	DELETE FROM dbo.CustomerWord

	;WITH T
	AS
	(
		SELECT 1 ColumnID, CustomerNumber, FirstName [Value] FROM Customer WITH (NOLOCK) WHERE FirstName IS NOT NULL AND FirstName<>'' AND ISNULL(CustomerAnonymized,0)=0 
		UNION ALL
		SELECT 2 ColumnID, CustomerNumber, LastName FROM Customer WITH (NOLOCK) WHERE LastName IS NOT NULL AND LastName<>'' AND ISNULL(CustomerAnonymized,0)=0 
		UNION ALL
		SELECT 5 ColumnID, CustomerNumber, REPLACE(PhoneNumber,' ','') FROM Customer  WITH (NOLOCK) WHERE PhoneNumber IS NOT NULL AND REPLACE(PhoneNumber,' ','')<>'' AND ISNULL(CustomerAnonymized,0)=0 
		UNION ALL
		SELECT 5 ColumnID, CustomerNumber, REPLACE(CellPhoneNumber,' ','') FROM Customer  WITH (NOLOCK) WHERE CellPhoneNumber IS NOT NULL AND REPLACE(CellPhoneNumber,' ','')<>'' AND ISNULL(CustomerAnonymized,0)=0 
	)
	INSERT INTO dbo.CustomerWord
	SELECT DISTINCT T.CustomerNumber, W.[Value], T.ColumnID 
	FROM T
	CROSS APPLY STRING_SPLIT(T.[Value], ' ') W
	WHERE RTRIM(W.Value) <> ''
	AND LEFT(RTRIM(W.Value),1) NOT IN ('#', '&', '?','<','/')
END
GO

