exports.up = async function (knex) {
  // Create standard Account object
  await knex.schema.createTable('accounts', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('(UUID())'));
    table.string('name', 255).notNullable();
    table.string('website', 255);
    table.string('phone', 50);
    table.string('industry', 100);
    table.uuid('ownerId');
    table.timestamps(true, true);

    table
      .foreign('ownerId')
      .references('id')
      .inTable('users')
      .onDelete('SET NULL');
    table.index(['name']);
    table.index(['ownerId']);
  });

  // Insert Account object definition
  const [objectId] = await knex('object_definitions').insert({
    id: knex.raw('(UUID())'),
    apiName: 'Account',
    label: 'Account',
    pluralLabel: 'Accounts',
    description: 'Standard Account object',
    isSystem: true,
    isCustom: false,
    created_at: knex.fn.now(),
    updated_at: knex.fn.now(),
  });

  // Insert Account field definitions
  const objectDefId =
    objectId ||
    (await knex('object_definitions').where('apiName', 'Account').first()).id;

  await knex('field_definitions').insert([
    {
      id: knex.raw('(UUID())'),
      objectDefinitionId: objectDefId,
      apiName: 'name',
      label: 'Account Name',
      type: 'String',
      length: 255,
      isRequired: true,
      isSystem: true,
      isCustom: false,
      displayOrder: 1,
      created_at: knex.fn.now(),
      updated_at: knex.fn.now(),
    },
    {
      id: knex.raw('(UUID())'),
      objectDefinitionId: objectDefId,
      apiName: 'website',
      label: 'Website',
      type: 'String',
      length: 255,
      isSystem: true,
      isCustom: false,
      displayOrder: 2,
      created_at: knex.fn.now(),
      updated_at: knex.fn.now(),
    },
    {
      id: knex.raw('(UUID())'),
      objectDefinitionId: objectDefId,
      apiName: 'phone',
      label: 'Phone',
      type: 'String',
      length: 50,
      isSystem: true,
      isCustom: false,
      displayOrder: 3,
      created_at: knex.fn.now(),
      updated_at: knex.fn.now(),
    },
    {
      id: knex.raw('(UUID())'),
      objectDefinitionId: objectDefId,
      apiName: 'industry',
      label: 'Industry',
      type: 'String',
      length: 100,
      isSystem: true,
      isCustom: false,
      displayOrder: 4,
      created_at: knex.fn.now(),
      updated_at: knex.fn.now(),
    },
    {
      id: knex.raw('(UUID())'),
      objectDefinitionId: objectDefId,
      apiName: 'ownerId',
      label: 'Owner',
      type: 'Reference',
      referenceObject: 'User',
      isSystem: true,
      isCustom: false,
      displayOrder: 5,
      created_at: knex.fn.now(),
      updated_at: knex.fn.now(),
    },
  ]);
};

exports.down = function (knex) {
  return knex.schema.dropTableIfExists('accounts');
};