USE INNO_ADMIN;
GO

CREATE TABLE S_OPERATIONAL.T_PLANIFIED_EMPLOYEE_PEM(
	PEM_ID			INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	PEM_CREATED_ON	DATETIME2 NOT NULL DEFAULT GETDATE(),
	PEM_CREATED_BY	VARCHAR(100) NOT NULL DEFAULT SYSTEM_USER
)
GO

CREATE TABLE S_OPERATIONAL.T_REAL_EMPLOYEE_REM(
	PEM_ID			INT NOT NULL PRIMARY KEY REFERENCES S_OPERATIONAL.T_PLANIFIED_EMPLOYEE_PEM(PEM_ID),
	EMP_ID			INT NOT NULL UNIQUE REFERENCES S_HR.T_EMPLOYEE_EMP(EMP_ID),
	REM_CREATED_ON	DATETIME2 NOT NULL DEFAULT GETDATE(),
	REM_CREATED_BY	VARCHAR(100) NOT NULL DEFAULT SYSTEM_USER
)
GO

CREATE TABLE S_OPERATIONAL.T_FICTITIOUS_EMPLOYEE_FEM(
	PEM_ID				INT NOT NULL PRIMARY KEY REFERENCES S_OPERATIONAL.T_PLANIFIED_EMPLOYEE_PEM(PEM_ID),
	STR_ID				TINYINT NOT NULL REFERENCES dbo.T_STORE_STR(STR_ID),
	FEM_NAME			VARCHAR(50) NOT NULL,
	FEM_HOURS_PER_WEEK	DECIMAL(4,2) NOT NULL,
	FEM_CREATED_ON		DATETIME2 NOT NULL DEFAULT GETDATE(),
	FEM_CREATED_BY		VARCHAR(100) NOT NULL DEFAULT SYSTEM_USER
)
GO

CREATE TABLE S_OPERATIONAL.T_OLD_FICTITIOUS_EMPLOYEE_OFE(
	PEM_ID				INT NOT NULL PRIMARY KEY REFERENCES S_OPERATIONAL.T_REAL_EMPLOYEE_REM(PEM_ID),
	STR_ID				TINYINT NOT NULL REFERENCES dbo.T_STORE_STR(STR_ID),
	OFE_NAME			VARCHAR(50) NOT NULL,
	OFE_HOURS_PER_WEEK	DECIMAL(4,2) NOT NULL,
	OFE_CREATED_ON		DATETIME2 NOT NULL DEFAULT GETDATE(),
	OFE_CREATED_BY		VARCHAR(100) NOT NULL DEFAULT SYSTEM_USER
)
GO

CREATE TRIGGER TRG_INS_T_REAL_EMPLOYEE_REM
	ON	S_OPERATIONAL.T_REAL_EMPLOYEE_REM
	FOR INSERT, UPDATE
AS
BEGIN
	DECLARE
		@ERRNO	INT,
		@ERRMSG	VARCHAR(255)

	/* LA VALEUR DE PEM_ID NE DOIT PAS ETRE UTILISEE DANS UNE AUTRE TABLE FILLE */
	IF EXISTS(	SELECT	
						*
				FROM	
						S_OPERATIONAL.T_FICTITIOUS_EMPLOYEE_FEM
				WHERE	
						PEM_ID IN (	SELECT 
											PEM_ID
									FROM   
											INSERTED))
	BEGIN
		SET @errno  = 30002
		SET @errmsg = 'Clef de T_PLANIFIED_EMPLOYEE_PEM déjà utilisée ailleurs. Insertion dans T_REAL_EMPLOYEE_REM impossible.'
		GOTO LBL_ERROR
	END
	
	/*GESTION D'ERREURS*/
	LBL_ERROR:
	RAISERROR @ERRNO @ERRMSG
	ROLLBACK TRANSACTION
END
GO

CREATE TRIGGER TRG_INS_T_FICTITIOUS_EMPLOYEE_FEM
	ON	S_OPERATIONAL.T_FICTITIOUS_EMPLOYEE_FEM
	FOR INSERT, UPDATE
AS
BEGIN
	DECLARE
		@ERRNO	INT,
		@ERRMSG	VARCHAR(255)

	/* LA VALEUR DE PEM_ID NE DOIT PAS ETRE UTILISEE DANS UNE AUTRE TABLE FILLE */
	IF EXISTS(	SELECT	
						*
				FROM		
						S_OPERATIONAL.T_REAL_EMPLOYEE_REM
				WHERE	
						PEM_ID IN (	SELECT 
											PEM_ID
									FROM   
											INSERTED))
	BEGIN
		SET @errno  = 30002
		SET @errmsg = 'Clef de T_PLANIFIED_EMPLOYEE_PEM déjà utilisée ailleurs. Insertion dans T_FICTITIOUS_EMPLOYEE_FEM impossible.'
		GOTO LBL_ERROR
	END
	
	/*GESTION D'ERREURS*/
	LBL_ERROR:
	RAISERROR @ERRNO @ERRMSG
	ROLLBACK TRANSACTION
END
GO

CREATE TRIGGER TRG_INS2_T_REAL_EMPLOYEE_REM 
	ON S_OPERATIONAL.T_REAL_EMPLOYEE_REM
	INSTEAD OF INSERT
AS
BEGIN
	DECLARE @TMP TABLE(PEM_ID INT NOT NULL, EMP_ID INT NOT NULL)

	--INSERTION DES CLEFS DANS LA TABLE MERE
	MERGE INTO S_OPERATIONAL.T_PLANIFIED_EMPLOYEE_PEM PEM
	USING INSERTED I
		ON PEM.PEM_ID = I.PEM_ID

	WHEN NOT MATCHED BY TARGET THEN
		INSERT DEFAULT VALUES
		OUTPUT INSERTED.PEM_ID, I.EMP_ID INTO @TMP(PEM_ID, EMP_ID);

	--INSERTION DANS LA TABLE FILLE
	INSERT INTO S_OPERATIONAL.T_REAL_EMPLOYEE_REM(PEM_ID, EMP_ID)
	SELECT	PEM_ID, EMP_ID
	FROM	@TMP;

	INSERT INTO S_OPERATIONAL.T_REAL_EMPLOYEE_REM(PEM_ID, EMP_ID)
	SELECT	PEM_ID, EMP_ID
	FROM	INSERTED
	WHERE	PEM_ID IS NOT NULL;
END
GO

CREATE TRIGGER TRG_DEL_T_FICTITIOUS_EMPLOYEE_FEM
	ON S_OPERATIONAL.T_FICTITIOUS_EMPLOYEE_FEM
	FOR DELETE
AS
BEGIN
	INSERT INTO S_OPERATIONAL.T_OLD_FICTITIOUS_EMPLOYEE_OFE(PEM_ID, STR_ID, OFE_NAME, OFE_HOURS_PER_WEEK)
	SELECT
			PEM_ID,
			STR_ID,
			FEM_NAME,
			FEM_HOURS_PER_WEEK
	FROM
			DELETED
END
GO

CREATE PROCEDURE S_OPERATIONAL.UP_CONVERT_FEM_TO_REM
	@PEM_ID	INT,
	@EMP_ID	INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @MSG VARCHAR(4000);

	BEGIN TRAN

	DELETE FROM S_OPERATIONAL.T_FICTITIOUS_EMPLOYEE_FEM
	WHERE	PEM_ID = @PEM_ID;

	INSERT INTO S_OPERATIONAL.T_REAL_EMPLOYEE_REM(PEM_ID, EMP_ID)
	VALUES (@PEM_ID, @EMP_ID);

	IF @@ERROR <> 0
		BEGIN	
			SELECT @MSG = ERROR_MESSAGE();
			RAISERROR(@MSG, 16, 1)
			
			ROLLBACK TRAN
		END
		ELSE
			COMMIT TRAN
END
GO

CREATE PROCEDURE S_OPERATIONAL.UP_FICTITIOUS_EMPLOYEE_INSERT
	@PEM_ID INT OUTPUT,
	@STR_ID TINYINT,
	@FEM_NAME VARCHAR(50),
	@FEM_HOURS DECIMAL(4,2)
AS
BEGIN
	SET NOCOUNT ON ;

	INSERT INTO S_OPERATIONAL.T_PLANIFIED_EMPLOYEE_PEM DEFAULT VALUES;
	
	SET @PEM_ID = SCOPE_IDENTITY();

	INSERT INTO S_OPERATIONAL.T_FICTITIOUS_EMPLOYEE_FEM(PEM_ID, STR_ID, FEM_NAME, FEM_HOURS_PER_WEEK)
	VALUES (@PEM_ID, @STR_ID, @FEM_NAME, @FEM_HOURS);
END