/**
 * Catalog Reconcile Robot
 *
 * Vollständiger Bulk-Abgleich Flexoptix ↔ ALLE Wettbewerber.
 *
 * Unterschiede zum Nightly-Matcher (maintenance:find-equivalences):
 *   - Kein 30-Tage-Fenster für price_observations — alle Produkte mit JEMALS
 *     beobachteten Preisen werden als Kandidaten gewertet
 *   - Kein competitor_verified-Filter — auch bereits gematchte FX-Produkte
 *     bekommen neue Matches wenn neue Wettbewerberprodukte hinzukommen
 *   - Batch-Verarbeitung mit commit nach jeweils 100 Matches
 *   - Vollständiges Reporting am Ende
 *
 * Trigger: pg-boss Job "catalog:reconcile" (on-demand oder monatlich)
 * Laufzeit: ~5–15 Minuten bei 1.000+ FX-Produkten
 */

import { pool } from "../utils/db";

export interface ReconcileResult {
  flexoptixProcessed: number;
  newAutoApproved: number;
  newPending: number;
  skippedLowConfidence: number;
  skippedAlreadyMatched: number;
  vendorBreakdown: Record<string, { autoApproved: number; pending: number }>;
  durationMs: number;
}

// ── Konfigurations-Konstanten ────────────────────────────────────────────────

/** Minimum-Confidence für pending-Eintrag (unter diesem Schwellwert: ignorieren) */
const CONFIDENCE_MIN = 0.50;

/** Confidence-Schwellwert für auto_approved */
const CONFIDENCE_AUTO_APPROVE = 0.73;

/**
 * Maximale Anzahl Tage seit letzter price_observation.
 * NULL = kein Filter (alle Produkte mit mind. 1 Observation).
 * Für Full-Reconcile: NULL.
 */
const MAX_PRICE_AGE_DAYS: number | null = null;

// ── Helper: Wellenlänge aus Text extrahieren ────────────────────────────────

function extractFirstNm(wavelengths: string | null): number | null {
  if (!wavelengths) return null;
  const m = wavelengths.match(/(\d{3,4})/);
  return m ? parseInt(m[1], 10) : null;
}

// ── Haupt-Matching-Logik (identisch mit Nightly-Matcher) ────────────────────

function calcConfidence(
  fx: { standard_name: string | null; fiber_type: string | null; reach_meters: number | null; wavelengths: string | null },
  cand: { standard_name: string | null; fiber_type: string | null; reach_meters: number | null; wavelengths: string | null }
): { confidence: number; basis: string[] } {
  // Max-Score: form_factor(25) + speed_gbps(20) + standard_name(30) +
  //            wavelength_nm(20) + fiber_type(10) + reach(10) = 115
  // Beide form_factor und speed_gbps sind bereits durch den SQL-Filter gesichert.
  let score = 0;
  const basis: string[] = [];

  score += 25; basis.push("form_factor");
  score += 20; basis.push("speed_gbps");

  if (
    fx.standard_name && cand.standard_name &&
    fx.standard_name.trim().toUpperCase() === cand.standard_name.trim().toUpperCase()
  ) {
    score += 30; basis.push("standard_name");
  }

  const fxNm = extractFirstNm(fx.wavelengths);
  const candNm = extractFirstNm(cand.wavelengths);
  if (fxNm !== null && candNm !== null) {
    if (Math.abs(fxNm - candNm) <= 15) {
      score += 20; basis.push(`wavelength_${fxNm}nm`);
    } else {
      score -= 20;
    }
  }

  if (fx.fiber_type && cand.fiber_type) {
    if (fx.fiber_type.trim().toUpperCase() === cand.fiber_type.trim().toUpperCase()) {
      score += 10; basis.push("fiber_type");
    } else {
      score -= 15;
    }
  }

  if (fx.reach_meters && cand.reach_meters && fx.reach_meters > 0 && cand.reach_meters > 0) {
    const diff = Math.abs(fx.reach_meters - cand.reach_meters);
    const tolerance = Math.max(fx.reach_meters, 1) * 0.25;
    if (diff <= tolerance) {
      score += 10; basis.push("reach");
    } else {
      score -= 15;
    }
  } else if (!fx.reach_meters && !cand.reach_meters) {
    score += 5; basis.push("reach_null");
  }

  const confidence = Math.max(0, Math.min(1, score / 115));
  return { confidence, basis };
}

// ── Haupt-Funktion ───────────────────────────────────────────────────────────

export async function runCatalogReconcile(): Promise<ReconcileResult> {
  const startMs = Date.now();
  console.log("=== Catalog Reconcile Robot ===");
  console.log(`  Started: ${new Date().toISOString()}`);
  console.log(`  Mode: FULL (no 30-day window, all vendors)`);

  const result: ReconcileResult = {
    flexoptixProcessed: 0,
    newAutoApproved: 0,
    newPending: 0,
    skippedLowConfidence: 0,
    skippedAlreadyMatched: 0,
    vendorBreakdown: {},
    durationMs: 0,
  };

  // ── Alle Flexoptix-Produkte laden ─────────────────────────────────────────
  // Kein competitor_verified-Filter → wir reconcilen ALLES
  const { rows: fxProducts } = await pool.query<{
    id: string;
    part_number: string;
    standard_name: string | null;
    form_factor: string | null;
    speed_gbps: string | null;
    fiber_type: string | null;
    reach_meters: number | null;
    wavelengths: string | null;
  }>(`
    SELECT t.id, t.part_number, t.standard_name, t.form_factor,
           t.speed_gbps, t.fiber_type, t.reach_meters, t.wavelengths
    FROM transceivers t
    JOIN vendors v ON v.id = t.vendor_id
    WHERE UPPER(v.name) LIKE '%FLEXOPTIX%'
      AND t.form_factor IS NOT NULL
      AND t.speed_gbps IS NOT NULL
    ORDER BY t.part_number
  `);

  result.flexoptixProcessed = fxProducts.length;
  console.log(`  Flexoptix products to process: ${fxProducts.length}`);

  const priceAgeFilter = MAX_PRICE_AGE_DAYS !== null
    ? `AND po.time > NOW() - INTERVAL '${MAX_PRICE_AGE_DAYS} days'`
    : "";

  for (const fx of fxProducts) {
    if (!fx.form_factor || !fx.speed_gbps) continue;

    // ── Wettbewerber-Kandidaten für dieses FX-Produkt ──────────────────────
    // Kandidaten = alle Wettbewerber mit gleichem form_factor und speed_gbps
    // die mindestens 1 price_observation haben (kein Zeitlimit)
    const { rows: candidates } = await pool.query<{
      competitor_id: string;
      part_number: string;
      standard_name: string | null;
      form_factor: string | null;
      speed_gbps: string | null;
      fiber_type: string | null;
      reach_meters: number | null;
      wavelengths: string | null;
      vendor_name: string;
      last_price: Date | null;
      price_count: string;
    }>(`
      SELECT t.id AS competitor_id, t.part_number, t.standard_name,
             t.form_factor, t.speed_gbps, t.fiber_type, t.reach_meters,
             t.wavelengths, v.name AS vendor_name,
             MAX(po.time) AS last_price, COUNT(*) AS price_count
      FROM transceivers t
      JOIN vendors v ON v.id = t.vendor_id
      JOIN price_observations po ON po.transceiver_id = t.id
      WHERE UPPER(v.name) NOT LIKE '%FLEXOPTIX%'
        AND v.is_competitor = true
        ${priceAgeFilter}
        AND UPPER(t.form_factor) = UPPER($1)
        AND ROUND(t.speed_gbps::NUMERIC, 2) = ROUND($2::NUMERIC, 2)
        AND t.id != $3
      GROUP BY t.id, t.part_number, t.standard_name, t.form_factor,
               t.speed_gbps, t.fiber_type, t.reach_meters, t.wavelengths, v.name
      HAVING COUNT(*) >= 1
    `, [fx.form_factor, fx.speed_gbps, fx.id]);

    for (const cand of candidates) {
      const { confidence, basis } = calcConfidence(fx, cand);

      if (confidence < CONFIDENCE_MIN) {
        result.skippedLowConfidence++;
        continue;
      }

      const status = confidence >= CONFIDENCE_AUTO_APPROVE ? "auto_approved" : "pending";
      const notes =
        `${fx.part_number} ↔ ${cand.part_number} (${cand.vendor_name}) | ` +
        `basis: ${basis.join(", ")} | reach: ${fx.reach_meters}m vs ${cand.reach_meters}m | ` +
        `wavelength: ${fx.wavelengths ?? "?"} vs ${cand.wavelengths ?? "?"} | ` +
        `last_price: ${cand.last_price?.toISOString() ?? "never"} | ` +
        `source: catalog-reconcile`;

      // Upsert — bereits approved/rejected Einträge nicht überschreiben
      const { rowCount } = await pool.query(`
        INSERT INTO transceiver_equivalences
          (flexoptix_id, competitor_id, confidence, match_basis, match_notes, status)
        VALUES ($1, $2, $3, $4, $5, $6)
        ON CONFLICT (flexoptix_id, competitor_id) DO UPDATE SET
          confidence   = EXCLUDED.confidence,
          match_basis  = EXCLUDED.match_basis,
          match_notes  = EXCLUDED.match_notes,
          updated_at   = NOW()
        WHERE transceiver_equivalences.status NOT IN ('approved', 'rejected', 'auto_approved')
      `, [fx.id, cand.competitor_id, confidence, basis, notes, status]);

      const wasInsertOrUpdate = (rowCount ?? 0) > 0;
      if (!wasInsertOrUpdate) {
        result.skippedAlreadyMatched++;
        continue;
      }

      // Vendor-Breakdown tracken
      if (!result.vendorBreakdown[cand.vendor_name]) {
        result.vendorBreakdown[cand.vendor_name] = { autoApproved: 0, pending: 0 };
      }

      if (status === "auto_approved") {
        result.newAutoApproved++;
        result.vendorBreakdown[cand.vendor_name].autoApproved++;

        // competitor_verified auf FX-Produkt setzen
        await pool.query(`
          UPDATE transceivers
          SET competitor_verified = true,
              competitor_verified_at = NOW(),
              competitor_status = 'matched',
              competitor_status_updated_at = NOW()
          WHERE id = $1 AND competitor_verified = false
        `, [fx.id]);
      } else {
        result.newPending++;
        result.vendorBreakdown[cand.vendor_name].pending++;
      }
    }
  }

  result.durationMs = Date.now() - startMs;

  // ── Abschluss-Report ───────────────────────────────────────────────────────
  console.log("\n=== Catalog Reconcile Results ===");
  console.log(`  Flexoptix processed:       ${result.flexoptixProcessed}`);
  console.log(`  New auto_approved:         ${result.newAutoApproved}`);
  console.log(`  New pending:               ${result.newPending}`);
  console.log(`  Skipped (low confidence):  ${result.skippedLowConfidence}`);
  console.log(`  Skipped (already matched): ${result.skippedAlreadyMatched}`);
  console.log(`  Duration:                  ${(result.durationMs / 1000).toFixed(1)}s`);
  console.log("\n  Vendor Breakdown:");
  for (const [vendor, counts] of Object.entries(result.vendorBreakdown).sort(
    (a, b) => (b[1].autoApproved + b[1].pending) - (a[1].autoApproved + a[1].pending)
  )) {
    console.log(`    ${vendor.padEnd(20)} auto_approved=${counts.autoApproved} pending=${counts.pending}`);
  }
  console.log("=================================\n");

  return result;
}
