import sqlite3
from flask import g

def init_db(app):
    with app.app_context():
        db = get_db(app)
        db.execute('''
            CREATE TABLE IF NOT EXISTS news (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT,
                source TEXT,
                published TEXT,
                url TEXT UNIQUE,
                summary TEXT,
                content TEXT,
                timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        db.commit()

def get_db(app):
    if 'db' not in g:
        g.db = sqlite3.connect(app.config['DATABASE'])
        g.db.row_factory = sqlite3.Row
    return g.db

def close_db(e=None):
    db = g.pop('db', None)
    if db is not None:
        db.close()

def cache_news(app, articles):
    db = get_db(app)
    for article in articles:
        try:
            db.execute('''
                INSERT OR REPLACE INTO news 
                (title, source, published, url, summary, content)
                VALUES (?, ?, ?, ?, ?, ?)
            ''', (
                article['title'],
                article['source'],
                article['published'],
                article['url'],
                article['summary'],
                article['content']
            ))
        except sqlite3.IntegrityError:
            continue
    db.commit()

def get_cached_news(app):
    db = get_db(app)
    cur = db.execute('SELECT * FROM news ORDER BY timestamp DESC LIMIT 50')
    return [dict(row) for row in cur.fetchall()]