/**
 *
 */
package org.openimaj.picslurper;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import javax.xml.datatype.DatatypeConfigurationException;
import javax.xml.datatype.DatatypeFactory;
import javax.xml.datatype.XMLGregorianCalendar;

import twitter4j.GeoLocation;
import twitter4j.HashtagEntity;
import twitter4j.MediaEntity;
import twitter4j.Place;
import twitter4j.RateLimitStatus;
import twitter4j.Status;
import twitter4j.URLEntity;
import twitter4j.User;
import twitter4j.UserMentionEntity;

/**
 * This class is a status feeder for picslurper that takes statuses from a
 * database. The default database driver is the MySQL driver and expects a mysql
 * URL in the constructor; e.g. jdbc:mysql://localhost/database
 * <p>
 * The class connects to the database lazily (that is, only when the feed is
 * started), so the driver can be changed after construction using
 * {@link #setDriver(String)}.
 * <p>
 * The schema of the table must include at least two columns: one that contains
 * the tweet text and one that contains the created time. The default column
 * names are expected to be <code>text</code> and <code>created_at</code>
 * respectively. These can be changed by passing in a map, where the key is the
 * expected column name and the value is the actual column name; e.g.
 * <code>text -> tweet_text</code>
 * <p>
 * The results are paged through in steps of 25 by default, so the database must
 * support the <code>LIMIT</code> command.
 *
 * @author David Dupplaw (dpd@ecs.soton.ac.uk)
 * @created 18 Sep 2013
 */
public class DatabaseStatusFeeder implements StatusFeeder {
        protected static class DatabaseFeederStatus implements Status {
                private final XMLGregorianCalendar cal;
                private final String text;

                public DatabaseFeederStatus(final String text, final XMLGregorianCalendar cal) {
                        this.text = text;
                        this.cal = cal;
                }

                /** */
                private static final long serialVersionUID = 1L;

                @Override
                public int compareTo(final Status o) {
                        return 0;
                }

                @Override
                public RateLimitStatus getRateLimitStatus() {
                        return null;
                }

                @Override
                public int getAccessLevel() {
                        return 0;
                }

                @Override
                public UserMentionEntity[] getUserMentionEntities() {
                        return null;
                }

                @Override
                public URLEntity[] getURLEntities() {
                        return null;
                }

                @Override
                public HashtagEntity[] getHashtagEntities() {
                        return null;
                }

                @Override
                public MediaEntity[] getMediaEntities() {
                        return null;
                }

                @Override
                public Date getCreatedAt() {
                        return this.cal.toGregorianCalendar().getTime();
                }

                @Override
                public long getId() {
                        return 0;
                }

                @Override
                public String getText() {
                        return this.text;
                }

                @Override
                public String getSource() {
                        return null;
                }

                @Override
                public boolean isTruncated() {
                        return false;
                }

                @Override
                public long getInReplyToStatusId() {
                        return 0;
                }

                @Override
                public long getInReplyToUserId() {
                        return 0;
                }

                @Override
                public String getInReplyToScreenName() {
                        return null;
                }

                @Override
                public GeoLocation getGeoLocation() {
                        return null;
                }

                @Override
                public Place getPlace() {
                        return null;
                }

                @Override
                public boolean isFavorited() {
                        return false;
                }

                @Override
                public User getUser() {
                        return null;
                }

                @Override
                public boolean isRetweet() {
                        return false;
                }

                @Override
                public Status getRetweetedStatus() {
                        return null;
                }

                @Override
                public long[] getContributors() {
                        return null;
                }

                @Override
                public long getRetweetCount() {
                        return 0;
                }

                @Override
                public boolean isRetweetedByMe() {
                        return false;
                }

                @Override
                public long getCurrentUserRetweetId() {
                        return 0;
                }

                @Override
                public boolean isPossiblySensitive() {
                        return false;
                }

        }

        /** The map of column names */
        private Map<String, String> columnNames = new HashMap<String, String>();

        /** The username to connect to the database */
        private final String username;

        /** The password to connect to the database */
        private final String password;

        /** The database table where the tweets are stored */
        private final String table;

        /** The database connection that is created */
        private Connection connection;

        /** The URL to the database */
        private final String url;

        /** The database driver to use */
        private String databaseDriver = "com.mysql.jdbc.Driver";

        /** The size of each page of results to retrieve from the database */
        private final int pageSize = 25;

        /**
         * Create a database status feeder using the database, table, username and
         * password provided.
         *
         * @param databaseURL
         *            The URL to the database
         * @param table
         *            The table to use
         * @param username
         *            The user name
         * @param password
         *            The password
         */
        public DatabaseStatusFeeder(final String databaseURL, final String table,
                        final String username, final String password)
        {
                this.url = databaseURL;
                this.table = table;
                this.username = username;
                this.password = password;
        }

        /**
         * Create a database status feeder using the database, table, username and
         * password provided with the given column mapping
         *
         * @param databaseURL
         *            The URL to the database
         * @param table
         *            The table to use
         * @param username
         *            The user name
         * @param password
         *            The password
         * @param columnNames
         *            The column mapping
         */
        public DatabaseStatusFeeder(final String databaseURL, final String table,
                        final String username, final String password, final Map<String, String> columnNames)
        {
                this(databaseURL, table, username, password);
                this.columnNames = columnNames;
        }

        /**
         * Create a connection using the given driver for the given database.
         *
         * @param driver
         *            The driver
         * @param url
         *            The URL to the database
         * @param username
         *            The username
         * @param password
         *            The password
         * @return The database connection
         * @throws ClassNotFoundException
         *             If the driver cannot be found
         * @throws SQLException
         *             If the connection could not be created
         */
        private static Connection createConnection(final String driver, final String url,
                        final String username, final String password)
                        throws ClassNotFoundException, SQLException
        {
                Class.forName(driver);
                if (username == null || password == null ||
                                username.trim().length() == 0 || password.trim().length() == 0)
                        return DriverManager.getConnection(url);
                else
                        return DriverManager.getConnection(url, username, password);
        }

        /**
         * {@inheritDoc}
         *
         * @see org.openimaj.picslurper.StatusFeeder#feedStatus(org.openimaj.picslurper.PicSlurper)
         */
        @Override
        public void feedStatus(final PicSlurper slurper) throws IOException {
                try {
                        // Create the connection to the database
                        this.connection = DatabaseStatusFeeder.createConnection(
                                        this.databaseDriver, this.url, this.username, this.password);

                        // Create a scrolling result set type
                        final Statement s = this.connection.createStatement(
                                        ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                        s.setFetchSize(this.pageSize);
                        s.setMaxRows(this.pageSize);

                        // First count how many rows there are so we know how many
                        // pages there will be.
                        String sql = "SELECT COUNT(*) FROM " + this.table;
                        ResultSet r = s.executeQuery(sql);
                        r.next();
                        final int rows = r.getInt(1);
                        final int pages = (int) Math.ceil(rows / (double) this.pageSize);

                        for (int pageNumber = 1; pageNumber <= pages; pageNumber++) {
                                sql = "SELECT " +
                                                this.getColumn("created_at", true) + ", " +
                                                this.getColumn("text", true) +
                                                " from " + this.table +
                                                " LIMIT " + (pageNumber * this.pageSize) + "," + this.pageSize;

                                r = s.executeQuery(sql);

                                // Read the results sets
                                while (r.next()) {
                                        try {
                                                final String createdAt = r.getString(1);
                                                final XMLGregorianCalendar cal = DatatypeFactory.newInstance()
                                                                .newXMLGregorianCalendar(createdAt);
                                                final String text = r.getString(2);

                                                final DatabaseFeederStatus status = new DatabaseFeederStatus(text, cal);
                                                slurper.handleStatus(status);
                                        } catch (final DatatypeConfigurationException e) {
                                                e.printStackTrace();
                                        }
                                }
                        }
                } catch (final ClassNotFoundException e) {
                        e.printStackTrace();
                } catch (final SQLException e) {
                        e.printStackTrace();
                }
        }

        /**
         * Get a column name through the column name mapping, if one exists.
         *
         * @param defaultName
         *            The name to look up
         * @param tf
         *            Whether to enclose in back-ticks
         * @return The column name to use
         */
        private String getColumn(final String defaultName, final boolean tf) {
                if (this.columnNames.get(defaultName) != null)
                        return tf ? "`" + this.columnNames.get(defaultName) + "`" : this.columnNames.get(defaultName);
                return tf ? "`" + defaultName + "`" : defaultName;
        }

        /**
         * Set the database driver to use.
         *
         * @param driverName
         *            The driver name.
         */
        public void setDriver(final String driverName) {
                this.databaseDriver = driverName;
        }

        /**
         * @param args
         */
        public static void main(final String[] args) {
                try {
                        // Connect to local database
                        final DatabaseStatusFeeder dbsf = new DatabaseStatusFeeder(
                                        "jdbc:mysql://localhost:3306/swr", "tweets", "swr", "swr");

                        final PicSlurper ps = new PicSlurper();
                        dbsf.feedStatus(ps);
                } catch (final IOException e) {
                        e.printStackTrace();
                }
        }
}