/**************************************************************************************
    Script: create_Process_FormResponseMilestone.sql
    Purpose: Register a MinistryPlatform Process (with one SQL step) that fires when a
             Form_Responses record is CREATED and runs the stored procedure
             dbo.api_custom_FormResponses_CreateMilestones.

    Modeled on:
      - Process 24 "Statement Maintenance Required": a Type-3 (SQL) step whose
        SQL_Statement is  EXEC <proc> @DomainID = 1.
      - Process 21 "Form Response Submitted - Forms": Table_Name = Form_Responses,
        Trigger_Fields = Form_Response_ID, with a Dependent_Condition that filters the
        triggering record (columns referenced as Form_Responses.<Column>).

    Prerequisite:
      - Run create_proc_FormResponse_CreateMilestone.sql first (creates the proc).

    Safe to re-run: the Process is created only if one with the same name doesn't exist;
    the step is created only if this Process has no SQL step yet.

    Target: MinistryPlatform | MS SQL Server 15.0 (SQL Server 2019)
**************************************************************************************/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DECLARE @Domain_ID    INT           = 1;
DECLARE @ProcessName  NVARCHAR(50)  = N'Form Response - Create Milestone';
DECLARE @StepName     NVARCHAR(50)  = N'Create Milestone from Form Response';
DECLARE @SqlStatement NVARCHAR(1000)= N'EXEC api_custom_FormResponses_CreateMilestones @DomainID = 1';

-- Owner: reuse an existing Process_Manager user so this is valid on any system.
DECLARE @Mgr INT;
SELECT TOP (1) @Mgr = p.[Process_Manager]
FROM   dbo.dp_Processes p
WHERE  p.[Process_Manager] IS NOT NULL
ORDER BY p.[Process_ID];

IF @Mgr IS NULL
BEGIN
    RAISERROR('No existing Process_Manager user was found to own the new process; set @Mgr explicitly.', 16, 1);
    RETURN;
END

/*---------------------------------------------------------------------------------
  1) The Process (fires on Form_Responses CREATE, only for milestone-mapped forms)
---------------------------------------------------------------------------------*/
DECLARE @Process_ID INT;
SELECT @Process_ID = [Process_ID] FROM dbo.dp_Processes WHERE [Process_Name] = @ProcessName;

IF @Process_ID IS NULL
BEGIN
    INSERT INTO dbo.dp_Processes
        ( [Process_Name], [Process_Manager], [Active], [Description],
          [Trigger_Fields], [Dependent_Condition],
          [Trigger_On_Create], [Trigger_On_Update], [Table_Name], [Domain_ID] )
    VALUES
        ( @ProcessName, @Mgr, 1,
          N'When a Form Response is created for a Form mapped in Milestone_Forms, create the matching Participant_Milestone.',
          N'Form_Response_ID',
          -- Only fire for responses whose Form is mapped in Milestone_Forms.
          N'Form_Response_ID > 0 AND EXISTS (SELECT 1 FROM Milestone_Forms mf WHERE mf.Form_ID = Form_Responses.Form_ID)',
          1,   -- Trigger_On_Create  (fire when a response is created)
          0,   -- Trigger_On_Update  (creation only, per requirement)
          N'Form_Responses',
          @Domain_ID );

    SET @Process_ID = SCOPE_IDENTITY();
END

/*---------------------------------------------------------------------------------
  2) The SQL step (Type 3) that runs the stored procedure
---------------------------------------------------------------------------------*/
IF NOT EXISTS (SELECT 1 FROM dbo.dp_Process_Steps
               WHERE [Process_ID] = @Process_ID AND [Process_Step_Type_ID] = 3)
    INSERT INTO dbo.dp_Process_Steps
        ( [Step_Name], [Instructions], [Process_Step_Type_ID], [Escalation_Only],
          [Order], [Process_ID], [Specific_User], [Supervisor_User],
          [SQL_Statement], [Domain_ID] )
    VALUES
        ( @StepName,
          N'Runs the stored procedure that inserts Participant_Milestones for milestone-mapped form responses.',
          3,        -- Process_Step_Type_ID 3 = SQL (same as process 24)
          0,        -- Escalation_Only
          1,        -- Order
          @Process_ID,
          @Mgr,     -- Specific_User (same convention as processes 21/24)
          0,        -- Supervisor_User
          @SqlStatement,
          @Domain_ID );

/*---------------------------------------------------------------------------------
  3) Verification
---------------------------------------------------------------------------------*/
SELECT [Process_ID], [Process_Name], [Active], [Table_Name],
       [Trigger_On_Create], [Trigger_On_Update], [Trigger_Fields], [Dependent_Condition]
FROM   dbo.dp_Processes
WHERE  [Process_ID] = @Process_ID;

SELECT [Process_Step_ID], [Step_Name], [Process_Step_Type_ID], [Order], [SQL_Statement]
FROM   dbo.dp_Process_Steps
WHERE  [Process_ID] = @Process_ID
ORDER BY [Order];
GO
