import sqlite3
import os
from datetime import datetime


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


def init_db() -> None:
    conn = sqlite3.connect(DB_PATH)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS insights (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            topic TEXT NOT NULL,
            content TEXT NOT NULL,
            provider TEXT NOT NULL,
            model TEXT NOT NULL,
            sent_at TEXT NOT NULL
        )
    """)
    conn.commit()
    conn.close()


def save_insight(topic: str, content: str, provider: str, model: str) -> int:
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    sent_at = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    cursor.execute(
        "INSERT INTO insights (topic, content, provider, model, sent_at) VALUES (?, ?, ?, ?, ?)",
        (topic, content, provider, model, sent_at),
    )
    insight_id = cursor.lastrowid
    conn.commit()
    conn.close()
    return insight_id


def get_insights(limit: int = 50):
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    rows = conn.execute(
        "SELECT * FROM insights ORDER BY id DESC LIMIT ?", (limit,)
    ).fetchall()
    conn.close()
    return [dict(row) for row in rows]