/**************************************************************************************
    Script: create_proc_FormResponse_CreateMilestone.sql
    Proc:   dbo.api_custom_FormResponses_CreateMilestones

    Purpose: Invoked by a MinistryPlatform Process (SQL step) when a Form_Responses
             record is created. For each milestone-mapped Form_Response that does not yet
             have a "Milestone Assigned" (Participant_Milestones) record, create one.

    WHY A "SWEEP" (set-based) PROC AND NOT A SINGLE-RECORD PROC:
      MinistryPlatform Process SQL steps run a fixed statement (e.g. process 24's
      "EXEC service_stmt_maintenance @DomainID = 1") -- the engine does NOT pass the
      triggering record's key into the SQL step. So, like MP's own service_* procs,
      this proc finds its own work: it processes every eligible, not-yet-recorded
      response. It is idempotent (safe to run repeatedly); the guarding process only
      fires it for Form_Responses tied to a Milestone_Forms row.

    Rules applied per response:
      - Milestone comes from the Milestone_Forms row for the response's Form.
      - Program comes from the related Form (Forms.Program_ID). Program_ID is REQUIRED
        on Participant_Milestones; responses whose Form has no Program are skipped and an
        administrator is alerted (raised error the Process logs) -- see the final check.
      - Participant resolved from the Form_Response's Contact (the form forced login):
        Contacts.Participant_Record, else the Participants row for that Contact.
      - Date_Accomplished = now; At_Prior_Church = 0, Followed_Up = 0, Discontinue_Journey = 0.

    Notes:
      - Called from a Process step (server-side), not the public API -> not registered in
        dp_API_Procedures.
      - First run will backfill milestones for any EXISTING responses to milestone forms.
        If that isn't desired, add a Response_Date cutoff to the WHERE clause.

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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE [dbo].[api_custom_FormResponses_CreateMilestones]
    @DomainID INT = 1
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT OFF;   -- let created rows persist even if the alert below raises

    ------------------------------------------------------------------------------
    -- Create the "Milestone Assigned" record for each eligible, not-yet-recorded
    -- milestone-mapped Form_Response.
    ------------------------------------------------------------------------------
    INSERT INTO dbo.Participant_Milestones
        ( [Participant_ID], [Milestone_ID], [Program_ID], [Date_Accomplished],
          [At_Prior_Church], [Followed_Up], [Discontinue_Journey], [Domain_ID] )
    SELECT
        x.[Participant_ID], x.[Milestone_ID], x.[Program_ID], GETDATE(),
        0, 0, 0, @DomainID
    FROM (
        SELECT
            mf.[Milestone_ID],
            f.[Program_ID],
            COALESCE(
                (SELECT c.[Participant_Record] FROM dbo.Contacts c WHERE c.[Contact_ID] = fr.[Contact_ID]),
                (SELECT TOP (1) p.[Participant_ID] FROM dbo.Participants p
                 WHERE p.[Contact_ID] = fr.[Contact_ID] ORDER BY p.[Participant_ID])
            ) AS [Participant_ID]
        FROM dbo.Form_Responses fr
        JOIN dbo.Milestone_Forms mf ON mf.[Form_ID] = fr.[Form_ID]
        JOIN dbo.Forms          f  ON f.[Form_ID]  = fr.[Form_ID]
        WHERE fr.[Contact_ID]  IS NOT NULL     -- form forced login, so this is set
          AND f.[Program_ID]   IS NOT NULL     -- required; no program -> handled below
    ) x
    WHERE x.[Participant_ID] IS NOT NULL
      AND NOT EXISTS (
          SELECT 1 FROM dbo.Participant_Milestones pm
          WHERE pm.[Participant_ID] = x.[Participant_ID]
            AND pm.[Milestone_ID]   = x.[Milestone_ID]
      );

    ------------------------------------------------------------------------------
    -- Explicit admin alert: a milestone-mapped Form with NO Program can't create a
    -- milestone (Program_ID is required). Raise so the Process surfaces it to staff.
    ------------------------------------------------------------------------------
    IF EXISTS (
        SELECT 1
        FROM dbo.Form_Responses fr
        JOIN dbo.Milestone_Forms mf ON mf.[Form_ID] = fr.[Form_ID]
        JOIN dbo.Forms          f  ON f.[Form_ID]  = fr.[Form_ID]
        WHERE fr.[Contact_ID] IS NOT NULL
          AND f.[Program_ID]  IS NULL
    )
    BEGIN
        RAISERROR('One or more milestone-mapped Forms have no Program_ID, so their responses could not create milestones. Set a Program on the affected Form(s).', 16, 1);
        RETURN;
    END
END
GO

/*---------------------------------------------------------------------------------
  Optional manual run:
  EXEC dbo.api_custom_FormResponses_CreateMilestones @DomainID = 1;
---------------------------------------------------------------------------------*/
