<?php
/**
 * Demo Oracle — DemoOracleDashStatsRepository.
 *
 * Portage de modules/oracle/lib/OracleDashStatsRepository.php : requetes de LECTURE
 * uniquement. Les methodes d'action ont ete RETIREES a la
 * source, pas desactivees :
 *
 *   setGlobalPref
 *
 * 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('DemoOracleDashStatsRepository')) return;

class DemoOracleDashStatsRepository
{
    public static function fetchGlobalPrefs($conn, array $prefNames): array
    {
        if (empty($prefNames)) return [];

        $cols = [];
        foreach ($prefNames as $pn) {
            $cols[] = "DBMS_STATS.GET_PREFS('" . $pn . "') AS \"$pn\"";
        }
        $sql = 'SELECT ' . implode(', ', $cols) . ' FROM DUAL';

        $stm = @oci_parse($conn, $sql);
        if (!$stm) return [];

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

        $prefs = [];
        if ($r = oci_fetch_assoc($stm)) {
            foreach ($prefNames as $pn) {
                $prefs[$pn] = (string)($r[$pn] ?? '');
            }
        }
        oci_free_statement($stm);
        return $prefs;
    }

    public static function fetchSystemStats($conn): array
    {
        $sql = "SELECT sname, pname, pval1, pval2
                FROM   SYS.AUX_STATS\$
                ORDER BY sname, pname";

        $stm = @oci_parse($conn, $sql);
        if (!$stm) return [];

        $rows = [];
        if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) $rows[] = $r;
        }
        oci_free_statement($stm);
        return $rows;
    }

    public static function fetchOptstatOperations($conn, int $limit = 50): array
    {
        $sql = "SELECT operation, target,
                       TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
                       TO_CHAR(end_time,   'YYYY-MM-DD HH24:MI:SS') AS end_time,
                       ROUND((CAST(end_time AS DATE) - CAST(start_time AS DATE)) * 86400, 1) AS duration_s,
                       status,
                       NVL(SUBSTR(notes, 1, 200), '') AS notes
                FROM DBA_OPTSTAT_OPERATIONS
                ORDER BY start_time DESC NULLS LAST
                FETCH FIRST :lim ROWS ONLY";

        $stm = @oci_parse($conn, $sql);
        if (!$stm) return [];

        oci_bind_by_name($stm, ':lim', $limit, -1, SQLT_INT);

        $rows = [];
        if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) $rows[] = $r;
        }
        oci_free_statement($stm);
        return $rows;
    }

    public static function fetchStaleBySchema($conn): array
    {
        $sql = "SELECT owner,
                       COUNT(*) AS total,
                       SUM(CASE WHEN stale_stats = 'YES' THEN 1 ELSE 0 END) AS stale,
                       SUM(CASE WHEN num_rows IS NULL  THEN 1 ELSE 0 END) AS never_analyzed,
                       TO_CHAR(MIN(last_analyzed), 'YYYY-MM-DD HH24:MI') AS oldest_analysis
                FROM   DBA_TAB_STATISTICS
                WHERE  object_type = 'TABLE'
                GROUP BY owner
                HAVING SUM(CASE WHEN stale_stats = 'YES' THEN 1 ELSE 0 END) > 0
                    OR SUM(CASE WHEN num_rows IS NULL  THEN 1 ELSE 0 END) > 0
                ORDER BY stale DESC, never_analyzed DESC, owner";

        $stm = @oci_parse($conn, $sql);
        if (!$stm) return [];

        $rows = [];
        if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) $rows[] = $r;
        }
        oci_free_statement($stm);
        return $rows;
    }

    public static function fetchTopStaleTables($conn, int $limit = 30): array
    {
        $sql = "SELECT owner, table_name, partition_name,
                       stale_stats,
                       num_rows, sample_size,
                       TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI') AS last_analyzed
                FROM   DBA_TAB_STATISTICS
                WHERE  object_type = 'TABLE'
                  AND  (stale_stats = 'YES' OR num_rows IS NULL)
                ORDER BY CASE WHEN num_rows IS NULL THEN 0 ELSE 1 END,
                         last_analyzed NULLS FIRST,
                         owner, table_name
                FETCH FIRST :lim ROWS ONLY";

        $stm = @oci_parse($conn, $sql);
        if (!$stm) return [];

        oci_bind_by_name($stm, ':lim', $limit, -1, SQLT_INT);

        $rows = [];
        if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            while ($r = oci_fetch_assoc($stm)) $rows[] = $r;
        }
        oci_free_statement($stm);
        return $rows;
    }
}
