/*
* Copyright (c) 2004-2005, The Hong Kong University of Science and Technology (HKUST)
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions
* are met:
*
*   * Redistributions of source code must retain the above copyright notice,
*     this list of conditions,the authors and the following disclaimer.
*   * Redistributions in binary form must reproduce the above copyright notice,
*     this list of conditions,the authors and the following disclaimer in
*     the documentation and/or other materials provided with the distribution.
*   * Neither the name of the university nor the names of its
*     contributors may be used to endorse or promote products derived from
*     this software without specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
* ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE
* LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*
*/
/*******************************************************
 * This data analyzer is for VMNET logs analysis only.
 * Author: Bingsheng He (saven@cs.ust.hk)
 * 2004-Nov-14.
 *******************************************************
 */

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


public class SDAnalyzer{
	public Connection con;
	public String aodvDir;
	public String tinyDBDir;
	public String DDDir;
	public String sketchDir;
	public int []RV;
	public void doQuery(String query){
		String result="";

			try{
					System.out.println("excuting query:"+query);
					Statement s = con.createStatement();
					s.execute(query);
					ResultSet rs = s.getResultSet(); // get any ResultSet that came from our query
					ResultSetMetaData mD=rs.getMetaData();
					int cN=mD.getColumnCount();
					int i=0;
					//System.out.println("cN="+cN);
					int numRec=0;

					if (rs != null) // if rs == null, then there is no ResultSet to view
					while ( rs.next() ) // this will step through our data row-by-row
					{

						for(i=1;i<cN;i++)
						{
						String tmp=rs.getString(i);
						if(tmp==null) tmp="0";
						result=result.concat(tmp+",");
						}
						String tmp=rs.getString(cN);
						if(tmp==null) tmp="0";
						result=result.concat(tmp+"\n");
						numRec++;
					}
					s.close();
					System.out.println(result);
				}catch(Exception e)
				{
					System.out.println("error:"+e);
				}

		//return result;
	}
	/*
	 * doQueryToFile: we dump the query result into fN
	 */
	public void doQueryToFile(String query, String fN){


			try{
					System.out.println("excuting query:"+query);
					Statement s = con.createStatement();
					PrintWriter out= new PrintWriter(new BufferedWriter(new FileWriter(fN)));

					s.execute(query);
					ResultSet rs = s.getResultSet(); // get any ResultSet that came from our query
					ResultSetMetaData mD=rs.getMetaData();
					int cN=mD.getColumnCount();
					int i=0;
					//System.out.println("cN="+cN);
					int numRec=0;

					if (rs != null) // if rs == null, then there is no ResultSet to view
					while ( rs.next() ) // this will step through our data row-by-row
					{
						String result="";
						String tmp="";
						for(i=1;i<cN;i++)
						{
						tmp=rs.getString(i);
						if(tmp==null) tmp="0";
						result=result+tmp+",";
						}
						tmp=rs.getString(cN);
						if(tmp==null) tmp="0";
						result=result.concat(tmp);
						out.println(result);
						numRec++;
					}
					s.close();
				//	System.out.println(result);
				}catch(Exception e)
				{
					System.out.println("error:"+e);
				}

		//return result;
	}
	SDAnalyzer()
	{

	}

	void importGDI()
	{
		//* (1) build table with the name of directory
		try
		{

			Statement s = con.createStatement();

			s.execute("drop table GDITable");
			//s.execute("create table "+directory+" (nodeid integer, epoch integer, value1 integer, value2 integer)");
			s.execute("create table GDITable (ts TIME, nodeid integer, light_reading integer, temp_reading integer, thermopile_reading integer, humidity_reading integer,intersema_pressure_reading integer, intersema_pressure_raw integer,  intersema_temp_reading integer, intersema_temp_raw integer, voltage_reading integer, vol integer)");

			System.out.println("we are here");

			int nodeid=0;
			int light=0;
			int temperature=0;
			int thermopile=0;
			int humidity=0;
			int []values=new int[11];
			String line=null;

			BufferedReader in = new BufferedReader(new FileReader("weather.log"));//"weather_sample.log"));//
			in.readLine();
			String newinin="";
			while(true)
			{
				line=in.readLine();
				if(line==null) break;
				String []inin=line.split("\t");
				for(int i=1;i<=11;i++)
				values[i-1]=Integer.parseInt(inin[i]);
				/*nodeid=Integer.parseInt(inner[1]);
				light=Integer.parseInt(inner[2]);
				temperature=Integer.parseInt(inner[3]);
				thermopile=Integer.parseInt(inner[4]);
				humidity=Integer.parseInt(inner[5]);*/
				if(newinin.equals(inin[0].substring(0,19)))
				continue;
				else
				newinin=inin[0].substring(0,19);
				String tuple="insert into GDITable values('"+newinin+"', ";
				for(int i=0;i<10;i++)
					tuple=tuple+values[i]+",";
				tuple=tuple+values[10]+")";
				System.out.println(tuple);
				//	+light+","+temperature+","+humidity+","+thermopile+")";
			//				System.out.println(tuple);
				s.execute(tuple);

				//System.out.println(inner[0]+","+inner[1]+","+inner[2]+","+inner[3]+","+inner[4]);
				con.commit();

			}

			in.close();


			con.commit();
			s.close();
			System.out.println("end of log");

		}
			catch(Exception e)
			{
				System.out.println("Error in import log: " +"," + e);
			}
	}

	void importIntel()
	{
		//* (1) build table with the name of directory
		try
		{

			Statement s = con.createStatement();
			//date:yyyy-mm-dd time:hh:mm:ss.xxx epoch:int moteid:int temperature:real humidity:real light:real voltage:real
			s.execute("drop table IntelTable");
			s.execute("create table IntelTable (ts TIME, epoch integer, nodeid integer, temperature double, humidity double, light double, voltage double)");

			System.out.println("we are here");

			//String ts;
			int []values=new int[2];
			double []Realvalues=new double[4];
			String line=null;

			BufferedReader in = new BufferedReader(new FileReader("inteldata.txt"));//"weather_sample.log"));//
			in.readLine();
			String ts="";
			while(true)
			{
				line=in.readLine();
				if(line==null) break;
				String []inin=line.split(" ");
				if(inin[0]==null || inin[1]==null || inin.length<8 || inin[1].length()<8 || inin[0].length()==0)
				{
					System.out.println("a null timestamp or too short"+inin.length);
					continue;
				}
				if(ts.equals(inin[0]+" "+inin[1].substring(0,8)))
				{
					System.out.println("repeat here");
					continue;
				}
				else
				ts=inin[0]+" "+inin[1].substring(0,8);

				for(int i=0;i<2;i++)
				if(inin[i+2]!=null || inin[i+2].length()!=0)
					values[i]=Integer.parseInt(inin[i+2]);
				else
					values[i]=0;

				for(int i=0;i<4;i++)
				if(inin[i+4]!=null && inin[i+4].length()!=0)
					Realvalues[i]=Double.parseDouble(inin[i+4]);
				else
					Realvalues[i]=0;
				String tuple="insert into IntelTable values('"+ts+"', ";
				for(int i=0;i<2;i++)
					tuple=tuple+values[i]+",";
				for(int i=0;i<3;i++)
					tuple=tuple+Realvalues[i]+",";
				tuple=tuple+Realvalues[3]+")";
				System.out.println(tuple);
				//	+light+","+temperature+","+humidity+","+thermopile+")";
			//				System.out.println(tuple);
				s.execute(tuple);

				//System.out.println(inner[0]+","+inner[1]+","+inner[2]+","+inner[3]+","+inner[4]);
				con.commit();

			}

			in.close();


			con.commit();
			s.close();
			System.out.println("end of log");

		}
			catch(Exception e)
			{
				System.out.println("Error in import log: " +"," + e);
			}
	}

	public void importIntel(String tableName, String schema, String fileName)
	{
		//* (1) build table with the name of directory
		try
		{

			Statement s = con.createStatement();
			//date:yyyy-mm-dd time:hh:mm:ss.xxx epoch:int moteid:int temperature:real humidity:real light:real voltage:real
			//s.execute("drop table "+tableName);
			String cSQL="create table "+tableName+" ("+ schema+")";
			System.out.println(cSQL);
			s.execute(cSQL);

			System.out.println("we are here");

			String ts="";
			int values;
			double Realvalues;
			double Realvalues2;
			String line=null;
			for(int id=1;id<=6;id++)
			{
			BufferedReader in = new BufferedReader(new FileReader("Input"+id+".dat"));//"weather_sample.log"));//
			in.readLine();
			while(true)
			{
				line=in.readLine();
				if(line==null) break;
				String []inin=line.split(",");
				if(ts.equals(inin[0]))
				{
					System.out.println("we are repeat here");
					continue;
				}
				ts=inin[0];
				if(inin.length!=4) continue;
				if(inin[1]!=null)
					values=Integer.parseInt(inin[1]);
				else
					values=0;
				if(inin[2]!=null)
					Realvalues=Double.parseDouble(inin[2]);
				else
					Realvalues=0;

				if(inin[3]!=null)
					Realvalues2=Double.parseDouble(inin[3]);
				else
					Realvalues2=0;
				String tuple="insert into "+tableName+" values('"+ts+"', ";
				tuple=tuple+values+",";
				tuple=tuple+Realvalues+","+Realvalues2+")";
				System.out.println(tuple);
				//	+light+","+temperature+","+humidity+","+thermopile+")";
			//				System.out.println(tuple);
				s.execute(tuple);

				//System.out.println(inner[0]+","+inner[1]+","+inner[2]+","+inner[3]+","+inner[4]);
				con.commit();

			}

			in.close();


			con.commit();
		}
			s.close();
			System.out.println("end of log");

		}
			catch(Exception e)
			{
				System.out.println("Error in importIntel(String tableName, String fileName): " +"," + e);
			}
	}

	void importNewDataSets(String dataSets, String schema, String tableName)
	{
		//* (1) build table with the name of directory
		try
		{

			Statement s = con.createStatement();
			//date:yyyy-mm-dd time:hh:mm:ss.xxx epoch:int moteid:int temperature:real humidity:real light:real voltage:real
			String cSQL="create table "+tableName+" (";
			/*for(int i=0;i<(schema.length-1);i++)
				cSQL=schema[i]+" "+types[i]+", ";
			cSQL=cSQL+ schema[schema.length-1]+" "+types[schema.length-1];*/
			cSQL=cSQL+schema;
			cSQL=cSQL+")";
			System.out.println("we are creating table using: "+cSQL);

			s.execute(cSQL);

			System.out.println("we are here");

			String ts="";

			String line=null;

			BufferedReader in = new BufferedReader(new FileReader(dataSets));//"weather_sample.log"));//
			in.readLine();
			while(true)
			{
				line=in.readLine();
				if(line==null) break;
				String []inin=line.split(",");
				if(inin.length<=2) continue;
				String tuple="insert into "+tableName+ " values('"+inin[0]+"', ";
				for(int i=1;i<(inin.length-1);i++)
					tuple=tuple+inin[i]+",";
				tuple=tuple+inin[inin.length-1]+")";
				System.out.println(tuple);
				s.execute(tuple);
				con.commit();

			}

			in.close();


			con.commit();
			s.close();
			System.out.println("end of log");

		}
			catch(Exception e)
			{
				System.out.println("Error in import log: " +"," + e);
			}
	}

	void dropTable(String tableName)
	{
		try{
			Statement s = con.createStatement();
			//date:yyyy-mm-dd time:hh:mm:ss.xxx epoch:int moteid:int temperature:real humidity:real light:real voltage:real
			s.execute("drop table "+tableName);
		}catch(Exception e)
		{
			System.out.println("we are in dropTable "+e);
		}
	}

	public static void main(String []argv)
	{
		try {

					SDAnalyzer log=new SDAnalyzer();
					Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
					 // set this to a MS Access DB you have on your machine
					String filename = "Bisque.mdb";
					String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
					database+= filename.trim() + ";DriverID=22}"; // add on to the end
					 // now we can get the connection from the DriverManager
					log.con = DriverManager.getConnection(database ,"","");
					for(int i=0;i<argv.length;i++)
					System.out.print(argv[i]+" ");
					System.out.println("");
					//log.importIntel("","");
					//log.importGDI();
					//log.importIntel();
					//log.importIntel("SmallTestTable","ts TIME, nodeid integer, temperature double, light double","");

					if(argv.length==1) //we are querying
					log.doQuery(argv[0]);
					else if(argv.length==2)
					{
						if(argv[0].equals("-del"))
						log.dropTable(argv[1]);
						else
						log.doQueryToFile(argv[0],argv[1]);

					}
					else if(argv.length>=3)
					{
						//java SDAnalyzer -tn testDS -source testDS.txt -schema "ts TIME, epoch integer, nodeid integer, temperature double, humidity double, light double, voltage double"
						//we note that -schema should be the last arguments.
						String tableName="";
						String dataSets="";
						String schema="";
						for(int i=0;i<argv.length;i++)
						if(argv[i].equals("-tn"))
						{
							tableName=argv[i+1];
						}
						else if(argv[i].equals("-source"))
						{
							dataSets=argv[i+1];
						}
						else if(argv[i].equals("-schema"))
						{
							schema=argv[i+1];
						}
						log.importNewDataSets(dataSets,schema,tableName);

					}
					log.con.close(); // close the Connection to let the database know we're done with it*/


		        }
		            catch (Exception e) {
		            System.out.println("Error in main: " + e);
        }

	}

}