<?php
/**
 * Demo Oracle — SQL Monitor natif, SANS aucune licence Oracle.
 *
 * C'est la piece de demonstration la plus parlante du module : Oracle facture
 * le Real-Time SQL Monitoring au titre du Tuning Pack, et ce fichier en
 * reconstitue l'essentiel a partir de vues qui ne relevent d'aucun pack —
 * GV$SESSION echantillonnee, GV$SQL_WORKAREA_ACTIVE, GV$SESSION_LONGOPS,
 * GV$SQL_PLAN.
 *
 * Portage de modules/oracle/lib/OracleSqlMonitorRepository.php dont la
 * branche Tuning Pack a ete RETIREE A LA SOURCE, pas desactivee :
 *
 *   listExecutionsPack   getExecutionPack   getPlanMonitorRows
 *   getAshActivity       getCursorTotals
 *
 * Consequence verifiable, et verifiee par le banc : ce fichier ne cite ni
 * GV$SQL_MONITOR, ni GV$SQL_PLAN_MONITOR, ni GV$ACTIVE_SESSION_HISTORY. Une
 * vue qu'on n'ecrit pas est une vue qu'on n'interroge pas.
 *
 * Convention conservee du module complet : classe sans etat, methodes
 * statiques, $conn en 1er argument, retour systematique en array — vide en
 * cas d'echec OCI, jamais false, jamais d'exception.
 *
 * Safe to include multiple times (guard sur class_exists).
 */
if (class_exists('DemoOracleSqlMonitorRepository')) return;

class DemoOracleSqlMonitorRepository
{
    /** Plafond dur sur toute liste, quelle que soit la demande du caller. */
    private const MAX_ROWS = 200;

    // ═══════════════════════════════════════════════════════════════════════
    //  Branche « session » — aucun pack : GV$SESSION, workarea, longops
    // ═══════════════════════════════════════════════════════════════════════

    /**
     * Inventaire des executions en cours, reconstitue depuis les sessions.
     *
     * Sans pack il n'existe aucun inventaire persistant : une execution
     * terminee est invisible. C'est la limite honnete de cette branche, et
     * l'interface doit le dire.
     *
     * L'agregation par cle d'execution dedoublonne coordinateur et esclaves
     * PX sans entrer dans le parallelisme detaille ; `sessions` donne au
     * passage le nombre de processus, information gratuite.
     */
    public static function listExecutionsSession($conn, string $sqlId = '', int $limit = 100): array
    {
        $where = ["s.type = 'USER'", "s.status = 'ACTIVE'",
                  's.sql_id IS NOT NULL', 's.sql_exec_id IS NOT NULL'];
        $binds = [];
        if ($sqlId !== '') { $where[] = 's.sql_id = :p_sql_id'; $binds[':p_sql_id'] = $sqlId; }

        $sql = "SELECT MIN(s.inst_id)                            AS inst_id,
                       s.sql_id,
                       s.sql_exec_id,
                       TO_CHAR(s.sql_exec_start, 'YYYY-MM-DD HH24:MI:SS') AS exec_start,
                       MAX(s.sql_plan_hash_value)                AS sql_plan_hash_value,
                       MIN(s.sid)                                AS sid,
                       COUNT(*)                                  AS sessions,
                       MAX(NVL(s.username, '?'))                 AS username,
                       MAX(NVL(s.module,   '?'))                 AS module,
                       ROUND((SYSDATE - s.sql_exec_start) * 86400) AS elapsed_s
                FROM   GV\$SESSION s
                WHERE  " . implode("\n                  AND  ", $where) . "
                GROUP BY s.sql_id, s.sql_exec_id, s.sql_exec_start
                ORDER BY s.sql_exec_start DESC
                FETCH FIRST " . self::cap($limit) . " ROWS ONLY";

        $rows = self::fetchAll($conn, $sql, $binds);
        if ($rows === null) return [];

        $out = [];
        foreach ($rows as $r) {
            $out[] = [
                'inst_id'     => (int)($r['INST_ID'] ?? 0),
                'sql_id'      => (string)($r['SQL_ID'] ?? ''),
                'sql_exec_id' => (int)($r['SQL_EXEC_ID'] ?? 0),
                'exec_start'  => (string)($r['EXEC_START'] ?? ''),
                'status'      => 'EXECUTING',
                'phv'         => (string)($r['SQL_PLAN_HASH_VALUE'] ?? ''),
                'sid'         => (int)($r['SID'] ?? 0),
                'sessions'    => (int)($r['SESSIONS'] ?? 0),
                'username'    => (string)($r['USERNAME'] ?? ''),
                'module'      => (string)($r['MODULE'] ?? ''),
                'elapsed_s'   => (float)($r['ELAPSED_S'] ?? 0),
                'cpu_s'       => 0.0,
                'buffer_gets' => 0,
                'disk_reads'  => 0,
                'px_maxdop'   => 0,
            ];
        }
        return $out;
    }

    /**
     * L'en-tete d'une execution, vue depuis les sessions qui la portent.
     *
     * Zero ligne = l'execution est terminee (ou la session a disparu). Sans
     * pack, il n'y a pas d'autre moyen de le savoir : le caller en deduit
     * status='DONE', et la page doit dire que la fin est deduite, pas lue.
     */
    public static function getExecutionSession($conn, string $sqlId, int $execId, string $execStart): array
    {
        $sql = "SELECT MIN(s.inst_id)                                    AS inst_id,
                       MIN(s.sid)                                        AS sid,
                       COUNT(*)                                          AS sessions,
                       MAX(s.sql_plan_hash_value)                        AS sql_plan_hash_value,
                       MAX(NVL(s.username, '?'))                         AS username,
                       MAX(NVL(s.module,   '?'))                         AS module,
                       MAX(NVL(s.service_name, ''))                      AS service_name,
                       ROUND((SYSDATE - MAX(s.sql_exec_start)) * 86400)  AS elapsed_s,
                       TO_CHAR(MAX(s.sql_exec_start), 'YYYY-MM-DD HH24:MI:SS') AS exec_start
                FROM   GV\$SESSION s
                WHERE  s.sql_id         = :p_sql_id
                  AND  s.sql_exec_id    = :p_exec_id
                  AND  s.sql_exec_start = TO_DATE(:p_exec_start, 'YYYY-MM-DD HH24:MI:SS')
                  AND  s.status         = 'ACTIVE'";

        $rows = self::fetchAll($conn, $sql, [
            ':p_sql_id'     => $sqlId,
            ':p_exec_id'    => $execId,
            ':p_exec_start' => $execStart,
        ]);
        /* Un GROUP BY absent rend toujours UNE ligne, a NULL si rien ne
           correspond : c'est COUNT(*) qui dit si l'execution vit encore. */
        if (empty($rows) || (int)($rows[0]['SESSIONS'] ?? 0) === 0) return [];
        $r = $rows[0];

        return [
            'inst_id'       => (int)($r['INST_ID'] ?? 0),
            'sql_id'        => $sqlId,
            'sql_exec_id'   => $execId,
            'exec_start'    => (string)($r['EXEC_START'] ?? $execStart),
            'status'        => 'EXECUTING',
            'phv'           => (string)($r['SQL_PLAN_HASH_VALUE'] ?? ''),
            'sid'           => (int)($r['SID'] ?? 0),
            'sessions'      => (int)($r['SESSIONS'] ?? 0),
            'username'      => (string)($r['USERNAME'] ?? ''),
            'module'        => (string)($r['MODULE'] ?? ''),
            'service_name'  => (string)($r['SERVICE_NAME'] ?? ''),
            'elapsed_s'     => (float)($r['ELAPSED_S'] ?? 0),
        ];
    }

    /**
     * UN echantillon des sessions de l'execution — le coeur de la branche.
     *
     * Un appel = un releve. L'accumulation en histogramme est faite par la
     * page, d'un poll a l'autre : c'est ce qui rend le rapport utile sans
     * pack, la ou ASH ferait le travail.
     *
     * @param bool $planLine V$SESSION.SQL_PLAN_LINE_ID est-elle exploitable ?
     *                       (cf. OracleSqlMonSource::planLineColumnExists)
     *                       Faux, la requete ne la demande meme pas : un
     *                       ORA-00904 rendrait tout l'echantillon vide.
     */
    public static function sampleSessionPlanLines($conn, string $sqlId, int $execId, string $execStart, bool $planLine = true): array
    {
        $planCols = $planLine
            ? "s.sql_plan_line_id            AS plan_line_id,
                       NVL(s.sql_plan_operation, '') AS plan_operation,
                       NVL(s.sql_plan_options,   '') AS plan_options,"
            : "CAST(NULL AS NUMBER)  AS plan_line_id,
                       CAST('' AS VARCHAR2(1)) AS plan_operation,
                       CAST('' AS VARCHAR2(1)) AS plan_options,";

        $sql = "SELECT s.inst_id,
                       s.sid,
                       $planCols
                       CASE WHEN s.state = 'WAITING' AND NVL(s.wait_class, 'Idle') <> 'Idle'
                            THEN s.wait_class ELSE 'CPU' END    AS wait_class,
                       CASE WHEN s.state = 'WAITING' AND NVL(s.wait_class, 'Idle') <> 'Idle'
                            THEN s.event      ELSE 'ON CPU' END AS event
                FROM   GV\$SESSION s
                WHERE  s.sql_id         = :p_sql_id
                  AND  s.sql_exec_id    = :p_exec_id
                  AND  s.sql_exec_start = TO_DATE(:p_exec_start, 'YYYY-MM-DD HH24:MI:SS')
                  AND  s.status         = 'ACTIVE'
                ORDER BY s.inst_id, s.sid";

        $rows = self::fetchAll($conn, $sql, [
            ':p_sql_id'     => $sqlId,
            ':p_exec_id'    => $execId,
            ':p_exec_start' => $execStart,
        ]);
        if ($rows === null) return [];

        $out = [];
        foreach ($rows as $r) {
            $out[] = [
                'inst_id'    => (int)($r['INST_ID'] ?? 0),
                'sid'        => (int)($r['SID'] ?? 0),
                'line'       => ($r['PLAN_LINE_ID'] !== null && $r['PLAN_LINE_ID'] !== '')
                                    ? (int)$r['PLAN_LINE_ID'] : null,
                'op'         => (string)($r['PLAN_OPERATION'] ?? ''),
                'opts'       => (string)($r['PLAN_OPTIONS'] ?? ''),
                'wait_class' => (string)($r['WAIT_CLASS'] ?? 'CPU'),
                'event'      => (string)($r['EVENT'] ?? ''),
            ];
        }
        return $out;
    }

    /**
     * Zones de travail actives — memoire, temp et passes, par ligne de plan.
     *
     * OPERATION_ID EST l'identifiant de ligne de plan : c'est la seule
     * attribution ligne a ligne disponible sans aucun pack, et elle survit
     * meme si SQL_PLAN_LINE_ID n'est pas alimentee.
     *
     * Repli : SQL_EXEC_ID existe sur cette vue depuis 11g, mais si le PARSE
     * echoue on correle par (inst_id, sid) via GV$SESSION.
     */
    public static function getWorkareaActive($conn, string $sqlId, int $execId, string $execStart): array
    {
        $cols = "w.inst_id, w.sid,
                       w.operation_id          AS plan_line_id,
                       w.operation_type,
                       w.policy,
                       w.work_area_size,
                       w.expected_size,
                       w.actual_mem_used,
                       w.max_mem_used,
                       w.number_passes,
                       NVL(w.tempseg_size, 0)  AS tempseg_size,
                       NVL(w.tablespace, '')   AS tablespace";

        $binds = [
            ':p_sql_id'     => $sqlId,
            ':p_exec_id'    => $execId,
            ':p_exec_start' => $execStart,
        ];

        $rows = self::fetchAll($conn,
            "SELECT $cols
             FROM   GV\$SQL_WORKAREA_ACTIVE w
             WHERE  w.sql_id      = :p_sql_id
               AND  w.sql_exec_id = :p_exec_id
             ORDER BY w.inst_id, w.sid, w.operation_id",
            [':p_sql_id' => $sqlId, ':p_exec_id' => $execId]);

        if ($rows === null) {
            /* SQL_EXEC_ID absente de la vue : on passe par les sessions. */
            $rows = self::fetchAll($conn,
                "SELECT $cols
                 FROM   GV\$SQL_WORKAREA_ACTIVE w
                 JOIN   GV\$SESSION s
                   ON   s.inst_id = w.inst_id AND s.sid = w.sid
                 WHERE  s.sql_id         = :p_sql_id
                   AND  s.sql_exec_id    = :p_exec_id
                   AND  s.sql_exec_start = TO_DATE(:p_exec_start, 'YYYY-MM-DD HH24:MI:SS')
                 ORDER BY w.inst_id, w.sid, w.operation_id",
                $binds);
        }
        if ($rows === null) return [];

        $out = [];
        foreach ($rows as $r) {
            $out[] = [
                'inst_id'    => (int)($r['INST_ID'] ?? 0),
                'sid'        => (int)($r['SID'] ?? 0),
                'line'       => (int)($r['PLAN_LINE_ID'] ?? 0),
                'type'       => (string)($r['OPERATION_TYPE'] ?? ''),
                'policy'     => (string)($r['POLICY'] ?? ''),
                'size'       => (int)($r['WORK_AREA_SIZE'] ?? 0),
                'expected'   => (int)($r['EXPECTED_SIZE'] ?? 0),
                'mem'        => (int)($r['ACTUAL_MEM_USED'] ?? 0),
                'max_mem'    => (int)($r['MAX_MEM_USED'] ?? 0),
                'passes'     => (int)($r['NUMBER_PASSES'] ?? 0),
                'temp'       => (int)($r['TEMPSEG_SIZE'] ?? 0),
                'tablespace' => (string)($r['TABLESPACE'] ?? ''),
            ];
        }
        return $out;
    }

    /**
     * Longops de l'execution — l'avancement declare par Oracle lui-meme.
     *
     * Contrairement a OracleSessionsRepository::fetchLongOps(), on garde
     * aussi les operations TERMINEES de l'execution : elles racontent ce qui
     * s'est deja passe, ce qui est precisement l'objet d'un moniteur.
     */
    public static function getLongOpsForExec($conn, string $sqlId, int $execId, string $execStart): array
    {
        $cols = "lo.inst_id, lo.sid,
                       lo.opname,
                       NVL(lo.target, '') AS target,
                       NVL(lo.units,  '') AS units,
                       lo.sofar, lo.totalwork,
                       TO_CHAR(lo.start_time,       'YYYY-MM-DD HH24:MI:SS') AS start_time,
                       TO_CHAR(lo.last_update_time, 'YYYY-MM-DD HH24:MI:SS') AS last_update_time,
                       lo.elapsed_seconds,
                       lo.time_remaining,
                       NVL(lo.message, '') AS message";

        $rows = self::fetchAll($conn,
            "SELECT $cols
             FROM   GV\$SESSION_LONGOPS lo
             WHERE  lo.sql_id         = :p_sql_id
               AND  lo.sql_exec_id    = :p_exec_id
               AND  lo.sql_exec_start = TO_DATE(:p_exec_start, 'YYYY-MM-DD HH24:MI:SS')
               AND  lo.totalwork > 0
             ORDER BY lo.start_time",
            [':p_sql_id' => $sqlId, ':p_exec_id' => $execId, ':p_exec_start' => $execStart]);

        if ($rows === null) {
            /* SQL_EXEC_ID absente : on retombe sur le sql_id, borne au debut
               de l'execution. Moins precis — deux executions du meme SQL se
               melangeraient — mais mieux que rien, et c'est dit dans la vue. */
            $rows = self::fetchAll($conn,
                "SELECT $cols
                 FROM   GV\$SESSION_LONGOPS lo
                 WHERE  lo.sql_id = :p_sql_id
                   AND  lo.last_update_time >= TO_DATE(:p_exec_start, 'YYYY-MM-DD HH24:MI:SS')
                   AND  lo.totalwork > 0
                 ORDER BY lo.start_time",
                [':p_sql_id' => $sqlId, ':p_exec_start' => $execStart]);
        }
        if ($rows === null) return [];

        $out = [];
        foreach ($rows as $r) {
            $sofar = (float)($r['SOFAR'] ?? 0);
            $total = (float)($r['TOTALWORK'] ?? 0);
            $out[] = [
                'inst_id'     => (int)($r['INST_ID'] ?? 0),
                'sid'         => (int)($r['SID'] ?? 0),
                'opname'      => (string)($r['OPNAME'] ?? ''),
                'target'      => (string)($r['TARGET'] ?? ''),
                'units'       => (string)($r['UNITS'] ?? ''),
                'sofar'       => $sofar,
                'totalwork'   => $total,
                'pct'         => ($total > 0) ? round($sofar * 100 / $total, 1) : 0.0,
                'start_time'  => (string)($r['START_TIME'] ?? ''),
                'last_update' => (string)($r['LAST_UPDATE_TIME'] ?? ''),
                'elapsed'     => (int)($r['ELAPSED_SECONDS'] ?? 0),
                'remaining'   => (int)($r['TIME_REMAINING'] ?? 0),
                'message'     => (string)($r['MESSAGE'] ?? ''),
            ];
        }
        return $out;
    }

    /**
     * Le plan ESTIME du curseur, pour un plan_hash_value donne.
     *
     * Meme strategie que OracleSqlDetailRepository::getPlanRows() — un seul
     * couple (inst_id, child_number) par PHV, le plus recent — mais borne a
     * un PHV, avec repli V$ si GV$ ne rend rien (droits, non-RAC).
     */
    public static function getEstimatedPlan($conn, string $sqlId, string $planHash): array
    {
        $cols = "p.id, p.parent_id, p.depth,
                       p.operation, NVL(p.options, '') AS options,
                       NVL(p.object_owner, '') AS object_owner,
                       NVL(p.object_name,  '') AS object_name,
                       p.cost, p.cardinality, p.bytes, p.time,
                       NVL(p.access_predicates, '') AS access_predicates,
                       NVL(p.filter_predicates, '') AS filter_predicates";
        $binds = [':p_sql_id' => $sqlId, ':p_phv' => $planHash];

        $rows = self::fetchAll($conn,
            "SELECT $cols
             FROM   GV\$SQL_PLAN p
             JOIN  (SELECT MAX(inst_id)      KEEP (DENSE_RANK LAST ORDER BY inst_id, child_number) AS inst_id,
                           MAX(child_number) KEEP (DENSE_RANK LAST ORDER BY inst_id, child_number) AS child_number
                    FROM   GV\$SQL_PLAN
                    WHERE  sql_id = :p_sql_id AND plan_hash_value = :p_phv) m
               ON   m.inst_id = p.inst_id AND m.child_number = p.child_number
             WHERE  p.sql_id = :p_sql_id AND p.plan_hash_value = :p_phv
             ORDER BY p.id", $binds);

        if (empty($rows)) {
            $rows = self::fetchAll($conn,
                "SELECT $cols
                 FROM   V\$SQL_PLAN p
                 JOIN  (SELECT MAX(child_number) AS child_number
                        FROM   V\$SQL_PLAN
                        WHERE  sql_id = :p_sql_id AND plan_hash_value = :p_phv) m
                   ON   m.child_number = p.child_number
                 WHERE  p.sql_id = :p_sql_id AND p.plan_hash_value = :p_phv
                 ORDER BY p.id", $binds);
        }
        if ($rows === null) return [];

        $out = [];
        foreach ($rows as $r) {
            $out[] = [
                'id'          => (int)($r['ID'] ?? 0),
                'pid'         => ($r['PARENT_ID'] !== null) ? (int)$r['PARENT_ID'] : null,
                'depth'       => (int)($r['DEPTH'] ?? 0),
                'op'          => (string)($r['OPERATION'] ?? ''),
                'opts'        => (string)($r['OPTIONS'] ?? ''),
                'owner'       => (string)($r['OBJECT_OWNER'] ?? ''),
                'obj'         => (string)($r['OBJECT_NAME'] ?? ''),
                'cost'        => ($r['COST'] !== null) ? (int)$r['COST'] : null,
                'est_rows'    => ($r['CARDINALITY'] !== null) ? (int)$r['CARDINALITY'] : null,
                'bytes'       => ($r['BYTES'] !== null) ? (int)$r['BYTES'] : null,
                'tms'         => ($r['TIME'] !== null) ? (int)$r['TIME'] : null,
                /* Ces trois-la n'existent que sous Real-Time SQL Monitoring.
                   null, pas 0 : la vue doit afficher « — », pas « aucune ». */
                'starts'      => null,
                'act_rows'    => null,
                'passes'      => null,
                'read_bytes'  => 0,
                'write_bytes' => 0,
                'mem'         => 0,
                'temp'        => 0,
                'active'      => false,
                'acc'         => (string)($r['ACCESS_PREDICATES'] ?? ''),
                'flt'         => (string)($r['FILTER_PREDICATES'] ?? ''),
            ];
        }
        return $out;
    }

    /**
     * Statistiques cumulees des sessions de l'execution.
     *
     * Une session dediee a une execution ne fait rien d'autre pendant
     * qu'elle tourne : le DELTA de ces compteurs entre deux releves EST la
     * consommation de l'execution. C'est ce qui donne des totaux honnetes
     * sans GV$SQL_MONITOR — le caller memorise le premier releve et publie
     * la difference.
     *
     * @return array<string,int> nom de statistique => valeur cumulee
     */
    public static function getSessionStatDelta($conn, string $sqlId, int $execId, string $execStart): array
    {
        $sql = "SELECT sn.name, SUM(st.value) AS value
                FROM   GV\$SESSTAT st
                JOIN   GV\$STATNAME sn
                  ON   sn.inst_id = st.inst_id AND sn.statistic# = st.statistic#
                WHERE  sn.name IN ('session logical reads',
                                   'physical reads',
                                   'physical read total bytes',
                                   'physical write total bytes',
                                   'CPU used by this session',
                                   'sorts (disk)',
                                   'user I/O wait time')
                  AND  (st.inst_id, st.sid) IN (
                         SELECT s.inst_id, s.sid
                         FROM   GV\$SESSION s
                         WHERE  s.sql_id         = :p_sql_id
                           AND  s.sql_exec_id    = :p_exec_id
                           AND  s.sql_exec_start = TO_DATE(:p_exec_start, 'YYYY-MM-DD HH24:MI:SS')
                           AND  s.status         = 'ACTIVE')
                GROUP BY sn.name";

        $rows = self::fetchAll($conn, $sql, [
            ':p_sql_id'     => $sqlId,
            ':p_exec_id'    => $execId,
            ':p_exec_start' => $execStart,
        ]);
        if ($rows === null) return [];

        $out = [];
        foreach ($rows as $r) {
            $out[(string)($r['NAME'] ?? '')] = (int)($r['VALUE'] ?? 0);
        }
        return $out;
    }

    // ═══════════════════════════════════════════════════════════════════════
    //  Helpers prives
    // ═══════════════════════════════════════════════════════════════════════

    /** Borne une limite demandee par le caller. Interpolee, donc castee. */
    private static function cap(int $limit): int
    {
        return max(1, min(self::MAX_ROWS, $limit));
    }

    /**
     * Execute et ramene toutes les lignes.
     *
     * Rend NULL si le PARSE ou l'EXECUTE echoue, [] si la requete ne ramene
     * rien. Cette distinction est ce qui permet aux methodes publiques de
     * choisir un repli sur echec sans le declencher sur un resultat vide
     * legitime — sans elle, une base sans longops relancerait la requete de
     * secours a chaque poll.
     *
     * Les binds sont pris par REFERENCE dans le tableau : oci_bind_by_name
     * lie une reference, et une variable de boucle reutilisee ferait pointer
     * tous les binds sur la derniere valeur.
     */
    private static function fetchAll($conn, string $sql, array $binds): ?array
    {
        $stm = @oci_parse($conn, $sql);
        if (!$stm) return null;

        foreach ($binds as $name => $value) {
            oci_bind_by_name($stm, $name, $binds[$name], -1,
                is_int($value) ? SQLT_INT : SQLT_CHR);
        }

        if (!@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            oci_free_statement($stm);
            return null;
        }

        $rows = [];
        while ($r = oci_fetch_assoc($stm)) {
            $rows[] = $r;
        }
        oci_free_statement($stm);
        return $rows;
    }
}
