/*
 * Main.java
 *
 * This program takes AOL search data as a space seperated list and imports
 * it into a database
 *
 *
 * Current Table setup:
 *
 * Name: data
 *
 * id - bigint, auto_increment
 * user_id - bigint
 * search_words - blob
 * time - Timestamp
 * click_rank - int
 * click_url - blob
 *
 *
 */

package com.andamp.aoldataimport;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;

/**
 *
 * @author Ralthor
 */
public class Main {
    
    /** Creates a new instance of Main */
    public Main() {
    }

    
    
    /* FILL IN PASSWORD AND USERNAME HERE */
    private static String USERNAME = "USERNAME";
    private static String PASSWORD = "PASSWORD";
    private static String URL = "jdbc:mysql://localhost/test";
    
    
    public static void main(String[] args) {
        if(args.length == 0) {
            usage();
            System.exit(1);
        }
        String Filename = args[0];
        System.out.println("Attempting to use file " + Filename);
        BufferedReader input = null;
        try {
            input = new BufferedReader(new FileReader(Filename));
        }
        catch(FileNotFoundException e) {
            System.err.println("File Not found, " + Filename + "\n" + e.toString());
        }
        
        int skipLines = 0;
        if(args.length > 1)
            skipLines = Integer.parseInt(args[1]);
            
        

        //Attempt database connection
        System.out.println("File found, attempting database conenction...");

        Connection conn = null;
        
        try
        {
            Class.forName ("com.mysql.jdbc.Driver").newInstance ();
            conn = DriverManager.getConnection(URL);
            
            //Uncomment for password protected database
            //conn = DriverManager.getConnection (URL, USERNAME, PASSWORD);
            System.out.println ("Database connection established");
        }
        catch (Exception e)
        {
            System.err.println ("Cannot connect to database server:\n" + e);
             System.exit(1);
        }
        
        
        
        
        
        int count;
        String inputLine = null;
        Statement s = null;
        try {
            s = conn.createStatement();
            if(skipLines == 0) {
                inputLine = input.readLine();   //First line is header we will skip it
                
            }
            else {
                for(int i =0; i < skipLines; i++) {
                    input.readLine();
                }
                
            }
            inputLine = input.readLine();
                
        }
        catch(IOException e) {
            System.out.println("Error reading file: " + e);
             System.exit(1);
        }
        catch(SQLException e) {
            System.out.println("Error creating statement " + e);
            System.exit(1);
        }
        String Query = null;
        int total = skipLines;
        while(inputLine != null) {

            try {
                
                String []tokens = inputLine.split("\t");
          
                
                if(tokens.length < 3 || tokens.length > 5) {
                    System.err.println("Should have gotten at least 3 tokens, but no more than 5, but didn't. ignoring line " + total);
                    inputLine = input.readLine();
                    continue;
                }
                
                try {
                
                int userID = Integer.parseInt(tokens[0]);
                String searchWords = tokens[1];
                Timestamp dateTime = java.sql.Timestamp.valueOf(tokens[2]);
                int clickRank  = -1;
                String clickUrl = "NONE";
                
                searchWords = searchWords.replaceAll("'", "''");
                
                
                
                if(tokens.length > 4) {
                    clickRank = Integer.parseInt(tokens[3]);
                    clickUrl = tokens[4];
                    clickUrl = clickUrl.replaceAll("'", "''");
                }
                
               
                Query = "INSERT INTO data (user_id, search_words, time, click_rank, click_url)"
                        + " VALUES('" + userID + "', '" + searchWords + "', '" + dateTime + "', '" + clickRank
                        + "', '" + clickUrl + "')";
                count = s.executeUpdate(Query);
                
                total += count;
                
                }
                catch(Exception e) {
                    System.out.println("Received Exception for total: " + total + "\n last query was: " + Query + "\nException: " + e);
                }
                inputLine = input.readLine();
                
            } catch(IOException e) {
                System.out.println("Error reading file, line " + total + ", error: " + e);
                System.exit(1);
            }
            
        }
        
        System.out.println("Imported " + total + " lines");

        //disconnect from db
        if (conn != null)
        {
            try
            {
                s.close();
                conn.close ();
                System.out.println ("Database connection terminated");
            }
            catch (Exception e) { /* ignore close errors */ }
        }

    }
    
    public static void usage() {
        System.out.println("Usage:\taoldataimport filename [skipLines]");
        System.out.println("\tfilename - the location of the file to import from");
        System.out.println("\tskiplines - Number of lines to skip in file (optional)");
    }
         
}