import { randomUUID } from 'node:crypto';
import { and, eq, inArray } from 'drizzle-orm';
import { type DbClient, getDb } from '../db/client';
import {
  type AllocatableZone,
  type NetworkZone,
  containerServices,
  ipAllocations,
  machines,
  moduleInfrastructure,
  moduleSystems,
} from '../db/schema';
import { decryptSecret, encryptSecret } from '../secrets/encryption';
import { getOrCreateMasterKey } from '../secrets/master-key';
import type { Machine, MachineRole, NetworkInterface } from '../types/infrastructure';
import { EncryptionEnvelopeSchema, parseJsonWithValidation } from '../validation/schemas';

/**
 * Machine filters
 */
export interface MachineFilters {
  zone?: NetworkZone;
}

/**
 * Add a new machine to the pool
 */
export async function addMachine(
  machine: Omit<
    Machine,
    'id' | 'createdAt' | 'updatedAt' | 'sshKeyEncrypted' | 'role' | 'interfaces'
  > & {
    sshKey: string;
    role?: MachineRole;
    interfaces?: NetworkInterface[];
  },
): Promise<Machine> {
  const db = getDb();
  const id = randomUUID();
  const now = new Date();

  // Encrypt SSH key
  const masterKey = await getOrCreateMasterKey();
  const encrypted = encryptSecret(machine.sshKey, masterKey);

  const values = {
    id,
    hostname: machine.hostname,
    zone: machine.zone,
    ipAddress: machine.ipAddress,
    sshUser: machine.sshUser,
    sshKeyEncrypted: JSON.stringify(encrypted),
    hardware: machine.hardware, // Drizzle auto-stringifies with mode: 'json'
    role: machine.role ?? 'host',
    interfaces: machine.interfaces ?? [],
    earmarkedModule: machine.earmarkedModule || null,
    createdAt: now,
    updatedAt: now,
  };

  await db.insert(machines).values(values);

  return {
    id,
    hostname: machine.hostname,
    zone: machine.zone,
    ipAddress: machine.ipAddress,
    sshUser: machine.sshUser,
    sshKeyEncrypted: JSON.stringify(encrypted),
    hardware: machine.hardware,
    role: (machine.role ?? 'host') as MachineRole,
    interfaces: (machine.interfaces ?? []) as NetworkInterface[],
    earmarkedModule: machine.earmarkedModule || null,
    apiOnly: false, // defaults to false; toggled later via separate flow
    createdAt: now,
    updatedAt: now,
  };
}

/**
 * Map a database row to a Machine object
 */
function rowToMachine(row: typeof machines.$inferSelect): Machine {
  const hardware = row.hardware || { cpu_cores: 0, memory_mb: 0, disk_gb: 0, arch: 'unknown' };
  const interfaces = Array.isArray(row.interfaces) ? (row.interfaces as NetworkInterface[]) : [];

  return {
    id: row.id,
    hostname: row.hostname,
    zone: row.zone as NetworkZone,
    ipAddress: row.ipAddress,
    sshUser: row.sshUser,
    sshKeyEncrypted: row.sshKeyEncrypted,
    hardware,
    role: (row.role as MachineRole) || 'host',
    interfaces,
    earmarkedModule: row.earmarkedModule ?? undefined,
    apiOnly: row.apiOnly,
    createdAt: new Date(row.createdAt),
    updatedAt: new Date(row.updatedAt),
  };
}

/**
 * Get machine by ID
 */
export async function getMachine(id: string): Promise<Machine | null> {
  const db = getDb();
  const result = await db.select().from(machines).where(eq(machines.id, id)).limit(1);
  if (result.length === 0) return null;
  return rowToMachine(result[0]);
}

/**
 * Get machine by hostname
 */
export async function getMachineByHostname(hostname: string): Promise<Machine | null> {
  const db = getDb();
  const result = await db.select().from(machines).where(eq(machines.hostname, hostname)).limit(1);
  if (result.length === 0) return null;
  return rowToMachine(result[0]);
}

/**
 * Get machine by IP address (checks primary IP and interface IPs)
 */
export async function getMachineByIp(ipAddress: string): Promise<Machine | null> {
  const db = getDb();
  const result = await db.select().from(machines).where(eq(machines.ipAddress, ipAddress)).limit(1);
  if (result.length > 0) return rowToMachine(result[0]);

  // Fallback: search interface IPs
  const allMachines = await db.select().from(machines);
  for (const row of allMachines) {
    const ifaces = Array.isArray(row.interfaces) ? (row.interfaces as NetworkInterface[]) : [];
    if (ifaces.some((iface) => iface.ipAddress === ipAddress)) {
      return rowToMachine(row);
    }
  }
  return null;
}

/**
 * Get the machine earmarked for a specific module, if any
 */
export async function getEarmarkedMachineForModule(moduleId: string): Promise<Machine | null> {
  const db = getDb();
  const result = await db
    .select()
    .from(machines)
    .where(eq(machines.earmarkedModule, moduleId))
    .limit(1);
  if (result.length === 0) return null;
  return rowToMachine(result[0]);
}

/**
 * Find the machine that will be used for a module's $machine: derivation.
 * Priority: earmarked machine > first available machine matching role in zone.
 * This is a "preview" of infrastructure selection for config interview purposes.
 */
export async function findMachineForModule(
  moduleId: string,
  zone?: NetworkZone,
  requiredRole?: MachineRole,
): Promise<Machine | null> {
  // 1. Check for earmarked machine
  const earmarked = await getEarmarkedMachineForModule(moduleId);
  if (earmarked) return earmarked;

  // 2. Look for a matching machine in the pool
  if (!zone) return null;
  const allMachines = await listMachines({ zone });

  const db = getDb();
  for (const machine of allMachines) {
    // Skip machines earmarked for other modules
    if (machine.earmarkedModule && machine.earmarkedModule !== moduleId) continue;
    // Skip machines occupied by other modules. Derived, not read from a stored
    // snapshot — this preview offered an occupied machine's address with no
    // error at all while the snapshot said the box was free (celilo#773).
    const occupants = getModulesOnMachine(machine.id, db);
    if (occupants.length > 0 && !occupants.includes(moduleId)) continue;
    // Match role if required
    if (requiredRole && (machine.role || 'host') !== requiredRole) continue;
    return machine;
  }

  return null;
}

/**
 * Update the earmarked module for a machine
 */
export async function updateMachineEarmark(
  machineId: string,
  earmarkedModule: string | null,
): Promise<void> {
  const db = getDb();
  await db
    .update(machines)
    .set({ earmarkedModule, updatedAt: new Date() })
    .where(eq(machines.id, machineId));
}

/**
 * Get decrypted SSH key for a machine
 */
export async function getMachineSshKey(machineId: string): Promise<string> {
  const machine = await getMachine(machineId);
  if (!machine) {
    throw new Error(`Machine not found: ${machineId}`);
  }

  const masterKey = await getOrCreateMasterKey();
  const encrypted = parseJsonWithValidation(
    machine.sshKeyEncrypted,
    EncryptionEnvelopeSchema,
    'SSH key encryption envelope',
  );
  return decryptSecret(encrypted, masterKey);
}

/**
 * List machines with optional filters
 */
export async function listMachines(filters?: MachineFilters): Promise<Machine[]> {
  const db = getDb();

  // Apply zone filter
  const results = filters?.zone
    ? await db.select().from(machines).where(eq(machines.zone, filters.zone))
    : await db.select().from(machines);

  return results.map(rowToMachine);
}

/**
 * Return every machine in any of the given zones — the discovery
 * query the aspect runner uses to find fan-out targets per
 * openspec/specs/base-module-aspects/spec.md.
 *
 * Filters:
 * - `excludeApiOnly` (default `true`): drops machines marked
 *   `api_only` (the greenwave / ISP-modem case). Aspects use
 *   Ansible, so api_only systems are unreachable. openspec/specs/base-module-aspects/spec.md
 *   D8.
 * - `excludeHostnames` (default `[]`): drops named hostnames.
 *   Aspect authors that need to skip the system running their own
 *   primary deploy pass it here; the framework doesn't auto-skip
 *   (openspec/specs/base-module-aspects/spec.md D3).
 *
 * NOTE on container_service systems: this Phase 1 implementation
 * covers MACHINE-based systems only (the machines table). LXCs/VMs
 * provisioned via container_service live in
 * `module_infrastructure` and inherit zone from `ip_allocations`;
 * supporting them is a future iteration. For Phase 1's
 * aspect-fanout test (and forgejo's e2e), all targets are machine
 * pool entries, so this is sufficient.
 */
export async function getSystemsByZone(
  zones: string[],
  options: { excludeApiOnly?: boolean; excludeHostnames?: string[] } = {},
): Promise<Machine[]> {
  if (zones.length === 0) return [];
  const db = getDb();
  const excludeApiOnly = options.excludeApiOnly ?? true;
  const excludeHostnames = new Set(options.excludeHostnames ?? []);

  const rows = await db
    .select()
    .from(machines)
    .where(inArray(machines.zone, zones as NetworkZone[]));

  return rows
    .map(rowToMachine)
    .filter((m) => !(excludeApiOnly && m.apiOnly))
    .filter((m) => !excludeHostnames.has(m.hostname));
}

/**
 * A container_service-provisioned system (Proxmox LXC, Digital
 * Ocean droplet, etc.) — the complement to a machine-pool entry.
 * Surfaced by `getContainerSystemsByZone` for SC5 Proxmox
 * reconciliation: each LXC's owning module has terraform state we
 * may need to update.
 *
 * `containerMetadata` is the raw JSON from the db (vmid, droplet
 * ID, container_ip, etc. — provider-specific shape); callers
 * inspect it as needed.
 */
export interface ContainerSystem {
  /** UUID of the module_infrastructure row. */
  infrastructureId: string;
  /** Module that owns this provisioned system (and its terraform state). */
  moduleId: string;
  /** Container service this system was provisioned through. */
  serviceId: string;
  providerName: 'proxmox' | 'digitalocean' | 'aws' | 'gcp' | 'azure';
  /** Zone from `ip_allocations` (the authoritative zone for the LXC's IP). */
  zone: NetworkZone;
  /** Container IP in CIDR (e.g., "10.0.20.42/24"). May be null if allocation absent. */
  containerIp: string | null;
  containerMetadata: Record<string, unknown> | null;
  apiOnly: boolean;
}

/**
 * Enumerate container_service-provisioned systems whose IP-allocation
 * zone matches the input. Used by SC5's Proxmox reconciler to find
 * LXCs whose terraform config may need rewriting when an aspect
 * declares `proxmox_reconcile.tfvars`.
 *
 * Filters api_only systems by default. The aspect runner reads the
 * same row to decide if Ansible should even reach the system.
 *
 * Phase 1 simulator note: cele2e test environments add systems via
 * `celilo machine add`, which writes to the machines table — NOT
 * module_infrastructure. So this function returns [] for those
 * tests. It only matters in production where container_service
 * (Proxmox) is real.
 */
export async function getContainerSystemsByZone(
  zones: string[],
  options: { excludeApiOnly?: boolean } = {},
): Promise<ContainerSystem[]> {
  if (zones.length === 0) return [];
  const db = getDb();
  const excludeApiOnly = options.excludeApiOnly ?? true;

  // ip_allocations is the authoritative source for which LXC sits
  // in which zone (Proxmox doesn't expose zone directly; celilo's
  // IPAM is what assigned it). Join it to module_infrastructure to
  // get the rest of the metadata.
  const rows = await db
    .select({
      infraId: moduleInfrastructure.id,
      moduleId: moduleInfrastructure.moduleId,
      serviceId: moduleInfrastructure.serviceId,
      containerMetadata: moduleInfrastructure.containerMetadata,
      apiOnly: moduleInfrastructure.apiOnly,
      ipZone: ipAllocations.zone,
      containerIp: ipAllocations.containerIp,
      providerName: containerServices.providerName,
    })
    .from(moduleInfrastructure)
    .innerJoin(ipAllocations, eq(ipAllocations.moduleId, moduleInfrastructure.moduleId))
    .innerJoin(containerServices, eq(containerServices.id, moduleInfrastructure.serviceId))
    .where(
      and(
        eq(moduleInfrastructure.infrastructureType, 'container_service'),
        // ip_allocations.zone is narrower than NetworkZone (no 'external');
        // safe to cast — any 'external' input would just match zero rows.
        inArray(ipAllocations.zone, zones as AllocatableZone[]),
      ),
    );

  return rows
    .filter((r) => !(excludeApiOnly && r.apiOnly))
    .filter((r): r is typeof r & { serviceId: string } => r.serviceId !== null)
    .map((r) => ({
      infrastructureId: r.infraId,
      moduleId: r.moduleId,
      serviceId: r.serviceId,
      providerName: r.providerName,
      zone: r.ipZone,
      containerIp: r.containerIp ?? null,
      containerMetadata: r.containerMetadata ?? null,
      apiOnly: r.apiOnly,
    }));
}

/**
 * Remove a machine from the pool
 */
export async function removeMachine(id: string): Promise<void> {
  const db = getDb();

  await db.delete(machines).where(eq(machines.id, id));
}

/**
 * Which modules occupy this machine, ANSWERED AT THE POINT OF USE (celilo#773).
 *
 * There used to be a `machines.assigned_module_ids` array holding this. It had
 * one writer, which only ever appended, no reader that reconciled it, and no
 * removal path at all — and it was load-bearing, because placement refuses a
 * machine whose list is non-empty and does not name the module being placed.
 * It diverged in both directions on the live fleet:
 *
 *   - OVER-recorded, permanently: removing a module left its id behind, so
 *     placement rejected an empty machine citing a module that no longer
 *     existed, and `machine remove` refused to remove it. No command could
 *     clear the entry; the only remedy was editing the database.
 *   - UNDER-recorded: `briq` hosted a VERIFIED `iptables` and reported
 *     "None (available)", so a second module could be placed onto an occupied
 *     box — the exact collision the filter exists to prevent — and the
 *     `machine remove` guard was equally blind. Only a separately-set earmark
 *     was keeping placement off it.
 *
 * Both silent. Deriving costs one indexed query and cannot drift, and removal
 * frees the machine for free: both source tables cascade on the module row.
 *
 * ⚠️ The UNION of both tables is deliberate, and is not the "two sources that
 * disagree" problem the old column was. Neither is a copy — both are FK columns
 * owned by the deploy path — and for a SAFETY guard the liberal read is the
 * correct one: reporting an occupied box as free is how two modules land on one
 * machine, while reporting a free box as occupied merely sends the operator to
 * look. `module_infrastructure` records the CLAIM at selection time and
 * `module_systems` the realized deployment, so a module mid-deploy is visible
 * in the first before it appears in the second.
 */
export function getModulesOnMachine(machineId: string, db: DbClient = getDb()): string[] {
  const claimed = db
    .select({ moduleId: moduleInfrastructure.moduleId })
    .from(moduleInfrastructure)
    .where(eq(moduleInfrastructure.machineId, machineId))
    .all();
  const deployed = db
    .select({ moduleId: moduleSystems.moduleId })
    .from(moduleSystems)
    .where(eq(moduleSystems.machineId, machineId))
    .all();

  return [...new Set([...claimed, ...deployed].map((r) => r.moduleId))].sort();
}

// `getModuleResourcesOnMachine` is deleted (celilo#773, Rule 1.2 / 7.6).
//
// It queried `module_infrastructure`, discarded the result into an unused
// variable, and returned `{cpu: 0, memory: 0, disk: 0}` behind a TODO. Its
// three callers subtracted that zero from the machine's hardware before
// comparing against the module's requirements — so the subtraction was
// ceremony and the comparison was really "is this machine big enough at all".
//
// That is a genuinely useful check and it survives, stated plainly, in
// `machineHasCapacity`. What is gone is the appearance of multi-tenant capacity
// accounting that never accounted for anything: a gate nobody has seen fail is
// not a gate, and one that cannot fail reads as protection that is not there.
//
// Real accounting needs a decision this issue does not make — whether a
// module's draw is its manifest MINIMUM (`requires.system`) or its deployed
// size, which for pool machines celilo does not own. That belongs to whoever
// builds capacity-aware placement for the machine pool.