<?php
require_once 'includes/config.php';
require_once 'includes/functions.php';
require_once 'includes/auth.php';
require_once 'includes/db.php';

$auth = new Auth();

// Check if user is logged in
if (!$auth->isLoggedIn()) {
    die('Unauthorized');
}

$db = Database::getInstance();

// Get selection ID from URL - handle both 'id' and 'selection_id' parameters
$selection_id = isset($_GET['id']) ? (int)$_GET['id'] : (isset($_GET['selection_id']) ? (int)$_GET['selection_id'] : 0);

if (!$selection_id) {
    die('Invalid selection ID');
}

// Debug: Log the selection ID being processed
error_log("Export: Processing selection ID: " . $selection_id);

// Verify user has access to this selection
$stmt = $db->prepare("
    SELECT s.*, p.id as project_id, p.title as project_title
    FROM selections s
    JOIN projects p ON s.project_id = p.id
    WHERE s.id = ? AND p.created_by = ?
");
$stmt->bind_param('ii', $selection_id, $_SESSION['user_id']);
$stmt->execute();
$selection = $stmt->get_result()->fetch_assoc();

if (!$selection) {
    die('Selection not found or access denied');
}

// Get project's connected survey
$stmt = $db->prepare("
    SELECT s.id as survey_id, s.title as survey_title 
    FROM project_surveys ps
    JOIN surveys s ON ps.survey_id = s.id
    WHERE ps.project_id = ?
    LIMIT 1
");
$stmt->bind_param('i', $selection['project_id']);
$stmt->execute();
$connected_survey = $stmt->get_result()->fetch_assoc();

if (!$connected_survey) {
    die('No survey connected to this project');
}

// Get survey questions (only answerable questions)
$stmt = $db->prepare("
    SELECT id, question_text, question_type
    FROM survey_questions 
    WHERE survey_id = ? AND question_type NOT IN ('section_header', 'descriptive_text', 'page_break')
    ORDER BY question_order ASC
");
$stmt->bind_param('i', $connected_survey['survey_id']);
$stmt->execute();
$questions_result = $stmt->get_result();
$questions = [];
while ($question = $questions_result->fetch_assoc()) {
    $questions[] = $question;
}

// Debug: Log question count
error_log("Export: Found " . count($questions) . " questions");

// Get all attributes
$attributes = [];
$attr_query = $db->query("SELECT id, name FROM attributes ORDER BY created_at ASC");
while ($attr = $attr_query->fetch_assoc()) {
    $attributes[$attr['id']] = $attr['name'];
}

// Get members with their attributes and responses
$stmt = $db->prepare("
    SELECT sm.panelist_id, pd.attribute_values
    FROM selection_members sm
    LEFT JOIN panel_data pd ON sm.panelist_id = pd.panelist_id
    WHERE sm.selection_id = ?
    ORDER BY sm.id ASC
");
$stmt->bind_param('i', $selection_id);
$stmt->execute();
$members_result = $stmt->get_result();
$members = [];
while ($member = $members_result->fetch_assoc()) {
    $members[$member['panelist_id']] = [
        'attributes' => json_decode($member['attribute_values'], true),
        'responses' => []
    ];
}

// Debug: Log member count
error_log("Export: Found " . count($members) . " members");

// Get all responses for this selection from synthetic_responses table
$stmt = $db->prepare("
    SELECT sr.panelist_id, sr.question_id, sr.response_text
    FROM synthetic_responses sr
    WHERE sr.selection_id = ?
");
$stmt->bind_param('i', $selection_id);
$stmt->execute();
$responses_result = $stmt->get_result();

$total_responses = 0;
// Organize responses by panelist
while ($response = $responses_result->fetch_assoc()) {
    $total_responses++;
    if (isset($members[$response['panelist_id']])) {
        $members[$response['panelist_id']]['responses'][$response['question_id']] = $response['response_text'];
    }
}

// Debug: Log response count
error_log("Export: Found " . $total_responses . " total responses in database");

// If no responses found, let's check if there are any responses in the database at all for this selection
if ($total_responses === 0) {
    // Check if responses exist in either table
    $check_synthetic = $db->prepare("SELECT COUNT(*) as count FROM synthetic_responses WHERE selection_id = ?");
    $check_synthetic->bind_param('i', $selection_id);
    $check_synthetic->execute();
    $synthetic_count = $check_synthetic->get_result()->fetch_assoc()['count'];
    
    error_log("Export DEBUG: synthetic_responses count: " . $synthetic_count);
    
    // Also check survey_responses table just in case
    $check_survey = $db->prepare("SELECT COUNT(*) as count FROM survey_responses WHERE selection_id = ?");
    $check_survey->bind_param('i', $selection_id);
    $check_survey->execute();
    $survey_count = $check_survey->get_result()->fetch_assoc()['count'];
    
    error_log("Export DEBUG: survey_responses count: " . $survey_count);
    
    if ($synthetic_count === 0 && $survey_count === 0) {
        die('No responses found for this selection. Please generate responses first.');
    }
}

// Set headers for CSV download
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="selection_' . $selection_id . '_raw_data_' . date('Y-m-d_H-i') . '.csv"');

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

// Add BOM for Excel UTF-8 compatibility
fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));

// Prepare headers
$headers = ['Panelist ID'];

// Add attribute headers
foreach ($attributes as $attr_name) {
    $headers[] = 'Attr_' . $attr_name;
}

// Add question headers
foreach ($questions as $question) {
    $question_header = 'Q' . $question['id'] . '_' . substr(preg_replace('/\s+/', '_', $question['question_text']), 0, 50);
    $headers[] = $question_header;
}

// Write headers
fputcsv($output, $headers);

// Write data rows
foreach ($members as $panelist_id => $member_data) {
    $row = [$panelist_id];
    
    // Add attribute values
    $attributes_data = $member_data['attributes'] ?? [];
    foreach ($attributes as $attr_id => $attr_name) {
        if (isset($attributes_data[$attr_id])) {
            // Handle array attributes (multiple choice)
            if (is_array($attributes_data[$attr_id])) {
                $row[] = implode(", ", $attributes_data[$attr_id]);
            } else {
                $row[] = $attributes_data[$attr_id];
            }
        } else {
            $row[] = '';
        }
    }
    
    // Add response values
    $responses_data = $member_data['responses'] ?? [];
    foreach ($questions as $question) {
        if (isset($responses_data[$question['id']])) {
            $row[] = $responses_data[$question['id']];
        } else {
            $row[] = 'No response'; // This will show if response is missing
        }
    }
    
    // Write the row
    fputcsv($output, $row);
}

fclose($output);
exit;
?>