foxhunt/migrations/010_remove_polygon_configurations.sql

-- Remove Polygon Configuration Migration
-- ====================================
-- This migration removes all Polygon-related configurations and references
-- from the Foxhunt HFT Trading System in preparation for dual-provider setup.
--
-- Prerequisites: Migration 009_dual_provider_configuration.sql must be applied first
-- Result: Clean removal of all Polygon references, system ready for Databento+Benzinga

-- === REMOVE POLYGON CONFIGURATION ENTRIES ===

-- Remove any existing Polygon configuration settings
DELETE FROM config_settings 
WHERE config_key ILIKE '%polygon%' 
   OR description ILIKE '%polygon%'
   OR category_path ILIKE '%polygon%';

-- Remove any existing Polygon categories
DELETE FROM config_categories 
WHERE category_name ILIKE '%polygon%' 
   OR description ILIKE '%polygon%'
   OR category_path ILIKE '%polygon%';

-- Remove any Polygon-related subscriptions
DELETE FROM config_subscriptions 
WHERE config_pattern ILIKE '%polygon%' 
   OR category_pattern ILIKE '%polygon%'
   OR service_name ILIKE '%polygon%';

-- === REMOVE POLYGON PROVIDER TABLES (IF THEY EXIST) ===

-- Drop Polygon-specific tables if they exist
DROP TABLE IF EXISTS polygon_configurations CASCADE;
DROP TABLE IF EXISTS polygon_subscriptions CASCADE;
DROP TABLE IF EXISTS polygon_endpoints CASCADE;
DROP TABLE IF EXISTS polygon_api_keys CASCADE;

-- === REMOVE POLYGON-RELATED FUNCTIONS ===

-- Drop any Polygon-specific functions
DROP FUNCTION IF EXISTS get_polygon_config(VARCHAR, VARCHAR) CASCADE;
DROP FUNCTION IF EXISTS set_polygon_config(VARCHAR, JSONB, VARCHAR) CASCADE;
DROP FUNCTION IF EXISTS notify_polygon_changes() CASCADE;

-- === REMOVE POLYGON-RELATED TRIGGERS ===

-- Clean up any Polygon-related triggers that might exist
DROP TRIGGER IF EXISTS polygon_config_notify ON config_settings;
DROP TRIGGER IF EXISTS polygon_provider_notify ON provider_configurations;

-- === VERIFY PREREQUISITES ===

-- Ensure dual-provider tables exist (from migration 009)
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'provider_configurations') THEN
        RAISE EXCEPTION 'Migration 009_dual_provider_configuration.sql must be applied first. provider_configurations table missing.';
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'provider_subscriptions') THEN
        RAISE EXCEPTION 'Migration 009_dual_provider_configuration.sql must be applied first. provider_subscriptions table missing.';
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'provider_endpoints') THEN
        RAISE EXCEPTION 'Migration 009_dual_provider_configuration.sql must be applied first. provider_endpoints table missing.';
    END IF;
END $$;

-- === AUDIT LOG FOR REMOVAL ===

-- Note: config_history table requires a valid config_setting_id (NOT NULL constraint)
-- Migration events are tracked in the migration_notes category instead
-- See the INSERT into config_categories below for migration tracking

-- === UPDATE DOCUMENTATION ===

-- Add migration note to config_categories
INSERT INTO config_categories (
    category_name, 
    parent_id, 
    category_path, 
    description, 
    is_system, 
    display_order,
    metadata
) VALUES (
    'migration_notes',
    (SELECT id FROM config_categories WHERE category_path = 'system'),
    'system.migration_notes',
    'Migration history and notes',
    true,
    999,
    '{"migration_010": "Removed Polygon provider configurations for dual-provider setup"}'::jsonb
);

-- === CLEANUP ORPHANED DATA ===

-- Remove any orphaned config_history entries that reference non-existent settings
DELETE FROM config_history 
WHERE config_setting_id IS NOT NULL 
  AND config_setting_id NOT IN (SELECT id FROM config_settings);

-- Remove any orphaned config_environment_overrides
DELETE FROM config_environment_overrides 
WHERE config_setting_id NOT IN (SELECT id FROM config_settings);

-- Remove any orphaned config_locks
DELETE FROM config_locks 
WHERE config_key NOT IN (SELECT config_key FROM config_settings);

-- === UPDATE STATISTICS ===
ANALYZE config_categories;
ANALYZE config_settings;
ANALYZE config_history;
ANALYZE config_environment_overrides;
ANALYZE config_subscriptions;
ANALYZE config_locks;
ANALYZE provider_configurations;
ANALYZE provider_subscriptions;
ANALYZE provider_endpoints;

-- === VERIFY DATABENTO/BENZINGA CONFIGS EXIST ===

-- Verify that Databento and Benzinga configurations are properly set up
DO $$
DECLARE
    databento_count INTEGER;
    benzinga_count INTEGER;
BEGIN
    -- Check Databento configurations
    SELECT COUNT(*) INTO databento_count
    FROM config_settings 
    WHERE category_path LIKE 'trading.providers.databento%';

    IF databento_count = 0 THEN
        RAISE WARNING 'No Databento configurations found. Run migration 009 if not already applied.';
    ELSE
        RAISE NOTICE 'Found % Databento configuration(s)', databento_count;
    END IF;

    -- Check Benzinga configurations
    SELECT COUNT(*) INTO benzinga_count
    FROM config_settings 
    WHERE category_path LIKE 'trading.providers.benzinga%';

    IF benzinga_count = 0 THEN
        RAISE WARNING 'No Benzinga configurations found. Run migration 009 if not already applied.';
    ELSE
        RAISE NOTICE 'Found % Benzinga configuration(s)', benzinga_count;
    END IF;

    -- Verify no Polygon configs remain
    IF EXISTS (
        SELECT 1 FROM config_settings 
        WHERE config_key ILIKE '%polygon%' 
           OR description ILIKE '%polygon%'
           OR category_path ILIKE '%polygon%'
    ) THEN
        RAISE WARNING 'Some Polygon configurations may still exist in the system';
    ELSE
        RAISE NOTICE 'All Polygon configurations have been successfully removed';
    END IF;
END $$;

-- === MIGRATION COMPLETION SUMMARY ===

-- Polygon configuration cleanup completed successfully!
-- 
-- REMOVED:
-- - All Polygon-related configuration settings
-- - Polygon configuration categories  
-- - Polygon subscription patterns
-- - Polygon-specific database tables and functions
-- - Polygon-related triggers and notifications
-- - Orphaned configuration data
-- 
-- ADDED:
-- - Prerequisites verification (ensures migration 009 was applied)
-- - Audit log entry for the removal
-- - Migration notes category for documentation
-- - Data integrity cleanup
-- - Post-migration verification checks
-- 
-- RESULT:
-- The system is now ready for the dual-provider setup with Databento and Benzinga.
-- No Polygon-related configurations or references remain in the database.
-- All provider configurations should be managed through the new provider_* tables.
--
-- NEXT STEPS:
-- 1. Verify services can connect to Databento and Benzinga APIs
-- 2. Update API keys in provider_configurations table
-- 3. Test configuration hot-reload functionality
-- 4. Monitor 'foxhunt_provider_changes' PostgreSQL notification channel