SET QUOTED_IDENTIFIER OFF;
GO
USE [Dota];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Game_Hero]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Games] DROP CONSTRAINT [FK_Game_Hero];
GO
IF OBJECT_ID(N'[dbo].[FK_GameRoleType]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Games] DROP CONSTRAINT [FK_GameRoleType];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Games]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Games];
GO
IF OBJECT_ID(N'[dbo].[Heroes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Heroes];
GO
IF OBJECT_ID(N'[dbo].[RoleTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[RoleTypes];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Games'
CREATE TABLE [dbo].[Games] (
    [ID] int  NOT NULL,
    [HeroID] int  NULL,
    [Date] datetime  NULL,
    [Won] tinyint  NULL,
    [RoleID] nvarchar(max)  NOT NULL,
    [RoleType_Id] int  NOT NULL
);
GO

-- Creating table 'Heroes'
CREATE TABLE [dbo].[Heroes] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] varchar(50)  NULL,
    [Type] varchar(50)  NULL
);
GO

-- Creating table 'RoleTypes'
CREATE TABLE [dbo].[RoleTypes] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ID] in table 'Games'
ALTER TABLE [dbo].[Games]
ADD CONSTRAINT [PK_Games]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Heroes'
ALTER TABLE [dbo].[Heroes]
ADD CONSTRAINT [PK_Heroes]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [Id] in table 'RoleTypes'
ALTER TABLE [dbo].[RoleTypes]
ADD CONSTRAINT [PK_RoleTypes]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [HeroID] in table 'Games'
ALTER TABLE [dbo].[Games]
ADD CONSTRAINT [FK_Game_Hero]
    FOREIGN KEY ([HeroID])
    REFERENCES [dbo].[Heroes]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_Game_Hero'
CREATE INDEX [IX_FK_Game_Hero]
ON [dbo].[Games]
    ([HeroID]);
GO

-- Creating foreign key on [RoleType_Id] in table 'Games'
ALTER TABLE [dbo].[Games]
ADD CONSTRAINT [FK_GameRoleType]
    FOREIGN KEY ([RoleType_Id])
    REFERENCES [dbo].[RoleTypes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_GameRoleType'
CREATE INDEX [IX_FK_GameRoleType]
ON [dbo].[Games]
    ([RoleType_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------