<?php

namespace App\Libraries;

use App\Models\QueryCacheModel;
use PDO;
use RuntimeException;

class QueryExecutor
{
    /**
     * @param array<string, mixed> $dataSource
     * @param array<int, mixed> $bindings
     * @return array{rows:array<int, array<string, mixed>>, row_count:int, execution_ms:float, cache_hit:bool}
     */
    public function executeSql(array $dataSource, string $sql, array $bindings = [], int $cacheTtl = 0): array
    {
        $cacheKey = md5((string) ($dataSource['id'] ?? 0) . '|' . $sql . '|' . json_encode($bindings));
        if ($cacheTtl > 0) {
            $cached = (new QueryCacheModel())->findValid($cacheKey);
            if (is_array($cached)) {
                $rows = json_decode((string) ($cached['result_data'] ?? '[]'), true);
                if (! is_array($rows)) {
                    $rows = [];
                }
                return [
                    'rows' => $rows,
                    'row_count' => (int) ($cached['row_count'] ?? count($rows)),
                    'execution_ms' => 0.0,
                    'cache_hit' => true,
                ];
            }
        }

        $pdo = $this->makePdo($dataSource);
        $started = microtime(true);
        $stmt = $pdo->prepare($sql);
        $stmt->execute($bindings);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $executionMs = (microtime(true) - $started) * 1000;

        if ($cacheTtl > 0) {
            $cacheModel = new QueryCacheModel();
            $cacheModel->where('cache_key', $cacheKey)->delete();
            $cacheModel->insert([
                'cache_key' => $cacheKey,
                'data_source_id' => (int) ($dataSource['id'] ?? 0),
                'result_data' => json_encode($rows, JSON_UNESCAPED_UNICODE),
                'row_count' => count($rows),
                'expires_at' => date('Y-m-d H:i:s', time() + $cacheTtl),
            ]);
        }

        return [
            'rows' => $rows,
            'row_count' => count($rows),
            'execution_ms' => round($executionMs, 2),
            'cache_hit' => false,
        ];
    }

    /**
     * @param array<string, mixed> $dataSource
     */
    private function makePdo(array $dataSource): PDO
    {
        $type = (string) ($dataSource['type'] ?? '');
        if ($type === 'mysql') {
            $dsn = sprintf(
                'mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',
                $dataSource['host'] ?? '',
                (int) ($dataSource['port'] ?? 3306),
                $dataSource['database_name'] ?? ''
            );
        } elseif ($type === 'postgresql') {
            $dsn = sprintf(
                'pgsql:host=%s;port=%d;dbname=%s',
                $dataSource['host'] ?? '',
                (int) ($dataSource['port'] ?? 5432),
                $dataSource['database_name'] ?? ''
            );
        } else {
            throw new RuntimeException('SQL execution is supported only for MySQL and PostgreSQL data sources.');
        }

        return new PDO(
            $dsn,
            (string) ($dataSource['username'] ?? ''),
            (string) ($dataSource['password'] ?? ''),
            [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_TIMEOUT => 30,
            ]
        );
    }
}