import { INSTANCE_STATES, type InstanceState } from '@celilo/capabilities';
import { sql } from 'drizzle-orm';
import {
  index,
  integer,
  primaryKey,
  sqliteTable,
  text,
  unique,
  uniqueIndex,
} from 'drizzle-orm/sqlite-core';

/**
 * Module lifecycle states
 * IMPORTED, VALIDATED, CONFIGURED, GENERATING, ERROR, DEPLOYING, INSTALLED, VERIFIED, UNINSTALLING, PAUSED
 *
 * `PAUSED` is a real member of this union rather than a side flag, and that is
 * the point (openspec/changes/module-pause-lifecycle/design.md D1): adding it
 * makes the type-checker enumerate every site that must now consider
 * paused-ness. A `pausedAt`-only flag would leave every `state === 'VERIFIED'`
 * comparison silently compiling while quietly reading a paused module as live.
 *
 * There is deliberately no `prePauseState`: pause is legal only from a settled
 * state and unpause redeploys, so the deploy path decides the resulting state
 * and there is nothing to restore.
 */
export type ModuleState =
  | 'IMPORTED'
  | 'VALIDATED'
  | 'CONFIGURED'
  | 'GENERATING'
  | 'DEPLOYING'
  | 'INSTALLED'
  | 'VERIFIED'
  | 'ERROR'
  | 'UNINSTALLING'
  | 'PAUSED';

/**
 * States a module may be paused FROM (design D1). `ERROR` is deliberately
 * included: quiescing a broken module to stop alert noise while working on it
 * is legitimate, and refusing would push the operator toward silencing those
 * alerts by some less visible route.
 */
export const PAUSABLE_STATES = [
  'INSTALLED',
  'VERIFIED',
  'ERROR',
] as const satisfies readonly ModuleState[];

/**
 * States that mean "a lifecycle transition is under way". Pausing one of these
 * would strand the transition, so pause is refused with a distinct message from
 * the never-deployed case.
 */
export const IN_FLIGHT_STATES = [
  'GENERATING',
  'DEPLOYING',
  'UNINSTALLING',
] as const satisfies readonly ModuleState[];

/**
 * Modules table - stores module metadata and manifest data
 * @owner celilo — the module registry itself
 */
export const modules = sqliteTable(
  'modules',
  {
    id: text('id').primaryKey(),
    name: text('name').notNull(),
    version: text('version').notNull(),
    description: text('description'),
    state: text('state').$type<ModuleState>().notNull().default('IMPORTED'),
    manifestData: text('manifest_data', { mode: 'json' })
      .$type<Record<string, unknown>>()
      .notNull(),
    sourcePath: text('source_path').notNull(),
    importedAt: integer('imported_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
    updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
    errorMessage: text('error_message'),
    /**
     * When the module was paused. Null unless `state = 'PAUSED'`. The state alone
     * cannot answer "how long", and the DURATION is what makes a forgotten pause
     * detectable (design D7) — every paused-module report carries the age.
     */
    pausedAt: integer('paused_at', { mode: 'timestamp' }),
    /** Operator-supplied explanation, so the row explains itself. */
    pauseReason: text('pause_reason'),
  },
  (table) => ({
    // Every management-API response asks "is anything paused?" (design D7), so
    // that lookup must stay a single indexed hit rather than a table scan.
    stateIdx: index('modules_state_idx').on(table.state),
  }),
);

/**
 * Module configuration - user-provided key-value pairs.
 *
 * Two columns, with strictly different roles:
 *   - `valueJson`: JSON-encoded canonical value, ALWAYS populated by
 *     the write path. This is the source of truth — readers MUST
 *     consume `valueJson` and `JSON.parse` it. Type fidelity is
 *     preserved here: `number` round-trips as `number`, `boolean` as
 *     `boolean`, arrays/objects as their JSON shape.
 *   - `value`: human-readable string form, used only by the CLI for
 *     display (`module config get`). NEVER used as a source of typed
 *     data — that path leaked stringly-typed numbers through to
 *     capability calls (see commit history for the forgejo SSH:2222
 *     case, where ssh_external_port was read back as "2222" the
 *     string and silently broke firewall.exposeService).
 *
 * Historically `value` was the canonical storage for primitives and
 * `valueJson` only for arrays/objects. That was Defect 1 — TS types
 * generated from the manifest claimed `number` while the runtime
 * value was string. Now closed.
 * @owner celilo — generic per-module KV; the migration destination (T9)
 */
export const moduleConfigs = sqliteTable(
  'module_configs',
  {
    id: integer('id').primaryKey({ autoIncrement: true }),
    moduleId: text('module_id')
      .notNull()
      .references(() => modules.id, { onDelete: 'cascade' }),
    key: text('key').notNull(),
    value: text('value').notNull(),
    valueJson: text('value_json'), // JSON for complex types (arrays, objects)
    /**
     * Who owns the row (hook-owned-state design D7 — one column shared with
     * `derived-value-recomputation`). 'hook' means the owning module's own
     * hook wrote it via `context.config.set`, and no operator path may touch
     * it. NULL is every row written before the column existed — operator
     * intent, legacy derived seeds, and framework keys alike — and stays the
     * value for operator writes until derived-value-recomputation lands its
     * own classification.
     */
    source: text('source'),
    createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
    updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  },
  (table) => ({
    uniqueModuleKey: unique().on(table.moduleId, table.key),
  }),
);

/**
 * Capabilities table - stores registered capabilities provided by modules
 * Example: namecheap module provides dns_registrar capability
 * @owner celilo — the brokering table, who provides what (T8)
 */
export const capabilities = sqliteTable('capabilities', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  moduleId: text('module_id')
    .notNull()
    .references(() => modules.id, { onDelete: 'cascade' }),
  capabilityName: text('capability_name').notNull(),
  version: text('version').notNull(),
  data: text('data', { mode: 'json' }).$type<Record<string, unknown>>().notNull(),
  /** JSON array of zone names this capability applies to, or null for zone-agnostic */
  zones: text('zones', { mode: 'json' }).$type<string[] | null>(),
  registeredAt: integer('registered_at', { mode: 'timestamp' })
    .notNull()
    .default(sql`(unixepoch())`),
});

/**
 * Capability bindings — which provider a consumer actually resolved to.
 *
 * `capabilities` is provider-side only: it answers "who COULD provide this",
 * never "who does this module actually use". Resolution happened at hook time
 * and vanished into the generated project. Every consumer of that fact
 * reconstructed the same approximation — the consumer's `requires` + `optional`
 * crossed with `capabilities` — which names every provider a module MIGHT have
 * bound to. For a module declaring four optional capabilities and using one,
 * that is three false edges and no way to tell which.
 *
 * A row is written when a consumer's hook first CALLS a method on an injected
 * capability, not when the loader resolves one. The loader deliberately injects
 * every registered capability regardless of what the consumer declared
 * (`loadCapabilityFunctions`, "not just required ones"), so resolution is a
 * superset of the declared set and recording it would restate the
 * approximation. The call is the binding.
 *
 * Unique on (consumer, capability): a redeploy re-asserts the row rather than
 * duplicating it, and a provider swap rewrites `provider_module_id` in place.
 * Cascaded on the consumer: the binding dies with the module that made it.
 *
 * This does NOT replace the permissive set. `planConsumerCleanup` must still
 * notify every provider that might hold minted state, including ones this table
 * has no row for.
 *
 * @owner celilo — cross-module bookkeeping of who resolved to whom (T8, beside `capabilities`)
 */
export const capabilityBindings = sqliteTable(
  'capability_bindings',
  {
    id: integer('id').primaryKey({ autoIncrement: true }),
    consumerModuleId: text('consumer_module_id')
      .notNull()
      .references(() => modules.id, { onDelete: 'cascade' }),
    capabilityName: text('capability_name').notNull(),
    providerModuleId: text('provider_module_id').notNull(),
    /** Last time the consumer called into this provider. */
    boundAt: integer('bound_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  },
  (table) => ({
    consumerCapabilityUnique: uniqueIndex('capability_bindings_consumer_capability_idx').on(
      table.consumerModuleId,
      table.capabilityName,
    ),
  }),
);

/**
 * One build-bus `on_upstream_publish` hook execution (services/build-bus/hook-dispatcher.ts).
 *
 * The self-update path had no durable record: outcomes were console output
 * inside the receiver daemon, so a CLI that never upgraded looked like a
 * healthy fleet (celilo#1304). One row per (event, module, hook) run — the
 * newest row answers "did the self-update for @celilo/cli@X fire and succeed?".
 *
 * `exit_code` is NULL when the script never ran to an exit (spawn failure).
 * Not an event-bus delivery: hooks run from the receiver's in-process
 * dispatcher, which no `deliveries` row was ever written for.
 *
 * @owner celilo — build-bus self-update audit trail (celilo#1304)
 */
export const buildBusHookRuns = sqliteTable(
  'build_bus_hook_runs',
  {
    id: integer('id').primaryKey({ autoIncrement: true }),
    /** The PublishEvent's dedup id — joins back to the bus's `build-bus.publish` event. */
    eventId: text('event_id').notNull(),
    packageName: text('package_name').notNull(),
    packageVersion: text('package_version').notNull(),
    tag: text('tag').notNull(),
    moduleId: text('module_id').notNull(),
    hookName: text('hook_name').notNull(),
    scriptPath: text('script_path').notNull(),
    /** Null means the script never reached an exit (spawn failure). */
    exitCode: integer('exit_code'),
    timedOut: integer('timed_out', { mode: 'boolean' }).notNull().default(false),
    durationMs: integer('duration_ms').notNull(),
    stdoutTail: text('stdout_tail'),
    stderrTail: text('stderr_tail'),
    ranAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  },
  (table) => ({
    ranAtLookup: index('build_bus_hook_runs_created_at_idx').on(table.ranAt),
  }),
);

/**
 * Capability secrets table - stores encrypted secrets owned by capabilities
 * Values are encrypted with AES-256-GCM using master key
 * Example: dns-external capability owns TSIG secret
 * @owner celilo — custody of ciphertext keyed by capability id; holds no format knowledge
 */
export const capabilitySecrets = sqliteTable(
  'capability_secrets',
  {
    id: integer('id').primaryKey({ autoIncrement: true }),
    capabilityId: integer('capability_id')
      .notNull()
      .references(() => capabilities.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    description: text('description'), // Added to store secret description
    encryptedValue: text('encrypted_value'), // Nullable - allows metadata-only storage during import
    iv: text('iv'), // Nullable - populated when secret value is set
    authTag: text('auth_tag'), // Nullable - populated when secret value is set
    createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
    updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  },
  (table) => ({
    uniqueCapabilitySecret: unique().on(table.capabilityId, table.name),
  }),
);

/**
 * Secrets table - stores encrypted secrets per module
 * Values are encrypted with AES-256-GCM using master key
 * @owner celilo — custody of ciphertext keyed by module; holds no format knowledge
 */
export const secrets = sqliteTable('secrets', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  moduleId: text('module_id')
    .notNull()
    .references(() => modules.id, { onDelete: 'cascade' }),
  name: text('name').notNull(),
  encryptedValue: text('encrypted_value').notNull(),
  iv: text('iv').notNull(),
  authTag: text('auth_tag').notNull(),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
});

/**
 * System configuration - system-wide settings
 * Used for $system: variables in templates
 * Examples: DNS servers, network settings, domain names
 * @owner celilo — generic operator KV
 */
export const systemConfig = sqliteTable('system_config', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  key: text('key').notNull().unique(),
  value: text('value').notNull(),
  description: text('description'),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
});

/**
 * System secrets table - stores encrypted system-level secrets
 * Values are encrypted with AES-256-GCM using master key
 * Examples: Proxmox root password, API tokens, SSH keys
 * @owner celilo — generic operator KV, encrypted
 */
export const systemSecrets = sqliteTable('system_secrets', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  key: text('key').notNull().unique(),
  encryptedValue: text('encrypted_value').notNull(),
  iv: text('iv').notNull(),
  authTag: text('auth_tag').notNull(),
  description: text('description'),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
});

/**
 * Module integrity table - stores checksums and signature for package verification
 * Used for runtime auditing to detect tampering, missing files, or extra files
 * @owner celilo — package checksums + signature, tamper detection
 */
export const moduleIntegrity = sqliteTable('module_integrity', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  moduleId: text('module_id')
    .notNull()
    .unique()
    .references(() => modules.id, { onDelete: 'cascade' }),
  checksums: text('checksums', { mode: 'json' }).$type<Record<string, string>>().notNull(), // { "path/to/file": "xxhash", ... }
  // Which version these checksums describe. NULL means the row was written
  // before celilo stamped versions — a fact `module verify` reports rather
  // than papers over, because a baseline whose version is unknown cannot be
  // compared to the module's and is not evidence of anything.
  version: text('version'),
  signature: text('signature'), // Nullable - null for directory imports, populated for .netapp packages
  importedAt: integer('imported_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
});

/**
 * Per-module hook jail policy — the operator's row in the four-step
 * precedence (per-module-jail-policy task 1.1), ranked between the
 * `CELILO_HOOK_JAIL` environment variable and the system-wide
 * `hooks.jail_policy` key.
 *
 * One row per module; an absent row means "follow the system", so there is
 * no default column to get wrong. The value is validated at set time by the
 * `module jail` verb and re-validated at read time by `resolveJailPolicy`,
 * which throws rather than coercing — a restored or hand-edited row outside
 * the accepted set must fail loudly, the same rule the system key follows
 * (peba's ruling on ce-8832).
 * @owner celilo — operator-set per-module jail policy
 */
export const moduleJailPolicies = sqliteTable('module_jail_policies', {
  moduleId: text('module_id')
    .primaryKey()
    .references(() => modules.id, { onDelete: 'cascade' }),
  policy: text('policy').$type<'auto' | 'off' | 'required'>().notNull(),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
});

/**
 * Lifecycle states an instance moves through.
 *
 * Re-exported from `@celilo/capabilities` rather than redefined here. `list`
 * PROMISES these to a caller's reconcile loop, so the vocabulary belongs to the
 * contract; a second copy in the schema is how a column and an interface come
 * to disagree about what `failed` means.
 */
export { INSTANCE_STATES, type InstanceState };

/**
 * Instances of a submodule (openspec/changes/submodules, D2 and D4).
 *
 * An instance IS a row in `modules`, under a celilo-derived id, which is what
 * lets every table and reader keyed on `moduleId` keep working unchanged:
 * `module_systems`, `module_configs`, `module_infrastructure`, health, backup,
 * fleet status and the removal path all needed no change. `moduleId` here is
 * both this table's key and the FK to that row, so an instance cannot exist
 * without the module row it names.
 *
 * This table carries only what a module row cannot express: who owns it, which
 * submodule it came from, and the opaque key its parent knows it by.
 *
 * IDENTITY IS THE TRIPLE (parent, submodule, instanceKey), not `instanceKey`
 * alone (D2). Two parents may use the same key string, and one parent may use
 * the same key across two of its submodules; both are legal and neither
 * collides. `instanceKey` is opaque — celilo stores it and never interprets
 * it, because a key derived from a display name orphans a running system the
 * first time somebody is renamed at the identity provider.
 * @owner celilo — instance ownership and lifecycle state
 */
export const moduleInstances = sqliteTable(
  'module_instances',
  {
    /**
     * The derived `modules.id` this instance runs as, e.g. `byoi-lab-sdf82c1e`.
     * Both this table's key and the FK, so the two cannot drift apart.
     */
    moduleId: text('module_id')
      .primaryKey()
      .references(() => modules.id, { onDelete: 'cascade' }),
    /** The module that declared the submodule and created this instance. */
    parentId: text('parent_id')
      .notNull()
      .references(() => modules.id, { onDelete: 'cascade' }),
    /** The submodule name the parent declared — its directory under `submodules/`. */
    submodule: text('submodule').notNull(),
    /** Opaque, stable id supplied by the parent. Never interpreted. */
    instanceKey: text('instance_key').notNull(),
    /**
     * Non-authoritative label for operator display. NOTHING may key on it, and
     * changing it does not change identity — that is the whole point of D2.
     */
    label: text('label'),
    state: text('state').$type<InstanceState>().notNull().default('pending'),
    /**
     * Why a `failed` instance failed. Null unless `state = 'failed'` — the
     * legitimate "this section is absent" case rather than a missing default.
     */
    failureReason: text('failure_reason'),
    /**
     * Whether retrying could succeed. Set with `failureReason`.
     *
     * A parent's reconcile loop reads this to decide whether to rebuild. A
     * missing interview answer (D7) is deterministic and will fail identically
     * forever, while a timed-out provision is worth another go. Without the
     * flag a caller has to string-match `failureReason`, which is the class of
     * bug where a reporter is trusted by its shape and quietly answers wrong.
     */
    retryable: integer('retryable', { mode: 'boolean' }),
    createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
    updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  },
  (table) => ({
    // The real identity (D2). Makes "this parent already has an instance under
    // this key" a database fact rather than a check somebody has to remember.
    identity: unique().on(table.parentId, table.submodule, table.instanceKey),
    // Every reconcile call lists one parent's instances, so that must not be a
    // table scan once a fleet carries tens of them.
    parentIdx: index('module_instances_parent_idx').on(table.parentId),
    // `list` filters by state, and the collapsed operator view rolls up health
    // per parent, which reads the same way.
    stateIdx: index('module_instances_state_idx').on(table.state),
  }),
);

/**
 * IPAM (IP Address Management) allocations table
 * Tracks VMID and IP address assignments per module
 * Prevents conflicts and enables automatic allocation from zone subnets
 * @owner celilo — IPAM is a core primitive
 */
export const ipAllocations = sqliteTable('ip_allocations', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  moduleId: text('module_id')
    .notNull()
    .references(() => modules.id, { onDelete: 'cascade' }),
  vmid: integer('vmid').notNull().unique(),
  containerIp: text('container_ip').notNull().unique(), // CIDR format (e.g., "10.0.10.10/24")
  zone: text('zone').$type<AllocatableZone>().notNull(),
  allocatedAt: integer('allocated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
});

/**
 * IP reservations table
 * Allows users to reserve IPs for infrastructure or external services
 * IPAM allocator skips reserved IPs
 * @owner celilo — IPAM is a core primitive
 */
export const ipReservations = sqliteTable('ip_reservations', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  ipStart: text('ip_start').notNull(), // Single IP or range start
  ipEnd: text('ip_end'), // NULL for single IP, end IP for range
  zone: text('zone').$type<AllocatableZone>().notNull(),
  reason: text('reason').notNull(),
  reservedAt: integer('reserved_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
});

/**
 * VMID reservations table
 * Allows users to reserve VMIDs for existing VMs or external systems
 * IPAM allocator skips reserved VMIDs
 * @owner celilo — IPAM is a core primitive; infra-provider columns; out of scope per S17, not blessed
 */
export const vmidReservations = sqliteTable('vmid_reservations', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  vmid: integer('vmid').notNull().unique(),
  reason: text('reason').notNull(),
  reservedAt: integer('reserved_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
});

/**
 * Module builds table
 * Tracks build metadata for modules with custom compilation requirements
 * Example: Caddy with RFC2136 DNS provider, custom Go binaries
 * @owner celilo — build metadata per module version
 */
export type BuildStatus = 'success' | 'failed' | 'in_progress';

export const moduleBuilds = sqliteTable('module_builds', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  moduleId: text('module_id')
    .notNull()
    .references(() => modules.id, { onDelete: 'cascade' }),
  version: text('version').notNull(),
  builtAt: integer('built_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  artifacts: text('artifacts', { mode: 'json' }).$type<string[]>().notNull(), // Array of artifact paths
  status: text('status').$type<BuildStatus>().notNull(),
  buildLog: text('build_log'), // Build output for debugging
});

/**
 * Network zones — the DEFINED vocabulary, not the active set.
 *
 * A zone being listed here means celilo knows the name and will accept
 * `network.<zone>.*` config for it. It does NOT mean the zone exists on a given
 * fleet: per openspec/specs/progressive-zone-disclosure/spec.md, "deployable
 * zones are those with a configured subnet", so a zone becomes REAL only when
 * something declares `network.<zone>.subnet` — normally the module that supplies
 * the network. Defining a zone here costs nothing and activates nothing.
 *
 * - isp-transit: the private segment between a celilo firewall and the router
 *   upstream of it. Non-placement and non-allocatable, because the upstream
 *   router is the address authority. It exists so a deployment CAN separate the
 *   wire to that router from the workload network, not so every deployment
 *   must: a firewall whose egress leg IS the workload network declares
 *   `default_route_zone: internal` and behaves exactly as it does today.
 * - internal: the semi-trusted LAN the management server sits on
 * - dmz: public-facing services
 * - app: internal application services
 * - secure: authentication and database services
 * - secure-mgmt: celilo's own control plane (management server + management-plane
 *   modules). Outside the data-plane tier chain; reaches every tier by trust.
 * - external: internet-hosted services — a cloud/VPS provider's network, NOT a
 *   VPN. Addressed by the provider, which is why it is excluded from IPAM below.
 * - control-plane-vpn: the ADMINISTRATIVE remote-access client subnet, activated
 *   by the module that terminates the tunnel (`wireguard` writes
 *   `network.control-plane-vpn.subnet`). Named for its purpose rather than its
 *   technology because a fleet may run more than one VPN — a site-to-site link
 *   or a user VPN is a different network with different trust, and `vpn` would
 *   have been the wrong name to have to share. Distinct from `external` too:
 *   that is someone else's cloud, this is a network the fleet's own firewall
 *   holds a leg on and must translate for.
 */
/**
 * ⚠️ ADDING A ZONE ALSO MEANS TOUCHING THE WEB CONSOLE.
 *
 * A zone comes into being here and nowhere else. How it is DRAWN — its accent
 * colour and the one-line description a person reads under its name — lives in
 * `apps/console/src/zones.ts`, because that is presentation and this is not.
 *
 * A zone added here and missed there draws grey with "no description yet",
 * which is legible and wrong. `apps/console/tests/zones.test.ts` fails until
 * the entry exists, so the pull request that adds the zone is the one that
 * finds out, rather than the release that ships it.
 */
export const NETWORK_ZONES = [
  'isp-transit',
  'internal',
  'dmz',
  'app',
  'secure',
  'secure-mgmt',
  'external',
  'control-plane-vpn',
] as const;

/**
 * Derived from NETWORK_ZONES on purpose: a hand-maintained runtime copy of this
 * list silently dropped `secure-mgmt`, so zone validation returned null for it
 * and callers fell back to a wrong-but-valid zone. Deriving the type from the
 * single array means a new zone cannot be added to one and missed by the other.
 */
export type NetworkZone = (typeof NETWORK_ZONES)[number];

/**
 * Zones an IP allocation or reservation can name: every NetworkZone whose
 * addresses celilo hands out.
 *
 * Three are excluded, for the same underlying reason — somebody else is the
 * address authority:
 *   - `isp-transit`, whose addresses the upstream router assigns;
 *   - `external`, whose systems are addressed by the cloud/VPS provider;
 *   - `vpn`, whose client addresses are assigned by the module terminating the
 *     tunnel. celilo allocating into that subnet would collide with the VPN
 *     server's own assignments.
 *
 * Derived rather than hand-written for the same reason as NetworkZone above —
 * the previous hand-written union was copied into two column definitions and a
 * cast in machine-pool.ts, and the cast had already drifted (it was missing
 * `secure-mgmt`, and its comment claimed the only difference was `external`).
 */
export type AllocatableZone = Exclude<
  NetworkZone,
  'isp-transit' | 'external' | 'control-plane-vpn'
>;

/** The zones whose addresses celilo hands out, as a runtime list. */
export const ALLOCATABLE_ZONES: AllocatableZone[] = NETWORK_ZONES.filter(
  (zone): zone is AllocatableZone =>
    zone !== 'isp-transit' && zone !== 'external' && zone !== 'control-plane-vpn',
);

/**
 * Is this a zone celilo allocates addresses in? Use this rather than testing
 * `zone !== 'external'` by hand — that check predates the other externally
 * addressed roles and silently makes them allocatable.
 */
export function isAllocatableZone(zone: NetworkZone): zone is AllocatableZone {
  return (ALLOCATABLE_ZONES as string[]).includes(zone);
}

/**
 * Container services table
 * Stores container service providers (Proxmox, Digital Ocean, etc.)
 * that can provision new containers/VMs on demand
 * @owner celilo — infra-provider registry; infra-provider columns; out of scope per S17, not blessed
 */
export const containerServices = sqliteTable('container_services', {
  id: text('id').primaryKey(), // UUID
  serviceId: text('service_id').notNull().unique(), // User-facing kebab-case ID
  name: text('name').notNull(),
  providerName: text('provider_name')
    .$type<'proxmox' | 'digitalocean' | 'aws' | 'gcp' | 'azure'>()
    .notNull(),
  zones: text('zones', { mode: 'json' }).$type<NetworkZone[]>().notNull(),
  apiCredentialsEncrypted: text('api_credentials_encrypted').notNull(),
  providerConfig: text('provider_config', { mode: 'json' })
    .$type<Record<string, unknown>>()
    .notNull(),
  verified: integer('verified', { mode: 'boolean' }).notNull().default(false),
  verifiedAt: integer('verified_at', { mode: 'timestamp' }),
  verificationError: text('verification_error'),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
});

/**
 * Machines table
 * Stores pre-existing machines (Raspberry Pi, VPS, bare metal)
 * that users have added to the pool for hosting modules
 * @owner celilo — the machine pool
 */
export const machines = sqliteTable('machines', {
  id: text('id').primaryKey(), // UUID
  hostname: text('hostname').notNull(),
  zone: text('zone').$type<NetworkZone>().notNull(),
  ipAddress: text('ip_address').notNull(),
  sshUser: text('ssh_user').notNull(),
  sshKeyEncrypted: text('ssh_key_encrypted').notNull(),
  hardware: text('hardware', { mode: 'json' })
    .$type<{
      cpu_cores: number;
      memory_mb: number;
      disk_gb: number;
      arch?: string;
    }>()
    .notNull(),
  /** Machine classification: 'host' (single interface) or 'router' (multi-interface) */
  role: text('role').$type<'host' | 'router'>().notNull().default('host'),
  /** Detected network interfaces with IPs and zones */
  interfaces: text('interfaces', { mode: 'json' })
    .$type<Array<{ name: string; ipAddress: string; zone: string }>>()
    .notNull()
    .default(sql`'[]'`),
  // No `assigned_module_ids` (celilo#773). Occupancy is derived from
  // `module_infrastructure` / `module_systems` at the point of use — both are
  // written by the deploy path and both cascade on module removal, so a machine
  // frees itself. The dropped column had one append-only writer, no removal
  // path, and no reader that reconciled it, and it had already diverged in both
  // directions on the live fleet.
  /** Module ID this machine is earmarked for. If set, only this module can use this machine. */
  earmarkedModule: text('earmarked_module'),
  /**
   * Appliance machines (e.g., greenwave = ISP modem) where celilo
   * has no shell-level access — only API calls. Base-module aspects
   * cannot Ansible to these systems, so the aspect runner skips
   * them. See openspec/specs/base-module-aspects/spec.md D8.
   */
  apiOnly: integer('api_only', { mode: 'boolean' }).notNull().default(false),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
});

/**
 * Module infrastructure table
 * Tracks which infrastructure (machine or container service) is used for each module
 * @owner celilo — which infra hosts which module; infra-provider columns; out of scope per S17, not blessed
 */
export const moduleInfrastructure = sqliteTable('module_infrastructure', {
  id: text('id').primaryKey(), // UUID
  moduleId: text('module_id')
    .notNull()
    .references(() => modules.id, { onDelete: 'cascade' }),
  infrastructureType: text('infrastructure_type')
    .$type<'machine' | 'container_service'>()
    .notNull(),
  machineId: text('machine_id').references(() => machines.id),
  serviceId: text('service_id').references(() => containerServices.id),
  containerMetadata: text('container_metadata', { mode: 'json' }).$type<Record<string, unknown>>(), // VMID, droplet ID, etc.
  /**
   * Mirrors machines.api_only — set when the container_service
   * driver registers an entry that has no SSH surface (rare today,
   * but exists for future API-only providers). Aspect runner reads
   * this to decide whether the system is reachable via Ansible.
   * See openspec/specs/base-module-aspects/spec.md D8.
   */
  apiOnly: integer('api_only', { mode: 'boolean' }).notNull().default(false),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
});

/**
 * Module systems table
 *
 * A module deploys onto 0..N *systems*, each an addressed host. This is the
 * deployment-STATE counterpart to module_configs (declared inputs): it records,
 * per deployed system, the hostname + IPv4 + zone + where it lives. Replaces the
 * old scalar `target_ip`/`vmid` rows in module_configs, which baked in the
 * one-module-one-host assumption. See openspec/specs/module-systems-addressing/spec.md.
 *
 *   - 0 rows: API-only modules (e.g. namecheap — no host).
 *   - 1 row:  the common case (technitium, homebridge, …).
 *   - N rows: multi-system modules (e.g. app + db).
 *
 * Keyed by (module_id, name): `name` is the stable, authoring-time handle from
 * the manifest's `requires.systems[].name` — what templates reference via
 * `$infra:<name>.…`. `hostname` is the runtime DNS hostname (often == name, but
 * user/well-known-assignable), used by DNS and events. See
 * openspec/specs/module-systems-addressing/spec.md.
 * @owner celilo — deployment state per addressed host; infra-provider columns; out of scope per S17, not blessed
 */
export const moduleSystems = sqliteTable(
  'module_systems',
  {
    moduleId: text('module_id')
      .notNull()
      .references(() => modules.id, { onDelete: 'cascade' }),
    /** Stable authoring-time handle (requires.systems[].name) — the per-system key. */
    name: text('name').notNull(),
    /** Runtime DNS hostname (often == name). */
    hostname: text('hostname').notNull(),
    /** CIDR-stripped IPv4 — the canonical address. */
    ipv4Address: text('ipv4_address').notNull(),
    /** Network zone the host sits in; CIDR is derived from this + ipv4Address. */
    zone: text('zone').$type<NetworkZone>().notNull(),
    /** Where the system lives: a pool machine or a celilo-provisioned container. */
    infraType: text('infra_type').$type<'machine' | 'container_service'>().notNull(),
    /** FK machines.id — set for machine-pool deploys (null otherwise). */
    machineId: text('machine_id').references(() => machines.id),
    /** FK container_services.id — set for container_service deploys (null otherwise). */
    serviceId: text('service_id').references(() => containerServices.id),
    /** Proxmox VMID — set only for proxmox containers. */
    vmid: integer('vmid'),
    // Canonical deployed SIZE of this system (ISS-0150). For celilo-provisioned
    // VM/LXC instances only (null for machine-pool systems celilo doesn't size).
    // Seeded from the module's `requires.system` at first provision, then owned
    // by `celilo proxmox … resize` — `requires.system` is only the minimum floor,
    // never the live size. See CLAUDE.md "requires.system is the MINIMUM".
    /** vCPU cores. */
    cpu: integer('cpu'),
    /** RAM in MB. */
    memory: integer('memory'),
    /** Root disk in GB. */
    disk: integer('disk'),
    createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
    updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  },
  (table) => ({
    pk: primaryKey({ columns: [table.moduleId, table.name] }),
  }),
);

/**
 * Web routes table
 * Tracks routes registered by modules via public_web capability functions.
 * Routes are registered during on_install hooks and removed during on_uninstall.
 * The public_web provider (Caddy) uses these to generate its configuration.
 * @owner capability:public_web — reverse-proxy configuration; migrates to the provider (T1)
 */
export const webRoutes = sqliteTable(
  'web_routes',
  {
    id: integer('id').primaryKey({ autoIncrement: true }),
    slug: text('slug').notNull(),
    moduleId: text('module_id')
      .notNull()
      .references(() => modules.id, { onDelete: 'cascade' }),
    type: text('type').$type<'static' | 'reverse_proxy'>().notNull(),
    path: text('path').notNull(),
    /** FQDN this route serves under — must be in caddy's hostnames list. */
    hostname: text('hostname').notNull(),
    targetHost: text('target_host'),
    targetPort: integer('target_port'),
    websocket: integer('websocket', { mode: 'boolean' }).notNull().default(false),
    contentHash: text('content_hash'),
    createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
    updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  },
  (table) => ({
    // Path uniqueness is scoped to hostname — two modules can both own
    // "/" as long as they're on different hostnames.
    hostnamePathUnique: uniqueIndex('web_routes_hostname_path_idx').on(table.hostname, table.path),
  }),
);

/**
 * Port-forward registry — the desired-state store for the `firewall` capability
 * (openspec/changes/unified-management-no-ssh/proposal.md). One row per exposed forward. `exposeService`
 * upserts, `unexposeService` deletes, and the firewall provider's converge
 * renders the COMPLETE ruleset for a firewall from these rows and applies it
 * atomically via `iptables-restore` — replacing the old per-rule `iptables -A`
 * (non-idempotent) + the "read the box back with iptables -L" source of truth.
 *
 * Keyed by `firewall_ip` (the converge target) so multiple firewalls each render
 * their own set. Shared-core (not a per-module JSON file) so any firewall
 * provider reconciles against the one canonical store.
 * @owner capability:firewall — one row is one DNAT rule; migrates to the provider (T2)
 */
export const portForwards = sqliteTable(
  'port_forwards',
  {
    id: integer('id').primaryKey({ autoIncrement: true }),
    /** The firewall host this forward is installed on (config.firewallIp). */
    firewallIp: text('firewall_ip').notNull(),
    /** Backend IP the forward targets. */
    internalIp: text('internal_ip').notNull(),
    /** Port — external == internal (no translation today). */
    port: integer('port').notNull(),
    protocol: text('protocol').$type<'TCP' | 'UDP'>().notNull(),
    /** Dedicated INTERNAL ingress IP (ISS-0156); NULL for the normal public path. */
    ingressIp: text('ingress_ip'),
    description: text('description').notNull().default(''),
    /**
     * The CONSUMER that registered it, stamped by the store from the calling
     * module (openspec/changes/consumer-removal-cleanup, D5a). `''` for rows
     * written before this column existed — unattributed, so no consumer removal
     * withdraws them.
     */
    registeredBy: text('registered_by').notNull().default(''),
    createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  },
  (table) => ({
    // One row per (OWNER, firewall, backend, port, protocol, ingress). The
    // owner is IN the index, not merely alongside it, and that is the whole
    // point: without it, consumer B exposing a forward A already has silently
    // REPLACES A's row, and B leaving then deletes a rule A still needs. Two
    // owners of one forward are two rows — the set, denormalised into the
    // table that already exists rather than a second table. `renderRuleset`
    // dedupes on the rule tuple so the duplicate renders once.
    //
    // This is the `dns_registration_consumers` lesson (see below): a single
    // overwritten owner column is not a label, because what cascades on it
    // decides when a LIVE record is forgotten.
    forwardUnique: uniqueIndex('port_forwards_unique_idx').on(
      table.firewallIp,
      table.internalIp,
      table.port,
      table.protocol,
      table.ingressIp,
      table.registeredBy,
    ),
  }),
);

/**
 * Trusted-source registry — the desired-state store for "this subnet may reach
 * every managed zone", the sibling of `port_forwards` and for the same reason: a
 * rule that lives only in the applied ruleset belongs to no module, so the next
 * converge correctly removes it. The admin VPN sat in exactly that position and
 * needed a shell script re-adding its rules every second.
 *
 * Distinct from a port forward: that publishes one backend on specific ports;
 * this is a whole origin subnet permitted to initiate into the segmented tiers.
 * @owner capability:firewall — the firewall ruleset; migrates to the provider (T3)
 */
export const trustedSources = sqliteTable(
  'trusted_sources',
  {
    id: integer('id').primaryKey({ autoIncrement: true }),
    /** The firewall host that renders this trust (config.firewallIp). */
    firewallIp: text('firewall_ip').notNull(),
    /** Subnet CIDR permitted to reach every managed zone. */
    subnet: text('subnet').notNull(),
    description: text('description').notNull().default(''),
    /** Module that registered it — reach into every tier must never be anonymous. */
    registeredBy: text('registered_by').notNull(),
    createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  },
  (table) => ({
    // One row per (OWNER, firewall, subnet) — same reason the owner is in
    // `port_forwards_unique_idx`. Two modules trusting the same subnet are two
    // rows, so one leaving does not revoke the other's reach. The renderer
    // already dedupes trusted subnets (`new Set`), so the pair renders once.
    trustedSourceUnique: uniqueIndex('trusted_sources_unique_idx').on(
      table.firewallIp,
      table.subnet,
      table.registeredBy,
    ),
  }),
);

/**
 * DNS registration ledger — one row per (provider, fqdn) the framework
 * has successfully registered via dns_registrar.registerHost. Written
 * framework-side by the capability loader (the only layer that knows
 * both consumer and provider), read back to drive the provider's
 * periodic refresh_registrations hook, the `public_dns` reachability
 * check, and the `celilo dns registrations` view.
 *
 * It records WHICH MODULE ASKED FOR WHICH NAME and nothing else. In
 * particular it does not store the published address: that is observable
 * on demand from public DNS, and a stored copy is one careless read away
 * from becoming an instruction again — which is exactly what took five
 * public names dark for nine days (celilo#626).
 *
 * Rows die with their provider by FK cascade. The consumer side is a
 * SET (`dns_registration_consumers`), not a column: the row must survive
 * until its LAST consumer is removed. The remote DNS record itself stays
 * (Namecheap DDNS has no delete API).
 * See designs/DISPATCHER_DAEMON_AND_TIMER_EVENTS.md (B2).
 * @owner celilo — claim ledger, deliberately stores no address (T4)
 */
export const dnsRegistrations = sqliteTable(
  'dns_registrations',
  {
    id: integer('id').primaryKey({ autoIncrement: true }),
    providerModuleId: text('provider_module_id')
      .notNull()
      .references(() => modules.id, { onDelete: 'cascade' }),
    fqdn: text('fqdn').notNull(),
    /**
     * True when celilo claimed this name on a module's behalf as the
     * companion of a declared name (`www.<domain>` ↔ `<domain>`) rather
     * than because a module asked for it. Companions are best effort at
     * claim time, so the `public_dns` check carries the manual-registrar
     * remediation for them.
     */
    companion: integer('companion', { mode: 'boolean' }).notNull().default(false),
    registeredAt: integer('registered_at', { mode: 'timestamp' })
      .notNull()
      .default(sql`(unixepoch())`),
    refreshedAt: integer('refreshed_at', { mode: 'timestamp' }),
  },
  (table) => ({
    providerFqdnUnique: uniqueIndex('dns_registrations_provider_fqdn_idx').on(
      table.providerModuleId,
      table.fqdn,
    ),
  }),
);

/**
 * Which modules currently depend on a DNS registration.
 *
 * Was a single `dns_registrations.consumer_module_id`, overwritten by
 * every re-assert. That is not a label — the FK cascade on it decided
 * when a LIVE record was forgotten, so recovering the fleet with
 * `run-hook caddy on_install` re-attributed most names to caddy and
 * removing caddy would then have cascade-deleted registrations that
 * authentik and the site modules still served. Keep-first has the mirror
 * failure. A set is the only model where the row dies exactly when the
 * last module that wants the name goes away (design.md D5).
 *
 * The introducing module is the earliest row by `id`.
 * @owner celilo — the consumer SET, dies with the last consumer (T5)
 */
export const dnsRegistrationConsumers = sqliteTable(
  'dns_registration_consumers',
  {
    id: integer('id').primaryKey({ autoIncrement: true }),
    registrationId: integer('registration_id')
      .notNull()
      .references(() => dnsRegistrations.id, { onDelete: 'cascade' }),
    moduleId: text('module_id')
      .notNull()
      .references(() => modules.id, { onDelete: 'cascade' }),
    firstSeenAt: integer('first_seen_at', { mode: 'timestamp' })
      .notNull()
      .default(sql`(unixepoch())`),
  },
  (table) => ({
    registrationModuleUnique: uniqueIndex('dns_registration_consumers_unique_idx').on(
      table.registrationId,
      table.moduleId,
    ),
  }),
);

/**
 * Consecutive runs the `public_dns` check could obtain NO evidence about a
 * subject (a served FQDN, or `system` for the echo service itself).
 *
 * A probe that could not run is not a pass. The one genuine external probe the
 * fleet had was itself unreachable during celilo#626 and recorded that as
 * *undetermined* with no check item at all, so a real outage produced complete
 * silence. One blip must still stay quiet — a prober outage once paged three
 * modules at once — so absences are counted rather than reported, and become
 * their own finding only once they persist. A subject that answers has its row
 * dropped, which is what makes the count consecutive.
 * @owner celilo — the audit subsystem's state about its own ability to observe (T7)
 */
export const publicDnsEvidence = sqliteTable('public_dns_evidence', {
  subject: text('subject').primaryKey(),
  undeterminedRuns: integer('undetermined_runs').notNull().default(0),
  lastCheckedAt: integer('last_checked_at', { mode: 'timestamp' })
    .notNull()
    .default(sql`(unixepoch())`),
});

/**
 * Internal split-horizon DNS A-record ledger. Mirrors `dns_registrations`
 * but for the dns_internal capability (technitium/knot): the capability
 * loader records every `dns_internal.registerRecord({type:'A'})` here so
 * celilo has an offline, queryable record of what hostname → IP it asked
 * the internal resolver to serve. Without this, the only source of truth
 * is the resolver's own DB, requiring a live probe (ISS-0094 / ISS-0111).
 *
 * `celilo system doctor` reads this to assert service hostnames resolve to
 * the firewall natIp (LAN-reachable) and not a zone-side container IP that
 * a LAN device can't route to. Rows die with their CONSUMER via FK cascade, and
 * NOT with their provider (celilo#1010 — see `providerModuleId`).
 * @owner capability:dns_internal — resolver configuration; migrates to the provider (T6)
 */
export const dnsInternalRecords = sqliteTable(
  'dns_internal_records',
  {
    id: integer('id').primaryKey({ autoIncrement: true }),
    /**
     * The resolver serving this record. A PLAIN column with no foreign key, and
     * that is the celilo#1010 correction rather than an oversight.
     *
     * It used to cascade, so swapping `technitium` for `knot-unbound-internal`
     * deleted the fleet's entire internal DNS ledger, `zone_routable_ip` view
     * overrides included. `web_routes` cascades on its consumer only and the two
     * docblocks claimed to be siblings, so the divergence read as intent and was
     * not. The claim on a capability-owned table is the CONSUMER
     * (openspec/changes/capability-owned-tables D3/D8), and `dns_internal`'s
     * declaration cannot express anything else.
     *
     * Attribution is still real and still enforced — it is half
     * `dns_internal_records_provider_host_idx` — it just no longer decides when a
     * LIVE record is forgotten. A provider leaving now leaves the ledger for the
     * next one to reconcile from, which is what stage 1's provider-arrival
     * backfill assumes. Migration `0027`.
     */
    providerModuleId: text('provider_module_id').notNull(),
    consumerModuleId: text('consumer_module_id')
      .notNull()
      .references(() => modules.id, { onDelete: 'cascade' }),
    /** The registered hostname (e.g. "git-ssh.git.celilo.computer"). */
    host: text('host').notNull(),
    /** The A-record value celilo asked the resolver to serve — the LAN/default
     *  answer (firewall natIp for a caddy-fronted host). */
    ip: text('ip').notNull(),
    /**
     * In-zone split-horizon answer (caddy's zone-routable IP), when this is a
     * caddy-fronted hostname that needs source-based views (ISS-0156,
     * openspec/specs/internal-dns-zone-views/spec.md). NULL for records with no zone override
     * (per-system identity, plain A records). This column is the durable
     * desired-state the resolver's view config is reconciled from.
     */
    zoneRoutableIp: text('zone_routable_ip'),
    registeredAt: integer('registered_at', { mode: 'timestamp' })
      .notNull()
      .default(sql`(unixepoch())`),
  },
  (table) => ({
    providerHostUnique: uniqueIndex('dns_internal_records_provider_host_idx').on(
      table.providerModuleId,
      table.host,
    ),
  }),
);

/**
 * Backup storage providers - destinations for backup archives
 * Supports local filesystem and S3-compatible storage (AWS S3, MinIO, Backblaze B2, Wasabi)
 * @owner celilo — backup destinations; infra-provider columns; out of scope per S17, not blessed
 */
export type BackupStorageProvider = 'local' | 's3';

export const backupStorages = sqliteTable('backup_storages', {
  id: text('id').primaryKey(), // UUID
  storageId: text('storage_id').notNull().unique(), // User-facing kebab-case ID
  name: text('name').notNull(),
  providerName: text('provider_name').$type<BackupStorageProvider>().notNull(),
  credentialsEncrypted: text('credentials_encrypted').notNull(), // Encrypted JSON blob
  providerConfig: text('provider_config', { mode: 'json' })
    .$type<Record<string, unknown>>()
    .notNull(),
  verified: integer('verified', { mode: 'boolean' }).notNull().default(false),
  verifiedAt: integer('verified_at', { mode: 'timestamp' }),
  verificationError: text('verification_error'),
  isDefault: integer('is_default', { mode: 'boolean' }).notNull().default(false),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
});

/**
 * Backup records - metadata for each backup taken
 * Tracks both system state backups and module data backups
 * @owner celilo — backup metadata
 */
export type BackupType = 'module_data' | 'system_state';
export type BackupStatus = 'in_progress' | 'completed' | 'failed';

export const backups = sqliteTable('backups', {
  id: text('id').primaryKey(), // UUID
  moduleId: text('module_id').references(() => modules.id, { onDelete: 'set null' }), // null = system state backup
  storageId: text('storage_id')
    .notNull()
    .references(() => backupStorages.id),
  storagePath: text('storage_path').notNull(), // path/key within storage
  backupType: text('backup_type').$type<BackupType>().notNull(),
  moduleVersion: text('module_version'), // module version at backup time
  schemaVersion: text('schema_version'), // optional data schema version from on_backup hook
  sizeBytes: integer('size_bytes'),
  metadata: text('metadata', { mode: 'json' })
    .$type<Record<string, unknown>>()
    .notNull()
    .default(sql`'{}'`),
  status: text('status').$type<BackupStatus>().notNull().default('in_progress'),
  errorMessage: text('error_message'),
  name: text('name'), // optional human-readable name/annotation
  // The process assembling this backup's staging directory. Lets the staging
  // reaper (services/backup-staging.ts) tell a live backup from one whose
  // process was killed before its `finally` could clean up. Nullable: rows
  // written before this column existed have no pid and age out via the TTL.
  pid: integer('pid'),
  startedAt: integer('started_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  completedAt: integer('completed_at', { mode: 'timestamp' }),
});

/**
 * Module operations - in-flight tracking for deploy/uninstall/backup/restore.
 *
 * Used by `checkInFlight()` to refuse a backup or restore while another
 * operation is active on any module. A row is created with status='in_progress'
 * when an operation starts and updated to 'completed' or 'failed' when it ends.
 *
 * The `pid` column carries the process that started the operation; a row whose
 * pid is no longer alive is treated as abandoned (the process crashed before
 * the completion update landed) and ignored by in-flight checks.
 * @owner celilo — in-flight operation tracking
 */
export type ModuleOperationKind =
  | 'deploy'
  | 'uninstall'
  | 'backup'
  | 'restore'
  | 'pause'
  | 'unpause';
export type ModuleOperationStatus = 'in_progress' | 'completed' | 'failed';

export const moduleOperations = sqliteTable('module_operations', {
  id: text('id').primaryKey(), // UUID
  moduleId: text('module_id').notNull(), // not a FK — survives module deletion (uninstall flow)
  operation: text('operation').$type<ModuleOperationKind>().notNull(),
  status: text('status').$type<ModuleOperationStatus>().notNull().default('in_progress'),
  pid: integer('pid').notNull(),
  startedAt: integer('started_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  completedAt: integer('completed_at', { mode: 'timestamp' }),
  errorMessage: text('error_message'),
});

/**
 * Aspect approvals table
 *
 * Records operator consent for a module's base-module aspect. The
 * operator approves the aspect's scope (applicable_zones +
 * triggers) at `celilo module import` time; that consent is
 * captured here and consulted before any aspect fan-out.
 *
 * `scopeHash` is a stable digest of `applicable_zones` + `triggers`
 * — it lets the framework detect scope-changing upgrades (D7).
 * If a new module version has the same hash, the prior approval
 * still covers it; if the hash differs, the upgrade is blocked
 * until the operator re-approves.
 *
 * Unique on (moduleId, version) — there's at most one approval
 * per module version. Cascade delete on module removal so stale
 * approvals don't linger.
 *
 * See openspec/specs/base-module-aspects/spec.md D2 + D7.
 * @owner celilo — operator consent, per module version + scope hash
 */
export const aspectApprovals = sqliteTable(
  'aspect_approvals',
  {
    id: text('id').primaryKey(), // UUID
    moduleId: text('module_id')
      .notNull()
      .references(() => modules.id, { onDelete: 'cascade' }),
    /** Module version this approval covers (matches modules.version). */
    version: text('version').notNull(),
    /** Hash of `applicable_zones` + `triggers` — see table comment. */
    scopeHash: text('scope_hash').notNull(),
    /**
     * The operator's decision for this (module, version, scope): `true` =
     * approved (run the aspect), `false` = explicitly refused (skip and do NOT
     * re-prompt — ISS-0027). The ABSENCE of a row is the third state, "not yet
     * decided" → interview. Defaults to true so pre-existing rows (all of which
     * were approvals) keep running.
     */
    consented: integer('consented', { mode: 'boolean' }).notNull().default(true),
    approvedAt: integer('approved_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
    /** Operator identifier (e.g., $USER at approval/denial time). Null when consent was granted in a context with no USER. */
    approver: text('approver'),
  },
  (table) => ({
    uniqueModuleVersion: unique('aspect_approvals_module_version').on(
      table.moduleId,
      table.version,
    ),
  }),
);

/**
 * Remote API principals (see openspec/changes/replace-ssh-cli-api/proposal.md).
 *
 * Each row is one API identity: a name, one SSH public key, and the set of
 * operations it may run. celilo renders these into the API account's
 * `authorized_keys` (one forced-command line per row) and authz keys off the
 * principal → `grants`.
 *
 * ponytail: one key per principal (a person wanting a second device makes a
 * second principal, e.g. `alice-laptop`). If multiple keys per identity is ever
 * needed, split into an `api_keys` child table — not worth it yet.
 * @owner celilo — remote-API identity + grants
 */
export const apiPrincipals = sqliteTable('api_principals', {
  id: text('id').primaryKey(), // UUID
  /** Human-readable principal name, kebab-case (e.g. "alice", "ci-deployer"). */
  name: text('name').notNull().unique(),
  /** SSH public key line: `<type> <base64> [comment]`. */
  publicKey: text('public_key').notNull(),
  /**
   * Operations this principal may run, as `command:subcommand` grants
   * (`module:deploy`), `command:*` wildcards (`service:*`), or `*` (all).
   * Deny-by-default: an operation not matched by any grant is refused.
   */
  grants: text('grants', { mode: 'json' }).$type<string[]>().notNull().default(sql`'[]'`),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
});

// ---------------------------------------------------------------------------
// Alerting (openspec/changes/add-alerting)
// ---------------------------------------------------------------------------

/**
 * Alert severity. Only `critical` pages.
 *
 * A check item's `warn` status always yields `warning`; a `fail` yields the
 * monitor's configured severity. A monitor deliberately configured as
 * `warning` therefore never pages even when its checks fail — the operator's
 * "record this but don't wake me" knob. See design D6.
 */
export type AlertSeverity = 'warning' | 'critical';

/** What a monitor runs. `module_hook` = a module's health_check hook. */
export type MonitorKind = 'module_hook' | 'builtin_check';

/**
 * Whether a monitor run executed at all. The load-bearing distinction: a run
 * that could not execute produces an empty failing set for reasons that say
 * nothing about the underlying checks, so it must never resolve anything.
 * See design D5.
 */
export type MonitorRunOutcome = 'success' | 'error';

export type AlertState = 'pending' | 'firing' | 'acked' | 'suppressed' | 'resolved';

/**
 * People celilo can reach. Deliberately independent of any transport or
 * module — a person exists before any notification module is deployed.
 * @owner celilo — deliberately independent of any transport or module
 */
export const people = sqliteTable('people', {
  id: text('id').primaryKey(), // UUID
  /** User-facing kebab-case name (e.g. "peter"). Never a UUID in CLI output. */
  name: text('name').notNull().unique(),
  /** IANA timezone (e.g. "America/Los_Angeles") — quiet hours are local to it. */
  timezone: text('timezone').notNull(),
  /** Quiet-hours window as local "HH:MM"; both null = always reachable. */
  quietHoursStart: text('quiet_hours_start'),
  quietHoursEnd: text('quiet_hours_end'),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
});

/**
 * A person's address on a transport, plus the policy for using it.
 *
 * Addresses live HERE, never in the transport module's config — otherwise
 * adding a recipient would require redeploying the module. signal-cli holds
 * one credential (its own registration); recipients are addresses, not
 * credentials. See design D8.
 * @owner celilo — who celilo pages and how; the calibration example in section 5
 */
export const routes = sqliteTable(
  'routes',
  {
    id: text('id').primaryKey(), // UUID
    personId: text('person_id')
      .notNull()
      .references(() => people.id, { onDelete: 'cascade' }),
    /** Module providing the `notification` capability. */
    transportModuleId: text('transport_module_id')
      .notNull()
      .references(() => modules.id, { onDelete: 'cascade' }),
    /** Transport-specific address (phone number, email, topic). */
    address: text('address').notNull(),
    /** Alerts below this severity are not delivered here. */
    severityFloor: text('severity_floor').$type<AlertSeverity>().notNull().default('warning'),
    /**
     * Whether replies can arrive over this route — set from whether the
     * transport implements `receive`. A route that cannot ack never stops
     * escalation (spec: *Unidirectional transport cannot acknowledge*).
     */
    canAck: integer('can_ack', { mode: 'boolean' }).notNull().default(false),
    enabled: integer('enabled', { mode: 'boolean' }).notNull().default(true),
    verifiedAt: integer('verified_at', { mode: 'timestamp' }),
    createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
    updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  },
  (table) => ({
    uniqueAddress: unique().on(table.personId, table.transportModuleId, table.address),
  }),
);

/**
 * Named, reusable escalation policy. Steps reference ROUTES rather than
 * people — "page Peter" is ambiguous about which transport to use.
 * @owner celilo — named escalation policy
 */
export const escalationPolicies = sqliteTable('escalation_policies', {
  id: text('id').primaryKey(), // UUID
  /** User-facing kebab-case name (e.g. "default", "critical"). */
  name: text('name').notNull().unique(),
  /**
   * Reserved escape hatch (design D13): quiet hours otherwise defer ALL
   * severities. Unused in MVP — the column exists so enabling it later is not
   * a migration.
   */
  bypassQuietHours: integer('bypass_quiet_hours', { mode: 'boolean' }).notNull().default(false),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
});

/**
 * One ordered step of an escalation policy. Delay is from escalation start.
 *
 * @owner celilo — the ordered steps of one
 */
export const escalationSteps = sqliteTable(
  'escalation_steps',
  {
    policyId: text('policy_id')
      .notNull()
      .references(() => escalationPolicies.id, { onDelete: 'cascade' }),
    /** 0-based order within the policy. */
    stepIndex: integer('step_index').notNull(),
    routeId: text('route_id')
      .notNull()
      .references(() => routes.id, { onDelete: 'cascade' }),
    /** Minutes after escalation begins. Step 0 is normally 0. */
    delayMinutes: integer('delay_minutes').notNull(),
  },
  (table) => ({
    pk: primaryKey({ columns: [table.policyId, table.stepIndex] }),
  }),
);

/**
 * What gets checked, how often, and how failures are routed.
 *
 * The module manifest's `hooks.health_check.interval` is only a SUGGESTION;
 * this row is the effective schedule and an operator edit survives module
 * upgrades. See design D3.
 * @owner celilo — what runs, how often, how failures route
 */
export const monitors = sqliteTable(
  'monitors',
  {
    id: text('id').primaryKey(), // UUID
    kind: text('kind').$type<MonitorKind>().notNull(),
    /** Module id for `module_hook`; audit check name for `builtin_check`. */
    target: text('target').notNull(),
    intervalMinutes: integer('interval_minutes').notNull(),
    /** Severity applied to `fail` items. Only `critical` pages. */
    severity: text('severity').$type<AlertSeverity>().notNull().default('critical'),
    /**
     * False for monitors watching the alerting system itself — a cascading
     * failure must not silence the component reporting it (design S4).
     */
    suppressible: integer('suppressible', { mode: 'boolean' }).notNull().default(true),
    enabled: integer('enabled', { mode: 'boolean' }).notNull().default(true),
    escalationPolicyId: text('escalation_policy_id').references(() => escalationPolicies.id, {
      onDelete: 'set null',
    }),
    lastRunAt: integer('last_run_at', { mode: 'timestamp' }),
    createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
    updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  },
  (table) => ({
    uniqueTarget: unique().on(table.kind, table.target),
  }),
);

/**
 * Record of each monitor execution. Exists so reconciliation can branch on
 * whether the last run actually ran (design D5) — without a persisted
 * outcome there is nothing to distinguish "found nothing wrong" from
 * "couldn't look".
 * @owner celilo — did the run execute at all
 */
export const monitorRuns = sqliteTable('monitor_runs', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  monitorId: text('monitor_id')
    .notNull()
    .references(() => monitors.id, { onDelete: 'cascade' }),
  ranAt: integer('ran_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  outcome: text('outcome').$type<MonitorRunOutcome>().notNull(),
  /** Populated when outcome is `error` (ssh failure, timeout, hook threw). */
  errorMessage: text('error_message'),
});

/**
 * A deliberate, time-boxed suppression source. Today only deploys create
 * these — a deploy is the same suppression mechanism as a machine-down alert,
 * with a window as the source instead of an ancestor alert (design D7).
 * @owner celilo — time-boxed deliberate suppression
 */
export const suppressionWindows = sqliteTable('suppression_windows', {
  id: text('id').primaryKey(), // UUID
  source: text('source').$type<'deploy'>().notNull(),
  scopeModuleId: text('scope_module_id')
    .notNull()
    .references(() => modules.id, { onDelete: 'cascade' }),
  startedAt: integer('started_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  /** Null while open. Set on deploy completion, including on failure. */
  endsAt: integer('ends_at', { mode: 'timestamp' }),
});

/**
 * The alert lifecycle. One row per alert key per firing episode: a resolve
 * followed by a re-fire mints a new row, so history is preserved.
 *
 * `acked`, `suppressed`, and `silenced` are deliberately THREE separate
 * concerns (design S5) — collapsing any two is how alerting systems become
 * untrustworthy.
 * @owner celilo — the alert lifecycle
 */
export const alerts = sqliteTable(
  'alerts',
  {
    id: text('id').primaryKey(), // UUID
    /** Stable key, e.g. `module:caddy/check:cert-validity`. See design D4. */
    key: text('key').notNull(),
    /**
     * Mirrors `key` while the alert is live; set to NULL on resolve. Backs the
     * "one live alert per key" unique index below — SQLite treats NULLs in a
     * unique index as distinct, so any number of resolved rows may share a key
     * while at most one live row may hold it. Written only by the
     * reconciliation code that sets `state`; the two must move together.
     */
    activeKey: text('active_key'),
    monitorId: text('monitor_id')
      .notNull()
      .references(() => monitors.id, { onDelete: 'cascade' }),
    state: text('state').$type<AlertState>().notNull().default('pending'),
    severity: text('severity').$type<AlertSeverity>().notNull(),
    firstFiredAt: integer('first_fired_at', { mode: 'timestamp' })
      .notNull()
      .default(sql`(unixepoch())`),
    lastSeenAt: integer('last_seen_at', { mode: 'timestamp' })
      .notNull()
      .default(sql`(unixepoch())`),
    /**
     * Notification is withheld until this instant, so an ancestor firing
     * moments later can establish suppression first, and so a failure that
     * clears immediately never pages (design S1).
     */
    graceUntil: integer('grace_until', { mode: 'timestamp' }).notNull(),
    /** Set when an ancestor ALERT is suppressing this one. */
    suppressedByAlertId: text('suppressed_by_alert_id'),
    /** Set when a suppression WINDOW (e.g. a deploy) is suppressing this one. */
    suppressedByWindowId: text('suppressed_by_window_id').references(() => suppressionWindows.id, {
      onDelete: 'set null',
    }),
    /** Escalation clock origin after suppression lifts — NOT firstFiredAt (S3). */
    unsuppressedAt: integer('unsuppressed_at', { mode: 'timestamp' }),
    /**
     * True between un-suppression and the next SUCCESSFUL run. While set, the
     * alert does not notify: if it recovered along with its ancestor it
     * resolves quietly instead of paging (design S2).
     */
    awaitingConfirmation: integer('awaiting_confirmation', { mode: 'boolean' })
      .notNull()
      .default(false),
    ackedBy: text('acked_by').references(() => people.id, { onDelete: 'set null' }),
    ackedAt: integer('acked_at', { mode: 'timestamp' }),
    /** Deliberate operator silence — distinct from suppression (S5). */
    silencedUntil: integer('silenced_until', { mode: 'timestamp' }),
    escalationStep: integer('escalation_step').notNull().default(0),
    nextEscalationAt: integer('next_escalation_at', { mode: 'timestamp' }),
    /**
     * Quiet hours defer DELIVERY, never the escalation clock (D13). When a step
     * falls due inside someone's window the step is still taken — the step
     * index advances and the next one is scheduled — and only the message
     * waits, held here until the window ends.
     */
    deferredUntil: integer('deferred_until', { mode: 'timestamp' }),
    /** Route the deferred message is owed to. */
    deferredRouteId: text('deferred_route_id').references(() => routes.id, {
      onDelete: 'set null',
    }),
    // No escalation policy is stored here on purpose. It lives on the MONITOR
    // and is resolved fresh on every sweep, so assigning a policy takes effect
    // on alerts that are already firing. Snapshotting it at alert creation made
    // `escalation-policy assign` a no-op for exactly the alert an operator was
    // trying to route — the one already paging nobody (#481).
    message: text('message').notNull(),
    details: text('details'),
    resolvedAt: integer('resolved_at', { mode: 'timestamp' }),
  },
  (table) => ({
    /**
     * One live alert per key. Resolved rows are exempt so history accumulates:
     * `activeKey` mirrors `key` while live and is NULL once resolved, and
     * SQLite treats NULLs in a unique index as distinct.
     */
    liveKey: uniqueIndex('alerts_live_key_idx').on(table.activeKey),
    keyLookup: index('alerts_key_idx').on(table.key),
  }),
);

/**
 * One outbound message, and the token that authorises a reply to it.
 *
 * Per DELIVERY, not per alert: the token identifies WHO replied, which is
 * what "an ack from the secondary is broadcast to everyone paged" needs, and
 * doubles as the audit trail (design D10).
 * @owner celilo — one outbound message + its reply token
 */
export const notificationDeliveries = sqliteTable('notification_deliveries', {
  id: text('id').primaryKey(), // UUID
  /** Short operator-typable token, unique across live deliveries. */
  token: text('token').notNull().unique(),
  kind: text('kind').$type<'alert' | 'interview'>().notNull(),
  /**
   * `alerts.id` when kind is `alert`; a BUS event id when kind is
   * `interview`. Deliberately not a foreign key — the event bus is a separate
   * SQLite database, so no FK can span it.
   */
  targetId: text('target_id').notNull(),
  routeId: text('route_id')
    .notNull()
    .references(() => routes.id, { onDelete: 'cascade' }),
  sentAt: integer('sent_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
  expiresAt: integer('expires_at', { mode: 'timestamp' }).notNull(),
  /** Set when a reply consumed this token. */
  consumedAt: integer('consumed_at', { mode: 'timestamp' }),
});

/**
 * Type exports for use in application code
 */
export type Module = typeof modules.$inferSelect;
export type NewModule = typeof modules.$inferInsert;
export type ModuleConfig = typeof moduleConfigs.$inferSelect;
export type NewModuleConfig = typeof moduleConfigs.$inferInsert;
export type Capability = typeof capabilities.$inferSelect;
export type NewCapability = typeof capabilities.$inferInsert;
export type Secret = typeof secrets.$inferSelect;
export type NewSecret = typeof secrets.$inferInsert;
export type SystemConfig = typeof systemConfig.$inferSelect;
export type NewSystemConfig = typeof systemConfig.$inferInsert;
export type SystemSecret = typeof systemSecrets.$inferSelect;
export type NewSystemSecret = typeof systemSecrets.$inferInsert;
export type ModuleIntegrity = typeof moduleIntegrity.$inferSelect;
export type NewModuleIntegrity = typeof moduleIntegrity.$inferInsert;
export type IpAllocation = typeof ipAllocations.$inferSelect;
export type NewIpAllocation = typeof ipAllocations.$inferInsert;
export type IpReservation = typeof ipReservations.$inferSelect;
export type NewIpReservation = typeof ipReservations.$inferInsert;
export type VmidReservation = typeof vmidReservations.$inferSelect;
export type NewVmidReservation = typeof vmidReservations.$inferInsert;
export type ModuleBuild = typeof moduleBuilds.$inferSelect;
export type NewModuleBuild = typeof moduleBuilds.$inferInsert;
export type ContainerService = typeof containerServices.$inferSelect;
export type NewContainerService = typeof containerServices.$inferInsert;
export type Machine = typeof machines.$inferSelect;
export type NewMachine = typeof machines.$inferInsert;
export type ModuleInfrastructure = typeof moduleInfrastructure.$inferSelect;
export type NewModuleInfrastructure = typeof moduleInfrastructure.$inferInsert;
export type WebRoute = typeof webRoutes.$inferSelect;
export type NewWebRoute = typeof webRoutes.$inferInsert;
export type BackupStorage = typeof backupStorages.$inferSelect;
export type NewBackupStorage = typeof backupStorages.$inferInsert;
export type Backup = typeof backups.$inferSelect;
export type NewBackup = typeof backups.$inferInsert;
export type ModuleOperation = typeof moduleOperations.$inferSelect;
export type NewModuleOperation = typeof moduleOperations.$inferInsert;
export type AspectApproval = typeof aspectApprovals.$inferSelect;
export type NewAspectApproval = typeof aspectApprovals.$inferInsert;
export type ApiPrincipal = typeof apiPrincipals.$inferSelect;
export type NewApiPrincipal = typeof apiPrincipals.$inferInsert;
export type Person = typeof people.$inferSelect;
export type NewPerson = typeof people.$inferInsert;
export type Route = typeof routes.$inferSelect;
export type NewRoute = typeof routes.$inferInsert;
export type EscalationPolicy = typeof escalationPolicies.$inferSelect;
export type NewEscalationPolicy = typeof escalationPolicies.$inferInsert;
export type EscalationStep = typeof escalationSteps.$inferSelect;
export type NewEscalationStep = typeof escalationSteps.$inferInsert;
export type Monitor = typeof monitors.$inferSelect;
export type NewMonitor = typeof monitors.$inferInsert;
export type MonitorRun = typeof monitorRuns.$inferSelect;
export type NewMonitorRun = typeof monitorRuns.$inferInsert;
export type SuppressionWindow = typeof suppressionWindows.$inferSelect;
export type NewSuppressionWindow = typeof suppressionWindows.$inferInsert;
export type Alert = typeof alerts.$inferSelect;
export type NewAlert = typeof alerts.$inferInsert;
export type NotificationDelivery = typeof notificationDeliveries.$inferSelect;
export type NewNotificationDelivery = typeof notificationDeliveries.$inferInsert;
export type CapabilityBinding = typeof capabilityBindings.$inferSelect;
export type NewCapabilityBinding = typeof capabilityBindings.$inferInsert;
export type BuildBusHookRun = typeof buildBusHookRuns.$inferSelect;
export type NewBuildBusHookRun = typeof buildBusHookRuns.$inferInsert;