Database migrations and schema management

# Custom index
class AddCompoundIndexToPosts < ActiveRecord::Migration[7.0]
  def change
    add_index :posts, [:user_id, :status]
    add_index :posts, [:status, :created_at], where: "status = 'published'"

    # Full-text search index (PostgreSQL)
    add_index :posts, :content, using: :gin,
      opclass: :gin_trgm_ops  # Requires pg_trgm extension
  end
end

# Data migration
class MigrateUserRoles < ActiveRecord::Migration[7.0]
  def up
    User.where(admin: true).update_all(role: 'admin')
    User.where(admin: false).update_all(role: 'user')

    remove_column :users, :admin
  end

  def down
    add_column :users, :admin, :boolean, default: false

    User.where(role: 'admin').update_all(admin: true)
  end
end

# Reversible block for complex operations
class AddStatusToUsers < ActiveRecord::Migration[7.0]
  def change
    add_column :users, :status, :string

    reversible do |dir|
      dir.up do
        User.update_all(status: 'active')
      end

      dir.down do
        # Cleanup logic
      end
    end

    change_column_null :users, :status, false
  end
end

# Add constraint
class AddConstraintToUsers < ActiveRecord::Migration[7.0]
  def change
    add_check_constraint :users, "age >= 18", name: "users_age_check"

    # Uniqueness constraint
    add_index :users, :email, unique: true
  end
end

# Create join table
class CreateJoinTableUsersGroups < ActiveRecord::Migration[7.0]
  def change
    create_join_table :users, :groups do |t|
      t.index :user_id
      t.index :group_id
      t.timestamps
    end
  end
end

# Enable PostgreSQL extension
class EnablePgExtensions < ActiveRecord::Migration[7.0]
  def change
    enable_extension 'uuid-ossp'
    enable_extension 'pg_trgm'  # Trigram matching for LIKE queries
    enable_extension 'hstore'   # Key-value storage
  end
end

# Create enum type (PostgreSQL)
class CreatePostStatusEnum < ActiveRecord::Migration[7.0]
  def up
    execute <<-SQL
      CREATE TYPE post_status AS ENUM ('draft', 'published', 'archived');
    SQL

    add_column :posts, :status, :post_status, default: 'draft'
  end

  def down
    remove_column :posts, :status

    execute <<-SQL
      DROP TYPE post_status;
    SQL
  end
end