class CreateGritAssaysDataTableColumns < ActiveRecord::Migration[7.2]
  def up
    create_table :grit_assays_data_table_columns, id: false do |t|
      t.bigint :id, primary_key: true, default: -> { 'nextval(\'grit_seq\'::regclass)' }
      t.string :created_by, limit: 30, null: false, default: "SYSTEM"
      t.datetime :created_at, null: false, default: -> { 'CURRENT_TIMESTAMP' }
      t.string :updated_by, limit: 30
      t.datetime :updated_at
      t.string :name, null: false
      t.string :safe_name, null: false, limit: 32
      t.references :data_table, null: false, foreign_key: { name: "assays_data_table_entities_assays_data_table_id_fkey", to_table: "grit_assays_data_tables" }
      t.references :assay_data_sheet_column, foreign_key: { name: "assays_data_table_entities_assays_assay_data_sheet_column_id_fkey", to_table: "grit_assays_assay_data_sheet_columns" }
      t.integer :sort
      t.string :aggregation_method
      t.string :source_type, null: false, default: "assay_data_sheet_column"
      t.string :entity_attribute_name
      t.jsonb :pivots, default: []

      t.unique_constraint([:safe_name,:data_table_id], name: "unique_safe_name_data_table_id", deferrable: :deferred)
    end

    execute "CREATE TRIGGER manage_stamps_grit_assays_data_table_columns BEFORE INSERT OR UPDATE ON public.grit_assays_data_table_columns FOR EACH ROW EXECUTE FUNCTION public.manage_stamps();"
  end

  def down
    drop_table :grit_assays_data_table_columns

    execute "DROP TRIGGER IF EXISTS manage_stamps_grit_assays_data_table_columns ON public.grit_assays_data_table_columns;"
  end
end
