defmodule Towerops.Repo.Migrations.ChangeSensorOidFieldsToJsonb do
  use Ecto.Migration

  def up do
    # Convert oid column from VARCHAR to JSONB
    # Existing string values will be stored as JSON strings
    execute """
    ALTER TABLE profile_sensor_definitions
    ALTER COLUMN oid TYPE jsonb
    USING CASE
      WHEN oid IS NULL THEN NULL
      ELSE to_jsonb(oid)
    END
    """

    # Convert num_oid column from VARCHAR to JSONB
    execute """
    ALTER TABLE profile_sensor_definitions
    ALTER COLUMN num_oid TYPE jsonb
    USING CASE
      WHEN num_oid IS NULL THEN NULL
      ELSE to_jsonb(num_oid)
    END
    """
  end

  def down do
    # Convert back to VARCHAR(255), extracting string values from JSONB
    execute """
    ALTER TABLE profile_sensor_definitions
    ALTER COLUMN oid TYPE varchar(255)
    USING CASE
      WHEN oid IS NULL THEN NULL
      WHEN jsonb_typeof(oid) = 'string' THEN oid #>> '{}'
      ELSE oid::text
    END
    """

    execute """
    ALTER TABLE profile_sensor_definitions
    ALTER COLUMN num_oid TYPE varchar(255)
    USING CASE
      WHEN num_oid IS NULL THEN NULL
      WHEN jsonb_typeof(num_oid) = 'string' THEN num_oid #>> '{}'
      ELSE num_oid::text
    END
    """
  end
end
