/*
 * FM.java -- Old main programm -- just to test the filemaker jdbc driver 
 * Filemake to SQL Converter 
 * Copyright (C) 2004 Robert Gordesch (rogo@mpiwg-berlin.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 2 of the License, or (at your option)
 * any later version.  Please read license.txt for the full details. A copy of
 * the GPL may be found at http://www.gnu.org/copyleft/lgpl.html  You should
 * have received a copy of the GNU General Public License along with this
 * program; if not, write to the Free Software Foundation, Inc., 59 Temple
 * Place, Suite 330, Boston, MA 02111-1307 USA  Created on 15.09.2003 by
 * rogo  
 */
import javax.swing.*;
import javax.swing.event.*;
import java.awt.event.*;
import java.awt.Dimension;
import java.awt.FlowLayout;
import java.awt.Image;
import java.sql.*;
import java.util.*;
import java.io.*;
/**
 *
 * <br> 
 * <h2>FileMaker Test (JDBC Database Driver Test)</h2>
 * Main class :  
 * contains the main Frame, all event handlers etc
 * <br>
 * The Database access is made over DBBean class
 * all other classes are just for the visualization of the data
 *  @version 0.1( first stable release)
 *  @author rogo
 * 
 */
public class FM extends JFrame implements ActionListener, TableModelListener
{
  /**
  * The database Bean instance.
  * 
  */
  DBBean bean;

  /**
   *  box - Tablenames
   */

  JComboBox box;

  /**
   *  box2 - Layout names
   */

  JComboBox box2;
  /**
  *  box3 - Database URLs 
  */

  JComboBox box3;
  TableComponent table;
  JTextField queryField = new JTextField("select titel from HowTos", 60);
  Vector tables = new Vector();
  Vector layouts = new Vector();

  FM fm = this;
  boolean noDBUpdate = false;
  int id = 0;
  /**
  * The result window used for query results
  */
  ResultWindow window;
  static FM fmInstance;
  static String url =  "jdbc:fmpro:http://141.14.237.42";//"jdbc:odbc:rogo2";
  static String url2 = "jdbc:postgresql://chrysantheme2/postgres";

  JPanel topPanel, contentPanel;
  int oldWidth = 0, oldHeight = 0;
  boolean resize = true;
  static FileWriter logFile;
  /**
   * tries to connect to database specified in url-Variable.
   * initializes all GUI components, then fails throws an exception
   * 
   */
  public FM() throws Exception
  {

   /* DBFront rock = new DBFront();
    rock.setURL("jdbc:odbc:rogo2");
    rock.setQuery("select productname,groupid from products");
    rock.query();
    System.out.println(rock.getNextRow());
    System.out.println(rock.getNextRow());
    System.out.println(rock.getNextRow());
    System.out.println(rock.getNextRow());
    System.out.println(rock.getColumnNames());
    rock.setQuery("select productname from products");  
    rock.query();
    System.out.println(rock.getNextRow());
    System.out.println(rock.getNextRow());
    System.out.println(rock.getNextRow());
    System.out.println(rock.getNextRow());
    System.out.println(rock.getColumnNames());
    */
    Image local = getToolkit().getImage(getClass().getResource("icons/fm.jpg"));
    if (local != null);
    setIconImage(local);
    bean = new DBBean();
    //tables = bean.getTableNames();
    //Collections.sort(tables);
    //layouts = bean.getLayoutNames((String) tables.get(0));
    //Collections.sort(layouts);
    box2 = new JComboBox();
    box3 = new JComboBox(new String[] { "jdbc:fmpro:http://localhost", "jdbc:fmpro:http://141.14.237.42","jdbc:fmpro:http://141.14.237.74:8050", "jdbc:odbc:rogo","jdbc:postgresql://erebos/test" });
    box3.setEditable(true);
    
   // Vector[] data = bean.getQueryData((String) tables.get(0));
    table = new TableComponent();
   // table.tableModel.setDataVector(data[0], data[1]);
   // table.tableModel.setRowCount((table.screenSize.height) / 20);
   // table.enlarge(getWidth(), getHeight());

    //id = table.tableModel.findColumn("ID");
    
    //System.out.println("ID is at " + id);

    box = new JComboBox(tables);
    box.addActionListener(this);
    box2.addActionListener(this);
    box3.addActionListener(this);

    contentPanel = new JPanel();
    contentPanel.setLayout(new BoxLayout(contentPanel, BoxLayout.Y_AXIS));
    topPanel = new JPanel(new FlowLayout(FlowLayout.LEFT, 5, 5));
    topPanel.add(Box.createRigidArea(new Dimension(10, 0)));
    topPanel.add(new JLabel("Choose table: "));
    topPanel.add(box);
    topPanel.add(Box.createRigidArea(new Dimension(10, 0)));
    topPanel.add(new JLabel("Choose layout: "));
    topPanel.add(box2);
    topPanel.add(Box.createRigidArea(new Dimension(10, 0)));
    topPanel.add(new JLabel("Choose dbURL: "));
    topPanel.add(box3);

    JPanel queryPanel = new JPanel();
    queryPanel.setLayout(new FlowLayout(FlowLayout.LEFT, 5, 5));
    //topPanel.add(Box.createRigidArea(new Dimension(10, 0)));
    queryPanel.add(new JLabel("Enter query: "));
    queryPanel.add(Box.createRigidArea(new Dimension(5, 0)));

    queryPanel.add(queryField);
    queryPanel.add(Box.createRigidArea(new Dimension(10, 0)));
    JButton makeQuery = new JButton("make Query");
    queryPanel.add(makeQuery);
    makeQuery.addActionListener(fm);
    queryField.addActionListener(fm);
    contentPanel.add(topPanel);
    contentPanel.add(queryPanel);
    //System.out.println(contentPanel.getPreferredSize());
    getContentPane().add(contentPanel, "North");
    getContentPane().add(table);
    this.addWindowListener(new WindowAdapter()
    {
      public void windowClosing(WindowEvent e)
      {
        try
        {
          closeLog();
        } catch (Exception e5)
        {
          System.out.println("Error while closing logfile " + e5);
        }
        System.exit(0);
      }
    });
    addComponentListener(new ComponentAdapter()
    {
      public void componentResized(ComponentEvent e)
      {

        // System.out.println("Window resized " + getWidth() + " " + getHeight());
        //Dimension size=table.tableScroller.getPreferredSize();
        //table.setAutoResizeMode(table.AUTO_RESIZE_OFF);
        // if (table.tableScroller.getPreferredSize().width < getWidth() && fm.isVisible())
        // table.sizeToFit(getWidth(), getHeight());
        if (oldWidth != getWidth() || oldHeight != getHeight())
        {
          table.enlarge(getWidth(), getHeight());

          Dimension dim = table.table.getPreferredSize();
          int size = contentPanel.getPreferredSize().height + new JScrollBar().getPreferredSize().height + new JMenuBar().getPreferredSize().height + 10;
          table.tableScroller.setPreferredSize(new Dimension(getWidth() - 15, getHeight() - size));
          Dimension d2 = table.tableScroller.getPreferredSize();

          oldWidth = getWidth();
          oldHeight = getHeight();
          //   System.out.println("fit" + getWidth() + " " + oldWidth);
          //table.table.setPreferredScrollableViewportSize(dim);

          table.setPreferredSize(new Dimension(d2.width + 15, d2.height + 5));
          table.table.revalidate();
          table.tableScroller.revalidate();
          table.revalidate();
          if (getHeight() > table.table.getPreferredSize().height + (2 * size))
            setSize(getWidth(), table.table.getPreferredSize().height + 2 * size);
          // System.out.println("size" + size);
        }
        repaint();
        setTitle("Filemaker Database Test - Table " + fm.box.getSelectedItem());

      }
    });
    setMenu();
    setSize(700, 600);
    pack();

    setVisible(true);

    // repaint();

  }

  /**
   *  
   */
  public static void main(String[] args)
  {
    try
    {
      System.setErr(System.out);
      if (System.getProperty("os.name").startsWith("W"))
        javax.swing.UIManager.setLookAndFeel(new com.sun.java.swing.plaf.windows.WindowsLookAndFeel());

      fmInstance = new FM();
      fmInstance.table.tableModel.addTableModelListener(fmInstance);
      if (!new File("./html").exists())
        new File("./html").mkdirs();
      logFile = new FileWriter("./html/index.html");
      openLog();
      // Statement stm = fm.bean.getConnection().createStatement();
      // stm.executeUpdate("DELETE FROM \""+fm.tables.get(0)+"\" WHERE \"erstellt von\"='rogo') ");
      // stm.executeUpdate("INSERT  INTO \""+fm.tables.get(0)+"\" (TITLE,)  VALUES ('Hier kommt Rogo') ");
      // stm.executeUpdate("UPDATE   \""+fm.tables.get(1)+"\" SET  \"Year\"='2001' WHERE ID='IT00002' ");

      // this.setDataVector(tableData, columnNames);
      // System.out.println(tableData + " " + columnNames);

    } catch (Exception e)
    {
      JOptionPane pane = new JOptionPane(e.getMessage() + "  \n URL: " + url, JOptionPane.ERROR_MESSAGE);

      JDialog dialog = pane.createDialog(null, " Exception occured while connecting");
      e.printStackTrace();
      dialog.addWindowListener(new WindowAdapter()
      {
        public void windowClosing(WindowEvent e)
        {
          System.exit(0);
        }
      });
      dialog.addComponentListener(new ComponentAdapter()
      {

        public void componentHidden(ComponentEvent e)
        {

          System.exit(0);
        }
      });
      dialog.show();
    }
  }
  public void actionPerformed(ActionEvent e)
  {
    String command = e.getActionCommand();
    //   System.out.println("command " + command);
    if (command == "comboBoxEdited")
    {
      System.out.println("hey" + box3.getSelectedItem());
      ((DefaultComboBoxModel) box3.getModel()).insertElementAt(box3.getSelectedItem(), 0);
    }
    if (command == "comboBoxChanged")
    {
      try
      {

        if (e.getSource() == box3)
        {
          String url = box3.getSelectedItem().toString();
          bean.url = (url != null) ? url : bean.url;
          bean.connection = null;
          bean.getConnection();
          tables = bean.getTableNames();
          Collections.sort(tables);
          layouts = bean.getLayoutNames((String) tables.get(0));
          Collections.sort(layouts);
          box.setModel(new DefaultComboBoxModel(tables));
          box2.setModel(new DefaultComboBoxModel(layouts));

        }
        if (e.getSource() != box2)
          box2.setModel(new DefaultComboBoxModel(bean.getLayoutNames(box.getSelectedItem().toString())));
        String query = "select * from " + bean.getQC() + box.getSelectedItem() + bean.getQC();
        if (box2.getSelectedItem() != "")
          query += " layout " + bean.getQC() + box2.getSelectedItem() + bean.getQC();

        Vector[] data = bean.getQueryData(query, bean.getMaxHits());
        noDBUpdate = true;
        if (data[0].isEmpty() || data[1].isEmpty())
        {
          if (!data[0].isEmpty())
          {
            data[0] = new Vector();
            Vector row = new Vector();
            row.add("Error empty layout!!!");
            data[0].addElement(row);
          }
          // if (data[1].isEmpty())
          {
            data[1] = new Vector();

            data[1].addElement("Error empty layout!!!");
          }
        }
        table.tableModel.setDataVector(data[0], data[1]);
        int numRows = table.screenSize.height / 20;
        table.tableModel.setRowCount((numRows > table.tableModel.getRowCount()) ? numRows : table.tableModel.getRowCount());
        noDBUpdate = false;
        //table.vergroessern(getWidth(),getHeight());
        id = table.tableModel.findColumn("ID");
        //  System.out.println("ID is at " + id);
        setTitle("Filemaker Database Test - Table " + fm.box.getSelectedItem());
        pack();

      } catch (Exception e2)
      {
      }
    }
    if (command == "show Driver features")
    {
      ResultWindow result = new ResultWindow(this);
      try
      {
        Vector[] vecs = bean.TestDB(bean.getConnection().getMetaData());
        result.updateResult(vecs[0], vecs[1]);
        // result.pack();
        result.title = "Database features";
        result.setVisible(true);
        String name = result.writeResult();
        if (ResultWindow.counter == 1)
          appendLog(result.title, name);
      } catch (Exception e2)
      {
      }
    }
    if (command.equals("make Query"))
      testQuery(queryField.getText());
    if (e.getSource() == queryField)
      testQuery(queryField.getText());

  }
  /**
   * Test the specified Query and display the result
   */
  public void testQuery(String query)
  {
    if (query == "")
      query = "select Titel,Standort,id from HowTos";

    if (window == null)
      window = new ResultWindow(fm);
    for (int i = 0; i < tables.size(); ++i)
      if (query.toLowerCase().indexOf(((String) tables.get(i)).toLowerCase()) >= 0)
      {
        window.title = " Table " + (String) tables.get(i) + " -  Query done: " + query;
        break;
      } else
        window.title = "Error! - No valid tablename found";

    try
    {
      if ((query.toLowerCase().indexOf("insert") >= 0) || (query.toLowerCase().indexOf("delete") >= 0) || (query.toLowerCase().indexOf("alter") >= 0) || (query.toLowerCase().indexOf("update") >= 0))
      {
        Statement stm = fm.bean.getConnection().createStatement();
        stm.executeUpdate(query);
        Vector vec = new Vector();
        Vector vec2 = new Vector();
        Vector vec3 = new Vector();
        vec2.add(query);
        vec3.add(vec2);
        vec.add("Insert, update, etc done! No results available");
        // vec.add("no Results were produced");

        window.updateResult(vec3, vec);
        window.pack();
        window.setVisible(true);

      } else
      {
        Vector[] vecs = bean.getQueryData(query, bean.getMaxHits());
        if (vecs[1].isEmpty())
          throw new Exception("Wrong columnname");
        window.updateResult(vecs[0], vecs[1]);
        window.pack();
        String name = window.writeResult();
        appendLog(window.title, name);
      }
      window.setVisible(true);
    } catch (Exception e)
    {
      System.out.println("Exception occured");
      System.out.println(e.getMessage());
      Vector vec = new Vector();
      Vector vec2 = new Vector();
      Vector vec3 = new Vector();
      vec2.add(e.getMessage());
      vec3.add(vec2);
      vec.add("Exception occured! No results available");
      //vec.add("no Results were produced");

      window.updateResult(vec3, vec);
      window.pack();
      window.setVisible(true);

    }

  }
  /**
   * invoked then the user changes the table or the tableModel changes
   */
  public void tableChanged(TableModelEvent e)
  {
    //  if (e.getType() == e.INSERT)
    //System.out.println("Insert done");
    // if (e.getType() == e.UPDATE)
    // System.out.println("Update done");
    //if (e.getType() == e.DELETE)
    //System.out.println("Delete done");
    if (e.getType() == TableModelEvent.UPDATE && !noDBUpdate)
    {
      int row = e.getFirstRow(), col = e.getColumn();
      String columnName = table.tableModel.getColumnName(col);
      if (id == -1)
        return;
      Object test = table.tableModel.getValueAt(row, id);
      String idVal = (test == null) ? "" : test.toString();
      String value = table.tableModel.getValueAt(row, col).toString();

      System.out.println("Got in " + columnName + " " + idVal + " " + box.getSelectedItem());
      if (table.tableModel.getValueAt(row, col) instanceof Vector)
      {
        System.out.println("found a Vector");
        return;
      }
      try
      {
        Statement stm = bean.getConnection().createStatement();
        //  stm.executeUpdate("DELETE FROM \""+fm.tables.get(0)+"\" WHERE \"erstellt von\"='rogo') ");

        if (idVal == "")
          stm.executeUpdate("INSERT  INTO " + bean.getQC() + box.getSelectedItem() + bean.getQC() + " (" + bean.getQC() + columnName + bean.getQC() + ")  VALUES ('" + value + "') ");
        else
          stm.executeUpdate("UPDATE   " + bean.getQC() + box.getSelectedItem() + bean.getQC() + " SET  " + bean.getQC() + columnName + bean.getQC() + "='" + value + "' WHERE ID='" + idVal + "' ");
      } catch (Exception e2)
      {
        System.out.println(e2.getMessage());
      }
    }
  }

  /**
   * initialize the menubar
   */
  private void setMenu()
  {
    JMenuBar menubar = new JMenuBar();
    JMenu menu = new JMenu("Options");
    JMenuItem item = new JMenuItem("show Driver features");
    item.addActionListener(fm);
    menu.add(item);
    menubar.add(menu);
    menu.setBackground(fm.getBackground());
    menubar.setBackground(fm.getBackground());
    setJMenuBar(menubar);
  }
  /**
   *  open the log  file (index.html)
   */

  public static void openLog() throws Exception
  {

    StringBuffer buff = new StringBuffer();
    buff.append("<html>");
    buff.append("<head>");
    buff.append("\n<title>");
    buff.append("FileMaker Test");
    buff.append("</title>\n");
    buff.append("</head>\n");

    buff.append("<body>\n");
    buff.append("<center>\n");
    buff.append("\n<h3>");
    buff.append("FileMaker Test");
    buff.append("</h3>\n");

    logFile.write(TableComponent.convertUml(buff).toString());
    logFile.flush();
  }
  /**
   * Append one entry to the log file(index.html)
   */
  public static void appendLog(String entry, String name) throws Exception
  {
    logFile.write("<a href=\"./" + name.substring(name.lastIndexOf("/") + 1) + "\">");
    logFile.write(entry + "</a>");
    logFile.write("<br>\n<br>\n");
    logFile.flush();
  }
  /**
   * closes the log file invoked then the application closes
   */
  public static void closeLog() throws Exception
  {
    logFile.write("\n</body>");
    logFile.write("\n</html>");

    logFile.close();
  }
}