import sqlite3
from datetime import datetime, timezone, timedelta

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

def get_conn():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def get_active_signals_no_filter(min_score: float = 35.0, limit: int = 20):
    """Get ALL signals without published_at filter (for 'ALLE' view) - NO status filter."""
    conn = get_conn()
    now = datetime.now(timezone.utc)
    query = """
        SELECT * FROM signals 
        WHERE radar_score >= ?
        ORDER BY timestamp DESC
        LIMIT ?
    """
    params = [min_score, limit]
    rows = conn.execute(query, params).fetchall()
    conn.close()
    return [dict(r) for r in rows]

def get_active_signals_days_filter(days: int = None, min_score: float = 35.0, limit: int = 20):
    """Get signals filtered by published_at days if days parameter given."""
    conn = get_conn()
    now = datetime.now(timezone.utc)
    
    query = """
        SELECT * FROM signals 
        WHERE radar_score >= ?
    """
    params = [min_score]
    
    # Add published_at filter only when days parameter is explicitly given (not None/empty)
    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 LIMIT ?"
    params.append(limit)
    
    rows = conn.execute(query, params).fetchall()
    conn.close()
    return [dict(r) for r in rows]

# Test the functions
print("=== Test 1: All signals (no days filter, no status filter) ===")
signals = get_active_signals_no_filter()
print(f"Total signals (all, no filter): {len(signals)}")
for s in signals[:3]:
    print(f"  {s['signal_id'][:8]}: published_at='{s['published_at']}', timestamp={s['timestamp']}")

print("\n=== Test 2: Signals with days=2 filter ===")
signals_2d = get_active_signals_days_filter(days=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_active_signals_days_filter(days=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_active_signals_days_filter(days=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']}")

print("\n=== Test 5: Signals with days=0 (should show all with published_at) ===")
signals_0d = get_active_signals_days_filter(days=0)
print(f"Signals with published_at (days=0): {len(signals_0d)}")
for s in signals_0d[:3]:
    print(f"  {s['signal_id'][:8]}: published_at='{s['published_at']}', timestamp={s['timestamp']}")