/*_________________________________________________*/
/*===============| Create Database |===============*/
/*‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾*/

USE master
GO

DROP DATABASE Eenmaalandermaal
GO

CREATE DATABASE Eenmaalandermaal
GO

USE Eenmaalandermaal

/*_________________________________________________*/
/*================| Create Script |================*/
/*‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾*/

go


--Tabellen creëren:
CREATE TABLE Vraag (
	vraagnummer				INT							NOT NULL,
	vraagtekst				VARCHAR(255)		NOT NULL,
	CONSTRAINT PK_Vraag PRIMARY KEY (vraagnummer)
)
go

CREATE TABLE Gebruiker (
	gebruikersnaam			VARCHAR(50)		NOT NULL,
	voornaam				VARCHAR(35)			NOT NULL,
	achternaam				VARCHAR(35)			NOT NULL,
	adresregel1				VARCHAR(95)			NOT NULL,
	adresregel2				VARCHAR(95)			NULL,
	postcode				VARCHAR(12)			NOT NULL,
	plaatsnaam				VARCHAR(100)		NOT NULL,
	land					VARCHAR(100)		NOT NULL,
	geboortedag				DATE				NOT NULL,
	mailbox					VARCHAR(255)		NOT NULL,
	wachtwoord				CHAR(64)			NOT NULL,
	vraagnummer				INT					NOT NULL,
	antwoordtekst			VARCHAR(255)		NOT NULL,
	registratiedatum 		DATE 				DEFAULT GETDATE(),
	land2					VARCHAR(100) NULL,
	plaatsnaam2				VARCHAR(100) NULL,
	postcode2				VARCHAR(12) NULL,
	geslacht				VARCHAR(5)		NOT NULL DEFAULT 'Man',
	CONSTRAINT PK_Gebruiker PRIMARY KEY (gebruikersnaam),
	CONSTRAINT AK_Mailbox UNIQUE (mailbox),
	CONSTRAINT FK_Vraag FOREIGN KEY (vraagnummer) REFERENCES Vraag(vraagnummer),
	CONSTRAINT CHK_geslacht CHECK (geslacht = 'Man' OR geslacht = 'Vrouw'),
	CONSTRAINT CHK_GebruikersnaamLengte CHECK (LEN(gebruikersnaam) <= 50) ,
	CONSTRAINT CHK_MailboxLengte CHECK (LEN(mailbox) <= 50)
)
go

CREATE TABLE Verkoper (
	gebruikersnaam			VARCHAR(50)		NOT NULL,
	banknaam				VARCHAR(50)			NULL,
	rekeningnummer			VARCHAR(34)			NULL,
	controleoptie			VARCHAR(20)			NOT NULL,
	creditcardnummer		DECIMAL(18,0)		NULL
	CONSTRAINT PK_Verkoper PRIMARY KEY (gebruikersnaam),
	CONSTRAINT FK_Gebruiker_3 FOREIGN KEY (gebruikersnaam) REFERENCES Gebruiker(gebruikersnaam),
	CONSTRAINT CHK_Controleoptie CHECK (controleoptie IN ('Creditcard', 'Post')),
)
go
CREATE TABLE Voorwerp (
	voorwerpnummer			INT IDENTITY(1,1)	NOT NULL,
	titel					TEXT				NOT NULL,
	beschrijving			TEXT				NOT NULL,
	startprijs				NUMERIC(10,2)		NOT NULL,
	betalingswijze			VARCHAR(100)		NOT NULL,
	betalingsinstructie		VARCHAR(255)		NULL,
	plaatsnaam				VARCHAR(200)		NOT NULL,
	landnaam				VARCHAR(200)		NOT NULL,
	looptijd				TINYINT				NOT NULL DEFAULT 7,
	looptijdbegindag		DATE				DEFAULT GETDATE(),
	looptijdbegintijdstip	TIME				DEFAULT CONVERT(time, CURRENT_TIMESTAMP),
	verzendkosten			NUMERIC(10,2)		NULL,
	verzendinstructies		VARCHAR(255)		NULL,
	verkoper			  VARCHAR(50)		NOT NULL,
	koper					VARCHAR(50)		NULL,
	looptijdeindedag		AS DATEADD(day, looptijd, looptijdbegindag),
	looptijdeindetijdstip	TIME				DEFAULT CONVERT(time, CURRENT_TIMESTAMP),
	veiliggesloten			BIT					NOT NULL,
	verkoopprijs			NUMERIC(10,2)		NULL,
	CONSTRAINT PK_Voorwerp PRIMARY KEY (voorwerpnummer),
	CONSTRAINT CHK_looptijd CHECK (looptijd IN (1,3,5,7,10)),
	CONSTRAINT FK_Gebruiker_1 FOREIGN KEY (verkoper) REFERENCES Verkoper(gebruikersnaam),
	CONSTRAINT FK_Gebruiker_2 FOREIGN KEY (koper) REFERENCES Gebruiker(gebruikersnaam),
	CONSTRAINT CHK_startprijs CHECK (startprijs >=0)
)
go

CREATE FUNCTION Maximaalvier ()
RETURNS BIT
AS
BEGIN
	IF EXISTS (	SELECT voorwerpnummer, COUNT(voorwerpnummer)
				FROM Bestand
				GROUP BY voorwerpnummer
				HAVING COUNT(voorwerpnummer) > 4 )
				RETURN 1
	RETURN 0
END
go

CREATE TABLE Bestand (
	bestandsnaam			VARCHAR(500)		NOT NULL,
	voorwerpnummer			INT					NOT NULL,
	CONSTRAINT PK_Bestand PRIMARY KEY (bestandsnaam, voorwerpnummer),
	CONSTRAINT CHK_Bestand CHECK (dbo.Maximaalvier() = 0),
	CONSTRAINT FK_Voorwerp_1 FOREIGN KEY (voorwerpnummer) REFERENCES Voorwerp(voorwerpnummer)
)
go

--Functie om ervoor te zorgen dat er geen kleiner of gelijk bod kan worden gemaakt dan de startprijs:
CREATE FUNCTION Startprijs (@voorwerpnummer INT)
RETURNS NUMERIC(10,2)
AS
BEGIN
	DECLARE @startprijs NUMERIC(10,2)
	SELECT @startprijs = startprijs
	FROM Voorwerp
	WHERE voorwerpnummer = @voorwerpnummer
	RETURN @startprijs
END
go

CREATE TABLE Bod (
	voorwerpnummer			INT					NOT NULL,
	bodbedrag				NUMERIC(10,2)		NOT NULL,
	gebruikersnaam			VARCHAR(50)		NOT NULL,
	boddag					DATE				NOT NULL,
	bodtijdstip				TIME				NOT NULL,
	CONSTRAINT PK_Bod PRIMARY KEY (voorwerpnummer, bodbedrag),
	CONSTRAINT AK_Bod UNIQUE (gebruikersnaam, boddag, bodtijdstip),
	CONSTRAINT FK_Voorwerp_2 FOREIGN KEY (voorwerpnummer) REFERENCES Voorwerp(voorwerpnummer),
	CONSTRAINT FK_Gebruiker_5 FOREIGN KEY (gebruikersnaam) REFERENCES Gebruiker(gebruikersnaam),
	--CONSTRAINT CHK_bodbedrag CHECK (dbo.Geenbod(voorwerpnummer) = 1),
	CONSTRAINT CHK_hogerdanstart CHECK (bodbedrag > dbo.Startprijs(voorwerpnummer))
)
go

CREATE TABLE Feedback (
	voorwerpnummer			INT					NOT NULL,
	gebruikersnaam			VARCHAR(50)			NOT NULL,
	gebruikersoort			VARCHAR(8)			NOT NULL,
	feedbacksoort			CHAR(8)				NOT NULL,
	feedbackdag				DATE				NOT NULL,
	feedbacktijd			TIME				NOT NULL,
	feedback				TEXT				NULL,
	CONSTRAINT PK_Feedback PRIMARY KEY (voorwerpnummer),
	CONSTRAINT FK_Voorwerp_3 FOREIGN KEY (voorwerpnummer) REFERENCES Voorwerp(voorwerpnummer),
	CONSTRAINT FK_Gebruiker_8 FOREIGN KEY (gebruikersnaam) REFERENCES Gebruiker(gebruikersnaam),
	CONSTRAINT CHK_Gebruikersoort CHECK (gebruikersoort IN ('Verkoper', 'Koper')),
	CONSTRAINT CHK_Feedbacksoort CHECK (feedbacksoort IN ('Negatief','Neutraal','Positief'))
)
go

CREATE TABLE Gebruikertelefoon (
	volgnr					DECIMAL(2,0)		NOT NULL,
	gebruikersnaam			VARCHAR(50)		NOT NULL,
	telefoonnummer			NUMERIC(13,0)		NOT NULL,
	CONSTRAINT PK_Gebruikertelefoon PRIMARY KEY (volgnr,gebruikersnaam),
	CONSTRAINT FK_Gebruiker_4 FOREIGN KEY (gebruikersnaam) REFERENCES Gebruiker(gebruikersnaam)
)
go

CREATE TABLE Rubriek (
	rubrieknummer			INT					NOT NULL,
	rubrieknaam				VARCHAR(50)			NOT NULL,
	subrubriekvan			INT					NULL,
	voorwerpCount		INT 				NULL,
	CONSTRAINT PK_Rubriek PRIMARY KEY (rubrieknummer),
	CONSTRAINT AK_Rubriek UNIQUE (rubrieknaam, subrubriekvan),
	CONSTRAINT FK_Rubriek_1 FOREIGN KEY (subrubriekvan) REFERENCES Rubriek(rubrieknummer)
)
go

CREATE TABLE Voorwerprubriek (
    voorwerpnummer                INT                        NOT NULL,
    rubrieknummer		          INT                        NOT NULL,
    CONSTRAINT PK_Voorwerprubriek PRIMARY KEY (voorwerpnummer, rubrieknummer),
    CONSTRAINT FK_Voorwerp_4 FOREIGN KEY (voorwerpnummer) REFERENCES Voorwerp(voorwerpnummer),
    CONSTRAINT FK_Rubriek_2 FOREIGN KEY (rubrieknummer) REFERENCES Rubriek(rubrieknummer)
)

CREATE TABLE PrecalculatedInfo (
  gebruikersHoeveelheid INT NULL,
  activeVoorwerpHoeveelheid   INT NULL,
  veilingVoltooid    INT NULL,
	aantalAfgelopen			INT NULL,
	Gemiddeldeprijsveiling	NUMERIC(10,2) NULL
)

GO
INSERT INTO PrecalculatedInfo VALUES (
  NULL,NULL,NULL,NULL,NULL
)

GO

CREATE TRIGGER CHECKVOORGEBRUIKERS ON Gebruiker
FOR INSERT,UPDATE,DELETE
AS
BEGIN
  UPDATE PrecalculatedInfo
    SET gebruikersHoeveelheid = (SELECT COUNT(gebruikersnaam) FROM Gebruiker)
END

GO

CREATE TRIGGER CHECKVOORVOORWERPEN ON Voorwerp
FOR INSERT,UPDATE,DELETE
AS
BEGIN
  UPDATE PrecalculatedInfo
    SET activeVoorwerpHoeveelheid = (SELECT COUNT(voorwerpnummer) FROM Voorwerp WHERE veiliggesloten = 0)
END

GO

CREATE TRIGGER CHECKVOORDONEVEILINGEN ON Voorwerp
FOR INSERT,UPDATE,DELETE
AS
BEGIN
  UPDATE PrecalculatedInfo
    SET veilingVoltooid = (
      SELECT COUNT(voorwerpnummer) FROM Voorwerp WHERE veiliggesloten = 1
    )
END

GO

UPDATE PrecalculatedInfo
  SET gebruikersHoeveelheid = (SELECT COUNT(gebruikersnaam) FROM Gebruiker)

UPDATE PrecalculatedInfo
  SET activeVoorwerpHoeveelheid = (SELECT COUNT(voorwerpnummer) FROM Voorwerp WHERE veiliggesloten = 0)

UPDATE PrecalculatedInfo
  SET veilingVoltooid = (
    SELECT COUNT(voorwerpnummer) FROM Voorwerp WHERE veiliggesloten = 1
  )

	UPDATE PrecalculatedInfo
		SET aantalAfgelopen = (
			SELECT COUNT(voorwerpnummer) FROM Voorwerp WHERE DATEDIFF(hour, (CAST(Voorwerp.looptijdeindedag AS DATETIME) + CAST(Voorwerp.looptijdeindetijdstip AS DATETIME)), GETDATE()) < 10 AND veiliggesloten = 0
		)

GO

CREATE TRIGGER CHECKVOORVEILINGENDONE ON Voorwerp
FOR INSERT,UPDATE,DELETE
AS
BEGIN
	IF EXISTS (
		SELECT voorwerpnummer FROM Voorwerp WHERE CURRENT_TIMESTAMP - (CAST(Voorwerp.looptijdeindedag AS DATETIME) + CAST(Voorwerp.looptijdeindetijdstip AS DATETIME)) < 10
	)
	BEGIN
		UPDATE PrecalculatedInfo
			SET aantalAfgelopen = (
				SELECT COUNT(voorwerpnummer) FROM Voorwerp WHERE DATEDIFF(DAY, CURRENT_TIMESTAMP , (CAST(Voorwerp.looptijdeindedag AS DATETIME) + CAST(Voorwerp.looptijdeindetijdstip AS DATETIME))) < 10 AND veiliggesloten = 1
			)
	END
END

GO

CREATE TABLE RegistratieCode (
	gebruikersnaam		VARCHAR(50)		NOT NULL,
	code				VARCHAR(8)			NOT NULL,

	CONSTRAINT PK_GebruikersNaam_1	PRIMARY KEY (gebruikersnaam),
	CONSTRAINT FK_GebruikersNaam_2	FOREIGN KEY (gebruikersnaam)	REFERENCES Gebruiker(gebruikersnaam)
)

CREATE TABLE VergetenWWCode (
	gebruikersnaam		VARCHAR(50)		NOT NULL,
	code				VARCHAR(8)			NOT NULL,

	CONSTRAINT PK_GebruikersNaam_2	PRIMARY KEY (gebruikersnaam),
	CONSTRAINT FK_GebruikersNaam_3	FOREIGN KEY (gebruikersnaam)	REFERENCES Gebruiker(gebruikersnaam)
)

CREATE TABLE VerBannenGebruikers (
	gebruikersnaam		VARCHAR(50) 	NOT NULL,
	VerbannenTot			DATETIME			NOT NULL,
	VerbannenDatum		DATETIME			DEFAULT GETDATE(),

	CONSTRAINT PK_GebruikersNaam_3	PRIMARY KEY (gebruikersnaam),
	CONSTRAINT FK_GebruikersNaam_4	FOREIGN KEY (gebruikersnaam)	REFERENCES Gebruiker(gebruikersnaam)
)

CREATE TABLE GedeactiveerdeGebruikers (
	gebruikersnaam		VARCHAR(50) 	NOT NULL,
	Deactivatiedatum	DATETIME			DEFAULT GETDATE(),

	CONSTRAINT PK_GebruikersNaam_4	PRIMARY KEY (gebruikersnaam),
	CONSTRAINT FK_GebruikersNaam_5	FOREIGN KEY (gebruikersnaam)	REFERENCES Gebruiker(gebruikersnaam)
)

CREATE TABLE Beheerders (
	gebruikersnaam	VARCHAR(50) NOT NULL,
	beheerdersinds	DATE DEFAULT GETDATE(),

	CONSTRAINT PK_Beheerders PRIMARY KEY (gebruikersnaam),
	CONSTRAINT FK_Gebruiker_6 FOREIGN KEY (gebruikersnaam) REFERENCES Gebruiker(gebruikersnaam)
)

GO

CREATE TRIGGER GEMIDDELDEPRIJSVOLTOOID ON Voorwerp
FOR INSERT, UPDATE
AS
BEGIN
	DECLARE @aantalvoltooid INT
	SELECT @aantalvoltooid = veilingVoltooid FROM PrecalculatedInfo
	DECLARE @totaalprijs NUMERIC(38,2)
	SELECT @totaalprijs = SUM(verkoopprijs) FROM Voorwerp WHERE veiliggesloten = 1
	DECLARE @resultaat NUMERIC(10,2)
	SET @resultaat = (@totaalprijs/@aantalvoltooid)

	UPDATE PrecalculatedInfo
	SET Gemiddeldeprijsveiling = @resultaat
END
go

CREATE TABLE FeedbackCode (
	mailbox			VARCHAR(255)	NOT NULL,
	voorwerpnummer	INT				NOT NULL,
	code			CHAR(6)			NOT NULL,
	CONSTRAINT PK_FeedbackCode PRIMARY KEY (mailbox, voorwerpnummer),
	CONSTRAINT FK_Gebruiker_7 FOREIGN KEY (mailbox) REFERENCES Gebruiker(mailbox),
	CONSTRAINT FK_Voorwerp_5 FOREIGN KEY (voorwerpnummer) REFERENCES Voorwerp(voorwerpnummer)
)
go

ALTER TABLE Gebruiker ADD CONSTRAINT CHK_Lengtegebruikersnaam CHECK (LEN(gebruikersnaam) <= 50)
ALTER TABLE Gebruiker ADD CONSTRAINT CHK_Minimumgebruiekrsnaam CHECK (LEN(gebruikersnaam) >= 3)



/*_________________________________________________*/
/*==============| Stored Procedures |==============*/
/*‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾*/

GO

CREATE PROCEDURE spVoegUserToe
  --Userinfo
   @gebruikersnaam       VARCHAR(100),
   @voornaam             VARCHAR(35),
   @achternaam           VARCHAR(35),
   @geboortedag          DATE,
   @geslacht             VARCHAR(5),
   @telefoonnummer       NUMERIC(13,0),

  --adresinfo
   @adresregel1          VARCHAR(95),
   @postcode             VARCHAR(12),
   @plaatsnaam           VARCHAR(100),
   @land                 VARCHAR(100),

  --Overige securityinfo
   @mailbox              VARCHAR(64),
   @wachtwoord           CHAR(64),
   @vraag                VARCHAR(255),
   @antwoordtekst        VARCHAR(255),
   @RegistratieCode      VARCHAR(8),

   --Optioneel
   @adresregel2          VARCHAR(95) = NULL,
   @plaatsnaam2           VARCHAR(100) = NULL,
   @postcode2             VARCHAR(12) = NULL,
   @land2                VARCHAR(100) = NULL,
   @telefoonnummer2      NUMERIC(13,0) = NULL
AS
BEGIN
  DECLARE @vraagnummer DECIMAL(3,0)
  IF EXISTS (
    SELECT vraagtekst FROM Vraag WHERE vraagtekst = @Vraag
  )
  BEGIN
    SET @vraagnummer = (SELECT vraagnummer FROM Vraag WHERE vraagtekst = @Vraag)
  END
  ELSE
  BEGIN
    SET @vraagnummer = (SELECT COUNT(vraagnummer) FROM Vraag) + 1

    INSERT INTO Vraag VALUES
    (@vraagnummer, @vraag)
  END

  IF EXISTS (
    SELECT mailbox FROM Gebruiker WHERE mailbox = @mailbox
  )
  BEGIN
    RAISERROR('Email is al geregistreerd!', 5,1)
    RETURN
  END

  INSERT INTO Gebruiker(gebruikersnaam, voornaam, achternaam, adresregel1, adresregel2, postcode, plaatsnaam, land, geboortedag, mailbox, wachtwoord, vraagnummer, antwoordtekst, registratiedatum, plaatsnaam2, postcode2, geslacht, land2) VALUES
  (@gebruikersnaam, @voornaam, @achternaam, @adresregel1, @adresregel2, @postcode, @plaatsnaam, @land, @geboortedag, @mailbox, @wachtwoord, @vraagnummer, @antwoordtekst, DEFAULT, @plaatsnaam2, @postcode2, @geslacht, @land2)

  INSERT INTO RegistratieCode VALUES (
    @gebruikersnaam, @RegistratieCode
  )

  INSERT INTO Gebruikertelefoon VALUES (
    1, @gebruikersnaam, @telefoonnummer
  )

  IF @telefoonnummer2 IS NOT NULL
  BEGIN
    INSERT INTO Gebruikertelefoon VALUES (
      2, @gebruikersnaam, @telefoonnummer2
    )
  END

END

GO

CREATE PROCEDURE spVoegCategorieToe
  --alle benodigde info
  @rubriekNaam          VARCHAR(50),

  --Optioneel
  @parentRubriekNr      INT = NULL,
  @parentRubriek        VARCHAR(50) = NULL
AS
BEGIN
  DECLARE @ParentNummer   INT
  DECLARE @RubriekNUMMER  INT
  IF @parentRubriekNr IS NULL
  BEGIN
    SET @RubriekNUMMER = (SELECT COUNT(rubrieknummer) FROM Rubriek) + 1
    IF @parentRubriek IS NOT NULL
    BEGIN
      SET @ParentNummer = (SELECT rubrieknummer FROM Rubriek WHERE rubrieknaam = @rubriekNaam)
    END
  END

  INSERT INTO Rubriek VALUES
  (@RubriekNUMMER, @rubriekNaam, @parentRubriek, NULL)
END

GO

CREATE PROCEDURE spVoegVoorwerpToe
  --Benodigde info
  @productnaam    TEXT,
  @beschrijving   TEXT,
  @startprijs     VARCHAR(12),
  @betalingswijze VARCHAR(100),
  @rubrieknummer  INT,

  @verkoper       VARCHAR(100),

  --Optioneel
  @looptijd               TINYINT = 7,
  @verzendkosten          VARCHAR(12) = NULL,
  @verzendinstructies     VARCHAR(255) = NULL,
  @betalingsinstructie    VARCHAR(255) = NULL,
  @looptijdbegintijdstip  TIME = DEFAULT,
  @begindag               DATE = DEFAULT,
  @gesloten               BIT = 0

AS
BEGIN
  DECLARE @voorwerpnummer INT

  DECLARE @plaatsnaam     VARCHAR(100)
  DECLARE @landnaam       VARCHAR(100)

  SET @plaatsnaam = (
    SELECT plaatsnaam FROM Gebruiker WHERE gebruikersnaam = @verkoper
  )

  SET @landnaam = (
    SELECT land FROM Gebruiker WHERE gebruikersnaam = @verkoper
  )

  INSERT INTO Voorwerp (titel, beschrijving, startprijs, betalingswijze, betalingsinstructie, plaatsnaam, landnaam, verkoper, veiliggesloten, looptijdbegintijdstip, looptijd, verzendkosten, verzendinstructies)
  VALUES (
    @productnaam, @beschrijving, CAST(@startprijs AS NUMERIC(10,2)), @betalingswijze, @betalingsinstructie, @plaatsnaam, @landnaam, @verkoper, @gesloten, CONVERT(time, CURRENT_TIMESTAMP), @looptijd, CAST(@verzendkosten AS NUMERIC(10,2)), @verzendinstructies
  )

  SET @voorwerpnummer = (SELECT TOP 1 voorwerpnummer FROM Voorwerp ORDER BY voorwerpnummer DESC)

  INSERT INTO Voorwerprubriek
  VALUES (
    @voorwerpnummer, @rubrieknummer
  )
END

GO

CREATE PROCEDURE spVoegBestandToe
--benodigde info
  @voorwerpnummer     INT,

--Optioneel
  @Bestand1         VARCHAR(500) = NULL,
  @Bestand2         VARCHAR(500) = NULL,
  @Bestand3         VARCHAR(500) = NULL,
  @Bestand4         VARCHAR(500) = NULL

AS
BEGIN
  IF @Bestand1 IS NOT NULL
  BEGIN
    IF NOT EXISTS (
      SELECT @Bestand1 FROM Bestand WHERE Bestand.bestandsnaam = @Bestand1
    )
    BEGIN
      INSERT INTO Bestand
      VALUES (@Bestand1, @voorwerpnummer)
    END
  END

  IF @Bestand2 IS NOT NULL
  BEGIN
    IF NOT EXISTS (
      SELECT @Bestand2 FROM Bestand WHERE Bestand.bestandsnaam = @Bestand2
    )
    BEGIN
      INSERT INTO Bestand
      VALUES (@Bestand2, @voorwerpnummer)
    END
  END

  IF @Bestand3 IS NOT NULL
  BEGIN
    IF NOT EXISTS (
      SELECT @Bestand3 FROM Bestand WHERE Bestand.bestandsnaam = @Bestand3
    )
    BEGIN
      INSERT INTO Bestand
      VALUES (@Bestand3, @voorwerpnummer)
    END
  END

  IF @Bestand4 IS NOT NULL
  BEGIN
    IF NOT EXISTS (
      SELECT @Bestand4 FROM Bestand WHERE Bestand.bestandsnaam = @Bestand4
    )
    BEGIN
      INSERT INTO Bestand
      VALUES (@Bestand4, @voorwerpnummer)
    END
  END

END

GO

CREATE PROCEDURE spNieuwBod
  @voorwerpnummer           INT,
  @bodbedrag                VARCHAR(12),
  @gebruikersnaam           VARCHAR(100),

  --Optioneel
  @boddag                   DATE = NULL,
  @bodtijdstip              TIME = NULL
AS
BEGIN
  IF @boddag IS NULL
  BEGIN
    SET @boddag = GETDATE()
  END

  IF @bodtijdstip IS NULL
  BEGIN
    SET @bodtijdstip = CONVERT(time, CURRENT_TIMESTAMP)
  END

  IF NOT EXISTS (
    SELECT voorwerpnummer FROM Bod WHERE Bod.bodbedrag > @bodbedrag AND Bod.voorwerpnummer = @voorwerpnummer
  )
  BEGIN
    INSERT INTO Bod VALUES(
      @voorwerpnummer, CAST(@bodbedrag AS NUMERIC(10,2)), @gebruikersnaam, @boddag, @bodtijdstip
    )
  END
END

GO

CREATE PROCEDURE spNieuweVerkoper
  @gebruikersnaam       VARCHAR(20),
  @controleoptie        VARCHAR(20),

  --Optioneel, rekeningnummer of creditcardnummer moet != NULL.
  @banknaam             VARCHAR(50) = NULL,
  @rekeningnummer       VARCHAR(34) = NULL,
  @creditcardnummer     DECIMAL(18,0) = NULL
AS
BEGIN
  IF NOT EXISTS (
    SELECT gebruikersnaam FROM Verkoper WHERE Verkoper.gebruikersnaam = @gebruikersnaam
  )
  BEGIN
    INSERT INTO Verkoper VALUES (
      @gebruikersnaam, @banknaam, @rekeningnummer, @controleoptie, @creditcardnummer
    )
  END
END

GO

CREATE PROCEDURE spNieuweFeedback
  @voorwerpnummer           INT,
  @gebruikersnaam			VARCHAR(100),
  @feedbacksoort			CHAR(8),
  @feedback					TEXT,

  --Automatisch bepaald
  @soortgebruiker			VARCHAR(8) = NULL,
  @feedbackdag				DATE = NULL,
  @feedbacktijd				TIME = NULL
AS
BEGIN
  IF @feedbackdag IS NULL
  BEGIN
    SET @feedbackdag = CONVERT(DATE, GETDATE())
  END

  IF @feedbacktijd IS NULL
  BEGIN
    SET @feedbacktijd = CONVERT(TIME, CURRENT_TIMESTAMP)
  END

  IF @soortgebruiker IS NULL
  BEGIN
      IF @gebruikersnaam IN (SELECT verkoper FROM Voorwerp WHERE voorwerpnummer = @voorwerpnummer)
  	BEGIN
  		SET @soortgebruiker = 'Verkoper'
  	END
  	IF @gebruikersnaam IN (SELECT koper FROM Voorwerp WHERE voorwerpnummer = @voorwerpnummer)
  	BEGIN
  		SET @soortgebruiker = 'Koper'
  	END
  END

  INSERT INTO Feedback(
    voorwerpnummer, gebruikersnaam, gebruikersoort, feedbacksoort, feedbackdag, feedbacktijd, feedback
  ) VALUES (
    @voorwerpnummer, @gebruikersnaam, @soortgebruiker, @feedbacksoort, @feedbackdag, @feedbacktijd, @Feedback
  )
END
GO

CREATE PROCEDURE spUpdateThings
AS
BEGIN
  UPDATE Voorwerp
  SET veiliggesloten = 1
  WHERE Voorwerp.looptijdeindedag < GETDATE() AND Voorwerp.looptijdeindetijdstip < CONVERT(time, CURRENT_TIMESTAMP) OR Voorwerp.looptijdeindedag < DATEADD(day, -1, GETDATE())
END

GO

CREATE PROCEDURE spUpdateAllVoorwerpen
AS
BEGIN
  UPDATE Voorwerp
  SET looptijdbegindag = GETDATE()
  WHERE looptijdeindetijdstip < CONVERT(time, CURRENT_TIMESTAMP) AND looptijdeindedag < GETDATE()

  UPDATE Voorwerp
  SET Veiliggesloten = 0
  WHERE Veiliggesloten = 1

  ALTER TABLE Voorwerp
    DROP COLUMN looptijdeindedag

  ALTER TABLE Voorwerp
    ADD looptijdeindedag		AS DATEADD(day, looptijd, looptijdbegindag)

END
GO

CREATE PROCEDURE spDeleteVeiling
  @voorwerpnummer INT
AS
BEGIN
  IF EXISTS (
    SELECT voorwerpnummer FROM Bestand WHERE voorwerpnummer = @voorwerpnummer
  )
  BEGIN
    DELETE FROM Bestand WHERE voorwerpnummer = @voorwerpnummer
  END

  IF EXISTS (
    SELECT voorwerpnummer FROM Bod WHERE voorwerpnummer = @voorwerpnummer
  )
  BEGIN
    DELETE FROM Bod WHERE voorwerpnummer = @voorwerpnummer
  END

  IF EXISTS (
    SELECT voorwerpnummer FROM Voorwerprubriek WHERE voorwerpnummer = @voorwerpnummer
  )
  BEGIN
    DELETE FROM Voorwerprubriek WHERE voorwerpnummer = @voorwerpnummer
  END

  IF EXISTS (
    SELECT voorwerpnummer FROM Voorwerp WHERE voorwerpnummer = @voorwerpnummer
  )
  BEGIN
    DELETE FROM Voorwerp WHERE voorwerpnummer = @voorwerpnummer
  END
END

GO

CREATE PROCEDURE spVoegBeheerderToe
  @gebruikersnaam     VARCHAR(100)
AS
BEGIN
  INSERT INTO Beheerders VALUES(
    @gebruikersnaam, GETDATE()
  )
END

GO

CREATE PROCEDURE spEditGebruikersNaam
  @gebruikersnaam         VARCHAR(100),
  @nieuwegebruikersnaam   VARCHAR(100)
AS
BEGIN
  UPDATE Gebruiker
  SET gebruikersnaam = @nieuwegebruikersnaam
  WHERE gebruikersnaam = @gebruikersnaam

  UPDATE Voorwerp
  SET verkoper = @nieuwegebruikersnaam
  WHERE verkoper = @gebruikersnaam

  UPDATE Voorwerp
  SET koper = @nieuwegebruikersnaam
  WHERE koper = @gebruikersnaam

  UPDATE Bod
  SET gebruikersnaam = @nieuwegebruikersnaam
  WHERE gebruikersnaam = @gebruikersnaam
END

GO

CREATE PROCEDURE spDeleteBod
  @voorwerpnummer   INT,
  @bodbedrag        NUMERIC(10,2)
AS
BEGIN
  DELETE FROM Bod
  WHERE voorwerpnummer = @voorwerpnummer AND bodbedrag = @bodbedrag
END

GO

CREATE PROCEDURE spDeleteUser
  @gebruikersnaam       VARCHAR(20)
AS
BEGIN

  IF EXISTS (
    SELECT code FROM RegistratieCode WHERE gebruikersnaam = @gebruikersnaam
  )
  BEGIN
    DELETE FROM RegistratieCode WHERE gebruikersnaam = @gebruikersnaam
  END

  DELETE FROM Gebruikertelefoon WHERE gebruikersnaam = @gebruikersnaam

  IF EXISTS (
    SELECT gebruikersnaam FROM Verkoper WHERE gebruikersnaam = @gebruikersnaam
  )
  BEGIN
    DELETE FROM Verkoper  WHERE gebruikersnaam = @gebruikersnaam
  END

  IF EXISTS (
    SELECT verkoper FROM Voorwerp WHERE verkoper = @gebruikersnaam
  )
  BEGIN
    DELETE FROM Voorwerp WHERE verkoper = @gebruikersnaam
  END

  DELETE FROM Gebruiker WHERE gebruikersnaam = @gebruikersnaam
END

/*__________________________________________________*/
/*===================| Triggers |===================*/
/*‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾*/

GO

CREATE TRIGGER CHECKVERHOGING ON Bod
FOR INSERT
AS
BEGIN
	DECLARE @verkoopprijs NUMERIC(10,2)
	DECLARE @bodbedrag NUMERIC(10,2)
	DECLARE @voorwerpnummer INT

	IF (SELECT bodbedrag FROM DELETED) != (SELECT bodbedrag FROM INSERTED)
	BEGIN

		SET @voorwerpnummer = (
			SELECT TOP 1 voorwerpnummer FROM Bod ORDER BY boddag DESC, bodtijdstip DESC
		)
		SET @verkoopprijs = (
			SELECT TOP 1 verkoopprijs FROM Voorwerp WHERE voorwerpnummer = @voorwerpnummer
		)
		SET @bodbedrag = (
			SELECT TOP 1 bodbedrag FROM Bod ORDER BY boddag DESC, bodtijdstip DESC
		)

		IF (@verkoopprijs >= 1 AND @verkoopprijs < 49.98 AND @bodbedrag < (@verkoopprijs + 0.5))
		BEGIN
			RAISERROR ('Uw bieding moet minimaal �0,50 meer zijn dat de huidige hoogste bieding.', 5, 1)
			ROLLBACK
		END
		IF (@verkoopprijs >= 49.99 AND @verkoopprijs <=500 AND @bodbedrag < (@verkoopprijs + 1))
		BEGIN
			RAISERROR ('Uw bieding moet minimaal �1,- meer zijn dat de huidige hoogste bieding.', 5, 1)
			ROLLBACK
		END
		IF (@verkoopprijs >= 500 AND @verkoopprijs <1000 AND @bodbedrag < (@verkoopprijs + 5))
		BEGIN
			RAISERROR ('Uw bieding moet minimaal �5,- meer zijn dat de huidige hoogste bieding.', 5, 1)
			ROLLBACK
		END
		IF (@verkoopprijs >= 1000 AND @verkoopprijs < 5000 AND @bodbedrag < (@verkoopprijs + 10))
		BEGIN
			RAISERROR ('Uw bieding moet minimaal �10,- meer zijn dat de huidige hoogste bieding.', 5, 1)
			ROLLBACK
		END
		IF (@verkoopprijs >= 5000 AND @bodbedrag < (@verkoopprijs + 50))
		BEGIN
			RAISERROR ('Uw bieding moet minimaal �1,- meer zijn dat de huidige hoogste bieding.', 5, 1)
			ROLLBACK
		END
	END
END

GO

CREATE TRIGGER CHECKFORWINNER ON Bod
FOR UPDATE, DELETE
AS
BEGIN
  UPDATE Voorwerp
    SET koper = (
      SELECT TOP 1 gebruikersnaam FROM Bod WHERE Bod.voorwerpnummer = Voorwerp.voorwerpnummer ORDER BY bodbedrag DESC
    )
END

GO

CREATE TRIGGER CHECKFORTIMEROVER ON Bod
FOR INSERT
AS
BEGIN
  IF EXISTS ((SELECT voorwerpnummer FROM Voorwerp WHERE (Voorwerp.looptijdeindedag < GETDATE() AND Voorwerp.looptijdeindetijdstip < CONVERT(time, CURRENT_TIMESTAMP) OR Voorwerp.looptijdeindedag < DATEADD(day, -1, GETDATE())) AND voorwerpnummer = (
    SELECT TOP 1 voorwerpnummer FROM Bod ORDER BY boddag DESC, bodtijdstip DESC
  )))
  BEGIN
    RAISERROR ('KAN NIET BIEDEN OP AFGELOPEN VEILING!', 5, 1)
    ROLLBACK
  END
END

GO

CREATE TRIGGER GETVOORWERPNUMMERRUBRIEK ON Voorwerprubriek
FOR INSERT,DELETE
AS
BEGIN
  UPDATE Rubriek
  SET voorwerpCount = (
    SELECT COUNT(voorwerpnummer) FROM Voorwerprubriek WHERE rubrieknummer = Rubriek.rubrieknummer
  )
END

GO

CREATE TRIGGER CHECKFORCREDITORBANK ON Verkoper
FOR INSERT,UPDATE
AS
BEGIN
	IF EXISTS (
    SELECT rekeningnummer FROM Verkoper WHERE rekeningnummer IS NULL AND creditcardnummer IS NULL)
	  BEGIN
		  RAISERROR ('Rekening of creditcardnummer must contain data!',5,1)
		  ROLLBACK
	  END
END


GO

CREATE TRIGGER CHECKFORLOOPTIJDEINDETIJDSTIP ON Voorwerp
AFTER INSERT,UPDATE
AS
BEGIN
  IF EXISTS (
    SELECT looptijdeindetijdstip FROM Voorwerp WHERE looptijdeindetijdstip = looptijdbegintijdstip
  )
  BEGIN
    UPDATE Voorwerp
    SET looptijdeindetijdstip = looptijdbegintijdstip
  END
END

GO

CREATE TRIGGER CHECKFORBODINTEGRITEIT ON Bod
FOR INSERT,UPDATE
AS
BEGIN
  IF EXISTS (
    SELECT gebruikersnaam, voorwerpnummer, bodbedrag FROM Bod WHERE voorwerpnummer IN (SELECT Bod.voorwerpnummer FROM Voorwerp, Bod WHERE Bod.gebruikersnaam = Voorwerp.verkoper AND Voorwerp.voorwerpnummer = Bod.voorwerpnummer)
  )
  BEGIN
    RAISERROR ('Cannot bid on your own Auction', 5,1)
    ROLLBACK
  END
END

GO

CREATE TRIGGER UPDATEVERKOOPPRIJS ON Bod
FOR INSERT,UPDATE,DELETE
AS
BEGIN
  UPDATE Voorwerp
    SET verkoopprijs = (
      SELECT MAX(bodbedrag) FROM Bod WHERE Bod.voorwerpnummer = Voorwerp.voorwerpnummer
    )

  UPDATE Voorwerp
  SET verkoopprijs = startprijs
  WHERE verkoopprijs IS NULL AND veiliggesloten = 1
END

GO

CREATE TRIGGER UPDATESTARTPRIJS ON Voorwerp
FOR INSERT
AS
BEGIN
  UPDATE Voorwerp
    SET verkoopprijs = startprijs
    WHERE verkoopprijs IS NULL

END

/*__________________________________________________*/
/*===================| Cascades |===================*/
/*‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾*/

ALTER TABLE Beheerders
drop CONSTRAINT FK_Gebruiker_6;

ALTER TABLE Beheerders
ADD CONSTRAINT FK_Gebruiker_6
    FOREIGN KEY (gebruikersnaam)
    REFERENCES Gebruiker(gebruikersnaam)
    ON DELETE CASCADE ON UPDATE CASCADE;


ALTER TABLE Verkoper
drop CONSTRAINT FK_Gebruiker_3;

ALTER TABLE Verkoper
ADD CONSTRAINT FK_Gebruiker_3
    FOREIGN KEY (gebruikersnaam)
    REFERENCES Gebruiker(gebruikersnaam)
    ON DELETE CASCADE ON UPDATE CASCADE;



ALTER TABLE Bod
drop CONSTRAINT FK_Gebruiker_5;

ALTER TABLE Bod
ADD CONSTRAINT FK_Gebruiker_5
    FOREIGN KEY (gebruikersnaam)
    REFERENCES Gebruiker(gebruikersnaam)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE Gebruikertelefoon
drop CONSTRAINT FK_Gebruiker_4;

ALTER TABLE Gebruikertelefoon
ADD CONSTRAINT FK_Gebruiker_4
    FOREIGN KEY (gebruikersnaam)
    REFERENCES Gebruiker(gebruikersnaam)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE Verkoper
drop CONSTRAINT FK_Gebruiker_3;

ALTER TABLE Verkoper
ADD CONSTRAINT FK_Gebruiker_3
    FOREIGN KEY (gebruikersnaam)
    REFERENCES Gebruiker(gebruikersnaam)

ALTER TABLE FeedbackCode
drop CONSTRAINT FK_Voorwerp_5;

ALTER TABLE FeedbackCode
ADD CONSTRAINT FK_Voorwerp_5
    FOREIGN KEY (voorwerpnummer)
    REFERENCES Voorwerp(voorwerpnummer)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE FeedbackCode
drop CONSTRAINT FK_Gebruiker_7;

ALTER TABLE FeedbackCode
ADD CONSTRAINT FK_Gebruiker_7
    FOREIGN KEY (mailbox)
    REFERENCES Gebruiker(mailbox)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE Feedback
DROP CONSTRAINT FK_Gebruiker_8

ALTER TABLE Feedback
ADD CONSTRAINT FK_Gebruiker_8
	FOREIGN KEY (gebruikersnaam)
	REFERENCES Gebruiker(gebruikersnaam)
	ON DELETE CASCADE ON UPDATE CASCADE;


GO

CREATE FUNCTION dbo.Biedenopeigenveiling (@gebruikersnaam VARCHAR(100), @voorwerpnummer INT)
RETURNS BIT
AS
BEGIN
	IF NOT EXISTS	(SELECT	voorwerpnummer, verkoper
					FROM	Voorwerp
					WHERE	voorwerpnummer = @voorwerpnummer
					AND		verkoper = @gebruikersnaam)
					RETURN 1
	RETURN 0
END
go

ALTER TABLE Bod ADD CONSTRAINT CHK_Biedenopeigenveiling CHECK (dbo.Biedenopeigenveiling(gebruikersnaam, voorwerpnummer) = 1)


/*_________________________________________________*/
/*==========| Meegeleverd adminaccount |===========*/
/*‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾*/

/*=================| Login Creds |==================/
Gebruikersnaam: TestAdmin
Email: TestAdmin@han.nl
Wachtwoord: Wachtwoord123
/==================================================*/

EXEC spVoegUserToe 'TestAdmin', 'Admin', 'Admin', '1990-01-01', 'Vrouw', 0612345678, 'Adminstraat', '1337HX', 'Straat', 'Land', 'TestAdmin@Admin.Admin', '88232fd34468c0bede2f46e03c3a5754010552b474e05a3b6f1ec72d4f1ab1aa', 'Wie is je favoriete pokemon?', 'Pikachu', '12345678'

DELETE FROM RegistratieCode WHERE code = '12345678'

EXEC spVoegBeheerderToe 'TestAdmin'