<?php
require_once __DIR__ . '/../../../includes/auth.php';
requireLogin();

require_once __DIR__ . '/../../../config/database.php';

/* ===============================
   FILTER + SEARCH
================================= */
$filter = $_GET['status'] ?? 'ALL';
$customer = trim($_GET['customer'] ?? '');
$account  = trim($_GET['account'] ?? '');
$unit     = trim($_GET['unit'] ?? '');
$dateFrom = $_GET['date_from'] ?? '';
$dateTo   = $_GET['date_to'] ?? '';
$search = trim($_GET['search'] ?? '');

/* ===============================
   LOAD INVOICES
================================= */
$query = "
    SELECT i.*, ba.account_name, cu.customer_name,
    (
        SELECT COALESCE(SUM(p.amount),0)
        FROM invoice_payments p
        WHERE p.invoice_id = i.id
    ) AS total_paid
FROM invoices i
LEFT JOIN billing_accounts ba ON ba.id = i.billing_account_id
LEFT JOIN billing_customers cu ON cu.id = ba.customer_id
";

$params = [];
$where = [];

/* STATUS FILTER */
if ($filter === 'ISSUED') {
    $where[] = "i.status = 'ISSUED'";
}
elseif ($filter === 'PAID') {
    $where[] = "i.status = 'PAID'";
}
elseif ($filter === 'OVERDUE') {
    $where[] = "i.status != 'PAID' AND i.due_date < CURDATE()";
}

/* CUSTOMER FILTER */
if ($customer !== '') {
    $where[] = "cu.customer_name = ?";
    $params[] = $customer;
}

/* ACCOUNT FILTER */
if ($account !== '') {
    $where[] = "ba.account_name = ?";
    $params[] = $account;
}

/* UNIT FILTER */
if ($unit !== '') {
    $where[] = "i.unit_name = ?";
    $params[] = $unit;
}

/* DATE RANGE FILTER */
if ($dateFrom !== '') {
    $where[] = "i.invoice_date >= ?";
    $params[] = $dateFrom;
}

if ($dateTo !== '') {
    $where[] = "i.invoice_date <= ?";
    $params[] = $dateTo;
}

/* SEARCH FILTER */
if ($search !== '') {
    $where[] = "(i.invoice_number LIKE ? OR cu.customer_name LIKE ?)";
    $params[] = "%$search%";
    $params[] = "%$search%";
}

/* APPLY WHERE */
if (!empty($where)) {
    $query .= " WHERE " . implode(" AND ", $where);
}

$query .= " ORDER BY i.invoice_date DESC";

$stmt = $pdo->prepare($query);
$stmt->execute($params);
$invoices = $stmt->fetchAll(PDO::FETCH_ASSOC);
/* ===============================
   LOAD FILTER DATA
================================= */

/* CUSTOMERS */
$customers = $pdo->query("
SELECT DISTINCT cu.customer_name
FROM billing_customers cu
ORDER BY cu.customer_name ASC
")->fetchAll(PDO::FETCH_COLUMN);

/* ACCOUNTS */
$accountsList = $pdo->query("
SELECT DISTINCT ba.account_name
FROM billing_accounts ba
ORDER BY ba.account_name ASC
")->fetchAll(PDO::FETCH_COLUMN);

/* UNITS */
$unitsList = $pdo->query("
SELECT DISTINCT unit_name
FROM invoices
WHERE unit_name IS NOT NULL AND unit_name != ''
ORDER BY unit_name ASC
")->fetchAll(PDO::FETCH_COLUMN);
?>

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

<div class="main-content">

    <div class="page-header">
        <h2>Invoices</h2>
    </div>

    <!-- SEARCH + FILTER -->
    <form method="GET" style="margin-bottom:15px; display:flex; gap:10px; flex-wrap:wrap;">

    <!-- SEARCH -->
    <input type="text" name="search"
        placeholder="Search invoice / customer..."
        value="<?= htmlspecialchars($search) ?>"
        style="padding:8px; min-width:200px;">

    <!-- STATUS -->
    <select name="status" style="padding:8px;">
        <option value="ALL" <?= $filter=='ALL'?'selected':'' ?>>All</option>
        <option value="ISSUED" <?= $filter=='ISSUED'?'selected':'' ?>>Issued</option>
        <option value="PAID" <?= $filter=='PAID'?'selected':'' ?>>Paid</option>
        <option value="OVERDUE" <?= $filter=='OVERDUE'?'selected':'' ?>>Overdue</option>
    </select>

   <!-- CUSTOMER -->
<select name="customer" style="padding:8px;">
    <option value="">All Customers</option>
    <?php foreach($customers as $c): ?>
        <option value="<?= htmlspecialchars($c) ?>" <?= $customer==$c?'selected':'' ?>>
            <?= htmlspecialchars($c) ?>
        </option>
    <?php endforeach; ?>
</select>

<!-- ACCOUNT -->
<select name="account" style="padding:8px;">
    <option value="">All Accounts</option>
    <?php foreach($accountsList as $a): ?>
        <option value="<?= htmlspecialchars($a) ?>" <?= $account==$a?'selected':'' ?>>
            <?= htmlspecialchars($a) ?>
        </option>
    <?php endforeach; ?>
</select>

<!-- UNIT -->
<select name="unit" style="padding:8px;">
    <option value="">All Units</option>
    <?php foreach($unitsList as $u): ?>
        <option value="<?= htmlspecialchars($u) ?>" <?= $unit==$u?'selected':'' ?>>
            <?= htmlspecialchars($u) ?>
        </option>
    <?php endforeach; ?>
</select>

    <!-- DATE RANGE -->
    <input type="date" name="date_from"
        value="<?= htmlspecialchars($dateFrom) ?>"
        style="padding:8px;">

    <input type="date" name="date_to"
        value="<?= htmlspecialchars($dateTo) ?>"
        style="padding:8px;">

    <button type="submit" class="btn btn-primary">Search</button>

</form>
    <!-- QUICK FILTER TABS -->
    <div style="margin-bottom:20px; display:flex; gap:10px; flex-wrap:wrap;">
        <a href="?status=ALL" class="btn">All</a>
        <a href="?status=ISSUED" class="btn">Issued</a>
        <a href="?status=PAID" class="btn">Paid</a>
        <a href="?status=OVERDUE" class="btn">Overdue</a>
    </div>

    <div class="table-wrap">
        <table>
            <thead>
                <tr>
                    <th>Invoice #</th>
                    <th>Customer</th>
                    <th>Account</th>
<th>Unit</th>
<th>Date</th>
                    <th>Due</th>
                    <th style="text-align:right;">Total</th>
                    <th style="text-align:right;">Paid</th>
                    <th style="text-align:right;">Balance</th>
                    <th>Status</th>
                    <th></th>
                </tr>
            </thead>
            <tbody>

            <?php foreach ($invoices as $inv):

                $balance = $inv['total'] - $inv['total_paid'];

                $status = $inv['status'];
                if ($status !== 'PAID' && $balance > 0 && strtotime($inv['due_date']) < time()) {
                    $status = 'OVERDUE';
                }

                $color = 'black';
                if ($status === 'ISSUED') $color = '#f39c12';
                elseif ($status === 'PAID') $color = 'green';
                elseif ($status === 'OVERDUE') $color = '#c0392b';
                elseif ($status === 'CANCELLED') $color = 'red';

            ?>

                <tr>
                    <td><?= htmlspecialchars((string)($inv['invoice_number'] ?? '')) ?></td>
                    <td><?= htmlspecialchars((string)($inv['customer_name'] ?? '-')) ?></td>
        <td><?= htmlspecialchars((string)($inv['account_name'] ?? '-')) ?></td>

<td>
<?= !empty($inv['unit_name']) 
    ? htmlspecialchars((string)$inv['unit_name']) 
    : '-' ?>
</td>

<td><?= htmlspecialchars((string)($inv['invoice_date'] ?? '')) ?></td>
                    <td><?= htmlspecialchars((string)($inv['due_date'] ?? '')) ?></td>

                    <td style="text-align:right;">
                        R <?= number_format($inv['total'], 2) ?>
                    </td>

                    <td style="text-align:right;">
                        R <?= number_format($inv['total_paid'], 2) ?>
                    </td>

                    <td style="text-align:right; color:<?= $balance > 0 ? 'red' : 'green' ?>;">
                        R <?= number_format($balance, 2) ?>
                    </td>

                    <td style="color:<?= $color ?>; font-weight:bold;">
                        <?= $status ?>
                    </td>

                    <td>
                        <a href="invoice_view.php?id=<?= $inv['id'] ?>" class="btn btn-primary">
                            View
                        </a>
                    </td>
                </tr>

            <?php endforeach; ?>

            </tbody>
        </table>
    </div>

</div>

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