import { getDb } from './db.js';

export function listCategoriesWithCounts() {
  return getDb().prepare(`
    SELECT c.*, COALESCE(COUNT(p.id), 0) AS part_count
    FROM categories c
    LEFT JOIN parts p ON p.category_id = c.id
    GROUP BY c.id
    ORDER BY c.sort_order, c.name_en
  `).all();
}

export function getCategory(id) {
  return getDb().prepare(`SELECT * FROM categories WHERE id = ?`).get(Number(id));
}

export function createCategory(input) {
  const stmt = getDb().prepare(`
    INSERT INTO categories (name_en, name_tg, sort_order)
    VALUES (@name_en, @name_tg, @sort_order)
  `);
  return stmt.run(normalize(input)).lastInsertRowid;
}

export function updateCategory(id, input) {
  getDb().prepare(`
    UPDATE categories
    SET name_en = @name_en, name_tg = @name_tg, sort_order = @sort_order
    WHERE id = @id
  `).run({ ...normalize(input), id: Number(id) });
}

// parts.category_id has ON DELETE SET NULL, so deleting a category leaves
// its parts in place (just uncategorized).
export function deleteCategory(id) {
  getDb().prepare(`DELETE FROM categories WHERE id = ?`).run(Number(id));
}

function normalize(c) {
  return {
    name_en:    (c.name_en || '').trim(),
    name_tg:    (c.name_tg || '').trim(),
    sort_order: Number.isFinite(Number(c.sort_order)) ? Number(c.sort_order) : 0
  };
}
