<?php
/**
 * One-time script: assess events DB and add sample events.
 * Run once in browser: /nazareth/install/seed-events.php
 * DELETE this file after use.
 */

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

header('Content-Type: text/html; charset=utf-8');
echo "<!DOCTYPE html><html><head><title>Seed Events</title><style>body{font-family:sans-serif;max-width:600px;margin:2rem auto;padding:1rem;} .ok{color:green;} .warn{color:orange;} pre{background:#f1f5f9;padding:0.75rem;border-radius:4px;} ul{margin:0.5rem 0;}</style></head><body>";
echo "<h1>Events DB assessment &amp; seed</h1>";

$report = [];
$report[] = ['step' => 'Connect', 'status' => 'ok', 'msg' => 'Connected to database'];

// 1) Check events table
try {
    $pdo->query("SELECT 1 FROM events LIMIT 1");
    $count = $pdo->query("SELECT COUNT(*) FROM events")->fetchColumn();
    $report[] = ['step' => 'Table events', 'status' => 'ok', 'msg' => "Exists. Current row count: $count"];
} catch (PDOException $e) {
    $report[] = ['step' => 'Table events', 'status' => 'fail', 'msg' => $e->getMessage()];
    echo "<p><strong>Error:</strong> events table missing. Run <a href='" . BASE_PATH . "/install/install.php'>install.php</a> first.</p>";
    echo "</body></html>";
    exit;
}

// 2) Ensure event_types exists and is seeded
try {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS event_types (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            color VARCHAR(7) NULL,
            display_order INT DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ");
    $report[] = ['step' => 'Table event_types', 'status' => 'ok', 'msg' => 'Exists or created'];

    try {
        $pdo->query("SELECT event_type_id FROM events LIMIT 1");
        $hasCol = true;
    } catch (PDOException $e) {
        $hasCol = false;
    }
    if (!$hasCol) {
        $pdo->exec("ALTER TABLE events ADD COLUMN event_type_id INT NULL AFTER location");
        try {
            $pdo->exec("ALTER TABLE events ADD CONSTRAINT fk_events_type FOREIGN KEY (event_type_id) REFERENCES event_types(id) ON DELETE SET NULL");
        } catch (PDOException $fk) { /* ignore */ }
        $report[] = ['step' => 'Column event_type_id', 'status' => 'ok', 'msg' => 'Added to events'];
    } else {
        $report[] = ['step' => 'Column event_type_id', 'status' => 'ok', 'msg' => 'Already present'];
    }

    $n = $pdo->query("SELECT COUNT(*) FROM event_types")->fetchColumn();
    if ($n == 0) {
        $defaultTypes = [
            ['Worship Service', '#2563eb', 1],
            ['Bible Study', '#059669', 2],
            ['Meeting', '#7c3aed', 3],
            ['Outreach', '#ea580c', 4],
            ['Conference', '#be185d', 5],
            ['Funeral', '#475569', 6],
            ['Wedding', '#db2777', 7],
            ['Youth', '#0d9488', 8],
            ['Children', '#65a30d', 9],
            ['Prayer', '#4f46e5', 10],
            ['Other', '#64748b', 11],
        ];
        $ins = $pdo->prepare('INSERT INTO event_types (name, color, display_order) VALUES (?, ?, ?)');
        foreach ($defaultTypes as $t) {
            $ins->execute([$t[0], $t[1], $t[2]]);
        }
        $report[] = ['step' => 'Seed event_types', 'status' => 'ok', 'msg' => count($defaultTypes) . ' types inserted'];
    } else {
        $report[] = ['step' => 'Seed event_types', 'status' => 'ok', 'msg' => "$n types already present"];
    }
} catch (PDOException $e) {
    $report[] = ['step' => 'event_types setup', 'status' => 'fail', 'msg' => $e->getMessage()];
}

// 3) Get first event_type_id for new events (optional)
$eventTypeId = null;
try {
    $row = $pdo->query("SELECT id FROM event_types ORDER BY display_order, id LIMIT 1")->fetch();
    if ($row) {
        $eventTypeId = (int) $row['id'];
    }
} catch (PDOException $e) {
}

// 4) Insert sample events (current month + next month) if there are few events
$existingCount = $pdo->query("SELECT COUNT(*) FROM events")->fetchColumn();
$toAdd = [];
$today = new DateTime('now');
$curMonth = $today->format('Y-m');
$nextMonth = (clone $today)->modify('first day of next month')->format('Y-m');

// Sample events: a few in current month, a few in next
$samples = [
    [$curMonth . '-01', '09:00', 'Main sanctuary', 'Worship Service'],
    [$curMonth . '-05', '18:00', 'Church hall', 'Bible Study'],
    [$curMonth . '-12', '18:30', 'Church', 'Prayer Meeting'],
    [$curMonth . '-15', '15:00', 'Youth centre', 'Youth Fellowship'],
    [$nextMonth . '-07', '09:00', 'Main sanctuary', 'Worship Service'],
    [$nextMonth . '-14', '10:00', 'Session room', 'Meeting - Session'],
];

$inserted = 0;
$cols = ['title', 'event_date', 'event_time', 'location', 'reminder_enabled', 'reminder_when', 'reminder_recipient_type'];
if ($eventTypeId !== null) {
    $cols[] = 'event_type_id';
}
$placeholders = implode(', ', array_fill(0, count($cols), '?'));

foreach ($samples as $s) {
    $date = $s[0];
    $time = $s[1];
    $location = $s[2];
    $title = $s[3];
    // Avoid duplicate titles on same date
    $exists = $pdo->prepare("SELECT 1 FROM events WHERE title = ? AND event_date = ?");
    $exists->execute([$title, $date]);
    if ($exists->fetch()) {
        continue;
    }
    $vals = [$title, $date, $time, $location, 0, 'day_before', 'all'];
    if ($eventTypeId !== null) {
        $vals[] = $eventTypeId;
    }
    try {
        $sql = 'INSERT INTO events (' . implode(', ', $cols) . ') VALUES (' . $placeholders . ')';
        $pdo->prepare($sql)->execute($vals);
        $inserted++;
    } catch (PDOException $e) {
        $report[] = ['step' => "Insert $title on $date", 'status' => 'fail', 'msg' => $e->getMessage()];
    }
}

if ($inserted > 0) {
    $report[] = ['step' => 'Sample events', 'status' => 'ok', 'msg' => "$inserted event(s) added"];
} else {
    $report[] = ['step' => 'Sample events', 'status' => 'ok', 'msg' => 'No new events added (duplicates or already present)'];
}

// Output report
echo "<ul>";
foreach ($report as $r) {
    $c = $r['status'] === 'fail' ? 'warn' : 'ok';
    echo "<li class='$c'><strong>" . htmlspecialchars($r['step']) . "</strong>: " . htmlspecialchars($r['msg']) . "</li>";
}
echo "</ul>";

$totalEvents = $pdo->query("SELECT COUNT(*) FROM events")->fetchColumn();
echo "<p><strong>Total events in DB now:</strong> $totalEvents</p>";
echo "<p><a href='" . BASE_PATH . "/events'>View Events calendar</a></p>";
echo "<p style='color:#64748b;font-size:0.9rem;'>Delete this file (install/seed-events.php) after use.</p>";
echo "</body></html>";
