import { getDb, sql } from '@rareindex/database';

/**
 * Read-only query helpers for the public API. Plain SQL through drizzle's `sql` tag: every
 * function returns already-shaped rows so route handlers stay thin. Nothing here writes.
 */
type Row = Record<string, unknown>;
async function rows<T = Row>(q: ReturnType<typeof sql>): Promise<T[]> {
  const res = await getDb().execute(q);
  return res as unknown as T[];
}

export const ASSET_COLUMNS = sql`a.id, a.slug, a.title, a.name, a.category_slug, a.family_slug, a.brand, a.franchise, a.set_name, a.set_code, a.number, a.year, a.edition, a.variant, a.language, a.hero_image_url, a.identifiers, a.data_quality,
  s.riv_usd, s.riv_low_usd, s.riv_high_usd, s.riv_confidence, s.riv_sample_size, s.latest_sale_usd, s.latest_sale_at, s.change_7d, s.change_30d, s.change_1y, s.ath_usd, s.atl_usd,
  s.sales_count, s.sales_30d, s.active_listings, s.min_ask_usd, s.liquidity_score, s.rarity_score, s.momentum_30d, s.trending_score, s.value_opportunity, s.updated_at as stats_updated_at`;

export async function searchAssets(opts: { q?: string; category?: string; limit: number; offset: number }) {
  const q = opts.q?.trim();
  const catFilter = opts.category ? sql`and (a.category_slug = ${opts.category} or a.family_slug = ${opts.category})` : sql``;
  if (q) {
    const tsq = q
      .split(/\s+/)
      .filter(Boolean)
      .map((t) => t.replace(/[^\p{L}\p{N}./-]/gu, ''))
      .filter(Boolean)
      .map((t) => `${t}:*`)
      .join(' & ');
    return rows(sql`
      select ${ASSET_COLUMNS},
        (coalesce(ts_rank(a.search, to_tsquery('simple', ${tsq})), 0) * 2 + similarity(a.title, ${q}) + coalesce(log(1 + s.sales_count), 0) * 0.05) as score
      from assets a left join asset_stats s on s.asset_id = a.id
      where (a.search @@ to_tsquery('simple', ${tsq}) or a.title % ${q} or a.identifiers::text ilike ${'%' + q + '%'}) ${catFilter}
      order by score desc, s.sales_count desc nulls last
      limit ${opts.limit} offset ${opts.offset}`);
  }
  return rows(sql`
    select ${ASSET_COLUMNS}, 0 as score from assets a left join asset_stats s on s.asset_id = a.id
    where true ${catFilter}
    order by s.sales_count desc nulls last, a.updated_at desc
    limit ${opts.limit} offset ${opts.offset}`);
}

export async function getAsset(idOrSlug: string) {
  const [asset] = await rows(sql`select ${ASSET_COLUMNS}, a.description, a.reference, a.model, a.series, a.rarity, a.production_quantity, a.original_msrp, a.original_msrp_currency, a.release_date, a.metadata, a.created_at, a.updated_at
    from assets a left join asset_stats s on s.asset_id = a.id where a.id = ${idOrSlug} or a.slug = ${idOrSlug} limit 1`);
  if (!asset) return null;
  const id = asset.id as string;
  const variants = await rows(sql`select v.id, v.variant_key, v.label, v.grader, v.grade, v.qualifier, v.condition, v.completeness, v.is_default,
      vs.riv_usd, vs.riv_low_usd, vs.riv_high_usd, vs.riv_confidence, vs.riv_sample_size, vs.latest_sale_usd, vs.latest_sale_at, vs.change_30d, vs.change_1y, vs.sales_count, vs.sales_30d, vs.active_listings, vs.min_ask_usd, vs.liquidity_score
    from asset_variants v left join variant_stats vs on vs.variant_id = v.id where v.asset_id = ${id} order by vs.sales_count desc nulls last, v.label`);
  const [valuation] = await rows(sql`select id, variant_id, computed_at, riv_usd, low_usd, high_usd, confidence, confidence_label, sample_size, window_days, methods, method, notes from valuations where asset_id = ${id} and variant_id is null order by computed_at desc limit 1`);
  const sources = await rows(sql`select source_id, count(*)::int as sales from sales where asset_id = ${id} and status = 'valid' group by source_id order by sales desc`);
  return { ...(asset as Row & { id: string }), variants, valuation: valuation ?? null, sources };
}

export async function assetSales(assetId: string, opts: { limit: number; offset: number; variantId?: string; includeFlagged?: boolean }) {
  const statusFilter = opts.includeFlagged ? sql`and status <> 'excluded'` : sql`and status = 'valid'`;
  const variantFilter = opts.variantId ? sql`and variant_id = ${opts.variantId}` : sql``;
  return rows(sql`select id, variant_id, source_id, source_url, sale_type, sale_date, price, currency, price_usd, buyer_premium_included, condition, grader, grade, certification_number, auction_house, lot_number, image_urls, raw_title, confidence, data_quality, status, flags
    from sales where asset_id = ${assetId} ${statusFilter} ${variantFilter} order by sale_date desc limit ${opts.limit} offset ${opts.offset}`);
}

export async function assetListings(assetId: string, opts: { limit: number; offset: number; availability?: string }) {
  const avail = opts.availability ?? 'available';
  return rows(sql`select id, variant_id, source_id, source_url, listing_type, price, currency, price_usd, seller, location, shipping_cost, condition, grader, grade, certification_number, image_urls, raw_title, listed_at, ends_at, availability, bid_count, first_seen_at, last_seen_at, discount_to_riv, flags
    from listings where asset_id = ${assetId} and availability = ${avail} order by price_usd asc nulls last limit ${opts.limit} offset ${opts.offset}`);
}

export async function assetHistory(assetId: string, opts: { variantId?: string; from?: string; to?: string }) {
  const variant = opts.variantId ?? '';
  const from = opts.from ?? '1900-01-01';
  const to = opts.to ?? '2999-12-31';
  return rows(sql`select date, riv_usd, latest_sale_usd, median_usd, sales_count, volume_usd, listings_count, min_ask_usd, observation_usd
    from price_snapshots where asset_id = ${assetId} and variant_id = ${variant} and date between ${from} and ${to} order by date asc`);
}

export async function listCategories() {
  return rows(sql`select c.slug, c.parent_slug, c.family_slug, c.name, c.short_name, c.description, c.level, c.phase, c.active, c.index_ticker, c.condition_scale, c.graders,
      (select count(*)::int from assets a where a.category_slug = c.slug) as tracked_assets
    from categories c where c.active order by c.sort_order`);
}

export async function listIndices() {
  return rows(sql`with latest as (
      select distinct on (index_id) index_id, date, value, constituents_count, transactions, volume_usd, median_sale_usd, market_cap_est_usd, market_cap_confidence, liquidity_score, momentum, tracked_assets, coverage
      from index_values order by index_id, date desc)
    select i.id, i.ticker, i.name, i.description, i.parent_ticker, i.family_slugs, i.methodology, i.weighting, i.base_date, i.base_value, i.min_constituents, i.is_flagship, i.color,
      l.date as as_of, l.value, l.constituents_count, l.transactions, l.volume_usd, l.median_sale_usd, l.market_cap_est_usd, l.market_cap_confidence, l.liquidity_score, l.momentum, l.tracked_assets, l.coverage,
      (select value from index_values v where v.index_id = i.id and v.date <= l.date - interval '1 day' order by date desc limit 1) as value_1d,
      (select value from index_values v where v.index_id = i.id and v.date <= l.date - interval '7 day' order by date desc limit 1) as value_7d,
      (select value from index_values v where v.index_id = i.id and v.date <= l.date - interval '30 day' order by date desc limit 1) as value_30d,
      (select value from index_values v where v.index_id = i.id and v.date <= l.date - interval '365 day' order by date desc limit 1) as value_1y,
      (select value from index_values v where v.index_id = i.id and v.date <= date_trunc('year', l.date)::date order by date desc limit 1) as value_ytd
    from indices i left join latest l on l.index_id = i.id where i.active order by i.is_flagship desc, i.ticker`);
}

export async function indexHistory(ticker: string, opts: { from?: string; to?: string }) {
  const from = opts.from ?? '1900-01-01';
  const to = opts.to ?? '2999-12-31';
  return rows(sql`select v.date, v.value, v.constituents_count, v.transactions, v.volume_usd, v.median_sale_usd, v.avg_sale_usd, v.market_cap_est_usd, v.liquidity_score, v.momentum, v.coverage
    from index_values v join indices i on i.id = v.index_id where i.ticker = ${ticker} and v.date between ${from} and ${to} order by v.date asc`);
}

export async function listMarkets() {
  return rows(sql`with latest as (
      select distinct on (category_slug) * from category_snapshots order by category_slug, date desc)
    select c.slug, c.name, c.family_slug, c.level, c.index_ticker, l.date as as_of, l.index_value, l.tracked_assets, l.assets_with_valuation, l.sales, l.volume_usd, l.median_sale_usd, l.active_listings, l.market_cap_est_usd, l.liquidity_score, l.change_1d, l.change_7d, l.change_30d, l.change_1y,
      (select count(*)::int from assets a where a.family_slug = c.slug or a.category_slug = c.slug) as assets_now
    from categories c left join latest l on l.category_slug = c.slug where c.active and c.level = 0 order by c.sort_order`);
}

export async function getMarket(slug: string) {
  const [category] = await rows(sql`select slug, parent_slug, family_slug, name, description, level, phase, index_ticker, condition_scale, graders from categories where slug = ${slug}`);
  if (!category) return null;
  const [snapshot] = await rows(sql`select * from category_snapshots where category_slug = ${slug} order by date desc limit 1`);
  const scope = sql`(a.category_slug = ${slug} or a.family_slug = ${slug})`;
  const [counts] = await rows(sql`select count(*)::int as tracked_assets, count(s.riv_usd)::int as valued_assets, coalesce(sum(s.sales_count),0)::int as sales_total, coalesce(sum(s.active_listings),0)::int as active_listings from assets a left join asset_stats s on s.asset_id = a.id where ${scope}`);
  const gainers = await rows(sql`select ${ASSET_COLUMNS} from assets a join asset_stats s on s.asset_id = a.id where ${scope} and s.change_30d is not null and s.riv_sample_size >= 3 order by s.change_30d desc limit 10`);
  const losers = await rows(sql`select ${ASSET_COLUMNS} from assets a join asset_stats s on s.asset_id = a.id where ${scope} and s.change_30d is not null and s.riv_sample_size >= 3 order by s.change_30d asc limit 10`);
  const mostValuable = await rows(sql`select ${ASSET_COLUMNS} from assets a join asset_stats s on s.asset_id = a.id where ${scope} and s.riv_usd is not null order by s.riv_usd desc limit 10`);
  const mostLiquid = await rows(sql`select ${ASSET_COLUMNS} from assets a join asset_stats s on s.asset_id = a.id where ${scope} and s.liquidity_score is not null order by s.liquidity_score desc limit 10`);
  const recentSales = await rows(sql`select sa.id, sa.asset_id, a.slug as asset_slug, a.title, sa.source_id, sa.source_url, sa.sale_date, sa.price, sa.currency, sa.price_usd, sa.grader, sa.grade from sales sa join assets a on a.id = sa.asset_id where ${scope} and sa.status = 'valid' order by sa.sale_date desc limit 20`);
  const history = await rows(sql`select date, index_value, sales, volume_usd, median_sale_usd, active_listings, tracked_assets from category_snapshots where category_slug = ${slug} order by date asc`);
  return { category, snapshot: snapshot ?? null, counts, gainers, losers, most_valuable: mostValuable, most_liquid: mostLiquid, recent_sales: recentSales, history };
}

export async function trending(opts: { category?: string; limit: number; offset: number }) {
  const cat = opts.category ? sql`and (a.category_slug = ${opts.category} or a.family_slug = ${opts.category})` : sql``;
  return rows(sql`select ${ASSET_COLUMNS} from assets a join asset_stats s on s.asset_id = a.id where s.trending_score is not null ${cat} order by s.trending_score desc limit ${opts.limit} offset ${opts.offset}`);
}

export async function latestSales(opts: { category?: string; minUsd?: number; limit: number; offset: number }) {
  const cat = opts.category ? sql`and (a.category_slug = ${opts.category} or a.family_slug = ${opts.category})` : sql``;
  const min = opts.minUsd ? sql`and sa.price_usd >= ${opts.minUsd}` : sql``;
  return rows(sql`select sa.id, sa.asset_id, a.slug as asset_slug, a.title, a.category_slug, a.hero_image_url, sa.source_id, sa.source_url, sa.sale_type, sa.sale_date, sa.price, sa.currency, sa.price_usd, sa.grader, sa.grade, sa.condition, sa.auction_house, sa.confidence
    from sales sa join assets a on a.id = sa.asset_id where sa.status = 'valid' ${cat} ${min} order by sa.sale_date desc, sa.created_at desc limit ${opts.limit} offset ${opts.offset}`);
}

/** Record sales: highest verified transaction per family (§154). */
export async function recordSales() {
  return rows(sql`select distinct on (a.family_slug) a.family_slug, c.name as family_name, sa.id as sale_id, sa.asset_id, a.slug as asset_slug, a.title, sa.source_id, sa.source_url, sa.sale_date, sa.price, sa.currency, sa.price_usd, sa.grader, sa.grade, sa.auction_house
    from sales sa join assets a on a.id = sa.asset_id join categories c on c.slug = a.family_slug
    where sa.status = 'valid' and sa.confidence >= 0.8 and sa.is_bundle = false
    order by a.family_slug, sa.price_usd desc`);
}

export async function platformStats() {
  const [r] = await rows(sql`select
    (select count(*)::int from assets) as assets,
    (select count(*)::int from sales where status = 'valid') as sales,
    (select count(*)::int from listings where availability = 'available') as listings,
    (select count(*)::int from sources where active) as sources,
    (select count(*)::int from connectors where status = 'active') as connectors,
    (select count(*)::int from categories where active) as categories`);
  return r;
}