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

import type { Album, AlbumDraft, AlbumType } from './types';

interface Row {
  id: string;
  device_id: string;
  external_id: string;
  name: string;
  photo_count: number;
  album_type: string;
  start_date: string | null;
  end_date: string | null;
  cover_photo_id: string | null;
  sort_order: number;
  synced_at: string;
}

const hydrate = (r: Row): Album => ({
  id: r.id,
  deviceId: r.device_id,
  externalId: r.external_id,
  name: r.name,
  photoCount: r.photo_count,
  albumType: (r.album_type as AlbumType) ?? 'user',
  startDate: r.start_date,
  endDate: r.end_date,
  coverPhotoId: r.cover_photo_id,
  sortOrder: r.sort_order,
  syncedAt: r.synced_at,
});

export async function upsertAlbum(pool: Pool, draft: AlbumDraft): Promise<Album> {
  await pgAll(
    pool,
    `INSERT INTO macsync.albums (id, device_id, external_id, name, album_type, start_date, end_date, cover_photo_id, sort_order)
     VALUES ($1, $2, $3, $4, COALESCE($5, 'user'), $6, $7, $8, COALESCE($9, 0))
     ON CONFLICT(device_id, external_id) DO UPDATE SET
       name           = excluded.name,
       album_type     = excluded.album_type,
       start_date     = COALESCE(excluded.start_date, albums.start_date),
       end_date       = COALESCE(excluded.end_date, albums.end_date),
       cover_photo_id = COALESCE(excluded.cover_photo_id, albums.cover_photo_id),
       sort_order     = excluded.sort_order,
       synced_at      = now()`,
    [
      draft.id,
      draft.deviceId,
      draft.externalId,
      draft.name,
      draft.albumType ?? null,
      draft.startDate ?? null,
      draft.endDate ?? null,
      draft.coverPhotoId ?? null,
      draft.sortOrder ?? null,
    ],
  );
  return getAlbum(pool, draft.id);
}

export async function setAlbumCoverPhoto(pool: Pool, albumId: string, coverPhotoId: string | null): Promise<void> {
  await pgRun(pool, 'UPDATE macsync.albums SET cover_photo_id = $1, updated_at = now() WHERE id = $2', [
    coverPhotoId,
    albumId,
  ]);
}

export async function getAlbum(pool: Pool, id: string): Promise<Album> {
  const row = await pgGet<Row>(pool, 'SELECT * FROM macsync.albums WHERE id = $1', [id]);
  if (!row) throw notFound('album_not_found', `album ${id} not found`);
  return hydrate(row);
}

export async function listAlbumsByDevice(pool: Pool, deviceId: string): Promise<readonly Album[]> {
  const rows = await pgAll<Row>(pool, 'SELECT * FROM macsync.albums WHERE device_id = $1 ORDER BY name ASC', [deviceId]);
  return rows.map(hydrate);
}

export async function incrementAlbumPhotoCount(pool: Pool, id: string): Promise<void> {
  await pgRun(pool, 'UPDATE macsync.albums SET photo_count = photo_count + 1 WHERE id = $1', [id]);
}
