// Deals data — fetched directly from Supabase `deals` table at runtime.
// Schema source of truth: ../mw-s8-dealpricing/supabase/migrations/.
// The bucket `deal-images` is PUBLIC, so we build URLs without signing.
// Hardcoded fallback used only on real network/config errors.

window.STATUS_LABELS = {
  verfuegbar: 'Verfügbar',
  reserviert: 'Reserviert',
  verkauft: 'Verkauft',
};

// --- map deal.status enum (10 states) → 3 frontend buckets ---
const STATUS_TO_FRONT = {
  lead: 'verfuegbar',
  analyzed: 'verfuegbar',
  listed: 'verfuegbar',
  under_contract: 'reserviert',
  pending_inspection: 'reserviert',
  pending_appraisal: 'reserviert',
  pending_close: 'reserviert',
  rehab: 'reserviert',
  handover_pm: 'verkauft',
  rented: 'verkauft',
};

// --- formatters (German locale display) ---
const fmtUSD = (n) => {
  if (n == null || isNaN(n)) return '—';
  return Math.round(Number(n)).toLocaleString('de-DE') + ' $';
};
const fmtPctRaw = (n, decimals = 1) => {
  if (n == null || isNaN(n)) return '—';
  return Number(n).toFixed(decimals).replace('.', ',');
};
const fmtPctSuffix = (n, decimals = 2) => fmtPctRaw(n, decimals) + ' %';

const MONTHS_DE = ['Januar','Februar','März','April','Mai','Juni','Juli','August','September','Oktober','November','Dezember'];
const fmtListedSince = (iso) => {
  if (!iso) return '';
  const d = new Date(iso);
  return `Gelistet seit ${d.getDate()}. ${MONTHS_DE[d.getMonth()]} ${d.getFullYear()}`;
};

// Closing date — "DD. Monat YYYY" or '' if null.
const fmtClosingDate = (iso) => {
  if (!iso) return '';
  const d = new Date(iso);
  if (isNaN(d.getTime())) return '';
  return `${d.getDate()}. ${MONTHS_DE[d.getMonth()]} ${d.getFullYear()}`;
};

// Map property_type → SVG placeholder shorthand.
const typeShortFor = (pt, unitCount) => {
  if (unitCount && unitCount >= 3) return 'Multi';
  if (unitCount === 2) return 'Duplex';
  if (!pt) return 'SFR';
  const u = pt.toUpperCase();
  if (u.includes('DUPLEX')) return 'Duplex';
  if (u.includes('TRIPLEX') || u.includes('QUAD') || u.includes('MULTI')) return 'Multi';
  return 'SFR';
};

// Pick the first non-null numeric in a list (DB precomputed → fallback compute).
const firstNumber = (...vals) => {
  for (const v of vals) {
    if (v == null) continue;
    const n = Number(v);
    if (!isNaN(n)) return n;
  }
  return null;
};

// Build the Bj./BR/BA meta string. Multi-unit uses deal_units when present.
const buildMeta = (row, units) => {
  const yearStr = row.year_built ? `Bj. ${row.year_built}` : null;
  const sqftStr = row.sqft ? Number(row.sqft).toLocaleString('de-DE') + ' sqft' : null;

  if (units && units.length > 1) {
    // Group by (bedrooms, bathrooms) to get a "2 × (2 BR / 1 BA)" style summary.
    const groups = {};
    units.forEach((u) => {
      const key = `${u.bedrooms ?? '?'}|${u.bathrooms ?? '?'}`;
      groups[key] = groups[key] || { count: 0, br: u.bedrooms, ba: u.bathrooms };
      groups[key].count += 1;
    });
    const groupStrs = Object.values(groups).map((g) => {
      const ba = g.ba != null ? String(g.ba).replace('.', ',') : '?';
      return `${g.count} × (${g.br ?? '?'} BR / ${ba} BA)`;
    });
    return [groupStrs.join(' + '), yearStr, sqftStr].filter(Boolean).join(' · ');
  }

  const baths = row.bathrooms != null ? String(row.bathrooms).replace('.', ',') : null;
  const brBa = [row.bedrooms != null && `${row.bedrooms} BR`, baths && `${baths} BA`]
    .filter(Boolean).join(' / ');
  return [brBa, yearStr, sqftStr].filter(Boolean).join(' · ');
};

// Build a public image URL (bucket is public — no signing needed).
const buildPublicImageUrl = (urlBase, bucket, storagePath) => {
  if (!storagePath) return null;
  const encoded = storagePath.split('/').map(encodeURIComponent).join('/');
  return `${urlBase}/storage/v1/object/public/${bucket}/${encoded}`;
};

// Map a Supabase row → the shape the existing React components expect.
const mapRow = (row, idx, cfg, assumptions) => {
  const a = (row.assumptions && typeof row.assumptions === 'object') ? row.assumptions : {};
  const fallback = assumptions || {};
  const interest = firstNumber(a.interest_pct, a.interestRate, fallback.interest_pct);
  const ltv = firstNumber(a.ltv_pct, a.ltvPct, fallback.ltv_pct);

  const price = firstNumber(row.purchase_price, row.asking_price);
  const equity = firstNumber(row.equity_required);
  const grossYield = firstNumber(row.gross_yield_pct);
  const netYield = firstNumber(row.net_yield_pct);
  const coc = firstNumber(row.cash_on_cash_pct);
  const cfMonth = firstNumber(row.monthly_cashflow);
  const cfYear = firstNumber(row.annual_cashflow);
  const noi = firstNumber(row.noi_annual);
  const mortgage = firstNumber(row.mortgage_amount);

  // Sum gross rent from deal_units (× 12); fall back to deals.s8_rent_monthly × 12 × unit_count.
  const units = Array.isArray(row.deal_units) ? row.deal_units.slice().sort((u1, u2) => (u1.position || 0) - (u2.position || 0)) : [];
  let grossRentMonthly = 0;
  if (units.length > 0) {
    grossRentMonthly = units.reduce((sum, u) => sum + (Number(u.s8_rent_monthly) || 0), 0);
  } else if (row.s8_rent_monthly) {
    grossRentMonthly = Number(row.s8_rent_monthly) * (row.unit_count || 1);
  }
  const grossRentYear = grossRentMonthly * 12;

  // Bankrate p.a. (annual mortgage payment / Annuität). Prefer the
  // algebraic identity NOI − cashflow whenever both are precomputed on
  // the row: this makes the "Einnahmen · Ausgaben" card on the deal
  // detail page close exactly (Bruttomiete − OpEx − Bankrate = Cashflow)
  // and matches whatever annuity factor the seed/calculator actually
  // used (6.5 % proper P&I, 8 % factor, etc.) without re-doing it.
  //
  // Fall back to the amortizing-loan formula only when NOI or
  // annual_cashflow isn't stored AND we have mortgage + interest.
  // Last resort: derive mortgage from purchase_price × LTV.
  let annuityYear = null;
  if (noi != null && cfYear != null) {
    annuityYear = noi - cfYear;
  } else {
    const mortgageFallback = mortgage != null
      ? mortgage
      : (price != null && ltv != null ? price * (ltv / 100) : null);
    if (mortgageFallback != null && interest != null) {
      const r = interest / 100 / 12;
      const n = (firstNumber(a.amort_years, fallback.amort_years) || 30) * 12;
      const monthly = r > 0 ? (mortgageFallback * r) / (1 - Math.pow(1 + r, -n)) : mortgageFallback / n;
      annuityYear = monthly * 12;
    }
  }

  // OpEx ≈ gross - noi (only if both known).
  const opexYear = (grossRentYear && noi != null) ? grossRentYear - noi : null;

  // Images (PostgREST nested under deal_images).
  const rawImages = Array.isArray(row.deal_images) ? row.deal_images : [];
  const images = rawImages
    .slice()
    .sort((i1, i2) => {
      if (!!i2.is_primary - !!i1.is_primary !== 0) return (!!i2.is_primary) - (!!i1.is_primary);
      return (i1.sort_order || 0) - (i2.sort_order || 0);
    })
    .map((i) => ({
      storage_path: i.storage_path,
      is_primary: !!i.is_primary,
      sort_order: i.sort_order || 0,
      url: buildPublicImageUrl(cfg.url, cfg.imageBucket, i.storage_path),
    }))
    .filter((i) => i.url);

  const imgPrimary = images.length ? images[0].url : null;

  return {
    id: row.id,
    slug: row.slug || null,
    num: String(idx + 1).padStart(2, '0'),
    status: STATUS_TO_FRONT[row.status] || 'verfuegbar',

    city: row.city || '—',
    state: row.state || '',
    // Prefer the dedicated `neighborhood` column (added in the
    // 2026-05-06 baseline). Fall back to address/title for older rows
    // that may still encode the area in those legacy fields.
    neighborhood: row.neighborhood || row.address || '',
    type: row.property_type || (row.unit_count > 1 ? 'Multifamily' : 'Single-Family Rental'),
    typeShort: typeShortFor(row.property_type, row.unit_count),
    meta: buildMeta(row, units),

    kaufpreis: fmtUSD(price),
    kaufpreisNum: price,
    rehab: fmtUSD(row.rehab_costs),
    rehabNum: row.rehab_costs != null ? Number(row.rehab_costs) : 0,
    ek: fmtUSD(equity),
    ekNum: equity,
    jahresNettomiete: fmtUSD(noi),
    coc: fmtPctRaw(coc, 1),
    nettoRendite: fmtPctRaw(netYield, 2),
    bruttoRendite: fmtPctRaw(grossYield, 2),
    grossRent: fmtUSD(grossRentYear),
    grossRentNum: grossRentYear,
    opex: fmtUSD(opexYear),
    opexNum: opexYear,
    unitCount: row.unit_count || units.length || 1,
    annuity: fmtUSD(annuityYear),
    cfMonat: fmtUSD(cfMonth),
    cfJahr: fmtUSD(cfYear),
    interest: interest != null ? fmtPctSuffix(interest, 2) : '—',
    ltv: ltv != null ? fmtPctSuffix(ltv, 0) : '—',
    ltvNum: ltv,

    narrative: row.description || '',
    highlights: [], // no column yet
    listedSince: fmtListedSince(row.updated_at || row.created_at),
    closingDate: row.closing_date || null,
    closingDateLabel: fmtClosingDate(row.closing_date),

    images,
    imgPrimary,
    units,
  };
};

// --- async loader; resolved promise stored on window so pages can await it ---
window.loadDeals = async function loadDeals() {
  const cfg = window.VT_SUPABASE;
  const assumptions = window.VT_ASSUMPTIONS || {};
  if (!cfg || !window.supabase || !window.supabase.createClient) {
    console.warn('[deals] Supabase config or client missing — using fallback data');
    return window.DEALS_FALLBACK;
  }
  try {
    const client = window.supabase.createClient(cfg.url, cfg.anonKey);
    const { data, error } = await client
      .from(cfg.table || 'deals')
      .select('*, deal_images(*), deal_units(*)')
      .order('updated_at', { ascending: false });
    if (error) throw error;
    if (!data || data.length === 0) {
      console.info('[deals] no featured deals in DB — empty state');
      return [];
    }
    return data.map((row, idx) => mapRow(row, idx, cfg, assumptions));
  } catch (err) {
    console.error('[deals] fetch failed — using fallback data', err);
    return window.DEALS_FALLBACK;
  }
};

// --- hardcoded fallback (used only on real network/config errors) ---
window.DEALS_FALLBACK = [
  {
    id: '04', num: '04', status: 'verfuegbar',
    city: 'Cleveland Heights', state: 'Ohio', neighborhood: 'Caledonia Ave',
    type: 'Single-Family Rental', typeShort: 'SFR',
    meta: '3 BR / 2 BA · Bj. 1948 · 1.280 sqft',
    kaufpreis: '134.000 $', kaufpreisNum: 134000,
    ek: '46.900 $', ekNum: 46900,
    jahresNettomiete: '8.880 $',
    coc: '5,1', nettoRendite: '6,63', bruttoRendite: '11,64',
    grossRent: '15.600 $', opex: '6.720 $', annuity: '7.040 $',
    cfMonat: '153 $', cfJahr: '1.840 $',
    interest: '7,15 %', ltv: '65 %',
    narrative: 'Solider SFR-Einstieg in etablierter Wohnlage. Renovierte Küche 2023, neues Dach 2021, Mieter seit 18 Monaten in place. Klassisches Turnkey-Objekt für den ersten US-Deal.',
    highlights: ['Mieter in place · Lease bis 2027','Kein CapEx-Überhang','Tax abatement bis 2028','Property Manager etabliert'],
    listedSince: 'Gelistet seit 12. März 2026',
    images: [], imgPrimary: null, units: [],
  },
];

// Single shared promise so deals.html / deal.html only fetch once per page-load.
window.DEALS_READY = window.loadDeals().then((arr) => {
  window.DEALS = arr;
  return arr;
});

// Initial value while the fetch is in flight (avoid `window.DEALS is undefined` crashes).
window.DEALS = window.DEALS || [];