import { sql } from "drizzle-orm"
import {
  integer,
  real,
  sqliteTable,
  text,
  uniqueIndex,
} from "drizzle-orm/sqlite-core"

// ── servers ────────────────────────────────────────────────────────────────────

export const servers = sqliteTable("servers", {
  id:        integer("id").primaryKey({ autoIncrement: true }),
  name:      text("name").notNull().default(""),
  host:      text("host").notNull(),
  port:      integer("port").notNull().default(443),
  username:  text("username").notNull().default("admin"),
  password:  text("password").notNull().default(""),
  useSsl:    integer("use_ssl", { mode: "boolean" }).notNull().default(true),
  verifySsl: integer("verify_ssl", { mode: "boolean" }).notNull().default(false),

  // metadata set manually by user
  type:      text("type", { enum: ["jump-host", "exit-node", "home-router"] })
               .notNull().default("home-router"),
  site:      text("site").notNull().default(""),
  country:   text("country").notNull().default(""),
  asn:       text("asn").notNull().default(""),
  comment:   text("comment").notNull().default(""),
  enabled:   integer("enabled", { mode: "boolean" }).notNull().default(true),

  /** Подсеть LAN (home-router), текст из формы */
  lanSubnet: text("lan_subnet").notNull().default(""),
  /** JSON-массив WAN-аплинков [{ id, name, isp, iface, ip, maxDl, maxUl }, …] */
  wanUplinks: text("wan_uplinks").notNull().default("[]"),
  /** Адрес в оверлее wg-flow (экспортёр IPFIX), например 10.255.254.5 */
  mgmtTunnelIp: text("mgmt_tunnel_ip").notNull().default(""),

  createdAt: text("created_at").notNull().default(sql`(datetime('now'))`),
  updatedAt: text("updated_at").notNull().default(sql`(datetime('now'))`),
})

// ── server_snapshots ───────────────────────────────────────────────────────────

export const serverSnapshots = sqliteTable("server_snapshots", {
  id:             integer("id").primaryKey({ autoIncrement: true }),
  serverId:       integer("server_id")
                    .notNull()
                    .references(() => servers.id, { onDelete: "cascade" }),
  polledAt:       text("polled_at").notNull(),
  status:         text("status", { enum: ["online", "offline"] }).notNull(),
  latencyMs:      real("latency_ms"),

  // data from RouterOS
  rosVersion:     text("ros_version"),
  boardName:      text("board_name"),
  uptime:         text("uptime"),
  cpuLoad:        integer("cpu_load"),
  freeMemory:     integer("free_memory"),
  totalMemory:    integer("total_memory"),
  identityName:   text("identity_name"),

  // raw JSON payloads for future use
  rawInterfaces:  text("raw_interfaces"),
  rawIpAddresses: text("raw_ip_addresses"),
})

// ── filter_rules ───────────────────────────────────────────────────────────────

export const filterRules = sqliteTable("filter_rules", {
  id:              integer("id").primaryKey({ autoIncrement: true }),
  serverId:        integer("server_id")
                    .notNull()
                    .references(() => servers.id, { onDelete: "cascade" }),
  sortOrder:       integer("sort_order").notNull().default(0),
  community:       text("community").notNull(),
  communityName:   text("community_name"),
  action:          text("action", { enum: ["route", "blackhole"] }).notNull().default("route"),
  gateway:         text("gateway").notNull().default(""),
  gatewayTunnelId: text("gateway_tunnel_id").notNull().default(""),
  description:     text("description").notNull().default(""),
  createdAt:       text("created_at").notNull().default(sql`(datetime('now'))`),
  updatedAt:       text("updated_at").notNull().default(sql`(datetime('now'))`),
})

// ── recursive_routes ───────────────────────────────────────────────────────────

export const recursiveRoutes = sqliteTable("recursive_routes", {
  id:            integer("id").primaryKey({ autoIncrement: true }),
  serverId:      integer("server_id")
                  .notNull()
                  .references(() => servers.id, { onDelete: "cascade" }),
  sortOrder:     integer("sort_order").notNull().default(0),
  dstAddress:    text("dst_address").notNull(),
  gateway:       text("gateway").notNull(),
  distance:      integer("distance").notNull().default(1),
  scope:         integer("scope"),
  targetScope:   integer("target_scope"),
  routingTable:  text("routing_table").notNull().default("main"),
  checkGateway:  text("check_gateway").notNull().default(""),
  country:       text("country").notNull().default(""),
  comment:       text("comment").notNull().default(""),
  disabled:      integer("disabled", { mode: "boolean" }).notNull().default(false),
  createdAt:     text("created_at").notNull().default(sql`(datetime('now'))`),
  updatedAt:     text("updated_at").notNull().default(sql`(datetime('now'))`),
})

// ── traffic collection settings ────────────────────────────────────────────────

export const trafficSettings = sqliteTable("traffic_settings", {
  id:             integer("id").primaryKey(),
  enabled:        integer("enabled", { mode: "boolean" }).notNull().default(true),
  intervalSec:    integer("interval_sec").notNull().default(30),
  retentionDays:  integer("retention_days").notNull().default(14),
  lastCollectedAt: text("last_collected_at"),
  lastDurationMs: integer("last_duration_ms"),
  lastError:      text("last_error"),
  createdAt:      text("created_at").notNull().default(sql`(datetime('now'))`),
  updatedAt:      text("updated_at").notNull().default(sql`(datetime('now'))`),
})

/** Настройки фоновой проверки доступности RouterOS REST API по серверам каталога. */
export const serversApiPingSettings = sqliteTable("servers_api_ping_settings", {
  id:             integer("id").primaryKey(),
  enabled:        integer("enabled", { mode: "boolean" }).notNull().default(false),
  intervalSec:    integer("interval_sec").notNull().default(120),
  lastCollectedAt: text("last_collected_at"),
  lastDurationMs: integer("last_duration_ms"),
  lastError:      text("last_error"),
  createdAt:      text("created_at").notNull().default(sql`(datetime('now'))`),
  updatedAt:      text("updated_at").notNull().default(sql`(datetime('now'))`),
})

/** Сырые пробы REST `/system/identity` по серверам — для UI сбора и для `buildSignalSnapshot` (алерты). */
export const serversRestPingSamples = sqliteTable("servers_rest_ping_samples", {
  id:         integer("id").primaryKey({ autoIncrement: true }),
  serverId:   integer("server_id")
    .notNull()
    .references(() => servers.id, { onDelete: "cascade" }),
  sampledAt:  text("sampled_at").notNull(),
  ok:         integer("ok", { mode: "boolean" }).notNull(),
  latencyMs:  integer("latency_ms"),
  error:      text("error"),
})

/** Сырые снимки GRE для алертов — пишет джоба `gre_bgp`. */
export const alertGreTunnelSamples = sqliteTable("alert_gre_tunnel_samples", {
  id:          integer("id").primaryKey({ autoIncrement: true }),
  sampledAt:   text("sampled_at").notNull(),
  targetLabel: text("target_label").notNull(),
  status:      text("status").notNull(),
})

/** Сырые снимки BGP-сессий для алертов — пишет джоба `gre_bgp`. */
export const alertBgpPeerSamples = sqliteTable("alert_bgp_peer_samples", {
  id:        integer("id").primaryKey({ autoIncrement: true }),
  sampledAt: text("sampled_at").notNull(),
  peerKey:   text("peer_key").notNull(),
  state:     text("state").notNull(),
})

// ── raw traffic samples (per server/interface/timepoint) ──────────────────────

export const trafficFlowSettings = sqliteTable("traffic_flow_settings", {
  id:              integer("id").primaryKey(),
  enabled:         integer("enabled", { mode: "boolean" }).notNull().default(false),
  collectorIp:     text("collector_ip").notNull().default("10.255.254.1"),
  flowListenPort:  integer("flow_listen_port").notNull().default(4739),
  wgListenPort:    integer("wg_listen_port").notNull().default(51821),
  prefix:          text("prefix").notNull().default("10.255.254.0/24"),
  publicEndpoint:  text("public_endpoint").notNull().default(""),
  hostPublicKey:   text("host_public_key").notNull().default(""),
  hostPrivateKey:  text("host_private_key").notNull().default(""),
  hubServerId:     integer("hub_server_id"),
  retentionHours:  integer("retention_hours").notNull().default(24),
  topN:            integer("top_n").notNull().default(200),
  lastDatagramAt:  text("last_datagram_at"),
  lastExporterIp:  text("last_exporter_ip"),
  lastError:       text("last_error"),
  packetsReceived: integer("packets_received").notNull().default(0),
  peersJson:       text("peers_json").notNull().default("[]"),
  createdAt:       text("created_at").notNull().default(sql`(datetime('now'))`),
  updatedAt:       text("updated_at").notNull().default(sql`(datetime('now'))`),
})

export const flowMinuteStats = sqliteTable("flow_minute_stats", {
  serverId:      integer("server_id").notNull(),
  bucketAt:      text("bucket_at").notNull(),
  bytes:         integer("bytes").notNull().default(0),
  packets:       integer("packets").notNull().default(0),
  uniqueSrc:     integer("unique_src").notNull().default(0),
  uniqueDst:     integer("unique_dst").notNull().default(0),
  conversations: integer("conversations").notNull().default(0),
}, (t) => [
  uniqueIndex("idx_flow_minute_stats_pk").on(t.serverId, t.bucketAt),
])

export const flowMinuteDims = sqliteTable("flow_minute_dims", {
  serverId: integer("server_id").notNull(),
  bucketAt: text("bucket_at").notNull(),
  dim:      text("dim").notNull(),
  key:      text("key").notNull(),
  bytes:    integer("bytes").notNull().default(0),
  packets:  integer("packets").notNull().default(0),
}, (t) => [
  uniqueIndex("idx_flow_minute_dims_pk").on(t.serverId, t.bucketAt, t.dim, t.key),
])

export const flowDailyDims = sqliteTable("flow_daily_dims", {
  serverId: integer("server_id").notNull(),
  day:      text("day").notNull(),
  dim:      text("dim").notNull(),
  key:      text("key").notNull(),
  bytes:    integer("bytes").notNull().default(0),
  packets:  integer("packets").notNull().default(0),
}, (t) => [
  uniqueIndex("idx_flow_daily_dims_pk").on(t.serverId, t.day, t.dim, t.key),
])

export const flowBuckets = sqliteTable("flow_buckets", {
  id:        integer("id").primaryKey({ autoIncrement: true }),
  serverId:  integer("server_id")
               .notNull()
               .references(() => servers.id, { onDelete: "cascade" }),
  bucketAt:  text("bucket_at").notNull(),
  src:       text("src").notNull(),
  dst:       text("dst").notNull(),
  proto:     integer("proto").notNull().default(0),
  srcPort:   integer("src_port").notNull().default(0),
  dstPort:   integer("dst_port").notNull().default(0),
  bytes:     integer("bytes").notNull().default(0),
  packets:   integer("packets").notNull().default(0),
  inIface:   text("in_iface").notNull().default(""),
  outIface:  text("out_iface").notNull().default(""),
  nextHop:   text("next_hop").notNull().default(""),
  flowStartMs: integer("flow_start_ms").notNull().default(0),
  flowEndMs:   integer("flow_end_ms").notNull().default(0),
}, (t) => [
  uniqueIndex("idx_flow_buckets_unique").on(
    t.serverId, t.bucketAt, t.src, t.dst, t.proto, t.srcPort, t.dstPort, t.inIface,
  ),
])

export const flowIpMeta = sqliteTable("flow_ip_meta", {
  prefix:    text("prefix").primaryKey(),
  asn:       integer("asn").notNull().default(0),
  country:   text("country").notNull().default(""),
  lat:       real("lat"),
  lng:       real("lng"),
  holder:    text("holder").notNull().default(""),
  ok:        integer("ok").notNull().default(1),
  fetchedAt: text("fetched_at").notNull(),
})

export const flowAsnMeta = sqliteTable("flow_asn_meta", {
  asn:       integer("asn").primaryKey(),
  holder:    text("holder").notNull().default(""),
  fetchedAt: text("fetched_at").notNull(),
})

export const trafficSamples = sqliteTable("traffic_samples", {
  id:            integer("id").primaryKey({ autoIncrement: true }),
  serverId:      integer("server_id")
                  .notNull()
                  .references(() => servers.id, { onDelete: "cascade" }),
  interfaceName: text("interface_name").notNull(),
  peerPublicKey: text("peer_public_key").notNull().default(""),
  sampledAt:     text("sampled_at").notNull(),
  rxBytes:       integer("rx_bytes").notNull().default(0),
  txBytes:       integer("tx_bytes").notNull().default(0),
  rxBps:         integer("rx_bps").notNull().default(0),
  txBps:         integer("tx_bps").notNull().default(0),
  running:       integer("running", { mode: "boolean" }).notNull().default(false),
  disabled:      integer("disabled", { mode: "boolean" }).notNull().default(false),
})

// ── uptime monitor settings ────────────────────────────────────────────────────

export const uptimeSettings = sqliteTable("uptime_settings", {
  id:             integer("id").primaryKey(),
  /** Устаревший агрегат: синхронизируется как resources ∨ ping ∨ speed (для совместимости). */
  enabled:        integer("enabled", { mode: "boolean" }).notNull().default(true),
  resourcesEnabled: integer("resources_enabled", { mode: "boolean" }).notNull().default(true),
  pingEnabled:    integer("ping_enabled", { mode: "boolean" }).notNull().default(true),
  speedEnabled:   integer("speed_enabled", { mode: "boolean" }).notNull().default(true),
  intervalSec:    integer("interval_sec").notNull().default(15),
  probeIntervalSec: integer("probe_interval_sec").notNull().default(15),
  speedIntervalSec: integer("speed_interval_sec").notNull().default(60),
  retentionDays:  integer("retention_days").notNull().default(14),
  lastCollectedAt: text("last_collected_at"),
  lastDurationMs: integer("last_duration_ms"),
  lastError:      text("last_error"),
  createdAt:      text("created_at").notNull().default(sql`(datetime('now'))`),
  updatedAt:      text("updated_at").notNull().default(sql`(datetime('now'))`),
})

export const uptimeProbes = sqliteTable("uptime_probes", {
  id:          text("id").primaryKey(),
  srcServerId: integer("src_server_id")
                .notNull()
                .references(() => servers.id, { onDelete: "cascade" }),
  srcInterface: text("src_interface").notNull().default(""),
  name:        text("name").notNull(),
  target:      text("target").notNull(),
  probeFilter: text("probe_filter").notNull().default("—"),
  enabled:     integer("enabled", { mode: "boolean" }).notNull().default(true),
  /** 0 — брать глобальный probe_interval_sec из uptime_settings */
  intervalSec: integer("interval_sec").notNull().default(0),
  /** Выводить пробу в блоке «Активные пробы» на дашборде */
  showOnDashboard: integer("show_on_dashboard", { mode: "boolean" }).notNull().default(false),
  sortOrder:   integer("sort_order").notNull().default(0),
  createdAt:   text("created_at").notNull().default(sql`(datetime('now'))`),
  updatedAt:   text("updated_at").notNull().default(sql`(datetime('now'))`),
})

export const uptimeProbeSamples = sqliteTable("uptime_probe_samples", {
  id:        integer("id").primaryKey({ autoIncrement: true }),
  probeId:   text("probe_id")
              .notNull()
              .references(() => uptimeProbes.id, { onDelete: "cascade" }),
  sampledAt: text("sampled_at").notNull(),
  rttMs:     integer("rtt_ms"),
  lossPct:   integer("loss_pct").notNull().default(0),
  status:    text("status", { enum: ["up", "warn", "down"] }).notNull().default("down"),
})

export const uptimeResourceSamples = sqliteTable("uptime_resource_samples", {
  id:            integer("id").primaryKey({ autoIncrement: true }),
  serverId:      integer("server_id")
                  .notNull()
                  .references(() => servers.id, { onDelete: "cascade" }),
  sampledAt:     text("sampled_at").notNull(),
  status:        text("status", { enum: ["online", "offline"] }).notNull().default("offline"),
  cpuLoad:       integer("cpu_load").notNull().default(0),
  freeMemory:    integer("free_memory").notNull().default(0),
  totalMemory:   integer("total_memory").notNull().default(0),
  freeHddSpace:  integer("free_hdd_space").notNull().default(0),
  totalHddSpace: integer("total_hdd_space").notNull().default(0),
  uptimeSeconds: integer("uptime_seconds").notNull().default(0),
  boardName:     text("board_name").notNull().default(""),
  rosVersion:    text("ros_version").notNull().default(""),
})

export const uptimeSpeedProbes = sqliteTable("uptime_speed_probes", {
  id:           text("id").primaryKey(),
  srcServerId:  integer("src_server_id")
                 .notNull()
                 .references(() => servers.id, { onDelete: "cascade" }),
  dstServerId:  integer("dst_server_id")
                 .notNull()
                 .references(() => servers.id, { onDelete: "cascade" }),
  srcInterface: text("src_interface").notNull().default(""),
  dstInterface: text("dst_interface").notNull().default(""),
  protocol:     text("protocol", { enum: ["tcp", "udp"] }).notNull().default("tcp"),
  direction:    text("direction", { enum: ["transmit", "receive", "both"] }).notNull().default("both"),
  durationSec:  integer("duration_sec").notNull().default(10),
  enabled:      integer("enabled", { mode: "boolean" }).notNull().default(true),
  lastRunAt:    text("last_run_at"),
  lastTxAvgMbps: real("last_tx_avg_mbps"),
  lastRxAvgMbps: real("last_rx_avg_mbps"),
  lastStatus:   text("last_status", { enum: ["done", "error"] }),
  lastError:    text("last_error"),
  lastPingRttMs:   integer("last_ping_rtt_ms"),
  lastPingLossPct: integer("last_ping_loss_pct"),
  lastPingAt:      text("last_ping_at"),
  lastPingError:   text("last_ping_error"),
  sortOrder:    integer("sort_order").notNull().default(0),
  createdAt:    text("created_at").notNull().default(sql`(datetime('now'))`),
  updatedAt:    text("updated_at").notNull().default(sql`(datetime('now'))`),
})

// ── EvoBGP integration (URL + API key на сервере) ─────────────────────────────

export const evobgpSettings = sqliteTable("evobgp_settings", {
  id:       integer("id").primaryKey(),
  baseUrl:  text("base_url").notNull().default(""),
  apiKey:   text("api_key").notNull().default(""),
  enabled:  integer("enabled", { mode: "boolean" }).notNull().default(false),
  updatedAt: text("updated_at").notNull().default(sql`(datetime('now'))`),
})

/** Единственная строка id=1: токен и чат Telegram для оповещений. */
export const alertTelegramSettings = sqliteTable("alert_telegram_settings", {
  id:        integer("id").primaryKey(),
  botToken:  text("bot_token").notNull().default(""),
  chatId:    text("chat_id").notNull().default(""),
  /** Тема супергруппы (forum): `message_thread_id` в Bot API; NULL — общий чат */
  messageThreadId: integer("message_thread_id"),
  updatedAt: text("updated_at").notNull().default(sql`(datetime('now'))`),
})

export const acmeSettings = sqliteTable("acme_settings", {
  id:                 integer("id").primaryKey(),
  directoryUrl:       text("directory_url").notNull().default("https://acme-v02.api.letsencrypt.org/directory"),
  cloudflareApiToken: text("cloudflare_api_token").notNull().default(""),
  defaultZoneId:      text("default_zone_id").notNull().default(""),
  accountPrivateKey:  text("account_private_key").notNull().default(""),
  updatedAt:          text("updated_at").notNull().default(sql`(datetime('now'))`),
})

export const certificateIssueJobs = sqliteTable("certificate_issue_jobs", {
  id:          text("id").primaryKey(),
  status:      text("status", { enum: ["queued", "running", "done", "failed"] }).notNull().default("queued"),
  step:        text("step").notNull().default("queued"),
  source:      text("source", { enum: ["manual", "scheduler"] }).notNull().default("manual"),
  serverId:    text("server_id").notNull(),
  certName:    text("cert_name").notNull(),
  domainNames: text("domain_names").notNull(),
  keyType:     text("key_type").notNull().default("rsa2048"),
  trustStore:  text("trust_store").notNull().default("www,api"),
  requestedAt: text("requested_at").notNull().default(sql`(datetime('now'))`),
  startedAt:   text("started_at"),
  finishedAt:  text("finished_at"),
  error:       text("error"),
})

export const certificateRenewSettings = sqliteTable("certificate_renew_settings", {
  id:              integer("id").primaryKey(),
  enabled:         integer("enabled", { mode: "boolean" }).notNull().default(true),
  intervalSec:     integer("interval_sec").notNull().default(21600),
  renewBeforeDays: integer("renew_before_days").notNull().default(30),
  lastCollectedAt: text("last_collected_at"),
  lastDurationMs:  integer("last_duration_ms"),
  lastError:       text("last_error"),
  updatedAt:       text("updated_at").notNull().default(sql`(datetime('now'))`),
})

export const backupScheduleSettings = sqliteTable("backup_schedule_settings", {
  id:             integer("id").primaryKey(),
  enabled:        integer("enabled", { mode: "boolean" }).notNull().default(true),
  frequency:      text("frequency", { enum: ["daily", "weekly", "monthly"] }).notNull().default("daily"),
  hour:           integer("hour").notNull().default(3),
  minute:         integer("minute").notNull().default(0),
  weekDay:        integer("week_day").notNull().default(0),
  monthDay:       integer("month_day").notNull().default(1),
  keepCount:      integer("keep_count").notNull().default(7),
  format:         text("format", { enum: ["rsc", "backup"] }).notNull().default("rsc"),
  serverIdsJson:  text("server_ids_json").notNull().default("[]"),
  lastRunAt:      text("last_run_at"),
  lastDurationMs: integer("last_duration_ms"),
  lastError:      text("last_error"),
  updatedAt:      text("updated_at").notNull().default(sql`(datetime('now'))`),
})

export const backupEntries = sqliteTable("backup_entries", {
  id:         text("id").primaryKey(),
  serverId:   text("server_id").notNull(),
  serverName: text("server_name").notNull(),
  filename:   text("filename").notNull(),
  sizeBytes:  integer("size_bytes").notNull(),
  kind:       text("kind", { enum: ["manual", "auto"] }).notNull().default("manual"),
  notes:      text("notes"),
  createdAt:  text("created_at").notNull(),
})

/** Группы правил: ANY = хотя бы одно; ALL = все одновременно в окне тика. */
export const alertGroups = sqliteTable("alert_groups", {
  id:                text("id").primaryKey(),
  name:              text("name").notNull(),
  combineMode:       text("combine_mode", { enum: ["any", "all"] }).notNull().default("any"),
  enabled:           integer("enabled", { mode: "boolean" }).notNull().default(true),
  /** Переопределение cooldown для агрегата группы; NULL — по умолчанию 5м */
  cooldownOverride:  text("cooldown_override", {
    enum: ["1м", "5м", "15м", "1ч", "4ч", "24ч"],
  }),
  createdAt:         text("created_at").notNull().default(sql`(datetime('now'))`),
  updatedAt:         text("updated_at").notNull().default(sql`(datetime('now'))`),
})

/** Правила оповещений (UI /alerts). */
export const alertRules = sqliteTable("alert_rules", {
  id:         text("id").primaryKey(),
  name:       text("name").notNull(),
  type:       text("type", {
    enum: ["gre-tunnel", "bgp-peer", "bgp-prefix", "gre-client", "server", "rtt", "loss", "traffic"],
  }).notNull(),
  /** Сводная подпись (первый target или join); для совместимости и поиска */
  target:     text("target").notNull(),
  condition:  text("condition").notNull(),
  severity:   text("severity", { enum: ["critical", "warning", "info"] }).notNull(),
  enabled:    integer("enabled", { mode: "boolean" }).notNull().default(true),
  cooldown:   text("cooldown", {
    enum: ["1м", "5м", "15м", "1ч", "4ч", "24ч"],
  }).notNull().default("5м"),
  chatId:     text("rule_chat_id").notNull().default(""),
  /** NULL / 0 — выкл. Секунды: уведомление только если условие держится столько времени без «отмены» (восстановление сигнала). */
  confirmStabilitySec: integer("confirm_stability_sec"),
  /** Политика recovery-уведомления для правила. */
  recoveryMode: text("recovery_mode", {
    enum: ["always", "never", "conditional"],
  }).notNull().default("always"),
  /** Доп. задержка подтверждения для recovery при `conditional`; NULL — без доп. ожидания. */
  recoveryStabilitySec: integer("recovery_stability_sec"),
  /** NULL — правило вне группы (отдельные отправки по cooldown правила) */
  groupId:    text("group_id"),
  createdAt:  text("created_at").notNull().default(sql`(datetime('now'))`),
  updatedAt:  text("updated_at").notNull().default(sql`(datetime('now'))`),
})

/** Несколько объектов на правило; срабатывание по OR (хотя бы один). */
export const alertRuleTargets = sqliteTable("alert_rule_targets", {
  id:        text("id").primaryKey(),
  ruleId:    text("rule_id").notNull(),
  target:    text("target").notNull(),
  sortIndex: integer("sort_index").notNull().default(0),
})

/** Несколько условий на правило; срабатывание по OR (любое из выбранных). */
export const alertRuleConditions = sqliteTable("alert_rule_conditions", {
  id:             text("id").primaryKey(),
  ruleId:         text("rule_id").notNull(),
  conditionLine:  text("condition_line").notNull(),
  sortIndex:      integer("sort_index").notNull().default(0),
})

/** Состояние движка: cooldown / дедуп по ключу rule:id или group:id */
export const alertEngineState = sqliteTable("alert_engine_state", {
  scopeKey:        text("scope_key").primaryKey(),
  lastFiredAt:     text("last_fired_at").notNull().default(""),
  lastPayloadHash: text("last_payload_hash"),
})

/** Снимок прошлого тика для live-сигналов (GRE/BGP): переходы «offline» / «восстановился». */
export const alertEnginePrevLive = sqliteTable("alert_engine_prev_live", {
  kind:         text("kind", { enum: ["gre", "bgp"] }).primaryKey(),
  payloadJson:  text("payload_json").notNull().default("{}"),
  updatedAt:    text("updated_at").notNull().default(sql`(datetime('now'))`),
})

/** Ожидание стабильности срабатывания по правилу (антидребезг). */
export const alertEngineConfirmPending = sqliteTable("alert_engine_confirm_pending", {
  ruleId:      text("rule_id").primaryKey(),
  payloadHash: text("payload_hash").notNull(),
  sinceAt:     text("since_at").notNull(),
})

/** Задел: несколько получателей Telegram (пока UI не подключён) */
export const alertDestinations = sqliteTable("alert_destinations", {
  id:               text("id").primaryKey(),
  kind:             text("kind", { enum: ["telegram"] }).notNull().default("telegram"),
  label:            text("label").notNull().default(""),
  telegramChatId:   text("telegram_chat_id").notNull().default(""),
  messageThreadId:  integer("message_thread_id"),
  enabled:          integer("enabled", { mode: "boolean" }).notNull().default(true),
  createdAt:        text("created_at").notNull().default(sql`(datetime('now'))`),
})

/** Журнал отправок / срабатываний (персист + при необходимости пополняется воркером). */
export const alertHistory = sqliteTable("alert_history", {
  id:        text("id").primaryKey(),
  ruleId:    text("rule_id"),
  groupId:   text("group_id"),
  ruleName:  text("rule_name").notNull(),
  severity:  text("severity", { enum: ["critical", "warning", "info"] }).notNull(),
  message:   text("message").notNull(),
  sentOk:    integer("sent_ok", { mode: "boolean" }).notNull().default(true),
  firedAt:   text("fired_at").notNull(),
})

/** Outbox доставки уведомлений: ретраи и идемпотентность отправки каналов. */
export const alertOutbox = sqliteTable("alert_outbox", {
  id:           text("id").primaryKey(),
  dedupeKey:    text("dedupe_key").notNull(),
  channel:      text("channel", { enum: ["telegram"] }).notNull().default("telegram"),
  status:       text("status", { enum: ["pending", "sent", "failed"] }).notNull().default("pending"),
  retryCount:   integer("retry_count").notNull().default(0),
  maxRetries:   integer("max_retries").notNull().default(3),
  nextAttemptAt:text("next_attempt_at").notNull(),
  payloadJson:  text("payload_json").notNull(),
  createdAt:    text("created_at").notNull().default(sql`(datetime('now'))`),
  sentAt:       text("sent_at"),
  lastError:    text("last_error"),
})

/** Cursor движка: watermark последней обработанной точки snapshot-источников. */
export const alertEngineCursor = sqliteTable("alert_engine_cursor", {
  id:                integer("id").primaryKey(),
  lastSourceFinishedAt: text("last_source_finished_at"),
  updatedAt:         text("updated_at").notNull().default(sql`(datetime('now'))`),
})

/** Журнал прогонов планировщика (аудит). */
export const schedulerRuns = sqliteTable("scheduler_runs", {
  id:          text("id").primaryKey(),
  jobKey:      text("job_key").notNull(),
  startedAt:   text("started_at").notNull(),
  finishedAt:  text("finished_at").notNull(),
  status:      text("status", { enum: ["ok", "error"] }).notNull(),
  error:       text("error"),
  durationMs:  integer("duration_ms").notNull().default(0),
  /** JSON: см. `SchedulerRunSnapshot` в types/scheduler-run-snapshot.ts */
  resultJson:  text("result_json"),
})

/** Централизованный append-only журнал событий системы. */
export const events = sqliteTable("events", {
  id:           text("id").primaryKey(),
  createdAt:    text("created_at").notNull(),
  level:        text("level", { enum: ["critical", "warning", "info"] }).notNull(),
  eventType:    text("event_type").notNull(),
  sourceModule: text("source_module").notNull(),
  title:        text("title").notNull(),
  message:      text("message").notNull(),
  entityType:   text("entity_type"),
  entityId:     text("entity_id"),
  payloadJson:  text("payload_json"),
})

export const uptimeSpeedTestRuns = sqliteTable("uptime_speed_test_runs", {
  id:                 text("id").primaryKey(),
  probeId:            text("probe_id"),
  srcServerId:        integer("src_server_id").notNull().references(() => servers.id, { onDelete: "cascade" }),
  dstServerId:        integer("dst_server_id").notNull().references(() => servers.id, { onDelete: "cascade" }),
  srcInterface:       text("src_interface").notNull().default(""),
  dstInterface:       text("dst_interface").notNull().default(""),
  srcAddress:         text("src_address"),
  dstAddress:         text("dst_address"),
  srcInterfaceAddress:text("src_interface_address"),
  dstInterfaceAddress:text("dst_interface_address"),
  protocol:           text("protocol", { enum: ["tcp", "udp"] }).notNull().default("tcp"),
  direction:          text("direction", { enum: ["transmit", "receive", "both"] }).notNull().default("both"),
  durationSec:        integer("duration_sec").notNull().default(10),
  txAvgMbps:          real("tx_avg_mbps"),
  rxAvgMbps:          real("rx_avg_mbps"),
  pingRttMs:          integer("ping_rtt_ms"),
  pingLossPct:        integer("ping_loss_pct"),
  pingError:          text("ping_error"),
  status:             text("status", { enum: ["done", "error"] }).notNull().default("done"),
  error:              text("error"),
  createdAt:          text("created_at").notNull().default(sql`(datetime('now'))`),
})

export const internetPathSettings = sqliteTable("internet_path_settings", {
  id:             integer("id").primaryKey(),
  enabled:        integer("enabled", { mode: "boolean" }).notNull().default(true),
  intervalSec:    integer("interval_sec").notNull().default(300),
  retentionDays:  integer("retention_days").notNull().default(14),
  lastCollectedAt: text("last_collected_at"),
  lastDurationMs: integer("last_duration_ms"),
  lastError:      text("last_error"),
  createdAt:      text("created_at").notNull().default(sql`(datetime('now'))`),
  updatedAt:      text("updated_at").notNull().default(sql`(datetime('now'))`),
})

// ── app users (local catalog, not portal JWT) ────────────────────────────────

export const appUsers = sqliteTable("app_users", {
  id:          text("id").primaryKey(),
  name:        text("name").notNull().default(""),
  login:       text("login").notNull().unique(),
  email:       text("email").notNull().default(""),
  role:        text("role", { enum: ["admin", "operator", "viewer"] }).notNull().default("viewer"),
  active:      integer("active", { mode: "boolean" }).notNull().default(true),
  avatar:      text("avatar").notNull().default(""),
  lastSeen:    text("last_seen"),
  sectionsJson: text("sections_json").notNull().default("[]"),
  serversJson:  text("servers_json").notNull().default("[]"),
  createdAt:   text("created_at").notNull().default(sql`(datetime('now'))`),
  updatedAt:   text("updated_at").notNull().default(sql`(datetime('now'))`),
})

export const userInterfaceBindings = sqliteTable("user_interface_bindings", {
  id:             text("id").primaryKey(),
  userId:         text("user_id")
                    .notNull()
                    .references(() => appUsers.id, { onDelete: "cascade" }),
  serverId:       integer("server_id")
                    .notNull()
                    .references(() => servers.id, { onDelete: "cascade" }),
  interfaceName:  text("interface_name").notNull(),
  interfaceType:  text("interface_type", { enum: ["ether", "gre", "wg", "other"] })
                    .notNull()
                    .default("other"),
  peerPublicKey:  text("peer_public_key").notNull().default(""),
  peerName:       text("peer_name").notNull().default(""),
  comment:        text("comment").notNull().default(""),
  createdAt:      text("created_at").notNull().default(sql`(datetime('now'))`),
  updatedAt:      text("updated_at").notNull().default(sql`(datetime('now'))`),
}, (t) => [
  uniqueIndex("idx_user_iface_bind_server_name_peer").on(t.serverId, t.interfaceName, t.peerPublicKey),
])

export const internetPathSnapshots = sqliteTable("internet_path_snapshots", {
  id:          integer("id").primaryKey({ autoIncrement: true }),
  sampledAt:   text("sampled_at").notNull(),
  payloadJson: text("payload_json").notNull(),
})

// ── inferred types ─────────────────────────────────────────────────────────────

export type Server         = typeof servers.$inferSelect
export type ServerInsert   = typeof servers.$inferInsert
export type Snapshot       = typeof serverSnapshots.$inferSelect
export type SnapshotInsert = typeof serverSnapshots.$inferInsert
export type FilterRuleRow  = typeof filterRules.$inferSelect
export type RecursiveRouteRow = typeof recursiveRoutes.$inferSelect
export type TrafficSettingsRow = typeof trafficSettings.$inferSelect
export type TrafficFlowSettingsRow = typeof trafficFlowSettings.$inferSelect
export type FlowBucketRow = typeof flowBuckets.$inferSelect
export type FlowIpMetaRow = typeof flowIpMeta.$inferSelect
export type FlowAsnMetaRow = typeof flowAsnMeta.$inferSelect
export type ServersApiPingSettingsRow = typeof serversApiPingSettings.$inferSelect
export type TrafficSampleRow = typeof trafficSamples.$inferSelect
export type UptimeSettingsRow = typeof uptimeSettings.$inferSelect
export type UptimeProbeRow = typeof uptimeProbes.$inferSelect
export type UptimeProbeSampleRow = typeof uptimeProbeSamples.$inferSelect
export type UptimeResourceSampleRow = typeof uptimeResourceSamples.$inferSelect
export type UptimeSpeedProbeRow = typeof uptimeSpeedProbes.$inferSelect
export type UptimeSpeedTestRunRow = typeof uptimeSpeedTestRuns.$inferSelect
export type InternetPathSettingsRow = typeof internetPathSettings.$inferSelect
export type InternetPathSnapshotRow = typeof internetPathSnapshots.$inferSelect
export type SchedulerRunRow = typeof schedulerRuns.$inferSelect
export type EventRow = typeof events.$inferSelect
export type EvobgpSettingsRow = typeof evobgpSettings.$inferSelect
export type AlertTelegramSettingsRow = typeof alertTelegramSettings.$inferSelect
export type AcmeSettingsRow = typeof acmeSettings.$inferSelect
export type CertificateIssueJobRow = typeof certificateIssueJobs.$inferSelect
export type CertificateRenewSettingsRow = typeof certificateRenewSettings.$inferSelect
export type BackupScheduleSettingsRow = typeof backupScheduleSettings.$inferSelect
export type BackupEntryRow = typeof backupEntries.$inferSelect
export type AlertGroupRow = typeof alertGroups.$inferSelect
export type AlertRuleRow = typeof alertRules.$inferSelect
export type AlertRuleTargetRow = typeof alertRuleTargets.$inferSelect
export type AlertEngineStateRow = typeof alertEngineState.$inferSelect
export type AlertDestinationRow = typeof alertDestinations.$inferSelect
export type AlertHistoryRow = typeof alertHistory.$inferSelect
export type AlertOutboxRow = typeof alertOutbox.$inferSelect
export type AlertEngineCursorRow = typeof alertEngineCursor.$inferSelect
export type AppUserRow = typeof appUsers.$inferSelect
export type UserInterfaceBindingRow = typeof userInterfaceBindings.$inferSelect
