function createMetricsAutomationService({ db, workflowHistory }) {
  function upsertMetric(divisionId, metricKey, value, date) {
    const existing = db.prepare('SELECT id FROM metrics_data WHERE division_id = ? AND metric_key = ? AND date = ?')
      .get(divisionId, metricKey, date);
    if (existing) {
      db.prepare('UPDATE metrics_data SET value = ?, is_mock = 0 WHERE id = ?').run(value, existing.id);
      return;
    }
    db.prepare('INSERT INTO metrics_data (division_id, metric_key, value, date, is_mock) VALUES (?, ?, ?, ?, 0)')
      .run(divisionId, metricKey, value, date);
  }

  function countTodosForDivision(divisionId, date, { doneOnly = false } = {}) {
    return db.prepare(`
      SELECT COUNT(*) as count
      FROM todos
      WHERE division_id = ?
        AND work_date <= ?
        AND (${doneOnly ? "done = 1 AND substr(completed_at, 1, 10) = ?" : "(completed_at IS NULL OR substr(completed_at, 1, 10) >= ?)"})
    `).get(divisionId, date, date).count;
  }

  function countHistoryStarts(divisionId, date) {
    return db.prepare('SELECT COUNT(*) as count FROM item_division_history WHERE division_id = ? AND start_date = ?')
      .get(divisionId, date).count;
  }

  function sumInvoiceAmount(date, columnName, status = null) {
    const query = `
      SELECT SUM(${columnName}) as total
      FROM invoices
      WHERE substr(COALESCE(invoice_date, updated_at, created_at), 1, 10) = ?
      ${status ? 'AND status = ?' : ''}
    `;
    const row = status ? db.prepare(query).get(date, status) : db.prepare(query).get(date);
    return Number(row?.total || 0);
  }

  function countByUpdatedDate(tableName, date, extraWhere = '', params = []) {
    const row = db.prepare(`
      SELECT COUNT(*) as count
      FROM ${tableName}
      WHERE substr(COALESCE(updated_at, created_at), 1, 10) = ?
      ${extraWhere ? `AND ${extraWhere}` : ''}
    `).get(date, ...params);
    return Number(row?.count || 0);
  }

  function collectForDate(inputDate = null) {
    const date = workflowHistory.normalizeDateInput(inputDate);
    const monthPrefix = date.slice(0, 7);
    const board = workflowHistory.getBoardSnapshot({ fromDate: date, toDate: date });
    const activeClients = board.divisions
      .filter((division) => ![7, 8].includes(division.id))
      .reduce((sum, division) => sum + (division.items || []).length, 0);
    const paidMonthRow = db.prepare(`
      SELECT SUM(subtotal) as total
      FROM invoices
      WHERE status = 'paid'
        AND substr(COALESCE(invoice_date, updated_at, created_at), 1, 7) = ?
    `).get(monthPrefix);
    const openExposureRow = db.prepare(`
      SELECT SUM(amount_due) as total
      FROM treasury_cases
      WHERE stage != 'paid'
    `).get();
    const paidMonthTotal = Number(paidMonthRow?.total || 0);
    const openExposure = Number(openExposureRow?.total || 0);

    const div1Done = countTodosForDivision(1, date, { doneOnly: true });
    const div1Total = countTodosForDivision(1, date);
    const div2Calls = countTodosForDivision(2, date, { doneOnly: true });
    const div4Done = countTodosForDivision(4, date, { doneOnly: true });

    const updates = [
      [1, 'emails_sent', div1Done],
      [1, 'response_rate', div1Total ? Math.round((div1Done / div1Total) * 100) : 0],
      [2, 'leads', countHistoryStarts(2, date)],
      [2, 'calls', div2Calls],
      [2, 'deals', db.prepare(`
        SELECT COUNT(*) as count
        FROM item_division_history
        WHERE division_id = 3
          AND start_date = ?
          AND item_id IN (SELECT item_id FROM item_division_history WHERE division_id = 2)
      `).get(date).count],
      [3, 'invoiced', Number(sumInvoiceAmount(date, 'subtotal').toFixed(2))],
      [3, 'collected', Number(sumInvoiceAmount(date, 'deposit_required', 'paid').toFixed(2))],
      [4, 'tasks_done', div4Done],
      [4, 'hours_worked', div4Done + countByUpdatedDate('production_assets', date) + countByUpdatedDate('staging_runs', date) + countByUpdatedDate('scope_controls', date)],
      [5, 'bugs_found', countByUpdatedDate('qa_checks', date, "status IN ('failed', 'blocked')")],
      [5, 'bugs_fixed', countByUpdatedDate('qa_checks', date, "status = 'passed'")],
      [6, 'reviews', countByUpdatedDate('success_loops', date, "loop_type = 'review_request' AND status IN ('done', 'completed')")],
      [6, 'support_tickets', countByUpdatedDate('success_loops', date, "loop_type = 'support'")],
      [7, 'policies', countByUpdatedDate('policies', date)],
      [7, 'legal_audits', countByUpdatedDate('meetings', date) + countByUpdatedDate('risks', date)],
      [8, 'mrr', Number(paidMonthTotal.toFixed(2))],
      [8, 'active_clients', activeClients]
      ,[8, 'net_profit', Number(Math.max(0, paidMonthTotal - openExposure).toFixed(2))]
    ];

    updates.forEach(([divisionId, metricKey, value]) => upsertMetric(divisionId, metricKey, value, date));
    return { date, updates: updates.map(([divisionId, metricKey, value]) => ({ division_id: divisionId, metric_key: metricKey, value })) };
  }

  return { collectForDate };
}

module.exports = { createMetricsAutomationService };
