IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_getPropertiesShortContent]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_getPropertiesShortContent]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[sp_getPropertiesShortContent] (
    @properties varchar(max),
    @destinationFilterPassedProperties varchar(max) = null,
    @language_code varchar(10) = 'en-US',
    @primary_sorting int = null,
    @reverse_primary_sorting bit = 0,
    @secondary_sorting int = null,
    @reverse_secondary_sorting bit = 0,
    @name_filter nvarchar(max) = null,
    @rating_filter varchar(100) = null,
    @chain_filter varchar(100) = null,
    @amenity_filter varchar(100) = null,
    @filter_disabling_allowed bit = 0,
    @page_index int = 1,
    @page_size int = 25,
    @use_old_paging bit = 0,
    @first_product_number int = null,
    @last_product_number int = null
    )
as
begin
    -----------------------------------------------------------------------------
    -- Step 0. Initialize parameters
    -----------------------------------------------------------------------------
    declare @language_id int
    select @language_id = language_id from languages where code = @language_code

    declare @step0Ids table (id int, apiFilterPassed bit, sequenceNumber int identity)

    insert into @step0Ids
        select * from fn_SplitStringWithCodeNames(@properties, ';', ',')

    declare @destinationFilterPassedPropertiesTbl table (id int)

    if @destinationFilterPassedProperties is not null
        insert into @destinationFilterPassedPropertiesTbl
            select * from
                fn_SplitStringToIntegers(@destinationFilterPassedProperties, ';')

    declare @ratings table (rating float)
    if (@rating_filter is not null)
        insert into @ratings
            select * from fn_SplitString(@rating_filter, ';')

    declare @amenities table (id int)
    if (@amenity_filter is not null)
        insert into @amenities
            select * from fn_SplitStringToIntegers(@amenity_filter, ';')

    declare @SORTING_BY_NAME int
    set @SORTING_BY_NAME = 1

    declare @SORTING_BY_RATING int
    set @SORTING_BY_RATING = 2

    -----------------------------------------------------------------------------
    -- Step 1. Apply filtering to the list
    -----------------------------------------------------------------------------
    declare @step1Ids table (id int, sequenceNumber int identity )

    insert into @step1Ids
        select p.id from @step0Ids p
            inner join properties a (nolock) on p.id = a.property_id
            left outer join propertyInformation b (nolock) on
                b.property_id=a.property_id and b.language_id=@language_id
        where
            -- filtering done by API
            p.apiFilterPassed = 1
        and
            -- name filter
            ((@name_filter is null)
             or (ltrim(isnull(b.[name], a.[name])) like ('%' + @name_filter + '%'))
            )
        and
            -- rating filter
            ((@rating_filter is null)
             or (a.star_rating in (select rating from @ratings)))
        and
            -- amenity filter
            ((@amenity_filter is null)
            or ((select count(*)
                from dbo.propertyAmenityReference par
                inner join @amenities a on a.id = par.property_amenity_id
                where par.property_id = p.id) = (select count(*) from @amenities))
            )
        order by sequenceNumber

    -----------------------------------------------------------------------------
    -- Step 2. Disable filter if no property satisfies filtering criteria and
    --         filter disabling is allowed
    --         and apply destination filter
    -----------------------------------------------------------------------------
    declare @filterDisabled bit
    set @filterDisabled = 0

    declare @step2Ids table (id int, sequenceNumber int identity )
    declare @filteredPropertiesIDs table (id int, sequenceNumber int identity )

    if ((select count(*) from @step1Ids) = 0 and @filter_disabling_allowed = 1)
    begin
        insert into @step2Ids
            select id from @step0Ids
        set @filterDisabled = 1
    end
    else
    begin
        insert into @step2Ids
            select id from @step1Ids
    end

    -- Store filtered properties.
    insert into @filteredPropertiesIDs
        select id from @step2Ids

    -- Apply destination filter
    if @destinationFilterPassedProperties is not null
    begin
        delete from @step2Ids

        insert into @step2Ids
            select fp.id
            from @filteredPropertiesIDs fp
            inner join @destinationFilterPassedPropertiesTbl dfp
                on fp.id = dfp.id
    end

    declare @destinationsFilterDisabled bit
    set @destinationsFilterDisabled = 0

    -- Clear destinations filter if no hotel satisfies filtering criteria
    if ((select count(*) from @step2Ids) = 0 and @filter_disabling_allowed = 1)
    begin
        delete from @step2Ids

        insert into @step2Ids
            select fp.id
            from @filteredPropertiesIDs fp

        set @destinationsFilterDisabled = 1
    end

    --select * from @step2Ids

    -----------------------------------------------------------------------------
    -- Step 3. Apply sorting to the list
    -----------------------------------------------------------------------------
    declare @sortings table (sortingId int, reverseSorting bit)
    declare @sorting int
    declare @reverse_sorting bit

    declare @step3Ids table (id int, sequenceNumber int identity )
    declare @step3IdsTemp table (id int, sequenceNumber int identity )

    insert into @step3Ids select id from @step2Ids

    insert into @sortings values (@secondary_sorting, @reverse_secondary_sorting)
    insert into @sortings values (@primary_sorting, @reverse_primary_sorting)

    declare sortingCursor cursor local fast_forward for
        select * from @sortings
    open sortingCursor
    fetch from sortingCursor into @sorting, @reverse_sorting

    while @@FETCH_STATUS = 0
    begin
        if (@sorting is not null and @sorting in (1,2))
        begin
            delete from @step3IdsTemp
            insert into @step3IdsTemp select id from @step3Ids

            delete from @step3Ids

            if (@sorting = @SORTING_BY_NAME)
            begin
                -- Sorting queries are dublicated because I have not found
                -- any way to reverse soring order by using one "select" operator
                -- in case when sorting is done by nvarchar field.
                -- Please contact me if you know one.
                -- I am also can't use separate algorithm step to reverse sorting
                -- order because it will also reverse previous sortings. //Yakov Zh.
                if (@reverse_sorting = 0)
                    insert into @step3Ids
                        select p.id from @step3IdsTemp p
                            inner join properties a (nolock) on p.id = a.property_id
                            left outer join propertyInformation b (nolock) on
                                b.property_id=a.property_id and b.language_id=@language_id
                        order by ltrim(isnull(b.[name], a.[name]))
                else
                    insert into @step3Ids
                        select p.id from @step3IdsTemp p
                            inner join properties a (nolock) on p.id = a.property_id
                            left outer join propertyInformation b (nolock) on
                                b.property_id=a.property_id and b.language_id=@language_id
                        order by ltrim(isnull(b.[name], a.[name])) desc, p.sequenceNumber
            end else if (@sorting = @SORTING_BY_RATING) -- Product rating
            begin
                insert into @step3Ids
                    select p.id from @step3IdsTemp p
                        inner join properties a (nolock) on p.id = a.property_id
                    order by
                        case @reverse_sorting
                            when 0 then coalesce(a.star_rating, 0)
                            else coalesce(-a.star_rating, -0)
                        end,
                        p.sequenceNumber
            end

        end

        fetch from sortingCursor into @sorting, @reverse_sorting
    end

    close sortingCursor
    deallocate sortingCursor
    -----------------------------------------------------------------------------
    -- Step 4. Apply paging to the list
    -----------------------------------------------------------------------------
    declare @step4Ids table (id int, sequenceNumber int identity (0,1))

    declare @pagingEnabled bit
    set @pagingEnabled = 1

    if (@use_old_paging = 1)
    begin
        declare @step4IdsTemp table (id int, sequenceNumber int identity (0,1))

        insert into @step4IdsTemp
            select id from @step3Ids
            order by sequenceNumber

        insert into @step4Ids
            select id from @step4IdsTemp
            where @first_product_number <= sequenceNumber
                  and sequenceNumber <= @last_product_number
            order by sequenceNumber
    end

    if (select count(*) from @step3Ids) <= @page_size
       and @use_old_paging = 0
    begin
        set @pagingEnabled = 0

        insert into @step4Ids
            select id from @step3Ids
            order by sequenceNumber
    end

    declare @firstProductNumber int

    declare @pages table (
            pageNumber int identity (0,1),
            begining nvarchar,
            ending nvarchar,
            first_product_number int,
            last_product_number int,
            is_simple bit,
            is_disabled bit)

    if not (@primary_sorting in (@SORTING_BY_NAME, @SORTING_BY_RATING))
       and @pagingEnabled = 1 and @use_old_paging = 0
    begin
        declare @step4Tmp table (id int, sequenceNumber int identity (0,1))
        insert into @step4Tmp select id from @step3Ids
        declare @lastProductNumber int
        declare @numberOfProducts int
        select @numberOfProducts = count(*) from @step3Ids

        set @firstProductNumber = 0

        while @firstProductNumber < @numberOfProducts
        begin
            if @lastProductNumber >= @numberOfProducts
                set @lastProductNumber = @numberOfProducts - 1

            insert into @pages values
                (null,
                 null,
                 @firstProductNumber,
                 @page_size - 1,
                 1,
                 0)
            set @firstProductNumber = @firstProductNumber + @page_size
        end


        set @firstProductNumber = @page_index * @page_size
        set @lastProductNumber = (@page_index + 1) * @page_size - 1

        insert into @step4Ids
            select id from @step4Tmp
                where sequenceNumber >= @firstProductNumber
                  and sequenceNumber <= @lastProductNumber
    end

    if @primary_sorting = @SORTING_BY_NAME
       and @pagingEnabled = 1 and @use_old_paging = 0
    begin
        -- create pages

        -- create and fill table with first letters of hotel names
        declare @hotelNames table (id int, firstLetter nchar, sequenceNumber int identity)

        insert into @hotelNames
            select p.id,
                   upper(substring(ltrim(isnull(b.[name], a.[name])),1,1))
            from @step3Ids p
                inner join properties a (nolock) on p.id = a.property_id
                left outer join propertyInformation b (nolock) on
                    b.property_id=a.property_id and b.language_id=@language_id
            order by p.sequenceNumber

        -- Create name groups. One group for each letter and one special group for numbers
        declare @namesGroups table (firstLetter nchar, entries int)

        declare @letter nchar(1)
        declare @entriesCount int
        declare @numericEntriesCount int

        -- We need a group for numbers if @numericEntriesCount will be greater than zero
        select @numericEntriesCount = count(*)
        from @hotelNames
        where ascii(firstLetter) >= ascii('0') and ascii(firstLetter) <= ascii('9')

        -- Fill groups with letters from a to z or from z to a if sorting order is reversed.
        if (@reverse_primary_sorting = 0)
            set @letter = 'A'
        else
            set @letter = 'Z'

        while(ascii(@letter) <= ascii('Z')
              and ascii(@letter) >= ascii('A'))
        begin
            select @entriesCount = count(firstLetter)
            from @hotelNames
            where firstLetter = @letter

            insert into @namesGroups
            values (@letter, @entriesCount)

            if (@reverse_primary_sorting = 0)
                set @letter = char(ascii(@letter) + 1)
            else
                set @letter = char(ascii(@letter) - 1)
        end

        -- Create pages
        declare @firstLetter nchar
        declare @currentLetter nchar
        declare @currentGroupCount int
        declare @currentPageCount int

        declare groupCursor cursor for select * from @namesGroups
        open groupCursor

        fetch from groupCursor into @currentLetter, @currentGroupCount
        set @currentPageCount = 0

        -- add page for numbers if sorting is not reversed
        if (@reverse_primary_sorting = 0 and @numericEntriesCount > 0)
                insert into @pages values ('0', '9', null, null, 0, 0)

        declare @start_new_page bit
        set @start_new_page = 1

        while @@FETCH_STATUS = 0
        begin
            if (@start_new_page = 1) -- We have added a page in previous iteration.
            begin
                -- Start new page
                set @firstLetter = @currentLetter -- remember first letter of new page
                set @start_new_page = 0
            end

            -- Check if we need to finish current page.
            -- We need to finish page when number of hotels in current page is
            -- greater than @page_size. Letters with zero hotels
            -- starting on it are assigned to current page to fill gaps in alphabet.
            if @currentGroupCount + @currentPageCount >= @page_size
                and @currentGroupCount is not null
            begin
                insert into @pages values
                    (@firstLetter, @currentLetter, null, null, 0, 0)
                set @currentPageCount = 0
                set @start_new_page = 1 -- We need to know that we started a new page
                                        -- on next iteration to remember next page's
                                        -- starting letter.
            end
            else
            begin
                -- no need to finish page. Update hotels counter only.
                set @currentPageCount = @currentPageCount + @currentGroupCount
            end

            fetch from groupCursor into @currentLetter, @currentGroupCount
        end

        -- if a page is not finished on last iteration -
        -- finish it and add to the list
        if (@start_new_page = 0)
            insert into @pages values
                (@firstLetter, @currentLetter, null, null, 0, 0)

        -- add page for numbers if sorting is reversed
        if (@reverse_primary_sorting = 1 and @numericEntriesCount > 0)
                insert into @pages values ('0', '9', null, null, 0, 0)

        -- Pages are added. Now select hotels for selected page.

        -- if order is reversed - replace Begining and Ending for convinience
        declare @currentPageStart nchar
        declare @currentPageEnd nchar

        if @reverse_primary_sorting = 0
            select
                @currentPageStart = Begining,
                @currentPageEnd = Ending
            from @pages
            where pageNumber = @page_index
        else
            select
                @currentPageStart = Ending,
                @currentPageEnd = Begining
            from @pages
            where pageNumber = @page_index

        -- Select hotels.
        insert into @step4Ids
            select id from @hotelNames
                where ascii(firstLetter) >= ascii(@currentPageStart)
                  and ascii(firstLetter) <= ascii(@currentPageEnd)
            order by sequenceNumber

        close groupCursor
        deallocate groupCursor
    end

    if @primary_sorting = @SORTING_BY_RATING
       and @pagingEnabled = 1 and @use_old_paging = 0
    begin
        -- Enumerate and add all possible rating pages
        declare @ratingPages table
            (fromValue int, toValue int, issimple bit, sequenceNumber int identity)
        insert into @ratingPages values (null, null, 1) -- (uncategorized)
        insert into @ratingPages values (0, 1, 0) -- 1 star
        insert into @ratingPages values (1, 2, 0) -- ...
        insert into @ratingPages values (2, 3, 0) -- ...
        insert into @ratingPages values (3, 4, 0) -- ...
        insert into @ratingPages values (4, 5, 0) -- 5 stars

        insert into @pages
            select
                r.fromValue,
                r.toValue,
                null,
                null,
                r.isSimple,
                (select (case count(*) when 0 then 1 else 0 end)
                from @step3Ids p
                    inner join properties a (nolock) on p.id = a.property_id
                where floor(a.star_rating) = r.toValue
                    or (r.toValue is null
                        and (a.star_rating is null or a.star_rating = 0)))
            from @ratingPages r
            where (r.isSimple = 0) -- Do not display non-rated hotels page if no non-rated hotels exists.
                  or exists(select * from @step3Ids p
                            inner join properties a (nolock) on p.id = a.property_id
                            where (a.star_rating is null or a.star_rating = 0))
            order by case @reverse_primary_sorting
                     when  0 then sequenceNumber
                             else -sequenceNumber
                     end

        while (select count(*) from @step4Ids) = 0
        begin
            -- Select hotels.
            insert into @step4Ids
                select id from @step3Ids p
                    inner join properties a (nolock) on p.id = a.property_id
                    inner join @pages pg on pg.pageNumber = @page_index
                where floor(a.star_rating) = pg.ending
                    or (pg.ending is null
                        and (a.star_rating is null or a.star_rating = 0))
                order by p.sequenceNumber

            set @page_index = @page_index + 1
        end
        set @page_index = @page_index - 1
    end

    -----------------------------------------------------------------------------
    -- Step 5. Select content
    -----------------------------------------------------------------------------

    -- Hotel content
    select
        p.sequenceNumber,
        a.[property_id] as PropertyID,
        a.[postal_code] as PostalCode,
        a.[latitude] as Latitude,
        a.[longitude] as Longitude,
        a.[longitude] as Longitude,
        a.[star_rating] as StarRating,
        a.[city_code] as CityCode,
        isnull(b.[city_name], a.[city_name]) as CityName,
        a.[province_code] as ProvinceCode,
        isnull(b.[province_name], a.[province_name]) as ProvinceName,
        a.[country_code] as CountryCode,
        isnull(b.[country_name], a.[country_name]) as CountryName,
        ltrim(isnull(b.[name], a.[name])) as [Name],
        a.[small_exterior_url] as SmallExteriorUrl,
        a.[large_exterior_url] as LargeExteriorUrl,
        a.[email] as Email,
        isnull(b.[address_line_1], a.[address_line_1]) as AddressLine1,
        isnull(b.[address_line_2], a.[address_line_2]) as AddressLine2,
        isnull(b.[address_line_3], a.[address_line_3]) as AddressLine3,
        isnull(b.[address_line_4], a.[address_line_4]) as AddressLine4,
        isnull(b.[short_description], a.[short_description]) as ShortDescription,
        a.[smoking_type_id] as SmokingTypeID
    from @step4Ids p
        inner join properties a (nolock) on p.id = a.property_id
        left outer join propertyInformation b (nolock) on b.property_id=a.property_id and b.language_id=@language_id
        left join propertyInformation c (nolock) on c.property_id=a.property_id and c.language_id=1
    order by p.sequenceNumber

    -- Select pages
    select
        pageNumber as PageIndex,
        null as DisplayText,
        begining as Beginning,
        ending as Ending,
        first_product_number as FirstProductNumber,
        last_product_number as LastProductNumber,
        0 as IsSeparator,
        is_simple as IsSimple,
        is_disabled as IsDisabled
    from @pages

    -- Select misc scalar values

    declare @sequenceNumbers table (id int, sequenceNumber int identity (0,1))
    insert into @sequenceNumbers select id from @step3Ids

    select @filterDisabled as FilterDisabled,
           @destinationsFilterDisabled as DestinationsFilterDisabled,
           (select count(*) from @step3Ids) as NumberOfProducts,
           (select count(*) from @step0Ids) as TotalNumberOfProducts,
           (select top 1 sequenceNumber from @sequenceNumbers
            where id = (select top 1 id from @step4Ids)) as FirstProductNumber,
            @page_index as PageIndex

    -- List of property ids for properies which passed the filter

    select id as PropertyID
    from @filteredPropertiesIDs
end