<?php
/**
 * Societies 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 = "Societies 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
$societies = [];
$message = '';
$error = '';

// Process form submission for creating a new society
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['create_society'])) {
    $name = $_POST['name'] ?? '';
    $code = $_POST['code'] ?? '';
    $circuitId = $_POST['circuit_id'] ?? null;
    // Support both legacy and new field names
    $location = $_POST['location'] ?? ($_POST['address'] ?? '');
    $chairperson = $_POST['chairperson'] ?? ($_POST['contact_person'] ?? '');
    $secretary = $_POST['secretary'] ?? '';
    $contact = $_POST['contact'] ?? ($_POST['contact_phone'] ?? '');
    $email = $_POST['email'] ?? ($_POST['contact_email'] ?? '');
    $established = $_POST['established'] ?? null;
    
    // Validate input
    if (empty($name) || empty($code) || empty($circuitId)) {
        $error = "Please fill all required fields.";
    } else {
        // Check if society code already exists
        $checkQuery = "SELECT id FROM societies WHERE code = ?";
        $stmt = $conn->prepare($checkQuery);
        $stmt->bind_param("s", $code);
        $stmt->execute();
        $result = $stmt->get_result();
        
        if ($result->num_rows > 0) {
            $error = "Society code already exists. Please use a different code.";
        } else {
            // Insert society - updated to match actual table structure with new columns
            $query = "INSERT INTO societies (name, code, circuit_id, description, address, contact_person, secretary, contact_email, contact_phone, established, created_at) 
                      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";
            
            $stmt = $conn->prepare($query);
            
            if ($stmt === false) {
                $error = "Prepare statement failed: " . $conn->error;
            } 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("ssisssssss", $name, $code, $circuitId, $description, $address, $contactPerson, $secretary, $contactEmail, $contactPhone, $established);
                
                if ($stmt->execute()) {
                    $message = "Society created successfully.";
                } else {
                    $error = "Error creating society: " . $conn->error;
                }
            }
        }
        
        $stmt->close();
    }
}

// Process form submission for editing a society
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['edit_society'])) {
    $societyId = $_POST['society_id'] ?? '';
    $name = $_POST['name'] ?? '';
    $code = $_POST['code'] ?? '';
    $circuitId = $_POST['circuit_id'] ?? null;
    // Support both legacy and new field names
    $location = $_POST['location'] ?? ($_POST['address'] ?? '');
    $chairperson = $_POST['chairperson'] ?? ($_POST['contact_person'] ?? '');
    $secretary = $_POST['secretary'] ?? '';
    $contact = $_POST['contact'] ?? ($_POST['contact_phone'] ?? '');
    $email = $_POST['email'] ?? ($_POST['contact_email'] ?? '');
    $established = $_POST['established'] ?? null;
    
    // Validate input
    if (empty($name) || empty($code) || empty($circuitId) || empty($societyId)) {
        $error = "Please fill all required fields.";
    } else {
        // Check if society code already exists for other societies
        $checkQuery = "SELECT id FROM societies WHERE code = ? AND id != ?";
        $stmt = $conn->prepare($checkQuery);
        $stmt->bind_param("si", $code, $societyId);
        $stmt->execute();
        $result = $stmt->get_result();
        
        if ($result->num_rows > 0) {
            $error = "Society code already exists. Please use a different code.";
        } else {
            // Update society
            $query = "UPDATE societies SET name = ?, code = ?, circuit_id = ?, description = ?, address = ?, 
                      contact_person = ?, secretary = ?, contact_email = ?, contact_phone = ?, established = ?, updated_at = NOW() 
                      WHERE id = ?";
            
            $stmt = $conn->prepare($query);
            // Types: name(s), code(s), circuit_id(i), description(s), address(s), contact_person(s), secretary(s), contact_email(s), contact_phone(s), established(s), id(i)
            $stmt->bind_param("ssisssssssi", $name, $code, $circuitId, $location, $location, $chairperson, $secretary, $email, $contact, $established, $societyId);
            
            if ($stmt->execute()) {
                $message = "Society updated successfully.";
            } else {
                $error = "Error updating society: " . $conn->error;
            }
        }
        
        $stmt->close();
    }
}

// Process society deletion
if (isset($_POST['delete_society']) && isset($_POST['society_id'])) {
    $societyId = (int)$_POST['society_id'];
    
    // Check if the society exists and if the user has permission to delete it
    $query = "SELECT s.*, c.diocese_id FROM societies s 
              JOIN circuits c ON s.circuit_id = c.id 
              WHERE s.id = ?";
    $stmt = $conn->prepare($query);
    $stmt->bind_param("i", $societyId);
    $stmt->execute();
    $result = $stmt->get_result();
    $society = $result->fetch_assoc();
    $stmt->close();
    
    if (!$society) {
        $error = "Society not found.";
    } else {
        // Check if user has permission to delete this society
        $hasPermission = false;
        if ($userRole === 'admin') {
            $hasPermission = true;
        } elseif ($userRole === 'diocesan_secretary' && $userDioceseId == $society['diocese_id']) {
            $hasPermission = true;
        } elseif ($userRole === 'circuit_secretary' && $userCircuitId == $society['circuit_id']) {
            $hasPermission = true;
        }
        
        if (!$hasPermission) {
            $error = "You do not have permission to delete this society.";
        } else {
            // Check if the society has members
            $query = "SELECT COUNT(*) as count FROM members WHERE society_id = ?";
            $stmt = $conn->prepare($query);
            $stmt->bind_param("i", $societyId);
            $stmt->execute();
            $result = $stmt->get_result();
            $memberCount = $result->fetch_assoc()['count'];
            $stmt->close();
            
            if ($memberCount > 0) {
                $error = "Cannot delete society because it has " . $memberCount . " members. Please reassign or remove the members first.";
            } else {
                // Check if the society has any related records in other tables
                $relatedRecords = false;
                
                // Example: Check if society has attendance records
                if (tableExists($conn, 'attendance')) {
                    $query = "SELECT COUNT(*) as count FROM attendance WHERE society_id = ?";
                    $stmt = $conn->prepare($query);
                    $stmt->bind_param("i", $societyId);
                    $stmt->execute();
                    $result = $stmt->get_result();
                    $attendanceCount = $result->fetch_assoc()['count'];
                    $stmt->close();
                    
                    if ($attendanceCount > 0) {
                        $relatedRecords = true;
                        $error = "Cannot delete society because it has related attendance records.";
                    }
                }
                
                // Example: Check if society has financial records
                if (!$relatedRecords && tableExists($conn, 'financial_records')) {
                    $query = "SELECT COUNT(*) as count FROM financial_records WHERE society_id = ?";
                    $stmt = $conn->prepare($query);
                    $stmt->bind_param("i", $societyId);
                    $stmt->execute();
                    $result = $stmt->get_result();
                    $financialCount = $result->fetch_assoc()['count'];
                    $stmt->close();
                    
                    if ($financialCount > 0) {
                        $relatedRecords = true;
                        $error = "Cannot delete society because it has related financial records.";
                    }
                }
                
                // If no related records, proceed with deletion
                if (!$relatedRecords) {
                    $query = "DELETE FROM societies WHERE id = ?";
                    $stmt = $conn->prepare($query);
                    $stmt->bind_param("i", $societyId);
                    
                    if ($stmt->execute()) {
                        $message = "Society deleted successfully.";
                        
                        // Log the deletion
                        if (function_exists('logActivity')) {
                            logActivity($conn, 'delete', 'societies', $societyId, "Deleted society: " . $society['name']);
                        }
                    } else {
                        $error = "Error deleting society: " . $conn->error;
                    }
                    $stmt->close();
                }
            }
        }
    }
}

// Get circuits based on user role and access level
$circuits = [];
if ($userRole === 'admin') {
    // Admin can see all circuits
    $query = "SELECT c.*, d.name as diocese_name 
              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 
              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 
              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();
}

// Get societies based on user role and filters
$query = "SELECT s.*, c.name as circuit_name, d.name as diocese_name, c.diocese_id 
          FROM societies s
          JOIN circuits c ON s.circuit_id = c.id
          JOIN dioceses d ON c.diocese_id = d.id";

// Apply filters based on URL parameters and user role
$whereConditions = [];
$params = [];
$paramTypes = "";

// Filter by circuit if specified in URL
if (isset($_GET['circuit']) && !empty($_GET['circuit'])) {
    $circuitId = (int)$_GET['circuit'];
    $whereConditions[] = "s.circuit_id = ?";
    $params[] = $circuitId;
    $paramTypes .= "i";
}

// Filter by diocese if specified in URL
if (isset($_GET['diocese']) && !empty($_GET['diocese'])) {
    $dioceseId = (int)$_GET['diocese'];
    $whereConditions[] = "c.diocese_id = ?";
    $params[] = $dioceseId;
    $paramTypes .= "i";
}

// Apply role-based filtering
if ($userRole === 'diocesan_secretary' && !empty($userDioceseId)) {
    $whereConditions[] = "c.diocese_id = ?";
    $params[] = $userDioceseId;
    $paramTypes .= "i";
} elseif ($userRole === 'circuit_secretary' && !empty($userCircuitId)) {
    $whereConditions[] = "s.circuit_id = ?";
    $params[] = $userCircuitId;
    $paramTypes .= "i";
} elseif ($userRole === 'society_secretary' && !empty($userSocietyId)) {
    $whereConditions[] = "s.id = ?";
    $params[] = $userSocietyId;
    $paramTypes .= "i";
}

// Combine all where conditions
if (!empty($whereConditions)) {
    $query .= " WHERE " . implode(" AND ", $whereConditions);
}

$query .= " ORDER BY s.name ASC";

// Prepare and execute the query
$stmt = $conn->prepare($query);
if (!empty($params)) {
    $stmt->bind_param($paramTypes, ...$params);
}
$stmt->execute();
$result = $stmt->get_result();
$societies = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();

// Get all circuits for filtering dropdown
$circuitsQuery = "SELECT c.id, c.name, d.name as diocese_name 
                 FROM circuits c
                 JOIN dioceses d ON c.diocese_id = d.id";

// Apply role-based filtering for circuits dropdown
if ($userRole === 'diocesan_secretary' && !empty($userDioceseId)) {
    $circuitsQuery .= " WHERE c.diocese_id = $userDioceseId";
} elseif ($userRole === 'circuit_secretary' && !empty($userCircuitId)) {
    $circuitsQuery .= " WHERE c.id = $userCircuitId";
}

$circuitsQuery .= " ORDER BY d.name, c.name";
$circuitsResult = $conn->query($circuitsQuery);
$circuitsForFilter = [];
if ($circuitsResult && $circuitsResult->num_rows > 0) {
    while ($row = $circuitsResult->fetch_assoc()) {
        $circuitsForFilter[] = $row;
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Society 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' ||  $userRole === 'circuit_chairman' ||$userRole === 'circuit_secretary') && !isset($_GET['create']) && !isset($_GET['edit'])): ?>
                        <a href="?create=1" class="btn btn-primary">
                            <i class="fas fa-plus-circle me-1"></i> Create New Society
                        </a>
                    <?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">
                    <?php if (isset($_GET['create']) || isset($_GET['edit'])): ?>
                    <!-- Create/Edit Society Card -->
                    <div class="col-xl-12 col-lg-12 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">
                                    <?php echo isset($_GET['edit']) ? 'Edit Society' : 'Create New Society'; ?>
                                </h6>
                            </div>
                            <div class="card-body">
                                <?php 
                                // Get circuits based on user role
                                $circuits = [];
                                
                                if ($userRole === 'admin') {
                                    // Admin can see all circuits
                                    $circuitsQuery = "SELECT c.id, c.name, d.name as diocese_name 
                                                     FROM circuits c
                                                     JOIN dioceses d ON c.diocese_id = d.id
                                                     ORDER BY d.name, c.name";
                                    $circuitsResult = $conn->query($circuitsQuery);
                                    
                                    if ($circuitsResult && $circuitsResult->num_rows > 0) {
                                        while ($row = $circuitsResult->fetch_assoc()) {
                                            $circuits[] = $row;
                                        }
                                    }
                                } elseif ($userRole === 'diocesan_secretary' && !empty($userDioceseId)) {
                                    // Diocesan secretary can see circuits in their diocese
                                    $circuitsQuery = "SELECT c.id, c.name, d.name as diocese_name 
                                                     FROM circuits c
                                                     JOIN dioceses d ON c.diocese_id = d.id
                                                     WHERE c.diocese_id = ?
                                                     ORDER BY c.name";
                                    $stmt = $conn->prepare($circuitsQuery);
                                    $stmt->bind_param("i", $userDioceseId);
                                    $stmt->execute();
                                    $result = $stmt->get_result();
                                    
                                    if ($result && $result->num_rows > 0) {
                                        while ($row = $result->fetch_assoc()) {
                                            $circuits[] = $row;
                                        }
                                    }
                                    $stmt->close();
                                } elseif (in_array($userRole, ['circuit_secretary', 'circuit_chairman']) && !empty($userCircuitId)) {
                                    // Circuit roles can only see their own circuit
                                    $circuitsQuery = "SELECT c.id, c.name, d.name as diocese_name 
                                                     FROM circuits c
                                                     JOIN dioceses d ON c.diocese_id = d.id
                                                     WHERE c.id = ?";
                                    $stmt = $conn->prepare($circuitsQuery);
                                    $stmt->bind_param("i", $userCircuitId);
                                    $stmt->execute();
                                    $result = $stmt->get_result();
                                    
                                    if ($result && $result->num_rows > 0) {
                                        while ($row = $result->fetch_assoc()) {
                                            $circuits[] = $row;
                                        }
                                    }
                                    $stmt->close();
                                }
                                
                                if (!empty($circuits)): 
                                ?>
                                    <form method="post" action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']); ?>">
                                        <?php
                                        // If editing, get society details
                                        if (isset($_GET['edit'])) {
                                            $editId = (int)$_GET['edit'];
                                            
                                            $query = "SELECT * FROM societies WHERE id = ?";
                                            $stmt = $conn->prepare($query);
                                            $stmt->bind_param("i", $editId);
                                            $stmt->execute();
                                            $result = $stmt->get_result();
                                            $editSociety = $result->fetch_assoc();
                                            $stmt->close();
                                            
                                            if (!$editSociety) {
                                                echo '<div class="alert alert-danger">Society not found.</div>';
                                            } else {
                                                echo '<input type="hidden" name="society_id" value="' . $editSociety['id'] . '">';
                                            }
                                        }
                                        ?>
                                        
                                        <div class="row">
                                            <div class="col-md-6 mb-3">
                                                <label for="name" class="form-label">Society Name</label>
                                                <input type="text" class="form-control" id="name" name="name" required
                                                    value="<?php echo isset($editSociety) ? htmlspecialchars($editSociety['name']) : ''; ?>">
                                            </div>

                                            <div class="col-md-6 mb-3">
                                                <label for="code" class="form-label">Society Code</label>
                                                <input type="text" class="form-control" id="code" name="code" required
                                                    value="<?php echo isset($editSociety) ? htmlspecialchars($editSociety['code']) : ''; ?>">
                                                <small class="form-text text-muted">Unique identifier for the society (e.g., ACCM, WCCW)</small>
                                            </div>
                                        </div>

                                        <div class="row">
                                            <div class="col-md-6 mb-3">
                                                <label for="circuit_id" class="form-label">Circuit</label>
                                                <select class="form-select" id="circuit_id" name="circuit_id" required>
                                                    <option value="">Select Circuit</option>
                                                    <?php foreach ($circuits as $circuit): ?>
                                                        <option value="<?php echo $circuit['id']; ?>" 
                                                            <?php echo (isset($editSociety) && $editSociety['circuit_id'] == $circuit['id']) ? 'selected' : ''; ?>>
                                                            <?php echo htmlspecialchars($circuit['name'] . ' (' . $circuit['diocese_name'] . ')'); ?>
                                                        </option>
                                                    <?php endforeach; ?>
                                                </select>
                                            </div>

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

                                        <div class="row">
                                            <div class="col-md-6 mb-3">
                                                <label for="contact_person" class="form-label">Contact Person</label>
                                                <input type="text" class="form-control" id="contact_person" name="contact_person"
                                                    value="<?php echo isset($editSociety) ? htmlspecialchars($editSociety['contact_person']) : ''; ?>">
                                            </div>

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

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

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

                                        <div class="row">
                                            <div class="col-md-6 mb-3">
                                                <label for="established" class="form-label">Established Date</label>
                                                <input type="date" class="form-control" id="established" name="established"
                                                    value="<?php echo isset($editSociety) ? htmlspecialchars($editSociety['established']) : ''; ?>">
                                            </div>
                                        </div>

                                        <div class="d-grid gap-2 d-md-flex justify-content-md-end">
                                            <a href="index.php" class="btn btn-secondary me-md-2">Cancel</a>
                                            <?php if (isset($_GET['edit'])): ?>
                                                <button type="submit" name="edit_society" class="btn btn-primary">Update Society</button>
                                            <?php else: ?>
                                                <button type="submit" name="create_society" class="btn btn-success">Create Society</button>
                                            <?php endif; ?>
                                        </div>
                                    </form>
                                <?php elseif (empty($circuits)): ?>
                                    <div class="alert alert-info">
                                        No circuits available. Please create a circuit first.
                                        <a href="../circuits/index.php?create=1" class="alert-link">Create a new circuit</a>.
                                    </div>
                                <?php else: ?>
                                    <div class="alert alert-warning">
                                        You do not have permission to create or edit societies.
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                    <?php else: ?>
                    <!-- Societies List Card -->
                    <div class="col-12">
                        <div class="card shadow mb-4">
                            <div class="card-header py-3">
                                <h6 class="m-0 font-weight-bold text-primary">Societies List</h6>
                            </div>
                            <div class="card-body">
                                <?php if (empty($societies)): ?>
                                    <div class="alert alert-info">
                                        No societies found.
                                        <?php if ($userRole === 'admin' || $userRole === 'diocesan_secretary' || $userRole === 'circuit_secretary'): ?>
                                            <a href="?create=1" class="alert-link">Create a new society</a>.
                                        <?php endif; ?>
                                    </div>
                                <?php else: ?>
                                    <div class="table-responsive">
                                        <table class="table table-bordered" id="societiesTable" width="100%" cellspacing="0">
                                            <thead>
                                                <tr>
                                                    <th>Name</th>
                                                    <th>Code</th>
                                                    <th>Circuit</th>
                                                    <th>Diocese</th>
                                                    <th>Location</th>
                                                    <th>Members</th>
                                                    <th>Actions</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php foreach ($societies as $society): ?>
                                                    <tr>
                                                        <td><?php echo htmlspecialchars($society['name']); ?></td>
                                                        <td><?php echo htmlspecialchars($society['code']); ?></td>
                                                        <td><?php echo htmlspecialchars($society['circuit_name']); ?></td>
                                                        <td><?php echo htmlspecialchars($society['diocese_name']); ?></td>
                                                        <td><?php echo htmlspecialchars($society['address'] ?? ''); ?></td>
                                                        <td>
                                                            <a href="../members/list.php?society=<?php echo $society['id']; ?>">
                                                                <?php 
                                                                // Get member count
                                                                $query = "SELECT COUNT(*) as count FROM members WHERE society_id = ?";
                                                                $stmt = $conn->prepare($query);
                                                                $stmt->bind_param("i", $society['id']);
                                                                $stmt->execute();
                                                                $result = $stmt->get_result();
                                                                $count = $result->fetch_assoc()['count'];
                                                                $stmt->close();
                                                                echo $count . ' members';
                                                                ?>
                                                            </a>
                                                        </td>
                                                        <td>
                                                            <div class="btn-group btn-group-sm">
                                                                <a href="view.php?id=<?php echo $society['id']; ?>" class="btn btn-info">
                                                                    <i class="fas fa-eye"></i>
                                                                </a>
                                                                <?php if ($userRole === 'admin' || 
                                                                          ($userRole === 'diocesan_secretary' && $userDioceseId == $society['diocese_id']) ||
                                                                          ($userRole === 'circuit_secretary' && $userCircuitId == $society['circuit_id'])): ?>
                                                                    <a href="?edit=<?php echo $society['id']; ?>" class="btn btn-primary">
                                                                        <i class="fas fa-edit"></i>
                                                                    </a>
                                                                    <button type="button" class="btn btn-danger" data-bs-toggle="modal" data-bs-target="#deleteModal<?php echo $society['id']; ?>">
                                                                        <i class="fas fa-trash"></i>
                                                                    </button>
                                                                <?php endif; ?>
                                                            </div>
                                                            
                                                            <!-- Delete Modal -->
                                                            <div class="modal fade" id="deleteModal<?php echo $society['id']; ?>" tabindex="-1" aria-labelledby="deleteModalLabel<?php echo $society['id']; ?>" aria-hidden="true">
                                                                <div class="modal-dialog">
                                                                    <div class="modal-content">
                                                                        <div class="modal-header">
                                                                            <h5 class="modal-title" id="deleteModalLabel<?php echo $society['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 society "<?php echo htmlspecialchars($society['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="society_id" value="<?php echo $society['id']; ?>">
                                                                                <button type="submit" name="delete_society" class="btn btn-danger">Delete</button>
                                                                            </form>
                                                                        </div>
                                                                    </div>
                                                                </div>
                                                            </div>
                                                        </td>
                                                    </tr>
                                                <?php endforeach; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                    <?php endif; ?>
                </div>

                <!-- Society Statistics Row -->
                <?php if (!isset($_GET['create']) && !isset($_GET['edit'])): ?>
                <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">Society Statistics</h6>
                            </div>
                            <div class="card-body">
                                <div class="row">
                                    <!-- 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>
                                    
                                    <!-- Members by Society Chart -->
                                    <div class="col-xl-6 col-lg-6">
                                        <div class="chart-container" style="height: 300px;">
                                            <canvas id="membersBySocietyChart"></canvas>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
                <?php endif; ?>
            </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
        $('#societiesTable').DataTable({
            order: [[0, 'asc']]
        });
        
        <?php if (!isset($_GET['create']) && !isset($_GET['edit'])): ?>
        // Initialize Charts
        const societiesByCircuitCtx = document.getElementById('societiesByCircuitChart').getContext('2d');
        const membersBySocietyCtx = document.getElementById('membersBySocietyChart').getContext('2d');
        
        <?php
        // 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'];
            }
        }
        
        // Get members by society data
        $societyLabels = [];
        $memberCounts = [];
        
        $query = "SELECT s.name, COUNT(m.id) as member_count 
                  FROM societies s 
                  LEFT JOIN members m ON s.id = m.society_id";
        
        if ($userRole === 'admin') {
            $query .= " GROUP BY s.id ORDER BY member_count DESC LIMIT 10";
        } elseif ($userRole === 'diocesan_secretary' && !empty($userDioceseId)) {
            $query .= " JOIN circuits c ON s.circuit_id = c.id 
                        WHERE c.diocese_id = $userDioceseId 
                        GROUP BY s.id ORDER BY member_count DESC LIMIT 10";
        } elseif ($userRole === 'circuit_secretary' && !empty($userCircuitId)) {
            $query .= " WHERE s.circuit_id = $userCircuitId 
                        GROUP BY s.id ORDER BY member_count DESC";
        } elseif ($userRole === 'society_secretary' && !empty($userSocietyId)) {
            $query .= " WHERE s.id = $userSocietyId GROUP BY s.id";
        }
        
        $result = $conn->query($query);
        
        if ($result && $result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $societyLabels[] = $row['name'];
                $memberCounts[] = $row['member_count'];
            }
        }
        ?>
        
        // Societies by Circuit Chart
        new Chart(societiesByCircuitCtx, {
            type: 'pie',
            data: {
                labels: <?php echo json_encode($circuitLabels); ?>,
                datasets: [{
                    data: <?php echo json_encode($societyCounts); ?>,
                    backgroundColor: [
                        '#4e73df', '#1cc88a', '#36b9cc', '#f6c23e', '#e74a3b',
                        '#5a5c69', '#858796', '#6610f2', '#fd7e14', '#20c9a6'
                    ]
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        position: 'bottom'
                    },
                    title: {
                        display: true,
                        text: 'Societies by Circuit'
                    }
                }
            }
        });
        
        // Members by Society Chart
        new Chart(membersBySocietyCtx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode($societyLabels); ?>,
                datasets: [{
                    label: 'Number of Members',
                    data: <?php echo json_encode($memberCounts); ?>,
                    backgroundColor: '#1cc88a',
                    borderColor: '#1cc88a',
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            precision: 0
                        }
                    }
                },
                plugins: {
                    title: {
                        display: true,
                        text: 'Members by Society'
                    }
                }
            }
        });
        <?php endif; ?>
    });
    </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>
