/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package services;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import services.models.*;

/**
 *
 * @author marc
 */
public class Services {

	private Connection getConnection() throws NamingException, SQLException
	{
		Context context = new InitialContext();
		DataSource ds = (DataSource)context.lookup("java:comp/env/jdbc/test");
		return ds.getConnection();
	}

	public boolean isUsernameAvailable(String username) throws Exception
	{
		boolean retour = false;

		try
		{
			Connection cn = this.getConnection();

			PreparedStatement st = cn.prepareStatement(
				"SELECT 1 FROM users WHERE username = ?"
			);
			st.setString(1, username);

			ResultSet rs = st.executeQuery();
			if (!rs.next()) retour = true;

			rs.close();
			st.close();
			cn.close();
		}
		catch (Exception e) {
			throw new Exception(e.getMessage());
		}

		return retour;
	}

	public boolean isAccessGranted(String username, String password, String uuid, String usbid) throws Exception
	{
		boolean retour = false;

		try
		{
			Connection cn = this.getConnection();

			PreparedStatement st = cn.prepareStatement(
				"SELECT 1 FROM users WHERE username = ? AND password = ? AND uuid = ? AND usbid = ?"
			);
			st.setString(1, username);
			st.setString(2, password);
			st.setString(3, uuid);
			st.setString(4, usbid);

			ResultSet rs = st.executeQuery();
			if (rs.next()) retour = true;

			rs.close();
			st.close();
			cn.close();
		}
		catch (Exception e) {
			throw new Exception(e.getMessage());
		}
		
		return retour;
	}

	public String createNewAccount(String username, String password, String firstname, String lastname, String email, String usbid) throws Exception
	{
		String retour = null;

		try
		{
			Connection cn = this.getConnection();

			retour = UUID.randomUUID().toString();

			PreparedStatement st = cn.prepareStatement(
				"INSERT INTO users VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, 0)"
			);
			st.setString(1, retour);
			st.setString(2, usbid);
			st.setString(3, username);
			st.setString(4, password);
			st.setString(5, firstname);
			st.setString(6, lastname);
			st.setString(7, email);

			st.executeUpdate();

			st.close();
			cn.close();
		}
		catch (Exception e) {
			throw new Exception(e.getMessage());
		}

		return retour;
	}

	public boolean blacklistAccount(String username, String password) throws Exception
	{
		boolean retour = false;

		try
		{
			Connection cn = this.getConnection();

			PreparedStatement st = cn.prepareStatement(
				"UPDATE users SET blacklist = 1 WHERE username = ? AND password = ?"
			);
			st.setString(1, username);
			st.setString(2, password);

			st.executeUpdate();
			retour = true;

			st.close();
			cn.close();
		}
		catch (Exception e) {
			throw new Exception(e.getMessage());
		}

		return retour;
	}

	public int addNewForm(String url, String action, String method, String name, int number) throws Exception
	{
		int retour = 0;

		try
		{
			Connection cn = this.getConnection();

			PreparedStatement st = cn.prepareStatement(
				"INSERT INTO forms VALUES (NULL, ?, ?, ?, ?, ?, NOW(), 0)",
				PreparedStatement.RETURN_GENERATED_KEYS
			);
			st.setString(1, url);
			st.setString(2, action);
			st.setString(3, method);
			st.setString(4, name);
			st.setInt(5, number);

			st.executeUpdate();
			ResultSet rs = st.getGeneratedKeys();
			if (rs.next()) retour = rs.getInt(1);

			rs.close();
			st.close();
			cn.close();
		}
		catch (Exception e) {
			throw new Exception(e.getMessage());
		}

		return retour;
	}

	public int addNewField(int formid, String name, String type, String value, int rank) throws Exception
	{
		int retour = 0;

		try
		{
			Connection cn = this.getConnection();

			PreparedStatement st = cn.prepareStatement(
				"INSERT INTO forms VALUES (NULL, ?, ?, ?, ?, ?)",
				PreparedStatement.RETURN_GENERATED_KEYS
			);
			st.setInt(1, formid);
			st.setString(2, name);
			st.setString(3, type);
			st.setString(4, value);
			st.setInt(5, rank);

			st.executeUpdate();
			ResultSet rs = st.getGeneratedKeys();
			if (rs.next()) retour = rs.getInt(1);

			rs.close();
			st.close();
			cn.close();
		}
		catch (Exception e) {
			throw new Exception(e.getMessage());
		}

		return retour;
	}

	public Global getGlobalFormById(int id) throws Exception
	{
		Global form = null;

		try
		{
			Connection cn = this.getConnection();

			PreparedStatement st1 = cn.prepareStatement(
				"SELECT id, url, action, method, name, number FROM forms WHERE id = ?"
			);
			st1.setInt(1, id);

			ResultSet rs1 = st1.executeQuery();
			if (rs1.next()) {
				Global tmp = new Global (
					rs1.getInt("id"), rs1.getString("url"), rs1.getString("action"),
					rs1.getString("method"), rs1.getString("name"), rs1.getInt("number")
				);

				List<Field> fields = new ArrayList<Field>();

				PreparedStatement st2 = cn.prepareStatement(
					"SELECT id, idform, name, type, value, rank FROM fields WHERE idform = ?"
				);
				st2.setInt(1, rs1.getInt("id"));

				ResultSet rs2 = st2.executeQuery();
				while (rs2.next()) {
					Field field = new Field (
						rs2.getInt("id"), rs2.getInt("idform"), rs2.getString("name"),
						rs2.getString("type"), rs2.getString("value"), rs2.getInt("rank")
					);

					fields.add(field);
				}

				tmp.setFields((Field[]) fields.toArray(new Field[0]));

				rs2.close();
				st2.close();

				form = tmp;
			}

			rs1.close();
			st1.close();
			cn.close();
		}
		catch (Exception e) {
			throw new Exception(e.getMessage());
		}

		return form;
	}

	public Form getFormById(int id) throws Exception
	{
		Form form = null;

		try
		{
			Connection cn = this.getConnection();

			PreparedStatement st = cn.prepareStatement(
				"SELECT id, url, action, method, name, number FROM forms WHERE id = ?"
			);
			st.setInt(1, id);

			ResultSet rs = st.executeQuery();
			if (rs.next()) {
				Form tmp = new Form (
					rs.getInt("id"), rs.getString("url"), rs.getString("action"),
					rs.getString("method"), rs.getString("name"), rs.getInt("number")
				);

				form = tmp;
			}

			rs.close();
			st.close();
			cn.close();
		}
		catch (Exception e) {
			throw new Exception(e.getMessage());
		}

		return form;
	}

	public Field getFieldById(int id) throws Exception
	{
		Field field = null;

		try
		{
			Connection cn = this.getConnection();

			PreparedStatement st = cn.prepareStatement(
				"SELECT id, idform, name, type, value, rank FROM fields WHERE id = ?"
			);
			st.setInt(1, id);

			ResultSet rs = st.executeQuery();
			if (rs.next()) {
				Field tmp = new Field (
					rs.getInt("id"), rs.getInt("idform"), rs.getString("name"),
					rs.getString("type"), rs.getString("value"), rs.getInt("rank")
				);

				field = tmp;
			}

			rs.close();
			st.close();
			cn.close();
		}
		catch (Exception e) {
			throw new Exception(e.getMessage());
		}

		return field;
	}

	public Global[] getGlobalForms(boolean valid) throws Exception
	{
		List<Global> forms = new ArrayList<Global>();

		try
		{
			Connection cn = this.getConnection();

			PreparedStatement st1;
			if (valid) {
				st1 = cn.prepareStatement(
					"SELECT id, url, action, method, name, number FROM forms WHERE valid = 1"
				);
			} else {
				st1 = cn.prepareStatement(
					"SELECT id, url, action, method, name, number FROM forms"
				);
			}

			ResultSet rs1 = st1.executeQuery();
			while (rs1.next()) {
				Global tmp = new Global (
					rs1.getInt("id"), rs1.getString("url"), rs1.getString("action"),
					rs1.getString("method"), rs1.getString("name"), rs1.getInt("number")
				);

				List<Field> fields = new ArrayList<Field>();

				PreparedStatement st2 = cn.prepareStatement(
					"SELECT id, idform, name, type, value, rank FROM fields WHERE idform = ?"
				);
				st2.setInt(1, rs1.getInt("id"));

				ResultSet rs2 = st2.executeQuery();
				while (rs2.next()) {
					Field field = new Field (
						rs2.getInt("id"), rs2.getInt("idform"), rs2.getString("name"),
						rs2.getString("type"), rs2.getString("value"), rs2.getInt("rank")
					);

					fields.add(field);
				}

				tmp.setFields((Field[]) fields.toArray(new Field[0]));

				rs2.close();
				st2.close();

				forms.add(tmp);
			}

			rs1.close();
			st1.close();
			cn.close();
		}
		catch (Exception e) {
			throw new Exception(e.getMessage());
		}

		return (Global[]) forms.toArray(new Global[0]);
	}

	public Form[] getForms(boolean valid) throws Exception
	{
		List<Form> forms = new ArrayList<Form>();

		try
		{
			Connection cn = this.getConnection();

			PreparedStatement st;
			if (valid) {
				st = cn.prepareStatement(
					"SELECT id, url, action, method, name, number FROM forms WHERE valid = ?"
				);
			} else {
				st = cn.prepareStatement(
					"SELECT id, url, action, method, name, number FROM forms"
				);
			}

			ResultSet rs = st.executeQuery();
			while (rs.next()) {
				Form tmp = new Form (
					rs.getInt("id"), rs.getString("url"), rs.getString("action"),
					rs.getString("method"), rs.getString("name"), rs.getInt("number")
				);

				forms.add(tmp);
			}

			rs.close();
			st.close();
			cn.close();
		}
		catch (Exception e) {
			throw new Exception(e.getMessage());
		}

		return (Form[]) forms.toArray(new Form[0]);
	}

	public Field[] getFieldsByForm(int id) throws Exception
	{
		List<Field> fields = new ArrayList<Field>();

		try
		{
			Connection cn = this.getConnection();

			PreparedStatement st = cn.prepareStatement(
				"SELECT id, idform, name, type, value, rank FROM fields WHERE idform = ?"
			);

			ResultSet rs = st.executeQuery();
			while (rs.next()) {
				Field tmp = new Field (
					rs.getInt("id"), rs.getInt("idform"), rs.getString("name"),
					rs.getString("type"), rs.getString("value"), rs.getInt("rank")
				);

				fields.add(tmp);
			}

			rs.close();
			st.close();
			cn.close();
		}
		catch (Exception e) {
			throw new Exception(e.getMessage());
		}

		return (Field[]) fields.toArray(new Field[0]);
	}


}