/*==============================================================================
  deploy_PersonnelDirectory.sql
  ------------------------------------------------------------------------------
  Data layer for the Personnel Directory custom widget -- the third page in the
  Organization Directory suite, alongside the Organization Finder and the
  Organization Detail page.

  WHAT THIS SCRIPT DOES
    1. Creates dbo.api_custom_PersonnelDirectory, which returns TWO result sets:
         DataSet1  one row per person: name, primary role and location, the
                   resolved phone number and email address
         DataSet2  that person's OTHER current assignments, one row each
    2. Registers the procedure in dp_API_Procedures
    3. Links the procedure to a security role in dp_Role_API_Procedures
    4. Runs verification queries -- the first three MUST each return a row

  BEFORE YOU RUN
    * Connect to the MinistryPlatform database (NOT master).
    * If your widget API client uses a role other than 'Administrators', change
      @RoleName. It appears in Step 2/3 and again in Step 4 -- change both.

  THINGS THE DATA TAUGHT US -- read these, they shape the output
    * A person can carry MORE THAN ONE assignment flagged Primary. Several
      clergy on the demo instance do. The procedure therefore picks one
      deterministically rather than returning duplicate people: current
      assignments first, then no end date before a dated one, then the latest
      start, then the lowest ID. Everything else current becomes an "other
      assignment" in DataSet2.
    * Assignment_End is used as the "still current" test, and future dates in it
      run to 2558 on the demo data. Current means End IS NULL OR End > today.
    * Company_Phone is empty on every Contact on the demo instance. Whichever
      phone source you configure, the procedure falls back through the remaining
      sources rather than publishing a blank card, and tells you in
      Phone_Source which one it actually used.

  PRIVACY
    Only the ONE resolved phone number is returned, never all three. A public
    directory configured for the office number must not ship mobile numbers to
    the browser, where anyone could read them out of the page source.

  COMPATIBILITY
    Written for SQL Server 2008-era compatibility levels: no TRY_CONVERT,
    STRING_SPLIT, CREATE OR ALTER, DROP IF EXISTS, IIF or CONCAT.

  SAFE TO RE-RUN. It will not create duplicates.
==============================================================================*/

SET NOCOUNT ON;
GO


IF OBJECT_ID('dbo.api_custom_PersonnelDirectory', 'P') IS NOT NULL
    DROP PROCEDURE dbo.api_custom_PersonnelDirectory;
GO

CREATE PROCEDURE [dbo].[api_custom_PersonnelDirectory]
    @DomainID   INT,
    @Username   NVARCHAR(75) = NULL,
    -- Which kinds of personnel to list, by Personnel Category ID. Run
    -- verification query 4e to list the categories on your instance.
    --   ''       = every category
    --   '2'      = clergy only
    --   '2,4'    = clergy and staff
    @PersonnelCategoryIDs NVARCHAR(500) = NULL,
    -- Which phone number to publish:
    --   1 = Company Phone from the Contact record   (default)
    --   2 = Location Phone of the primary assignment
    --   3 = Mobile Phone from the Contact record
    -- If the chosen source is empty the others are tried in the order above, and
    -- Phone_Source reports which one was used.
    @PhoneSource INT = 1,
    -- Optional. When set, an Alternate Email of this type is preferred over the
    -- Contact record's own email address. Falls back to the Contact email when
    -- the person has no alternate of that type. Leave NULL to always use the
    -- Contact email.
    @AlternateEmailTypeID INT = NULL,
    -- Optional: restrict to personnel whose PRIMARY assignment is at one of
    -- these Congregations. Leave NULL or '' for the whole diocese.
    @CongregationIDs NVARCHAR(500) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @CatList NVARCHAR(520) = NULL;
    DECLARE @CngList NVARCHAR(520) = NULL;
    DECLARE @Today   DATETIME      = GETDATE();

    IF LTRIM(RTRIM(ISNULL(@PersonnelCategoryIDs, ''))) <> ''
        SET @CatList = ',' + REPLACE(LTRIM(RTRIM(@PersonnelCategoryIDs)), ' ', '') + ',';

    IF LTRIM(RTRIM(ISNULL(@CongregationIDs, ''))) <> ''
        SET @CngList = ',' + REPLACE(LTRIM(RTRIM(@CongregationIDs)), ' ', '') + ',';

    IF @PhoneSource NOT IN (1, 2, 3) SET @PhoneSource = 1;

    /*------------------------------------------------------------------
      Current personnel, filtered by category.
    ------------------------------------------------------------------*/
    DECLARE @People TABLE (
        Personnel_ID INT PRIMARY KEY,
        Contact_ID   INT,
        Category_ID  INT
    );

    INSERT INTO @People (Personnel_ID, Contact_ID, Category_ID)
    SELECT P.Personnel_ID, P.Contact_ID, P.Personnel_Category_ID
    FROM dbo.Personnel P
    WHERE (P.End_Date IS NULL OR P.End_Date > @Today)
      AND (P.Termination_Date IS NULL OR P.Termination_Date > @Today)
      AND (@CatList IS NULL
           OR @CatList LIKE '%,' + CONVERT(VARCHAR(12), P.Personnel_Category_ID) + ',%');

    /*------------------------------------------------------------------
      Every CURRENT assignment, ranked so exactly one is treated as primary.
      Primary_Assignment is not unique in practice, so the tie is broken:
        flagged primary first, then open-ended before dated,
        then latest start, then lowest ID.
    ------------------------------------------------------------------*/
    DECLARE @Assign TABLE (
        Personnel_ID  INT,
        Assignment_ID INT,
        Role_Name     NVARCHAR(50),
        Location_ID   INT,
        Location_Name NVARCHAR(50),
        Location_Phone VARCHAR(25),
        Congregation_ID INT,
        Assignment_Type NVARCHAR(50),
        Is_Primary    BIT,
        Rank_No       INT
    );

    INSERT INTO @Assign (Personnel_ID, Assignment_ID, Role_Name, Location_ID, Location_Name,
                         Location_Phone, Congregation_ID, Assignment_Type, Is_Primary, Rank_No)
    SELECT
          A.Personnel_ID
        , A.Personnel_Assignment_ID
        , ISNULL(AR.Assignment_Role, '')
        , A.Location_ID
        , ISNULL(L.Location_Name, '')
        , ISNULL(CONVERT(VARCHAR(25), L.Phone), '')
        , L.Congregation_ID
        , ISNULL(AT.Personnel_Assignment_Type, '')
        , ISNULL(A.Primary_Assignment, 0)
        , ROW_NUMBER() OVER (
              PARTITION BY A.Personnel_ID
              ORDER BY ISNULL(A.Primary_Assignment, 0) DESC,
                       CASE WHEN A.Assignment_End IS NULL THEN 0 ELSE 1 END,
                       A.Assignment_Start DESC,
                       A.Personnel_Assignment_ID
          )
    FROM dbo.Personnel_Assignments A
    INNER JOIN @People PE                     ON PE.Personnel_ID = A.Personnel_ID
    LEFT  JOIN dbo.Assignment_Roles AR        ON AR.Assignment_Role_ID = A.Assignment_Role_ID
    LEFT  JOIN dbo.Locations L                ON L.Location_ID = A.Location_ID
    LEFT  JOIN dbo.Personnel_Assignment_Types AT
                                              ON AT.Personnel_Assignment_Type_ID = A.Personnel_Assignment_Type_ID
    WHERE (A.Assignment_End IS NULL OR A.Assignment_End > @Today);

    /*------------------------------------------------------------------
      The final person set, after the congregation filter. Both result sets are
      driven from this, so DataSet2 can never ship assignment rows for somebody
      DataSet1 left out.
    ------------------------------------------------------------------*/
    DECLARE @Final TABLE (Personnel_ID INT PRIMARY KEY);

    INSERT INTO @Final (Personnel_ID)
    SELECT PE.Personnel_ID
    FROM @People PE
    LEFT JOIN @Assign PR ON PR.Personnel_ID = PE.Personnel_ID AND PR.Rank_No = 1
    WHERE (@CngList IS NULL
           OR (PR.Congregation_ID IS NOT NULL
               AND @CngList LIKE '%,' + CONVERT(VARCHAR(12), PR.Congregation_ID) + ',%'));

    /*------------------------------------------------------------------
      DataSet1 -- one row per person.
    ------------------------------------------------------------------*/
    SELECT
          PE.Personnel_ID
        , C.Contact_ID
        , ISNULL(C.Display_Name, '')                    AS Display_Name
        , ISNULL(C.Nickname, ISNULL(C.First_Name, ''))  AS First_Name
        , ISNULL(C.Last_Name, '')                       AS Last_Name
        , ISNULL(PC.Personnel_Category, '')             AS Personnel_Category
        , PE.Category_ID                                AS Personnel_Category_ID
        , ISNULL(PR.Role_Name, '')                      AS Primary_Role
        , ISNULL(PR.Location_Name, '')                  AS Primary_Location
        , ISNULL(PR.Congregation_ID, 0)                 AS Primary_Congregation_ID
        , ISNULL(PR.Assignment_Type, '')                AS Primary_Assignment_Type
        -- The one resolved phone number, with the configured source preferred
        -- and the others used only as a fallback.
        , CASE @PhoneSource
              WHEN 1 THEN COALESCE(NULLIF(LTRIM(RTRIM(CONVERT(VARCHAR(25), C.Company_Phone))), ''),
                                   NULLIF(PR.Location_Phone, ''),
                                   NULLIF(LTRIM(RTRIM(CONVERT(VARCHAR(25), C.Mobile_Phone))), ''), '')
              WHEN 2 THEN COALESCE(NULLIF(PR.Location_Phone, ''),
                                   NULLIF(LTRIM(RTRIM(CONVERT(VARCHAR(25), C.Company_Phone))), ''),
                                   NULLIF(LTRIM(RTRIM(CONVERT(VARCHAR(25), C.Mobile_Phone))), ''), '')
              ELSE        COALESCE(NULLIF(LTRIM(RTRIM(CONVERT(VARCHAR(25), C.Mobile_Phone))), ''),
                                   NULLIF(LTRIM(RTRIM(CONVERT(VARCHAR(25), C.Company_Phone))), ''),
                                   NULLIF(PR.Location_Phone, ''), '')
          END                                           AS Phone
        -- Which source the number above actually came from, so a blank or an
        -- unexpected number can be traced without guessing.
        , CASE
              WHEN @PhoneSource = 1 AND LTRIM(RTRIM(ISNULL(CONVERT(VARCHAR(25), C.Company_Phone), ''))) <> '' THEN 'Company'
              WHEN @PhoneSource = 2 AND ISNULL(PR.Location_Phone, '') <> ''                                   THEN 'Location'
              WHEN @PhoneSource = 3 AND LTRIM(RTRIM(ISNULL(CONVERT(VARCHAR(25), C.Mobile_Phone), ''))) <> ''   THEN 'Mobile'
              WHEN LTRIM(RTRIM(ISNULL(CONVERT(VARCHAR(25), C.Company_Phone), ''))) <> ''                       THEN 'Company (fallback)'
              WHEN ISNULL(PR.Location_Phone, '') <> ''                                                        THEN 'Location (fallback)'
              WHEN LTRIM(RTRIM(ISNULL(CONVERT(VARCHAR(25), C.Mobile_Phone), ''))) <> ''                        THEN 'Mobile (fallback)'
              ELSE 'None'
          END                                           AS Phone_Source
        -- The alternate email of the configured type wins where one exists.
        , COALESCE(NULLIF(LTRIM(RTRIM(AE.Email_Address)), ''),
                   NULLIF(LTRIM(RTRIM(C.Email_Address)), ''), '')  AS Email
        , CASE
              WHEN NULLIF(LTRIM(RTRIM(AE.Email_Address)), '') IS NOT NULL THEN 'Alternate'
              WHEN NULLIF(LTRIM(RTRIM(C.Email_Address)), '')  IS NOT NULL THEN 'Contact'
              ELSE 'None'
          END                                           AS Email_Source
        -- How many further current assignments this person has, so the widget
        -- can decide whether to render an "Other assignments" block at all.
        , (SELECT COUNT(*) FROM @Assign X
            WHERE X.Personnel_ID = PE.Personnel_ID AND X.Rank_No > 1)  AS Other_Assignment_Count
    FROM @Final F
    INNER JOIN @People PE                ON PE.Personnel_ID = F.Personnel_ID
    INNER JOIN dbo.Contacts C            ON C.Contact_ID = PE.Contact_ID
    LEFT  JOIN dbo.Personnel_Categories PC
                                         ON PC.Personnel_Category_ID = PE.Category_ID
    LEFT  JOIN @Assign PR                ON PR.Personnel_ID = PE.Personnel_ID AND PR.Rank_No = 1
    LEFT  JOIN dbo.Alternate_Emails AE   ON AE.Contact_ID = C.Contact_ID
                                        AND @AlternateEmailTypeID IS NOT NULL
                                        AND AE.Alternate_Email_Type_ID = @AlternateEmailTypeID
    ORDER BY ISNULL(C.Last_Name, ''), ISNULL(C.First_Name, '');

    /*------------------------------------------------------------------
      DataSet2 -- the other current assignments, one row each.
    ------------------------------------------------------------------*/
    SELECT
          A.Personnel_ID
        , A.Assignment_ID
        , A.Role_Name        AS Assignment_Role
        , A.Location_Name    AS Assignment_Location
        -- Lets the widget link each location back to the Organization Detail
        -- page, the same way it links the primary assignment.
        , ISNULL(A.Congregation_ID, 0) AS Assignment_Congregation_ID
        , A.Assignment_Type
    FROM @Assign A
    INNER JOIN @Final F ON F.Personnel_ID = A.Personnel_ID
    WHERE A.Rank_No > 1
    ORDER BY A.Personnel_ID, A.Rank_No;
END
GO


/*==============================================================================
  STEP 2 & 3 -- Register the procedure and link it to a role
==============================================================================*/
DECLARE @ProcName NVARCHAR(128) = N'api_custom_PersonnelDirectory';
DECLARE @ProcDesc NVARCHAR(255) = N'Personnel Directory widget: current personnel with primary role, location, phone, email and other assignments.';
DECLARE @RoleName NVARCHAR(50)  = N'Administrators';   -- <<< change here if needed

DECLARE @procPK SYSNAME, @linkFK SYSNAME, @domainID INT,
        @cols NVARCHAR(400), @vals NVARCHAR(400), @sql NVARCHAR(MAX);

SET @domainID = 1;
IF OBJECT_ID('dbo.dp_Domains', 'U') IS NOT NULL
    SELECT @domainID = ISNULL(MIN(Domain_ID), 1) FROM dbo.dp_Domains;

SELECT @procPK = name FROM sys.columns
 WHERE object_id = OBJECT_ID('dbo.dp_API_Procedures') AND is_identity = 1;
SELECT @linkFK = name FROM sys.columns
 WHERE object_id = OBJECT_ID('dbo.dp_Role_API_Procedures') AND name LIKE '%Procedure[_]ID';

IF @procPK IS NULL OR @linkFK IS NULL
BEGIN
    RAISERROR('Could not inspect dp_API_Procedures / dp_Role_API_Procedures. Are you connected to the MinistryPlatform database?', 16, 1);
    RETURN;
END

PRINT 'Using identity column ' + @procPK + ', link column ' + @linkFK + ', Domain_ID ' + CONVERT(VARCHAR(12), @domainID) + '.';

IF NOT EXISTS (SELECT 1 FROM dbo.dp_API_Procedures WHERE Procedure_Name = @ProcName)
BEGIN
    SET @cols = N'Procedure_Name';
    SET @vals = N'@p';

    IF EXISTS (SELECT 1 FROM sys.columns
               WHERE object_id = OBJECT_ID('dbo.dp_API_Procedures') AND name = 'Description')
    BEGIN
        SET @cols = @cols + N', Description';
        SET @vals = @vals + N', @d';
    END

    IF EXISTS (SELECT 1 FROM sys.columns
               WHERE object_id = OBJECT_ID('dbo.dp_API_Procedures') AND name = 'Domain_ID')
    BEGIN
        SET @cols = @cols + N', Domain_ID';
        SET @vals = @vals + N', @dom';
    END

    SET @sql = N'INSERT INTO dbo.dp_API_Procedures (' + @cols + N') VALUES (' + @vals + N');';
    EXEC sp_executesql @sql,
         N'@p NVARCHAR(128), @d NVARCHAR(255), @dom INT',
         @p = @ProcName, @d = @ProcDesc, @dom = @domainID;

    PRINT 'Registered ' + @ProcName + ' in dp_API_Procedures.';
END
ELSE
    PRINT @ProcName + ' was already registered in dp_API_Procedures.';

SET @cols = N'Role_ID, ' + QUOTENAME(@linkFK);
SET @vals = N'r.Role_ID, ap.' + QUOTENAME(@procPK);

IF EXISTS (SELECT 1 FROM sys.columns
           WHERE object_id = OBJECT_ID('dbo.dp_Role_API_Procedures') AND name = 'Domain_ID')
BEGIN
    SET @cols = @cols + N', Domain_ID';
    SET @vals = @vals + N', @dom';
END

SET @sql = N'
IF NOT EXISTS (
    SELECT 1
    FROM dbo.dp_Role_API_Procedures rap
    INNER JOIN dbo.dp_API_Procedures ap ON ap.' + QUOTENAME(@procPK) + N' = rap.' + QUOTENAME(@linkFK) + N'
    INNER JOIN dbo.dp_Roles r           ON r.Role_ID = rap.Role_ID
    WHERE ap.Procedure_Name = @p AND r.Role_Name = @r)
BEGIN
    INSERT INTO dbo.dp_Role_API_Procedures (' + @cols + N')
    SELECT ' + @vals + N'
    FROM dbo.dp_Roles r
    CROSS JOIN dbo.dp_API_Procedures ap
    WHERE r.Role_Name = @r AND ap.Procedure_Name = @p;
END';

EXEC sp_executesql @sql,
     N'@p NVARCHAR(128), @r NVARCHAR(50), @dom INT',
     @p = @ProcName, @r = @RoleName, @dom = @domainID;
GO


/*==============================================================================
  STEP 4 -- Verification. 4a, 4b and 4c must EACH return one row.
==============================================================================*/
DECLARE @ProcName NVARCHAR(128) = N'api_custom_PersonnelDirectory';
DECLARE @RoleName NVARCHAR(50)  = N'Administrators';
DECLARE @procPK SYSNAME, @linkFK SYSNAME, @sql NVARCHAR(MAX);

SELECT @procPK = name FROM sys.columns
 WHERE object_id = OBJECT_ID('dbo.dp_API_Procedures') AND is_identity = 1;
SELECT @linkFK = name FROM sys.columns
 WHERE object_id = OBJECT_ID('dbo.dp_Role_API_Procedures') AND name LIKE '%Procedure[_]ID';

-- 4a
SELECT 'Proc exists' AS Check_Name, SCHEMA_NAME(schema_id) AS [Schema], name AS Procedure_Name
FROM sys.procedures WHERE name = @ProcName;

-- 4b  (expect a Name_Length of 29)
SELECT 'API registered' AS Check_Name,
       Procedure_Name AS Registered_As,
       DATALENGTH(Procedure_Name)/2 AS Name_Length,
       CASE WHEN Procedure_Name = @ProcName COLLATE Latin1_General_BIN
            THEN 'OK' ELSE 'MISMATCH - check for spaces or casing' END AS Name_Clean
FROM dbo.dp_API_Procedures
WHERE LTRIM(RTRIM(Procedure_Name)) = @ProcName;

-- 4c
SET @sql = N'
SELECT ''Role linked'' AS Check_Name, r.Role_Name, ap.Procedure_Name
FROM dbo.dp_Role_API_Procedures rap
INNER JOIN dbo.dp_API_Procedures ap ON ap.' + QUOTENAME(@procPK) + N' = rap.' + QUOTENAME(@linkFK) + N'
INNER JOIN dbo.dp_Roles r           ON r.Role_ID = rap.Role_ID
WHERE ap.Procedure_Name = @p AND r.Role_Name = @r;';
EXEC sp_executesql @sql, N'@p NVARCHAR(128), @r NVARCHAR(50)', @p = @ProcName, @r = @RoleName;
GO

-- 4d. What the widget will receive, with everything at its default.
--     Watch the Phone_Source column: if it reads "(fallback)" or "None" for most
--     rows, the phone source you configured is not populated on this instance.
EXEC dbo.api_custom_PersonnelDirectory @DomainID = 1;
GO

-- 4e. The Personnel Categories on this instance with a count of current people
--     against each. These are the IDs for personnelCategoryIDs in the embed.
SELECT PC.Personnel_Category_ID AS ID,
       PC.Personnel_Category    AS Name,
       COUNT(P.Personnel_ID)    AS Current_People
FROM dbo.Personnel_Categories PC
LEFT JOIN dbo.Personnel P
       ON P.Personnel_Category_ID = PC.Personnel_Category_ID
      AND (P.End_Date IS NULL OR P.End_Date > GETDATE())
      AND (P.Termination_Date IS NULL OR P.Termination_Date > GETDATE())
GROUP BY PC.Personnel_Category_ID, PC.Personnel_Category
ORDER BY PC.Personnel_Category;
GO

-- 4f. The Alternate Email Types on this instance, for alternateEmailTypeID.
SELECT Alternate_Email_Type_ID AS ID, Display_Name AS Name,
       (SELECT COUNT(*) FROM dbo.Alternate_Emails AE
         WHERE AE.Alternate_Email_Type_ID = T.Alternate_Email_Type_ID) AS Addresses_On_File
FROM dbo.Alternate_Email_Types T
ORDER BY Display_Name;
GO
