-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 04/02/2010 21:08:07
-- Generated from EDMX file: C:\Users\lukas\Documents\Visual Studio 2010\Projects\TelecoSystems\TelecoSystems.Web\Models\TelecoSystems.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [TelecoSystems];
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_ContractIncident]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[IncidentSet] DROP CONSTRAINT [FK_ContractIncident];
GO
IF OBJECT_ID(N'[dbo].[FK_CustomerCompany_inherits_Customer]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CustomerSet_CustomerCompany] DROP CONSTRAINT [FK_CustomerCompany_inherits_Customer];
GO
IF OBJECT_ID(N'[dbo].[FK_CustomerContract]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ContractSet] DROP CONSTRAINT [FK_CustomerContract];
GO
IF OBJECT_ID(N'[dbo].[FK_CustomerPerson_inherits_Customer]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CustomerSet_CustomerPerson] DROP CONSTRAINT [FK_CustomerPerson_inherits_Customer];
GO
IF OBJECT_ID(N'[dbo].[FK_EditionTargetGroup]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[VariantSet] DROP CONSTRAINT [FK_EditionTargetGroup];
GO
IF OBJECT_ID(N'[dbo].[FK_ProductVariant]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[VariantSet] DROP CONSTRAINT [FK_ProductVariant];
GO
IF OBJECT_ID(N'[dbo].[FK_VariantContract]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ContractSet] DROP CONSTRAINT [FK_VariantContract];
GO

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

IF OBJECT_ID(N'[dbo].[ContractSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ContractSet];
GO
IF OBJECT_ID(N'[dbo].[CustomerSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CustomerSet];
GO
IF OBJECT_ID(N'[dbo].[CustomerSet_CustomerCompany]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CustomerSet_CustomerCompany];
GO
IF OBJECT_ID(N'[dbo].[CustomerSet_CustomerPerson]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CustomerSet_CustomerPerson];
GO
IF OBJECT_ID(N'[dbo].[IncidentSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[IncidentSet];
GO
IF OBJECT_ID(N'[dbo].[ProductSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProductSet];
GO
IF OBJECT_ID(N'[dbo].[TargetGroupSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TargetGroupSet];
GO
IF OBJECT_ID(N'[dbo].[VariantSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[VariantSet];
GO

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

-- Creating table 'Products'
CREATE TABLE [dbo].[Products] (
    [Id] int  NOT NULL,
    [Name] nvarchar(80)  NOT NULL,
    [Description] nvarchar(256)  NULL,
    [Image] varbinary(max)  NULL,
    [CreatorID] uniqueidentifier  NOT NULL,
    [DateCreated] datetime  NOT NULL,
    [DateLastModified] datetime  NOT NULL
);
GO

-- Creating table 'Variants'
CREATE TABLE [dbo].[Variants] (
    [Id] int  NOT NULL,
    [ProductID] int  NOT NULL,
    [Name] nvarchar(80)  NOT NULL,
    [Description] nvarchar(256)  NULL,
    [Price] float  NOT NULL,
    [DateCreated] datetime  NOT NULL,
    [DateLastModified] datetime  NOT NULL,
    [TargetGroupId] int  NOT NULL,
    [IsInSale] bit  NOT NULL
);
GO

-- Creating table 'Contracts'
CREATE TABLE [dbo].[Contracts] (
    [Id] int  NOT NULL,
    [DealerID] uniqueidentifier  NOT NULL,
    [EditionID] int  NOT NULL,
    [DateCreated] datetime  NOT NULL,
    [DateLastModified] datetime  NOT NULL,
    [CustomerID] int  NOT NULL
);
GO

-- Creating table 'Customers'
CREATE TABLE [dbo].[Customers] (
    [Id] int  NOT NULL,
    [DateCreated] datetime  NOT NULL,
    [DateLastModified] datetime  NOT NULL,
    [Email] nvarchar(max)  NULL,
    [Phone] nvarchar(max)  NOT NULL,
    [Address_City] nvarchar(max)  NOT NULL,
    [Address_PostalCode] nvarchar(max)  NOT NULL,
    [Address_Street] nvarchar(max)  NOT NULL,
    [Address_Country] nvarchar(max)  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Note] nvarchar(max)  NULL
);
GO

-- Creating table 'Incidents'
CREATE TABLE [dbo].[Incidents] (
    [Id] int  NOT NULL,
    [ContractID] int  NOT NULL,
    [OfficerID] uniqueidentifier  NOT NULL,
    [Priority] smallint  NOT NULL,
    [IsSolved] bit  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [DateCreated] datetime  NOT NULL,
    [DateLastModified] datetime  NOT NULL
);
GO

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

-- Creating table 'Customers_CustomerCompany'
CREATE TABLE [dbo].[Customers_CustomerCompany] (
    [IDNumber] nvarchar(max)  NOT NULL,
    [ContactPerson_FirstName] nvarchar(max)  NOT NULL,
    [ContactPerson_LastName] nvarchar(max)  NOT NULL,
    [ContactPerson_Phone] nvarchar(max)  NOT NULL,
    [Id] int  NOT NULL
);
GO

-- Creating table 'Customers_CustomerPerson'
CREATE TABLE [dbo].[Customers_CustomerPerson] (
    [FirstName] nvarchar(max)  NOT NULL,
    [LastName] nvarchar(max)  NOT NULL,
    [BirthDate] datetime  NULL,
    [Id] int  NOT NULL
);
GO

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

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

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

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

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

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

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

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

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

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

-- Creating foreign key on [ProductID] in table 'Variants'
ALTER TABLE [dbo].[Variants]
ADD CONSTRAINT [FK_ProductVariant]
    FOREIGN KEY ([ProductID])
    REFERENCES [dbo].[Products]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProductVariant'
CREATE INDEX [IX_FK_ProductVariant]
ON [dbo].[Variants]
    ([ProductID]);
GO

-- Creating foreign key on [EditionID] in table 'Contracts'
ALTER TABLE [dbo].[Contracts]
ADD CONSTRAINT [FK_VariantContract]
    FOREIGN KEY ([EditionID])
    REFERENCES [dbo].[Variants]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_VariantContract'
CREATE INDEX [IX_FK_VariantContract]
ON [dbo].[Contracts]
    ([EditionID]);
GO

-- Creating foreign key on [CustomerID] in table 'Contracts'
ALTER TABLE [dbo].[Contracts]
ADD CONSTRAINT [FK_CustomerContract]
    FOREIGN KEY ([CustomerID])
    REFERENCES [dbo].[Customers]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CustomerContract'
CREATE INDEX [IX_FK_CustomerContract]
ON [dbo].[Contracts]
    ([CustomerID]);
GO

-- Creating foreign key on [ContractID] in table 'Incidents'
ALTER TABLE [dbo].[Incidents]
ADD CONSTRAINT [FK_ContractIncident]
    FOREIGN KEY ([ContractID])
    REFERENCES [dbo].[Contracts]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ContractIncident'
CREATE INDEX [IX_FK_ContractIncident]
ON [dbo].[Incidents]
    ([ContractID]);
GO

-- Creating foreign key on [TargetGroupId] in table 'Variants'
ALTER TABLE [dbo].[Variants]
ADD CONSTRAINT [FK_EditionTargetGroup]
    FOREIGN KEY ([TargetGroupId])
    REFERENCES [dbo].[TargetGroups]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EditionTargetGroup'
CREATE INDEX [IX_FK_EditionTargetGroup]
ON [dbo].[Variants]
    ([TargetGroupId]);
GO

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

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

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