<?php
/**
 * Member financial trends - individual member giving history and trends
 */

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

$stmt = $pdo->prepare("
    SELECT m.*, 
    (SELECT GROUP_CONCAT(mn.name ORDER BY mn.name SEPARATOR ', ')
     FROM member_ministries mm
     JOIN ministries mn ON mn.id = mm.ministry_id
     WHERE mm.member_id = m.id) AS ministry_names
    FROM members m
    WHERE m.id = ?
");
$stmt->execute([$id]);
$member = $stmt->fetch();

if (!$member) {
    header('Location: ' . BASE_PATH . '/members');
    exit;
}

$memberName = trim(($member['title'] ?? '') . ' ' . ($member['first_name'] ?? '') . ' ' . ($member['middle_name'] ?? '') . ' ' . ($member['last_name'] ?? ''));

// Date range filter
$start = trim($_GET['start'] ?? date('Y-01-01'));
$end = trim($_GET['end'] ?? date('Y-m-d'));
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $start) || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $end)) {
    $start = date('Y-01-01');
    $end = date('Y-m-d');
}

// Total giving (all time) - all transactions for this member
$stmt = $pdo->prepare("
    SELECT COALESCE(SUM(t.amount), 0) AS total, COUNT(t.id) AS tx_count
    FROM transactions t
    WHERE t.member_id = ?
");
$stmt->execute([$id]);
$allTime = $stmt->fetch();

// Total giving for date range - all transactions for this member
$stmt = $pdo->prepare("
    SELECT COALESCE(SUM(t.amount), 0) AS total, COUNT(t.id) AS tx_count
    FROM transactions t
    WHERE t.member_id = ? 
      AND t.transaction_date BETWEEN ? AND ?
");
$stmt->execute([$id, $start, $end]);
$periodTotal = $stmt->fetch();

// Giving by fund - all funds where this member has transactions
$stmt = $pdo->prepare("
    SELECT f.name, COALESCE(SUM(t.amount), 0) AS total, COUNT(t.id) AS tx_count
    FROM funds f
    INNER JOIN transactions t ON t.fund_id = f.id 
        AND t.member_id = ?
        AND t.transaction_date BETWEEN ? AND ?
    GROUP BY f.id
    ORDER BY total DESC
");
$stmt->execute([$id, $start, $end]);
$byFund = $stmt->fetchAll();

// Monthly trends (last 12 months) - all transactions for this member, grouped by fund
$monthLabels = [];
for ($i = 11; $i >= 0; $i--) {
    $monthLabels[] = date('M Y', strtotime("-$i months"));
}

// Get all funds this member has contributed to
$fundsForTrends = $pdo->prepare("
    SELECT DISTINCT f.id, f.name
    FROM funds f
    INNER JOIN transactions t ON t.fund_id = f.id
    WHERE t.member_id = ?
    ORDER BY f.name
");
$fundsForTrends->execute([$id]);
$fundsList = $fundsForTrends->fetchAll();

// Build monthly trends data by fund
$monthlyTrendsByFund = [];
foreach ($fundsList as $fund) {
    $fundData = ['name' => $fund['name'], 'months' => []];
    for ($i = 11; $i >= 0; $i--) {
        $monthStart = date('Y-m-01', strtotime("-$i months"));
        $monthEnd = date('Y-m-t', strtotime("-$i months"));
        $stmt = $pdo->prepare("
            SELECT COALESCE(SUM(t.amount), 0) 
            FROM transactions t
            WHERE t.member_id = ? 
              AND t.fund_id = ?
              AND t.transaction_date BETWEEN ? AND ?
        ");
        $stmt->execute([$id, $fund['id'], $monthStart, $monthEnd]);
        $fundData['months'][] = (float) $stmt->fetchColumn();
    }
    $monthlyTrendsByFund[] = $fundData;
}

// Also keep total monthly trends for backward compatibility
$monthlyTrends = [];
for ($i = 11; $i >= 0; $i--) {
    $monthStart = date('Y-m-01', strtotime("-$i months"));
    $monthEnd = date('Y-m-t', strtotime("-$i months"));
    $stmt = $pdo->prepare("
        SELECT COALESCE(SUM(t.amount), 0) 
        FROM transactions t
        WHERE t.member_id = ? 
          AND t.transaction_date BETWEEN ? AND ?
    ");
    $stmt->execute([$id, $monthStart, $monthEnd]);
    $monthTotal = (float) $stmt->fetchColumn();
    $monthlyTrends[] = [
        'month' => date('M Y', strtotime($monthStart)),
        'total' => $monthTotal
    ];
}

// Recent transactions - all transactions for this member
$stmt = $pdo->prepare("
    SELECT t.*, f.name AS fund_name
    FROM transactions t
    JOIN funds f ON t.fund_id = f.id
    WHERE t.member_id = ? 
      AND t.transaction_date BETWEEN ? AND ?
    ORDER BY t.transaction_date DESC, t.id DESC
    LIMIT 50
");
$stmt->execute([$id, $start, $end]);
$transactions = $stmt->fetchAll();

$pageTitle = 'Member Financial Trends';
$currentPage = 'members';
require_once __DIR__ . '/../../includes/header.php';
?>

<div class="header">
    <h1>Financial Trends: <?= htmlspecialchars($memberName) ?></h1>
    <div class="header-actions">
        <a href="<?= BASE_PATH ?>/members/view/<?= $id ?>" class="link-muted">View Member</a>
        <a href="<?= BASE_PATH ?>/members" class="link-muted">← Members</a>
        <a href="<?= BASE_PATH ?>/logout">Logout</a>
    </div>
</div>

<div class="card">
    <div class="filter-bar" style="margin-bottom: 1.5rem;">
        <form method="get" style="display: flex; gap: 0.5rem; align-items: center; flex-wrap: wrap;">
            <label>From</label>
            <input type="date" name="start" value="<?= htmlspecialchars($start) ?>">
            <label>To</label>
            <input type="date" name="end" value="<?= htmlspecialchars($end) ?>">
            <button type="submit" class="btn btn-sm">Apply</button>
            <a href="?start=<?= date('Y-01-01') ?>&end=<?= date('Y-m-d') ?>" class="btn btn-sm btn-secondary">This Year</a>
            <a href="?start=<?= date('Y-m-01') ?>&end=<?= date('Y-m-d') ?>" class="btn btn-sm btn-secondary">This Month</a>
        </form>
    </div>

    <div class="report-summary-stats" style="display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 1rem; margin-bottom: 1.5rem;">
        <div style="padding: 1rem; background: #f8fafc; border-radius: 6px;">
            <div style="font-size: 0.875rem; color: #64748b; margin-bottom: 0.25rem;">All-Time Total</div>
            <div style="font-size: 1.5rem; font-weight: 600; color: #1e293b;">GH&#8373; <?= number_format((float)$allTime['total'], 2) ?></div>
            <div style="font-size: 0.75rem; color: #94a3b8; margin-top: 0.25rem;"><?= (int)$allTime['tx_count'] ?> transaction<?= (int)$allTime['tx_count'] !== 1 ? 's' : '' ?></div>
        </div>
        <div style="padding: 1rem; background: #f8fafc; border-radius: 6px;">
            <div style="font-size: 0.875rem; color: #64748b; margin-bottom: 0.25rem;">Period Total</div>
            <div style="font-size: 1.5rem; font-weight: 600; color: #1e293b;">GH&#8373; <?= number_format((float)$periodTotal['total'], 2) ?></div>
            <div style="font-size: 0.75rem; color: #94a3b8; margin-top: 0.25rem;"><?= (int)$periodTotal['tx_count'] ?> transaction<?= (int)$periodTotal['tx_count'] !== 1 ? 's' : '' ?></div>
        </div>
        <div style="padding: 1rem; background: #f8fafc; border-radius: 6px;">
            <div style="font-size: 0.875rem; color: #64748b; margin-bottom: 0.25rem;">Average</div>
            <div style="font-size: 1.5rem; font-weight: 600; color: #1e293b;">GH&#8373; <?= number_format($periodTotal['tx_count'] > 0 ? (float)$periodTotal['total'] / $periodTotal['tx_count'] : 0, 2) ?></div>
            <div style="font-size: 0.75rem; color: #94a3b8; margin-top: 0.25rem;">per transaction</div>
        </div>
    </div>

    <?php if (!empty($byFund)): ?>
    <div class="charts-container" style="display: grid; grid-template-columns: 1fr 1fr; gap: 2rem; margin-bottom: 2rem;">
        <div class="chart-wrapper">
            <h3 style="margin-top: 0; margin-bottom: 1rem;">Giving by Fund</h3>
            <div style="position: relative; height: 350px; width: 100%;">
                <canvas id="fundPieChart"></canvas>
            </div>
        </div>
        <div class="chart-wrapper">
            <h3 style="margin-top: 0; margin-bottom: 1rem;">Monthly Trends</h3>
            <div style="position: relative; height: 350px; width: 100%;">
                <canvas id="trendsChart"></canvas>
            </div>
        </div>
    </div>

    <table style="margin-bottom: 1.5rem;">
        <thead>
            <tr>
                <th>Fund</th>
                <th style="text-align: right;">Transactions</th>
                <th style="text-align: right;">Total</th>
                <th style="text-align: right;">% of Total</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($byFund as $row): 
                $percentage = $periodTotal['total'] > 0 ? ($row['total'] / $periodTotal['total']) * 100 : 0;
            ?>
            <tr>
                <td><?= htmlspecialchars($row['name']) ?></td>
                <td style="text-align: right;"><?= (int)$row['tx_count'] ?></td>
                <td style="text-align: right;">GH&#8373; <?= number_format((float)$row['total'], 2) ?></td>
                <td style="text-align: right;"><?= number_format($percentage, 1) ?>%</td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
    <?php else: ?>
    <p class="text-muted">No financial transactions found for this member in the selected period.</p>
    <?php endif; ?>

    <?php if (!empty($transactions)): ?>
    <h3>Recent Transactions</h3>
    <div class="table-wrap">
        <table>
            <thead>
                <tr>
                    <th>Date</th>
                    <th>Fund</th>
                    <th style="text-align: right;">Amount</th>
                    <th>Note</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($transactions as $t): ?>
                <tr>
                    <td><?= date('M j, Y', strtotime($t['transaction_date'])) ?></td>
                    <td><?= htmlspecialchars($t['fund_name']) ?></td>
                    <td style="text-align: right;">GH&#8373; <?= number_format((float)$t['amount'], 2) ?></td>
                    <td><?= htmlspecialchars($t['note'] ?? '') ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
    <?php endif; ?>
</div>

<?php if (!empty($byFund)): ?>
<script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.0/dist/chart.umd.min.js"></script>
<script>
(function() {
    var fundData = <?= json_encode(array_map(function($f) { return ['name' => $f['name'], 'total' => (float)$f['total']]; }, $byFund)) ?>;
    var trendsData = <?= json_encode($monthlyTrends) ?>;
    var trendsByFund = <?= json_encode($monthlyTrendsByFund) ?>;
    var monthLabels = <?= json_encode($monthLabels) ?>;
    var colors = ['#2563eb', '#10b981', '#f59e0b', '#ef4444', '#8b5cf6', '#ec4899', '#06b6d4', '#84cc16'];

    // Fund Pie Chart
    var pieCtx = document.getElementById('fundPieChart');
    if (pieCtx && fundData.length > 0) {
        new Chart(pieCtx, {
            type: 'pie',
            data: {
                labels: fundData.map(f => f.name),
                datasets: [{
                    data: fundData.map(f => f.total),
                    backgroundColor: colors.slice(0, fundData.length)
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                aspectRatio: 1,
                plugins: {
                    legend: { position: 'bottom', labels: { padding: 15, boxWidth: 15 } },
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                var label = context.label || '';
                                var value = context.parsed || 0;
                                var total = context.dataset.data.reduce((a, b) => a + b, 0);
                                var percent = total > 0 ? ((value / total) * 100).toFixed(1) : 0;
                                return label + ': GH₵ ' + value.toLocaleString('en-US', {minimumFractionDigits: 2, maximumFractionDigits: 2}) + ' (' + percent + '%)';
                            }
                        }
                    }
                }
            }
        });
    }

    // Trends Line Chart - showing different funds
    var trendsCtx = document.getElementById('trendsChart');
    if (trendsCtx && trendsByFund.length > 0) {
        var datasets = trendsByFund.map(function(fund, idx) {
            return {
                label: fund.name,
                data: fund.months,
                borderColor: colors[idx % colors.length],
                backgroundColor: colors[idx % colors.length] + '20',
                fill: false,
                tension: 0.4
            };
        });
        
        // Add total line if there are multiple funds
        if (trendsByFund.length > 1) {
            datasets.push({
                label: 'Total',
                data: trendsData.map(t => t.total),
                borderColor: '#64748b',
                backgroundColor: '#64748b20',
                fill: false,
                tension: 0.4,
                borderDash: [5, 5],
                borderWidth: 2
            });
        }
        
        new Chart(trendsCtx, {
            type: 'line',
            data: {
                labels: monthLabels,
                datasets: datasets
            },
            options: {
                responsive: true,
                maintainAspectRatio: true,
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            callback: function(value) {
                                return 'GH₵ ' + value.toLocaleString('en-US', {minimumFractionDigits: 0, maximumFractionDigits: 0});
                            }
                        }
                    }
                },
                plugins: {
                    legend: {
                        position: 'bottom',
                        labels: {
                            boxWidth: 12,
                            padding: 10
                        }
                    },
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                return context.dataset.label + ': GH₵ ' + context.parsed.y.toLocaleString('en-US', {minimumFractionDigits: 2, maximumFractionDigits: 2});
                            }
                        }
                    }
                }
            }
        });
    } else if (trendsCtx && trendsData.length > 0) {
        // Fallback to single line if no fund breakdown
        new Chart(trendsCtx, {
            type: 'line',
            data: {
                labels: trendsData.map(t => t.month),
                datasets: [{
                    label: 'Monthly Giving (GH₵)',
                    data: trendsData.map(t => t.total),
                    borderColor: colors[0],
                    backgroundColor: colors[0] + '20',
                    fill: true,
                    tension: 0.4
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                aspectRatio: 2,
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            callback: function(value) {
                                return 'GH₵ ' + value.toLocaleString('en-US', {minimumFractionDigits: 0, maximumFractionDigits: 0});
                            }
                        }
                    }
                },
                plugins: {
                    legend: {
                        position: 'bottom',
                        labels: { padding: 15, boxWidth: 12 }
                    },
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                return 'GH₵ ' + context.parsed.y.toLocaleString('en-US', {minimumFractionDigits: 2, maximumFractionDigits: 2});
                            }
                        }
                    }
                }
            }
        });
    }
})();
</script>
<?php endif; ?>

<style>
/* Print Styles for Member Financial Trends */
@media print {
    .sidebar,
    .header-actions,
    .filter-bar,
    button,
    .btn,
    a[href]:not(.print-link) {
        display: none !important;
    }
    
    .main {
        margin: 0;
        padding: 0;
        max-width: 100%;
    }
    
    .card {
        box-shadow: none;
        border: none;
        padding: 0;
        margin: 0;
    }
    
    .charts-container {
        grid-template-columns: 1fr !important;
        gap: 2rem !important;
    }
    
    .chart-wrapper {
        page-break-inside: avoid;
        margin-bottom: 1.5rem;
    }
    
    .chart-wrapper > div {
        height: 400px !important;
        width: 100% !important;
    }
    
    /* Hide charts in print - show detailed tables instead */
    canvas,
    .chart-wrapper,
    .charts-container,
    div[style*="position: relative"][style*="height"] {
        display: none !important;
    }
    
    h3:has(+ div[style*="position: relative"]) {
        display: none !important;
    }
    
    /* Enhanced table styling for print */
    table {
        width: 100%;
        border-collapse: collapse;
        margin-bottom: 1.5rem;
        font-size: 0.875rem;
    }
    
    table thead {
        background-color: #f5f5f5;
    }
    
    table tbody tr:nth-child(even) {
        background-color: #fafafa;
    }
    
    table th,
    table td {
        border: 1px solid #333;
        padding: 0.5rem 0.75rem;
    }
    
    table th {
        background-color: #e5e5e5 !important;
        font-weight: 700;
        color: #000;
    }
    
    table td[style*="text-align: right"],
    table th[style*="text-align: right"] {
        text-align: right !important;
    }
    
    table {
        width: 100%;
        border-collapse: collapse;
    }
    
    table th,
    table td {
        border: 1px solid #ddd;
        padding: 0.5rem;
    }
    
    table th {
        background-color: #f5f5f5;
    }
    
    @page {
        margin: 1.5cm;
        size: A4 landscape;
    }
}
</style>

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