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

  def up do
    # Backfill SNMP v2c credentials from site → organization hierarchy
    execute """
    UPDATE devices d
    SET
      snmp_community = COALESCE(
        NULLIF(d.snmp_community, ''),
        s.snmp_community,
        o.snmp_community
      ),
      snmp_community_source = CASE
        WHEN d.snmp_community IS NOT NULL AND d.snmp_community != '' THEN 'device'
        WHEN s.snmp_community IS NOT NULL THEN 'site'
        ELSE 'organization'
      END,
      snmp_version = COALESCE(
        NULLIF(d.snmp_version, ''),
        s.snmp_version,
        o.snmp_version,
        '2c'
      ),
      snmp_port = COALESCE(
        d.snmp_port,
        s.snmp_port,
        o.snmp_port,
        161
      )
    FROM sites s
    INNER JOIN organizations o ON s.organization_id = o.id
    WHERE d.site_id = s.id
    """

    # Backfill SNMPv3 credentials from site → organization hierarchy
    execute """
    UPDATE devices d
    SET
      snmpv3_username = COALESCE(
        NULLIF(d.snmpv3_username, ''),
        s.snmpv3_username,
        o.snmpv3_username
      ),
      snmpv3_security_level = COALESCE(
        NULLIF(d.snmpv3_security_level, ''),
        s.snmpv3_security_level,
        o.snmpv3_security_level
      ),
      snmpv3_auth_protocol = COALESCE(
        NULLIF(d.snmpv3_auth_protocol, ''),
        s.snmpv3_auth_protocol,
        o.snmpv3_auth_protocol,
        'SHA-256'
      ),
      snmpv3_auth_password = COALESCE(
        d.snmpv3_auth_password,
        s.snmpv3_auth_password,
        o.snmpv3_auth_password
      ),
      snmpv3_priv_protocol = COALESCE(
        NULLIF(d.snmpv3_priv_protocol, ''),
        s.snmpv3_priv_protocol,
        o.snmpv3_priv_protocol,
        'AES'
      ),
      snmpv3_priv_password = COALESCE(
        d.snmpv3_priv_password,
        s.snmpv3_priv_password,
        o.snmpv3_priv_password
      ),
      snmpv3_credential_source = CASE
        WHEN d.snmpv3_username IS NOT NULL AND d.snmpv3_username != '' THEN 'device'
        WHEN s.snmpv3_username IS NOT NULL THEN 'site'
        ELSE 'organization'
      END
    FROM sites s
    INNER JOIN organizations o ON s.organization_id = o.id
    WHERE d.site_id = s.id
      AND d.snmp_version = '3'
    """

    # Backfill MikroTik credentials from site → organization hierarchy
    execute """
    UPDATE devices d
    SET
      mikrotik_username = COALESCE(
        d.mikrotik_username,
        s.mikrotik_username,
        o.mikrotik_username
      ),
      mikrotik_password = COALESCE(
        d.mikrotik_password,
        s.mikrotik_password,
        o.mikrotik_password
      ),
      mikrotik_port = COALESCE(
        d.mikrotik_port,
        s.mikrotik_port,
        o.mikrotik_port,
        8729
      ),
      mikrotik_ssh_port = COALESCE(
        d.mikrotik_ssh_port,
        s.mikrotik_ssh_port,
        o.mikrotik_ssh_port,
        22
      ),
      mikrotik_use_ssl = COALESCE(
        d.mikrotik_use_ssl,
        s.mikrotik_use_ssl,
        o.mikrotik_use_ssl,
        true
      ),
      mikrotik_enabled = COALESCE(
        d.mikrotik_enabled,
        s.mikrotik_enabled,
        o.mikrotik_enabled,
        false
      ),
      mikrotik_credential_source = CASE
        WHEN d.mikrotik_username IS NOT NULL THEN 'device'
        WHEN s.mikrotik_username IS NOT NULL THEN 'site'
        ELSE 'organization'
      END
    FROM sites s
    INNER JOIN organizations o ON s.organization_id = o.id
    WHERE d.site_id = s.id
    """
  end

  def down do
    # This migration is not reversible - we can't "un-denormalize" the data
    # The credentials are now explicitly stored on devices
    :ok
  end
end
