import type { Pool } from 'pg';
import { pgAll, pgRun } from '@/shared/db';

export async function attachPhotoToAlbum(pool: Pool, photoId: string, albumId: string): Promise<void> {
  try {
    await pgRun(
      pool,
      `INSERT INTO macsync.photo_albums (photo_id, album_id)
       VALUES ($1, $2)
       ON CONFLICT (photo_id, album_id) DO NOTHING`,
      [photoId, albumId],
    );
  } catch (err) {
    throw new Error(
      `attachPhotoToAlbum failed (photo=${photoId} album=${albumId}): ${err instanceof Error ? err.message : String(err)}`,
    );
  }
}

export async function attachPhotoToAlbumsBulk(
  pool: Pool,
  pairs: ReadonlyArray<{ photoId: string; albumId: string }>,
): Promise<number> {
  if (pairs.length === 0) return 0;
  const values: string[] = [];
  const params: string[] = [];
  pairs.forEach((p, i) => {
    values.push(`($${i * 2 + 1}, $${i * 2 + 2})`);
    params.push(p.photoId, p.albumId);
  });
  const sql = `INSERT INTO macsync.photo_albums (photo_id, album_id) VALUES ${values.join(', ')}
               ON CONFLICT (photo_id, album_id) DO NOTHING`;
  try {
    await pgRun(pool, sql, params);
  } catch (err) {
    throw new Error(
      `attachPhotoToAlbumsBulk failed (n=${pairs.length}): ${err instanceof Error ? err.message : String(err)}`,
    );
  }
  return pairs.length;
}

export async function listAlbumIdsForPhoto(pool: Pool, photoId: string): Promise<readonly string[]> {
  try {
    const rows = await pgAll<{ album_id: string }>(
      pool,
      'SELECT album_id FROM macsync.photo_albums WHERE photo_id = $1',
      [photoId],
    );
    return rows.map((r) => r.album_id);
  } catch (err) {
    throw new Error(`listAlbumIdsForPhoto failed (photo=${photoId}): ${err instanceof Error ? err.message : String(err)}`);
  }
}

export async function listPhotoIdsForAlbum(
  pool: Pool,
  albumId: string,
  limit = 500,
): Promise<readonly string[]> {
  try {
    const rows = await pgAll<{ photo_id: string }>(
      pool,
      'SELECT photo_id FROM macsync.photo_albums WHERE album_id = $1 LIMIT $2',
      [albumId, limit],
    );
    return rows.map((r) => r.photo_id);
  } catch (err) {
    throw new Error(`listPhotoIdsForAlbum failed (album=${albumId}): ${err instanceof Error ? err.message : String(err)}`);
  }
}
