use SEPSLoader
go

-- Statement for schema creation
DECLARE @sql NVARCHAR(MAX)
SET @sql ='CREATE SCHEMA StackExchange AUTHORIZATION [dbo]'

IF NOT EXISTS(SELECT name FROM sys.schemas WHERE name = 'StackExchange')
begin
	EXEC sp_executesql @sql;	
end

-- NOTE: StackExchange is replaced by the name of the site

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'StackExchange.[Badges]') AND type in (N'U'))
DROP TABLE StackExchange.[Badges]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'StackExchange.[Comments]') AND type in (N'U'))
DROP TABLE StackExchange.[Comments]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'StackExchange.[Posts]') AND type in (N'U'))
DROP TABLE StackExchange.[Posts]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'StackExchange.[PostTags]') AND type in (N'U'))
DROP TABLE StackExchange.[PostTags]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'StackExchange.[PostTypes]') AND type in (N'U'))
DROP TABLE StackExchange.[PostTypes]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'StackExchange.[Users]') AND type in (N'U'))
DROP TABLE StackExchange.[Users]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'StackExchange.[Votes]') AND type in (N'U'))
DROP TABLE StackExchange.[Votes]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'StackExchange.[VoteTypes]') AND type in (N'U'))
DROP TABLE StackExchange.[VoteTypes]
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'StackExchange.[PostLinks]') AND type IN (N'U'))
DROP TABLE StackExchange.[PostLinks]
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'StackExchange.[LinkTypes]') AND type IN (N'U'))
DROP TABLE StackExchange.[LinkTypes]

SET ansi_nulls  ON
SET quoted_identifier  ON
SET ansi_padding  ON

CREATE TABLE StackExchange.[LinkTypes] (
  Id INT NOT NULL,
  [Type] VARCHAR(40) NOT NULL,
  CONSTRAINT PK_LinkTypes PRIMARY KEY CLUSTERED (Id ASC) 
);

CREATE TABLE StackExchange.[VoteTypes] (
  [Id]   [INT]    NOT NULL,
  [Name] [VARCHAR](40)    NOT NULL
  , CONSTRAINT [PK__VoteType] PRIMARY KEY CLUSTERED ( [Id] ASC ) ON [PRIMARY]
  )ON [PRIMARY]

SET ansi_nulls  ON
SET quoted_identifier  ON

CREATE TABLE StackExchange.[PostTypes] (
  [Id]   [INT]    NOT NULL,
  [Type] [NVARCHAR](10)    NOT NULL
  , CONSTRAINT [PK_PostTypes] PRIMARY KEY CLUSTERED ( [Id] ASC ) ON [PRIMARY]
  ) ON [PRIMARY]

IF 0 = 1--SPLIT
  BEGIN
	SET ansi_nulls  ON
	SET quoted_identifier  ON

	CREATE TABLE StackExchange.[PostTags] (
	  [PostId] [INT]    NOT NULL,
	  [Tag]    [NVARCHAR](50)    NOT NULL
	  , CONSTRAINT [PK_PostTags_1] PRIMARY KEY CLUSTERED ( [PostId] ASC,[Tag] ASC ) ON [PRIMARY]
	  ) ON [PRIMARY]
  
  END
INSERT StackExchange.[VoteTypes] ([Id], [Name]) VALUES(1, N'AcceptedByOriginator')
INSERT StackExchange.[VoteTypes] ([Id], [Name]) VALUES(2, N'UpMod')
INSERT StackExchange.[VoteTypes] ([Id], [Name]) VALUES(3, N'DownMod')
INSERT StackExchange.[VoteTypes] ([Id], [Name]) VALUES(4, N'Offensive')
INSERT StackExchange.[VoteTypes] ([Id], [Name]) VALUES(5, N'Favorite')
INSERT StackExchange.[VoteTypes] ([Id], [Name]) VALUES(6, N'Close')
INSERT StackExchange.[VoteTypes] ([Id], [Name]) VALUES(7, N'Reopen')
INSERT StackExchange.[VoteTypes] ([Id], [Name]) VALUES(8, N'BountyStart')
INSERT StackExchange.[VoteTypes] ([Id], [Name]) VALUES(9, N'BountyClose')
INSERT StackExchange.[VoteTypes] ([Id], [Name]) VALUES(10,N'Deletion')
INSERT StackExchange.[VoteTypes] ([Id], [Name]) VALUES(11,N'Undeletion')
INSERT StackExchange.[VoteTypes] ([Id], [Name]) VALUES(12,N'Spam')
INSERT StackExchange.[VoteTypes] ([Id], [Name]) VALUES(13,N'InformModerator')
INSERT StackExchange.[PostTypes] ([Id], [Type]) VALUES(1, N'Question') 
INSERT StackExchange.[PostTypes] ([Id], [Type]) VALUES(2, N'Answer') 
INSERT StackExchange.[LinkTypes] ([Id], [Type]) VALUES(1, N'Linked')
INSERT StackExchange.[LinkTypes] ([Id], [Type]) VALUES(3, N'Duplicate')

IF 0 = 1--FULLTEXT
  BEGIN
	IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'StackExchange.[Posts]'))
	ALTER FULLTEXT INDEX ON StackExchange.[Posts] DISABLE
	IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'StackExchange.[Posts]'))
	DROP FULLTEXT INDEX ON StackExchange.[Posts]
	IF  EXISTS (SELECT * FROM sysfulltextcatalogs ftc WHERE ftc.name = N'PostFullText')
	DROP FULLTEXT CATALOG [PostFullText]
	CREATE FULLTEXT CATALOG [PostFullText]WITH ACCENT_SENSITIVITY = ON
	AUTHORIZATION dbo
  END



SET ansi_padding  OFF
SET ansi_nulls  ON
SET quoted_identifier  ON

CREATE TABLE StackExchange.[Votes] (
  [Id]           [INT]    NOT NULL,
  [PostId]       [INT]    NOT NULL,
  [UserId]       [INT]    NULL,
  [BountyAmount] [INT]    NULL,
  [VoteTypeId]   [INT]    NOT NULL,
  [CreationDate] [DATETIME]    NOT NULL
  , CONSTRAINT [PK_Votes] PRIMARY KEY CLUSTERED ( [Id] ASC ) ON [PRIMARY]
  ) ON [PRIMARY]

IF 0 = 1-- INDICES
  BEGIN
    CREATE NONCLUSTERED INDEX [IX_Votes_Id_PostId] ON StackExchange.[Votes] (
          [Id] ASC,
          [PostId] ASC)
    ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [IX_Votes_VoteTypeId] ON StackExchange.[Votes] (
          [VoteTypeId] ASC)
    ON [PRIMARY]
  END

SET ansi_nulls  ON
SET quoted_identifier  ON

CREATE TABLE StackExchange.[Users] (
  [Id]             [INT]    NOT NULL,
  [AboutMe]        [NVARCHAR](MAX)    NULL,
  [Age]            [INT]    NULL,
  [CreationDate]   [DATETIME]    NOT NULL,
  [DisplayName]    [NVARCHAR](40)    NOT NULL,
  [DownVotes]      [INT]    NOT NULL,
  [EmailHash]      [NVARCHAR](40)    NULL,
  [LastAccessDate] [DATETIME]    NOT NULL,
  [Location]       [NVARCHAR](100)    NULL,
  [Reputation]     [INT]    NOT NULL,
  [UpVotes]        [INT]    NOT NULL,
  [Views]          [INT]    NOT NULL,
  [WebsiteUrl]     [NVARCHAR](200)    NULL,
  [AccountId]		[INT] NULL
  , CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ( [Id] ASC ) ON [PRIMARY]
  ) ON [PRIMARY]

IF 0 = 1-- INDICES
  BEGIN
    CREATE NONCLUSTERED INDEX [IX_Users_DisplayName] ON StackExchange.[Users] (
          [DisplayName] ASC)
    ON [PRIMARY]
  END


SET ansi_nulls  ON
SET quoted_identifier  ON

CREATE TABLE StackExchange.[Posts] (
  [Id]                    [INT]    NOT NULL,
  [AcceptedAnswerId]      [INT]    NULL,
  [AnswerCount]           [INT]    NULL,
  [Body]                  [NTEXT]    NOT NULL,
  [ClosedDate]            [DATETIME]    NULL,
  [CommentCount]          [INT]    NULL,
  [CommunityOwnedDate]    [DATETIME]    NULL,
  [CreationDate]          [DATETIME]    NOT NULL,
  [FavoriteCount]         [INT]    NULL,
  [LastActivityDate]      [DATETIME]    NOT NULL,
  [LastEditDate]          [DATETIME]    NULL,
  [LastEditorDisplayName] [NVARCHAR](40)    NULL,
  [LastEditorUserId]      [INT]    NULL,
  [OwnerUserId]           [INT]    NULL,
  [ParentId]              [INT]    NULL,
  [PostTypeId]            [INT]    NOT NULL,
  [Score]                 [INT]    NOT NULL,
  [Tags]                  [NVARCHAR](150)    NULL,
  [Title]                 [NVARCHAR](250)    NULL,
  [ViewCount]             [INT]    NOT NULL
  , CONSTRAINT [PK_Posts] PRIMARY KEY CLUSTERED ( [Id] ASC ) ON [PRIMARY]
  -- INDICES ,CONSTRAINT [IX_Posts_Id_AcceptedAnswerId] UNIQUE NONCLUSTERED ([Id] ASC,[AcceptedAnswerId] ASC ) ON [PRIMARY],
  -- INDICES CONSTRAINT [IX_Posts_Id_OwnerUserId] UNIQUE NONCLUSTERED ([Id] ASC,[OwnerUserId] ASC ) ON [PRIMARY],
  -- INDICES CONSTRAINT [IX_Posts_Id_ParentId] UNIQUE NONCLUSTERED ([Id] ASC,[ParentId] ASC ) ON [PRIMARY]
  )ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

IF 0 = 1-- INDICES
  BEGIN
    CREATE NONCLUSTERED INDEX [IX_Posts_Id_PostTypeId] ON StackExchange.[Posts] (
          [Id] ASC,
          [PostTypeId] ASC)
    ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [IX_Posts_PostType] ON StackExchange.[Posts] (
          [PostTypeId] ASC)
    ON [PRIMARY]
  END

IF 0 = 1--FULLTEXT
  BEGIN
	EXEC dbo.Sp_fulltext_table
	  @tabname = N'StackExchange.[Posts]' ,
	  @action = N'create' ,
	  @keyname = N'PK_Posts' ,
	  @ftcat = N'PostFullText'

	DECLARE  @lcid INT

	SELECT @lcid = lcid
	FROM   MASTER.dbo.syslanguages
	WHERE  alias = N'English'

	EXEC dbo.Sp_fulltext_column
	  @tabname = N'StackExchange.[Posts]' ,
	  @colname = N'Body' ,
	  @action = N'add' ,
	  @language = @lcid

	SELECT @lcid = lcid
	FROM   MASTER.dbo.syslanguages
	WHERE  alias = N'English'

	EXEC dbo.Sp_fulltext_column
	  @tabname = N'StackExchange.[Posts]' ,
	  @colname = N'Title' ,
	  @action = N'add' ,
	  @language = @lcid

	EXEC dbo.Sp_fulltext_table
	  @tabname = N'StackExchange.[Posts]' ,
	  @action = N'start_change_tracking'

	EXEC dbo.Sp_fulltext_table
	  @tabname = N'StackExchange.[Posts]' ,
	  @action = N'start_background_updateindex'

  END

SET ansi_nulls  ON
SET quoted_identifier  ON

CREATE TABLE StackExchange.[Comments] (
  [Id]           [INT]    NOT NULL,
  [CreationDate] [DATETIME]    NOT NULL,
  [PostId]       [INT]    NOT NULL,
  [Score]        [INT]    NULL,
  [Text]         [NVARCHAR](700)    NOT NULL,
  [UserId]       [INT]    NULL
  , CONSTRAINT [PK_Comments] PRIMARY KEY CLUSTERED ( [Id] ASC ) ON [PRIMARY]
  ) ON [PRIMARY]

IF 0 = 1-- INDICES
  BEGIN
    CREATE NONCLUSTERED INDEX [IX_Comments_Id_PostId] ON StackExchange.[Comments] (
          [Id] ASC,
          [PostId] ASC)
    ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [IX_Comments_Id_UserId] ON StackExchange.[Comments] (
          [Id] ASC,
          [UserId] ASC)
    ON [PRIMARY]
  END

SET ansi_nulls  ON
SET quoted_identifier  ON

CREATE TABLE StackExchange.[Badges] (
  [Id]     [INT]    NOT NULL,
  [Name]   [NVARCHAR](40)    NOT NULL,
  [UserId] [INT]    NOT NULL,
  [Date]   [DATETIME]    NOT NULL
  , CONSTRAINT [PK_Badges] PRIMARY KEY CLUSTERED ( [Id] ASC ) ON [PRIMARY]
  ) ON [PRIMARY]

CREATE TABLE StackExchange.[PostLinks] (
  Id INT NOT NULL,
  CreationDate DATETIME NOT NULL,
  PostId INT NOT NULL,
  RelatedPostId INT NOT NULL,
  LinkTypeId TINYINT NOT NULL,
  CONSTRAINT [PK_PostLinks] PRIMARY KEY CLUSTERED ([Id] ASC)
) 

IF 0 = 1-- INDICES
  BEGIN
    CREATE NONCLUSTERED INDEX [IX_Badges_Id_UserId] ON StackExchange.[Badges] (
          [Id] ASC,
          [UserId] ASC)
    ON [PRIMARY]
  END

