#!/bin/env node

'use strict';

const pg = require('../server/db');

// pg.schema.dropTableIfExists('transactions')
pg.raw('DROP TABLE IF EXISTS companies CASCADE')
  .then(() => pg.raw('DROP TABLE IF EXISTS years CASCADE'))
  .then(() => pg.raw('DROP TABLE IF EXISTS accounts CASCADE'))
  .then(() => pg.raw('DROP TABLE IF EXISTS account_types CASCADE'))
  .then(() => pg.raw('DROP TABLE IF EXISTS tickets CASCADE'))
  .then(() => pg.raw('DROP TABLE IF EXISTS ticket_types CASCADE'))
  .then(() => pg.raw('DROP TABLE IF EXISTS transactions CASCADE'))
  .then(() => pg.raw('DROP TABLE IF EXISTS initial_balance CASCADE'))

  .then(() => pg.schema.createTable('companies', (table) => {
    table.increments();
    table.text('name').notNullable().unique();
    table.text('organization_number').notNullable().unique();
  }))
  .then(() => pg.schema.createTable('years', (table) => {
    table.increments();
    table.integer('company_id').references('companies.id').notNullable().onUpdate('cascade');
    table.integer('previous_year_id').references('id');
    table.date('start_date').notNullable();
    table.date('end_date').notNullable();
    table.text('name');
  }))
  .then(() => pg.schema.createTable('account_types', (table) => {
    table.increments();
    table.text('name').notNullable().unique();
  }))
  .then(() => pg.schema.createTable('accounts', (table) => {
    table.increments();
    table.text('name').notNullable();
    table.integer('number').notNullable();
    table.integer('sru');
    table.integer('account_type_id').notNullable().references('account_types.id').onUpdate('cascade');
  }))
  .then(() => pg.schema.createTable('initial_balance', (table) => {
    table.increments();
    table.integer('year_id').notNullable().references('years.id').onUpdate('cascade');
    table.integer('account_id').notNullable().references('accounts.id').onUpdate('cascade');
    table.decimal('amount').notNullable();
  }))
  .then(() => pg.schema.createTable('ticket_types', (table) => {
    table.increments();
    table.integer('number').notNullable();
    table.text('name').notNullable().unique();
  }))
  .then(() => pg.schema.createTableIfNotExists('tickets', (table) => {
    table.increments();
    table.integer('type');
    table.integer('ticket_type_id').references('ticket_types.id').onUpdate('cascade');
    // table.integer('ticket_type_id').notNullable().references('ticket_types.id').onUpdate('cascade');
    table.integer('number').notNullable();
    table.date('date').notNullable();
    table.text('title');
    table.date('date_created').notNullable();
    table.timestamp('uploaded_at').defaultsTo(pg.fn.now());
  }))
  .then(() => pg.schema.createTableIfNotExists('transactions', (table) => {
    table.increments();
    table.integer('account_id').notNullable().references('accounts.id').onUpdate('cascade');
    table.decimal('amount', 14, 2).notNullable();
    table.integer('ticket_id').notNullable().references('tickets.id').onUpdate('cascade');
  }))
  .then(() => pg('account_types').insert([{ name: 'Tillgång' }, { name: 'Skuld' }, { name: 'Intäkt' }, { name: 'Kostnad' }]))
  .then(() => pg.destroy())
  .catch((err) => {
    console.log(err);
    pg.destroy();
  });