import { getDb } from "./db";
import type { ListingCard, ListingRow, Market, MarketMetricRow, PropertyRow, SearchParams, SearchResult, ValuationRow } from "./types";
import { stripAccents } from "./normalize";

function toCard(r: ListingRow & { estimate?: number | null; ratio?: number | null; verdict?: string | null; confidence?: string | null }): ListingCard {
  let image: string | null = null;
  try { const a = JSON.parse(r.images ?? "[]"); image = Array.isArray(a) && a.length ? a[0] : null; } catch { /* ignore */ }
  return {
    uid: r.uid, propertyId: r.property_id, market: r.market, price: r.price, address: r.address, city: r.city, region: r.region, category: r.category, typeLabel: r.type_label,
    bedrooms: r.bedrooms, bathrooms: r.bathrooms, areaSqft: r.area_sqft, lat: r.lat, lng: r.lng, image, imageCount: r.image_count ?? 0, connector: r.connector, sourceId: r.source_id,
    url: r.url, firstSeen: r.first_seen, estimate: r.estimate ?? null, ratio: r.ratio ?? null, verdict: r.verdict ?? null, confidence: r.confidence ?? null,
  };
}

const CARD_COLS = `l.uid, l.property_id, l.source_id, l.connector, l.external_id, l.market, l.url, l.status, l.title, NULL AS description, l.price, l.price_label, l.address, l.unit, l.city, l.region, l.sector,
  l.lat, l.lng, l.category, l.type_label, l.bedrooms, l.bathrooms, l.powder_rooms, l.area_sqft, l.lot_sqft, l.year_built, NULL AS features, NULL AS details, l.images, l.image_count,
  l.agency, l.broker, l.mls, l.availability, l.furnished, l.pets, l.first_seen, l.last_seen, l.updated_at, l.quality_score, l.dup_of, NULL AS provenance,
  v.estimate, v.ratio, v.verdict, v.confidence`;

function ftsQuery(q: string): string {
  const toks = stripAccents(q).toLowerCase().replace(/[^a-z0-9\s]/g, " ").split(/\s+/).filter((t) => t.length >= 2);
  return toks.map((t) => `"${t}"*`).join(" ");
}

export function searchListings(p: SearchParams): SearchResult {
  const db = getDb();
  const where: string[] = ["l.price > 0"]; const args: Record<string, unknown> = {};
  const market: Market = p.market ?? "sale";
  where.push("l.market = @market"); args.market = market;
  if (p.city) { where.push("l.city = @city COLLATE NOCASE"); args.city = p.city; }
  if (p.region) { where.push("l.region = @region"); args.region = p.region; }
  if (p.category?.length) { where.push(`l.category IN (${p.category.map((_, i) => `@cat${i}`).join(",")})`); p.category.forEach((c, i) => (args[`cat${i}`] = c)); }
  if (p.priceMin) { where.push("l.price >= @pmin"); args.pmin = p.priceMin; }
  if (p.priceMax) { where.push("l.price <= @pmax"); args.pmax = p.priceMax; }
  if (p.bedroomsMin) { where.push("l.bedrooms >= @bmin"); args.bmin = p.bedroomsMin; }
  if (p.bathroomsMin) { where.push("l.bathrooms >= @bamin"); args.bamin = p.bathroomsMin; }
  if (p.areaMin) { where.push("l.area_sqft >= @amin"); args.amin = p.areaMin; }
  if (p.yearMin) { where.push("l.year_built >= @ymin"); args.ymin = p.yearMin; }
  if (p.withImages) where.push("l.image_count > 0");
  if (p.bbox) { const [w, s, e, n] = p.bbox; where.push("l.lng BETWEEN @w AND @e AND l.lat BETWEEN @s AND @n"); Object.assign(args, { w, s, e, n }); }
  let join = "LEFT JOIN valuation v ON v.listing_uid = l.uid";
  if (p.q && p.q.trim()) {
    const fq = ftsQuery(p.q);
    if (fq) { join += " JOIN listing_fts f ON f.uid = l.uid"; where.push("listing_fts MATCH @fq"); args.fq = fq; }
  }
  const order = {
    recent: "l.first_seen DESC", price_asc: "l.price ASC", price_desc: "l.price DESC", area_desc: "l.area_sqft DESC NULLS LAST",
    deal: "CASE WHEN v.ratio IS NULL THEN 1 ELSE 0 END, v.ratio DESC",
  }[p.sort ?? "recent"];
  const limit = Math.min(Math.max(p.limit ?? 24, 1), 500); const offset = Math.max(p.offset ?? 0, 0);
  const sqlWhere = where.join(" AND ");
  const total = (db.prepare(`SELECT count(*) c FROM listing l ${join} WHERE ${sqlWhere}`).get(args) as { c: number }).c;
  const rows = db.prepare(`SELECT ${CARD_COLS} FROM listing l ${join} WHERE ${sqlWhere} ORDER BY ${order}, l.uid LIMIT @limit OFFSET @offset`).all({ ...args, limit, offset }) as ListingRow[];
  return { items: rows.map(toCard), total, limit, offset };
}

/** Points pour la carte : jusqu'à `limit` annonces dans la bbox (allégé). */
export function mapPoints(p: SearchParams): { uid: string; lat: number; lng: number; price: number; category: string; propertyId: string }[] {
  const r = searchListings({ ...p, limit: Math.min(p.limit ?? 1500, 3000), offset: 0 });
  return r.items.filter((i) => i.lat != null && i.lng != null && i.price != null).map((i) => ({ uid: i.uid, lat: i.lat!, lng: i.lng!, price: i.price!, category: i.category, propertyId: i.propertyId }));
}

export function getListing(uid: string): (ListingRow & { valuation: ValuationRow | null; priceEvents: { ts: string; price: number }[] }) | null {
  const db = getDb();
  const l = db.prepare("SELECT * FROM listing WHERE uid = ?").get(uid) as ListingRow | undefined;
  if (!l) return null;
  const valuation = (db.prepare("SELECT * FROM valuation WHERE listing_uid = ?").get(uid) as ValuationRow | undefined) ?? null;
  const priceEvents = db.prepare("SELECT ts, price FROM price_event WHERE listing_uid = ? AND price IS NOT NULL ORDER BY ts").all(uid) as { ts: string; price: number }[];
  return { ...l, valuation, priceEvents };
}

export function getProperty(id: string): (PropertyRow & { listings: ListingCard[] }) | null {
  const db = getDb();
  const p = db.prepare("SELECT * FROM property WHERE id = ?").get(id) as PropertyRow | undefined;
  if (!p) return null;
  const rows = db.prepare(`SELECT ${CARD_COLS} FROM listing l LEFT JOIN valuation v ON v.listing_uid = l.uid WHERE l.property_id = ? ORDER BY l.market, l.price`).all(id) as ListingRow[];
  return { ...p, listings: rows.map(toCard) };
}

export function nearbyListings(lat: number, lng: number, market: Market, excludeUid: string | null, limit = 8, radiusDeg = 0.02): ListingCard[] {
  const db = getDb();
  const rows = db.prepare(`SELECT ${CARD_COLS}, ((l.lat-@lat)*(l.lat-@lat) + (l.lng-@lng)*(l.lng-@lng)*0.52) AS d2 FROM listing l LEFT JOIN valuation v ON v.listing_uid = l.uid
    WHERE l.market = @market AND l.uid <> @ex AND l.price > 0 AND l.lat BETWEEN @lat-@r AND @lat+@r AND l.lng BETWEEN @lng-@r*1.4 AND @lng+@r*1.4 ORDER BY d2 LIMIT @limit`)
    .all({ lat, lng, market, ex: excludeUid ?? "", r: radiusDeg, limit }) as ListingRow[];
  return rows.map(toCard);
}

export function neighbourhoodScore(lat: number, lng: number) {
  const db = getDb();
  return db.prepare(`SELECT walk, transit, bike, calm, services, global, version, computed_at FROM neighbourhood_score
    WHERE lat BETWEEN ?-0.004 AND ?+0.004 AND lng BETWEEN ?-0.006 AND ?+0.006 ORDER BY (lat-?)*(lat-?)+(lng-?)*(lng-?)*0.52 LIMIT 1`)
    .get(lat, lat, lng, lng, lat, lat, lng, lng) as { walk: number | null; transit: number | null; bike: number | null; calm: number | null; services: number | null; global: number | null; version: string; computed_at: string } | undefined ?? null;
}

export function featured(market: Market, limit = 8): ListingCard[] {
  const db = getDb();
  const rows = db.prepare(`SELECT ${CARD_COLS} FROM listing l LEFT JOIN valuation v ON v.listing_uid = l.uid
    WHERE l.market = @market AND l.image_count >= 4 AND l.price > 0 AND l.lat IS NOT NULL AND l.quality_score >= 80 ${market === "sale" ? "AND v.confidence IN ('A','B') AND v.ratio BETWEEN 0.9 AND 1.3" : ""}
    ORDER BY l.first_seen DESC LIMIT @limit`).all({ market, limit }) as ListingRow[];
  return rows.map(toCard);
}

export function suggestPlaces(q: string, limit = 8): { kind: string; name: string; region: string | null; nSale: number; nRent: number }[] {
  const db = getDb();
  const like = `${stripAccents(q).toLowerCase()}%`;
  const rows = db.prepare(`SELECT kind, name, region, n_sale, n_rent FROM place ORDER BY (n_sale + n_rent) DESC`).all() as { kind: string; name: string; region: string | null; n_sale: number; n_rent: number }[];
  return rows.filter((r) => { const n = stripAccents(r.name).toLowerCase(); return n.startsWith(like.slice(0, -1)) || n.split(/[\s-]+/).some((w) => w.startsWith(like.slice(0, -1))); })
    .slice(0, limit).map((r) => ({ kind: r.kind, name: r.name, region: r.region, nSale: r.n_sale, nRent: r.n_rent }));
}

export function suggestAddresses(q: string, limit = 6): { uid: string; address: string; city: string; market: Market; propertyId: string }[] {
  const fq = ftsQuery(q); if (!fq) return [];
  const db = getDb();
  return db.prepare(`SELECT l.uid, l.address, l.city, l.market, l.property_id AS propertyId FROM listing_fts f JOIN listing l ON l.uid = f.uid WHERE listing_fts MATCH ? AND l.address IS NOT NULL ORDER BY rank LIMIT ?`).all(fq, limit) as { uid: string; address: string; city: string; market: Market; propertyId: string }[];
}

export function searchRoleUnits(q: string, limit = 8) {
  const fq = ftsQuery(q); if (!fq) return [];
  const db = getDb();
  return db.prepare(`SELECT u.id, u.address, u.unit, u.city, u.type_prop, u.year_built, u.floor_area_m2, u.role_value, u.est FROM role_unit_fts f JOIN role_unit u ON u.id = f.id WHERE role_unit_fts MATCH ? ORDER BY rank LIMIT ?`).all(fq, limit) as
    { id: string; address: string; unit: string | null; city: string; type_prop: string; year_built: number | null; floor_area_m2: number | null; role_value: number | null; est: number | null }[];
}

export function marketMetrics(scope: string, value: string, market: Market): MarketMetricRow[] {
  return getDb().prepare("SELECT * FROM market_metric WHERE scope = ? AND scope_value = ? AND market = ? ORDER BY n DESC").all(scope, value, market) as MarketMetricRow[];
}
export function topPlaces(kind: "city" | "region", market: Market, limit = 20) {
  const col = market === "sale" ? "n_sale" : "n_rent";
  return getDb().prepare(`SELECT kind, name, region, lat, lng, n_sale, n_rent FROM place WHERE kind = ? AND ${col} > 0 ORDER BY ${col} DESC LIMIT ?`).all(kind, limit) as { kind: string; name: string; region: string | null; lat: number; lng: number; n_sale: number; n_rent: number }[];
}
export function regionMetrics(market: Market, category = "all"): MarketMetricRow[] {
  return getDb().prepare("SELECT * FROM market_metric WHERE scope = 'region' AND market = ? AND category = ? ORDER BY n DESC").all(market, category) as MarketMetricRow[];
}
export function cityMetrics(market: Market, category = "all", limit = 30): MarketMetricRow[] {
  return getDb().prepare("SELECT * FROM market_metric WHERE scope = 'city' AND market = ? AND category = ? ORDER BY n DESC LIMIT ?").all(market, category, limit) as MarketMetricRow[];
}
export function marketIndex(typeProp: string): { month: string; idx: number; ppm2: number; n: number }[] {
  return getDb().prepare("SELECT month, idx, ppm2, n FROM market_index WHERE type_prop = ? ORDER BY month").all(typeProp) as { month: string; idx: number; ppm2: number; n: number }[];
}
export function recentTransactions(lat: number, lng: number, limit = 10) {
  return getDb().prepare(`SELECT id, date, amount, street, city, lat, lng, property_type, year_built, floor_area FROM sale_transaction
    WHERE lat BETWEEN ?-0.01 AND ?+0.01 AND lng BETWEEN ?-0.014 AND ?+0.014 AND street IS NOT NULL ORDER BY date DESC LIMIT ?`).all(lat, lat, lng, lng, limit) as
    { id: string; date: string; amount: number; street: string; city: string; lat: number; lng: number; property_type: string | null; year_built: number | null; floor_area: number | null }[];
}