/*First create the database AWARDS using the following command or via GUI, then connect to the database*/
/*CREATE DATABASE AWARDS;*/

/*Execute the following commands in AWARDS Database*/

/*Copy the files Category.csv, Movie.csv, Academy_Awards_Temp.csv (produced by R_Code_To_Cleanup) 
to the folder which is accessible to this code. Make sure that you give all permissions on the three files:

MOVIE.csv
CATEGORY.csv
NOMINATIONS.csv

*/

DROP TABLE IF EXISTS MOVIE CASCADE;


CREATE TABLE MOVIE(MOVIE_ID SERIAL CONSTRAINT MOVIE_PK1 PRIMARY KEY,
MOVIE_NAME VARCHAR(300) NOT NULL,
YEAR INTEGER NOT NULL
);


DROP TABLE IF EXISTS CATEGORY CASCADE;

CREATE TABLE CATEGORY(CATEGORY_ID SERIAL CONSTRAINT CATEGORY_PK1 PRIMARY KEY,
CATEGORY_NAME VARCHAR(300) NOT NULL
);

DROP TABLE IF EXISTS NOMINATIONS CASCADE;

/*Some movies are nominated for the same category multiple times in the same year, like Actress - Supporting Role of 'The Last Picture Show' movie.
Hence to distinguish such rows in NOMINATIONS Table, the column NOMINATION_NUMBER is added to the nominations table (given below)*/

CREATE TABLE NOMINATIONS(MOVIE_ID INTEGER NOT NULL,
CATEGORY_ID INTEGER NOT NULL,
NOMINATION_NUMBER INTEGER NOT NULL,
WON INTEGER NOT NULL CONSTRAINT NOMINATIONS_CHK1 CHECK (WON IN (0,1)),
CONSTRAINT NOMINATIONS_PK PRIMARY KEY (MOVIE_ID, CATEGORY_ID, NOMINATION_NUMBER), 
   CONSTRAINT NOMINATIONS_FK1 FOREIGN KEY (MOVIE_ID) REFERENCES MOVIE(MOVIE_ID),
   CONSTRAINT NOMINATIONS_FK2 FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY(CATEGORY_ID)   
   );



/*Please modify the directory names in the following three commands where you copied the CSV files (CATEGORY.csv, MOVIE.csv, and NOMINATIONS.csv*/

COPY CATEGORY(CATEGORY_NAME) FROM 'C:\Users\Sekhar\Documents\Github\Project_3_607_My_Work\CATEGORY.csv' with DELIMITER ',' CSV HEADER;

COPY MOVIE(MOVIE_NAME, YEAR) FROM 'C:\Users\Sekhar\Documents\Github\Project_3_607_My_Work\MOVIE.csv' with DELIMITER ',' encoding 'LATIN1' CSV HEADER;

COPY NOMINATIONS(MOVIE_ID, CATEGORY_ID,NOMINATION_NUMBER,WON) FROM 'C:\Users\Sekhar\Documents\Github\Project_3_607_My_Work\NOMINATIONS.csv' with DELIMITER ',' encoding 'LATIN1' CSV HEADER;

SELECT * FROM CATEGORY;
SELECT * FROM MOVIE where year = 2010 order by movie_name ;
SELECT * FROM NOMINATIONS;


/*Simple query to get the Movies, winners, nominees etc. */
COPY (SELECT A.MOVIE_ID MOVIE_ID, A.MOVIE_NAME MOVIE_NAME, A.YEAR AS YEAR, B.CATEGORY_ID CATEGORY_ID,B.CATEGORY_NAME CATEGORY_NAME, C.WON WON
FROM MOVIE A, CATEGORY B, NOMINATIONS C
WHERE A.MOVIE_ID = C.MOVIE_ID AND B.CATEGORY_ID = C.CATEGORY_ID
) TO 'C:\Users\Sekhar\Documents\Github\Project_3_607_My_Work\Awards_File.csv' WITH  CSV HEADER;

SELECT A.MOVIE_ID, A.MOVIE_NAME, A.YEAR, B.CATEGORY_ID,B.CATEGORY_NAME, C.WON
FROM MOVIE A, CATEGORY B, NOMINATIONS C
WHERE A.MOVIE_ID = C.MOVIE_ID AND B.CATEGORY_ID = C.CATEGORY_ID;