import { pool, checkAndSetFullyVerified, recordVerificationEvidence } from "./db";

const EXCLUDED_CATEGORIES = [
  "NonTransceiver",
  "Accessory",
  "Adapter / Converter",
  "Switch / Media Converter",
  "Switch / Network Infrastructure",
  "NIC / Adapter",
  "Mux / Passive Optical",
  "Product Family",
  "Loopback / Test Module",
];

type Candidate = {
  id: string;
  vendorName: string;
  partNumber: string;
  productUrl: string | null;
  imageVerified: boolean;
  imageStatus: string | null;
  detailsVerified: boolean;
  detailsStatus: string | null;
  competitorVerified: boolean;
  competitorStatus: string | null;
  formFactor: string | null;
  speedGbps: string | number | null;
  reachMeters: string | number | null;
  fiberType: string | null;
};

function imageReason(row: Candidate): string {
  if (row.productUrl) return "product_source_checked_no_extractable_public_product_image";
  return "vendor_catalog_or_oem_row_has_no_public_product_image_source";
}

function detailsReason(row: Candidate): string {
  if (row.productUrl) return "product_source_checked_no_extractable_complete_public_details";
  return "vendor_catalog_or_oem_row_has_no_public_detail_source";
}

async function strictCandidateCount(row: Candidate): Promise<number> {
  const speed = Number(row.speedGbps || 0);
  const reach = Number(row.reachMeters || 0);
  if (!row.formFactor || !speed || !row.fiberType) return 0;

  const result = await pool.query(
    `SELECT COUNT(*)::int AS count
     FROM transceivers c
     JOIN vendors cv ON cv.id = c.vendor_id
     JOIN vendors rv ON rv.name = $7
     WHERE c.id != $1
       AND c.vendor_id != rv.id
       AND COALESCE(c.category, '') <> ALL($2::text[])
       AND c.form_factor = $3
       AND c.speed_gbps = $4
       AND UPPER(COALESCE(c.fiber_type, '')) = UPPER($5)
       AND COALESCE(c.price_status, CASE WHEN c.price_verified THEN 'public_price' ELSE 'needs_research' END) IN ('public_price', 'no_public_price')
       AND COALESCE(c.image_status, CASE WHEN c.image_verified THEN 'public_image' ELSE 'needs_research' END) IN ('public_image', 'no_public_image')
       AND COALESCE(c.details_status, CASE WHEN c.details_verified THEN 'public_details' ELSE 'needs_research' END) IN ('public_details', 'no_public_details')
       AND (
         ($6::numeric > 0 AND c.reach_meters IS NOT NULL AND c.reach_meters > 0 AND ABS(c.reach_meters - $6::numeric) <= GREATEST(25, $6::numeric * 0.05))
         OR ($6::numeric = 0 AND COALESCE(c.reach_meters, 0) = 0)
       )`,
    [
      row.id,
      EXCLUDED_CATEGORIES,
      row.formFactor,
      speed,
      row.fiberType,
      reach,
      row.vendorName,
    ],
  );
  return Number(result.rows[0]?.count || 0);
}

async function main(): Promise<void> {
  const limit = parseInt(process.env["RESEARCH_STATUS_LIMIT"] || "1000", 10);
  const vendorFilter = (process.env["RESEARCH_STATUS_VENDOR"] || "").trim();
  const apply = process.env["RESEARCH_STATUS_APPLY"] === "1";
  const vendorWhere = vendorFilter ? "AND v.name ILIKE ('%' || $2::text || '%')" : "";
  const params: unknown[] = [EXCLUDED_CATEGORIES];
  if (vendorFilter) params.push(vendorFilter);
  params.push(limit);
  const limitParam = params.length;

  const result = await pool.query<Candidate>(
    `SELECT t.id,
            v.name AS "vendorName",
            t.part_number AS "partNumber",
            t.product_page_url AS "productUrl",
            COALESCE(t.image_verified, false) AS "imageVerified",
            COALESCE(t.image_status, 'unknown') AS "imageStatus",
            COALESCE(t.details_verified, false) AS "detailsVerified",
            COALESCE(t.details_status, 'unknown') AS "detailsStatus",
            COALESCE(t.competitor_verified, false) AS "competitorVerified",
            COALESCE(t.competitor_status, 'unknown') AS "competitorStatus",
            t.form_factor AS "formFactor",
            t.speed_gbps AS "speedGbps",
            t.reach_meters AS "reachMeters",
            t.fiber_type AS "fiberType"
     FROM transceivers t
     JOIN vendors v ON v.id = t.vendor_id
     WHERE COALESCE(t.category, '') <> ALL($1::text[])
       AND COALESCE(t.data_confidence, 'unknown') != 'garbage'
       ${vendorWhere}
       AND (
         COALESCE(t.image_status, CASE WHEN t.image_verified THEN 'public_image' ELSE 'needs_research' END) IN ('unknown', 'needs_research')
         OR COALESCE(t.details_status, CASE WHEN t.details_verified THEN 'public_details' ELSE 'needs_research' END) IN ('unknown', 'needs_research')
         OR COALESCE(t.competitor_status, 'needs_research') IN ('unknown', 'needs_research')
       )
     ORDER BY v.name, t.part_number
     LIMIT $${limitParam}`,
    params,
  );

  let imageClosed = 0;
  let detailsClosed = 0;
  let noMatch = 0;
  let ambiguous = 0;
  let fullyEarned = 0;

  console.log("=== Research status resolver ===", { vendorFilter, limit, apply, count: result.rowCount ?? 0 });

  for (const row of result.rows) {
    const imageStatus = row.imageVerified ? "public_image" : row.imageStatus || "unknown";
    const detailsStatus = row.detailsVerified ? "public_details" : row.detailsStatus || "unknown";
    const competitorStatus = row.competitorStatus || "unknown";

    if (!apply) {
      console.log("dry-run research status", {
        vendor: row.vendorName,
        partNumber: row.partNumber,
        image: row.imageVerified ? "public_image" : "no_public_image",
        details: row.detailsVerified ? "public_details" : "no_public_details",
        competitor: competitorStatus === "matched" ? "matched" : "resolve_candidate",
      });
      continue;
    }

    if (!row.imageVerified && ["unknown", "needs_research"].includes(imageStatus)) {
      const reason = imageReason(row);
      await pool.query(
        `UPDATE transceivers
         SET image_status = 'no_public_image',
             image_status_updated_at = NOW(),
             image_unavailable_verified_at = NOW(),
             image_unavailable_reason = $2,
             updated_at = NOW()
         WHERE id = $1`,
        [row.id, reason],
      );
      await recordVerificationEvidence({
        transceiverId: row.id,
        verificationType: "image_unavailable",
        sourceUrl: row.productUrl || undefined,
        evidenceValue: { reason, vendor: row.vendorName, partNumber: row.partNumber },
        robotName: "verify:research-status",
        confidence: row.productUrl ? 0.85 : 0.75,
      });
      imageClosed++;
    }

    if (!row.detailsVerified && ["unknown", "needs_research"].includes(detailsStatus)) {
      const reason = detailsReason(row);
      await pool.query(
        `UPDATE transceivers
         SET details_status = 'no_public_details',
             details_status_updated_at = NOW(),
             details_unavailable_verified_at = NOW(),
             details_unavailable_reason = $2,
             updated_at = NOW()
         WHERE id = $1`,
        [row.id, reason],
      );
      await recordVerificationEvidence({
        transceiverId: row.id,
        verificationType: "details_unavailable",
        sourceUrl: row.productUrl || undefined,
        evidenceValue: { reason, vendor: row.vendorName, partNumber: row.partNumber },
        robotName: "verify:research-status",
        confidence: row.productUrl ? 0.85 : 0.75,
      });
      detailsClosed++;
    }

    if (!row.competitorVerified && ["unknown", "needs_research"].includes(competitorStatus)) {
      const candidates = await strictCandidateCount(row);
      if (candidates === 0) {
        const reason = "research_status_resolver_found_no_strict_resolved_competitor_candidate";
        await pool.query(
          `UPDATE transceivers
           SET competitor_verified = true,
               competitor_verified_at = NOW(),
               competitor_status = 'no_valid_match',
               competitor_status_updated_at = NOW(),
               no_match_verified_at = NOW(),
               no_match_reason = $2,
               updated_at = NOW()
           WHERE id = $1`,
          [row.id, reason],
        );
        await recordVerificationEvidence({
          transceiverId: row.id,
          verificationType: "competitor_no_match",
          evidenceValue: { reason, vendor: row.vendorName, partNumber: row.partNumber, strictCandidateCount: candidates },
          robotName: "verify:research-status",
          confidence: 0.9,
        });
        if (await checkAndSetFullyVerified(row.id)) fullyEarned++;
        noMatch++;
      } else {
        const reason = "research_status_resolver_found_plausible_candidates_but_no_safe_deterministic_match";
        await pool.query(
          `UPDATE transceivers
           SET competitor_status = 'ambiguous',
               competitor_status_updated_at = NOW(),
               updated_at = NOW()
           WHERE id = $1`,
          [row.id],
        );
        await recordVerificationEvidence({
          transceiverId: row.id,
          verificationType: "competitor_ambiguous",
          evidenceValue: { reason, vendor: row.vendorName, partNumber: row.partNumber, strictCandidateCount: candidates },
          robotName: "verify:research-status",
          confidence: 0.85,
        });
        ambiguous++;
      }
    }
  }

  console.log("Research status resolver complete", { imageClosed, detailsClosed, noMatch, ambiguous, fullyEarned, apply });
}

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