-- tables for a fruit stand 
-- to use, connect to database: fruitstand
--   psql fruitstand
--  you have select and insert permissions. (when the apples are gone, they are gone!)

create table fruit (
  name char(12) primary key,
  price money check (price>cost),
  cost  money  not null check ("cost" >= 0),
  stock int check (stock >= 0)
);

create table sales (
  who varchar(20) default 'CASH',
  date date default current_date,
  total money default 0,
  saleid serial primary key
);

create table sold (
  fruit char(12) not null references fruit(name),
  quantity int not null check (quantity > 0),
  saleid int not null references sales 
    default currval('sales_saleid_seq')
);

-- we haven't gotten here yet, but a RULE allows you to define further actions
create or replace rule figure as
on insert to sold do also (
			-- subtract from stock
   update fruit set stock = stock-new.quantity
	  where name = new.fruit;
			-- add price*quantity to this sale
   update sales set total = total + new.quantity *
	  (select price from fruit where name=new.fruit)
      where saleid = new.saleid
);
