CREATE VIEW "public"."listings_multi_currency" AS (
  select l.id as listing_id, l.asset_id, l.last_seen_at, l.price, l.currency, l.price_usd,
    l.price_usd * cad.rate as price_cad, l.price_usd * eur.rate as price_eur, l.price_usd * gbp.rate as price_gbp, l.price_usd * jpy.rate as price_jpy
  from listings l
  left join lateral (select f.rate from fx_rates f where f.base = 'USD' and f.quote = 'CAD' and f.date <= l.last_seen_at::date order by f.date desc limit 1) cad on true
  left join lateral (select f.rate from fx_rates f where f.base = 'USD' and f.quote = 'EUR' and f.date <= l.last_seen_at::date order by f.date desc limit 1) eur on true
  left join lateral (select f.rate from fx_rates f where f.base = 'USD' and f.quote = 'GBP' and f.date <= l.last_seen_at::date order by f.date desc limit 1) gbp on true
  left join lateral (select f.rate from fx_rates f where f.base = 'USD' and f.quote = 'JPY' and f.date <= l.last_seen_at::date order by f.date desc limit 1) jpy on true
);--> statement-breakpoint
CREATE VIEW "public"."sales_multi_currency" AS (
  select s.id as sale_id, s.asset_id, s.sale_date, s.price, s.currency, s.price_usd,
    s.price_usd * cad.rate as price_cad, s.price_usd * eur.rate as price_eur, s.price_usd * gbp.rate as price_gbp, s.price_usd * jpy.rate as price_jpy,
    cad.date as fx_date_cad, eur.date as fx_date_eur, gbp.date as fx_date_gbp, jpy.date as fx_date_jpy
  from sales s
  left join lateral (select f.rate, f.date from fx_rates f where f.base = 'USD' and f.quote = 'CAD' and f.date <= s.sale_date::date order by f.date desc limit 1) cad on true
  left join lateral (select f.rate, f.date from fx_rates f where f.base = 'USD' and f.quote = 'EUR' and f.date <= s.sale_date::date order by f.date desc limit 1) eur on true
  left join lateral (select f.rate, f.date from fx_rates f where f.base = 'USD' and f.quote = 'GBP' and f.date <= s.sale_date::date order by f.date desc limit 1) gbp on true
  left join lateral (select f.rate, f.date from fx_rates f where f.base = 'USD' and f.quote = 'JPY' and f.date <= s.sale_date::date order by f.date desc limit 1) jpy on true
);