const _ = require('lodash')
const { Client } = require('pg')

// CONFIGURATION
const client = new Client({
  host: process.env.PG_HOST,
  port: process.env.PG_PORT,
  user: 'admin',
  password: process.env.PG_PASSWORD,
  database: 'app-development'
})

client.connect()

const query = (q,cb) => {
  client.query(q).then(res => {
    cb(res)
  }).catch( (err) => {
    cb(null,err)
  })
}

// NORMALIZERS
const unflatten = function(a, b, c) {
  (c = "undefined" == typeof c ? [] : c),
    (b = "undefined" == typeof b ? { id: 0 } : b);
  var d = _.filter(a, function(e) {
    return e.parent_id == b.id;
  });
  return (
    _.isEmpty(d) ||
      (0 == b.id ? (c = d) : (b.children = d),
      _.each(d, function(e) {
        unflatten(a, e);
      })),
    c
  );
}

const queries = {

  login: (email,passord,cb) => {
    query(`SELECT * FROM users  WHERE email = "${email}"`, (res,err) => {

    })
  },

  item: (slug, cb) => {

    const q = `
      WITH RECURSIVE tree AS (

        SELECT
          "items".*
          0::bigint as parent_id
        FROM "items"
        INNER JOIN "friendly_id_slugs"
        ON "friendly_id_slugs"."sluggable_id" = "items"."id" AND "friendly_id_slugs"."sluggable_type" = "Item"
        WHERE
          "friendly_id_slugs"."sluggable_type" = 'Item'
          AND
          "friendly_id_slugs"."slug" = "${slug}")
        ORDER BY "items"."id" ASC LIMIT 1

        UNION ALL

        SELECT
          it.*,
          t.id as parent_id
        FROM items as it, tree as t
        INNER JOIN item_children as itc
        ON t.id = itc.item_id
        WHERE itc.child_id = it.id

      )
      SELECT * FROM tree
    `
    query( q, (res,err) => {
      cb(unflatten(res.rows))
    })
  }
}

module.exports = queries