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
{
	Connection connection = null;
	Statement statement;
	private Map<String, String> CookieToTable = new HashMap<String, String>();
	private Map<String, List<String>> CookieToKey = new HashMap<String, List<String>>();
	
	public DBWrapper()
	{
		try
		{
			System.out.println("Opening sqlite db data.db");
			connection = DriverManager.getConnection("jdbc:sqlite:data.db");
		}
		catch(SQLException e)
		{ 
			System.err.println(e.getMessage());
		}
		System.out.println("Opening data.db successful");
	}
	
	public void CreateTable(String cookie, String name)
	{
		name = StripNonAlphaNumericFromName(name);
		name = name.replaceAll("\\s+","");
		System.out.println(cookie + " " + name);
		try
		{
		CookieToTable.put(cookie, name);
		Statement statement = connection.createStatement();
		statement.setQueryTimeout(30);
		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 cookie, String name)
	{
		String table = CookieToTable.get(cookie);
		try
		{
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30);
			// 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)
		{ 
			System.err.println(e.getMessage());
		}	
	}
	
	public void Update(String cookie, String recordId, String recordKey, HashMap<String,Object> nameToValue)
	{
		try
		{
			String table = CookieToTable.get(cookie);
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30);
			// 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);
			}
			
			if (!recordKey.equals(""))
			{
				statement.executeUpdate("update " + table + " set recordKey = '" + recordKey + "' where recordId='" + recordId + "'");
			}
			
			String updateQuery = CreateUpdateQuery(table, recordId, nameToValue);
			statement.executeUpdate(updateQuery);
		}
		catch(SQLException e)
		{
			System.err.println(e.getMessage());
		}
	}
	
	public void UpdateNewsText(String cookie, String recordId, String text)
	{
		try
		{
			text = text.replace("'", "''");
			String table = CookieToTable.get(cookie);
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30);
			System.out.println("update " + table + " set FullText = '" + text + "' where recordId='" + recordId + "'");
			statement.executeUpdate("update " + table + " set FullText = '" + text + "' where recordId='" + recordId + "'");	
		}
		catch(SQLException e)
		{
			System.err.println(e.getMessage());
		}
	}
	
	public void AddKey(String cookie, String key)
	{
		if (CookieToKey.get(cookie) == null)
			CookieToKey.put(cookie, new ArrayList<String>());
	
		CookieToKey.get(cookie).add(key);
	}
	
	public String GetKey(String cookie)
	{
		String key = "";
		
		for(String s: CookieToKey.get(cookie))
		{
			key = key + "/" + s;
		}
	
		// Remove the first /
		key = key.substring(1);
	
		return key;
	}
	
	public String CreateKeyIfExist(String cookie, HashMap<String,Object> nameToValue)
	{
		String key = "";
	
		for(String s: CookieToKey.get(cookie))
		{
			if (nameToValue.get(s) == null)
				return "";
				
			key = key + "/" + nameToValue.get(s);
		}
		
		// Remove the first /
		key = key.substring(1);
		
		return key;
	}
	
	private String StripNonAlphaNumericFromName(String name)
	{
		return name.replaceAll("[^A-Za-z0-9]", "");
	}
	
	private String CreateUpdateQuery(String table, String recordId, HashMap<String,Object> nameToValue)
	{
		String query = "update " + table + " set ";
	
		Iterator it = nameToValue.entrySet().iterator();
		while (it.hasNext()) {
			Map.Entry pairs = (Map.Entry)it.next();
			query = query + pairs.getKey() + " = " + "'" + pairs.getValue().toString().replace("'","''") + "', ";
			it.remove(); // avoids a ConcurrentModificationException
		}
		
		// remove last ,
		query = query.substring(0,query.length()-2);
		
		query = query + " where recordId='" + recordId + "'";
		
		return query;
	}
}