package net.sourceforge.phpeclipse.wiki.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import net.sourceforge.phpeclipse.wiki.editor.WikiEditorPlugin;
import net.sourceforge.phpeclipse.wiki.internal.ConfigurationManager;
import net.sourceforge.phpeclipse.wiki.internal.IConfiguration;
import net.sourceforge.phpeclipse.wiki.preferences.Util;

public class WikipediaDB {

  public static void dump(ResultSet rs) throws SQLException {

    // the order of the rows in a cursor
    // are implementation dependent unless you use the SQL ORDER statement
    ResultSetMetaData meta = rs.getMetaData();
    int colmax = meta.getColumnCount();
    int i;
    Object o = null;

    // the result set is a cursor into the data. You can only
    // point to one row at a time
    // assume we are pointing to BEFORE the first row
    // rs.next() points to next row and returns true
    // or false if there is no next row, which breaks the loop
    for (; rs.next();) {
      for (i = 0; i < colmax; ++i) {
        o = rs.getObject(i + 1); // Is SQL the first column is indexed
        // with 1 not 0
        System.out.print(o.toString() + " ");
      }

      System.out.println(" ");
    }
  }

  public static ArrayList getResultAsString(ResultSet rs) throws SQLException {
    ArrayList list = new ArrayList();
    int maxProposals = 500;
    // the order of the rows in a cursor
    // are implementation dependent unless you use the SQL ORDER statement
    ResultSetMetaData meta = rs.getMetaData();
    int colmax = meta.getColumnCount();
    int i;
    Object o = null;

    // the result set is a cursor into the data. You can only
    // point to one row at a time
    // assume we are pointing to BEFORE the first row
    // rs.next() points to next row and returns true
    // or false if there is no next row, which breaks the loop
    for (; rs.next();) {
      for (i = 0; i < colmax; ++i) {
        o = rs.getObject(i + 1); // Is SQL the first column is indexed
        // with 1 not 0
        list.add(o.toString());
        maxProposals--;
        if (maxProposals <= 0) {
          return list;
        }
      }
    }
    return list;
  }

  public static String getFirstRow(ResultSet rs) throws SQLException {
    // the order of the rows in a cursor
    // are implementation dependent unless you use the SQL ORDER statement
    ResultSetMetaData meta = rs.getMetaData();
    int colmax = meta.getColumnCount();
    int i;
    Object o = null;

    // the result set is a cursor into the data. You can only
    // point to one row at a time
    // assume we are pointing to BEFORE the first row
    // rs.next() points to next row and returns true
    // or false if there is no next row, which breaks the loop
    for (; rs.next();) {
      for (i = 0; i < colmax; ++i) {
        o = rs.getObject(i + 1); // Is SQL the first column is indexed
        // with 1 not 0
        return o.toString();
      }
    }
    return null;
  }

  public static void main(String[] args) {
    WikipediaDB db = null;

    try {
      db = new WikipediaDB();
    } catch (Exception ex1) {
      ex1.printStackTrace(); // could not start db
      return; // bye bye
    }

    try {
      // do a query
      ArrayList list = db.queryPrefixTexts("Programming:PHP");
      //      db.query("SELECT * FROM cur WHERE cur_title like 'Programming:PHP%'"); // WHERE num_col < 250");
      for (int i = 0; i < list.size(); i++) {
        System.out.println(list.get(i).toString());
      }
      // at end of program
      db.shutdown();
    } catch (SQLException ex3) {
      ex3.printStackTrace();
    }
  }

  //  private static void readFile(WikipediaDB db, String filename) {
  //    FileReader fileReader;
  //    try {
  //      BufferedReader bufferedReader = new BufferedReader(new FileReader(filename));
  //      // String line;
  //      LineTokenizer lineTokenizer = new LineTokenizer();
  //      StringBuffer line = new StringBuffer(1024);
  //      while (lineTokenizer.getToken(line, bufferedReader)) {
  //        if (line.length() == 0) {
  //          // this should not happen
  //        } else {
  //          // try {
  //          // db.update("INSERT INTO wp_titles(title) VALUES('" + line + "')");
  //          System.out.println(line);
  //          line.delete(0, line.length());
  //          // addLine(line);
  //          // } catch (SQLException ex3) {
  //          //// ex3.printStackTrace();
  //          // }
  //        }
  //      }
  //      bufferedReader.close();
  //    } catch (FileNotFoundException e) {
  //      // ignore this
  //      // TODO DialogBox which asks the user if she/he likes to build new index?
  //    } catch (IOException e) {
  //      // TODO Auto-generated catch block
  //      e.printStackTrace();
  //    }
  //  }

  private final Connection conn;

  private final PreparedStatement fGetPrefixTitles;

  private final PreparedStatement fGetPrefixTexts;

  private final PreparedStatement fGetExactText;

  public WikipediaDB() throws Exception // note more general exception
  {

    // Load the Database Engine JDBC driver
    // mysql-connector.jar should be in the class path or made part of the current jar
    Class.forName("com.mysql.jdbc.Driver");

    // determine the first SQL configuration
    IConfiguration configuration = null;
    try {
      List allConfigsList = ConfigurationManager.getInstance().getConfigurations();
      ArrayList configsList = new ArrayList();
      for (int i = 0; i < allConfigsList.size(); i++) {
        configuration = (IConfiguration) allConfigsList.get(i);
        if (configuration.getType().equals(WikiEditorPlugin.WIKIPEDIA_SQL)) {
          break;
        }
        configuration = null;
      }
    } catch (Throwable th) {
      // 
    }

    // connect to the database. This will load the db files and start the
    // database if it is not alread running.
    // db_file_name_prefix is used to open or create files that hold the state
    // of the db.
    // It can contain directory names relative to the
    // current working directory
    if (configuration != null) {
      conn = DriverManager.getConnection(configuration.getURL(), configuration.getUser(), configuration.getPassword());
    } else {
      // default configuration for XAMPP distribution
      conn = DriverManager.getConnection("jdbc:mysql://localhost/wikidb", // filenames
          "root", // category
          ""); // password
    }
    fGetPrefixTitles = conn.prepareStatement("SELECT cur_title FROM cur WHERE LOWER( cur_title ) like ?");
    fGetPrefixTexts = conn.prepareStatement("SELECT cur_text FROM cur WHERE LOWER( cur_title ) like ?");
    fGetExactText = conn.prepareStatement("SELECT cur_text FROM cur WHERE cur_title = ?");
  }

  //use for SQL commands CREATE and SELECT
  public synchronized void query(String expression) throws SQLException {

    Statement st = null;
    ResultSet rs = null;

    st = conn.createStatement(); // statement objects can be reused with
    // repeated calls to execute but we
    // choose to make a new one each time
    rs = st.executeQuery(expression); // run the query

    // do something with the result set.
    dump(rs);
    st.close(); // NOTE!! if you close a statement the associated ResultSet is
    // closed too
    // so you should copy the contents to some other object.
    // the result set is invalidated also if you recycle an Statement
    // and try to execute some other query before the result set has been
    // completely examined.
  }

  public synchronized ArrayList queryPrefixTitle(String prefix) throws SQLException {
    fGetPrefixTitles.setString(1, prefix.toLowerCase() + '%');
    ResultSet rs = null;
    rs = fGetPrefixTitles.executeQuery(); // run the query
    // do something with the result set.
    ArrayList list = getResultAsString(rs);
    if (list != null) {
      // convert to editor format
      for (int i = 0; i < list.size(); i++) {
        list.set(i, Util.db2TitleLink((String) list.get(i)));
      }
    }
    return list;
    //    st.close(); // NOTE!! if you close a statement the associated ResultSet is
  }

  public synchronized ArrayList queryPrefixTexts(String prefix) throws SQLException {
    fGetPrefixTexts.setString(1, prefix.toLowerCase() + '%');
    ResultSet rs = null;
    rs = fGetPrefixTexts.executeQuery(); // run the query
    // do something with the result set.
    return getResultAsString(rs);
    //    st.close(); // NOTE!! if you close a statement the associated ResultSet is
  }

  public synchronized String queryExactText(String prefix) throws SQLException {
    fGetExactText.setString(1, prefix);
    ResultSet rs = null;
    rs = fGetExactText.executeQuery(); // run the query
    // do something with the result set.
    return getFirstRow(rs);
    //    st.close(); // NOTE!! if you close a statement the associated ResultSet is
  }

  public static String getExactText(String prefix) {
    WikipediaDB db = null;

    try {
      db = new WikipediaDB();
    } catch (Exception ex1) {
      ex1.printStackTrace(); // could not start db
      return null; // bye bye
    }

    try {
      String text = db.queryExactText(prefix);
      db.shutdown();
      return text;
    } catch (SQLException ex3) {
      ex3.printStackTrace();
    }
    return null;
  }

  public void shutdown() throws SQLException {

    conn.close(); // if there are no other open connection
    // db writes out to files and shuts down
    // this happens anyway at garbage collection
    // when program ends
  }

  //use for SQL commands DROP and INSERT and UPDATE
  public synchronized void update(String expression) throws SQLException {

    Statement st = null;

    st = conn.createStatement(); // statements

    int i = st.executeUpdate(expression); // run the query

    if (i == -1) {
      System.out.println("db error : " + expression);
    }

    st.close();
  }
}