const express = require('express');
const { all, audit, json, now, one, parseJson, run } = require('../db');
const { requireCapability } = require('../middleware/auth');
const { formatAssetId } = require('../services/assets');
const companies = require('../services/companies');
const assetClasses = require('../services/assetClasses');
const zones = require('../services/zones');
const section179Limits = require('../services/section179Limits');
const section280fLimits = require('../services/section280fLimits');
const shortTaxYear = require('../services/shortTaxYear');

const router = express.Router();

// Special depreciation zones (e.g. New York Liberty Zone). GET is reference data used by the
// asset form; mutations require config.manage.
router.get('/depreciation-zones', (req, res) => {
  res.json({ zones: zones.list() });
});

router.post('/depreciation-zones', requireCapability('config.manage'), (req, res, next) => {
  try {
    res.status(201).json({ zone: zones.createZone(req.body, req.user.id) });
  } catch (error) {
    next(error);
  }
});

router.put('/depreciation-zones/:code', requireCapability('config.manage'), (req, res, next) => {
  try {
    const zone = zones.updateZone(req.params.code, req.body, req.user.id);
    if (!zone) return res.status(404).json({ error: 'Zone not found' });
    return res.json({ zone });
  } catch (error) {
    return next(error);
  }
});

router.delete('/depreciation-zones/:code', requireCapability('config.manage'), (req, res) => {
  if (!zones.deleteZone(req.params.code, req.user.id)) return res.status(404).json({ error: 'Zone not found' });
  return res.status(204).end();
});

// Annual Section 179 deduction limits & phaseout thresholds. GET is reference data; mutations
// require config.manage. The engine caps each asset's §179 deduction at the applicable year's limit.
router.get('/section-179-limits', (req, res) => {
  res.json({ limits: section179Limits.list() });
});

router.post('/section-179-limits', requireCapability('config.manage'), (req, res, next) => {
  try {
    res.status(201).json({ limit: section179Limits.createLimit(req.body, req.user.id) });
  } catch (error) {
    next(error);
  }
});

router.put('/section-179-limits/:year', requireCapability('config.manage'), (req, res, next) => {
  try {
    const limit = section179Limits.updateLimit(req.params.year, req.body, req.user.id);
    if (!limit) return res.status(404).json({ error: 'Limit not found' });
    return res.json({ limit });
  } catch (error) {
    return next(error);
  }
});

router.delete('/section-179-limits/:year', requireCapability('config.manage'), (req, res) => {
  if (!section179Limits.deleteLimit(req.params.year, req.user.id)) return res.status(404).json({ error: 'Limit not found' });
  return res.status(204).end();
});

// §280F passenger-automobile depreciation caps (indexed yearly; user-managed).
router.get('/section-280f-limits', (req, res) => {
  res.json({ limits: section280fLimits.list() });
});

router.post('/section-280f-limits', requireCapability('config.manage'), (req, res, next) => {
  try {
    res.status(201).json({ limit: section280fLimits.createLimit(req.body, req.user.id) });
  } catch (error) {
    next(error);
  }
});

router.put('/section-280f-limits/:year', requireCapability('config.manage'), (req, res, next) => {
  try {
    const limit = section280fLimits.updateLimit(req.params.year, req.body, req.user.id);
    if (!limit) return res.status(404).json({ error: 'Limit not found' });
    return res.json({ limit });
  } catch (error) {
    return next(error);
  }
});

router.delete('/section-280f-limits/:year', requireCapability('config.manage'), (req, res) => {
  if (!section280fLimits.deleteLimit(req.params.year, req.user.id)) return res.status(404).json({ error: 'Limit not found' });
  return res.status(204).end();
});

// Short tax years (per company) — prorate that year's MACRS depreciation.
router.get('/short-tax-years', (req, res) => {
  res.json({ years: shortTaxYear.list(req.companyId) });
});

router.post('/short-tax-years', requireCapability('config.manage'), (req, res, next) => {
  try {
    res.status(201).json({ year: shortTaxYear.save(req.companyId, req.body, req.user.id) });
  } catch (error) {
    next(error);
  }
});

router.delete('/short-tax-years/:year', requireCapability('config.manage'), (req, res) => {
  if (!shortTaxYear.remove(req.companyId, req.params.year, req.user.id)) return res.status(404).json({ error: 'Not found' });
  return res.status(204).end();
});

// Asset class catalog (seeded from the IRS tables, then user-managed). GET is reference
// data used by the category picker; mutations require config.manage.
router.get('/asset-classes', (req, res) => {
  res.json({ assetClasses: assetClasses.list() });
});

router.post('/asset-classes', requireCapability('config.manage'), (req, res, next) => {
  try {
    res.status(201).json({ assetClass: assetClasses.createClass(req.body, req.user.id) });
  } catch (error) {
    next(error);
  }
});

router.put('/asset-classes/:id', requireCapability('config.manage'), (req, res, next) => {
  try {
    const assetClass = assetClasses.updateClass(req.params.id, req.body, req.user.id);
    if (!assetClass) return res.status(404).json({ error: 'Asset class not found' });
    return res.json({ assetClass });
  } catch (error) {
    return next(error);
  }
});

router.delete('/asset-classes/:id', requireCapability('config.manage'), (req, res) => {
  if (!assetClasses.deleteClass(req.params.id, req.user.id)) return res.status(404).json({ error: 'Asset class not found' });
  return res.status(204).end();
});

// Companies (stored in `entities`). Returns the user's accessible active companies for the switcher;
// company admins requesting ?includeDisposed=1 get the full management list (incl. disposed).
router.get('/entities', (req, res) => {
  if (req.query.includeDisposed === '1' && companies.isCompanyAdmin(req.user)) {
    return res.json({ entities: companies.listCompanies({ includeDisposed: true }) });
  }
  return res.json({ entities: companies.companiesForUser(req.user) });
});

router.post('/entities', requireCapability('config.manage'), (req, res, next) => {
  try {
    res.status(201).json({ entity: companies.createCompany(req.body, req.user.id) });
  } catch (error) {
    next(error);
  }
});

router.put('/entities/:id', requireCapability('config.manage'), (req, res) => {
  const entity = companies.updateCompany(req.params.id, req.body, req.user.id);
  if (!entity) return res.status(404).json({ error: 'Company not found' });
  return res.json({ entity });
});

router.post('/entities/:id/dispose', requireCapability('config.manage'), (req, res) => {
  const result = companies.disposeCompany(req.params.id, req.body || {}, req.user.id);
  if (!result.ok) return res.status(result.status).json({ error: result.error });
  return res.json({ entity: result.company });
});

router.post('/entities/:id/restore', requireCapability('config.manage'), (req, res) => {
  const entity = companies.restoreCompany(req.params.id, req.user.id);
  if (!entity) return res.status(404).json({ error: 'Company not found' });
  return res.json({ entity });
});

router.get('/references', (req, res) => {
  const rows = all('SELECT * FROM reference_values WHERE entity_id = ? ORDER BY type, name', [req.companyId]);
  res.json({ references: rows.map((row) => ({ ...row, metadata: parseJson(row.metadata, {}) })) });
});

// ---- Asset categories (managed reference_values of type 'category') ----------
// Asset category is stored on each asset by name, so a rename cascades to assets.

function categoryRow(row) {
  const meta = parseJson(row.metadata, {});
  const templateId = meta.id_template_id || null;
  const template = templateId ? one('SELECT name, pattern FROM asset_id_templates WHERE id = ?', [templateId]) : null;
  // Categories link to an asset class by its unique id (class numbers are NOT unique — e.g. 00.12
  // covers both Computers and Casinos). The number/label are resolved from the linked class so the
  // right one is always shown; a manually-typed number (no id) falls back to that text.
  const classId = meta.asset_class_id || null;
  const cls = classId ? one('SELECT id, class_number, description FROM asset_classes WHERE id = ?', [classId]) : null;
  return {
    id: row.id,
    name: row.name,
    code: row.code,
    asset_class_id: cls ? cls.id : null,
    asset_class_number: cls ? cls.class_number : (meta.asset_class_number || null),
    asset_class_label: cls ? `${cls.class_number} — ${cls.description}` : null,
    id_template_id: template ? templateId : null,
    id_template_name: template ? template.name : null,
    id_template_pattern: template ? template.pattern : null,
    asset_count: one('SELECT COUNT(*) AS c FROM assets WHERE category = ? AND entity_id = ?', [row.name, row.entity_id]).c
  };
}

// Resolve the class number a category should carry: the linked class's number wins; otherwise a
// manually-entered number. Returns { classId, classNumber }.
function resolveCategoryClass(body, prev = {}) {
  if (body.asset_class_id !== undefined && body.asset_class_id !== null && body.asset_class_id !== '') {
    const cls = one('SELECT class_number FROM asset_classes WHERE id = ?', [body.asset_class_id]);
    if (cls) return { classId: Number(body.asset_class_id), classNumber: cls.class_number };
  }
  // Explicit null id, or a manual number with no id → manual override (clears the link).
  if (body.asset_class_id === null || body.asset_class_id === '' || body.asset_class_number !== undefined) {
    return { classId: null, classNumber: body.asset_class_number || null };
  }
  return { classId: prev.asset_class_id || null, classNumber: prev.asset_class_number || null };
}

const categoryEditor = requireCapability('config.manage');

router.get('/asset-categories', (req, res) => {
  const rows = all("SELECT * FROM reference_values WHERE type = 'category' AND entity_id = ? ORDER BY name", [req.companyId]);
  res.json({ categories: rows.map(categoryRow) });
});

router.post('/asset-categories', categoryEditor, (req, res) => {
  const name = String(req.body.name || '').trim();
  if (!name) return res.status(400).json({ error: 'Category name is required' });
  if (one("SELECT id FROM reference_values WHERE type = 'category' AND name = ? AND entity_id = ?", [name, req.companyId])) {
    return res.status(400).json({ error: 'That category already exists' });
  }
  const { classId, classNumber } = resolveCategoryClass(req.body);
  const result = run(
    "INSERT INTO reference_values (entity_id, type, name, code, metadata) VALUES (?, 'category', ?, ?, ?)",
    [req.companyId, name, req.body.code || null, json({ id_template_id: req.body.id_template_id || null, asset_class_id: classId, asset_class_number: classNumber })]
  );
  audit(req.user.id, 'create', 'asset_category', result.lastInsertRowid, null, { name });
  return res.status(201).json({ category: categoryRow(one('SELECT * FROM reference_values WHERE id = ?', [result.lastInsertRowid])) });
});

router.put('/asset-categories/:id', categoryEditor, (req, res) => {
  const before = one("SELECT * FROM reference_values WHERE id = ? AND type = 'category' AND entity_id = ?", [req.params.id, req.companyId]);
  if (!before) return res.status(404).json({ error: 'Category not found' });
  const name = String(req.body.name ?? before.name).trim();
  if (!name) return res.status(400).json({ error: 'Category name is required' });
  if (name !== before.name && one("SELECT id FROM reference_values WHERE type = 'category' AND name = ? AND entity_id = ?", [name, req.companyId])) {
    return res.status(400).json({ error: 'That category already exists' });
  }
  const meta = parseJson(before.metadata, {});
  if (req.body.id_template_id !== undefined) meta.id_template_id = req.body.id_template_id || null;
  const classChanged = req.body.asset_class_id !== undefined || req.body.asset_class_number !== undefined;
  if (classChanged) {
    const resolved = resolveCategoryClass(req.body, meta);
    meta.asset_class_id = resolved.classId;
    meta.asset_class_number = resolved.classNumber;
  }
  run('UPDATE reference_values SET name = ?, code = ?, metadata = ? WHERE id = ?', [name, req.body.code ?? before.code, json(meta), req.params.id]);
  let renamed = 0;
  if (name !== before.name) {
    renamed = run('UPDATE assets SET category = ?, updated_at = ? WHERE category = ? AND entity_id = ?', [name, now(), before.name, req.companyId]).changes || 0;
  }
  // The class number is shared by every asset in the category — cascade any change (within this company).
  if (classChanged) {
    run('UPDATE assets SET asset_class_number = ?, updated_at = ? WHERE category = ? AND entity_id = ?', [meta.asset_class_number, now(), name, req.companyId]);
  }
  audit(req.user.id, 'update', 'asset_category', req.params.id, before, { name, renamed_assets: renamed });
  return res.json({ category: categoryRow(one('SELECT * FROM reference_values WHERE id = ?', [req.params.id])), renamed_assets: renamed });
});

router.delete('/asset-categories/:id', categoryEditor, (req, res) => {
  const before = one("SELECT * FROM reference_values WHERE id = ? AND type = 'category' AND entity_id = ?", [req.params.id, req.companyId]);
  if (!before) return res.status(404).json({ error: 'Category not found' });
  const affected = one('SELECT COUNT(*) AS c FROM assets WHERE category = ? AND entity_id = ?', [before.name, req.companyId]).c;
  run('DELETE FROM reference_values WHERE id = ?', [req.params.id]);
  audit(req.user.id, 'delete', 'asset_category', req.params.id, before, { asset_count: affected });
  return res.json({ deleted: true, asset_count: affected });
});

// ---- Asset departments (managed reference_values of type 'department') -------
// Department is stored on each asset by name, so a rename cascades to assets.

function departmentRow(row) {
  return {
    id: row.id,
    name: row.name,
    code: row.code,
    asset_count: one('SELECT COUNT(*) AS c FROM assets WHERE department = ? AND entity_id = ?', [row.name, row.entity_id]).c
  };
}

router.get('/asset-departments', (req, res) => {
  const rows = all("SELECT * FROM reference_values WHERE type = 'department' AND entity_id = ? ORDER BY name", [req.companyId]);
  res.json({ departments: rows.map(departmentRow) });
});

router.post('/asset-departments', categoryEditor, (req, res) => {
  const name = String(req.body.name || '').trim();
  if (!name) return res.status(400).json({ error: 'Department name is required' });
  if (one("SELECT id FROM reference_values WHERE type = 'department' AND name = ? AND entity_id = ?", [name, req.companyId])) {
    return res.status(400).json({ error: 'That department already exists' });
  }
  const result = run(
    "INSERT INTO reference_values (entity_id, type, name, code, metadata) VALUES (?, 'department', ?, ?, '{}')",
    [req.companyId, name, req.body.code || null]
  );
  audit(req.user.id, 'create', 'asset_department', result.lastInsertRowid, null, { name });
  return res.status(201).json({ department: departmentRow(one('SELECT * FROM reference_values WHERE id = ?', [result.lastInsertRowid])) });
});

router.put('/asset-departments/:id', categoryEditor, (req, res) => {
  const before = one("SELECT * FROM reference_values WHERE id = ? AND type = 'department' AND entity_id = ?", [req.params.id, req.companyId]);
  if (!before) return res.status(404).json({ error: 'Department not found' });
  const name = String(req.body.name ?? before.name).trim();
  if (!name) return res.status(400).json({ error: 'Department name is required' });
  if (name !== before.name && one("SELECT id FROM reference_values WHERE type = 'department' AND name = ? AND entity_id = ?", [name, req.companyId])) {
    return res.status(400).json({ error: 'That department already exists' });
  }
  run('UPDATE reference_values SET name = ?, code = ? WHERE id = ?', [name, req.body.code ?? before.code, req.params.id]);
  let renamed = 0;
  if (name !== before.name) {
    renamed = run('UPDATE assets SET department = ?, updated_at = ? WHERE department = ? AND entity_id = ?', [name, now(), before.name, req.companyId]).changes || 0;
  }
  audit(req.user.id, 'update', 'asset_department', req.params.id, before, { name, renamed_assets: renamed });
  return res.json({ department: departmentRow(one('SELECT * FROM reference_values WHERE id = ?', [req.params.id])), renamed_assets: renamed });
});

router.delete('/asset-departments/:id', categoryEditor, (req, res) => {
  const before = one("SELECT * FROM reference_values WHERE id = ? AND type = 'department' AND entity_id = ?", [req.params.id, req.companyId]);
  if (!before) return res.status(404).json({ error: 'Department not found' });
  const affected = one('SELECT COUNT(*) AS c FROM assets WHERE department = ? AND entity_id = ?', [before.name, req.companyId]).c;
  run('DELETE FROM reference_values WHERE id = ?', [req.params.id]);
  audit(req.user.id, 'delete', 'asset_department', req.params.id, before, { asset_count: affected });
  return res.json({ deleted: true, asset_count: affected });
});

// ---- PM categories (managed reference_values of type 'pm_category') ----------
// Separate from asset categories. Stored on each PM plan by name, so a rename cascades.

function pmCategoryRow(row) {
  return {
    id: row.id,
    name: row.name,
    code: row.code,
    plan_count: one('SELECT COUNT(*) AS c FROM pm_plans WHERE category = ? AND entity_id = ?', [row.name, row.entity_id]).c
  };
}

router.get('/pm-categories', (req, res) => {
  const rows = all("SELECT * FROM reference_values WHERE type = 'pm_category' AND entity_id = ? ORDER BY name", [req.companyId]);
  res.json({ categories: rows.map(pmCategoryRow) });
});

router.post('/pm-categories', categoryEditor, (req, res) => {
  const name = String(req.body.name || '').trim();
  if (!name) return res.status(400).json({ error: 'Category name is required' });
  if (one("SELECT id FROM reference_values WHERE type = 'pm_category' AND name = ? AND entity_id = ?", [name, req.companyId])) {
    return res.status(400).json({ error: 'That PM category already exists' });
  }
  const result = run(
    "INSERT INTO reference_values (entity_id, type, name, code, metadata) VALUES (?, 'pm_category', ?, ?, '{}')",
    [req.companyId, name, req.body.code || null]
  );
  audit(req.user.id, 'create', 'pm_category', result.lastInsertRowid, null, { name });
  return res.status(201).json({ category: pmCategoryRow(one('SELECT * FROM reference_values WHERE id = ?', [result.lastInsertRowid])) });
});

router.put('/pm-categories/:id', categoryEditor, (req, res) => {
  const before = one("SELECT * FROM reference_values WHERE id = ? AND type = 'pm_category' AND entity_id = ?", [req.params.id, req.companyId]);
  if (!before) return res.status(404).json({ error: 'PM category not found' });
  const name = String(req.body.name ?? before.name).trim();
  if (!name) return res.status(400).json({ error: 'Category name is required' });
  if (name !== before.name && one("SELECT id FROM reference_values WHERE type = 'pm_category' AND name = ? AND entity_id = ?", [name, req.companyId])) {
    return res.status(400).json({ error: 'That PM category already exists' });
  }
  run('UPDATE reference_values SET name = ?, code = ? WHERE id = ?', [name, req.body.code ?? before.code, req.params.id]);
  let renamed = 0;
  if (name !== before.name) {
    renamed = run('UPDATE pm_plans SET category = ?, updated_at = ? WHERE category = ? AND entity_id = ?', [name, now(), before.name, req.companyId]).changes || 0;
  }
  audit(req.user.id, 'update', 'pm_category', req.params.id, before, { name, renamed_plans: renamed });
  return res.json({ category: pmCategoryRow(one('SELECT * FROM reference_values WHERE id = ?', [req.params.id])), renamed_plans: renamed });
});

router.delete('/pm-categories/:id', categoryEditor, (req, res) => {
  const before = one("SELECT * FROM reference_values WHERE id = ? AND type = 'pm_category' AND entity_id = ?", [req.params.id, req.companyId]);
  if (!before) return res.status(404).json({ error: 'PM category not found' });
  const affected = one('SELECT COUNT(*) AS c FROM pm_plans WHERE category = ? AND entity_id = ?', [before.name, req.companyId]).c;
  run('DELETE FROM reference_values WHERE id = ?', [req.params.id]);
  audit(req.user.id, 'delete', 'pm_category', req.params.id, before, { plan_count: affected });
  return res.json({ deleted: true, plan_count: affected });
});

// ---- Part categories (managed reference_values of type 'part_category') ------
// Separate from asset/PM categories. Stored on each part by name, so a rename cascades.

function partCategoryRow(row) {
  return {
    id: row.id,
    name: row.name,
    code: row.code,
    part_count: one('SELECT COUNT(*) AS c FROM parts WHERE category = ?', [row.name]).c
  };
}

router.get('/part-categories', (req, res) => {
  const rows = all("SELECT * FROM reference_values WHERE type = 'part_category' AND entity_id = ? ORDER BY name", [req.companyId]);
  res.json({ categories: rows.map(partCategoryRow) });
});

router.post('/part-categories', categoryEditor, (req, res) => {
  const name = String(req.body.name || '').trim();
  if (!name) return res.status(400).json({ error: 'Category name is required' });
  if (one("SELECT id FROM reference_values WHERE type = 'part_category' AND name = ? AND entity_id = ?", [name, req.companyId])) {
    return res.status(400).json({ error: 'That part category already exists' });
  }
  const result = run(
    "INSERT INTO reference_values (entity_id, type, name, code, metadata) VALUES (?, 'part_category', ?, ?, '{}')",
    [req.companyId, name, req.body.code || null]
  );
  audit(req.user.id, 'create', 'part_category', result.lastInsertRowid, null, { name });
  return res.status(201).json({ category: partCategoryRow(one('SELECT * FROM reference_values WHERE id = ?', [result.lastInsertRowid])) });
});

router.put('/part-categories/:id', categoryEditor, (req, res) => {
  const before = one("SELECT * FROM reference_values WHERE id = ? AND type = 'part_category' AND entity_id = ?", [req.params.id, req.companyId]);
  if (!before) return res.status(404).json({ error: 'Part category not found' });
  const name = String(req.body.name ?? before.name).trim();
  if (!name) return res.status(400).json({ error: 'Category name is required' });
  if (name !== before.name && one("SELECT id FROM reference_values WHERE type = 'part_category' AND name = ? AND entity_id = ?", [name, req.companyId])) {
    return res.status(400).json({ error: 'That part category already exists' });
  }
  run('UPDATE reference_values SET name = ?, code = ? WHERE id = ?', [name, req.body.code ?? before.code, req.params.id]);
  let renamed = 0;
  if (name !== before.name) {
    renamed = run('UPDATE parts SET category = ?, updated_at = ? WHERE category = ?', [name, now(), before.name]).changes || 0;
  }
  audit(req.user.id, 'update', 'part_category', req.params.id, before, { name, renamed_parts: renamed });
  return res.json({ category: partCategoryRow(one('SELECT * FROM reference_values WHERE id = ?', [req.params.id])), renamed_parts: renamed });
});

router.delete('/part-categories/:id', categoryEditor, (req, res) => {
  const before = one("SELECT * FROM reference_values WHERE id = ? AND type = 'part_category' AND entity_id = ?", [req.params.id, req.companyId]);
  if (!before) return res.status(404).json({ error: 'Part category not found' });
  const affected = one('SELECT COUNT(*) AS c FROM parts WHERE category = ?', [before.name]).c;
  run('DELETE FROM reference_values WHERE id = ?', [req.params.id]);
  audit(req.user.id, 'delete', 'part_category', req.params.id, before, { part_count: affected });
  return res.json({ deleted: true, part_count: affected });
});

// ---- Asset ID templates -----------------------------------------------------
// Naming patterns (e.g. 'DT-######') used to auto-assign asset tags. The first run of
// '#' is replaced with a zero-padded, auto-incrementing number. Assigned to categories.

function idTemplateCategoryCount(templateId) {
  return all("SELECT metadata FROM reference_values WHERE type = 'category'")
    .filter((row) => parseJson(row.metadata, {}).id_template_id === templateId).length;
}

function idTemplateRow(row) {
  return {
    id: row.id,
    name: row.name,
    pattern: row.pattern,
    next_number: row.next_number,
    preview: formatAssetId(row.pattern, row.next_number),
    category_count: idTemplateCategoryCount(row.id)
  };
}

router.get('/asset-id-templates', (req, res) => {
  res.json({ templates: all('SELECT * FROM asset_id_templates ORDER BY name').map(idTemplateRow) });
});

router.post('/asset-id-templates', categoryEditor, (req, res) => {
  const name = String(req.body.name || '').trim();
  const pattern = String(req.body.pattern || '').trim();
  if (!name) return res.status(400).json({ error: 'A template name is required' });
  if (!pattern.includes('#')) return res.status(400).json({ error: 'Pattern must include at least one # for the number' });
  const next = Math.max(1, Number(req.body.next_number) || 1);
  const ts = now();
  const result = run(
    'INSERT INTO asset_id_templates (name, pattern, next_number, created_at, updated_at) VALUES (?, ?, ?, ?, ?)',
    [name, pattern, next, ts, ts]
  );
  audit(req.user.id, 'create', 'asset_id_template', result.lastInsertRowid, null, { name, pattern });
  return res.status(201).json({ template: idTemplateRow(one('SELECT * FROM asset_id_templates WHERE id = ?', [result.lastInsertRowid])) });
});

router.put('/asset-id-templates/:id', categoryEditor, (req, res) => {
  const before = one('SELECT * FROM asset_id_templates WHERE id = ?', [req.params.id]);
  if (!before) return res.status(404).json({ error: 'Template not found' });
  const pattern = req.body.pattern !== undefined ? String(req.body.pattern).trim() : before.pattern;
  if (!pattern.includes('#')) return res.status(400).json({ error: 'Pattern must include at least one # for the number' });
  run(
    'UPDATE asset_id_templates SET name = ?, pattern = ?, next_number = ?, updated_at = ? WHERE id = ?',
    [
      req.body.name !== undefined ? String(req.body.name).trim() || before.name : before.name,
      pattern,
      req.body.next_number !== undefined ? Math.max(1, Number(req.body.next_number) || 1) : before.next_number,
      now(),
      req.params.id
    ]
  );
  audit(req.user.id, 'update', 'asset_id_template', req.params.id, before, req.body);
  return res.json({ template: idTemplateRow(one('SELECT * FROM asset_id_templates WHERE id = ?', [req.params.id])) });
});

router.delete('/asset-id-templates/:id', categoryEditor, (req, res) => {
  const before = one('SELECT * FROM asset_id_templates WHERE id = ?', [req.params.id]);
  if (!before) return res.status(404).json({ error: 'Template not found' });
  const id = Number(req.params.id);
  // Unassign from any categories that referenced it.
  let unassigned = 0;
  for (const row of all("SELECT id, metadata FROM reference_values WHERE type = 'category'")) {
    const meta = parseJson(row.metadata, {});
    if (meta.id_template_id === id) {
      meta.id_template_id = null;
      run('UPDATE reference_values SET metadata = ? WHERE id = ?', [json(meta), row.id]);
      unassigned += 1;
    }
  }
  run('DELETE FROM asset_id_templates WHERE id = ?', [id]);
  audit(req.user.id, 'delete', 'asset_id_template', id, before, { unassigned_categories: unassigned });
  return res.json({ deleted: true, category_count: unassigned });
});

module.exports = router;