import { eq, sql } from 'drizzle-orm';
import type { Database } from '@rareindex/database';
import { collections, collectionSnapshots } from '@rareindex/database';
import { logger } from '@rareindex/shared';

const log = logger.child({ job: 'account.snapshots' });

/**
 * Daily portfolio snapshot per collection: Σ value (variant RIV → asset RIV → manual value) and
 * Σ cost basis in USD. Idempotent for a given date (upsert).
 */
export async function runPortfolioSnapshots(db: Database, date = new Date().toISOString().slice(0, 10)): Promise<number> {
  const rows = (await db.execute(sql`
    select c.id as collection_id,
           coalesce(sum(coalesce(vs.riv_usd, s.riv_usd, ci.manual_value_usd, 0) * greatest(ci.quantity, 1)), 0)::float as value_usd,
           coalesce(sum(coalesce(ci.purchase_price_usd, 0) * greatest(ci.quantity, 1)), 0)::float as cost_basis_usd,
           count(ci.id)::int as items
    from collections c
    left join collection_items ci on ci.collection_id = c.id and ci.sold_at is null
    left join asset_stats s on s.asset_id = ci.asset_id
    left join variant_stats vs on vs.variant_id = ci.variant_id
    group by c.id
  `)) as unknown as Array<{ collection_id: string; value_usd: number; cost_basis_usd: number; items: number }>;
  let n = 0;
  for (const r of rows) {
    if (r.items === 0) continue;
    await db
      .insert(collectionSnapshots)
      .values({ collectionId: r.collection_id, date, valueUsd: Number(r.value_usd), costBasisUsd: Number(r.cost_basis_usd), items: r.items })
      .onConflictDoUpdate({ target: [collectionSnapshots.collectionId, collectionSnapshots.date], set: { valueUsd: Number(r.value_usd), costBasisUsd: Number(r.cost_basis_usd), items: r.items } });
    n++;
  }
  log.info({ collections: n, date }, 'portfolio snapshots written');
  return n;
}

export async function snapshotOne(db: Database, collectionId: string): Promise<void> {
  const date = new Date().toISOString().slice(0, 10);
  const rows = (await db.execute(sql`
    select coalesce(sum(coalesce(vs.riv_usd, s.riv_usd, ci.manual_value_usd, 0) * greatest(ci.quantity, 1)), 0)::float as value_usd,
           coalesce(sum(coalesce(ci.purchase_price_usd, 0) * greatest(ci.quantity, 1)), 0)::float as cost_basis_usd,
           count(ci.id)::int as items
    from collection_items ci
    left join asset_stats s on s.asset_id = ci.asset_id
    left join variant_stats vs on vs.variant_id = ci.variant_id
    where ci.collection_id = ${collectionId} and ci.sold_at is null
  `)) as unknown as Array<{ value_usd: number; cost_basis_usd: number; items: number }>;
  const r = rows[0];
  if (!r || r.items === 0) return;
  await db
    .insert(collectionSnapshots)
    .values({ collectionId, date, valueUsd: Number(r.value_usd), costBasisUsd: Number(r.cost_basis_usd), items: r.items })
    .onConflictDoUpdate({ target: [collectionSnapshots.collectionId, collectionSnapshots.date], set: { valueUsd: Number(r.value_usd), costBasisUsd: Number(r.cost_basis_usd), items: r.items } });
  await db.update(collections).set({ updatedAt: new Date() }).where(eq(collections.id, collectionId));
}