<?php
/**
 * [FEAT 1.6.27] PÁGINA SAÚDE DO SISTEMA
 *
 * Check-up activo de problemas que merecem atenção:
 *   - Imports pendentes de aprovação há > 3 dias
 *   - Releases LIVE que não reportam há > 60 dias
 *   - Faixas sem ISRC
 *   - Releases pendentes de revisão há > 14 dias
 *   - Matches em revisão há > 7 dias
 *   - Artistas com saldo significativo sem método de pagamento
 *
 * Cada check tem severidade (critical/warning/info) e link para o sítio
 * onde resolver. Página única, view-only — não faz nada autonomamente.
 *
 * Acedida via:
 *   ?page=rmbz-admin-distribution&tab=royalties&rsub=health
 */

defined('ABSPATH') or exit;


function rmbz_render_system_health() {
    global $wpdb;
    $prefix = $wpdb->prefix;

    // ── Coleção de checks ──────────────────────────────────────────────────
    $checks = [];

    // ─────────────────────────────────────────────────────────────────────
    // CHECK 1: Imports pendentes de aprovação há > 3 dias
    // ─────────────────────────────────────────────────────────────────────
    $t_logs = $prefix . 'rmbz_distro_import_logs';
    if ( $wpdb->get_var("SHOW TABLES LIKE '$t_logs'") === $t_logs ) {
        $stuck_imports = $wpdb->get_results(
            "SELECT id, source_name, reporting_period, file_hash, imported_at,
                    DATEDIFF(CURRENT_DATE(), DATE(imported_at)) AS days_old,
                    COALESCE(total_amount_usd, 0) AS amount
               FROM $t_logs
              WHERE approval_status = 'pending_approval'
                AND (status IS NULL OR status = 'active')
                AND DATEDIFF(CURRENT_DATE(), DATE(imported_at)) > 3
              ORDER BY imported_at ASC
              LIMIT 20"
        );
        if ( ! empty($stuck_imports) ) {
            $total_stuck = 0;
            foreach ( $stuck_imports as $si ) $total_stuck += (float) $si->amount;
            $details = [];
            foreach ( $stuck_imports as $si ) {
                $details[] = [
                    'label' => $si->source_name . ' · ' . $si->reporting_period,
                    'meta'  => $si->days_old . ' dias atrás · $' . number_format( (float) $si->amount, 2),
                    'url'   => admin_url('admin.php?page=rmbz-admin-distribution&tab=royalties&rsub=imports'),
                ];
            }
            $checks[] = [
                'severity' => count($stuck_imports) >= 5 ? 'critical' : 'warning',
                'icon'     => '⏳',
                'title'    => count($stuck_imports) . ' import' . (count($stuck_imports) === 1 ? '' : 's') . ' pendente' . (count($stuck_imports) === 1 ? '' : 's') . ' há mais de 3 dias',
                'desc'     => 'Os artistas não vão ver este dinheiro até aprovares: $' . number_format($total_stuck, 2) . ' USD em espera.',
                'details'  => $details,
                'action_label' => 'Ir a Importações',
                'action_url'   => admin_url('admin.php?page=rmbz-admin-distribution&tab=royalties&rsub=imports'),
            ];
        }
    }

    // ─────────────────────────────────────────────────────────────────────
    // CHECK 2: Releases LIVE sem reportarem há > 60 dias
    // ─────────────────────────────────────────────────────────────────────
    $t_releases = $prefix . 'rmbz_distro_releases';
    $t_tracks   = $prefix . 'rmbz_distro_tracks';
    if ( $wpdb->get_var("SHOW TABLES LIKE '$t_releases'") === $t_releases ) {
        $cols_t = $wpdb->get_col("SHOW COLUMNS FROM $t_tracks", 0);
        if ( in_array('last_seen_in_csv', $cols_t, true) ) {
            $silent_releases = $wpdb->get_results( $wpdb->prepare(
                "SELECT r.id, r.title, r.artist_name, r.status,
                        DATEDIFF(CURRENT_DATE(), MAX(t.last_seen_in_csv)) AS days_since
                   FROM $t_releases r
              LEFT JOIN $t_tracks t ON t.release_id = r.id
                  WHERE r.status IN ('live', 'distribuido')
                    AND ( t.last_seen_in_csv IS NULL OR t.last_seen_in_csv < %s )
                  GROUP BY r.id
                 HAVING days_since > 60 OR days_since IS NULL
                  ORDER BY days_since DESC
                  LIMIT 10",
                date('Y-m-d', strtotime('-60 days'))
            ) );
            // Filtrar: deve ter pelo menos 1 track
            $silent_releases = array_filter( $silent_releases, function ($r) {
                return $r->days_since !== null;
            } );
            if ( ! empty($silent_releases) ) {
                $details = [];
                foreach ( $silent_releases as $sr ) {
                    $details[] = [
                        'label' => $sr->title . ' · ' . $sr->artist_name,
                        'meta'  => $sr->days_since . ' dias sem reportar',
                        'url'   => admin_url('admin.php?page=rmbz-admin-distribution&tab=approval&release_id=' . $sr->id),
                    ];
                }
                $checks[] = [
                    'severity' => 'warning',
                    'icon'     => '🔇',
                    'title'    => count($silent_releases) . ' release' . (count($silent_releases) === 1 ? '' : 's') . ' LIVE sem reportar há > 60 dias',
                    'desc'     => 'Pode indicar que a loja removeu o release, ou problema de tracking. Verifica.',
                    'details'  => $details,
                    'action_label' => 'Ver Faixas Silenciosas',
                    'action_url'   => admin_url('admin.php?page=rmbz-admin-distribution&tab=royalties&rsub=silent'),
                ];
            }
        }
    }

    // ─────────────────────────────────────────────────────────────────────
    // CHECK 3: Faixas sem ISRC em releases publicados
    // ─────────────────────────────────────────────────────────────────────
    if ( $wpdb->get_var("SHOW TABLES LIKE '$t_tracks'") === $t_tracks ) {
        $no_isrc = $wpdb->get_results(
            "SELECT t.id, t.track_title, r.title AS release_title, r.artist_name, r.status
               FROM $t_tracks t
          LEFT JOIN $t_releases r ON r.id = t.release_id
              WHERE (t.isrc_code IS NULL OR t.isrc_code = '')
                AND r.status IN ('live', 'distribuido', 'delivered')
              ORDER BY r.id DESC
              LIMIT 15"
        );
        if ( ! empty($no_isrc) ) {
            $details = [];
            foreach ( $no_isrc as $ni ) {
                $details[] = [
                    'label' => $ni->track_title . ' · ' . $ni->release_title,
                    'meta'  => $ni->artist_name . ' · ' . $ni->status,
                    'url'   => admin_url('admin.php?page=rmbz-admin-distribution&tab=approval&release_id=' . ( $ni->release_title ? '#track-' . $ni->id : '' ) ),
                ];
            }
            $checks[] = [
                'severity' => 'critical',
                'icon'     => '🚨',
                'title'    => count($no_isrc) . ' faixa' . (count($no_isrc) === 1 ? '' : 's') . ' sem ISRC em release' . (count($no_isrc) === 1 ? '' : 's') . ' ao vivo',
                'desc'     => 'Sem ISRC, não há royalties associados. Atribui ISRC manualmente ou via auto-gerador.',
                'details'  => $details,
                'action_label' => 'Ir a Releases',
                'action_url'   => admin_url('admin.php?page=rmbz-admin-distribution&tab=approval'),
            ];
        }
    }

    // ─────────────────────────────────────────────────────────────────────
    // CHECK 4: Releases pendentes de revisão há > 14 dias
    // ─────────────────────────────────────────────────────────────────────
    if ( $wpdb->get_var("SHOW TABLES LIKE '$t_releases'") === $t_releases ) {
        $stuck_releases = $wpdb->get_results(
            "SELECT id, title, artist_name, status,
                    DATEDIFF(CURRENT_DATE(), DATE(created_at)) AS days_old
               FROM $t_releases
              WHERE status IN ('pendente', 'pending', 'submitted', 'returned')
                AND DATEDIFF(CURRENT_DATE(), DATE(created_at)) > 14
              ORDER BY created_at ASC
              LIMIT 10"
        );
        if ( ! empty($stuck_releases) ) {
            $details = [];
            foreach ( $stuck_releases as $sr ) {
                $details[] = [
                    'label' => $sr->title . ' · ' . $sr->artist_name,
                    'meta'  => $sr->days_old . ' dias parado · ' . $sr->status,
                    'url'   => admin_url('admin.php?page=rmbz-admin-distribution&tab=approval&release_id=' . $sr->id),
                ];
            }
            $checks[] = [
                'severity' => 'warning',
                'icon'     => '📋',
                'title'    => count($stuck_releases) . ' release' . (count($stuck_releases) === 1 ? '' : 's') . ' à espera de revisão há > 14 dias',
                'desc'     => 'Artistas podem ficar frustrados. Aprova, devolve ou rejeita.',
                'details'  => $details,
                'action_label' => 'Ir a Releases',
                'action_url'   => admin_url('admin.php?page=rmbz-admin-distribution&tab=approval'),
            ];
        }
    }

    // ─────────────────────────────────────────────────────────────────────
    // CHECK 5: Matches em revisão há > 7 dias
    // ─────────────────────────────────────────────────────────────────────
    $t_mr = $prefix . 'rmbz_distro_match_review';
    if ( $wpdb->get_var("SHOW TABLES LIKE '$t_mr'") === $t_mr ) {
        $stuck_matches = (int) $wpdb->get_var(
            "SELECT COUNT(DISTINCT CONCAT(IFNULL(suggested_track_id,'NULL'), '|', csv_isrc, '|', match_method))
               FROM $t_mr
              WHERE status = 'pending'
                AND DATEDIFF(CURRENT_DATE(), DATE(created_at)) > 7"
        );
        if ( $stuck_matches > 0 ) {
            $total_stuck_amount = (float) $wpdb->get_var(
                "SELECT COALESCE(SUM(csv_amount_usd), 0)
                   FROM $t_mr
                  WHERE status = 'pending'
                    AND DATEDIFF(CURRENT_DATE(), DATE(created_at)) > 7"
            );
            $checks[] = [
                'severity' => $stuck_matches >= 50 ? 'critical' : 'warning',
                'icon'     => '🧩',
                'title'    => $stuck_matches . ' match' . ($stuck_matches === 1 ? '' : 'es') . ' em revisão há > 7 dias',
                'desc'     => 'Receita afectada: $' . number_format($total_stuck_amount, 2) . ' USD. Usa o botão "Aprovar todos ≥90%" para acelerar.',
                'details'  => [],
                'action_label' => 'Ir a Matches a Rever',
                'action_url'   => admin_url('admin.php?page=rmbz-admin-distribution&tab=match_review'),
            ];
        }
    }

    // ─────────────────────────────────────────────────────────────────────
    // CHECK 6: Artistas com saldo > $5 sem método de pagamento
    // ─────────────────────────────────────────────────────────────────────
    $t_wallets = $prefix . 'rmbz_wallets';
    $t_ledger  = $prefix . 'rmbz_distro_ledger';
    if ( $wpdb->get_var("SHOW TABLES LIKE '$t_wallets'") === $t_wallets ) {
        $cols_w = $wpdb->get_col("SHOW COLUMNS FROM $t_wallets", 0);
        // Coluna do método de pagamento — variável conforme schema; tenta vários nomes comuns
        $payment_col = null;
        foreach ( ['payment_method', 'metodo_pagamento', 'mpesa_number', 'wallet_provider'] as $c ) {
            if ( in_array($c, $cols_w, true) ) { $payment_col = $c; break; }
        }
        if ( $payment_col && $wpdb->get_var("SHOW TABLES LIKE '$t_ledger'") === $t_ledger ) {
            // Saldo = SUM(ledger.amount) - SUM(payouts.amount_usd)
            $t_payouts = $prefix . 'rmbz_distro_payouts';
            $needy = $wpdb->get_results(
                "SELECT w.user_telemovel, w.user_nome,
                        COALESCE((SELECT SUM(amount) FROM $t_ledger WHERE artist_telemovel = w.user_telemovel AND reverted_at IS NULL), 0)
                        - COALESCE((SELECT SUM(amount_usd) FROM $t_payouts WHERE artist_telemovel = w.user_telemovel AND status = 'completed' AND reverted_at IS NULL), 0) AS saldo
                   FROM $t_wallets w
                  WHERE ( w.$payment_col IS NULL OR w.$payment_col = '' )
              ORDER BY saldo DESC"
            );
            $needy = array_filter( $needy, function ($n) { return (float) $n->saldo >= 5.0; } );
            $needy = array_slice( $needy, 0, 15 );
            if ( ! empty($needy) ) {
                $total_needy = 0;
                $details = [];
                foreach ( $needy as $n ) {
                    $total_needy += (float) $n->saldo;
                    $details[] = [
                        'label' => $n->user_nome ?: $n->user_telemovel,
                        'meta'  => '$' . number_format( (float) $n->saldo, 2) . ' por pagar',
                        'url'   => admin_url('admin.php?page=rmbz-admin-distribution&tab=royalties&rsub=artist_summary&artist=' . urlencode($n->user_telemovel)),
                    ];
                }
                $checks[] = [
                    'severity' => 'info',
                    'icon'     => '💳',
                    'title'    => count($needy) . ' artista' . (count($needy) === 1 ? '' : 's') . ' com saldo sem método de pagamento',
                    'desc'     => 'Total parado por pagar: $' . number_format($total_needy, 2) . ' USD. Pede aos artistas para configurarem M-Pesa/e-Mola no app.',
                    'details'  => $details,
                    'action_label' => 'Ver detalhes',
                    'action_url'   => admin_url('admin.php?page=rmbz-admin-distribution&tab=royalties&rsub=payouts'),
                ];
            }
        }
    }

    // ─────────────────────────────────────────────────────────────────────
    // Contadores por severidade
    // ─────────────────────────────────────────────────────────────────────
    $count_critical = 0; $count_warning = 0; $count_info = 0;
    foreach ( $checks as $c ) {
        if ( $c['severity'] === 'critical' ) $count_critical++;
        elseif ( $c['severity'] === 'warning' ) $count_warning++;
        else $count_info++;
    }
    // Ordenar: critical primeiro
    usort( $checks, function ($a, $b) {
        $order = ['critical' => 0, 'warning' => 1, 'info' => 2];
        return ($order[$a['severity']] ?? 3) <=> ($order[$b['severity']] ?? 3);
    } );

    $is_healthy = empty($checks);
    ?>

    <div class="rsh-page">

        <div class="rsh-head">
            <h2>🩺 Saúde do sistema</h2>
            <p>Verificações activas — coisas que merecem a tua atenção</p>
        </div>

        <?php // ── Banner geral ──────────────────────────────────────────── ?>
        <?php if ( $is_healthy ): ?>
            <div class="rsh-banner healthy">
                <div class="rsh-banner-icon">✅</div>
                <div class="rsh-banner-text">
                    <strong>Tudo em ordem!</strong>
                    <p>Nenhum problema detectado neste momento. O sistema está saudável.</p>
                </div>
            </div>
        <?php else: ?>
            <div class="rsh-banner alert">
                <div class="rsh-banner-icon">⚠️</div>
                <div class="rsh-banner-text">
                    <strong><?php echo count($checks); ?> verificação<?php echo count($checks) === 1 ? '' : 'ões'; ?> com observações</strong>
                    <p>
                        <?php if ( $count_critical > 0 ): ?>
                            <span class="rsh-badge critical"><?php echo $count_critical; ?> crítico<?php echo $count_critical === 1 ? '' : 's'; ?></span>
                        <?php endif; ?>
                        <?php if ( $count_warning > 0 ): ?>
                            <span class="rsh-badge warning"><?php echo $count_warning; ?> aviso<?php echo $count_warning === 1 ? '' : 's'; ?></span>
                        <?php endif; ?>
                        <?php if ( $count_info > 0 ): ?>
                            <span class="rsh-badge info"><?php echo $count_info; ?> informação<?php echo $count_info === 1 ? '' : 'ões'; ?></span>
                        <?php endif; ?>
                    </p>
                </div>
                <div class="rsh-banner-stamp">
                    Verificado em <?php echo esc_html( date_i18n('d/m/Y H:i') ); ?>
                </div>
            </div>
        <?php endif; ?>

        <?php // ── Lista de checks ───────────────────────────────────────── ?>
        <?php foreach ( $checks as $c ): ?>
            <div class="rsh-check rsh-sev-<?php echo esc_attr($c['severity']); ?>">
                <div class="rsh-check-head">
                    <div class="rsh-check-icon"><?php echo $c['icon']; ?></div>
                    <div class="rsh-check-info">
                        <strong><?php echo esc_html($c['title']); ?></strong>
                        <p><?php echo esc_html($c['desc']); ?></p>
                    </div>
                    <?php if ( ! empty($c['action_url']) ): ?>
                        <a href="<?php echo esc_url($c['action_url']); ?>" class="rsh-check-action button button-primary">
                            <?php echo esc_html( $c['action_label'] ?? 'Ver' ); ?> →
                        </a>
                    <?php endif; ?>
                </div>
                <?php if ( ! empty($c['details']) ): ?>
                    <div class="rsh-check-details">
                        <?php foreach ( $c['details'] as $d ): ?>
                            <a href="<?php echo esc_url($d['url'] ?? '#'); ?>" class="rsh-detail-row">
                                <span class="rsh-detail-label"><?php echo esc_html($d['label']); ?></span>
                                <span class="rsh-detail-meta"><?php echo esc_html($d['meta']); ?></span>
                            </a>
                        <?php endforeach; ?>
                    </div>
                <?php endif; ?>
            </div>
        <?php endforeach; ?>

        <?php // ── Rodapé ──────────────────────────────────────────────── ?>
        <div class="rsh-foot">
            <small>
                💡 Esta página é regenerada a cada visita. Para refrescar, recarrega a página.
                Os checks são <strong>view-only</strong> — não fazem alterações automáticas.
            </small>
        </div>

    </div>

    <style>
    .rsh-page { font-family: -apple-system, system-ui, sans-serif; max-width: 1100px; }
    .rsh-head h2 { margin: 0 0 4px 0; font-size: 18px; }
    .rsh-head p { margin: 0 0 16px 0; color: #6b7280; font-size: 12px; }

    .rsh-banner {
        display: flex; align-items: center; gap: 16px;
        padding: 16px 20px;
        border-radius: 10px; margin-bottom: 16px;
    }
    .rsh-banner.healthy {
        background: linear-gradient(135deg, #ecfdf5 0%, #d1fae5 100%);
        border: 1px solid #6ee7b7;
        border-left: 4px solid #10b981;
    }
    .rsh-banner.alert {
        background: linear-gradient(135deg, #fffbeb 0%, #fef3c7 100%);
        border: 1px solid #fcd34d;
        border-left: 4px solid #f59e0b;
    }
    .rsh-banner-icon { font-size: 32px; line-height: 1; }
    .rsh-banner-text { flex: 1; }
    .rsh-banner-text strong { display: block; font-size: 15px; color: #1a1a1a; }
    .rsh-banner-text p { margin: 4px 0 0 0; font-size: 12px; }
    .rsh-banner-text p span { margin-right: 6px; }
    .rsh-banner-stamp {
        font-size: 11px; color: #6b7280;
        font-family: ui-monospace, monospace;
    }
    .rsh-badge {
        display: inline-block;
        padding: 2px 9px; border-radius: 11px;
        font-size: 11px; font-weight: 700;
    }
    .rsh-badge.critical { background: #fee2e2; color: #991b1b; }
    .rsh-badge.warning  { background: #fef3c7; color: #92400e; }
    .rsh-badge.info     { background: #dbeafe; color: #1e40af; }

    .rsh-check {
        background: #fff;
        border: 1px solid #e5e7eb;
        border-radius: 10px;
        margin-bottom: 12px;
        overflow: hidden;
    }
    .rsh-check.rsh-sev-critical { border-left: 4px solid #dc2626; }
    .rsh-check.rsh-sev-warning  { border-left: 4px solid #f59e0b; }
    .rsh-check.rsh-sev-info     { border-left: 4px solid #3b82f6; }

    .rsh-check-head {
        display: flex; align-items: center; gap: 14px;
        padding: 14px 18px;
    }
    .rsh-check-icon { font-size: 24px; line-height: 1; flex-shrink: 0; }
    .rsh-check-info { flex: 1; min-width: 0; }
    .rsh-check-info strong { display: block; font-size: 14px; color: #1a1a1a; margin-bottom: 3px; }
    .rsh-check-info p { margin: 0; font-size: 12px; color: #6b7280; line-height: 1.5; }
    .rsh-check-action {
        flex-shrink: 0;
        white-space: nowrap;
        font-size: 12px !important;
    }

    .rsh-check-details {
        background: #fafafa;
        border-top: 1px solid #f3f4f6;
        padding: 4px 0;
    }
    .rsh-detail-row {
        display: flex;
        justify-content: space-between;
        align-items: center;
        gap: 12px;
        padding: 7px 18px;
        border-bottom: 1px solid #f3f4f6;
        font-size: 12px;
        color: inherit;
        text-decoration: none;
        transition: background 0.1s;
    }
    .rsh-detail-row:last-child { border-bottom: none; }
    .rsh-detail-row:hover { background: #fffbeb; }
    .rsh-detail-label {
        color: #1a1a1a;
        font-weight: 500;
        flex: 1;
        white-space: nowrap;
        overflow: hidden;
        text-overflow: ellipsis;
    }
    .rsh-detail-meta {
        color: #6b7280;
        font-size: 11px;
        font-variant-numeric: tabular-nums;
        flex-shrink: 0;
    }

    .rsh-foot {
        margin-top: 18px;
        padding: 10px 14px;
        background: #f9fafb;
        border-radius: 6px;
        color: #6b7280;
        font-size: 11.5px;
        line-height: 1.6;
    }
    </style>
    <?php
}
