<?php
/**
 * Export Project URLs to CSV (All Batches)
 * 
 * Downloads ALL URLs across ALL batches for a project.
 * - Shows actual status (complete, partial, clicked, etc.)
 * - Adds "Client Unique ID" column (extracted from client_url using uid_param per batch)
 * - Adds "RR Unique ID" column (unique_identifier)
 * - Adds "Batch #" column
 * - Supports status[] filter via GET params (multi-select)
 * 
 * Usage:
 *   export-project-urls.php?id=123
 *   export-project-urls.php?id=123&status[]=complete&status[]=partial
 */

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

$client_id = $_SESSION['client_id'];

// Get parameters — id is the numeric project ID from the projects table
$project_numeric_id = (int)($_GET['id'] ?? 0);
$status_filter = $_GET['status'] ?? []; // Array of statuses

// Validate parameters
if ($project_numeric_id < 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
    $stmt = $pdo->prepare("
        SELECT id, project_id, project_name
        FROM projects
        WHERE id = ? AND client_id = ?
    ");
    $stmt->execute([$project_numeric_id, $client_id]);
    $project = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if (!$project) {
        $_SESSION['error'] = 'Project not found or access denied';
        header('Location: projects-list.php');
        exit;
    }
    
    $project_code = $project['project_id']; // e.g. "PRJ001"
    
    // Fetch uid_param per batch (each batch can have its own uid_param)
    $stmt = $pdo->prepare("
        SELECT batch_number, uid_param
        FROM survey_url_batches
        WHERE project_id = ?
    ");
    $stmt->execute([$project_code]);
    $batch_uid_params = [];
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $batch_uid_params[$row['batch_number']] = $row['uid_param'] ?? '';
    }
    
    // Build query with optional status filter
    $sql = "
        SELECT 
            su.unique_identifier,
            su.batch_number,
            su.rr_proxy_url,
            su.client_url,
            su.status,
            su.is_sent,
            su.sent_to_user_id,
            su.sent_at,
            su.clicked_at,
            su.created_at
        FROM survey_urls su
        WHERE su.project_id = ?
    ";
    $params = [$project_code];
    
    if (!empty($status_filter)) {
        $placeholders = str_repeat('?,', count($status_filter) - 1) . '?';
        $sql .= " AND su.status IN ($placeholders)";
        $params = array_merge($params, $status_filter);
    }
    
    $sql .= " ORDER BY su.batch_number ASC, su.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-project.php?id=' . $project_numeric_id);
        exit;
    }
    
    // Generate filename
    $filter_suffix = !empty($status_filter) ? '_' . implode('-', $status_filter) : '_all';
    $filename = sprintf(
        '%s_AllBatches%s_%s.csv',
        $project_code,
        $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',
        'Batch #',
        '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) {
        // Get uid_param for this batch
        $uid_param = $batch_uid_params[$url['batch_number']] ?? '';
        
        // 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['batch_number'],                                               // Batch #
            $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', $project_code]);
    fputcsv($output, ['Project Name', $project['project_name']]);
    fputcsv($output, ['Total Batches', count($batch_uid_params)]);
    fputcsv($output, ['Filter Applied', !empty($status_filter) ? implode(', ', $status_filter) : 'All statuses']);
    fputcsv($output, ['URLs Exported', count($urls)]);
    fputcsv($output, ['Export Date', date('Y-m-d H:i:s')]);
    
    fclose($output);
    
    error_log(sprintf(
        "Client %d exported %d project-level URLs from project %s (filter: %s)",
        $client_id,
        count($urls),
        $project_code,
        !empty($status_filter) ? implode(',', $status_filter) : 'all'
    ));
    
    exit;
    
} catch (Exception $e) {
    error_log("Project Export Error: " . $e->getMessage());
    $_SESSION['error'] = 'Error exporting URLs: ' . $e->getMessage();
    header('Location: view-project.php?id=' . $project_numeric_id);
    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 '';
}
?>