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

class DemoOracleAshRepository
{
    /**
     * Vérifie si la base a accès au Diagnostics Pack via le paramètre
     * CONTROL_MANAGEMENT_PACK_ACCESS. Les bases Cloud (conn_type='cloud')
     * sont gérées en amont : ce check ne doit pas être appelé pour elles.
     *
     * @return array{has_license: bool, pack_access: string}
     */
    public function getLicenseInfo($conn): array
    {
        $packAccess = 'NONE';
        $hasLicense = false;

        $stm = oci_parse($conn,
            "SELECT value FROM V\$PARAMETER WHERE name = 'control_management_pack_access'");
        if ($stm && @oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            if ($r = oci_fetch_assoc($stm)) {
                $packAccess = (string)$r['VALUE'];
                $hasLicense = (stripos($packAccess, 'DIAGNOSTIC') !== false);
            }
            oci_free_statement($stm);
        }

        return ['has_license' => $hasLicense, 'pack_access' => $packAccess];
    }

    /**
     * ASH agrégée par tranche horaire × catégorie (CPU / wait_class).
     * Source du graphique stacked-bar du rapport historique.
     *
     * @return list<array{sample_bucket:string, category:string, cnt:int}>
     */
    public function fetchAshHourly($conn, int $days): array
    {
        $sql = "SELECT TO_CHAR(TRUNC(ash.sample_time, 'HH24'), 'YYYY-MM-DD HH24:MI:SS') AS sample_bucket,
                       CASE WHEN ash.session_state = 'ON CPU'
                            THEN 'CPU'
                            ELSE NVL(ash.wait_class, 'Other')
                       END AS category,
                       COUNT(*) AS cnt
                FROM   DBA_HIST_ACTIVE_SESS_HISTORY ash
                WHERE  ash.sample_time >= SYSDATE - :days
                  AND  ash.dbid = (SELECT dbid FROM v\$database)
                GROUP  BY TRUNC(ash.sample_time, 'HH24'),
                          CASE WHEN ash.session_state = 'ON CPU'
                               THEN 'CPU'
                               ELSE NVL(ash.wait_class, 'Other')
                          END
                ORDER  BY 1, 2";

        return $this->fetchAshHourlyRows($conn, $sql, $days, function ($r) {
            return [
                'sample_bucket' => (string)$r['SAMPLE_BUCKET'],
                'category'      => (string)$r['CATEGORY'],
                'cnt'           => (int)$r['CNT'],
            ];
        });
    }

    /**
     * Heatmap Charge — total ASH activity, agrégé par jour × heure.
     *
     * @return list<array{day:string, hour:int, cnt:int}>
     */
    public function fetchHeatmapCharge($conn, int $days): array
    {
        $sql = "SELECT TO_CHAR(TRUNC(ash.sample_time,'DD'),'YYYY-MM-DD') AS day_bucket,
                       TO_NUMBER(TO_CHAR(ash.sample_time,'HH24'))         AS hour_of_day,
                       COUNT(*)                                            AS cnt
                FROM   DBA_HIST_ACTIVE_SESS_HISTORY ash
                WHERE  ash.sample_time >= SYSDATE - :days
                  AND  ash.dbid = (SELECT dbid FROM v\$database)
                GROUP  BY TRUNC(ash.sample_time,'DD'), TO_NUMBER(TO_CHAR(ash.sample_time,'HH24'))
                ORDER  BY 1, 2";

        return $this->fetchAshHourlyRows($conn, $sql, $days, [$this, 'mapHeatmapRow']);
    }

    /**
     * Heatmap I/O — ASH filtrée sur wait_class IN ('User I/O', 'System I/O').
     *
     * @return list<array{day:string, hour:int, cnt:int}>
     */
    public function fetchHeatmapIo($conn, int $days): array
    {
        $sql = "SELECT TO_CHAR(TRUNC(ash.sample_time,'DD'),'YYYY-MM-DD') AS day_bucket,
                       TO_NUMBER(TO_CHAR(ash.sample_time,'HH24'))         AS hour_of_day,
                       COUNT(*)                                            AS cnt
                FROM   DBA_HIST_ACTIVE_SESS_HISTORY ash
                WHERE  ash.sample_time >= SYSDATE - :days
                  AND  ash.dbid = (SELECT dbid FROM v\$database)
                  AND  ash.wait_class IN ('User I/O','System I/O')
                GROUP  BY TRUNC(ash.sample_time,'DD'), TO_NUMBER(TO_CHAR(ash.sample_time,'HH24'))
                ORDER  BY 1, 2";

        return $this->fetchAshHourlyRows($conn, $sql, $days, [$this, 'mapHeatmapRow']);
    }

    /**
     * Heatmap Application — log switches (V$LOG_HISTORY) agrégés par jour × heure.
     *
     * @return list<array{day:string, hour:int, cnt:int}>
     */
    public function fetchHeatmapLogSwitches($conn, int $days): array
    {
        $sql = "SELECT TO_CHAR(TRUNC(lh.first_time,'DD'),'YYYY-MM-DD') AS day_bucket,
                       TO_NUMBER(TO_CHAR(lh.first_time,'HH24'))         AS hour_of_day,
                       COUNT(*)                                          AS cnt
                FROM   V\$LOG_HISTORY lh
                WHERE  lh.first_time >= SYSDATE - :days
                GROUP  BY TRUNC(lh.first_time,'DD'), TO_NUMBER(TO_CHAR(lh.first_time,'HH24'))
                ORDER  BY 1, 2";

        return $this->fetchAshHourlyRows($conn, $sql, $days, [$this, 'mapHeatmapRow']);
    }

    // ─────────────────────────────────────────────────────────────────────

    /**
     * Helper interne : prépare, binde :days, exécute, applique $mapper sur
     * chaque ligne, libère le statement. Tableau vide en cas d'échec.
     */
    private function fetchAshHourlyRows($conn, string $sql, int $days, callable $mapper): array
    {
        $rows = [];
        $stm = oci_parse($conn, $sql);
        if (!$stm) return $rows;

        oci_bind_by_name($stm, ':days', $days, -1, SQLT_INT);
        if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) {
                $rows[] = $mapper($r);
            }
        }
        oci_free_statement($stm);
        return $rows;
    }

    private function mapHeatmapRow(array $r): array
    {
        return [
            'day'  => (string)$r['DAY_BUCKET'],
            'hour' => (int)$r['HOUR_OF_DAY'],
            'cnt'  => (int)$r['CNT'],
        ];
    }
}
