<?php
require 'includes/header.php';
require_once 'includes/db.php';
require_once 'includes/functions.php';

$companyId = function_exists('getCurrentCompanyId') ? getCurrentCompanyId() : null;
$projectId = isset($_GET['id']) ? (int)$_GET['id'] : 0;
if ($projectId <= 0) {
    header('Location: projects.php');
    exit;
}

$project = null;
try {
    $hasProj = $pdo->query("SHOW TABLES LIKE 'projects'")->rowCount() > 0;
    if (!$hasProj) { throw new Exception('projects_missing'); }
    $sql = "SELECT * FROM projects WHERE id = ?";
    $params = [$projectId];
    if ($companyId && function_exists('tableHasColumn') && tableHasColumn('projects','company_id')) {
        $sql = "SELECT * FROM projects WHERE id = ? AND (company_id = ? OR company_id IS NULL)";
        $params = [$projectId, $companyId];
    }
    $st = $pdo->prepare($sql);
    $st->execute($params);
    $project = $st->fetch(PDO::FETCH_ASSOC) ?: null;
} catch (Throwable $e) { $project = null; }

if (!$project) {
    header('Location: projects.php?not_found=1');
    exit;
}

$startRaw = trim((string)($_GET['start_date'] ?? ''));
$endRaw = trim((string)($_GET['end_date'] ?? ''));
$startDate = ($startRaw !== '' && preg_match('/^\d{4}-\d{2}-\d{2}$/', $startRaw)) ? ($startRaw . ' 00:00:00') : date('Y-m-01 00:00:00');
$endDate = ($endRaw !== '' && preg_match('/^\d{4}-\d{2}-\d{2}$/', $endRaw)) ? ($endRaw . ' 23:59:59') : date('Y-m-t 23:59:59');

$approvedStatuses = function_exists('kpiSqlList') ? kpiSqlList(kpiPaymentFinalizedStatuses()) : "('verified','approved','paid','completed','success')";
$payDateCol = function_exists('kpiPaymentDateColumn') ? kpiPaymentDateColumn('payments') : 'created_at';

$revenue = 0.0;
$expense = 0.0;
$payments = [];
$expenses = [];

try {
    $cmpPm = ($companyId && function_exists('tableHasColumn') && tableHasColumn('payments','company_id')) ? " AND (pm.company_id = ?)" : "";
    $cmpParams = ($companyId && function_exists('tableHasColumn') && tableHasColumn('payments','company_id')) ? [$companyId] : [];
    $sqlRev = "
        SELECT COALESCE(SUM(pm.amount),0) AS revenue
        FROM payments pm
        LEFT JOIN allocations a ON a.id = pm.allocation_id
        LEFT JOIN properties p ON p.id = a.property_id
        LEFT JOIN projects prjProp ON prjProp.ref_table = 'properties' AND prjProp.ref_id = p.id
        WHERE (pm.project_id = ? OR (pm.project_id IS NULL AND prjProp.id = ?))
          AND pm.status IN $approvedStatuses
          AND pm.$payDateCol BETWEEN ? AND ?
          $cmpPm
    ";
    $stR = $pdo->prepare($sqlRev);
    $stR->execute(array_merge([$projectId, $projectId, $startDate, $endDate], $cmpParams));
    $revenue = (float)($stR->fetchColumn() ?: 0);
} catch (Throwable $e) { $revenue = 0.0; }

try {
    ensureManualExpensesTable();
    $cmpEm = ($companyId && function_exists('tableHasColumn') && tableHasColumn('expenses_manual','company_id')) ? " AND (em.company_id = ?)" : "";
    $cmpParams = ($companyId && function_exists('tableHasColumn') && tableHasColumn('expenses_manual','company_id')) ? [$companyId] : [];
    $sqlExp = "
        SELECT COALESCE(SUM(em.amount),0) AS expense
        FROM expenses_manual em
        LEFT JOIN projects prjEst ON prjEst.ref_table = 'estates' AND prjEst.ref_id = em.estate_id
        WHERE (em.project_id = ? OR (em.project_id IS NULL AND em.estate_id IS NOT NULL AND prjEst.id = ?))
          AND em.amount IS NOT NULL AND em.amount > 0
          AND LOWER(TRIM(em.status)) = 'approved'
          AND em.expense_date BETWEEN ? AND ?
          $cmpEm
    ";
    $stE = $pdo->prepare($sqlExp);
    $stE->execute(array_merge([$projectId, $projectId, $startDate, $endDate], $cmpParams));
    $expense = (float)($stE->fetchColumn() ?: 0);
} catch (Throwable $e) { $expense = 0.0; }

try {
    $cmpPm = ($companyId && function_exists('tableHasColumn') && tableHasColumn('payments','company_id')) ? " AND (pm.company_id = ?)" : "";
    $cmpParams = ($companyId && function_exists('tableHasColumn') && tableHasColumn('payments','company_id')) ? [$companyId] : [];
    $sqlPay = "
        SELECT
            pm.id,
            pm.amount,
            pm.status,
            pm.$payDateCol AS txn_date,
            pm.reference,
            pm.method,
            u.name AS user_name
        FROM payments pm
        LEFT JOIN users u ON u.id = pm.user_id
        LEFT JOIN allocations a ON a.id = pm.allocation_id
        LEFT JOIN properties p ON p.id = a.property_id
        LEFT JOIN projects prjProp ON prjProp.ref_table = 'properties' AND prjProp.ref_id = p.id
        WHERE (pm.project_id = ? OR (pm.project_id IS NULL AND prjProp.id = ?))
          AND pm.$payDateCol BETWEEN ? AND ?
          $cmpPm
        ORDER BY pm.$payDateCol DESC, pm.id DESC
        LIMIT 200
    ";
    $stP = $pdo->prepare($sqlPay);
    $stP->execute(array_merge([$projectId, $projectId, $startDate, $endDate], $cmpParams));
    $payments = $stP->fetchAll(PDO::FETCH_ASSOC) ?: [];
} catch (Throwable $e) { $payments = []; }

try {
    ensureManualExpensesTable();
    $cmpEm = ($companyId && function_exists('tableHasColumn') && tableHasColumn('expenses_manual','company_id')) ? " AND (em.company_id = ?)" : "";
    $cmpParams = ($companyId && function_exists('tableHasColumn') && tableHasColumn('expenses_manual','company_id')) ? [$companyId] : [];
    $sqlEm = "
        SELECT
            em.id,
            em.amount,
            em.status,
            em.expense_date AS txn_date,
            em.title,
            em.account_head,
            em.account_sub_head,
            u.name AS recorded_by
        FROM expenses_manual em
        LEFT JOIN users u ON u.id = em.recorded_by_user_id
        LEFT JOIN projects prjEst ON prjEst.ref_table = 'estates' AND prjEst.ref_id = em.estate_id
        WHERE (em.project_id = ? OR (em.project_id IS NULL AND em.estate_id IS NOT NULL AND prjEst.id = ?))
          AND em.expense_date BETWEEN ? AND ?
          $cmpEm
        ORDER BY em.expense_date DESC, em.id DESC
        LIMIT 200
    ";
    $stEm = $pdo->prepare($sqlEm);
    $stEm->execute(array_merge([$projectId, $projectId, $startDate, $endDate], $cmpParams));
    $expenses = $stEm->fetchAll(PDO::FETCH_ASSOC) ?: [];
} catch (Throwable $e) { $expenses = []; }

$transactions = [];
foreach ($payments as $p) {
    $transactions[] = [
        'type' => 'Payment',
        'id' => (int)($p['id'] ?? 0),
        'date' => (string)($p['txn_date'] ?? ''),
        'amount' => (float)($p['amount'] ?? 0),
        'status' => (string)($p['status'] ?? ''),
        'note' => trim((string)($p['reference'] ?? '')),
        'who' => trim((string)($p['user_name'] ?? '')),
    ];
}
foreach ($expenses as $e) {
    $transactions[] = [
        'type' => 'Expense',
        'id' => (int)($e['id'] ?? 0),
        'date' => (string)($e['txn_date'] ?? ''),
        'amount' => 0.0 - (float)($e['amount'] ?? 0),
        'status' => (string)($e['status'] ?? ''),
        'note' => trim((string)($e['title'] ?? '')),
        'who' => trim((string)($e['recorded_by'] ?? '')),
    ];
}
usort($transactions, function($a, $b) {
    $da = (string)($a['date'] ?? '');
    $db = (string)($b['date'] ?? '');
    if ($da === $db) return 0;
    return $da < $db ? 1 : -1;
});

$profit = $revenue - $expense;
?>

<div class="container-fluid px-4 py-4">
    <div class="d-flex flex-column flex-md-row justify-content-between align-items-md-center mb-3 gap-3">
        <div>
            <h1 class="h4 fw-bold text-white mb-1"><?= htmlspecialchars((string)($project['name'] ?? 'Project')) ?></h1>
            <div class="text-muted small">
                <?= htmlspecialchars((string)($project['location'] ?? '')) ?>
                <?php if (!empty($project['type'])): ?> • <?= htmlspecialchars((string)$project['type']) ?><?php endif; ?>
                <?php if (!empty($project['status'])): ?> • <?= htmlspecialchars((string)$project['status']) ?><?php endif; ?>
            </div>
        </div>
        <div class="d-flex gap-2">
            <a href="projects.php" class="btn btn-outline-light">Back</a>
        </div>
    </div>

    <div class="card border-0 shadow-sm mb-3">
        <div class="card-body">
            <form method="GET" class="row g-2 align-items-end">
                <input type="hidden" name="id" value="<?= (int)$projectId ?>">
                <div class="col-6 col-md-3">
                    <label class="form-label small text-muted fw-bold">Start Date</label>
                    <input type="date" name="start_date" class="form-control" value="<?= htmlspecialchars(substr($startDate, 0, 10)) ?>">
                </div>
                <div class="col-6 col-md-3">
                    <label class="form-label small text-muted fw-bold">End Date</label>
                    <input type="date" name="end_date" class="form-control" value="<?= htmlspecialchars(substr($endDate, 0, 10)) ?>">
                </div>
                <div class="col-12 col-md-6 d-flex justify-content-end gap-2">
                    <a class="btn btn-outline-secondary" href="project-details.php?id=<?= (int)$projectId ?>">Reset</a>
                    <button class="btn btn-primary" type="submit">Apply</button>
                </div>
            </form>
        </div>
    </div>

    <div class="row g-3 mb-3">
        <div class="col-12 col-md-4">
            <div class="card border-0 shadow-sm h-100">
                <div class="card-body">
                    <div class="text-muted small">Revenue</div>
                    <div class="h5 mb-0">₦<?= number_format($revenue, 2) ?></div>
                </div>
            </div>
        </div>
        <div class="col-12 col-md-4">
            <div class="card border-0 shadow-sm h-100">
                <div class="card-body">
                    <div class="text-muted small">Expenses</div>
                    <div class="h5 mb-0">₦<?= number_format($expense, 2) ?></div>
                </div>
            </div>
        </div>
        <div class="col-12 col-md-4">
            <div class="card border-0 shadow-sm h-100">
                <div class="card-body">
                    <div class="text-muted small">Profit</div>
                    <div class="h5 mb-0 <?= $profit < 0 ? 'text-danger' : 'text-success' ?>">₦<?= number_format($profit, 2) ?></div>
                </div>
            </div>
        </div>
    </div>

    <div class="card border-0 shadow-sm">
        <div class="card-body">
            <div class="d-flex justify-content-between align-items-center flex-wrap gap-2 mb-2">
                <div class="fw-bold">Transactions</div>
                <div class="text-muted small"><?= htmlspecialchars(date('M d, Y', strtotime($startDate))) ?> – <?= htmlspecialchars(date('M d, Y', strtotime($endDate))) ?></div>
            </div>
            <div class="table-responsive">
                <table class="table align-middle mb-0">
                    <thead>
                        <tr>
                            <th>Date</th>
                            <th>Type</th>
                            <th>Note</th>
                            <th>By</th>
                            <th>Status</th>
                            <th class="text-end">Amount</th>
                            <th class="text-end">Ref</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (empty($transactions)): ?>
                            <tr><td colspan="7" class="text-muted fst-italic py-3">No transactions in this period.</td></tr>
                        <?php else: ?>
                            <?php foreach ($transactions as $t): ?>
                                <?php
                                    $dt = !empty($t['date']) ? date('M d, Y', strtotime((string)$t['date'])) : '—';
                                    $amt = (float)($t['amount'] ?? 0);
                                ?>
                                <tr>
                                    <td><?= htmlspecialchars($dt) ?></td>
                                    <td><?= htmlspecialchars((string)($t['type'] ?? '')) ?></td>
                                    <td><?= htmlspecialchars((string)($t['note'] ?? '')) ?></td>
                                    <td><?= htmlspecialchars((string)($t['who'] ?? '')) ?></td>
                                    <td><?= htmlspecialchars((string)($t['status'] ?? '')) ?></td>
                                    <td class="text-end fw-bold <?= $amt < 0 ? 'text-danger' : 'text-success' ?>">₦<?= number_format($amt, 2) ?></td>
                                    <td class="text-end text-muted">#<?= (int)($t['id'] ?? 0) ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<?php require 'includes/footer.php'; ?>