# src/database.py
import sqlite3
from datetime import datetime
import os
import csv

os.makedirs("data", exist_ok=True)

DB_NAME = "data/sentiment_history.db" 
CSV_FILE_PATH = "data/test_cases.csv"

def init_db():
    try:
        conn = sqlite3.connect(DB_NAME)
        cursor = conn.cursor()
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            text TEXT NOT NULL,
            label TEXT NOT NULL,
            timestamp DATETIME NOT NULL
        );
        """)
        conn.commit()
        conn.close()
    except Exception as e:
        print(f"Lỗi khi khởi tạo CSDL: {e}")

def add_entry(text, label):
    try:
        conn = sqlite3.connect(DB_NAME)
        cursor = conn.cursor()
        timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        cursor.execute(
            "INSERT INTO history (text, label, timestamp) VALUES (?, ?, ?)",
            (text, label, timestamp)
        )
        conn.commit()
        conn.close()
    except Exception as e:
        print(f"Lỗi khi ghi vào CSDL: {e}")

def append_to_csv(text, label):
    file_exists = os.path.isfile(CSV_FILE_PATH)

    with open(CSV_FILE_PATH, mode='a', newline='', encoding='utf-8-sig') as f:
        writer = csv.writer(f)
        if not file_exists:
            writer.writerow(['text', 'true_label'])           
        writer.writerow([text, label])

def get_history():
    try:
        conn = sqlite3.connect(DB_NAME)
        cursor = conn.cursor()
        cursor.execute("SELECT timestamp, text, label FROM history ORDER BY timestamp DESC")
        rows = cursor.fetchall()
        conn.close()
        return rows
    except Exception as e:
        print(f"Lỗi khi đọc CSDL: {e}")
        return []