/*

 *   StarOffice News Server

 *   see AUTHORS for the list of contributors

 *

 *   This program is free software: you can redistribute it and/or modify

 *   it under the terms of the GNU General Public License as published by

 *   the Free Software Foundation, either version 3 of the License, or

 *   (at your option) any later version.

 *

 *   This program is distributed in the hope that it will be useful,

 *   but WITHOUT ANY WARRANTY; without even the implied warranty of

 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 *   GNU General Public License for more details.

 *

 *   You should have received a copy of the GNU General Public License

 *   along with this program.  If not, see <http://www.gnu.org/licenses/>.

 */

package com.so.news.storage;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.zip.CRC32;

import com.so.news.Config;

import com.so.news.util.StringTemplate;

/**

 * Database abstraction class.

 * @author Christian Lins (christian.lins@web.de)

 */

public class Database

{

  private static Database instance = null;

  /**

   * Initializes the Database subsystem, e.g. loading a JDBC driver and

   * connection to the Database Managment System.

   * This method is called when the daemon starts up or at the first

   * call to Database.getInstance().

   * @throws java.lang.Exception

   */

  public static void arise()

    throws Exception

  {

    // Tries to load the Database driver and establish a connection.

    if(instance == null)

      instance = new Database();

  }

  /**

   * @return Instance of the current Database backend. Returns null if an error

   * has occurred.

   */

  public static Database getInstance()

  {

    try

    {

      arise();

      return instance;

    }

    catch(Exception ex)

    {

      ex.printStackTrace();

      return null;

    }

  }

  private Connection conn = null;

  /**

   * Private constructor.

   * @throws java.lang.Exception

   */

  private Database()

    throws Exception

  {

    Class.forName(

            Config.getInstance().get("n3tpd.storage.dbmsdriver", ""));

    this.conn = DriverManager.getConnection(

            Config.getInstance().get("n3tpd.storage.database", ""),

            Config.getInstance().get("n3tpd.storage.user", "n3tpd_user"),

            Config.getInstance().get("n3tpd.storage.password", ""));

    this.conn.setAutoCommit(false);

  }

  /**

   * Adds an article to the database.

   * @param article

   * @return

   * @throws java.sql.SQLException

   */

  public boolean addArticle(Article article)

    throws SQLException

  {

    Statement stmt = this.conn.createStatement();

    String sql0 = "START TRANSACTION";

    String sql1 = "INSERT INTO articles (message_id,header,body)" +

            "VALUES('%mid', '%header', '%body')";

    StringTemplate tmpl = new StringTemplate(sql1);

    tmpl.set("body", article.getBody());

    tmpl.set("mid", article.getMessageID());

    tmpl.set("header", article.getHeaderSource());

    sql1 = tmpl.toString();

    String sql2 = "COMMIT";

    // Add statements as batch

    stmt.addBatch(sql0);

    stmt.addBatch(sql1);

    // TODO: For each newsgroup add a reference

    String sql = "INSERT INTO postings (group_id, article_id, article_index)" +

                 "VALUES (%gid, (SELECT article_id FROM articles WHERE message_id = '%mid')," +

                 " %idx)";

    tmpl = new StringTemplate(sql);

    tmpl.set("gid", article.getGroupID());

    tmpl.set("mid", article.getMessageID());

    tmpl.set("idx", getMaxArticleIndex() + 1);

    stmt.addBatch(tmpl.toString());

    // Commit

    stmt.addBatch(sql2);

    // And execute the batch

    stmt.executeBatch();

    return true;

  }

  /**

   * Adds a group to the Database.

   * @param name

   * @throws java.sql.SQLException

   */

  public boolean addGroup(String name)

    throws SQLException

  {

    CRC32 crc = new CRC32();

    crc.update(name.getBytes());

    long id = crc.getValue();

    Statement stmt = conn.createStatement();

    return 1 == stmt.executeUpdate("INSERT INTO Groups (ID, Name) VALUES (" + id + ", '" + name + "')");

  }

  public void delete(Article article)

  {

  }

  public void delete(Group group)

  {

  }

  public Article getArticle(String messageID)

    throws SQLException

  {

    Statement stmt = this.conn.createStatement();

    ResultSet rs =

      stmt.executeQuery("SELECT * FROM articles WHERE message_id = '" + messageID + "'");

    return new Article(rs);

  }

  public Article getArticle(long gid, long article_id)

    throws SQLException

  {

    Statement stmt = this.conn.createStatement();

    String sql = "SELECT * FROM articles WHERE article_id = " +

            "(SELECT article_id FROM postings WHERE " +

            "group_id = " + gid + " AND article_id = " + article_id +")";

    ResultSet rs =

      stmt.executeQuery(sql);

    if(rs.next())

      return new Article(rs);

    else

      return null;

  }

  public ResultSet getArticles()

    throws SQLException

  {

    Statement stmt = conn.createStatement();

    return stmt.executeQuery("SELECT * FROM articles");

  }

  /**

   * Reads all Groups from the Database.

   * @return

   * @throws java.sql.SQLException

   */

  public ResultSet getGroups()

    throws SQLException

  {

    Statement stmt = conn.createStatement();

    ResultSet rs = stmt.executeQuery("SELECT * FROM groups");

    return rs;

  }

  /**

   * Returns the Group that is identified by the name.

   * @param name

   * @return

   * @throws java.sql.SQLException

   */

  public Group getGroup(String name)

    throws SQLException

  {   

    Statement stmt = this.conn.createStatement();

    String sql = "SELECT group_id FROM groups WHERE Name = '%name'";

    StringTemplate tmpl = new StringTemplate(sql);

    tmpl.set("name", name);

    ResultSet rs = stmt.executeQuery(tmpl.toString());

    if(!rs.next())

      return null;

    else

    {

      long id = rs.getLong("group_id");

      return new Group(name, id);

    }

  }

  public int getMaxArticleIndex()

    throws SQLException

  {

    Statement stmt = conn.createStatement();

    ResultSet rs = stmt.executeQuery(

      "SELECT Max(article_index) FROM postings");

    if(!rs.next())

      return 0;

    else

      return rs.getInt(1);

  }

  public int getLastArticleNumber(Group group)

    throws SQLException

  {

    Statement stmt = conn.createStatement();

    ResultSet rs = stmt.executeQuery(

      "SELECT Max(article_index) FROM postings WHERE group_id = " + group.getID());

    if(!rs.next())

      return 0;

    else

      return rs.getInt(1);

  }

  public int getFirstArticleNumber(Group group)

    throws SQLException

  {

    Statement stmt = conn.createStatement();

    ResultSet rs = stmt.executeQuery(

      "SELECT Min(article_index) FROM postings WHERE group_id = " + group.getID());

    if(!rs.next())

      return 0;

    else

      return rs.getInt(1);

  }

  /**

   * Returns a group name identified by the given id.

   * @param id

   * @return

   * @throws java.sql.SQLException

   */

  public String getGroup(int id)

    throws SQLException

  {

    Statement stmt = conn.createStatement();

    ResultSet rs   = stmt.executeQuery(

            "SELECT name FROM groups WHERE group_id = '" + id + "'");

    if(rs.next())

    {

      return rs.getString(1);

    }

    else

      return null;

  }

  public Article getOldestArticle()

    throws SQLException

  {

    Statement stmt = conn.createStatement();

    ResultSet rs = 

      stmt.executeQuery("SELECT * FROM Articles WHERE Date = (SELECT Min(Date) FROM Articles)");

    if(rs.next())

      return new Article(rs);

    else

      return null;

  }

  /**

   * Checks if there is a group with the given name in the Database.

   * @param name

   * @return

   * @throws java.sql.SQLException

   */

  public boolean isGroupExisting(String name)

    throws SQLException

  {

    Statement stmt = this.conn.createStatement();

    ResultSet rs   = stmt.executeQuery("SELECT * FROM Groups WHERE Name = '" + name + "'");

    return rs.next();

  }

  public void updateArticle(Article article)

  {

  }

}