const pg = require('pg');
const sb = require('@supabase/supabase-js');
const { Pool } = pg;
const pool = new Pool()
// Emits errors on behalf of idle clients
pool.on('error', (err, client) => {
console.error('[~]Unexpected error on idle client', err)
process.exit(-1)
})
const supabase = sb.createClient(process.env.SUPABASE_URL, process.env.SUPABASE_SERVICE_ROLE_KEY, {
auth:{
persistSession: false,
autoRefreshToken: false,
detectSession: false,
}
});
// Checkout a client from the pool
// const client = await pool.connect()
// returns checked out client to the pool
// client.release()
// Just use pool.query() to query the database
/**
* Gets all marker data within the kpd_cip table
* @note Does not return any rows that do not have a specific address
* @returns JSON array of marker data
*/
function getKPDCIP(lat, lon, latDelt, lonDelt){
let latMin = lat - latDelt;
let latMax = lat + latDelt;
let lonMin = lon - lonDelt;
let lonMax = lon + lonDelt;
if(!lat, !lon, !latDelt, !lonDelt) return Promise.reject("Invalid parameters");
if(lat=""||lon==""||latDelt==""||lonDelt=="") return Promise.reject("Invalid parameters");
return new Promise((resolve, reject) => {
// let sql ="SELECT * FROM kpd_cip WHERE latitude BETWEEN ? AND ? AND longitude BETWEEN ? AND ?";
let sql = "SELECT * FROM kpd_cip WHERE address IS NOT ' Knoxville, TN';";
pool.query(sql).then((rows) =>{
resolve(rows.rows);
}).catch((err) => {
console.error("[-]", err);
reject(err);
})
});
}
/**
* Returns a single row of given id from the kpd_cip table
* @param {number} id
* @returns marker data
*/
function getCIPByID(id){
let sql = "SELECT * FROM kpd_cip WHERE id = $1 LIMIT 1;";
return new Promise((resolve, reject) => {
pool.query(sql, [id]).then((rows) => {
if(isNaN(+id)){
reject("Invalid parameter")
}else{
resolve(rows.rows[0]);
}
}).catch((err) => {
console.error("[-]", err);
reject(err);
})
});
}
/**
* Gets KCSO zones data by id
* @param {number} id
* @returns row of zone data
*/
function getZonesByID(id){
let sql = "SELECT * FROM knox_zones WHERE id = $1 LIMIT 1;";
return new Promise((resolve, reject) => {
pool.query(sql, [id]).then((rows, err) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(rows.rows[0]);
})
});
}
function getKPDCIPByLocation(lat, lon, date){
let sql = "SELECT * FROM kpd_cip WHERE latitude = $1 AND longitude = $2 AND date = $3 LIMIT 1;";
return new Promise((resolve, reject) => {
pool.query(sql, [lat, lon, date], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(rows.rows[0]);
})
});
}
/**
* Gets all data from default filter.
* @param {*} county No need to pass a value as this service is only available for Knox County. Default value is 'knox'
* @returns array of arrays of data in order for source 1-3
*/
function getDefaultCounty(county="knox"){
// Will later be used to actually get data from specific counties. Will now just only be used to get data from Knox County
// Maybe as this may stick to just knoxville or very few locations.
let final =[]
let sqlCIP =
"SELECT * FROM kpd_cip WHERE date > extract(epoch from (now()-interval '2 days')) AND class >= 2 AND class!='6';";
let sqlZones =
"SELECT * FROM knox_zones WHERE date > extract(epoch from (now()-interval '2 days')) AND class >= 2 AND class!='6';";
let sqlClery =
"SELECT * FROM utpd_clery WHERE reported > extract(epoch from (now()-interval '2 days')) AND class >=2 AND class!='6';";
return new Promise((resolve, reject) => {
pool.query(sqlCIP, (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
final.push(rows.rows);
pool.query(sqlZones, (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
final.push(rows.rows);
pool.query(sqlClery, (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
final.push(rows.rows);
resolve(final);
});
});
})
})
}
/**
* Gets the latest KPD CIP data from the database
* @param {*} lastCIP last cip date in unix time
* @returns array of marker JSON data
*/
function getLatestCIP(lastCIP){
let sql = `SELECT * FROM kpd_cip WHERE date > $1;`;
return new Promise((resolve, reject) => {
pool.query(sql, [lastCIP], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(rows.rows);
})
});
}
/**
* Gets custom filter data
* @param {*} dateS How many days back to get data. Options include: 2,4,7,14,30
* @param {*} class1 boolean to fetch class 1 data
* @param {*} sor boolean for sor | inactive
* @param {*} dog boolean for ddm | inactive
* @returns array of arrays
*/
function getCustomFilter(dateS, class1, dog){
// Checks to make sure dateS is passed as a valid option.
// Due to replacing instead of binding, this is a security risk.
// Look into fixing later, but sql params were being difficult
let defaultDates=["2","4","7","14","30"];
if(!defaultDates.includes(dateS)) return Promise.reject("Invalid date parameter");
let date = parseInt(dateS);
let final =[]
let sqlCIP =
"SELECT * FROM kpd_cip WHERE date > extract(epoch from (now()-interval '? days')) AND class >= 2 AND class != 6;";
sqlCIP = sqlCIP.replace("?", date);
if(class1){
sqlCIP = sqlCIP.replace(";", " UNION SELECT * FROM (SELECT * FROM kpd_cip WHERE class =1 AND date > extract(epoch from (now()-interval '? days')) AND class !=6 ORDER BY date DESC LIMIT 80);");
sqlCIP = sqlCIP.replace('?', date);
}
let sqlZones =
"SELECT * FROM knox_zones WHERE date > extract(epoch from (now()-interval '? days')) AND class >= 2 AND class != 6;";
sqlZones = sqlZones.replace("?", date);
if(class1){
sqlZones = sqlZones.replace(";", " UNION SELECT * FROM ( SELECT * FROM knox_zones WHERE class =1 AND date > extract(epoch from (now()-interval '? days')) AND class != 6 ORDER BY date DESC LIMIT 80);");
sqlZones=sqlZones.replace("?", date);
}
let sqlClery =
"SELECT * FROM utpd_clery WHERE reported > extract(epoch from (now()-interval '? days')) AND class >= 2 AND class != 6;";
sqlClery=sqlClery.replace("?", date);
if(class1){
sqlClery = sqlClery.replace(";", " UNION SELECT * FROM (SELECT * FROM utpd_clery WHERE class =1 AND reported > extract(epoch from (now()-interval '? days')) AND class != 6 ORDER BY reported DESC LIMIT 80);");
sqlClery=sqlClery.replace("?", date);
}
// console.log(sqlCIP, sqlZones, sqlClery)
return new Promise((resolve, reject) => {
pool.query(sqlCIP, (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
final.push(rows.rows);
pool.query(sqlZones, (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
final.push(rows.rows);
pool.query(sqlClery, (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
final.push(rows.rows);
resolve(final);
});
});
})
})
}
/**
* Function for incrementing a count in the counts table
* Usually used for monitoring api calls
* @param {*} countee item to be incremented
* @param {*} amount amount to be incremented by
* @returns Promise
*/
function incrementCount(countee, amount=1){
let sql = `UPDATE counts SET count = count + $1, date_updated = $2 WHERE "for" = $3;`;
let updatedDate = new Date().toISOString();
return new Promise((resolve, reject) => {
pool.query(sql, [amount, updatedDate, countee], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve("Updated count");
})
});
}
/**
* Updates class count for counts table
* @param {string} classNum number of the class count to be updated. i.e. 1-6
* @param {number} amount Amount to be incremented by. Default is 1
* @returns Promise with result of update
*/
function incrementClassCount(classNum, amount=1, monthly=true){
let sql = 'UPDATE counts SET count = count + $1, date_updated = $2 WHERE "for" in($3,$4);';
let noMonthly = 'UPDATE counts SET count = count + $1, date_updated = $2 WHERE "for" in($3);';
let updatedDate = new Date().toISOString();
let classParsed = "class"+classNum;
let mParsed = "mClass"+classNum;
return new Promise((resolve, reject) => {
if(monthly){
pool.query(sql, [amount, updatedDate,classParsed, mParsed], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve("Updated count");
})
}else{
pool.query(noMonthly, [amount, updatedDate,classParsed], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve("Updated count");
})
}
});
}
/**
* Ensures all class counts are in sync with what is in the database
* returns a promise
*/
function syncClassCounts(){
let kpd = `select class, count(*) from kpd_cip group by class order by "class";`;
let zones = `select class, count(*) from knox_zones group by class order by "class";`;
let clery = `select class, count(*) from utpd_clery group by class order by "class";`;
let update = `UPDATE counts SET count = (case when "for"='class0' then cast($1 as bigint)
when "for"='class1' then cast($2 as bigint)
when "for"='class2' then cast($3 as bigint)
when "for"='class3' then cast($4 as bigint)
when "for"='class4' then cast($5 as bigint)
when "for"='class5' then cast($6 as bigint)
when "for"='class6' then cast($7 as bigint)
when "for"='knox_zones' then cast($9 as bigint)
when "for"='kpd_cip' then cast($10 as bigint)
when "for"='utpd_clery' then cast($11 as bigint)
end),
date_updated = $8 WHERE "for" in('class0', 'class1', 'class2', 'class3', 'class4', 'class5', 'class6', 'knox_zones', 'kpd_cip', 'utpd_clery');`;
let updatedDate = new Date().toISOString();
let classes = {0:0, 1:0, 2:0, 3:0, 4:0, 5:0, 6:0};
return new Promise((resolve, reject) => {
pool.query(kpd, (err, row) => {
if(err){
console.error("[-]", err);
reject(err);
}
let rows = row.rows;
classes[0] = parseInt(rows[0].count);
classes[1] = parseInt(rows[1].count);
classes[2] = parseInt(rows[2].count);
classes[3] = parseInt(rows[3].count);
classes[4] = parseInt(rows[4].count);
classes[5] = parseInt(rows[5].count);
classes[6] = parseInt(rows[6].count);
pool.query(zones, (err, Zonerows) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(Zonerows.rows.length ==0){
for(let i = 0; i<7; i++){
Zonerows.rows.push({class:i, count:0});
}
} else if(Zonerows.rows.length !=7){
for(let i = 0; i<7; i++){
if(Zonerows.rows[i].class != i){
Zonerows.rows.splice(i, 0, {class:i, count:0});
}
}
}
classes[0] += parseInt(Zonerows.rows[0].count);
classes[1] += parseInt(Zonerows.rows[1].count);
classes[2] += parseInt(Zonerows.rows[2].count);
classes[3] += parseInt(Zonerows.rows[3].count);
classes[4] += parseInt(Zonerows.rows[4].count);
classes[5] += parseInt(Zonerows.rows[5].count);
classes[6] += parseInt(Zonerows.rows[6].count);
pool.query(clery, (err, Cleryrows) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(Cleryrows.rows.length ==0){
for(let i = 0; i<7; i++){
Cleryrows.rows.push({class:i, count:0});
}
}else if(Cleryrows.rows.length !=7){
for(let i = 0; i<7; i++){
if(Cleryrows.rows[i].class != i){
Cleryrows.rows.splice(i, 0, {class:i, count:0});
}
}
}
classes[0] += parseInt(Cleryrows.rows[0].count);
classes[1] += parseInt(Cleryrows.rows[1].count);
classes[2] += parseInt(Cleryrows.rows[2].count);
classes[3] += parseInt(Cleryrows.rows[3].count);
classes[4] += parseInt(Cleryrows.rows[4].count);
classes[5] += parseInt(Cleryrows.rows[5].count);
classes[6] += parseInt(Cleryrows.rows[6].count);
let zoneCount = parseInt(Zonerows.rows[0].count) + parseInt(Zonerows.rows[1].count) + parseInt(Zonerows.rows[2].count) +parseInt(Zonerows.rows[3].count) +parseInt(Zonerows.rows[4].count) +parseInt(Zonerows.rows[5].count) +parseInt(Zonerows.rows[6].count);
let cipCount = parseInt(rows[0].count) + parseInt(rows[1].count) + parseInt(rows[2].count) +parseInt(rows[3].count) +parseInt(rows[4].count) +parseInt(rows[5].count) +parseInt(rows[6].count);
let utCount = parseInt(Cleryrows.rows[0].count) + parseInt(Cleryrows.rows[1].count) + parseInt(Cleryrows.rows[2].count) +parseInt(Cleryrows.rows[3].count) +parseInt(Cleryrows.rows[4].count) +parseInt(Cleryrows.rows[5].count) +parseInt(Cleryrows.rows[6].count);
pool.query(update, [classes[0], classes[1], classes[2], classes[3], classes[4], classes[5], classes[6], updatedDate, zoneCount, cipCount, utCount], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(JSON.stringify(classes));
})
})
})
})
})
}
/**
* Resets the monthly count for all classes
* @returns Promise with result of reset
*/
function resetMonthlyCount(){
// SQL QUERIES
let grab =`SELECT * FROM counts WHERE "for" in('mClass0', 'mClass1', 'mClass2', 'mClass3', 'mClass4', 'mClass5', 'mClass6');`;
let ins = `INSERT INTO counts("for", count, date_updated) VALUES (?,$1,$2), (?,$3,$4), (?,$5,$6), (?,$7,$8), (?,$9,$10), (?,$11,$12), (?,$13,$14);`;
let sql = `UPDATE counts SET count = 0, date_updated=$1 WHERE "for" in('mClass0', 'mClass1', 'mClass2', 'mClass3', 'mClass4', 'mClass5', 'mClass6');`;
let updatedDate = new Date().toISOString();
return new Promise((resolve, reject) => {
pool.query(grab, (err, row) => {
if(err){
console.error(err);
reject(err);
}
// Sets all counts in temporary variables
let c0, c1, c2, c3, c4, c5, c6;
let rows = row.rows;
for(let i =0;i<rows.length;i++){
switch(parseInt(rows[i].id)){
case 20:
c0 = rows[i].count;
break;
case 19:
c1 = rows[i].count;
break;
case 18:
c2 = rows[i].count;
break;
case 17:
c3 = rows[i].count;
break;
case 16:
c4 = rows[i].count;
break;
case 15:
c5 = rows[i].count;
break;
case 14:
c6 = rows[i].count;
break;
default:
break;
}
}
// Sets date to previous month
let mDate = new Date();
mDate = new Date(mDate.setMonth(mDate.getMonth()-1));
const months = ["January","February","March","April","May","June","July","August","September","October","November","December"];
let month = months[mDate.getMonth()];
let year = mDate.getFullYear()
let c0s = 'Class0'+month+year;
let c1s = 'Class1'+month+year;
let c2s = 'Class2'+month+year;
let c3s = 'Class3'+month+year;
let c4s = 'Class4'+month+year;
let c5s = 'Class5'+month+year;
let c6s = 'Class6'+month+year;
ins = ins.replace("?", JSON.stringify(c0s).replaceAll('"', "'"));
ins = ins.replace("?", JSON.stringify(c1s).replaceAll('"', "'"));
ins = ins.replace("?", JSON.stringify(c2s).replaceAll('"', "'"));
ins = ins.replace("?", JSON.stringify(c3s).replaceAll('"', "'"));
ins = ins.replace("?", JSON.stringify(c4s).replaceAll('"', "'"));
ins = ins.replace("?", JSON.stringify(c5s).replaceAll('"', "'"));
ins = ins.replace("?", JSON.stringify(c6s).replaceAll('"', "'"));
// Inserts counts for previous months into the counts table
pool.query(ins, [c0, updatedDate, c1, updatedDate, c2, updatedDate, c3, updatedDate, c4, updatedDate, c5, updatedDate, c6, updatedDate], (err) => {
if(err){
console.error("[-]Error inserting prev month counts:",err);
reject(err);
}
// Resets all monthly counts to 0
pool.query(sql,[updatedDate] ,(err) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve("Monthly count reset");
});
})
})
});
}
function getStatsByLocation(lat, lon){
let sql = `SELECT * FROM kpd_cip WHERE ST_DWithin(geo::geography, ST_MakePoint($1,$2),2*1609.34) AND date > extract(epoch from (now()-interval '2 days'))`;
let counts = `SELECT "count","for" FROM counts WHERE "for" in('Class1$1', 'Class2$1', 'Class3$1', 'Class4$1', 'Class5$1', 'mClass1', 'mClass2', 'mClass3', 'mClass4', 'mClass5');`;
let months = ["January","February","March","April","May","June","July","August","September","October","November","December"];
let month = new Date().getMonth()-1
let year = new Date().getFullYear();
if(month==-1){
month = 11;
year = year-1;
}
let date = months[month]+year;
counts = counts.replaceAll("$1", date);
return new Promise((resolve, reject) => {
pool.query(sql, [lon, lat], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
pool.query(counts, (err, counts) => {
if(err){
console.error("[-]", err);
reject(err);
}
let cc = rows.rows
resolve({data:rows.rows, counts:counts.rows});
})
})
})
}
/**
* Returns all crime stats from the counts table
* @param {string} county county to get stats for. Default is knox
* @returns Promise with result of query
*/
function getIntegrationStats(county="knox"){
let sql = `SELECT "count","for" FROM counts WHERE "for" in('kpd_cip', 'knox_zones', 'utpd_clery', 'class1', 'class2', 'class3', 'class4', 'class5', 'mClass1', 'mClass2', 'mClass3', 'mClass4', 'mClass5');`;
return new Promise((resolve, reject) => {
pool.query(sql, (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(rows.rows);
})
});
}
/**
* Gets kpd cip data within 2 miles and within 2 days
* @param {*} lat
* @param {*} lon
* @returns crime data
*/
function getLocalTemperalData(lat, lon){
let sql = `SELECT * FROM kpd_cip WHERE ST_DWithin(geo::geography, ST_MakePoint($1,$2),2*1609.34) AND date > extract(epoch from (now()-interval '2 days'));`;
return new Promise((resolve, reject) => {
pool.query(sql, [lon, lat], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(rows.rows);
})
});
}
/**
* Gets current and previous monthly counts
* @returns counts
*/
function getMonthlyStats(){
let sql = `SELECT "count","for" FROM counts WHERE "for" in('mClass1', 'mClass2', 'mClass3', 'mClass4', 'mClass5', 'Class1$1', 'Class2$1', 'Class3$1', 'Class4$1', 'Class5$1');`;
let months = ["January","February","March","April","May","June","July","August","September","October","November","December"];
let month = new Date().getMonth()-1
let year = new Date().getFullYear();
if(month==-1){
month = 11;
year = year-1;
}
let date = months[month]+year;
sql = sql.replaceAll("$1", date);
return new Promise((resolve, reject) => {
pool.query(sql,(err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(rows.rows);
})
})
}
/**
* Creates a new user report
* @param {string} uid
* @param {string} incident
* @param {string} description
* @param {number} cl
* @param {string} address
* @param {number} lat
* @param {number} long
* @returns Promise with success message on success
*/
function createUserReport(uid, incident, description, cl, address, lat, long){
return new Promise((resolve, reject) => {
createEntity(uid, "user_reports").then((eid) => {
let date = new Date().toISOString();
getAlias(uid).then((alias) => {
let sql = "INSERT INTO user_reports(id, uid, incident, description, class, address, latitude, longitude, created_at, alias, verified) VALUES($1,$2,$3,$4,$5,$6,$7,$8, $9, $10, FALSE);";
pool.query(sql, [eid, uid, incident, description, cl, address, lat, long, date, alias], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve("User report created");
})
}).catch((err) => {
console.error("[-]", err);
reject(err);
})
})
})
}
/**
* Gets user report by id
* @param {number} id
* @returns row data of user report
*/
function getUserReport(id){
let sql = "SELECT id,incident,class,address,latitude,longitude,date,description,created_at,geo FROM user_reports WHERE id = $1 LIMIT 1;";
return new Promise((resolve, reject) => {
pool.query(sql, [id], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(rows.rows[0]);
})
})
}
/**
* Gets all user reports by a specific user
* @param {number} uid
* @returns array of row data
*/
function getURByUID(uid){
let sql = "SELECT * FROM user_reports WHERE uid = $1;";
return new Promise((resolve, reject) => {
pool.query(sql, [uid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(rows.rows);
})
})
}
/**
* Checks and sets whether a post should be verified
* @param {*} uid
* @param {*} eid
* @returns promise with success message on success
*/
function checkVerified(uid, eid){
let sql = "SELECT verified FROM user_reports WHERE uid = $1 AND id = $2 LIMIT 1;";
let update = "UPDATE user_reports SET verified = $1 WHERE uid = $2 AND id = $3;";
return new Promise((resolve, reject) => {
getURVerificationCount(eid).then((counts) => {
pool.query(sql, [uid, eid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(rows.rows.length == 0){
reject("No user report found with that id");
}
if(counts.total > 3){
//If post if not already verified with 3+ verifications, then verify it
if(!rows.rows[0].verified){
pool.query(update, [true, uid, eid], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve("User report:",eid,"has been verified");
})
}
}else{
//If post has 3 or less verifications, then unverify it
if(rows.rows[0].verified){
pool.query(update, [false, uid, eid], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve("User report:",eid,"has been unverified");
})
}
}
resolve("No changes to user report verification status", eid);
})
}).catch((err) => {
console.error("[-]", err);
reject(err);
})
})
}
/**
* Creates new user report verification
* @param {string} uid
* @param {number} pid
* @returns promise with success message on success
*/
function createURValidation(uid, pid){
let sql = 'INSERT INTO uvalidations(uid, eid, created_at) VALUES($1,$2,$3);';
return new Promise((resolve, reject) => {
pool.query(sql, [uid, pid, new Date().toISOString()], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
checkVerified(uid, pid).catch((err) => {
console.error("[-]", err);
})
updateUserVerificationScoreFromEID(pid, 1).catch((err) => {
console.error("[-]", err);
})
resolve("Validation created");
})
})
}
/**
* Deletes a user report verification
* @param {string} uid
* @param {number} pid
* @returns promise with success message on success
*/
function deleteURValidation(uid, pid){
let sql = 'DELETE FROM uvalidations WHERE uid = $1 AND eid = $2;';
return new Promise((resolve, reject) => {
pool.query(sql, [uid, pid], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
checkVerified(uid, pid).catch((err) => {
console.error("[-]", err);
})
updateUserVerificationScoreFromEID(pid, -1).catch((err) => {
console.error("[-]", err);
})
resolve("Validation deleted");
})
})
}
/**
* Creates user report contest
* @param {string} uid
* @param {number} pid
* @returns promise with success message on success
*/
function createURContest(uid, pid){
let sql = 'INSERT INTO ucontests(uid, eid, created_at) VALUES($1,$2,$3);';
return new Promise((resolve, reject) => {
pool.query(sql, [uid, pid, new Date().toISOString()], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
checkVerified(uid, pid).catch((err) => {
console.error("[-]", err);
})
updateUserVerificationScoreFromEID(pid, -1).catch((err) => {
console.error("[-]", err);
})
resolve("Contest created");
})
})
}
/**
* Deletes user report contest
* @param {string} uid
* @param {number} pid
* @returns promise with success message on success
*/
function deleteURContest(uid, pid){
let sql = 'DELETE FROM ucontests WHERE uid = $1 AND eid = $2;';
return new Promise((resolve, reject) => {
pool.query(sql, [uid, pid], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
checkVerified(uid, pid).catch((err) => {
console.error("[-]", err);
})
updateUserVerificationScoreFromEID(pid, 1).catch((err) => {
console.error("[-]", err);
})
resolve("Contest deleted");
})
})
}
/**
* Gets contest and verification count for a user report
* @param {number} pid
* @returns object that contains verifications, contests, and total.
*/
function getURVerificationCount(pid){
let sql = 'SELECT COUNT(*) FROM uvalidations WHERE eid = $1;';
let contest = 'SELECT COUNT(*) FROM ucontests WHERE eid = $1;';
return new Promise((resolve, reject) => {
pool.query(sql, [pid], (err, verifications) => {
if(err){
console.error("[-]", err);
reject(err);
}
pool.query(contest, [pid], (err, contests) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(verifications.rows.length == 0 || contests.rows.length == 0){
return reject("No user report found with that id");
}else{
let total = parseInt(verifications.rows[0].count) + parseInt(contests.rows[0].count);
resolve({verifications:parseInt(verifications.rows[0].count), contests:parseInt(contests.rows[0].count), total:total});
}
})
})
})
}
//------------------------USER FUNCTIONS-----------------------------------------------------------------------------------------------------------------//
//Array for alias generation
//Later it may do a random db call for alias generation
let aliasOptions = [
"Firecrackers",
"Flooded road",
"Fire event",
"School bus wreck",
"Fire",
"Welfare check",
"Wreck with property damage",
"Doe drill",
"Lines down",
"Fire alarm",
"Accident on waterway",
"Lost or found property",
"Emergency medical services",
"Motor vehicle accident",
"Noise disturbance",
"Private pull",
"Property check",
"Public drunk",
"Reposessed vehicle",
"Standby",
"Phone report",
"Traffic problem",
"Tree down in road",
"Unwarranted call information",
"User request for information",
"Want officer for investigation",
"Wreck injury",
"Wreck property damage",
"Humane",
"Injured person",
"Law enforcement requested",
"Location detail - only",
"Scan unit check",
"Be on lookout",
"Bolo",
"Pole down",
"Mental transport",
"Mental transport longhaul",
"Pick up prisoner",
"Public assist",
"Unwarranted call utility",
"Convey/escort",
"Injured child <5",
"Traffic light out",
"Haz mat",
"Business check",
"Vandalism progress",
"Warrant service",
"Disturbance",
"Alarm",
"Shoplifter",
"Business hold up alarm",
"Unknown overdose",
"Vandalism",
"Fight",
"Disturbance in progress",
"Forgery/fraud",
"Hit and run",
"Hit & run w/injury",
"Hit & run",
"Suspicious person",
"Drunk driver",
"Missing person",
"Urgent forgery & fraud in progress",
"Indecent exposure",
"Prowler on premises",
"Theft",
"Assault",
"Vehicle burglary",
"Drunk driver in progress",
"Stolen vehicle",
"Stolen vehicle in progress",
"Assault medical attention required",
"Business burglary",
"Attempted business burglary",
"Attempted residence burglary",
"Attempted residence burglary in progress",
"Theft in progress",
"Business burglary in progress",
"Residence burglary",
"Residence burglary in progress",
"Shooting scrape",
"Shooting",
"Shooting in progress",
"Stabbing in progress",
"Murder",
"Murder in progress",
"Attempted murder",
];
/**
* Creates new user within sherlock database
* @param {string} uid
* @returns Promise with success message on success.
*/
function createUser(uid){
let sql = "INSERT INTO users(id, post_count, verifiedpost_count, comment_count, like_count, verification_score, alias, alias_expire, role, date_created, userreport_count, dislike_count) VALUES($1,0,0,0,0,0, $2, $3, 'member', $4,0,0);";
// Aliases will expire after 7 days time.
let expire = new Date();
expire = new Date(expire.setDate(expire.getDate()+7));
let alias = aliasOptions[Math.floor(Math.random() * (aliasOptions.length))];
return new Promise((resolve, reject)=>{
pool.query(sql, [uid, alias, expire, new Date().toISOString()], (err) => {
if(err){
console.error("[-]", err)
reject(err)
}
subscribe(uid, ['crime_high','crime_medium','social_comment','social_upvote_post', 'social_upvote_comment', 'social_remove_comment','social_remove_post'])
resolve("New user with id: "+uid+" has been added to the sherlock database.")
})
})
}
/**
* Deletes user within sherlock api
* @param {string} uid
* @returns Promise with success message on success
*/
function deleteUser(uid){
let sql = "DELETE FROM users WHERE id = $1";
return new Promise(async (resolve, reject)=>{
const {error} = await supabase.auth.admin.deleteUser(uid);
if(error){
console.error("[-]Error deleting supabase user:", error)
reject(error)
return
}
pool.query(sql, [uid], (err) => {
if(err){
console.error("[-]", err)
reject(err)
}
resolve("Success, user: "+uid+" has been deleted from Sherlock database.")
})
})
}
/**
* Grabs info on a user from the database
* @param {string} uid
* @returns json obect of data from users table
*/
function getUser(uid){
let sql = 'SELECT * FROM users WHERE id = $1 LIMIT 1;';
console.log("UID: ", uid);
return new Promise((resolve, reject) => {
pool.query(sql, [uid], (err, rows) => {
if(err){
console.error("[-]", err)
reject(err)
}
if(rows.rows.length == 0){
reject("No user found with that id");
}else{
resolve(rows.rows[0]);
}
})
})
}
/**
* Gets a users specific role
* @param {string} uid
* @returns string of role
*/
function getUserRole(uid){
let sql = "SELECT role FROM users WHERE id = $1 LIMIT 1;";
return new Promise((resolve, reject) => {
pool.query(sql, [uid], (err, rows) => {
if(err){
console.error("[-]", err)
reject(err)
}
if(rows.rows.length == 0){
reject("No user found with that id");
}else{
resolve(rows.rows[0].role);
}
})
})
}
/**
* Updates designated users verification score
* @param {string} uid
* @param {number} score the amount to be added. can be negative
* @returns promise with success message on success
*/
function updateUserVerificationScore(uid, score){
let sql = "UPDATE users SET verification_score = verification_score + $1 WHERE id = $2;";
return new Promise((resolve, reject) => {
pool.query(sql, [score, uid], (err) => {
if(err){
console.error("[-]", err)
reject(err)
}
resolve("Updated verification score for user: " + uid);
})
});
}
/**
* Updates user verification score, but from an entity id
* @param {number} eid
* @param {number} score number to be added aka offset
* @returns promise with success message on success
*/
function updateUserVerificationScoreFromEID(eid, score){
let sql = "UPDATE users SET verification_score = verification_score + $1 WHERE id = (SELECT owned_by FROM entity WHERE id = $2);";
return new Promise((resolve, reject) => {
pool.query(sql, [score, eid], (err) => {
if(err){
console.error("[-]", err)
reject(err)
}
resolve("Updated verification score for user: " + eid);
})
})
}
/**
* Gets all the user count values
* @param {string} uid
* @returns Promise with user counts
*/
function getUserCounts(uid){
let sql = "SELECT post_count, verifiedpost_count, comment_count, like_count, verification_score, dislike_count FROM users WHERE id = $1 LIMIT 1;";
return new Promise((resolve, reject) => {
pool.query(sql, [uid], (err, rows) => {
if(err){
console.error("[-]", err)
reject(err)
}
if(rows.rows.length == 0){
reject("No user found with that id");
}else{
resolve(rows.rows[0]);
}
})
});
}
/**
* Increments a user count by 1
* @param {string} uid user id
* @param {string} type Options include: post, verifiedPost, comment, like, urcount
* @returns Promise with string on success.
*/
function incrementUserCount(uid, type){
let sql = "UPDATE users SET ? = ? + 1 WHERE id = $1;";
let uct;
// Sets the count type to be updated
return new Promise((resolve, reject) => {
switch(type){
case "post":
uct = "post_count";
break;
case "verifiedpost":
uct = "verifiedpost_count";
break;
case "comment":
uct = "comment_count";
break;
case "like":
uct = "like_count";
break;
case "dislike":
uct = "dislike_count";
break;
case "urcount":
uct = "userreport_count";
break;
case "strike":
uct = "strike_count";
break;
case "reported":
uct = "reported_count";
break;
default:
return reject("Invalid type "+ type);
}
sql = sql.replaceAll("?", uct);
pool.query(sql, [uid], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve("Updated " + type + " count.");
});
});
}
/**
* Decrement a user count by 1
* @param {*} uid
* @param {*} type
* @returns promise with success message on success
*/
function decrementUserCount(uid, type){
let sql = "UPDATE users SET ? = ? - 1 WHERE id = $1;";
let uct;
return new Promise((resolve, reject) => {
switch(type){
case "posts":
uct = "post_count";
break;
case "reportposts":
uct="post_count";
break;
case "verifiedpost":
uct = "verifiedpost_count";
break;
case "comment":
uct = "comment_count";
break;
case "like":
uct = "like_count";
break;
case "dislike":
uct = "dislike_count";
break;
case "user_reports":
uct = "userreport_count";
break;
default:
return reject("Invalid type "+ type);
}
sql = sql.replaceAll("?", uct);
pool.query(sql, [uid], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve("Updated " + type + " count.");
});
});
}
/**
* Generates a new alias for a user
* @param {string} uid
* @returns Promise with new alias
*/
function renewAlias(uid){
let sql = "UPDATE users SET alias = $1, alias_expire = $2 WHERE id = $3 RETURNING alias;";
let expire = new Date();
expire = new Date(expire.setDate(expire.getDate()+7));
let alias = aliasOptions[Math.floor(Math.random()* aliasOptions.length)];
return new Promise((resolve, reject) => {
pool.query(sql, [alias, expire, uid], (err, row) => {
if(err){
console.error("[-]", err)
reject(err);
}
if(row.rows.length == 0){
reject("No user found with that id");
}else{
resolve(row.rows[0].alias);
}
})
});
}
/**
* Gets a users alias from the database. Sets one if current is expired.
* @param {string} userid id of the user to get alias for
* @returns Promise with alias of user
*/
function getAlias(userid){
let sql = "SELECT * FROM users WHERE id = $1 LIMIT 1;";
return new Promise((resolve, reject) => {
pool.query(sql, [userid]).then((row) => {
let alias = row.rows[0].alias;
let expire = new Date(row.rows[0].alias_expire).getTime();
let now = new Date().getTime();
// Updates alias if expired
if(now > expire){
renewAlias(userid).then((newAlias) => {
resolve(newAlias);
}).catch((err) => {
console.log("[-] Getting alias error: ", err);
reject(err);
})
}else{
resolve(alias);
}
}).catch((err) => {
console.log("[-] Getting alias error: ", err);
reject(err);
})
});
}
//----------------------------------------------------------------- Social Media Functions -----------------------------------------------------------------//
/**
* Creates a new entity in the entity table.
* @note This is required before creating any new report
*/
function createEntity(uid, obj){
let sql = 'INSERT INTO entity(id, owned_by, likes, comments, obj) VALUES(DEFAULT, $1, 0, 0, $2) RETURNING id;';
return new Promise((resolve, reject) => {
pool.query(sql,[uid, obj] ,(err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(rows.rows[0].id)
})
})
}
/**
* Deletes entry from entity table
* @param {number} eid
* @param {string} uid
* @returns promise with success message on success
*/
function deleteEntity(eid, uid){
let sql = `DELETE FROM entity WHERE id = $1 AND owned_by = $2 RETURNING obj;`;
return new Promise((resolve, reject) => {
pool.query(sql, [eid, uid], (err, result) => {
if(err){
console.error("[-]", err);
reject(err);
}
decrementUserCount(uid, result.rows[0].obj).catch((err) => {
console.error("[-]", err);
})
resolve("Entity deleted", eid);
})
})
}
/**
* Gets the like and comment count for an entity
* @param {number} eid
* @returns json object with like and comment count
*/
function getEntityCounts(eid){
let sql = 'SELECT likes, comments, owned_by FROM entity WHERE id = $1 LIMIT 1;';
return new Promise((resolve, reject) => {
pool.query(sql, [eid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
//Deletes post when the like count is less than -5
if(rows.rows[0].likes < -5){
//Makes sure it is not an official entity
if(rows.rows[0].owned_by != 'sherlock'){
deleteEntity(eid, rows.rows[0].owned_by).catch((err) => {
console.error("[-]", err);
})
reject("Entity no longer exists.");
return
}
}
if(rows.rows.length == 0){
reject("No entity found with that id");
}else{
// Removes owned_by from the object from response for anonymity
delete rows.rows[0].owned_by;
resolve(rows.rows[0]);
}
})
})
}
/**
* Function for updating entity counts
* @param {number} eid
* @param {string} type current counts are likes and comments
* @returns success message on success
*/
function updateEntityCount(eid, type, operation){
let sql = "UPDATE entity SET ? = ? ! 1 WHERE id = $1;";
let ect;
return new Promise((resolve, reject) => {
switch(type){
case "likes":
ect = "likes";
break;
case "comments":
ect = "comments";
break;
default:
return reject("Invalid type "+ type);
}
switch(operation){
case "inc":
sql = sql.replace("!", "+");
break;
case "dec":
sql = sql.replace("!", "-");
break;
default:
return reject("Invalid operation");
}
sql = sql.replaceAll("?", ect);
pool.query(sql, [eid], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve("Updated " + type + " count.");
});
});
}
/**
* Helper function to hide an entity from a user
* @param {*} eid entity id
* @returns success message on success
*/
function hideEntity(eid){
let sql = 'UPDATE entity SET hidden = TRUE WHERE id = $1;';
return new Promise((resolve, reject)=>{
pool.query(sql, [eid], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve("Entity hidden");
})
})
}
/**
* Reports an entity to the moderation team
* @param {*} eid entity id of post to report
* @param {*} uid user id of reporter
* @param {*} reason reason for reporting
* @returns success message on success
*/
function reportEntity(eid, uid, reason){
let sql = 'INSERT INTO post_moderation(eid, reporter, reason, created_at) VALUES($1,$2,$3,$4);';
let reported = 'UPDATE entity SET reported = reported + 1 WHERE id = $1 RETURNING reported;';
return new Promise((resolve, reject) => {
pool.query(sql, [eid, uid, reason, new Date().toISOString()], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
pool.query(reported, [eid], (rows, err) => {
if(rows.rows.length == 0){
reject("No entity found with that id");
}else if(rows.rows[0].reported > 3){
hideEntity(eid).catch((err) => {
console.error("[-]", err);
})
}
})
resolve("Entity reported");
})
})
}
/**
* Helper function to hide a comment
* @param {*} cid comment id
* @returns success message on success
*/
function hideComment(cid){
let sql = 'UPDATE comments SET hidden = TRUE WHERE cid = $1;';
return new Promise((resolve, reject) => {
pool.query(sql, [cid], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve("Comment hidden");
})
})
}
/**
*
* @param {*} cid comment id
* @param {*} uid user id of reporter
* @param {*} reason reason for reporting
* @returns success message on success
*/
function reportComment(cid, uid, reason){
let sql = 'INSERT INTO comment_moderation(cid, reporter, reason, created_at) VALUES($1,$2,$3,$4);';
let reported = 'UPDATE comments SET reported = reported + 1 WHERE cid = $1 RETURNING reported;';
return new Promise((resolve, reject) => {
pool.query(sql, [cid, uid, reason, new Date().toISOString()], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
pool.query(reported, [cid], (rows, err) => {
if(rows.rows.length == 0){
reject("No comment found with that id");
}else if(rows.rows[0].reported > 3){
hideComment(cid).catch((err) => {
console.error("[-]", err);
})
}
})
resolve("Comment reported");
})
})
}
/**
* Gets all moderation reports for a user
* @param {*} uid user's id
* @returns object of {entities:[], comments:[]} where entities is an array of posts and comments is an array of comments
*/
function getReports(uid){
let entity = 'SELECT eid, body, created_at, alias, jurisdiction FROM posts WHERE uid = $1 ORDER BY created_at DESC;';
let comment = 'SELECT id, eid, body, created_at, alias FROM entitycomment WHERE uid = $1 ORDER BY created_at DESC;';
return new Promise((resolve, reject) => {
pool.query(entity, [uid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(rows.rows.length == 0){
reject("No posts found for that user");
}else{
pool.query(comment, [uid], (err, rows2) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve({entities:rows.rows, comments:rows2.rows});
})
}
})
})
}
// ----------------- POST FUNCTIONS ----------------- //
/**
* Creates a new post
* @param {string} uid
* @param {string} body
* @returns id of the new post
*/
function createPost(uid, body){
return new Promise((resolve, reject) => {
createEntity(uid, 'posts').then((eid) => {
let date = new Date().toISOString();
let po = `INSERT INTO posts(eid, uid, body, created_at, alias) VALUES($1,$2,$3,$4, $5) RETURNING *;`;
getAlias(uid).then((alias) => {
pool.query(po, [eid, uid, body, date, alias], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(rows.rows.length == 0){
reject("Could not create post");
}else{
incrementUserCount(uid, "post").catch((err) => {
console.error("[-] Creating post error:", err);
})
resolve(rows.rows[0]);
}
})
}).catch((err) => {
console.log("[-]Creating post error:", err);
reject(err);
})
}).catch((err) => {
console.log("[-]Creating post error:", err);
reject(err);
})
})
}
/**
* Updates a post with new body
* @param {number} eid
* @param {string} uid
* @param {string} body
* @returns promise with success message on success
*/
function editPost(eid, uid, body){
let sql = 'UPDATE posts SET body = $1 WHERE eid = $2 AND uid = $3;';
return new Promise((resolve, reject) => {
pool.query(sql, [body, eid, uid], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve("Post edited");
})
})
}
/**
* Creates post with reference to a report
* @param {string} uid
* @param {number} rid
* @param {string} body
* @returns promise with id of new post
*/
function createReportPost(uid, rid, body){
return new Promise((resolve, reject) => {
createEntity(uid, "reportposts").then((eid) => {
let date = new Date().toISOString();
let repo = 'INSERT INTO reportposts(eid, uid, rid, body, created_at, alias) VALUES($1,$2,$3,$4,$5, $6) RETURNING eid;';
getAlias(uid).then((alias) => {
pool.query(repo, [eid, uid, rid, body, date, alias], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(rows.rows.length == 0){
reject("Could not create post");
}else{
incrementUserCount(uid, "post").catch((err) => {
console.error("[-]", err);
})
resolve(rows.rows[0].eid);
}
})
})
}).catch((err) => {
reject(err);
})
})
}
/**
* Edits a report post with new body
* @param {number} eid
* @param {string} uid
* @param {string} body
* @returns promise with success message on success
*/
function editReportPost(eid, uid, body){
let sql = 'UPDATE reportposts SET body = $1 WHERE eid = $2 AND uid = $3;';
return new Promise((resolve, reject) => {
pool.query(sql, [body, eid, uid], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve("Post edited");
})
})
}
/**
* Gets a post by its id
* @param {number} eid
* @returns row of post data
*/
function getPost(eid){
let sql = 'SELECT * FROM posts WHERE eid = $1 LIMIT 1;';
return new Promise((resolve, reject) => {
pool.query(sql, [eid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(rows.rows.length == 0){
reject("No post found with that id");
}if(rows.rows[0].hidden){
reject("Post has been hidden");
}else{
resolve(rows.rows[0]);
}
})
})
}
/**
* Same as getPost. Gets a report post by its id
* @param {number} eid
* @returns row of report post data
*/
function getReportPost(eid){
let sql = 'SELECT eid,body,created_at,alias,rid FROM reportposts WHERE eid = $1 LIMIT 1;';
return new Promise((resolve, reject) => {
pool.query(sql, [eid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(rows.rows.length == 0){
reject("No post found with that id");
}else{
resolve(rows.rows[0]);
}
})
})
}
/**
* Gets posts after older than an eid
* @param {integer} id of the post to decrement from. Will return posts less recent than id
* @returns 20 post rows
*/
function getPosts(id){
let sql = 'SELECT * FROM posts WHERE eid < $1 ORDER BY eid DESC LIMIT 10;';
return new Promise((resolve, reject) => {
pool.query(sql, [id], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
for(let i = 0; i < rows.rows.length; i++){
if(rows.rows[i].hidden){
rows.rows.splice(i, 1);
i--;
}
}
resolve(rows.rows);
})
})
}
/**
* Same as getPosts.Gets posts older than an id
* @param {number} id
* @returns 20 report post rows
*/
function getReportPosts(id){
let sql = 'SELECT eid,body,created_at,alias,rid FROM reportposts WHERE eid < $1 ORDER BY eid DESC LIMIT 10;';
return new Promise((resolve, reject) =>{
pool.query(sql, [id], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(rows.rows);
})
})
}
/**
* Basic timeline alg for now. Grabs all posts
* @param {number} pid lowest id between posts and reportposts
* @returns object with posts and reportposts fields
*/
function getFeed(pid, newest){
let sql = `SELECT * FROM posts WHERE eid > $2 AND eid < $1 ORDER BY eid DESC LIMIT 10;`;
let sqlrp = `SELECT * FROM reportposts WHERE eid > $2 AND eid < $1 ORDER BY eid DESC LIMIT 10;`;
return new Promise((resolve, reject) => {
pool.query(sql, [pid, newest], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
pool.query(sqlrp, [pid, newest], (err, rprows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve({posts:rows.rows, reportposts:rprows.rows});
})
})
})
}
/**
* Gets posts older than a specific post id
* @param {*} pid
* @returns array of post and reportpost rows
*/
function getOlderPosts(pid){
let sql = `SELECT * FROM posts WHERE eid < $1 ORDER BY eid DESC LIMIT 10;`;
let sqlrp = `SELECT * FROM reportposts WHERE eid < $1 ORDER BY eid DESC LIMIT 10;`;
return new Promise((resolve, reject) => {
pool.query(sql, [pid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
pool.query(sqlrp, [pid], (err, rprows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve({posts:rows.rows, reportposts:rprows.rows});
})
})
})
}
/**
* Gets posts and reportposts newer than a specific post id
* @param {*} pid
* @returns
*/
function getNewerPosts(pid){
let sql = `SELECT * FROM posts WHERE eid > $1 ORDER BY eid DESC LIMIT 10;`;
let sqlrp = `SELECT * FROM reportposts WHERE eid > $1 ORDER BY eid DESC LIMIT 10;`;
return new Promise((resolve, reject) => {
pool.query(sql, [pid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
pool.query(sqlrp, [pid], (err, rprows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve({posts:rows.rows, reportposts:rprows.rows});
})
})
})
}
/**
* Gets initial posts for the feed
* @returns object with posts and reportposts fields
*/
function getInitPosts(){
let sql = 'SELECT * FROM posts ORDER BY eid DESC LIMIT 10;';
let sqlrp = 'SELECT * FROM reportposts ORDER BY eid DESC LIMIT 10;';
return new Promise((resolve, reject) => {
pool.query(sql, (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
pool.query(sqlrp, (err, rprows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve({posts:rows.rows, reportposts:rprows.rows});
})
})
})
}
/**
* Gets all posts of a user
* @param {number} uid
* @returns object with posts and reportposts fields
*/
function getUserPosts(uid){
let sql = 'SELECT eid,body,created_at,alias FROM posts WHERE uid = $1 ORDER BY eid DESC LIMIT 10;';
let sqlrp = 'SELECT eid,body,created_at,alias,rid FROM reportposts WHERE uid = $1 ORDER BY eid DESC LIMIT 10;';
return new Promise((resolve, reject) => {
pool.query(sql, [uid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
pool.query(sqlrp, [uid], (err, rprows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve({posts:rows.rows, reportposts:rprows.rows});
})
})
})
}
// ----------------- COMMENT FUNCTIONS ----------------- //
/**
* creates a new comment
* @param {*} uid
* @param {*} body
* @param {number} eid
* @returns
*/
function createComment(uid, body, eid){
return new Promise((resolve, reject) => {
let date = new Date().toISOString();
let sql = 'INSERT INTO entitycomment(eid, uid, body, created_at, alias, likes) VALUES($1,$2,$3,$4, $5,0) RETURNING *;';
let postAlias = `SELECT alias FROM posts WHERE eid = $1;`;
let op = `SELECT uid FROM posts WHERE eid=$1;`
// Gets alias of the user at time of post
// pool.query(postAlias, [eid], (err, aliasRow) => {
// if(err){
// console.error("[-]", err);
// reject(err);
// }
// let alias = aliasRow.rows[0].alias;
// //Creates user comment
// pool.query(sql, [eid, uid, body, date, alias], (err, rows) => {
// if(err){
// console.error("[-]", err);
// reject(err);
// }
// if(rows.rows.length == 0){
// reject("Could not create comment");
// }else{
// incrementUserCount(uid, "comment").catch((err) => {
// console.error("[-]", err);
// })
// resolve(rows.rows[0]);
// }
// })
// })
//Gets current alias of user
getAlias(uid).then((alias) => {
pool.query(sql, [eid, uid, body, date, alias], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(rows.rows.length == 0){
reject("Could not create comment");
}else{
incrementUserCount(uid, "comment").catch((err) => {
console.error("[-]", err);
})
pool.query(op, [eid], (err, op_uid)=>{
if(err){
console.error("[-]", err);
}else{
let opUID = op_uid.rows[0].uid;
if(opUID != uid){
}
}
})
resolve(rows.rows[0]);
}
})
})
})
}
/**
* Deletes a comment
* @param {number} cid
* @param {string} uid
* @returns promise with success message on success
*/
function deleteComment(cid, uid){
let sql = 'DELETE FROM entitycomment WHERE id = $1 AND uid = $2;';
return new Promise((resolve, reject) => {
pool.query(sql, [cid, uid], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
decrementUserCount(uid, "comment").catch((err) => {
console.error("[-]", err);
})
resolve("Comment deleted");
})
})
}
/**
* Updates a comment with new body
* @param {number} cid
* @param {string} uid
* @param {string} body
* @returns
*/
function editComment(cid, uid, body){
let sql = 'UPDATE entitycomment SET body = $1 WHERE id = $2 AND uid = $3;';
return new Promise((resolve, reject) => {
pool.query(sql, [body, cid, uid], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve("Comment updated");
})
})
}
/**
* Gets all user comments for an entity
* @param {string} uid
* @param {number} eid
* @returns list of entity ids
*/
function getUserComments(uid, eid){
let sql = `SELECT id FROM entitycomment WHERE uid = $1 AND eid = $2;`;
return new Promise((resolve, reject) => {
pool.query(sql, [uid, eid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
for(let i = 0; i < rows.rows.length; i++){
rows.rows[i] = rows.rows[i].id;
}
resolve(rows.rows);
})
})
}
/**
* Gets 20 comments for an entity
* @param {number} eid
* @returns array of comment rows
*/
function getComments(eid, uid){
// let sql = "SELECT body, created_at,eid,id,likes,alias,uid,owned_by FROM (SELECT body, created_at, eid, id, likes, alias, uid FROM entitycomment WHERE eid = $1 ORDER BY id DESC LIMIT 20), (SELECT owned_by FROM entity WHERE id = $1);";
//Gets comments for post, grabs user who created entity(OP)
//Currently makes two seperate queries to retrieve op uid, may later change to only get one
let sql = 'SELECT body,created_at,eid,id,likes,alias,uid FROM entitycomment WHERE eid = $1 ORDER BY id ASC LIMIT 20;';
let op = `SELECT owned_by FROM entity WHERE id = $1;`;
return new Promise((resolve, reject) => {
pool.query(op, [eid], (err, opRow) => {
if (err){
console.error("[-]", err);
reject(err);
}
pool.query(sql, [eid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
//Determines if any comments are from the OP
for(let i =0; i< rows.rows.length; i++){
if(rows.rows[i].uid == opRow.rows[0].owned_by){
rows.rows[i].isOP = true;
}else{
rows.rows[i].isOP = false;
}
if(rows.rows[i].uid == uid){
rows.rows[i].ownsComment = true;
}
//Removes uid from the rows
delete rows.rows[0].uid;
}
resolve(rows.rows);
})
})
})
}
/**
* Gets all comments a user has made
* @param {*} uid user id
*/
function getAllUserComments(uid){
let sql = 'SELECT body,created_at,eid,id,likes,alias,uid FROM entitycomment WHERE uid=$1 ORDER BY id DESC;';
return new Promise((resolve, reject)=>{
pool.query(sql, [uid], (err, rows)=>{
if(err){
console.error('[-]', err)
reject(err)
}else{
resolve(rows)
}
})
})
}
/**
* Gets 20 comments after a specific comment id
* @param {number} eid entity
* @param {number} cid comment
* @returns array of row objects
*/
function getCommentsAfter(eid, cid, uid){
//Makes two queries to distinguish between OP and other comments, may change later to only make one
let sql = 'SELECT body,created_at,eid,id,likes,alias, uid FROM entitycomment WHERE eid = $1 AND id < $2 ORDER BY id ASC LIMIT 20;';
let op = `SELECT owned_by FROM entity WHERE id = $1;`;
return new Promise((resolve, reject) => {
pool.query(op, [eid], (err, opRow) => {
if (err){
console.error("[-]", err);
reject(err);
}
pool.query(sql, [eid, cid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
//Determines if any comments are from the OP
for(let i =0; i< rows.rows.length; i++){
if(rows.rows[i].uid == opRow.rows[0].owned_by){
rows.rows[i].isOP = true;
}else{
rows.rows[i].isOP = false;
}
if(rows.rows[i].uid == uid){
rows.rows[i].ownsComment = true;
}
//Removes uid from the rows
delete rows.rows[i].uid;
}
resolve(rows.rows);
})
})
})
}
/**
* Creates a reply to a comment
* @param {number} cid
* @param {string} uid
* @param {string} body
* @returns
*/
function createReply(cid, uid, body, eid){
return new Promise((resolve, reject) => {
let date = new Date().toISOString();
let sql = 'INSERT INTO replies(cid, uid, body, created_at, alias, eid) VALUES($1,$2,$3,$4, $5, $6) RETURNING id;';
getAlias(uid).then((alias) => {
pool.query(sql, [cid, uid, body, date, alias, eid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(rows.rows.length == 0){
reject("Could not create reply");
}else{
incrementUserCount(uid, "comment").catch((err) => {
console.error("[-]", err);
})
resolve(rows.rows[0].id);
}
})
})
})
}
/**
* Deletes a reply
* @param {number} rid
* @param {string} uid
* @returns promise with success message on success
*/
function deleteReply(rid, uid){
let sql = 'DELETE FROM replies WHERE id = $1 AND uid = $2;';
return new Promise((resolve, reject) => {
pool.query(sql, [rid, uid], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
decrementUserCount(uid, "comment").catch((err) => {
console.error("[-]", err);
})
resolve("Reply deleted");
})
})
}
/**
* Updates a reply with new body
* @param {number} rid
* @param {string} uid
* @param {string} body
* @returns
*/
function editReply(rid, uid, body){
let sql = 'UPDATE replies SET body = $1 WHERE id = $2 AND uid = $3;';
return new Promise((resolve, reject) => {
pool.query(sql, [body, rid, uid], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve("Reply updated");
})
})
}
/**
* Gets 15 replies for a comment
* @param {*} cid
* @returns array of reply rows
*/
function getReplies(cid){
let sql = 'SELECT body,created_at,eid,id,likes,alias FROM replies WHERE cid = $1 ORDER BY id DESC LIMIT 15;';
return new Promise((resolve, reject) =>{
pool.query(sql, [cid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(rows.rows);
})
})
}
/**
* Gets 15 replies after a specific reply id
* @param {number} cid comment
* @param {number} rid reply
* @returns array of row objects
*/
function getRepliesAfter(cid, rid){
let sql = 'SELECT body,created_at,eid,id,likes,alias FROM replies WHERE cid = $1 AND id > $2 ORDER BY id DESC LIMIT 15;';
return new Promise((resolve, reject) =>{
pool.query(sql, [cid, rid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(rows.rows);
})
})
}
/**
* Gets amount of comments for an entity
* @param {mumber} eid entity id
* @returns promise with count of comments
*/
function getCommentCount(eid){
let sql = 'SELECT COUNT(*) FROM entitycomment WHERE eid = $1;';
let sqlr = 'SELECT COUNT(*) FROM replies WHERE cid = $1;';
return new Promise((resolve, reject) => {
pool.query(sql, [eid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
pool.query(sqlr, [eid], (err, rrows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(rows.rows[0].count + rrows.rows[0].count);
})
})
})
}
/**
* Gets comment count from entity table
* @param {number} eid
* @returns promise with comment count
*/
function getCommentCountFromEntity(eid){
let sql = 'SELECT comments FROM entity WHERE id = $1;';
return new Promise((resolve, reject) => {
pool.query(sql, [eid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(rows.rows[0].comments);
})
})
}
/**
* Creates a like entry for a comment
* @param {string} uid
* @param {number} cid comment id
* @returns promise with id of new like
*/
function likeComment(uid, cid){
let sql = `INSERT INTO commentlike(date, "user", cid, sentiment) VALUES($1,$2,$3,$4) RETURNING id;`;
let test = `SELECT * FROM commentlike WHERE cid = $1 AND "user" = $2;`;
return new Promise((resolve, reject) => {
let date = new Date().toISOString();
//Checks if user has already liked the entity
//Will prob change later
pool.query(test, [cid, uid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(rows.rows.length > 0){
if(rows.rows[0].type == 'like'){
resolve(rows.rows[0].id);
return
}else{
let up = `UPDATE commentlike SET sentiment = $1 WHERE cid = $2 AND "user" = $3 RETURNING id;`;
pool.query(up, ['like', cid, uid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
decrementUserCount(uid, "dislike")
incrementUserCount(uid, "like")
resolve(rows.rows[0].id);
return;
})
}
}else{
pool.query(sql, [date, uid, cid, 'like'], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
incrementUserCount(uid, "like")
resolve(rows.rows[0].id);
})
}
})
})
}
/**
* dislikes a comment
* @param {*} uid
* @param {*} cid
* @returns promise with id of new dislike
*/
function dislikeComment(uid, cid){
let sql = `INSERT INTO commentlike(date, "user", cid, sentiment) VALUES($1,$2,$3,$4) RETURNING id;`;
let test = `SELECT * FROM commentlike WHERE cid = $1 AND "user" = $2;`;
return new Promise((resolve, reject) => {
let date = new Date().toISOString();
//Checks if user has already liked the entity
//Will prob change later
pool.query(test, [cid, uid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(rows.rows.length > 0){
if(rows.rows[0].type == 'dislike'){
resolve(rows.rows[0].id);
return
}else{
let up = `UPDATE commentlike SET sentiment = $1 WHERE cid = $2 AND "user"= $3 RETURNING id;`;
pool.query(up, ['dislike', cid, uid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
decrementUserCount(uid, "like")
incrementUserCount(uid, "dislike")
resolve(rows.rows[0].id);
return;
})
}
}else{
pool.query(sql, [date, uid, cid, 'dislike'], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
incrementUserCount(uid, "dislike")
resolve(rows.rows[0].id);
})
}
})
})
}
/**
* will delete a comment like
* @param {*} uid
* @param {*} cid
* @returns promise with success message
*/
function deleteCommentLike(uid, cid){
let sql = `DELETE FROM commentlike WHERE cid = $1 AND "user" = $2 RETURNING sentiment;`;
return new Promise((resolve, reject) => {
pool.query(sql, [cid, uid], (err, row) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(row.rows.length == 0){
reject("No like found with that id");
return
}
if(row.rows[0].sentiment == 'like'){
decrementUserCount(uid, "like").catch((err) => {
console.error("[-]", err);
})
}else{
decrementUserCount(uid, "dislike").catch((err) => {
console.error("[-]", err);
})
}
resolve("Like deleted");
})
})
}
/**
* checks if an id has liked a comment
* @param {*} uid
* @param {*} cid
* @returns sentiment of user towards a comment
*/
function hasLikedComment(uid, cid){
let sql = `SELECT sentiment FROM commentlike WHERE cid = $1 AND "user" = $2;`;
return new Promise((resolve, reject) => {
pool.query(sql, [cid, uid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(rows.rows.length == 0){
resolve({sentiment: 'none'});
}else{
resolve(rows.rows[0]);
}
})
})
}
// ----------------- LIKE FUNCTIONS ----------------- //
/**
* Creates a new like for an entity
* @param {string} uid
* @param {number} eid entity id
* @returns promise with id of new like
*/
function createLike(uid, eid){
let sql = 'INSERT INTO entitylike(eid, uid, created_at, sentiment) VALUES($1,$2,$3, $4) RETURNING id;';
let test = 'SELECT * FROM entitylike WHERE eid = $1 AND uid = $2;';
return new Promise((resolve, reject) => {
let date = new Date().toISOString();
//Checks if user has already liked the entity
//Will prob change later
pool.query(test, [eid, uid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(rows.rows.length > 0){
if(rows.rows[0].sentiment == 'like'){
resolve(rows.rows[0].id);
return
}else{
let up = 'UPDATE entitylike SET sentiment = $1 WHERE eid = $2 AND uid = $3 RETURNING id;';
pool.query(up, ['like', eid, uid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
decrementUserCount(uid, "dislike")
incrementUserCount(uid, "like")
resolve(rows.rows[0].id);
return;
})
}
}else{
pool.query(sql, [eid, uid, date, 'like'], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
incrementUserCount(uid, "like")
resolve(rows.rows[0].id);
})
}
})
})
}
/**
* Creates a new dislike for an entity
* @param {*} uid
* @param {*} eid entity id
* @returns promise with id of new dislike
*/
function createDislike(uid, eid){
let sql = 'INSERT INTO entitylike(eid, uid, created_at, sentiment) VALUES($1,$2,$3, $4) RETURNING id;';
let test = 'SELECT * FROM entitylike WHERE eid = $1 AND uid = $2;';
return new Promise((resolve, reject) => {
let date = new Date().toISOString();
//Checks if user has already disliked the entity
//May change to use a single query
pool.query(test, [eid, uid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(rows.rows.length > 0){
if(rows.rows[0].sentiment == 'dislike'){
resolve(rows.rows[0].id);
return
}else{
let up = 'UPDATE entitylike SET sentiment = $1 WHERE eid = $2 AND uid = $3 RETURNING id;';
pool.query(up, ['dislike', eid, uid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
decrementUserCount(uid, "like")
incrementUserCount(uid, "dislike")
resolve(rows.rows[0].id);
return;
})
}
}else{
pool.query(sql, [eid, uid, date, 'dislike'], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
incrementUserCount(uid, "dislike")
resolve(rows.rows[0].id);
})
}
})
})
}
/**
* Deletes entity like
* @param {number} eid entity id
* @param {string} uid
* @returns promise with success message on success
*/
function deleteLike(uid, eid){uid
let sql = 'DELETE FROM entitylike WHERE eid = $1 AND uid = $2 RETURNING sentiment;';
return new Promise((resolve, reject) => {
pool.query(sql, [eid, uid], (err, row) => {
if(err){
console.error("[-]", err);uid
reject(err);
}
if(row.rows.length == 0){
reject("No like found with that id");
return
}
if(row.rows[0].sentiment == 'like'){
decrementUserCount(uid, "like").catch((err) => {
console.error("[-]", err);
})
}else{
decrementUserCount(uid, "dislike").catch((err) => {
console.error("[-]", err);
})
}
resolve("Like deleted");
})
})
}
/**
* Gets number of likes for an entity
* @param {number} eid
* @returns number of likes
*/
function getLikeCount(eid){
let sql = 'SELECT COUNT(*) FROM entitylike WHERE eid = $1;';
return new Promise((resolve, reject) => {
pool.query(sql, [eid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(rows.rows[0].count);
})
})
}
/**
* Gets like count from entity table
* @param {number} eid
* @returns promise with like count
*/
function getLikeCountFromEntity(eid){
let sql = `SELECT likes FROM entity WHERE id = $1;`;
return new Promise((resolve, reject) => {
pool.query(sql, [eid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(rows.rows[0].likes);
})
})
}
/**
* Checks to see if a user has liked an entity
* @param {string} uid
* @param {number} eid
* @returns boolean
*/
function hasLiked(uid, eid){
let sql = 'SELECT sentiment FROM entitylike WHERE eid = $1 AND uid = $2;';
return new Promise((resolve, reject) => {
pool.query(sql, [eid, uid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(rows.rows.length == 0){
resolve({sentiment:'none'})
}
resolve(rows.rows[0]);
})
})
}
//----------------------------------------------------------------- FEEDBACK FUNCTIONS -----------------------------------------------------------------//
/**
* Adds feedback/contact entries to the database.
* @param {*} uid user id
* @param {*} type bug, suggestion, location, contact
* @param {*} body message/location/etc
*/
function addFeedback(uid, type, body){
let sql = 'INSERT INTO feedback(uid, type, message, date) VALUES($1,$2,$3,$4) RETURNING id;';
return new Promise((resolve, reject) => {
let date = new Date().toISOString();
pool.query(sql, [uid, type, body, date], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
if(rows.rows.length == 0){
reject("Could not create feedback entry");
}else{
resolve(rows.rows[0].id);
}
})
})
}
/**
* Gets all feedback entries for a user
* @param {*} uid user id
* @returns array of feedback rows
*/
function getFeedback(uid){
let sql = 'SELECT * FROM feedback WHERE uid = $1 ORDER BY date DESC;';
return new Promise((resolve, reject) => {
pool.query(sql, [uid], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}else{
resolve(rows.rows);
}
})
})
}
/**
* Gets feedback entries by type for a user
* @param {*} uid user id
* @param {*} type bug, suggestion, location, contact
* @returns array of feedback rows
*/
function getFeedbackByType(uid, type){
let sql = 'SELECT * FROM feedback WHERE uid = $1 AND type = $2 ORDER BY date DESC;';
return new Promise((resolve, reject) => {
pool.query(sql, [uid, type], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}else{
resolve(rows.rows);
}
})
})
}
/**
* Deletes a feedback entry
* @param {*} fid id of feedback entry
* @param {*} uid user id
* @returns success message on success
*/
function deleteFeedback(fid, uid){
let sql = 'DELETE FROM feedback WHERE id = $1 AND uid = $2;';
return new Promise((resolve, reject) => {
pool.query(sql, [fid, uid], (err) => {
if(err){
console.error("[-]", err);
reject(err);
}else{
resolve("Feedback entry deleted");
}
})
})
}
//----------------------------------------------------------------- Notifications Functions -----------------------------------------------------------------//
/**
* Creates subscription in subscription table
* @param {string[]} uid user id
* @param {string} topics topic user is subscribed
* @returns id of subscription or error
*/
function subscribe(uid, topics){
let sql = `INSERT INTO subscriptions(uid, topic, date_added) VALUES($1, $2, $3) RETURNING id;`
return new Promise((resolve, reject)=>{
let date = new Date().toISOString();
for(const topic of topics){
pool.query(sql, [uid, topic, date], (err, rows)=>{
if(err){
console.error("[-]", err)
reject(err)
}else{
resolve(rows.rows[0].id)
}
})
}
})
}
/**
* Sets default subscriptions for user
* @param {string} uid user id
* @returns success message on success
*/
function subscribeDefault(uid){
let sql = `INSERT INTO subscriptions(uid, topic, date_added) VALUES($1, $2, $3) RETURNING id;`
const topics = [
"crime_medium",
"crime_high",
"social_comment",
"social_upvote_post",
"social_upvote_comment",
"social_remove_post",
"social_remove_comment"
]
return new Promise((resolve, reject)=>{
let date = new Date().toISOString();
for(const topic of topics){
pool.query(sql, [uid, topic, date], (err, rows)=>{
if(err){
console.error("[-]", err)
reject(err)
}else{
resolve(rows.rows[0].id)
}
})
}
})
}
/**
*
* @param {string} uid
* @param {string[]} topics
* @returns Success message on success or error
*/
function deleteSubscription(uid, topics){
let sql = `DELETE FROM subscriptions WHERE uid=$1 AND topic=$2;`
return new Promise((resolve, reject)=>{
for(const topic of topics){
pool.query(sql, [uid, topic], (err)=>{
if(err){
console.error("[-]",err)
reject(err)
}else{
resolve("Subscription(s) deleted.")
}
})
}
})
}
/**
* Deletes all subscriptions of a user
* @param {string} uid user id
* @returns success message.
*/
function clearSubscriptions(uid){
let sql = `DELETE FROM subscriptions WHERE uid=$1;`
return new Promise((resolve, reject)=>{
for(const topic of topics){
pool.query(sql, [uid, topic], (err)=>{
if(err){
console.error("[-]",err)
reject(err)
}else{
resolve("Subscriptions deleted.")
}
})
}
})
}
/**
* Gets notification subscriptions for a user
* @param {string} uid user id
* @returns all topics a user is subscribed to
*/
function getSubscriptions(uid){
let sql=`SELECT topic FROM subscriptions WHERE uid=$1;`
return new Promise((resolve, reject)=>{
pool.query(sql, [uid], (err, rows)=>{
if(err){
console.error("[-]",err)
reject(err)
}else{
resolve(rows.rows)
}
})
})
}
/**
* Adds device to user for subscriptions
* @param {string} uid USER ID
* @param {string} token Expo push token
* @param {string} app_version version of sherlock
* @param {string} os Android or IOS
* @param {string} name name of device
* @returns success message on success
*/
function addDevice(uid, token, app_version, os, name){
let sql = `INSERT INTO user_devices(uid, "token", os, name, app_version, date_added) VALUES($1,$2,$3,$4,$5,$6);`
return new Promise((resolve, reject)=>{
let date = new Date().toISOString();
let formattedVersion = app_version.split(".");
pool.query(sql, [uid, token, os, name, formattedVersion, date], (err)=>{
if(err){
if(err.code=='23505'){
console.log("Device/token already exists.")
reject("[#]Device already exists.")
}else{
console.error("[-]",err)
reject(err)
}
}else{
resolve("Device added successfully.")
}
})
})
}
/**
* Adds device that has yet to be logged in
* @param {string} token Expo push token
* @param {string} app_version version of sherlock
* @param {string} os Android or IOS
* @param {string} name name of device
* @returns Success message on success
*/
function addAnonDevice(token, app_version, os, name){
let sql = `INSERT INTO user_devices("token", os, name, app_version, date_added) VALUES($1,$2,$3,$4,$5);`
return new Promise((resolve, reject)=>{
let date = new Date().toISOString();
let formattedVersion = app_version.split(".");
pool.query(sql, [token, os, name, formattedVersion, date], (err)=>{
if(err){
if(err.code=='23505'){
console.log("Device/token already exists.")
reject("[#]Device already exists.")
}else{
console.error("[-]",err)
reject(err)
}
}else{
resolve("Device added successfully.")
}
})
})
}
/**
* Deletes a user's device
* @param {string} uid user id
* @param {string} token expo push token
* @returns Success message on success
*/
function deleteDevice(uid, token){
let sql =`DELETE FROM user_devices WHERE uid=$1 AND "token"=$2;`
//Technically doesn't need uid, but it is here to ensure security
return new Promise((resolve, reject)=>{
pool.query(sql, [uid, token],(err)=>{
if(err){
console.error("[-]",err)
reject(err)
}else{
resolve("Device deleted successfully.")
}
})
})
}
/**
* Deletes a device
* @param {string} token expo push token
* @returns Success message on success
*/
function deleteDeviceAdmin(token){
let sql =`DELETE FROM user_devices WHERE "token"=$1;`
return new Promise((resolve, reject)=>{
pool.query(sql, [token],(err)=>{
if(err){
console.error("[-]",err)
reject(err)
}else{
resolve("Device("+token+") deleted successfully.")
}
})
})
}
/**
* Logs in a device to a specific user.
* @param {string} uid user id
* @param {string} token Expo push token
* @returns Success message on success
*/
function loginDevice(uid, token){
let sql = `UPDATE user_devices SET uid=$1 WHERE "token"=$2 RETURNING "token";`
return new Promise((resolve, reject)=>{
pool.query(sql, [uid, token], (err,row)=>{
if(err){
console.error("[-]", err)
reject(err)
}else{
if(row.rowCount==0){
reject("Device does not exist")
}
resolve("Device logged in.")
}
})
})
}
/**
* Logs out a specific device
* @param {string} uid user id
* @param {string} token Expo push token
* @returns Success message on success
*/
function logoutDevice(uid, token){
let sql = `UPDATE user_devices SET uid=NULL WHERE "token"=$1 AND uid=$2;`
return new Promise((resolve, reject)=>{
pool.query(sql, [token, uid], (err)=>{
if(err){
console.error("[-]",err)
reject(err)
}else{
resolve("Device logged out.")
}
})
})
}
/**
* Updates all user devices last updated data.
* @param {string} uid user id
* @returns Success message on success
* @note This is to be used in cases where all devices of a user have been updated.
*/
function updateLastNotified(uid){
let sql =`UPDATE user_devices SET last_notified=$1 WHERE uid=$2;`
return new Promise((resolve, reject)=>{
let date = new Date().toISOString();
pool.query(sql, [date, uid], (err)=>{
if(err){
console.error("[-]",err)
reject(err)
}else{
resolve("Successfully updated user's last notidied.")
}
})
})
}
/**
* Updates last_notified column of devices in a topic
* @param {string[]} topic topic a user is subscribed to
* @returns success message on success
*/
function updateTopicLastNotified(topics){
let sql = `UPDATE user_devices
SET last_notified = $1
WHERE uid IN (
SELECT uid
FROM subscriptions
WHERE topic = $2
)`
//Turns into array if not already
if(!Array.isArray(topics)){
topics = [topics];
}
return new Promise((resolve, reject)=>{
let date = new Date().toISOString();
//Runs through each topic and updates each device in that topic.
for(let topic of topics){
pool.query(sql, [date, topic], (error) => {
if (error) {
console.error("[-]", error);
reject(error);
} else {
resolve("Updated last_notified for", topic);
}
});
}
})
}
/**
* Updates app version of a device
* @param {string} token expo push token
* @param {string} version version of app
* @param {string} uid user id
* @note Version must be in Major.Minor.Patch format ex: 1.3.456
* @returns Success message on success
*/
function updateAppVersion(token, version, uid){
let sql = `UPDATE user_devices SET app_version=$1 WHERE token=$2 AND uid=$3;`
return new Promise((resolve, reject)=>{
//Version is stored as array in db, need to split
let formattedVersion = version.split(".")
pool.query(sql, [formattedVersion, token, uid], (err)=>{
if(err){
console.error("[-]",err)
reject(err)
}else{
resolve("Successfully updated device's app version.")
}
})
})
}
/**
* Function to update a devices name
* @param {string} token expo push token
* @param {string} name new name of device
* @param {string} uid user id
* @returns Success message on success
*/
function updateDeviceName(token, name, uid){
let sql = `UPDATE user_devices SET name=$1 WHERE "token"=$2 AND uid=$3;`
return new Promise((resolve, reject)=>{
pool.query(sql, [name, token, uid], (err)=>{
if(err){
console.error("[-]",err)
reject(err)
}else{
resolve("Device name updated successfully.")
}
})
})
}
/**
* Gets all distinct expo tokens for a list of topics
* @param {string[]} topics array of topics
* @returns array of expo push tokens
*/
function getDevicesFromTopics(topics){
//makes string for paramterized query, i.e: $1,$2,$3
let placeholders = topics.map((_,index) =>`$${index+1}`).join(',')
let tokens = `select distinct ud.token, s.topic from user_devices ud join subscriptions s on ud.uid = s.uid where s.topic IN (${placeholders});`;
return new Promise((resolve, reject)=>{
pool.query(tokens, topics, (err,rows)=>{
if(err){
console.error("[-]",err)
reject(err)
}else{
let token_array = rows.rows.map(row=>row.token)
resolve(token_array)
}
})
})
}
/**
* Grabs all users and the needed information for personalized notifications
* @param {string[]} topics list of topics
* @returns object {uid:string, tokens:[],topics:[]}
*/
function getUsersByTopics(topics){
let placeholders = topics.map((_, index) => `$${index + 1}`).join(",");
let tokens = `select ud.uid,ud.token, s.topic from user_devices ud join subscriptions s on ud.uid = s.uid where s.topic IN (${placeholders});`;
return new Promise((resolve, reject)=>{
pool.query(tokens, topics, (err, result)=>{
if(err){
console.error("[-]",err)
reject(err)
}else{
const rows = result.rows;
const userMap = {};
for(const {uid, token, topic} of rows){;
if(!userMap[uid]){
userMap[uid] = {
tokens: new Set(),
topics: new Set()
}
}
userMap[uid].tokens.add(token)
userMap[uid].topics.add(topic)
}
const users = Object.entries(userMap).map(([uid, {tokens, topics}]) =>({
uid,
tokens: Array.from(tokens),
topics: Array.from(topics)
}))
resolve(users)
}
})
})
}
/**
* Gets all push tokens for a certain topic
* @param {string} topic
* @returns array of expo push tokens
*/
function getTopicDevices(topic){
let sql = `select distinct "token" from user_devices ud join subscriptions s on ud.uid = s.uid where s.topic=$1`;
return new Promise((resolve, reject)=>{
pool.query(sql, [topic], (err, rows)=>{
if(err){
console.error("[-]", err)
reject(err)
}else{
let token_array = rows.rows.map(row=>row.token)
resolve(token_array)
}
})
})
}
/**
* Gets all devices connected to a user's account
* @param {string} uid
* @returns
*/
function getUserDevices(uid){
let sql = `SELECT * FROM user_devices WHERE uid=$1;`
return new Promise((resolve, reject)=>{
pool.query(sql, [uid], (err, rows)=>{
if(err){
console.error("[-]",err)
reject(err)
}else{
resolve(rows.rows)
}
})
})
}
//----------------------------------------------------------------- UTIL/Helper Functions -----------------------------------------------------------------//
/**
* Returns the time of the most recent entry in a table
* @param {string} table
* @note DB time formats need refactoring for more uniformity before this will be completely reliable. However it will work for current use
*/
function getLastEntryTime(table){
let tableExt;
switch(table){
case "kpd_cip":
tableExt = "date";
break;
case "knox_zones":
tableExt = "date";
break;
case "utpd_clery":
tableExt = "date_added";
break;
default:
return Promise.reject("Invalid table");
}
let sql = `SELECT ${tableExt} FROM ${table} ORDER BY ${tableExt} desc LIMIT 1;`;
return new Promise((resolve, reject) => {
pool.query(sql, (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(parseInt(rows.rows[0][tableExt]));
})
})
}
//----------------------------------------------------------------- AI FUNC -----------------------------------------------------------------//
/**
* Gets all cip data for training
* @returns all cip rows
*/
function aiData(){
let sql = 'SELECT * FROM kpd_cip;';
return new Promise((resolve, reject) => {
pool.query(sql, (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve(rows.rows);
})
})
}
//----------------------------------------------------------------- WEB MAP -----------------------------------------------------------------//
/**
* Gets the latest CIP data for web map
* @param {number} day start day
* @returns Returns 2 weeks of data after start date
*/
function getMap(day){
let cip ="SELECT * FROM kpd_cip WHERE date > $1 AND date < extract(epoch from (to_timestamp($1::bigint)+interval '14 days'))";
let zones = "SELECT * FROM knox_zones WHERE date > $1 AND date < extract(epoch from (to_timestamp($1::bigint)+interval '14 days'))";
day = new Date(day).getTime();
return new Promise((resolve, reject) => {
pool.query(cip, [day], (err, rows) => {
if(err){
console.error("[-]", err);
reject(err);
}
pool.query(zones, [day], (err, zrows) => {
if(err){
console.error("[-]", err);
reject(err);
}
resolve({cip:rows.rows, zones:zrows.rows});
})
})
})
}
//----------------------------------------------------------------- TESTING -----------------------------------------------------------------//
/**
* Test function for testing db queries
*/
function test(){
// db.all("SELECT * FROM knox_zones", (err, rows) => {
// if(err){
// console.error(err);
// }else{
// for(let i =0;i<rows.length;i++){
// let d = parseInt(new Date(rows[i].date).getTime().toString().substring(0,10))
// console.log(d,rows[i].id)
// db.get(`UPDATE knox_zones SET 'date'=? WHERE id=? RETURNING *`, [d,rows[i].id], (err, r)=>{
// if(err){
// console.error("[-]", err);
// }else{
// console.log("UPDATED: ",r.id)
// }
// })
// }
// }
// })
}
module.exports = {
pool,
getKPDCIP,
getCIPByID,
getZonesByID,
getKPDCIPByLocation,
getDefaultCounty,
getLatestCIP,
getCustomFilter,
incrementCount,
incrementClassCount,
resetMonthlyCount,
getStatsByLocation,
getIntegrationStats,
getAlias,
createUser,
deleteUser,
getUserCounts,
incrementUserCount,
renewAlias,
getLastEntryTime,
getUserRole,
getUser,
updateUserVerificationScore,
createEntity,
deleteEntity,
createPost,
editPost,
createReportPost,
editReportPost,
createComment,
deleteComment,
editComment,
createReply,
deleteReply,
editReply,
createLike,
deleteLike,
getLikeCount,
getURVerificationCount,
createURValidation,
deleteURValidation,
createURContest,
deleteURContest,
createUserReport,
getInitPosts,
getPosts,
getReportPosts,
getPost,
getReportPost,
updateUserVerificationScoreFromEID,
getUserPosts,
getUserReport,
getURByUID,
getComments,
getCommentsAfter,
getReplies,
getRepliesAfter,
getFeed,
getCommentCount,
getCommentCountFromEntity,
getLikeCountFromEntity,
getLikeCount,
updateEntityCount,
getEntityCounts,
hasLiked,
getUserComments,
getNewerPosts,
getOlderPosts,
createDislike,
getMap,
getLocalTemperalData,
getMonthlyStats,
aiData,
syncClassCounts,
likeComment,
dislikeComment,
deleteCommentLike,
hasLikedComment,
reportComment,
reportEntity,
getReports,
addFeedback,
getFeedback,
getFeedbackByType,
deleteFeedback,
getAllUserComments,
subscribe,
deleteSubscription,
getSubscriptions,
updateAppVersion,
updateDeviceName,
updateLastNotified,
addDevice,
deleteDevice,
getTopicDevices,
getDevicesFromTopics,
addAnonDevice,
loginDevice,
logoutDevice,
getUserDevices,
updateTopicLastNotified,
getUsersByTopics,
deleteDeviceAdmin,
subscribeDefault,
clearSubscriptions,
test
};