<?php
/**
 * Seed sample finance data only.
 * - Creates common offering types (funds) if missing
 * - Inserts hypothetical transactions for the last 6 months
 *
 * Run once from the browser:
 *   /nazareth/install/seed-finance-sample.php
 */

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

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

echo "Starting sample finance data seeding...\n\n";

// Get an admin user ID for created_by
try {
    $stmt = $pdo->query("SELECT id FROM users WHERE role = 'admin' LIMIT 1");
    $adminUserId = (int) ($stmt->fetchColumn() ?: 1);
} catch (PDOException $e) {
    $adminUserId = 1;
}

// Ensure core funds exist
$funds = [];
$fundDefinitions = [
    ['name' => 'Tithes',               'desc' => 'Personal tithes',                 'is_personal' => 1],
    ['name' => 'General Offering',     'desc' => 'Sunday offerings',                'is_personal' => 0],
    ['name' => 'Thanksgiving Offering','desc' => 'Special thanksgiving offerings',  'is_personal' => 0],
    ['name' => 'Harvest',              'desc' => 'Annual harvest',                  'is_personal' => 0],
    ['name' => 'Building Project',     'desc' => 'Building project contributions',  'is_personal' => 0],
    ['name' => 'Missions',             'desc' => 'Missions and outreach',           'is_personal' => 0],
    // Day-of-week summary funds
    ['name' => 'Sunday',               'desc' => 'All offerings received on Sundays','is_personal' => 0],
    ['name' => 'Monday',               'desc' => 'All offerings received on Mondays','is_personal' => 0],
    ['name' => 'Tuesday',              'desc' => 'All offerings received on Tuesdays','is_personal' => 0],
    ['name' => 'Wednesday',            'desc' => 'All offerings received on Wednesdays','is_personal' => 0],
    ['name' => 'Thursday',             'desc' => 'All offerings received on Thursdays','is_personal' => 0],
    ['name' => 'Friday',               'desc' => 'All offerings received on Fridays','is_personal' => 0],
    ['name' => 'Saturday',             'desc' => 'All offerings received on Saturdays','is_personal' => 0],
];

foreach ($fundDefinitions as $def) {
    $stmt = $pdo->prepare("SELECT id FROM funds WHERE name = ?");
    $stmt->execute([$def['name']]);
    $id = $stmt->fetchColumn();
    if (!$id) {
        $stmtIns = $pdo->prepare("INSERT INTO funds (name, description, is_personal) VALUES (?, ?, ?)");
        $stmtIns->execute([$def['name'], $def['desc'], $def['is_personal']]);
        $id = $pdo->lastInsertId();
        echo "Created fund: {$def['name']} (id {$id})\n";
    } else {
        echo "Found existing fund: {$def['name']} (id {$id})\n";
    }
    $funds[$def['name']] = (int) $id;
}

// Convenience map for weekday funds if present
$weekdayFunds = [];
foreach (['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'] as $dayName) {
    if (isset($funds[$dayName])) {
        $weekdayFunds[$dayName] = $funds[$dayName];
    }
}

if (empty($funds)) {
    echo "\nNo funds available; aborting.\n</pre></body></html>";
    exit;
}

// Get some active members to attach personal offerings to
$memberIds = [];
try {
    $stmt = $pdo->query("
        SELECT id 
        FROM members 
        WHERE (status != 'deceased' OR status IS NULL) 
          AND (date_of_death IS NULL)
        ORDER BY id
        LIMIT 40
    ");
    $memberIds = array_map('intval', array_column($stmt->fetchAll(PDO::FETCH_ASSOC), 'id'));
} catch (PDOException $e) {
    $memberIds = [];
}

echo "\nUsing " . count($memberIds) . " members for personal offerings.\n";

// Prepare transaction insert (including service column if it exists)
$hasService = false;
try {
    $pdo->query("SELECT service FROM transactions LIMIT 1");
    $hasService = true;
} catch (PDOException $e) {
    $hasService = false;
}

if ($hasService) {
    $txSql = "INSERT INTO transactions (fund_id, member_id, amount, transaction_date, service, note, created_by) 
              VALUES (?, ?, ?, ?, ?, ?, ?)";
} else {
    $txSql = "INSERT INTO transactions (fund_id, member_id, amount, transaction_date, note, created_by) 
              VALUES (?, ?, ?, ?, ?, ?)";
}
$txStmt = $pdo->prepare($txSql);

$today = new DateTime();
$monthsBack = 18; // seed data for last 18 months
$totalTx = 0;

for ($m = 0; $m < $monthsBack; $m++) {
    $monthDate = (clone $today)->modify("-{$m} months");
    $monthLabel = $monthDate->format('F Y');
    echo "\nGenerating data for {$monthLabel}...\n";

    $monthStart = (clone $monthDate)->modify('first day of this month');

    // 1. Regular Sunday general offerings (4 Sundays)
    for ($week = 0; $week < 4; $week++) {
        $sunday = (clone $monthStart)->modify("+{$week} week")->modify('sunday this week');
        if ($sunday > $today || (int)$sunday->format('m') !== (int)$monthDate->format('m')) continue;
        $serviceLabel = (rand(0, 1) === 0) ? '1st' : '2nd';

        $offeringCount = rand(18, 40);
        $dayTotal = 0;
        for ($i = 0; $i < $offeringCount; $i++) {
            $amount = rand(5, 120); // GH¢ 5 – 120
            $note = 'Sunday ' . ($serviceLabel === '1st' ? '1st' : '2nd') . ' service offering';
            if ($hasService) {
                $txStmt->execute([
                    $funds['General Offering'],
                    null,
                    $amount,
                    $sunday->format('Y-m-d'),
                    $serviceLabel,
                    $note,
                    $adminUserId,
                ]);
            } else {
                $txStmt->execute([
                    $funds['General Offering'],
                    null,
                    $amount,
                    $sunday->format('Y-m-d'),
                    $note,
                    $adminUserId,
                ]);
            }
            $totalTx++;
            $dayTotal += $amount;
        }

        // Summary entry in Sunday fund, if configured (helps drive day-of-week charts)
        if (!empty($weekdayFunds['Sunday']) && $dayTotal > 0) {
            $summaryAmount = max(50, (int) round($dayTotal * 0.15)); // about 15% of that day's giving
            if ($hasService) {
                $txStmt->execute([
                    $weekdayFunds['Sunday'],
                    null,
                    $summaryAmount,
                    $sunday->format('Y-m-d'),
                    $serviceLabel,
                    'Summary for Sunday offerings',
                    $adminUserId,
                ]);
            } else {
                $txStmt->execute([
                    $weekdayFunds['Sunday'],
                    null,
                    $summaryAmount,
                    $sunday->format('Y-m-d'),
                    'Summary for Sunday offerings',
                    $adminUserId,
                ]);
            }
            $totalTx++;
        }
    }

    // 2. Tithes (personal, spread through month)
    if (!empty($memberIds)) {
        foreach ($memberIds as $memberId) {
            if (rand(1, 10) <= 7) { // ~70% of members pay tithes that month
                $dayOffset = rand(1, 25);
                $titheDate = (clone $monthStart)->modify("+{$dayOffset} days");
                if ($titheDate > $today) continue;
                $amount = rand(80, 600);
                $serviceLabel = (rand(0, 1) === 0) ? '1st' : '2nd';
                $note = 'Monthly tithe';
                if ($hasService) {
                    $txStmt->execute([
                        $funds['Tithes'],
                        $memberId,
                        $amount,
                        $titheDate->format('Y-m-d'),
                        $serviceLabel,
                        $note,
                        $adminUserId,
                    ]);
                } else {
                    $txStmt->execute([
                        $funds['Tithes'],
                        $memberId,
                        $amount,
                        $titheDate->format('Y-m-d'),
                        $note,
                        $adminUserId,
                    ]);
                }
                $totalTx++;
            }
        }
    }

    // 3. Midweek offerings (e.g. Wednesday / Friday services)
    $midweekDays = ['Wednesday', 'Friday'];
    foreach ($midweekDays as $dayName) {
        if (rand(1, 3) === 1) { // ~33% chance per month per midweek day
            $midweekDate = (clone $monthStart)->modify($dayName . ' this week');
            if ($midweekDate > $today || (int)$midweekDate->format('m') !== (int)$monthDate->format('m')) continue;
            $serviceLabel = (rand(0, 1) === 0) ? '1st' : '2nd';
            $offeringCount = rand(8, 20);
            $dayTotalMid = 0;
            for ($i = 0; $i < $offeringCount; $i++) {
                $amount = rand(2, 60);
                $note = $dayName . ' service offering';
                if ($hasService) {
                    $txStmt->execute([
                        $funds['General Offering'],
                        null,
                        $amount,
                        $midweekDate->format('Y-m-d'),
                        $serviceLabel,
                        $note,
                        $adminUserId,
                    ]);
                } else {
                    $txStmt->execute([
                        $funds['General Offering'],
                        null,
                        $amount,
                        $midweekDate->format('Y-m-d'),
                        $note,
                        $adminUserId,
                    ]);
                }
                $totalTx++;
                $dayTotalMid += $amount;
            }

            // Summary entry in weekday fund if available
            if (isset($weekdayFunds[$dayName]) && $dayTotalMid > 0) {
                $summaryAmount = max(20, (int) round($dayTotalMid * 0.2));
                if ($hasService) {
                    $txStmt->execute([
                        $weekdayFunds[$dayName],
                        null,
                        $summaryAmount,
                        $midweekDate->format('Y-m-d'),
                        $serviceLabel,
                        'Summary for ' . $dayName . ' offerings',
                        $adminUserId,
                    ]);
                } else {
                    $txStmt->execute([
                        $weekdayFunds[$dayName],
                        null,
                        $summaryAmount,
                        $midweekDate->format('Y-m-d'),
                        'Summary for ' . $dayName . ' offerings',
                        $adminUserId,
                    ]);
                }
                $totalTx++;
            }
        }
    }

    // 4. Thanksgiving / Harvest / Building / Missions occasional events
    $specialFunds = ['Thanksgiving Offering', 'Harvest', 'Building Project', 'Missions'];
    foreach ($specialFunds as $name) {
        if (!isset($funds[$name])) continue;
        if (rand(1, 4) !== 1) continue; // ~25% chance per month per fund
        $eventDate = (clone $monthStart)->modify('+' . rand(1, 27) . ' days');
        if ($eventDate > $today) continue;
        $serviceLabel = (rand(0, 1) === 0) ? '1st' : '2nd';
        $contributors = !empty($memberIds)
            ? (array) array_rand($memberIds, min(rand(5, 20), count($memberIds)))
            : range(0, rand(10, 30));
        foreach ($contributors as $idx) {
            $memberId = !empty($memberIds) ? $memberIds[$idx] : null;
            $amount = rand(20, 300);
            $note = $name . ' contribution';
            if ($hasService) {
                $txStmt->execute([
                    $funds[$name],
                    $memberId,
                    $amount,
                    $eventDate->format('Y-m-d'),
                    $serviceLabel,
                    $note,
                    $adminUserId,
                ]);
            } else {
                $txStmt->execute([
                    $funds[$name],
                    $memberId,
                    $amount,
                    $eventDate->format('Y-m-d'),
                    $note,
                    $adminUserId,
                ]);
            }
            $totalTx++;
        }
    }
}

echo "\nInserted {$totalTx} sample transactions across {$monthsBack} months.\n";
echo "\nDone. Open Finance → Dashboard or Finance → Reports to explore the sample data.\n";

echo "</pre><p><a href=\"../finance\">Go to Finance</a></p></body></html>";

