<?php

declare(strict_types=1);

namespace App\Framework\Performance\Examples;

use App\Framework\Performance\PerformanceMeter;
use App\Framework\Performance\PerformanceCategory;

/**
 * Beispiel für Database-Performance-Tracking
 *
 * Zeigt, wie Database-Operationen mit dem PerformanceMeter
 * gemessen und analysiert werden können.
 */
class DatabasePerformanceExample
{
    private PerformanceMeter $meter;
    private array $queryLog = [];
    private array $config;

    public function __construct(PerformanceMeter $meter, array $config = [])
    {
        $this->meter = $meter;
        $this->config = array_merge([
            'log_all_queries' => false,
            'log_slow_queries_only' => true,
            'slow_query_threshold_ms' => 100,
            'track_query_patterns' => true,
            'track_connection_pool' => true,
        ], $config);
    }

    /**
     * Query-Ausführung mit Performance-Tracking
     */
    public function executeQuery(string $sql, array $params = []): array
    {
        $queryId = uniqid('query_');
        $queryType = $this->getQueryType($sql);
        $measurementId = "db_query_{$queryType}_{$queryId}";

        // Query-Start markieren
        $this->meter->mark("query_start_{$queryType}", PerformanceCategory::DATABASE);

        // Query-Kontext sammeln
        $queryContext = [
            'sql' => $sql,
            'params' => $params,
            'type' => $queryType,
            'start_time' => microtime(true),
        ];

        $this->queryLog[$queryId] = $queryContext;

        // Hauptmessung starten
        $this->meter->startMeasure($measurementId, PerformanceCategory::DATABASE);

        try {
            // Query-Preparation messen
            $statement = $this->meter->measure('query_preparation', function() use ($sql) {
                return $this->prepareStatement($sql);
            }, PerformanceCategory::DATABASE);

            // Parameter-Binding messen
            if (!empty($params)) {
                $this->meter->measure('parameter_binding', function() use ($statement, $params) {
                    $this->bindParameters($statement, $params);
                }, PerformanceCategory::DATABASE);
            }

            // Query-Execution messen
            $result = $this->meter->measure('query_execution', function() use ($statement) {
                return $this->executeStatement($statement);
            }, PerformanceCategory::DATABASE);

            // Result-Fetching messen (bei SELECT)
            if ($queryType === 'SELECT') {
                $data = $this->meter->measure('result_fetching', function() use ($result) {
                    return $this->fetchResults($result);
                }, PerformanceCategory::DATABASE);
            } else {
                $data = $result;
            }

            // Query-Ende markieren
            $this->meter->mark("query_end_{$queryType}", PerformanceCategory::DATABASE);

            // Performance-Analyse
            $this->analyzeQueryPerformance($queryId, $queryContext);

            return $data;

        } catch (\Exception $e) {
            $this->meter->mark("query_error_{$queryType}", PerformanceCategory::DATABASE);
            throw $e;
        } finally {
            $this->meter->endMeasure($measurementId);
            unset($this->queryLog[$queryId]);
        }
    }

    /**
     * Transaction-Performance-Tracking
     */
    public function executeTransaction(callable $operations): mixed
    {
        $transactionId = uniqid('trans_');
        $measurementId = "db_transaction_{$transactionId}";

        $this->meter->mark('transaction_begin', PerformanceCategory::DATABASE);
        $this->meter->startMeasure($measurementId, PerformanceCategory::DATABASE);

        // Transaction-Start messen
        $this->meter->measure('transaction_start', function() {
            $this->beginTransaction();
        }, PerformanceCategory::DATABASE);

        try {
            // Transaction-Operationen ausführen
            $result = $this->meter->measure('transaction_operations', function() use ($operations) {
                return $operations();
            }, PerformanceCategory::DATABASE);

            // Commit messen
            $this->meter->measure('transaction_commit', function() {
                $this->commitTransaction();
            }, PerformanceCategory::DATABASE);

            $this->meter->mark('transaction_committed', PerformanceCategory::DATABASE);

            return $result;

        } catch (\Exception $e) {
            // Rollback messen
            $this->meter->measure('transaction_rollback', function() {
                $this->rollbackTransaction();
            }, PerformanceCategory::DATABASE);

            $this->meter->mark('transaction_rolled_back', PerformanceCategory::DATABASE);
            throw $e;

        } finally {
            $this->meter->endMeasure($measurementId);
        }
    }

    /**
     * Bulk-Insert mit Performance-Tracking
     */
    public function bulkInsert(string $table, array $data): bool
    {
        $recordCount = count($data);
        $measurementId = "bulk_insert_{$table}_{$recordCount}";

        $this->meter->mark("bulk_insert_start_{$table}", PerformanceCategory::DATABASE);
        $this->meter->startMeasure($measurementId, PerformanceCategory::DATABASE);

        try {
            // Data-Preparation
            $preparedData = $this->meter->measure('bulk_data_preparation', function() use ($data) {
                return $this->prepareBulkData($data);
            }, PerformanceCategory::DATABASE);

            // SQL-Generation
            $sql = $this->meter->measure('bulk_sql_generation', function() use ($table, $preparedData) {
                return $this->generateBulkInsertSql($table, $preparedData);
            }, PerformanceCategory::DATABASE);

            // Bulk-Execution
            $result = $this->meter->measure('bulk_execution', function() use ($sql, $preparedData) {
                return $this->executeBulkInsert($sql, $preparedData);
            }, PerformanceCategory::DATABASE);

            $this->meter->mark("bulk_insert_completed_{$table}", PerformanceCategory::DATABASE);

            // Performance-Statistiken
            $this->logBulkInsertStats($table, $recordCount);

            return $result;

        } finally {
            $this->meter->endMeasure($measurementId);
        }
    }

    /**
     * Connection-Pool Performance-Tracking
     */
    public function withConnection(callable $operation): mixed
    {
        if (!$this->config['track_connection_pool']) {
            return $operation();
        }

        $connectionId = uniqid('conn_');

        // Connection-Acquisition messen
        $connection = $this->meter->measure('connection_acquisition', function() {
            return $this->acquireConnection();
        }, PerformanceCategory::DATABASE);

        $this->meter->mark('connection_acquired', PerformanceCategory::DATABASE);

        try {
            // Operation ausführen
            return $this->meter->measure("connection_operation_{$connectionId}", function() use ($operation, $connection) {
                return $operation($connection);
            }, PerformanceCategory::DATABASE);

        } finally {
            // Connection-Release messen
            $this->meter->measure('connection_release', function() use ($connection) {
                $this->releaseConnection($connection);
            }, PerformanceCategory::DATABASE);

            $this->meter->mark('connection_released', PerformanceCategory::DATABASE);
        }
    }

    /**
     * Query-Performance analysieren
     */
    private function analyzeQueryPerformance(string $queryId, array $context): void
    {
        $duration = (microtime(true) - $context['start_time']) * 1000;
        $queryType = $context['type'];

        // Slow-Query Detection
        if ($duration > $this->config['slow_query_threshold_ms']) {
            $this->meter->mark("slow_query_{$queryType}", PerformanceCategory::DATABASE);
            $this->handleSlowQuery($context, $duration);
        }

        // Query-Pattern-Tracking
        if ($this->config['track_query_patterns']) {
            $this->trackQueryPattern($context, $duration);
        }

        // Logging-Entscheidung
        $shouldLog = $this->config['log_all_queries'] ||
                    ($this->config['log_slow_queries_only'] && $duration > $this->config['slow_query_threshold_ms']);

        if ($shouldLog) {
            $this->logQueryPerformance($context, $duration);
        }
    }

    /**
     * Langsame Queries behandeln
     */
    private function handleSlowQuery(array $context, float $duration): void
    {
        error_log("SLOW QUERY DETECTED: {$context['type']} took {$duration}ms");
        error_log("SQL: " . $this->sanitizeSqlForLogging($context['sql']));

        // Detailed Performance-Report für slow queries
        $report = $this->meter->generateReport();
        $dbMeasurements = array_filter($report['measurements'], function($m) {
            return $m['category'] === 'database';
        });

        if (!empty($dbMeasurements)) {
            error_log("DB Performance breakdown: " . json_encode($dbMeasurements));
        }
    }

    /**
     * Query-Pattern-Tracking
     */
    private function trackQueryPattern(array $context, float $duration): void
    {
        $pattern = $this->extractQueryPattern($context['sql']);
        $patternMeasurement = "query_pattern_{$pattern}";

        // Pattern-spezifische Statistiken in separater Messung
        static $patternMeasurements = [];

        if (!isset($patternMeasurements[$pattern])) {
            $patternMeasurements[$pattern] = [
                'count' => 0,
                'total_time' => 0,
                'measurement_id' => $patternMeasurement,
            ];
        }

        $patternMeasurements[$pattern]['count']++;
        $patternMeasurements[$pattern]['total_time'] += $duration;

        // Bei häufigen Patterns: Marker setzen
        if ($patternMeasurements[$pattern]['count'] % 10 === 0) {
            $avgTime = $patternMeasurements[$pattern]['total_time'] / $patternMeasurements[$pattern]['count'];
            $this->meter->mark("frequent_pattern_{$pattern}_avg_{$avgTime}ms", PerformanceCategory::DATABASE);
        }
    }

    /**
     * Bulk-Insert-Statistiken loggen
     */
    private function logBulkInsertStats(string $table, int $recordCount): void
    {
        $report = $this->meter->generateReport();

        // Bulk-Insert-spezifische Messungen finden
        $bulkMeasurements = array_filter($report['measurements'], function($m) {
            return strpos($m['category'], 'bulk') !== false ||
                   strpos($m['category'], 'database') !== false;
        });

        $totalTime = array_sum(array_column($bulkMeasurements, 'total_time_ms'));
        $recordsPerSecond = $recordCount / ($totalTime / 1000);

        error_log("BULK INSERT STATS: {$table} - {$recordCount} records in {$totalTime}ms ({$recordsPerSecond} records/sec)");
    }

    /**
     * Query-Performance loggen
     */
    private function logQueryPerformance(array $context, float $duration): void
    {
        $logData = [
            'query_type' => $context['type'],
            'duration_ms' => round($duration, 2),
            'sql_pattern' => $this->extractQueryPattern($context['sql']),
            'param_count' => count($context['params']),
            'timestamp' => microtime(true),
        ];

        // Sensitive Parameter entfernen
        if (!empty($context['params'])) {
            $logData['has_params'] = true;
        }

        error_log('DB_PERFORMANCE: ' . json_encode($logData));
    }

    // === HELPER-METHODEN ===

    private function getQueryType(string $sql): string
    {
        $sql = trim(strtoupper($sql));

        if (strpos($sql, 'SELECT') === 0) return 'SELECT';
        if (strpos($sql, 'INSERT') === 0) return 'INSERT';
        if (strpos($sql, 'UPDATE') === 0) return 'UPDATE';
        if (strpos($sql, 'DELETE') === 0) return 'DELETE';
        if (strpos($sql, 'CREATE') === 0) return 'CREATE';
        if (strpos($sql, 'ALTER') === 0) return 'ALTER';
        if (strpos($sql, 'DROP') === 0) return 'DROP';

        return 'OTHER';
    }

    private function extractQueryPattern(string $sql): string
    {
        // SQL normalisieren für Pattern-Erkennung
        $pattern = preg_replace('/\s+/', ' ', trim($sql));
        $pattern = preg_replace('/\d+/', '?', $pattern); // Zahlen durch ? ersetzen
        $pattern = preg_replace("/'[^']*'/", '?', $pattern); // Strings durch ? ersetzen
        $pattern = preg_replace('/\([^)]*\)/', '(?)', $pattern); // Parameter-Listen normalisieren

        return substr($pattern, 0, 100); // Auf 100 Zeichen kürzen
    }

    private function sanitizeSqlForLogging(string $sql): string
    {
        // Sensitive Daten für Logging entfernen
        $sanitized = preg_replace("/'[^']*'/", "'***'", $sql);
        return substr($sanitized, 0, 200) . (strlen($sanitized) > 200 ? '...' : '');
    }

    // === DUMMY-IMPLEMENTIERUNGEN ===

    private function prepareStatement(string $sql): object
    {
        usleep(2000); // 2ms für Statement-Preparation
        return (object)['sql' => $sql, 'prepared' => true];
    }

    private function bindParameters(object $statement, array $params): void
    {
        usleep(1000 * count($params)); // 1ms pro Parameter
    }

    private function executeStatement(object $statement): object
    {
        $queryType = $this->getQueryType($statement->sql);
        $executionTimes = [
            'SELECT' => rand(5000, 50000),   // 5-50ms
            'INSERT' => rand(2000, 15000),   // 2-15ms
            'UPDATE' => rand(3000, 25000),   // 3-25ms
            'DELETE' => rand(3000, 20000),   // 3-20ms
            'OTHER' => rand(1000, 10000),    // 1-10ms
        ];

        usleep($executionTimes[$queryType] ?? 5000);

        return (object)[
            'success' => true,
            'affected_rows' => rand(1, 100),
            'data' => range(1, rand(1, 1000)),
        ];
    }

    private function fetchResults(object $result): array
    {
        $rowCount = count($result->data);
        usleep(100 * $rowCount); // 0.1ms pro Row
        return $result->data;
    }

    private function beginTransaction(): void
    {
        usleep(1000); // 1ms für Transaction-Start
    }

    private function commitTransaction(): void
    {
        usleep(3000); // 3ms für Commit
    }

    private function rollbackTransaction(): void
    {
        usleep(2000); // 2ms für Rollback
    }

    private function prepareBulkData(array $data): array
    {
        usleep(500 * count($data)); // 0.5ms pro Record
        return $data;
    }

    private function generateBulkInsertSql(string $table, array $data): string
    {
        usleep(2000); // 2ms für SQL-Generation
        return "INSERT INTO {$table} VALUES " . str_repeat('(?),', count($data));
    }

    private function executeBulkInsert(string $sql, array $data): bool
    {
        usleep(1000 * count($data)); // 1ms pro Record
        return true;
    }

    private function acquireConnection(): object
    {
        usleep(rand(1000, 10000)); // 1-10ms für Connection-Akquisition
        return (object)['id' => uniqid('conn_'), 'acquired_at' => microtime(true)];
    }

    private function releaseConnection(object $connection): void
    {
        usleep(500); // 0.5ms für Connection-Release
    }
}
