table.sql

--CREATE TABLE FOR USERS
CREATE TABLE USERS(
USERID INT NOT NULL PRIMARY KEY,
USERNAME VARCHAR(20) UNIQUE NOT NULL,
PASSWORD VARCHAR(20) NOT NULL,
FISRTNAME VARCHAR(20) NOT NULL,
LASTNAME VARCHAR(20) NOT NULL,
EMAIL VARCHAR(50) NOT NULL,
CREATIONDATE DATE NOT NULL,
SECURITYLEVEL INT NOT NULL,
IP VARCHAR(20) NOT NULL,
LOCKED CHAR CHECK(LOCKED IN('N','Y')),
LOGGED CHAR CHECK(LOGGED IN('N','Y')),
PAIDUNTILDATE DATE,
LASTLOGIN DATE,
COINS INT DEFAULT 0,
LIKES INT DEFAULT 0,
AVATAR VARCHAR(100) NOT NULL,
HOMEPAGE VARCHAR(100) NOT NULL
);
--CREATE TABLE FOR MOVIES
CREATE TABLE MOVIES(
MOVIEID INT NOT NULL PRIMARY KEY,
NAME VARCHAR(50) UNIQUE NOT NULL,
DESCRIPTION VARCHAR(1000) NOT NULL,
RELEASEDATE DATE NOT NULL,
STUDIO VARCHAR(50) NOT NULL,
DIRECTOR INT NOT NULL,
AGERATING INT NOT NULL,
AVGRATING FLOAT,
OVERALLRANK INT,
IMAGE VARCHAR(50) NOT NULL,
WEBSITE VARCHAR(50) NOT NULL,
RUNNINGTIME INT NOT NULL
);
--CREATE TABLE ALLOWING MULTIPLE GENRE AND RANKING WITH GENRE
CREATE TABLE GENRE(
MOVIEID INT NOT NULL,
GENRE VARCHAR(20) NOT NULL,
RANK INT NOT NULL,
CONSTRAINT FORGENRE_MOVIE FOREIGN KEY(MOVIEID) REFERENCES MOVIES(MOVIEID) ON DELETE CASCADE 
);
--CREATE TABLE FOR ACTORS
CREATE TABLE ACTORS(
ACTORID INT NOT NULL PRIMARY KEY,
NAME VARCHAR(20) NOT NULL
);
--CREATE TABLE REPRESENTING CASTING LIST
CREATE TABLE CASTING(
MOVIEID INT NOT NULL,
ACTORID INT NOT NULL,
CONSTRAINT FORCASTING_MOVIE FOREIGN KEY(MOVIEID) REFERENCES MOVIES(MOVIEID) ON DELETE CASCADE,
CONSTRAINT FORCASTING_ACTOR FOREIGN KEY(ACTORID) REFERENCES ACTORS(ACTORID) ON DELETE CASCADE
);
--CREATE TABLE FOR COLLECTION
CREATE TABLE COLLECTION(
USERID INT NOT NULL,
MOVIEID INT NOT NULL,
RATING INT CHECK(RATING<=10 AND RATING>=0),
COMMENTS VARCHAR(1000),
OWN CHAR CHECK(OWN IN('Y','N')),
WISHLIST CHAR CHECK(WISHLIST IN('Y','N')),
WATCHED CHAR CHECK(WATCHED IN('Y','N')),
CONSTRAINT FORCOLLECTION_USER FOREIGN KEY(USERID) REFERENCES USERS(USERID) ON DELETE CASCADE,
CONSTRAINT FORCOLLECTION_MOVIE FOREIGN KEY(MOVIEID) REFERENCES MOVIES(MOVIEID) ON DELETE CASCADE
);
--CREATE TABLE FOR FRIEND LIST
CREATE TABLE FRIEND(
USERID INT NOT NULL,
FRIENDID INT NOT NULL,
CONSTRAINT FORFRIEND_USER FOREIGN KEY(USERID) REFERENCES USERS(USERID) ON DELETE CASCADE,
CONSTRAINT FORFRIEND FOREIGN KEY(FRIENDID) REFERENCES USERS(USERID) ON DELETE CASCADE
);
--CREATE TABLE FOR PERSONAL LISTS
CREATE TABLE LISTS(
LISTID INT NOT NULL PRIMARY KEY,
USERID INT NOT NULL,
NAME VARCHAR(50) NOT NULL,
DESCRIPTION VARCHAR(1000),
IMAGE VARCHAR(100),
LIKES INT DEFAULT 0,
TIP INT DEFAULT 0,
CONSTRAINT FORLISTS_USER FOREIGN KEY(USERID) REFERENCES USERS(USERID) ON DELETE CASCADE
);
--CREATE TABLE FOR LIST CONTAINER
CREATE TABLE LISTITEM(
LISTID INT NOT NULL,
MOVIEID INT NOT NULL,
POSITION INT NOT NULL,
COMMENTS VARCHAR(1000),
LIKES INT DEFAULT 0,
TIP INT DEFAULT 0,
IMAGE VARCHAR(100),
CONSTRAINT FORCONTAINER_LIST FOREIGN KEY(LISTID) REFERENCES LISTS(LISTID) ON DELETE CASCADE,
CONSTRAINT FORCONTAINER_MOVIE FOREIGN KEY(MOVIEID) REFERENCES MOVIES(MOVIEID) ON DELETE CASCADE
);
--CREATE TABLE FOR POST
CREATE TABLE POST(
POSTID INT NOT NULL PRIMARY KEY,
USERNAME VARCHAR(50) NOT NULL,
TITLE VARCHAR(100) NOT NULL,
POSTDATE DATE NOT NULL,
TEXT VARCHAR(1000) NOT NULL,
LIKES INT DEFAULT 0,
TIP INT DEFAULT 0,
LOCKED CHAR CHECK(LOCKED IN ('Y','N')),
MODERATION CHAR CHECK(MODERATION IN ('Y','N')),
THREADID INT NOT NULL,
CONSTRAINT FORPOST_USER FOREIGN KEY(USERNAME) REFERENCES USERS(USERNAME) ON DELETE CASCADE
);
--CREATE TABLE FOR SUBSCRIPTION
CREATE TABLE SUBSCRIPTION(
USERID INT NOT NULL,
MOVIEID INT,
LISTID INT,
THREADID INT,
CONSTRAINT FORSUBSCRIPTION_USER FOREIGN KEY(USERID) REFERENCES USERS(USERID) ON DELETE CASCADE,
CONSTRAINT FORSUBSCRIPTION_MOVIE FOREIGN KEY(MOVIEID) REFERENCES MOVIES(MOVIEID) ON DELETE CASCADE,
CONSTRAINT FORSUBSCRIPTION_LIST FOREIGN KEY(LISTID) REFERENCES LISTS(LISTID) ON DELETE CASCADE
);
--CREATE TABLE FOR RATINGS
CREATE TABLE RATINGS(
USERID INT NOT NULL,
MOVIEID INT NOT NULL,
VALUE INT CHECK(VALUE<=10 AND VALUE>=0) NOT NULL,
CONSTRAINT FORRATINGS_USER FOREIGN KEY(USERID) REFERENCES USERS(USERID) ON DELETE CASCADE,
CONSTRAINT FORRATINGS_MOVIE FOREIGN KEY(MOVIEID) REFERENCES MOVIES(MOVIEID) ON DELETE CASCADE
);
--CREATE TABLE FOR COIN TRANSACTION
CREATE TABLE COINTRANSACTION(
ID INT NOT NULL PRIMARY KEY,
GIVER INT NOT NULL,
RECEIVER INT NOT NULL,
REASON VARCHAR(500),
AMOUNT INT NOT NULL,
CONSTRAINT FORCOIN_FROM FOREIGN KEY(GIVER) REFERENCES USERS(USERID) ON DELETE CASCADE,
CONSTRAINT FORCOIN_TO FOREIGN KEY(RECEIVER) REFERENCES USERS(USERID) ON DELETE CASCADE
);
--CREATE TABLE FOR THUMBS
CREATE TABLE THUMBS(
USERID INT NOT NULL,
MOVIEID INT NOT NULL,
REASON VARCHAR(1000),
CONSTRAINT FORTHUMB_USER FOREIGN KEY(USERID) REFERENCES USERS(USERID) ON DELETE CASCADE,
CONSTRAINT FORTHUMB_MOVIE FOREIGN KEY(MOVIEID) REFERENCES MOVIES(MOVIEID) ON DELETE CASCADE
);
--CREATE NEW TABLE FOR MESSAGE
CREATE TABLE MESSAGE(
TEXT VARCHAR(1000) NOT NULL,
SENDER INT NOT NULL,
RECEIVER INT NOT NULL,
SENDTIME TIMESTAMP NOT NULL,
READ CHAR CHECK(READ IN 'Y','N'),
CONSTRAINT FORMESSAGESENDER FOREIGN KEY(SENDER) REFERENCES USERS(USERID) ON DELETE CASCADE,
CONSTRAINT FORMESSAGERECEIVER FOREIGN KEY(SENDER) REFERENCES USERS(USERID) ON DELETE CASCADE
);
--CREATE TABLE FOR BAD NAMES
CREATE TABLE BADNAMES(
BADNAME VARCHAR(20)
);
--CREATE TABLE FOR MOVIES WAITING TO BE VALIDATED
CREATE TABLE VALIDATEMOVIE(
--NO MOVIEID
NAME VARCHAR(50) NOT NULL,
DESCRIPTION VARCHAR(1000) NOT NULL,
RELEASEDATE DATE NOT NULL, -- Couldn't get date format to work
STUDIO VARCHAR(50) NOT NULL,
DIRECTOR VARCHAR(50) NOT NULL,
AGERATING INT NOT NULL,
--NO AVGRATING
--NO OVERALLRANK
IMAGE VARCHAR(50) NOT NULL,
WEBSITE VARCHAR(50) NOT NULL,
RUNNINGTIME INT NOT NULL,
VALID CHAR DEFAULT 'N'
);