USE [master]
GO
IF db_id('TPBD2e1147782') is not null DROP DATABASE [TPBD2e1147782]
GO

CREATE DATABASE [TPBD2e1147782]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'TPBD2e1147782', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\TPBD2e1147782.mdf' , SIZE = 5120KB , FILEGROWTH = 10%)
 LOG ON
( NAME = N'TPBD2e1147782_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\TPBD2e1147782_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [TPBD2e1147782] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TPBD2e1147782] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TPBD2e1147782] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TPBD2e1147782] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TPBD2e1147782] SET ARITHABORT OFF
GO
ALTER DATABASE [TPBD2e1147782] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [TPBD2e1147782] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TPBD2e1147782] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
GO
ALTER DATABASE [TPBD2e1147782] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TPBD2e1147782] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TPBD2e1147782] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [TPBD2e1147782] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TPBD2e1147782] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TPBD2e1147782] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TPBD2e1147782] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TPBD2e1147782] SET  DISABLE_BROKER
GO
ALTER DATABASE [TPBD2e1147782] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TPBD2e1147782] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TPBD2e1147782] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TPBD2e1147782] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [TPBD2e1147782] SET  READ_WRITE
GO
ALTER DATABASE [TPBD2e1147782] SET RECOVERY SIMPLE
GO
ALTER DATABASE [TPBD2e1147782] SET  MULTI_USER
GO
ALTER DATABASE [TPBD2e1147782] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [TPBD2e1147782] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
ALTER DATABASE [TPBD2e1147782] SET DELAYED_DURABILITY = DISABLED
GO
USE [TPBD2e1147782]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [TPBD2e1147782] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO


IF OBJECT_ID('dbo.Inventaire') IS NOT NULL DROP TABLE dbo.Inventaire;
GO
IF OBJECT_ID('dbo.Character_Class') IS NOT NULL DROP TABLE dbo.Character_Class;
GO
IF OBJECT_ID('dbo.Class') IS NOT NULL DROP TABLE dbo.Class;
GO
IF OBJECT_ID('dbo.Character') IS NOT NULL DROP TABLE dbo.Character;
GO
IF OBJECT_ID('dbo.EmailAddress') IS NOT NULL DROP TABLE dbo.EmailAddress;
GO
IF OBJECT_ID('dbo.Users') IS NOT NULL DROP TABLE dbo.Users;
GO

USE [TPBD2e1147782]
GO

CREATE TABLE dbo.Users (
UserID          INT NOT NULL IDENTITY(1, 1)
    CONSTRAINT PK_Users_UserID PRIMARY KEY,
Name            nvarchar(255) NOT NULL,
Billing_Address nvarchar(255) NOT NULL,
Country 		nvarchar(255) NOT NULL
)
GO

CREATE TABLE dbo.Character (
CharacterID     INT NOT NULL IDENTITY(1, 1),
UserID INT NOT NULL
	CONSTRAINT FK_Character_UserID FOREIGN KEY (UserID)
	REFERENCES dbo.Users (UserID)
	ON DELETE CASCADE
    ON UPDATE CASCADE,
CharacterName   nvarchar(255) NOT NULL,
CharacterLevel  integer NOT NULL,
CharacterRace   nvarchar(255) NOT NULL
	CONSTRAINT PK_Character PRIMARY KEY (UserID, CharacterID)
)
GO

CREATE TABLE dbo.EmailAddress (
EmailAddressID  INT NOT NULL IDENTITY(1, 1),
UserID INT NOT NULL
    CONSTRAINT FK_EmailAddress_UserID FOREIGN KEY (UserID)
    REFERENCES dbo.Users (UserID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
EmailAddress    nvarchar(255) NOT NULL,
    CONSTRAINT PK_EmailAddress PRIMARY KEY (UserID, EmailAddressID)
)
GO

CREATE TABLE dbo.Class (
ClassID     INT NOT NULL IDENTITY(1, 1)
    CONSTRAINT PK_Class_ClassID PRIMARY KEY,
Name        nvarchar(255) NOT NULL,
)
GO

CREATE TABLE dbo.Character_Class (
CharacterCharacterID    INT NOT NULL
    CONSTRAINT FK_Character_Class_CharacterCharacterID FOREIGN KEY (CharacterCharacterID)
    REFERENCES dbo.Character (CharacterID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
ClassClassID            INT NOT NULL
    CONSTRAINT FK_Character_Class_ClassClassID FOREIGN KEY (ClassClassID)
    REFERENCES dbo.Class (ClassID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT PK_Character_Class PRIMARY KEY (CharacterCharacterID, ClassClassID)
)
GO

CREATE TABLE dbo.Inventaire (
ItemID              	INT NOT NULL IDENTITY(1, 1),
CharacterCharacterID    INT NOT NULL
    CONSTRAINT FK_Inventory_CharacterCharacterID FOREIGN KEY (CharacterCharacterID)
    REFERENCES dbo.Character (CharacterID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
ItemName            nvarchar(255) NOT NULL,
ItemValue           integer NOT NULL,
    CONSTRAINT PK_Inventory PRIMARY KEY (CharacterCharacterID, ItemID)
)
GO

-----------------------
-- PARTIE INSERTION
-----------------------

DELETE FROM dbo.Users
WHERE 1 = 1
GO

DELETE FROM dbo.Character
WHERE 1 = 1
GO

DELETE FROM dbo.Class
WHERE 1 = 1
GO

DELETE FROM dbo.EmailAddress
WHERE 1 = 1
GO

DELETE FROM dbo.Inventaire
WHERE 1 = 1
GO

DELETE FROM dbo.Character_Class
WHERE 1 = 1
GO

-- Déclaration des variables
DECLARE @ID_BenDes as INT;
DECLARE @ID_PhiGui as INT;
DECLARE @ID_ÉtiBou as INT;
DECLARE @ID_JosBoi as INT;
DECLARE @ID_MikTur as INT;

DECLARE @ID_Goth as INT;
DECLARE @ID_Arag as INT;
DECLARE @ID_Giml as INT;
DECLARE @ID_Necr as INT;
DECLARE @ID_Jova as INT;
DECLARE @ID_Kash as INT;

INSERT INTO dbo.Users (Name, Billing_Address, Country) VALUES
('Benoit Desrosiers', '54 Rue des Abeilles', 'Canada'),
('Philippe Guilmette', '185 Rue du Slav', 'Russie'),
('Étienne Boutet', '23 Rue de la Baguette', 'Niger'),
('Joseph Boisvert', '99 Boulevard Allard', 'Pakistan'),
('Mike Turcotte', '550 Broadway Avenue', 'États-Unis' )

SET @ID_BenDes = (SELECT UserID FROM dbo.Users WHERE Name = 'Benoit Desrosiers')
SET @ID_PhiGui = (SELECT UserID FROM dbo.Users WHERE Name = 'Philippe Guilmette')
SET @ID_ÉtiBou = (SELECT UserID FROM dbo.Users WHERE Name = 'Étienne Boutet')
SET @ID_JosBoi = (SELECT UserID FROM dbo.Users WHERE Name = 'Joseph Boisvert')
SET @ID_MikTur = (SELECT UserID FROM dbo.Users WHERE Name = 'Mike Turcotte')

INSERT INTO dbo.Character (CharacterName, CharacterLevel, CharacterRace) VALUES
('Gothtarte', 60, 'Dark Elf'),
('Aragorn', 52, 'Human'),
('Gimli', 30, 'Dwarf'),
('Necromanco', 22, 'High Elf'),
('Jova', 5, 'Human'),
('Kashir', 17, 'Khajit')

SET @ID_Goth = (SELECT CharacterID from dbo.Character WHERE CharacterName = 'Gothtarte')
SET @ID_Arag = (SELECT CharacterID from dbo.Character WHERE CharacterName = 'Aragorn')
SET @ID_Giml = (SELECT CharacterID from dbo.Character WHERE CharacterName = 'Gimli')
SET @ID_Necr = (SELECT CharacterID from dbo.Character WHERE CharacterName = 'Necromanco')
SET @ID_Jova = (SELECT CharacterID from dbo.Character WHERE CharacterName = 'Jova')
SET @ID_Kash = (SELECT CharacterID from dbo.Character WHERE CharacterName = 'Kashir')

INSERT INTO dbo.Class (Name) VALUES
('Warrior'),
('Mage'),
('Necromancer'),
('Archer'),
('Thief'),
('Paladin'),
('Monk'),
('Cleric')

INSERT INTO dbo.EmailAddress (UserID, EmailAddress) VALUES
(@ID_BenDes, 'ben1@hotmail.com'),
(@ID_BenDes, 'ben2@gmail.com'),
(@ID_PhiGui, 'phil1@hotmail.com'),
(@ID_PhiGui, 'phil2@gmail.com'),
(@ID_ÉtiBou, 'et1@hotmail.com'),
(@ID_ÉtiBou, 'et2@gmail.com'),
(@ID_JosBoi, 'jos1@hotmail.com'),
(@ID_JosBoi, 'jos2@gmail.com'),
(@ID_MikTur, 'mike1@hotmail.com'),
(@ID_MikTur, 'mike2@gmail.com')

INSERT INTO dbo.Inventaire (CharacterCharacterID, ItemName, ItemValue) VALUES
(@ID_Goth, 'Spade', 5),
(@ID_Goth, 'Iron Sword', 50),
(@ID_Arag, 'Lobster', 15),
(@ID_Arag, 'Mithril Mace', 75),
(@ID_Giml, 'Rune Helm', 250),
(@ID_Giml, 'Magic Staff', 100),
(@ID_Necr, 'Spade', 5),
(@ID_Necr, 'Bow', 20),
(@ID_Jova, 'Magic Staff', 100),
(@ID_Kash, 'Steel Sword', 80)

DECLARE @ID_Warrior as INT;
DECLARE @ID_Mage as INT;
DECLARE @ID_Necromancer as INT;
DECLARE @ID_Archer as INT;
DECLARE @ID_Thief as INT;
DECLARE @ID_Paladin as INT;
DECLARE @ID_Monk as INT;
DECLARE @ID_Cleric as INT;

SET @ID_Warrior = (SELECT ClassID from dbo.Class WHERE Name = 'Warrior')
SET @ID_Mage = (SELECT ClassID from dbo.Class WHERE Name = 'Mage')
SET @ID_Necromancer = (SELECT ClassID from dbo.Class WHERE Name = 'Necromancer')
SET @ID_Archer = (SELECT ClassID from dbo.Class WHERE Name = 'Archer')
SET @ID_Thief = (SELECT ClassID from dbo.Class WHERE Name = 'Thief')
SET @ID_Paladin = (SELECT ClassID from dbo.Class WHERE Name = 'Paladin')
SET @ID_Monk = (SELECT ClassID from dbo.Class WHERE Name = 'Monk')
SET @ID_Cleric = (SELECT ClassID from dbo.Class WHERE Name = 'Cleric')

INSERT INTO dbo.Character_Class (CharacterCharacterID, ClassClassID) VALUES
(@ID_Goth, @ID_Paladin),
(@ID_Goth, @ID_Mage),
(@ID_Arag, @ID_Mage),
(@ID_Arag, @ID_Thief),
(@ID_Giml, @ID_Warrior),
(@ID_Giml, @ID_Necromancer),
(@ID_Necr, @ID_Monk),
(@ID_Jova, @ID_Mage),
(@ID_Jova, @ID_Cleric),
(@ID_Kash, @ID_Archer)
GO