Collaborate App Work

CollaborateAppSchema.sql

DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  facebook_id BIGINT,
  linkedin_id BIGINT,
  profile_created BOOLEAN DEFAULT false,
  created_at DATE DEFAULT CURRENT_TIMESTAMP,
  access_token VARCHAR(255)
);

DROP TABLE IF EXISTS profile;

CREATE TABLE profiles (
  user_id INT PRIMARY KEY REFERENCES users (id),
  email VARCHAR(255),
  full_name VARCHAR(255),
  nickname VARCHAR(255),
  bio TEXT,
  profile_image VARCHAR(255),
  industries INT[],
  updated_at DATE
);

DROP TABLE IF EXISTS industries;

CREATE TABLE industries (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  thumbnail VARCHAR(255)
);

DROP TABLE IF EXISTS matches;

CREATE TABLE matches (
  id SERIAL PRIMARY KEY,
  matcher INT REFERENCES users (id),
  matchee INT REFERENCES users (id)
);

DROP TABLE IF EXISTS reviews;

CREATE TABLE reviews (
  id SERIAL PRIMARY KEY,
  reviewer_id INT REFERENCES users (id),
  match_id INT REFERENCES matches (id),
  rating INT,
  review TEXT
);

DROP TABLE IF EXISTS notifications;

CREATE TABLE notifications (
  id SERIAL PRIMARY KEY,
  match_id INT REFERENCES matches (id),
  message TEXT,
  read BOOLEAN DEFAULT false
);

collaborateDatabase.js

const pgp = require('pg-promise')()
const connectionString = process.env.DATABASE_URL  || `postgres://localhost:5432/collaborate`
const db = pgp(connectionString)

const createUser = (attributes) => {
  const sql = `
    INSERT INTO
      users (facebook_id, access_token)
    VALUES
      ($1, $2)
    RETURNING
      *
  `
  return db.oneOrNone(sql, [attributes.facebook_id, attributes.access_token])
}

const updateUser = (attributes) => {
  const sql = `
    UPDATE
      users
    SET access_token=$1
    WHERE users.id=$2
    RETURNING
      *
  `
  return db.one(sql, [attributes.access_token, attributes.id])
}

const deleteUser = (userId) => {
  const sql = `
  DELETE FROM
    users
    *
  WHERE
    id=$1
  `
  return db.none(sql, userId)
}

const createProfile = (attributes) => {
  const sql = `
    INSERT INTO
      profiles (user_id, email, full_name, nickname, bio, profile_image, industries)
    VALUES
      ($1, $2, $3, $4, $5, $6, $7)
    RETURNING
      *
  `
  const variables = [
    attributes.user_id,
    attributes.email,
    attributes.full_name,
    attributes.nickname,
    attributes.bio,
    attributes.profile_image,
    attributes.industries
  ]
  return db.one(sql, variables)
}

const getProfile = (userId) => {
  const sql = `
    SELECT * FROM
      profiles
    WHERE
      user_id = $1
  `
  return db.oneOrNone(sql, userId)
}

const getProfilesByIndustry = (industries) => {
  const sql = `
    SELECT * FROM
      profiles
    WHERE
      industries <@ $1
  `

  return db.any(sql, [industries])
}

const createMatch = (attributes) => {
  const sql = `
    INSERT INTO
      matches (id, matcher, matchee)
    VALUES
      ($1, $2, $3)
    RETURNING
      *
  `
  const variables = [
    attributes.id,
    attributes.matcher,
    attributes.matchee
  ]
  return db.oneOrNone(sql, variables)
}

const createReview = (attributes) => {
  const sql = `
    INSERT INTO
      reviews (id, reviewer_id, match_id, rating, review)
    VALUES
      ($1, $2 , $3, $4, $5)
    RETURNING
      *
  `
  const variables = [
    attributes.id,
    attributes.reviewer_id,
    attributes.match_id,
    attributes.rating,
    attributes.review
  ]
  return db.oneOrNone(sql, variables)
}

const createNotifications = (attributes) => {
  const sql = `
    INSERT INTO
      notifications (id, match_id, message)
    VALUES
      ($1, $2, $3)
    RETURNING
      *
  `
  const variables = [
    attributes.id,
    attributes.match_id,
    attributes.message
  ]

  return db.oneOrNone(sql, variables)
}

const findUserByFacebookId = (facebookId) => {
  const sql = `
    SELECT * FROM
      users
    WHERE (facebook_id=$1)
  `
  return db.oneOrNone(sql, facebookId)
}

const findUserById = (id) => {
  const sql = `
    SELECT * FROM
      users
    WHERE (id=$1)
  `
  return db.oneOrNone(sql, id)
}

module.exports = {
  createUser,
  updateUser,
  deleteUser,
  createProfile,
  getProfile,
  createMatch,
  createReview,
  createNotifications,
  findUserByFacebookId,
  findUserById,
  getProfilesByIndustry
}

CollaborateFacebook.js

const express = require('express')
const httpRequest = require('request')
const graphApiUrl = 'https://graph.facebook.com/v2.9'

const router = express.Router()

router.get('/picture/get', (request, response) => {
  const url = `${graphApiUrl}/${request.user.facebook_id}/picture` +
    `?type=large&redirect=false&access_token=${request.user.access_token}`

  httpRequest(url, (error, apiResponse, body) => {
    body = JSON.parse(body)
    response.json(body.data.url)
  })
})

router.get('/picture/', (request, response) => {
  const url = `${graphApiUrl}/${request.user.facebook_id}/picture` +
    `?type=large&redirect=false&access_token=${request.user.access_token}`

  httpRequest(url, (error, apiResponse, body) => {
    response.header("Content-Type", "text/html")
    response.send(`<img src=${JSON.parse(body).data.url}>`)
  })
})

module.exports = router