<?php
// Shared formatting and settings helpers. Every dynamic value shown in a
// page goes through e(); every money/date value is formatted only here, so
// a future format change is one line.

require_once __DIR__ . '/db.php';

/** HTML-escape shortcut. Use around every dynamic value printed into a page. */
function e(?string $value): string
{
    return htmlspecialchars($value ?? '', ENT_QUOTES, 'UTF-8');
}

/**
 * Cache-busting query string for a local asset (assets/css/app.css,
 * assets/js/app.js), based on the file's last-modified time. Without this,
 * a browser (or a host's static-file cache) can keep serving a stale
 * app.css/app.js after a deploy, since the filename never changes —
 * exactly what made a newly added CSS rule invisible until a hard refresh.
 * $relativePath is relative to the project root, e.g. 'assets/css/app.css'.
 */
function asset_version(string $relativePath): string
{
    $fullPath = __DIR__ . '/../' . $relativePath;

    return file_exists($fullPath) ? (string) filemtime($fullPath) : (string) time();
}

/** Reads one row from settings, with an in-request cache. */
function get_setting(string $key, ?string $default = null): ?string
{
    static $cache = null;

    if ($cache === null) {
        $cache = [];
        $stmt = get_db()->query('SELECT setting_key, setting_value FROM settings');
        foreach ($stmt->fetchAll() as $row) {
            $cache[$row['setting_key']] = $row['setting_value'];
        }
    }

    return $cache[$key] ?? $default;
}

/** Writes one row to settings (insert or update). */
function set_setting(string $key, string $value): void
{
    // VALUES(setting_value) (not a repeated :value placeholder) — PDO's
    // native prepared statements (PDO::ATTR_EMULATE_PREPARES = false,
    // includes/db.php) don't allow binding the same named parameter twice.
    $stmt = get_db()->prepare(
        'INSERT INTO settings (setting_key, setting_value) VALUES (:key, :value)
         ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value)'
    );
    $stmt->execute(['key' => $key, 'value' => $value]);
}

/** Formats a DECIMAL(10,2) amount using the configured currency symbol/position. */
function format_money(string|float $amount): string
{
    $symbol = get_setting('currency_symbol', '¥');
    $position = get_setting('currency_position', 'before');
    $formatted = number_format((float) $amount, 2);

    return $position === 'after' ? $formatted . $symbol : $symbol . $formatted;
}

/** "Good morning"/"Good afternoon"/"Good evening" based on the server's current hour. */
function greeting_phrase(): string
{
    $hour = (int) date('G');
    if ($hour < 12) {
        return 'Good morning';
    }
    if ($hour < 18) {
        return 'Good afternoon';
    }
    return 'Good evening';
}

/** Calls the free FX API for CNY rates; returns null on any failure (network, timeout, bad shape). */
function fetch_cny_to_bdt_rate(): ?float
{
    $url = 'https://open.er-api.com/v6/latest/CNY';

    if (function_exists('curl_init')) {
        $ch = curl_init($url);
        curl_setopt_array($ch, [
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT => 3,
            CURLOPT_SSL_VERIFYPEER => true,
        ]);
        $json = curl_exec($ch);
    } else {
        $json = @file_get_contents($url, false, stream_context_create(['http' => ['timeout' => 3]]));
    }

    if (!$json) {
        return null;
    }

    $rate = json_decode($json, true)['rates']['BDT'] ?? null;

    return is_numeric($rate) ? (float) $rate : null;
}

/**
 * CNY→BDT FX rate, cached in the settings table for the calendar day so the
 * free API is called at most once per day rather than once per page view.
 * Null when today's rate can't be fetched and nothing is cached yet.
 */
function get_cny_to_bdt_rate(): ?float
{
    $today = date('Y-m-d');

    if (get_setting('fx_cny_bdt_date') === $today) {
        $cached = get_setting('fx_cny_bdt_rate');
        return $cached !== null ? (float) $cached : null;
    }

    $rate = fetch_cny_to_bdt_rate();
    if ($rate === null) {
        return null;
    }

    set_setting('fx_cny_bdt_rate', (string) $rate);
    set_setting('fx_cny_bdt_date', $today);

    return $rate;
}

/**
 * BDT equivalent of a CNY amount for the dashboard's "Spent in <month>"
 * card, e.g. "≈ ৳12,345 BDT". Null when the day's FX rate isn't available,
 * so the caller can hide the line rather than show a broken "≈ ৳0".
 */
function format_bdt_equivalent(string|float $cnyAmount): ?string
{
    $rate = get_cny_to_bdt_rate();
    if ($rate === null) {
        return null;
    }

    return '≈ ৳' . number_format((float) $cnyAmount * $rate, 0) . ' BDT';
}

/** Formats a SQL DATE (YYYY-MM-DD) as e.g. "Wed, Jul 2". */
function format_date(string $sqlDate): string
{
    $ts = strtotime($sqlDate);
    return $ts === false ? $sqlDate : date('D, M j', $ts);
}

/** The real current month as YYYY-MM, computed live — never a fixed value. */
function current_month(): string
{
    return date('Y-m');
}

/**
 * Resolves a ?month= query value to a valid YYYY-MM string (Section 16).
 * Anything missing or malformed falls back to the current month, silently.
 */
function resolve_viewed_month(?string $raw): string
{
    if ($raw === null || !preg_match('/^(\d{4})-(\d{2})$/', $raw, $m)) {
        return current_month();
    }

    $year = (int) $m[1];
    $month = (int) $m[2];

    if ($year < 2000 || $year > 2100 || $month < 1 || $month > 12) {
        return current_month();
    }

    return $raw;
}

/** The month `$delta` steps away from `$yearMonth` (±1 for prev/next), as YYYY-MM. */
function adjacent_month(string $yearMonth, int $delta): string
{
    $dt = DateTime::createFromFormat('Y-m-d', $yearMonth . '-01');
    if ($dt === false) {
        $dt = new DateTime('first day of this month');
    }
    $dt->modify(($delta >= 0 ? '+' : '') . $delta . ' month');

    return $dt->format('Y-m');
}

/** Splits "YYYY-MM" into [year, month] ints, for budgets' (budget_year, budget_month) columns. */
function split_year_month(string $yearMonth): array
{
    [$year, $month] = explode('-', $yearMonth);

    return [(int) $year, (int) $month];
}

function has_month_budgets(string $yearMonth): bool
{
    [$year, $month] = split_year_month($yearMonth);
    $stmt = get_db()->prepare('SELECT 1 FROM budgets WHERE budget_year = :year AND budget_month = :month LIMIT 1');
    $stmt->execute(['year' => $year, 'month' => $month]);

    return (bool) $stmt->fetchColumn();
}

/** Planned amounts for a month, keyed by category_id. */
function get_month_budget_map(string $yearMonth): array
{
    [$year, $month] = split_year_month($yearMonth);
    $stmt = get_db()->prepare('SELECT category_id, planned_amount FROM budgets WHERE budget_year = :year AND budget_month = :month');
    $stmt->execute(['year' => $year, 'month' => $month]);

    $map = [];
    foreach ($stmt->fetchAll() as $row) {
        $map[(int) $row['category_id']] = $row['planned_amount'];
    }

    return $map;
}

/** Total planned across all categories for a month, as a decimal string. */
function get_month_planned_total(string $yearMonth): string
{
    [$year, $month] = split_year_month($yearMonth);
    $stmt = get_db()->prepare('SELECT COALESCE(SUM(planned_amount), 0) FROM budgets WHERE budget_year = :year AND budget_month = :month');
    $stmt->execute(['year' => $year, 'month' => $month]);

    return (string) $stmt->fetchColumn();
}

/**
 * Idempotent copy-forward (Section 22, B2): if $yearMonth has no budget
 * rows yet and the previous month does, copies them. Returns true only on
 * the run that actually performs the copy, so the caller can show a
 * one-time "Budgets copied from X" notice.
 */
function copy_budgets_forward_if_needed(string $yearMonth): bool
{
    if (has_month_budgets($yearMonth)) {
        return false;
    }

    $prevMonth = adjacent_month($yearMonth, -1);
    [$prevYear, $prevMonthNum] = split_year_month($prevMonth);
    [$year, $month] = split_year_month($yearMonth);

    $db = get_db();
    $stmt = $db->prepare('SELECT category_id, planned_amount FROM budgets WHERE budget_year = :year AND budget_month = :month');
    $stmt->execute(['year' => $prevYear, 'month' => $prevMonthNum]);
    $previousBudgets = $stmt->fetchAll();

    if (!$previousBudgets) {
        return false;
    }

    // INSERT IGNORE: harmless no-op if another request already copied these
    // rows in the moment between the check above and this insert.
    $insert = $db->prepare(
        'INSERT IGNORE INTO budgets (category_id, budget_year, budget_month, planned_amount)
         VALUES (:category_id, :year, :month, :amount)'
    );
    foreach ($previousBudgets as $row) {
        $insert->execute([
            'category_id' => $row['category_id'],
            'year' => $year,
            'month' => $month,
            'amount' => $row['planned_amount'],
        ]);
    }

    return true;
}

/**
 * The dashboard summary card's budget line (Section 20): null when the
 * month has no budgets at all, so the card falls back to showing the spent
 * total alone — never a "0% of ¥0" error state.
 */
function get_budget_summary(string $yearMonth): ?array
{
    $plannedTotal = (float) get_month_planned_total($yearMonth);
    if ($plannedTotal <= 0) {
        return null;
    }

    $spentTotal = (float) get_month_expense_total($yearMonth);
    $remaining = $plannedTotal - $spentTotal;

    return [
        'planned_formatted'   => format_money($plannedTotal),
        'percent_used'        => (int) round($spentTotal / $plannedTotal * 100),
        'remaining_formatted' => format_money(abs($remaining)),
        'over_budget'         => $remaining < 0,
    ];
}

/** Human label for a YYYY-MM string, e.g. "July 2026". */
function month_label(string $yearMonth): string
{
    $ts = DateTime::createFromFormat('Y-m-d', $yearMonth . '-01');
    return $ts === false ? $yearMonth : $ts->format('F Y');
}

/** Just the month name, e.g. "July" (for the summary card kicker). */
function month_name(string $yearMonth): string
{
    $ts = DateTime::createFromFormat('Y-m-d', $yearMonth . '-01');
    return $ts === false ? $yearMonth : $ts->format('F');
}

/** [start_date, end_date] (inclusive) SQL DATE strings for a YYYY-MM month. */
function month_date_range(string $yearMonth): array
{
    $start = DateTime::createFromFormat('Y-m-d', $yearMonth . '-01');
    if ($start === false) {
        $start = new DateTime('first day of this month');
    } else {
        $start->modify('first day of this month');
    }
    $end = (clone $start)->modify('last day of this month');

    return [$start->format('Y-m-d'), $end->format('Y-m-d')];
}

/** Total spent in a month across all categories, as a decimal string. */
function get_month_expense_total(string $yearMonth): string
{
    [$start, $end] = month_date_range($yearMonth);
    $stmt = get_db()->prepare(
        'SELECT COALESCE(SUM(amount), 0) FROM expenses WHERE expense_date BETWEEN :start AND :end'
    );
    $stmt->execute(['start' => $start, 'end' => $end]);

    return (string) $stmt->fetchColumn();
}

/**
 * Total spent by one specific user in a month, as a decimal string. Powers
 * the dashboard's "My Expenses" card so each person can see their own
 * monthly total without adding up (or seeing) the other person's spending.
 */
function get_user_month_expense_total(string $yearMonth, int $userId): string
{
    [$start, $end] = month_date_range($yearMonth);
    $stmt = get_db()->prepare(
        'SELECT COALESCE(SUM(amount), 0) FROM expenses
         WHERE expense_date BETWEEN :start AND :end AND user_id = :user_id'
    );
    $stmt->execute(['start' => $start, 'end' => $end, 'user_id' => $userId]);

    return (string) $stmt->fetchColumn();
}

/**
 * [start_date, end_date, through_month_num] for January 1 of $year through
 * the last day of the current real month — "year to date," not the full
 * calendar year. A past year that's already over is capped at December.
 */
function year_to_date_range(int $year): array
{
    $throughMonthNum = ((int) date('Y') === $year) ? (int) date('n') : 12;
    $start = sprintf('%04d-01-01', $year);
    $end = (new DateTime(sprintf('%04d-%02d-01', $year, $throughMonthNum)))
        ->modify('last day of this month')->format('Y-m-d');

    return [$start, $end, $throughMonthNum];
}

/** Year-to-date expense total (Jan 1 through the current real month), as a decimal string. */
function get_year_expense_total(int $year): string
{
    [$start, $end] = year_to_date_range($year);
    $stmt = get_db()->prepare(
        'SELECT COALESCE(SUM(amount), 0) FROM expenses WHERE expense_date BETWEEN :start AND :end'
    );
    $stmt->execute(['start' => $start, 'end' => $end]);

    return (string) $stmt->fetchColumn();
}

/**
 * Month-by-month expense totals for the year so far (January through the
 * current real month), oldest first — bars scale to the largest month in
 * the list, same convention as build_category_chart_data().
 */
function get_year_month_breakdown(int $year): array
{
    [$start, $end, $throughMonthNum] = year_to_date_range($year);
    $stmt = get_db()->prepare(
        'SELECT MONTH(expense_date) AS m, COALESCE(SUM(amount), 0) AS total
         FROM expenses WHERE expense_date BETWEEN :start AND :end
         GROUP BY MONTH(expense_date)'
    );
    $stmt->execute(['start' => $start, 'end' => $end]);
    $byMonth = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

    $yearTotal = 0.0;
    foreach ($byMonth as $total) {
        $yearTotal += (float) $total;
    }
    $maxMonth = $byMonth ? max(array_map('floatval', $byMonth)) : 0.0;

    $rows = [];
    for ($m = 1; $m <= $throughMonthNum; $m++) {
        $amount = (float) ($byMonth[$m] ?? 0);
        $ym = sprintf('%04d-%02d', $year, $m);
        $rows[] = [
            'month'           => $ym,
            'label'           => month_name($ym),
            'total_formatted' => format_money($amount),
            'percent'         => $yearTotal > 0 ? number_format($amount / $yearTotal * 100, 1) : '0.0',
            'width_percent'   => $maxMonth > 0 ? number_format($amount / $maxMonth * 100, 1) : '0.0',
        ];
    }

    return $rows;
}

/** Per-category totals for a month, highest first. Categories with no spending are omitted. */
function get_month_category_totals(string $yearMonth): array
{
    [$start, $end] = month_date_range($yearMonth);
    $stmt = get_db()->prepare(
        'SELECT c.id, c.name, c.color, SUM(e.amount) AS total
         FROM expenses e
         JOIN categories c ON c.id = e.category_id
         WHERE e.expense_date BETWEEN :start AND :end
         GROUP BY c.id, c.name, c.color
         ORDER BY total DESC'
    );
    $stmt->execute(['start' => $start, 'end' => $end]);

    return $stmt->fetchAll();
}

/**
 * Adds 'percent' (share of the month total) and 'width_percent' (relative
 * to the largest category) to each row from get_month_category_totals().
 */
function build_category_chart_data(array $categoryTotals): array
{
    if (empty($categoryTotals)) {
        return [];
    }

    $max = (float) $categoryTotals[0]['total'];
    $monthTotal = array_sum(array_map(fn ($row) => (float) $row['total'], $categoryTotals));

    return array_map(function ($row) use ($max, $monthTotal) {
        $amount = (float) $row['total'];

        return [
            'id'              => (int) $row['id'],
            'name'            => $row['name'],
            'color'           => $row['color'],
            'icon'            => category_icon($row['name']),
            'amount_formatted' => format_money($amount),
            'percent'         => $monthTotal > 0 ? number_format($amount / $monthTotal * 100, 1) : '0.0',
            'width_percent'   => $max > 0 ? number_format($amount / $max * 100, 1) : '0.0',
        ];
    }, $categoryTotals);
}

/**
 * The most recent expenses in a month (for the Recent list), ordered by the
 * actual expense_date — not by created_at/id — so backfilled or edited
 * entries always sort by when they happened, not when they were typed in.
 * Same-day entries are broken by created_at/id (newest-entered first) purely
 * as a tiebreaker, not as the primary sort.
 */
function get_recent_expenses(string $yearMonth, int $limit = 5): array
{
    [$start, $end] = month_date_range($yearMonth);
    $stmt = get_db()->prepare(
        'SELECT e.id, e.expense_date, e.amount, e.description, e.category_id, e.payment_method_id, e.user_id,
                c.name AS category_name, c.color AS category_color,
                u.display_name AS user_display_name
         FROM expenses e
         JOIN categories c ON c.id = e.category_id
         JOIN users u ON u.id = e.user_id
         WHERE e.expense_date BETWEEN :start AND :end
         ORDER BY e.expense_date DESC, e.created_at DESC, e.id DESC
         LIMIT ' . (int) $limit
    );
    $stmt->execute(['start' => $start, 'end' => $end]);

    return $stmt->fetchAll();
}

/**
 * Shapes one get_recent_expenses() row for display (initial render and AJAX
 * refresh share this). Includes both the raw editable values (for the edit
 * modal) and a display fallback (category name when no description was typed).
 */
function format_recent_expense_row(array $row): array
{
    $ownerId = (int) $row['user_id'];

    return [
        'id'                 => (int) $row['id'],
        'expense_date'       => $row['expense_date'],
        'date_formatted'     => format_date($row['expense_date']),
        'amount'             => $row['amount'],
        'amount_formatted'   => format_money($row['amount']),
        'category_id'        => (int) $row['category_id'],
        'category_name'      => $row['category_name'],
        'category_color'     => $row['category_color'],
        'category_icon'      => category_icon($row['category_name']),
        'payment_method_id'  => $row['payment_method_id'] !== null ? (int) $row['payment_method_id'] : null,
        'description'        => $row['description'],
        'description_display' => ($row['description'] !== null && $row['description'] !== '')
            ? $row['description'] : $row['category_name'],
        'user_display_name'  => $row['user_display_name'],
        // Sumona can add any expense but only edit/delete her own; Motaher
        // (admin) can edit/delete everything. Computed once here so the
        // PHP row template, the JSON API, and the JS re-render after a
        // save all check the same value instead of re-deriving it.
        'editable'           => current_user_is_admin() || $ownerId === (int) (current_user()['id'] ?? 0),
    ];
}

/** All active expense categories in display order (for the entry form grid). */
function get_active_expense_categories(): array
{
    $stmt = get_db()->query(
        "SELECT id, name, color FROM categories WHERE type = 'expense' AND is_active = 1 ORDER BY sort_order"
    );

    return $stmt->fetchAll();
}

/** All active payment methods in display order. */
function get_active_payment_methods(): array
{
    $stmt = get_db()->query('SELECT id, name FROM payment_methods WHERE is_active = 1 ORDER BY sort_order');

    return $stmt->fetchAll();
}

/** Bootstrap Icons class for a category, by name. Presentation only — not stored data. */
function category_icon(string $name): string
{
    return match ($name) {
        'Food' => 'bi-egg-fried',
        'Groceries' => 'bi-cart3',
        'Transportation' => 'bi-bus-front',
        'Home' => 'bi-house-door',
        'Utilities' => 'bi-lightning-charge',
        'Health/Medical' => 'bi-heart-pulse',
        'Shopping' => 'bi-bag',
        'Personal' => 'bi-person',
        'Travel' => 'bi-airplane',
        'Recreation' => 'bi-controller',
        'Family' => 'bi-people',
        'Gifts' => 'bi-gift',
        'Debt' => 'bi-bank',
        'Paycheck' => 'bi-cash-stack',
        'Bonus' => 'bi-star',
        'Interest' => 'bi-percent',
        'Savings' => 'bi-piggy-bank',
        default => 'bi-tag',
    };
}

/**
 * Renders one category tile grid. Shared by the dashboard's hero form and
 * the edit modal (includes/layout/edit_modal.php), and now also the
 * All Expenses filter bar's category dropdown does its own thing — this is
 * specifically the tappable tile grid used for entry/editing.
 */
function render_category_grid(
    array $visible,
    array $hidden,
    int $selectedId,
    string $hiddenInputId,
    string $hiddenInputName,
    string $gridId,
    string $moreBtnId
): void {
    ?>
    <input type="hidden" name="<?= e($hiddenInputName) ?>" id="<?= e($hiddenInputId) ?>" value="<?= e((string) $selectedId) ?>">
    <div class="cat-grid" id="<?= e($gridId) ?>">
      <?php foreach ($visible as $cat): ?>
        <button type="button" class="cat<?= (int) $cat['id'] === $selectedId ? ' selected' : '' ?>"
                style="--c:<?= e($cat['color']) ?>" data-id="<?= e((string) $cat['id']) ?>" data-name="<?= e($cat['name']) ?>">
          <span class="cat-ic"><i class="bi <?= e(category_icon($cat['name'])) ?>"></i></span>
          <span class="cat-name"><?= e($cat['name']) ?></span>
        </button>
      <?php endforeach; ?>
      <?php if ($hidden): ?>
        <button type="button" class="cat cat-more" id="<?= e($moreBtnId) ?>">
          <span class="cat-ic"><i class="bi bi-three-dots"></i></span>
          <span class="cat-name">More</span>
        </button>
        <?php foreach ($hidden as $cat): ?>
          <button type="button" class="cat<?= (int) $cat['id'] === $selectedId ? ' selected' : ' d-none' ?>"
                  style="--c:<?= e($cat['color']) ?>" data-id="<?= e((string) $cat['id']) ?>" data-name="<?= e($cat['name']) ?>">
            <span class="cat-ic"><i class="bi <?= e(category_icon($cat['name'])) ?>"></i></span>
            <span class="cat-name"><?= e($cat['name']) ?></span>
          </button>
        <?php endforeach; ?>
      <?php endif; ?>
    </div>
    <?php
}

/**
 * Renders one expense row (the ".tx" card). Shared by the dashboard's
 * Recent list and the All Expenses results list, so both pages produce
 * byte-identical, equally-editable rows for the shared edit modal to open.
 * Expects a format_recent_expense_row() shaped array.
 */
function render_expense_row(array $item): void
{
    $editable = $item['editable'] ?? true;
    ?>
    <div class="tx<?= $editable ? '' : ' tx-locked' ?>" style="--c:<?= e($item['category_color']) ?>"
         <?= $editable ? 'role="button" tabindex="0"' : '' ?>
         data-editable="<?= $editable ? '1' : '0' ?>"
         data-id="<?= e((string) $item['id']) ?>"
         data-amount="<?= e($item['amount']) ?>"
         data-expense-date="<?= e($item['expense_date']) ?>"
         data-category-id="<?= e((string) $item['category_id']) ?>"
         data-payment-method-id="<?= e($item['payment_method_id'] !== null ? (string) $item['payment_method_id'] : '') ?>"
         data-description="<?= e($item['description'] ?? '') ?>">
      <span class="tx-ic"><i class="bi <?= e($item['category_icon']) ?>"></i></span>
      <div>
        <div class="tx-desc"><?= e($item['description_display']) ?></div>
        <div class="tx-meta"><?= e($item['category_name']) ?> · <?= e($item['date_formatted']) ?> · <?= e($item['user_display_name']) ?></div>
      </div>
      <div class="tx-amt tnum"><?= e($item['amount_formatted']) ?></div>
    </div>
    <?php
}

// =============================================================================
// Income (Phase 9) — deliberately parallel to the expense functions above,
// not unified with them: incomes have no payment method and a different
// category list, so sharing code would cost more clarity than it'd save.
// =============================================================================

/** All active income categories in display order (for the entry form grid). */
function get_active_income_categories(): array
{
    $stmt = get_db()->query(
        "SELECT id, name, color FROM categories WHERE type = 'income' AND is_active = 1 ORDER BY sort_order"
    );

    return $stmt->fetchAll();
}

/** Total income in a month, as a decimal string. */
function get_month_income_total(string $yearMonth): string
{
    [$start, $end] = month_date_range($yearMonth);
    $stmt = get_db()->prepare(
        'SELECT COALESCE(SUM(amount), 0) FROM incomes WHERE income_date BETWEEN :start AND :end'
    );
    $stmt->execute(['start' => $start, 'end' => $end]);

    return (string) $stmt->fetchColumn();
}

/**
 * All income entries for a month, newest first. Unlike expenses' Recent
 * list (capped at 5), income volume is naturally low, so the income page
 * just lists everything for the month (Section 8).
 */
function get_month_incomes(string $yearMonth): array
{
    [$start, $end] = month_date_range($yearMonth);
    $stmt = get_db()->prepare(
        'SELECT i.id, i.income_date, i.amount, i.description, i.category_id,
                c.name AS category_name, c.color AS category_color,
                u.display_name AS user_display_name
         FROM incomes i
         JOIN categories c ON c.id = i.category_id
         JOIN users u ON u.id = i.user_id
         WHERE i.income_date BETWEEN :start AND :end
         ORDER BY i.income_date DESC, i.id DESC'
    );
    $stmt->execute(['start' => $start, 'end' => $end]);

    return $stmt->fetchAll();
}

/** Shapes one get_month_incomes() row for display (mirrors format_recent_expense_row). */
function format_recent_income_row(array $row): array
{
    return [
        'id'                 => (int) $row['id'],
        'income_date'        => $row['income_date'],
        'date_formatted'     => format_date($row['income_date']),
        'amount'             => $row['amount'],
        'amount_formatted'   => format_money($row['amount']),
        'category_id'        => (int) $row['category_id'],
        'category_name'      => $row['category_name'],
        'category_color'     => $row['category_color'],
        'category_icon'      => category_icon($row['category_name']),
        'description'        => $row['description'],
        'description_display' => ($row['description'] !== null && $row['description'] !== '')
            ? $row['description'] : $row['category_name'],
        'user_display_name'  => $row['user_display_name'],
    ];
}

/**
 * Renders one income row (the ".tx" card, income-flavored). data-kind
 * "income" is how app.js's shared row-click delegation knows to open the
 * income edit modal instead of the expense one.
 */
function render_income_row(array $item): void
{
    ?>
    <div class="tx" style="--c:<?= e($item['category_color']) ?>" role="button" tabindex="0" data-kind="income"
         data-id="<?= e((string) $item['id']) ?>"
         data-amount="<?= e($item['amount']) ?>"
         data-income-date="<?= e($item['income_date']) ?>"
         data-category-id="<?= e((string) $item['category_id']) ?>"
         data-description="<?= e($item['description'] ?? '') ?>">
      <span class="tx-ic"><i class="bi <?= e($item['category_icon']) ?>"></i></span>
      <div>
        <div class="tx-desc"><?= e($item['description_display']) ?></div>
        <div class="tx-meta"><?= e($item['category_name']) ?> · <?= e($item['date_formatted']) ?> · <?= e($item['user_display_name']) ?></div>
      </div>
      <div class="tx-amt tnum" style="color:var(--good);">+<?= e($item['amount_formatted']) ?></div>
    </div>
    <?php
}

/**
 * The dashboard's savings strip (B4): income − expenses = "Saved this
 * month". Unlike the budget line, this always renders — even ¥0 income is
 * meaningful information, not an error state.
 */
function get_month_savings(string $yearMonth): array
{
    $income = (float) get_month_income_total($yearMonth);
    $expense = (float) get_month_expense_total($yearMonth);
    $saved = $income - $expense;

    return [
        'income_formatted' => format_money($income),
        'expense_formatted' => format_money($expense),
        'saved_formatted' => format_money(abs($saved)),
        'is_negative' => $saved < 0,
    ];
}
