/*
*
* Paros and its related class files.
* 
* Paros is an HTTP/HTTPS proxy for assessing web application security.
* Copyright (C) 2003-2004 Chinotec Technologies Company
* 
* This program is free software; you can redistribute it and/or
* modify it under the terms of the Clarified Artistic License
* as published by the Free Software Foundation.
* 
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
* Clarified Artistic License for more details.
* 
* You should have received a copy of the Clarified Artistic License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
*/

package org.parosproxy.paros.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Vector;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.parosproxy.paros.model.HistoryReference;
import org.parosproxy.paros.network.HttpBody;
import org.parosproxy.paros.network.HttpMalformedHeaderException;
import org.parosproxy.paros.network.HttpMessage;


/**
 *
 * To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Generation - Code and Comments
 */
public class TableHistory extends AbstractTable {

	private static final String	HISTORYID	= "HISTORYID";
	private static final String SESSIONID	= "SESSIONID";
	private static final String HISTTYPE	= "HISTTYPE";
	private static final String METHOD 		= "METHOD";
	private static final String URI			= "URI";
	private static final String TIMESENTMILLIS = "TIMESENTMILLIS";
	private static final String TIMEELAPSEDMILLIS = "TIMEELAPSEDMILLIS";
	private static final String REQHEADER	= "REQHEADER";
	private static final String REQBODY		= "REQBODY";
	private static final String RESHEADER	= "RESHEADER";
	private static final String RESBODY		= "RESBODY";

    private PreparedStatement psRead = null;
    private PreparedStatement psWrite1 = null;
    private CallableStatement psWrite2 = null;
    private PreparedStatement psDelete = null;
    private PreparedStatement psDeleteTemp = null;
    private PreparedStatement psContainsURI = null;
    
    public TableHistory() {
    }
    
    protected void reconnect(Connection conn) throws SQLException {
        psRead = conn.prepareStatement("SELECT TOP 1 * FROM HISTORY WHERE " + HISTORYID + " = ?");
        // updatable recordset does not work in hsqldb jdbc impelementation!
        //psWrite = mConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        psWrite1= conn.prepareStatement("INSERT INTO HISTORY ("
        		+ SESSIONID + "," + HISTTYPE + "," + TIMESENTMILLIS + "," + TIMEELAPSEDMILLIS + "," + METHOD + "," + URI + "," + REQHEADER + "," + REQBODY + "," + RESHEADER + "," + RESBODY
				+ ") VALUES (?, ? ,?, ?, ?, ?, ?, ? ,? , ?)");
        psWrite2 = conn.prepareCall("CALL IDENTITY();");
        psDelete = conn.prepareStatement("DELETE FROM HISTORY WHERE " + HISTORYID + " = ?");
        psDeleteTemp = conn.prepareStatement("DELETE FROM HISTORY WHERE " + HISTTYPE + " = " + HistoryReference.TYPE_TEMPORARY);
        psContainsURI = conn.prepareStatement("SELECT TOP 1 HISTORYID FROM HISTORY WHERE " + URI + " = ? AND " + REQBODY + " = ? AND " + SESSIONID + " = ? AND " + HISTTYPE + " = ? AND " + METHOD + " = ?");
    }
    
    
    	
	public synchronized RecordHistory read(int historyId) throws HttpMalformedHeaderException, SQLException {
		psRead.setInt(1, historyId);
		psRead.execute();
		ResultSet rs = psRead.getResultSet();
		RecordHistory result = build(rs);
		rs.close();
		return result;
	}
	
	public synchronized RecordHistory write(long sessionId, int histType, HttpMessage msg) throws HttpMalformedHeaderException, SQLException {
	    
	    String reqHeader = "";
	    String reqBody = "";
	    String resHeader = "";
	    String resBody = "";
	    String method = "";
	    String uri = "";
	    
	    if (!msg.getRequestHeader().isEmpty()) {
	        reqHeader = msg.getRequestHeader().toString();
	        reqBody = msg.getRequestBody().toString();
	        method = msg.getRequestHeader().getMethod();
	        uri = msg.getRequestHeader().getURI().toString();
	    }

	    if (!msg.getResponseHeader().isEmpty()) {
	        resHeader = msg.getResponseHeader().toString();
	        resBody = msg.getResponseBody().toString(HttpBody.STORAGE_CHARSET);
	    }
	    
	    return write(sessionId, histType, msg.getTimeSentMillis(), msg.getTimeElapsedMillis(), method, uri, reqHeader, reqBody, resHeader, resBody);
	    
	}
	
	private synchronized RecordHistory write(long sessionId, int histType, long timeSentMillis, int timeElapsedMillis,
	        String method, String uri,
	        String reqHeader, String reqBody, String resHeader, String resBody) throws HttpMalformedHeaderException, SQLException {

		psWrite1.setLong(1, sessionId);
		psWrite1.setInt(2, histType);
		psWrite1.setLong(3, timeSentMillis);
		psWrite1.setInt(4, timeElapsedMillis);
		psWrite1.setString(5, method);
		psWrite1.setString(6, uri);
		psWrite1.setString(7, reqHeader);
		psWrite1.setString(8, reqBody);
		psWrite1.setString(9, resHeader);
		psWrite1.setString(10, resBody);
		psWrite1.executeUpdate();
				
		/*
        String sql = "INSERT INTO HISTORY ("
        		+ REQHEADER + "," + REQBODY + "," + RESHEADER + "," + RESBODY +
				") VALUES ('"+ reqHeader + "','" + reqBody + "','" + resHeader + "','" + resBody + "'); CALL IDENTITY();";
		Statement stmt = mConn.createStatement();
		stmt.executeQuery(sql);
		ResultSet rs = stmt.getResultSet();
		*/
		
		psWrite2.executeQuery();
		ResultSet rs = psWrite2.getResultSet();
		rs.next();
		int id = rs.getInt(1);
		rs.close();
		return read(id);
	}
	
	private RecordHistory build(ResultSet rs) throws HttpMalformedHeaderException, SQLException {
		RecordHistory history = null;
		if (rs.next()) {
			history = new RecordHistory(
					rs.getInt(HISTORYID),
					rs.getInt(HISTTYPE),
					rs.getLong(TIMESENTMILLIS),
					rs.getInt(TIMEELAPSEDMILLIS),
					rs.getString(REQHEADER),
					rs.getString(REQBODY),
					rs.getString(RESHEADER),
					rs.getString(RESBODY)
			);
			
		}
		return history;
	
	}
	
	public Vector getHistoryList(long sessionId, int histType) throws SQLException {
	    PreparedStatement psReadSession = getConnection().prepareStatement("SELECT " + HISTORYID + " FROM HISTORY WHERE " + SESSIONID + " = ? AND " + HISTTYPE + " = ? ORDER BY " + HISTORYID);
        
	    Vector v = new Vector();
	    psReadSession.setLong(1, sessionId);
	    psReadSession.setInt(2, histType);
	    psReadSession.executeQuery();
	    ResultSet rs = psReadSession.getResultSet();
	    
	    while (rs.next()) {
	        int last = rs.getInt(HISTORYID);
	        v.add(new Integer(last));
	    }
	    rs.close();
	    psReadSession.close();
	    
		return v;
	}

	public List getHistoryList(long sessionId, int histType, String filter, boolean isRequest) throws SQLException {
        PreparedStatement psReadSearch = getConnection().prepareStatement("SELECT * FROM HISTORY WHERE " + SESSIONID + " = ? AND " + HISTTYPE + " = ? ORDER BY " + HISTORYID);

	    Pattern pattern = Pattern.compile(filter, Pattern.MULTILINE| Pattern.CASE_INSENSITIVE);
		Matcher matcher = null;

		Vector v = new Vector();
		psReadSearch.setLong(1, sessionId);
		psReadSearch.setInt(2, histType);
		psReadSearch.executeQuery();
		ResultSet rs = psReadSearch.getResultSet();
		while (rs.next()) {
		    if (isRequest) {
		        matcher = pattern.matcher(rs.getString(REQHEADER));
		        if (matcher.find()) {
		            v.add(new Integer(rs.getInt(HISTORYID)));
		            continue;
		        }
		        matcher = pattern.matcher(rs.getString(REQBODY));
		        if (matcher.find()) {
		            v.add(new Integer(rs.getInt(HISTORYID)));
		            continue;
		        }
		    } else {
		        matcher = pattern.matcher(rs.getString(RESHEADER));
		        if (matcher.find()) {
		            v.add(new Integer(rs.getInt(HISTORYID)));
		            continue;
		        }
		        matcher = pattern.matcher(rs.getString(RESBODY));
		        if (matcher.find()) {
		            v.add(new Integer(rs.getInt(HISTORYID)));
		            continue;
		        }
		    }
		    
		}
		rs.close();
	    psReadSearch.close();

	    return v;
	}

//	public List getHistoryList(long sessionId, int histType, String filter) throws SQLException {
//	    Pattern pattern = Pattern.compile(filter, Pattern.MULTILINE| Pattern.CASE_INSENSITIVE);
//		Matcher matcher = null;
//
//		Vector v = new Vector();
//		psReadSearch.setLong(1, sessionId);
//		psReadSearch.setInt(2, histType);
//		psReadSearch.executeQuery();
//		ResultSet rs = psReadSearch.getResultSet();
//		while (rs.next()) {
//		    matcher = pattern.matcher(rs.getString(REQHEADER));
//		    if (matcher.find()) {
//				v.add(new Integer(rs.getInt(HISTORYID)));
//				continue;
//		    }
//		    matcher = pattern.matcher(rs.getString(REQBODY));
//		    if (matcher.find()) {
//				v.add(new Integer(rs.getInt(HISTORYID)));
//				continue;
//		    }
//		    matcher = pattern.matcher(rs.getString(RESHEADER));
//		    if (matcher.find()) {
//				v.add(new Integer(rs.getInt(HISTORYID)));
//				continue;
//		    }
//		    matcher = pattern.matcher(rs.getString(RESBODY));
//		    if (matcher.find()) {
//				v.add(new Integer(rs.getInt(HISTORYID)));
//				continue;
//		    }
//		    
//		}
//	    return v;
//	}
	
	public void deleteHistorySession(long sessionId) throws SQLException {
        Statement stmt = getConnection().createStatement();
        stmt.executeUpdate("DELETE FROM HISTORY WHERE " + SESSIONID + " = " + sessionId);
        stmt.close();
	}
	
	public void deleteHistoryType(long sessionId, int historyType) throws SQLException {
        Statement stmt = getConnection().createStatement();
        stmt.executeUpdate("DELETE FROM HISTORY WHERE " + SESSIONID + " = " + sessionId + " AND " + HISTTYPE + " = " + historyType);
        stmt.close();
	}

	public void delete(int historyId) throws SQLException {
		psDelete.setInt(1, historyId);
		psDelete.executeUpdate();
		
	}
	
	public void deleteTemporary() throws SQLException {
	    psDeleteTemp.execute();
	}
	
	public boolean containsURI(long sessionId, int historyType, String method, String uri, String body) throws SQLException {
	    psContainsURI.setString(1, uri);
	    psContainsURI.setString(2, body);
	    psContainsURI.setLong(3, sessionId);
	    psContainsURI.setInt(4, historyType);
	    psContainsURI.setString(5, method);
	    psContainsURI.executeQuery();
		ResultSet rs = psContainsURI.getResultSet();
	    if (rs.next()) {
	        return true;
	    }
	    rs.close();
	    return false;
	    
	}
}
