<?php
/**
 * Journal Trades — shared filter/query/row-render logic.
 *
 * Single source of truth for "build a filtered, ordered, paginated trade
 * list" — used by both public/journal.php's initial server-rendered page
 * and api/journal/trades-more.php's progressive-loading ("Show More")
 * endpoint, so the two can never drift into showing different trades for
 * the same filters. Kept in its own file (not inlined in journal.php)
 * specifically so the API endpoint can require it without pulling in the
 * rest of journal.php's page-rendering code.
 */

/** Reads/validates the Journal Trades filter set from any params array ($_GET on either caller). */
function buildJournalTradesFilters(array $q): array {
    return [
        'instrument'          => $q['instrument'] ?? null,
        'account_id'          => $q['account_id'] ?? null,
        'account_type'        => in_array($q['account_type'] ?? '', ['prop_firm', 'personal'], true) ? $q['account_type'] : null,
        'session'             => $q['session'] ?? null,
        'from'                => $q['from'] ?? null,
        'to'                  => $q['to'] ?? null,
        'classification'      => $q['classification'] ?? null,
        'strategy_version_id' => $q['strategy_version_id'] ?? null,
        'status'              => in_array($q['status'] ?? '', ['open', 'closed', 'draft'], true) ? $q['status'] : null,
        // Numeric "risk % greater than X" filter — used by Reports' Over-
        // Risking tile to link to the exact trades it counted.
        'min_risk_pct'        => is_numeric($q['min_risk_pct'] ?? null) ? (float)$q['min_risk_pct'] : null,
        // Free-text search — Active Journeys' Trade Journal filter bar
        // (journal redesign, 2026-07-31). Matches instrument, account
        // label, or the trade's own notes.
        'q'                   => trim((string)($q['q'] ?? '')) !== '' ? trim((string)$q['q']) : null,
    ];
}

/**
 * Builds the WHERE clause + bound params shared by the count and fetch
 * queries. Always restricts to accounts whose lifecycle is still Active —
 * a trade on a breached or closed account never appears in Journal Trades,
 * regardless of any other filter selected. This is a base rule, not a
 * togglable filter.
 */
function journalTradesWhere(string $userId, array $filters, $db = null): array {
    // Validation Workflow — workflow-aware, not unconditionally
    // main-workflow-only. $db is optional only for defensive backward
    // compatibility (falls back to the pre-existing main-workflow-only
    // condition if ever called without it); both real callers below
    // (journalTradesCount()/journalTradesFetch()) always have $db and
    // always pass it through.
    $validationCond = $db ? validationScopeSql(ValidationService::getWorkflowState($userId, $db), 't') : '(t.is_validation = 0 OR t.is_validation IS NULL)';
    $where = [
        't.user_id = :uid',
        't.deleted_at IS NULL',
        $validationCond,
        '(ta.deleted_at IS NULL)',
        "(ta.lifecycle_state IS NULL OR ta.lifecycle_state NOT IN ('breached','closed'))",
    ];
    $params = [':uid' => $userId];

    if ($filters['instrument'])   { $where[] = 't.instrument = :instrument'; $params[':instrument'] = $filters['instrument']; }
    if ($filters['account_id'])   { $where[] = 't.account_id = :account_id'; $params[':account_id'] = $filters['account_id']; }
    if ($filters['account_type']) { $where[] = 'ta.account_type = :account_type'; $params[':account_type'] = $filters['account_type']; }
    if ($filters['session'])      { $where[] = 't.session = :session'; $params[':session'] = $filters['session']; }
    if ($filters['from'])         { $where[] = 't.closed_at >= :from'; $params[':from'] = $filters['from'] . ' 00:00:00'; }
    if ($filters['to'])           { $where[] = 't.closed_at <= :to'; $params[':to'] = $filters['to'] . ' 23:59:59'; }
    if ($filters['classification']) {
        // Comma-separated list support — Reports' A+/A/B/C grade groups each
        // map to 1-3 underlying trade_classification values. A plain single
        // value still works exactly as before.
        $classList = array_filter(array_map('trim', explode(',', $filters['classification'])));
        if (count($classList) > 1) {
            $placeholders = [];
            foreach (array_values($classList) as $ci => $cv) { $placeholders[] = ":classification{$ci}"; $params[":classification{$ci}"] = $cv; }
            $where[] = 't.trade_classification IN (' . implode(',', $placeholders) . ')';
        } elseif (!empty($classList)) {
            $where[] = 't.trade_classification = :classification'; $params[':classification'] = $classList[array_key_first($classList)];
        }
    }
    if ($filters['strategy_version_id']) { $where[] = 't.strategy_version_id = :svid'; $params[':svid'] = $filters['strategy_version_id']; }
    if ($filters['status'])              { $where[] = 't.status = :status'; $params[':status'] = $filters['status']; }
    if ($filters['min_risk_pct'] !== null) { $where[] = 't.risk_pct > :min_risk_pct'; $params[':min_risk_pct'] = $filters['min_risk_pct']; }
    if (!empty($filters['q'])) {
        $where[] = '(t.instrument LIKE :q_instrument OR ta.label LIKE :q_label OR t.notes LIKE :q_notes)';
        $needle = '%' . $filters['q'] . '%';
        $params[':q_instrument'] = $needle;
        $params[':q_label'] = $needle;
        $params[':q_notes'] = $needle;
    }

    return [$where, $params];
}

function journalTradesCount($db, string $userId, array $filters): int {
    [$where, $params] = journalTradesWhere($userId, $filters, $db);
    $whereClause = implode(' AND ', $where);
    $row = $db->raw(
        "SELECT COUNT(*) AS cnt FROM trades t LEFT JOIN trading_accounts ta ON ta.id = t.account_id WHERE {$whereClause}",
        $params
    );
    return (int)($row[0]['cnt'] ?? 0);
}

/**
 * Fetches one page of trades. Ordering makes the Journal feel "live": the
 * account with the most recent trading activity (of ANY status, not just
 * closed) sorts first, and within an account the newest trade sorts first
 * — never oldest-first. account_last_activity is computed once via a
 * grouped derived table (not a per-row correlated subquery) so this stays
 * cheap regardless of how many trades exist.
 */
function journalTradesFetch($db, string $userId, array $filters, int $offset, int $limit): array {
    [$where, $params] = journalTradesWhere($userId, $filters, $db);
    $whereClause = implode(' AND ', $where);
    // Distinct placeholder name — never reuse :uid twice in one bound
    // params array (a real, previously-confirmed PDO/MariaDB bug on this
    // project when the same named placeholder appears more than once).
    $params[':uid_activity'] = $userId;
    $offset = max(0, $offset);
    $limit = max(1, min(200, $limit));
    $validationCondActivity = validationScopeSql(ValidationService::getWorkflowState($userId, $db));

    return $db->raw(
        "SELECT t.id, t.instrument, t.direction, t.status, t.closed_at, t.captured_at,
                ta.label AS account_label, ta.account_type,
                CASE WHEN ta.account_type = 'prop_firm' THEN t.challenge_pnl ELSE t.broker_pnl END AS effective_pnl
         FROM trades t
         LEFT JOIN trading_accounts ta ON ta.id = t.account_id
         LEFT JOIN (
             SELECT account_id, MAX(COALESCE(closed_at, captured_at)) AS last_activity
             FROM trades
             WHERE user_id = :uid_activity AND deleted_at IS NULL AND {$validationCondActivity}
             GROUP BY account_id
         ) acct_activity ON acct_activity.account_id = t.account_id
         WHERE {$whereClause}
         ORDER BY acct_activity.last_activity DESC, COALESCE(t.closed_at, t.captured_at) DESC
         LIMIT {$limit} OFFSET {$offset}",
        $params
    );
}

/** Renders one trade row — shared between the server-rendered page and the "Show More" JSON endpoint. */
function renderJournalTradeRowHtml(array $t): string {
    $pnl = $t['effective_pnl'] !== null ? (float)$t['effective_pnl'] : null;
    $when = $t['closed_at'] ?? $t['captured_at'];
    $url = '/traderalign/journal?' . http_build_query(['view' => 'trades', 'trade_id' => $t['id']]);
    $status = $t['status'] ?? '';

    $html  = '<a href="' . htmlspecialchars($url) . '" class="trade-row">';
    $html .= '<div class="acct-row-left">';
    $html .= '<div class="row-name">' . htmlspecialchars($t['instrument'] ?? 'Unknown')
        . ' <span class="' . (($t['direction'] ?? '') === 'long' ? 'positive' : 'negative') . '" style="font-size:.68rem">' . strtoupper($t['direction'] ?? '') . '</span></div>';
    $html .= '<div class="row-meta">' . htmlspecialchars($t['account_label'] ?? 'Unknown account')
        . ' &middot; ' . ($when ? date('M j, Y g:ia', strtotime($when)) : '&mdash;')
        . ' &middot; <span class="status-pill ' . htmlspecialchars($status) . '">' . htmlspecialchars(ucfirst($status)) . '</span></div>';
    $html .= '</div>';
    $html .= '<span class="row-amount ' . ($pnl === null ? '' : pnlClass($pnl)) . '">' . ($pnl === null ? 'View &rarr;' : pnlFormat($pnl)) . '</span>';
    $html .= '</a>';
    return $html;
}
