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

class DemoOracleConfigSqltuneRepository
{
    public static function fetchAutoTuningParams($conn): array
    {
        $sql = "SELECT parameter_name, parameter_value, parameter_type,
                       is_default, description
                FROM DBA_ADVISOR_PARAMETERS
                WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
                ORDER BY parameter_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 fetchSqlProfiles($conn, int $limit = 50): array
    {
        $sql = "SELECT name, category, status, sql_text, type, force_matching,
                       TO_CHAR(created,       'YYYY-MM-DD HH24:MI:SS') AS created,
                       TO_CHAR(last_modified,  'YYYY-MM-DD HH24:MI:SS') AS last_modified,
                       description
                FROM DBA_SQL_PROFILES
                ORDER BY created DESC
                FETCH FIRST :lim ROWS ONLY";

        $stm = @oci_parse($conn, $sql);
        if (!$stm) return [];
        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)) {
            $txt = $r['SQL_TEXT'] ?? '';
            if (is_object($txt) && method_exists($txt, 'load')) {
                $txt = $txt->load() ?: '';
                if (method_exists($r['SQL_TEXT'], 'free')) $r['SQL_TEXT']->free();
            }
            $r['SQL_TEXT'] = mb_substr((string)$txt, 0, 200);
            $rows[] = $r;
        }
        oci_free_statement($stm);
        return $rows;
    }

    public static function fetchTuningExecutions($conn, int $limit = 20): array
    {
        $sql = "SELECT task_name, execution_name, execution_type,
                       TO_CHAR(execution_start, 'YYYY-MM-DD HH24:MI:SS') AS exec_start,
                       TO_CHAR(execution_end,   'YYYY-MM-DD HH24:MI:SS') AS exec_end,
                       status, error_message
                FROM DBA_ADVISOR_EXECUTIONS
                WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
                ORDER BY execution_start DESC
                FETCH FIRST :lim ROWS ONLY";

        $stm = @oci_parse($conn, $sql);
        if (!$stm) return [];
        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;
    }
}
