import Promise from 'bluebird';

const dataCount = 1000000;
const orgId = 'abc';
const orgName = 'abc;'

var knex = require('knex')({
  client: 'mysql',
  connection: {
    host: '127.0.0.1',
    user: 'root',
    password: '19890213',
    database: 'test',
  }
});

let t1, t2, t3, t4;

knex.schema.dropTableIfExists('candidates')
.then(() => {
  return knex.schema.dropTableIfExists('jobs')
})
.then(() => {
  return knex.schema.dropTableIfExists('organizations');
})
.then(() => {
  return knex.schema.createTableIfNotExists('organizations', (table) => {
    table.string('id').primary();
    table.string('name');
  });
})
.then(() => {
  return knex.schema.createTableIfNotExists('jobs', (table) => {
    table.string('id').primary();
    table.string('org_id');
  });
})
.then(() => {
  const q = knex.schema.createTableIfNotExists('candidates', (table) => {
    table.increments('id');
    table.string('org_id').references('organizations.id').onDelete('CASCADE').onUpdate('CASCADE');
    table.string('job_id').references('jobs.id').onDelete('SET NULL');
    table.integer('archived');
    table.integer('deleted');
  });
  return q;
})
.then(() => {
  // seed data
  // 10000 jobs
  const jobs = [];
  const candidates = [];

  for (var i = 0; i < dataCount; i ++ ) {
    const jobId = i;
    jobs.push({
      id: jobId,
      org_id: orgId,
    });
    candidates.push({
      org_id: orgId,
      job_id: jobId,
      archived: 0,
      deleted: 0,
    });
  }
  const org = {
    id: orgId,
    name: orgName,
  }
  const q0 = knex('organizations').insert(org);
  const q1 = knex('jobs').insert(jobs);
  const q2 = knex('candidates').insert(candidates);
  return q0.then(() => {
    return q1;
  })
  .then(() => {
    return q2;
  });
})
.then(() => {
  console.log('SEED DATA DONE');
  const orgId = 'abc';
  const cJobIdSubQuery = knex.select('candidates.id', 'candidates.job_id')
  .from('candidates')
  .where('candidates.org_id', orgId)
  .andWhere('candidates.archived', 0)
  .andWhere('candidates.deleted', 0)
  .as('cJob');

  const query = knex.select(knex.raw('jobs.id, count(cJob.id) as count'))
  .from('jobs')
  .leftJoin(cJobIdSubQuery, 'jobs.id', 'cJob.job_id')
  .where('jobs.org_id', orgId)
  .groupBy('jobs.id');

  console.log('query1: ', query.toString())

  t1 = Date.now();

  return query;
})
.then((rows) => {
  t2 = Date.now();
  console.log('used: ', t2 - t1)

  // Start new query
  const query = knex.select('j.id', knex.raw(`count(c.id) as count`))
  .from('jobs as j')
  .leftJoin('candidates as c', 'j.id', 'c.job_id')
  .where('j.org_id', orgId)
  .where(function() {
    this.where('c.archived', 0)
    .orWhereNull('c.archived');
  })
  .where(function() {
    this.where('c.deleted', 0)
    .orWhereNull('c.deleted');
  })
  .groupBy('j.id');

  console.log('query2: ', query.toString())
  t3 = Date.now();

  return query;
})
.then((rows) => {
  t4 = Date.now();
  console.log('used: ', t4 - t3)
})