<?php include 'db.php'; ?>
<!DOCTYPE html>
<html>
<head>
    <title>Projects & Expenses</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css">
</head>
<body class="p-4">

<div class="container">
    <h2>Projects</h2>
    <button class="btn btn-primary mb-3" data-bs-toggle="modal" data-bs-target="#addProjectModal">Add Project</button>

    <table class="table table-bordered">
        <thead>
            <tr>
                <th>Project</th>
                <th>Client</th>
                <th>PO Value</th>
                <th>Total Expenses</th>
                <th>Margin</th>
                <th>Actions</th>
            </tr>
        </thead>
        <tbody>
        <?php
$sql = "SELECT p.*, 
               IFNULL(SUM(e.amount),0) as total_expenses,
               (SELECT expense_name 
                FROM project_expenses 
                WHERE project_id = p.id 
                ORDER BY id DESC LIMIT 1) as last_expense
        FROM projects p 
        LEFT JOIN project_expenses e ON p.id = e.project_id 
        GROUP BY p.id";

$result = $conn->query($sql);

if ($result && $result->num_rows > 0) {
    while($row = $result->fetch_assoc()){
        $margin = $row['project_po'] - $row['total_expenses'];
        echo "<tr>
            <td>{$row['project_name']}</td>
            <td>{$row['last_expense']}</td>
            <td>{$row['project_po']}</td>
            <td>{$row['total_expenses']}</td>
            <td style='color:" . ($margin<0?'red':'green') . "'>{$margin}</td>
            <td>
                <a href='project_expenses.php?id={$row['id']}' class='btn btn-sm btn-warning'>Add Expenses</a>
            </td>
        </tr>";
    }
} else {
    echo "<tr><td colspan='6'>No projects found</td></tr>";
}
        ?>
        </tbody>
    </table>
</div>

<!-- Add Project Modal -->
<div class="modal fade" id="addProjectModal" tabindex="-1">
  <div class="modal-dialog">
    <form method="POST" action="save_project.php" class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title">Add Project</h5>
        <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
      </div>
      <div class="modal-body">
          <input type="text" name="project_name" class="form-control mb-2" placeholder="Project Name" required>
          <input type="text" name="client_name" class="form-control mb-2" placeholder="Client Name">
          <input type="number" step="0.01" name="project_po" class="form-control mb-2" placeholder="PO Value" required>
      </div>
      <div class="modal-footer">
        <button type="submit" class="btn btn-success">Save</button>
      </div>
    </form>
  </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>