<?php
// pages/streaming_reports.php
requireRole('any');

// Filters
$month = isset($_GET['month']) ? $_GET['month'] : date('Y-m');
$service_filter = isset($_GET['service_type']) ? $_GET['service_type'] : 'All';

$start_date = $month . '-01';
$end_date = date('Y-m-t', strtotime($start_date));

// Fetch Streams for Legend
$streams = $pdo->query("SELECT id, name FROM youtube_streams")->fetchAll(PDO::FETCH_KEY_PAIR);

// 0. Auto-Migration / Self-Healing
try {
    // 1. YouTube: physical_attendance
    $check_phy = $pdo->query("SHOW COLUMNS FROM youtube_manual_logs LIKE 'physical_attendance'");
    if (!$check_phy->fetch()) {
        $pdo->exec("ALTER TABLE youtube_manual_logs ADD COLUMN `physical_attendance` INT DEFAULT 0");
    }

    // 2. YouTube: service_type
    $check_st = $pdo->query("SHOW COLUMNS FROM youtube_manual_logs LIKE 'service_type'");
    if (!$check_st->fetch()) {
        $pdo->exec("ALTER TABLE youtube_manual_logs ADD COLUMN `service_type` VARCHAR(100) DEFAULT 'General'");
    }

    // 3. Radio: service_type
    $check_rad = $pdo->query("SHOW COLUMNS FROM radio_manual_logs LIKE 'service_type'");
    if (!$check_rad->fetch()) {
        $pdo->exec("ALTER TABLE radio_manual_logs ADD COLUMN `service_type` VARCHAR(100) DEFAULT 'General' AFTER `service_date`");
        // Ensure ID column exists for Radio too
        try { $pdo->exec("ALTER TABLE radio_manual_logs ADD COLUMN `id` INT AUTO_INCREMENT PRIMARY KEY FIRST"); } catch(Exception $ignore){}
    }
} catch (Exception $e) {
    // If it fails, the page might crash later on SELECT, but we avoid "Duplicate Column" errors now.
    // Uncomment for debugging: echo "Migration Error: " . $e->getMessage();
}

// 1. Fetch YouTube Manual Logs (Peak Viewers + Physical Attendance)
$sql_manual = "SELECT service_date, stream_id, peak_viewers, physical_attendance, service_type FROM youtube_manual_logs WHERE service_date BETWEEN ? AND ? ";
$params_manual = [$start_date, $end_date];

if ($service_filter !== 'All') {
    $sql_manual .= " AND service_type = ? ";
    $params_manual[] = $service_filter;
}
$sql_manual .= " ORDER BY service_date";

$yt_manual_stmt = $pdo->prepare($sql_manual);
$yt_manual_stmt->execute($params_manual);
$yt_manual_data = $yt_manual_stmt->fetchAll();

// 2. Fetch Radio Manual Logs
$sql_radio = "SELECT service_date, service_type, listener_count FROM radio_manual_logs WHERE service_date BETWEEN ? AND ? ";
$params_radio = [$start_date, $end_date];

if ($service_filter !== 'All') {
    $sql_radio .= " AND service_type = ? ";
    $params_radio[] = $service_filter;
}
$radio_stmt = $pdo->prepare($sql_radio);
$radio_stmt->execute($params_radio);
$radio_data = $radio_stmt->fetchAll();

// ... existing radio/auto logic ...

// Prepare Datasets
$dates = [];
$current = strtotime($start_date);
$end = strtotime($end_date);
while ($current <= $end) {
    $dates[] = date('Y-m-d', $current);
    $current = strtotime('+1 day', $current);
}

function mapData($dates, $raw_data, $date_col, $val_col, $stream_id = null) {
    $out = [];
    $lookup = [];
    foreach ($raw_data as $row) {
        if ($stream_id !== null && isset($row['stream_id']) && $row['stream_id'] != $stream_id) continue;
        
        $d = $row[$date_col];
        if (!isset($lookup[$d])) $lookup[$d] = 0;
        $lookup[$d] = max($lookup[$d], (int)$row[$val_col]);
    }
    foreach ($dates as $d) {
        $out[] = isset($lookup[$d]) ? $lookup[$d] : 0;
    }
    return $out;
}

$datasets = [];

// Radio Dataset
if (!empty($radio_data)) {
    $radio_values = mapData($dates, $radio_data, 'service_date', 'listener_count');
    if (array_sum($radio_values) > 0) {
        $datasets[] = [
            'label' => 'Web Radio',
            'borderColor' => '#00d2d3',
            'backgroundColor' => 'rgba(0, 210, 211, 0.2)',
            'data' => $radio_values,
            'tension' => 0.4,
            'spanGaps' => true
        ];
    }
}

// Physical Attendance Dataset (Shared across streams, but data is per row. Usually logged once per service. 
// If multiple streams log physical attendance for same service which one to pick? Max?
$phy_values = mapData($dates, $yt_manual_data, 'service_date', 'physical_attendance');
if (array_sum($phy_values) > 0) {
    $datasets[] = [
        'label' => 'Physical Attendance',
        'borderColor' => '#2e86de', // Blue
        'backgroundColor' => 'rgba(46, 134, 222, 0.1)',
        'borderWidth' => 3,
        'pointRadius' => 4,
        'fill' => true,
        'data' => $phy_values,
        'tension' => 0.3,
        'spanGaps' => true
    ];
}

// YouTube Datasets
$colors = ['#ff6b6b', '#feca57', '#54a0ff', '#5f27cd'];
$color_idx = 0;

foreach ($streams as $sid => $sname) {
    $c = $colors[$color_idx % count($colors)];
    $color_idx++;

    // Auto Logs
    if (!empty($yt_auto_data)) {
        $auto_vals = mapData($dates, $yt_auto_data, 'log_date', 'max_viewers', $sid);
        if (array_sum($auto_vals) > 0) {
            $datasets[] = [
                'label' => "$sname (Auto)",
                'borderColor' => $c,
                'backgroundColor' => $c,
                'fill' => false,
                'data' => $auto_vals,
                'tension' => 0.3,
                'spanGaps' => true,
                'hidden' => true // Default hide auto to reduce clutter
            ];
        }
    }

    // Manual Viewers
    $manual_vals = mapData($dates, $yt_manual_data, 'service_date', 'peak_viewers', $sid);
    if (array_sum($manual_vals) > 0) {
        $datasets[] = [
            'label' => "$sname (Viewers)",
            'borderColor' => $c,
            'borderDash' => [5, 5], 
            'pointStyle' => 'rectRot',
            'radius' => 5,
            'fill' => false,
            'data' => $manual_vals,
            'spanGaps' => true
        ];
    }
}

$service_types_opt = [
    'All',
    'Sunday School Service',
    'Elementary Sunday School',
    'Devotional Service',
    'Evening Service / YPS / Evangelistic',
    'Bible Study',
    'Other Services'
];
?>

<style>
@media print {
    @page { size: landscape; margin: 0.5cm; }
    
    /* 1. Hide EVERYTHING by default */
    body {
        visibility: hidden;
        background: #fff !important;
    }
    
    /* 2. Hide Navigation & Header explicitly to remove layout spacing */
    header, nav, .hide-print, .btn, .alert {
        display: none !important;
    }

    /* 3. Style the printable area */
    #printable-area {
        visibility: visible;
        position: absolute;
        left: 0;
        top: 0;
        width: 100%;
        background: white !important;
        color: black !important;
    }

    /* Ensure children are visible */
    #printable-area * {
        visibility: visible;
    }

    /* Card styling reset */
    .card { 
        border: none !important; 
        margin-bottom: 20px !important;
        padding: 0 !important;
        background: white !important;
        color: black !important;
        box-shadow: none !important;
    }
    
    /* Chart area */
    canvas { 
        max-height: 400px !important; 
        border: 1px solid #ccc !important;
    }

    /* Table styling */
    table { 
        border-collapse: collapse !important; 
        width: 100% !important; 
        font-size: 10pt !important;
        color: #000 !important;
    }
    
    th { 
        background-color: #f0f0f0 !important; 
        color: #000 !important; 
        border: 1px solid #000 !important; 
    }
    
    td { 
        border: 1px solid #000 !important; 
        color: #000 !important;
    }
}
</style>

<div style="display: flex; justify-content: space-between; align-items: center; margin-bottom: 25px;">
    <h1>Streaming Reports</h1>
    <div class="hide-print" style="display: flex; gap: 10px; align-items: center;">
        <button onclick="window.print()" class="btn" style="background: #444;"><i class="fas fa-print"></i> Export PDF</button>
        
        <form method="GET" style="display: flex; gap: 10px; align-items: center;">
            <input type="hidden" name="page" value="streaming_reports">
            
            <select name="service_type" class="form-control" onchange="this.form.submit()">
                <?php foreach($service_types_opt as $opt): ?>
                    <option value="<?php echo $opt; ?>" <?php echo $service_filter === $opt ? 'selected' : ''; ?>><?php echo $opt; ?></option>
                <?php endforeach; ?>
            </select>
            
            <input type="month" name="month" class="form-control" value="<?php echo $month; ?>" onchange="this.form.submit()">
        </form>
    </div>
</div>

<!-- Printable Wrapper -->
<div id="printable-area">
    <h2 style="text-align: center; margin-bottom: 20px;">
        Streaming Report - <?php echo date('F Y', strtotime($start_date)); ?>
        <?php if($service_filter !== 'All') echo " ($service_filter)"; ?>
    </h2>

    <!-- Chart Card -->
    <div class="card" style="margin-bottom: 30px; background: white; color: black;">
        <h3 class="hide-print" style="color: black;">Viewers vs Physical Attendance</h3>
        <canvas id="streamingChart" height="100"></canvas>
    </div>

    <div class="card" style="background: white !important; color: black !important;">
        <h3 style="color: black !important;">Daily Breakdown (<?php echo date('F Y', strtotime($start_date)); ?>)</h3>
        <table class="table" style="width: 100%; margin-top: 15px; font-size: 0.9rem; background: white !important; color: black !important;">
            <thead style="background: white !important; color: black !important;">
                <tr style="border-bottom: 2px solid #000; text-align: left;">
                    <th style="padding: 10px; background: white !important; color: black !important;">Date</th>
                    <th style="padding: 10px; background: white !important; color: black !important;">Service Type</th>
                    <?php if ($service_filter === 'All'): ?><th style="padding: 10px; background: white !important; color: black !important;">Web Radio</th><?php endif; ?>
                    <th style="padding: 10px; background: white !important; color: black !important;">Physical Attd.</th>
                    <?php foreach ($streams as $sname): ?>
                        <th style="padding: 10px; background: white !important; color: black !important;"><?php echo htmlspecialchars($sname); ?></th>
                    <?php endforeach; ?>
                </tr>
            </thead>
            <tbody>
                <?php 
                // Prepare rows grouped by Date + Service Type
                $report_rows = [];
                
                // 1. Process YouTube Manual Data
                foreach ($yt_manual_data as $row) {
                    $key = $row['service_date'] . '|' . ($row['service_type'] ?: 'General');
                    if (!isset($report_rows[$key])) {
                        $report_rows[$key] = [
                            'date' => $row['service_date'],
                            'type' => $row['service_type'] ?: 'General',
                            'radio' => 0, // Radio usually not linked to type in same way, or is it?
                            'phy' => 0, 
                            'streams' => []
                        ];
                    }
                    $report_rows[$key]['phy'] = max($report_rows[$key]['phy'], (int)$row['physical_attendance']);
                    $report_rows[$key]['streams'][$row['stream_id']] = (int)$row['peak_viewers'];
                }

                // 2. Process Radio Data
                foreach ($radio_data as $row) {
                    $key = $row['service_date'] . '|' . ($row['service_type'] ?: 'General');
                    if (!isset($report_rows[$key])) {
                        $report_rows[$key] = [
                            'date' => $row['service_date'],
                            'type' => $row['service_type'] ?: 'General',
                            'radio' => 0,
                            'phy' => 0, 
                            'streams' => []
                        ];
                    }
                    $report_rows[$key]['radio'] = max($report_rows[$key]['radio'], (int)$row['listener_count']);
                }
                
                // Sort by Date DESC
                uksort($report_rows, function($a, $b) {
                    return strcmp($b, $a);
                });

                foreach ($report_rows as $row): 
                ?>
                <tr style="border-bottom: 1px solid #ccc;">

                    <td style="padding: 10px; background: white !important; color: black !important; font-weight: bold;"><?php echo date('D, M j', strtotime($row['date'])); ?></td>
                    <td style="padding: 10px; background: white !important; color: black !important;"><?php echo htmlspecialchars($row['type']); ?></td>
                    
                    <?php if ($service_filter === 'All'): ?>
                    <td style="padding: 10px; background: white !important; color: black !important; font-weight: bold;">
                        <?php echo ($row['radio'] > 0) ? $row['radio'] : '-'; ?>
                    </td>
                    <?php endif; ?>
                    
                    <td style="padding: 10px; background: white !important; color: black !important; font-weight: bold;">
                        <?php echo ($row['phy'] > 0) ? $row['phy'] : '-'; ?>
                    </td>

                    <?php foreach ($streams as $sid => $sname): 
                        $val = isset($row['streams'][$sid]) ? $row['streams'][$sid] : 0;
                    ?>
                        <td style="padding: 10px; background: white !important; color: black !important;">
                            <span style="font-weight: bold;"><?php echo $val > 0 ? $val : '-'; ?></span>
                        </td>
                    <?php endforeach; ?>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
</div>


<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<?php
// Filter Chart Data to remove empty dates (Same logic as table)
$chart_indices = [];
$cnt = count($dates);
for ($i = 0; $i < $cnt; $i++) {
    $has_val = false;
    foreach ($datasets as $ds) {
        if (isset($ds['data'][$i]) && $ds['data'][$i] > 0) {
            $has_val = true;
            break;
        }
    }
    if ($has_val) $chart_indices[] = $i;
}

$chart_labels = [];
foreach ($chart_indices as $idx) {
    // Format: "01" (Day)
    $chart_labels[] = date('d', strtotime($dates[$idx]));
}

// Rebuild datasets with filtered data
foreach ($datasets as $k => $ds) {
    $new_data = [];
    foreach ($chart_indices as $idx) {
        $new_data[] = $ds['data'][$idx];
    }
    $datasets[$k]['data'] = $new_data;
}
?>
<script>
const ctx = document.getElementById('streamingChart').getContext('2d');
// Register plugin to force white background on export/print if needed, 
// but CSS @media print is usually enough for browser print. 
// For Chart.js explicit background:
const plugin = {
  id: 'customCanvasBackgroundColor',
  beforeDraw: (chart, args, options) => {
    const {ctx} = chart;
    ctx.save();
    ctx.globalCompositeOperation = 'destination-over';
    ctx.fillStyle = options.color || '#ffffff';
    ctx.fillRect(0, 0, chart.width, chart.height);
    ctx.restore();
  }
};

const streamingChart = new Chart(ctx, {
    type: 'line',
    data: {
        labels: <?php echo json_encode($chart_labels); ?>,
        datasets: <?php echo json_encode($datasets); ?>
    },
    options: {
        responsive: true,
        spanGaps: true, // Type here to be safe
        plugins: {
            legend: { position: 'top', labels: { color: '#000' } }, // Always black for better contrast on white card
            tooltip: { mode: 'index', intersect: false },
            customCanvasBackgroundColor: { color: 'white' }
        },
        scales: {
            y: {
                beginAtZero: true,
                grid: { color: '#eee' },
                ticks: { color: '#666' }
            },
            x: {
                grid: { color: '#eee' },
                ticks: { color: '#666' }
            }
        }
    },
    plugins: [plugin]
});
</script>
