package com.buzzphone.server.WML;

import java.sql.*;

/**
 * The Trivia game helper method used in the WML JSP page
 */
public class TriviaHelper {

  static String className = "org.hsqldb.jdbcDriver";
  static String jdbcURL = "jdbc:hsqldb:/Users/juntao/Java/tomcat/jakarta-tomcat-4.1.24/webapps/Trivia/database/TriviaDB";

  static public String [] getQuestions (String category) {
    Connection conn= null;
    Statement stmt= null;
    ResultSet rs = null;

    try {
      Class.forName(className);
      conn = DriverManager.getConnection (jdbcURL, "sa", "");
      conn.setAutoCommit(true);
      stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                  ResultSet.CONCUR_READ_ONLY);
      rs = stmt.executeQuery("select id from Questions " +
                             "where category='" + category +
                             "' and hasImage=1 and hasMusic=0");
      int size = 0;
      while (rs.next()) size++;
      String [] result = new String [size];
      rs.beforeFirst();
      for (int i = 0; i < size; i++) {
        rs.next();
        result[i] = rs.getString(1);
      }
      return result;
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        if (rs != null ) rs.close();
        if (stmt != null) stmt.close();
        if (conn != null) conn.close();
      } catch (Exception ee) {}
    }
    return null;
  }
  
  static public String getResult (String [] questionIds, String [] answers) {
    Connection conn= null;
    PreparedStatement pstmt= null;
    ResultSet rs = null;

    try {
      Class.forName(className);
      conn = DriverManager.getConnection (jdbcURL, "sa", "");
      conn.setAutoCommit(true);
      int correctNum = 0;
      pstmt = conn.prepareStatement("select id from Questions " +
                                    "where id=? and answerId=?");
      for (int i = 0; i < questionIds.length; i++) {
        pstmt.setString(1, questionIds[i]);
        pstmt.setString(2, answers[i]);
        rs = pstmt.executeQuery();
        if (rs.next()) correctNum++;
        rs.close();
      }
      pstmt.close();

      return correctNum + " correct out of " +
             questionIds.length + " questions";

    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        if (rs != null ) rs.close();
        if (pstmt != null) pstmt.close();
        if (conn != null) conn.close();
      } catch (Exception ee) {}
    }
    return null;
  }
  
}
