<?php
/**
 * Seed Test Data Script
 * Creates hypothetical members, households, financial transactions, and other test data
 * Run this once to populate the database with test data for development/testing
 */

require_once __DIR__ . '/../config/database.php';

echo "<!DOCTYPE html><html><head><meta charset='UTF-8'><title>Seeding Test Data</title><style>body{font-family:monospace;padding:2rem;background:#f5f5f5;}pre{background:#fff;padding:1rem;border-radius:4px;}</style></head><body><h1>Seeding Test Data</h1><pre>";

echo "Starting test data seeding...\n\n";

// Get admin user ID for transactions
$stmt = $pdo->query("SELECT id FROM users WHERE role = 'admin' LIMIT 1");
$adminUserId = $stmt->fetchColumn() ?: 1;

// Get fund IDs (create if they don't exist)
$funds = [];
$fundNames = ['General Offering', 'Building Fund', 'Missions', 'Tithes'];
foreach ($fundNames as $fundName) {
    $stmt = $pdo->prepare("SELECT id FROM funds WHERE name = ?");
    $stmt->execute([$fundName]);
    $fundId = $stmt->fetchColumn();
    if (!$fundId) {
        $isPersonal = ($fundName === 'Tithes') ? 1 : 0;
        $stmt = $pdo->prepare("INSERT INTO funds (name, description, is_personal) VALUES (?, ?, ?)");
        $stmt->execute([$fundName, $fundName . ' fund', $isPersonal]);
        $fundId = $pdo->lastInsertId();
    }
    $funds[$fundName] = $fundId;
}

// Get ministry IDs
$ministries = [];
$stmt = $pdo->query("SELECT id, name FROM ministries");
foreach ($stmt->fetchAll() as $m) {
    $ministries[$m['name']] = $m['id'];
}

// Create households
$households = [];
$householdData = [
    ['Asante Family', '123 Main Street, Accra'],
    ['Owusu Family', '456 High Street, Accra'],
    ['Mensah Family', '789 Church Road, Accra'],
    ['Amoah Family', '321 Market Square, Accra'],
    ['Boateng Family', '654 School Lane, Accra'],
    ['Darko Family', '987 Park Avenue, Accra'],
];

foreach ($householdData as $h) {
    $stmt = $pdo->prepare("INSERT INTO households (name, address) VALUES (?, ?)");
    $stmt->execute($h);
    $households[] = $pdo->lastInsertId();
}

// Test members data - Mix of males and females for voting tests
$members = [
    // Male members (8 total - enough for Presbyters elections)
    ['title' => 'Dr', 'first_name' => 'Jonathan', 'middle_name' => 'Nii', 'last_name' => 'Barnor', 'gender' => 'Male', 'date_of_birth' => '1975-03-15', 'phone' => '0244123456', 'email' => 'jonathan.barnor@example.com', 'marital_status' => 'Married', 'occupation' => 'Medical Doctor', 'status' => 'communicant', 'join_date' => '2010-01-15', 'baptism_date' => '1995-06-20', 'address' => '123 Main Street, Accra', 'household_id' => $households[0]],
    ['title' => 'Mr', 'first_name' => 'Kwame', 'middle_name' => 'Kofi', 'last_name' => 'Asante', 'gender' => 'Male', 'date_of_birth' => '1980-07-22', 'phone' => '0244234567', 'email' => 'kwame.asante@example.com', 'marital_status' => 'Married', 'occupation' => 'Engineer', 'status' => 'communicant', 'join_date' => '2012-03-10', 'baptism_date' => '1998-08-15', 'address' => '123 Main Street, Accra', 'household_id' => $households[0]],
    ['title' => 'Mr', 'first_name' => 'Emmanuel', 'middle_name' => 'Yaw', 'last_name' => 'Owusu', 'gender' => 'Male', 'date_of_birth' => '1985-11-08', 'phone' => '0244345678', 'email' => 'emmanuel.owusu@example.com', 'marital_status' => 'Single', 'occupation' => 'Teacher', 'status' => 'communicant', 'join_date' => '2015-05-20', 'baptism_date' => '2000-12-10', 'address' => '456 High Street, Accra', 'household_id' => $households[1]],
    ['title' => 'Rev', 'first_name' => 'Samuel', 'middle_name' => 'Kwaku', 'last_name' => 'Mensah', 'gender' => 'Male', 'date_of_birth' => '1970-02-14', 'phone' => '0244456789', 'email' => 'samuel.mensah@example.com', 'marital_status' => 'Married', 'occupation' => 'Pastor', 'status' => 'communicant', 'join_date' => '2008-09-01', 'baptism_date' => '1990-04-25', 'address' => '789 Church Road, Accra', 'household_id' => $households[2]],
    ['title' => 'Mr', 'first_name' => 'Daniel', 'middle_name' => 'Nana', 'last_name' => 'Amoah', 'gender' => 'Male', 'date_of_birth' => '1990-09-30', 'phone' => '0244567890', 'email' => 'daniel.amoah@example.com', 'marital_status' => 'Single', 'occupation' => 'Accountant', 'status' => 'communicant', 'join_date' => '2018-02-14', 'baptism_date' => '2005-07-18', 'address' => '321 Market Square, Accra', 'household_id' => $households[3]],
    ['title' => 'Mr', 'first_name' => 'Michael', 'middle_name' => 'Kojo', 'last_name' => 'Boateng', 'gender' => 'Male', 'date_of_birth' => '1988-04-12', 'phone' => '0244678901', 'email' => 'michael.boateng@example.com', 'marital_status' => 'Married', 'occupation' => 'Businessman', 'status' => 'communicant', 'join_date' => '2016-07-08', 'baptism_date' => '2003-11-22', 'address' => '654 School Lane, Accra', 'household_id' => $households[4]],
    ['title' => 'Mr', 'first_name' => 'David', 'middle_name' => 'Akwasi', 'last_name' => 'Darko', 'gender' => 'Male', 'date_of_birth' => '1992-12-05', 'phone' => '0244789012', 'email' => 'david.darko@example.com', 'marital_status' => 'Single', 'occupation' => 'Software Developer', 'status' => 'communicant', 'join_date' => '2019-01-10', 'baptism_date' => '2008-03-14', 'address' => '987 Park Avenue, Accra', 'household_id' => $households[5]],
    ['title' => 'Mr', 'first_name' => 'Joseph', 'middle_name' => 'Fiifi', 'last_name' => 'Adjei', 'gender' => 'Male', 'date_of_birth' => '1983-06-18', 'phone' => '0244890123', 'email' => 'joseph.adjei@example.com', 'marital_status' => 'Married', 'occupation' => 'Lawyer', 'status' => 'communicant', 'join_date' => '2014-11-25', 'baptism_date' => '1999-09-30', 'address' => '111 Business District, Accra', 'household_id' => null],
    
    // Female members (12 total - enough for Presbyters elections)
    ['title' => 'Mrs', 'first_name' => 'Grace', 'middle_name' => 'Ama', 'last_name' => 'Asante', 'gender' => 'Female', 'date_of_birth' => '1982-05-20', 'phone' => '0244901234', 'email' => 'grace.asante@example.com', 'marital_status' => 'Married', 'occupation' => 'Nurse', 'status' => 'communicant', 'join_date' => '2011-04-12', 'baptism_date' => '1997-10-05', 'address' => '123 Main Street, Accra', 'household_id' => $households[0]],
    ['title' => 'Mrs', 'first_name' => 'Mary', 'middle_name' => 'Akosua', 'last_name' => 'Owusu', 'gender' => 'Female', 'date_of_birth' => '1987-08-14', 'phone' => '0244012345', 'email' => 'mary.owusu@example.com', 'marital_status' => 'Married', 'occupation' => 'Teacher', 'status' => 'communicant', 'join_date' => '2013-06-18', 'baptism_date' => '2002-01-20', 'address' => '456 High Street, Accra', 'household_id' => $households[1]],
    ['title' => 'Miss', 'first_name' => 'Esther', 'middle_name' => 'Efua', 'last_name' => 'Mensah', 'gender' => 'Female', 'date_of_birth' => '1995-01-25', 'phone' => '0244123456', 'email' => 'esther.mensah@example.com', 'marital_status' => 'Single', 'occupation' => 'Student', 'status' => 'communicant', 'join_date' => '2017-08-30', 'baptism_date' => '2010-05-15', 'address' => '789 Church Road, Accra', 'household_id' => $households[2]],
    ['title' => 'Mrs', 'first_name' => 'Patience', 'middle_name' => 'Abena', 'last_name' => 'Amoah', 'gender' => 'Female', 'date_of_birth' => '1978-10-03', 'phone' => '0244234567', 'email' => 'patience.amoah@example.com', 'marital_status' => 'Married', 'occupation' => 'Businesswoman', 'status' => 'communicant', 'join_date' => '2009-12-05', 'baptism_date' => '1994-07-22', 'address' => '321 Market Square, Accra', 'household_id' => $households[3]],
    ['title' => 'Mrs', 'first_name' => 'Joyce', 'middle_name' => 'Adwoa', 'last_name' => 'Boateng', 'gender' => 'Female', 'date_of_birth' => '1984-03-17', 'phone' => '0244345678', 'email' => 'joyce.boateng@example.com', 'marital_status' => 'Married', 'occupation' => 'Accountant', 'status' => 'communicant', 'join_date' => '2012-09-14', 'baptism_date' => '1999-12-08', 'address' => '654 School Lane, Accra', 'household_id' => $households[4]],
    ['title' => 'Miss', 'first_name' => 'Ruth', 'middle_name' => 'Maame', 'last_name' => 'Darko', 'gender' => 'Female', 'date_of_birth' => '1993-07-22', 'phone' => '0244456789', 'email' => 'ruth.darko@example.com', 'marital_status' => 'Single', 'occupation' => 'Pharmacist', 'status' => 'communicant', 'join_date' => '2018-03-20', 'baptism_date' => '2006-11-12', 'address' => '987 Park Avenue, Accra', 'household_id' => $households[5]],
    ['title' => 'Mrs', 'first_name' => 'Comfort', 'middle_name' => 'Yaa', 'last_name' => 'Adjei', 'gender' => 'Female', 'date_of_birth' => '1981-11-28', 'phone' => '0244567890', 'email' => 'comfort.adjei@example.com', 'marital_status' => 'Married', 'occupation' => 'Banker', 'status' => 'communicant', 'join_date' => '2010-07-10', 'baptism_date' => '1996-04-18', 'address' => '111 Business District, Accra', 'household_id' => null],
    ['title' => 'Miss', 'first_name' => 'Priscilla', 'middle_name' => 'Akua', 'last_name' => 'Osei', 'gender' => 'Female', 'date_of_birth' => '1991-02-09', 'phone' => '0244678901', 'email' => 'priscilla.osei@example.com', 'marital_status' => 'Single', 'occupation' => 'Marketing Manager', 'status' => 'communicant', 'join_date' => '2016-04-25', 'baptism_date' => '2004-08-30', 'address' => '222 Commercial Street, Accra', 'household_id' => null],
    ['title' => 'Mrs', 'first_name' => 'Hannah', 'middle_name' => 'Esi', 'last_name' => 'Kwarteng', 'gender' => 'Female', 'date_of_birth' => '1976-09-16', 'phone' => '0244789012', 'email' => 'hannah.kwarteng@example.com', 'marital_status' => 'Married', 'occupation' => 'Social Worker', 'status' => 'communicant', 'join_date' => '2007-10-08', 'baptism_date' => '1992-06-14', 'address' => '333 Residential Area, Accra', 'household_id' => null],
    ['title' => 'Miss', 'first_name' => 'Deborah', 'middle_name' => 'Ama', 'last_name' => 'Sarpong', 'gender' => 'Female', 'date_of_birth' => '1989-04-30', 'phone' => '0244890123', 'email' => 'deborah.sarpong@example.com', 'marital_status' => 'Single', 'occupation' => 'Engineer', 'status' => 'communicant', 'join_date' => '2015-11-12', 'baptism_date' => '2001-09-25', 'address' => '444 Tech Hub, Accra', 'household_id' => null],
    ['title' => 'Mrs', 'first_name' => 'Sarah', 'middle_name' => 'Abena', 'last_name' => 'Gyasi', 'gender' => 'Female', 'date_of_birth' => '1980-12-07', 'phone' => '0244901234', 'email' => 'sarah.gyasi@example.com', 'marital_status' => 'Married', 'occupation' => 'Doctor', 'status' => 'communicant', 'join_date' => '2011-08-22', 'baptism_date' => '1998-02-11', 'address' => '555 Medical Center, Accra', 'household_id' => null],
    ['title' => 'Miss', 'first_name' => 'Rebecca', 'middle_name' => 'Efua', 'last_name' => 'Tetteh', 'gender' => 'Female', 'date_of_birth' => '1994-06-19', 'phone' => '0244012345', 'email' => 'rebecca.tetteh@example.com', 'marital_status' => 'Single', 'occupation' => 'Graphic Designer', 'status' => 'communicant', 'join_date' => '2019-05-15', 'baptism_date' => '2007-10-08', 'address' => '666 Creative Avenue, Accra', 'household_id' => null],
];

$memberIds = [];
$memberMinistries = [
    'Church Choir' => [0, 1, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19],
    'Singing Band' => [0, 2, 9, 11, 13, 15],
    'Praise Showers' => [1, 3, 10, 12, 14, 16],
    'Children\'s Ministry' => [4, 5, 11, 13, 15],
    'Youth Ministry' => [5, 6, 7, 12, 13, 14, 15, 16, 17, 18, 19],
    'Ushering' => [0, 1, 2, 3, 9, 10, 11, 12],
];

// Insert members
$stmt = $pdo->prepare("INSERT INTO members (title, first_name, middle_name, last_name, gender, date_of_birth, phone, email, marital_status, occupation, status, join_date, baptism_date, address, household_id, emergency_contact, emergency_phone) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

foreach ($members as $idx => $member) {
    $emergencyContact = $member['marital_status'] === 'Married' ? 'Spouse' : 'Parent';
    $emergencyPhone = '0244' . str_pad(rand(100000, 999999), 6, '0', STR_PAD_LEFT);
    
    $stmt->execute([
        $member['title'],
        $member['first_name'],
        $member['middle_name'],
        $member['last_name'],
        $member['gender'],
        $member['date_of_birth'],
        $member['phone'],
        $member['email'],
        $member['marital_status'],
        $member['occupation'],
        $member['status'],
        $member['join_date'],
        $member['baptism_date'],
        $member['address'],
        $member['household_id'],
        $emergencyContact,
        $emergencyPhone
    ]);
    $memberIds[] = $pdo->lastInsertId();
    echo "Created member: {$member['title']} {$member['first_name']} {$member['last_name']}\n";
}

// Assign members to ministries
$stmt = $pdo->prepare("INSERT INTO member_ministries (member_id, ministry_id) VALUES (?, ?)");
foreach ($memberMinistries as $ministryName => $memberIndices) {
    if (!isset($ministries[$ministryName])) continue;
    $ministryId = $ministries[$ministryName];
    foreach ($memberIndices as $idx) {
        if (isset($memberIds[$idx])) {
            try {
                $stmt->execute([$memberIds[$idx], $ministryId]);
            } catch (PDOException $e) {
                // Ignore duplicates
            }
        }
    }
}

// Create financial transactions (offerings and tithes)
$transactionStmt = $pdo->prepare("INSERT INTO transactions (fund_id, member_id, amount, transaction_date, note, created_by) VALUES (?, ?, ?, ?, ?, ?)");

// Generate transactions for the past 12 months
$today = new DateTime();
for ($month = 0; $month < 12; $month++) {
    $date = clone $today;
    $date->modify("-$month months");
    
    // General offerings (Sundays - 4 per month)
    for ($sunday = 1; $sunday <= 4; $sunday++) {
        $sundayDate = clone $date;
        $sundayDate->modify("first day of this month");
        $sundayDate->modify("+" . ($sunday * 7 - 7) . " days");
        if ($sundayDate > $today) continue;
        
        // Random members contribute
        $contributors = array_rand($memberIds, min(rand(5, 15), count($memberIds)));
        if (!is_array($contributors)) $contributors = [$contributors];
        
        foreach ($contributors as $idx) {
            $amount = rand(10, 200) + (rand(0, 99) / 100); // 10.00 to 200.99
            $transactionStmt->execute([
                $funds['General Offering'],
                $memberIds[$idx],
                $amount,
                $sundayDate->format('Y-m-d'),
                'Sunday offering',
                $adminUserId
            ]);
        }
    }
    
    // Tithes (personal fund - monthly per member)
    foreach ($memberIds as $memberId) {
        if (rand(1, 10) <= 7) { // 70% of members pay tithes
            $amount = rand(50, 500) + (rand(0, 99) / 100);
            $titheDate = clone $date;
            $titheDate->modify("first day of this month");
            $titheDate->modify("+" . rand(1, 15) . " days");
            if ($titheDate <= $today) {
                $transactionStmt->execute([
                    $funds['Tithes'],
                    $memberId,
                    $amount,
                    $titheDate->format('Y-m-d'),
                    'Monthly tithe',
                    $adminUserId
                ]);
            }
        }
    }
    
    // Building fund contributions (occasional)
    if (rand(1, 4) === 1) {
        $contributors = array_rand($memberIds, min(rand(3, 8), count($memberIds)));
        if (!is_array($contributors)) $contributors = [$contributors];
        
        foreach ($contributors as $idx) {
            $amount = rand(100, 1000) + (rand(0, 99) / 100);
            $bfDate = clone $date;
            $bfDate->modify("first day of this month");
            $bfDate->modify("+" . rand(1, 28) . " days");
            if ($bfDate <= $today) {
                $transactionStmt->execute([
                    $funds['Building Fund'],
                    $memberIds[$idx],
                    $amount,
                    $bfDate->format('Y-m-d'),
                    'Building fund contribution',
                    $adminUserId
                ]);
            }
        }
    }
    
    // Missions contributions (occasional)
    if (rand(1, 3) === 1) {
        $contributors = array_rand($memberIds, min(rand(2, 6), count($memberIds)));
        if (!is_array($contributors)) $contributors = [$contributors];
        
        foreach ($contributors as $idx) {
            $amount = rand(20, 300) + (rand(0, 99) / 100);
            $missionDate = clone $date;
            $missionDate->modify("first day of this month");
            $missionDate->modify("+" . rand(1, 28) . " days");
            if ($missionDate <= $today) {
                $transactionStmt->execute([
                    $funds['Missions'],
                    $memberIds[$idx],
                    $amount,
                    $missionDate->format('Y-m-d'),
                    'Missions support',
                    $adminUserId
                ]);
            }
        }
    }
}

echo "\nCreated financial transactions for the past 12 months\n";

// Update household head_member_id (first member in each household)
foreach ($households as $idx => $householdId) {
    if (isset($memberIds[$idx])) {
        $pdo->prepare("UPDATE households SET head_member_id = ? WHERE id = ?")
            ->execute([$memberIds[$idx], $householdId]);
    }
}

echo "\nTest data seeding completed successfully!\n";
echo "Created:\n";
echo "- " . count($households) . " households\n";
echo "- " . count($members) . " members (8 male, 12 female)\n";
echo "- Multiple financial transactions across 12 months\n";
echo "- Ministry assignments\n";
echo "\nYou can now test the voting system and other features!\n";

echo "</pre><p><a href='/nazareth/members'>View Members</a> | <a href='/nazareth/finance'>View Finance</a> | <a href='/nazareth/voting'>View Voting</a></p></body></html>";
