/**
 * ATGBICS Detail Page Verifier
 *
 * Lightweight Shopify product.js pass for ATGBICS rows that already have
 * price + image + product URL but still lack detail verification.
 *
 * No browser, no Playwright. Fetches one JSON endpoint per product page.
 */
import { pool } from "../utils/db";

interface TargetRow {
  id: string;
  part_number: string;
  product_page_url: string;
}

interface ShopifyVariant {
  sku?: string;
  price?: number;
}

interface ShopifyProduct {
  title?: string;
  description?: string;
  tags?: string[];
  type?: string;
  vendor?: string;
  variants?: ShopifyVariant[];
  featured_image?: string;
}

interface ExtractedDetails {
  formFactor?: string;
  speed?: string;
  speedGbps?: number;
  reachLabel?: string;
  reachMeters?: number;
  fiberType?: string;
  wavelengths?: string;
  connector?: string;
  category?: string;
  standardName?: string;
}

const HEADERS = {
  "User-Agent": "TIP-ATGBICS-DetailVerifier/1.0 (+https://transceiver-db.context-x.org)",
  Accept: "application/json,text/plain,*/*",
};

function sleep(ms: number): Promise<void> {
  return new Promise((resolve) => setTimeout(resolve, ms));
}

function stripHtml(input: string): string {
  return input.replace(/<[^>]+>/g, " ").replace(/&nbsp;|&amp;|&reg;|®/gi, " ").replace(/\s+/g, " ").trim();
}

function productJsonUrl(productUrl: string): string {
  const clean = productUrl.split("?")[0].replace(/\/$/, "");
  return clean.endsWith(".js") ? clean : `${clean}.js`;
}

function firstTagValue(tags: string[], prefix: string): string | undefined {
  const tag = tags.find((item) => item.toLowerCase().startsWith(prefix.toLowerCase()));
  return tag ? tag.slice(prefix.length).trim() : undefined;
}

function parseDistance(value: string): { label: string; meters: number } | undefined {
  const match = value.match(/(\d+(?:[.,]\d+)?)\s*(km|m)\b/i);
  if (!match) return undefined;
  const amount = parseFloat(match[1].replace(",", "."));
  if (!Number.isFinite(amount) || amount <= 0) return undefined;
  const unit = match[2].toLowerCase();
  const label = `${String(amount).replace(/\.0$/, "")}${unit}`;
  const meters = unit === "km" ? Math.round(amount * 1000) : Math.round(amount);
  return { label, meters };
}

function parseFormFactor(text: string, tags: string[]): string | undefined {
  const productCategory = firstTagValue(tags, "Product Category_")?.toUpperCase();
  if (productCategory === "QSFPP") return "QSFP+";
  if (productCategory === "SFPP") return "SFP+";
  if (productCategory === "QSFPDD") return "QSFP-DD";
  if (productCategory) return productCategory.replace("QSFPDD", "QSFP-DD");

  if (/qsfp-?dd/i.test(text)) return "QSFP-DD";
  if (/\bosfp\b/i.test(text)) return "OSFP";
  if (/qsfp28/i.test(text)) return "QSFP28";
  if (/qsfp56/i.test(text)) return "QSFP56";
  if (/qsfp\+|qsfpp/i.test(text)) return "QSFP+";
  if (/sfp28/i.test(text)) return "SFP28";
  if (/sfp\+|sfpp/i.test(text)) return "SFP+";
  if (/\bsfp\b/i.test(text)) return "SFP";
  return undefined;
}

function parseSpeed(text: string, tags: string[]): { speed?: string; speedGbps?: number } {
  const rate = firstTagValue(tags, "Max Data Rate_") || text;
  const match = rate.match(/(\d+(?:\.\d+)?)\s*(?:g|gbps)\b/i);
  if (!match) return {};
  const speedGbps = Math.round(parseFloat(match[1]));
  if (!Number.isFinite(speedGbps) || speedGbps <= 0) return {};
  return { speed: `${speedGbps}G`, speedGbps };
}

function parseFiber(text: string, tags: string[]): string | undefined {
  const cableType = firstTagValue(tags, "Cable Type_");
  if (cableType) {
    if (/mmf|multi/i.test(cableType)) return "MMF";
    if (/smf|single/i.test(cableType)) return "SMF";
    if (/copper|dac|twinax/i.test(cableType)) return "Copper";
  }

  if (/loopback/i.test(text)) return "N/A";
  if (/copper|dac|twinax|base-t|rj45/i.test(text)) return "Copper";
  if (/mmf|multi[- ]?mode/i.test(text)) return "MMF";
  if (/smf|single[- ]?mode/i.test(text)) return "SMF";
  return undefined;
}

function parseWavelength(text: string, tags: string[], fiberType?: string): string | undefined {
  if (fiberType === "Copper" || fiberType === "N/A") return "N/A";
  const tag = firstTagValue(tags, "Wavelength_");
  const fromTag = tag?.match(/(\d{3,4})\s*nm/i);
  if (fromTag) return fromTag[1];
  const fromText = text.match(/(\d{3,4})\s*nm/i);
  if (fromText) return fromText[1];
  return undefined;
}

function parseConnector(tags: string[]): string | undefined {
  return firstTagValue(tags, "Interface_")?.replace(/\//g, "/");
}

function extractDetails(product: ShopifyProduct): ExtractedDetails | null {
  const tags = product.tags ?? [];
  const title = stripHtml(product.title ?? "");
  const description = stripHtml(product.description ?? "");
  const text = `${title} ${description} ${tags.join(" ")}`;
  const isLoopback = /loopback/i.test(text);
  const distanceTag = firstTagValue(tags, "Max Distance_");
  const distance = (distanceTag ? parseDistance(distanceTag) : undefined) ?? parseDistance(text);
  const fiberType = parseFiber(text, tags);
  const wavelengths = parseWavelength(text, tags, fiberType);
  const speed = parseSpeed(text, tags);

  if (isLoopback) {
    return {
      formFactor: parseFormFactor(text, tags),
      ...speed,
      reachLabel: "N/A",
      reachMeters: 0,
      fiberType: "N/A",
      wavelengths: "N/A",
      connector: parseConnector(tags),
      category: "Loopback / Test Module",
      standardName: title || undefined,
    };
  }

  if (!distance || !fiberType) return null;

  return {
    formFactor: parseFormFactor(text, tags),
    ...speed,
    reachLabel: distance.label,
    reachMeters: distance.meters,
    fiberType,
    wavelengths,
    connector: parseConnector(tags),
    category: fiberType === "Copper" ? "Copper" : "Compatible",
    standardName: title || undefined,
  };
}

async function fetchProduct(url: string): Promise<ShopifyProduct | null> {
  const resp = await fetch(productJsonUrl(url), { headers: HEADERS, signal: AbortSignal.timeout(20000) });
  if (!resp.ok) return null;
  return (await resp.json()) as ShopifyProduct;
}

async function main(): Promise<void> {
  const limit = Number.parseInt(process.env.ATGBICS_DETAIL_LIMIT || "150", 10);
  const result = await pool.query<TargetRow>(`
    SELECT t.id, t.part_number, t.product_page_url
    FROM transceivers t
    JOIN vendors v ON v.id = t.vendor_id
    WHERE v.name = 'ATGBICS'
      AND t.price_verified = true
      AND t.image_verified = true
      AND COALESCE(t.product_page_url, '') != ''
      AND COALESCE(t.details_verified, false) = false
    ORDER BY t.updated_at ASC, t.part_number
    LIMIT $1
  `, [limit]);

  let fetched = 0;
  let updated = 0;
  let skipped = 0;

  for (const row of result.rows) {
    const product = await fetchProduct(row.product_page_url);
    fetched++;
    if (!product) {
      skipped++;
      await sleep(250);
      continue;
    }

    const details = extractDetails(product);
    if (!details) {
      skipped++;
      await sleep(250);
      continue;
    }

    const update = await pool.query(`
      UPDATE transceivers
      SET form_factor = COALESCE(NULLIF($2::text, ''), form_factor),
          speed = COALESCE(NULLIF($3::text, ''), speed),
          speed_gbps = COALESCE($4::numeric, speed_gbps),
          reach_label = $5,
          reach_meters = $6,
          fiber_type = $7,
          wavelengths = COALESCE(NULLIF($8::text, ''), wavelengths),
          connector = COALESCE(NULLIF($9::text, ''), connector),
          category = COALESCE(NULLIF($10::text, ''), category),
          standard_name = COALESCE(NULLIF(standard_name, ''), NULLIF($11::text, '')),
          details_verified = true,
          details_verified_at = COALESCE(details_verified_at, NOW()),
          details_source_url = COALESCE(NULLIF(details_source_url, ''), product_page_url),
          data_confidence = CASE
            WHEN COALESCE(data_confidence, 'unknown') IN ('unknown', 'enriched_estimated')
              THEN 'scraped_unverified'
            ELSE data_confidence
          END,
          notes = CONCAT_WS(' | ', NULLIF(notes, ''), 'ATGBICS product.js detail verifier 2026-05-09'),
          updated_at = NOW()
      WHERE id = $1
        AND COALESCE(details_verified, false) = false
      RETURNING id
    `, [
      row.id,
      details.formFactor || null,
      details.speed || null,
      details.speedGbps || null,
      details.reachLabel,
      details.reachMeters,
      details.fiberType,
      details.wavelengths || null,
      details.connector || null,
      details.category || null,
      details.standardName || null,
    ]);

    if ((update.rowCount ?? 0) > 0) updated++;
    else skipped++;

    if (fetched % 25 === 0) {
      console.log(`[ATGBICS details] fetched=${fetched} updated=${updated} skipped=${skipped}`);
    }
    await sleep(250);
  }

  const promoted = await pool.query(`
    UPDATE transceivers
    SET fully_verified = true,
        fully_verified_at = COALESCE(fully_verified_at, NOW())
    WHERE price_verified = true
      AND image_verified = true
      AND details_verified = true
      AND competitor_verified = true
      AND COALESCE(fully_verified, false) = false
    RETURNING id
  `);

  console.log(`[ATGBICS details] done fetched=${fetched} updated=${updated} skipped=${skipped} promoted=${promoted.rowCount ?? 0}`);
}

if (require.main === module) {
  main()
    .then(() => pool.end())
    .catch((err) => {
      console.error("Fatal:", err);
      pool.end().finally(() => process.exit(1));
    });
}
