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

  def up do
    # Fix voltage sensors that were incorrectly classified as temperature
    # These sensors have "voltage" in their name but were classified as temperature
    # This happens when MikroTik devices report incorrect mtxrGaugeUnit values

    # Update sensors: set correct type, unit, and divisor
    execute """
    UPDATE snmp_sensors
    SET sensor_type = 'voltage',
        sensor_unit = 'V',
        sensor_divisor = 10
    WHERE sensor_oid LIKE '1.3.6.1.4.1.14988.1.1.3.100.1.3.%'
      AND sensor_type = 'temperature'
      AND LOWER(sensor_descr) LIKE '%voltage%'
      AND sensor_divisor = 1
    """

    # Divide existing sensor_reading values by 10 to correct historical data
    execute """
    UPDATE snmp_sensor_readings
    SET value = value / 10
    WHERE sensor_id IN (
      SELECT id
      FROM snmp_sensors
      WHERE sensor_oid LIKE '1.3.6.1.4.1.14988.1.1.3.100.1.3.%'
        AND sensor_type = 'voltage'
        AND LOWER(sensor_descr) LIKE '%voltage%'
        AND sensor_divisor = 10
    )
    """
  end

  def down do
    # Revert changes
    execute """
    UPDATE snmp_sensors
    SET sensor_type = 'temperature',
        sensor_unit = '',
        sensor_divisor = 1
    WHERE sensor_oid LIKE '1.3.6.1.4.1.14988.1.1.3.100.1.3.%'
      AND sensor_type = 'voltage'
      AND LOWER(sensor_descr) LIKE '%voltage%'
      AND sensor_divisor = 10
    """

    # Multiply sensor readings back by 10
    execute """
    UPDATE snmp_sensor_readings
    SET value = value * 10
    WHERE sensor_id IN (
      SELECT id
      FROM snmp_sensors
      WHERE sensor_oid LIKE '1.3.6.1.4.1.14988.1.1.3.100.1.3.%'
        AND sensor_type = 'temperature'
        AND LOWER(sensor_descr) LIKE '%voltage%'
        AND sensor_divisor = 1
    )
    """
  end
end
