import sqlite3
import re
from datetime import datetime
import argparse

def mine_projects(db_path, since_timestamp, output_file):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    query = """
    SELECT s.id, s.title, m.content, m.timestamp
    FROM messages m
    JOIN sessions s ON m.session_id = s.id
    WHERE s.id NOT LIKE 'cron_%' 
      AND s.started_at > ? 
      AND m.role = 'user'
    ORDER BY m.timestamp ASC
    """

    cursor.execute(query, (since_timestamp,))
    rows = cursor.fetchall()

    keywords = ['project', 'deliverable', 'working on', 'migrate', 'build', 'implement']

    extracted = []
    for row in rows:
        session_id, title, content, ts = row
        content = str(content)
        # find sentences with keywords
        sentences = re.split(r'(?<=[.!?])\s+', content)
        for s in sentences:
            if any(k in s.lower() for k in keywords):
                # remove newlines
                s_clean = s.replace('\n', ' ').strip()
                # truncate if too long
                if len(s_clean) > 300:
                    s_clean = s_clean[:300] + '...'
                
                # format date
                dt = datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M')
                extracted.append(f"[{dt}] {session_id} ({title}): {s_clean}")

    # deduplicate while keeping order
    seen = set()
    dedup = []
    for line in extracted:
        if line not in seen:
            seen.add(line)
            dedup.append(line)

    with open(output_file, "w") as f:
        for line in dedup:
            f.write(line + "\n")

    print(f"Extracted {len(dedup)} mentions to {output_file}.")

if __name__ == "__main__":
    parser = argparse.ArgumentParser(description='Extract project mentions from Hermes state.db')
    parser.add_argument('--since', type=float, required=True, help='Unix timestamp to start from')
    parser.add_argument('--out', type=str, default='/tmp/project_mentions.txt', help='Output file path')
    args = parser.parse_args()
    
    mine_projects("/root/.hermes/state.db", args.since, args.out)