package org.oulu.tol.tsarni.workflow.engine.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.oulu.tol.tsarni.workflow.engine.ApplicationContext;
import org.oulu.tol.tsarni.workflow.engine.entities.Action;
import org.oulu.tol.tsarni.workflow.engine.entities.KitchenObject;
import org.oulu.tol.tsarni.workflow.engine.entities.TemplateManner;
import org.oulu.tol.tsarni.workflow.engine.entities.OntologyTable;
import org.oulu.tol.tsarni.workflow.engine.entities.ProcessModel;
import org.oulu.tol.tsarni.workflow.engine.entities.Task;
import org.oulu.tol.tsarni.workflow.engine.entities.TemplateAction;
import org.oulu.tol.tsarni.workflow.engine.entities.TemplateTask;
import org.oulu.tol.tsarni.workflow.engine.localization.LocalizationUtil;

import com.vaadin.data.util.IndexedContainer;
import com.vaadin.ui.AbstractSelect.ItemCaptionMode;
import com.vaadin.ui.Notification.Type;
import com.vaadin.ui.ComboBox;
import com.vaadin.ui.TextField;

public abstract class ProcessModelProvider {
	
	public static final int GATHER_ITEMS_FIRST = 0;
	public static final int SPLIT_EACH_STEP = 1; 
	
	public static final String SELECT_TASKS_SQL = "SELECT * FROM task t, action a WHERE t.process_id=? AND a.task_id= t.id";
	public static final String SELECT_ACTIONS_SQL = "SELECT a.id,a.type_id,a.sequence,a.object_id,a.location_object_id,a.instrument_id,a.duration,a.cue_id d.ontology_id AS manner FROM action a,ontology d,manner m WHERE a.task = ? AND a.manner_id = m.id AND d.reference = m.ontology_id  ORDER BY a.sequence ASC";
	public static final String SELECT_TASK_TEMPLATES_SQL = "SELECT t.id,d.definition AS name,d1.definition AS description FROM task_template t, ontology d, ontology d1 WHERE d.reference = t.ontology_id  AND d.language=? AND t.description=d1.reference";
	public static final String SELECT_OBJECTS_OF_TYPE_SQL = "SELECT id,name FROM object WHERE kitchen_model=? AND type_id=? ORDER BY name ASC";
	public static final String SELECT_INSTRUMENTS_OF_TYPE_SQL = "SELECT * FROM instrument WHERE kitchen_model=? AND type_id=? ORDER BY name ASC";
	public static final String SELECT_PROCESSES_SQL = "SELECT * FROM processes WHERE owner=? ORDER BY modified ASC";
	public static final String INSERT_PROCESSES_SQL = "INSERT INTO processes (id,owner,modified,name,duration,kitchen_id,cooking_strategy) VALUES(?,?,?,?,?,?,?)";
	public static final String UPDATE_PROCESSES_SQL = "UPDATE processes SET owner=?,modified=?,name=?,duration=?,kitchen_id=?,cooking_strategy=? where id=?";
	public static final String UPDATE_TASK_SQL = "UPDATE task SET owner=?,modified=?,process_id=?,sequence=?,name=?,description=?,task_template_id=? where id=?";
	public static final String INSERT_TASK_SQL = "INSERT INTO task id,owner,modified,process_id,sequence,name,description,task_template_id VALUES (?,?,?,?,?,?,?,?)";
	public static final String INSERT_ACTION_SQL = "INSERT INTO action id,type_id,task_id,sequence,object_id,location_object_id,instrument_id,manner_id,cue_id,duration VALUES (?,?,?,?,?,?,?,?,?,?)";
	public static final String UPDATE_ACTION_SQL = "UPDATE action SET type_id=?,task_id=?,sequence=?,object_id=?,location_object_id=?,instrument_id=?,manner_id=?,cue_id=?,duration=? where id=?";
	public static final String INSERT_TEMPLATE_TASK_SQL = "INSERT INTO task_template id,ontology_id,description,duration VALUES (?,?,?,?)";
	public static final String INSERT_TEMPLATE_TASK_ACTION_SQL = "INSERT INTO task_template_action task_template_id,sequence,action_template_id VALUES (?,?,?)";
	
	public static final String INSERT_TEMPLATE_ACTION_SQL = "INSERT INTO task_template_actions id,task_template_id,sequence,type_id,template_object_id,instrument_template_id,manner_id,duration,location_template_id,ontology_id,end_state VALUES (?,?,?,?,?,?,?,?,?,?,?)";
	public static final String SELECT_ALL_ONTOLOGIES_BY_DEFINTION_SQL = "SELECT count(o.id) FROM ontology o, object_template ot WHERE ot.ontology_id=o.reference AND o.language=? AND o.definition=?";
	public static final String DELETE_ONTOLOGY_BY_REFERENCE_SQL = "DELETE FROM ontology WHERE reference=?";
	public static final String DELETE_TEMPLATE_TASK_SQL = "DELETE FROM task_template tt,task_template_actions tta WHERE tta.task_template_id=tt.id AND tt.id=?";
	public static final String DELETE_TEMPLATE_ACTION_SQL = "DELETE FROM action_template at WHERE at.id=?";
	
	public static final String SELECT_ACTION_ONTOLOGIES_SQL = "SELECT o.definition,o.reference FROM ontology o, task_template_actions tta WHERE tta.ontology_id=o.reference AND o.language=?";
	
	public static final String DELETE_OBJECT_ONTOLOGY_BY_REFERENCE_SQL = "DELETE FROM ontology o,object_template ot WHERE o.reference=? AND ot.ontology_id=o.reference";
	public static final String DELETE_INSTRUMENT_ONTOLOGY_BY_REFERENCE_SQL = "DELETE FROM ontology o,instrument_template ot WHERE o.reference=? AND ot.ontology_id=o.reference";
	public static final String DELETE_MANNER_ONTOLOGY_BY_REFERENCE_SQL = "DELETE FROM ontology o,manner_template ot WHERE o.reference=? AND ot.ontology_id=o.reference";
	public static final String DELETE_TASK_ONTOLOGY_BY_REFERENCE_SQL = "DELETE FROM ontology o,task_template ot WHERE o.reference=? AND ot.ontology_id=o.reference";
	public static final String DELETE_ACTION_ONTOLOGY_BY_REFERENCE_SQL = "DELETE FROM ontology o,task_template_actions ot WHERE o.reference=? AND ot.ontology_id=o.reference";
	
	public static final String INSERT_MANNER_TEMPLATE_ONTOLGY_SQL = "INSERT INTO manner_template(id, ontology_id, cue, description) VALUES (?, ?, ?, ?)";
	
	public static final String SELECT_TASK_TEMPLATE_ACTIONS_SQL = ""
			+ "SELECT a.id,a.type_id,ot.ontology_id as object,lt.ontology_id as location,a.end_state,a.duration"
			+ "it.ontology_id as instrument,tta.sequence, mt.ontology_id as manner FROM task_template_actions tta, action_template a,"
			+ "object_template ot, object_template lt, instrument_template it, manner_template mt WHERE a.id = tta.action_template_id"
			+ "AND ot.id=a.object_template_id AND mt.id=a.manner_template_id"
			+ "AND lt.id=a.location_template_id AND it.id=a.instrument_template_id  "
			+ "AND tta.task_template_id=?  ORDER BY a.sequence ASC";
	
	public static final String SELECT_PRIMARY_ONTOLOGY_SQL = "SELECT o.definition FROM ontology o WHERE 0.reference=? AND o.language=? AND o.is_primary_term=true";
	
	
	public static List<ProcessModel> getModelsByOwner (long user) {
		Connection conn = ApplicationContext.getDBConnection();
		ArrayList<ProcessModel> models = new ArrayList<ProcessModel>();
		try {
			PreparedStatement selectModels = conn.prepareStatement(SELECT_PROCESSES_SQL);
			selectModels.setLong(1, user);
			ResultSet selectModelsResult = selectModels.executeQuery();
			while (selectModelsResult.next()) {
				ProcessModel model = new ProcessModel();
				model.setId(selectModelsResult.getLong("id"));
				model.setName(selectModelsResult.getString("name"));
				model.setDuration(selectModelsResult.getLong("duration"));
				model.setCookingStrategy(selectModelsResult.getInt("cooking_strategy"));
				model.setKitchenId(selectModelsResult.getLong("kitchen_id"));
				models.add(model);
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			ApplicationContext.createNotification(LocalizationUtil.getLocalization("kitchen.model.notification.sql.exception"),  Type.ERROR_MESSAGE );
		}
		return models;
	}
	
	public static void saveProcessModel(final ProcessModel processModel) {
		Connection conn = ApplicationContext.getDBConnection();
		try {
			PreparedStatement insertProcessModel = conn.prepareStatement(INSERT_PROCESSES_SQL);
			processModel.setId(KitchenModelProvider.getNextIncrement(conn));
			insertProcessModel.setLong(1, processModel.getId());
			insertProcessModel.setLong(2, ApplicationContext.getUser().getId());
			insertProcessModel.setLong(3, ApplicationContext.getTimeStamp());
			insertProcessModel.setString(4, processModel.getName());
			insertProcessModel.setLong(5,processModel.getDuration());
			insertProcessModel.setLong(6, processModel.getKitchenId());
			insertProcessModel.setLong(7, processModel.getCookingStrategy());
			insertProcessModel.executeUpdate();
			
			for (Task task:processModel.getTasks()) {
				if (task.getId() > 0) updateProcessTask(task,conn);
				else {
					task.setProcessId(processModel.getId());
					saveProcessTask(task,conn);
				}
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void updateProcessModel(final ProcessModel processModel) {
		Connection conn = ApplicationContext.getDBConnection();
		try {
			PreparedStatement insertProcessModel = conn.prepareStatement(UPDATE_PROCESSES_SQL);
			processModel.setId(KitchenModelProvider.getNextIncrement(conn));
			insertProcessModel.setLong(1, ApplicationContext.getUser().getId());
			insertProcessModel.setLong(2, ApplicationContext.getTimeStamp());
			insertProcessModel.setString(3, processModel.getName());
			insertProcessModel.setLong(4,processModel.getDuration());
			insertProcessModel.setLong(5, processModel.getKitchenId());
			insertProcessModel.setLong(6, processModel.getCookingStrategy());
			insertProcessModel.setLong(7, processModel.getId());
			insertProcessModel.executeUpdate();	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	@SuppressWarnings("unchecked")
	public static IndexedContainer  getActionTemplateOntologies() {
		IndexedContainer container = new IndexedContainer();
		container.addContainerProperty("definition", String.class, null);
		container.addContainerProperty("reference", Long.class, null);

		Connection conn = ApplicationContext.getDBConnection();
		try {
			PreparedStatement selectObjectOntologies = conn.prepareStatement(SELECT_ACTION_ONTOLOGIES_SQL);
			selectObjectOntologies.setString(1, ApplicationContext.getLocale().getISO3Country());
			ResultSet selectTaskTemplateActionResults = selectObjectOntologies.executeQuery();

			while (selectTaskTemplateActionResults.next()) {
				long ref = selectTaskTemplateActionResults.getLong("reference");
				container.addItem(ref);
				container.getItem(ref).getItemProperty("definition").setValue(selectTaskTemplateActionResults.getString("definition"));
				container.getItem(ref).getItemProperty("reference").setValue(selectTaskTemplateActionResults.getLong("reference"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return container;
	}
	
	public static String getPrimaryOntology(long referenceId, Connection conn) {
		if (conn == null) conn = ApplicationContext.getDBConnection();
		try {
			PreparedStatement selectPrimaryOntology = conn.prepareStatement(SELECT_PRIMARY_ONTOLOGY_SQL);
			selectPrimaryOntology.setLong(1, referenceId);
			selectPrimaryOntology.setString(2, ApplicationContext.getLocale().getISO3Country());
			ResultSet selectTaskTemplateActionResults = selectPrimaryOntology.executeQuery();
			if (selectTaskTemplateActionResults.next()) {
				return selectTaskTemplateActionResults.getString("definition");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return "";
	}
	
	@SuppressWarnings("unchecked")
	public static IndexedContainer  getTaskTemplateAction(long templateId) {
		IndexedContainer container = new IndexedContainer();
		
		container.addContainerProperty("sequence", Integer.class, null);
		container.addContainerProperty("action", ComboBox.class, null);
		container.addContainerProperty("object", String.class, null);
		container.addContainerProperty("location", String.class, null);
		container.addContainerProperty("end_state", String.class, null);
		container.addContainerProperty("instrument", String.class, null);
		container.addContainerProperty("manner", String.class, null);
		container.addContainerProperty("duration", String.class, null);
		container.addContainerProperty("type", String.class, null);
		if (templateId == 0L) return container;
		Connection conn = ApplicationContext.getDBConnection();
		try {
			PreparedStatement selectTaskTemplateActions = conn.prepareStatement(SELECT_TASK_TEMPLATE_ACTIONS_SQL);
			selectTaskTemplateActions.setLong(1, templateId);
			ResultSet selectTaskTemplateActionResults = selectTaskTemplateActions.executeQuery();
		
			
			while(selectTaskTemplateActionResults.next()) {
				long id = selectTaskTemplateActionResults.getLong("id");
				container.addItem(id);
				container.getItem(id).getItemProperty("sequence").setValue(selectTaskTemplateActionResults.getInt("sequence"));
				container.getItem(id).getItemProperty("object").setValue(getPrimaryOntology(selectTaskTemplateActionResults.getLong("object"),conn));
				container.getItem(id).getItemProperty("location").setValue(getPrimaryOntology(selectTaskTemplateActionResults.getLong("location"),conn));
				container.getItem(id).getItemProperty("instrument").setValue(getPrimaryOntology(selectTaskTemplateActionResults.getLong("instrument"),conn));
				container.getItem(id).getItemProperty("end_state").setValue(KitchenObject.getTitle(selectTaskTemplateActionResults.getInt("end_state")));
				container.getItem(id).getItemProperty("type").setValue(Action.getTitle(selectTaskTemplateActionResults.getInt("type_id")));
				container.getItem(id).getItemProperty("manner").setValue(getPrimaryOntology(selectTaskTemplateActionResults.getLong("manner"),conn));
				container.getItem(id).getItemProperty("duration").setValue(getDuration(selectTaskTemplateActionResults.getLong("duration")));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return container;
	}
	
	/*
	 * This method is meant for creating a new task based on existing template that is suitable for objects in selected 
	 * kitchen model.  
	 */
	@SuppressWarnings("unchecked")
	public static IndexedContainer  createNewTaskBasedOnTemplate(long templateId,long kitchenModelId) {
		IndexedContainer container = new IndexedContainer();
		container.addContainerProperty("sequence", Integer.class, null);
		container.addContainerProperty("action", String.class, null);
		container.addContainerProperty("object", ComboBox.class, null);
		container.addContainerProperty("location", ComboBox.class, null);
		container.addContainerProperty("state", String.class, null);
		container.addContainerProperty("instrument", ComboBox.class, null);
		container.addContainerProperty("manner", String.class, null);
		container.addContainerProperty("duration", String.class, null);
		
		Connection conn = ApplicationContext.getDBConnection();
		try {
			PreparedStatement selectTaskTemplateActions = conn.prepareStatement(SELECT_TASK_TEMPLATE_ACTIONS_SQL);
			selectTaskTemplateActions.setLong(1, templateId);
			ResultSet selectTaskTemplateActionResults = selectTaskTemplateActions.executeQuery();
		
			
			while(selectTaskTemplateActionResults.next()) {
				long id = selectTaskTemplateActionResults.getLong("id");
				container.addItem(id);
			
				container.getItem(id).getItemProperty("sequence").setValue(selectTaskTemplateActionResults.getInt("sequence"));
				container.getItem(id).getItemProperty("action").setValue(Action.getTitle(selectTaskTemplateActionResults.getInt("type")));
				container.getItem(id).getItemProperty("object").setValue(getObjectSelectionCombobox(conn,kitchenModelId, selectTaskTemplateActionResults.getInt("object")));
				container.getItem(id).getItemProperty("location").setValue(getObjectSelectionCombobox(conn,kitchenModelId, selectTaskTemplateActionResults.getInt("location")));
				container.getItem(id).getItemProperty("instrument").setValue(getInstrumentSelectionCombobox(conn, kitchenModelId, selectTaskTemplateActionResults.getInt("instrument")));
				container.getItem(id).getItemProperty("state").setValue(KitchenObject.getTitle(selectTaskTemplateActionResults.getInt("state")));
				container.getItem(id).getItemProperty("manner").setValue(selectTaskTemplateActionResults.getString("manner"));
				container.getItem(id).getItemProperty("duration").setValue(getDuration(selectTaskTemplateActionResults.getLong("duration")));

			}
			ApplicationContext.createNotification(LocalizationUtil.getLocalization("generic.notification.load.success.content"),Type.TRAY_NOTIFICATION);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return container;
	}
	
	@SuppressWarnings("unchecked")
	public static ComboBox getObjectSelectionCombobox(Connection conn,long kitchenModelId, int type)  {

		if (conn == null) conn = ApplicationContext.getDBConnection();
		ComboBox objectSelection = new ComboBox();
		objectSelection.setNullSelectionAllowed(false);
		IndexedContainer container = new IndexedContainer();
		container.addContainerProperty("name", String.class, null);
		
		if (type == 0) {
			container.addItem(0L);
			container.getItem(0L).getItemProperty("name").setValue(KitchenObject.getTitle(type));
			objectSelection.setContainerDataSource(container);
			objectSelection.setItemCaptionMode(ItemCaptionMode.PROPERTY);
			objectSelection.setItemCaptionPropertyId("name");
			objectSelection.setEnabled(false);
		}
		
		
		try {
			PreparedStatement selectObjects = conn.prepareStatement(SELECT_OBJECTS_OF_TYPE_SQL);
			selectObjects.setLong(1, kitchenModelId);
			selectObjects.setLong(2, type);
			ResultSet selectObjectsResults = selectObjects.executeQuery();
		
			
			while(selectObjectsResults.next()) {
				container.addItem(selectObjectsResults.getLong("id"));
				container.getItem(selectObjectsResults.getLong("id")).getItemProperty("name").setValue(selectObjectsResults.getString("name"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		objectSelection.setContainerDataSource(container);
		objectSelection.setItemCaptionMode(ItemCaptionMode.PROPERTY);
		objectSelection.setItemCaptionPropertyId("name");
		return objectSelection;
	}
	
	@SuppressWarnings("unchecked")
	public static ComboBox getInstrumentSelectionCombobox(Connection conn,long modelId, int type) throws SQLException {

		ComboBox objectSelection = new ComboBox();
		objectSelection.setNullSelectionAllowed(false);
		IndexedContainer container = new IndexedContainer();
		container.addContainerProperty("name", String.class, null);
		
		if (type == 0) {
			container.addItem(0L);
			container.getItem(0L).getItemProperty("name").setValue(KitchenObject.getTitle(type));
			objectSelection.setContainerDataSource(container);
			objectSelection.setItemCaptionMode(ItemCaptionMode.PROPERTY);
			objectSelection.setItemCaptionPropertyId("name");
			objectSelection.setEnabled(false);
		}
		
		PreparedStatement selectObjects = conn.prepareStatement(SELECT_INSTRUMENTS_OF_TYPE_SQL);
		selectObjects.setLong(1, modelId);
		selectObjects.setLong(2, type);
		ResultSet selectObjectsResults = selectObjects.executeQuery();
	
		
		while(selectObjectsResults.next()) {
			container.addItem(selectObjectsResults.getLong("id"));
			container.getItem(selectObjectsResults.getLong("id")).getItemProperty("name").setValue(selectObjectsResults.getString("name"));
		}
		
		objectSelection.setContainerDataSource(container);
		objectSelection.setItemCaptionMode(ItemCaptionMode.PROPERTY);
		objectSelection.setItemCaptionPropertyId("name");
		return objectSelection;
	}
	
	public static String getDuration(long ms) {
		return (new SimpleDateFormat("mm:ss")).format(new Date(ms));
	}
	
	@SuppressWarnings("unchecked")
	public static IndexedContainer loadCookingStrategyContainer () {
		IndexedContainer container = new IndexedContainer();
		container.addContainerProperty("name", String.class, null);
		
		container.addItem(GATHER_ITEMS_FIRST);
		container.getItem(GATHER_ITEMS_FIRST).getItemProperty("name").setValue(LocalizationUtil.getLocalization("recipe.model.strategy.gather.caption"));
		container.addItem(SPLIT_EACH_STEP);
		container.getItem(SPLIT_EACH_STEP).getItemProperty("name").setValue(LocalizationUtil.getLocalization("recipe.model.strategy.split.caption"));
		
		return container;
	}
	
	@SuppressWarnings("unchecked")
	public static IndexedContainer loadTaskTemplates () {
		IndexedContainer container = new IndexedContainer();
		container.addContainerProperty("name", String.class, null);
		container.addContainerProperty("description", String.class, null);
		Connection conn = ApplicationContext.getDBConnection();
		try {
			PreparedStatement selectTaskTemplates = conn.prepareStatement(SELECT_TASK_TEMPLATES_SQL);
			selectTaskTemplates.setString(1, "fin");
			ResultSet selectTaskTemplatesResults = selectTaskTemplates.executeQuery();
		
			
			while(selectTaskTemplatesResults.next()) {
				long id = selectTaskTemplatesResults.getLong("id");
				container.addItem(id);
				container.getItem(id).getItemProperty("name").setValue(selectTaskTemplatesResults.getString("name"));
				container.getItem(id).getItemProperty("description").setValue(selectTaskTemplatesResults.getString("description"));
			}
			ApplicationContext.createNotification(LocalizationUtil.getLocalization("generic.notification.load.success.content"),Type.TRAY_NOTIFICATION);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return container;
	}
	
	@SuppressWarnings("unchecked")
	public static IndexedContainer loadProcessTasks(long processId) {
		IndexedContainer container = new IndexedContainer();
		container.addContainerProperty("Sequence", Integer.class, null);
		container.addContainerProperty("Name", String.class, null);
		container.addContainerProperty("Description", String.class, null);
		container.addContainerProperty("Template", String.class, null);
		container.addContainerProperty("Task", Task.class, null);
		Connection conn = ApplicationContext.getDBConnection();
		
		try {
			PreparedStatement selectTasks = conn.prepareStatement(SELECT_TASKS_SQL);
			selectTasks.setLong(1, processId);
			ResultSet selectTaskResults = selectTasks.executeQuery();
			
			while(selectTaskResults.next()) {
				long id = selectTaskResults.getLong("id");
				container.addItem(id);
				container.getItem(id).getItemProperty("Name").setValue(selectTaskResults.getString("name"));
				container.getItem(id).getItemProperty("Description").setValue(selectTaskResults.getString("description"));
				container.getItem(id).getItemProperty("Sequence").setValue(selectTaskResults.getInt("sescription"));
				container.getItem(id).getItemProperty("Template").setValue(Long.toString(selectTaskResults.getLong("template")));
			}
			ApplicationContext.createNotification(LocalizationUtil.getLocalization("generic.notification.load.success.content"),Type.TRAY_NOTIFICATION);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return container;
	}
	
	@SuppressWarnings("unchecked")
	public static IndexedContainer loadTaskActions(long taskId, long kitchenModelId) {
		IndexedContainer container = new IndexedContainer();
		container.addContainerProperty("sequence", Integer.class, null);
		container.addContainerProperty("name", TextField.class, null);
		container.addContainerProperty("object", ComboBox.class, null);
		container.addContainerProperty("location", ComboBox.class, null);
		container.addContainerProperty("instrument", ComboBox.class, null);
		container.addContainerProperty("manner", ComboBox.class, null);
		container.addContainerProperty("duration", TextField.class, null);
		if (taskId < 0L) return container; // Return empty but initialized container when temporaryId is being used
		
		Connection conn = ApplicationContext.getDBConnection();
		
		try {
			PreparedStatement selectActions = conn.prepareStatement(SELECT_ACTIONS_SQL);
			selectActions.setLong(1, taskId);
			ResultSet selectActionsResults = selectActions.executeQuery();
			
			while(selectActionsResults.next()) {
				long id = selectActionsResults.getLong("id");
				container.addItem(id);
				container.getItem(id).getItemProperty("sequence").setValue(selectActionsResults.getInt("sequence"));
				container.getItem(id).getItemProperty("name").setValue(Action.getTitle(selectActionsResults.getInt("type")));
				container.getItem(id).getItemProperty("object").setValue(getObjectSelectionCombobox(conn,kitchenModelId, selectActionsResults.getInt("object")));
				container.getItem(id).getItemProperty("location").setValue(getObjectSelectionCombobox(conn,kitchenModelId, selectActionsResults.getInt("location")));
				container.getItem(id).getItemProperty("instrument").setValue(getInstrumentSelectionCombobox(conn, kitchenModelId, selectActionsResults.getInt("instrument")));
				//container.getItem(id).getItemProperty("manner").setValue(selectActionsResults.getString("manner"));
				container.getItem(id).getItemProperty("duration").setValue(getDuration(selectActionsResults.getLong("duration")));
				//cue missing
			}
			ApplicationContext.createNotification(LocalizationUtil.getLocalization("generic.notification.load.success.content"),Type.TRAY_NOTIFICATION);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return container;
	}
	
	public static void updateProcessTask(Task task, Connection conn) {
		if (conn == null) conn = ApplicationContext.getDBConnection();
		
		try {
			PreparedStatement updateTask = conn.prepareStatement(UPDATE_TASK_SQL);
			task.setId(KitchenModelProvider.getNextIncrement(conn));
			
			updateTask.setLong(1, ApplicationContext.getUser().getId());
			updateTask.setLong(2, ApplicationContext.getTimeStamp());
			updateTask.setLong(3, task.getProcessId());
			updateTask.setInt(4, task.getSequence());
			updateTask.setString(5, task.getName());
			updateTask.setLong(6, task.getTemplateId());
			updateTask.setLong(7, task.getId());
			updateTask.execute();
			
			for (Action action:task.getActions()) {
				if (action.getId() > 0) updateTaskAction(action,conn);
				else {
					action.setTaskId(task.getId());
					saveTaskAction(action,conn);
				}
			}
			ApplicationContext.createNotification(LocalizationUtil.getLocalization("generic.notification.save.success.content"),Type.TRAY_NOTIFICATION);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	
	
	public static void saveMannerTemplate(final TemplateManner templateManner)  {
		
		Connection conn = ApplicationContext.getDBConnection();
		try {
			long referenceId =  KitchenModelProvider.getNextIncrement(conn);
			KitchenModelProvider.saveOntologyDefinition(templateManner.getDescription(), ApplicationContext.getLocale().getISO3Country(),  referenceId, true, conn);
			PreparedStatement insertTemplateManner = conn.prepareStatement(INSERT_MANNER_TEMPLATE_ONTOLGY_SQL);
			insertTemplateManner.setLong(1, KitchenModelProvider.getNextIncrement(conn));
			insertTemplateManner.setLong(2, KitchenModelProvider.saveOntologyMap(templateManner.getOntologyMap(),templateManner.getPrimaryDefinitions(),conn));
			insertTemplateManner.setLong(3, templateManner.getCue());
			insertTemplateManner.setLong(4, referenceId);
			insertTemplateManner.executeUpdate();
			ApplicationContext.createNotification(LocalizationUtil.getLocalization("generic.notification.save.success.content"),Type.TRAY_NOTIFICATION);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void saveTemplateAction(TemplateAction action, Connection conn) {
		if (conn == null) conn = ApplicationContext.getDBConnection();
		try {
			long ontologyId = KitchenModelProvider.saveOntologyMap(action.getOntologyMap(),action.getPrimaryDefinitions(), conn);
			PreparedStatement insertAction = conn.prepareStatement(INSERT_TEMPLATE_ACTION_SQL);
			insertAction.setLong(1, KitchenModelProvider.getNextIncrement(conn));
			insertAction.setLong(2, action.getTaskId()  );
			insertAction.setLong(3, action.getSequence());
			insertAction.setInt(4, action.getType());
			insertAction.setLong(5, action.getObject());
			insertAction.setLong(6, action.getInstrument());
			insertAction.setLong(7, action.getManner());
			insertAction.setLong(8, action.getDuration());
			insertAction.setLong(9, action.getLocation());
			insertAction.setLong(10,ontologyId);
			insertAction.setInt(11, action.getEndState());
			insertAction.execute();
			ApplicationContext.createNotification(LocalizationUtil.getLocalization("generic.notification.save.success.content"),Type.TRAY_NOTIFICATION);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void deleteActionTemplate(long actionId) {
		Connection conn = ApplicationContext.getDBConnection();
		try {
			PreparedStatement deleteOntology = conn.prepareStatement(DELETE_TEMPLATE_ACTION_SQL);
			deleteOntology.setLong(1, actionId);
			deleteOntology.executeQuery();
			ApplicationContext.createNotification(LocalizationUtil.getLocalization("generic.notification.delete.success.content"),Type.TRAY_NOTIFICATION);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void deleteTaskTemplate(long taskId) {
		Connection conn = ApplicationContext.getDBConnection();
		try {
			PreparedStatement deleteOntology = conn.prepareStatement(DELETE_TEMPLATE_TASK_SQL);
			deleteOntology.setLong(1, taskId);
			deleteOntology.executeQuery();
			ApplicationContext.createNotification(LocalizationUtil.getLocalization("generic.notification.delete.success.content"),Type.TRAY_NOTIFICATION);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	
	public static void deleteOntology(long referenceId, int type) {
		Connection conn = ApplicationContext.getDBConnection();
		String sql = null;
		switch (type) {
		case OntologyTable.OBJECT_TEMPLATE_ONTOLOGY: sql = DELETE_OBJECT_ONTOLOGY_BY_REFERENCE_SQL;
			break;
		case OntologyTable.ACTIONS_TEMPLATE_ONTOLOGY: sql = DELETE_ACTION_ONTOLOGY_BY_REFERENCE_SQL;
			break;
		case OntologyTable.MANNER_TEMPLATE_ONTOLOGY: sql = DELETE_MANNER_ONTOLOGY_BY_REFERENCE_SQL;
			break;
		case OntologyTable.TASK_TEMPLATE_ONTOLOGY: sql = DELETE_TASK_ONTOLOGY_BY_REFERENCE_SQL;
			break;
		case OntologyTable.INSTRUMENT_TEMPLATE_ONTOLOGY: sql = DELETE_INSTRUMENT_ONTOLOGY_BY_REFERENCE_SQL;
		break;
		}
		
		if (sql != null) {
			try {
				PreparedStatement deleteOntology = conn.prepareStatement(sql);
				deleteOntology.setLong(1, referenceId);
				deleteOntology.executeQuery();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	
		
	}
	
	public static boolean validateOntology(String definition,String language) {
		if (definition == null || definition.trim().isEmpty()) return false;
		Connection conn = ApplicationContext.getDBConnection();
		try {
			PreparedStatement checkDuplicateSelect = conn.prepareStatement(SELECT_ALL_ONTOLOGIES_BY_DEFINTION_SQL);
			checkDuplicateSelect.setString(1, language);
			checkDuplicateSelect.setString(2, definition);
			ResultSet checkDuplicateSelectResult = checkDuplicateSelect.executeQuery();
			while (checkDuplicateSelectResult.next()) {
				if (checkDuplicateSelectResult.getInt(1) > 0) return false;
			}

			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return true;
	}
	
	public static void saveTaskTemplate(TemplateTask task, Connection conn) {
		if (conn == null) conn = ApplicationContext.getDBConnection();
		
		try {
			PreparedStatement insertTask = conn.prepareStatement(INSERT_TEMPLATE_TASK_SQL);
			task.setId(KitchenModelProvider.getNextIncrement(conn));
			long ontologyId = KitchenModelProvider.saveOntologyMap(task.getOntologyMap(),task.getPrimaryDefinitions(), conn);
			insertTask.setLong(1, task.getId());
			insertTask.setLong(2, ontologyId);
			insertTask.setString(3, task.getDescription());
			insertTask.setLong(4, task.getDuration());
			insertTask.execute();
			
			for (Object itemId:task.getTaskTemplateActions().getItemIds()) {
				long actionId = (Long) itemId;
				int sequence = (Integer)task.getTaskTemplateActions().getItem(itemId).getItemProperty("sequence").getValue();
				ProcessModelProvider.saveTaskTemplateAction(actionId,task.getId(),sequence, conn);
			}
			
			ApplicationContext.createNotification(LocalizationUtil.getLocalization("generic.notification.save.success.content"),Type.TRAY_NOTIFICATION);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void saveTaskTemplateAction(long actionId,long taskId, int sequence, Connection conn) {
		if (conn == null) conn = ApplicationContext.getDBConnection();
		
		try {
			PreparedStatement insertTask = conn.prepareStatement(INSERT_TEMPLATE_TASK_ACTION_SQL);
			insertTask.setLong(1, taskId);
			insertTask.setInt(2, sequence);
			insertTask.setLong(3,actionId);
			insertTask.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		

	}
	
	public static void saveProcessTask(Task task, Connection conn) {
		if (conn == null) conn = ApplicationContext.getDBConnection();
		
		try {
			PreparedStatement insertTask = conn.prepareStatement(INSERT_TASK_SQL);
			task.setId(KitchenModelProvider.getNextIncrement(conn));
			insertTask.setLong(1, task.getId());
			insertTask.setLong(2, ApplicationContext.getUser().getId());
			insertTask.setLong(3, ApplicationContext.getTimeStamp());
			insertTask.setLong(4, task.getProcessId());
			insertTask.setInt(5, task.getSequence());
			insertTask.setString(6, task.getName());
			insertTask.setLong(7, task.getTemplateId());
			insertTask.execute();
			
			for (Action action:task.getActions()) {
				action.setTaskId(task.getId());
				saveTaskAction(action,conn);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public static void updateTaskAction(Action action, Connection conn) {
		if (conn == null) conn = ApplicationContext.getDBConnection();
		try {
			PreparedStatement insertAction = conn.prepareStatement(UPDATE_ACTION_SQL);
			
			insertAction.setLong(1, action.getType() );
			insertAction.setLong(2, action.getTaskId() );
			insertAction.setInt(3, action.getSequence());
			insertAction.setLong(4, action.getObject());
			insertAction.setLong(5, action.getLocation());
			insertAction.setLong(6, action.getInstrument());
			insertAction.setLong(7, action.getManner());
			insertAction.setLong(8, action.getCue());
			insertAction.setLong(10,action.getDuration());
			insertAction.setLong(9, action.getId());
			insertAction.execute();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void saveTaskAction(Action action, Connection conn) {
		if (conn == null) conn = ApplicationContext.getDBConnection();
		try {
			PreparedStatement insertAction = conn.prepareStatement(INSERT_ACTION_SQL);
			insertAction.setLong(1, KitchenModelProvider.getNextIncrement(conn));
			insertAction.setLong(2, action.getType() );
			insertAction.setLong(3, action.getTaskId() );
			insertAction.setInt(4, action.getSequence());
			insertAction.setLong(5, action.getObject());
			insertAction.setLong(6, action.getLocation());
			insertAction.setLong(7, action.getInstrument());
			insertAction.setLong(8, action.getManner());
			insertAction.setLong(9, action.getCue());
			insertAction.setLong(10,action.getDuration());
			insertAction.execute();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
}
