
--ALTER PROCEDURE [sSupport].[pCoboticsAHCList]
--(
DECLARE
    @rOrgId SMALLINT=1002,
    @rFromDate VARCHAR(50) = '2026-01-01',
    @rToDate VARCHAR(50) = '2026-10-01'
--)
--AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @FromDate DATETIME,
        @ToDate DATETIME,
        @ToDateNextDay DATETIME;

    /*====================================================
      1. Validate From Date
    ====================================================*/
    IF ISNULL(@rFromDate, '') = ''
    BEGIN
        SELECT 'From Date is required!' AS Result;
        RETURN;
    END;

    /*====================================================
      2. Validate To Date
    ====================================================*/
    IF ISNULL(@rToDate, '') = ''
    BEGIN
        SELECT 'To Date is required!' AS Result;
        RETURN;
    END;

    /*====================================================
      3. Convert dates
    ====================================================*/
    SET @FromDate = CONVERT(DATETIME, @rFromDate, 111);
    SET @ToDate = CONVERT(DATETIME, @rToDate, 111);

    /*====================================================
      4. Validate date range
    ====================================================*/
    IF @ToDate < @FromDate
    BEGIN
        SELECT 'To Date should not be less than From Date!' AS Result;
        RETURN;
    END;

    IF @ToDate > DATEADD(YEAR, 1, @FromDate)
    BEGIN
        SELECT 'Please select a date range within 1 year!' AS Result;
        RETURN;
    END;

    SET @ToDateNextDay = DATEADD(DAY, 1, @ToDate);

    DECLARE @SetupGroupId INT;

    SELECT TOP 1
        @SetupGroupId = Id
    FROM  [sExamination].[tSetupGroupMaster] 
    WHERE IsProviderHealthCheckup = 1;

    /*====================================================
      5. Filter appointments first
    ====================================================*/
    SELECT
        PA.Id,
        PA.OrgId,
        PA.SetupId,
        PA.PatientSourceId,
        PA.PatientSourceTypeId,
        PA.AssignToDoctorId,
        PA.HealthIndicatorId,
        PA.AssignDate
    INTO #FilteredPA
    FROM [sSupport].[tProviderAppointment] PA
    INNER JOIN [sExamination].[tSetupMaster] Setup
        ON Setup.Id = PA.SetupId    
    WHERE PA.OrgId = @rOrgId AND Setup.SetupGroupId = @SetupGroupId
      AND PA.AssignDate >= @FromDate
      AND PA.AssignDate < @ToDateNextDay;

    /* Index filtered appointments */
    CREATE UNIQUE CLUSTERED INDEX IX_FilteredPA_Id
        ON #FilteredPA (Id);

    /*====================================================
      6. Find latest StateId = 591 log using CTE

      Only logs belonging to filtered appointments
      are considered.
    ====================================================*/
    CREATE TABLE #LatestAppointmentLog
    (
        ProviderAppointmentId BIGINT NOT NULL,
        OutTime DATETIME NULL
    );

    ;WITH RankedLogs AS
    (
        SELECT
            OPD.ProviderAppointmentId,
            OPD.CreatedDateTime AS OutTime,
            ROW_NUMBER() OVER
            (
                PARTITION BY OPD.ProviderAppointmentId
                ORDER BY OPD.Id DESC
            ) AS RN
        FROM [sSupport].[tProviderAppointmentLog] OPD
        INNER JOIN #FilteredPA FPA
            ON FPA.Id = OPD.ProviderAppointmentId
        WHERE OPD.StateId = 591
    )
    INSERT INTO #LatestAppointmentLog
    (
        ProviderAppointmentId,
        OutTime
    )
    SELECT
        ProviderAppointmentId,
        OutTime
    FROM RankedLogs
    WHERE RN = 1
    OPTION (RECOMPILE);

    /* Index temporary log results */
    CREATE UNIQUE CLUSTERED INDEX IX_LatestAppointmentLog_Id
        ON #LatestAppointmentLog (ProviderAppointmentId);

    /*====================================================
      7. Final result
    ====================================================*/
    SELECT DISTINCT

        CASE
            WHEN PA.PatientSourceTypeId = 95
                THEN ISNULL(EMP.Code, '-')           
            ELSE '-'
        END AS EmployeeCode,

        CASE
            WHEN PA.PatientSourceTypeId = 95
                THEN ISNULL(
                    EMP.FirstName + ' ' + EMP.LastName, '-'
                )           
            ELSE '-'
        END AS EmployeeName,

        Doctor.Code AS DoctorCode,

        ISNULL(Doctor.FirstName, '') + ' ' +
        ISNULL(Doctor.LastName, '') AS DoctorName,

        ISNULL
        (
            CONVERT(VARCHAR(10), PA.AssignDate, 103) + ' ' +
            REPLACE
            (
                REPLACE
                (
                    LTRIM
                    (
                        RIGHT
                        (
                            CONVERT(VARCHAR(30), PA.AssignDate, 100),
                            8
                        )
                    ),
                    'AM', ' AM'
                ),
                'PM', ' PM'
            ),
            '-'
        ) AS InTime,

        ISNULL
        (
            CASE
                WHEN LastOPD.OutTime IS NOT NULL
                THEN
                    CONVERT(VARCHAR(10), LastOPD.OutTime, 103)
                    + ' ' +
                    REPLACE
                    (
                        REPLACE
                        (
                            LTRIM
                            (
                                RIGHT
                                (
                                    CONVERT(
                                        VARCHAR(30),
                                        LastOPD.OutTime,
                                        100
                                    ),
                                    8
                                )
                            ),
                            'AM', ' AM'
                        ),
                        'PM', ' PM'
                    )
            END,
            '-'
        ) AS OutTime,

        SM.Title AS HealthIndicatorStatus

    FROM #FilteredPA PA

    INNER JOIN [sHrm].[tEmployeeMaster] EMP
        ON EMP.Id = PA.PatientSourceId
       AND PA.PatientSourceTypeId = 95    

    INNER JOIN [sCommon].[tStatusMaster] SM
        ON SM.Id = PA.HealthIndicatorId

    INNER JOIN [sHrm].[tEmployeeMaster] Doctor
        ON Doctor.Id = PA.AssignToDoctorId

    LEFT JOIN #LatestAppointmentLog LastOPD
        ON LastOPD.ProviderAppointmentId = PA.Id

    ORDER BY
        EmployeeCode,
        InTime;

    /*====================================================
      8. Clean up temporary tables
    ====================================================*/
    DROP TABLE #LatestAppointmentLog;
    DROP TABLE #FilteredPA;

END;
