/* 
	JSPWiki - a JSP-based WikiWiki clone.

	This PageProvider Copyright (C) 2003 Sulka Haro <sulka@iki.fi>

	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 2 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, write to the Free Software
	Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */
package com.ecyrd.jspwiki.providers;

import java.io.*;
import java.util.Properties;
import java.util.Collection;
import java.util.Date;
import java.util.TreeSet;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Category;

import java.sql.*;

import com.ecyrd.jspwiki.*;

/*

CREATE TABLE jspwiki_pageinfo (
	id INT NOT NULL auto_increment,
	name VARCHAR(200) NOT NULL DEFAULT "",
	version INT NOT NULL DEFAULT 1,
	author VARCHAR(200),
	lastmod TIMESTAMP,
	PRIMARY KEY (name, version),
	INDEX (id)
	);

CREATE TABLE jspwiki_pagedata (
	id INT NOT NULL DEFAULT 0 PRIMARY KEY,
	data TEXT
	);

CREATE TABLE jspwiki_meta (
	structure_version INT NOT NULL DEFAULT 1
	);

*/

/**
 *  Provides an JDBC Database based repository for Wiki pages.
 *
 *  @author Sulka Haro <sulka@iki.fi>
 */
public class MySQLProvider
	implements WikiPageProvider
{
	private static final Category   log = Category.getInstance(MySQLProvider.class);
   
	private static final String	  DRIVER_PROPERTY	  = "jspwiki.MySQLProvider.driver";
	private static final String	  DBURL_PROPERTY	  = "jspwiki.MySQLProvider.dburl";

	private boolean InitSuccessful;

	private String dbUrl;

	public void initialize( Properties properties )
		throws NoRequiredPropertyException
	{
		log.debug("Initializing SQLProvider");
		
		InitSuccessful = true;
		
		this.dbUrl = properties.getProperty(DBURL_PROPERTY);
		
		// try to instantiate the JDBC driver
		try
		{
			Class.forName(properties.getProperty(DRIVER_PROPERTY)).newInstance();
		}
		catch (Exception e)
		{
			// rethrow here
			log.error("JDBC driver " + properties.getProperty(DRIVER_PROPERTY) + " count not be instantiated",e);
			InitSuccessful = false;
		}

		// Try database connection, if it fails at startup, there's little point in
		// going further
		
		Connection dbConnection = this.getDBConnection();
		
		if (dbConnection == null)
		{
			log.error("SQLProvider database initialization failed!");
			InitSuccessful = false;
		}
		else
		{
			// Got connection, don't need it now
			try
			{
				dbConnection.close();
			}
			catch (Exception e)
			{
				log.error("Closing database connection failed",e);
				InitSuccessful = false;
				// rethrow
			}
		}

		checkDBValidity();
		
	}

	private void checkDBValidity()
	{
		Connection connection = getDBConnection();
		
		boolean pageExists = false;
		
		log.debug("*********************** DBUPDATE");
		
		try
		{
			PreparedStatement pstmt = null;
		
			pstmt = connection.prepareStatement("select name, version, id from jspwiki_pageinfo order by name, version");
			ResultSet rset = pstmt.executeQuery();
			
			String name = "";
			long version = 0;
			long id = 0;
			
			pstmt = connection.prepareStatement("UPDATE jspwiki_pageinfo SET version = ? WHERE id = ?");
			
			while (rset.next())
			{
				id = rset.getLong("id");
				
				if (rset.getString("name").equals(name) && rset.getLong("version") > 1 && rset.getLong("version") > (version +1))
				{
					pstmt.setLong(1,version+1);
					pstmt.setLong(2,id);
					pstmt.executeUpdate();
					log.debug("updating DB, changing " + rset.getString("name") + " version from " + rset.getLong("version") + " to " + (version+1));
					version = version + 1;
				}
				else
				{
					version = rset.getLong("version");
				}
				name = rset.getString("name");
			}
		}
		catch (Exception e)
		{
			log.error("Problem validating DB",e);
		}
		finally
		{
			closeConnection(connection);
		}
		
		log.debug("*********************** / DBUPDATE");
	}

	public boolean pageExists( String page )
	{
		Connection connection = getDBConnection();
		
		boolean pageExists = false;
		
		try
		{
			PreparedStatement pstmt = null;
		
			pstmt = connection.prepareStatement("SELECT id FROM jspwiki_pageinfo WHERE name = ? LIMIT 1");
			pstmt.setString(1,page);				
			
			ResultSet rset = pstmt.executeQuery();
			
			if (rset.next())
			{
				pageExists = true;
			}		
		}
		catch (Exception e)
		{
			log.error("Problem querying for page",e);
		}
		finally
		{
			closeConnection(connection);
		}
		
		log.debug("SQLProvider pageExists(" + page + ") = " + pageExists);
		
		return pageExists;
	}

	public synchronized String getPageText( String page, int version )
		throws ProviderException
	{
		log.debug("SQLProvider getPageText(" + page + "," + version + ")");
		
		Connection connection = getDBConnection();
		
		String result = "";
		
		try
		{
			// If version == -1, return latest
		
			PreparedStatement pstmt = null;
		
			if (version == -1)
			{
				pstmt = connection.prepareStatement("SELECT jspwiki_pagedata.data FROM jspwiki_pageinfo, jspwiki_pagedata WHERE jspwiki_pageinfo.id = jspwiki_pagedata.id AND jspwiki_pageinfo.name = ? ORDER BY jspwiki_pageinfo.version DESC LIMIT 1");
				pstmt.setString(1,page);				
			}
			else
			{
				pstmt = connection.prepareStatement("SELECT jspwiki_pagedata.data FROM jspwiki_pageinfo, jspwiki_pagedata WHERE jspwiki_pageinfo.id = jspwiki_pagedata.id AND jspwiki_pageinfo.name = ? and jspwiki_pageinfo.version = ?");
				pstmt.setString(1,page);
				pstmt.setInt(2,version);
			}
			
			ResultSet rset = pstmt.executeQuery();
			
			if (rset.next())
			{
				result = rset.getString("data");
			}
			else
			{
				throw new ProviderException("SQLProvider: page " + page + ", version " + version + " not found");
			}
		}
		catch (Exception e)
		{
			log.error("Problem querying for page",e);
			throw new ProviderException("SQLProvider: problem querying for page " + page + ", version " + version + " not found");
		}
		finally
		{
			closeConnection(connection);
		}
		
		return result;
	}
	
	private int getPageDBId(String page, int version)
		throws ProviderException
	{
		Connection connection = getDBConnection();
		
		int result = 0;
		
		try
		{
			// If version == -1, return latest
		
			PreparedStatement pstmt = null;
		
			if (version == -1)
			{
				pstmt = connection.prepareStatement("SELECT id FROM jspwiki_pageinfo WHERE jspwiki_pageinfo.name = ? ORDER BY jspwiki_pageinfo.version DESC LIMIT 1");
				pstmt.setString(1,page);				
			}
			else
			{
				pstmt = connection.prepareStatement("SELECT id FROM jspwiki_pageinfo WHERE jspwiki_pageinfo.name = ? AND jspwiki_pageinfo.version = ?");
				pstmt.setString(1,page);
				pstmt.setInt(2,version);
			}
			
			ResultSet rset = pstmt.executeQuery();
			
			if (rset.next())
			{
				result = rset.getInt("id");
			}
			else
			{
				throw new ProviderException("SQLProvider: problem querying for page " + page + ", version " + version + " not found");
			}		
		}
		catch (Exception e)
		{
			log.error("Problem querying for page",e);
			throw new ProviderException("SQLProvider: problem querying for page " + page + ", version " + version + " not found");
		}
		finally
		{
			closeConnection(connection);
		}
		
		return result;
	}
	
	public void deleteVersion( String page, int version )
		throws ProviderException
	{
		if (log.isDebugEnabled())
		{
			log.debug("SQLProvider deleteVersion(" + page + "," + version + ")");
		}
		
		Connection connection = getDBConnection();

		try
		{
			// If version == -1, return latest
		
			PreparedStatement pstmt = null;
		
			int id = getPageDBId(page, version);
			
			pstmt = connection.prepareStatement("DELETE FROM jspwiki_pageinfo, jspwiki_pagedata WHERE id = ?");
			pstmt.setInt(1,id);
			
			pstmt.executeUpdate();

		}
		catch (Exception e)
		{
			log.error("Problem querying for page",e);
			throw new ProviderException("SQLProvider: problem deleting page " + page + ", version " + version + " not found");
		}
		finally
		{
			closeConnection(connection);
		}
	}

	public void deletePage( String pageName )
		throws ProviderException
	{
		try
		{
			while (getMaxPageId(pageName) != 0)
			{
				int version = getMaxPageId(pageName);
				deleteVersion(pageName, version);
			}
		}
		catch (Exception e)
		{
			log.error("Problem deleting page", e);
			throw new ProviderException("Problem deleting page");
		}
	}

	private synchronized int getMaxPageId( String page )
	{
		log.debug("SQLProvider getMaxPageId(" + page + ")");	
	
		Connection connection = getDBConnection();
		
		int result = 0;
		
		try
		{
			PreparedStatement pstmt = connection.prepareStatement("SELECT MAX(version) AS maxid FROM jspwiki_pageinfo WHERE jspwiki_pageinfo.name = ?");
			pstmt.setString(1,page);				
			
			ResultSet rset = pstmt.executeQuery();
			
			if (rset.next())
			{
				result = rset.getInt("maxid");
			}		
		}
		catch (Exception e)
		{
			log.error("Problem querying for page max id", e);
		}
		finally
		{
			closeConnection(connection);
		}
	
		return result;
	}

	public synchronized void putPageText( WikiPage page, String text )
	{
		Connection connection = getDBConnection();
		
		int nextVersion = getMaxPageId(page.getName()) + 1;
		int autoIntId;
		
		try
		{
			PreparedStatement pstmt = connection.prepareStatement("INSERT INTO jspwiki_pageinfo (name, author, version) VALUES (?,?,?)");
			pstmt.setString(1,page.getName());
			pstmt.setString(2,page.getAuthor());
			pstmt.setInt(3,nextVersion);
			
			pstmt.executeUpdate();
			
			pstmt = connection.prepareStatement("SELECT last_insert_id()");
			
			ResultSet rset = pstmt.executeQuery();
			
			//ResultSet rset = pstmt.getGeneratedKeys();
			
			if (rset.next())
			{
				autoIntId = rset.getInt(1);
				
				pstmt = connection.prepareStatement("INSERT INTO jspwiki_pagedata (id, data) VALUES (?,?)");
				pstmt.setInt(1,autoIntId);
				pstmt.setString(2,text);
				pstmt.executeUpdate();
			}
			else
			{
				log.error("Problem saving page");
			}
		}
		catch (Exception e)
		{
			log.error("Problem saving page", e);
		}
		finally
		{
			closeConnection(connection);
		}
	}
	
	public int getPageCount ()
	{
		Connection connection = getDBConnection();
		
		int result = 0;
		
		try
		{
			PreparedStatement pstmt = connection.prepareStatement("SELECT count(distinct name) as count FROM jspwiki_pageinfo");
			
			ResultSet rset = pstmt.executeQuery();
			
			while (rset.next())
			{
				result = rset.getInt("count");
			}
		}
		catch (Exception e)
		{
			log.error("Problem querying for all page count",e);
		}
		finally
		{
			closeConnection(connection);
		}
		
		return result;
	}
	
	public Collection getAllChangedSince (Date date)
	{
		Connection connection = getDBConnection();
		
		ArrayList result = new ArrayList();
		
		try
		{
			PreparedStatement pstmt = connection.prepareStatement("SELECT distinct(name) as name FROM jspwiki_pageinfo WHERE timestamp > ?");
			pstmt.setTimestamp(1,new Timestamp(date.getTime()));
			
			ResultSet rset = pstmt.executeQuery();
			
			while (rset.next())
			{
				result.add( getPageInfo(rset.getString("name"),-1) );
			}
		}
		catch (Exception e)
		{
			log.error("Problem querying for all pages changes since " + date, e);
		}
		finally
		{
			closeConnection(connection);
		}
		
		return result;
	}
	
	public Collection getAllPages ()
	{
		log.debug("SQLProvider getAllPages()");
		
		Connection connection = getDBConnection();
		
		ArrayList result = new ArrayList();
		
		try
		{
			PreparedStatement pstmt = connection.prepareStatement("SELECT distinct(name) as name FROM jspwiki_pageinfo");
			
			ResultSet rset = pstmt.executeQuery();
			
			while (rset.next())
			{
				result.add( getPageInfo(rset.getString("name"),-1) );
			}
		}
		catch (Exception e)
		{
			log.error("Problem querying for all pages",e);
		}
		finally
		{
			closeConnection(connection);
		}
		
		return result;
	}
	
	public Collection findPages ( QueryItem[] query )
	{
		log.debug("SQLProvider getAllPages()");
		
		Connection connection = getDBConnection();
		
		TreeSet result = new TreeSet ( new SearchResultComparator() );
		
		StringBuffer queryString = new StringBuffer(50);
		
		for( int j = 0; j < query.length; j++ )
        {
			queryString.append(" AND pd.data LIKE '?' ");
		}
		
		try
		{
			PreparedStatement pstmt = connection.prepareStatement("SELECT distinct(pi.name) as name FROM jspwiki_pageinfo pi, jspwiki_pagedata pd WHERE pi.id = pd.id " + queryString.toString());
			
			for( int j = 0; j < query.length; j++ )
        	{
				pstmt.setString(j,query[j].word);
			}
			
			ResultSet rset = pstmt.executeQuery();
			
			while (rset.next())
			{
				result.add( new SQLSearchResult(rset.getString("name"),1) );
			}
		}
		catch (Exception e)
		{
			log.error("Problem querying for page",e);
		}
		finally
		{
			closeConnection(connection);
		}
		
		return result;
	}

	public WikiPage getPageInfo( String page, int version )
		throws ProviderException
	{
	
		log.debug("SQLProvider getPageInfo(" + page + "," + version + ")");
	
		Connection connection = getDBConnection();
		
		WikiPage result = null;
		
		try
		{
			// If version == -1, return latest
		
			PreparedStatement pstmt = null;
		
			if (version == -1)
			{
				pstmt = connection.prepareStatement("SELECT * FROM jspwiki_pageinfo WHERE jspwiki_pageinfo.name = ? ORDER BY jspwiki_pageinfo.version DESC LIMIT 1");
				pstmt.setString(1,page);
			}
			else
			{
				pstmt = connection.prepareStatement("SELECT * FROM jspwiki_pageinfo WHERE jspwiki_pageinfo.name = ? AND jspwiki_pageinfo.version = ?");
				pstmt.setString(1,page);
				pstmt.setInt(2,version);
			}
			
			ResultSet rset = pstmt.executeQuery();
			
			if (rset.next())
			{
				result = new WikiPage( page );
				result.setAuthor(rset.getString("author"));
				result.setVersion(rset.getInt("version"));
				result.setLastModified(rset.getTimestamp("lastmod"));
			}
		}
		catch (Exception e)
		{
			log.error("Problem querying for page",e);
		}
		finally
		{
			closeConnection(connection);
		}
		
		return result;
	}

	public List getVersionHistory( String page )
		throws ProviderException
	{
		Connection connection = getDBConnection();
		
		ArrayList list = new ArrayList();

		try
		{
			PreparedStatement pstmt = connection.prepareStatement("SELECT version FROM jspwiki_pageinfo WHERE name = ? ORDER BY version DESC");
			pstmt.setString(1,page);
			
			ResultSet rset = pstmt.executeQuery();
			
			while (rset.next())
			{
				list.add( getPageInfo(page,rset.getInt("version")) );
			}
		}
		catch (Exception e)
		{
			log.error("Problem querying for page id " + page + " version history", e);
		}
		finally
		{
			closeConnection(connection);
		}
		
		return list;
	}

	/**
	 *  Returns a new database connection as defined in the propertyfile
	 *  using database.url property.
	 */
	private Connection getDBConnection()
	{
		Connection dbConnection = null;
		PreparedStatement stmt = null;
		
		try
		{
			dbConnection = DriverManager.getConnection(this.dbUrl);
		}
		catch (Exception e)
		{
			log.error("Opening database connection failed.",e);
			e.printStackTrace();
		}
		
		return dbConnection;
	}

	private void closeConnection(Connection connection)
	{
		if (connection != null)
		{
			try
			{
				connection.close();
			}
			catch (Exception e)
			{
				log.error("Closing database connection failed.",e);
			}
		}
	}

	public String getProviderInfo()
	{
		return "JDBC Database provider";
	}

    /**
     *  Searches return this class.
     */
    public class SQLSearchResult
        implements SearchResult
    {
        private int      m_score;
        private WikiPage m_page;

        public SQLSearchResult( String name, int score )
        {
            m_page  = new WikiPage( name );
            m_score = score;
        }

        public WikiPage getPage()
        {
            return m_page;
        }

        public int getScore()
        {
            return m_score;
        }
    }


}