<?php
// public/product_categories.php
require_once __DIR__ . '/../helpers/auth.php';
require_role(['admin', 'staff']);

$pdo = db();
$title = 'Product Categories - IMS';

function h(?string $value): string
{
    return htmlspecialchars((string)($value ?? ''), ENT_QUOTES, 'UTF-8');
}
function post_str(string $k): string
{
    return trim((string)($_POST[$k] ?? ''));
}
function post_int(string $k): int
{
    return (int)($_POST[$k] ?? 0);
}
function category_hierarchy_ready(PDO $pdo): bool
{
    $stmt = $pdo->query("SHOW COLUMNS FROM product_categories LIKE 'parent_id'");
    return (bool)$stmt->fetch(PDO::FETCH_ASSOC);
}
function load_category_options(PDO $pdo, bool $hierarchyReady): array
{
    if ($hierarchyReady) {
        return $pdo->query("\n            SELECT\n                c.id,\n                c.name,\n                c.parent_id,\n                p.name AS parent_name\n            FROM product_categories c\n            LEFT JOIN product_categories p ON p.id = c.parent_id\n            ORDER BY\n                COALESCE(p.name, c.name) ASC,\n                CASE WHEN c.parent_id IS NULL THEN 0 ELSE 1 END ASC,\n                c.name ASC\n        ")->fetchAll(PDO::FETCH_ASSOC);
    }

    return $pdo->query("SELECT id, name, NULL AS parent_id, NULL AS parent_name FROM product_categories ORDER BY name ASC")
        ->fetchAll(PDO::FETCH_ASSOC);
}
function category_display_name(array $category): string
{
    $name = (string)($category['name'] ?? '');
    $parentName = trim((string)($category['parent_name'] ?? ''));
    if ($parentName !== '') {
        return $parentName . ' → ' . $name;
    }
    return $name;
}

$hierarchyReady = category_hierarchy_ready($pdo);
$error = '';
$success = '';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $action = $_POST['action'] ?? '';

    if ($action === 'add') {
        $name = post_str('name');
        $parentId = $hierarchyReady ? post_int('parent_id') : 0;
        $parentId = $parentId > 0 ? $parentId : null;

        if ($name === '') {
            $error = 'Category name is required.';
        } elseif ($parentId !== null) {
            $check = $pdo->prepare('SELECT id FROM product_categories WHERE id = :id LIMIT 1');
            $check->execute([':id' => $parentId]);
            if (!$check->fetchColumn()) {
                $error = 'Selected parent category was not found.';
            }
        }

        if ($error === '') {
            try {
                if ($hierarchyReady) {
                    $stmt = $pdo->prepare('INSERT INTO product_categories (name, parent_id) VALUES (:name, :parent_id)');
                    $stmt->execute([':name' => $name, ':parent_id' => $parentId]);
                } else {
                    $stmt = $pdo->prepare('INSERT INTO product_categories (name) VALUES (:name)');
                    $stmt->execute([':name' => $name]);
                }
                $success = 'Category added.';
            } catch (PDOException $e) {
                $error = 'Error adding category: ' . $e->getMessage();
            }
        }
    }

    if ($action === 'edit') {
        $id = post_int('id');
        $name = post_str('name');
        $parentId = $hierarchyReady ? post_int('parent_id') : 0;
        $parentId = $parentId > 0 ? $parentId : null;

        if ($id <= 0 || $name === '') {
            $error = 'Invalid category data.';
        } elseif ($hierarchyReady && $parentId !== null && $parentId === $id) {
            $error = 'A category cannot be its own parent.';
        } elseif ($hierarchyReady && $parentId !== null) {
            $check = $pdo->prepare('SELECT id, parent_id FROM product_categories WHERE id = :id LIMIT 1');
            $check->execute([':id' => $parentId]);
            $parentRow = $check->fetch(PDO::FETCH_ASSOC);
            if (!$parentRow) {
                $error = 'Selected parent category was not found.';
            } elseif ((int)($parentRow['parent_id'] ?? 0) === $id) {
                $error = 'Only one parent-child level is allowed.';
            }
        }

        if ($error === '') {
            try {
                if ($hierarchyReady) {
                    $stmt = $pdo->prepare('UPDATE product_categories SET name = :name, parent_id = :parent_id WHERE id = :id LIMIT 1');
                    $stmt->execute([':name' => $name, ':parent_id' => $parentId, ':id' => $id]);
                } else {
                    $stmt = $pdo->prepare('UPDATE product_categories SET name = :name WHERE id = :id LIMIT 1');
                    $stmt->execute([':name' => $name, ':id' => $id]);
                }
                $success = 'Category updated.';
            } catch (PDOException $e) {
                $error = 'Error updating category: ' . $e->getMessage();
            }
        }
    }

    if ($action === 'delete') {
        $id = post_int('id');
        if ($id <= 0) {
            $error = 'Invalid category ID.';
        } else {
            try {
                if ($hierarchyReady) {
                    $pdo->prepare('UPDATE product_categories SET parent_id = NULL WHERE parent_id = :id')->execute([':id' => $id]);
                }
                $stmt = $pdo->prepare('DELETE FROM product_categories WHERE id = :id LIMIT 1');
                $stmt->execute([':id' => $id]);
                $success = "Category deleted (products in this category are now 'None').";
            } catch (PDOException $e) {
                $error = 'Error deleting category: ' . $e->getMessage();
            }
        }
    }
}

$editId = (int)($_GET['edit'] ?? 0);
$edit = null;
if ($editId > 0) {
    if ($hierarchyReady) {
        $stmt = $pdo->prepare('SELECT c.*, p.name AS parent_name FROM product_categories c LEFT JOIN product_categories p ON p.id = c.parent_id WHERE c.id = :id LIMIT 1');
    } else {
        $stmt = $pdo->prepare('SELECT *, NULL AS parent_name FROM product_categories WHERE id = :id LIMIT 1');
    }
    $stmt->execute([':id' => $editId]);
    $edit = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$edit) {
        $error = 'Category not found.';
        $editId = 0;
    }
}

$categoryOptions = load_category_options($pdo, $hierarchyReady);
$categories = $hierarchyReady
    ? $pdo->query("\n        SELECT\n            c.id,\n            c.name,\n            c.parent_id,\n            p.name AS parent_name,\n            COALESCE(child_counts.child_count, 0) AS child_count,\n            COALESCE(product_counts.product_count, 0) AS product_count\n        FROM product_categories c\n        LEFT JOIN product_categories p ON p.id = c.parent_id\n        LEFT JOIN (\n            SELECT parent_id, COUNT(*) AS child_count\n            FROM product_categories\n            WHERE parent_id IS NOT NULL\n            GROUP BY parent_id\n        ) child_counts ON child_counts.parent_id = c.id\n        LEFT JOIN (\n            SELECT category_id, COUNT(*) AS product_count\n            FROM products\n            WHERE category_id IS NOT NULL\n            GROUP BY category_id\n        ) product_counts ON product_counts.category_id = c.id\n        ORDER BY\n            COALESCE(p.name, c.name) ASC,\n            CASE WHEN c.parent_id IS NULL THEN 0 ELSE 1 END ASC,\n            c.name ASC\n    ")->fetchAll(PDO::FETCH_ASSOC)
    : $pdo->query("\n        SELECT\n            c.id,\n            c.name,\n            NULL AS parent_id,\n            NULL AS parent_name,\n            0 AS child_count,\n            COALESCE(product_counts.product_count, 0) AS product_count\n        FROM product_categories c\n        LEFT JOIN (\n            SELECT category_id, COUNT(*) AS product_count\n            FROM products\n            WHERE category_id IS NOT NULL\n            GROUP BY category_id\n        ) product_counts ON product_counts.category_id = c.id\n        ORDER BY c.name ASC\n    ")->fetchAll(PDO::FETCH_ASSOC);

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">Product Categories</h4>
  </div>

  <?php if (!$hierarchyReady): ?>
    <div class="alert alert-warning">Hierarchy upgrade is not installed yet. Run <strong>public/category_hierarchy_upgrade.sql</strong> first, then reload this page.</div>
  <?php endif; ?>

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

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

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

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

            <div class="mb-3">
              <label class="form-label">Category Name *</label>
              <input class="form-control" name="name" required value="<?= h($edit['name'] ?? '') ?>">
            </div>

            <div class="mb-3">
              <label class="form-label">Parent Category</label>
              <select class="form-select" name="parent_id" <?= $hierarchyReady ? '' : 'disabled' ?>>
                <option value="0">Top-level category</option>
                <?php foreach ($categoryOptions as $category): ?>
                  <?php if ($editId > 0 && (int)$category['id'] === $editId) continue; ?>
                  <?php if ($editId > 0 && (int)($category['parent_id'] ?? 0) === $editId) continue; ?>
                  <option value="<?= (int)$category['id'] ?>" <?= ((int)($edit['parent_id'] ?? 0) === (int)$category['id']) ? 'selected' : '' ?>>
                    <?= h(category_display_name($category)) ?>
                  </option>
                <?php endforeach; ?>
              </select>
              <div class="form-text">Use a parent category to create a subcategory like <strong>Electronics → Peripherals</strong>.</div>
            </div>

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

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

    <div class="col-12 col-lg-8">
      <div class="card shadow-sm">
        <div class="card-body">
          <h6 class="mb-3">Category List</h6>

          <div class="table-responsive">
            <table class="table table-sm align-middle">
              <thead>
                <tr>
                  <th>Name</th>
                  <th>Level</th>
                  <th>Products</th>
                  <th>Children</th>
                  <th style="width: 160px;"></th>
                </tr>
              </thead>
              <tbody>
                <?php if (!$categories): ?>
                  <tr><td colspan="5" class="text-muted">No categories yet.</td></tr>
                <?php else: ?>
                  <?php foreach ($categories as $category): ?>
                    <?php $isChild = !empty($category['parent_id']); ?>
                    <tr>
                      <td>
                        <div class="fw-semibold"><?= h($category['name']) ?></div>
                        <?php if (!empty($category['parent_name'])): ?>
                          <div class="small text-muted">Parent: <?= h($category['parent_name']) ?></div>
                        <?php endif; ?>
                      </td>
                      <td><?= $isChild ? 'Subcategory' : 'Top-level' ?></td>
                      <td><?= (int)$category['product_count'] ?></td>
                      <td><?= (int)$category['child_count'] ?></td>
                      <td class="text-end">
                        <a class="btn btn-outline-primary btn-sm" href="product_categories.php?edit=<?= (int)$category['id'] ?>">Edit</a>
                        <form method="POST" class="d-inline" onsubmit="return confirm('Delete this category? Child categories will become top-level and products in this category will become unassigned.');">
                          <input type="hidden" name="action" value="delete">
                          <input type="hidden" name="id" value="<?= (int)$category['id'] ?>">
                          <button class="btn btn-outline-danger btn-sm" type="submit">Delete</button>
                        </form>
                      </td>
                    </tr>
                  <?php endforeach; ?>
                <?php endif; ?>
              </tbody>
            </table>
          </div>
        </div>
      </div>
    </div>
  </div>
</main>

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