const { Client } = require('pg')
const fs = require('fs')
const csv = require('csv-streamify')

const parseCSV = require('./parseCSV')

// Create a client
const client = new Client()

// Stuff to do to close well
process.stdin.resume();//so the program will not close instantly

function exitHandler(options, err) {
  if (options.cleanup) client.end().then(() => {
    console.log("Connection Closed 👌")
    if (options.exit) process.exit();
  })
  else if (options.exit) process.exit();
  if (err) console.log(err.stack);
}

//do something when app is closing
process.on('exit', exitHandler.bind(null,{cleanup:true}));

//catches ctrl+c event
process.on('SIGINT', exitHandler.bind(null, {exit:true}));

// catches "kill pid" (for example: nodemon restart)
process.on('SIGUSR1', exitHandler.bind(null, {exit:true}));
process.on('SIGUSR2', exitHandler.bind(null, {exit:true}));

//catches uncaught exceptions
process.on('uncaughtException', exitHandler.bind(null, {exit:true}));




// print helper for the console
global.print = (res) => {
  console.log('Response from database :\x1b[35m')
  console.log(res.rows)
  console.log("\x1b[0m")
}

// Connect the client
client.connect().then(() => {
  client.query('SELECT COUNT(*) FROM clips', (err, res) => {
    err ? createDatabase() : startRepl()
  })
})

// If the database does not have the good tables, create & populate them
const createDatabase = () => {
  console.log('Database did not exist')
  console.log(' Creating Table clips')
  client.query(`
    CREATE TABLE clips (
      clipID    serial,
      clipTitle text,
      clipYear  smallint,
      clipType  char(100),
      PRIMARY KEY (clipID)
    );`
  , (err, res) => {
    if (err) { console.log(err); return }
    console.log(' Creating Table links')
    client.query(`
      CREATE TABLE links (
        clipIDFrom  serial,
        clipIDTo    serial,
        linkType    char(100),
        PRIMARY KEY (clipIDFrom, clipIDTo),
        FOREIGN KEY (clipIDFrom) REFERENCES clips(clipId) ON DELETE CASCADE,
        FOREIGN KEY (clipIDTo) REFERENCES clips(clipId) ON DELETE CASCADE
      );`
    , (err, res) => {
      if (err) { console.log(err); return }
      console.log(' Creating Table ratings')
      client.query(`
        CREATE TABLE ratings (
          clipID      serial,
          numberOfVotes  int,
          rank        bigint,
          PRIMARY KEY (clipID),
          FOREIGN KEY (clipID) REFERENCES clips ON DELETE CASCADE
        );`
      , (err, res) => {
        if (err) { console.log(err); return }
        console.log(' Creating Table people')
        client.query(`
          CREATE TABLE people (
            pid   serial,
            FullName  char(100),
            PRIMARY KEY (pid)
          );`
        , (err, res) => {
          if (err) { console.log(err); return }
          console.log(' Creating Table acted')
          client.query(`
            CREATE TABLE acted (
              pid       serial,
              clipID    serial,
              add_info  text,
              PRIMARY KEY (pid, clipID),
              FOREIGN KEY (pid) REFERENCES people,
              FOREIGN KEY (clipID) REFERENCES clips
            );`
          , (err, res) => {
            if (err) { console.log(err); return }
            console.log(' Creating Table roles')
            client.query(`
            CREATE TABLE roles (
              pid           serial,
              order_credits int,
              roles         text,
              FOREIGN KEY (pid) REFERENCES people
            );`
              , (err, res) => {
                if (err) { console.log(err); return }
                console.log(' Creating Table countries')
                client.query(`
                CREATE TABLE countries(
                  coID  serial,
                  name  char(100),
                  PRIMARY KEY (coID)
              );`, (err, res) => {
                if (err) { console.log(err); return }
                startRepl()
                parseCSV.run()
              })
            })
          })
        })
      })
    })
  })

}

// Otherwise (and then) start the console
const startRepl = () => {
  global.client = client
  console.log('Database Exists')
  console.log('run \x1b[33mclient.query(\'SELECT * FROM A\').then(print)\x1b[0m')
  require('repl').start({})
}

// The actual check of weather the good tables exists