SET NOCOUNT ON

-- Configure users

IF (OBJECT_ID('dbo.users') IS NOT NULL)
BEGIN
	DROP TABLE dbo.[users]
END
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[users](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[user_name] [varchar](15) NOT NULL,
 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (FILLFACTOR = 100, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

DECLARE @UserCount int
DECLARE @TotalUserCount int

SET @TotalUserCount = 500000

SET @UserCount = 0
WHILE (@UserCount < @TotalUserCount)
BEGIN
	SET @UserCount = @UserCount + 1
	INSERT INTO dbo.[users](user_name)
	VALUES('DiggUser'+CONVERT(char(18), @UserCount))
END

-- Configure friends
IF (OBJECT_ID('dbo.friends') IS NOT NULL)
BEGIN
	DROP TABLE dbo.[friends]
END
GO

CREATE TABLE [dbo].[friends](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[user_id] [int] NOT NULL,
	[user_name] [varchar](15) NOT NULL,
	[friend_id] [int] NOT NULL,
	[friend_name] [varchar](15) NOT NULL,
	[mutual] [bit] NOT NULL,
	[date_created] [datetime] NOT NULL,
 CONSTRAINT [PK_friends] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-- Setup friend relationships. Power law.

DECLARE @UserCount int
DECLARE @TotalUserCount int

SELECT @TotalUserCount = MAX(id) FROM users

DECLARE @FriendCount int
DECLARE @FriendList table(friend_id int)
DECLARE @FriendDate datetime
SET @FriendDate = GETUTCDATE()

TRUNCATE TABLE friends

DECLARE @Base float

SET @Base = (@TotalUserCount + 1000) / 1000

-- Setup friend relationships. Power law.
SET @UserCount = 0
WHILE (@UserCount < @TotalUserCount)
BEGIN
	SET @UserCount = @UserCount + 1
	SET @FriendCount = POWER(100, RAND())	
	DELETE FROM @FriendList
	WHILE (@FriendCount > 0) 
	BEGIN
		INSERT INTO @FriendList(friend_id) 
		VALUES(CONVERT(int, POWER(@Base, RAND())*1000)-1000)
		
		SET @FriendCount = @FriendCount - 1
	END

	INSERT INTO dbo.friends(user_id, user_name, friend_id, friend_name, mutual, date_created)
	SELECT DISTINCT base.id, base.user_name, friend.id, friend.user_name, 0, @FriendDate
	FROM
		@FriendList f
		JOIN dbo.users base ON base.id = @UserCount
		JOIN dbo.users friend ON friend.id = f.friend_id
END

GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_User_To_Friend] ON [dbo].[friends] 
(
	[user_id] ASC,
	[friend_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_Friend] ON [dbo].[friends] 
(
	[friend_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

-- Diggs
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF (OBJECT_ID('dbo.diggs') IS NOT NULL)
BEGIN
	DROP TABLE dbo.[diggs]
END
GO

CREATE TABLE [dbo].[diggs](
	[id] [int] NOT NULL IDENTITY(1,1),
	[item_id] [int] NOT NULL,
	[user_id] [int] NOT NULL,
	[digdate] [datetime] NOT NULL,
 CONSTRAINT [PK_diggs] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET NOCOUNT ON
GO

-- Create ~five hundred million diggs.
DECLARE @ItemCount int
DECLARE @TotalItemCount int
DECLARE @TotalUserCount int
DECLARE @Diggs int
DECLARE @UserDiggs table(user_id int, digdate datetime)
DECLARE @BaseDate datetime
SET @BaseDate = '2007-01-01'

SELECT @TotalUserCount = MAX(id) FROM users
SET @ItemCount = 1
SET @TotalItemCount = 500000

WHILE (@ItemCount < @TotalItemCount)
BEGIN
	SET @Diggs = RAND() * 2000.0

	DELETE FROM @UserDiggs
	
	WHILE (@Diggs > 0)
	BEGIN
		INSERT INTO @UserDiggs(user_id, digdate)
		VALUES(RAND()*@TotalUserCount+1, DATEADD(minute, RAND()*2102400, @BaseDate))
		
		SET @Diggs = @Diggs - 1
	END
	
	INSERT INTO diggs(item_id, user_id, digdate)
	SELECT 
		@ItemCount, user_id, MAX(digdate) 
	FROM
		@UserDiggs
	GROUP BY 
		user_id
	
	SET @ItemCount = @ItemCount  + 1
END
GO

CREATE NONCLUSTERED INDEX [IX_diggs_item] ON [dbo].[diggs] 
(
	[item_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_diggs_user] ON [dbo].[diggs] 
(
	[user_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO