<?php
/**
 * Demo Oracle — DemoOracleTablespacesRepository.
 *
 * Portage de modules/oracle/lib/OracleTablespacesRepository.php : requetes de LECTURE
 * uniquement. Les methodes d'action ont ete RETIREES a la
 * source, pas desactivees :
 *
 *   resizeDatafile, addRedoLogGroup, dropRedoLogGroup, dropRedoThread,
 * resizeAllRedoLogs et switchAndCheckpoint
 *
 * 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('DemoOracleTablespacesRepository')) return;

class DemoOracleTablespacesRepository
{
    public function fetchDataTablespaces($conn): array
    {
        $sql = "SELECT df.tablespace_name
                     , SUM(df.bytes)                               total_bytes
                     , SUM(df.bytes) - NVL(SUM(fs.free_bytes), 0) used_bytes
                     , NVL(SUM(fs.free_bytes), 0)                  free_bytes
                     , COUNT(DISTINCT df.file_id)                  file_count
                     , MAX(df.autoextensible)                      autoextensible
                     , SUM(df.maxbytes)                            max_bytes
                     , MAX(ts.encrypted)                           encrypted
                  FROM dba_data_files df
                  LEFT JOIN (SELECT tablespace_name, SUM(bytes) free_bytes
                               FROM dba_free_space
                              GROUP BY tablespace_name) fs
                         ON fs.tablespace_name = df.tablespace_name
                  LEFT JOIN dba_tablespaces ts
                         ON ts.tablespace_name = df.tablespace_name
                 GROUP BY df.tablespace_name
                 ORDER BY SUM(df.bytes) DESC";

        $rows = [];
        $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 function fetchTempTablespaces($conn): array
    {
        $sql = "SELECT tf.tablespace_name
                     , SUM(tf.bytes)          total_bytes
                     , COUNT(tf.file_id)      file_count
                     , MAX(tf.autoextensible) autoextensible
                     , MAX(ts.encrypted)      encrypted
                  FROM dba_temp_files tf
                  LEFT JOIN dba_tablespaces ts
                         ON ts.tablespace_name = tf.tablespace_name
                 GROUP BY tf.tablespace_name
                 ORDER BY tf.tablespace_name";

        $rows = [];
        $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 function fetchTempUsage($conn): array
    {
        $sql = "SELECT tablespace_name
                     , SUM(bytes_used) used_bytes
                     , SUM(bytes_free) free_bytes
                  FROM v\$temp_space_header
                 GROUP BY tablespace_name";

        $rows = [];
        $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['TABLESPACE_NAME']] = [
                    'used' => (float)$r['USED_BYTES'],
                    'free' => (float)$r['FREE_BYTES'],
                ];
            }
        }
        oci_free_statement($stm);
        return $rows;
    }

    public function fetchRedoLogGroups($conn): array
    {
        $sql = "SELECT thread#   thread_num
                     , group#    group_num
                     , bytes     bytes
                     , members   members
                     , status    status
                     , archived  archived
                  FROM v\$log
                 ORDER BY thread#, group#";

        $rows = [];
        $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 function fetchRedoLogMembers($conn): array
    {
        $sql = "SELECT group#   group_num
                     , member   member
                     , status   status
                     , type     type
                  FROM v\$logfile
                 ORDER BY group#, member";

        $rows = [];
        $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 function fetchRedoSwitchCount24h($conn): ?int
    {
        $sql = "SELECT COUNT(*) cnt
                  FROM v\$log_history
                 WHERE first_time >= SYSDATE - 1";

        $stm = oci_parse($conn, $sql);
        if (!$stm) return null;
        if (@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            $r = oci_fetch_assoc($stm);
            oci_free_statement($stm);
            if ($r) return (int)$r['CNT'];
        } else {
            oci_free_statement($stm);
        }
        return null;
    }

    public function fetchActiveThreads($conn): array
    {
        $sql = "SELECT instance_number, thread#  thread_num, status
                  FROM gv\$instance
                 WHERE status = 'OPEN'";

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

    // ── DBA actions ────────────────────────────────────────────────────────

    public static function fetchDatafiles($conn, string $tsName): array
    {
        $sql = "SELECT file_id, file_name, tablespace_name,
                       ROUND(bytes/1048576) AS size_mb,
                       ROUND(maxbytes/1048576) AS max_size_mb,
                       autoextensible, status
                  FROM DBA_DATA_FILES
                 WHERE tablespace_name = :ts
                 ORDER BY file_id";
        $stmt = @oci_parse($conn, $sql);
        if (!$stmt) return [];
        oci_bind_by_name($stmt, ':ts', $tsName, 128, SQLT_CHR);
        if (!@oci_execute($stmt, OCI_NO_AUTO_COMMIT)) {
            oci_free_statement($stmt);
            return [];
        }
        $rows = [];
        while ($row = oci_fetch_assoc($stmt)) $rows[] = $row;
        oci_free_statement($stmt);
        return $rows;
    }
}
