/****** Object:  Table [dbo].[ReservationAddressWord]    Script Date: 27-09-2022 17:03:53 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReservationAddressWord]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ReservationAddressWord](
	[AddressNumber] [int] NOT NULL,
	[SearchWord] [varchar](200) NOT NULL,
	[ColumnID] [int] NOT NULL
 CONSTRAINT [PK_ReservationAddressWord] PRIMARY KEY CLUSTERED 
 (
	[AddressNumber] 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_ReservationAddressWord]    Script Date: 27-09-2022 17:03:53 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ReservationAddressWord]') AND name = N'IX_ReservationAddressWord')
CREATE NONCLUSTERED INDEX [IX_ReservationAddressWord] ON [dbo].[ReservationAddressWord]
(
	[SearchWord] ASC,
	[ColumnID] ASC,
	[AddressNumber] 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_ReservationAddressWord_RESERVATIONADDRESS]') AND parent_object_id = OBJECT_ID(N'[dbo].[ReservationAddressWord]'))
ALTER TABLE [dbo].[ReservationAddressWord] DROP CONSTRAINT [FK_ReservationAddressWord_RESERVATIONADDRESS]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ReservationAddressWord_RESERVATIONADDRESS1]') AND parent_object_id = OBJECT_ID(N'[dbo].[ReservationAddressWord]'))
ALTER TABLE [dbo].[ReservationAddressWord]  WITH CHECK ADD  CONSTRAINT [FK_ReservationAddressWord_RESERVATIONADDRESS1] FOREIGN KEY([AddressNumber])
REFERENCES [dbo].[RESERVATIONADDRESS] ([AddressNumber])
ON UPDATE  CASCADE 
ON DELETE  CASCADE 
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ReservationAddressWord_RESERVATIONADDRESS1]') AND parent_object_id = OBJECT_ID(N'[dbo].[ReservationAddressWord]'))
ALTER TABLE [dbo].[ReservationAddressWord] CHECK CONSTRAINT [FK_ReservationAddressWord_RESERVATIONADDRESS1]
GO
IF NOT EXISTS(
  SELECT *
  FROM HOTELSETUP
  WHERE SEARCHKEY='ReservationAddressWordReady'
  AND KeyValue='1'
)
BEGIN
	DELETE FROM dbo.ReservationAddressWord

	;WITH T
	AS
	(
		SELECT 1 ColumnID, AddressNumber, FirstName [Value] FROM RESERVATIONADDRESS WITH (NOLOCK) WHERE FirstName IS NOT NULL AND FirstName<>'' AND (ISNULL(AddressAnonymized,0)=0 OR AddressType<>1)
		UNION ALL
		SELECT 2 ColumnID, AddressNumber, LastName FROM RESERVATIONADDRESS WITH (NOLOCK) WHERE LastName IS NOT NULL AND LastName<>'' AND (ISNULL(AddressAnonymized,0)=0 OR AddressType<>1)
		UNION ALL
		SELECT 3 ColumnID, AddressNumber, ATTENTIONNAME FROM RESERVATIONADDRESS WITH (NOLOCK) WHERE ATTENTIONNAME IS NOT NULL AND ATTENTIONNAME<>'' AND ISNULL(AddressAnonymized,0)=0
		UNION ALL
		SELECT 4 ColumnID, AddressNumber, ADDRESS1 FROM RESERVATIONADDRESS  WITH (NOLOCK) WHERE ADDRESS1 IS NOT NULL AND ADDRESS1<>'' AND (ISNULL(AddressAnonymized,0)=0 OR AddressType<>1)
		UNION ALL
		SELECT 5 ColumnID, AddressNumber, REPLACE(Phone,' ','') FROM RESERVATIONADDRESS  WITH (NOLOCK) WHERE Phone IS NOT NULL AND REPLACE(Phone,' ','')<>'' AND (ISNULL(AddressAnonymized,0)=0 OR AddressType<>1)
		UNION ALL
		SELECT 5 ColumnID, AddressNumber, REPLACE(Cell,' ','') FROM RESERVATIONADDRESS  WITH (NOLOCK) WHERE Cell IS NOT NULL AND REPLACE(Cell,' ','')<>'' AND (ISNULL(AddressAnonymized,0)=0 OR AddressType<>1)
		UNION ALL
		SELECT 6 ColumnID, AddressNumber, Email FROM RESERVATIONADDRESS  WITH (NOLOCK) WHERE Email IS NOT NULL AND Email<>'' AND (ISNULL(AddressAnonymized,0)=0 OR AddressType<>1)
		UNION ALL
		SELECT 7 ColumnID, AddressNumber, ZIPCODE FROM RESERVATIONADDRESS  WITH (NOLOCK) WHERE ZIPCODE IS NOT NULL AND ZIPCODE<>'' AND (ISNULL(AddressAnonymized,0)=0 OR AddressType<>1)
		UNION ALL
		SELECT 8 ColumnID, AddressNumber, City FROM RESERVATIONADDRESS  WITH (NOLOCK) WHERE City IS NOT NULL AND City<>'' AND (ISNULL(AddressAnonymized,0)=0 OR AddressType<>1)
	)
	INSERT INTO dbo.ReservationAddressWord
	SELECT DISTINCT T.AddressNumber, 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 ('#', '&', '?','<','/')

  INSERT INTO HotelSetup (SearchKey, KeyValue) VALUES ('ReservationAddressWordReady', '1')

END
GO
