<?php
/**
 * Demo Oracle — DemoOracleSessionsRepository.
 *
 * Portage de modules/oracle/lib/OracleSessionsRepository.php : requetes de LECTURE
 * uniquement. Les methodes d'action ont ete RETIREES a la
 * source, pas desactivees :
 *
 *   killSession
 *
 * Une action absente du serveur ne se contourne pas.
 *
 * 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('DemoOracleSessionsRepository')) return;

class DemoOracleSessionsRepository
{
    /**
     * Sessions actives du cluster (status='ACTIVE', type='USER').
     * Joint gv$sqlarea pour récupérer les 120 premiers caractères du SQL.
     *
     * @return list<array{
     *   inst_id:int, sid:int, serial_num:int, username:string, schemaname:string,
     *   sql_id:string|null,
     *   sql_start:string|null, duration_sec:int,
     *   wait_class:string, wait_event:string|null, machine:string|null,
     *   program:string|null, sql_preview:string|null
     * }>
     */
    public function fetchActiveSessions($conn): array
    {
        $sql = "SELECT s.inst_id,
                       s.sid,
                       s.serial# AS serial_num,
                       s.username,
                       s.schemaname,
                       s.sql_id,
                       TO_CHAR(NVL(s.sql_exec_start, SYSDATE - s.last_call_et/86400),
                               'YYYY-MM-DD HH24:MI:SS') AS sql_start,
                       s.last_call_et AS duration_sec,
                       CASE WHEN s.state = 'ON CPU'
                            THEN 'CPU'
                            ELSE NVL(s.wait_class, 'Other')
                       END AS wait_class,
                       s.event AS wait_event,
                       s.machine,
                       s.program,
                       SUBSTR(sa.sql_text, 1, 120) AS sql_preview
                FROM   gv\$session s
                LEFT JOIN gv\$sqlarea sa
                       ON sa.inst_id = s.inst_id
                      AND sa.sql_id  = s.sql_id
                WHERE  s.status   = 'ACTIVE'
                  AND  s.type     = 'USER'
                  AND  s.username IS NOT NULL
                ORDER BY s.last_call_et DESC";

        $rows = [];
        $stm  = oci_parse($conn, $sql);
        if (!$stm) return $rows;
        if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) {
                $rows[] = [
                    'inst_id'      => (int)$r['INST_ID'],
                    'sid'          => (int)$r['SID'],
                    'serial_num'   => (int)$r['SERIAL_NUM'],
                    'username'     => (string)($r['USERNAME']   ?? ''),
                    'schemaname'   => (string)($r['SCHEMANAME'] ?? ''),
                    'sql_id'            => $r['SQL_ID']            !== null ? (string)$r['SQL_ID']            : null,
                    'sql_start'    => $r['SQL_START']  !== null ? (string)$r['SQL_START']  : null,
                    'duration_sec' => (int)($r['DURATION_SEC'] ?? 0),
                    'wait_class'   => (string)($r['WAIT_CLASS'] ?? 'Other'),
                    'wait_event'   => $r['WAIT_EVENT']  !== null ? (string)$r['WAIT_EVENT']  : null,
                    'machine'      => $r['MACHINE']     !== null ? (string)$r['MACHINE']     : null,
                    'program'      => $r['PROGRAM']     !== null ? (string)$r['PROGRAM']     : null,
                    'sql_preview'  => $r['SQL_PREVIEW'] !== null ? (string)$r['SQL_PREVIEW'] : null,
                ];
            }
        }
        oci_free_statement($stm);
        return $rows;
    }

    /**
     * Long operations en cours (gv$session_longops) — non terminées.
     * Estimation de fin = last_update_time + time_remaining.
     *
     * @return list<array{
     *   inst_id:int, sid:int, serial_num:int, username:string,
     *   sql_id:string|null, opname:string, target:string,
     *   sofar:float, totalwork:float,
     *   start_time:string, last_update_time:string,
     *   elapsed_seconds:int, time_remaining:int, estimated_end:string,
     *   message:string|null
     * }>
     */
    public function fetchLongOps($conn): array
    {
        $sql = "SELECT lo.inst_id,
                       lo.sid,
                       lo.serial# AS serial_num,
                       lo.username,
                       lo.sql_id,
                       lo.opname,
                       lo.target,
                       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,
                       TO_CHAR(lo.last_update_time + lo.time_remaining/86400,
                               'YYYY-MM-DD HH24:MI:SS') AS estimated_end,
                       lo.message
                FROM   gv\$session_longops lo
                WHERE  lo.sofar < lo.totalwork
                  AND  lo.time_remaining > 0
                ORDER BY lo.time_remaining";

        $rows = [];
        $stm  = oci_parse($conn, $sql);
        if (!$stm) return $rows;
        if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) {
                $rows[] = [
                    'inst_id'          => (int)$r['INST_ID'],
                    'sid'              => (int)$r['SID'],
                    'serial_num'       => (int)$r['SERIAL_NUM'],
                    'username'         => (string)($r['USERNAME'] ?? ''),
                    'sql_id'           => $r['SQL_ID'] !== null ? (string)$r['SQL_ID'] : null,
                    'opname'           => (string)($r['OPNAME']  ?? ''),
                    'target'           => (string)($r['TARGET']  ?? ''),
                    'sofar'            => (float)$r['SOFAR'],
                    'totalwork'        => (float)$r['TOTALWORK'],
                    'start_time'       => (string)$r['START_TIME'],
                    'last_update_time' => (string)$r['LAST_UPDATE_TIME'],
                    'elapsed_seconds'  => (int)$r['ELAPSED_SECONDS'],
                    'time_remaining'   => (int)$r['TIME_REMAINING'],
                    'estimated_end'    => (string)$r['ESTIMATED_END'],
                    'message'          => $r['MESSAGE'] !== null ? (string)$r['MESSAGE'] : null,
                ];
            }
        }
        oci_free_statement($stm);
        return $rows;
    }

    /**
     * Sessions impliquées dans un blocage (bloqueurs OU bloquées).
     * RAC-aware (GV$). On embarque les colonnes nécessaires à construire l'arbre
     * blocking_instance/blocking_session côté front + un aperçu du SQL.
     *
     * @return list<array{
     *   inst_id:int, sid:int, serial_num:int,
     *   username:string, schemaname:string, osuser:string,
     *   machine:string|null, program:string|null, module:string|null,
     *   blocking_instance:?int, blocking_session:?int,
     *   event:string|null, wait_class:string, seconds_in_wait:int,
     *   state:string|null, status:string,
     *   sql_id:string|null, sql_preview:string|null,
     *   holds_blocking_lock:int
     * }>
     */
    public function fetchBlockingSessions($conn): array
    {
        $sql = "SELECT s.inst_id,
                       s.sid,
                       s.serial# AS serial_num,
                       NVL(s.username,   '?')  AS username,
                       NVL(s.schemaname, '?')  AS schemaname,
                       NVL(s.osuser,     '?')  AS osuser,
                       s.machine, s.program, s.module,
                       s.blocking_instance, s.blocking_session,
                       s.event,
                       CASE WHEN s.state = 'ON CPU'
                            THEN 'CPU'
                            ELSE NVL(s.wait_class, 'Other')
                       END AS wait_class,
                       NVL(s.seconds_in_wait, 0) AS seconds_in_wait,
                       s.state, s.status,
                       s.sql_id,
                       SUBSTR(sa.sql_text, 1, 200) AS sql_preview,
                       (SELECT COUNT(*) FROM gv\$lock l
                        WHERE l.inst_id = s.inst_id AND l.sid = s.sid
                          AND l.block > 0) AS holds_blocking_lock
                FROM   gv\$session s
                LEFT JOIN gv\$sqlarea sa
                       ON sa.inst_id = s.inst_id
                      AND sa.sql_id  = s.sql_id
                WHERE  s.blocking_session IS NOT NULL
                   OR  EXISTS (
                       SELECT 1 FROM gv\$session b
                       WHERE b.blocking_instance = s.inst_id
                         AND b.blocking_session  = s.sid
                   )
                ORDER BY s.inst_id, s.sid";

        $rows = [];
        $stm  = oci_parse($conn, $sql);
        if (!$stm) return $rows;
        if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) {
                $rows[] = [
                    'inst_id'             => (int)$r['INST_ID'],
                    'sid'                 => (int)$r['SID'],
                    'serial_num'          => (int)$r['SERIAL_NUM'],
                    'username'            => (string)($r['USERNAME']   ?? ''),
                    'schemaname'          => (string)($r['SCHEMANAME'] ?? ''),
                    'osuser'              => (string)($r['OSUSER']     ?? ''),
                    'machine'             => $r['MACHINE'] !== null ? (string)$r['MACHINE'] : null,
                    'program'             => $r['PROGRAM'] !== null ? (string)$r['PROGRAM'] : null,
                    'module'              => $r['MODULE']  !== null ? (string)$r['MODULE']  : null,
                    'blocking_instance'   => $r['BLOCKING_INSTANCE'] !== null ? (int)$r['BLOCKING_INSTANCE'] : null,
                    'blocking_session'    => $r['BLOCKING_SESSION']  !== null ? (int)$r['BLOCKING_SESSION']  : null,
                    'event'               => $r['EVENT']  !== null ? (string)$r['EVENT']  : null,
                    'wait_class'          => (string)($r['WAIT_CLASS'] ?? 'Other'),
                    'seconds_in_wait'     => (int)($r['SECONDS_IN_WAIT'] ?? 0),
                    'state'               => $r['STATE']  !== null ? (string)$r['STATE']  : null,
                    'status'              => (string)($r['STATUS']     ?? ''),
                    'sql_id'              => $r['SQL_ID']      !== null ? (string)$r['SQL_ID']      : null,
                    'sql_preview'         => $r['SQL_PREVIEW'] !== null ? (string)$r['SQL_PREVIEW'] : null,
                    'holds_blocking_lock' => (int)($r['HOLDS_BLOCKING_LOCK'] ?? 0),
                ];
            }
        }
        oci_free_statement($stm);
        return $rows;
    }

    /*
     * fetchRunningSqlMonitor() a ete RETIREE.
     *
     * Elle interrogeait GV$SQL_MONITOR — donc le Tuning Pack — sans avoir
     * le moindre appelant, pendant que l'en-tete de _report_sql_detail.php
     * affirmait le contraire. Le module doit pouvoir soutenir sans reserve
     * qu'aucune vue licenciee n'est lue : la demonstration reconstruit son
     * SQL Monitor a partir de GV$SESSION (cf. DemoOracleSqlMonitorRepository).
     */
}
