import { BaseRepository, type CountResult } from "./base.js";
import type {
  MonitorAlertConfigRecord,
  MonitorAlertConfigInsert,
  MonitorAlertConfigUpdate,
  MonitorAlertConfigFilter,
  MonitorAlertConfigTriggerRecord,
  MonitorAlertConfigTriggerInsert,
  MonitorAlertConfigWithTriggers,
  MonitorAlertConfigMonitorRecord,
  MonitorAlertConfigMonitorInsert,
  TriggerRecord,
  MonitorAlertV2Record,
  MonitorAlertV2Insert,
  MonitorAlertV2Update,
  MonitorAlertV2Filter,
  MonitorAlertStatusType,
  MonitorAlertV2WithConfig,
} from "../../types/db.js";
import { GetDbType } from "../../tool.js";

/**
 * Repository for monitor alert configuration operations
 */
export class MonitorAlertConfigRepository extends BaseRepository {
  // ============ Monitor Alert Config CRUD ============

  /**
   * Insert a new monitor alert config
   */
  async insertMonitorAlertConfig(data: MonitorAlertConfigInsert): Promise<number> {
    const dbType = GetDbType();
    const insertData = {
      monitor_tag: data.monitor_tag || null,
      alert_for: data.alert_for,
      alert_value: data.alert_value,
      failure_threshold: data.failure_threshold,
      success_threshold: data.success_threshold,
      alert_description: data.alert_description || null,
      create_incident: data.create_incident || "NO",
      is_active: data.is_active || "YES",
      severity: data.severity || "WARNING",
      created_at: this.knex.fn.now(),
      updated_at: this.knex.fn.now(),
    };

    if (dbType === "postgresql") {
      const result = await this.knex("monitor_alerts_config").insert(insertData).returning("id");
      const inserted = Array.isArray(result) ? result[0] : result;
      return typeof inserted === "object" && inserted !== null
        ? Number((inserted as { id: number }).id)
        : Number(inserted);
    }

    const result = await this.knex("monitor_alerts_config").insert(insertData);
    const inserted = Array.isArray(result) ? result[0] : result;
    return typeof inserted === "object" && inserted !== null
      ? Number((inserted as { id: number }).id)
      : Number(inserted);
  }

  /**
   * Update an existing monitor alert config by ID
   */
  async updateMonitorAlertConfig(id: number, data: MonitorAlertConfigUpdate): Promise<number> {
    const updateData: Record<string, unknown> = {
      updated_at: this.knex.fn.now(),
    };

    if (data.alert_for !== undefined) updateData.alert_for = data.alert_for;
    if (data.alert_value !== undefined) updateData.alert_value = data.alert_value;
    if (data.failure_threshold !== undefined) updateData.failure_threshold = data.failure_threshold;
    if (data.success_threshold !== undefined) updateData.success_threshold = data.success_threshold;
    if (data.alert_description !== undefined) updateData.alert_description = data.alert_description;
    if (data.create_incident !== undefined) updateData.create_incident = data.create_incident;
    if (data.is_active !== undefined) updateData.is_active = data.is_active;
    if (data.severity !== undefined) updateData.severity = data.severity;

    return await this.knex("monitor_alerts_config").where({ id }).update(updateData);
  }

  /**
   * Get a single monitor alert config by ID
   */
  async getMonitorAlertConfigById(id: number): Promise<MonitorAlertConfigRecord | undefined> {
    return await this.knex("monitor_alerts_config").where({ id }).first();
  }

  /**
   * Get monitor alert configs with optional filtering
   */
  async getMonitorAlertConfigs(filter: MonitorAlertConfigFilter): Promise<MonitorAlertConfigRecord[]> {
    let query = this.knex("monitor_alerts_config as mac").select("mac.*").whereRaw("1=1");

    if (filter.id !== undefined) {
      query = query.andWhere("mac.id", filter.id);
    }
    if (filter.monitor_tag !== undefined) {
      query = query
        .join("monitor_alerts_config_monitors as macm", "mac.id", "macm.monitor_alerts_id")
        .andWhere("macm.monitor_tag", filter.monitor_tag);
    }
    if (filter.alert_for !== undefined) {
      query = query.andWhere("mac.alert_for", filter.alert_for);
    }
    if (filter.is_active !== undefined) {
      query = query.andWhere("mac.is_active", filter.is_active);
    }

    return await query.orderBy("mac.id", "desc");
  }

  /**
   * Get all monitor alert configs for a specific monitor tag
   */
  async getMonitorAlertConfigsByMonitorTag(monitorTag: string): Promise<MonitorAlertConfigRecord[]> {
    return await this.knex("monitor_alerts_config as mac")
      .select("mac.*")
      .join("monitor_alerts_config_monitors as macm", "mac.id", "macm.monitor_alerts_id")
      .where("macm.monitor_tag", monitorTag)
      .orderBy("mac.id", "desc");
  }

  /**
   * Get all active monitor alert configs
   */
  async getActiveMonitorAlertConfigs(): Promise<MonitorAlertConfigRecord[]> {
    return await this.knex("monitor_alerts_config").where({ is_active: "YES" }).orderBy("id", "desc");
  }

  /**
   * Get all active monitor alert configs for a specific monitor
   */
  async getActiveMonitorAlertConfigsByMonitorTag(monitorTag: string): Promise<MonitorAlertConfigRecord[]> {
    return await this.knex("monitor_alerts_config as mac")
      .select("mac.*")
      .join("monitor_alerts_config_monitors as macm", "mac.id", "macm.monitor_alerts_id")
      .where("macm.monitor_tag", monitorTag)
      .andWhere("mac.is_active", "YES")
      .orderBy("mac.id", "desc");
  }

  /**
   * Delete a monitor alert config by ID, including all child rows.
   *
   * Child rows are removed explicitly even though FK cascades are declared:
   * SQLite never enforces them (foreign_keys pragma is off), so relying on
   * CASCADE orphans children on the default deployment. See
   * docs/adr/0008-explicit-deletes-over-fk-cascades.md.
   */
  async deleteMonitorAlertConfig(id: number): Promise<number> {
    await this.knex("monitor_alerts_v2").where({ config_id: id }).del();
    await this.knex("monitor_alerts_config_triggers").where({ monitor_alerts_id: id }).del();
    await this.knex("monitor_alerts_config_monitors").where({ monitor_alerts_id: id }).del();
    return await this.knex("monitor_alerts_config").where({ id }).del();
  }

  /**
   * Delete all monitor alert configs for a specific monitor tag
   */
  async deleteMonitorAlertConfigsByMonitorTag(monitorTag: string): Promise<number> {
    // Find all config IDs that have this monitor tag in the junction table
    const configIds = await this.knex("monitor_alerts_config_monitors")
      .select("monitor_alerts_id")
      .where({ monitor_tag: monitorTag });

    if (configIds.length === 0) return 0;

    // Remove the monitor from the junction table, along with its per-monitor
    // alert state — a shared config survives the detach, but its v2 rows for
    // this tag would otherwise dangle (see deleteMonitorAlertConfig on why
    // FK cascades can't be relied on)
    await this.knex("monitor_alerts_v2").where({ monitor_tag: monitorTag }).del();
    await this.knex("monitor_alerts_config_monitors").where({ monitor_tag: monitorTag }).del();

    // Delete any configs that now have zero monitors
    const ids = configIds.map((r: { monitor_alerts_id: number }) => r.monitor_alerts_id);
    let deletedCount = 0;
    for (const id of ids) {
      const remainingMonitors = await this.knex("monitor_alerts_config_monitors")
        .count("* as count")
        .where({ monitor_alerts_id: id })
        .first<CountResult>();
      if (Number(remainingMonitors?.count) === 0) {
        await this.deleteMonitorAlertConfig(id);
        deletedCount++;
      }
    }
    return deletedCount;
  }

  /**
   * Count monitor alert configs with optional filtering
   */
  async getMonitorAlertConfigsCount(filter: MonitorAlertConfigFilter): Promise<CountResult | undefined> {
    let query = this.knex("monitor_alerts_config as mac").count("* as count");

    if (filter.id !== undefined) {
      query = query.andWhere("mac.id", filter.id);
    }
    if (filter.monitor_tag !== undefined) {
      query = query
        .join("monitor_alerts_config_monitors as macm", "mac.id", "macm.monitor_alerts_id")
        .andWhere("macm.monitor_tag", filter.monitor_tag);
    }
    if (filter.alert_for !== undefined) {
      query = query.andWhere("mac.alert_for", filter.alert_for);
    }
    if (filter.is_active !== undefined) {
      query = query.andWhere("mac.is_active", filter.is_active);
    }

    return await query.first<CountResult>();
  }

  /**
   * Get paginated monitor alert configs with triggers
   */
  async getMonitorAlertConfigsPaginated(
    page: number,
    limit: number,
    filter?: MonitorAlertConfigFilter,
  ): Promise<{ configs: MonitorAlertConfigRecord[]; total: number }> {
    // Build count query
    let countQuery = this.knex("monitor_alerts_config as mac").count("* as count");
    if (filter?.monitor_tag) {
      countQuery = countQuery
        .join("monitor_alerts_config_monitors as macm", "mac.id", "macm.monitor_alerts_id")
        .where("macm.monitor_tag", filter.monitor_tag);
    }
    if (filter?.is_active) {
      countQuery = countQuery.andWhere("mac.is_active", filter.is_active);
    }
    if (filter?.alert_for) {
      countQuery = countQuery.andWhere("mac.alert_for", filter.alert_for);
    }
    const totalResult = await countQuery.first<CountResult>();
    const total = totalResult ? Number(totalResult.count) : 0;

    // Build paginated query
    let query = this.knex("monitor_alerts_config as mac").select("mac.*").orderBy("mac.id", "desc");
    if (filter?.monitor_tag) {
      query = query
        .join("monitor_alerts_config_monitors as macm", "mac.id", "macm.monitor_alerts_id")
        .where("macm.monitor_tag", filter.monitor_tag);
    }
    if (filter?.is_active) {
      query = query.andWhere("mac.is_active", filter.is_active);
    }
    if (filter?.alert_for) {
      query = query.andWhere("mac.alert_for", filter.alert_for);
    }
    const configs = await query.limit(limit).offset((page - 1) * limit);

    return { configs, total };
  }

  // ============ Monitor Alert Config Triggers CRUD ============

  /**
   * Add a trigger to a monitor alert config
   */
  async addTriggerToMonitorAlertConfig(data: MonitorAlertConfigTriggerInsert): Promise<number[]> {
    return await this.knex("monitor_alerts_config_triggers").insert({
      monitor_alerts_id: data.monitor_alerts_id,
      trigger_id: data.trigger_id,
      created_at: this.knex.fn.now(),
      updated_at: this.knex.fn.now(),
    });
  }

  /**
   * Add multiple triggers to a monitor alert config
   */
  async addTriggersToMonitorAlertConfig(monitorAlertsId: number, triggerIds: number[]): Promise<void> {
    if (triggerIds.length === 0) return;

    const inserts = triggerIds.map((triggerId) => ({
      monitor_alerts_id: monitorAlertsId,
      trigger_id: triggerId,
      created_at: this.knex.fn.now(),
      updated_at: this.knex.fn.now(),
    }));

    await this.knex("monitor_alerts_config_triggers").insert(inserts);
  }

  /**
   * Remove a trigger from a monitor alert config
   */
  async removeTriggerFromMonitorAlertConfig(monitorAlertsId: number, triggerId: number): Promise<number> {
    return await this.knex("monitor_alerts_config_triggers")
      .where({ monitor_alerts_id: monitorAlertsId, trigger_id: triggerId })
      .del();
  }

  /**
   * Remove all triggers from a monitor alert config
   */
  async removeAllTriggersFromMonitorAlertConfig(monitorAlertsId: number): Promise<number> {
    return await this.knex("monitor_alerts_config_triggers").where({ monitor_alerts_id: monitorAlertsId }).del();
  }

  /**
   * Get all trigger associations for a monitor alert config
   */
  async getMonitorAlertConfigTriggers(monitorAlertsId: number): Promise<MonitorAlertConfigTriggerRecord[]> {
    return await this.knex("monitor_alerts_config_triggers")
      .where({ monitor_alerts_id: monitorAlertsId })
      .orderBy("trigger_id", "asc");
  }

  /**
   * Get trigger IDs for a monitor alert config
   */
  async getMonitorAlertConfigTriggerIds(monitorAlertsId: number): Promise<number[]> {
    const records = await this.knex("monitor_alerts_config_triggers")
      .select("trigger_id")
      .where({ monitor_alerts_id: monitorAlertsId });
    return records.map((r: { trigger_id: number }) => r.trigger_id);
  }

  /**
   * Replace all triggers for a monitor alert config (remove old, add new)
   */
  async replaceMonitorAlertConfigTriggers(monitorAlertsId: number, triggerIds: number[]): Promise<void> {
    await this.removeAllTriggersFromMonitorAlertConfig(monitorAlertsId);
    if (triggerIds.length > 0) {
      await this.addTriggersToMonitorAlertConfig(monitorAlertsId, triggerIds);
    }
  }

  // ============ Monitor Alert Config Monitors CRUD ============

  /**
   * Add multiple monitors to an alert config
   */
  async addMonitorsToAlertConfig(alertConfigId: number, monitorTags: string[]): Promise<void> {
    if (monitorTags.length === 0) return;

    const inserts = monitorTags.map((monitorTag) => ({
      monitor_alerts_id: alertConfigId,
      monitor_tag: monitorTag,
      created_at: this.knex.fn.now(),
      updated_at: this.knex.fn.now(),
    }));

    await this.knex("monitor_alerts_config_monitors").insert(inserts);
  }

  /**
   * Remove all monitors from an alert config
   */
  async removeAllMonitorsFromAlertConfig(alertConfigId: number): Promise<number> {
    return await this.knex("monitor_alerts_config_monitors").where({ monitor_alerts_id: alertConfigId }).del();
  }

  /**
   * Replace all monitors for an alert config (remove old, add new)
   */
  async replaceAlertConfigMonitors(alertConfigId: number, monitorTags: string[]): Promise<void> {
    await this.removeAllMonitorsFromAlertConfig(alertConfigId);
    if (monitorTags.length > 0) {
      await this.addMonitorsToAlertConfig(alertConfigId, monitorTags);
    }
  }

  /**
   * Get monitor tags for an alert config
   */
  async getAlertConfigMonitorTags(alertConfigId: number): Promise<string[]> {
    const records = await this.knex("monitor_alerts_config_monitors")
      .select("monitor_tag")
      .where({ monitor_alerts_id: alertConfigId });
    return records.map((r: { monitor_tag: string }) => r.monitor_tag);
  }

  // ============ Composite / Join Operations ============

  /**
   * Get monitor alert config with its associated triggers
   */
  async getMonitorAlertConfigWithTriggers(id: number): Promise<MonitorAlertConfigWithTriggers | undefined> {
    const config = await this.getMonitorAlertConfigById(id);
    if (!config) return undefined;

    const triggerRecords = await this.knex("monitor_alerts_config_triggers as mact")
      .join("triggers as t", "mact.trigger_id", "t.id")
      .select("t.*")
      .where("mact.monitor_alerts_id", id);

    const monitorTags = await this.getAlertConfigMonitorTags(id);

    return {
      ...config,
      triggers: triggerRecords as TriggerRecord[],
      monitor_tags: monitorTags,
    };
  }

  /**
   * Get all monitor alert configs for a monitor with their triggers
   */
  async getMonitorAlertConfigsWithTriggersByMonitorTag(monitorTag: string): Promise<MonitorAlertConfigWithTriggers[]> {
    const configs = await this.getMonitorAlertConfigsByMonitorTag(monitorTag);

    const result: MonitorAlertConfigWithTriggers[] = [];
    for (const config of configs) {
      const triggerRecords = await this.knex("monitor_alerts_config_triggers as mact")
        .join("triggers as t", "mact.trigger_id", "t.id")
        .select("t.*")
        .where("mact.monitor_alerts_id", config.id);

      const monitorTags = await this.getAlertConfigMonitorTags(config.id);

      result.push({
        ...config,
        triggers: triggerRecords as TriggerRecord[],
        monitor_tags: monitorTags,
      });
    }

    return result;
  }

  /**
   * Get all active monitor alert configs with their triggers
   */
  async getActiveMonitorAlertConfigsWithTriggers(): Promise<MonitorAlertConfigWithTriggers[]> {
    const configs = await this.getActiveMonitorAlertConfigs();

    const result: MonitorAlertConfigWithTriggers[] = [];
    for (const config of configs) {
      const triggerRecords = await this.knex("monitor_alerts_config_triggers as mact")
        .join("triggers as t", "mact.trigger_id", "t.id")
        .select("t.*")
        .where("mact.monitor_alerts_id", config.id);

      const monitorTags = await this.getAlertConfigMonitorTags(config.id);

      result.push({
        ...config,
        triggers: triggerRecords as TriggerRecord[],
        monitor_tags: monitorTags,
      });
    }

    return result;
  }

  /**
   * Check if a trigger is associated with any monitor alert config
   */
  async isTriggerUsedInMonitorAlertConfig(triggerId: number): Promise<boolean> {
    const result = await this.knex("monitor_alerts_config_triggers")
      .count("* as count")
      .where({ trigger_id: triggerId })
      .first<CountResult>();
    return Number(result?.count) > 0;
  }

  /**
   * Get all monitor alert configs that use a specific trigger
   */
  async getMonitorAlertConfigsByTriggerId(triggerId: number): Promise<MonitorAlertConfigRecord[]> {
    return await this.knex("monitor_alerts_config as mac")
      .join("monitor_alerts_config_triggers as mact", "mac.id", "mact.monitor_alerts_id")
      .select("mac.*")
      .where("mact.trigger_id", triggerId)
      .orderBy("mac.id", "desc");
  }

  // ============ Monitor Alerts V2 CRUD ============

  /**
   * Insert a new monitor alert v2 record
   */
  async insertMonitorAlertV2(data: MonitorAlertV2Insert): Promise<MonitorAlertV2Record> {
    const dbType = GetDbType();
    const insertData: Record<string, unknown> = {
      config_id: data.config_id,
      monitor_tag: data.monitor_tag || null,
      incident_id: data.incident_id || null,
      alert_status: data.alert_status,
      created_at: this.knex.fn.now(),
      updated_at: this.knex.fn.now(),
    };

    if (dbType === "postgresql") {
      const [record] = await this.knex("monitor_alerts_v2").insert(insertData).returning("*");
      return record;
    } else {
      const [id] = await this.knex("monitor_alerts_v2").insert(insertData);
      const record = await this.knex("monitor_alerts_v2").where({ id }).first();
      return record as MonitorAlertV2Record;
    }
  }

  /**
   * Update an existing monitor alert v2 by ID
   */
  async updateMonitorAlertV2(id: number, data: MonitorAlertV2Update): Promise<number> {
    const updateData: Record<string, unknown> = {
      updated_at: this.knex.fn.now(),
    };

    if (data.incident_id !== undefined) updateData.incident_id = data.incident_id;
    if (data.alert_status !== undefined) updateData.alert_status = data.alert_status;

    return await this.knex("monitor_alerts_v2").where({ id }).update(updateData);
  }

  /**
   * Update alert status by ID
   */
  async updateMonitorAlertV2Status(id: number, alertStatus: MonitorAlertStatusType): Promise<number> {
    return await this.knex("monitor_alerts_v2").where({ id }).update({
      alert_status: alertStatus,
      updated_at: this.knex.fn.now(),
    });
  }

  /**
   * Get a single monitor alert v2 by ID
   */
  async getMonitorAlertV2ById(id: number): Promise<MonitorAlertV2Record | undefined> {
    return await this.knex("monitor_alerts_v2").where({ id }).first();
  }

  /**
   * Get monitor alerts v2 with optional filtering
   */
  async getMonitorAlertsV2(filter: MonitorAlertV2Filter): Promise<MonitorAlertV2Record[]> {
    let query = this.knex("monitor_alerts_v2").whereRaw("1=1");

    if (filter.id !== undefined) {
      query = query.andWhere("id", filter.id);
    }
    if (filter.config_id !== undefined) {
      query = query.andWhere("config_id", filter.config_id);
    }
    if (filter.monitor_tag !== undefined) {
      query = query.andWhere("monitor_tag", filter.monitor_tag);
    }
    if (filter.incident_id !== undefined) {
      query = query.andWhere("incident_id", filter.incident_id);
    }
    if (filter.alert_status !== undefined) {
      query = query.andWhere("alert_status", filter.alert_status);
    }

    return await query.orderBy("id", "desc");
  }

  /**
   * Get all monitor alerts v2 for a specific config
   */
  async getMonitorAlertsV2ByConfigId(configId: number): Promise<MonitorAlertV2Record[]> {
    return await this.knex("monitor_alerts_v2").where({ config_id: configId }).orderBy("id", "desc");
  }

  /**
   * Check if a triggered alert exists for a specific config
   */
  async hasTriggeredAlertForConfig(configId: number): Promise<boolean> {
    const result = await this.knex("monitor_alerts_v2")
      .count("* as count")
      .where({ config_id: configId, alert_status: "TRIGGERED" })
      .first<CountResult>();
    return Number(result?.count) > 0;
  }

  /**
   * Get the active (TRIGGERED) alert for a specific config
   */
  async getActiveAlertForConfig(configId: number): Promise<MonitorAlertV2Record | undefined> {
    return await this.knex("monitor_alerts_v2")
      .where({ config_id: configId, alert_status: "TRIGGERED" })
      .orderBy("id", "desc")
      .first();
  }

  /**
   * Get all triggered alerts
   */
  async getAllTriggeredAlerts(): Promise<MonitorAlertV2Record[]> {
    return await this.knex("monitor_alerts_v2").where({ alert_status: "TRIGGERED" }).orderBy("id", "desc");
  }

  /**
   * Delete a monitor alert v2 by ID
   */
  async deleteMonitorAlertV2(id: number): Promise<number> {
    return await this.knex("monitor_alerts_v2").where({ id }).del();
  }

  /**
   * Delete all monitor alerts v2 for a specific config
   */
  async deleteMonitorAlertsV2ByConfigId(configId: number): Promise<number> {
    return await this.knex("monitor_alerts_v2").where({ config_id: configId }).del();
  }

  /**
   * Get monitor alert v2 with its config details
   */
  async getMonitorAlertV2WithConfig(id: number): Promise<MonitorAlertV2WithConfig | undefined> {
    const alert = await this.getMonitorAlertV2ById(id);
    if (!alert) return undefined;

    const config = await this.getMonitorAlertConfigById(alert.config_id);
    if (!config) return undefined;

    return {
      ...alert,
      config,
    };
  }

  /**
   * Get all triggered alerts with their config details
   */
  async getAllTriggeredAlertsWithConfig(): Promise<MonitorAlertV2WithConfig[]> {
    const alerts = await this.getAllTriggeredAlerts();
    const result: MonitorAlertV2WithConfig[] = [];

    for (const alert of alerts) {
      const config = await this.getMonitorAlertConfigById(alert.config_id);
      if (config) {
        result.push({ ...alert, config });
      }
    }

    return result;
  }

  /**
   * Add incident ID to an alert
   */
  async addIncidentToAlert(alertId: number, incidentId: number): Promise<number> {
    return await this.knex("monitor_alerts_v2").where({ id: alertId }).update({
      incident_id: incidentId,
      updated_at: this.knex.fn.now(),
    });
  }

  /**
   * Get alerts by incident ID
   */
  async getAlertsByIncidentId(incidentId: number): Promise<MonitorAlertV2Record[]> {
    return await this.knex("monitor_alerts_v2").where({ incident_id: incidentId }).orderBy("id", "desc");
  }

  /**
   * Count monitor alerts v2 with optional filtering
   */
  async getMonitorAlertsV2Count(filter: MonitorAlertV2Filter): Promise<CountResult | undefined> {
    let query = this.knex("monitor_alerts_v2").count("* as count");

    if (filter.id !== undefined) {
      query = query.andWhere("id", filter.id);
    }
    if (filter.config_id !== undefined) {
      query = query.andWhere("config_id", filter.config_id);
    }
    if (filter.incident_id !== undefined) {
      query = query.andWhere("incident_id", filter.incident_id);
    }
    if (filter.alert_status !== undefined) {
      query = query.andWhere("alert_status", filter.alert_status);
    }

    return await query.first<CountResult>();
  }

  /**
   * Get paginated monitor alerts v2 with config details
   */
  async getMonitorAlertsV2Paginated(
    page: number,
    limit: number,
    filter?: MonitorAlertV2Filter,
  ): Promise<{ alerts: MonitorAlertV2WithConfig[]; total: number }> {
    // Build count query
    let countQuery = this.knex("monitor_alerts_v2").count("* as count");
    if (filter?.alert_status) {
      countQuery = countQuery.where("alert_status", filter.alert_status);
    }
    if (filter?.config_id) {
      countQuery = countQuery.andWhere("config_id", filter.config_id);
    }
    const totalResult = await countQuery.first<CountResult>();
    const total = totalResult ? Number(totalResult.count) : 0;

    // Build paginated query
    let query = this.knex("monitor_alerts_v2").orderBy("id", "desc");
    if (filter?.alert_status) {
      query = query.where("alert_status", filter.alert_status);
    }
    if (filter?.config_id) {
      query = query.andWhere("config_id", filter.config_id);
    }
    const rawAlerts = await query.limit(limit).offset((page - 1) * limit);

    // Fetch config for each alert
    const alerts: MonitorAlertV2WithConfig[] = [];
    for (const alert of rawAlerts) {
      const config = await this.getMonitorAlertConfigById(alert.config_id);
      if (config) {
        alerts.push({ ...alert, config });
      }
    }

    return { alerts, total };
  }
}
