package de.intevation.flys.artifacts.datacage;

import java.util.Collection;
import java.util.List;

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

import de.intevation.artifacts.GlobalContext;

import de.intevation.artifactdatabase.db.SQL;
import de.intevation.artifactdatabase.db.SQLExecutor;

import de.intevation.artifactdatabase.LifetimeListener;

import de.intevation.artifactdatabase.data.StateData;

import de.intevation.artifactdatabase.state.Output;
import de.intevation.artifactdatabase.state.Facet;

import de.intevation.artifacts.Artifact;
import de.intevation.artifacts.ArtifactDatabase;
import de.intevation.artifacts.ArtifactDatabaseException;

import de.intevation.flys.artifacts.FLYSArtifact;

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

import org.apache.log4j.Logger;

import org.w3c.dom.Document;

public class Datacage
implements   LifetimeListener
{
    private static Logger log = Logger.getLogger(Datacage.class);

    public static final String ARTEFACT_DATABASE_KEY =
        "global.artifact.database";

    private String SQL_DELETE_ALL_USERS      = "delete.all.users";
    private String SQL_DELETE_ALL_ARTIFACTS  = "delete.all.artifacts";
    private String SQL_USER_ID_NEXTVAL       = "user.id.nextval";
    private String SQL_USER_BY_GID           = "user.by.gid";
    private String SQL_INSERT_USER           = "insert.user";
    private String SQL_COLLECTION_BY_GID     = "collection.by.gid";
    private String SQL_COLLECTION_ID_NEXTVAL = "collection.id.nextval";
    private String SQL_INSERT_COLLECTION     = "insert.collection";
    private String SQL_ARTIFACT_BY_GID       = "artifact.by.gid";
    private String SQL_COLLECTION_ITEM_ID_NEXTVAL =
        "collection.item.id.nextval";
    private String SQL_INSERT_COLLECTION_ITEM = "insert.collection.item";
    private String SQL_ARTIFACT_ID_NEXTVAL    = "artifact.id.nextval";
    private String SQL_INSERT_ARTIFACT        = "insert.artifact";
    private String SQL_ARTIFACT_DATA_ID_NEXTVAL = "artifact.data.id.nextval";
    private String SQL_INSERT_ARTIFACT_DATA   = "insert.artifact.data";
    private String SQL_OUT_ID_NEXTVALUE       = "out.id.nextval";
    private String SQL_INSERT_OUT             = "insert.out";
    private String SQL_FACET_ID_NEXTVAL       = "facet.id.nextval";
    private String SQL_INSERT_FACET           = "insert.facet";

    protected SQLExecutor sqlExecutor;

    public class InitialScan 
    implements   ArtifactDatabase.ArtifactLoadedCallback
    {
        protected LRUCache<String, Integer> users;
        protected LRUCache<String, Integer> collections;
        protected LRUCache<String, Integer> artifacts;

        protected GlobalContext context;

        public InitialScan() {
            users       = new LRUCache<String, Integer>();
            collections = new LRUCache<String, Integer>();
            artifacts   = new LRUCache<String, Integer>();
        }

        public InitialScan(GlobalContext context) {
            this();
            this.context = context;
        }

        @Override
        public void artifactLoaded(
            String   userId,
            String   collectionId,
            String   artifactId,
            Artifact artifact
        ) {
            if (!(artifact instanceof FLYSArtifact)) {
                log.warn("ignoring none FLYS artifacts");
                return;
            }

            FLYSArtifact flysArtifact = (FLYSArtifact)artifact;

            Integer uId = getUserId(userId);
            // TODO: We need the name of the collection
            Integer cId = getCollectionId(collectionId, uId, "XXX");

            storeArtifact(artifactId, cId, flysArtifact);
        }

        protected Integer getId(
            LRUCache<String, Integer> cache,
            final String              idString,
            final String              selectById
        ) {
            Integer id = cache.get(idString);
            if (id != null) {
                return id;
            }

            final Integer [] res = new Integer[1];

            SQLExecutor.Instance exec = sqlExecutor.new Instance() {
                @Override
                public boolean doIt() throws SQLException {
                    prepareStatement(selectById);
                    stmnt.setString(1, idString);
                    result = stmnt.executeQuery();
                    if (!result.next()) {
                        return false;
                    }
                    res[0] = result.getInt(1);
                    return true;
                }
            };

            if (exec.runRead()) {
                cache.put(idString, res[0]);
                return res[0];
            }

            return null;
        }

        protected void storeArtifact(
            final String       artifactId,
            Integer            collectionId,
            final FLYSArtifact artifact
        ) {
            Integer aId = getId(artifacts, artifactId, SQL_ARTIFACT_BY_GID);

            if (aId != null) {
                // We've already stored it. Just create the collection item.
                storeCollectionItem(collectionId, aId);
                return;
            }
            // We need to write it to database

            final Integer [] res = new Integer[1];

            SQLExecutor.Instance exec = sqlExecutor.new Instance() {
                @Override
                public boolean doIt() throws SQLException {
                    prepareStatement(SQL_ARTIFACT_ID_NEXTVAL);
                    result = stmnt.executeQuery();
                    if (!result.next()) {
                        return false;
                    }
                    res[0] = result.getInt(1);
                    reset();
                    prepareStatement(SQL_INSERT_ARTIFACT);
                    stmnt.setInt   (1, res[0]);
                    stmnt.setString(2, artifactId);
                    stmnt.setString(3, artifact.getCurrentStateId());
                    stmnt.execute();
                    conn.commit();
                    return true;
                }
            };

            if (!exec.runWrite()) {
                log.error("storing of artifact failed.");
                return;
            }

            artifacts.put(artifactId, aId = res[0]);

            storeCollectionItem(collectionId, aId);

            storeData(aId, artifact);

            storeOuts(aId, artifact);
        }

        protected void storeOuts(
            final int          artifactId,
            final FLYSArtifact artifact
        ) {
            final List<Output> outs = artifact.getCurrentOutputs(context);

            if (outs.isEmpty()) {
                return;
            }

            final int [] outIds = new int[outs.size()];

            SQLExecutor.Instance exec = sqlExecutor.new Instance() {
                @Override
                public boolean doIt() throws SQLException {
                    prepareStatement(SQL_OUT_ID_NEXTVALUE);
                    for (int i = 0; i < outIds.length; ++i) {
                        result = stmnt.executeQuery();
                        if (!result.next()) {
                            log.error("generation of out ids failed");
                            return false;
                        }
                        outIds[i] = result.getInt(1);
                        result.close(); result = null;
                    }
                    reset();
                    prepareStatement(SQL_INSERT_OUT);
                    for (int i = 0; i < outIds.length; ++i) {
                        Output out = outs.get(i);
                        stmnt.setInt(1, outIds[i]);
                        stmnt.setInt(2, artifactId);
                        stmnt.setString(3, out.getName());
                        setString(stmnt, 4, out.getDescription());
                        setString(stmnt, 5, out.getType());
                        stmnt.execute();
                    }
                    conn.commit();
                    return true;
                }
            };

            if (!exec.runWrite()) {
                log.error("storing artifact outs failed");
                return;
            }

            final int FACETS = numFacets(outs);

            if (FACETS == 0) {
                return;
            }

            exec = sqlExecutor.new Instance() {
                @Override
                public boolean doIt() throws SQLException {
                    int [] facetIds = new int[FACETS];
                    prepareStatement(SQL_FACET_ID_NEXTVAL);
                    for (int i = 0; i < facetIds.length; ++i) {
                        result = stmnt.executeQuery();
                        if (!result.next()) {
                            log.error("generation of facet ids failed");
                            return false;
                        }
                        facetIds[i] = result.getInt(1);
                        result.close(); result = null;
                    }
                    reset();
                    prepareStatement(SQL_INSERT_FACET);
                    int index = 0;
                    for (int i = 0, N = outs.size(); i < N; ++i) {
                        Output out = outs.get(i);
                        int outId = outIds[i];
                        for (Facet facet: out.getFacets()) {
                            stmnt.setInt(1, facetIds[index]);
                            stmnt.setInt(2, outId);
                            stmnt.setString(3, facet.getName());
                            stmnt.setInt(4, facet.getIndex());
                            stmnt.setString(5, "XXX"); // TODO: handle states
                            setString(stmnt, 6, facet.getDescription());
                            stmnt.execute();
                            ++index;
                        }
                    }
                    conn.commit();
                    return true;
                }
            };

            if (!exec.runWrite()) {
                log.error("storing facets failed");
            }
        }

        protected void storeData(
            final int     artifactId,
            FLYSArtifact  artifact
        ) {
            final Collection<StateData> data = artifact.getAllData();

            if (data.isEmpty()) {
                return;
            }

            SQLExecutor.Instance exec = sqlExecutor.new Instance() {
                @Override
                public boolean doIt() throws SQLException {
                    int [] ids = new int[data.size()];
                    prepareStatement(SQL_ARTIFACT_DATA_ID_NEXTVAL);

                    for (int i = 0; i < ids.length; ++i) {
                        result = stmnt.executeQuery();
                        if (!result.next()) {
                            log.error("generating id for artifact data failed");
                            return false;
                        }
                        ids[i] = result.getInt(1);
                        result.close(); result = null;
                    }
                    reset();
                    prepareStatement(SQL_INSERT_ARTIFACT_DATA);

                    int i = 0;
                    for (StateData sd: data) {
                        int id = ids[i++];
                        stmnt.setInt(1, id);
                        stmnt.setInt(2, artifactId);
                        // XXX: Where come the nulls from?
                        String type = sd.getType();
                        if (type == null) type = "String";
                        stmnt.setString(3, type);
                        stmnt.setString(4, sd.getName());
                        setString(stmnt, 5, sd.getValue());
                        stmnt.execute();
                    }

                    conn.commit();
                    return true;
                }
            };

            if (!exec.runWrite()) {
                log.error("storing artifact data failed");
            }
        }

        protected void storeCollectionItem(
            final Integer collectionId,
            final Integer artifactId
        ) {
            SQLExecutor.Instance exec = sqlExecutor.new Instance() {
                @Override
                public boolean doIt() throws SQLException {
                    prepareStatement(SQL_COLLECTION_ITEM_ID_NEXTVAL);
                    result = stmnt.executeQuery();
                    if (!result.next()) {
                        return false;
                    }
                    int ciId = result.getInt(1);
                    reset();
                    prepareStatement(SQL_INSERT_COLLECTION_ITEM);
                    stmnt.setInt(1, ciId);
                    stmnt.setInt(2, collectionId);
                    stmnt.setInt(3, artifactId);
                    stmnt.execute();
                    conn.commit();
                    return true;
                }
            };

            if (!exec.runWrite()) {
                log.error("storing of collection item failed.");
            }
        }

        protected Integer getCollectionId(
            final String  collectionId,
            final Integer ownerId,
            final String  collectionName
        ) {
            Integer c = getId(collections, collectionId, SQL_COLLECTION_BY_GID);

            if (c != null) {
                return c;
            }

            final Integer [] res = new Integer[1];

            SQLExecutor.Instance exec = sqlExecutor.new Instance() {
                @Override
                public boolean doIt() throws SQLException {
                    prepareStatement(SQL_COLLECTION_ID_NEXTVAL);
                    result = stmnt.executeQuery();
                    if (!result.next()) {
                        return false;
                    }
                    res[0] = result.getInt(1);
                    reset();
                    prepareStatement(SQL_INSERT_COLLECTION);
                    stmnt.setInt   (1, res[0]);
                    stmnt.setString(2, collectionId);
                    stmnt.setInt   (3, ownerId);
                    stmnt.setString(4, collectionName);
                    stmnt.execute();
                    conn.commit();
                    return true;
                }
            };

            if (exec.runWrite()) {
                collections.put(collectionId, res[0]);
                return res[0];
            }

            return null;
        }

        protected Integer getUserId(final String userId) {

            Integer u = getId(users, userId, SQL_USER_BY_GID);

            if (u != null) {
                return u;
            }

            final Integer [] res = new Integer[1];

            SQLExecutor.Instance exec = sqlExecutor.new Instance() {
                @Override
                public boolean doIt() throws SQLException {
                    prepareStatement(SQL_USER_ID_NEXTVAL);
                    result = stmnt.executeQuery();
                    if (!result.next()) {
                        return false;
                    }
                    res[0] = result.getInt(1);
                    reset();
                    prepareStatement(SQL_INSERT_USER);
                    stmnt.setInt   (1, res[0]);
                    stmnt.setString(2, userId);
                    stmnt.execute();
                    conn.commit();
                    return true;
                }
            };

            if (exec.runWrite()) {
                users.put(userId, res[0]);
                return res[0];
            }

            return null;
        }

        public boolean scan(ArtifactDatabase adb) {
            log.debug("scan");
            try {
                adb.loadAllArtifacts(this);
            }
            catch (ArtifactDatabaseException ade) {
                log.error(ade);
                return false;
            }
            return true;
        }
    } // class InitialScan


    public Datacage() {
    }

    @Override
    public void setup(Document document) {
        log.debug("setup");
        DBConfig config = DBConfig.getInstance();
        setupSQL(config.getSQL());
        sqlExecutor = new SQLExecutor(config.getDBConnection());
    }

    protected void setupSQL(SQL sql) {
        SQL_DELETE_ALL_USERS      = sql.get(SQL_DELETE_ALL_USERS);
        SQL_DELETE_ALL_ARTIFACTS  = sql.get(SQL_DELETE_ALL_ARTIFACTS);
        SQL_USER_ID_NEXTVAL       = sql.get(SQL_USER_ID_NEXTVAL);
        SQL_USER_BY_GID           = sql.get(SQL_USER_BY_GID);
        SQL_INSERT_USER           = sql.get(SQL_INSERT_USER);
        SQL_COLLECTION_BY_GID     = sql.get(SQL_COLLECTION_BY_GID);
        SQL_COLLECTION_ID_NEXTVAL = sql.get(SQL_COLLECTION_ID_NEXTVAL);
        SQL_INSERT_COLLECTION     = sql.get(SQL_INSERT_COLLECTION);
        SQL_ARTIFACT_BY_GID       = sql.get(SQL_ARTIFACT_BY_GID);
        SQL_COLLECTION_ITEM_ID_NEXTVAL =
            sql.get(SQL_COLLECTION_ITEM_ID_NEXTVAL);
        SQL_INSERT_COLLECTION_ITEM =
            sql.get(SQL_INSERT_COLLECTION_ITEM);
        SQL_ARTIFACT_ID_NEXTVAL = sql.get(SQL_ARTIFACT_ID_NEXTVAL);
        SQL_INSERT_ARTIFACT     = sql.get(SQL_INSERT_ARTIFACT);
        SQL_ARTIFACT_DATA_ID_NEXTVAL = sql.get(SQL_ARTIFACT_DATA_ID_NEXTVAL);
        SQL_INSERT_ARTIFACT_DATA = sql.get(SQL_INSERT_ARTIFACT_DATA);
        SQL_OUT_ID_NEXTVALUE     = sql.get(SQL_OUT_ID_NEXTVALUE);
        SQL_INSERT_OUT           = sql.get(SQL_INSERT_OUT);
        SQL_FACET_ID_NEXTVAL     = sql.get(SQL_FACET_ID_NEXTVAL);
        SQL_INSERT_FACET         = sql.get(SQL_INSERT_FACET);
    }

    protected static final int numFacets(List<Output> outs) {
        int sum = 0;
        for (Output out: outs) {
            sum += out.getFacets().size();
        }
        return sum;
    }

    protected static final void setString(
        PreparedStatement stmnt, 
        int               index,
        Object            value
    ) 
    throws SQLException
    {
        if (value == null) {
            stmnt.setNull(index, Types.VARCHAR);
        }
        else {
            stmnt.setString(index, value.toString());
        }
    }

    @Override
    public void systemUp(GlobalContext context) {
        log.debug("systemUp entered");
        initialScan(context);
        log.debug("systemUp leaved");
    }

    protected void initialScan(GlobalContext context) {
        log.debug("initialScan");

        Object adbObject = context.get(ARTEFACT_DATABASE_KEY);

        if (!(adbObject instanceof ArtifactDatabase)) {
            log.error("missing artefact database. Cannot scan");
            return;
        }

        ArtifactDatabase adb = (ArtifactDatabase)adbObject;

        if (!cleanDatabase()) {
            log.error("cleaning database failed");
            return;
        }

        InitialScan is = new InitialScan(context);

        if (!is.scan(adb)) {
            log.error("initial scan failed");
            return;
        }

    }

    protected boolean cleanDatabase() {

        log.debug("cleanDatabase");

        boolean success = sqlExecutor.new Instance() {
            @Override
            public boolean doIt() throws SQLException {
                prepareStatement(SQL_DELETE_ALL_USERS);
                stmnt.execute();
                prepareStatement(SQL_DELETE_ALL_ARTIFACTS);
                stmnt.execute();
                conn.commit();
                return true;
            }
        }.runWrite();

        log.debug("after runWrite(): " + success);

        return success;
    }


    @Override
    public void systemDown(GlobalContext context) {
        log.debug("systemDown");
    }
}
// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :