import type { Kysely } from "kysely";

/**
 * Export approval requests.
 *
 * `console_export_requests` is newer than the last kysely-codegen run, so it is absent
 * from the generated `DB` type — this works against an untyped `Kysely<any>`,
 * the same approach `promo-code-campaigns.ts` uses.
 *
 * The workbook itself lives in GCS; the row carries `file_path`. Rows written before
 * that change hold the bytes in `file_bytes` instead, so anything reading a file has
 * to cope with either — see `getWithFile`.
 */

export type ExportRequestStatus = "pending" | "approved" | "rejected";

export interface ExportRequestRow {
  id: string;
  requested_by: string;
  requested_by_id: string | null;
  section: string;
  label: string;
  filename: string;
  filters_summary: string | null;
  row_count: number;
  file_size: number;
  status: ExportRequestStatus;
  reviewed_by: string | null;
  reviewed_at: string | null;
  review_note: string | null;
  delivered_at: string | null;
  delivery_error: string | null;
  /** Set once the stored workbook has been deleted, 24h after delivery. */
  file_purged_at: string | null;
  created_at: string;
}

/** A console user an export mail is addressed to. */
export interface ConsoleUserRecipient {
  id: string;
  email: string;
  name: string;
}

function toRecipient(row: {
  id: string;
  email: string | null;
  first_name?: string | null;
  last_name?: string | null;
}): ConsoleUserRecipient {
  const name = [row.first_name, row.last_name]
    .map((p) => (p ?? "").trim())
    .filter(Boolean)
    .join(" ");
  const email = String(row.email ?? "").trim();
  return { id: String(row.id), email, name: name || email };
}

/** Columns safe to list — deliberately excludes `file_bytes`. */
const LIST_COLUMNS = [
  "id",
  "requested_by",
  "requested_by_id",
  "section",
  "label",
  "filename",
  "filters_summary",
  "row_count",
  "file_size",
  "status",
  "reviewed_by",
  "reviewed_at",
  "review_note",
  "delivered_at",
  "delivery_error",
  // Listed so the queue can say the file is gone rather than offering a download
  // that would 410.
  "file_purged_at",
  "created_at",
] as const;

export class ExportRequestsRepository {
  private db: Kysely<any>;

  constructor(db: unknown) {
    this.db = db as Kysely<any>;
  }

  /**
   * Records a request whose workbook is already in the bucket.
   *
   * Takes the object path and the size rather than the bytes: the upload happens
   * before this call, so a failure to store the file never leaves behind a request
   * that can't be fulfilled.
   */
  async create(input: {
    requestedBy: string;
    requestedById: string | null;
    section: string;
    label: string;
    filename: string;
    filtersSummary: string | null;
    rowCount: number;
    filePath: string;
    fileSize: number;
  }): Promise<ExportRequestRow> {
    const row = await this.db
      .insertInto("console_export_requests")
      .values({
        requested_by: input.requestedBy,
        requested_by_id: input.requestedById,
        section: input.section,
        label: input.label,
        filename: input.filename,
        filters_summary: input.filtersSummary,
        row_count: input.rowCount,
        file_path: input.filePath,
        file_size: input.fileSize,
        status: "pending",
      })
      .returning([...LIST_COLUMNS])
      .executeTakeFirstOrThrow();
    return row as ExportRequestRow;
  }

  /**
   * The review queue.
   *
   * `file_bytes` is never selected here — a list of twenty requests would
   * otherwise ship twenty spreadsheets to the browser.
   */
  async list(opts: {
    status?: ExportRequestStatus;
    requestedBy?: string;
    limit: number;
    offset: number;
  }): Promise<{ items: ExportRequestRow[]; total: number }> {
    const base = () => {
      let q: any = this.db.selectFrom("console_export_requests");
      if (opts.status) q = q.where("status", "=", opts.status);
      // Used for the "my requests" view, so a non-admin can see their own.
      if (opts.requestedBy) q = q.where("requested_by", "=", opts.requestedBy);
      return q;
    };

    const [items, count] = await Promise.all([
      base()
        .select([...LIST_COLUMNS])
        // Pending first so the queue leads with what needs action.
        .orderBy(
          this.db.dynamic.ref("status") as any,
          "asc",
        )
        .orderBy("created_at", "desc")
        .limit(opts.limit)
        .offset(opts.offset)
        .execute(),
      base()
        .select(({ fn }: any) => [fn.countAll().as("total")])
        .executeTakeFirst(),
    ]);
    return {
      items: items as ExportRequestRow[],
      total: Number((count as any)?.total ?? 0),
    };
  }

  /**
   * Metadata plus whatever locates the file — for delivering or downloading it.
   *
   * Returns both `file_path` and the legacy `file_bytes` because either may be the
   * one that is set: new rows point at GCS, rows predating that still carry their
   * only copy inline. Callers use `resolveWorkbook` rather than reaching for a
   * particular one.
   */
  async getWithFile(id: string): Promise<
    | (ExportRequestRow & {
        file_path: string | null;
        file_bytes: Buffer | null;
      })
    | null
  > {
    const row = await this.db
      .selectFrom("console_export_requests")
      .select([...LIST_COLUMNS, "file_path", "file_bytes"])
      .where("id", "=", id)
      .executeTakeFirst();
    return (row as any) ?? null;
  }

  /**
   * Moves a request out of `pending`.
   *
   * Guarded on the current status being `pending` so two approvers acting at once
   * can't both send the mail — the second update matches no row.
   */
  async review(input: {
    id: string;
    status: Exclude<ExportRequestStatus, "pending">;
    reviewedBy: string;
    note: string | null;
  }): Promise<ExportRequestRow | null> {
    const row = await this.db
      .updateTable("console_export_requests")
      .set({
        status: input.status,
        reviewed_by: input.reviewedBy,
        reviewed_at: new Date(),
        review_note: input.note,
      })
      .where("id", "=", input.id)
      .where("status", "=", "pending")
      .returning([...LIST_COLUMNS])
      .executeTakeFirst();
    return (row as ExportRequestRow | undefined) ?? null;
  }

  /**
   * Active super admins, for the "a request is waiting" and "it was reviewed"
   * notices.
   *
   * Queried here rather than hard-coded or configured: whoever is a super admin
   * today is exactly who can action the queue, so the notification list can never
   * drift from the permission.
   *
   * The id comes back alongside the address because each mail carries a sign-in
   * link minted for one console user — a shared link would sign every reader in as
   * the same person. Inactive and deleted accounts are excluded: they cannot action
   * the queue, so mailing them a sign-in link would be both useless and unwise.
   */
  async superAdmins(): Promise<ConsoleUserRecipient[]> {
    const rows = await this.db
      .selectFrom("console_users")
      .select(["id", "email", "first_name", "last_name"])
      .where("is_super_admin", "=", true)
      .where("is_active", "=", true)
      .where((eb: any) =>
        eb.or([eb("is_deleted", "is", null), eb("is_deleted", "=", false)]),
      )
      .execute();
    return rows
      .map((r: any) => toRecipient(r))
      .filter((r: ConsoleUserRecipient) => r.email.includes("@"));
  }

  /**
   * Resolves a requester's console account from the address on the request.
   *
   * The row stores `requested_by_id`, but it is nullable and rows written before it
   * existed have only the address. Looking the account up by email lets those
   * requesters still get a working sign-in link instead of a bare URL.
   */
  async consoleUserByEmail(email: string): Promise<ConsoleUserRecipient | null> {
    if (!email) return null;
    const row = await this.db
      .selectFrom("console_users")
      .select(["id", "email", "first_name", "last_name"])
      .where("email", "=", email)
      .where("is_active", "=", true)
      .executeTakeFirst();
    return row ? toRecipient(row) : null;
  }

  /**
   * The requester's account by the id stored on the request.
   *
   * Not filtered on `is_active`: this is only used to put a human name on a mail
   * about something they already did, and a deactivated account still deserves to
   * be named rather than shown as a bare address. Whether they may actually sign in
   * is decided when the link is redeemed, not here.
   */
  async consoleUserById(id: string): Promise<ConsoleUserRecipient | null> {
    if (!id) return null;
    const row = await this.db
      .selectFrom("console_users")
      .select(["id", "email", "first_name", "last_name"])
      .where("id", "=", id)
      .executeTakeFirst();
    return row ? toRecipient(row) : null;
  }

  /**
   * Delivered exports whose file is now past its retention window.
   *
   * Only rows that actually reached the requester: `delivered_at` is set by
   * `markDelivery` on a successful send, so an approval whose email failed keeps its
   * file and stays resendable. Pending and rejected requests are never returned —
   * the first still needs its file for review, the second is a separate question.
   *
   * `limit` bounds one pass so a large backlog is worked through over several runs
   * instead of one long transaction against the bucket.
   */
  async findPurgeableFiles(
    deliveredBefore: Date,
    limit: number,
  ): Promise<Array<{ id: string; file_path: string; delivered_at: string }>> {
    const rows = await this.db
      .selectFrom("console_export_requests")
      .select(["id", "file_path", "delivered_at"])
      .where("status", "=", "approved")
      .where("delivered_at", "is not", null)
      .where("delivered_at", "<", deliveredBefore)
      .where("file_purged_at", "is", null)
      .where("file_path", "is not", null)
      .orderBy("delivered_at", "asc")
      .limit(limit)
      .execute();
    return rows as Array<{
      id: string;
      file_path: string;
      delivered_at: string;
    }>;
  }

  /**
   * Records that the stored file is gone.
   *
   * Called after the object has actually been deleted, never before: if this update
   * failed first, the object would be left with nothing pointing at it and no row to
   * drive a retry. In the other order a failed update just means the next pass tries
   * the delete again, which is harmless.
   */
  async markFilePurged(id: string): Promise<void> {
    await this.db
      .updateTable("console_export_requests")
      .set({ file_purged_at: new Date() })
      .where("id", "=", id)
      .execute();
  }

  /** Records the outcome of the email send, so a failure is visible. */
  async markDelivery(
    id: string,
    error: string | null,
  ): Promise<void> {
    await this.db
      .updateTable("console_export_requests")
      .set(
        error
          ? { delivery_error: error }
          : { delivered_at: new Date(), delivery_error: null },
      )
      .where("id", "=", id)
      .execute();
  }
}