if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_events_categories]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[events] DROP CONSTRAINT FK_events_categories
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_enrollments_classes]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[enrollments] DROP CONSTRAINT FK_enrollments_classes
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_events_classes]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[events] DROP CONSTRAINT FK_events_classes
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_categories_users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[categories] DROP CONSTRAINT FK_categories_users
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_enrollments_users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[enrollments] DROP CONSTRAINT FK_enrollments_users
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[ginyuu].[udf_ChooseMax]') and xtype in (N'FN', N'IF', N'TF'))
drop function [ginyuu].[udf_ChooseMax]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[ginyuu].[udf_GetDayMask]') and xtype in (N'FN', N'IF', N'TF'))
drop function [ginyuu].[udf_GetDayMask]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[ginyuu].[udf_GetPrivileges]') and xtype in (N'FN', N'IF', N'TF'))
drop function [ginyuu].[udf_GetPrivileges]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[ginyuu].[udf_GetStatus]') and xtype in (N'FN', N'IF', N'TF'))
drop function [ginyuu].[udf_GetStatus]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[ginyuu].[udf_IsAllowed]') and xtype in (N'FN', N'IF', N'TF'))
drop function [ginyuu].[udf_IsAllowed]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[ginyuu].[udf_ParseBeginTime]') and xtype in (N'FN', N'IF', N'TF'))
drop function [ginyuu].[udf_ParseBeginTime]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[ginyuu].[udf_ParseDays]') and xtype in (N'FN', N'IF', N'TF'))
drop function [ginyuu].[udf_ParseDays]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[ginyuu].[udf_ParseEndTime]') and xtype in (N'FN', N'IF', N'TF'))
drop function [ginyuu].[udf_ParseEndTime]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[ginyuu].[udf_ParseTime]') and xtype in (N'FN', N'IF', N'TF'))
drop function [ginyuu].[udf_ParseTime]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[ginyuu].[sp_RandomClassTimes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [ginyuu].[sp_RandomClassTimes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[categories]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[classes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[classes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[enrollments]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[enrollments]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[events]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[events]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[ginyuu].[templocations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [ginyuu].[templocations]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[ginyuu].[temptimes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [ginyuu].[temptimes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[users]
GO

CREATE TABLE [dbo].[categories] (
	[categoryid] [int] IDENTITY (1, 1) NOT NULL ,
	[userid] [int] NULL ,
	[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[allowstudent] [int] NOT NULL ,
	[allowprofessor] [int] NOT NULL ,
	[allowta] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[classes] (
	[classid] [int] IDENTITY (1, 1) NOT NULL ,
	[cclassid] [int] NOT NULL ,
	[name] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[code] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[location] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[days] [int] NULL ,
	[begintime] [datetime] NULL ,
	[endtime] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[enrollments] (
	[classid] [int] NOT NULL ,
	[userid] [int] NOT NULL ,
	[isstudent] [bit] NOT NULL ,
	[ista] [bit] NOT NULL ,
	[isprofessor] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[events] (
	[eventid] [int] IDENTITY (1, 1) NOT NULL ,
	[categoryid] [int] NOT NULL ,
	[classid] [int] NULL ,
	[begindate] [datetime] NOT NULL ,
	[enddate] [datetime] NOT NULL ,
	[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [ginyuu].[templocations] (
	[rownumber] [int] NULL ,
	[location] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [ginyuu].[temptimes] (
	[rownumber] [int] NULL ,
	[times] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[users] (
	[userid] [int] IDENTITY (1, 1) NOT NULL ,
	[personid] [int] NOT NULL ,
	[username] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[categories] WITH NOCHECK ADD 
	CONSTRAINT [PK_categories] PRIMARY KEY  CLUSTERED 
	(
		[categoryid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[classes] WITH NOCHECK ADD 
	CONSTRAINT [PK_classes] PRIMARY KEY  CLUSTERED 
	(
		[classid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[events] WITH NOCHECK ADD 
	CONSTRAINT [PK_events] PRIMARY KEY  CLUSTERED 
	(
		[eventid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[users] WITH NOCHECK ADD 
	CONSTRAINT [PK_users] PRIMARY KEY  CLUSTERED 
	(
		[userid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[classes] WITH NOCHECK ADD 
	CONSTRAINT [IX_classes] UNIQUE  NONCLUSTERED 
	(
		[cclassid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[users] WITH NOCHECK ADD 
	CONSTRAINT [IX_users] UNIQUE  NONCLUSTERED 
	(
		[personid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[categories] ADD 
	CONSTRAINT [FK_categories_users] FOREIGN KEY 
	(
		[userid]
	) REFERENCES [dbo].[users] (
		[userid]
	)
GO

ALTER TABLE [dbo].[enrollments] ADD 
	CONSTRAINT [FK_enrollments_classes] FOREIGN KEY 
	(
		[classid]
	) REFERENCES [dbo].[classes] (
		[classid]
	),
	CONSTRAINT [FK_enrollments_users] FOREIGN KEY 
	(
		[userid]
	) REFERENCES [dbo].[users] (
		[userid]
	)
GO

ALTER TABLE [dbo].[events] ADD 
	CONSTRAINT [FK_events_categories] FOREIGN KEY 
	(
		[categoryid]
	) REFERENCES [dbo].[categories] (
		[categoryid]
	),
	CONSTRAINT [FK_events_classes] FOREIGN KEY 
	(
		[classid]
	) REFERENCES [dbo].[classes] (
		[classid]
	)
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

setuser N'ginyuu'
GO

CREATE PROCEDURE sp_RandomClassTimes AS
BEGIN
  DECLARE @maxrow INTEGER
  DECLARE @rndrow INTEGER
  DECLARE @rndvalue VARCHAR(255)
  DECLARE @classid INTEGER

  SELECT @maxrow = MAX(rownumber) FROM temptimes;
  SET @classid = 0
  SELECT TOP 1 @classid = classid FROM classes WHERE days IS NULL;
  WHILE(@classid > 0)
  BEGIN
    SET @rndrow = 1 + FLOOR(RAND() * @maxrow)
    SELECT @rndvalue = times FROM temptimes WHERE rownumber = @rndrow
    UPDATE classes SET 
      begintime = ginyuu.udf_ParseBeginTime(@rndvalue), 
      endtime = ginyuu.udf_ParseEndTime(@rndvalue), 
      days = ginyuu.udf_ParseDays(@rndvalue)
      WHERE classid = @classid
    SET @classid = 0
    SELECT TOP 1 @classid = classid FROM classes WHERE days IS NULL;
  END

  SELECT @maxrow = MAX(rownumber) FROM templocations;
  SET @classid = 0
  SELECT TOP 1 @classid = classid FROM classes WHERE location IS NULL;
  WHILE(@classid > 0)
  BEGIN
    SET @rndrow = 1 + FLOOR(RAND() * @maxrow)
    SELECT @rndvalue = location FROM templocations WHERE rownumber = @rndrow
    UPDATE classes SET location = @rndvalue WHERE classid = @classid
    SET @classid = 0
    SELECT TOP 1 @classid = classid FROM classes WHERE location IS NULL;
  END
END
GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

setuser N'ginyuu'
GO


CREATE FUNCTION udf_ChooseMax (@a int, @b int)
RETURNS INT
AS
BEGIN
DECLARE @i int
  IF @a > @b
    SET @i = @a
  ELSE
    SET @i = @b
RETURN (@i)
END


GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

setuser N'ginyuu'
GO

CREATE FUNCTION udf_GetDayMask(@day INTEGER)
RETURNS INT AS
BEGIN
  RETURN POWER(2,@day-1)
END
GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

setuser N'ginyuu'
GO


CREATE FUNCTION udf_GetPrivileges(@userid int, @categoryid int, @classid int)
RETURNS INT
AS
BEGIN
DECLARE @r int
  SET @r = 0
  SELECT @r = 2
    FROM categories AS c
    WHERE c.categoryid = @categoryid AND c.userid = @userid;
  IF NOT @r = 2
    SELECT @r = ginyuu.udf_ChooseMax(em.isstudent * c.allowstudent, ginyuu.udf_ChooseMax(em.ista * c.allowta, em.isprofessor * c.allowprofessor))
    FROM enrollments AS em, categories AS c
    WHERE em.classid = @classid AND em.userid = @userid AND c.categoryid = @categoryid
RETURN (@r)
END


GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

setuser N'ginyuu'
GO

CREATE FUNCTION ginyuu.udf_GetStatus(@isstudent int, @ista int, @isprofessor int)
RETURNS VARCHAR(255) AS
BEGIN
DECLARE @r VARCHAR(255)
  SET @r = ""
  IF @isstudent <> 0
    SET @r = "student"
  IF @ista <> 0
    SET @r = "ta";
  IF @isprofessor <> 0
    SET @r = "instructor"
  RETURN (@r)
END

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

setuser N'ginyuu'
GO


CREATE FUNCTION udf_IsAllowed
(
  @requiredpermission INT,
  @userid INT,
  @classid INT,
  @allowstudent INT,
  @allowta INT,
  @allowprofessor INT
)
RETURNS bit AS  
BEGIN
DECLARE @b bit
IF
(
 EXISTS
    (
      SELECT * FROM enrollments AS em
      WHERE
      (
        em.userid = @userid
        AND
        (@classid IS NULL OR em.classid = @classid)
        AND
        (
          (em.isstudent * @allowstudent >= @requiredpermission)
          OR
          (em.ista * @allowta >= @requiredpermission)
          OR
          (em.isprofessor * @allowprofessor >= @requiredpermission)
        )
      )  
    )
)
  SET @b = 1
ELSE
  SET @b = 0
RETURN @b
END


GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

setuser N'ginyuu'
GO

CREATE FUNCTION udf_ParseBeginTime(@time VARCHAR(255))
RETURNS DATETIME AS
BEGIN
  DECLARE @times VARCHAR(255)
  SET @times = RIGHT(@time, LEN(@time) - CHARINDEX(" ", @time))
  RETURN SUBSTRING(@times, 1, CHARINDEX("-", @times) - 1)
END
GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

setuser N'ginyuu'
GO

CREATE FUNCTION udf_ParseDays(@time VARCHAR(255))
RETURNS INT AS
BEGIN
  DECLARE @days VARCHAR(255)
  DECLARE @day INTEGER
  SET @days = SUBSTRING(@time, 1, CHARINDEX(" ", @time) - 1)
  SET @day = 0
  IF CHARINDEX("U", @days) > 0
    SET @day = @day | 1
  IF CHARINDEX("M", @days) > 0
    SET @day = @day | 2
  IF CHARINDEX("T", @days) > 0
    SET @day = @day | 4
  IF CHARINDEX("W", @days) > 0
    SET @day = @day | 8
  IF CHARINDEX("R", @days) > 0
    SET @day = @day | 16
  IF CHARINDEX("F", @days) > 0
    SET @day = @day | 32
  IF CHARINDEX("S", @days) > 0
    SET @day = @day | 64
  RETURN @day
END

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

setuser N'ginyuu'
GO

CREATE FUNCTION udf_ParseEndTime(@time VARCHAR(255))
RETURNS DATETIME AS
BEGIN
  DECLARE @times VARCHAR(255)
  SET @times = RIGHT(@time, LEN(@time) - CHARINDEX(" ", @time))
  RETURN RIGHT(@times, LEN(@times) - CHARINDEX("-", @times))
END
GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

setuser N'ginyuu'
GO

CREATE FUNCTION udf_ParseTime(@time VARCHAR(255))
RETURNS VARCHAR(255) AS
BEGIN
  RETURN RIGHT(@time, LEN(@time) - CHARINDEX(" ", @time));
END
GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO