<?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 project ID from URL
$project_id = isset($_GET['id']) ? (int)$_GET['id'] : 0;

if (!$project_id) {
    die('Invalid project ID');
}

// Verify user has access to this project
$stmt = $db->prepare("SELECT id, title FROM projects WHERE id = ? AND created_by = ?");
$stmt->bind_param('ii', $project_id, $_SESSION['user_id']);
$stmt->execute();
$project = $stmt->get_result()->fetch_assoc();

if (!$project) {
    die('Project not found or access denied');
}

// Get all selections for this project
$stmt = $db->prepare("SELECT id, name FROM selections WHERE project_id = ? ORDER BY created_at ASC");
$stmt->bind_param('i', $project_id);
$stmt->execute();
$selections_result = $stmt->get_result();
$selections = [];
while ($row = $selections_result->fetch_assoc()) {
    $selections[] = $row;
}

if (empty($selections)) {
    die('No selections found for this project');
}

// Get 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', $project_id);
$stmt->execute();
$connected_survey = $stmt->get_result()->fetch_assoc();

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

// Get survey 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;
}

// 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'];
}

// Set headers for CSV download
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="project_' . $project_id . '_data_' . date('Y-m-d') . '.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 = ['Selection Name', 'Panelist ID'];

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

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

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

// Process each selection and its members
foreach ($selections as $selection) {
    // Get members with their attributes
    $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();
    
    while ($member = $members_result->fetch_assoc()) {
        $row = [$selection['name'], $member['panelist_id']];
        
        // Get member attributes
        $attribute_values = json_decode($member['attribute_values'], true);
        
        // Add attribute values
        foreach ($attributes as $attr_id => $attr_name) {
            if (isset($attribute_values[$attr_id])) {
                // Handle array attributes (multiple choice)
                if (is_array($attribute_values[$attr_id])) {
                    $row[] = implode(", ", $attribute_values[$attr_id]);
                } else {
                    $row[] = $attribute_values[$attr_id];
                }
            } else {
                $row[] = '';
            }
        }
        
        // Get responses for this member
        $stmt = $db->prepare("
            SELECT question_id, response_text
            FROM synthetic_responses
            WHERE selection_id = ? AND panelist_id = ?
        ");
        $stmt->bind_param('is', $selection['id'], $member['panelist_id']);
        $stmt->execute();
        $responses_result = $stmt->get_result();
        
        $responses = [];
        while ($response = $responses_result->fetch_assoc()) {
            $responses[$response['question_id']] = $response['response_text'];
        }
        
        // Add response values
        foreach ($questions as $question) {
            $row[] = isset($responses[$question['id']]) ? $responses[$question['id']] : '';
        }
        
        // Write the row
        fputcsv($output, $row);
    }
}

fclose($output);
exit;