package utils;

import java.io.*;
import java.sql.*;

public class WineImporter {
	static Connection conn;
	
	//if the string doesn't exist in the table, it gets added
	public static int getId(String name, String table) {
		Statement stmt = null;
		ResultSet rs = null;
		int id = 0;
		
		if(name.equals(""))
			return 0;
		name = name.replace("'", "\\'");
		
		try {
		    stmt = conn.createStatement();
		    rs = stmt.executeQuery("SELECT id FROM "+table+" WHERE name='"+name+"'");
		    
		    if(!rs.next()) {
		    	stmt = conn.createStatement();
			    stmt.executeUpdate("INSERT INTO "+table+" (name) VALUES ('"+name+"')");
			    stmt = conn.createStatement();
			    rs = stmt.executeQuery("SELECT id FROM "+table+" WHERE name='"+name+"'");
			    rs.next();
		    }
		    id = rs.getInt("id");
		}catch(SQLException ex) {
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}
		
		return id;
	}
	
	public static void main(String[] args) {
		BufferedReader in;
		
		try {
			conn = DriverManager.getConnection("jdbc:mysql://localhost/wines", "root", "");
			
			in = Utils.loadTextFile("wineCSV.csv");
			
			String line, name, winery, vintage, country, region, variety, location, date_added, date_drank, size, rating, price, image;
			int loc_num, rack, section, column, row, winery_id, country_id, region_id, variety_id;
			
			in.readLine();	//ignore column headers
			while(in.ready()) {
				line = in.readLine();
				
				name = line.substring(0, line.indexOf(","));
				line = line.substring(line.indexOf(",")+1);
				name = name.replace("'", "\\'");
				
				if(line.substring(0, 1).equals("\"")) {
					winery = line.substring(0, line.indexOf(",", line.lastIndexOf("\"")));
					line = line.substring(line.indexOf(",", line.lastIndexOf("\""))+1);
					winery = winery.substring(1, winery.length()-1);
				}else {
					winery = line.substring(0, line.indexOf(","));
					line = line.substring(line.indexOf(",")+1);
					winery = winery.trim();
				}
				winery_id = getId(winery, "wineries");
				
				vintage = line.substring(0, line.indexOf(","));
				line = line.substring(line.indexOf(",")+1);
				if(vintage.equals(""))
					vintage = "0";
				
				country = line.substring(0, line.indexOf(","));
				line = line.substring(line.indexOf(",")+1);
				if(country.equals("U.S.A."))
					country = "USA";
				country_id = getId(country, "countries");
				
				if(line.substring(0, 1).equals("\"")) {
					region = line.substring(0, line.indexOf(",", line.lastIndexOf("\"")));
					line = line.substring(line.indexOf(",", line.lastIndexOf("\""))+1);
					region = region.substring(1, region.length()-1);
				}else {
					region = line.substring(0, line.indexOf(","));
					line = line.substring(line.indexOf(",")+1);
					region = region.trim();
				}
				region_id = getId(region, "regions");
				
				variety = line.substring(0, line.indexOf(","));
				line = line.substring(line.indexOf(",")+1);
				variety_id = getId(variety, "varieties");
				
				location = line.substring(0, line.indexOf(","));
				line = line.substring(line.indexOf(",")+1);
				
				if(location.length() == 4) {
					rack = Integer.parseInt(location.substring(0, 1));
					section = Integer.parseInt(location.substring(1, 2));
					column = Integer.parseInt(location.substring(2, 3));
					row = Integer.parseInt(location.substring(3, 4));
					
					loc_num = (rack << 24) + (section << 16) + (column << 8) + row;
				}else
					loc_num = 0;
				
				date_added = line.substring(0, line.indexOf(","));
				line = line.substring(line.indexOf(",")+1);
				date_added = date_added.replace('/', '-');
				date_added = date_added.substring(6, 10) + "-" + date_added.substring(0, 5);
				
				rating = line.substring(0, line.indexOf(","));
				line = line.substring(line.indexOf(",")+1);
				
				date_drank = line.substring(0, line.indexOf(","));
				line = line.substring(line.indexOf(",")+1);
				date_drank = date_drank.replace('/', '-');
				if(date_drank.equals(""))
					date_drank = "0000-00-00";
				else
					date_drank = date_drank.substring(6, 10) + "-" + date_drank.substring(0, 5);
				
				size = line.substring(0, line.indexOf(","));
				line = line.substring(line.indexOf(",")+1);
				
				price = line.substring(0, line.indexOf(","));
				line = line.substring(line.indexOf(",")+1);	//
				
				image = line;
				if(!image.equals(""))
					image += ".png";
				
				System.out.println(name+","+winery_id+","+vintage+","+country_id+","+region_id+","+variety_id+","+loc_num+","+date_added+","+date_drank+","+size+","+image);
			
				Statement stmt = null;
				
				String winery_id_string, country_id_string, region_id_string, variety_id_string;
				
				if(winery_id == 0)
					winery_id_string = "NULL";
				else
					winery_id_string = "'"+winery_id+"'";
				
				if(country_id == 0)
					country_id_string = "NULL";
				else
					country_id_string = "'"+country_id+"'";
				
				if(region_id == 0)
					region_id_string = "NULL";
				else
					region_id_string = "'"+region_id+"'";
				
				if(variety_id == 0)
					variety_id_string = "NULL";
				else
					variety_id_string = "'"+variety_id+"'";
				
				try {
				    stmt = conn.createStatement();
				    stmt.executeUpdate("INSERT INTO wines (name, winery_id, vintage, country_id, region_id, variety_id, date_added, date_drank, size, image, cellar_location) VALUES ('"+name+"', "+winery_id_string+", '"+vintage+"', "+country_id_string+", "+region_id_string+", "+variety_id_string+", '"+date_added+"', '"+date_drank+"', '"+size+"', '"+image+"', '"+loc_num+"')");
				}catch(SQLException ex) {
				    // handle any errors
				    System.out.println("SQLException: " + ex.getMessage());
				    System.out.println("SQLState: " + ex.getSQLState());
				    System.out.println("VendorError: " + ex.getErrorCode());
				}
			}
			
			conn.close();
		}catch(IOException ioe) {
			ioe.printStackTrace();
		}catch(SQLException ex) {
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}
	}
}