<?php
/**
 * Organization Structure Setup Script
 * GHAMECC Choir Management Platform
 * 
 * This script creates initial hierarchical structure (Connexion > Diocese > Circuit > Society)
 */

// Include database connection
require_once 'config/database.php';

// Start with a clean output buffer
ob_start();
echo "=== GHAMECC Organization Structure Setup ===\n\n";

// Function to check if a table has records
function tableHasRecords($tableName) {
    global $conn;
    $result = $conn->query("SELECT COUNT(*) as count FROM $tableName");
    $row = $result->fetch_assoc();
    return $row['count'] > 0;
}

// Create Connexion
function createConnexion($name, $description) {
    global $conn;
    
    // Check if connexion already exists
    $stmt = $conn->prepare("SELECT id FROM connexion WHERE name = ?");
    $stmt->bind_param("s", $name);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($result->num_rows > 0) {
        echo "Connexion '$name' already exists.\n";
        $row = $result->fetch_assoc();
        $stmt->close();
        return $row['id'];
    }
    
    // Create the connexion
    $stmt = $conn->prepare("INSERT INTO connexion (name, description) VALUES (?, ?)");
    $stmt->bind_param("ss", $name, $description);
    $stmt->execute();
    $connexionId = $stmt->insert_id;
    $stmt->close();
    
    echo "Created connexion: $name\n";
    return $connexionId;
}

// Create Diocese
function createDiocese($name, $connexionId, $description, $address, $contactPerson, $contactEmail, $contactPhone) {
    global $conn;
    
    // Check if diocese already exists
    $stmt = $conn->prepare("SELECT id FROM dioceses WHERE name = ? AND connexion_id = ?");
    $stmt->bind_param("si", $name, $connexionId);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($result->num_rows > 0) {
        echo "Diocese '$name' already exists.\n";
        $row = $result->fetch_assoc();
        $stmt->close();
        return $row['id'];
    }
    
    // Create the diocese
    $stmt = $conn->prepare("INSERT INTO dioceses (name, connexion_id, description, address, contact_person, contact_email, contact_phone) 
                           VALUES (?, ?, ?, ?, ?, ?, ?)");
    $stmt->bind_param("sisssss", $name, $connexionId, $description, $address, $contactPerson, $contactEmail, $contactPhone);
    $stmt->execute();
    $dioceseId = $stmt->insert_id;
    $stmt->close();
    
    echo "Created diocese: $name\n";
    return $dioceseId;
}

// Create Circuit
function createCircuit($name, $dioceseId, $description, $address, $contactPerson, $contactEmail, $contactPhone) {
    global $conn;
    
    // Check if circuit already exists
    $stmt = $conn->prepare("SELECT id FROM circuits WHERE name = ? AND diocese_id = ?");
    $stmt->bind_param("si", $name, $dioceseId);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($result->num_rows > 0) {
        echo "Circuit '$name' already exists.\n";
        $row = $result->fetch_assoc();
        $stmt->close();
        return $row['id'];
    }
    
    // Create the circuit
    $stmt = $conn->prepare("INSERT INTO circuits (name, diocese_id, description, address, contact_person, contact_email, contact_phone) 
                           VALUES (?, ?, ?, ?, ?, ?, ?)");
    $stmt->bind_param("sisssss", $name, $dioceseId, $description, $address, $contactPerson, $contactEmail, $contactPhone);
    $stmt->execute();
    $circuitId = $stmt->insert_id;
    $stmt->close();
    
    echo "Created circuit: $name\n";
    return $circuitId;
}

// Create Society
function createSociety($name, $circuitId, $description, $address, $contactPerson, $contactEmail, $contactPhone) {
    global $conn;
    
    // Check if society already exists
    $stmt = $conn->prepare("SELECT id FROM societies WHERE name = ? AND circuit_id = ?");
    $stmt->bind_param("si", $name, $circuitId);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($result->num_rows > 0) {
        echo "Society '$name' already exists.\n";
        $row = $result->fetch_assoc();
        $stmt->close();
        return $row['id'];
    }
    
    // Create the society
    $stmt = $conn->prepare("INSERT INTO societies (name, circuit_id, description, address, contact_person, contact_email, contact_phone) 
                           VALUES (?, ?, ?, ?, ?, ?, ?)");
    $stmt->bind_param("sisssss", $name, $circuitId, $description, $address, $contactPerson, $contactEmail, $contactPhone);
    $stmt->execute();
    $societyId = $stmt->insert_id;
    $stmt->close();
    
    echo "Created society: $name\n";
    return $societyId;
}

// Create event types
function createEventTypes() {
    global $conn;
    
    // Define event types
    $eventTypes = [
        ['Sunday Service', 'Sunday Service', 'Regular Sunday church service'],
        ['Rehearsal', 'Rehearsal', 'Regular choir rehearsal session'],
        ['Special Service', 'Special Service', 'Special church service like Easter, Christmas, etc.'],
        ['Meeting', 'Meeting', 'Administrative or planning meeting'],
        ['Workshop', 'Other', 'Training or skill development workshop'],
        ['Concert', 'Other', 'Public performance or concert']
    ];
    
    // Insert event types
    $stmt = $conn->prepare("INSERT INTO event_types (name, category, description) VALUES (?, ?, ?)");
    foreach ($eventTypes as $eventType) {
        // Check if event type already exists
        $checkStmt = $conn->prepare("SELECT id FROM event_types WHERE name = ?");
        $checkStmt->bind_param("s", $eventType[0]);
        $checkStmt->execute();
        $result = $checkStmt->get_result();
        
        if ($result->num_rows == 0) {
            $stmt->bind_param("sss", $eventType[0], $eventType[1], $eventType[2]);
            $stmt->execute();
            echo "Created event type: {$eventType[0]}\n";
        } else {
            echo "Event type '{$eventType[0]}' already exists.\n";
        }
        $checkStmt->close();
    }
    $stmt->close();
}

// Create financial categories
function createFinancialCategories() {
    global $conn;
    
    // Define financial categories
    $categories = [
        ['Monthly Dues', 'Dues', 'Regular monthly membership dues'],
        ['Annual Dues', 'Dues', 'Annual membership dues'],
        ['Welfare Contribution', 'Welfare', 'Contributions to the welfare fund'],
        ['Special Project', 'Contribution', 'Contributions for special projects'],
        ['Equipment Fund', 'Contribution', 'Contributions for equipment purchases'],
        ['Uniform Fund', 'Contribution', 'Contributions for choir uniforms'],
        ['General Donation', 'Donation', 'General donations to the choir'],
        ['Special Donation', 'Donation', 'Special purpose donations']
    ];
    
    // Insert financial categories
    $stmt = $conn->prepare("INSERT INTO financial_categories (name, type, description) VALUES (?, ?, ?)");
    foreach ($categories as $category) {
        // Check if category already exists
        $checkStmt = $conn->prepare("SELECT id FROM financial_categories WHERE name = ?");
        $checkStmt->bind_param("s", $category[0]);
        $checkStmt->execute();
        $result = $checkStmt->get_result();
        
        if ($result->num_rows == 0) {
            $stmt->bind_param("sss", $category[0], $category[1], $category[2]);
            $stmt->execute();
            echo "Created financial category: {$category[0]}\n";
        } else {
            echo "Financial category '{$category[0]}' already exists.\n";
        }
        $checkStmt->close();
    }
    $stmt->close();
}

// Create report types
function createReportTypes() {
    global $conn;
    
    // Define report types
    $reportTypes = [
        ['Monthly Report', 'Secretariate', 'monthly_report.php', 'Standard monthly activity report'],
        ['Quarterly Report', 'Secretariate', 'quarterly_report.php', 'Quarterly summary report'],
        ['Annual Report', 'Secretariate', 'annual_report.php', 'Annual comprehensive report'],
        ['Financial Statement', 'Financial', 'financial_statement.php', 'Financial statement report'],
        ['Attendance Report', 'Music Directorate', 'attendance_report.php', 'Member attendance report'],
        ['Event Report', 'Music Directorate', 'event_report.php', 'Event summary report']
    ];
    
    // Insert report types
    $stmt = $conn->prepare("INSERT INTO report_types (name, category, template_file, description) VALUES (?, ?, ?, ?)");
    foreach ($reportTypes as $reportType) {
        // Check if report type already exists
        $checkStmt = $conn->prepare("SELECT id FROM report_types WHERE name = ?");
        $checkStmt->bind_param("s", $reportType[0]);
        $checkStmt->execute();
        $result = $checkStmt->get_result();
        
        if ($result->num_rows == 0) {
            $stmt->bind_param("ssss", $reportType[0], $reportType[1], $reportType[2], $reportType[3]);
            $stmt->execute();
            echo "Created report type: {$reportType[0]}\n";
        } else {
            echo "Report type '{$reportType[0]}' already exists.\n";
        }
        $checkStmt->close();
    }
    $stmt->close();
}

// Setup organization structure
if (!tableHasRecords('connexion')) {
    echo "Setting up organization structure...\n\n";
    
    // Create Connexion
    $connexionId = createConnexion('Ghana Methodist Church', 'The Methodist Church Ghana');
    
    // Create Dioceses
    $accraId = createDiocese('Accra Diocese', $connexionId, 'Accra Diocese of the Methodist Church Ghana', 
                           'Accra, Ghana', 'Bishop John Smith', 'bishop.accra@methodist.org', '+233 20 1234567');
    
    $kumasiId = createDiocese('Kumasi Diocese', $connexionId, 'Kumasi Diocese of the Methodist Church Ghana', 
                            'Kumasi, Ghana', 'Bishop James Brown', 'bishop.kumasi@methodist.org', '+233 20 7654321');
    
    // Create Circuits for Accra Diocese
    $accraNorthId = createCircuit('Accra North Circuit', $accraId, 'Northern Circuit of Accra Diocese', 
                                'North Accra, Ghana', 'Rev. Michael Addo', 'rev.addo@methodist.org', '+233 24 1112233');
    
    $accraCentralId = createCircuit('Accra Central Circuit', $accraId, 'Central Circuit of Accra Diocese', 
                                  'Central Accra, Ghana', 'Rev. Sarah Mensah', 'rev.mensah@methodist.org', '+233 24 4455667');
    
    // Create Circuits for Kumasi Diocese
    $kumasiCentralId = createCircuit('Kumasi Central Circuit', $kumasiId, 'Central Circuit of Kumasi Diocese', 
                                   'Central Kumasi, Ghana', 'Rev. Daniel Owusu', 'rev.owusu@methodist.org', '+233 24 8899001');
    
    // Create Societies for Accra North Circuit
    $madinaId = createSociety('Madina Society', $accraNorthId, 'Madina Methodist Church Choir', 
                            'Madina, Accra', 'Choir President', 'madina.choir@methodist.org', '+233 26 1122334');
    
    $adentaId = createSociety('Adenta Society', $accraNorthId, 'Adenta Methodist Church Choir', 
                            'Adenta, Accra', 'Choir President', 'adenta.choir@methodist.org', '+233 26 5566778');
    
    // Create Societies for Accra Central Circuit
    $circleId = createSociety('Circle Society', $accraCentralId, 'Circle Methodist Church Choir', 
                            'Circle, Accra', 'Choir President', 'circle.choir@methodist.org', '+233 26 9900112');
    
    $osuId = createSociety('Osu Society', $accraCentralId, 'Osu Methodist Church Choir', 
                         'Osu, Accra', 'Choir President', 'osu.choir@methodist.org', '+233 26 3344556');
    
    // Create Societies for Kumasi Central Circuit
    $asokwaId = createSociety('Asokwa Society', $kumasiCentralId, 'Asokwa Methodist Church Choir', 
                            'Asokwa, Kumasi', 'Choir President', 'asokwa.choir@methodist.org', '+233 26 7788990');
    
    $amakomId = createSociety('Amakom Society', $kumasiCentralId, 'Amakom Methodist Church Choir', 
                            'Amakom, Kumasi', 'Choir President', 'amakom.choir@methodist.org', '+233 26 1122334');
    
    echo "Organization structure created successfully.\n\n";
} else {
    echo "Organization structure already exists. Skipping creation.\n\n";
}

// Create event types
if (!tableHasRecords('event_types')) {
    echo "Creating event types...\n";
    createEventTypes();
    echo "Event types created successfully.\n\n";
} else {
    echo "Event types already exist. Skipping creation.\n\n";
}

// Create financial categories
if (!tableHasRecords('financial_categories')) {
    echo "Creating financial categories...\n";
    createFinancialCategories();
    echo "Financial categories created successfully.\n\n";
} else {
    echo "Financial categories already exist. Skipping creation.\n\n";
}

// Create report types
if (!tableHasRecords('report_types')) {
    echo "Creating report types...\n";
    createReportTypes();
    echo "Report types created successfully.\n\n";
} else {
    echo "Report types already exist. Skipping creation.\n\n";
}

echo "Organization setup completed successfully!\n";

// Output the buffer
$output = ob_get_clean();
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>GHAMECC Organization Setup</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <style>
        pre {
            background-color: #f8f9fa;
            padding: 15px;
            border-radius: 5px;
            white-space: pre-wrap;
        }
    </style>
</head>
<body>
    <div class="container my-5">
        <div class="row">
            <div class="col-12">
                <h1>GHAMECC Organization Setup</h1>
                <div class="card">
                    <div class="card-header">
                        Setup Results
                    </div>
                    <div class="card-body">
                        <pre><?php echo htmlspecialchars($output); ?></pre>
                        <div class="d-flex gap-2">
                            <a href="setup_users.php" class="btn btn-secondary">Setup Users</a>
                            <a href="login.php" class="btn btn-primary">Go to Login Page</a>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</body>
</html>
