/**************************************************************************************
    Script: deploy_JourneyMilestonesIndividual.sql
    Proc:   dbo.api_custom_JourneyMilestonesIndividual_JSON  (READ)

    Purpose: For the signed-in user, return every milestone in a Journey
             (identified by @JourneyID), in Sort_Order, with:
               - the milestone's Icon and Title,
               - whether the user has ACHIEVED it (and the date, if so),
               - and when NOT achieved, the correct Form and/or Event for the parish running the
                 journey (the Congregation that owns @GroupID), via the Milestone_Forms
                 row for that congregation + milestone. Participants may attend a parish
                 other than their home parish, so their household is NOT used.

    Consumed by: perpetual-style login-gated widget (journey-milestones.html) via the
                 public CustomWidget endpoint:
                 https://<host>.cloudapps.ministryplatform.cloud/sky/api/CustomWidget

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

    This script does the standard three things (same pattern as the Perpetual
    Adoration deploy scripts):
      1. CREATE OR ALTER the procedure.
      2. Register it in dp_API_Procedures so the API may call it.
      3. Link it to the Administrators role in dp_Role_API_Procedures.
    All steps are guarded / idempotent and safe to re-run. Verification queries at end.

    NOTE ON IDENTITY: the widget passes @Username = the signed-in user's IdToken 'sub'
    (same value the adoration widget passed). The proc resolves it against
    dp_Users.User_GUID or dp_Users.User_Name.

    NOTE ON GROUP GATE (optional): if @GroupID is supplied, the signed-in user must be
    a CURRENT participant of that group or the proc returns an empty array. Leave
    @GroupID unset (NULL) to show the journey to any signed-in user (original behavior).
**************************************************************************************/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*=================================================================================
  1) The procedure
=================================================================================*/
CREATE OR ALTER PROCEDURE [dbo].[api_custom_JourneyMilestonesIndividual_JSON]
    @DomainID    INT            = NULL,               -- supplied automatically by the CustomWidget API
    @Username    NVARCHAR(255)  = NULL,               -- signed-in user's IdToken 'sub' (User_GUID or User_Name)
    @JourneyID   INT            = NULL,               -- which Journey to display (Journeys.Journey_ID)
    @GroupID     INT            = NULL                -- if set, user must be a CURRENT participant of this group
AS
BEGIN
    SET NOCOUNT ON;

    ----------------------------------------------------------------------------
    -- Resolve the signed-in user -> Contact -> Participant.
    ----------------------------------------------------------------------------
    DECLARE @UserID          INT,
            @ContactID        INT,
            @CongregationID   INT,
            @ParticipantID    INT,
            @IsGroupMember    BIT = 0;

    SELECT TOP (1)
           @UserID    = u.[User_ID],
           @ContactID = u.[Contact_ID]
    FROM   dbo.dp_Users u
    WHERE  CONVERT(NVARCHAR(50), u.[User_GUID]) = @Username
        OR u.[User_Name] = @Username
    ORDER BY u.[User_ID];

    IF @ContactID IS NOT NULL
    BEGIN
        SELECT @ParticipantID = c.[Participant_Record]
        FROM   dbo.Contacts c
        WHERE  c.[Contact_ID] = @ContactID;

        -- Fall back to the Participants table if the Contact has no Participant_Record set.
        IF @ParticipantID IS NULL
            SELECT TOP (1) @ParticipantID = p.[Participant_ID]
            FROM   dbo.Participants p
            WHERE  p.[Contact_ID] = @ContactID
            ORDER BY p.[Participant_ID];
    END

    ----------------------------------------------------------------------------
    -- Parish for the "Get Started" form links: inferred from the Congregation that
    -- owns the journey's group (@GroupID). Participants may attend a parish other
    -- than their home parish, so we do NOT use their household congregation.
    ----------------------------------------------------------------------------
    SELECT @CongregationID = g.[Congregation_ID]
    FROM   dbo.Groups g
    WHERE  g.[Group_ID] = @GroupID;

    ----------------------------------------------------------------------------
    -- Group participation gate (optional): if a group is configured, the signed-in
    -- user must be a CURRENT participant of it; otherwise nothing is returned.
    -- Leave @GroupID unset (NULL) to disable this gate.
    ----------------------------------------------------------------------------
    IF @GroupID IS NOT NULL
    BEGIN
        IF @ParticipantID IS NOT NULL AND EXISTS (
                SELECT 1
                FROM   dbo.Group_Participants gp
                WHERE  gp.[Participant_ID] = @ParticipantID
                  AND  gp.[Group_ID]       = @GroupID
                  AND  (gp.[Start_Date] IS NULL OR gp.[Start_Date] <= GETDATE())
                  AND  (gp.[End_Date]   IS NULL OR gp.[End_Date]    > GETDATE()))
            SET @IsGroupMember = 1;

        -- Configured group, but the user is not a current member -> return empty.
        IF @IsGroupMember = 0
        BEGIN
            SELECT N'[]' AS JsonResult;
            RETURN;
        END
    END

    ----------------------------------------------------------------------------
    -- Build the milestone rows (ordered by Sort_Order) as a JSON array.
    ----------------------------------------------------------------------------
    SELECT ISNULL((
        SELECT
            m.[Milestone_ID]                                    AS Milestone_ID,
            m.[Milestone_Title]                                 AS Milestone_Title,
            m.[Icon]                                            AS Icon,
            m.[Sort_Order]                                      AS Sort_Order,
            CASE WHEN pm.[Participant_Milestone_ID] IS NOT NULL THEN 1 ELSE 0 END AS Achieved,
            CONVERT(VARCHAR(10), pm.[Date_Accomplished], 23)    AS Date_Accomplished,   -- yyyy-mm-dd or null
            f.[Form_ID]                                         AS Form_ID,
            f.[Form_Title]                                      AS Form_Title,
            CONVERT(NVARCHAR(50), f.[Form_GUID])                AS Form_GUID,
            mf.[Event_ID]                                       AS Event_ID,
            e.[Event_Title]                                     AS Event_Title
        FROM dbo.Milestones m
        -- LEFT JOIN so EVERY milestone in the journey is returned, even for a user who
        -- has no Participant_Milestones yet (those milestones come back uncompleted).
        -- The correlated key selects that user's most recent record for the milestone
        -- (a dated record first, then the newest), so a milestone never doubles up.
        LEFT JOIN dbo.Participant_Milestones pm
               ON  pm.[Milestone_ID]   = m.[Milestone_ID]
              AND  pm.[Participant_ID] = @ParticipantID
              AND  pm.[Participant_Milestone_ID] = (
                     SELECT TOP (1) x.[Participant_Milestone_ID]
                     FROM   dbo.Participant_Milestones x
                     WHERE  x.[Participant_ID] = @ParticipantID
                       AND  x.[Milestone_ID]  = m.[Milestone_ID]
                     ORDER BY CASE WHEN x.[Date_Accomplished] IS NULL THEN 1 ELSE 0 END,
                              x.[Date_Accomplished] DESC,
                              x.[Participant_Milestone_ID] DESC)
        -- The form assigned to this milestone for the user's parish (if any).
        LEFT JOIN dbo.Milestone_Forms mf
               ON mf.[Milestone_ID]    = m.[Milestone_ID]
              AND mf.[Congregation_ID] = @CongregationID
        LEFT JOIN dbo.Forms f
               ON f.[Form_ID] = mf.[Form_ID]
        -- Optional Event that fulfills this milestone at this congregation.
        LEFT JOIN dbo.Events e
               ON e.[Event_ID] = mf.[Event_ID]
        WHERE m.[Journey_ID]   = @JourneyID
          AND m.[Discontinued] = 0
        ORDER BY
            CASE WHEN m.[Sort_Order] IS NULL THEN 1 ELSE 0 END,
            m.[Sort_Order],
            m.[Milestone_Title]
        FOR JSON PATH
    ), N'[]') AS JsonResult;
END
GO

/*=================================================================================
  2) Register the procedure with the MinistryPlatform API
=================================================================================*/
IF NOT EXISTS (SELECT 1 FROM dbo.dp_API_Procedures WHERE [Procedure_Name] = N'api_custom_JourneyMilestonesIndividual_JSON')
    INSERT INTO dbo.dp_API_Procedures ([Procedure_Name], [Description])
    VALUES (N'api_custom_JourneyMilestonesIndividual_JSON',
            N'Returns a Journey''s milestones with the signed-in user''s completion status/date and the correct parish form link.');
GO

/*=================================================================================
  3) Link the procedure to the Administrators role
=================================================================================*/
IF NOT EXISTS (
        SELECT 1
        FROM   dbo.dp_Role_API_Procedures rap
        JOIN   dbo.dp_API_Procedures ap ON ap.[API_Procedure_ID] = rap.[API_Procedure_ID]
        JOIN   dbo.dp_Roles          r  ON r.[Role_ID]           = rap.[Role_ID]
        WHERE  ap.[Procedure_Name] = N'api_custom_JourneyMilestonesIndividual_JSON'
          AND  r.[Role_Name]       = N'Administrators')
    INSERT INTO dbo.dp_Role_API_Procedures ([Role_ID], [API_Procedure_ID], [Domain_ID])
    SELECT r.[Role_ID], ap.[API_Procedure_ID], 1                 -- Domain_ID is required (NOT NULL); 1 = default domain
    FROM   dbo.dp_Roles r
    CROSS JOIN dbo.dp_API_Procedures ap
    WHERE  r.[Role_Name]       = N'Administrators'
      AND  ap.[Procedure_Name] = N'api_custom_JourneyMilestonesIndividual_JSON';
GO

/*=================================================================================
  4) Verification (each should return a row)
=================================================================================*/
SELECT 'Proc exists'   AS Check_Name WHERE EXISTS (SELECT 1 FROM sys.objects WHERE [name] = N'api_custom_JourneyMilestonesIndividual_JSON' AND [type] = 'P');
SELECT 'API registered' AS Check_Name WHERE EXISTS (SELECT 1 FROM dbo.dp_API_Procedures WHERE [Procedure_Name] = N'api_custom_JourneyMilestonesIndividual_JSON');
SELECT 'Role linked'    AS Check_Name WHERE EXISTS (
        SELECT 1 FROM dbo.dp_Role_API_Procedures rap
        JOIN dbo.dp_API_Procedures ap ON ap.[API_Procedure_ID] = rap.[API_Procedure_ID]
        JOIN dbo.dp_Roles r ON r.[Role_ID] = rap.[Role_ID]
        WHERE ap.[Procedure_Name] = N'api_custom_JourneyMilestonesIndividual_JSON' AND r.[Role_Name] = N'Administrators');
GO

/*---------------------------------------------------------------------------------
  Optional: preview the JSON for a known user (replace the GUID/username).
  EXEC dbo.api_custom_JourneyMilestonesIndividual_JSON @Username = N'<User_GUID or User_Name>', @JourneyID = <Journey_ID>;
---------------------------------------------------------------------------------*/
