package votorola.a.diff.harvest.cache;

import java.security.NoSuchAlgorithmException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Logger;

import javax.mail.internet.AddressException;

import votorola.a.diff.harvest.Message;
import votorola.a.voter.IDPair;
import votorola.g.lang.ThreadRestricted;
import votorola.g.lang.ThreadSafe;
import votorola.g.lang.Warning;
import votorola.g.logging.LoggerX;
import votorola.g.sql.Database;

/**
 * The relational store for diff messages. This class automatically creates the
 * necessary table if it does not exist and basically hides away the database
 * layer. Note: It throws errors on double insertions.
 */
@ThreadSafe
final public class DiffMessageTable {

    /**
     * This is only exposed as a quick hint for somebody trying to understand
     * the database structure. This value might change.
     */
    @Warning("non-Api")
    public static final String SCHEMA_NAME = "harvest";

    /**
     * This is only exposed as a quick hint for somebody trying to understand
     * the database structure. This value might change.
     */
    @Warning("non-Api")
    public static final String TABLE_NAME = "diff_messages";

    private static final String TABLE = "\"" + SCHEMA_NAME + "\".\""
            + TABLE_NAME + "\"";

    /**
     * Maximum rows in the result. This should match the maximum client fetch
     * length.
     */
    @Warning("non-Api")
    public static final int MAX_RESULTS = 200;

    private final static Logger LOGGER = LoggerX.i(DiffMessageTable.class);

    /**
     * Main database object.
     */
    private final transient Database database;

    /**
     * Constructs a Table.
     * 
     * @param database
     *            Database to connect to.
     * @throws SQLException
     * @throws NoSuchAlgorithmException
     */
    DiffMessageTable(final Database database) throws SQLException {
        this.database = database;

        synchronized (database) {
            database.ensureSchema(SCHEMA_NAME);
        }

        statementKeyBase = getClass().getName() + ":" + SCHEMA_NAME + "/"
                + TABLE_NAME + ".";
    }

    /**
     * Statement key base for all queries.
     */
    private final transient String statementKeyBase;

    /**
     * Creates this table in the database.
     * 
     * @throws SQLException
     */
    void create() throws SQLException {
        final String key = statementKeyBase + "create";
        synchronized (database) {
            PreparedStatement prepStatem = database.statementCache().get(key);
            if (prepStatem == null) {
                prepStatem = database
                        .connection()
                        .prepareStatement(
                                "CREATE TABLE "
                                        + TABLE
                                        + " (id serial primary key"
                                        + ", author varchar NOT NULL"
                                        + ", addressee varchar NOT NULL"
                                        + ", pollname varchar NOT NULL"
                                        + ", content varchar NOT NULL"
                                        + ", a integer NOT NULL"
                                        + ", ar integer NOT NULL"
                                        + ", b integer NOT NULL"
                                        + ", br integer NOT NULL"
                                        + ", selectand varchar NOT NULL"
                                        + ", archive_url varchar NOT NULL"
                                        + ", path varchar NOT NULL"
                                        + ", sent_ts timestamp with time zone NOT NULL"
                                        + ", UNIQUE(author,addressee,archive_url,path,sent_ts)"
                                        + " )");

                database.statementCache().put(key, prepStatem);
            }
            prepStatem.execute();
        }
    }

    /**
     * Returns the database in which this table is stored.
     * 
     * @return This objects database.
     */
    Database getDatabase() {
        return database;
    }

    /**
     * Drops this table from the database.
     * 
     * @throws SQLException
     */
    void drop() throws SQLException {
        final String key = statementKeyBase + "drop";
        synchronized (database) {
            PreparedStatement prepStatem = database.statementCache().get(key);
            if (prepStatem == null) {
                prepStatem = database.connection().prepareStatement(
                        "DROP TABLE " + TABLE);
                database.statementCache().put(key, prepStatem);
            }
            prepStatem.execute();
        }
    }

    /**
     * Returns true if this table exists in the database; false otherwise.
     * 
     * @return whether database already exists or not.
     * @throws SQLException
     */
    public boolean exists() throws SQLException {
        final String key = statementKeyBase + "exists";
        synchronized (database) {
            PreparedStatement prepStatem = database.statementCache().get(key);
            if (prepStatem == null) {
                prepStatem = database.connection().prepareStatement(
                        "SELECT * FROM " + TABLE);
                prepStatem.setMaxRows(1);
                database.statementCache().put(key, prepStatem);
            }
            try {
                prepStatem.execute();
            } catch (SQLException x) {
                final String sqlState = x.getSQLState();
                if ("3F000".equals(sqlState)) {
                    return false; // 3F000 = [missing schema]
                }

                if ("42P01".equals(sqlState)) {
                    return false; // 42P01 = UNDEFINED TABLE
                }

                throw x;
            }
        }
        return true;
    }

    /**
     * Query the table for the parameters. Order is: id, author, addressee,
     * pollname, content, archive_url, path, sent_ts, a, ar, b, br, selectand
     * 
     * 
     * @param poll
     *            poll-name
     * @param users
     *            mailish-username of one or two users
     * @param id
     *            only fetch posts newer than id
     * @return rs ResultSet with newest first.
     * @throws SQLException
     * @throws AddressException
     */
    @ThreadRestricted("rs holds lock on database")
    public ResultSet get(final String poll, final String[] users, final int id)
            throws SQLException, AddressException {

        final StringBuilder whereScope = new StringBuilder();
        String conj = " WHERE"; // conjunction
        if (poll != null && !poll.isEmpty()) {
            whereScope.append(conj).append(" pollname = ?");
            conj = " AND";
        }
        if (users != null && users.length != 0) {
            for (final String user : users) {
                if (user.isEmpty()) {
                    continue;
                }
                whereScope.append(conj)
                        .append(" (addressee = ? OR author = ?)");
                conj = " AND";
            }
        }
        if (id > 0) {
            whereScope.append(conj).append(" id > ?");
            conj = " AND";
        }

        final String key = statementKeyBase + "getDiffMsgs"
                + whereScope.toString();

        PreparedStatement prepStatem = database.statementCache().get(key);

        final String query = "SELECT id, author, addressee, pollname"
                + ", content, archive_url, path, sent_ts" /* message */
                + ", a, ar, b, br, selectand" /* difference */
                + " FROM " + TABLE + whereScope.toString()
                + " ORDER BY sent_ts DESC LIMIT " + MAX_RESULTS;
        if (prepStatem == null) {
            prepStatem = database.connection().prepareStatement(query);
            database.statementCache().put(key, prepStatem);
        }

        int index = 1;
        if (poll != null && !poll.isEmpty()) {
            prepStatem.setString(index++, poll);
        }
        if (users != null && users.length != 0) {
            for (final String user : users) {
                if (user.isEmpty()) {
                    continue;
                }
                final String email = IDPair.toInternetAddress(user)
                        .getAddress();
                prepStatem.setString(index++, email);
                prepStatem.setString(index++, email);
            }
        }
        if (id != 0) {
            prepStatem.setInt(index++, id);
        }

        return prepStatem.executeQuery();
    }

    /**
     * Stores a message. This throws an exception on double insert attempts on
     * the primary key, which consists author,addresse,url and sentDate
     * 
     * @throws SQLException
     */
    void put(final AuthDiffMessage authMsg) throws SQLException {

        synchronized (database) {
            final String insertKey = statementKeyBase + "putDiffMsgI";
            PreparedStatement s = database.statementCache().get(insertKey);
            if (s == null) {
                s = database
                        .connection()
                        .prepareStatement(
                                "INSERT INTO "
                                        + TABLE
                                        + " (author"
                                        + ", addressee, archive_url, path, pollname"
                                        + ", content, a, ar"
                                        + ", b, br, sent_ts, selectand"
                                        + ")"
                                        + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                database.statementCache().put(insertKey, s);
            }
            Message msg = authMsg.diffMessage().message();

            s.setString(1, authMsg.author());
            s.setString(2, authMsg.addressee());
            s.setString(3, msg.mc().archiveUrl());
            s.setString(4, msg.path());
            s.setString(5, authMsg.diffMessage().draftPair().aCore()
                    .pollName());
            s.setString(6, msg.content());
            {
                // CWFIX obsolete form, e.g. by storing p.key() instead
                final votorola.a.diff.DiffKeyParse parse =
                  authMsg.diffMessage().draftPair().diffKeyParse();
                final java.util.List<Integer> aPath = parse.aPath();
                final java.util.List<Integer> bPath = parse.bPath();
                final int aN = aPath.size();
                final int bN = bPath.size();
                if(aN > 2 || bN > 2) {
                    throw new votorola.g.VotorolaRuntimeException(
                      "new draft revision path too long for old Harvester" );
                }

                s.setInt(7, aPath.get(0));
                s.setInt(8, aN > 1? aPath.get(1): -1);
                s.setInt(9, bPath.get(0));
                s.setInt(10, bN > 1? bPath.get(1): -1);
            }
            s.setTimestamp(11, new java.sql.Timestamp(msg.mc().sentDate()
                    .getTime()));
            s.setString(12, authMsg.selectand());
            s.executeUpdate();

        }
    }

    /**
     * Remove all stored messages for an archive selected by its archive url.
     * 
     * @param archiveUrl
     * @return number of removed messages
     * @throws SQLException
     */
    public int removeArchive(final String archiveUrl) throws SQLException {
        synchronized (database) {
            final String removeKey = statementKeyBase + "remDiffMsgI";
            PreparedStatement s = database.statementCache().get(removeKey);
            if (s == null) {
                s = database.connection().prepareStatement(
                        "DELETE FROM " + TABLE + " where archive_url=?");
                database.statementCache().put(removeKey, s);
            }
            s.setString(1, archiveUrl);

            return s.executeUpdate();
        }
    }

}