<?php
/**
 * Export Conference Delegates to Excel
 * GHAMECC Choir Management Platform
 */

require_once '../includes/auth.php';
require_once '../config/database.php';

// Require login
requireLogin();

// Get export level from URL (society, circuit, or diocese)
$level = isset($_GET['level']) ? $_GET['level'] : 'society';

// Check if user has appropriate permissions based on level
$hasAccess = false;
$userId = $_SESSION['user_id'];

if ($level === 'society' && hasRole(['choirmaster', 'society_secretary', 'society_treasurer'])) {
    $hasAccess = true;
} elseif ($level === 'circuit' && hasRole(['circuit_chairman', 'circuit_secretary', 'circuit_treasurer', 'circuit_music_director'])) {
    $hasAccess = true;
} elseif ($level === 'diocese' && hasRole(['diocesan_chairman', 'diocesan_secretary', 'diocesan_treasurer', 'diocesan_dom'])) {
    $hasAccess = true;
} elseif (hasRole(['admin'])) {
    $hasAccess = true;
}

// Redirect if no access
if (!$hasAccess) {
    header("Location: ../index.php");
    exit("Unauthorized access");
}

// Get user's organizational ID based on level
$userEntityId = 0;

if ($level === 'society' && !hasRole(['admin'])) {
    $userQuery = "SELECT society_id FROM users WHERE id = $userId";
    $userResult = fetchRow($userQuery);
    if ($userResult && isset($userResult['society_id'])) {
        $userEntityId = $userResult['society_id'];
    }
} elseif ($level === 'circuit' && !hasRole(['admin'])) {
    $userQuery = "SELECT circuit_id FROM users WHERE id = $userId";
    $userResult = fetchRow($userQuery);
    if ($userResult && isset($userResult['circuit_id'])) {
        $userEntityId = $userResult['circuit_id'];
    }
} elseif ($level === 'diocese' && !hasRole(['admin'])) {
    $userQuery = "SELECT diocese_id FROM users WHERE id = $userId";
    $userResult = fetchRow($userQuery);
    if ($userResult && isset($userResult['diocese_id'])) {
        $userEntityId = $userResult['diocese_id'];
    }
}

// If no entity ID found and not admin, redirect to index
if (!$userEntityId && !hasRole(['admin'])) {
    header("Location: ../index.php");
    exit("No " . $level . " assigned to your account");
}

// Get conference ID from URL
$conferenceId = isset($_GET['conference_id']) ? (int)$_GET['conference_id'] : 0;

// If no conference ID provided, redirect to conferences list
if (!$conferenceId) {
    header("Location: index.php");
    exit("No conference selected");
}

// Get conference details
$conference = fetchRow("SELECT c.*, 
                       CASE 
                           WHEN c.type = 'circuit' THEN ct.name 
                           WHEN c.type = 'diocese' THEN d.name 
                       END as entity_name,
                       c.type as conference_type
                       FROM conferences c
                       LEFT JOIN circuits ct ON (c.entity_id = ct.id AND c.type = 'circuit')
                       LEFT JOIN dioceses d ON (c.entity_id = d.id AND c.type = 'diocese')
                       WHERE c.id = $conferenceId AND (c.type = 'circuit' OR c.type = 'diocese')");

// If conference not found, redirect
if (!$conference) {
    header("Location: index.php");
    exit("Conference not found");
}

// Check if user has access to this conference based on level
$hasConferenceAccess = false;

if (hasRole(['admin'])) {
    $hasConferenceAccess = true;
} elseif ($level === 'society') {
    // Get society information
    $society = fetchRow("SELECT s.*, c.name as circuit_name, d.name as diocese_name, d.id as diocese_id 
                        FROM societies s 
                        JOIN circuits c ON s.circuit_id = c.id
                        JOIN dioceses d ON c.diocese_id = d.id
                        WHERE s.id = $userEntityId");

    if (!$society) {
        header("Location: ../index.php");
        exit("Society information not found");
    }

    // For circuit conferences, check if society belongs to the conference's circuit
    if ($conference['conference_type'] == 'circuit' && $society['circuit_id'] == $conference['entity_id']) {
        $hasConferenceAccess = true;
    }
    // For diocese conferences, check if society belongs to the conference's diocese
    elseif ($conference['conference_type'] == 'diocese' && $society['diocese_id'] == $conference['entity_id']) {
        $hasConferenceAccess = true;
    }
} elseif ($level === 'circuit') {
    // Get circuit information
    $circuit = fetchRow("SELECT c.*, d.id as diocese_id 
                        FROM circuits c 
                        JOIN dioceses d ON c.diocese_id = d.id
                        WHERE c.id = $userEntityId");

    if (!$circuit) {
        header("Location: ../index.php");
        exit("Circuit information not found");
    }

    // For circuit conferences, check if user's circuit matches the conference's circuit
    if ($conference['conference_type'] == 'circuit' && $userEntityId == $conference['entity_id']) {
        $hasConferenceAccess = true;
    }
    // For diocese conferences, check if circuit belongs to the conference's diocese
    elseif ($conference['conference_type'] == 'diocese' && $circuit['diocese_id'] == $conference['entity_id']) {
        $hasConferenceAccess = true;
    }
} elseif ($level === 'diocese') {
    // For diocese conferences, check if user's diocese matches the conference's diocese
    if ($conference['conference_type'] == 'diocese' && $userEntityId == $conference['entity_id']) {
        $hasConferenceAccess = true;
    }
}

// Redirect if no access
if (!$hasConferenceAccess) {
    header("Location: index.php");
    exit("You don't have access to this conference");
}

// Get delegates based on level
$delegates = [];

if ($level === 'society') {
    // Get delegates for society
    $delegates = fetchRows("SELECT cr.id, cr.payment_status, cr.registration_date, cr.notes, 
                          m.id as member_id, m.first_name, m.last_name as surname, m.email, m.contact_number as phone, 
                          m.sex as gender, m.part as voice_part, m.dob as date_of_birth,
                          s.name as society_name
                          FROM conference_registrations cr
                          JOIN members m ON cr.member_id = m.id
                          JOIN societies s ON m.society_id = s.id
                          WHERE cr.conference_id = $conferenceId
                          AND m.society_id = $userEntityId
                          ORDER BY m.last_name, m.first_name");
} elseif ($level === 'circuit') {
    // Get delegates for circuit
    $delegates = fetchRows("SELECT cr.id, cr.payment_status, cr.registration_date, cr.notes, 
                          m.id as member_id, m.first_name, m.last_name as surname, m.email, m.contact_number as phone, 
                          m.sex as gender, m.part as voice_part, m.dob as date_of_birth,
                          s.name as society_name, s.id as society_id
                          FROM conference_registrations cr
                          JOIN members m ON cr.member_id = m.id
                          JOIN societies s ON m.society_id = s.id
                          JOIN circuits c ON s.circuit_id = c.id
                          WHERE cr.conference_id = $conferenceId
                          AND s.circuit_id = $userEntityId
                          ORDER BY s.name, m.last_name, m.first_name");
} elseif ($level === 'diocese') {
    // Get delegates for diocese
    $delegates = fetchRows("SELECT cr.id, cr.payment_status, cr.registration_date, cr.notes, 
                          m.id as member_id, m.first_name, m.last_name as surname, m.email, m.contact_number as phone, 
                          m.sex as gender, m.part as voice_part, m.dob as date_of_birth,
                          s.name as society_name, s.id as society_id,
                          c.name as circuit_name, c.id as circuit_id
                          FROM conference_registrations cr
                          JOIN members m ON cr.member_id = m.id
                          JOIN societies s ON m.society_id = s.id
                          JOIN circuits c ON s.circuit_id = c.id
                          JOIN dioceses d ON c.diocese_id = d.id
                          WHERE cr.conference_id = $conferenceId
                          AND d.id = $userEntityId
                          ORDER BY c.name, s.name, m.last_name, m.first_name");
}

// Set headers for Excel download
$filename = "Conference_Delegates_" . ucfirst($level) . "_" . date('Y-m-d') . ".xls";
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Pragma: no-cache");
header("Expires: 0");

// Create Excel content
echo "
<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>
<head>
    <meta http-equiv='Content-Type' content='text/html; charset=utf-8'>
    <style>
        table {border-collapse: collapse;}
        table, th, td {border: 1px solid black;}
        th {background-color: #f2f2f2; font-weight: bold;}
    </style>
</head>
<body>
    <h2>Conference Delegates - " . htmlspecialchars($conference['name']) . "</h2>
    <p><strong>" . ucfirst($conference['conference_type']) . ":</strong> " . htmlspecialchars($conference['entity_name']) . "</p>
    <p><strong>Date:</strong> " . date('F d, Y', strtotime($conference['start_date'])) . " to " . date('F d, Y', strtotime($conference['end_date'])) . "</p>
    <p><strong>Total Delegates:</strong> " . count($delegates) . "</p>
    
    <table>
        <thead>
            <tr>
                <th>No.</th>";

// Add columns based on level
if ($level === 'circuit' || $level === 'diocese') {
    echo "<th>Society</th>";
}
if ($level === 'diocese') {
    echo "<th>Circuit</th>";
}

echo "
                <th>Name</th>
                <th>Phone</th>
                <th>Email</th>
                <th>Gender</th>
                <th>Voice Part</th>
                <th>Payment Status</th>
                <th>Registration Date</th>
                <th>Notes</th>
            </tr>
        </thead>
        <tbody>";

$count = 1;
foreach ($delegates as $delegate) {
    echo "<tr>
            <td>" . $count . "</td>";
    
    // Add society and circuit columns based on level
    if ($level === 'circuit' || $level === 'diocese') {
        echo "<td>" . htmlspecialchars($delegate['society_name']) . "</td>";
    }
    if ($level === 'diocese') {
        echo "<td>" . htmlspecialchars($delegate['circuit_name']) . "</td>";
    }
    
    echo "
            <td>" . htmlspecialchars($delegate['first_name'] . ' ' . $delegate['surname']) . "</td>
            <td>" . htmlspecialchars($delegate['phone']) . "</td>
            <td>" . htmlspecialchars($delegate['email']) . "</td>
            <td>" . htmlspecialchars(ucfirst($delegate['gender'] ?? 'Not specified')) . "</td>
            <td>" . htmlspecialchars(ucfirst($delegate['voice_part'] ?? 'Not specified')) . "</td>
            <td>" . htmlspecialchars(ucfirst(str_replace('_', ' ', $delegate['payment_status']))) . "</td>
            <td>" . date('Y-m-d', strtotime($delegate['registration_date'])) . "</td>
            <td>" . htmlspecialchars($delegate['notes'] ?? '') . "</td>
        </tr>";
    $count++;
}

echo "
        </tbody>
    </table>
    <p>Generated on: " . date('Y-m-d H:i:s') . "</p>
</body>
</html>";
exit;
