USE DD_ECM

IF (SELECT NAME FROM [TBMT_FUNCTIONS] WHERE NAME = 'ADDRESS') IS NULL
	BEGIN
		INSERT INTO TBMT_FUNCTIONS (NAME, DESCRIPTION, [PARAMETERS], ACTIVE, ADDED_WHO) 
			VALUES ('ADDRESS', 'Finds an Address by Winline Account Number', 'Name,Street,Zip,City', 1, 'UPDATE-006')
	END


CREATE TABLE [dbo].[TBMT_TEMPLATE_ITEMS_FUNCTIONS](
	[GUID] [int] IDENTITY(1,1) NOT NULL,
	[TEMPLATE_ITEM_ID] [int] NOT NULL,
	[FUNCTION_ID] [int] NOT NULL,
	[FUNCTION_PARAMETERS] [nvarchar](max) NULL,
	[SEQUENCE] [int] NULL,
	[ADDED_WHEN] [datetime] NULL,
	[ADDED_WHO] [nvarchar](50) NULL,
	[CHANGED_WHEN] [datetime] NULL,
	[CHANGED_WHO] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[TBMT_TEMPLATE_ITEMS_FUNCTIONS] ADD  CONSTRAINT [DF_TBMT_TEMPLATE_ITEMS_FUNCTIONS_ADDED_WHEN]  DEFAULT (getdate()) FOR [ADDED_WHEN]
GO
ALTER TABLE [dbo].[TBMT_TEMPLATE_ITEMS_FUNCTIONS] ADD  CONSTRAINT [DF_TBMT_TEMPLATE_ITEMS_FUNCTIONS_SEQUENCE]  DEFAULT (0) FOR [SEQUENCE]
GO

IF (SELECT COUNT(*) FROM TBMT_TEMPLATE_ITEMS_FUNCTIONS) = 0 
	BEGIN
		INSERT INTO TBMT_TEMPLATE_ITEMS_FUNCTIONS (TEMPLATE_ITEM_ID, FUNCTION_ID, FUNCTION_PARAMETERS, ADDED_WHO)
		SELECT GUID AS TEMPLATE_ITEM_ID, FUNCTION_ID, FUNCTION_PARAMETERS, 'UPDATE-006' AS ADDED_WHO 
		FROM TBMT_TEMPLATE_ITEMS 
		WHERE FUNCTION_ID IS NOT NULL
	END
GO

ALTER TABLE TBMT_TEMPLATE_ITEMS DROP COLUMN FUNCTION_ID
ALTER TABLE TBMT_TEMPLATE_ITEMS DROP COLUMN FUNCTION_PARAMETERS
GO

ALTER  VIEW [dbo].[VWMT_ITEMS]
AS
	SELECT DISTINCT
		[TBITEMS].[GUID] AS ITEM_ID
		,[TBTEMPLATES].[NAME] [TEMPLATE_NAME]
		,[TBTABLES].[NAME] AS [TABLE_NAME]
		,[TBITEMS].[NAME] AS [ITEM_NAME]		
		,[TBTYPES].[NAME] AS [DATA_TYPE]
		,[TBTABLES].[IS_HEAD] AS [IS_HEAD]
		,[TBITEMS].[IS_READ_ONLY]
		,[TBITEMS].[IS_VISIBLE]
        ,[TBITEMS].[IS_REQUIRED]
		,[TBITEMS].[IS_VIRTUAL]
		,[TBITEMS].[ORDER_KEY]
		,[TBITEMS].[PREFER_EXTERNAL]
	FROM
		[dbo].[TBMT_TEMPLATE_ITEMS] [TBITEMS]
		INNER JOIN [dbo].[TBMT_TABLES] [TBTABLES] ON [TBITEMS].[TABLE_ID] = [TBTABLES].[GUID]
		INNER JOIN [dbo].[TBMT_TYPES] [TBTYPES] ON [TBITEMS].[TYPE_ID] = [TBTYPES].[GUID]
		INNER JOIN [dbo].[TBMT_TEMPLATES] [TBTEMPLATES] ON [TBTABLES].[TEMPLATE_ID] = [TBTEMPLATES].[GUID]		
GO

CREATE	VIEW [dbo].[VWMT_FUNCTIONS]
AS
	SELECT 
		TBITEMS.TEMPLATE_ITEM_ID AS ITEM_ID,
		TBFUNCTIONS.NAME FUNCTION_NAME,
		TBITEMS.FUNCTION_ID,
		TBITEMS.FUNCTION_PARAMETERS,
		TBITEMS.SEQUENCE
	FROM TBMT_TEMPLATE_ITEMS_FUNCTIONS TBITEMS
	LEFT OUTER JOIN [dbo].[TBMT_FUNCTIONS] AS [TBFUNCTIONS] ON [TBITEMS].[FUNCTION_ID] = [TBFUNCTIONS].[GUID]
	ORDER BY ITEM_ID, SEQUENCE
GO