package neu.integration.ctic.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.xml.bind.annotation.adapters.CollapsedStringAdapter;

public class HoneypotStatUtil {

	public static Map<String,List<Map<String,String>>> getHoneyStat(){
		String sql_protocol = "SELECT protocol,SUM(counts)as num FROM attacker_geoip_info  GROUP BY protocol ORDER BY num desc"; //s7,modbus每个协议对应的记录数
		String sql_s7_country = "SELECT country,protocol,SUM(counts)as num FROM attacker_geo_diff_ip_info WHERE protocol=\"s7\" GROUP BY country ORDER BY num desc ";//每个国家的对应的s7记录条数，降序排列
		String sql_modbus_country = "SELECT country,protocol,SUM(counts)as num FROM attacker_geo_diff_ip_info WHERE protocol=\"modbus\" GROUP BY country ORDER BY num desc ";//每个国家的对应的modbus记录条数，降序排列
		String sql_totalnum = "SELECT SUM(counts)as num  FROM attacker_geo_diff_ip_info WHERE protocol=\"modbus\" OR protocol=\"s7\"";
		
		Statement stmt = null;
		//ResultSet rs_protocol,rs_s7_country,rs_modbus_country = null;
		ResultSet rs = null; 
		PreparedStatement pstmt = null;
		Map<String,List<Map<String,String>>> result= new HashMap<String,List<Map<String,String>>>();
		List<Map<String,String>> nodes = new ArrayList<Map<String,String>>(); 
		List<Map<String,String>> links = new ArrayList<Map<String,String>>();
		Map<String,String> first = new HashMap<String,String>();
		List<String> nodelist = new ArrayList<String>();
		first.put("name","total");
		nodes.add(first);
		try{
		Connection conn = DBUtil.getConn("localhost:3306/beijing","root","root");
		stmt = conn.createStatement();
		//pstmt = DBUtil.prepareStmt(conn,sql);
	/*	rs_protocol = DBUtil.executeQuery(stmt, sql_protocol);
		rs_s7_country = DBUtil.executeQuery(stmt, sql_s7_country);
		rs_modbus_country = DBUtil.executeQuery(stmt, sql=_ modbus_country);*/
		List<String> protocolName = new ArrayList<String>();
		rs = DBUtil.executeQuery(stmt, sql_protocol);
		while(rs.next()){
			Map<String,String> node = new HashMap<String,String>();
			Map<String,String> link = new HashMap<String,String>();
			String protocol = rs.getString("protocol");
			protocolName.add(protocol);
			String num = rs.getString("num");
			if(!nodelist.contains(protocol)){
				nodelist.add(protocol);
				node.put("name", protocol);
				nodes.add(node);
			}
			link.put("source", "total");
			link.put("target", protocol);
			link.put("value",num);
			links.add(link);
		}
		for(String name : protocolName){
			String sqlGenerate = "SELECT country,protocol,SUM(counts)as num FROM attacker_geo_diff_ip_info WHERE protocol=\""+name+"\" GROUP BY country ORDER BY num desc";
			rs = DBUtil.executeQuery(stmt, sqlGenerate);
			while(rs.next()){
				Map<String,String> node = new HashMap<String,String>();
				Map<String,String> link = new HashMap<String,String>();
				String country = rs.getString("country");
				String num = rs.getString("num");
				if(!nodelist.contains(country)){
					nodelist.add(country);
					node.put("name", country);
					nodes.add(node);
				}
				link.put("source", name);
				link.put("target", country);
				link.put("value",num);
				
				links.add(link);	
			}
			
		}
		
		
		
		System.out.println(nodes);
		System.out.println(links);
		
		result.put("nodes", nodes);
		result.put("links", links);
		System.out.println(result);
		DBUtil.close(conn, stmt, pstmt, rs);
		/*	rs_modbus_country.close();*/
		}
		catch(SQLException se){
		      //Handle errors for JDBC
		      se.printStackTrace();
		   }catch(Exception e){
		      //Handle errors for Class.forName
		      e.printStackTrace();
		   }
		return result;
	}

}