/**
 * Backup metadata service - CRUD operations for backup records.
 * Tracks both system state and module data backups.
 */

import { randomUUID } from 'node:crypto';
import { and, desc, eq, gt, gte, isNotNull, like } from 'drizzle-orm';
import { getDb } from '../db/client';
import { type Backup, type BackupStatus, type BackupType, backups } from '../db/schema';
import type { BackupHistory } from './backup-schedule';

/** Short ID length used for display and lookup */
export const SHORT_ID_LENGTH = 8;

export interface CreateBackupParams {
  moduleId?: string | null;
  storageId: string;
  storagePath: string;
  backupType: BackupType;
  moduleVersion?: string;
}

export interface BackupRecord extends Backup {
  id: string;
}

/**
 * Create a new backup record (status: in_progress)
 */
export function createBackupRecord(params: CreateBackupParams): BackupRecord {
  const db = getDb();
  const id = randomUUID();
  const now = new Date();

  const values = {
    id,
    moduleId: params.moduleId ?? null,
    storageId: params.storageId,
    storagePath: params.storagePath,
    backupType: params.backupType,
    moduleVersion: params.moduleVersion ?? null,
    schemaVersion: null,
    sizeBytes: null,
    metadata: {},
    status: 'in_progress' as BackupStatus,
    errorMessage: null,
    // Recorded so the staging reaper can distinguish a live backup from one
    // whose process was killed before its `finally` ran — see
    // services/backup-staging.ts.
    pid: process.pid,
    startedAt: now,
    completedAt: null,
  };

  db.insert(backups).values(values).run();

  return { ...values, startedAt: now, completedAt: null } as BackupRecord;
}

/**
 * Mark a backup as completed with final metadata
 */
export function completeBackup(
  id: string,
  params: {
    sizeBytes: number;
    metadata?: Record<string, unknown>;
    schemaVersion?: string;
  },
): void {
  const db = getDb();
  db.update(backups)
    .set({
      status: 'completed' as BackupStatus,
      sizeBytes: params.sizeBytes,
      metadata: params.metadata ?? {},
      schemaVersion: params.schemaVersion ?? null,
      completedAt: new Date(),
    })
    .where(eq(backups.id, id))
    .run();
}

/**
 * Mark a backup as failed
 */
export function failBackup(id: string, errorMessage: string): void {
  const db = getDb();
  db.update(backups)
    .set({
      status: 'failed' as BackupStatus,
      errorMessage,
      completedAt: new Date(),
    })
    .where(eq(backups.id, id))
    .run();
}

/**
 * Get a backup by ID or short ID prefix.
 * Supports both full UUIDs and 8-char short IDs.
 */
export function getBackup(id: string): Backup | null {
  const db = getDb();

  // Try exact match first
  const exact = db.select().from(backups).where(eq(backups.id, id)).limit(1).all();
  if (exact.length > 0) return exact[0];

  // Try prefix match for short IDs
  if (id.length < 36) {
    const prefixed = db
      .select()
      .from(backups)
      .where(like(backups.id, `${id}%`))
      .all();
    if (prefixed.length === 1) return prefixed[0];
    if (prefixed.length > 1) {
      throw new Error(
        `Ambiguous backup ID '${id}' matches ${prefixed.length} backups. Use a longer prefix.`,
      );
    }
  }

  // Try name match (case-insensitive)
  const byName = db.select().from(backups).where(like(backups.name, id)).all();
  if (byName.length === 1) return byName[0];
  if (byName.length > 1) {
    throw new Error(
      `Ambiguous backup name '${id}' matches ${byName.length} backups. Use a unique name or backup ID.`,
    );
  }

  return null;
}

/**
 * List backups with optional module filter and limit
 */
export function listBackups(options?: {
  moduleId?: string;
  limit?: number;
  /**
   * Only attempts started at or after this instant (epoch ms).
   *
   * A WINDOW rather than a bigger `limit`, because the two answer different
   * questions and only one of them is stable. "The last 500 attempts" spans a
   * week for a module failing hourly and a year for one succeeding daily, so a
   * caller asking for a fixed period cannot express it as a count. The console
   * needs a period: it draws days, and a row missing from its answer renders as
   * a day on which nothing ran.
   */
  since?: number;
}): Backup[] {
  const db = getDb();
  const limit = options?.limit ?? 20;

  const filters = [
    options?.moduleId ? eq(backups.moduleId, options.moduleId) : undefined,
    options?.since !== undefined ? gte(backups.startedAt, new Date(options.since)) : undefined,
  ].filter((clause) => clause !== undefined);

  const query = db.select().from(backups);
  const filtered = filters.length > 0 ? query.where(and(...filters)) : query;
  return filtered.orderBy(desc(backups.startedAt)).limit(limit).all();
}

/**
 * What a module's backup history says, for the due-ness decision.
 *
 * Only `module_data` rows count. A module's cadence is about its own data; a
 * system backup that happens to name it must not make it look covered.
 *
 * Split from the decision itself so the policy stays pure and testable
 * (Rule 2.3) — see `isBackupDueFromHistory`.
 */
export function loadBackupHistory(moduleId: string): BackupHistory {
  const db = getDb();
  const forThisModule = and(eq(backups.moduleId, moduleId), eq(backups.backupType, 'module_data'));

  // Ordered and measured by `completedAt`, matching the freshness audit
  // (services/audit/backup-source.ts). Measuring due-ness from `startedAt`
  // while the audit measured from `completedAt` meant the two paths could
  // disagree about a module's age by the duration of the backup itself —
  // design.md D6. A `completed` row with no `completedAt` is not a backup
  // either path will count.
  const [lastSuccess] = db
    .select()
    .from(backups)
    .where(and(forThisModule, eq(backups.status, 'completed'), isNotNull(backups.completedAt)))
    .orderBy(desc(backups.completedAt))
    .limit(1)
    .all();

  const [lastAttempt] = db
    .select()
    .from(backups)
    .where(forThisModule)
    .orderBy(desc(backups.startedAt))
    .limit(1)
    .all();

  // Attempts since the last success — every row newer than it, or every row at
  // all when there has never been one. An `in_progress` row is included: the
  // decision only ever uses this to back OFF, so counting a live attempt is
  // the safe direction.
  const since = lastSuccess
    ? and(forThisModule, gt(backups.startedAt, lastSuccess.startedAt))
    : forThisModule;

  return {
    lastSuccessAt: lastSuccess?.completedAt ?? null,
    lastAttemptAt: lastAttempt?.startedAt ?? null,
    consecutiveFailures: db.select({ id: backups.id }).from(backups).where(since).all().length,
  };
}

/**
 * Every backup record still claiming to be in progress, oldest first.
 *
 * Feeds the record-resolution pass (`resolveAbandonedBackups`), which corrects
 * the ones whose process is gone. Unbounded on purpose — there is no sensible
 * limit on "how many lies to fix", and celilo-mgr had 107 of them.
 */
export function listInProgressBackups(): Backup[] {
  const db = getDb();
  return db
    .select()
    .from(backups)
    .where(eq(backups.status, 'in_progress'))
    .orderBy(backups.startedAt)
    .all();
}

/**
 * List completed backups for a specific module, ordered newest first
 */
export function listCompletedBackupsForModule(moduleId: string): Backup[] {
  const db = getDb();
  return db
    .select()
    .from(backups)
    .where(eq(backups.moduleId, moduleId))
    .orderBy(desc(backups.startedAt))
    .all()
    .filter((b) => b.status === 'completed');
}

/**
 * Set or clear a human-readable name on a backup
 */
export function updateBackupName(id: string, name: string | null): void {
  const db = getDb();
  db.update(backups).set({ name }).where(eq(backups.id, id)).run();
}

/**
 * Delete a backup record
 */
export function deleteBackupRecord(id: string): void {
  const db = getDb();
  db.delete(backups).where(eq(backups.id, id)).run();
}

/**
 * Format bytes into human-readable size
 */
export function formatSize(bytes: number | null): string {
  if (bytes === null || bytes === 0) return '0 B';
  const units = ['B', 'KB', 'MB', 'GB'];
  let size = bytes;
  let unitIndex = 0;
  while (size >= 1024 && unitIndex < units.length - 1) {
    size /= 1024;
    unitIndex++;
  }
  return `${size.toFixed(unitIndex === 0 ? 0 : 1)} ${units[unitIndex]}`;
}