-- CREATE DATABASE
DROP DATABASE IF EXISTS code4care;
CREATE DATABASE code4care CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI;
USE code4care;

-- CREATE TABLES


CREATE TABLE administrator (
	admin_id INT NOT NULL AUTO_INCREMENT,
	admin_first_name VARCHAR(64) NOT NULL,
	admin_last_name VARCHAR(64) NOT NULL,
	admin_email VARCHAR(255) NOT NULL,
	admin_username VARCHAR(64) NOT NULL,
	admin_password VARCHAR(255) NOT NULL,
	admin_status BIT(1) DEFAULT 1 NOT NULL,
	admin_last_login TIMESTAMP(0) DEFAULT 0,
	admin_created TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
	admin_updated TIMESTAMP(0) DEFAULT 0,
	PRIMARY KEY(admin_id),
	UNIQUE ind_admin_username (admin_username)
) ENGINE=INNODB CHARSET=UTF8MB4 COLLATE=UTF8MB4_UNICODE_CI;

CREATE TABLE customer (
	cust_id INT NOT NULL AUTO_INCREMENT,
	cust_first_name VARCHAR(64) NOT NULL,
	cust_last_name VARCHAR(64) NOT NULL,
	cust_email VARCHAR(255) NOT NULL,
	cust_username VARCHAR(64) NOT NULL,
	cust_password VARCHAR(255) NOT NULL,
	cust_status BIT(1) DEFAULT 1 NOT NULL,
	cust_last_login TIMESTAMP(0) DEFAULT 0,
	cust_created TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
	cust_updated TIMESTAMP(0) DEFAULT 0,
	PRIMARY KEY(cust_id),
	UNIQUE ind_cust_username (cust_username)
) ENGINE=INNODB CHARSET=UTF8MB4 COLLATE=UTF8MB4_UNICODE_CI;


CREATE TABLE bill_address (
	bill_id INT NOT NULL AUTO_INCREMENT,
	bill_address_1 VARCHAR(64) NOT NULL,
	bill_address_2 VARCHAR(64) NOT NULL,
	bill_city VARCHAR(64) NOT NULL,
	bill_state VARCHAR(64) NOT NULL,
	bill_zip VARCHAR(16) NOT NULL,
	bill_phone VARCHAR(16) DEFAULT NULL,
	bill_created TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP,
	bill_updated TIMESTAMP(0) DEFAULT 0,
	PRIMARY KEY(bill_id),
	fk_cust_id INT NOT NULL,
	CONSTRAINT ind_fk_cust_id UNIQUE (fk_cust_id),
	FOREIGN KEY(fk_cust_id)
		REFERENCES customer(cust_id)
		ON DELETE CASCADE

)  CHARSET=UTF8MB4 COLLATE=UTF8MB4_UNICODE_CI;


CREATE TABLE ship_address (
	ship_id INT NOT NULL AUTO_INCREMENT,
	ship_address_1 VARCHAR(64) NOT NULL,
	ship_address_2 VARCHAR(64) NOT NULL,
	ship_city VARCHAR(64) NOT NULL,
	ship_state VARCHAR(64) NOT NULL,
	ship_zip VARCHAR(16) NOT NULL,
	ship_phone VARCHAR(16) DEFAULT NULL,
	ship_created TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP,
	ship_updated TIMESTAMP(0) DEFAULT 0,
	PRIMARY KEY(ship_id),
	fk_cust_id INT NOT NULL,
	CONSTRAINT ind_fk_cust_id UNIQUE (fk_cust_id),
	FOREIGN KEY(fk_cust_id)
		REFERENCES customer(cust_id)
		ON DELETE CASCADE

)  CHARSET=UTF8MB4 COLLATE=UTF8MB4_UNICODE_CI;


CREATE TABLE product (
	prod_id INT NOT NULL AUTO_INCREMENT,
	prod_sku VARCHAR(128) NOT NULL,
	prod_brand VARCHAR(128) NOT NULL,
	prod_name VARCHAR(128) NOT NULL,
	prod_price DECIMAL(5,2) NOT NULL,
	prod_short_desc VARCHAR(255) DEFAULT NULL,
	prod_long_desc TEXT DEFAULT NULL,
	prod_cart_desc VARCHAR(128) DEFAULT NULL,
	prod_capacity DECIMAL(4,2) DEFAULT NULL,
	prod_stock INT NOT NULL,
	prod_weight DECIMAL(4,2) DEFAULT NULL,
	prod_image VARCHAR(128) DEFAULT NULL,
	prod_thumbnail VARCHAR(128) DEFAULT NULL,
	prod_created TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP,
	prod_updated TIMESTAMP(0) DEFAULT 0,
	prod_page VARCHAR(128) DEFAULT NULL,
	PRIMARY KEY(prod_id),
	CONSTRAINT ind_prod_sku UNIQUE (prod_sku)
)  CHARSET=UTF8MB4 COLLATE=UTF8MB4_UNICODE_CI;


CREATE TABLE product_rating (
	rating_id INT NOT NULL AUTO_INCREMENT,
	rating_rating INT NOT NULL,
	rating_feedback TEXT NOT NULL,
	PRIMARY KEY(rating_id),
	fk_cust_id INT NOT NULL,
	fk_prod_id INT NOT NULL,
	FOREIGN KEY(fk_cust_id)
		REFERENCES customer(cust_id)
		ON DELETE CASCADE,
	FOREIGN KEY(fk_prod_id)
		REFERENCES product(prod_id)
		ON DELETE CASCADE
)  CHARSET=UTF8MB4 COLLATE=UTF8MB4_UNICODE_CI;


CREATE TABLE customer_order (
	order_id INT NOT NULL AUTO_INCREMENT,
	order_date TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP,
	order_total DECIMAL(5,2) NOT NULL,
	order_payment_type VARCHAR(255) NOT NULL,
	order_status VARCHAR(128) DEFAULT 'Open' NOT NULL,
	order_tracking VARCHAR(255) DEFAULT NULL,
	order_carrier VARCHAR(128) DEFAULT NULL,
	order_address_1 VARCHAR(64) NOT NULL,
	order_address_2 VARCHAR(64) NOT NULL,
	order_city VARCHAR(64) NOT NULL,
	order_state VARCHAR(64) NOT NULL,
	order_zip VARCHAR(16) NOT NULL,
	order_phone VARCHAR(16) DEFAULT NULL,
	order_updated TIMESTAMP(0) DEFAULT 0,
	PRIMARY KEY(order_id),
	fk_cust_id INT NOT NULL,
	FOREIGN KEY(fk_cust_id)
		REFERENCES customer(cust_id)
)  CHARSET=UTF8MB4 COLLATE=UTF8MB4_UNICODE_CI;

CREATE TABLE order_item (
	item_id INT NOT NULL AUTO_INCREMENT,
	item_price DECIMAL(5,2) NOT NULL,
	item_quantity INT DEFAULT 1 NOT NULL,
	PRIMARY KEY(item_id),
	fk_order_id INT NOT NULL,
	fk_prod_id INT NOT NULL,
	FOREIGN KEY(fk_prod_id)
		REFERENCES product(prod_id),
	FOREIGN KEY(fk_order_id)
		REFERENCES customer_order(order_id)
)  CHARSET=UTF8MB4 COLLATE=UTF8MB4_UNICODE_CI;


CREATE TABLE question (
	question_id INT NOT NULL AUTO_INCREMENT,
	question_text VARCHAR(1000) NOT NULL,
	question_created TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP,
	question_updated TIMESTAMP(0) DEFAULT 0,
	PRIMARY KEY(question_id),
	fk_cust_id INT NOT NULL,
	FOREIGN KEY(fk_cust_id)
		REFERENCES customer(cust_id)
) CHARSET=UTF8MB4 COLLATE=UTF8MB4_UNICODE_CI;


CREATE TABLE answer (
	answer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	answer_text VARCHAR(1000) NOT NULL,
	answer_created TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP,
	answer_updated TIMESTAMP(0) DEFAULT 0,
	PRIMARY KEY(answer_id),
	fk_question_id INT NOT NULL,
	fk_cust_id INT NOT NULL,
	FOREIGN KEY(fk_question_id)
		REFERENCES question(question_id),
	FOREIGN KEY(fk_cust_id)
		REFERENCES customer(cust_id)
) CHARSET=UTF8MB4 COLLATE=UTF8MB4_UNICODE_CI;

-- TEST DATA

INSERT INTO `administrator` (`admin_id`, `admin_first_name`, `admin_last_name`, `admin_email`, `admin_username`, `admin_password`, `admin_status`, `admin_last_login`, `admin_created`, `admin_updated`) VALUES
(1, 'Esteban', 'Rodriguez', 'erodriguez0705@gmail.com', 'erodriguez', '$2y$12$OsV3UrIzX9RMXde8mZVBM.adODw587DZkbTAdzIf/cF4A/FQFMYdi', 1, NULL, '2019-10-20 22:29:30', NULL);
INSERT INTO `customer` (`cust_id`, `cust_first_name`, `cust_last_name`, `cust_email`, `cust_username`, `cust_password`, `cust_status`, `cust_last_login`, `cust_created`, `cust_updated`) VALUES
(1, 'Esteban', 'Rodriguez', 'erodriguez0705@gmail.com', 'erodriguez', '$2y$12$OsV3UrIzX9RMXde8mZVBM.adODw587DZkbTAdzIf/cF4A/FQFMYdi', 1, NULL, '2019-10-20 22:29:30', NULL);
INSERT INTO `bill_address` (`bill_id`, `bill_address_1`, `bill_address_2`, `bill_city`, `bill_state`, `bill_zip`, `bill_phone`, `bill_created`, `bill_updated`, `fk_cust_id`) VALUES
(1, '830 Chapman St', 'Apt B', 'Bakersfield', 'CA', '93307', NULL, '2019-10-20 22:31:05', NULL, 1);
INSERT INTO `ship_address` (`ship_id`, `ship_address_1`, `ship_address_2`, `ship_city`, `ship_state`, `ship_zip`, `ship_phone`, `ship_created`, `ship_updated`, `fk_cust_id`) VALUES
(1, '830 Chapman St', 'Apt B', 'Bakersfield', 'CA', '93307', NULL, '2019-10-20 22:31:05', NULL, 1);

INSERT INTO `product` (`prod_stock`, `prod_sku`, `prod_brand`, `prod_name`, `prod_short_desc`, `prod_price`, `prod_image`, prod_cart_desc) VALUES
(250, 'C4C-BS-1', 'Code4Care', 'Step 1: Body Scrub for Eczema', 'To cleanse the skin of bacteria that may be causing irritation of the skin. The scrub on a baby’s gentle skin may also reduce the inflammatory effect on the epidermis.', 11.99, 'step-1.png', 'Cleanse the skin of bacteria that may be causing irritation of the skin.');

INSERT INTO `product` (`prod_stock`, `prod_sku`, `prod_brand`, `prod_name`, `prod_short_desc`, `prod_price`, `prod_image`, prod_cart_desc, prod_page) VALUES
(250, 'C4C-M-1', 'Code4Care', 'Step 2: Moisturizer for Eczema', 'Relieve the harsh irritants on the infant’s skin and smoothen the dry patches of the cycle of eczema.', 14.99, 'step-2.png', 'Relieve the harsh irritants on the infant’s skin and smoothen the dry patches of the cycle of eczema.', 'MoisturizerPage.php');

INSERT INTO `product` (`prod_stock`, `prod_sku`, `prod_brand`, `prod_name`, `prod_cart_desc`, `prod_price`, `prod_image`, prod_page) VALUES
(250, 'C4C-CC-1', 'Code4Care', 'Step 3: Cooling Cream', 'Fusce pharetra hendrerit diam, molestie tempor dui aliquet vel. Nunc non tellus nulla.', 4.99, 'step-3.png', 'CoolingCream.php');

INSERT INTO product (prod_name, prod_stock, prod_sku, prod_brand, prod_price,  prod_short_desc, prod_cart_desc, prod_image) VALUES
('Chamomile Essential Oil', 250, 'C4C-EO-1', 'Code4Care', 5.99, 'Chamomile smoothens the areas of the skin by its antioxidant properties that can relieve itchiness and dryness.', 'Chamomile smoothens the areas of the skin by its antioxidant properties that can relieve itchiness and dryness.', 'C4C-EO-1.png');

INSERT INTO product (prod_name, prod_stock, prod_sku, prod_brand, prod_price, prod_short_desc, prod_cart_desc, prod_image) VALUES
('Aloe Vera Essential Oil', 250, 'C4C-EO-2', 'Code4Care', 5.99, 'Aloe Vera\’s extracts offers a combination of cooling sensation of vitamins A and C which promote skin hydration it\’s working agents enhance rejuvenation to the dryness of the skin like a moisturizer.', 'Aloe Vera\’s extracts offers a combination of cooling sensation of vitamins A and C which promotes skin hydration.', 'C4C-EO-2.png');
INSERT INTO product (prod_name, prod_stock, prod_sku, prod_brand, prod_price, prod_short_desc, prod_cart_desc, prod_image, prod_page) VALUES
('Tea Tree Essential Oil', 250, 'C4C-EO-3', 'Code4Care', 5.99, 'Tea tree oil has its anti-inflammatory and calming skin properties which promote circulation to the skin cells.', 'Tea tree oil has its anti-inflammatory and calming skin properties which promote circulation to the skin cells.', 'C4C-EO-3.png', 'TeaTreeOilPage.php');

INSERT INTO product (prod_name, prod_stock, prod_sku, prod_brand, prod_price, prod_short_desc, prod_cart_desc, prod_image, prod_page) VALUES
('Calendula Essential Oil', 250, 'C4C-EO-4', 'Code4Care', 5.99, 'The marigold flower is extracted and diluted to engrave its richness to the skin via its soothing sensational properties such as it being anti-inflammatory and relieves itchiness on the top barrier of the skin by reduction of (inflammation) and pain. 
', 'The marigold flower is extracted and diluted to engrave its richness to the skin via its soothing sensational properties.', 'C4C-EO-4.png', 'CalendulaPage.php');
-- INSERT INTO `product_rating` (`rating_id`, `rating_rating`, `rating_feedback`, `fk_cust_id`, `fk_prod_id`) VALUES
-- (1, 4, 'The product works!', 1, 1);
-- INSERT INTO `product` (`prod_id`, `prod_stock`, `prod_sku`, `prod_brand`, `prod_name`, `prod_cart_desc`, `prod_price`, `prod_capacity`, `prod_image`) VALUES
-- (4, 0, 'C4C-L-2', 'Code4Care', 'Step 2: Eczema Lotion (Relief Phase)', 'Vivamus pretium dictum feugiat. Vivamus sit amet placerat tortor. Curabitur ac mi sit amet ante volutpat bibendum.', 4.99, 5, 'step-2.png');
-- INSERT INTO `product` (`prod_id`, `prod_stock`, `prod_sku`, `prod_brand`, `prod_name`, `prod_cart_desc`, `prod_price`, `prod_capacity`, `prod_image`) VALUES
-- (5, 33, 'C4C-CC-2', 'Code4Care', 'Step 3: Cooling Cream', 'Fusce pharetra hendrerit diam, molestie tempor dui aliquet vel. Nunc non tellus nulla.', 4.99, 5, 'step-3.png');


-- TRIGGERS
DELIMITER //
CREATE TRIGGER check_rating BEFORE INSERT ON product_rating
FOR EACH ROW
BEGIN
	IF (NEW.rating_rating < 1 OR NEW.rating_rating > 5)
	THEN
		SIGNAL SQLSTATE '45000'
			SET MESSAGE_TEXT = 'Invalid rating';
	END IF;
END;
//
