// Data layer — fetches from Supabase and exposes the same window.FIN_DATA shape
// the rest of the app already uses, so existing components keep working.

// ─── Initial empty state (replaced after first fetch) ────────────────────────
window.FIN_DATA = {
  CARDS: [],
  ACCOUNTS: [],
  CATEGORIES: [],
  TRANSACTIONS: [],
  MONTHLY_HISTORY: [],
  SETTINGS: { fx_rate_usd_to_sgd: 1.35 },
};

// ─── DB shape → app shape mappers ────────────────────────────────────────────
// Existing components expect specific field names (camelCase + nested bonus
// object). DB schema uses snake_case + flat columns.

function mapCardFromDB(row) {
  const out = {
    id: row.id,
    name: row.name,
    last4: row.last4,
    color: row.color,
    accent: row.accent,
    network: row.network,
    type: row.type,
    debit: row.type === "debit",
    statementBalance: Number(row.statement_balance) || 0,
    dueDate: row.due_date ? formatDueDate(row.due_date) : null,
  };
  if (row.bonus_kind) {
    out.bonus = {
      kind: row.bonus_kind,
      rate: Number(row.bonus_rate),
      baseRate: Number(row.bonus_base_rate),
      capSgd: row.bonus_cap_sgd != null ? Number(row.bonus_cap_sgd) : null,
      period: row.bonus_period,
      category: row.bonus_category_label,
    };
    if (row.contactless_cap_sgd != null) out.bonus.contactlessCapSgd = Number(row.contactless_cap_sgd);
    if (row.online_cap_sgd != null) out.bonus.onlineCapSgd = Number(row.online_cap_sgd);
    if (row.sq_gate_sgd != null) out.bonus.sqGateSgd = Number(row.sq_gate_sgd);
    if (row.accelerated_rate != null) out.bonus.acceleratedRate = Number(row.accelerated_rate);
    if (row.sea_rate != null) out.bonus.seaRate = Number(row.sea_rate);
    if (row.other_overseas_rate != null) out.bonus.otherOverseasRate = Number(row.other_overseas_rate);
    if (row.hotel_booking_rate != null) out.bonus.hotelBookingRate = Number(row.hotel_booking_rate);
    if (row.chosen_category_id) out.bonus.chosenCategoryId = row.chosen_category_id;
    if (row.ladys_quarter_start) out.bonus.ladysQuarterStart = row.ladys_quarter_start;
  }
  return out;
}

function formatDueDate(iso) {
  // "2026-05-18" → "May 18"
  const d = new Date(iso + "T00:00:00");
  return d.toLocaleDateString("en-SG", { month: "short", day: "numeric" });
}

function mapTxFromDB(row) {
  return {
    id: row.id,
    date: row.date,
    time: row.time,
    type: row.type,
    amount: Number(row.amount),
    currency: row.currency,
    amountSgd: Number(row.amount_sgd),
    fxRate: Number(row.fx_rate),
    merchant: row.merchant,
    category: row.category_id,
    cardId: row.card_id,
    incomeType: row.income_type,
    account: row.account_id,
    fromAccount: row.from_account_id,
    toAccount: row.to_account_id,
    note: row.note,
    recurring: !!row.recurring,
    recurringSeriesId: row.recurring_series_id,
    paymentMethod: row.payment_method,
    reimbursable: !!row.reimbursable,
    reimbursableAmount: row.reimbursable_amount != null ? Number(row.reimbursable_amount) : null,
    reimbursableStatus: row.reimbursable_status,
    reimbursableFrom: row.reimbursable_from,
    linkedTo: row.linked_to,
    ppvMode: row.ppv_mode,
    prviRegion: row.prvi_region,
    krisflyerMode: row.krisflyer_mode,
    isOverseas: !!row.is_overseas,
  };
}

function mapAccountFromDB(row) {
  return {
    id: row.id,
    name: row.name,
    type: row.type,
    currency: row.currency,
    color: row.color,
    primary: !!row.is_primary,
    // balance is computed from transactions, not stored
    balance: 0,
  };
}

// ─── Compute derived fields the existing UI expects ──────────────────────────

// Aggregates current-month spending onto each card so the existing computeMiles()
// and Cards & Miles view keep working without code changes.
function enrichCardsWithSpending(cards, transactions) {
  const prefix = thisMonthPrefix();
  const monthExpenses = transactions.filter(t =>
    t.type === "expense" && t.date.startsWith(prefix) && t.cardId
  );

  return cards.map(card => {
    const cardTxs = monthExpenses.filter(t => t.cardId === card.id);
    const spent = cardTxs.reduce((s, t) => s + t.amount, 0);
    const enriched = { ...card, spent };

    if (!card.bonus) return enriched;

    if (card.bonus.kind === "krisflyer") {
      enriched.sqSpent = cardTxs
        .filter(t => t.krisflyerMode === "sq")
        .reduce((s, t) => s + t.amount, 0);
    }
    if (card.bonus.kind === "ppv") {
      enriched.contactlessSpent = cardTxs
        .filter(t => t.ppvMode === "contactless")
        .reduce((s, t) => s + t.amount, 0);
      enriched.onlineSpent = cardTxs
        .filter(t => t.ppvMode === "online")
        .reduce((s, t) => s + t.amount, 0);
    }
    if (card.bonus.kind === "prvi") {
      enriched.localSpent = cardTxs.filter(t => t.prviRegion === "local").reduce((s, t) => s + t.amount, 0);
      enriched.seaSpent = cardTxs.filter(t => t.prviRegion === "sea").reduce((s, t) => s + t.amount, 0);
      enriched.otherOverseasSpent = cardTxs.filter(t => t.prviRegion === "other").reduce((s, t) => s + t.amount, 0);
      enriched.hotelSpent = cardTxs.filter(t => t.prviRegion === "hotel").reduce((s, t) => s + t.amount, 0);
    }
    if (card.bonus.kind === "cashback-overseas") {
      enriched.overseasSpent = cardTxs
        .filter(t => t.isOverseas)
        .reduce((s, t) => s + t.amount, 0);
    }
    return enriched;
  });
}

// Computes account balances from transaction history.
// Bank accounts: income → +amount, expense (with account_id, no card) → -amount,
// transfer → +to / -from
function enrichAccountsWithBalances(accounts, transactions) {
  return accounts.map(acc => {
    let balance = 0;
    transactions.forEach(t => {
      if (t.type === "income" && t.account === acc.id) {
        balance += t.amount;
      } else if (t.type === "expense" && t.account === acc.id && !t.cardId) {
        balance -= t.amount;
      } else if (t.type === "transfer") {
        if (t.fromAccount === acc.id) balance -= t.amount;
        if (t.toAccount === acc.id) balance += t.amount;
      }
    });
    return { ...acc, balance: Math.round(balance * 100) / 100 };
  });
}

// Build the MONTHLY_HISTORY array from transactions
function computeMonthlyHistory(transactions) {
  const months = {};
  transactions.forEach(tx => {
    const [year, monthNum] = tx.date.split("-");
    const key = year + "-" + monthNum;
    if (!months[key]) {
      months[key] = { year: parseInt(year), monthNum: parseInt(monthNum), expenses: 0, income: 0 };
    }
    const amt = Number(tx.amountSgd ?? tx.amount);
    if (tx.type === "expense") months[key].expenses += amt;
    if (tx.type === "income") months[key].income += amt;
  });

  const monthNames = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"];
  return Object.values(months)
    .sort((a, b) => a.year - b.year || a.monthNum - b.monthNum)
    .map(m => ({
      month: monthNames[m.monthNum - 1],
      year: m.year,
      expenses: Math.round(m.expenses),
      income: Math.round(m.income),
    }));
}

// ─── Main loader ─────────────────────────────────────────────────────────────

async function loadAllData() {
  const sb = window.sb;

  const [catsRes, accsRes, cardsRes, txsRes, settingsRes] = await Promise.all([
    sb.from("categories").select("*").eq("archived", false).order("sort_order"),
    sb.from("accounts").select("*").eq("archived", false).order("name"),
    sb.from("cards").select("*").eq("archived", false).order("type").order("name"),
    sb.from("transactions").select("*").order("date", { ascending: false }).order("time", { ascending: false, nullsFirst: false }),
    sb.from("app_settings").select("*").maybeSingle(),
  ]);

  // Surface any DB errors clearly
  for (const [name, res] of [
    ["categories", catsRes], ["accounts", accsRes],
    ["cards", cardsRes], ["transactions", txsRes], ["settings", settingsRes],
  ]) {
    if (res.error) throw new Error(`Failed to load ${name}: ${res.error.message}`);
  }

  const transactions = (txsRes.data || []).map(mapTxFromDB);
  const cardsRaw = (cardsRes.data || []).map(mapCardFromDB);
  const accountsRaw = (accsRes.data || []).map(mapAccountFromDB);

  window.FIN_DATA = {
    CATEGORIES: catsRes.data || [],
    ACCOUNTS: enrichAccountsWithBalances(accountsRaw, transactions),
    CARDS: enrichCardsWithSpending(cardsRaw, transactions),
    TRANSACTIONS: transactions,
    MONTHLY_HISTORY: computeMonthlyHistory(transactions),
    SETTINGS: settingsRes.data || { fx_rate_usd_to_sgd: 1.35 },
  };
}

// ─── Insert helpers (used by Add Transaction modal) ──────────────────────────

async function insertTransaction(payload) {
  // payload should already be in DB shape (snake_case columns)
  const { data, error } = await window.sb
    .from("transactions")
    .insert(payload)
    .select()
    .single();
  if (error) throw new Error(error.message);
  return data;
}

window.loadAllData = loadAllData;
window.insertTransaction = insertTransaction;