const promise = require("bluebird");
const options = {
  // Initialization Options
  promiseLib: promise, // use bluebird as promise library
  capSQL: true, // when building SQL queries dynamically, capitalize SQL keywords
};
const pgp = require("pg-promise")(options);
const connectionString = process.env.DATABASE_URL;
const parse = require("pg-connection-string").parse;
const oldS3Url = 'uploads.participedia.xyz';
const newS3Url = process.env.AWS_S3_BUCKET;

let config;
try {
  config = parse(connectionString);
  if (process.env.NODE_ENV === "test" || config.host === "localhost") {
    config.ssl = false;
  } else {
    config.ssl = true;
  }
} catch (e) {
  console.error("# Error parsing DATABASE_URL environment variable");
}

let db = pgp(config);
getPhotos('cases');
getPhotos('methods');
getPhotos('organizations');

function getPhotos(table) {
  db.any(`SELECT id, photos FROM ${table}`)
  .then(function(data) {
    data.forEach(data => {
      var photo = data.photos;
      if (photo.search(oldS3Url) >= 0) {
        var newPhoto = photo.replace(oldS3Url, newS3Url);
        updatePhoto(data.id, newPhoto, table);
      } else {
        console.log(`Row ${data.id} doesn't have the URL we are looking for.`);
      }
    });
  })
  .catch(function(error) {
    console.log(error);
  });
}

function updatePhoto(id, value, table) {
  const dataSingle = {photos: value};
  const condition = pgp.as.format(` WHERE id = ${id}`, dataSingle);
  const update = pgp.helpers.update(dataSingle, null, table) + condition;
  
  db.none(update)
  .then(function(data) {
    console.log(`Table: ${table}; ID: ${id} updated`);
  })
  .catch(function(error) {
    console.log(error);
  });
}
@@ -880,16 +880,38 @@
 , photos
+, files, videos, audio
  FROM $%7B
@@ -981,44 +981,200 @@
     
-var photo = data.photos;%0A      if (p
+// Handle photos%0A      var newPhoto = data.photos;%0A      var newFiles = data.files;%0A      var newVideos = data.videos;%0A      var newAudio = data.audio;%0A      var metaData = %7B%7D;%0A%0A      if (newP
 hoto
@@ -1203,28 +1203,24 @@
 ) %7B%0A        
-var 
 newPhoto = p
@@ -1218,17 +1218,20 @@
 Photo = 
-p
+newP
 hoto.rep
@@ -1268,45 +1268,877 @@
     
-updatePhoto(data.id, newPhoto, table)
+metaData%5B'photos'%5D = newPhoto;%0A      %7D else %7B%0A        // console.log(%60Table: $%7Btable%7D, Column 'photos', ID $%7Bdata.id%7D doesn't have the URL we are looking for.%60);%0A      %7D%0A%0A      if (newFiles.search(oldS3Url) %3E= 0) %7B%0A        newFiles = newFiles.replace(oldS3Url, newS3Url);%0A        metaData%5B'files'%5D = newFiles;%0A      %7D else %7B%0A        // console.log(%60Table: $%7Btable%7D, Column 'files', ID $%7Bdata.id%7D doesn't have the URL we are looking for.%60);%0A      %7D%0A%0A      if (newVideos.search(oldS3Url) %3E= 0) %7B%0A        newVideos = newVideos.replace(oldS3Url, newS3Url);%0A        metaData%5B'videos'%5D = newAudio;%0A      %7D else %7B%0A        // console.log(%60Table: $%7Btable%7D, Column 'videos', ID $%7Bdata.id%7D doesn't have the URL we are looking for.%60);%0A      %7D%0A%0A      if (newAudio.search(oldS3Url) %3E= 0) %7B%0A        newAudio = newAudio.replace(oldS3Url, newS3Url);%0A        metaData%5B'audio'%5D = newAudio
 ;%0A  
@@ -2157,16 +2157,19 @@
 %0A       
+ //
  console
@@ -2178,11 +2178,43 @@
 og(%60
-Row
+Table: $%7Btable%7D, Column 'audio', ID
  $%7Bd
@@ -2273,16 +2273,229 @@
       %7D%0A
+%0A%0A      if (metaData.hasOwnProperty('photos') %7C%7C metaData.hasOwnProperty('files') %7C%7C metaData.hasOwnProperty('videos') %7C%7C metaData.hasOwnProperty('audio')) %7B%0A        updatePhoto(data.id, metaData, table);%0A      %7D%0A
     %7D);%0A
@@ -2624,24 +2624,14 @@
 le =
- %7Bphotos:
  value
-%7D
 ;%0A  

Refactor script to update multiple column per row at once