<?php
/**
 * Export Batch URLs to CSV
 * 
 * Updated:
 * - Shows actual status (complete, partial, clicked, etc.) not just Available/Sent
 * - Adds "Client Unique ID" column (extracted from client_url using uid_param)
 * - Adds "RR Unique ID" column (unique_identifier)
 * - Supports status[] filter via GET params (multi-select)
 * 
 * Usage:
 *   export-batch-urls.php?project_id=X&batch=1
 *   export-batch-urls.php?project_id=X&batch=1&status[]=complete&status[]=partial
 */

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

$client_id = $_SESSION['client_id'];

// Get parameters
$project_id = $_GET['project_id'] ?? '';
$batch_number = (int)($_GET['batch'] ?? 0);
$status_filter = $_GET['status'] ?? []; // Array of statuses

// Validate parameters
if (empty($project_id) || $batch_number < 1) {
    $_SESSION['error'] = 'Invalid export parameters';
    header('Location: projects-list.php');
    exit;
}

// Ensure status_filter is an array
if (!is_array($status_filter)) {
    $status_filter = [$status_filter];
}

// Valid statuses
$valid_statuses = ['available', 'assigned', 'sent', 'clicked', 'complete', 'partial', 'earlyscreenout', 'latescreenout', 'quotafull', 'timeout'];
$status_filter = array_intersect($status_filter, $valid_statuses);

try {
    $pdo = getClientDBConnection();
    
    // Verify access to project and batch — also fetch uid_param
    $stmt = $pdo->prepare("
        SELECT 
            p.project_name,
            p.project_id,
            b.batch_number,
            b.total_urls,
            b.sent_count,
            b.upload_type,
            b.uid_param,
            b.created_at
        FROM survey_url_batches b
        JOIN projects p ON b.project_id = p.project_id
        WHERE b.project_id = ? 
        AND b.batch_number = ?
        AND p.client_id = ?
    ");
    $stmt->execute([$project_id, $batch_number, $client_id]);
    $batch = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if (!$batch) {
        $_SESSION['error'] = 'Batch not found or access denied';
        header('Location: projects-list.php');
        exit;
    }
    
    $uid_param = $batch['uid_param'] ?? '';
    
    // Build query with optional status filter
    $sql = "
        SELECT 
            unique_identifier,
            rr_proxy_url,
            client_url,
            status,
            is_sent,
            sent_to_user_id,
            sent_at,
            clicked_at,
            created_at
        FROM survey_urls
        WHERE project_id = ? AND batch_number = ?
    ";
    $params = [$project_id, $batch_number];
    
    if (!empty($status_filter)) {
        $placeholders = str_repeat('?,', count($status_filter) - 1) . '?';
        $sql .= " AND status IN ($placeholders)";
        $params = array_merge($params, $status_filter);
    }
    
    $sql .= " ORDER BY id ASC";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $urls = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (empty($urls)) {
        $_SESSION['error'] = 'No URLs found matching the selected filters';
        header('Location: view-batch-urls.php?project_id=' . urlencode($project_id) . '&batch=' . $batch_number);
        exit;
    }
    
    // Generate filename
    $filter_suffix = !empty($status_filter) ? '_' . implode('-', $status_filter) : '_all';
    $filename = sprintf(
        '%s_Batch%d%s_%s.csv',
        $project_id,
        $batch_number,
        $filter_suffix,
        date('Ymd_His')
    );
    
    // Set headers for CSV download
    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));
    
    // CSV headers
    fputcsv($output, [
        'RR Unique ID',
        'Client Unique ID',
        'RR Proxy URL',
        'Client Survey URL',
        'Status',
        'Sent To User ID',
        'Sent Date/Time',
        'Clicked Date/Time',
        'Created Date/Time'
    ]);
    
    // Status labels
    $status_labels = [
        'available'       => 'Available',
        'assigned'        => 'Assigned',
        'sent'            => 'Sent',
        'clicked'         => 'Clicked',
        'complete'        => 'Complete',
        'partial'         => 'Partial',
        'earlyscreenout'  => 'Early Screenout',
        'latescreenout'   => 'Late Screenout',
        'quotafull'       => 'Quota Full',
        'timeout'         => 'Timeout'
    ];
    
    // Write data rows
    foreach ($urls as $url) {
        // Extract client's unique ID from client_url using uid_param
        $client_uid = extractClientUid($url['client_url'], $uid_param);
        
        fputcsv($output, [
            $url['unique_identifier'],                                          // RR Unique ID
            $client_uid,                                                        // Client Unique ID
            $url['rr_proxy_url'],                                               // RR Proxy URL
            $url['client_url'],                                                 // Client Survey URL
            $status_labels[$url['status']] ?? ucfirst($url['status']),          // Status
            $url['sent_to_user_id'] ?? '',                                      // Sent To User ID
            $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'])) : '',
            date('Y-m-d H:i:s', strtotime($url['created_at']))
        ]);
    }
    
    // Summary section
    fputcsv($output, []);
    fputcsv($output, ['SUMMARY']);
    fputcsv($output, ['Project ID', $batch['project_id']]);
    fputcsv($output, ['Project Name', $batch['project_name']]);
    fputcsv($output, ['Batch Number', $batch_number]);
    fputcsv($output, ['UID Parameter', $uid_param ?: '(not set)']);
    fputcsv($output, ['Filter Applied', !empty($status_filter) ? implode(', ', $status_filter) : 'All statuses']);
    fputcsv($output, ['URLs Exported', count($urls)]);
    fputcsv($output, ['Total URLs in Batch', $batch['total_urls']]);
    fputcsv($output, ['Upload Type', ucfirst($batch['upload_type'])]);
    fputcsv($output, ['Batch Created', date('Y-m-d H:i:s', strtotime($batch['created_at']))]);
    fputcsv($output, ['Export Date', date('Y-m-d H:i:s')]);
    
    fclose($output);
    
    error_log(sprintf(
        "Client %d exported %d URLs from project %s batch %d (filter: %s)",
        $client_id,
        count($urls),
        $project_id,
        $batch_number,
        !empty($status_filter) ? implode(',', $status_filter) : 'all'
    ));
    
    exit;
    
} catch (Exception $e) {
    error_log("Export Error: " . $e->getMessage());
    $_SESSION['error'] = 'Error exporting URLs: ' . $e->getMessage();
    header('Location: view-batch-urls.php?project_id=' . urlencode($project_id) . '&batch=' . $batch_number);
    exit;
}

/**
 * Extract the client's unique identifier from a URL using the uid_param name
 */
function extractClientUid($url, $uid_param) {
    if (empty($uid_param) || empty($url)) {
        return '';
    }
    
    $parsed = parse_url($url);
    if (isset($parsed['query'])) {
        parse_str($parsed['query'], $params);
        if (isset($params[$uid_param])) {
            return $params[$uid_param];
        }
    }
    
    return '';
}
?>