exports.up = function (knex) {
  return knex.schema
    // Add orgWideDefault to object_definitions
    .alterTable('object_definitions', (table) => {
      table
        .enum('orgWideDefault', ['private', 'public_read', 'public_read_write'])
        .defaultTo('private')
        .notNullable();
    })
    // Create role_object_permissions table
    .createTable('role_object_permissions', (table) => {
      table.uuid('id').primary().defaultTo(knex.raw('(UUID())'));
      table.uuid('roleId').notNullable();
      table.uuid('objectDefinitionId').notNullable();
      table.boolean('canCreate').defaultTo(false);
      table.boolean('canRead').defaultTo(false);
      table.boolean('canEdit').defaultTo(false);
      table.boolean('canDelete').defaultTo(false);
      table.boolean('canViewAll').defaultTo(false);
      table.boolean('canModifyAll').defaultTo(false);
      table.timestamps(true, true);

      table
        .foreign('roleId')
        .references('id')
        .inTable('roles')
        .onDelete('CASCADE');
      table
        .foreign('objectDefinitionId')
        .references('id')
        .inTable('object_definitions')
        .onDelete('CASCADE');
      table.unique(['roleId', 'objectDefinitionId']);
      table.index(['roleId']);
      table.index(['objectDefinitionId']);
    })
    // Create role_field_permissions table
    .createTable('role_field_permissions', (table) => {
      table.uuid('id').primary().defaultTo(knex.raw('(UUID())'));
      table.uuid('roleId').notNullable();
      table.uuid('fieldDefinitionId').notNullable();
      table.boolean('canRead').defaultTo(true);
      table.boolean('canEdit').defaultTo(true);
      table.timestamps(true, true);

      table
        .foreign('roleId')
        .references('id')
        .inTable('roles')
        .onDelete('CASCADE');
      table
        .foreign('fieldDefinitionId')
        .references('id')
        .inTable('field_definitions')
        .onDelete('CASCADE');
      table.unique(['roleId', 'fieldDefinitionId']);
      table.index(['roleId']);
      table.index(['fieldDefinitionId']);
    })
    // Create record_shares table for sharing specific records
    .createTable('record_shares', (table) => {
      table.uuid('id').primary().defaultTo(knex.raw('(UUID())'));
      table.uuid('objectDefinitionId').notNullable();
      table.uuid('recordId').notNullable();
      table.uuid('granteeUserId').notNullable();
      table.uuid('grantedByUserId').notNullable();
      table.json('accessLevel').notNullable(); // { canRead, canEdit, canDelete }
      table.timestamp('expiresAt').nullable();
      table.timestamp('revokedAt').nullable();
      table.timestamp('createdAt').defaultTo(knex.fn.now());
      table.timestamp('updatedAt').defaultTo(knex.fn.now());

      table
        .foreign('objectDefinitionId')
        .references('id')
        .inTable('object_definitions')
        .onDelete('CASCADE');
      table
        .foreign('granteeUserId')
        .references('id')
        .inTable('users')
        .onDelete('CASCADE');
      table
        .foreign('grantedByUserId')
        .references('id')
        .inTable('users')
        .onDelete('CASCADE');
      table.index(['objectDefinitionId', 'recordId']);
      table.index(['granteeUserId']);
      table.index(['expiresAt']);
      table.index(['revokedAt']);
    });
};

exports.down = function (knex) {
  return knex.schema
    .dropTableIfExists('record_shares')
    .dropTableIfExists('role_field_permissions')
    .dropTableIfExists('role_object_permissions')
    .alterTable('object_definitions', (table) => {
      table.dropColumn('orgWideDefault');
    });
};