/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package bigdatamangementcs1;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 *
 * @author cyl1
 */
public class getmovies2 {

    public getmovies2() {
        int first = 0;
        int tempid = 0;

        try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection conn = DriverManager.getConnection("jdbc:mysql://mysql-server-1.macs.hw.ac.uk/movielens?user=cyl1&password=abccyl1354");
			Statement st = conn.createStatement();
			String query = "SELECT movies.id, movies.title, movies.release_date, "
                                + "movies.video, movies.IMDBURL, ratings.user, ratings.rating, "
                                + "ratings.timestamp  FROM movies INNER JOIN ratings ON movies.id = ratings.movie";
                        ResultSet rs = st.executeQuery(query);

                        String json = "";

                        File file = new File("test.json");
                        if (!file.exists()){
                            file.createNewFile();
                        } else {
                            file.delete();
                            file.createNewFile();
                        }

                        FileWriter fw = new FileWriter(file.getAbsoluteFile());
                        BufferedWriter bw = new BufferedWriter(fw);


                        while(rs.next()){


                            int id = rs.getInt("id");
                            String title = "\""+rs.getString("title")+"\"";
                            String release_date ="\""+ rs.getString("release_date")+"\"";
                            String video = "\""+rs.getString("video")+"\"";
                            String IMDBURL = "\""+rs.getString("IMDBURL")+"\"";
                            int user = rs.getInt("user");
                            int rating = rs.getInt("rating");
                            String timestamp = "\""+rs.getString("timestamp")+"\"";
                            //System.out.format("%s %s\n", id,genre);



                            if (first == 0) {
                                json = "{\"id\" :"+id+", "
                                    + "\"title\" :"+title+", "
                                    + "\"release_date\" :"+release_date+", "
                                    + "\"video\" :"+video+", "
                                    + "\"IMDBURL\" :"+IMDBURL+", "
                                    + "ratings :["+"{\"user\" :"+user+", "
                                    + "\"rating\" :"+rating+", "
                                    + "\"timestamp\" :"+timestamp+"}]}";
                                first = 1;
                                tempid = id;
                                continue;
                            } else if (id==tempid & first==1) {
                                json = json.replace("]}", ", ");
                                json += "{\"user\" :"+user+", "
                                    + "\"rating\" :"+rating+", "
                                    + "\"timestamp\" :"+timestamp+"}]}";
                            } else if (!(id ==tempid) & first==1) {
                                json = "\n{\"id\" :"+id+", "
                                    + "\"title\" :"+title+", "
                                    + "\"release_date\" :"+release_date+", "
                                    + "\"video\" :"+video+", "
                                    + "\"IMDBURL\" :"+IMDBURL+", "
                                    + "ratings :["+"{\"user\" :"+user+", "
                                    + "\"rating\" :"+rating+", "
                                    + "\"timestamp\" :"+timestamp+"}]}";
                            }


                            bw.write(json);
                            tempid = id;



                        }
                        bw.close();
			st.close();
			conn.close();
                        System.out.println("test.json saved");
		} catch (Exception e) {
			System.out.println("ERROR!! " +e);
                }
}}