#!/usr/bin/env python3
"""Query and format the full SaaS opportunity backlog."""
import sqlite3, os, json
from collections import OrderedDict

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

rows = conn.execute("""
    SELECT o.*, r.source, r.detected_pain_point, r.persona, r.url, r.severity,
           r.text as raw_text
    FROM saas_opportunities o
    JOIN raw_signals r ON o.signal_id = r.id
    ORDER BY o.total_score DESC
""").fetchall()

# ====== PART 1: JSON registry (100-point SaaS Validator scoring) ======
# Pain/20, Saturation/20, Feasibility/20, Distribution/20, Monetization/20
def scale(old_score):
    """Scale 1-5 original to 0-20."""
    return min(20, max(0, old_score * 4))

registry = []
for r in rows:
    entry = {
        "saas_id": r["value_proposition"].lower().replace(" ", "-").replace("/", "-")[:50],
        "title": r["value_proposition"],
        "target_audience": r["persona"],
        "problem_solved": r["detected_pain_point"],
        "proposed_solution": r["value_proposition"],
        "signals": [r["source"], r["url"] or ""],
        "evaluation": {
            "pain": scale(r["potential_score"]),
            "saturation": scale(min(5, 6 - (r["complexity_score"] // 1))),  # inverse proxy
            "feasibility": scale(r["complexity_score"]),
            "distribution": scale(r["distribution_score"]),
            "monetization": scale(r["monetization_score"]),
            "total_score": scale(r["potential_score"]) +
                           scale(min(5, 6 - (r["complexity_score"] // 1))) +
                           scale(r["complexity_score"]) +
                           scale(r["distribution_score"]) +
                           scale(r["monetization_score"])
        },
        "suggested_mvp_stack": {
            5: "FastAPI/Next.js + SQLite + Resend/SendGrid + Stripe",
            4: "Next.js + Supabase + Stripe + Tailwind",
            3: "Next.js + Supabase + Stripe + OpenAI API",
            2: "Python/FastAPI + React + PostgreSQL + Celery",
            1: "Python/FastAPI + React + PostgreSQL + Redis + Celery"
        }.get(r["complexity_score"], "Next.js + Supabase + Stripe")
    }
    registry.append(entry)

print("=" * 60)
print("PART 1: JSON REGISTRY (100-POINT SAAS VALIDATOR SCORING)")
print("=" * 60)
print(json.dumps(registry, indent=2))

# ====== PART 2: Markdown table with score grid ======
print("\n\n")
print("=" * 60)
print("PART 2: PRIORITIZED BACKLOG — ALL 24 OPPORTUNITIES")
print("=" * 60)
print()
print("| Rang | Concept SaaS & Cible | Pain point (Source) | Score (P/Sat/Feas/D/M) | Architecture MVP | Moat |")
print("| :--- | :--- | :--- | :--- | :--- | :--- |")
for i, r in enumerate(rows, 1):
    avg = r["total_score"]
    concept = r["value_proposition"][:55]
    persona = r["persona"][:25]
    pain = r["detected_pain_point"][:50]
    score = f"P:{r['potential_score']}/ Sat:-/ Feas:{r['complexity_score']}/ D:{r['distribution_score']}/ M:{r['monetization_score']}"
    mvp = {
        5: "FastAPI/Next.js + SQLite + Resend + Stripe",
        4: "Next.js + Supabase + Stripe + Tailwind",
        3: "Next.js + Supabase + OpenAI + Stripe",
        2: "Python/FastAPI + React + PostgreSQL + Celery",
        1: "Python/FastAPI + React + Redis + Celery"
    }.get(r["complexity_score"], "Next.js + Supabase")
    moat = {
        "Trades/Contractors": "Subreddit targeting + word-of-mouth among trades",
        "Legal Tech": "Freelance community distribution + AI fine-tuning on contracts",
        "Finance/Compliance": "QuickBooks/Xero integration lock-in",
        "Freelance/Finance": "Milestone workflow + reminder automation",
        "Community/Tools": "Discord OAuth + server analytics uniqueness",
        "Creator/Tools": "YouTube API partnership + creator distribution",
        "Agency/Tools": "White-label + recurring reporting workflow lock-in",
        "Developer/Tools": "API integration lock-in + dev community word-of-mouth",
        "E-commerce/Supply Chain": "Supplier database crawl + price history data",
        "E-commerce/Analytics": "Return pattern data + Shopify app store",
    }.get(r["vertical"], "Data integration lock-in")
    print(f"| {i} | {persona} — {concept} | \"{pain[:45]}...\" | {score} | {mvp} | {moat} |")

print()
print("=" * 60)
print("PART 3: TOP 5 RECOMMENDATIONS (with rationale)")
print("=" * 60)

top5 = rows[:5]
for i, r in enumerate(top5, 1):
    print(f"\n{'─'*60}")
    print(f"  #{i}: {r['value_proposition']}")
    print(f"{'─'*60}")
    print(f"  👤 Cible:       {r['persona']}")
    print(f"  🔴 Pain:        {r['detected_pain_point']}")
    print(f"  📊 Score:       {r['total_score']}/5 ({r['potential_score']} pain · {r['complexity_score']} feas · {r['distribution_score']} dist · {r['monetization_score']} mon)")
    print(f"  📦 MVP:         2-{4 - r['complexity_score']} semaines")
    print(f"  💰 Pricing:     $(10-29) × {r['distribution_score'] * 30} utilisateurs potentiels identifiés")

conn.close()