/**
 * Data-freshness helpers — the single source of truth for "is the local
 * activity data recent enough to bill/report against?" used by the
 * getDataFreshness capability, the invoice generation paths, and (later) any
 * UI banner. The staleness rule is a pure function so every surface agrees on
 * what "stale" means.
 *
 * Scoping (SECURITY): scan_history / sync_history are written by the desktop
 * sidecar to its single-tenant local DB. scan_history carries NO org or device
 * column (it links to scan_roots, which is also local-only), so it can never be
 * attributed to an org — reading it on the multi-tenant hub would leak other
 * orgs' state. Therefore:
 *   - Local mode (no organizationId): read scan/sync history directly.
 *   - Org/hub mode: NEVER read the unscoped local tables. Derive freshness from
 *     the org's own devices (devices.lastSyncAt) and device-scoped sync_history.
 */
import { and, desc, eq, inArray, isNotNull } from "drizzle-orm";
import type { BaseSQLiteDatabase } from "drizzle-orm/sqlite-core";
import type * as schema from "../db/schema";
import { devices, scanHistory, syncHistory } from "../db/schema";

/**
 * The shared Drizzle database type. Mirrors CapabilityDb: both the web hub
 * (LibSQLDatabase over Turso) and the desktop app (BaseSQLiteDatabase over
 * bun:sqlite) structurally satisfy this, so the queries run unchanged on both.
 */
// biome-ignore lint/suspicious/noExplicitAny: cross-driver common base (libsql + bun:sqlite)
type FreshnessDb = BaseSQLiteDatabase<any, any, typeof schema>;

/** Default staleness threshold: data older than this (minutes) is stale. */
export const DEFAULT_STALE_MINUTES = 60;

export interface DataFreshness {
  /** ISO timestamp of the most recent successful ingest/sync, or null if unknown. */
  lastIngestAt: string | null;
  /** ISO timestamp of the most recent completed sync, or null if unknown. */
  lastSyncAt: string | null;
  /** A scan is currently running (local mode only; always false on the hub). */
  ingestRunning: boolean;
  /** A sync is currently running. */
  syncRunning: boolean;
}

export interface GetDataFreshnessOptions {
  /**
   * Active organization. When null/undefined, the DB is a single-tenant local
   * DB and scan/sync history is read directly. When set, org/hub mode applies
   * and only device-scoped signals are read (no cross-org leak).
   */
  organizationId?: string | null;
  /**
   * Device IDs owned by the org's members (from getTeamDeviceFilter). Only used
   * in org mode:
   *   - null/undefined → cannot scope safely → empty freshness.
   *   - []             → org member with zero devices → empty freshness.
   *   - [...ids]       → scope sync/device freshness to these devices.
   */
  deviceIds?: string[] | null;
}

const EMPTY_FRESHNESS: DataFreshness = {
  lastIngestAt: null,
  lastSyncAt: null,
  ingestRunning: false,
  syncRunning: false,
};

const toIso = (d: Date | null | undefined): string | null =>
  d ? d.toISOString() : null;

/**
 * Read current data freshness. In local mode reports the most recent COMPLETED
 * scan (ingest) and sync; running rows surface as the *Running booleans so
 * callers can tell "stale because idle" from "stale but already refreshing".
 * In org/hub mode reports device-scoped sync freshness only (see file header).
 */
export async function getDataFreshness(
  db: FreshnessDb,
  opts: GetDataFreshnessOptions = {},
): Promise<DataFreshness> {
  if (opts.organizationId) {
    return getOrgDataFreshness(db, opts.deviceIds ?? null);
  }
  return getLocalDataFreshness(db);
}

async function getLocalDataFreshness(db: FreshnessDb): Promise<DataFreshness> {
  const [lastIngest] = await db
    .select({ completedAt: scanHistory.completedAt })
    .from(scanHistory)
    .where(
      and(
        eq(scanHistory.status, "completed"),
        isNotNull(scanHistory.completedAt),
      ),
    )
    .orderBy(desc(scanHistory.completedAt))
    .limit(1);

  const [ingestRunning] = await db
    .select({ id: scanHistory.id })
    .from(scanHistory)
    .where(eq(scanHistory.status, "running"))
    .limit(1);

  const [lastSync] = await db
    .select({ completedAt: syncHistory.completedAt })
    .from(syncHistory)
    .where(
      and(
        eq(syncHistory.status, "completed"),
        isNotNull(syncHistory.completedAt),
      ),
    )
    .orderBy(desc(syncHistory.completedAt))
    .limit(1);

  const [syncRunning] = await db
    .select({ id: syncHistory.id })
    .from(syncHistory)
    .where(eq(syncHistory.status, "running"))
    .limit(1);

  return {
    lastIngestAt: toIso(lastIngest?.completedAt),
    lastSyncAt: toIso(lastSync?.completedAt),
    ingestRunning: Boolean(ingestRunning),
    syncRunning: Boolean(syncRunning),
  };
}

async function getOrgDataFreshness(
  db: FreshnessDb,
  deviceIds: string[] | null,
): Promise<DataFreshness> {
  // No device list to scope by → nothing we can safely attribute to this org.
  if (!deviceIds || deviceIds.length === 0) return EMPTY_FRESHNESS;

  // Freshness of the org's data on the hub == the most recent device sync.
  // devices.lastSyncAt is the hub-side record of that (the local sync_history
  // rows live on the device, not here). scan_history is never read in org mode.
  const [latestDevice] = await db
    .select({ lastSyncAt: devices.lastSyncAt })
    .from(devices)
    .where(inArray(devices.id, deviceIds))
    .orderBy(desc(devices.lastSyncAt))
    .limit(1);

  const [syncRunning] = await db
    .select({ id: syncHistory.id })
    .from(syncHistory)
    .where(
      and(
        eq(syncHistory.status, "running"),
        inArray(syncHistory.deviceId, deviceIds),
      ),
    )
    .limit(1);

  const lastSyncAt = toIso(latestDevice?.lastSyncAt);
  return {
    // On the hub there is no separate "ingest"; the freshest the data can be is
    // the latest device sync, so ingest and sync freshness coincide.
    lastIngestAt: lastSyncAt,
    lastSyncAt,
    ingestRunning: false,
    syncRunning: Boolean(syncRunning),
  };
}

export interface IsStaleInput {
  /** ISO timestamp of the last successful ingest, or null if unknown/no history. */
  lastIngestAt: string | null;
  /** Reference "now" for the age check. Defaults to the current time. */
  asOf?: Date | string | number;
  /** Max allowed age in minutes before data is stale. */
  maxAgeMinutes?: number;
  /**
   * End of the report/invoice window. If the last ingest predates it, the
   * window isn't fully captured (recent work in the period is missing) → stale.
   */
  windowEnd?: Date | string | number | null;
}

/**
 * The shared staleness rule. Data is stale when:
 *  - the last ingest predates the report/invoice window end (coverage gap), OR
 *  - the last ingest is older than maxAgeMinutes relative to asOf.
 *
 * A null/unparseable lastIngestAt means "unknown" — no freshness history has
 * been recorded (fresh install, or the hub where scan history isn't tracked).
 * That is NOT treated as stale: we refuse to emit a false warning when valid
 * data may exist. Callers wanting to flag "never ingested" must do so
 * explicitly from the freshness fields, not by inferring it from isStale.
 */
export function isStale({
  lastIngestAt,
  asOf,
  maxAgeMinutes = DEFAULT_STALE_MINUTES,
  windowEnd,
}: IsStaleInput): boolean {
  if (!lastIngestAt) return false;

  const lastMs = new Date(lastIngestAt).getTime();
  if (Number.isNaN(lastMs)) return false;

  if (windowEnd != null) {
    const windowEndMs = new Date(windowEnd).getTime();
    if (!Number.isNaN(windowEndMs) && lastMs < windowEndMs) return true;
  }

  const asOfMs = asOf != null ? new Date(asOf).getTime() : Date.now();
  return asOfMs - lastMs > maxAgeMinutes * 60_000;
}

function formatAge(ms: number): string {
  const mins = Math.max(0, Math.floor(ms / 60_000));
  if (mins < 1) return "just now";
  if (mins < 60) return `${mins}m ago`;
  const hours = Math.floor(mins / 60);
  if (hours < 24) return `${hours}h ago`;
  const days = Math.floor(hours / 24);
  return `${days}d ago`;
}

/**
 * Build a non-blocking, human-readable staleness warning for surfaces that
 * generate billable output (invoices/reports). Returns null when the data is
 * fresh OR when freshness is unknown (no history recorded) — same rule as
 * {@link isStale}, so all surfaces agree and the hub never emits a false
 * "stale" for data it simply doesn't track scan history for.
 */
export function getStaleWarning(
  freshness: Pick<DataFreshness, "lastIngestAt">,
  opts: Omit<IsStaleInput, "lastIngestAt"> = {},
): string | null {
  const { lastIngestAt } = freshness;
  if (!lastIngestAt || !isStale({ lastIngestAt, ...opts })) return null;

  const asOfMs = opts.asOf != null ? new Date(opts.asOf).getTime() : Date.now();
  const ageMs = asOfMs - new Date(lastIngestAt).getTime();
  return `Data last ingested ${formatAge(ageMs)} — recent work may be missing; run sync/ingest.`;
}