<?php
/**
 * Demo Oracle — tablespaces et redo logs (section « config/tablespaces »).
 *
 * Portage de modules/oracle/_report_tablespaces.php. La lecture est complete :
 * tablespaces data et temp, groupes et membres de redo, commutations sur 24 h,
 * threads actifs, et les recommandations calculees.
 *
 * Les recommandations SONT conservees, y compris la commande SQL qu'elles
 * proposent. C'est du texte : la demonstration affiche ce qu'il faudrait
 * faire, elle ne sait pas le faire. Le module complet, lui, offre a cote un
 * bouton qui l'execute — redimensionnement de datafile, ajout ou suppression
 * de groupe redo, suppression de thread. Ces gestionnaires ne sont pas ici, et
 * DemoOracleTablespacesRepository n'expose aucune methode d'ecriture.
 *
 * Inclus par report.php. Variables : $oraDb, $db, $currentUser, $settings.
 */

/** @var array      $oraDb       */
/** @var object     $db          */
/** @var array|null $currentUser */
/** @var object     $settings    */

require_once __DIR__ . '/lib/DemoOracleOciConnect.php';
require_once __DIR__ . '/lib/DemoOracleTablespacesRepository.php';


$ociError        = null;
$fetchError      = null;
$tsRows          = [];
$tmpRows         = [];
$tmpUsage        = [];
$logRows         = [];
$lfRows          = [];
$redoSwitches24h = null;
$activeThreads   = [];

$conn = demo_oracle_oci_open($oraDb, $db, $ociError, '', 'config/tablespaces');

if (!$conn) {
    $fetchError = $ociError
        ? ('ORA-' . $ociError['code'] . ': ' . $ociError['message'])
        : t('demo_oracle.connect_error');
} else {
    $repo = new DemoOracleTablespacesRepository();

    $tsRows          = $repo->fetchDataTablespaces($conn);
    $tmpRows         = $repo->fetchTempTablespaces($conn);
    $tmpUsage        = $repo->fetchTempUsage($conn);
    $logRows         = $repo->fetchRedoLogGroups($conn);
    $lfRows          = $repo->fetchRedoLogMembers($conn);
    $redoSwitches24h = $repo->fetchRedoSwitchCount24h($conn);
    $activeThreads   = $repo->fetchActiveThreads($conn);

    oci_close($conn);
}

// ── PHP-side derived data ────────────────────────────────────────────────────

// Group logfile members by group_num
$lfByGroup = [];
foreach ($lfRows as $lf) {
    $lfByGroup[(int)$lf['GROUP_NUM']][] = $lf;
}

// Redo log summary stats
$logThreads      = count(array_unique(array_column($logRows, 'THREAD_NUM')));
$logGroups       = count($logRows);
$logMembersPerGrp = !empty($logRows) ? (int)$logRows[0]['MEMBERS'] : 0;
$logSizeBytes    = !empty($logRows) ? (int)$logRows[0]['BYTES']   : 0;

// JSON for JavaScript charts
$tsJson = json_encode(array_map(function ($r) {
    $total = (float)$r['TOTAL_BYTES'];
    $used  = (float)$r['USED_BYTES'];
    $free  = (float)$r['FREE_BYTES'];
    return [
        'name'        => $r['TABLESPACE_NAME'],
        'total'       => $total,
        'used'        => $used,
        'free'        => $free,
        'pct'         => $total > 0 ? round($used / $total * 100, 1) : 0,
        'files'       => (int)$r['FILE_COUNT'],
        'autoextend'  => $r['AUTOEXTENSIBLE'] === 'YES',
        'encrypted'   => ($r['ENCRYPTED'] ?? 'NO') === 'YES',
    ];
}, $tsRows));

$tmpJson = json_encode(array_map(function ($r) use ($tmpUsage) {
    $total   = (float)$r['TOTAL_BYTES'];
    $tsName  = $r['TABLESPACE_NAME'];
    $hasUsage = isset($tmpUsage[$tsName]);
    $used    = $hasUsage ? $tmpUsage[$tsName]['used'] : null;
    $free    = $hasUsage ? $tmpUsage[$tsName]['free'] : null;
    return [
        'name'       => $tsName,
        'total'      => $total,
        'used'       => $used,
        'free'       => $free,
        'pct'        => ($hasUsage && $total > 0) ? round($used / $total * 100, 1) : null,
        'files'      => (int)$r['FILE_COUNT'],
        'autoextend' => $r['AUTOEXTENSIBLE'] === 'YES',
        'encrypted'  => ($r['ENCRYPTED'] ?? 'NO') === 'YES',
    ];
}, $tmpRows));

// ── Recommendations ─────────────────────────────────────────────────────────

// Helper: round up to a "nice" size (next power-of-2 multiple of 64 MB)
if (!function_exists('_demo_niceSize')) {
function _demo_niceSize(float $bytes): float {
    $mb64 = 67108864; // 64 MB
    return max($mb64, ceil($bytes / $mb64) * $mb64);
}
}
if (!function_exists('_demo_fmtSize')) {
function _demo_fmtSize(float $bytes): string {
    if ($bytes >= 1099511627776) return round($bytes / 1099511627776, 1) . ' To';
    if ($bytes >= 1073741824)    return round($bytes / 1073741824, 1) . ' Go';
    if ($bytes >= 1048576)       return round($bytes / 1048576) . ' Mo';
    return $bytes . ' o';
}
}

// — Tablespace resize recommendations —
$tsRecommendations = [];
foreach ($tsRows as $r) {
    $total   = (float)$r['TOTAL_BYTES'];
    $used    = (float)$r['USED_BYTES'];
    $free    = (float)$r['FREE_BYTES'];
    $maxB    = (float)$r['MAX_BYTES'];
    $autoext = $r['AUTOEXTENSIBLE'] === 'YES';
    $pctUsed   = $total > 0 ? $used / $total * 100 : 0;
    $name      = $r['TABLESPACE_NAME'];
    $encrypted = ($r['ENCRYPTED'] ?? 'NO') === 'YES';

    // Target: used + 25% headroom, minimum 64 MB
    $target = _demo_niceSize($used * 1.25);

    if ($pctUsed >= 85) {
        // Nearly full — recommend grow
        if (!$autoext) {
            $tsRecommendations[] = [
                'ts'        => $name,
                'encrypted' => $encrypted,
                'type'      => 'grow',
                'reason'    => t('demo_oracle.reco_ts_full_noautoext'),
                'cmd'       => "ALTER DATABASE DATAFILE '<datafile>' AUTOEXTEND ON NEXT 256M MAXSIZE " . _demo_fmtSize($target) . ";",
            ];
        } elseif ($maxB > 0 && $used / $maxB * 100 >= 80) {
            $tsRecommendations[] = [
                'ts'        => $name,
                'encrypted' => $encrypted,
                'type'      => 'grow',
                'reason'    => t('demo_oracle.reco_ts_full_maxreached'),
                'cmd'       => "ALTER TABLESPACE " . $name . " ADD DATAFILE SIZE 1G AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED;",
            ];
        } else {
            $tsRecommendations[] = [
                'ts'        => $name,
                'encrypted' => $encrypted,
                'type'      => 'grow',
                'reason'    => sprintf(t('demo_oracle.reco_ts_full'), round($pctUsed, 0)),
                'cmd'       => "ALTER TABLESPACE " . $name . " ADD DATAFILE SIZE 1G AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED;",
            ];
        }
    } elseif ($pctUsed <= 25 && $total > 536870912 && $total > $target * 1.5) {
        // Oversized — recommend shrink (only if > 512 MB allocated and 50% bigger than target)
        $tsRecommendations[] = [
            'ts'        => $name,
            'encrypted' => $encrypted,
            'type'      => 'shrink',
            'reason'    => sprintf(t('demo_oracle.reco_ts_oversized'), round($pctUsed, 0)),
            'cmd'       => "ALTER DATABASE DATAFILE '<datafile>' RESIZE " . _demo_fmtSize($target) . ";",
        ];
    }
}

// — Redo log resize recommendations —
$redoRecommendations = [];
if ($redoSwitches24h !== null && $logSizeBytes > 0) {
    $switchesPerHour = $redoSwitches24h / 24;

    if ($switchesPerHour > 6) {
        // Too many switches — recommend larger redo
        // Target: double the size for each doubling of frequency above 4/h
        $factor = max(2, ceil($switchesPerHour / 4));
        $recommendedSize = min($logSizeBytes * $factor, 16 * 1073741824); // cap at 16 GB
        $recommendedSize = _demo_niceSize($recommendedSize);
        $redoRecommendations[] = [
            'type'   => 'resize',
            'reason' => sprintf(t('demo_oracle.reco_redo_too_frequent'), round($switchesPerHour, 1)),
            'size'   => $recommendedSize,
        ];
    } elseif ($switchesPerHour < 0.5 && $logSizeBytes > 209715200) {
        // Very infrequent switches with large logs — suggest shrink (min 200 MB)
        $recommendedSize = max(209715200, _demo_niceSize($logSizeBytes / 2));
        if ($recommendedSize < $logSizeBytes) {
            $redoRecommendations[] = [
                'type'   => 'resize',
                'reason' => sprintf(t('demo_oracle.reco_redo_infrequent'), round($switchesPerHour, 2)),
                'size'   => $recommendedSize,
            ];
        }
    }
}

// — Inactive thread detection —
$inactiveThreadGroups = [];
if (!empty($activeThreads) && !empty($logRows)) {
    $allLogThreads = array_unique(array_map(function ($r) { return (int)$r['THREAD_NUM']; }, $logRows));
    $inactiveThreadNums = array_diff($allLogThreads, $activeThreads);
    foreach ($inactiveThreadNums as $thr) {
        $groups = array_filter($logRows, function ($r) use ($thr) {
            return (int)$r['THREAD_NUM'] === $thr;
        });
        $groupNums = array_map(function ($r) { return (int)$r['GROUP_NUM']; }, $groups);
        if (!empty($groupNums)) {
            $inactiveThreadGroups[] = [
                'thread' => $thr,
                'groups' => array_values($groupNums),
            ];
        }
    }
}

$hasRecommendations = !empty($tsRecommendations) || !empty($redoRecommendations) || !empty($inactiveThreadGroups);

// ── i18n labels for JS ──────────────────────────────────────────────────────
$jsLabels = [
    'usedLabel'  => t('demo_oracle.ts_used'),
    'freeLabel'  => t('demo_oracle.ts_free'),
    'allocLabel' => t('demo_oracle.ts_allocated'),
];

// ── Render view ─────────────────────────────────────────────────────────────
View::display(__DIR__ . '/views/report/tablespaces.php', [
    'settings'             => $settings,
    'oraDb'                => $oraDb,
    'fetchError'           => $fetchError,
    'tsRows'               => $tsRows,
    'tmpRows'              => $tmpRows,
    'tmpUsage'             => $tmpUsage,
    'logRows'              => $logRows,
    'lfByGroup'            => $lfByGroup,
    'logThreads'           => $logThreads,
    'logGroups'            => $logGroups,
    'logMembersPerGrp'     => $logMembersPerGrp,
    'logSizeBytes'         => $logSizeBytes,
    'redoSwitches24h'      => $redoSwitches24h,
    'tsJson'               => $tsJson,
    'tmpJson'              => $tmpJson,
    'tsRecommendations'    => $tsRecommendations,
    'redoRecommendations'  => $redoRecommendations,
    'inactiveThreadGroups' => $inactiveThreadGroups,
    'hasRecommendations'   => $hasRecommendations,
    'jsLabels'             => $jsLabels,
]);
