import 'server-only';
import { z } from 'zod';
import { getDb, sql } from '@rareindex/database';
import { CATEGORIES, descendants } from '@rareindex/taxonomy';
import type { ToolDefinition } from '@rareindex/ai';

/**
 * Structured tools for AI Research (§128). The model can only call these; each one is a fixed,
 * parameterised query — never free-form SQL. Results are compact and always carry provenance
 * columns (slug/url/date) so the assistant can cite them.
 */
type Row = Record<string, unknown>;
const run = async (q: ReturnType<typeof sql>): Promise<Row[]> => (await getDb().execute(q)) as unknown as Row[];

const WINDOWS = { '7d': 'change_7d', '30d': 'change_30d', '90d': 'change_90d', '1y': 'change_1y' } as const;
type Window = keyof typeof WINDOWS;

function scope(category?: string | null) {
  if (!category) return sql``;
  const slugs = [category, ...descendants(category)];
  return sql`and a.category_slug in (${sql.join(slugs.map((s) => sql`${s}`), sql`, `)})`;
}

const ASSET_COLS = sql`a.slug, a.title, a.category_slug, a.year, a.hero_image_url, 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_90d, s.change_1y, s.sales_count, s.sales_30d, s.active_listings, s.min_ask_usd, s.liquidity_score, s.rarity_score, s.trending_score, s.value_opportunity`;

export const TOOL_SCHEMAS = {
  search_assets: z.object({ query: z.string().min(1).max(200), category: z.string().nullable().optional(), limit: z.number().int().min(1).max(25).default(10) }),
  get_asset: z.object({ slug: z.string() }),
  get_asset_sales: z.object({ slug: z.string(), limit: z.number().int().min(1).max(50).default(20) }),
  get_asset_listings: z.object({ slug: z.string(), limit: z.number().int().min(1).max(50).default(20) }),
  get_price_history: z.object({ slug: z.string(), days: z.number().int().min(7).max(3650).default(365) }),
  list_categories: z.object({ family: z.string().nullable().optional() }),
  get_category_market: z.object({ category: z.string() }),
  get_index: z.object({ ticker: z.string() }),
  get_index_history: z.object({ ticker: z.string(), days: z.number().int().min(7).max(3650).default(365) }),
  top_movers: z.object({ category: z.string().nullable().optional(), window: z.enum(['7d', '30d', '90d', '1y']).default('30d'), direction: z.enum(['gainers', 'losers']).default('gainers'), min_sales: z.number().int().min(0).default(3), limit: z.number().int().min(1).max(25).default(10) }),
  compare: z.object({ items: z.array(z.string()).min(2).max(6).describe('asset slugs, category slugs or index tickers'), window: z.enum(['7d', '30d', '90d', '1y']).default('1y') }),
  screen: z.object({
    category: z.string().nullable().optional(),
    min_price_usd: z.number().nullable().optional(),
    max_price_usd: z.number().nullable().optional(),
    min_change: z.number().nullable().optional().describe('minimum return over the window as a fraction, e.g. 0.3'),
    max_change: z.number().nullable().optional(),
    window: z.enum(['7d', '30d', '90d', '1y']).default('1y'),
    min_sales: z.number().int().min(0).default(3),
    max_sales_1y: z.number().int().nullable().optional(),
    min_liquidity: z.number().min(0).max(100).nullable().optional(),
    sort: z.enum(['change', 'value', 'liquidity', 'sales', 'trending', 'opportunity']).default('change'),
    limit: z.number().int().min(1).max(50).default(20),
  }),
  record_sales: z.object({ category: z.string().nullable().optional(), limit: z.number().int().min(1).max(25).default(10) }),
  radar: z.object({ kind: z.string().nullable().optional(), limit: z.number().int().min(1).max(25).default(10) }),
  platform_coverage: z.object({}),
} as const;

export type ToolName = keyof typeof TOOL_SCHEMAS;

const DESCRIPTIONS: Record<ToolName, string> = {
  search_assets: 'Full-text search over canonical assets (cards, watches, sneakers, LEGO, games…). Returns slugs, valuations and activity. Use before any asset-specific tool when you only have a name.',
  get_asset: 'Asset detail: attributes, RareIndex Valuation with low/high/confidence/sample size, changes, sales/listings counts, variants (grades) with their own valuations.',
  get_asset_sales: 'Recent observed sales for an asset (date, price, currency, USD, grade, source URL).',
  get_asset_listings: 'Current listings (asks, not sales) for an asset with discount vs RIV.',
  get_price_history: 'Daily RIV / latest-sale / median / volume series for an asset over N days.',
  list_categories: 'Taxonomy with tracked asset counts; optionally children of a family.',
  get_category_market: 'Category market snapshot: index value, tracked assets, sales, volume, median sale, listings, changes; plus top gainers/losers and most valuable.',
  get_index: 'RareIndex index (RARE or RARE-XXX) latest value, changes, constituents, transactions, market cap estimate with confidence.',
  get_index_history: 'Index daily values over N days.',
  top_movers: 'Best or worst performing assets over a window, optionally within a category, requiring a minimum number of sales.',
  compare: 'Compare returns over a window across asset slugs, category slugs and/or index tickers (mixed allowed). Returns start/end values and return.',
  screen: 'Screen assets by category, price range, return over window, sales counts, liquidity; sort by change/value/liquidity/sales/trending/opportunity.',
  record_sales: 'Highest verified sales (optionally within a category).',
  radar: 'Rare Radar findings: first listing in years, ultra-low population, unusual price discrepancy, record sale…',
  platform_coverage: 'Counts of assets, sales, listings, sources and the latest data timestamps — use to explain coverage limits.',
};

export function toolDefinitions(): ToolDefinition[] {
  return (Object.keys(TOOL_SCHEMAS) as ToolName[]).map((name) => ({
    name,
    description: DESCRIPTIONS[name],
    inputSchema: z.toJSONSchema(TOOL_SCHEMAS[name], { target: 'draft-2020-12', io: 'input' }) as Record<string, unknown>,
  }));
}

async function assetBySlug(slug: string) {
  const [a] = await run(sql`select a.id, ${ASSET_COLS}, a.brand, a.franchise, a.set_name, a.number, a.variant, a.edition, a.language, a.identifiers from assets a left join asset_stats s on s.asset_id = a.id where a.slug = ${slug} or a.id = ${slug} limit 1`);
  return a ?? null;
}

export async function executeTool(name: string, rawInput: unknown): Promise<unknown> {
  if (!(name in TOOL_SCHEMAS)) throw new Error(`unknown tool ${name}`);
  const schema = TOOL_SCHEMAS[name as ToolName];
  const input = schema.parse(rawInput ?? {}) as z.infer<typeof schema>;
  switch (name as ToolName) {
    case 'search_assets': {
      const i = input as z.infer<typeof TOOL_SCHEMAS.search_assets>;
      const tsq = i.query.split(/\s+/).map((t) => t.replace(/[^\p{L}\p{N}./-]/gu, '')).filter(Boolean).map((t) => `${t}:*`).join(' & ');
      const rows = await run(sql`select ${ASSET_COLS}, (coalesce(ts_rank(a.search, to_tsquery('simple', ${tsq})),0)*2 + similarity(a.title, ${i.query})) 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 % ${i.query}) ${scope(i.category)} order by score desc, s.sales_count desc nulls last limit ${i.limit}`);
      return { count: rows.length, results: rows };
    }
    case 'get_asset': {
      const i = input as z.infer<typeof TOOL_SCHEMAS.get_asset>;
      const a = await assetBySlug(i.slug);
      if (!a) return { error: 'asset not found', hint: 'use search_assets first' };
      const variants = await run(sql`select v.label, v.grader, v.grade, vs.riv_usd, vs.riv_confidence, vs.riv_sample_size, vs.sales_count, vs.latest_sale_usd, vs.change_30d, vs.change_1y from asset_variants v left join variant_stats vs on vs.variant_id = v.id where v.asset_id = ${a.id as string} order by vs.sales_count desc nulls last limit 15`);
      const rest = { ...a };
      delete rest.id;
      return { ...rest, url: `/asset/${a.slug as string}`, variants };
    }
    case 'get_asset_sales': {
      const i = input as z.infer<typeof TOOL_SCHEMAS.get_asset_sales>;
      const a = await assetBySlug(i.slug);
      if (!a) return { error: 'asset not found' };
      const sales = await run(sql`select sale_date, price, currency, price_usd, grader, grade, condition, sale_type, source_id, source_url, auction_house from sales where asset_id = ${a.id as string} and status = 'valid' order by sale_date desc limit ${i.limit}`);
      return { asset: a.title, count: sales.length, sales };
    }
    case 'get_asset_listings': {
      const i = input as z.infer<typeof TOOL_SCHEMAS.get_asset_listings>;
      const a = await assetBySlug(i.slug);
      if (!a) return { error: 'asset not found' };
      const listings = await run(sql`select price, currency, price_usd, grader, grade, condition, seller, source_id, source_url, ends_at, discount_to_riv, listing_type from listings where asset_id = ${a.id as string} and availability = 'available' order by price_usd asc nulls last limit ${i.limit}`);
      return { asset: a.title, note: 'asking prices, not transactions', count: listings.length, listings };
    }
    case 'get_price_history': {
      const i = input as z.infer<typeof TOOL_SCHEMAS.get_price_history>;
      const a = await assetBySlug(i.slug);
      if (!a) return { error: 'asset not found' };
      const rows = await run(sql`select date, riv_usd, latest_sale_usd, median_usd, sales_count, volume_usd, listings_count from price_snapshots where asset_id = ${a.id as string} and variant_id = '' and date >= current_date - ${i.days}::int order by date`);
      return { asset: a.title, points: thin(rows, 120) };
    }
    case 'list_categories': {
      const i = input as z.infer<typeof TOOL_SCHEMAS.list_categories>;
      const rows = await run(sql`select c.slug, c.name, c.parent_slug, c.family_slug, c.level, c.phase, c.index_ticker, (select count(*)::int from assets a where a.category_slug = c.slug) as tracked_assets from categories c where c.active ${i.family ? sql`and (c.family_slug = ${i.family})` : sql`and c.level = 0`} order by c.sort_order`);
      return { count: rows.length, categories: rows };
    }
    case 'get_category_market': {
      const i = input as z.infer<typeof TOOL_SCHEMAS.get_category_market>;
      const cat = CATEGORIES.find((c) => c.slug === i.category);
      if (!cat) return { error: 'unknown category', hint: 'call list_categories' };
      const [snap] = await run(sql`select * from category_snapshots where category_slug = ${i.category} order by date desc limit 1`);
      const [counts] = await run(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, percentile_cont(0.5) within group (order by s.riv_usd) as median_riv_usd from assets a left join asset_stats s on s.asset_id = a.id where true ${scope(i.category)}`);
      const gainers = await run(sql`select a.slug, a.title, s.riv_usd, s.change_30d, s.sales_count from assets a join asset_stats s on s.asset_id = a.id where s.change_30d is not null and s.riv_sample_size >= 3 ${scope(i.category)} order by s.change_30d desc limit 5`);
      const losers = await run(sql`select a.slug, a.title, s.riv_usd, s.change_30d, s.sales_count from assets a join asset_stats s on s.asset_id = a.id where s.change_30d is not null and s.riv_sample_size >= 3 ${scope(i.category)} order by s.change_30d asc limit 5`);
      const valuable = await run(sql`select a.slug, a.title, s.riv_usd, s.riv_confidence, s.sales_count from assets a join asset_stats s on s.asset_id = a.id where s.riv_usd is not null ${scope(i.category)} order by s.riv_usd desc limit 5`);
      return { category: { slug: cat.slug, name: cat.name, index: cat.index }, snapshot: snap ?? null, counts, gainers, losers, most_valuable: valuable, url: `/markets/${cat.slug}` };
    }
    case 'get_index': {
      const i = input as z.infer<typeof TOOL_SCHEMAS.get_index>;
      const t = i.ticker.toUpperCase();
      const [idx] = await run(sql`select i.ticker, i.name, i.description, i.methodology, i.weighting, i.base_date, i.base_value, i.min_constituents from indices i where i.ticker = ${t}`);
      if (!idx) return { error: 'unknown index' };
      const [latest] = await run(sql`select v.* from index_values v join indices i on i.id = v.index_id where i.ticker = ${t} order by v.date desc limit 1`);
      if (!latest) return { ...idx, published: false, note: 'Index not yet published: not enough priced constituents.' };
      const change = async (days: number) => {
        const [p] = await run(sql`select value from index_values v join indices i on i.id = v.index_id where i.ticker = ${t} and v.date <= ${latest.date as string}::date - ${days}::int order by v.date desc limit 1`);
        return p && Number(p.value) > 0 ? (Number(latest.value) - Number(p.value)) / Number(p.value) : null;
      };
      return { ...idx, published: true, as_of: latest.date, value: latest.value, constituents: latest.constituents_count, transactions: latest.transactions, volume_usd: latest.volume_usd, median_sale_usd: latest.median_sale_usd, market_cap_est_usd: latest.market_cap_est_usd, market_cap_confidence: latest.market_cap_confidence, liquidity_score: latest.liquidity_score, momentum: latest.momentum, change_1d: await change(1), change_7d: await change(7), change_30d: await change(30), change_1y: await change(365), url: `/rareindex/${t}` };
    }
    case 'get_index_history': {
      const i = input as z.infer<typeof TOOL_SCHEMAS.get_index_history>;
      const rows = await run(sql`select v.date, v.value, v.transactions, v.volume_usd from index_values v join indices i on i.id = v.index_id where i.ticker = ${i.ticker.toUpperCase()} and v.date >= current_date - ${i.days}::int order by v.date`);
      return { ticker: i.ticker.toUpperCase(), points: thin(rows, 120) };
    }
    case 'top_movers': {
      const i = input as z.infer<typeof TOOL_SCHEMAS.top_movers>;
      const col = sql.raw(`s.${WINDOWS[i.window as Window]}`);
      const order = i.direction === 'gainers' ? sql`desc` : sql`asc`;
      const rows = await run(sql`select ${ASSET_COLS} from assets a join asset_stats s on s.asset_id = a.id where ${col} is not null and s.riv_sample_size >= ${i.min_sales} ${scope(i.category)} order by ${col} ${order} limit ${i.limit}`);
      return { window: i.window, direction: i.direction, count: rows.length, results: rows };
    }
    case 'compare': {
      const i = input as z.infer<typeof TOOL_SCHEMAS.compare>;
      const days = { '7d': 7, '30d': 30, '90d': 90, '1y': 365 }[i.window as Window];
      const out: Row[] = [];
      for (const item of i.items) {
        const up = item.toUpperCase();
        if (up === 'RARE' || up.startsWith('RARE-')) {
          const [end] = await run(sql`select v.date, v.value from index_values v join indices x on x.id = v.index_id where x.ticker = ${up} order by v.date desc limit 1`);
          const [start] = end ? await run(sql`select v.date, v.value from index_values v join indices x on x.id = v.index_id where x.ticker = ${up} and v.date <= ${end.date as string}::date - ${days}::int order by v.date desc limit 1`) : [];
          out.push({ item: up, type: 'index', start_date: start?.date ?? null, start_value: start?.value ?? null, end_date: end?.date ?? null, end_value: end?.value ?? null, return: ret(start?.value, end?.value) });
          continue;
        }
        const cat = CATEGORIES.find((c) => c.slug === item);
        if (cat) {
          const [end] = await run(sql`select date, index_value, median_sale_usd from category_snapshots where category_slug = ${item} and index_value is not null order by date desc limit 1`);
          const [start] = end ? await run(sql`select date, index_value from category_snapshots where category_slug = ${item} and index_value is not null and date <= ${end.date as string}::date - ${days}::int order by date desc limit 1`) : [];
          out.push({ item, type: 'category', start_date: start?.date ?? null, start_value: start?.index_value ?? null, end_date: end?.date ?? null, end_value: end?.index_value ?? null, return: ret(start?.index_value, end?.index_value) });
          continue;
        }
        const a = await assetBySlug(item);
        if (!a) {
          out.push({ item, type: 'unknown', error: 'not found' });
          continue;
        }
        const [end] = await run(sql`select date, riv_usd from price_snapshots where asset_id = ${a.id as string} and variant_id = '' and riv_usd is not null order by date desc limit 1`);
        const [start] = end ? await run(sql`select date, riv_usd from price_snapshots where asset_id = ${a.id as string} and variant_id = '' and riv_usd is not null and date <= ${end.date as string}::date - ${days}::int order by date desc limit 1`) : [];
        out.push({ item, type: 'asset', title: a.title, start_date: start?.date ?? null, start_value: start?.riv_usd ?? null, end_date: end?.date ?? null, end_value: end?.riv_usd ?? null, return: ret(start?.riv_usd, end?.riv_usd), riv_usd: a.riv_usd, sales_count: a.sales_count });
      }
      return { window: i.window, items: out, note: 'return = null means insufficient history for the window' };
    }
    case 'screen': {
      const i = input as z.infer<typeof TOOL_SCHEMAS.screen>;
      const col = sql.raw(`s.${WINDOWS[i.window as Window]}`);
      const sortCol = { change: col, value: sql`s.riv_usd`, liquidity: sql`s.liquidity_score`, sales: sql`s.sales_count`, trending: sql`s.trending_score`, opportunity: sql`s.value_opportunity` }[i.sort as string] ?? col;
      const sortDir = i.sort === 'opportunity' ? sql`ASC NULLS LAST` : sql`DESC NULLS LAST`; // opportunity: most below RIV first
      const rows = await run(sql`select ${ASSET_COLS} from assets a join asset_stats s on s.asset_id = a.id where s.riv_sample_size >= ${i.min_sales} ${scope(i.category)}
        ${i.min_price_usd != null ? sql`and s.riv_usd >= ${i.min_price_usd}` : sql``} ${i.max_price_usd != null ? sql`and s.riv_usd <= ${i.max_price_usd}` : sql``}
        ${i.min_change != null ? sql`and ${col} >= ${i.min_change}` : sql``} ${i.max_change != null ? sql`and ${col} <= ${i.max_change}` : sql``}
        ${i.max_sales_1y != null ? sql`and s.sales_1y <= ${i.max_sales_1y}` : sql``} ${i.min_liquidity != null ? sql`and s.liquidity_score >= ${i.min_liquidity}` : sql``}
        order by ${sortCol} ${sortDir} limit ${i.limit}`);
      return { filters: i, count: rows.length, results: rows };
    }
    case 'record_sales': {
      const i = input as z.infer<typeof TOOL_SCHEMAS.record_sales>;
      const rows = await run(sql`select a.slug, a.title, a.category_slug, sa.sale_date, sa.price, sa.currency, sa.price_usd, sa.grader, sa.grade, sa.source_id, sa.source_url, sa.auction_house from sales sa join assets a on a.id = sa.asset_id where sa.status = 'valid' and sa.confidence >= 0.8 and sa.is_bundle = false ${scope(i.category)} order by sa.price_usd desc limit ${i.limit}`);
      return { count: rows.length, sales: rows };
    }
    case 'radar': {
      const i = input as z.infer<typeof TOOL_SCHEMAS.radar>;
      const rows = await run(sql`select r.kind, r.score, r.evidence, r.detected_at, a.slug, a.title from radar_findings r join assets a on a.id = r.asset_id where (r.expires_at is null or r.expires_at > now()) ${i.kind ? sql`and r.kind = ${i.kind}` : sql``} order by r.detected_at desc limit ${i.limit}`);
      return { count: rows.length, findings: rows };
    }
    case 'platform_coverage': {
      const [r] = await run(sql`select (select count(*)::int from assets) as assets, (select count(*)::int from assets a join asset_stats s on s.asset_id=a.id where s.riv_usd is not null) as valued_assets, (select count(*)::int from sales where status='valid') as sales, (select max(sale_date) from sales) as latest_sale, (select count(*)::int from listings where availability='available') as listings, (select count(*)::int from sources where active) as sources, (select max(date) from index_values) as latest_index_date`);
      const families = await run(sql`select a.family_slug, count(*)::int as assets, coalesce(sum(s.sales_count),0)::int as sales from assets a left join asset_stats s on s.asset_id = a.id group by a.family_slug order by assets desc`);
      return { ...r, families };
    }
  }
  return { error: 'unhandled' };
}

function ret(start: unknown, end: unknown): number | null {
  const s = Number(start);
  const e = Number(end);
  return Number.isFinite(s) && Number.isFinite(e) && s > 0 ? (e - s) / s : null;
}

/** Keep tool payloads small: at most n evenly spaced points. */
function thin<T>(rows: T[], n: number): T[] {
  if (rows.length <= n) return rows;
  const step = rows.length / n;
  const out: T[] = [];
  for (let i = 0; i < n; i++) out.push(rows[Math.floor(i * step)]!);
  if (out[out.length - 1] !== rows[rows.length - 1]) out.push(rows[rows.length - 1]!);
  return out;
}

/** One-line summary of a tool result for the transparent trace shown to users. */
export function summarizeToolResult(name: string, output: unknown): string {
  if (!output || typeof output !== 'object') return '';
  const o = output as Record<string, unknown>;
  if (o.error) return `error: ${String(o.error)}`;
  if (typeof o.count === 'number') return `${o.count} rows`;
  if (Array.isArray(o.points)) return `${o.points.length} points`;
  if (Array.isArray(o.items)) return `${o.items.length} items`;
  if (name === 'get_asset' && o.title) return String(o.title);
  if (name === 'get_index') return o.published ? `value ${o.value}` : 'not published';
  return 'ok';
}