DECLARE 
@rOrgId    SMALLINT=3001,
    @rFromDate VARCHAR(50) = '2026-04-01',
    @rToDate   VARCHAR(50) = '2026-10-01'

BEGIN
    SET NOCOUNT ON;

    ------------------------------------------------------------------
    -- Validation
    ------------------------------------------------------------------
    IF ISNULL(@rFromDate, '') = ''
    BEGIN SELECT 'From Date is required!' AS Result; RETURN; END;

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

    IF ISDATE(@rFromDate) = 0 OR ISDATE(@rToDate) = 0
    BEGIN SELECT 'Invalid date format!' AS Result; RETURN; END;

    DECLARE @FromDate        DATE = CONVERT(DATE, @rFromDate, 111),
            @ToDate          DATE = CONVERT(DATE, @rToDate, 111),
            @ToDateExclusive DATE;

    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 @ToDateExclusive = DATEADD(DAY, 1, @ToDate);

    ------------------------------------------------------------------
    -- Step 1: appointments in range (heap first = faster load)
    ------------------------------------------------------------------
    CREATE TABLE #Appt
    (
        Id                  BIGINT      NOT NULL,   -- use BIGINT if Id is bigint
        PatientSourceId     BIGINT      NULL,
        PatientSourceTypeId SMALLINT      NULL,
        HealthIndicatorId   INT      NULL,
        AssignToDoctorId    BIGINT      NULL,
        AssignDate          DATETIME NULL
    );

    INSERT INTO #Appt (Id, PatientSourceId, PatientSourceTypeId,
                       HealthIndicatorId, AssignToDoctorId, AssignDate)
    SELECT PA.Id, PA.PatientSourceId, PA.PatientSourceTypeId,
           PA.HealthIndicatorId, PA.AssignToDoctorId, PA.AssignDate
    FROM [sSupport].[tProviderAppointment] PA
    INNER JOIN [sExamination].[tSetupMaster]      Setup ON Setup.Id = PA.SetupId
    INNER JOIN [sExamination].[tSetupGroupMaster] SGM   ON SGM.Id   = Setup.SetupGroupId
                                                       AND SGM.IsProviderHealthCheckup = 1
    WHERE PA.OrgId = @rOrgId
      AND PA.AssignDate BETWEEN @FromDate AND @ToDateExclusive;
     

    -- Temp indexes, built after load (private to this session, no locks on real tables)
    CREATE UNIQUE CLUSTERED INDEX IX_tmpAppt_Id
        ON #Appt (Id);

    CREATE NONCLUSTERED INDEX IX_tmpAppt_Source
        ON #Appt (PatientSourceTypeId, PatientSourceId)
        INCLUDE (AssignToDoctorId, HealthIndicatorId, AssignDate);

    ------------------------------------------------------------------
    -- Step 2: latest "out" log per appointment (one index seek each)
    ------------------------------------------------------------------
    CREATE TABLE #LastOut
    (
        ProviderAppointmentId INT      NOT NULL,
        OutTime               DATETIME NULL
    );

    INSERT INTO #LastOut (ProviderAppointmentId, OutTime)
    SELECT A.Id, X.CreatedDateTime
    FROM #Appt A
    CROSS APPLY
    (
        SELECT TOP 1 OPD.CreatedDateTime
        FROM [sSupport].[tProviderAppointmentLog] OPD
        WHERE OPD.ProviderAppointmentId = A.Id
          AND OPD.StateId = 591              -- keep as literal (matches filtered index)
        ORDER BY OPD.Id DESC
    ) X;

    CREATE UNIQUE CLUSTERED INDEX IX_tmpLastOut_Id
        ON #LastOut (ProviderAppointmentId);

    ------------------------------------------------------------------
    -- Step 3: final result
    ------------------------------------------------------------------
    SELECT
        CASE A.PatientSourceTypeId
             WHEN 95   THEN ISNULL(EMP.Code, '-')             
             ELSE '-' END AS EmployeeCode,

        CASE A.PatientSourceTypeId
             WHEN 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), A.AssignDate, 103) + ' '
               + REPLACE(REPLACE(LTRIM(RIGHT(CONVERT(VARCHAR(30), A.AssignDate, 100), 8)), 'AM', ' AM'), 'PM', ' PM'), '-') AS InTime,

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

        SM.Title AS HealthIndicatorStatus
    FROM #Appt A
    INNER JOIN [sCommon].[tStatusMaster]    SM     ON SM.Id     = A.HealthIndicatorId
    INNER JOIN [sHrm].[tEmployeeMaster]     Doctor ON Doctor.Id = A.AssignToDoctorId
    LEFT JOIN  [sHrm].[tEmployeeMaster]     EMP    ON EMP.Id = A.PatientSourceId AND A.PatientSourceTypeId = 95    
    LEFT JOIN  #LastOut LO ON LO.ProviderAppointmentId = A.Id
    ORDER BY EmployeeCode, A.AssignDate;

    DROP TABLE #LastOut;
    DROP TABLE #Appt;
END;