<?php
/**
 * Circuits Management
 * GHAMECC Choir Management Platform
 */

// Include necessary files
require_once '../includes/auth.php';
require_once '../config/database.php';

// Require login
requireLogin();

// Set page title and base path for includes
$pageTitle = "Circuits Management";
$basePath = '..';

// Get user role and entity IDs
$userRole = $_SESSION['role'] ?? '';
$userSocietyId = $_SESSION['society_id'] ?? null;
$userCircuitId = $_SESSION['circuit_id'] ?? null;
$userDioceseId = $_SESSION['diocese_id'] ?? null;

// Initialize variables
$circuits = [];
$message = '';
$error = '';

// Process form submission for creating a new circuit
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['create_circuit'])) {
    $name = $_POST['name'] ?? '';
    $code = $_POST['code'] ?? '';
    $dioceseId = $_POST['diocese_id'] ?? null;
    $location = $_POST['location'] ?? '';
    $chairperson = $_POST['chairperson'] ?? '';
    $secretary = $_POST['secretary'] ?? '';
    $contact = $_POST['contact'] ?? '';
    $email = $_POST['email'] ?? '';
    
    // Validate input
    if (empty($name) || empty($code) || empty($dioceseId)) {
        $error = "Please fill all required fields.";
    } else {
        // Debug connection and table
        if (!$conn) {
            $error = "Database connection failed.";
        } else {
            // Check if circuits table exists
            $tableCheck = $conn->query("SHOW TABLES LIKE 'circuits'");
            if (!$tableCheck || $tableCheck->num_rows === 0) {
                $error = "Circuits table does not exist.";
            } else {
                // Check if circuit code already exists
                $checkQuery = "SELECT id FROM circuits WHERE code = ?";
                $stmt = $conn->prepare($checkQuery);
                
                if ($stmt === false) {
                    $error = "Prepare statement failed: " . $conn->error;
                } else {
                    $stmt->bind_param("s", $code);
                    $stmt->execute();
                    $result = $stmt->get_result();
                    
                    if ($result->num_rows > 0) {
                        $error = "Circuit code already exists. Please use a different code.";
                    } else {
                        // Insert circuit - updated to match actual table structure
                        $query = "INSERT INTO circuits (name, code, diocese_id, description, address, contact_person, contact_email, contact_phone, created_at) 
                                  VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW())";
                        
                        // Debug SQL query
                        $error_debug = "Query: " . $query;
                        
                        // Check table structure
                        $tableStructure = $conn->query("DESCRIBE circuits");
                        if (!$tableStructure) {
                            $error = "Cannot get table structure: " . $conn->error;
                        } else {
                            $columns = [];
                            while ($row = $tableStructure->fetch_assoc()) {
                                $columns[] = $row['Field'];
                            }
                            $error_debug .= "<br>Table columns: " . implode(", ", $columns);
                        }
                        
                        $stmt = $conn->prepare($query);
                        
                        if ($stmt === false) {
                            $error = "Prepare statement failed: " . $conn->error . "<br>" . $error_debug;
                        } else {
                            // Map form fields to actual database columns
                            $description = $location ?? ''; // Using location as description
                            $address = $location ?? ''; // Using location as address too
                            $contactPerson = $chairperson ?? ''; // Using chairperson as contact_person
                            $contactEmail = $email ?? ''; // Using email as contact_email
                            $contactPhone = $contact ?? ''; // Using contact as contact_phone
                            
                            $stmt->bind_param("ssisssss", $name, $code, $dioceseId, $description, $address, $contactPerson, $contactEmail, $contactPhone);
                            
                            if ($stmt->execute()) {
                                $message = "Circuit created successfully.";
                            } else {
                                $error = "Error creating circuit: " . $conn->error;
                            }
                            
                            $stmt->close();
                        }
                    }
                }
            }
        }
    }
}

// Process form submission for editing a circuit
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['edit_circuit'])) {
    $circuitId = $_POST['circuit_id'] ?? '';
    $name = $_POST['name'] ?? '';
    $code = $_POST['code'] ?? '';
    $dioceseId = $_POST['diocese_id'] ?? null;
    $location = $_POST['location'] ?? '';
    $chairperson = $_POST['chairperson'] ?? '';
    $secretary = $_POST['secretary'] ?? '';
    $contact = $_POST['contact'] ?? '';
    $email = $_POST['email'] ?? '';
    
    // Validate input
    if (empty($name) || empty($code) || empty($dioceseId) || empty($circuitId)) {
        $error = "Please fill all required fields.";
    } else {
        // Debug connection and table
        if (!$conn) {
            $error = "Database connection failed.";
        } else {
            // Check if circuits table exists
            $tableCheck = $conn->query("SHOW TABLES LIKE 'circuits'");
            if (!$tableCheck || $tableCheck->num_rows === 0) {
                $error = "Circuits table does not exist.";
            } else {
                // Check if circuit code already exists for other circuits
                $checkQuery = "SELECT id FROM circuits WHERE code = ? AND id != ?";
                $stmt = $conn->prepare($checkQuery);
                
                if ($stmt === false) {
                    $error = "Prepare statement failed: " . $conn->error;
                } else {
                    $stmt->bind_param("si", $code, $circuitId);
                    $stmt->execute();
                    $result = $stmt->get_result();
                    
                    if ($result->num_rows > 0) {
                        $error = "Circuit code already exists. Please use a different code.";
                    } else {
                        // Update circuit
                        $query = "UPDATE circuits SET name = ?, code = ?, diocese_id = ?, description = ?, address = ?, 
                                  contact_person = ?, contact_email = ?, contact_phone = ?, updated_at = NOW() 
                                  WHERE id = ?";
                        
                        $stmt = $conn->prepare($query);
                        // Types: name(s), code(s), diocese_id(i), description(s), address(s), contact_person(s), contact_email(s), contact_phone(s), id(i)
                        $stmt->bind_param("ssisssssi", $name, $code, $dioceseId, $location, $location, $chairperson, $email, $contact, $circuitId);
                        
                        if ($stmt->execute()) {
                            $message = "Circuit updated successfully.";
                        } else {
                            $error = "Error updating circuit: " . $conn->error;
                        }
                    }
                    
                    $stmt->close();
                }
            }
        }
    }
}

// Process circuit deletion
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['delete_circuit'])) {
    $circuitId = $_POST['circuit_id'] ?? '';
    
    if (empty($circuitId)) {
        $error = "Invalid circuit ID.";
    } else {
        // Debug connection and table
        if (!$conn) {
            $error = "Database connection failed.";
        } else {
            // Check if circuits table exists
            $tableCheck = $conn->query("SHOW TABLES LIKE 'circuits'");
            if (!$tableCheck || $tableCheck->num_rows === 0) {
                $error = "Circuits table does not exist.";
            } else {
                // Check if circuit has societies
                $checkQuery = "SELECT COUNT(*) as society_count FROM societies WHERE circuit_id = ?";
                $stmt = $conn->prepare($checkQuery);
                
                if ($stmt === false) {
                    $error = "Prepare statement failed: " . $conn->error;
                } else {
                    $stmt->bind_param("i", $circuitId);
                    $stmt->execute();
                    $result = $stmt->get_result();
                    $row = $result->fetch_assoc();
                    
                    if ($row['society_count'] > 0) {
                        $error = "Cannot delete circuit. It has " . $row['society_count'] . " societies associated with it.";
                    } else {
                        // Delete circuit
                        $query = "DELETE FROM circuits WHERE id = ?";
                        $stmt = $conn->prepare($query);
                        
                        if ($stmt === false) {
                            $error = "Prepare statement failed: " . $conn->error;
                        } else {
                            $stmt->bind_param("i", $circuitId);
                            
                            if ($stmt->execute()) {
                                $message = "Circuit deleted successfully.";
                            } else {
                                $error = "Error deleting circuit: " . $conn->error;
                            }
                            
                            $stmt->close();
                        }
                    }
                    
                    $stmt->close();
                }
            }
        }
    }
}

// Get dioceses based on user role and access level
$dioceses = [];
if ($userRole === 'admin') {
    // Admin can see all dioceses
    $query = "SELECT id, name FROM dioceses ORDER BY name";
    $stmt = $conn->prepare($query);
    $stmt->execute();
    $result = $stmt->get_result();
    $dioceses = $result->fetch_all(MYSQLI_ASSOC);
    $stmt->close();
} elseif ($userRole === 'diocesan_secretary' && !empty($userDioceseId)) {
    // Diocese secretary can only see their diocese
    $query = "SELECT id, name FROM dioceses WHERE id = ?";
    $stmt = $conn->prepare($query);
    $stmt->bind_param("i", $userDioceseId);
    $stmt->execute();
    $result = $stmt->get_result();
    $dioceses = $result->fetch_all(MYSQLI_ASSOC);
    $stmt->close();
}

// Get circuits based on user role and access level
if ($userRole === 'admin') {
    // Admin can see all circuits
    $query = "SELECT c.*, d.name as diocese_name, 
              (SELECT COUNT(*) FROM societies WHERE circuit_id = c.id) as society_count
              FROM circuits c
              JOIN dioceses d ON c.diocese_id = d.id
              ORDER BY d.name, c.name";
    $stmt = $conn->prepare($query);
    $stmt->execute();
    $result = $stmt->get_result();
    $circuits = $result->fetch_all(MYSQLI_ASSOC);
    $stmt->close();
} elseif ($userRole === 'diocesan_secretary' && !empty($userDioceseId)) {
    // Diocese secretary can see circuits in their diocese
    $query = "SELECT c.*, d.name as diocese_name, 
              (SELECT COUNT(*) FROM societies WHERE circuit_id = c.id) as society_count
              FROM circuits c
              JOIN dioceses d ON c.diocese_id = d.id
              WHERE c.diocese_id = ?
              ORDER BY c.name";
    $stmt = $conn->prepare($query);
    $stmt->bind_param("i", $userDioceseId);
    $stmt->execute();
    $result = $stmt->get_result();
    $circuits = $result->fetch_all(MYSQLI_ASSOC);
    $stmt->close();
} elseif ($userRole === 'circuit_secretary' && !empty($userCircuitId)) {
    // Circuit secretary can only see their circuit
    $query = "SELECT c.*, d.name as diocese_name, 
              (SELECT COUNT(*) FROM societies WHERE circuit_id = c.id) as society_count
              FROM circuits c
              JOIN dioceses d ON c.diocese_id = d.id
              WHERE c.id = ?";
    $stmt = $conn->prepare($query);
    $stmt->bind_param("i", $userCircuitId);
    $stmt->execute();
    $result = $stmt->get_result();
    $circuits = $result->fetch_all(MYSQLI_ASSOC);
    $stmt->close();
}

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Circuit Management - GHAMECC Choir Management</title>
    
    <?php include '../includes/favicon.php'; ?>
    
    <!-- Bootstrap CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <!-- Font Awesome -->
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <!-- DataTables CSS -->
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.4/css/dataTables.bootstrap5.min.css">
    <!-- Chart.js -->
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <!-- Custom CSS -->
    <link href="../assets/css/style.css" rel="stylesheet">
</head>
<body>
    <!-- Navigation -->
    <?php include '../includes/navbar.php'; ?>
    
    <div class="container-fluid">
        <div class="row">
            <!-- Sidebar -->
            <?php include '../includes/sidebar.php'; ?>
            
            <!-- Main Content -->
            <main class="col-md-9 ms-sm-auto col-lg-10 px-md-4 py-4">
                <div class="d-flex justify-content-between flex-wrap flex-md-nowrap align-items-center pt-3 pb-2 mb-3 border-bottom">
                    <h1 class="h2"><?php echo $pageTitle; ?></h1>
                    <?php if ($userRole === 'admin' || $userRole === 'diocesan_secretary'): ?>
                    <div class="btn-toolbar mb-2 mb-md-0">
                        <button id="toggleFormButton" class="btn btn-sm btn-primary">
                            <i class="fas fa-plus"></i> Create New Circuit
                        </button>
                    </div>
                    <?php endif; ?>
                </div>
                
                <?php if (!empty($message)): ?>
                    <div class="alert alert-success alert-dismissible fade show" role="alert">
                        <?php echo $message; ?>
                        <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
                    </div>
                <?php endif; ?>

                <?php if (!empty($error)): ?>
                    <div class="alert alert-danger alert-dismissible fade show" role="alert">
                        <?php echo $error; ?>
                        <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
                    </div>
                <?php endif; ?>

                <!-- Content Row -->
                <div class="row">
                    <!-- Create/Edit Circuit Card -->
                    <div class="col-xl-4 col-lg-5 col-md-12 mb-4" id="circuitFormCard" <?php echo (!isset($_GET['edit'])) ? 'style="display: none;"' : ''; ?>>
                        <div class="card shadow mb-4">
                            <div class="card-header py-3">
                                <h6 class="m-0 font-weight-bold text-primary">
                                    <?php echo isset($_GET['edit']) ? 'Edit Circuit' : 'Create New Circuit'; ?>
                                </h6>
                            </div>
                            <div class="card-body">
                                <?php if (($userRole === 'admin' || $userRole === 'diocesan_secretary') && !empty($dioceses)): ?>
                                    <form method="post" action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']); ?>">
                                        <?php
                                        // If editing, get circuit details
                                        if (isset($_GET['edit'])) {
                                            $editId = (int)$_GET['edit'];
                                            
                                            // Reconnect to database
                                            require_once '../config/database.php';
                                            
                                            $query = "SELECT * FROM circuits WHERE id = ?";
                                            $stmt = $conn->prepare($query);
                                            $stmt->bind_param("i", $editId);
                                            $stmt->execute();
                                            $result = $stmt->get_result();
                                            $editCircuit = $result->fetch_assoc();
                                            $stmt->close();
                                            $conn->close();
                                            
                                            if (!$editCircuit) {
                                                echo '<div class="alert alert-danger">Circuit not found.</div>';
                                            } else {
                                                echo '<input type="hidden" name="circuit_id" value="' . $editCircuit['id'] . '">';
                                            }
                                        }
                                        ?>
                                        
                                        <div class="mb-3">
                                            <label for="name" class="form-label">Circuit Name</label>
                                            <input type="text" class="form-control" id="name" name="name" required
                                                value="<?php echo isset($editCircuit) ? htmlspecialchars($editCircuit['name']) : ''; ?>">
                                        </div>

                                        <div class="mb-3">
                                            <label for="code" class="form-label">Circuit Code</label>
                                            <input type="text" class="form-control" id="code" name="code" required
                                                value="<?php echo isset($editCircuit) ? htmlspecialchars($editCircuit['code']) : ''; ?>">
                                            <small class="form-text text-muted">Unique identifier for the circuit (e.g., ACC, WCC)</small>
                                        </div>

                                        <div class="mb-3">
                                            <label for="diocese_id" class="form-label">Diocese</label>
                                            <select class="form-select" id="diocese_id" name="diocese_id" required>
                                                <option value="">Select Diocese</option>
                                                <?php foreach ($dioceses as $diocese): ?>
                                                    <option value="<?php echo $diocese['id']; ?>" 
                                                        <?php echo (isset($editCircuit) && $editCircuit['diocese_id'] == $diocese['id']) ? 'selected' : ''; ?>>
                                                        <?php echo htmlspecialchars($diocese['name']); ?>
                                                    </option>
                                                <?php endforeach; ?>
                                            </select>
                                        </div>

                                        <div class="mb-3">
                                            <label for="location" class="form-label">Location</label>
                                            <input type="text" class="form-control" id="location" name="location"
                                                value="<?php echo isset($editCircuit) ? htmlspecialchars($editCircuit['address']) : ''; ?>">
                                        </div>

                                        <div class="mb-3">
                                            <label for="chairperson" class="form-label">Chairperson</label>
                                            <input type="text" class="form-control" id="chairperson" name="chairperson"
                                                value="<?php echo isset($editCircuit) ? htmlspecialchars($editCircuit['contact_person']) : ''; ?>">
                                        </div>

                                        <div class="mb-3">
                                            <label for="secretary" class="form-label">Secretary</label>
                                            <input type="text" class="form-control" id="secretary" name="secretary"
                                                value="<?php echo isset($editCircuit) ? htmlspecialchars($editCircuit['secretary'] ?? '') : ''; ?>">
                                        </div>

                                        <div class="mb-3">
                                            <label for="contact" class="form-label">Contact Number</label>
                                            <input type="text" class="form-control" id="contact" name="contact"
                                                value="<?php echo isset($editCircuit) ? htmlspecialchars($editCircuit['contact_phone']) : ''; ?>">
                                        </div>

                                        <div class="mb-3">
                                            <label for="email" class="form-label">Email Address</label>
                                            <input type="email" class="form-control" id="email" name="email"
                                                value="<?php echo isset($editCircuit) ? htmlspecialchars($editCircuit['contact_email']) : ''; ?>">
                                        </div>

                                        <div class="d-grid gap-2">
                                            <?php if (isset($_GET['edit'])): ?>
                                                <button type="submit" name="edit_circuit" class="btn btn-primary">Update Circuit</button>
                                                <a href="index.php" class="btn btn-secondary">Cancel</a>
                                            <?php else: ?>
                                                <button type="submit" name="create_circuit" class="btn btn-success">Create Circuit</button>
                                            <?php endif; ?>
                                        </div>
                                    </form>
                                <?php elseif (empty($dioceses)): ?>
                                    <div class="alert alert-info">
                                        No dioceses available. Please create a diocese first.
                                    </div>
                                <?php else: ?>
                                    <div class="alert alert-warning">
                                        You do not have permission to create or edit circuits.
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>

                    <!-- Circuits List Card -->
                    <div class="col-xl-8 col-lg-7 col-md-12 mb-4">
                        <div class="card shadow mb-4">
                            <div class="card-header py-3">
                                <h6 class="m-0 font-weight-bold text-primary">Circuits List</h6>
                            </div>
                            <div class="card-body">
                                <?php if (empty($circuits)): ?>
                                    <div class="alert alert-info">
                                        No circuits found.
                                    </div>
                                <?php else: ?>
                                    <div class="table-responsive">
                                        <table class="table table-bordered" id="circuitsTable" width="100%" cellspacing="0">
                                            <thead>
                                                <tr>
                                                    <th>Name</th>
                                                    <th>Code</th>
                                                    <th>Diocese</th>
                                                    <th>Location</th>
                                                    <th>Societies</th>
                                                    <th>Actions</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php foreach ($circuits as $circuit): ?>
                                                    <tr>
                                                        <td><?php echo htmlspecialchars($circuit['name']); ?></td>
                                                        <td><?php echo htmlspecialchars($circuit['code']); ?></td>
                                                        <td><?php echo htmlspecialchars($circuit['diocese_name']); ?></td>
                                                        <td><?php echo htmlspecialchars($circuit['address']); ?></td>
                                                        <td>
                                                            <a href="../societies/index.php?circuit=<?php echo $circuit['id']; ?>">
                                                                <?php echo $circuit['society_count']; ?> societies
                                                            </a>
                                                        </td>
                                                        <td>
                                                            <div class="btn-group btn-group-sm">
                                                                <a href="view.php?id=<?php echo $circuit['id']; ?>" class="btn btn-info">
                                                                    <i class="fas fa-eye"></i>
                                                                </a>
                                                                <?php if ($userRole === 'admin' || ($userRole === 'diocesan_secretary' && $userDioceseId == $circuit['diocese_id'])): ?>
                                                                    <a href="index.php?edit=<?php echo $circuit['id']; ?>" class="btn btn-primary">
                                                                        <i class="fas fa-edit"></i>
                                                                    </a>
                                                                    <?php if ($circuit['society_count'] == 0): ?>
                                                                        <button type="button" class="btn btn-danger" data-bs-toggle="modal" data-bs-target="#deleteModal<?php echo $circuit['id']; ?>">
                                                                            <i class="fas fa-trash"></i>
                                                                        </button>
                                                                    <?php endif; ?>
                                                                <?php endif; ?>
                                                            </div>
                                                            
                                                            <!-- Delete Modal -->
                                                            <div class="modal fade" id="deleteModal<?php echo $circuit['id']; ?>" tabindex="-1" aria-labelledby="deleteModalLabel<?php echo $circuit['id']; ?>" aria-hidden="true">
                                                                <div class="modal-dialog">
                                                                    <div class="modal-content">
                                                                        <div class="modal-header">
                                                                            <h5 class="modal-title" id="deleteModalLabel<?php echo $circuit['id']; ?>">Confirm Deletion</h5>
                                                                            <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                                                                        </div>
                                                                        <div class="modal-body">
                                                                            Are you sure you want to delete the circuit "<?php echo htmlspecialchars($circuit['name']); ?>"?
                                                                            This action cannot be undone.
                                                                        </div>
                                                                        <div class="modal-footer">
                                                                            <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                                                                            <form method="post">
                                                                                <input type="hidden" name="circuit_id" value="<?php echo $circuit['id']; ?>">
                                                                                <button type="submit" name="delete_circuit" class="btn btn-danger">Delete</button>
                                                                            </form>
                                                                        </div>
                                                                    </div>
                                                                </div>
                                                            </div>
                                                        </td>
                                                    </tr>
                                                <?php endforeach; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                </div>

                <!-- Circuit Statistics Row -->
                <div class="row">
                    <div class="col-xl-12 col-lg-12">
                        <div class="card shadow mb-4">
                            <div class="card-header py-3">
                                <h6 class="m-0 font-weight-bold text-primary">Circuit Statistics</h6>
                            </div>
                            <div class="card-body">
                                <div class="row">
                                    <!-- Circuits by Diocese Chart -->
                                    <div class="col-xl-6 col-lg-6">
                                        <div class="chart-container" style="height: 300px;">
                                            <canvas id="circuitsByDioceseChart"></canvas>
                                        </div>
                                    </div>
                                    
                                    <!-- Societies by Circuit Chart -->
                                    <div class="col-xl-6 col-lg-6">
                                        <div class="chart-container" style="height: 300px;">
                                            <canvas id="societiesByCircuitChart"></canvas>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </main>
        </div>
    </div>
    
    <!-- Bootstrap JS Bundle with Popper -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
    <!-- jQuery -->
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <!-- DataTables JS -->
    <script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.4/js/dataTables.bootstrap5.min.js"></script>
    
    <script>
    document.addEventListener('DOMContentLoaded', function() {
        // Initialize DataTable
        $('#circuitsTable').DataTable({
            order: [[0, 'asc']]
        });
        
        // Initialize Charts
        const circuitsByDioceseCtx = document.getElementById('circuitsByDioceseChart').getContext('2d');
        const societiesByCircuitCtx = document.getElementById('societiesByCircuitChart').getContext('2d');
        
        <?php
        // Reconnect to database for chart data
        require_once '../config/database.php';
        
        // Get circuits by diocese data
        $dioceseLabels = [];
        $dioceseCounts = [];
        
        $query = "SELECT d.name, COUNT(c.id) as circuit_count 
                  FROM dioceses d 
                  LEFT JOIN circuits c ON d.id = c.diocese_id 
                  GROUP BY d.id 
                  ORDER BY circuit_count DESC";
        
        if ($userRole === 'diocesan_secretary' && !empty($userDioceseId)) {
            $query = "SELECT d.name, COUNT(c.id) as circuit_count 
                      FROM dioceses d 
                      LEFT JOIN circuits c ON d.id = c.diocese_id 
                      WHERE d.id = $userDioceseId 
                      GROUP BY d.id";
        }
        
        $result = $conn->query($query);
        
        if ($result && $result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $dioceseLabels[] = $row['name'];
                $dioceseCounts[] = $row['circuit_count'];
            }
        }
        
        // Get societies by circuit data
        $circuitLabels = [];
        $societyCounts = [];
        
        $query = "SELECT c.name, COUNT(s.id) as society_count 
                  FROM circuits c 
                  LEFT JOIN societies s ON c.id = s.circuit_id";
        
        if ($userRole === 'admin') {
            $query .= " GROUP BY c.id ORDER BY society_count DESC LIMIT 10";
        } elseif ($userRole === 'diocesan_secretary' && !empty($userDioceseId)) {
            $query .= " WHERE c.diocese_id = $userDioceseId GROUP BY c.id ORDER BY society_count DESC";
        } elseif ($userRole === 'circuit_secretary' && !empty($userCircuitId)) {
            $query .= " WHERE c.id = $userCircuitId GROUP BY c.id";
        }
        
        $result = $conn->query($query);
        
        if ($result && $result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $circuitLabels[] = $row['name'];
                $societyCounts[] = $row['society_count'];
            }
        }
        
        // Close connection at the end of the file after all includes and queries
        // $conn->close();
        ?>
        
        // Circuits by Diocese Chart
        new Chart(circuitsByDioceseCtx, {
            type: 'pie',
            data: {
                labels: <?php echo json_encode($dioceseLabels); ?>,
                datasets: [{
                    data: <?php echo json_encode($dioceseCounts); ?>,
                    backgroundColor: [
                        '#4e73df', '#1cc88a', '#36b9cc', '#f6c23e', '#e74a3b',
                        '#5a5c69', '#858796', '#6610f2', '#fd7e14', '#20c9a6'
                    ]
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        position: 'bottom'
                    },
                    title: {
                        display: true,
                        text: 'Circuits by Diocese'
                    }
                }
            }
        });
        
        // Societies by Circuit Chart
        new Chart(societiesByCircuitCtx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode($circuitLabels); ?>,
                datasets: [{
                    label: 'Number of Societies',
                    data: <?php echo json_encode($societyCounts); ?>,
                    backgroundColor: '#4e73df',
                    borderColor: '#4e73df',
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            precision: 0
                        }
                    }
                },
                plugins: {
                    title: {
                        display: true,
                        text: 'Societies by Circuit'
                    }
                }
            }
        });
        
        // Toggle form visibility
        document.getElementById('toggleFormButton').addEventListener('click', function() {
            const formCard = document.getElementById('circuitFormCard');
            if (formCard.style.display === 'none') {
                formCard.style.display = 'block';
            } else {
                formCard.style.display = 'none';
            }
        });
    });
    </script>
    
    <?php
    // Close the database connection at the very end of the file
    // after all includes and operations are complete
    if (isset($conn) && $conn) {
        $conn->close();
    }
    ?>
</body>
</html>
