import sqlite3
from datetime import datetime
from flask import current_app, g
import pytz

def get_db():
    """Kết nối database SQLite"""
    if 'db' not in g:
        g.db = sqlite3.connect(
            current_app.config['DATABASE'],
            detect_types=sqlite3.PARSE_DECLTYPES
        )
        g.db.row_factory = sqlite3.Row
    return g.db

def close_db(e=None):
    """Đóng kết nối database"""
    db = g.pop('db', None)
    if db is not None:
        db.close()

def init_db():
    """Khởi tạo database"""
    db = get_db()
    db.execute('''
        CREATE TABLE IF NOT EXISTS sentiments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            text TEXT NOT NULL,
            sentiment TEXT NOT NULL,
            timestamp TEXT NOT NULL
        )
    ''')
    db.commit()

def save_sentiment(text, sentiment):
    """Lưu kết quả phân loại vào database"""
    db = get_db()
    # Lấy thời gian theo múi giờ Việt Nam (UTC+7)
    vietnam_tz = pytz.timezone('Asia/Ho_Chi_Minh')
    timestamp = datetime.now(vietnam_tz).strftime('%Y-%m-%d %H:%M:%S')
    db.execute(
        'INSERT INTO sentiments (text, sentiment, timestamp) VALUES (?, ?, ?)',
        (text, sentiment, timestamp)
    )
    db.commit()

def get_history(limit=50):
    """Lấy lịch sử phân loại"""
    db = get_db()
    history = db.execute(
        'SELECT * FROM sentiments ORDER BY timestamp DESC LIMIT ?',
        (limit,)
    ).fetchall()
    return history