//sql.js should be in the same folder see http://kripken.github.io/sql.js
<script src='sql.js'></script>
<input type="file" id="uploadedActivityReports">

<script>
//Create the database
var db = new SQL.Database();
/**
// Run a query without reading the results
db.run("CREATE TABLE test (col1, col2);");
// Insert two rows: (1,111) and (2,222)
db.run("INSERT INTO test VALUES (?,?), (?,?)", [1,111,2,222]);

// Prepare a statement
var stmt = db.prepare("SELECT * FROM test WHERE col1 BETWEEN $start AND $end");
stmt.getAsObject({$start:1, $end:1}); // {col1:1, col2:111}

// Bind new values
stmt.bind({$start:1, $end:2});
while(stmt.step()) { //
		var row = stmt.getAsObject();
		console.log(row)
		// [...] do something with the row of result
}
**/

document.getElementById("uploadedActivityReports").addEventListener("change",readUploadedActivityReports);

function readUploadedCsv(e,callback) {
	const reader = new FileReader()
	reader.onload = function(loadEvent) {
		const rawLines= reader.result.split(/\r?\n/).filter(line=>line.replace(/\s+/g).length>0)
		const cols=splitCsv(rawLines[0])
		callback(rawLines.slice(1).map(line=>splitCsv(line).reduce((accum,curr,index)=>Object.assign(accum,{[cols[index]]:curr}),{})))
	};
	if(e.target.files.length == 1) {
		reader.readAsText(e.target.files[0]);
	}
}

const dateReg=/^(\d\d?)\/(\d\d?)\/(\d\d\d\d)\s+\d\d?:\d\d?:\d\d?\s+(AM|PM)$/
const numReg=/^\d+$/
const floatReg=/^\d+\.\d+$/
const boolReg=/^(True|False)$/i
function getTypes(allRecords) {
	let types={}
	for(let i in allRecords) {
		for(let key in allRecords[i]) {
			if(types[key]) continue;
			let val=allRecords[i][key]
			if(val.length==0) continue;
			else if(dateReg.exec(val)) types[key]='date'
			else if(numReg.exec(val)) types[key]='integer'
			else if(floatReg.exec(val)) types[key]='float'
			else if(boolReg.exec(val)) types[key]='boolean'
			else types[key]='text'
		}
	}
	return types
}

function getColDeclarations(types) {
	return Object.keys(types).map(col=>`${col} ${types[col] =='boolean' ? 'integer':types[col]}`)
}
function getValueDeclarations(record,types) {
	function getDec(type,val) {
		if(val.length==0) {
			return 'NULL'
		}
		switch(type) {
			case 'date':
				let [match,month,day,year,ampm]=dateReg.exec(val)
				return `'${year}-${month}-${day}'`
			case 'integer':
				return parseInt(val)
			case 'float':
				return parseFloat(val)
			case 'boolean':
				return val.toLowerCase(val) =='true' ? 1 : 0
			default:
				return `'${val.replace(/'/g,'\'\'')}'`
		}
	}
	return Object.keys(types).map(col=>getDec(types[col],record[col]))
}
/**
 ** example function
**/
function readUploadedActivityReports(e) {
	const name='activity'
	makeTableFromFile(name,e,()=>{
		let stmt = db.prepare("SELECT count(*) as howMany FROM activity WHERE LastName=$LastName");
		let result=stmt.getAsObject({$LastName:'Pagenstecher'}); // {col1:1, col2:111}
		console.log(result)
	})
}
function makeTableFromFile(tableName,e,callback) {
	const exists=db.prepare(`SELECT name FROM sqlite_master WHERE type='table' AND name=$tableName`).getAsObject({$tableName:tableName}).name !== undefined
	if(exists) db.run(`DROP table ${tableName}`);
	readUploadedCsv(e,allRecords=>{
		const types=getTypes(allRecords)
		const createQuery=`CREATE table ${tableName} (${getColDeclarations(types).join(',')})`
		// console.log('create query is ',createQuery)
		db.run(createQuery)
		allRecords.forEach(record=>{
			let insertQuery=`INSERT into ${tableName} (${Object.keys(types).join(',')}) VALUES (${getValueDeclarations(record,types).join(`,`)})`
			// console.log('insert query is',insertQuery)
			db.run(insertQuery)
		})
		callback()
	})
}
function splitCsv(str) {
	return str.split(',').reduce((accum,curr)=>{
		if(accum.isConcatting) {
			accum.soFar[accum.soFar.length-1] += ','+curr
		} else {
			accum.soFar.push(curr)
		}
		if(curr.split('"').length % 2 == 0) {
			accum.isConcatting= !accum.isConcatting
		}
		return accum;
	},{soFar:[],isConcatting:false}).soFar
}
</script>