import 'server-only';
import { cache } from 'react';
import { fairPrices, liquidationModel, marketDepth, type FairPrices, type LiquidationModel, type ListingLifecycle, type MarketDepth } from '@rareindex/valuation';
import { rows, one, sql, num, int } from './_util';

/**
 * Market depth (§25–§26): current asks of the representative variant against its RIV. Falls back to
 * all variants only when the asset has a single variant (then the comparison is unambiguous).
 */
export interface AssetDepth {
  depth: MarketDepth;
  riv: number;
  variantId: string | null;
  scope: 'variant' | 'asset';
}

export const getAssetDepth = cache(async (assetId: string, variantId: string | null): Promise<AssetDepth | null> => {
  const stats = await one<Record<string, unknown>>(sql`
    SELECT s.riv_variant_id, s.riv_usd, (SELECT count(*) FROM asset_variants v WHERE v.asset_id = ${assetId}) AS variants,
           vs.riv_usd AS variant_riv
    FROM asset_stats s LEFT JOIN variant_stats vs ON vs.variant_id = ${variantId ?? sql`s.riv_variant_id`}
    WHERE s.asset_id = ${assetId}`);
  if (!stats) return null;
  const target = variantId ?? (stats.riv_variant_id ? String(stats.riv_variant_id) : null);
  const riv = variantId ? num(stats.variant_riv) : num(stats.variant_riv) ?? num(stats.riv_usd);
  if (riv === null) return null;
  const single = int(stats.variants) <= 1;
  const scope: AssetDepth['scope'] = target && !single ? 'variant' : 'asset';
  const asks = await rows<{ price_usd: unknown }>(sql`
    SELECT l.price_usd FROM listings l
    WHERE l.asset_id = ${assetId} AND l.availability = 'available' AND l.price_usd > 0 AND l.listing_type <> 'auction'
      ${scope === 'variant' ? sql`AND l.variant_id = ${target}` : sql``}
      AND NOT (l.grader IS NOT NULL AND l.grade IS NULL)`);
  const depth = marketDepth(asks.map((a) => num(a.price_usd)), riv);
  return depth ? { depth, riv, variantId: scope === 'variant' ? target : null, scope } : null;
});

/**
 * Days on market / time-to-sale (§24, §30, §214) from observed listing lifecycles. Only listings the
 * connector marked `sold` are sale outcomes; `ended` / `removed` are censored. With fewer than 10 own
 * lifecycles the category's pooled lifecycles (last 365 days) are used and labelled as such.
 */
export interface AssetLiquidation {
  model: LiquidationModel;
  fair: FairPrices;
  level: 'asset' | 'category';
  lifecycles: number;
}

const LIFECYCLE_SELECT = sql`l.first_seen_at, l.last_seen_at, l.availability,
  CASE WHEN l.price_usd > 0 AND coalesce(vs.riv_usd, s.riv_usd) > 0 THEN l.price_usd / coalesce(vs.riv_usd, s.riv_usd) END AS ask_to_riv`;

function toLifecycle(x: Record<string, unknown>): ListingLifecycle | null {
  const first = x.first_seen_at instanceof Date ? x.first_seen_at : new Date(String(x.first_seen_at));
  const last = x.last_seen_at instanceof Date ? x.last_seen_at : new Date(String(x.last_seen_at));
  const outcome = String(x.availability);
  if (Number.isNaN(first.getTime()) || Number.isNaN(last.getTime())) return null;
  if (outcome !== 'sold' && outcome !== 'ended' && outcome !== 'removed') return null;
  return { firstSeen: first, lastSeen: last, outcome, askToRiv: num(x.ask_to_riv) };
}

export const getAssetLiquidation = cache(async (asset: { id: string; categorySlug: string; rivUsd: number | null; rivLowUsd: number | null; rivHighUsd: number | null }): Promise<AssetLiquidation | null> => {
  const own = await rows<Record<string, unknown>>(sql`
    SELECT ${LIFECYCLE_SELECT} FROM listings l
    LEFT JOIN variant_stats vs ON vs.variant_id = l.variant_id
    LEFT JOIN asset_stats s ON s.asset_id = l.asset_id
    WHERE l.asset_id = ${asset.id} AND l.availability IN ('sold', 'ended', 'removed') AND l.last_seen_at > l.first_seen_at
    ORDER BY l.last_seen_at DESC LIMIT 2000`);
  let level: AssetLiquidation['level'] = 'asset';
  let raw = own;
  if (own.length < 10) {
    level = 'category';
    raw = await rows<Record<string, unknown>>(sql`
      SELECT ${LIFECYCLE_SELECT} FROM listings l
      JOIN assets a ON a.id = l.asset_id
      LEFT JOIN variant_stats vs ON vs.variant_id = l.variant_id
      LEFT JOIN asset_stats s ON s.asset_id = l.asset_id
      WHERE a.category_slug = ${asset.categorySlug} AND l.availability IN ('sold', 'ended', 'removed')
        AND l.last_seen_at > l.first_seen_at AND l.last_seen_at >= now() - interval '365 days'
      ORDER BY l.last_seen_at DESC LIMIT 5000`);
  }
  const lifecycles = raw.map(toLifecycle).filter((x): x is ListingLifecycle => x !== null);
  if (!lifecycles.length) return null;
  const model = liquidationModel(lifecycles);
  const fair = fairPrices({ riv: asset.rivUsd, low: asset.rivLowUsd, high: asset.rivHighUsd }, model);
  return { model, fair, level, lifecycles: lifecycles.length };
});