delivery-desk/api/repositories/notesRepository.ts

import { database } from '../database.js';
import type { Note, NoteListQuery, ReviewStatus } from '../../shared/types.js';

interface NoteRow {
  id: number; project_id: number; collection_id: number; external_id: string | null; title: string; description: string; tags: string;
  review_status: ReviewStatus; version_number: number; active_round_id: number | null; approved_version_number: number | null; created_at: string;
  image_count: number | string; annotation_count: number | string; comment_count: number | string; cover_url: string | null;
}

function toNote(row: NoteRow): Note {
  return {
    ...row,
    id: Number(row.id), project_id: Number(row.project_id), collection_id: Number(row.collection_id), version_number: Number(row.version_number),
    active_round_id: row.active_round_id == null ? null : Number(row.active_round_id), approved_version_number: row.approved_version_number == null ? null : Number(row.approved_version_number),
    image_count: Number(row.image_count), annotation_count: Number(row.annotation_count), comment_count: Number(row.comment_count),
    tags: JSON.parse(row.tags || '[]') as string[],
    cover_image: row.cover_url ? (/^https?:\/\//.test(row.cover_url) || row.cover_url.startsWith('/') ? row.cover_url : `/uploads/${row.cover_url}`) : '',
  };
}

const select = `
  SELECT n.id, n.project_id, n.collection_id, n.external_id, n.title, n.description, n.tags, n.review_status, n.version_number, n.active_round_id, n.approved_version_number, n.created_at,
    COALESCE(ic.image_count, 0) AS image_count,
    COALESCE(ac.annotation_count, 0) AS annotation_count,
    COALESCE(cc.comment_count, 0) AS comment_count,
    cover.url AS cover_url
  FROM notes n
  LEFT JOIN (SELECT note_id, version_number, COUNT(*) AS image_count FROM images GROUP BY note_id, version_number) ic ON ic.note_id = n.id AND ic.version_number = n.version_number
  LEFT JOIN (SELECT i.note_id, i.version_number, COUNT(a.id) AS annotation_count FROM images i LEFT JOIN annotations a ON a.image_id = i.id GROUP BY i.note_id, i.version_number) ac ON ac.note_id = n.id AND ac.version_number = n.version_number
  LEFT JOIN (SELECT note_id, COUNT(*) AS comment_count FROM work_comments GROUP BY note_id) cc ON cc.note_id = n.id
  LEFT JOIN images cover ON cover.note_id = n.id AND cover.version_number = n.version_number AND cover.order_index = 0`;

export const notesRepository = {
  async list(query: NoteListQuery = {}): Promise<Note[]> {
    const conditions: string[] = []; const params: unknown[] = [];
    if (query.collectionId) { conditions.push('n.collection_id = ?'); params.push(query.collectionId); }
    if (query.externalId?.trim()) { conditions.push('n.external_id = ?'); params.push(query.externalId.trim()); }
    if (query.status) { conditions.push('n.review_status = ?'); params.push(query.status); }
    if (query.q?.trim()) { conditions.push('(n.title LIKE ? OR n.description LIKE ?)'); params.push(`%${query.q.trim()}%`, `%${query.q.trim()}%`); }
    if (query.tag?.trim()) { conditions.push('n.tags LIKE ?'); params.push(`%"${query.tag.trim()}"%`); }
    if (query.projectId) { conditions.push('n.project_id = ?'); params.push(query.projectId); }
    if (query.groupId) { conditions.push('n.project_id IN (SELECT id FROM projects WHERE group_id = ?)'); params.push(query.groupId); }
    const where = conditions.length ? ` WHERE ${conditions.join(' AND ')}` : '';
    const order = query.order === 'asc' ? 'ASC' : 'DESC';
    const sort = query.sort === 'annotations' ? 'annotation_count' : 'n.created_at';
    return (await database.all<NoteRow>(`${select}${where} ORDER BY ${sort} ${order}, n.id DESC`, params)).map(toNote);
  },
  async findById(id: number): Promise<Note | null> {
    const row = await database.one<NoteRow>(`${select} WHERE n.id = ?`, [id]);
    return row ? toNote(row) : null;
  },
  async findByExternalId(collectionId: number, externalId: string): Promise<Note | null> {
    const row = await database.one<NoteRow>(`${select} WHERE n.collection_id = ? AND n.external_id = ?`, [collectionId, externalId]);
    return row ? toNote(row) : null;
  },
  async findByProjectExternalId(projectId: number, externalId: string): Promise<Note | null> {
    const row = await database.one<NoteRow>(`${select} WHERE n.project_id = ? AND n.external_id = ?`, [projectId, externalId]);
    return row ? toNote(row) : null;
  },
  async create(title: string, description: string, collectionId: number, tags: string[]): Promise<number> {
    return database.insertId('INSERT INTO notes (title, description, collection_id, tags, review_status) VALUES (?, ?, ?, ?, ?)', [title, description, collectionId, JSON.stringify(tags), 'pending']);
  },
  async setStatus(id: number, status: ReviewStatus): Promise<boolean> {
    return (await database.execute('UPDATE notes SET review_status = ? WHERE id = ?', [status, id])).changes > 0;
  },
  async remove(id: number): Promise<boolean> { return (await database.execute('DELETE FROM notes WHERE id = ?', [id])).changes > 0; },
};