/**
 * <p>
 * Copyright © 2009-2010, Bruce-Robert Pocock
 * </p>
 * <p>
 * Based upon public domain sample code provided by Authorize.net
 * </p>
 * <p>
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or (at
 * your option) any later version.
 * </p>
 * <p>
 * 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
 * General Public License for more details.
 * </p>
 * <p>
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 * </p>
 * 
 * @author brpocock
 */

package org.starhope.appius.mb;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Calendar;
import java.util.Collection;
import java.util.GregorianCalendar;
import java.util.LinkedList;
import java.util.Random;
import java.util.Vector;

import org.json.JSONException;
import org.json.JSONObject;
import org.starhope.appius.except.NotFoundException;
import org.starhope.appius.game.AppiusClaudiusCaecus;
import org.starhope.appius.pay.util.Invoiceable;
import org.starhope.appius.sql.SQLPeerDatum;
import org.starhope.appius.user.AbstractUser;
import org.starhope.appius.user.Person;
import org.starhope.appius.user.User;
import org.starhope.appius.util.AppiusConfig;

import com.tootsville.WebUtil;
import com.tootsville.user.Toot;

/**
 * This class represents an instance of a purchased enrolment
 * (subscription) to a game, as bound to a particular user and period of
 * time.
 * 
 * @author brpocock, <a href="mailto:theys@resinteractive.com">Tim
 *         Heys</a>
 */
public class UserEnrolment extends SQLPeerDatum implements Invoiceable {

	/**
	 * An internally-used array of characters used to create order
	 * codes. A-Z, notably excluding I and O, and 0-9.
	 */
	private static final char orderCodeChars[] = { 'A', 'B', 'C', 'D',
			'E', 'F', 'G', 'H', 'J', 'K', 'L', 'M', 'N', 'P', 'R', 'T',
			'U', 'V', 'W', 'X', 'Y', '0', '1', '2', '3', '4', '5', '6',
			'7', '8', '9' };

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

	/**
	 * @param userID user enrolled
	 * @return array of all current enrolments
	 */
	public static Collection <UserEnrolment> getAllForUserID (
			final int userID) {
		final LinkedList <UserEnrolment> enrolments = new LinkedList <UserEnrolment> ();
		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			con = AppiusConfig.getDatabaseConnection ();
			st = con
					.prepareStatement ("SELECT * FROM subscriptions WHERE user_id=? ORDER BY expires_at DESC");

			st.setInt (1, userID);
			if (st.execute ()) {
				rs = st.getResultSet ();
				final long twoYears = 63113851900l;
				final Date twoYearsAgo = new Date (System
						.currentTimeMillis ()
						- twoYears);
				while (rs.next ()) {
					UserEnrolment enrolment = null;
					try {
						enrolment = new UserEnrolment (rs);
					} catch (final NotFoundException e) {
						AppiusClaudiusCaecus
								.reportBug (
										"User ID#"
												+ userID
												+ " subscription can't be found, user claims to have a subscription to "
												+ rs
														.getInt ("product_id"),
										e);
					}
					if (null != enrolment)
						if (enrolment.getExpires ().after (twoYearsAgo)) {
							enrolments.add (enrolment);
						}
				}
			}
		} catch (final SQLException e) {
			return new Vector <UserEnrolment> ();
		} finally {
			if (null != rs) {
				try {
					rs.close ();
				} catch (final SQLException e) { /* No Op */
				}
			}
			if (null != st) {
				try {
					st.close ();
				} catch (final SQLException e) { /* No Op */
				}
			}
			if (null != con) {
				try {
					con.close ();
				} catch (final SQLException e) { /* No Op */
				}
			}

		}
		return enrolments;
	}

	/**
	 * Retrieve a User Enrolment based off the invoice number split into
	 * orderSource and orderCode.
	 * 
	 * @param orderSource The source of the order (ex. 'auth')
	 * @param orderCode The code of the order
	 * @return A UserEnrolment with the invoice number of
	 *         {orderSource}-{orderCode}
	 * @throws NotFoundException if an enrolment cannot be found with
	 *             the given orderSource and orderCode.
	 */
	public static UserEnrolment getBySourceAndCode (
			final String orderSource, final String orderCode)
		throws NotFoundException {
		return new UserEnrolment (orderSource, orderCode);
	}

	/**
	 * Get all enrolments for a given user in the past two years from
	 * today's date.
	 * 
	 * @param userID user enrolled
	 * @return array of all current enrolments
	 */
	public static UserEnrolment [] getLastTwoYearsForUserID (
			final int userID) {
		final LinkedList <UserEnrolment> enrolments = new LinkedList <UserEnrolment> ();
		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			con = AppiusConfig.getDatabaseConnection ();
			st = con
					.prepareStatement ("SELECT * FROM subscriptions WHERE user_id=? ORDER BY expires_at DESC");

			st.setInt (1, userID);
			if (st.execute ()) {
				rs = st.getResultSet ();
				final long twoYears = 63113851900l;
				final Date twoYearsAgo = new Date (System
						.currentTimeMillis ()
						- twoYears);
				while (rs.next ()) {
					UserEnrolment enrolment = null;
					try {
						enrolment = new UserEnrolment (rs);
					} catch (final NotFoundException e) {
						AppiusClaudiusCaecus
								.reportBug (
										"User subscription can't be found, user claims to have a subscription to "
												+ rs
														.getInt ("product_id"),
										e);
					}
					if (null != enrolment)
						if (enrolment.getExpires ().after (twoYearsAgo)) {
							enrolments.add (enrolment);
						}
				}
			}
		} catch (final SQLException e) {
			return new UserEnrolment [] {};
		} finally {
			if (null != rs) {
				try {
					rs.close ();
				} catch (final SQLException e) { /* No Op */
				}
			}
			if (null != st) {
				try {
					st.close ();
				} catch (final SQLException e) { /* No Op */
				}
			}
			if (null != con) {
				try {
					con.close ();
				} catch (final SQLException e) { /* No Op */
				}
			}

		}
		return enrolments.toArray (new UserEnrolment [enrolments
				.size ()]);
	}

	/**
	 * Authorize.net subscription ID
	 */
	private BigDecimal authSubID;

	/**
	 * start date
	 */
	private java.sql.Date begins;

	/**
	 * expiry date
	 */
	private Date expires;

	/**
	 * database ID
	 */
	private int id;

	/**
	 * Has this enrolment been activated already?
	 */
	private volatile boolean isActivated = false;

	/**
	 * order code
	 */
	private String orderCode;

	/**
	 * order source
	 */
	private String orderSource;

	/**
	 * Enrolment product ID
	 */
	private int productID;

	/**
	 * The user who owns this enrolment
	 */
	private int userID;

	/**
	 * @param rs SQL result set representing an enrolment
	 * @throws NotFoundException WRITEME
	 */
	public UserEnrolment (final ResultSet rs) throws NotFoundException {
		try {
			set (rs);
		} catch (final SQLException e) {
			throw new NotFoundException (e.toString ());
		}
	}

	/**
	 * WRITEME: document
	 * 
	 * @param order_source order source
	 * @param product_id WRITEME
	 * @param user_id WRITEME
	 * @throws NotFoundException if the enrolment type is not found.
	 */
	public UserEnrolment (final String order_source,
			final int product_id, final int user_id)
		throws NotFoundException {
		generateOrderCode ();
		orderSource = order_source;

		begins = new Date (0);
		productID = product_id;
		userID = user_id;
		expires = new Date (0);
		try {
			insert ();
		} catch (final SQLException e1) {
			e1.printStackTrace ();
		}
	}

	/**
	 * @param order_source order source
	 * @param order_code order code
	 * @throws NotFoundException if the order doesn't already exist
	 */
	public UserEnrolment (final String order_source,
			final String order_code) throws NotFoundException {
		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			con = AppiusConfig.getDatabaseConnection ();
			st = con
					.prepareStatement ("SELECT * FROM subscriptions WHERE order_source=? AND order_code=?");

			st.setString (1, order_source);
			st.setString (2, order_code);
			rs = st.executeQuery ();
			rs.next ();
			set (rs);
			rs.close ();
			st.close ();
		} catch (final SQLException e) {
			throw new NotFoundException (order_source + "-"
					+ order_code);
		} finally {
			if (null != rs) {
				try {
					rs.close ();
				} catch (final SQLException e) { /* No Op */
				}
			}
			if (null != st) {
				try {
					st.close ();
				} catch (final SQLException e) { /* No Op */
				}
			}
			if (null != con) {
				try {
					con.close ();
				} catch (final SQLException e) { /* No Op */
				}
			}

		}
	}

	/**
	 * Deprecated, see {@link #activate()}
	 */
	@Deprecated
	public void activate () {
		activate (true);
	}

	/**
	 * @param newEnrolment true if this is a new account FIXME:
	 *            activatedUser.isPaidMember () instead of accepting a
	 *            boolean?
	 */
	public void activate (final boolean newEnrolment) {
		if (isActivated) return;
		if (userID > 1) {
			if (newEnrolment) {
				final Toot activatedUser = (Toot) User.getByID (userID);
				if (null == activatedUser)
					throw AppiusClaudiusCaecus
							.fatalBug ("Can't activate enrolment, no user #"
									+ userID);
				activatedUser.affirmPaidMember ();
				activatedUser.sendConfirmationForPremium ();
				startEnrolment ();
				activatedUser.startEnrolment (this);
			} else {
				continueEnrolment ();
			}
		}
		isActivated = true;
	}

	/**
	 * Don't use. Use something else instead. WRITEME theys
	 */
	@Deprecated
	public void addPayment () {
		try {
			Payment.addPaymentToSequence (this);
			final Calendar cal = new GregorianCalendar ();
			cal.add (Calendar.MONTH, getEnrolment ()
					.getPrivilegeMonths ());
			setExpires (new Date (cal.getTimeInMillis ()));
			changed ();
		} catch (final NotFoundException e) {
			AppiusClaudiusCaecus
					.reportBug ("No payments are being found for "
							+ getOrderSource () + "-" + getOrderCode ());
		}
	}

	/**
	 * WRITEME theys
	 */
	public void cancelNow () {
		final Date now = new Date (System.currentTimeMillis ());
		setExpires (now);
	}

	/**
	 * TODO: document this method (theys, Sep 24, 2009)
	 */
	public void continueEnrolment () {
		final Calendar cal = new GregorianCalendar ();
		cal.add (Calendar.MONTH, getEnrolment ().getPrivilegeMonths ());
		setExpires (new Date (cal.getTimeInMillis ()));
		changed ();
	}

	/**
	 * @see org.starhope.appius.sql.SQLPeerDatum#flush()
	 */
	@Override
	public void flush () {
		System.err.println ("Flushing enrolments");
		System.err.println ("begins: " + begins);
		System.err.println ("expires: " + expires);
		System.err.println ("for id: " + getUserID ());
		Connection con = null;
		PreparedStatement st = null;
		try {
			con = AppiusConfig.getDatabaseConnection ();
			st = con
					.prepareStatement ("UPDATE subscriptions SET user_id=?, begins_at=?, expires_at=?, product_id=?, auth_sub_id=? WHERE order_source=? AND order_code=?");
			st.setInt (1, getUserID ());
			st.setDate (2, begins);
			st.setDate (3, expires);
			st.setInt (4, getProductID ());
			if (null == getAuthSubID ()) {
				st.setNull (5, Types.DECIMAL);
			} else {
				st.setBigDecimal (5, getAuthSubID ());
			}
			st.setString (6, getOrderSource ());
			st.setString (7, getOrderCode ());
			st.execute ();
		} catch (final SQLException e) {
			AppiusClaudiusCaecus.fatalBug (e);
		} finally {
			if (null != st) {
				try {
					st.close ();
				} catch (final SQLException e) { /* No Op */
				}
			}
			if (null != con) {
				try {
					con.close ();
				} catch (final SQLException e) { /* No Op */
				}
			}

		}
	}

	/**
	 * Create a pseudorandom, unique order code consisting of the
	 * approved letters and numbers. (Excludes the letters O, I, Z, and
	 * S for potential confusion with 0, 1, 2, and 5)
	 */
	private void generateOrderCode () {
		orderCode = "";
		boolean found = false;
		final Random rnd = new Random ();

		PreparedStatement st = null;
		Connection con = null;
		try {
			con = AppiusConfig.getDatabaseConnection ();
			st = con
					.prepareStatement ("SELECT COUNT(*) FROM subscriptions WHERE order_source=? AND order_code=?");
			st.setString (1, orderSource);
			while ( !found) {
				// generate 10 random characters from the given
				for (int i = 0; i < 10; ++i) {
					orderCode += String
							.valueOf (UserEnrolment.orderCodeChars [(int) (rnd
									.nextDouble () * UserEnrolment.orderCodeChars.length)]);
				}
				st.setString (2, orderCode);
				if (st.execute ()) {
					final ResultSet exists = st.getResultSet ();
					exists.next ();
					if (exists.getInt (1) == 0) {
						found = true;
					}
				}
			}
		} catch (final SQLException e) {
			throw AppiusClaudiusCaecus.fatalBug (e);
		} finally {
			if (null != st) {
				try {
					st.close ();
				} catch (final SQLException e) { /* No Op */
				}
			}
			if (null != con) {
				try {
					con.close ();
				} catch (final SQLException e) { /* No Op */
				}
			}
		}
		return;
	}

	/**
	 * @see org.starhope.appius.pay.util.Invoiceable#getAmount()
	 */
	public BigDecimal getAmount () {
		return getEnrolment ().getPrice ();
	}

	/**
	 * @return authorize.net subscriber ID
	 */
	public BigDecimal getAuthSubID () {
		return authSubID;
	}

	/**
	 * @return begin date
	 */
	public Date getBegins () {
		return begins;
	}

	/**
	 * @see org.starhope.appius.pay.util.Invoiceable#getBuyer()
	 */
	public Person getBuyer () {
		// return getLastPayment ().getPayer ();
		return null;
	}

	/**
	 * @see org.starhope.appius.sql.SQLPeerDatum#getCacheUniqueID()
	 */
	@Override
	protected String getCacheUniqueID () {
		return orderSource + orderCode;
	}

	/**
	 * @see org.starhope.appius.pay.util.Invoiceable#getCurrency()
	 */
	public Currency getCurrency () {
		return Currency.get_USD (); // TODO: Currency support
	}

	/**
	 * TODO: document this method (brpocock, Nov 19, 2009)
	 * 
	 * @return WRITEME
	 */
	public String getDescription () {
		return getEnrolment ().getTitle () + " for "
				+ ((Toot) User.getByID (getUserID ())).getUserName ();
	}

	/**
	 * @return enrolment
	 */
	public Enrolment getEnrolment () {
		try {
			return Enrolment.getByID (productID);
		} catch (final NotFoundException e) {
			return null;
		}
	}

	/**
	 * @return expiry
	 */
	public Date getExpires () {
		return expires;
	}

	/**
	 * @return unique ID
	 */
	private int getID () {
		return id;
	}

	/**
	 * @see org.starhope.appius.pay.util.Invoiceable#getInvoiceID()
	 */
	public String getInvoiceID () {
		return orderSource + "-" + orderCode;
	}

	/**
	 * @see org.starhope.appius.pay.util.Invoiceable#getInvoiceIDPrefix()
	 */
	public char getInvoiceIDPrefix () {
		return 'B'; // Bruce-Robert Pocock
	}

	/**
	 * Checks all enrolments for current user to determine when their
	 * last expiration ends and if it ends before today. Returns the
	 * date furthest into the future.
	 * 
	 * @return WRITEME
	 */
	private java.sql.Date getLastExpiration () {
		Date lastExpiration = new Date (System.currentTimeMillis ());

		for (final UserEnrolment enrolment : UserEnrolment
				.getAllForUserID (getUserID ()))
			if (lastExpiration.before (enrolment.getExpires ())) {
				WebUtil.log ("Unit Test (" + lastExpiration
						+ ") is before (" + enrolment.getExpires ()
						+ ")");
				lastExpiration = enrolment.getExpires ();
			}

		WebUtil.log ("Unit Test (Last Expiration for User ID#"
				+ getUserID () + "): " + lastExpiration);

		return lastExpiration;
	}

	/**
	 * @return last payment
	 * @throws NotFoundException if nobody's paid anything yet
	 */
	public Payment getLastPayment () throws NotFoundException {
		return Payment.getLastPaymentFor (this);
	}

	/**
	 * @return order code
	 */
	public String getOrderCode () {
		return orderCode;
	}

	/**
	 * @return order source
	 */
	public String getOrderSource () {
		return orderSource;
	}

	/**
	 * @return product ID of enrolment
	 */
	public int getProductID () {
		return productID;
	}

	/**
	 * @return the next date where billing will reoccur
	 */
	public java.util.Date getRecurs () {
		final int months = getEnrolment ().getPrivilegeMonths ();
		final Calendar cal = Calendar.getInstance ();
		cal.setTimeInMillis (begins.getTime ());
		cal.add (Calendar.MONTH, months);
		return cal.getTime ();
	}

	/**
	 * @see org.starhope.appius.pay.util.Invoiceable#getTitle()
	 */
	public String getTitle () {
		return "Enrolment for Tootsville.com";
	}

	/**
	 * @return user who is enrolled
	 */
	public AbstractUser getUser () {
		final AbstractUser u = User.getByID (getUserID ());
		if (null == u)
			throw AppiusClaudiusCaecus
					.fatalBug ("Can't find user by ID #" + getUserID ());
		return u;
	}

	/**
	 * @return the user ID of the user who �owns� this enrolment
	 */
	public int getUserID () {
		return userID;
	}

	/**
	 * @throws SQLException WRITEME
	 */
	public void insert () throws SQLException {
		Connection con = null;
		PreparedStatement st = null;
		try {
			con = AppiusConfig.getDatabaseConnection ();
			st = con
					.prepareStatement ("INSERT INTO subscriptions (user_id, begins_at, expires_at, order_source, order_code, product_id, auth_sub_id) VALUES (?,?,?,?,?,?,?)");
			st.setInt (1, userID);
			st.setDate (2, begins);
			st.setDate (3, expires);
			st.setString (4, orderSource);
			st.setString (5, orderCode);
			st.setInt (6, productID);
			st.setBigDecimal (7, authSubID);
			st.execute ();
		} catch (final SQLException e) {
			AppiusClaudiusCaecus.fatalBug (e);
		} finally {
			if (null != st) {
				try {
					st.close ();
				} catch (final SQLException e) { /* No Op */
				}
			}
			if (null != con) {
				try {
					con.close ();
				} catch (final SQLException e) { /* No Op */
				}
			}
		}
	}

	/**
	 * @return true if the enrolment hasn't expired yet
	 */
	public boolean isActive () {
		final Date now = new Date (System.currentTimeMillis ());
		return expires.after (now);
	}

	/**
	 * TODO: document this method (twheys, Oct 2, 2009)
	 * 
	 * @return FIXME
	 */
	public boolean isRecurring () {
		return getEnrolment ().isAutoRenew ();
	}

	/**
	 * TODO: document this method (theys, Sep 24, 2009)
	 */
	public void killEnrolment () {
		final java.sql.Date epoch = new java.sql.Date (0);
		System.err
				.println ("Killing enrolment. New start and expire date set to: "
						+ epoch);
		setBegins (epoch);
		setExpires (epoch);
		flush ();
	}

	/**
	 * @throws SQLException WRITEME
	 * @see org.starhope.appius.sql.SQLPeerDatum#set(java.sql.ResultSet)
	 */
	@Override
	public void set (final ResultSet rs) throws SQLException {
		userID = rs.getInt ("user_id");
		id = rs.getInt ("id");
		begins = rs.getDate ("begins_at");
		expires = rs.getDate ("expires_at");
		orderSource = rs.getString ("order_source");
		orderCode = rs.getString ("order_code");
		productID = rs.getInt ("product_id");
		authSubID = rs.getBigDecimal ("auth_sub_id");
		if (rs.wasNull ()) {
			authSubID = null;
		}
	}

	/**
	 * @param authSubID1 authorize.net subscription ID
	 */
	public void setAuthSubID (final BigDecimal authSubID1) {
		authSubID = authSubID1;
		flush ();
	}

	/**
	 * TODO: document (twheys)
	 * 
	 * @param authSubID2 authorize.net subscription ID
	 * @throws NumberFormatException FIXME
	 */
	public void setAuthSubID (final String authSubID2)
		throws NumberFormatException {
		if (null == authSubID2) {
			AppiusClaudiusCaecus
					.reportBug ("ARB failing. subscription id: " + id
							+ " order code: " + orderSource + "-"
							+ orderCode);
			authSubID = null;
		} else {
			setAuthSubID (new BigDecimal (authSubID2));
		}
	}

	/**
	 * @param newBegins begin date
	 */
	public void setBegins (final Date newBegins) {
		begins = newBegins;
	}

	/**
	 * @param newEnrolment enrolment product
	 */
	public void setEnrolment (final Enrolment newEnrolment) {
		productID = newEnrolment.getProductID ();
	}

	/**
	 * @param newExpires expiry date
	 */
	public void setExpires (final Date newExpires) {
		expires = newExpires;
	}

	/**
	 * @param orderCode1 order code
	 */
	public void setOrderCode (final String orderCode1) {
		orderCode = orderCode1;
	}

	/**
	 * @param orderSource1 order source
	 */
	public void setOrderSource (final String orderSource1) {
		orderSource = orderSource1;
	}

	/**
	 * @param productID1 enrolment product
	 */
	public void setProductID (final int productID1) {
		productID = productID1;
	}

	/**
	 * @param newUser user enrolled
	 */
	public void setUser (final User newUser) {
		setUserID (newUser.getUserID ());
	}

	/**
	 * @param userID1 user enrolled
	 */
	public void setUserID (final int userID1) {
		userID = userID1;
	}

	/**
	 * TODO: document this method (theys, Sep 24, 2009)
	 */
	public void startEnrolment () {
		final java.sql.Date beginsOn = getLastExpiration ();
		System.err
				.println ("Starting enrolment. New start date set to: "
						+ beginsOn);
		try {
			setBegins (beginsOn);
			setExpires (Enrolment.getByID (productID).getExpiryFor (
					begins));
		} catch (final NotFoundException e) {
			// Default catch action, report bug (theys, Sep 24, 2009)
			AppiusClaudiusCaecus.reportBug (e);
		}
		changed ();
	}

	/**
	 * @see org.starhope.appius.sql.SQLPeerDatum#toJSON()
	 */
	@Override
	public JSONObject toJSON () {
		final JSONObject o = new JSONObject ();
		try {
			o.put ("userID", getUserID ());
			o.put ("authSubID", getAuthSubID ());
			o.put ("orderCode", getOrderCode ());
			o.put ("orderSource", getOrderSource ());
			o.put ("userEnrolmentID", getID ());
			o.put ("product", getEnrolment ());
			o.put ("begins", getBegins ().toString ());
			o.put ("expires", getExpires ().toString ());
			o.put ("enrolment", getEnrolment ().getPrice ());
			try {
				o.put ("lastPayment", getLastPayment ().toJSON ());
			} catch (final NotFoundException e) {
				AppiusClaudiusCaecus.reportBug (e);
			}
		} catch (final JSONException e) {
			AppiusClaudiusCaecus.reportBug (e);
			throw new Error (e);
		}
		return o;
	}

}