# frozen_string_literal: true

class AddTimeAwareUniqueIndexesToRules < ActiveRecord::Migration[8.1]
  def up
    # First, clean up existing duplicate records to allow unique constraints
    cleanup_duplicate_rules

    # Add time-aware unique indexes for policy-generated rules
    # This prevents exact duplicate time windows while allowing temporal flexibility

    # For temporary rules (with expiration dates)
    add_index :rules,
              [:network_range_id, :waf_action, :waf_policy_id, :expires_at],
              name: 'index_rules_on_network_policy_expires_unique',
              unique: true,
              where: "source = 'policy' AND expires_at IS NOT NULL"

    # For permanent rules (no expiration date)
    add_index :rules,
              [:network_range_id, :waf_action, :waf_policy_id],
              name: 'index_rules_on_network_policy_unique',
              unique: true,
              where: "source = 'policy' AND expires_at IS NULL"

    # Additional indexes for performance
    add_index :rules, [:source, :expires_at], name: 'index_rules_on_source_expires'
    add_index :rules, [:waf_policy_id, :expires_at], name: 'index_rules_on_policy_expires'
  end

  def down
    remove_index :rules, name: 'index_rules_on_network_policy_expires_unique'
    remove_index :rules, name: 'index_rules_on_network_policy_unique'
    remove_index :rules, name: 'index_rules_on_source_expires'
    remove_index :rules, name: 'index_rules_on_policy_expires'
  end

  private

  def cleanup_duplicate_rules
    # Clean up duplicates for policy rules with the same expiration time
    duplicate_sql = <<~SQL
      WITH ranked_rules AS (
        SELECT id,
               ROW_NUMBER() OVER (
                 PARTITION BY network_range_id, waf_action, waf_policy_id, expires_at
                 ORDER BY created_at DESC
               ) as rn
        FROM rules
        WHERE source = 'policy'
      )
      DELETE FROM rules
      WHERE id IN (SELECT id FROM ranked_rules WHERE rn > 1)
    SQL

    execute duplicate_sql
    Rails.logger.info "Cleaned up duplicate policy rules with same expiration times"
  end
end
