/*
 * Class RepositoryQueryDialgo <one line to give the program's name and a brief idea of what it does.>
 *
 * Copyright (c) 2009 Max-Planck-Gesellschaft, Martin Hewitson <martin.hewitson at aei.mpg.de>
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 *
 */

/*
 * RepositoryQueryDialgo.java
 *
 * Created on 25-Jul-2009, 17:06:05
 */
package mpipeline.repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.DefaultComboBoxModel;
import javax.swing.DefaultListModel;
import javax.swing.JButton;
import javax.swing.JOptionPane;
import javax.swing.JTextArea;
import javax.swing.ListSelectionModel;
import mpipeline.main.MainWindow;

/**
 *
 * @author Martin Hewitson <martin.hewitson at aei.mpg.de>
 */
public class RepositoryQueryDialog extends javax.swing.JDialog {

  private boolean cancelled = true;
  private MainWindow mw = null;
  private boolean modal = true;
  private java.sql.Connection repoConnection = null;
  private ArrayList<String> tables = new ArrayList<String>();
  private ArrayList<String> fields = new ArrayList<String>();
  private boolean showConstructorButton = true;
  
  /** Creates new form RepositoryQueryDialgo
   * @param parent
   * @param modal
   * @param conn
   * @param showConstructorButton
   */
  public RepositoryQueryDialog(MainWindow parent, boolean modal, java.sql.Connection conn, boolean showConstructorButton) {
    super(parent, modal);
    initComponents();

    this.mw = parent;
    this.showConstructorButton = showConstructorButton;
    this.modal = modal;

    // Return if the connection is invalid();
    if (conn == null) {
      dispose();
      return;
    }
    repoConnection = conn;

    // get a table list
    try {
      ResultSet rs;
      Statement stmt = repoConnection.createStatement();
      rs = stmt.executeQuery("SHOW TABLES");
      while (rs.next()) {
        String str = rs.getString(1);
        tables.add(str);
      }
    }
    catch (SQLException ex) {
      Logger.getLogger(RepositoryQueryDialog.class.getName()).log(Level.SEVERE, null, ex);
    }
    tablesCombo.setModel(new DefaultComboBoxModel(tables.toArray()));

    if (tables.contains("objmeta")) {
      tablesCombo.setSelectedItem("objmeta");
    }

    // set fields
    setFieldList();

    // condition list
    conditionsList.setModel(new DefaultListModel());
  }

  private void setFieldList() {

    Object o = tablesCombo.getSelectedItem();
    if (o != null) {

      String tbl = o.toString();
      String q = "describe " + tbl;
      DefaultListModel mdl = new DefaultListModel();
      
      try {
        ResultSet rs;
        Statement stmt = repoConnection.createStatement();
        fields.clear();
        rs = stmt.executeQuery(q);
        while (rs.next()) {
          String str = rs.getString(1);
          fields.add(str);
          mdl.addElement(str);
        }
      }
      catch (SQLException ex) {
        Logger.getLogger(RepositoryQueryDialog.class.getName()).log(Level.SEVERE, null, ex);
      }

      fieldList.setModel(mdl);
      fieldList.setSelectedIndex(0);
      fieldList.setSelectionInterval(0, fields.size() - 1);
      fieldList.setSelectionMode(ListSelectionModel.MULTIPLE_INTERVAL_SELECTION);

      fieldOrderCombo.setModel(new DefaultComboBoxModel(fields.toArray()));
    }
  }

  private void buildQuery() {

    String q = "SELECT ";

    // selected fields
    Object[] selectedFields = fieldList.getSelectedValues();
    for (int ll = 0; ll < selectedFields.length; ll++) {
      q = q + selectedFields[ll].toString();
      if (ll < selectedFields.length - 1) {
        q = q + ",";
      }
    }

    // from db
    Object o = tablesCombo.getSelectedItem();
    if (o != null) {
      q = q + " FROM " + o.toString();
    }

    // conditions
    Object[] selectedConditions = conditionsList.getSelectedValues();
    if (selectedConditions.length > 0) {
      q = q + " WHERE ";
      for (int ll = 0; ll < selectedConditions.length; ll++) {
        String cstr = selectedConditions[ll].toString().trim();
        if (ll == 0) {
          cstr = cstr.replaceFirst("AND", "");
          cstr = cstr.replaceFirst("OR", "");
          cstr = cstr.replaceFirst("XOR", "");
        }
        q = q + cstr + " ";
      }
    }

    // order by
    o = fieldOrderCombo.getSelectedItem();
    if (o != null) {
      q = q + " ORDER BY " + o.toString();
    }
    o = orderDirectionCombo.getSelectedItem();
    if (o != null) {
      q = q + " " + o.toString();
    }

    q = q + ";";

    queryTxtField.setText(q);
  }

  public boolean isCancelled() {
    return cancelled;
  }

  @Override
  public void dispose() {
    super.dispose();
    if (repoConnection != null) {
      try {
        repoConnection.close();
      } catch (SQLException ex) { }
    }
  }

  protected void executeActionPerformed(java.awt.event.ActionEvent evt) {
      
    String q = queryTxtField.getText();
    try {
      Statement stmt = repoConnection.createStatement();
      ResultSet rs;
      rs = stmt.executeQuery(q);
      // pass to query results table
      QueryResultsTableDialog qrt = new QueryResultsTableDialog(mw, modal, rs, q, showConstructorButton);
      qrt.setUsedConn(repoConnection);
      qrt.setVisible(true);
    }
    catch (Exception ex) {
      JOptionPane.showMessageDialog(mw,
              ex.getMessage(),
              "Query error",
              JOptionPane.ERROR_MESSAGE);
    }
  }

  public JTextArea getQueryTxtField() {
    return queryTxtField;
  }

  public java.sql.Connection getRepoConnection() {
    return repoConnection;
  }

  public JButton getExecuteBtn() {
    return executeBtn;
  }

  /** 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.
   */
  @SuppressWarnings("unchecked")
  // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
  private void initComponents() {

    jPanel1 = new javax.swing.JPanel();
    addConditionBtn = new javax.swing.JButton();
    jLabel3 = new javax.swing.JLabel();
    jScrollPane1 = new javax.swing.JScrollPane();
    fieldList = new javax.swing.JList();
    jLabel2 = new javax.swing.JLabel();
    jScrollPane2 = new javax.swing.JScrollPane();
    conditionsList = new javax.swing.JList();
    jLabel1 = new javax.swing.JLabel();
    removeConditionBtn = new javax.swing.JButton();
    tablesCombo = new javax.swing.JComboBox();
    jScrollPane3 = new javax.swing.JScrollPane();
    queryTxtField = new javax.swing.JTextArea();
    jLabel4 = new javax.swing.JLabel();
    fieldOrderCombo = new javax.swing.JComboBox();
    orderDirectionCombo = new javax.swing.JComboBox();
    jPanel2 = new javax.swing.JPanel();
    doneBtn = new javax.swing.JButton();
    executeBtn = new javax.swing.JButton();

    setDefaultCloseOperation(javax.swing.WindowConstants.DISPOSE_ON_CLOSE);
    setTitle("Query an LTPDA Repository");
    setLocationByPlatform(true);

    jPanel1.setBorder(new javax.swing.border.SoftBevelBorder(javax.swing.border.BevelBorder.RAISED));

    addConditionBtn.setIcon(new javax.swing.ImageIcon(getClass().getResource("/mpipeline/icons/add.png"))); // NOI18N
    addConditionBtn.setRolloverIcon(new javax.swing.ImageIcon(getClass().getResource("/mpipeline/icons/add_ro.png"))); // NOI18N
    addConditionBtn.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        addConditionBtnActionPerformed(evt);
      }
    });

    jLabel3.setText("Conditions");

    fieldList.addListSelectionListener(new javax.swing.event.ListSelectionListener() {
      public void valueChanged(javax.swing.event.ListSelectionEvent evt) {
        fieldListValueChanged(evt);
      }
    });
    jScrollPane1.setViewportView(fieldList);

    jLabel2.setText("select");

    conditionsList.addListSelectionListener(new javax.swing.event.ListSelectionListener() {
      public void valueChanged(javax.swing.event.ListSelectionEvent evt) {
        conditionsListValueChanged(evt);
      }
    });
    jScrollPane2.setViewportView(conditionsList);

    jLabel1.setText("From table");

    removeConditionBtn.setIcon(new javax.swing.ImageIcon(getClass().getResource("/mpipeline/icons/subtract.png"))); // NOI18N
    removeConditionBtn.setRolloverIcon(new javax.swing.ImageIcon(getClass().getResource("/mpipeline/icons/subtract_ro.png"))); // NOI18N
    removeConditionBtn.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        removeConditionBtnActionPerformed(evt);
      }
    });

    tablesCombo.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        tablesComboActionPerformed(evt);
      }
    });

    queryTxtField.setColumns(20);
    queryTxtField.setLineWrap(true);
    queryTxtField.setRows(5);
    queryTxtField.setWrapStyleWord(true);
    jScrollPane3.setViewportView(queryTxtField);

    jLabel4.setText("order by");

    fieldOrderCombo.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        fieldOrderComboActionPerformed(evt);
      }
    });

    orderDirectionCombo.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "DESC", "ASC" }));
    orderDirectionCombo.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        orderDirectionComboActionPerformed(evt);
      }
    });

    org.jdesktop.layout.GroupLayout jPanel1Layout = new org.jdesktop.layout.GroupLayout(jPanel1);
    jPanel1.setLayout(jPanel1Layout);
    jPanel1Layout.setHorizontalGroup(
      jPanel1Layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
      .add(org.jdesktop.layout.GroupLayout.TRAILING, jPanel1Layout.createSequentialGroup()
        .addContainerGap()
        .add(jPanel1Layout.createParallelGroup(org.jdesktop.layout.GroupLayout.TRAILING)
          .add(jScrollPane3, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, 513, Short.MAX_VALUE)
          .add(jPanel1Layout.createSequentialGroup()
            .add(jPanel1Layout.createParallelGroup(org.jdesktop.layout.GroupLayout.TRAILING)
              .add(jLabel2)
              .add(jLabel1)
              .add(jLabel4))
            .addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED)
            .add(jPanel1Layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
              .add(fieldOrderCombo, 0, 182, Short.MAX_VALUE)
              .add(jScrollPane1, 0, 0, Short.MAX_VALUE)
              .add(tablesCombo, 0, 182, Short.MAX_VALUE))
            .add(18, 18, 18)
            .add(jPanel1Layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
              .add(orderDirectionCombo, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
              .add(jLabel3)
              .add(jPanel1Layout.createSequentialGroup()
                .add(jScrollPane2, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, 170, Short.MAX_VALUE)
                .addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED)
                .add(jPanel1Layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
                  .add(removeConditionBtn)
                  .add(addConditionBtn, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 33, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE))))))
        .addContainerGap())
    );

    jPanel1Layout.linkSize(new java.awt.Component[] {addConditionBtn, removeConditionBtn}, org.jdesktop.layout.GroupLayout.HORIZONTAL);

    jPanel1Layout.setVerticalGroup(
      jPanel1Layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
      .add(jPanel1Layout.createSequentialGroup()
        .addContainerGap()
        .add(jPanel1Layout.createParallelGroup(org.jdesktop.layout.GroupLayout.BASELINE)
          .add(jLabel1)
          .add(tablesCombo, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
          .add(jLabel3))
        .add(18, 18, 18)
        .add(jPanel1Layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING, false)
          .add(jPanel1Layout.createSequentialGroup()
            .add(addConditionBtn)
            .addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED)
            .add(removeConditionBtn))
          .add(jLabel2)
          .add(jScrollPane2, 0, 0, Short.MAX_VALUE)
          .add(jScrollPane1, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 188, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE))
        .addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED, 21, Short.MAX_VALUE)
        .add(jPanel1Layout.createParallelGroup(org.jdesktop.layout.GroupLayout.BASELINE)
          .add(jLabel4)
          .add(fieldOrderCombo, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
          .add(orderDirectionCombo, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE))
        .add(34, 34, 34)
        .add(jScrollPane3, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
        .addContainerGap())
    );

    jPanel1Layout.linkSize(new java.awt.Component[] {addConditionBtn, removeConditionBtn}, org.jdesktop.layout.GroupLayout.VERTICAL);

    jPanel2.setBorder(new javax.swing.border.SoftBevelBorder(javax.swing.border.BevelBorder.RAISED));

    doneBtn.setText("Done");
    doneBtn.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        doneBtnActionPerformed(evt);
      }
    });

    executeBtn.setText("Execute");
    executeBtn.addActionListener(new java.awt.event.ActionListener() {
      public void actionPerformed(java.awt.event.ActionEvent evt) {
        executeBtnActionPerformed(evt);
      }
    });

    org.jdesktop.layout.GroupLayout jPanel2Layout = new org.jdesktop.layout.GroupLayout(jPanel2);
    jPanel2.setLayout(jPanel2Layout);
    jPanel2Layout.setHorizontalGroup(
      jPanel2Layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
      .add(org.jdesktop.layout.GroupLayout.TRAILING, jPanel2Layout.createSequentialGroup()
        .addContainerGap(381, Short.MAX_VALUE)
        .add(doneBtn)
        .addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED)
        .add(executeBtn)
        .add(2, 2, 2))
    );
    jPanel2Layout.setVerticalGroup(
      jPanel2Layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
      .add(jPanel2Layout.createSequentialGroup()
        .addContainerGap()
        .add(jPanel2Layout.createParallelGroup(org.jdesktop.layout.GroupLayout.BASELINE)
          .add(executeBtn)
          .add(doneBtn))
        .addContainerGap(org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
    );

    org.jdesktop.layout.GroupLayout layout = new org.jdesktop.layout.GroupLayout(getContentPane());
    getContentPane().setLayout(layout);
    layout.setHorizontalGroup(
      layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
      .add(layout.createSequentialGroup()
        .addContainerGap()
        .add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
          .add(jPanel1, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
          .add(org.jdesktop.layout.GroupLayout.TRAILING, jPanel2, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        .addContainerGap())
    );
    layout.setVerticalGroup(
      layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
      .add(org.jdesktop.layout.GroupLayout.TRAILING, layout.createSequentialGroup()
        .addContainerGap()
        .add(jPanel1, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
        .addPreferredGap(org.jdesktop.layout.LayoutStyle.UNRELATED)
        .add(jPanel2, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
        .addContainerGap())
    );

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

  private void doneBtnActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_doneBtnActionPerformed

    cancelled = false;
    this.dispose();
  }//GEN-LAST:event_doneBtnActionPerformed

  private void addConditionBtnActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_addConditionBtnActionPerformed

    // create condition dialog
    QueryConditionDialog qcd = new QueryConditionDialog(mw, true, fields);

    qcd.setVisible(true);

    if (!qcd.isCancelled()) {
      String condition = qcd.getCondition();
      DefaultListModel mdl = (DefaultListModel) conditionsList.getModel();
      mdl.addElement(condition);
      conditionsList.setSelectionInterval(0, mdl.size() - 1);
      this.buildQuery();
    }
  }//GEN-LAST:event_addConditionBtnActionPerformed

  private void tablesComboActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_tablesComboActionPerformed

    if (conditionsList.getModel() instanceof DefaultListModel) {
      ((DefaultListModel) conditionsList.getModel()).clear();
    }
    this.setFieldList();
    this.buildQuery();
  }//GEN-LAST:event_tablesComboActionPerformed

  private void fieldListValueChanged(javax.swing.event.ListSelectionEvent evt) {//GEN-FIRST:event_fieldListValueChanged

    this.buildQuery();
  }//GEN-LAST:event_fieldListValueChanged

  private void fieldOrderComboActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_fieldOrderComboActionPerformed

    this.buildQuery();
  }//GEN-LAST:event_fieldOrderComboActionPerformed

  private void conditionsListValueChanged(javax.swing.event.ListSelectionEvent evt) {//GEN-FIRST:event_conditionsListValueChanged

    this.buildQuery();
  }//GEN-LAST:event_conditionsListValueChanged

  private void orderDirectionComboActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_orderDirectionComboActionPerformed

    this.buildQuery();
  }//GEN-LAST:event_orderDirectionComboActionPerformed

  private void executeBtnActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_executeBtnActionPerformed

    // Don't build the query again because we want to allow
    // that the user can use user defined queries.
    // this.buildQuery();

    // Execute the ActionPerformed onlyif we have a LTPDAworkbench.
    // Otherwise it is done in MATLAB
    if (mw != null) {
      executeActionPerformed(evt);
    }

  }//GEN-LAST:event_executeBtnActionPerformed

  private void removeConditionBtnActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_removeConditionBtnActionPerformed

    DefaultListModel mdl = (DefaultListModel) conditionsList.getModel();
    while (conditionsList.getSelectedIndex() >= 0) {
      mdl.remove(conditionsList.getSelectedIndex());
    }
    this.buildQuery();
  }//GEN-LAST:event_removeConditionBtnActionPerformed
  // Variables declaration - do not modify//GEN-BEGIN:variables
  private javax.swing.JButton addConditionBtn;
  private javax.swing.JList conditionsList;
  private javax.swing.JButton doneBtn;
  private javax.swing.JButton executeBtn;
  private javax.swing.JList fieldList;
  private javax.swing.JComboBox fieldOrderCombo;
  private javax.swing.JLabel jLabel1;
  private javax.swing.JLabel jLabel2;
  private javax.swing.JLabel jLabel3;
  private javax.swing.JLabel jLabel4;
  private javax.swing.JPanel jPanel1;
  private javax.swing.JPanel jPanel2;
  private javax.swing.JScrollPane jScrollPane1;
  private javax.swing.JScrollPane jScrollPane2;
  private javax.swing.JScrollPane jScrollPane3;
  private javax.swing.JComboBox orderDirectionCombo;
  private javax.swing.JTextArea queryTxtField;
  private javax.swing.JButton removeConditionBtn;
  private javax.swing.JComboBox tablesCombo;
  // End of variables declaration//GEN-END:variables
}