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

class DemoOracleDiagRepository
{
    // ── ORA-01555 ────────────────────────────────────────────────────────

    public static function ora1555GetUndoParams($conn): array
    {
        $params = [];
        $stm = @oci_parse($conn,
            "SELECT name, value FROM V\$PARAMETER
             WHERE name IN ('undo_management','undo_retention','undo_tablespace','undo_recovery')");
        if ($stm && @oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) {
                $params[(string)$r['NAME']] = (string)$r['VALUE'];
            }
            oci_free_statement($stm);
        }
        return $params;
    }

    public static function ora1555GetUndoStats($conn, int $limit = 50): array
    {
        $rows = [];
        $stm = @oci_parse($conn,
            "SELECT TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI') AS begin_time,
                    TO_CHAR(end_time,   'YYYY-MM-DD HH24:MI') AS end_time,
                    undoblks, txncount, maxquerylen, maxconcurrency,
                    unxpstealcnt, expstealcnt,
                    ssolderrcnt, nospaceerrcnt,
                    tuned_undoretention
             FROM   V\$UNDOSTAT
             ORDER BY begin_time DESC
             FETCH FIRST :lim ROWS ONLY");
        if ($stm) {
            oci_bind_by_name($stm, ':lim', $limit, -1, SQLT_INT);
            if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
                while ($r = oci_fetch_assoc($stm)) $rows[] = $r;
            }
            oci_free_statement($stm);
        }
        return $rows;
    }

    // ── ORA-04030 ────────────────────────────────────────────────────────

    public static function ora4030GetPgaParams($conn): array
    {
        $params = [];
        $stm = @oci_parse($conn,
            "SELECT name, value FROM V\$PARAMETER
             WHERE name IN ('pga_aggregate_target','pga_aggregate_limit',
                            'memory_target','memory_max_target',
                            'workarea_size_policy')");
        if ($stm && @oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) {
                $params[(string)$r['NAME']] = (string)$r['VALUE'];
            }
            oci_free_statement($stm);
        }
        return $params;
    }

    public static function ora4030GetPgaStats($conn): array
    {
        $stats = [];
        $stm = @oci_parse($conn,
            "SELECT name, value FROM V\$PGASTAT
             WHERE name IN ('aggregate PGA target parameter',
                            'aggregate PGA auto target',
                            'total PGA inuse',
                            'total PGA allocated',
                            'maximum PGA allocated',
                            'total freeable PGA memory',
                            'PGA memory freed back to OS',
                            'cache hit percentage',
                            'over allocation count')");
        if ($stm && @oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) {
                $stats[(string)$r['NAME']] = (float)$r['VALUE'];
            }
            oci_free_statement($stm);
        }
        return $stats;
    }

    public static function ora4030GetTopProcesses($conn, int $limit = 25): array
    {
        $rows = [];
        $stm = @oci_parse($conn,
            "SELECT inst_id, pid, spid, username, program,
                    pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem
             FROM   GV\$PROCESS
             WHERE  pname IS NOT NULL
             ORDER  BY pga_alloc_mem DESC
             FETCH FIRST :lim ROWS ONLY");
        if ($stm) {
            oci_bind_by_name($stm, ':lim', $limit, -1, SQLT_INT);
            if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
                while ($r = oci_fetch_assoc($stm)) $rows[] = $r;
            }
            oci_free_statement($stm);
        }
        return $rows;
    }

    public static function ora4030GetMemoryByCategory($conn): array
    {
        $rows = [];
        $stm = @oci_parse($conn,
            "SELECT category, SUM(allocated) AS allocated, SUM(used) AS used,
                    SUM(max_allocated) AS max_allocated
             FROM   GV\$PROCESS_MEMORY
             GROUP BY category
             ORDER BY SUM(allocated) DESC");
        if ($stm && @oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) $rows[] = $r;
            oci_free_statement($stm);
        }
        return $rows;
    }

    // ── ORA-04031 ────────────────────────────────────────────────────────

    public static function ora4031GetSgaParams($conn): array
    {
        $params = [];
        $stm = @oci_parse($conn,
            "SELECT name, value FROM V\$PARAMETER
             WHERE name IN ('sga_target','sga_max_size','shared_pool_size',
                            'shared_pool_reserved_size','memory_target',
                            'cursor_sharing','open_cursors','session_cached_cursors')");
        if ($stm && @oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) {
                $params[(string)$r['NAME']] = (string)$r['VALUE'];
            }
            oci_free_statement($stm);
        }
        return $params;
    }

    public static function ora4031GetSharedPoolTop($conn, int $limit = 25): array
    {
        $rows = [];
        $stm = @oci_parse($conn,
            "SELECT name, bytes
             FROM   V\$SGASTAT
             WHERE  pool = 'shared pool'
             ORDER BY bytes DESC
             FETCH FIRST :lim ROWS ONLY");
        if ($stm) {
            oci_bind_by_name($stm, ':lim', $limit, -1, SQLT_INT);
            if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
                while ($r = oci_fetch_assoc($stm)) $rows[] = $r;
            }
            oci_free_statement($stm);
        }
        return $rows;
    }

    public static function ora4031GetSharedPoolFree($conn): array
    {
        $stm = @oci_parse($conn,
            "SELECT NVL(SUM(CASE WHEN name = 'free memory'           THEN bytes END), 0) AS free_mem,
                    NVL(SUM(CASE WHEN name = 'reserved free memory'  THEN bytes END), 0) AS reserved_free
             FROM V\$SGASTAT WHERE pool = 'shared pool'");
        if ($stm && @oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            if ($r = oci_fetch_assoc($stm)) {
                oci_free_statement($stm);
                return [
                    'free_mem'      => (float)($r['FREE_MEM']      ?? 0),
                    'reserved_free' => (float)($r['RESERVED_FREE'] ?? 0),
                ];
            }
            oci_free_statement($stm);
        }
        return ['free_mem' => 0.0, 'reserved_free' => 0.0];
    }

    public static function ora4031GetLibraryCache($conn): array
    {
        $rows = [];
        $stm = @oci_parse($conn,
            "SELECT namespace, gets, gethits, gethitratio,
                    pins, pinhits, pinhitratio,
                    reloads, invalidations
             FROM   V\$LIBRARYCACHE
             ORDER BY reloads DESC NULLS LAST");
        if ($stm && @oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) $rows[] = $r;
            oci_free_statement($stm);
        }
        return $rows;
    }

    public static function ora4031GetHighVersionCount($conn, int $minVersions = 10, int $limit = 25): array
    {
        $rows = [];
        $stm = @oci_parse($conn,
            "SELECT sql_id,
                    version_count,
                    SUBSTR(sql_text, 1, 120) AS sql_preview,
                    parsing_schema_name,
                    module
             FROM   V\$SQLAREA
             WHERE  version_count >= :min_ver
             ORDER BY version_count DESC
             FETCH FIRST :lim ROWS ONLY");
        if ($stm) {
            oci_bind_by_name($stm, ':min_ver', $minVersions, -1, SQLT_INT);
            oci_bind_by_name($stm, ':lim', $limit, -1, SQLT_INT);
            if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
                while ($r = oci_fetch_assoc($stm)) $rows[] = $r;
            }
            oci_free_statement($stm);
        }
        return $rows;
    }

    // ── Slowdown ─────────────────────────────────────────────────────────

    public static function slowdownGetTopWaits($conn, int $limit = 20): array
    {
        $rows = [];
        $stm = @oci_parse($conn,
            "SELECT event, wait_class,
                    total_waits, total_timeouts,
                    ROUND(time_waited_micro/1e6, 2)        AS time_waited_s,
                    ROUND(average_wait_micro/1000,  2)     AS avg_ms
             FROM   V\$SYSTEM_EVENT
             WHERE  wait_class != 'Idle'
             ORDER BY time_waited_micro DESC
             FETCH FIRST :lim ROWS ONLY");
        if ($stm) {
            oci_bind_by_name($stm, ':lim', $limit, -1, SQLT_INT);
            if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
                while ($r = oci_fetch_assoc($stm)) $rows[] = $r;
            }
            oci_free_statement($stm);
        }
        return $rows;
    }

    public static function slowdownGetActiveSessions($conn, int $limit = 25): array
    {
        $rows = [];
        $stm = @oci_parse($conn,
            "SELECT s.inst_id, s.sid, s.serial# AS serial_num,
                    s.username, s.schemaname,
                    s.sql_id, s.last_call_et,
                    CASE WHEN s.state = 'ON CPU'
                         THEN 'CPU'
                         ELSE NVL(s.wait_class, 'Other')
                    END AS wait_class,
                    s.event, s.machine, s.program
             FROM   GV\$SESSION s
             WHERE  s.status   = 'ACTIVE'
               AND  s.type     = 'USER'
               AND  s.username IS NOT NULL
             ORDER BY s.last_call_et DESC
             FETCH FIRST :lim ROWS ONLY");
        if ($stm) {
            oci_bind_by_name($stm, ':lim', $limit, -1, SQLT_INT);
            if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
                while ($r = oci_fetch_assoc($stm)) $rows[] = $r;
            }
            oci_free_statement($stm);
        }
        return $rows;
    }

    public static function slowdownGetTopSql($conn, int $limit = 20): array
    {
        $rows = [];
        $stm = @oci_parse($conn,
            "SELECT sql_id,
                    SUBSTR(sql_text, 1, 120)         AS sql_preview,
                    parsing_schema_name,
                    executions,
                    ROUND(elapsed_time/1e6, 2)       AS elapsed_s,
                    ROUND(cpu_time/1e6, 2)           AS cpu_s,
                    buffer_gets, disk_reads, rows_processed
             FROM   V\$SQLSTATS
             ORDER BY elapsed_time DESC
             FETCH FIRST :lim ROWS ONLY");
        if ($stm) {
            oci_bind_by_name($stm, ':lim', $limit, -1, SQLT_INT);
            if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
                while ($r = oci_fetch_assoc($stm)) $rows[] = $r;
            }
            oci_free_statement($stm);
        }
        return $rows;
    }

    public static function slowdownGetLongOps($conn, int $limit = 15): array
    {
        $rows = [];
        $stm = @oci_parse($conn,
            "SELECT inst_id, sid, serial# AS serial_num,
                    username, opname, target,
                    sofar, totalwork, units,
                    TO_CHAR(start_time,       'YYYY-MM-DD HH24:MI:SS') AS start_time,
                    TO_CHAR(last_update_time, 'YYYY-MM-DD HH24:MI:SS') AS last_update_time,
                    elapsed_seconds, time_remaining,
                    sql_id
             FROM   GV\$SESSION_LONGOPS
             WHERE  time_remaining > 0 OR sofar < totalwork
             ORDER BY start_time DESC
             FETCH FIRST :lim ROWS ONLY");
        if ($stm) {
            oci_bind_by_name($stm, ':lim', $limit, -1, SQLT_INT);
            if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
                while ($r = oci_fetch_assoc($stm)) $rows[] = $r;
            }
            oci_free_statement($stm);
        }
        return $rows;
    }
}
