import { createHash, randomUUID } from "node:crypto";
import dotenv from "dotenv";
import { createDBPool } from "@/internal/datastore/index";
import {
  buildVersionObjectPath,
  EDMStorage,
  type EDMTemplateObject,
} from "@/lib/storage/edm-storage";

dotenv.config();

/**
 * Mirror every SendGrid dynamic template into the local store (GCS + coredb).
 *
 * ── Safety ───────────────────────────────────────────────────────────────────
 * Dry-run by default. Nothing is written until you pass --apply, because the
 * .env this reads is production.
 *
 * ── Idempotency ──────────────────────────────────────────────────────────────
 * Re-runnable. A template whose active version already exists locally with a
 * matching html hash is skipped, so an interrupted run resumes cleanly and a
 * repeat run is a no-op.
 *
 * ── Why ids are preserved ────────────────────────────────────────────────────
 * Each template keeps its SendGrid "d-<hex>" id. Those ids are referenced as
 * bare strings from promo_code, signup_promo_code, curated_events,
 * member_offers and ~70 hardcoded entries in the main core's MAIL_TEMPLATES
 * registry. Minting new ids here would mean rewriting all of that; preserving
 * them makes the cutover a no-op for every consumer.
 *
 * Usage:
 *   tsx src/cmd/backfill-edm-to-gcs.ts              # dry run, reports only
 *   tsx src/cmd/backfill-edm-to-gcs.ts --apply      # writes
 *   tsx src/cmd/backfill-edm-to-gcs.ts --apply --limit 5
 */

const APPLY = process.argv.includes("--apply");
const limitArg = process.argv.indexOf("--limit");
const LIMIT = limitArg > -1 ? parseInt(process.argv[limitArg + 1] ?? "0", 10) : 0;

const SENDGRID_BASE = "https://api.sendgrid.com/v3";
const API_KEY = process.env.SENDGRID_API_KEY;

const config = {
  host: process.env.DATABASE_HOST,
  port: parseInt(process.env.DATABASE_PORT || "5432"),
  database: process.env.DATABASE_NAME,
  user: process.env.DATABASE_USER,
  password: process.env.DATABASE_PWD,
  ssl:
    process.env.DATABASE_HOST !== "localhost" &&
    process.env.DATABASE_HOST !== "127.0.0.1"
      ? { rejectUnauthorized: false }
      : undefined,
};

type SGVersion = {
  id: string;
  template_id: string;
  active: 0 | 1;
  name: string;
  html_content?: string;
  plain_content?: string;
  subject?: string;
  updated_at: string;
  generate_plain_content?: boolean;
};

type SGTemplate = {
  id: string;
  name: string;
  generation: string;
  updated_at: string;
  versions: SGVersion[];
};

const sg = async <T>(path: string): Promise<T> => {
  const res = await fetch(`${SENDGRID_BASE}${path}`, {
    headers: {
      Authorization: `Bearer ${API_KEY}`,
      "Content-Type": "application/json",
    },
  });
  if (!res.ok) {
    throw new Error(`SendGrid ${res.status} on ${path}: ${await res.text()}`);
  }
  return (await res.json()) as T;
};

const listAll = async (): Promise<SGTemplate[]> => {
  const all: SGTemplate[] = [];
  let pageToken: string | undefined;

  do {
    const query = new URLSearchParams({ generations: "dynamic", page_size: "200" });
    if (pageToken) query.set("page_token", pageToken);

    const page = await sg<{ result: SGTemplate[]; _metadata?: { next?: string } }>(
      `/templates?${query.toString()}`,
    );

    all.push(...(page.result ?? []));
    pageToken = page._metadata?.next
      ? (new URL(page._metadata.next).searchParams.get("page_token") ?? undefined)
      : undefined;
  } while (pageToken);

  return all;
};

const sha256 = (s: string) => createHash("sha256").update(s).digest("hex");

// Sequential on purpose. The list endpoint gives version metadata but not
// html_content, so each template costs a second call; hammering that in
// parallel earns a 429 and, per prior incidents in this codebase, concurrency
// against a rate-limited upstream fails in ways sequential access does not.
const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));

async function run() {
  if (!API_KEY) {
    console.error("SENDGRID_API_KEY is not set — nothing to back fill from.");
    process.exit(1);
  }

  console.log(
    APPLY
      ? "▶ BACKFILL — writing to GCS and the database"
      : "▶ DRY RUN — nothing will be written. Pass --apply to commit.",
  );
  console.log(`  database: ${config.database}@${config.host}`);

  const db = createDBPool(config);
  const storage = EDMStorage();

  let templates = await listAll();
  console.log(`  found ${templates.length} dynamic templates in SendGrid`);
  if (LIMIT > 0) {
    templates = templates.slice(0, LIMIT);
    console.log(`  limited to ${templates.length}`);
  }

  const stats = { created: 0, skipped: 0, noVersion: 0, failed: 0 };

  for (const [i, summary] of templates.entries()) {
    const label = `[${i + 1}/${templates.length}] ${summary.id} ${summary.name}`;

    try {
      const full = await sg<SGTemplate>(`/templates/${summary.id}`);
      const version =
        full.versions?.find((v) => v.active === 1) ?? full.versions?.[0];

      if (!version || !version.html_content) {
        console.log(`  ⚠ ${label} — no version with html, skipped`);
        stats.noVersion++;
        continue;
      }

      const html = version.html_content;
      const htmlHash = sha256(html);

      const existing = await db
        .selectFrom("edm_templates as t")
        .leftJoin("edm_template_versions as v", "v.id", "t.active_version_id")
        .select(["t.id", "v.html_sha256"])
        .where("t.id", "=", summary.id)
        .executeTakeFirst();

      if (existing && existing.html_sha256 === htmlHash) {
        stats.skipped++;
        continue;
      }

      const object: EDMTemplateObject = {
        // Subject is the whole reason this backfill cannot be lazy: it exists
        // only on the SendGrid version record, and no caller supplies one.
        subject: version.subject ?? "",
        html,
        plain: version.plain_content ?? "",
        generatePlain: version.generate_plain_content ?? true,
      };

      if (!APPLY) {
        console.log(
          `  · ${label} — would mirror (subject: ${JSON.stringify(object.subject.slice(0, 60))}, ${html.length} bytes)`,
        );
        stats.created++;
        await sleep(60);
        continue;
      }

      const versionId = randomUUID();
      const storagePath = buildVersionObjectPath(summary.id, versionId);

      // GCS first: an unreferenced object is harmless, a row pointing at bytes
      // that do not exist renders a blank email in production.
      await storage.putTemplateVersion(summary.id, versionId, object);

      if (!existing) {
        await db
          .insertInto("edm_templates")
          .values({
            id: summary.id,
            name: full.name,
            generation: "dynamic",
            // Mirrored, but SendGrid still holds the original until we delete
            // it in the final cutover step.
            source: "sendgrid",
          })
          .execute();
      }

      const nextNumber = await db
        .selectFrom("edm_template_versions")
        .select((eb) => eb.fn.max("version_number").as("max"))
        .where("template_id", "=", summary.id)
        .executeTakeFirst();

      await db
        .insertInto("edm_template_versions")
        .values({
          id: versionId,
          template_id: summary.id,
          version_number: Number(nextNumber?.max ?? 0) + 1,
          name: version.name ?? full.name,
          subject: object.subject,
          storage_path: storagePath,
          html_sha256: htmlHash,
          generate_plain_content: object.generatePlain,
          active: 1,
        })
        .execute();

      await db
        .updateTable("edm_template_versions")
        .set({ active: 0 })
        .where("template_id", "=", summary.id)
        .where("id", "!=", versionId)
        .execute();

      await storage.publishVersion(summary.id, versionId);

      await db
        .updateTable("edm_templates")
        .set({ active_version_id: versionId, name: full.name, updated_at: new Date() })
        .where("id", "=", summary.id)
        .execute();

      console.log(`  ✓ ${label}`);
      stats.created++;
      await sleep(60);
    } catch (err) {
      console.error(`  ✗ ${label} — ${(err as Error).message}`);
      stats.failed++;
    }
  }

  console.log("\n── summary ──");
  console.log(`  mirrored:      ${stats.created}`);
  console.log(`  already local: ${stats.skipped}`);
  console.log(`  no html:       ${stats.noVersion}`);
  console.log(`  failed:        ${stats.failed}`);
  if (!APPLY) console.log("\n  DRY RUN — re-run with --apply to commit.");

  await db.destroy();
}

run().catch((err) => {
  console.error(err);
  process.exit(1);
});