<?php
/**
 * [1.5.190] FAIXAS SILENCIOSAS — detector de faixas que não recebem relatórios
 *
 * Categorias detectadas:
 *
 *   🟠 SILENCIOSAS — receberam relatórios antes mas há 3+ meses não recebem
 *      Causa típica: distribuidor parou de enviar relatório, DSP retirou
 *      a música sem aviso, ou falha sistémica no relatório.
 *
 *   🔵 ATRASO INICIAL — release_date >= 3 meses mas NUNCA receberam relatório
 *      Causa típica: faixa nova distribuída há tempo, mas DSPs ainda não
 *      reportaram (atraso de pipeline) OU a entrega aos DSPs nunca aconteceu
 *      e ninguém reparou.
 *
 * Niveis de severidade (ambas categorias):
 *   • amarelo:  3-4 meses
 *   • laranja:  5-6 meses
 *   • vermelho: 7+ meses
 *
 * Apenas para admin — artistas não veem este painel.
 */

if ( ! defined( 'ABSPATH' ) ) exit;


function rmbz_render_royalties_silent_tracks() {
    global $wpdb;

    // [1.6.118] Sub-tabs: Detector (default) | Retro-fix
    $sub = isset( $_GET['sub'] ) ? sanitize_key( $_GET['sub'] ) : 'detector';
    if ( ! in_array( $sub, [ 'detector', 'retrofix' ], true ) ) $sub = 'detector';

    $base_url = admin_url( 'admin.php?page=rmbz-admin-distribution&tab=royalties&rsub=silent' );
    $url_det  = esc_url( $base_url );
    $url_rfx  = esc_url( add_query_arg( 'sub', 'retrofix', $base_url ) );
    ?>
    <h2 class="nav-tab-wrapper" style="margin-bottom:18px;">
        <a href="<?php echo $url_det; ?>" class="nav-tab <?php echo $sub === 'detector' ? 'nav-tab-active' : ''; ?>">
            📊 Detector
        </a>
        <a href="<?php echo $url_rfx; ?>" class="nav-tab <?php echo $sub === 'retrofix' ? 'nav-tab-active' : ''; ?>">
            🔧 Retro-fix
        </a>
    </h2>
    <?php

    // Routing
    if ( $sub === 'retrofix' ) {
        if ( function_exists( 'rmbz_render_silent_retrofix' ) ) {
            rmbz_render_silent_retrofix();
        } else {
            echo '<div class="notice notice-error"><p>Módulo retro-fix não carregado.</p></div>';
        }
        return;
    }

    // ───── Detector original abaixo ─────

    $t_tracks   = $wpdb->prefix . 'rmbz_distro_tracks';
    $t_releases = $wpdb->prefix . 'rmbz_distro_releases';
    $t_wallets  = $wpdb->prefix . 'rmbz_wallets';

    // Verificar se a coluna last_seen_in_csv já foi criada
    $cols_t = $wpdb->get_col( "SHOW COLUMNS FROM $t_tracks" );
    if ( ! in_array( 'last_seen_in_csv', $cols_t, true ) ) {
        echo '<div class="notice notice-warning"><p>';
        echo '⚠ A coluna <code>last_seen_in_csv</code> ainda não foi criada. Recarregue uma página admin para correr a migração automática.';
        echo '</p></div>';
        return;
    }

    // Filtros
    $filter_severity = isset($_GET['sev']) ? sanitize_key($_GET['sev']) : 'all';   // all/yellow/orange/red
    $filter_category = isset($_GET['cat']) ? sanitize_key($_GET['cat']) : 'all';   // all/silent/initial
    $filter_artist   = isset($_GET['artist']) ? sanitize_text_field($_GET['artist']) : '';

    $three_months_ago = date('Y-m-d', strtotime('-3 months'));
    $five_months_ago  = date('Y-m-d', strtotime('-5 months'));
    $seven_months_ago = date('Y-m-d', strtotime('-7 months'));

    // ───────────────────────────────────────────────────────────────────────
    // QUERY: faixas silenciosas (já receberam mas pararam) + atraso inicial
    //
    // Critério para incluir:
    //   1. Release está num estado que devia receber royalties:
    //      live/distribuido/approved
    //   2. (silent)   last_seen_in_csv NOT NULL e <= 3 meses atrás
    //      OU
    //      (initial)  last_seen_in_csv IS NULL E release_date <= 3 meses atrás
    // ───────────────────────────────────────────────────────────────────────

    $where_artist = '';
    $params_artist = [];
    if ( ! empty( $filter_artist ) ) {
        $where_artist = " AND (w.user_nome LIKE %s OR r.artist_telemovel LIKE %s)";
        $params_artist[] = '%' . $wpdb->esc_like( $filter_artist ) . '%';
        $params_artist[] = '%' . $wpdb->esc_like( $filter_artist ) . '%';
    }

    // Considerar apenas releases live/distribuido/approved
    $where_status = "r.status IN ('live','distribuido','approved')";

    $query = "
        SELECT
            t.id              AS track_id,
            t.track_title     AS track_title,
            t.isrc_code       AS isrc_code,
            t.last_seen_in_csv,
            r.id              AS release_id,
            r.title           AS release_title,
            r.upc_code        AS upc_code,
            r.status          AS release_status,
            r.release_date    AS release_date,
            r.cover_image     AS cover_image,
            r.artist_telemovel AS artist_telemovel,
            COALESCE(w.user_nome, '(sem nome)') AS artist_name,
            CASE
                WHEN t.last_seen_in_csv IS NULL THEN 'initial'
                ELSE 'silent'
            END AS category,
            CASE
                WHEN t.last_seen_in_csv IS NULL THEN
                    CASE
                        WHEN r.release_date <= %s THEN 'red'
                        WHEN r.release_date <= %s THEN 'orange'
                        ELSE 'yellow'
                    END
                ELSE
                    CASE
                        WHEN t.last_seen_in_csv <= %s THEN 'red'
                        WHEN t.last_seen_in_csv <= %s THEN 'orange'
                        ELSE 'yellow'
                    END
            END AS severity,
            CASE
                WHEN t.last_seen_in_csv IS NULL THEN
                    DATEDIFF(CURDATE(), r.release_date)
                ELSE
                    DATEDIFF(CURDATE(), t.last_seen_in_csv)
            END AS days_silent
        FROM $t_tracks t
        INNER JOIN $t_releases r ON t.release_id = r.id
        LEFT JOIN $t_wallets w ON r.artist_telemovel = w.user_telemovel
        WHERE $where_status
          AND r.release_date IS NOT NULL
          AND r.release_date != '0000-00-00'
          AND (
            (t.last_seen_in_csv IS NOT NULL AND t.last_seen_in_csv <= %s)
            OR
            (t.last_seen_in_csv IS NULL AND r.release_date <= %s)
          )
          $where_artist
        ORDER BY days_silent DESC, t.id DESC
        LIMIT 5000
    ";

    $params = array_merge(
        [ $seven_months_ago, $five_months_ago, $seven_months_ago, $five_months_ago, $three_months_ago, $three_months_ago ],
        $params_artist
    );
    $rows = $wpdb->get_results( $wpdb->prepare( $query, $params ) );

    // Aplicar filtros client-side (mais simples que SQL dinâmico)
    if ( $filter_category !== 'all' || $filter_severity !== 'all' ) {
        $rows = array_filter( $rows, function( $r ) use ( $filter_category, $filter_severity ) {
            if ( $filter_category !== 'all' && $r->category !== $filter_category ) return false;
            if ( $filter_severity !== 'all' && $r->severity !== $filter_severity ) return false;
            return true;
        } );
        $rows = array_values( $rows );
    }

    // KPIs
    $total = count( $rows );
    $by_severity = [ 'yellow' => 0, 'orange' => 0, 'red' => 0 ];
    $by_category = [ 'silent' => 0, 'initial' => 0 ];
    $artists_affected = [];
    foreach ( $rows as $r ) {
        $by_severity[ $r->severity ] = ( $by_severity[ $r->severity ] ?? 0 ) + 1;
        $by_category[ $r->category ] = ( $by_category[ $r->category ] ?? 0 ) + 1;
        $artists_affected[ $r->artist_telemovel ] = true;
    }

    // URL builder
    $build_url = function( $changes = [] ) {
        $args = $_GET;
        foreach ( $changes as $k => $v ) {
            if ( $v === null || $v === '' ) unset($args[$k]);
            else $args[$k] = $v;
        }
        return '?' . http_build_query( $args );
    };

    ?>
    <div class="rmbz-st-wrap">

        <div class="rmbz-st-header">
            <div>
                <h2 style="margin:0 0 4px;">🔇 Faixas Silenciosas</h2>
                <p style="margin:0; color:#666; font-size:13px;">
                    Faixas live ou distribuídas que não estão a receber relatórios — possíveis problemas com distribuidor ou DSP.
                    Apenas admins veem esta página; os artistas não são alertados.
                </p>
            </div>
        </div>

        <?php if ( $total === 0 ): ?>
            <div class="rmbz-st-empty">
                <div style="font-size:48px;">🎉</div>
                <h3 style="margin:8px 0;">Tudo a reportar normalmente</h3>
                <p style="margin:0; color:#666;">Nenhuma faixa silenciosa detectada. Boa gestão!</p>
            </div>
        <?php else: ?>

            <div class="rmbz-st-kpis">
                <div class="rmbz-st-kpi">
                    <div class="lbl">Total</div>
                    <div class="val"><?php echo (int) $total; ?></div>
                    <div class="sub">faixa(s) afectada(s)</div>
                </div>
                <div class="rmbz-st-kpi" style="border-left-color:#dc2626;">
                    <div class="lbl">Crítico (7+ meses)</div>
                    <div class="val" style="color:#dc2626;"><?php echo (int) $by_severity['red']; ?></div>
                    <div class="sub">precisa atenção urgente</div>
                </div>
                <div class="rmbz-st-kpi" style="border-left-color:#ea580c;">
                    <div class="lbl">Médio (5-6 meses)</div>
                    <div class="val" style="color:#ea580c;"><?php echo (int) $by_severity['orange']; ?></div>
                </div>
                <div class="rmbz-st-kpi" style="border-left-color:#f59e0b;">
                    <div class="lbl">Recente (3-4 meses)</div>
                    <div class="val" style="color:#f59e0b;"><?php echo (int) $by_severity['yellow']; ?></div>
                </div>
                <div class="rmbz-st-kpi">
                    <div class="lbl">Artistas afectados</div>
                    <div class="val"><?php echo count( $artists_affected ); ?></div>
                </div>
            </div>

            <div class="rmbz-st-filters">
                <span class="rmbz-st-filter-label">Categoria:</span>
                <a href="<?php echo esc_url( $build_url(['cat' => 'all']) ); ?>"
                   class="rmbz-st-filter-btn <?php echo $filter_category === 'all' ? 'active' : ''; ?>">
                    Todas (<?php echo (int) ($by_category['silent'] + $by_category['initial']); ?>)
                </a>
                <a href="<?php echo esc_url( $build_url(['cat' => 'silent']) ); ?>"
                   class="rmbz-st-filter-btn <?php echo $filter_category === 'silent' ? 'active' : ''; ?>">
                    🟠 Pararam de reportar (<?php echo (int) $by_category['silent']; ?>)
                </a>
                <a href="<?php echo esc_url( $build_url(['cat' => 'initial']) ); ?>"
                   class="rmbz-st-filter-btn <?php echo $filter_category === 'initial' ? 'active' : ''; ?>">
                    🔵 Nunca reportaram (<?php echo (int) $by_category['initial']; ?>)
                </a>

                <span class="rmbz-st-filter-label" style="margin-left:18px;">Severidade:</span>
                <a href="<?php echo esc_url( $build_url(['sev' => 'all']) ); ?>"
                   class="rmbz-st-filter-btn <?php echo $filter_severity === 'all' ? 'active' : ''; ?>">Todas</a>
                <a href="<?php echo esc_url( $build_url(['sev' => 'red']) ); ?>"
                   class="rmbz-st-filter-btn <?php echo $filter_severity === 'red' ? 'active sev-red' : ''; ?>">7+ meses</a>
                <a href="<?php echo esc_url( $build_url(['sev' => 'orange']) ); ?>"
                   class="rmbz-st-filter-btn <?php echo $filter_severity === 'orange' ? 'active sev-orange' : ''; ?>">5-6 meses</a>
                <a href="<?php echo esc_url( $build_url(['sev' => 'yellow']) ); ?>"
                   class="rmbz-st-filter-btn <?php echo $filter_severity === 'yellow' ? 'active sev-yellow' : ''; ?>">3-4 meses</a>

                <form method="get" style="display:inline-flex; gap:6px; margin-left:auto;">
                    <?php foreach ( $_GET as $k => $v ): if ( $k === 'artist' ) continue; ?>
                        <input type="hidden" name="<?php echo esc_attr($k); ?>" value="<?php echo esc_attr($v); ?>">
                    <?php endforeach; ?>
                    <input type="text" name="artist" value="<?php echo esc_attr( $filter_artist ); ?>"
                           placeholder="Filtrar por artista..." class="rmbz-st-search">
                    <button type="submit" class="rmbz-st-search-btn">Procurar</button>
                </form>
            </div>

            <div class="rmbz-st-table-wrap">
                <table class="rmbz-st-table">
                    <thead>
                        <tr>
                            <th style="width:48px;"></th>
                            <th>Faixa</th>
                            <th>Artista</th>
                            <th>Status</th>
                            <th>ISRC / UPC</th>
                            <th style="text-align:right;">Sem reportar há</th>
                            <th style="text-align:right; width:120px;">Última vez</th>
                            <th style="width:120px;"></th>
                        </tr>
                    </thead>
                    <tbody>
                    <?php foreach ( $rows as $r ):
                        $sev_color = [ 'red' => '#dc2626', 'orange' => '#ea580c', 'yellow' => '#f59e0b' ][ $r->severity ] ?? '#666';
                        $sev_bg    = [ 'red' => '#fef2f2', 'orange' => '#fff7ed', 'yellow' => '#fffbeb' ][ $r->severity ] ?? '#fafafa';
                        $cat_label = $r->category === 'silent' ? '🟠 parou' : '🔵 nunca';
                        $months    = (int) floor( $r->days_silent / 30 );
                        $last_seen = $r->category === 'initial'
                            ? '— (desde lançamento)'
                            : ( $r->last_seen_in_csv ? date('M Y', strtotime($r->last_seen_in_csv)) : '—' );
                    ?>
                        <tr style="background:<?php echo esc_attr($sev_bg); ?>;">
                            <td>
                                <?php if ( ! empty($r->cover_image) ): ?>
                                    <img src="<?php echo esc_url($r->cover_image); ?>" alt="" class="rmbz-st-cover">
                                <?php else: ?>
                                    <div class="rmbz-st-cover-ph">🎵</div>
                                <?php endif; ?>
                            </td>
                            <td>
                                <strong><?php echo esc_html( $r->track_title ?: '(sem título)' ); ?></strong>
                                <div style="font-size:11px; color:#888;">
                                    do álbum: <?php echo esc_html( $r->release_title ?: '—' ); ?>
                                </div>
                            </td>
                            <td>
                                <?php echo esc_html( $r->artist_name ); ?>
                                <div style="font-size:11px; color:#888;">
                                    <code><?php echo esc_html( $r->artist_telemovel ); ?></code>
                                </div>
                            </td>
                            <td>
                                <span class="rmbz-st-pill"><?php echo esc_html( ucfirst( $r->release_status ) ); ?></span>
                                <div style="font-size:11px; color:#666; margin-top:3px;"><?php echo esc_html( $cat_label ); ?></div>
                            </td>
                            <td style="font-size:11px;">
                                <?php if ( ! empty($r->isrc_code) ): ?>
                                    ISRC: <code><?php echo esc_html($r->isrc_code); ?></code><br>
                                <?php else: ?>
                                    <span style="color:#dc2626;">⚠ sem ISRC</span><br>
                                <?php endif; ?>
                                <?php if ( ! empty($r->upc_code) ): ?>
                                    UPC: <code><?php echo esc_html($r->upc_code); ?></code>
                                <?php endif; ?>
                            </td>
                            <td style="text-align:right;">
                                <strong style="color:<?php echo esc_attr($sev_color); ?>; font-size:14px;">
                                    <?php echo (int) $months; ?> mês<?php echo $months !== 1 ? 'es' : ''; ?>
                                </strong>
                                <div style="font-size:11px; color:#888;">
                                    <?php echo (int) $r->days_silent; ?> dias
                                </div>
                            </td>
                            <td style="text-align:right; font-size:11px; color:#666;">
                                <?php echo esc_html( $last_seen ); ?>
                            </td>
                            <td>
                                <a href="<?php echo esc_url( admin_url('admin.php?page=rmbz-admin-distribution&tab=releases&view=detail&id=' . (int) $r->release_id) ); ?>"
                                   class="rmbz-st-action-btn" target="_blank">
                                    🔍 Ver release
                                </a>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                    </tbody>
                </table>
            </div>

            <p style="margin-top:14px; font-size:12px; color:#666;">
                <strong>O que fazer com cada caso:</strong>
                Para faixas que <em>pararam de reportar</em>, contacta o distribuidor ou verifica se o artista
                ainda tem a música activa nos DSPs. Para faixas que <em>nunca reportaram</em>, confirma se a entrega
                aos DSPs foi concluída — em alguns casos é só atraso de pipeline (1-2 meses normais).
            </p>

        <?php endif; ?>

    </div>

    <style>
    .rmbz-st-wrap { padding:0; }
    .rmbz-st-header { margin-bottom:18px; }
    .rmbz-st-empty {
        background:#fff; border:1px solid #dcdcde; border-radius:8px;
        padding:60px 30px; text-align:center;
    }
    .rmbz-st-kpis {
        display:grid; grid-template-columns:repeat(auto-fit, minmax(180px, 1fr));
        gap:10px; margin-bottom:16px;
    }
    .rmbz-st-kpi {
        background:#fff; border:1px solid #dcdcde; border-radius:6px;
        padding:14px 16px; border-left:4px solid #999;
    }
    .rmbz-st-kpi .lbl { font-size:11px; color:#787c82; text-transform:uppercase; letter-spacing:0.04em; }
    .rmbz-st-kpi .val { font-size:24px; font-weight:700; color:var(--wp-components-color-foreground, #1d2327); margin-top:2px; }
    .rmbz-st-kpi .sub { font-size:11px; color:#787c82; margin-top:2px; }

    .rmbz-st-filters {
        display:flex; gap:6px; flex-wrap:wrap; margin-bottom:14px;
        align-items:center; padding:12px; background:#fafafa;
        border:1px solid #dcdcde; border-radius:6px;
    }
    .rmbz-st-filter-label { font-size:11px; color:#787c82; text-transform:uppercase; letter-spacing:0.04em; font-weight:600; margin-right:4px; }
    .rmbz-st-filter-btn {
        background:#fff; border:1px solid #c3c4c7; color:#50575e !important;
        padding:5px 11px; border-radius:14px; font-size:12px;
        text-decoration:none !important; transition:all 0.15s ease;
    }
    .rmbz-st-filter-btn:hover { background:#f0f0f1; }
    .rmbz-st-filter-btn.active {
        background:var(--wp-admin-theme-color, #2271b1) !important; color:#fff !important;
        border-color:var(--wp-admin-theme-color, #2271b1) !important;
    }
    .rmbz-st-filter-btn.active.sev-red { background:#dc2626 !important; border-color:#dc2626 !important; }
    .rmbz-st-filter-btn.active.sev-orange { background:#ea580c !important; border-color:#ea580c !important; }
    .rmbz-st-filter-btn.active.sev-yellow { background:#f59e0b !important; border-color:#f59e0b !important; color:var(--wp-components-color-foreground, #1d2327) !important; }
    .rmbz-st-search {
        padding:5px 10px; border:1px solid #c3c4c7; border-radius:5px;
        font-size:12px; min-width:200px;
    }
    .rmbz-st-search-btn {
        background:var(--wp-admin-theme-color, #2271b1); color:#fff; border:1px solid var(--wp-admin-theme-color, #2271b1);
        padding:5px 12px; border-radius:5px; font-size:12px; cursor:pointer;
    }

    .rmbz-st-table-wrap {
        background:#fff; border:1px solid #dcdcde; border-radius:6px; overflow:hidden;
    }
    .rmbz-st-table { width:100%; border-collapse:collapse; }
    .rmbz-st-table thead { background:#fafafa; }
    .rmbz-st-table th {
        text-align:left; padding:10px 12px; font-size:11px; color:#787c82;
        text-transform:uppercase; letter-spacing:0.04em; font-weight:600;
        border-bottom:1px solid #dcdcde;
    }
    .rmbz-st-table td {
        padding:10px 12px; border-bottom:1px solid #f0f0f1; font-size:13px;
        vertical-align:middle;
    }
    .rmbz-st-table tr:last-child td { border-bottom:none; }

    .rmbz-st-cover {
        width:42px; height:42px; object-fit:cover; border-radius:4px; display:block;
    }
    .rmbz-st-cover-ph {
        width:42px; height:42px; background:#f0f0f1; border-radius:4px;
        display:flex; align-items:center; justify-content:center; color:#a7aaad; font-size:18px;
    }
    .rmbz-st-pill {
        background:#f0f0f1; color:#646970; padding:2px 8px; border-radius:10px;
        font-size:10px; font-weight:600; text-transform:uppercase;
    }
    .rmbz-st-action-btn {
        background:#fff; color:var(--wp-admin-theme-color, #2271b1) !important; border:1px solid var(--wp-admin-theme-color, #2271b1);
        padding:5px 10px; border-radius:5px; font-size:11px; font-weight:600;
        text-decoration:none !important; display:inline-block;
    }
    .rmbz-st-action-btn:hover {
        background:var(--wp-admin-theme-color, #2271b1); color:#fff !important;
    }
    </style>
    <?php
}
