/**
 * FS.com numeric SKU alias quarantine.
 *
 * FS.com pages expose both a marketplace SKU (FS-123456) and a real optical
 * product P/N (for example OSFP-DR8-1.6T-FL). Older scraper passes created
 * duplicate active rows for the numeric SKU. Those rows distort equivalence
 * research because the real product row already exists for the same URL.
 *
 * Default is dry-run. Set FS_SKU_ALIAS_APPLY=1 to write quarantine/evidence.
 */
import { pool, recordVerificationEvidence } from "./db";
import { logger } from "./logger";

type AliasCandidate = {
  alias_id: string;
  alias_part_number: string;
  alias_url: string;
  canonical_id: string;
  canonical_part_number: string;
  canonical_url: string;
};

function applyMode(): boolean {
  return process.env.FS_SKU_ALIAS_APPLY === "1";
}

async function quarantineFsSkuAliases(): Promise<void> {
  const limit = Math.max(1, parseInt(process.env.FS_SKU_ALIAS_LIMIT || "500", 10));
  const apply = applyMode();

  logger.info("=== FS.com numeric SKU alias quarantine ===", { limit, apply });

  const result = await pool.query<AliasCandidate>(`
    WITH fs AS (
      SELECT
        t.*,
        regexp_replace(
          regexp_replace(COALESCE(t.product_page_url, ''), '\\?.*$', ''),
          '^https://www\\.fs\\.com/de/de/',
          'https://www.fs.com/de/'
        ) AS norm_url
      FROM transceivers t
      JOIN vendors v ON v.id = t.vendor_id
      WHERE v.name = 'FS.COM'
        AND COALESCE(t.product_page_url, '') ~* 'fs\\.com/.*/products/[0-9]+\\.html'
    ),
    numeric_alias AS (
      SELECT *
      FROM fs
      WHERE part_number ~ '^FS-[0-9]+$'
        AND COALESCE(data_confidence, 'unknown') != 'garbage'
    ),
    canonical AS (
      SELECT *
      FROM fs
      WHERE part_number !~ '^FS-[0-9]+$'
        AND COALESCE(data_confidence, 'unknown') != 'garbage'
        AND price_verified = true
        AND image_verified = true
        AND details_verified = true
    )
    SELECT DISTINCT ON (n.id)
      n.id AS alias_id,
      n.part_number AS alias_part_number,
      n.product_page_url AS alias_url,
      c.id AS canonical_id,
      c.part_number AS canonical_part_number,
      c.product_page_url AS canonical_url
    FROM numeric_alias n
    JOIN canonical c ON c.norm_url = n.norm_url
    ORDER BY n.id, c.fully_verified DESC, c.updated_at DESC
    LIMIT $1
  `, [limit]);

  logger.info("FS.com numeric SKU alias candidates", { count: result.rowCount ?? 0 });

  if (!apply) {
    for (const row of result.rows.slice(0, 40)) {
      logger.info("dry-run alias", {
        alias: row.alias_part_number,
        canonical: row.canonical_part_number,
        url: row.alias_url,
      });
    }
    return;
  }

  let updated = 0;
  for (const row of result.rows) {
    const reason = [
      "FS.com numeric marketplace SKU alias",
      `${row.alias_part_number} duplicates canonical product P/N ${row.canonical_part_number}`,
      "for the same normalized FS product URL",
    ].join("; ");

    await recordVerificationEvidence({
      transceiverId: row.alias_id,
      verificationType: "artifact_quarantine",
      sourceUrl: row.alias_url,
      evidenceValue: {
        reason,
        aliasPartNumber: row.alias_part_number,
        canonicalTransceiverId: row.canonical_id,
        canonicalPartNumber: row.canonical_part_number,
        canonicalUrl: row.canonical_url,
      },
      robotName: "verify:fs:sku-aliases",
      confidence: 1,
    });

    const update = await pool.query(`
      UPDATE transceivers
      SET category = 'NonTransceiver',
          price_verified = false,
          image_verified = false,
          details_verified = false,
          competitor_verified = false,
          fully_verified = false,
          competitor_status = 'needs_research',
          competitor_status_updated_at = NOW(),
          notes = CASE
            WHEN COALESCE(notes, '') ILIKE '%' || $2::text || '%' THEN notes
            ELSE CONCAT_WS(E'\\n', NULLIF(notes, ''), $2::text)
          END,
          updated_at = NOW()
      WHERE id = $1
        AND COALESCE(category, '') != 'NonTransceiver'
      RETURNING id
    `, [row.alias_id, reason]);

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

  logger.info("FS.com numeric SKU alias quarantine complete", { updated });
}

if (require.main === module) {
  quarantineFsSkuAliases()
    .then(() => pool.end())
    .catch((err) => {
      logger.error("FS.com numeric SKU alias quarantine failed", { error: (err as Error).message });
      pool.end();
      process.exit(1);
    });
}
