/**
 * Mehrere ATTACHMENT-Events pro Ticket und Kalendertag (lokal) zu einem Event zusammenführen.
 */
export function mergeLegacyAttachmentEventsByDay(db) {
  const rows = db
    .prepare(
      `SELECT id, ticket_id, created_at, description,
              date(created_at, 'localtime') AS day_key
       FROM events
       WHERE type = 'ATTACHMENT'
       ORDER BY ticket_id, day_key, created_at ASC`,
    )
    .all();

  /** @type {Map<string, Array<{id: string, description: string}>>} */
  const groups = new Map();
  for (const r of rows) {
    const k = `${r.ticket_id}|${r.day_key}`;
    if (!groups.has(k)) groups.set(k, []);
    groups.get(k).push({ id: r.id, description: r.description || '' });
  }

  for (const [, evs] of groups) {
    if (evs.length <= 1) continue;
    const keeper = evs[0].id;
    const others = evs.slice(1);
    const descParts = evs
      .map((e) => String(e.description || '').trim())
      .filter(Boolean);
    const mergedDesc = [...new Set(descParts)].join('\n\n');

    db.exec('BEGIN');
    try {
      db.prepare('UPDATE events SET description = ? WHERE id = ?').run(
        mergedDesc,
        keeper,
      );
      for (const o of others) {
        db.prepare('UPDATE ticket_attachments SET event_id = ? WHERE event_id = ?').run(
          keeper,
          o.id,
        );
        db.prepare('DELETE FROM events WHERE id = ?').run(o.id);
      }
      db.exec('COMMIT');
    } catch (e) {
      db.exec('ROLLBACK');
      throw e;
    }
  }
}