// src/lib/admin/export-queries.ts — God-Mode structured-export row builders.
//
// Pure DB read loaders (the `export:run` sys-permission gate is enforced by
// the route handler that calls these — see [dataset]/route.ts). Each builder
// returns an ordered `columns` list + flat `rows`
// whose values are already primitives (Date → ISO string, nested objects →
// JSON string) so both the CSV serializer and JSON.stringify emit clean output.
// These are admin-sized tables: we load all rows, except the audit log which is
// capped (most-recent AUDIT_EXPORT_CAP) to bound a worst-case export.
import "server-only";
import { asc, desc } from "drizzle-orm";
import {
  computeBillingStatus,
  pickRelevantSubscription,
} from "@/lib/billing/status";
import { adminAuditLog } from "@/lib/db/admin-schema";
import { member, organization, subscription, user } from "@/lib/db/auth-schema";
import { db } from "@/lib/db/client";

export const EXPORT_DATASETS = [
  "users",
  "organizations",
  "subscriptions",
  "audit",
] as const;
export type ExportDataset = (typeof EXPORT_DATASETS)[number];

/** Most-recent audit rows exported; older rows are omitted (documented cap). */
export const AUDIT_EXPORT_CAP = 50_000;

export interface ExportPayload {
  columns: string[];
  rows: Record<string, unknown>[];
}

export function isExportDataset(value: string): value is ExportDataset {
  return (EXPORT_DATASETS as readonly string[]).includes(value);
}

function iso(d: Date | null | undefined): string | null {
  return d ? d.toISOString() : null;
}

async function buildUsers(): Promise<ExportPayload> {
  const rows = await db
    .select({
      id: user.id,
      email: user.email,
      name: user.name,
      role: user.role,
      banned: user.banned,
      emailVerified: user.emailVerified,
      twoFactorEnabled: user.twoFactorEnabled,
      createdAt: user.createdAt,
    })
    .from(user)
    .orderBy(asc(user.createdAt));
  return {
    columns: [
      "id",
      "email",
      "name",
      "role",
      "banned",
      "emailVerified",
      "twoFactorEnabled",
      "createdAt",
    ],
    rows: rows.map((r) => ({
      ...r,
      banned: Boolean(r.banned),
      createdAt: iso(r.createdAt),
    })),
  };
}

async function buildOrganizations(): Promise<ExportPayload> {
  const [orgs, subs, memberCounts] = await Promise.all([
    db
      .select({
        id: organization.id,
        name: organization.name,
        slug: organization.slug,
        stripeCustomerId: organization.stripeCustomerId,
        trialEndsAt: organization.trialEndsAt,
        createdAt: organization.createdAt,
      })
      .from(organization)
      .orderBy(asc(organization.name)),
    db
      .select({
        referenceId: subscription.referenceId,
        plan: subscription.plan,
        status: subscription.status,
        periodStart: subscription.periodStart,
      })
      .from(subscription),
    db.select({ organizationId: member.organizationId }).from(member),
  ]);

  const subsByOrg = new Map<string, (typeof subs)[number][]>();
  for (const s of subs) {
    const list = subsByOrg.get(s.referenceId) ?? [];
    list.push(s);
    subsByOrg.set(s.referenceId, list);
  }
  const countByOrg = new Map<string, number>();
  for (const m of memberCounts) {
    countByOrg.set(
      m.organizationId,
      (countByOrg.get(m.organizationId) ?? 0) + 1,
    );
  }

  const now = new Date();
  return {
    columns: [
      "id",
      "name",
      "slug",
      "stripeCustomerId",
      "trialEndsAt",
      "memberCount",
      "createdAt",
      "billingStatus",
      "plan",
    ],
    rows: orgs.map((org) => {
      const sub = pickRelevantSubscription(subsByOrg.get(org.id) ?? []);
      const billingStatus = computeBillingStatus(
        {
          trialEndsAt: org.trialEndsAt ?? null,
          subscriptionStatus: sub?.status ?? null,
        },
        now,
      );
      return {
        id: org.id,
        name: org.name,
        slug: org.slug,
        stripeCustomerId: org.stripeCustomerId,
        trialEndsAt: iso(org.trialEndsAt),
        memberCount: countByOrg.get(org.id) ?? 0,
        createdAt: iso(org.createdAt),
        billingStatus,
        plan: sub?.plan ?? null,
      };
    }),
  };
}

async function buildSubscriptions(): Promise<ExportPayload> {
  const rows = await db
    .select({
      id: subscription.id,
      organizationId: subscription.referenceId,
      plan: subscription.plan,
      status: subscription.status,
      stripeCustomerId: subscription.stripeCustomerId,
      stripeSubscriptionId: subscription.stripeSubscriptionId,
      seats: subscription.seats,
      periodEnd: subscription.periodEnd,
      canceledAt: subscription.canceledAt,
    })
    .from(subscription)
    .orderBy(asc(subscription.referenceId));
  return {
    columns: [
      "id",
      "organizationId",
      "plan",
      "status",
      "stripeCustomerId",
      "stripeSubscriptionId",
      "seats",
      "periodEnd",
      "canceledAt",
    ],
    rows: rows.map((r) => ({
      ...r,
      periodEnd: iso(r.periodEnd),
      canceledAt: iso(r.canceledAt),
    })),
  };
}

async function buildAudit(): Promise<ExportPayload> {
  const rows = await db
    .select({
      id: adminAuditLog.id,
      createdAt: adminAuditLog.createdAt,
      actorEmail: adminAuditLog.actorEmail,
      action: adminAuditLog.action,
      targetType: adminAuditLog.targetType,
      targetId: adminAuditLog.targetId,
      targetLabel: adminAuditLog.targetLabel,
      ipAddress: adminAuditLog.ipAddress,
      metadata: adminAuditLog.metadata,
    })
    .from(adminAuditLog)
    .orderBy(desc(adminAuditLog.createdAt))
    .limit(AUDIT_EXPORT_CAP);
  return {
    columns: [
      "id",
      "createdAt",
      "actorEmail",
      "action",
      "targetType",
      "targetId",
      "targetLabel",
      "ipAddress",
      "metadata",
    ],
    rows: rows.map((r) => ({
      ...r,
      createdAt: iso(r.createdAt),
      // Flatten the jsonb blob to a stable JSON string for both CSV and JSON.
      metadata: r.metadata == null ? null : JSON.stringify(r.metadata),
    })),
  };
}

/** Build the column list + flattened rows for a dataset. */
export function buildExportPayload(
  dataset: ExportDataset,
): Promise<ExportPayload> {
  switch (dataset) {
    case "users":
      return buildUsers();
    case "organizations":
      return buildOrganizations();
    case "subscriptions":
      return buildSubscriptions();
    case "audit":
      return buildAudit();
  }
}