const fs = require('fs');
const path = require('path');
const crypto = require('crypto');

function registerWorkspaceRoutes(app, { db, ai, memory, invoices, workflowHistory, rootDir, dataDir }) {
  function getInvoiceLogoFile() {
    const extensions = ['png', 'jpg', 'jpeg', 'webp', 'svg'];
    for (const ext of extensions) {
      const preferredPath = path.join(dataDir, `mine.${ext}`);
      if (fs.existsSync(preferredPath)) {
        return { filePath: preferredPath, ext };
      }
      const legacyPath = path.join(rootDir, `mine.${ext}`);
      if (fs.existsSync(legacyPath)) {
        return { filePath: legacyPath, ext };
      }
    }
    return null;
  }

  function ensureExecutiveOrdersCard(targetDivisionId) {
    const title = '⚡ Executive Orders';
    let card = db.prepare('SELECT id FROM items WHERE division_id = ? AND title = ?').get(targetDivisionId, title);
    if (!card) {
      card = { id: workflowHistory.createItem({ divisionId: targetDivisionId, title, magicLink: crypto.randomUUID() }) };
    }
    return card.id;
  }

  function getTreasuryStageFromInvoiceStatus(status) {
    if (status === 'paid') return 'paid';
    if (status === 'sent') return 'invoiced';
    return 'deposit_pending';
  }

  function syncTreasuryCaseFromInvoice(invoice) {
    const amountDue = Number(invoice.deposit_required || invoice.subtotal || 0);
    const stage = getTreasuryStageFromInvoiceStatus(invoice.status);
    const existing = db.prepare('SELECT id FROM treasury_cases WHERE invoice_id = ?').get(invoice.id);
    if (existing) {
      db.prepare(`
        UPDATE treasury_cases
        SET division_id = 3,
            item_id = ?,
            client_name = ?,
            stage = ?,
            amount_due = ?,
            due_date = ?,
            channel = COALESCE(NULLIF(channel, ''), 'bank_transfer'),
            notes = ?,
            updated_at = ?
        WHERE id = ?
      `).run(
        invoice.item_id || null,
        invoice.client_name,
        stage,
        amountDue,
        invoice.due_date || null,
        invoice.notes || '',
        new Date().toISOString(),
        existing.id
      );
      return existing.id;
    }
    const now = new Date().toISOString();
    const result = db.prepare(`
      INSERT INTO treasury_cases (division_id, item_id, invoice_id, client_name, stage, amount_due, due_date, channel, notes, created_at, updated_at)
      VALUES (3, ?, ?, ?, ?, ?, ?, 'bank_transfer', ?, ?, ?)
    `).run(
      invoice.item_id || null,
      invoice.id,
      invoice.client_name,
      stage,
      amountDue,
      invoice.due_date || null,
      invoice.notes || '',
      now,
      now
    );
    return result.lastInsertRowid;
  }

  app.get('/assets/invoice-logo', (_req, res) => {
    const file = getInvoiceLogoFile();
    if (!file) return res.status(404).end();
    res.sendFile(file.filePath);
  });

  app.get('/api/board', (req, res) => {
    res.json(workflowHistory.getBoardSnapshot());
  });

  app.put('/api/divisions/:id/vfp', (req, res) => {
    db.prepare('UPDATE divisions SET vfp = ? WHERE id = ?').run(req.body.vfp, req.params.id);
    res.json({ success: true });
  });

  app.put('/api/divisions/:id/info', (req, res) => {
    db.prepare('UPDATE divisions SET info = ? WHERE id = ?').run(req.body.info, req.params.id);
    res.json({ success: true });
  });

  app.post('/api/items', (req, res) => {
    const divisionId = parseInt(req.body.division_id, 10);
    const title = req.body.title;
    const workDate = workflowHistory.normalizeDateInput(req.body?.work_date);
    const itemId = workflowHistory.createItem({ divisionId, title, magicLink: crypto.randomUUID(), workDate });

    const autoTasks = {
      1: ['Review communication channel', 'Assign internal team members'],
      2: ['Audit current SEO status', 'Schedule strategy call'],
      3: ['Generate & Send Invoice 50% deposit', 'Verify payment receipt'],
      4: ['Setup staging environment', 'Review client assets & brief'],
      5: ['Run Zero-Bug Protocol checklist', 'Test mobile responsiveness'],
      6: ['Send welcome onboarding email', 'Schedule 48-hour check-in call'],
      7: ['Review legal contracts', 'Add client to central CRM'],
      8: ['Review strategic alignment', 'Approve final project brief']
    };

    if (autoTasks[divisionId]) autoTasks[divisionId].forEach((task) => workflowHistory.addTodo({ itemId, divisionId, text: task, workDate }));

    memory.logMemory(divisionId, 'client_created', `New client/project started: '${title}'`);
    res.json({ id: itemId });
  });

  app.put('/api/items/:id/title', (req, res) => {
    workflowHistory.updateItemMeta(Number(req.params.id), { title: req.body.title });
    res.json({ success: true });
  });

  app.put('/api/items/:id/move', (req, res) => {
    const newDivisionId = parseInt(req.body.division_id, 10);
    const item = db.prepare('SELECT title, division_id FROM items WHERE id = ?').get(req.params.id);
    const newDivision = db.prepare('SELECT name FROM divisions WHERE id = ?').get(newDivisionId);
    const oldDivision = db.prepare('SELECT name FROM divisions WHERE id = ?').get(item.division_id);

    workflowHistory.moveItem(Number(req.params.id), newDivisionId, req.body?.move_date);
    memory.logMemory(newDivisionId, 'client_moved', `Moved '${item.title}' from ${oldDivision.name} to ${newDivision.name}`);

    const existing = db.prepare('SELECT COUNT(*) as c FROM todos WHERE item_id = ? AND division_id = ?').get(req.params.id, newDivisionId).c;
    if (existing === 0) {
      const autoTasks = {
        1: ['Review comms channel'],
        2: ['Schedule strategy call'],
        3: ['Generate & Send Invoice'],
        4: ['Setup staging environment'],
        5: ['Run Zero-Bug Protocol'],
        6: ['Send welcome onboarding email'],
        7: ['Review legal contracts'],
        8: ['Approve final brief']
      };
      if (autoTasks[newDivisionId]) autoTasks[newDivisionId].forEach((task) => workflowHistory.addTodo({
        itemId: Number(req.params.id),
        divisionId: newDivisionId,
        text: task,
        workDate: req.body?.move_date
      }));
    }

    res.json({ success: true });
  });

  app.delete('/api/items/:id', (req, res) => {
    db.prepare('DELETE FROM items WHERE id = ?').run(req.params.id);
    res.json({ success: true });
  });

  app.post('/api/items/:id/todos', (req, res) => {
    const item = db.prepare('SELECT division_id FROM items WHERE id = ?').get(req.params.id);
    const id = workflowHistory.addTodo({
      itemId: Number(req.params.id),
      divisionId: item.division_id,
      text: req.body.text,
      workDate: req.body?.work_date
    });
    res.json({ id });
  });

  app.put('/api/todos/:id/toggle', (req, res) => {
    const done = req.body.done ? 1 : 0;
    workflowHistory.toggleTodo(Number(req.params.id), done === 1);
    const linkedOrder = db.prepare('SELECT executive_order_id, text, item_id, division_id FROM todos WHERE id = ?').get(req.params.id);
    if (linkedOrder?.executive_order_id) {
      const status = done ? 'done' : 'in_progress';
      const now = new Date().toISOString();
      db.prepare(`
        UPDATE executive_orders
        SET status = ?, updated_at = ?, completed_at = ?
        WHERE id = ?
      `).run(status, now, done ? now : null, linkedOrder.executive_order_id);
    }
    if (done === 1) {
      const item = db.prepare('SELECT title FROM items WHERE id = ?').get(linkedOrder.item_id);
      memory.logMemory(linkedOrder.division_id, 'task_completed', `Completed: '${linkedOrder.text}' for '${item.title}'`);
    }
    res.json({ success: true });
  });

  app.delete('/api/todos/:id', (req, res) => {
    const todo = db.prepare('SELECT executive_order_id FROM todos WHERE id = ?').get(req.params.id);
    db.prepare('DELETE FROM todos WHERE id = ?').run(req.params.id);
    if (todo?.executive_order_id) {
      db.prepare('UPDATE executive_orders SET linked_todo_id = NULL, status = ?, updated_at = ? WHERE id = ?')
        .run('open', new Date().toISOString(), todo.executive_order_id);
    }
    res.json({ success: true });
  });

  app.get('/api/settings', (req, res) => {
    res.json(db.prepare('SELECT * FROM settings WHERE id = 1').get());
  });

  app.put('/api/settings', (req, res) => {
    const body = req.body;
    const localRuntime = ['embedded', 'ollama'].includes(body.local_runtime) ? body.local_runtime : 'ollama';
    const aiProvider = ['embedded', 'ollama', 'openrouter'].includes(body.ai_provider) ? body.ai_provider : 'openrouter';
    const embeddingProvider = body.embedding_provider || (localRuntime === 'embedded' ? 'embedded' : 'ollama');
    const embeddingModel = body.embedding_model || (localRuntime === 'embedded'
      ? (body.local_runtime_model || body.ai_model || 'qwen2.5-3b-instruct-4bit')
      : 'nomic-embed-text');
    db.prepare(`UPDATE settings
      SET ai_provider=?,
          ai_model=?,
          ai_api_key=?,
          embedding_provider=?,
          embedding_model=?,
          local_runtime=?,
          local_runtime_model=?,
          owner_name=?,
          company_name=?,
          city=?,
          agent_run_hour=?,
          app_language=?,
          show_mock_data=?,
          platform_tutorial_dismissed=?
      WHERE id=1`)
      .run(
        aiProvider,
        body.ai_model,
        body.ai_api_key,
        embeddingProvider,
        embeddingModel,
        localRuntime,
        body.local_runtime_model || body.ai_model || (localRuntime === 'embedded' ? 'qwen2.5-3b-instruct-4bit' : 'llama3.1:8b'),
        body.owner_name || '',
        body.company_name || '',
        body.city || '',
        body.agent_run_hour || 7,
        'en',
        body.show_mock_data ? 1 : 0,
        body.platform_tutorial_dismissed ? 1 : 0
      );
    res.json({ success: true });
  });

  app.get('/api/services', (req, res) => {
    res.json({ services: db.prepare('SELECT * FROM services ORDER BY id ASC').all() });
  });

  app.get('/api/executive-orders', (req, res) => {
    const targetDivisionId = parseInt(req.query?.target_division_id, 10);
    const sql = `
      SELECT eo.*, d.name as target_division_name
      FROM executive_orders eo
      LEFT JOIN divisions d ON d.id = eo.target_division_id
      ${Number.isFinite(targetDivisionId) ? 'WHERE eo.target_division_id = ?' : ''}
      ORDER BY
        CASE eo.status WHEN 'open' THEN 0 WHEN 'in_progress' THEN 1 WHEN 'done' THEN 2 ELSE 3 END,
        CASE eo.priority WHEN 'high' THEN 0 WHEN 'medium' THEN 1 ELSE 2 END,
        datetime(eo.created_at) DESC,
        eo.id DESC
    `;
    const orders = Number.isFinite(targetDivisionId)
      ? db.prepare(sql).all(targetDivisionId)
      : db.prepare(sql).all();
    res.json({ orders });
  });

  app.post('/api/executive-orders', (req, res) => {
    const targetDivisionId = parseInt(req.body?.target_division_id, 10);
    const title = String(req.body?.title || '').trim();
    const body = String(req.body?.body || '').trim();
    const priority = ['low', 'medium', 'high'].includes(req.body?.priority) ? req.body.priority : 'medium';
    const dueDate = String(req.body?.due_date || '').trim() || null;
    if (!targetDivisionId || !title) {
      return res.status(400).json({ error: 'target_division_id and title are required' });
    }
    const now = new Date().toISOString();
    const linkedItemId = ensureExecutiveOrdersCard(targetDivisionId);
    const result = db.prepare(`
      INSERT INTO executive_orders (source_division_id, target_division_id, title, body, priority, status, due_date, linked_item_id, created_at, updated_at)
      VALUES (8, ?, ?, ?, ?, 'open', ?, ?, ?, ?)
    `).run(targetDivisionId, title, body, priority, dueDate, linkedItemId, now, now);
    const orderId = result.lastInsertRowid;
    const todoText = `[ED ORDER] ${title}`.substring(0, 100);
    const todoId = workflowHistory.addTodo({
      itemId: linkedItemId,
      divisionId: targetDivisionId,
      text: todoText,
      executiveOrderId: orderId
    });
    db.prepare('UPDATE executive_orders SET linked_todo_id = ? WHERE id = ?').run(todoId, orderId);
    const targetDivision = db.prepare('SELECT name FROM divisions WHERE id = ?').get(targetDivisionId);
    memory.logMemory(targetDivisionId, 'executive_order_created', `Executive order created for ${targetDivision?.name || `Division ${targetDivisionId}`}: '${title}'`);
    res.json({ id: orderId, success: true });
  });

  app.put('/api/executive-orders/:id', (req, res) => {
    const existing = db.prepare('SELECT * FROM executive_orders WHERE id = ?').get(req.params.id);
    if (!existing) return res.status(404).json({ error: 'Order not found' });
    const status = ['open', 'in_progress', 'done'].includes(req.body?.status) ? req.body.status : existing.status;
    const priority = ['low', 'medium', 'high'].includes(req.body?.priority) ? req.body.priority : existing.priority;
    const title = String(req.body?.title ?? existing.title).trim() || existing.title;
    const body = String(req.body?.body ?? existing.body).trim();
    const dueDate = req.body?.due_date === undefined ? existing.due_date : (String(req.body?.due_date || '').trim() || null);
    const now = new Date().toISOString();
    const completedAt = status === 'done' ? (existing.completed_at || now) : null;
    db.prepare(`
      UPDATE executive_orders
      SET title = ?, body = ?, priority = ?, status = ?, due_date = ?, updated_at = ?, completed_at = ?
      WHERE id = ?
    `).run(title, body, priority, status, dueDate, now, completedAt, req.params.id);
    if (existing.linked_todo_id) {
      workflowHistory.updateTodoMeta(existing.linked_todo_id, { text: `[ED ORDER] ${title}`.substring(0, 100) });
      workflowHistory.toggleTodo(existing.linked_todo_id, status === 'done');
    }
    if (status === 'done' && existing.status !== 'done') {
      memory.logMemory(existing.target_division_id, 'executive_order_completed', `Executive order completed: '${title}'`);
    }
    res.json({ success: true });
  });

  app.delete('/api/executive-orders/:id', (req, res) => {
    const existing = db.prepare('SELECT * FROM executive_orders WHERE id = ?').get(req.params.id);
    if (!existing) return res.json({ success: true });
    if (existing.linked_todo_id) {
      db.prepare('DELETE FROM todos WHERE id = ?').run(existing.linked_todo_id);
    }
    db.prepare('DELETE FROM executive_orders WHERE id = ?').run(req.params.id);
    memory.logMemory(existing.target_division_id, 'executive_order_deleted', `Executive order removed: '${existing.title}'`);
    res.json({ success: true });
  });

  app.get('/api/policies/:divisionId', (req, res) => {
    const divisionId = parseInt(req.params.divisionId, 10);
    const policies = db.prepare('SELECT * FROM policies WHERE division_id = ? ORDER BY CASE status WHEN \'active\' THEN 0 WHEN \'draft\' THEN 1 ELSE 2 END, datetime(updated_at) DESC, id DESC').all(divisionId);
    res.json({ policies });
  });

  app.post('/api/policies', (req, res) => {
    const divisionId = parseInt(req.body?.division_id, 10);
    const title = String(req.body?.title || '').trim();
    const content = String(req.body?.content || '').trim();
    const scope = String(req.body?.scope || 'all').trim() || 'all';
    const status = ['draft', 'active', 'archived'].includes(req.body?.status) ? req.body.status : 'draft';
    if (!divisionId || !title) return res.status(400).json({ error: 'division_id and title are required' });
    const now = new Date().toISOString();
    const result = db.prepare('INSERT INTO policies (division_id, title, content, status, scope, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)').run(divisionId, title, content, status, scope, now, now);
    memory.logMemory(divisionId, 'policy_created', `Policy created: '${title}'`);
    res.json({ id: result.lastInsertRowid, success: true });
  });

  app.put('/api/policies/:id', (req, res) => {
    const existing = db.prepare('SELECT * FROM policies WHERE id = ?').get(req.params.id);
    if (!existing) return res.status(404).json({ error: 'Policy not found' });
    const title = String(req.body?.title ?? existing.title).trim() || existing.title;
    const content = String(req.body?.content ?? existing.content).trim();
    const scope = String(req.body?.scope ?? existing.scope).trim() || existing.scope;
    const status = ['draft', 'active', 'archived'].includes(req.body?.status) ? req.body.status : existing.status;
    db.prepare('UPDATE policies SET title = ?, content = ?, scope = ?, status = ?, updated_at = ? WHERE id = ?')
      .run(title, content, scope, status, new Date().toISOString(), req.params.id);
    res.json({ success: true });
  });

  app.delete('/api/policies/:id', (req, res) => {
    const existing = db.prepare('SELECT * FROM policies WHERE id = ?').get(req.params.id);
    if (!existing) return res.json({ success: true });
    db.prepare('DELETE FROM policies WHERE id = ?').run(req.params.id);
    memory.logMemory(existing.division_id, 'policy_deleted', `Policy removed: '${existing.title}'`);
    res.json({ success: true });
  });

  app.get('/api/meetings/:divisionId', (req, res) => {
    const divisionId = parseInt(req.params.divisionId, 10);
    const meetings = db.prepare('SELECT * FROM meetings WHERE division_id = ? ORDER BY COALESCE(meeting_date, created_at) ASC, id DESC').all(divisionId);
    res.json({ meetings });
  });

  app.post('/api/meetings', (req, res) => {
    const divisionId = parseInt(req.body?.division_id, 10);
    const title = String(req.body?.title || '').trim();
    const cadence = String(req.body?.cadence || 'weekly').trim() || 'weekly';
    const meetingDate = String(req.body?.meeting_date || '').trim() || null;
    const notes = String(req.body?.notes || '').trim();
    const nextAction = String(req.body?.next_action || '').trim();
    if (!divisionId || !title) return res.status(400).json({ error: 'division_id and title are required' });
    const now = new Date().toISOString();
    const result = db.prepare('INSERT INTO meetings (division_id, title, cadence, meeting_date, notes, next_action, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)').run(divisionId, title, cadence, meetingDate, notes, nextAction, now, now);
    memory.logMemory(divisionId, 'meeting_created', `Meeting created: '${title}'`);
    res.json({ id: result.lastInsertRowid, success: true });
  });

  app.put('/api/meetings/:id', (req, res) => {
    const existing = db.prepare('SELECT * FROM meetings WHERE id = ?').get(req.params.id);
    if (!existing) return res.status(404).json({ error: 'Meeting not found' });
    const title = String(req.body?.title ?? existing.title).trim() || existing.title;
    const cadence = String(req.body?.cadence ?? existing.cadence).trim() || existing.cadence;
    const meetingDate = req.body?.meeting_date === undefined ? existing.meeting_date : (String(req.body?.meeting_date || '').trim() || null);
    const notes = String(req.body?.notes ?? existing.notes).trim();
    const nextAction = String(req.body?.next_action ?? existing.next_action).trim();
    db.prepare('UPDATE meetings SET title = ?, cadence = ?, meeting_date = ?, notes = ?, next_action = ?, updated_at = ? WHERE id = ?')
      .run(title, cadence, meetingDate, notes, nextAction, new Date().toISOString(), req.params.id);
    res.json({ success: true });
  });

  app.delete('/api/meetings/:id', (req, res) => {
    const existing = db.prepare('SELECT * FROM meetings WHERE id = ?').get(req.params.id);
    if (!existing) return res.json({ success: true });
    db.prepare('DELETE FROM meetings WHERE id = ?').run(req.params.id);
    memory.logMemory(existing.division_id, 'meeting_deleted', `Meeting removed: '${existing.title}'`);
    res.json({ success: true });
  });

  app.get('/api/risks/:divisionId', (req, res) => {
    const divisionId = parseInt(req.params.divisionId, 10);
    const risks = db.prepare('SELECT * FROM risks WHERE division_id = ? ORDER BY CASE status WHEN \'open\' THEN 0 WHEN \'monitoring\' THEN 1 ELSE 2 END, datetime(updated_at) DESC, id DESC').all(divisionId);
    res.json({ risks });
  });

  app.post('/api/risks', (req, res) => {
    const divisionId = parseInt(req.body?.division_id, 10);
    const title = String(req.body?.title || '').trim();
    const impact = String(req.body?.impact || '').trim();
    const mitigation = String(req.body?.mitigation || '').trim();
    const ownerDivisionId = parseInt(req.body?.owner_division_id, 10) || divisionId;
    const status = ['open', 'monitoring', 'mitigated'].includes(req.body?.status) ? req.body.status : 'open';
    if (!divisionId || !title) return res.status(400).json({ error: 'division_id and title are required' });
    const now = new Date().toISOString();
    const result = db.prepare('INSERT INTO risks (division_id, title, impact, mitigation, status, owner_division_id, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)').run(divisionId, title, impact, mitigation, status, ownerDivisionId, now, now);
    memory.logMemory(divisionId, 'risk_created', `Risk added: '${title}'`);
    res.json({ id: result.lastInsertRowid, success: true });
  });

  app.put('/api/risks/:id', (req, res) => {
    const existing = db.prepare('SELECT * FROM risks WHERE id = ?').get(req.params.id);
    if (!existing) return res.status(404).json({ error: 'Risk not found' });
    const title = String(req.body?.title ?? existing.title).trim() || existing.title;
    const impact = String(req.body?.impact ?? existing.impact).trim();
    const mitigation = String(req.body?.mitigation ?? existing.mitigation).trim();
    const ownerDivisionId = parseInt(req.body?.owner_division_id, 10) || existing.owner_division_id || existing.division_id;
    const status = ['open', 'monitoring', 'mitigated'].includes(req.body?.status) ? req.body.status : existing.status;
    db.prepare('UPDATE risks SET title = ?, impact = ?, mitigation = ?, status = ?, owner_division_id = ?, updated_at = ? WHERE id = ?')
      .run(title, impact, mitigation, status, ownerDivisionId, new Date().toISOString(), req.params.id);
    res.json({ success: true });
  });

  app.delete('/api/risks/:id', (req, res) => {
    const existing = db.prepare('SELECT * FROM risks WHERE id = ?').get(req.params.id);
    if (!existing) return res.json({ success: true });
    db.prepare('DELETE FROM risks WHERE id = ?').run(req.params.id);
    memory.logMemory(existing.division_id, 'risk_deleted', `Risk removed: '${existing.title}'`);
    res.json({ success: true });
  });

  app.get('/api/communication-templates/:divisionId', (req, res) => {
    const divisionId = parseInt(req.params.divisionId, 10);
    const templates = db.prepare(`
      SELECT *
      FROM communication_templates
      WHERE division_id = ?
      ORDER BY
        CASE status WHEN 'active' THEN 0 WHEN 'draft' THEN 1 ELSE 2 END,
        datetime(updated_at) DESC,
        id DESC
    `).all(divisionId);
    res.json({ templates });
  });

  app.post('/api/communication-templates', (req, res) => {
    const divisionId = parseInt(req.body?.division_id, 10);
    const title = String(req.body?.title || '').trim();
    const templateType = String(req.body?.template_type || 'general').trim() || 'general';
    const status = ['draft', 'active', 'archived'].includes(req.body?.status) ? req.body.status : 'draft';
    const content = String(req.body?.content || '').trim();
    if (!divisionId || !title) return res.status(400).json({ error: 'division_id and title are required' });
    const now = new Date().toISOString();
    const result = db.prepare(`
      INSERT INTO communication_templates (division_id, title, template_type, status, content, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `).run(divisionId, title, templateType, status, content, now, now);
    memory.logMemory(divisionId, 'communication_template_created', `Communication template created: '${title}'`);
    res.json({ id: result.lastInsertRowid, success: true });
  });

  app.put('/api/communication-templates/:id', (req, res) => {
    const existing = db.prepare('SELECT * FROM communication_templates WHERE id = ?').get(req.params.id);
    if (!existing) return res.status(404).json({ error: 'Template not found' });
    const title = String(req.body?.title ?? existing.title).trim() || existing.title;
    const templateType = String(req.body?.template_type ?? existing.template_type).trim() || existing.template_type;
    const status = ['draft', 'active', 'archived'].includes(req.body?.status) ? req.body.status : existing.status;
    const content = String(req.body?.content ?? existing.content).trim();
    db.prepare(`
      UPDATE communication_templates
      SET title = ?, template_type = ?, status = ?, content = ?, updated_at = ?
      WHERE id = ?
    `).run(title, templateType, status, content, new Date().toISOString(), req.params.id);
    res.json({ success: true });
  });

  app.delete('/api/communication-templates/:id', (req, res) => {
    const existing = db.prepare('SELECT * FROM communication_templates WHERE id = ?').get(req.params.id);
    if (!existing) return res.json({ success: true });
    db.prepare('DELETE FROM communication_templates WHERE id = ?').run(req.params.id);
    memory.logMemory(existing.division_id, 'communication_template_deleted', `Communication template removed: '${existing.title}'`);
    res.json({ success: true });
  });

  app.get('/api/campaign-briefs/:divisionId', (req, res) => {
    const divisionId = parseInt(req.params.divisionId, 10);
    const briefs = db.prepare(`
      SELECT *
      FROM campaign_briefs
      WHERE division_id = ?
      ORDER BY
        CASE status WHEN 'active' THEN 0 WHEN 'draft' THEN 1 ELSE 2 END,
        datetime(updated_at) DESC,
        id DESC
    `).all(divisionId);
    res.json({ briefs });
  });

  app.post('/api/campaign-briefs', (req, res) => {
    const divisionId = parseInt(req.body?.division_id, 10);
    const title = String(req.body?.title || '').trim();
    const briefType = String(req.body?.brief_type || 'campaign').trim() || 'campaign';
    const status = ['draft', 'active', 'archived'].includes(req.body?.status) ? req.body.status : 'draft';
    const angle = String(req.body?.angle || '').trim();
    const notes = String(req.body?.notes || '').trim();
    if (!divisionId || !title) return res.status(400).json({ error: 'division_id and title are required' });
    const now = new Date().toISOString();
    const result = db.prepare(`
      INSERT INTO campaign_briefs (division_id, title, brief_type, status, angle, notes, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `).run(divisionId, title, briefType, status, angle, notes, now, now);
    memory.logMemory(divisionId, 'campaign_brief_created', `Campaign brief created: '${title}'`);
    res.json({ id: result.lastInsertRowid, success: true });
  });

  app.put('/api/campaign-briefs/:id', (req, res) => {
    const existing = db.prepare('SELECT * FROM campaign_briefs WHERE id = ?').get(req.params.id);
    if (!existing) return res.status(404).json({ error: 'Campaign brief not found' });
    const title = String(req.body?.title ?? existing.title).trim() || existing.title;
    const briefType = String(req.body?.brief_type ?? existing.brief_type).trim() || existing.brief_type;
    const status = ['draft', 'active', 'archived'].includes(req.body?.status) ? req.body.status : existing.status;
    const angle = String(req.body?.angle ?? existing.angle).trim();
    const notes = String(req.body?.notes ?? existing.notes).trim();
    db.prepare(`
      UPDATE campaign_briefs
      SET title = ?, brief_type = ?, status = ?, angle = ?, notes = ?, updated_at = ?
      WHERE id = ?
    `).run(title, briefType, status, angle, notes, new Date().toISOString(), req.params.id);
    res.json({ success: true });
  });

  app.delete('/api/campaign-briefs/:id', (req, res) => {
    const existing = db.prepare('SELECT * FROM campaign_briefs WHERE id = ?').get(req.params.id);
    if (!existing) return res.json({ success: true });
    db.prepare('DELETE FROM campaign_briefs WHERE id = ?').run(req.params.id);
    memory.logMemory(existing.division_id, 'campaign_brief_deleted', `Campaign brief removed: '${existing.title}'`);
    res.json({ success: true });
  });

  app.get('/api/treasury-cases/:divisionId', (req, res) => {
    const divisionId = parseInt(req.params.divisionId, 10);
    const invoicesForDivision = db.prepare('SELECT * FROM invoices WHERE division_id = ?').all(divisionId);
    invoicesForDivision.forEach((invoice) => syncTreasuryCaseFromInvoice(invoice));
    const cases = db.prepare(`
      SELECT tc.*, i.title AS item_title
      FROM treasury_cases tc
      LEFT JOIN items i ON i.id = tc.item_id
      WHERE tc.division_id = ?
      ORDER BY
        CASE tc.stage
          WHEN 'overdue' THEN 0
          WHEN 'follow_up' THEN 1
          WHEN 'deposit_pending' THEN 2
          WHEN 'invoiced' THEN 3
          WHEN 'paid' THEN 4
          ELSE 5
        END,
        COALESCE(tc.due_date, '9999-99-99') ASC,
        datetime(tc.updated_at) DESC,
        tc.id DESC
    `).all(divisionId);
    res.json({ cases });
  });

  app.post('/api/treasury-cases', (req, res) => {
    const divisionId = parseInt(req.body?.division_id, 10) || 3;
    const itemId = req.body?.item_id ? parseInt(req.body.item_id, 10) : null;
    const clientName = String(req.body?.client_name || '').trim();
    const stage = ['deposit_pending', 'invoiced', 'follow_up', 'paid', 'overdue'].includes(req.body?.stage) ? req.body.stage : 'deposit_pending';
    const amountDue = Number(req.body?.amount_due || 0);
    const dueDate = String(req.body?.due_date || '').trim() || null;
    const channel = ['bank_transfer', 'stripe', 'cash', 'custom'].includes(req.body?.channel) ? req.body.channel : 'bank_transfer';
    const notes = String(req.body?.notes || '').trim();
    if (!clientName) return res.status(400).json({ error: 'client_name is required' });
    const now = new Date().toISOString();
    const result = db.prepare(`
      INSERT INTO treasury_cases (division_id, item_id, client_name, stage, amount_due, due_date, channel, notes, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(divisionId, itemId, clientName, stage, Number.isFinite(amountDue) ? amountDue : 0, dueDate, channel, notes, now, now);
    memory.logMemory(divisionId, 'treasury_case_created', `Treasury case created for '${clientName}' (${stage}).`);
    res.json({ id: result.lastInsertRowid, success: true });
  });

  app.put('/api/treasury-cases/:id', (req, res) => {
    const existing = db.prepare('SELECT * FROM treasury_cases WHERE id = ?').get(req.params.id);
    if (!existing) return res.status(404).json({ error: 'Treasury case not found' });
    const itemId = req.body?.item_id === undefined
      ? existing.item_id
      : (req.body?.item_id ? parseInt(req.body.item_id, 10) : null);
    const clientName = String(req.body?.client_name ?? existing.client_name).trim() || existing.client_name;
    const stage = ['deposit_pending', 'invoiced', 'follow_up', 'paid', 'overdue'].includes(req.body?.stage) ? req.body.stage : existing.stage;
    const amountDue = req.body?.amount_due === undefined ? existing.amount_due : Number(req.body.amount_due || 0);
    const dueDate = req.body?.due_date === undefined ? existing.due_date : (String(req.body?.due_date || '').trim() || null);
    const channel = ['bank_transfer', 'stripe', 'cash', 'custom'].includes(req.body?.channel) ? req.body.channel : existing.channel;
    const notes = String(req.body?.notes ?? existing.notes).trim();
    db.prepare(`
      UPDATE treasury_cases
      SET item_id = ?, client_name = ?, stage = ?, amount_due = ?, due_date = ?, channel = ?, notes = ?, updated_at = ?
      WHERE id = ?
    `).run(itemId, clientName, stage, Number.isFinite(amountDue) ? amountDue : 0, dueDate, channel, notes, new Date().toISOString(), req.params.id);
    if (stage === 'paid' && existing.stage !== 'paid') {
      memory.logMemory(existing.division_id, 'treasury_case_paid', `Treasury case marked paid for '${clientName}'.`);
    }
    res.json({ success: true });
  });

  app.delete('/api/treasury-cases/:id', (req, res) => {
    const existing = db.prepare('SELECT * FROM treasury_cases WHERE id = ?').get(req.params.id);
    if (!existing) return res.json({ success: true });
    db.prepare('DELETE FROM treasury_cases WHERE id = ?').run(req.params.id);
    memory.logMemory(existing.division_id, 'treasury_case_deleted', `Treasury case removed for '${existing.client_name}'.`);
    res.json({ success: true });
  });

  app.get('/api/invoice-profile', (_req, res) => {
    const profile = db.prepare('SELECT * FROM invoice_profile WHERE id = 1').get() || {};
    res.json({ profile });
  });

  app.put('/api/invoice-profile', (req, res) => {
    const body = req.body || {};
    const existingProfile = db.prepare('SELECT * FROM invoice_profile WHERE id = 1').get() || {};
    db.prepare(`
      UPDATE invoice_profile
      SET display_name = ?,
          tagline = ?,
          legal_name = ?,
          address_line1 = ?,
          address_line2 = ?,
          city_postcode_country = ?,
          country = ?,
          utr = ?,
          national_id = ?,
          website = ?,
          bank_holder = ?,
          bank_account = ?,
          bank_swift = ?,
          bank_bic = ?,
          bank_address = ?,
          logo_url = ?
      WHERE id = 1
    `).run(
      body.display_name || '',
      body.tagline || '',
      body.legal_name || '',
      body.address_line1 || '',
      body.address_line2 || '',
      body.city_postcode_country || '',
      body.country || '',
      body.utr || '',
      body.national_id || '',
      body.website || '',
      body.bank_holder || '',
      body.bank_account || '',
      body.bank_swift || '',
      body.bank_bic || '',
      body.bank_address || '',
      body.logo_url === undefined ? (existingProfile.logo_url || '') : (body.logo_url || '')
    );
    res.json({ success: true });
  });

  app.post('/api/invoice-logo', (req, res) => {
    const dataUrl = String(req.body?.data_url || '').trim();
    const match = dataUrl.match(/^data:(image\/[a-zA-Z0-9.+-]+);base64,(.+)$/);
    if (!match) return res.status(400).json({ error: 'Invalid image upload payload' });
    const mime = match[1];
    const base64 = match[2];
    const extMap = {
      'image/png': 'png',
      'image/jpeg': 'jpg',
      'image/jpg': 'jpg',
      'image/webp': 'webp',
      'image/svg+xml': 'svg'
    };
    const ext = extMap[mime];
    if (!ext) return res.status(400).json({ error: 'Unsupported logo format' });
    for (const existingExt of ['png', 'jpg', 'jpeg', 'webp', 'svg']) {
      const filePath = path.join(dataDir, `mine.${existingExt}`);
      if (fs.existsSync(filePath)) fs.unlinkSync(filePath);
      const legacyPath = path.join(rootDir, `mine.${existingExt}`);
      if (fs.existsSync(legacyPath)) fs.unlinkSync(legacyPath);
    }
    const filePath = path.join(dataDir, `mine.${ext}`);
    fs.writeFileSync(filePath, Buffer.from(base64, 'base64'));
    db.prepare('UPDATE invoice_profile SET logo_url = ? WHERE id = 1').run('/assets/invoice-logo');
    res.json({ success: true, logo_url: '/assets/invoice-logo' });
  });

  app.get('/api/invoices/:divisionId', (req, res) => {
    const divisionId = parseInt(req.params.divisionId, 10);
    const invoiceRows = db.prepare(`
      SELECT inv.*, i.title AS item_title
      FROM invoices inv
      LEFT JOIN items i ON i.id = inv.item_id
      WHERE inv.division_id = ?
      ORDER BY datetime(inv.updated_at) DESC, inv.id DESC
    `).all(divisionId);
    const itemRows = db.prepare('SELECT * FROM invoice_line_items ORDER BY position ASC, id ASC').all();
    const invoicesWithItems = invoiceRows.map((invoice) => ({
      ...invoice,
      items: itemRows.filter((item) => item.invoice_id === invoice.id)
    }));
    res.json({ invoices: invoicesWithItems });
  });

  app.post('/api/invoices', (req, res) => {
    const body = req.body || {};
    const divisionId = parseInt(body.division_id, 10) || 3;
    const itemId = body.item_id ? parseInt(body.item_id, 10) : null;
    const clientName = String(body.client_name || '').trim();
    const clientAddress = String(body.client_address || '').trim();
    const clientTaxId = String(body.client_tax_id || '').trim();
    const clientCode = String(body.client_code || '').trim();
    const invoiceNumber = String(body.invoice_number || invoices.buildInvoiceNumber(db)).trim();
    const invoiceDate = String(body.invoice_date || new Date().toISOString().slice(0, 10)).trim();
    const dueDate = String(body.due_date || '').trim();
    const currency = ['EUR', 'GBP', 'USD', 'CAD'].includes(body.currency) ? body.currency : 'EUR';
    const paymentReference = String(body.payment_reference || '').trim();
    const terms = String(body.terms || '').trim();
    const notes = String(body.notes || '').trim();
    const status = ['draft', 'sent', 'paid'].includes(body.status) ? body.status : 'draft';
    const items = Array.isArray(body.items) ? body.items : [];
    if (!clientName || !items.length) return res.status(400).json({ error: 'client_name and at least one line item are required' });
    const normalizedItems = items
      .map((item, index) => ({
        description: String(item.description || '').trim(),
        amount: Number(item.amount || 0),
        position: index
      }))
      .filter((item) => item.description);
    if (!normalizedItems.length) return res.status(400).json({ error: 'At least one valid line item is required' });
    const subtotal = normalizedItems.reduce((sum, item) => sum + (Number.isFinite(item.amount) ? item.amount : 0), 0);
    const depositRequired = body.deposit_required === undefined ? subtotal / 2 : Number(body.deposit_required || 0);
    const now = new Date().toISOString();
    const result = db.prepare(`
      INSERT INTO invoices (division_id, item_id, client_name, client_address, client_tax_id, client_code, invoice_number, invoice_date, due_date, currency, subtotal, deposit_required, payment_reference, terms, notes, status, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(divisionId, itemId, clientName, clientAddress, clientTaxId, clientCode, invoiceNumber, invoiceDate, dueDate, currency, subtotal, Number.isFinite(depositRequired) ? depositRequired : subtotal / 2, paymentReference, terms, notes, status, now, now);
    const invoiceId = result.lastInsertRowid;
    const insertItem = db.prepare('INSERT INTO invoice_line_items (invoice_id, description, amount, position) VALUES (?, ?, ?, ?)');
    normalizedItems.forEach((item) => insertItem.run(invoiceId, item.description, Number.isFinite(item.amount) ? item.amount : 0, item.position));
    syncTreasuryCaseFromInvoice({
      id: invoiceId,
      item_id: itemId,
      client_name: clientName,
      due_date: dueDate,
      deposit_required: Number.isFinite(depositRequired) ? depositRequired : subtotal / 2,
      subtotal,
      notes,
      status
    });
    memory.logMemory(divisionId, 'invoice_created', `Invoice ${invoiceNumber} created for '${clientName}'.`);
    res.json({ id: invoiceId, invoice_number: invoiceNumber, success: true });
  });

  app.put('/api/invoices/:id', (req, res) => {
    const existing = db.prepare('SELECT * FROM invoices WHERE id = ?').get(req.params.id);
    if (!existing) return res.status(404).json({ error: 'Invoice not found' });
    const body = req.body || {};
    const itemId = body.item_id === undefined ? existing.item_id : (body.item_id ? parseInt(body.item_id, 10) : null);
    const clientName = String(body.client_name ?? existing.client_name).trim() || existing.client_name;
    const clientAddress = String(body.client_address ?? existing.client_address).trim();
    const clientTaxId = String(body.client_tax_id ?? existing.client_tax_id).trim();
    const clientCode = String(body.client_code ?? existing.client_code).trim();
    const invoiceNumber = String(body.invoice_number ?? existing.invoice_number).trim() || existing.invoice_number;
    const invoiceDate = String(body.invoice_date ?? existing.invoice_date).trim();
    const dueDate = body.due_date === undefined ? existing.due_date : (String(body.due_date || '').trim() || null);
    const currency = ['EUR', 'GBP', 'USD', 'CAD'].includes(body.currency) ? body.currency : existing.currency;
    const paymentReference = String(body.payment_reference ?? existing.payment_reference).trim();
    const terms = String(body.terms ?? existing.terms).trim();
    const notes = String(body.notes ?? existing.notes).trim();
    const status = ['draft', 'sent', 'paid'].includes(body.status) ? body.status : existing.status;
    const now = new Date().toISOString();
    let subtotal = Number(existing.subtotal || 0);
    let depositRequired = body.deposit_required === undefined ? Number(existing.deposit_required || 0) : Number(body.deposit_required || 0);
    if (Array.isArray(body.items)) {
      const normalizedItems = body.items
        .map((item, index) => ({
          description: String(item.description || '').trim(),
          amount: Number(item.amount || 0),
          position: index
        }))
        .filter((item) => item.description);
      subtotal = normalizedItems.reduce((sum, item) => sum + (Number.isFinite(item.amount) ? item.amount : 0), 0);
      if (body.deposit_required === undefined) depositRequired = subtotal / 2;
      db.prepare('DELETE FROM invoice_line_items WHERE invoice_id = ?').run(req.params.id);
      const insertItem = db.prepare('INSERT INTO invoice_line_items (invoice_id, description, amount, position) VALUES (?, ?, ?, ?)');
      normalizedItems.forEach((item) => insertItem.run(req.params.id, item.description, Number.isFinite(item.amount) ? item.amount : 0, item.position));
    }
    db.prepare(`
      UPDATE invoices
      SET item_id = ?, client_name = ?, client_address = ?, client_tax_id = ?, client_code = ?, invoice_number = ?, invoice_date = ?, due_date = ?, currency = ?, subtotal = ?, deposit_required = ?, payment_reference = ?, terms = ?, notes = ?, status = ?, updated_at = ?
      WHERE id = ?
    `).run(itemId, clientName, clientAddress, clientTaxId, clientCode, invoiceNumber, invoiceDate, dueDate, currency, subtotal, Number.isFinite(depositRequired) ? depositRequired : subtotal / 2, paymentReference, terms, notes, status, now, req.params.id);
    syncTreasuryCaseFromInvoice({
      id: Number(req.params.id),
      item_id: itemId,
      client_name: clientName,
      due_date: dueDate,
      deposit_required: Number.isFinite(depositRequired) ? depositRequired : subtotal / 2,
      subtotal,
      notes,
      status
    });
    res.json({ success: true });
  });

  app.delete('/api/invoices/:id', (req, res) => {
    const existing = db.prepare('SELECT * FROM invoices WHERE id = ?').get(req.params.id);
    if (!existing) return res.json({ success: true });
    db.prepare('DELETE FROM treasury_cases WHERE invoice_id = ?').run(req.params.id);
    db.prepare('DELETE FROM invoice_line_items WHERE invoice_id = ?').run(req.params.id);
    db.prepare('DELETE FROM invoices WHERE id = ?').run(req.params.id);
    memory.logMemory(existing.division_id, 'invoice_deleted', `Invoice ${existing.invoice_number} deleted.`);
    res.json({ success: true });
  });

  app.get('/invoices/:id/print', (req, res) => {
    const invoice = db.prepare('SELECT * FROM invoices WHERE id = ?').get(req.params.id);
    if (!invoice) return res.status(404).send('Invoice not found');
    const items = db.prepare('SELECT * FROM invoice_line_items WHERE invoice_id = ? ORDER BY position ASC, id ASC').all(req.params.id);
    const profile = db.prepare('SELECT * FROM invoice_profile WHERE id = 1').get() || {};
    res.setHeader('Content-Type', 'text/html; charset=utf-8');
    res.send(invoices.renderInvoiceHtml({ profile, invoice, items }));
  });

  app.post('/api/services', (req, res) => {
    const { name, description, price, currency, deliverables, ideal_client: idealClient, icon } = req.body;
    const result = db.prepare('INSERT INTO services (name, description, price, currency, deliverables, ideal_client, icon) VALUES (?, ?, ?, ?, ?, ?, ?)').run(
      name,
      description || '',
      price || '',
      ['EUR', 'GBP', 'USD', 'CAD'].includes(currency) ? currency : 'EUR',
      deliverables || '',
      idealClient || '',
      icon || 'fa-box'
    );
    memory.logMemory(0, 'service_added', `Added service: '${name}' at ${['EUR', 'GBP', 'USD', 'CAD'].includes(currency) ? currency : 'EUR'} ${price}`);
    res.json({ id: result.lastInsertRowid });
  });

  app.put('/api/services/:id', (req, res) => {
    const { name, description, price, currency, deliverables, ideal_client: idealClient, icon } = req.body;
    db.prepare('UPDATE services SET name=?, description=?, price=?, currency=?, deliverables=?, ideal_client=?, icon=? WHERE id=?').run(
      name,
      description || '',
      price || '',
      ['EUR', 'GBP', 'USD', 'CAD'].includes(currency) ? currency : 'EUR',
      deliverables || '',
      idealClient || '',
      icon || 'fa-box',
      req.params.id
    );
    memory.logMemory(0, 'service_updated', `Updated service: '${name}'`);
    res.json({ success: true });
  });

  app.delete('/api/services/:id', (req, res) => {
    const service = db.prepare('SELECT name FROM services WHERE id = ?').get(req.params.id);
    db.prepare('DELETE FROM services WHERE id = ?').run(req.params.id);
    if (service) memory.logMemory(0, 'service_deleted', `Deleted service: '${service.name}'`);
    res.json({ success: true });
  });

  app.post('/api/onboarding/complete', async (req, res) => {
    const {
      owner_name: ownerName,
      company_name: companyName,
      city,
      services,
      ai_provider: aiProvider,
      ai_model: aiModel,
      ai_api_key: aiApiKey,
      app_language: appLanguage
    } = req.body;
    const settings = db.prepare('SELECT * FROM settings WHERE id = 1').get();

    if (settings.onboarding_complete) {
      return res.json({ success: true, skipped: true, vfps_updated: 0, battle_plans_created: 0, ai_used: false });
    }

    const selectedProvider = ['embedded', 'ollama', 'openrouter'].includes(aiProvider)
      ? aiProvider
      : (['embedded', 'ollama', 'openrouter'].includes(settings.ai_provider) ? settings.ai_provider : 'openrouter');
    const selectedModel = String(aiModel || '').trim()
      || String(settings.ai_model || '').trim()
      || (selectedProvider === 'embedded' ? 'qwen2.5-3b-instruct-4bit' : selectedProvider === 'ollama' ? 'llama3.1:8b' : 'google/gemini-2.5-flash');
    const effectiveApiKey = selectedProvider === 'openrouter'
      ? String(aiApiKey || '').trim()
      : (settings.ai_api_key || '');

    db.prepare(`UPDATE settings
      SET owner_name=?,
          company_name=?,
          city=?,
          ai_provider=?,
          ai_model=?,
          ai_api_key=?,
          app_language=?
      WHERE id=1`)
      .run(ownerName || '', companyName || '', city || '', selectedProvider, selectedModel, effectiveApiKey, 'en');

    if (services && services.length > 0) {
      db.prepare('DELETE FROM services').run();
      const insertService = db.prepare('INSERT INTO services (name, description, price, currency, deliverables, ideal_client, icon) VALUES (?, ?, ?, ?, ?, ?, ?)');
      services.forEach((service) => insertService.run(
        service.name,
        service.description || '',
        service.price || '',
        ['EUR', 'GBP', 'USD', 'CAD'].includes(service.currency) ? service.currency : 'EUR',
        service.deliverables || '',
        service.ideal_client || '',
        service.icon || 'fa-box'
      ));
    }

    const effectiveSettings = db.prepare('SELECT * FROM settings WHERE id = 1').get();
    const allServices = db.prepare('SELECT * FROM services').all();
    const servicesDesc = allServices.map((service) => `${service.name} (${service.currency || 'EUR'} ${service.price}): ${service.description}`).join('; ');
    const results = {
      vfps_updated: 0,
      infos_updated: 0,
      battle_plans_created: 0,
      ai_used: false,
      ai_provider_used: null,
      ai_fallback_used: false,
      ai_setup_message: ''
    };

    try {
      const insertBattlePlan = db.prepare('INSERT INTO battle_plans (division_id, period, title, created_at) VALUES (?, ?, ?, ?)');
      const now = new Date().toISOString();

      const vfpPrompt = `You are setting up a business called "${companyName}" owned by ${ownerName} based in ${city}.
Services offered: ${servicesDesc}
Generate a specific, motivating VFP (Valuable Final Product — 1 sentence, the ideal outcome of this division) for each division.
Return ONLY valid JSON (no markdown): {"8":"...","7":"...","1":"...","2":"...","3":"...","4":"...","5":"...","6":"..."}`;
      const vfpResponse = await ai.callAIWithFallback(vfpPrompt, effectiveSettings, ai.buildLanguageInstruction(effectiveSettings, { json: true }));
      const vfpRaw = vfpResponse.content;
      const vfpMap = ai.parseJsonResponse(vfpRaw);
      if (vfpMap && typeof vfpMap === 'object' && !Array.isArray(vfpMap)) {
        Object.entries(vfpMap).forEach(([divisionId, vfp]) => {
          if (typeof vfp !== 'string' || !vfp) return;
          db.prepare('UPDATE divisions SET vfp = ? WHERE id = ?').run(vfp.substring(0, 300), parseInt(divisionId, 10));
          results.vfps_updated++;
        });
      }

      const divisionScopes = {
        8: 'Executive command, targets, priorities, org-wide bottlenecks, resource allocation, strategic oversight.',
        7: 'Executive implementation, admin/legal control, policy execution, organizational support, compliance.',
        1: 'Internal and external communication lines, routing, dispatch, follow-up coordination, clarity of information.',
        2: 'Lead flow, dissemination, positioning, outreach, promotion, demand generation, conversion pipeline.',
        3: 'Treasury, invoicing, collections, deposits, financial discipline, cash visibility.',
        4: 'Production, technical delivery, build execution, implementation, fulfillment.',
        5: 'Quality assurance, readiness checks, standards, technical review, release confidence.',
        6: 'Client/public satisfaction, support, retention, reviews, relationship continuity.'
      };
      const infoPrompt = `You are configuring the Org Board for "${companyName}" owned by ${ownerName} in ${city}.
Services offered: ${servicesDesc}

For each division below, write a detailed but concise operational scope in English.
Each scope must explain:
1. what the division exists to do
2. why it exists in this business specifically
3. what outcomes it owns
4. how it supports the surrounding divisions

Division scopes:
8: ${divisionScopes[8]}
7: ${divisionScopes[7]}
1: ${divisionScopes[1]}
2: ${divisionScopes[2]}
3: ${divisionScopes[3]}
4: ${divisionScopes[4]}
5: ${divisionScopes[5]}
6: ${divisionScopes[6]}

Return ONLY valid JSON (no markdown):
{"8":"...","7":"...","1":"...","2":"...","3":"...","4":"...","5":"...","6":"..."}`;
      const infoResponse = await ai.callAIWithFallback(infoPrompt, effectiveSettings, ai.buildLanguageInstruction(effectiveSettings, { json: true }));
      const infoMap = ai.parseJsonResponse(infoResponse.content);
      if (infoMap && typeof infoMap === 'object' && !Array.isArray(infoMap)) {
        Object.entries(infoMap).forEach(([divisionId, info]) => {
          if (typeof info !== 'string' || !info) return;
          db.prepare('UPDATE divisions SET info = ? WHERE id = ?').run(info.substring(0, 1200), parseInt(divisionId, 10));
          results.infos_updated++;
        });
      }

      const bpPrompt = `Agency: "${companyName}". Services: ${servicesDesc}.
For each of these 4 divisions generate 3 YEARLY goals and 3 MONTHLY goals (for the current month).
Division IDs: 2=Marketing, 3=Treasury, 4=Technical, 6=CustomerSuccess.
Return ONLY valid JSON (no markdown):
{"2":{"yearly":["goal","goal","goal"],"monthly":["goal","goal","goal"]},"3":{...},"4":{...},"6":{...}}`;
      const bpResponse = await ai.callAIWithFallback(bpPrompt, effectiveSettings, ai.buildLanguageInstruction(effectiveSettings, { json: true }));
      const bpRaw = bpResponse.content;
      const bpMap = ai.parseJsonResponse(bpRaw);
      if (bpMap && typeof bpMap === 'object' && !Array.isArray(bpMap)) {
        Object.entries(bpMap).forEach(([divisionId, periods]) => {
          if (typeof periods !== 'object' || Array.isArray(periods)) return;
          Object.entries(periods).forEach(([period, goals]) => {
            if (!Array.isArray(goals)) return;
            goals.forEach((goal) => {
              const title = String(goal).replace(/^\[.*?\]\s*/, '').substring(0, 200);
              if (title.length <= 3) return;
              insertBattlePlan.run(parseInt(divisionId, 10), period, title, now);
              results.battle_plans_created++;
            });
          });
        });
      }

      results.ai_used = true;
      results.ai_provider_used = bpResponse.provider || infoResponse.provider || vfpResponse.provider || effectiveSettings.ai_provider;
      results.ai_fallback_used = Boolean(vfpResponse.fallbackUsed || infoResponse.fallbackUsed || bpResponse.fallbackUsed);
      results.ai_setup_message = results.ai_fallback_used
        ? `AI personalization completed using fallback provider: ${results.ai_provider_used}.`
        : `AI personalization completed using ${results.ai_provider_used}.`;
    } catch (error) {
      console.log('[Onboarding] AI setup skipped:', error.message);
      results.ai_setup_message = 'Onboarding completed without AI personalization. Configure OpenRouter or Ollama to enable it.';
    }

    db.demoMetrics?.seedDemoSeries();
    db.prepare('UPDATE settings SET onboarding_complete = 1 WHERE id = 1').run();
    memory.logMemory(8, 'onboarding', `Agency "${companyName}" onboarded by ${ownerName} in ${city}`);
    res.json({ success: true, ...results });
  });

  app.post('/api/system/reset', (req, res) => {
    const confirmation = String(req.body?.confirmation || '').trim().toUpperCase();
    if (confirmation !== 'RESET') {
      return res.status(400).json({ error: 'confirmation must be RESET' });
    }

    db.resetWorkspace();
    res.json({ success: true, message: 'Workspace reset complete. Onboarding is required again.' });
  });
}

module.exports = { registerWorkspaceRoutes };
