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 Text 
{
  private Database db = new Database();
  private OracleDataSource ods = null; 

  public Text()
  {
  }
  
  public String edit(String section_id)
  {
    String SQL = "";
    String text_id = "";
    String text = "";
    if( section_id == null ) return "No text specified";
    try
    {
      OracleDataSource ods = new OracleDataSource();
      ods.setURL( db.getDbUrl() );
      ods.setUser( db.getDbUser() );
      ods.setPassword( db.getDbPassword() );

      SQL = "select t.text from wiki_text t "
          + "where t.section_id = " + section_id
          + "  and t.active_flag = 'Y'";
      Connection conn = ods.getConnection();
      Statement state = conn.createStatement();
      ResultSet rs = state.executeQuery(SQL);
      while( rs.next() )
      {
        text = rs.getString("text");
      }
      
      rs.close();
      conn.close();
    }
    catch (Exception e)
    {
      // to do
      text = "Error in Text.edit(): " + e.getMessage() + "<br>SQL: " + SQL;
      text_id = "0";
    }
    
    text = text.replaceAll("\n", "");
    text = text.replaceAll("\r", "");

    //if( text_id == null ) return "no text specified";
    String html = "<form method='post'>\n" +
      "<input type='hidden' name='section_id' value='" + section_id + "' />\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	= '" + text + "' ;\n" +
      "oFCKeditor.Create() ;\n" +

		"</script>\n" +
		"<br />\n" +
		"<input type='submit' name='editText' value='Save' />\n" +
	  "</form>\n";
    
    return html;
  }
  
  public String update(String section_id, String text, String updater)
  {
    String result = "Updates not saved.";
    String SQL = "";
    if( section_id == null ) return "No section_id specified";
    if( text == null ) return "No text specified";
    try
    {
      OracleDataSource ods = new OracleDataSource();
      ods.setURL( db.getDbUrl() );
      ods.setUser( db.getDbUser() );
      ods.setPassword( db.getDbPassword() );
      
      String v_section_id = "";
      String v_text = "empty";
      String v_updater = "guest";
      
      if( section_id != null ) v_section_id = section_id.replaceAll("'", "&#39;");
      else return "Error: no section_id specified.";
      if( text != null ) v_text = text.replaceAll("'", "&#39;");
      v_text = v_text.replaceAll("\n\r", "");
      if( updater != null ) v_updater = updater.replaceAll("'", "&#39;");
      
      boolean unModerated = this.isUnmoderated(section_id, v_updater);
      String active_flag = "Q"; // new text defaults to needing moderation Q for query, question, etc.
      
      Connection conn = ods.getConnection();
      Statement state = conn.createStatement();

      if( unModerated ) 
      {
        // if this is an unmoderated page or the user is the page owner then
        // set the currently active text to 'N'
        SQL = "update wiki_text set active_flag = 'N' where section_id = " + v_section_id
          + " and active_flag = 'Y'";

        state.execute(SQL);

        // create a new statement for the next instruction
        state = conn.createStatement();
        
        // the new text is automatically active
        active_flag = "Y"; 
      }
      
      SQL = "insert into wiki_text (section_id, text, updated_by, active_flag) "
        + "values (" + v_section_id + ", '" + v_text + "', '" + v_updater + "', '" + active_flag + "' )";
      

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

      conn.close();
    }
    catch (Exception e)
    {
      result = "Error in Text.update(): " + e.getMessage() + "<br>SQL: " + SQL;
    }
    return result;
  }
  
  public String authorise(String text_id, String authoriser)
  {
    return "authorise";
  }
  
  public String parseText(String text)
  {
    String t = "";
    if( text != null )
    {
      if( text.length() > 0 )
      {
        if( text.indexOf("[") >= 0 )
        {
          int start = 0;
          int end = text.indexOf("[");
          boolean plainText = true;
          while ( end >= 0 && start < text.length()+1 )
          {
            if( end != start ) 
            {
              // either the text doesn't start with a [ or there isn't one immediately after a ]
              // add in the plain text at the beginning or between links
              t = t + text.substring(start, end);
            }
            start = end + 1; 
            end = text.indexOf("]", start);
            if( end >= start ) t = t + parseLink( text.substring(start, end) );
            else 
            {
              // no matching closing bracket
              t = t + text.substring(start);
              return t;
            }
            // look for another link
            start = end + 1;
            end = text.indexOf("[", start);
            if( end < 0 )
            {
              // no more links - just add in the rest of the plain text
              t = t + text.substring(start);
              return t;
            }
          }
        }
        else
        {
          t = text;
        }
      }
    }
    return t;
  }
  
  public String parseLink(String link)
  {
    if( link == null ) return "";
    link = link.trim();
    if( link.length() < 3 ) return link;
    return "<a href='default.jsp?page=" + link + "'>" + link + "</a>";
  }
  
  public String setActive(String text_id, String updater)
  {
    String result = "Updates not saved.";
    if( updater == null ) updater = "guest";
    String SQL = "";
    if( text_id == null ) return "Error: text_id not specified.";
    try
    {
      OracleDataSource ods = new OracleDataSource();
      ods.setURL( db.getDbUrl() );
      ods.setUser( db.getDbUser() );
      ods.setPassword( db.getDbPassword() );
      
      String v_text_id = "";
      String v_updater = "guest";
      
      v_text_id = text_id.replaceAll("'", "&#39;");
      v_updater = updater.replaceAll("'", "&#39;");
      
      SQL = "update wiki_text set active_flag = 'N' "
        + "where section_id in (select t2.section_id from wiki_text t2 where t2.text_id = " + v_text_id + ")";
      
      Connection conn = ods.getConnection();
      Statement state = conn.createStatement();
      state.execute(SQL);

      SQL = "update wiki_text set active_flag = 'Y', "
        + " approved_by = '" + v_updater + "', " 
        + " approved_on = SYSDATE "
        + "where text_id = " + v_text_id;
      
      state = conn.createStatement();

      if( state.executeUpdate(SQL) > 0 )
      {
        result = "Text activated.";
      }

      conn.close();
    }
    catch (Exception e)
    {
      result = "Error in Text.setActive(): " + e.getMessage() + "<br>SQL: " + SQL;
    }
    return result;
  }
  
  public boolean isUnmoderated(String section_id, String user_id)
  {
    String SQL = "";
    boolean allow_edits = true;
    if( section_id == null || user_id == null ) return false;
    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, "
          + "  p.page_owner, p.moderation "
          + "from wiki_page p, wiki_section s "
          + "where s.section_id = '" + section_id + "' "
          + "  and s.page_id = p.page_id ";

      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;
      }
      rs.close();
      conn.close();
    }
    catch (Exception e)
    {
      // to do
      return false;
    }
    return allow_edits;
  }
}