-- Databas: ebookstore
-- Tabell: customer (Ersätter users)
CREATE TABLE customer (
cus_id INT PRIMARY KEY AUTO_INCREMENT,
cus_name VARCHAR(255) NOT NULL,
cus_mail VARCHAR(255) NOT NULL UNIQUE,
cus_pass VARCHAR(255) NOT NULL,
cus_roll ENUM('user', 'admin') NOT NULL DEFAULT 'user'
);
-- Tabell: book
CREATE TABLE book (
book_id INT PRIMARY KEY AUTO_INCREMENT,
author VARCHAR(255),
book_name VARCHAR(255),
img_url VARCHAR(255),
price DECIMAL(7,2)
);
-- Tabell: order
CREATE TABLE `order` (
order_id INT PRIMARY KEY AUTO_INCREMENT,
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
payment_da DATETIME,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customer(cus_id) ON DELETE CASCADE
);
-- Tabell: bookorder
CREATE TABLE bookorder (
bookorder_id INT PRIMARY KEY AUTO_INCREMENT,
quantity INT NOT NULL,
price DECIMAL(7,2) NOT NULL,
order_order INT,
book_book_id INT,
FOREIGN KEY (order_order) REFERENCES `order`(order_id) ON DELETE CASCADE,
FOREIGN KEY (book_book_id) REFERENCES book(book_id) ON DELETE CASCADE
);
-- Startdata för böcker (3-4 böcker)
INSERT INTO book (author, book_name, img_url, price) VALUES
('George Orwell', '1984', 'images/1984.jpg', 99.50),
('Aldous Huxley', 'Brave New World', 'images/brave_new_world.jpg', 129.00),
('Ray Bradbury', 'Fahrenheit 451', 'images/fahrenheit_451.jpg', 89.00),
('J.R.R. Tolkien', 'The Hobbit', 'images/the_hobbit.jpg', 149.00);