Project 1

DROP SCHEMA IF EXISTS ecommerce;
CREATE SCHEMA ecommerce;
USE ecommerce;

CREATE TABLE user_data (
user_id INT PRIMARY KEY,
First_name VARCHAR(50) NOT NULL,
Last_name VARCHAR(50) NOT NULL,
dob DATE NOT NULL,
gender VARCHAR(1) NOT NULL,
primary_address INT,
primary_phone INT,
primary_email INT,
primary_payment INT
);

CREATE TABLE buyer (
buyer_id INT PRIMARY KEY,
user_id INT NOT NULL,
country VARCHAR(40) NOT NULL,
country_code INT NOT NULL,
area_code INT NOT NULL,
phone_number INT NOT NULL,
extention INT,
phone_type VARCHAR(8) NOT NULL,
primary_phone BOOLEAN NOT NULL DEFAULT FALSE,
FOREIGN KEY(user_id) REFERENCES user_data(user_id) ON DELETE CASCADE
);


ALTER TABLE user_data
ADD FOREIGN KEY(phone_id)
REFERENCES buyer(phone_id)
ON DELETE CASCADE;

CREATE TABLE email (
email_id INT PRIMARY KEY,
user_id INT NOT NULL,
email VARCHAR(260) NOT NULL,
email_type VARCHAR(20) NOT NULL,
primary_email BOOLEAN NOT NULL DEFAULT FALSE,
FOREIGN KEY(user_id) REFERENCES user_data(user_id) ON DELETE CASCADE
);

CREATE TABLE payment (
payment_id INT PRIMARY KEY,
user_id INT NOT NULL,
cc_number VARCHAR(16) NOT NULL,
cc_type VARCHAR(20) NOT NULL,
cc_name_different VARCHAR(100),
cc_exp_month INT NOT NULL,
cc_exp_year INT NOT NULL,
cc_sec_code INT NOT NULL,
primary_payment BOOLEAN NOT NULL DEFAULT FALSE,
FOREIGN KEY(user_id) REFERENCES user_data(user_id) ON DELETE CASCADE
);
select * from buyer;

CREATE TABLE order_details (
order_id INT PRIMARY KEY,
user_id INT NOT NULL,
cart_id INT,
payment_id INT NOT NULL,
payment_date DATE,
order_date DATE NOT NULL,
ship_date DATE,
ship_method VARCHAR(30) NOT NULL,
fullfillment_date DATE,
cancellation_date DATE,
return_date DATE,
FOREIGN KEY(user_id) REFERENCES user_data(user_id) ON DELETE CASCADE,
FOREIGN KEY(payment_id) REFERENCES payment(payment_id) ON DELETE CASCADE
);

CREATE TABLE cart (
cart_id INT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT,
quantity INT NOT NULL,
discount INT,
FOREIGN KEY(order_id) REFERENCES order_details(order_id) ON DELETE CASCADE
);

ALTER TABLE order_details
ADD FOREIGN KEY (cart_id)
REFERENCES cart(cart_id)
ON DELETE CASCADE;

CREATE TABLE catalog_product (
product_id INT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL,
user_id INT NOT NULL,
manufacturer_id VARCHAR(10) NOT NULL,
color_id INT,
size_id INT,
unit_id INT,
price_per_unit INT,
weight_per_unit INT
);

ALTER TABLE cart
ADD FOREIGN KEY(product_id)
REFERENCES catalog_product(product_id)
ON DELETE CASCADE;

SELECT 'LOADING user_data ' as 'INFO';
SOURCE load_user_data.dump;

SELECT 'LOADING email' as 'INFO' ;
SOURCE load_email.dump;

SELECT 'LOADING order_details' as 'INFO';
SOURCE load_order_details.dump;

SELECT 'LOADING phone' as 'INFO';
SOURCE load_phone.dump;

source show_elapsed.sql ;