--- a/scripts/merge-features.py	Wed Jun 29 13:50:21 2016 -0400
+++ b/scripts/merge-features.py	Wed Jun 29 17:56:19 2016 -0400
@@ -1,15 +1,16 @@
 #! /usr/bin/env python
 
-import sys, argparse, os.path
-import cvutils, utils, moving
-from metadata import createDatabase, Site, VideoSequence
+import sys, argparse, os.path, sqlite3
+import cvutils, utils, moving, storage
+from metadata import createDatabase, Site, VideoSequence, CameraView
 from datetime import datetime, timedelta
 
 parser = argparse.ArgumentParser(description='The program merges feature trajectories recorded from the same site synchronously between start and end time.')
 parser.add_argument('-i', dest = 'metadataFilename', help = 'name of the metadata file', required = True)
 parser.add_argument('-n', dest = 'siteId', help = 'site id or site name', required = True)
-parser.add_argument('--t1', dest = 'startTime', help = 'time to start merging features (format %Y-%m-%d %H:%M:%S, eg 2011-06-22 10:00:39)') # if not provided, take common time interval
-parser.add_argument('--t2', dest = 'endTime', help = 'time to stop merging features (format %Y-%m-%d %H:%M:%S, eg 2011-06-22 10:00:39)')
+parser.add_argument('--start', dest = 'startTime', help = 'time to start merging features (format %Y-%m-%d %H:%M:%S, eg 2011-06-22 10:00:39)') # if not provided, take common time interval
+parser.add_argument('--end', dest = 'endTime', help = 'time to stop merging features (format %Y-%m-%d %H:%M:%S, eg 2011-06-22 10:00:39)')
+parser.add_argument('-o', dest = 'outputDBFilename', help = 'name of the output SQLite file', required = True)
 
 args = parser.parse_args()
 
@@ -25,30 +26,84 @@
 startTime = datetime.strptime(args.startTime, utils.datetimeFormat)
 endTime = datetime.strptime(args.endTime, utils.datetimeFormat)
 processInterval = moving.TimeInterval(startTime, endTime)
-videoSequences = session.query(VideoSequence).filter(VideoSequence.site == site).order_by(VideoSequence.startTime.asc()).all() #.order_by(VideoSequence.cameraViewIdx) .filter(VideoSequence.startTime <= startTime)
+cameraViews = session.query(CameraView).filter(CameraView.site == site)
+videoSequences = session.query(VideoSequence).order_by(VideoSequence.startTime.asc()).all() #.order_by(VideoSequence.cameraViewIdx) .filter(VideoSequence.startTime <= startTime)
+videoSequences = [vs for vs in videoSequences if vs.cameraView in cameraViews]
 #timeIntervals = [v.intersection(startTime, endTime) for v in videoSequences]
-
-cameraViews = set([v.cameraView for v in videoSequences])
+#cameraViews = set([v.cameraView for v in videoSequences])
 
 videoSequences = {cv: [v for v in videoSequences if v.cameraView == cv] for cv in cameraViews}
 timeIntervals = {}
 for cv in videoSequences:
     timeIntervals[cv] = moving.TimeInterval.unionIntervals([v.getTimeInterval() for v in videoSequences[cv]])
 
+# intersection of the time interval (union) for each camera view
 commonTimeInterval = timeIntervals.values()[0]
 for inter in timeIntervals.values()[1:]:
     commonTimeInterval = moving.TimeInterval.intersection(commonTimeInterval, inter)
 commonTimeInterval = moving.TimeInterval.intersection(commonTimeInterval, processInterval)
 
+if commonTimeInterval.empty():
+    print('Empty time interval. Exiting')
+    sys.exit()
+
 if len(set([cv.cameraType.frameRate for cv in cameraViews])) > 1:
     print('Different framerates of the cameras ({}) are not handled yet. Exiting'.format([cv.cameraType.frameRate for cv in cameraViews]))
+else:
+    frameRate = cv.cameraType.frameRate
 
-for cv, v in videoSequences.iteritems():
-    
+try:
+    outConnection = sqlite3.connect(args.outputDBFilename)
+    outCursor = outConnection.cursor()
+    storage.createTrajectoryTable(outCursor, 'positions')
+    storage.createTrajectoryTable(outCursor, 'velocities')
+    storage.createFeatureCorrespondenceTable(outCursor)
+    outConnection.commit()
+except sqlite3.OperationalError as error:
+    storage.printDBError(error)
+    sys.exit()
+
+dirname = os.path.split(args.metadataFilename)[0]
+if len(dirname) == 0:
+    dirname = '.'
 
-# for all camera view, for all video, select from positions and velocities where frame_number is in the right range and insert in new database
+newTrajectoryId = -1
+# first frame num is commonTimeInterval
+for cv, vs in videoSequences.iteritems():
+    #return cursor.fetchone()[0] == 1
+    for videoSequence in vs:
+        print videoSequence.name
+        try:
+            vsConnection = sqlite3.connect(dirname+os.path.sep+videoSequence.getDatabaseFilename())
+            vsCursor = vsConnection.cursor()
+            if commonTimeInterval.first < videoSequence.startTime:
+                firstFrameNum = -(videoSequence.startTime-commonTimeInterval.first).seconds*frameRate
+            else:
+                firstFrameNum = (commonTimeInterval.first-videoSequence.startTime).seconds*frameRate
+            lastFrameNum = (commonTimeInterval.last-videoSequence.startTime).seconds*frameRate
+            # positions table
+            vsCursor.execute('SELECT * FROM positions WHERE frame_number BETWEEN {} AND {} ORDER BY trajectory_id'.format(firstFrameNum, lastFrameNum))
+            featureIdCorrespondences = {}
+            currentTrajectoryId = -1
+            for row in vsCursor:
+                if row[0] != currentTrajectoryId:
+                    currentTrajectoryId = row[0]
+                    newTrajectoryId += 1
+                    featureIdCorrespondences[currentTrajectoryId] = newTrajectoryId
+                outCursor.execute(storage.insertTrajectoryQuery('positions'), (newTrajectoryId, row[1]-firstFrameNum, row[2], row[3]))
+            # velocities table
+            vsCursor.execute('SELECT * FROM velocities WHERE frame_number BETWEEN {} AND {} ORDER BY trajectory_id'.format(firstFrameNum, lastFrameNum))
+            for row in vsCursor:
+                outCursor.execute(storage.insertTrajectoryQuery('velocities'), (featureIdCorrespondences[row[0]], row[1]-firstFrameNum, row[2], row[3]))
+            # saving the id correspondences
+            for oldId, newId in featureIdCorrespondences.iteritems():
+                outCursor.execute("INSERT INTO feature_correspondences (trajectory_id, source_dbname, db_trajectory_id) VALUES ({},\"{}\",{})".format(newId, videoSequence.name, oldId))
+            outConnection.commit()
+        except sqlite3.OperationalError as error:
+            storage.printDBError(error)
 
-# should we save the information of the new "sequence" in the metadata?
-#session.add(VideoSequence('merged', , timedelta(seconds = 31616./30.), laurierSite, laurierCameraViewSpot0))
-
-#session.commit()
+# TODO save the information of the new "sequence" in the metadata
+mergedCameraView = CameraView('merged', None, site, cv.cameraType, None, None)
+session.commit()
+session.add(VideoSequence('merged', commonTimeInterval.first, commonTimeInterval.last-commonTimeInterval.first, mergedCameraView))
+session.commit()