import type { Knex as KnexType } from "knex";
import { BaseRepository } from "./base.js";
import GC from "../../../global-constants.js";
import { GetMinuteStartNowTimestampUTC } from "../../tool.js";
import type {
  MonitoringData,
  MonitoringDataInsert,
  AggregatedMonitoringData,
  TimestampStatusCount,
  TimestampStatusCountByMonitor,
} from "../../types/db.js";

/**
 * Sample types alert evaluation can see (see docs/adr/0005-alerts-evaluate-alert-visible-samples.md).
 * Exactly the types written by flows that enqueue alert evaluation: scheduler checks
 * (REALTIME/ERROR/TIMEOUT), default-status fill (DEFAULT_STATUS), and data-API pushes (MANUAL).
 * SIGNAL rows (raw heartbeat receipts) and INCIDENT/MAINTENANCE overlays stay invisible, so the
 * alert window freezes during manual overlays instead of triggering or resolving on them.
 */
const ALERT_VISIBLE_TYPES = [GC.REALTIME, GC.ERROR, GC.TIMEOUT, GC.MANUAL, GC.DEFAULT_STATUS];

/**
 * Scheduled-check sample types that count toward a monitor's Confirmation Threshold
 * (issue #712). Intentionally narrower than ALERT_VISIBLE_TYPES: MANUAL pushes
 * and DEFAULT_STATUS fill stay transparent to threshold counting.
 */
const OBSERVED_CHECK_TYPES = [GC.REALTIME, GC.TIMEOUT, GC.ERROR];

/**
 * Overlay sample types that FREEZE Confirmation Threshold counting (issue #712):
 * while one is active the count does not advance, and it acts as a hard boundary the
 * pending run cannot cross. Included in the confirmation lookback (unlike MANUAL/DEFAULT,
 * which stay transparent) so the resolver can detect the boundary.
 */
const OVERLAY_TYPES = [GC.INCIDENT, GC.MAINTENANCE];

/**
 * Repository for monitoring data operations
 */
export class MonitoringRepository extends BaseRepository {
  async insertMonitoringData(data: MonitoringDataInsert): Promise<MonitoringData | null> {
    const { monitor_tag, timestamp, status, latency, type, error_message, raw_status } = data;

    // Perform insert/update - works across PostgreSQL, MySQL, and SQLite
    await this.knex("monitoring_data")
      .insert({ monitor_tag, timestamp, status, latency, type, error_message, raw_status })
      .onConflict(["monitor_tag", "timestamp"])
      .merge({ status, latency, type, error_message, raw_status });

    // Query and return the inserted/updated record (works consistently across all databases)
    const record = await this.knex("monitoring_data")
      .where("monitor_tag", monitor_tag)
      .where("timestamp", timestamp)
      .first();

    return record as MonitoringData | null;
  }

  async getMonitoringData(monitor_tag: string, start: number, end: number): Promise<MonitoringData[]> {
    return await this.knex("monitoring_data")
      .where("monitor_tag", monitor_tag)
      .where("timestamp", ">=", start)
      .where("timestamp", "<", end)
      .orderBy("timestamp", "asc");
  }

  async getLatestMonitoringData(monitor_tag: string): Promise<MonitoringData | undefined> {
    return await this.knex("monitoring_data")
      .where("monitor_tag", monitor_tag)
      .orderBy("timestamp", "desc")
      .limit(1)
      .first();
  }

  async getLatestMonitoringDataN(monitor_tag: string, limit: number): Promise<MonitoringData[]> {
    return await this.knex("monitoring_data")
      .where("monitor_tag", monitor_tag)
      .orderBy("timestamp", "desc")
      .limit(limit);
  }

  async getMonitoringDataPaginated(
    page: number,
    limit: number,
    filter?: { monitor_tag?: string; start_time?: number; end_time?: number },
  ): Promise<MonitoringData[]> {
    let query = this.knex("monitoring_data").select("*");

    if (filter?.monitor_tag) {
      query = query.where("monitor_tag", filter.monitor_tag);
    }

    if (filter?.start_time) {
      query = query.where("timestamp", ">=", filter.start_time);
    }

    if (filter?.end_time) {
      query = query.where("timestamp", "<=", filter.end_time);
    }

    return await query
      .orderBy("timestamp", "desc")
      .limit(limit)
      .offset((page - 1) * limit);
  }

  async getMonitoringDataCount(filter?: {
    monitor_tag?: string;
    start_time?: number;
    end_time?: number;
  }): Promise<{ count: number }> {
    let query = this.knex("monitoring_data").count("* as count");

    if (filter?.monitor_tag) {
      query = query.where("monitor_tag", filter.monitor_tag);
    }

    if (filter?.start_time) {
      query = query.where("timestamp", ">=", filter.start_time);
    }

    if (filter?.end_time) {
      query = query.where("timestamp", "<=", filter.end_time);
    }

    const result = await query.first();
    return { count: Number(result?.count) || 0 };
  }

  async getMonitoringDataAt(monitor_tag: string, timestamp: number): Promise<MonitoringData | undefined> {
    return await this.knex("monitoring_data")
      .where("monitor_tag", monitor_tag)
      .where("timestamp", timestamp)
      .orderBy("timestamp", "desc")
      .limit(1)
      .first();
  }

  async getLatestMonitoringDataAllActive(monitor_tags: string[]): Promise<MonitoringData[]> {
    if (!monitor_tags || monitor_tags.length === 0) {
      return [];
    }

    const latestPerMonitor = this.knex("monitoring_data")
      .select("monitor_tag")
      .max("timestamp as max_timestamp")
      .whereIn("monitor_tag", monitor_tags)
      .groupBy("monitor_tag")
      .as("latest_per_monitor");

    return await this.knex("monitoring_data as md")
      .join(latestPerMonitor, function (this: KnexType.JoinClause) {
        this.on("md.monitor_tag", "=", "latest_per_monitor.monitor_tag").andOn(
          "md.timestamp",
          "=",
          "latest_per_monitor.max_timestamp",
        );
      })
      .select("md.*");
  }

  async getLastHeartbeat(monitor_tag: string): Promise<MonitoringData | undefined> {
    return await this.knex("monitoring_data")
      .where("monitor_tag", monitor_tag)
      .where("type", GC.SIGNAL)
      .orderBy("timestamp", "desc")
      .limit(1)
      .first();
  }

  async getAggregatedMonitoringData(
    monitor_tag: string,
    start: number,
    end: number,
  ): Promise<AggregatedMonitoringData | undefined> {
    return await this.knex("monitoring_data")
      .select(
        this.knex.raw("COUNT(CASE WHEN status = 'DEGRADED' THEN 1 END) as DEGRADED"),
        this.knex.raw("COUNT(CASE WHEN status = 'UP' THEN 1 END) as UP"),
        this.knex.raw("COUNT(CASE WHEN status = 'DOWN' THEN 1 END) as DOWN"),
        this.knex.raw("AVG(latency) as avg_latency"),
        this.knex.raw("MAX(latency) as max_latency"),
        this.knex.raw("MIN(latency) as min_latency"),
      )
      .where("monitor_tag", monitor_tag)
      .where("timestamp", ">=", start)
      .where("timestamp", "<=", end)
      .first();
  }

  async getLastStatusBefore(monitor_tag: string, timestamp: number): Promise<MonitoringData | undefined> {
    return await this.knex("monitoring_data")
      .where("monitor_tag", monitor_tag)
      .where("timestamp", "<", timestamp)
      .orderBy("timestamp", "desc")
      .limit(1)
      .first();
  }

  async getLastStatusBeforeAll(monitor_tags: string[], timestamp: number): Promise<MonitoringData | undefined> {
    return await this.knex("monitoring_data")
      .whereIn("monitor_tag", monitor_tags)
      .where("timestamp", "<", timestamp)
      .orderBy("timestamp", "desc")
      .limit(1)
      .first();
  }

  async getDataGroupByDayAlternative(
    monitor_tag: string,
    start: number,
    end: number,
  ): Promise<Array<{ timestamp: number; status: string; latency: number }>> {
    return await this.knex("monitoring_data")
      .select("timestamp", "status", "latency")
      .where("monitor_tag", monitor_tag)
      .andWhere("timestamp", ">=", start)
      .andWhere("timestamp", "<=", end)
      .orderBy("timestamp", "asc");
  }

  async getLastStatusBeforeCombined(
    monitor_tags_arr: string[],
    timestamp: number,
    minTimestamp: number | null,
  ): Promise<{ timestamp: number; total_entries: number; latency: number; status: string } | undefined> {
    let query = this.knex("monitoring_data")
      .select(
        "timestamp",
        this.knex.raw("COUNT(*) as total_entries"),
        this.knex.raw("AVG(latency) as latency"),
        this.knex.raw(`
          CASE 
          WHEN SUM(CASE WHEN status = 'DOWN' THEN 1 ELSE 0 END) > 0 THEN 'DOWN'
          WHEN SUM(CASE WHEN status = 'DEGRADED' THEN 1 ELSE 0 END) > 0 THEN 'DEGRADED'
          ELSE 'UP'
          END as status
        `),
      )
      .whereIn("monitor_tag", monitor_tags_arr);

    if (!!minTimestamp) {
      query = query.whereBetween("timestamp", [minTimestamp, timestamp]);
    } else {
      query = query.where("timestamp", "=", timestamp);
    }

    return await query
      .groupBy("timestamp")
      .havingRaw("COUNT(*) = ?", [monitor_tags_arr.length])
      .orderBy("timestamp", "desc")
      .limit(1)
      .first();
  }

  async background(retentionDays: number = 100): Promise<number> {
    const safeRetentionDays = Math.max(1, Math.floor(retentionDays || 100));
    const cutoffTimestamp = GetMinuteStartNowTimestampUTC() - 86400 * safeRetentionDays;
    return await this.knex("monitoring_data").where("timestamp", "<", cutoffTimestamp).del();
  }

  async consecutivelyStatusFor(monitor_tag: string, status: string, lastX: number): Promise<boolean> {
    const result = await this.knex
      .with("last_records", (qb: KnexType.QueryBuilder) => {
        qb.select("*")
          .from("monitoring_data")
          .where("monitor_tag", monitor_tag)
          .whereIn("type", ALERT_VISIBLE_TYPES)
          .orderBy("timestamp", "desc")
          .limit(lastX);
      })
      .select(
        this.knex.raw(
          "CASE WHEN COUNT(*) <= SUM(CASE WHEN status = ? THEN 1 ELSE 0 END) THEN 1 ELSE 0 END as is_affected",
          [status],
        ),
      )
      .from("last_records")
      .first();

    return result.is_affected === 1;
  }

  async consecutivelyLatencyGreaterThan(
    monitor_tag: string,
    latencyThreshold: number,
    lastX: number,
  ): Promise<boolean> {
    const result = await this.knex
      .with("last_records", (qb: KnexType.QueryBuilder) => {
        qb.select("*")
          .from("monitoring_data")
          .where("monitor_tag", monitor_tag)
          .whereIn("type", ALERT_VISIBLE_TYPES)
          .orderBy("timestamp", "desc")
          .limit(lastX);
      })
      .select(
        this.knex.raw(
          "CASE WHEN COUNT(*) <= SUM(CASE WHEN latency > ? THEN 1 ELSE 0 END) THEN 1 ELSE 0 END as is_affected",
          [latencyThreshold],
        ),
      )
      .from("last_records")
      .first();

    return result.is_affected === 1;
  }

  async consecutivelyLatencyLessThan(monitor_tag: string, latencyThreshold: number, lastX: number): Promise<boolean> {
    const result = await this.knex
      .with("last_records", (qb: KnexType.QueryBuilder) => {
        qb.select("*")
          .from("monitoring_data")
          .where("monitor_tag", monitor_tag)
          .whereIn("type", ALERT_VISIBLE_TYPES)
          .orderBy("timestamp", "desc")
          .limit(lastX);
      })
      .select(
        this.knex.raw(
          "CASE WHEN COUNT(*) <= SUM(CASE WHEN latency < ? THEN 1 ELSE 0 END) THEN 1 ELSE 0 END as is_recovered",
          [latencyThreshold],
        ),
      )
      .from("last_records")
      .first();

    return result.is_recovered === 1;
  }

  /**
   * Recent samples the Confirmation Threshold resolver needs, newest first: scheduled-check
   * observations (REALTIME/TIMEOUT/ERROR) plus incident/maintenance overlays. MANUAL pushes
   * and DEFAULT fill are excluded — they stay transparent to the counter. Returns `type` so
   * the resolver can stop at overlay rows (freeze). Observations whose status is NO_DATA are
   * excluded entirely (neutral — they neither advance nor reset the count and must not consume lookback slots).
   */
  async getRecentSamplesForConfirmation(
    monitor_tag: string,
    beforeTs: number,
    limit: number,
  ): Promise<Array<{ timestamp: number; status: string | null; raw_status: string | null; type: string | null }>> {
    return await this.knex("monitoring_data")
      .select("timestamp", "status", "raw_status", "type")
      .where("monitor_tag", monitor_tag)
      .where("timestamp", "<", beforeTs)
      .whereIn("type", [...OBSERVED_CHECK_TYPES, ...OVERLAY_TYPES])
      .whereNot("status", GC.NO_DATA)
      .orderBy("timestamp", "desc")
      .limit(limit);
  }

  /**
   * The committed status of the most recent real scheduled-check observation before `beforeTs`
   * — the Confirmation Threshold "anchor" (the side currently shown). Looks past overlays,
   * MANUAL/DEFAULT, and NO_DATA so a long incident/maintenance window can never hide the anchor
   * (issue #712). Returns null when there is no prior observation (cold start).
   */
  async getLastObservedStatus(monitor_tag: string, beforeTs: number): Promise<string | null> {
    const row = await this.knex("monitoring_data")
      .select("status")
      .where("monitor_tag", monitor_tag)
      .where("timestamp", "<", beforeTs)
      .whereIn("type", OBSERVED_CHECK_TYPES)
      .whereNot("status", GC.NO_DATA)
      .orderBy("timestamp", "desc")
      .limit(1)
      .first();
    return row ? (row.status ?? null) : null;
  }

  /**
   * Backfill a confirmed status flip: set each row's committed status to its observed raw_status.
   * `confirmThreshold` is the number of consecutive checks that confirmed the flip — when it is a
   * number the run resolved to an unhealthy side and a per-row note ("Down"/"Degraded confirmed
   * after N consecutive checks", matching each row's own severity) is appended to the existing
   * error text; when it is null the run resolved to UP (recovery) and the error text is cleared.
   */
  async backfillConfirmedStatus(
    monitor_tag: string,
    timestamps: number[],
    confirmThreshold: number | null,
  ): Promise<number> {
    if (timestamps.length === 0) return 0;

    // Recovery (confirmed UP): rows become the UP side — clear any held error text in one update.
    if (confirmThreshold === null) {
      return await this.knex("monitoring_data")
        .where("monitor_tag", monitor_tag)
        .whereIn("timestamp", timestamps)
        .whereNotNull("raw_status")
        .update({
          status: this.knex.ref("raw_status"),
          error_message: null,
        });
    }

    // Confirmed unhealthy: set each row's status from its observed raw_status and APPEND a
    // severity-matched confirmation note to the existing error text (preserving the observed
    // failure reason). Done per-row for portable string concatenation (|| vs CONCAT differ across
    // SQLite/PG/MySQL), per-row severity wording, and idempotency if the backfill is replayed.
    // The whole read+update window runs in one transaction — a confirmation flip is one logical
    // write, so it must not leave the window half-confirmed/half-held if a row update fails.
    return await this.knex.transaction(async (trx: KnexType.Transaction) => {
      const rows = await trx("monitoring_data")
        .select("timestamp", "error_message", "raw_status")
        .where("monitor_tag", monitor_tag)
        .whereIn("timestamp", timestamps)
        .whereNotNull("raw_status");

      let updated = 0;
      for (const row of rows) {
        const severity = row.raw_status === GC.DEGRADED ? "Degraded" : "Down";
        const note = `${severity} confirmed after ${confirmThreshold} consecutive checks`;
        const existing: string | null = row.error_message;
        let nextMessage: string;
        if (!existing) {
          nextMessage = note;
        } else if (existing.indexOf(note) !== -1) {
          nextMessage = existing; // already appended — keep idempotent
        } else {
          nextMessage = `${existing} | ${note}`;
        }
        updated += await trx("monitoring_data")
          .where({ monitor_tag, timestamp: row.timestamp })
          .update({ status: row.raw_status, error_message: nextMessage });
      }
      return updated;
    });
  }

  async updateMonitoringData(
    monitor_tag: string,
    start: number,
    end: number,
    newStatus: string,
    type: string,
    latency: number = 0,
    deviation: number = 0,
  ): Promise<unknown[]> {
    const count = Math.floor((end - start) / 60) + 1;
    const timestamps = Array.from({ length: count }, (_, i) => start + i * 60);

    // Generate random latency as latency ± deviation (never below 0)
    const generateLatency = () => {
      if (deviation === 0) return latency;
      const randomOffset = Math.floor(Math.random() * (deviation * 2 + 1)) - deviation;
      return Math.max(0, latency + randomOffset);
    };

    const records = timestamps.map((ts) => ({
      monitor_tag,
      timestamp: ts,
      status: newStatus,
      type,
      latency: generateLatency(),
    }));

    const batchSize = 500;

    return await this.knex.transaction(async (trx: KnexType.Transaction) => {
      const results: unknown[] = [];

      for (let i = 0; i < records.length; i += batchSize) {
        const batch = records.slice(i, i + batchSize);
        // Use raw insert with ON CONFLICT to update all fields including latency
        const result = await trx("monitoring_data")
          .insert(batch)
          .onConflict(["monitor_tag", "timestamp"])
          .merge(["status", "type", "latency"]);
        results.push(result);
      }

      return results;
    });
  }

  async deleteMonitorDataByTag(tag?: string, start?: number, end?: number): Promise<number> {
    const query = this.knex("monitoring_data");
    if (tag) {
      query.where("monitor_tag", tag);
    }
    if (start !== undefined) {
      query.where("timestamp", ">=", start);
    }
    if (end !== undefined) {
      query.where("timestamp", "<=", end);
    }
    return await query.del();
  }

  /**
   * Get aggregated status counts grouped by timestamp intervals
   * @param monitorTag - The monitor tag(s) to query (single string or array of strings)
   * @param startTimestamp - The starting timestamp (UTC seconds)
   * @param intervalInSeconds - The interval size in seconds (e.g., 86400 for 1 day)
   * @param numberOfPoints - Number of intervals/points to return
   * @returns Array of { ts, countOfUp, countOfDown, countOfDegraded }
   */
  async getStatusCountsByInterval(
    monitorTag: string | string[],
    startTimestamp: number,
    intervalInSeconds: number,
    numberOfPoints: number,
  ): Promise<Array<TimestampStatusCount>> {
    const endTimestamp = startTimestamp + numberOfPoints * intervalInSeconds;

    // Determine database client to use appropriate timestamp arithmetic
    // SQLite uses CAST(... as INT), others (PG, MySQL) use FLOOR()
    const client = (this.knex.client as any).config.client;
    const isSQLite = client === "better-sqlite3" || client === "sqlite3";

    let tsExpression = "";
    if (isSQLite) {
      tsExpression = `CAST((timestamp - ?) / ? AS INT) * ? + ?`;
    } else {
      tsExpression = `FLOOR((timestamp - ?) / ?) * ? + ?`;
    }

    // Handle single tag or array of tags
    const isArray = Array.isArray(monitorTag);
    const tagClause = isArray ? `monitor_tag IN (${monitorTag.map(() => "?").join(", ")})` : `monitor_tag = ?`;
    // Use snake_case aliases for cross-database compatibility (PostgreSQL lowercases unquoted identifiers)
    const sql = `
      SELECT 
        ${tsExpression} as ts,
        SUM(CASE WHEN status = 'UP' THEN 1 ELSE 0 END) AS count_of_up,
        SUM(CASE WHEN status = 'DOWN' THEN 1 ELSE 0 END) AS count_of_down,
        SUM(CASE WHEN status = 'DEGRADED' THEN 1 ELSE 0 END) AS count_of_degraded,
        SUM(CASE WHEN status = 'MAINTENANCE' THEN 1 ELSE 0 END) AS count_of_maintenance,
        AVG(latency) AS avg_latency,
				MAX(latency) AS max_latency,
				MIN(latency) AS min_latency
      FROM monitoring_data
      WHERE ${tagClause} AND timestamp >= ? AND timestamp < ?
      GROUP BY ts
      ORDER BY ts ASC
    `;

    // Bindings:
    // 1-4: tsExpression parameters (start, interval, interval, start)
    // 5+: WHERE clause parameters (tag(s), start, end)
    const bindings = [
      startTimestamp,
      intervalInSeconds,
      intervalInSeconds,
      startTimestamp,
      ...(isArray ? monitorTag : [monitorTag]),
      startTimestamp,
      endTimestamp,
    ];

    const result = await this.knex.raw(sql, bindings);

    // Handle different database drivers:
    // - SQLite (better-sqlite3): returns array directly
    // - PostgreSQL: returns { rows: [...] }
    // - MySQL: returns [rows, fields] where rows is an array
    let rows: any[];
    if (Array.isArray(result)) {
      // SQLite or MySQL (MySQL returns [rows, fields])
      rows = Array.isArray(result[0]) ? result[0] : result;
    } else {
      // PostgreSQL
      rows = result.rows || [];
    }

    return rows.map((row: any) => ({
      ts: Number(row.ts),
      countOfUp: Number(row.count_of_up) || 0,
      countOfDown: Number(row.count_of_down) || 0,
      countOfDegraded: Number(row.count_of_degraded) || 0,
      countOfMaintenance: Number(row.count_of_maintenance) || 0,
      avgLatency: Number(row.avg_latency) || 0,
      maxLatency: Number(row.max_latency) || 0,
      minLatency: Number(row.min_latency) || 0,
    }));
  }

  /**
   * Get aggregated status counts grouped by monitor_tag and timestamp intervals
   * @param monitorTags - Monitor tags to query
   * @param startTimestamp - The starting timestamp (UTC seconds)
   * @param intervalInSeconds - The interval size in seconds (e.g., 86400 for 1 day)
   * @param numberOfPoints - Number of intervals/points to return
   */
  async getStatusCountsByIntervalGroupedByMonitor(
    monitorTags: string[],
    startTimestamp: number,
    intervalInSeconds: number,
    numberOfPoints: number,
  ): Promise<Array<TimestampStatusCountByMonitor>> {
    if (!monitorTags || monitorTags.length === 0) {
      return [];
    }

    const endTimestamp = startTimestamp + numberOfPoints * intervalInSeconds;

    const client = (this.knex.client as any).config.client;
    const isSQLite = client === "better-sqlite3" || client === "sqlite3";

    const tsExpression = isSQLite ? `CAST((timestamp - ?) / ? AS INT) * ? + ?` : `FLOOR((timestamp - ?) / ?) * ? + ?`;

    const sql = `
      SELECT
        monitor_tag,
        ${tsExpression} as ts,
        SUM(CASE WHEN status = 'UP' THEN 1 ELSE 0 END) AS count_of_up,
        SUM(CASE WHEN status = 'DOWN' THEN 1 ELSE 0 END) AS count_of_down,
        SUM(CASE WHEN status = 'DEGRADED' THEN 1 ELSE 0 END) AS count_of_degraded,
        SUM(CASE WHEN status = 'MAINTENANCE' THEN 1 ELSE 0 END) AS count_of_maintenance,
        AVG(latency) AS avg_latency,
        MAX(latency) AS max_latency,
        MIN(latency) AS min_latency
      FROM monitoring_data
      WHERE monitor_tag IN (${monitorTags.map(() => "?").join(", ")}) AND timestamp >= ? AND timestamp < ?
      GROUP BY monitor_tag, ts
      ORDER BY monitor_tag ASC, ts ASC
    `;

    const bindings = [
      startTimestamp,
      intervalInSeconds,
      intervalInSeconds,
      startTimestamp,
      ...monitorTags,
      startTimestamp,
      endTimestamp,
    ];

    const result = await this.knex.raw(sql, bindings);

    let rows: any[];
    if (Array.isArray(result)) {
      rows = Array.isArray(result[0]) ? result[0] : result;
    } else {
      rows = result.rows || [];
    }

    return rows.map((row: any) => ({
      monitor_tag: row.monitor_tag,
      ts: Number(row.ts),
      countOfUp: Number(row.count_of_up) || 0,
      countOfDown: Number(row.count_of_down) || 0,
      countOfDegraded: Number(row.count_of_degraded) || 0,
      countOfMaintenance: Number(row.count_of_maintenance) || 0,
      avgLatency: Number(row.avg_latency) || 0,
      maxLatency: Number(row.max_latency) || 0,
      minLatency: Number(row.min_latency) || 0,
    }));
  }

  /**
   * Get aggregated status counts and average latency for the last N rows
   * @param monitorTag - The monitor tag(s) to query (single string or array of strings)
   * @param lastX - Number of most recent rows to include
   * @returns Object with ts=0, counts of each status, and average latency
   */
  async getStatusCountsForLastN(monitorTag: string | string[], lastX: number): Promise<TimestampStatusCount> {
    const tags = Array.isArray(monitorTag) ? monitorTag : [monitorTag];

    const result = await this.knex
      .with("last_records", (qb: KnexType.QueryBuilder) => {
        qb.select("status", "latency")
          .from("monitoring_data")
          .whereIn("monitor_tag", tags)
          .orderBy("timestamp", "desc")
          .limit(lastX);
      })
      .select(
        this.knex.raw("SUM(CASE WHEN status = 'UP' THEN 1 ELSE 0 END) AS count_of_up"),
        this.knex.raw("SUM(CASE WHEN status = 'DOWN' THEN 1 ELSE 0 END) AS count_of_down"),
        this.knex.raw("SUM(CASE WHEN status = 'DEGRADED' THEN 1 ELSE 0 END) AS count_of_degraded"),
        this.knex.raw("SUM(CASE WHEN status = 'MAINTENANCE' THEN 1 ELSE 0 END) AS count_of_maintenance"),
        this.knex.raw("AVG(latency) AS avg_latency"),
        this.knex.raw("MAX(latency) AS max_latency"),
        this.knex.raw("MIN(latency) AS min_latency"),
      )
      .from("last_records")
      .first();

    return {
      ts: 0,
      countOfUp: Number(result?.count_of_up) || 0,
      countOfDown: Number(result?.count_of_down) || 0,
      countOfDegraded: Number(result?.count_of_degraded) || 0,
      countOfMaintenance: Number(result?.count_of_maintenance) || 0,
      avgLatency: Number(result?.avg_latency) || 0,
      maxLatency: Number(result?.max_latency) || 0,
      minLatency: Number(result?.min_latency) || 0,
    };
  }

  //get the last known status for a monitor
  async getLastKnownStatus(monitor_tag: string): Promise<MonitoringData | undefined> {
    return await this.knex("monitoring_data").where("monitor_tag", monitor_tag).orderBy("timestamp", "desc").first();
  }
}
