#!/usr/bin/env python3
"""Query the DB for the backlog report."""
import sqlite3, os

conn = sqlite3.connect(os.path.join("/root/workspace", "saas_opportunities.db"))
conn.row_factory = sqlite3.Row

rows = conn.execute("""
    SELECT 
        o.id,
        o.vertical,
        o.value_proposition,
        o.complexity_score as complexity,
        o.potential_score as potential,
        o.distribution_score as distribution,
        o.monetization_score as monetization,
        o.total_score,
        r.detected_pain_point,
        r.persona,
        r.workaround,
        r.severity,
        r.url,
        r.title
    FROM saas_opportunities o
    JOIN raw_signals r ON o.signal_id = r.id
    WHERE o.status = 'validated'
    ORDER BY o.total_score DESC, o.monetization_score DESC
""").fetchall()

print("=== BACKLOG COMPLET ===")
print(f"Total signaux: {conn.execute('SELECT COUNT(*) FROM raw_signals').fetchone()[0]}")
print(f"Total opportunités: {conn.execute('SELECT COUNT(*) FROM saas_opportunities').fetchone()[0]}")
print()

for r in rows:
    print(f"--- OPP #{r['id']} ---")
    print(f"Vertical: {r['vertical']}")
    print(f"Proposition: {r['value_proposition']}")
    print(f"Pain point: {r['detected_pain_point']}")
    print(f"Persona: {r['persona']}")
    print(f"Workaround: {r['workaround']}")
    print(f"Score: {r['total_score']:.1f} (S:{r['potential']} C:{r['complexity']} D:{r['distribution']} M:{r['monetization']})")
    print()

conn.close()