Musa_Kazim_01676780800.sql

CREATE DATABASE cloud_kitchen;

USE cloud_kitchen;

CREATE TABLE menu_items (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,
	
	name VARCHAR(60),
	description VARCHAR(255),
	price DECIMAL(10, 2)
);

CREATE TABLE orders (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,

	status ENUM('new', 'in-progress', 'completed'),

	customer_id INT UNSIGNED NOT NULL,

	FOREIGN KEY (customer_id) REFERENCES customers (id)

);

CREATE TABLE customers (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,

	name VARCHAR(40),
	email VARCHAR(40),
	phone_number VARCHAR(15),
	password VARCHAR(40),
	date_of_birth DATETIME,

	address_id INT UNSIGNED,

	FOREIGN KEY (address_id) REFERENCES addresses (id),
	UNIQUE INDEX unq_email (email)
);


CREATE TABLE addresses (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,

	street VARCHAR(60),
	zip_code VARCHAR(10),
	house_no VARCHAR(10),
	state VARCHAR(20)
);

CREATE TABLE menu_item_images (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,
	
	image VARCHAR(255),

	menu_item_id INT UNSIGNED NOT NULL,

	FOREIGN KEY (menu_item_id) REFERENCES menu_items (id)
);

CREATE TABLE order_items (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,

	quantity INT NOT NULL,

	menu_item_id INT UNSIGNED NOT NULL,
	order_id INT UNSIGNED NOT NULL,

	FOREIGN KEY (menu_item_id) REFERENCES menu_items (id),
	FOREIGN KEY (order_id) REFERENCES orders (id)

	
);


SELECT *
FROM menu_items
ORDER BY created_at DESC
LIMIT 20;

SELECT *
FROM orders
WHERE created_at = CURRENT_DATE() and ( status = 'new' OR status = 'in-progress')
ORDER BY created_at;
;

DELETE FROM orders
WHERE status = 'completed' AND created_at < now() - INTERVAL 15 day;