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 {

	private static IdDAO instance;

	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         NOT NULL  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");
		stmt.execute("CREATE TABLE Address (" +
				" Id        INT         NOT NULL  PRIMARY KEY," +
				" VisitorId INT         NOT NULL," +
				" Premise   VARCHAR(50)," +
				" Street    VARCHAR(50)," +
				" District  VARCHAR(50)," +
				" Town      VARCHAR(50)," +
				" County    VARCHAR(50)," +
				" OutCode   VARCHAR(50)," +
				" Country   VARCHAR(50)," +
				" FOREIGN KEY (VisitorId) REFERENCES Visitor (Id) ON DELETE CASCADE" +
				")");
		stmt.execute("SET PROPERTY \"sql.enforce_strict_size\" true");
	}

	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 loadUserByEmail(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 User loadUserById(long id) throws SQLException {
		Connection conn = getConnection();
		try {
			PreparedStatement stmt = conn.prepareStatement("SELECT * FROM Visitor WHERE Id=?");
			stmt.setLong(1, id);
			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				User result = new User();
				read(rs, result);
				return result;
			}
			return null;
		} finally {
			conn.close();
		}
	}

	public void deleteUserById(long id) throws SQLException {
		Connection conn = getConnection();
		try {
			PreparedStatement stmt = conn.prepareStatement("DELETE FROM Visitor WHERE Id=?");
			stmt.setLong(1, id);
			int result = stmt.executeUpdate();
			if (result > 1) {
				throw new SQLException("Postcondition violated: more than one row deleted");
			}
		} finally {
			conn.close();
		}
	}

	public void insertUser(User user) throws SQLException {
		Connection conn = getConnection();
		try {
			// TODO: need to get the seq value so that we can stuff
			// it back into the given User 
//			* * *
			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();
		}
	}
	
	public Address loadAddress(User user) throws SQLException {
		Connection conn = getConnection();
		try {
			PreparedStatement stmt = conn.prepareStatement("SELECT * FROM Address WHERE VisitorId=?");
			stmt.setLong(1, user.getId());
			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				Address result = new Address();
				read(rs, result);
				return result;
			}
			return null;
		} finally {
			conn.close();
		}
	}

	private void read(ResultSet rs, Address address) throws SQLException {
		address.setId(rs.getLong("Id"));
		address.setPremise(rs.getString("Premise"));
		address.setStreet(rs.getString("Street"));
		address.setDistrict(rs.getString("District"));
		address.setTown(rs.getString("Town"));
		address.setCounty(rs.getString("County"));
		address.setOutcode(rs.getString("OutCode"));
		address.setIncode(rs.getString("InCode"));
		address.setCountry(rs.getString("Country"));
	}

	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("Id=? ");
			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);
		}
	}

	public static IdDAO getInstance() {
		if (instance != null) {
			return instance;
		}
		synchronized (IdDAO.class) {
			if (instance == null) {
				IdDAO dao = new IdDAO();
				try {
					dao.init();
					instance = dao;
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
		}
		return instance;
	}
}