-- transaction_search: resolve expense_metadata by BOTH links, without fanning out.
--
-- THE DEFECT (measured 2026-08-22): the view joined only on
-- `em.transaction_id = t.id`, so it silently omitted every card-settled order,
-- which carries only `matched_transaction_id`. 148 transactions are affected --
-- Glovo orders in Milan, Uber Eats, all `source='order-bridge'` -- and for each
-- of them the view reports NULL payment_method / order_reference / line_items
-- while the data sits in the table.
--
-- That is not cosmetic. This view is the agent read surface: Hermes'
-- postgres-personal MCP selects payment_method and order_reference from it
-- (personal-agent-gateway/mcp-servers/postgres-personal/queries.py), and
-- home-dashboard reads it via dash_ro. CLAUDE.md states the rule the view broke:
-- "That lookup must match transaction_id OR matched_transaction_id: a
-- card-settled order has only the latter."
--
-- NOT a fan-out fix, contrary to a first reading. The original join CANNOT fan
-- out: expense_metadata_transaction_id_key is UNIQUE on transaction_id, so it
-- matched at most one row by construction. Sums taken over this view were never
-- inflated.
--
-- But broadening the join is exactly what INTRODUCES that risk, which is why
-- LATERAL is used rather than a second OR'd condition. transaction_id and
-- matched_transaction_id are enforced by two SEPARATE unique indexes
-- (expense_metadata_transaction_id_key, uq_expense_matched_txn), so one
-- transaction can legally be pointed at by two DIFFERENT rows -- one via each
-- link. Zero such transactions exist today; the LATERAL means that stays a
-- non-event rather than a silent duplicate in the agent's spending totals.
--
-- Tie-break matches every other reader (getTransactions, replaceLineItems,
-- matchTransaction, /api/transactions/[id]/order), so the row this view resolves
-- is the row the UI shows.
--
-- Verified before applying: row count unchanged at 4760 (= count(transactions)),
-- sum(amount) over debits unchanged at 1514412.89 -- amount comes from
-- transactions, never from the metadata row -- and the only difference is the
-- 148 rows that gain the metadata they always should have had.
--
-- CREATE OR REPLACE VIEW DROPS GRANTS. dash_ro (home-dashboard) and agent_ro
-- (Hermes) both read this view and losing either breaks a surface with no error
-- at replace time, so both are re-granted below.

CREATE OR REPLACE VIEW transaction_search AS
 SELECT t.id,
    t.transaction_date,
    t.description,
    COALESCE(t.merchant_normalized, t.merchant_name) AS merchant,
    t.category,
    t.transaction_type,
    COALESCE(t.amount_aud, t.amount) AS amount,
    t.foreign_currency_amount,
    t.foreign_currency_code,
    t.location,
    s.bank_name,
    s.card_name,
    s.account_number,
    s.statement_type,
    s.billing_end_date,
    em.payment_method,
    em.order_reference,
    em.line_items
   FROM transactions t
     LEFT JOIN statements s ON s.id = t.statement_id
     LEFT JOIN LATERAL (
       SELECT em2.payment_method, em2.order_reference, em2.line_items
         FROM expense_metadata em2
        WHERE em2.transaction_id = t.id OR em2.matched_transaction_id = t.id
        ORDER BY (em2.transaction_id = t.id) DESC NULLS LAST, em2.id
        LIMIT 1
     ) em ON true;

GRANT SELECT ON transaction_search TO dash_ro;
GRANT SELECT ON transaction_search TO agent_ro;