/**
 * Stock Velocity Analyzer — Abverkauf & Zulauf Evaluation
 *
 * Processes time-series stock_observations to compute:
 *   • avg_daily_sell_rate  — implied units sold per day
 *   • total_units_sold     — implied cumulative sold in window
 *   • Zulauf events        — when and how much stock was replenished
 *   • estimated_stockout_date — when current stock is expected to run out
 *
 * Data sources ranked by confidence:
 *   3 = FS.com   — per-warehouse breakdown + units_sold counter
 *   2 = QSFPTEK  — global real-time quantity
 *   1 = ATGBICS/Optcore — binary in/out stock only (skipped for velocity)
 *
 * Velocity is only computed for confidence ≥ 2 sources.
 *
 * Called by pg-boss job "analyze:stock:velocity".
 */

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

// ── Config ────────────────────────────────────────────────────────────────────

const MIN_OBS_FOR_VELOCITY = 2;       // absolute minimum observations
const MAX_INTERVAL_HOURS = 96;         // ignore gaps > 4 days (data outage, not a sale)
const MIN_INTERVAL_HOURS = 0.4;        // ignore observations < 24min apart (duplicates)
const WINDOW_DAYS = 30;               // look back this many days for velocity calc

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

interface StockObs {
  time: Date;
  physicalQty: number;       // warehouse_de_qty + warehouse_global_qty
  quantityAvailable: number | null;
  backorderQty: number | null;
  backorderEstimatedDate: string | null;
  unitsSold: number | null;
  priceNet: number | null;
}

interface VelocityEvent {
  transceiverId: string;
  vendorId: string;
  eventAt: Date;
  eventType: "sold" | "zulauf" | "unchanged" | "data_gap";
  unitsDelta: number;
  dailyRate: number | null;
  qtyBefore: number;
  qtyAfter: number;
  hoursElapsed: number;
}

interface VelocityResult {
  transceiverId: string;
  vendorId: string;
  windowStart: Date;
  windowEnd: Date;
  obsCount: number;
  avgDailySellRate: number | null;
  peakDailySellRate: number | null;
  totalSellEvents: number;
  totalUnitsSoldImplied: number;
  unitsSoldCounterDelta: number | null;
  unitsSoldDailyRate: number | null;
  totalZulaufEvents: number;
  totalUnitsZulauf: number;
  lastZulaufAt: Date | null;
  nextExpectedDelivery: string | null;
  currentQty: number | null;
  currentBackorderQty: number | null;
  currentPriceNet: number | null;
  estimatedStockoutDays: number | null;
  estimatedStockoutDate: Date | null;
  velocityConfidence: "high" | "medium" | "low" | "insufficient";
  events: VelocityEvent[];
}

// ── Core velocity computation ──────────────────────────────────────────────────

function computeVelocity(
  transceiverId: string,
  vendorId: string,
  observations: StockObs[]
): VelocityResult {
  const sorted = [...observations].sort((a, b) => a.time.getTime() - b.time.getTime());

  const windowStart = sorted[0].time;
  const windowEnd   = sorted[sorted.length - 1].time;
  const windowDays  = Math.max(1, (windowEnd.getTime() - windowStart.getTime()) / 86400000);

  const latest = sorted[sorted.length - 1];
  const earliest = sorted[0];

  // ── FS.com units_sold counter delta (most reliable) ──────────────────────
  let unitsSoldCounterDelta: number | null = null;
  let unitsSoldDailyRate: number | null = null;

  const firstWithSold = sorted.find((o) => o.unitsSold !== null && o.unitsSold > 0);
  const lastWithSold  = [...sorted].reverse().find((o) => o.unitsSold !== null && o.unitsSold > 0);

  if (firstWithSold && lastWithSold && firstWithSold !== lastWithSold) {
    const delta = (lastWithSold.unitsSold ?? 0) - (firstWithSold.unitsSold ?? 0);
    // Sanity: delta should be positive and not unrealistically large (>10x multiplier = format glitch)
    const spanDays = Math.max(1, (lastWithSold.time.getTime() - firstWithSold.time.getTime()) / 86400000);
    if (delta > 0 && delta < (firstWithSold.unitsSold ?? 1) * 5) {
      unitsSoldCounterDelta = delta;
      unitsSoldDailyRate = delta / spanDays;
    }
  }

  // ── Interval-by-interval delta analysis ──────────────────────────────────
  const events: VelocityEvent[] = [];
  const sellRates: number[] = [];
  let totalSellEvents = 0;
  let totalUnitsSoldImplied = 0;
  let totalZulaufEvents = 0;
  let totalUnitsZulauf = 0;
  let lastZulaufAt: Date | null = null;

  for (let i = 1; i < sorted.length; i++) {
    const prev = sorted[i - 1];
    const curr = sorted[i];

    const hoursElapsed = (curr.time.getTime() - prev.time.getTime()) / 3600000;

    // Skip too-close (duplicates) or too-far (outages)
    if (hoursElapsed < MIN_INTERVAL_HOURS) continue;
    if (hoursElapsed > MAX_INTERVAL_HOURS) {
      events.push({
        transceiverId, vendorId,
        eventAt: curr.time,
        eventType: "data_gap",
        unitsDelta: 0,
        dailyRate: null,
        qtyBefore: prev.physicalQty,
        qtyAfter: curr.physicalQty,
        hoursElapsed,
      });
      continue;
    }

    const delta = curr.physicalQty - prev.physicalQty;
    const daysFraction = hoursElapsed / 24;

    if (delta < 0) {
      // Stock decreased → implied sales
      const unitsSold = -delta;
      const dailyRate = unitsSold / daysFraction;

      events.push({
        transceiverId, vendorId,
        eventAt: curr.time,
        eventType: "sold",
        unitsDelta: delta,        // negative
        dailyRate,
        qtyBefore: prev.physicalQty,
        qtyAfter: curr.physicalQty,
        hoursElapsed,
      });

      sellRates.push(dailyRate);
      totalSellEvents++;
      totalUnitsSoldImplied += unitsSold;

    } else if (delta > 0) {
      // Stock increased → Zulauf (replenishment or restock)
      events.push({
        transceiverId, vendorId,
        eventAt: curr.time,
        eventType: "zulauf",
        unitsDelta: delta,        // positive
        dailyRate: null,
        qtyBefore: prev.physicalQty,
        qtyAfter: curr.physicalQty,
        hoursElapsed,
      });

      totalZulaufEvents++;
      totalUnitsZulauf += delta;
      lastZulaufAt = curr.time;

    } else {
      events.push({
        transceiverId, vendorId,
        eventAt: curr.time,
        eventType: "unchanged",
        unitsDelta: 0,
        dailyRate: null,
        qtyBefore: prev.physicalQty,
        qtyAfter: curr.physicalQty,
        hoursElapsed,
      });
    }
  }

  // ── Compute average sell rate ─────────────────────────────────────────────
  let avgDailySellRate: number | null = null;
  let peakDailySellRate: number | null = null;

  if (sellRates.length > 0) {
    // Use trimmed mean (remove top 10% outliers to avoid one-off bulk events)
    const sorted_rates = [...sellRates].sort((a, b) => a - b);
    const trimCount = Math.max(0, Math.floor(sorted_rates.length * 0.1));
    const trimmed = sorted_rates.slice(0, sorted_rates.length - trimCount);
    avgDailySellRate = trimmed.reduce((s, r) => s + r, 0) / trimmed.length;
    peakDailySellRate = sorted_rates[sorted_rates.length - 1];
  }

  // Prefer units_sold daily rate from FS.com counter (more reliable)
  const effectiveRate = unitsSoldDailyRate ?? avgDailySellRate;

  // ── Current state from latest observation ────────────────────────────────
  const currentQty = latest.physicalQty;
  const currentBackorderQty = latest.backorderQty;
  const currentPriceNet = latest.priceNet;
  const nextExpectedDelivery = latest.backorderEstimatedDate;

  // ── Stockout prediction ───────────────────────────────────────────────────
  let estimatedStockoutDays: number | null = null;
  let estimatedStockoutDate: Date | null = null;

  if (effectiveRate !== null && effectiveRate > 0 && currentQty !== null && currentQty > 0) {
    estimatedStockoutDays = currentQty / effectiveRate;
    estimatedStockoutDate = new Date(
      latest.time.getTime() + estimatedStockoutDays * 86400000
    );
  } else if (currentQty === 0) {
    estimatedStockoutDays = 0;
    estimatedStockoutDate = latest.time;
  }

  // ── Confidence assessment ─────────────────────────────────────────────────
  const meaningfulObs = sorted.length;
  let velocityConfidence: "high" | "medium" | "low" | "insufficient";

  if (meaningfulObs >= 14 && (totalSellEvents >= 3 || unitsSoldCounterDelta !== null)) {
    velocityConfidence = "high";
  } else if (meaningfulObs >= 5 && totalSellEvents >= 1) {
    velocityConfidence = "medium";
  } else if (meaningfulObs >= 2) {
    velocityConfidence = "low";
  } else {
    velocityConfidence = "insufficient";
  }

  return {
    transceiverId,
    vendorId,
    windowStart,
    windowEnd,
    obsCount: meaningfulObs,
    avgDailySellRate,
    peakDailySellRate,
    totalSellEvents,
    totalUnitsSoldImplied,
    unitsSoldCounterDelta,
    unitsSoldDailyRate,
    totalZulaufEvents,
    totalUnitsZulauf,
    lastZulaufAt,
    nextExpectedDelivery: nextExpectedDelivery ?? null,
    currentQty,
    currentBackorderQty: currentBackorderQty ?? null,
    currentPriceNet,
    estimatedStockoutDays,
    estimatedStockoutDate,
    velocityConfidence,
    events,
  };
}

// ── Database I/O ──────────────────────────────────────────────────────────────

async function fetchObservations(
  vendorId: string
): Promise<Map<string, StockObs[]>> {
  const result = await pool.query<{
    transceiver_id: string;
    time: Date;
    warehouse_de_qty: number | null;
    warehouse_global_qty: number | null;
    quantity_available: number | null;
    backorder_qty: number | null;
    backorder_estimated_date: string | null;
    units_sold: number | null;
    price_net: number | null;
  }>(
    `SELECT
       transceiver_id,
       time,
       warehouse_de_qty,
       warehouse_global_qty,
       quantity_available,
       backorder_qty,
       backorder_estimated_date::text,
       units_sold,
       price_net
     FROM stock_observations
     WHERE source_vendor_id = $1
       AND stock_confidence >= 2
       AND time >= NOW() - INTERVAL '${WINDOW_DAYS} days'
     ORDER BY transceiver_id, time`,
    [vendorId]
  );

  const byProduct = new Map<string, StockObs[]>();
  for (const row of result.rows) {
    const obs: StockObs = {
      time: row.time,
      physicalQty:
        (row.warehouse_de_qty ?? 0) + (row.warehouse_global_qty ?? 0) ||
        (row.quantity_available ?? 0),
      quantityAvailable: row.quantity_available,
      backorderQty: row.backorder_qty,
      backorderEstimatedDate: row.backorder_estimated_date,
      unitsSold: row.units_sold,
      priceNet: row.price_net,
    };
    const list = byProduct.get(row.transceiver_id) ?? [];
    list.push(obs);
    byProduct.set(row.transceiver_id, list);
  }
  return byProduct;
}

async function upsertVelocityResult(r: VelocityResult): Promise<void> {
  await pool.query(
    `INSERT INTO stock_velocity (
       transceiver_id, vendor_id, computed_at,
       window_start, window_end, obs_count,
       avg_daily_sell_rate, peak_daily_sell_rate,
       total_sell_events, total_units_sold_implied,
       units_sold_counter_delta, units_sold_daily_rate,
       total_zulauf_events, total_units_zulauf,
       last_zulauf_at, next_expected_delivery,
       current_qty, current_backorder_qty, current_price_net,
       estimated_stockout_days, estimated_stockout_date,
       velocity_confidence
     ) VALUES (
       $1, $2, NOW(),
       $3, $4, $5,
       $6, $7,
       $8, $9,
       $10, $11,
       $12, $13,
       $14, $15,
       $16, $17, $18,
       $19, $20,
       $21
     )
     ON CONFLICT (transceiver_id, vendor_id) DO UPDATE SET
       computed_at             = EXCLUDED.computed_at,
       window_start            = EXCLUDED.window_start,
       window_end              = EXCLUDED.window_end,
       obs_count               = EXCLUDED.obs_count,
       avg_daily_sell_rate     = EXCLUDED.avg_daily_sell_rate,
       peak_daily_sell_rate    = EXCLUDED.peak_daily_sell_rate,
       total_sell_events       = EXCLUDED.total_sell_events,
       total_units_sold_implied = EXCLUDED.total_units_sold_implied,
       units_sold_counter_delta = EXCLUDED.units_sold_counter_delta,
       units_sold_daily_rate   = EXCLUDED.units_sold_daily_rate,
       total_zulauf_events     = EXCLUDED.total_zulauf_events,
       total_units_zulauf      = EXCLUDED.total_units_zulauf,
       last_zulauf_at          = EXCLUDED.last_zulauf_at,
       next_expected_delivery  = EXCLUDED.next_expected_delivery,
       current_qty             = EXCLUDED.current_qty,
       current_backorder_qty   = EXCLUDED.current_backorder_qty,
       current_price_net       = EXCLUDED.current_price_net,
       estimated_stockout_days = EXCLUDED.estimated_stockout_days,
       estimated_stockout_date = EXCLUDED.estimated_stockout_date,
       velocity_confidence     = EXCLUDED.velocity_confidence`,
    [
      r.transceiverId, r.vendorId,
      r.windowStart, r.windowEnd, r.obsCount,
      r.avgDailySellRate, r.peakDailySellRate,
      r.totalSellEvents, r.totalUnitsSoldImplied,
      r.unitsSoldCounterDelta, r.unitsSoldDailyRate,
      r.totalZulaufEvents, r.totalUnitsZulauf,
      r.lastZulaufAt, r.nextExpectedDelivery,
      r.currentQty, r.currentBackorderQty, r.currentPriceNet,
      r.estimatedStockoutDays, r.estimatedStockoutDate,
      r.velocityConfidence,
    ]
  );
}

async function insertVelocityEvents(events: VelocityEvent[]): Promise<void> {
  if (events.length === 0) return;

  // Deduplicate against existing events (don't re-insert known events)
  const minTime = events.reduce((min, e) => e.eventAt < min ? e.eventAt : min, events[0].eventAt);
  const txId = events[0].transceiverId;
  const vendorId = events[0].vendorId;

  await pool.query(
    `DELETE FROM stock_velocity_events
     WHERE transceiver_id = $1 AND vendor_id = $2 AND event_at >= $3`,
    [txId, vendorId, minTime]
  );

  for (const e of events) {
    await pool.query(
      `INSERT INTO stock_velocity_events
         (transceiver_id, vendor_id, event_at, event_type, units_delta, daily_rate,
          qty_before, qty_after, hours_elapsed)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
       ON CONFLICT DO NOTHING`,
      [
        e.transceiverId, e.vendorId, e.eventAt, e.eventType,
        e.unitsDelta, e.dailyRate, e.qtyBefore, e.qtyAfter, e.hoursElapsed,
      ]
    );
  }
}

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

export async function analyzeStockVelocity(): Promise<void> {
  console.log("=== Stock Velocity Analyzer starting ===\n");

  // Find all vendors with confidence >= 2 stock data
  const vendorResult = await pool.query<{ id: string; name: string }>(
    `SELECT DISTINCT v.id, v.name
     FROM stock_observations so
     JOIN vendors v ON v.id = so.source_vendor_id
     WHERE so.stock_confidence >= 2
       AND so.time >= NOW() - INTERVAL '${WINDOW_DAYS} days'
     ORDER BY v.name`
  );

  let totalProducts = 0;
  let totalSellEvents = 0;
  let totalZulaufEvents = 0;
  let skipped = 0;

  for (const vendor of vendorResult.rows) {
    console.log(`\n[${vendor.name}] Loading observations…`);
    const obsMap = await fetchObservations(vendor.id);

    let vProducts = 0;
    let vSellEvents = 0;
    let vZulaufEvents = 0;

    for (const [transceiverId, observations] of obsMap) {
      if (observations.length < MIN_OBS_FOR_VELOCITY) {
        skipped++;
        continue;
      }

      const result = computeVelocity(transceiverId, vendor.id, observations);

      await upsertVelocityResult(result);
      await insertVelocityEvents(result.events);

      vProducts++;
      vSellEvents += result.totalSellEvents;
      vZulaufEvents += result.totalZulaufEvents;
    }

    console.log(
      `  ${vProducts} products | ` +
      `${vSellEvents} sell events | ` +
      `${vZulaufEvents} Zulauf events`
    );

    totalProducts   += vProducts;
    totalSellEvents  += vSellEvents;
    totalZulaufEvents += vZulaufEvents;
  }

  console.log("\n=== Stock Velocity Analyzer complete ===");
  console.log(`  Vendors analyzed:  ${vendorResult.rows.length}`);
  console.log(`  Products analyzed: ${totalProducts}`);
  console.log(`  Sell events:       ${totalSellEvents}`);
  console.log(`  Zulauf events:     ${totalZulaufEvents}`);
  if (skipped > 0) console.log(`  Skipped (<${MIN_OBS_FOR_VELOCITY} obs): ${skipped}`);
}

if (require.main === module) {
  analyzeStockVelocity()
    .then(() => pool.end())
    .catch((err) => {
      console.error("Fatal:", err);
      pool.end();
      process.exit(1);
    });
}
