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

class DemoOracleConfigStatsRepository
{
    private static $sysSchemas = "'SYS','SYSTEM','OUTLN','DBSNMP','APPQOSSYS','DBSFWUSER','CTXSYS','DVSYS','LBACSYS','MDSYS','OLAPSYS','ORDDATA','ORDSYS','WMSYS','XDB','GSMADMIN_INTERNAL','AUDSYS','OJVMSYS','REMOTE_SCHEDULER_AGENT','SI_INFORMTN_SCHEMA','GGSYS'";

    public static function fetchSchemaStatsSummary($conn): array
    {
        $exc = self::$sysSchemas;
        $sql = "WITH stats AS (
                    SELECT owner,
                           CASE object_type WHEN 'TABLE' THEN 'TABLE' WHEN 'PARTITION' THEN 'TAB_PART' ELSE 'TABLE' END AS otype,
                           last_analyzed, stale_stats
                    FROM DBA_TAB_STATISTICS WHERE stattype_locked IS NULL AND object_type IN ('TABLE','PARTITION')
                    UNION ALL
                    SELECT owner,
                           CASE object_type WHEN 'INDEX' THEN 'INDEX' ELSE 'IDX_PART' END AS otype,
                           last_analyzed, stale_stats
                    FROM DBA_IND_STATISTICS WHERE object_type IN ('INDEX','PARTITION')
                )
                SELECT owner,
                    TO_CHAR(MAX(CASE WHEN otype='TABLE'    THEN last_analyzed END),'YYYY-MM-DD HH24:MI') AS last_table,
                    SUM(CASE WHEN otype='TABLE'    THEN 1 ELSE 0 END) AS cnt_table,
                    SUM(CASE WHEN otype='TABLE'    AND (stale_stats='YES' OR last_analyzed IS NULL) THEN 1 ELSE 0 END) AS obs_table,
                    TO_CHAR(MAX(CASE WHEN otype='INDEX'    THEN last_analyzed END),'YYYY-MM-DD HH24:MI') AS last_index,
                    SUM(CASE WHEN otype='INDEX'    THEN 1 ELSE 0 END) AS cnt_index,
                    SUM(CASE WHEN otype='INDEX'    AND (stale_stats='YES' OR last_analyzed IS NULL) THEN 1 ELSE 0 END) AS obs_index,
                    TO_CHAR(MAX(CASE WHEN otype='TAB_PART' THEN last_analyzed END),'YYYY-MM-DD HH24:MI') AS last_tab_part,
                    SUM(CASE WHEN otype='TAB_PART' THEN 1 ELSE 0 END) AS cnt_tab_part,
                    SUM(CASE WHEN otype='TAB_PART' AND (stale_stats='YES' OR last_analyzed IS NULL) THEN 1 ELSE 0 END) AS obs_tab_part,
                    TO_CHAR(MAX(CASE WHEN otype='IDX_PART' THEN last_analyzed END),'YYYY-MM-DD HH24:MI') AS last_idx_part,
                    SUM(CASE WHEN otype='IDX_PART' THEN 1 ELSE 0 END) AS cnt_idx_part,
                    SUM(CASE WHEN otype='IDX_PART' AND (stale_stats='YES' OR last_analyzed IS NULL) THEN 1 ELSE 0 END) AS obs_idx_part
                FROM stats
                WHERE owner NOT IN ({$exc})
                GROUP BY owner
                ORDER BY owner";

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

    public static function fetchDictionaryStats($conn): array
    {
        $sql = "SELECT table_name,
                       TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') AS last_analyzed,
                       num_rows
                FROM DBA_TAB_STATISTICS
                WHERE owner = 'SYS' AND object_type = 'TABLE'
                  AND table_name IN ('TAB\$','IND\$','COL\$','OBJ\$','USER\$','CLU\$',
                                     'HIST_HEAD\$','HISTGRM\$','SEG\$','TS\$','TAB_STATS\$','IND_STATS\$')
                ORDER BY table_name";

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

    public static function fetchAutoTask($conn, string $clientName): array
    {
        $sql = "SELECT client_name, status, consumer_group, window_group,
                       TO_CHAR(last_change, 'YYYY-MM-DD HH24:MI:SS') AS last_change
                FROM DBA_AUTOTASK_CLIENT
                WHERE client_name = :cn";

        $stm = @oci_parse($conn, $sql);
        if (!$stm) return [];
        oci_bind_by_name($stm, ':cn', $clientName, 128, SQLT_CHR);
        if (!@oci_execute($stm, OCI_NO_AUTO_COMMIT)) { oci_free_statement($stm); return []; }
        $row = oci_fetch_assoc($stm);
        oci_free_statement($stm);
        return $row ?: [];
    }

    public static function fetchAutoTaskHistory($conn, string $clientName, int $limit = 20): array
    {
        $sql = "SELECT client_name,
                       window_name,
                       TO_CHAR(window_start_time, 'YYYY-MM-DD HH24:MI:SS') AS window_start,
                       TO_CHAR(window_end_time,   'YYYY-MM-DD HH24:MI:SS') AS window_end,
                       window_duration,
                       job_status, job_info
                FROM DBA_AUTOTASK_JOB_HISTORY
                WHERE client_name = :cn
                ORDER BY window_start_time DESC
                FETCH FIRST :lim ROWS ONLY";

        $stm = @oci_parse($conn, $sql);
        if (!$stm) return [];
        oci_bind_by_name($stm, ':cn',  $clientName, 128, SQLT_CHR);
        oci_bind_by_name($stm, ':lim', $limit, -1, SQLT_INT);
        if (!@oci_execute($stm, OCI_NO_AUTO_COMMIT)) { oci_free_statement($stm); return []; }
        $rows = [];
        while ($r = oci_fetch_assoc($stm)) $rows[] = $r;
        oci_free_statement($stm);
        return $rows;
    }
}
