function createDemoMetricsService({ db }) {
  const DAY_WINDOW = 15;

  function normalizeDate(input) {
    if (!input) return new Date().toISOString().slice(0, 10);
    return String(input).slice(0, 10);
  }

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

  function getDaySeries(endDate = null, days = DAY_WINDOW) {
    const dates = [];
    const end = new Date(`${normalizeDate(endDate)}T12:00:00`);
    for (let index = days - 1; index >= 0; index -= 1) {
      const current = new Date(end);
      current.setDate(current.getDate() - index);
      dates.push(current.toISOString().slice(0, 10));
    }
    return dates;
  }

  function wave(index, amplitude, cadence = 3, bias = 0) {
    return Math.round(Math.sin((index + 1) / cadence) * amplitude + bias);
  }

  function buildDailyDivisionValues(index) {
    const leads = 14 + ((index * 3) % 10) + wave(index, 3, 2.5, 0);
    const calls = 2 + ((index + 1) % 4) + Math.max(0, wave(index, 1, 2.1, 0));
    const deals = Math.max(0, Math.min(4, Math.round((leads / 10) + wave(index, 1, 3.8, -1))));

    const emailsSent = 18 + ((index * 4) % 16) + wave(index, 4, 2.6, 0);
    const responseRate = Math.max(24, Math.min(72, 34 + ((index * 5) % 18) + wave(index, 6, 3.1, 0)));

    const invoiced = (deals * 1250) + (calls * 180) + (index % 2 ? 250 : 0);
    const collected = Math.max(0, Math.round(invoiced * (0.5 + ((index % 3) * 0.12))));

    const tasksDone = 5 + ((index * 2) % 7) + Math.max(0, deals - 1);
    const hoursWorked = 6 + ((index * 3) % 5) + Math.max(0, tasksDone - 4);

    const bugsFound = Math.max(0, 4 + (index % 4) + wave(index, 2, 2.4, -1));
    const bugsFixed = Math.max(0, bugsFound + 1 + ((index + 2) % 3));

    const reviews = Math.max(0, Math.round(deals / 2) + (index % 2));
    const supportTickets = Math.max(1, 2 + (index % 3) + wave(index, 1, 4.2, 0));

    const policies = index % 5 === 0 ? 2 : index % 3 === 0 ? 1 : 0;
    const legalAudits = index % 6 === 0 ? 1 : 0;

    const activeClients = Math.max(5, 6 + deals + reviews + (index % 4));
    const mrr = Math.max(9000, (collected * 6) + (activeClients * 250) + (reviews * 120));
    const netProfit = Math.max(0, mrr - (hoursWorked * 70) - (supportTickets * 45) - (bugsFound * 20));

    return {
      1: { emails_sent: emailsSent, response_rate: responseRate },
      2: { leads, calls, deals },
      3: { invoiced, collected },
      4: { tasks_done: tasksDone, hours_worked: hoursWorked },
      5: { bugs_found: bugsFound, bugs_fixed: bugsFixed },
      6: { reviews, support_tickets: supportTickets },
      7: { policies, legal_audits: legalAudits },
      8: { mrr, active_clients: activeClients, net_profit: netProfit }
    };
  }

  function seedDemoSeries({ endDate = null, days = DAY_WINDOW } = {}) {
    const dates = getDaySeries(endDate, days);
    const updates = [];
    dates.forEach((date, index) => {
      const bundle = buildDailyDivisionValues(index);
      Object.entries(bundle).forEach(([divisionId, metrics]) => {
        Object.entries(metrics).forEach(([metricKey, value]) => {
          upsertMockMetric(Number(divisionId), metricKey, value, date);
          updates.push({ division_id: Number(divisionId), metric_key: metricKey, value, date, is_mock: 1 });
        });
      });
    });
    return { seeded_days: dates.length, rows: updates.length, updates };
  }

  return { seedDemoSeries };
}

module.exports = { createDemoMetricsService };
