<?php
/**
 * Preaching Plan - manage and generate preaching plan
 */

require_once __DIR__ . '/../../includes/auth.php';
requireAuth();
requireAdmin();

// Ensure preaching_plan table exists
try {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS preaching_plan (
            id INT AUTO_INCREMENT PRIMARY KEY,
            service_date DATE NOT NULL,
            service_type VARCHAR(20) DEFAULT 'first-service',
            occasion VARCHAR(200),
            preacher VARCHAR(200),
            liturgist VARCHAR(200),
            ga_bible_reader VARCHAR(200),
            twi_bible_reader VARCHAR(200),
            english_bible_reader VARCHAR(200),
            remarks VARCHAR(200),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_service_date (service_date),
            INDEX idx_service_type (service_type)
        )
    ");
    
    // Add english_bible_reader column if it doesn't exist (for existing tables)
    try {
        $pdo->exec("ALTER TABLE preaching_plan ADD COLUMN english_bible_reader VARCHAR(200) AFTER twi_bible_reader");
    } catch (PDOException $e) {
        // Column already exists, ignore
        if (strpos($e->getMessage(), 'Duplicate column name') === false) {
            error_log('Preaching plan column addition: ' . $e->getMessage());
        }
    }
    
    // Add "applies to all services" columns if they don't exist
    $applyAllColumns = [
        'preacher_applies_all' => 'AFTER preacher',
        'liturgist_applies_all' => 'AFTER liturgist',
        'ga_reader_applies_all' => 'AFTER ga_bible_reader',
        'twi_reader_applies_all' => 'AFTER twi_bible_reader',
        'english_reader_applies_all' => 'AFTER english_bible_reader'
    ];
    
    foreach ($applyAllColumns as $colName => $position) {
        try {
            $pdo->exec("ALTER TABLE preaching_plan ADD COLUMN $colName TINYINT(1) DEFAULT 0 $position");
        } catch (PDOException $e) {
            // Column already exists, ignore
            if (strpos($e->getMessage(), 'Duplicate column name') === false) {
                error_log("Preaching plan column addition ($colName): " . $e->getMessage());
            }
        }
    }
} catch (PDOException $e) {
    // Log error but don't die - table might already exist
    error_log('Preaching plan table creation: ' . $e->getMessage());
}

// Handle nested routing: /events/preaching-plan/add, /events/preaching-plan/edit/123, etc.
// When $route_action is 'preaching-plan', the actual action is in $route_id
$action = $route_action ?? 'list';
$id = isset($route_id) ? $route_id : null;

// Parse the route string to handle nested routes
$route = $_GET['route'] ?? '';
$routeParts = $route ? explode('/', trim($route, '/')) : [];

// If action is 'preaching-plan', parse the nested route
if ($action === 'preaching-plan') {
    // Find 'preaching-plan' in the route parts
    $preachingPlanIndex = array_search('preaching-plan', $routeParts);
    if ($preachingPlanIndex !== false && isset($routeParts[$preachingPlanIndex + 1])) {
        $nestedAction = $routeParts[$preachingPlanIndex + 1];
        if (in_array($nestedAction, ['add', 'edit', 'delete', 'generate', 'export', 'broadcast'])) {
            $action = $nestedAction;
            // Get ID from next part if it exists and is numeric
            if (isset($routeParts[$preachingPlanIndex + 2]) && is_numeric($routeParts[$preachingPlanIndex + 2])) {
                $id = (int)$routeParts[$preachingPlanIndex + 2];
            } else {
                $id = null;
            }
        } elseif (is_numeric($nestedAction)) {
            // Direct ID: /events/preaching-plan/123 (treat as edit)
            $action = 'edit';
            $id = (int)$nestedAction;
        } else {
            // Unknown nested action, default to list
            $action = 'list';
            $id = null;
        }
    } else {
        // No nested action, default to list
        $action = 'list';
        $id = null;
    }
} else {
    // Normal routing: convert $id to int if it's numeric
    $id = is_numeric($id) ? (int)$id : null;
}

if ($action === 'add' || ($action === 'edit' && $id)) {
    require __DIR__ . '/preaching-plan-form.php';
    return;
}

if ($action === 'delete' && $id) {
    $stmt = $pdo->prepare('DELETE FROM preaching_plan WHERE id = ?');
    $stmt->execute([$id]);
    header('Location: ' . BASE_PATH . '/events/preaching-plan?toast=' . urlencode('Entry deleted.'));
    exit;
}

if ($action === 'generate') {
    require __DIR__ . '/preaching-plan-report.php';
    return;
}

if ($action === 'broadcast') {
    require __DIR__ . '/preaching-plan-broadcast.php';
    return;
}

if ($action === 'export') {
    require __DIR__ . '/preaching-plan-export.php';
    return;
}

// List preaching plan entries - filter by year and service type
$year = isset($_GET['year']) ? (int)$_GET['year'] : date('Y');
$serviceType = $_GET['service_type'] ?? 'first-service';
$month = $_GET['month'] ?? null;

$where = "YEAR(service_date) = ? AND service_type = ?";
$params = [$year, $serviceType];

if ($month && preg_match('/^\d{4}-\d{2}$/', $month)) {
    $where .= " AND DATE_FORMAT(service_date, '%Y-%m') = ?";
    $params[] = $month;
}

try {
    $stmt = $pdo->prepare("
        SELECT * FROM preaching_plan 
        WHERE $where
        ORDER BY service_date ASC
    ");
    $stmt->execute($params);
    $entries = $stmt->fetchAll();
} catch (PDOException $e) {
    $entries = [];
    // Show error to user if table doesn't exist
    if (strpos($e->getMessage(), "doesn't exist") !== false || strpos($e->getMessage(), 'Unknown table') !== false) {
        // Table doesn't exist, try to create it
        try {
            $pdo->exec("
                CREATE TABLE IF NOT EXISTS preaching_plan (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    service_date DATE NOT NULL,
                    service_type VARCHAR(20) DEFAULT 'first-service',
                    occasion VARCHAR(200),
                    preacher VARCHAR(200),
                    liturgist VARCHAR(200),
                    ga_bible_reader VARCHAR(200),
                    twi_bible_reader VARCHAR(200),
                    english_bible_reader VARCHAR(200),
                    remarks VARCHAR(200),
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    INDEX idx_service_date (service_date),
                    INDEX idx_service_type (service_type)
                )
            ");
            // Retry query
            $stmt = $pdo->prepare("
                SELECT * FROM preaching_plan 
                WHERE $where
                ORDER BY service_date ASC
            ");
            $stmt->execute($params);
            $entries = $stmt->fetchAll();
        } catch (PDOException $e2) {
            error_log('Preaching plan table creation retry error: ' . $e2->getMessage());
        }
    } else {
        error_log('Preaching plan query error: ' . $e->getMessage());
    }
}

// Get unique months for filter
try {
    $stmt = $pdo->prepare("
        SELECT DISTINCT DATE_FORMAT(service_date, '%Y-%m') AS month
        FROM preaching_plan
        WHERE YEAR(service_date) = ? AND service_type = ?
        ORDER BY month ASC
    ");
    $stmt->execute([$year, $serviceType]);
    $availableMonths = $stmt->fetchAll(PDO::FETCH_COLUMN);
} catch (PDOException $e) {
    $availableMonths = [];
}

// Initialize totalEntries before header
$totalEntries = 0;
try {
    $checkStmt = $pdo->query("SELECT COUNT(*) as total FROM preaching_plan");
    $totalEntries = $checkStmt->fetch()['total'] ?? 0;
} catch (PDOException $e) {
    // Ignore if table doesn't exist yet
}

$pageTitle = 'Preaching Plan';
$currentPage = 'events';
require_once __DIR__ . '/../../includes/header.php';
?>

<div class="header">
    <h1>Preaching Plan</h1>
    <div class="header-actions">
        <a href="<?= BASE_PATH ?>/events/preaching-plan/add" class="btn">Add Entry</a>
        <?php if (!empty($entries) || $totalEntries > 0): ?>
        <a href="<?= BASE_PATH ?>/events/preaching-plan/generate?year=<?= $year ?>&service_type=<?= urlencode($serviceType) ?><?= $month ? '&month=' . urlencode($month) : '' ?>" class="btn">View</a>
        <a href="<?= BASE_PATH ?>/events/preaching-plan/export?year=<?= $year ?>&service_type=<?= urlencode($serviceType) ?><?= $month ? '&month=' . urlencode($month) : '' ?>" class="btn btn-secondary">Export CSV</a>
        <a href="<?= BASE_PATH ?>/events/preaching-plan/broadcast?year=<?= $year ?>&service_type=<?= urlencode($serviceType) ?><?= $month ? '&month=' . urlencode($month) : '' ?>" class="btn btn-secondary">Broadcast</a>
        <?php endif; ?>
        <a href="<?= BASE_PATH ?>/events">Back to Events</a>
        <a href="<?= BASE_PATH ?>/logout">Logout</a>
    </div>
</div>

<div class="filter-bar">
    <form method="get" style="display: flex; gap: 0.5rem; align-items: center; flex-wrap: wrap;">
        <label>Year:</label>
        <select name="year">
            <?php for ($y = date('Y') - 1; $y <= date('Y') + 1; $y++): ?>
            <option value="<?= $y ?>" <?= $year == $y ? 'selected' : '' ?>><?= $y ?></option>
            <?php endfor; ?>
        </select>
        <label>Service:</label>
        <select name="service_type">
            <option value="first-service" <?= $serviceType === 'first-service' ? 'selected' : '' ?>>First Service</option>
            <option value="forenoon" <?= $serviceType === 'forenoon' ? 'selected' : '' ?>>Forenoon</option>
            <option value="joint-service" <?= $serviceType === 'joint-service' ? 'selected' : '' ?>>Joint Service</option>
        </select>
        <label>Month:</label>
        <select name="month">
            <option value="">All Months</option>
            <?php foreach ($availableMonths as $m): ?>
            <option value="<?= htmlspecialchars($m) ?>" <?= $month === $m ? 'selected' : '' ?>><?= date('F Y', strtotime($m . '-01')) ?></option>
            <?php endforeach; ?>
        </select>
        <button type="submit" class="btn">Filter</button>
    </form>
</div>

<?php 
// Debug: Check if table exists and count all entries (already initialized above)
try {
    if ($totalEntries === 0) {
        $checkStmt = $pdo->query("SELECT COUNT(*) as total FROM preaching_plan");
        $totalEntries = $checkStmt->fetch()['total'] ?? 0;
    }
} catch (PDOException $e) {
    // Table doesn't exist or error
}

if (empty($entries)): 
    if ($totalEntries > 0): ?>
        <div class="alert" style="margin-bottom: 1rem; padding: 0.75rem; background-color: #fff3cd; border: 1px solid #ffc107; color: #856404; border-radius: 4px;">
            <strong>Note:</strong> No entries found for the selected filters (Year: <?= $year ?>, Service: <?= htmlspecialchars($serviceType) ?><?= $month ? ', Month: ' . htmlspecialchars($month) : '' ?>). 
            <?php if ($totalEntries > 0): ?>
                There are <?= $totalEntries ?> total entries in the database. Try adjusting your filters.
            <?php endif; ?>
        </div>
    <?php else: ?>
        <p class="text-muted">No preaching plan entries found. <a href="<?= BASE_PATH ?>/events/preaching-plan/add">Add an entry</a>.</p>
    <?php endif; ?>
<?php else: ?>
<div class="table-wrap">
    <table>
        <thead>
            <tr>
                <th>Date</th>
                <th>Occasion</th>
                <th>Preacher</th>
                <th>Liturgist</th>
                <?php if ($serviceType !== 'first-service'): ?>
                <th>Ga Bible Reader</th>
                <?php endif; ?>
                <th>Twi Bible Reader</th>
                <th>English Bible Reader</th>
                <th>Remarks</th>
                <th>Actions</th>
            </tr>
        </thead>
        <tbody>
            <?php 
            $currentMonth = null;
            foreach ($entries as $entry): 
                $entryMonth = date('F Y', strtotime($entry['service_date']));
                if ($currentMonth !== $entryMonth):
                    $currentMonth = $entryMonth;
            ?>
            <tr style="background-color: #f5f5f5; font-weight: 600;">
                <td colspan="<?= $serviceType === 'first-service' ? '8' : '9' ?>" style="padding: 0.75rem;"><?= strtoupper($currentMonth) ?></td>
            </tr>
            <?php endif; ?>
            <?php
            // Helper function for title case
            function toTitleCase($str) {
                if (empty($str)) return '-';
                return ucwords(strtolower($str));
            }
            ?>
            <tr>
                <td><?= date('jS', strtotime($entry['service_date'])) ?></td>
                <td><?= htmlspecialchars(toTitleCase($entry['occasion'])) ?></td>
                <td><?= htmlspecialchars($entry['preacher'] ?: '-') ?></td>
                <td><?= htmlspecialchars($entry['liturgist'] ?: '-') ?></td>
                <?php if ($serviceType !== 'first-service'): ?>
                <td><?= htmlspecialchars($entry['ga_bible_reader'] ?: '-') ?></td>
                <?php endif; ?>
                <td><?= htmlspecialchars($entry['twi_bible_reader'] ?: '-') ?></td>
                <td><?= htmlspecialchars($entry['english_bible_reader'] ?: '-') ?></td>
                <td><?= htmlspecialchars(toTitleCase($entry['remarks'])) ?></td>
                <td class="actions">
                    <a href="<?= BASE_PATH ?>/events/preaching-plan/edit/<?= $entry['id'] ?>" class="btn-icon" title="Edit">
                        <svg width="16" height="16" viewBox="0 0 16 16" fill="none" xmlns="http://www.w3.org/2000/svg">
                            <path d="M11.333 2.00001C11.5084 1.82445 11.7163 1.68507 11.9448 1.59123C12.1734 1.49738 12.4181 1.45093 12.6663 1.45093C12.9146 1.45093 13.1593 1.49738 13.3878 1.59123C13.6164 1.68507 13.8242 1.82445 13.9997 2.00001C14.1753 2.17556 14.3147 2.38341 14.4085 2.61197C14.5024 2.84052 14.5488 3.08522 14.5488 3.33345C14.5488 3.58167 14.5024 3.82637 14.4085 4.05493C14.3147 4.28348 14.1753 4.49133 13.9997 4.66689L5.99967 12.6669L1.33301 13.3336L1.99967 8.66689L11.333 2.00001Z" stroke="currentColor" stroke-width="1.5" stroke-linecap="round" stroke-linejoin="round"/>
                        </svg>
                    </a>
                    <a href="<?= BASE_PATH ?>/events/preaching-plan/delete/<?= $entry['id'] ?>" class="btn-icon btn-icon-danger" data-confirm="Delete this entry?" title="Delete">
                        <svg width="16" height="16" viewBox="0 0 16 16" fill="none" xmlns="http://www.w3.org/2000/svg">
                            <path d="M2 4H3.33333H14" stroke="currentColor" stroke-width="1.5" stroke-linecap="round" stroke-linejoin="round"/>
                            <path d="M5.33333 4V2.66667C5.33333 2.31305 5.47381 1.97391 5.72386 1.72386C5.97391 1.47381 6.31305 1.33334 6.66667 1.33334H9.33333C9.68696 1.33334 10.0261 1.47381 10.2761 1.72386C10.5262 1.97391 10.6667 2.31305 10.6667 2.66667V4M12.6667 4V13.3333C12.6667 13.687 12.5262 14.0261 12.2761 14.2761C12.0261 14.5262 11.687 14.6667 11.3333 14.6667H4.66667C4.31305 14.6667 3.97391 14.5262 3.72386 14.2761C3.47381 14.0261 3.33333 13.687 3.33333 13.3333V4H12.6667Z" stroke="currentColor" stroke-width="1.5" stroke-linecap="round" stroke-linejoin="round"/>
                            <path d="M6.66667 7.33334V11.3333" stroke="currentColor" stroke-width="1.5" stroke-linecap="round" stroke-linejoin="round"/>
                            <path d="M9.33333 7.33334V11.3333" stroke="currentColor" stroke-width="1.5" stroke-linecap="round" stroke-linejoin="round"/>
                        </svg>
                    </a>
                </td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</div>
<?php endif; ?>

<?php require_once __DIR__ . '/../../includes/footer.php'; ?>
