package main;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.json.JSONException;
import org.json.JSONObject;

/**
 *
 * @author s129778
 */
public class Post implements Runnable{
	Thread runner;
        JSONObject tweet;
        JSONObject twuser;
        
        
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        PreparedStatement pst = null;
	String url = "jdbc:postgresql://131.155.240.72:5432/Twitter";
        String user = "postgres";
        String password = "2IOC02";
	public Post() {
	}
        public void start(){
            runner.start();
        }
        public Post(JSONObject tweet, JSONObject user) {
            //create new tread
            
            runner = new Thread(this, "post");
            this.twuser = user;
            this.tweet = tweet;
	}
	
	
	public void run() {
        try {
            con = DriverManager.getConnection(url, user, password);
            st = con.createStatement();
            rs = st.executeQuery("SELECT VERSION()");
              
            //st.executeUpdate("CREATE TABLE name (ID INT)");
            //st.executeUpdate("DROP TABLE name");
            //st.executeQuery("SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'name'  AND pid <> pg_backend_pid();");
            //st.executeUpdate("DROP DATABASE name");
            //st.executeUpdate("CREATE ROLE joe2 PASSWORD 'ok' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;");
            //select query
            String query = "SELECT * FROM tweet WHERE tweetid<=9000000 ORDER BY tweetid";
            pst = con.prepareStatement(query);
            rs = pst.executeQuery();
            //process select query
            while ( rs.next() ) {
                int id = rs.getInt("tweetid");
                System.out.println( "ID = " + id );
                System.out.println (rs.getTimestamp("createdat"));
            }
            
            //insert query with check for existance
            //for(int i=0;i<900000;i++){
            String update = "INSERT INTO tweet (tweetid,createdat,favcount,retweetcount,text) SELECT ? , ? ,?,?, ? WHERE NOT EXISTS (SELECT * FROM tweet WHERE tweetid= ? )";
            pst = con.prepareStatement(update);
            pst.setLong(1, tweet.getLong("id"));
            pst.setString(2, tweet.getString("created_at"));
            pst.setLong(3, tweet.getLong("favorite_count"));
            pst.setLong(4, tweet.getLong("retweet_count"));
            pst.setString(5, tweet.getString("text"));
            pst.setLong(6, tweet.getLong("id"));
            pst.executeUpdate();
            
            
            
            update = "INSERT INTO ispostedby (tweetid,userid) SELECT ? , ? WHERE NOT EXISTS (SELECT * FROM ispostedby WHERE tweetid= ? )";
            pst = con.prepareStatement(update);
            pst.setLong(1, tweet.getLong("id"));
            pst.setLong(2, twuser.getLong("id"));
            pst.setLong(3, tweet.getLong("id"));
            pst.executeUpdate();
            
            //update = "INSERT INTO twitteruser (userid,displayname,timezone,tweetcount,followercount,followedcount,location,brand) Select 1";
            update = "INSERT INTO twitteruser (userid,displayname,timezone,tweetcount,followercount,followedcount,location) SELECT  ? ,? ,? ,? , ?, ? ,? WHERE NOT EXISTS (SELECT * FROM twitteruser WHERE userid= ? )";
            pst = con.prepareStatement(update);
            pst.setLong(1, twuser.getLong("id"));
            pst.setString(2, twuser.getString("name"));
            pst.setString(3, twuser.getString("time_zone"));
            pst.setLong(4, twuser.getLong("statuses_count"));
            pst.setLong(5, twuser.getLong("followers_count"));
            pst.setLong(6, twuser.getLong("friends_count"));
            pst.setString(7, twuser.getString("location"));
            pst.setLong(8, twuser.getLong("id"));
            pst.executeUpdate();
            
            ArrayList<String> brands=getBrands();
            for(String brand : brands){
                update = "INSERT INTO likesbrand (userid,brand) SELECT ? , ? WHERE NOT EXISTS (SELECT * FROM likesbrand WHERE userid= ? AND brand = ?)";
                pst = con.prepareStatement(update);

                pst.setLong(1, twuser.getLong("id"));
                pst.setString(2, brand);
                pst.setLong(3, twuser.getLong("id"));
                pst.setString(4, brand);
                pst.executeUpdate();
            }
            //}
            //update 
            //pst = con.prepareStatement("UPDATE tweet set text = 'j' where tweetid=1;");
            //pst.executeUpdate();
            
            //delete
            //pst = con.prepareStatement("DELETE from tweet where tweetid=1;");
            //pst.executeUpdate();
            
            
               
            
            
            
           

        } catch (JSONException ex) {
            Logger.getLogger(Post.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(Main.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(Main.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    
        
        }
	
	ArrayList<String> getBrands(){
            ArrayList<String> result= new ArrayList<String>();
            String text=null;
        try {
            text = tweet.getString("text");
            text = text.toLowerCase();
        } catch (JSONException ex) {
            Logger.getLogger(Post.class.getName()).log(Level.SEVERE, null, ex);
        }
            if(text.contains("samsung")||text.contains("galaxy")){
                result.add("Samsung");
                System.out.println("samsung");
            } else {
                result.add("geen");
                System.out.println(text);
            }
            return result;
        }
}