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

import type { Photo, PhotoDraft, PhotoFilter } from './types';

interface Row {
  id: string;
  device_id: string;
  album_id: string | null;
  external_id: string;
  filename: string;
  media_type: string;
  width: number;
  height: number;
  duration: number | null;
  taken_at: string | null;
  storage_bucket: string | null;
  storage_key: string | null;
  synced_at: string;
}

const hydrate = (r: Row): Photo => ({
  id: r.id,
  deviceId: r.device_id,
  albumId: r.album_id,
  externalId: r.external_id,
  filename: r.filename,
  mediaType: r.media_type,
  width: r.width,
  height: r.height,
  duration: r.duration,
  takenAt: r.taken_at,
  storageBucket: r.storage_bucket,
  storageKey: r.storage_key,
  syncedAt: r.synced_at,
});

export async function upsertPhoto(pool: Pool, draft: PhotoDraft): Promise<Photo> {
  await pgAll(
    pool,
    `INSERT INTO macsync.photos (id, device_id, album_id, external_id, filename, media_type, width, height, duration, taken_at, storage_bucket, storage_key)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
     ON CONFLICT(device_id, external_id) DO UPDATE SET
       album_id = COALESCE(excluded.album_id, photos.album_id),
       storage_bucket = COALESCE(excluded.storage_bucket, photos.storage_bucket),
       storage_key = COALESCE(excluded.storage_key, photos.storage_key),
       synced_at = now()`,
    [
      draft.id,
      draft.deviceId,
      draft.albumId ?? null,
      draft.externalId,
      draft.filename,
      draft.mediaType,
      draft.width,
      draft.height,
      draft.duration ?? null,
      draft.takenAt ?? null,
      draft.storageBucket ?? null,
      draft.storageKey ?? null,
    ],
  );
  return getPhoto(pool, draft.id);
}

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

export async function listPhotos(pool: Pool, filter?: PhotoFilter): Promise<readonly Photo[]> {
  const conditions: string[] = [];
  const params: unknown[] = [];

  if (filter?.deviceId) {
    conditions.push(`device_id = $${params.length + 1}`);
    params.push(filter.deviceId);
  }
  if (filter?.albumId) {
    conditions.push(`album_id = $${params.length + 1}`);
    params.push(filter.albumId);
  }
  if (filter?.since) {
    conditions.push(`synced_at > $${params.length + 1}`);
    params.push(filter.since);
  }

  const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';
  const limit = filter?.limit ?? 100;
  const rows = await pgAll<Row>(
    pool,
    `SELECT * FROM macsync.photos ${where} ORDER BY taken_at DESC NULLS LAST LIMIT $${params.length + 1}`,
    [...params, limit],
  );
  return rows.map(hydrate);
}

export async function markPhotoUploaded(pool: Pool, id: string, bucket: string, key: string): Promise<void> {
  await pgRun(pool, 'UPDATE macsync.photos SET storage_bucket = $1, storage_key = $2 WHERE id = $3', [bucket, key, id]);
}
