exports.up = function (knex) {
  return knex.schema
    .createTable('object_definitions', (table) => {
      table.uuid('id').primary().defaultTo(knex.raw('(UUID())'));
      table.string('apiName', 255).notNullable().unique();
      table.string('label', 255).notNullable();
      table.string('pluralLabel', 255);
      table.text('description');
      table.boolean('isSystem').defaultTo(false);
      table.boolean('isCustom').defaultTo(true);
      table.timestamps(true, true);

      table.index(['apiName']);
    })
    .createTable('field_definitions', (table) => {
      table.uuid('id').primary().defaultTo(knex.raw('(UUID())'));
      table.uuid('objectDefinitionId').notNullable();
      table.string('apiName', 255).notNullable();
      table.string('label', 255).notNullable();
      table.string('type', 50).notNullable(); // String, Number, Date, Boolean, Reference, etc.
      table.integer('length');
      table.integer('precision');
      table.integer('scale');
      table.string('referenceObject', 255);
      table.text('defaultValue');
      table.text('description');
      table.boolean('isRequired').defaultTo(false);
      table.boolean('isUnique').defaultTo(false);
      table.boolean('isSystem').defaultTo(false);
      table.boolean('isCustom').defaultTo(true);
      table.integer('displayOrder').defaultTo(0);
      table.timestamps(true, true);

      table
        .foreign('objectDefinitionId')
        .references('id')
        .inTable('object_definitions')
        .onDelete('CASCADE');
      table.unique(['objectDefinitionId', 'apiName']);
      table.index(['objectDefinitionId']);
    });
};

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