-- Migration: Add IP logging to webhook_logs
-- Description: Add source_ip and user_agent columns for IP whitelisting

-- Add source_ip column if it doesn't exist
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'webhook_logs' AND column_name = 'source_ip'
  ) THEN
    ALTER TABLE webhook_logs ADD COLUMN source_ip VARCHAR(45);
    COMMENT ON COLUMN webhook_logs.source_ip IS 'Source IP address of webhook request (IPv4 or IPv6)';
  END IF;
END $$;

-- Add user_agent column if it doesn't exist
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'webhook_logs' AND column_name = 'user_agent'
  ) THEN
    ALTER TABLE webhook_logs ADD COLUMN user_agent TEXT;
    COMMENT ON COLUMN webhook_logs.user_agent IS 'User agent string from webhook request';
  END IF;
END $$;

-- Add index on source_ip for efficient IP-based queries
CREATE INDEX IF NOT EXISTS idx_webhook_logs_source_ip ON webhook_logs(source_ip);

-- Verify columns were added
SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'webhook_logs' 
  AND column_name IN ('source_ip', 'user_agent');