#!/usr/bin/env python3
"""Find probable date range for a photo by looking at neighboring filenames."""

import sqlite3
import re
import sys

DB_PATH = "/home/johan/immich-compare/immich_index.db"

def get_neighbors(filename, conn):
    """Find files with similar names and show their dates."""
    # Extract prefix and number from filename like IMG_0656.jpg
    match = re.match(r'^([A-Za-z_]+)(\d+)', filename)
    if not match:
        return None
    
    prefix = match.group(1)
    num = int(match.group(2))
    
    # Search for nearby numbers (±20)
    results = []
    c = conn.cursor()
    
    for offset in range(-20, 21):
        test_num = num + offset
        if test_num < 0:
            continue
        # Try different formats for the number
        for fmt in [f"{test_num:04d}", f"{test_num:05d}", f"{test_num}"]:
            pattern = f"{prefix}{fmt}%"
            c.execute("""SELECT filename, date, id FROM assets 
                        WHERE filename LIKE ? AND date != '2024-01-28'
                        ORDER BY filename""", (pattern,))
            for row in c.fetchall():
                if row not in results:
                    results.append(row)
    
    # Sort by filename
    results.sort(key=lambda x: x[0])
    return results

def show_context(filename):
    conn = sqlite3.connect(DB_PATH)
    
    # Check if this file exists and its current date
    c = conn.cursor()
    c.execute("SELECT id, date FROM assets WHERE filename = ?", (filename,))
    current = c.fetchone()
    
    print(f"\n=== {filename} ===")
    if current:
        print(f"Current date: {current[1]} (ID: {current[0]})")
    else:
        print("Not found in Immich")
    
    neighbors = get_neighbors(filename, conn)
    if neighbors:
        print(f"\nNeighboring files with known dates:")
        print("-" * 50)
        dates = set()
        for fname, date, fid in neighbors:
            marker = " <--" if fname.upper().startswith(filename.upper().split('.')[0]) else ""
            print(f"  {fname:30} {date}{marker}")
            if date != '2024-01-28':
                dates.add(date)
        
        if dates:
            dates = sorted(dates)
            print("-" * 50)
            print(f"Date range: {dates[0]} to {dates[-1]}")
    else:
        print("No neighboring files found")
    
    conn.close()

if __name__ == "__main__":
    if len(sys.argv) > 1:
        show_context(sys.argv[1])
    else:
        # Show all Jan 28, 2024 files that match IMG_xxxx pattern
        conn = sqlite3.connect(DB_PATH)
        c = conn.cursor()
        c.execute("""SELECT filename FROM assets 
                    WHERE date = '2024-01-28' 
                    AND filename LIKE 'IMG_%'
                    ORDER BY filename""")
        print("Jan 28, 2024 files needing dates:")
        for row in c.fetchall():
            print(f"  {row[0]}")
        conn.close()