fuckingtech/_spec/ai_resources.ts

import { Kysely } from "@kysely/kysely";
import { parse as parseCsv } from "@std/csv/parse";
import { Public } from "@/default.sqlite.d.ts";
import { database, s3Client } from "@/app/utils/database.ts";

// --- NEW IMPORTS ---
import puppeteer, { Browser } from "npm:puppeteer";
import { clickCmp } from "npm:puppeteer-cmp-clicker";
import { join } from "@std/path";

const S3_MEDIA_PREFIX = "medias/resources/ai-gf-web-screenshots";

// Define the type for the S3 object metadata, based on your snippet
type S3ObjectStatus = {
  type: "Object";
  key: string;
  lastModified: Date | undefined;
  size: number | undefined;
  etag: string | undefined;
  "content-type": string;
  metadata: Record<string, any>;
};

// --- HELPER FUNCTIONS ---

function slugify(text: string): string {
  if (!text) return "";
  return text
    .toString()
    .toLowerCase()
    .trim()
    .replace(/\s+/g, "-") // Replace spaces with -
    .replace(/[^\w\-]+/g, "") // Remove all non-word chars
    .replace(/\-\-+/g, "-"); // Replace multiple - with single -
}

/**
 * Takes a screenshot of a URL, uploads it to S3, and returns the S3 key.
 * Returns null if the process fails.
 */
async function takeAndUploadScreenshot(
  browser: Browser,
  url: string,
  resourceName: string,
): Promise<string | null> {
  if (!url || !url.startsWith("http")) {
    console.warn(`  - Invalid URL for screenshot: ${url}`);
    return null;
  }

  const page = await browser.newPage();
  try {
    console.log(`  - Navigating to ${url} for screenshot...`);
    await page.setViewport({ width: 1280, height: 720 });
    await page.goto(url, { waitUntil: "networkidle2", timeout: 45000 });
    const res = await clickCmp({ page });
    await page.evaluate((_) => {
      function xcc_contains(selector, text) {
        var elements = document.querySelectorAll(selector);
        return Array.prototype.filter.call(elements, function (element) {
          return RegExp(text, "ig").test(element.textContent.trim());
        });
      }
      var _xcc;
      _xcc = xcc_contains(
        "[id*=cookie] a, [class*=cookie] a, [id*=cookie] button, [class*=cookie] button",
        "^(Alle akzeptieren|Akzeptieren|Verstanden|Zustimmen|Okay|OK|Yes|Y|Accept|Accept All|Oui)$",
      );
      if (_xcc != null && _xcc.length != 0) _xcc[0].click();
    });

    const screenshotBuffer = await page.screenshot({
      type: "jpeg",
      quality: 90,
    });
    const screenshotFilename = `${slugify(resourceName)}-screenshot.jpeg`;

    const key = join(
      S3_MEDIA_PREFIX,
      screenshotFilename,
    );

    console.log(`  - Uploading screenshot to S3 at key: ${key}`);

    await s3Client.putObject(key, screenshotBuffer, {
      metadata: { "Content-Type": "image/jpeg" },
    });

    return key;
  } catch (error) {
    console.error(
      `  - !!! Failed to take/upload screenshot for ${url}:`,
      error.message,
    );
    return null;
  } finally {
    await page.close();
  }
}

/**
 * Fetches metadata for a list of S3 keys.
 */
const getS3ObjectsFromKeys = async (
  imageKeys: string[],
): Promise<S3ObjectStatus[]> => {
  const imageMetadataPromises = imageKeys.map(
    async (key): Promise<S3ObjectStatus | null> => {
      try {
        console.log("stat", key);
        const data = await s3Client.statObject(key);
        const formattedData = {
          ...data,
          "content-type": key.endsWith(".jpg") ? "image/jpeg" : "image/png",
          metadata: {
            ...(data.metadata ?? {}),
            "x-amz-meta-alt": data.metadata?.["x-amz-meta-alt"],
          },
        };
        return {
          type: "Object",
          key: formattedData.key,
          lastModified: formattedData.lastModified,
          size: formattedData.size,
          etag: formattedData.etag,
          versionId: formattedData.versionId,
          "content-type": formattedData["content-type"],
          metadata: formattedData.metadata,
        } as S3ObjectStatus;
      } catch (error) {
        console.error(
          `  - !!! Failed to get S3 metadata for key ${key}:`,
          error.message,
        );
        return null; // Return null if a single key fails
      }
    },
  );

  const imageMetadataResults = await Promise.all(imageMetadataPromises);
  // Filter out any null results from failed metadata fetches
  return imageMetadataResults.filter((meta): meta is S3ObjectStatus =>
    meta !== null
  );
};

// --- ORIGINAL SCRIPT (ADAPTED) ---

const CSV_FILE_PATH = "./_spec/ai-gf-inventory-full-ai-gf-inventory-full.csv";
const DEFAULT_ADDED_BY_USER_ID = 1;
const DEFAULT_RESOURCE_TYPE_NAME = "AI Tool";

const TAG_CATEGORIES = {
  COUNTRY: "Country",
  LANGUAGE: "Language",
  PLATFORM: "Platform",
  FEATURE: "Feature",
  INTERFACE_STYLE: "Interface Style",
  TOU_SUMMARY: "Terms of Use Summary",
};

const CSV_COLUMNS = [
  "name",
  "link",
  "company",
  "year",
  "country",
  "language",
  "marketplace",
  "blurb",
  "pricing_text",
  "base_price_str",
  "features",
  "interface_style",
  "tou_summary",
  "tou_details",
] as const;

type CsvRow = Record<typeof CSV_COLUMNS[number], string | null>;

async function importAiTools(db: Kysely<Public>) {
  console.log(`Starting import from ${CSV_FILE_PATH}...`);

  // --- Launch Puppeteer browser once for efficiency ---
  console.log("Launching browser...");
  const browser = await puppeteer.launch({
    headless: true,
    args: ["--no-sandbox", "--disable-setuid-sandbox"], // Good practice for server environments
  });

  try {
    const categoryIds = await getOrCreateTagCategories(db, TAG_CATEGORIES);
    const defaultResourceTypeId = await getResourceTypeId(
      db,
      DEFAULT_RESOURCE_TYPE_NAME,
    );

    const csvContent = await Deno.readTextFile(CSV_FILE_PATH);
    const parsedData = parseCsv(csvContent, {
      startLine: 3,
      separator: ",",
      columns: CSV_COLUMNS,
      lazyQuotes: true,
    }) as CsvRow[];

    console.log(`Parsed ${parsedData.length} data rows.`);
    let successCount = 0;
    let errorCount = 0;

    for (let i = 0; i < parsedData.length; i++) {
      const row = parsedData[i];
      const rowNum = i + 3;

      const cleanedName = (row.name || "").replace(/^\[x\]\s*/, "").trim();
      console.log(`\n--- Processing Row ${rowNum}: ${cleanedName} ---`);

      if (!cleanedName || !row.company) {
        console.warn(`Skipping row ${rowNum}: Missing 'Name' or 'Company'.`);
        errorCount++;
        continue;
      }

      // --- Screenshot and S3 Upload Logic ---
      let imagesJson: string | undefined = undefined;
      const validUrl = row.link?.trim();
      if (validUrl) {
        const screenshotKey = await takeAndUploadScreenshot(
          browser,
          validUrl,
          cleanedName,
        );
        if (screenshotKey) {
          const imageMetadata = await getS3ObjectsFromKeys([screenshotKey]);
          if (imageMetadata.length > 0) {
            imagesJson = JSON.stringify(imageMetadata);
          }
        }
      }

      const authorId = await getOrCreatePerson(db, row.company!.trim());
      const basePrice = row.base_price_str
        ? parseFloat(row.base_price_str.replace(",", "."))
        : null;

      try {
        const resourceInsertResult = await db.insertInto("resources")
          .values({
            title: cleanedName,
            url: row.link?.trim() || undefined,
            author_id: authorId,
            resource_type_id: defaultResourceTypeId,
            publication_date: row.year?.trim() || undefined,
            added_by: Math.random() > 0.5 ? 1 : 2,
            description_html: (await Promise.try(() =>
              JSON.parse(row.blurb) || row.blurb
            ).catch(() => row.blurb)) || undefined,
            notes_html: (await Promise.try(() =>
              JSON.parse(row.tou_details) || row.tou_details
            ).catch(() => row.tou_details)) || undefined,
            hidden: 0,
            order: 0,
            base_price: isNaN(basePrice as number) ? undefined : basePrice,
            images_json: imagesJson, // Add the new field here
          })
          .returning("id")
          .executeTakeFirstOrThrow();

        const resourceId = resourceInsertResult.id;
        console.log(`  Resource inserted with ID: ${resourceId}`);

        // Tag processing remains the same

        await processTags(
          db,
          resourceId,
          row.country,
          categoryIds[TAG_CATEGORIES.COUNTRY],
        );
        await processTags(
          db,
          resourceId,
          row.language,
          categoryIds[TAG_CATEGORIES.LANGUAGE],
        );
        await processTags(
          db,
          resourceId,
          row.marketplace,
          categoryIds[TAG_CATEGORIES.PLATFORM],
        );
        await processTags(
          db,
          resourceId,
          row.features,
          categoryIds[TAG_CATEGORIES.FEATURE],
        );
        await processTags(
          db,
          resourceId,
          row.interface_style,
          categoryIds[TAG_CATEGORIES.INTERFACE_STYLE],
        );
        await processTags(
          db,
          resourceId,
          row.tou_summary,
          categoryIds[TAG_CATEGORIES.TOU_SUMMARY],
        );

        successCount++;
        console.log(`--- Successfully processed Row ${rowNum} ---`);
      } catch (err) {
        errorCount++;
        console.error(
          `!!! Error processing Row ${rowNum} (${cleanedName}):`,
          err,
        );
      }
    }

    console.log(`\n--- Import Complete ---`);
    console.log(`Successfully processed: ${successCount} rows`);
    console.log(`Failed/Skipped:       ${errorCount} rows`);
  } finally {
    // --- Ensure the browser is always closed ---
    await browser.close();
    console.log("Browser closed.");
  }
}

async function getOrCreateTagCategories(
  db: Kysely<Public>,
  categories: Record<string, string>,
) {
  const categoryIds: Record<string, number> = {};
  for (const categoryName of Object.values(categories)) {
    let category = await db.selectFrom("tag_categories")
      .select("id")
      .where("name", "=", categoryName)
      .executeTakeFirst();

    if (!category) {
      const insertResult = await db.insertInto("tag_categories")
        .values({ name: categoryName })
        .returning("id")
        .executeTakeFirstOrThrow();
      category = { id: insertResult.id };
    }
    categoryIds[categoryName] = category.id;
  }
  return categoryIds;
}

async function getResourceTypeId(db: Kysely<Public>, resourceTypeName: string) {
  return db.selectFrom("resource_types")
    .select("id")
    .where("name_en", "=", resourceTypeName)
    .executeTakeFirstOrThrow()
    .then((result) => result.id);
}

async function getOrCreatePerson(db: Kysely<Public>, companyName: string) {
  let author = await db.selectFrom("persons")
    .select("id")
    .where("name", "=", companyName)
    .executeTakeFirst();

  if (!author) {
    const personInsertResult = await db.insertInto("persons")
      .values({ id: null as never, name: companyName })
      .returning("id")
      .executeTakeFirstOrThrow();
    author = { id: personInsertResult.id };
  }
  return author.id;
}

async function processTags(
  db: Kysely<Public>,
  resourceId: number,
  tagString: string | null | undefined,
  categoryId: number,
) {
  if (
    !tagString || tagString.trim() === "" ||
    tagString.trim().toLowerCase() === "unknown"
  ) return;

  // This regex correctly handles comma-separated values that might be inside quotes.
  const tagRegex = /(?:[^,"']|"(?:\\.|[^"\\])*"|'(?:\\.|[^'\\])*')+/g;
  const tags =
    tagString.match(tagRegex)?.map((tag) =>
      tag.trim().replace(/^["']|["']$/g, "") // remove quotes from start/end
    ).filter(Boolean) || []; // filter out any empty strings that might result

  if (tags.length === 0) return;

  console.log(
    `  Processing ${tags.length} tags for category ID ${categoryId}...`,
  );

  // Collect tag values for batch insert
  const tagValues = tags.map((tagName) => ({
    name: tagName,
    tag_category_id: categoryId,
  }));

  await db.insertInto("tags")
    .values(tagValues)
    .onConflict((oc) => oc.columns(["name", "tag_category_id"]).doNothing())
    .execute();

  const insertedTags = await db.selectFrom("tags")
    .select(["id", "name"])
    .where("name", "in", tags)
    .where("tag_category_id", "=", categoryId)
    .execute();

  const tagIdMap = new Map(insertedTags.map((tag) => [tag.name, tag.id]));

  // Filter out any tags that might not have been inserted or found
  const resourceTagValues = tags
    .map((tagName) => ({
      resource_id: resourceId,
      tag_id: tagIdMap.get(tagName),
    }))
    .filter((v): v is { resource_id: number; tag_id: number } =>
      v.tag_id !== undefined
    );

  if (resourceTagValues.length > 0) {
    await db.insertInto("resource_tags")
      .values(resourceTagValues)
      .onConflict((oc) => oc.columns(["resource_id", "tag_id"]).doNothing())
      .execute();
  }
}

if (import.meta.main) {
  importAiTools(database)
    .then(() => {
      console.log("Script finished.");
      Deno.exit(0);
    })
    .catch((err) => {
      console.error("Script failed:", err);
      Deno.exit(1);
    });
}