/* ============================================================
   Mass Intention Calendar - complete database deployment
   ------------------------------------------------------------
   Run this ONE script against the MinistryPlatform database
   (e.g., MPCatholicDemo). It performs all three required steps:

     1. Creates the stored procedure
     2. Registers it as an API Procedure (dp_API_Procedures)
     3. Links it to a Security Role (dp_Role_API_Procedures)
        -- without step 3 the widget API returns:
        -- "Procedure 'X' does not exists or user does not have
        --  access to it."

   Safe to re-run: the proc uses CREATE OR ALTER and the inserts
   are guarded by IF NOT EXISTS.

   Parameters of note:
     @CongregationIDs - comma-separated list of Congregation_ID
       (parish) values. Blank/NULL returns every congregation's
       Masses (original behavior); one ID limits to a single parish;
       several IDs return a cluster of parishes. The calendar web
       page supplies this from its CONFIG section.
   ============================================================ */

------------------------------------------------------------
-- STEP 1: Stored procedure
------------------------------------------------------------
GO
CREATE OR ALTER PROCEDURE [dbo].[api_custom_MassIntentionCalendar_JSON]
    @DomainID         INT,
    @Username         NVARCHAR(75)  = NULL,   -- supplied by API when user logged in; unused
    @StartDate        DATETIME      = NULL,
    @EndDate          DATETIME      = NULL,
    -- Comma-separated list of Congregation_ID (parish) values to include.
    -- Blank/NULL = all congregations (original behavior).
    -- Single ID  = one parish  (e.g. '5')
    -- Multiple   = a cluster of parishes (e.g. '5,8,12')
    @CongregationIDs  NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    -- Defaults: today through +60 days if the page passes no range
    IF @StartDate IS NULL SET @StartDate = CAST(GETDATE() AS DATE);
    IF @EndDate   IS NULL SET @EndDate   = DATEADD(DAY, 60, @StartDate);

    -- Normalize an empty/whitespace list to NULL so the filter below
    -- treats it as "all congregations".
    IF LTRIM(RTRIM(ISNULL(@CongregationIDs, ''))) = '' SET @CongregationIDs = NULL;

    SELECT (
        SELECT
            E.Event_ID,
            E.Event_Title,
            E.Event_Start_Date,
            E.Event_End_Date,
            E.Congregation_ID,
            C.Congregation_Name,
            E.Registration_Active,
            ISNULL(R.Registrant_Count, 0) AS Registrant_Count,
            CASE WHEN ISNULL(R.Registrant_Count, 0) = 0
                 THEN 'Available' ELSE 'Reserved' END AS Intention_Status
        FROM Events E
        INNER JOIN Congregations C
            ON C.Congregation_ID = E.Congregation_ID
        OUTER APPLY (
            SELECT COUNT(*) AS Registrant_Count
            FROM Event_Participants EP
            WHERE EP.Event_ID = E.Event_ID
              -- Statuses that count as "intention made":
              -- 2 = Registered, 3 = Attended, 4 = Confirmed
              -- (excludes 1 Interested, 5 Cancelled, 20 Abandoned,
              --  21 Awaiting Payment)
              AND EP.Participation_Status_ID IN (2, 3, 4)
        ) R
        WHERE E.Event_Type_ID   = 13          -- Mass
          AND E.Cancelled       = 0
          AND ISNULL(E._Approved, 0) = 1
          -- Match what the public calendar shows; remove these two
          -- lines if ALL approved Masses should appear regardless of
          -- web visibility:
          AND ISNULL(E._Web_Approved, 0) = 1
          AND E.Visibility_Level_ID = 4       -- Public
          AND E.Event_Start_Date >= @StartDate
          AND E.Event_Start_Date <  @EndDate
          AND E.Domain_ID = @DomainID
          -- Congregation filter: NULL = all; otherwise only the listed IDs.
          -- Uses a delimited-string LIKE match (no STRING_SPLIT/TRY_CONVERT)
          -- so it works on older SQL Server compatibility levels. Spaces in
          -- the list are stripped; leading/trailing commas prevent partial
          -- matches (e.g. "5" will not match Congregation_ID 15).
          AND (
                @CongregationIDs IS NULL
                OR ',' + REPLACE(@CongregationIDs, ' ', '') + ','
                   LIKE '%,' + CAST(E.Congregation_ID AS VARCHAR(12)) + ',%'
              )
        ORDER BY E.Event_Start_Date
        FOR JSON PATH, INCLUDE_NULL_VALUES
    ) AS JsonResult;
END
GO

------------------------------------------------------------
-- STEP 2: Register as an API Procedure
------------------------------------------------------------
IF NOT EXISTS (SELECT 1 FROM dp_API_Procedures
               WHERE Procedure_Name = 'api_custom_MassIntentionCalendar_JSON')
BEGIN
    INSERT INTO dp_API_Procedures (Procedure_Name, Description)
    VALUES ('api_custom_MassIntentionCalendar_JSON',
            'Mass Intention calendar widget data');
END
GO

------------------------------------------------------------
-- STEP 3: Link the API Procedure to a Security Role
-- (Administrators role. To use a different role, change the
--  Role_Name below.)
------------------------------------------------------------
DECLARE @RoleID INT = (SELECT Role_ID FROM dp_Roles
                       WHERE Role_Name = 'Administrators');
DECLARE @APIProcID INT = (SELECT API_Procedure_ID FROM dp_API_Procedures
                          WHERE Procedure_Name = 'api_custom_MassIntentionCalendar_JSON');

IF NOT EXISTS (SELECT 1 FROM dp_Role_API_Procedures
               WHERE Role_ID = @RoleID AND API_Procedure_ID = @APIProcID)
BEGIN
    INSERT INTO dp_Role_API_Procedures (Role_ID, API_Procedure_ID, Domain_ID)
    VALUES (@RoleID, @APIProcID, 1);
END
GO

------------------------------------------------------------
-- Verification: all three queries must return a row
------------------------------------------------------------
SELECT 'Proc exists' AS Verification_Step, name
FROM sys.procedures WHERE name = 'api_custom_MassIntentionCalendar_JSON';

SELECT 'API registered' AS Verification_Step, Procedure_Name
FROM dp_API_Procedures
WHERE Procedure_Name = 'api_custom_MassIntentionCalendar_JSON';

SELECT 'Role linked' AS Verification_Step, r.Role_Name
FROM dp_Role_API_Procedures rap
JOIN dp_Roles r ON r.Role_ID = rap.Role_ID
JOIN dp_API_Procedures ap ON ap.API_Procedure_ID = rap.API_Procedure_ID
WHERE ap.Procedure_Name = 'api_custom_MassIntentionCalendar_JSON';
GO