DROP TABLE IF EXISTS Video;



DROP TABLE IF EXISTS Comments;



DROP TABLE IF EXISTS URL;



DROP TABLE IF EXISTS Photo;



DROP TABLE IF EXISTS Text1;



DROP TABLE IF EXISTS Post;



DROP TABLE IF EXISTS Member;



DROP TABLE IF EXISTS Channel;



DROP TABLE IF EXISTS Group1;



DROP TABLE IF EXISTS Block1;



DROP TABLE IF EXISTS Connection1;



DROP TABLE IF EXISTS Settings;



DROP TABLE IF EXISTS Password1;



DROP TABLE IF EXISTS User;



DROP SCHEMA dbo;



DROP SCHEMA guest;



DROP SCHEMA db_owner;



DROP SCHEMA db_accessadmin;



DROP SCHEMA db_securityadmin;



DROP SCHEMA db_ddladmin;



DROP SCHEMA db_backupoperator;



DROP SCHEMA db_datareader;



DROP SCHEMA db_datawriter;



DROP SCHEMA db_denydatareader;



DROP SCHEMA db_denydatawriter;



CREATE SCHEMA dbo;



CREATE SCHEMA guest;



CREATE SCHEMA db_owner;



CREATE SCHEMA db_accessadmin;



CREATE SCHEMA db_securityadmin;



CREATE SCHEMA db_ddladmin;



CREATE SCHEMA db_backupoperator;



CREATE SCHEMA db_datareader;



CREATE SCHEMA db_datawriter;



CREATE SCHEMA db_denydatareader;



CREATE SCHEMA db_denydatawriter;


CREATE TABLE User (
 UserID      NUMERIC(14,0) NOT NULL ,
 Email       VARCHAR(320) NOT NULL ,
 Phone       VARCHAR(20) NOT NULL ,
 Location    NVARCHAR(50) NULL ,
 Birthday    DATE NOT NULL ,
 School      NVARCHAR(50) NULL ,
 Employer    NVARCHAR(50) NULL ,
 Bio         NVARCHAR(1000) NULL ,
 Sex         BIT NULL ,
 Gender      TINYINT NULL ,
 HomepageURL VARCHAR(512) NOT NULL ,

 CONSTRAINT PK_User PRIMARY KEY CLUSTERED (UserID ASC)
);



CREATE TABLE Group1 (

 GroupID     NUMERIC(12,0) NOT NULL ,
 CreatorID   NUMERIC(14,0) NOT NULL ,
 Description NVARCHAR(5000) NOT NULL ,

 CONSTRAINT PK_Group PRIMARY KEY CLUSTERED (GroupID ASC),
 CONSTRAINT FK_317 FOREIGN KEY (CreatorID)
  REFERENCES User(UserID)
);



CREATE TABLE Block1
(
 BlockID     NUMERIC(17,0) NOT NULL ,
 IssuerID    NUMERIC(14,0) NOT NULL ,
 TargetID    NUMERIC(14,0) NOT NULL ,
 DateAdded   DATETIME(6) NOT NULL ,
 DateRemoved DATETIME(6) NOT NULL ,

 CONSTRAINT PK_Block PRIMARY KEY CLUSTERED (BlockID ASC),
 CONSTRAINT FK_262 FOREIGN KEY (IssuerID)
  REFERENCES User(UserID),
 CONSTRAINT FK_266 FOREIGN KEY (TargetID)
  REFERENCES User(UserID)
);




CREATE TABLE Connection1
(
 ConnectionID NUMERIC(17,0) NOT NULL ,
 ReqUserID    NUMERIC(14,0) NOT NULL ,
 AckUserID    NUMERIC(14,0) NOT NULL ,
 DateReq      DATETIME(6) NOT NULL ,
 DateAck      DATETIME(6) NULL ,
 Success      BIT NULL ,
 DateEnded    DATETIME(6) NULL ,
 WhoEnded     BIT NULL ,

 CONSTRAINT PK_Relationship PRIMARY KEY CLUSTERED (ConnectionID ASC),
 CONSTRAINT FK_237 FOREIGN KEY (ReqUserID)
  REFERENCES User(UserID),
 CONSTRAINT FK_241 FOREIGN KEY (AckUserID)
  REFERENCES User(UserID)
);



CREATE TABLE Settings
(
 UserID          NUMERIC(14,0) NOT NULL ,
 EmailPrivacy    BIT NULL ,
 PhonePrivacy    BIT NULL ,
 HomepagePrivacy BIT NULL ,
 DetailsPrivacy  BIT NULL ,

 CONSTRAINT PK_Settings PRIMARY KEY CLUSTERED (UserID ASC),
 CONSTRAINT FK_151 FOREIGN KEY (UserID)
  REFERENCES User(UserID)
);




CREATE TABLE Password1
(
 Hash        CHAR(60) NOT NULL ,
 UserID      NUMERIC(14,0) NOT NULL ,
 DateCreated DATETIME NOT NULL ,
 IsActive    BIT NOT NULL ,

 CONSTRAINT PK_Password PRIMARY KEY CLUSTERED (Hash ASC, UserID ASC),
 CONSTRAINT FK_124 FOREIGN KEY (UserID)
  REFERENCES User(UserID)
);





CREATE TABLE Member
(
 UserID     NUMERIC(14,0) NOT NULL ,
 GroupID    NUMERIC(12,0) NOT NULL ,
 DateJoined DATETIME(6) NOT NULL ,
 DateLeft   DATETIME(6) NULL ,
 Nickname   NVARCHAR(50) NULL ,
 MemberID   NUMERIC(15,0) NOT NULL ,

 CONSTRAINT PK_Member PRIMARY KEY CLUSTERED (MemberID ASC),
 CONSTRAINT FK_301 FOREIGN KEY (UserID)
  REFERENCES User(UserID),
 CONSTRAINT FK_306 FOREIGN KEY (GroupID)
  REFERENCES Group1(GroupID)
);





CREATE TABLE Channel
(
 ChannelID   NUMERIC(15,0) NOT NULL ,
 GroupID     NUMERIC(12,0) NULL ,
 DateCreated DATETIME(6) NOT NULL ,
 Description VARCHAR(1000) NOT NULL ,
 Visible     BIT NOT NULL ,

 CONSTRAINT PK_Group PRIMARY KEY CLUSTERED (ChannelID ASC),
 CONSTRAINT FK_291 FOREIGN KEY (GroupID)
  REFERENCES Group1(GroupID)
);




CREATE TABLE Post
(
 PostID          NUMERIC(16,0) NOT NULL ,
 AuthorID        NUMERIC(14,0) NOT NULL ,
 ChannelID       NUMERIC(15,0) NOT NULL ,
 DateCreated     DATETIME(6) NOT NULL ,
 PostType        TINYINT NOT NULL ,
 Scope           TINYINT NOT NULL ,
 Deleted         BIT NOT NULL ,
 AllowComments   BIT NULL ,
 AllowRatings    BIT NULL ,
 PreviousVersion NUMERIC(16,0) NULL ,

 CONSTRAINT PK_Post PRIMARY KEY CLUSTERED (PostID ASC),
 CONSTRAINT FK_94 FOREIGN KEY (AuthorID)
  REFERENCES User(UserID),
 CONSTRAINT FK_160 FOREIGN KEY (ChannelID)
  REFERENCES Channel(ChannelID),
 CONSTRAINT FK_321 FOREIGN KEY (PreviousVersion)
  REFERENCES Post(PostID)
);




CREATE TABLE Video
(
 PostID NUMERIC(16,0) NOT NULL ,
 URL    VARCHAR(512) NOT NULL ,

 CONSTRAINT PK_Video PRIMARY KEY CLUSTERED (PostID ASC),
 CONSTRAINT FK_213 FOREIGN KEY (PostID)
  REFERENCES Post(PostID)
);




CREATE TABLE Comments
(
 CommentID       NUMERIC(18,0) NOT NULL ,
 AuthorID        NUMERIC(14,0) NOT NULL ,
 PostID          NUMERIC(16,0) NOT NULL ,
 DateCreated     DATETIME(6) NOT NULL ,
 Body            NVARCHAR(5000) NOT NULL ,
 ParentID        NUMERIC(18,0) NULL ,
 PreviousVersion NUMERIC(18,0) NOT NULL ,

 CONSTRAINT PK_Comments PRIMARY KEY CLUSTERED (CommentID ASC),
 CONSTRAINT FK_195 FOREIGN KEY (PostID)
  REFERENCES Post(PostID),
 CONSTRAINT FK_203 FOREIGN KEY (ParentID)
  REFERENCES Comments(CommentID),
 CONSTRAINT FK_207 FOREIGN KEY (AuthorID)
  REFERENCES User(UserID),
 CONSTRAINT FK_325 FOREIGN KEY (PreviousVersion)
  REFERENCES Comments(CommentID)
);





CREATE TABLE URL
(
 PostID NUMERIC(16,0) NOT NULL ,
 URL    VARCHAR(512) NOT NULL ,
 Label  VARCHAR(100) NULL ,

 CONSTRAINT PK_URL PRIMARY KEY CLUSTERED (PostID ASC),
 CONSTRAINT FK_185 FOREIGN KEY (PostID)
  REFERENCES Post(PostID)
);



CREATE TABLE Photo
(
 PostID    NUMERIC(16,0) NOT NULL ,
 URL       VARCHAR(512) NOT NULL ,
 Caption   NVARCHAR(5000) NOT NULL ,
 DateTaken DATETIME(6) NULL ,

 CONSTRAINT PK_Photo PRIMARY KEY CLUSTERED (PostID ASC),
 CONSTRAINT FK_174 FOREIGN KEY (PostID)
  REFERENCES Post(PostID)
);



CREATE TABLE Text1
(
 PostID NUMERIC(16,0) NOT NULL ,
 Body   TEXT(50000) NOT NULL ,
 Title  NVARCHAR(100) NOT NULL ,

 CONSTRAINT PK_Text PRIMARY KEY CLUSTERED (PostID ASC),
 CONSTRAINT FK_138 FOREIGN KEY (PostID)
  REFERENCES Post(PostID)
);