9trgbGc1

1drop database if exists shop;
2CREATE DATABASE shop;
3use shop;
4CREATE TABLE products (
5    id INT(11) NOT NULL AUTO_INCREMENT,
6    productName VARCHAR(50) DEFAULT NULL,
7    quantityAvailable INT(11) DEFAULT NULL,
8    price DOUBLE DEFAULT NULL,
9    productInformation TEXT,
10    imageName VARCHAR(16) DEFAULT NULL,
11    category VARCHAR(20) DEFAULT NULL,
12    PRIMARY KEY (id)
13);
14CREATE TABLE users (
15    id INT(11) NOT NULL AUTO_INCREMENT,
16    email VARCHAR(30) NOT NULL,
17    password VARCHAR(50) NOT NULL,
18    firstName VARCHAR(30) NOT NULL,
19    lastName VARCHAR(30) NOT NULL,
20    address VARCHAR(255) NOT NULL,
21    town VARCHAR(30) NOT NULL,
22    telNum VARCHAR(15) NOT NULL,
23    role int(5) DEFAULT 0,
24    salt VARCHAR(255) NOT NULL,
25    PRIMARY KEY (id),
26    UNIQUE KEY email (email)
27);
28CREATE TABLE user_purchase (
29    id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
30    user_id INT(11) DEFAULT NULL,
31    purchaseStatus int(5) DEFAULT 1,
32    deliveryAddress varchar(255) DEFAULT NULL,
33    purchaseDate DATETIME DEFAULT CURRENT_TIMESTAMP,
34    CONSTRAINT FOREIGN KEY(user_id) REFERENCES users(id)
35);
36CREATE TABLE products_cart (
37    id INT(11) PRIMARY KEY AUTO_INCREMENT,
38    user_id INT(11) NOT NULL,
39    product_id INT(11) NOT NULL,
40    quantityOfCartProduct INT(11) NOT NULL,
41    purchase_id INT DEFAULT 0,
42    CONSTRAINT FOREIGN KEY(user_id) REFERENCES users(id),
43    CONSTRAINT FOREIGN KEY(product_id) REFERENCES products(id)
44);
45
46CREATE TABLE user_password(
47	id INT(11) PRIMARY KEY AUTO_INCREMENT,
48    user_id INT(11) NOT NULL,
49    creationDate DATETIME DEFAULT CURRENT_TIMESTAMP,
50    verificationCode varchar(10) NOT NULL 
51);
52
53
54INSERT INTO users(email, password, firstName, lastName, address, town, telNum, role, salt)
55values('niki1509tl3@gmail.com', 'c88b0dde0a04219e5a84d811be68bb3', 'admin', 'admin', 'admin','admin', 'admin', 3,'[B@15668d40');
56/*pass 123Qwe*/