<?php
/**
 * Export Selection URLs
 * Location: /clients/export-selection-urls.php
 * 
 * Exports URLs assigned to a specific selection as CSV,
 * with optional status filtering.
 * Includes: Start Time, End Time, LOI, IP Address, IP Duplication, Quality Flag
 */
ini_set('display_errors', 0);
require_once 'client-session.php';
requireClientLogin();

$selection_id = isset($_GET['selection_id']) ? (int)$_GET['selection_id'] : 0;
$status_filter = $_GET['status'] ?? 'all';

if (!$selection_id) {
    header('Location: projects-list.php');
    exit;
}

$pdo = getClientDBConnection();

// Verify ownership
$stmt = $pdo->prepare("
    SELECT ps.*, p.project_id AS project_code, p.project_name, p.eloi
    FROM project_selections ps
    JOIN projects p ON ps.project_id = p.id
    WHERE ps.id = ? AND ps.client_id = ?
");
$stmt->execute([$selection_id, $_SESSION['client_id']]);
$selection = $stmt->fetch();

if (!$selection) {
    header('Location: projects-list.php');
    exit;
}

// Detect duplicate IPs within this selection
$stmt = $pdo->prepare("
    SELECT respondent_ip, COUNT(*) as ip_count 
    FROM survey_urls 
    WHERE assigned_to_selection_id = ? 
      AND respondent_ip IS NOT NULL 
      AND respondent_ip != ''
    GROUP BY respondent_ip 
    HAVING COUNT(*) > 1
");
$stmt->execute([$selection_id]);
$duplicate_ips = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

// Build query
$where = "assigned_to_selection_id = ?";
$params = [$selection_id];

$valid_statuses = ['assigned','sent','clicked','complete','partial','earlyscreenout','latescreenout','quotafull','timeout'];
if ($status_filter !== 'all' && in_array($status_filter, $valid_statuses)) {
    $where .= " AND status = ?";
    $params[] = $status_filter;
}

$stmt = $pdo->prepare("
    SELECT unique_identifier, client_url, rr_proxy_url, status, 
           is_sent, sent_to_user_id, sent_at, clicked_at, completed_at,
           actual_loi_seconds, respondent_ip, quality_flag, quality_notes,
           created_at
    FROM survey_urls
    WHERE $where
    ORDER BY id ASC
");
$stmt->execute($params);
$urls = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Speedster threshold
$eloi = (int)($selection['eloi'] ?? 10);
$threshold_sec = $eloi * 60 * (33.33 / 100);

// Generate filename
$safe_name = preg_replace('/[^a-zA-Z0-9]/', '_', $selection['selection_name'] ?? 'selection');
$filename = $selection['project_code'] . '_' . $safe_name . '_' . $status_filter . '_' . date('Ymd') . '.csv';

// Output CSV
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Pragma: no-cache');
header('Expires: 0');

$output = fopen('php://output', 'w');

// Header row
fputcsv($output, [
    'Unique ID', 'Client URL', 'Proxy URL', 'Status',
    'Sent', 'Member ID', 'Sent At', 
    'Start Time', 'End Time', 'LOI (minutes)', 'Speedster',
    'IP Address', 'IP Duplicate', 'IP Dup Count',
    'Quality Flag', 'Quality Notes', 'Created At'
]);

foreach ($urls as $url) {
    $loi_seconds = $url['actual_loi_seconds'];
    $loi_min = ($loi_seconds !== null) ? round($loi_seconds / 60, 1) : '';
    $is_speedster = ($loi_seconds !== null && $loi_seconds < $threshold_sec) ? 'Yes' : '';
    
    $ip = $url['respondent_ip'] ?? '';
    $is_ip_dup = (!empty($ip) && isset($duplicate_ips[$ip])) ? 'Yes' : '';
    $ip_dup_count = (!empty($ip) && isset($duplicate_ips[$ip])) ? $duplicate_ips[$ip] : '';

    fputcsv($output, [
        $url['unique_identifier'],
        $url['client_url'],
        $url['rr_proxy_url'],
        $url['status'],
        $url['is_sent'] ? 'Yes' : 'No',
        $url['sent_to_user_id'] ?? '',
        $url['sent_at'] ?? '',
        $url['clicked_at'] ?? '',
        $url['completed_at'] ?? '',
        $loi_min,
        $is_speedster,
        $ip,
        $is_ip_dup,
        $ip_dup_count,
        $url['quality_flag'] ?? '',
        $url['quality_notes'] ?? '',
        $url['created_at']
    ]);
}

fclose($output);
exit;