#!/bin/bash

# PostgreSQL Enterprise Validation & Testing Suite
# Comprehensive validation of all hardening configurations
# 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'
CYAN='\033[0;36m'
NC='\033[0m' # No Color

# Configuration
DB_APP="rateright-db"
MAIN_APP="rateright-au"
VALIDATION_REPORT="enterprise_validation_report_$(date +%Y%m%d_%H%M%S).txt"

# Test counters
TESTS_PASSED=0
TESTS_FAILED=0
TESTS_WARNING=0

echo -e "${BLUE}═══════════════════════════════════════════════════════════════${NC}"
echo -e "${BLUE}    PostgreSQL Enterprise Validation Suite${NC}"
echo -e "${BLUE}═══════════════════════════════════════════════════════════════${NC}"

# Function to log results
log_result() {
    echo "$1" >> "$VALIDATION_REPORT"
    echo -e "$1"
}

# Function to run test
run_test() {
    local test_name="$1"
    local test_command="$2"
    local expected_result="$3"
    
    echo -e "\n${CYAN}Testing: $test_name${NC}"
    
    if eval "$test_command"; then
        TESTS_PASSED=$((TESTS_PASSED + 1))
        log_result "${GREEN}✅ PASSED: $test_name${NC}"
        return 0
    else
        TESTS_FAILED=$((TESTS_FAILED + 1))
        log_result "${RED}❌ FAILED: $test_name${NC}"
        return 1
    fi
}

# Function to run warning test
run_warning_test() {
    local test_name="$1"
    local test_command="$2"
    
    echo -e "\n${CYAN}Testing: $test_name${NC}"
    
    if eval "$test_command"; then
        TESTS_PASSED=$((TESTS_PASSED + 1))
        log_result "${GREEN}✅ PASSED: $test_name${NC}"
        return 0
    else
        TESTS_WARNING=$((TESTS_WARNING + 1))
        log_result "${YELLOW}⚠️  WARNING: $test_name${NC}"
        return 1
    fi
}

# ============================================
# VALIDATION TEST SUITE
# ============================================

log_result "Enterprise Validation Report - $(date)"
log_result "Database: $DB_APP"
log_result "Application: $MAIN_APP"
log_result "============================================"

# ============================================
# 1. SECURITY VALIDATION
# ============================================

log_result "\n${BLUE}[1/10] Security Configuration Validation${NC}"
log_result "----------------------------------------"

# Test 1.1: Check for restricted users
cat > test_security_users.sql << 'EOF'
SELECT COUNT(*) > 0 as has_app_user
FROM pg_user
WHERE usename IN ('rateright_app', 'rateright_readonly');
EOF

run_test "Application-specific users exist" \
    "fly postgres connect -a $DB_APP < test_security_users.sql | grep -q 't'"

# Test 1.2: Check RLS is enabled
cat > test_rls.sql << 'EOF'
SELECT COUNT(*) as rls_enabled_count
FROM pg_tables
WHERE schemaname = 'public'
    AND tablename IN ('users', 'contracts', 'payments', 'invoices')
    AND rowsecurity = true;
EOF

run_test "Row Level Security enabled on sensitive tables" \
    "fly postgres connect -a $DB_APP < test_rls.sql | grep -q '4'"

# Test 1.3: Check for dangerous permissions
cat > test_dangerous_perms.sql << 'EOF'
SELECT COUNT(*) = 0 as no_public_create
FROM information_schema.schema_privileges
WHERE grantee = 'PUBLIC'
    AND privilege_type = 'CREATE'
    AND schema_name = 'public';
EOF

run_test "Public CREATE permission revoked" \
    "fly postgres connect -a $DB_APP < test_dangerous_perms.sql | grep -q 't'"

# ============================================
# 2. PERFORMANCE VALIDATION
# ============================================

log_result "\n${BLUE}[2/10] Performance Configuration Validation${NC}"
log_result "--------------------------------------------"

# Test 2.1: Check shared buffers
cat > test_performance.sql << 'EOF'
SELECT 
    CASE 
        WHEN setting::int >= 256*1024*1024/8192 THEN true
        ELSE false
    END as adequate_shared_buffers
FROM pg_settings
WHERE name = 'shared_buffers';
EOF

run_test "Shared buffers >= 256MB" \
    "fly postgres connect -a $DB_APP < test_performance.sql | grep -q 't'"

# Test 2.2: Check for indexes
cat > test_indexes.sql << 'EOF'
SELECT COUNT(*) > 10 as has_indexes
FROM pg_indexes
WHERE schemaname = 'public'
    AND indexname LIKE 'idx_%';
EOF

run_warning_test "Performance indexes created" \
    "fly postgres connect -a $DB_APP < test_indexes.sql | grep -q 't'"

# Test 2.3: Check pg_stat_statements
cat > test_monitoring_ext.sql << 'EOF'
SELECT COUNT(*) > 0 as has_monitoring
FROM pg_extension
WHERE extname = 'pg_stat_statements';
EOF

run_test "Query monitoring extension enabled" \
    "fly postgres connect -a $DB_APP < test_monitoring_ext.sql | grep -q 't'"

# ============================================
# 3. BACKUP VALIDATION
# ============================================

log_result "\n${BLUE}[3/10] Backup Configuration Validation${NC}"
log_result "----------------------------------------"

# Test 3.1: Check WAL archiving
cat > test_wal_archiving.sql << 'EOF'
SELECT 
    CASE 
        WHEN current_setting('wal_level') IN ('replica', 'logical') THEN true
        ELSE false
    END as wal_configured;
EOF

run_test "WAL level configured for backups" \
    "fly postgres connect -a $DB_APP < test_wal_archiving.sql | grep -q 't'"

# Test 3.2: List recent backups
echo "Checking for recent backups..."
if fly postgres backup list --app $DB_APP 2>/dev/null | grep -q "backup"; then
    log_result "${GREEN}✅ Backups found${NC}"
    TESTS_PASSED=$((TESTS_PASSED + 1))
else
    log_result "${YELLOW}⚠️  No recent backups found${NC}"
    TESTS_WARNING=$((TESTS_WARNING + 1))
fi

# ============================================
# 4. SSL/TLS VALIDATION
# ============================================

log_result "\n${BLUE}[4/10] SSL/TLS Configuration Validation${NC}"
log_result "-----------------------------------------"

# Test 4.1: Check SSL is enabled
cat > test_ssl.sql << 'EOF'
SELECT current_setting('ssl') = 'on' as ssl_enabled;
EOF

run_test "SSL enabled" \
    "fly postgres connect -a $DB_APP < test_ssl.sql | grep -q 't'"

# Test 4.2: Check minimum TLS version
cat > test_tls_version.sql << 'EOF'
SELECT 
    CASE 
        WHEN current_setting('ssl_min_protocol_version') >= 'TLSv1.2' THEN true
        ELSE false
    END as secure_tls;
EOF

run_test "Minimum TLS 1.2 enforced" \
    "fly postgres connect -a $DB_APP < test_tls_version.sql | grep -q 't'"

# Test 4.3: Check for non-SSL connections
cat > test_ssl_connections.sql << 'EOF'
SELECT COUNT(*) = 0 as all_ssl
FROM pg_stat_activity a
LEFT JOIN pg_stat_ssl s USING (pid)
WHERE a.pid != pg_backend_pid()
    AND (s.ssl IS NULL OR s.ssl = false);
EOF

run_warning_test "All connections using SSL" \
    "fly postgres connect -a $DB_APP < test_ssl_connections.sql | grep -q 't'"

# ============================================
# 5. LOGGING VALIDATION
# ============================================

log_result "\n${BLUE}[5/10] Logging Configuration Validation${NC}"
log_result "-----------------------------------------"

# Test 5.1: Check connection logging
cat > test_logging.sql << 'EOF'
SELECT 
    current_setting('log_connections') = 'on' AND
    current_setting('log_disconnections') = 'on' as connection_logging;
EOF

run_test "Connection logging enabled" \
    "fly postgres connect -a $DB_APP < test_logging.sql | grep -q 't'"

# Test 5.2: Check slow query logging
cat > test_slow_query_log.sql << 'EOF'
SELECT 
    CASE 
        WHEN current_setting('log_min_duration_statement')::int <= 1000 THEN true
        ELSE false
    END as slow_query_logging;
EOF

run_test "Slow query logging configured" \
    "fly postgres connect -a $DB_APP < test_slow_query_log.sql | grep -q 't'"

# ============================================
# 6. CONNECTION POOL VALIDATION
# ============================================

log_result "\n${BLUE}[6/10] Connection Pool Validation${NC}"
log_result "----------------------------------"

# Test 6.1: Check max connections
cat > test_connections.sql << 'EOF'
SELECT 
    setting::int >= 100 as adequate_connections
FROM pg_settings
WHERE name = 'max_connections';
EOF

run_test "Adequate max connections (>=100)" \
    "fly postgres connect -a $DB_APP < test_connections.sql | grep -q 't'"

# Test 6.2: Check current connection usage
cat > test_connection_usage.sql << 'EOF'
WITH conn_stats AS (
    SELECT 
        COUNT(*) as current_connections,
        current_setting('max_connections')::int as max_connections
    FROM pg_stat_activity
)
SELECT 
    current_connections,
    max_connections,
    ROUND(100.0 * current_connections / max_connections, 2) as usage_percent,
    CASE 
        WHEN (100.0 * current_connections / max_connections) < 80 THEN 'OK'
        WHEN (100.0 * current_connections / max_connections) < 95 THEN 'WARNING'
        ELSE 'CRITICAL'
    END as status
FROM conn_stats;
EOF

fly postgres connect -a $DB_APP < test_connection_usage.sql > connection_usage.txt
log_result "${CYAN}Connection pool usage:${NC}"
cat connection_usage.txt | tail -n 3

# ============================================
# 7. COMPLIANCE VALIDATION
# ============================================

log_result "\n${BLUE}[7/10] Australian Compliance Validation${NC}"
log_result "----------------------------------------"

# Test 7.1: Check for audit logs table
cat > test_audit_logs.sql << 'EOF'
SELECT COUNT(*) > 0 as has_audit_logs
FROM information_schema.tables
WHERE table_schema = 'public'
    AND table_name IN ('audit_logs', 'audit_logs_enhanced');
EOF

run_test "Audit logging table exists" \
    "fly postgres connect -a $DB_APP < test_audit_logs.sql | grep -q 't'"

# Test 7.2: Check for compliance triggers
cat > test_compliance_triggers.sql << 'EOF'
SELECT COUNT(*) > 0 as has_triggers
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 t.tgname LIKE 'compliance_audit_%';
EOF

run_warning_test "Compliance triggers configured" \
    "fly postgres connect -a $DB_APP < test_compliance_triggers.sql | grep -q 't'"

# ============================================
# 8. HEALTH CHECK VALIDATION
# ============================================

log_result "\n${BLUE}[8/10] Health Monitoring Validation${NC}"
log_result "------------------------------------"

# Test 8.1: Run health check
if [ -f "database_hardening/04_health_monitoring.py" ]; then
    echo "Running health monitoring script..."
    if python database_hardening/04_health_monitoring.py > health_check_output.txt 2>&1; then
        log_result "${GREEN}✅ Health monitoring script executed successfully${NC}"
        TESTS_PASSED=$((TESTS_PASSED + 1))
    else
        log_result "${YELLOW}⚠️  Health monitoring script failed (may need DATABASE_URL)${NC}"
        TESTS_WARNING=$((TESTS_WARNING + 1))
    fi
else
    log_result "${YELLOW}⚠️  Health monitoring script not found${NC}"
    TESTS_WARNING=$((TESTS_WARNING + 1))
fi

# ============================================
# 9. DISASTER RECOVERY VALIDATION
# ============================================

log_result "\n${BLUE}[9/10] Disaster Recovery Validation${NC}"
log_result "------------------------------------"

# Test 9.1: Check for recovery procedures
if [ -f "disaster_recovery_procedures.md" ]; then
    log_result "${GREEN}✅ Disaster recovery procedures documented${NC}"
    TESTS_PASSED=$((TESTS_PASSED + 1))
else
    log_result "${YELLOW}⚠️  Disaster recovery procedures not found${NC}"
    TESTS_WARNING=$((TESTS_WARNING + 1))
fi

# Test 9.2: Check rollback procedures
if [ -f "database_hardening/07_rollback_procedures.sh" ]; then
    log_result "${GREEN}✅ Rollback procedures available${NC}"
    TESTS_PASSED=$((TESTS_PASSED + 1))
else
    log_result "${RED}❌ Rollback procedures not found${NC}"
    TESTS_FAILED=$((TESTS_FAILED + 1))
fi

# ============================================
# 10. COMPREHENSIVE STATUS CHECK
# ============================================

log_result "\n${BLUE}[10/10] Comprehensive Status Check${NC}"
log_result "------------------------------------"

# Create comprehensive status query
cat > comprehensive_check.sql << 'EOF'
WITH security_check AS (
    SELECT 
        (SELECT COUNT(*) FROM pg_tables WHERE rowsecurity = true) as rls_tables,
        (SELECT current_setting('ssl')) as ssl_status
),
performance_check AS (
    SELECT 
        (SELECT setting FROM pg_settings WHERE name = 'shared_buffers') as shared_buffers,
        (SELECT COUNT(*) FROM pg_indexes WHERE schemaname = 'public') as index_count
),
backup_check AS (
    SELECT 
        current_setting('wal_level') as wal_level,
        current_setting('archive_mode') as archive_mode
),
monitoring_check AS (
    SELECT 
        current_setting('log_connections') as log_connections,
        current_setting('log_min_duration_statement') as slow_query_threshold
)
SELECT 
    jsonb_pretty(jsonb_build_object(
        'security', jsonb_build_object(
            'rls_enabled_tables', s.rls_tables,
            'ssl_enabled', s.ssl_status
        ),
        'performance', jsonb_build_object(
            'shared_buffers', p.shared_buffers,
            'indexes', p.index_count
        ),
        'backup', jsonb_build_object(
            'wal_level', b.wal_level,
            'archive_mode', b.archive_mode
        ),
        'monitoring', jsonb_build_object(
            'connection_logging', m.log_connections,
            'slow_query_ms', m.slow_query_threshold
        )
    )) as status
FROM security_check s, performance_check p, backup_check b, monitoring_check m;
EOF

echo "Running comprehensive status check..."
fly postgres connect -a $DB_APP < comprehensive_check.sql > comprehensive_status.json 2>/dev/null

if [ -s comprehensive_status.json ]; then
    log_result "${GREEN}✅ Comprehensive status check completed${NC}"
    log_result "${CYAN}Status summary saved to comprehensive_status.json${NC}"
    TESTS_PASSED=$((TESTS_PASSED + 1))
else
    log_result "${RED}❌ Comprehensive status check failed${NC}"
    TESTS_FAILED=$((TESTS_FAILED + 1))
fi

# ============================================
# VALIDATION SUMMARY
# ============================================

log_result "\n${BLUE}═══════════════════════════════════════════════════════════════${NC}"
log_result "${BLUE}    Validation Summary${NC}"
log_result "${BLUE}═══════════════════════════════════════════════════════════════${NC}"

TOTAL_TESTS=$((TESTS_PASSED + TESTS_FAILED + TESTS_WARNING))
PASS_RATE=$((TESTS_PASSED * 100 / TOTAL_TESTS))

log_result "Total Tests: $TOTAL_TESTS"
log_result "${GREEN}Passed: $TESTS_PASSED${NC}"
log_result "${YELLOW}Warnings: $TESTS_WARNING${NC}"
log_result "${RED}Failed: $TESTS_FAILED${NC}"
log_result "Pass Rate: $PASS_RATE%"

# Determine overall status
if [ $TESTS_FAILED -eq 0 ] && [ $TESTS_WARNING -le 3 ]; then
    log_result "\n${GREEN}✅ ENTERPRISE VALIDATION SUCCESSFUL${NC}"
    log_result "Your PostgreSQL database meets enterprise-grade standards!"
    EXIT_CODE=0
elif [ $TESTS_FAILED -eq 0 ]; then
    log_result "\n${YELLOW}⚠️  VALIDATION PASSED WITH WARNINGS${NC}"
    log_result "Review warnings to achieve full enterprise compliance"
    EXIT_CODE=1
else
    log_result "\n${RED}❌ VALIDATION FAILED${NC}"
    log_result "Critical issues must be resolved for enterprise deployment"
    EXIT_CODE=2
fi

# ============================================
# RECOMMENDATIONS
# ============================================

log_result "\n${BLUE}Recommendations:${NC}"
log_result "----------------"

if [ $TESTS_WARNING -gt 0 ] || [ $TESTS_FAILED -gt 0 ]; then
    log_result "1. Review failed tests and warnings in: $VALIDATION_REPORT"
    log_result "2. Run individual hardening scripts to fix issues"
    log_result "3. Re-run validation after fixes"
fi

if [ ! -f "health_check_output.txt" ]; then
    log_result "4. Set DATABASE_URL and run health monitoring regularly"
fi

log_result "5. Schedule regular validation checks (weekly recommended)"
log_result "6. Keep documentation updated with any configuration changes"

# ============================================
# ARTIFACTS
# ============================================

log_result "\n${BLUE}Validation Artifacts Created:${NC}"
log_result "-----------------------------"
log_result "• Full Report: $VALIDATION_REPORT"
log_result "• Connection Usage: connection_usage.txt"
log_result "• Comprehensive Status: comprehensive_status.json"

if [ -f "health_check_output.txt" ]; then
    log_result "• Health Check: health_check_output.txt"
fi

log_result "\n${GREEN}Validation completed at $(date)${NC}"

exit $EXIT_CODE