/**
 * dblookup_netcallerid2orator.bsh
 * 
 * This script looks up the telephone number that was received
 * from the NetCallerId in a MySQL database and sends a speech
 * message to announce the caller.  If the caller does not exist
 * in the database it sends the caller name that came from the
 * caller id message.
 *
 * It then logs the call to the received calls table.
 *
 */

import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormatter;
import org.joda.time.format.DateTimeFormat;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.text.DateFormat;

static SimpleDateFormat callDateFormat = new SimpleDateFormat("MMddHHmm");

// All xPL4Java beanshell scripts must have an init() method.
void init() {
    // Set the default source...
    SetDefaultSource("cdp1802-xpl4java.micklaptop");

    // Set the listener...
    ListenFor("xpl-trig.*.*.*.cid.netcall");

}

void handleXPLMessage(theMessage) {

    // Get the caller name and number from the caller id message...
    String callerName = theMessage.getNamedValue("cln");
    String callerNumber = theMessage.getNamedValue("phone");
    String callDateStr = theMessage.getNamedValue("call-date");
    Date callDate = callDateFormat.parse(callDateStr);

    System.out.println("Caller Name: " + callerName + ", Caller Number: " + callerNumber + ", Call Date: " + callDateStr);

    // Database interaction...
    try {
	String dbURL = "jdbc:mysql://192.168.64.2/your_database_name";
	String dbUserName = "your_username";
	String dbPassword = "your_password";

	try {
	    Class.forName("com.mysql.jdbc.Driver").newInstance();
	} catch (Exception e) {
	    System.err.println("LOGGER:: Unable to connect to database " + dbURL + ", User=" + dbUserName + ", Password=" + dbPassword + " -- " + connectError.getMessage());
	}

	// Connect to the database and look up the phone number...
	Connection dbConnection = DriverManager.getConnection(dbURL, dbUserName, dbPassword);
	dbConnection.setAutoCommit(true);
	statement = dbConnection.createStatement();
	rs = statement.executeQuery("SELECT caller_name FROM caller_names WHERE phone_number = '" + callerNumber + "'");
	while (rs.next()) {
	    callerName = rs.getString("caller_name");
	}
	rs.close();
	statement.close();

	// Create a string of the call datetime to insert into the database...
	DateTime callDateJoda = new DateTime(callDate);     // This datetime does not have the year set...
	DateTime currentDateTime = new DateTime();
	callDateJoda = callDateJoda.year().setCopy(currentDateTime.getYear());
	DateTimeFormatter fmt = DateTimeFormat.forPattern("yyyyMMddHHmmss");
	String callDateTimeStr = fmt.print(callDateJoda);
	String cmd = "INSERT INTO incoming_calls (caller_name, phone_number, call_datetime) VALUES ('" + callerName+ "', '" + callerNumber + "', '" + callDateTimeStr + "')";

	// Record the call in the incoming_calls table...
	statement = dbConnection.createStatement();

	statement.executeUpdate(cmd);

	dbConnection.close();


    } catch (SQLException connectError) {
	System.err.println("SQLException: " + connectError.getMessage());
	System.err.println("SQLState: " + connectError.getSQLState());
	System.err.println("Vendor Error: " + connectError.getErrorCode());

    }

    // Create the speech message...
    speechMessage = CreateMessage("xpl-cmnd", "*", "tts.basic");
    speechMessage.addNamedValue("speech", "Incoming call from " + callerName);

    // Send the speech message...
    SendMessage(speechMessage);
}