import { and, desc, eq, gte, lt, sql, type SQL } from "drizzle-orm";
import type { Request, Response } from "express";
import { db, permissionAuditTable, usersTable } from "@workspace/db";
import type {
  PermissionAuditChangeKind,
  PermissionAuditTargetKind,
} from "@workspace/db";

// Shared building blocks for the permission_audit table. Mirrors the role
// permission audit pattern in routes/roles.ts so the per-entity history
// endpoints stay consistent in shape, filters and pagination semantics.

export const PERMISSION_AUDIT_DATE_RE = /^\d{4}-\d{2}-\d{2}$/;

function parseUtcDate(s: string): Date | null {
  if (!PERMISSION_AUDIT_DATE_RE.test(s)) return null;
  const d = new Date(`${s}T00:00:00.000Z`);
  if (Number.isNaN(d.getTime())) return null;
  const [y, m, day] = s.split("-").map(Number);
  if (
    d.getUTCFullYear() !== y ||
    d.getUTCMonth() + 1 !== m ||
    d.getUTCDate() !== day
  ) {
    return null;
  }
  return d;
}

export type PermissionAuditFilters = {
  actorUserId: number | null;
  fromDate: Date | null;
  toDateExclusive: Date | null;
};

export function parsePermissionAuditFilters(
  req: Request,
  res: Response,
): PermissionAuditFilters | null {
  const actorUserIdStr =
    typeof req.query.actorUserId === "string"
      ? req.query.actorUserId.trim()
      : "";
  const fromStr = typeof req.query.from === "string" ? req.query.from.trim() : "";
  const toStr = typeof req.query.to === "string" ? req.query.to.trim() : "";

  let actorUserId: number | null = null;
  if (actorUserIdStr) {
    const parsed = Number(actorUserIdStr);
    if (!Number.isFinite(parsed) || !Number.isInteger(parsed) || parsed < 0) {
      res
        .status(400)
        .json({ error: "Invalid 'actorUserId' (expected non-negative integer)" });
      return null;
    }
    // 0 = explicit "no filter" so admins can clear the dropdown without
    // dropping the param entirely from the URL.
    actorUserId = parsed > 0 ? parsed : null;
  }
  let fromDate: Date | null = null;
  let toDateExclusive: Date | null = null;
  if (fromStr) {
    fromDate = parseUtcDate(fromStr);
    if (!fromDate) {
      res.status(400).json({ error: "Invalid 'from' date (expected YYYY-MM-DD)" });
      return null;
    }
  }
  if (toStr) {
    const toDate = parseUtcDate(toStr);
    if (!toDate) {
      res.status(400).json({ error: "Invalid 'to' date (expected YYYY-MM-DD)" });
      return null;
    }
    toDateExclusive = new Date(toDate.getTime() + 24 * 60 * 60 * 1000);
  }
  if (fromDate && toDateExclusive && fromDate >= toDateExclusive) {
    res.status(400).json({ error: "'from' must be on or before 'to'" });
    return null;
  }
  return { actorUserId, fromDate, toDateExclusive };
}

function buildWhere(
  targetKind: PermissionAuditTargetKind,
  targetId: number,
  filters: PermissionAuditFilters,
): SQL {
  const conditions: SQL[] = [
    eq(permissionAuditTable.targetKind, targetKind),
    eq(permissionAuditTable.targetId, targetId),
  ];
  if (filters.actorUserId != null)
    conditions.push(eq(permissionAuditTable.actorUserId, filters.actorUserId));
  if (filters.fromDate)
    conditions.push(gte(permissionAuditTable.createdAt, filters.fromDate));
  if (filters.toDateExclusive)
    conditions.push(lt(permissionAuditTable.createdAt, filters.toDateExclusive));
  return and(...conditions) as SQL;
}

type SerializedActor = {
  id: number;
  username: string;
  displayNameAr: string | null;
  displayNameEn: string | null;
  avatarUrl: string | null;
} | null;

export type SerializedPermissionAuditEntry = {
  id: number;
  targetKind: PermissionAuditTargetKind;
  targetId: number;
  changeKind: PermissionAuditChangeKind;
  previousIds: number[];
  newIds: number[];
  addedIds: number[];
  removedIds: number[];
  createdAt: Date;
  actor: SerializedActor;
};

export async function listPermissionAudit(opts: {
  targetKind: PermissionAuditTargetKind;
  targetId: number;
  limit: number;
  offset: number;
  filters: PermissionAuditFilters;
}): Promise<{
  entries: SerializedPermissionAuditEntry[];
  totalCount: number;
  limit: number;
  offset: number;
  nextOffset: number | null;
}> {
  const where = buildWhere(opts.targetKind, opts.targetId, opts.filters);

  const rows = await db
    .select({
      id: permissionAuditTable.id,
      targetKind: permissionAuditTable.targetKind,
      targetId: permissionAuditTable.targetId,
      changeKind: permissionAuditTable.changeKind,
      previousIds: permissionAuditTable.previousIds,
      newIds: permissionAuditTable.newIds,
      createdAt: permissionAuditTable.createdAt,
      actorUserId: permissionAuditTable.actorUserId,
      actorUsername: usersTable.username,
      actorDisplayNameAr: usersTable.displayNameAr,
      actorDisplayNameEn: usersTable.displayNameEn,
      actorAvatarUrl: usersTable.avatarUrl,
    })
    .from(permissionAuditTable)
    .leftJoin(usersTable, eq(usersTable.id, permissionAuditTable.actorUserId))
    .where(where)
    .orderBy(desc(permissionAuditTable.createdAt), desc(permissionAuditTable.id))
    .limit(opts.limit)
    .offset(opts.offset);

  const [{ count: totalCount } = { count: 0 }] = await db
    .select({ count: sql<number>`count(*)::int` })
    .from(permissionAuditTable)
    .where(where);

  const entries: SerializedPermissionAuditEntry[] = rows.map((r) => {
    const prev = (r.previousIds ?? []) as number[];
    const next = (r.newIds ?? []) as number[];
    const prevSet = new Set(prev);
    const nextSet = new Set(next);
    return {
      id: r.id,
      targetKind: r.targetKind as PermissionAuditTargetKind,
      targetId: r.targetId,
      changeKind: r.changeKind as PermissionAuditChangeKind,
      previousIds: prev,
      newIds: next,
      addedIds: next.filter((p) => !prevSet.has(p)),
      removedIds: prev.filter((p) => !nextSet.has(p)),
      createdAt: r.createdAt,
      actor:
        r.actorUserId != null && r.actorUsername != null
          ? {
              id: r.actorUserId,
              username: r.actorUsername,
              displayNameAr: r.actorDisplayNameAr,
              displayNameEn: r.actorDisplayNameEn,
              avatarUrl: r.actorAvatarUrl,
            }
          : null,
    };
  });

  const nextOffset =
    opts.offset + rows.length < totalCount
      ? opts.offset + rows.length
      : null;

  return {
    entries,
    totalCount,
    limit: opts.limit,
    offset: opts.offset,
    nextOffset,
  };
}

// Helper used by route handlers to serialize the prev/new id arrays into
// the shape the audit table expects. We always sort to keep entries
// canonical and easy to diff.
export function sortedIds(ids: Iterable<number>): number[] {
  return Array.from(new Set(ids)).sort((a, b) => a - b);
}

type Tx = Parameters<Parameters<typeof db.transaction>[0]>[0];

export async function recordPermissionAudit(
  tx: Tx,
  args: {
    targetKind: PermissionAuditTargetKind;
    targetId: number;
    changeKind: PermissionAuditChangeKind;
    actorUserId: number | null;
    previousIds: number[];
    newIds: number[];
  },
) {
  await tx.insert(permissionAuditTable).values({
    targetKind: args.targetKind,
    targetId: args.targetId,
    changeKind: args.changeKind,
    actorUserId: args.actorUserId ?? null,
    previousIds: sortedIds(args.previousIds),
    newIds: sortedIds(args.newIds),
  });
}
