<?php
/**
 * [FEAT 1.6.26] VISÃO POR DSP (LOJA)
 *
 * Dashboard que mostra como o catálogo da 1MZ rende em cada loja
 * (Spotify, Apple Music, YouTube Music, Audiomack, etc).
 *
 * Útil para:
 *   - Decidir onde focar promoção
 *   - Detectar lojas que pararam de reportar
 *   - Comparar tendências entre lojas
 *
 * Acedida via:
 *   ?page=rmbz-admin-distribution&tab=royalties&rsub=by_dsp
 *
 * Fonte de dados: `rmbz_distro_daily_stats` (já tem `store` como coluna).
 */

defined('ABSPATH') or exit;


function rmbz_render_royalties_by_dsp() {
    global $wpdb;
    $t_daily   = $wpdb->prefix . 'rmbz_distro_daily_stats';
    $t_wallets = $wpdb->prefix . 'rmbz_wallets';

    if ( $wpdb->get_var("SHOW TABLES LIKE '$t_daily'") !== $t_daily ) {
        echo '<div class="notice notice-warning"><p>Tabela <code>rmbz_distro_daily_stats</code> ainda não existe. Importa pelo menos um CSV para começar.</p></div>';
        return;
    }

    // ── KPIs globais (para % de cada loja) ────────────────────────────────
    $total_all = (float) $wpdb->get_var(
        "SELECT COALESCE(SUM(sales),0) FROM $t_daily WHERE pending_approval = 0"
    );
    $total_streams = (int) $wpdb->get_var(
        "SELECT COALESCE(SUM(streams),0) FROM $t_daily WHERE pending_approval = 0"
    );

    // ── Janela: últimos 6 meses (para crescimento)  ───────────────────────
    $month_curr_start = date('Y-m-01', strtotime('-1 month'));   // mês anterior (completo)
    $month_curr_end   = date('Y-m-t',  strtotime('-1 month'));
    $month_prev_start = date('Y-m-01', strtotime('-2 months'));
    $month_prev_end   = date('Y-m-t',  strtotime('-2 months'));

    // ── Por loja (TOTAL acumulado) ────────────────────────────────────────
    $by_dsp = $wpdb->get_results(
        "SELECT store,
                SUM(streams) AS streams,
                SUM(sales) AS revenue,
                COUNT(DISTINCT track_id) AS tracks,
                COUNT(DISTINCT artist_telemovel) AS artists,
                COUNT(DISTINCT country_code) AS countries
           FROM $t_daily
          WHERE pending_approval = 0 AND store IS NOT NULL AND store != ''
          GROUP BY store
          ORDER BY revenue DESC"
    );

    // ── Por loja × mês actual + mês anterior (crescimento) ────────────────
    $curr_by_dsp = $wpdb->get_results( $wpdb->prepare(
        "SELECT store, COALESCE(SUM(sales),0) AS revenue
           FROM $t_daily
          WHERE pending_approval = 0
            AND report_date BETWEEN %s AND %s
          GROUP BY store",
        $month_curr_start, $month_curr_end
    ), OBJECT_K );
    $prev_by_dsp = $wpdb->get_results( $wpdb->prepare(
        "SELECT store, COALESCE(SUM(sales),0) AS revenue
           FROM $t_daily
          WHERE pending_approval = 0
            AND report_date BETWEEN %s AND %s
          GROUP BY store",
        $month_prev_start, $month_prev_end
    ), OBJECT_K );

    // ── Histórico mensal — últimos 12 meses ──────────────────────────────
    $hist_rows = $wpdb->get_results(
        "SELECT DATE_FORMAT(report_date, '%Y-%m') AS ym,
                store,
                SUM(sales) AS revenue
           FROM $t_daily
          WHERE pending_approval = 0
            AND report_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
            AND store IS NOT NULL AND store != ''
          GROUP BY ym, store
          ORDER BY ym ASC"
    );
    // Indexar: [ym][store] = revenue
    $hist_by_month = [];
    $hist_all_stores = [];
    $hist_months = [];
    foreach ( $hist_rows as $h ) {
        $hist_by_month[ $h->ym ][ $h->store ] = (float) $h->revenue;
        $hist_all_stores[ $h->store ] = true;
        $hist_months[ $h->ym ] = true;
    }
    // Garantir 12 meses contínuos
    $months_window = [];
    for ( $i = 11; $i >= 0; $i-- ) {
        $months_window[] = date('Y-m', strtotime("-{$i} months"));
    }

    // Top 5 lojas por receita acumulada (para o gráfico — outros agrupados em "Outras")
    $top_stores = array_slice(
        array_map(function($d){ return $d->store; }, $by_dsp),
        0, 5
    );

    // ── Mês actual = "este mês em curso" (NÃO usar para média, usar mês anterior) ─
    $months_pt = ['', 'Janeiro', 'Fevereiro', 'Março', 'Abril', 'Maio', 'Junho',
                  'Julho', 'Agosto', 'Setembro', 'Outubro', 'Novembro', 'Dezembro'];
    [$cy, $cm] = explode('-', date('Y-m', strtotime('-1 month')));
    $ref_month_label = ($months_pt[(int)$cm] ?? '') . ' ' . $cy;
    [$py, $pm] = explode('-', date('Y-m', strtotime('-2 months')));
    $prev_month_label = ($months_pt[(int)$pm] ?? '') . ' ' . $py;
    ?>

    <div class="rbd-page">

        <div class="rbd-head">
            <h2>📦 Visão por DSP (Loja)</h2>
            <p>Como o catálogo rende em cada plataforma · todos os tempos</p>
        </div>

        <?php // ── KPIs principais ─────────────────────────────────────── ?>
        <div class="rbd-kpis">
            <div class="rbd-kpi green">
                <div class="lbl">💰 Receita total</div>
                <div class="val">$<?php echo number_format($total_all, 2); ?></div>
                <div class="sub">acumulado · todas as lojas</div>
            </div>
            <div class="rbd-kpi blue">
                <div class="lbl">🎵 Streams totais</div>
                <div class="val"><?php echo number_format($total_streams); ?></div>
                <div class="sub">acumulado · todas as lojas</div>
            </div>
            <div class="rbd-kpi purple">
                <div class="lbl">📦 Lojas activas</div>
                <div class="val"><?php echo count($by_dsp); ?></div>
                <div class="sub">com pelo menos 1 stream</div>
            </div>
            <div class="rbd-kpi amber">
                <div class="lbl">🏆 Loja líder</div>
                <div class="val"><?php echo $by_dsp ? esc_html( $by_dsp[0]->store ) : '—'; ?></div>
                <div class="sub">
                    <?php if ( ! empty($by_dsp) && $total_all > 0 ): ?>
                        <?php echo number_format($by_dsp[0]->revenue / $total_all * 100, 1); ?>% do total
                    <?php endif; ?>
                </div>
            </div>
        </div>

        <?php // ── Gráfico empilhado mensal ─────────────────────────────── ?>
        <?php if ( ! empty($months_window) && ! empty($top_stores) ): ?>
        <div class="rbd-card">
            <div class="rbd-card-head">
                <strong>📈 Receita mensal por loja · últimos 12 meses</strong>
                <span>top 5 lojas + outras</span>
            </div>
            <div class="rbd-stacked-chart">
                <?php
                // Encontrar máximo mensal para escalar barras
                $max_month_sum = 0;
                foreach ( $months_window as $ym ) {
                    $sum_m = 0;
                    foreach ( $hist_by_month[$ym] ?? [] as $store => $rev ) {
                        $sum_m += $rev;
                    }
                    if ( $sum_m > $max_month_sum ) $max_month_sum = $sum_m;
                }
                // Paleta para cada loja (gera por hash do nome)
                $store_colors = [];
                $palette = ['#10b981', '#3b82f6', '#f0b90b', '#8b5cf6', '#ec4899', '#6b7280'];
                foreach ( $top_stores as $i => $st ) {
                    $store_colors[$st] = $palette[$i % count($palette)] ?? '#6b7280';
                }
                $store_colors['__outras__'] = '#9ca3af';

                foreach ( $months_window as $ym ):
                    $mdata = $hist_by_month[$ym] ?? [];
                    $sum_m = array_sum($mdata);
                    $h_pct = $max_month_sum > 0 ? ( $sum_m / $max_month_sum * 100 ) : 0;
                ?>
                    <div class="rbd-bar">
                        <div class="bar-stack" style="height: <?php echo number_format($h_pct, 1); ?>%;">
                            <?php
                            if ( $sum_m > 0 ):
                                // Segmentos por loja: top 5 + agregar resto como "Outras"
                                $others = 0;
                                $segments = [];
                                foreach ( $mdata as $st => $rev ) {
                                    if ( in_array($st, $top_stores, true) ) {
                                        $segments[$st] = $rev;
                                    } else {
                                        $others += $rev;
                                    }
                                }
                                if ( $others > 0 ) $segments['__outras__'] = $others;
                                arsort( $segments );
                                foreach ( $segments as $st => $rev ):
                                    $seg_pct = ( $rev / $sum_m ) * 100;
                                    $color = $store_colors[$st] ?? '#6b7280';
                                    $label = $st === '__outras__' ? 'Outras' : $st;
                                    $tooltip = $label . ': $' . number_format($rev, 4);
                            ?>
                                <div class="bar-seg" style="height: <?php echo number_format($seg_pct, 1); ?>%; background: <?php echo esc_attr($color); ?>;"
                                     title="<?php echo esc_attr($tooltip); ?>"></div>
                            <?php endforeach; endif; ?>
                        </div>
                        <div class="bar-lbl"><?php echo esc_html(substr($ym, 5, 2)); ?>/<?php echo esc_html(substr($ym, 2, 2)); ?></div>
                        <?php if ( $sum_m > 0 ): ?>
                            <div class="bar-val">$<?php echo number_format($sum_m, 0); ?></div>
                        <?php endif; ?>
                    </div>
                <?php endforeach; ?>
            </div>
            <div class="rbd-legend">
                <?php foreach ( $top_stores as $st ): ?>
                    <span class="rbd-legend-item">
                        <span class="rbd-legend-dot" style="background: <?php echo esc_attr($store_colors[$st]); ?>;"></span>
                        <?php echo esc_html($st); ?>
                    </span>
                <?php endforeach; ?>
                <span class="rbd-legend-item">
                    <span class="rbd-legend-dot" style="background: <?php echo esc_attr($store_colors['__outras__']); ?>;"></span>
                    Outras
                </span>
            </div>
        </div>
        <?php endif; ?>

        <?php // ── Tabela por DSP ─────────────────────────────────────── ?>
        <div class="rbd-card">
            <div class="rbd-card-head">
                <strong>📋 Detalhe por loja</strong>
                <span>ordenado por receita acumulada</span>
            </div>

            <?php if ( empty($by_dsp) ): ?>
                <div class="rbd-empty">
                    Nenhuma loja com dados ainda. Importa um CSV para começar.
                </div>
            <?php else: ?>
            <table class="rbd-table">
                <thead>
                    <tr>
                        <th>Loja</th>
                        <th class="num">Receita total</th>
                        <th class="num">% do total</th>
                        <th class="num">Streams</th>
                        <th class="num">Faixas</th>
                        <th class="num">Artistas</th>
                        <th class="num">Países</th>
                        <th class="num">
                            <?php echo esc_html($ref_month_label); ?>
                            <br><small style="font-weight:400; opacity:0.7;">vs <?php echo esc_html($prev_month_label); ?></small>
                        </th>
                    </tr>
                </thead>
                <tbody>
                <?php foreach ( $by_dsp as $d ):
                    $pct = $total_all > 0 ? ($d->revenue / $total_all) * 100 : 0;
                    $curr_rev = isset($curr_by_dsp[$d->store]) ? (float) $curr_by_dsp[$d->store]->revenue : 0;
                    $prev_rev = isset($prev_by_dsp[$d->store]) ? (float) $prev_by_dsp[$d->store]->revenue : 0;
                    if ( $prev_rev > 0 ) {
                        $delta = ( ( $curr_rev - $prev_rev ) / $prev_rev ) * 100;
                    } elseif ( $curr_rev > 0 ) {
                        $delta = 100; // surgir do nada = 100% crescimento (fallback)
                    } else {
                        $delta = 0;
                    }
                ?>
                    <tr>
                        <td><strong><?php echo esc_html($d->store); ?></strong></td>
                        <td class="num"><strong>$<?php echo number_format((float) $d->revenue, 4); ?></strong></td>
                        <td class="num">
                            <div class="rbd-bar-wrap">
                                <span class="rbd-bar-pct-bar" style="width: <?php echo number_format($pct, 1); ?>%;"></span>
                                <span class="rbd-bar-pct-text"><?php echo number_format($pct, 1); ?>%</span>
                            </div>
                        </td>
                        <td class="num"><?php echo number_format((int) $d->streams); ?></td>
                        <td class="num"><?php echo (int) $d->tracks; ?></td>
                        <td class="num"><?php echo (int) $d->artists; ?></td>
                        <td class="num"><?php echo (int) $d->countries; ?></td>
                        <td class="num">
                            <div><strong>$<?php echo number_format($curr_rev, 2); ?></strong></div>
                            <?php if ( $curr_rev > 0 || $prev_rev > 0 ): ?>
                                <small class="<?php echo $delta >= 0 ? 'rbd-up' : 'rbd-down'; ?>">
                                    <?php echo $delta >= 0 ? '⬆' : '⬇'; ?>
                                    <?php echo number_format(abs($delta), 0); ?>%
                                </small>
                            <?php endif; ?>
                        </td>
                    </tr>
                <?php endforeach; ?>
                </tbody>
            </table>
            <?php endif; ?>
        </div>

    </div>

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

    .rbd-kpis {
        display: grid;
        grid-template-columns: repeat(auto-fit, minmax(180px, 1fr));
        gap: 10px; margin-bottom: 16px;
    }
    .rbd-kpi {
        background: #fff; border: 1px solid #e5e5e5;
        border-left: 3px solid #ccc;
        border-radius: 8px; padding: 12px 16px;
    }
    .rbd-kpi.green  { border-left-color: #10b981; }
    .rbd-kpi.blue   { border-left-color: #3b82f6; }
    .rbd-kpi.purple { border-left-color: #8b5cf6; }
    .rbd-kpi.amber  { border-left-color: #f0b90b; }
    .rbd-kpi .lbl { font-size: 10px; color: #888; text-transform: uppercase; letter-spacing: 0.3px; font-weight: 600; }
    .rbd-kpi .val { font-size: 22px; font-weight: 600; margin: 4px 0 2px; color: #1a1a1a; font-variant-numeric: tabular-nums; }
    .rbd-kpi .sub { font-size: 11px; color: #666; }

    .rbd-card {
        background: #fff; border: 1px solid #e5e5e5;
        border-radius: 8px; padding: 14px 16px; margin-bottom: 14px;
    }
    .rbd-card-head {
        display: flex; justify-content: space-between;
        align-items: center; margin-bottom: 12px;
    }
    .rbd-card-head strong { font-size: 13px; color: #1a1a1a; }
    .rbd-card-head span { font-size: 11px; color: #888; }

    /* Gráfico stacked */
    .rbd-stacked-chart {
        display: flex; align-items: flex-end;
        gap: 6px; height: 200px;
        margin-bottom: 10px;
        padding: 6px 0;
    }
    .rbd-bar {
        flex: 1; min-width: 40px;
        display: flex; flex-direction: column;
        align-items: center; justify-content: flex-end;
        height: 100%;
        position: relative;
    }
    .rbd-bar .bar-stack {
        width: 100%; display: flex; flex-direction: column-reverse;
        background: #f3f4f6;
        border-radius: 4px 4px 0 0;
        overflow: hidden;
        min-height: 1px;
    }
    .rbd-bar .bar-seg {
        width: 100%;
        transition: opacity 0.15s;
        cursor: help;
    }
    .rbd-bar .bar-seg:hover { opacity: 0.85; }
    .rbd-bar .bar-lbl {
        font-size: 9px; color: #6b7280;
        margin-top: 4px;
        font-family: ui-monospace, monospace;
    }
    .rbd-bar .bar-val {
        font-size: 9px; color: #1a1a1a; font-weight: 600;
        font-variant-numeric: tabular-nums;
    }

    .rbd-legend {
        display: flex; gap: 14px; flex-wrap: wrap;
        padding: 8px 0; font-size: 11px; color: #4b5563;
    }
    .rbd-legend-item { display: inline-flex; align-items: center; gap: 5px; }
    .rbd-legend-dot {
        display: inline-block; width: 10px; height: 10px;
        border-radius: 50%;
    }

    /* Tabela */
    .rbd-table { width: 100%; border-collapse: collapse; font-size: 12px; }
    .rbd-table th {
        background: #fafafa; padding: 10px 12px; text-align: left;
        font-size: 10px; text-transform: uppercase;
        color: #666; font-weight: 600;
        border-bottom: 1px solid #e5e5e5;
    }
    .rbd-table th.num, .rbd-table td.num { text-align: right; font-variant-numeric: tabular-nums; }
    .rbd-table td { padding: 10px 12px; border-bottom: 1px solid #f3f4f6; vertical-align: middle; }
    .rbd-table tr:last-child td { border-bottom: none; }
    .rbd-table tr:hover { background: #fffbeb; }

    .rbd-bar-wrap {
        position: relative;
        display: inline-block; width: 100%;
        background: #f3f4f6;
        height: 14px;
        border-radius: 7px; overflow: hidden;
    }
    .rbd-bar-pct-bar {
        position: absolute; left: 0; top: 0; bottom: 0;
        background: linear-gradient(90deg, #10b981, #059669);
        min-width: 1px;
    }
    .rbd-bar-pct-text {
        position: relative; z-index: 2;
        font-size: 10px; color: #1a1a1a;
        text-shadow: 0 0 2px #fff;
        line-height: 14px;
        padding-left: 4px;
    }

    .rbd-up   { color: #065f46; font-weight: 600; }
    .rbd-down { color: #991b1b; font-weight: 600; }

    .rbd-empty {
        text-align: center; padding: 30px;
        color: #888; font-size: 13px;
        background: #fafafa; border-radius: 6px;
    }
    </style>
    <?php
}
