CREATE DATABASE IF NOT EXISTS PorkShop;
USE PorkShop;

create table if not exists customer (
  customer_id INT NOT NULL AUTO_INCREMENT,
  fname VARCHAR(30) NOT NULL,
  lname VARCHAR(30) NOT NULL,
  email VARCHAR(45) NOT NULL UNIQUE,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  primary key (customer_id)
);

create table if not exists address (
  address_id INT NOT NULL AUTO_INCREMENT,
  customer_id INT NOT NULL,
  address_line_1 VARCHAR(20) NOT NULL,
  address_line_2 VARCHAR(20),
  city VARCHAR(15) NOT NULL,
  state VARCHAR(2) NOT NULL,
  zipcode VARCHAR(5) NOT NULL,
  primary key (address_id),
  foreign key (customer_id) references customer(customer_id)
);

create table if not exists card (
  payment_id INT NOT NULL AUTO_INCREMENT,
  customer_id INT NOT NULL,
  cc_holder_name VARCHAR(60) NOT NULL,
  cc_num VARCHAR(16) NOT NULL,
  cc_exp_month VARCHAR (2) NOT NULL,
  cc_exp_year VARCHAR (2) NOT NULL,
  cc_cvc VARCHAR(3) NOT NULL,
  billing_addr_id INT,
  primary key (payment_id),
  foreign key (customer_id) references customer(customer_id),
  foreign key (billing_addr_id) references address(address_id) ON DELETE SET NULL
);

create table if not exists orders (
  order_id INT NOT NULL AUTO_INCREMENT,
  customer_id INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  sub_total DECIMAL(19, 2),
  tax_amount DECIMAL(19, 2),
  grand_total DECIMAL(19, 2),
  status VARCHAR(10),
  shipping_addr_id INT,
  primary key (order_id),
  foreign key (customer_id) references customer(customer_id),
  foreign key (shipping_addr_id) references address(address_id)
  ON DELETE SET NULL ON UPDATE CASCADE
);


create table if not exists payment_info (
  payment_info_id INT NOT NULL AUTO_INCREMENT,
  order_id INT NOT NULL,
  cc_holder_name VARCHAR (60) NOT NULL,
  cc_last_four VARCHAR(4) NOT NULL,
  cc_exp_month VARCHAR(2) NOT NULL,
  cc_exp_yr VARCHAR(2),
  approval_status VARCHAR(10),
  primary key (payment_info_id),
  foreign key (order_id) references orders(order_id)
);

create table if not exists brand (
  brand_id INT NOT NULL AUTO_INCREMENT,
  brand_name VARCHAR(45) NOT NULL,
  phone VARCHAR(11) NOT NULL,
  primary key (brand_id)
);

create table if not exists category (
  cat_id INT NOT NULL AUTO_INCREMENT,
  cat_name VARCHAR(45) NOT NULL,
  description VARCHAR(255) NOT NULL,
  primary key (cat_id)
);

create table if not exists product (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  product_desc VARCHAR(255),
  product_price DECIMAL(19, 2) NOT NULL,
  product_image VARCHAR(100),
  cat_id INT NOT NULL,
  brand_id INT NOT NULL,
  primary key (product_id),
  foreign key(cat_id) references category(cat_id),
  foreign key(brand_id) references brand(brand_id)
);

create table if not exists order_product (
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  qty INT NOT NULL,
  price DECIMAL (19, 2),
  primary key (order_id, product_id)
);

-- add a condition, if qty > 0 in_stock == true
create table if not exists inventory (
  product_id INT NOT NULL,
  qty INT NOT NULL,
  in_stock BOOLEAN NOT NULL,
  max_sale_qty INT NOT NULL,
  min_sale_qty INT NOT NULL,
  min_stock_threshold INT NOT NULL,
  primary key (product_id)
);

DELIMITER $$
CREATE TRIGGER `before_inventory_update`
BEFORE UPDATE ON `inventory`
 FOR EACH ROW BEGIN
  declare msg varchar(128);
  IF new.qty < 0 THEN
    SET msg = concat('QuantityConstraintError: Trying to insert a negative value in qty: ', cast(new.qty as char));
    signal sqlstate '45000' SET message_text = msg;
   end IF
END$$
DELIMITER;


create table if not exists employee (
  emp_id INT NOT NULL AUTO_INCREMENT,
  fname VARCHAR(30) NOT NULL,
  lname VARCHAR(30) NOT NULL,
  email VARCHAR(255) NOT NULL,
  type VARCHAR(10) NOT NULL,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  primary key (emp_id)
);

create table if not exists order_change_log (
  change_log_id INT NOT NULL AUTO_INCREMENT,
  emp_id INT NOT NULL,
  order_id INT NOT NULL,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  description VARCHAR(255),
  primary key (change_log_id),
  foreign key (order_id) references orders(order_id),
  foreign key (emp_id) references employee(emp_id)
);

-- Creating a view for the sales
CREATE VIEW sales_info AS
SELECT o.order_id, o.created_at AS order_date,
p.product_id, p.qty, p.price, c.cat_name
FROM orders AS o, order_product AS p, product AS pd, category AS c
WHERE o.order_id = p.order_id AND p.product_id = pd.product_id
AND pd.cat_id = c.cat_id ORDER BY order_id;


-- insert into customer values(1, "Daniel", "Lawson", "daniellawson@mail.com", "dlawson87", "password");
-- insert into customer values(2, "May", "Moody", "maymoody@ymail.com", "maymoody67", "password");
-- insert into customer values(3, "Guy", "Bishop", "guybishop@mail.com", "guybishop88", "password");
-- insert into customer values(4, "Alicia", "Sandoval", "aliciasando@mail.com", "lizzysandoval", "password");
--
-- insert into address values(1, 1, "1345 Stone St", "APT 657", "Minneapolis", "MN", "12345");
-- insert into address values(2, 1, "2345 Henepin Ave", "Suite 345", "Minneapolis", "MN", "12354");
-- insert into address values(3, 2, "12 SW Garcia Ave", "", "San Antonio", "TX", "78234");
-- insert into address values(4, 3, "1234 Wayward Pines", "APT 324", "Mountain View", "CA", "98674");
-- insert into address values(5, 4, "865 Heatherstone", "", "Orlando", "FL", "34567");
-- insert into address values(6, 4, "1254 Canterbury Rd", "", "Austin", "TX", "75465");
--
-- insert into card values(1, 1, "Daniel Lawson", "1234567891011234", "09", "19", "123", 2);
-- insert into card values(2, 1, "Daniel Lawson", "4952886449112142", "12", "20", "456", 2);
-- insert into card values(3, 2, "May Moody", "5486572352459909", "01", "18", "765", 3);
-- insert into card values(4, 3, "Guy Bishop", "1386194809291102", "10", "21", "342", 4);
-- insert into card values(5, 4, "Alicia Sandoval", "4187632373022016", "11", "20", "787", 5);
--
-- -- insertion of brands
-- insert into brand values(1, "Puppy Apparel Co.", "2025551234");
-- insert into brand values(2, "Good Boy Food Factory", "2025559999");
-- insert into brand values(3, "Pooch Accessories", "2025555678");
--
-- -- insertion into categories
-- insert into category values(1, "Apparel", "Clothes and Costumes");
-- insert into category values(2, "Toys", "Chewing Toys, bones, rubber balls");
-- insert into category values(3, "Beds", "Beds of different sizes");
-- insert into category values(4, "Food", "dry food, wet food, training snacks");
-- insert into category values(5, "Accessories", "hats, leashes, collars, miscellaneous");
--
-- -- insert into products
-- insert into product values(1, "Ewok Costume", "front paw makes your pup look like they're walking on two feet. Has Velcro.", 12.99, "ewok_costume.jpeg", 1, 1);
-- insert into product values(2, "Reversible Pink Raincoat", "Has Velcro closure, water-resistant, pink color", 14.99, "reversible_pink.jpeg", 1, 1);
-- insert into product values(3, "Yoda costume", "Has Velcro closure, water-resistant, leash port in back allows access to dog's collar", 19.99, "yoda_costume.jpeg", 1, 1);
-- insert into product values(4, "Reversible Blue Raincoat", "Has Velcro closure, water-resistant, blue color", 14.99, "reversible_blue.jpeg", 1, 1);
-- insert into product values(5, "Split Hood Parka", "Has Velcro closure, pleated t-body construction for improved fit", 29.99, "red_parka.jpeg", 1, 1);
-- insert into product values(6, "Chewbacca Hoodie", "Easily slips over your dog's head. Features Chewbacca fur with hood and bandolier strap", 14.99, "chewie_costume.jpeg", 1, 1);
-- insert into product values(7, "Beef Dry Food", "Grain-Free nutrition with no gluten ingredients for easier digestibility", 53.99, "dryfood1.jpeg", 4, 2);
-- insert into product values(8,	"Salmon Dry Food", "Fresh salmon is always first for great taste. Made with whole foods for gentle digestion", 59.99, "dryfood2.jpeg", 4, 2);
-- insert into product values(9,	"Chicken Dry Food", "Grain Free, Puppy Recipe Dry Dog Food from WholeHearted. Real chicken is the first ingredient", 24.99, "dryfood3.jpeg", 4, 2);
-- insert into product values(10, "Beef Wet Food", "Case of 12, Ensures optimal nutrient absorption to keep your dog fit and healthy", 18.99, "wetfood1.jpeg", 4, 2);
-- insert into product values(11, "Wet Food Pack", "Case of 12", 8.99, "wetfood2.jpeg", 4, 2);
-- insert into product values(12, "Beef Canned Food", "Case of 12", 23.99, "wetfood3.jpeg", 4, 2);
-- insert into product values(13, "Monster Ball", "Squeaks when played with. Made of latex", 6.99, "toy1.jpeg", 2, 3);
-- insert into product values(14, "Teddy Bear", "Nontoxic, No messy filling", 10.99, "toy2.jpeg", 2, 3);
-- insert into product values(15, "Chewie Bone", "Authentic STAR WARS themed dog toy. Soft plush material", 9.99, "toy3.jpeg", 2, 3);
-- insert into product values(16, "SqueakAir Tennis Ball", "Non-abrasive tennis material will not wear down your dog's teeth", 10.99, "toy4.jpeg", 2, 3);
-- insert into product values(17, "Yoda Plushie", "Encourages active play through crinkling and shaking", 13.99, "toy5.jpeg", 2, 3);
-- insert into product values(18, "Knot Bone", "Nylabone Big Chews are so big and so strong that even your big dog cannot destroy them", 12.99, "toy6.jpeg", 2, 3);
-- insert into product values(19, "Grey Orthopedic Bed", "Large Orthopedic Foam Filled Dog Bed from the Harmony Manhattan Lofts Collection", 44.99, "bed1.jpeg", 3, 3);
-- insert into product values(20, "Medium Orthopedic Bed", "For medium breeds. Orthopedic foam helps ease joint pain", 49.99, "bed2.jpeg",	3, 3);
-- insert into product values(21, "Small Gold Bed", "For small breeds. Cushion is filled with an orthopedic foam fill to provide hip and joint support and added comfort", 34.99, "bed3.jpeg", 3, 3);
-- insert into product values(22, "Adjustable Leash", "Great for hands-free use in walking or hiking", 10.99, "leash1.jpeg", 5, 3);
-- insert into product values(23, "Dapper Dan Bows", "One bowtie is dapper and modern in a burgundy and white polka dot print", 12.99, "bows.jpeg", 5, 3);
-- insert into product values(24, "Adjustable Harness", "Built-in slide allows you to adjust size for a customized fit", 12.99, "harness1.jpeg", 5, 3);
-- insert into product values(25, "Chewbacca Leash", "Max Length 7.5 inches. Accommodates waists up to 6 inches", 13.99, "leash2.jpeg", 5, 3);
-- insert into product values(26, "Rose Gold Collar", "Rose Gold Bling Dog Collar from the Modern Luxe Collection", 12.99, "collar1.jpeg",	5, 3);
-- insert into product values(27, "Retractable Leash", "Perfectly designed two-color tape leash for dogs with color-coordinated brake button", 23.99, "leash3.jpeg", 5, 3);
--
-- -- insertion into INVENTORY
--
-- insert into inventory values(1, 25, TRUE, 10, 1, 10); -- clothes
-- insert into inventory values(2, 25, TRUE, 10, 1, 10); -- clothes
-- insert into inventory values(3, 25, TRUE, 10, 1, 10); -- clothes
-- insert into inventory values(4, 25, TRUE, 10, 1, 10); -- clothes
-- insert into inventory values(5, 25, TRUE, 10, 1, 10); -- clothes
-- insert into inventory values(6, 25, TRUE, 10, 1, 10); -- clothes
-- insert into inventory values(7, 100, TRUE, 10, 1, 10); -- food
-- insert into inventory values(8, 100, TRUE, 10, 1, 10); -- food
-- insert into inventory values(9, 100, TRUE, 10, 1, 10); -- food
-- insert into inventory values(10, 100, TRUE, 10, 1, 10); -- food
-- insert into inventory values(11, 100, TRUE, 10, 1, 10); -- food
-- insert into inventory values(12, 100, TRUE, 10, 1, 10); -- food
-- insert into inventory values(13, 100, TRUE, 10, 1, 10); -- toys
-- insert into inventory values(14, 100, TRUE, 10, 1, 10); -- toys
-- insert into inventory values(15, 50, TRUE, 10, 1, 10); -- toys
-- insert into inventory values(16, 100, TRUE, 10, 1, 10); -- toys
-- insert into inventory values(17, 100, TRUE, 10, 1, 10); -- toys
-- insert into inventory values(18, 50, TRUE, 10, 1, 10); -- toys
-- insert into inventory values(19, 30, TRUE, 2, 1, 2); -- beds
-- insert into inventory values(20, 30, TRUE, 2, 1, 2); -- beds
-- insert into inventory values(21, 30, TRUE, 2, 1, 2); -- beds
-- insert into inventory values(22, 50, TRUE, 10, 1, 10); -- accessories
-- insert into inventory values(23, 50, TRUE, 10, 1, 10); -- accessories
-- insert into inventory values(24, 50, TRUE, 10, 1, 10); -- accessories
-- insert into inventory values(25, 50, TRUE, 10, 1, 10); -- accessories
-- insert into inventory values(26, 50, TRUE, 10, 1, 10); -- accessories
-- insert into inventory values(27, 50, TRUE, 10, 1, 10); -- accessories
--
-- insert into employee values(1, "Ernesto", "Sims", "ernestosims@borkshop.com", "admin", "ernestosims67", "password");
-- insert into employee values(2, "Bonnie", "Hopper", "bonniehopper@borkshop.com", "service", "bonniehopper13", "password");
--
-- -- Inserting preliminary Orders for username dlawson87
-- insert into orders (customer_id, sub_total, tax_amount, grand_total, status, shipping_addr_id)
-- VALUES(1, 139.93, 11.55, 151.48, "PROCESSING", 1);
--
-- insert into order_product VALUES(1, 3, 1, 19.99);
-- insert into order_product VALUES(1, 9 , 4, 24.99);
-- insert into order_product VALUES(1, 13, 1, 6.99);
-- insert into order_product VALUES(1, 26, 1, 12.99);