/*Note, you can either use our phpmyadmin interface located at
https://lampbusters.com/bob    choose your database (Musername)
And copy and paste this file starting at "use Musername" into the SQL
window, OR you can first upload
this file to your home directory (not public_html, the one above it), then
log into lampbusters.com  using putty, choose the menu for mysql and log in
then type /. mysqltest.sql   to load up this file */

use Musername;
/*replace the username part with YOUR username, but leave the M */

create table auction_items(
item_id int PRIMARY KEY AUTO_INCREMENT,
customer_id int,
item varchar(25),
description text,
minbid float,
category enum('book', 'CD', 'DVD', 'VHS', 'computer', 'UPS'),
/* enums have many problems, this category field should be a foreign key to a 
    category table to do it right */
starttime datetime,
endtime datetime
) engine = InnoDB;

create table auction_bids(
bid_id int PRIMARY KEY AUTO_INCREMENT,
item_id int,
FOREIGN KEY (item_id) REFERENCES auction_items (item_id) ON DELETE CASCADE,
bidtime TIMESTAMP NOT NULL,
bidamount DECIMAL(10,2),
customer_id int,
wonbid char(3)
) engine = InnoDB;

create table customers(
customer_id int PRIMARY KEY AUTO_INCREMENT,
firstname varchar(25),
lastname varchar(25),
street varchar(40),
city varchar (25),
state char(2),
zipcode char(5),
numbadcomments int,
numgoodcomments int
) engine = InnoDB;


insert into auction_items values (NULL, 2, 'MySQL and PHP Programming',
'great book, easy read, in excellent condition, first owner', 13.95, 'book',
NOW(), '2009-03-12 23:59:59'),
(NULL, 2, 'Lord of the Rings DVDs', 'All three movies, extended versions, tons
of extras', 20.00, 'DVD', NULL, DATE_ADD(NOW(), INTERVAL 5 DAY));

insert into customers values (NULL, 'Phillip', 'Waclawski', '123 Nowhere st',
'Mesa', 'AZ', '85202', 0, 0),
(NULL, 'Ghenghis', 'Kahn', 'Your City Soon', 'Mongolia', 'AZ', '85343', 3412,
212),
(NULL, 'Martha', 'Stewart', 'Let Me Out of Here Ave', 'New York', 'NY', '90210',
50, 50);

insert into auction_bids values (NULL, 1, NOW(), 15.03, 1, 'no'),
(NULL, 1, NOW(), 16.00, 2, 'no'),
(NULL, 1, NOW(), 18.00, 3, 'no'), 
(NULL, 2, NOW(), 27.00, 1, 'yes');