fruitmail

#!/usr/bin/env bash
# fruitmail - Fast Apple Mail search via SQLite
# Usage: fruitmail <command> [args] [options]

set -e

# --- Helpers ---

# Escape single quotes for SQL safety
escape_sql() {
    echo "${1//\'/\'\'}"
}

# Find the latest Mail database (searches V9+)
find_db() {
    local db_dir
    local db_path
    db_dir=$(ls -d "$HOME/Library/Mail/V"* 2>/dev/null | sort -V | tail -n1 || true)
    if [[ -n "$db_dir" ]]; then
        db_path="$db_dir/MailData/Envelope Index"
        if [[ -f "$db_path" ]]; then
            echo "$db_path"
            return 0
        fi
    fi
    for v in {15..9}; do
        db_path="$HOME/Library/Mail/V$v/MailData/Envelope Index"
        if [[ -f "$db_path" ]]; then
            echo "$db_path"
            return 0
        fi
    done
    echo "Error: Could not find Mail database." >&2
    echo "       Please ensure Terminal has 'Full Disk Access' in System Settings > Privacy & Security." >&2
    return 1
}

# --- Configuration ---

DB_PATH="${MAIL_DB:-}"
LIMIT=20
FORMAT="table"
QUIET=0
USE_COPY=0
ARGS=()

# Global Options
while [[ $# -gt 0 ]]; do
    case "$1" in
        -n|--limit) LIMIT="$2"; shift 2 ;;
        -j|--json) FORMAT="json"; shift ;;
        -c|--csv) FORMAT="csv"; shift ;;
        -q|--quiet) QUIET=1; shift ;;
        --db) DB_PATH="$2"; shift 2 ;;
        --copy) USE_COPY=1; shift ;;
        -h|--help) ARGS+=("$1"); shift ;;
        -*) echo "Unknown option: $1" >&2; exit 1 ;;
        *) ARGS+=("$1"); shift ;;
    esac
done
set -- "${ARGS[@]}"

CMD="${1:-help}"
shift || true

# DB Setup
if [[ -z "$DB_PATH" ]]; then
    DB_PATH=$(find_db)
fi

if [[ "$USE_COPY" -eq 1 ]]; then
    TEMP_DB=$(mktemp -t fruitmail.XXXXXX)
    trap 'rm -f "$TEMP_DB" 2>/dev/null || true' EXIT INT TERM
    cp "$DB_PATH" "$TEMP_DB"
    DB_TARGET="$TEMP_DB"
    DB_OPTS=()
else
    DB_TARGET="$DB_PATH"
    DB_OPTS=(-readonly -cmd ".timeout 2000")
fi

run_query() {
    local query="$1"
    # Pretty print tables with box style if available (SQLite 3.33+)
    local format_args=()
    case "$FORMAT" in
        json) format_args=(-json) ;;
        csv) format_args=(-csv -header) ;;
        table) 
            if sqlite3 -version | grep -q "3\.[3-9][0-9]\."; then
                format_args=(-box) # Use box if available
            else
                format_args=(-header -column)
            fi
            ;;
    esac
    sqlite3 "${DB_OPTS[@]}" "${format_args[@]}" "$DB_TARGET" "$query"
}

# --- Advanced Search Builder ---
build_search_query() {
    local criteria="1=1"
    local joins=""
    local has_attachments=0
    local has_recipients=0
    
    while [[ $# -gt 0 ]]; do
        case "$1" in
            --subject)
                val=$(escape_sql "$2"); shift 2
                criteria="$criteria AND s.subject LIKE '%$val%'"
                ;;
            --sender|--from)
                val=$(escape_sql "$2"); shift 2
                criteria="$criteria AND a.address LIKE '%$val%'"
                ;;
            --from-name)
                val=$(escape_sql "$2"); shift 2
                criteria="$criteria AND a.comment LIKE '%$val%'"
                ;;
            --to)
                val=$(escape_sql "$2"); shift 2
                has_recipients=1
                criteria="$criteria AND ra.address LIKE '%$val%'"
                ;;
            --unread)
                shift
                criteria="$criteria AND m.read = 0"
                ;;
            --read)
                shift
                criteria="$criteria AND m.read = 1"
                ;;
            --days)
                val="$2"; shift 2
                since=$(($(date +%s) - val * 86400))
                criteria="$criteria AND m.date_sent >= $since"
                ;;
            --attachment-type)
                val=$(escape_sql "$2"); shift 2
                has_attachments=1
                criteria="$criteria AND att.name LIKE '%.$val'"
                ;;
            --has-attachment)
                shift
                has_attachments=1
                ;;
            *)
                echo "Unknown search flag: $1" >&2; return 1
                ;;
        esac
    done

    # Base joins
    joins="JOIN subjects s ON m.subject = s.ROWID
           LEFT JOIN addresses a ON m.sender = a.ROWID"

    if [[ "$has_recipients" -eq 1 ]]; then
        joins="$joins
           JOIN recipients r ON m.ROWID = r.message
           JOIN addresses ra ON r.address = ra.ROWID"
    fi

    if [[ "$has_attachments" -eq 1 ]]; then
         joins="$joins
           JOIN attachments att ON m.ROWID = att.message"
    fi

    run_query "
        SELECT DISTINCT m.ROWID as id, 
               datetime(m.date_sent, 'unixepoch', 'localtime') as date,
               a.address as sender,
               s.subject
        FROM messages m
        $joins
        WHERE $criteria AND m.deleted = 0
        ORDER BY m.date_sent DESC
        LIMIT $LIMIT;
    "
}

# --- Commands ---

case "$CMD" in
    search)
        build_search_query "$@"
        ;;
        
    # Wrapper shortcuts mapping to search
    subject) build_search_query --subject "$1" ;;
    sender|from) build_search_query --sender "$1" ;;
    from-name) build_search_query --from-name "$1" ;;
    to) build_search_query --to "$1" ;;
    unread) build_search_query --unread ;;
    recent) 
        DAYS="${1:-7}"
        [[ ! "$DAYS" =~ ^[0-9]+$ ]] && DAYS=7
        build_search_query --days "$DAYS" 
        ;;
    
    # Specific commands that don't fit generic search
    attachments)
        run_query "
            SELECT DISTINCT m.ROWID as id,
                   datetime(m.date_sent, 'unixepoch', 'localtime') as date,
                   a.address as sender,
                   s.subject,
                   att.name as attachment
            FROM messages m
            JOIN subjects s ON m.subject = s.ROWID
            LEFT JOIN addresses a ON m.sender = a.ROWID
            JOIN attachments att ON m.ROWID = att.message
            ORDER BY m.date_sent DESC
            LIMIT $LIMIT;
        "
        ;;
        
    attachment-type)
        # Keeps original specific query for showing attachment names
        EXT=$(escape_sql "${1:-pdf}")
        run_query "
            SELECT DISTINCT m.ROWID as id,
                   datetime(m.date_sent, 'unixepoch', 'localtime') as date,
                   a.address as sender,
                   s.subject,
                   att.name as attachment
            FROM messages m
            JOIN subjects s ON m.subject = s.ROWID
            LEFT JOIN addresses a ON m.sender = a.ROWID
            JOIN attachments att ON m.ROWID = att.message
            WHERE att.name LIKE '%.${EXT}'
            ORDER BY m.date_sent DESC
            LIMIT $LIMIT;
        "
        ;;

    open)
        MSG_ID="${1//[^0-9]/}"
        if [[ -z "$MSG_ID" ]]; then
            echo "Usage: fruitmail open <message_id>" >&2
            exit 1
        fi
        MSG_URI=$(sqlite3 "${DB_OPTS[@]}" "$DB_TARGET" "SELECT document_id FROM messages WHERE ROWID = $MSG_ID;")
        if [[ -n "$MSG_URI" ]]; then
            if ! open "message://%3c${MSG_URI}%3e"; then
                echo "Error: Failed to open message in Mail.app." >&2
                exit 1
            fi
        else
            # Fallback for missing document_id: search all mailboxes
            OPEN_RESULT=$(osascript << EOF
tell application "Mail"
    try
        set targetId to $MSG_ID
        set msg to missing value
        repeat with mailboxRef in every mailbox
            try
                set msg to first message of mailboxRef whose id is targetId
                exit repeat
            end try
        end repeat
        if msg is missing value then
            return "ERROR_NOT_FOUND"
        end if
        open msg
        activate
    on error
        return "ERROR_NOT_FOUND"
    end try
end tell
EOF
)
            if [[ "$OPEN_RESULT" == "ERROR_NOT_FOUND" ]]; then
                echo "Error: Message not found." >&2
                exit 1
            fi
        fi
        ;;

    body)
        MSG_ID="${1//[^0-9]/}"
        if [[ -z "$MSG_ID" ]]; then
            echo "Usage: fruitmail body <message_id>" >&2
            exit 1
        fi

        # AppleScript to get content
        CONTENT=$(osascript << EOF
tell application "Mail"
    try
        set targetId to $MSG_ID
        set msg to missing value
        repeat with mailboxRef in every mailbox
            try
                set msg to first message of mailboxRef whose id is targetId
                exit repeat
            end try
        end repeat
        if msg is missing value then
            return "ERROR_NOT_FOUND"
        end if
        return content of msg
    on error
        return "ERROR_NOT_FOUND"
    end try
end tell
EOF
)
        # Handle JSON output for body
        if [[ "$CONTENT" == "ERROR_NOT_FOUND" ]]; then
             echo "Error: Message not found." >&2; exit 1
        fi
        
        if [[ "$FORMAT" == "json" ]]; then
            # Safe JSON creation using jq if available, otherwise manual
            # Escape newlines and quotes for manual JSON
            clean_content="${CONTENT//\\/\\\\}"
            clean_content="${clean_content//\"/\\\"}"
            clean_content="${clean_content//$'\n'/\\n}"
            clean_content="${clean_content//$'\r'/}"
            echo "{\"id\": $MSG_ID, \"body\": \"$clean_content\"}"
        else
            echo "$CONTENT"
        fi
        ;;

    stats)
        echo "=== Mail Database Statistics ==="
        echo "Database: $DB_PATH"
        [[ "$USE_COPY" -eq 1 ]] && echo "Access: Safe Copy" || echo "Access: Read-Only (Paranoid)"
        echo ""
        echo "Total messages: $(sqlite3 "${DB_OPTS[@]}" "$DB_TARGET" "SELECT COUNT(*) FROM messages;")"
        echo "Unread:         $(sqlite3 "${DB_OPTS[@]}" "$DB_TARGET" "SELECT COUNT(*) FROM messages WHERE read = 0 AND deleted = 0;")"
        echo "Deleted:        $(sqlite3 "${DB_OPTS[@]}" "$DB_TARGET" "SELECT COUNT(*) FROM messages WHERE deleted = 1;")"
        echo "Attachments:    $(sqlite3 "${DB_OPTS[@]}" "$DB_TARGET" "SELECT COUNT(DISTINCT message) FROM attachments;")"
        echo ""
        echo "Size: $(du -h "$DB_PATH" | cut -f1)"
        ;;
    
    query)
        run_query "$1"
        ;;
    
    help|--help|-h)
        cat << 'EOF'
fruitmail - Fast Apple Mail search via SQLite
version 1.2.0 (Unified Search)

Usage: fruitmail [options] <command> [args]

Commands:
  search [flags]              Advanced search (see flags below)
  subject <pattern>           Search by subject (shortcut)
  sender <pattern>            Search by sender (shortcut)
  to <pattern>                Search by recipient (shortcut)
  unread                      List unread emails (shortcut)
  recent <days>               List recent emails (shortcut)
  
  open <id>                   Open email in Mail.app
  body <id>                   Read content (supports --json)
  stats                       Database statistics
  query "<sql>"               Run raw SQL query

Search Flags:
  --subject <text>
  --sender <text>
  --to <text>
  --unread / --read
  --days <num>
  --has-attachment
  --attachment-type <ext>

Examples:
  fruitmail search --subject "invoice" --days 30 --unread
  fruitmail sender "amazon" --json
  fruitmail body 12345 --json
EOF
        ;;
    
    *)
        echo "Unknown command: $CMD" >&2
        exit 1
        ;;
esac