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

import type {
  Photo,
  PhotoClassificationStatus,
  PhotoDraft,
  PhotoFaceExtractionStatus,
  PhotoFilter,
  PhotoMediaKind,
  PhotoProcessingStatus,
} from './types';

interface Row {
  id: string;
  device_id: string;
  album_id: string | null;
  external_id: string;
  filename: string;
  media_type: string;
  media_kind: string | null;
  mime_type: string | null;
  width: number;
  height: number;
  duration: number | null;
  taken_at: string | null;
  modified_at: string | null;
  imported_at: string;
  storage_bucket: string | null;
  storage_key: string | null;
  thumbnail_key: string | null;
  preview_key: string | null;
  latitude: number | null;
  longitude: number | null;
  location_name: string | null;
  is_favorite: boolean;
  is_hidden: boolean;
  is_screenshot: boolean;
  is_selfie: boolean;
  is_burst: boolean;
  burst_identifier: string | null;
  exif: Record<string, unknown> | null;
  content_hash: string | null;
  processing_status: string;
  processing_error: string | null;
  category: string | null;
  semantic_tags: unknown;
  classification_status: string;
  face_extraction_status: string;
  face_count: number;
  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,
  mediaKind: r.media_kind as PhotoMediaKind | null,
  mimeType: r.mime_type,
  width: r.width,
  height: r.height,
  duration: r.duration,
  takenAt: r.taken_at,
  modifiedAt: r.modified_at,
  importedAt: r.imported_at,
  storageBucket: r.storage_bucket,
  storageKey: r.storage_key,
  thumbnailKey: r.thumbnail_key,
  previewKey: r.preview_key,
  latitude: r.latitude,
  longitude: r.longitude,
  locationName: r.location_name,
  isFavorite: r.is_favorite,
  isHidden: r.is_hidden,
  isScreenshot: r.is_screenshot,
  isSelfie: r.is_selfie,
  isBurst: r.is_burst,
  burstIdentifier: r.burst_identifier,
  exif: r.exif,
  contentHash: r.content_hash,
  processingStatus: r.processing_status as PhotoProcessingStatus,
  processingError: r.processing_error,
  category: r.category,
  semanticTags: Array.isArray(r.semantic_tags) ? (r.semantic_tags as readonly string[]) : null,
  classificationStatus: r.classification_status as PhotoClassificationStatus,
  faceExtractionStatus: r.face_extraction_status as PhotoFaceExtractionStatus,
  faceCount: r.face_count,
  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, media_kind, mime_type,
       width, height, duration, taken_at, modified_at, imported_at,
       storage_bucket, storage_key, thumbnail_key, preview_key,
       latitude, longitude, location_name,
       is_favorite, is_hidden, is_screenshot, is_selfie, is_burst, burst_identifier,
       exif, content_hash,
       processing_status, classification_status, face_extraction_status, face_count,
       category, semantic_tags
     )
     VALUES (
       $1, $2, $3, $4, $5, $6, $7, $8,
       $9, $10, $11, $12, $13, COALESCE($14, now()),
       $15, $16, $17, $18,
       $19, $20, $21,
       COALESCE($22, false), COALESCE($23, false), COALESCE($24, false), COALESCE($25, false), COALESCE($26, false), $27,
       $28, $29,
       COALESCE($30, 'pending'), COALESCE($31, 'pending'), COALESCE($32, 'pending'), COALESCE($33, 0),
       $34, $35
     )
     ON CONFLICT(device_id, external_id) DO UPDATE SET
       album_id              = COALESCE(excluded.album_id, photos.album_id),
       media_kind            = COALESCE(excluded.media_kind, photos.media_kind),
       mime_type             = COALESCE(excluded.mime_type, photos.mime_type),
       modified_at           = COALESCE(excluded.modified_at, photos.modified_at),
       storage_bucket        = COALESCE(excluded.storage_bucket, photos.storage_bucket),
       storage_key           = COALESCE(excluded.storage_key, photos.storage_key),
       thumbnail_key         = COALESCE(excluded.thumbnail_key, photos.thumbnail_key),
       preview_key           = COALESCE(excluded.preview_key, photos.preview_key),
       latitude              = COALESCE(excluded.latitude, photos.latitude),
       longitude             = COALESCE(excluded.longitude, photos.longitude),
       location_name         = COALESCE(excluded.location_name, photos.location_name),
       is_favorite           = excluded.is_favorite,
       is_hidden             = excluded.is_hidden,
       is_screenshot         = excluded.is_screenshot,
       is_selfie             = excluded.is_selfie,
       is_burst              = excluded.is_burst,
       burst_identifier      = COALESCE(excluded.burst_identifier, photos.burst_identifier),
       exif                  = COALESCE(excluded.exif, photos.exif),
       content_hash          = COALESCE(excluded.content_hash, photos.content_hash),
       processing_status     = excluded.processing_status,
       classification_status = excluded.classification_status,
       face_extraction_status= excluded.face_extraction_status,
       face_count            = excluded.face_count,
       category              = COALESCE(excluded.category, photos.category),
       semantic_tags         = COALESCE(excluded.semantic_tags, photos.semantic_tags),
       synced_at             = now()`,
    [
      draft.id,
      draft.deviceId,
      draft.albumId ?? null,
      draft.externalId,
      draft.filename,
      draft.mediaType,
      draft.mediaKind ?? null,
      draft.mimeType ?? null,
      draft.width,
      draft.height,
      draft.duration ?? null,
      draft.takenAt ?? null,
      draft.modifiedAt ?? null,
      draft.importedAt ?? null,
      draft.storageBucket ?? null,
      draft.storageKey ?? null,
      draft.thumbnailKey ?? null,
      draft.previewKey ?? null,
      draft.latitude ?? null,
      draft.longitude ?? null,
      draft.locationName ?? null,
      draft.isFavorite ?? null,
      draft.isHidden ?? null,
      draft.isScreenshot ?? null,
      draft.isSelfie ?? null,
      draft.isBurst ?? null,
      draft.burstIdentifier ?? null,
      draft.exif ?? null,
      draft.contentHash ?? null,
      draft.processingStatus ?? null,
      draft.classificationStatus ?? null,
      draft.faceExtractionStatus ?? null,
      draft.faceCount ?? null,
      draft.category ?? null,
      draft.semanticTags ?? 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,
  contentHash: string | null = null,
): Promise<void> {
  await pgRun(
    pool,
    `UPDATE macsync.photos
       SET storage_bucket = $1,
           storage_key    = $2,
           content_hash   = COALESCE($3, content_hash),
           updated_at     = now()
     WHERE id = $4`,
    [bucket, key, contentHash, id],
  );
}
