/**
 * TIP Forecast Engine
 *
 * Aggregates all prediction signals into demand index + price forecast
 * per technology, for 3/9/12/18 month horizons.
 *
 * Signal weights (empirically estimated, tunable):
 *
 *   Signal Type              | Weight | Lead Time | Notes
 *   ─────────────────────────┼────────┼───────────┼──────────────────────────
 *   Hyperscaler CapEx YoY    | 0.30   | 6-12 mo   | Strongest single signal
 *   AI Cluster Announcements | 0.25   | 3-9 mo    | Direct DC build demand
 *   eBay Sold Velocity       | 0.20   | 1-3 mo    | Actual market demand now
 *   Distributor Lead Times   | 0.15   | 2-4 mo    | Supply constraint proxy
 *   GitHub Tech Signals      | 0.06   | 6-18 mo   | Enterprise adoption rate
 *   Standards Status         | 0.04   | 12-24 mo  | Long-horizon technology wave
 *
 * Forecast output:
 *   demand_index  — 0-100 composite score
 *   price_direction — 'rising'|'falling'|'stable'
 *   price_delta_pct — estimated % change over horizon
 *   confidence  — 0.0-1.0 based on how many signals available
 */

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

const TECHNOLOGIES = [
  "100G-QSFP28",
  "400G-QSFP-DD",
  "400G-ZR",
  "800G-OSFP",
  "10G-SFP+",
];

const TECH_KEYWORDS: Record<string, { speeds: string[]; formFactors: string[]; githubKeys: string[] }> = {
  "100G-QSFP28":  { speeds: ["100G"],  formFactors: ["QSFP28"],  githubKeys: ["400G"] }, // 100G still riding 400G wave
  "400G-QSFP-DD": { speeds: ["400G"],  formFactors: ["QSFP-DD", "QSFP28"], githubKeys: ["400G", "OSFP"] },
  "400G-ZR":      { speeds: ["400G-ZR","400G"], formFactors: ["QSFP-DD"], githubKeys: ["ZR", "400G"] },
  "800G-OSFP":    { speeds: ["800G"],  formFactors: ["OSFP", "QSFP-DD"],  githubKeys: ["800G", "OSFP"] },
  "10G-SFP+":     { speeds: ["10G"],   formFactors: ["SFP+"],  githubKeys: [] },
};

interface SignalBundle {
  capexYoyPct:         number | null;  // latest hyperscaler YoY CapEx growth
  aiAnnouncementsQty:  number;         // AI cluster announcements last 90 days
  aiEstimatedTcvrs:    number;         // total estimated transceivers from announcements
  ebayVelocity:        number | null;  // sold count last measurement
  ebayActiveListings:  number | null;
  avgLeadTimeWeeks:    number | null;  // average distributor lead time
  inStockPct:          number | null;  // % of distributors showing in stock
  githubRepoCount:     number | null;  // total repos for tech keywords
  standardsApproved:   number;         // how many relevant standards published
  standardsInProgress: number;         // how many relevant standards in progress
}

async function gatherSignals(tech: string): Promise<SignalBundle> {
  const keys = TECH_KEYWORDS[tech];
  const speedList = keys.speeds.map(s => `'${s}'`).join(",");
  const formList  = keys.formFactors.map(f => `'${f}'`).join(",");

  // Hyperscaler CapEx — most recent quarter's YoY growth across all companies
  const capexRow = await pool.query<{ avg_yoy: number }>(`
    SELECT AVG(yoy_growth_pct) AS avg_yoy
    FROM hyperscaler_capex
    WHERE period_end > NOW() - INTERVAL '9 months'
      AND yoy_growth_pct IS NOT NULL
  `);
  const capexYoyPct = capexRow.rows[0]?.avg_yoy ?? null;

  // AI cluster announcements — last 90 days
  const aiRow = await pool.query<{ qty: number; est_tcvrs: number }>(`
    SELECT COUNT(*) AS qty, COALESCE(SUM(estimated_transceivers), 0) AS est_tcvrs
    FROM ai_cluster_announcements
    WHERE announced_date > NOW() - INTERVAL '90 days'
      AND (network_speed LIKE '%${tech.includes("800G") ? "800" : "400"}%'
           OR network_speed IS NULL)
  `);
  const aiAnnouncementsQty = parseInt(String(aiRow.rows[0]?.qty ?? 0));
  const aiEstimatedTcvrs   = parseInt(String(aiRow.rows[0]?.est_tcvrs ?? 0));

  // eBay velocity — latest measurement matching form factor
  const ebayRow = await pool.query<{ sold: number; active: number }>(`
    SELECT AVG(sold_count_30d) AS sold, AVG(active_listings) AS active
    FROM marketplace_velocity
    WHERE scraped_at > NOW() - INTERVAL '7 days'
      AND (speed_label IN (${speedList}) OR form_factor IN (${formList}))
  `);
  const ebayVelocity       = ebayRow.rows[0]?.sold ?? null;
  const ebayActiveListings = ebayRow.rows[0]?.active ?? null;

  // Distributor lead times
  const distRow = await pool.query<{ avg_lead: number; in_stock_pct: number }>(`
    SELECT
      AVG(lead_time_weeks) FILTER (WHERE lead_time_weeks IS NOT NULL) AS avg_lead,
      100.0 * COUNT(*) FILTER (WHERE in_stock = true) / NULLIF(COUNT(*), 0) AS in_stock_pct
    FROM distributor_lead_times
    WHERE scraped_at > NOW() - INTERVAL '14 days'
      AND (form_factor IN (${formList}) OR speed_label IN (${speedList}))
  `);
  const avgLeadTimeWeeks = distRow.rows[0]?.avg_lead ?? null;
  const inStockPct       = distRow.rows[0]?.in_stock_pct ?? null;

  // GitHub signals
  let githubRepoCount: number | null = null;
  if (keys.githubKeys.length > 0) {
    const ghKeys = keys.githubKeys.map(k => `'${k}'`).join(",");
    const ghRow = await pool.query<{ total: number }>(`
      SELECT SUM(value) AS total
      FROM github_tech_signals
      WHERE technology IN (${ghKeys})
        AND metric = 'repo_count'
        AND week_start >= NOW() - INTERVAL '14 days'
    `);
    githubRepoCount = ghRow.rows[0]?.total ?? null;
  }

  // Standards status
  const stdSpeeds = keys.speeds.map(s => `'${s}'`).join(",");
  const stdRow = await pool.query<{ approved: number; in_progress: number }>(`
    SELECT
      COUNT(*) FILTER (WHERE current_status = 'published') AS approved,
      COUNT(*) FILTER (WHERE current_status IN ('in-progress', 'ballot')) AS in_progress
    FROM standards_activity
    WHERE technology IN (${stdSpeeds})
       OR standard_name LIKE '%${keys.speeds[0] ?? ""}%'
  `);
  const standardsApproved    = parseInt(String(stdRow.rows[0]?.approved ?? 0));
  const standardsInProgress  = parseInt(String(stdRow.rows[0]?.in_progress ?? 0));

  return {
    capexYoyPct, aiAnnouncementsQty, aiEstimatedTcvrs,
    ebayVelocity, ebayActiveListings,
    avgLeadTimeWeeks, inStockPct,
    githubRepoCount,
    standardsApproved, standardsInProgress,
  };
}

function computeDemandIndex(s: SignalBundle, horizonMonths: number): {
  index: number;
  priceDirection: string;
  priceDeltaPct: number;
  confidence: number;
  breakdown: Record<string, number>;
} {
  let score = 50; // neutral baseline
  let signalCount = 0;
  const breakdown: Record<string, number> = {};

  // 1. Hyperscaler CapEx (weight 0.30) — primary signal
  if (s.capexYoyPct !== null) {
    const capexScore = Math.min(100, Math.max(0, 50 + s.capexYoyPct * 1.2));
    score += (capexScore - 50) * 0.30;
    breakdown.capex = capexScore;
    signalCount++;
  }

  // 2. AI Cluster Announcements (weight 0.25)
  if (s.aiAnnouncementsQty > 0) {
    // > 20 announcements in 90 days = max signal
    const aiScore = Math.min(100, 50 + s.aiAnnouncementsQty * 2.5);
    score += (aiScore - 50) * 0.25;
    breakdown.ai_clusters = aiScore;
    signalCount++;
  }

  // 3. eBay Velocity (weight 0.20) — higher velocity = more demand
  if (s.ebayVelocity !== null && s.ebayActiveListings !== null) {
    const ratio = s.ebayActiveListings > 0
      ? (s.ebayVelocity / s.ebayActiveListings) * 100
      : 0;
    // > 30% sell-through = strong demand
    const ebayScore = Math.min(100, Math.max(0, 50 + (ratio - 15) * 1.5));
    score += (ebayScore - 50) * 0.20;
    breakdown.ebay_velocity = ebayScore;
    signalCount++;
  }

  // 4. Distributor Lead Times (weight 0.15) — longer lead = tighter supply = higher prices
  if (s.avgLeadTimeWeeks !== null) {
    // 4 weeks = normal, 12+ weeks = constrained
    const leadScore = Math.min(100, Math.max(0, 50 + (s.avgLeadTimeWeeks - 4) * 4));
    score += (leadScore - 50) * 0.15;
    breakdown.lead_times = leadScore;
    signalCount++;

    // Invert for long horizon: tight supply now = more capacity investment = prices drop later
    if (horizonMonths >= 12 && s.avgLeadTimeWeeks > 12) {
      score -= 5; // price correction expected
    }
  }

  // 5. GitHub signals (weight 0.06)
  if (s.githubRepoCount !== null) {
    const ghScore = Math.min(100, Math.max(0, 40 + Math.log10(Math.max(1, s.githubRepoCount)) * 12));
    score += (ghScore - 50) * 0.06;
    breakdown.github = ghScore;
    signalCount++;
  }

  // 6. Standards status (weight 0.04)
  if (s.standardsApproved > 0 || s.standardsInProgress > 0) {
    // Published standards = late-game adoption = price pressure down
    // In-progress = early game = price still rising
    const stdScore = s.standardsApproved > 2 ? 35 : (s.standardsInProgress > 2 ? 65 : 50);
    score += (stdScore - 50) * 0.04;
    breakdown.standards = stdScore;
    signalCount++;
  }

  const demandIndex = Math.round(Math.min(100, Math.max(0, score)) * 10) / 10;

  // Price direction logic
  let priceDirection = "stable";
  let priceDeltaPct = 0;

  if (demandIndex > 65) {
    priceDirection = "rising";
    priceDeltaPct = Math.round((demandIndex - 50) * 0.4 * 10) / 10;
  } else if (demandIndex < 40) {
    priceDirection = "falling";
    priceDeltaPct = -Math.round((50 - demandIndex) * 0.35 * 10) / 10;
  }

  // Adjust price delta by horizon (longer = larger moves)
  const horizonMultiplier = Math.sqrt(horizonMonths / 3);
  priceDeltaPct = Math.round(priceDeltaPct * horizonMultiplier * 10) / 10;

  const confidence = Math.min(1.0, signalCount / 6);

  return { index: demandIndex, priceDirection, priceDeltaPct, confidence, breakdown };
}

export async function runForecastEngine(): Promise<void> {
  logger.info("Forecast engine starting");

  const horizons = [3, 9, 12, 18];
  let computed = 0;

  for (const tech of TECHNOLOGIES) {
    try {
      const signals = await gatherSignals(tech);

      for (const horizon of horizons) {
        const forecast = computeDemandIndex(signals, horizon);

        await pool.query(`
          INSERT INTO forecast_signals
            (technology, horizon_months, demand_index, price_direction,
             price_delta_pct, confidence, signal_count, signal_breakdown, forecast_date)
          VALUES ($1, $2, $3, $4, $5, $6, $7, $8, CURRENT_DATE)
          ON CONFLICT (technology, horizon_months, forecast_date)
          DO UPDATE SET
            demand_index     = EXCLUDED.demand_index,
            price_direction  = EXCLUDED.price_direction,
            price_delta_pct  = EXCLUDED.price_delta_pct,
            confidence       = EXCLUDED.confidence,
            signal_count     = EXCLUDED.signal_count,
            signal_breakdown = EXCLUDED.signal_breakdown,
            computed_at      = NOW()
        `, [
          tech, horizon, forecast.index, forecast.priceDirection,
          forecast.priceDeltaPct, forecast.confidence,
          forecast.breakdown ? Object.keys(forecast.breakdown).length : 0,
          JSON.stringify(forecast.breakdown),
        ]);
        computed++;

        logger.info(
          `${tech} +${horizon}mo: demand=${forecast.index}/100, ` +
          `price=${forecast.priceDirection} ${forecast.priceDeltaPct > 0 ? "+" : ""}${forecast.priceDeltaPct}%, ` +
          `confidence=${Math.round(forecast.confidence * 100)}%`
        );
      }
    } catch (err) {
      logger.error(`Forecast engine failed for ${tech}`, { err });
    }
  }

  logger.info(`Forecast engine done — ${computed} forecasts computed`);
}
