import { Op, fn, col, literal } from "sequelize";
import { initDb } from "@/lib/db";
import type {
  ElectionSecurityEventType,
  ElectionSecurityReviewStatus,
} from "@/types";

export interface ElectionSecurityEventRow {
  id: string;
  electionId: string;
  voterRollId: string | null;
  eventType: ElectionSecurityEventType;
  ipHash: string | null;
  deviceFingerprintHash: string | null;
  detail: Record<string, unknown> | null;
  reviewStatus: ElectionSecurityReviewStatus;
  reviewedByUserId: string | null;
  createdAt: Date;
}

export async function getElectionSecurityEvents(
  electionId: string,
  workspaceId: string
): Promise<ElectionSecurityEventRow[]> {
  const { Election, ElectionSecurityEvent } = initDb();
  const election = await Election.findOne({
    where: { id: electionId, workspaceId },
  });
  if (!election) return [];

  const events = await ElectionSecurityEvent.findAll({
    where: { electionId },
    order: [["createdAt", "DESC"]],
    limit: 200,
  });

  return events.map((event) => ({
    id: event.id,
    electionId: event.electionId,
    voterRollId: event.voterRollId,
    eventType: event.eventType,
    ipHash: event.ipHash,
    deviceFingerprintHash: event.deviceFingerprintHash,
    detail: event.detail,
    reviewStatus: event.reviewStatus,
    reviewedByUserId: event.reviewedByUserId,
    createdAt: event.createdAt,
  }));
}

export async function getElectionObservers(electionId: string, workspaceId: string) {
  const { Election, ElectionObserver, User } = initDb();
  const election = await Election.findOne({
    where: { id: electionId, workspaceId },
  });
  if (!election) return [];

  const rows = await ElectionObserver.findAll({
    where: { electionId, revokedAt: null },
    include: [
      {
        model: User,
        as: "user",
        attributes: ["id", "name", "email", "role"],
      },
    ],
    order: [["invitedAt", "DESC"]],
  });

  return rows.map((row) => {
    const user = row.get("user") as
      | { id: string; name: string; email: string; role: string }
      | undefined;
    return {
      id: row.id,
      userId: row.userId,
      invitedAt: row.invitedAt,
      name: user?.name ?? "Unknown",
      email: user?.email ?? "",
      role: user?.role ?? "election_observer",
    };
  });
}

/**
 * Scheduled scan: find device/IP clusters on open elections and flag new ones.
 */
export async function runScheduledAnomalyScan(): Promise<{
  scannedElections: number;
  flagsCreated: number;
}> {
  const { Election, BallotCastEvent, ElectionSecurityEvent } = initDb();
  const openElections = await Election.findAll({
    where: { status: "open", isTestMode: false },
    attributes: ["id"],
  });

  let flagsCreated = 0;

  for (const election of openElections) {
    const deviceClusters = (await BallotCastEvent.findAll({
      attributes: [
        "deviceFingerprintHash",
        [fn("COUNT", col("id")), "castCount"],
        [fn("COUNT", fn("DISTINCT", col("voterRollId"))), "voterCount"],
      ],
      where: {
        electionId: election.id,
        deviceFingerprintHash: { [Op.ne]: null },
      },
      group: ["deviceFingerprintHash"],
      having: literal("COUNT(DISTINCT voterRollId) > 1"),
      raw: true,
    })) as unknown as {
      deviceFingerprintHash: string;
      castCount: number;
      voterCount: number;
    }[];

    for (const cluster of deviceClusters) {
      const existing = await ElectionSecurityEvent.findOne({
        where: {
          electionId: election.id,
          eventType: "device_reuse_flag",
          deviceFingerprintHash: cluster.deviceFingerprintHash,
          reviewStatus: "open",
        },
      });
      if (existing) continue;

      const sample = await BallotCastEvent.findOne({
        where: {
          electionId: election.id,
          deviceFingerprintHash: cluster.deviceFingerprintHash,
        },
        order: [["castAt", "DESC"]],
      });

      await ElectionSecurityEvent.create({
        electionId: election.id,
        voterRollId: sample?.voterRollId ?? null,
        eventType: "device_reuse_flag",
        ipHash: sample?.ipHash ?? null,
        deviceFingerprintHash: cluster.deviceFingerprintHash,
        detail: {
          source: "scheduled_scan",
          voterCount: Number(cluster.voterCount),
          castCount: Number(cluster.castCount),
        },
        reviewStatus: "open",
      });
      flagsCreated += 1;
    }

    const ipClusters = (await BallotCastEvent.findAll({
      attributes: [
        "ipHash",
        [fn("COUNT", col("id")), "castCount"],
        [fn("COUNT", fn("DISTINCT", col("voterRollId"))), "voterCount"],
      ],
      where: {
        electionId: election.id,
        ipHash: { [Op.ne]: null },
      },
      group: ["ipHash"],
      having: literal("COUNT(DISTINCT voterRollId) >= 3"),
      raw: true,
    })) as unknown as {
      ipHash: string;
      castCount: number;
      voterCount: number;
    }[];

    for (const cluster of ipClusters) {
      const existing = await ElectionSecurityEvent.findOne({
        where: {
          electionId: election.id,
          eventType: "ai_anomaly_flag",
          ipHash: cluster.ipHash,
          reviewStatus: "open",
        },
      });
      if (existing) continue;

      const sample = await BallotCastEvent.findOne({
        where: { electionId: election.id, ipHash: cluster.ipHash },
        order: [["castAt", "DESC"]],
      });

      await ElectionSecurityEvent.create({
        electionId: election.id,
        voterRollId: sample?.voterRollId ?? null,
        eventType: "ai_anomaly_flag",
        ipHash: cluster.ipHash,
        deviceFingerprintHash: sample?.deviceFingerprintHash ?? null,
        detail: {
          source: "scheduled_scan",
          reason: "ip_cluster",
          voterCount: Number(cluster.voterCount),
          castCount: Number(cluster.castCount),
        },
        reviewStatus: "open",
      });
      flagsCreated += 1;
    }
  }

  return { scannedElections: openElections.length, flagsCreated };
}