package de.mpiwg.gazetteer.scripts;

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.util.HashMap;
import java.util.Map;



/**
 * 
 * mvn exec:java -Dexec.mainClass="de.mpiwg.gazetteer.scripts.SectionsIndex" -Dexec.args="Gazetteer root admin"
 * 
 * @author jurzua
 *
 */
public class SectionsIndex {

	private static String TABLE_NAME = "sections_index";
	
	private static String DROP_TABLE = 
			"DROP TABLE IF EXISTS `"+ TABLE_NAME +"`;";
	
	private static String CREATE_TABLE = 
			"CREATE TABLE `" + TABLE_NAME + "` (`id` int(10) NOT NULL AUTO_INCREMENT, "
			+ "`name` varchar(255) NOT NULL, "
			+ "`books_id` varchar(5) NOT NULL, "
			+ "`section_after` varchar(255) NOT NULL, "
			+ "`start_page` int(5) NOT NULL, `end_page` int(5) NOT NULL, "
			+ "`level` int(5) NOT NULL, "
			+ "`split_from` int(10) DEFAULT NULL, PRIMARY KEY (`id`) ) "
			+ "ENGINE=MyISAM DEFAULT CHARSET=utf8;";
	
	private static String SELECT_SECTIONS = 
			"SELECT * FROM sections";
	
	private static String SECTIONS_COUNT = 
			"SELECT count(*) FROM sections";
	
	private static String SELECT_SECTIONS_VERSIONS = 
			"SELECT * from sections_versions";
	
	
	private static void execute(String dbName, String dbUser, String dbPwd){
		
		Connection conn = null;
		
		
		try {
			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			String dbURL = "jdbc:mysql://localhost/"+ dbName +"?characterEncoding=UTF-8";
			conn = DriverManager.getConnection(dbURL, dbUser, dbPwd);
			
			createTable(conn);
			copyFromSectionsTable(conn);
			replaceFromSectionsRevisions(conn);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private static void replaceFromSectionsRevisions(Connection conn){
		Statement stmt = null;
		ResultSet rs = null;
		try {
		
			stmt = conn.createStatement();
			rs = stmt.executeQuery(SELECT_SECTIONS_VERSIONS);
			
			Map<String, SectionVersion> mapVersions = new HashMap<String, SectionsIndex.SectionVersion>();
			
			while(rs.next()){
				SectionVersion version = new SectionVersion(rs);
				if(!mapVersions.containsKey(version.books_id)){
					mapVersions.put(version.books_id, version);
				}else if(version.version > mapVersions.get(version.books_id).version){
					mapVersions.put(version.books_id, version);
				}
			}
			
			for(SectionVersion sectionVersion : mapVersions.values()){
				
				String query = "SELECT * FROM sections_revisions WHERE "
						+ "books_id = '"+ sectionVersion.books_id +"' AND "
						+ "versions_id = " + sectionVersion.id + " AND "
						+ "deleted = 0";
				stmt = conn.createStatement();
				rs = stmt.executeQuery(query);
				
				while(rs.next()){
					Section section = new Section(rs, false);
					
					if(section.deleted == 0){
						if(section.id == -1){
							
							PreparedStatement stmt0 = section.getInsertStatementWithoutId(conn);
							stmt0.executeUpdate();
							ResultSet rs0 = stmt0.getGeneratedKeys();
						    rs0.next();
						    int newSectionId = rs0.getInt(1); 
							
							//Updating sections_revisions (from -1 to new id)
						    String sqlUpdateRevision = "UPDATE sections_revisions SET sections_id = ? WHERE id = ?";
						    PreparedStatement stmt1 = conn.prepareStatement(sqlUpdateRevision);
						    stmt1.setInt(1, newSectionId);
						    stmt1.setInt(2, section.sectionsRevisionsId);
						    int rowsUpdated = stmt1.executeUpdate();
						    
						    System.out.println("Changing revision section with id: " + section.sectionsRevisionsId + " from -1 to " + newSectionId + ". Rows updated: " + rowsUpdated);
						    //System.out.print("#");
						}else{
							PreparedStatement stm = section.getUpdateStatement(conn);
							stm.execute();
						}						
					}
				}
			}
			
			System.out.println();
			
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private static void copyFromSectionsTable(Connection conn){
		Statement stmt = null;
		ResultSet rs = null;
		try {
			/*
			stmt = conn.createStatement();
			rs = stmt.executeQuery(SECTIONS_COUNT);
			rs.next();
			int count = rs.getInt(1);
			*/
			stmt = conn.createStatement();
			rs = stmt.executeQuery(SELECT_SECTIONS);
			
			int index = 0;
			while(rs.next()){
				Section section = new Section(rs, true);
				
				//stmt = conn.createStatement();
				//stmt.executeUpdate(section.getInsertStatement());
				PreparedStatement stm = section.getInsertStatementWithId(conn);
				stm.execute();
				
				if(index % 100 == 0){
					System.out.print("*");
				}
				index++;
			}
			System.out.println();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	private static void createTable(Connection conn){
		Statement stmt = null;
		
		try {
			stmt = conn.createStatement();
			int rows = stmt.executeUpdate(DROP_TABLE);
			System.out.println("DROP table: " + rows);
			
		} catch (Exception e) {
			System.err.println("\n" + DROP_TABLE);
			e.printStackTrace();
		}
		
		try {
			stmt = conn.createStatement();
			int rows = stmt.executeUpdate(CREATE_TABLE);
			System.out.println("CREATE table: " + rows);
		} catch (Exception e) {
			System.err.println("\n" + CREATE_TABLE);
			e.printStackTrace();
		}
	}
	
	
	private static void printSetting(){
        
       int mb = 1024*1024;
        
       //Getting the runtime reference from system
       Runtime runtime = Runtime.getRuntime();
        
       System.out.println("##### Heap utilization statistics [MB] #####");
        
       //Print used memory
       System.out.println("Used Memory:"
           + (runtime.totalMemory() - runtime.freeMemory()) / mb);

       //Print free memory
       System.out.println("Free Memory:"
           + runtime.freeMemory() / mb);
        
       //Print total available memory
       System.out.println("Total Memory:" + runtime.totalMemory() / mb);

       //Print Maximum available memory
       System.out.println("Max Memory:" + runtime.maxMemory() / mb);
   }
	
	public static void main(String[] args){
		
		printSetting();
		
		String dbName = args[0];
		String dbUser = args[1];
		String dbPwd = args[2];
		
		SectionsIndex.execute(dbName, dbUser, dbPwd);
		
		System.exit(0);
	} 
	//table: sections_revisions
	private static class SectionVersion{
		
		private int id;
		private int version;
		private String editor;
		private String books_id;
		
		public SectionVersion(ResultSet rs) throws SQLException{
			this.id = rs.getInt("id");
			this.version = rs.getInt("version");
			this.editor = rs.getString("editor");
			this.books_id = rs.getString("books_id");
		}
		
		public int getVersion(){
			return this.version;
		}
	}
	
	private static class Section{
		private int id;
		private String name;
		private String books_id;
		private String section_after;
		private int start_page;
		private int end_page;
		private int level;
		private int split_from;
		//private int sections_id;
		
		//tables for the revisions_sections
		private int sectionsRevisionsId;
		private int deleted;
		
		public Section(ResultSet rs, boolean isSectionTable) throws SQLException{
			
			this.name = rs.getString("name");
			this.books_id = rs.getString("books_id");
			this.section_after = rs.getString("section_after");
			this.start_page = rs.getInt("start_page");
			this.end_page = rs.getInt("end_page");
			this.level = rs.getInt("level");
			this.split_from = rs.getInt("split_from");
			if(isSectionTable){
				this.id = rs.getInt("id");
			}else{
				this.id = rs.getInt("sections_id");
				this.sectionsRevisionsId = rs.getInt("id");
				this.deleted = rs.getInt("deleted");
			}
		}
		
		public PreparedStatement getInsertStatementWithId(Connection conn) throws SQLException{
			
			String sql = "INSERT INTO " + TABLE_NAME + " " + 
					"(id, name, books_id, section_after, start_page, end_page, level, split_from) VALUES" +
					"(?,?,?,?,?,?,?,?)";
			PreparedStatement stm = conn.prepareStatement(sql);
			
			stm.setInt(1, id);
			stm.setString(2, name);
			stm.setString(3, books_id);
			stm.setString(4, section_after);
			stm.setInt(5, start_page);
			stm.setInt(6, end_page);
			stm.setInt(7, level);
			stm.setInt(8, split_from);
			
			return stm;
		}
		
		public PreparedStatement getInsertStatementWithoutId(Connection conn) throws SQLException{
		
			String sql = "INSERT INTO " + TABLE_NAME + " " + 
					"(name, books_id, section_after, start_page, end_page, level, split_from) VALUES" +
					"(?,?,?,?,?,?,?)";
			PreparedStatement stm = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			
			stm.setString(1, name);
			stm.setString(2, books_id);
			stm.setString(3, section_after);
			stm.setInt(4, start_page);
			stm.setInt(5, end_page);
			stm.setInt(6, level);
			stm.setInt(7, split_from);
			
			return stm;
		}
		
		public PreparedStatement getUpdateStatement(Connection conn) throws SQLException{
			String s = "UPDATE " + TABLE_NAME + " " +
					"SET id = ?, name = ?, books_id = ?, section_after = ?, start_page = ?, end_page = ?, level = ?, split_from = ? " +
					"WHERE id = ?"; 
			PreparedStatement stm = conn.prepareStatement(s);
			
			stm.setInt(1, id);
			stm.setString(2, name);
			stm.setString(3, books_id);
			stm.setString(4, section_after);
			stm.setInt(5, start_page);
			stm.setInt(6, end_page);
			stm.setInt(7, level);
			stm.setInt(8, split_from);
			stm.setInt(9, id);
			
			return stm;
		}
		/*
		public String getUpdateStatement(){
			
			String statement = "UPDATE " + TABLE_NAME + " ";
			statement += "WHERE id = " + sections_id + " ";
			//statement += " (id, name, books_id, section_after, start_page, end_page, level, split_from) ";
			statement += " VALUES (" +
					sections_id + ", '" + 
					name + "', '"+ 
					books_id +"', '"+
					section_after+"', "+ 
					start_page +", " + 
					end_page + ", "+ 
					level +", " + 
					split_from + ")";
			
			return statement;
		}*/
		
	}
}