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

  def up do
    # Fix voltage sensors for RouterOS/Mikrotik devices
    # These should have sensor_divisor=10 (deci-volts) but were incorrectly set to 1

    # Update voltage sensors where:
    # 1. sensor_type = 'voltage'
    # 2. sensor_unit = 'V'
    # 3. sensor_divisor = 1 (incorrect)
    # 4. OID starts with Mikrotik gauge table: 1.3.6.1.4.1.14988.1.1.3.100.1

    execute """
    UPDATE snmp_sensors
    SET sensor_divisor = 10
    WHERE sensor_type = 'voltage'
      AND sensor_unit = 'V'
      AND sensor_divisor = 1
      AND sensor_oid LIKE '1.3.6.1.4.1.14988.1.1.3.100.1.3.%'
    """

    # Also divide existing sensor_reading values by 10 for these sensors
    # This fixes historical data so graphs show correct values
    execute """
    UPDATE snmp_sensor_readings
    SET value = value / 10
    WHERE sensor_id IN (
      SELECT id
      FROM snmp_sensors
      WHERE sensor_type = 'voltage'
        AND sensor_unit = 'V'
        AND sensor_divisor = 10
        AND sensor_oid LIKE '1.3.6.1.4.1.14988.1.1.3.100.1.3.%'
    )
    """
  end

  def down do
    # Revert voltage sensors back to divisor=1
    execute """
    UPDATE snmp_sensors
    SET sensor_divisor = 1
    WHERE sensor_type = 'voltage'
      AND sensor_unit = 'V'
      AND sensor_divisor = 10
      AND sensor_oid LIKE '1.3.6.1.4.1.14988.1.1.3.100.1.3.%'
    """

    # 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_type = 'voltage'
        AND sensor_unit = 'V'
        AND sensor_divisor = 1
        AND sensor_oid LIKE '1.3.6.1.4.1.14988.1.1.3.100.1.3.%'
    )
    """
  end
end
