/**
 * Query layer for system_health_findings.
 *
 * All DB writes to this table go through these functions — not inline in routes.
 */

import { and, count, eq, isNull, desc } from 'drizzle-orm';
import type { DrizzleClient } from '../client.js';
import { systemHealthFindings, groupDeals, deals } from '../schema.js';

// ─── Read ─────────────────────────────────────────────────────────────────────

/** Returns all unresolved unarmed_entity findings, newest last-seen first. */
export async function listUnresolvedFindings(db: DrizzleClient) {
  return db
    .select()
    .from(systemHealthFindings)
    .where(
      and(eq(systemHealthFindings.kind, 'unarmed_entity'), isNull(systemHealthFindings.resolvedAt)),
    )
    .orderBy(desc(systemHealthFindings.lastSeenAt));
}

/** Count of unresolved unarmed_entity findings (for the admin banner). */
export async function countUnresolvedFindings(db: DrizzleClient): Promise<number> {
  const [row] = await db
    .select({ c: count() })
    .from(systemHealthFindings)
    .where(
      and(eq(systemHealthFindings.kind, 'unarmed_entity'), isNull(systemHealthFindings.resolvedAt)),
    );
  return row?.c ?? 0;
}

// ─── Write ────────────────────────────────────────────────────────────────────

/** Mark a finding as resolved (alarm was successfully re-armed). */
export async function resolveFinding(db: DrizzleClient, findingId: string): Promise<void> {
  await db
    .update(systemHealthFindings)
    .set({ resolvedAt: new Date() })
    .where(eq(systemHealthFindings.id, findingId));
}

export async function touchFinding(
  db: DrizzleClient,
  input: {
    kind: string;
    entityType: string;
    entityId: string;
    detail: string;
    at?: Date;
  },
): Promise<void> {
  const at = input.at ?? new Date();
  const existing = await db
    .select({ id: systemHealthFindings.id })
    .from(systemHealthFindings)
    .where(
      and(
        eq(systemHealthFindings.kind, input.kind),
        eq(systemHealthFindings.entityType, input.entityType),
        eq(systemHealthFindings.entityId, input.entityId),
        isNull(systemHealthFindings.resolvedAt),
      ),
    )
    .limit(1);

  if (existing[0]) {
    await db
      .update(systemHealthFindings)
      .set({ lastSeenAt: at, detail: input.detail })
      .where(eq(systemHealthFindings.id, existing[0].id));
    return;
  }

  await db.insert(systemHealthFindings).values({
    kind: input.kind,
    entityType: input.entityType,
    entityId: input.entityId,
    detail: input.detail,
  });
}

/**
 * Fetch alarm timing info for a group_deal entity.
 * Returns alarmAt + alarmKind derived from group state, or null if not found.
 */
export async function getGroupDealAlarmInfo(
  db: DrizzleClient,
  groupDealId: string,
): Promise<{ alarmAt: Date | null; alarmKind: 'partial_decision' | 'deadline' } | null> {
  const rows = await db
    .select({
      groupState: groupDeals.groupState,
      extendedDeadline: groupDeals.extendedDeadline,
      partialDecisionDeadline: groupDeals.partialDecisionDeadline,
      windowEnd: deals.windowEnd,
    })
    .from(groupDeals)
    .innerJoin(deals, eq(deals.id, groupDeals.dealId))
    .where(eq(groupDeals.id, groupDealId))
    .limit(1);
  const row = rows[0];
  if (!row) return null;
  if (row.groupState === 'PARTIAL_PENDING' && row.partialDecisionDeadline) {
    return { alarmAt: row.partialDecisionDeadline, alarmKind: 'partial_decision' };
  }
  return { alarmAt: row.extendedDeadline ?? row.windowEnd, alarmKind: 'deadline' };
}

/** Find the unresolved finding for a given entity, if any. */
export async function findUnresolvedFindingForEntity(
  db: DrizzleClient,
  entityType: string,
  entityId: string,
) {
  const rows = await db
    .select()
    .from(systemHealthFindings)
    .where(
      and(
        eq(systemHealthFindings.kind, 'unarmed_entity'),
        eq(systemHealthFindings.entityType, entityType),
        eq(systemHealthFindings.entityId, entityId),
        isNull(systemHealthFindings.resolvedAt),
      ),
    )
    .limit(1);
  return rows[0] ?? null;
}