<?php
/**
 * Export URL Data - CSV Download
 * 
 * Modes:
 *   1. Filtered: ?project_id=X&selection_id=Y&url_status=Z  (specific filters)
 *   2. Complete: ?project_id=X&mode=complete  (all sent URLs with full project details)
 */

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

$client_id = $_SESSION['client_id'];
$project_str_id = $_GET['project_id'] ?? '';
$project_db_id = intval($_GET['project_db_id'] ?? 0);
$mode = $_GET['mode'] ?? 'filtered';
$selectionFilter = $_GET['selection_id'] ?? 'all';
$statusFilter = $_GET['url_status'] ?? 'all';

if (empty($project_str_id)) {
    die('Missing project ID');
}

try {
    $pdo = getClientDBConnection();
    
    // Verify client owns this project
    $stmt = $pdo->prepare("SELECT * FROM projects WHERE project_id = ? AND client_id = ?");
    $stmt->execute([$project_str_id, $client_id]);
    $project = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if (!$project) {
        die('Project not found or access denied');
    }
    
    // Build the query
    $where = ["su.project_id = ?"];
    $params = [$project_str_id];
    
    if ($mode === 'complete') {
        // Complete report: all sent URLs (exclude 'available')
        $where[] = "su.status != 'available'";
    } else {
        // Filtered mode
        if ($selectionFilter !== 'all' && intval($selectionFilter) > 0) {
            $where[] = "su.sent_to_user_id IN (SELECT user_id FROM selection_members WHERE selection_id = ?)";
            $params[] = intval($selectionFilter);
        }
        if ($statusFilter !== 'all') {
            $validStatuses = ['available','sent','clicked','complete','partial','earlyscreenout','latescreenout','quotafull','timeout'];
            if (in_array($statusFilter, $validStatuses)) {
                $where[] = "su.status = ?";
                $params[] = $statusFilter;
            }
        }
    }
    
    $whereClause = implode(' AND ', $where);
    
    // Fetch URLs with selection info
    $sql = "
        SELECT 
            su.unique_identifier,
            su.rr_proxy_url,
            su.client_url,
            su.status as url_status,
            su.batch_number,
            su.is_sent,
            su.sent_to_user_id,
            su.sent_at,
            su.clicked_at,
            su.completed_at,
            su.actual_loi_seconds,
            su.quality_flag,
            su.quality_notes,
            su.created_at as url_created_at,
            sm.sample_status as member_sample_status,
            sm.completed_at as member_completed_at,
            ps.selection_name,
            ps.selection_id as selection_code
        FROM survey_urls su
        LEFT JOIN selection_members sm ON su.sent_to_user_id = sm.user_id 
            AND sm.selection_id IN (SELECT id FROM project_selections WHERE project_id = ?)
        LEFT JOIN project_selections ps ON sm.selection_id = ps.id
        WHERE $whereClause
        ORDER BY su.batch_number ASC, su.id ASC
    ";
    
    // Add the project_id for the subquery join
    array_unshift($params, $project['id']);
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $urls = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (empty($urls) && $mode !== 'complete') {
        die('No URLs found matching the selected filters. Go back and try different filters.');
    }
    
    // Status labels
    $statusLabels = [
        'available' => 'Available',
        'sent' => 'Sent',
        'clicked' => 'Clicked',
        'complete' => 'Complete',
        'partial' => 'Partial Complete',
        'earlyscreenout' => 'Early Screen Out',
        'latescreenout' => 'Late Screen Out',
        'timeout' => 'Timed Out',
        'quotafull' => 'Quota Full',
    ];
    
    $memberStatusLabels = [
        'pending' => 'Pending',
        'complete' => 'Complete',
        'screen_out' => 'Screen Out',
        'quota_full' => 'Quota Full',
        'over_quota' => 'Over Quota',
    ];
    
    // Generate filename
    $filterSuffix = $mode === 'complete' ? 'Complete' : 'Filtered';
    $filename = sprintf(
        '%s_%s_URLs_%s.csv',
        $project['project_id'],
        $filterSuffix,
        date('Ymd_His')
    );
    
    // Set CSV headers
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Pragma: no-cache');
    header('Expires: 0');
    
    $output = fopen('php://output', 'w');
    
    // UTF-8 BOM for Excel
    fprintf($output, chr(0xEF) . chr(0xBB) . chr(0xBF));
    
    if ($mode === 'complete') {
        // Complete report: project details header
        fputcsv($output, ['PROJECT DETAILS']);
        fputcsv($output, ['Project ID', $project['project_id']]);
        fputcsv($output, ['Project Name', $project['project_name']]);
        fputcsv($output, ['Client Reference', $project['client_reference'] ?? '']);
        fputcsv($output, ['Industry', $project['industry']]);
        fputcsv($output, ['Status', $project['status']]);
        fputcsv($output, ['Sample Size (eN)', $project['sample_size']]);
        fputcsv($output, ['ELOI (min)', $project['eloi']]);
        fputcsv($output, ['Deadline', $project['deadline']]);
        fputcsv($output, ['Created', $project['created_at']]);
        fputcsv($output, ['Export Date', date('Y-m-d H:i:s')]);
        fputcsv($output, ['Total URLs Exported', count($urls)]);
        fputcsv($output, []);
        fputcsv($output, ['URL DATA']);
    }
    
    // CSV column headers
    fputcsv($output, [
        'Unique ID',
        'Proxy URL (Relevant Reflex)',
        'Client Survey URL',
        'URL Status',
        'Quality Flag',
        'Quality Notes',
        'Batch #',
        'Selection',
        'Member Sample Status',
        'Sent At',
        'Clicked At',
        'Completed At',
        'Actual LOI (seconds)',
        'Actual LOI (minutes)'
    ]);
    
    // Data rows
    foreach ($urls as $url) {
        $loiSec = $url['actual_loi_seconds'] ?? '';
        $loiMin = ($loiSec !== '' && $loiSec !== null) ? round($loiSec / 60, 2) : '';
        fputcsv($output, [
            $url['unique_identifier'],
            $url['rr_proxy_url'],
            $url['client_url'],
            $statusLabels[$url['url_status']] ?? $url['url_status'],
            ucfirst(str_replace('_', ' ', $url['quality_flag'] ?? 'valid')),
            $url['quality_notes'] ?? '',
            $url['batch_number'],
            $url['selection_name'] ?? 'Unlinked',
            $memberStatusLabels[$url['member_sample_status']] ?? ($url['member_sample_status'] ?? ''),
            $url['sent_at'] ? date('Y-m-d H:i:s', strtotime($url['sent_at'])) : '',
            $url['clicked_at'] ? date('Y-m-d H:i:s', strtotime($url['clicked_at'])) : '',
            $url['completed_at'] ? date('Y-m-d H:i:s', strtotime($url['completed_at'])) : '',
            $loiSec,
            $loiMin,
        ]);
    }
    
    // Summary at the bottom
    fputcsv($output, []);
    fputcsv($output, ['SUMMARY']);
    
    // Count by status
    $statusCounts = [];
    foreach ($urls as $url) {
        $s = $url['url_status'];
        $statusCounts[$s] = ($statusCounts[$s] ?? 0) + 1;
    }
    foreach ($statusCounts as $status => $count) {
        fputcsv($output, [$statusLabels[$status] ?? $status, $count]);
    }
    fputcsv($output, ['Total', count($urls)]);
    
    fclose($output);
    
    error_log(sprintf(
        "Client %d exported %d URLs from project %s (mode: %s)",
        $client_id, count($urls), $project_str_id, $mode
    ));
    
    exit;
    
} catch (Exception $e) {
    error_log("Export URL Data Error: " . $e->getMessage());
    die('Error exporting data: ' . htmlspecialchars($e->getMessage()));
}
?>