import { asc, desc, eq, sql } from "drizzle-orm"
import { db, sqliteDatabase } from "../db/index.js"
import {
  alertGroups,
  alertHistory,
  alertRuleConditions,
  alertRuleTargets,
  alertRules,
  alertTelegramSettings,
  servers,
  uptimeProbes,
} from "../db/schema.js"

export type ApiAlertGroup = {
  id: string
  name: string
  combineMode: "any" | "all"
  enabled: boolean
  cooldownOverride: (typeof alertRules.$inferSelect)["cooldown"] | null
}

export type ApiAlertRule = {
  id: string
  name: string
  type: (typeof alertRules.$inferSelect)["type"]
  /** Сводная строка для списка / поиска */
  target: string
  /** Все объекты правила (OR) */
  targets: string[]
  groupId: string | null
  /** Сводная строка условий для списка */
  condition: string
  /** Все условия (OR); если пусто в БД — используется `condition` */
  conditions: string[]
  severity: (typeof alertRules.$inferSelect)["severity"]
  enabled: boolean
  cooldown: (typeof alertRules.$inferSelect)["cooldown"]
  /** Секунды стабильности перед отправкой; null — выключено */
  confirmStabilitySec: number | null
  /** Политика отправки recovery для этого правила. */
  recoveryMode: "always" | "never" | "conditional"
  /** Доп. задержка подтверждения recovery при `conditional`. */
  recoveryStabilitySec: number | null
  chatId: string
  lastFiredAt: string | null
}

export type ApiAlertHistoryEntry = {
  id: string
  ruleName: string
  severity: (typeof alertHistory.$inferSelect)["severity"]
  message: string
  sent: boolean
  firedAt: string
  source: "db" | "live"
}

function ensureTelegramRow() {
  let row = db.select().from(alertTelegramSettings).where(eq(alertTelegramSettings.id, 1)).limit(1).all()[0]
  if (!row) {
    db.insert(alertTelegramSettings).values({ id: 1 }).run()
    row = db.select().from(alertTelegramSettings).where(eq(alertTelegramSettings.id, 1)).limit(1).all()[0]
  }
  return row!
}

function lastFiredByRuleId(): Map<string, string> {
  const rows = sqliteDatabase
    .prepare(
      `SELECT rule_id AS ruleId, MAX(fired_at) AS mx
       FROM alert_history
       WHERE rule_id IS NOT NULL AND rule_id != ''
       GROUP BY rule_id`,
    )
    .all() as { ruleId: string; mx: string }[]
  const m = new Map<string, string>()
  for (const r of rows) {
    if (r.ruleId && r.mx) m.set(r.ruleId, r.mx)
  }
  return m
}

function targetsByRuleId(): Map<string, string[]> {
  const rows = db
    .select()
    .from(alertRuleTargets)
    .orderBy(asc(alertRuleTargets.sortIndex), asc(alertRuleTargets.id))
    .all()
  const m = new Map<string, string[]>()
  for (const t of rows) {
    const rid = t.ruleId
    const arr = m.get(rid) ?? []
    arr.push(t.target)
    m.set(rid, arr)
  }
  return m
}

function conditionsByRuleId(): Map<string, string[]> {
  const rows = db
    .select()
    .from(alertRuleConditions)
    .orderBy(asc(alertRuleConditions.sortIndex), asc(alertRuleConditions.id))
    .all()
  const m = new Map<string, string[]>()
  for (const t of rows) {
    const rid = t.ruleId
    const arr = m.get(rid) ?? []
    arr.push(t.conditionLine)
    m.set(rid, arr)
  }
  return m
}

export function listAlertGroups(): ApiAlertGroup[] {
  return db
    .select()
    .from(alertGroups)
    .orderBy(asc(alertGroups.name))
    .all()
    .map((g) => ({
      id: g.id,
      name: g.name,
      combineMode: g.combineMode,
      enabled: g.enabled,
      cooldownOverride: g.cooldownOverride ?? null,
    }))
}

export function listAlertRules(): ApiAlertRule[] {
  const lf = lastFiredByRuleId()
  const tgtMap = targetsByRuleId()
  const condMap = conditionsByRuleId()
  const rows = db.select().from(alertRules).all()
  return rows.map((r) => {
    const targets = tgtMap.get(r.id)
    const ts = targets?.length ? targets : [r.target]
    const condLines = condMap.get(r.id)
    const cs = condLines?.length ? condLines : [r.condition]
    return {
      id: r.id,
      name: r.name,
      type: r.type,
      target: r.target,
      targets: ts,
      groupId: r.groupId ?? null,
      condition: r.condition,
      conditions: cs,
      severity: r.severity,
      enabled: r.enabled,
      cooldown: r.cooldown,
      confirmStabilitySec:
        r.confirmStabilitySec != null && r.confirmStabilitySec > 0 ? r.confirmStabilitySec : null,
      recoveryMode:
        r.recoveryMode === "never" || r.recoveryMode === "conditional" ? r.recoveryMode : "always",
      recoveryStabilitySec:
        r.recoveryStabilitySec != null && r.recoveryStabilitySec > 0 ? r.recoveryStabilitySec : null,
      chatId: r.chatId ?? "",
      lastFiredAt: lf.get(r.id) ?? null,
    }
  })
}

function summarizeTargets(targets: string[]): string {
  const u = targets.map((t) => t.trim()).filter(Boolean)
  if (u.length === 0) return ""
  if (u.length <= 2) return u.join(", ")
  return `${u[0]}, ${u[1]} +${u.length - 2}`
}

function summarizeConditionLines(lines: string[]): string {
  const u = lines.map((t) => t.trim()).filter(Boolean)
  if (u.length === 0) return "—"
  if (u.length === 1) return u[0]!
  if (u.length <= 3) return u.join(" · ")
  return `${u[0]} · ${u[1]} · +${u.length - 2}`
}

export type ReplaceAlertRuleInput = {
  id: string
  name: string
  type: ApiAlertRule["type"]
  /** @deprecated используйте targets; если нет targets — берётся target */
  target?: string
  targets?: string[]
  /** Сводная строка; если не передана — из conditions */
  condition?: string
  /** Несколько условий (OR); если пусто — используется `condition` */
  conditions?: string[]
  severity: ApiAlertRule["severity"]
  enabled: boolean
  cooldown: ApiAlertRule["cooldown"]
  /** Секунды; 0 / undefined / null — без задержки подтверждения */
  confirmStabilitySec?: number | null
  recoveryMode?: "always" | "never" | "conditional"
  recoveryStabilitySec?: number | null
  chatId: string
  groupId?: string | null
}

export type ReplaceAlertGroupInput = {
  id: string
  name: string
  combineMode: "any" | "all"
  enabled: boolean
  cooldownOverride: ApiAlertRule["cooldown"] | null
}

/** Атомарно заменяет группы, правила и строки targets. */
export function replaceAlertsConfig(payload: { groups: ReplaceAlertGroupInput[]; rules: ReplaceAlertRuleInput[] }) {
  const now = sql`(datetime('now'))`
  const { groups, rules } = payload
  db.transaction((tx) => {
    tx.delete(alertRuleConditions).run()
    tx.delete(alertRuleTargets).run()
    tx.delete(alertRules).run()
    tx.delete(alertGroups).run()
    for (const g of groups) {
      tx.insert(alertGroups).values({
        id: g.id,
        name: g.name.trim() || g.id,
        combineMode: g.combineMode,
        enabled: g.enabled,
        cooldownOverride: g.cooldownOverride ?? null,
        updatedAt: now,
      }).run()
    }
    for (const r of rules) {
      const rawTargets =
        r.targets && r.targets.length > 0
          ? r.targets.map((t) => t.trim()).filter(Boolean)
          : [String(r.target ?? "").trim()].filter(Boolean)
      const summary = summarizeTargets(rawTargets.length ? rawTargets : ["—"])
      const rawConds =
        r.conditions && r.conditions.length > 0
          ? r.conditions.map((c) => c.trim()).filter(Boolean)
          : [String(r.condition ?? "").trim()].filter(Boolean)
      const condSummary = summarizeConditionLines(rawConds.length ? rawConds : ["—"])
      const stab =
        r.confirmStabilitySec != null &&
        Number.isFinite(r.confirmStabilitySec) &&
        r.confirmStabilitySec > 0
          ? Math.min(86400, Math.max(1, Math.floor(r.confirmStabilitySec)))
          : null
      const recoveryMode =
        r.recoveryMode === "never" || r.recoveryMode === "conditional" ? r.recoveryMode : "always"
      const recoveryStabilitySec =
        r.recoveryStabilitySec != null &&
        Number.isFinite(r.recoveryStabilitySec) &&
        r.recoveryStabilitySec > 0
          ? Math.min(86400, Math.max(1, Math.floor(r.recoveryStabilitySec)))
          : null
      tx.insert(alertRules).values({
        id: r.id,
        name: r.name,
        type: r.type,
        target: summary || "—",
        condition: condSummary,
        severity: r.severity,
        enabled: r.enabled,
        cooldown: r.cooldown,
        confirmStabilitySec: stab,
        recoveryMode,
        recoveryStabilitySec,
        chatId: r.chatId ?? "",
        groupId: r.groupId && r.groupId.trim() ? r.groupId.trim() : null,
        updatedAt: now,
      }).run()
      rawTargets.forEach((t, i) => {
        tx.insert(alertRuleTargets).values({
          id: `rt-${r.id}-${i}`,
          ruleId: r.id,
          target: t,
          sortIndex: i,
        }).run()
      })
      rawConds.forEach((c, i) => {
        tx.insert(alertRuleConditions).values({
          id: `rc-${r.id}-${i}`,
          ruleId: r.id,
          conditionLine: c,
          sortIndex: i,
        }).run()
      })
    }
  })
}

/** Совместимость: только правила, группы не трогаем. */
export function replaceAlertRules(rules: ReplaceAlertRuleInput[]) {
  const curGroups = listAlertGroups()
  replaceAlertsConfig({ groups: curGroups.map((g) => ({ ...g })), rules })
}

export function getTelegramSettingsRow() {
  return ensureTelegramRow()
}

export function getTelegramPublic() {
  const row = ensureTelegramRow()
  const tid = row.messageThreadId
  const messageThreadId =
    typeof tid === "number" && Number.isFinite(tid) && Math.floor(tid) >= 1 ? Math.floor(tid) : null
  return {
    chatId: row.chatId ?? "",
    tokenConfigured: Boolean(row.botToken?.trim()),
    messageThreadId,
  }
}

export function updateTelegramSettings(patch: {
  token?: string | null
  chatId?: string | undefined
  /** undefined — не менять; null — сбросить тему */
  messageThreadId?: number | null
}) {
  const cur = ensureTelegramRow()
  let nextToken = cur.botToken
  let nextChat = cur.chatId ?? ""
  let nextThread: number | null | undefined = undefined

  if (patch.token !== undefined) {
    if (patch.token === null || patch.token === "") nextToken = ""
    else nextToken = patch.token.trim()
  }
  if (patch.chatId !== undefined) nextChat = patch.chatId.trim()
  if (patch.messageThreadId !== undefined) {
    if (patch.messageThreadId === null) nextThread = null
    else {
      const n = Math.floor(patch.messageThreadId)
      nextThread = Number.isFinite(n) && n >= 1 ? n : null
    }
  }

  const base = {
    botToken: nextToken,
    chatId: nextChat,
    updatedAt: sql`(datetime('now'))`,
  }
  if (nextThread !== undefined) {
    db.update(alertTelegramSettings)
      .set({ ...base, messageThreadId: nextThread })
      .where(eq(alertTelegramSettings.id, 1))
      .run()
  } else {
    db.update(alertTelegramSettings).set(base).where(eq(alertTelegramSettings.id, 1)).run()
  }
  return getTelegramPublic()
}

/** Токен только для внутреннего вызова Telegram API (не отдаётся клиенту). */
export function getTelegramBotToken(): string {
  return ensureTelegramRow().botToken?.trim() ?? ""
}

/** Для `sendMessage`: только если в БД задана валидная тема. */
export function getTelegramMessageThreadIdForApi(): number | undefined {
  const { messageThreadId } = getTelegramPublic()
  return messageThreadId ?? undefined
}

/** Текст тестового сообщения по полям правила (как в UI предпросмотра, без записи в историю). */
export function formatAlertRuleTestTelegramText(opts: {
  name: string
  targets: string[]
  conditionLine: string
  severity: "critical" | "warning" | "info"
  cooldown: string
}): string {
  const emoji =
    opts.severity === "critical" ? "🔴" : opts.severity === "warning" ? "🟡" : "🔵"
  const obj = opts.targets.length ? opts.targets.join(", ") : "—"
  return [
    "🧪 MikroTik Manager — тестовая отправка по полям формы правила (в историю срабатываний не записывается).",
    "",
    `${emoji} ${opts.name}`,
    `Объект: ${obj}`,
    `Событие: ${opts.conditionLine}`,
    `Cooldown: ${opts.cooldown}`,
  ].join("\n")
}

/** Отправка текста в Telegram (движок алертов и тест). */
export async function sendTelegramAlertMessage(opts: {
  text: string
  /** Если задан — вместо токена из БД (ручной тест) */
  token?: string
  /** Пусто — глобальный chat из БД */
  chatId?: string
  /** Переопределение темы; undefined — как в настройках */
  messageThreadId?: number | null
}): Promise<{ ok: true } | { ok: false; error: string }> {
  const token = (opts.token?.trim() || getTelegramBotToken()).trim()
  if (!token) return { ok: false, error: "Не задан Bot Token" }
  const pub = getTelegramPublic()
  const chatId = (opts.chatId?.trim() || pub.chatId || "").trim()
  if (!chatId) return { ok: false, error: "Не задан Chat ID" }
  const threadId =
    opts.messageThreadId !== undefined && opts.messageThreadId !== null
      ? opts.messageThreadId >= 1
        ? Math.floor(opts.messageThreadId)
        : undefined
      : getTelegramMessageThreadIdForApi()
  const url = `https://api.telegram.org/bot${encodeURIComponent(token)}/sendMessage`
  try {
    const payload: { chat_id: string; text: string; message_thread_id?: number } = {
      chat_id: chatId,
      text: opts.text,
    }
    if (threadId != null) payload.message_thread_id = threadId
    const res = await fetch(url, {
      method: "POST",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify(payload),
    })
    const data = (await res.json().catch(() => ({}))) as { ok?: boolean; description?: string }
    if (!res.ok || data.ok === false) {
      return { ok: false, error: data.description ?? res.statusText ?? "Telegram API error" }
    }
    return { ok: true }
  } catch (e) {
    return { ok: false, error: e instanceof Error ? e.message : "Ошибка сети" }
  }
}

export function appendAlertHistory(entry: {
  id: string
  ruleId?: string | null
  groupId?: string | null
  ruleName: string
  severity: (typeof alertHistory.$inferSelect)["severity"]
  message: string
  sentOk: boolean
  firedAt?: string
}) {
  const firedAt = entry.firedAt ?? new Date().toISOString()
  db.insert(alertHistory)
    .values({
      id: entry.id,
      ruleId: entry.ruleId ?? null,
      groupId: entry.groupId ?? null,
      ruleName: entry.ruleName,
      severity: entry.severity,
      message: entry.message,
      sentOk: entry.sentOk,
      firedAt,
    })
    .run()
}

interface LiveResRow {
  serverName: string
  sampledAt: string
}

interface LiveProbeRow {
  probeName: string
  target: string
  sampledAt: string
  status: string
  rttMs: number | null
  lossPct: number | null
}

function loadLiveResourceIssues(): LiveResRow[] {
  return sqliteDatabase
    .prepare(
      `SELECT s.name AS serverName, urs.sampled_at AS sampledAt
       FROM uptime_resource_samples urs
       INNER JOIN (
         SELECT server_id, MAX(id) AS mid FROM uptime_resource_samples GROUP BY server_id
       ) latest ON latest.mid = urs.id
       INNER JOIN servers s ON s.id = urs.server_id
       WHERE urs.status = 'offline'`,
    )
    .all() as LiveResRow[]
}

function loadLiveProbeIssues(): LiveProbeRow[] {
  return sqliteDatabase
    .prepare(
      `SELECT p.name AS probeName, p.target AS target, ups.sampled_at AS sampledAt,
              ups.status AS status, ups.rtt_ms AS rttMs, ups.loss_pct AS lossPct
       FROM uptime_probe_samples ups
       INNER JOIN (
         SELECT probe_id, MAX(id) AS mid FROM uptime_probe_samples GROUP BY probe_id
       ) latest ON latest.mid = ups.id
       INNER JOIN uptime_probes p ON p.id = ups.probe_id
       WHERE ups.status IN ('down', 'warn')`,
    )
    .all() as LiveProbeRow[]
}

function toLiveHistoryEntries(): ApiAlertHistoryEntry[] {
  const out: ApiAlertHistoryEntry[] = []
  for (const r of loadLiveResourceIssues()) {
    const safeId = `live-res-${r.serverName}-${r.sampledAt}`.replace(/[^a-zA-Z0-9._-]+/g, "_")
    out.push({
      id: safeId,
      ruleName: "Сервер (ресурсы)",
      severity: "critical",
      message: `${r.serverName}: статус offline по данным мониторинга`,
      sent: true,
      firedAt: r.sampledAt,
      source: "live",
    })
  }
  for (const p of loadLiveProbeIssues()) {
    const sev = p.status === "down" ? "critical" : "warning"
    const loss = p.lossPct != null ? `${p.lossPct}%` : "—"
    const rtt = p.rttMs != null ? `${p.rttMs} мс` : "—"
    const safeId = `live-prb-${p.probeName}-${p.target}-${p.sampledAt}`.replace(/[^a-zA-Z0-9._-]+/g, "_")
    out.push({
      id: safeId,
      ruleName: "Ping-проба",
      severity: sev,
      message: `${p.probeName} → ${p.target}: ${p.status} (RTT ${rtt}, потери ${loss})`,
      sent: true,
      firedAt: p.sampledAt,
      source: "live",
    })
  }
  return out
}

export function listPersistedHistory(limit: number): ApiAlertHistoryEntry[] {
  const rows = db
    .select()
    .from(alertHistory)
    .orderBy(desc(alertHistory.firedAt))
    .limit(limit)
    .all()
  return rows.map((h) => ({
    id: h.id,
    ruleName: h.ruleName,
    severity: h.severity,
    message: h.message,
    sent: h.sentOk,
    firedAt: h.firedAt,
    source: "db" as const,
  }))
}

export function listMergedHistory(persistLimit = 40, liveCap = 25): ApiAlertHistoryEntry[] {
  const persisted = listPersistedHistory(persistLimit)
  const live = toLiveHistoryEntries().slice(0, liveCap)
  const merged = [...persisted, ...live].sort((a, b) => (a.firedAt < b.firedAt ? 1 : a.firedAt > b.firedAt ? -1 : 0))
  const seen = new Set<string>()
  const dedup: ApiAlertHistoryEntry[] = []
  for (const e of merged) {
    if (seen.has(e.id)) continue
    seen.add(e.id)
    dedup.push(e)
    if (dedup.length >= 50) break
  }
  return dedup
}

export function getAlertsMeta() {
  const srvRows = db
    .select({
      name: servers.name,
      host: servers.host,
      site: servers.site,
      country: servers.country,
      type: servers.type,
    })
    .from(servers)
    .all()
  const labels = srvRows.map((s) => (s.name?.trim() ? s.name.trim() : s.host))
  const serversDetail = srvRows.map((r) => ({
    name: r.name?.trim() || "",
    host: r.host?.trim() || "",
    site: r.site?.trim() || "",
    country: r.country?.trim() || "",
    type: (r.type ?? "home-router") as string,
  }))
  const probes = db.select({ name: uptimeProbes.name, target: uptimeProbes.target }).from(uptimeProbes).all()
  const probeTargets = probes.map((p) => `${p.name} → ${p.target}`)
  const defaults = ["8.8.8.8", "1.1.1.1"]
  const rttLossTargets: string[] = []
  for (const L of labels.slice(0, 12)) {
    for (const d of defaults) rttLossTargets.push(`${L} → ${d}`)
  }
  return {
    servers: labels,
    greClients: labels,
    probeTargets: probeTargets.length ? probeTargets : rttLossTargets,
    rttLossTargets: rttLossTargets.length ? rttLossTargets : labels.map((L) => `${L} → 8.8.8.8`),
    trafficServers: labels,
    serversDetail,
  }
}
