import java.sql.*;
import java.util.Map;
import java.util.HashMap;
import java.util.List;
import java.util.ArrayList;
import java.util.Iterator;

public class DBWrapper
{
	private Connection connection = null;
	private Statement statement;
	private final static String DATABASE_NAME = "data.db";
	
	public DBWrapper()
	{
		try
		{
			System.out.println("Opening sqlite db " + DATABASE_NAME);
			connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE_NAME);
		}
		catch(SQLException e)
		{ 
			System.err.println(e.getMessage());
		}
		System.out.println("Opening " + DATABASE_NAME + " successful");
	}
	
	public void CreateTable(String name)
	{
		name = StripNonAlphaNumericFromName(name);
		try
		{
			Statement statement = connection.createStatement();
			statement.executeUpdate("create table " + name + " (recordId text primary key, recordKey text)");
			
			// news tables are special cases and a full text column is added. 
			// This column is used to add the full text from the  separate query.
			if (name.equals("rtnews") || name.equals("histnews"))
			{
				statement.executeUpdate("alter table " + name + " add FullText text");
			}
			
		}
		catch(SQLException e)
		{ 
			System.err.println(e.getMessage());
		}
	}
	
	public void AddColumn(String table, String name)
	{
		try
		{
			Statement statement = connection.createStatement();
			
			// The correct type should be added to but for simplicity we will store everything as TEXT
			statement.executeUpdate("alter table " + table + " add " + name + " text");
		}
		catch(SQLException e)
		{ 
			// Dont print the duplicate errors on reconnect
			if (!e.getMessage().contains("duplicate"))
				System.err.println(e.getMessage());
		}	
	}
	
	public void Update(String table, String recordId, String recordKey, HashMap<String,Object> nameToValue)
	{
		if (nameToValue == null)
			return;
			
		if (nameToValue.size() == 0)
			return;
	
		try
		{
			Statement statement = connection.createStatement();
			// Check if id exist
			ResultSet rs = statement.executeQuery("select recordId from " + table + " where recordId='" + recordId + "'");
				
			if(!rs.next())
			{
			// No Id lets create new row
				String createQuery = "insert into " + table + "(recordId) values(" + recordId + ")";
				statement.executeUpdate(createQuery);
			}

			String updateQuery = CreateUpdateQuery(table, recordId, recordKey, nameToValue);
			statement.executeUpdate(updateQuery);
		}
		catch(SQLException e)
		{
			System.err.println(e.getMessage());
		}
	}
	
	public void UpdateNewsText(String table, String recordId, String text)
	{
		try
		{
			text = text.replace("'", "''"); // Prepare the text for sqlite
			Statement statement = connection.createStatement();
			statement.executeUpdate("update " + table + " set FullText = '" + text + "' where recordId='" + recordId + "'");	
		}
		catch(SQLException e)
		{
			System.err.println(e.getMessage());
		}
	}
	
	private String StripNonAlphaNumericFromName(String name)
	{
		String ret = name.replaceAll("[^A-Za-z0-9]", "");
		return ret.replaceAll("\\s+","");
	}
	
	private String CreateUpdateQuery(String table, String recordId, String recordKey, HashMap<String,Object> nameToValue)
	{
		String query = "update " + table + " set ";
	
		if (!recordKey.equals(""))
		{
			query = query + "recordKey = '" + recordKey + "', ";
		}
	
		Iterator it = nameToValue.entrySet().iterator();
		while (it.hasNext()) {
			Map.Entry pairs = (Map.Entry)it.next();
			if (pairs.getKey() != null && pairs.getValue() != null)
			{
				String key = pairs.getKey().toString();
				String value = pairs.getValue().toString().replace("'","''");
				query = query + key + " = '" + value + "', ";
			}
			it.remove(); // avoids a ConcurrentModificationException
		}
		
		// remove last ,
		query = query.substring(0,query.length()-2);
		
		query = query + " where recordId='" + recordId + "'";
		
		return query;
	}
}