schema.sql

use confusion;

CREATE TABLE dish (
    id          INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name        VARCHAR(50) UNIQUE NOT NULL,
	image       VARCHAR(50) NOT NULL,
	category    VARCHAR(20) NOT NULL,
	label       VARCHAR(10) DEFAULT '',
	price       FLOAT NOT NULL,
	featured    BOOLEAN NOT NULL DEFAULT 0,
	description TEXT NOT NULL,
	createdAt   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updatedAt   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE comment (
	id        INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	dishId    INT(6) UNSIGNED NOT NULL,
	rating    TINYINT(1) UNSIGNED NOT NULL,
	comment   TEXT,
	authorId  INT(6) UNSIGNED NOT NULL,
	date      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	FOREIGN KEY (dishId) REFERENCES dish(id) ON DELETE CASCADE,
	FOREIGN KEY (authorId) REFERENCES user(id) ON DELETE CASCADE
);

CREATE TABLE leader (
	id          INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name        VARCHAR(50) NOT NULL,
	image       VARCHAR(50) NOT NULL,
	designation VARCHAR(50) NOT NULL,
	abbr        VARCHAR(10) NOT NULL,
	featured    BOOLEAN NOT NULL DEFAULT 0,
	description TEXT NOT NULL,
	createdAt   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updatedAt   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE promotion (
	id          INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name        VARCHAR(50) NOT NULL,
	image       VARCHAR(50) NOT NULL,
	label       VARCHAR(20) NOT NULL DEFAULT '',
	price       FLOAT NOT NULL,
	featured    BOOLEAN NOT NULL DEFAULT 0,
	description TEXT NOT NULL,
	createdAt   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updatedAt   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE user (
	id          INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	facebookId  VARCHAR(50),
	firstname   VARCHAR(50) NOT NULL,
	lastname    VARCHAR(50) NOT NULL,
	admin       BOOLEAN NOT NULL DEFAULT false,
	username    VARCHAR(15) UNIQUE NOT NULL,
	password    TEXT,
	createdAt   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updatedAt   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE favoriteDish (
	id          INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	userId      INT(6) UNSIGNED,
	dishId      INT(6) UNSIGNED,
	createdAt   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updatedAt   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	UNIQUE KEY  `unique_userId_dishId` (`userId`, `dishId`),
	FOREIGN KEY (userId) REFERENCES user(id) ON DELETE CASCADE,
	FOREIGN KEY (dishId) REFERENCES dish(id) ON DELETE CASCADE
);