import { db, id, now } from '../db.js';
import { camelGroup } from './mappers.js';

export function listGroups() {
  return db.prepare(`
    SELECT g.*, COUNT(ug.user_id) AS member_count
    FROM groups g
    LEFT JOIN user_groups ug ON ug.group_id = g.id
    GROUP BY g.id
    ORDER BY g.name
  `).all().map((group) => ({ ...camelGroup(group), memberCount: group.member_count }));
}

export function createGroup(payload) {
  const groupId = id('grp');
  db.prepare('INSERT INTO groups (id, name, description, created_at) VALUES (?, ?, ?, ?)').run(
    groupId,
    payload.name,
    payload.description || '',
    now()
  );
  const stmt = db.prepare('INSERT OR IGNORE INTO user_groups (user_id, group_id) VALUES (?, ?)');
  for (const userId of payload.userIds) stmt.run(userId, groupId);
  return camelGroup(db.prepare('SELECT * FROM groups WHERE id = ?').get(groupId));
}