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

  def up do
    # Fix MikroTik gauge table sensors that were discovered with divisor 1
    # due to the bug in get_int_value() that rejected 0 values.
    #
    # This affects sensors where:
    # - mtxrGaugeUnit was 0, nil, or an unknown value
    # - Name-based override should have set divisor to 10
    # - But the override didn't run or failed, leaving divisor at 1
    #
    # We identify these by:
    # - OID matches gauge table pattern (1.3.6.1.4.1.14988.1.1.3.100.1.3.*)
    # - sensor_type should be temperature/voltage/current/power (tenths of unit)
    # - sensor_divisor is currently 1 (wrong)

    # Temperature sensors (should be tenths of degrees)
    execute """
    UPDATE snmp_sensors
    SET sensor_divisor = 10,
        sensor_unit = '°C',
        last_value = CASE
          WHEN last_value IS NOT NULL THEN last_value / 10.0
          ELSE NULL
        END
    WHERE sensor_oid LIKE '1.3.6.1.4.1.14988.1.1.3.100.1.3.%'
      AND sensor_type = 'temperature'
      AND sensor_divisor = 1
    """

    # Voltage sensors (should be tenths of volts)
    execute """
    UPDATE snmp_sensors
    SET sensor_divisor = 10,
        sensor_unit = 'V',
        last_value = CASE
          WHEN last_value IS NOT NULL THEN last_value / 10.0
          ELSE NULL
        END
    WHERE sensor_oid LIKE '1.3.6.1.4.1.14988.1.1.3.100.1.3.%'
      AND sensor_type = 'voltage'
      AND sensor_divisor = 1
    """

    # Current sensors (should be tenths of amps)
    execute """
    UPDATE snmp_sensors
    SET sensor_divisor = 10,
        sensor_unit = 'A',
        last_value = CASE
          WHEN last_value IS NOT NULL THEN last_value / 10.0
          ELSE NULL
        END
    WHERE sensor_oid LIKE '1.3.6.1.4.1.14988.1.1.3.100.1.3.%'
      AND sensor_type = 'current'
      AND sensor_divisor = 1
    """

    # Power sensors (should be tenths of watts)
    execute """
    UPDATE snmp_sensors
    SET sensor_divisor = 10,
        sensor_unit = 'W',
        last_value = CASE
          WHEN last_value IS NOT NULL THEN last_value / 10.0
          ELSE NULL
        END
    WHERE sensor_oid LIKE '1.3.6.1.4.1.14988.1.1.3.100.1.3.%'
      AND sensor_type = 'power'
      AND sensor_divisor = 1
    """
  end

  def down do
    # Revert to divisor 1 (incorrect, but reversible)

    execute """
    UPDATE snmp_sensors
    SET sensor_divisor = 1,
        sensor_unit = '',
        last_value = CASE
          WHEN last_value IS NOT NULL THEN last_value * 10.0
          ELSE NULL
        END
    WHERE sensor_oid LIKE '1.3.6.1.4.1.14988.1.1.3.100.1.3.%'
      AND sensor_type = 'temperature'
      AND sensor_divisor = 10
    """

    execute """
    UPDATE snmp_sensors
    SET sensor_divisor = 1,
        sensor_unit = '',
        last_value = CASE
          WHEN last_value IS NOT NULL THEN last_value * 10.0
          ELSE NULL
        END
    WHERE sensor_oid LIKE '1.3.6.1.4.1.14988.1.1.3.100.1.3.%'
      AND sensor_type = 'voltage'
      AND sensor_divisor = 10
    """

    execute """
    UPDATE snmp_sensors
    SET sensor_divisor = 1,
        sensor_unit = '',
        last_value = CASE
          WHEN last_value IS NOT NULL THEN last_value * 10.0
          ELSE NULL
        END
    WHERE sensor_oid LIKE '1.3.6.1.4.1.14988.1.1.3.100.1.3.%'
      AND sensor_type = 'current'
      AND sensor_divisor = 10
    """

    execute """
    UPDATE snmp_sensors
    SET sensor_divisor = 1,
        sensor_unit = '',
        last_value = CASE
          WHEN last_value IS NOT NULL THEN last_value * 10.0
          ELSE NULL
        END
    WHERE sensor_oid LIKE '1.3.6.1.4.1.14988.1.1.3.100.1.3.%'
      AND sensor_type = 'power'
      AND sensor_divisor = 10
    """
  end
end
