-- Copyright (C) 2001 YesSoftware. All rights reserved.
-- BugTrack_MSSQL.sql

if exists (select * from sysobjects where id = object_id(N'card_types') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table card_types
GO
if exists (select * from sysobjects where id = object_id(N'categories') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table categories
GO
if exists (select * from sysobjects where id = object_id(N'editorial_categories') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table editorial_categories
GO
if exists (select * from sysobjects where id = object_id(N'editorials') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table editorials
GO
if exists (select * from sysobjects where id = object_id(N'items') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table items
GO
if exists (select * from sysobjects where id = object_id(N'members') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table members
GO
if exists (select * from sysobjects where id = object_id(N'orders') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table orders
GO

CREATE TABLE card_types (
       card_type_id         int IDENTITY primary key,
       name                 varchar(50) NOT NULL
)
GO

insert into card_types (name) values ('Visa')
GO
insert into card_types (name) values ('American Express')
GO

CREATE TABLE categories (
       category_id          int  IDENTITY primary key,
       name                 varchar(50) NOT NULL
)
GO

insert into categories (name) values ('Programming')
GO
insert into categories (name) values ('Databases')
GO
insert into categories (name) values ('HTML & Web design')
GO

CREATE TABLE editorial_categories (
       editorial_cat_id     int  IDENTITY primary key,
       editorial_cat_name   varchar(50) NULL
)
GO

insert into editorial_categories (editorial_cat_name) values ('What')
GO
insert into editorial_categories (editorial_cat_name) values ('New')
GO
insert into editorial_categories (editorial_cat_name) values ('Weekly')
GO
insert into editorial_categories (editorial_cat_name) values ('General')
GO

CREATE TABLE editorials (
       article_id           int IDENTITY primary key,
       editorial_cat_id     int NULL DEFAULT 0,
       article_title        varchar(200) NULL,
       article_desc         text NULL,
       item_id              int NULL DEFAULT 0
)
GO

insert into editorials (editorial_cat_id, article_title, article_desc, item_id)
	values(1, 'A Sharp Combination', 'To get inside C#, Microsoft''s new OO programming language, use A Preview of C# as a guide. It offers a preview of Visual Studio.NET and an overview of the .NET framework, and demonstrates how C# is integrated with ASP+, ADO+, and COM+ in .NET applications. You''ll get examples of C# in action, too.', 22)
GO
insert into editorials (editorial_cat_id, article_title, article_desc, item_id)
	values(2, '1001 Web Site Construction Tips and Tricks', '39.95', 21)
GO
insert into editorials (editorial_cat_id, article_title, article_desc, item_id)
	values(3, 'Flash 4 Magic', 'If you''re right in the middle of learning (or just jumping into) Flash to create and manipulate animations, music tracks, sound effects, and interface design, try the Flash 4 Magic. Inside are tutorials, graphic presentations, and a CD.', 8)
GO
insert into editorials (editorial_cat_id, article_title, article_desc, item_id)
	values(4, '<b><font color="brown">Free Shipping on orders over $40</font></b>', 'For limited time only, until next Sunday, you can enjoy free shipping. Simply order more then $40 worth of books and shipping''s on us.', 0)
GO

CREATE TABLE items (
       item_id              int IDENTITY primary key,
       category_id          int NOT NULL,
       name                 varchar(255) NOT NULL,
       author               varchar(100) NULL,
       price                numeric(10,2) NOT NULL,
       product_url          varchar(255) NULL,
       image_url            varchar(100) NULL,
       notes                text NULL,
       is_recommended       tinyint NULL DEFAULT 0,
       rating								int default 0,
       rating_count					int default 0
)