/*==============================================================================
  deploy_PersonnelDirectory_photos.sql
  ------------------------------------------------------------------------------
  Adds personnel photographs to the Personnel Directory, taken from the DEFAULT file attached to
  each Contact record -- the image you see top-left on the Organization page
  in the Platform, marked DEFAULT in the Files panel.

  RUN THIS AFTER deploy_PersonnelDirectory.sql. It does two things:

    1. Creates the view dbo.api_custom_PersonnelDirectory_Photos, which maps each
       Contact_ID to the GUID of its default file attachment.
    2. Replaces dbo.api_custom_PersonnelDirectory so it returns that GUID as
       Photo_File_GUID. The widget turns the GUID into an image URL.

  WHY A VIEW RATHER THAN A JOIN INSIDE THE PROCEDURE

  dp_Files is an internal MinistryPlatform table and its column names have moved
  around between versions: the file GUID is variously Unique_Name or File_GUID,
  the default-image flag is Default_Image or Is_Default_Image, and older versions
  hang files off dp_Pages via Page_ID while newer ones carry Table_Name directly.
  Section 1 inspects the table, prints what it found, and builds the view to
  match. If it cannot work out the shape, it creates a view that returns no rows
  and says so -- the Personnel Directory then works exactly as before, just without
  photographs, rather than failing to deploy.

  SAFE TO RE-RUN.
==============================================================================*/

SET NOCOUNT ON;
GO

/*==============================================================================
  SECTION 1 -- Inspect dp_Files and build the logo view to match
==============================================================================*/
PRINT '--- Columns found on dp_Files ---';
SELECT c.name AS Column_Name, TYPE_NAME(c.user_type_id) AS Data_Type
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('dbo.dp_Files')
ORDER BY c.column_id;
GO

DECLARE @guidCol  SYSNAME,
        @dfltCol  SYSNAME,
        @recCol   SYSNAME,
        @tblCol   SYSNAME,
        @pageCol  SYSNAME,
        @idCol    SYSNAME,
        @where    NVARCHAR(400),
        @sql      NVARCHAR(MAX);

IF OBJECT_ID('dbo.dp_Files') IS NULL
BEGIN
    PRINT 'dp_Files was not found. Creating an empty logo view; the finder will run without photographs.';
    SET @guidCol = NULL;
END
ELSE
BEGIN
    -- The file GUID: prefer a real uniqueidentifier column, else a name that
    -- looks like one.
    SELECT TOP 1 @guidCol = c.name
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID('dbo.dp_Files')
      AND (TYPE_NAME(c.user_type_id) = 'uniqueidentifier'
           OR c.name IN ('Unique_Name','File_GUID','Unique_File_Id','dp_fileUniqueId'))
    ORDER BY CASE WHEN TYPE_NAME(c.user_type_id) = 'uniqueidentifier' THEN 0 ELSE 1 END,
             CASE c.name WHEN 'Unique_Name' THEN 0 WHEN 'File_GUID' THEN 1 ELSE 2 END;

    SELECT TOP 1 @dfltCol = c.name
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID('dbo.dp_Files') AND c.name LIKE '%Default%'
    ORDER BY c.column_id;

    SELECT TOP 1 @recCol = c.name
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID('dbo.dp_Files') AND c.name = 'Record_ID';

    SELECT TOP 1 @tblCol = c.name
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID('dbo.dp_Files') AND c.name = 'Table_Name';

    SELECT TOP 1 @pageCol = c.name
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID('dbo.dp_Files') AND c.name = 'Page_ID';

    SELECT TOP 1 @idCol = c.name
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID('dbo.dp_Files') AND c.is_identity = 1;
END

IF @guidCol IS NULL OR @recCol IS NULL
BEGIN
    PRINT 'Could not identify the GUID or Record_ID column on dp_Files.';
    PRINT 'Creating an empty logo view. Logos will be blank; everything else works.';
    SET @sql = N'
CREATE VIEW dbo.api_custom_PersonnelDirectory_Photos AS
SELECT CONVERT(INT, NULL) AS Record_ID, CONVERT(VARCHAR(50), NULL) AS Photo_GUID
WHERE 1 = 0;';
END
ELSE
BEGIN
    -- Restrict to files attached to Contacts, however this version records
    -- that association.
    IF @tblCol IS NOT NULL
        SET @where = N'f.' + QUOTENAME(@tblCol) + N' = ''Contacts''';
    ELSE IF @pageCol IS NOT NULL AND OBJECT_ID('dbo.dp_Pages') IS NOT NULL
        SET @where = N'f.' + QUOTENAME(@pageCol) + N' IN (SELECT pg.Page_ID FROM dbo.dp_Pages pg WHERE pg.Table_Name = ''Contacts'')';
    ELSE
        SET @where = N'1 = 1';   -- last resort: Record_ID alone

    PRINT 'Using GUID column: '    + @guidCol;
    PRINT 'Using default flag: '   + ISNULL(@dfltCol, '(none - will take the oldest file)');
    PRINT 'Contact filter: '  + @where;

    -- One row per contact: the file flagged DEFAULT, else the earliest file.
    SET @sql = N'
CREATE VIEW dbo.api_custom_PersonnelDirectory_Photos AS
SELECT x.Record_ID, x.Photo_GUID
FROM (
    SELECT f.' + QUOTENAME(@recCol) + N' AS Record_ID,
           CONVERT(VARCHAR(50), f.' + QUOTENAME(@guidCol) + N') AS Photo_GUID,
           ROW_NUMBER() OVER (
               PARTITION BY f.' + QUOTENAME(@recCol) + N'
               ORDER BY ' +
               CASE WHEN @dfltCol IS NOT NULL
                    THEN N'CASE WHEN f.' + QUOTENAME(@dfltCol) + N' = 1 THEN 0 ELSE 1 END, '
                    ELSE N'' END +
               ISNULL(N'f.' + QUOTENAME(@idCol), N'f.' + QUOTENAME(@recCol)) + N'
           ) AS rn
    FROM dbo.dp_Files f
    WHERE ' + @where + N'
      AND f.' + QUOTENAME(@guidCol) + N' IS NOT NULL
) x
WHERE x.rn = 1;';
END

IF OBJECT_ID('dbo.api_custom_PersonnelDirectory_Photos', 'V') IS NOT NULL
    DROP VIEW dbo.api_custom_PersonnelDirectory_Photos;

EXEC sp_executesql @sql;
PRINT 'Created view dbo.api_custom_PersonnelDirectory_Photos.';
GO


/*==============================================================================
  SECTION 2 -- Replace the procedure so it returns Photo_File_GUID
==============================================================================*/
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
        -- GUID of the DEFAULT file on the Contact record. The widget turns it
        -- into an image URL; a person with no photo falls back to initials.
        , ISNULL(GP.Photo_GUID, '')                     AS Photo_File_GUID
        , 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.api_custom_PersonnelDirectory_Photos GP
                                         ON GP.Record_ID = C.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


/*==============================================================================
  SECTION 3 -- Verify. Paste one GUID into this URL in a PRIVATE window:
    https://YOURHOST.ministryplatform.net/ministryplatformapi/files/THE-GUID
==============================================================================*/
SELECT C.Contact_ID, C.Display_Name,
       ISNULL(GP.Photo_GUID, 'NO DEFAULT FILE') AS Photo_File_GUID
FROM dbo.Personnel P
INNER JOIN dbo.Contacts C ON C.Contact_ID = P.Contact_ID
LEFT  JOIN dbo.api_custom_PersonnelDirectory_Photos GP ON GP.Record_ID = C.Contact_ID
WHERE (P.End_Date IS NULL OR P.End_Date > GETDATE())
ORDER BY C.Display_Name;

EXEC dbo.api_custom_PersonnelDirectory @DomainID = 1;
GO

/*==============================================================================
  Section 2 drops and recreates the procedure, which does NOT affect its row in
  dp_API_Procedures or its role link. Recycle the MinistryPlatform API
  application pool if the API then reports that the procedure does not exist.
==============================================================================*/
