/*
 * Copyright (c) 2010 by Intevation GmbH
 *
 * This program is free software under the LGPL (>=v2.1)
 * Read the file LGPL.txt coming with the software for details
 * or visit http://www.gnu.org/licenses/ if it does not exist.
 */

package de.intevation.artifactdatabase;

import de.intevation.artifacts.Artifact;
import de.intevation.artifacts.ArtifactCollection;
import de.intevation.artifacts.ArtifactCollectionFactory;
import de.intevation.artifacts.ArtifactFactory;
import de.intevation.artifacts.ArtifactSerializer;
import de.intevation.artifacts.User;
import de.intevation.artifacts.UserFactory;

import de.intevation.artifacts.common.utils.XMLUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import java.util.ArrayList;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

import org.w3c.dom.Document;


/**
 * The backend implements the low level layer used to store artifacts
 * in a SQL database.
 *
 * @author <a href="mailto:sascha.teichmann@intevation.de">Sascha L. Teichmann</a>
 */
public class Backend
implements   DatabaseCleaner.ArtifactReviver
{
    private static Logger logger = Logger.getLogger(Backend.class);

    /**
     * The SQL statement to create new artifact id inside the database.
     */
    public static final String SQL_NEXT_ID =
        SQL.get("artifacts.id.nextval");

    /**
     * The SQL statement to insert an artifact into the database.
     */
    public static final String SQL_INSERT =
        SQL.get("artifacts.insert");

    /**
     * The SQL statement to update some columns of an existing
     * artifact in the database.
     */
    public static final String SQL_UPDATE =
        SQL.get("artifacts.update");

    /**
     * The SQL statement to touch the access time of an
     * artifact inside the database.
     */
    public static final String SQL_TOUCH =
        SQL.get("artifacts.touch");

    /**
     * The SQL statement to load an artifact by a given
     * identifier from the database.
     */
    public static final String SQL_LOAD_BY_GID =
        SQL.get("artifacts.select.gid");

    /**
     * The SQL statement to get the database id of an artifact
     * identified by the identifier.
     */
    public static final String SQL_GET_ID =
        SQL.get("artifacts.get.id");

    /**
     * The SQL statement to replace the content of an
     * existing artifact inside the database.
     */
    public static final String SQL_REPLACE =
        SQL.get("artifacts.replace");

    // USER SQL

    public static final String SQL_USERS_NEXT_ID =
        SQL.get("users.id.nextval");

    public static final String SQL_USERS_INSERT =
        SQL.get("users.insert");

    public static final String SQL_USERS_SELECT_ID_BY_GID =
        SQL.get("users.select.id.by.gid");

    public static final String SQL_USERS_SELECT_GID =
        SQL.get("users.select.gid");

    public static final String SQL_USERS_DELETE_ID =
        SQL.get("users.delete.id");

    public static final String SQL_USERS_DELETE_COLLECTIONS =
        SQL.get("users.delete.collections");

    public static final String SQL_USERS_SELECT_ALL =
        SQL.get("users.select.all");

    public static final String SQL_USERS_COLLECTIONS =
        SQL.get("users.collections");

    public static final String SQL_USERS_COLLECTION_IDS =
        SQL.get("users.collection.ids");

    public static final String SQL_USERS_DELETE_ALL_COLLECTIONS =
        SQL.get("users.delete.all.collections");

    public static final String SQL_ARTIFACTS_IN_ONLY_COLLECTION_ONLY =
        SQL.get("artifacts.in.one.collection.only");

    public static final String SQL_OUTDATE_ARTIFACTS_COLLECTION =
        SQL.get("outdate.artifacts.collection");

    public static final String SQL_OUTDATE_ARTIFACTS_USER =
        SQL.get("outdate.artifacts.user");

    public static final String SQL_DELETE_USER_COLLECTION_ITEMS =
        SQL.get("delete.user.collection.items");

    public static final String SQL_COLLECTIONS_NEXT_ID =
        SQL.get("collections.id.nextval");

    public static final String SQL_COLLECTIONS_INSERT =
        SQL.get("collections.insert");

    public static final String SQL_COLLECTIONS_SELECT_USER =
        SQL.get("collections.select.user");

    public static final String SQL_COLLECTIONS_SELECT_ALL =
        SQL.get("collections.select.all");

    /** The singleton.*/
    protected static Backend instance;

    /**
     * The database cleaner. Reference is stored here because
     * the cleaner is woken up if the backend finds an outdated
     * artifact. This artifact should be removed as soon as
     * possible.
     */
    protected DatabaseCleaner cleaner;

    /**
     * To revive an artifact from the bytes coming from the database
     * we need the artifact factory which references the artifact
     * serializer which is able to do the reviving job.
     */
    protected FactoryLookup   factoryLookup;

    /**
     * Little helper interface to decouple the ArtifactDatabase
     * from the Backend. A ArtifactDatabase should depend on a
     * Backend but a Backend not from an ArtifactDatabase.
     */
    public interface FactoryLookup {

        /**
         * Returns an ArtifactFactory which is bound to a given name.
         * @param factoryName The name of the artifact factory.
         * @return The ArtifactFactory bound to the factory name or
         * null if not matching factory is found.
         */
        ArtifactFactory getArtifactFactory(String factoryName);

    } // interface FactoryLookup

    /**
     * Inner class that brigdes between the persisten form of the
     * artifact and the living one inside the artifact database.
     * After the describe(), feed(), advance() and out() operations
     * of the artifact it must be possible to write to modified artifact
     * back into the database.
     */
    public final class PersistentArtifact
    extends            Id
    {
        private Artifact           artifact;
        private ArtifactSerializer serializer;
        private Long               ttl;

        /**
         * Cronstructor to create a persistent artifact.
         * @param artifact   The living artifact.
         * @param serializer The serializer to store the artifact
         * after the operations.
         * @param ttl The time to life of the artifact.
         * @param id The database id of the artifact.
         */
        public PersistentArtifact(
            Artifact           artifact,
            ArtifactSerializer serializer,
            Long               ttl,
            int                id
        ) {
            super(id);
            this.artifact   = artifact;
            this.serializer = serializer;
            this.ttl        = ttl;
        }

        /**
         * Returns the wrapped living artifact.
         * @return the living artifact.
         */
        public Artifact getArtifact() {
            return artifact;
        }

        /**
         * Returns the serialized which is able to write a
         * modified artifact back into the database.
         * @return The serializer.
         */
        public ArtifactSerializer getSerializer() {
            return serializer;
        }

        /**
         * The time to life of the artifact.
         * @return The time to live.
         */
        public Long getTTL() {
            return ttl;
        }

        /**
         * Stores the living artifact back into the database.
         */
        public void store() {
            if (logger.isDebugEnabled()) {
                logger.debug("storing artifact id = " + getId());
            }
            Backend.this.store(this);
        }

        /**
         * Only touches the access time of the artifact.
         */
        public void touch() {
            if (logger.isDebugEnabled()) {
                logger.debug("touching artifact id = " + getId());
            }
            Backend.this.touch(this);
        }
    } // class ArtifactWithId

    /**
     * Default constructor
     */
    public Backend() {
    }

    /**
     * Constructor to create a backend with a link to the database cleaner.
     * @param cleaner The clean which periodically removes outdated
     * artifacts from the database.
     */
    public Backend(DatabaseCleaner cleaner) {
        this.cleaner = cleaner;
    }


    /**
     * Returns the singleton of this Backend.
     *
     * @return the backend.
     */
    public static synchronized Backend getInstance() {
        if (instance == null) {
            instance = new Backend();
        }

        return instance;
    }

    /**
     * Sets the factory lookup mechanism to decouple ArtifactDatabase
     * and Backend.
     * @param factoryLookup
     */
    public void setFactoryLookup(FactoryLookup factoryLookup) {
        this.factoryLookup = factoryLookup;
    }

    /**
     * Sets the database cleaner explicitly.
     * @param cleaner The database cleaner
     */
    public void setCleaner(DatabaseCleaner cleaner) {
        this.cleaner = cleaner;
    }

    /**
     * Returns a new unique identifier to external identify
     * the artifact across the system. This implementation
     * uses random UUIDs v4 to achieve this target.
     * @return the new identifier
     */
    public String newIdentifier() {
        // TODO: check database for collisions.
        return StringUtils.newUUID();
    }

    /**
     * Stores a new artifact into the database.
     * @param artifact The artifact to be stored
     * @param factory  The factory which build the artifact
     * @param ttl      The initial time to life of the artifact.
     * @return         A persistent wrapper around the living
     * artifact to be able to write modification later.
     * @throws Exception Thrown if something went wrong with the
     * storage process.
     */
    public PersistentArtifact storeInitially(
        Artifact        artifact,
        ArtifactFactory factory,
        Long            ttl
    )
    throws Exception
    {
        return new PersistentArtifact(
            artifact,
            factory.getSerializer(),
            ttl,
            insertDatabase(artifact, factory, ttl));
    }

    /**
     * Stores an artifact into database if it does not exist there.
     * If it exists there it is only updated.
     * @param artifact The artifact to store/update.
     * @param factory The factory which created the artifact.
     * @param ttl The initial time to live of the artifact.
     * @return A persistent version of the artifact to be able
     * to store a modification later.
     * @throws Exception Thrown if something went wrong during
     * storing/updating.
     */
    public PersistentArtifact storeOrReplace(
        Artifact        artifact,
        ArtifactFactory factory,
        Long            ttl
    )
    throws Exception
    {
        return new PersistentArtifact(
            artifact,
            factory.getSerializer(),
            ttl,
            storeOrReplaceDatabase(artifact, factory, ttl));
    }

    /**
     * Implementors of this interface are able to process the raw
     * artifact data from the database for loading.
     */
    public interface ArtifactLoader {

        /**
         * Creates a custom object from the raw artifact database data.
         * @param factory The factory that created this artifact.
         * @param ttl The current time to life of the artifact.
         * @param bytes The raw artifact bytes from the database.
         * @param id The database id of the artifact.
         * @return The custom object created by the implementation.
         */
        Object load(ArtifactFactory factory, Long ttl, byte [] bytes, int id);

    } // interface ArtifactLoader

    /**
     * Fetches an artifact from the database identified by the
     * given identifier.
     * @param identifer The identifier of the artifact.
     * @return A persistent wrapper around the found artifact
     * to be able to write back a modifaction later or null
     * if no artifact is found for this identifier.
     */
    public PersistentArtifact getArtifact(String identifer) {

        return (PersistentArtifact)loadArtifact(
            identifer,
            new ArtifactLoader() {

                public Object load(
                    ArtifactFactory factory,
                    Long            ttl,
                    byte []         bytes,
                    int             id
                ) {
                    ArtifactSerializer serializer = factory.getSerializer();

                    Artifact artifact = serializer.fromBytes(bytes);

                    return artifact == null
                        ? null
                        : new PersistentArtifact(artifact, serializer, ttl, id);
                }
            });
    }

    /**
     * More general loading mechanism for artifacts. The concrete
     * load processing is delegated to the given loader.
     * @param identifer The identifier of the artifact.
     * @param loader The loader which processes the raw database data.
     * @return The object created by the loader.
     */
    public Object loadArtifact(String identifer, ArtifactLoader loader) {

        if (!StringUtils.checkUUID(identifer)) {
            return null;
        }

        Connection        connection  = null;
        PreparedStatement stmnt_load  = null;
        ResultSet         load_result = null;

        DataSource dataSource = DBConnection.getDataSource();
        try {
            connection = dataSource.getConnection();
            stmnt_load = connection.prepareStatement(SQL_LOAD_BY_GID);
            stmnt_load.setString(1, identifer);

            load_result = stmnt_load.executeQuery();

            if (!load_result.next()) {
                return null;
            }

            int  id   = load_result.getInt(1);
            long ttlX = load_result.getLong(3);

            Long ttl = load_result.wasNull() ? null : Long.valueOf(ttlX);

            if (ttl != null) { // real time to life
                long last_access = load_result.getTimestamp(2).getTime();
                if (last_access + ttlX < System.currentTimeMillis()) {
                    artifactOutdated(id);
                    return null;
                }
            }

            String factoryName = load_result.getString(4);

            if (factoryLookup == null) {
                logger.error("factory lookup == null");
                return null;
            }

            ArtifactFactory factory = factoryLookup
                .getArtifactFactory(factoryName);

            if (factory == null) {
                logger.error("factory '" + factoryName + "' not found");
                return null;
            }

            byte [] bytes = load_result.getBytes(5);

            return loader.load(factory, ttl, bytes, id);
        }
        catch (SQLException sqle) {
            logger.error(sqle.getLocalizedMessage(), sqle);
        }
        finally {
            if (load_result != null) {
                try { load_result.close(); }
                catch (SQLException sqle) {}
            }
            if (stmnt_load != null) {
                try { load_result.close(); }
                catch (SQLException sqle) {}
            }
            if (connection != null) {
                try { connection.close(); }
                catch (SQLException sqle) {}
            }
        }
        return null;
    }

    /**
     * Called if the load mechanism found an outdated artifact.
     * It  wakes up the database cleaner.
     * @param id The id of the outdated artifact.
     */
    protected void artifactOutdated(int id) {
        if (logger.isDebugEnabled()) {
            logger.info("artifactOutdated: id = " + id);
        }
        if (cleaner != null) {
            cleaner.wakeup();
        }
    }

    public Artifact reviveArtifact(String factoryName, byte [] bytes) {
        if (factoryLookup == null) {
            logger.error("reviveArtifact: factory lookup == null");
            return null;
        }
        ArtifactFactory factory = factoryLookup
            .getArtifactFactory(factoryName);

        if (factory == null) {
            logger.error(
                "reviveArtifact: no factory '" + factoryName + "' found");
            return null;
        }

        ArtifactSerializer serializer = factory.getSerializer();

        return serializer.fromBytes(bytes);
    }

    /**
     * Internal method to store/replace an artifact inside the database.
     * If an artifact with the given identifier does not exists it is
     * created else only the content data is updated.
     * @param artifact The artifact to be store/update inside the database.
     * @param factory The factory that created the artifact.
     * @param ttl The initial time to life of the artifact.
     * @return The database id of the stored/updated artifact.
     */
    protected int storeOrReplaceDatabase(
        Artifact        artifact,
        ArtifactFactory factory,
        Long            ttl
    ) {
        String uuid = artifact.identifier();

        if (!StringUtils.checkUUID(uuid)) {
            throw new RuntimeException("No valid UUID");
        }

        Connection        connection = null;
        PreparedStatement stmnt      = null;
        ResultSet         result     = null;

        DataSource dataSource = DBConnection.getDataSource();
        try {
            connection = dataSource.getConnection();
            try {
                connection.setAutoCommit(false);

                stmnt = connection.prepareStatement(SQL_GET_ID);

                stmnt.setString(1, uuid);
                result = stmnt.executeQuery();

                Integer ID = result.next()
                    ? Integer.valueOf(result.getInt(1))
                    : null;

                result.close(); result = null;
                stmnt.close();  stmnt  = null;

                if (ID != null) { // already in database
                    int id = ID.intValue();

                    stmnt = connection.prepareStatement(SQL_REPLACE);

                    if (ttl == null) {
                        stmnt.setNull(1, Types.BIGINT);
                    }
                    else {
                        stmnt.setLong(1, ttl.longValue());
                    }

                    stmnt.setString(2, factory.getName());
                    stmnt.setBytes(
                        3,
                        factory.getSerializer().toBytes(artifact));
                    stmnt.setInt(4, id);

                    stmnt.execute();
                    connection.commit();
                    return id;
                }

                stmnt = connection.prepareStatement(SQL_NEXT_ID);
                result = stmnt.executeQuery();

                if (!result.next()) {
                    throw new RuntimeException("No id generated");
                }

                int id = result.getInt(1);

                result.close(); result = null;
                stmnt.close();  stmnt  = null;

                stmnt = connection.prepareStatement(SQL_INSERT);

                stmnt.setInt(1, id);
                stmnt.setString(2, uuid);
                if (ttl == null) {
                    stmnt.setNull(3, Types.BIGINT);
                }
                else {
                    stmnt.setLong(3, ttl.longValue());
                }

                stmnt.setString(4, factory.getName());

                stmnt.setBytes(
                    5,
                    factory.getSerializer().toBytes(artifact));

                stmnt.execute();
                connection.commit();
                return id;
            }
            catch (SQLException sqle) {
                connection.rollback();
                throw sqle;
            }
        }
        catch (SQLException sqle) {
            logger.error(sqle.getLocalizedMessage(), sqle);
        }
        finally {
            if (result != null) {
                try { result.close(); }
                catch (SQLException sqle) {}
            }
            if (stmnt != null) {
                try { stmnt.close(); }
                catch (SQLException sqle) {}
            }
            if (connection != null) {
                try { connection.close(); }
                catch (SQLException sqle) {}
            }
        }
        throw new RuntimeException("failed insert artifact into database");
    }

    /**
     * Internal method to store an artifact inside the database.
     * @param artifact The artifact to be stored.
     * @param factory The factory which created the artifact.
     * @param ttl The initial time to live of the artifact.
     * @return The database id of the stored artifact.
     */
    protected int insertDatabase(
        Artifact        artifact,
        ArtifactFactory factory,
        Long            ttl
    ) {
        String uuid = artifact.identifier();

        Connection        connection    = null;
        PreparedStatement stmnt_next_id = null;
        PreparedStatement stmnt_insert  = null;
        ResultSet         res_id        = null;

        DataSource dataSource = DBConnection.getDataSource();
        try {
            connection = dataSource.getConnection();
            try {
                connection.setAutoCommit(false);

                stmnt_next_id = connection.prepareStatement(SQL_NEXT_ID);
                stmnt_insert  = connection.prepareStatement(SQL_INSERT);

                res_id = stmnt_next_id.executeQuery();

                if (!res_id.next()) {
                    throw new RuntimeException("No id generated");
                }

                int id = res_id.getInt(1);

                stmnt_insert.setInt(1, id);
                stmnt_insert.setString(2, uuid);
                if (ttl == null) {
                    stmnt_insert.setNull(3, Types.BIGINT);
                }
                else {
                    stmnt_insert.setLong(3, ttl.longValue());
                }

                stmnt_insert.setString(4, factory.getName());

                stmnt_insert.setBytes(
                    5,
                    factory.getSerializer().toBytes(artifact));

                stmnt_insert.execute();

                connection.commit();

                return id;
            }
            catch (SQLException sqle) {
                connection.rollback();
                throw sqle;
            }
        }
        catch (SQLException sqle) {
            logger.error(sqle.getLocalizedMessage(), sqle);
        }
        finally {
            if (res_id != null) {
                try { res_id.close(); }
                catch (SQLException sqle) {}
            }
            if (stmnt_insert != null) {
                try { stmnt_insert.close(); }
                catch (SQLException sqle) {}
            }
            if (stmnt_next_id != null) {
                try { stmnt_next_id.close(); }
                catch (SQLException sqle) {}
            }
            if (connection != null) {
                try { connection.close(); }
                catch (SQLException sqle) {}
            }
        }
        throw new RuntimeException("failed insert artifact into database");
    }

    /**
     * Touches the access timestamp of a given artifact to prevent
     * that it will be removed from the database by the database cleaner.
     * @param artifact The persistent wrapper around the living artifact.
     */
    public void touch(PersistentArtifact artifact) {

        try {
            Connection        connection  = null;
            PreparedStatement stmnt_touch = null;
            DataSource        dataSource  = DBConnection.getDataSource();
            try {
                connection = dataSource.getConnection();
                try {
                    connection.setAutoCommit(false);
                    stmnt_touch = connection.prepareStatement(SQL_TOUCH);
                    stmnt_touch.setInt(1, artifact.getId());
                    stmnt_touch.execute();
                    connection.commit();
                }
                catch (SQLException sqle) {
                    connection.rollback();
                }
            }
            catch (SQLException sqle) {
                logger.error(sqle.getLocalizedMessage(), sqle);
            }
            finally {
                if (stmnt_touch != null) {
                    try { stmnt_touch.close(); }
                    catch (SQLException sqle) {}
                }
                if (connection != null) {
                    try { connection.close(); }
                    catch (SQLException sqle) {}
                }
            }
        }
        catch (Exception e) {
            logger.error(e.getLocalizedMessage(), e);
        }
    }

    /**
     * Writes modification of an artifact back to the database.
     * @param artifact The persistent wrapper around a living
     * artifact.
     */
    public void store(PersistentArtifact artifact) {

        try {
            Connection        connection   = null;
            PreparedStatement stmnt_update = null;
            DataSource        dataSource   = DBConnection.getDataSource();
            try {
                connection = dataSource.getConnection();
                try {
                    connection.setAutoCommit(false);
                    stmnt_update = connection.prepareStatement(SQL_UPDATE);
                    stmnt_update.setInt(2, artifact.getId());

                    byte [] bytes = artifact
                        .getSerializer()
                        .toBytes(artifact.getArtifact());

                    stmnt_update.setBytes(1, bytes);
                    stmnt_update.execute();
                    connection.commit();
                }
                catch (SQLException sqle) {
                    connection.rollback();
                }
            }
            catch (SQLException sqle) {
                logger.error(sqle.getLocalizedMessage(), sqle);
            }
            finally {
                if (stmnt_update != null) {
                    try { stmnt_update.close(); }
                    catch (SQLException sqle) {}
                }
                if (connection != null) {
                    try { connection.close(); }
                    catch (SQLException sqle) {}
                }
            }
        }
        catch (Exception e) {
            logger.error(e.getLocalizedMessage(), e);
        }
    }

    public User createUser(
        String      name, 
        Document    role,
        UserFactory factory,
        Object      context
    ) {
        Connection        connection    = null;
        PreparedStatement stmnt_next_id = null;
        PreparedStatement stmnt_insert  = null;
        ResultSet         result        = null;

        String identifier = newIdentifier();

        DataSource dataSource = DBConnection.getDataSource();
        try {
            connection = dataSource.getConnection();
            try {
                connection.setAutoCommit(false);

                stmnt_next_id = connection.prepareStatement(SQL_USERS_NEXT_ID);
                stmnt_insert  = connection.prepareStatement(SQL_USERS_INSERT);

                result = stmnt_next_id.executeQuery();

                if (!result.next()) {
                    throw new RuntimeException("No id generated");
                }

                int id = result.getInt(1);

                stmnt_insert.setInt(1, id);
                stmnt_insert.setString(2, identifier);
                stmnt_insert.setString(3, name);

                byte [] roleData = role == null
                    ? null
                    : XMLUtils.toByteArray(role);

                if (roleData == null) {
                    stmnt_insert.setNull(4, Types.BIGINT);
                }
                else {
                    stmnt_insert.setBytes(4, roleData);
                }

                stmnt_insert.execute();

                connection.commit();

                return factory.createUser(identifier, name, role, context);
            }
            catch (SQLException sqle) {
                connection.rollback();
                throw sqle;
            }
        }
        catch (SQLException sqle) {
            logger.error(sqle.getLocalizedMessage(), sqle);
        }
        finally {
            if (result != null) {
                try { result.close(); }
                catch (SQLException sqle) {}
            }
            if (stmnt_insert != null) {
                try { stmnt_insert.close(); }
                catch (SQLException sqle) {}
            }
            if (stmnt_next_id != null) {
                try { stmnt_next_id.close(); }
                catch (SQLException sqle) {}
            }
            if (connection != null) {
                try { connection.close(); }
                catch (SQLException sqle) {}
            }
        }
        return null;
    }

    public boolean deleteUser(String identifier) {

        Connection        conn   = null;
        ResultSet         result = null;
        PreparedStatement stmnt  = null;

        if (!StringUtils.checkUUID(identifier)) {
            return false;
        }

        DataSource dataSource = DBConnection.getDataSource();
        try {
            conn = dataSource.getConnection();
            try {
                conn.setAutoCommit(false);

                // fetch user id
                stmnt = conn.prepareStatement(SQL_USERS_SELECT_ID_BY_GID);

                stmnt.setString(1, identifier);
                result = stmnt.executeQuery();

                if (!result.next()) { // No such user
                    return false;
                }

                int id = result.getInt(1);

                result.close(); result = null;
                stmnt.close();  stmnt  = null;

                // outdate the artifacts exclusively used by the user

                stmnt = conn.prepareStatement(SQL_OUTDATE_ARTIFACTS_USER);
                stmnt.setInt(1, id);
                stmnt.setInt(2, id);
                stmnt.execute();

                stmnt.close(); stmnt = null;

                // delete the collection items of the user

                stmnt = conn.prepareStatement(SQL_DELETE_USER_COLLECTION_ITEMS);
                stmnt.setInt(1, id);
                stmnt.execute();
                stmnt.close(); stmnt = null;

                // delete the collections of the user

                stmnt = conn.prepareStatement(SQL_USERS_DELETE_COLLECTIONS);
                stmnt.setInt(1, id);
                stmnt.execute();
                stmnt.close(); stmnt = null;

                // delete the user

                stmnt = conn.prepareStatement(SQL_USERS_DELETE_ID);
                stmnt.setInt(1, id);
                stmnt.execute();

                conn.commit();

                return true;
            }
            catch (SQLException sqle) {
                conn.rollback();
                throw sqle;
            }
        }
        catch (SQLException sqle) {
            logger.error(sqle.getLocalizedMessage(), sqle);
        }
        finally {
            if (result != null) {
                try { result.close(); }
                catch (SQLException sqle) {}
            }
            if (stmnt != null) {
                try { stmnt.close(); }
                catch (SQLException sqle) {}
            }
            if (conn != null) {
                try { conn.close(); }
                catch (SQLException sqle) {}
            }
        }

        return false;
    }

    public User getUser(
        String      identifier, 
        UserFactory factory,
        Object      context
    ) {
        Connection        conn   = null;
        ResultSet         result = null;
        PreparedStatement stmnt  = null;

        if (!StringUtils.checkUUID(identifier)) {
            logger.debug("Invalid UUID: '" + identifier + "'");
            return null;
        }

        DataSource dataSource = DBConnection.getDataSource();
        try {
            conn = dataSource.getConnection();
            stmnt = conn.prepareStatement(SQL_USERS_SELECT_GID);
            stmnt.setString(1, identifier);
            result = stmnt.executeQuery();
            if (!result.next()) { // no such user
                return null;
            }
            // omit id
            String  name     = result.getString(2);
            byte [] roleData = result.getBytes(3);

            Document role = XMLUtils.fromByteArray(roleData);

            return factory.createUser(identifier, name, role, context);
        }
        catch (SQLException sqle) {
            logger.error(sqle.getLocalizedMessage(), sqle);
        }
        finally {
            if (result != null) {
                try { result.close(); }
                catch (SQLException sqle) {}
            }
            if (stmnt != null) {
                try { stmnt.close(); }
                catch (SQLException sqle) {}
            }
            if (conn != null) {
                try { conn.close(); }
                catch (SQLException sqle) {}
            }
        }

        return null;
    }

    public User [] getUsers(UserFactory factory, Object context) {
        Connection        conn   = null;
        ResultSet         result = null;
        PreparedStatement stmnt  = null;

        DataSource dataSource = DBConnection.getDataSource();
        try {
            conn   = dataSource.getConnection();
            stmnt  = conn.prepareStatement(SQL_USERS_SELECT_ALL);
            result = stmnt.executeQuery();

            ArrayList<User> users = new ArrayList<User>();

            while (result.next()) {
                // omit id
                String  identifier = result.getString(2);
                String  name       = result.getString(3);
                byte [] roleData   = result.getBytes(4);

                Document role = XMLUtils.fromByteArray(roleData);
                User     user =
                    factory.createUser(identifier, name, role, context);
                users.add(user);
            }

            return users.toArray(new User[users.size()]);
        }
        catch (SQLException sqle) {
            logger.error(sqle.getLocalizedMessage(), sqle);
        }
        finally {
            if (result != null) {
                try { result.close(); }
                catch (SQLException sqle) {}
            }
            if (stmnt != null) {
                try { stmnt.close(); }
                catch (SQLException sqle) {}
            }
            if (conn != null) {
                try { conn.close(); }
                catch (SQLException sqle) {}
            }
        }

        return null;
    }

    public ArtifactCollection createCollection(
        String                    ownerIdentifier, 
        String                    name,
        ArtifactCollectionFactory factory,
        Document                  data,
        Object                    context
    ) {
        if (name == null) {
            logger.debug("Name is null");
            return null;
        }

        if (!StringUtils.checkUUID(ownerIdentifier)) {
            logger.debug("Invalid owner id: '" + ownerIdentifier + "'");
            return null;
        }

        Connection        conn   = null;
        ResultSet         result = null;
        PreparedStatement stmnt  = null;

        DataSource dataSource = DBConnection.getDataSource();
        try {
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            try {
                // fetch owner id
                stmnt = conn.prepareStatement(SQL_USERS_SELECT_ID_BY_GID);
                stmnt.setString(1, ownerIdentifier);
                result = stmnt.executeQuery();

                if (!result.next()) { // no such user
                    return null;
                }

                int ownerId = result.getInt(1);
                result.close(); result = null;
                stmnt.close();  stmnt  = null;

                // fetch new collection seq number.
                stmnt = conn.prepareStatement(SQL_COLLECTIONS_NEXT_ID);
                result = stmnt.executeQuery();

                if (!result.next()) { // no identifier generated
                    return null;
                }

                int id = result.getInt(1);
                result.close(); result = null;
                stmnt.close();  stmnt  = null;

                String identifier = newIdentifier();

                stmnt = conn.prepareStatement(SQL_COLLECTIONS_INSERT);

                stmnt.setInt(1, id);
                stmnt.setString(2, identifier);
                stmnt.setString(3, name);
                stmnt.setInt(4, ownerId);

                // XXX: A bit odd: we don't have a collection, yet.
                Long ttl = factory.timeToLiveUntouched(null, context);

                if (ttl == null) {
                    stmnt.setNull(5, Types.BIGINT);
                }
                else {
                    stmnt.setLong(5, ttl);
                }

                stmnt.execute();
                conn.commit();

                return factory.createCollection(
                    identifier, name, data, context);
            }
            catch (SQLException sqle) {
                conn.rollback();
                throw sqle;
            }
        }
        catch (SQLException sqle) {
            logger.error(sqle.getLocalizedMessage(), sqle);
        }
        finally {
            if (result != null) {
                try { result.close(); }
                catch (SQLException sqle) {}
            }
            if (stmnt != null) {
                try { stmnt.close(); }
                catch (SQLException sqle) {}
            }
            if (conn != null) {
                try { conn.close(); }
                catch (SQLException sqle) {}
            }
        }
        return null;
    }

    public ArtifactCollection [] listCollections(
        String                    ownerIdentifier,
        Document                  data,
        ArtifactCollectionFactory collectionFactory,
        UserFactory               userFactory,
        Object                    context
    ) {
        if (ownerIdentifier != null 
        && !StringUtils.checkUUID(ownerIdentifier)) {
            logger.debug("Invalid owner id: '" + ownerIdentifier + "'");
            return null;
        }

        Connection        conn   = null;
        ResultSet         result = null;
        PreparedStatement stmnt  = null;

        DataSource dataSource = DBConnection.getDataSource();
        try {
            ArrayList<ArtifactCollection> collections =
                new ArrayList<ArtifactCollection>();
            conn = dataSource.getConnection();

            if (ownerIdentifier != null) {
                stmnt = conn.prepareStatement(SQL_COLLECTIONS_SELECT_USER);
                stmnt.setString(1, ownerIdentifier);
            }
            else {
                stmnt = conn.prepareStatement(SQL_COLLECTIONS_SELECT_ALL);
            }

            result = stmnt.executeQuery();

            while (result.next()) {
                String collectionIdentifier = result.getString(1);
                String collectionName       = result.getString(2);
                long   creationTime         = result.getLong(3);
                String userIdentifier       = result.getString(4);

                ArtifactCollection collection =
                    collectionFactory.createCollection(
                        collectionIdentifier,
                        collectionName,
                        data,
                        context);

                if (userIdentifier != null) {
                    collection.setUser(new LazyBackendUser(
                        userIdentifier, userFactory, this, context));
                }

                collections.add(collection);
            }

            return collections.toArray(
                new ArtifactCollection[collections.size()]);
        }
        catch (SQLException sqle) {
            logger.error(sqle.getLocalizedMessage(), sqle);
        }
        finally {
            if (result != null) {
                try { result.close(); }
                catch (SQLException sqle) {}
            }
            if (stmnt != null) {
                try { stmnt.close(); }
                catch (SQLException sqle) {}
            }
            if (conn != null) {
                try { conn.close(); }
                catch (SQLException sqle) {}
            }
        }
        return null;
    }

    public boolean deleteCollection(String collectionId) {
        // TODO: Implement me!
        return false;
    }

    public Document getCollectionAttribute(
        String collectionId,
        String artifactId
    ) {
        // TODO: Implement me!
        return null;
    }

    public boolean setCollectionAttribute(
        String collectionId, 
        String artifactId,
        Document attribute
    ) {
        // TODO: Implement me!
        return false;
    }

    public boolean addCollectionArtifact(
        String collectionId,
        String artifactId
    ) {
        // TODO: Implement me!
        return false;
    }

    public boolean removeCollectionArtifact(
        String collectionId,
        String artifactId
    ) {
        // TODO: Implement me!
        return false;
    }

    public ArtifactCollection [] listCollectionArtifacts(String collectionId) {
        // TODO: Implement me!
        return null;
    }

}
// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :