--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,
invStatus      char(10)     NOT NULL CHECK (status IN ('paid','unpaid','part-paid')),
descriptionID  char(4)      NOT NULL REFERENCES description (descriptionID),
subTotal       DECIMAL(6,2) NOT NULL,
PSTexempt      char(3),     
totalPST       DECIMAL(6,2)
totalDue       DECIMAL(6,2) NOT NULL,
invComments       TEXT);


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