<?php
/**
 * Demo Oracle — DemoOracleSqlDetailRepository.
 *
 * Portage de modules/oracle/lib/OracleSqlDetailRepository.php : requetes de
 * LECTURE uniquement, comme l'original — ce repository n'a jamais rien ecrit.
 *
 * Sept methodes ne sont pas reprises, non parce qu'elles ecrivent, mais parce
 * qu'elles n'alimentent que des onglets absents de la demonstration :
 *
 *   getSqlMonitorList                       onglet « SQL Monitoring »
 *   resolveIndexTable, getTableStats,       onglet « Objets lies »
 *   getColumnStats, getIndexes, getIndexColumns
 *
 * Ne pas les porter est ce qui rend le retrait reel : la page ne se contente
 * pas de masquer ces donnees, elle ne les demande pas a Oracle. C'est aussi ce
 * qui la rend nettement plus legere — l'onglet « Objets lies » declenchait
 * jusqu'a cinq requetes du dictionnaire par table du plan.
 *
 * getLicenseInfo n'est pas reprise non plus : DemoOracleAshSource porte deja la
 * lecture de CONTROL_MANAGEMENT_PACK_ACCESS pour tout le module.
 *
 * Safe to include multiple times (guard sur class_exists).
 */
if (class_exists('DemoOracleSqlDetailRepository')) return;

class DemoOracleSqlDetailRepository
{

    public static function getPlanRows($conn, string $sqlId, string $planCols): array
    {
        // GV$ (RAC-aware) : un curseur peut n'exister que sur une autre instance.
        // On retient, par plan_hash_value, un seul couple (inst_id, child_number)
        // — le plus recent — pour ne pas melanger les lignes de plan entre instances.
        $sql = "SELECT $planCols FROM GV\$SQL_PLAN p
         JOIN (SELECT plan_hash_value,
                      MAX(inst_id)      KEEP (DENSE_RANK LAST ORDER BY inst_id, child_number) inst_id,
                      MAX(child_number) KEEP (DENSE_RANK LAST ORDER BY inst_id, child_number) child_number
               FROM GV\$SQL_PLAN WHERE sql_id = :sql_id GROUP BY plan_hash_value) m
           ON  m.plan_hash_value = p.plan_hash_value
          AND  m.inst_id         = p.inst_id
          AND  m.child_number    = p.child_number
         WHERE p.sql_id = :sql_id
         ORDER BY p.plan_hash_value, p.id";
        $rows = self::fetchPlanRows($conn, $sql, $sqlId);
        if (!empty($rows)) return $rows;

        // Fallback V$ (instance locale) si GV$ ne renvoie rien (droits/non-RAC).
        $sqlV = "SELECT $planCols FROM V\$SQL_PLAN p
         JOIN (SELECT plan_hash_value, MAX(child_number) max_child
               FROM V\$SQL_PLAN WHERE sql_id = :sql_id GROUP BY plan_hash_value) m
           ON m.plan_hash_value = p.plan_hash_value AND m.max_child = p.child_number
         WHERE p.sql_id = :sql_id
         ORDER BY p.plan_hash_value, p.id";
        return self::fetchPlanRows($conn, $sqlV, $sqlId);
    }

    public static function getAwrPlanRows($conn, string $sqlId, string $planCols): array
    {
        $sql = "SELECT $planCols FROM DBA_HIST_SQL_PLAN p
         WHERE p.sql_id = :sql_id AND p.dbid = (SELECT dbid FROM V\$DATABASE)
         ORDER BY p.plan_hash_value, p.id";
        return self::fetchPlanRows($conn, $sql, $sqlId);
    }

    public static function getOtherXml($conn, string $sqlId, string $phv, string $source): string
    {
        if ($source === 'awr') {
            return self::fetchOtherXml($conn, 'DBA_HIST_SQL_PLAN',
                ' AND dbid = (SELECT dbid FROM V$DATABASE)', $sqlId, $phv);
        }
        // Mémoire : GV$ (RAC) puis fallback V$ (instance locale, droits/non-RAC).
        $x = self::fetchOtherXml($conn, "GV\$SQL_PLAN", '', $sqlId, $phv);
        if ($x !== '') return $x;
        return self::fetchOtherXml($conn, "V\$SQL_PLAN", '', $sqlId, $phv);
    }

    /** Lit other_xml (CLOB) du plan id=1 dans la vue donnée. '' si absent. */
    private static function fetchOtherXml($conn, string $view, string $extra, string $sqlId, string $phv): string
    {
        $sql = "SELECT other_xml FROM {$view}
                WHERE sql_id = :sid AND plan_hash_value = :phv AND id = 1
                AND other_xml IS NOT NULL{$extra}
                AND ROWNUM = 1";
        $stm = @oci_parse($conn, $sql);
        if (!$stm) return '';
        $phvStr = (string)$phv;
        oci_bind_by_name($stm, ':sid', $sqlId, -1, SQLT_CHR);
        oci_bind_by_name($stm, ':phv', $phvStr, -1, SQLT_CHR);
        if (!@oci_execute($stm, OCI_NO_AUTO_COMMIT)) { oci_free_statement($stm); return ''; }
        $row = oci_fetch_assoc($stm);
        if (!$row || !isset($row['OTHER_XML']) || $row['OTHER_XML'] === null) {
            oci_free_statement($stm);
            return '';
        }
        $lob = $row['OTHER_XML'];
        $content = '';
        if (is_object($lob) && method_exists($lob, 'load')) {
            $content = $lob->load() ?: '';
            if (method_exists($lob, 'free')) $lob->free();
        } else {
            $content = (string)$lob;
        }
        oci_free_statement($stm);
        return $content;
    }

    public static function getEfficiencyFromMemory($conn, string $sqlId): array
    {
        // GV$ (RAC) puis fallback V$ (instance locale, droits/non-RAC).
        $rows = self::effMemory($conn, "GV\$SQL", $sqlId);
        if (!empty($rows)) return $rows;
        return self::effMemory($conn, "V\$SQL", $sqlId);
    }

    private static function effMemory($conn, string $view, string $sqlId): array
    {
        $stm = @oci_parse($conn,
            "SELECT plan_hash_value phv,
                    SUM(executions) execs, SUM(elapsed_time)/1e6 elapsed_s,
                    SUM(cpu_time)/1e6 cpu_s, SUM(buffer_gets) buf_gets,
                    SUM(disk_reads) disk_reads, SUM(rows_processed) rows_proc,
                    SUM(parse_calls) parse_calls
             FROM {$view} WHERE sql_id = :sql_id GROUP BY plan_hash_value");
        if (!$stm) return [];
        oci_bind_by_name($stm, ':sql_id', $sqlId, -1, SQLT_CHR);
        if (!@oci_execute($stm, OCI_NO_AUTO_COMMIT)) { oci_free_statement($stm); return []; }
        $rows = [];
        while ($r = oci_fetch_assoc($stm)) {
            $ex = max(1, (int)($r['EXECS'] ?? 1));
            $rows[] = [
                'phv'         => (string)($r['PHV'] ?? ''),
                'execs'       => (int)($r['EXECS'] ?? 0),
                'elapsed_s'   => round((float)($r['ELAPSED_S']  ?? 0), 3),
                'cpu_s'       => round((float)($r['CPU_S']      ?? 0), 3),
                'buf_gets'    => (int)($r['BUF_GETS']   ?? 0),
                'disk_reads'  => (int)($r['DISK_READS'] ?? 0),
                'rows_proc'   => (int)($r['ROWS_PROC']  ?? 0),
                'parse_calls' => (int)($r['PARSE_CALLS']?? 0),
                'etime_exec'  => round((float)($r['ELAPSED_S'] ?? 0) / $ex * 1000, 2),
                'cpu_exec'    => round((float)($r['CPU_S']     ?? 0) / $ex * 1000, 2),
                'bufs_exec'   => round((int)($r['BUF_GETS']   ?? 0) / $ex),
            ];
        }
        oci_free_statement($stm);
        return $rows;
    }

    public static function getEfficiencyFromAwr($conn, string $sqlId): array
    {
        $stm = @oci_parse($conn,
            "SELECT plan_hash_value phv,
                    SUM(executions_delta) execs, SUM(elapsed_time_delta)/1e6 elapsed_s,
                    SUM(cpu_time_delta)/1e6 cpu_s, SUM(buffer_gets_delta) buf_gets,
                    SUM(disk_reads_delta) disk_reads, SUM(rows_processed_delta) rows_proc,
                    SUM(parse_calls_delta) parse_calls
             FROM DBA_HIST_SQLSTAT
             WHERE sql_id = :sql_id AND dbid = (SELECT dbid FROM V\$DATABASE)
             GROUP BY plan_hash_value");
        if (!$stm) return [];
        oci_bind_by_name($stm, ':sql_id', $sqlId, -1, SQLT_CHR);
        return self::fetchEfficiencyRows($stm);
    }

    public static function getEfficiencyFromBashSnapper($conn, string $sqlId): array
    {
        $stm = @oci_parse($conn,
            "SELECT plan_hash_value phv,
                    SUM(executions_delta) execs, SUM(elapsed_time_delta)/1e6 elapsed_s,
                    SUM(cpu_time_delta)/1e6 cpu_s, SUM(buffer_gets_delta) buf_gets,
                    SUM(disk_reads_delta) disk_reads, SUM(rows_processed_delta) rows_proc,
                    SUM(parse_calls_delta) parse_calls
             FROM SW\$BASH_HIST_SQLSTAT
             WHERE sql_id = :sql_id
             GROUP BY plan_hash_value");
        if (!$stm) return [];
        oci_bind_by_name($stm, ':sql_id', $sqlId, -1, SQLT_CHR);
        return self::fetchEfficiencyRows($stm);
    }

    public static function getSqlTextFromMemory($conn, string $sqlId): string
    {
        // GV$ (RAC) puis fallback V$ (instance locale, droits/non-RAC).
        $t = self::sqlTextMemory($conn, "GV\$SQL", $sqlId);
        if ($t !== '') return $t;
        return self::sqlTextMemory($conn, "V\$SQL", $sqlId);
    }

    private static function sqlTextMemory($conn, string $view, string $sqlId): string
    {
        $stm = @oci_parse($conn,
            "SELECT CASE WHEN sql_fulltext IS NOT NULL
                         THEN DBMS_LOB.SUBSTR(sql_fulltext, 32767, 1) ELSE '' END sql_text
             FROM {$view} WHERE sql_id = :sql_id AND ROWNUM = 1");
        if (!$stm) return '';
        oci_bind_by_name($stm, ':sql_id', $sqlId, -1, SQLT_CHR);
        if (!@oci_execute($stm, OCI_NO_AUTO_COMMIT)) { oci_free_statement($stm); return ''; }
        $text = '';
        if ($r = oci_fetch_assoc($stm)) {
            $text = (string)($r['SQL_TEXT'] ?? '');
        }
        oci_free_statement($stm);
        return $text;
    }

    public static function getSqlTextFromAwr($conn, string $sqlId): string
    {
        $stm = @oci_parse($conn,
            "SELECT CASE WHEN sql_text IS NOT NULL
                         THEN DBMS_LOB.SUBSTR(sql_text, 32767, 1) ELSE '' END sql_text
             FROM DBA_HIST_SQLTEXT WHERE sql_id = :sql_id AND ROWNUM = 1");
        if (!$stm) return '';
        oci_bind_by_name($stm, ':sql_id', $sqlId, -1, SQLT_CHR);
        if (!@oci_execute($stm, OCI_NO_AUTO_COMMIT)) { oci_free_statement($stm); return ''; }
        $text = '';
        if ($r = oci_fetch_assoc($stm)) {
            $text = (string)($r['SQL_TEXT'] ?? '');
        }
        oci_free_statement($stm);
        return $text;
    }

    public static function getLoadHistoryFromAwr($conn, string $sqlId): array
    {
        $stm = @oci_parse($conn,
            "SELECT TO_CHAR(sn.begin_interval_time,'YYYY-MM-DD HH24:MI') snap_time,
                    st.plan_hash_value,
                    NVL(st.executions_delta,     0) exec_d,
                    NVL(st.elapsed_time_delta,   0) ela_d,
                    NVL(st.cpu_time_delta,        0) cpu_d,
                    NVL(st.iowait_delta,          0) iow_d,
                    NVL(st.clwait_delta,          0) clw_d,
                    NVL(st.apwait_delta,          0) apw_d,
                    NVL(st.ccwait_delta,          0) ccw_d,
                    NVL(st.buffer_gets_delta,     0) lios_d,
                    NVL(st.disk_reads_delta,      0) pio_d,
                    NVL(st.rows_processed_delta,  0) rows_d,
                    NVL(st.parse_calls_delta,     0) prs_d,
                    NVL(st.fetches_delta,         0) fet_d
             FROM dba_hist_sqlstat st
             JOIN dba_hist_snapshot sn
                  ON sn.snap_id = st.snap_id AND sn.dbid = st.dbid
             WHERE st.sql_id = :sql_id
             AND   st.dbid   = (SELECT dbid FROM V\$DATABASE)
             AND   NVL(st.executions_delta, 0) > 0
             ORDER BY sn.snap_id, st.plan_hash_value");
        if (!$stm) return [];
        oci_bind_by_name($stm, ':sql_id', $sqlId, -1, SQLT_CHR);
        return self::fetchLoadHistRows($stm);
    }

    public static function getLoadHistoryFromBashSnapper($conn, string $sqlId): array
    {
        $stm = @oci_parse($conn,
            "SELECT TO_CHAR(TRUNC(h.sample_time, 'HH24'), 'YYYY-MM-DD HH24:MI') snap_time,
                    h.sql_plan_hash_value plan_hash_value,
                    COUNT(*) exec_d,
                    COUNT(*) * 1e6 ela_d,
                    SUM(CASE WHEN h.session_state = 'ON CPU' THEN 1 ELSE 0 END) * 1e6 cpu_d,
                    SUM(CASE WHEN h.wait_class = 'User I/O' OR h.wait_class = 'System I/O' THEN 1 ELSE 0 END) * 1e6 iow_d,
                    SUM(CASE WHEN h.wait_class = 'Cluster' THEN 1 ELSE 0 END) * 1e6 clw_d,
                    SUM(CASE WHEN h.wait_class = 'Application' THEN 1 ELSE 0 END) * 1e6 apw_d,
                    SUM(CASE WHEN h.wait_class = 'Concurrency' THEN 1 ELSE 0 END) * 1e6 ccw_d,
                    0 lios_d, 0 pio_d, 0 rows_d, 0 prs_d, 0 fet_d
             FROM SW\$BASH_HIST_ACTIVE_SESS_HISTORY h
             WHERE h.sql_id = :sql_id
             GROUP BY TRUNC(h.sample_time, 'HH24'), h.sql_plan_hash_value
             HAVING COUNT(*) > 0
             ORDER BY 1, 2");
        if (!$stm) return [];
        oci_bind_by_name($stm, ':sql_id', $sqlId, -1, SQLT_CHR);
        return self::fetchLoadHistRows($stm);
    }

    // ── Private helpers ──────────────────────────────────────────────────

    private static function fetchPlanRows($conn, string $sql, string $sqlId): array
    {
        $stm = @oci_parse($conn, $sql);
        if (!$stm) return [];
        oci_bind_by_name($stm, ':sql_id', $sqlId, -1, SQLT_CHR);
        if (!@oci_execute($stm, OCI_NO_AUTO_COMMIT)) { oci_free_statement($stm); return []; }
        $rows = [];
        while ($r = oci_fetch_assoc($stm)) {
            $rows[] = [
                'phv'   => (string)($r['PLAN_HASH_VALUE'] ?? ''),
                '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'] ?? ''),
                'alias' => (string)($r['OBJECT_ALIAS'] ?? ''),
                'cost'  => ($r['COST'] !== null)        ? (int)$r['COST']        : null,
                'cpu'   => ($r['CPU_COST'] !== null)    ? (int)$r['CPU_COST']    : null,
                'ioc'   => ($r['IO_COST'] !== null)     ? (int)$r['IO_COST']     : null,
                'rows'  => ($r['CARDINALITY'] !== null)  ? (int)$r['CARDINALITY'] : null,
                'bytes' => ($r['BYTES'] !== null)        ? (int)$r['BYTES']       : null,
                'tms'   => ($r['TIME'] !== null)         ? (int)$r['TIME']        : null,
                'acc'   => (string)($r['ACCESS_PREDICATES'] ?? ''),
                'flt'   => (string)($r['FILTER_PREDICATES'] ?? ''),
                'proj'  => (string)($r['PROJECTION'] ?? ''),
                'pst'   => (string)($r['PARTITION_START'] ?? ''),
                'psp'   => (string)($r['PARTITION_STOP'] ?? ''),
            ];
        }
        oci_free_statement($stm);
        return $rows;
    }

    private static function fetchEfficiencyRows($stm): array
    {
        if (!@oci_execute($stm, OCI_NO_AUTO_COMMIT)) { oci_free_statement($stm); return []; }
        $rows = [];
        while ($r = oci_fetch_assoc($stm)) {
            $ex = max(1, (int)($r['EXECS'] ?? 1));
            $rows[] = [
                'phv'         => (string)($r['PHV'] ?? ''),
                'execs'       => (int)($r['EXECS'] ?? 0),
                'elapsed_s'   => round((float)($r['ELAPSED_S']  ?? 0), 3),
                'cpu_s'       => round((float)($r['CPU_S']      ?? 0), 3),
                'buf_gets'    => (int)($r['BUF_GETS']   ?? 0),
                'disk_reads'  => (int)($r['DISK_READS'] ?? 0),
                'rows_proc'   => (int)($r['ROWS_PROC']  ?? 0),
                'parse_calls' => (int)($r['PARSE_CALLS']?? 0),
                'etime_exec'  => round((float)($r['ELAPSED_S'] ?? 0) / $ex * 1000, 2),
                'cpu_exec'    => round((float)($r['CPU_S']     ?? 0) / $ex * 1000, 2),
                'bufs_exec'   => round((int)($r['BUF_GETS']   ?? 0) / $ex),
            ];
        }
        oci_free_statement($stm);
        return $rows;
    }

    private static function fetchLoadHistRows($stm): array
    {
        if (!@oci_execute($stm, OCI_NO_AUTO_COMMIT)) { oci_free_statement($stm); return []; }
        $rows = [];
        while ($r = oci_fetch_assoc($stm)) {
            $rows[] = [
                'snap' => (string)$r['SNAP_TIME'],
                'phv'  => (string)$r['PLAN_HASH_VALUE'],
                'exec' => (int)$r['EXEC_D'],
                'ela'  => (float)$r['ELA_D'],
                'cpu'  => (float)$r['CPU_D'],
                'iow'  => (float)$r['IOW_D'],
                'clw'  => (float)$r['CLW_D'],
                'apw'  => (float)$r['APW_D'],
                'ccw'  => (float)$r['CCW_D'],
                'lios' => (int)$r['LIOS_D'],
                'pio'  => (int)$r['PIO_D'],
                'rows' => (int)$r['ROWS_D'],
                'prs'  => (int)$r['PRS_D'],
                'fet'  => (int)$r['FET_D'],
            ];
        }
        oci_free_statement($stm);
        return $rows;
    }
}
