-- Run this script Only after data is Inserted into Table MIG_ROUTE_SEND_TO_PROD_STP_PARAMS

-- DROP TABLE MIG_ROUTE_CMP_PROJ_PARAMS
IF OBJECT_ID(N'dbo.MIG_ROUTE_CMP_PROJ_PARAMS', N'U') IS NULL
	BEGIN	
		CREATE TABLE [dbo].[MIG_ROUTE_CMP_PROJ_PARAMS](
			[ROUTE_ID] [varchar](30) NOT NULL,
			[STEP_ID] [varchar](15) NOT NULL,
			[COMPANY] [varchar](255) NOT NULL,
			[SEND_AS] [varchar](10) NOT NULL,
			[PROJ] [varchar](255) NOT NULL,
			[SITE] [varchar](255) NOT NULL,
			[GUID] [varchar](36) NOT NULL,
		 CONSTRAINT PK_MIG_ROUTE_CMP_PROJ_PARAMS PRIMARY KEY (ROUTE_ID, STEP_ID, [GUID], [COMPANY], [PROJ], [SITE])
		) ON [PRIMARY]
	END
GO

-- Query BEGIN

; WITH CTE_SPLIT_SEND_TO_PROD AS(
	SELECT RT.ROUTE_ID, RT.STEP_ID, CP.ITEMS as BAAN_COMP_PROJ, RT.[GUID] FROM dbo.MIG_ROUTE_SEND_TO_PROD_STP_PARAMS RT
	CROSS APPLY  [dbo].[MIG_SPLIT_FN](RT.BAAN_COMP_PROJ, ',') CP
	
),

CTE_SPLIT_COMPANY_PROJ AS (
	SELECT ROUTE_ID, STEP_ID, [GUID], SUBSTRING(BAAN_COMP_PROJ, 1, (CHARINDEX('~',BAAN_COMP_PROJ, 0) -1) ) AS COMPANY,
			SUBSTRING(BAAN_COMP_PROJ, CHARINDEX('~',BAAN_COMP_PROJ, 0)+1,  (LEN(BAAN_COMP_PROJ) - CHARINDEX('~',BAAN_COMP_PROJ, 0) ) ) AS [PROJ_OR_STANDARD]
	FROM CTE_SPLIT_SEND_TO_PROD 
	WHERE BAAN_COMP_PROJ <>''

),

CTE_SPLIT_COMPANY_SITE AS (
	SELECT	CMP.ROUTE_ID, 
			CMP.STEP_ID, 
			CMP.COMPANY, 
			CMP.PROJ_OR_STANDARD,
			 -- substring with the use of start index as 3 and length -3
			( SUBSTRING(CMP.COMPANY, 3, (CHARINDEX('_S_',CMP.COMPANY, 0) -3)) ) AS [NEW_COMPANY],
			( SUBSTRING(CMP.COMPANY, 
						( CHARINDEX('_S_',CMP.COMPANY, 0) + 3 ), 
								(LEN(CMP.COMPANY) - CHARINDEX('_S_',CMP.COMPANY, 0)) 
						) 
			) AS [SITE]
	FROM CTE_SPLIT_COMPANY_PROJ CMP
	WHERE CMP.COMPANY LIKE 'S_%' AND CMP.COMPANY LIKE '%_S_%'
)

INSERT INTO MIG_ROUTE_CMP_PROJ_PARAMS
-- Records with Non-Empty Companies
SELECT	CMP.ROUTE_ID, 
		CMP.STEP_ID, 
		COALESCE(ST.NEW_COMPANY, CMP.COMPANY) as COMPANY, 
		(CASE WHEN CMP.PROJ_OR_STANDARD <> 'STANDARD' THEN 'CUSTOMIZED' ELSE CMP.PROJ_OR_STANDARD END) AS [SEND_AS], 
		(CASE WHEN CMP.PROJ_OR_STANDARD <> 'STANDARD' THEN CMP.PROJ_OR_STANDARD ELSE '' END) AS [PROJ], 
		COALESCE(ST.[SITE],'') AS [SITE],
		CMP.[GUID]
FROM CTE_SPLIT_COMPANY_PROJ CMP
LEFT JOIN CTE_SPLIT_COMPANY_SITE ST
ON	CMP.ROUTE_ID = ST.ROUTE_ID AND
	CMP.STEP_ID = ST.STEP_ID AND
	CMP.COMPANY = ST.COMPANY AND
	CMP.PROJ_OR_STANDARD = ST.PROJ_OR_STANDARD


--UNION

---- Records with Empty Companies
--SELECT  TP.ROUTE_ID, 
--		TP.STEP_ID,
--		'' AS COMPANY, 
--		'' AS [SEND_AS], 
--		'' AS [PROJ], 
--		'' AS [SITE] ,
--		RT.[GUID]
--FROM RT_TOOL_PRM TP 
--LEFT JOIN MIG_ROUTE_SEND_TO_PROD_STP_PARAMS RT
--ON	RT.ROUTE_ID = TP.ROUTE_ID AND
--	RT.STEP_ID = TP.STEP_ID 
--LEFT JOIN NINTRA_TOOL NT 
--ON TP.TOOL_KEY = NT.TOOL_KEY AND
--	TP.PRM_IDX = NT.PRM_IDX 
--WHERE   TP.TOOL_KEY= 'WkFl_ExtrToBAAN' AND 
--		TP.PRM_IDX = 1 AND 
--		TP.PRM_VALUE = ''

--UNION

---- Records with Empty Companies
--SELECT  TP.ROUTE_ID, 
--		TP.STEP_ID,
--		'' AS COMPANY, 
--		'' AS [SEND_AS], 
--		'' AS [PROJ], 
--		'' AS [SITE] ,
--		RT.[GUID]
--FROM RT_TOOL_PRM TP 
--LEFT JOIN MIG_ROUTE_SEND_TO_PROD_STP_PARAMS RT
--ON	RT.ROUTE_ID = TP.ROUTE_ID AND
--	RT.STEP_ID = TP.STEP_ID 
--LEFT JOIN NINTRA_TOOL NT 
--ON TP.TOOL_KEY = NT.TOOL_KEY AND
--	TP.PRM_IDX = NT.PRM_IDX 
--WHERE   TP.TOOL_KEY= 'WkFl_TOP_ToBAAN' AND 
--		TP.PRM_IDX = 1 AND 
--		TP.PRM_VALUE = ''

