package com.buzzphone.server;

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import java.util.*;

import com.buzzphone.server.Google.*;

/**
 * The servlet that works with the trivia midlet
 */
public class MIDPTrivia extends HttpServlet {

  private static int DOWNLOAD = 1;
  private static int SUBMIT = 2;
  private static int GOOGLE = 3;
  private static int SYNC01 = 4;
  private static int SYNC02 = 5;
  private static String googleKey;
  private static String fileroot;

  public void init () throws ServletException {
    try {
      Class.forName("org.hsqldb.jdbcDriver");
    } catch (Exception e) {
      e.printStackTrace();
      throw new ServletException("Cannot find DB driver");
    }

    try {
      Properties config = new Properties();
      config.load(this.getClass().getResourceAsStream("/config.properties"));
      googleKey = config.getProperty("GoogleKey");
      fileroot = config.getProperty("DBFileRoot");
    } catch (Exception e) {
      e.printStackTrace();
      throw new ServletException("Property file error");
    }
  }

  public void doPost(HttpServletRequest request,
                    HttpServletResponse response)
              throws ServletException, IOException {

    response.setContentType("application/binary");
    try {
      InputStream in = request.getInputStream();
      OutputStream out = response.getOutputStream();
      DataInputStream din = new DataInputStream(in);
      DataOutputStream dout = new DataOutputStream(out);

      int opcode = din.readInt ();
      if ( opcode == DOWNLOAD) {
        processDownload (din, dout);
      } else if ( opcode == SUBMIT) {
        processSubmit (din, dout);
      } else if ( opcode == GOOGLE) {
        processGoogle (din, dout);
      } else if ( opcode == SYNC01) {
        processSync01 (din, dout);
      } else if ( opcode == SYNC02) {
        processSync02 (din, dout);
      } else {
        throw new Exception("wrong op code");
      }
      din.close();
      dout.close();
      in.close();
      out.close();

    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  private void processSubmit (DataInputStream din,
              DataOutputStream dout) throws Exception {
    Connection c =
      DriverManager.getConnection ("jdbc:hsqldb:" +
                     fileroot + "TriviaDB", "sa", "");
    int correctNum = 0, totalNum = 0, answer = 0;
    String questionId = null;
    PreparedStatement pstmt =
        c.prepareStatement("select id from " +
            "Questions where id=? and answerId=?",
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    int size = din.readInt();
    for (int i = 0; i < size; i++) {
      questionId = din.readUTF();
      answer = din.readInt();
      pstmt.setString(1, questionId);
      pstmt.setString(2, Integer.toString(answer));
      ResultSet rs = pstmt.executeQuery();
      if (rs.next()) correctNum++;
      totalNum++;
    }
    dout.writeInt(correctNum);
    dout.writeInt(totalNum - correctNum);
    dout.flush();
    pstmt.close();
    c.close();
  }

  private void processGoogle (DataInputStream din,
             DataOutputStream dout) throws Exception {
    String keywords = din.readUTF();
    boolean isPhrase = din.readBoolean();
    int maxBuffer = din.readInt();
    int maxResults = din.readInt();

    Facade f = new Facade (googleKey);
    f.getResults(keywords, isPhrase,
                 maxBuffer, maxResults);

    dout.writeUTF(f.spellSugg);
    dout.writeInt(f.urls.length);
    for (int i = 0; i < f.urls.length; i++) {
      dout.writeUTF(f.urls[i]);
      dout.writeUTF(f.snippets[i]);
    }
    dout.flush();
  }

  private void processDownload (DataInputStream din,
                                DataOutputStream dout) throws Exception {

    String category = din.readUTF();

    Connection c =
      DriverManager.getConnection ("jdbc:hsqldb:" + fileroot +
                                   "TriviaDB", "sa", "");
    Statement stmt = c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                       ResultSet.CONCUR_READ_ONLY);
    ResultSet rs1 =
            stmt.executeQuery("select id, question, hasImage, " +
            "hasMusic, sendAnswer, answerHint, answerId" +
            " from Questions " +
            "where category='" + category + "'");
    PreparedStatement pstmt =
        c.prepareStatement("select choiceId, choice from Choices " +
            "where questionId=? order by choiceId ASC",
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    int questionNum = 0;
    while (rs1.next()) questionNum++;
    rs1.beforeFirst();

    dout.writeInt(questionNum);
    while (rs1.next()) {
      String questionId = rs1.getString(1);
      dout.writeUTF(questionId);
      // the question text
      dout.writeUTF(rs1.getString(2));
      pstmt.setString(1, questionId);
      ResultSet rs2 = pstmt.executeQuery();

      int choiceNum = 0;
      while (rs2.next()) choiceNum++;
      dout.writeInt(choiceNum);
      rs2.beforeFirst();
      while (rs2.next()) {
        // the choice text
        dout.writeUTF(rs2.getString(2));
      }
      rs2.close();

      boolean hasImage = rs1.getBoolean(3);
      dout.writeBoolean(hasImage);
      if (hasImage) {
        byte [] buf = readFromFile("png/" + questionId + ".png");
        dout.writeInt(buf.length);
        dout.write(buf, 0, buf.length);
      }
      boolean hasMusic = rs1.getBoolean(4);
      dout.writeBoolean(hasMusic);
      if (hasMusic) {
        byte [] buf = readFromFile("midi/" + questionId + ".mid");
        dout.writeInt(buf.length);
        dout.write(buf, 0, buf.length);
      }
      boolean sendAnswer = rs1.getBoolean(5);
      dout.writeBoolean(sendAnswer);
      if (sendAnswer) {
        // The answerHint
        dout.writeUTF(rs1.getString(6));
        // The answerId
        dout.writeInt(rs1.getInt(7));
      }
      pstmt.close();
    }
    dout.flush();
    stmt.close();
    c.close();
  }


  private void processSync01 (DataInputStream din,
             DataOutputStream dout) throws Exception {

    long clientSyncTime = din.readLong();

    Connection c =
      DriverManager.getConnection ("jdbc:hsqldb:" +
                      fileroot + "TriviaDB", "sa", "");
    Statement stmt = c.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);

    // Get the server sync time stamp
    ResultSet rs = stmt.executeQuery("select ts from " +
                             "History where time < 0");
    rs.next();
    long serverSyncTime = rs.getLong(1);
    rs.close();

    // Get the size of the update list
    ResultSet rs1 =
            stmt.executeQuery("select ts, correctNum, " +
            "wrongNum, time " + " from History " +
            "where ts > " + clientSyncTime +
            " and time > 0");
    int size = 0;
    while (rs1.next()) size++;
    rs1.beforeFirst();

    // Return the time stamp,
    // update list size and the list itself
    dout.writeLong(serverSyncTime);
    dout.writeInt(size);
    while (rs1.next()) {
      dout.writeInt (rs1.getInt(2));
      dout.writeInt (rs1.getInt(3));
      dout.writeLong (rs1.getLong(4));
      dout.writeLong (rs1.getLong(1));
    }
    dout.flush();
    rs1.close();
    stmt.close();
    c.close();
  }

  private void processSync02 (DataInputStream din,
             DataOutputStream dout) throws Exception {

    Connection c =
      DriverManager.getConnection ("jdbc:hsqldb:" +
        fileroot + "TriviaDB", "sa", "");
    PreparedStatement pstmt =
        c.prepareStatement("insert into History " +
            "(ts, correctNum, wrongNum, time) " +
            "values (?, ?, ?, ?)",
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    int size = din.readInt();
    for (int i = 0; i < size; i++) {
      pstmt.setInt (2, din.readInt());
      pstmt.setInt (3, din.readInt());
      pstmt.setLong (4, din.readLong());
      pstmt.setLong (1, din.readLong());
      pstmt.executeUpdate();
    }
    pstmt.close();

    Statement stmt = c.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);
    stmt.executeUpdate("update History set ts=" +
                       System.currentTimeMillis() +
                       " where time < 0");
    stmt.close();
    c.close();
  }


  public byte [] readFromFile (String name) throws Exception {
    FileInputStream fin = new FileInputStream(fileroot + name);
    ByteArrayOutputStream baos = new ByteArrayOutputStream ();
    byte [] buf = new byte [256];
    int i = 0;
    while ( (i = fin.read(buf)) != -1 ) {
      baos.write(buf, 0, i);
    }
    baos.flush();
    byte [] result = baos.toByteArray();
    baos.close();
    fin.close();
    return result;
  }

}
