exports.up = (knex, Promise) => Promise.all([
  knex.raw('create extension if not exists "uuid-ossp"'), // Add postgres UUID support
  knex.schema.withSchema('public')
    // Base User Schema
    .createTableIfNotExists('users', t => {
      t.uuid('id')
        .primary()
        .notNullable()
        .unique()
        .defaultTo(knex.raw('uuid_generate_v4()'));
      t.timestamps(true, true); // use timestamps, default to now
      t.string('country', 2)
        .notNullable();
      t.string('email')
        .unique()
        .notNullable();
      t.boolean('email_verified')
        .notNullable()
        .defaultTo(false);
      t.string('first_name')
        .notNullable();
      t.string('last_name')
        .notNullable();
      t.string('password')
        .notNullable();
      t.string('phone_number')
        .nullable()
        .defaultTo(null);
      t.string('profile_photo_url')
        .nullable()
        .defaultTo(null);
      t.json('system_roles')
        .notNullable()
        .defaultTo(
          JSON.stringify([
            'user',
          ])
        );
      t.string('timezone')
        .notNullable()
        .defaultTo('UTC');
      t.boolean('tos_accepted')
        .notNullable()
        .defaultTo(false);
      t.timestamp('deleted_at')
        .nullable()
        .defaultTo(null);
    })
    // Base Accounts Schema
    .createTableIfNotExists('accounts', t => {
      t.uuid('id')
        .primary()
        .notNullable()
        .unique()
        .defaultTo(knex.raw('uuid_generate_v4()'));
      t.timestamps(true, true); // use timestamps, default to now
      t.uuid('owner_id')
        .notNullable()
        .references('id')
        .inTable('users')
        .onDelete('CASCADE');
      t.uuid('created_by')
        .notNullable()
        .references('id')
        .inTable('users')
        .onDelete('CASCADE');
      t.enu('type', ['personal', 'organization'])
        .notNullable()
        .defaultTo('personal');
      t.timestamp('deleted_at')
        .nullable()
        .defaultTo(null);
    })
    // Associate Users to Accounts
    .createTableIfNotExists('account_memberships', t => {
      t.increments('id')
        .primary();
      t.timestamps(true, true); // use timestamps, default to now
      t.uuid('account_id')
        .notNullable()
        .references('id')
        .inTable('accounts')
        .onDelete('CASCADE');
      t.uuid('user_id')
        .notNullable()
        .references('id')
        .inTable('users')
        .onDelete('CASCADE');
      t.enu('role', ['member', 'admin'])
        .notNullable()
        .defaultTo('member');
    })
    // Store default accounts for users
    .createTableIfNotExists('default_user_accounts', t => {
      t.uuid('user_id')
        .notNullable()
        .references('id')
        .inTable('users')
        .onDelete('CASCADE');
      t.uuid('account_id')
        .notNullable()
        .references('id')
        .inTable('accounts')
        .onDelete('CASCADE');
    }),
]);

exports.down = (knex, Promise) => Promise.all([
  knex.raw('DROP TABLE IF EXISTS public.users CASCADE'),
  knex.raw('DROP TABLE IF EXISTS public.accounts CASCADE'),
  knex.raw('DROP TABLE IF EXISTS public.account_memberships CASCADE'),
  knex.raw('DROP TABLE IF EXISTS public.default_user_accounts CASCADE'),
]);