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

  public Section()
  {
  }
  
  public String showHistory(String section_id, String user_id)
  {
    String SQL = "";
    String html = "";
    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();
      
      boolean allow_edits = false;
      SQL = "select p.page_owner, p.moderation "
          + "from wiki_section s, wiki_page p "
          + "where s.section_id = " + section_id 
          + "  and s.page_id = p.page_id ";

      ResultSet rs = state.executeQuery(SQL);
      while( rs.next() )
      {
        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;
        }
      }
      rs.close();

      html = "<table>\n <tr><th>Current</th><th>Text</th><th>Date</th><th>Author</th>";
      if( allow_edits ) html = html + "<th>Action</th>";
      html = html + "</tr>\n";

      state = conn.createStatement();
      SQL = "select t.text_id, t.text, t.updated_by, t.updated_on, t.active_flag "
          + "from wiki_text t  "
          + "where t.section_id = " + section_id 
          + "order by t.text_id desc ";
      rs = state.executeQuery(SQL);
      
      int rownum = 0;
      while( rs.next() && rownum < 10 )
      {
        rownum++;
        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() > 50 ) t = t.substring(0,47) + "...";
        if( allow_edits ) 
        {
          html = html + "\n<form method='post'>"
            + "\n<input type='hidden' name='text_id' value='" + rs.getString("text_id") + "'>";
        }
        html = html + "<tr><td>" + rs.getString("active_flag") + "</td><td title='" + full_text + "'>" + t + 
          "</td><td>" + rs.getString("updated_on") + "</td><td>" + rs.getString("updated_by") + "</td>";
        if( allow_edits ) 
        {
          html = html + "<td>\n<input type='submit' name='historyAction' value='Set to Current'></td>";
        }
        html = html + "</tr>";
        if( allow_edits ) 
        {
          html = html + "\n</form>";
        }
      }
      html = html + "</table>";
      
      rs.close();
      conn.close();
    }
    catch (Exception e)
    {
      // to do
      html = "Error in Section.showHistory(): " + e.getMessage() + "<br>SQL: " + SQL;
    }
    
    return html;
  }

  public String addSection(String page_name, String section_name)
  {
    String result = "";
    String SQL = "";
    if( page_name == null || page_name.length() < 1 ) return "Error: no page_name specified.";

    try
    {
      OracleDataSource ods = new OracleDataSource();
      ods.setURL( db.getDbUrl() );
      ods.setUser( db.getDbUser() );
      ods.setPassword( db.getDbPassword() );
      
      String v_section_name = section_name;
      if( v_section_name == null ) v_section_name = "new section";
            
      Connection conn = ods.getConnection();
      Statement state = conn.createStatement();
      
      String page_id = "";
      SQL = "select page_id from wiki_page where page_name = '" + page_name + "'";
      
      ResultSet rs = state.executeQuery(SQL);
      while( rs.next() )
      {
        page_id = rs.getString("page_id");
      }
      rs.close();

      if( page_id == null || page_id.length() < 1 ) return "Error: page_name not found.";

      SQL = "insert into wiki_section (page_id, section_name) "
        + "values ( " + page_id + ", '" + v_section_name + "' )";
      
      state = conn.createStatement();

      if( state.executeUpdate(SQL) > 0 )
      {
        result = "Section added.";
        
        addSectionText(page_id);
      }

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

  public String insertSection(String section_name, String before_section_id)
  {
    String SQL = "";
    String result = "";
    if( before_section_id == null || before_section_id.length() < 1 ) return "Error: no before_section_id specified.";

    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();
      
      SQL = "select page_id, position from wiki_section where section_id = " + before_section_id;
      
      String page_id = "";
      String position = "";
      
      ResultSet rs = state.executeQuery(SQL);
      while( rs.next() )
      {
        page_id = rs.getString("page_id");
        position = rs.getString("position");
      }
      rs.close();

      SQL = "update wiki_section set position = position + 1 "
          + "where page_id = " + page_id
          + "  and position >= " + position;
      state = conn.createStatement();

      state.executeUpdate(SQL);
      
      String v_section_name = section_name;
      if( v_section_name == null ) v_section_name = "new section";

      SQL = "insert into wiki_section (page_id, section_name, position) "
        + "values ( " + page_id + ", '" + v_section_name + "', " + position + " )";
      
      state = conn.createStatement();

      if( state.executeUpdate(SQL) > 0 )
      {
        result = "Section added.";
        
        addSectionText(page_id);
      }

      conn.close();
    }
    catch (Exception e)
    {
      // to do
      result = "Error in Section.deleteSection(): " + e.getMessage() + "<br>SQL: " + SQL;
    }
    
    return result;
  }
  
  public String moveSectionUp(String section_id)
  {
    String SQL = "";
    String result = "";
    if( section_id == null ) return "Error: section_id not specified.";
    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();

      SQL = "select page_id, position from wiki_section where section_id = " + section_id;
      
      String page_id = "";
      String position = "";
      
      ResultSet rs = state.executeQuery(SQL);
      while( rs.next() )
      {
        page_id = rs.getString("page_id");
        position = rs.getString("position");
      }
      rs.close();
      if( page_id == null ) return "Error: page_id not found for section: " + section_id;
      if( position == null ) return "Error: position not found for section: " + section_id;
      int current_position = Integer.valueOf(position).intValue();
      if( current_position < 2 ) return "Error: cannot move section up any more.";
      int new_position = current_position - 1;

      // move the other section down first
      state = conn.createStatement();
      SQL = "update wiki_section set position = " + current_position
          + "where page_id = " + page_id
          + "  and position = " + new_position;
      state.executeUpdate(SQL);

      // now move this section up
      state = conn.createStatement();
      SQL = "update wiki_section set position = " + new_position
          + "where section_id = " + section_id;
      if( state.executeUpdate(SQL) > 0 )
      {
        result = "Section moved.";
      }

      conn.close();
    }
    catch (Exception e)
    {
      // to do
      result = "Error in Section.moveSectionUp(): " + e.getMessage() + "<br>SQL: " + SQL;
    }
    
    return result;
  }

  public String moveSectionDown(String section_id)
  {
    String SQL = "";
    String result = "";
    if( section_id == null ) return "Error: section_id not specified.";
    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();

      SQL = "select page_id, position from wiki_section where section_id = " + section_id;
      
      String page_id = "";
      String position = "";
      
      ResultSet rs = state.executeQuery(SQL);
      while( rs.next() )
      {
        page_id = rs.getString("page_id");
        position = rs.getString("position");
      }
      rs.close();
      if( page_id == null ) return "Error: page_id not found for section: " + section_id;
      if( position == null ) return "Error: position not found for section: " + section_id;
      int current_position = Integer.valueOf(position).intValue();
      int new_position = current_position + 1;

      // move the other section up first
      state = conn.createStatement();
      SQL = "update wiki_section set position = " + current_position
          + "where page_id = " + page_id
          + "  and position = " + new_position;
      if( state.executeUpdate(SQL) < 1 )
      {
        return "Error: cannot move section down any more.";
      }

      // now move this section down
      state = conn.createStatement();
      SQL = "update wiki_section set position = " + new_position
          + "where section_id = " + section_id;
      if( state.executeUpdate(SQL) > 0 )
      {
        result = "Section moved.";
      }

      conn.close();
    }
    catch (Exception e)
    {
      // to do
      result = "Error in Section.moveSectionDown(): " + e.getMessage() + "<br>SQL: " + SQL;
    }
    
    return result;
  }

  public String renameSection(String section_id, String new_section_name)
  {
    String SQL = "";
    String result = "";
    if( section_id == null ) return "Error: no section_id provided";
    if( new_section_name == null ) return "Error: no section name provided";

    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();

      new_section_name = new_section_name.replaceAll("'", "&#39;");

      SQL = "update wiki_section set section_name = '" + new_section_name + "' "
          + "where section_id = " + section_id;

      if( state.executeUpdate(SQL) > 0 )
      {
        result = "Section renamed.";
      }

      conn.close();
    }
    catch (Exception e)
    {
      // to do
      result = "Error in Section.deleteSection(): " + e.getMessage() + "<br>SQL: " + SQL;
    }
    
    return result;
  }

  public String deleteSection(String section_id)
  {
    String SQL = "";
    String result = "";
    if( section_id == null ) return "Error: no section_id provided";
    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();

      SQL = "update wiki_section set active_flag = 'N' "
          + "where section_id = " + section_id;

      if( state.executeUpdate(SQL) > 0 )
      {
        result = "Section deleted.";
      }

      conn.close();
    }
    catch (Exception e)
    {
      // to do
      result = "Error in Section.deleteSection(): " + e.getMessage() + "<br>SQL: " + SQL;
    }
    
    return result;
  }
  
  public String unDeleteSection(String section_id)
  {
    String SQL = "";
    String result = "";
    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();

      SQL = "update wiki_section set active_flag = 'Y' "
          + "where section_id = " + section_id;

      if( state.executeUpdate(SQL) > 0 )
      {
        result = "Section deleted.";
      }

      conn.close();
    }
    catch (Exception e)
    {
      // to do
      result = "Error in Section.unDeleteSection(): " + e.getMessage() + "<br>SQL: " + SQL;
    }
    
    return result;
  }
  
  private String addSectionText(String page_id)
  {
    String SQL = "";
    String result = "";
    String section_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();

      SQL = "select max(section_id) section_id from wiki_section "
        + "where page_id = " + page_id;
      
      ResultSet rs = state.executeQuery(SQL);
      while( rs.next() )
      {
        section_id = rs.getString("section_id");
      }
      rs.close();
      
      if( section_id == null || section_id.length() == 0 )
      {
        conn.close();
        return "Error: new section id not found.";
      }

      SQL = "insert into wiki_text (section_id, text) "
        + "values ( " + section_id + ", 'new section' )";
      
      state = conn.createStatement();

      if( state.executeUpdate(SQL) > 0 )
      {
        result = "  added.";
      }
    }
    catch (Exception e)
    {
      // to do
      result = "Error in Section.addSectionText(): " + e.getMessage() + "<br>SQL: " + SQL;
    }
    
    return result;
  }

}