#!/bin/bash

# PostgreSQL Enterprise Hardening Rollback Procedures
# Emergency rollback scripts for each hardening configuration
# App: rateright-db

set -e

# Color codes for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# Configuration
DB_APP="rateright-db"
MAIN_APP="rateright-au"

echo -e "${RED}═══════════════════════════════════════════════════════════════${NC}"
echo -e "${RED}    PostgreSQL Hardening Rollback Procedures${NC}"
echo -e "${RED}═══════════════════════════════════════════════════════════════${NC}"

# Function to confirm action
confirm_rollback() {
    echo -e "${YELLOW}⚠️  WARNING: This will rollback $1${NC}"
    read -p "Are you sure you want to proceed? (yes/no): " -r
    if [[ ! $REPLY =~ ^[Yy][Ee][Ss]$ ]]; then
        echo -e "${GREEN}Rollback cancelled${NC}"
        exit 0
    fi
}

# Function to check command success
check_success() {
    if [ $? -eq 0 ]; then
        echo -e "${GREEN}✅ $1${NC}"
    else
        echo -e "${RED}❌ $1 failed${NC}"
    fi
}

# ============================================
# ROLLBACK MENU
# ============================================

show_menu() {
    echo -e "\n${BLUE}Select rollback option:${NC}"
    echo "1. Rollback Security Configuration"
    echo "2. Rollback Performance Optimization"
    echo "3. Rollback Backup Configuration"
    echo "4. Rollback SSL/TLS Configuration"
    echo "5. Rollback Logging & Monitoring"
    echo "6. Rollback All Configurations (Complete Reset)"
    echo "7. Emergency Database Recovery"
    echo "0. Exit"
}

# ============================================
# 1. ROLLBACK SECURITY CONFIGURATION
# ============================================

rollback_security() {
    confirm_rollback "Security Configuration"
    
    echo -e "\n${YELLOW}Rolling back security configuration...${NC}"
    
    cat > rollback_security.sql << 'EOF'
-- Rollback Row Level Security
ALTER TABLE users DISABLE ROW LEVEL SECURITY;
ALTER TABLE contracts DISABLE ROW LEVEL SECURITY;
ALTER TABLE payments DISABLE ROW LEVEL SECURITY;
ALTER TABLE invoices DISABLE ROW LEVEL SECURITY;
ALTER TABLE audit_logs DISABLE ROW LEVEL SECURITY;

-- Drop security policies
DROP POLICY IF EXISTS users_isolation ON users;
DROP POLICY IF EXISTS contracts_isolation ON contracts;
DROP POLICY IF EXISTS payments_isolation ON payments;
DROP POLICY IF EXISTS audit_logs_admin_only ON audit_logs;

-- Drop compliance triggers
DROP TRIGGER IF EXISTS audit_user_compliance ON users;
DROP TRIGGER IF EXISTS audit_payment_compliance ON payments;

-- Drop audit function
DROP FUNCTION IF EXISTS audit_compliance_changes();

-- Restore public permissions (CAREFUL - only for development)
-- GRANT CREATE ON SCHEMA public TO PUBLIC;

-- Remove restricted users (optional - be careful)
-- DROP USER IF EXISTS rateright_app;
-- DROP USER IF EXISTS rateright_readonly;

-- Notify
SELECT 'Security configuration rolled back' as status;
EOF

    fly postgres connect -a $DB_APP < rollback_security.sql
    check_success "Security rollback completed"
}

# ============================================
# 2. ROLLBACK PERFORMANCE OPTIMIZATION
# ============================================

rollback_performance() {
    confirm_rollback "Performance Optimization"
    
    echo -e "\n${YELLOW}Rolling back performance optimization...${NC}"
    
    cat > rollback_performance.sql << 'EOF'
-- Reset memory settings to defaults
ALTER SYSTEM RESET shared_buffers;
ALTER SYSTEM RESET effective_cache_size;
ALTER SYSTEM RESET maintenance_work_mem;
ALTER SYSTEM RESET work_mem;
ALTER SYSTEM RESET wal_buffers;

-- Reset checkpoint settings
ALTER SYSTEM RESET checkpoint_completion_target;
ALTER SYSTEM RESET checkpoint_timeout;
ALTER SYSTEM RESET max_wal_size;
ALTER SYSTEM RESET min_wal_size;

-- Reset autovacuum settings
ALTER SYSTEM RESET autovacuum;
ALTER SYSTEM RESET autovacuum_max_workers;
ALTER SYSTEM RESET autovacuum_naptime;
ALTER SYSTEM RESET autovacuum_vacuum_scale_factor;
ALTER SYSTEM RESET autovacuum_analyze_scale_factor;

-- Reset query optimization settings
ALTER SYSTEM RESET random_page_cost;
ALTER SYSTEM RESET effective_io_concurrency;
ALTER SYSTEM RESET default_statistics_target;
ALTER SYSTEM RESET max_parallel_workers_per_gather;
ALTER SYSTEM RESET max_parallel_workers;

-- Reset logging for performance
ALTER SYSTEM RESET log_min_duration_statement;
ALTER SYSTEM RESET log_checkpoints;
ALTER SYSTEM RESET log_lock_waits;
ALTER SYSTEM RESET log_temp_files;
ALTER SYSTEM RESET log_autovacuum_min_duration;

-- Drop monitoring views
DROP VIEW IF EXISTS slow_queries;
DROP VIEW IF EXISTS index_usage;
DROP VIEW IF EXISTS table_bloat;
DROP VIEW IF EXISTS cache_hit_ratio;

-- Drop indexes (CAREFUL - only non-critical ones)
-- List indexes first to review
SELECT 
    schemaname,
    tablename,
    indexname
FROM pg_indexes
WHERE schemaname = 'public'
    AND indexname LIKE 'idx_%'
ORDER BY tablename, indexname;

-- Reload configuration
SELECT pg_reload_conf();

SELECT 'Performance optimization rolled back' as status;
EOF

    fly postgres connect -a $DB_APP < rollback_performance.sql
    check_success "Performance rollback completed"
}

# ============================================
# 3. ROLLBACK BACKUP CONFIGURATION
# ============================================

rollback_backup() {
    confirm_rollback "Backup Configuration"
    
    echo -e "\n${YELLOW}Rolling back backup configuration...${NC}"
    
    cat > rollback_backup.sql << 'EOF'
-- Reset archive settings
ALTER SYSTEM RESET archive_mode;
ALTER SYSTEM RESET archive_command;
ALTER SYSTEM RESET archive_timeout;
ALTER SYSTEM RESET wal_level;
ALTER SYSTEM RESET max_wal_senders;
ALTER SYSTEM RESET wal_keep_size;
ALTER SYSTEM RESET max_replication_slots;
ALTER SYSTEM RESET wal_compression;

-- Drop backup monitoring views and functions
DROP VIEW IF EXISTS backup_health;
DROP FUNCTION IF EXISTS check_backup_health();

-- Clean up audit logs related to backups
DELETE FROM audit_logs 
WHERE action IN ('BACKUP_CREATED', 'BACKUP_TEST_SUCCESS')
    AND created_at > NOW() - INTERVAL '30 days';

-- Reload configuration
SELECT pg_reload_conf();

SELECT 'Backup configuration rolled back' as status;
EOF

    fly postgres connect -a $DB_APP < rollback_backup.sql
    
    # Remove backup scripts
    if [ -f "automated_backup.sh" ]; then
        rm automated_backup.sh
        echo -e "${GREEN}Removed automated_backup.sh${NC}"
    fi
    
    if [ -f "test_backup_restore.sh" ]; then
        rm test_backup_restore.sh
        echo -e "${GREEN}Removed test_backup_restore.sh${NC}"
    fi
    
    check_success "Backup rollback completed"
}

# ============================================
# 4. ROLLBACK SSL/TLS CONFIGURATION
# ============================================

rollback_ssl() {
    confirm_rollback "SSL/TLS Configuration"
    
    echo -e "\n${YELLOW}Rolling back SSL/TLS configuration...${NC}"
    
    cat > rollback_ssl.sql << 'EOF'
-- Reset SSL settings (CAREFUL - reduces security)
ALTER SYSTEM RESET ssl;
ALTER SYSTEM RESET ssl_min_protocol_version;
ALTER SYSTEM RESET ssl_max_protocol_version;
ALTER SYSTEM RESET ssl_ciphers;

-- Drop SSL monitoring views and functions
DROP VIEW IF EXISTS ssl_connection_monitor;
DROP FUNCTION IF EXISTS check_ssl_compliance();
DROP FUNCTION IF EXISTS alert_non_ssl_connections();

-- Reload configuration
SELECT pg_reload_conf();

SELECT 'SSL/TLS configuration rolled back' as status;
EOF

    fly postgres connect -a $DB_APP < rollback_ssl.sql
    
    # Update DATABASE_URL to remove SSL requirement (CAREFUL)
    echo -e "${YELLOW}Note: You may need to manually update DATABASE_URL to remove sslmode parameter${NC}"
    
    check_success "SSL/TLS rollback completed"
}

# ============================================
# 5. ROLLBACK LOGGING & MONITORING
# ============================================

rollback_logging() {
    confirm_rollback "Logging & Monitoring Configuration"
    
    echo -e "\n${YELLOW}Rolling back logging & monitoring...${NC}"
    
    cat > rollback_logging.sql << 'EOF'
-- Reset logging configuration
ALTER SYSTEM RESET logging_collector;
ALTER SYSTEM RESET log_directory;
ALTER SYSTEM RESET log_filename;
ALTER SYSTEM RESET log_rotation_age;
ALTER SYSTEM RESET log_rotation_size;
ALTER SYSTEM RESET log_line_prefix;
ALTER SYSTEM RESET log_connections;
ALTER SYSTEM RESET log_disconnections;
ALTER SYSTEM RESET log_statement;
ALTER SYSTEM RESET log_min_messages;
ALTER SYSTEM RESET log_min_error_statement;

-- Drop monitoring views
DROP VIEW IF EXISTS database_activity_monitor;
DROP VIEW IF EXISTS table_activity_monitor;
DROP VIEW IF EXISTS lock_monitor;

-- Drop monitoring functions
DROP FUNCTION IF EXISTS check_critical_conditions();
DROP FUNCTION IF EXISTS get_monitoring_dashboard();
DROP FUNCTION IF EXISTS capture_query_statistics();
DROP FUNCTION IF EXISTS log_compliance_event();

-- Drop compliance triggers
DO $$
DECLARE
    tbl RECORD;
BEGIN
    FOR tbl IN 
        SELECT tablename 
        FROM pg_tables 
        WHERE schemaname = 'public' 
            AND tablename IN ('users', 'contracts', 'payments', 'invoices')
    LOOP
        EXECUTE FORMAT('DROP TRIGGER IF EXISTS compliance_audit_%s ON %I;', 
            tbl.tablename, tbl.tablename);
    END LOOP;
END $$;

-- Drop monitoring tables (CAREFUL - contains audit data)
-- DROP TABLE IF EXISTS audit_logs_enhanced;
-- DROP TABLE IF EXISTS query_performance_history;

-- Reload configuration
SELECT pg_reload_conf();

SELECT 'Logging & monitoring rolled back' as status;
EOF

    fly postgres connect -a $DB_APP < rollback_logging.sql
    check_success "Logging rollback completed"
}

# ============================================
# 6. COMPLETE ROLLBACK
# ============================================

rollback_all() {
    confirm_rollback "ALL CONFIGURATIONS - This will reset to pre-hardening state"
    
    echo -e "\n${RED}Performing complete rollback...${NC}"
    
    # Execute all individual rollbacks
    rollback_security
    rollback_performance
    rollback_backup
    rollback_ssl
    rollback_logging
    
    # Additional cleanup
    cat > complete_rollback.sql << 'EOF'
-- Final cleanup
SELECT pg_reload_conf();

-- List remaining custom objects for review
SELECT 'Custom Functions:' as object_type, count(*) as count
FROM pg_proc
WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
UNION ALL
SELECT 'Custom Views:', count(*)
FROM pg_views
WHERE schemaname = 'public'
UNION ALL
SELECT 'Custom Triggers:', count(*)
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'public' AND NOT tgisinternal;

SELECT 'Complete rollback finished' as status;
EOF

    fly postgres connect -a $DB_APP < complete_rollback.sql
    check_success "Complete rollback finished"
}

# ============================================
# 7. EMERGENCY RECOVERY
# ============================================

emergency_recovery() {
    echo -e "\n${RED}EMERGENCY DATABASE RECOVERY${NC}"
    echo -e "${YELLOW}This procedure should only be used when the database is unresponsive${NC}"
    
    confirm_rollback "EMERGENCY RECOVERY - This may cause data loss"
    
    echo -e "\n${YELLOW}Starting emergency recovery...${NC}"
    
    # Step 1: Force restart
    echo "Step 1: Force restarting database..."
    fly postgres restart --app $DB_APP --force
    sleep 10
    
    # Step 2: Kill long-running queries
    cat > emergency_kill_queries.sql << 'EOF'
-- Terminate all connections except current
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
    AND state = 'active'
    AND query_start < NOW() - INTERVAL '1 minute';

-- Cancel all active queries
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
    AND state = 'active';
EOF

    fly postgres connect -a $DB_APP < emergency_kill_queries.sql
    
    # Step 3: Reset critical parameters
    cat > emergency_reset.sql << 'EOF'
-- Reset critical parameters
ALTER SYSTEM RESET ALL;

-- Force checkpoint
CHECKPOINT;

-- Analyze all tables
ANALYZE;

-- Reindex system catalogs
REINDEX SYSTEM CONCURRENTLY;

SELECT 'Emergency recovery completed' as status;
EOF

    fly postgres connect -a $DB_APP < emergency_reset.sql
    
    # Step 4: Restore from backup if needed
    echo -e "${YELLOW}If data corruption is detected, restore from backup:${NC}"
    echo "fly postgres backup list --app $DB_APP"
    echo "fly postgres backup restore --app $DB_APP --backup-id <backup-id>"
    
    check_success "Emergency recovery completed"
}

# ============================================
# MAIN SCRIPT LOGIC
# ============================================

# Create backup before any rollback
create_config_backup() {
    echo -e "\n${BLUE}Creating configuration backup...${NC}"
    
    BACKUP_DIR="rollback_backups/$(date +%Y%m%d_%H%M%S)"
    mkdir -p $BACKUP_DIR
    
    fly postgres connect -a $DB_APP -c "
        SELECT name, setting 
        FROM pg_settings 
        WHERE source != 'default'
        ORDER BY name
    " > $BACKUP_DIR/current_settings.txt
    
    echo -e "${GREEN}Configuration backed up to $BACKUP_DIR${NC}"
}

# Main menu loop
while true; do
    show_menu
    read -p "Enter your choice (0-7): " choice
    
    case $choice in
        1)
            create_config_backup
            rollback_security
            ;;
        2)
            create_config_backup
            rollback_performance
            ;;
        3)
            create_config_backup
            rollback_backup
            ;;
        4)
            create_config_backup
            rollback_ssl
            ;;
        5)
            create_config_backup
            rollback_logging
            ;;
        6)
            create_config_backup
            rollback_all
            ;;
        7)
            emergency_recovery
            ;;
        0)
            echo -e "${GREEN}Exiting rollback procedures${NC}"
            exit 0
            ;;
        *)
            echo -e "${RED}Invalid option. Please select 0-7${NC}"
            ;;
    esac
    
    echo -e "\n${YELLOW}Press Enter to continue...${NC}"
    read
done