db.py

import sqlite3
import os
from datetime import datetime

DB_PATH = "db/data.db"
os.makedirs("db", exist_ok=True)

def get_connection():
    return sqlite3.connect(DB_PATH)

def init_db():
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("""
        CREATE TABLE IF NOT EXISTS history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id TEXT NOT NULL,
            original TEXT,
            summary TEXT,
            tags TEXT,
            sentiment TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.commit()
    conn.close()

def save_history(user_id, original, summary, tags, sentiment):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("""
        INSERT INTO history (user_id, original, summary, tags, sentiment)
        VALUES (?, ?, ?, ?, ?)
    """, (user_id, original, summary, tags, sentiment))
    conn.commit()
    conn.close()

def get_user_history(user_id, limit=20):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("""
        SELECT original, summary, tags, sentiment, created_at
        FROM history
        WHERE user_id = ?
        ORDER BY created_at DESC
        LIMIT ?
    """, (user_id, limit))
    rows = cur.fetchall()
    conn.close()
    return rows

def enforce_history_limit(user_id, max_entries=20):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("SELECT COUNT(*) FROM history WHERE user_id = ?", (user_id,))
    count = cur.fetchone()[0]
    if count >= max_entries:
        to_delete = count - max_entries + 1
        cur.execute("""
            DELETE FROM history 
            WHERE id IN (
                SELECT id FROM history WHERE user_id = ?
                ORDER BY created_at ASC
                LIMIT ?
            )
        """, (user_id, to_delete))
        conn.commit()
    conn.close()