USE	MASTER
GO
DROP DATABASE iproject23
GO
CREATE DATABASE iproject23
GO
USE iproject23
GO

CREATE FUNCTION dbo.getUserFromProduct (@item INT)
RETURNS VARCHAR(50)
BEGIN
	DECLARE @user VARCHAR(50);
	SELECT @user = verkoper FROM Voorwerp WHERE voorwerpnummer = @item
	RETURN @user
END;
GO

CREATE FUNCTION dbo.isverkoper(@gebruikeromtetesten VARCHAR(50))
RETURNS INTEGER AS
BEGIN

	DECLARE		@verkoper		VARCHAR(5);
	DECLARE		@return			INTEGER;

	SET @return = 0;

	SELECT	@verkoper = verkoper
	FROM	Gebruiker
	WHERE	@gebruikeromtetesten = gebruikersnaam;

	IF (@verkoper = 'wel')
	SET @return = 1;


	RETURN @return

END
GO

CREATE FUNCTION dbo.aantalfilesvoorwerp(@voorwerp INTEGER)
RETURNS INTEGER AS
BEGIN

	DECLARE		@return		INTEGER;

	SELECT		@return = count(voorwerpnummer)
	FROM		Bestand
	WHERE		voorwerpnummer = @voorwerp

	RETURN		@return

END
GO

CREATE TABLE Vraag (
vraagnummer				INTEGER				NOT NULL IDENTITY(1,1),
vraagtekst				VARCHAR(255)		NOT NULL,

-- PRIMARY KEYS
CONSTRAINT PK_Vraag_vraagnummer PRIMARY KEY (vraagnummer)
)

CREATE TABLE Gebruiker (
gebruikersnaam			VARCHAR(255)		NOT NULL,
voornaam				VARCHAR(255)		NOT NULL,
achternaam				VARCHAR(255)		NOT NULL,
adresregel1				VARCHAR(255)		NOT NULL,
adresregel2				VARCHAR(255)			NULL,
postcode				VARCHAR(255)		NOT NULL,
plaatsnaam				VARCHAR(255)		NOT NULL,
land					VARCHAR(255)		NOT NULL,
geboortedag				DATE				NOT NULL,
mailbox					VARCHAR(255)		NOT NULL,
wachtwoord				VARCHAR(255)		NOT NULL,
vraagnummer				INTEGER				NOT NULL,
antwoordtekst			VARCHAR(255)		NOT NULL,
verkoper				VARCHAR(255)		NOT NULL		DEFAULT 'niet',
beheerder				VARCHAR(255)		NOT NULL		DEFAULT 'niet',
geblokkeerd				VARCHAR(255)		NOT NULL		DEFAULT 'niet',

-- PRIMARY KEYS
CONSTRAINT PK_Gebruiker_gebruikersnaam PRIMARY KEY (gebruikersnaam),

-- FOREIGN KEYS
CONSTRAINT FK_Gebruiker_vraag FOREIGN KEY (vraagnummer) REFERENCES Vraag (vraagnummer)
ON UPDATE CASCADE ON DELETE NO ACTION,

-- CHECKS
CONSTRAINT CK_Gebruiker CHECK (verkoper in ('wel', 'niet')),
CONSTRAINT CK_Gebruiker_Beheerder CHECK (beheerder in ('wel', 'niet')),
CONSTRAINT CK_Gebruiker_Geblokkeerd CHECK (geblokkeerd in ('wel', 'niet'))
)

CREATE TABLE Gebruikerstelefoon (
volgnummer				INTEGER				NOT NULL IDENTITY(1,1),
gebruikersnaam			VARCHAR(255)		NOT NULL,
telefoonnummer			INTEGER				NOT NULL,

-- PRIMARY KEYS
CONSTRAINT PK_Gebruikerstelefoon_volgnummer_gebruikersnaam PRIMARY KEY (volgnummer, gebruikersnaam),

-- FOREIGN KEYS
CONSTRAINT FK_Gebruikerstelefoon_gebruikersnaam FOREIGN KEY (gebruikersnaam) REFERENCES Gebruiker (gebruikersnaam)
ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE TABLE Verkoper (
gebruikersnaam			VARCHAR(255)		NOT NULL,
banknaam				VARCHAR(255)			NULL,
rekeningnummer			VARCHAR(255)			NULL,
controleoptienaam		VARCHAR(255)		NOT NULL,
creditcardnummer		VARCHAR(255)			NULL,

--PRIMARY KEYS
CONSTRAINT PK_Verkoper_gebruikersnaam PRIMARY KEY (gebruikersnaam),

-- FOREIGN KEYS
CONSTRAINT FK_Verkoper_gebruikernaam	FOREIGN KEY (gebruikersnaam) REFERENCES Gebruiker (gebruikersnaam)
ON UPDATE NO ACTION ON DELETE NO ACTION,

-- CHECKS
CONSTRAINT ck1_Verkoper CHECK (controleoptienaam in ('Creditcard', 'Post')),
CONSTRAINT ck2_Verkoper CHECK (dbo.isverkoper(gebruikersnaam) = 1),
CONSTRAINT ck3_Verkoper CHECK ((creditcardnummer is NULL AND controleoptienaam != 'Creditcard') OR
								(creditcardnummer is NOT NULL AND controleoptienaam = 'Creditcard')),
CONSTRAINT ck4_Verkoper CHECK (	(rekeningnummer is NULL AND creditcardnummer is NOT NULL) OR
								(rekeningnummer is NOT NULL AND creditcardnummer is NULL) OR
								(rekeningnummer is NOT NULL AND creditcardnummer is NOT NULL))
)

CREATE TABLE Voorwerp (
voorwerpnummer			BIGINT				NOT NULL IDENTITY(1,1),
titel					VARCHAR(255)		NOT NULL,
beschrijving			TEXT				NOT NULL,
startprijs				NUMERIC(10,2)		NOT NULL,
betalingswijze			VARCHAR(255)		NOT NULL,
betalingsinstructie		TEXT					NULL,
plaatsnaam				VARCHAR(255)		NOT NULL,
land					VARCHAR(255)		NOT NULL,
looptijd				INTEGER				NOT NULL,
looptijdbegindag		DATE				NOT NULL,
looptijdbegintijdstip	TIME				NOT NULL,
verzendkosten			NUMERIC(10,2)			NULL,
verzendinstructies		TEXT					NULL,
verkoper				VARCHAR(255)		NOT NULL,
koper					VARCHAR(255)			NULL,
looptijdeindedag		DATE				NOT NULL,
looptijdeindetijdstip	TIME				NOT NULL,
veilinggesloten			VARCHAR(255)		NOT NULL,
verkoopprijs			NUMERIC(10,2)			NULL,
geblokkeerd				VARCHAR(255)		NOT NULL 	DEFAULT 'niet',

-- PRIMARY KEYS
CONSTRAINT PK_Voorwerp_voorwerpnummer PRIMARY KEY (voorwerpnummer),

-- FOREIGN KEYS
CONSTRAINT FK_Voorwerp_koper		FOREIGN KEY (koper)		REFERENCES Gebruiker (gebruikersnaam)
ON UPDATE CASCADE ON DELETE NO ACTION,
CONSTRAINT FK_Voorwerp_verkoper		FOREIGN KEY (verkoper)	REFERENCES Verkoper (gebruikersnaam)
ON UPDATE CASCADE ON DELETE NO ACTION,
CONSTRAINT CK_Voorwerp_geblokkeerd CHECK (geblokkeerd in ('wel', 'niet'))
)

CREATE TABLE Bestand (
filenaam				VARCHAR(255)		NOT NULL,
voorwerpnummer			BIGINT				NOT NULL

-- PRIMARY KEYS
CONSTRAINT PK_Bestand_filenaam PRIMARY KEY (filenaam),

-- FOREIGN KEYS
CONSTRAINT FK_Bestand_voorwerpnummer FOREIGN KEY (voorwerpnummer) REFERENCES Voorwerp (voorwerpnummer)
ON UPDATE CASCADE ON DELETE CASCADE,
-- CHECK CONSTRAINTS
CONSTRAINT ck_Bestand CHECK (dbo.aantalfilesvoorwerp(voorwerpnummer) <= 4)
)

CREATE TABLE Bod (
voorwerpnummer			BIGINT				NOT NULL,
bodbedrag				NUMERIC(10,2)		NOT NULL,
gebruikersnaam			VARCHAR(255)		NOT NULL,
boddag					DATE				NOT NULL,
bodtijdstip				TIME				NOT NULL,


-- PRIMARY KEYS
CONSTRAINT PK_Bod_voorwerp_bodbedrag PRIMARY KEY (voorwerpnummer, bodbedrag),

-- FOREIGN KEYS
CONSTRAINT FK_Bod_voorwerpnummer	FOREIGN KEY (voorwerpnummer)	REFERENCES Voorwerp (voorwerpnummer)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT FK_Bod_gebruiker			FOREIGN KEY (gebruikersnaam)	REFERENCES Gebruiker (gebruikersnaam)
ON UPDATE NO ACTION ON DELETE NO ACTION,

-- CHECK CONSTRAINTS
CONSTRAINT CH_Bod_verkoper	CHECK(dbo.getUserFromProduct(voorwerpnummer) <> gebruikersnaam)
)

CREATE TABLE Feedback (
voorwerpnummer			BIGINT				NOT NULL,
soortgebruiker			VARCHAR(255)		NOT NULL,
feedbacksoort			VARCHAR(255)		NOT NULL,
dag						DATE				NOT NULL,
tijdstip				TIME				NOT NULL,
commentaar				VARCHAR(255)			NULL,


-- PRIMARY KEYS
CONSTRAINT PK_Feedback_voorwerpnummer_gebruiker PRIMARY KEY (voorwerpnummer, soortgebruiker),

-- FOREIGN KEYS
CONSTRAINT FK_Feedback_voorwerpnummer FOREIGN KEY (voorwerpnummer) REFERENCES Voorwerp (voorwerpnummer)
ON UPDATE CASCADE ON DELETE CASCADE,

-- CHECKS
CONSTRAINT CK_Feedback1	CHECK (feedbacksoort in ('Negatief', 'Neutraal', 'Positief')),
CONSTRAINT CK_Feedback2	CHECK (soortgebruiker in ('Koper', 'Verkoper')),
)

CREATE TABLE Rubriek (
rubrieknummer			INTEGER				NOT NULL IDENTITY(1,1),
rubrieknaam				VARCHAR(255)		NOT NULL,
parentrubrieknummer		INTEGER				    NULL,
volgnummer				INTEGER				NOT NULL,

-- PRIMARY KEYS
CONSTRAINT PK_Rubriek_rubrieknummer PRIMARY KEY (rubrieknummer),

-- FOREIGN KEYS
CONSTRAINT FK_Rubriek_subrubrieknummer FOREIGN KEY (parentrubrieknummer) REFERENCES Rubriek (rubrieknummer)
ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE TABLE Voorwerpinrubriek (
voorwerpnummer			BIGINT				NOT NULL,
rubrieklaagste			INTEGER				NOT NULL,

-- PRIMARY KEYS
CONSTRAINT PK_Voorwerpinrubriek_voorwerpnummer_rubrieklaagste PRIMARY KEY (voorwerpnummer, rubrieklaagste),

-- FOREIGN KEYS
CONSTRAINT FK_Voorwerpinrubriek_voorwerpnummer	FOREIGN KEY (voorwerpnummer) REFERENCES Voorwerp (voorwerpnummer)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT FK_Voorwerpinrubriek_rubrieklaagste	FOREIGN KEY (rubrieklaagste) REFERENCES Rubriek (rubrieknummer)
ON UPDATE CASCADE ON DELETE CASCADE
)

USE master
GO