/**************************************************************************************
    Script: deploy_JourneyMilestonesFamily.sql
    Proc:   dbo.api_custom_JourneyMilestonesFamily_JSON  (READ)

    Purpose: The "family" companion to api_custom_JourneyMilestonesIndividual_JSON.
             It returns the SIGNED-IN USER'S OWN HOUSEHOLD members who are current
             participants of a GROUP (the group is chosen in the widget's configuration
             section) and, for each, the full list of milestones in a Journey
             (identified by @JourneyID) in Sort_Order, with:
               - the milestone's Icon and Title,
               - whether THAT member has ACHIEVED it (and the date, if so),
               - and 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. Members may attend a parish other than
                 their home parish, so their household is NOT used.

             The result is a JSON ARRAY of members, each with a nested "Milestones"
             array, so the widget can render one section per member.

    Consumed by: login-gated widget (journey-milestones-family.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 individual /
    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.

    NOTES ON PARAMETERS:
      - @Username (the signed-in user's IdToken 'sub') resolves to a Contact -> Household,
        and ONLY that household's members are returned. So each parishioner sees just
        their own family, never other families in the group.
      - @JourneyID (Journeys.Journey_ID) selects the journey; @GroupID limits members to
        current participants of that group AND supplies the parish (its Congregation)
        used to resolve the form/event links. Both are IDs from the widget.
**************************************************************************************/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*=================================================================================
  1) The procedure
=================================================================================*/
CREATE OR ALTER PROCEDURE [dbo].[api_custom_JourneyMilestonesFamily_JSON]
    @DomainID    INT            = NULL,               -- supplied automatically by the CustomWidget API
    @Username    NVARCHAR(255)  = NULL,               -- signed-in user's IdToken 'sub' (parity only; not used to pick members)
    @JourneyID   INT            = NULL,               -- which Journey to display (Journeys.Journey_ID)
    @GroupID     INT            = NULL                -- the group whose current participants are shown
AS
BEGIN
    SET NOCOUNT ON;

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

    -- Resolve the signed-in user's household (family). Only members of THIS household
    -- are returned, so a parishioner sees just their own family's participants.
    DECLARE @HouseholdID INT;
    SELECT TOP (1) @HouseholdID = c.[Household_ID]
    FROM   dbo.dp_Users u
    JOIN   dbo.Contacts c ON c.[Contact_ID] = u.[Contact_ID]
    WHERE  CONVERT(NVARCHAR(50), u.[User_GUID]) = @Username
        OR u.[User_Name] = @Username
    ORDER BY u.[User_ID];

    ----------------------------------------------------------------------------
    -- Build the members array. Each member carries a nested Milestones array.
    -- JSON_QUERY(ISNULL(... , '[]')) keeps Milestones as a real JSON array even
    -- when a member happens to have no milestones in this journey.
    ----------------------------------------------------------------------------
    SELECT ISNULL((
        SELECT
            p.[Participant_ID]                          AS Participant_ID,
            c.[Contact_ID]                              AS Contact_ID,
            c.[Display_Name]                            AS Display_Name,
            c.[Nickname]                                AS Nickname,
            c.[First_Name]                              AS First_Name,
            c.[Last_Name]                               AS Last_Name,
            JSON_QUERY(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 for each member,
                -- even one with no Participant_Milestones yet (comes back uncompleted).
                -- The correlated key selects that member's most recent record for the
                -- milestone (a dated record first, then the newest) to avoid duplicates.
                LEFT JOIN dbo.Participant_Milestones pm
                       ON  pm.[Milestone_ID]   = m.[Milestone_ID]
                      AND  pm.[Participant_ID] = p.[Participant_ID]
                      AND  pm.[Participant_Milestone_ID] = (
                             SELECT TOP (1) x.[Participant_Milestone_ID]
                             FROM   dbo.Participant_Milestones x
                             WHERE  x.[Participant_ID] = p.[Participant_ID]
                               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 journey'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 Milestones
        FROM dbo.Group_Participants gp
        JOIN dbo.Participants p ON p.[Participant_ID] = gp.[Participant_ID]
        JOIN dbo.Contacts     c ON c.[Contact_ID]     = p.[Contact_ID]
        WHERE gp.[Group_ID] = @GroupID
          -- Only the signed-in user's own household (family).
          AND c.[Household_ID] = @HouseholdID
          -- "current" membership: started, and not yet ended
          AND (gp.[Start_Date] IS NULL OR gp.[Start_Date] <= GETDATE())
          AND (gp.[End_Date]   IS NULL OR gp.[End_Date]    > GETDATE())
          -- exclude deceased contacts (Contact_Status_ID = 3)
          AND (c.[Contact_Status_ID] IS NULL OR c.[Contact_Status_ID] <> 3)
        ORDER BY c.[Last_Name], c.[First_Name], c.[Display_Name]
        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_JourneyMilestonesFamily_JSON')
    INSERT INTO dbo.dp_API_Procedures ([Procedure_Name], [Description])
    VALUES (N'api_custom_JourneyMilestonesFamily_JSON',
            N'Returns every current participant of a group, each with the Journey''s milestones, their 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_JourneyMilestonesFamily_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_JourneyMilestonesFamily_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_JourneyMilestonesFamily_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_JourneyMilestonesFamily_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_JourneyMilestonesFamily_JSON' AND r.[Role_Name] = N'Administrators');
GO

/*---------------------------------------------------------------------------------
  Optional: preview the JSON for a known group (replace the id or name).
  EXEC dbo.api_custom_JourneyMilestonesFamily_JSON @JourneyID = <Journey_ID>, @GroupID = <Group_ID>;
---------------------------------------------------------------------------------*/
