DB="$HOME/Library/Mail/V10/MailData/Envelope Index"

# Date range from $ARGUMENTS. Use SQLite date expressions directly:
# "yesterday"   → dt >= date('now','-1 day','localtime') AND dt < date('now','localtime')
# "today"       → dt >= date('now','localtime')
# "last 7 days" → dt >= date('now','-7 days','localtime')
# "this month"  → dt >= date('now','start of month','localtime')
# Always print the range before querying so the period is visible in output.

sqlite3 "$DB" "
SELECT datetime(m.date_received,'unixepoch','localtime') as dt,
       s.subject, a.address as sender, mb.url, m.ROWID
FROM messages m
JOIN subjects  s  ON m.subject = s.ROWID
JOIN addresses a  ON m.sender  = a.ROWID
JOIN mailboxes mb ON m.mailbox = mb.ROWID
WHERE datetime(m.date_received,'unixepoch','localtime') >= date('now','-1 day','localtime')
  AND datetime(m.date_received,'unixepoch','localtime') <  date('now','localtime')
  AND m.deleted = 0
  AND mb.url NOT LIKE '%Spam%' AND mb.url NOT LIKE '%Trash%'
  AND mb.url NOT LIKE '%Sent%'
  AND (
    s.subject LIKE '%receipt%'
    OR s.subject LIKE '%invoice%'
    OR s.subject LIKE '%payment%'
    OR s.subject LIKE '%order%'
    OR s.subject LIKE '%purchase%'
    OR s.subject LIKE '%charge%'
    OR s.subject LIKE '%transaction%'
    OR s.subject LIKE '%billing%'
    OR s.subject LIKE '%renewal%'
    OR s.subject LIKE '%subscription%'
    OR s.subject LIKE '%paid%'
    OR s.subject LIKE '%refund%'
    OR s.subject LIKE '%transfer%'
    OR s.subject LIKE '%successful%'
    OR s.subject LIKE '%confirmation%'
    -- Indonesian / multilingual
    OR s.subject LIKE '%bukti%'
    OR s.subject LIKE '%pembayaran%'
    OR s.subject LIKE '%tagihan%'
    OR s.subject LIKE '%transaksi%'
    OR s.subject LIKE '%berhasil%'
    -- Common financial senders
    OR a.address LIKE '%bank%'
    OR a.address LIKE '%paypal%'
    OR a.address LIKE '%stripe%'
    OR a.address LIKE '%xendit%'
    OR a.address LIKE '%livin%'
    OR a.address LIKE '%gopay%'
    OR a.address LIKE '%ovo%'
    OR a.address LIKE '%dana%'
    OR a.address LIKE '%apple%'
    OR a.address LIKE '%google%'
    OR a.address LIKE '%amazon%'
    OR a.address LIKE '%shopee%'
    OR a.address LIKE '%tokopedia%'
    OR a.address LIKE '%grab%'
  )
ORDER BY m.date_received DESC;" 2>/dev/null

python3 ~/.claude/skills/_mail-shared/parser.py <ROWID1> <ROWID2> ...

import re

patterns = [
    r'(?:Rp|IDR)\s*[\d.,]+',          # Indonesian Rupiah
    r'(?:USD|US\$|\$)\s*[\d.,]+',      # USD
    r'(?:SGD|S\$)\s*[\d.,]+',          # Singapore dollar
    r'Total[:\s]+(?:Rp|IDR|USD|\$|€|£)[\d.,]+',
    r'Amount[:\s]+(?:Rp|IDR|USD|\$|€|£)[\d.,]+',
    r'(?:€|£|¥|₹|RM|THB|PHP|VND)\s*[\d.,]+',
]