package uk.co.patrickhaston.wiki;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.pool.OracleDataSource;


public class Comment 
{
  private String dbUser = "";
  private String dbPassword = "";
  private String dbUrl = "";
  private OracleDataSource ods = null; 

  public Comment()
  {
    Database db = new Database();
    dbUser = db.getDbUser();
    dbPassword = db.getDbPassword();
    dbUrl = db.getDbUrl();
  }

  public String showComments(String comment_id, String user_id, String reply_to, String page_id)
  {
    String SQL = "";
    String html = "";
    if( comment_id == null ) return "Error: no comment_id specified";
    if( reply_to == null ) reply_to = "";
    try
    {
      OracleDataSource ods = new OracleDataSource();
      ods.setURL(dbUrl);
      ods.setUser(dbUser);
      ods.setPassword(dbPassword);

      Connection conn = ods.getConnection();
      Statement state = conn.createStatement();
      
      boolean allow_edits = false;
      SQL = "SELECT comment_id, comment_type_code, comment_text, created_by, created_on, level, lpad('+',level, '+') indent "
        + "FROM wiki_comment "
        + "CONNECT BY PRIOR comment_id = reply_to_comment_id "
        + "START WITH comment_id = " + comment_id;

      ResultSet rs = state.executeQuery(SQL);

      html = html + "<table>";
      
      int rownum = 0;
      while( rs.next() && rownum < 10 )
      {
        rownum++;
        String full_text = rs.getString("comment_text");
        String this_comment_id = rs.getString("comment_id");
        String style = "wiki_" + rs.getString("comment_type_code").toLowerCase();
        if( full_text == null ) full_text = "";
        html = html + "\n<form method='post'>";
        html = html + "<tr><td><span class='" + style + "'>" + rs.getString("indent")
          + rs.getString("comment_type_code") + "</span></td><td>" + full_text + 
          "</td><td><span class='wiki_comment'>" + rs.getString("created_on") 
          + "</span></td><td><span class='wiki_comment'>" + rs.getString("created_by") + "</span></td>";
        if( reply_to.equals( this_comment_id ) )
        {
          // the user has asked to reply to this comment
          CommentType wikiCommentType = new CommentType();
          
          html = html + "<td>&nbsp;</td></tr>\n<tr><td colspan='5'>";
          html = html + 
            "<input type='hidden' name='comment_type_code' value='TOPIC' />\n" +
            "<input type='hidden' name='page_id' value='" + page_id + "' />\n" +
            "<input type='hidden' name='reply_to_comment_id' value='" + this_comment_id + "' />\n" +
            "<label for='comment_type'>Comment Type</label> " + wikiCommentType.getSelect("comment_type") + "<br>\n" +
            "<script type='text/javascript'>\n" +
            "var sBasePath = 'fckeditor/';\n" +
            "var oFCKeditor = new FCKeditor( 'FCKeditor1' ) ;\n" +
            "oFCKeditor.BasePath	= sBasePath ;\n" +
            "oFCKeditor.Height	= 300 ;\n" +
            "oFCKeditor.Value	= '' ;\n" +
            "oFCKeditor.Create() ;\n" +
            "</script>\n" +
            "<br />\n" +
            "<input type='submit' name='commentAction' value='Save Comment' />\n"
            + "</td>";
        }
        else
        {
          // they haven't asked to reply to this topic, but perhaps they might like to...
          html = html + "<td>\n<input type='hidden' name='reply_to' value='" + this_comment_id 
            + "'><input type='submit' name='commentAction' value='Reply'></td>";
        }
        html = html + "</tr>";
        html = html + "\n</form>";
      }
      html = html + "</table>";
      
      rs.close();
      conn.close();
    }
    catch (Exception e)
    {
      // to do
      html = "Error in Comment.showComments(): " + e.getMessage() + "<br>SQL: " + SQL;
    }
    
    return html;
  }
  
  public String saveComment(String page_id, String comment_type, String comment_title, String comment_text, String reply_to, String user_id)
  {
    String result = "Comment not saved.";
    String SQL = "";
    if( comment_text == null ) return "No text specified";
    try
    {
      Database db = new Database();
      
      dbUser = db.getDbUser();
      dbPassword = db.getDbPassword();
      dbUrl = db.getDbUrl();
  
      OracleDataSource ods = new OracleDataSource();
      ods.setURL(dbUrl);
      ods.setUser(dbUser);
      ods.setPassword(dbPassword);
      
      String v_page_id = "";
      String v_type = "COMMENT";
      String v_title = "no title";
      String v_text = "no comment";
      String v_reply_to = "null";
      String v_user_id = "guest";
      
      if( page_id != null ) v_page_id = page_id.replaceAll("'", "&#39;");
      else return "Error: no page_id specified.";
      if( comment_type != null ) v_type = comment_type.replaceAll("'", "&#39;");
      if( comment_title != null ) v_title = comment_title.replaceAll("'", "&#39;");
      if( comment_text != null ) v_text = comment_text.replaceAll("'", "&#39;");
      v_text = v_text.replaceAll("\n\r", "");

      if( reply_to != null ) v_reply_to = reply_to.replaceAll("'", "&#39;");
      if( v_reply_to.length() == 0 ) v_reply_to = "null";
      
      if( user_id != null ) v_user_id = user_id.replaceAll("'", "&#39;");
      if( user_id.length() == 0 ) user_id = "guest";
      
      Connection conn = ods.getConnection();
      Statement state = conn.createStatement();

      SQL = "insert into wiki_comment (page_id, comment_type_code, comment_title, comment_text, reply_to_comment_id, created_by) "
        + "values (" + v_page_id + ", '" + v_type + "', '" + v_title + "', '" + v_text + "', " + v_reply_to + ", '" + v_user_id + "' )";

      if( state.executeUpdate(SQL) > 0 )
      {
        result = "Comment saved.";
      }

      conn.close();
    }
    catch (Exception e)
    {
      result = "Error in Comment.saveComment(): " + e.getMessage() + "<br>SQL: " + SQL;
    }

    return result;
  }

}