--Invoice Database 

drop table description;
drop table invoice;
drop table website;
drop table customer;


CREATE TABLE Customer
(customerID   char(3)  Primary Key,
firstName     char(30),
lastName      char(30), 
company       char(50),
streetNumber  char(10),
streetName    char(30),
city          char(20),
prov          char(20),
postal        char(7),      
phone         char(14),      
phone2        char(14),     
cell          char(14),      
home          char(14),      
fax           char(14),      
customerDate  date     NOT NULL,
domain        char(40) REFERENCES website (domain));


CREATE TABLE website
(domain         char(40) PRIMARY KEY,
customerID      char(3)  REFERENCES Customer (customerID),
dateRegistered  date     NOT NULL,
dateExpires     date     NOT NULL,
amountPaid      DECIMAL(6,2));


CREATE TABLE invoice
(invoiceNumber char(4)     PRIMARY KEY,
customerID     char(3)     REFERENCES customer (customerID),
dateIssued     date        NOT NULL,
dateDue        date        NOT NULL,
datePaid       date,
status         char(10)    CHECK (status IN ('paid','unpaid','part-paid')) NOT NULL,
descriptionID  char(4)     REFERENCES description (descriptionID) NOT NULL,
subTotal       DECIMAL(6,2) NOT NULL,
PSTexempt      char(3),     
totalPST       DECIMAL(6,2)
totalDue       DECIMAL(6,2) NOT NULL,
comments       TEXT);


CREATE TABLE description
(descriptionID char(4)        PRIMARY KEY,
invoiceNumber  char(4)        REFERENCES invoice(invoiceNumber) NOT NULL,
product        TEXT           NOT NULL,
price          DECIMAL(6,2)   NOT NULL,
PSTexempt      char(3)        CHECK (PSTexempt IN ('yes','no')) NOT NULL,
PSTamount      DECIMAL(6,2));