/**
 * Market intelligence tools: get_cable_recommendations, get_market_overview, get_technology_roadmap
 */
import type { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { z } from "zod";
import { pool } from "../db.js";

export async function registerMarketTools(server: McpServer): Promise<void> {
  // --- Tool: get_cable_recommendations ---
  server.tool(
    "get_cable_recommendations",
    "Recommend DAC (Direct Attach Copper) or AOC (Active Optical Cable) cables based on switch ports and distance requirements. Considers port type, speed, reach, and budget to suggest the optimal cable solution.",
    {
      switch_model: z.string().optional().describe("Switch model for port compatibility check, e.g. 'Nexus 93180YC-FX3'"),
      port_speed_gbps: z.number().describe("Required port speed in Gbps: 10, 25, 40, 100, 200, 400"),
      distance_meters: z.number().describe("Required cable run distance in meters (0.5 to 100)"),
      form_factor: z.string().optional().describe("Port form factor: SFP+, SFP28, QSFP+, QSFP28, QSFP-DD, OSFP. Auto-detected from speed if omitted."),
      environment: z.enum(["rack", "row", "room"]).default("rack").describe("rack = <3m, row = 3-10m, room = 10-100m"),
      budget_preference: z.enum(["cheapest", "balanced", "best_quality"]).default("balanced"),
    },
    async ({ switch_model, port_speed_gbps, distance_meters, form_factor, environment, budget_preference }) => {
      // Auto-detect form factor from speed if not provided
      const ffMap: Record<number, string[]> = {
        10: ["SFP+"],
        25: ["SFP28"],
        40: ["QSFP+"],
        100: ["QSFP28", "QSFP-DD"],
        200: ["QSFP56", "QSFP-DD"],
        400: ["QSFP-DD", "OSFP"],
        800: ["QSFP-DD800", "OSFP"],
      };
      const targetFormFactors = form_factor ? [form_factor] : (ffMap[port_speed_gbps] || ["QSFP28"]);

      // Determine cable type recommendation
      const cableType = distance_meters <= 5 ? "DAC (recommended)" :
                         distance_meters <= 7 ? "DAC or AOC" :
                         distance_meters <= 100 ? "AOC (required)" :
                         "Optical transceiver + fiber required";

      const isDACViable = distance_meters <= 7;
      const isAOCViable = distance_meters >= 1 && distance_meters <= 100;

      // Search for matching cables
      const cableCategories = [];
      if (isDACViable) cableCategories.push("DAC");
      if (isAOCViable) cableCategories.push("AOC");

      const ffPlaceholders = targetFormFactors.map((_, i) => `$${i + 2}`).join(",");
      const catPlaceholders = cableCategories.map((_, i) => `$${i + 2 + targetFormFactors.length}`).join(",");

      const values: unknown[] = [
        port_speed_gbps,
        ...targetFormFactors,
        ...cableCategories,
      ];

      const result = await pool.query(
        `SELECT t.id, t.slug, t.standard_name, t.form_factor, t.speed, t.speed_gbps,
                t.reach_label, t.reach_meters, t.category, t.connector, t.power_consumption_w,
                v.name as vendor, v.type as vendor_type,
                (SELECT MIN(po.price) FROM price_observations po
                 WHERE po.transceiver_id = t.id AND po.time > NOW() - INTERVAL '7 days'
                ) as min_price,
                (SELECT MAX(po.price) FROM price_observations po
                 WHERE po.transceiver_id = t.id AND po.time > NOW() - INTERVAL '7 days'
                ) as max_price,
                (SELECT COUNT(DISTINCT po.source_vendor_id) FROM price_observations po
                 WHERE po.transceiver_id = t.id AND po.stock_level = 'in_stock'
                   AND po.time > NOW() - INTERVAL '7 days'
                ) as in_stock_vendors
         FROM transceivers t
         LEFT JOIN vendors v ON v.id = t.vendor_id
         WHERE t.speed_gbps = $1
           AND t.form_factor IN (${ffPlaceholders})
           AND t.category IN (${catPlaceholders})
           AND t.reach_meters >= ${distance_meters}
         ORDER BY
           CASE '${budget_preference}'
             WHEN 'cheapest' THEN (SELECT MIN(po.price) FROM price_observations po WHERE po.transceiver_id = t.id AND po.time > NOW() - INTERVAL '7 days')
             WHEN 'best_quality' THEN -t.reach_meters
             ELSE t.reach_meters
           END ASC NULLS LAST
         LIMIT 15`,
        values
      );

      // Check switch compatibility if model provided
      let switchCompat: unknown[] = [];
      if (switch_model && result.rows.length > 0) {
        const txIds = result.rows.map(r => r.id);
        const switchRes = await pool.query(
          `SELECT c.transceiver_id, c.status, c.firmware_min, sw.model
           FROM compatibility c
           JOIN switches sw ON sw.id = c.switch_id
           WHERE sw.model ILIKE $1
             AND c.transceiver_id = ANY($2)`,
          [`%${switch_model}%`, txIds]
        );
        switchCompat = switchRes.rows;
      }

      const compatMap = new Map((switchCompat as any[]).map(c => [c.transceiver_id, c]));

      const recommendations = result.rows.map(r => ({
        slug: r.slug,
        standard: r.standard_name,
        form_factor: r.form_factor,
        speed: r.speed,
        category: r.category,
        reach_meters: r.reach_meters,
        reach_label: r.reach_label,
        connector: r.connector,
        power_w: r.power_consumption_w,
        vendor: r.vendor,
        pricing: {
          min_price: r.min_price ? parseFloat(r.min_price) : null,
          max_price: r.max_price ? parseFloat(r.max_price) : null,
          in_stock_vendors: parseInt(r.in_stock_vendors) || 0,
        },
        switch_compatible: switch_model
          ? (compatMap.has(r.id) ? (compatMap.get(r.id) as any).status : "not_verified")
          : undefined,
        firmware_min: switch_model && compatMap.has(r.id)
          ? (compatMap.get(r.id) as any).firmware_min
          : undefined,
      }));

      return {
        content: [{
          type: "text",
          text: JSON.stringify({
            request: {
              speed_gbps: port_speed_gbps,
              distance_meters,
              form_factors: targetFormFactors,
              environment,
              budget: budget_preference,
              switch_model: switch_model || null,
            },
            recommendation: {
              cable_type: cableType,
              dac_viable: isDACViable,
              aoc_viable: isAOCViable,
              rationale: isDACViable && distance_meters <= 3
                ? "DAC is the cheapest and lowest-latency option for short rack-level runs. No optics or fiber needed."
                : isDACViable
                ? "DAC works at this distance but is near its limit. AOC provides more headroom."
                : isAOCViable
                ? "AOC is required at this distance. DAC copper cannot reliably reach beyond 7m."
                : "Neither DAC nor AOC can cover this distance. Use optical transceivers (SR/LR) with fiber patch cables.",
            },
            cables: recommendations,
            count: recommendations.length,
            tips: [
              isDACViable ? "DAC cables are passive and draw zero power from the switch. Ideal for ToR deployments." : null,
              isAOCViable ? "AOC cables are lighter and more flexible than DAC. Better for cable management in dense environments." : null,
              distance_meters > 100 ? "For distances > 100m, use SR transceivers with OM3/OM4 MMF fiber." : null,
              distance_meters > 300 ? "For distances > 300m, use LR transceivers with OS2 SMF fiber." : null,
            ].filter(Boolean),
          }, null, 2),
        }],
      };
    }
  );

  // --- Tool: get_market_overview ---
  server.tool(
    "get_market_overview",
    "Get market overview statistics: vendor counts, price ranges, technology distribution by form factor. Useful for market analysis, sales strategy, and inventory planning.",
    {
      form_factor: z.string().optional().describe("Filter by form factor: SFP+, SFP28, QSFP28, QSFP-DD, OSFP, etc."),
      speed_gbps: z.number().optional().describe("Filter by speed in Gbps"),
      include_vendor_breakdown: z.boolean().default(true).describe("Include per-vendor statistics"),
      include_price_tiers: z.boolean().default(true).describe("Include price tier analysis (Budget/Standard/Premium)"),
    },
    async ({ form_factor, speed_gbps, include_vendor_breakdown, include_price_tiers }) => {
      const conditions: string[] = [];
      const values: unknown[] = [];
      let idx = 1;

      if (form_factor) {
        conditions.push(`t.form_factor ILIKE $${idx}`);
        values.push(`%${form_factor}%`);
        idx++;
      }
      if (speed_gbps) {
        conditions.push(`t.speed_gbps = $${idx}`);
        values.push(speed_gbps);
        idx++;
      }

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

      // Overall stats
      const overallResult = await pool.query(
        `SELECT
           COUNT(*) as total_transceivers,
           COUNT(DISTINCT t.form_factor) as form_factor_count,
           COUNT(DISTINCT t.vendor_id) as vendor_count,
           COUNT(DISTINCT t.speed_gbps) as speed_variants,
           MIN(t.speed_gbps) as min_speed_gbps,
           MAX(t.speed_gbps) as max_speed_gbps,
           COUNT(*) FILTER (WHERE t.market_status = 'Mainstream') as mainstream_count,
           COUNT(*) FILTER (WHERE t.market_status = 'Growth') as growth_count,
           COUNT(*) FILTER (WHERE t.market_status = 'Emerging') as emerging_count,
           COUNT(*) FILTER (WHERE t.market_status = 'Legacy') as legacy_count,
           COUNT(*) FILTER (WHERE t.market_status = 'EOL') as eol_count
         FROM transceivers t
         ${where}`,
        values
      );

      // Form factor distribution
      const ffResult = await pool.query(
        `SELECT t.form_factor,
                COUNT(*) as count,
                ARRAY_AGG(DISTINCT t.speed_gbps ORDER BY t.speed_gbps) as speeds,
                COUNT(DISTINCT t.vendor_id) as vendors
         FROM transceivers t
         ${where}
         GROUP BY t.form_factor
         ORDER BY COUNT(*) DESC`,
        values
      );

      // Price ranges by form factor
      let priceRanges = null;
      if (include_price_tiers) {
        const priceResult = await pool.query(
          `SELECT t.form_factor, t.speed_gbps,
                  MIN(po.price)::numeric(10,2) as min_price,
                  AVG(po.price)::numeric(10,2) as avg_price,
                  MAX(po.price)::numeric(10,2) as max_price,
                  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY po.price)::numeric(10,2) as p25,
                  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY po.price)::numeric(10,2) as median,
                  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY po.price)::numeric(10,2) as p75,
                  COUNT(DISTINCT po.source_vendor_id) as vendor_count,
                  COUNT(*) as observation_count
           FROM price_observations po
           JOIN transceivers t ON t.id = po.transceiver_id
           WHERE po.time > NOW() - INTERVAL '30 days'
             ${form_factor ? `AND t.form_factor ILIKE $${idx}` : ''}
             ${speed_gbps ? `AND t.speed_gbps = $${idx + (form_factor ? 1 : 0)}` : ''}
           GROUP BY t.form_factor, t.speed_gbps
           ORDER BY t.speed_gbps DESC, t.form_factor`,
          form_factor ? (speed_gbps ? [`%${form_factor}%`, speed_gbps] : [`%${form_factor}%`])
                      : (speed_gbps ? [speed_gbps] : [])
        );
        priceRanges = priceResult.rows;
      }

      // Vendor breakdown
      let vendorBreakdown = null;
      if (include_vendor_breakdown) {
        const vendorResult = await pool.query(
          `SELECT v.name, v.type, v.country, v.is_competitor,
                  COUNT(DISTINCT t.id) as transceiver_count,
                  ARRAY_AGG(DISTINCT t.form_factor ORDER BY t.form_factor) as form_factors,
                  COUNT(DISTINCT t.speed_gbps) as speed_variants
           FROM vendors v
           JOIN transceivers t ON t.vendor_id = v.id
           ${where ? where.replace('WHERE', 'WHERE') : ''}
           GROUP BY v.id, v.name, v.type, v.country, v.is_competitor
           ORDER BY COUNT(DISTINCT t.id) DESC
           LIMIT 25`,
          values
        );
        vendorBreakdown = vendorResult.rows;
      }

      // Technology trends from market_metrics
      const trendResult = await pool.query(
        `SELECT mm.technology, mm.metric_type,
                mm.value, mm.source, mm.time
         FROM market_metrics mm
         WHERE mm.time > NOW() - INTERVAL '90 days'
         ORDER BY mm.time DESC
         LIMIT 20`
      );

      return {
        content: [{
          type: "text",
          text: JSON.stringify({
            overview: overallResult.rows[0],
            form_factor_distribution: ffResult.rows,
            price_ranges: priceRanges,
            vendor_breakdown: vendorBreakdown,
            recent_market_metrics: trendResult.rows,
            generated_at: new Date().toISOString(),
          }, null, 2),
        }],
      };
    }
  );

  // --- Tool: get_technology_roadmap ---
  server.tool(
    "get_technology_roadmap",
    "Get technology lifecycle roadmap with hype cycle positioning, Norton-Bass adoption data, and timeline projections. Essential for technology planning and investment decisions.",
    {
      technology: z.string().optional().describe("Specific technology to analyze, e.g. '800G', 'CPO', '400ZR', 'DWDM'. Omit for full roadmap."),
      category: z.enum(["transceiver", "technology", "all"]).default("all").describe("Filter by category"),
      include_market_data: z.boolean().default(true).describe("Include market metrics (shipments, ASP, revenue)"),
      include_news: z.boolean().default(true).describe("Include recent news for each technology"),
    },
    async ({ technology, category, include_market_data, include_news }) => {
      // Get lifecycle data
      const lcConditions: string[] = [];
      const lcValues: unknown[] = [];
      let idx = 1;

      if (technology) {
        lcConditions.push(`tl.technology ILIKE $${idx}`);
        lcValues.push(`%${technology}%`);
        idx++;
      }
      if (category !== "all") {
        lcConditions.push(`tl.category = $${idx}`);
        lcValues.push(category);
        idx++;
      }

      const lcWhere = lcConditions.length > 0 ? `WHERE ${lcConditions.join(" AND ")}` : "";

      const lifecycleResult = await pool.query(
        `SELECT tl.technology, tl.category, tl.current_phase,
                tl.introduction_year, tl.early_adoption_year,
                tl.mainstream_year, tl.peak_year, tl.decline_year,
                tl.adoption_percent, tl.notes
         FROM technology_lifecycle tl
         ${lcWhere}
         ORDER BY
           CASE tl.current_phase
             WHEN 'trigger' THEN 1
             WHEN 'peak_hype' THEN 2
             WHEN 'trough' THEN 3
             WHEN 'early_adoption' THEN 4
             WHEN 'slope_of_enlightenment' THEN 5
             WHEN 'early_mainstream' THEN 6
             WHEN 'mainstream' THEN 7
             WHEN 'plateau' THEN 8
             WHEN 'decline' THEN 9
           END ASC`,
        lcValues
      );

      // Enrich each technology with market data and news
      const technologies = await Promise.all(lifecycleResult.rows.map(async (lc) => {
        const enriched: Record<string, unknown> = {
          technology: lc.technology,
          category: lc.category,
          current_phase: lc.current_phase,
          adoption_percent: lc.adoption_percent,
          timeline: {
            introduction: lc.introduction_year,
            early_adoption: lc.early_adoption_year,
            mainstream: lc.mainstream_year,
            peak: lc.peak_year,
            decline: lc.decline_year,
          },
          notes: lc.notes,
        };

        // Phase description mapping
        const phaseDescriptions: Record<string, string> = {
          trigger: "Innovation Trigger — Technology just announced or in labs. High risk, potentially transformative.",
          peak_hype: "Peak of Inflated Expectations — Massive buzz, early commercial products, inflated claims.",
          trough: "Trough of Disillusionment — Initial hype faded, real challenges exposed. Best time to invest.",
          early_adoption: "Early Adoption — Proven technology, early mainstream deployments beginning.",
          slope_of_enlightenment: "Slope of Enlightenment — Use cases clear, ecosystem growing, prices dropping.",
          early_mainstream: "Early Mainstream — Widely deployed, multiple vendors, competitive pricing.",
          mainstream: "Mainstream — Dominant technology in its segment. Commoditized.",
          plateau: "Plateau of Productivity — Mature, fully commoditized, being displaced by next gen.",
          decline: "Decline — Being replaced by newer technology. Consider migration planning.",
        };
        enriched.phase_description = phaseDescriptions[lc.current_phase] || "Unknown phase";

        // Investment recommendation
        const investmentMap: Record<string, string> = {
          trigger: "WATCH — Too early for production. Monitor for breakthroughs.",
          peak_hype: "EVALUATE — Test in lab environments. Do not commit large budgets.",
          trough: "INVEST — Best price-to-value window. Early mover advantage.",
          early_adoption: "INVEST — Strong time to adopt. Good vendor support, dropping prices.",
          slope_of_enlightenment: "BUY — Excellent time to deploy. Proven, prices declining.",
          early_mainstream: "BUY — Optimal cost/performance. Multiple vendor options.",
          mainstream: "MAINTAIN — Standard choice. Commoditized pricing.",
          plateau: "PLAN MIGRATION — Start evaluating next-gen alternatives.",
          decline: "MIGRATE — Actively transition to successor technology.",
        };
        enriched.investment_recommendation = investmentMap[lc.current_phase] || "EVALUATE";

        // Market data
        if (include_market_data) {
          const metricsResult = await pool.query(
            `SELECT mm.metric_type, mm.value, mm.source, mm.time, mm.notes
             FROM market_metrics mm
             WHERE mm.technology ILIKE $1
             ORDER BY mm.metric_type, mm.time DESC
             LIMIT 20`,
            [`%${lc.technology.split(' ')[0]}%`]
          );
          enriched.market_data = metricsResult.rows;
        }

        // Recent news
        if (include_news) {
          const newsResult = await pool.query(
            `SELECT na.title, na.source, na.published_at, na.summary, na.relevance_score
             FROM news_articles na
             WHERE na.search_vector @@ plainto_tsquery('english', $1)
                OR na.title ILIKE $2
             ORDER BY na.published_at DESC
             LIMIT 3`,
            [lc.technology, `%${lc.technology.split('(')[0].trim()}%`]
          );
          enriched.recent_news = newsResult.rows;
        }

        // Transceiver count in this technology segment
        const txCountResult = await pool.query(
          `SELECT COUNT(*) as count,
                  COUNT(*) FILTER (WHERE market_status = 'Mainstream') as mainstream,
                  COUNT(*) FILTER (WHERE market_status IN ('Growth', 'Emerging')) as growing
           FROM transceivers
           WHERE standard_name ILIKE $1
              OR speed ILIKE $1
              OR category ILIKE $1`,
          [`%${lc.technology.split(' ')[0]}%`]
        );
        enriched.transceiver_stats = txCountResult.rows[0];

        return enriched;
      }));

      // Build hype cycle visualization data (position on the curve)
      const hypePositions = technologies.map(t => ({
        technology: t.technology,
        phase: t.current_phase,
        adoption_pct: t.adoption_percent,
        recommendation: t.investment_recommendation,
      }));

      return {
        content: [{
          type: "text",
          text: JSON.stringify({
            roadmap: technologies,
            hype_cycle_summary: hypePositions,
            total_technologies: technologies.length,
            generated_at: new Date().toISOString(),
            note: "Lifecycle data is updated quarterly based on market research, vendor announcements, and pricing trends. Norton-Bass forecasting (Phase 3) will add quantitative adoption predictions.",
          }, null, 2),
        }],
      };
    }
  );
}
