/*
 * Schema for lab 4, Comp2400, 2007
 *
 * Robin Garner
 * Dept. of Computer Science, ANU
 */

DROP TABLE wine_order;
DROP TABLE wine;
DROP TABLE customer;

CREATE TABLE customer (
  customer_name     varchar  PRIMARY KEY,
  address           varchar  NOT NULL
);

CREATE TABLE wine (
  wine_name         varchar      PRIMARY KEY,
  price_per_bottle  numeric(8,2) NOT NULL,
  quantity_in_stock int          NOT NULL
);

CREATE TABLE wine_order (
  customer_name     varchar      NOT NULL REFERENCES customer,
  wine_name         varchar      NOT NULL REFERENCES wine,
  order_date        date         NOT NULL,
  shipped_date      date,
  quantity          int          NOT NULL CHECK (quantity > 0),
  amount_paid       numeric(8,2) NOT NULL 
    CHECK (amount_paid >= 0 AND amount_paid <= total_price),
  total_price       numeric(8,2) NOT NULL CHECK (total_price > 0),
  PRIMARY KEY (customer_name,wine_name,order_date)
);

COPY customer (customer_name, address) FROM stdin CSV;
Fred Bloggs,1 First Street
Jane Doe,2 Second Street
Xiao Feng,3 Third Street
\.

COPY wine (wine_name, price_per_bottle, quantity_in_stock) FROM stdin CSV;
Chateau Thames Embankment 2007,5.00,1000
Chateau dYquem 1956,1200.00,1
Grange Hermitage 1967,1500.00,0
Chateau Thames Embankment 2006,5.00,96
Chateau Carton 2006,7.00,1200
Chateau Fleet Street 2004, 6.00, 12000
\.

COPY wine_order (customer_name, wine_name, order_date, shipped_date, quantity, total_price, amount_paid) FROM stdin CSV;
Fred Bloggs,Chateau Thames Embankment 2007,2007-01-01,2007-01-02,12,54.00,54.00
Fred Bloggs,Chateau Thames Embankment 2007,2007-01-14,2007-01-16,6,54.00,54.00
Fred Bloggs,Chateau Thames Embankment 2006,2007-01-14,2007-01-16,6,54.00,54.00
Fred Bloggs,Chateau Carton 2006,2007-09-17,,8,86.40,20.0
Fred Bloggs,Chateau Thames Embankment 2007,2007-09-17,,8,86.40,20.0
Jane Doe,Chateau dYquem 1956,2007-01-01,2007-01-08,1,1200,1200
Jane Doe,Chateau dYquem 1956,2007-02-17,2007-02-28,1,1200,1200
Jane Doe,Chateau dYquem 1956,2007-04-06,2007-04-25,1,1200,1200
Xiao Feng,Grange Hermitage 1967,2007-04-29,2007-05-03,1,1399.50,1399.50
Xiao Feng,Chateau Thames Embankment 2006,2007-04-29,2007-05-03,11,1399.50,1399.50
Xiao Feng,Grange Hermitage 1967,2007-09-17,,1,1399.50,1399.50
Xiao Feng,Chateau Thames Embankment 2006,2007-09-17,,11,1399.50,1399.50
\.