/*
    JSPWiki - a JSP-based WikiWiki clone.

    Copyright (C) 2001-2002 Janne Jalkanen (Janne.Jalkanen@iki.fi)

    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU Lesser General Public License as published by
    the Free Software Foundation; either version 2.1 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 Lesser General Public License for more details.

    You should have received a copy of the GNU Lesser 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.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Iterator;
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 com.ecyrd.jspwiki.*;

/**
 *  Provides a simple database-based repository for Wiki pages.
 *
 *  This is a rather complete rewrite of FileSystemProvider.
 *
 *  @author Thierry Lach
 */
public class DatabaseProvider
    implements WikiPageProvider
{
    private static final Category   log = Category.getInstance(DatabaseProvider.class);
    private Connection m_connection = null;
    private String m_url = "";
    private String m_username;
    private String m_password ;
    private String m_driver;

    /**
     *  Name of the property that defines the url for the database connection to the pages.
     */
    public static final String      PROP_URL = "jspwiki.databaseProvider.url";

    /**
     *  Name of the property that defines the username for the database connection to the pages.
     */
    public static final String      PROP_USERNAME = "jspwiki.databaseProvider.username";

    /**
     *  Name of the property that defines the password for the database connection to the pages.
     */
    public static final String      PROP_PASSWORD = "jspwiki.databaseProvider.password";

    /**
     *  Name of the property that defines the driver for the database connection to the pages.
     */
    public static final String      PROP_DRIVER = "jspwiki.databaseProvider.driver";

    /**
     *  Name of the property that defines where default page directories are.
     *  Used only during database initialization.
     */
    public static final String      PROP_DEFAULT_PAGE_DIR = "jspwiki.databaseProvider.default.page.dir";

    /**
     *  @throws FileNotFoundException If the specified page directory does not exist.
     *  @throws IOException In case the specified page directory is a file, not a directory.
     */
    public void initialize( Properties properties )
        throws NoRequiredPropertyException,
                IOException
    {
        log.debug("Initing DatabaseProvider");
        m_username = WikiEngine.getRequiredProperty( properties, PROP_USERNAME );
        m_password = WikiEngine.getRequiredProperty( properties, PROP_PASSWORD );
        m_driver = WikiEngine.getRequiredProperty( properties, PROP_DRIVER );

        m_url = WikiEngine.getRequiredProperty( properties, PROP_URL );

        try {
        	Class.forName(m_driver);
			m_connection = DriverManager.getConnection(m_url, m_username, m_password);
		} catch (SQLException e) {
            throw new IOException("Unable to get database connection: "+m_url);
		} catch (ClassNotFoundException e) {
            throw new IOException("Unable to find database driver: "+m_driver);
		}

        int rowCount = 0;
        // Check if the page table exists
        try {
            String sql = "SELECT COUNT(*) FROM WIKI_PAGE";
            Statement stmt = m_connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            rs.next();
	        rowCount = rs.getInt(1);
        	rs.close();
        	stmt.close();
        }
        catch (SQLException se) {
            throw new IOException("Unable to find the table: WIKI_PAGE");
        }

        // Check if the version table exists
        try {
            String sql = "SELECT COUNT(*) FROM WIKI_PAGE_VERSION";
            Statement stmt = m_connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            rs.next();
        	rs.close();
        	stmt.close();
        }
        catch (SQLException se) {
            throw new IOException("Unable to find the table: WIKI_PAGE_VERSION");
        }

        if (rowCount == 0) {
            try {
                initializeTables(WikiEngine.getRequiredProperty( properties, PROP_DEFAULT_PAGE_DIR ));
        	}
	        catch (IOException ioe) {
    	        throw new IOException("Unable to initialize the table: WIKI_PAGE");
        	}
        }

        log.info("Wikipages are read from : "+m_url + " table WIKI_PAGE");
        log.info("Wikipage versions are read from : "+m_url + " table WIKI_PAGE_VERSION");
    }

    public boolean isConnectionOK()
    {
		try {
			String sql = "SELECT 1";
			Statement stmt = m_connection.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.close();
			stmt.close();
		}
		catch (SQLException se) {
			return false;
		}
		return true;
    }

    public void ensureConnection()
    	throws SQLException
    {
		if (!isConnectionOK())
		{	// must have timed out
			log.info("connection lost -- attempting reconnection");
			m_connection = DriverManager.getConnection(m_url, m_username, m_password);
		}
    }

    public boolean pageExists( String page )
    {
        boolean found = false;
        try {
            ensureConnection();
            String sql = "SELECT PAGE_ID FROM WIKI_PAGE " +
        	             " WHERE PAGE_NAME = ?";
        	PreparedStatement ps = m_connection.prepareStatement(sql);
        	ps.setString(1, page);
        	ResultSet rs = ps.executeQuery();
	        if( rs.next() )
	        {
                found = true;
	        }
        	rs.close();
        	ps.close();
        }
        catch (SQLException se) {
        }
        return found;
     }

    /**
     *  Read the text directly from the correct file.
     */
    private String getCurrentPageText( String page )
    {
        String result  = null;
        try {
            ensureConnection();
            String sql = "SELECT PAGE_TEXT FROM WIKI_PAGE"  +
        	             " WHERE PAGE_NAME = ?";
        	PreparedStatement ps = m_connection.prepareStatement(sql);
        	ps.setString(1, page);
        	ResultSet rs = ps.executeQuery();

	        if( rs.next() )
	        {
                    result = rs.getString("PAGE_TEXT");
	        }
	        else
	        {
	            // This is okay.
	            log.info("New page '"+page+"'");
	        }
        	rs.close();
        	ps.close();

        }
        catch (SQLException se) {
        }

        return result;
    }

    /**
     *  This implementation just returns the current version, as filesystem
     *  does not provide versioning information for now.
     */
    public String getPageText( String page, int version )
        throws ProviderException
    {
        if (version == WikiPageProvider.LATEST_VERSION) {
	        return getCurrentPageText( page );
        }

        String result  = null;
        try {
            ensureConnection();
            String sql = "SELECT VERSION_TEXT FROM WIKI_PAGE_VERSION"  +
        	             " WHERE VERSION_NAME = ?" +
        	             " AND VERSION_NUM = ?";
        	PreparedStatement ps = m_connection.prepareStatement(sql);
        	ps.setString(1, page);
        	ps.setInt(2, version);
        	ResultSet rs = ps.executeQuery();

	        if( rs.next() )
	        {
                result = rs.getString("VERSION_TEXT");
	        }
	        else
	        {
	            // This is okay.
	            log.info("New page '"+page+"'");
	        }
        	rs.close();
        	ps.close();

        }
        catch (SQLException se) {
        }
        return result;
    }

    private void insertPageText( WikiPage page, String text )
    {
        try {
            ensureConnection();
        	String sql = "INSERT INTO WIKI_PAGE" +
        	             " (PAGE_NAME, PAGE_VERSION," +
        	             "  PAGE_CREATED, PAGE_CREATED_BY," +
        	             "  PAGE_MODIFIED, PAGE_MODIFIED_BY," +
        	             "  PAGE_TEXT)" +
        	             " VALUES (?, ?, ?, ?, ?, ?, ?)";
        	PreparedStatement psPage = m_connection.prepareStatement(sql);
        	psPage.setString(1, page.getName());
        	psPage.setInt(2, 1);
        	Timestamp d = null;
        	if (page.getLastModified() != null) {
           	    d = new Timestamp(page.getLastModified().getTime());
        	}
        	else {
           	    d = new Timestamp(System.currentTimeMillis());
        	}
        	psPage.setTimestamp(3, d);
            psPage.setString(4, page.getAuthor());
        	psPage.setTimestamp(5, d);
            psPage.setString(6, page.getAuthor());
            psPage.setString(7, text);
        	psPage.execute();
            psPage.close();

        	sql = "INSERT INTO WIKI_PAGE_VERSION" +
        	      " (VERSION_NAME, VERSION_NUM," +
        	      "  VERSION_CREATED, VERSION_CREATED_BY," +
        	      "  VERSION_MODIFIED, VERSION_MODIFIED_BY," +
        	      "  VERSION_TEXT)" +
        	      " VALUES (?, ?, ?, ?, ?, ?, ?)";
        	PreparedStatement psVer = m_connection.prepareStatement(sql);
        	psVer.setString(1, page.getName());
        	psVer.setInt(2, 1);
        	psVer.setTimestamp(3, d);
            psVer.setString(4, page.getAuthor());
        	psVer.setTimestamp(5, d);
            psVer.setString(6, page.getAuthor());
            psVer.setString(7, text);
        	psVer.execute();
            psVer.close();
        }
        catch( SQLException e )
        {
            log.error( "Saving failed" );
        }
    }

    private void updatePageText( WikiPage page, String text )
    {
        String sql = "SELECT PAGE_VERSION FROM WIKI_PAGE" +
                     " WHERE PAGE_NAME = ?";
        int version = 0;
        try {
            ensureConnection();
            PreparedStatement psQuery = m_connection.prepareStatement(sql);
            psQuery.setString(1, page.getName());
            ResultSet rs = psQuery.executeQuery();
            rs.next();
	        version = rs.getInt(1);
            rs.close();
        	psQuery.close();
        }
        catch (SQLException se) {
            log.error( "Unable to get current version" );
        }
        version += 1;

        try {
            ensureConnection();
        	sql = "UPDATE WIKI_PAGE" +
        	      "  SET PAGE_MODIFIED = ?," +
        	      "      PAGE_MODIFIED_BY = ?," +
        	      "      PAGE_VERSION = ?," +
        	      "      PAGE_TEXT = ?" +
        	      " WHERE PAGE_NAME = ?";
        	PreparedStatement psPage = m_connection.prepareStatement(sql);
        	Timestamp d = null;
        	if (page.getLastModified() != null) {
           	    d = new Timestamp(page.getLastModified().getTime());
        	}
        	else {
           	    d = new Timestamp(System.currentTimeMillis());
        	}
        	psPage.setTimestamp(1, d);
            psPage.setString(2, page.getAuthor());
        	psPage.setInt(3, version);
        	psPage.setString(4, text);
            psPage.setString(5, page.getName());
        	psPage.execute();
            psPage.close();

        	sql = "INSERT INTO WIKI_PAGE_VERSION" +
        	      " (VERSION_NAME, VERSION_NUM," +
        	      "  VERSION_CREATED, VERSION_CREATED_BY," +
        	      "  VERSION_MODIFIED, VERSION_MODIFIED_BY," +
        	      "  VERSION_TEXT)" +
        	      " VALUES (?, ?, ?, ?, ?, ?, ?)";
        	PreparedStatement psVer = m_connection.prepareStatement(sql);
        	psVer.setString(1, page.getName());
        	psVer.setInt(2, version);
        	psVer.setTimestamp(3, d);
            psVer.setString(4, page.getAuthor());
        	psVer.setTimestamp(5, d);
            psVer.setString(6, page.getAuthor());
            psVer.setString(7, text);
        	psVer.execute();
            psVer.close();
        }
        catch( SQLException e )
        {
            log.error( "Saving failed" );
        }
    }

    public void putPageText( WikiPage page, String text )
    {
    	if ( pageExists(page.getName()) ) {
            updatePageText ( page, text );
    	}
    	else {
            insertPageText ( page, text );
    	}
    }

    public Collection getAllPages()
        throws ProviderException
    {
        log.debug("Getting all pages...");

        ArrayList set = new ArrayList();

        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ensureConnection();
        	String sql = "SELECT PAGE_NAME FROM WIKI_PAGE";
        	ps = m_connection.prepareStatement(sql);
        	rs = ps.executeQuery();
	        while(rs.next())
	        {
	            String wikiname = rs.getString("PAGE_NAME");

	            WikiPage page = getPageInfo( wikiname,
	                                         WikiPageProvider.LATEST_VERSION );
	            if( page == null )
	            {
	                // This should not really happen.
	                // FIXME: Should we throw an exception here?
	                log.error("Page "+wikiname+" was found in directory listing, but could not be located individually.");
	                continue;
	            }

	            set.add( page );
	        }
        	rs.close();
        	ps.close();
        }
        catch (SQLException se) {
        }

        return set;
    }

    public Collection getAllChangedSince( Date date )
    {
        ArrayList set = new ArrayList();

        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ensureConnection();
        	String sql = "SELECT PAGE_NAME FROM WIKI_PAGE" +
        	              " WHERE PAGE_MODIFIED > ?";
        	ps = m_connection.prepareStatement(sql);
            ps.setDate(1, new java.sql.Date(date.getTime()));
        	rs = ps.executeQuery();
	        while(rs.next())
	        {
	            String wikiname = rs.getString("PAGE_NAME");

	            WikiPage page = null;
				try {
					page = getPageInfo(wikiname, WikiPageProvider.LATEST_VERSION);
				} catch (ProviderException e) {
					page = null;
				}
	            if( page == null )
	            {
	                // This should not really happen.
	                // FIXME: Should we throw an exception here?
	                log.error("Page "+wikiname+" was found in directory listing, but could not be located individually.");
	                continue;
	            }

	            set.add( page );
	        }
        	rs.close();
        	ps.close();
        }
        catch (SQLException se) {
        }

        return set;
    }

    public int getPageCount()
    {
        int count = 0;
        // Check if the page table exists
        try {
            ensureConnection();
            String sql = "SELECT COUNT(*) FROM WIKI_PAGE";
            Statement stmt = m_connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            rs.next();
            count = rs.getInt(1);
            rs.close();
            stmt.close();
        }
        catch (SQLException se) {
        }
        return count;

    }

    public Collection findPages( QueryItem[] query )
    {
        TreeSet res = new TreeSet( new SearchResultComparator() );

        Collection wikipages = null;
		try {
			wikipages = getAllPages();
		} catch (ProviderException e) {
		}

    nextfile:
        for(Iterator i = wikipages.iterator(); i.hasNext(); )
        {
            String line = null;

            // log.debug("Searching page "+wikipages[i].getPath() );
			WikiPage page = (WikiPage)i.next();
            try
            {
                String pagetext       = getCurrentPageText(page.getName());

                int scores[] = new int[ query.length ];

                BufferedReader in = new BufferedReader( new StringReader(pagetext) );

                while( (line = in.readLine()) != null )
                {
                    line = line.toLowerCase();

                    for( int j = 0; j < query.length; j++ )
                    {
                        int index = -1;

                        while( (index = line.indexOf( query[j].word, index+1 )) != -1 )
                        {
                            // log.debug("   Match found for "+query[j].word );

                            if( query[j].type != QueryItem.FORBIDDEN )
                            {
                                scores[j]++; // Mark, found this word n times
                            }
                            else
                            {
                                // Found something that was forbidden.
                                continue nextfile;
                            }
                        }
                    }
                }

                //
                //  Check that we have all required words.
                //

                int totalscore = 0;

                for( int j = 0; j < scores.length; j++ )
                {
                    // Give five points for each occurrence
                    // of the word in the wiki name.

                    if( page.getName().toLowerCase().indexOf( query[j].word ) != -1 &&
                        query[j].type != QueryItem.FORBIDDEN )
                        scores[j] += 5;

                    //  Filter out pages if the search word is marked 'required'
                    //  but they have no score.

                    if( query[j].type == QueryItem.REQUIRED && scores[j] == 0 )
                        continue nextfile;

                    //
                    //  Count the total score for this page.
                    //
                    totalscore += scores[j];
                }

                if( totalscore > 0 )
                {
                    res.add( new SearchResultImpl(page.getName(),totalscore) );
                }
            }
            catch( IOException e )
            {
                log.error( "Failed to read", e );
            }
        }

        return res;
    }

    /**
     *  Always returns the latest version here.
     */
    private WikiPage getCurrentPageInfo( String page)
    {
        WikiPage p = null;
        try {
            ensureConnection();
            String sql = "SELECT PAGE_VERSION," +
                         " PAGE_MODIFIED," +
                         " PAGE_MODIFIED_BY" +
                         " FROM WIKI_PAGE" +
          	             " WHERE PAGE_NAME = ?";
        	PreparedStatement ps = m_connection.prepareStatement(sql);
        	ps.setString(1, page);
        	ResultSet rs = ps.executeQuery();

	        if(rs.next())
	        {
        		p = new WikiPage( page );
                p.setVersion(rs.getInt("PAGE_VERSION"));
	            p.setLastModified( rs.getTimestamp("PAGE_MODIFIED") );
	            p.setAuthor( rs.getString("PAGE_MODIFIED_BY") );
	        	rs.close();
    	    	ps.close();
	        }
        }
        catch (SQLException se) {
            log.error("get current page info", se);
            p = new WikiPage( page );
        }
        return p;
    }

    /**
     *  Return the correct version of the page.
     */
    public WikiPage getPageInfo( String page, int version )
        throws ProviderException
    {
        WikiPage p = null;
        if (version == WikiPageProvider.LATEST_VERSION ) {
            p = getCurrentPageInfo(page);
        }
        else {
	        try {
                ensureConnection();
	            String sql = "SELECT VERSION_NUM," +
	                         " VERSION_MODIFIED," +
	                         " VERSION_MODIFIED_BY" +
	                         " FROM WIKI_PAGE_VERSION" +
	          	             " WHERE VERSION_NAME = ?" +
	          	             " AND VERSION_NUM = ?";
	        	PreparedStatement ps = m_connection.prepareStatement(sql);
	        	ps.setString(1, page);
	        	ps.setInt(2, version);
	        	ResultSet rs = ps.executeQuery();

		        if(rs.next())
		        {
	        		p = new WikiPage( page );
	                p.setVersion(rs.getInt("VERSION_NUM"));
		            p.setLastModified( rs.getTimestamp("VERSION_MODIFIED") );
		            p.setAuthor( rs.getString("VERSION_MODIFIED_BY") );
		        	rs.close();
	    	    	ps.close();
		        }
	        }
	        catch (SQLException se) {
	        }
        }
        return p;
    }

    /**
     *  Provide the list of versions.
     */
    public List getVersionHistory( String page )
        throws ProviderException
    {
        ArrayList list = new ArrayList();

        try {
            ensureConnection();
            String sql = "SELECT VERSION_NUM," +
                         " VERSION_MODIFIED," +
                         " VERSION_MODIFIED_BY" +
                         " FROM WIKI_PAGE_VERSION" +
          	             " WHERE VERSION_NAME = ?";
        	PreparedStatement ps = m_connection.prepareStatement(sql);
        	ps.setString(1, page);
        	ResultSet rs = ps.executeQuery();

	        while(rs.next())
	        {
        		WikiPage p = new WikiPage( page );
                p.setVersion(rs.getInt("VERSION_NUM"));
	            p.setLastModified( rs.getTimestamp("VERSION_MODIFIED") );
	            p.setAuthor( rs.getString("VERSION_MODIFIED_BY") );
                list.add(p);
	        }
        	rs.close();
	    	ps.close();
        }
        catch (SQLException se) {
        }
        return list;
    }

    public String getProviderInfo()
    {
        return "";
    }

    public void deleteVersion( String pageName, int version )
    {
        // TODO: If they delete the current version,
        //       replace the current version with the previous version.
        try {
            ensureConnection();
        	String sql = "DELETE FROM WIKI_PAGE_VERSION" +
        	             " WHERE VERSION_NAME = ?" +
        	             " AND VERSION_NUM = ?";
        	PreparedStatement psVer = m_connection.prepareStatement(sql);
        	psVer.setString(1, pageName);
        	psVer.setInt(2, version);
        	psVer.execute();
            psVer.close();

        }
        catch( SQLException e )
        {
            log.error( "Delete failed" );
        }
    }

    public void deletePage( String pageName )
    {
        try {
            ensureConnection();
        	String sql = "DELETE FROM WIKI_PAGE_VERSION" +
        	             " WHERE VERSION_NAME = ?";
        	PreparedStatement psVer = m_connection.prepareStatement(sql);
        	psVer.setString(1, pageName);
        	psVer.execute();
            psVer.close();

        	sql = "DELETE FROM WIKI_PAGE" +
        	      " WHERE PAGE_NAME = ?";
        	PreparedStatement psPage = m_connection.prepareStatement(sql);
        	psPage.setString(1, pageName);
        	psPage.execute();
            psPage.close();

        }
        catch( SQLException e )
        {
            log.error( "Delete failed" );
        }
    }

	/**
	 * Method initializeTables.
	 */
	private void initializeTables(String default_page_dir)
	throws IOException {
		FileSystemProvider fsp = new FileSystemProvider();
        Properties props = new Properties();
        props.put(FileSystemProvider.PROP_PAGEDIR, default_page_dir);
        try {
			fsp.initialize(props);
	        Collection pages = fsp.getAllPages();
	        for (Iterator i = pages.iterator(); i.hasNext(); ) {
	        	WikiPage page = (WikiPage)i.next();
	            String text = fsp.getPageText(page.getName(), WikiPageProvider.LATEST_VERSION);
	            putPageText(page, text);
	        }
        }
        catch (ProviderException e) {
            throw new IOException(e.getMessage());
        }
        catch (NoRequiredPropertyException e) {
            throw new IOException(e.getMessage());
        }
	}

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

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

        public WikiPage getPage()
        {
            return m_page;
        }

        public int getScore()
        {
            return m_score;
        }
    }

}