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

class DemoOracleDashBackupsRepository
{
    public static function fetchDatabaseInfo($conn): array
    {
        $stm = @oci_parse($conn, "SELECT name, log_mode, database_role FROM V\$DATABASE");
        if (!$stm) return [];
        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 fetchLastFullBackupTime($conn): ?string
    {
        $sql = "SELECT TO_CHAR(MAX(end_time), 'YYYY-MM-DD HH24:MI:SS') AS last_ok
                FROM V\$RMAN_BACKUP_JOB_DETAILS
                WHERE status = 'COMPLETED'
                  AND input_type IN ('DB FULL','DB INCR','DATAFILE FULL','DATAFILE INCR')";
        $stm = @oci_parse($conn, $sql);
        if (!$stm) return null;
        if (!@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            oci_free_statement($stm);
            return null;
        }
        $row = oci_fetch_assoc($stm);
        oci_free_statement($stm);
        return $row ? (string)($row['LAST_OK'] ?? '') ?: null : null;
    }

    public static function fetchLastArchivelogBackupTime($conn): ?string
    {
        $sql = "SELECT TO_CHAR(MAX(end_time), 'YYYY-MM-DD HH24:MI:SS') AS last_ok
                FROM V\$RMAN_BACKUP_JOB_DETAILS
                WHERE status = 'COMPLETED' AND input_type = 'ARCHIVELOG'";
        $stm = @oci_parse($conn, $sql);
        if (!$stm) return null;
        if (!@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            oci_free_statement($stm);
            return null;
        }
        $row = oci_fetch_assoc($stm);
        oci_free_statement($stm);
        return $row ? (string)($row['LAST_OK'] ?? '') ?: null : null;
    }

    public static function fetchRmanJobs($conn, int $limit = 30): array
    {
        $sql = "SELECT session_recid, session_stamp, input_type, status,
                       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,
                       time_taken_display,
                       input_bytes_display, output_bytes_display, compression_ratio,
                       output_device_type
                FROM V\$RMAN_BACKUP_JOB_DETAILS
                ORDER BY start_time 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;
    }

    /**
     * Synthèse des backups de base — 1 backup = 1 « îlot » de backup sets dont
     * les completion_time sont espacés de moins de 2 h (islanding). Ne retient
     * que les sauvegardes de base : backup_type D/I avec incremental_level NULL
     * (FULL) ou 0 (INCR 0), sur ~1 mois glissant. Renvoie les $limit derniers.
     *
     * Source : V$BACKUP_SET (base courante, pas de catalogue RMAN requis).
     * Inspiré d'une requête catalogue (rc_backup_set) adaptée aux vues locales.
     *
     * @return list<array{TYPE_LABEL:string, FIN:string, NB_SETS:int, RANG:int}>
     */
    public static function fetchBackupSummary($conn, int $limit = 10, ?string &$oraError = null): array
    {
        $sql = "WITH backups AS (                 -- 1 ligne par backup set (~1 mois)
                    SELECT s.completion_time AS fin, s.incremental_level AS lvl
                    FROM   V\$BACKUP_SET s
                    WHERE  s.backup_type IN ('D','I')
                      AND  (s.incremental_level IS NULL OR s.incremental_level = 0)
                      AND  s.completion_time > ADD_MONTHS(SYSDATE, -1) - 1
                ),
                flagged AS (                       -- nouvel îlot si écart >= 2 h
                    SELECT fin, lvl,
                           CASE WHEN LAG(fin) OVER (ORDER BY fin) IS NULL
                                 OR fin - LAG(fin) OVER (ORDER BY fin) >= 2/24
                                THEN 1 ELSE 0 END AS new_grp
                    FROM   backups
                ),
                grouped AS (                       -- id d'îlot = somme cumulée
                    SELECT fin, lvl,
                           SUM(new_grp) OVER (ORDER BY fin) AS grp
                    FROM   flagged
                ),
                clusters AS (                      -- 1 ligne par îlot = 1 backup
                    SELECT grp,
                           MAX(fin)  AS fin,
                           COUNT(*)  AS nb_sets,
                           CASE WHEN MIN(lvl) = 0 THEN 'INCR 0' ELSE 'FULL' END AS type_label
                    FROM   grouped
                    GROUP  BY grp
                )
                SELECT type_label,
                       TO_CHAR(fin, 'YYYY-MM-DD HH24:MI:SS') AS fin,
                       nb_sets, rang
                FROM (
                    SELECT type_label, fin, nb_sets,
                           ROW_NUMBER() OVER (ORDER BY fin DESC) AS rang
                    FROM   clusters
                )
                WHERE  rang <= :lim
                ORDER  BY rang";
        $stm = @oci_parse($conn, $sql);
        if (!$stm) {
            $e = oci_error($conn);
            $oraError = $e ? ('ORA-' . $e['code'] . ': ' . $e['message']) : 'oci_parse failed';
            return [];
        }
        oci_bind_by_name($stm, ':lim', $limit, -1, SQLT_INT);
        if (!@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            $e = oci_error($stm);
            $oraError = $e ? ('ORA-' . $e['code'] . ': ' . $e['message']) : 'oci_execute failed';
            oci_free_statement($stm);
            return [];
        }
        $rows = [];
        while ($r = oci_fetch_assoc($stm)) {
            $rows[] = [
                'TYPE_LABEL' => (string)($r['TYPE_LABEL'] ?? ''),
                'FIN'        => (string)($r['FIN'] ?? ''),
                'NB_SETS'    => (int)($r['NB_SETS'] ?? 0),
                'RANG'       => (int)($r['RANG'] ?? 0),
            ];
        }
        oci_free_statement($stm);
        return $rows;
    }

    public static function diagnoseBackupSummary($conn): array
    {
        $diag = ['accessible' => false, 'total_rows' => 0, 'categories' => [], 'error' => null];
        $stm = @oci_parse($conn, "SELECT COUNT(*) AS cnt FROM V\$BACKUP_SET");
        if (!$stm) {
            $e = oci_error($conn);
            $diag['error'] = $e ? ('ORA-' . $e['code'] . ': ' . $e['message']) : 'parse failed';
            return $diag;
        }
        if (!@oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
            $e = oci_error($stm);
            $diag['error'] = $e ? ('ORA-' . $e['code'] . ': ' . $e['message']) : 'execute failed';
            oci_free_statement($stm);
            return $diag;
        }
        $r = oci_fetch_assoc($stm);
        oci_free_statement($stm);
        $diag['accessible'] = true;
        $diag['total_rows'] = (int)($r['CNT'] ?? 0);

        if ($diag['total_rows'] > 0) {
            $stm = @oci_parse($conn, "SELECT backup_type, COUNT(*) AS cnt FROM V\$BACKUP_SET GROUP BY backup_type");
            if ($stm && @oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
                while ($r = oci_fetch_assoc($stm)) {
                    $diag['categories'][$r['BACKUP_TYPE']] = (int)$r['CNT'];
                }
            }
            if ($stm) oci_free_statement($stm);
        }
        return $diag;
    }

    public static function fetchArchivedLogs($conn, int $limit = 20): array
    {
        $sql = "SELECT thread#, sequence#, applied, archived, deleted, status,
                       TO_CHAR(first_time,    'YYYY-MM-DD HH24:MI:SS') AS first_time,
                       TO_CHAR(completion_time,'YYYY-MM-DD HH24:MI:SS') AS completion_time,
                       ROUND(blocks * block_size / 1048576, 1) AS size_mb,
                       name
                FROM V\$ARCHIVED_LOG
                WHERE name IS NOT NULL
                ORDER BY completion_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);
        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;
    }
}
