-- Create the database.
CREATE DATABASE crisscott;

-- Create the scott user.
CREATE USER scott;

-- Switch to the new database.
\c crisscott scott

-- Products table.
CREATE TABLE products (
product_id		SERIAL			PRIMARY KEY NOT NULL,
product_name 	VARCHAR(100)	NOT NULL,
description		TEXT,
product_type	VARCHAR(50)		NOT NULL,
category_id		INTEGER			NOT NULL,
inventory		INTEGER			NOT NULL DEFAULT 0,
available		BOOLEAN			NOT NULL DEFAULT FALSE,
width			NUMERIC(5,2),
height			NUMERIC(5,2),
depth			NUMERIC(5,2),
weight			NUMERIC(5,2),
image_path		VARCHAR(255)
);

-- Product price table.
CREATE TABLE product_price (
product_id		INTEGER			NOT NULL,
price			NUMERIC(5,2)	NOT NULL,
currency		VARCHAR(10)		NOT NULL
);

-- Categories table.
CREATE TABLE categories (
category_id		SERIAL			PRIMARY KEY NOT NULL,
category_name	VARCHAR(100)	NOT NULL
);

-- Contributor table.
CREATE TABLE contributor (
contributor_id	SERIAL			PRIMARY KEY NOT NULL,
first_name		VARCHAR(50)		NOT NULL,
middle_name		VARCHAR(50),
last_name		VARCHAR(100)	NOT NULL,
website			VARCHAR(100),
email			VARCHAR(100)	NOT NULL,
street1			VARCHAR(100)	NOT NULL,
street2			VARCHAR(100),
city			VARCHAR(100)	NOT NULL,
state			CHAR(2),
country			CHAR(2)			NOT NULL,
postal			VARCHAR(10)		NOT NULL
);

-- Populate the tables.
INSERT INTO categories (category_name) VALUES ('Shippable Software');
INSERT INTO categories (category_name) VALUES ('Downloadable Software');
INSERT INTO categories (category_name) VALUES ('Books');
INSERT INTO categories (category_name) VALUES ('Misc');

INSERT INTO products
(product_name, description, product_type, category_id, inventory, width, height, depth, weight)
VALUES ('Pro PHP-GTK', 'The single greatest literary work ever!', 'Book', 3, 15, 7.5, 10, 1.75, 16);
INSERT INTO products
(product_name, description, product_type, category_id, inventory, width, height, depth, weight)
VALUES ('Blue PHP-GTK Pencils', 'Pencils with PHP-GTK on them.', 'Misc', 4, 5, .25, 7, .25, 2);

INSERT INTO product_price (product_id, price, currency) VALUES (1, 44.95, 'USD');