DROP DATABASE IF EXISTS cs336g32;
CREATE DATABASE cs336g32;
USE cs336g32;

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS rideOffers;
DROP TABLE IF EXISTS rides;
DROP TABLE IF EXISTS rating;
DROP TABLE IF EXISTS messages;

CREATE TABLE users(
	username	VARCHAR(20) UNIQUE NOT NULL,
    password	VARCHAR(50) NOT NULL,
    email 		VARCHAR(30) UNIQUE NOT NULL,
    name		VARCHAR(20),
    address		VARCHAR(50),
    phone		VARCHAR(16),
    carDesc		VARCHAR(20) DEFAULT "None",
    carNumPassengers	INT DEFAULT "0",
    type		VARCHAR(5) DEFAULT "user",
    PRIMARY KEY (username)
);

CREATE TABLE rideOffers(
	rideId INT UNIQUE NOT NULL AUTO_INCREMENT,
	driver VARCHAR(20) NOT NULL,
    carDesc VARCHAR(20) NOT NULL,
    departure VARCHAR(20) NOT NULL,
    destination VARCHAR(20) NOT NULL,
    timeWindow VARCHAR(20) NOT NULL,
    recurring BOOL NOT NULL DEFAULT "0",
    PRIMARY KEY(rideId),
    FOREIGN KEY(driver) REFERENCES users (username) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE rideRequests(
	rideId INT UNIQUE NOT NULL AUTO_INCREMENT,
    rider VARCHAR(20) NOT NULL,
    departure VARCHAR(20) NOT NULL,
    destination VARCHAR(20) NOT NULL,
    timeWindow VARCHAR(20) NOT NULL,
    recurring BOOL NOT NULL DEFAULT "0",
    PRIMARY KEY(rideId),
	FOREIGN KEY(rider) REFERENCES users (username) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE rides(
	rideId INT NOT NULL,
	driver VARCHAR(20) NOT NULL,
    carDesc VARCHAR(20) NOT NULL,
    date DATETIME NOT NULL DEFAULT current_timestamp,
    PRIMARY KEY(rideId),
    FOREIGN KEY(rideId) REFERENCES rideRequests (rideId) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(driver) REFERENCES users (username) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE rating(
	userRated VARCHAR(20) NOT NULL,
    userRater VARCHAR(20) NOT NULL,
	rideId INT NOT NULL,
	value INT NOT NULL,
    comment TEXT,
    PRIMARY KEY(rideId, userRated, userRater),
    FOREIGN KEY(rideId) REFERENCES rideOffers (rideId) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(userRated) REFERENCES users (username) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(userRater) REFERENCES users (username) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE messages(
	toUser VARCHAR(20) NOT NULL,
    fromUser VARCHAR(20) NOT NULL,
    body TEXT NOT NULL,
    FOREIGN KEY(toUser) REFERENCES users (username) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(fromUser) REFERENCES users (username) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(toUser, fromUser)
);

INSERT INTO users(username, password, email, name, address, phone, carDesc, carNumPassengers, type)
	VALUES ("admin", "password", "admin@rideme.com", "Administrator", "The Matrix", "1234567890", "UFO", "0", "admin");
INSERT INTO users(username, password, email, name, address, phone, carDesc, carNumPassengers, type)
	VALUES ("support", "support", "support@hotmail.com", "Renard Tumbokon", "The Sub-Matrix", "9081234567", "Space ship", "0", "staff");
INSERT INTO users(username, password, email, name, address, phone, carDesc, carNumPassengers, type)
	VALUES ("renard", "renard", "renard@hotmail.com", "Renard Tumbokon", "302 Cranford Ave", "9081234567", "Small ass car", "1", "user");
INSERT INTO users(username, password, email, name, address, phone, carDesc, carNumPassengers, type)
	VALUES ("nick", "nick", "nick@hotmail.com", "Nick Prezioso", "10 Concord Dr", "4165430986", "Big ass truck", "4", "user");