/*
 * Copyright 2007 - 2012 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package net.sf.jailer.ui;

import java.awt.Color;
import java.awt.Dimension;
import java.awt.Font;
import java.awt.GridBagConstraints;
import java.awt.Image;
import java.awt.Insets;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.io.FileWriter;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.swing.DefaultComboBoxModel;
import javax.swing.ImageIcon;
import javax.swing.JCheckBox;
import javax.swing.JComponent;
import javax.swing.JLabel;
import javax.swing.JTextField;
import javax.swing.event.DocumentEvent;
import javax.swing.event.DocumentListener;

import net.sf.jailer.datamodel.Association;
import net.sf.jailer.datamodel.Column;
import net.sf.jailer.datamodel.DataModel;
import net.sf.jailer.datamodel.Table;
import net.sf.jailer.util.SqlUtil;

/**
 * Query Builder Dialog.
 * 
 * @author Ralf Wisser
 */
public class QueryBuilderDialog extends javax.swing.JDialog {

	/** Creates new form QueryBuilderDialog */
	public QueryBuilderDialog(java.awt.Frame parent) {
		super(parent, true);
		initComponents();
		distinctCheckBox.addItemListener(new ItemListener() {
			@Override
			public void itemStateChanged(ItemEvent e) {
				selectDistinct = distinctCheckBox.isSelected();
				updateSQL();
			}
		});
		mlmTextField.getDocument().addDocumentListener(new DocumentListener() {
			@Override
			public void changedUpdate(DocumentEvent e) {
				appendMLM(mlmTextField.getText());
			}

			@Override
			public void insertUpdate(DocumentEvent e) {
				appendMLM(mlmTextField.getText());
			}

			@Override
			public void removeUpdate(DocumentEvent e) {
				appendMLM(mlmTextField.getText());
			}
		});
		sqlTextArea.setContentType("text/sql");
		sqlEditButton.setVisible(false);
		pack();
		setSize(Math.max(700, getWidth()), 500);
		UIUtil.initPeer();
	}

	/**
	 * This method is called from within the constructor to initialize the form.
	 * WARNING: Do NOT modify this code. The content of this method is always
	 * regenerated by the Form Editor.
	 */
	// <editor-fold defaultstate="collapsed"
	// desc="Generated Code">//GEN-BEGIN:initComponents
	private void initComponents() {
		java.awt.GridBagConstraints gridBagConstraints;

		relationshipsPanel = new javax.swing.JPanel();
		jPanel1 = new javax.swing.JPanel();
		jPanel3 = new javax.swing.JPanel();
		jScrollPane2 = new javax.swing.JScrollPane();
		sqlTextArea = new javax.swing.JEditorPane();
		jPanel2 = new javax.swing.JPanel();
		joinAWithBButton = new javax.swing.JButton();
		saveButton = new javax.swing.JButton();
		clipboardSingleLineButton = new javax.swing.JButton();
		jPanel4 = new javax.swing.JPanel();
		jLabel1 = new javax.swing.JLabel();
		mlmTextField = new javax.swing.JTextField();
		clipboardButton = new javax.swing.JButton();
		sqlEditButton = new javax.swing.JButton();
		jPanel5 = new javax.swing.JPanel();
		jLabel2 = new javax.swing.JLabel();
		distinctCheckBox = new javax.swing.JCheckBox();

		setDefaultCloseOperation(javax.swing.WindowConstants.DISPOSE_ON_CLOSE);
		setTitle("Query Builder");
		getContentPane().setLayout(new java.awt.GridBagLayout());

		relationshipsPanel.setBorder(javax.swing.BorderFactory
				.createEmptyBorder(1, 1, 1, 1));
		relationshipsPanel.setLayout(new java.awt.GridBagLayout());
		gridBagConstraints = new java.awt.GridBagConstraints();
		gridBagConstraints.gridx = 1;
		gridBagConstraints.gridy = 1;
		gridBagConstraints.fill = java.awt.GridBagConstraints.BOTH;
		gridBagConstraints.weightx = 1.0;
		gridBagConstraints.insets = new java.awt.Insets(0, 4, 0, 0);
		getContentPane().add(relationshipsPanel, gridBagConstraints);

		jPanel1.setBorder(javax.swing.BorderFactory.createTitledBorder(
				javax.swing.BorderFactory.createEmptyBorder(1, 1, 1, 1),
				"SQL Query",
				javax.swing.border.TitledBorder.DEFAULT_JUSTIFICATION,
				javax.swing.border.TitledBorder.DEFAULT_POSITION,
				new java.awt.Font("DejaVu Sans", 0, 12), new java.awt.Color(86,
						82, 125))); // NOI18N
		jPanel1.setLayout(new java.awt.GridBagLayout());

		jPanel3.setLayout(new java.awt.GridBagLayout());
		gridBagConstraints = new java.awt.GridBagConstraints();
		gridBagConstraints.gridx = 1;
		gridBagConstraints.gridy = 2;
		gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
		jPanel1.add(jPanel3, gridBagConstraints);

		jScrollPane2.setViewportView(sqlTextArea);

		gridBagConstraints = new java.awt.GridBagConstraints();
		gridBagConstraints.gridx = 1;
		gridBagConstraints.gridy = 1;
		gridBagConstraints.fill = java.awt.GridBagConstraints.BOTH;
		gridBagConstraints.weightx = 1.0;
		gridBagConstraints.weighty = 1.0;
		gridBagConstraints.insets = new java.awt.Insets(0, 4, 0, 0);
		jPanel1.add(jScrollPane2, gridBagConstraints);

		gridBagConstraints = new java.awt.GridBagConstraints();
		gridBagConstraints.gridx = 1;
		gridBagConstraints.gridy = 10;
		gridBagConstraints.fill = java.awt.GridBagConstraints.BOTH;
		gridBagConstraints.weightx = 1.0;
		gridBagConstraints.weighty = 1.0;
		getContentPane().add(jPanel1, gridBagConstraints);

		jPanel2.setLayout(new java.awt.GridBagLayout());

		joinAWithBButton.setText(" Join selected Tables");
		joinAWithBButton.addActionListener(new java.awt.event.ActionListener() {
			public void actionPerformed(java.awt.event.ActionEvent evt) {
				joinAWithBButtonActionPerformed(evt);
			}
		});
		gridBagConstraints = new java.awt.GridBagConstraints();
		gridBagConstraints.gridx = 1;
		gridBagConstraints.gridy = 1;
		gridBagConstraints.anchor = java.awt.GridBagConstraints.WEST;
		gridBagConstraints.weightx = 1.0;
		gridBagConstraints.insets = new java.awt.Insets(2, 4, 2, 2);
		jPanel2.add(joinAWithBButton, gridBagConstraints);

		saveButton.setText(" Save ");
		saveButton.addActionListener(new java.awt.event.ActionListener() {
			public void actionPerformed(java.awt.event.ActionEvent evt) {
				saveButtonActionPerformed(evt);
			}
		});
		gridBagConstraints = new java.awt.GridBagConstraints();
		gridBagConstraints.gridx = 2;
		gridBagConstraints.gridy = 1;
		gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
		gridBagConstraints.weightx = 1.0;
		gridBagConstraints.insets = new java.awt.Insets(2, 2, 2, 2);
		jPanel2.add(saveButton, gridBagConstraints);

		clipboardSingleLineButton.setText(" Copy as Single Line ");
		clipboardSingleLineButton
				.setToolTipText(" Copy the query as a single line to the clipboard");
		clipboardSingleLineButton
				.addActionListener(new java.awt.event.ActionListener() {
					public void actionPerformed(java.awt.event.ActionEvent evt) {
						clipboardSingleLineButtonActionPerformed(evt);
					}
				});
		gridBagConstraints = new java.awt.GridBagConstraints();
		gridBagConstraints.gridx = 4;
		gridBagConstraints.gridy = 1;
		gridBagConstraints.insets = new java.awt.Insets(2, 2, 2, 4);
		jPanel2.add(clipboardSingleLineButton, gridBagConstraints);

		jPanel4.setLayout(new java.awt.GridBagLayout());

		jLabel1.setText("multi-line continuation  ");
		jLabel1.setToolTipText("multi-line continuation character");
		gridBagConstraints = new java.awt.GridBagConstraints();
		gridBagConstraints.gridx = 1;
		gridBagConstraints.gridy = 2;
		gridBagConstraints.anchor = java.awt.GridBagConstraints.WEST;
		jPanel4.add(jLabel1, gridBagConstraints);
		gridBagConstraints = new java.awt.GridBagConstraints();
		gridBagConstraints.gridx = 2;
		gridBagConstraints.gridy = 2;
		gridBagConstraints.fill = java.awt.GridBagConstraints.HORIZONTAL;
		gridBagConstraints.ipadx = 16;
		gridBagConstraints.insets = new java.awt.Insets(0, 0, 0, 4);
		jPanel4.add(mlmTextField, gridBagConstraints);

		gridBagConstraints = new java.awt.GridBagConstraints();
		gridBagConstraints.gridx = 2;
		gridBagConstraints.gridy = 0;
		gridBagConstraints.gridwidth = 3;
		gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
		jPanel2.add(jPanel4, gridBagConstraints);

		clipboardButton.setText(" Copy to Clipboard ");
		clipboardButton.addActionListener(new java.awt.event.ActionListener() {
			public void actionPerformed(java.awt.event.ActionEvent evt) {
				clipboardButtonActionPerformed(evt);
			}
		});
		gridBagConstraints = new java.awt.GridBagConstraints();
		gridBagConstraints.gridx = 3;
		gridBagConstraints.gridy = 1;
		gridBagConstraints.insets = new java.awt.Insets(2, 2, 2, 2);
		jPanel2.add(clipboardButton, gridBagConstraints);

		sqlEditButton.setText(" Execute ");
		sqlEditButton.addActionListener(new java.awt.event.ActionListener() {
			public void actionPerformed(java.awt.event.ActionEvent evt) {
				sqlEditButtonActionPerformed(evt);
			}
		});
		gridBagConstraints = new java.awt.GridBagConstraints();
		gridBagConstraints.gridx = 1;
		gridBagConstraints.gridy = 1;
		gridBagConstraints.anchor = java.awt.GridBagConstraints.WEST;
		gridBagConstraints.weightx = 1.0;
		gridBagConstraints.insets = new java.awt.Insets(0, 6, 0, 0);
		jPanel2.add(sqlEditButton, gridBagConstraints);

		gridBagConstraints = new java.awt.GridBagConstraints();
		gridBagConstraints.gridx = 1;
		gridBagConstraints.gridy = 30;
		gridBagConstraints.fill = java.awt.GridBagConstraints.BOTH;
		getContentPane().add(jPanel2, gridBagConstraints);

		jPanel5.setLayout(new java.awt.GridBagLayout());

		jLabel2.setText("Select ");
		gridBagConstraints = new java.awt.GridBagConstraints();
		gridBagConstraints.gridx = 1;
		gridBagConstraints.gridy = 1;
		gridBagConstraints.anchor = java.awt.GridBagConstraints.WEST;
		gridBagConstraints.insets = new java.awt.Insets(1, 0, 0, 0);
		jPanel5.add(jLabel2, gridBagConstraints);

		distinctCheckBox.setText("distinct");
		gridBagConstraints = new java.awt.GridBagConstraints();
		gridBagConstraints.gridx = 2;
		gridBagConstraints.gridy = 1;
		gridBagConstraints.anchor = java.awt.GridBagConstraints.WEST;
		gridBagConstraints.weightx = 1.0;
		jPanel5.add(distinctCheckBox, gridBagConstraints);

		gridBagConstraints = new java.awt.GridBagConstraints();
		gridBagConstraints.gridx = 1;
		gridBagConstraints.gridy = 0;
		gridBagConstraints.fill = java.awt.GridBagConstraints.BOTH;
		gridBagConstraints.weightx = 1.0;
		gridBagConstraints.insets = new java.awt.Insets(6, 8, 0, 0);
		getContentPane().add(jPanel5, gridBagConstraints);

		pack();
	}// </editor-fold>//GEN-END:initComponents

	private void saveButtonActionPerformed(java.awt.event.ActionEvent evt) {// GEN-FIRST:event_saveButtonActionPerformed
		String fn = UIUtil.choseFile(null, ".", "Save SQL Query", "", this,
				false, false);
		if (fn != null) {
			try {
				PrintWriter out = new PrintWriter(new FileWriter(fn));
				out.print(sqlTextArea.getText());
				out.close();
			} catch (Exception e) {
				UIUtil.showException(this, "Error saving query", e);
			}
		}
	}// GEN-LAST:event_saveButtonActionPerformed

	private void clipboardButtonActionPerformed(java.awt.event.ActionEvent evt) {// GEN-FIRST:event_clipboardButtonActionPerformed
		sqlTextArea.selectAll();
		sqlTextArea.copy();
		sqlTextArea.select(0, 0);
	}// GEN-LAST:event_clipboardButtonActionPerformed

	private void joinAWithBButtonActionPerformed(java.awt.event.ActionEvent evt) {// GEN-FIRST:event_joinAWithBButtonActionPerformed
		createPathQuery(null);
	}// GEN-LAST:event_joinAWithBButtonActionPerformed

	private void clipboardSingleLineButtonActionPerformed(
			java.awt.event.ActionEvent evt) {// GEN-FIRST:event_clipboardSingleLineButtonActionPerformed
		String orig = sqlTextArea.getText();
		sqlTextArea.setText(orig.replaceAll(" *(\n|\r)+ *", " "));
		sqlTextArea.selectAll();
		sqlTextArea.copy();
		sqlTextArea.setText(orig);
		sqlTextArea.select(0, 0);
	}// GEN-LAST:event_clipboardSingleLineButtonActionPerformed

	private void sqlEditButtonActionPerformed(java.awt.event.ActionEvent evt) {// GEN-FIRST:event_sqlEditButtonActionPerformed
		// TODO add your handling code here:
	}// GEN-LAST:event_sqlEditButtonActionPerformed

	private Font font = new JLabel("normal").getFont();

	/**
	 * Non-bold font.
	 */
	private Font nonBoldFont = new Font(font.getName(), font.getStyle()
			& ~Font.BOLD, font.getSize());

	/**
	 * Subject of query.
	 */
	private Table subject;

	private boolean selectDistinct = false;

	public static enum JoinOperator {
		Join("Join"), LeftJoin("Left Join");

		private final String operator;

		private JoinOperator(String operator) {
			this.operator = operator;
		}

		public String toString() {
			return operator;
		}
	}

	/**
	 * Relationship.
	 */
	public static class Relationship {
		public List<Relationship> children = new ArrayList<Relationship>();
		public Relationship parent;
		public String whereClause;
		public String anchorWhereClause;
		public Association anchor;
		public boolean needsAnchor = false;
		public Association association;
		public JoinOperator joinOperator = JoinOperator.Join;

		public int level;
		public List<Relationship> origChildren = null;
		public JTextField aliasTextField;
		public Color originalBGColor;
		public boolean selectColumns;
		public String alias;
		public String aliasSuggestion;
		public Relationship originalParent;

		public List<Association> getPathToRoot() {
			List<Association> path = new ArrayList<Association>();
			path.add(association);
			if (parent != null) {
				path.addAll(parent.getPathToRoot());
			}
			return path;
		}

		public List<Relationship> flatten(int level, Relationship parent,
				boolean withLastPseudoChild) {
			this.level = level;
			this.parent = parent;
			List<Relationship> flat = new ArrayList<Relationship>();
			flat.add(this);
			if (withLastPseudoChild) {
				Relationship lastChild = new Relationship();
				lastChild.parent = this;
				lastChild.level = level + 1;
				flat.add(lastChild);
			}
			for (Relationship child : children) {
				flat.addAll(child.flatten(level + 1, this, withLastPseudoChild));
			}
			return flat;
		}

		public void dump(int level) {
			String indent = "                     ".substring(0, level * 4);
			System.out.println(indent
					+ (association == null ? "" : (association.source.getName()
							+ " -> " + association.destination.getName())));
			System.out.println(indent + anchorWhereClause);
			for (Relationship r : children) {
				r.dump(level + 1);
			}
		}
	}

	private Map<List<Association>, String> originalAnchorSQL = new HashMap<List<Association>, String>();
	private Map<List<Association>, String> originalConditionSQL = new HashMap<List<Association>, String>();
	private Map<List<Association>, Association> originalAnchor = new HashMap<List<Association>, Association>();

	/**
	 * Root relationship.
	 */
	private Relationship rootRelationship;

	private void resetRelationshipsPanel() {
		relationshipsPanel.removeAll();
		// Table lastTable = null;
		List<Relationship> relationships = rootRelationship.flatten(0, null,
				true);
		for (int y = 0; y < relationships.size(); ++y) {
			final Relationship relationship = relationships.get(y);

			javax.swing.JLabel label;

			java.awt.GridBagConstraints gridBagConstraints = new java.awt.GridBagConstraints();
			gridBagConstraints.gridx = 1;
			gridBagConstraints.gridy = y;
			gridBagConstraints.fill = GridBagConstraints.HORIZONTAL;
			gridBagConstraints.weightx = 0.0;
			gridBagConstraints.anchor = java.awt.GridBagConstraints.WEST;
			if (y == 0) {
				label = new javax.swing.JLabel();
				label.setText(y == 0 ? " From  " : " Join  ");
				label.setFont(nonBoldFont);

				relationshipsPanel.add(label, gridBagConstraints);
			} else if (relationship.association != null) {
				JComboBox joinCB = new JComboBox();
				DefaultComboBoxModel aModel = new DefaultComboBoxModel(
						JoinOperator.values());
				joinCB.setModel(aModel);
				joinCB.setSelectedItem(relationship.joinOperator);

				joinCB.addItemListener(new ItemListener() {
					@Override
					public void itemStateChanged(ItemEvent e) {
						if (e.getStateChange() == ItemEvent.SELECTED) {
							relationship.joinOperator = (JoinOperator) e
									.getItem();
							resetRelationshipsPanel();
							updateSQL();
						}
					}
				});

				relationshipsPanel.add(joinCB, gridBagConstraints);
			}

			gridBagConstraints = new java.awt.GridBagConstraints();
			gridBagConstraints.gridx = 3;
			gridBagConstraints.gridy = y;
			gridBagConstraints.fill = GridBagConstraints.HORIZONTAL;
			gridBagConstraints.weightx = 0.0;
			gridBagConstraints.insets = new Insets(0,
					0 + relationship.level * 12, 2, 0);
			gridBagConstraints.anchor = java.awt.GridBagConstraints.WEST;
			JComboBox tableCB = null;
			if (relationship != rootRelationship) {
				tableCB = new JComboBox() {
					private boolean layingOut = false;

					public void doLayout() {
						try {
							layingOut = true;
							super.doLayout();
						} finally {
							layingOut = false;
						}
					}

					public Dimension getSize() {
						Dimension sz = super.getSize();
						if (!layingOut) {
							sz.width = Math.max(sz.width,
									super.getPreferredSize().width);
						}
						return sz;
					}

					@Override
					public Dimension getPreferredSize() {
						return new Dimension(Math.min(
								super.getPreferredSize().width, 300),
								super.getPreferredSize().height);
					}

					private static final long serialVersionUID = -6555670830339032571L;
				};
				DefaultComboBoxModel aModel = new DefaultComboBoxModel();
				aModel.addElement("");
				Table lastTable = relationship.parent == rootRelationship ? subject
						: relationship.parent.association.destination;
				for (Association a : lastTable.associations) {
					aModel.addElement(joinTableRender(lastTable, a));
				}
				tableCB.setModel(aModel);
				if (relationship.association != null) {
					tableCB.setSelectedItem(joinTableRender(lastTable,
							relationship.association));
				}
				final Table ft = lastTable;
				tableCB.addItemListener(new ItemListener() {
					@Override
					public void itemStateChanged(ItemEvent e) {
						if (e.getStateChange() == ItemEvent.SELECTED) {
							Association sa = null;
							for (Association a : ft.associations) {
								if (joinTableRender(ft, a).equals(e.getItem())) {
									sa = a;
									break;
								}
							}
							if (sa != null) {
								relationship.association = sa;
								relationship.children.clear();
								relationship.anchorWhereClause = originalAnchorSQL
										.get(relationship.getPathToRoot());
								relationship.whereClause = originalConditionSQL
										.get(relationship.getPathToRoot());
								relationship.anchor = originalAnchor
										.get(relationship.getPathToRoot());
								if (relationship.parent != null
										&& !relationship.parent.children
												.contains(relationship)) {
									relationship.parent.children
											.add(relationship);
								}
							} else {
								if (relationship.parent != null) {
									relationship.parent.children
											.remove(relationship);
								}
							}
							resetRelationshipsPanel();
							updateSQL();
						}
					}
				});
				relationshipsPanel.add(tableCB, gridBagConstraints);

				final JLabel minusLabel = new javax.swing.JLabel();
				minusLabel.setText(null);
				minusLabel.setIcon(minusImage);
				minusLabel.setToolTipText("remove this table from query");

				gridBagConstraints = new java.awt.GridBagConstraints();
				gridBagConstraints.gridx = 2;
				gridBagConstraints.gridy = y;
				gridBagConstraints.fill = GridBagConstraints.HORIZONTAL;
				gridBagConstraints.weightx = 0.0;
				gridBagConstraints.anchor = java.awt.GridBagConstraints.WEST;
				gridBagConstraints.insets = new Insets(0, 4, 0, 0);

				minusLabel.setEnabled(false);
				final JComboBox combobox = tableCB;
				minusLabel.addMouseListener(new java.awt.event.MouseAdapter() {
					public void mouseEntered(java.awt.event.MouseEvent evt) {
						minusLabel.setEnabled(true);
					}

					public void mouseExited(java.awt.event.MouseEvent evt) {
						minusLabel.setEnabled(false);
					}

					public void mouseClicked(java.awt.event.MouseEvent evt) {
						combobox.setSelectedItem("");
					}
				});

				if (relationship.association != null) {
					relationshipsPanel.add(minusLabel, gridBagConstraints);
				}
			} else {
				label = new javax.swing.JLabel();
				label.setText(subject.getName());
				relationshipsPanel.add(label, gridBagConstraints);
			}

			if (relationship.association == null
					&& relationship != rootRelationship) {
				final JLabel jlabel = new javax.swing.JLabel();
				jlabel.setText(null);
				jlabel.setIcon(joinImage);
				jlabel.setToolTipText("join another table");

				gridBagConstraints = new java.awt.GridBagConstraints();
				gridBagConstraints.gridx = 4;
				gridBagConstraints.gridy = y - 1;
				gridBagConstraints.fill = GridBagConstraints.HORIZONTAL;
				gridBagConstraints.weightx = 0.0;
				gridBagConstraints.anchor = java.awt.GridBagConstraints.WEST;
				gridBagConstraints.insets = new Insets(0, 4, 0, 0);
				relationshipsPanel.add(jlabel, gridBagConstraints);

				final JComponent finalTCB = tableCB;
				finalTCB.setVisible(false);
				jlabel.setEnabled(false);

				jlabel.addMouseListener(new java.awt.event.MouseAdapter() {
					public void mouseEntered(java.awt.event.MouseEvent evt) {
						jlabel.setEnabled(true);
					}

					public void mouseExited(java.awt.event.MouseEvent evt) {
						jlabel.setEnabled(false);
					}

					public void mouseClicked(java.awt.event.MouseEvent evt) {
						finalTCB.setVisible(true);
						jlabel.setVisible(false);
					}
				});
			}

			if (relationship.association != null
					|| relationship == rootRelationship) {
				label = new javax.swing.JLabel();
				label.setText("   as ");
				label.setFont(nonBoldFont);

				gridBagConstraints = new java.awt.GridBagConstraints();
				gridBagConstraints.gridx = 5;
				gridBagConstraints.gridy = y;
				gridBagConstraints.fill = GridBagConstraints.HORIZONTAL;
				gridBagConstraints.weightx = 0.0;
				gridBagConstraints.anchor = java.awt.GridBagConstraints.WEST;
				relationshipsPanel.add(label, gridBagConstraints);

				String alias = "";
				if (relationship != null && relationship.aliasTextField != null) {
					alias = relationship.aliasTextField.getText();
				}
				JTextField aliasField = new JTextField(alias);
				relationship.aliasTextField = aliasField;
				relationship.originalBGColor = aliasField.getBackground();
				aliasField.getDocument().addDocumentListener(
						new DocumentListener() {
							@Override
							public void changedUpdate(DocumentEvent e) {
								checkAliases();
								updateSQL();
							}

							@Override
							public void insertUpdate(DocumentEvent e) {
								checkAliases();
								updateSQL();
							}

							@Override
							public void removeUpdate(DocumentEvent e) {
								checkAliases();
								updateSQL();
							}
						});
				gridBagConstraints = new java.awt.GridBagConstraints();
				gridBagConstraints.gridx = 6;
				gridBagConstraints.gridy = y;
				gridBagConstraints.fill = GridBagConstraints.HORIZONTAL;
				gridBagConstraints.weightx = 0.0;
				gridBagConstraints.anchor = java.awt.GridBagConstraints.WEST;
				relationshipsPanel.add(aliasField, gridBagConstraints);

				final JCheckBox selectColumnsCB = new JCheckBox(
						"select columns");
				selectColumnsCB.setSelected(relationship.selectColumns);

				selectColumnsCB.addItemListener(new ItemListener() {
					@Override
					public void itemStateChanged(ItemEvent e) {
						relationship.selectColumns = selectColumnsCB
								.isSelected();
						updateSQL();
					}
				});

				gridBagConstraints = new java.awt.GridBagConstraints();
				gridBagConstraints.gridx = 8;
				gridBagConstraints.gridy = y;
				gridBagConstraints.fill = GridBagConstraints.HORIZONTAL;
				gridBagConstraints.weightx = 1.0;
				gridBagConstraints.anchor = java.awt.GridBagConstraints.WEST;
				gridBagConstraints.insets = new Insets(0, 10, 0, 0);
				relationshipsPanel.add(selectColumnsCB, gridBagConstraints);
			}
			// lastTable = relationship == null? null : relationship.association
			// == null? subject : relationship.association.destination;
		}

		JLabel label = new javax.swing.JLabel();
		label.setText("                ");

		GridBagConstraints gridBagConstraints = new java.awt.GridBagConstraints();
		gridBagConstraints.gridx = 6;
		gridBagConstraints.gridy = relationships.size() + 1;
		gridBagConstraints.fill = GridBagConstraints.HORIZONTAL;
		gridBagConstraints.weighty = 1.0;
		gridBagConstraints.anchor = java.awt.GridBagConstraints.WEST;
		relationshipsPanel.add(label, gridBagConstraints);

		checkAliases();
		updateSQL();
		validate();
	}

	/**
	 * Gets render of an association for the join-combobox.
	 * 
	 * @param association
	 *            the association
	 * @return render of association
	 */
	private String joinTableRender(Table from, Association association) {
		int n = 0;
		for (Association a : from.associations) {
			if (a.destination == association.destination) {
				++n;
			}
		}
		return datamodel.getDisplayName(association.destination)
				+ (n > 1 ? " on " + association.getName() : "");
	}

	/**
	 * Checks aliases, renders naming conflicts.
	 */
	private void checkAliases() {
		List<Relationship> relationships = rootRelationship.flatten(0, null,
				false);
		for (Relationship a : relationships) {
			a.aliasTextField.setBackground(a.originalBGColor);
		}
		for (Relationship a : relationships) {
			a.aliasSuggestion = null;
			for (Relationship b : relationships) {
				if (a != b) {
					String ta = a.aliasTextField.getText().trim();
					Table tableA = a.association == null ? subject
							: a.association.destination;
					String tb = b.aliasTextField.getText().trim();
					Table tableB = b.association == null ? subject
							: b.association.destination;
					if (ta.length() == 0) {
						ta = unquote(tableA.getUnqualifiedName());
					}
					if (tb.length() == 0) {
						tb = unquote(tableB.getUnqualifiedName());
					}
					if (ta.equalsIgnoreCase(tb)) {
						String as = null;
						String aName = unquote(tableA.getUnqualifiedName());
						for (int i = 1; i <= aName.length(); ++i) {
							as = aName.substring(0, i);
							if (as.endsWith("_")) {
								continue;
							}
							boolean unique = true;
							for (Relationship c : relationships) {
								Table tableC = c.association == null ? subject
										: c.association.destination;
								if (!unquote(tableC.getUnqualifiedName())
										.equalsIgnoreCase(aName)) {
									if (unquote(tableC.getUnqualifiedName())
											.toLowerCase().startsWith(
													as.toLowerCase())) {
										unique = false;
										break;
									}
								}
							}
							if (unique) {
								break;
							}
						}
						a.aliasSuggestion = as;

						Color bg = new Color(255, 150, 140);
						a.aliasTextField.setBackground(bg);
						b.aliasTextField.setBackground(bg);
					}
				}
			}
		}
	}

	private String unquote(String name) {
		if (!name.isEmpty()) {
			char fc = name.charAt(0);
			if (!Character.isLetterOrDigit(fc) && fc != '_') {
				String fcStr = Character.toString(fc);
				if (name.startsWith(fcStr) && name.endsWith(fcStr)) {
					name = name.substring(1, name.length() - 1);
				}
			}
		}
		return name;
	}

	private String prevSQL = "";

	/**
	 * Updates the SQL query.
	 */
	private void updateSQL() {
		appendMLM("");
		String currentSql = prevSQL; // createSQL(sqlIsSingleLine,
										// qualifyTableNames);
		String sql = sqlTextArea.getText();
		String suffix = "";
		if (sql.startsWith(currentSql)) {
			suffix = sql.substring(currentSql.length());
		}
		sqlTextArea.setText((prevSQL = createSQL(false)) + suffix);
		appendMLM(mlmTextField.getText());
		sqlTextArea.setCaretPosition(0);
	}

	/**
	 * Gets current SQL statement.
	 * 
	 * @return current SQL statement
	 */
	public String getSQL() {
		appendMLM("");
		String sql = sqlTextArea.getText();
		appendMLM(mlmTextField.getText());
		sqlTextArea.setCaretPosition(0);
		return sql;
	}

	/**
	 * Creates SQL query.
	 */
	private String createSQL(boolean singleLine) {
		StringBuffer sql = new StringBuffer("Select "
				+ (selectDistinct ? "distinct " : ""));
		String lf = System.getProperty("line.separator", "\n");
		String tab = "       ";

		List<Relationship> relationships = rootRelationship.flatten(0, null,
				false);
		boolean needsIndent = false;
		for (Relationship r : relationships) {
			if (r.children.size() > 1) {
				needsIndent = true;
				break;
			}
		}

		if (!singleLine) {
			int sa = 0;
			for (int i = 0; i < relationships.size(); ++i) {
				Relationship r = relationships.get(i);
				if (r.selectColumns) {
					++sa;
				}
			}
			if (sa > 1) {
				sql.append(lf + tab);
			}
		}
		boolean selectAll = true;
		boolean fr = true;
		for (int i = 0; i < relationships.size(); ++i) {
			Relationship r = relationships.get(i);
			Table t = r.association == null ? subject
					: r.association.destination;
			r.alias = r.aliasTextField.getText().trim();
			if (r.alias.equals("")) {
				r.alias = t.getName();
			}
			if (r.selectColumns) {
				selectAll = false;
				if (!fr) {
					sql.append(", ");
					if (!singleLine) {
						sql.append(lf + tab);
					}
				}
				fr = false;
				boolean f = true;
				for (Column c : t.getColumns()) {
					if (!f) {
						sql.append(", ");
					}
					f = false;
					sql.append(r.alias + "." + c.name);
				}
			}
		}
		if (selectAll) {
			sql.append("*");
		}
		if (!singleLine) {
			sql.append(lf);
		} else {
			sql.append(" ");
		}
		sql.append("From ");
		// String lastAlias = "";
		for (int i = 0; i < relationships.size(); ++i) {
			Relationship r = relationships.get(i);
			String indent = "";
			if (needsIndent) {
				for (int l = 0; l < r.level; ++l) {
					indent += "  ";
				}
			}
			Table t = r.association == null ? subject
					: r.association.destination;
			if (r.association != null) {
				sql.append(singleLine ? " " : (lf + tab + indent));
				sql.append(r.joinOperator + " ");
			} else if (relationships.size() > 1) {
				sql.append(singleLine ? "" : (lf + tab + indent));
			}
			sql.append(t.getName());
			String alias = r.aliasTextField.getText().trim();
			if (alias.length() > 0) {
				sql.append(" " + alias);
			} else {
				alias = t.getName();
			}
			String lastAlias = "";
			Relationship parent = r.parent;
			if (parent != null) {
				lastAlias = parent.aliasTextField.getText().trim();
				if (lastAlias.length() <= 0) {
					lastAlias = parent.association == null ? subject.getName()
							: parent.association.destination.getName();
				}
			}
			if (r.association != null) {
				String jc;
				if (!r.association.reversed) {
					jc = SqlUtil.replaceAliases(
							r.association.getUnrestrictedJoinCondition(),
							lastAlias, alias);
				} else {
					jc = SqlUtil.replaceAliases(
							r.association.getUnrestrictedJoinCondition(),
							alias, lastAlias);
				}
				if (r.joinOperator == JoinOperator.LeftJoin
						&& r.originalParent == null) {
					if (r.whereClause != null) {
						jc = "("
								+ jc
								+ ") and ("
								+ SqlUtil.replaceAliases(r.whereClause,
										r.alias, lastAlias) + ")";
					}
				}
				sql.append(" on " + jc);
			}
			// lastAlias = alias;
		}

		boolean f = true;
		int lines = 0;
		for (int i = 0; i < relationships.size(); ++i) {
			Relationship r = relationships.get(i);
			if (r.whereClause != null) {
				++lines;
			}
		}

		for (int i = 0; i < relationships.size(); ++i) {
			Relationship r = relationships.get(i);
			String lastAlias = "";
			Relationship parent = r.originalParent;
			if (parent == null) {
				parent = r.parent;
			}
			if (parent != null) {
				lastAlias = parent.aliasTextField.getText().trim();
				if (lastAlias.length() <= 0) {
					lastAlias = parent.association == null ? subject.getName()
							: parent.association.destination.getName();
				}
			}
			boolean appendAnd = true;
			if (r.anchorWhereClause != null && r.anchor != null) {
				boolean anchorExists = false;
				for (Relationship c : r.children) {
					if (c.association == r.anchor) {
						anchorExists = true;
						break;
					}
				}
				if (!anchorExists) {
					appendAnd = false;
					if (f) {
						sql.append(singleLine ? " " : lf);
						sql.append("Where");
						sql.append(singleLine || lines == 1 ? " " : (lf + tab));
					} else {
						sql.append(singleLine ? " " : (lf + tab));
						sql.append("and ");
					}
					sql.append("("
							+ SqlUtil.replaceAliases(r.anchorWhereClause,
									r.alias, lastAlias) + ")");
					f = false;
				}
			}
			if (appendAnd
					&& r.whereClause != null
					&& (r.joinOperator == JoinOperator.Join || r.originalParent != null)) {
				if (f) {
					sql.append(singleLine ? " " : lf);
					sql.append("Where");
					sql.append(singleLine || lines == 1 ? " " : (lf + tab));
				} else {
					sql.append(singleLine ? " " : (lf + tab));
					sql.append("and ");
				}
				sql.append("("
						+ SqlUtil.replaceAliases(r.whereClause, r.alias,
								lastAlias) + ")");
				f = false;
			}
		}

		if (!singleLine) {
			sql.append(" " + lf);
		} else {
			sql.append(" ");
			return sql.toString().replaceAll(" *(\n|\r)+ *", " ");
		}
		String sqlString = sql.toString().trim();
		sqlString += lf;

		return sqlString;
	}

	/**
	 * The data model.
	 */
	private DataModel datamodel;

	/**
	 * Opens the dialog.
	 * 
	 * @param table
	 *            subject of query
	 * @param usePath
	 *            if <code>true</code>, immediately build query based on
	 *            selected path
	 * @param associationsOnPath
	 *            currently selected associations path
	 */
	public void buildQuery(Table table, boolean usePath,
			boolean showJoinButton, List<Association> associationsOnPath,
			List<String> whereClauses, DataModel datamodel) {
		this.associationsOnPath = associationsOnPath;
		if (table == null) {
			return;
		}
		sqlTextArea.setText("");
		mlmTextField.setText("");
		this.datamodel = datamodel;
		subject = table;
		// relationships.clear();
		Relationship firstR = new Relationship();
		rootRelationship = firstR;
		firstR.selectColumns = true;
		// relationships.add(firstR);
		firstR.whereClause = null;
		if (whereClauses != null && whereClauses.size() > 0) {
			firstR.whereClause = whereClauses.get(0);
		}
		if (usePath && !associationsOnPath.isEmpty()) {
			createPathQuery(whereClauses);
		}
		if (associationsOnPath.isEmpty() || !showJoinButton) {
			joinAWithBButton.setVisible(false);
		} else {
			joinAWithBButton.setVisible(true);
		}
		resetRelationshipsPanel();

		List<JTextField> tf = new ArrayList<JTextField>();
		List<String> as = new ArrayList<String>();
		for (Relationship r : rootRelationship.flatten(0, null, false)) {
			if (r.aliasSuggestion != null) {
				tf.add(r.aliasTextField);
				as.add(r.aliasSuggestion);
			}
		}
		Map<String, Integer> counterPerAlias = new HashMap<String, Integer>();
		for (int i = 0; i < tf.size(); ++i) {
			Integer c = counterPerAlias.get(as.get(i));
			if (c == null) {
				c = 1;
			}
			counterPerAlias.put(as.get(i), c + 1);
			tf.get(i).setText(as.get(i) + c);
		}
		checkAliases();

		setLocation(getParent().getX() + (getParent().getWidth() - getWidth())
				/ 2, getParent().getY()
				+ (getParent().getHeight() - getHeight()) / 2);
		UIUtil.fit(this);
		setVisible(true);
	}

	/**
	 * Opens the dialog.
	 * 
	 * @param table
	 *            subject of query
	 * @param usePath
	 *            if <code>true</code>, immediately build query based on
	 *            selected path
	 * @param root
	 *            root relation
	 */
	public void buildQuery(Table table, Relationship root, DataModel datamodel) {
		if (table == null) {
			return;
		}
		sqlTextArea.setText("");
		mlmTextField.setText("");
		this.datamodel = datamodel;
		subject = table;
		rootRelationship = root;
		joinAWithBButton.setVisible(false);
		resetRelationshipsPanel();

		List<JTextField> tf = new ArrayList<JTextField>();
		List<String> as = new ArrayList<String>();
		boolean distinct = false;
		for (Relationship r : rootRelationship.flatten(0, null, false)) {
			if (r.aliasSuggestion != null) {
				tf.add(r.aliasTextField);
				as.add(r.aliasSuggestion);
			}
			if (r.association != null && r.joinOperator != JoinOperator.Join) {
				distinct = true;
			}
			originalAnchorSQL.put(r.getPathToRoot(), r.anchorWhereClause);
			originalConditionSQL.put(r.getPathToRoot(), r.whereClause);
			originalAnchor.put(r.getPathToRoot(), r.anchor);
		}
		Map<String, Integer> counterPerAlias = new HashMap<String, Integer>();
		for (int i = 0; i < tf.size(); ++i) {
			Integer c = counterPerAlias.get(as.get(i));
			if (c == null) {
				c = 1;
			}
			counterPerAlias.put(as.get(i), c + 1);
			tf.get(i).setText(as.get(i) + c);
		}
		checkAliases();

		setLocation(getParent().getX() + (getParent().getWidth() - getWidth())
				/ 2, getParent().getY()
				+ (getParent().getHeight() - getHeight()) / 2);
		UIUtil.fit(this);
		distinctCheckBox.setSelected(distinct);
		setVisible(true);
	}

	private List<Association> associationsOnPath;

	private void createPathQuery(List<String> whereClauses) {
		// relationships.clear();
		Relationship firstR = new Relationship();
		rootRelationship = firstR;
		// relationships.add(firstR);
		firstR.selectColumns = true;
		if (whereClauses != null && whereClauses.size() > 0) {
			firstR.whereClause = whereClauses.get(0);
		}
		subject = associationsOnPath.get(0).source;
		for (int i = 0; i < associationsOnPath.size(); ++i) {
			Association a = associationsOnPath.get(i);
			Relationship r = new Relationship();
			r.association = a;
			if (whereClauses != null && whereClauses.size() > i + 1) {
				r.whereClause = whereClauses.get(i + 1);
			}
			firstR.children.add(r);
			firstR = r;
		}
		resetRelationshipsPanel();
	}

	private String mlm = "";

	private void appendMLM(String mlm) {
		mlm = mlm.trim();
		if (mlm.length() > 1) {
			mlm = mlm.substring(0, 1);
		}
		if (this.mlm.equals(mlm)) {
			return;
		}
		if (this.mlm.length() > 0) {
			String omlm = this.mlm;
			if ("\\|[]()^-$".indexOf(omlm) >= 0) {
				omlm = "\\" + omlm;
			}
			sqlTextArea.setText(sqlTextArea.getText().replaceAll(
					" " + omlm + "([\n\r])", "$1"));
			sqlTextArea.select(0, 0);
		}
		this.mlm = mlm;
		if (mlm.length() > 0) {
			if ("\\".equals(mlm) || "$".equals(mlm)) {
				mlm = "\\" + mlm;
			}
			sqlTextArea.setText(sqlTextArea.getText().replaceAll(
					"([^\n\r;])([\n\r])", "$1 " + mlm + "$2"));
			sqlTextArea.select(0, 0);
		}
	}

	// Variables declaration - do not modify//GEN-BEGIN:variables
	private javax.swing.JButton clipboardButton;
	private javax.swing.JButton clipboardSingleLineButton;
	private javax.swing.JCheckBox distinctCheckBox;
	private javax.swing.JLabel jLabel1;
	private javax.swing.JLabel jLabel2;
	private javax.swing.JPanel jPanel1;
	private javax.swing.JPanel jPanel2;
	private javax.swing.JPanel jPanel3;
	private javax.swing.JPanel jPanel4;
	private javax.swing.JPanel jPanel5;
	private javax.swing.JScrollPane jScrollPane2;
	private javax.swing.JButton joinAWithBButton;
	private javax.swing.JTextField mlmTextField;
	private javax.swing.JPanel relationshipsPanel;
	private javax.swing.JButton saveButton;
	public javax.swing.JButton sqlEditButton;
	private javax.swing.JEditorPane sqlTextArea;
	// End of variables declaration//GEN-END:variables

	private ImageIcon joinImage = null;
	private ImageIcon minusImage = null;
	{
		String dir = "/net/sf/jailer/resource";

		// load image
		try {
			joinImage = new ImageIcon(new ImageIcon(getClass().getResource(
					dir + "/collapsed.png")).getImage().getScaledInstance(22,
					18, Image.SCALE_SMOOTH));
		} catch (Exception e) {
			e.printStackTrace();
		}
		try {
			minusImage = new ImageIcon(new ImageIcon(getClass().getResource(
					dir + "/minus.png")).getImage().getScaledInstance(22, 18,
					Image.SCALE_SMOOTH));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static final long serialVersionUID = -2801831496446636545L;
}
