package ambulance.crew.test;

import java.io.File;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Set;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.almworks.sqlite4java.SQLiteConnection;
import com.almworks.sqlite4java.SQLiteException;
import com.google.common.collect.ImmutableSet;

public class AmbulanceCrewTestMain {
	private static Logger logger = LoggerFactory.getLogger(AmbulanceCrewTestMain.class);

	private final File dbFile = new File("D:/!DB/ambulance.sqlite.db");
	private SQLiteConnection db = null;
	private final int alsClass = 1, blsClass = 2, mvoClass = 3, cprClass = 4;
	private final Calendar startDate = new GregorianCalendar(2014, Calendar.FEBRUARY, 3), endDate = new GregorianCalendar(2014, Calendar.FEBRUARY, 10);
	
	public static void main(String[] args) {
		new AmbulanceCrewTestMain();
	}
	
	private AmbulanceCrewTestMain() {
		try {
			if(dbFile.exists()) {
				boolean deleted = dbFile.delete();
				
				if(!deleted) {
					logger.error("could not delete dbFile: "+dbFile.getAbsolutePath());
					System.exit(1);
				} else {
					logger.debug("deleted dbFile: "+dbFile.getAbsolutePath());
				}
			}
			
			setupDB();
		} catch (Throwable t) {
			logger.error(t.getMessage(), t);
			t.printStackTrace();
		}
	}
	
	private void setupDB() throws ClassNotFoundException, SQLiteException {
		try {
			final long startInitialiseNs = System.nanoTime();

			db = new SQLiteConnection(dbFile);
			db.open(true);

			createTables(db);
			
			addUsers(db);
			addCertifications(db);
			addRoles(db);
			addShifts(db);
			addShiftCrews(db);
			addCoverRequests(db);
			
			/*

// all shifts (those with filled and missing roles):
SELECT S.SHIFTID, R.ROLEID, STARTDATE, ENDDATE, CODE, USERNAME
FROM SHIFTS S
JOIN ROLES R
LEFT OUTER JOIN SHIFTCREWS SC ON S.SHIFTID = SC.SHIFTID AND R.ROLEID = SC.ROLEID
LEFT OUTER JOIN USERS U ON SC.USERID = U.USERID
ORDER BY S.STARTDATE

// Shifts with missing crew members
SELECT S.SHIFTID, R.ROLEID, STARTDATE, ENDDATE, CODE
FROM SHIFTS S
JOIN ROLES R
LEFT OUTER JOIN SHIFTCREWS SC ON S.SHIFTID = SC.SHIFTID AND R.ROLEID = SC.ROLEID
WHERE SC.SHIFTID IS NULL
ORDER BY S.STARTDATE

// find users eligible to fill a specified slot
SELECT S.SHIFTID, S.STARTDATE, S.ENDDATE, R.CODE, USERNAME
FROM SHIFTS S
JOIN ROLES R
JOIN ROLECERTIFICATIONS RC ON R.ROLEID = RC.ROLEID
JOIN CERTIFICATIONS C ON RC.CLASSID = C.CLASSID
JOIN USERCERTIFICATIONS UC ON C.CERTIFICATIONID = UC.CERTIFICATIONID
JOIN USERS U ON UC.USERID = U.USERID
LEFT OUTER JOIN (
SELECT USERID
FROM SHIFTCREWS SC
WHERE SC.SHIFTID = 1
) EXISTINGCREW ON UC.USERID = EXISTINGCREW.USERID
WHERE SHIFTID = 1 AND R.ROLEID = 2 AND UC.VALIDFROM <= S.STARTDATE AND UC.VALIDTO >= S.ENDDATE AND EXISTINGCREW.USERID IS NULL

// get information about cover requests
SELECT * 
FROM COVERREQUESTS CR
JOIN SHIFTCREWS SC ON CR.SHIFTCREWID = SC.SHIFTCREWID
JOIN ROLES R ON SC.ROLEID = R.ROLEID
JOIN USERS U ON CR.ORIGINALUSERID = U.USERID
			*/
			
			
			
			if(logger.isDebugEnabled()) {
				final long initialiseMS = (System.nanoTime()-startInitialiseNs) / 1000000;
				logger.debug("initialiseMS: "+initialiseMS);
			}
		} catch(SQLiteException sqle) {
			logger.error(sqle.getMessage(), sqle);
			throw sqle;
		} finally {
			db.dispose();
		}
	}
	
	private void createTables(SQLiteConnection db) throws SQLiteException {
		final long startCreateTablesNs = System.nanoTime();

		try {
			db.exec("CREATE TABLE IF NOT EXISTS Users (" +
					"UserID INTEGER PRIMARY KEY, " +
					"Username VARCHAR(32) UNIQUE NOT NULL)");
			// db.exec("CREATE INDEX IF NOT EXISTS USERS_USERNAME_INDEX ON Users(Username)");
			
			// ALS (AEMT-P, AEMT-CC) - ALS would be the code and AEMT-P and AEMT-CC would be descriptions
			db.exec("CREATE TABLE IF NOT EXISTS Certifications (" +
					"CertificationID INTEGER PRIMARY KEY, " +
					"ClassID INT NOT NULl, " +
					"Code VARCHAR(16) NOT NULL, " +
					"Description VARCHAR(32), " + 
					"UNIQUE(Code, Description))");
			//db.exec("CREATE INDEX IF NOT EXISTS CERTIFICATIONS_CODE_INDEX ON Certifications(Code)");
			//db.exec("CREATE INDEX IF NOT EXISTS CERTIFICATIONS_DESCRIPTION_INDEX ON Certifications(Description)");
			
			db.exec("CREATE TABLE IF NOT EXISTS UserCertifications (" +
					"UserCertificationID INTEGER PRIMARY KEY, " +
					"UserID INTEGER NOT NULL REFERENCES Users(UserID), " +
					"CertificationID INTEGER NOT NULL REFERENCES Certifications(CertificationID), " +
					"ValidFrom DATE NOT NULL, " +
					"ValidTo DATE NOT NULL, " +
					"UNIQUE (UserID, CertificationID, ValidFrom, ValidTo))");
			
			db.exec("CREATE TABLE IF NOT EXISTS Roles (" +
					"RoleID INTEGER PRIMARY KEY, " +
					"Code VARCHAR(16) UNIQUE NOT NULL)");
			//db.exec("CREATE INDEX IF NOT EXISTS ROLES_CODE_INDEX ON Roles(Code)");
			
			db.exec("CREATE TABLE IF NOT EXISTS RoleCertifications (" +
					"RoleID INTEGER NOT NULL REFERENCES Roles(RoleID), " +
					"ClassID INTEGER NOT NULL REFERENCES Certifications(ClassID)," +
					"PRIMARY KEY(RoleID, ClassID))");
			
			db.exec("CREATE TABLE IF NOT EXISTS Shifts (" +
					"ShiftID INTEGER PRIMARY KEY, " +
					"StartDate DATE NOT NULL, " +
					"EndDate DATE NOT NULL, " +
					"UNIQUE (StartDate, EndDate))");
			//db.exec("CREATE INDEX IF NOT EXISTS SHIFTS_STARTDATE_INDEX ON Shifts(StartDate)");
			//db.exec("CREATE INDEX IF NOT EXISTS SHIFTS_ENDDATE_INDEX ON Shifts(EndDate)");
			
			db.exec("CREATE TABLE IF NOT EXISTS ShiftCrews (" +
					"ShiftCrewID INTEGER PRIMARY KEY, " +
					"ShiftID INTEGER NOT NULL REFERENCES Shifts(ShiftID), " +
					"RoleID INTEGER NOT NULL REFERENCES Roles(RoleID), " +
					"UserID INTEGER NOT NULL REFERENCES Users(UserID), " +
					"Permanent BOOLEAN NOT NULL, " +
					"UNIQUE (ShiftID, UserID), " +
					"UNIQUE (ShiftID, RoleID))");
			
			db.exec("CREATE TABLE IF NOT EXISTS CoverRequests (" +
					"CoverRequestID INTEGER PRIMARY KEY, " +
					"ShiftCrewID INTEGER NOT NULL UNIQUE REFERENCES ShiftCrews(ShiftCrewID), " +
					"OriginalUserID INTEGER NOT NULL REFERENCES Users(UserID), " +
					"DateRequested DATE NOT NULL, " +
					"NewUserID INTEGER REFERENCES Users(UserID), " +
					"DateCoverFound DATE)");
			if(logger.isDebugEnabled()) {
				final long createTablesMs = (System.nanoTime()-startCreateTablesNs) / 1000000;
				logger.debug("createTablesMs: "+createTablesMs);
			}
		} catch(SQLiteException sqle1) {
			logger.error(sqle1.getMessage()+", code: "+sqle1.getErrorCode(), sqle1);
			throw sqle1;
		} finally {
		}
	}
	
	private void addUsers(SQLiteConnection db) throws SQLiteException {
		logger.debug("");

		db.exec("INSERT INTO Users (Username) VALUES ('svettel'); " +
				"INSERT INTO Users (Username) VALUES ('falonso'); " +
				"INSERT INTO Users (Username) VALUES ('mwebber'); " +
				"INSERT INTO Users (Username) VALUES ('lhamilton'); " +
				"INSERT INTO Users (Username) VALUES ('kraikkonen'); " +
				"INSERT INTO Users (Username) VALUES ('nrosberg'); " +
				"INSERT INTO Users (Username) VALUES ('rgrosjean'); " +
				"INSERT INTO Users (Username) VALUES ('fmassa'); " +
				"INSERT INTO Users (Username) VALUES ('jbutton'); " +
				"INSERT INTO Users (Username) VALUES ('nhulkenberg'); " +
				"INSERT INTO Users (Username) VALUES ('sperez'); " +
				"INSERT INTO Users (Username) VALUES ('pdiresta'); " +
				"INSERT INTO Users (Username) VALUES ('asutil'); " +
				"INSERT INTO Users (Username) VALUES ('dricciardo'); " +
				"INSERT INTO Users (Username) VALUES ('jevergne'); " +
				"INSERT INTO Users (Username) VALUES ('egutierrez'); " +
				"INSERT INTO Users (Username) VALUES ('vbottas'); " +
				"INSERT INTO Users (Username) VALUES ('pmaldonado'); " +
				"INSERT INTO Users (Username) VALUES ('jbianchi'); " +
				"INSERT INTO Users (Username) VALUES ('cpic'); " +
				"INSERT INTO Users (Username) VALUES ('hkovalainen'); " +
				"INSERT INTO Users (Username) VALUES ('gvdgarde'); " +
				"INSERT INTO Users (Username) VALUES ('mchilton'); ");
	}
	
	private void addCertifications(SQLiteConnection db) throws SQLiteException {
		logger.debug("");

		db.exec("INSERT INTO Certifications (ClassID, Code, Description) VALUES ("+alsClass+", 'ALS', 'AEMT-P'); " +
				"INSERT INTO Certifications (ClassID, Code, Description) VALUES ("+alsClass+", 'ALS', 'AEMT-CC'); " +
				"INSERT INTO Certifications (ClassID, Code, Description) VALUES ("+blsClass+", 'BLS', "+null+"); " +
				"INSERT INTO Certifications (ClassID, Code, Description) VALUES ("+mvoClass+", 'MVO', "+null+"); " +
				"INSERT INTO Certifications (ClassID, Code, Description) VALUES ("+cprClass+", 'CPR', "+null+");");

		db.exec("INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-05-20', '2014-05-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'svettel' AND CODE = 'ALS' AND DESCRIPTION = 'AEMT-P'; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-06-10', '2014-06-10' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'falonso' AND CODE = 'MVO' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-02-20', '2014-02-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'mwebber' AND CODE = 'CPR' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-09-20', '2014-09-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'lhamilton' AND CODE = 'ALS' AND DESCRIPTION = 'AEMT-CC'; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-11-15', '2014-11-15' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'nrosberg' AND CODE = 'BLS' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-10-15', '2014-10-15' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'nrosberg' AND CODE = 'MVO' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-12-12', '2014-12-12' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'kraikkonen' AND CODE = 'BLS' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-01-20', '2014-01-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'rgrosjean' AND CODE = 'ALS' AND DESCRIPTION = 'AEMT-CC'; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-04-01', '2014-04-01' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'fmassa' AND CODE = 'MVO' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-02-01', '2014-02-01' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'fmassa' AND CODE = 'BLS' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-07-20', '2014-07-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'jbutton' AND CODE = 'MVO' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-06-15', '2014-06-15' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'nhulkenberg' AND CODE = 'CPR' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-08-20', '2014-08-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'sperez' AND CODE = 'BLS' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-10-01', '2014-10-01' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'pdiresta' AND CODE = 'BLS' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-06-01', '2014-06-01' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'pdiresta' AND CODE = 'MVO' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-01-10', '2014-01-10' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'asutil' AND CODE = 'MVO' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-02-28', '2014-02-28' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'dricciardo' AND CODE = 'MVO' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-03-01', '2014-03-01' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'jevergne' AND CODE = 'CPR' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-03-01', '2014-03-01' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'jevergne' AND CODE = 'BLS' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-07-20', '2014-07-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'egutierrez' AND CODE = 'MVO' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-02-20', '2014-02-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'vbottas' AND CODE = 'ALS' AND DESCRIPTION = 'AEMT-P'; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-11-03', '2014-11-03' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'pmaldonado' AND CODE = 'BLS' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-06-20', '2014-06-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'jbianchi' AND CODE = 'CPR' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-04-30', '2014-04-30' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'cpic' AND CODE = 'MVO' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-02-20', '2014-02-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'hkovalainen' AND CODE = 'MVO' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-01-10', '2014-01-10' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'gvdgarde' AND CODE = 'CPR' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-01-10', '2014-01-10' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'gvdgarde' AND CODE = 'MVO' AND DESCRIPTION IS NULL; " +
				"INSERT INTO UserCertifications (UserID, CertificationID, ValidFrom, ValidTo) " +
				"SELECT UserID, CertificationID, '2013-04-19', '2014-04-19' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'mchilton' AND CODE = 'MVO' AND DESCRIPTION IS NULL; ");
	}
	
	private void addRoles(SQLiteConnection db) throws SQLiteException {
		logger.debug("");
		
		db.exec("INSERT INTO Roles (Code) VALUES ('ALS'); " +
				"INSERT INTO Roles (Code) VALUES ('BLS'); " +
				"INSERT INTO Roles (Code) VALUES ('MVO'); " +
				"INSERT INTO Roles (Code) VALUES ('CPR'); " +
				"INSERT INTO Roles (Code) VALUES ('5TH'); ");

		db.exec("INSERT INTO RoleCertifications (RoleID, ClassID) SELECT ROLEID, "+alsClass+" FROM ROLES WHERE CODE = 'ALS'; " +
				"INSERT INTO RoleCertifications (RoleID, ClassID) SELECT ROLEID, "+blsClass+" FROM ROLES WHERE CODE = 'BLS'; " +
				"INSERT INTO RoleCertifications (RoleID, ClassID) SELECT ROLEID, "+alsClass+" FROM ROLES WHERE CODE = 'BLS'; " +
				"INSERT INTO RoleCertifications (RoleID, ClassID) SELECT ROLEID, "+mvoClass+" FROM ROLES WHERE CODE = 'MVO'; " +
				"INSERT INTO RoleCertifications (RoleID, ClassID) SELECT ROLEID, "+cprClass+" FROM ROLES WHERE CODE = 'CPR'; " +
				"INSERT INTO RoleCertifications (RoleID, ClassID) SELECT ROLEID, "+alsClass+" FROM ROLES WHERE CODE = '5TH'; " +
				"INSERT INTO RoleCertifications (RoleID, ClassID) SELECT ROLEID, "+blsClass+" FROM ROLES WHERE CODE = '5TH'; " +
				"INSERT INTO RoleCertifications (RoleID, ClassID) SELECT ROLEID, "+mvoClass+" FROM ROLES WHERE CODE = '5TH'; " +
				"INSERT INTO RoleCertifications (RoleID, ClassID) SELECT ROLEID, "+cprClass+" FROM ROLES WHERE CODE = '5TH'; ");
	}
	
	private void addShifts(SQLiteConnection db) throws SQLiteException {
		logger.debug("");
		
		Calendar date = Calendar.getInstance();
		date.clear();
		date.setTime(startDate.getTime());
		
		Calendar tomorrow = Calendar.getInstance();
		tomorrow.clear();
		tomorrow.setTime(startDate.getTime());
		tomorrow.add(Calendar.DATE, 1);

		String insertString = "";
		
		while(date.before(endDate)) {
			switch(date.get(Calendar.DAY_OF_WEEK)) {
				case Calendar.MONDAY: {
					
				} //$FALL-THROUGH$
				case Calendar.TUESDAY: {
					
				} //$FALL-THROUGH$
				case Calendar.WEDNESDAY: {
					
				} //$FALL-THROUGH$
				case Calendar.THURSDAY: {
					
				} //$FALL-THROUGH$
				case Calendar.FRIDAY: {
					// Weekdays
					
					String earlyStart = getDateString(date)+" 19:00";
					String earlyEnd = getDateString(date)+" 23:00";
					
					String lateStsrt = getDateString(date)+" 23:00";
					String lateEnd = getDateString(tomorrow)+" 07:00";

//					db.exec("INSERT INTO Shifts (StartDate, EndDate) VALUES ('"+earlyStart+"', '"+earlyEnd+"')");
//					db.exec("INSERT INTO Shifts (StartDate, EndDate) VALUES ('"+lateStsrt+"', '"+lateEnd+"')");
					
					insertString += "INSERT INTO Shifts (StartDate, EndDate) VALUES ('"+earlyStart+"', '"+earlyEnd+"')"+"; \n";
					insertString += "INSERT INTO Shifts (StartDate, EndDate) VALUES ('"+lateStsrt+"', '"+lateEnd+"')"+"; \n";
					
					break;
				} case Calendar.SATURDAY: {
					
				} //$FALL-THROUGH$
				case Calendar.SUNDAY: {
					// Weekends
					
					String shiftOneStart = getDateString(date)+" 07:00";
					String shiftOneEnd = getDateString(date)+" 13:00";
					
					String shiftTwoStart = getDateString(date)+" 13:00";
					String shiftTwoEnd = getDateString(date)+" 19:00";
					
					String shiftThreeStart = getDateString(date)+" 19:00";
					String shiftThreeEnd = getDateString(date)+" 23:00";
					
					String shiftFourStart = getDateString(date)+" 23:00";
					String shiftFourEnd = getDateString(tomorrow)+" 07:00";
					
//					db.exec("INSERT INTO Shifts (StartDate, EndDate) VALUES ('"+shiftOneStart+"', '"+shiftOneEnd+"')");
//					db.exec("INSERT INTO Shifts (StartDate, EndDate) VALUES ('"+shiftTwoStart+"', '"+shiftTwoEnd+"')");
//					db.exec("INSERT INTO Shifts (StartDate, EndDate) VALUES ('"+shiftThreeStart+"', '"+shiftThreeEnd+"')");
//					db.exec("INSERT INTO Shifts (StartDate, EndDate) VALUES ('"+shiftFourStart+"', '"+shiftFourEnd+"')");
					
					insertString += "INSERT INTO Shifts (StartDate, EndDate) VALUES ('"+shiftOneStart+"', '"+shiftOneEnd+"')"+"; \n";
					insertString += "INSERT INTO Shifts (StartDate, EndDate) VALUES ('"+shiftTwoStart+"', '"+shiftTwoEnd+"')"+"; \n";
					insertString += "INSERT INTO Shifts (StartDate, EndDate) VALUES ('"+shiftThreeStart+"', '"+shiftThreeEnd+"')"+"; \n";
					insertString += "INSERT INTO Shifts (StartDate, EndDate) VALUES ('"+shiftFourStart+"', '"+shiftFourEnd+"')"+"; \n";
					
					break;
				} default: {
					logger.error("unhandled dayOfWeek: "+date.get(Calendar.DAY_OF_WEEK));
				}
			}
			
			date.add(Calendar.DATE, 1);
			tomorrow.add(Calendar.DATE, 1);
		}
		
		db.exec(insertString);
		logger.debug(insertString);
	}
	
	private void addShiftCrews(SQLiteConnection db) throws SQLiteException {
		logger.debug("");
		
		// non-permanent assignments
		db.exec("INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) " +
				"SELECT SHIFTID, ROLEID, USERID, 0 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-03 19:00' AND CODE = '5TH' AND USERNAME = 'nrosberg'; " +
				"INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) " +
				"SELECT SHIFTID, ROLEID, USERID, 0 FROM SHIFTS JOIN ROLES JOIN USERS WHERE STARTDATE = '2014-02-03 19:00' AND CODE = 'ALS' AND USERNAME = 'lhamilton'; ");

		Set<Integer> weekdays = ImmutableSet.of(Calendar.MONDAY, Calendar.TUESDAY, Calendar.WEDNESDAY, Calendar.THURSDAY, Calendar.FRIDAY);
		Set<Integer> weekends = ImmutableSet.of(Calendar.SATURDAY, Calendar.SUNDAY);
		Set<Integer> allDays = ImmutableSet.of(Calendar.MONDAY, Calendar.TUESDAY, Calendar.WEDNESDAY, Calendar.THURSDAY, Calendar.FRIDAY, Calendar.SATURDAY, Calendar.SUNDAY);
		
		// permanent assignments
		List<PermanentShift> permanentShifts = new ArrayList<PermanentShift>();
		
		permanentShifts.add(new PermanentShift("egutierrez", "MVO", allDays, "23"));
		permanentShifts.add(new PermanentShift("jbianchi", "CPR", allDays, "23"));
		permanentShifts.add(new PermanentShift("vbottas", "ALS", allDays, "23"));
		permanentShifts.add(new PermanentShift("pdiresta", "BLS", allDays, "23"));
		permanentShifts.add(new PermanentShift("sperez", "5TH", allDays, "23"));
		
		permanentShifts.add(new PermanentShift("gvdgarde", "CPR", weekdays, "19"));
		permanentShifts.add(new PermanentShift("mchilton", "MVO", weekdays, "19"));
		
		permanentShifts.add(new PermanentShift("pmaldonado", "BLS", weekends, "07"));
		permanentShifts.add(new PermanentShift("hkovalainen", "MVO", weekends, "07"));
		permanentShifts.add(new PermanentShift("jevergne", "CPR", weekends, "07"));
		
//	"SELECT UserID, CertificationID, '2013-05-20', '2014-05-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'svettel' AND CODE = 'ALS' AND DESCRIPTION = 'AEMT-P'");
//"SELECT UserID, CertificationID, '2013-06-10', '2014-06-10' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'falonso' AND CODE = 'MVO' AND DESCRIPTION IS NULL");
//"SELECT UserID, CertificationID, '2013-02-20', '2014-02-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'mwebber' AND CODE = 'CPR' AND DESCRIPTION IS NULL");
//"SELECT UserID, CertificationID, '2013-12-12', '2014-12-12' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'kraikkonen' AND CODE = 'BLS' AND DESCRIPTION IS NULL");
//"SELECT UserID, CertificationID, '2013-01-20', '2014-01-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'rgrosjean' AND CODE = 'ALS' AND DESCRIPTION = 'AEMT-CC'");
//"SELECT UserID, CertificationID, '2013-04-01', '2014-04-01' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'fmassa' AND CODE = 'MVO' AND DESCRIPTION IS NULL");
//"SELECT UserID, CertificationID, '2013-02-01', '2014-02-01' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'fmassa' AND CODE = 'BLS' AND DESCRIPTION IS NULL");
//"SELECT UserID, CertificationID, '2013-07-20', '2014-07-20' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'jbutton' AND CODE = 'MVO' AND DESCRIPTION IS NULL");
//"SELECT UserID, CertificationID, '2013-06-15', '2014-06-15' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'nhulkenberg' AND CODE = 'CPR' AND DESCRIPTION IS NULL");
//"SELECT UserID, CertificationID, '2013-01-10', '2014-01-10' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'asutil' AND CODE = 'MVO' AND DESCRIPTION IS NULL");
//"SELECT UserID, CertificationID, '2013-02-28', '2014-02-28' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'dricciardo' AND CODE = 'MVO' AND DESCRIPTION IS NULL");
//"SELECT UserID, CertificationID, '2013-04-30', '2014-04-30' FROM USERS JOIN CERTIFICATIONS WHERE USERNAME = 'cpic' AND CODE = 'MVO' AND DESCRIPTION IS NULL");
		
		Calendar date = Calendar.getInstance();
		date.clear();
		date.setTime(startDate.getTime());
		
		String insertString = "";
		
		while(date.before(endDate)) {
			for(PermanentShift shift : permanentShifts) {
				if(shift.daysOfWeek.contains(date.get(Calendar.DAY_OF_WEEK))) {
					String startDateString = getDateString(date)+" "+shift.startHour+":00";
					
					// logger.debug("username: "+shift.username+", roleCode: "+shift.roleCode+", startDateString: "+startDateString);
					
//					db.exec("INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) " +
//							"SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS " +
//							"WHERE STARTDATE = '"+startDateString+"' AND CODE = '"+shift.roleCode+"' AND USERNAME = '"+shift.username+"'");
					
					insertString += "INSERT INTO ShiftCrews (ShiftID, RoleID, UserID, Permanent) " +
							"SELECT SHIFTID, ROLEID, USERID, 1 FROM SHIFTS JOIN ROLES JOIN USERS " +
							"WHERE STARTDATE = '"+startDateString+"' AND CODE = '"+shift.roleCode+"' AND USERNAME = '"+shift.username+"'"+"; \n";
					
					if(db.getChanges() < 1) {
						logger.debug("changes: "+db.getChanges());
						logger.debug("username: "+shift.username+", roleCode: "+shift.roleCode+", startDateString: "+startDateString);
					}
				}
			}
			
			date.add(Calendar.DAY_OF_MONTH, 1);
		}
		
		db.exec(insertString);
		logger.debug(insertString);
	}
	
	private void addCoverRequests(SQLiteConnection db) throws SQLiteException {
		logger.debug("");

		db.exec("INSERT INTO CoverRequests (ShiftCrewID, OriginalUserID, DateRequested) " +
				"SELECT ShiftCrewID, U.UserID, '"+getDateString(Calendar.getInstance())+"' " +
				"FROM SHIFTCREWS SC JOIN SHIFTS S ON SC.SHIFTID = S.SHIFTID JOIN ROLES R ON SC.ROLEID = R.ROLEID JOIN USERS U " +
				"WHERE STARTDATE = '2014-02-06 19:00' AND R.CODE = 'MVO' AND USERNAME = 'mchilton'");
		
		logger.debug("INSERT INTO CoverRequests (ShiftCrewID, OriginalUserID, DateRequested) " +
				"SELECT ShiftCrewID, U.UserID, '"+getDateString(Calendar.getInstance())+"' " +
				"FROM SHIFTCREWS SC JOIN SHIFTS S ON SC.SHIFTID = S.SHIFTID JOIN ROLES R ON SC.ROLEID = R.ROLEID JOIN USERS U " +
				"WHERE STARTDATE = '2014-02-06 19:00' AND R.CODE = 'MVO' AND USERNAME = 'mchilton'");
	}
	
	private String getDateString(Calendar calendar) {
		String toReturn = calendar.get(Calendar.YEAR)+"-";
		
		if(calendar.get(Calendar.MONTH)+1 < 10) {
			toReturn += "0"+(calendar.get(Calendar.MONTH)+1)+"-";
		} else {
			toReturn += (calendar.get(Calendar.MONTH)+1)+"-";
		}
		
		if(calendar.get(Calendar.DAY_OF_MONTH) < 10) {
			toReturn += "0"+calendar.get(Calendar.DAY_OF_MONTH);
		} else {
			toReturn += calendar.get(Calendar.DAY_OF_MONTH);
		}
		
		return toReturn;
	}
	
	class PermanentShift {		
		private final String username, roleCode, startHour;
		private final Collection<Integer> daysOfWeek;
		
		private PermanentShift(String username, String roleCode, Collection<Integer> daysOfWeek, String startHour) {
			this.username = username;
			this.roleCode = roleCode;
			this.daysOfWeek = daysOfWeek;
			this.startHour = startHour;
		}
	}
}