import 'server-only';
import { and, asc, desc, eq, inArray, isNull, sql, count } from '@/lib/db';
import { db, assets, assetStats, assetVariants, variantStats, collections, collectionItems, collectionSnapshots, watchlists, watchlistItems, alerts, notifications, savedSearches, priceTargets, listings, indexValues, indices, categories, users } from '@/lib/db';
import { summarizePortfolio, type PortfolioItemInput, type PortfolioSummary } from './portfolio';

// ---------------------------------------------------------------- assets (minimal local search)

export interface AssetHit {
  id: string;
  slug: string;
  title: string;
  categorySlug: string;
  familySlug: string;
  year: number | null;
  heroImageUrl: string | null;
  rivUsd: number | null;
  rivConfidence: number | null;
  salesCount: number;
}

/** Trigram + prefix search over asset titles for the "add item" flow (packages/search may replace it). */
export async function searchAssets(q: string, limit = 12, categorySlug?: string): Promise<AssetHit[]> {
  const term = q.trim();
  if (term.length < 2) return [];
  const rows = (await db().execute(sql`
    select a.id, a.slug, a.title, a.category_slug, a.family_slug, a.year, a.hero_image_url,
           s.riv_usd, s.riv_confidence, coalesce(s.sales_count, 0) as sales_count,
           greatest(similarity(a.title, ${term}), case when a.title ilike ${'%' + term + '%'} then 0.6 else 0 end) as score
    from assets a
    left join asset_stats s on s.asset_id = a.id
    where (a.title % ${term} or a.title ilike ${'%' + term + '%'} or a.search @@ plainto_tsquery('simple', ${term}))
      ${categorySlug ? sql`and a.category_slug = ${categorySlug}` : sql``}
    order by score desc, coalesce(s.sales_count, 0) desc
    limit ${limit}
  `)) as unknown as Array<Record<string, unknown>>;
  return rows.map((r) => ({
    id: String(r.id),
    slug: String(r.slug),
    title: String(r.title),
    categorySlug: String(r.category_slug),
    familySlug: String(r.family_slug),
    year: r.year === null ? null : Number(r.year),
    heroImageUrl: (r.hero_image_url as string | null) ?? null,
    rivUsd: r.riv_usd === null ? null : Number(r.riv_usd),
    rivConfidence: r.riv_confidence === null ? null : Number(r.riv_confidence),
    salesCount: Number(r.sales_count ?? 0),
  }));
}

export async function getAssetBrief(assetId: string) {
  const rows = await db().select({ asset: assets, stats: assetStats }).from(assets).leftJoin(assetStats, eq(assetStats.assetId, assets.id)).where(eq(assets.id, assetId)).limit(1);
  return rows[0] ?? null;
}

export async function listVariants(assetId: string) {
  return db().select({ v: assetVariants, s: variantStats }).from(assetVariants).leftJoin(variantStats, eq(variantStats.variantId, assetVariants.id)).where(eq(assetVariants.assetId, assetId)).orderBy(asc(assetVariants.label));
}

// ---------------------------------------------------------------- collections

export async function listCollections(userId: string) {
  const cols = await db().select().from(collections).where(eq(collections.userId, userId)).orderBy(asc(collections.createdAt));
  if (!cols.length) return [] as Array<{ collection: typeof collections.$inferSelect; summary: PortfolioSummary }>;
  const items = await loadItems(cols.map((c) => c.id));
  return cols.map((c) => ({ collection: c, summary: summarizePortfolio(items.filter((i) => i.collectionId === c.id)) }));
}

export type LoadedItem = PortfolioItemInput & { collectionId: string; variantId: string | null; variantLabel: string | null; acquiredCurrency: string | null; purchasePriceNative: number | null; source: string | null; certificationNumber: string | null; serial: string | null; notes: string | null; tags: string[]; photos: string[]; assetSlug: string; heroImageUrl: string | null; condition: string | null; createdAt: Date; soldAt: string | null };

export async function loadItems(collectionIds: string[]): Promise<LoadedItem[]> {
  if (!collectionIds.length) return [];
  const rows = await db()
    .select({ item: collectionItems, asset: assets, stats: assetStats, variant: assetVariants, vstats: variantStats })
    .from(collectionItems)
    .innerJoin(assets, eq(assets.id, collectionItems.assetId))
    .leftJoin(assetStats, eq(assetStats.assetId, assets.id))
    .leftJoin(assetVariants, eq(assetVariants.id, collectionItems.variantId))
    .leftJoin(variantStats, eq(variantStats.variantId, collectionItems.variantId))
    .where(inArray(collectionItems.collectionId, collectionIds))
    .orderBy(desc(collectionItems.createdAt));
  return rows.map(({ item, asset, stats, variant, vstats }) => ({
    id: item.id,
    collectionId: item.collectionId,
    assetId: asset.id,
    assetSlug: asset.slug,
    heroImageUrl: asset.heroImageUrl,
    title: asset.title,
    categorySlug: asset.categorySlug,
    familySlug: asset.familySlug,
    quantity: item.quantity,
    purchasePriceUsd: item.purchasePriceUsd,
    purchasePriceNative: item.purchasePrice,
    acquiredCurrency: item.purchaseCurrency,
    acquiredAt: item.acquiredAt ? String(item.acquiredAt) : null,
    grader: item.grader ?? variant?.grader ?? null,
    grade: item.grade ?? variant?.grade ?? null,
    variantId: item.variantId,
    variantLabel: variant?.label ?? null,
    variantRivUsd: vstats?.rivUsd ?? null,
    variantConfidence: vstats?.rivConfidence ?? null,
    assetRivUsd: stats?.rivUsd ?? null,
    assetConfidence: stats?.rivConfidence ?? null,
    manualValueUsd: item.manualValueUsd,
    liquidityScore: stats?.liquidityScore ?? null,
    rarityScore: stats?.rarityScore ?? null,
    change30d: stats?.change30d ?? null,
    source: item.source,
    certificationNumber: item.certificationNumber,
    serial: item.serial,
    notes: item.notes,
    tags: item.tags ?? [],
    photos: item.photos ?? [],
    condition: item.condition,
    createdAt: item.createdAt,
    soldAt: item.soldAt ? String(item.soldAt) : null,
    soldPriceUsd: item.soldPriceUsd,
  }));
}

export async function getCollection(userId: string, id: string) {
  const rows = await db().select().from(collections).where(and(eq(collections.id, id), eq(collections.userId, userId))).limit(1);
  return rows[0] ?? null;
}

export async function getCollectionDetail(userId: string, id: string) {
  const col = await getCollection(userId, id);
  if (!col) return null;
  const items = await loadItems([id]);
  const summary = summarizePortfolio(items);
  const history = await db().select().from(collectionSnapshots).where(eq(collectionSnapshots.collectionId, id)).orderBy(asc(collectionSnapshots.date));
  return { collection: col, items, summary, history };
}

export async function portfolioHistory(userId: string): Promise<Array<{ date: string; valueUsd: number; costBasisUsd: number }>> {
  const rows = (await db().execute(sql`
    select s.date::text as date, sum(s.value_usd)::float as value_usd, sum(s.cost_basis_usd)::float as cost_basis_usd
    from collection_snapshots s join collections c on c.id = s.collection_id
    where c.user_id = ${userId}
    group by s.date order by s.date
  `)) as unknown as Array<{ date: string; value_usd: number; cost_basis_usd: number }>;
  return rows.map((r) => ({ date: r.date, valueUsd: Number(r.value_usd), costBasisUsd: Number(r.cost_basis_usd) }));
}

export async function indexSeries(ticker: string, since?: string) {
  const idx = await db().select({ id: indices.id }).from(indices).where(eq(indices.ticker, ticker)).limit(1);
  if (!idx[0]) return [];
  const rows = await db().select({ date: indexValues.date, value: indexValues.value }).from(indexValues).where(since ? and(eq(indexValues.indexId, idx[0].id), sql`${indexValues.date} >= ${since}`) : eq(indexValues.indexId, idx[0].id)).orderBy(asc(indexValues.date));
  return rows.map((r) => ({ date: String(r.date), value: r.value }));
}

// ---------------------------------------------------------------- watchlist

export async function getOrCreateWatchlist(userId: string) {
  const rows = await db().select().from(watchlists).where(eq(watchlists.userId, userId)).orderBy(asc(watchlists.createdAt)).limit(1);
  if (rows[0]) return rows[0];
  const { newId } = await import('@rareindex/shared');
  const id = newId('watchlist');
  await db().insert(watchlists).values({ id, userId, name: 'Watchlist' });
  return (await db().select().from(watchlists).where(eq(watchlists.id, id)))[0]!;
}

export interface WatchRow {
  item: typeof watchlistItems.$inferSelect;
  asset: typeof assets.$inferSelect | null;
  stats: typeof assetStats.$inferSelect | null;
  category: typeof categories.$inferSelect | null;
}

export async function loadWatchlist(userId: string): Promise<WatchRow[]> {
  const wl = await getOrCreateWatchlist(userId);
  const items = await db().select().from(watchlistItems).where(eq(watchlistItems.watchlistId, wl.id)).orderBy(desc(watchlistItems.createdAt));
  const assetIds = items.filter((i) => i.targetType === 'asset').map((i) => i.targetId);
  const catIds = items.filter((i) => i.targetType === 'category').map((i) => i.targetId);
  const [assetRows, catRows] = await Promise.all([
    assetIds.length ? db().select({ asset: assets, stats: assetStats }).from(assets).leftJoin(assetStats, eq(assetStats.assetId, assets.id)).where(inArray(assets.id, assetIds)) : Promise.resolve([]),
    catIds.length ? db().select().from(categories).where(inArray(categories.slug, catIds)) : Promise.resolve([]),
  ]);
  const aMap = new Map(assetRows.map((r) => [r.asset.id, r]));
  const cMap = new Map(catRows.map((c) => [c.slug, c]));
  return items.map((item) => ({ item, asset: aMap.get(item.targetId)?.asset ?? null, stats: aMap.get(item.targetId)?.stats ?? null, category: cMap.get(item.targetId) ?? null }));
}

export async function isWatched(userId: string, targetType: string, targetId: string): Promise<boolean> {
  const wl = await db().select({ id: watchlists.id }).from(watchlists).where(eq(watchlists.userId, userId));
  if (!wl.length) return false;
  const rows = await db().select({ id: watchlistItems.id }).from(watchlistItems).where(and(inArray(watchlistItems.watchlistId, wl.map((w) => w.id)), eq(watchlistItems.targetType, targetType), eq(watchlistItems.targetId, targetId))).limit(1);
  return Boolean(rows[0]);
}

// ---------------------------------------------------------------- alerts / notifications / saved / targets

export async function listAlerts(userId: string) {
  const rows = await db().select().from(alerts).where(eq(alerts.userId, userId)).orderBy(desc(alerts.createdAt));
  const assetIds = rows.filter((a) => a.targetType === 'asset').map((a) => a.targetId);
  const aRows = assetIds.length ? await db().select({ id: assets.id, title: assets.title, slug: assets.slug }).from(assets).where(inArray(assets.id, assetIds)) : [];
  const aMap = new Map(aRows.map((a) => [a.id, a]));
  return rows.map((a) => ({ alert: a, asset: aMap.get(a.targetId) ?? null }));
}

export async function listNotifications(userId: string, limit = 50) {
  return db().select().from(notifications).where(eq(notifications.userId, userId)).orderBy(desc(notifications.createdAt)).limit(limit);
}

export async function unreadCount(userId: string): Promise<number> {
  const rows = await db().select({ n: count() }).from(notifications).where(and(eq(notifications.userId, userId), isNull(notifications.readAt)));
  return Number(rows[0]?.n ?? 0);
}

export async function listSavedSearches(userId: string) {
  return db().select().from(savedSearches).where(eq(savedSearches.userId, userId)).orderBy(desc(savedSearches.createdAt));
}

export async function listTargets(userId: string) {
  const rows = await db().select({ target: priceTargets, asset: assets, stats: assetStats }).from(priceTargets).innerJoin(assets, eq(assets.id, priceTargets.assetId)).leftJoin(assetStats, eq(assetStats.assetId, assets.id)).where(eq(priceTargets.userId, userId)).orderBy(desc(priceTargets.createdAt));
  return rows;
}

/** Deal Radar: available listings priced materially below RIV inside the member's universe (§123). */
export async function dealRadar(userId: string, opts: { minDiscount?: number; limit?: number } = {}) {
  const minDiscount = opts.minDiscount ?? 0.15;
  const limit = opts.limit ?? 50;
  const rows = (await db().execute(sql`
    with universe as (
      select distinct a.category_slug from collection_items ci join collections c on c.id = ci.collection_id join assets a on a.id = ci.asset_id where c.user_id = ${userId}
      union
      select distinct a.category_slug from watchlist_items wi join watchlists w on w.id = wi.watchlist_id join assets a on a.id = wi.target_id where w.user_id = ${userId} and wi.target_type = 'asset'
      union
      select wi.target_id from watchlist_items wi join watchlists w on w.id = wi.watchlist_id where w.user_id = ${userId} and wi.target_type = 'category'
    ), watched as (
      select wi.target_id as asset_id from watchlist_items wi join watchlists w on w.id = wi.watchlist_id where w.user_id = ${userId} and wi.target_type = 'asset'
    )
    select l.id, l.asset_id, a.slug, a.title, a.category_slug, a.hero_image_url, l.source_id, l.source_url, l.price_usd, l.currency, l.price, l.grader, l.grade, l.condition,
           l.discount_to_riv, s.riv_usd, s.riv_confidence, s.riv_sample_size, l.last_seen_at, l.ends_at,
           (a.id in (select asset_id from watched)) as watched
    from listings l
    join assets a on a.id = l.asset_id
    join asset_stats s on s.asset_id = a.id
    where l.availability = 'available'
      and l.discount_to_riv is not null and l.discount_to_riv <= ${-minDiscount} and l.discount_to_riv >= -0.5 and not ('riv_review' = any(l.flags))
      and s.riv_confidence >= 0.5 and s.riv_sample_size >= 5
      and (a.category_slug in (select category_slug from universe) or a.id in (select asset_id from watched))
    order by watched desc, l.discount_to_riv asc
    limit ${limit}
  `)) as unknown as Array<Record<string, unknown>>;
  return rows;
}

export async function publicProfile(handle: string) {
  const rows = await db().select().from(users).where(and(eq(users.handle, handle.toLowerCase()), isNull(users.deletedAt))).limit(1);
  const u = rows[0];
  if (!u) return null;
  const cols = await db().select().from(collections).where(and(eq(collections.userId, u.id), eq(collections.isPublic, true))).orderBy(asc(collections.createdAt));
  const items = await loadItems(cols.map((c) => c.id));
  return { user: u, collections: cols.map((c) => ({ collection: c, summary: summarizePortfolio(items.filter((i) => i.collectionId === c.id)) })), items };
}

export async function activeListingsForAssets(assetIds: string[]) {
  if (!assetIds.length) return [];
  return db().select({ assetId: listings.assetId, n: count(), minAsk: sql<number | null>`min(${listings.priceUsd})` }).from(listings).where(and(inArray(listings.assetId, assetIds), eq(listings.availability, 'available'))).groupBy(listings.assetId);
}