/**
 * migrate-photos.ts
 *
 * Migrates photos (and albums + junction rows) from the legacy
 * imajin-media-gallery Postgres+MinIO stack into the mac-sync SQLite+MinIO target.
 *
 * Usage:
 *   bun run scripts/migrate-photos.ts [--dry-run]
 *
 * Environment / CLI overrides (env var or --flag=value):
 *   SOURCE_DB_URL          Postgres DSN for source (default: postgres://localhost:25448/lilith_media_gallery)
 *   SOURCE_MINIO_ENDPOINT  MinIO endpoint for source (default: http://localhost:9012)
 *   SOURCE_MINIO_ACCESS    MinIO access key for source (default: minioadmin)
 *   SOURCE_MINIO_SECRET    MinIO secret key for source (default: minioadmin)
 *   SOURCE_MINIO_BUCKET    Source bucket name (default: media-gallery)
 *   TARGET_DB_PATH         SQLite file path for target (default: ./src/server/data/mac-sync.db)
 *   TARGET_MINIO_ENDPOINT  MinIO endpoint for target (default: http://localhost:9000)
 *   TARGET_MINIO_ACCESS    MinIO access key for target (default: minioadmin)
 *   TARGET_MINIO_SECRET    MinIO secret key for target (default: minioadmin)
 *   TARGET_MINIO_BUCKET    Target bucket name (default: mac-sync)
 */

import { Database } from 'bun:sqlite';
import { Client as PgClient } from 'pg';
import { Client as MinioClient } from 'minio';
import { randomUUID } from 'crypto';
import type { Readable } from 'stream';

// ── Logger ────────────────────────────────────────────────────────────────────

type Level = 'info' | 'warn' | 'error';

function log(level: Level, msg: string, meta?: Record<string, unknown>): void {
  const line = { ts: new Date().toISOString(), level, msg, ...(meta ?? {}) };
  const stream = level === 'error' || level === 'warn' ? process.stderr : process.stdout;
  stream.write(`${JSON.stringify(line)}\n`);
}

// ── CLI / env arg parsing ─────────────────────────────────────────────────────

function arg(name: string, envKey: string, fallback: string): string {
  const flag = `--${name}=`;
  const fromCli = process.argv.find((a) => a.startsWith(flag));
  if (fromCli) return fromCli.slice(flag.length);
  return process.env[envKey] ?? fallback;
}

const DRY_RUN = process.argv.includes('--dry-run');

const SOURCE_DB_URL       = arg('source-db-url',          'SOURCE_DB_URL',          'postgres://localhost:25448/lilith_media_gallery');
const SOURCE_MINIO_ENDPOINT = arg('source-minio-endpoint','SOURCE_MINIO_ENDPOINT',  'http://localhost:9012');
const SOURCE_MINIO_ACCESS = arg('source-minio-access',    'SOURCE_MINIO_ACCESS',    'minioadmin');
const SOURCE_MINIO_SECRET = arg('source-minio-secret',    'SOURCE_MINIO_SECRET',    'minioadmin');
const SOURCE_MINIO_BUCKET = arg('source-minio-bucket',    'SOURCE_MINIO_BUCKET',    'media-gallery');

const TARGET_DB_PATH        = arg('target-db-path',         'TARGET_DB_PATH',         './src/server/data/mac-sync.db');
const TARGET_MINIO_ENDPOINT = arg('target-minio-endpoint',  'TARGET_MINIO_ENDPOINT',  'http://localhost:9000');
const TARGET_MINIO_ACCESS   = arg('target-minio-access',    'TARGET_MINIO_ACCESS',    'minioadmin');
const TARGET_MINIO_SECRET   = arg('target-minio-secret',    'TARGET_MINIO_SECRET',    'minioadmin');
const TARGET_MINIO_BUCKET   = arg('target-minio-bucket',    'TARGET_MINIO_BUCKET',    'mac-sync');

// ── MinIO client factory ──────────────────────────────────────────────────────

function makeMinioClient(endpoint: string, accessKey: string, secretKey: string): MinioClient {
  const url = new URL(endpoint);
  return new MinioClient({
    endPoint:  url.hostname,
    port:      url.port ? parseInt(url.port, 10) : (url.protocol === 'https:' ? 443 : 80),
    useSSL:    url.protocol === 'https:',
    accessKey,
    secretKey,
  });
}

// ── Source row types (from imajin-media-gallery schema) ───────────────────────

interface SourceDevice {
  id:          string;
  hardware_id: string;
  name:        string;
  platform:    string;
}

interface SourcePhoto {
  id:                string;
  device_id:         string;
  local_identifier:  string;
  media_type:        string;
  width:             number;
  height:            number;
  file_size:         number | null;
  duration_seconds:  number | null;
  captured_at:       Date;
  imported_at:       Date;
  storage_key:       string | null;
  original_filename: string | null;
  mime_type:         string | null;
  _targetId?:        string;
}

interface SourceAlbum {
  id:               string;
  device_id:        string;
  local_identifier: string;
  title:            string;
  album_type:       string;
  photo_count:      number;
}

interface SourceAlbumPhoto {
  album_id: string;
  photo_id:  string;
}

interface Counters {
  migrated: number;
  skipped:  number;
  failed:   number;
}

// ── Helpers ───────────────────────────────────────────────────────────────────

function isoOrNull(d: Date | null | undefined): string | null {
  return d ? d.toISOString() : null;
}

function effectiveMimeType(mediaType: string, mimeType: string | null): string {
  if (mimeType) return mimeType;
  return mediaType === 'video' || mediaType === 'live_photo' ? 'video/mp4' : 'image/jpeg';
}

function effectiveDuration(mediaType: string, durationSeconds: number | null): number | null {
  return mediaType === 'video' || mediaType === 'live_photo' ? durationSeconds : null;
}

// ── Main ──────────────────────────────────────────────────────────────────────

async function main(): Promise<void> {
  if (DRY_RUN) {
    log('info', 'DRY-RUN — no data will be written');
    log('info', 'Source DB', { url: SOURCE_DB_URL });
    log('info', 'Source MinIO', { endpoint: SOURCE_MINIO_ENDPOINT, bucket: SOURCE_MINIO_BUCKET });
    log('info', 'Target DB', { path: TARGET_DB_PATH });
    log('info', 'Target MinIO', { endpoint: TARGET_MINIO_ENDPOINT, bucket: TARGET_MINIO_BUCKET });
    log('info', 'Script wired up correctly. Remove --dry-run to execute.');
    return;
  }

  // ── Connect source (Postgres) ─────────────────────────────────────────────
  const srcPg = new PgClient({ connectionString: SOURCE_DB_URL });
  await srcPg.connect();
  log('info', 'Connected to source Postgres');

  // ── Open target (SQLite) ──────────────────────────────────────────────────
  const targetDb = new Database(TARGET_DB_PATH);
  targetDb.exec('PRAGMA journal_mode = WAL');
  targetDb.exec('PRAGMA foreign_keys = ON');
  targetDb.exec('PRAGMA busy_timeout = 5000');
  log('info', 'Opened target SQLite', { path: TARGET_DB_PATH });

  // ── MinIO clients ─────────────────────────────────────────────────────────
  const srcMinio = makeMinioClient(SOURCE_MINIO_ENDPOINT, SOURCE_MINIO_ACCESS, SOURCE_MINIO_SECRET);
  const tgtMinio = makeMinioClient(TARGET_MINIO_ENDPOINT, TARGET_MINIO_ACCESS, TARGET_MINIO_SECRET);

  const bucketExists = await tgtMinio.bucketExists(TARGET_MINIO_BUCKET);
  if (!bucketExists) {
    await tgtMinio.makeBucket(TARGET_MINIO_BUCKET);
    log('info', 'Created target bucket', { bucket: TARGET_MINIO_BUCKET });
  }

  // ── Device mapping ────────────────────────────────────────────────────────
  const { rows: srcDevices } = await srcPg.query<SourceDevice>(
    'SELECT id, hardware_id, name, platform FROM devices ORDER BY created_at ASC',
  );
  log('info', 'Loaded source devices', { count: srcDevices.length });

  const deviceIdMap = new Map<string, string>();

  for (const sd of srcDevices) {
    const existing = targetDb
      .prepare('SELECT id FROM devices WHERE hardware_id = ? AND revoked_at IS NULL')
      .get(sd.hardware_id) as { id: string } | undefined;

    if (existing) {
      deviceIdMap.set(sd.id, existing.id);
    } else {
      const newId    = randomUUID();
      const token    = `${randomUUID()}-${randomUUID()}`;
      targetDb
        .prepare(
          "INSERT INTO devices (id, name, hardware_id, token, platform, registered_at) " +
          "VALUES (?, ?, ?, ?, ?, datetime('now'))",
        )
        .run(newId, sd.name, sd.hardware_id, token, sd.platform ?? 'macos');
      deviceIdMap.set(sd.id, newId);
      log('info', 'Created target device', { hardwareId: sd.hardware_id, targetId: newId });
    }
  }

  // ── Albums ────────────────────────────────────────────────────────────────
  const { rows: srcAlbums } = await srcPg.query<SourceAlbum>(
    'SELECT id, device_id, local_identifier, title, album_type, photo_count FROM albums ORDER BY created_at ASC',
  );
  log('info', 'Loaded source albums', { count: srcAlbums.length });

  const albumIdMap    = new Map<string, string>();
  const albumCounters: Counters = { migrated: 0, skipped: 0, failed: 0 };

  for (const sa of srcAlbums) {
    const targetDeviceId = deviceIdMap.get(sa.device_id);
    if (!targetDeviceId) {
      log('warn', 'Album skipped — no target device', { albumId: sa.id, srcDeviceId: sa.device_id });
      albumCounters.skipped++;
      continue;
    }

    const existing = targetDb
      .prepare('SELECT id FROM albums WHERE device_id = ? AND external_id = ?')
      .get(targetDeviceId, sa.local_identifier) as { id: string } | undefined;

    if (existing) {
      albumIdMap.set(sa.id, existing.id);
      albumCounters.skipped++;
      continue;
    }

    const newId = randomUUID();
    try {
      targetDb
        .prepare(
          "INSERT INTO albums (id, device_id, external_id, name, photo_count, synced_at) " +
          "VALUES (?, ?, ?, ?, ?, datetime('now'))",
        )
        .run(newId, targetDeviceId, sa.local_identifier, sa.title, sa.photo_count);
      albumIdMap.set(sa.id, newId);
      albumCounters.migrated++;
    } catch (err) {
      log('error', 'Album insert failed', { albumId: sa.id, err: (err as Error).message });
      albumCounters.failed++;
    }
  }
  log('info', 'Albums done', albumCounters);

  // ── Photos ────────────────────────────────────────────────────────────────
  const { rows: srcPhotos } = await srcPg.query<SourcePhoto>(
    `SELECT id, device_id, local_identifier, media_type, width, height,
            file_size, duration_seconds, captured_at, imported_at,
            storage_key, original_filename, mime_type
     FROM photos
     ORDER BY captured_at ASC`,
  );
  log('info', 'Loaded source photos', { count: srcPhotos.length });

  const photoCounters: Counters = { migrated: 0, skipped: 0, failed: 0 };

  const checkPhotoStmt  = targetDb.prepare('SELECT id FROM photos WHERE device_id = ? AND external_id = ?');
  const insertPhotoStmt = targetDb.prepare(
    "INSERT INTO photos " +
    "  (id, device_id, external_id, filename, media_type, width, height, duration, " +
    "   taken_at, storage_bucket, storage_key, synced_at) " +
    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))",
  );

  for (let i = 0; i < srcPhotos.length; i++) {
    const sp = srcPhotos[i];

    if (i > 0 && i % 100 === 0) {
      log('info', 'Photo migration progress', { processed: i, total: srcPhotos.length, ...photoCounters });
    }

    const targetDeviceId = deviceIdMap.get(sp.device_id);
    if (!targetDeviceId) {
      photoCounters.skipped++;
      continue;
    }

    const existing = checkPhotoStmt.get(targetDeviceId, sp.local_identifier) as { id: string } | undefined;
    if (existing) {
      photoCounters.skipped++;
      continue;
    }

    const newId = randomUUID();
    let targetStorageKey: string | null = null;

    if (sp.storage_key) {
      try {
        const srcStream = await srcMinio.getObject(SOURCE_MINIO_BUCKET, sp.storage_key) as Readable;
        targetStorageKey = sp.storage_key;
        await tgtMinio.putObject(TARGET_MINIO_BUCKET, targetStorageKey, srcStream);
      } catch (err) {
        log('warn', 'Blob copy failed — row inserted without storage key', {
          photoId:    sp.id,
          storageKey: sp.storage_key,
          err:        (err as Error).message,
        });
        targetStorageKey = null;
      }
    }

    try {
      insertPhotoStmt.run(
        newId,
        targetDeviceId,
        sp.local_identifier,
        sp.original_filename ?? '',
        effectiveMimeType(sp.media_type, sp.mime_type),
        sp.width,
        sp.height,
        effectiveDuration(sp.media_type, sp.duration_seconds),
        isoOrNull(sp.captured_at),
        targetStorageKey ? TARGET_MINIO_BUCKET : null,
        targetStorageKey,
      );
      sp._targetId = newId;
      photoCounters.migrated++;
    } catch (err) {
      log('error', 'Photo insert failed', { photoId: sp.id, err: (err as Error).message });
      photoCounters.failed++;
    }
  }
  log('info', 'Photos done', photoCounters);

  // ── Album-photo junctions ─────────────────────────────────────────────────
  const { rows: srcJunctions } = await srcPg.query<SourceAlbumPhoto>(
    'SELECT album_id, photo_id FROM album_photos',
  );
  log('info', 'Loaded source album_photos junctions', { count: srcJunctions.length });

  const junctionCounters: Counters = { migrated: 0, skipped: 0, failed: 0 };

  const srcPhotoById = new Map<string, SourcePhoto>();
  for (const sp of srcPhotos) srcPhotoById.set(sp.id, sp);

  const insertJunctionStmt = targetDb.prepare(
    'INSERT OR IGNORE INTO album_photos (album_id, photo_id) VALUES (?, ?)',
  );

  for (const j of srcJunctions) {
    const targetAlbumId = albumIdMap.get(j.album_id);
    const targetPhotoId = srcPhotoById.get(j.photo_id)?._targetId;

    if (!targetAlbumId || !targetPhotoId) {
      junctionCounters.skipped++;
      continue;
    }

    try {
      insertJunctionStmt.run(targetAlbumId, targetPhotoId);
      junctionCounters.migrated++;
    } catch (err) {
      log('error', 'Junction insert failed', {
        albumId: j.album_id,
        photoId: j.photo_id,
        err:     (err as Error).message,
      });
      junctionCounters.failed++;
    }
  }
  log('info', 'Junctions done', junctionCounters);

  // ── Summary ───────────────────────────────────────────────────────────────
  await srcPg.end();
  targetDb.close();

  log('info', 'Migration complete', {
    devices:   deviceIdMap.size,
    albums:    albumCounters,
    photos:    photoCounters,
    junctions: junctionCounters,
  });

  const anyFailed =
    albumCounters.failed + photoCounters.failed + junctionCounters.failed > 0;
  if (anyFailed) {
    process.exit(1);
  }
}

main().catch((err: unknown) => {
  log('error', 'Fatal error', { err: (err as Error).message });
  process.exit(1);
});
