<?php
// public/products.php
require_once __DIR__ . '/../helpers/auth.php';
require_once __DIR__ . '/../helpers/audit.php';

require_role(['admin', 'staff']);

$pdo = db();
$title = "Products - IMS";

// ---------------------------
// Helpers
// ---------------------------
function post_str(string $key): string { return trim((string)($_POST[$key] ?? '')); }
function post_int(string $key): int { return (int)($_POST[$key] ?? 0); }
function post_float(string $key): float {
    $val = trim((string)($_POST[$key] ?? '0'));
    return (float)$val;
}

function generate_sku_from_id(int $id): string {
    // Format: PRD-000001
    return 'PRD-' . str_pad((string)$id, 6, '0', STR_PAD_LEFT);
}

// ---------------------------
// Filters
// ---------------------------
$q = trim((string)($_GET['q'] ?? ''));
$supplier_filter = (int)($_GET['supplier_id'] ?? 0);

// ---------------------------
// Fetch categories + suppliers (dropdowns)
// ---------------------------
$categories = $pdo->query("SELECT id, name FROM product_categories ORDER BY name ASC")->fetchAll();

$suppliers = $pdo->query("
  SELECT id, supplier_code, name
  FROM suppliers
  WHERE is_active = 1
  ORDER BY name ASC
")->fetchAll();

// ---------------------------
// Handle Actions (ADD/EDIT/DELETE)
// ---------------------------
$action = $_POST['action'] ?? '';
$error = '';
$success = '';
$created_product_id = 0;
$created_product_sku = '';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {

    if ($action === 'add') {
        $auto_sku = isset($_POST['auto_sku']) ? 1 : 0;

        $sku_input = post_str('sku');
        $name = post_str('name');
        $category_id = post_int('category_id') ?: null;
        $supplier_id = post_int('supplier_id') ?: null;

        $unit = post_str('unit') ?: 'pcs';
        $cost_price = post_float('cost_price');
        $sell_price = post_float('sell_price');
        $reorder_level = post_int('reorder_level');
        $description = post_str('description');

        if ($name === '') {
            $error = "Product Name is required.";
        } else {
            // If auto SKU, ignore typed SKU.
            $sku_to_use = $auto_sku ? '' : $sku_input;

            try {
                $pdo->beginTransaction();

                // SKU might be required in schema; insert a temporary unique SKU first if auto.
                if ($sku_to_use === '') {
                    $sku_to_use = 'TMP-' . bin2hex(random_bytes(6));
                }

                $stmt = $pdo->prepare("
                    INSERT INTO products (category_id, supplier_id, sku, name, description, unit, cost_price, sell_price, reorder_level)
                    VALUES (:category_id, :supplier_id, :sku, :name, :description, :unit, :cost_price, :sell_price, :reorder_level)
                ");
                $stmt->execute([
                    ':category_id' => $category_id,
                    ':supplier_id' => $supplier_id,
                    ':sku' => $sku_to_use,
                    ':name' => $name,
                    ':description' => $description !== '' ? $description : null,
                    ':unit' => $unit,
                    ':cost_price' => $cost_price,
                    ':sell_price' => $sell_price,
                    ':reorder_level' => $reorder_level,
                ]);

                $new_id = (int)$pdo->lastInsertId();

                // If auto SKU OR user left SKU blank, set SKU based on ID.
                if ($auto_sku || $sku_input === '') {
                    $final_sku = generate_sku_from_id($new_id);

                    // If somehow exists, add suffix.
                    $try = 0;
                    while (true) {
                        $check = $pdo->prepare("SELECT COUNT(*) FROM products WHERE sku = :sku AND id != :id");
                        $check->execute([':sku' => $final_sku, ':id' => $new_id]);
                        $exists = (int)$check->fetchColumn() > 0;
                        if (!$exists) break;
                        $try++;
                        $final_sku = generate_sku_from_id($new_id) . '-' . $try;
                    }

                    $upd = $pdo->prepare("UPDATE products SET sku = :sku WHERE id = :id LIMIT 1");
                    $upd->execute([':sku' => $final_sku, ':id' => $new_id]);

                    $created_product_sku = $final_sku;
                } else {
                    $created_product_sku = $sku_to_use;
                }

                $pdo->commit();

                $created_product_id = $new_id;
                $success = "Product added successfully. SKU: " . $created_product_sku;

                audit_log(
                    'CREATE_PRODUCT',
                    'products',
                    (string)$new_id,
                    json_encode(['sku' => $created_product_sku, 'name' => $name])
                );

            } catch (Throwable $e) {
                if ($pdo->inTransaction()) $pdo->rollBack();
                $error = "Error adding product: " . $e->getMessage();
            }
        }
    }

    if ($action === 'edit') {
        $id = post_int('id');
        $sku = post_str('sku');
        $name = post_str('name');
        $category_id = post_int('category_id') ?: null;
        $supplier_id = post_int('supplier_id') ?: null;

        $unit = post_str('unit') ?: 'pcs';
        $cost_price = post_float('cost_price');
        $sell_price = post_float('sell_price');
        $reorder_level = post_int('reorder_level');
        $description = post_str('description');
        $is_active = isset($_POST['is_active']) ? 1 : 0;

        if ($id <= 0 || $sku === '' || $name === '') {
            $error = "Invalid product data. SKU and Product Name are required.";
        } else {
            try {
                $stmt = $pdo->prepare("
                    UPDATE products
                    SET category_id = :category_id,
                        supplier_id = :supplier_id,
                        sku = :sku,
                        name = :name,
                        description = :description,
                        unit = :unit,
                        cost_price = :cost_price,
                        sell_price = :sell_price,
                        reorder_level = :reorder_level,
                        is_active = :is_active
                    WHERE id = :id
                    LIMIT 1
                ");
                $stmt->execute([
                    ':category_id' => $category_id,
                    ':supplier_id' => $supplier_id,
                    ':sku' => $sku,
                    ':name' => $name,
                    ':description' => $description !== '' ? $description : null,
                    ':unit' => $unit,
                    ':cost_price' => $cost_price,
                    ':sell_price' => $sell_price,
                    ':reorder_level' => $reorder_level,
                    ':is_active' => $is_active,
                    ':id' => $id,
                ]);
                $success = "Product updated successfully.";

                audit_log(
                    'UPDATE_PRODUCT',
                    'products',
                    (string)$id,
                    json_encode(['sku' => $sku, 'name' => $name])
                );

            } catch (PDOException $e) {
                $error = "Error updating product: " . $e->getMessage();
            }
        }
    }

    if ($action === 'delete') {
        $id = post_int('id');
        if ($id <= 0) {
            $error = "Invalid product ID.";
        } else {
            $stmt = $pdo->prepare("SELECT sku, name FROM products WHERE id = :id LIMIT 1");
            $stmt->execute([':id' => $id]);
            $toDel = $stmt->fetch();

            try {
                $stmt = $pdo->prepare("DELETE FROM products WHERE id = :id LIMIT 1");
                $stmt->execute([':id' => $id]);
                $success = "Product deleted successfully.";

                audit_log(
                    'DELETE_PRODUCT',
                    'products',
                    (string)$id,
                    json_encode([
                        'sku' => (string)($toDel['sku'] ?? ''),
                        'name' => (string)($toDel['name'] ?? ''),
                    ])
                );

            } catch (PDOException $e) {
                $error = "Error deleting product: " . $e->getMessage();
            }
        }
    }
}

// ---------------------------
// Edit mode (GET)
// ---------------------------
$edit_id = (int)($_GET['edit'] ?? 0);
$edit_product = null;

if ($edit_id > 0) {
    $stmt = $pdo->prepare("SELECT * FROM products WHERE id = :id LIMIT 1");
    $stmt->execute([':id' => $edit_id]);
    $edit_product = $stmt->fetch();
    if (!$edit_product) {
        $error = "Product not found.";
        $edit_id = 0;
    }
}

// ---------------------------
// Fetch product list + supplier + current stock
// ---------------------------
$sql = "
  SELECT
    p.*,
    c.name AS category_name,
    s.name AS supplier_name,
    s.supplier_code,

    (
      COALESCE(SUM(CASE WHEN m.movement_type='IN' THEN m.quantity ELSE 0 END),0)
      - COALESCE(SUM(CASE WHEN m.movement_type='OUT' THEN m.quantity ELSE 0 END),0)
      + COALESCE(SUM(CASE WHEN m.movement_type='ADJUST' THEN m.quantity ELSE 0 END),0)
    ) AS current_stock

  FROM products p
  LEFT JOIN product_categories c ON c.id = p.category_id
  LEFT JOIN suppliers s ON s.id = p.supplier_id
  LEFT JOIN inventory_movements m ON m.product_id = p.id
  WHERE 1=1
";

$params = [];

if ($q !== '') {
    $sql .= " AND (p.sku LIKE :q OR p.name LIKE :q) ";
    $params[':q'] = '%' . $q . '%';
}

if ($supplier_filter > 0) {
    $sql .= " AND p.supplier_id = :sid ";
    $params[':sid'] = $supplier_filter;
}

$sql .= "
  GROUP BY p.id
  ORDER BY p.created_at DESC
";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$products = $stmt->fetchAll();

require_once __DIR__ . '/../templates/header.php';
require_once __DIR__ . '/../templates/sidebar.php';
?>

<main class="col-12 col-md-9 col-lg-10 p-3 p-md-4">

  <div class="d-flex align-items-center justify-content-between mb-3">
    <h4 class="mb-0">Products</h4>
  </div>

  <?php if ($error !== ''): ?>
    <div class="alert alert-danger"><?= htmlspecialchars($error) ?></div>
  <?php endif; ?>

  <?php if ($success !== ''): ?>
    <div class="alert alert-success">
      <?= htmlspecialchars($success) ?>
      <?php if ($created_product_id > 0 && $created_product_sku !== ''): ?>
        <div class="mt-2">
          <a class="btn btn-dark btn-sm" target="_blank" href="print_label.php?id=<?= (int)$created_product_id ?>">
            Print Label
          </a>
          <div class="small text-muted mt-1">
            Print this label and paste it on the product. This label uses a QR code (best for phone camera scanning).
          </div>
        </div>
      <?php endif; ?>
    </div>
  <?php endif; ?>

  <!-- Filters -->
  <div class="card shadow-sm mb-3">
    <div class="card-body">
      <form class="row g-2" method="GET">
        <div class="col-12 col-md-5">
          <input class="form-control" name="q" value="<?= htmlspecialchars($q) ?>" placeholder="Search SKU or Name">
        </div>
        <div class="col-12 col-md-4">
          <select class="form-select" name="supplier_id">
            <option value="0">All Suppliers</option>
            <?php foreach ($suppliers as $s): ?>
              <option value="<?= (int)$s['id'] ?>" <?= $supplier_filter === (int)$s['id'] ? 'selected' : '' ?>>
                <?= htmlspecialchars($s['name']) ?>
              </option>
            <?php endforeach; ?>
          </select>
        </div>
        <div class="col-12 col-md-3">
          <button class="btn btn-primary w-100" type="submit">Filter</button>
        </div>
      </form>
    </div>
  </div>

  <div class="row g-3">
    <!-- Form -->
    <div class="col-12 col-lg-4">
      <div class="card shadow-sm">
        <div class="card-body">
          <h5 class="card-title mb-3"><?= $edit_id > 0 ? 'Edit Product' : 'Add Product' ?></h5>

          <form method="POST">
            <input type="hidden" name="action" value="<?= $edit_id > 0 ? 'edit' : 'add' ?>">
            <?php if ($edit_id > 0): ?>
              <input type="hidden" name="id" value="<?= (int)$edit_product['id'] ?>">
            <?php endif; ?>

            <?php if ($edit_id <= 0): ?>
              <div class="form-check mb-2">
                <input class="form-check-input" type="checkbox" name="auto_sku" id="auto_sku" checked>
                <label class="form-check-label" for="auto_sku">Auto-generate SKU + QR Code</label>
              </div>
            <?php endif; ?>

            <div class="mb-2">
              <label class="form-label">SKU <?= $edit_id > 0 ? '*' : '(optional if auto)' ?></label>
              <input class="form-control" name="sku"
                     <?= $edit_id > 0 ? 'required' : '' ?>
                     placeholder="<?= $edit_id > 0 ? '' : 'Leave empty to auto-generate' ?>"
                     value="<?= htmlspecialchars($edit_product['sku'] ?? '') ?>">
              <?php if ($edit_id <= 0): ?>
                <div class="form-text">If Auto-generate is checked, this value will be ignored.</div>
              <?php endif; ?>
            </div>

            <div class="mb-2">
              <label class="form-label">Product Name *</label>
              <input class="form-control" name="name" required
                     value="<?= htmlspecialchars($edit_product['name'] ?? '') ?>">
            </div>

            <div class="mb-2">
              <label class="form-label">Supplier</label>
              <select class="form-select" name="supplier_id">
                <option value="0">-- None --</option>
                <?php foreach ($suppliers as $s): ?>
                  <?php $selected = ($edit_product && (int)$edit_product['supplier_id'] === (int)$s['id']) ? 'selected' : ''; ?>
                  <option value="<?= (int)$s['id'] ?>" <?= $selected ?>>
                    <?= htmlspecialchars($s['name']) ?><?= $s['supplier_code'] ? ' (' . htmlspecialchars($s['supplier_code']) . ')' : '' ?>
                  </option>
                <?php endforeach; ?>
              </select>
            </div>

            <div class="mb-2">
              <label class="form-label">Category</label>
              <select class="form-select" name="category_id">
                <option value="0">-- None --</option>
                <?php foreach ($categories as $c): ?>
                  <?php $selected = ($edit_product && (int)$edit_product['category_id'] === (int)$c['id']) ? 'selected' : ''; ?>
                  <option value="<?= (int)$c['id'] ?>" <?= $selected ?>>
                    <?= htmlspecialchars($c['name']) ?>
                  </option>
                <?php endforeach; ?>
              </select>
            </div>

            <div class="mb-2">
              <label class="form-label">Unit</label>
              <input class="form-control" name="unit" placeholder="pcs, box, kg..."
                     value="<?= htmlspecialchars($edit_product['unit'] ?? 'pcs') ?>">
            </div>

            <div class="row g-2">
              <div class="col-6">
                <div class="mb-2">
                  <label class="form-label">Cost Price</label>
                  <input class="form-control" name="cost_price" type="number" step="0.01" min="0"
                         value="<?= htmlspecialchars((string)($edit_product['cost_price'] ?? '0.00')) ?>">
                </div>
              </div>
              <div class="col-6">
                <div class="mb-2">
                  <label class="form-label">Sell Price</label>
                  <input class="form-control" name="sell_price" type="number" step="0.01" min="0"
                         value="<?= htmlspecialchars((string)($edit_product['sell_price'] ?? '0.00')) ?>">
                </div>
              </div>
            </div>

            <div class="mb-2">
              <label class="form-label">Reorder Level</label>
              <input class="form-control" name="reorder_level" type="number" step="1" min="0"
                     value="<?= htmlspecialchars((string)($edit_product['reorder_level'] ?? '0')) ?>">
            </div>

            <div class="mb-2">
              <label class="form-label">Description</label>
              <textarea class="form-control" name="description" rows="3"><?= htmlspecialchars($edit_product['description'] ?? '') ?></textarea>
            </div>

            <?php if ($edit_id > 0): ?>
              <div class="form-check mb-3">
                <?php $checked = ((int)($edit_product['is_active'] ?? 1) === 1) ? 'checked' : ''; ?>
                <input class="form-check-input" type="checkbox" name="is_active" id="is_active" <?= $checked ?>>
                <label class="form-check-label" for="is_active">Active</label>
              </div>

              <a class="btn btn-dark w-100 mb-2" target="_blank" href="print_label.php?id=<?= (int)$edit_product['id'] ?>">
                Print Label
              </a>
            <?php endif; ?>

            <button class="btn btn-primary w-100" type="submit">
              <?= $edit_id > 0 ? 'Save Changes' : 'Add Product' ?>
            </button>

            <?php if ($edit_id > 0): ?>
              <a class="btn btn-outline-secondary w-100 mt-2" href="products.php">Cancel</a>
            <?php endif; ?>
          </form>
        </div>
      </div>
    </div>

    <!-- Table -->
    <div class="col-12 col-lg-8">
      <div class="card shadow-sm">
        <div class="card-body">
          <div class="table-responsive">
            <table class="table table-sm align-middle">
              <thead>
                <tr>
                  <th>SKU</th>
                  <th>Name</th>
                  <th>Supplier</th>
                  <th>Category</th>
                  <th class="text-end">Stock</th>
                  <th class="text-end">Sell</th>
                  <th class="text-center">Active</th>
                  <th style="width:280px;"></th>
                </tr>
              </thead>
              <tbody>
                <?php if (!$products): ?>
                  <tr><td colspan="8" class="text-muted">No products found.</td></tr>
                <?php else: ?>
                  <?php foreach ($products as $p): ?>
                    <?php
                      $stock = (int)($p['current_stock'] ?? 0);
                      $reorder = (int)($p['reorder_level'] ?? 0);
                      $low = ($reorder > 0 && $stock <= $reorder);
                    ?>
                    <tr class="<?= $low ? 'table-warning' : '' ?>">
                      <td><?= htmlspecialchars($p['sku']) ?></td>
                      <td><?= htmlspecialchars($p['name']) ?></td>

                      <td>
                        <?php if (!empty($p['supplier_name'])): ?>
                          <a href="supplier_view.php?id=<?= (int)$p['supplier_id'] ?>">
                            <?= htmlspecialchars($p['supplier_name']) ?>
                          </a>
                        <?php else: ?>
                          <span class="text-muted">-</span>
                        <?php endif; ?>
                      </td>

                      <td><?= htmlspecialchars($p['category_name'] ?? '-') ?></td>
                      <td class="text-end fw-semibold"><?= $stock ?></td>
                      <td class="text-end"><?= number_format((float)$p['sell_price'], 2) ?></td>
                      <td class="text-center"><?= ((int)$p['is_active'] === 1) ? '✅' : '❌' ?></td>

                      <td class="text-end">
                        <a class="btn btn-dark btn-sm" target="_blank" href="print_label.php?id=<?= (int)$p['id'] ?>">QR Label</a>
                        <a class="btn btn-outline-dark btn-sm" href="product_stock_history.php?product_id=<?= (int)$p['id'] ?>">History</a>
                        <a class="btn btn-outline-primary btn-sm" href="products.php?edit=<?= (int)$p['id'] ?>">Edit</a>
                        <form method="POST" class="d-inline" onsubmit="return confirm('Delete this product?');">
                          <input type="hidden" name="action" value="delete">
                          <input type="hidden" name="id" value="<?= (int)$p['id'] ?>">
                          <button class="btn btn-outline-danger btn-sm" type="submit">Delete</button>
                        </form>
                      </td>
                    </tr>
                  <?php endforeach; ?>
                <?php endif; ?>
              </tbody>
            </table>
          </div>

          <div class="text-muted small">
            Highlighted rows = stock at/under reorder level.
          </div>
        </div>
      </div>
    </div>
  </div>
</main>

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