import sqlite3
from datetime import datetime, timezone, timedelta

DB_PATH = "/opt/struktur/social-media-radar/signals/radar.db"

def get_all_signals():
    """Get ALL signals without any filter except radar_score."""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    query = "SELECT * FROM signals WHERE radar_score >= 0.35 ORDER BY timestamp DESC"
    rows = conn.execute(query).fetchall()
    conn.close()
    return [dict(r) for r in rows]

def get_signals_with_days_filter(days: int):
    """Get signals filtered by published_at days if days parameter given."""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    now = datetime.now(timezone.utc)
    
    query = "SELECT * FROM signals WHERE radar_score >= 0.35"
    params = []
    
    # Add published_at filter only when days parameter is explicitly given
    if days is not None and days > 0:
        cutoff = (now - timedelta(days=days)).isoformat()
        query += """
            AND published_at IS NOT NULL 
            AND published_at != '' 
            AND published_at >= ?
        """
        params.append(cutoff)
    
    query += " ORDER BY timestamp DESC"
    rows = conn.execute(query, params).fetchall()
    conn.close()
    return [dict(r) for r in rows]

# Test
print("=== Test 1: All signals (no days filter) ===")
signals = get_all_signals()
print(f"Total signals (radar_score >= 0.35): {len(signals)}")
for s in signals[:5]:
    print(f"  {s['signal_id'][:8]}: source={s['source_name']}, published_at='{s['published_at']}', timestamp={s['timestamp']}")

print("\n=== Test 2: Signals with days=2 filter ===")
signals_2d = get_signals_with_days_filter(2)
print(f"Signals with published_at within 2 days: {len(signals_2d)}")
for s in signals_2d[:3]:
    print(f"  {s['signal_id'][:8]}: published_at='{s['published_at']}', timestamp={s['timestamp']}")

print("\n=== Test 3: Signals with days=7 filter ===")
signals_7d = get_signals_with_days_filter(7)
print(f"Signals with published_at within 7 days: {len(signals_7d)}")
for s in signals_7d[:3]:
    print(f"  {s['signal_id'][:8]}: published_at='{s['published_at']}', timestamp={s['timestamp']}")

print("\n=== Test 4: Signals with days=14 filter ===")
signals_14d = get_signals_with_days_filter(14)
print(f"Signals with published_at within 14 days: {len(signals_14d)}")
for s in signals_14d[:3]:
    print(f"  {s['signal_id'][:8]}: published_at='{s['published_at']}', timestamp={s['timestamp']}")