<?php
require_once __DIR__ . '/../config/user_functions.php';

session_start();
if (!isset($_SESSION['user'])) { header('Location: ../auth/login.php'); exit; }
$role = $_SESSION['user']['role'] ?? 'viewer';
require_once __DIR__ . '/../config/db_remote.php';
include '../includes/header.php';
include '../includes/sidebar.php';

$errors = [];
$messages = [];

// Filters
$start_date = $_GET['start_date'] ?? '';
$end_date   = $_GET['end_date'] ?? '';
$grower     = trim($_GET['grower'] ?? '');
$variety    = trim($_GET['variety'] ?? '');
$lot        = trim($_GET['lot'] ?? '');
$page       = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
$per_page   = 50;

// Build WHERE
$where = ["status='DUMPED'"];
if ($start_date !== '') {
    $sd = $mysqli->real_escape_string($start_date);
    $where[] = "DATE(updated_at) >= '{$sd}'";
}
if ($end_date !== '') {
    $ed = $mysqli->real_escape_string($end_date);
    $where[] = "DATE(updated_at) <= '{$ed}'";
}
if ($grower !== '') {
    $g = $mysqli->real_escape_string($grower);
    $where[] = "grower LIKE '%{$g}%'";
}
if ($variety !== '') {
    $v = $mysqli->real_escape_string($variety);
    $where[] = "variety LIKE '%{$v}%'";
}
if ($lot !== '') {
    $l = $mysqli->real_escape_string($lot);
    $where[] = "lot LIKE '%{$l}%'";
}

$where_sql = implode(' AND ', $where);

// Handle POST actions (edit / restore)
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_POST['action']) && $_POST['action'] === 'update_bin') {
        $id       = (int)($_POST['id'] ?? 0);
        $grower_u = $mysqli->real_escape_string($_POST['grower'] ?? '');
        $variety_u= $mysqli->real_escape_string($_POST['variety'] ?? '');
        $lot_u    = $mysqli->real_escape_string($_POST['lot'] ?? '');
        $qty_u    = (int)($_POST['quantity'] ?? 0);

        if ($id > 0) {
            $mysqli->query("
                UPDATE bins_ingresso
                SET grower='{$grower_u}',
                    variety='{$variety_u}',
                    lot='{$lot_u}',
                    quantity={$qty_u},
                    updated_at = NOW()
                WHERE id={$id} AND status='DUMPED'
            ");
            if ($mysqli->affected_rows > 0) {
                $messages[] = "Bin #{$id} updated successfully.";
            } else {
                $errors[] = "No changes applied to bin #{$id}.";
            }
        }
    }

    if (isset($_POST['action']) && $_POST['action'] === 'restore_bin') {
        $id = (int)($_POST['id'] ?? 0);
        if ($id > 0) {
            $mysqli->query("
                UPDATE bins_ingresso
                SET status='AVAILABLE',
                    updated_at = NOW()
                WHERE id={$id} AND status='DUMPED'
            ");
            if ($mysqli->affected_rows > 0) {
                $messages[] = "Bin #{$id} restored to AVAILABLE.";
            } else {
                $errors[] = "Bin #{$id} could not be restored.";
            }
        }
    }

    // After POST, redirect (PRG pattern)
    $qs = $_SERVER['QUERY_STRING'] ?? '';
    header("Location: dumped_bins.php" . ($qs ? "?".$qs : ""));
    exit;
}

// CSV export
if (isset($_GET['export']) && $_GET['export'] == '1') {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="dumped_bins_export.csv"');

    $sql = "
        SELECT id, grower, variety, lot, quantity, date, updated_at
        FROM bins_ingresso
        WHERE {$where_sql}
        ORDER BY updated_at DESC
    ";
    $res = $mysqli->query($sql);

    $out = fopen('php://output', 'w');
    fputcsv($out, ['ID','Grower','Variety','Lot','Quantity','Date','Date Dumped']);

    if ($res) {
        while ($row = $res->fetch_assoc()) {
            fputcsv($out, [
                $row['id'],
                $row['grower'],
                $row['variety'],
                $row['lot'],
                $row['quantity'],
                $row['date'],
                $row['updated_at'],
            ]);
        }
    }
    fclose($out);
    exit;
}

// Pagination: count total
$count_res = $mysqli->query("SELECT COUNT(*) AS total FROM bins_ingresso WHERE {$where_sql}");
$total_rows = ($count_res && $r = $count_res->fetch_assoc()) ? (int)$r['total'] : 0;
$total_pages = max(1, (int)ceil($total_rows / $per_page));
if ($page > $total_pages) $page = $total_pages;
$offset = ($page - 1) * $per_page;

// Main query
$sql = "
    SELECT id, grower, variety, lot, quantity, date, updated_at
    FROM bins_ingresso
    WHERE {$where_sql}
    ORDER BY updated_at DESC
    LIMIT {$offset}, {$per_page}
";
$result = $mysqli->query($sql);

// Load edit bin if requested
$edit_bin = null;
if (isset($_GET['edit_id'])) {
    $eid = (int)$_GET['edit_id'];
    $res_e = $mysqli->query("
        SELECT id, grower, variety, lot, quantity, date, updated_at
        FROM bins_ingresso
        WHERE id={$eid} AND status='DUMPED'
        LIMIT 1
    ");
    if ($res_e && $res_e->num_rows === 1) {
        $edit_bin = $res_e->fetch_assoc();
    }
}
?>
<div class="container-fluid">
    <h1 class="mt-4 mb-3">Dumped Bins</h1>

    <?php if ($errors): ?>
        <div class="alert alert-danger shadow-sm">
            <ul class="mb-0">
                <?php foreach ($errors as $e): ?>
                    <li><?= htmlspecialchars($e) ?></li>
                <?php endforeach; ?>
            </ul>
        </div>
    <?php endif; ?>

    <?php if ($messages): ?>
        <div class="alert alert-success shadow-sm">
            <ul class="mb-0">
                <?php foreach ($messages as $m): ?>
                    <li><?= htmlspecialchars($m) ?></li>
                <?php endforeach; ?>
            </ul>
        </div>
    <?php endif; ?>

    <!-- Filters -->
    <form method="get" class="row g-3 mb-3">
        <div class="col-md-2">
            <label class="form-label">Start Date</label>
            <input type="date" name="start_date" class="form-control" value="<?= htmlspecialchars($start_date) ?>">
        </div>
        <div class="col-md-2">
            <label class="form-label">End Date</label>
            <input type="date" name="end_date" class="form-control" value="<?= htmlspecialchars($end_date) ?>">
        </div>
        <div class="col-md-2">
            <label class="form-label">Grower</label>
            <input type="text" name="grower" class="form-control" value="<?= htmlspecialchars($grower) ?>">
        </div>
        <div class="col-md-2">
            <label class="form-label">Variety</label>
            <input type="text" name="variety" class="form-control" value="<?= htmlspecialchars($variety) ?>">
        </div>
        <div class="col-md-2">
            <label class="form-label">Lot</label>
            <input type="text" name="lot" class="form-control" value="<?= htmlspecialchars($lot) ?>">
        </div>
        <div class="col-md-2 d-flex align-items-end justify-content-between">
            <div>
                <button type="submit" class="btn btn-primary me-2">Filter</button>
                <a href="dumped_bins.php" class="btn btn-secondary">Reset</a>
            </div>
            <button type="submit" name="export" value="1" class="btn btn-outline-success">Export CSV</button>
        </div>
    </form>

    <?php if ($edit_bin): ?>
    <!-- Edit card -->
    <div class="card mb-4 shadow-sm">
        <div class="card-header">Edit Dumped Bin #<?= $edit_bin['id'] ?></div>
        <div class="card-body">
            <form method="post">
                <input type="hidden" name="action" value="update_bin">
                <input type="hidden" name="id" value="<?= $edit_bin['id'] ?>">
                <div class="row g-3">
                    <div class="col-md-3">
                        <label class="form-label">Grower</label>
                        <input type="text" name="grower" class="form-control" value="<?= htmlspecialchars($edit_bin['grower']) ?>">
                    </div>
                    <div class="col-md-3">
                        <label class="form-label">Variety</label>
                        <input type="text" name="variety" class="form-control" value="<?= htmlspecialchars($edit_bin['variety']) ?>">
                    </div>
                    <div class="col-md-2">
                        <label class="form-label">Lot</label>
                        <input type="text" name="lot" class="form-control" value="<?= htmlspecialchars($edit_bin['lot']) ?>">
                    </div>
                    <div class="col-md-2">
                        <label class="form-label">Quantity</label>
                        <input type="number" name="quantity" class="form-control" value="<?= (int)$edit_bin['quantity'] ?>">
                    </div>
                    <div class="col-md-2 d-flex align-items-end">
                        <button type="submit" class="btn btn-primary me-2">Save</button>
                        <a href="dumped_bins.php" class="btn btn-secondary">Cancel</a>
                    </div>
                </div>
            </form>
        </div>
    </div>
    <?php endif; ?>

    <!-- Table -->
    <div class="card shadow-sm">
        <div class="card-header d-flex justify-content-between align-items-center">
            <span>Dumped Bins List</span>
            <span class="text-muted small"><?= $total_rows ?> record(s) found</span>
        </div>
        <div class="card-body">
            <div class="table-responsive">
                <table class="table table-sm table-bordered align-middle">
                    <thead class="table-light">
                        <tr>
                            <th>ID</th>
                            <th>Grower</th>
                            <th>Variety</th>
                            <th>Lot</th>
                            <th>Quantity</th>
                            <th>Date</th>
                            <th>Date Dumped</th>
                            <th style="width:150px;">Actions</th>
                        </tr>
                    </thead>
                    <tbody>
                    <?php if ($result && $result->num_rows > 0): ?>
                        <?php while($row = $result->fetch_assoc()): ?>
                        <tr>
                            <td><?= $row['id'] ?></td>
                            <td><?= htmlspecialchars($row['grower']) ?></td>
                            <td><?= htmlspecialchars($row['variety']) ?></td>
                            <td><?= htmlspecialchars($row['lot']) ?></td>
                            <td><?= (int)$row['quantity'] ?></td>
                            <td><?= htmlspecialchars($row['date']) ?></td>
                            <td><?= htmlspecialchars($row['updated_at']) ?></td>
                            <td>
                                <a href="dumped_bins.php?<?=
                                    http_build_query(array_merge($_GET, ['edit_id'=>$row['id']]))
                                ?>" class="btn btn-sm btn-outline-primary mb-1">Edit</a>
                                <form method="post" style="display:inline-block"
                                      onsubmit="return confirm('Restore bin #<?= $row['id'] ?> to AVAILABLE?');">
                                    <input type="hidden" name="action" value="restore_bin">
                                    <input type="hidden" name="id" value="<?= $row['id'] ?>">
                                    <button type="submit" class="btn btn-sm btn-outline-warning">Restore</button>
                                </form>
                            </td>
                        </tr>
                        <?php endwhile; ?>
                    <?php else: ?>
                        <tr>
                            <td colspan="8" class="text-center text-muted">No dumped bins found.</td>
                        </tr>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>

            <!-- Pagination -->
            <?php if ($total_pages > 1): ?>
            <nav>
              <ul class="pagination pagination-sm mb-0">
                <?php for ($p=1; $p<=$total_pages; $p++): ?>
                  <?php
                    $qs = $_GET;
                    $qs['page'] = $p;
                    $url = 'dumped_bins.php?'.http_build_query($qs);
                  ?>
                  <li class="page-item <?= $p==$page ? 'active' : '' ?>">
                    <a class="page-link" href="<?= $url ?>"><?= $p ?></a>
                  </li>
                <?php endfor; ?>
              </ul>
            </nav>
            <?php endif; ?>
        </div>
    </div>
</div>

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