function createClientSuccessService({ db }) {
  const defaultLoops = [
    ['review_request', '48-Hour Review Request'],
    ['support_checkin', 'Support Check-In'],
    ['referral_upsell', 'Referral / Upsell Pulse']
  ];

  function ensureSuccessLoops(divisionId = 6) {
    const items = db.prepare("SELECT id FROM items WHERE division_id = ? AND title != '⚡ Executive Orders'").all(divisionId);
    const insert = db.prepare(`
      INSERT INTO success_loops (division_id, item_id, loop_type, title, status, next_action_date, notes, created_at, updated_at)
      VALUES (?, ?, ?, ?, 'pending', ?, '', ?, ?)
    `);
    const now = new Date();
    const nowIso = now.toISOString();
    const nextDate = new Date(now.getTime() + 2 * 86400000).toISOString().slice(0, 10);
    items.forEach((item) => {
      const count = db.prepare('SELECT COUNT(*) as c FROM success_loops WHERE division_id = ? AND item_id = ?').get(divisionId, item.id).c;
      if (count > 0) return;
      defaultLoops.forEach(([loopType, title]) => insert.run(divisionId, item.id, loopType, title, nextDate, nowIso, nowIso));
    });
  }

  function listSuccessLoops(divisionId = 6) {
    ensureSuccessLoops(divisionId);
    return db.prepare(`
      SELECT sl.*, item.title AS item_title
      FROM success_loops sl
      LEFT JOIN items item ON item.id = sl.item_id
      WHERE sl.division_id = ?
      ORDER BY COALESCE(sl.updated_at, sl.created_at) DESC, sl.id DESC
    `).all(divisionId);
  }

  function insertSuccessLoop(payload) {
    const now = new Date().toISOString();
    const result = db.prepare(`
      INSERT INTO success_loops (division_id, item_id, loop_type, title, status, next_action_date, notes, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      Number(payload.division_id) || 6,
      payload.item_id ? Number(payload.item_id) : null,
      String(payload.loop_type || 'review_request').trim(),
      String(payload.title || '').trim(),
      String(payload.status || 'pending').trim(),
      String(payload.next_action_date || '').trim(),
      String(payload.notes || '').trim(),
      now,
      now
    );
    return result.lastInsertRowid;
  }

  function updateSuccessLoop(id, payload) {
    const existing = db.prepare('SELECT * FROM success_loops WHERE id = ?').get(id);
    if (!existing) return false;
    db.prepare(`
      UPDATE success_loops
      SET item_id = ?,
          loop_type = ?,
          title = ?,
          status = ?,
          next_action_date = ?,
          notes = ?,
          updated_at = ?
      WHERE id = ?
    `).run(
      payload.item_id ? Number(payload.item_id) : null,
      String(payload.loop_type || existing.loop_type || 'review_request').trim(),
      String(payload.title || existing.title || '').trim(),
      String(payload.status || existing.status || 'pending').trim(),
      String(payload.next_action_date || existing.next_action_date || '').trim(),
      String(payload.notes || existing.notes || '').trim(),
      new Date().toISOString(),
      id
    );
    return true;
  }

  function deleteSuccessLoop(id) {
    db.prepare('DELETE FROM success_loops WHERE id = ?').run(id);
  }

  return {
    listSuccessLoops,
    insertSuccessLoop,
    updateSuccessLoop,
    deleteSuccessLoop
  };
}

module.exports = { createClientSuccessService };
