/* assign search path to specified auction name */
set search_path to yourschemanamegoeshere,public;

/*
drop table bids;
drop table lot_detail;
drop table lot;
drop table person;
*/

create table person (
        person_id       serial,
        last_name       varchar(30) not null, 
        first_name      varchar(30), 
        home_phone      varchar(30), 
        cell_phone      varchar(30), 
        email           varchar(100),
        addr            varchar(50),
        city            varchar(30),
        state           varchar(2),
        zip             varchar(10),
        primary key (person_id)
);

create table lot (
        lot_id          serial,
        person_id       int, /* id of donor */
        estimated_value numeric(10,2), /* 10 digits + 2 decimal places */
        minimum_bid     numeric(10,2),
        bid_increment   numeric(5,2),
        foreign key (person_id) references person(person_id),
        primary key (lot_id)
);

create table lot_detail (
        lot_id          int not null,
        description     varchar(50) not null, 
        detail          varchar(1000),
        foreign key (lot_id) references lot(lot_id)
);

create table bids (
        bid_id          serial,
        person_id       int not null,  /* id of bidder */
        lot_id          int not null,  /* item that was bid on */
        time            timestamp,
        foreign key (lot_id) references lot(lot_id),
        foreign key (person_id) references person(person_id),
        primary key (bid_id)
);


insert into person values(default,'Smith','John','123-12-1234','','jsmith@gmail','123 Pine','Abilene','TX','79605');
insert into person values(default,'Jones','Martha','234-24-2456','','mjones@gmail','234 Oak','Dallas','TX','78600');
insert into person values(default,'Williams','Amy','345-56-5678','','awilliams@gmail','345 Beech','Abilene','TX','79603');

insert into lot values(default,1,10.00,2.00,1.00);
insert into lot values(default,1,50.00,10.00,2.00);
insert into lot values(default,1,1000.00,200.00,20.00);
insert into lot values(default,2,50.00,10.00,2.00);
insert into lot values(default,2,50.00,10.00,2.00);

insert into lot_detail values(1,'Picture Frame','');
insert into lot_detail values(2,'Tennis Racket','Brand new!');
insert into lot_detail values(3,'Football Jersey','Formerly worn by OJ Simpson');
insert into lot_detail values(3,'Bloody Glove','Formerly owned by OJ Simpson');
insert into lot_detail values(3,'Gun','Used by OJ Simpson in an attempt to obtain other items in this lot.');
insert into lot_detail values(4,'Massage','From Massage Parlor');
insert into lot_detail values(5,'Hot Air Ballon','');

