## Entity relation design for reference: https://github.com/pmeaney/expressKnexPostgres-timetracker/blob/master/timetracker_ERD.png

## MIGRATION FILE

/*
Table creation order:
locations,
activity_codes,
cost_centers,
employees,
projects,
activities,
timesheets
*/
exports.up = function(knex, Promise) {
	return Promise.all([

	knex.schema.createTable('locations', function(table) {
	  table.increments('location_id').primary();
	  table.text('location_name');
	  table.text('location_address');
	  table.text('location_city');
	  table.text('location_state');
	  table.text('location_zip');
	  table.enu('location_type', ['residential', 'commercial']);
	  table.timestamps(true, true);// this automatically sets 'created at' and 'updated at' timestamps
	}),

	knex.schema.createTable('activity_codes', function(table) {
	  table.increments('activity_code').primary();
	  table.text('activity_type');
  	  table.timestamps(true, true);// this automatically sets 'created at' and 'updated at' timestamps
	}),

	knex.schema.createTable('cost_centers', function(table) {
	  table.increments('cost_center_id').primary();
	  table.text('cost_center_name');
  	  table.timestamps(true, true);// this automatically sets 'created at' and 'updated at' timestamps
	}),

	knex.schema.createTable('employees', function(table) {
	  table.increments('employee_id').primary();
	  table.text('firstName');
	  table.text('lastName');
	  table.text('phone');
	  table.text('email');
	  table.integer('pay');
	  table.timestamps(true, true);// this automatically sets 'created at' and 'updated at' timestamps
	}),

	knex.schema.createTable('projects', function(table) {
	  table.increments('project_id').primary();
	  table.integer('location_id').references('locations.location_id');
	  table.integer('project_mgr_emp_id').references('employees.employee_id');
	  table.date('project_date_begin');
	  table.date('project_date_end');
	  table.timestamps(true, true);// this automatically sets 'created at' and 'updated at' timestamps
	}),


	// 1:M activities:timesheets --> can have several timesheets per activity
	// ==> allows for multiple employees per pactivity
	knex.schema.createTable('activities', function(table) {
	  table.increments('activity_id').primary();
	  table.integer('activity_code').references('activity_codes.activity_code');
	  table.integer('project_id').references('projects.project_id');
	  table.text('activity_notes');
	  table.timestamp('activity_datetime_begin');
	  table.timestamp('activity_datetime_end');


	  table.timestamps(true, true);// this automatically sets 'created at' and 'updated at' timestamps
	}),

	// get all timesheets by activity_id and order descending by date
	// to, for example, get list of timesheets
	knex.schema.createTable('timesheets', function(table) {
	  table.increments('timesheet_id').primary();
	  table.integer('activity_id').references('activities.activity_id');
	  table.integer('authorized_by_emp_id').references('employees.employee_id');
	  table.integer('cost_center_id').references('cost_centers.cost_center_id');
	  table.integer('timesheet_for_emp_id').references('employees.employee_id');
	  table.text('timesheet_notes');
	  table.timestamp('timesheet_submitted_datetime');
	  table.timestamp('timesheet_clockin');
	  table.timestamp('timesheet_clockout');

	  // info on GPS precision: https://en.wikipedia.org/wiki/Decimal_degrees
	  table.specificType('timesheet_clockin_lat', 'DECIMAL'); //.defaultTo(knex.raw('POINT (37.3875, -122.0575)'))
	  table.specificType('timesheet_clockin_long', 'DECIMAL'); //.defaultTo(knex.raw('POINT (37.3875, -122.0575)'))
	  table.specificType('timesheet_clockout_lat', 'DECIMAL'); //.defaultTo(knex.raw('POINT (37.3875, -122.0575)'))
	  table.specificType('timesheet_clockout_long', 'DECIMAL'); //.defaultTo(knex.raw('POINT (37.3875, -122.0575)'))

	  table.timestamps(true, true);// this automatically sets 'created at' and 'updated at' timestamps
	}),

	])
};

exports.down = function(knex, Promise) {

/*
Table creation order:
locations,
activity_codes,
cost_centers,
employees,
projects,
activities,
timesheets
*/
	return Promise.all([
	    knex.schema.dropTable('timesheets'),
	    knex.schema.dropTable('activities'),
	    knex.schema.dropTable('projects'),
	    knex.schema.dropTable('employees'),
	    knex.schema.dropTable('cost_centers'),
	    knex.schema.dropTable('activity_codes'),
	    knex.schema.dropTable('locations'),
	  ]);

};


## SEED FILE
/*
Table creation order:
locations,
activity_codes,
cost_centers,
employees,
projects,
activities,
timesheets
*/


exports.seed = function(knex, Promise) {
  // Deletes ALL existing entries
  return knex('timesheets').del()
    .then(() => knex('activities').del())
    .then(() => knex('projects').del())
    .then(() => knex('employees').del())
    .then(() => knex('cost_centers').del())
    .then(() => knex('activity_codes').del())
    .then(() => knex('locations').del())

    .then(() => {  // Using promises allows to catch errors during process
      return Promise.all([

      knex('locations').insert([
        { location_id: 1, location_name: 'commercial: Brisbane post office', location_address: "280 Old County Rd", location_city: "brisbane", location_state: "ca", location_zip: "94005", location_type: "commercial"},
        { location_id: 2, location_name: 'commercial: Brisbane Hardware & Sply Inc', location_address: "1 Visitacion Ave", location_city: "brisbane", location_state: "ca", location_zip: "94005", location_type: "commercial"},
      ]),

      knex('activity_codes').insert([
        { activity_code: 1, activity_type: 'painting'},
        { activity_code: 2, activity_type: 'carpentry'},
        { activity_code: 3, activity_type: 'electrical'},
        { activity_code: 4, activity_type: 'plumbing'},
        { activity_code: 5, activity_type: 'landscaping'},
        { activity_code: 6, activity_type: 'irrigation'},
      ]),

      /*
        cost centers allow me to create different timesheet categories per department
        more info on cost center info design: http://www.doa.la.gov/OTS/ERP/blueprint/finance/FI-CO-003%20Presentation.pdf
      */

      knex('cost_centers').insert([
        { cost_center_id: 1, cost_center_name: 'operations payroll' }
      ]),

      knex('employees').insert([
        { employee_id: 1, firstName: 'George', lastName: 'Jefferson', phone: "123-456-7890", email: "email@gmail.com", pay: "20" },
        { employee_id: 2, firstName: 'Bill', lastName: 'Smith', phone: "123-456-7890", email: "email@gmail.com", pay: "20" },
        { employee_id: 3, firstName: 'James', lastName: 'Bond', phone: "123-456-7890", email: "email@gmail.com", pay: "20" },
        { employee_id: 4, firstName: 'Sam', lastName: 'Anderson', phone: "123-456-7890", email: "email@gmail.com", pay: "20" },
      ]),

      knex('projects').insert([
        { project_id: 1, location_id: 1, project_mgr_emp_id: 1, project_date_begin: '2018-01-01', project_date_end: '2018-01-20'},
        { project_id: 2, location_id: 2, project_mgr_emp_id: 3, project_date_begin: '2018-01-15', project_date_end: '2018-01-30'},
      ]),

// note: UTC is 7 hours ahead of PST
//  2018-01-01T00:00:00.000Z
      knex('activities').insert([
        { activity_id: 1, activity_code: 1, project_id: 1, activity_notes: 'paint with blue until you run out, then switch to red', activity_datetime_begin: '2018-01-01T13:00:00.000Z', activity_datetime_end: '2018-01-01T21:00:00.000Z'},
        { activity_id: 2, activity_code: 1, project_id: 2, activity_notes: 'work on building shelf in store', activity_datetime_begin: '2018-02-01T13:00:00.000Z', activity_datetime_end: '2018-02-01T21:00:00.000Z'},
      ]),

      // for coordinates, take first 8 digits supplied
      knex('timesheets').insert([
        { timesheet_id: 1, activity_id: 1, authorized_by_emp_id: 1, cost_center_id: 1, timesheet_for_emp_id: 2, timesheet_notes: 'ran 15 minutes late', timesheet_submitted_datetime: '2018-01-14T15:00:00.000Z', timesheet_clockin: '2018-01-01T13:15:00.000Z', timesheet_clockout: '2018-01-01T21:00:00.000Z', timesheet_clockin_lat: '37.685246', timesheet_clockin_long: '-122.40277', timesheet_clockout_lat: '37.685116', timesheet_clockout_long: '-122.40140'},
        { timesheet_id: 2, activity_id: 2, authorized_by_emp_id: 1, cost_center_id: 1, timesheet_for_emp_id: 3, timesheet_notes: 'all good', timesheet_submitted_datetime: '2018-02-14T15:02:00.000Z', timesheet_clockin: '2018-02-01T13:05:00.000Z', timesheet_clockout: '2018-02-02T21:05:00.000Z', timesheet_clockin_lat: '37.684578', timesheet_clockin_long: '-122.40233', timesheet_clockout_lat: '37.684116', timesheet_clockout_long: '-122.40240'},
      ])

    ]) // end promise.all
    .catch(reason => {
        console.log('inner error: ',  reason);
    })

  }) // end main .then
  .catch(reason => {
      console.log('outer error:', reason);
  }); // last .then
};