// server/db/schema.ts
import { sqliteTable, text, integer, real, uniqueIndex } from "drizzle-orm/sqlite-core";

export const machines = sqliteTable("machines", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  hostname: text("hostname").notNull(),
  port: integer("port").notNull().default(22),
  osFamily: text("os_family").notNull().default("unknown"),
  osVersion: text("os_version"),
  osCodename: text("os_codename"),
  arch: text("arch"),
  machineKind: text("machine_kind"),          // physical | vm | proxmox_host | lxc | raspberry_pi | workstation | unknown
  virtualization: text("virtualization"),     // none | qemu | kvm | lxc | docker | vmware | ...
  hardwareProfile: text("hardware_profile"),  // generic_vm | baremetal_server | raspberry_pi | gpu_server | proxmox_host | ...
  username: text("username").notNull(),
  encPassword: text("enc_password").notNull(),
  encSudoPassword: text("enc_sudo_password"),
  aptProxyMode: text("apt_proxy_mode").notNull().default("direct"),
  aptProxyUrl: text("apt_proxy_url"),
  status: text("status").notNull().default("unknown"),
  lastCheckedAt: text("last_checked_at"),
  lastSeenAt: text("last_seen_at"),
  createdAt: text("created_at").notNull(),
  updatedAt: text("updated_at"),
  deletedAt: text("deleted_at"),
});

export const snapshots = sqliteTable("snapshots", {
  id: text("id").primaryKey(),
  machineId: text("machine_id").notNull().references(() => machines.id, { onDelete: "cascade" }),
  kind: text("kind").notNull().default("apt_update_analyze"),
  schemaVersion: integer("schema_version").notNull().default(1),
  checkedAt: text("checked_at").notNull(),
  status: text("status").notNull(),
  payloadJson: text("payload_json").notNull(),
  importantJson: text("important_json"),
  rawLogPath: text("raw_log_path"),
  rawArtifactId: text("raw_artifact_id"),
  sourceJobId: text("source_job_id"),
});

export const executions = sqliteTable("executions", {
  id: text("id").primaryKey(),
  machineId: text("machine_id").notNull().references(() => machines.id, { onDelete: "cascade" }),
  action: text("action").notNull(),
  mode: text("mode").notNull().default("manual"),
  schemaVersion: integer("schema_version").notNull().default(1),
  startedAt: text("started_at").notNull(),
  finishedAt: text("finished_at"),
  status: text("status").notNull(),
  requestId: text("request_id"),
  jobId: text("job_id"),
  resultJson: text("result_json"),
  importantJson: text("important_json"),
  reportPath: text("report_path"),
  rawLogPath: text("raw_log_path"),
  reportId: text("report_id"),
  exitCode: integer("exit_code"),
  errorKind: text("error_kind"),
  errorMessage: text("error_message"),
});

export const machineState = sqliteTable("machine_state", {
  machineId: text("machine_id").primaryKey().references(() => machines.id, { onDelete: "cascade" }),
  status: text("status").notNull(),
  aptStatus: text("apt_status"),
  aptUpdatesCount: integer("apt_updates_count").notNull().default(0),
  aptRebootRequired: integer("apt_reboot_required").notNull().default(0),
  aptLastAnalyzeAt: text("apt_last_analyze_at"),
  dockerStatus: text("docker_status"),
  dockerInstalled: integer("docker_installed").notNull().default(0),
  dockerStacksCount: integer("docker_stacks_count").notNull().default(0),
  dockerUpdatesCount: integer("docker_updates_count").notNull().default(0),
  dockerPruneAvailable: integer("docker_prune_available").notNull().default(0),
  postInstallStatus: text("post_install_status"),
  metricsLastCollectedAt: text("metrics_last_collected_at"),
  cpuLoad1: real("cpu_load1"),
  memoryUsedPercent: real("memory_used_percent"),
  rootUsedPercent: real("root_used_percent"),
  diskWarningsCount: integer("disk_warnings_count").notNull().default(0),
  hardwareWarningsCount: integer("hardware_warnings_count").notNull().default(0),
  runningJobId: text("running_job_id"),
  lastErrorKind: text("last_error_kind"),
  lastErrorMessage: text("last_error_message"),
  updatedAt: text("updated_at").notNull(),
});

export const machineHardware = sqliteTable("machine_hardware", {
  machineId: text("machine_id").primaryKey().references(() => machines.id, { onDelete: "cascade" }),
  probeSnapshotId: text("probe_snapshot_id"),
  cpuModel: text("cpu_model"),
  cpuCores: integer("cpu_cores"),
  memoryBytes: integer("memory_bytes"),
  gpusJson: text("gpus_json"),
  disksJson: text("disks_json"),
  networkJson: text("network_json"),
  firmwareJson: text("firmware_json"),
  driverJson: text("driver_json"),
  warningsJson: text("warnings_json"),
  updatedAt: text("updated_at").notNull(),
});

export const machineMetricsLatest = sqliteTable("machine_metrics_latest", {
  machineId: text("machine_id").primaryKey().references(() => machines.id, { onDelete: "cascade" }),
  snapshotId: text("snapshot_id"),
  collectedAt: text("collected_at").notNull(),
  cpuLoad1: real("cpu_load1"),
  cpuLoad5: real("cpu_load5"),
  cpuCores: integer("cpu_cores"),
  memoryTotalBytes: integer("memory_total_bytes"),
  memoryUsedBytes: integer("memory_used_bytes"),
  memoryAvailableBytes: integer("memory_available_bytes"),
  memoryUsedPercent: real("memory_used_percent"),
  filesystemsJson: text("filesystems_json"),
  rootUsedPercent: real("root_used_percent"),
  warningsJson: text("warnings_json"),
});

export const machineEvents = sqliteTable("machine_events", {
  id: text("id").primaryKey(),
  machineId: text("machine_id").references(() => machines.id, { onDelete: "cascade" }),
  eventType: text("event_type").notNull(),
  severity: text("severity").notNull(),       // info | warning | error
  createdAt: text("created_at").notNull(),
  actorType: text("actor_type"),              // user | system | schedule | hermes
  actorId: text("actor_id"),
  snapshotId: text("snapshot_id"),
  executionId: text("execution_id"),
  jobId: text("job_id"),
  message: text("message"),
  payloadJson: text("payload_json"),
});

export const importantMessages = sqliteTable("important_messages", {
  id: text("id").primaryKey(),
  machineId: text("machine_id").references(() => machines.id, { onDelete: "cascade" }),
  source: text("source").notNull(),           // apt | docker | post_install | ssh | system
  category: text("category").notNull(),        // error | warning | future_major_change | ...
  severity: text("severity").notNull(),
  packageName: text("package_name"),
  component: text("component"),
  message: text("message").notNull(),
  rawLineRef: text("raw_line_ref"),
  snapshotId: text("snapshot_id"),
  executionId: text("execution_id"),
  firstSeenAt: text("first_seen_at").notNull(),
  lastSeenAt: text("last_seen_at").notNull(),
  acknowledged: integer("acknowledged").notNull().default(0),
  acknowledgedAt: text("acknowledged_at"),
  acknowledgedBy: text("acknowledged_by"),
  payloadJson: text("payload_json"),
});

export const reports = sqliteTable("reports", {
  id: text("id").primaryKey(),
  machineId: text("machine_id").references(() => machines.id, { onDelete: "cascade" }),
  executionId: text("execution_id"),
  kind: text("kind").notNull(),                // machine | global | cleanup | hermes
  title: text("title").notNull(),
  path: text("path").notNull(),
  createdAt: text("created_at").notNull(),
  pinned: integer("pinned").notNull().default(0),
  summaryJson: text("summary_json"),
});

export const rawArtifacts = sqliteTable("raw_artifacts", {
  id: text("id").primaryKey(),
  machineId: text("machine_id").references(() => machines.id, { onDelete: "cascade" }),
  kind: text("kind").notNull(),                // raw_log | rendered_template | export | screenshot
  path: text("path").notNull(),
  bytes: integer("bytes"),
  sha256: text("sha256"),
  createdAt: text("created_at").notNull(),
  expiresAt: text("expires_at"),
  pinned: integer("pinned").notNull().default(0),
  redacted: integer("redacted").notNull().default(1),
  retentionPolicy: text("retention_policy"),   // default | failed | pinned | short
  deletedAt: text("deleted_at"),
  deleteReason: text("delete_reason"),
  metadataJson: text("metadata_json"),
});

// --- Préexistant (WIP api_clients) : NE PAS supprimer ---
export const apiClients = sqliteTable(
  "api_clients",
  {
    id: text("id").primaryKey(),
    name: text("name").notNull(),
    tokenPrefix: text("token_prefix").notNull(),
    tokenHash: text("token_hash").notNull(),
    scopesJson: text("scopes_json").notNull(),
    createdAt: text("created_at").notNull(),
    lastUsedAt: text("last_used_at"),
    revokedAt: text("revoked_at"),
  },
  (table) => ({
    tokenHashIdx: uniqueIndex("api_clients_token_hash_unique").on(table.tokenHash),
  }),
);

// --- Phase 2 : credentials isolés (non destructif) ---
export const machineCredentials = sqliteTable("machine_credentials", {
  machineId: text("machine_id").primaryKey().references(() => machines.id, { onDelete: "cascade" }),
  authMethod: text("auth_method").notNull(),        // password | ssh_key
  encPassword: text("enc_password"),
  encSudoPassword: text("enc_sudo_password"),
  encPrivateKey: text("enc_private_key"),
  encKeyPassphrase: text("enc_key_passphrase"),
  sudoMode: text("sudo_mode").notNull(),            // same_as_ssh | separate | none
  createdAt: text("created_at").notNull(),
  updatedAt: text("updated_at").notNull(),
  lastTestAt: text("last_test_at"),
  status: text("status"),                           // ok | error | unknown
});

export const machineHostKeys = sqliteTable("machine_host_keys", {
  id: text("id").primaryKey(),
  machineId: text("machine_id").references(() => machines.id, { onDelete: "cascade" }),
  hostname: text("hostname").notNull(),
  port: integer("port").notNull(),
  keyType: text("key_type"),
  fingerprintSha256: text("fingerprint_sha256").notNull(),
  publicKey: text("public_key"),
  status: text("status").notNull(),                 // approved | changed | rejected | unknown
  firstSeenAt: text("first_seen_at").notNull(),
  lastSeenAt: text("last_seen_at").notNull(),
});