<?php
/**
 * Budget Management
 */

require_once __DIR__ . '/../../includes/auth.php';
requireAuth();
requirePermission('finance.record');

$pageTitle = 'Budget Management';
$currentPage = 'finance';

// Ensure budgets table exists
try {
    $pdo->query('SELECT 1 FROM budgets LIMIT 1');
} catch (PDOException $e) {
    try {
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS budgets (
                id INT AUTO_INCREMENT PRIMARY KEY,
                fund_id INT NOT NULL,
                budget_year SMALLINT NOT NULL,
                budget_period ENUM('monthly', 'quarterly', 'yearly') DEFAULT 'yearly',
                period_value VARCHAR(20) NULL,
                amount DECIMAL(12,2) NOT NULL,
                notes TEXT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                created_by INT NULL,
                UNIQUE KEY unique_budget (fund_id, budget_year, budget_period, period_value),
                FOREIGN KEY (fund_id) REFERENCES funds(id) ON DELETE CASCADE,
                FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
                INDEX idx_budget_year (budget_year),
                INDEX idx_fund_year (fund_id, budget_year)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
    } catch (PDOException $e2) {
        // Ignore
    }
}

$action = $_GET['action'] ?? 'list';
$budgetId = (int)($_GET['id'] ?? 0);
$currentYear = (int)date('Y');
$selectedYear = (int)($_GET['year'] ?? $currentYear);

// Handle form submissions
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if ($action === 'create' || $action === 'edit') {
        $fundId = (int)($_POST['fund_id'] ?? 0);
        $budgetYear = (int)($_POST['budget_year'] ?? $currentYear);
        $budgetPeriod = trim($_POST['budget_period'] ?? 'yearly');
        $periodValue = trim($_POST['period_value'] ?? '');
        $amount = trim($_POST['amount'] ?? '');
        $notes = trim($_POST['notes'] ?? '');
        
        $errors = [];
        if (!$fundId) $errors[] = 'Please select a fund.';
        if ($budgetYear < 2000 || $budgetYear > 2100) $errors[] = 'Invalid year.';
        if (!in_array($budgetPeriod, ['monthly', 'quarterly', 'yearly'])) $errors[] = 'Invalid period.';
        if (!is_numeric($amount) || (float)$amount < 0) $errors[] = 'Please enter a valid amount.';
        
        if (empty($errors)) {
            try {
                if ($action === 'create') {
                    $stmt = $pdo->prepare("
                        INSERT INTO budgets (fund_id, budget_year, budget_period, period_value, amount, notes, created_by)
                        VALUES (?, ?, ?, ?, ?, ?, ?)
                    ");
                    $stmt->execute([
                        $fundId,
                        $budgetYear,
                        $budgetPeriod,
                        $periodValue ?: null,
                        (float)$amount,
                        $notes ?: null,
                        currentUserId()
                    ]);
                    header('Location: ' . BASE_PATH . '/finance/budgets?toast=' . urlencode('Budget created successfully.'));
                } else {
                    $stmt = $pdo->prepare("
                        UPDATE budgets 
                        SET fund_id = ?, budget_year = ?, budget_period = ?, period_value = ?, amount = ?, notes = ?
                        WHERE id = ?
                    ");
                    $stmt->execute([
                        $fundId,
                        $budgetYear,
                        $budgetPeriod,
                        $periodValue ?: null,
                        (float)$amount,
                        $notes ?: null,
                        $budgetId
                    ]);
                    header('Location: ' . BASE_PATH . '/finance/budgets?toast=' . urlencode('Budget updated successfully.'));
                }
                exit;
            } catch (PDOException $e) {
                if (strpos($e->getMessage(), 'unique_budget') !== false) {
                    $errors[] = 'A budget already exists for this fund, year, and period.';
                } else {
                    $errors[] = 'Error saving budget: ' . $e->getMessage();
                }
            }
        }
    }
    
    if ($action === 'delete' && $budgetId) {
        try {
            $pdo->prepare('DELETE FROM budgets WHERE id = ?')->execute([$budgetId]);
            header('Location: ' . BASE_PATH . '/finance/budgets?toast=' . urlencode('Budget deleted successfully.'));
            exit;
        } catch (PDOException $e) {
            header('Location: ' . BASE_PATH . '/finance/budgets?toast_error=' . urlencode('Error deleting budget.'));
            exit;
        }
    }
}

// Get funds
$funds = $pdo->query('SELECT id, name FROM funds ORDER BY name')->fetchAll();

// Get budgets for selected year
$budgets = [];
$budgetStats = [];
try {
    $budgetsStmt = $pdo->prepare("
        SELECT b.*, f.name AS fund_name
        FROM budgets b
        JOIN funds f ON b.fund_id = f.id
        WHERE b.budget_year = ?
        ORDER BY f.name, b.budget_period, b.period_value
    ");
    $budgetsStmt->execute([$selectedYear]);
    $budgets = $budgetsStmt->fetchAll();
    
    // Calculate actuals vs budgets
    foreach ($budgets as &$budget) {
        $startDate = $selectedYear . '-01-01';
        $endDate = $selectedYear . '-12-31';
        
        // Adjust date range based on period
        if ($budget['budget_period'] === 'monthly' && $budget['period_value']) {
            $month = (int)$budget['period_value'];
            $startDate = sprintf('%d-%02d-01', $selectedYear, $month);
            $endDate = date('Y-m-t', strtotime($startDate));
        } elseif ($budget['budget_period'] === 'quarterly' && $budget['period_value']) {
            $quarter = (int)$budget['period_value'];
            $startMonth = ($quarter - 1) * 3 + 1;
            $startDate = sprintf('%d-%02d-01', $selectedYear, $startMonth);
            $endDate = date('Y-m-t', strtotime(sprintf('%d-%02d-01', $selectedYear, $startMonth + 2)));
        }
        
        $actualStmt = $pdo->prepare("
            SELECT COALESCE(SUM(amount), 0) AS total
            FROM transactions
            WHERE fund_id = ? AND transaction_date BETWEEN ? AND ?
        ");
        $actualStmt->execute([$budget['fund_id'], $startDate, $endDate]);
        $actual = $actualStmt->fetchColumn();
        
        $budget['actual'] = (float)$actual;
        $budget['variance'] = $budget['actual'] - (float)$budget['amount'];
        $budget['percent'] = (float)$budget['amount'] > 0 ? ($budget['actual'] / (float)$budget['amount']) * 100 : 0;
    }
    unset($budget);
    
    // Overall stats
    $totalBudget = array_sum(array_map(function($b) { return (float)$b['amount']; }, $budgets));
    $totalActual = array_sum(array_map(function($b) { return $b['actual']; }, $budgets));
    $budgetStats = [
        'total_budget' => $totalBudget,
        'total_actual' => $totalActual,
        'total_variance' => $totalActual - $totalBudget,
        'percent' => $totalBudget > 0 ? ($totalActual / $totalBudget) * 100 : 0
    ];
} catch (PDOException $e) {
    error_log('Budget query error: ' . $e->getMessage());
}

// Get budget for edit
$editBudget = null;
if ($action === 'edit' && $budgetId) {
    try {
        $stmt = $pdo->prepare('SELECT * FROM budgets WHERE id = ?');
        $stmt->execute([$budgetId]);
        $editBudget = $stmt->fetch();
        if (!$editBudget) {
            $action = 'list';
        }
    } catch (PDOException $e) {
        $action = 'list';
    }
}

require_once __DIR__ . '/../../includes/header.php';
?>

<div class="header">
    <h1>Budget Management</h1>
    <div class="header-actions">
        <a href="<?= BASE_PATH ?>/finance" class="link-muted">← Finance</a>
        <a href="<?= BASE_PATH ?>/logout">Logout</a>
    </div>
</div>

<?php if ($action === 'create' || $action === 'edit'): ?>
<!-- Create/Edit Form -->
<div class="card">
    <h2 style="margin-top: 0;"><?= $action === 'create' ? 'Create' : 'Edit' ?> Budget</h2>
    
    <?php if (!empty($errors)): ?>
    <div class="alert alert-error">
        <ul style="margin: 0; padding-left: 1.5rem;">
            <?php foreach ($errors as $error): ?>
            <li><?= htmlspecialchars($error) ?></li>
            <?php endforeach; ?>
        </ul>
    </div>
    <?php endif; ?>
    
    <form method="post">
        <div class="form-group">
            <label for="fund_id">Fund *</label>
            <select id="fund_id" name="fund_id" required>
                <option value="">-- Select Fund --</option>
                <?php foreach ($funds as $fund): ?>
                <option value="<?= $fund['id'] ?>" <?= ($editBudget && $editBudget['fund_id'] == $fund['id']) ? 'selected' : '' ?>>
                    <?= htmlspecialchars($fund['name']) ?>
                </option>
                <?php endforeach; ?>
            </select>
        </div>
        
        <div class="form-group">
            <label for="budget_year">Year *</label>
            <input type="number" id="budget_year" name="budget_year" min="2000" max="2100" required
                value="<?= htmlspecialchars($editBudget ? $editBudget['budget_year'] : $selectedYear) ?>">
        </div>
        
        <div class="form-group">
            <label for="budget_period">Period Type *</label>
            <select id="budget_period" name="budget_period" required>
                <option value="yearly" <?= (!$editBudget || $editBudget['budget_period'] === 'yearly') ? 'selected' : '' ?>>Yearly</option>
                <option value="quarterly" <?= ($editBudget && $editBudget['budget_period'] === 'quarterly') ? 'selected' : '' ?>>Quarterly</option>
                <option value="monthly" <?= ($editBudget && $editBudget['budget_period'] === 'monthly') ? 'selected' : '' ?>>Monthly</option>
            </select>
        </div>
        
        <div class="form-group" id="period_value_group" style="display: none;">
            <label for="period_value" id="period_value_label">Period Value</label>
            <select id="period_value" name="period_value">
                <option value="">-- Select --</option>
            </select>
        </div>
        
        <div class="form-group">
            <label for="amount">Budget Amount (GH&#8373;) *</label>
            <input type="number" id="amount" name="amount" step="0.01" min="0" required
                value="<?= htmlspecialchars($editBudget ? $editBudget['amount'] : '') ?>">
        </div>
        
        <div class="form-group">
            <label for="notes">Notes</label>
            <textarea id="notes" name="notes"><?= htmlspecialchars($editBudget ? $editBudget['notes'] : '') ?></textarea>
        </div>
        
        <button type="submit" class="btn"><?= $action === 'create' ? 'Create' : 'Update' ?> Budget</button>
        <a href="<?= BASE_PATH ?>/finance/budgets" class="btn btn-secondary">Cancel</a>
    </form>
</div>

<script>
(function() {
    const periodSelect = document.getElementById('budget_period');
    const periodValueGroup = document.getElementById('period_value_group');
    const periodValueSelect = document.getElementById('period_value');
    const periodValueLabel = document.getElementById('period_value_label');
    
    function updatePeriodValue() {
        const period = periodSelect.value;
        periodValueSelect.innerHTML = '<option value="">-- Select --</option>';
        
        if (period === 'monthly') {
            periodValueLabel.textContent = 'Month';
            for (let i = 1; i <= 12; i++) {
                const monthNames = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
                const option = document.createElement('option');
                option.value = i;
                option.textContent = monthNames[i - 1];
                periodValueSelect.appendChild(option);
            }
            periodValueGroup.style.display = 'block';
        } else if (period === 'quarterly') {
            periodValueLabel.textContent = 'Quarter';
            for (let i = 1; i <= 4; i++) {
                const option = document.createElement('option');
                option.value = i;
                option.textContent = 'Q' + i;
                periodValueSelect.appendChild(option);
            }
            periodValueGroup.style.display = 'block';
        } else {
            periodValueGroup.style.display = 'none';
        }
        
        <?php if ($editBudget && $editBudget['period_value']): ?>
        periodValueSelect.value = <?= json_encode($editBudget['period_value']) ?>;
        <?php endif; ?>
    }
    
    periodSelect.addEventListener('change', updatePeriodValue);
    updatePeriodValue();
})();
</script>

<?php else: ?>
<!-- Budget List -->
<!-- Year Selector -->
<div class="card">
    <form method="get" style="display: flex; gap: 1rem; align-items: end;">
        <div>
            <label for="year" style="display: block; margin-bottom: 0.5rem; font-size: 0.875rem; color: #64748b;">Year</label>
            <input type="number" id="year" name="year" min="2000" max="2100" value="<?= $selectedYear ?>" style="padding: 0.5rem; border: 1px solid var(--border); border-radius: 4px;">
        </div>
        <div>
            <button type="submit" class="btn">View</button>
        </div>
    </form>
</div>

<!-- Statistics -->
<div style="display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 1rem; margin-bottom: 1.5rem;">
    <div class="card" style="background: #f8fafc;">
        <div style="font-size: 0.875rem; color: #64748b; margin-bottom: 0.5rem;">Total Budget</div>
        <div style="font-size: 1.75rem; font-weight: 600; color: #1e293b;">GH&#8373; <?= number_format($budgetStats['total_budget'], 2) ?></div>
    </div>
    <div class="card" style="background: #f8fafc;">
        <div style="font-size: 0.875rem; color: #64748b; margin-bottom: 0.5rem;">Total Actual</div>
        <div style="font-size: 1.75rem; font-weight: 600; color: #1e293b;">GH&#8373; <?= number_format($budgetStats['total_actual'], 2) ?></div>
    </div>
    <div class="card" style="background: #f8fafc;">
        <div style="font-size: 0.875rem; color: #64748b; margin-bottom: 0.5rem;">Variance</div>
        <div style="font-size: 1.75rem; font-weight: 600; color: <?= $budgetStats['total_variance'] >= 0 ? '#10b981' : '#ef4444' ?>;">
            <?= $budgetStats['total_variance'] >= 0 ? '+' : '' ?>GH&#8373; <?= number_format($budgetStats['total_variance'], 2) ?>
        </div>
    </div>
    <div class="card" style="background: #f8fafc;">
        <div style="font-size: 0.875rem; color: #64748b; margin-bottom: 0.5rem;">Progress</div>
        <div style="font-size: 1.75rem; font-weight: 600; color: #1e293b;"><?= number_format($budgetStats['percent'], 1) ?>%</div>
        <div style="margin-top: 0.5rem;">
            <div style="width: 100%; height: 8px; background: #e2e8f0; border-radius: 4px; overflow: hidden;">
                <div style="width: <?= min(100, max(0, $budgetStats['percent'])) ?>%; height: 100%; background: <?= $budgetStats['percent'] > 100 ? '#ef4444' : '#10b981' ?>;"></div>
            </div>
        </div>
    </div>
</div>

<!-- Budget List -->
<div class="card">
    <div style="display: flex; justify-content: space-between; align-items: center; margin-bottom: 1rem;">
        <h2 style="margin: 0;">Budgets for <?= $selectedYear ?></h2>
        <a href="<?= BASE_PATH ?>/finance/budgets?action=create&year=<?= $selectedYear ?>" class="btn">Create Budget</a>
    </div>
    
    <?php if (empty($budgets)): ?>
    <p class="text-muted">No budgets set for <?= $selectedYear ?>. <a href="<?= BASE_PATH ?>/finance/budgets?action=create&year=<?= $selectedYear ?>">Create one</a>.</p>
    <?php else: ?>
    <div class="table-wrap">
        <table>
            <thead>
                <tr>
                    <th>Fund</th>
                    <th>Period</th>
                    <th style="text-align: right;">Budget</th>
                    <th style="text-align: right;">Actual</th>
                    <th style="text-align: right;">Variance</th>
                    <th style="text-align: center;">Progress</th>
                    <th style="text-align: center;">Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($budgets as $budget): ?>
                <tr>
                    <td><?= htmlspecialchars($budget['fund_name']) ?></td>
                    <td>
                        <?php
                        $periodLabel = ucfirst($budget['budget_period']);
                        if ($budget['period_value']) {
                            if ($budget['budget_period'] === 'monthly') {
                                $monthNames = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
                                $periodLabel .= ' - ' . $monthNames[(int)$budget['period_value'] - 1];
                            } elseif ($budget['budget_period'] === 'quarterly') {
                                $periodLabel .= ' - Q' . $budget['period_value'];
                            }
                        }
                        echo htmlspecialchars($periodLabel);
                        ?>
                    </td>
                    <td style="text-align: right; font-weight: 500;">GH&#8373; <?= number_format($budget['amount'], 2) ?></td>
                    <td style="text-align: right;">GH&#8373; <?= number_format($budget['actual'], 2) ?></td>
                    <td style="text-align: right; color: <?= $budget['variance'] >= 0 ? '#10b981' : '#ef4444' ?>; font-weight: 500;">
                        <?= $budget['variance'] >= 0 ? '+' : '' ?>GH&#8373; <?= number_format($budget['variance'], 2) ?>
                    </td>
                    <td style="text-align: center;">
                        <div style="display: inline-block; width: 100px; height: 8px; background: #e2e8f0; border-radius: 4px; overflow: hidden;">
                            <div style="width: <?= min(100, max(0, $budget['percent'])) ?>%; height: 100%; background: <?= $budget['percent'] > 100 ? '#ef4444' : '#10b981' ?>;"></div>
                        </div>
                        <span style="margin-left: 0.5rem; font-size: 0.875rem; color: #64748b;"><?= number_format($budget['percent'], 1) ?>%</span>
                    </td>
                    <td style="text-align: center;">
                        <a href="<?= BASE_PATH ?>/finance/budgets?action=edit&id=<?= $budget['id'] ?>&year=<?= $selectedYear ?>" class="btn-icon" title="Edit">
                            <svg width="16" height="16" fill="none" stroke="currentColor" viewBox="0 0 24 24">
                                <path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M11 5H6a2 2 0 00-2 2v11a2 2 0 002 2h11a2 2 0 002-2v-5m-1.414-9.414a2 2 0 112.828 2.828L11.828 15H9v-2.828l8.586-8.586z"/>
                            </svg>
                        </a>
                        <a href="<?= BASE_PATH ?>/finance/budgets?action=delete&id=<?= $budget['id'] ?>&year=<?= $selectedYear ?>" 
                           onclick="return confirm('Are you sure you want to delete this budget?')" 
                           class="btn-icon" title="Delete" style="color: #ef4444;">
                            <svg width="16" height="16" fill="none" stroke="currentColor" viewBox="0 0 24 24">
                                <path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M19 7l-.867 12.142A2 2 0 0116.138 21H7.862a2 2 0 01-1.995-1.858L5 7m5 4v6m4-6v6m1-10V4a1 1 0 00-1-1h-4a1 1 0 00-1 1v3M4 7h16"/>
                            </svg>
                        </a>
                    </td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
    <?php endif; ?>
</div>

<!-- Budget vs Actual Report -->
<?php if (!empty($budgets)): ?>
<div class="card">
    <h2 style="margin-top: 0;">Budget vs Actual Comparison</h2>
    <canvas id="budgetChart" style="max-height: 400px;"></canvas>
</div>

<script src="https://cdn.jsdelivr.net/npm/chart.js@3.9.1/dist/chart.min.js"></script>
<script>
(function() {
    const ctx = document.getElementById('budgetChart');
    if (ctx) {
        const funds = <?= json_encode(array_map(function($b) { return $b['fund_name'] . ' (' . ucfirst($b['budget_period']) . ($b['period_value'] ? ' - ' . ($b['budget_period'] === 'monthly' ? ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'][(int)$b['period_value']-1] : 'Q' . $b['period_value']) : '') . ')'; }, $budgets)) ?>;
        const budgets = <?= json_encode(array_map(function($b) { return (float)$b['amount']; }, $budgets)) ?>;
        const actuals = <?= json_encode(array_map(function($b) { return $b['actual']; }, $budgets)) ?>;
        
        new Chart(ctx, {
            type: 'bar',
            data: {
                labels: funds,
                datasets: [
                    {
                        label: 'Budget',
                        data: budgets,
                        backgroundColor: '#1e3a5f',
                        borderColor: '#2c5282',
                        borderWidth: 1
                    },
                    {
                        label: 'Actual',
                        data: actuals,
                        backgroundColor: '#10b981',
                        borderColor: '#059669',
                        borderWidth: 1
                    }
                ]
            },
            options: {
                responsive: true,
                maintainAspectRatio: true,
                plugins: {
                    legend: { display: true },
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                return context.dataset.label + ': GH₵ ' + context.parsed.y.toLocaleString('en-US', {minimumFractionDigits: 2, maximumFractionDigits: 2});
                            }
                        }
                    }
                },
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            callback: function(value) {
                                return 'GH₵ ' + value.toLocaleString('en-US', {minimumFractionDigits: 0, maximumFractionDigits: 0});
                            }
                        }
                    }
                }
            }
        });
    }
})();
</script>
<?php endif; ?>

<?php endif; ?>

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