#ifndef TRAJECTORYDBACCESSLIST_H_
#define TRAJECTORYDBACCESSLIST_H_

#include "TrajectoryDBAccess.h"

#include <boost/foreach.hpp>

/**
 * TrajectoryDBAccessList class.
 *
 * The TrajectoryDBAccessList class allows to perform basic operations on a database representing trajectory as a List.
 */
template<typename T>
class TrajectoryDBAccessList: public TrajectoryDBAccess<T> {
 protected:
  /** Get ids of trajectories starting or ending at frameNum
      (specific to list version of db) */
  bool trajectoryIdStartingEndingAt(std::vector<int>& ids, const int& frameNum, const std::string& firstOrLast) {
    if (!TrajectoryDBAccess<T>::db->isConnected())
      return false;
    
    std::stringstream stmtSS;
    stmtSS << "SELECT trajectory_id from trajectory_instants WHERE "<< firstOrLast <<"_instant=" << frameNum;
    return TrajectoryDBAccess<T>::db->executeStatementSelectIntegers(ids, stmtSS.str().c_str());
  }

 public:
  /** Create single index for a table. */
  bool createIndex(const std::string& tableName, const std::string& columnName, const bool& unique = false) {
    if (!TrajectoryDBAccess<T>::db->isConnected())
      return false;
    
    std::string stmtStr = "CREATE ";
    if (unique)
      stmtStr += "UNIQUE ";
    stmtStr += "INDEX "+tableName+"_"+columnName+"_index ON "+tableName+"("+columnName+")";
    return TrajectoryDBAccess<T>::db->executeStatement(stmtStr.c_str());
  }

	/**
	 * Create a Trajectory table.
	 *
	 * @return information whether the operation was successful
	 */
	bool createTable(const std::string& tableName = "trajectories")
	{
		if (!TrajectoryDBAccess<T>::db->isConnected())
		{
			return false;
		}

		std::string statementString = "create table "+tableName+" ( trajectory_id INTEGER, frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY( trajectory_id, frame_number ) );";
		  //"create table trajectories ( trajectory_id INTEGER, frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, z_coordinate REAL, PRIMARY KEY( trajectory_id, frame_number ) );";
		bool success = TrajectoryDBAccess<T>::db->executeStatement(statementString.c_str());
		return success;
	}

	/** Create view of first and last frame numbers of trajectories.
	 * (specific to list version of db) */
	bool createViewInstants(const std::string& firstOrLast) {
	  if (!TrajectoryDBAccess<T>::db->isConnected())
	    return false;

	  std::string minOrMax = (firstOrLast=="first")?"min":"max";
	  std::string stmtStr = "CREATE VIEW IF NOT EXISTS "+getViewName(firstOrLast)+" AS select trajectory_id, "+minOrMax+"(frame_number) as frame_number from positions group by trajectory_id";
	  bool success = TrajectoryDBAccess<T>::db->executeStatement(stmtStr.c_str());
	  return success;
	}

	/** Create view or temporary table of first and last instants, 
	    as well as length of each trajectory
	    (specific to list version of db) */
	bool createInstants(const std::string& view) {
	  if (!TrajectoryDBAccess<T>::db->isConnected())
	    return false;

	  std::string viewOrTable = (view=="view")?"VIEW":"TEMP TABLE";
	  std::string stmtStr = "CREATE "+viewOrTable+" IF NOT EXISTS trajectory_instants AS select trajectory_id, min(frame_number) as first_instant, max(frame_number) as last_instant, max(frame_number)-min(frame_number)+1 as length from positions group by trajectory_id";
	  // alternative for trajectory length: SELECT count(*) as length FROM trajectories GROUP BY trajectory_id

	  bool success = TrajectoryDBAccess<T>::db->executeStatement(stmtStr.c_str());

	  //success = success && createIndex("trajectory_instants", "first_instant");
	  success = success && createIndex("trajectory_instants", "last_instant");
	  success = success && createIndex("trajectory_instants", "trajectory_id", true);

	  return success;
	}

	/** Get ids of trajectories starting at frameNum
	    (specific to list version of db) */
	bool trajectoryIdStartingAt(std::vector<int>& ids, const int& frameNum) { return trajectoryIdStartingEndingAt(ids, frameNum, "first");}
	/** Get ids of trajectories ending at frameNum
	    (specific to list version of db) */
	bool trajectoryIdEndingAt(std::vector<int>& ids, const int& frameNum) { return trajectoryIdStartingEndingAt(ids, frameNum, "last");}

	/** Get ids of trajectories with one instant between first and last instant */
	bool trajectoryIdInInterval(std::vector<int>& ids, const unsigned int& firstInstant, const unsigned int& lastInstant) {
	  if (!TrajectoryDBAccess<T>::db->isConnected())
	    return false;
	  
	  std::stringstream stmtSS;
	  stmtSS << "SELECT trajectory_id from positions WHERE frame_number BETWEEN " << firstInstant << " and " << lastInstant;
	  return TrajectoryDBAccess<T>::db->executeStatementSelectIntegers(ids, stmtSS.str().c_str());
	}

	/** Returns the maximum trajectory length
	 * \TODO if trajectory_instants does not exist, get it from positions table
	 * \TODO provide a mechanism to handle empty results */
	bool maxTrajectoryLength(unsigned int& length) {
	  std::string stmtStr = "select max(length) from trajectory_instants";
	  std::vector<int> result;
	  bool success = TrajectoryDBAccess<T>::db->executeStatementSelectIntegers(result, stmtStr.c_str());
	  if (!result.empty())
	    length = result[0];
	  return success;
	}

	

	/**
	 * Read trajectories from a database.
	 *
	 * @param[out] trajectories trajectories
	 * @param[in] limit maximum number of trajectories, which will be read
	 * @param[in] offset offset
	 * @return information whether the operation was successful
	 */
	bool read(std::vector<std::shared_ptr<Trajectory<T> > > &trajectories, const std::string& tableName = "trajectories")
	{
		if (!TrajectoryDBAccess<T>::db->isConnected())
		{
			return false;
		}

		std::string statement = "select * from "+tableName+" order by trajectory_id, frame_number;";

		std::vector<std::vector<std::string> > result;
		bool success = TrajectoryDBAccess<T>::db->executeStatementGetMatrix(statement.c_str(), result);
		if (success)
		{
		  if (trajectories.empty())
			{
			  trajectories = std::vector<std::shared_ptr<Trajectory<T> > >();
			}

			bool firstId = true;
			unsigned int prev = 0;
			for (unsigned int i = 0; i < result.size(); ++i)
			{
				unsigned int trajectoryId = convertString<unsigned int> (result[i][0]);

				std::string s = "";
				for (unsigned int j = 1; j < result[i].size(); ++j)
				{
					s += result[i][j] + " ";
				}

				std::istringstream is(s);
				TrajectoryElement<T> t;
				is >> t;

				if (firstId || trajectoryId != prev)
				{
					firstId = false;
					trajectories.push_back(std::shared_ptr<Trajectory<T> >(new Trajectory<T> ()));
					trajectories.back()->setId(trajectoryId);
					prev = trajectoryId;
				}

				trajectories.back()->add(t);
			}
		}

		return success;
	}

	/**
	 * Read prototypes from a database and matching trajectory Ids.
	 *
	 * @param[out] multimap of prototype ids and trajectory ids
	 * @retur boolean : if  the operation was successful or not
	 * 
	 */
	bool read(std::multimap<int,int>& matches, const std::string& tableName = "prototypes") { 
	  if (TrajectoryDBAccess<T>::db->isConnected())
	      return false;

	  std::string statement = "select * from "+tableName+" order by trajectory_id, trajectory_id_matched;";

	  bool success = TrajectoryDBAccess<T>::db->executeStatementSelectPrototypeMatches(matches, statement.c_str());
	  return success;
	}

	/// Reads trajectory with specific number
	bool read(std::shared_ptr<Trajectory<cv::Point2f> >& trajectory, const int& trajectoryId, const std::string& tableName = "trajectories") {
	  if (!TrajectoryDBAccess<cv::Point2f>::db->isConnected())
	      return false;

	  std::string statement = "select * from "+tableName+" where trajectory_id = " +TrajectoryDBAccess<T>::toString(trajectoryId)+ " order by frame_number;";
	  std::map<int, std::vector<TrajectoryElement<cv::Point2f> > > trajectoryElements;
	  bool success = TrajectoryDBAccess<T>::db->executeStatementSelectTrajectoryElements(trajectoryElements, statement.c_str());
	  if (success) {
	    assert(trajectoryElements.count(trajectoryId) == 1);
	    assert(trajectoryElements.size() == 1);
	    trajectory = std::shared_ptr<Trajectory<cv::Point2f> >(new Trajectory<cv::Point2f>(trajectoryId, trajectoryElements[trajectoryId]));
	  }
	  
	  return success;
	}

	/// Reads trajectory with specific number
	bool read(std::vector<std::shared_ptr<Trajectory<cv::Point2f> > >& trajectories, const std::vector<int>& trajectoryIds, const std::string& tableName = "trajectories") {
	  if (!TrajectoryDBAccess<cv::Point2f>::db->isConnected())
	      return false;
	  bool success = false;
	  if (!trajectoryIds.empty()) {
	    std::stringstream trajectoryIdsSS;
	    BOOST_FOREACH(int id, trajectoryIds)
	      trajectoryIdsSS << id << ", ";
	    std::string statement = "select * from "+tableName+" where trajectory_id in (" +trajectoryIdsSS.str()+ ") order by frame_number;";
	    std::map<int, std::vector<TrajectoryElement<cv::Point2f> > > trajectoryElements;
	    success = TrajectoryDBAccess<T>::db->executeStatementSelectTrajectoryElements(trajectoryElements, statement.c_str());
	    if (success) {
	      BOOST_FOREACH(int id, trajectoryIds)
		trajectories.push_back(std::shared_ptr<Trajectory<cv::Point2f> >(new Trajectory<cv::Point2f>(id, trajectoryElements[id])));
	    }
	  }
	  return success;
	}

	/** Write the trajectory to the database */
	bool write(const Trajectory<T> &trajectory, const std::string& tableName = "trajectories")
	{
		std::string sid = TrajectoryDBAccess<T>::toString(trajectory.getId());

		for (unsigned int i = 0; i < trajectory.size(); ++i)
		{
		  //std::string stmt = "insert into trajectories (trajectory_id, frame_number, x_coordinate, y_coordinate, z_coordinate) values (";
			std::string stmt = "insert into "+tableName+" (trajectory_id, frame_number, x_coordinate, y_coordinate) values (";
			stmt += sid + ", ";
			std::stringstream ss;
			T p = trajectory[i];
			ss << trajectory.getFrameNumber(i) << ", " << p.x << ", " << p.y;
			stmt += ss.str();

			/* if (dim(trajectory.getPoint(i)) == 2) //for z_coordinate */
			/* { */
			/* 	stmt += ",0"; */
			/* } */

			stmt += ");";

			bool success = TrajectoryDBAccess<T>::db->executeStatement(stmt.c_str());
			if (!success)
			{
			  std::cout << "rollback" << std::endl;
			  success = TrajectoryDBAccess<T>::db->rollback();
			  if (!success)
			    {
			      return false;
			    }
			}
		}
		return true;
	}

private:
	/**
	 * Convert string to variable with a type of \a Tc.
	 *
	 * @param s input parameter
	 * @return output variable
	 */
	template<typename Tc> Tc convertString(std::string s)
	{
	  std::istringstream is(s);
	  Tc x;
	  is >> x;
	  return x;
	}

	/// Returns the name of the view
	static std::string getViewName(const std::string& firstOrLast) { return "trajectory_"+firstOrLast+"_instants";}
};

#endif /* TRAJECTORYDBACCESSLIST_H_ */