package test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.hsqldb.jdbcDriver;

import com.sun.identity.idm.IdRepo;

public class IdDAO {

	public void init() throws SQLException {
		Connection conn = getConnection();
		if (!databaseTablesExist(conn)) {
			setupDatabase(conn);
		}
		if (!databaseTablesExist(conn)) {
			throw new Error("oops");
		}
		conn.close();
	}

	private void setupDatabase(Connection conn) throws SQLException {
		Statement stmt = conn.createStatement();
		stmt.execute("CREATE TABLE Visitor (" +
				"Id   INT  PRIMARY KEY," +
				" EmailAddress VARCHAR(50) NOT NULL," +
				" Password VARCHAR(50) NOT NULL," +
				" FirstName VARCHAR(50) NOT NULL," +
				" LastName VARCHAR(50) NOT NULL," +
				" UNIQUE (EmailAddress)" +
				")");
		stmt.execute("CREATE SEQUENCE Visitor_Id_SEQ");
	}

	private boolean databaseTablesExist(Connection conn) throws SQLException {
		ResultSet rs = conn.getMetaData().getTables(null, null, null, null);
		while (rs.next()) {
			if ("Visitor".equalsIgnoreCase(rs.getString("TABLE_NAME"))) {
				return true;
			}
		}
		return false;
	}

	private Connection getConnection() throws SQLException {
		Properties props = new Properties();
		props.setProperty("user", "sa");
		return jdbcDriver.getConnection("jdbc:hsqldb:mem:test", props);
	}

	public User loadUser(String email) throws SQLException {
		Connection conn = getConnection();
		try {
			PreparedStatement stmt = conn.prepareStatement("SELECT * FROM Visitor WHERE EmailAddress=?");
			stmt.setString(1, email);
			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				User result = new User();
				read(rs, result);
				return result;
			}
			return null;
		} finally {
			conn.close();
		}
	}

	public void insertUser(User user) throws SQLException {
		Connection conn = getConnection();
		try {
			PreparedStatement stmt = conn.prepareStatement("INSERT INTO Visitor (Id, EmailAddress, FirstName, LastName, Password) VALUES (NEXT VALUE FOR Visitor_Id_SEQ, ?, ?, ?, ?)");
			stmt.setString(1, user.getEmailAddress());
			stmt.setString(2, user.getFirstName());
			stmt.setString(3, user.getLastName());
			stmt.setString(4, user.getPassword());
			stmt.executeUpdate();
		} finally {
			conn.close();
		}
	}
	
	public void updateUser(User user) throws SQLException {
		Connection conn = getConnection();
		try {
			PreparedStatement stmt = conn.prepareStatement("UPDATE Visitor SET EmailAddress=?, FirstName=?, LastName=? WHERE Id=?");
			stmt.setString(1, user.getEmailAddress());
			stmt.setString(2, user.getFirstName());
			stmt.setString(3, user.getLastName());
			stmt.setLong(4, user.getId());
			stmt.executeUpdate();
		} finally {
			conn.close();
		}
	}

	private void read(ResultSet rs, User user) throws SQLException {
		user.setId(rs.getLong("Id"));
		user.setEmailAddress(rs.getString("EmailAddress"));
		user.setFirstName(rs.getString("FirstName"));
		user.setLastName(rs.getString("LastName"));
		user.setPassword(rs.getString("Password"));
	}

	public List search(String pattern, int maxResults, Map avPairs,
	                   int filterOp)
		throws SQLException
	{
		Connection conn = getConnection();
		try {
			PreparedStatement pstmt = buildSearchStatement(conn, pattern, maxResults, avPairs, filterOp);
			ResultSet rs = pstmt.executeQuery();
			List results = new ArrayList();
			while (rs.next()) {
				User user = new User();
				read(rs, user);
				results.add(user);
			}
			rs.close();
			pstmt.close();
			return results;
		} finally {
			conn.close();
		}
	}

	private PreparedStatement buildSearchStatement(Connection conn,
	                                               String pattern,
	                                               int maxResults,
	                                               Map avPairs,
	                                               int filterOp)
		throws SQLException
	{
		// construct the sql,
		StringBuffer query = new StringBuffer("SELECT");
		if (maxResults > 0) {
			// HSQLDB specific SQL,
			query.append(" TOP "+maxResults);
		}
		query.append(" * FROM Visitor ");
		Iterator i = null;
		if (avPairs != null) {
			i = avPairs.entrySet().iterator();
		}
		if (pattern != null || i != null && i.hasNext()) {
			query.append("WHERE ");
		}
		if (pattern != null) {
			query.append("EmailAddress=? ");
			if (i != null && i.hasNext()) {
				appendOperator(filterOp, query);
			}
		}
		while (i != null && i.hasNext()) {
			Map.Entry attrVal = (Map.Entry)i.next();
			query.append(attrVal.getKey());
			query.append("=? ");
			if (i.hasNext()) {
				appendOperator(filterOp, query);
			}
		}
		// fill in PreparedStatement parameters,
		PreparedStatement pstmt = conn.prepareStatement(query.toString());
		int seq = 1;
		if (pattern != null) {
			pstmt.setString(seq++, pattern);
		}
		if (avPairs != null) {
			i = avPairs.entrySet().iterator();
			while (i.hasNext()) {
				Map.Entry attrVal = (Map.Entry)i.next();
				pstmt.setString(seq++, (String)attrVal.getKey());
			}
		}
		return pstmt;
	}

	private void appendOperator(int filterOp, StringBuffer query) {
		if (filterOp == IdRepo.AND_MOD) {
			query.append("AND ");
		} else if (filterOp == IdRepo.OR_MOD) {
			query.append("OR ");
		} else {
			throw new IllegalArgumentException("Unknown filterOp value "+filterOp);
		}
	}
}