/**
 * Flexoptix Detail Enricher
 *
 * Fetches full product specifications and compatibility data from the Flexoptix
 * API on a per-SKU basis (specifications=1&compatibilities=1) and writes all
 * structured fields back to the transceivers table.
 *
 * Unlike the bulk catalog sync (specifications=0 to avoid HTTP 503), this robot
 * processes products in small batches with rate-limiting so the API stays happy.
 *
 * Fields written per product:
 *   fx_specifications      — raw [{label, value}, ...] blob (for datasheet gen)
 *   fx_compatibilities     — full [{sku, compatible_to_vendor, original_part_number}]
 *   compliance_code        — "LX SGMII", "SR4", "LR4", etc.
 *   laser_type             — "FP", "DFB", "VCSEL", "EML"
 *   receiver_type          — "PIN", "APD"
 *   supported_protocols    — TEXT[]
 *   extinction_ratio_db    — dB
 *   cdr_support            — boolean
 *   inbuilt_fec            — boolean
 *   power_consumption_w    — W (overrides if empty)
 *   optical_budget_db      — dB (overrides if empty)
 *   tx_power_min_dbm       — dBm
 *   tx_power_max_dbm       — dBm
 *   rx_sensitivity_dbm     — dBm
 *   modulation             — "NRZ", "PAM4", etc.
 *   wavelength_tx_nm       — nm (overrides if empty)
 *   wavelength_rx_nm       — nm (overrides if empty)
 *   image_url              — product image URL
 *   product_page_url       — product page URL
 *   detail_synced_at       — timestamp of this sync
 *
 * Scheduling:
 *   - Runs daily at 03:00 UTC
 *   - Processes BATCH_SIZE products per run (prioritises unseen, then stale >7d)
 *   - Rate: 1 API call per 600ms (~1.6 rps, safe for Magento)
 */

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

// ── Constants ──────────────────────────────────────────────────────────────

/** Products per enricher run. Full catalog (~1100 products) in ~11 daily runs. */
const BATCH_SIZE = 100;

/** Milliseconds between per-SKU API calls (Magento rate-limit safety). */
const API_CALL_DELAY_MS = 600;

// ── Types ──────────────────────────────────────────────────────────────────

interface FxApiCompatibility {
  sku: string | null;
  compatible_to_vendor: string;
  original_part_number: string | null;
}

interface FxApiSpec {
  label: string;
  value: unknown;
}

interface FxApiProduct {
  sku: string;
  name?: string;
  url?: string;
  image?: string;
  compatibilities?: FxApiCompatibility[];
  specifications?: FxApiSpec[];
}

interface ParsedSpecs {
  formFactor: string | null;
  speedGbps: number | null;
  complianceCode: string | null;
  laserType: string | null;
  receiverType: string | null;
  supportedProtocols: string[];
  extinctionRatioDb: number | null;
  cdrSupport: boolean | null;
  inbuiltFec: boolean | null;
  powerConsumptionW: number | null;
  opticalBudgetDb: number | null;
  txPowerMinDbm: number | null;
  txPowerMaxDbm: number | null;
  rxSensitivityDbm: number | null;
  modulation: string | null;
  wavelengthTxNm: number | null;
  wavelengthRxNm: number | null;
  tempRange: string | null;
  domSupport: boolean | null;
}

export interface DetailEnricherResult {
  processed: number;
  updated: number;
  notFound: number;
  apiErrors: number;
  dbErrors: number;
}

// ── Helpers ────────────────────────────────────────────────────────────────

function specValue(specs: FxApiSpec[], label: string): string | null {
  const entry = specs.find(s => s.label.toLowerCase() === label.toLowerCase());
  if (!entry) return null;
  const v = entry.value;
  if (Array.isArray(v)) return v.join(", ");
  if (typeof v === "string") return v.trim() || null;
  if (typeof v === "number" || typeof v === "boolean") return String(v);
  return null;
}

function specArray(specs: FxApiSpec[], label: string): string[] {
  const entry = specs.find(s => s.label.toLowerCase() === label.toLowerCase());
  if (!entry) return [];
  if (Array.isArray(entry.value)) return entry.value.filter(v => typeof v === "string") as string[];
  const v = entry.value;
  if (typeof v === "string" && v.trim()) return [v.trim()];
  return [];
}

function parseDbm(text: string | null): { min: number | null; max: number | null } {
  if (!text) return { min: null, max: null };
  // Format: "-15 dBm / -8 dBm" or "-31 dBm / -8 dBm (overload) @100M"
  const numbers = text.match(/-?\d+(?:\.\d+)?\s*dBm/gi) ?? [];
  const values = numbers
    .map(n => parseFloat(n.replace(/dBm/i, "").trim()))
    .filter(n => Number.isFinite(n));
  return {
    min: values[0] ?? null,
    max: values[1] ?? null,
  };
}

function parseWavelengthNm(text: string | null): number | null {
  if (!text) return null;
  const match = text.match(/(\d{3,4})\s*nm/);
  return match ? parseInt(match[1], 10) : null;
}

function parsePowerW(text: string | null): number | null {
  if (!text) return null;
  const match = text.match(/([\d.]+)\s*W/i);
  return match ? parseFloat(match[1]) : null;
}

function parseDb(text: string | null): number | null {
  if (!text) return null;
  const match = text.match(/([\d.]+)\s*dB(?!m)/i);
  return match ? parseFloat(match[1]) : null;
}

function parseTempRange(text: string | null, operatingTemp: string | null): "COM" | "IND" | null {
  // Parse degree-range strings like "0°C - 70°C" or "-40°C - 85°C"
  if (text && /°C/.test(text)) {
    const minMatch = text.match(/(-?\d+)\s*°C/);
    const minC = minMatch ? parseInt(minMatch[1], 10) : null;
    if (minC !== null && minC < -10) return "IND";
    return "COM";
  }
  // Classify from the operating temperature label
  const combined = [text, operatingTemp].filter(Boolean).join(" ").toLowerCase();
  if (/industrial|ind\b|-40/.test(combined)) return "IND";
  if (/commercial|standard|com\b/.test(combined)) return "COM";
  return null;
}

function parseDomSupport(text: string | null): boolean | null {
  if (!text) return null;
  const lower = text.toLowerCase();
  if (/not implemented|no|none/.test(lower)) return false;
  if (/yes|implemented|supported|digital/.test(lower)) return true;
  return null;
}

function parseBoolean(text: string | null): boolean | null {
  if (!text) return null;
  const lower = text.toLowerCase().trim();
  if (["yes", "true", "1", "ja"].includes(lower)) return true;
  if (["no", "false", "0", "nein", "none"].includes(lower)) return false;
  return null;
}

function parseModulation(text: string | null): string | null {
  if (!text) return null;
  // Normalize "NRZ @100M - 800M" → "NRZ", "PAM4" → "PAM4"
  const match = text.match(/\b(NRZ|PAM4|PAM-4|DP-QPSK|QPSK|16QAM|64QAM|OOK)\b/i);
  return match ? match[1].toUpperCase().replace("PAM-4", "PAM4") : text.trim();
}

/**
 * Authoritative form factor from the structured "Form Factor" spec (NOT the name).
 */
function normalizeFormFactorSpec(v: string | null): string | null {
  if (!v) return null;
  const t = v.toUpperCase();
  const order = ["QSFP-DD800","QSFP-DD","QSFP112","QSFP56","QSFP28","QSFP+",
    "OSFP224","OSFP112","OSFP","SFP56","SFP28","CSFP","SFP+","SFP",
    "XFP","CFP4","CFP2","CFP","GBIC"];
  for (const ff of order) if (t.includes(ff)) return ff === "CSFP" ? "SFP" : ff;
  return null;
}

/** Map a datasheet line rate (Gbit/s) to the nominal standard speed. */
function mapLineRateToNominal(u: number): number {
  const table: Array<[number, number]> = [
    [0.2, 0.1], [1.5, 1], [3.5, 2.5], [6, 5], [13, 10], [20, 16],
    [33, 25], [46, 40], [60, 50], [120, 100], [240, 200], [480, 400],
    [960, 800], [2000, 1600],
  ];
  for (const [ceil, nom] of table) if (u <= ceil) return nom;
  return Math.round(u);
}

/**
 * Authoritative nominal speed (Gbps) sourced from the datasheet specs only:
 * primary = highest Ethernet rate named in "Supported Protocols";
 * fallback = upper bound of "Bandwidth" line rate mapped to nominal.
 * Never derived from the product name.
 */
function nominalSpeedFromSpecs(specs: FxApiSpec[]): number | null {
  const protocols = specArray(specs, "Supported Protocols");
  let best: number | null = null;
  for (const p of protocols) {
    const t = p.toLowerCase();
    let sp: number | null = null;
    if (/\bfast ethernet\b/.test(t)) sp = 0.1;
    else if (/\bgigabit ethernet\b/.test(t)) sp = 1;
    else {
      // Terabit first ("1.6t ethernet" -> 1600), then Gigabit. Aggregate "Nx MMMg"
      // twin-port labels resolve via the explicit total (e.g. "1.6T Ethernet").
      const mt = t.match(/\b(\d+(?:\.\d+)?)\s*t(?:b|be|ig)?\s*ethernet\b/);
      if (mt) sp = parseFloat(mt[1]) * 1000;
      else {
        const m = t.match(/(?:^|[^x\d])(\d+(?:\.\d+)?)\s*g(?:b|be|ig)?\s*ethernet\b/) || t.match(/\b(\d+)\s*gbe\b/);
        if (m) sp = parseFloat(m[1]);
      }
    }
    if (sp !== null && (best === null || sp > best)) best = sp;
  }
  if (best !== null) return best;
  const bw = specValue(specs, "Bandwidth");
  if (bw) {
    const gbits = Array.from(bw.matchAll(/([\d.]+)\s*gbit\/s/gi)).map(m => parseFloat(m[1]));
    if (gbits.length) return mapLineRateToNominal(Math.max(...gbits));
  }
  return null;
}

/** Format a nominal Gbps value as a display label ("1G", "100G", "1.6T"). */
function speedLabelGbps(g: number | null): string | null {
  if (g === null) return null;
  if (g >= 1000) return `${g / 1000}T`;
  if (g < 1) return `${Math.round(g * 1000)}M`;
  return `${g}G`;
}

/**
 * Parse the flat specifications array into structured fields.
 */
function parseSpecs(specs: FxApiSpec[]): ParsedSpecs {
  const txPowers = parseDbm(specValue(specs, "Transmit min/max per lane"));
  const rxPowers = parseDbm(specValue(specs, "Receiver min/max per lane"));

  return {
    formFactor:         normalizeFormFactorSpec(specValue(specs, "Form Factor")),
    speedGbps:          nominalSpeedFromSpecs(specs),
    complianceCode:     specValue(specs, "Compliance Code"),
    laserType:          specValue(specs, "Laser"),
    receiverType:       specValue(specs, "Receiver Type"),
    supportedProtocols: specArray(specs, "Supported Protocols"),
    extinctionRatioDb:  parseDb(specValue(specs, "Extinction Ratio")),
    cdrSupport:         parseBoolean(specValue(specs, "CDR")),
    inbuiltFec:         parseBoolean(specValue(specs, "Inbuilt FEC")),
    powerConsumptionW:  parsePowerW(specValue(specs, "Power Consumption")),
    opticalBudgetDb:    parseDb(specValue(specs, "Powerbudget (dB)")),
    txPowerMinDbm:      txPowers.min,
    txPowerMaxDbm:      txPowers.max,
    rxSensitivityDbm:   rxPowers.min,
    modulation:         parseModulation(specValue(specs, "Modulation")),
    wavelengthTxNm:     parseWavelengthNm(specValue(specs, "Wavelength TX (Typical)")),
    wavelengthRxNm:     parseWavelengthNm(specValue(specs, "Wavelength RX (Typical)")),
    tempRange:          parseTempRange(
                          specValue(specs, "Temperature Range"),
                          specValue(specs, "Operating Temperature"),
                        ),
    domSupport:         parseDomSupport(specValue(specs, "Digital Diagnostic Monitoring (DDM)")),
  };
}

// ── API client ──────────────────────────────────────────────────────────────

async function authenticate(baseUrl: string, timeoutMs: number): Promise<string> {
  const existingToken = process.env["FLEXOPTIX_API_TOKEN"]?.trim();
  if (existingToken) return existingToken;

  const username = process.env["FLEXOPTIX_API_USERNAME"]?.trim();
  const password = process.env["FLEXOPTIX_API_PASSWORD"]?.trim();
  if (!username || !password) {
    throw new Error("FLEXOPTIX_API_USERNAME + FLEXOPTIX_API_PASSWORD required for detail enricher");
  }

  const authPath = process.env["FLEXOPTIX_API_AUTH_PATH"]?.trim() ?? "/rest/V1/integration/customer/token";
  const url = `${baseUrl}${authPath}`;
  const controller = new AbortController();
  const timer = setTimeout(() => controller.abort(), timeoutMs);

  try {
    const res = await fetch(url, {
      method: "POST",
      headers: { "content-type": "application/json", accept: "application/json" },
      body: JSON.stringify({ username, password }),
      signal: controller.signal,
    });
    if (!res.ok) throw new Error(`Auth failed: HTTP ${res.status}`);
    const token = await res.json();
    if (typeof token !== "string") throw new Error("Auth response was not a string token");
    return token;
  } finally {
    clearTimeout(timer);
  }
}

/**
 * Normalize a FX SKU for the API query.
 * Strips variant/self-configure suffixes that exist in TIP DB but not in the API:
 *   "S.B1312.10.DLI:Sx"  → "S.B1312.10.DLI"  (self-configure parent)
 *   "M4.T8SL.x"          → "M4.T8SL"          (placeholder variant)
 *   "P.1696.25.yy.R"     → kept as-is         (real SKU with letter suffix)
 */
function normalizeSku(sku: string): string {
  // Strip ":Sx", ":S1", ":AB", etc. (colon-delimited variant suffixes)
  const colonSuffix = sku.replace(/:[A-Za-z0-9]+$/, "");
  if (colonSuffix !== sku) return colonSuffix;
  // Strip trailing ".x" or ".y" (single-letter placeholder segments)
  const dotSuffix = sku.replace(/\.[xy]$/i, "");
  if (dotSuffix !== sku) return dotSuffix;
  return sku;
}

async function fetchProductDetail(
  baseUrl: string,
  productPath: string,
  sku: string,
  headers: Record<string, string>,
  timeoutMs: number,
): Promise<FxApiProduct | null> {
  const apiSku = normalizeSku(sku);
  const url = new URL(productPath, baseUrl.endsWith("/") ? baseUrl : `${baseUrl}/`);
  url.searchParams.set("sku", apiSku);
  url.searchParams.set("specifications", "1");
  url.searchParams.set("compatibilities", "1");

  const controller = new AbortController();
  const timer = setTimeout(() => controller.abort(), timeoutMs);

  try {
    const res = await fetch(url.toString(), { headers, signal: controller.signal });
    if (!res.ok) return null;

    const body = await res.json();
    // API returns array for SKU query
    const rows = Array.isArray(body) ? body : [body];
    const row = rows[0];
    if (!row || typeof row !== "object") return null;

    return row as FxApiProduct;
  } catch {
    return null;
  } finally {
    clearTimeout(timer);
  }
}

// ── DB helpers ─────────────────────────────────────────────────────────────

interface FxProduct {
  id: string;
  part_number: string;
  power_consumption_w: number | null;
  optical_budget_db: number | null;
  wavelength_tx_nm: number | null;
  wavelength_rx_nm: number | null;
}

async function fetchBatch(): Promise<FxProduct[]> {
  const result = await pool.query<FxProduct>(`
    SELECT
      t.id,
      t.part_number,
      t.power_consumption_w,
      t.optical_budget_db,
      t.wavelength_tx_nm,
      t.wavelength_rx_nm
    FROM transceivers t
    JOIN vendors v ON v.id = t.vendor_id
    WHERE UPPER(v.name) LIKE '%FLEXOPTIX%'
      -- FX catalog SKUs always contain a dot (e.g. S.1303.10.G, Q2.85850.100.D5)
      -- Products without a dot are misidentified non-FX items — skip them
      AND t.part_number LIKE '%.%'
      AND (
        t.detail_synced_at IS NULL
        OR t.detail_synced_at < NOW() - INTERVAL '7 days'
      )
    ORDER BY
      t.detail_synced_at ASC NULLS FIRST,
      t.data_completeness DESC  -- process most-complete products first
    LIMIT $1
  `, [BATCH_SIZE]);
  return result.rows;
}

async function writeDetails(
  transceiverId: string,
  product: FxApiProduct,
  parsed: ParsedSpecs,
): Promise<void> {
  const compat = Array.isArray(product.compatibilities) ? product.compatibilities : [];
  const specs = Array.isArray(product.specifications) ? product.specifications : [];

  await pool.query(`
    UPDATE transceivers SET
      form_factor          = COALESCE($23, form_factor),
      speed_gbps           = COALESCE($24, speed_gbps),
      speed                = COALESCE($25, speed),
      fx_specifications    = $1,
      fx_compatibilities   = $2,
      compliance_code      = COALESCE(compliance_code, $3),
      laser_type           = COALESCE(laser_type, $4),
      receiver_type        = COALESCE(receiver_type, $5),
      supported_protocols  = COALESCE(supported_protocols, $6),
      extinction_ratio_db  = COALESCE(extinction_ratio_db, $7),
      cdr_support          = COALESCE(cdr_support, $8),
      inbuilt_fec          = COALESCE(inbuilt_fec, $9),
      power_consumption_w  = COALESCE(power_consumption_w, $10),
      optical_budget_db    = COALESCE(optical_budget_db, $11),
      tx_power_min_dbm     = COALESCE(tx_power_min_dbm, $12),
      tx_power_max_dbm     = COALESCE(tx_power_max_dbm, $13),
      rx_sensitivity_dbm   = COALESCE(rx_sensitivity_dbm, $14),
      modulation           = COALESCE(modulation, $15),
      wavelength_tx_nm     = COALESCE(wavelength_tx_nm, $16),
      wavelength_rx_nm     = COALESCE(wavelength_rx_nm, $17),
      temp_range           = COALESCE(NULLIF(temp_range, 'COM'), $18),
      dom_support          = COALESCE(dom_support, $19),
      image_url            = COALESCE(NULLIF(image_url, ''), $20),
      product_page_url     = COALESCE(NULLIF(product_page_url, ''), $21),
      detail_synced_at     = NOW(),
      updated_at           = NOW()
    WHERE id = $22
  `, [
    specs.length > 0 ? JSON.stringify(specs) : null,               // $1
    compat.length > 0 ? JSON.stringify(compat) : null,             // $2
    parsed.complianceCode,                                          // $3
    parsed.laserType,                                               // $4
    parsed.receiverType,                                            // $5
    parsed.supportedProtocols.length > 0 ? parsed.supportedProtocols : null, // $6
    parsed.extinctionRatioDb,                                       // $7
    parsed.cdrSupport,                                              // $8
    parsed.inbuiltFec,                                              // $9
    parsed.powerConsumptionW,                                       // $10
    parsed.opticalBudgetDb,                                         // $11
    parsed.txPowerMinDbm,                                           // $12
    parsed.txPowerMaxDbm,                                           // $13
    parsed.rxSensitivityDbm,                                        // $14
    parsed.modulation,                                              // $15
    parsed.wavelengthTxNm,                                          // $16
    parsed.wavelengthRxNm,                                          // $17
    parsed.tempRange,                                               // $18
    parsed.domSupport,                                              // $19
    product.image ?? null,                                          // $20
    product.url ?? null,                                            // $21
    transceiverId,                                                  // $22
    parsed.formFactor,                                              // $23
    parsed.speedGbps,                                               // $24
    speedLabelGbps(parsed.speedGbps),                               // $25
  ]);
}

// ── Main export ─────────────────────────────────────────────────────────────

export async function runFlexoptixDetailEnricher(): Promise<DetailEnricherResult> {
  const baseUrl = process.env["FLEXOPTIX_API_BASE_URL"]?.trim();
  if (!baseUrl) {
    throw new Error("FLEXOPTIX_API_BASE_URL not configured");
  }

  const productPath = process.env["FLEXOPTIX_API_PRODUCTS_PATH"]?.trim()
    ?? "/rest/V2/flexoptix/products";
  const timeoutMs = parseInt(process.env["FLEXOPTIX_API_TIMEOUT_MS"]?.trim() ?? "30000", 10);

  const ts = () => new Date().toISOString();
  console.log(`[${ts()}] Flexoptix detail enricher starting (batch=${BATCH_SIZE})`);

  const token = await authenticate(baseUrl, timeoutMs);
  const headers: Record<string, string> = {
    accept: "application/json",
    authorization: `Bearer ${token}`,
  };

  const batch = await fetchBatch();
  console.log(`[${ts()}] Batch: ${batch.length} FX products queued for detail sync`);

  let updated = 0;
  let notFound = 0;
  let apiErrors = 0;
  let dbErrors = 0;

  for (const product of batch) {
    // Rate-limit: sleep between calls
    await new Promise(resolve => setTimeout(resolve, API_CALL_DELAY_MS));

    let apiProduct: FxApiProduct | null = null;
    try {
      apiProduct = await fetchProductDetail(baseUrl, productPath, product.part_number, headers, timeoutMs);
    } catch (err: unknown) {
      apiErrors++;
      console.warn(
        `[${ts()}] detail-enricher API error (${product.part_number}): ` +
        `${err instanceof Error ? err.message : String(err)}`,
      );
      continue;
    }

    if (!apiProduct) {
      // Not found in FX API — still mark synced so we don't retry daily,
      // but log it so we can investigate if many products come back empty
      notFound++;
      await pool.query(
        `UPDATE transceivers SET detail_synced_at = NOW() WHERE id = $1`,
        [product.id],
      ).catch(() => null);
      continue;
    }

    const specs = Array.isArray(apiProduct.specifications) ? apiProduct.specifications : [];
    const parsed = parseSpecs(specs);

    try {
      await writeDetails(product.id, apiProduct, parsed);
      updated++;
    } catch (err: unknown) {
      dbErrors++;
      console.warn(
        `[${ts()}] detail-enricher DB error (${product.part_number}): ` +
        `${err instanceof Error ? err.message : String(err)}`,
      );
    }
  }

  console.log(
    `[${ts()}] Flexoptix detail enricher done: ` +
    `${batch.length} queued, ${updated} updated, ${notFound} not-in-api, ` +
    `${apiErrors} api-errors, ${dbErrors} db-errors`,
  );

  return {
    processed: batch.length,
    updated,
    notFound,
    apiErrors,
    dbErrors,
  };
}
