--### Zuri Wed 02/13/2019  8:08:53.04 ###  
--### Zuri9.1902.2401 ###  
IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID('NFOR_FORM') AND TYPE IN ('U'))
BEGIN
CREATE TABLE dbo.NFOR_FORM(
	FOR_N_ID			INT IDENTITY(1,1) NOT NULL,
	FOR_C_IDENTIFIER	VARCHAR(50) NOT NULL,
	FOR_ARE_N_ID		INT NOT NULL,
	FOR_C_RELEASEID		VARCHAR(40) NULL,
	FOR_N_RELEASENUM	INT NOT NULL DEFAULT (1),
	FOR_C_TITLE			NVARCHAR(150) NOT NULL,
	FOR_C_TABLENAME		VARCHAR(100) NOT NULL,
	FOR_C_SOURCE		VARCHAR(MAX) NULL,
	FOR_C_METADATA		VARCHAR(MAX) NULL,
	FOR_C_TEMPLATEFILE	VARCHAR(200) NULL,
	FOR_D_CREATEDATE	DATETIME NOT NULL  DEFAULT (GETDATE()) ,
	FOR_N_CREATOR		INT NOT NULL,
	FOR_D_PUBLISHDATE	DATETIME NULL,
	FOR_N_PUBLISHER		INT NOT NULL,
	FOR_B_ISEDITING		BIT NOT NULL,
	FOR_N_EDITORID		INT NOT NULL,
	FOR_C_OFFLINEUSERS	VARCHAR(500),
	CONSTRAINT PK_NFOR_FORM PRIMARY KEY CLUSTERED (FOR_N_ID ASC)
)
END
GO

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID('NDSC_DATASOURCE_CONFIG') AND TYPE IN ('U'))
BEGIN
CREATE TABLE dbo.NDSC_DATASOURCE_CONFIG(
	DSC_N_ID INT IDENTITY(1,1) NOT NULL,
	DSC_C_IDENTIFIER VARCHAR(50) NOT NULL,
	DSC_FOR_N_ID INT NOT NULL,
	DSC_C_TITLE VARCHAR(100) NOT NULL,
	DSC_C_CONTROLCLASS VARCHAR(300) NOT NULL,
	DSC_C_METADATA VARCHAR(MAX) NULL,
	CONSTRAINT PK_NDSC_DATASOURCE_CONFIG PRIMARY KEY CLUSTERED (DSC_N_ID ASC) 
)
END
GO

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID('NCFG_FIELD_CONFIG') AND TYPE IN ('U'))
BEGIN
CREATE TABLE dbo.NCFG_FIELD_CONFIG(
	CFG_N_ID INT IDENTITY(1,1) NOT NULL,
	CFG_FOR_N_ID INT NOT NULL,
	CFG_C_NAME VARCHAR(100) NOT NULL,
	CFG_C_COLUMNNAME VARCHAR(50) NOT NULL,
	CFG_C_IDENTIFIER VARCHAR(50) NOT NULL,
	CFG_C_CONTROLCLASS VARCHAR(200) NULL,
	CFG_N_ORDER INT NOT NULL,
	CFG_C_METADATA VARCHAR(MAX) NULL,
	CFG_D_PUBLISHDATE DATETIME NULL,
	CFG_N_DELETE BIT NULL,
	CONSTRAINT PK_NCFG_FIELD_CONFIG PRIMARY KEY CLUSTERED 
	(CFG_N_ID ASC)
) 
END
GO

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID('NFDC_FIELD_DATACONFIG') AND TYPE IN ('U'))
BEGIN
CREATE TABLE dbo.NFDC_FIELD_DATACONFIG(
	FDC_N_ID INT IDENTITY(1,1) NOT NULL,
	FDC_CFG_N_ID INT NOT NULL,
	FDC_C_NAMEKEY VARCHAR(20) NULL,
	FDC_N_FIELDTYPE INT NOT NULL,
	FDC_C_METADATA VARCHAR(MAX) NULL,
	CONSTRAINT PK_NDC_FIELD_DATACONFIG PRIMARY KEY CLUSTERED 
	(FDC_N_ID ASC) 
) 
END
GO

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID('NPFM_PUBLISHEDFORM') AND TYPE IN ('U'))
BEGIN
CREATE TABLE dbo.NPFM_PUBLISHEDFORM(
	PFM_N_ID INT IDENTITY(1,1) NOT NULL,
	PFM_FOR_N_ID INT NOT NULL,
	PFM_C_CONFIGURATION VARCHAR(MAX) NULL,
	PFM_C_SOURCE VARCHAR(MAX) NULL,
	PFM_D_PUBLISHDATE	DATETIME NULL,
	PFM_N_PUBLISHER		INT NOT NULL,
	CONSTRAINT PK_NPFM_PUBLISHEDFORM PRIMARY KEY CLUSTERED 
	(PFM_N_ID ASC) 
) 
END
GO

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID('NFXC_FORM_CONTENT') AND TYPE IN ('U'))
BEGIN
CREATE TABLE dbo.NFXC_FORM_CONTENT(
	FXM_FOR_N_ID INT NOT NULL,
	FXM_CTT_N_ID INT NOT NULL,
	CONSTRAINT PK_NFXC_FORM_CONTENT PRIMARY KEY CLUSTERED (FXM_FOR_N_ID ASC, FXM_CTT_N_ID ASC) 
)
END
GO

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID('NFPC_FORM_PROCESS_CONFIG') AND TYPE IN ('U'))
BEGIN
CREATE TABLE dbo.NFPC_FORM_PROCESS_CONFIG(
	FPC_ATV_N_ID			INT NOT NULL,
	FPC_FOR_N_ID			INT NOT NULL,
	FPC_FOR_C_IDENTIFIER    VARCHAR(40),
	FPC_C_METADATA		    VARCHAR(MAX),
	CONSTRAINT PK_NFPC_FORM_PROCESS_CONFIG PRIMARY KEY CLUSTERED (FPC_ATV_N_ID ASC) 
)
END
GO

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID('NFIW_FORM_ITEM_WORKFLOW') AND TYPE IN ('U'))
BEGIN
CREATE TABLE dbo.NFIW_FORM_ITEM_WORKFLOW(
	FIW_N_ID			INT IDENTITY(1,1),
	FIW_IWF_N_ID		INT,
	FIW_N_FORM_PK_ID	INT,
	FIW_C_FORM_ALIAS	VARCHAR(200),
	FIW_C_FORM_GUID		VARCHAR(40)
	CONSTRAINT PK_NFIW_FORM_ITEM_WORKFLOW PRIMARY KEY CLUSTERED (FIW_N_ID ASC)
)
END
GO

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID('NOFC_OFFLINE_FORM_CONFIG') AND TYPE IN ('U'))
BEGIN
CREATE TABLE dbo.NOFC_OFFLINE_FORM_CONFIG(
	OFC_ATV_N_ID			INT,
	OFC_FOR_N_ID			INT,
	OFC_FOR_C_IDENTIFIER    VARCHAR(40),
	OFC_C_METADATA		    VARCHAR(MAX),
	CONSTRAINT PK_NOFC_OFFLINE_FORM_CONFIG PRIMARY KEY CLUSTERED (OFC_ATV_N_ID ASC) 
)
END
GO

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID('NOFF_OFFLINE_FORM') AND TYPE IN ('U'))
BEGIN
CREATE TABLE dbo.NOFF_OFFLINE_FORM(
	OFF_N_ID			INT IDENTITY(1,1),
	OFF_OFC_ATV_N_ID	INT,
	OFF_IWF_N_ID		INT,
	OFF_N_FORM_ID		INT,
	OFF_C_FORM_ALIAS	VARCHAR(200),
	OFF_C_METADATA 		VARCHAR(MAX)
	CONSTRAINT PK_NOFF_OFFLINE_FORM PRIMARY KEY CLUSTERED (OFF_N_ID ASC)
)
END
GO

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID('NOFU_OFFLINE_FORM_USER') AND TYPE IN ('U'))
BEGIN
CREATE TABLE dbo.NOFU_OFFLINE_FORM_USER(
	OFU_N_ID			INT IDENTITY(1,1),
	OFU_OFF_N_ID		INT,
	OFU_USR_N_ID		INT
	CONSTRAINT PK_NOFU_OFFLINE_FORM_USER PRIMARY KEY CLUSTERED (OFU_N_ID ASC) 
)
END
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_NOFF_OFFLINE_FORM]') AND parent_object_id = OBJECT_ID(N'[NOFF_OFFLINE_FORM]'))
ALTER TABLE [NOFF_OFFLINE_FORM]  WITH CHECK ADD  CONSTRAINT [FK_NOFF_OFFLINE_FORM] FOREIGN KEY([OFF_OFC_ATV_N_ID])
REFERENCES [NOFC_OFFLINE_FORM_CONFIG] ([OFC_ATV_N_ID])
ON DELETE CASCADE
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_NOFU_OFFLINE_FORM_USER]') AND parent_object_id = OBJECT_ID(N'[NOFU_OFFLINE_FORM_USER]'))
ALTER TABLE [NOFU_OFFLINE_FORM_USER]  WITH CHECK ADD  CONSTRAINT [FK_NOFU_OFFLINE_FORM_USER] FOREIGN KEY([OFU_OFF_N_ID])
REFERENCES [NOFF_OFFLINE_FORM] ([OFF_N_ID])
ON DELETE CASCADE
GO


IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_NCFG_FIELD_CONFIG_NFOR_FORM]') AND parent_object_id = OBJECT_ID(N'[NCFG_FIELD_CONFIG]'))
ALTER TABLE [NCFG_FIELD_CONFIG]  WITH CHECK ADD  CONSTRAINT [FK_NCFG_FIELD_CONFIG_NFOR_FORM] FOREIGN KEY([CFG_FOR_N_ID])
REFERENCES [NFOR_FORM] ([FOR_N_ID])
ON DELETE CASCADE
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_NCFG_FIELD_CONFIG_NFOR_FORM]') AND parent_object_id = OBJECT_ID(N'[NCFG_FIELD_CONFIG]'))
ALTER TABLE [NCFG_FIELD_CONFIG] CHECK CONSTRAINT [FK_NCFG_FIELD_CONFIG_NFOR_FORM]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_NDSC_DATASOURCE_CONFIG_NFOR_FORM]') AND parent_object_id = OBJECT_ID(N'[NDSC_DATASOURCE_CONFIG]'))
ALTER TABLE [NDSC_DATASOURCE_CONFIG]  WITH CHECK ADD  CONSTRAINT [FK_NDSC_DATASOURCE_CONFIG_NFOR_FORM] FOREIGN KEY([DSC_FOR_N_ID])
REFERENCES [NFOR_FORM] ([FOR_N_ID])
ON DELETE CASCADE
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_NDSC_DATASOURCE_CONFIG_NFOR_FORM]') AND parent_object_id = OBJECT_ID(N'[NDSC_DATASOURCE_CONFIG]'))
ALTER TABLE [NDSC_DATASOURCE_CONFIG] CHECK CONSTRAINT [FK_NDSC_DATASOURCE_CONFIG_NFOR_FORM]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_NFDC_FIELD_DATACONFIG_NCFG_FIELD_CONFIG]') AND parent_object_id = OBJECT_ID(N'[NFDC_FIELD_DATACONFIG]'))
ALTER TABLE [NFDC_FIELD_DATACONFIG]  WITH CHECK ADD  CONSTRAINT [FK_NFDC_FIELD_DATACONFIG_NCFG_FIELD_CONFIG] FOREIGN KEY([FDC_CFG_N_ID])
REFERENCES [NCFG_FIELD_CONFIG] ([CFG_N_ID])
ON DELETE CASCADE
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_NFDC_FIELD_DATACONFIG_NCFG_FIELD_CONFIG]') AND parent_object_id = OBJECT_ID(N'[NFDC_FIELD_DATACONFIG]'))
ALTER TABLE [NFDC_FIELD_DATACONFIG] CHECK CONSTRAINT [FK_NFDC_FIELD_DATACONFIG_NCFG_FIELD_CONFIG]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_NPFM_PUBLISHEDFORM_NFOR_FORM]') AND parent_object_id = OBJECT_ID(N'[NPFM_PUBLISHEDFORM]'))
ALTER TABLE [NPFM_PUBLISHEDFORM]  WITH CHECK ADD  CONSTRAINT [FK_NPFM_PUBLISHEDFORM_NFOR_FORM] FOREIGN KEY([PFM_FOR_N_ID])
REFERENCES [NFOR_FORM] ([FOR_N_ID])
ON DELETE CASCADE
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_NPFM_PUBLISHEDFORM_NFOR_FORM]') AND parent_object_id = OBJECT_ID(N'[NPFM_PUBLISHEDFORM]'))
ALTER TABLE [NPFM_PUBLISHEDFORM] CHECK CONSTRAINT [FK_NPFM_PUBLISHEDFORM_NFOR_FORM]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_NFXC_FORM_CONTENT_NFOR_FORM]') AND parent_object_id = OBJECT_ID(N'[NFXC_FORM_CONTENT]'))
ALTER TABLE [NFXC_FORM_CONTENT] WITH CHECK ADD CONSTRAINT [FK_NFXC_FORM_CONTENT_NFOR_FORM] FOREIGN KEY([FXM_FOR_N_ID]) 
REFERENCES [NFOR_FORM] ([FOR_N_ID])
ON DELETE  CASCADE 
GO
--### SET ZURI VERSION ###
begin try
	declare @v sysname = '9.1902.2401'--zuriversion
	declare @d varchar(50) = SYSDATETIMEOFFSET()
	if not @v is null and exists (select 1 from sys.extended_properties where class = 0 and name = 'Version')
	begin
		exec sp_updateextendedproperty Version, @v
		exec sp_updateextendedproperty VersionDate, @d
	end 
	else
	begin
		exec sp_addextendedproperty Version, @v
		exec sp_addextendedproperty VersionDate, @d
	end
	select * from sys.extended_properties 
end try
begin catch
end catch

--### Zuri9.1902.2401 ###  
