IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ReferralAddress')
BEGIN
	CREATE TABLE [Md].[ReferralAddress](
		[ReferralAddressID] [bigint] IDENTITY(1,1) NOT NULL,
		[ReferralTypeID] [smallint] NOT NULL,
		[ProvinceID] [smallint] NOT NULL,
		[ReferralSentTo] [varchar](255) NOT NULL,
		[ReferralAddress] [varchar](255) NOT NULL,
		[ReferralEmail] [varchar](100) NULL,
		[ReferralPhone] [varchar](50) NULL,
		[ReferralFax] [varchar](50) NULL,
		[IsCancelled] [bit] NOT NULL,
	 CONSTRAINT [PK_ReferralAddress] PRIMARY KEY CLUSTERED 
	(
		[ReferralAddressID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [Md].[ReferralAddress] ADD  CONSTRAINT [DF_ReferralAddress_IsCancelled]  DEFAULT ((0)) FOR [IsCancelled]

	ALTER TABLE [Md].[ReferralAddress]  WITH CHECK ADD  CONSTRAINT [FK_ReferralAddress_Provinces] FOREIGN KEY([ProvinceID])
	REFERENCES [Md].[Provinces] ([ProvinceID])

	ALTER TABLE [Md].[ReferralAddress] CHECK CONSTRAINT [FK_ReferralAddress_Provinces]

	ALTER TABLE [Md].[ReferralAddress]  WITH CHECK ADD  CONSTRAINT [FK_ReferralAddress_ReferralTypes] FOREIGN KEY([ReferralTypeID])
	REFERENCES [Md].[ReferralTypes] ([ReferralTypeID])

	ALTER TABLE [Md].[ReferralAddress] CHECK CONSTRAINT [FK_ReferralAddress_ReferralTypes]

	CREATE TABLE [App].[ApplicationReferralAddress](
		[ApplicationReferralDetailID] [bigint] NOT NULL,
		[ReferralAddressID] [bigint] NOT NULL,
	 CONSTRAINT [PK_ApplicationReferralAddress] PRIMARY KEY CLUSTERED 
	(
		[ApplicationReferralDetailID] ASC,
		[ReferralAddressID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [App].[ApplicationReferralAddress]  WITH CHECK ADD  CONSTRAINT [FK_ApplicationReferralAddress_ApplicationReferrals] FOREIGN KEY([ApplicationReferralDetailID])
	REFERENCES [App].[ApplicationReferrals] ([ApplicationReferralDetailID])

	ALTER TABLE [App].[ApplicationReferralAddress] CHECK CONSTRAINT [FK_ApplicationReferralAddress_ApplicationReferrals]
END
GO

CREATE OR ALTER PROC Md.Pr_ReferralAddress
AS
BEGIN
	SELECT ReferralAddressID, ReferralTypeID, ProvinceID, ReferralSentTo, ReferralAddress, ReferralEmail, ReferralPhone, ReferralFax
	FROM Md.ReferralAddress
	WHERE IsCancelled = 0
END
GO

IF NOT EXISTS (SELECT TOP 1 1 FROM [Integration].[DataSources] WHERE DataSourceName = 'ReferralAddress')
BEGIN
	INSERT INTO [Integration].[DataSources](DataSourceName, ConnectionID,CommandTypeID,RequestQuery,EntitySchema,
		IsCacheEnabled,CacheMinutes,IsCancelled, CreatedByID,CreatedDate,UpdatedByID, UpdatedDate)
	VALUES('ReferralAddress', 1, 1,'EXEC Md.Pr_ReferralAddress', 
		'<entity name="Associations"><property name="ReferralAddressID" isKey="true" /><property name="ReferralTypeID" /><property name="ReferralSentTo" isTitle="true" /><property name="ProvinceID" /><property name="ReferralAddress" /><property name="ReferralEmail" /><property name="ReferralPhone" /><property name="ReferralFax" /></entity>',
		0,0,0,1,'2024-06-30 12:52:21.487',1,'2024-06-30 12:52:21.487')
END
GO