IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[CSAPP_PROTOCOL_SIGNATURES]'))
BEGIN
	CREATE TABLE [dbo].[CSAPP_PROTOCOL_SIGNATURES](
	    pk INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	    PK_PROTOCOL int null,
	    signature_type varchar(255) NULL,
	    PK_DOCS int NULL
	   )


	INSERT INTO CSAPP_PROTOCOL_SIGNATURES (PK_PROTOCOL, signature_type, PK_DOCS)
	SELECT PROTOCOL_PK, FIELD_NAME, CASE  WHEN ISNUMERIC(FIELD_VALUE) = 1 THEN FIELD_VALUE ELSE NULL END FROM CSAPP_PROTOCOL 
	WHERE FIELD_NAME = 'SIGNATUREEMPLOYEE' OR  FIELD_NAME = 'SIGNATURECUSTOMER' OR FIELD_NAME = 'SIGNATURETHIRD'
END

IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[CSAPP_MAIN_PROTOCOL]'))
BEGIN
	CREATE TABLE [dbo].[CSAPP_MAIN_PROTOCOL](
	    pk INT PRIMARY KEY NOT NULL,
	    CREATE_DATE DATETIME NULL,
		EDITOR VARCHAR(255) NULL,
		PROTOCOL_GROUP VARCHAR(255) NULL,
		LICENSE VARCHAR(255) NULL,
		VIN VARCHAR(255) NULL,
		GUID VARCHAR(255) NULL,
		PROTOCOL_TYPE INT NULL,
		PROTOCOL_KIND VARCHAR(255) NULL,
		IS_TEMPLATE BIT NULL,
		CO BIT NULL,
		CI BIT NULL
	   )

	INSERT INTO CSAPP_MAIN_PROTOCOL (pk)
	SELECT DISTINCT PROTOCOL_PK FROM CSAPP_PROTOCOL

	SET DATEFORMAT YMD
	UPDATE CSAPP_MAIN_PROTOCOL SET CREATE_DATE = (SELECT TOP 1  FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'DATE' AND PROTOCOL_PK = CSAPP_MAIN_PROTOCOL.PK)
	UPDATE CSAPP_MAIN_PROTOCOL SET EDITOR = (SELECT TOP 1  FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'EMPLOYEE' AND PROTOCOL_PK = CSAPP_MAIN_PROTOCOL.PK)
	UPDATE CSAPP_MAIN_PROTOCOL SET LICENSE = (SELECT TOP 1  FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'LICENSE' AND PROTOCOL_PK = CSAPP_MAIN_PROTOCOL.PK)
	UPDATE CSAPP_MAIN_PROTOCOL SET VIN = (SELECT TOP 1 FIELD_VALUE FROM CSAPP_PROTOCOL WHERE FIELD_NAME = 'VIN' AND PROTOCOL_PK = CSAPP_MAIN_PROTOCOL.PK)

END

IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[CSAPP_PROTOCOL]')
AND name = 'VALUE_TYPE')
BEGIN
	ALTER TABLE CSAPP_PROTOCOL ADD VALUE_TYPE VARCHAR(255) NULL
END

IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[rights]')
AND name = 'CSAPP_CCHEQ_PROTOCOL_GROUP')
	ALTER TABLE rights ADD CSAPP_CCHEQ_PROTOCOL_GROUP bit

EXEC dbo.LANG_ADD_COMPONENT @comp = 'CSAPP_PROTOCOL_SUBJECT_DEFAULT', -- varchar(100)
    @text = N'Protokoll zum Fahrzeug', -- nvarchar(max)
    @lang = 'DE', -- varchar(3)
    @update = NULL -- bit