SET AUTOCOMMIT=1;

CREATE DATABASE IF NOT EXISTS botiga_php_db charset utf8;

USE botiga_php_db; 

DROP TABLE IF EXISTS bp_products;
DROP TABLE IF EXISTS bp_categories;
DROP TABLE IF EXISTS bp_users;

CREATE TABLE IF NOT EXISTS botiga_php_db.bp_users (
    bp_user_id             INT AUTO_INCREMENT,
    bp_user_name           VARCHAR(32) NOT NULL UNIQUE,
    bp_user_pass           VARCHAR(64) NOT NULL UNIQUE,
    bp_user_addDate        DATE NOT NULL,
    PRIMARY KEY (bp_user_id)
 );

 CREATE TABLE IF NOT EXISTS botiga_php_db.bp_categories (
    bp_category_id         INT AUTO_INCREMENT,
    bp_category_name       VARCHAR(32) NOT NULL UNIQUE,
    PRIMARY KEY (bp_category_id)
 );

 CREATE TABLE IF NOT EXISTS botiga_php_db.bp_products (
    bp_product_id           INT AUTO_INCREMENT,
    bp_product_name         VARCHAR(32) NOT NULL,
    bp_product_info         VARCHAR(50),
    bp_product_price        DOUBLE NOT NULL,
    bp_product_category     INT,
    bp_product_img_01       VARCHAR(32),
    bp_product_img_02       VARCHAR(32),
    bp_product_img_03       VARCHAR(32),
    bp_product_addDate      DATE NOT NULL,
    bp_product_owner        INT,
    PRIMARY KEY (bp_product_id),
    FOREIGN KEY (bp_product_category) REFERENCES bp_categories(bp_category_id),
    FOREIGN KEY (bp_product_owner) REFERENCES bp_users(bp_user_id)
 );

INSERT INTO `bp_categories` (`bp_category_id`, `bp_category_name`) VALUES (NULL, 'Wooden'), (NULL, 'Brick'), (NULL, 'Stone'), (NULL, 'Alimium'), (NULL, 'Mix');

INSERT INTO bp_users VALUES (NULL, 'jones', '$2y$10$cQ/dbUCwpn70GxHyzLLG8O0KH0o67uagM8x0UASYXToTVplCFq4.m', '2021-12-09');
INSERT INTO bp_users VALUES (NULL, 'jack', '$2y$10$nkeFH50KmNCQKxKrsE/NQu1gx5YMqPxrQyL9e/eRyY1p9j8GrOTe.', '2021-09-12');
INSERT INTO bp_users VALUES (NULL, 'john', '$2y$10$c60U1n5hhploFWTGgnM3SOB5oSH70/f2sGvz1S5H3EZIeEul5x4ZK', '2021-10-11');
INSERT INTO bp_users VALUES (NULL, 'jerry', '$2y$10$gtQrwpRjdydYJTTXLa5jWOK3/CYriXNDuUuHtFqrlQ1o2w7a7893u', '2021-11-10');


INSERT INTO bp_products VALUES (NULL, 'Hanni Koroma', 'Japanese designed wooden nordic house', '95000', '1', 'house01.jpg', 'house02.jpg', 'house03.jpg', '2001-11-22', 1);
INSERT INTO bp_products VALUES (NULL, 'Fiskarhedenvillan', 'Classic 20th century English brick house', '200000', '2', 'house04.jpg', 'house05.jpg', 'house06.jpg', '1958-06-20', 2);
INSERT INTO bp_products VALUES (NULL, 'Barn House', 'American aluminium suspended house', '65000', '5', 'house07.jpg', 'house08.jpg', 'house09.jpg', '1994-09-11', 3);
INSERT INTO bp_products VALUES (NULL, 'Hygge', 'Nordic house built with a mix of natural materials', '150000', '5', 'house10.jpg', 'house11.jpg', 'house12.jpg', '2021-11-28', 4);