<?php
/**
 * Demo Oracle — DemoOracleRealtimeRepository.
 *
 * Portage de modules/oracle/lib/OracleRealtimeRepository.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('DemoOracleRealtimeRepository')) return;

class DemoOracleRealtimeRepository
{
    public static function fetchTimeline($conn, string $ashView, string $dimExpr, int $minutes, int $svcHash, string $svcWhere, string $stateWhere): array
    {
        $rows = [];
        $sql = "SELECT TO_CHAR(TRUNC(ash.sample_time,'MI'),'YYYY-MM-DD HH24:MI') AS bucket,
                       {$dimExpr} AS dim_value,
                       COUNT(*) AS samples
                FROM {$ashView} ash
                WHERE ash.sample_time >= SYSDATE - :wmin/1440
                  AND ash.session_type != 'BACKGROUND'
                  {$svcWhere}
                  {$stateWhere}
                GROUP BY TRUNC(ash.sample_time,'MI'), {$dimExpr}
                ORDER BY 1, 2";
        $stm = @oci_parse($conn, $sql);
        if (!$stm) return $rows;
        oci_bind_by_name($stm, ':wmin', $minutes, -1, SQLT_INT);
        if ($svcHash !== 0) oci_bind_by_name($stm, ':svc_hash', $svcHash, -1, SQLT_INT);
        if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) {
                $bkt = (string)$r['BUCKET'];
                $dv  = (string)$r['DIM_VALUE'];
                if (!isset($rows[$bkt])) $rows[$bkt] = [];
                $rows[$bkt][$dv] = (int)$r['SAMPLES'];
            }
        }
        oci_free_statement($stm);
        return $rows;
    }

    public static function fetchTopSql($conn, string $ashView, int $minutes, int $svcHash, string $svcWhere, string $stateWhere, string $xfWhere, string $xfVal, bool $xfBind): array
    {
        $rows = [];
        $sql = "SELECT ash.sql_id, COUNT(*) samples
                FROM {$ashView} ash
                WHERE ash.sample_time >= SYSDATE - :wmin/1440
                  AND ash.sql_id IS NOT NULL
                  AND ash.session_type != 'BACKGROUND'
                  {$svcWhere}
                  {$stateWhere}
                  {$xfWhere}
                GROUP BY ash.sql_id
                ORDER BY COUNT(*) DESC
                FETCH FIRST 10 ROWS ONLY";
        $stm = @oci_parse($conn, $sql);
        if (!$stm) return $rows;
        oci_bind_by_name($stm, ':wmin', $minutes, -1, SQLT_INT);
        if ($svcHash !== 0) oci_bind_by_name($stm, ':svc_hash', $svcHash, -1, SQLT_INT);
        if ($xfBind && $xfVal !== '') oci_bind_by_name($stm, ':xf_val', $xfVal, -1, SQLT_CHR);
        if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) {
                $rows[] = ['sql_id' => (string)$r['SQL_ID'], 'samples' => (int)$r['SAMPLES']];
            }
        }
        oci_free_statement($stm);
        return $rows;
    }

    public static function fetchTopEvents($conn, string $ashView, int $minutes, int $svcHash, string $svcWhere, string $stateWhere, string $xfWhere, string $xfVal, bool $xfBind): array
    {
        $rows = [];
        $sql = "SELECT CASE WHEN ash.session_state='ON CPU' THEN 'CPU'
                            ELSE NVL(ash.event,'Other') END AS event_name,
                       CASE WHEN ash.session_state='ON CPU' THEN 'CPU'
                            ELSE NVL(ash.wait_class,'Other') END AS wait_class,
                       COUNT(*) AS samples
                FROM {$ashView} ash
                WHERE ash.sample_time >= SYSDATE - :wmin/1440
                  AND ash.session_type != 'BACKGROUND'
                  {$svcWhere}
                  {$stateWhere}
                  {$xfWhere}
                GROUP BY CASE WHEN ash.session_state='ON CPU' THEN 'CPU' ELSE NVL(ash.event,'Other') END,
                         CASE WHEN ash.session_state='ON CPU' THEN 'CPU' ELSE NVL(ash.wait_class,'Other') END
                ORDER BY COUNT(*) DESC
                FETCH FIRST 15 ROWS ONLY";
        $stm = @oci_parse($conn, $sql);
        if (!$stm) return $rows;
        oci_bind_by_name($stm, ':wmin', $minutes, -1, SQLT_INT);
        if ($svcHash !== 0) oci_bind_by_name($stm, ':svc_hash', $svcHash, -1, SQLT_INT);
        if ($xfBind && $xfVal !== '') oci_bind_by_name($stm, ':xf_val', $xfVal, -1, SQLT_CHR);
        if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) {
                $rows[] = [
                    'event'      => (string)$r['EVENT_NAME'],
                    'wait_class' => (string)$r['WAIT_CLASS'],
                    'samples'    => (int)$r['SAMPLES'],
                ];
            }
        }
        oci_free_statement($stm);
        return $rows;
    }

    public static function fetchTopModules($conn, string $ashView, int $minutes, int $svcHash, string $svcWhere, string $stateWhere, string $xfWhere, string $xfVal, bool $xfBind): array
    {
        return self::topN($conn,
            "SELECT NVL(ash.module,'(none)') AS lbl, COUNT(*) samples
             FROM {$ashView} ash
             WHERE ash.sample_time >= SYSDATE - :wmin/1440
               AND ash.session_type != 'BACKGROUND'
               AND ash.module IS NOT NULL
               {$svcWhere} {$stateWhere} {$xfWhere}
             GROUP BY ash.module ORDER BY COUNT(*) DESC FETCH FIRST 10 ROWS ONLY",
            $minutes, $svcHash, $xfVal, $xfBind);
    }

    public static function fetchTopActions($conn, string $ashView, int $minutes, int $svcHash, string $svcWhere, string $stateWhere, string $xfWhere, string $xfVal, bool $xfBind): array
    {
        return self::topN($conn,
            "SELECT NVL(ash.action,'(none)') AS lbl, COUNT(*) samples
             FROM {$ashView} ash
             WHERE ash.sample_time >= SYSDATE - :wmin/1440
               AND ash.session_type != 'BACKGROUND'
               AND ash.action IS NOT NULL
               {$svcWhere} {$stateWhere} {$xfWhere}
             GROUP BY ash.action ORDER BY COUNT(*) DESC FETCH FIRST 10 ROWS ONLY",
            $minutes, $svcHash, $xfVal, $xfBind);
    }

    public static function fetchTopServices($conn, string $ashView, int $minutes, int $svcHash, string $svcWhere, string $stateWhere, string $xfWhere, string $xfVal, bool $xfBind): array
    {
        return self::topN($conn,
            "SELECT NVL(svc.name, TO_CHAR(ash.service_hash)) AS lbl, COUNT(*) samples
             FROM {$ashView} ash
             LEFT JOIN v\$services svc ON svc.name_hash = ash.service_hash
             WHERE ash.sample_time >= SYSDATE - :wmin/1440
               AND ash.session_type != 'BACKGROUND'
               {$svcWhere} {$stateWhere} {$xfWhere}
             GROUP BY NVL(svc.name, TO_CHAR(ash.service_hash)) ORDER BY COUNT(*) DESC FETCH FIRST 10 ROWS ONLY",
            $minutes, $svcHash, $xfVal, $xfBind);
    }

    public static function fetchTopMachines($conn, string $ashView, int $minutes, int $svcHash, string $svcWhere, string $stateWhere, string $xfWhere, string $xfVal, bool $xfBind): array
    {
        return self::topN($conn,
            "SELECT NVL(ash.machine,'(unknown)') AS lbl, COUNT(*) samples
             FROM {$ashView} ash
             WHERE ash.sample_time >= SYSDATE - :wmin/1440
               AND ash.session_type != 'BACKGROUND'
               {$svcWhere} {$stateWhere} {$xfWhere}
             GROUP BY ash.machine ORDER BY COUNT(*) DESC FETCH FIRST 10 ROWS ONLY",
            $minutes, $svcHash, $xfVal, $xfBind);
    }

    public static function fetchTopObjects($conn, string $ashView, int $minutes, int $svcHash, string $svcWhere, string $stateWhere, string $xfWhere, string $xfVal, bool $xfBind): array
    {
        return self::topN($conn,
            "SELECT NVL(obj.object_name,'#'||ash.current_obj#) AS lbl, COUNT(*) samples
             FROM {$ashView} ash
             LEFT JOIN dba_objects obj ON obj.object_id = ash.current_obj#
             WHERE ash.sample_time >= SYSDATE - :wmin/1440
               AND ash.session_type != 'BACKGROUND'
               AND ash.current_obj# > 0
               {$svcWhere} {$stateWhere} {$xfWhere}
             GROUP BY NVL(obj.object_name,'#'||ash.current_obj#) ORDER BY COUNT(*) DESC FETCH FIRST 10 ROWS ONLY",
            $minutes, $svcHash, $xfVal, $xfBind);
    }

    public static function fetchTopObjectTypes($conn, string $ashView, int $minutes, int $svcHash, string $svcWhere, string $stateWhere, string $xfWhere, string $xfVal, bool $xfBind): array
    {
        return self::topN($conn,
            "SELECT NVL(obj.object_type,'(unknown)') AS lbl, COUNT(*) samples
             FROM {$ashView} ash
             LEFT JOIN dba_objects obj ON obj.object_id = ash.current_obj#
             WHERE ash.sample_time >= SYSDATE - :wmin/1440
               AND ash.session_type != 'BACKGROUND'
               AND ash.current_obj# > 0
               {$svcWhere} {$stateWhere} {$xfWhere}
             GROUP BY obj.object_type ORDER BY COUNT(*) DESC FETCH FIRST 10 ROWS ONLY",
            $minutes, $svcHash, $xfVal, $xfBind);
    }

    public static function fetchTopInstances($conn, string $ashView, int $minutes, int $svcHash, string $svcWhere, string $stateWhere, string $xfWhere, string $xfVal, bool $xfBind): array
    {
        return self::topN($conn,
            "SELECT TO_CHAR(ash.inst_id) AS lbl, COUNT(*) samples
             FROM {$ashView} ash
             WHERE ash.sample_time >= SYSDATE - :wmin/1440
               AND ash.session_type != 'BACKGROUND'
               {$svcWhere} {$stateWhere} {$xfWhere}
             GROUP BY ash.inst_id ORDER BY ash.inst_id",
            $minutes, $svcHash, $xfVal, $xfBind);
    }

    public static function fetchTopPdbs($conn, string $ashView, int $minutes, int $svcHash, string $svcWhere, string $stateWhere, string $xfWhere, string $xfVal, bool $xfBind): array
    {
        return self::topN($conn,
            "SELECT NVL(c.name,'CDB\$ROOT') AS lbl, COUNT(*) samples
             FROM {$ashView} ash
             LEFT JOIN v\$containers c ON c.con_id = ash.con_id
             WHERE ash.sample_time >= SYSDATE - :wmin/1440
               AND ash.session_type != 'BACKGROUND'
               {$svcWhere} {$stateWhere} {$xfWhere}
             GROUP BY NVL(c.name,'CDB\$ROOT') ORDER BY COUNT(*) DESC FETCH FIRST 10 ROWS ONLY",
            $minutes, $svcHash, $xfVal, $xfBind);
    }

    public static function isPdbSupported($conn): bool
    {
        $stm = @oci_parse($conn, "SELECT version FROM v\$instance");
        if (!$stm) return false;
        if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            if ($r = oci_fetch_assoc($stm)) {
                oci_free_statement($stm);
                $vp    = explode('.', (string)($r['VERSION'] ?? '0'));
                $major = (int)($vp[0] ?? 0);
                $minor = (int)($vp[1] ?? 0);
                return $major > 12 || ($major === 12 && $minor >= 2);
            }
        }
        oci_free_statement($stm);
        return false;
    }

    public static function fetchServices($conn): array
    {
        $rows = [];
        $stm = @oci_parse($conn,
            "SELECT name, name_hash FROM v\$services WHERE name NOT IN ('SYS\$BACKGROUND','SYS\$USERS') ORDER BY name");
        if (!$stm) return $rows;
        if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) {
                $rows[(string)$r['NAME_HASH']] = (string)$r['NAME'];
            }
        }
        oci_free_statement($stm);
        return $rows;
    }

    private static function topN($conn, string $sql, int $minutes, int $svcHash, string $xfVal, bool $xfBind): array
    {
        $rows = [];
        $stm = @oci_parse($conn, $sql);
        if (!$stm) return $rows;
        oci_bind_by_name($stm, ':wmin', $minutes, -1, SQLT_INT);
        if ($svcHash !== 0) oci_bind_by_name($stm, ':svc_hash', $svcHash, -1, SQLT_INT);
        if ($xfBind && $xfVal !== '') oci_bind_by_name($stm, ':xf_val', $xfVal, -1, SQLT_CHR);
        if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) {
                $rows[] = ['label' => (string)$r['LBL'], 'samples' => (int)$r['SAMPLES']];
            }
        }
        oci_free_statement($stm);
        return $rows;
    }
}
