import "server-only";

import type { DashboardQuery, SortKey } from "@/lib/dashboard-query";
import type { DisplayStatus, HealthStatus } from "@/lib/status";
import { Prisma } from "@/generated/prisma/client";
import { db } from "../db";

export const DASHBOARD_PAGE_SIZE = 100;

export type DashboardSummary = {
  total: number;
  working: number;
  slow: number;
  notWorking: number;
  disabled: number;
  pending: number; // enabled but never checked
  lastCheckedAt: Date | null;
};

export type DashboardRow = {
  id: string;
  projectCode: string;
  projectName: string;
  monitoringEnabled: boolean;
  currentStatus: HealthStatus | null;
  lastCheckedAt: Date | null;
  currentHttpStatus: number | null;
  currentResponseTimeMs: number | null;
  currentErrorMessage: string | null;
  failureStartedAt: Date | null;
};

export async function getDashboardSummary(): Promise<DashboardSummary> {
  const [row] = await db.$queryRaw<
    Array<Omit<DashboardSummary, "lastCheckedAt"> & { last_checked_at: Date | null }>
  >`
    SELECT
      COUNT(*)::int                                                                       AS total,
      COUNT(*) FILTER (WHERE monitoring_enabled AND current_status = 'WORKING')::int      AS working,
      COUNT(*) FILTER (WHERE monitoring_enabled AND current_status = 'SLOW')::int         AS slow,
      COUNT(*) FILTER (WHERE monitoring_enabled AND current_status = 'NOT_WORKING')::int  AS "notWorking",
      COUNT(*) FILTER (WHERE NOT monitoring_enabled)::int                                 AS disabled,
      COUNT(*) FILTER (WHERE monitoring_enabled AND current_status IS NULL)::int          AS pending,
      MAX(last_checked_at) FILTER (WHERE monitoring_enabled)                              AS last_checked_at
    FROM projects
    WHERE deleted_at IS NULL`;

  const { last_checked_at, ...counts } = row;
  return { ...counts, lastCheckedAt: last_checked_at };
}

// Must mirror displayStatus() in src/lib/status.ts.
export const STATUS_FILTERS: Record<DisplayStatus, Prisma.Sql> = {
  WORKING: Prisma.sql`monitoring_enabled AND current_status = 'WORKING'`,
  SLOW: Prisma.sql`monitoring_enabled AND current_status = 'SLOW'`,
  NOT_WORKING: Prisma.sql`monitoring_enabled AND current_status = 'NOT_WORKING'`,
  PENDING: Prisma.sql`monitoring_enabled AND current_status IS NULL`,
  DISABLED: Prisma.sql`NOT monitoring_enabled`,
};

// Fixed SQL fragments only — user input picks a key, it is never interpolated.
// Check results of disabled apps are hidden in the table, so they sort as empty.
const SORT_EXPRESSIONS: Record<SortKey, Prisma.Sql> = {
  status: Prisma.sql`CASE
    WHEN NOT monitoring_enabled THEN 4
    WHEN current_status = 'NOT_WORKING' THEN 0
    WHEN current_status = 'SLOW' THEN 1
    WHEN current_status IS NULL THEN 2
    ELSE 3 END`,
  code: Prisma.sql`LOWER(project_code)`,
  name: Prisma.sql`LOWER(project_name)`,
  checkedAt: Prisma.sql`CASE WHEN monitoring_enabled THEN last_checked_at END`,
  responseTime: Prisma.sql`CASE WHEN monitoring_enabled THEN current_response_time_ms END`,
};

function escapeLike(term: string): string {
  return term.replace(/[\\%_]/g, (char) => `\\${char}`);
}

export async function listDashboardRows(query: DashboardQuery) {
  const conditions: Prisma.Sql[] = [Prisma.sql`deleted_at IS NULL`];
  if (query.status) conditions.push(STATUS_FILTERS[query.status]);
  if (query.q) {
    const pattern = `%${escapeLike(query.q)}%`;
    conditions.push(Prisma.sql`(project_code ILIKE ${pattern} OR project_name ILIKE ${pattern})`);
  }
  const where = Prisma.join(conditions, " AND ");
  const direction = query.dir === "desc" ? Prisma.sql`DESC` : Prisma.sql`ASC`;

  const [[{ total }], rows] = await Promise.all([
    db.$queryRaw<Array<{ total: number }>>`SELECT COUNT(*)::int AS total FROM projects WHERE ${where}`,
    db.$queryRaw<DashboardRow[]>`
      SELECT
        id,
        project_code             AS "projectCode",
        project_name             AS "projectName",
        monitoring_enabled       AS "monitoringEnabled",
        current_status::text     AS "currentStatus",
        last_checked_at          AS "lastCheckedAt",
        current_http_status      AS "currentHttpStatus",
        current_response_time_ms AS "currentResponseTimeMs",
        current_error_message    AS "currentErrorMessage",
        failure_started_at       AS "failureStartedAt"
      FROM projects
      WHERE ${where}
      ORDER BY ${SORT_EXPRESSIONS[query.sort]} ${direction} NULLS LAST, LOWER(project_code) ASC, id ASC
      LIMIT ${DASHBOARD_PAGE_SIZE} OFFSET ${(query.page - 1) * DASHBOARD_PAGE_SIZE}`,
  ]);

  return { rows, total, pageCount: Math.max(1, Math.ceil(total / DASHBOARD_PAGE_SIZE)) };
}

// ---------------------------------------------------------------------------
// Analytics
// ---------------------------------------------------------------------------

export const TREND_DAYS = 14;

export type TrendDay = {
  /** Calendar day in the display timezone, as YYYY-MM-DD. */
  day: string;
  working: number;
  slow: number;
  notWorking: number;
};

/**
 * Check outcomes per calendar day (in the display timezone) for the last
 * TREND_DAYS days, including days with no checks. Deleted apps are excluded
 * so the trend reflects the apps that are still on the dashboard.
 */
export async function getCheckTrend(timezone: string, now: Date = new Date()): Promise<TrendDay[]> {
  const since = new Date(now.getTime() - (TREND_DAYS - 1) * 86_400_000);
  const rows = await db.$queryRaw<Array<{ day: string; status: HealthStatus; count: number }>>`
    SELECT
      to_char(hc.checked_at AT TIME ZONE ${timezone}, 'YYYY-MM-DD') AS day,
      hc.status::text                                              AS status,
      COUNT(*)::int                                                AS count
    FROM health_checks hc
    JOIN projects p ON p.id = hc.project_id
    WHERE p.deleted_at IS NULL
      AND hc.checked_at >= ${since}
    GROUP BY 1, 2`;

  const byDay = new Map<string, TrendDay>();
  const dayKey = new Intl.DateTimeFormat("en-CA", { timeZone: timezone, year: "numeric", month: "2-digit", day: "2-digit" });
  for (let i = TREND_DAYS - 1; i >= 0; i--) {
    const day = dayKey.format(new Date(now.getTime() - i * 86_400_000));
    byDay.set(day, { day, working: 0, slow: 0, notWorking: 0 });
  }
  for (const row of rows) {
    const entry = byDay.get(row.day);
    if (!entry) continue; // the window edge in another day boundary
    if (row.status === "WORKING") entry.working = row.count;
    else if (row.status === "SLOW") entry.slow = row.count;
    else entry.notWorking = row.count;
  }
  return [...byDay.values()];
}

export type ResponseTimeStats = {
  /** Enabled apps whose latest check produced a response time. */
  measured: number;
  averageMs: number | null;
  medianMs: number | null;
  slowestMs: number | null;
  /** Buckets over the latest response time of every enabled, responding app. */
  buckets: { under1s: number; under5s: number; under15s: number; over15s: number };
};

export async function getResponseTimeStats(): Promise<ResponseTimeStats> {
  const [row] = await db.$queryRaw<
    Array<{
      measured: number;
      average: number | null;
      median: number | null;
      slowest: number | null;
      under1s: number;
      under5s: number;
      under15s: number;
      over15s: number;
    }>
  >`
    SELECT
      COUNT(*)::int                                                          AS measured,
      ROUND(AVG(current_response_time_ms))::int                              AS average,
      PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY current_response_time_ms)  AS median,
      MAX(current_response_time_ms)::int                                     AS slowest,
      COUNT(*) FILTER (WHERE current_response_time_ms < 1000)::int           AS under1s,
      COUNT(*) FILTER (WHERE current_response_time_ms >= 1000 AND current_response_time_ms < 5000)::int   AS under5s,
      COUNT(*) FILTER (WHERE current_response_time_ms >= 5000 AND current_response_time_ms < 15000)::int  AS under15s,
      COUNT(*) FILTER (WHERE current_response_time_ms >= 15000)::int         AS over15s
    FROM projects
    WHERE deleted_at IS NULL
      AND monitoring_enabled
      AND current_status IN ('WORKING', 'SLOW')
      AND current_response_time_ms IS NOT NULL`;

  return {
    measured: row.measured,
    averageMs: row.average,
    medianMs: row.median === null ? null : Math.round(Number(row.median)),
    slowestMs: row.slowest,
    buckets: { under1s: row.under1s, under5s: row.under5s, under15s: row.under15s, over15s: row.over15s },
  };
}

export type AttentionRow = {
  id: string;
  projectCode: string;
  projectName: string;
  currentStatus: HealthStatus;
  currentResponseTimeMs: number | null;
  currentErrorMessage: string | null;
  failureStartedAt: Date | null;
  lastCheckedAt: Date | null;
};

const ATTENTION_FIELDS = Prisma.sql`
  id,
  project_code             AS "projectCode",
  project_name             AS "projectName",
  current_status::text     AS "currentStatus",
  current_response_time_ms AS "currentResponseTimeMs",
  current_error_message    AS "currentErrorMessage",
  failure_started_at       AS "failureStartedAt",
  last_checked_at          AS "lastCheckedAt"`;

/** Apps that are down, longest outage first. */
export function listOutages(limit = 5) {
  return db.$queryRaw<AttentionRow[]>`
    SELECT ${ATTENTION_FIELDS}
    FROM projects
    WHERE deleted_at IS NULL AND monitoring_enabled AND current_status = 'NOT_WORKING'
    ORDER BY failure_started_at ASC NULLS LAST, LOWER(project_code) ASC
    LIMIT ${limit}`;
}

/** Responding apps with the slowest latest response, slowest first. */
export function listSlowest(limit = 5) {
  return db.$queryRaw<AttentionRow[]>`
    SELECT ${ATTENTION_FIELDS}
    FROM projects
    WHERE deleted_at IS NULL AND monitoring_enabled AND current_status IN ('WORKING', 'SLOW')
      AND current_response_time_ms IS NOT NULL
    ORDER BY current_response_time_ms DESC, LOWER(project_code) ASC
    LIMIT ${limit}`;
}