<?php
/**
 * Project Worth & Collections Report
 * LandSales Pro - Shows total potential value of projects and collections by date range
 */
define('APP_RUNNING', true);
require_once '../../db_connect.php';
require_once '../../includes/auth.php';
require_once '../../includes/subscription.php';
require_once '../../includes/functions.php';

initSecureSession();
requireAuth();
requireSubscription();

$userId = $_SESSION['user_id'];

// Date range filters
$dateFrom = $_GET['date_from'] ?? date('Y-m-01'); // First day of current month
$dateTo = $_GET['date_to'] ?? date('Y-m-d'); // Today
$projectFilter = $_GET['project_id'] ?? '';

// Get all projects for filter dropdown
$allProjects = db_fetch_all("SELECT id, name FROM projects WHERE user_id = ? ORDER BY name", 'i', [$userId]);

// Build project filter condition
$projectCondition = "";
$projectParams = [$userId];
$projectTypes = 'i';
if ($projectFilter) {
    $projectCondition = " AND pr.id = ?";
    $projectParams[] = $projectFilter;
    $projectTypes .= 'i';
}

// Get project worth data (total potential value if all plots are sold)
$projectWorthQuery = "SELECT
    pr.id,
    pr.name,
    pr.location,
    pr.status as project_status,
    COUNT(p.id) as total_plots,
    SUM(CASE WHEN p.status = 'available' THEN 1 ELSE 0 END) as available_plots,
    SUM(CASE WHEN p.status = 'reserved' THEN 1 ELSE 0 END) as reserved_plots,
    SUM(CASE WHEN p.status = 'sold' THEN 1 ELSE 0 END) as sold_plots,
    COALESCE(SUM(p.price), 0) as total_potential_value,
    COALESCE(SUM(CASE WHEN p.status = 'available' THEN p.price ELSE 0 END), 0) as unsold_value,
    COALESCE(SUM(CASE WHEN p.status IN ('sold', 'reserved') THEN p.price ELSE 0 END), 0) as committed_value
    FROM projects pr
    LEFT JOIN plots p ON pr.id = p.project_id
    WHERE pr.user_id = ? {$projectCondition}
    GROUP BY pr.id
    ORDER BY total_potential_value DESC";

$projectsWorth = db_fetch_all($projectWorthQuery, $projectTypes, $projectParams);

// Get collections per project within date range
$collectionsQuery = "SELECT
    pr.id as project_id,
    pr.name as project_name,
    COUNT(DISTINCT s.id) as sales_count,
    COALESCE(SUM(s.total_price), 0) as total_sales_value,
    COALESCE(SUM(py.amount), 0) as total_collected,
    COUNT(py.id) as payment_count
    FROM projects pr
    LEFT JOIN plots p ON pr.id = p.project_id
    LEFT JOIN sales s ON p.id = s.plot_id AND s.status IN ('active', 'completed')
    LEFT JOIN payments py ON s.id = py.sale_id AND py.status = 'confirmed'
        AND py.payment_date >= ? AND py.payment_date <= ?
    WHERE pr.user_id = ? {$projectCondition}
    GROUP BY pr.id
    ORDER BY total_collected DESC";

$collectionsParams = [$dateFrom, $dateTo, $userId];
$collectionsTypes = 'ssi';
if ($projectFilter) {
    $collectionsParams[] = $projectFilter;
    $collectionsTypes .= 'i';
}

$projectCollections = db_fetch_all($collectionsQuery, $collectionsTypes, $collectionsParams);

// Create lookup for collections by project id
$collectionsLookup = [];
foreach ($projectCollections as $pc) {
    $collectionsLookup[$pc['project_id']] = $pc;
}

// Get overall totals for all projects
$overallTotals = db_fetch_one("SELECT
    COALESCE(SUM(p.price), 0) as total_potential_value,
    COALESCE(SUM(CASE WHEN p.status = 'available' THEN p.price ELSE 0 END), 0) as unsold_value,
    COALESCE(SUM(CASE WHEN p.status IN ('sold', 'reserved') THEN p.price ELSE 0 END), 0) as committed_value,
    COUNT(p.id) as total_plots,
    SUM(CASE WHEN p.status = 'sold' THEN 1 ELSE 0 END) as sold_plots
    FROM plots p
    JOIN projects pr ON p.project_id = pr.id
    WHERE pr.user_id = ?", 'i', [$userId]);

// Get overall collections in date range
$overallCollections = db_fetch_one("SELECT
    COALESCE(SUM(py.amount), 0) as total_collected,
    COUNT(py.id) as payment_count
    FROM payments py
    JOIN sales s ON py.sale_id = s.id
    WHERE py.user_id = ? AND py.status = 'confirmed'
    AND py.payment_date >= ? AND py.payment_date <= ?", 'iss', [$userId, $dateFrom, $dateTo]);

// Get total outstanding balance (all time)
$outstandingBalance = db_fetch_one("SELECT
    COALESCE(SUM(s.total_price - COALESCE((SELECT SUM(amount) FROM payments WHERE sale_id = s.id AND status = 'confirmed'), 0)), 0) as balance
    FROM sales s WHERE s.user_id = ? AND s.status = 'active'", 'i', [$userId])['balance'];

// Get daily collections for chart (last 30 days or within date range)
$dailyCollections = db_fetch_all("SELECT
    DATE(payment_date) as date,
    SUM(amount) as amount,
    COUNT(*) as count
    FROM payments
    WHERE user_id = ? AND status = 'confirmed'
    AND payment_date >= ? AND payment_date <= ?
    GROUP BY DATE(payment_date)
    ORDER BY date", 'iss', [$userId, $dateFrom, $dateTo]);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Project Worth & Collections - <?php echo APP_NAME; ?></title>
    <link href="https://fonts.googleapis.com/css2?family=Inter:wght@300;400;500;600;700;800&display=swap" rel="stylesheet">
    <link rel="stylesheet" href="<?php echo SITE_URL; ?>/assets/font-awesome/css/all.min.css">
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<body>
    <?php include '../includes/sidebar.php'; ?>

    <div class="main-content">
        <div class="top-header">
            <button class="menu-toggle" onclick="toggleSidebar()">
                <i class="fas fa-bars"></i>
            </button>
            <h1 class="page-title">Project Worth & Collections</h1>
            <div class="header-actions">
                <a href="index.php" class="btn btn-outline">
                    <i class="fas fa-arrow-left"></i> Back
                </a>
                <button onclick="window.print()" class="btn btn-outline">
                    <i class="fas fa-print"></i> Print
                </button>
            </div>
        </div>

        <div class="content-wrapper">
            <!-- Date Range Filter -->
            <div class="filters-card">
                <form method="GET" class="filter-form">
                    <div class="filter-group">
                        <label>Date Range</label>
                        <div class="date-range">
                            <input type="date" name="date_from" value="<?php echo $dateFrom; ?>" class="form-control">
                            <span>to</span>
                            <input type="date" name="date_to" value="<?php echo $dateTo; ?>" class="form-control">
                        </div>
                    </div>
                    <div class="filter-group">
                        <label>Project</label>
                        <select name="project_id" class="form-control">
                            <option value="">All Projects</option>
                            <?php foreach ($allProjects as $proj): ?>
                            <option value="<?php echo $proj['id']; ?>" <?php echo $projectFilter == $proj['id'] ? 'selected' : ''; ?>>
                                <?php echo htmlspecialchars($proj['name']); ?>
                            </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <button type="submit" class="btn btn-primary">
                        <i class="fas fa-filter"></i> Apply Filter
                    </button>
                    <?php if ($dateFrom !== date('Y-m-01') || $dateTo !== date('Y-m-d') || $projectFilter): ?>
                    <a href="project-worth.php" class="btn btn-outline">Clear</a>
                    <?php endif; ?>
                </form>
            </div>

            <!-- Overall Summary Cards -->
            <div class="summary-section">
                <h2><i class="fas fa-chart-pie"></i> Overall Summary (All Projects)</h2>
                <div class="stats-grid">
                    <div class="stat-card gradient-blue">
                        <div class="stat-icon"><i class="fas fa-gem"></i></div>
                        <div class="stat-content">
                            <div class="stat-label">Total Project Worth</div>
                            <div class="stat-value">TZS <?php echo number_format($overallTotals['total_potential_value']); ?></div>
                            <div class="stat-sub">If all <?php echo number_format($overallTotals['total_plots']); ?> plots sold</div>
                        </div>
                    </div>

                    <div class="stat-card gradient-green">
                        <div class="stat-icon"><i class="fas fa-check-circle"></i></div>
                        <div class="stat-content">
                            <div class="stat-label">Committed Value</div>
                            <div class="stat-value">TZS <?php echo number_format($overallTotals['committed_value']); ?></div>
                            <div class="stat-sub"><?php echo number_format($overallTotals['sold_plots']); ?> plots sold/reserved</div>
                        </div>
                    </div>

                    <div class="stat-card gradient-orange">
                        <div class="stat-icon"><i class="fas fa-hourglass-half"></i></div>
                        <div class="stat-content">
                            <div class="stat-label">Unsold Value</div>
                            <div class="stat-value">TZS <?php echo number_format($overallTotals['unsold_value']); ?></div>
                            <div class="stat-sub">Available plots value</div>
                        </div>
                    </div>

                    <div class="stat-card gradient-red">
                        <div class="stat-icon"><i class="fas fa-balance-scale"></i></div>
                        <div class="stat-content">
                            <div class="stat-label">Outstanding Balance</div>
                            <div class="stat-value">TZS <?php echo number_format($outstandingBalance); ?></div>
                            <div class="stat-sub">Pending collection</div>
                        </div>
                    </div>
                </div>
            </div>

            <!-- Collections in Date Range -->
            <div class="collections-section">
                <h2>
                    <i class="fas fa-money-bill-wave"></i>
                    Collections (<?php echo date('M j, Y', strtotime($dateFrom)); ?> - <?php echo date('M j, Y', strtotime($dateTo)); ?>)
                </h2>
                <div class="collections-summary">
                    <div class="collection-stat">
                        <div class="collection-value">TZS <?php echo number_format($overallCollections['total_collected']); ?></div>
                        <div class="collection-label">Total Collected</div>
                    </div>
                    <div class="collection-stat">
                        <div class="collection-value"><?php echo number_format($overallCollections['payment_count']); ?></div>
                        <div class="collection-label">Payments Received</div>
                    </div>
                    <?php
                    $days = max(1, (strtotime($dateTo) - strtotime($dateFrom)) / 86400 + 1);
                    $dailyAvg = $overallCollections['total_collected'] / $days;
                    ?>
                    <div class="collection-stat">
                        <div class="collection-value">TZS <?php echo number_format($dailyAvg); ?></div>
                        <div class="collection-label">Daily Average</div>
                    </div>
                </div>
            </div>

            <!-- Collections Chart -->
            <?php if (!empty($dailyCollections)): ?>
            <div class="card chart-card">
                <div class="card-header">
                    <h3><i class="fas fa-chart-area"></i> Daily Collections Trend</h3>
                </div>
                <div class="card-body">
                    <canvas id="collectionsChart" height="200"></canvas>
                </div>
            </div>
            <?php endif; ?>

            <!-- Project-wise Breakdown -->
            <div class="card">
                <div class="card-header">
                    <h3><i class="fas fa-building"></i> Project-wise Breakdown</h3>
                </div>
                <div class="card-body">
                    <?php if (empty($projectsWorth)): ?>
                    <div class="empty-state">
                        <i class="fas fa-folder-open"></i>
                        <p>No projects found</p>
                    </div>
                    <?php else: ?>
                    <div class="table-responsive">
                        <table class="data-table">
                            <thead>
                                <tr>
                                    <th>Project</th>
                                    <th>Plots</th>
                                    <th>Total Worth</th>
                                    <th>Committed</th>
                                    <th>Unsold Value</th>
                                    <th>Collected (Period)</th>
                                    <th>Actions</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($projectsWorth as $project):
                                    $collections = $collectionsLookup[$project['id']] ?? ['total_collected' => 0, 'payment_count' => 0];
                                    $soldPercent = $project['total_plots'] > 0 ? ($project['sold_plots'] / $project['total_plots']) * 100 : 0;
                                ?>
                                <tr>
                                    <td>
                                        <div class="project-cell">
                                            <strong><?php echo htmlspecialchars($project['name']); ?></strong>
                                            <?php if ($project['location']): ?>
                                            <span class="location"><?php echo htmlspecialchars($project['location']); ?></span>
                                            <?php endif; ?>
                                        </div>
                                    </td>
                                    <td>
                                        <div class="plots-cell">
                                            <span class="total"><?php echo $project['total_plots']; ?> total</span>
                                            <div class="plot-breakdown">
                                                <span class="available" title="Available"><?php echo $project['available_plots']; ?></span>
                                                <span class="reserved" title="Reserved"><?php echo $project['reserved_plots']; ?></span>
                                                <span class="sold" title="Sold"><?php echo $project['sold_plots']; ?></span>
                                            </div>
                                            <div class="mini-progress">
                                                <div class="mini-fill" style="width: <?php echo $soldPercent; ?>%"></div>
                                            </div>
                                        </div>
                                    </td>
                                    <td>
                                        <span class="amount-primary">TZS <?php echo number_format($project['total_potential_value']); ?></span>
                                    </td>
                                    <td>
                                        <span class="amount-success">TZS <?php echo number_format($project['committed_value']); ?></span>
                                    </td>
                                    <td>
                                        <span class="amount-warning">TZS <?php echo number_format($project['unsold_value']); ?></span>
                                    </td>
                                    <td>
                                        <div class="collected-cell">
                                            <span class="amount-collected">TZS <?php echo number_format($collections['total_collected']); ?></span>
                                            <span class="payment-count"><?php echo $collections['payment_count']; ?> payments</span>
                                        </div>
                                    </td>
                                    <td>
                                        <a href="../projects/view.php?id=<?php echo $project['id']; ?>" class="btn-icon" title="View Project">
                                            <i class="fas fa-eye"></i>
                                        </a>
                                    </td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                            <tfoot>
                                <tr class="totals-row">
                                    <td><strong>TOTALS</strong></td>
                                    <td>
                                        <strong><?php echo array_sum(array_column($projectsWorth, 'total_plots')); ?> plots</strong>
                                    </td>
                                    <td>
                                        <strong class="amount-primary">TZS <?php echo number_format(array_sum(array_column($projectsWorth, 'total_potential_value'))); ?></strong>
                                    </td>
                                    <td>
                                        <strong class="amount-success">TZS <?php echo number_format(array_sum(array_column($projectsWorth, 'committed_value'))); ?></strong>
                                    </td>
                                    <td>
                                        <strong class="amount-warning">TZS <?php echo number_format(array_sum(array_column($projectsWorth, 'unsold_value'))); ?></strong>
                                    </td>
                                    <td>
                                        <strong class="amount-collected">TZS <?php echo number_format($overallCollections['total_collected']); ?></strong>
                                    </td>
                                    <td></td>
                                </tr>
                            </tfoot>
                        </table>
                    </div>
                    <?php endif; ?>
                </div>
            </div>
        </div>
    </div>

    <style>
        body { font-family: 'Inter', -apple-system, BlinkMacSystemFont, sans-serif; margin: 0; background: #f1f5f9; }
        .header-actions { display: flex; gap: 0.75rem; }
        .btn { padding: 0.625rem 1.25rem; border-radius: 8px; font-weight: 500; text-decoration: none; display: inline-flex; align-items: center; gap: 0.5rem; transition: all 0.2s; border: none; cursor: pointer; font-size: 0.875rem; }
        .btn-primary { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; }
        .btn-outline { border: 1px solid #e2e8f0; background: white; color: #64748b; }

        .filters-card { background: white; border-radius: 12px; padding: 1.25rem; margin-bottom: 1.5rem; box-shadow: 0 2px 8px rgba(0,0,0,0.05); }
        .filter-form { display: flex; gap: 1.5rem; align-items: flex-end; flex-wrap: wrap; }
        .filter-group { display: flex; flex-direction: column; gap: 0.5rem; }
        .filter-group label { font-size: 0.8rem; font-weight: 500; color: #64748b; }
        .date-range { display: flex; align-items: center; gap: 0.5rem; }
        .date-range span { color: #94a3b8; }
        .form-control { padding: 0.625rem 1rem; border: 1px solid #e2e8f0; border-radius: 8px; font-size: 0.875rem; }
        .form-control:focus { outline: none; border-color: #667eea; }

        .summary-section, .collections-section { margin-bottom: 1.5rem; }
        .summary-section h2, .collections-section h2 { font-size: 1.1rem; color: #1e293b; margin-bottom: 1rem; display: flex; align-items: center; gap: 0.5rem; }
        .summary-section h2 i, .collections-section h2 i { color: #667eea; }

        .stats-grid { display: grid; grid-template-columns: repeat(4, 1fr); gap: 1rem; }
        .stat-card { background: white; border-radius: 12px; padding: 1.25rem; display: flex; align-items: flex-start; gap: 1rem; box-shadow: 0 2px 8px rgba(0,0,0,0.05); position: relative; overflow: hidden; }
        .stat-card::before { content: ''; position: absolute; top: 0; left: 0; right: 0; height: 3px; }
        .gradient-blue::before { background: linear-gradient(135deg, #3b82f6 0%, #2563eb 100%); }
        .gradient-green::before { background: linear-gradient(135deg, #22c55e 0%, #16a34a 100%); }
        .gradient-orange::before { background: linear-gradient(135deg, #f59e0b 0%, #d97706 100%); }
        .gradient-red::before { background: linear-gradient(135deg, #ef4444 0%, #dc2626 100%); }
        .stat-icon { width: 48px; height: 48px; border-radius: 12px; display: flex; align-items: center; justify-content: center; font-size: 1.25rem; }
        .gradient-blue .stat-icon { background: rgba(59, 130, 246, 0.1); color: #3b82f6; }
        .gradient-green .stat-icon { background: rgba(34, 197, 94, 0.1); color: #22c55e; }
        .gradient-orange .stat-icon { background: rgba(245, 158, 11, 0.1); color: #f59e0b; }
        .gradient-red .stat-icon { background: rgba(239, 68, 68, 0.1); color: #ef4444; }
        .stat-content { flex: 1; }
        .stat-label { font-size: 0.8rem; color: #64748b; margin-bottom: 0.25rem; }
        .stat-value { font-size: 1.5rem; font-weight: 700; color: #1e293b; }
        .stat-sub { font-size: 0.75rem; color: #94a3b8; margin-top: 0.25rem; }

        .collections-summary { display: flex; gap: 2rem; background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); border-radius: 12px; padding: 1.5rem 2rem; }
        .collection-stat { text-align: center; flex: 1; }
        .collection-value { font-size: 1.75rem; font-weight: 700; color: white; }
        .collection-label { font-size: 0.85rem; color: rgba(255,255,255,0.8); margin-top: 0.25rem; }

        .card { background: white; border-radius: 12px; box-shadow: 0 2px 8px rgba(0,0,0,0.05); margin-bottom: 1.5rem; }
        .chart-card { }
        .card-header { padding: 1rem 1.25rem; border-bottom: 1px solid #f1f5f9; }
        .card-header h3 { margin: 0; font-size: 1rem; font-weight: 600; color: #1e293b; display: flex; align-items: center; gap: 0.5rem; }
        .card-header h3 i { color: #667eea; }
        .card-body { padding: 1.25rem; }

        .table-responsive { overflow-x: auto; }
        .data-table { width: 100%; border-collapse: collapse; }
        .data-table th, .data-table td { padding: 1rem; text-align: left; border-bottom: 1px solid #f1f5f9; }
        .data-table th { background: #f8fafc; font-weight: 600; font-size: 0.75rem; text-transform: uppercase; letter-spacing: 0.5px; color: #64748b; }
        .data-table tbody tr:hover { background: #f8fafc; }

        .project-cell { display: flex; flex-direction: column; }
        .project-cell strong { color: #1e293b; }
        .project-cell .location { font-size: 0.8rem; color: #94a3b8; }

        .plots-cell { }
        .plots-cell .total { font-weight: 600; color: #1e293b; display: block; }
        .plot-breakdown { display: flex; gap: 0.5rem; margin-top: 0.25rem; }
        .plot-breakdown span { font-size: 0.75rem; padding: 0.125rem 0.375rem; border-radius: 4px; }
        .plot-breakdown .available { background: #dcfce7; color: #166534; }
        .plot-breakdown .reserved { background: #fef3c7; color: #92400e; }
        .plot-breakdown .sold { background: #fee2e2; color: #991b1b; }
        .mini-progress { height: 4px; background: #e2e8f0; border-radius: 2px; margin-top: 0.5rem; overflow: hidden; }
        .mini-fill { height: 100%; background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); border-radius: 2px; }

        .amount-primary { font-weight: 600; color: #1e293b; }
        .amount-success { font-weight: 600; color: #16a34a; }
        .amount-warning { font-weight: 600; color: #d97706; }
        .amount-collected { font-weight: 600; color: #667eea; }

        .collected-cell { display: flex; flex-direction: column; }
        .payment-count { font-size: 0.75rem; color: #94a3b8; }

        .btn-icon { width: 32px; height: 32px; border-radius: 8px; display: flex; align-items: center; justify-content: center; color: #64748b; background: #f1f5f9; text-decoration: none; transition: all 0.2s; }
        .btn-icon:hover { background: #667eea; color: white; }

        .totals-row { background: #f8fafc; font-size: 0.95rem; }
        .totals-row td { border-top: 2px solid #e2e8f0; }

        .empty-state { text-align: center; padding: 3rem; color: #94a3b8; }
        .empty-state i { font-size: 2.5rem; margin-bottom: 0.75rem; }

        @media print {
            body { background: white; }
            .sidebar, .top-header, .filters-card, .header-actions, .btn-icon { display: none !important; }
            .main-content { margin-left: 0; padding: 0; }
            .card { box-shadow: none; border: 1px solid #e2e8f0; }
            .collections-summary { background: #1e293b !important; }
        }

        @media (max-width: 1200px) {
            .stats-grid { grid-template-columns: repeat(2, 1fr); }
        }

        @media (max-width: 768px) {
            .stats-grid { grid-template-columns: 1fr; }
            .collections-summary { flex-direction: column; gap: 1rem; }
            .filter-form { flex-direction: column; align-items: stretch; }
            .date-range { flex-wrap: wrap; }
        }
    </style>

    <?php if (!empty($dailyCollections)): ?>
    <script>
        const ctx = document.getElementById('collectionsChart').getContext('2d');
        new Chart(ctx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode(array_map(function($d) { return date('M j', strtotime($d['date'])); }, $dailyCollections)); ?>,
                datasets: [{
                    label: 'Collections (TZS)',
                    data: <?php echo json_encode(array_column($dailyCollections, 'amount')); ?>,
                    backgroundColor: 'rgba(102, 126, 234, 0.8)',
                    borderColor: '#667eea',
                    borderWidth: 1,
                    borderRadius: 4
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: { display: false }
                },
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            callback: function(value) {
                                if (value >= 1000000) return 'TZS ' + (value / 1000000).toFixed(1) + 'M';
                                if (value >= 1000) return 'TZS ' + (value / 1000).toFixed(0) + 'K';
                                return 'TZS ' + value;
                            }
                        }
                    }
                }
            }
        });
    </script>
    <?php endif; ?>
</body>
</html>
