<?php
/**
 * Demo Oracle — la requete du rapport ASH detaille.
 *
 * Extraite du controleur : dans ce module, aucun appel OCI ne vit hors de
 * lib/. Le module complet garde ce SQL dans _report_ash_detail.php ; le
 * deplacer ici met la requete dans le perimetre que le banc de test audite.
 *
 * Deux variantes, selon la source resolue par DemoOracleAshSource :
 * DBA_HIST_ACTIVE_SESS_HISTORY (Diagnostic Pack) ou
 * SW$BASH_HIST_ACTIVE_SESS_HISTORY (repli sw$bash_snapper). Elles rendent
 * exactement les memes colonnes, si bien que le reste de la chaine — le
 * decoupage en tranches, le graphique, les Top N — ne sait pas laquelle a
 * repondu.
 *
 * L'ERREUR OCI REMONTE. La convention du module veut qu'une lecture rende un
 * tableau vide plutot que false, et elle est conservee — mais un tableau vide
 * disait deux choses a la fois : « aucun echantillon » et « la requete a
 * echoue ». Une table invisible, un synonyme absent, un privilege manquant
 * devenaient une page vide et muette, impossible a diagnostiquer depuis
 * l'ecran. $ociError, passe par reference, distingue les deux — meme motif
 * que demo_oracle_oci_open().
 *
 * Safe to include multiple times (guard sur class_exists).
 */
if (class_exists('DemoOracleAshDetailRepository')) return;

class DemoOracleAshDetailRepository
{
    /**
     * Une tranche d'echantillons ASH, agregee par intervalle et par dimension.
     *
     * @param string     $source   'diag_pack' ou 'bash_snapper'
     * @param array|null $ociError renseigne en cas d'echec : ['code','message'].
     *                             Reste null si la requete a abouti, meme sans
     *                             ramener une seule ligne.
     * @return array<array<string, mixed>> vide en cas d'echec OCI
     */
    public static function fetchRows(
        $conn,
        string $source,
        string $dtBegin,
        string $dtEnd,
        int    $bucketSecs,
        ?array &$ociError = null
    ): array {
        $ociError = null;
        /* L'instant de reference des intervalles : l'epoque Unix, pour que
           deux tranches consecutives s'alignent sur la meme grille. */
        $bucketExpr =
            "TO_DATE('1970-01-01', 'YYYY-MM-DD') "
            . "+ FLOOR((CAST(se.sample_time AS DATE) - TO_DATE('1970-01-01', 'YYYY-MM-DD')) "
            .          "* 86400 / :bucket_secs) "
            . "* :bucket_secs / 86400";

        $sql = $source === 'bash_snapper'
            ? "SELECT TO_CHAR($bucketExpr, 'YYYY-MM-DD HH24:MI:SS') AS minute_bucket,
            CASE WHEN se.session_state = 'ON CPU' THEN 'CPU'
            ELSE NVL(se.wait_class, 'Other')
            END AS category,
            NVL(se.sql_id, '')                            AS sql_id,
            TO_CHAR(NVL(se.sql_plan_hash_value, 0))      AS phv,
            TO_CHAR(NVL(se.force_matching_signature, 0)) AS fms,
            NVL(se.module, '')                            AS module,
            NVL(se.action, '')                            AS action,
            CASE WHEN se.session_state = 'ON CPU' THEN 'CPU'
            ELSE NVL(se.event, 'Other')
            END AS wait_event,
            NVL(sv.name, '')                                AS service_name,
            NVL(se.machine, '')                             AS machine,
            NVL(obj.object_name, '')                        AS object_name,
            NVL(obj.object_type, '')                        AS object_type,
            NVL(pe.object_name, '')                         AS plsql_entry,
            NVL(po.object_name, '')                         AS plsql_object,
            COUNT(*) AS cnt
            FROM   SW\$BASH_HIST_ACTIVE_SESS_HISTORY se
            LEFT JOIN V\$SERVICES sv
            ON sv.name_hash = se.service_hash
            LEFT JOIN DBA_OBJECTS obj
            ON obj.object_id = se.current_obj#
            AND se.current_obj# > 0
            LEFT JOIN DBA_OBJECTS pe
            ON pe.object_id = se.plsql_entry_object_id
            AND se.plsql_entry_object_id > 0
            LEFT JOIN DBA_OBJECTS po
            ON po.object_id = se.plsql_object_id
            AND se.plsql_object_id > 0
            WHERE  se.session_type != 'BACKGROUND'
            AND  se.sample_time >= TO_DATE(:dt_begin, 'YYYY-MM-DD HH24:MI')
            AND  se.sample_time <  TO_DATE(:dt_end,   'YYYY-MM-DD HH24:MI')
            GROUP  BY $bucketExpr,
            CASE WHEN se.session_state = 'ON CPU' THEN 'CPU'
            ELSE NVL(se.wait_class, 'Other')
            END,
            se.sql_id, se.sql_plan_hash_value, se.force_matching_signature,
            se.module, se.action,
            CASE WHEN se.session_state = 'ON CPU' THEN 'CPU'
            ELSE NVL(se.event, 'Other')
            END,
            sv.name,
            se.machine,
            obj.object_name, obj.object_type,
            pe.object_name,
            po.object_name
            ORDER  BY 1, 2"
            : "SELECT TO_CHAR($bucketExpr, 'YYYY-MM-DD HH24:MI:SS') AS minute_bucket,
            CASE WHEN se.session_state = 'ON CPU' THEN 'CPU'
            ELSE NVL(se.wait_class, 'Other')
            END AS category,
            NVL(se.sql_id, '')                            AS sql_id,
            TO_CHAR(NVL(se.sql_plan_hash_value, 0))      AS phv,
            TO_CHAR(NVL(se.force_matching_signature, 0)) AS fms,
            NVL(se.module, '')                            AS module,
            NVL(se.action, '')                            AS action,
            CASE WHEN se.session_state = 'ON CPU' THEN 'CPU'
            ELSE NVL(se.event, 'Other')
            END AS wait_event,
            NVL(sn.service_name, '')                        AS service_name,
            NVL(se.machine, '')                             AS machine,
            NVL(obj.object_name, '')                        AS object_name,
            NVL(obj.object_type, '')                        AS object_type,
            NVL(pe.object_name, '')                         AS plsql_entry,
            NVL(po.object_name, '')                         AS plsql_object,
            COUNT(*) AS cnt
            FROM   DBA_HIST_ACTIVE_SESS_HISTORY se
            LEFT JOIN DBA_HIST_SERVICE_NAME sn
            ON sn.dbid = se.dbid
            AND sn.service_name_hash = se.service_hash
            LEFT JOIN DBA_OBJECTS obj
            ON obj.object_id = se.current_obj#
            AND se.current_obj# > 0
            LEFT JOIN DBA_OBJECTS pe
            ON pe.object_id = se.plsql_entry_object_id
            AND se.plsql_entry_object_id > 0
            LEFT JOIN DBA_OBJECTS po
            ON po.object_id = se.plsql_object_id
            AND se.plsql_object_id > 0
            WHERE  se.session_type != 'BACKGROUND'
            AND  se.dbid = (SELECT dbid FROM v\$database)
            AND  se.sample_time >= TO_DATE(:dt_begin, 'YYYY-MM-DD HH24:MI')
            AND  se.sample_time <  TO_DATE(:dt_end,   'YYYY-MM-DD HH24:MI')
            GROUP  BY $bucketExpr,
            CASE WHEN se.session_state = 'ON CPU' THEN 'CPU'
            ELSE NVL(se.wait_class, 'Other')
            END,
            se.sql_id, se.sql_plan_hash_value, se.force_matching_signature,
            se.module, se.action,
            CASE WHEN se.session_state = 'ON CPU' THEN 'CPU'
            ELSE NVL(se.event, 'Other')
            END,
            sn.service_name,
            se.machine,
            obj.object_name, obj.object_type,
            pe.object_name,
            po.object_name
            ORDER  BY 1, 2";

        $stm = @oci_parse($conn, $sql);
        if (!$stm) {
            $e = oci_error($conn) ?: [];
            $ociError = ['code' => (int)($e['code'] ?? 0),
                         'message' => (string)($e['message'] ?? '')];
            return [];
        }

        oci_bind_by_name($stm, ':dt_begin',    $dtBegin,    -1, SQLT_CHR);
        oci_bind_by_name($stm, ':dt_end',      $dtEnd,      -1, SQLT_CHR);
        oci_bind_by_name($stm, ':bucket_secs', $bucketSecs, -1, SQLT_INT);

        $rows = [];
        if (!@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            /* ORA-00942 (table ou vue inexistante) est ici le cas le plus
               probable quand la source est sw$bash_snapper : le paquetage est
               visible dans ALL_OBJECTS, mais ses tables ne le sont pas depuis
               ce compte — synonyme public absent, ou SELECT non accorde. */
            $e = oci_error($stm) ?: [];
            $ociError = ['code' => (int)($e['code'] ?? 0),
                         'message' => (string)($e['message'] ?? '')];
            oci_free_statement($stm);
            return [];
        }
        while ($r = oci_fetch_assoc($stm)) {
            $rows[] = [
                'minute_bucket' => (string)$r['MINUTE_BUCKET'],
                'category'      => (string)$r['CATEGORY'],
                'sql_id'        => (string)$r['SQL_ID'],
                'phv'           => (string)$r['PHV'],
                'fms'           => (string)$r['FMS'],
                'module'        => (string)$r['MODULE'],
                'action'        => (string)$r['ACTION'],
                'wait_event'    => (string)$r['WAIT_EVENT'],
                'service_name'  => (string)$r['SERVICE_NAME'],
                'machine'       => (string)$r['MACHINE'],
                'object_name'   => (string)$r['OBJECT_NAME'],
                'object_type'   => (string)$r['OBJECT_TYPE'],
                'plsql_entry'   => (string)$r['PLSQL_ENTRY'],
                'plsql_object'  => (string)$r['PLSQL_OBJECT'],
                'cnt'           => (int)$r['CNT'],
            ];
        }
        oci_free_statement($stm);
        return $rows;
    }
}
