this has an issue where it only pulls from 9/19 back even though there are emails from 9/20 and 9/21 - i am concerned it is not extracting all emails - figure out what's going on and design a more robust app #!/bin/bash
set -e  # Exit immediately if a command exits with a non-zero status

# Name of the virtual environment
VENV_NAME="email_env"

# Database and output file paths
DB_PATH="/Users/matthew/Desktop/1.sqlite"
OUTPUT_DIR="/Users/matthew/Desktop/All_Emails"

# --- Virtual Environment Management ---
if [ ! -d "$VENV_NAME" ]; then
    echo "Creating virtual environment: $VENV_NAME"
    python3 -m venv "$VENV_NAME"
fi
source "$VENV_NAME"/bin/activate

# --- Dependency Installation ---
echo "Installing dependencies..."
pip install html2text

# --- Email Extraction Script (Embedded Python) ---
echo "Starting email extraction..."
python3 << EOF
import sqlite3
import os
from datetime import datetime, timedelta
import html2text
import sys

DB_PATH = "$DB_PATH"
OUTPUT_DIR = "$OUTPUT_DIR"

def convert_timestamp(timestamp):
    """Converts Apple's timestamp to a datetime object."""
    return datetime(2001, 1, 1) + timedelta(seconds=timestamp)

def sanitize_filename(filename):
    """Sanitizes a filename to remove invalid characters."""
    return "".join(c for c in filename if c.isalnum() or c in ("_", "-", "."))[:255]

try:
    # Connect to the SQLite database
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    # Check the table structure
    cursor.execute("PRAGMA table_info(ZMESSAGE)")
    message_columns = [column[1] for column in cursor.fetchall()]
    cursor.execute("PRAGMA table_info(ZMESSAGECONTENT)")
    content_columns = [column[1] for column in cursor.fetchall()]

    print("ZMESSAGE columns:", message_columns)
    print("ZMESSAGECONTENT columns:", content_columns)

    # Determine which table has ZBODYHTML
    if 'ZBODYHTML' in message_columns:
        body_table = 'ZMESSAGE'
    elif 'ZBODYHTML' in content_columns:
        body_table = 'ZMESSAGECONTENT'
    else:
        raise Exception("ZBODYHTML column not found in either table")

    # SQL query to select all emails
    query = f"""
    SELECT 
        m.Z_PK,           -- Email ID
        {body_table}.ZBODYHTML,  -- Email Body (HTML)
        m.ZDATERECEIVED,  -- Received Timestamp
        m.ZSUBJECT,       -- Subject
        m.ZFROMHEADER     -- Sender
    FROM ZMESSAGE m
    {"JOIN ZMESSAGECONTENT ON m.Z_PK = ZMESSAGECONTENT.ZMESSAGE" if body_table == 'ZMESSAGECONTENT' else ''}
    WHERE m.ZACCOUNT = 2 
    ORDER BY m.ZDATERECEIVED DESC;
    """
    cursor.execute(query)

    # Iterate through the results
    for row in cursor:
        email_id, body_html, raw_date, subject, from_header = row
        date = convert_timestamp(raw_date)

        # Convert HTML to plain text
        h = html2text.HTML2Text()
        h.ignore_links = False
        body_text = h.handle(body_html) if body_html else "No body content"

        # Check if the email contains the word "aron"
        search_text = f"{body_text} {subject} {from_header}".lower()
        if "aron" not in search_text:
            continue

        # Extract date components for folder organization
        year = date.strftime("%Y")
        month = date.strftime("%m")
        day = date.strftime("%d")
        time = date.strftime("%H-%M-%S")

        # Create directory for the date
        day_dir = os.path.join(OUTPUT_DIR, year, month, day)
        os.makedirs(day_dir, exist_ok=True)

        # Create a filename for the email
        filename = f"{time}_{sanitize_filename(subject or 'No Subject')}_{email_id}.txt"
        filepath = os.path.join(day_dir, filename)

        # Write the email content to the file
        with open(filepath, 'w', encoding='utf-8') as f:
            f.write("================== EMAIL START ==================\n")
            f.write(f"ID: {email_id}\n")
            f.write(f"Date: {date}\n")
            f.write(f"From: {from_header}\n")
            f.write(f"Subject: {subject}\n\n")
            f.write(f"{body_text}\n")
            f.write("================== EMAIL END ===================\n")

        print(f"Processed email {email_id} from {date}")

    conn.close()
    print("Email extraction complete. Check the 'All_Emails' folder on your desktop.")

except Exception as e:
    print(f"An error occurred: {e}")

EOF

deactivate  # Deactivate the virtual environment
echo "Bash script finished."