package org.plenix.translator.sql;

import java.util.Vector;
import java.util.Hashtable;
import java.sql.SQLException;

import org.plenix.translator.Language;
import org.plenix.translator.TranslatorImpl;
import org.plenix.translator.TranslatorException;

public class SQLTranslator extends TranslatorImpl {
  private String connectionName = null;
  private Language[] languages = null;

  public SQLTranslator(String theConnectionName) throws SQLException {
    this.connectionName = theConnectionName;

    Vector languageList = DBUtil.executeQuery(connectionName, "SELECT * FROM language ORDER BY ordering");
    int languageCount = languageList.size();
  
    this.languages = new Language[languageCount];
    
    for (int i = 0; i < languageCount; i++) {
      Hashtable row = (Hashtable) languageList.elementAt(i);
      String languageCode = (String) row.get("CODE");
      String languageName = (String) row.get("NAME");
      this.languages[i] = new Language(languageCode, languageName);
    }
  }

  public Language getLanguage(String code) {
    for (int i = 0; i < this.languages.length; i++) {
      if (this.languages[i].getCode().equals(code)) {
        return this.languages[i];
      }
    }

    return null;
  }

  public Language[] getLanguages() {
    return this.languages;
  }


  public String[] getReferenceTerms(String sourceLanguage, String term)
    throws TranslatorException
  {
    String sqlStatement =
      "SELECT reference_term " +
      "FROM   term " +
      "WHERE  language = '" + sourceLanguage + "' AND " +
      "       term = '" + term + "' " +
      "ORDER BY reference_term";

    return getTerms(sqlStatement, "REFERENCE_TERM");
  }

  public String[] translate(String referenceTerm, String targetLanguage)
    throws TranslatorException
  {
    String sqlStatement =
      "SELECT term " +
      "FROM   term " +
      "WHERE  reference_term = '" + referenceTerm + "' AND " +
      "       language = '" + targetLanguage + "' " +
      "ORDER BY term";

    return getTerms(sqlStatement, "TERM");
  }

  private String[] getTerms(String sqlStatement, String columnName)
    throws TranslatorException
  {
    Vector termVector = null;

    try {
      termVector = DBUtil.executeQuery(this.connectionName, sqlStatement);
    } catch (SQLException e) {
      throw new TranslatorException(e.getMessage());
    }

    int termCount = termVector.size();

    if (termCount == 0) {
      return null;
    }

    String[] referenceTerms = new String[termCount];

    for (int i = 0; i < termCount; i++) {
      Hashtable termRow = (Hashtable) termVector.elementAt(i);
      referenceTerms[i] = (String) termRow.get(columnName);
    }

    return referenceTerms;
  }

  public static void main(String[] args) throws Exception {
    SQLTranslator translator = new SQLTranslator("dictionary");
    String[] referenceTerms = translator.getReferenceTerms(args[0], args[1]);

    for (int i = 0; i < referenceTerms.length; i++) {
      System.out.println(referenceTerms[i]);
    }
  }
}