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 Page 
{
  private String page_name = "";
  private String page_html = "";
  
  private Database db = new Database();
  private OracleDataSource ods = null; 
  
  public Page()
  {
  }
  
  public String viewPage(String page_name, String user_id, String search_for)
  {
    String SQL = "";
    Text wikiText = new Text();
    page_html = "";
    if( search_for == null ) search_for = "";
    
    try
    {
      OracleDataSource ods = new OracleDataSource();
      ods.setURL( db.getDbUrl() );
      ods.setUser( db.getDbUser() );
      ods.setPassword( db.getDbPassword() );

      Connection conn = ods.getConnection();
      Statement state = conn.createStatement();
      
      // check to see that this page exists
      boolean page_found = false;
      SQL = "select page_id from wiki_page where page_name = '" + page_name + "' ";
      ResultSet rs = state.executeQuery(SQL);
      while( rs.next() )
      {
        page_found = true;
      }
      rs.close();
      if( !page_found )
      {
        page_html = "Sorry, but the page you are looking for does not exist. "
          + "Please return to the <a href='default.jsp?page=Welcome'>Welcome</a> page.";
        return page_html;
      }

      state = conn.createStatement();
      SQL = "select t.text, s.section_name, s.section_id, s.active_flag section_active, "
          + "  p.page_owner, p.moderation "
          + "from wiki_page p, wiki_section s, wiki_text t "
          + "where p.page_name = '" + page_name + "' "
          + "  and s.page_id = p.page_id "
          + "  and t.section_id = s.section_id "
          + "  and p.active_flag = 'Y' "
          + "  and s.active_flag = 'Y' "
          + "  and t.active_flag = 'Y' "
          + "order by s.position ";
      rs = state.executeQuery(SQL);
      while( rs.next() )
      {
        boolean allow_edits = false;

        String owner = rs.getString( "page_owner" );
        String moderation = rs.getString( "moderation" );
        
        if( owner == null ) owner = "Z";
        if( user_id == null ) user_id = "X";
        
        if( moderation == null ) moderation = "N";
        
        if( owner.toUpperCase().equals( user_id.toUpperCase() ) )
        {
          allow_edits = true;
        }
        else
        {
          if( moderation.startsWith("N") ) allow_edits = true;
          else allow_edits = false;
        }
      
        if( allow_edits ) page_html = page_html + "<span class='wiki_edit'><a href='edit.jsp?section_id=" + rs.getString("section_id") + "'>edit</a></span>";
        page_html = page_html + "<h2 class='wiki'>" + highlightSearchTerms(rs.getString("section_name"), search_for) + "</h2>";
        page_html = page_html + "<div class='wiki'>" + highlightSearchTerms(wikiText.parseText( rs.getString("text") ), search_for) + "</div>";
      }
      
      rs.close();
      conn.close();
    }
    catch (Exception e)
    {
      // to do
      page_html = "Error in Form.getFormName(): " + e.getMessage() + "<br>SQL: " + SQL;
    }
    
    return page_html;
  }
  
  public String historyPage(String page_name, String user_id)
  {
    String SQL = "";
    Section wikiSection = new Section();
    page_html = "";
    try
    {
      OracleDataSource ods = new OracleDataSource();
      ods.setURL( db.getDbUrl() );
      ods.setUser( db.getDbUser() );
      ods.setPassword( db.getDbPassword() );

      SQL = "select s.section_name, s.section_id, s.active_flag section_active, "
          + "  p.page_owner, p.moderation "
          + "from wiki_page p, wiki_section s "
          + "where p.page_name = '" + page_name + "' "
          + "  and s.page_id = p.page_id "
          + "  and p.active_flag = 'Y' "
          + "  and s.active_flag = 'Y' "
          + "order by s.position ";
      Connection conn = ods.getConnection();
      Statement state = conn.createStatement();
      ResultSet rs = state.executeQuery(SQL);
      while( rs.next() )
      {
        boolean allow_edits = false;

        String owner = rs.getString( "page_owner" );
        String moderation = rs.getString( "moderation" );
        
        if( owner == null ) owner = "Z";
        if( user_id == null ) user_id = "X";
        
        if( moderation == null ) moderation = "N";
        
        if( owner.toUpperCase().equals( user_id.toUpperCase() ) )
        {
          allow_edits = true;
        }
        else
        {
          if( moderation.startsWith("N") ) allow_edits = true;
          else allow_edits = false;
        }
      
        if( allow_edits ) page_html = page_html + "<span class='wiki_edit'><a href='edit.jsp?section_id=" + rs.getString("section_id") + "'>edit</a></span>";
        page_html = page_html + "<h2 class='wiki'>" + rs.getString("section_name") + "</h2>";
        page_html = page_html + wikiSection.showHistory( rs.getString("section_id"), user_id );
      }
      
      rs.close();
      conn.close();
    }
    catch (Exception e)
    {
      // to do
      page_html = "Error in Page.historyPage(): " + e.getMessage() + "<br>SQL: " + SQL;
    }
    
    return page_html;
  }
  
  public String getPageInfo(String page_name, String user_id)
  {
    String SQL = "";
    page_html = "";
    try
    {
      OracleDataSource ods = new OracleDataSource();
      ods.setURL( db.getDbUrl() );
      ods.setUser( db.getDbUser() );
      ods.setPassword( db.getDbPassword() );

      SQL = "select page_owner, moderation "
          + "from wiki_page "
          + "where page_name = '" + page_name + "' ";
      Connection conn = ods.getConnection();
      Statement state = conn.createStatement();
      ResultSet rs = state.executeQuery(SQL);

      String owner = "";
      String moderation = "";
      
      while( rs.next() )
      {
        owner = rs.getString("page_owner");
        moderation = rs.getString("moderation");
      }
      
      rs.close();
      conn.close();

      if( user_id != null && owner != null && user_id.toUpperCase().equals( owner.toUpperCase() ) )
      {
        // this is the owner's page
        // allow them to transfer ownership
        page_html = page_html + "<form method='post'>";
        User user = new User();
        page_html = page_html + user.getSelect("owner", user_id);
        page_html = page_html + "<input type='submit' name='editAction' value='Transfer Ownership'>\n";
        page_html = page_html + "<select name='moderation'>";

        if( moderation.startsWith("N") ) // fully public
          page_html = page_html + "<option value='N' selected>All edits instantly accepted</option>";
        else
          page_html = page_html + "<option value='N'>All edits instantly accepted</option>";

        if( moderation.startsWith("P") ) // fully public
          page_html = page_html + "<option value='P' selected>Edits need approval by page owner</option>";
        else
          page_html = page_html + "<option value='P'>Edits need approval by page owner</option>";

        if( moderation.startsWith("F") ) // fully public
          page_html = page_html + "<option value='F' selected>Only page owner can edit</option>";
        else
          page_html = page_html + "<option value='F'>Only page owner can edit</option>";

        page_html = page_html + "</select>";
        page_html = page_html + "<input type='submit' name='editAction' value='Change Moderation'>\n";


      page_html = page_html + "<br>\n"
        + "<input type='hidden' name='page' value='" + page_name + "'>\n"
        + "<input type='text' name='new_page_name' value='" + page_name + "'>\n"
        + "<input type='submit' name='editAction' value='Rename Page'>\n"
        + "</form>";


      }
      else
      {
        if( owner == null ) owner = "Page not owned";
        page_html = page_html + "<p>Page Owner: " + owner + ".";
        if( moderation.startsWith("N") ) // fully public
          page_html = page_html + " Moderation: All edits instantly accepted.";
        if( moderation.startsWith("P") ) // fully public
          page_html = page_html + " Moderation: Edits need approval by page owner.";
        if( moderation.startsWith("F") ) // fully public
          page_html = page_html + " Moderation: Only page owner can edit this page.";
        page_html = page_html + "</p>";
      }
    }
    catch (Exception e)
    {
      // to do
      page_html = "Error in Page.getPageInfo(): " + e.getMessage() + "<br>SQL: " + SQL;
    }

    return page_html;
  }

  public String editPage(String page_name, String user_id)
  {
    String SQL = "";
    page_html = "";
    boolean allow_edits = true;
    try
    {
      OracleDataSource ods = new OracleDataSource();
      ods.setURL( db.getDbUrl() );
      ods.setUser( db.getDbUser() );
      ods.setPassword( db.getDbPassword() );

      SQL = "select t.text_id, s.section_name, t.text, s.section_id, s.active_flag section_active, "
          + "  p.page_owner, p.moderation "
          + "from wiki_page p, wiki_section s, wiki_text t "
          + "where p.page_name = '" + page_name + "' "
          + "  and s.page_id = p.page_id "
          + "  and t.section_id = s.section_id "
          + "  and p.active_flag = 'Y' "
          + "  and t.active_flag = 'Y' "
          + "order by s.position ";
      Connection conn = ods.getConnection();
      Statement state = conn.createStatement();
      ResultSet rs = state.executeQuery(SQL);
      
      while( rs.next() )
      {
        allow_edits = false;
        String owner = rs.getString( "page_owner" );
        String moderation = rs.getString( "moderation" );
        
        if( owner == null ) owner = "Z";
        if( user_id == null ) user_id = "X";
        
        if( moderation == null ) moderation = "N";
        
        if( owner.toUpperCase().equals( user_id.toUpperCase() ) )
        {
          allow_edits = true;
        }
        else
        {
          allow_edits = false;
        }
        if( moderation.startsWith("N") ) allow_edits = true;
        if( moderation.startsWith("P") ) allow_edits = true;
      
        if( allow_edits )
        {
          page_html = page_html + "<div class='wiki_edit'>\n";
          page_html = page_html + "<form method='post'>\n"
            + "<input type='hidden' name='page' value='" + page_name + "'>\n"
            + "<input type='hidden' name='section_id' value='" + rs.getString("section_id") + "'>\n"
            + "<input type='text' name='new_section_name' value='" + rs.getString("section_name") + "'>\n"
            + "<input type='submit' name='editAction' value='Rename Section'>\n"
            + "<input type='submit' name='editAction' value='Insert Section Above'>\n"
            + "<input type='submit' name='editAction' value='Move Up'>\n"
            + "<input type='submit' name='editAction' value='Move Down'>\n";
          if( rs.getString("section_active").startsWith("Y") )
            page_html = page_html + "<input type='submit' name='editAction' value='Delete Section'>\n";
          else
            page_html = page_html + "<input type='submit' name='editAction' value='Undelete Section'>\n";
  
          page_html = page_html + "</form>";
          page_html = page_html + "<span class='wiki_edit'><a href='edit.jsp?section_id=" + rs.getString("section_id") + "'>edit</a></span>";
        }
        
        page_html = page_html + "<h2 class='wiki'>" + rs.getString("section_name");
        if( rs.getString("section_active").startsWith("N") )
          page_html = page_html + "<em> - deleted</em>";
        page_html = page_html + "</h2>\n";
        page_html = page_html + "<div class='wiki'>" + rs.getString("text") + "</div>\n";
        if( allow_edits ) page_html = page_html + "</div>\n";
      }
      
      rs.close();
      conn.close();
    }
    catch (Exception e)
    {
      // to do
      page_html = "Error in Page.editPage(): " + e.getMessage() + "<br>SQL: " + SQL;
    }

    if( allow_edits ) 
    {
      page_html = page_html + "<form method='post'>\n"
        + "<input type='hidden' name='page' value='" + page_name + "'>\n"
        + "<input type='text' name='new_section_name' value=''>\n"
        + "<input type='submit' name='editAction' value='Add New Section'>\n"
        + "<input type='text' name='new_page_name' value=''>\n"
        + "<input type='submit' name='editAction' value='Add Page'>\n"
        + "</form>";
    }
    
    return page_html;
  }
  
  public String discussionPage(String page_name, String user_id, String reply_to )
  {
    String SQL = "";
    Comment wikiComment = new Comment();
    page_html = "";
    if( reply_to == null ) reply_to = "";
    try
    {
      OracleDataSource ods = new OracleDataSource();
      ods.setURL( db.getDbUrl() );
      ods.setUser( db.getDbUser() );
      ods.setPassword( db.getDbPassword() );

      Connection conn = ods.getConnection();
      Statement state = conn.createStatement();

      String page_id = "";
      SQL = "select p.page_id "
          + "from wiki_page p "
          + "where p.page_name = '" + page_name + "' ";
      ResultSet rs = state.executeQuery(SQL);

      while( rs.next() )
      {
        page_id = rs.getString("page_id");
      }

      state = conn.createStatement();
      SQL = "select topic.comment_title, topic.comment_id "
          + "from wiki_page p, wiki_comment topic "
          + "where p.page_name = '" + page_name + "' "
          + "  and topic.page_id = p.page_id "
          + "  and p.active_flag = 'Y' "
          + "  and topic.active_flag = 'Y' "
          + "  and topic.comment_type_code = 'TOPIC' "
          + "order by topic.comment_id ";
      rs = state.executeQuery(SQL);

      while( rs.next() )
      {
        page_html = page_html + "<h2 class='wiki'>" + rs.getString("comment_title") + "</h2>";
        page_html = page_html + wikiComment.showComments( rs.getString("comment_id"), user_id, reply_to, page_id );
      }
      if( reply_to.equals("0") )
      {
        // show the edit box
        page_html = page_html + 
          "<form method='post'>\n" +
          "<input type='hidden' name='page_id' value='" + page_id + "' />\n" +
          "<input type='hidden' name='comment_type_code' value='TOPIC' />\n" +
          "<input type='hidden' name='reply_to_comment_id' value='NULL' />\n" +
          "<label for='comment_title'>Title:</label> <input type='text' name='comment_title' size='40' /><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 Topic' />\n" +
          "</form>\n";
      }
      else
      {
        // all the user to add a new page
        page_html = page_html + "\n<form method='post'>"
          + "\n<input type='hidden' name='page_id' value='" + page_id + "'>"
          + "\n<input type='hidden' name='reply_to' value='0'>"
          + "\n<input type='submit' name='commentAction' value='Add New Topic'>"
          + "\n</form>";
      }
      
      rs.close();
      conn.close();
    }
    catch (Exception e)
    {
      // to do
      page_html = "Error in Page.historyPage(): " + e.getMessage() + "<br>SQL: " + SQL;
    }
    
    return page_html;
  }
  
  public String addPage(String new_page_name, String user_id)
  {
    String result = "";
    String SQL = "";
    String owner = user_id;
    if( owner == null ) owner = "guest";
    try
    {
      OracleDataSource ods = new OracleDataSource();
      ods.setURL( db.getDbUrl() );
      ods.setUser( db.getDbUser() );
      ods.setPassword( db.getDbPassword() );
      
      String v_page_name = new_page_name;
      if( v_page_name == null || v_page_name.length() == 0) v_page_name = "new page";
            
      Connection conn = ods.getConnection();
      Statement state = conn.createStatement();
      
      SQL = "select page_id from wiki_page where page_name = '" + v_page_name + "'";
      ResultSet rs = state.executeQuery(SQL);
      
      while( rs.next() )
      {
        // if we got here then there must already be a page with this name;
        rs.close();
        conn.close();
        return "Sorry, but a page of this name already exists: "
          + "<a href='default.jsp?page=" + v_page_name + "'>" + v_page_name + "</a>";
      }
      
      
      state = conn.createStatement();

      SQL = "insert into wiki_page (page_name, page_owner) "
        + "values ( '" + v_page_name + "', '" + owner + "' )";
      
      if( state.executeUpdate(SQL) > 0 )
      {
        result = "Page added: <a href='default.jsp?page=" + v_page_name + "'>" + v_page_name + "</a>";
      }

      conn.close();
    }
    catch (Exception e)
    {
      result = "Error in Page.addPage(): " + e.getMessage() + "<br>SQL: " + SQL;
    }
    return result;
  }
  
  public String renamePage(String page_name, String new_page_name)
  {
    String result = "";
    String SQL = "";
    if( page_name == null ) return "Error: original page name not specified.";
    try
    {
      OracleDataSource ods = new OracleDataSource();
      ods.setURL( db.getDbUrl() );
      ods.setUser( db.getDbUser() );
      ods.setPassword( db.getDbPassword() );
      
      String v_page_name = new_page_name;
      if( v_page_name == null ) v_page_name = "new page";
            
      Connection conn = ods.getConnection();
      Statement state = conn.createStatement();

      SQL = "update wiki_page set page_name = '" + v_page_name + "' "
        + " where page_name = '" + v_page_name + "' ";
      
      if( state.executeUpdate(SQL) > 0 )
      {
        result = "Page renamed.";
      }

      conn.close();
    }
    catch (Exception e)
    {
      result = "Error in Page.renamePage(): " + e.getMessage() + "<br>SQL: " + SQL;
    }
    return result;
  }
  
  public String getName(String page_name, String section_id, String text_id)
  {
    String SQL = "";
    String name = "";
    if( page_name != null ) return page_name;
    if( section_id != null )
    {
      SQL = "select p.page_name from wiki_section s, wiki_page p "
          + "where s.section_id = " + section_id
          + "  and s.page_id = p.page_id ";
    }
    else
    {
      if( text_id == null ) return "Welcome";

      SQL = "select p.page_name from wiki_text t, wiki_section s, wiki_page p "
          + "where t.text_id = " + text_id
          + "  and t.section_id = s.section_id "
          + "  and s.page_id = p.page_id ";
    }
    try
    {
      OracleDataSource ods = new OracleDataSource();
      ods.setURL( db.getDbUrl() );
      ods.setUser( db.getDbUser() );
      ods.setPassword( db.getDbPassword() );

      Connection conn = ods.getConnection();
      Statement state = conn.createStatement();
      ResultSet rs = state.executeQuery(SQL);
      while( rs.next() )
      {
        name = rs.getString("page_name");
      }
      
      rs.close();
      conn.close();
    }
    catch (Exception e)
    {
      // to do
      name = "Error in Page.getName( " + page_name + ", " + text_id + " ): " + e.getMessage() + "<br>SQL: " + SQL;
    }
    
    return name;
  }
  
  public String setPageOwner(String page_name, String owner)
  {
    String result = "";
    String SQL = "";
    if( page_name == null ) return "Error: page name not specified.";
    if( owner == null ) return "Error: owner not specified.";
    try
    {
      OracleDataSource ods = new OracleDataSource();
      ods.setURL( db.getDbUrl() );
      ods.setUser( db.getDbUser() );
      ods.setPassword( db.getDbPassword() );
      
      String v_owner = owner;
            
      Connection conn = ods.getConnection();
      Statement state = conn.createStatement();

      SQL = "update wiki_page set page_owner = '" + v_owner + "' "
        + " where page_name = '" + page_name + "' ";
      
      if( state.executeUpdate(SQL) > 0 )
      {
        result = "Page owner changed.";
      }

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

  public String setModeration(String page_name, String moderation)
  {
    String result = "";
    String SQL = "";
    if( page_name == null ) return "Error: page name not specified.";
    if( moderation == null ) return "Error: moderation not specified.";
    try
    {
      OracleDataSource ods = new OracleDataSource();
      ods.setURL( db.getDbUrl() );
      ods.setUser( db.getDbUser() );
      ods.setPassword( db.getDbPassword() );
      
      String v_moderation = moderation;
      if( v_moderation.length() == 0 ) v_moderation = "Error: moderation not specified.";
      if( v_moderation.length() > 1 ) v_moderation = v_moderation.substring(0,1);
            
      Connection conn = ods.getConnection();
      Statement state = conn.createStatement();

      SQL = "update wiki_page set moderation = '" + v_moderation + "' "
        + " where page_name = '" + page_name + "' ";
      
      if( state.executeUpdate(SQL) > 0 )
      {
        result = "Page moderation changed.";
      }

      conn.close();
    }
    catch (Exception e)
    {
      result = "Error in Page.setModeration(): " + e.getMessage() + "<br>SQL: " + SQL;
    }
    return result;
  }
  
  public String searchResults(String search_for, String user_id, String search_page_title, 
    String search_section_title, String search_in_text, String search_page, String search_id)
  {
    String SQL = "";
    String terms[] = null;
    String result = "";
    if( search_for == null || search_for.length() < 2 )
    {
      result = "Enter a search term and click search.";
    }
    else
    {
      try
      {
        if( search_id == null || search_id.length() == 0 )
        {
          //result = "You searched for " + search_for;
          OracleDataSource ods = new OracleDataSource();
          ods.setURL( db.getDbUrl() );
          ods.setUser( db.getDbUser() );
          ods.setPassword( db.getDbPassword() );
  
          Connection conn = ods.getConnection();
          Statement state = conn.createStatement();
          
          // clear out old search results
          SQL = "delete from wiki_search_results where search_id in "
            + "( select search_id from wiki_search where created_by = '" + user_id +"' )";
  
          state.executeUpdate(SQL);
          
          state = conn.createStatement();
          SQL = "insert into wiki_search (SEARCH_FOR, CREATED_BY) "
            + "values ( '" + search_for + "', '" + user_id +"' )";
  
          state.executeUpdate(SQL);
          search_for = search_for.trim().toUpperCase();

          
          state = conn.createStatement();
          SQL = "select max(search_id) search_id from wiki_search "
            + "where created_by = '" + user_id +"' ";
          
          search_id = "";
          
          ResultSet rs = state.executeQuery(SQL);
          while( rs.next() )
          {
            search_id = rs.getString("search_id");
          }
          //result = result + " search_id = " + search_id;
          
          if( search_page_title != null && search_page_title.startsWith("Y") )
          {
            state = conn.createStatement();
            SQL = "insert into wiki_search_results (search_id, page_id, score) "
              + "select " + search_id + " search_id, page_id, 100 score "
              + "from wiki_page "
              + "where upper(page_name) like '%" + search_for + "%' ";
            state.executeUpdate(SQL);
          }

          if( search_section_title != null && search_section_title.startsWith("Y") )
          {
            state = conn.createStatement();
            SQL = "insert into wiki_search_results (search_id, page_id, score) "
              + "select " + search_id + " search_id, p.page_id, 30 score "
              + "from wiki_page p, wiki_section s "
              + "where upper(s.section_name) like '%" + search_for + "%' "
              + "  and s.active_flag = 'Y' "
              + "  and s.page_id = p.page_id ";
            state.executeUpdate(SQL);
          }
    
          if( search_in_text != null && search_in_text.startsWith("Y") )
          {
            state = conn.createStatement();
            SQL = "insert into wiki_search_results (search_id, page_id, score) "
              + "select " + search_id + " search_id, p.page_id, 10 score "
              + "from wiki_page p, wiki_section s, wiki_text t "
              + "where upper(t.text) like '%" + search_for + "%' "
              + "  and t.section_id = s.section_id "
              + "  and t.active_flag = 'Y' "
              + "  and s.active_flag = 'Y' "
              + "  and s.page_id = p.page_id ";
            state.executeUpdate(SQL);
          }

          // read what we found from the WIKI_SEARCH_RESULTS table
          state = conn.createStatement();

          SQL = "select p.page_name, results.score, nvl(page_text.title, 'empty page') title, nvl(page_text.text, 'no text') text "
            + "from wiki_page p, "
            + " (select page_id, sum(score) score from wiki_search_results "
            + "  where search_id = " + search_id + " group by page_id ) results, "
            + " (select s.section_name title, t.text, s.page_id from wiki_section s, wiki_text t "
            + "    where t.section_id = s.section_id "
            + "    and s.active_flag = 'Y' "
            + "    and t.active_flag = 'Y' "
            + "    and s.position in (select min(position) from wiki_section s2 where s2.page_id = s.page_id ) "
            + "  ) page_text "
            + "where p.page_id = results.page_id "
            + "  and p.page_id = page_text.page_id (+) "
            + "order by score desc";
    
          rs = state.executeQuery(SQL);
          int rowcount = 0;
          result = result + "<h2>Search Results</h2>";
          while( rs.next() )
          {
            String full_text = rs.getString("text");
            if( full_text == null ) full_text = "";
            full_text = full_text.replaceAll("\\<.*?>",""); // strip out the html tags
            String t = new String(full_text);
            if( t.length() > 100 ) t = t.substring(0,97) + "...";
            rowcount++;
            result = result + "<div class='wiki_search_result'>" + rowcount 
              + ". <a href='default.jsp?page=" + rs.getString("page_name") 
              + "&amp;search_for=" + search_for + "'>" 
              + rs.getString("page_name") + "</a> <small><em>Score: "+ rs.getString("score") + "</em></small><br>"
              + "<small><strong>" + highlightSearchTerms( rs.getString("title"), search_for)  + "</strong> " 
              + highlightSearchTerms( t, search_for) + "</small></div>"; 
          }
          
          if( rowcount == 0 )
          {
            result = result + "No matches found<br>";
          }

          conn.close();
        }
      }
      catch (Exception e)
      {
        result = "Error in Page.searchResults(): " + e.getMessage() + "<br>SQL: " + SQL;
      }
    }
    return result;
  }
  
  private String highlightSearchTerms(String text, String search_for)
  {
    if( text == null ) return null;
    if( search_for == null ) return text;
    if( search_for.length() < 2 ) return text;
    String result = "";
    String upper = text.toUpperCase();
    int strlen = search_for.length();
    
    int foundPosition = upper.indexOf( search_for );
    int oldPosition = 0;
    if( foundPosition == -1 ) return text;
    while( foundPosition != -1 ) 
    { 
      result = result + text.substring(oldPosition, foundPosition) 
        + "<span class='wiki_highlight'>" + text.substring(foundPosition, foundPosition + strlen) + "</span>";
      oldPosition = foundPosition + strlen;
      foundPosition = text.toString().indexOf( search_for );
    } 
    result = result + text.substring(oldPosition);
    return result; 
  }
}