<?php
/**
 * ReportRenderer — génère le contenu HTML des rapports pour l'envoi par e-mail.
 *
 * Chaque rapport produit un résumé compact (pas de JS, inline CSS) avec un lien
 * « voir en ligne » vers la page web complète.
 */
class ReportRenderer
{
    /** Registre des rapports disponibles. */
    public const REPORTS = [
        'cnp/ad_anomalies' => [
            'label_key'   => 'report_sub.report_cnp_ad_anomalies',
            'subject_key' => 'report_sub.subject_cnp_ad_anomalies',
            'module'      => 'cnp',
            'url'         => '/modules/CNP/ad_anomalies.php',
        ],
        'cmdb/db_inventory' => [
            'label_key'   => 'report_sub.report_cmdb_db_inventory',
            'subject_key' => 'report_sub.subject_cmdb_db_inventory',
            'module'      => 'cmdb',
            'url'         => '/modules/CMDB/db_inventory.php',
        ],
        'cnp/cmdb_sync' => [
            'label_key'   => 'report_sub.report_cnp_cmdb_sync',
            'subject_key' => 'report_sub.subject_cnp_cmdb_sync',
            'module'      => 'cnp',
            'url'         => '/modules/CNP/cmdb_sync.php',
        ],
    ];

    /**
     * Rend un rapport en HTML pour e-mail.
     * @return array{ok: bool, html: string, subject: string, error: ?string}
     */
    public static function render(string $reportKey): array
    {
        $meta = self::REPORTS[$reportKey] ?? null;
        if (!$meta) {
            return ['ok' => false, 'html' => '', 'subject' => '', 'error' => "Unknown report: {$reportKey}"];
        }

        $subject = t($meta['subject_key']);

        try {
            $body = match ($reportKey) {
                'cnp/ad_anomalies'  => self::renderAdAnomalies(),
                'cmdb/db_inventory' => self::renderDbInventory(),
                'cnp/cmdb_sync'     => self::renderCmdbSync(),
            };
        } catch (\Throwable $e) {
            return ['ok' => false, 'html' => '', 'subject' => $subject,
                    'error' => $e->getMessage()];
        }

        $html = self::emailShell($subject, $body, $meta['url']);
        return ['ok' => true, 'html' => $html, 'subject' => $subject, 'error' => null];
    }

    /* ══════════════════════════════════════════════════════════════════
     *  Report renderers
     * ══════════════════════════════════════════════════════════════════ */

    private static function renderAdAnomalies(): string
    {
        require_once WEBROOT_PATH . '/modules/CMDB/lib/CmdbConnect.php';

        $err  = null;
        $conn = cmdb_oci_open($err, 'report/ad_anomalies');
        if (!$conn) {
            throw new \RuntimeException('CMDB connection failed: ' . ($err['message'] ?? 'unknown'));
        }

        $counts = [];
        $queries = [
            'no_vfr'   => "SELECT COUNT(*) cnt FROM REPORT_SGBD_OWNER.CNP\$ADUSERS u
                           WHERE NOT EXISTS (SELECT 1 FROM REPORT_SGBD_OWNER.CNP\$ADUG g WHERE g.san = u.san AND g.cn = 'ORA_VFR_MD5')
                              OR NOT EXISTS (SELECT 1 FROM REPORT_SGBD_OWNER.CNP\$ADUG g WHERE g.san = u.san AND g.cn = 'ORA_VFR_12C')",
            'only_vfr' => "SELECT COUNT(*) cnt FROM REPORT_SGBD_OWNER.CNP\$ADUSERS u
                           WHERE EXISTS (SELECT 1 FROM REPORT_SGBD_OWNER.CNP\$ADUG g WHERE g.san = u.san AND g.cn = 'ORA_VFR_MD5')
                             AND EXISTS (SELECT 1 FROM REPORT_SGBD_OWNER.CNP\$ADUG g WHERE g.san = u.san AND g.cn = 'ORA_VFR_12C')
                             AND NOT EXISTS (SELECT 1 FROM REPORT_SGBD_OWNER.CNP\$ADUG g WHERE g.san = u.san AND g.cn LIKE 'ORA_%' AND g.cn NOT IN ('ORA_VFR_MD5','ORA_VFR_12C'))",
            'no_ldap'  => "SELECT COUNT(*) cnt FROM REPORT_SGBD_OWNER.CNP\$ADUSERS WHERE pass_hash != 'In LDAP'",
        ];

        foreach ($queries as $key => $sql) {
            $stm = @oci_parse($conn, $sql);
            $counts[$key] = 0;
            if ($stm && @oci_execute($stm, OCI_NO_AUTO_COMMIT)) {
                $r = oci_fetch_assoc($stm);
                $counts[$key] = (int)($r['CNT'] ?? 0);
                oci_free_statement($stm);
            }
        }
        oci_close($conn);

        $html = '<table style="width:100%;border-collapse:collapse;font-size:14px">';
        $html .= self::tableRow(t('cnp.anomalies_no_vfr_title'), $counts['no_vfr'], $counts['no_vfr'] > 0 ? '#c0392b' : '#27ae60');
        $html .= self::tableRow(t('cnp.anomalies_only_vfr_title'), $counts['only_vfr'], '#64748b');
        $html .= self::tableRow(t('cnp.anomalies_no_ldap_title'), $counts['no_ldap'], $counts['no_ldap'] > 0 ? '#c0392b' : '#27ae60');
        $html .= '</table>';

        return $html;
    }

    private static function renderDbInventory(): string
    {
        $cmdbDb = CmdbCache::openReadOnly();
        if (!$cmdbDb) {
            throw new \RuntimeException('CMDB cache not available (cmdb.sqlite).');
        }

        $total = 0; $byStatus = []; $byVersion = [];
        $stm = $cmdbDb->query("SELECT db_status, oracle_version, COUNT(*) cnt FROM liste_bases GROUP BY db_status, oracle_version");
        if ($stm) {
            while ($r = $stm->fetchArray(SQLITE3_ASSOC)) {
                $cnt = (int)$r['cnt'];
                $total += $cnt;
                $st = $r['db_status'] ?: 'UNKNOWN';
                $byStatus[$st] = ($byStatus[$st] ?? 0) + $cnt;
                $ver = $r['oracle_version'] ?: '?';
                $byVersion[$ver] = ($byVersion[$ver] ?? 0) + $cnt;
            }
        }
        $cmdbDb->close();

        arsort($byStatus);
        arsort($byVersion);

        $html = '<p style="font-size:16px;font-weight:600;margin:0 0 12px">'
              . t('report_sub.total_databases') . ' : <strong>' . $total . '</strong></p>';

        $html .= '<h3 style="font-size:14px;margin:12px 0 6px">' . t('cmdb.inventory_col_status') . '</h3>';
        $html .= '<table style="width:100%;border-collapse:collapse;font-size:14px">';
        foreach ($byStatus as $st => $cnt) {
            $color = strcasecmp($st, 'OPEN') === 0 ? '#27ae60' : '#e67e22';
            $html .= self::tableRow($st, $cnt, $color);
        }
        $html .= '</table>';

        $html .= '<h3 style="font-size:14px;margin:12px 0 6px">' . t('cmdb.inventory_col_version') . '</h3>';
        $html .= '<table style="width:100%;border-collapse:collapse;font-size:14px">';
        foreach (array_slice($byVersion, 0, 10) as $ver => $cnt) {
            $html .= self::tableRow($ver, $cnt, '#2563eb');
        }
        $html .= '</table>';

        return $html;
    }

    private static function renderCmdbSync(): string
    {
        $db = Database::getInstance();

        $cmdbDb = CmdbCache::openReadOnly();
        if (!$cmdbDb) {
            throw new \RuntimeException('CMDB cache not available (cmdb.sqlite).');
        }

        // Bases Oracle enregistrées
        $oraDbs = $db->fetchAll("SELECT name FROM oracle_databases");
        $oraNames = array_column($oraDbs, 'name');

        // Bases CMDB
        $cmdbBases = [];
        $stm = $cmdbDb->query("SELECT db_unique_name, trigramme, environnement FROM liste_bases");
        if ($stm) while ($r = $stm->fetchArray(SQLITE3_ASSOC)) $cmdbBases[] = $r;
        $cmdbDb->close();

        $missing = 0;
        foreach ($cmdbBases as $c) {
            if (!in_array($c['db_unique_name'], $oraNames, true)) $missing++;
        }

        // Écarts groupes
        $oraDbDetails = $db->fetchAll(
            "SELECT d.name, d.subgroup, g.name AS group_name
             FROM oracle_databases d JOIN oracle_groups g ON g.id = d.group_id"
        );
        $oraMap = [];
        foreach ($oraDbDetails as $d) $oraMap[$d['name']] = $d;

        $envToSub = ['prod'=>'prod','dev'=>'dev','homol'=>'homol','perf'=>'perf','secours'=>'secours'];
        $mismatches = 0;
        foreach ($cmdbBases as $c) {
            $dbu = $c['db_unique_name'];
            if (!isset($oraMap[$dbu])) continue;
            $ora = $oraMap[$dbu];
            $cmdbSub = $envToSub[$c['environnement'] ?? ''] ?? 'dev';
            if (strcasecmp($ora['group_name'], $c['trigramme']) !== 0 || $ora['subgroup'] !== $cmdbSub) {
                $mismatches++;
            }
        }

        $html = '<table style="width:100%;border-collapse:collapse;font-size:14px">';
        $html .= self::tableRow(t('cnp.sync_missing_title'), $missing, $missing > 0 ? '#c0392b' : '#27ae60');
        $html .= self::tableRow(t('cnp.sync_mismatch_title'), $mismatches, $mismatches > 0 ? '#e67e22' : '#27ae60');
        $html .= self::tableRow(t('report_sub.total_databases'), count($cmdbBases), '#64748b');
        $html .= '</table>';

        return $html;
    }

    /* ══════════════════════════════════════════════════════════════════
     *  HTML helpers
     * ══════════════════════════════════════════════════════════════════ */

    private static function emailShell(string $title, string $body, string $url): string
    {
        $appTitle = htmlspecialchars(Settings::getInstance()->get('app_title', 'Application'));
        $date     = date('Y-m-d H:i');
        return '<div style="font-family:system-ui,sans-serif;max-width:600px;margin:0 auto;padding:20px">'
             . '<h2 style="color:#2563eb;margin:0 0 16px;border-bottom:2px solid #2563eb;padding-bottom:8px">'
             . htmlspecialchars($title) . '</h2>'
             . $body
             . '<p style="margin:20px 0 0;font-size:12px;color:#94a3b8">'
             . $appTitle . ' — ' . $date
             . ' — <a href="' . htmlspecialchars($url) . '" style="color:#2563eb">'
             . t('report_sub.view_online') . '</a></p>'
             . '</div>';
    }

    private static function tableRow(string $label, int|string $value, string $color = '#1e293b'): string
    {
        return '<tr style="border-bottom:1px solid #e2e8f0">'
             . '<td style="padding:6px 10px">' . htmlspecialchars($label) . '</td>'
             . '<td style="padding:6px 10px;text-align:right;font-weight:700;color:' . $color . '">'
             . htmlspecialchars((string)$value) . '</td></tr>';
    }
}
