CREATE proc [dbo].[BusinessGetByParams]
	@Id int,
	@UserId nvarchar(128),
	@SearchTerm varchar(50),

	@NorthEastLong numeric(12,9),
	@NorthEastLat numeric(12,9),

	@SouthWestLong numeric(12,9),
	@SouthWestLat numeric(12,9),

	@PinnedByUser bit,
	@PageNumber int = 1,
	@PageSize int = 20,
	@Categories nvarchar(100),
	@WhiskeyId int
as
begin
	set @SearchTerm = lower(replace(@SearchTerm, ' ', ''))

	declare @offset int = (@PageNumber - 1) * @PageSize;
	declare @date date = getdate();

	set @Categories = isnull(@Categories, '');

	with Categories(CategoryId)
	as
	(
		select item CategoryId from dbo.split(@Categories,'|')
	)
	select		COUNT(*) OVER() TotalRows,
				@PageNumber PageNumber,
				CEILING((COUNT(*) OVER()) / CAST(@PageSize AS float)) TotalPages,

				b.[Id]
				,b.ImagePath
				,[SubscriptionId]
				,b.UserId
				,[BusinessName]
				,BusinessDescription
				,BusinessInformation
				,[FullAddress]
				,b.[Phone]
				,b.[CreatedDate]
				,b.[IsActive]
				,b.IsPublished
				,[GeoLat]
				,[GeoLong]
				,AditionalInfoJson
				,aspnetu.Email
				,case when ubp.Id is null then 0 else 1 end IsPinned

				,punchCard.PunchCardQty
				,offer.OfferQty
				,timeline.MessageQty
				,bc.CategoryId
				,c.CategoryName Category
				,u.Id ConsumerId
	FROM		[dbo].[Business] b
	join        whiskey.Business wb
		on wb.BusinessId = b.Id
	left join		[dbo].[AspNetUsers] aspnetu
		on	aspnetu.Id = @UserId
	left join		[dbo].[User] u
		on	u.IdentityUserId = aspnetu.Id
	left join	[dbo].[UserBusinessPin] ubp
		on	ubp.BusinessId = b.Id
		and	ubp.UserId = u.Id
	left join   [dbo].BusinessCategory bc
		on bc.BusinessId = b.Id
	left join   [dbo].Category c
		on c.Id = bc.CategoryId
	outer apply (
		select	count(*) PunchCardQty
		from	[dbo].[PunchCard]
		where	BusinessId = b.Id
		and		IsActive = 1
		and		IsPublished = 1
		and		(
					CONVERT(VARCHAR(10), ExpirationDate, 103) >= CONVERT(VARCHAR(10), GETDATE(), 103)
					or
					ExpirationDate is null
				)
	) punchCard

	outer apply (
		select	count(*) OfferQty
		from	[dbo].Offer
		where	BusinessId = b.Id
		and		IsActive = 1
		and		(
					@date between AvailableFrom and AvailableTo
				)
	) offer

	outer apply (
		select	count(*) MessageQty
		from	[dbo].BusinessMessage
		where	BusinessId = b.Id
	) timeline

	where		(b.Id = @Id or @Id is null)
	--and			(
	--				replace(
	--					lower(
	--						isnull(b.BusinessName, '')
	--					),
	--				' ', '')

	--				like '%' + replace(@SearchTerm, ' ','') + '%'

	--				or

	--				@SearchTerm is null
	--			)
	--and			(@Categories = '' or bc.CategoryId in (select CategoryId from Categories))
	--and			(
	--				(ubp.Id is not null and @PinnedByUser = 1)
	--				or
	--				(@PinnedByUser is null)
	--			)
	--and			(
	--				(
	--					[GeoLat] <= @NorthEastLat and [GeoLat] >= @SouthWestLat
	--					and
	--					[GeoLong] <= @NorthEastLong and [GeoLong] >= @SouthWestLong
	--				)
	--				or
	--				(
	--					@NorthEastLat = 0 or @SouthWestLat = 0
	--					or
	--					@NorthEastLong = 0 or @SouthWestLong = 0
	--				)
	--			)

	and b.IsActive = 1
	and b.IsPublished = 1
	and wb.MainId = @WhiskeyId
	--and wb.IsMainBusiness = 0
	order by	b.BusinessName

	OFFSET @offset rows fetch next @PageSize rows ONLY
end
GO

CREATE proc [dbo].[BusinessGetByUserId]
	@UserId nvarchar(128)
as
begin
	  SELECT b.Id
			,null SubscriptionId
			--,isnull(s.SubscriptionName, 'Unsubscribed') SubscriptionName
			, null SubscriptionName
			,bu.UserId
			,b.ImagePath
			,BusinessName
			,BusinessDescription
			,BusinessInformation
			,FullAddress
			,Phone
			,b.CreatedDate
			,b.IsActive
			,b.IsPublished
			,GeoLat
			,GeoLong
			,AditionalInfoJson
			,anu.Email
	from	dbo.Business b
	join	dbo.BusinessUser bu
		on	bu.BusinessId = b.Id
	join	dbo.[User] u
		on u.Id = bu.UserId
	join  dbo.aspNetUsers anu
		on  anu.id = u.identityUserId
	--left join dbo.Subscription s
	--	on  s.Id = b.SubscriptionId
	where	u.identityUserId = @UserId
end
GO

CREATE proc [dbo].[BusinessUpdate]
	@BusinessId int,
	@UserId nvarchar(128),
	@BusinessName nvarchar(150),
	@BusinessDescription nvarchar(1000),
	@BusinessInformation nvarchar(max),
	@FullAddress nvarchar(500),
	@Phone nvarchar(15),
	@GeoLat decimal(12,9),
    @GeoLong decimal(12,9),
	@IsPublished bit,
	@AditionalInfoJSON nvarchar(max)
as
begin

	--declare @businessId int

	--select	@businessId = b.Id
	--from dbo.Business b
	--join dbo.[User] u
	--	on u.Id = b.UserId
	--where	u.IdentityUserId = @UserId
	--declare @canBePublished bit = (select cast(case when (@BusinessName is not null
	--												  and @BusinessDescription is not null
	--												  and @GeoLat is not null
	--												  and @GeoLat is not null
	--												  and ImagePath is not null)
	--										    then 1 else 0 end as bit)
	--								from dbo.Business
	--								where Id = @BusinessId)

	UPDATE [dbo].[Business]
	   SET [BusinessName] = @BusinessName,
		   [BusinessDescription] = @BusinessDescription,
		   [BusinessInformation] = @BusinessInformation,
		   [FullAddress] = @FullAddress,
		   [Phone] = @Phone,
		   [GeoLat] = @GeoLat,
		   [GeoLong] = @GeoLong,
		   [IsPublished] = @IsPublished,
		   [AditionalInfoJson] = @AditionalInfoJSON
	 WHERE Id = @businessId

	 exec dbo.BusinessGetByUserId @UserId

end
GO