<?php
/**
 * Demo Oracle — DemoOracleIndicatorsRepository.
 *
 * Portage de modules/oracle/lib/OracleIndicatorsRepository.php : requetes de LECTURE
 * uniquement.
 *
 * Convention conservee du module complet : classe sans etat, $conn (ressource
 * OCI8) en premier argument, retour systematique en array — vide en cas
 * d'echec OCI, jamais false, jamais d'exception. La page appelante affiche
 * l'erreur globale.
 */

if (class_exists('DemoOracleIndicatorsRepository')) return;

class DemoOracleIndicatorsRepository
{
    public static function fetchSysStats($conn): array
    {
        $kv = [];
        $sql = "
            SELECT name, value FROM v\$sysstat
            WHERE name IN ('parse count (total)','parse count (hard)',
                           'parse time cpu','parse time elapsed',
                           'execute count','CPU used by this session',
                           'sorts (memory)','sorts (disk)',
                           'redo entries','redo log space requests',
                           'consistent gets','db block gets','physical reads')
            UNION ALL
            SELECT 'librarycache.pins',    NVL(SUM(pins),0)    FROM v\$librarycache
            UNION ALL
            SELECT 'librarycache.pinhits', NVL(SUM(pinhits),0) FROM v\$librarycache
            UNION ALL
            SELECT 'latch.gets',           NVL(SUM(gets),0)    FROM v\$latch
            UNION ALL
            SELECT 'latch.misses',         NVL(SUM(misses),0)  FROM v\$latch
            UNION ALL
            SELECT 'event.buffer_busy_waits',
                   NVL((SELECT total_waits FROM v\$system_event WHERE event='buffer busy waits'),0)
              FROM dual
        ";
        $stm = @oci_parse($conn, $sql);
        if ($stm) {
            if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
                while ($r = oci_fetch_assoc($stm)) {
                    $kv[(string)$r['NAME']] = (float)$r['VALUE'];
                }
            }
            oci_free_statement($stm);
        }
        return $kv;
    }

    public static function fetchDbCacheAdvice($conn): array
    {
        return self::fetchAdvisorRows($conn, "
            SELECT size_for_estimate AS sz, estd_physical_read_factor AS factor,
                   estd_physical_reads AS metric, size_factor
            FROM   v\$db_cache_advice
            WHERE  name = 'DEFAULT'
              AND  block_size = (SELECT value FROM v\$parameter WHERE name='db_block_size')
            ORDER BY size_for_estimate");
    }

    public static function fetchSharedPoolAdvice($conn): array
    {
        return self::fetchAdvisorRows($conn, "
            SELECT shared_pool_size_for_estimate AS sz,
                   estd_lc_load_time_factor AS factor,
                   estd_lc_load_time AS metric,
                   shared_pool_size_factor AS size_factor
            FROM   v\$shared_pool_advice
            ORDER BY shared_pool_size_for_estimate");
    }

    public static function fetchPgaAdvice($conn): array
    {
        return self::fetchAdvisorRows($conn, "
            SELECT pga_target_for_estimate/1048576 AS sz,
                   pga_target_factor AS factor,
                   estd_pga_cache_hit_percentage AS metric,
                   pga_target_factor AS size_factor
            FROM   v\$pga_target_advice
            ORDER BY pga_target_for_estimate");
    }

    public static function fetchSgaAdvice($conn): array
    {
        return self::fetchAdvisorRows($conn, "
            SELECT sga_size AS sz, sga_size_factor AS factor,
                   estd_db_time AS metric, sga_size_factor AS size_factor
            FROM   v\$sga_target_advice
            ORDER BY sga_size");
    }

    public static function fetchMemoryAdvice($conn): array
    {
        return self::fetchAdvisorRows($conn, "
            SELECT memory_size AS sz, memory_size_factor AS factor,
                   estd_db_time AS metric, memory_size_factor AS size_factor
            FROM   v\$memory_target_advice
            ORDER BY memory_size");
    }

    private static function fetchAdvisorRows($conn, string $sql): array
    {
        $rows = [];
        $stm = @oci_parse($conn, $sql);
        if ($stm) {
            if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
                while ($r = oci_fetch_assoc($stm)) {
                    $rows[] = [
                        'sz'          => (float)($r['SZ'] ?? 0),
                        'factor'      => (float)($r['FACTOR'] ?? 0),
                        'metric'      => (float)($r['METRIC'] ?? 0),
                        'size_factor' => (float)($r['SIZE_FACTOR'] ?? 0),
                    ];
                }
            }
            oci_free_statement($stm);
        }
        return $rows;
    }
}
