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

  @moduledoc """
  Fix unique constraints to handle NULL values correctly.

  PostgreSQL's default unique constraint treats NULL values as distinct,
  allowing multiple rows with the same non-NULL values but different NULL values.

  This migration replaces the existing unique constraints with ones that use
  NULLS NOT DISTINCT, which treats NULL values as equal for uniqueness purposes.

  This fixes race conditions where concurrent upserts with NULL values could
  create duplicate records.
  """

  def up do
    # Drop old unique indexes
    drop_if_exists unique_index(:snmp_neighbors, [:interface_id, :remote_chassis_id, :protocol])
    drop_if_exists unique_index(:snmp_mac_addresses, [:device_id, :mac_address, :vlan_id])

    # Clean up existing duplicates before creating new constraints
    # Keep the most recently updated record, delete older duplicates
    execute("""
    DELETE FROM snmp_neighbors
    WHERE id IN (
      SELECT id
      FROM (
        SELECT id,
               ROW_NUMBER() OVER (
                 PARTITION BY interface_id, COALESCE(remote_chassis_id, ''), protocol
                 ORDER BY updated_at DESC
               ) AS rn
        FROM snmp_neighbors
      ) t
      WHERE rn > 1
    )
    """)

    execute("""
    DELETE FROM snmp_mac_addresses
    WHERE id IN (
      SELECT id
      FROM (
        SELECT id,
               ROW_NUMBER() OVER (
                 PARTITION BY device_id, mac_address, COALESCE(vlan_id::text, '')
                 ORDER BY updated_at DESC
               ) AS rn
        FROM snmp_mac_addresses
      ) t
      WHERE rn > 1
    )
    """)

    # Create new unique indexes with NULLS NOT DISTINCT
    # This ensures NULL values are treated as equal for uniqueness
    create unique_index(:snmp_neighbors, [:interface_id, :remote_chassis_id, :protocol],
             nulls_distinct: false
           )

    create unique_index(:snmp_mac_addresses, [:device_id, :mac_address, :vlan_id],
             nulls_distinct: false
           )
  end

  def down do
    # Revert to original unique indexes (which allow multiple NULLs)
    drop_if_exists unique_index(:snmp_neighbors, [:interface_id, :remote_chassis_id, :protocol])
    drop_if_exists unique_index(:snmp_mac_addresses, [:device_id, :mac_address, :vlan_id])

    create unique_index(:snmp_neighbors, [:interface_id, :remote_chassis_id, :protocol])
    create unique_index(:snmp_mac_addresses, [:device_id, :mac_address, :vlan_id])
  end
end
