UFN_DAILYSALEITEM_EVENT_GETAVAILABILITY_2

CREATE function dbo.UFN_DAILYSALEITEM_EVENT_GETAVAILABILITY_2
(
    @DATETIMEOFFSET datetimeoffset, 
    @SALESMETHODID uniqueidentifier
)
returns @VALIDEVENTS table
(
    DAILYSALESITEMID uniqueidentifier,
    PROGRAMID uniqueidentifier,
    PRICETYPECODEID uniqueidentifier,
    EVENTID uniqueidentifier,
    CAPACITY integer,
    ISONSALE bit,
    FACEPRICE money,
    LOCATION nvarchar(100)
)
as
    begin
        declare @TODAY datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(getutcdate(), 1);
        set @TODAY = dbo.UFN_DATETIMEOFFSET_GETEARLIESTTIME(@TODAY);
        declare @TOMORROW datetimeoffset = dateadd(day, 1, @TODAY);

        declare @TODAYSEVENTS table(
            ITEMID uniqueidentifier,
            EVENTID uniqueidentifier,
            STARTDATETIMEWITHOFFSET datetimeoffset, 
            ONSALEDATETIMEWITHOFFSET datetimeoffset,
            ISONSALE bit, -- this ISONSALE will just grab everything that's ONSALEENDTIME hasn't passed

            AVAILABILITY integer
        );

        declare @TEMPEVENTS table(
            EVENTID uniqueidentifier,
            PRICETYPECODEID uniqueidentifier,
            DAILYSALEITEMPROGRAMID uniqueidentifier,
            ISONSALE bit, -- this ISONSALE will serve as an enabled/disabled check for schedule option type = 1

            ONSALEDATETIMEWITHOFFSET datetimeoffset,
            AVAILABILITY integer,
            PROGRAMID uniqueidentifier
        );

        insert into @TODAYSEVENTS
        select
            DAILYSALEITEMPROGRAM.ID,
            EVENT.ID,
            EVENT.STARTDATETIMEWITHOFFSET,
            EVENTSALESMETHOD.ONSALEDATETIMEWITHOFFSET,
            --Figure out ISONSALE value

            case
                when PROGRAMSALESMETHOD.ONSALEENDTYPECODE = 0 then  --event start time

                    case 
                        when EVENT.STARTDATETIMEWITHOFFSET > @DATETIMEOFFSET
                            then 1
                        else 0
                    end
                when PROGRAMSALESMETHOD.ONSALEENDTYPECODE = 1 then  --minutes before

                    case
                        when EVENT.STARTDATETIMEWITHOFFSET > dateadd(mi, PROGRAMSALESMETHOD.ONSALEENDINTERVAL, @DATETIMEOFFSET)
                            then 1
                        else 0
                    end
                when PROGRAMSALESMETHOD.ONSALEENDTYPECODE = 2 then  --hours before

                    case
                        when EVENT.STARTDATETIMEWITHOFFSET > dateadd(hh, PROGRAMSALESMETHOD.ONSALEENDINTERVAL, @DATETIMEOFFSET)
                            then 1
                        else 0
                    end
                when PROGRAMSALESMETHOD.ONSALEENDTYPECODE = 3 then  --minutes after

                    case 
                        when EVENT.STARTDATETIMEWITHOFFSET > dateadd(mi, -1 * PROGRAMSALESMETHOD.ONSALEENDINTERVAL, @DATETIMEOFFSET)
                            then 1
                        else 0
                    end
                else 0
            end,
            null
        from dbo.DAILYSALEITEMPROGRAM
        inner join dbo.DAILYSALEITEM
            on DAILYSALEITEM.ID = DAILYSALEITEMPROGRAM.ID
        inner join dbo.EVENT
            on EVENT.PROGRAMID = DAILYSALEITEMPROGRAM.PROGRAMID
        inner join dbo.EVENTSALESMETHOD
            on EVENTSALESMETHOD.EVENTID = EVENT.ID
        inner join dbo.PROGRAMSALESMETHOD
            on PROGRAMSALESMETHOD.PROGRAMID = DAILYSALEITEMPROGRAM.PROGRAMID
        where 
            EVENT.STARTDATETIMEWITHOFFSET >= @TODAY and
            EVENT.STARTDATETIMEWITHOFFSET < @TOMORROW and
            DAILYSALEITEM.ISACTIVE = 1 and
            DAILYSALEITEMPROGRAM.SCHEDULEOPTIONTYPECODE in (1,2) and
            EVENTSALESMETHOD.SALESMETHODID = @SALESMETHODID and
            PROGRAMSALESMETHOD.SALESMETHODID = @SALESMETHODID
        order by EVENT.STARTDATETIMEWITHOFFSET asc

        -- Updating this once the events are filtered

        -- helps make sure that the proper indexes are used by the view

        update @TODAYSEVENTS set
            AVAILABILITY = TICKETCOUNTS.AVAILABILITY
        from @TODAYSEVENTS as TODAYSEVENTS
        inner join dbo.V_PROGRAMEVENT_TICKETCOUNTS as TICKETCOUNTS on TICKETCOUNTS.ID = TODAYSEVENTS.EVENTID

        insert into @TEMPEVENTS
        -- one for each time

        select distinct
            EVENTID,
            DAILYSALEITEMPROGRAM.PRICETYPECODEID,
            DAILYSALEITEMPROGRAM.ID,
            TODAYSEVENTS.ISONSALE,
            TODAYSEVENTS.ONSALEDATETIMEWITHOFFSET,
            TODAYSEVENTS.AVAILABILITY,
            DAILYSALEITEMPROGRAM.PROGRAMID
        from @TODAYSEVENTS TODAYSEVENTS
        inner join dbo.DAILYSALEITEMPROGRAM
            on DAILYSALEITEMPROGRAM.ID = TODAYSEVENTS.ITEMID
        where DAILYSALEITEMPROGRAM.SCHEDULEOPTIONTYPECODE = 1

        insert into @TEMPEVENTS
        -- next available 

        select 
            [EVENT].EVENTID,
            DAILYSALEITEMPROGRAM.PRICETYPECODEID,
            DAILYSALEITEMPROGRAM.ID,
            [EVENT].ISONSALE,
            [EVENT].ONSALEDATETIMEWITHOFFSET,
            [EVENT].AVAILABILITY,
            DAILYSALEITEMPROGRAM.PROGRAMID
        from dbo.DAILYSALEITEMPROGRAM
        outer apply (
            --Next available event today

            select top(1) 
                EVENTID,
                AVAILABILITY,
                ISONSALE,
                ONSALEDATETIMEWITHOFFSET
            from @TODAYSEVENTS TODAYSEVENTS
            where 
                ITEMID = DAILYSALEITEMPROGRAM.ID and
                ISONSALE = 1 and
                AVAILABILITY > 0
            order by STARTDATETIMEWITHOFFSET asc
        ) as NEXTAVAILABLETODAY
        outer apply (
            --Next available event after today (if needed)

            select top(1) 
                [EVENT].ID as EVENTID,
                TICKETCOUNTS.AVAILABILITY,
                --ISONSALE Will be updated in a set-based update below

                --We don't need to consider this now?

                null as ISONSALE, 
                EVENTSALESMETHOD.ONSALEDATETIMEWITHOFFSET
            from dbo.[EVENT]
            inner join dbo.EVENTSALESMETHOD
                on [EVENT].ID = EVENTSALESMETHOD.EVENTID
            inner join dbo.V_PROGRAMEVENT_TICKETCOUNTS TICKETCOUNTS
                on [EVENT].ID = TICKETCOUNTS.ID
            where 
                NEXTAVAILABLETODAY.EVENTID is null and -- If there is no available event today

                DAILYSALEITEMPROGRAM.SHOWALLDATES = 1 and -- and we can look beyond today

                [EVENT].PROGRAMID = DAILYSALEITEMPROGRAM.PROGRAMID and
                EVENTSALESMETHOD.SALESMETHODID = @SALESMETHODID and
                [EVENT].STARTDATETIMEWITHOFFSET >= @TOMORROW and
                TICKETCOUNTS.AVAILABILITY > 0
            order by [EVENT].STARTDATETIMEWITHOFFSET asc
        ) as NEXTAVAILABLEEVENT
        outer apply (
            -- Next event as placeholder (if needed)

            select top(1) 
                EVENTID,
                AVAILABILITY,
                ISONSALE,
                ONSALEDATETIMEWITHOFFSET
            from @TODAYSEVENTS
            where 
                NEXTAVAILABLEEVENT.EVENTID is null and -- If there is no viable event in the future

                ITEMID = DAILYSALEITEMPROGRAM.ID
            order by STARTDATETIMEWITHOFFSET desc
        ) as NEXTEVENT
        cross apply (
            select
                coalesce(NEXTAVAILABLETODAY.EVENTID, NEXTAVAILABLEEVENT.EVENTID, NEXTEVENT.EVENTID) as EVENTID,
                coalesce(NEXTAVAILABLETODAY.AVAILABILITY, NEXTAVAILABLEEVENT.AVAILABILITY, NEXTEVENT.AVAILABILITY) as AVAILABILITY,
                coalesce(NEXTAVAILABLETODAY.ISONSALE, NEXTAVAILABLEEVENT.ISONSALE, NEXTEVENT.ISONSALE) as ISONSALE,
                coalesce(NEXTAVAILABLETODAY.ONSALEDATETIMEWITHOFFSET, NEXTAVAILABLEEVENT.ONSALEDATETIMEWITHOFFSET, NEXTEVENT.ONSALEDATETIMEWITHOFFSET) as ONSALEDATETIMEWITHOFFSET
        ) as [EVENT]
        where 
            DAILYSALEITEMPROGRAM.SCHEDULEOPTIONTYPECODE = 2 and
            not exists(select 1 from @TEMPEVENTS where DAILYSALEITEMPROGRAMID = DAILYSALEITEMPROGRAM.ID)

        /* 
            For cases where the event was not from today, but is the next available, ISONSALE will be null 
            as it isn't in TODAYSEVENTS.  Since TODAYSEVENTS is an optimization to immediately narrow down
            our working set, this makes sense. Since it's on the rare side of things (or so research shows),
            we'll calculate the ISONSALE here for any events that aren't occurring today but got swept in due
            to a next available setting.  In my opinion this is cleaner than throwing another coalesce in the
            previous statement.
        */
        update @TEMPEVENTS 
        set
            ISONSALE = case
                when PROGRAMSALESMETHOD.ONSALEENDTYPECODE = 0 then  --event start time

                    case 
                        when EVENT.STARTDATETIMEWITHOFFSET > @DATETIMEOFFSET
                            then 1
                        else 0
                    end
                when PROGRAMSALESMETHOD.ONSALEENDTYPECODE = 1 then  --minutes before

                    case
                        when EVENT.STARTDATETIMEWITHOFFSET > dateadd(mi, PROGRAMSALESMETHOD.ONSALEENDINTERVAL, @DATETIMEOFFSET)
                            then 1
                        else 0
                    end
                when PROGRAMSALESMETHOD.ONSALEENDTYPECODE = 2 then  --hours before

                    case
                        when EVENT.STARTDATETIMEWITHOFFSET > dateadd(hh, PROGRAMSALESMETHOD.ONSALEENDINTERVAL, @DATETIMEOFFSET)
                            then 1
                        else 0
                    end
                when PROGRAMSALESMETHOD.ONSALEENDTYPECODE = 3 then  --minutes after

                    case 
                        when EVENT.STARTDATETIMEWITHOFFSET > dateadd(mi, -1 * PROGRAMSALESMETHOD.ONSALEENDINTERVAL, @DATETIMEOFFSET)
                            then 1
                        else 0
                    end
                else 0
            end 
        from dbo.EVENT
        inner join dbo.PROGRAMSALESMETHOD
            on EVENT.PROGRAMID = PROGRAMSALESMETHOD.PROGRAMID
        where EVENT.ID = EVENTID
        and PROGRAMSALESMETHOD.SALESMETHODID = @SALESMETHODID
        and ISONSALE is null

        insert into @VALIDEVENTS
        select
            TEMPEVENTS.DAILYSALEITEMPROGRAMID,
            TEMPEVENTS.PROGRAMID,
            TEMPEVENTS.PRICETYPECODEID,
            TEMPEVENTS.EVENTID,
            TEMPEVENTS.AVAILABILITY,
            case
                when CUSTOMPRICING.HASCUSTOMPRICING = 1 and PROGRAMEVENTPRICE.PRICETYPECODEID is null then 0  -- Scenario where program has a price type that custom even pricing doesn't, not on sale

                when TEMPEVENTS.ONSALEDATETIMEWITHOFFSET < @DATETIMEOFFSET and TEMPEVENTS.ISONSALE = 1 then 1
                else 0
            end as ISONSALE,
            isnull(PROGRAMEVENTPRICE.FACEPRICE, PROGRAMPRICE.FACEPRICE) as FACEPRICE,
            (
                select top(1) NAME 
                from dbo.EVENTLOCATION 
                inner join dbo.PROGRAMEVENTLOCATION
                    on PROGRAMEVENTLOCATION.EVENTLOCATIONID = EVENTLOCATION.ID
                where PROGRAMEVENTLOCATION.EVENTID = TEMPEVENTS.EVENTID
            ) as LOCATION
        from
            @TEMPEVENTS TEMPEVENTS
        left join 
            dbo.PROGRAMEVENTPRICE on
                PROGRAMEVENTPRICE.EVENTID = TEMPEVENTS.EVENTID and 
                PROGRAMEVENTPRICE.PRICETYPECODEID = TEMPEVENTS.PRICETYPECODEID
        left join 
            dbo.PROGRAMPRICE on
                PROGRAMPRICE.PROGRAMID = TEMPEVENTS.PROGRAMID and 
                PROGRAMPRICE.PRICETYPECODEID = TEMPEVENTS.PRICETYPECODEID
        left outer join
            dbo.SALESMETHODEXCLUDEDPRICETYPE on
                SALESMETHODEXCLUDEDPRICETYPE.PRICETYPECODEID = TEMPEVENTS.PRICETYPECODEID
                and SALESMETHODEXCLUDEDPRICETYPE.SALESMETHODID = @SALESMETHODID
        outer apply (
            select
                case
                    when exists (
                        select 1
                        from dbo.PROGRAMEVENTPRICE
                        where PROGRAMEVENTPRICE.EVENTID = TEMPEVENTS.EVENTID
                    ) then 1
                    else 0
                end as HASCUSTOMPRICING
        ) as CUSTOMPRICING
        where 
            SALESMETHODEXCLUDEDPRICETYPE.PRICETYPECODEID is null

        return;

    end;