import { pool } from "./client";

export interface SearchParams {
  q?: string;
  form_factor?: string;
  speed?: string;
  speed_gbps?: number;
  category?: string;
  fiber_type?: string;
  reach_min?: number;
  reach_max?: number;
  wdm_type?: string;
  coherent?: boolean;
  market_status?: string;
  vendor?: string;
  verified?: "price" | "image" | "details" | "full";
  limit?: number;
  offset?: number;
}

export async function searchTransceivers(params: SearchParams) {
  const conditions: string[] = [
    `COALESCE(t.data_confidence, 'unknown') != 'garbage'`,
    `COALESCE(t.product_page_url, '') NOT LIKE '%/category/%'`,
    `COALESCE(t.category, '') NOT IN ('NonTransceiver', 'Accessory', 'Adapter / Converter', 'Switch / Media Converter', 'Switch / Network Infrastructure', 'NIC / Adapter', 'Mux / Passive Optical', 'Product Family', 'Loopback / Test Module')`,
  ];
  const values: any[] = [];
  let idx = 1;

  if (params.q) {
    conditions.push(`(search_vector @@ plainto_tsquery('english', $${idx}) OR t.part_number ILIKE '%' || $${idx} || '%' OR t.standard_name ILIKE '%' || $${idx} || '%')`);
    values.push(params.q);
    idx++;
  }
  if (params.form_factor) {
    conditions.push(`form_factor = $${idx}`);
    values.push(params.form_factor);
    idx++;
  }
  if (params.speed) {
    conditions.push(`speed = $${idx}`);
    values.push(params.speed);
    idx++;
  }
  if (params.speed_gbps) {
    conditions.push(`speed_gbps = $${idx}`);
    values.push(params.speed_gbps);
    idx++;
  }
  if (params.category) {
    conditions.push(`category = $${idx}`);
    values.push(params.category);
    idx++;
  }
  if (params.fiber_type) {
    conditions.push(`fiber_type = $${idx}`);
    values.push(params.fiber_type);
    idx++;
  }
  if (params.reach_min) {
    conditions.push(`reach_meters >= $${idx}`);
    values.push(params.reach_min);
    idx++;
  }
  if (params.reach_max) {
    conditions.push(`reach_meters <= $${idx}`);
    values.push(params.reach_max);
    idx++;
  }
  if (params.wdm_type) {
    conditions.push(`wdm_type = $${idx}`);
    values.push(params.wdm_type);
    idx++;
  }
  if (params.coherent !== undefined) {
    conditions.push(`coherent = $${idx}`);
    values.push(params.coherent);
    idx++;
  }
  if (params.market_status) {
    conditions.push(`market_status = $${idx}`);
    values.push(params.market_status);
    idx++;
  }
  if (params.vendor) {
    conditions.push(`v.name ILIKE $${idx}`);
    values.push(`%${params.vendor}%`);
    idx++;
  }
  if (params.verified) {
    const col = params.verified === "full" ? "fully_verified" : params.verified + "_verified";
    conditions.push(`t.${col} = true`);
  }

  const where = conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "";
  const limit = params.limit || 50;
  const offset = params.offset || 0;

  // Add relevance ranking when full-text search is used
  const orderBy = params.q
    ? `ORDER BY (t.part_number ILIKE $1) DESC, ts_rank(search_vector, plainto_tsquery('english', $1)) DESC, fully_verified DESC NULLS LAST, has_image DESC NULLS LAST`
    : `ORDER BY fully_verified DESC NULLS LAST, has_image DESC NULLS LAST, speed_gbps DESC NULLS LAST, reach_meters ASC NULLS LAST`;

  const query = `
    SELECT t.*, v.name as vendor_name
    FROM transceivers t
    LEFT JOIN vendors v ON t.vendor_id = v.id
    ${where}
    ${orderBy}
    LIMIT ${limit} OFFSET ${offset}
  `;

  const countQuery = `SELECT COUNT(*) FROM transceivers t LEFT JOIN vendors v ON t.vendor_id = v.id ${where}`;

  const [dataResult, countResult] = await Promise.all([
    pool.query(query, values),
    pool.query(countQuery, values),
  ]);

  return {
    data: dataResult.rows,
    total: parseInt(countResult.rows[0].count),
    limit,
    offset,
  };
}

export async function getTransceiverById(id: string) {
  const result = await pool.query(
    `SELECT t.*, v.name as vendor_name, s.name as standard_full_name
     FROM transceivers t
     LEFT JOIN vendors v ON t.vendor_id = v.id
     LEFT JOIN standards s ON t.standard_id = s.id
     WHERE t.id::text = $1::text OR t.slug = $1::text`,
    [id]
  );
  return result.rows[0] || null;
}

export interface SwitchSearchParams extends SearchParams {
  whitebox?: boolean;
  sonic_compatible?: boolean;
  asic_vendor?: string;
  nos?: string;
  ocp?: boolean;
  max_speed_gbps?: number;
}

export async function searchSwitches(params: SwitchSearchParams) {
  const conditions: string[] = [];
  const values: any[] = [];
  let idx = 1;

  if (params.q) {
    conditions.push(`sw.search_vector @@ plainto_tsquery('english', $${idx})`);
    values.push(params.q);
    idx++;
  }
  if (params.category) {
    conditions.push(`sw.category = $${idx}`);
    values.push(params.category);
    idx++;
  }
  if (params.whitebox !== undefined) {
    conditions.push(`sw.is_whitebox = $${idx}`);
    values.push(params.whitebox);
    idx++;
  }
  if (params.sonic_compatible !== undefined) {
    conditions.push(`sw.sonic_compatible = $${idx}`);
    values.push(params.sonic_compatible);
    idx++;
  }
  if (params.asic_vendor) {
    conditions.push(`sw.asic_vendor ILIKE $${idx}`);
    values.push(`%${params.asic_vendor}%`);
    idx++;
  }
  if (params.nos) {
    conditions.push(`$${idx} = ANY(sw.supported_nos)`);
    values.push(params.nos);
    idx++;
  }
  if (params.ocp !== undefined) {
    conditions.push(`sw.is_ocp_accepted = $${idx}`);
    values.push(params.ocp);
    idx++;
  }
  if (params.max_speed_gbps) {
    conditions.push(`sw.max_speed_gbps = $${idx}`);
    values.push(params.max_speed_gbps);
    idx++;
  }

  const where = conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "";
  const limit = params.limit || 50;
  const offset = params.offset || 0;

  const orderBy = params.q
    ? `ORDER BY ts_rank(sw.search_vector, plainto_tsquery('english', $1)) DESC`
    : `ORDER BY sw.max_speed_gbps DESC NULLS LAST`;

  const query = `
    SELECT sw.*, v.name as vendor_name
    FROM switches sw
    LEFT JOIN vendors v ON sw.vendor_id = v.id
    ${where}
    ${orderBy}
    LIMIT ${limit} OFFSET ${offset}
  `;

  const countQuery = `SELECT COUNT(*) FROM switches sw ${where}`;

  const [dataResult, countResult] = await Promise.all([
    pool.query(query, values),
    pool.query(countQuery, values),
  ]);

  return {
    data: dataResult.rows,
    total: parseInt(countResult.rows[0].count),
    limit,
    offset,
  };
}

export async function getSwitchById(id: string) {
  const result = await pool.query(
    `SELECT sw.*, v.name as vendor_name, v.website as vendor_website
     FROM switches sw
     LEFT JOIN vendors v ON sw.vendor_id = v.id
     WHERE sw.id = $1`,
    [id]
  );
  return result.rows[0] || null;
}

export async function getSwitchDocuments(switchId: string) {
  const result = await pool.query(
    `SELECT id, doc_type, title, source_url, download_url, language, version, is_official, vendor_doc_id, page_count, file_size_bytes, r2_key, created_at
     FROM product_documents
     WHERE switch_id = $1
     ORDER BY is_official DESC, doc_type, title`,
    [switchId]
  );
  return result.rows;
}

export async function getSwitchIssues(switchId: string) {
  const result = await pool.query(
    `SELECT id, source_type, source_name, source_url, title, summary, severity,
            issue_tags, affected_firmware, fix_firmware, date_reported, is_resolved, confidence
     FROM product_issues
     WHERE switch_id = $1
     ORDER BY
       CASE severity WHEN 'critical' THEN 0 WHEN 'warning' THEN 1 ELSE 2 END,
       date_reported DESC NULLS LAST`,
    [switchId]
  );
  return result.rows;
}

export async function getTransceiverIssues(transceiverI: string) {
  const result = await pool.query(
    `SELECT id, source_type, source_name, source_url, title, summary, severity,
            issue_tags, affected_firmware, fix_firmware, date_reported, is_resolved, confidence
     FROM product_issues
     WHERE transceiver_id = $1
     ORDER BY
       CASE severity WHEN 'critical' THEN 0 WHEN 'warning' THEN 1 ELSE 2 END,
       date_reported DESC NULLS LAST`,
    [transceiverI]
  );
  return result.rows;
}

export async function getTransceiverDocuments(transceiverI: string) {
  const result = await pool.query(
    `SELECT id, doc_type, title, source_url, download_url, language, version, is_official, vendor_doc_id, page_count, file_size_bytes, r2_key, created_at
     FROM product_documents
     WHERE transceiver_id = $1
     ORDER BY is_official DESC, doc_type, title`,
    [transceiverI]
  );
  return result.rows;
}

export async function getCompatibleTransceivers(switchId: string) {
  const result = await pool.query(
    `SELECT t.*, c.status, c.verified_by, c.verification_method, c.notes as compat_notes,
            v.name AS vendor_name, v.type AS vendor_type, v.website AS vendor_website,
            -- Latest verified price
            COALESCE(t.price_verified_eur,
              (SELECT po.price FROM price_observations po WHERE po.transceiver_id = t.id ORDER BY po.time DESC LIMIT 1)
            ) AS latest_price,
            CASE WHEN t.price_verified_eur IS NOT NULL THEN 'EUR'
              ELSE (SELECT po.currency FROM price_observations po WHERE po.transceiver_id = t.id ORDER BY po.time DESC LIMIT 1)
            END AS latest_currency
     FROM compatibility c
     JOIN transceivers t ON c.transceiver_id = t.id
     LEFT JOIN vendors v ON t.vendor_id = v.id
     WHERE c.switch_id = $1 AND c.status = 'compatible'
     ORDER BY
       CASE WHEN LOWER(v.name) = 'flexoptix' THEN 0 ELSE 1 END,
       CASE WHEN c.verification_method = 'vendor_compat' THEN 0
            WHEN c.verification_method = 'vendor_matrix' THEN 1
            ELSE 2 END,
       t.speed_gbps DESC`,
    [switchId]
  );
  return result.rows;
}

/**
 * getFlexoptixSuggestions — returns Flexoptix transceivers that physically fit
 * the switch's port slots, derived from ports_config JSONB keys.
 * Works even before the compat scraper has processed the switch.
 */
export async function getFlexoptixSuggestions(switchId: string) {
  const result = await pool.query(
    `WITH sw_vendor AS (
       -- Map the switch's vendor to the Flexoptix coding-matrix token. A transceiver
       -- only truly runs in the switch if Flexoptix can code it for this platform.
       SELECT
         CASE
           WHEN v.name ILIKE '%aruba%' OR v.name ILIKE '%hpe%' OR v.name ILIKE '%hewlett%' OR v.name ILIKE '%hp h3c%' OR v.name ILIKE '%hp %' THEN 'aruba'
           WHEN v.name ILIKE '%cisco%' THEN 'cisco'
           WHEN v.name ILIKE '%arista%' THEN 'arista'
           WHEN v.name ILIKE '%juniper%' THEN 'juniper'
           WHEN v.name ILIKE '%dell%' OR v.name ILIKE '%force10%' THEN 'dell'
           WHEN v.name ILIKE '%huawei%' THEN 'huawei'
           WHEN v.name ILIKE '%fortinet%' THEN 'fortinet'
           WHEN v.name ILIKE '%mikrotik%' THEN 'mikrotik'
           WHEN v.name ILIKE '%nvidia%' OR v.name ILIKE '%mellanox%' THEN 'mellanox'
           WHEN v.name ILIKE '%extreme%' OR v.name ILIKE '%enterasys%' THEN 'extreme'
           WHEN v.name ILIKE '%nokia%' OR v.name ILIKE '%alcatel%' THEN 'nokia'
           WHEN v.name ILIKE '%brocade%' OR v.name ILIKE '%ruckus%' THEN 'brocade'
           WHEN v.name ILIKE '%netgear%' THEN 'netgear'
           WHEN v.name ILIKE '%zyxel%' THEN 'zyxel'
           WHEN v.name ILIKE '%ubiquiti%' THEN 'ubiquiti'
           WHEN v.name ILIKE '%d-link%' OR v.name ILIKE '%dlink%' THEN 'd-link'
           WHEN v.name ILIKE '%allied telesis%' THEN 'allied telesis'
           WHEN v.name ILIKE '%adtran%' THEN 'adtran'
           WHEN v.name ILIKE '%ciena%' THEN 'ciena'
           ELSE NULL  -- unmapped (whitebox e.g. Edgecore) -> MSA-standard fallback
         END AS vpat
       FROM switches s JOIN vendors v ON v.id = s.vendor_id WHERE s.id = $1
     ),
     gate AS (
       -- Hard gate: a switch only presents compatibility if its port config has
       -- been verified against the manufacturer datasheet/source. Unverified =
       -- no claim (return nothing) rather than show potentially-wrong data.
       SELECT ports_verified FROM switches WHERE id = $1
     ),
     switch_ports AS (
       -- Parse each ports_config key 'SPEED_FORMFACTOR' (e.g. '100G_QSFP28') into
       -- a numeric port speed (Gbps) + a cage family. Real transceivers are then
       -- bounded by the port speed so corrupt/oversized records cannot leak in.
       SELECT DISTINCT
         -- numeric speed prefix: 100G->100, 25G->25, 2.5G->2.5, 100M->0.1, 800G->800
         -- speed token may be at the start ('100G_QSFP28') or embedded
         -- ('max_per_slot_100G_QSFP28' on chassis switches). Match before the cage.
         CASE
           WHEN k ~ '[0-9.]+M[_A-Za-z]' THEN (substring(k from '([0-9.]+)M[_A-Za-z]')::numeric / 1000)
           WHEN k ~ '[0-9.]+T[_A-Za-z]' THEN (substring(k from '([0-9.]+)T[_A-Za-z]')::numeric * 1000)
           WHEN k ~ '[0-9.]+G[_A-Za-z]' THEN substring(k from '([0-9.]+)G[_A-Za-z]')::numeric
           WHEN k ~ '[0-9.]+G$'         THEN substring(k from '([0-9.]+)G$')::numeric
           ELSE NULL
         END AS port_speed,
         -- the port's specific cage type (determines which modules mechanically seat)
         CASE
           WHEN k ILIKE '%QSFP-DD800%' THEN 'QSFP-DD800'
           WHEN k ILIKE '%QSFP-DD%'    THEN 'QSFP-DD'
           WHEN k ILIKE '%QSFP112%'    THEN 'QSFP112'
           WHEN k ILIKE '%QSFP56%'     THEN 'QSFP56'
           WHEN k ILIKE '%QSFP28%'     THEN 'QSFP28'
           WHEN k ILIKE '%QSFP+%'      THEN 'QSFP+'
           WHEN k ILIKE '%QSFP%'       THEN 'QSFP+'
           WHEN k ILIKE '%OSFP224%'    THEN 'OSFP224'
           WHEN k ILIKE '%OSFP112%'    THEN 'OSFP112'
           WHEN k ILIKE '%OSFP%'       THEN 'OSFP'
           WHEN k ILIKE '%SFP56%'      THEN 'SFP56'
           WHEN k ILIKE '%SFP28%'      THEN 'SFP28'
           WHEN k ILIKE '%SFP+%'       THEN 'SFP+'
           WHEN k ILIKE '%SFP%'        THEN 'SFP+'
           WHEN k ILIKE '%CFP2%'       THEN 'CFP2'
           WHEN k ILIKE '%CFP4%'       THEN 'CFP4'
           WHEN k ILIKE '%CFP%'        THEN 'CFP'
           WHEN k ILIKE '%RJ45%' OR k ILIKE '%mGig%' THEN 'RJ45'
           ELSE NULL
         END AS port_ff
       FROM switches sw,
            jsonb_object_keys(sw.ports_config) AS k
       WHERE sw.id = $1 AND sw.ports_config IS NOT NULL
     )
     SELECT t.id, t.slug, t.part_number, t.standard_name, t.form_factor,
            t.speed, t.speed_gbps, t.reach_meters, t.reach_label,
            t.fiber_type, t.wavelength_tx_nm AS wavelength_nm, t.market_status,
            t.product_page_url, t.image_url,
            t.price_verified_eur, t.price_verified_at, t.street_price_usd AS price_verified_usd,
            v.name AS vendor_name, v.website AS vendor_website,
            COALESCE(t.price_verified_eur,
              (SELECT po.price FROM price_observations po
               WHERE po.transceiver_id = t.id ORDER BY po.time DESC LIMIT 1)
            ) AS latest_price,
            CASE WHEN t.price_verified_eur IS NOT NULL THEN 'EUR'
              ELSE (SELECT po.currency FROM price_observations po
                    WHERE po.transceiver_id = t.id ORDER BY po.time DESC LIMIT 1)
            END AS latest_currency,
            so.warehouse_de_qty,
            so.warehouse_global_qty,
            so.backorder_qty,
            so.backorder_estimated_date
     FROM transceivers t
     JOIN vendors v ON t.vendor_id = v.id
     LEFT JOIN LATERAL (
       SELECT warehouse_de_qty, warehouse_global_qty, backorder_qty, backorder_estimated_date
       FROM stock_observations
       WHERE transceiver_id = t.id
       ORDER BY time DESC
       LIMIT 1
     ) so ON true
     WHERE LOWER(v.name) = 'flexoptix'
       AND (SELECT ports_verified FROM gate) IS TRUE
       -- Only parts confirmed in Flexoptix's live API catalog (have authoritative
       -- datasheet specs). Excludes phantom/misattributed parts not actually
       -- orderable from Flexoptix, and parts with no source-of-truth specs.
       AND t.fx_specifications IS NOT NULL
       AND t.speed_gbps IS NOT NULL AND t.speed_gbps > 0
       AND EXISTS (
         SELECT 1 FROM switch_ports sp
         WHERE sp.port_ff IS NOT NULL
           AND sp.port_speed IS NOT NULL
           -- module must mechanically seat in this port cage. A cage accepts its own
           -- type plus smaller/backward-compatible modules, never a larger one:
           --   QSFP-DD/OSFP cages take QSFP-family; QSFP28/56 cages take QSFP+/28/56
           --   (NOT QSFP-DD); SFP cages take all SFP variants. No cross-family.
           AND t.form_factor = ANY (
             CASE sp.port_ff
               WHEN 'QSFP-DD800' THEN ARRAY['QSFP-DD800','QSFP-DD','QSFP112','QSFP56','QSFP28','QSFP+']
               WHEN 'QSFP-DD'    THEN ARRAY['QSFP-DD','QSFP112','QSFP56','QSFP28','QSFP+']
               WHEN 'QSFP112'    THEN ARRAY['QSFP112','QSFP56','QSFP28','QSFP+']
               WHEN 'QSFP56'     THEN ARRAY['QSFP56','QSFP28','QSFP+']
               WHEN 'QSFP28'     THEN ARRAY['QSFP28','QSFP+']
               WHEN 'QSFP+'      THEN ARRAY['QSFP+']
               WHEN 'OSFP224'    THEN ARRAY['OSFP224','OSFP112','OSFP']
               WHEN 'OSFP112'    THEN ARRAY['OSFP112','OSFP']
               WHEN 'OSFP'       THEN ARRAY['OSFP']
               WHEN 'SFP56'      THEN ARRAY['SFP56','SFP28','SFP+','SFP']
               WHEN 'SFP28'      THEN ARRAY['SFP28','SFP+','SFP']
               WHEN 'SFP+'       THEN ARRAY['SFP+','SFP']
               WHEN 'CFP2'       THEN ARRAY['CFP2']
               WHEN 'CFP4'       THEN ARRAY['CFP4']
               WHEN 'CFP'        THEN ARRAY['CFP']
               WHEN 'RJ45'       THEN ARRAY['RJ45','Copper']
               ELSE ARRAY[]::text[]
             END
           )
           -- speed must not exceed the port's speed (slower module in faster cage OK)
           AND t.speed_gbps <= sp.port_speed
       )
       -- AND Flexoptix must be able to code it for THIS switch's platform, so it
       -- actually runs (not just physically fits). Whitebox/unmapped -> MSA standard.
       AND EXISTS (
         SELECT 1 FROM jsonb_array_elements(t.fx_compatibilities) c, sw_vendor
         WHERE (sw_vendor.vpat IS NOT NULL AND c->>'compatible_to_vendor' ILIKE '%' || sw_vendor.vpat || '%')
            OR (sw_vendor.vpat IS NULL AND c->>'compatible_to_vendor' ILIKE '%MSA Standard%')
       )
     ORDER BY t.speed_gbps DESC NULLS LAST, t.reach_meters ASC NULLS LAST`,
    [switchId]
  );
  return result.rows;
}

export async function listVendors(type?: string) {
  const query = type
    ? `SELECT v.*,
         (SELECT COUNT(*) FROM transceivers t WHERE t.vendor_id = v.id) as transceiver_count,
         (SELECT COUNT(*) FROM switches s WHERE s.vendor_id = v.id) as switch_count
       FROM vendors v WHERE v.type = $1 ORDER BY v.name`
    : `SELECT v.*,
         (SELECT COUNT(*) FROM transceivers t WHERE t.vendor_id = v.id) as transceiver_count,
         (SELECT COUNT(*) FROM switches s WHERE s.vendor_id = v.id) as switch_count
       FROM vendors v ORDER BY v.name`;
  const result = await pool.query(query, type ? [type] : []);
  return result.rows;
}

export async function listStandards(speed?: string) {
  const base = `
    SELECT s.*,
           COUNT(t.id)::int AS transceiver_count
    FROM   standards s
    LEFT   JOIN transceivers t ON t.standard_id = s.id`;
  const query = speed
    ? `${base} WHERE s.speed = $1 GROUP BY s.id ORDER BY s.speed_gbps DESC NULLS LAST, s.name`
    : `${base} GROUP BY s.id ORDER BY s.speed_gbps DESC NULLS LAST, s.name`;
  const result = await pool.query(query, speed ? [speed] : []);
  return result.rows;
}

export async function getDbStats() {
  const result = await pool.query(`
    SELECT
      (SELECT COUNT(*) FROM vendors) as vendor_count,
      (SELECT COUNT(*) FROM standards) as standard_count,
      (SELECT COUNT(*) FROM transceivers) as transceiver_count,
      (SELECT COUNT(*) FROM switches) as switch_count,
      (SELECT COUNT(*) FROM compatibility) as compatibility_count,
      (SELECT COUNT(*) FROM breakouts) as breakout_count,
      (SELECT COUNT(*) FROM knowledge_base) as kb_count,
      (SELECT COUNT(*) FROM documents) as document_count,
      (SELECT COUNT(*) FROM news_articles) as news_count,
      (SELECT COUNT(*) FROM product_documents) as product_document_count,
      (SELECT COUNT(*) FROM switches WHERE image_url IS NOT NULL) as switches_with_images,
      (SELECT COUNT(*) FROM switches WHERE datasheet_r2_key IS NOT NULL) as switches_with_datasheets
  `);
  return result.rows[0];
}
