import 'server-only';
import { cache } from 'react';
import { one, rows, sql, int, date } from './_util';

export interface SiteStats {
  assets: number;
  sales: number;
  observations: number;
  activeListings: number;
  sources: number;
  connectors: number;
  categoriesWithData: number;
  lastSaleAt: Date | null;
  lastUpdateAt: Date | null;
}

/** Real counts for the homepage stat strip and the Data page. Never estimated. */
export const getSiteStats = cache(async (): Promise<SiteStats> => {
  const r = await one<Record<string, unknown>>(sql`
    SELECT
      (SELECT count(*) FROM assets) AS assets,
      (SELECT count(*) FROM sales WHERE status <> 'excluded') AS sales,
      (SELECT count(*) FROM price_observations) AS observations,
      (SELECT count(*) FROM listings WHERE availability = 'available') AS active_listings,
      (SELECT count(*) FROM sources WHERE active) AS sources,
      (SELECT count(*) FROM connectors WHERE status = 'active') AS connectors,
      (SELECT count(DISTINCT category_slug) FROM assets) AS categories_with_data,
      (SELECT max(sale_date) FROM sales) AS last_sale_at,
      GREATEST((SELECT max(created_at) FROM sales), (SELECT max(created_at) FROM price_observations), (SELECT max(updated_at) FROM asset_stats)) AS last_update_at
  `);
  return {
    assets: int(r?.assets),
    sales: int(r?.sales),
    observations: int(r?.observations),
    activeListings: int(r?.active_listings),
    sources: int(r?.sources),
    connectors: int(r?.connectors),
    categoriesWithData: int(r?.categories_with_data),
    lastSaleAt: date(r?.last_sale_at),
    lastUpdateAt: date(r?.last_update_at),
  };
});

export interface LatestSale {
  id: string;
  assetSlug: string;
  assetTitle: string;
  categorySlug: string;
  heroImageUrl: string | null;
  saleDate: Date;
  price: number;
  currency: string;
  priceUsd: number;
  grader: string | null;
  grade: string | null;
  sourceId: string;
  sourceName: string;
  sourceUrl: string;
  saleType: string;
}

export const getLatestSales = cache(async (limit = 12): Promise<LatestSale[]> => {
  const r = await rows<Record<string, unknown>>(sql`
    SELECT s.id, a.slug AS asset_slug, a.title AS asset_title, a.category_slug, a.hero_image_url, s.sale_date, s.price, s.currency, s.price_usd, s.grader, s.grade, s.source_id, src.name AS source_name, s.source_url, s.sale_type
    FROM sales s JOIN assets a ON a.id = s.asset_id JOIN sources src ON src.id = s.source_id
    WHERE s.status = 'valid'
    ORDER BY s.sale_date DESC, s.created_at DESC LIMIT ${limit}
  `);
  return r.map((x) => ({
    id: String(x.id),
    assetSlug: String(x.asset_slug),
    assetTitle: String(x.asset_title),
    categorySlug: String(x.category_slug),
    heroImageUrl: (x.hero_image_url as string | null) ?? null,
    saleDate: date(x.sale_date)!,
    price: Number(x.price),
    currency: String(x.currency),
    priceUsd: Number(x.price_usd),
    grader: (x.grader as string | null) ?? null,
    grade: (x.grade as string | null) ?? null,
    sourceId: String(x.source_id),
    sourceName: String(x.source_name),
    sourceUrl: String(x.source_url),
    saleType: String(x.sale_type),
  }));
});