<?php
/**
 * Pledges - record and list pledges per member/fund/year
 */

$pageTitle = 'Pledges';
$currentPage = 'finance';

// Ensure pledges table exists (for existing installs)
try {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS pledges (
            id INT AUTO_INCREMENT PRIMARY KEY,
            member_id INT NOT NULL,
            fund_id INT NOT NULL,
            amount DECIMAL(12,2) NOT NULL,
            pledge_year SMALLINT NOT NULL,
            notes TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE,
            FOREIGN KEY (fund_id) REFERENCES funds(id),
            INDEX idx_pledge_year (pledge_year),
            INDEX idx_pledge_member (member_id)
        )
    ");
} catch (PDOException $e) {
    if (strpos($e->getMessage(), 'already exists') === false) {
        error_log('Pledges table: ' . $e->getMessage());
    }
}

$filterYear = isset($_GET['year']) ? (int) $_GET['year'] : (int) date('Y');
$filterMemberId = isset($_GET['member_id']) ? (int) $_GET['member_id'] : 0;

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $postAction = $_POST['action'] ?? '';

    if ($postAction === 'save_pledge') {
        $id = isset($_POST['pledge_id']) ? (int) $_POST['pledge_id'] : 0;
        $memberId = (int) ($_POST['member_id'] ?? 0);
        $fundId = (int) ($_POST['fund_id'] ?? 0);
        $amount = (float) ($_POST['amount'] ?? 0);
        $pledgeYear = (int) ($_POST['pledge_year'] ?? date('Y'));
        $notes = trim($_POST['notes'] ?? '') ?: null;

        if ($memberId <= 0 || $fundId <= 0 || $amount <= 0 || $pledgeYear < 2000 || $pledgeYear > 2100) {
            header('Location: ' . BASE_PATH . '/finance/pledges?toast_error=' . urlencode('Invalid member, fund, amount, or year.'));
            exit;
        }

        if ($id > 0) {
            $pdo->prepare('UPDATE pledges SET member_id=?, fund_id=?, amount=?, pledge_year=?, notes=? WHERE id=?')
                ->execute([$memberId, $fundId, $amount, $pledgeYear, $notes, $id]);
            header('Location: ' . BASE_PATH . '/finance/pledges?toast=' . urlencode('Pledge updated.'));
        } else {
            $pdo->prepare('INSERT INTO pledges (member_id, fund_id, amount, pledge_year, notes) VALUES (?, ?, ?, ?, ?)')
                ->execute([$memberId, $fundId, $amount, $pledgeYear, $notes]);
            header('Location: ' . BASE_PATH . '/finance/pledges?toast=' . urlencode('Pledge added.'));
        }
        exit;
    }
}

if (isset($_GET['delete']) && (int) $_GET['delete'] > 0) {
    $pdo->prepare('DELETE FROM pledges WHERE id = ?')->execute([(int) $_GET['delete']]);
    header('Location: ' . BASE_PATH . '/finance/pledges?toast=' . urlencode('Pledge deleted.'));
    exit;
}

$where = ['1=1'];
$params = [];
if ($filterYear > 0) {
    $where[] = 'p.pledge_year = ?';
    $params[] = $filterYear;
}
if ($filterMemberId > 0) {
    $where[] = 'p.member_id = ?';
    $params[] = $filterMemberId;
}
$whereStr = implode(' AND ', $where);

$pledges = $pdo->prepare("
    SELECT p.*, f.name AS fund_name,
           CONCAT(m.first_name, ' ', m.last_name) AS member_name
    FROM pledges p
    JOIN funds f ON f.id = p.fund_id
    JOIN members m ON m.id = p.member_id
    WHERE $whereStr
    ORDER BY p.pledge_year DESC, m.last_name, m.first_name, f.name
");
$pledges->execute($params);
$pledges = $pledges->fetchAll(PDO::FETCH_ASSOC);

$funds = $pdo->query('SELECT id, name FROM funds ORDER BY name')->fetchAll(PDO::FETCH_ASSOC);
$members = $pdo->query('SELECT id, first_name, last_name FROM members ORDER BY last_name, first_name')->fetchAll(PDO::FETCH_ASSOC);

$editId = isset($_GET['edit']) ? (int) $_GET['edit'] : 0;
$editPledge = null;
if ($editId > 0) {
    $stmt = $pdo->prepare('SELECT * FROM pledges WHERE id = ?');
    $stmt->execute([$editId]);
    $editPledge = $stmt->fetch(PDO::FETCH_ASSOC);
}

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

<div class="header">
    <h1>Pledges</h1>
    <div class="header-actions">
        <a href="<?= BASE_PATH ?>/finance" class="link-muted">← Finance</a>
        <a href="<?= BASE_PATH ?>/finance/reports?type=pledge" class="btn btn-secondary">Pledge vs giving</a>
        <a href="<?= BASE_PATH ?>/logout">Logout</a>
    </div>
</div>

<div class="card">
    <h2 style="margin-top: 0;"><?= $editPledge ? 'Edit pledge' : 'Add pledge' ?></h2>
    <form method="post">
        <input type="hidden" name="action" value="save_pledge">
        <?php if ($editPledge): ?><input type="hidden" name="pledge_id" value="<?= (int) $editPledge['id'] ?>"><?php endif; ?>
        <div class="form-row">
            <div class="form-group">
                <label for="pledge_member">Member *</label>
                <select id="pledge_member" name="member_id" required>
                    <option value="">— Select —</option>
                    <?php foreach ($members as $m): ?>
                    <option value="<?= (int) $m['id'] ?>" <?= ($editPledge && (int)$editPledge['member_id'] === (int)$m['id']) ? 'selected' : '' ?>><?= htmlspecialchars(trim($m['first_name'] . ' ' . $m['last_name'])) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="form-group">
                <label for="pledge_fund">Fund *</label>
                <select id="pledge_fund" name="fund_id" required>
                    <option value="">— Select —</option>
                    <?php foreach ($funds as $f): ?>
                    <option value="<?= (int) $f['id'] ?>" <?= ($editPledge && (int)$editPledge['fund_id'] === (int)$f['id']) ? 'selected' : '' ?>><?= htmlspecialchars($f['name']) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
        </div>
        <div class="form-row">
            <div class="form-group">
                <label for="pledge_amount">Amount (GH¢) *</label>
                <input type="number" id="pledge_amount" name="amount" step="0.01" min="0.01" required value="<?= $editPledge ? htmlspecialchars($editPledge['amount']) : '' ?>">
            </div>
            <div class="form-group">
                <label for="pledge_year">Year *</label>
                <input type="number" id="pledge_year" name="pledge_year" min="2000" max="2100" required value="<?= $editPledge ? (int) $editPledge['pledge_year'] : date('Y') ?>">
            </div>
        </div>
        <div class="form-group">
            <label for="pledge_notes">Notes</label>
            <textarea id="pledge_notes" name="notes" rows="2"><?= $editPledge ? htmlspecialchars($editPledge['notes'] ?? '') : '' ?></textarea>
        </div>
        <div class="form-actions" style="margin-top: 1rem;">
            <button type="submit" class="btn"><?= $editPledge ? 'Update' : 'Add' ?> pledge</button>
            <?php if ($editPledge): ?><a href="<?= BASE_PATH ?>/finance/pledges" class="btn btn-secondary">Cancel</a><?php endif; ?>
        </div>
    </form>
</div>

<div class="card">
    <h2>Pledge list</h2>
    <form method="get" class="filter-bar" style="margin-bottom: 1rem;">
        <label>Year:</label>
        <select name="year" onchange="this.form.submit()">
            <?php for ($y = date('Y'); $y >= date('Y') - 5; $y--): ?>
            <option value="<?= $y ?>" <?= $filterYear === $y ? 'selected' : '' ?>><?= $y ?></option>
            <?php endfor; ?>
        </select>
        <label style="margin-left: 1rem;">Member:</label>
        <select name="member_id" onchange="this.form.submit()">
            <option value="">All</option>
            <?php foreach ($members as $m): ?>
            <option value="<?= (int) $m['id'] ?>" <?= $filterMemberId === (int)$m['id'] ? 'selected' : '' ?>><?= htmlspecialchars(trim($m['first_name'] . ' ' . $m['last_name'])) ?></option>
            <?php endforeach; ?>
        </select>
    </form>
    <div class="table-wrap">
        <table>
            <thead>
                <tr>
                    <th>Member</th>
                    <th>Fund</th>
                    <th style="text-align: right;">Amount</th>
                    <th>Year</th>
                    <th>Notes</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php if (empty($pledges)): ?>
                <tr><td colspan="6" class="text-muted">No pledges for the selected filter.</td></tr>
                <?php else: ?>
                <?php foreach ($pledges as $p): ?>
                <tr>
                    <td><?= htmlspecialchars($p['member_name']) ?></td>
                    <td><?= htmlspecialchars($p['fund_name']) ?></td>
                    <td style="text-align: right;">GH¢ <?= number_format((float) $p['amount'], 2) ?></td>
                    <td><?= (int) $p['pledge_year'] ?></td>
                    <td><?= htmlspecialchars($p['notes'] ?? '') ?></td>
                    <td class="actions">
                        <a href="<?= BASE_PATH ?>/finance/pledges?edit=<?= (int) $p['id'] ?>" class="btn-icon" title="Edit">✎</a>
                        <a href="<?= BASE_PATH ?>/finance/pledges?delete=<?= (int) $p['id'] ?>" class="btn-icon btn-icon-danger" data-confirm="Delete this pledge?" title="Delete">🗑</a>
                    </td>
                </tr>
                <?php endforeach; ?>
                <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

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