#!/usr/bin/env python3
"""Initialise la base SQLite du SaaS Validator."""
import sqlite3, os

DB_PATH = os.path.join(os.path.dirname(__file__), "saas_opportunities.db")

def get_conn():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    return conn

def init_db():
    conn = get_conn()
    conn.executescript("""
        CREATE TABLE IF NOT EXISTS raw_signals (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            source TEXT NOT NULL,
            url TEXT,
            title TEXT,
            text TEXT NOT NULL,
            timestamp TEXT NOT NULL DEFAULT (datetime('now')),
            detected_pain_point TEXT,
            persona TEXT,
            workaround TEXT,
            severity INTEGER DEFAULT 0,
            processed INTEGER DEFAULT 0
        );

        CREATE TABLE IF NOT EXISTS saas_opportunities (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            vertical TEXT NOT NULL,
            value_proposition TEXT NOT NULL,
            complexity_score INTEGER DEFAULT 0,
            potential_score INTEGER DEFAULT 0,
            distribution_score INTEGER DEFAULT 0,
            monetization_score INTEGER DEFAULT 0,
            total_score REAL GENERATED ALWAYS AS (
                (complexity_score + potential_score + distribution_score + monetization_score) / 4.0
            ) STORED,
            status TEXT DEFAULT 'draft',
            created_at TEXT DEFAULT (datetime('now')),
            signal_id INTEGER REFERENCES raw_signals(id)
        );

        CREATE INDEX IF NOT EXISTS idx_signals_processed ON raw_signals(processed);
        CREATE INDEX IF NOT EXISTS idx_opportunities_status ON saas_opportunities(status);
    """)
    conn.commit()
    conn.close()
    print(f"✓ Base initialisée : {DB_PATH}")

def insert_signal(source, url, title, text, pain_point, persona, workaround, severity):
    conn = get_conn()
    conn.execute(
        "INSERT INTO raw_signals (source, url, title, text, detected_pain_point, persona, workaround, severity) VALUES (?,?,?,?,?,?,?,?)",
        (source, url, title, text, pain_point, persona, workaround, severity)
    )
    conn.commit()
    signal_id = conn.execute("SELECT last_insert_rowid()").fetchone()[0]
    conn.close()
    return signal_id

def promote_to_opportunity(signal_id, vertical, value_prop, complexity, potential, distribution, monetization):
    conn = get_conn()
    conn.execute(
        "INSERT INTO saas_opportunities (signal_id, vertical, value_proposition, complexity_score, potential_score, distribution_score, monetization_score, status) VALUES (?,?,?,?,?,?,?,'validated')",
        (signal_id, vertical, value_prop, complexity, potential, distribution, monetization)
    )
    conn.execute("UPDATE raw_signals SET processed=1 WHERE id=?", (signal_id,))
    conn.commit()
    conn.close()

def get_all_opportunities(status=None):
    conn = get_conn()
    if status:
        rows = conn.execute("SELECT * FROM saas_opportunities WHERE status=? ORDER BY total_score DESC", (status,)).fetchall()
    else:
        rows = conn.execute("SELECT * FROM saas_opportunities ORDER BY total_score DESC").fetchall()
    conn.close()
    return rows

if __name__ == "__main__":
    init_db()