import { and, eq, gt, gte, inArray, isNull, lt, sql } from 'drizzle-orm';
import type { Database } from '@rareindex/database';
import { alerts, alertEvents, assets, assetStats, categories, categorySnapshots, indices, indexValues, listings, auctionLots, sales, radarFindings, populationReports, notifications, priceTargets, users } from '@rareindex/database';
import { newId, logger } from '@rareindex/shared';
import { sendMail, alertEmail } from '@rareindex/notify';
import { evaluateAssetAlert, evaluateCategoryAlert, evaluateIndexAlert, inQuietHours, targetHit, type AlertRow, type AssetState, type CategoryState, type IndexState, type Trigger } from './evaluate.js';

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

interface UserPrefs {
  email: string;
  emailAlerts: boolean;
  quietStart: number | null;
  quietEnd: number | null;
}

async function loadUsers(db: Database, ids: string[]): Promise<Map<string, UserPrefs>> {
  if (!ids.length) return new Map();
  const rows = await db.select({ id: users.id, email: users.email, prefs: users.preferences, deletedAt: users.deletedAt }).from(users).where(inArray(users.id, ids));
  return new Map(rows.filter((r) => !r.deletedAt).map((r) => {
    const p = (r.prefs ?? {}) as Record<string, unknown>;
    return [r.id, { email: r.email, emailAlerts: p.emailAlerts !== false, quietStart: (p.quietStart as number | null) ?? null, quietEnd: (p.quietEnd as number | null) ?? null }];
  }));
}

/** Persist a trigger: notification row, alert_events audit row, alert bookkeeping, optional e-mail. */
export async function deliver(db: Database, opts: { userId: string; alertId: string | null; channel: string; trigger: Trigger; prefs: UserPrefs | undefined; kind?: string; now?: Date }): Promise<void> {
  const now = opts.now ?? new Date();
  const wantsEmail = (opts.channel === 'email' || opts.channel === 'both') && opts.prefs?.emailAlerts !== false && opts.prefs?.email;
  const hold = opts.prefs ? inQuietHours(opts.prefs, now) : false;
  let emailedAt: Date | null = null;
  if (wantsEmail && !hold) {
    const res = await sendMail({ to: opts.prefs!.email, ...alertEmail({ title: opts.trigger.title, body: opts.trigger.body, href: `${(process.env.NEXT_PUBLIC_SITE_URL ?? 'https://www.rareindex.io').replace(/\/$/, '')}${opts.trigger.href}`, facts: opts.trigger.facts }), tags: [{ name: 'kind', value: 'alert' }] });
    if (res.ok) emailedAt = now;
    else log.warn({ err: res.error, userId: opts.userId }, 'alert e-mail failed');
  }
  if (opts.channel !== 'email' || !emailedAt) {
    await db.insert(notifications).values({ id: newId('event'), userId: opts.userId, kind: opts.kind ?? 'alert', title: opts.trigger.title, body: opts.trigger.body, href: opts.trigger.href, payload: { facts: opts.trigger.facts, alertId: opts.alertId, heldForQuietHours: hold && Boolean(wantsEmail) }, emailedAt });
  }
  if (opts.alertId) {
    await db.insert(alertEvents).values({ id: newId('event'), alertId: opts.alertId, userId: opts.userId, message: opts.trigger.title, payload: { facts: opts.trigger.facts, href: opts.trigger.href } });
    await db.update(alerts).set({ lastTriggeredAt: now, triggerCount: sql`${alerts.triggerCount} + 1` }).where(eq(alerts.id, opts.alertId));
  }
}

async function assetState(db: Database, assetId: string, since: Date, now: Date): Promise<AssetState | null> {
  const a = await db.select({ asset: assets, stats: assetStats }).from(assets).leftJoin(assetStats, eq(assetStats.assetId, assets.id)).where(eq(assets.id, assetId)).limit(1);
  const row = a[0];
  if (!row) return null;
  const belowRiv = (await db.execute(sql`select l.id, l.title, l.ends_at, coalesce(au.auction_house, l.source_id) as house, l.all_in_bid_usd::float as bid, l.riv_usd_at_assessment::float as riv, l.bid_vs_riv::float as d, l.fee_basis
    from auction_lots l left join auctions au on au.id = l.auction_id
    where l.asset_id = ${assetId} and l.status in ('live','upcoming') and l.assessment_verdict = 'deal' and l.bid_vs_riv is not null and (l.ends_at is null or l.ends_at > now()) limit 5`)) as unknown as Array<{ id: string; title: string; ends_at: Date | null; house: string; bid: number; riv: number; d: number; fee_basis: string | null }>;
  const [newListings, lots, ending, record, baseline, pop] = await Promise.all([
    db.select({ id: listings.id, priceUsd: listings.priceUsd, sourceId: listings.sourceId, firstSeenAt: listings.firstSeenAt }).from(listings).where(and(eq(listings.assetId, assetId), eq(listings.availability, 'available'), gte(listings.firstSeenAt, since))),
    db.select({ id: auctionLots.id, title: auctionLots.title, endsAt: auctionLots.endsAt, auctionHouse: sql<string>`coalesce(${auctionLots.sourceId}, '')` }).from(auctionLots).where(and(eq(auctionLots.assetId, assetId), gte(auctionLots.createdAt, since))),
    db.select({ id: auctionLots.id, title: auctionLots.title, endsAt: auctionLots.endsAt, auctionHouse: sql<string>`coalesce(${auctionLots.sourceId}, '')` }).from(auctionLots).where(and(eq(auctionLots.assetId, assetId), gt(auctionLots.endsAt, now), lt(auctionLots.endsAt, new Date(now.getTime() + 24 * 3600_000)))),
    db.select({ priceUsd: sales.priceUsd, saleDate: sales.saleDate, sourceId: sales.sourceId }).from(sales).where(and(eq(sales.assetId, assetId), eq(sales.status, 'valid'), gte(sales.createdAt, since))).orderBy(sql`${sales.priceUsd} desc`).limit(1),
    db.execute(sql`select count(*)::float / 3 as n from sales where asset_id = ${assetId} and status = 'valid' and sale_date >= ${new Date(now.getTime() - 120 * 86_400_000).toISOString()}::timestamptz and sale_date < ${new Date(now.getTime() - 30 * 86_400_000).toISOString()}::timestamptz`) as unknown as Promise<Array<{ n: number }>>,
    db.select().from(populationReports).where(and(eq(populationReports.assetId, assetId), gte(populationReports.createdAt, since))).orderBy(sql`${populationReports.reportDate} desc`).limit(2),
  ]);
  const s = row.stats;
  const priorAth = s?.athUsd ?? null;
  const rec = record[0];
  const newRecord = rec && (priorAth === null || rec.priceUsd >= priorAth) ? { priceUsd: rec.priceUsd, saleDate: rec.saleDate, sourceId: rec.sourceId } : null;
  let populationChange: AssetState['populationChange'] = null;
  if (pop.length) {
    const latest = pop[0]!;
    const prev = await db.select({ total: populationReports.total }).from(populationReports).where(and(eq(populationReports.assetId, assetId), eq(populationReports.grader, latest.grader), lt(populationReports.reportDate, latest.reportDate))).orderBy(sql`${populationReports.reportDate} desc`).limit(1);
    if (prev[0] && prev[0].total !== latest.total) populationChange = { grader: latest.grader, from: prev[0].total, to: latest.total, date: latest.reportDate };
  }
  return {
    title: row.asset.title,
    slug: row.asset.slug,
    rivUsd: s?.rivUsd ?? null,
    rivConfidence: s?.rivConfidence ?? null,
    rivSampleSize: s?.rivSampleSize ?? 0,
    athUsd: priorAth,
    latestSaleUsd: s?.latestSaleUsd ?? null,
    latestSaleAt: s?.latestSaleAt ?? null,
    sales30d: s?.sales30d ?? 0,
    baselineSales30d: baseline[0]?.n ?? null,
    newListings,
    newAuctionLots: lots,
    endingLots: ending.filter((l): l is typeof l & { endsAt: Date } => l.endsAt !== null),
    belowRivLots: belowRiv.map((l) => ({ id: l.id, title: l.title, endsAt: l.ends_at ? new Date(l.ends_at) : null, auctionHouse: l.house, allInBidUsd: Number(l.bid), rivUsd: Number(l.riv), bidVsRiv: Number(l.d), feeBasis: l.fee_basis })),
    newRecordSale: newRecord,
    populationChange,
  };
}

async function categoryState(db: Database, slug: string, since: Date, now: Date): Promise<CategoryState | null> {
  const c = await db.select().from(categories).where(eq(categories.slug, slug)).limit(1);
  if (!c[0]) return null;
  const snap = await db.select().from(categorySnapshots).where(eq(categorySnapshots.categorySlug, slug)).orderBy(sql`${categorySnapshots.date} desc`).limit(1);
  const [rec, radar, lots, ending, base] = await Promise.all([
    db.execute(sql`select a.title, a.slug, s.price_usd, s.sale_date from sales s join assets a on a.id = s.asset_id join asset_stats st on st.asset_id = a.id where a.category_slug = ${slug} and s.status = 'valid' and s.created_at >= ${since.toISOString()}::timestamptz and (st.ath_usd is null or s.price_usd >= st.ath_usd) order by s.price_usd desc limit 1`) as unknown as Promise<Array<{ title: string; slug: string; price_usd: number; sale_date: Date }>>,
    db.execute(sql`select a.title, a.slug, r.kind, r.score from radar_findings r join assets a on a.id = r.asset_id where a.category_slug = ${slug} and r.detected_at >= ${since.toISOString()}::timestamptz order by r.score desc limit 5`) as unknown as Promise<Array<{ title: string; slug: string; kind: string; score: number }>>,
    db.execute(sql`select count(*)::int as n from auction_lots l join assets a on a.id = l.asset_id where a.category_slug = ${slug} and l.created_at >= ${since.toISOString()}::timestamptz`) as unknown as Promise<Array<{ n: number }>>,
    db.execute(sql`select count(*)::int as n from auction_lots l join assets a on a.id = l.asset_id where a.category_slug = ${slug} and l.ends_at > ${now.toISOString()}::timestamptz and l.ends_at < ${new Date(now.getTime() + 24 * 3600_000).toISOString()}::timestamptz`) as unknown as Promise<Array<{ n: number }>>,
    db.execute(sql`select count(*)::float / 3 as n from sales s join assets a on a.id = s.asset_id where a.category_slug = ${slug} and s.status = 'valid' and s.sale_date >= ${new Date(now.getTime() - 120 * 86_400_000).toISOString()}::timestamptz and s.sale_date < ${new Date(now.getTime() - 30 * 86_400_000).toISOString()}::timestamptz`) as unknown as Promise<Array<{ n: number }>>,
  ]);
  const r = rec[0];
  return {
    name: c[0].name,
    slug,
    change1d: snap[0]?.change1d ?? null,
    newRecordSale: r ? { assetTitle: r.title, assetSlug: r.slug, priceUsd: Number(r.price_usd), saleDate: new Date(r.sale_date) } : null,
    radarFindings: radar.map((x) => ({ assetTitle: x.title, assetSlug: x.slug, kind: x.kind, score: Number(x.score) })),
    newAuctionLots: lots[0]?.n ?? 0,
    endingLots: ending[0]?.n ?? 0,
    sales30d: snap[0]?.sales ?? 0,
    baselineSales30d: base[0]?.n ?? null,
  };
}

async function indexState(db: Database, ticker: string): Promise<IndexState | null> {
  const i = await db.select().from(indices).where(eq(indices.ticker, ticker)).limit(1);
  if (!i[0]) return null;
  const vals = await db.select({ date: indexValues.date, value: indexValues.value }).from(indexValues).where(eq(indexValues.indexId, i[0].id)).orderBy(sql`${indexValues.date} desc`).limit(2);
  const change1d = vals.length === 2 && vals[1]!.value > 0 ? vals[0]!.value / vals[1]!.value - 1 : null;
  return { ticker, name: i[0].name, change1d, value: vals[0]?.value ?? null };
}

/** Evaluate every active alert. `since` = last run time (defaults to 1 hour ago). */
export async function runAlerts(db: Database, opts: { since?: Date; now?: Date } = {}): Promise<{ evaluated: number; triggered: number }> {
  const now = opts.now ?? new Date();
  const since = opts.since ?? new Date(now.getTime() - 3600_000);
  const rows = await db.select().from(alerts).where(eq(alerts.active, true));
  const prefs = await loadUsers(db, [...new Set(rows.map((r) => r.userId))]);
  const cacheA = new Map<string, AssetState | null>();
  const cacheC = new Map<string, CategoryState | null>();
  const cacheI = new Map<string, IndexState | null>();
  let triggered = 0;
  for (const a of rows) {
    if (!prefs.has(a.userId)) continue;
    const row: AlertRow = { id: a.id, userId: a.userId, alertType: a.alertType, targetType: a.targetType, targetId: a.targetId, threshold: a.threshold, active: a.active, lastTriggeredAt: a.lastTriggeredAt, cooldownMinutes: a.cooldownMinutes, name: a.name, channel: a.channel };
    let trigger: Trigger | null = null;
    try {
      if (a.targetType === 'asset') {
        if (!cacheA.has(a.targetId)) cacheA.set(a.targetId, await assetState(db, a.targetId, since, now));
        const s = cacheA.get(a.targetId);
        if (s) trigger = evaluateAssetAlert(row, s, now);
      } else if (a.targetType === 'category') {
        if (!cacheC.has(a.targetId)) cacheC.set(a.targetId, await categoryState(db, a.targetId, since, now));
        const s = cacheC.get(a.targetId);
        if (s) trigger = evaluateCategoryAlert(row, s, now);
      } else if (a.targetType === 'index') {
        if (!cacheI.has(a.targetId)) cacheI.set(a.targetId, await indexState(db, a.targetId));
        const s = cacheI.get(a.targetId);
        if (s) trigger = evaluateIndexAlert(row, s, now);
      }
    } catch (err) {
      log.error({ err: err instanceof Error ? err.message : String(err), alertId: a.id }, 'alert evaluation failed');
      continue;
    }
    if (trigger) {
      await deliver(db, { userId: a.userId, alertId: a.id, channel: a.channel, trigger, prefs: prefs.get(a.userId), now });
      triggered++;
    }
  }
  log.info({ evaluated: rows.length, triggered }, 'alerts evaluated');
  return { evaluated: rows.length, triggered };
}

/** Price targets: notify once when reached (in-app + e-mail per prefs). */
export async function runTargets(db: Database, now = new Date()): Promise<number> {
  const rows = await db.select({ t: priceTargets, asset: assets, stats: assetStats }).from(priceTargets).innerJoin(assets, eq(assets.id, priceTargets.assetId)).leftJoin(assetStats, eq(assetStats.assetId, assets.id)).where(isNull(priceTargets.notifiedAt));
  const prefs = await loadUsers(db, [...new Set(rows.map((r) => r.t.userId))]);
  let n = 0;
  for (const { t, asset, stats } of rows) {
    const riv = stats?.rivUsd ?? null;
    if (!targetHit(t.direction as 'above' | 'below', t.targetUsd, riv)) continue;
    const usd = (v: number) => new Intl.NumberFormat('en-US', { style: 'currency', currency: 'USD', maximumFractionDigits: 0 }).format(v);
    const trigger: Trigger = { kind: 'alert', title: `Target reached: ${asset.title}`, body: `RIV is ${usd(riv!)} — your ${t.direction === 'above' ? 'sell' : 'buy'} target was ${usd(t.targetUsd)}.`, href: `/asset/${asset.slug}`, facts: [['RIV', usd(riv!)], ['Target', usd(t.targetUsd)]] };
    await deliver(db, { userId: t.userId, alertId: null, channel: 'both', trigger, prefs: prefs.get(t.userId), kind: 'target_hit', now });
    await db.update(priceTargets).set({ hitAt: now, notifiedAt: now }).where(eq(priceTargets.id, t.id));
    n++;
  }
  return n;
}