<?php
require_once 'client-session.php';
requireClientLogin();

$pdo = getClientDBConnection();

// Get filter values from query string (same as projects-list.php)
$filter_status = $_GET['status'] ?? '';
$filter_industry = $_GET['industry'] ?? '';
$filter_from_date = $_GET['from_date'] ?? '';
$filter_to_date = $_GET['to_date'] ?? '';
$filter_min_loi = $_GET['min_loi'] ?? '';
$filter_max_loi = $_GET['max_loi'] ?? '';

// Build query with filters
$query = "SELECT * FROM projects WHERE client_id = ?";
$params = [$_SESSION['client_id']];

if ($filter_status) { $query .= " AND status = ?"; $params[] = $filter_status; }
if ($filter_industry) { $query .= " AND industry = ?"; $params[] = $filter_industry; }
if ($filter_from_date) { $query .= " AND DATE(created_at) >= ?"; $params[] = $filter_from_date; }
if ($filter_to_date) { $query .= " AND DATE(created_at) <= ?"; $params[] = $filter_to_date; }
if ($filter_min_loi) { $query .= " AND eloi >= ?"; $params[] = intval($filter_min_loi); }
if ($filter_max_loi) { $query .= " AND eloi <= ?"; $params[] = intval($filter_max_loi); }

$query .= " ORDER BY created_at DESC";
$stmt = $pdo->prepare($query);
$stmt->execute($params);
$projects = $stmt->fetchAll();

// For each project, get real-time completion stats
$projectStats = [];
if (!empty($projects)) {
    $projectIds = array_column($projects, 'project_id');
    $ph = implode(',', array_fill(0, count($projectIds), '?'));

    // URL-level stats per project
    $stmt = $pdo->prepare("
        SELECT project_id,
               COUNT(*) as total_urls,
               SUM(CASE WHEN status = 'complete' THEN 1 ELSE 0 END) as completes,
               SUM(CASE WHEN status = 'clicked' THEN 1 ELSE 0 END) as clicked,
               SUM(CASE WHEN status IN ('earlyscreenout','latescreenout') THEN 1 ELSE 0 END) as screenouts,
               SUM(CASE WHEN status = 'quotafull' THEN 1 ELSE 0 END) as quotafull,
               SUM(CASE WHEN status = 'timeout' THEN 1 ELSE 0 END) as timeouts,
               AVG(CASE WHEN status='complete' AND actual_loi_seconds IS NOT NULL THEN actual_loi_seconds END) as avg_loi,
               SUM(CASE WHEN quality_flag = 'speedster' THEN 1 ELSE 0 END) as speedsters,
               SUM(CASE WHEN quality_flag = 'ip_duplicate' THEN 1 ELSE 0 END) as ip_dups
        FROM survey_urls
        WHERE project_id IN ($ph)
        GROUP BY project_id
    ");
    $stmt->execute($projectIds);
    foreach ($stmt->fetchAll() as $r) $projectStats[$r['project_id']] = $r;

    // Selection counts per project (by project internal id)
    $intIds = array_column($projects, 'id');
    $ph2 = implode(',', array_fill(0, count($intIds), '?'));
    $stmt = $pdo->prepare("
        SELECT project_id, COUNT(*) as sel_count
        FROM project_selections
        WHERE project_id IN ($ph2)
        GROUP BY project_id
    ");
    $stmt->execute($intIds);
    $selCounts = [];
    foreach ($stmt->fetchAll() as $r) $selCounts[$r['project_id']] = $r['sel_count'];
}

// Build filter description
$filter_desc = [];
if ($filter_status) $filter_desc[] = "Status: $filter_status";
if ($filter_industry) $filter_desc[] = "Industry: $filter_industry";
if ($filter_from_date) $filter_desc[] = "From: $filter_from_date";
if ($filter_to_date) $filter_desc[] = "To: $filter_to_date";
if ($filter_min_loi) $filter_desc[] = "Min LOI: {$filter_min_loi}min";
if ($filter_max_loi) $filter_desc[] = "Max LOI: {$filter_max_loi}min";
$filter_text = empty($filter_desc) ? 'All Projects' : implode(' | ', $filter_desc);

// Global totals
$g_completes = 0; $g_target = 0; $g_screenout = 0; $g_speedsters = 0; $g_ipdups = 0; $g_quality = 0;
foreach ($projects as $p) {
    $s = $projectStats[$p['project_id']] ?? [];
    $g_completes += intval($s['completes'] ?? 0);
    $g_target += intval($p['sample_size']);
    $g_screenout += intval($s['screenouts'] ?? 0);
    $g_speedsters += intval($s['speedsters'] ?? 0);
    $g_ipdups += intval($s['ip_dups'] ?? 0);
}
$g_quality = $g_speedsters + $g_ipdups;

$status_colors = [
    'Created' => '#0066cc', 'Targeted' => '#d97706', 'Live' => '#059669',
    'On hold' => '#dc2626', 'Closed' => '#6b7280'
];
?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Projects Export</title>
    <style>
        * { box-sizing: border-box; }
        body { font-family: Arial, sans-serif; padding: 30px; color: #1a1a1a; font-size: 11px; }
        .header { text-align: center; margin-bottom: 24px; border-bottom: 3px solid #059669; padding-bottom: 12px; }
        .header h1 { color: #059669; margin: 0 0 4px 0; font-size: 22px; }
        .header .subtitle { font-size: 13px; color: #666; }
        .header .generated { font-size: 10px; color: #999; margin-top: 4px; }

        .filter-info { background: #f3f4f6; padding: 8px 14px; margin-bottom: 16px; border-radius: 6px; font-size: 11px; }

        .kpi-row { display: flex; gap: 0; border: 1px solid #e5e7eb; border-radius: 6px; overflow: hidden; margin-bottom: 16px; }
        .kpi-box { flex: 1; text-align: center; padding: 10px 6px; border-right: 1px solid #e5e7eb; background: white; }
        .kpi-box:last-child { border-right: none; }
        .kpi-num { font-size: 20px; font-weight: 800; color: #059669; }
        .kpi-num.red { color: #dc2626; }
        .kpi-num.amber { color: #d97706; }
        .kpi-label { font-size: 8px; color: #6b7280; text-transform: uppercase; font-weight: 600; margin-top: 2px; }

        table { width: 100%; border-collapse: collapse; margin-bottom: 20px; }
        table th { background: #059669; color: white; padding: 5px 4px; text-align: left; font-size: 8px; font-weight: 700; text-transform: uppercase; letter-spacing: 0.3px; white-space: nowrap; }
        table td { padding: 5px 4px; border-bottom: 1px solid #f3f4f6; font-size: 9px; }
        table tr:nth-child(even) { background: #f9fafb; }

        .badge { padding: 2px 5px; border-radius: 8px; font-size: 8px; font-weight: 700; white-space: nowrap; display: inline-block; }
        .badge-green { background: #d1fae5; color: #065f46; }
        .badge-red { background: #fee2e2; color: #991b1b; }
        .badge-amber { background: #fef3c7; color: #92400e; }
        .badge-blue { background: #dbeafe; color: #1e40af; }
        .badge-gray { background: #e5e7eb; color: #374151; }

        .mono { font-family: monospace; font-size: 9px; color: #059669; font-weight: 700; }
        .pbar { background: #e5e7eb; border-radius: 3px; height: 6px; width: 50px; display: inline-block; vertical-align: middle; }
        .pfill { background: #059669; border-radius: 3px; height: 100%; display: block; }

        .footer { margin-top: 24px; text-align: center; color: #999; font-size: 10px; border-top: 1px solid #e5e7eb; padding-top: 10px; }

        @media print { body { padding: 15px; } }
    </style>
</head>
<body>
    <div class="header">
        <h1>RELEVANT REFLEX</h1>
        <div class="subtitle">Projects Completion Report</div>
        <div class="generated">Generated <?php echo date('M j, Y g:i A'); ?> | Client: <?php echo htmlspecialchars($_SESSION['client_name']); ?></div>
    </div>

    <div class="filter-info"><strong>Filters:</strong> <?php echo htmlspecialchars($filter_text); ?></div>

    <!-- Summary KPIs -->
    <div class="kpi-row">
        <div class="kpi-box"><div class="kpi-num"><?php echo count($projects); ?></div><div class="kpi-label">Projects</div></div>
        <div class="kpi-box"><div class="kpi-num"><?php echo number_format($g_completes); ?>/<?php echo number_format($g_target); ?></div><div class="kpi-label">Completes / Target</div></div>
        <div class="kpi-box"><div class="kpi-num"><?php echo $g_target > 0 ? round(($g_completes/$g_target)*100,1) : 0; ?>%</div><div class="kpi-label">Completion</div></div>
        <div class="kpi-box"><div class="kpi-num"><?php echo number_format($g_screenout); ?></div><div class="kpi-label">Screen Outs</div></div>
        <div class="kpi-box"><div class="kpi-num <?php echo $g_quality > 0 ? 'red' : ''; ?>"><?php echo $g_quality; ?></div><div class="kpi-label">Quality Issues</div></div>
    </div>

    <?php if (empty($projects)): ?>
        <p style="text-align: center; padding: 40px; color: #666;">No projects match the selected filters.</p>
    <?php else: ?>
    <table>
        <thead>
            <tr>
                <th>Project ID</th>
                <th>Name</th>
                <th>Status</th>
                <th>eLOI</th>
                <th>Target</th>
                <th>Sel.</th>
                <th>Completes</th>
                <th>%</th>
                <th>Progress</th>
                <th>Avg LOI</th>
                <th>Screen Out</th>
                <th>Quota Full</th>
                <th>Speedsters</th>
                <th>IP Dups</th>
                <th>Deadline</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($projects as $project):
                $s = $projectStats[$project['project_id']] ?? [];
                $completes = intval($s['completes'] ?? 0);
                $target = intval($project['sample_size']);
                $pct = $target > 0 ? round(($completes / $target) * 100, 1) : 0;
                $avgLoi = isset($s['avg_loi']) && $s['avg_loi'] > 0 ? round($s['avg_loi'] / 60, 1) . 'm' : '-';
                $spd = intval($s['speedsters'] ?? 0);
                $ipd = intval($s['ip_dups'] ?? 0);
                $sels = $selCounts[$project['id']] ?? 0;
                $col = $status_colors[$project['status']] ?? '#6b7280';
            ?>
            <tr>
                <td class="mono"><?php echo htmlspecialchars($project['project_id']); ?></td>
                <td><strong><?php echo htmlspecialchars($project['project_name']); ?></strong></td>
                <td><span class="badge" style="background:<?php echo $col; ?>;color:white;"><?php echo $project['status']; ?></span></td>
                <td><?php echo $project['eloi']; ?>m</td>
                <td><?php echo number_format($target); ?></td>
                <td><?php echo $sels; ?></td>
                <td><strong><?php echo $completes; ?></strong></td>
                <td><strong><?php echo $pct; ?>%</strong></td>
                <td><div class="pbar"><div class="pfill" style="width:<?php echo min(100, $pct); ?>%;"></div></div></td>
                <td><?php echo $avgLoi; ?></td>
                <td><?php echo intval($s['screenouts'] ?? 0); ?></td>
                <td><?php echo intval($s['quotafull'] ?? 0); ?></td>
                <td><?php echo $spd > 0 ? '<span class="badge badge-amber">' . $spd . '</span>' : '0'; ?></td>
                <td><?php echo $ipd > 0 ? '<span class="badge badge-red">' . $ipd . '</span>' : '0'; ?></td>
                <td><?php echo date('d M Y', strtotime($project['deadline'])); ?></td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
    <?php endif; ?>

    <div class="footer">
        Relevant Reflex &mdash; Projects Completion Report &mdash; <?php echo date('F j, Y g:i A'); ?>
    </div>

    <script>window.onload = function() { window.print(); };</script>
</body>
</html>