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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[articles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[articles]
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].[departments]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[departments]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[emails]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[emails]
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'[dbo].[ratings]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ratings]
GO

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

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

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

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

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

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[templates]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[templates]
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].[announcements] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[xdate] [smalldatetime] NULL ,
	[xtime] [smalldatetime] NULL ,
	[subject] [nvarchar] (255) NULL ,
	[message] [ntext] NULL ,
	[sendemail] [tinyint] NULL ,
	[recipient] [nvarchar] (10) NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[articles] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[category] [int] NULL ,
	[subject] [nvarchar] (255) NULL ,
	[body] [ntext] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[categories] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[name] [nvarchar] (50) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[departments] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[name] [nvarchar] (50) NULL ,
	[email] [nvarchar] (50) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[emails] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[xfrom] [nvarchar] (50) NULL ,
	[xto] [nvarchar] (50) NULL ,
	[subject] [nvarchar] (255) NULL ,
	[message] [ntext] NULL ,
	[xdate] [smalldatetime] NULL ,
	[xtime] [smalldatetime] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[events] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[startdate] [smalldatetime] NULL ,
	[starttime] [smalldatetime] NULL ,
	[description] [nvarchar] (255) NULL ,
	[notify1] [int] NULL ,
	[notify2] [int] NULL ,
	[notify3] [int] NULL ,
	[member] [nvarchar] (50) NULL ,
	[xuser] [nvarchar] (50) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ratings] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[response] [int] NULL ,
	[comment] [ntext] NULL ,
	[rating] [tinyint] NULL ,
	[xdate] [smalldatetime] NULL ,
	[xtime] [smalldatetime] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[requestfields] (
	[name] [nvarchar] (50) NOT NULL ,
	[label] [nvarchar] (50) NULL ,
	[maxlength] [int] NULL ,
	[ftype] [nvarchar] (20) NULL ,
	[forder] [int] NULL ,
	[fvalue] [nvarchar] (255) NULL ,
	[validation] [nvarchar] (10) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[requests] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[xuser] [nvarchar] (50) NULL ,
	[department] [int] NULL ,
	[ownership] [nvarchar] (50) NULL ,
	[origin] [nvarchar] (10) NULL ,
	[status] [nvarchar] (10) NULL ,
	[closedby] [nvarchar] (50) NULL ,
	[xdate] [smalldatetime] NULL ,
	[xtime] [smalldatetime] NULL ,
	[xkey] [nvarchar] (255) NULL ,
	[priority] [int] NULL ,
	[subject] [nvarchar] (255) NULL ,
	[description] [ntext] NULL ,
	[filename] [nvarchar] (255) NULL ,
	[email] [nvarchar] (255) NULL ,
	[awaiting] [nvarchar] (10) NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[responses] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[request] [int] NULL ,
	[private] [tinyint] NULL ,
	[xdate] [smalldatetime] NULL ,
	[xtime] [smalldatetime] NULL ,
	[cc] [nvarchar] (50) NULL ,
	[message] [ntext] NULL ,
	[filename] [nvarchar] (255) NULL ,
	[notify] [tinyint] NULL ,
	[staffmember] [nvarchar] (50) NULL ,
	[xaction] [nvarchar] (50) NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[settings] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[setting] [nvarchar] (50) NULL ,
	[xvalue] [nvarchar] (255) NULL ,
	[description] [nvarchar] (255) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[staff] (
	[login] [nvarchar] (50) NOT NULL ,
	[pass] [nvarchar] (255) NULL ,
	[name] [nvarchar] (50) NULL ,
	[email] [nvarchar] (50) NULL ,
	[notify] [tinyint] NULL ,
	[signature] [nvarchar] (255) NULL ,
	[active] [tinyint] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[stafftodepartments] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[member] [nvarchar] (50) NULL ,
	[department] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[templates] (
	[id] [int] NOT NULL ,
	[subject] [nvarchar] (255) NULL ,
	[content] [ntext] NULL ,
	[member] [nvarchar] (50) NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[users] (
	[login] [nvarchar] (50) NOT NULL ,
	[pass] [nvarchar] (255) NULL ,
	[name] [nvarchar] (50) NULL ,
	[email] [nvarchar] (50) NULL ,
	[url] [nvarchar] (50) NULL ,
	[company] [nvarchar] (50) NULL ,
	[approved] [tinyint] NULL ,
	[reason] [nvarchar] (255) NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[announcements] WITH NOCHECK ADD 
	CONSTRAINT [PK_announcements] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[articles] WITH NOCHECK ADD 
	CONSTRAINT [PK_articles] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

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

ALTER TABLE [dbo].[departments] WITH NOCHECK ADD 
	CONSTRAINT [PK_departments] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[emails] WITH NOCHECK ADD 
	CONSTRAINT [PK_emails] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

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

ALTER TABLE [dbo].[ratings] WITH NOCHECK ADD 
	CONSTRAINT [PK_ratings] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[requestfields] WITH NOCHECK ADD 
	CONSTRAINT [PK_requestfields] PRIMARY KEY  CLUSTERED 
	(
		[name]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[requests] WITH NOCHECK ADD 
	CONSTRAINT [PK_requests] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[responses] WITH NOCHECK ADD 
	CONSTRAINT [PK_responses] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[settings] WITH NOCHECK ADD 
	CONSTRAINT [PK_settings] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[staff] WITH NOCHECK ADD 
	CONSTRAINT [PK_staff] PRIMARY KEY  CLUSTERED 
	(
		[login]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[stafftodepartments] WITH NOCHECK ADD 
	CONSTRAINT [PK_stafftodepartments] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[templates] WITH NOCHECK ADD 
	CONSTRAINT [PK_templates] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

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

ALTER TABLE [dbo].[articles] ADD 
	CONSTRAINT [FK_articles_categories] FOREIGN KEY 
	(
		[category]
	) REFERENCES [dbo].[categories] (
		[id]
	) ON DELETE CASCADE 
GO

ALTER TABLE [dbo].[events] ADD 
	CONSTRAINT [FK_events_staff] FOREIGN KEY 
	(
		[member]
	) REFERENCES [dbo].[staff] (
		[login]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_events_users] FOREIGN KEY 
	(
		[xuser]
	) REFERENCES [dbo].[users] (
		[login]
	) ON DELETE CASCADE 
GO

ALTER TABLE [dbo].[ratings] ADD 
	CONSTRAINT [FK_ratings_responses] FOREIGN KEY 
	(
		[response]
	) REFERENCES [dbo].[responses] (
		[id]
	) ON DELETE CASCADE 
GO

ALTER TABLE [dbo].[requests] ADD 
	CONSTRAINT [FK_requests_departments] FOREIGN KEY 
	(
		[department]
	) REFERENCES [dbo].[departments] (
		[id]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_requests_staff] FOREIGN KEY 
	(
		[ownership]
	) REFERENCES [dbo].[staff] (
		[login]
	) NOT FOR REPLICATION ,
	CONSTRAINT [FK_requests_staff1] FOREIGN KEY 
	(
		[closedby]
	) REFERENCES [dbo].[staff] (
		[login]
	) NOT FOR REPLICATION ,
	CONSTRAINT [FK_requests_users] FOREIGN KEY 
	(
		[xuser]
	) REFERENCES [dbo].[users] (
		[login]
	) ON DELETE CASCADE 
GO

alter table [dbo].[requests] nocheck constraint [FK_requests_staff]
GO

alter table [dbo].[requests] nocheck constraint [FK_requests_staff1]
GO

ALTER TABLE [dbo].[responses] ADD 
	CONSTRAINT [FK_responses_requests] FOREIGN KEY 
	(
		[request]
	) REFERENCES [dbo].[requests] (
		[id]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_responses_staff] FOREIGN KEY 
	(
		[staffmember]
	) REFERENCES [dbo].[staff] (
		[login]
	) NOT FOR REPLICATION 
GO

alter table [dbo].[responses] nocheck constraint [FK_responses_staff]
GO

ALTER TABLE [dbo].[stafftodepartments] ADD 
	CONSTRAINT [FK_stafftodepartments_departments] FOREIGN KEY 
	(
		[department]
	) REFERENCES [dbo].[departments] (
		[id]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_stafftodepartments_staff] FOREIGN KEY 
	(
		[member]
	) REFERENCES [dbo].[staff] (
		[login]
	)
GO

ALTER TABLE [dbo].[templates] ADD 
	CONSTRAINT [FK_templates_staff] FOREIGN KEY 
	(
		[member]
	) REFERENCES [dbo].[staff] (
		[login]
	)
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.viewrequests
AS
SELECT     dbo.requests.*, dbo.departments.name AS name
FROM         dbo.departments INNER JOIN
                      dbo.requests ON dbo.departments.id = dbo.requests.department

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

INSERT INTO settings (setting,xvalue,description) Values('wd_title','WebDesk','WebDesk Title')
GO
INSERT INTO settings (setting,xvalue,description) Values('publicurl','http://www.yakom.it','Public URL (http://www.server.com/webdesk/)')
GO
INSERT INTO settings (setting,xvalue,description) Values('companyname','Yakom','Company name')
GO
INSERT INTO settings (setting,xvalue,description) Values('companyurl','http://www.yakom.it','Company Website (http://www.company.com)')
GO
INSERT INTO settings (setting,xvalue,description) Values('sysemail','sys@email.com','System administrator email')
GO
INSERT INTO settings (setting,xvalue,description) Values('uploadlimit','102400','Maximum upload filesize in bytes (102400 bytes = 100 Kb.)')
GO
INSERT INTO settings (setting,xvalue,description) Values('encryptkey','gnimmax','Encryption Key')
GO
INSERT INTO settings (setting,xvalue,description) Values('popserver','','POP3 Incoming Mail Server')
GO
INSERT INTO settings (setting,xvalue,description) Values('popaccount','','POP3 account name')
GO
INSERT INTO settings (setting,xvalue,description) Values('poppwd','','POP3 account password')
GO
INSERT INTO settings (setting,xvalue,description) Values('fieldbgcolor','#F2F2F2','Field Background Color (HEX)')
GO
INSERT INTO settings (setting,xvalue,description) Values('stafffieldbgcolor','#FFFFCC','Staff response background color (HEX)')
GO
INSERT INTO settings (setting,xvalue,description) Values('urgentnotify','','Urgent notification email address (if empty no notification is sent)')
GO
INSERT INTO settings (setting,xvalue,description) Values('staff_request_deletion','1','Deny staff members permission to delete requests')
GO
INSERT INTO settings (setting,xvalue,description) Values('user_kb_access','1','Require login to access Knowledge Base')
GO
INSERT INTO settings (setting,xvalue,description) Values('user_emailrequest_access','1','Require login to email a request')
GO
INSERT INTO settings (setting,xvalue,description) Values('user_request_access','1','Require login to submit a request')
GO
INSERT INTO settings (setting,xvalue,description) Values('user_track_access','1','Require login to track a request')
GO
INSERT INTO settings (setting,xvalue,description) Values('user_respond_access','1','Require login to respond to a request')
GO
INSERT INTO settings (setting,xvalue,description) Values('user_validation','1','Require user approval by admin')
GO
INSERT INTO settings (setting,xvalue,description) Values('lg_wdname','WebDesk Manager 1.0','WebDesk name')
GO

INSERT INTO staff (login,pass,name,email,notify,signature,active) Values('admin','067706AE073906FD074D','Admin','admin@webdesk.com',0,'Sys Admin',1)
GO
INSERT INTO users (login,approved) Values('anonymous',1)	
GO
INSERT INTO departments (name,email) Values('Default','default@webdesk.com')	
GO