package geekblog;


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


/**
 * Expects a database looking something like the following:
 *
 * <pre>
 * CREATE TABLE entries (ID long identity, posted datetime, title varchar, text varchar)
 * CREATE TABLE articles (entryID long)
 * CREATE TABLE comments (entryID long, name varchar, email varchar, comment varchar)
 * CREATE TABLE links (linkID long identity, linkText varchar, linkURL varchar)
 * </pre>
 *
 * <p>
 * TODO: put close() calls around any open ResultSets
 *
 */
public class JDBCDataManager
  implements DataManager
{
  protected Connection conn;
  protected Map statements = new HashMap();


  /**
   * Constructor placeholder; all real work done in init()
   */
  public JDBCDataManager()
  {
  }


  /**
   * Initializer.
   */
  public void init(ServletContext context)
  {
    // TODO: Look up database.url, database.userid, and database.password 
    // properties from ServletContext (or Preferences node)
    //
  }
  /**
   * Shut everything down.
   */
  public void destroy(ServletContext context)
  {
    try
    {
      conn.close();
    }
    catch (SQLException sqlEx)
    {
      context.log(sqlEx, "*** Error shutting down connection ***");
    }
  }


  // ================================================================
  //                                              JDBC-specific API
  // ================================================================
  /**
   * Return the JDBC Connection used by this DataManager. Intended solely
   * for use by component-internal routines, like that of util.jsp.
   */
  public Connection getConnection()
  {
    return conn;
  }


  // ================================================================
  //                                               Weblog entry API
  // ================================================================
  /**
   * Retrieve an entry by ID.
   */
  public BlogEntry getEntry(long entryID)
  {
    BlogEntry retval = null;

    try
    {
      PreparedStatement ps;
      if ((ps = (PreparedStatement)statements.get("getEntry")) == null)
      {
        String sql = 
          "SELECT posted, title, text FROM entries WHERE id = ?";

        ps = conn.prepareStatement(sql);
        statements.put("getEntry", ps);
      }

      ps.setLong(1, entryID);

      ResultSet rs = ps.executeQuery();
      if (rs.next())
        retval = new BlogEntry(rs.getTimestamp(1), rs.getString(2), rs.getString(3));
      if (rs.next())
      {
        // More than one? This is an error.
        throw new RuntimeException("More than one entry " + entryID + " found.");
      }
      
      return retval;
    }
    catch (SQLException sqlEx)
    {
      throw new RuntimeException(sqlEx);
    }
  }
  /**
   * Retrieve every single last one.
   */
  public Collection getAllEntries()
  {
    ArrayList retval = new ArrayList();

    try
    {
      PreparedStatement ps;
      if ((ps = (PreparedStatement)statements.get("getLastNEntries")) == null)
      {
        String sql = 
          "SELECT id, posted, title, text FROM entries ORDER BY posted DESC";

        ps = conn.prepareStatement(sql);
        statements.put("getLastNEntries", ps);
      }

      ResultSet rs = ps.executeQuery();
      while (rs.next())
      {
        retval.add(new BlogEntry(rs.getTimestamp(2), rs.getString(3), rs.getString(4)));
      }

      return retval;
    }
    catch (SQLException sqlEx)
    {
      throw new RuntimeException(sqlEx);
    }
  }
  /**
   * Retrieve the last "n" entries.
   */
  public Collection getLastNEntries(int n)
  {
    ArrayList retval = new ArrayList();

    try
    {
      PreparedStatement ps;
      if ((ps = (PreparedStatement)statements.get("getLastNEntries")) == null)
      {
        String sql = 
          "SELECT id, posted, title, text FROM entries ORDER BY posted DESC";

        ps = conn.prepareStatement(sql);
        statements.put("getLastNEntries", ps);
      }

      ResultSet rs = ps.executeQuery();
      while (rs.next() && (n-- > 0))
      {
        retval.add(new BlogEntry(rs.getTimestamp(2), rs.getString(3), rs.getString(4)));
      }

      return retval;
    }
    catch (SQLException sqlEx)
    {
      throw new RuntimeException(sqlEx);
    }
  }
  /**
   * Retrieve a list of entries for a given date.
   *
   * @param date Day/month/year tuple to find entries for.
   *             Ignores hour, minutes, seconds.
   */
  public Collection getEntriesForDate(java.util.Date date)
  {
    ArrayList retval = new ArrayList();

    try
    {
      PreparedStatement ps;
      if ((ps = (PreparedStatement)statements.get("getEntriesForDate")) == null)
      {
        String sql = 
          "SELECT posted, title, text FROM entries WHERE DAYOFYEAR(posted) = DAYOFYEAR(?) AND YEAR(posted) = YEAR(?) ORDER BY posted DESC";

        ps = conn.prepareStatement(sql);
        statements.put("getEntriesForDate", ps);
      }

      ps.setDate(1, new java.sql.Date(date.getTime()));
      ps.setDate(2, new java.sql.Date(date.getTime()));

      ResultSet rs = ps.executeQuery();
      while (rs.next())
      {
        retval.add(new BlogEntry(rs.getTimestamp(1), rs.getString(2), rs.getString(3)));
      }

      return retval;
    }
    catch (SQLException sqlEx)
    {
      throw new RuntimeException(sqlEx);
    }
  }
  /**
   * Retrieve a list of entries for a given month/year.
   *
   * @param date Month/year tuple to find entries for.
   *             Ignores day, hour, minutes, seconds.
   */
  public Collection getEntriesForMonth(java.util.Date date)
  {
    ArrayList retval = new ArrayList();

    try
    {
      PreparedStatement ps;
      if ((ps = (PreparedStatement)statements.get("getEntriesForMonth")) == null)
      {
        String sql = 
          "SELECT posted, title, text FROM entries WHERE MONTH(posted) = MONTH(?) AND YEAR(posted) = YEAR(?) ORDER BY posted DESC";

        ps = conn.prepareStatement(sql);
        statements.put("getEntriesForMonth", ps);
      }

      ps.setDate(1, new java.sql.Date(date.getTime()));
      ps.setDate(2, new java.sql.Date(date.getTime()));

      ResultSet rs = ps.executeQuery();
      while (rs.next())
      {
        retval.add(new BlogEntry(rs.getTimestamp(1), rs.getString(2), rs.getString(3)));
      }

      return retval;
    }
    catch (SQLException sqlEx)
    {
      throw new RuntimeException(sqlEx);
    }
  }
  /**
   * Retrieve a count of the entries for a given date.
   *
   * @param date Day/month/year to find count of entries for. 
   *             Ignores hour, minutes, seconds.
   */
  public int getEntryCountForDate(java.util.Date date)
  {
    try
    {
      PreparedStatement ps;
      if ((ps = (PreparedStatement)statements.get("getEntryCountForDate")) == null)
      {
        String sql = 
          "SELECT COUNT(*) FROM entries WHERE DAYOFYEAR(posted) = DAYOFYEAR(?) AND YEAR(posted) = YEAR(?)";

        ps = conn.prepareStatement(sql);
        statements.put("getEntryCountForDate", ps);
      }

      ps.setDate(1, new java.sql.Date(date.getTime()));
      ps.setDate(2, new java.sql.Date(date.getTime()));

      ResultSet rs = ps.executeQuery();
      if (rs.next())
        return rs.getInt(1);
      else
        throw new RuntimeException("How can an aggregate query not return at least one row?!?");
    }
    catch (SQLException sqlEx)
    {
      throw new RuntimeException(sqlEx);
    }
  }
  /**
   * Retrieve a count of the entries for a given month.
   *
   * @param date Month and year to find count of entries for. 
   *             Ignores day, hour, minutes, seconds.
   */
  public int getEntryCountForMonth(java.util.Date date)
  {
    try
    {
      PreparedStatement ps;
      if ((ps = (PreparedStatement)statements.get("getEntryCountForMonth")) == null)
      {
        String sql = 
          "SELECT COUNT(*) FROM entries WHERE MONTH(posted) = MONTH(?) AND YEAR(posted) = YEAR(?)";

        ps = conn.prepareStatement(sql);
        statements.put("getEntryCountForMonth", ps);
      }

      ps.setDate(1, new java.sql.Date(date.getTime()));
      ps.setDate(2, new java.sql.Date(date.getTime()));

      ResultSet rs = ps.executeQuery();
      if (rs.next())
      {
        int retval = rs.getInt(1);
        return retval;
      }
      else
        throw new RuntimeException("How can an aggregate query not return at least one row?!?");
    }
    catch (SQLException sqlEx)
    {
      throw new RuntimeException(sqlEx);
    }
  }
  /**
   * Mark an entry as an article.
   */
  public void markAsArticle(BlogEntry entry)
  {
    try
    {
      PreparedStatement cps;
      if ((cps = (PreparedStatement)statements.get("markAsArticleCheck")) == null)
      {
        String sql =
          "SELECT * FROM articles WHERE entryID = ?";

        cps = conn.prepareStatement(sql);
        statements.put("markAsArticleCheck", cps);
      }
      cps.setLong(1, entry.getID());
      ResultSet rs = cps.executeQuery();
      if (rs.next())
      {
        // This entry has already been marked as an article; don't mark it again
        return;
      }

      PreparedStatement ps;
      if ((ps = (PreparedStatement)statements.get("markAsArticle")) == null)
      {
        String sql = 
          "INSERT INTO articles VALUES(?)";

        ps = conn.prepareStatement(sql);
        statements.put("markAsArticle", ps);
      }

      ps.setLong(1, entry.getID());
      int updateCt = ps.executeUpdate();
      if (updateCt != 1)
      {
        throw new RuntimeException("Couldn't insert entry as article: " + entry.getID());
      }
    }
    catch (SQLException sqlEx)
    {
      throw new RuntimeException(sqlEx);
    }
  }
  /**
   * Retrieve a list of all the entries marked as articles.
   */
  public Collection getArticleEntries()
  {
    ArrayList retval = new ArrayList();

    try
    {
      PreparedStatement ps;
      if ((ps = (PreparedStatement)statements.get("getArticleEntries")) == null)
      {
        String sql = 
          "SELECT posted, title, text FROM entries e, articles a WHERE a.entryID = e.id";

        ps = conn.prepareStatement(sql);
        statements.put("getArticleEntries", ps);
      }

      ResultSet rs = ps.executeQuery();
      while (rs.next())
      {
        retval.add(new BlogEntry(rs.getTimestamp(1), rs.getString(2), rs.getString(3)));
      }

      return retval;
    }
    catch (SQLException sqlEx)
    {
      throw new RuntimeException(sqlEx);
    }
  }
  /**
   * Find all entries containing some text expression
   */
  public Collection findEntries(String text)
  {
    ArrayList retval = new ArrayList();

    try
    {
      String searchParam = "%" + text + "%";
      
      PreparedStatement ps;
      if ((ps = (PreparedStatement)statements.get("findEntries")) == null)
      {
        String sql = 
          "SELECT id, posted, title, text FROM entries WHERE text LIKE ? OR title LIKE ? ORDER BY posted DESC";

        ps = conn.prepareStatement(sql);
        statements.put("findEntries", ps);
      }

      ps.setString(1, searchParam);
      ps.setString(2, searchParam);

      ResultSet rs = ps.executeQuery();
      while (rs.next())
        retval.add(new BlogEntry(rs.getTimestamp(2), rs.getString(3), rs.getString(4)));
      rs.close();

      return retval;
    }
    catch (SQLException sqlEx)
    {
      throw new RuntimeException(sqlEx);
    }
  }
  /**
   * Post an entry (either new or edited).
   */
  public void postEntry(BlogEntry entry)
  {
    try
    {
      // First find if this entry already exists
      if (getEntry(entry.getID()) == null)
      {
        PreparedStatement ps;
        if ((ps = (PreparedStatement)statements.get("postEntry.pt1")) == null)
        {
          String sql = 
            "INSERT INTO entries VALUES (?, ?, ?, ?)";

          ps = conn.prepareStatement(sql);
          statements.put("postEntry.pt1", ps);
        }

        ps.setLong(1, entry.getID());
        ps.setTimestamp(2, new java.sql.Timestamp(entry.getTimestamp().getTime()));
        ps.setString(3, entry.getTitle());
        ps.setString(4, entry.getText());

        int updateCt = ps.executeUpdate();
        if (updateCt != 1)
          throw new RuntimeException("Update count wasn't 1?!?");
      }
      else
      {
        PreparedStatement ps;
        if ((ps = (PreparedStatement)statements.get("postEntry.pt2")) == null)
        {
          String sql = 
            "UPDATE entries SET title = ?, text = ? WHERE ID = ?";

          ps = conn.prepareStatement(sql);
          statements.put("postEntry.pt2", ps);
        }

        ps.setString(1, entry.getTitle());
        ps.setString(2, entry.getText());
        ps.setLong(3, entry.getID());

        int updateCt = ps.executeUpdate();
        if (updateCt != 1)
          throw new RuntimeException("Update count wasn't 1?!?");
      }
    }
    catch (SQLException sqlEx)
    {
      throw new RuntimeException(sqlEx);
    }
  }
  /**
   * Remove an entry. Also removes all comments and articles associated with 
   * that entry.
   */
  public void removeEntry(long entryID)
  {
    // TODO Transact both under a single transaction for atomicity
    //
    try
    {
      PreparedStatement ps;
      if ((ps = (PreparedStatement)statements.get("removeEntry.pt1")) == null)
      {
        String sql = "DELETE FROM entries WHERE ID = ?";
        
        ps = conn.prepareStatement(sql);
        statements.put("removeEntry.pt1", ps);
      }
      ps.setLong(1, entryID);
      ps.executeUpdate();
        // Ignore possibility we tried to remove an entry that doesn't exist
    
      PreparedStatement ps2;
      if ((ps2 = (PreparedStatement)statements.get("removeEntry.pt2")) == null)
      {
        String sql = "DELETE FROM comments WHERE entryID = ?";
        
        ps2 = conn.prepareStatement(sql);
        statements.put("removeEntry.pt2", ps2);
      }
      ps2.setLong(1, entryID);
      ps2.executeUpdate();
        // Ignore possibility we tried to remove an entry that doesn't exist
    
      PreparedStatement ps3;
      if ((ps3 = (PreparedStatement)statements.get("removeEntry.pt3")) == null)
      {
        String sql = "DELETE FROM articles WHERE entryID = ?";
        
        ps3 = conn.prepareStatement(sql);
        statements.put("removeEntry.pt3", ps3);
      }
      ps3.setLong(1, entryID);
      ps3.executeUpdate();
        // Ignore possibility we tried to remove an entry that doesn't exist
    }
    catch (SQLException sqlEx)
    {
      throw new RuntimeException(sqlEx);
    }
  }
  
  
  // ================================================================
  //                                            Weblog comments API
  // ================================================================
  /**
   * Retrieve the list of comments for a given entry.
   */
  public int getCommentCountForEntry(long entryID)
  {
    if (getEntry(entryID) == null)
      return -1;

    try
    {
      PreparedStatement ps;
      if ((ps = (PreparedStatement)statements.get("getCommentCountForEntry")) == null)
      {
        String sql = 
          "SELECT COUNT(*) FROM comments WHERE entryID = ?";

        ps = conn.prepareStatement(sql);
        statements.put("getCommentCountForEntry", ps);
      }

      ps.setLong(1, entryID);

      ResultSet rs = ps.executeQuery();
      if (rs.next())
        return rs.getInt(1);
      
      return -1;
    }
    catch (Exception sqlEx)
    {
      throw new RuntimeException(sqlEx);
    }
  }
  /**
   * Retrieve the list of comments for a given entry.
   */
  public Collection getCommentsForEntry(long entryID)
  {
    if (getEntry(entryID) == null)
      return null;

    ArrayList retval = new ArrayList();

    try
    {
      PreparedStatement ps;
      if ((ps = (PreparedStatement)statements.get("getCommentsForEntry")) == null)
      {
        String sql = 
          "SELECT name, email, comment FROM comments WHERE entryID = ?";

        ps = conn.prepareStatement(sql);
        statements.put("getCommentsForEntry", ps);
      }

      ps.setLong(1, entryID);

      ResultSet rs = ps.executeQuery();
      while (rs.next())
      {
        retval.add(new BlogComment(entryID, rs.getString(1), rs.getString(2), rs.getString(3)));
      }

      return retval;
    }
    catch (Exception sqlEx)
    {
      throw new RuntimeException(sqlEx);
    }
  }
  /**
   * Post a comment to an entry.
   */
  public void postComment(long entryID, BlogComment comment)
  {
    try
    {
      PreparedStatement ps;
      if ((ps = (PreparedStatement)statements.get("postComment")) == null)
      {
        String sql = 
          "INSERT INTO comments VALUES (?, ?, ?, ?)";

        ps = conn.prepareStatement(sql);
        statements.put("postComment", ps);
      }

      ps.setLong(1, entryID);
      ps.setString(2, comment.getName());
      ps.setString(3, comment.getEMail());
      ps.setString(4, comment.getComment());

      int updateCt = ps.executeUpdate();
      if (updateCt != 1)
        throw new RuntimeException("Update count wasn't 1?!?");
    }
    catch (Exception sqlEx)
    {
      throw new RuntimeException(sqlEx);
    }
  }
}