declare
    @rOrgId    SMALLINT = 3001,
    @rFromDate VARCHAR(50) = '2026-04-01',
    @rToDate   VARCHAR(50) = '2026-05-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);

    ------------------------------------------------------------------
    -- Dynamic index setup (unique names per execution)
    ------------------------------------------------------------------
    DECLARE @Suffix    VARCHAR(32)  = REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''),
            @IdxPA     SYSNAME,
            @IdxLog    SYSNAME,
            @Sql       NVARCHAR(MAX),
            @PACreated BIT = 0,
            @LogCreated BIT = 0,
            @ErrMsg    NVARCHAR(2048),
            @ErrSev    INT,
            @ErrState  INT;

    SET @IdxPA  = 'IX_tmp_PA_'  + @Suffix;
    SET @IdxLog = 'IX_tmp_PAL_' + @Suffix;

    BEGIN TRY
        BEGIN TRY
            SET @Sql = N'CREATE NONCLUSTERED INDEX ' + QUOTENAME(@IdxPA) + N'
                         ON [sSupport].[tProviderAppointment] (OrgId, AssignDate)
                         INCLUDE (PatientSourceId, PatientSourceTypeId, HealthIndicatorId, AssignToDoctorId, SetupId)';
            EXEC sp_executesql @Sql;
            SET @PACreated = 1;

            SET @Sql = N'CREATE NONCLUSTERED INDEX ' + QUOTENAME(@IdxLog) + N'
                         ON [sSupport].[tProviderAppointmentLog] (ProviderAppointmentId, StateId)
                         INCLUDE (CreatedDateTime)';
            EXEC sp_executesql @Sql;
            SET @LogCreated = 1;
        END TRY
        BEGIN CATCH
            -- If index creation fails (permissions etc.), carry on without it
            PRINT 'Temporary index creation skipped: ' + ERROR_MESSAGE();
        END CATCH;

        --------------------------------------------------------------
        -- Main query
        --------------------------------------------------------------
        SELECT
            CASE PA.PatientSourceTypeId
                 WHEN 95   THEN ISNULL(EMP.Code, '-')
                 WHEN 1009 THEN ISNULL(CSR.Code, '-')
                 ELSE '-' END AS EmployeeCode,

            CASE PA.PatientSourceTypeId
                 WHEN 95   THEN ISNULL(EMP.FirstName + ' ' + EMP.LastName, '-')
                 WHEN 1009 THEN ISNULL(CSR.FirstName + ' ' + CSR.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(CONVERT(VARCHAR(10), LastOPD.OutTime, 103) + ' '
                   + REPLACE(REPLACE(LTRIM(RIGHT(CONVERT(VARCHAR(30), LastOPD.OutTime, 100), 8)), 'AM', ' AM'), 'PM', ' PM'), '-') AS OutTime,

            SM.Title AS HealthIndicatorStatus
        FROM [sSupport].[tProviderAppointment] PA
        INNER JOIN [sCommon].[tStatusMaster]          SM     ON SM.Id     = PA.HealthIndicatorId
        INNER JOIN [sHrm].[tEmployeeMaster]           Doctor ON Doctor.Id = PA.AssignToDoctorId
        INNER JOIN [sExamination].[tSetupMaster]      Setup  ON Setup.Id  = PA.SetupId
        INNER JOIN [sExamination].[tSetupGroupMaster] SGM    ON SGM.Id    = Setup.SetupGroupId
                                                            AND SGM.IsProviderHealthCheckup = 1
        LEFT JOIN  [sHrm].[tEmployeeMaster]           EMP    ON EMP.Id = PA.PatientSourceId
                                                            AND PA.PatientSourceTypeId = 95
        LEFT JOIN  [sHrm].[tCsrBenefiterMaster]       CSR    ON CSR.Id = PA.PatientSourceId
                                                            AND PA.PatientSourceTypeId = 1009
        OUTER APPLY
        (
            SELECT TOP 1 OPD.CreatedDateTime AS OutTime
            FROM [sSupport].[tProviderAppointmentLog] OPD
            WHERE OPD.ProviderAppointmentId = PA.Id
              AND OPD.StateId = 591
            ORDER BY OPD.Id DESC
        ) LastOPD
        WHERE PA.OrgId = @rOrgId
          AND PA.AssignDate >= @FromDate
          AND PA.AssignDate <  @ToDateExclusive
        ORDER BY EmployeeCode, PA.AssignDate
        OPTION (RECOMPILE);   -- forces the optimizer to see the new indexes

        --------------------------------------------------------------
        -- Cleanup on success
        --------------------------------------------------------------
        IF @LogCreated = 1
        BEGIN
            SET @Sql = N'DROP INDEX ' + QUOTENAME(@IdxLog) + N' ON [sSupport].[tProviderAppointmentLog]';
            EXEC sp_executesql @Sql;
            SET @LogCreated = 0;
        END;

        IF @PACreated = 1
        BEGIN
            SET @Sql = N'DROP INDEX ' + QUOTENAME(@IdxPA) + N' ON [sSupport].[tProviderAppointment]';
            EXEC sp_executesql @Sql;
            SET @PACreated = 0;
        END;
    END TRY
    BEGIN CATCH
        SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSev = ERROR_SEVERITY(), @ErrState = ERROR_STATE();

        -- Cleanup on failure (never leave orphan indexes behind)
        BEGIN TRY
            IF @LogCreated = 1
            BEGIN
                SET @Sql = N'DROP INDEX ' + QUOTENAME(@IdxLog) + N' ON [sSupport].[tProviderAppointmentLog]';
                EXEC sp_executesql @Sql;
            END;
            IF @PACreated = 1
            BEGIN
                SET @Sql = N'DROP INDEX ' + QUOTENAME(@IdxPA) + N' ON [sSupport].[tProviderAppointment]';
                EXEC sp_executesql @Sql;
            END;
        END TRY
        BEGIN CATCH
        END CATCH;

        RAISERROR(@ErrMsg, @ErrSev, @ErrState);   -- SQL 2008 has no THROW
    END CATCH;
END;