// SMSLib for Java v3
// A Java API library for sending and receiving SMS via a GSM modem
// or other supported gateways.
// Web Site: http://www.smslib.org
//
// Copyright (C) 2002-2008, Thanasis Delenikas, Athens/GREECE.
// SMSLib is distributed under the terms of the Apache License version 2.0
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

package org.smslib.smsserver.interfaces;

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

public class Database extends org.smslib.smsserver.AInterface
{
	public Database(String infId, Properties props, org.smslib.smsserver.SMSServer server)
	{
		super(infId, props, server);
		description = "Default database interface.";
	}

	public void start() throws Exception
	{
		Connection con;
		Statement cmd;

		Class.forName(props.getProperty(infId + ".driver"));

		con = getDbConnection();
		if (con != null)
		{
			cmd = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			cmd.executeUpdate("update " + props.getProperty("database.tables.sms_out", "smssvr_out") + " set status = 'U' where status = 'Q'");
			con.commit();
			cmd.close();
			con.close();
		}
		server.srv.getLogger().info("SMSServer: Interface Database started.");
}

	public void stop() throws Exception
	{
		server.srv.getLogger().info("SMSServer: Interface Database started.");
	}

	public void MessagesReceived(List msgList) throws Exception
	{
		org.smslib.InboundMessage msg;
		Connection con;
		Statement cmd;
		ResultSet rs;

		con = getDbConnection();
		cmd = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
		rs = cmd.executeQuery("select * from " + props.getProperty(infId + ".tables.sms_in", "smssvr_in") + " where id = -1");
		for (int i = 0; i < msgList.size(); i++)
		{
			msg = (org.smslib.InboundMessage) msgList.get(i);
			if ((msg.getType() == org.smslib.MessageTypes.INBOUND) || (msg.getType() == org.smslib.MessageTypes.STATUSREPORT))
			{
				rs.moveToInsertRow();
				rs.updateInt("process", 0);
				rs.updateString("originator", msg.getOriginator());
				if (msg.getType() == org.smslib.MessageTypes.INBOUND) rs.updateString("type", "I");
				else if (msg.getType() == org.smslib.MessageTypes.STATUSREPORT) rs.updateString("type", "S");
				if (msg.getEncoding() == org.smslib.MessageEncodings.ENC7BIT) rs.updateString("encoding", "7");
				else if (msg.getEncoding() == org.smslib.MessageEncodings.ENC8BIT) rs.updateString("encoding", "8");
				else if (msg.getEncoding() == org.smslib.MessageEncodings.ENCUCS2) rs.updateString("encoding", "U");
				if (msg.getDate() != null) rs.updateTimestamp("message_date", new Timestamp(msg.getDate().getTime()));
				rs.updateTimestamp("receive_date", new Timestamp(new java.util.Date().getTime()));
				rs.updateString("text", msg.getText().replaceAll("'", "''"));
				rs.updateString("gateway_id", msg.getGatewayId());
				rs.insertRow();
			}
		}
		rs.close();
		cmd.close();
		con.commit();
		con.close();
	}

	public List getMessagesToSend() throws Exception
	{
		ArrayList msgList = new ArrayList();
		org.smslib.OutboundMessage msg;
		Connection con;
		Statement cmd;
		ResultSet rs;
		int msgCount;

		msgCount = 0;
		con = getDbConnection();
		if (con != null)
		{
			cmd = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			if (props.getProperty(infId + ".type").equalsIgnoreCase("mysql"))
				rs = cmd.executeQuery("select * from " + props.getProperty(infId + ".tables.sms_out", "smssvr_out") + " where status = 'U' order by priority, id");
			else
				rs = cmd.executeQuery("select *, case when priority = 'H' then 1 when priority = 'N' then 2 when priority = 'L' then 3 else 4 end as prioritynum from " + props.getProperty(infId + ".tables.sms_out", "smssvr_out") + " where status = 'U' order by prioritynum, id");
			while (rs.next())
			{
				msgCount++;
				if (msgCount > Integer.parseInt(props.getProperty(infId + ".batch_size"))) break;
				msg = new org.smslib.OutboundMessage(rs.getString("recipient"), rs.getString("text"));
				if (rs.getString("priority").equalsIgnoreCase("L")) msg.setPriority(org.smslib.MessagePriorities.LOW);
				else if (rs.getString("priority").equalsIgnoreCase("N")) msg.setPriority(org.smslib.MessagePriorities.NORMAL);
				else if (rs.getString("priority").equalsIgnoreCase("H")) msg.setPriority(org.smslib.MessagePriorities.HIGH);
				msg.setId("" + rs.getString("id"));
				if (rs.getString("encoding").equals("7")) msg.setEncoding(org.smslib.MessageEncodings.ENC7BIT);
				else if (rs.getString("encoding").equals("8")) msg.setEncoding(org.smslib.MessageEncodings.ENC8BIT);
				else msg.setEncoding(org.smslib.MessageEncodings.ENCUCS2);
				if (rs.getInt("status_report") == 1) msg.setStatusReport(true);
				if (rs.getInt("flash_sms") == 1) msg.setFlashSms(true);
				if (rs.getInt("src_port") != -1)
				{
					msg.setSrcPort(rs.getInt("src_port"));
					msg.setDstPort(rs.getInt("dst_port"));
				}
				if (rs.getString("originator") != null) msg.setFrom(rs.getString("originator"));
				msg.setGatewayId(rs.getString("gateway_id"));
				msgList.add(msg);
				rs.updateString("status", "Q"); 
				rs.updateRow();
				con.commit();
			}
			rs.close();
			cmd.close();
			con.close();
		}
		return msgList;
	}

	public void markMessage(org.smslib.OutboundMessage msg) throws Exception
	{
		Connection con = null;
		ResultSet rs = null;
		Statement cmd = null;

		con = getDbConnection();
		cmd = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		rs = cmd.executeQuery("select * from " + props.getProperty(infId + ".tables.sms_out", "smssvr_out") + " where id = " + msg.getId());
		if (rs.next())
		{
			if (msg.getMessageStatus() == org.smslib.MessageStatuses.SENT)
			{
				rs.updateString("status", "S");
				rs.updateTimestamp("sent_date", new Timestamp(msg.getDispatchDate().getTime()));
				rs.updateString("gateway_id", msg.getGatewayId());
				rs.updateString("ref_no", msg.getRefNo());
			}
			else if (msg.getMessageStatus() == org.smslib.MessageStatuses.FAILED)
			{
				int errors = rs.getInt("errors");
				errors++;
				rs.updateInt("errors", errors);
				if (errors > Integer.parseInt(props.getProperty(infId + ".retries", "2"))) rs.updateString("status", "F");
				else rs.updateString("status", "U");
			}
			rs.updateRow();
			con.commit();
			rs.close();
			cmd.close();
			con.close();
		}
	}

	private Connection getDbConnection()
	{
		Connection dbCon = null;

		try
		{
			dbCon = DriverManager.getConnection(props.getProperty(infId + ".url"), props.getProperty(infId + ".username", ""), props.getProperty(infId + ".password", ""));
			dbCon.setAutoCommit(false);
		}
		catch (SQLException e)
		{
			server.srv.getLogger().error("SMSServer: Database Interface: error!", e);
		}
		return dbCon;
	}
}